Đánh giá lequocthai.com:
1. Hàm SUM “ma”: Khi các hàng ẩn vẫn bị tính toán
Bạn đã kiểm tra phép tính nhiều lần nhưng tổng số vẫn sai. Nguyên nhân thường gặp là hàm SUM của Excel cộng tất cả các ô trong một phạm vi, ngay cả khi bạn đã ẩn các hàng thủ công hoặc lọc chúng ra.
Trong ví dụ này, nếu sau khi ẩn các hàng từ 5 đến 10, bạn nhập:
=SUM(A1:A15)
công thức vẫn trả về 120, mặc dù tổng của các ô hiển thị chỉ là 75.
Dữ liệu “ma” này rất khó phát hiện. Thay vào đó, hãy sử dụng hàm SUBTOTAL:
=SUBTOTAL(109,A1:A15)
Số 109 trong đối số đầu tiên báo cho Excel thực hiện phép cộng và bỏ qua mọi hàng bị ẩn—dù do bộ lọc hay thủ công. Nhóm mã hàm từ 101-111 (như 109) đều có tính năng này.
2. Bộ lọc vô hình: Khi biểu tượng phễu thất bại
Thông thường, khi lọc dữ liệu trong bảng Excel, bạn sẽ thấy biểu tượng phễu ở tiêu đề cột.
Tuy nhiên, biểu tượng này đôi khi biến mất—thường là khi bạn dùng công cụ Advanced Filter (Lọc Nâng cao). Excel tưởng rằng nó đang giúp bạn giữ giao diện sạch sẽ, nhưng thực tế lại khiến bạn không biết dữ liệu đang bị thiếu.
Lúc này, hãy nhìn vào số thứ tự hàng bên trái. Nếu dãy số bị đứt quãng và có màu xanh lam, nghĩa là một bộ lọc đang hoạt động.
Để khắc phục, trong tab Data (Dữ liệu), nhấp vào Clear (Xóa). Thao tác này sẽ xóa mọi bộ lọc trong trang tính và khôi phục toàn bộ dữ liệu.
3. Kiểu dữ liệu hỗn hợp: Số được lưu dưới dạng văn bản
Một ô trông giống số nhưng hoạt động như văn bản là lỗi nguy hiểm nhất trong Excel. Nếu bạn tính tổng một cột có số lưu dưới dạng văn bản, Excel sẽ coi chúng bằng 0, dẫn đến kết quả sai.
Theo mặc định, Excel căn phải số và căn trái văn bản. Nếu giá trị của bạn dính sát lề trái, có thể chúng không phải là số. Bạn có thể thấy hình tam giác lỗi nhỏ màu xanh lá ở góc ô, nhưng nếu tính năng này bị tắt, bạn sẽ không hề hay biết.
Để sửa, với các ô được chọn, nhấp vào General (Chung) trong menu Number Format (Định dạng Số) của tab Home (Trang chủ).
Nếu chúng vẫn căn trái, hãy nhấp vào Text to Columns (Văn bản thành Cột) trong tab Data (Dữ liệu), rồi nhấp Finish (Hoàn tất).
Excel sẽ đánh giá lại và chuyển đổi văn bản thành số. Bây giờ, Status Bar (Thanh Trạng thái) sẽ hiển thị Average (Trung bình) và Sum (Tổng).
4. Khoảng trắng “ma” trong đếm và tra cứu
Một tình huống khó chịu là khi phép đếm hoặc tra cứu trả về kết quả sai mà không rõ nguyên nhân.
Giả sử bạn muốn đếm số lần “Cap” xuất hiện trong cột Product. Bạn dùng công thức:
=COUNTIF(T_Sales[Product],M1)
Tuy nhiên, dù có bốn mục “cap”, công thức chỉ trả về 3. Lý do có thể là một khoảng trắng vô hình ở cuối từ “Cap” trong một ô. “Cap ” (có dấu cách) khác với “Cap” (không dấu cách).
Để làm sạch dữ liệu mà không ảnh hưởng khoảng trắng giữa các từ (như “Rubik’s Cube”), dùng hàm TRIM trong một cột phụ:
=TRIM([@Product])
Hàm này loại bỏ khoảng trắng thừa ở đầu và cuối. Sau đó, bạn có thể chọn dữ liệu mới, dán dưới dạng giá trị (Ctrl+Shift+V) vào cột gốc và xóa cột phụ. Công thức lúc này sẽ đếm chính xác.
Nếu thường xuyên xử lý dữ liệu lộn xộn, hãy dùng Power Query. Sau khi chuyển đổi dữ liệu thành bảng, nhấp From Table/Range (Từ Bảng/Dải ô) trong tab Data (Dữ liệu). Trong Power Query Editor, nhấp chuột phải vào tiêu đề cột và chọn Transform > Trim (Chuyển đổi > Cắt bớt). Excel sẽ tự động loại bỏ khoảng trắng thừa mỗi khi bạn nhấp Refresh (Làm mới).
5. Hàm biến động và tính toán thủ công
Excel có thể chạy rất chậm nếu phải tính toán lại quá nhiều. Nguyên nhân thường do các hàm biến động như OFFSET, INDIRECT và TODAY. Không như hàm thông thường, chúng tính toán lại mỗi khi có bất kỳ thay đổi nào trong workbook.
Để giảm độ trễ, bạn có thể chọn Manual (Thủ công) trong menu Calculation Options (Tùy chọn Tính toán) của tab Formulas (Công thức).
Cách tốt hơn là thay thế hàm biến động bằng giải pháp ổn định, như dùng INDEX thay cho OFFSET hoặc XLOOKUP thay cho INDIRECT. Ngay cả Microsoft cũng khuyên giảm hàm biến động để tăng tốc bảng tính.
6. Giá trị được mã hóa cứng
Công thức Excel cần linh hoạt, nhưng đôi khi ta lại nhập trực tiếp một giá trị (như thuế suất) vào trong công thức khi đang vội. Đây gọi là hard-coding (mã hóa cứng), và nó giống như tạo ra một điểm yếu tiềm ẩn trong bảng tính.
Mọi biến số nên được đặt trong ô riêng có nhãn, và công thức tham chiếu đến ô đó. Như vậy, khi cần thay đổi, bạn chỉ sửa một chỗ.
Tốt hơn nữa, bạn có thể đặt tên cho ô đó. Chọn ô, trong Name Box (Hộp Tên) ở góc trên bên trái, nhập một tên có ý nghĩa để tạo named range (dải ô được đặt tên). Sau đó, dùng tên này trong công thức.
Để kiểm tra, nhấn Ctrl+~. Thao tác này hiển thị mọi công thức, giúp bạn dễ dàng phát hiện chỗ nào dùng số trực tiếp thay vì tham chiếu ô.
Ngoài những lỗi vô hình trên, còn một số lỗi Excel phổ biến khác—như hợp nhất ô, không dùng bảng Excel và dùng sai tham chiếu—có thể làm hỏng dữ liệu của bạn. Bằng cách sửa những lỗi này, bạn đảm bảo bảng tính của mình vừa đáng tin cậy vừa hoạt động trơn tru.
Tags: Excel, lỗi ẩn, kiểm tra dữ liệu
Tags: Excel, lỗi ẩn, kiểm tra dữ liệu
