Une introduction aux plages dynamiques
La fonction RECHERCHEV est souvent utilisée pour rechercher des informations stockées dans des tableaux dans Excel. Donc par exemple si nous avons une liste de noms et d'âges de personnes :
Et puis on peut dans une cellule voisine utiliser la fonction RECHERCHEV pour déterminer l'âge de Paul :
Jusqu'à présent, il s'agit d'un assez standard. Mais que se passe-t-il si nous devons ajouter d'autres noms à la liste ? L'idée évidente serait de modifier la plage dans le VLOOKUP. Cependant, dans un modèle vraiment complexe, il peut y avoir plusieurs références à RECHERCHEV. Cela signifie que nous devrions changer chaque référence - en supposant que nous savions où elles se trouvaient.
Cependant, Excel fournit une alternative - appelée plage DYNAMIQUE. Il s'agit d'une plage qui étend une mise à jour automatiquement. C'est parfait si vos listes sont en constante expansion (par exemple, les données de ventes mensuelles).
Pour configurer une plage dynamique, nous devons avoir un nom de plage - nous appellerons donc le nôtre AGE_DATA. L'approche de configuration des plages dynamiques diffère entre Excel 2007 et les versions antérieures d'Excel :
Dans Excel 2007, cliquez sur « Définir le nom » sous les formules :
Dans les versions antérieures d'Excel, cliquez sur "Insérer" puis sur Noms".
Dans la fenêtre contextuelle, entrez le nom de notre plage dynamique - qui est « DONNEES D'ÂGE » :
Dans la case intitulée « Se réfère à », nous devons entrer la plage de nos données. Ce sera un résultat utilisé par une fonction OFFSET. Cela a 5 arguments:
=OFFSET(Référence, Lignes, Cols, Hauteur, Largeur)
- La Référence est l'adresse du coin HAUT GAUCHE de notre gamme - dans ce cas la cellule B5
- Les lignes sont le nombre de lignes en haut à gauche que nous voulons que cette plage soit - qui sera 0 dans ce cas
- Le Cols est le nombre de lignes en haut à gauche que nous voulons que cette plage soit - qui sera 0 dans ce cas
- La hauteur de la gamme - voir ci-dessous pour cela
- La largeur de la gamme - c'est 2 a nous avons DEUX colonnes dans notre gamme (le nom des personnes et leur âge)
Maintenant, la hauteur de la plage devra varier en fonction du nombre d'entrées dans notre table (qui est actuellement de 7).
Bien sûr, nous voulons un moyen de compter les lignes de notre table qui se met à jour automatiquement - donc une façon de le faire est d'utiliser la fonction COUNTA. Cela compte simplement le nombre de cellules non vides dans une plage. Comme nos noms sont dans la colonne B, le nombre d'entrées dans nos données est COUNTA(B:B).
Notez que si vous deviez mettre ceci dans une cellule, vous obtiendriez la valeur 8 - car elle inclut l'en-tête Names. Cependant, qu'il est sans importance.
Donc, dans la case « Refers To », nous mettons :
=OFFSET($B$5,0,0,counta(B:B),2)
Et cliquez sur le bouton OK. Notre gamme dynamique est maintenant créée.
Revenez maintenant aux formules RECHERCHEV et remplacez la plage $B:4:$C11 par le nom de notre nouvelle plage dynamique AGE_DATA donc nous avons :
Jusqu'à présent, rien n'a changé. Cependant, si nous ajoutons quelques noms supplémentaires à notre table :
Et dans la cellule où nous avions Paul, remplacez-le par un nouveau nom tel que Pedro (qui ne figurait pas sur la liste d'origine) :
Et nous voyons qu'Excel a automatiquement renvoyé l'âge de Pedro - même si nous n'avons pas modifié les formules RECHERCHEV. Au lieu de cela, la portée de la plage dynamique a augmenté pour inclure les noms supplémentaires.
Les plages dynamiques sont très utiles lorsque nous avons des volumes de données croissants, en particulier lorsque des tables VLOOKUP et PIVOT sont requises.
