Simplifier vos tableaux avec CHOISIRCOLS

Simplifier vos tableaux avec CHOISIRCOLS
Dernière mise à jour le 05/02/2024
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 aider à 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

  1. Le tableau initial

    Sélectionner toutes les cellules de votre tableau

  2. Première colonne à retourner

    Indiquez seulement le numéro de colonne à renvoyer

  3. 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)

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

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

4 Comments

  1. Aleph
    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.

    Reply

    • Frédéric LE GUEN
      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.

      Reply

      • ALEPH
        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 🙂

      • Frédéric LE GUEN
        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

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.

Simplifier vos tableaux avec CHOISIRCOLS

Reading time: 3 minutes
Dernière mise à jour le 05/02/2024

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.

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

  1. Le tableau initial

    Sélectionner toutes les cellules de votre tableau

  2. Première colonne à retourner

    Indiquez seulement le numéro de colonne à renvoyer

  3. 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)

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

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

4 Comments

  1. Aleph
    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.

    Reply

    • Frédéric LE GUEN
      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.

      Reply

      • ALEPH
        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 🙂

      • Frédéric LE GUEN
        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

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.