La fonction DECALER()

Quelques exemples d'utilisation :

  • les listes (dynamiques) de validité ;
  • la gestion (dynamique) des noms ;
  • la recherche de valeurs dans un tableau de données.

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  a L  lignes  (ici pour la plage K3:Q23;  L =21  21 = 23-3 +1 ..poteaux et intervalles ) et C colonnes (ici pour la plage K3:Q23; C= 7 : de K  à Q). Cette plage sert de base pour déterminer la référence de la nouvelle plage ;
  2. nb_ligne : un nombre entier, que je nomme NB_LIG,  compris entre 0 et L : (dans 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 (dans l'exemple : NB_COL= 2 et 2 <= 7) qui sera le décalage horizontal ;
  4. hauteur : un nombre entier compris entre 1 et L - NB_LIG (dans l'exemple : H= 2 et 1<= 2 <= 21 - 16) qui sera le nombre de lignes de la  nouvelle plage ;
  5. largeur : un nombre entier compris entre 1 et C - NB_COL (dans l'exemple : L= 3 et 1 <= 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 a un fond coloré 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 (donc 3) dans la formule =DECALER(D4:F6;2;0;1;1)
  • la référence D6 dans la formule =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 plage de 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; 1;2) rends le contenu de la cellule F4  soit  24 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 vides 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)