La fonction FILTRE, ne permet normalement pas de renvoyer un résultat avec des colonnes séparées. Mais en englobant la fonction FILTRE dans d'autres fonctions, on peut réaliser un filtre dynamique sur des colonnes séparées.

Mise à jour

• Si votre version d'Excel 365 possède la fonction CHOISIRCOLS, une solution plus simple est expliquée dans cet article.

Construire un tableau en mémoire

Quelle que soit la technique que vous allez choisir, nous allons devoir nous appuyer sur la création d'un tableau en mémoire (ou encore appelé matrice).

Déjà, la fonction FILTRE renvoie un tableau. Et nous allons nous appuyer sur cette caractéristique pour construire notre tableau en mémoire. Mais ce n'est pas tout, on va devoir aussi construire un autre type de tableau, une matrice, pour la gestion des colonnes à renvoyer.

Ce point n'est pas anodin pour la suite. Car selon les configurations de votre ordinateur, le délimiteur peut être :

• soit le point (.)
• soit la virgule (,)
• soit le backslash (\)

Avec l'ordinateur que j'utilise, le séparateur est le point.

={4.0.1.2}

Le seul moyen de savoir quel est le délimiteur sur votre ordinateur, c'est soit d'essayer chacun des symboles pour trouver lequel est le bon. Ou alors, d'utiliser le VBA et de lancer l'instruction suivante dans la fenêtre exécution.

?Application.International(xlColumnSeparator)

C'est la seule technique pour savoir le symbole à utiliser. L'information n'est pas présente dans les options d'Excel

Présentation du problème à traiter

Nous avons un tableau de 5 colonnes qui représente les ventes que nous avons effectuées auprès de nos clients.

Nous voulons filtrer sur la colonne des dates mais nous voulons retourner les 4 autres colonnes (Magasin, Fruit, Qté et Réglé ?). Pour cela, il y a 3 techniques différentes que nous allons détailler.

Utiliser la fonction CHOISIR

Dans son utilisation normale, la fonction CHOISIR retourne la nième valeur d'une liste. Par exemple, la fonction suivante retourne Mercredi :

=CHOISIR(3;"Lundi";"Mardi";"Mercredi";"Jeudi";"Vendredi")

Mais dans une utilisation plus complexe, nous pouvons remplacer le premier paramètre par une matrice et ainsi renvoyer un tableau.

=CHOISIR({1.2.3.4};A2:A16;B2:B16;D2:D16;E2:E16)

Et donc, il ne nous reste plus qu'à utiliser cette formule en premier paramètre de la fonction FILTRE, pour retourner nos données filtrées avec des colonnes séparées.

=FILTRE(CHOISIR({1.2.3.4};A2:A16;B2:B16;D2:D16;E2:E16);C2:C16=G2)

On peut également changer l'ordre des colonnes à retourner.

=FILTRE(CHOISIR({1.2.3.4};B2:B16;D2:D16;A2:A16;E2:E16);C2:C16=G2)

Construction avec la fonction INDEX

La fonction INDEX permet de retourner la position d'une valeur à l'intersection d'une ligne et d'une colonne.

Mais on peut aussi, remplacer les paramètres 2 et 3 de la fonction INDEX par une matrice de lignes et une matrice de colonnes.

1. Premier paramètre : La fonction FILTRE sur tout le tableau.

FILTRE(A2:E16;C2:C16=G2)

2. Deuxième paramètre : Le nombre de lignes à retourner grâce à une combinaison entre les fonctions NB.SI.ENS et SEQUENCE.

SEQUENCE(NB.SI.ENS(C2:C16;G2))

3. Troisième paramètre : Une matrice représentant les numéros de colonnes à renvoyer.

{1.2.4.5}

La formule complète est la suivante :

=INDEX(FILTRE(A2:E16;C2:C16=G2);SEQUENCE(NB.SI.ENS(C2:C16;G2));{1.2.4.5})

Avec cette technique, il est très facile de permuter les colonnes juste en changeant l'ordre des colonnes à retourner, comme par exemple {2.5.1.4}. Vous trouverez des informations complémentaires sur la fonction CHOISIR sur le site de Microsoft.