Calcul nombre de ligne différentes

Bonjour, je sollicite votre aide afin de trouver une fonction EXCEL permettant de calculer le nombre total de facture différente.

le problème est qu'un ps (professionnel de santé) peut utiliser le même numéro de facture qu'un autre PS.

De plus, si le numéro de facture est identique et que le numéro de lot est différent, il faudra comptabiliser 2 factures.

En gros, je cherche une fonction permettant de calculer le nombre de ligne différente sans doublon. voici un exemple de fichier

Merci d'avance pour votre aide

Bonjour,

Une tentative:

Ajout d'une colonne intermédiaire ou je concatene le N) de Facture et de Lot (Colonne E)

Puis calul du nombre de données différentes par

=

=SOMMEPROD(1/NB.SI(E2:E316;E2:E316))

Petit souci : Je trouve une de plus que toi...

@+

Bonsoir,

Problème simple, mais qui ne s'est pas révélé aussi simple qu'il y paraît... Mais j'aurai appris quelques petites choses !

D'abord, j'ai pensé que dans ton cas une fonction personnalisée simplifiait grandement les choses, j'ai donc commencé par là et la voilà :

Function NBFACT(fact As Range)
    Dim d As Object, k, i%
    Set d = CreateObject("Scripting.Dictionary")
    With fact
        For i = 1 To .Rows.Count
            k = Trim(.Cells(i, 1)) & Trim(.Cells(i, 2)) & Trim(.Cells(i, 3))
            d(k) = ""
        Next i
    End With
    If d.exists("") Then d.Remove ("")
    NBFACT = d.Count
End Function

Je la tape donc pour tester : =NBFACT(A2:C1000), j'obtiens 152 ! Et tu indiques : 151 !!

J'avais élargis la plage car introduit une élimination des vides dans le calcul (pour le cas où des lignes vides soient incluses).

Je ramène à : =NBFACT(A2:C316) pour vérifier cet aspect : 152 toujours !

Là je passe au comptage manuel ! J'arrive au bout et... : 150 !!! Pas plus avancé.

Je passe donc à une vérification par formule :

Je concatène les 3 cellules de la ligne en colonne D, puis j'applique la formule classique :

=SOMMEPROD(1/NB.SI(D2:D316;D2:D316))

Résultat : 142 !!!! Là, ça commence à me !

Je tape donc en F2 : =NB.SI($D$2:$D$316;D2) que je tire sur la colonne pour avoir un résultat par ligne. Je refais un calcul à partir de là en additionnant les inverses : toujours 142 qui confirme le calcul de NB.SI, je m'y attendais et j'embraie sur une vérification des doublons avec les valeurs de NB.SI.

Là surprise ! Bien que la concaténation soit une chaîne, de 21 caractères au plus, le fait que tous ces caractères soient des chiffres fait apparaître que NB.SI assure correctement les différentiations jusqu'au 15e caractère, si la différence intervient à partir du 16e caractère jusqu'à la fin, il ne différencie plus et considère comme doublons...

Il semble donc qu'il opére la comparaison en termes numériques (la limitant à 15 chiffres significatifs).

Je ne sais si en ajoutant une lettre dans la chaîne cela pourrait rétablir la situation ? Je vais tacher de trouver le temps de tester ça...

J'ai donc opéré dans ma vérification un décompte des éliminations à tort de NB.SI, dans un autre colonne noté les doublons...

Totalisé ces 2 colonnes. Et résultat SOMMEPROD : 142 + doublons éliminés à tort = 151 !

315 (total) - doublons notés = 151 !

Cela réglait un point, mais cela ne m'expliquait pas pourquoi ma fonction en comptait 152 !

J'ai donc parsemé une autre colonne avec une formule qui m'indiquait le résultat à trouver si on arrêtait le compte à la ligne où était la formule.

Et j'ai étendu la plage comptée par la fonction par blocs de lignes, jsuq'à ce qu'une différence apparaisse. Une fois cernée la zone, une extension ligne par ligne a permis d'arriver à la ligne exacte où la différence se produisait.

Au cas particulier, mais cela n'a pas une importance fondamentale, il s'agit de la ligne 272.

Groupe de 3 lignes considérées comme identiques par NB.SI, ce que j'avais entériné en notant les lignes 271 et 272 comme doublons de la ligne 270...

Mais en y regardant de plus près, si la ligne 271 était la même que la 270, la 272 différait de la 271 par les 17e et 18e chiffres (sur 21) et je l'avais donc considérée à tort comme doublon !

Rectification faite, le résultat s'établit donc à 152 ! Ouf !

Je ferais passer le classeur après l'avoir un peu remis en état, avec les principaux tests, et si possible après avoir testé la formule avec caractères alpha dans la chaîne.

Tu peux tester la fonction ci-dessus, elle fonctionne sur ton modèle.

Par contre, il est possible que tes 3 colonnes ne soient pas contiguës sur ton fichier réel : dans ce cas il vaudrait mieux traiter 3 plages d'une colonne à la place d'une plage de 3 colonne. Signale-le moi et j'adapterai la fonction en conséquence.

Cordialement.

bonjour

un essai

cordialement

sans vba ;formules matricielles a validation tridactyles

ficier pret a l'emploi

Bonsoir MFerrand, tout d'abord merci beaucoup pour ton ton aide.

Après vérification, il y a bien 152 factures différentes (140800012 et 160700007 ne sont pas des doublons car numéro de lot différent. ta formule marche très bien. Par contre, mes 3 colonnes ne sont pas contiguës dans mon fichier réels

Voilà d'abord la fonction réaménagée avec 3 arguments à lui passer, correspondant aux 3 plages colonnes.

Function NBFACT(fps As Range, fact As Range, flot As Range)
    Dim d As Object, k, i%
    Application.Volatile
    If fps.Columns.Count = 1 And fact.Columns.Count = 1 And flot.Columns.Count = 1 Then
    ElseIf fps.Rows.Count = fact.Rows.Count And fps.Rows.Count = flot.Rows.Count Then
    Else
        NBFACT = CVErr(xlErrNum): Exit Function
    End If
    Set d = CreateObject("Scripting.Dictionary")
    For i = 1 To fps.Rows.Count
        k = Trim(fps.Cells(i, 1)) & Trim(fact.Cells(i, 2)) & Trim(flot.Cells(i, 3))
        d(k) = ""
    Next i
    If d.exists("") Then d.Remove ("")
    NBFACT = d.Count
End Function

J'ai mis des noms en rapport avec tes 3 colonnes, cependant comme aucune différence de traitement selon la colonne, les interchanger est sans conséquence.

J'ai par contre ajouté une vérification que les plages ne comportent qu'une colonne et ont le même nombre de lignes

Si une différence est détectée, la fonction renverra l'erreur #NOMBRE!, ce qui devrait alerter sur la source de l'anomalie.

Elle se recalcule lors des recalculs : ce n'était peut-être pas nécessaire si l'utilisation en reste ponctuelle, mais dans ce cas tu mettras une apostrophe devant la commande Application.Volatile pour l'invalider (cela la transforme en commentaire).

J'ai nettoyé ton fichiers des divers calculs que j'y ai opéré en laissant les résultats des tests principaux. L'adjonction d'une concaténation en y incorporant une lettre devant montre que dans ce cas la formule SOMMEPROD avec NB.SI fonctionne bien et fournit le résultat. L'inconvénient est de devoir faire cette concaténation dans une colonne du fait que NB.SI n'accepte pas de matrice non constituée par une plage de cellule.

Je suis pour ma part partisan de la fonction perso (sinon je n'aurais pas pris cette voie ) déjà parce qu'elle évite de mobiliser un espace sur la feuille pour le calcul, on ne la tape que dans une cellule, et elle ne représente elle-même qu'un code réduit. Mais aussi parce que la méthode utilisée est extrêmement rapide, et très probablement plus rapide que SOMMEPROD.

Pour information, elle utilise l'outil Dictionary. On constitue une concaténation des éléments des 3 colonnes laquelle constitue une clé d'un élément de dictionnaire. On fait ça en boucle du début à la fin, sans se préoccuper si l'élément existe ou non, s'il existe il ne sera pas doublonné. Les doublons sont éliminés naturellement dans la foulée. A la fin on teste si un élément du dico a une clé vide (cas où une ou autant qu'on veut lignes vides ont été rencontrées) : si elle existe on supprime l'élément. Et on renvoie la réponse qui n'est autre que le nombre d'éléments du dictionnaire que l'on vient de constituer.

Cordialement.

j'ai un message d'erreur avec la nouvelle fonction personnalisée

J'aurais dû la tester avant !

        k = Trim(fps.Cells(i, 1)) & Trim(fact.Cells(i, 1)) & Trim(flot.Cells(i, 1))

Dans cette ligne, j'ai laissé 1, 2, 3 qui étaient les colonnes de la plage unique...

Avec 3 plages à une colonne, ça va plus : mettre 1 partout.

Moi ça me signalait une référence circulaire parce que 3 faisait pointer sur la colonne ou était la formule.

Même si tu mets des plage à une colonne, si tu indiques un numéro de colonne différent de 1, Excel ou VBA iront chercher en dehors de la plage. Intéressant pour l'adressage, mais pas dans d'autres cas !

Avec mes excuses !

Rebonjour,

avec mon fichier cela ne marche plus, je n'arrive pas à voir d'où vient l'erreur. es ce que c'est liée au format de la cellule qui est en texte?...

Une formule ne peut pas fonctionner dans une cellule en format texte.

Bonjour,

perso j'en compte 152

P.

ps: éviter le mot URGENT dans les sujets, voir la charte à ce sujet

ps2: il faut For i = 1 To fPS.Rows.Count dans le code de mferrand