|
|
Selecting and Filling a Cell Range
From NeoWiki
(Difference between revisions)
| Revision as of 05:43, 22 April 2007 (edit) ( | contribs) (→Filling a range) ← Previous diff |
Revision as of 15:27, 8 June 2007 (edit) (undo) Jgd (Talk | contribs) (add two sections in French and in comments) Next diff → |
||
| Line 1: | Line 1: | ||
| - | |||
| ==Selecting and Filling a Range of Cells== | ==Selecting and Filling a Range of Cells== | ||
| Line 33: | Line 32: | ||
| * Select the cell B2 and enter in the formula line, in the top of the page : "=B1+A2" (without quotes). Press {{key|Enter}}. The sum 3 appears in the cell B2. | * Select the cell B2 and enter in the formula line, in the top of the page : "=B1+A2" (without quotes). Press {{key|Enter}}. The sum 3 appears in the cell B2. | ||
| *Select the range B2:B20, choose the {{menu|Edit}} then the {{menu|Fill}} sub-menu and the {{menu|Down}} item. Now every cell of the selected range contains the sum of the successive integers and the cell B20 contains the sum of the first 20 integers, i.e. 210. | *Select the range B2:B20, choose the {{menu|Edit}} then the {{menu|Fill}} sub-menu and the {{menu|Down}} item. Now every cell of the selected range contains the sum of the successive integers and the cell B20 contains the sum of the first 20 integers, i.e. 210. | ||
| + | |||
| + | <!--==Utilisation des références absolues== | ||
| + | Vous trouverez la définition des notions de ''références ablolues et relatives'' en consultant l'aide de NeoOffice : allez dans le menu {{menu|Aide}} puis {{menu|Aide de NeoOffice}}, dans le menu déroulant choisissez {{menu|NeoOffice Calc}}, cliquez sur {{section|Recherche}} et entrez ''Références'' dans le champ de recherche. Sélectionnez {{prefName|Adresses et références absolues et relatives}}.<br> | ||
| + | :Exemple d'utilisation des références absolues :<br> | ||
| + | Supposons que vous vouliez entrer dans la cellule D1 la valeur (A1 + B1 + C1)*n, où ''n'' est un nombre que vous voulez pouvoir modifier au besoin, et ensuite copier cette formule pour toutes les cellules de la plage A1:C5 par exemple. Il suffit de procéder de la façon suivante : | ||
| + | *Entrez le nombre ''n'' dans la cellule E1 par exemple | ||
| + | *Cliquez dans la cellule D1 et entrez dans la barre de formule : | ||
| + | := SOMME(A1:C1)*$E$1 | ||
| + | *Pressez la touche {{key|Retour}} | ||
| + | *Cliquez dans la cellule D1 et remplissez la plage D1:D5 par glissement. La cellule D2 affichera la valeur (A2+B2+C2)*$E$1. Sans l'tilisation des références absolues, D2 afficherait (A2+B2+C2)*E2. | ||
| + | |||
| + | ==Effectuer des opérations arithmétiques sur les cellules d'une plage== | ||
| + | Vous pouvez utiliser la fonction ''Collage Spécial'' pour effectuer des opérations arithmétiques sur les valeurs d'une plage de cellules.<br> | ||
| + | :Exemples : | ||
| + | ::1- Multiplier toutes les valeurs d'une plage par un même nombre | ||
| + | * Entrez le multiplicateur, par exemple 2,5 dans une cellule vide | ||
| + | *Copiez ce nombre, {{key|Cmd-C}} | ||
| + | *Sélectionnez la plage de cellules à modifier | ||
| + | *Choisissez le menu {{menu|Édition}} puis le sous-menu {{menu|Collage spécial…}} | ||
| + | *Dans la fenêtre de dialogue, dans la section {{section|Opérations}} cochez {{prefName|Multiplication}}. Cliquez sur {{button|OK}} | ||
| + | Tous les nombres contenus dans les cellules sélectionnées sont remplacés par leur produit par 2,5.<br> | ||
| + | '''N.B.''' La plage de cellules sélectionnée doit constituer une matrice. | ||
| + | ::2 -Effectuer des opérations sur une plage par ''Copie/Collage spécial'' d'une autre plage.<br> | ||
| + | Supposons que vous vouliez utiliser la plage C1:D2 pour effectuer des opérations sur la plage A1:B2 | ||
| + | *Sélectionnez et copiez la plage C1:D2 | ||
| + | *Sélectionnez la plage de cellules à modifier, A1:B2 | ||
| + | *Choisissez le menu {{menu|Édition}} puis le sous-menu {{menu|Collage spécial…}} | ||
| + | *Dans la fenêtre de dialogue, dans la section {{section|Opérations}} cochez l'opération souhaitée, par exemple {{prefName|Multiplication}}. Cliquez sur {{button|OK}} | ||
| + | La plage A1:B2 contient maintenant les produits A1*C1, A2*C2, B1*D1 et B2*D2.--> | ||
| ==Showing the formulas== | ==Showing the formulas== | ||
Revision as of 15:27, 8 June 2007
Contents |
Selecting and Filling a Range of Cells
Sometimes you might wish to fill an entire row or column in your spreadsheet with data “automatically,†e.g., sequential dates or numbers. You can do this in NeoOffice using the following steps:
Selecting a range
- In the “Input field†in the left top of the sheet, type the name of the beginning cell and then the name of the ending cell of the desired range, separated by a colon (:), e.g. A1:A20, and press the Enter key.
You can also select the range with the mouse, or by clicking in the top-left cell in the range and shift-clicking in the bottom right cell in the range, if you do not have the cell references memorized. - You can save that range for easier future use by giving it a significant name. Open the Insert menu, then the Names sub-menu, and select the Define… item. Type in a name and press Add. (This name will now be visible in the Range names section in the Navigator window.)
Afterwards, it will be easy to select the range by double-clicking its name in the Range names section in the Navigator.
Filling a range
- Select a cell range and choose the Edit menu then the Fill sub-menu and the Series… item.
Four types of series Linear, Growth, Date and AutoFill are available.
- The automatic filling is done from a sort list. You can see all the available lists by selecting Sort Lists in the NeoOffice Calc section of the Preferences sub-menu of the NeoOffice menu.
You can add here your personal lists:
- Click New
- Enter the successive values of your list, separated with a line break. Click OK
- That series will be available via the Series… item in the Fill sub-menu of the Edit menu by checking the case AutoFill in the Series type section and choosing the first element of the list as the Start value.
Drag-and-Fill:
- You'll notice a little black square in the bottom-right corner of the current cell. This is a drag-and-fill tool; click-and-drag the square along the row/column you wish to fill, from the 'source' cell to the last cell you want filled, and then release the corner. The tool behaves differently depending on what's in the current cell (or cells - note that this also works with blocks of cells, though only in one direction at a time):
- Text - copies the same text into all the target cells
- Number - adds 1 to each suceeding cell, useful for filling a column/row quickly with 1-50 (disconcertingly. it also does this with non-integer cell values, so you can get the sequence e.g. -1.1, -0.1, 0.9, 1.9!)
- Formula - equivalent to copying/pasting the formula (see Copying Formulas below), i.e. the cell references get updated for each cell filled, unless the cell reference in the formula is absolute, i.e. $B$1 will stay $B$1 all the time, but B1 will become B2, B3 etc. (or C1, D1 if you're filling a row)
Copying formulas
- The Fill function allows you to apply a recurrent formula to a cell range.
Example :
- Select the range A1:A20, enter 1 in A, choose the Edit menu then the Fill sub-menu, click Series.
- Set up the Type to Linear, the Start value to 1, the End value to 20 and the Increment to 1.
- Click OK.
The series of the first 20 integers appears in the selected range.
- Enter 1 in the cell B1.
- Select the cell B2 and enter in the formula line, in the top of the page : "=B1+A2" (without quotes). Press Enter. The sum 3 appears in the cell B2.
- Select the range B2:B20, choose the Edit then the Fill sub-menu and the Down item. Now every cell of the selected range contains the sum of the successive integers and the cell B20 contains the sum of the first 20 integers, i.e. 210.
Showing the formulas
If in the View section of NeoOffice Calc of the Preferences sub-menu of the NeoOffice menu, you check the Value highlighting option, the cells which contain values will be displayed in blue, and those which contain formulas in green. If you choose that option, you'll not be able to change the character color in those cells.
