Filtre sur dates

Version imprimable

Filtre sur dates

Bonjour à tous,

J'ai trois cas de figure pour extraire une durée avec JourDépart et JourFin, limité par le 01/01/2012 comme borne inférieure et par le 31/12/2012 comme borne supérieure :

1) Un étendu qui commence le mois passé et prend fin le mois en cours.

Exp : du 25/11/2012 au 24/12/2012

Alors : JourDépart = 01/01/2012, JourFin = 24/12/2012

Durée = 24 j dans le mois de décembre.

2) Un étendu qui commence le mois en cours et prend fin le mois même.

Exp : du 02/12/2012 au 17/12/2012

Alors : JourDépart = 02/12/2012, JourFin = 17/12/2012

Durée = 16 j dans le mois de décembre.

3) Un étendu qui commence le mois en cours et prend fin le mois suivant.

Exp : du 28/12/2012 au 17/12/2012

Alors : JourDépart = 28/12/2012, JourFin = 31/12/2012
Durée = 4 j dans le mois de décembre.

En résumé, je ne définirais les extrémités de ma durée, depuis les étendus de dates, qu'avec les jours communs du mois de décembre et ceux des jours des étendus (les intersections des jours).

J’aimerais ajouter une condition dans le critère du champ 3 pour m’extraire les dates en colonne C qui ont le mois égal ou inférieur au mois en cours (<=12), et dans le critère du champ 4 pour m’extraire les dates en colonne D qui ont le mois égal ou supérieur au mois en cours (>=12).

Dans la colonne C, j’aurais des dates avec le mois de novembre (??/11/2012) et décembre (??/12/2012).

Dans la colonne D, j’aurais des dates avec le mois de décembre (??/12/2012) et janvier 2013 (??/01/2013).

Mais avec ce code :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 Sub Filtre() Dim NbLg As Long Dim Plg As Range If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData NbLg = Range("A2").End(xlDown).Row Set Plg = Range("A1:E" & NbLg) With Plg .AutoFilter field:=3, Criteria1:=">=" & CSng(CDate("01/12/2012")) .AutoFilter field:=4, Criteria1:="<=" & CSng(CDate("31/12/2012")) End With End Sub
Le filtre est toujours nul :(

Alors, comment résoudre cette problématique ?

Merci d’avance.

04/12/2012, 13h57
Philippe Tulliez

Bonjour,
A la lecture du code donc sans avoir testé, il semblerait qu'il manque la fonction AND
04/12/2012, 23h14
apt

1 pièce(s) jointe(s)

Bonjour Philippe,

Le AND pour les deux colonnes ?

et on ajoutera quoi comme condition après ?

Bonsoir,

En PJ, un exemple avec le résultat voulu.

Le filtre automatique est appliqué en deux fois sur deux colonnes C ensuite D, pour permettre d'extraire les dates de chaque colonne est de remplir un deuxième tableau qui me servira dans un autre traitement.

Seulement j’ai du mal à avoir mon 2ème tableau !

J’espère que l’exemple sera plus explicite :mrgreen:

Merci de votre aide.

1 pièce(s) jointe(s)

Bonjour,
Je te suggère l'utilisation des filtres avancés appelé aussi élaborés.
La zone Data contenant au moins les deux colonnes dates y compris leurs étiquettes nommée db et commencant en A1
En E1 la date (1er jour du mois)
La zone des critères (exemple H1:H2 et nommé dbCriteria
En H1, étiquette Formule, en H2, la formule ci-dessous

Code:

=ET(($B4>=E$1)*($A4<=FIN.MOIS(E$1;0)) )

Le code VBA à placer dans l'objet de la feuille où se trouve la zone à filtrer.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Option Explicit Function Filter(lst As Range, criteria As Range) lst.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=criteria ', Unique:=False End Function Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$E$1" Then Debug.Print Filter(Range("db"), Range("dbCriteria")) End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$E$1" And ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End Sub

06/12/2012, 16h30
apt

1 pièce(s) jointe(s)

Bonjour Philippe,

En essayant d'adapter ton code, sur mes données, je n'ai pas réussi à avoir le troisième tableau en bas dans l'exemple en pièce jointe ?

Merci.
06/12/2012, 21h36
Philippe Tulliez

Bonsoir,
Dans la zone de critères je vois déjà un problème dans la formule se trouvant en C2

Code:

=ET(($B2>=G$1)*($A2<=FIN.MOIS(G$1;0)) )

La zone data couvre la plage A2:F10. Les étiquettes Date Début & Fin sont en $C$2:$D$2 et la première ligne de données est en ligne 3 donc la formule devrait faire référence à C2 et D2
La formule pour la date devrait être

Code:

=ET($D3>=H$1;$C3<=FIN.MOIS(H$1;0))

De plus dans le code tu vérifies si la cellule E1 est modifiée comme je l'avais initialement prévue mais comme tu as déplacé la liste de choix à G2 il faut changer l'adresse Target aussi.
Comme il y a deux choix, Date et Type je préfère pour ma part une autre approche.
Dès que j'aurai l'occasion je mettrai un autre classeur avec ma suggestion.
En résumé quand il y a une formule comme critère, la formule doit renvoyer VRAI ou FAUX et doit faire référence à la première ligne data de la liste des données, ici ligne 3 (Etiquettes en ligne 2).
07/12/2012, 10h48
apt

Bonjour Philippe,

Merci pour la correction.

Voila une autre autre formule qui marche, seulement lorsque le mois est janvier, ça me pose problème :

Code:

=OU(MOIS($C3)<=MOIS(G$1);MOIS($D3)>=MOIS(G$1))
08/12/2012, 10h57
Philippe Tulliez

1 pièce(s) jointe(s)

Bonjour,

Cette formule ne peut pas te donner satisfaction.
La formule OU renvoie VRAI si au moins une des conditions est à VRAI
Si l'on a une date de décembre en G1, la formule renverra VRAI pour toutes les dates testées.

Bonjour,
Voici le classeur exemple promis.
Ce filtre est basé sur le filtre avancé d'Excel.
Le filtre est déclenché dès le choix d'un des critères se trouvant dans dans la plage pnChoice ou en cliquant sur le bouton bascule nommé tgleFilter
Une indication du nombre de lignes répondant aux critères spécifiés dans la zone des critères est présent en cellule D2 et est calculée à l'aide de la formule BDNBVAL qui fonctionne selon les mêmes exigences que le filtre avancé d'excel.
Une mise forme conditionnelle indique les cellules répondant aux critères
Le filtre avancé ou filtre élaboré d'excel fonctionne selon 2 arguments obligatoires qui sont la zone des données et la zone des critères.
Les formules Base de données d'excel fonctionnent avec le même type d'arguments avec en plus l'argument n° de colonne
Dans le fichier exemple la formule BDNBVAL renvoie le nombre de cellules remplies dans la colonne 1, répondant aux critères définis dans la plage pnCriteria

Code:

=SI(BDNBVAL(db;1;Criteres);BDNBVAL(db;1;Criteres) & " Donnée(s) répondent aux critères";"Pas de données répondant aux critères")

La formule qui permet à l'outil Filtre élaborés de filtrer la base de données
selon 3 critères est

Code:

=ET(D5>=B$2;C5<=FIN.MOIS(B$2;0);$C$2=F5)

Bonsoir Philippe, le fil,

Ca marche bien, merci.

Mais j'aimerais comprendre pourquoi avoir précéder l'appel de la fonction Filter() par Debug.Print ?

Code:

Debug.Print Filter(Range("db"), Range("dbCriteria"))

Dans le nouvel code en bas, j'aimerais remplacer les dates concernées par le changement (Celles de "Début" seront remplacées par le 01/12/2012 et celles de "Fin" par le 31/12/2012), pour ensuite les sauvegarder dans un tableau Tb().

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Private Sub tgleFilter_Click()
 
    With Me.tgleFilter
        .Caption = IIf(.Value, "Effacer filtre", "Filtrer")
        If .Value Then
            Debug.Print Filter(Range("db"), Range("dbCriteria"))
            'Changement des dates de Début et Fin concernées
            '???
 
            'Sauvegarde des colonnes A, C, D du filre dans le tableau Tb()
            Tb = Range("db").SpecialCells(xlCellTypeVisible)
 
           'Traitement du tableau Tb()
            TraitementTb
        Else
            If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
        End If
    End With
End Sub

Dans un module :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Option Explicit
Public Tb()
Sub TraitementTb()
    Dim i As Integer
    Dim JDép As Long, JFin As Long
   ' Si le tableau contient au moins une ligne de donnée
    If IsArray(Tb) And UBound(Tb) > 0 Then
        For i = 1 To UBound(Tb)
            'traitement
            JDép = Format(Tb(i, 2), "d") ' Colonne Début
            JFin = Format(Tb(i, 3), "d")    ' Colonne Fin
            MsgBox "JDép : " & JDép & vbCrLf & _
                   "JFin : " & JFin
        Next i
    End If
End Sub

Merci.

09/12/2012, 01h09
Philippe Tulliez

Bonsoir,

Citation:

Mais j'aimerais comprendre pourquoi avoir précéder l'appel de la fonction Filter() par Debug.Print ?

Ce n'est pas très catholique comme appel à la fonction.
J'aurais pu utiliser A = Filter()
En fait, la fonction devrait idéalement renvoyer une valeur mais je n'ai pas eu le temps de la terminer. Donc debug.print filter() est une façon d'invoquer la fonction comme msgbox Filter(), A=Filter() aurait tout aussi bien pu faire l'affaire.
Mais on peut aussi appeler une fonction comme une procédure.

Code:

Filter Range("db"), Range("dbCriteria")

Au lieu de

Code:

Debug.Print Filter(Range("db"), Range("dbCriteria"))
09/12/2012, 08h53
apt

Bonjour Philippe,

Merci pour l'explication :ccool:

En attendant les autres questions ;)
09/12/2012, 09h09
Philippe Tulliez

Bonjour,

Citation:

Envoyé par apt

Bonsoir Philippe, le fil,
Dans le nouvel code en bas, j'aimerais remplacer les dates concernées par le changement (Celles de "Début" seront remplacées par le 01/12/2012 et celles de "Fin" par le 31/12/2012), pour ensuite les sauvegarder dans un tableau Tb().

Dans cette discussion, qu'attends-tu exactement ?
Est-ce une table contenant le n° des lignes filtrées ou qui répondent aux critères du filtre ?
09/12/2012, 09h31
apt

Bonjour,

Dans ton exemple fournie, j'ai essayé le filtre pour le mois de décembre 2012.

Alors j'ai eu ce résultat :

Début Fin
03/11/12 02/12/12
20/11/12 19/12/12
28/12/12 18/01/13
25/11/12 09/12/12
02/12/12 17/12/12

Maintenant, pour un traitement de dates, spécifique au mois de décembre, j'aimerais ne retenir que les dates avec le mois "12" et remplacer les autres par le début ou la fin du mois en cours, à savoir modifier le résultat précédent par celui-ci :

Début Fin
01/12/12 02/12/12
01/12/12 19/12/12
28/12/12 31/12/12
01/12/12 09/12/12
02/12/12 17/12/12

- Remplacer le "Début" 03/11/12 par 01/12/12

- Remplacer le "Début" 20/11/12 par 01/12/12

- Remplacer la "Fin" 18/01/13 par 31/12/12

- Et ainsi de suite ....

Une autre question ?

Voila :P

1 pièce(s) jointe(s)

Bonjour,
Effectivement maintenant c'est clair
Je regarderai cela tout à l'heure sauf si d'ici là, un autre intervenant t'apporte la réponse.
Il me semble avoir déjà réalisé ce type de tableau avec une gestion de chantier.

Bonjour,
Dans cette version 4 du classeur, j'ai laissé la feuille nommée FiltrePeriode.
J'ai décomposé en trois feuilles l'ensemble des données parce-que c'est plus proche de ma façon de travailler.
J'utilise toujours également un minimum de VBA, privilégiant Excel dont j'utilise les cellules comme paramètres.
Je ne vois pas non plus l'utilité de créer du code VBA pour écrire dans les cellules d'Excel des formules que je peux écrire directement moi même dans ces cellules. De plus c'est bien plus simple pour la maintenance mais cela est un avis et un choix personnel.

J'ai utilisé les même couleurs que celles utilisées dans la première feuille (Bleu pour les Data, Orange pour les paramètres, vert pour les filtres)
La technique utilisée est également basée sur le filtre élaboré d'excel mais en utilisant l'exportation plutôt que le filtre sur place.
J'ai préféré le déclenchement par un bouton pour ne pas perturber l'utilisateur mais bien entendu on pourrait également utiliser en plus la procédure événementielle Worksheet_Change comme cela se fait dans la feuille FiltrePeriode
Une feuille contenant les données nommée db, une feuille paramètre nommée Param, une feuille de rapport/consultation nommée Filtre

Chaque plage porte un nom dbPerso, areaExport, areaCriteria
Les formules calculant le premier jour et le dernier jours sont placées dans les colonnes à droite de celles qui servent à l'exportation.
Les colonnes C et D de la feuille Filtre peuvent être masquées
La formule en colonne F

Code:

=SI(NBCAR(A5);MAX(C5;$I$3);"")

La formule en colonne G

Code:

=SI(NBCAR(A5);MIN(D5;FIN.MOIS($I$3;0));"")

Ces formules sont copiées sur cinq lignes.

Un message se trouvant en cellule F3 apparaît en rouge pour signaler qu'il faut copier les cellules sur plus de lignes si le nombre filtrés est plus grands que le nombre de lignes contenant les formules.
La formule en F3

Code:

=SI(BDNBVAL(dbPerso;1;areaCriteria)>NBVAL($F$4:$F$9);"Agrandir la zone de formules";"")

Code de la procédure événementielle qui se trouve dans l'objet de la feuille [Filtre]
Code:

1 2 3 Private Sub cbFilter_Click() mFilter.Export ' Appel de la procédure Export du module mFilter End Sub
Code des procédures présentent dans le module mFilter
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Option Explicit Public rngData As Range, rngExport As Range, rngCriteria As Range Public flagInit As Boolean Sub Init() With ThisWorkbook Set rngData = Range("dbPerso") Set rngExport = Range("areaExport") Set rngCriteria = Range("areaCriteria") End With flagInit = True End Sub Sub Export() If Not flagInit Then Init With rngData .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCriteria, CopyToRange:=rngExport End With End Sub
Il faut encore finaliser ce classeur pour placer les titres colonnes F & G, nommer les cellules I3 & J3 pour la clarté de lecture des formules.
Nommer la plage de cellules où l'on place les formules etc...
Nommer la plage prévue pour l'exportation afin de nettoyer le format pour la prochaine sélection etc...
Mais je pense que ce classeur donne suffisamment d'idées pour développer ce que tu cherches à faire.

Les plages de données des feuilles Param et db étant converties en tableau il est possible d'ajouter des données. Ces plages sont ainsi dynamiques.

Malgré le soin apporté à la conception de ce classeur Démo et les tests réalisés, il est possible qu'il y ait malgré tout un petit bug qui subsisterait

Bonsoir Philippe,

Merci pour les explications :cool:

J'ai tenté d'écrire les formules en VBA, parce qu'on ne connait pas la hauteur du filtre en lignes :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Dim Lg As Long Dim Tb() As Variant Lg = Cells(Rows.Count, 1).End(xlUp).Row Range("F5:G" & Lg).ClearContents '=SI(NBCAR(A5);MAX(C5;$I$3);"") [F5].Formula = "=IF(LEN(A5),MAX(C5,$I$3),"""")" '=SI(NBCAR(A5);MIN(D5;FIN.MOIS($I$3;0));"") [G5].Formula = "=IF(LEN(A5),MIN(D5,EOMONTH($I$3,0)),"""")" Range("F5:G5").AutoFill Destination:=Range("F5:G" & Lg), Type:=xlFillDefault 'Sauvegarde des colonnes A, C et D dans un tableau Tb() Tb = Union(Range("A5:A" & Lg), Range("C5:C" & Lg), Range("D5:D" & Lg))
Seulement, j'ai un problème quant à la sauvegarde des colonnes A, C et D dans le tableau Tb()

Je n'obtiens que la colonne A !

Pour le nommage des plages et critères, je vais me penchais dessus :D

Merci encore.

:)