Nội dung

Chapter 6 Building Formulas and Functions Are you ready to start creating powerful and useful worksheets by building your own formulas? This chapter explains formulas, shows you how to build them, and shows you how to incorporate Excel’s versatile worksheet functions into your formulas. You also learn useful formula techniques such as how to sum a row or column of numbers, how to create quick formulas using the AutoSum feature, how to use range names and range from other workbooks in your formulas, and how to move and copy formulas. 08_577639-ch06.indd 110 3/15/10 2:42 PM Understanding Excel Formulas . . . . . . . . . . . . . . . . . . . 112 Build a Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 Understanding Excel Functions . . . . . . . . . . . . . . . . . . . 116 Add a Function to a Formula . . . . . . . . . . . . . . . . . . . . 118 Add a Row or Column of Numbers . . . . . . . . . . . . . . . . 120 Build an AutoSum Formula . . . . . . . . . . . . . . . . . . . . . . 122 Add a Range Name to a Formula . . . . . . . . . . . . . . . . . 124 Reference Another Worksheet Range in a Formula . . . . . . 126 Move or Copy a Formula . . . . . . . . . . . . . . . . . . . . . . . 128 08_577639-ch06.indd 111 3/15/10 2:42 PM Understanding Excel Formulas To get the most out of Excel, you need to understand formulas so that you can perform calculations on your worksheet data. You need to know the components of a formula, you need to understand arithmetic and comparison formulas, and you need to understand the importance of precedence when building a formula. Formulas A formula is a set of symbols and values that perform some kind of calculation and produce a result. All Excel formulas have the same general structure: an equal sign (=) followed by one or more operands and operators. The equal sign tells Excel to interpret everything that follows in the cell as a formula. For example, if you type =5+8 into a cell, Excel interprets the 5+8 text as a formula, and displays the result in the cell (13). Operands Every Excel formula includes one or more operands, which are the data that Excel uses in the calculation. The simplest type of operand is a constant value, which is usually a number. However, most Excel formulas include references to worksheet data, which can be a cell address (such as B1), a range address (such as B1:B5), or a range name. Finally, you can also use any of Excel’s built-in functions as an operand. Operators In an Excel formula that contains two or more operands, each operand is separated by an operator, which is a symbol that combines the operands in some way, usually mathematically. Example operators include the plus sign (+) and the multiplication sign (*). For example, the formula =B1+B2 adds the values in cells B1 and B2. 112 08_577639-ch06.indd 112 3/15/10 2:42 PM 6 CHAPTER Building Formulas and Functions Arithmetic Formulas An arithmetic formula combines numeric operands — numeric constants, functions that return numeric results, and fields or items that contain numeric values — with mathematical operators to perform a calculation. Because Excel worksheets primarily deal with numeric data, arithmetic formulas are by far the most common formulas used in worksheet calculations. There are seven arithmetic operators you can use to construct arithmetic formulas: Operator Name Example Result + Addition =10 + 5 15 – Subtraction =10 – 5 5 – Negation =–10 –10 * Multiplication =10 * 5 50 / Division =10 / 5 2 % Percentage =10% 0.1 ^ Exponentiation =10 ^ 5 100000 Comparison Formulas There are six operators you can use to construct comparison formulas: A comparison formula combines numeric operands — numeric constants, functions that Operator Name Example return numeric results, and fields or items = Equal to =10 = 5 that contain numeric values — with special operators to compare one operand with < Less than =10 < 5 another. A comparison formula always <= Less than or equal to =10 < = 5 returns a logical result. This means that if the > Greater than =10 > 5 comparison is true, then the formula returns the value 1, which is equivalent to the logical > = Greater than or equal to =10 > = 5 value TRUE; if the comparison is false, then <> Not equal to =10 < > 5 the formula returns the value 0, which is equivalent to the logical value FALSE. Operator Precedence Most of your formulas include multiple operands and operators. In many cases, the order in which Excel performs the calculations is crucial. Consider the formula =3 + 5 ^ 2. If you calculate from left to right, the answer you get is 64 (3 + 5 equals 8, and 8 ^ 2 equals 64). However, if you perform the exponentiation first and then the addition, the result is 28 (5 ^ 2 equals 25, and 3 + 25 equals 28). Result 0 0 0 1 1 1 To solve this problem, Excel evaluates a formula according to a predefined order of precedence, which is determined by the formula operators: Operator Operation Precedence () Parentheses 1st – Negation 2nd % Percentage 3rd ^ Exponentiation 4th * and / Multiplication and division 5th + and – Addition and subtraction 6th =<<=>>=<> Comparison 7th 113 08_577639-ch06.indd 113 3/15/10 2:42 PM Build a Formula You can add a formula to a worksheet cell using a technique similar to adding data to a cell. To ensure that Excel treats the text as a formula, be sure to begin with an equal sign (=) and then type your operands and operators. When you add a formula to a cell, Excel displays the formula result in the cell, not the formula itself. For example, if you add the formula =C3+C4 to a cell, that cell displays the sum of the values in cells C3 and C4. To see the formula, click the cell and examine the Formula bar. Build a Formula 1 Click in the cell in which you want to build the formula. 2 Type =. • Your typing also appears in the Formula bar. Note: You can also type the formula into the Formula bar. 1 2 3 Type or click an operand. For example, to reference a cell in your formula, click in the cell. • 3 Excels inserts the address of the clicked cell into the formula. 114 08_577639-ch06.indd 114 3/15/10 2:43 PM 6 CHAPTER Building Formulas and Functions 6 4 Type an operator. 5 Repeat Steps 3 and 4 to add other operands and operators to your formula. 5 6 Click or press . 4 • If Excel displays only the result of the formula, how do I make changes to the formula? Excel displays the formula result in the cell, but it still keeps track of the original formula. To display the formula again, you have two choices: Click the cell and then edit the formula using the Formula bar, or doubleclick the cell to display the original formula in the cell and then edit the formula. In both cases, press when you finish editing the formula. Excel displays the formula result in the cell. If I have many formulas, is there an easy way to view the formulas? Yes. You can configure the worksheet to show the formulas instead of their results. Click File and then click Options to open the Excel Options dialog box. Click the Advanced tab, scroll to the Display options for this worksheet section, click the Show formulas in cells instead of their calculated results check box ( changes to ), and then click OK. You can also toggle between formulas and results by pressing +‘ . 115 08_577639-ch06.indd 115 3/15/10 2:43 PM Understanding Excel Functions To build powerful and useful formulas, you often need to include one or more Excel functions as operands. You need to understand the advantages of using functions, you need to know the basic structure of every function, and you need to review Excel’s function types. Functions A function is a predefined formula that performs a specific task. For example, the SUM function calculates the total of a list of numbers, and the PMT (payment) function calculates a loan or mortgage payment. You can use functions on their own, preceded by =, or as part of a larger formula. Function Advantages Functions are designed to take you beyond the basic arithmetic and comparison formulas by offering two main advantages. First, functions make simple but cumbersome formulas easier to use. For example, calculating a loan payment requires a complex formula, but Excel’s PMT function makes this easy. Second, functions enable you to include complex mathematical expressions in your worksheets that otherwise would be difficult or impossible to construct using simple arithmetic operators. Function Structure Every worksheet function has the same basic structure: NAME(Argument1, Argument2, ...). The NAME part identifies the function. In worksheet formulas and custom PivotTable formulas, the function name always appears in uppercase letters: PMT, SUM, AVERAGE, and so on. The items that appear within the parentheses are the functions’ arguments. The arguments are the inputs that functions use to perform calculations. For example, the function SUM(A1,B2,C3) adds the values in cells A1, B2, and C3. 116 08_577639-ch06.indd 116 3/15/10 2:43 PM 6 CHAPTER Building Formulas and Functions Statistical Functions The following table lists some common statistical functions: Function Description AVERAGE(number1,number2,...) Returns the average of the arguments COUNT(number1,number2,...) Counts the numbers in the argument list MAX(number1,number2,...) Returns the maximum value of the arguments MEDIAN(number1,number2,...) Returns the median value of the arguments MIN(number1,number2,...) Returns the minimum value of the arguments MODE(number1,number2,...) Returns the most common value of the arguments STDEV(number1,number2,...) Returns the standard deviation based on a sample STDEVP(number1,number2,...) Returns the standard deviation based on an entire population Financial Functions Most of Excel’s financial functions use the following arguments: Argument Description rate The fixed rate of interest over the term of the loan or investment nper The number of payments or deposit periods over the term of the loan or investment pmt The periodic payment or deposit pv The present value of the loan (the principal) or the initial deposit in an investment fv The future value of the loan or investment type The type of payment or deposit: 0 (the default) for end-of-period payments or deposits; 1 for beginningof-period payments or deposits The following table lists some common financial functions: Function Description FV(rate,nper,pmt,pv,type) Returns the future value of an investment or loan IPMT(rate,per,nper,pv,fv,type) Returns the interest payment for a specified period of a loan NPER(rate,pmt,pv,fv,type) Returns the number of periods for an investment or loan PMT(rate,nper,pv,fv,type) Returns the periodic payment for a loan or investment PPMT(rate,per,nper,pv,fv,type) Returns the principal payment for a specified period of a loan PV(rate,nper,pmt,fv,type) Returns the present value of an investment RATE(nper,pmt,pv,fv,type,guess) Returns the periodic interest rate for a loan or investment 117 08_577639-ch06.indd 117 3/15/10 2:43 PM Add a Function to a Formula To get the benefit of an Excel function, you need to use it within a formula. You can use a function as the only operand in the formula, or you can include the function as part of a larger formula. In “Understanding Excel Functions,” you learned that Excel has many functions and that most functions take one or more arguments, but it is often difficult to remember a function’s arguments and the order in which they appear. To make it easy to choose the function you need and to add the appropriate arguments, Excel offers the Insert Function feature. Add a Function to a Formula 4 1 Click in the cell in which you want to build the formula. 2 Type =. 3 Type any operands and operators you need before adding the function. 1 4 Click the Insert Function button ( 2 ). The Insert Function dialog box appears. 5 5 Click and then click the category that contains the function you want to use. 6 6 Click the function. 7 Click OK. 7 118 08_577639-ch06.indd 118 3/15/10 2:43 PM 6 CHAPTER Building Formulas and Functions 9 8 0 The Function Arguments dialog box appears. 0 8 Click inside an argument box. 9 Click the cell that contains the argument value. You can also type the argument value. ! 0 Repeat Steps 8 and 9 to fill as many arguments as you need. • The function result appears here. ! Click OK. • • Excel adds the function to the formula. Excel displays the formula result. Note: In this example, the result appears in the parentheses to indicate a negative value. In loan calculations, money that you pay out is always a negative amount. Note: If your formula requires any other operands and and then operators, press type what you need to complete your formula. Do I have to specify a value for every function argument? How do I calculate a monthly financial result if I only have yearly values? Not necessarily. Some function arguments are required to obtain a result, but some are optional. In the PMT function, for example, the rate, nper, and pv arguments are required, but the fv and type arguments are optional. When the Function Arguments dialog box displays a result for the function, then you know you have entered all of the required arguments. This is a common problem. For example, if your loan payment worksheet contains an annual interest rate and a loan term in years, how do you calculate the monthly payment using the PMT function? You need to convert the rate and term to monthly values. That is, you divide the annual interest rate by 12, and you multiply the term by 12. For example, if the annual rate is in cell B2, the term in years is in B3, and the loan amount is in B4, the function PMT(B2/12, B3*12, B4) calculates the monthly payment. 119 08_577639-ch06.indd 119 3/15/10 2:43 PM