Excel Dynamic Named Range: Comment créer et utiliser

Ce tutoriel vous montre comment créer et utiliser des plages dynamiques nommées dans Excel, mettant automatiquement à jour les calculs à mesure que vos données changent. Contrairement aux gammes nommées statiques, les plages dynamiques s'adaptent pour inclure de nouvelles données sans intervention manuelle.

Le tutoriel de la semaine dernière a couvert des gammes statiques nommées. Si vos données changent fréquemment, les plages dynamiques nommées sont essentielles pour des calculs efficaces et automatisés. Ce tutoriel fournit des instructions étape par étape.

Sujets clés:

• Création de gammes de gammes dynamiques unidimensionnelles
• Création de plages nommées dynamiques bidimensionnelles
• Utilisation de gammes dynamiques nommées dans des formules

Création de gammes dynamiques nommées dans Excel

Commençons par une plage dynamique nommée de dynamique à rangs variables:

1. Accédez à l'onglet Formules , puis dans le groupe de noms définis et sélectionnez Définir le nom . Alternativement, appuyez sur Ctrl F3 pour ouvrir le gestionnaire de noms et cliquez sur Nouveau… .
2. La boîte de dialogue du nouveau nom apparaît. Entrer:
   • Un nom pour votre gamme (par exemple, "éléments").
   • Sélectionnez la portée (généralement «classeur»).
   • Dans la boîte fait référence , utilisez une formule OFFSET ou INDEX (expliqué ci-dessous).
3. Cliquez sur OK.

L'image ci-dessous montre la définition d'une plage dynamique nommée "éléments" englobant toutes les données de la colonne A (à l'exclusion de l'en-tête):

En utilisant la formule OFFSET

La formule OFFSET général pour une plage dynamique est:

OFFSET(first_cell, 0, 0, COUNTA(range), 1)

Où:

• first_cell est la cellule supérieure à gauche de votre gamme (par exemple, $A$1 ).
• range est la colonne entière (par exemple, $A:$A ) ou une gamme suffisamment grande pour englober vos données prévues (par exemple, $A$2:$A$2000 ).

COUNTA compte les cellules non liées, déterminant la hauteur de la plage. Ajustez les lignes d'en-tête en soustrayant le nombre de lignes d'en-tête du résultat COUNTA . Par exemple, si les données commencent dans A2 (une ligne d'en-tête):

=OFFSET(Sheet3!$A$2, 0, 0, COUNTA(Sheet3!$A:$A)-1, 1)

En utilisant la formule INDEX

Alternativement, utilisez INDEX et COUNTA :

first_cell:INDEX(column, COUNTA(column))

Cela combine une cellule de départ fixe (par exemple, $A$2 ) avec une cellule de fin déterminée par INDEX et COUNTA . Par exemple:

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

COUNTA renvoie le nombre de cellules non verbales; INDEX utilise cela pour trouver la dernière cellule. Le résultat est une plage dynamique.

Notes importantes sur OFFSET et INDEX :

• OFFSET est volatile (recalcule fréquemment), ce qui a un impact potentiellement sur les performances sur les grands ensembles de données. INDEX est généralement plus efficace.
• Évitez les cellules vierges dans la plage de données pour les deux formules.
• Assurez-vous qu'aucune donnée supplémentaire n'existe sous la plage dynamique.

Création de plages dynamiques bidimensionnelles

Pour les gammes avec des lignes et des colonnes dynamiques, modifiez la formule INDEX / COUNTA :

first_cell:INDEX($1:$1048576, COUNTA(first_column), COUNTA(first_row))

Ceci utilise deux fonctions COUNTA pour déterminer la dernière ligne et colonne. La feuille de travail entière ( $1:$1048576 ) est utilisée comme tableau.

Utilisation de gammes dynamiques nommées dans des formules

Les plages dynamiques s'intègrent parfaitement dans les formules. L'exemple ci-dessous montre une formule INDEX / MATCH à l'aide de plages dynamiques nommées ( Lookup_range , Return_range , Lookup_value ) pour les mises à jour automatiques en tant que modifications de données:

=INDEX(Return_range, MATCH(Lookup_value, Lookup_range, 0))

Cela élimine les mises à jour de la formule manuelle lors de l'ajout de nouvelles données.

Exemple de classeur téléchargeable:

Excel Dynamic Named Range - Exemple de classeur

Ce tutoriel fournit un guide complet pour créer et utiliser des gammes dynamiques nommées dans Excel pour des feuilles de calcul plus efficaces et adaptables.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!