Excel 2010 part 13

pdf
Số trang Excel 2010 part 13 10 Cỡ tệp Excel 2010 part 13 1,016 KB Lượt tải Excel 2010 part 13 0 Lượt đọc Excel 2010 part 13 0
Đánh giá Excel 2010 part 13
4.8 ( 20 lượt)
Nhấn vào bên dưới để tải tài liệu
Để tải xuống xem đầy đủ hãy nhấn vào bên trên
Chủ đề liên quan

Nội dung

Add a Row or Column of Numbers You can quickly add worksheet numbers by building a formula that uses Excel’s SUM function. Adding a range of numbers is probably the most common worksheet calculation, so it is useful to know how to use Excel’s SUM function. Although you can use SUM to add individual cells or a rectangular range of cells, you will most often need to add a row or a column of data. In this case, when you use the SUM function in a formula, you can specify as the function’s argument a reference to either a row or a column of numbers. Add a Row or Column of Numbers 1 Click in the cell where you want the sum to appear. 2 Type =sum(. • When you begin a function, Excel displays a banner that shows you the function’s arguments. Note: In the function banner, bold arguments are required, and arguments that appear in square brackets are optional. 2 1 3 Use the mouse to click and drag the row or column of numbers that you want to add. • Excel adds a reference for the range to the formula. 3 120 08_577639-ch06.indd 120 3/15/10 2:43 PM 6 CHAPTER Building Formulas and Functions 4 Type ). 5 Click or press 5 . 4 • • Excel enters the formula. Excel displays the sum in the cell. Can I use the SUM function to total rows and columns at the same time? Can I use the SUM function to total only certain values in a row or column? Yes, the SUM function works not only with simple row and column ranges, but with any rectangular range. After you type =sum(, use the mouse to click and drag the entire range that you want to sum. Yes. The SUM function can accept multiple arguments, so you can enter as many cells or ranges as you need. After you type =sum(, hold down and either click each cell that you want to include in the total, or use the mouse to click and drag each range that you want to sum. 121 08_577639-ch06.indd 121 3/15/10 2:43 PM Build an AutoSum Formula You can reduce the time it takes to build a worksheet as well as reduce the possibility of errors by using Excel’s AutoSum feature. By default, the AutoSum tool adds a SUM function formula to a cell and automatically adds the function arguments based on the structure of the worksheet data. However, you can also use AutoSum to quickly create simple formulas that use common functions, such as AVERAGE, COUNT, MAX, or MIN. Build an AutoSum Formula 1 Click in the cell where you want the sum to appear. Note: For AutoSum to work, the cell you select should be below or to the right of the range you want to sum. 1 2 Click the Sum button ( ). • If you want to use a function other than SUM, click the Sum and then click the operation you want to use: Average, Count Numbers, Max, or Min. 2 122 08_577639-ch06.indd 122 3/15/10 2:43 PM 6 CHAPTER Building Formulas and Functions 3 • Excel adds a SUM function formula to the cell. Note: You can also press + = instead of clicking • . Excel guesses that the range above (or to the left) of the cell is the one you want to add. If Excel guessed wrong, select the correct range. 3 Click • or press . Excel displays the sum. Is there a way to see the sum of a range without adding an AutoSum formula? Is there a faster way to add an AutoSum formula? Yes. You can use Excel’s status bar to do this. When you select any range, Excel adds the range’s numeric values and displays the result on the right side of the status bar — for example, Sum: 75200. By default, Excel also displays the Average and Count. If you want to see a different calculation, right-click the result in the status bar and then click the operation you want to use: Numerical Count, Maximum, or Minimum. Yes. If you know the range you want to sum, and that range is either a vertical column with a blank cell below it or a horizontal row with a blank cell to its right, select the range (including the blank cell) and then click or press + = . Excel populates the blank cell with a SUM formula that totals the selected range. 123 08_577639-ch06.indd 123 3/15/10 2:43 PM Add a Range Name to a Formula You can make your formulas easier to build, more accurate, and easier to read by using range names as operands. For example, the formula =SUM(B2:B10) is difficult to decipher on its own because you cannot tell at a glance what kind of data is in the range B2:B10. However, with the formula =SUM(Expenses), it is immediately obvious that the formula is adding a range of expense values. If you are not sure what range names are or how to define a range name, see Chapter 4 to learn more about range names, including how to define names for ranges in Excel. Add a Range Name to a Formula 1 Click in the cell in which you 2 want to build the formula, type =, and then type any operands and operators you need before adding the range name. 2 Click the Formulas tab. 1 3 Click Use in Formula. • Excel displays a list of the range names in the current workbook. 4 Click the range name you want 3 4 to use. 124 08_577639-ch06.indd 124 3/15/10 2:43 PM 6 CHAPTER Building Formulas and Functions • Excel inserts the range name into the formula. 5 Type any operands and operators you need to complete your formula. • 6 If you need to insert other range names into your formula, repeat Steps 2 to 5 for each name. 6 Click or press . Excel calculates the formula result. If I create a range name after I build my formula, is there an easy way to convert the range reference to the range name? Do I have to use the list of range names to insert range names into my formulas? Yes. Excel offers an Apply Names feature that replaces range references with their associated range names throughout a worksheet. Click the Formulas tab, click the Define Name , and then click Apply Names to open the Apply Names dialog box. In the Apply names list, click the range name you want to use, and then click OK. Excel replaces the associated range references with the range name in each formula in the current worksheet. No. As you build your formula, you can type the range name by hand, if you know it. Alternatively, as you build your formula, click the cell or select the range that has the defined name, and Excel adds the name to your formula instead of the range address. If you want to work from a list of the defined range names, click an empty area of the worksheet, click Formulas, click Use in Formula, click Paste Names, and then click Paste List. 125 08_577639-ch06.indd 125 3/15/10 2:43 PM Reference Another Worksheet Range in a Formula You can add flexibility to your formulas by adding references to ranges that reside in other worksheets. This enables you to take advantage of work you have done in other worksheets so you do not have to waste time repeating your work on the current worksheet. You can even add references to ranges that reside in other workbooks, as described in the second Tip on the following page. Reference Another Worksheet Range in a Formula 1 Click in the cell in which you want to build the formula, type =, and then type any operands and operators you need before adding the range reference. 1 2 Press + until the worksheet you want to use appears. 2 126 08_577639-ch06.indd 126 3/15/10 2:43 PM 6 CHAPTER Building Formulas and Functions 3 Select the range you want to use. 4 Press + until you return to the original worksheet. • 4 3 A reference to the range on the other worksheet appears in your formula. 5 Type any operands and operators you need to complete your formula. 6 6 Click or press . Excel calculates the formula result. Can I reference a range in another worksheet by hand? Can I reference a range in another workbook in my formula? Yes. Rather than selecting the other worksheet range with your mouse, you can type the range reference directly into your formula. Type the worksheet name, surrounded by single quotation marks (‘) if the name contains a space; type an exclamation mark (!); then type the cell or range address. Here is an example: ‘Expenses 2010’!B2:B10. Yes. First make sure the workbook you want to reference is open. When you reach the point in your formula where you want to add the reference, click the Excel icon ( ) in the Windows taskbar, and then click the other workbook to switch to it. Click the worksheet that has the range you want to reference, and then select the range. Click and then click the original workbook to switch back to it. Excel adds the other workbook range reference to your formula. 127 08_577639-ch06.indd 127 3/15/10 2:43 PM Move or Copy a Formula You can restructure or reorganize a worksheet by moving an existing formula to a different part of the worksheet. When you move a formula, Excel preserves the formula’s range references. Excel also enables you to make a copy of a formula, which is a useful technique if you require a duplicate of the formula elsewhere or if you require a formula that is similar to an existing formula. When you copy a formula, Excel adjusts the range references to the new location. Move or Copy a Formula Move a Formula 1 1 Click the cell that contains the formula you want to move. 2 2 Position over any outside border of the cell. changes to . 3 3 Click and drag the cell to the new location. changes to . • • Excel displays an outline of the cell. 4 Excel displays the address of the new location. 4 Release the mouse button. • • Excel moves the formula to the new location. Excel does not change the formula’s range references. 128 08_577639-ch06.indd 128 3/15/10 2:43 PM 6 CHAPTER Building Formulas and Functions Copy a Formula 1 1 Click the cell that contains the 4 formula you want to copy. 2 Press and hold . 3 Position over any outside 3 border of the cell. changes to . 4 Click and drag the cell to the 5 location where you want the copy to appear. • • Excel displays an outline of the cell. Excel displays the address of the new location. 5 Release the mouse button. 6 Release . • • Excel creates a copy of the formula in the new location. Excel adjusts the range references. Note: You can make multiple copies by dragging the bottomright corner of the cell. Excel fills the adjacent cells with copies of the formula. Why does Excel adjust the range references when I copy a formula? When you make a copy of a formula, Excel assumes that you want that copy to reference different ranges than in the original formula. In particular, Excel assumes that the ranges you want to use in the new formula are positioned relative to the ranges used in the original formula, and that the relative difference is equal to the number of rows and columns you dragged the cell to create the copy. For example, suppose your original formula references cell A1, and you make a copy of the formula in the cell one column to the right. In that case, Excel also adjusts the cell reference one column to the right, so it becomes B1 in the new formula. If you do not want Excel to adjust a range reference, switch the reference to the absolute format. Double-click the cell that contains the formula you want to edit, select the cell reference you want to change, and then press . 129 08_577639-ch06.indd 129 3/15/10 2:43 PM
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.