لطفا منتظر باشید ...
45. Copy and Move Formulas
44 About Calc Formulas 50 Create a Range You can copy, move, and paste one cell into another using standard copy-and-paste tools such as the Windows Clipboard. When you copy formulas that contain cell addresses, Calc updates the cell references so they become relative references . For example, suppose that you enter this formula in cell A1 :
KEY TERMRelative reference A cell that is referenced in relation to the current cell.
This formula contains two cell references. The references are relative because the references A2 and A3 change if you copy the formula elsewhere. If you copy the formula to cell B5 , for example, B5 holds this:
=A2 + A3
=B6 + B7
absolute reference . The reference $B$5 is an absolute reference. If you want to sum two columns of data (A1 with B1, A2 with B2 , and so on) and then multiply each sum by some constant number, for example, the constant number can be a cell referred to as an absolute reference . That formula might resemble this:
=(A1 + B1) * $J$1
KEY TERMAbsolute reference A cell reference that does not change if you copy the formula elsewhere.
TIPWhen entering cell references, the letters are not case-sensitive. You can type a3 and Calc will convert it to the cell reference A3 .In this case, $J$1 is an absolute reference, but A1 and B1 are relative. If you copy the formula down one row, the formula changes to this:Notice that the first two cells changed because when you originally entered them, they were relative cell references. You told Calc, by placing dollar signs in front of the absolute cell reference's row and column references, not to change that reference when you copy the formula elsewhere.$B5 is a partial absolute cell reference. If you copy a formula with $B5 inside the computation, the $B keeps the B column intact, but the fifth row updates to the row location of the target cell. For example, if you type the formula
=(A2 + B2) * $J$1
in cell A1 and then copy the formula to cell F6 , cell F6 holds this formula:
=2 * $B5
You copied the formula to a cell five rows and five columns over in the worksheet. Calc did not update the column name, B , because you told Calc to keep that column name absolute. (It is always B no matter where you copy the formula.) Calc added five to the row number, however, because the row number is relative and open to change whenever you copy the formula.
=2 * $B10
TIPMost of the time, you'll use relative referencing. If you insert or delete rows, columns, or cells, your formulas remain accurate because the cells that they reference change as your worksheet changes.| 1. | Enter the Total Formula For this sheet, assume you want a formula for each past year and projected year into the future. You would type the formula to total the first year in cell B17 . One formula that would total this year would be52 About Calc Functions explores Sum() and other Calc functions.Notice that cell A12 does not display all of the month of September's name. The column is not wide enough to display the full month. Calc warns you that this cell's contents aren't fully displayed with a small triangle along the right side of the cell. You can widen cells like this that you find are too narrow by dragging the dividing line between the name of column A and column B to the right to give every month name enough room to display properly.60 Format Cells explains how to format cells the way you want them to look. The labels for Factor and Adjusted Total are right-justified (with the Align Right button); however, they first enter their respective cells left-justified because they contain text. |
| 5. | Enter the First Adjusted Total To enter the correct adjusted total in cell B19 , you would type =B17 * $B$18.By using absolute addressing in cell B18 (that is, $B$18 ), when you copy it to the remaining years, all the cells you copy to will also use B18 instead of a different cell for the factor. TIPYou don't need to leave spaces between operators such as multiplication (*) in formulas. Doing so makes them easier to read and to check for errors, however. |
| 6. | .Copy the Adjusted Total To copy the adjusted total to the other years, you can use Ctrl+C and then paste with Ctrl+V into each year's adjusted total cell, but it's simpler just to drag the small square in the first cell's lower-right corner (the mouse pointer changes to a plus sign when you point to this square) across through the cells that are to receive the copied formula.When you release your mouse after making such a copy with one or more absolute cell addresses in the range, the absolute address remains the same and the relative addresses inside the cells change. This sounds less obvious than it is. In other words, when you copy the formula =B17 * $B$18 to cell C19 , cell C19 gets this formula: =C17 * $B$18. Cell D19 gets =D17 * $B$18, and so on. |
NOTEYou can make only the row or only the column of a cell address absolute. In the cell reference M$15 , the column named M is relative and will change if you copy a cell that contains this reference elsewhere, but the absolute row number, $15 , will not change.