Function valdif&(r As Range)
Dim oCell As Range, oColl As Scripting.Dictionary
Application.Volatile
Set oColl = CreateObject("Scripting.Dictionary")
For Each oCell In r.Cells
If IsNumeric(oCell) Then If oCell.Value <> 0 And Not oColl.Exists(CStr(oCell.Value)) Then oColl.Add CStr(oCell.Value), oCell.Value
Next
valdif = oColl.Count
End Function
=SOMME(SI(B1:B10<>0;(1/NB.SI(B1:B10;B1:B10))))
=SOMMEPROD((B2:B10<>0)/NB.SI(B2:B10;B2:B10))
=SOMME(N(FREQUENCE(SI(B2:B10<>0;B2:B10);B2:B10)>0))
C'est ce qui fait la difference...de fait, je supprime mon message #2...trouver le nombre de valeurs différentes
Function valdif2&(r As Range)
Dim oCell As Range, oColl As Scripting.Dictionary
Application.Volatile
Set oColl = CreateObject("Scripting.Dictionary")
For Each oCell In r.Cells
If IsNumeric(oCell) Then If oCell.Value <> 0 And Not oColl.Exists(CStr(0 + oCell.Value)) Then oColl.Add CStr(0 + oCell.Value), oCell.Value
Next
valdif2 = oColl.Count
End Function
=SOMME(N(FREQUENCE(SI(SI(ESTERREUR(SI(C2:C21<>0;SUBSTITUE(C2:C21;".";",")*1));0;SI(C2:C21<>0;SUBSTITUE(C2:C21;".";",")*1));EQUIV(SUBSTITUE(C2:C21;".";",")*1;SUBSTITUE(C2:C21;".";",")*1;0));LIGNE(INDIRECT("1:"&LIGNES(C2:C21))))>0))
=SOMME(N(FREQUENCE(SI(SI(ESTERREUR(SI(C2:C21<>0;SUBSTITUE(C2:C21;".";",")*1));0;SI(C2:C21<>0;SUBSTITUE(C2:C21;".";",")*1));EQUIV(SUBSTITUE(C2:C21;".";",")*1;SUBSTITUE(C2:C21;".";",")*1;0));LIGNE(C1:C21))>0))
=NB(1/(FREQUENCE(SI(SI(ESTERREUR(SI(C2:C21<>0;SUBSTITUE(C2:C21;".";",")*1));0;SI(C2:C21<>0;SUBSTITUE(C2:C21;".";",")*1));EQUIV(SUBSTITUE(C2:C21;".";",")*1;SUBSTITUE(C2:C21;".";",")*1;0));LIGNE(C1:C21))))
=valdif(SI((B3:B22="Bien")*1;L3:L22))