La fonction DECALER()

La fonction DECALER --- exemple : DECALER (K3:Q23 ; 16 ; 2; 2 ; 3) --- rend la référence d'une plage de cellules lorsque les paramètres qui lui sont transmis sont :

plage : une plage de cellules (dans l'exemple : K3:Q23 ). Cette plage a L  lignes  (pour K3:Q23 ;  L = 21  car 21 = 23-3 +1 --- poteaux et intervalles  ---) et C colonnes (pour  K3:Q23 ; C = 7 : de K  à Q). Cette plage sert de base pour déterminer la référence de la nouvelle plage ;

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 ;

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 ;

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 ;

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.

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

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) lorsque l'on utilise la formule =DECALER( D4:F6 ; 2 ; 0 ; 1 ; 1)

la référence D6 lorsque l'on utilise dans la formule  =SOMME(D4:DECALER( D4:F6 ; 2 ; 0 ; 1 ; 1)) qui équivaut à SOMME(D4:D6) soit 6.

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) rend le contenu de la cellule F4  soit  24 alors que DECALER(D4:E5; 1 ; 2) rend l 'erreur de syntaxe #VALEUR.

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

Exemple :

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

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)

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

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 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).