Bài viết: Xử lý các lỗi của công thức (phần 3)

[BNTT]

3. Xử lý lỗi công thức bằng hàm IFERROR()

Ở bài trước, bạn đã thấy cách dùng hàm IF() để tránh lỗi #DIV/0!, bằng cách kiểm tra giá trị của số chia trong công thức xem nó có bằng 0 hay không. Hàm IF() này làm việc rất tốt nếu bạn có thể dự đoán được loại lỗi cụ thể mà người dùng có thể phạm phải. Tuy nhiên, sẽ có nhiều trường hợp bạn không thể biết trước được nguyên nhân chính xác gây ra lỗi. Ví dụ, công thức đơn giản này: =GrossProfit/Sales có thể tạo một lỗi #DIV/0! nếu như Sales bằng 0; tuy nhiên nó cũng có thể tạo lỗi #NAME? nếu GrossProfit hoặc Sales không tồn tại, hay nó có thể tạo lỗi #REF! nếu các ô liên kết với GrossProfit hoặc Sales (hoặc cả hai) bị xóa.

Nếu bạn muốn xử lý lỗi một cách tinh tế trong các bảng tính, tốt nhất bạn nên giả định rằng bất kỳ lỗi nào cũng có thể xảy ra. Điều này không có nghĩa là bạn phải tạo các phép thử phức tạp bằng cách sử dụng hàm IF() lồng bên trong công thức để kiểm tra mọi loại lỗi (#DIV/0!, #N/A, v.v...), vì Excel có cung cấp sẵn cho bạn một phép thử đơn giản cho mọi loại lỗi.

Hàm IFERROR(), IFERROR(value, value_if_error)

Nếu biểu thức value không tạo ra một lỗi, IFERROR() trả về kết quả của biểu thức; còn nếu không, nó sẽ trả về value_if_error (là chuỗi rỗng hoặc một thông báo lỗi). Sau đây là một ví dụ:

=IFERROR(GrossProfit / Sales), "")


4. Sử dụng chức năng kiểm tra lỗi công thức (Formula Error Checker)

Nếu bạn sử dụng Microsoft Word, có lẽ bạn đã quen với những đường gợn sóng màu xanh xanh xuất hiện ở bên dưới các từ hoặc cụm từ mà chương trình kiểm tra văn phạm (grammar checker) cho là không đúng. Grammar checker hoạt động bằng cách sử dụng một bộ quy tắc để kiểm tra văn phạm và cú pháp. Khi bạn nhập văn bản, grammar checker âm thầm theo dõi từng câu từng chữ của bạn, nếu có thứ gì đó bạn nhập không đúng với những quy tắc của grammar checker, đường gợn sóng sẽ xuất hiện để báo cho bạn biết là có vấn đề.

Excel cũng có tính năng tương tự như vậy: chức năng kiểm tra lỗi công thức (formula error checker). Nó tương tự như grammar checker, dùng một một bộ quy tắc để kiểm tra các phép tính và cũng hoạt động cách âm thầm khi giám sát những công thức của bạn. Nếu nó phát hiện ra điều gì đó không ổn, nó sẽ hiển thị một dấu hiệu báo lỗi — một cái tam giác màu xanh — ở góc trái phía trên của ô chứa công thức, như minh họa ở hình 3.

Hình 3


4.1. Chọn một cách xử lý lỗi

Khi bạn chọn cái ô có dấu hiệu báo lỗi, Excel hiển thị một smart tag ngay cạnh đó, và nếu bạn đặt con trỏ chuột lên trên cái biểu tượng mới xuất hiện này, một câu thông báo miêu tả lỗi mắc phải sẽ hiện lên, như minh họa trong hình 4. Bên góc phải của biểu tượng này còn có một nút nhấn để mở ra một danh sách những cách xử lý lỗi cho bạn chọn:

• Help on This Error - Tìm hiểu thông tin về lỗi qua hệ thống Help của Excel.
• Show Calculation Steps - Chạy chức năng Evaluate Formula (đánh giá công thức).
• Ignore Error - Bỏ qua, giữ nguyên công thức sai như vậy.
• Edit in Formula Bar - Hiển thị công thức trong chế độ chỉnh sửa (Edit) trên thanh công thức (formula bar). Chẳng qua là để cho bạn tự sửa lại công thức.
• Error-Checking Options - Hiện các tùy chọn của chức năng Error Checking từ hộp thoại Option để bạn chọn (sẽ nói chi tiết hơn ở bài sau).

Hình 4


4.2. Thiết lập các tùy chọn cho việc kiểm tra lỗi

Cũng giống như việc kiểm tra ngữ pháp trong Word, chức năng kiểm tra lỗi công thức (Formula Error Checker) cũng có một số những tùy chọn để quy định cách nó làm việc và sẽ đánh dấu những lỗi nào. Để xem những tùy chọn này, bạn có hai cách:

• Chọn Office, Excel Options để hiển thị hộp thoại Excel Options, và chọn Formulas
• Chọn Error-Checking Options trong danh sách xổ xuống của cái biểu tượng báo lỗi (như đã nói trong bài trước).

Cả hai cách đều mở ra những tùy chọn cho Error Checking và Error Checking Rules như minh họa ở hình 5:

Hình 5

Sau đây là tóm tắt về những tùy chọn:

• Enable Background Error Checking - Check box này bật và tắt chế độ tự động của chức năng Formula Error Checker. Nếu bạn tắt chế độ này, mỗi khi bạn muốn kiểm tra lỗi công thức, bạn chọn Formulas,Error Checking.

• Indicate Errors Using This Color - Chọn màu cho dấu chỉ báo lỗi (cái tam giác nhỏ xíu ở góc bên trái ô có lỗi).

• Reset Ignored Errors - Nếu bạn đã bỏ qua một hoặc nhiều lỗi, bạn có thể cho hiển thị lại các lỗi đó bằng cách nhấn nút này.

• Cells Containing Formulas That Result in an Error - Khi tùy chọn này được kích hoạt, chức năng Formula Error Checker sẽ đánh dấu vào ô công thức có kết quả là các giá trị lỗi như #DIV/0!, #NAME?, hay bất kỳ giá trị lỗi nào đã sử dụng trước đó.

• Inconsistent Calculated Column Formula in Tables - Khi tùy chọn này được kích hoạt, Excel kiểm tra các công thức trong cột dùng để tính toán của một Table (một dạng bảng đặc biệt của Excel), và đánh dấu vào những ô có công thức mà cấu trúc của công thức này không giống với những công thức khác trong cột. Trong smart tag ở ô có lỗi, có kèm thêm lệnh Restore to Calculated Column Formula, cho phép bạn cập nhật lại công thức để nó nhất quán với những công thức ở phần còn lại trong cột.

• Cells Containing Years Represented as 2 Digits - Khi tùy chọn này được kích hoạt, chức năng Formula Error Checker sẽ đánh dấu vào những công thức có bao gồm những giá trị ngày tháng mà trong đó con số chỉ năm chỉ có 2 chữ số (một tình huống mơ hồ, không rõ ràng, bởi vì chuỗi đó có thể tham chiếu đến một ngày nào ở những năm 1900 lẫn những năm 2000). Với trường hợp này, danh sách tùy chọn trong smart tag có chứa hai lệnh — Convert XX to 19XX và Convert XX to 20XX — cho phép bạn chuyển đổi con số chỉ năm có 2 chữ số thành con số có 4 chữ số.

• Numbers Formatted as Text or Preceded by an Apostrophe - Khi tùy chọn này được kích hoạt, chức năng Formula Error Checker sẽ đánh dấu vào những ô có chứa một con số được định dạng dưới dạng text hoặc có một dấu nháy đơn (') ở trước. Với trường hợp này, danh sách tùy chọn trong smart tag có thêm lệnh Convert to Number để chuyển con số đó thành một con số thật sự (định dạng theo kiểu số).

• Formulas Inconsistent with Other Formulas in the Region - Khi tùy chọn này được kích hoạt, chức năng Formula Error Checker sẽ đánh dấu vào những công thức có cấu trúc khác với những công thức tương tự ở xung quanh nó. Ví dụ, xem bảng tính được minh họa ở hình 6. Trong hàng SALES TOTAL (hàng 7), các tổng cho Jan, Feb và Mar (ô B7, C7 và D7) đều là tính tổng của những ô phía trên, chẳng hạn như công thức trong ô D7 là: =SUM(D46), và công thức tính tổng trong các ô F7, G7 và H7 cũng y như vậy. Tuy nhiên, công thức trong ô E7 là =SUM(B77), hay nói cách khác, ô này tính tổng của các giá trị trong hàng chứ không phải là các giá trị trong cột. Điều này không phải là không đúng, nhưng nó không nhất quán và có thể dẫn đến một vấn đề nào đó (ví dụ như có ai đó lỡ tay AutoFill ô E7 sang bên phải hoặc sang bên trái). Với trường hợp này, danh sách tùy chọn trong smart tag có thêm một lệnh đại loại như Copy Formula from Left (copy công thức ở ô bên trái sang đây) để làm cho công thức này nhất quán với những công thức xung quanh.
  
  
  
  
  Hình 6

• Formulas Which Omit Cells in a Region - Khi tùy chọn này được kích hoạt, chức năng Formula Error Checker sẽ đánh dấu vào những công thức (mà những công thức này) bỏ qua các hàng gần kề với dãy được tham chiếu trong công thức.

• Unlocked Cells Containing Formulas - Khi tùy chọn này được kích hoạt, chức năng Formula Error Checker sẽ đánh dấu vào những công thức nằm trong các ô không được khóa (unlocked). Đây không phải là một lỗi mà là một cảnh báo rằng những người khác có thể sửa đổi công thức, ngay cả sau khi bạn đã bảo vệ (protect) bảng tính. Với trường hợp này, danh sách tùy chọn trong smart tag có thêm lệnh Lock Cell dùng để khóa ô lại và ngăn không cho người dùng khác thay đổi công thức sau khi bạn đã bảo vệ bảng tính.

• Formulas Referring to Empty Cells - Khi tùy chọn này được kích hoạt, chức năng Formula Error Checker sẽ đánh dấu vào những công thức tham chiếu đến các ô rỗng. Với trường hợp này, danh sách tùy chọn trong smart tag có thêm lệnh Trace Empty Cell để cho phép bạn tìm ô rỗng mà công thức này đang tham chiếu đến (và bạn có thể nhập dữ liệu vào ô rỗng đó, hoặc điều chỉnh công thức sao cho nó không tham chiếu đến ô này nữa).

​

• Data Entered in a Table Is Invalid - Khi tùy chọn này được kích hoạt, chức năng Formula Error Checker sẽ đánh dấu vào những ô vi phạm các quy tắc hiệu lực hóa dữ liệu (data-validation rules) của một Table. Điều này có thể xảy ra nếu bạn thiết lập một quy tắc Data-validation với chỉ một kiểu Warning hoặc Information, người dùng vẫn có thể chọn nhập những dữ liệu không hợp lệ trong trường hợp này, và Formula Error Checker sẽ đánh dấu vào những ô chứa dữ liệu không hợp lệ. Danh sách tùy chọn trong smart tag có thêm lệnhDisplay Type Information, hiển thị quy tắc Data-validation mà những ô đó vi phạm.

Một số bài viết có liên quan:
1/ Xử lý các lỗi của công thức (phần 2)
2/ Xử lý các lỗi của công thức (phần 1)
3/ Chiêu thứ 27: Chuyển đổi con số dạng văn bản sang số thực
4/ Làm việc với công thức mảng trong Excel
5/ Các lỗi thường gặp trong Excel
6/ 6 thói quen cá nhân khi làm việc với dữ liệu và Excel
7/ 29 cách tiết kiệm thời gian với các công thức Excel (phần 3)
8/ 29 cách tiết kiệm thời gian với các công thức Excel (phần 2)
9/ 29 cách tiết kiệm thời gian với các công thức Excel (phần 1)
10/ Tổng quan về Go To Special

http://www.giaiphapexcel.com/vbb/content.php?331