Excel cung cấp nhiều hàm mạnh mẽ, nhưng ngay cả khi dùng thường xuyên, sai lầm phổ biến nhất đến từ chi tiết nhỏ: dùng sai match mode, so sánh văn bản đã định dạng thay vì giá trị, hoặc giả định định dạng ô cũng làm tròn số bên dưới.
Trong hướng dẫn này, chúng tôi sẽ thảo luận 9 hàm Excel thường bị dùng sai và chỉ cách khắc phục.
1. VLOOKUP – Đừng Dùng Nó Cho Mọi Việc
Cách Sai: Nhiều người dùng phụ thuộc VLOOKUP cho mọi thứ, ngay cả khi hàm khác phù hợp hơn. VLOOKUP chỉ tìm bên phải, yêu cầu dữ liệu sắp xếp cho khớp xấp xỉ, và có thể vỡ khi chèn cột vào dữ liệu nguồn. Mặc định VLOOKUP dùng khớp xấp xỉ (TRUE), yêu cầu cột đầu tiên được sắp xếp và thường trả về mục sai nếu dữ liệu không sắp xếp đúng.
Sửa: Dùng XLOOKUP với khớp chính xác trong Microsoft 365, hoặc INDEX+MATCH nếu dùng Excel cũ.
Dùng INDEX/MATCH Cho Linh Hoạt Hơn:
- Chọn ô và chèn công thức sau để tra giá sản phẩm cụ thể.
=INDEX(G2:G101, MATCH(L3, E2:E101, 0))
Điều này cũng xử lý "tra cứu trái" bằng cách chọn bất kỳ cột trả về nào trong INDEX.
Dành Cho Người Dùng Excel 365, Xem Xét XLOOKUP:
- Chọn ô và chèn công thức sau.
=XLOOKUP(L3, E2:E101, G2:G101, "Not found", 0)
Tham số 0 ép khớp chính xác, và tham số thứ tư cho thông báo "không tìm thấy" thân thiện.
- Hoạt động mọi hướng (trái hoặc phải).
- Không vỡ khi chèn cột.
- Hiệu quả hơn cho tập dữ liệu lớn.
- Luồng logic rõ ràng hơn.
2. SUMIF/SUMIFS – Viết Tiêu Chí Sai
Cách Sai: Viết toán tử trực tiếp vào chuỗi tiêu chí (ví dụ: ">=2025-03-01" như văn bản thuần) thay vì nối chuỗi, trộn lẫn criteria_range và sum_range, hoặc dùng tham chiếu toàn cột như A:A, làm chậm tính toán.
Sửa: Giữ phạm vi căn chỉnh, và xây tiêu chí toán tử bằng nối chuỗi; dùng DATE/EOMONTH cho bộ lọc ngày vững chắc.
- Dùng phạm vi cụ thể chứa dữ liệu thực, vì nhanh và chính xác hơn.
=SUMIF(D2:D101,"East",H2:H101)
- Tổng bán hàng tháng 3/2025 khu vực West:
=SUMIFS( H2:H101, D2:D101, "West", A2:A101, ">=" & DATE(2025,3,1), A2:A101, "<=" & EOMONTH(DATE(2025,3,1),0) )
Điều này tránh惊喜 locale và vấn đề date-text. Luôn đảm bảo sum_range và mỗi criteria_range cùng kích thước.
Mẹo Pro: Dùng Excel Tables và tham chiếu có cấu trúc cho mở rộng phạm vi tự động.
3. Câu Lệnh IF – Ác Mộng Lồng Nhau
Cách Sai: So sánh hàm boolean với TRUE không cần thiết (ví dụ: =IF(AND(E4="Y",F4="Y")=TRUE, …)) hoặc tạo IF lồng nhau sâu cho bản đồ danh mục đơn giản, khó đọc và bảo trì.
Sửa: Để AND/OR trả về boolean trực tiếp. Cho nhiều điều kiện, dùng IFS, CHOOSE/MATCH, hoặc SWITCH cho công thức sạch, dễ quản lý hơn.
- Kiểm tra boolean sạch:
=IF(AND(D2="East", F2>=3), "Bulk East", "Other")
- Đa điều kiện điểm dùng IFS:
=IFS(F2:F101>=5, "High", F2:F101>=2, "Medium", TRUE, "Low")
- Bản đồ danh mục gọn với CHOOSE/MATCH (ví dụ: chuyển xếp loại chữ J2 thành điểm GPA):
=CHOOSE(MATCH(J2, {"A","B","C","D","F"}, 0), 4,3,2,1,0)
Điều này đơn giản và ít lỗi hơn kim tự tháp IF sâu.
4. CONCATENATE – Cách Tiếp Cận Lỗi Thời
Cách Sai: Vẫn dùng hàm CONCATENATE lỗi thời hoặc nối nhiều & cho nối văn bản phức tạp, cồng kềnh và dễ lỗi.
Sửa: Dùng TEXTJOIN trong Excel hiện đại, vì nó nối hiệu quả nhiều giá trị với dấu phân cách chỉ định.
- Dùng TEXTJOIN cho nhiều giá trị với dấu phân cách:
=IF(L2="","", TEXTJOIN(", ", TRUE, FILTER(E$2:E$101, C$2:C$101=L2)))
Dùng toán tử & cho nối đơn giản, nhưng TEXTJOIN tỏa sáng khi bạn cần:
- Bỏ qua ô trống tự động.
- Dùng cùng dấu phân cách xuyên suốt.
- Nối một phạm vi ô.
5. COUNTIF – Bỏ Qua Hiệu Quả Nhiều Tiêu Chí
Cách Sai: Dùng COUNTIF với toán tử nguyên văn thay vì nối chuỗi, kỳ vọng kết quả phân biệt chữ hoa/thường sai, hoặc quên wildcard cần trong ngoặc kép. Sai lầm phổ biến khác là cộng nhiều COUNTIF thay vì dùng COUNTIFS hiệu quả hơn cho nhiều tiêu chí.
Sửa: Nối toán tử đúng. Khi cần phân biệt chữ hoa/thường hoặc logic "chứa" phức tạp hơn, chuyển sang SUMPRODUCT hoặc FILTER.
- Đếm mã bắt đầu bằng US-E:
=COUNTIF(J2:J101, "US-E*")
- Đếm đơn hàng sản phẩm chứa từ "phone" (không phân biệt hoa thường):
=SUMPRODUCT(--ISNUMBER(SEARCH("phone", E2:E101)))
Nếu cần "chứa" phân biệt hoa thường, thay SEARCH bằng FIND.
6. ROUND – Làm Tròn Sai Thời Điểm
Cách Sai: Giả định định dạng ô 2 chữ số thập phân cũng làm tròn giá trị bên dưới trong tính toán. Định dạng chỉ thay đổi hình thức, không phải giá trị lưu trữ, dẫn đến chênh lệch trong tổng.
Sửa: Làm tròn ở bước quy tắc kinh doanh yêu cầu. Dùng ROUND, ROUNDUP, ROUNDDOWN, hoặc MROUND cho bước nhảy.
- Số tiền dòng đã làm tròn:
=ROUND(F2:F101*G2:G101, 2)
- Làm tròn đến 0.05 gần nhất (phổ biến giá tiền mặt):
Nguyên tắc cốt lõi: Làm tròn kết quả để hiển thị, nhưng giữ độ chính xác trong tính toán trung gian trừ khi quy tắc yêu cầu khác.
7. TEXT Và Định Dạng Ngày Dùng Trong Tính Toán
Cách Sai: Chuyển giá trị thành văn bản để hiển thị rồi cố dùng kết quả văn bản đó trong tính toán toán học, hoặc so sánh chuỗi ngày đã định dạng với giá trị ngày thực. Điều này thường liên quan đến xem ngày như văn bản và dùng thao tác văn bản phức tạp thay vì hàm ngày chuyên dụng.
Sửa: Thực hiện tính toán trên giá trị số thô. Dùng hàm TEXT chỉ ở bước trình bày cuối cùng, như tiêu đề biểu đồ hoặc nhãn báo cáo.
- Dùng hàm ngày đúng với giá trị ngày thực:
=YEAR(A1) =MONTH(A1) =DAY(A1)
- Tiêu đề dashboard hiển thị tháng và tổng mà không làm hỏng số:
="March " & YEAR(DATE(2025,3,1)) & " Sales: " & TEXT(SUMIFS(H$2:H$101, A$2:A$101, ">="&DATE(2025,3,1), A$2:A$101, "<="&EOMONTH(DATE(2025,3,1),0)),"$#,##0")
- Bộ lọc tháng vững chắc dùng ngày (không cần TEXT):
=SUMIFS(H$2:H$101, D$2:D$101, "East", A$2:A$101, ">="&DATE(2025,3,1), A$2:A$101, "<="&EOMONTH(DATE(2025,3,1),0))
8. SUMPRODUCT – Quên Ép Buộc Hoặc Khi FILTER Rõ Hơn
Cách Sai: Quên ép buộc mảng boolean (TRUE/FALSE) thành số (1/0), hoặc xây mảng kích thước không khớp. Ngược lại, dùng SUMPRODUCT phức tạp khi SUM(FILTER(…)) đơn giản trong Excel hiện đại dễ đọc hơn.
Sửa: Dùng unary kép — hoặc nhân 1 để ép TRUE/FALSE thành 1/0. Trong Microsoft 365, ưu tiên mẫu SUM và FILTER minh bạch cho tổng nhiều tiêu chí.
- Tổng bán hàng khu vực East, sản phẩm chứa "phone", và số lượng ít nhất 3 (tương thích cũ):
=SUMPRODUCT((D$2:D$101="East") * ISNUMBER(SEARCH("phone", E$2:E$101)) * (F$2:F$101>=3) * H$2:H$101)
- Cùng logic như mảng động (365/2021):
=SUM(FILTER(H$2:H$101, (D$2:D$101="East")*(ISNUMBER(SEARCH("phone", E$2:E$101)))*(F$2:F$101>=3)) )
Với FILTER, tiêu chí nhân nhau như cổng 1/0, và kết quả dễ đọc.
9. IFERROR Dùng Như Cái Băng Keo Che Khuyết Tật
Cách Sai: Bọc một công thức lớn, phức tạp trong IFERROR(…,"") để chặn tất cả lỗi. Điều này nguy hiểm vì nó có thể che giấu vấn đề thực như tên phạm vi sai chính tả, #DIV/0! thực sự, hoặc khuyết tật logic khác bạn nên biết.
Sửa: Chỉ bắt lỗi cụ thể bạn mong đợi. Dùng IFNA cho hàm tra cứu khi giá trị không tìm thấy, và dùng IF để xử lý ô trống trước khi tính toán.
- Hiện trống nếu khóa tra cứu trống; ngược lại, chỉ hiện thông báo "không tìm thấy" thân thiện khi thực sự thiếu:
=IF(L2="","", IFNA(XLOOKUP(L2, E$2:E$101, G$2:G$101),"No match"))
Trống:
Không Khớp:
- Chuyển văn bản thành số chỉ khi có gì để chuyển:
=IF(A2="", "", VALUE(A2))
Điều này bảo tồn số 0 hợp pháp và tránh che lấp lỗi không liên quan.
Tóm Tắt Best Practice
- Chọn đúng công cụ: Đừng mặc định dùng hàm quen thuộc khi có lựa chọn tốt hơn.
- Cụ thể với phạm vi: Tránh tham chiếu toàn cột trừ khi cần thiết.
- Tưởng tượng khả năng bảo trì: Viết công thức người khác (kể cả bạn tương lai) hiểu được.
- Dùng kiểu dữ liệu đúng: Xử lý ngày như ngày, số như số.
- Test trường hợp biên: Xem xét ô trống, lỗi, dữ liệu bất ngờ.
- Tận dụng Excel Tables: Dùng tham chiếu có cấu trúc cho phạm vi động.
- Cập nhật kiến thức: Học hàm mới Excel 365 thay thế công thức legacy phức tạp.
Kết Luận
Bằng cách tránh những sai lầm phổ biến này, bạn có thể tạo spreadsheet hiệu quả, dễ đọc và tin cậy hơn. Những điểm chính là chọn đúng hàm cho việc, chính xác với tiêu chí và phạm vi, và tách biệt kiểu dữ liệu—tính toán trên số và dùng hàm định dạng chỉ cho trình bày cuối cùng. Việc phá vỡ thói quen cũ từ tutorial lỗi thời không chỉ làm công thức ít lỗi hơn mà còn làm công việc dễ bảo trì và hiểu hơn.
Nhận MIỄN PHÍ Bài Tập Excel Nâng Cao Có Giải Pháp!