Table des matières
Avez-vous des tableaux avec des données dans Excel qui peuvent être redimensionnées, c'est-à-dire que le nombre de lignes (colonnes) peut augmenter ou diminuer au cours du travail ? Si la taille du tableau "flotte", vous devrez surveiller en permanence ce moment et le corriger :
- liens dans les formules de rapport qui font référence à notre table
- plages initiales de tableaux croisés dynamiques qui sont construits selon notre tableau
- plages initiales de cartes construites selon notre table
- plages pour les listes déroulantes qui utilisent notre table comme source de données
Tout cela au total ne vous laissera pas vous ennuyer 😉
Il sera beaucoup plus pratique et correct de créer une plage dynamique "en caoutchouc", dont la taille s'ajustera automatiquement au nombre réel de lignes et de colonnes de données. Pour mettre cela en œuvre, il existe plusieurs façons.
Méthode 1. Table intelligente
Mettez en surbrillance votre plage de cellules et sélectionnez dans l'onglet Accueil – Formater comme tableau (Accueil – Formater comme tableau):
Si vous n'avez pas besoin du motif rayé ajouté au tableau comme effet secondaire, vous pouvez le désactiver dans l'onglet qui apparaît. Constructeur (Conception). Chaque tableau ainsi créé reçoit un nom qui peut être remplacé par un autre plus pratique au même endroit sur l'onglet Constructeur (Conception) dans le champ Nom de la table (Nom de la table).
Nous pouvons maintenant utiliser des liens dynamiques vers notre "table intelligente":
- Tableau 1 – lien vers l'ensemble du tableau à l'exception de la ligne d'en-tête (A2:D5)
- Tableau1[#Tous] – lien vers l'ensemble du tableau (A1:D5)
- Tableau1[Pierre] – référence à une colonne de plage sans le premier en-tête de cellule (C2:C5)
- Tableau1[#Entêtes] – lien vers le « header » avec les noms des colonnes (A1:D1)
De telles références fonctionnent très bien dans les formules, par exemple :
= SUM (Tableau1[Moscou]) – calcul de la somme pour la colonne « Moscou »
or
=VPR(F5;Tableau 1;3;0) - recherchez dans le tableau le mois à partir de la cellule F5 et émettez la somme de Saint-Pétersbourg correspondante (qu'est-ce que RECHERCHEV ?)
Ces liens peuvent être utilisés avec succès lors de la création de tableaux croisés dynamiques en sélectionnant dans l'onglet Insérer – Tableau croisé dynamique (Insérer – Tableau croisé dynamique) et en saisissant le nom de la table intelligente comme source de données :
Si vous sélectionnez un fragment d'un tel tableau (par exemple, les deux premières colonnes) et créez un diagramme de n'importe quel type, lors de l'ajout de nouvelles lignes, elles seront automatiquement ajoutées au diagramme.
Lors de la création de listes déroulantes, les liens directs vers les éléments de tableau intelligent ne peuvent pas être utilisés, mais vous pouvez facilement contourner cette limitation à l'aide d'une astuce tactique : utilisez la fonction INDIRECT (INDIRECT), qui transforme le texte en lien :
Ceux. un lien vers une table intelligente sous la forme d'une chaîne de texte (entre guillemets !) se transforme en un lien à part entière, et la liste déroulante le perçoit normalement.
Méthode 2 : plage nommée dynamique
Si transformer vos données en une table intelligente n'est pas souhaitable pour une raison quelconque, vous pouvez utiliser une méthode légèrement plus compliquée, mais beaucoup plus subtile et polyvalente - créer une plage nommée dynamique dans Excel qui fait référence à notre table. Ensuite, comme dans le cas d'un tableau intelligent, vous pouvez librement utiliser le nom de la plage créée dans n'importe quelles formules, rapports, graphiques, etc. Commençons par un exemple simple :
Tâche : créer une plage nommée dynamique qui ferait référence à une liste de villes et s'étirerait et se rétrécirait automatiquement lors de l'ajout ou de la suppression de nouvelles villes.
Nous aurons besoin de deux fonctions Excel intégrées disponibles dans n'importe quelle version - POICPOZ (RENCONTRE) pour déterminer la dernière cellule de la plage, et INDEX (INDICE) pour créer un lien dynamique.
Trouver la dernière cellule à l'aide de MATCH
MATCH(lookup_value, plage, match_type) – une fonction qui recherche une valeur donnée dans une plage (ligne ou colonne) et renvoie le nombre ordinal de la cellule où elle a été trouvée. Par exemple, la formule MATCH("Mars";A1:A5;0) renverra le nombre 4 en conséquence, car le mot "Mars" se trouve dans la quatrième cellule de la colonne A1:A5. Le dernier argument de la fonction Match_Type = 0 signifie que nous recherchons une correspondance exacte. Si cet argument n'est pas spécifié, la fonction passera en mode de recherche pour la plus petite valeur la plus proche - c'est exactement ce qui peut être utilisé avec succès pour trouver la dernière cellule occupée dans notre tableau.
L'essence de l'astuce est simple. MATCH recherche les cellules dans la plage de haut en bas et, en théorie, devrait s'arrêter lorsqu'il trouve la plus petite valeur la plus proche de celle donnée. Si vous spécifiez une valeur qui est manifestement supérieure à toute valeur disponible dans le tableau comme valeur souhaitée, alors MATCH atteindra la toute fin du tableau, ne trouvera rien et donnera le numéro de séquence de la dernière cellule remplie. Et nous en avons besoin !
S'il n'y a que des nombres dans notre tableau, nous pouvons spécifier un nombre comme valeur souhaitée, qui est évidemment supérieure à toutes celles du tableau :
Pour une garantie, vous pouvez utiliser le nombre 9E + 307 (9 fois 10 à la puissance 307, c'est-à-dire 9 avec 307 zéros) – le nombre maximum avec lequel Excel peut fonctionner en principe.
S'il y a des valeurs de texte dans notre colonne, alors comme équivalent du plus grand nombre possible, vous pouvez insérer la construction REPEAT("i", 255) - une chaîne de texte composée de 255 lettres "i" - la dernière lettre de l'alphabet. Étant donné qu'Excel compare réellement les codes de caractères lors de la recherche, tout texte dans notre tableau sera techniquement "plus petit" qu'une ligne "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy":
Générer un lien avec INDEX
Maintenant que nous connaissons la position du dernier élément non vide dans le tableau, il reste à former un lien vers l'ensemble de notre gamme. Pour cela nous utilisons la fonction :
INDEX(plage ; num_ligne ; num_colonne)
Il donne le contenu de la cellule de la plage par numéro de ligne et de colonne, c'est-à-dire par exemple, la fonction =INDEX(A1:D5;3;4) dans notre tableau avec les villes et les mois de la méthode précédente donnera 1240 - le contenu de la 3e ligne et de la 4e colonne, c'est-à-dire les cellules D3. S'il n'y a qu'une seule colonne, son numéro peut être omis, c'est-à-dire que la formule INDEX(A2:A6;3) donnera "Samara" dans la dernière capture d'écran.
Et il y a une nuance pas tout à fait évidente: si l'INDEX n'est pas simplement entré dans la cellule après le signe =, comme d'habitude, mais est utilisé comme dernière partie de la référence à la plage après les deux-points, alors il ne donne plus le contenu de la cellule, mais son adresse ! Ainsi, une formule comme $A$2:INDEX($A$2:$A$100;3) donnera une référence à la plage A2:A4 en sortie.
Et c'est là qu'intervient la fonction MATCH, que nous insérons à l'intérieur de l'INDEX pour déterminer dynamiquement la fin de la liste :
=$A$2:INDEX($A$2:$A$100; EQUIV(REP("I";255);A2:A100))
Créer une plage nommée
Il reste à emballer le tout en un seul tout. Ouvrir un onglet formule (Formules) Et cliquez sur le Gestionnaire de noms (Gestionnaire de noms). Dans la fenêtre qui s'ouvre, cliquez sur le bouton Création (Nouveau), saisissez le nom et la formule de notre gamme dans le champ Catégorie (Référence):
Il reste à cliquer sur OK et la plage prête peut être utilisée dans toutes les formules, listes déroulantes ou graphiques.
- Utilisation de la fonction RECHERCHEV pour lier des tables et des valeurs de recherche
- Comment créer une liste déroulante à remplissage automatique
- Comment créer un tableau croisé dynamique pour analyser une grande quantité de données