Autres Nombre de valeurs différentes (Sommeprod mais avec des "")

zebanx

XLDnaute Accro
Bonjour à tous,

Pourriez-vous SVP m'indiquer comment amender la formule suivante pour qu'elle fonctionne avec des cellules vides sur la plage ? (sinon on passera par un autre UDF...)

#SOMMEPROD(1/NB.SI(C$2:C$11;C$2:C$11))

Vous en remerciant par avance.

zebanx
 

Fichiers joints

JHA

XLDnaute Barbatruc
Bonjour à tous,

As-tu essayé:
VB:
=SOMMEPROD(1/NB.SI(C$2:C$11;"><"))
JHA
 

zebanx

XLDnaute Accro
Bonjour à tous,

As-tu essayé:
VB:
=SOMMEPROD(1/NB.SI(C$2:C$11;"><"))
JHA
Salut JHA, :)

Ca me donne le nombre total de valeurs mais sans tenir compte des valeurs distinctes.
(Je vais regarder côté UDF).

++
 

zebanx

XLDnaute Accro
Re-

Ce code parait très bien fonctionner.
Peut-être y a-t-il un moyen de contourner sur ce sommeprod bien particulier... c'est plus pour savoir désormais.

Bonne journée à toi et au forum.
zebanx

VB:
Public Function ValeursUniquesDansPlage(MaPlage As Range)
'par: https://excel-malin.com
    
    On Error GoTo ValeursUniquesDansPlageErreur
    'définition de variables
    Dim N As Long
    Dim Compteur As Long
    Dim Ligne As String
    Dim Valeur As String
    Dim NombreDeValeursUniques As Long
    Dim ValeursUniques() As String
    'valeurs par défaut
    N = 0
    Compteur = 0
    Ligne = ""
    Valeur = ""
    NombreDeValeursUniques = 0
    
    N = MaPlage.Count
    ReDim ValeursUniques(0 To N)
    'itération dans la Plage
    If (N > 0) Then
        For Compteur = 1 To N
            Valeur = CStr(MaPlage.Cells(Compteur).Value)
            If (Not (InStr(1, Ligne, Valeur, vbBinaryCompare) > 0)) Then
                Ligne = Ligne & ("[" & Valeur & "]")
                NombreDeValeursUniques = NombreDeValeursUniques + 1
                ValeursUniques(NombreDeValeursUniques) = Valeur
            End If
        Next Compteur
    End If
    'résultat final
    ValeursUniquesDansPlage = NombreDeValeursUniques
    Exit Function
ValeursUniquesDansPlageErreur:
    MsgBox "Une erreur s'est produite..."
    ValeursUniquesDansPlage = 0
End Function
 

JHA

XLDnaute Barbatruc
Bonjour à tous,

Peut-être celle-ci:
VB:
=SOMME(SI(FREQUENCE(SI(C$2:C$11<>"";EQUIV(C$2:C$11;C$2:C$11;0));LIGNE($A$2:$A$11)-LIGNE($A$1));1))
A valider par les 3 touches

JHA
 

R@chid

XLDnaute Barbatruc
Bonjour @ tous,
une autre matricielle un plus courte :
Code:
=NB(1/SI(C2:C11<>"";EQUIV(C2:C11;C2:C11;0)=LIGNE(C2:C11)-1))
@ valider toujours par Ctrl+Shift+Enter

Bonjour @zebanx , @JHA

Comme @JHA :) ou en plus court et toujours matricielle: SOMME(SI(C$2:$C11<>"";1/(NB.SI(C$2:C$11;C$2:C$11))))

A+
Attention mon ami si tu peux éviter cette formule ce serait mieux, puisqu'elle est gourmande en ressource en cas de plages des milliers de lignes, et prend un temps énorme pour donner le résultat qui est parfois avec des décimales.
Tu peux toutes fois faire un test avec des centaines de valeurs différentes sur une plage des cinquantaine de milliers de lignes pour voir.


Cordialement
 

zebanx

XLDnaute Accro
Bonjour à tous, le forum

Merci @rachid pour ce code et cette remarque.

Effectivement, sur un test de 30000 lignes ne comprenant que des formules, votre (ton) code en formule reste très rapide.
Comme l'UDF proposé par Mapomme.

Ma plage de controle s'étend sur beaucoup moins de lignes mais je suis une nouvelle fois très content de voir des propositions variées, recherchées et qui font toutes le travail demandé !

Merci à vous.
Bonne journée :cool:


@mapomme : Salut, ;)
Pourquoi avoir retiré ton post STP ??? Ca fonctionne très bien
 
Dernière édition:

Roblochon

XLDnaute Impliqué
Bonjour,

Oui, le nombre de lignes (et/ou colonnes) est souvent un problème avec les matricielles. Ce qui est bien ici, c'est que l'utilisateur aura l'embarras du choix.

@rachid je conserve la tienne dans mes petits papiers. Merci

zut! j'ai loupé la version de @mapomme

@zebanx encore une conversation pleine de richesses. Merci
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour à tous :)

Pas bien réveillé, j'ai cru avoir posté en double donc j'ai supprimé le doublon qui n'existait pas en fait o_O !
Donc je poste à nouveau la fonction NBdiff:
Code:
Function NBdiff&(xrg As Range)
Dim dic, s, xcell
  Set dic = CreateObject("scripting.dictionary")
  dic.CompareMode = TextCompare
  For Each xcell In xrg
    s = CStr(xcell)
    If s <> "" Then If Not dic.exists(s) Then dic.Add s, ""
  Next xcell
  NBdiff = dic.Count
End Function
 

zebanx

XLDnaute Accro
Re-
Merci à vous deux.

Même si l'UDF du #4 n'a plus aucune raison d'être utilisée avec l'UDF fournit par Mapomme, il faut quand même signaler que ce code génère des erreurs en utilisant Instr de cette manière (par exemple en mettant 6 et 66 dans une même plage, reconnu une fois).

Je pourrais l'amender avec des "," & (x) & "," comme un récent sujet traité par... Mapomme :D mais ni le temps disponible ni la longueur du code ne m'invite à le faire. Mais il fallait le signaler et retenir, c'est là le point essentiel, une vigilance bien particulière à l'utilisation de Instr() en VBA qui vient souvent "taper" un nombre trop importants d'occurences dans son utilisation habituelle comme évoqué supra.

Et prévoir*, quand on fournit un exemple, de prévoir une plage un peu plus large qui regroupe plus de "cas" car ce point m'avait échappé avant de faire le test sur plusieurs milliers de lignes évoqué par Rachid.

zebanx

----
* j'allais marqué "pour ma pomme" (formule usuelle) qui aurait été pour le coup contre-indiqué :D
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Re @zebanx :) , à toutes et tous,

Juste pour le fun, j'ai tenté de voir comment se comportaient les temps de calcul de la formule la plus rapide et efficace (celle de @R@chid que je salue amicalement :) ;)) par rapport à ceux de la fonction de ma pomme.

Ce ne sont pas tant les valeurs en seconde que le comportement qui est intéressant (voir graphique dans le fichier) :
  • on a fait une estimation du temps de calcul par la formule matricielle diminué du temps de calcul par la fonction
  • pour les petites valeurs de nombres de ligne, la formule est plus rapide
  • ensuite on trouve une zone où la fonction est juste un peu plus rapide que la formule (grosso modo, les durées sont du même ordre)
  • mais à partir d'environ 20 000 lignes, il a une "cassure" et le delta des temps de calcul s'envole
Par conséquent, sur mon PC, si on a moins de 20 000 lignes, la formule est préférable car c'est plus simple, au delà mieux vaut utiliser la fonction.

c'est tout... :rolleyes:
 

Fichiers joints

Dernière édition:

Haut Bas