Formule Excel: plage nommée dynamique avec INDEX -

Table des matières

Formule générique

=$A$1:INDEX($A:$A,lastrow)

Sommaire

Une façon de créer une plage nommée dynamique dans Excel consiste à utiliser la fonction INDEX. Dans l'exemple illustré, la plage nommée "data" est définie par la formule suivante:

=$A$2:INDEX($A:$A,COUNTA($A:$A))

qui se résout dans la plage $ A $ 2: $ A $ 10.

Remarque: cette formule est destinée à définir une plage nommée qui peut être utilisée dans d'autres formules.

Explication

Cette page montre un exemple de plage nommée dynamique créée avec la fonction INDEX avec la fonction COUNTA. Les plages nommées dynamiques se développent et se contractent automatiquement lorsque des données sont ajoutées ou supprimées. Ils constituent une alternative à l'utilisation d'un tableau Excel, qui se redimensionne également lorsque des données sont ajoutées ou supprimées.

La fonction INDEX renvoie la valeur à une position donnée dans une plage ou un tableau. Vous pouvez utiliser INDEX pour récupérer des valeurs individuelles ou des lignes et colonnes entières dans une plage. Ce qui rend INDEX particulièrement utile pour les plages nommées dynamiques, c'est qu'il renvoie en fait une référence. Cela signifie que vous pouvez utiliser INDEX pour construire une référence mixte comme $ A $ 1: A100.

Dans l'exemple illustré, la plage nommée "data" est définie par la formule suivante:

=$A$2:INDEX($A:$A,COUNTA($A:$A))

qui se résout dans la plage $ A $ 2: $ A $ 10.

Comment fonctionne cette formule

Notez d'abord que cette formule est composée de deux parties qui se trouvent de chaque côté de l'opérateur de plage (:). Sur la gauche, nous avons la référence de départ pour la plage, codée en dur comme:

$A$2

Sur la droite se trouve la référence de fin de la plage, créée avec INDEX comme ceci:

INDEX($A:$A,COUNTA($A:$A))

Ici, nous alimentons INDEX toute la colonne A pour le tableau, puis utilisons la fonction COUNTA pour déterminer la "dernière ligne" de la plage. COUNTA fonctionne bien ici car il y a 10 valeurs dans la colonne A, y compris une ligne d'en-tête. COUNTA renvoie donc 10, qui va directement dans INDEX comme numéro de ligne. INDEX renvoie ensuite une référence à $ A $ 10, la dernière ligne utilisée de la plage:

INDEX($A:$A,10) // resolves to $A$10

Ainsi, le résultat final de la formule est cette plage:

$A$2:$A$10

Une gamme bidimensionnelle

L'exemple ci-dessus fonctionne pour une plage unidimensionnelle. Pour créer une plage dynamique bidimensionnelle où le nombre de colonnes est également dynamique, vous pouvez utiliser la même approche, développée comme ceci:

=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))

Comme précédemment, COUNTA est utilisé pour déterminer le "lastrow", et nous utilisons à nouveau COUNTA pour obtenir le "lastcolumn". Ceux-ci sont fournis pour indexer respectivement comme row_num et column_num.

Cependant, pour le tableau, nous fournissons la feuille de calcul complète, entrée comme toutes les 1048576 lignes, ce qui permet à INDEX de renvoyer une référence dans un espace 2D.

Remarque: Excel 2003 ne prend en charge que 65535 lignes.

Déterminer la dernière ligne

Il existe plusieurs façons de déterminer la dernière ligne (dernière position relative) dans un ensemble de données, en fonction de la structure et du contenu des données dans la feuille de calcul: