Cách sửa lỗi công thức trong Excel bằng nhóm công cụ Formula Auditing

Bạn đã biết cách đánh giá và sửa lỗi công thức trong Excel hiệu quả nhất là gì chưa? Trong bài học Excel ngày hôm nay, hãy cùng Gitiho tìm hiểu về nhóm công cụ Formula Auditing và cách sử dụng các tính năng này để kiểm tra công thức trong Excel nhé.

Cách đánh giá và sửa lỗi công thức trong Excel bằng Formula Auditing

Nhóm công cụ Formula Auditing nằm trong thẻ Formulas, được thiết kế giúp người dùng phân tích các công thức Excel và mối liên hệ giữa các công thức Excel với các tham chiếu ô tương ứng. Dựa vào quá trình đánh giá hàm với các công cụ Formular Auditing, chúng ta có thể phát hiện và sửa các lỗi công thức trong Excel. Vậy, hãy cùng Gitiho đi tìm hiểu từng tính năng trong nhóm Formula Auditing nhé!

Trace Precedents: Truy gốc các giá trị sử dụng trong công thức

Công cụ Trace Precedents có chức năng chỉ ra các ô chứa giá trị nguồn được sử dụng trong công thức Excel. Khi bạn chọn một công thức và sử dụng Trace Precedents, bạn sẽ thấy Excel vẽ các đường mũi tên theo chiều từ ô chứa giá trị nguồn đến ô chứa công thức xây dựng và tính toán dựa trên giá trị nguồn đó.

Giả sử chúng ta có công thức Excel tại ô D2 như sau:

=B2*C2

Như bạn thấy, trong công thức Excel chứa 2 tham chiếu ô lần lượt là B2 và C2. Khi sử dụng công cụ Trace Precedents, màn hình trang tính Excel của chúng ta sẽ hiển thị mũi tên xanh như trong hình dưới đây.

 

Với công cụ Trace Precedents này, bạn có thể dễ dàng truy vết các giá trị, từ đó phát hiện các lỗi công thức trong Excel bắt nguồn từ sai lệch tham chiếu ô. Dù là công thức tính tổng đơn giản như trong ví dụ hay các phép tính nâng cao kết hợp điều kiện logic, việc truy vết dữ liệu cũng là một bước không thể bỏ qua để đảm bảo độ chính xác tuyệt đối của phép tính. Chính vì vậy, đừng bao giờ quên kiểm tra công thức trong Excel của mình bạn nhé!

Xem thêm: Hướng dẫn sử dụng hàm SUMIFS tính tổng nhiều điều kiện

Trace Dependents: Tìm các công thức chứa giá trị được chọn

Nếu như công cụ Trace Precedents kiểm tra công thức trong Excel bằng cách truy gốc các giá trị, thì ngược lại, công cụ Trace Dependents xác định các công thức chứa giá trị trong phạm vi ô tính được chọn. Cách làm tương tự như Trace Precedents: Excel vẽ ra các đường mũi tên theo chiều từ phạm vi ô đang chọn đến các ô chứa công thức đích.

Giả sử chúng ta muốn kiểm tra lỗi công thức trong Excel bằng cách chọn ô B3 và nhấn Trace Dependents. Sau khi nhấn lần một, bạn sẽ thấy mũi tên hiện lên chỉ từ ô B3 sang ô D3 chứa công thức =B3*C3. Nếu bạn nhấn Trace Precedents lần thứ hai, Excel sẽ kẻ thêm một đường mũi tên xanh từ ô D3 xuống ô D7 chứa công thức =SUM(D2:D6).

Như vậy, bạn có thể thấy ô B3 xuất hiện trực tiếp trong phép nhân tại ô D3. Vì kết quả ô D3 được sử dụng cho phép cộng tại ô D7, do đó ô B3 cũng gián tiếp xuất hiện trong công thức ô D7. Đó chính là một ví dụ về mối quan hệ giữa các công thức và tham chiếu ô trong Excel.

Lưu ý:

Khi sử dụng bộ đôi công cụ kiểm tra công thức trong Excel Trace Precedents và Trace Dependents nhiều lần với nhiều ô tính Excel, các đường mũi tên xanh cũ sẽ không biến mất mà vẫn hiển thị trên màn hình trang tính. Sau một vài lần thao tác truy vết như vậy, bạn sẽ thấy các đường mũi tên xuất hiện chồng chéo lên nhau cực kì rối mắt. Do đó, hãy sử dụng công cụ Trace Precedents và Trace Dependents một cách hợp lý để phát hiện các lỗi công thức trong Excel mà không bị hoa mắt chóng mặt nhé.

Xem thêm: Hướng dẫn hai cách lấy địa chỉ ô Excel đơn giản nhất

Remove Arrows: Xóa các mũi tên Trace

Nếu bạn đã kiểm tra công thức trong Excel xong xuôi và muốn xóa toàn bộ các đường mũi tên đã tạo từ Trace Precedents và Trace Dependents, hãy nhấn vào Remove Arrows ngay bên dưới 2 tính năng Trace tại thanh công cụ.

Bạn có thể chọn xóa tất cả, chỉ xóa các mũi tên Trace Precedents hoặc chỉ xóa các mũi tên Trace Dependents bằng cách nhấn vào các tùy chọn tương ứng.

Error Checking: Phát hiện lỗi công thức trong Excel

Công cụ Error Checking giúp bạn phát hiện nhanh các lỗi công thức trong Excel. Bạn chỉ cần nhấn vào Error Checking trong nhóm Formula Auditing và Excel sẽ tự động quét trang tính để kiểm tra công thức trong Excel, xác định các lỗi tồn tại.

Chúng ta có một ví dụ như trong hình dưới đây:

 

Công cụ Error Tracking đã phát hiện được một lỗi công thức trong Excel. 

• Công thức hàm INDEX MATCH chứa lỗi tại ô G4: 

=INDEX(B3:D5,MATCH(G3,A3:A5,0),MATCH(G2,B2:D2,0))

• Chi tiết lỗi: Một giá trị được chỉ định trong công thức hiện không tồn tại.

Với lỗi #N/A này, bạn có thể nhấn nút Help on this error. Excel sẽ điều hướng đến trang web hỗ trợ của Microsoft để giúp bạn sửa lỗi công thức trong Excel. Hoặc bạn nhấn Show Calculation Steps để kích hoạt công cụ Evaluate Formula và hiển thị các bước tính toán trong công thức. Nếu bạn muốn bỏ qua lỗi đó, hãy nhấn Ignore Error.

Nếu bạn cần truy vết lỗi công thức trong Excel, hãy nhấn nút mũi tên bên cạnh Error Tracking và chọn Trace Error. Excel sẽ vẽ các đường mũi tên xanh tương tự như với tính năng Trace Precedents và Trace Dependents.

Xem thêm: Giới thiệu về hàm NA trong Excel để hiển thị lỗi #N/A

Evaluate Formula: Đánh giá công thức

Làm thế nào để phân tách công thức thành từng bước để hiểu được thứ tự tính toán của công thức? Hãy tìm hiểu cách kiểm tra công thức trong Excel qua một ví dụ nhé. Giả sử chúng ta có một công thức cần được đánh giá như sau:

=INDEX(B3:D5,MATCH(G3,A3:A5,0),MATCH(G2,B2:D2,0))

Bạn hãy vào thẻ Formulas => nhóm Formula Auditing => Evaluate Formula.

Hộp thoại Evaluate Formula trong Excel hiện lên cùng với công thức bạn đã chọn.

Tại ô Evaluation, bạn sẽ nhận thấy trong công thức có một phần gạch chân. Chúng ta sẽ thao tác với phần gạch chân này bằng cách nhấn vào các phím lựa chọn như sau:

• Evaluate: Tiến hành tính toán, tìm kiếm kết quả của phần được gạch chân (G3).
• Step In: Hiển thị thông số nội dung của phần được gạch chân.

Nếu bạn nghi ngờ phần gạch chân chứa giá trị sai, hãy nhấn Step In để kiểm tra và kịp thời phát hiện lỗi công thức trong Excel. Sau khi đã kiểm tra, bạn nhấn Step Out để quay về màn hình đánh giá thứ tự các bước tính toán công thức.

Lưu ý:

Nút Step In sẽ không hoạt động nếu tham chiếu ô của công thức dẫn đến một công thức trong một trang tính khác. Bên cạnh đó, nếu một tham chiếu ô nhất định xuất hiện nhiều lần trong công thức, Step In sẽ chỉ được áp dụng cho lần xuất hiện đầu tiên của tham chiếu.

Tại bước này, bạn hãy nhấn Evaluate hoặc nhấn phím cách (Spacebar) trên bàn phím để thực hiện đánh giá công thức Excel. Hãy để ý ô Evaluation và bạn sẽ thấy cách Excel chạy công thức cho dữ liệu của mình với các phần được gạch chân.

Sau khi hoàn tất quá trình đánh giá, bạn nhấn Close để đóng hộp thoại Evaluate Formula. Trong trường hợp bạn muốn thực hiện lại đánh giá, nhấn Restart.

Như vậy, chúng ta đã hoàn tất quá trình đánh giá công thức hàm INDEX MATCH tham chiếu dữ liệu. Evaluate Formula trong Excel cho phép bạn tính toán kết quả lần lượt cho từng thành phần của công thức, do đó, đây là công cụ hoàn hảo khi trang tính của bạn chứa các công thức phức tạp.

Xem thêm: Cách dùng hàm MATCH - trợ thủ đắc lực cho hàm tìm kiếm trong Excel

Lưu ý với Evaluate Formula khi đánh giá và sửa lỗi công thức trong Excel

Khi sử dụng Evaluate Formula trong Excel để đánh giá công thức, hãy lưu ý một số thành phần dưới đây có thể sẽ không hiển thị đúng theo mong đợi của bạn:

• Một số phần của công thức chứa hàm IF hoặc hàm CHOOSE sẽ không được đánh giá, mà thay vào đó Evaluate Formula hiển thị giá trị #N/A.
• Nếu xuất hiện tham chiếu tới ô trống, Evaluate Formula sẽ hiển thị giá trị 0.
• Công thức chứa vòng lặp sẽ không được đánh giá đúng.
• Những hàm tự động cập nhật kết quả có thể sẽ không được đánh giá đúng, bao gồm hàm TODAY, hàm RAND, hàm NOW,... hay các hàm OFFSET, hàm INDIRECT,...

Xem thêm: Giới thiệu về hàm OFFSET và các cách kết hợp hàm OFFSET trong Excel

Watch Window: Giám sát công thức

Nếu bạn đang làm việc với khối dữ liệu lớn, có thể bạn sẽ lạc mất các công thức quan trọng của mình. Để đề phòng tình trạng này, hãy sử dụng công cụ Watch Window để giám sát các công thức cần thiết trước khi tiếp tục xử lý dữ liệu trên trang tính Excel.

Những gì bạn cần làm là nhấn Watch Window trên thanh công cụ, sau đó nhấn Add Watch... và chọn phạm vi ô tính bạn muốn lưu lại. Toàn bộ thông tin về ô tính sẽ hiện thị trong hộp thoại.

 

Nếu bạn cần theo dõi tất cả các ô chứa công thức trên trang tính, một cách làm nhanh chóng là vào thẻ Home => nhóm Editing => Find & Select => Go To Special => Formulars.

Sau khi thêm vào hộp thoại Watch Window các ô tính quan trọng, bạn sẽ luôn nhìn thấy các dữ liệu về địa chỉ ô tính, giá trị ô tính, công thức ô tính bất kể bạn đang di chuyển đến vị trí nào trong trang tính, hay thậm chí di chuyển giữa các bảng tính khác nhau.

Để xóa một ô tính ra khỏi hộp thoại Watch Window, thao tác vô cùng đơn giản là chọn ô và nhấn Delete Watch.

Xem thêm: Hướng dẫn cách sử dụng Find và Replace nâng cao trong Excel – tối ưu nhất

Tổng kết

Qua bài viết này, chúng ta đã tìm hiểu về các tính năng kiểm tra công thức trong Excel với nhóm công cụ Formula Auditing. Các bạn hãy ghi nhớ cách sửa lỗi công thức trong Excel để dữ liệu trên trang tính luôn chính xác nhé. Để hạn chế tối đa các trường hợp xảy ra lỗi công thức trong Excel, các bạn hãy tham khảo các giải pháp trên blog Gitiho nhé.