Một ngoại lệ đáng chú ý bằng cách sử dụng một mảng liên tục ở vị trí của một tài liệu tham khảo trong phạm vi chức năng một là với các chức năng cơ sở dữ liệu sử dụng một tham chiếu đến một loạt các tiêu chuẩn . Thật không may, bằng cách sử dụng một mảng liên tục | Chapter 21 Tools and Methods for Debugging Formulas 567 Pay Attention to the Colors When you edit a cell that contains a formula Excel color-codes the cell and range references in the formula. Excel also outlines the cells and ranges used in the formula by using corresponding colors. Therefore you can see at a glance the cells that are used in the formula. You also can manipulate the colored outline to change the cell or range reference. To change the references that are used drag the outline s border or fill handle at the lower-right corner of the outline . VALUE ERRORS The VALUE error is very common and can occur under the following conditions An argument for a function is of an incorrect data type or the formula attempts to perform an operation using incorrect data. For example a formula that adds a value to a text string returns the VALUE error. A function s argument is a range when it should be a single value. A custom worksheet function is not calculated. With some versions of Excel inserting or moving a sheet may cause this error. You can use Ctrl Alt F9 to force a recalculation. A custom worksheet function attempts to perform an operation that is not valid. For example custom functions cannot modify the Excel environment or make changes to other cells. You forget to press Ctrl Shift Enter when entering an array formula. Absolute Relative Reference Problems As described in Chapter 2 a cell reference can be relative for example A1 absolute for example A 1 or mixed for example A1 or A 1 . The type of cell reference that you use in a formula is relevant only if the formula will be copied to other cells. A common problem is to use a relative reference when you should use an absolute reference. As shown in Figure 21-2 cell C1 contains a tax rate which is used in the formulas in column C. The formula in cell C4 is B4 B4 C 1 568 Part V Miscellaneous Formula Techniques Figure 21-2 Formulas in the range C4 C6 use an absolute reference to cell C1. Notice that the .
