Si vous travaillez sur des tableaux de plusieurs dizaines de colonnes, cet article, va vous montrer comment la fonction CHOISIRCOLS va vous aider à reconstituer un tableau en utilisant les noms des 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
- Le tableau initial
Sélectionner toutes les cellules de votre tableau
- Première colonne à retourner
Indiquez seulement le numéro de colonne à renvoyer
- Deuxième colonne à retourner
Idem, indiquer la colonne à renvoyer et ainsi de suite
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)
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
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.
Il faut maintenant trouver le numéro de colonne associé au nom sélectionné. Pour cela, il y a la fonction EQUIV.
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.
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))
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). Nous avons utilisé l'astuce avec F9 pour afficher le détail de ESTVIDE(C24:I24)
Et on recherche le dernier paramètre -1
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))
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
Aleph
Frédéric LE GUEN
23/02/2023 @ 12:39
Bonjour,
Je ne comprends pas pourquoi faire une gymnastique si compliqué pour retourné la plage des en-têtes.
Cela fonctionne bien sûr mais n'aurait il pas été plus simple de faire :
=CHOISIRCOLS(tbl_Clients;EQUIV(FILTRE(C24:I24;C24:I24"");
tbl_Clients[#En-têtes];0))
Cela me semble beaucoup plus intuitif et direct.
23/02/2023 @ 14:02
C'est toute la beauté d'Excel, il y a toujours qqun pour trouver une meilleure solution. Et non, je n'ai pas eu cette idée qui est meilleure.
24/02/2023 @ 16:12
Merci beaucoup pour ce compliment.
Votre site est vraiment formidable.
J'ai eu du mal à comprendre l'étape 6; votre proposition est pour moi une source de beaucoup de découvertes et de nouvelles perspectives.
Je suppose que la fonction RECHERCHEX renvoie une plage plutôt qu'une valeur, ce qui donne beaucoup de richesses supplémentaires à cet outil que j'adore aussi 🙂
26/02/2023 @ 13:53
Bonjour,
C'est exactement ça. RECHERCHEX renvoie un tableau (et pas seulement une cellule). C'est un sujet que je ne développe par trop sur mon site car il est un peu trop "expert". Mais si ça te donne des idées et que tu veux partager tes expériences sur les fonctions qui retournent des tableaux, ça peut être l'occasion de nouveaux articles