Simplifier le travail avec des tableaux ayant de nombreuses colonnes

Temps de lecture : 3 minutes

Si vous travaillez sur des tableaux de plusieurs dizaines de colonnes, cet article, va vous montrer comment la fonction CHOISIRCOLS va vous aidez à reconstituer un tableau en utilisant les noms des colonnes.

Tableau avec un grand nombre de colonnes

Les données sont dans un Tableau afin de simplifier leurs sélections.

Etape 1 : Présentation de la fonction CHOISIRCOLS

CHOISIRCOLS est une nouvelle fonction qui est disponible avec Office 365 et Excel Online et qui permet d'extraire certaines colonnes d'un tableau existant. Ses paramètres sont

  • Le tableau initial
  • La première colonne à retourner (numéro de la colonne)
  • La seconde colonne
  • .....

Par exemple, si nous voulons retourner le prénom, le nom de famille et la ville, voici la formule que nous devons écrire

=CHOISIRCOLS(tbl_Clients;3;5;7)

Fonction CHOISIRCOLS pour retourner 3 colonnes

Etape 2 : Créer un menu déroulant

Nous allons créer un menu déroulant en prenant comme source la ligne d'en-têtes du Tableau

Menu deroulant a partir de lentete du tableau

Etape 3 : Convertir le nom d'une colonne en position

A partir du menu déroulant, il est maintenant facile de choisir le titre de l'une des colonnes.

Selection dun des noms de colonne a partir du menu deroulant

Il faut maintenant trouver le numéro de colonne associé au nom sélectionné. Pour cela, il y a la fonction EQUIV.

=EQUIV(C24;tbl_Clients[#En-têtes];0)

Postion de la colonne dans le tableau

Etape 4 : Ajouter EQUIV dans CHOISIRCOLS

Grâce à ce résultat, nous allons remplacer la sélection de la première colonne par la fonction précédente.

=CHOISIRCOLS(tbl_Clients;EQUIV(C24;tbl_Clients[#En-têtes];0))

Combinaison CHOISIRCOLS et EQUIV

Etape 5 : Adapter pour plusieurs colonnes

Maintenant, pour retourner plusieurs colonnes, nous allons changer le premier paramètre de la fonction EQUIV pour prendre en compte toutes les colonnes que nous voulons retourner.

=CHOISIRCOLS(tbl_Clients;EQUIV(C24:E24;tbl_Clients[#En-têtes];0))

La fonction EQUIV prend en compte plusieurs cellules

Mais il reste un dernier problème à résoudre. La plage de cellules de la fonction EQUIV ne peut pas contenir de cellules vides. Il faut donc trouver une autre solution pour prendre en compte les cellules vides

Etape 6 : RECHERCHEX retourne une référence.

C'est la partie la plus difficile de cet article.

Il existe 2 fonctions dans Excel qui permettent de retourner un résultat ou bien la référence d'une cellule ; il s'agit des fonctions INDEX et RECHERCHEX. Nous avons vu comment utiliser cette fonctionnalité pour faire une somme dynamique.

Ici, nous allons construire une fonction RECHERCHEX qui va récupérer la dernière cellule renseignée dans une plage de données.

=RECHERCHEX(FAUX;ESTVIDE(C24:I24);C24:I24;;;-1)

La partie importante, c'est le deuxième paramètre. Ici, on construit un tableau de FAUX (cellule non vide) ou VRAI (cellule vide).

Detail du deuxieme parametre de la fonction RECHERCHEX

Et on recherche le dernier (paramètre -1) FAUX (valeur recherchée en premier paramètre)

Il nous reste à remplacer la seconde référence de la plage dans la fonction EQUIV par la fonction RECHERCHEX. La formule complète et adaptable à toutes les situations est la suivante

=CHOISIRCOLS(tbl_Clients;EQUIV(C24:
RECHERCHEX(FAUX;ESTVIDE(C24:I24);C24:I24;;;-1);
tbl_Clients[#En-têtes];0))

Formule complete pour reduire un tableau

Télécharger le fichier avec la formule complète

Autre solution

Gaetan Mourmant, un autre Microsoft MVP, propose la solution suivante avec FILTRE, SEQUENCE, EXCLURE et LET

=LET(ZonePlageTout;tbl_Clients[#Tout ];Zone_Filtre;C24:M24;
Zone_Plage;EXCLURE(ZonePlageTout;1);
Zone_Plage_EnTete;EXCLURE(ZonePlageTout;1-LIGNES(ZonePlageTout));
FILTRE(Zone_Plage;RECHERCHEX(Zone_Plage_EnTete;FILTRE(Zone_Filtre;Zone_Filtre<>"");
SEQUENCE(1;COLONNES(FILTRE(Zone_Filtre;Zone_Filtre<>""));1;0);0)))

Vidéo

Retrouver toutes les explications dans la vidéo suivante

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

MICROSOFT MVP

RECHERCHE D’EMPLOI AVEC