=DECALER(A1;3;2) renvoie la cellule située 3 lignes plus bas et 2 colonnes plus à droite que A1, c'est-à-dire C4. Donnez-lui aussi une hauteur et une largeur et elle renvoie une plage entière, ce à quoi DECALER sert surtout : des totaux et des moyennes sur une plage qui se déplace ou s'agrandit. DECALER s'appelle OFFSET dans un Excel anglais, le nom que montre le tableau : =OFFSET(A1,E2,F2). Vous pouvez aussi y taper les formules en français, avec des points-virgules.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Rows | Cols | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
=DECALER(A1;E2;F2)3 lignes plus bas et 2 colonnes plus à droite que A1, on arrive en C4, le prix de Carrot, $0.80. Mettez Cols à 0 pour obtenir le nom Carrot, ou Rows à 5 pour la ligne de Milk. Les lignes et les colonnes peuvent être négatives pour monter ou revenir en arrière, et un déplacement au-delà du haut ou du bord de la feuille donne #REF!.
Syntaxe de DECALER
=OFFSET(reference, rows, cols, [height], [width])
reference(réf) : la cellule (ou la plage) de départ.rows,cols(lignes, colonnes) : de combien se déplacer. 0 signifie rester sur place.height,width(hauteur, largeur) : la taille de la plage à renvoyer, comptée à partir de la cellule atteinte. Omises, elles valent la taille dereference.
Seule dans une cellule, une DECALER qui renvoie plusieurs cellules se propage dans Excel 365 ; les versions plus anciennes affichent généralement #VALEUR! (en anglais #VALUE!). Dans SOMME, MOYENNE, NB ou MAX, elle fonctionne comme une plage.
Additionner les N dernières lignes
L'usage classique de DECALER : un total qui couvre toujours les lignes les plus récentes, quel que soit le nombre de lignes ajoutées. NB (COUNT en anglais) trouve combien il y a de valeurs, DECALER descend jusqu'à la première des N dernières, et la hauteur prend N lignes.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last N | Total | ||
| 2 | Jan | 4,200 | 3 | 14,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
=SOMME(DECALER(B1;NB(B2:B13)-E2+1;0;E2;1))Il y a 7 valeurs, donc DECALER commence 7-3+1, soit 5 lignes sous B1, en B6, et prend 3 lignes : de May à Jul, 14,900. En français, la formule s'écrit =SOMME(DECALER(B1;NB(B2:B13)-E2+1;0;E2;1)). Tapez 4900 en B9 (août) et le total passe à Jun, Jul et Aug, car NB trouve maintenant 8. La plage B2:B13 laisse de la place pour le reste de l'année. La colonne ne doit pas avoir de cellules vides au milieu, sinon NB compte trop peu et la fenêtre tombe au mauvais endroit.
Une moyenne glissante
Recopiée vers le bas d'une colonne, DECALER avec un décalage de lignes négatif donne à chaque ligne une fenêtre sur les lignes du dessus : ici, la moyenne du mois en cours et des deux précédents.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month average |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| 4 | Mar | 4,800 | 4,300 |
| 5 | Apr | 5,100 | 4,600 |
| 6 | May | 4,600 | 4,833 |
| 7 | Jun | 5,300 | 5,000 |
| 8 | Jul | 5,000 | 4,967 |
=MOYENNE(DECALER(B4;-2;0;3;1))C4 fait la moyenne de B2:B4 (de Jan à Mar), 4,300. Chaque ligne en dessous décale la fenêtre d'une ligne vers le bas. Remplacez le 3 par 6 et le -2 par -5 pour une moyenne sur six mois (en commençant alors la formule à la ligne 7). Ce cas précis n'a pas du tout besoin de DECALER : =MOYENNE(B2:B4) recopiée vers le bas depuis C4 fait la même chose, car les références relatives se déplacent déjà. DECALER se justifie quand la taille de la fenêtre vient d'une cellule.
Pourquoi INDEX est souvent le meilleur choix
DECALER est volatile : Excel recalcule chaque DECALER après toute modification n'importe où dans le classeur, puisqu'il ne peut pas savoir à l'avance vers quelles cellules elle pointera. Une feuille qui en contient des milliers devient lente. INDEX renvoie aussi une référence, et une plage écrite début:INDEX(...) s'agrandit de la même façon sans être volatile :
=SUM(OFFSET(B2, 0, 0, E2, 1)) first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2)) same rows, not volatile
Les deux lisent les E2 premières lignes de la colonne, la première de façon volatile, la seconde non. Dans un Excel français : =SOMME(DECALER(B2;0;0;E2;1)) et =SOMME(B2:INDEX(B2:B13;E2)). DECALER est aussi plus difficile à vérifier : Repérer les antécédents et les contours colorés qu'Excel dessine pendant la modification de la formule montrent la cellule de départ et les arguments, pas la plage que DECALER finit par renvoyer. Utilisez DECALER pour un modèle rapide ou une plage de graphique ; préférez INDEX dans les gros classeurs. La page INDEX en dit plus sur le renvoi de plages, et INDIRECT est l'autre fonction de référence volatile.
Exercice : le total des N premiers mois
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | First N | Total | ||
| 2 | Jan | 4,200 | 4 | |||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
À vous : En F2, utilisez DECALER (OFFSET) dans SOMME pour faire le total des N premiers mois, N étant en E2.
Questions fréquentes
Que fait DECALER dans Excel ?
Elle renvoie une référence située à un nombre donné de lignes et de colonnes d'une cellule de départ, éventuellement redimensionnée. =DECALER(A1;3;2) est la cellule située 3 lignes plus bas et 2 colonnes à droite de A1, c'est-à-dire C4.
Comment additionner les N dernières lignes dans Excel ?
Partez de l'en-tête et descendez jusqu'à la première des N dernières valeurs : =SOMME(DECALER(B1;NB(B2:B100)-N+1;0;N;1)). NB trouve combien il y a de valeurs, et la hauteur N prend autant de lignes. Cela ne fonctionne que si la colonne n'a pas de trous.
Pourquoi DECALER est-elle volatile ?
Excel recalcule chaque DECALER après toute modification du classeur, car les cellules vers lesquelles elle pointe ne sont connues qu'une fois qu'elle s'exécute. Sur de gros classeurs, cela ralentit tout. Une plage construite avec INDEX, comme B2:INDEX(B2:B100;N), fait le même travail sans être volatile.
Quels sont les arguments de DECALER ?
DECALER(réf;lignes;colonnes;[hauteur];[largeur]) : la cellule de départ, de combien de lignes descendre (négatif pour monter), de combien de colonnes se décaler (négatif pour revenir en arrière), et éventuellement la taille de la plage à renvoyer.
