SOMMEPROD sans doublons avec critères

Y compris Power BI, Power Query et toute autre question en lien avec Excel
a
admdg
Jeune membre
Jeune membre
Messages : 21
Inscrit le : 9 septembre 2014
Version d'Excel : 2007

Message par admdg » 18 juin 2015, 11:25

Bonjour à tous,

Ça fait quelques jours que je bloque sur une formule, je vous explique:
Je reçois un tableau Excel de taille différente (je ne connais pas le nombre de lignes), dont j'ai besoin d'extraire le nombre de "TPE" différents par semaine.

Parce que ce serait trop facile, la colonne où figure les numéros de TPE (type "TPE1501IBS00013", "TPE1501IBS00019" etc.) comprend d'autres références que je ne veux pas prendre en compte.

J'ai commencé par rédiger une formule avec SOMMEPROD qui compte le nombre de TPE dans les bornes de dates correspondantes avec les conditions de sommeprod et la fonction GAUCHE, mais ma formule ne fait que de me compter le nombre de TPE au total or il peut y en avoir une vingtaine avec la même référence, et je souhaiterai ne pas avoir de doublon dans mon résultat final...
=SOMMEPROD(((I:I>=B2)*1)*((I:I<=C2)*1)*((GAUCHE(K:K;3)="TPE")*1))
Colonne I : dates des références
Colonne B et C : bornes de mes semaines (du 01/01/15 au 07/01/15 par exemple)
Colonne K : références

Je vous mets mon fichier en pièce jointe ici:
https://mon-partage.fr/f/caGLloIc/
Modifié en dernier par admdg le 18 juin 2015, 13:56, modifié 1 fois.
a
admdg
Jeune membre
Jeune membre
Messages : 21
Inscrit le : 9 septembre 2014
Version d'Excel : 2007

Message par admdg » 18 juin 2015, 12:00

J'ai oublié de préciser qu'en rajoutant le critère
(1/NB.SI(K2:K1000;K2:K1000))
le code marche, mais comme précisé plus haut je ne connais pas le nombre de ligne ce qui fait planter le fichier lorsque l'ont veut utiliser K:K avec des cellules vides
g
gmb
Fanatique d'Excel
Fanatique d'Excel
Messages : 12'314
Appréciations reçues : 290
Inscrit le : 4 avril 2013
Version d'Excel : 2016

Message par gmb » 18 juin 2015, 17:13

Bonjour

Un essai à tester. Te convient-il ?
Bye !
Classeur1 v1.xls
(42.5 Kio) Téléchargé 70 fois
a
admdg
Jeune membre
Jeune membre
Messages : 21
Inscrit le : 9 septembre 2014
Version d'Excel : 2007

Message par admdg » 19 juin 2015, 08:27

Bonjour gmb merci de ta réponse,

J'ai téléchargé le fichier et ai essayé de l'ouvrir en xls et xlsm pour la macro mais j'appuie sur le bouton et rien ne se passe, est-ce qu'il faut que je fasse quelque chose d'autre ?
a
admdg
Jeune membre
Jeune membre
Messages : 21
Inscrit le : 9 septembre 2014
Version d'Excel : 2007

Message par admdg » 19 juin 2015, 09:00

Ah non enfaite c'est juste que je n'avais pas mis suffisamment de lignes pour voir les résultats ! :oops:
C'est exactement ça merci beaucoup !!!

Petite question histoire d'être sûr, comment est-ce que je pourrais m'y prendre pour rajouter une autre référence en plus de "TPE" par la suite ? Par exemple une référence "RET" comme ci-dessous
Sub NbreDeTPE()

    Set plage = Range("K2:K" & Range("K" & 65536).End(xlUp).Row)
    For ln = 2 To Range("B" & 65536).End(xlUp).Row
        Set dico = CreateObject("Scripting.dictionary")
        For Each c In plage
            If c.Offset(0, -2) >= Cells(ln, "B") And c.Offset(0, -2) <= Cells(ln, "C") _
                    And Left(c.Value, 3) = "TPE"   &"RET" Then
                dico(c.Value) = ""
            End If
        Next c
        Cells(ln, "D").Value = dico.Count
    Next ln
    
End Sub
g
gmb
Fanatique d'Excel
Fanatique d'Excel
Messages : 12'314
Appréciations reçues : 290
Inscrit le : 4 avril 2013
Version d'Excel : 2016

Message par gmb » 19 juin 2015, 11:36

Bonjour
admdg a écrit :comment est-ce que je pourrais m'y prendre pour rajouter une autre référence en plus de "TPE" par la suite ? ...comme ci-dessous
Pas tout à fait.
Si tu veux le nombre de TPE plus celui de RET, il te faut plutôt quelque chose comme ça :
Sub NbreDeTPE()

    Set plage = Range("K2:K" & Range("K" & 65536).End(xlUp).Row)
    For ln = 2 To Range("B" & 65536).End(xlUp).Row
        Set dico = CreateObject("Scripting.dictionary")
        For Each c In plage
            If c.Offset(0, -2) >= Cells(ln, "B") And c.Offset(0, -2) <= Cells(ln, "C") _
                    And [surligner](Left(c.Value, 3) = "TPE" Or Left(c.Value, 3) = "RET")[/surligner]Then
                dico(c.Value) = ""
            End If
        Next c
        Cells(ln, "D").Value = dico.Count
    Next ln
End Sub
Bye !
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message