Excel cung cấp nhiều hàm mạnh mẽ nhưng ngay cả khi sử dụng chúng thường xuyên, những lỗi phổ biến nhất vẫn đến từ những chi tiết nhỏ:sử dụng chế độ khớp sai, so sánh văn bản đã định dạng thay vì giá trị hoặc cho rằng định dạng ô cũng làm tròn số cơ bản.
Trong hướng dẫn này, chúng ta sẽ thảo luận về 9 hàm Excel phổ biến thường được sử dụng không chính xác và chỉ cho bạn cách khắc phục chúng.
1. VLOOKUP – Ngừng sử dụng nó cho mọi thứ
Con đường sai lầm: Nhiều người dùng phụ thuộc vào VLOOKUP cho mọi thứ, ngay cả khi các chức năng khác phù hợp hơn. VLOOKUP chỉ tìm kiếm ở bên phải, yêu cầu dữ liệu được sắp xếp để khớp gần đúng và có thể bị hỏng khi các cột được chèn vào dữ liệu nguồn. Theo mặc định, VLOOKUP sử dụng kết quả khớp gần đúng (TRUE), yêu cầu cột đầu tiên được sắp xếp và thường trả về mục không chính xác nếu dữ liệu không được sắp xếp đúng cách.
Sửa: Sử dụng XLOOKUP với kết quả khớp chính xác trong Microsoft 365 hoặc INDEX+MATCH nếu bạn đang dùng phiên bản Excel cũ hơn.
Sử dụng INDEX/MATCH để linh hoạt hơn:
- Chọn một ô và chèn công thức sau để tra cứu giá của một sản phẩm cụ thể.
=INDEX(G2:G101, MATCH(L3, E2:E101, 0))
Điều này cũng xử lý việc “tra cứu bên trái” bằng cách chọn bất kỳ cột trả về nào bạn thích bên trong INDEX.
Đối với người dùng Excel 365, hãy xem xét XLOOKUP:
- Chọn một ô và chèn công thức sau.
=XLOOKUP(L3, E2:E101, G2:G101, "Not found", 0)
Số 0 buộc phải khớp chính xác và đối số thứ tư đưa ra thông báo thân thiện “không tìm thấy”.
- Hoạt động theo mọi hướng (trái hoặc phải).
- Không bị hỏng khi chèn cột.
- Hiệu quả hơn đối với tập dữ liệu lớn.
- Luồng logic rõ ràng hơn.
2. SUMIF/SUMIFS – Viết sai tiêu chí
Con đường sai lầm: Viết toán tử trực tiếp vào chuỗi tiêu chí (ví dụ:“>=2025-03-01” dưới dạng văn bản thuần túy) thay vì nối chúng, trộn lẫn các đối số Criteria_range và sum_range hoặc sử dụng toàn bộ tham chiếu cột như A:A, điều này có thể làm chậm quá trình tính toán.
Sửa: Giữ các phạm vi được căn chỉnh và xây dựng tiêu chí toán tử bằng cách nối; sử dụng DATE/EOMONTH để lọc ngày hiệu quả.
- Sử dụng các phạm vi cụ thể chứa dữ liệu thực tế của bạn vì nó nhanh hơn và chính xác hơn.
=SUMIF(D2:D101,"East",H2:H101)
- Tổng doanh số tháng 3 năm 2025 ở khu vực phía Tây:
=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 những bất ngờ về miền địa phương và các vấn đề về ngày tháng văn bản. Luôn đảm bảo sum_range và mỗi Criteria_range có cùng kích thước.
Mẹo chuyên nghiệp: Sử dụng Bảng Excel và tham chiếu có cấu trúc để tự động mở rộng phạm vi.
3. Câu lệnh IF – Cơn ác mộng lồng nhau
Con đường sai lầm: So sánh hàm boolean với TRUE một cách không cần thiết (ví dụ:=IF(AND(E4=”Y”,F4=”Y”)=TRUE, …)) hoặc tạo các câu lệnh IF lồng nhau sâu cho các bản đồ danh mục đơn giản, khó đọc và duy trì.
Sửa: Hãy để các hàm AND/OR trả về trực tiếp các giá trị boolean. Đối với nhiều điều kiện, hãy sử dụng IFS, CHOOSE/MATCH hoặc SWITCH để có công thức rõ ràng hơn, dễ quản lý hơn.
- Một bài kiểm tra boolean rõ ràng:
=IF(AND(D2="East", F2>=3), "Bulk East", "Other")
- Điểm đa điều kiện sử dụng IFS:
=IFS(F2:F101>=5, "High", F2:F101>=2, "Medium", TRUE, "Low")
- Bản đồ danh mục gọn gàng với CHỌN/MATCH (ví dụ:chuyển đổi điểm chữ cái trong 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 hơn và ít xảy ra lỗi hơn so với kim tự tháp IF sâu.
4. CONCATENATE – Cách tiếp cận lỗi thời
Con đường sai lầm: Vẫn sử dụng hàm CONCATENATE đã lỗi thời hoặc nối nhiều ký hiệu và nối chuỗi để nối văn bản phức tạp, việc này có thể rườm rà và dễ xảy ra lỗi.
Sửa: Sử dụng TEXTJOIN trong các phiên bản Excel hiện đại vì nó có thể nối nhiều giá trị một cách hiệu quả với một dấu phân cách được chỉ định.
- Sử dụng TEXTJOIN cho nhiều giá trị có dấu phân cách:
=IF(L2="","", TEXTJOIN(", ", TRUE, FILTER(E$2:E$101, C$2:C$101=L2)))
Sử dụng toán tử &để ghép nối đơn giản, nhưng TEXTJOIN sẽ tỏa sáng khi bạn cần:
- Tự động bỏ qua các ô trống.
- Sử dụng cùng một dấu phân cách.
- Nối một dải ô.
5. COUNTIF – Bỏ qua nhiều tiêu chí hiệu quả
Con đường sai lầm: Sử dụng COUNTIF với các toán tử theo nghĩa đen thay vì ghép chúng, mong đợi kết quả phân biệt chữ hoa chữ thường hoặc quên rằng các ký tự đại diện cần phải được đặt trong dấu ngoặc kép. Một lỗi phổ biến khác là tính tổng nhiều hàm COUNTIF thay vì sử dụng COUNTIFS hiệu quả hơn cho nhiều tiêu chí.
Sửa: Nối các toán tử một cách chính xác. Khi bạn cần phân biệt chữ hoa chữ thường hoặc logic "chứa" phức tạp hơn, hãy chuyển sang SUMPRODVEL hoặc FILTER.
- Đếm mã bắt đầu bằng US-E:
=COUNTIF(J2:J101, "US-E*")
- Đếm các đơn đặt hàng có sản phẩm chứa từ “điện thoại” (không phân biệt chữ hoa chữ thường):
=SUMPRODUCT(--ISNUMBER(SEARCH("phone", E2:E101)))
Nếu bạn cần "chứa" phân biệt chữ hoa chữ thường, hãy thay thế TÌM KIẾM bằng TÌM.
6. VÒNG – Làm tròn sai thời điểm
Con đường sai lầm: Giả sử rằng việc định dạng một ô thành hai chữ số thập phân cũng làm tròn giá trị cơ bản trong phép tính. Định dạng chỉ thay đổi hình thức chứ không thay đổi giá trị được lưu trữ, điều này có thể dẫn đến sự khác biệt về tổng số.
Sửa: Làm tròn ở bước mà các quy tắc kinh doanh yêu cầu. Sử dụng ROUND, ROUNDUP, ROUNDDOWN hoặc MROUND để tăng dần.
- Số 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 với định giá bằng tiền mặt):
Nguyên tắc chính: Làm tròn kết quả để hiển thị nhưng vẫn đảm bảo độ chính xác trong các phép tính trung gian trừ khi có yêu cầu cụ thể khác.
7. Định dạng VĂN BẢN và Ngày được Sử dụng trong Tính toán
Con đường sai lầm: Chuyển đổi các giá trị thành văn bản cho mục đích hiển thị và sau đó cố gắng sử dụng các kết quả dựa trên văn bản đó trong các phép tính toán học hoặc so sánh chuỗi ngày được định dạng với giá trị ngày thực. Điều này thường liên quan đến việc xử lý ngày tháng dưới dạng văn bản và sử dụng thao tác văn bản phức tạp thay vì các hàm ngày chuyên dụng.
Sửa: Thực hiện tính toán trên các giá trị số thô. Chỉ sử dụng chức năng TEXT ở bước trình bày cuối cùng, chẳng hạn như tiêu đề biểu đồ hoặc nhãn báo cáo.
- Sử dụng các hàm ngày thích hợp với các giá trị ngày thực tế:
=YEAR(A1) =MONTH(A1) =DAY(A1)
- Tiêu đề trang tổng quan hiển thị tháng và tổng số mà không làm hỏng các con số của bạn:
="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 mạnh mẽ bằng cách sử 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. TỔNG HỢP – Quên ép buộc hoặc khi LỌC rõ ràng hơn
Con đường sai lầm: Quên ép buộc các mảng boolean (TRUE/FALSE) thành các số (1/0) hoặc xây dựng các mảng có kích thước không khớp. Ngược lại, sử dụng công thức SUMPRODVEL phức tạp khi tổ hợp SUM(FILTER(…)) đơn giản trong Excel hiện đại sẽ dễ đọc hơn.
Sửa: Sử dụng đơn phân kép — hoặc nhân với 1 để buộc TRUE/FALSE thành 1/0. Trong Microsoft 365, hãy ưu tiên mẫu SUM và LỌC minh bạch cho các tổng có nhiều tiêu chí.
- Tổng doanh số bán hàng ở khu vực phía Đông, các sản phẩm có chứa “điện thoại” và số lượng ít nhất 3 sản phẩm thân thiện với di sản:
=SUMPRODUCT((D$2:D$101="East") * ISNUMBER(SEARCH("phone", E$2:E$101)) * (F$2:F$101>=3) * H$2:H$101)
- Logic tương tự 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 LỌC, các tiêu chí nhân với nhau thành cổng 1/0 và kết quả vẫn có thể đọc được.
9. IFERROR Được sử dụng làm băng dán chăn
Con đường sai lầm: Gói một công thức lớn, phức tạp trong hàm IFERROR(…,””) để loại bỏ tất cả lỗi. Điều này nguy hiểm vì nó có thể che giấu các vấn đề thực sự như tên dải sai chính tả, #DIV/0 chính hãng! lỗi hoặc sai sót logic khác mà bạn nên biết.
Sửa: Chỉ bắt lỗi cụ thể mà bạn mong đợi. Sử dụng IFNA cho các hàm tra cứu khi không tìm thấy giá trị và sử dụng câu lệnh IF để xử lý các ô trống trước khi thực hiện phép tính.
- Hiển thị trống nếu khóa tra cứu trống; nếu không, chỉ hiển thị thông báo "không tìm thấy" thân thiện khi nó thực sự bị thiếu:
=IF(L2="","", IFNA(XLOOKUP(L2, E$2:E$101, G$2:G$101),"No match"))
Trống:
Không khớp:
- Chỉ chuyển đổi văn bản thành số khi có nội dung cần chuyển đổi:
=IF(A2="", "", VALUE(A2))
Điều này bảo tồn các số 0 hợp lệ và tránh che giấu các lỗi không liên quan.
Tóm tắt các phương pháp hay nhất
- Chọn công cụ phù hợp: Đừng mặc định sử dụng các chức năng quen thuộc khi có các lựa chọn thay thế tốt hơn.
- Hãy cụ thể với các phạm vi: Tránh tham chiếu toàn bộ cột trừ khi cần thiết.
- Hãy nghĩ đến khả năng bảo trì: Viết công thức mà người khác (bao gồm cả bạn trong tương lai) có thể hiểu được.
- Sử dụng loại dữ liệu thích hợp: Hãy coi ngày là ngày, số là số.
- Các trường hợp thử nghiệm: Hãy xem điều gì sẽ xảy ra với các ô trống, lỗi hoặc dữ liệu không mong muốn.
- Tận dụng bảng Excel: Sử dụng tham chiếu có cấu trúc cho phạm vi động.
- Luôn cập nhật: Tìm hiểu các hàm mới trong Excel 365 có thể thay thế các công thức cũ phức tạp.
Kết luận
Bằng cách tránh những lỗi phổ biến này, bạn có thể tạo bảng tính Excel hiệu quả, dễ đọc và đáng tin cậy hơn. Điều quan trọng cần lưu ý là chọn hàm phù hợp cho công việc, chính xác với tiêu chí và phạm vi của bạn, đồng thời giữ riêng biệt các loại dữ liệu—thực hiện các phép tính trên số và chỉ sử dụng các hàm định dạng cho bản trình bày cuối cùng. Việc phá bỏ những thói quen cũ học được từ các hướng dẫn lỗi thời sẽ không chỉ giúp công thức của bạn ít mắc lỗi hơn mà còn giúp công việc của bạn dễ duy trì và dễ hiểu hơn.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!