Comment identifier la date la plus ancienne ou la plus récente en fonction de critères sur Excel ?

Dans ce tutoriel, je vais vous montrer comment identifier la date la plus ancienne (ou la plus récente) dans une base de données en fonction de critères spécifiques.

Cela permettra d’extraire facilement des informations pertinentes et utiles à partir de vos bases de données.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons utiliser le tableau suivant dans lequel dans lequel sont répertoriées les informations concernant la production de fruits d’une exploitation, avec les numéros de lot, les produits, les dates de lot et les quantités de production.

À partir de ce tableau, nous allons souhaiter extraire les informations concernant la production des bananes, des framboises et des pêches en fonction de critères spécifiques.

2. Obtenir la date la plus ancienne ou la plus récente avec un simple critère

Pour commencer en douceur nous allons souhaiter extraire la date de la toute première production de bananes. Il s’agit donc d’obtenir une information dans cette base en utilisant un unique critère de sélection : le nom du fruit.

Pour cela, nous allons utiliser deux fonctions Excel bien connues, qui sont les fonctions MIN() et SI().

De cette manière, la fonction SI() va permettre de construire une matrice de valeurs constituées des dates de la base de données, qui répondent à un critère donné. Cette matrice nous servira ensuite de critère à intégrer dans la fonction MIN() afin d’en récupérer la valeur la plus petite.

En effet, cette fonction SI() permet de réaliser des calculs conditionnels, c’est-à-dire qu’elle va effectuer une action si une condition est vraie et une autre action si la condition est fausse.

Pour que les choses soient plus claires, nous saisissons alors la formule suivante dans la cellule G10 :

 =SI(B9:B76=G8;C9:C76) 

Ici, nous allons donc récupérer une matrice reprenant la liste des dates correspondantes aux lots de production de bananes.

Étant donné que nous n’avons pas spécifier de valeur à retourner lorsque le résultat est « FAUX », c’est-à-dire pour les autres fruits, Excel insérera des valeurs « FAUX » pour ces derniers.

Pour visionner le résultat correspondant après avoir saisi la formule, nous sélectionnons à nouveau la cellule G10, puis nous appuyons sur la touche [F2] pour nous placer dans la barre des formules et ensuite nous appuyons sur la touche [F9] pour qu’Excel affiche le résultat de la fonction directement à la place de celle-ci :

Comme vous pouvez alors le constater, le résultat est effectivement une matrice de valeurs saisies entre crochets constitués de FAUX et de valeurs numériques.

Maintenant, il suffit d’encapsuler ce résultat en tant qu’argument de la fonction MIN(), qui va nous retourner la valeur minimale de cette matrice, en ignorant les « FAUX » :

 =MIN(SI(B9:B76=G8;C9:C76)) 

Pour étendre cette formule sur les cellules situées à droite, et obtenir les données concernant les productions de framboises et de pêches, nous allons devoir modifier les types de références utilisées.

Pour cela, nous pouvons utiliser la touche [F4] du clavier pour passer en références absolues, à l’exception de la référence à la cellule G8 qui doit être libre sur les colonnes :

 =MIN(SI($B$9:$B$76=G$8;$C$9:$C$76)) 

Puis nous pouvons étendre la formule sur les autres colonnes :

Pour obtenir la date de la dernière production, nous pouvons reprendre exactement la même formule, en modifiant simplement la fonction MIN() par MAX() :

 =MAX(SI($B$9:$B$76=G$8;$C$9:$C$76)) 

3. Obtenir la date la plus ancienne ou la plus récente avec deux critères

Maintenant, voyons comment extraire la date la plus ancienne de cette base en utilisant deux critères de sélection : le nom du fruit et le poids de la production, lequel devant être supérieur à 500kg.

Pour cela, nous allons utiliser une deuxième fonction SI() pour ajouter ce deuxième critère.

La création de la matrice est donc possible avec la formule suivante qui permet d’obtenir la liste des dates correspondant aux lots de production de bananes dont le poids est supérieur à 500 kg :

 =SI($B$9:$B$76=G$8;SI($D$9:$D$76>500;$C$9:$C$76)) 

Ici, la première partie, « SI($B$9:$B$76=G$8 » vérifie si chaque cellule correspondante dans la colonne B est égale à la valeur de G8, c’est ce que nous venons de voir juste avant.

Lorsque c’est le cas et que cette condition est vraie (c'est-à-dire que le nom du fruit est « Bananes »), alors nous passons à la deuxième partie de la fonction SI(), « SI($D$9:$D$76>500;$C$9:$C$76)) », qui vérifie si le poids de chaque lot de production (colonne D) est supérieur à 500 kg.

Si les deux conditions sont vraies (c'est-à-dire que le fruit est « Bananes » et le poids est supérieur à 500 kg), alors la fonction SI() renvoie la date correspondante de la colonne C9:C76.

Si l'une ou les deux conditions sont fausses, la fonction SI() renvoie « FAUX ».

Ainsi, cette formule crée une matrice de dates qui répond simultanément aux deux critères : le fruit est « Bananes » et le poids de la production est supérieur à 500 kg.

Une fois que nous avons obtenu la matrice de dates basée sur les deux critères, nous utilisons la fonction MIN() pour extraire la plus petite date de cette matrice :

  =MIN(SI($B$9:$B$76=G$8;SI($D$9:$D$76>500;$C$9:$C$76))) 

Ainsi, en utilisant une fonction SI() imbriquée, nous pouvons appliquer des critères multiples pour extraire des informations précises de votre base de données Excel. Cette méthode peut être étendue pour effectuer des calculs conditionnels complexes avec de nombreux critères.

4. Filtrer sur une période donnée

Maintenant, nous pouvons utiliser les techniques vues dans la partie précédente afin d’adapter la formule pour tenir compte d’une période.

Pour en revenir sur cet exemple, nous allons maintenant souhaiter obtenir la date la plus ancienne pour la production sur un mois donné, ici le mois de juillet.

Ici l’astuce va être de reprendre la formule que nous avons élaborée juste avant, sauf qu’au lieu d’analyser le poids des productions, nous allons nous intéresser sur les valeurs de la colonne reprenant les dates de production, que nous allons encapsuler en tant qu’argument de la fonction MOIS().

Cette fonction MOIS() permet comme son l’indique d’extraire le mois à partir d’une date donnée.

En encapsulant ainsi une plage de cellules contenant des dates à l’intérieur de cette fonction, nous allons obtenir une liste de numéro de mois allant de 1 jusqu’à 12, sous la forme d’une matrice de données.

Il suffira ensuite de confronter ces mois avec le numéro du mois qui nous intéresse, ici le « 7 » :

 =MIN(SI($B$9:$B$76=G$8;SI(MOIS($C$9:$C$76)=7;$C$9:$C$76)))  

Et voici le résultat final de nos analyses :

5. Les fonctions MIN.SI.ENS() et MAX.SI.ENS()

Pour finir, sachez que sur les dernières versions d’Excel, des formules ont été introduites pour réaliser nativement ces opérations. Il s’agit des fonctions MIN.SI.ENS() et MAX.SI.ENS(). Ces fonctions ont été conçues pour simplifier considérablement le processus d'extraction de valeurs minimales ou maximales en fonction de critères spécifiques.

Si je n'en ai pas parlé jusqu'à maintenant, c'est que malheureusement, celles-ci ne sont pas disponibles dans toutes les versions plus anciennes d'Excel. Cependant, si vous disposez de la dernière version du logiciel, vous avez la possibilité d'utiliser ces fonctions pour accomplir ces tâches de manière plus efficace et conviviale.

Elles vous permettent de gagner du temps en évitant d'avoir à créer des formules complexes à l'aide de fonctions SI() imbriquées.

En effet, les fonctions MIN.SI.ENS() et MAX.SI.ENS() sont particulièrement utiles lorsque vous devez extraire des valeurs minimales ou maximales en fonction de plusieurs critères simultanés. Elles simplifient grandement le processus en vous permettant de spécifier les critères de manière plus claire et intuitive, ce qui rend l'analyse de données plus rapide et plus précise.