La fonction DECALER()

La syntaxe de la fonction : DECALER (Plage; NbLigne; NbColonne; Hauteur. Largeur)

en anglais
OFFSET en anglais ( pour les macro-commandes)

1 Calculer la référence d'une plage de cellules

La fonction DECALER rend la référence d'une plage de cellules lorsque les paramètres qui lui sont transmis sont :

  1. plage: une plage de cellules (exemple : K3:Q23 ) qui sert de base pour déterminer la situation de la nouvelle plage. Cette plage a L  lignes  (ici L =21) et C colonnes (ici C= 7)
  2. nb_ligne : un nombre entier, que je nomme NB_LIG,  compris entre 0 et L : (exemple : NB_LIG = 16) et 16 < = 21 qui sera  le décalage vertical
  3. nb_colonne : un nombre entier, que je nomme NB_COL, compris entre 0 et C (exemple : NB_COL= 2 et 2 < = 7) qui sera le décalage horizontal
  4. hauteur : un nombre entier H compris entre 1 et L - NB_LIG (exemple : 2 et 2 < = 21 - 16) qui sera le nombre de lignes de la  nouvelle plage
  5. largeur : un nombre entier L compris entre 1 et C - NB_COL (exemple : 3 et 3 <  = 7 - 2) qui sera le nombre de colonne de la  nouvelle plage

 

Cliquer sur l'image pour l'agrandir

Dans l'exemple, la plage base du décalage K3:Q23 est colorée en bleu et la plage résultant de DECALER(K3:Q23 ;16;2;2;3) est colorée en vert.

  Attention,
  comme indiqué en 1 (ou en 2) le décalage débute à 0.
  Par exemple DECALER(K3:Q23;0;0;2;3) référencie la plage K3:M4

Cas particulier :

Lorsque L=H=1, DECALER rend selon le contexte la référence ou la valeur de la plage (ici une cellule) résultat du décalage

Cliquer sur l'image pour l'agrandir

Dans l'exemple illustré par l'image ci-dessus, DECALER(D4:F6;2;0;1;1) rend le contenu de la cellule D6 dans la formule =DECALER(D4:F6;2;0;1;1) ou la référence D6 dans =SOMME(D4:DECALER(D4:F6;2;0;1;1)) qui équivaut à SOMME(D4:D6) soit 6.

2. Cas particulier : H et L n'existent pas

Lorsque les paramètres H et L déterminant les dimensions de la plage cible ne sont pas définis ( ou L=H=0 )  et que la base est réduite à une cellule, DECALER rend (à la manière d' INDEX) la valeur d'une cellule.

Dans l'exemple illustré ci-dessous DECALER(D4; 2;3) rends le contenu de la cellule F5  soit  25 alors que DECALER(D4:E5;1;2) est l 'erreur de syntaxe #VALEUR.

Cliquer sur l'image pour l'agrandir

 

3. La combinaison DECALER NBVAL

Les paramètres Hauteur et Largeur de la fonction DECALER peuvent être obtenus par "calcul", en autres, grâce à la fonction NBVAL().

information complémentaire disponible en cliquant ici

La fonction NBVAL() nécessite pour seul argument : une plage de cellule

et rend le nombre de cellules non vide de cette plage

Exemple :

DECALER(A1;8;1;NBVAL($B:$B);1) :

Cliquer sur l'image pour l'agrandir

Dans cet exemple, NBVAL($B:$B) rend 9 (contrairement aux apparences B2 (et resp B3 sont vides - ie : fusionnée avec A2 (resp A3))

DECALER(A1;8;1;NBVAL($B:$B);1) équivaut à DECALER(A1;8;1;9;1)

4. La combinaison DECALER EQUIV

Les paramètres NbLigne et NbColonne de la fonction DECALER peuvent être obtenus par "calcul", en autres, grâce à la fonction EQUIV.

information complémentaire disponible en cliquant ici

La fonction EQUIV() utilise 3 arguments :

  1. l'élément cherché
  2. la portion de ligne (ou de colonne) où doit s'effectuer la recherche
  3. 0 (pour rechercher une correspondance exacte)

et rend la position de l'élément cherché dans la (portion de) ligne ou de colonne..

Exemple :

DECALER(A2;EQUIV("Adresse IP";$A:$A;0)-2;EQUIV("B204";$2:$2;0)-1) :

Cette formule est une application du $2 ; elle aura pour résultat la valeur d'une cellule.
Le paramètre NbLigne vaut EQUIV("Adresse IP";$A:$A;0)-2 donc 3 - 2 = 1 . Il faut soustraire 2 car DECALER commence à 0 et la cellule de base de DECALER  est A2 et non A1
Le paramètre NbColonne vaut EQUIV("B204";$A:$A;0)-1 donc 4 -1 = 3. cette fois encore,il faut soustraire 1 car DECALER commence à 0

DECALER(A2;EQUIV("Adresse IP";$A:$A;0)-2;EQUIV("B204";$2:$2;0)-1)  équivaut donc à DECALER(A2;1,3) donc à 172.16.150.16 et rend donc l'adresse IP de la machine en salle B204

Si on transforme la formule en

DECALER(A1;EQUIV("Adresse IP";$A:$A;0)-1;EQUIV("B204";$2:$2;0)-1) :

Cela conduit à la gestion dynamique des données (à découvrir ICI) car cette formule fonctionnera toujours et rendra toujours l'adresse IP de la machine en salle B204  même si sont insérés des critères supplémentaires à n'importe quelle position de la colonne A (la plage $A:$A) et des salles nouvelles à n'importe quelle position de la ligne 2 (la plage $2:$2)