XL 2019 Compter le nombre de "cellule non vide" par mois

[p4u02614]

Bonjour à toutes et à tous et merci de me lire.
J'ai dans les cellules A3:A310 des dates (au format : mardi 1 septembre 2020 jusqu'au mercredi 30 juin 2021)
J'ai dans les cellules B3:B310 une valeur alphanumérique (au format LEN010, LEN012, ANC016, ...)

J'aimerais faire une consolidation qui, pour chaque mois, affiche le nombre de cellules non vide et sans doublon :
Donc dans les cellules D3: D13 la liste des mois de Janvier à juin,
Et dans les cellules E3:E13 le nombre de cellules non vide en regard du nom du mois dans la colonne précédente.

Exemple :
Septembre 15
Octobre 13
Et ainsi de suite jusqu'au mois de décembre.

Par avance merci de votre aide.

 

[tbft]

Bonjour

Serait-il possible d'avoir un fichier exemple avec des données bidons, svp? mais qui reflète le soucis..
D'avance merci.
Cordialement.

 

[p4u02614]

Fichier exemple

 

[chris]

Bonjour

Une solution par PowerPivot et TCD

 

[p4u02614]

Bonjour

Une solution par PowerPivot et TCD

La solution que je cherche est une formule ! Mais merci beaucoup de vous être penché sur mon problème. Bonne soirée.

 

[laurent950]

Bonsoir,
Avec une Formule c'est complexe aussi.
Ps : Les dates doivent être écrite dans le bon format en D3:E12
Pas de Majuscule à Septembre Etc. (J'ai forcé avec Lcase pour passer la chaîne en Minuscule)

C'est Possible de passer par une fonction personnalisé avec une petite adoption, mais le principe est là.

Option Explicit
Sub test()
Dim FD As Worksheet
    Set FD = Worksheets(ActiveSheet.Name)
' Suppression des valeurs consolidation
    FD.Range("E3:E" & FD.Range("D1048576").End(xlUp).Row).ClearContents
Dim TbBis(1 To 3) As Variant
        TbBis(1) = FD.Range("A4:B" & FD.Range("A1048576").End(xlUp).Row).Value
    Set TbBis(2) = FD.Range("A4:B" & FD.Range("A1048576").End(xlUp).Row)
        TbBis(3) = FD.Range("D3:E" & FD.Range("D1048576").End(xlUp).Row).Value
Dim i As Integer, j As Integer
' Suppression de la surbrillance
        TbBis(2).Interior.Pattern = xlNone
' Indentification Doublon (Mois et Code établissement)
    For i = LBound(TbBis(1), 1) To UBound(TbBis(1), 1)
        For j = i + 1 To UBound(TbBis(1), 1)
            If TbBis(1)(i, 2) <> Empty And TbBis(1)(j, 2) <> Empty And Month(TbBis(1)(i, 1)) & TbBis(1)(i, 2) = Month(TbBis(1)(j, 1)) & TbBis(1)(j, 2) Then
                TbBis(1)(j, 2) = "d " & TbBis(1)(j, 2)
            End If
        Next j
    Next i
    For i = LBound(TbBis(1), 1) To UBound(TbBis(1), 1)
        If TbBis(1)(i, 2) Like "*" & "d" & "*" Then
            TbBis(2)(i, 2).Interior.Color = 65535
        End If
    Next i
' Consolidation (Nb Code établissement par mois sans doublon)
    For i = LBound(TbBis(3), 1) To UBound(TbBis(3), 1)
        For j = LBound(TbBis(1), 1) To UBound(TbBis(1), 1)
            If TbBis(1)(j, 2) <> Empty And LCase(TbBis(3)(i, 1)) = Format(TbBis(1)(j, 1), "mmmm") And Not TbBis(1)(j, 2) Like "*" & "d" & "*" Then
                TbBis(3)(i, 2) = TbBis(3)(i, 2) + 1
            End If
        Next j
    Next i
' RESTITUTION DU TABLEAU CONSOLIDATION AVEC LES VALEURS DANS LA FEUILLE
    FD.[E3].Resize(UBound(TbBis(3), 1)) = Application.Index(TbBis(3), , 2)
' Libére la mémoire
    Erase TbBis
    Set FD = Nothing
    i = Empty: j = Empty
End Sub

Cdt
laurent950

 

[JHA]

Bonjour à tous,

Avec une colonne supplémentaire "c" masquée pour la gestion des double.
Je te conseille de mettre 0 au lieu de "" dans la formule de la cellule "A3" , cela sera plus simple pour la gestion des formules autrement tu auras des erreurs.
Pour éviter les liens des formules en colonne "A", j'ai copié et collage spécial "valeur" de cette colonne du tableau.

JHA

 

[p4u02614]

Bonjour à Laurent 950 et JHA. Ainsi qu'a tous ceux qui me liront.
Vous avez chacun à votre manière chercher une solution (qui a dû vous prendre beaucoup de temps) et qui, par ma faute, n'est pas ce que je recherche. Je vous ai induit en erreur par l'intitulé erroné de mon titre (corrigé maintenant). Je vous présentent à tous les deux toutes mes excuses. Mais, j'ai pris le temps de regarder vos solution et j'ai appris plusieurs choses qui pourrait me servir à l'avenir !

Effectivement, ce que je recherche, ce sont le nombre de cellules NON VIDE pour chacun des mois de 'septembre' à Juin (année scolaire) afin de déterminer le nombre de visite effectuée mensuellement dans les établissements scolaires.

Cette fois-ci, et afin de ne pas vous faire perdre votre temps, Je vous transmet le classeur complet avec en fond jaune vif les deux problèmes que je n'arrive pas à solutionner (mais je pense que cela ne dois pas être aussi compliqué que les solutions que vous m'avez déjà soumises ) :
1) Dans la feuille 'Consolidation' Je n'arrive pas à utiliser la fonction SOMMEPROD avec le comptage des cellules non vides ! (Alors que dans les autres colonnes j'y suis arrivé !)
2) Dans la feuille 'Prestations', la date du premier septembre de l'année choisie dans la feuille 'Page de garde' s'affiche par rapport au jour de la semaine. Exemple : Si c'est un lundi elle s'affiche en cellule A3, si c'est un dimanche elle s'affiche dans la cellule A9. Et cela fonctionne très bien. Sauf quelle refuse de s'afficher dans le format de toutes les autres dates. Ce n'est pas grave mais je ne comprend pas ce mystère !!!

 

[chris]

Re

Une règle quand on utilise des tableaux structurés : ne pas garder le nom automatique type Tableau1 mais, de même qu'on ne garde ni Classeur1, ni Feuil1, leur donner un nom signifiant, idem pour leurs colonnes...

2 solutions

• formules (dont matricielles, lourdes)
• PowerQuery (consolidation de droite, à actualiser par Données, Actualiser Tout)

2) Dans la feuille 'Prestations', la date du premier septembre de l'année choisie dans la feuille 'Page de garde' s'affiche par rapport au jour de la semaine. Exemple : Si c'est un lundi elle s'affiche en cellule A3, si c'est un dimanche elle s'affiche dans la cellule A9. Et cela fonctionne très bien. Sauf quelle refuse de s'afficher dans le format de toutes les autres dates. Ce n'est pas grave mais je ne comprend pas ce mystère !!!

la fonction CONCATENER renvoie du TEXTE et non une date.
voir ma correction dans Prestations

Reste une ambiguïté : tu voulais "sans doublons" mais tes établissements sont en doublon...

 

[JHA]

Bonjour à tous,

A essayer

JHA

 

[job75]

Bonjour à tous,

Voyez le fichier joint et les formules en C3 et E3 sur le même principe qu'en D3

Pour qu'elles donnent un résultat correct il a fallu transformer en nombre la date en Prestations!A4.

A+

 

[p4u02614]

Re

Une règle quand on utilise des tableaux structurés : ne pas garder le nom automatique type Tableau1 mais, de même qu'on ne garde ni Classeur1, ni Feuil1, leur donner un nom signifiant, idem pour leurs colonnes...

2 solutions

• formules (dont matricielles, lourdes)
• PowerQuery (consolidation de droite, à actualiser par Données, Actualiser Tout)

la fonction CONCATENER renvoie du TEXTE et non une date.
voir ma correction dans Prestations

Reste une ambiguïté : tu voulais "sans doublons" mais tes établissements sont en doublon...

Pour la Feuille 'Consolidation' C'est parfait ! j'étais pas loin de la solution pour le nombre désétablissement visité, mais, de toute façon, je n'avais pas penser à un calcul matriciel pour les Périodes réalisées. Merci beaucoup. C'est génial.

En ce qui concerne de problème de format des dates 7 premières cellules (a3 à a10) cela ne fonctionne que pour l'année 2020-2021. Si on change (sur la feuille 'Page de garde' par exemple en 2019-2020 dont le 01/09/2019 tombe un jeudi... plus rien ne fonctionne !

Mais Honnêtement je suis déjà tellement heureux avec la consolidation et de comprendre mes erreur encore mille fois "merci".

 

[p4u02614]

Bonjour à tous,

A essayer

JHA

Magnifique JHA. Tu as trouvé. Bravo et mille merci à toi aussi. Je peux dire que les problèmes sont résolument résolut. Bravo à tous le monde.

 

[chris]

RE

Une autre proposition pour la correction des formules sur Page de garde qui donne de vraies dates et respecte une autre règle des tableaux : une seule formule par colonne.

J'ai également corrigé les 7 premières lignes de Prestations mais ta structure en semaine ne permet pas de respecter la règle de la formule unique sans remanier le tableau
J'ai aussi corrigé les formules de Consolidation qui n'utilisent plus DATEVAL puisque ce sont des dates et plus du texte

Comme de toute évidence tu n'es pas intéressé par la solution POwerQuery, je l'ai retirée du classeur

 

[p4u02614]

RE

Une autre proposition pour la correction des formules sur Page de garde qui donne de vraies dates et respecte une autre règle des tableaux : une seule formule par colonne.

J'ai également corrigé les 7 premières lignes de Prestations mais ta structure en semaine ne permet pas de respecter la règle de la formule unique sans remanier le tableau
J'ai aussi corrigé les formules de Consolidation qui n'utilisent plus DATEVAL puisque ce sont des dates et plus du texte

Comme de toute évidence tu n'es pas intéressé par la solution POwerQuery, je l'ai retirée du classeur

J'ai regardé avec attention tes modifications et si je change dans la feuille "Page de garde" l'année scolaire plus aucune date ne s'affiche. Alors il est vrai que tes formules nouvelles formules sont plus "propre" mais j'ai gardé ta première proposition qui fonctionne parfaitement et j'ai même ajouter un tableau de consolidation pour l'année scolaire en cours avec "Le codes des établissements triées et sans doublons", "Le nom des établissements" ainsi que "Le nombre de fois que l'établissement à été visité". Mont travail ce termine ici.

Je te remercie encore de toutes la sollicitude dont tu ma entouré afin que je puisse réaliser mon projet.
Bon dimanche à toi Chris.