Commencons ce tuto sur la fonction décaler sur Microsoft Excel !
La fonction DECALER est en fait une fonction de type « poupée-russe » ! En fait elle cache deux fonctions en une seule. Nous allons voir cela en détail.
Description
Cette fonction permet de ressortir une valeur d’une plage de données en se décalant d’un nombre de ligne et de colonne :
- Référence : Cellule de départ
- Lignes : Nombre de lignes à décaler
- Colonnes : Nombre de colonnes à décaler
Les deux critères suivants ne sont pas obligatoires. Une fois définis, nous obtenons une fonction un peu différente (d’où la double fonction) :
- [Hauteur] : Nombre de lignes attendues
- [Largeur] : Nombre de colonnes attendues
Transposition de données
En utilisant les trois premiers critères de la fonction, on peut créer un tableau de transposition :
L’objectif est de partir de la cellule C2 et d’extraire la nième valeur (en ligne), correspondante au mois désirée. Dans cet exemple, il n’y a aucun décalage en colonne.
Il est aussi possible de se décaler en négatif :
Extraction de données
Dans cet exemple, l’objectif est d’extraire des données d’une table :
Grâce aux fonctions EQUIV, il est possible de ressortir la position d’une valeur dans une plage :
« Septembre » correspond au 9ème mois et « Pantalons » à la 3ème ligne de la table (en omettant les entêtes).
Il est donc possible de trouver le nombre de ventes de pantalons pour le mois de septembre en se décalant de la première cellule d’entête de 3 lignes et de 9 colonnes.
Pour ceux qui lisent régulièrement les tutos, cela ressemble étrangement à la fonction INDEX (tuto Excel « recherche V vs index equiv ») !
Il est également possible de combiner les deux fonctions EQUIV dans la fonction DECALER :
DECALER(Table2[[#En-têtes];[Produits]];EQUIV($B$13;Table2[Produits];0);EQUIV($B$11;Table2[[#En-têtes];[Janvier]:[Décembre]];0))
Somme dynamique
En utilisant les deux derniers paramètres de la fonction DECALER, on obtient une plage de données. Donc, il n’est pas possible d’utiliser cette fonction seule. Elle doit être combinée à une autre, comme par exemple une somme.
L’objectif est donc de faire la somme d’une plage de données variable :
La formule fait la somme de la formule :
DECALER([@Produits];0;1;1;$C$11)
On part de la ligne des produits, on se décaler d’aucune ligne, d’une colonne. Nous arrivons donc sur le mois de Janvier. On définit la hauteur à 1 ligne (et oui, on ne mélange pas les chemises et les bottes !), et une largeur dépendante du mois choisi en cellule B11 : soit 9 colonnes.
Ici, une mise en forme conditionnelle a été appliquée pour rendre le tableau plus sympathique :
Menus déroulants dynamiques
L’objectif est de faire un menu déroulant sur une liste de valeurs qui est amenée à évoluer :
Pour cela, il faut définir une plage nommée (dans le ruban Excel, « Données » → « Gestionnaire de noms »), puis saisir :
DECALER($A$1;1;0;NBVAL($A:$A)-1;1)
On part de la cellule A1, on se décale d’une ligne (on ne prend pas la ligne d’entête), 0 colonne. On souhaite une hauteur dépendante du nombre de valeurs dans la colonne A grâce au NBVAL($A:$A) (en retirant le comptage de la ligne d’entête) et une largeur de 1 colonne.
Si l’on ajoute des données, le menu déroulant s’ajuste automatiquement :
Il fait absolument que les données soient contiguës.
Les Experts Excel vous proposent des formations, du développement, de l’expertise et de l’assistance sur Microsoft Excel. N’hésitez plus et contactez-les dès maintenant !