Matricielles

FILTRE avec colonnes séparées

Temps de lecture 3 minutes

La fonction FILTRE, ne permet normalement pas de renvoyer un résultat sur 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.

Construire un tableau en mémoire

Quelques 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}

Affichage des valeurs a partir de lecriture dune matrice

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)
Votre symbole de delimiteur dune matrice retourne en VBA
Le point est le symbole du délimiteur de matrice sur l'ordinateur où est installé Excel

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é auprès de nos clients.

Tableau initial a filtrer

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)

Tableau renvoyer par la fonction CHOISIR

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)

FILTRE sur des colonnes separees avec la fonction CHOISIR

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)

Colonnes reordonnees grace a lutilisation de la fonction CHOISIR

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.

Recherche en ligne et en 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)

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

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

  1. 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})

Fonction FILTRE avec la fonction INDEX et SEQUENCE

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}

Related posts

Trouver le nombre de lignes

Frédéric LE GUEN

TRIER dynamiquement vos données

Frédéric LE GUEN

C’est quoi #EPARS!

Frédéric LE GUEN

Laissez un commentaire

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

Ce site utilise des cookies pour améliorer votre expérience et vos recherches. Nous pensons que vous êtes dʼaccord sur ce principe mais vous pouvez refuser cette option. Accepter Continuer

Privacy & Cookies Policy