Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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

p4u02614

XLDnaute Nouveau
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.
 
Dernière édition:
Trier par date Trier par votes

laurent950

XLDnaute Barbatruc
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à.

VB: 
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
 
Dernière édition:
Vote positif 0 Vote négatif
J

JHA

XLDnaute Barbatruc
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
 

Pièces jointes

  • Classeur1 (20).xlsx
    26.4 KB · Affichages: 7
Vote positif 0 Vote négatif

p4u02614

XLDnaute Nouveau
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 !!!
 

Pièces jointes

  • TEST.xlsx
    91.1 KB · Affichages: 11
Vote positif 0 Vote négatif

chris

XLDnaute Barbatruc
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...
 

Pièces jointes

  • Cosolid.xlsx
    106.6 KB · Affichages: 9
Dernière édition:
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
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+
 

Pièces jointes

  • TEST(1).xlsx
    91.2 KB · Affichages: 5
Vote positif 0 Vote négatif

p4u02614

XLDnaute Nouveau

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".
 
Vote positif 0 Vote négatif

chris

XLDnaute Barbatruc
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
 

Pièces jointes

  • Consolid2.xlsx
    94.3 KB · Affichages: 6
Vote positif 0 Vote négatif

p4u02614

XLDnaute Nouveau

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.
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

S
  • Question
Microsoft 365 A partir de la 1ere cellule non vide, récupérer le mois
Réponses
4
Affichages
152
Sissou71
S
Y
  • Question
XL 2010 compter nombre des labels de format texte non vide
Réponses
2
Affichages
530
yahya belbachir
Y
O
  • Résolu(e)
Microsoft 365 Effacer le contenu de cellules si une autre cellule est vide
Réponses
9
Affichages
221
Olihoes
O
N
  • Question
Microsoft 365 Tableau calcul entre deux dates
Réponses
1
Affichages
172
job75
N
  • Résolu(e)
XL 2019 Joindre des données de plusieurs feuilles
Réponses
4
Affichages
561
Nasseuz
N
S
  • Question
Microsoft 365 Référer à la première cellule non vide en haut
Réponses
3
Affichages
433
ShrekL
S
C
  • Résolu(e)
XL 2021 planning
Réponses
14
Affichages
1 K
Chavanie21
C
D
  • Question
XL 2021 Calcul des âges
2
Réponses
16
Affichages
1 K
DomL
D
C
  • Résolu(e)
XL 2016 Calculer le nombre de jours effectués par mois
Réponses
8
Affichages
550
CathU
C
M
  • Question
XL 2010 Compter le nombre de mois en fonction de plusieur critéres
Réponses
2
Affichages
443
Marylgb
M
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…