Formule Excel: plage nommée dynamique avec OFFSET -

Table des matières

Formule générique

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

Sommaire

Une façon de créer une plage nommée dynamique avec une formule consiste à utiliser la fonction OFFSET avec la fonction COUNTA. Les plages dynamiques sont également appelées plages extensibles - elles s'étendent et se contractent automatiquement pour s'adapter aux données nouvelles ou supprimées.

Remarque: OFFSET est une fonction volatile, ce qui signifie qu'elle recalcule à chaque modification apportée à une feuille de calcul. Avec une machine moderne et un ensemble de données plus petit, cela ne devrait pas poser de problème, mais vous pouvez voir des performances plus lentes sur de grands ensembles de données. Dans ce cas, envisagez plutôt de créer une plage nommée dynamique avec la fonction INDEX.

Dans l'exemple illustré, la formule utilisée pour la plage dynamique est:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Explication

Cette formule utilise la fonction OFFSET pour générer une plage qui se développe et se contracte en ajustant la hauteur et la largeur en fonction d'un nombre de cellules non vides.

Le premier argument dans OFFSET représente la première cellule des données (l'origine), qui dans ce cas est la cellule B5. Les deux arguments suivants sont des décalages pour les lignes et les colonnes et sont fournis comme zéro.

Les deux derniers arguments représentent la hauteur et la largeur. La hauteur et la largeur sont générées à la volée en utilisant COUNTA, ce qui rend la référence résultante dynamique.

Pour la hauteur, nous utilisons la fonction COUNTA pour compter les valeurs non vides dans la plage B5: B100. Cela suppose qu'il n'y a pas de valeurs vides dans les données et aucune valeur au-delà de B100. COUNTA renvoie 6.

Pour la largeur, nous utilisons la fonction COUNTA pour compter les valeurs non vides dans la plage B5: Z5. Cela suppose aucune cellule d'en-tête et aucun en-tête au-delà de Z5. COUNTA renvoie 6.

À ce stade, la formule ressemble à ceci:

=OFFSET(B5,0,0,6,6)

Avec ces informations, OFFSET renvoie une référence à B5: G10, qui correspond à une plage de 6 lignes de hauteur sur 6 colonnes de diamètre.

Remarque: Les plages utilisées pour la hauteur et la largeur doivent être ajustées pour correspondre à la disposition de la feuille de calcul.

Variante avec références complètes de colonne / ligne

Vous pouvez également utiliser des références complètes de colonne et de ligne pour la hauteur et la largeur comme ceci:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Notez que la hauteur est ajustée avec -2 pour prendre en compte les valeurs d'en-tête et de titre dans les cellules B4 et B2. L'avantage de cette approche est la simplicité des gammes à l'intérieur de COUNTA. L'inconvénient vient des colonnes et des lignes pleines de taille énorme - il faut prendre soin d'éviter des valeurs erronées en dehors de la plage, car elles peuvent facilement annuler le décompte.

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: