Vì sao công thức Excel không tính ra kết quả mà hiện nguyên dòng chữ?

Do thiếu dấu bằng ở đầu công thức nên Excel coi nội dung là văn bản. Kiểm tra ô đó có dấu bằng chưa và định dạng ô có đang là Text không. Sửa nhanh: bấm F2 rồi Enter, hoặc chọn cả cột và dùng Data, Text to Columns, bấm Finish để chuyển về dạng số.

Vì sao SUM và AVERAGE ra kết quả sai dù nhìn cột dữ liệu vẫn đủ?

Vì hai hàm chỉ tính ô chứa số thật; ô trống và ô chứa chữ bị bỏ qua, nên AVERAGE chia cho ít ô hơn thực tế và ra trung bình cao hơn. Số lưu dạng chuỗi làm SUM bằng 0. Kiểm tra nhanh bằng cách so COUNT với COUNTA trên cùng vùng.

Lồng nhiều IF thì viết thế nào cho đúng thứ tự?

Kiểm tra mức cao nhất trước rồi hạ dần, vì Excel dừng ở điều kiện đúng đầu tiên. Xếp loại bốn mức thì nên xét từ 9 trở lên, rồi 8, rồi 6,5, mức cuối là giá trị mặc định. Đảo thứ tự là mọi trường hợp đều rơi vào điều kiện đầu.

VLOOKUP báo lỗi #N/A trong khi mã đã có trong bảng tra thì sửa thế nào?

Thường là dữ liệu khác nhau ở dạng ẩn: khoảng trắng thừa ở đầu hoặc cuối mã, một bên là số còn một bên là chuỗi số, hoặc ký tự ẩn dán từ web. Xử lý bằng TRIM và CLEAN, kiểm tra bằng LEN và ISNUMBER, rồi ép kiểu cho khớp khoá. Nhớ tham số cuối phải là 0.

Khi nào nên dùng IFERROR và khi nào không nên?

Nên dùng khi bảng đã đúng và chỉ cần trình bày sạch, ví dụ che #N/A của VLOOKUP hay #DIV/0! khi mẫu số bằng 0. Không dùng để giấu lỗi chưa hiểu nguyên nhân, vì IFERROR che mọi loại lỗi kể cả tham chiếu sai. Chỉ cần che lỗi tra cứu thì dùng IFNA.

← Quay lại Blog Thủ thuật văn phòng

Các hàm Excel cơ bản cho dân văn phòng: 7 hàm dùng hằng ngày

2026-09-20 · Tân IT365

Bảng tính Excel với các hàm SUM, AVERAGE, IF và VLOOKUP cùng kết quả

Phần lớn thời gian làm báo cáo mất ở những việc lặp đi lặp lại: cộng tay từng cột, đếm số dòng theo từng phòng, mở hai bảng ra dò từng mã hàng để điền giá. Bảy hàm Excel trong bài này xử lý gần hết những việc đó, và đều có sẵn trong mọi bản Excel trên máy tính.

Bài đi từ dễ tới khó: công thức và tham chiếu ô, SUM và AVERAGE, IF lồng nhau, COUNTIF và SUMIF, VLOOKUP, IFERROR, rồi ví dụ bảng chấm công dùng cả bảy hàm.

Minh bạch: bài viết có thể chứa liên kết tiếp thị. Nếu anh/chị mua dịch vụ qua các liên kết này, Tân IT365 có thể nhận một khoản hoa hồng nhỏ. Khoản này không làm tăng giá anh/chị phải trả và không chi phối nội dung hướng dẫn.

1. Công thức Excel hoạt động thế nào: dấu =, địa chỉ ô, tham chiếu tương đối và tuyệt đối

Mọi công thức trong Excel bắt đầu bằng dấu =. Gõ =1+1 rồi Enter, ô hiện 2; gõ thiếu dấu bằng thì Excel coi đó là chữ và ô hiện nguyên "1+1".

Địa chỉ ô cho Excel biết lấy dữ liệu ở đâu. Bốn kiểu cần phân biệt:

Một ô: B2 — cột B, dòng 2.
Một vùng: B2:B10.
Ô ở sheet khác: 'Bang luong'!C5 — tên sheet có dấu cách phải đặt trong dấu nháy đơn.
Toàn bộ một cột: B:B — tiện khi số dòng tăng, nhưng sẽ tính cả ô trống phía dưới.

Khác biệt quan trọng nhất nằm ở dấu $. Viết B2 là tham chiếu tương đối: copy xuống dòng dưới, Excel tự đổi thành B3, B4. Viết $B$2 là tham chiếu tuyệt đối: copy đi đâu vẫn trỏ đúng ô B2. Kiểu hỗn hợp $B2 và B$2 đã có trong bảng dưới. Bấm F4 liên tục khi đang ở trong địa chỉ ô để Excel xoay vòng bốn kiểu.

Kiểu tham chiếu	Ví dụ	Khi kéo công thức	Dùng cho
Tương đối	`B2`	Đổi cả cột và dòng	Công thức áp cho từng dòng dữ liệu
Tuyệt đối	`$B$2`	Không đổi	Ô hằng số: thuế suất, đơn giá, tỷ lệ
Hỗn hợp	`$B2` hoặc `B$2`	Chỉ đổi một chiều	Bảng hai chiều, bảng tra cứu

Phép tính dùng + - * / và luỹ thừa ^. Nhớ đặt ngoặc cho rõ: tiền hàng có VAT viết =B2*C2*(1+$F$1), vì =B2*C2+$F$1 sẽ cộng thẳng tỷ lệ vào số tiền.

Nên định dạng vùng dữ liệu thành Table (Ctrl+T) để công thức tự lan khi thêm dòng, và bấm Ctrl+` khi cần xem công thức thay vì kết quả.

2. SUM và AVERAGE: cộng và tính trung bình đúng cách

Hai hàm cơ bản nhất, cũng là hai hàm bị dùng sai nhiều nhất.

=SUM(B2:B10) — cộng toàn bộ dãy số.
=AVERAGE(B2:B10) — trung bình cộng các ô chứa số.
=SUM(B2,C2,E2) — cộng vài ô rời nhau, ngăn bằng dấu phẩy.

Thao tác nhanh: đặt con trỏ ở ô cần tổng dưới một cột số rồi bấm Alt+= — Excel tự đoán vùng.

Điểm cần hiểu rõ là cách hai hàm xử lý ô trống và ô chứa chữ:

Ô trống bị bỏ qua hoàn toàn. Cả hai hàm đều không tính ô trống, không coi là số 0.
Ô chứa chữ cũng bị bỏ qua. Nếu cột B có ô ghi "chưa nhập" thì AVERAGE chỉ chia cho số ô số, nên trung bình thường cao hơn thực tế — lý do nhiều bảng lương ra con số vô lý.
Ô trông như số nhưng là chữ thì không được cộng. Số có dấu nháy đơn phía trước ('100) hoặc dán từ phần mềm khác thường ở dạng văn bản. Dấu hiệu: tổng bằng 0 dù có dữ liệu.

Cách kiểm tra nhanh độ tin cậy của một cột, gõ cạnh bảng:

Ô kiểm tra	Công thức	Ý nghĩa
Đếm ô có số	`=COUNT(B2:B10)`	Cho biết đang có bao nhiêu giá trị số thật
Đếm ô có dữ liệu	`=COUNTA(B2:B10)`	Nếu lớn hơn COUNT thì trong cột có ô chứa chữ
Đếm ô trống	`=COUNTBLANK(B2:B10)`	Số dòng còn thiếu dữ liệu
Tổng khi đã lọc	`=SUBTOTAL(109,B2:B10)`	Chỉ cộng các dòng đang hiện, bỏ dòng bị lọc

Nếu COUNTA lớn hơn COUNT, đừng sửa từng ô: chọn cột đó rồi Data → Text to Columns → Finish, hoặc tạo cột phụ =VALUE(TRIM(B2)) và dán đè giá trị. Chặn dữ liệu bẩn từ đầu bằng Data Validation chỉ cho nhập số.

3. Hàm IF: điều kiện cơ bản và IF lồng nhiều lớp

IF là hàm ra quyết định: nếu điều kiện đúng thì trả về một giá trị, sai thì trả về giá trị khác.

=IF(điều kiện, giá trị khi đúng, giá trị khi sai)

Ví dụ một dòng dữ liệu ở ô B2 là điểm tổng kết:

=IF(B2>=8,"Đạt","Chưa đạt") — so sánh số, kết quả là chữ nên phải đặt trong dấu nháy kép.
=IF(B2="","Chưa chấm","Đã chấm") — kiểm tra ô trống.
=IF(C2="Nam","Anh","Chị") — so sánh chữ, không phân biệt chữ hoa chữ thường.
=IF(AND(B2>=8,C2>=8),"Đạt cả hai","Xét lại") — ghép hai điều kiện bằng AND (đúng khi tất cả đúng) hoặc OR (đúng khi một điều kiện đúng).

Toán tử so sánh: =, >, <, >=, <=, <> (khác). Chú ý Excel không hiểu !=.

Khi cần chia thành nhiều mức, ta lồng IF vào phần "sai". Bảng xếp loại bốn mức:

Mức	Điều kiện	Công thức lồng đầy đủ
Xuất sắc	Từ 9 trở lên	`=IF(B2>=9,"Xuất sắc",IF(B2>=8,"Giỏi",IF(B2>=6.5,"Khá","Chưa đạt")))`
Giỏi	Từ 8 đến dưới 9
Khá	Từ 6,5 đến dưới 8
Chưa đạt	Dưới 6,5

Nguyên tắc số một khi lồng IF: kiểm tra từ mức cao nhất xuống, vì Excel dừng ở điều kiện đúng đầu tiên. Viết =IF(B2>=6.5,"Khá",IF(B2>=8,"Giỏi",...)) thì người 9 điểm cũng bị xếp "Khá" — điều kiện sau không bao giờ được xét.

Hai lỗi hay gặp khác:

Thiếu ngoặc đóng. Mỗi IF cần một ngoặc đóng; Excel tô màu ngoặc khi gõ để dễ đối chiếu.
So sánh số với chữ. B2>=8 với ô chứa chữ "8" luôn sai, vì Excel xếp chữ lớn hơn mọi số. Kiểm tra cột đó bằng =ISNUMBER(B2).

Từ Excel 2019 có thêm IFS: =IFS(B2>=9,"Xuất sắc",B2>=8,"Giỏi",B2>=6.5,"Khá",TRUE,"Chưa đạt") — dễ đọc hơn IF lồng nhưng không chạy trên Excel 2013, 2016.

4. COUNTIF và SUMIF: đếm và cộng theo điều kiện

Hai hàm này thay cho việc lọc rồi đếm bằng mắt — việc tốn thời gian nhất khi tổng hợp báo cáo.

=COUNTIF(vùng đếm, điều kiện) — đếm số ô khớp điều kiện.
=SUMIF(vùng điều kiện, điều kiện, vùng cần cộng) — chú ý điều kiện ở giữa, khác thứ tự của SUM.

Ví dụ với cột C ghi phòng ban, D ghi ngày công, G ghi thực lĩnh:

Công thức	Việc nó làm
`=COUNTIF(C2:C50,"Kế toán")`	Đếm nhân viên phòng Kế toán
`=SUMIF(C2:C50,"Kế toán",G2:G50)`	Tổng thực lĩnh của phòng Kế toán
`=COUNTIF(D2:D50,">=24")`	Đếm người làm đủ 24 công trở lên
`=SUMIF(D2:D50,"<20",G2:G50)`	Tổng tiền của nhóm thiếu công

Dấu * là ký tự đại diện trong điều kiện, dùng khi chỉ nhớ một phần giá trị:

"Hà*" — mọi chuỗi bắt đầu bằng "Hà": Hà, Hải, Hạnh.
"*2026*" — chuỗi có chứa "2026" ở bất kỳ vị trí nào, ví dụ mã chứng từ.
"?A" — dấu ? thay cho đúng một ký tự: BA, CA, DA khớp, AA không khớp.
"<>" — đếm ô khác rỗng; "" — đếm ô rỗng.

Ba điều bắt buộc nhớ để không ra kết quả sai:

Vùng đếm và vùng cộng phải cùng kích thước. SUMIF khoá theo ô đầu tiên, nên SUMIF(C2:C50,"Kế toán",G1:G49) vẫn chạy nhưng sai trong im lặng.
Điều kiện dạng số phải để trong ngoặc kép hoặc nối bằng &: ">="&E1 để lấy ngưỡng từ ô E1.
Ô trống không khớp "*". Đếm ô thật có dữ liệu thì dùng "<>" hoặc COUNTA.

Khi cần nhiều điều kiện, thêm chữ S: =COUNTIFS(C2:C50,"Kế toán",D2:D50,">=24") và =SUMIFS(G2:G50,C2:C50,"Kế toán",D2:D50,">=24"). SUMIFS đảo cấu trúc so với SUMIF: vùng cộng đứng đầu, rồi mới đến các cặp vùng và điều kiện — chép công thức mẫu mà không để ý điểm này là nguyên nhân phổ biến của lỗi #VALUE! hoặc kết quả luôn bằng 0.

Cần tổng quan theo mọi nhóm trong một lần thì dùng Insert → PivotTable; COUNTIF và SUMIF phù hợp hơn khi báo cáo cần cố định, tự cập nhật.

5. VLOOKUP: tra cứu giữa hai bảng và các lỗi #N/A thường gặp

VLOOKUP giải bài toán quen thuộc: bảng chính chỉ có mã hàng, còn tên hàng và đơn giá nằm ở bảng phụ.

=VLOOKUP(giá trị cần tìm, bảng tra, số thứ tự cột cần lấy, kiểu tra)

Giả sử bảng tra ở $B$2:$D$20, cột B là mã hàng, C tên hàng, D đơn giá. Công thức ở ô E2: =VLOOKUP(A2,$B$2:$D$20,3,0) — tìm mã ở A2 và lấy giá trị ở cột thứ ba.

Tham số	Cần điền	Sai thì ra sao
Bảng tra	Khoá bằng `$`: `$B$2:$D$20`	Không khoá thì kéo xuống là vùng tra trôi, #N/A hàng loạt
Số thứ tự cột	Đếm từ cột đầu của bảng tra, tính cả cột khoá	Đếm nhầm là lấy sai dữ liệu; vượt số cột báo #REF!
Kiểu tra	`0` = tra chính xác	Để trống hoặc 1 là tra gần đúng, sai với bảng không sắp xếp

Điều kiện bắt buộc: cột khoá phải nằm ở cột trái nhất của vùng tra — VLOOKUP chỉ tìm từ trái sang phải, không tìm ngược. Nếu dữ liệu gốc xếp ngược, dùng INDEX + MATCH hoặc XLOOKUP() trên Excel 2021.

Lỗi #N/A nghĩa là "không tìm thấy". Nguyên nhân theo thứ tự hay gặp:

Khoảng trắng thừa ở đầu, cuối hoặc giữa mã — ô nhìn giống nhau nhưng khác dữ liệu. Xử lý: VLOOKUP(TRIM(A2),...), kiểm tra độ dài bằng =LEN(B2).
Một bên là số, một bên là chữ. Mã "0012" không khớp số 12. Kiểm tra bằng =ISNUMBER(A2) rồi ép kiểu bằng VALUE hoặc nối thêm &"".
Ký tự ẩn dán từ web: loại bằng =CLEAN(TRIM(A2)).

Với bảng danh mục, tham số cuối luôn là 0; chỉ dùng 1 cho bài toán thang bậc như tra thuế suất, và khi đó bảng buộc phải sắp xếp tăng dần.

6. IFERROR: che lỗi #N/A, #DIV/0! cho báo cáo sạch

Bảng báo cáo còn vài ô #N/A, #DIV/0! hay #VALUE! là bảng chưa in được. IFERROR xử lý việc này trong một câu lệnh.

=IFERROR(công thức, giá trị hiện khi lỗi)

Ghép với ba tình huống thường gặp:

Tra cứu không có kết quả: =IFERROR(VLOOKUP(A2,$B$2:$D$20,3,0),"Không có trong danh mục").
Chia cho ô trống hoặc ô 0: =IFERROR(G2/D2,0) trả về 0 thay vì #DIV/0! khi ngày công bằng 0.
Nối dữ liệu sai kiểu: =IFERROR(VALUE(B2),"Sai định dạng") khi ô cần đổi thành số nhưng chứa chữ.

Nhưng chỉ nên dùng IFERROR sau khi đã hiểu mình che lỗi gì. Ba lưu ý thực tế:

IFERROR che mọi loại lỗi. Công thức gốc trỏ nhầm cột thì IFERROR giấu luôn lỗi đó, bảng nhìn "sạch" nhưng sai — hãy tạm xoá IFERROR để xem công thức gốc ra gì.
Có hàm che riêng lỗi #N/A. =IFNA(công thức,"Không tìm thấy") chỉ che lỗi tra cứu, còn #DIV/0! hay #REF! vẫn hiện để biết có vấn đề thật.
Che lỗi không đồng nghĩa đã đúng. Nếu số ô ra giá trị thay thế nhiều bất thường, đặt ngay ô kiểm tra =COUNTIF(E2:E100,"Không có trong danh mục") để biết có bao nhiêu mã đang lệch.

Lỗi hiện trên ô	Nghĩa là	Cách xử lý gọn
`#N/A`	Không tìm thấy giá trị tra	Sửa khoá bằng TRIM/CLEAN, rồi bọc IFNA
`#DIV/0!`	Chia cho 0 hoặc ô trống	`=IF(D2=0,"",G2/D2)`
`#VALUE!`	Sai kiểu dữ liệu trong phép tính	Kiểm tra bằng ISNUMBER, ép kiểu bằng VALUE

Mẹo trình bày: trả về chuỗi rỗng "" khi bảng cần in gọn mắt, còn báo cáo gửi người khác đọc số thì trả về "Không có dữ liệu" để không ai hiểu nhầm ô trống là chưa nhập.

Cuối cùng: IFERROR bọc ngoài, IF và VLOOKUP lồng trong là được; không viết =SUM(IFERROR(...)) vì SUMIFS gọn và ổn định hơn.

7. Ví dụ thực hành: bảng chấm công dùng cả 7 hàm

Ghép cả bảy hàm vào một bảng chấm công tự cập nhật mỗi tháng.

Bước 1 — dựng bảng dữ liệu, đặt tên cột ở dòng 1:

~~~text | Mã NV | Họ tên | Phòng | Ngày công | Hệ số | Lương CB | Thực lĩnh | Xếp loại | |-------|--------|-------|-----------|-------|----------|-----------|----------| | NV01 | Nguyễn A | Kế toán | 24 | 1.0 | 6.000.000 | | | | NV02 | Trần B | Kỹ thuật | 26 | 1.2 | 8.000.000 | | | | NV03 | Lê C | Kinh doanh | 22 | 1.1 | 7.000.000 | | | ~~~

Bảng tra phụ đặt ở vùng J1:K4, cột J là tên phòng, cột K là hệ số.

Bước 2 — điền công thức từ trái sang phải:

Hệ số: =VLOOKUP(C2,$J$2:$K$4,2,0) — khoá vùng bằng $ để kéo xuống không trôi.
Thực lĩnh: =ROUND(D2*F2/26*E2,0).
Xếp loại: =IF(D2>=26,"Vượt",IF(D2>=24,"Đủ công",IF(D2>=20,"Thiếu nhẹ","Thiếu nhiều"))).
Che lỗi hệ số: =IFNA(VLOOKUP(C2,$J$2:$K$4,2,0),"Chưa khai hệ số").

Bước 3 — khối tổng hợp dưới bảng:

~~~text | Việc cần tính | Công thức | |---------------|-----------| | Tổng ngày công | =SUM(D2:D31) | | Ngày công trung bình | =AVERAGE(D2:D31) | | Số người đủ công | =COUNTIF(D2:D31,">=24") | | Số người phòng Kinh doanh | =COUNTIF(C2:C31,"Kinh doanh") | | Tổng thực lĩnh Kinh doanh | =SUMIF(C2:C31,"Kinh doanh",G2:G31) | | Lương bình quân Kinh doanh | =IFERROR(SUMIF(C2:C31,"Kinh doanh",G2:G31)/COUNTIF(C2:C31,"Kinh doanh"),0) | ~~~

Bước 4 — kiểm tra trước khi in: tổng =SUM(G2:G31) phải bằng tổng các SUMIF theo từng phòng cộng lại; so =COUNT(C2:C31) với =COUNTA(C2:C31) để biết cột có ô chứa chữ; bấm Ctrl+` rà lại xem có ô nào nhảy lệch không rồi bấm lần nữa để hiện kết quả.

Ba lỗi hay gặp: nhập "24 ngày" vào cột ngày công khiến SUM trả về 0; thêm dòng mới mà không sao chép công thức, nên định dạng vùng thành Table bằng Ctrl+T để công thức tự lan; và khoá vùng sai — bảng tra luôn khoá $J$2:$K$4, vùng dữ liệu thì không khoá.

Khi đã quen bảy hàm này, việc dựng file theo dõi công việc hay đối chiếu hoá đơn chỉ còn là gõ lại cùng bộ công thức với tên cột khác, và nên ghi chú lại trong một sheet "Ghi chú" của file.

Bài viết liên quan