Hàm Excel: học công thức trên bảng tính trực tiếp
Công thức và hàm Excel giải thích trên bảng tính bạn sửa được: VLOOKUP, XLOOKUP, IF, SUMIF, COUNTIF, ngày tháng, văn bản và mảng động. Đổi một số hay một công thức, bảng tính tự tính lại ngay trên trình duyệt.
Bắt đầu khóa họcCông thức cơ bản
- SUMGõ =SUM(B2:B6) bên dưới một cột số để cộng chúng lại, hoặc nhấn Alt+= để AutoSum tự viết công thức. Tính tổng theo dòng, các ô rời nhau và nhiều sheet trên trang tính sửa được.
- Phép trừExcel không có hàm SUBTRACT: gõ =B2-C2 để lấy ô này trừ ô kia. Trừ cả một cột, trừ nhiều ô cùng lúc, trừ phần trăm hoặc trừ ngày, trên trang tính sửa được ngay.
- Nhân và chiaNhân trong Excel bằng dấu sao, =B2*C2, và chia bằng dấu gạch chéo, =B2/C2. Nhân cả cột với một số, dùng PRODUCT và tránh lỗi #DIV/0!, ngay trên các trang tính bạn có thể sửa.
- AVERAGE=AVERAGE(B2:B7) cộng các số trong B2:B7 rồi chia cho số lượng số. Tìm hiểu ô trống và số 0 làm thay đổi kết quả ra sao, cách bỏ qua số 0 và cách tính trung bình 3 giá trị lớn nhất.
- COUNT và COUNTA=COUNT(B2:B8) đếm các ô chứa số, =COUNTA(B2:B8) đếm mọi ô không trống, còn =COUNTBLANK(B2:B8) đếm các ô trống. Xem cả ba hàm trên một trang tính bạn có thể sửa trực tiếp.
- Tham chiếu tuyệt đốiTham chiếu tuyệt đối như $E$1 giữ nguyên khi bạn sao chép công thức, còn tham chiếu tương đối như E1 thì dịch chuyển theo. Nhấn F4 để thêm dấu đô la. Xem sự khác biệt ngay trên các trang tính bạn có thể sửa.
- Phần trămCông thức tính phần trăm trong Excel là =phần/tổng, ví dụ =B2/C2, với ô được định dạng phần trăm. Phần trăm trên tổng, phần trăm của một số, cộng thêm hoặc trừ bớt một tỷ lệ phần trăm, ngay trên các trang tính tương tác.
- Phần trăm thay đổiCông thức tính phần trăm thay đổi trong Excel là =(mới-cũ)/cũ, ví dụ =(C2-B2)/B2, định dạng ô theo phần trăm. Kết quả âm là giảm. Các trang tính tương tác có ví dụ so sánh tháng này với tháng trước, giá trị ban đầu bằng 0 và điểm phần trăm.
Hàm logic
- IF=IF(B2>=50,"Pass","Fail") kiểm tra B2 có từ 50 trở lên không, đúng thì trả về Pass, sai thì trả về Fail. Tìm hiểu cú pháp IF, IF với văn bản, IF với phép tính, IF khi ô trống và những lỗi khiến IF trả về kết quả sai.
- IF lồng nhau=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))) đặt một hàm IF vào trong hàm IF khác để chọn giữa nhiều hơn hai kết quả. Tìm hiểu cách đọc IF lồng nhau, vì sao thứ tự điều kiện quan trọng, và khi nào nên dùng IFS hoặc bảng tra cứu.
- IFS=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F") kiểm tra từng điều kiện theo thứ tự và trả về giá trị đi cùng điều kiện TRUE đầu tiên. Tìm hiểu cú pháp IFS, giá trị mặc định TRUE, vì sao IFS trả về #N/A và so sánh với IF lồng nhau.
- AND, OR, NOT=AND(B2>=10,B2<=20) chỉ trả về TRUE khi mọi điều kiện đều đúng, còn =OR(B2="North",B2="South") trả về TRUE khi có ít nhất một điều kiện đúng. Tìm hiểu AND, OR, NOT và XOR khi dùng riêng và khi đặt trong IF, cách kiểm tra một số có nằm giữa hai giá trị không, và cách viết AND, OR trong công thức mảng.
- IFERROR=IFERROR(B2/C2,0) trả về B2/C2, hoặc 0 khi phép chia bị lỗi. Tìm hiểu IFERROR với VLOOKUP, trả về ô trống thay cho lỗi, vì sao IFNA hợp hơn cho tra cứu, và vì sao giấu mọi lỗi có thể che mất sai sót thật.
- SWITCH=SWITCH(B2,"N","North","S","South","Unknown") lần lượt so B2 với từng giá trị và trả về kết quả đi cặp với giá trị khớp chính xác đầu tiên, hoặc Unknown khi không có giá trị nào khớp. Tìm hiểu cú pháp SWITCH, giá trị mặc định, mẫu SWITCH(TRUE,...) và khi nào nên dùng IFS hoặc IF lồng nhau.
- ISBLANK, ISNUMBER=ISBLANK(B2) trả về TRUE khi B2 trống, còn =ISNUMBER(B2) trả về TRUE khi B2 chứa số. Tìm hiểu ISBLANK, ISNUMBER, ISTEXT, ISERROR, ISNA, ISEVEN và ISODD, vì sao công thức trả về "" không phải ô trống, và cách ISNUMBER(SEARCH()) kiểm tra ô có chứa văn bản hay không.
Tra cứu dữ liệu
- VLOOKUP=VLOOKUP(F2,A2:D6,3,FALSE) tìm F2 trong cột đầu tiên của A2:D6 và trả về giá trị ở cột thứ ba của cùng hàng. So khớp chính xác và gần đúng, sửa lỗi #N/A, tra cứu từ sheet khác, hai điều kiện.
- XLOOKUP=XLOOKUP(F2,A2:A6,C2:C6) tìm F2 trong A2:A6 và trả về giá trị ở cùng hàng của C2:C6. Văn bản khi không tìm thấy, trả về nhiều cột cùng lúc, tra cứu sang trái, kết quả khớp cuối cùng, so khớp gần đúng và ký tự đại diện.
- INDEX MATCH=INDEX(C2:C6,MATCH(F2,A2:A6,0)) tìm hàng chứa F2 trong cột A và trả về giá trị ở hàng đó của cột C. Cách này dò được sang trái, tra cứu hai chiều và chạy trên mọi phiên bản Excel.
- INDEX=INDEX(A2:C6,3,2) trả về giá trị ở hàng thứ ba, cột thứ hai của A2:C6. Dùng hàm này để lấy mục thứ n của danh sách, cả một hàng hoặc một cột, và giá trị ở vị trí mà MATCH tìm được.
- MATCH=MATCH(E2,A2:A6,0) trả về vị trí của E2 trong A2:A6: là 4 nếu đó là mục thứ tư. Kiểu khớp 0, 1 và -1, ký tự đại diện, khớp phân biệt chữ hoa chữ thường và cách kiểm tra một giá trị có nằm trong danh sách hay không.
- HLOOKUP=HLOOKUP("Mar",A1:E3,2,FALSE) tìm Mar ở hàng đầu tiên của A1:E3 và trả về giá trị ở hàng thứ hai của cùng cột. Tìm khớp chính xác, khớp gần đúng, và khi nào XLOOKUP là lựa chọn tốt hơn.
- XMATCH=XMATCH(E2,A2:A6) trả về vị trí của E2 trong A2:A6, mặc định là khớp chính xác. Hàm này còn tìm được giá trị nhỏ hơn hoặc lớn hơn liền kề mà không cần sắp xếp, tìm từ dưới lên, và dùng ký tự đại diện.
- Dò tìm nhiều điều kiện=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7) trả về giá trị ở hàng có cột A khớp với E2 và cột B khớp với F2. Kèm theo là cách dùng INDEX MATCH, cột phụ cho VLOOKUP và FILTER để lấy mọi kết quả khớp.
- VLOOKUP và XLOOKUPXLOOKUP làm được mọi việc của VLOOKUP, mặc định so khớp chính xác, không cần số thứ tự cột, tra cứu được sang trái và có đối số khi không tìm thấy. VLOOKUP vẫn là lựa chọn khi tệp phải mở được trong Excel 2019 trở về trước.
- INDIRECT=INDIRECT("C"&E2) đọc ô có địa chỉ được ghép thành văn bản: cột C, hàng ghi trong E2. Dùng hàm này để chọn trang tính theo tên ghi trong ô, tạo vùng từ các con số và làm danh sách thả xuống phụ thuộc.
- OFFSET=OFFSET(A1,3,2) trả về ô cách A1 3 hàng xuống dưới và 2 cột sang phải. Khi có thêm chiều cao, hàm trả về cả một vùng, đây là cách tính tổng N hàng cuối hay dựng trung bình trượt.
- CHOOSE=CHOOSE(B2,"Low","Medium","High") trả về Low khi B2 là 1, Medium khi là 2 và High khi là 3. Đổi số thành tên, chọn vùng để tính tổng, thay thế IF lồng nhau và chọn cột bằng CHOOSECOLS.
Đếm và tính tổng theo điều kiện
- COUNTIF=COUNTIF(B2:B7,"North") đếm các ô trong B2:B7 chứa North. Đếm theo văn bản, số, ký tự đại diện, ô trống và ngày, và tìm giá trị trùng lặp, trên trang tính bạn có thể sửa trực tiếp.
- COUNTIFS=COUNTIFS(A2:A7,"North",C2:C7,">50") đếm các hàng có khu vực là North và doanh số lớn hơn 50. Đếm giữa hai số hoặc hai ngày, với logic OR và với ô trống, trên trang tính trực tiếp.
- SUMIF=SUMIF(A2:A7,"North",C2:C7) cộng các giá trị trong C2:C7 ở những dòng mà cột A là North. Tổng nếu lớn hơn, nếu văn bản có chứa, theo ngày và từ sheet khác, trên trang tính sửa được.
- SUMIFS=SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Apple") cộng doanh số trong C2:C7 ở những dòng có khu vực là North và sản phẩm là Apple. Khoảng ngày, logic OR và bộ lọc tùy chọn, trên trang tính sửa được.
- AVERAGEIF=AVERAGEIF(A2:A7,"North",C2:C7) tính trung bình các giá trị trong C2:C7 ở những hàng có cột A là North. Tìm hiểu AVERAGEIFS cho nhiều điều kiện, trung bình bỏ qua số 0, cách xử lý #DIV/0!, cùng MAXIFS và MINIFS.
- Đếm ô chứa văn bản=COUNTIF(A2:A8,"*") đếm các ô trong A2:A8 chứa văn bản, bỏ qua số, ngày tháng và ô trống. Đếm các ô chứa một từ cụ thể, và trả về một giá trị nếu ô có chứa văn bản.
- COUNTIF ô không trống=COUNTIF(B2:B8,"<>") đếm các ô trong B2:B8 không trống, giống như COUNTA. Thêm điều kiện khác bằng COUNTIFS và xử lý những ô chỉ trông như trống.
- Đếm giá trị duy nhất=COUNTA(UNIQUE(A2:A9)) đếm có bao nhiêu giá trị khác nhau trong A2:A9. Với Excel đời cũ, dùng =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)). Đếm giá trị chỉ xuất hiện một lần, đếm theo điều kiện và bỏ qua ô trống.
- SUMPRODUCT=SUMPRODUCT(B2:B6,C2:C6) nhân từng số lượng với đơn giá rồi cộng các kết quả. Với điều kiện như (A2:A7="North")*C2:C7, hàm tính tổng và đếm ở những chỗ SUMIFS không làm được: theo tháng, so cột với cột, với OR.
- SUBTOTAL=SUBTOTAL(9,C2:C8) cộng C2:C8 như SUM nhưng bỏ qua các dòng SUBTOTAL khác trong vùng và các dòng bị bộ lọc ẩn đi. Mã hàm 9 và 109, đếm số dòng hiển thị, và AGGREGATE để bỏ qua lỗi.
- Trung bình có trọng số=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) là trung bình có trọng số: mỗi giá trị được nhân với trọng số của nó, các tích được cộng lại, rồi tổng được chia cho tổng các trọng số. Áp dụng cho điểm số, GPA theo tín chỉ và giá theo số lượng.
Văn bản
- Nối chuỗi`=A2&" "&B2` nối văn bản trong A2 và B2, có một dấu cách ở giữa. CONCATENATE và CONCAT làm cùng việc đó; TEXT giúp số và ngày tháng vẫn dễ đọc khi được nối vào.
- TEXTJOIN`=TEXTJOIN(", ",TRUE,A2:A6)` nối mọi ô của A2:A6 thành một đoạn văn bản, có dấu phẩy và khoảng trắng giữa các mục và bỏ qua ô trống. Thêm FILTER để chỉ nối các dòng thỏa một điều kiện.
- Tách văn bản`=TEXTBEFORE(A2," ")` trả về tên đầu từ `Ana Silva`, còn `=TEXTAFTER(A2," ")` trả về phần họ. TEXTSPLIT tách một ô thành nhiều cột cùng lúc; LEFT, MID và FIND làm việc tương tự trong Excel cũ.
- LEFT, RIGHT, MID`=LEFT(A2,3)` trả về 3 ký tự đầu của A2, `=RIGHT(A2,2)` trả về 2 ký tự cuối, và `=MID(A2,5,4)` trả về 4 ký tự bắt đầu từ ký tự thứ 5. Kết hợp với FIND và LEN khi độ dài thay đổi.
- FIND và SEARCH`=SEARCH("apple",A2)` trả về vị trí bắt đầu của `apple` trong A2, không phân biệt hoa thường. FIND làm điều tương tự nhưng phân biệt chữ hoa chữ thường. Cả hai trả về #VALUE! khi không có văn bản, và ISNUMBER biến điều đó thành phép kiểm tra "ô có chứa".
- SUBSTITUTE, REPLACE`=SUBSTITUTE(A2,"-","")` xóa mọi dấu gạch ngang trong A2: SUBSTITUTE thay văn bản bằng cách khớp nội dung. REPLACE thay theo vị trí: `=REPLACE(A2,1,3,"XYZ")` ghi đè 3 ký tự đầu tiên.
- TRIM`=TRIM(A2)` xóa các khoảng trắng trước và sau văn bản trong A2, và gộp các chuỗi khoảng trắng giữa các từ thành một. SUBSTITUTE xóa mọi khoảng trắng hoặc khoảng trắng không ngắt mà TRIM bỏ sót.
- UPPER, LOWER, PROPER`=UPPER(A2)` viết hoa mọi chữ cái trong A2, `=LOWER(A2)` chuyển tất cả thành chữ thường, còn `=PROPER(A2)` viết hoa chữ cái đầu của mỗi từ. Để chỉ viết hoa chữ cái đầu của cả đoạn văn bản, kết hợp UPPER, LEFT và MID.
- LEN`=LEN(A2)` trả về số ký tự trong A2, tính cả dấu cách và dấu câu. Kết hợp với TRIM và SUBSTITUTE, nó còn đếm được số từ, và với SUM thì đếm được số ký tự của cả một vùng.
- TEXT`=TEXT(A2,"mmm d, yyyy")` biến ngày trong A2 thành văn bản như `Mar 15, 2026`, còn `=TEXT(B2,"$#,##0.00")` biến 1250.5 thành `$1,250.50`. Kết quả là văn bản, nên hãy dùng nó làm nhãn, không dùng để tính tiếp.
- Văn bản thành số`=VALUE(A2)` biến một số được lưu dưới dạng văn bản, như `'120`, thành số 120. Hai dấu trừ, `=--A2`, làm điều tương tự, NUMBERVALUE xử lý dấu phẩy thập phân, còn Convert to Number sửa ô ngay tại chỗ.
- Xuống dòng trong ôNhấn Alt+Enter khi đang gõ trong ô để xuống dòng ngay trong ô đó (trên Mac là Control+Option+Return). Trong công thức, `CHAR(10)` là ký tự xuống dòng: `=A2&CHAR(10)&B2` đưa B2 xuống dòng thứ hai, hiện ra khi bật Wrap Text.
- Số 0 ở đầuExcel bỏ số 0 ở đầu vì `00742` là số 742. Giữ lại chúng bằng định dạng số tùy chỉnh như `00000`, một dấu nháy đơn (`'00742`) hoặc định dạng Text, hay thêm vào bằng `=TEXT(A2,"00000")`.
- Ký tự đại diệnTrong điều kiện của Excel, `*` thay cho số ký tự bất kỳ và `?` thay cho đúng một ký tự: `=COUNTIF(A2:A7,"*apple*")` đếm các ô có chứa `apple`. `~` biến một ký tự đại diện trở lại thành ký tự thường.
Ngày và giờ
- Tính tuổi=DATEDIF(B2,TODAY(),"Y") trả về số tuổi tròn năm của người sinh vào ngày trong B2. Tính tuổi tại một ngày cụ thể, tính theo năm, tháng và ngày, và tính tuổi không cần DATEDIF.
- DATEDIF=DATEDIF(A2,B2,"M") đếm số tháng tròn giữa ngày bắt đầu trong A2 và ngày kết thúc trong B2. Các đơn vị Y, M, D, YM, MD và YD, lý do DATEDIF không có trong danh sách hàm, và lỗi #NUM!.
- Số ngày giữa hai ngày=B2-A2 trả về số ngày giữa ngày trong A2 và ngày muộn hơn trong B2. Đếm ngày bằng DAYS, tính cả hai ngày đầu cuối, hoặc lấy số tuần, tháng, năm hay ngày làm việc.
- Thứ trong tuần=TEXT(A2,"dddd") trả về tên thứ của ngày trong A2, như Monday, còn =WEEKDAY(A2) trả về thứ dưới dạng số. Tên viết tắt, các kiểu trả về của WEEKDAY và cách kiểm tra cuối tuần.
- TODAY và NOW=TODAY() trả về ngày hôm nay và =NOW() trả về ngày giờ hiện tại, cả hai đều cập nhật mỗi khi trang tính tính lại. Đếm số ngày còn lại tới một ngày, và chèn ngày cố định bằng Ctrl+;.
- Cộng ngày và tháng=A2+30 trả về ngày sau A2 đúng 30 ngày. Để cộng tháng, dùng =EDATE(A2,3); để lấy ngày cuối tháng, dùng =EOMONTH(A2,0); còn để cộng năm, dùng EDATE với 12 tháng cho mỗi năm.
- NETWORKDAYS và WORKDAY=NETWORKDAYS(A2,B2) đếm số ngày làm việc (Thứ Hai đến Thứ Sáu) từ A2 đến B2, tính cả hai ngày đầu và cuối. =WORKDAY(A2,10) trả về ngày cách A2 10 ngày làm việc. Cả hai hàm đều bỏ qua được một danh sách ngày nghỉ lễ.
- DATE, YEAR, MONTH, DAY=DATE(2026,3,15) trả về ngày 15 tháng 3 năm 2026 từ một năm, một tháng và một ngày. YEAR, MONTH và DAY tách một ngày ra thành từng phần, còn DATE tự chuyển tháng 13 sang năm sau.
- Tính thời gian=B2-A2 trả về khoảng thời gian giữa giờ bắt đầu ở A2 và giờ kết thúc ở B2: định dạng h:mm để thấy 8:30, hoặc nhân với 24 để có 8,5 giờ. Ca qua nửa đêm, tổng trên 24 giờ và tiền công theo giờ làm.
- Số tuần=WEEKNUM(A2) trả về số tuần của ngày trong A2, với tuần bắt đầu từ Chủ Nhật. =ISOWEEKNUM(A2) trả về số tuần ISO dùng ở châu Âu, nơi tuần bắt đầu từ Thứ Hai. Kèm cách lấy ngày đầu tuần và đổi số tuần thành ngày.
Toán học và thống kê
- ROUND=ROUND(A2,2) làm tròn số ở A2 đến hai chữ số thập phân, còn =ROUND(A2,0) làm tròn đến số nguyên gần nhất. Số chữ số âm làm tròn đến hàng chục, hàng trăm và hàng nghìn; MROUND làm tròn đến bội số bất kỳ.
- ROUNDUP / ROUNDDOWN=ROUNDUP(A2,0) luôn làm tròn ra xa số 0, nên 2,1 thành 3, còn =ROUNDDOWN(A2,0) luôn làm tròn về phía số 0, nên 2,9 thành 2. CEILING và FLOOR làm tròn lên hoặc xuống đến một bội số, INT và TRUNC bỏ phần thập phân.
- Độ lệch chuẩn=STDEV.S(B2:B9) cho độ lệch chuẩn của một mẫu, còn =STDEV.P(B2:B9) cho độ lệch chuẩn của cả tổng thể. Dùng STDEV.S trừ khi dữ liệu là mọi giá trị có thể có. VAR.S và VAR.P cho phương sai.
- RANK=RANK.EQ(B2,$B$2:$B$7) cho biết thứ hạng của B2 trong các giá trị ở B2:B7, giá trị lớn nhất xếp hạng 1. Thêm 1 làm đối số thứ ba để xếp giá trị nhỏ nhất lên đầu. Các giá trị bằng nhau có chung hạng; COUNTIFS xếp hạng trong từng nhóm.
- Số ngẫu nhiên=RANDBETWEEN(1,100) trả về một số nguyên ngẫu nhiên từ 1 đến 100, còn =RAND() trả về một số thập phân ngẫu nhiên từ 0 đến dưới 1. RANDARRAY lấp đầy cả một vùng, INDEX kết hợp RANDBETWEEN chọn ngẫu nhiên một mục, và Paste Special > Values giữ cố định kết quả.
- MOD và ABS=MOD(A2,B2) trả về số dư khi chia A2 cho B2, nên =MOD(17,5) bằng 2. =ABS(A2) trả về một số đã bỏ dấu, nên =ABS(B2-C2) là độ chênh lệch giữa hai giá trị, bất kể giá trị nào lớn hơn.
- PMT=PMT(B2/12,B3*12,-B1) trả về số tiền phải trả mỗi tháng cho khoản vay B1 với lãi suất năm ở B2 trong B3 năm. Chia lãi suất cho 12, nhân số năm với 12 và đặt dấu trừ trước số tiền vay để kết quả là số dương.
- NPV và IRR=NPV(E2,B3:B5)+B2 chiết khấu các dòng tiền tương lai theo lãi suất ở E2 rồi cộng khoản đầu tư ban đầu ở B2, khoản mà NPV không được chiết khấu. =IRR(B2:B5) trả về lãi suất làm NPV đó bằng 0. XNPV và XIRR dùng ngày thực tế.
- CAGR=(B2/A2)^(1/C2)-1 cho ra tốc độ tăng trưởng kép hằng năm từ giá trị đầu ở A2 đến giá trị cuối ở B2 trong C2 năm. =RRI(C2,A2,B2) trả về cùng tỷ lệ đó. Định dạng ô kết quả theo kiểu phần trăm.
Mảng động
- FILTER=FILTER(A2:C7,B2:B7="North") trả về mọi hàng của A2:C7 có khu vực là North, và kết quả tự cập nhật khi dữ liệu thay đổi. Học cách lọc nhiều điều kiện với * và +, if_empty, lỗi #CALC! và sắp xếp kết quả.
- UNIQUE=UNIQUE(B2:B8) trả về mỗi giá trị của B2:B8 một lần, theo thứ tự xuất hiện đầu tiên, và cập nhật khi danh sách thay đổi. Tìm hiểu hàng duy nhất, exactly_once, danh sách duy nhất đã sắp xếp, đếm giá trị duy nhất và dùng kết quả làm nguồn cho danh sách thả xuống.
- SORT và SORTBY=SORT(A2:C7,3,-1) trả về bảng A2:C7 được sắp xếp theo cột thứ ba, giá trị lớn nhất lên đầu, và tự sắp xếp lại khi dữ liệu thay đổi. SORTBY sắp xếp theo vùng bất kỳ, kể cả nhiều cột và một thứ tự tùy chỉnh.
- SEQUENCE=SEQUENCE(5) trả về các số từ 1 đến 5 theo một cột, còn =SEQUENCE(3,4) lấp đầy 3 hàng và 4 cột. Thêm giá trị bắt đầu và bước nhảy để tạo dãy bất kỳ, kể cả dãy ngày, số thứ tự tự tăng theo danh sách và lịch tháng.
- TRANSPOSE=TRANSPOSE(A1:D3) chuyển các hàng của A1:D3 thành cột và vẫn liên kết với dữ liệu nguồn. Để sao chép một lần, dùng Paste Special > Transpose. TOCOL xếp cả một lưới ô vào một cột.
- LET=LET(total,SUM(B2:B6),IF(total>500,total*0.9,total)) tính tổng một lần, đặt tên là total và dùng tên đó hai lần. LET giúp công thức dài ngắn hơn, dễ đọc hơn và nhanh hơn, vì mỗi phần được đặt tên chỉ tính một lần.
- LAMBDA=LAMBDA(price,price*1.2)(B2) định nghĩa một hàm nhỏ có một đầu vào là price và gọi nó trên B2. Lưu LAMBDA trong Name Manager để dùng như hàm có sẵn, hoặc truyền nó vào MAP, BYROW, SCAN và REDUCE.
Lỗi và cách khắc phục
- Lỗi #SPILL!#SPILL! nghĩa là một công thức trả về nhiều giá trị nhưng không có chỗ để đặt chúng: một ô trong vùng trả tràn không trống. Xóa các ô cản đường và kết quả sẽ xuất hiện.
- Lỗi #VALUE!#VALUE! nghĩa là công thức nhận sai loại giá trị, thường gặp nhất là văn bản ở chỗ cần số: =B2+C2 bị lỗi khi C2 chứa "n/a" hoặc một khoảng trắng. SUM bỏ qua văn bản, nên =SUM(B2:C2) vẫn chạy.
- Lỗi #NAME?#NAME? nghĩa là Excel không nhận ra một từ trong công thức: tên hàm gõ sai như =SUMM(B2:B6), văn bản thiếu dấu ngoặc kép, vùng thiếu dấu hai chấm, tên chưa được định nghĩa, hoặc hàm mà phiên bản Excel của bạn chưa có.
- Lỗi #REF!#REF! nghĩa là công thức tham chiếu đến một ô không còn tồn tại, thường do một hàng, cột hoặc trang tính mà công thức dùng đã bị xóa: =B2*C2 thành =B2*#REF!. Lỗi này cũng xuất hiện khi VLOOKUP hoặc INDEX yêu cầu một cột hay hàng nằm ngoài vùng.
- Lỗi #N/A#N/A nghĩa là một phép dò tìm không tìm thấy giá trị cần tìm. Hãy kiểm tra lỗi gõ, khoảng trắng thừa và vùng bảng bị trượt khi kéo công thức xuống, rồi dùng IFNA để hiện thông báo cho những giá trị thật sự không có.
- Lỗi #DIV/0!#DIV/0! xuất hiện khi một công thức chia cho 0 hoặc cho một ô trống, như =B2/C2 khi C2 trống. =IF(C2=0,"",B2/C2) hiển thị ô trống thay vào đó, và AVERAGE của một vùng không có số nào cũng trả về lỗi này.
- Tham chiếu vòngTham chiếu vòng là công thức tham chiếu tới chính ô của nó, trực tiếp hoặc thông qua các công thức khác, ví dụ =SUM(B2:B7) được gõ vào B7. Excel sẽ cảnh báo, hiện 0 và liệt kê ô đó trong Formulas > Error Checking > Circular References.
- Công thức không tính toánNếu Excel hiện công thức thay vì kết quả, ô đang được định dạng là Text, công thức bắt đầu bằng dấu nháy đơn hoặc dấu cách, hoặc Show Formulas đang bật. Nếu kết quả không cập nhật, chế độ tính toán đang là Manual: Formulas > Calculation Options > Automatic.
Công cụ dữ liệu
- Xóa dữ liệu trùngChọn dữ liệu rồi bấm Data > Remove Duplicates để xóa các hàng lặp lại ngay tại chỗ, hoặc dùng =UNIQUE(A2:A9) để có một bản sạch mà vẫn giữ bản gốc. Tìm, đánh dấu và đếm dữ liệu trùng, và xóa trùng theo hai cột.
- Tô màu dữ liệu trùngChọn các ô rồi vào Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Để tô cả hàng, chỉ tô bản sao thứ hai hoặc so khớp giữa hai cột, hãy dùng quy tắc công thức như =COUNTIF($A$2:$A$9,A2)>1.
- Định dạng có điều kiệnĐịnh dạng có điều kiện tô màu một ô khi một điều kiện đúng. Dùng Home > Conditional Formatting cho các quy tắc có sẵn, hoặc New Rule > Use a formula với quy tắc như =$C2>100 để tô màu cả hàng, ngày quá hạn và ô chứa văn bản cần tìm.
- Danh sách thả xuốngChọn các ô, vào Data > Data Validation, chọn List, rồi gõ các mục (North,South,East) hoặc chọn một vùng làm nguồn. Sau đó làm danh sách tự cập nhật với UNIQUE, phụ thuộc vào một danh sách khác, và tra cứu mục đã chọn.
- So sánh hai cộtĐể so sánh hai cột theo từng hàng, dùng =A2=B2 (hoặc EXACT nếu cần phân biệt hoa thường). Để tìm giá trị có ở cột này mà thiếu ở cột kia, dùng COUNTIF, MATCH hoặc XLOOKUP, rồi tô màu điểm khác biệt bằng định dạng có điều kiện.
- Pivot tablePivot table nhóm các hàng của một bảng theo danh mục và tính tổng một con số cho từng nhóm, không cần công thức: Insert > PivotTable, rồi kéo các trường vào Rows và Values. Dưới đây là các bước, giải thích bốn vùng và cách dựng cùng bảng tổng hợp bằng công thức.
