(App Tutorial) Microsoft Office Excel 2003 Inside Out

Podstrony

[ Pobierz całość w formacie PDF ]
.For example, after defin-ing the labels in Figure 12-12, you could click cell I5, type an equal sign, and then click cellC5.Normally Excel would insert the cell reference C5 in the formula, but instead the labelintersection Qtr 2 Product 2 appears.TroubleshootingI get unpredictable results from my formulas when using labelsWhen you use labels in formulas, leave a blank column or row between the worksheet tableand the formulas.Otherwise, results can be unpredictable, especially when you are workingwith more than one row or column of formulas.For example, without a blank row separatingthe formulas labeled Total and Average at the bottom of the table in Figure 12-10, the Aver-age formulas would include the values returned by the Total formulas when labels are used.Another way to solve this problem is to use defined names instead of labels in the formulas.Naming Cells and Cell RangesIf you find yourself repeatedly typing cryptic cell addresses, such as Sheet3!A1:AJ51, into for-mulas, don t worry Excel has a better way.Assign a short, memorable name to any popularcell or range, and then use that name instead of the cryptogram in formulas.368Chapter 12Part 5: Creating Formulas and Performing Data AnalysisBuilding FormulasAfter you define names in a worksheet, those names are made available to any other work-sheets in the workbook.A name defining a cell range in Sheet6, for example, is available foruse in formulas in Sheet1, Sheet2, and so on in the workbook.As a result, each workbookcontains its own set of names.You can also define worksheet-level names that are availableonly on the worksheet in which they are defined.For more information about worksheet-level names, see Workbook-Wide vs.Worksheet-Only Nameson page 372.Tip Don t define names for simple tablesIn a simple table with row and column headings, you can use the headings themselves informulas located in the same rows and columns, without first having to define names.See Creating Natural-Language Formulas on page 364.Using Names in FormulasWhen you use the name of a cell or a range in a formula, the result is the same as if youentered the cell or range address.For example, suppose you entered the formula =A1+A2 incell A3.If you defined the name Mark as cell A1 and the name Vicki as cell A2, the formula=Mark+Vicki has the same result.The easiest way to define a name follows:1 Select a cell.2 Click the Name box in the formula bar, as shown in Figure 12-13.3 Type TestName, and then press Enter.f12ie13Figure 12-13.Use the Name box in the formula bar to quickly assign names tocells and ranges.Keep the following basics in mind when using names in formulas:�% The Name box normally displays the address of the selected cell.If the selected cell orrange is named, the name takes precedence over the address and is displayed in theName box.369Chapter 12Part 5: Creating Formulas and Performing Data AnalysisMicrosoft Office Excel 2003 Inside Out�% When you define a name for a range of cells, the range name does not appear in theName box unless the entire range is selected.�% When you click the Name box and select a name, the cell selection switches to thenamed cells.�% If you type a name that has already been defined into the Name box, Excel switches theselection instead of redefining the name.�% When you define a name, the address includes the worksheet name and the cell refer-ence is absolute.For example, when you define the name TestName for cell C5 inSheet1, the actual name definition is recorded as Sheet1!$C$5.For more information about absolute references, see Understanding Relative, Absolute, and MixedReferences on page 353.Defining and Managing NamesInstead of coming up with new names for cells and ranges, you can simply use existing textlabels to create names.Choosing Insert, Name, Define, you can use text in adjacent cells todefine cell and range names, as seen in Figure 12-14.You can choose this command also toredefine existing names.Rules for NamingThe following rules apply when you name cells and ranges in Excel:�% All names must begin with a letter, a backslash (\), or an underscore (_).�% Numbers can be used.�% Spaces can t be used.�% Excel translates blank spaces in labels to underscores in defined names.�% Symbols other than backslash and underscore can t be used.�% Names that resemble cell references (for example, AB$5 or R1C7) can t be used.�% Single letters, with the exception of the letters R and C, can be used as names.A name can contain 255 characters, but if it contains more than 253 characters, you can tselect it from the Name box.Excel does not distinguish between uppercase and lowercasecharacters in names.For example, if you create the name Tax and then create the nameTAX in the same workbook, the first name is overwritten by the second.Tip Press Ctrl+F3 to display the Define Name dialog box instantly.370Chapter 12Part 5: Creating Formulas and Performing Data AnalysisBuilding FormulasIf you select the range you want to name before choosing the Insert, Name, Define command,and you are happy using the adjacent label as a name, just press Enter to define the name.Thenext time you open the Define Name dialog box, the name appears in the Names In Work-book list, which displays all the defined names for the workbook.You can define a name also without first selecting a cell or range in the worksheet.For exam-ple, in the Define Name dialog box, type Test2 in the Names In Workbook box and then type=D20 in the Refers To box.Click Add to add the name to the list.The Define Name dialog box remains open, and the Refers To box displays the name defini-tion =Sheet1!D20.Excel adds the worksheet reference for you, but note that the cell referencestays relative, just as you entered it.If you do not enter the equal sign preceding the reference,Excel interprets the definition as text.For example, if you typed D20 instead of =D20, theRefers To box would display the text constant ="D20" as the definition of the name Test2.When the Define Name dialog box is open, you can insert references in the Refers To box alsoby selecting cells in the worksheet.If you name several cells or ranges in the Define Name dia-log box, be sure to click Add after entering each definition.(If you click OK, Excel closes thedialog box.)f12ie14Figure 12-14.When you choose Insert, Name, Define, any label in an adjacent cell in thesame row or column is suggested as a name.Editing NamesTo redefine an existing cell or range name in the Define Name dialog box, first select thename in the Names In Workbook list and then edit the cell or range reference in the Refers Tobox.You can either type a new reference or select a new cell or range directly in the worksheetwhile the Define Name dialog box is open.To delete a name in the Define Name dialog box, select the name from the Names In Work-book list and then click Delete [ Pobierz całość w formacie PDF ]

zanotowane.pl

doc.pisz.pl

pdf.pisz.pl

bezglutenowo.pev.pl