Élargir automatiquement de données RECHERCHEV

Le 2ème argument de la fonction RECHERCHEV est le tableau_table. Il s’agit de la plage de cellules contenant les données à utiliser dans la fonction RECHERCHEV. Par exemple, dans le code ci-dessous A1: C9 est le tableau_table.

=VLOOKUP("O Redding",A1:C9,3,FALSE) Lorsque vous utilisez la fonction RECHERCHEV, nous savons souvent combien de lignes il y a dans les données, nous savons donc quelles références de cellule inclure dans le tableau_table. Mais, il existe également des circonstances où des lignes supplémentaires seront ajoutées aux données existantes, par conséquent, la répartition exacte des données n'est pas connue. Si vous en avez fait l'expérience, vous avez probablement suivi l'une des méthodes suivantes:

• • Inclus un tableau_table ce qui est nettement plus grand que nécessaire pour que les données futures puissent être ajoutées (cette méthode peut entraîner des temps de calcul plus lents).
  • • Mise à jour du tableau_table
      argument chaque fois que les données changent pour inclure les données supplémentaires (cette méthode nécessite des mises à jour / modifications régulières de la formule RECHERCHEV).

   

  • • Inséré les nouvelles lignes dans l’existant tableau_table, plutôt qu’en bas (cette méthode vous oblige à vous rappeler de les insérer au milieu et non en bas. De plus, comment les autres utilisateurs sauront-ils qu’il faut ajouter les nouvelles données au milieu).

   

  Mais saviez-vous qu’il existe d’autres moyens? Les méthodes, qui une fois configurées, ne nécessitent pas la mise à jour de la fonction RECHERCHEV ou le placement intelligent des données. Dans cet article, nous examinerons ces options:

  1. 1. Tableaux Excel

   

  1. 1. Plages nommées dynamiques

     Tableaux Excel

     Un tableau Excel est une fonctionnalité souvent mal comprise, mais nous n’entrerons pas dans les détails ici. Le point clé de cet article est qu’une table se développe ou se rétracte automatiquement pour s’adapter aux données.

     Mise en place de la table

     Pour configurer un tableau, sélectionnez simplement les cellules et cliquez sur Insérer -> Tableau (Raccourci: Ctrl + T) La fenêtre Créer une table s’ouvre. Nos données ont des en-têtes et se trouvent dans la plage indiquée, nous pouvons donc cliquer sur D’accord. Le tableau est maintenant formaté automatiquement pour être agrégé (ceci peut facilement être changé si vous le souhaitez). Nous renommerons la table en un nom plus utile. Cliquez n’importe où dans le tableau, puis cliquez sur Outils de table: conception dans le ruban. Renommez la table dans le Nom de la table boîte.

     Utilisation de la table

     Nous pouvons maintenant utiliser la table «TelephoneList» comme table_array dans le VLOOKUP. La cellule F4 comprend la formule suivante:

     =VLOOKUP(F2,TelephoneList,3,FALSE) Chaque fois que de nouvelles lignes sont ajoutées aux données, la source de RECHERCHEV sera automatiquement mise à jour pour inclure ces cellules. Dans la capture d'écran ci-dessous, B Adams a été ajouté aux cellules A10-C10 et «TelephoneList» a également été mis à jour automatiquement. B Adams peut maintenant être trouvé dans la RECHERCHEV sans avoir besoin de changer la formule RECHERCHEV.
     
     

     Plages nommées dynamiques

     Une plage nommée est un groupe de cellules auquel un autre nom peut faire référence. Par exemple, vous pouvez cliquer sur les cellules A1-C9 et leur donner le nom «myRange». «MyRange» peut désormais être utilisé dans des formules au lieu de référencer A1-C9. Pour configurer une plage nommée, vous utiliserez l’une des deux méthodes

     Configuration rapide de la gamme nommée

     Pour configurer rapidement une plage nommée, mettez simplement les cellules en surbrillance et saisissez le nom dans la zone de plage.

     Définition du nom

     Pour une configuration plus détaillée, utilisez la section Définir le nom du ruban Formules. À l’aide des menus, cliquez sur: Formules -> Définir des noms -> Définir un nom. Une nouvelle fenêtre apparaîtra dans laquelle vous pourrez saisir les informations de votre plage nommée. Puis clique D’accord.

     Configuration d’une plage nommée dynamique

     Mais nous pouvons aller plus loin. Nous pouvons utiliser des formules pour laisser Excel s’entraîner à la taille que la plage nommée doit être, c’est ce qu’on appelle une plage nommée dynamique. En utilisant une plage nommée dynamique, elle s’étendra automatiquement pour inclure toutes les nouvelles données ajoutées en bas. Ces plages nommées dynamiques rencontrent des problèmes lorsqu’il existe des cellules vides ou lorsque des données supplémentaires sont insérées sous la table de recherche. Il existe deux méthodes différentes utilisées ci-dessous pour résoudre ces deux problèmes. Pour l’une ou l’autre option, configurez la plage nommée dynamique en définissant le nom. Mais au lieu d’utiliser des références à une plage de cellules, nous utiliserons la fonction INDEX pour créer la plage. OPTION 1: basez la plage sur la dernière cellule utilisée dans une colonne. Regardez la fonction utilisée dans cet exemple.

     =Sheet1!$A$1:INDEX(Sheet1!$C:$C,MAX((Sheet1!$C:$C<>"")*(ROW(Sheet1!$C:$C))))

     Il s’agit d’une formule matricielle, appuyez donc sur Ctrl + Maj + Entrée pour entrer la formule dans le Réfère faire de la boxe.

     La formule commence par le nom de la feuille et une référence à la première cellule. La fonction INDEX est utilisée avec les fonctions MAX et ROW pour renvoyer l’adresse de la dernière cellule utilisée dans la colonne. Dans l’exemple ci-dessus, la fonction INDEX renvoie une référence de cellule à partir de la colonne C.

     Cette formule fonctionne toujours là où il y a des cellules vides, mais peut créer des plages plus grandes que nécessaire s’il y a des cellules utilisées sous lookup_data.

     OPTION 2: compter le nombre de cellules dans une colonne

     

     Regardez la fonction utilisée dans cet exemple.

     =Sheet1!$A$1:INDEX(Sheet1!$C:$C,COUNTA(Sheet1!$C:$C))

     La formule commence par le nom de la feuille et une référence à la première cellule. La fonction INDEX est ensuite utilisée conjointement avec les fonctions COUNTA pour renvoyer l’adresse en fonction du nombre de cellules non vides. Dans l’exemple ci-dessus, la fonction INDEX renvoie une référence de cellule à partir de la colonne C.

     Cette formule ne fonctionne pas correctement là où il y a des cellules vides, car elle peut créer une plage plus petite que nécessaire.

     Utilisation d’une plage nommée dynamique avec RECHERCHEV

     Nous pouvons maintenant utiliser «myRange» comme tableau_table dans le VLOOKUP.

     

     La cellule F4 comprend la formule suivante:

     =VLOOKUP(F2,myRange,3,FALSE)

     Chaque fois que de nouvelles lignes sont ajoutées aux données, la RECHERCHEV sera automatiquement mise à jour pour inclure ces cellules. B Adams a été ajouté aux cellules A10-C10 et «myRange» a également été mis à jour automatiquement. B Adams se trouve maintenant dans la RECHERCHEV.

     

     Vous pouvez continuer à ajouter des données jusqu’à 500 lignes et la RECHERCHEV fonctionnerait. Si vous voulez plus de 500 lignes, mettez simplement à jour les références de cellule à la fonction INDEX dans «myRange».

     Autres articles de la série Mastering VLOOKUP

     N’oubliez pas:

     Si vous avez trouvé cet article utile ou si vous avez une meilleure approche, veuillez laisser un commentaire ci-dessous.

     Avez-vous besoin d’aide pour l’adapter à vos besoins?

     Je suppose que les exemples de cet article ne correspondaient pas exactement à votre situation. Nous utilisons tous Excel différemment, il est donc impossible d’écrire un article qui répondra aux besoins de chacun. En prenant le temps de comprendre les techniques et principes de cet article (et ailleurs sur ce site) vous devriez pouvoir l’adapter à vos besoins.

     Mais si vous avez encore du mal :

     1. Lisez d’autres blogs ou regardez des vidéos YouTube sur le même sujet. Vous en bénéficierez beaucoup plus en découvrant vos propres solutions.
     2. Demandez le «Excel Ninja» dans votre bureau. C’est incroyable ce que les autres savent.
     3. Posez une question dans un forum   Excel, ou la Communauté Microsoft Answers. N’oubliez pas que les personnes présentes sur ces forums donnent généralement leur temps gratuitement. Veillez donc à rédiger votre question, assurez-vous qu’elle est claire et concise. Répertoriez toutes les choses que vous avez essayées et fournissez des captures d’écran, des segments de code et des exemples de classeurs.

     Et ensuite?
     N’y allez pas encore, il y a beaucoup plus à apprendre sur comment-supprimer. Consultez les derniers articles:

     sauvegarder