[ Pobierz całość w formacie PDF ]
.The Assistants, Formulas & Functions, Hide Formula Errors command dis-plays the dialog box shown below, allowing you to select a range of cells in which to hideany visible error values.G12ie03sbTo install the Spreadsheet Assistant, refer to the Start CD on the companion CD and followthe instructions on your screen.Creating References to Other Worksheets in the Same WorkbookYou can refer to cells in other worksheets within the same workbook just as easily as you referto cells in the same worksheet.For example, to enter a reference to cell A9 in Sheet2 into cellB10 in Sheet1, do this:1 Select cell B10 in Sheet1, and type an equal sign.2 Click the Sheet2 tab.3 Click cell A9, and then press Enter.354Chapter 12 Part 5: Creating Formulas and Performing Data AnalysisBuilding FormulasAfter you press Enter, Sheet1 is made active.Select cell B10, and you will see that it containsthe formula =Sheet2!A9.The worksheet portion of the reference is separated from the cell portion by an exclamationpoint.Note also that the cell reference is relative, which is the default when you select cells tocreate references to other worksheets.Creating References to Worksheets in Other WorkbooksYou can refer to cells in worksheets in separate workbooks in the same way that you refer tocells in other worksheets within the same workbook.These references are called external ref-erences.For example, to enter a reference to Book2 into Book1, follow these steps:1 Create a new workbook Book2 by clicking the New button on the Standard toolbar.2 Choose Window, Arrange, Vertical.3 Select cell A1 in Sheet1 of Book1, and type an equal sign.4 Click anywhere in the Book2 window to make the workbook active.5 Click the Sheet2 tab at the bottom of the Book2 window.6 Click cell A2.Before pressing Enter to lock in the formula, your screen should looksimilar to Figure 12-1.7 Press Enter to lock in the reference.f12ie01Figure 12-1.Enter external references easily by clicking the cell to which youwant to refer.Understanding Row-Column Reference StyleIn R1C1 reference style, both rows and columns are numbered.The cell reference R1C1means row 1, column 1; therefore, R1C1 and A1 refer to the same cell.Although R1C1 refer-ence style isn t widely used anymore, it was the standard in some spreadsheet programs, suchas Multiplan.The normal reference style in Excel assigns letters to columns and numbers torows, such as A1 or Z100.To turn on the R1C1 reference style, choose Tools, Options, click the General tab, and selectthe R1C1 Reference Style option.The cell references in all your formulas automaticallychange to R1C1 format.For example, cell M10 becomes R10C13, and cell IV65536, the lastcell in your worksheet, becomes R65536C256.In R1C1 notation, relative cell references are displayed in terms of their relationship to thecell that contains the formula rather than by their actual coordinates.This can be helpfulwhen you are more interested in the relative position of a cell than in its absolute position.355Chapter 12 Part 5: Creating Formulas and Performing Data AnalysisMicrosoft Office Excel 2003 Inside OutFor example, suppose you want to enter in cell R10C2 (B10) a formula that adds cells R1C1(A1) and R1C2 (B1).After selecting cell R10C2, type an equal sign, select cell R1C1, type aplus sign, select cell R1C2, and then press Enter.Excel displays =R[ 9]C[ 1]+R[ 9]C.Nega-tive row and column numbers indicate that the referenced cell is above or to the left of theformula cell; positive numbers indicate that the referenced cell is below or to the right of theformula cell.The brackets indicate relative references.This formula reads, Add the cell ninerows up and one column to the left to the cell nine rows up in the same column. A relative reference to another cell must include brackets.Otherwise, Excel assumes you reusing absolute references.For example, the formula =R8C1+R9C1 uses absolute referencesto the cells in rows 8 and 9 of column 1.How Copying Affects Cell ReferencesOne of the handiest things about using references is the capability to copy and paste formu-las.But you need to understand what happens to your references after you paste so that youcan create formulas with references that operate the way you want them to.Copying Relative References When you copy a cell containing a formula with relative cellreferences, the references change automatically, relative to the position of the cell where you pastethe formula.Referring to Figure 12-2, suppose you type the formula =AVERAGE(B4:E4) in cellF4.This formula averages the values in the four-cell range that begins four columns to the left ofcell F4.f12ie02Figure 12-2.Cell F4 contains relative references to the cells to its left.Note You can find the sample file used in this example, Exam.xls, on thecompanion CD.You want to repeat this calculation for the remaining rows as well.Instead of typing a newformula in each cell in column F, you select cell F4 and choose Edit, Copy.Then you selectcells F5:F8, choose Edit, Paste Special, and then select the Formulas and Number Formatsoption (to preserve the formatting).The results are shown in Figure 12-3.Because the for-mula in cell F4 contains a relative reference, Excel adjusts the references in each copy of theformula.As a result, each copy of the formula calculates the average of the cells in the corre-sponding row.For example, cell F7 contains the formula =AVERAGE(B7:E7).356Chapter 12 Part 5: Creating Formulas and Performing Data AnalysisBuilding Formulasf12ie03Figure 12-3.We copied the relative references from cell F4 to cells F5:F8.Copying Absolute References If you want cell references to remain the same when youcopy them, use absolute references.For example, in the worksheet on the left in Figure 12-4,cell B2 contains the hourly rate at which employees are to be paid, and cell C5 contains therelative reference formula =B2*B5.Suppose that you want to copy the formula in C5 to therange C6:C8.The worksheet on the right in Figure 12-4 shows what happens if you copy theexisting formula to this range: You get erroneous results.Although the formulas in cellsC6:C8 should refer to cell B2, they don t.For example, cell C8 contains the incorrect formula=B5*B8.f12ie04Figure 12-4.The formula in cell C5 contains relative references.We copied the relative for-mula in cell C5 to cells C6:C8, producing incorrect results.Note You can find the sample file used in this example, Wages.xls, on thecompanion CD.Because the reference to cell B2 in the original formula is relative, it changes as you copy theformula to the other cells.To correctly apply the wage rate in cell B2 to all the calculations,you must change the reference to cell B2 to an absolute reference before you copy the formula.To change the reference style, click the formula bar, click the reference to cell B2, and thenpress F4.The result is the following formula: =$B$2*B5.When you copy this modified formula to cells C6:C8, the second cell reference, but notthe first, is adjusted within each formula.In Figure 12-5, cell C8 now contains the correctformula: =$B$2*B8 [ Pobierz całość w formacie PDF ]