Limiter le nombre de données extraites avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Limiter les extractions

En emboîtant la fonction Excel Filtre dans la fonction Index, il est possible de définir très précisément la quantité d'informations souhaitée en retour. Et c'est ce que nous allons découvrir ici.



Sur l'exemple illustré par la capture, nous travaillons à partir d'une base de données des activités de sorties. Sur la droite, l'utilisateur choisit un département avec une liste déroulante. En-dessous, seules les dix premières activités de ce département sont extraites avec un niveau de détail limité sur le nom et la ville.

Classeur Excel à télécharger
Pour la démonstration de ces filtres maîtrisés, nous suggérons d'appuyer l'étude sur un classeur Excel hébergeant cette base de données des activités de sorties.

• Télécharger le classeur limiter-nombre-extractions.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous retrouvons le tableau des activités de sorties entre les colonnes B et E. Sur la droite, une liste déroulante permet de choisir un département en cellule G4. C'est en-dessous, entre les colonnes G et H que doit être produite l'extraction restreinte et maîtrisée.

Si vous déployez la zone Nom en haut à gauche de la feuille, vous remarquez que le tableau des activités de sorties est reconnu sous le nom tab. De même, le nom dep identifie la colonne des départements. Nous exploiterons ces noms pour simplifier la formule d'extraction filtrante.

Extraction filtrante
Nous devons exercer un filtre sur le département choisi par l'utilisateur avec la liste déroulante. Mais sur ces résultats filtrés, nous ne devons pas tout prendre. C'est la raison pour laquelle nous devons imbriquer la fonction Excel Filtre dans la fonction d'extraction Index. Ainsi, dans un raisonnement matriciel, nous pourrons définir les indices de ligne et de colonne à récupérer.

• Sélectionner la première cellule de la zone d'extraction en cliquant sur la case G7,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Puis, lui imbriquer la fonction pour filtrer les données, suivie d'une parenthèse, soit : Filtre(,
• Dès lors, désigner le tableau à filtrer par son nom, soit : tab,

Le critère du filtre
Maintenant et comme il est de coutume avec la fonction filtre, nous devons émettre un critère pour limiter les résultats au département choisi par l'utilisateur, avec la liste déroulante en cellule G4.

• Taper un point-virgule (;) pour passer dans l'argument du critère de la fonction Filtre,
• Désigner la colonne des départements par son nom, soit : dep,
• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Puis, désigner le département choisi par l'utilisateur en cliquant sur sa cellule G4,

Ainsi, nous n'entendons conserver que les activités de ce département, dans un premier temps.

• Taper un point-virgule (;) pour passer dans l'argument de l'exception,
• Taper alors le texte suivant entre guillemets : "Pas de résultat",

C'est ainsi que répondra la formule dans la première case, lorsqu'aucune activité ne correspondra à la demande.

• Maintenant, fermer la parenthèse de la fonction Filtre,

C'est ainsi que nous sommes de retour dans les bornes de la fonction Index.

Limiter le nombre de lignes
Sur cette première restriction, c'est une seconde qui doit intervenir maintenant grâce à la fonction d'extraction Index. Dans l'argument suivant et dans ce raisonnement matriciel, nous devons énumérer les indices de ligne que nous souhaitons extraire. Il s'agit des dix premiers en l'occurrence ici.

• Taper un point-virgule (;) pour passer dans l'argument de la ligne de la fonction Index,

Vous l'avez compris, dans ce raisonnement matriciel, il ne s'agit pas d'une seule ligne mais d'une matrice de lignes.

• Entre accolades, construire la matrice suivante : {1;2;3;4;5;6;7;8;9;10},

Dans la syntaxe, ce sont les points-virgules qui définissent une matrice verticale pour représenter les dix premières lignes concluantes à extraire.

Limiter et choisir les colonnes
C'est maintenant que nous souhaitons choisir et limiter les informations à extraire en colonnes. Il s'agit du nom et de la ville, donc de la première et de la quatrième colonnes du tableau source. Dans la syntaxe, à la place du point-virgule, c'est le symbole du point qui permet de matérialiser une matrice horizontale.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de la fonction Index,

Comme précédemment, en guise d'indice unique, c'est une matrice horizontale cette fois que nous allons lui passer dans ce raisonnement matriciel.

• Créer la matrice horizontale suivante : {1.4},

C'est ainsi que nous désignons la première et la quatrième colonnes du tableau pour n'extraire sur ces lignes limitées en nombre, que les informations sur le nom et la ville.

• Fermer la parenthèse de la fonction Index,
• Puis, valider la formule matricielle par la touche Entrée du clavier,

Comme vous pouvez l'apprécier, sur les dix premiers résultats, ce sont strictement les informations du département choisi qui sont extraites et ce, pour les colonnes souhaitées. Bien entendu, si vous changez de département, l'extraction doublement restrictive s'ajuste en parfaite cohérence. Et dans le même temps, vous notez l'apparition d'une mise en forme conditionnelle prédéfinie qui repère en couleur les lignes concordantes dans le tableau source.

L'astuce dans l'astuce
Si vous avez suivi mes suggestions de formations sur les calculs matriciels, vous l'aurez sans doute anticipé, il existe une méthode plus simple pour définir ces matrices, qui plus est, aux bornes potentiellement dynamiques. Par exemple, pour la matrice verticale, la matrice suivante en remplacement de la matrice statique que nous avons définie, fait parfaitement l'affaire : LIGNE(1:10). Pour une syntaxe finale considérablement allégée, à plus forte raison que ces matrices sont longues.

=INDEX(FILTRE(tab; dep=G4; "Pas de résultat"); LIGNE(1:10); {1.4})