Công thức Excel là xương sống của mọi bảng tính mạnh mẽ, nhưng chúng cũng có thể trở thành nguồn cơn của sự thất vọng khi mọi thứ không theo ý muốn. Một lỗi đánh máy nhỏ hay dấu ngoặc đơn đặt sai vị trí cũng có thể làm sai lệch hoàn toàn các phép tính của bạn chỉ trong chớp mắt. Excel có thể hiển thị những lỗi khó hiểu như #REF! hay #N/A, khiến bạn muốn bỏ cuộc. Thay vì chấp nhận số phận, hãy trang bị cho mình những kỹ năng gỡ lỗi Excel chuyên nghiệp này để xác định chính xác nguồn gốc lỗi và khôi phục trật tự cho các bảng tính của mình.
Cho dù bạn là một chuyên gia bảng tính hay chỉ mới bắt đầu hành trình với Excel, việc nắm vững nghệ thuật gỡ lỗi công thức sẽ giúp bạn tiết kiệm thời gian, tránh đau đầu và nâng cao năng suất làm việc hiệu quả.
Hình ảnh minh họa một số ứng dụng thú vị và sáng tạo của Microsoft Excel trong công việc và cuộc sống
4 Kỹ thuật gỡ lỗi Excel cơ bản nhất mà ai cũng cần biết
Bắt đầu từ những lỗi rõ ràng nhất: Kiểm tra chính tả, cú pháp và dấu ngoặc
Ngay cả những người dùng Excel kỳ cựu cũng có thể bị mắc kẹt bởi những sai lầm đơn giản. Trước khi đi sâu vào các phương pháp gỡ lỗi phức tạp, hãy đảm bảo bạn đã kiểm tra những điều cơ bản trước tiên. Xét cho cùng, Excel không khoan nhượng với lỗi chính tả. Một lỗi đánh máy duy nhất trong tên hàm (ví dụ: SUM thay vì SMU), tham chiếu ô (ví dụ: A1 thay vì A2) hoặc toán tử (+ thay vì -) có thể làm hỏng toàn bộ phép tính của bạn.
Ví dụ, công thức =VLOOUP(A1, B1:C10, 2, FALSE) sẽ không hoạt động vì hàm VLOOKUP đã bị đánh máy sai. Bạn nên sử dụng tính năng tự động hoàn thành của Excel để tránh những lỗi chính tả như vậy. Ngoài ra, bạn cũng cần lưu ý đến dấu ngoặc đơn, vì chúng sẽ quyết định thứ tự ưu tiên của các phép toán trong công thức của bạn. Để minh họa, khi bạn nhập =10 + 5 * 2, kết quả sẽ là 20, nhưng =(10 + 5) * 2 lại cho kết quả là 30.
Bạn cũng cần sử dụng cú pháp cụ thể định nghĩa thứ tự và loại đối số mà hàm yêu cầu. Ví dụ, =SUM(A1:A10) là đúng để tính tổng một phạm vi, nhưng =SUM(A1, A10) sẽ chỉ tính tổng hai ô đó mà thôi. Nếu bạn không chắc chắn về cú pháp của một hàm, hãy sử dụng nút fx bên cạnh thanh công thức để nhận trợ giúp chi tiết.
Ví dụ về lỗi chính tả trong công thức VLOOKUP và cách khắc phục đơn giản trong Excel
3 Cách chia nhỏ công thức Excel phức tạp để dễ dàng gỡ lỗi
Kiểm tra với các giá trị đơn giản: Tách đoạn và thay thế bằng số
Khi bạn đối phó với những công thức dài và phức tạp, việc chia nhỏ chúng là rất quan trọng để xác định nguồn gốc lỗi. Bạn có thể chia công thức thành các phần nhỏ hơn và kiểm tra từng phần riêng lẻ. Điều này giúp bạn khoanh vùng chính xác phần đang gây ra sự cố.
Thay vì chỉ nhìn vào công thức =IF(SUM(A1:A10)>100, AVERAGE(B1:B10), SUM(C1:C10)), bạn có thể kiểm tra SUM(A1:A10), AVERAGE(B1:B10) và SUM(C1:C10) một cách riêng biệt. Bạn cũng có thể sử dụng các ô tạm thời để hiển thị kết quả của từng phần, giúp phân tích dễ dàng hơn.
Bạn cũng nên thay thế các phần phức tạp của công thức bằng các số đơn giản để xem liệu logic cốt lõi có hoạt động đúng không. Điều này giúp bạn loại trừ các vấn đề liên quan đến kiểu dữ liệu hoặc các giá trị không mong muốn trong phạm vi của bạn.
Minh họa cách chia nhỏ một công thức Excel phức tạp thành các phần nhỏ hơn để dễ dàng gỡ lỗi
2 Công cụ tích hợp mạnh mẽ trong Excel để gỡ lỗi hiệu quả
Excel đi kèm với các công cụ tích hợp mạnh mẽ giúp bạn theo dõi và loại bỏ các lỗi công thức. Bạn sẽ tìm thấy bộ công cụ này trong tab Formulas (Công thức) trên thanh ribbon của Excel. Hãy cùng tìm hiểu một số tùy chọn phổ biến nhất.
Trace Precedents (Truy tìm phần phụ thuộc)
Tùy chọn này giúp bạn hình dung các mối quan hệ phụ thuộc trong bảng tính và hiểu dữ liệu đang đến từ đâu. Ví dụ, nếu bạn truy tìm phần phụ thuộc cho một ô có công thức =SUM(A1:A5), các mũi tên sẽ chỉ từ các ô A1 đến A5 đến ô đã chọn, cho thấy chúng là nguồn dữ liệu.
Ví dụ về việc sử dụng công cụ Trace Precedents trong Excel để theo dõi nguồn dữ liệu của một ô công thức
Trace Dependents (Truy tìm phần liên quan)
Đây là chức năng ngược lại với Trace Precedents, nó hiển thị những ô nào khác đang dựa vào kết quả của ô được chọn. Bằng cách này, bạn có thể lường trước hậu quả của các thay đổi và tránh những lỗi không mong muốn trong bảng tính của mình.
Evaluate Formula (Đánh giá công thức)
Đây là một thủ thuật Excel cần thiết mà nhiều người ước mình biết sớm hơn. Nó mở một hộp thoại cho phép bạn xem xét quá trình tính toán công thức từng bước một. Bạn có thể xem nhanh kết quả của từng phần trong công thức. Điều này cực kỳ tiện dụng khi xử lý các công thức lồng nhau phức tạp hoặc các công thức có nhiều hàm.
Error Checking (Kiểm tra lỗi)
Excel tự động thực hiện một số kiểm tra lỗi ngầm. Khi không có cách nào thông thường phát huy tác dụng, hãy sử dụng công cụ Error Checking để kích hoạt kiểm tra các lỗi phổ biến như công thức không nhất quán hoặc lỗi bên trong công thức.
Giao diện công cụ Error Checking của Excel hiển thị các lỗi tiềm ẩn trong bảng tính của bạn
1 Mẹo xem tất cả công thức Excel cùng lúc
Tính năng Show Formulas (Hiện công thức) giúp bạn bao quát toàn bộ
Kiểm tra công thức cho từng ô có thể tốn thời gian. Thay vì nhấp vào từng ô riêng lẻ, bạn có thể sử dụng tùy chọn Show Formulas để hiển thị tất cả các công thức cùng lúc. Khi bạn kích hoạt nó, Excel tạm thời ẩn các kết quả tính toán trong tất cả các ô và thay vào đó hiển thị các công thức thực tế.
Bằng cách đó, bạn có một cái nhìn tổng thể toàn diện về cấu trúc bảng tính của mình và cách các công thức tương tác với nhau. Nó giúp bạn nhanh chóng quét bảng tính để tìm các lỗi tiềm ẩn trong công thức, chẳng hạn như lỗi đánh máy, tham chiếu ô không chính xác hoặc dấu ngoặc đơn đặt sai vị trí.
Tuy nhiên, khi bạn đang ở chế độ Show Formulas, bạn không thể chỉnh sửa công thức trực tiếp. Bạn phải ghi lại các lỗi và quay lại chế độ bình thường để thực hiện các chỉnh sửa cần thiết.
Màn hình Excel hiển thị tất cả công thức thay vì kết quả tính toán khi bật chế độ Show Formulas
Làm chủ các công thức Excel của bạn
Gỡ lỗi công thức Excel là một kỹ năng mà bất kỳ ai cũng có thể nắm vững, và nó ít đáng sợ hơn nhiều so với vẻ ngoài. Hãy lưu lại bài viết này và xem lại bất cứ khi nào bạn gặp lỗi công thức. Bạn không còn cần phải nhìn chằm chằm vào màn hình không ngừng, nghi ngờ logic của mình hay phải tra Google để tìm câu trả lời đúng. Bằng cách áp dụng những kỹ thuật này, bạn sẽ tự tin hơn trong việc quản lý và làm việc với các bảng tính Excel, tối ưu hóa năng suất và giảm thiểu những “cơn đau đầu” không đáng có.