Microsoft Office Excel 2003 Inside Out

[ Pobierz całość w formacie PDF ]
.Auditing and Documenting WorksheetsExcel has a number of powerful and flexible features that help you audit and debug yourworksheets and document your work.In this section, you explore the following features: celltracers, error checking, Formula Auditing mode, the Evaluate Formula dialog box, the WatchWindow, Text To Speech, the Comment command, and Go To Special.Most of Excel s auditing features can be accessed via the Formula Auditing toolbar, which isshown in Figure 9-8.You display the Formula Auditing toolbar by choosing Formula Audit-ing on the Tools menu and then clicking Show Formula Auditing Toolbar.f09ie08Figure 9-8.The Formula Auditing toolbar provides access to most of Excel sauditing features.251Chapter 9Part 3: Formatting and Editing WorksheetsMicrosoft Office Excel 2003 Inside OutChecking for ErrorsChoose Tools, Error Checking (or click the Error Checking button on the Formula Auditingtoolbar) to quickly find any error values displayed on the current worksheet and display theError Checking dialog box, as shown in Figure 9-9.The first erroneous cell in the worksheetis selected and its contents are displayed in the dialog box, along with a suggestion about thenature of the problem.f09ie09Figure 9-9.The Error Checking dialog box helps you figure out what s wrong with formulasthat display error values.When your problem appears in the dialog box, the following selections are available:�% Help On This Error displays a Help topic relating to the problem cell.�% Show Calculation Steps displays the Evaluate Formula dialog box.See Evaluating andAuditing Formulas on page 253.�% Ignore Error skips over the selected cell.To un-ignore errors, click Options and thenclick Reset Ignored Errors.�% Edit In Formula Bar opens the selected cell in the formula bar for editing.When you refinished, click Resume (the Help On This Error button changes to Resume).Click the Previous and Next buttons to locate additional errors on the current worksheet.Clickthe Options button to display the Options dialog box shown in Figure 9-10.Select or clearoptions in the dialog box to determine the kinds of problems you want to find when youchoose Error Checking.Click the Reset Ignored Errors button if you want to recheck or if youclicked the Ignore Error button by mistake.252Chapter 9Part 3: Formatting and Editing WorksheetsAdvanced Formatting and Editing Techniquesf09ie10Figure 9-10.Click Options in the Error Checking dialog box to specify error-checking rules.With the Options dialog box closed, you can continue checking errors.When you haveaddressed the last error, Excel displays an alert box informing you that the error check iscomplete for the entire sheet.Click OK to clear the alert box, and the Error Checking dialogbox closes.Evaluating and Auditing FormulasSometimes it s difficult to tell what s going on in a complex nested formula.A formula isnested when parts of it (called arguments) can be calculated separately.For example, in theformula =INDEX(pacadata!$A$2:$U$43, Animal Info !$B$5,5), the reference AnimalInfo !$B$5 indicates a cell containing a number.To make this formula easier to read, you cansubstitute this reference with the number contained in the cell for example, 7.The formulawould then be =INDEX(pacadata!$A$2:$U$43,7,5).When you choose Tools, Formula Auditing, Evaluate Formula, you can check complex for-mulas easily.Figure 9-11 shows the Evaluate Formula dialog box in action.For more information about formulas and arguments, see Chapter 12, Building Formulas.Click Evaluate to replace calculable arguments with their resulting values.You can click Eval-uate as many times as necessary if your formula contains many nested levels.For example, ifyou click Evaluate in Figure 9-11, the formula displayed in the Evaluation box replaces theaforementioned Animal Info reference with its value.Clicking Evaluate a second time calcu-lates the next available level which in this case is the end result, Suri Alpaca, as shown inFigure 9-12.253Chapter 9Part 3: Formatting and Editing WorksheetsMicrosoft Office Excel 2003 Inside Outf09ie11Figure 9-11.Choose Tools, Formula Auditing, Evaluate Formula to inspect nested formulas.Note You can find the sample file used in this example, Alpacas.xls, on thecompanion CD.f09ie12Figure 9-12.Each time you click Evaluate, another nested level in the selected formula iscalculated.Eventually, clicking Evaluate results in the formula s displayed value, and the Evaluate buttonchanges to Restart, allowing you to go through the steps again.Click Step In to separate eachcalculable reference into separate boxes, making the hierarchy more apparent.In our exam-ple, the evaluated reference is to a constant, which cannot be further evaluated.If the refer-ence were to a cell containing another formula, that would appear in the Evaluate Formuladialog box, as shown in Figure 9-13.Where there are no more steps to be displayed, click StepOut to close the Step In box and replace the reference with the resulting value.254Chapter 9Part 3: Formatting and Editing WorksheetsAdvanced Formatting and Editing Techniquesf09ie13Figure 9-13 [ Pobierz całość w formacie PDF ]

Archiwum