Formulas & Functions Excel 2007 - 5. Troubleshooting Formulas (1 người xem)

[BNTT]

Phỏng dịch từ cuốn Formulas and Functions with Microsoft Office Excel 2007 của Paul McFedries

Part I: MASTERING EXCEL RANGES AND FORMULAS - Nắm vững các dãy và công thức trong Excel

• Chapter 1: Getting The Most Out Of Range - Tận dụng tối đa các dãy

• Chapter 2: Using a Range Name - Sử dụng tên cho dãy

• Chapter 3: Building Basic Formulas - Thiết lập các công thức (cơ bản)

• Chapter 4: Creating Advanced Formulas - Thiết lập các công thức (nâng cao)

• Chapter 5: Troubleshooting Formulas - Xử lý lỗi công thức

Chapter 5 - TROUBLESHOOTING FORMULAS

Xử lý các lỗi của công thức

Despite your best efforts, the odd error might appear in your formulas from time to time. These errors can be mathematical (for example, dividing by zero), or Excel might simply be incapable of interpreting the formula. In the latter case, problems can be caught while you’re entering the formula. For example, if you try to enter a formula that has unbalanced parentheses, Excel won’t accept the entry; it displays an error message instead. Other errors are more insidious. For example, your formula might appear to be working — that is, it returns a value — but the result is incorrect because
the data is flawed or because your formula has referenced the wrong cell or range.
Cho dù bạn có cố gắng đến cỡ nào, thì thỉnh thoảng vẫn có những cái lỗi kỳ lạ xuất hiện trong công thức của bạn. Những lỗi này có thể là về toán học (ví dụ, chia cho không), hoặc Excel dường như không hiểu được các công thức. Trong trường hợp thứ hai này, các lỗi có thể sẽ được bắt gặp trong khi bạn gõ công thức. Ví dụ, nếu bạn cố nhập một công thức có các dấu ngoặc đơn không cân bằng (thiếu hoặc thừa dấu mở hoặc đóng ngoặc), Excel sẽ không chấp nhận công thức, thay vào đó, nó hiển thị một thông báo lỗi. Những lỗi khác mang tính ngấm ngầm hơn. Ví dụ, công thức của bạn có vẻ như vẫn làm việc — nghĩa là nó trả về một giá trị — nhưng kết quả thì sai, bởi vì dữ liệu bị sai sót hoặc bởi vì công thức tham chiếu đến ô hay dãy không đúng.

Whatever the error and whatever the cause, formula woes need to be worked out. But don’t fall into the trap of thinking that your spreadsheets are problem free. And the more complex the model is, the greater the chance is that errors can creep in. A KPMG study from a few years ago found that a staggering 90% of spreadsheets used for tax calculations contained errors.
Cho dù lỗi là gì và nguyên nhân là gì, những điều phiền toái của công thức cũng phải được giải quyết. Nhưng đừng bao giờ nghĩ rằng các bảng tính của bạn không có lỗi. Và một mô hình (một bảng tính do bạn phác thảo ra) càng phức tạp, thì khả năng các lỗi có thể xảy ra càng nhiều.

The good news is that fixing formula flaws need not be drudgery. With a bit of know-how and Excel’s top-notch troubleshooting tools, sniffing out and repairing model maladies isn’t hard. This chapter tells you everything you need to know.
Việc sửa các lỗi công thức không phải là một công việc khó nhọc. Với một chút hiểu biết và những công cụ xử lý sự cố hàng đầu của Excel, việc tìm ra và xử lý các chứng bệnh (của mô hình) thì không khó lắm. Chương này sẽ cho bạn mọi thứ mà bạn cần biết.​

IN THIS CHAPTER:
Những nội dung chính trong chương này:

• Understanding Excel’s Error Values
  Tìm hiểu về các Giá Trị Lỗi trong Excel

• Fixing Other Formula Errors
  Sửa các lỗi công thức khác

• Handling Formula Errors with IFERROR()
  Xử lý các lỗi công thức bằng Hàm IFERROR()

• Using the Formula Error Checker
  Sử dụng công cụ kiểm tra lỗi công thức (Formula Error Checker)

• Auditing a Worksheet
  Kiểm tra một bảng tính

 

[BNTT]

Chapter 5 - TROUBLESHOOTING FORMULAS


5.1. Understanding Excel’s Error Values
Tìm hiểu các Giá Trị Lỗi trong Excel

When you enter or edit a formula or change one of the formula’s input values, Excel might show an error value as the formula result. Excel has seven different error values: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. The next few sections give you a detailed look at these values and offer suggestions for solving them.
Khi bạn nhập hay sửa đổi một công thức, hoặc thay đổi một giá trị nhập liệu nào đó trong công thức, Excel có thể sẽ hiện ra một giá trị lỗi như là kết quả của công thức. Excel có 7 giá trị lỗi khác nhau: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, và #VALUE!. Các mục tiếp theo đây sẽ cho bạn một cái nhìn chi tiết về những giá trị này và đưa ra những gợi ý để giải quyết chúng.​

#DIV/0!

The #DIV/0! error almost always means that the cell’s formula is trying to divide by zero, a mathematical no-no. The cause is usually a reference to a cell that either is blank or contains the value 0. Check the cell’s precedents (the cells that are directly or indirectly referenced in the formula) to look for possible culprits. You’ll also see #DIV/0! if you enter an inappropriate argument in some functions. MOD(), for example, returns #DIV/0! if the second argument is 0.
Lỗi #DIV/0! hầu như luôn có nghĩa rằng công thức trong ô đang cố chia (một giá trị) cho 0 (zero), một điều không thể có trong toán học. Điều này thường là do các tham chiếu dẫn đến một ô rỗng hoặc một ô chứa giá trị bằng 0. Bạn kiểm tra các precedent của ô (các ô được tham chiếu trực tiếp hay gián tiếp trong công thức) để tìm ra nguyên nhân có thể có. Bạn cũng sẽ thấy #DIV/0! nếu bạn nhập một đối số không thích hợp trong một số hàm. Ví dụ, hàm MOD() trả về #DIV/0! nếu đối số thứ hai là 0.

That Excel treats blank cells as the value 0 can pose problems in a worksheet that requires the user to fill in the data. If your formula requires division by one of the temporarily blank cells, it will show #DIV/0! as the result, possibly causing confusion for the user. You can get around this by telling Excel not to perform the calculation if the cell used as the divisor is 0. This is done with the IF() worksheet function, which I discuss in detail in Chapter 8, “Working with Logical and Information Functions.” For example, consider the following formula that uses named cells to calculate gross margin:
Việc Excel xem các giá trị rỗng là 0 có thể dẫn đến những vấn đề trong một bảng tính có yêu cầu người dùng điền dữ liệu vào. Nếu công thức của bạn đòi hỏi một phép chia với một trong các ô rỗng tạm thời, nó sẽ hiển thị #DIV/0! dưới dạng kết quả, (và điều này) có thể gây bối rối cho người dùng. Bạn có thể giải quyết điều này bằng cách yêu cầu Excel không thực hiện phép chia nếu ô được sử dụng là số chia là 0. Điều này làm được với hàm IF(), là hàm mà tôi viết trong chương 8, "Làm việc với các hàm Dò tìm và Tham chiếu". Ví dụ, bạn xem công thức sau đây, sử dụng các ô đã được đặt tên để tính tổng lợi nhuận (gross margin):

= GrossProfit / Sales​

To prevent the #DIV/0! error from appearing if the Sales cell is blank (or 0), you would modify the formula as follows:
Để ngăn lỗi #DIV/0! xuất hiện nếu ô Sales rỗng (hoặc bằng 0), bạn nên sửa công thức trên như sauu:

= IF(Sales = 0, "", GrossProfit / Sales)​

If the value of the Sales cell is 0, the formula returns the empty string; otherwise, it performs the calculation.
Nếu giá trị của ô Sales bằng 0, thì công thức trả về một chuỗi rỗng (có nghĩa là để trống ô đó), còn không thì thực hiện phép tính.​

 

[BNTT]

5.1. Understanding Excel’s Error Values


#N/A

The #N/A error value is short for not available, and it means that the formula couldn’t return a legitimate result. You usually see #N/A when you use an inappropriate argument (or if you omit a required argument) in a function. HLOOKUP() and VLOOKUP(), for example, return #N/A if the lookup value is smaller than the first value in the lookup range.
Giá trị lỗi #N/A là viết tắt của chữ Not Availablel, nó có nghĩa là công thức không thể trả về một kết quả hợp lệ. Bạnb thường thấy #N/A khi bạn sử dụng một đối số không thích hợp (hoặc nếu bạn bỏ qua một đối số bắt buộc) trong một hàm. Ví dụ, hàm HLOOKUP() và hàm VLOOKUP() sẽ trả về #N/A nếu như giá trị dò tìm (lookup_value) nhỏ hơn giá trị đầu tiên trong dãy dò tìm (table_array).

To solve the problem, first check the formula’s input cells to see if any of them are displaying the #N/A error. If so, that’s why your formula is returning the same error; the problem actually lies in the input cell. When you’ve found where the error originates, examine the formula’s operands to look for inappropriate data types. In particular, check the arguments used in each function to ensure that they make sense for the function and that no required arguments are missing.
Để giải quyết vấn đề này, đầu tiên bạn kiểm tra các ô nhập liệu của công thức xem có ô nào hiển thị lỗi #N/A không. Nếu có, thì đó là lý do tại sao công thức của bạn cũng có lỗi đó; vấn đề thực sự nằm trong các ô dữ liệu. Khi bạn đã tìm thấy nơi xuất phát lỗi, bạn kiểm tra lại các toán hạng (operand) trong công thức để thay bằng các kiểu dữ liệu thích hợp. Đặc biệt, bạn nên kiểm tra các đối số sử dụng trong hàm để bảo đảm rằng chúng có nghĩa và bạn không bị thiếu các đối số bắt buộc.​

 

[BNTT]

5.1. Understanding Excel’s Error Values


#NAME?

You see the #NAME? error when Excel doesn’t recognize a name you used in a formula or when it interprets text within the formula as an undefined name. This means that the #NAME? error pops up in a wide variety of circumstances:
Bạn thấy lỗi #NAME? khi Excel không nhận biết một cái tên mà bạn sử dụng trong công thức, hoặc khi Excel cho rằng chuỗi văn bản trong công thức là một cái tên chưa xác định. Điều này có nghĩa rằng lỗi #NAME? xuất hiện trong nhiều tình huống khác nhau:

• You spelled a range name incorrectly.
  Bạn nhập sai chính tả (nhập không đúng) một tên dãy.

• You used a range name that you haven’t yet defined.
  Bạn dùng một tên dãy mà bạn chưa định nghĩa.

• You spelled a function name incorrectly.
  Bạn nhập sai chính tả (nhập không đúng) một tên hàm.

• You used a function that’s part of an uninstalled add-in.
  Bạn sử dụng một hàm thuộc một Add-in chưa được cài đặt (nhất là Add-in Analysic ToolPak).

• You used a string value without surrounding it with quotation marks.
  Bạn sử dụng một chuỗi mà không đặt chúng trong một cặp dấu nháy kép (").

• You entered a range reference and accidentally omitted the colon.
  Bạn nhập một tham chiếu dãy mà vô tình bỏ sót dấu hai chấm ).

• You entered a reference to a range on another worksheet and didn’t enclose the sheet name in single quotation marks.
  Bạn nhập một tham chiếu đến một dãy trên một bảng tính khác mà không nhập tên của bảng tính đó trong cặp dấu nháy đơn (').

TIP: When entering function names and defined names, use all lowercase letters. If Excel recognizes a name, it converts the function to all uppercase and the defined name to its original case. If no conversion occurs, you misspelled the name, you haven’t defined it yet, or you’re using a function from an add-in that isn’t loaded.
Khi nhập các tên hàm và các tên (của ô, của dãy) đã được định nghĩa, bạn hãy nhập chúng với loại chữ thường. Nếu Excel nhận biết đó là một tên, nó sẽ tự động chuyển đổi các tên hàm thành chữ in hoa và tên (của ô, của dãy) thành kiểu chữ gốc (mà bạn đã nhập khi định nghĩa) của tên đó. Nếu Excel không tự chuyển đổi, thì chắc chắn rằng bạn đã nhập sai, hoặc bạn chưa định nghĩa cái tên này, hoặc bạn sử dụng một hàm của một Add-in chưa được cài đặt.​

These are mostly syntax errors, so fixing them means double-checking your formula and correcting range name or function name misspellings, or inserting missing quotation marks or colons. Also, be sure to define any range names you use and to install the appropriate add-in modules for functions you use.
Đây hầu hết là các lỗi cú pháp, do đó sửa chúng nghĩa là kiểm tra kỹ lại công thức, hiệu chỉnh tên dãy hoặc tên hàm bị nhập sai, hoặc chèn thêm các dấu nháy kép, dấu hai chấm bị thiếu... Bạn cũng phải bảo đảm rằng bạn đã định nghĩa các tên dãy mà bạn sử dụng, và đã cài đặt các Add-in thích hợp cho những hàm mà bạn dùng.​

 

[BNTT]

5.1. Understanding Excel’s Error Values


#NULL!

Excel displays the #NULL! error in a very specific case: when you use the intersection operator (a space) on two ranges that have no cells in common. For example, the ranges A1:B2 and C34 have no common cells, so the following formula returns the #NULL! error:
Excel hiển thị lỗi #NULL! trong một trường hợp rất riêng biệt: khi bạn dùng toán tử giao (một khoảng trắng) trên hai dãy không giao nhau (không có các ô chung). Ví dụ, dãy A1:B2 và dãy C34 không có ô nào chung cả, nên công thức sau đây sẽ trả về lỗi #NULL!:

= SUM(A1:B2 C34)​

Check your range coordinates to ensure that they’re accurate. In addition, check to see if one of the ranges has been moved so that the two ranges in your formula no longer intersect.
(Bạn hãy) kiểm tra lại các tọa độ dãy để bảo đảm rằng chúng chính xác. Ngoài ra, viêc kiểm tra còn để xem có phải nguyên nhân làm cho hai dãy trong công thức của bạn không còn giao nhau có phải là một trong hai dãy đã bị di chuyển (đi chỗ khác) hay không. ​

 

[BNTT]

5.1. Understanding Excel’s Error Values


#NUM!

The #NUM! error means there’s a problem with a number in your formula. This almost always means that you entered an invalid argument in a math or trig function. For example, you entered a negative number as the argument for the SQRT() or LOG() function. Check the formula’s input cells — particularly those cells used as arguments for mathematical functions — to make sure the values are appropriate.
Lỗi #NUM! có nghĩa là có vấn đề gì đó với một con số trong công thức của bạn. Lỗi này thường luôn có nghĩa là bạn đã nhập một đối số không hợp lệ trong một hàm toán học hay một một hàm lượng giác. Ví dụ, bạn nhập một số âm làm đối số cho hàm SQRT() hay hàm LOG(). Hãy kiểm tra lại các ô nhập liệu của công thức — đặc biệt là những ô sử dụng làm đối số cho các hàm toán học — để bảo đảm rằng các giá trị này thích hợp.

The #NUM! error also appears if you’re using iteration (or a function that uses iteration) and Excel can’t calculate a result. There could be no solution to the problem, or you might need to adjust the iteration parameters.
Lỗi #NUM! còn xuất hiện khi bạn sử dụng sự lặp đi lặp lại (hoặc một công thức sử dụng sự lặp đi lặp lại) mà Excel không thể tính được kết quả. Không có giải pháp nào cho vấn đề này trừ phi bạn sửa lại các tham số bị lặp.

➔ To learn more about iteration, see "Using Iteration and Circular References"
Để tìm hiểu thêm về sự lặp đi lặp lại, bạn xem bài "Sử dụng sự lặp lại và các tham chiếu tuần hoàn"​

 

[BNTT]

5.1. Understanding Excel’s Error Values


#REF!

The #REF! error means that your formula contains an invalid cell reference, which is usually caused by one of the following actions:
Lỗi #REF! có nghĩa là công thức của bạn chứa một tham chiếu ô không hợp lệ, điều này thường gặp phải do một trong các hành động sau đây:

• You deleted a cell to which the formula refers. You need to add the cell back in or adjust the formula reference.
  Bạn xóa một ô mà công thức tham chiếu đến. Bạn cần phải trả lại ô đó (vào bảng tính) hoặc điều chỉnh tham chiếu của công thức.

• You cut a cell and then pasted it in a cell used by the formula. You need to undo the cut and paste the cell elsewhere. (Note that it’s okay to copy a cell and paste it on a cell used by the formula.)
  Bạn cắt (cut) một ô rồi dán (paste) nó vào một ô được sử dụng cho công thức. Bạn cần phải quay lại (undo) việc cắt này và (nếu cần thì) dán nó vào một ô ở chỗ khác. (Lưu ý rằng, việc copy một ô và dán nó vào một ô được sử dụng cho công thức thì lại không sao cả.)

• Your formula references a nonexistent cell address, such as B0. This can happen if you cut or copy a formula that uses relative references and paste it in such a way that the invalid cell address is created. For example, suppose that your formula references cell B1. If you cut or copy the cell containing the formula and paste it one row higher, the reference to B1 becomes invalid because Excel can’t move the cell reference up one row.
  Công thức của bạn tham chiếu đến một địa chỉ ô không hiện hữu, như là B0 (B zero). Điều này có thể xảy ra nếu bạn cắt hoặc copy một công thức đang sử dụng các tham chiếu tương đối và dán nó theo một cách (nào đó) tạo ra một địa chỉ ô không hợp lệ. Ví dụ, giả sử công thức của bạn đang tham chiếu đến ô B1. Nếu bạn cắt hay copy ô chứa công thức này và dán nó cao hơn một hàng, tham chiếu đến B1 trở thành không hợp lệ bởi vì Excel không thể di chuyển ô tham chiếu đó lên một hàng.

 

[BNTT]

5.1. Understanding Excel’s Error Values


#VALUE!

When Excel generates a #VALUE! error, it means you’ve used an inappropriate argument in a function. This is most often caused by using the wrong data type. For example, you might have entered or referenced a string value instead of a numeric value. Similarly, you might have used a range reference in a function argument that requires a single cell or value. Excel also generates this error if you use a value that’s larger or smaller than Excel can handle. (Excel can work with values between –1E–307 and 1E+307.)
Khi Excel đưa ra một lỗi #VALUE!, thì có nghĩa là bạn đã sử dụng một đối số không thích hợp trong một hàm. Điều này thường xảy ra nhiều nhất là do bạn sử dụng nhầm kiểu dữ liệu. Ví dụ, lẽ ra phải nhập một giá trị số, bạn lại nhập vào một giá trị chuỗi. Tương tự, bạn đã sử dụng một tham chiếu dãy cho một đối số của một hàm, trong khi đối số đòi hỏi phải là một ô đơn lẻ hoặc một giá trị đơn lẻ. Excel cũng đưa ra lỗi này khi bạn sử dụng một giá trị vượt quá khả năng xử lý của Excel (Excel chỉ có thể làm việc với các số trong khoảng từ -1E-307 cho đến 1E+307 thôi).

In all these cases, you solve the problem by double-checking your function arguments to find and edit the inappropriate arguments.
Trong tất cả những trường hợp này, bạn giải quyết vấn đề bằng cách kiểm tra kỹ lại các đối số của công thức và sửa lại các đối số không phù hợp.​

 

[BNTT]

Chapter 5 - TROUBLESHOOTING FORMULAS


5.2. Fixing Other Formula Errors
Xử lý các lỗi khác của công thức

Not all formula errors generate one of Excel’s seven error values. Instead, you might see a warning dialog box from Excel (for example, if you try to enter a function without including a required argument). Or, you might not see any indication that something is wrong. To help you in these situations, the following sections cover some of the most common formulas errors.
Không phải tất cả các lỗi công thức chỉ là một trong 7 giá trị lỗi của Excel. Bạn có thể sẽ thấy những hộp thoại cảnh báo lỗi khác nữa (ví dụ, bạn cố nhập một hàm mà không đưa vào một đối số bắt buộc). Hoặc, bạn có thể chẳng thấy một dấu hiệu nào báo rằng có cái gì đó là không ổn (nhưng công thức của bạn vẫn có lỗi). Để giúp bạn trong những tình huống này, những phần tiếp theo đây trình bày một số lỗi công thức phổ biến nhất.​

5.2.1. Missing or Mismatched Parentheses
Quên hoặc thiếu các dấu ngoặc đơn

If you miss a parenthesis when typing a formula, or if you place a parenthesis in the wrong location, Excel usually displays a dialog box like the one shown in Figure 5.1 when you attempt to confirm the formula. If the edited formula is what you want, click Yes to have Excel enter the corrected formula automatically; if the edited formula is not correct, click No and edit the formula by hand.
Nếu bạn thiếu một dấu ngoặc đơn khi nhập một công thức, hoặc nếu bạn đặt một dấu ngoặc đơn sai vị trí, Excel thường hiển thị một hộp thoại như minh họa ở hình 5.1 khi bạn cố xác nhận công thức. Nếu thấy công thức (do Excel gợi ý trong hộp thoại) là đúng những gì bạn muốn, bạn nhấn Yes để Excel tự động sửa lại công thức cho bạn; còn nếu thấy công thức đó sai, bạn nhấn No và tự sửa lại công thức.

Figure 5.1 (hình 5.1)​

To help you avoid missing or mismatched parentheses, Excel provides two visual clues in the formula itself when you’re editing it:
Để giúp bạn tránh việc bị quên hoặc thiếu các dấu ngoặc đơn, Excel cung cấp hai gợi ý trực quan (có thể thấy được ngay) trong chính công thức khi bạn đang sửa (hoặc nhập) công thức:

• The first clue occurs when you type a right parenthesis. Excel highlights both the right parenthesis and its corresponding left parenthesis. If you type what you think is the last right parenthesis and Excel doesn’t highlight the first left parenthesis, your parentheses are unbalanced.
  Gợi ý đầu tiên xuất hiện khi bạn nhập dấu đóng ngoặc đơn đầu tiên ở bên phải (của công thức). Excel sẽ làm nổi bật lên (giống như là tô đậm lên) dấu mở ngoặc đơn tương ứng ở đầu bên trái (của công thức). Nếu như bạn nhập một dấu dóng ngoặc đơn mà bạn nghĩ là dấu cuối cùng rồi, trong khi Excel không làm nổi bật cái dấu mở ngoặc đơn đầu tiên bên trái, thì như vậy có nghĩa là các cặp dấu đóng mở ngoặc đơn của bạn chưa cân bằng (thiếu, hoặc thừa).

• The second clue occurs when you use the left and right arrow keys to navigate a formula. When you cross over a parenthesis, Excel highlights the other parenthesis in the pair and formats both parentheses with the same color.
  Gợi ý thứ hai xuất hiện khi bạn dùng những phím mũi tên sang phải, sang trái để chạy qua chạy lại trong công thức. Khi bạn chạy ngang qua một dấu ngoặc đơn, Excel sẽ làm nổi bật dấu ngoặc đơn tương ứng ở đầu bên kia và định dạng cho cả hai dấu (đóng và mở) cùng một màu nào đó.

 

[BNTT]

5.2. Fixing Other Formula Errors


5.2.2. Erroneous Formula Results
Sai sót ở các kết quả của công thức

If a formula produces no warnings or error values, the result might still be in error. If the result of a formula is incorrect, here are a few techniques that can help you understand and fix the problem:
Cho dù một công thức không tạo ra các cảnh báo hoặc giá trị lỗi, kết quả của nó vẫn có thể bị lỗi. Sau đây là một số kỹ thuật giúp bạn tìm hiểu và khắc phục những lỗi ở kết quả của công thức:

• Calculate complex formulas one term at a time. In the formula bar, select the expression you want to calculate, and then press F9. Excel converts the expression into its value. Make sure that you press the Esc key when you’re done, to avoid entering the formula with just the calculated values.
  Tính toán các công thức phức tạp mỗi lần một số hạng. Trong thanh công thức (formula bar), chọn biểu thức mà bạn muốn tính, và nhấn F9. Excel sẽ chuyển đổi biểu thức thành giá trị của nó. Hãy chắc chắn rằng bạn nhấn Esc khi hoàn tất, để tránh việc nhập công thức mà chỉ có những giá trị tính toán.
  
  
• Evaluate the formula. This feature enables you to step through the various parts of a formula.
  Lượng giá công thức. Tính năng này cho phép bạn lướt qua các phần của khác nhau của một công thức (xem nó chạy như thế nào, nhất là đối với các công thức lồng ghép phức tạp hay các công thức mảng).
  
  
• Break up long or complex formulas. One of the most complicated aspects of formula troubleshooting is making sense out of long formulas. The previous techniques can help (by enabling you to evaluate parts of the formula), but it’s usually best to keep your formulas as short as you can at first. When you get things working properly, you can combine formulas for a more efficient model.
  Phân chia các công thức dài hoặc phức tạp. Một trong những khía cạnh phức tạp nhất của việc xử lý các sự cố trong công thức là hiểu được những công thức dài. Kỹ thuật tôi nói ở trên (lượng giá các công thức) có thể giúp bạn, nhung tốt nhất, bạn nên giữ cho công thức của bạn lúc ban đầu càng ngắn càng tốt. Khi bạn đã nắm vững tốt rồi, bạn có thể kết hợp những công thức với nhau trong một mô hình hiệu quả hơn.
  
  
• Recalculate all formulas. A particular formula might display the wrong result because other formulas on which it depends need to be recalculated. This is particularly true if one or more of those formulas use custom VBA functions. Press Ctrl+Alt+F9 to recalculate all worksheet formulas.
  Tính toán lại tất cả các công thức. Một công thức cụ thể có thể hiển thị nhầm kết quả do việc tính toán lại những công thức khác có liên quan đến nó. Điều này đặc biệt đúng nếu một hay nhiều công thức này sử dụng các hàm VBA tự tạo. Để tính toán lại tất cả các công thức, bạn nhấn Ctrl+Alt+F9.
  
  
• Pay attention to operator precedence. As explained in Chapter 3, “Building Basic Formulas,” Excel’s operator precedence means that certain operations are performed before others. An erroneous formula result could therefore be caused by Excel’s precedence order. To control precedence, use parentheses.
  Chú ý các thứ tự ưu tiên của các toán hạng. Như đã nói ở chương ba, "Thiết lập những công thức (cơ bản)", thứ tự ưu tiên toán tử trong Excel nghĩa là có một số phép tính nhất định luôn được thực thi trước những phép tính khác. Một lỗi nào đó ở kết quả của công thức có thể có nguyên nhân từ cái thứ tự ưu tiên này của Excel. Để kiểm soát tốt các thứ tự ưu tiên, bạn nên dùng các dấu ngoặc đơn.
  
  
• Watch out for nonblank “blank” cells. A cell might appear to be blank, but it might actually contain data or even a formula. For example, some users “clear” a cell by pressing the spacebar, which Excel then treats as a nonblank cell. Similarly, some formulas return the empty string instead of a value (for example, see the IF() function formula I showed you earlier in this chapter for avoiding the #DIV/0! error).
  Lưu ý các ô "rỗng" mà không rỗng. Một ô có vẻ như là rỗng (bạn thấy nó trống trơn), nhưng thực sự nó có thể chứa dữ liệu hoặc thậm chí một công thức. Ví dụ, một số người "xóa" một ô bằng cách nhấn phím Space, khi đó Excel sẽ xem đây là một ô không trống (bởi vì trong đó có một ký tự rỗng, tạo nên do việc nhấn phím Space). Tương tự, có một số công thức trả về chuỗi rỗng thay vì là một giá trị (ví dụ, xem công thức hàm IF() đã được nói đến ở bài #2 trên đây, dùng để tránh lỗi #DIV/0!).
  
  
• Watch unseen values. For a large model, your formula could be using cells that you can’t see because they’re offscreen or on another sheet. Excel’s Watch Window enables you to keep an eye on the current value of one or more cells.
  Lưu ý những giá trị không thấy được. Đối với một mô hình bảng tính lớn, bạn có thể không thấy hết được các công thức trong các ô bởi vì chúng nằm ở ngoài phạm vi màn hình hoặc nằm trong các Sheet khác. Chức năng Watch Window cho phép bạn thấy được giá trị hiện hành của một hoặc nhiều ô.

 

[BNTT]

5.2. Fixing Other Formula Errors


5.2.3. Fixing Circular References
Sửa các Tham chiếu Tuần hoàn

A circular reference occurs when a formula refers to its own cell. This can happen in one of two ways:
Một Tham chiếu Tuần hoàn xảy ra khi một công thức tham chiếu đến một ô của chính nó. Điều này có thể xảy ra do một trong hai cách sau:

• Directly — The formula explicitly references its own cell. For example, a circular reference would result if the following formula were entered into cell A1:
  Trực tiếp — Công thức tham chiếu đến một ô của chính nó cách rõ ràng. Ví dụ, một tham chiếu tuần hoàn thường xuất hiện nếu công thức sau đây được nhập trong ô A1:
  
  = A1+A2​
• Indirectly — The formula references a cell or function that, in turn, references the formula’s cell. For example, suppose that cell A1 contains the following formula:
  Gián tiếp — Công thức tham chiếu đến một ô hoặc một hàm, mà ô hay hàm này lại tham chiếu đến ô chứa công thức. Ví dụ, ô A1 chứa công thức sau đây:
  
  = A5*10​
  A circular reference would result if cell A5 referred to cell A1, as in this example:
  Một tham chiếu tuần hoàn xảy ra nếu ô A5 lại tham chiếu đến A1, như trong ví dụ sau đây:
  
  = SUM(A11)​

When Excel detects a circular reference, it displays the dialog box shown in Figure 5.2.
Khi Excel phát hiện ra một tham chiếu tuần hoàn, nó sẽ hiển thị hộp thoại như ở hình 5.2.

Figure 5.2 (hình 5.2)​

When you choose OK, Excel displays tracer arrows that connect the cells involved in the circular reference. Knowing which cells are involved enables you to correct the formula in one of them to solve the problem.
Khi bạn nhấn OK, Excel sẽ hiển thị ra các mũi tên tracer, liên kết các ô liên quan trong tham chiếu tuần hoàn. Biết các ô nào có liên quan với nhau sẽ cho phép bạn sửa công thức ở một trong các ô đó để giải quyết vấn đề.​

 

[BNTT]

Chapter 5 - TROUBLESHOOTING FORMULAS


5.3. Handling Formula Errors with IFERROR()
Xử lý lỗi công thức bằng hàm IFERROR()

Earlier you saw how to use the IF() function to avoid a #DIV/0! error by testing the value of the formula divisor to see if it equals 0. This works fine if you can anticipate the specific type of error the user may make. However, there will be many instances where you can’t know the exact nature of the error in advance. For example, the simple formula =GrossProfit/Sales may generate a #DIV/0! error if Sales equals 0. However, it may also generate a #NAME? error if the name GrossProfit or the name Sales no longer exists, or it may generate a #REF! error if the cells associated with one or both of GrossProfit and Sales were deleted.
Ở 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.

If you want to handle errors gracefully in your worksheets, it’s often best to assume that any error can occur. Fortunately, that doesn’t mean you have to construct complex tests using deeply nested IF() functions that check for every error type (#DIV/0!, #N/A, and so on). Instead, Excel enables you to use a simple test for any error.
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.

In previous versions of Excel, you would use the ISERROR(value) function, where value is an expression: If value generates any error, ISERROR() returns True; if value doesn’t generate an error, ISERROR() returns False. You would then incorporate this into an IF() test, using the following general syntax:
Trong các phiên bản trước của Excel (từ Excel 2003 trở về trước), bạn đã biết sử dụng hàm ISERROR(value), trong đó value là một biểu thức: nếu value tạo ra bất kỳ lỗi nào, ISERROR() trả về giá trị TRUE, còn nếu value không tạo ra lỗi, ISERROR() trả về giá trị FALSE; sau đó bạn đưa hàm này vào một phép thử IF(), bằng cách sử dụng cú pháp sau:

=IF(ISERROR(expression), ErrorResult, expression)

If expression generates an error, this formula returns the ErrorResult value (such as the null string or an error message); otherwise, it returns the result of expression. Here’s an example that uses the GrossProfit/Sales expression:
Nếu expression tạo ra một lỗi, công thức này trả về giá trị ErrorResult (chẳng hạn như chuỗi rỗng hoặc một thông báo lỗi); còn nếu không, nó trả về kết quả của expression. Sau đây là một ví dụ sử dụng biểu thức GrossProfit/Sales:

=IF(ISERROR(GrossProfit / Sales), "", GrossProfit / Sales)

The problem with using IF() and ISERROR() to handle errors is that it requires you to input the expression twice: once in the ISERROR() function and again as the False result in the IF() function. This not only takes longer to input, but it also makes your formulas harder to maintain because if you make changes to the expression, you have to change both instances.
Vấn đề với việc sử dụng IF() và ISERROR() để xử lý các lỗi là đòi hỏi bạn nhập biểu thức (expression) hai lần: một lần trong hàm ISERROR() và một lần nữa trong dạng kết quả FALSE của hàm IF(). Điều này không chỉ mất thời gian để nhập mà còn làm cho các công thức khó xử lý hơn, bởi vì nếu bạn thực hiện các thay đổi với các biểu thức, bạn phải thay đổi cả hai trường hợp.

Excel 2007 makes handling formula errors much easier by introducing the IFERROR() function that essentially combines IF() and ISERROR() into a single function:
Excel 2007 làm cho việc xử lý các lỗi công thức trở nên dễ dàng hơn bằng cách giới thiệu hàm IFERROR(), mà về cơ bản chính là kết hợp giữa hàm IF() và hàm ISERROR() thành một hàm duy nhất:

IFERROR(value, value_if_error)

If the value expression doesn’t generate an error, IFERROR() returns the expression result; otherwise, it returns value_if_error (which might be the null string or an error message). Here’s an example:
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), "")

As you can see, this is much better than using IF() and ISERROR() because it’s shorter, easier to read, and easier to maintain because you only use your expression once.
Như bạn thấy đấy, điều này tốt hơn cách dùng IF() và ISERROR() nhiều, bởi vì nó ngắn hơn, dễ đọc hơn, và dễ xử lý công thức hơn bởi vì bạn chỉ sử dụng biểu thức có một lần.​

Xem thêm: Hàm IFERROR(), Hàm ISERROR()​

 

[BNTT]

Chapter 5 - TROUBLESHOOTING FORMULAS


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

If you use Microsoft Word, you’re probably familiar with the wavy green lines that appear under words and phrases that the grammar checker has flagged as being incorrect. The grammar checker operates by using a set of rules that determine correct grammar and syntax. As you type, the grammar checker operates in the background and constantly monitors your writing. If something you write goes against one of the grammar checker’s rules, the wavy line appears to let you know there’s a problem.
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 has a similar feature: the formula error checker. It’s similar to the grammar checker, in that it uses a set of rules to determine correctness, and it operates in the background to monitor your formulas. If it detects something amiss, it displays an error indicator — a green triangle — in the upper-left corner of the cell containing the formula, as shown in Figure 5.3.
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 5.3

Figure 5.3 (hình 5.3)​

 

[BNTT]

5.4. Using the Formula Error Checker


5.4.1. Choosing an Error Action
Chọn một cách xử lý lỗi

When you select the cell, Excel displays a smart tag beside it. If you hover your mouse pointer over the icon, a pop-up message describes the error, as shown in Figure 5.4. The smart tag drop-down list contains the following actions:
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 5.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 — Choose this option to get information on the error via the Excel Help system.
  Tìm hiểu thông tin về lỗi qua hệ thống Help của Excel.
  
  
• Show Calculation Steps — Choose this option to run the Evaluate Formula feature.
  Chạy chức năng Evaluate Formula (đánh giá công thức).
  
  
• Ignore Error — Choose this option to leave the formula as is.
  Bỏ qua, giữ nguyên công thức sai như vậy.
  
  
• Edit in Formula Bar — Choose this option to display the formula in Edit mode in the formula bar. This enables you to fix the problem by editing the formula.
  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 — Choose this option to display the Error Checking tab of the Options dialog box (discussed next).
  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).

Figure 5.4 (hình 5.4)​

 

[BNTT]

5.4. Using the Formula Error Checker


5.4.2. Setting Error Checker Options
Thiết lập các tùy chọn cho việc kiểm tra lỗi

Like Word’s grammar checker, Excel’s Formula Error Checker has a number of options that control how it works and which errors it flags. To see these options, you have two choices:
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:

• Choose Office, Excel Options to display the Excel Options dialog box, and then click Formulas.
  Chọn Office, Excel Options để hiển thị hộp thoại Excel Options, và chọn Formulas
  
  
• Choose Error-Checking Options in the smart tag’s drop-down list (as described in the previous section).
  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).

Either way, the options appear in the Error Checking and Error Checking Rules sections, as shown in Figure 5.5.
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.5:

Figure 5.5 (hình 5.5)​

Here’s a rundown of the available options:
Sau đây là tóm tắt về những tùy chọn:

• Enable Background Error Checking — This check box toggles the formula error checker’s background operation on and off. If you turn off the background checking, you can run a check at any time by choosing Formulas, 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 — Use this color palette to click the color of the error indicator.
  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 — If you’ve ignored one or more errors, you can redisplay the error indicators by clicking this button.
  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 — When this check box is activated, the formula error checker flags formulas that evaluate to #DIV/0!, #NAME?, or any of the other error values discussed earlier.
  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 — When this check box is activated, Excel examines the formulas in a table’s calculated column and flags any cells with a formula that has a different structure than the other cells in the column. The smart tag for this error includes the command Restore to Calculated Column Formula, which enables you to update the formula so that it’s consistent with the rest of the column.
  (Tùy chọn mới ở phiên bản Excel 2007) 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 — When this check box is activated, the formula error checker flags formulas that contain date text strings in which the year contains only two digits (a possibly ambiguous situation because the string could refer to a date in either the 1900s or the 2000s). In such a case, the list of options supplied in the smart tag contains two commands — Convert XX to 19XX and Convert XX to 20XX —that enable you to convert the two-digit year to a four-digit year.
  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 — When this check box is activated, the formula error checker flags cells that contain a number that is either formatted as text or preceded by an apostrophe. In such a case, the list of options supplied in the smart tag contains the Convert to Number command to convert the test to its numeric equivalent.
  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ố).

(còn một bài nữa)​

 

[BNTT]

5.4. Using the Formula Error Checker


5.4.2. Setting Error Checker Options (continued)
Thiết lập các tùy chọn cho việc kiểm tra lỗi (tiếp theo bài trước)

• Formulas Inconsistent with Other Formulas in the Region — When this check box is activated, the formula error checker flags formulas that are structured differently from similar formulas in the surrounding area. For example, consider the worksheet shown in Figure 5.6. In the SALES TOTAL row (row 7), the totals for Jan, Feb, and Mar (cells B7, C7, and D7) are all derived by summing the cells above. For example, the formula in cell D7 is =SUM(D46). This is also true of the sums in cells F7, G7, and H7. However, the formula in cell E7 is =SUM(B77). In other words, this cell sums the row values instead of the column values. This isn’t incorrect, but it is inconsistent and could lead to problems (for example, if someone tried to AutoFill cell E7 to the left or right). In such a case, the list of options supplied in the smart tag contains a command such as Copy Formula from Left to bring the formula into consistency with the surrounding 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 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 5.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.
  
  

  
  Figure 5.6 (hình 5.6)​
  
  
• Formulas Which Omit Cells in a Region — When this check box is activated, the formula error checker flags formulas that omit cells that are adjacent to a range referenced in the formula. For example, suppose that the formula is =AVERAGE(A2:A10), where A2:A10 is a range of numeric values. If cell A1 also contains a numeric value, the formula error checker flags the formula to alert you to the possibility that you missed including cell A1 in the formula. In such a case, the list of options supplied in the smart tag will contain the command Update Formula to Include Cells to adjust the formula automatically.
  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 — When this check box is activated, the formula error checker flags formulas that reside in unlocked cells. This isn’t an error so much as a warning that other people could tamper with the formula even after you have protected the sheet. In such a case, the list of options supplied in the smart tag will contain the command Lock Cell to lock the cell and prevent users from changing the formula after you protect the sheet.
  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 — When this check box is activated, the formula error checker flags formulas that reference empty cells. In such a case, the list of options supplied in the smart tag will contain the command Trace Empty Cell to enable you to find the empty cell. (At this point, you can either enter data into the cell or adjust the formula so that it doesn’t reference the cell.)
  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 — When this check box is activated, the formula error checker flags cells that violate a table’s data-validation rules. This can happen if you set up a data-validation rule with only a Warning or Information style, in which case the user can still opt to enter the invalid data. In such cases, the formula error checker will flag the cells that contain invalid data. The smart tag list includes the Display Type Information command that shows the data validation rule that the cell data violates.
  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ệnh Display Type Information, hiển thị quy tắc Data-validation mà những ô đó vi phạm.

 

[BNTT]

Chapter 5 - TROUBLESHOOTING FORMULAS


5.5. Auditing a Worksheet
Kiểm tra một bảng tính

As you’ve seen, some formula errors are the result of referencing other cells that contain errors or inappropriate values. The first step in troubleshooting these kinds of formula problems is to determine which cell or (or group of cells) is causing the error. This is straightforward if the formula references only a single cell, but it gets progressively more difficult as the number of references increases. (Another complicating factor is the use of range names because it won’t be obvious which range each name is referencing.)
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).

To determine which cells are wreaking havoc on your formulas, you can use Excel’s auditing features to visualize and trace a formula’s input values and error sources.
Để 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.5.1. Understanding Auditing
Tìm hiểu việc Kiểm tra bảng tính

Excel’s formula-auditing features operate by creating tracers — arrows that literally point out the cells involved in a formula. You can use tracers to find three kinds of cells:
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 — These are cells that are directly or indirectly referenced in a formula. For example, suppose that cell B4 contains the formula =B2; then B2 is a direct precedent of B4. Now suppose that cell B2 contains the formula =A2/2; this makes A2 a direct precedent of B2, but it’s also an indirect precedent of cell B4.
  Đâ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 — These are cells that are directly or indirectly referenced by a formula in another cell. In the preceding example, cell B2 is a direct dependent of A2, and B4 is an indirect dependent of A2.
  Đâ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 — These are cells that contain an error value and are directly or indirectly referenced in a formula (and therefore, cause the same error to appear in the formula).
  Đâ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).

Figure 5.7 shows a worksheet with three examples of tracer arrows:
Hình 5.7 minh họa một bảng tính với ba ví dụ của các mũi tên tracer:

Figure 5.7 (hình 5.7)​

• Cell B4 contains the formula =B2, and B2 contains =A2/2. The arrows (they’re blue onscreen) point out the precedents (direct and indirect) of B4.
  Ô 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.
  
  
• Cell D4 contains the formula =D2, and D2 contains =D1/0. The latter produces the #DIV/0! error. Therefore, the same error appears in cell D4. The arrow (it’s red onscreen) is pointing out the source of the error.
  Ô 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.
  
  
• Cell G4 contains the formula =Sheet2!A1. Excel displays the dashed arrow with the worksheet icon whenever the precedent or dependent exists on a different worksheet.
  Ô 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.

 

[BNTT]

5.5. Auditing a Worksheet


5.5.2. Tracing Cell Precedents
Truy tìm các Precedent của ô

To trace cell precedents, follow these steps:
Để tìm các precedent của ô, theo các bước sau:

1. Select the cell containing the formula whose precedents you want to trace.
   Chọn ô chứa công thức mà bạn muốn truy tìm precedent của nó.
   
   
2. Choose Formulas, Trace Precedents. Excel adds a tracer arrow to each direct precedent.
   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. Keep repeating step 2 to see more levels of precedents.
   Tiếp tục lập lại bước 2 để thêm các cấp precedent (nếu có).

NOTE: You also can trace precedents by double-clicking the cell, provided that you turn off in-cell editing. You do this by choosing Office, Excel Options to display the Options dialog box, clicking Advanced, and then deactivating the Allow Editing Directly in Cells check box. Now when you double-click a cell, Excel selects the formula’s precedents.
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.5.3. Tracing Cell Dependents
Truy tìm các Dependent của ô

Here are the steps to follow to trace cell dependents:
Đây các bước để truy tìm các precedent của ô:

1. Select the cell whose dependents you want to trace.
   Chọn ô mà bạn muốn truy tìm dependent của nó.
   
   
2. Choose Formulas, Trace Dependents. Excel adds a tracer arrow to each direct dependent.
   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. Keep repeating step 2 to see more levels of dependents.
   Tiếp tục lập lại bước 2 để thêm các cấp dependent (nếu có).

5.5.4. Tracing Cell Errors
Truy tìm các nguyên nhân lỗi của ô

To trace cell errors, follow these steps:
Để tìm các nguồn gốc gây ra lỗi cho ô, theo các bước sau

1. Select the cell containing the error you want to trace.
   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. Choose Formulas, Error Checking, Trace Error. Excel adds a tracer arrow to each cell that produced the error.
   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.5. Removing Tracer Arrows
Gỡ bỏ những mũi tên Tracer

To remove the tracer arrows, you have three choices:
Để gỡ bỏ những mũi tên tracer, bạn có ba lựa chọn:

• To remove all the tracer arrows, choose Formulas, Remove Arrows.
  Để gỡ bỏ tất cảc các mũi tên tracer, chọn Formulas, Remove Arrows.
  
  
• To remove precedent arrows one level at a time, choose Formulas, drop down the Remove Arrows list, and choose Remove Precedent 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.
  
  
• To remove dependent arrows one level at a time, choose Formulas, drop down the Remove Arrows list, and choose Remove Dependent 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.

 

[BNTT]

5.5. Auditing a Worksheet


5.5.6. Evaluating Formulas
Lượng giá các công thức

Earlier, you learned that you can troubleshoot a wonky formula by evaluating parts of the formula. You do this by selecting the part of the formula you want to evaluate and then pressing F9. This works fine, but it can be tedious in a long or complex formula, and there’s always the danger that you might accidentally confirm a partially evaluated formula and lose your work.
Trong các bài trướ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).

A better solution is Excel’s Evaluate Formula feature. It does the same thing as the F9 technique, but it’s easier and safer. Here’s how it works:
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. Select the cell that contains the formula you want to evaluate.
   Chọn ô chứa công thức mà bạn muốn lượng giá.
   
   
2. Choose Formulas, Evaluate Formula. Excel displays the Evaluate Formula dialog box.
   Chọn Formulas, Evaluate Formula. Excel hiển thị hộp thoại Evaluate Formula.
   
   
3. The current term in the formula is underlined in the Evaluation box. At each step, you choose from one or more of the following buttons:
   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 — Click this button to display the current value of the underlined term.
     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 — Click this button to display the first dependent of the underlined term. If that dependent also has a dependent, choose this button again to see it (see Figure 5.8).
     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 5.8)
     
     

     
     Figure 5.8 (hình 5.8)​
     
     
   • Step Out — Click this button to hide a dependent and evaluate its precedent.
     Nhấn nút này để che bớt một dependent và lượng giá precedent của nó.
   
   
   
4. Repeat step 3 until you’ve completed your evaluation.
   Lặp lại bước 3 cho đến khi bạn hoàn tất việc lượng giá.
   
   
5. Click Close.
   Nhấn Close.

 

[BNTT]

5.5. Auditing a Worksheet


5.5.6. Watching Cell Values
Theo dõi các giá trị ô

In the precedent tracer example shown in Figure 5.7, the formula in cell G4 refers to a cell in another worksheet, which is represented in the trace by a worksheet icon. In other words, you can’t see the formula cell and the precedent cell at the same time. This could also happen if the precedent existed on another workbook or even elsewhere on the same sheet if you’re working with a large model.
Trong ví dụ về precedent tracer minh họa ở hình 5.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.

This is a problem because there’s no easy way to determine the current contents or value of the unseen precedent. If you’re having a problem, troubleshooting requires that you track down the far-off precedent to see if it might be the culprit. That’s bad enough with a single unseen cell, but what if your formula refers to 5 or 10 such cells? And what if those cells are scattered in different worksheets and workbooks?
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?

This level of hassle — not at all uncommon in the spreadsheet world — was no doubt the inspiration behind an elegant solution: the Watch Window. This window enables you to keep tabs on both the value and the formula in any cell in any worksheet in any open workbook. Here’s how you set up a watch:
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. Activate the workbook that contains the cell or cells you want to watch.
   Kích hoạt bảng tính chứa ô hoặc những ô mà bạn muốn theo dõi.
   
   
2. Choose Formulas, Watch Window. Excel displays the Watch Window.
   Chọn Formulas, Watch Window. Excel hiển thị cửa sổ Watch Window.
   
   
3. Click Add Watch. Excel displays the Add Watch dialog box.
   Nhấn Add Watch. Excel hiển thị hộp thoại Add Watch.
   
   
4. Either select the cell you want to watch, or type in a reference formula for the cell (for example, =A1). Note that you can select a range to add multiple cells to the Watch Window.
   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. Click Add. Excel adds the cell or cells to the Watch Window, as shown in Figure 5.9.
   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 5.9.
   
   

   
   Figure 5.9 (hình 5.9)​

-------------------------- END OF PART I --------------------------
Hết Phần I​