[ Pobierz całość w formacie PDF ]
.We created an absolute reference to cell B2 before copying the formula.Copying Mixed References You can use mixed references in your formulas to anchor aportion of a cell reference.(In a mixed reference, one portion is absolute and the other is rel-ative.) When you copy a mixed reference, Excel anchors the absolute portion and adjusts therelative portion to reflect the location of the cell to which you copied the formula.To create a mixed reference, you can press the F4 key to cycle through the four combinationsof absolute and relative references for example, from B2 to $B$2 to B$2 to $B2.The loan payment table in Figure 12-6 uses mixed references (and an absolute reference).You need to enter only one formula in cell C6 and then copy it down and across to fill the table.Cell C6 contains the formula = PMT ($B6,$C$3,C$5) to calculate the annual payments on a$10,000 loan over a period of 15 years at an interest rate of 6 percent.We copied this for-mula to cells C6:F10 to calculate payments on three additional loan amounts using fouradditional interest rates.f12ie06Figure 12-6.This loan payment table uses formulas that contain mixed references.Note You can find the sample file used in this example, Loan.xls, on thecompanion CD.The first cell reference, $B6, indicates that we always want to refer to the values in column B butthe row reference (Rate) can change.Similarly, the mixed reference, C$5, indicates that wealways want to refer to the values in row 5 but the column reference (Loan Amount) canchange.For example, cell E8 contains the formula = PMT ($B8,$C$3,E$5).Without mixedreferences, we would have to edit the formulas manually in each of the cells in the range C6:F10.358Chapter 12 Part 5: Creating Formulas and Performing Data AnalysisBuilding FormulasTroubleshootingInserted cells are not included in formulasIf you have a SUM formula at the bottom of a row of numbers, and then insert new rowsbetween the numbers and the formula, the range reference in the SUM function doesn tinclude the new cells.Unfortunately, you can t do much about this.This is an age-oldspreadsheet problem, but Excel attempts to correct it for you automatically.The range ref-erence in the SUM formula will indeed not change when you insert new rows.If you typenew values in the newly inserted cells, however, the range reference adjusts with each newentry.The only caveat is that you must enter the new values one at a time, starting with thecell directly below the first column of the existing list.If you begin entering values in themiddle of a group of newly inserted cells, the range reference remains unaffected.For moreinformation about the SUM function, see Using the SUM Function on page 411.Editing FormulasYou edit formulas the same way you edit text entries.To delete characters in a formula,drag through the characters in the cell or the formula bar and press Backspace or Delete.To replace a character, highlight it and type its replacement.To replace a reference,highlight it and then click the new cell you want the formula to use.A relative reference isentered automatically.You can also insert additional cell references in a formula.For example, to insert a referenceto cell B1 in the formula =A1+A3, simply move the insertion point between A1 and the plussign and either type +B1 or type a plus sign and click cell B1.The formula becomes=A1+B1+A3.Understanding Reference SyntaxSo far, we have used the default worksheet and workbook names for the examples in this book.When you save a workbook, you must give it a permanent name.If you create a formula firstand then save the workbook with a new name, the formula is adjusted accordingly.For exam-ple, if you save Book2 as Sales.xls, the remote reference formula =[Book2]Sheet2!$A$2changes to =[Sales.xls]Sheet2!$A$2.And if you rename Sheet2 of Sales.xls to February, thereference changes to =[Sales.xls]February!$A$2.If the referenced workbook is closed, the fullpath to the folder where the workbook is stored appears in the reference, as shown in theexample ='C:\Work\[Sales.xls]February'!$A$2.In the preceding example, note that apostrophes surround the workbook and worksheet por-tion of the reference.Excel adds the apostrophes around the path when you close the work-book.If you type a new reference to a closed workbook, however, you must add the359Chapter 12 Part 5: Creating Formulas and Performing Data AnalysisMicrosoft Office Excel 2003 Inside Outapostrophes yourself.To avoid typing errors, open the closed workbook and click cells withthe mouse to enter references so that Excel inserts them in the correct syntax for you.Using Numeric Text in FormulasThe term numeric text refers to an entry that is not strictly numbers, but includes bothnumbers and a few specific text characters.You can perform mathematical operations onnumeric text values as long as the numeric string contains only the following characters:0 1 2 3 4 5 6 7 8 9.+ - E eIn addition, you can use the / character in fractions.You can also use the following fivenumber-formatting characters:$ , % ( )You must enclose numeric text strings in quotation marks.For example, if you type theformula =$1234+$123, Excel displays an error message stating that Excel found an error inthe formula you entered.(The error message also offers to correct the error for you byremoving the dollar signs.) But the formula ="$1234"+"$123" produces the result 1357(ignoring the dollar signs).When Excel performs the addition, it automatically translatesnumeric text entries into numeric values.About Text ValuesThe term text values refers to any entry that is neither a number nor a numeric text value;the entry is treated as text only.You manipulate text values in the same way that you manip-ulate numeric values.For example, if cell A1 contains the text ABCDE and you type the for-mula =A1 in cell A10, cell A10 displays ABCDE.You can use the & (ampersand) operator to concatenate, or join, several text values.Extend-ing the preceding example, if cell A2 contains the text FGHIJ and you type the formula=A1&A2 in cell A3, cell A3 displays ABCDEFGHIJ.To include a space between the twostrings, change the formula to =A1&" "&A2.This formula uses two concatenation operatorsand a literal string, or string constant (a space enclosed in quotation marks).You can use the & operator to concatenate strings of numeric values as well.For example, ifcell A3 contains the numeric value 123 and cell A4 contains the numeric value 456, the for-mula =A3&A4 produces the string 123456.This string is left aligned in the cell because it sconsidered a text value.(Remember, you can use numeric text values to perform any mathe-matical operation as long as the numeric string contains only the numeric characters listedon the previous page.)Finally, you can use the & operator to concatenate a text value and a numeric value.Forexample, if cell A1 contains the text ABCDE and cell A3 contains the numeric value 123, theformula =A1&A3 produces the string ABCDE123
[ Pobierz całość w formacie PDF ]