tailieunhanh - Excel 2002 Formulas phần 4

Mỗi thời gian được tính công thức trong A3, nó phải được tính một lần nữa vì A3 đã thay đổi. Việc tính toán sẽ đi mãi mãi. Nói cách khác, câu trả lời không bao giờ được giải bạn nhập một công thức có chứa một tham chiếu vòng tròn, Excel sẽ hiển thị một hộp thoại với ba lựa chọn | Chapter 8 Lookups 223 Choosing among Multiple Lookup Tables You can of course have any number of lookup tables in a worksheet. In some cases your formula may need to decide which lookup table to use. Figure 8-9 shows an example. Figure 8-9 This worksheet demonstrates the use of multiple lookup tables. This workbook calculates sales commission and contains two lookup tables G3 H9 named Tablel and J3 K8 named Table2 . The commission rate for a particular sales representative depends on two factors the sales rep s years of service column B and the amount sold column C . Column D contains formulas that look up the commission rate from the appropriate table. For example the formula in cell D2 is VLOOKUP C2 IF B2 3 Table1 Table2 2 The second argument for the VLOOKUP function consists of an IF formula that uses the value in column B to determine which lookup table to use. The formula in column E simply multiplies the sales amount in column C by the commission rate in column D. The formula in cell E2 for example is C2 D2 ON THE CD You can access the workbook shown in Figure 8-9 on the companion CD-ROM. 224 Part 11 Using Functions in Your Formulas Determining Letter Grades for Test Scores A common use of a lookup table is to assign letter grades for test scores. Figure 8-10 shows a worksheet with student test scores. The range E2 F6 named GradeList displays a lookup table used to assign a letter grade to a test score. Figure 8-10 Looking up letter grades for test scores ON THE CD The companion CD-ROM contains a workbook that demonstrates both formulas in this section. Column C contains formulas that use the VLOOKUP function and the lookup table to assign a grade based on the score in column B. The formula in C2 for example is VLOOKUP B2 GradeList 2 When the lookup table is small as in the example shown in Figure 8-10 you can use a literal array in place of the lookup table. The formula that follows for example returns a letter grade without using a lookup table. Rather the