[ Pobierz całość w formacie PDF ]
.The Formula Auditing toolbar (refer to Figure 9-8) contains six buttons that control differentfunctions of the cell tracers: Trace Precedents, Remove Precedent Arrows, Trace Dependents,Remove Dependent Arrows, Remove All Arrows, and Trace Error.You can also choose Tools,Formula Auditing to control the cell tracers (and to display the Formula Auditing toolbar).256Chapter 9 Part 3: Formatting and Editing WorksheetsAdvanced Formatting and Editing TechniquesUnderstanding Precedents and DependentsThe terms precedent and dependent crop up quite often in this section.They refer tothe relationships that cells containing formulas create with other cells.A lot of what aspreadsheet is all about is wrapped up in these concepts, so here s a brief descriptionof each term:Ï% Precedents are cells whose values are used by the formula in the selected cell.A cellthat has precedents always contains a formula.Ï% Dependents are cells that use the value in the selected cell.A cell that has depen-dents can contain either a formula or a constant value.For example, if the formula =SUM(A1:A5) is in cell A6, cell A6 has precedents (A1:A5) butno apparent dependents.Cell A1 has a dependent (A6), but no apparent precedents.A cellcan be both a precedent and a dependent if the cell contains a formula and is also refer-enced by another formula.Tracing Dependent CellsIn the worksheet in Figure 9-16, we selected cell B2, which contains the hourly rate value.Tofind out which cells contain formulas that use this value, you can click the Trace Dependentsbutton on the Formula Auditing toolbar.Although this worksheet is elementary, to make iteasier to illustrate the cell tracers, consider the ramifications of using the cell tracers in a largeand complex worksheet.f09ie16Figure 9-16.When you trace dependents, arrows point to formulas that directly refer to theselected cell.Note You can find the sample file used in this example, Audit.xls, on thecompanion CD.The tracer arrows indicate that cell B2 is directly referred to by the formulas in cells C5, C6,C7, and C8.A dot appears in cell B2, indicating that it is has dependents.If you click Trace257Chapter 9 Part 3: Formatting and Editing WorksheetsMicrosoft Office Excel 2003 Inside OutDependents again, another set of arrows appears, indicating the next level of dependenciesor indirect dependents.Figure 9-17 shows the results.f09ie17Figure 9-17.When you click Trace Dependents again, arrows point to the next level offormulas, ones that indirectly refer to the selected cell.You can click Remove Dependent Arrows (next to Trace Dependents) to go backward.Clickonce to remove the level of dependents you last displayed, and click again to remove thenext level.One handy feature of the tracer arrows is that you can use them to navigate, which can be areal advantage in a large worksheet.For example, in Figure 9-17 with cell B2 still selected,double-click the arrow pointing from cell B2 to cell C8.(When you move the mouse pointerover a tracer arrow, it becomes arrow-shaped.) The selection jumps to the other end of thearrow, and cell C8 becomes the active cell.Now if you double-click the arrow pointing fromcell C8 to cell E8, the selection jumps to cell E8.If you double-click the same arrow again, theselection jumps back to cell C8 at the other end.If you double-click an arrow that extendsbeyond the screen, the window shifts to display the cell at the other end.You can use this fea-ture to jump from cell to cell along a path of precedents and dependents.Clearing Tracer ArrowsEach time you trace another cell s precedents or dependents, additional tracer arrows appear.You ll find, however, that your screen quickly becomes cluttered, making it difficult to discernthe data flow for particular cells.It s a good idea to start fresh each time you want to tracecells.To remove all the tracer arrows from the screen, click the Remove All Arrows button onthe Formula Auditing toolbar.Tracing Precedent CellsYou can also trace in the opposite direction by starting from a cell that contains a formula andtracing the cells that are referred to in the formula.In Figure 9-18 on the next page, weselected cell E5, which contains one of the net wages formulas.To find out which cells thisformula refers to, we clicked Trace Precedents.258Chapter 9 Part 3: Formatting and Editing WorksheetsAdvanced Formatting and Editing Techniquesf09ie18Figure 9-18.When you trace precedents, arrows point from all the cells that the formula inthe selected cell directly refers to.This time, an arrow appears with dots in cells C5 and D5.The dots identify these cells asprecedents in the data flow.(The appearance of dots in both C5 and D5 indicates that bothcells are equally precedent to the selected cell.) Notice that the arrow still points in the samedirection toward the formula and in the direction of the data flow even though westarted from the opposite end of the path.To continue the trace, click the Trace Precedentsbutton again.Figure 9-19 shows the results.f09ie19Figure 9-19.When you trace precedents again, arrows point from the next (indirect) level ofcells that the formula in the selected cell refers to.Tracing ErrorsSuppose your worksheet displays error values, like the ones shown in Figure 9-20.To traceone of these errors back to its source, select a cell that contains an error.Notice that the cells containing errors display smart tag indicators in the upper left corner ofeach cell, as shown in Figure 9-21, and when you select one of them, the smart tag actions menuappears.The smart tag actions menu displays applicable options, including Trace Error.259Chapter 9 Part 3: Formatting and Editing WorksheetsMicrosoft Office Excel 2003 Inside Outf09ie20Figure 9-20.Cells with error values display smart tag action menus.f09ie21Figure 9-21.Select a cell that contains an error value and click Trace Error to display arrowsthat trace the error back to its source.Excel selects the cell that contains the first formula in the error chain and draws red arrowsfrom that cell to the cell you selected.Excel draws blue arrows to the cell that contains the firsterroneous formula from the values the formula uses.It s up to you to determine the reasonfor the error; Excel takes you to the source formula and shows you the precedents.In ourexample, the error is caused by a space character inadvertently entered in cell B6, replacingthe hours-worked figure.This is a common, vexing problem, because cells containing spacecharacters appear to be empty, but a truly empty cell would not have produced an error inthis case.Tracing References to Other WorksheetsIf a cell contains a reference to a different worksheet or to a worksheet in another workbook,a dashed tracer arrow appears with a small icon attached, as shown in Figure 9-22.You can-not continue to trace precedents using the same procedure from the active cell when a dashedtracer arrow appears.If you double-click a dashed tracer arrow, the Go To dialog box appears, with the referencedisplayed in the Go To list, as shown in Figure 9-23 [ Pobierz caÅ‚ość w formacie PDF ]

  • zanotowane.pl
  • doc.pisz.pl
  • pdf.pisz.pl
  • funlifepok.htw.pl

    •