Discussion :

[merlinus3000]

Bonjour à tous,

Est-ce qu'il est possible d'avoir avec la fonction EQUIV, quelque chose qui puisse me résoudre le cas suivant :

J'ai deux menus déroulants, qui représentent les années et les mois

Cellule MOIS (liste déroulante de 1 à 12)
Cellule ANNEE (liste déroulante de 2015 à 2020)

Et j'ai une colonne avec des dates différentes :

30/06/2016
31/10/2019
30/04/2019
30/11/2019
28/02/2019
30/04/2020
30/11/2020


Et je souhaite savoir dans quelle ligne il y a une date qui soit du mois et de l'année que je recherche, ensuite la suivante, etc.

Ou une autre fonction qui pourrait répondre à la demande initiale.

Merci !

[Philippe Tulliez]

Bonjour,
Plusieurs solutions possibles.

Dans ces réponses, nous partons de l'hypothèse que les cellules contenant les listes mois et année se nomment respectivement Month et Year et que les dates se trouvent dans la colonne F de la liste des données commençant en ligne 2 (la ligne 1 aant les étiquettes de colonnes)

1. Si tu souhaites mettre en évidence les lignes contenant en colonne F la date correspondant aux critères, la mise en forme conditionnelle suffit avec comme règle Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué
   Après avoir sélectionné la liste des données (par exemple A2:F201)
   La formule sera
   

   Code :

   Sélectionner tout - Visualiser dans une fenêtre à part

   =ET(MOIS($F2)=Month;ANNEE($F2)=Year)

2. Si tu souhaites savoir combien de lignes contiennent les dates correspondant aux critères
   

   Code :

   Sélectionner tout - Visualiser dans une fenêtre à part

   =SOMMEPROD( (MOIS($F$2:$F$201)=Month) * (ANNEE($F$2:$F$201)=Year)  * 1)

3. Quant à connaître les lignes contenant les dates répondant aux critères, il est possible d'utiliser soit une formule matricielle, soit la formule SOMMEPROD mais il faudra étendre ces formules sur plusieurs lignes, une formule ne renvoyant qu'une seule valeur dans une cellule.
   En supposant que la colonne F ne contient qu'une seule date répondant aux critères, voici un exemple de la formule SOMMEPROD combinée aux formules MOIS, ANNEE et LIGNE
   

   Code :

   Sélectionner tout - Visualiser dans une fenêtre à part

   =SOMMEPROD( (MOIS($F$2:$F$201)=Month) * (ANNEE($F$2:$F$201)=Year)  * LIGNE($F$2:$F$201))-1

   ou mieux encore en ajoutant la formule LIGNE(Plage) permettant ainsi de déplacer la liste sans perturber le résultat que renvoie la formule
   

   =SOMMEPROD( (MOIS($F$4:$F$203)=Month) * (ANNEE($F$4:$F$203)=Year) * LIGNE($F$4:$F$203))-LIGNE(A4:F203)+1

[merlinus3000]

Ca y est presque.

J'ai repris la formule.

Si j'ai une occurrence, il me sort la ligne où se trouve la date qui m'intéresse.

Mais si le mois et l'année reviennent, il en fait la somme.

Est-ce qu'il y aurait moyen d'avoir les lignes où apparaissent les différentes dates que j'ai filtré ?

Merci beaucoup pour l'aide apportée.

[Philippe Tulliez]

Bonjour,
Si tu souhaites filtrer les lignes dont les dates correspondent aux critères, tu as deux solutions.

1. Les filtres simples - Utilisation des filtres chronologiques dans la colonne contenant les dates
2. Les filtres avancés - Utiliser les critères calculés en plaçant dans la zone des critères pour les étiquettes de colonnes par exemple _fn_ et comme critère calculé, la formule ci-dessous pour obtenir les lignes ayant comme date en colonne F l'année 2008 et le mois 3
   

   Code :

   Sélectionner tout - Visualiser dans une fenêtre à part

   =ET(MOIS(F2)=3;ANNEE(F2)=2008)

   Si les filtres doivent être régulièrement utilisés il faudrait remplacer les constantes 2008 et 3 par des références absolues à des cellules contenant les valeurs Année et Mois (cellules nommée Year et Month)
   La formule
   

   Code :

   Sélectionner tout - Visualiser dans une fenêtre à part

   =ET(MOIS(F2)=Month;ANNEE(F2)=Year)

   L'utilisation des filtres avancés à l'avantage supplémentaire de permettre l'exportation des données.

A lire éventuellement Les filtres avancés ou élaborés dans Excel

[merlinus3000]

J'ai pensé à cette solution, mais j'ai un autre paramètre, a savoir le choix de la colonne où je dois aller chercher mes dates.

J'arrive à définir la colonne qui m'intéresse (kilométrage), reste à chercher les lignes.

Avec cela et un index equiv, je pense pouvoir alimenter les données que j'aurais pu croiser selon les 3 paramètres : mois, année et kilométrage.

[Philippe Tulliez]

Bonjour,
Je crois que je ne comprends plus ce que tu cherches exactement.
Est-ce filtrer les données suivant critères ?
Est-ce renvoyer le numéro de toutes les lignes répondant aux critères ?
Dans ce dernier cas, il n'est pas possible à ma connaissance d'avoir le résultat dans une seule cellule sans passer par du code VBA sauf peut-être avec une matricielle.

[merlinus3000]

Dans mon premier essai, c'est repérer les lignes. Ensuite si je fais glisser le code, qu'il me donne la ligne suivante qui m'intéresse (s'il y a plus d'une date correspondante).

Si j'ai le numéro de ligne, je pourrais faire une exportation (avec index equiv).

Si ça peut fonctionner en matriciel, je suis preneur aussi.

Merci !

[papouclo]

Bonjour,
Un classeur exemple serait peut-être le bienvenu pour avoir une idée de la structure de la feuille.
Le classeur joint donne quelques idées pour trouver les différentes occurrences de dates dans une colonne.
Le problème de la fonction EQUIV est qu'elle ne permet d'obtenir que la première occurrence de l'élément recherché. S'il y en a plusieurs,il vaut mieux remplacer la plage de recherche par une plage de même importance au moyen d'une formule matricielle du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= SI(plagedates = datecherchee; ligne(plagedates);"")

On peut alors chercher les différents éléments numériques de cette plage intermédiaire à l'aide de la fonction PETITE.VALEUR qui n'est pas sensible aux cellules vides.
On obtiendra la ligne de la n ième occurrence avec une formule matricielle telle que:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= PETITE.VALEUR( SI(plagedates = datecherchee;LIGNE(plagedates);"");n)

à valider par Ctrl + Maj + Entrée.
L'autre problème est que l'on se donne non pas une date précise mais une année, par exemple en C3, et un mois, disons en D3, d'où la nécessité, dans la partie de formule en SI donnant la plage intermédidaire, d'un test plus ou moins semblable à celui proposé par Philippe et,pour la ligne de la n ième occurrence, une formule matricielle pouvant ressembler à :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= PETITE.VALEUR(SI((ANNEE(plagedates)=$C3)*(MOIS(plagedates)=$D3);LIGNE(plagedates);"");n)

Ceci est mis en oeuvre dans la feuille sommeprod_petitevaleur du classeur joint.
Dans les exemples du premier message, toutes les dates sont des fins de mois. S'il s'agit d'une contrainte de la colonne de date, on peut alors,toujours pour l'année et le mois cherchés respectivement en C3 et D3,
se placer sur une cellule de la ligne 3 et définir le nom datecherchee par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=FIN.MOIS(DATE($C3;$D3;1);0)

Attention aux références mixtes dans cette définition !
Avec 2019 et 2 en C3 et D3, datecherchee correspondra, pour toute cellule de la ligne 3, à la date 28/02/2019. Mais, avec 2015 et 5 en C4 et D4, datecherchee correspondra, pour toute cellule de la ligne 4,
à la date 31/05/2015.
Le test se simplifie. Cette méthode est mise en oeuvre dans la feuille finmois_petitevaleur.
Le problème de ces deux feuilles est la nécessité d'utiliser des formules matricielles.
Une troisième méthode serait d'utiliser à nouveau EQUIV mais de décaler la plage d'un nombre suffisant de lignes pour éliminer les occurrences déjà trouvées lorsque l'on veut chercher l'occurrence suivante;
la fonction DECALER est toute trouvée pour ceci et cela évite les formules matricielles. Cette troisième méthode est mise en oeuvre dans la feuille finmois_décaler.
Cordialement
Claude

[merlinus3000]

Bonsoir,

J'ai mis en pièce-jointe un modèle de données, avec trois onglets : INPUT, DATA et OUTPUT.

Dans INPUT, j'ai les colonnes suivantes : NOM, EMAIL et TELEPHONE, ainsi que des dates à différents kilométrages.

Dans DATA, j'ai les données que je vais utiliser pour mes menus déroulants.

Dans OUTPUT, j'ai une partie supérieure, avec mes 3 paramètres, à savoir : MOIS, ANNEE et KILOMETRAGE.

En dessous, j'ai trois parties : NOM, EMAIL et TELEPHONE.

Donc selon le kilométrage, l'année et le mois, les informations se rempliront en se basant sur ce qu'il y a dans INPUT.

Pour revenir sur une remarque, le fait que les jours étaient proche de la fin du mois, c'était parce que je n'avais utilisé que la génération aléatoire d'un nombre sur les années et les mois, et pas les jours. J'ai corrigé cela dans le modèle que j'ai mis en pièce-jointe.

Merci pour l'aide que vous pourrez m'apporter.

[Philippe Tulliez]

Bonjour,
Proposition avec AdvancedFilter (méthode du filtre avancé)
Voir image des deux feuilles concernées pour que tout le monde puisse comprendre sans ouvrir le classeur
Plage nommées dans la feuille [Input] : dbSource (A1:M101) et dbSource_Label (La première ligne de la plage dbSource soit les étiquettes de colonnes)
Cellules nommées dans la feuille [Output]
km = A2, Month = B2, Year = C2 (Listes déroulantes)
La formule du critère calculé

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ET(MOIS(DECALER(INPUT!$A$1;LIGNE(INPUT!A2)-1;EQUIV(Km;dbSource_Label;0)-1))=Month;ANNEE(DECALER(INPUT!$A$1;LIGNE(INPUT!A2)-1;EQUIV(Km;dbSource_Label;0)-1))=Year)

Pour le filtre avancé, la zone data = Range("dbSource"), les zones des critères (en vert), d'exportation (en brun) se trouvent dans la feuille [Output]

La procédure VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Sub ExportByAdvancedFilter()
 Dim areaData As Range, areaExport As Range, areaCriteria As Range
 Set areaData = Range("dbSource")
 With ThisWorkbook.Worksheets("Output")
  Set areaCriteria = .Range("E1:E2")
  Set areaExport = .Range("A5:C5")
 End With
 areaData.AdvancedFilter xlFilterCopy, areaCriteria, areaExport
End Sub

Il reste a insérer un bouton ActiveX pour lancer la procédure ou la lancer avec la procédure événementielle Change