Filtrer les dernières données selon critère 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 :


Dernières valeurs selon critère

Nous allons découvrir ici qu'il est tout à fait possible de filtrer un tableau Excel en ne prélevant que les N dernières valeurs satisfaisant un critère.



Sur l'exemple illustré par la capture, nous travaillons sur un tableau de relevés des températures. Sur la droite et avec une seule formule, nous parvenons à extraire les 5 derniers jours durant lesquels la température n'a pas excédé les trente degrés.

Classeur Excel à télécharger
Pour débuter, nous suggérons d'appuyer l'étude sur ce tableau de relevés des températures.

• Télécharger le classeur dernieres-valeurs-critere.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 découvrons le relevé des températures entre les colonnes B et C. L'extraction des cinq derniers relevés inférieurs à trente degrés doit être produite entre les colonnes E et F.

Extraction filtrante
Pour commencer, nous devons amorcer une extraction uniquement sur les lignes pour lesquelles la température ne dépasse pas la condition imposée sur les trente degrés. Pour restreindre le tableau d'extraction sur ces enregistrements spécifiques, nous devons imbriquer la fonction Filtre dans la fonction Index. C'est en effet la fonction Filtre qui permet d'isoler les lignes concordantes pour restituer un tableau d'extraction filtré à la fonction Index.

• Sélectionner la première cellule de la zone d'extraction en cliquant sur sa case E4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction classique d'extraction suivie d'une parenthèse, soit : Index(,
• Puis, inscrire la fonction matricielle d'extraction, soit : Filtre(,

En effet, le premier argument de la fonction Index est le tableau de recherche. Grâce à la fonction Filtre, nous allons le limiter aux lignes pour lesquelles les températures descendent en-dessous des trente degrés.

• Désigner l'intégralité du tableau à filtrer en sélectionnant la plage de cellules B3:C33,
• Taper un point-virgule (;) pour passer dans l'argument du critère de la fonction Filtre,
• Sélectionner toutes les températures, soit la plage de cellules C3:C33,
• Dès lors, ajouter le critère suivant : <30,

Ne l'oublions pas en effet, la fonction Filtre est une fonction matricielle. Ce sont donc toutes les températures qui vont être confrontées tour à tour à ce critère pour n'extraire que les lignes concluantes et les passer à la fonction Index.

• Fermer la parenthèse de la fonction Filtre,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne de la fonction Index,

Les cinq dernières lignes
Dans ce raisonnement matriciel, en guise d'indices de ligne, c'est un groupe que nous allons transmettre à la fonction Index, plus précisément les cinq dernières lignes correspondant au critère. Pour cela, c'est la fonction Sequence, avec un pas inversé qui va nous permettre de les remonter les unes à la suite des autres.

• Inscrire la fonction pour créer des séries, suivie d'une parenthèse, soit : Sequence(,

En premier argument, nous devons indiquer le nombre de lignes sur lequel doit s'étendre la suite. Nous l'avons dit, nous souhaitons seulement conserver les cinq dernières lignes concluantes.

• Inscrire le chiffre 5 et taper un point-virgule (;),

Cette fois, nous sommes dans l'argument permettant de définir le nombre de colonnes à retourner. C'est un critère que nous sommes en train de construire sur la colonne des températures pour connaître les indices de ligne à renvoyer. Donc une seule colonne est concernée. C'est dans l'argument suivant de la fonction Index que nous définirons le nombre de colonnes à fournir à l'extraction.

• Inscrire le chiffre 1 suivi d'un point-virgule (;),

Nous atteignons ainsi l'argument consistant à définir l'indice de départ de la série. Dans notre cas, il s'agit du dernier pour les lignes concordantes, filtrées dans le premier argument de la fonction Index. Pour parvenir à nos fins, nous allons dégainer une astuce. Pour bien la comprendre, nous suggérons de nous appuyer sur l'assistant fonction.

• A gauche de la barre de formule, cliquer sur le bouton de l'assistant fonction (fx),
• Dans la boîte de dialogue qui suit, cliquer dans la zone intitulée Début,

Nous devons connaître le nombre de lignes répondant favorablement au critère de température pour être en mesure de débuter l'extraction à partir de la dernière.

• Inscrire la fonction Somme suivie d'une parenthèse, soit : Somme(,
• Dans l'enchaînement, ouvrir une nouvelle parenthèse,

En effet, nous allons construire une matrice conditionnelle que nous allons ensuite transformer.

• Désigner toutes les températures, soit de nouveau la plage de cellules C3:C33,
• Comme précédemment, ajouter le critère suivant : <30,
• Fermer alors la parenthèse de la matrice conditionnelle,
• Multiplier ce résultat par 1, soit : *1,

De cette façon, nous convertissons les résultats en matrice de chiffres ou plutôt de position dans ce cas précis.

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

Comme vous pouvez le voir, des indications surgissent sur la droite de l'assistant fonction. En regard de la zone intitulée Début, un nombre indique que 17 lignes de ce tableau concordent. Vous pouvez facilement le vérifier en parcourant les températures inscrites sur la feuille. C'est ainsi que la fonction Sequence décide de démarrer la série à partir de la dix-septième position. C'est bien ce que nous voulions mais pas pour aller vers l'avant (17;18;19;20;21) mais pour revenir en arrière.

• Pour cela, cliquer tout d'abord dans la zone intitulée Pas,
• Puis, inscrire le pas négatif suivant : -1,

C'est ainsi que nous inversons la progression de la suite, comme en atteste la matrice de retour, livrée par l'assistant en bas à droite de la boîte de dialogue.

Colonnes en retour
Il ne nous reste plus qu'à terminer la formule en indiquant le nombre de colonnes souhaitées à l'extraction, avec le troisième paramètre de la fonction Index.

• Dans la barre de formule, cliquer à la toute fin après la dernière parenthèse fermante,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne,
• Dès lors, construire la matrice horizontale suivante : {1.2},
• Fermer la parenthèse de la fonction Index,
• Enfin, valider la formule par la touche Entrée du clavier,

Comme vous pouvez l'apprécier, nous obtenons bien les cinq derniers relevés dans l'ordre décroissant et pour lesquels la température n'a pas dépassé les trente degrés.

Trier la série dans l'ordre croissant
Sachez enfin, que nous pourrions très bien inverser l'ordre de la série résultante, pour préférer une organisation croissante sur les jours, pour ces cinq derniers relevés concordants. Pour cela, il suffit d'englober la fonction Sequence dans la fonction Trier.



=INDEX(FILTRE(B3:C33; C3:C33<30); TRIER(SEQUENCE(5; 1; SOMME((C3:C33<30)*1); -1)); {1.2})