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

[BNTT]

5. Kiểm tra một bảng tính

​

Như bạn đã thấy, một số lỗi công thức là kết quả của việc tham chiếu tới những ô chứa các giá trị lỗi hoặc các giá trị không thích hợp. Bước đầu tiên trong việc xử lý các loại lỗi công thức này là tìm cho ra ô (hoặc nhóm ô) nào gây ra lỗi. Điều này thì đơn giản nếu công thức chỉ tham chiếu đến một ô, nhưng nó nó trở nên phức tạp hơn khi số tham chiếu tăng lên. (Một yếu tố phức tạp khác là việc sử dụng các tên dãy, bởi vì không dễ gì biết được dãy nào ứng với cái tên dãy đang được tham chiếu đến).

Để tìm ra những ô nào gây ra lỗi cho các công thức của bạn, bạn có thể dùng chức năng kiểm tra của Excel để hình dung ra và truy tìm dấu vết các dữ liệu được nhập trong công thức cũng như nguồn gốc của các lỗi.


5.1. Tìm hiểu việc Kiểm tra bảng tính

Chức năng Kiểm tra công thức (formula-auditing) của Excel hoạt động bằng cách tạo ra các tracer, là những cái mũi tên hai đầu chỉ ra các ô có liên quan trong một công thức. Bạn có thể sử dụng các tracer để tìm ra ba loại ô như sau:

• Precedents Đây là các ô được tham chiếu trực tiếp hoặc gián tiếp trong một công thức. Ví dụ, ô B4 chứa công thức =B2; rồi B2 sau đó lại là một precedent trực tiếp của B4. Bây giờ giả sử rằng ô B2 chứa công thức =A2/2; điều này làm cho A2 trở thành một precedent trực tiếp của B2 đồng thời cũng là một precedent gián tiếp của B4.
• Dependents Đây là các ô được tham chiếu trực tiếp hoặc gián tiếp bởi một công thức đang nằm trong một ô khác. Trong ví dụ trên đây, ô B2 là một dependent trực tiếp của A2, và B4 là một dependent gián tiếp của A2.
• Errors Đây là những ô chứa một giá trị lỗi và đang được tham chiếu trực tiếp hoặc gián tiếp trong một công thức (và do đó nó gây ra một lỗi tương tự trong công thức này).

Hình 7 minh họa một bảng tính với ba ví dụ của các mũi tên tracer:

Hình 7​

• Ô B4 chứa công thức =B2, và B2 chứa công thức =A2/2. Những cái mũi tên (màu xanh) chỉ ra các precedent (trực tiếp và gián tiếp) của B4.
• Ô D4 chứa công thức =D2, và D2 chứa công thức =D1/0, gây ra lỗi #DIV/0!, do đó, lỗi này cũng xuất hiện ở ô D4. Mũi tên (màu đỏ) chỉ ra nguồn gốc của lỗi.
• Ô G4 chứa công thức =Sheet2!A1. Excel hiển thị một mũi tên đứt khúc với một cái biểu tượng bảng tính ở đuôi mũi tên khi precedent hoặc dependent nằm trên một trang tính (worksheet) khác.

5.2. Truy tìm các Precendent của ô
Để tìm các precedent của ô, theo các bước sau:

1. Chọn ô chứa công thức mà bạn muốn truy tìm precedent của nó.
2. Chọn Formulas, Trace Precedents. Excel sẽ thêm một mũi tên vào mỗi cái precedent trực tiếp.
3. Tiếp tục lập lại bước 2 để thêm các cấp precedent (nếu có).

Bạn cũng có thể truy tìm các precedent bằng cách nhấn đúp vào ô, với điều kiện là bạn phải tắt chức năng sửa trực tiếp trong ô. Bạn làm điều này bằng cách chọn Office, Excel Options để mở hộp thoại Options, nhấn Advanced, và rồi xóa dấu kiểm ở tùy chọn Allow Editing Directly in Cells. Bây giờ, khi bạn nhấn đúp vào một ô, Excel sẽ chọn các precedent của công thức.

5.3. Truy tìm các Dependent của ô
Đây các bước để truy tìm các precedent của ô:

1. Chọn ô mà bạn muốn truy tìm dependent của nó.
2. Chọn Formulas, Trace Dependents. Excel sẽ thêm một mũi tên vào mỗi cái dependent trực tiếp.
3. Tiếp tục lập lại bước 2 để thêm các cấp dependent (nếu có).

5.4. Truy tìm các nguyên nhân lỗi của ô
Để tìm các nguồn gốc gây ra lỗi cho ô, theo các bước sau

1. Chọn ô có chứa lỗi mà bạn muốn truy tìm nguồn gốc của lỗi này.
2. Chọn Formulas, Error Checking, Trace Error. Excel sẽ thêm một mũi tên (màu đỏ) vào mỗi ô là nguồn gốc của lỗi.

5.5. Gỡ bỏ những mũi tên Tracer
Để gỡ bỏ những mũi tên tracer, bạn có ba lựa chọn:

• Để gỡ bỏ tất cảc các mũi tên tracer, chọn Formulas, Remove Arrows.
• Để gỡ bỏ các mũi tên precedent mỗi lần một cấp, chọn Formulas, nhấn danh sách xổ xuống Remove Arrows, và chọn Remove Precedent Arrows.
• Để gỡ bỏ các mũi tên dependent mỗi lần một cấp, chọn Formulas, nhấn danh sách xổ xuống Remove Arrows, và chọn Remove Dependent Arrows.

5.6. Lượng giá các công thức
Bạn đã học để có thể xử lý sự cố cho một công thức bị lỗi hoặc lượng giá các phần của công thức. Bạn làm điều này bằng cách chọn một phần nào đó của công thức bạn muốn lượng giá và nhấn F9. Việc này có tác dụng tốt, nhưng nó có thể mất thời gian với một công thức dài hoặc phức tạp, và luôn luôn có rủi ro khi bạn vô tình xác nhận (nhấn Enter) một công thức chỉ mới được lượng giá một phần và làm cho bạn mất công (phải làm lại từ đầu).

Chức năng Evaluate Formula của Excel là một giải pháp tốt hơn. Nó hoạt động giống như kỹ thuật nhấn F9, nhưng dễ hơn và an toàn hơn. Sau đây là cách nó làm việc:

1. Chọn ô chứa công thức mà bạn muốn lượng giá.
2. Chọn Formulas, Evaluate Formula. Excel hiển thị hộp thoại Evaluate Formula.
3. Số hạng hiện hành trong công thức được gạch dưới trong hộp Evaluation. Tại mỗi bước, bạn chọn từ một đến nhiều nút sau đây:
   • Evaluate Nhấn nút này để hiển thị giá trị hiện hành của số hạng được gạch dưới.
   • Step In Nhấn nút này để hiển thị cái dependent đầu tiên của số hạng được gạch dưới. Nếu cái dependent này cũng có một dependent, nhấn tiếp nút này để xem nó (xem hình 8)
     
     
     
     
     Hình 8
     
   • Step Out Nhấn nút này để che bớt một dependent và lượng giá precedent của nó.
4. Lặp lại bước 3 cho đến khi bạn hoàn tất việc lượng giá.
5. Nhấn Close.

5.6. Theo dõi các giá trị ô
Trong ví dụ về precedent tracer minh họa ở hình 7, công thức trong ô G4 tham chiếu đến một ô trong một worksheet khác, được trình bày trong tracer bằng một biểu tượng có hình cái bảng tính. Nói một cách khác, bạn không thể thấy ô công thức và ô precedent cùng một lúc. Điều này cũng có thể xảy ra nếu precedent nằm trong một bảng tính khác hoặc thậm chí là nằm ở một nơi khác trong cùng trang tính nếu bảng tính của bạn khá lớn.

Vấn đề là không có cách nào dễ dàng để quyết định nội dung hiện hành hoặc giá trị của precedent mà không thấy được nó. Nếu bạn gặp một vấn đề gì đó, việc xử lý sự cố có thể đòi hỏi bạn phải theo dõi cái precedent đang nằm xa lắc này để xem nó có phải là nguyên nhân gây lỗi hay không. Chừng đó thôi đã đủ mệt rồi, vậy bạn sẽ làm gì khi công thức tham chiếu đến 5 hoặc 10 ô như vậy? Và phải làm gì nếu các ô đó nằm phân tán trong các trang tính hay bảng tính khác nhau?

Mức độ phiền toái này không phải là hiếm trong thế giới bảng tính chắc chắn là nguồn cảm hứng nằm sau một giải pháp tinh tế: Watch Window. Cửa sổ này cho phép bạn theo dõi cả giá trị lẫn công thức nằm trong bất kỳ trang tính hoặc bảng tính nào đang mở. Sau đây là những cách thiết lập một cái "theo dõi":

1. Kích hoạt bảng tính chứa ô hoặc những ô mà bạn muốn theo dõi.
2. Chọn Formulas, Watch Window. Excel hiển thị cửa sổ Watch Window.
3. Nhấn Add Watch. Excel hiển thị hộp thoại Add Watch.
4. Chọn ô mà bạn muốn theo dõi, hay nhập một công tức tham chiếu đến ô (ví dụ, =A1). Chú ý rằng bạn có thể chọn một dãy ô để thêm một lần nhiều ô vào Watch Window.
5. Nhấn Add. Excel thêm ô hoặc các ô vửa chọn vào cửa sổ Watch Window, như minh họa ở hình 9.
   
   
   
   
   Hình 9

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