Ceci est une ancienne révision du document !
I didn't have to work for very long in Calc before I needed to use cell names to reference values in different cells. This need was followed by those same reference names failing to do what I was expecting from them. While referencing cells is not complicated, it sometimes requires a little thought about what you're trying to accomplish. At its heart, there are two reference types: relative and absolute. Relative references refer to a set of offsets from the current cell. Absolute references refer to the exact (or fixed) cell, column, or row. With these two types we can show references in four different ways. Cell references can even cross between sheets. Sometime, you will need to reference more than one cell. This is done with cell ranges. You can make a cell range relative or absolute just like the cell reference. You can even name a cell range.
Je n'ai pas eu à travailler très longtemps dans Calc avant d'avoir besoin d'utiliser des noms de cellules pour référencer des valeurs dans d'autres cellules. Ce besoin a été suivi par des échecs d'obtenir ce que je voulais de ces noms de référence.
Relative References A relative cell reference is a set of offsets from the current cell. If you put a reference to C4 in cell D6, the reference is to the cell one column to the left and two rows up. If you copy this reference to other cells, say D7, it will refer to the cell C5, which is one column to the left and two rows up from D7. Let's create an example to clear up any confusion. Create a new Spreadsheet document. In the cells B3 and B4 put the values 15 and 46. In the cells C3 and C4 put in the values 11 and 14. Select cell B5 and enter the following formula (yes, the equal sign [=] is necessary): =B3+B4 When you press enter, it will show a total of 61, the sum of the two values in B3 and B4. Now, right-click B5 and copy. Select C5, right-click, and paste. In C5 you get the total 25, the sum of the two values in C3 and C4. See how the reference shifted to the new column? Since we didn't change rows, the row references stayed the same. However, if we did, the relative cell reference would have shifted to accommodate the change. So, when we make a relative reference, it will move positions, keeping the same offsets, when copied to a new location.
Absolute References An absolute cell reference is fixed. If you make an absolute reference to cell C1, then no matter where you copy that reference, it always refers to C1. We create absolute references by adding the dollar sign ($) before the column or row we want to remain absolute. For example $C$1 is an absolute reference to the cell C1. Let's do another example to illustrate absolute references. Imagine we need to multiply a group of numbers by a factor. In cell D1 we put 0.75. This is our factor. In D2, D3, and D4 we put 10, 20, and 30. In the E column, we will put our calculations. In cell E2 put the formula: =D2*D1 This will give us the answer 7.5, which is correct. However, if we copy the formula in E2 to E3 and E4, we get answers of 200 and 600, which are wrong. If you look at the copied formulas, the references shifted. This is because we used a relative reference. We do want the first number to shift when we copy, but we need D1 to stay fixed. Edit the formula in E2 and change it to =D2*$D$1 We still have the right answer in E2. Now, copy the formula in E2 to E3 and E4. We get the answers 15 and 22.5, which are the correct answers. If you look at the copied formulas in E3 and E4, you find the reference to D1 remained fixed.
Four Ways to Reference Cells With this in mind, this gives us four different ways we can reference a cell. They are relative, absolute, and two partially absolute references. • D1 – Relative, from cell E3, it is one column left and two rows up • $D$1 – Absolute, from any position references cell D1 • $D1 – Partially absolute, column D is fixed but the row is relative • D$1 – Partially absolute, column is relative but the row is fixed to 1
Reference Cell in Another Sheet To reference a cell in another sheet, we use dot notation to add the sheet name to the reference. For example, Sheet1.A1. This is handy if you need to carry a calculation or figure from one sheet to another. To illustrate, we will rename our current sheet, add a new one, and create a reference from one to the other. Right-click on the tab for the current sheet and select “Rename Sheet.” When prompted, name the sheet MyData and click OK. To create a new sheet, click on the plus (+) at the end of the sheet tabs. Select cell A1 in the new sheet. Type in =MyData.E2 which will pick up the value of 7.5 from the cell E2 in the MyData sheet.
NOTE: If the sheet name contains spaces, surround the name with single quotes, as in 'My Sheet'.C3. Just like other references, the reference to the sheet is absolute or relative depending on whether we put the dollar sign ($) in front of it. Cell Ranges Sometimes, you need to reference a group of cells rather than just one. For such cases, we use cell ranges. A cell range is created by separating two cell references with a colon (:). The left cell references the upper left corner of the cell range, and the right cell references the lower right corner of the cell range. For example A1:C2 represents a range of six cells: A1, A2, B1, B2, C1, and C2. However, the range can also represent just one column or row as in C2:C100 or B3:H3. The same rules for relative and absolute references apply to cell ranges. You can create a full or partial absolute reference. You can even apply a range across sheets. Let's say you need a cell range of all the A1 cells on all the sheets named Sheet1 through Sheet10. You would use the cell range Sheet1.A1:Sheet10.A1.
For convenience, you can name a range. To name a range, select a range of cells in the sheet. Insert > Names > Define brings up the “Define Name” dialog. Give the range a name. Keep in mind that cell range names can contain only letters, numbers, and underscore (_). Note the use of absolute references in the Range field. You can adjust your range as needed, manually or by clicking the range button and selecting the range with the mouse or cursor. The Scope field allows you to define whether the named range applies to the entire document or just a certain sheet. Click OK to save the range with the new name. Cell references and ranges are fundamental when you begin to work on more complex sheet layouts, functions, and formulas in Calc documents. Understanding how relative and absolute references work can save time and prevent mistakes when copying formulas and references. While simple, incorrect use of references can lead to the wrong answer.