Discussion :

[Kestion100]

Bonjour,
Je cherche à calculer le nombre d'occurrences uniques des valeurs dans une ou deux colonne avec nombre de lignes très importants.

J'ai trouvé les formules:

Code formule :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME(SI('MEF NAT MAT'!$A$2:$A$73787<>"";1/NB.SI('MEF NAT MAT'!$A$2:$A$73787;'MEF NAT MAT'!$A$2:$A$73787)))

Et:

Code formule :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME(SI('MEF NAT MAT'!$A$2:$A$73787<>"";1/NB.SI.ENS('MEF NAT MAT'!$A$2:$A$73787;'MEF NAT MAT'!$A$2:$A$73787;'MEF NAT MAT'!$D$2:$D$73787;'MEF NAT MAT'!$D$2:$D$73787)))

Mais comme vous l'avez remarqué, elles portent sur 73787 lignes et cela rend le calcul, au moins, fastidieux et, en tout cas, inutilisable.

Existe-t-il un moyen autre d'effectuer de tels calculs de façon utilisable.


MERCI BEAUCOUP

[Thautheme]

Bonjour Kestion, bonjour le forum,

Par formule je ne sais pas faire... Une proposition VBA avec le code ci-dessous :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Sub Macro1()
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim I As Long 'déclare la variable I (Incrément)
Dim TMP As Variant 'déclare la variable TMP (tableau TeMPoraire)
Dim R As Range 'déclare la variable R (Recherche)
Dim T As String 'déclare la variable T (Texte)
 
Set O = Worksheets("Feuil1") 'définit l'onglet O (à adapter a ton cas)
TV = O.Range("A1:A73787") 'définit le tableau des valeurs TV (à adapter à ton cas)
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
For I = 2 To 73787 'boucle sur toutes les lignes du tableau des valeurs TV (en partant de la seconde)
    D(TV(I, 1)) = "" 'alimente le dictionnaire D
Next I 'prochaine ligne de la boucle
TMP = D.keys 'récupère dans le tableau temporaire TMP, la liste des éléments du dictionnaire D sans doublon
For I = 0 To UBound(TMP) 'boucle sur tous les élément du tableau temporaire TMP
    If Application.WorksheetFunction.CountIf(O.Range("A1:A73787"), TMP(I)) = 1 Then 'condition : si l'élément est unique
        Set R = O.Columns(1).Find(TMP(I), , xlValues, xlWhole) 'définit la recherche R (recherche l'élément TMP(I) dans la colonne 1 (=A) de l'onglet O
        R.Interior.ColorIndex = 3 'colore en rouge l'intérieur de l'ocuurence trouvée
        T = IIf(T = "", R.Address, T & Chr(10) & R.Address) 'définit le texte T
    End If 'fin de la condition
Next I 'prochain élément de la boucle
MsgBox T 'affiche le texte T
End Sub

[Kestion100]

Merci mais je dois mal me débrouiller, la msgbox est vide.

Je chercher bien un nombre, le nombre de couples (An;Bn) uniques dans ces deux colonnes

[Kestion100]

Je me suis mal exprimé, j'ai, dans ma colonne A:A un certain nombre de valeurs qui se répètent, je voudrais connaître le nombre de valeur mais sans compter les répétitions.

Idem, en B:B.

Je voudrais aussi connaître le nombre de valeurs (An;Bn) sans compter les répétitions.

[Kestion100]

Je crois comprendre que ton code isole les valeurs qui seraient uniques dans une colonne comprenant des valeurs répétées et des valeurs uniques?

[Kestion100]

Peut-être que je peux récupérer le

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UBound(TMP)

???

Si ça fonctionne, je te demanderais comment faire sur 2 colonnes

[Thautheme]

Re,

Désolé pour le retard...

Je crois comprendre que ton code isole les valeurs qui seraient uniques dans une colonne comprenant des valeurs répétées et des valeurs uniques?

C'est exactement cela. On peut le faire sur deux colonnes en modifiant la plage du tableau des valeurs par exemple...

[Kestion100]

Ca fonctionne même si c'est un peu chronophage.
Je crains que sur 2 colonnes ce le soit 2x plus.....

[Thautheme]

Re,

J'ai fait un test rapide sur 73787 lignes avec un ALEA.ETRE.BORNES(1;10) et à peine 5 valeurs uniques, ça a été très rapide. C'est peut-être à cause des bornes 1 à 10 seulement.
Sinon, ça ne fait pas le café non plus...

[Kestion100]

J'ignore complètement la notion de:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALEA.ETRE.BORNES(1;10)

Peux-tu m'en dire plus.

Pour ce qui est de mon fichier, il s'agit de 5064 valeurs uniques répétées dans les 73787 lignes.....

[Kestion100]

Mon ordi devait peut-être travailler à autre chose en tâche de fond hier car ce matin, c'est plus rapide.

A noté que le traitement ralenti au fil des

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

I

, je le constate car j'ai ajouté un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.StatusBar = I

.

J'ai mis en commentaire tout ce qui est:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
For I = 0 To UBound(TMP) 'boucle sur tous les élément du tableau temporaire TMP
    If Application.WorksheetFunction.CountIf(O.Range("A1:A73787"), TMP(I)) = 1 Then 'condition : si l'élément est unique
        Set R = O.Columns(1).Find(TMP(I), , xlValues, xlWhole) 'définit la recherche R (recherche l'élément TMP(I) dans la colonne 1 (=A) de l'onglet O
        R.Interior.ColorIndex = 3 'colore en rouge l'intérieur de l'ocuurence trouvée
        T = IIf(T = "", R.Address, T & Chr(10) & R.Address) 'définit le texte T
    End If 'fin de la condition
Next I 'prochain élément de la boucle
MsgBox T 'affiche le texte T

Pour ne récupérer que le :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UBound(TMP)

Dans une cellule

[Kestion100]

Première tentative pour 2 colonnes:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Case Is = 8
 
Set O = Worksheets(2) 'définit l'onglet O (à adapter a ton cas)
TV = O.Range(A & "1" & ":" & A & FinTab, D & "1" & ":" & D & FinTab) '("A1:A73787") 'définit le tableau des valeurs TV (à adapter à ton cas)
'Range ("A:A, C:C, G:I").Select
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
For I = 2 To FinTab '87 'boucle sur toutes les lignes du tableau des valeurs TV (en partant de la seconde)
    D(TV(I, 1)) = "" 'alimente le dictionnaire D
    Application.StatusBar = I & " " & FinTab
Next I 'prochaine ligne de la boucle
TMP = D.keys 'récupère dans le tableau temporaire TMP, la liste des éléments du dictionnaire D sans doublon
 
Range("L8") = UBound(TMP) - 1
 
End Select

Erreur sur la ligne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

TV = O.Range(A & "1" & ":" & A & FinTab, D & "1" & ":" & D & FinTab) '("A1:A73787") 'définit le tableau des valeurs TV (à adapter à ton cas)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Erreur d'exécution '91':
Variable objet ou variable de bloc With non définie

[Kestion100]

En effet.....:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

TV = O.Range("A1:A" & FinTab, "D1:D" & FinTab)

[Thautheme]

Bonjour Kestion, bonjour le forum,

ELEA.ENTRE.BORNE(min; max) est une fonction qui renvoie des valeurs aléatoires allant de min à max. J'avais donc sélectionné 72787 cellules dans la colonne A et appliqué cette fonction. Puis, comme ces nombres aléatoires changent au moindre recalcul dans l'onglet, j'ai fait un copier/coller les valeurs. C'est comme ça que j'ai pu tester...
À mon avis le Application.StatusBar = I doit considérablement ralentir l'exécution du code.
Pour info chez moi avec le premier code que je t'ai proposé, 73787 cellules avec 5064 valeurs répétées et 26 valeurs uniques... moins de 23 secondes pour finir le code...

Pour deux colonnes disjointes le coderais comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub Macro1()
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim I As Long 'déclare la variable I (Incrément)
Dim J As Byte 'déclare la variable J (Incrément)
Dim Fintab As Integer 'déclare la variable FinTab
Dim COL As Byte 'déclare la variable COL (COLonne)
Dim TMP As Variant 'déclare la variable TMP (tableau TeMPoraire)
 
Set O = Worksheets(2) 'définit l'onglet O (à adapter a ton cas)
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
Fintab = O.Cells(Application.Rows.Count, "A").End(xlUp).Row 'définit la dernière ligne éditée DL de la colonne A de l'onglet O
COL = 1 'initialise la colonne COL
For J = 1 To 2 'boucle 1 : sur deux éléments (colonnes)
    TV = O.Range(O.Cells(1, COL), O.Cells(Fintab, COL)) 'définit le tableau des valeurs TV (uniquement la colonne COL)
    For I = 2 To Fintab 'boucle 2 : sur toutes les lignes I du tableau des valeurs TV
        D(TV(I, 1)) = "" 'alimente le dictionnaire D
        If I = Fintab Then COL = 4 'si I est égale à FinTab, redéfinit la colonne COL
    Next I 'prochaine ligne de la boucle 2
Next J 'prochaine élément de la boucle 1
TMP = D.keys 'récupère les éléments du dictionnaire D sans doublons
End Sub

[Kestion100]

En effet, maintenant que tu me le dis, la premier test que j'ai fait après un simple copier coller de ton code a été très rapide, mais voyant apparaître la msgbox vide, j'ai voulu le relancer et là, grosse lenteur, du coup, StatusBar pour vérifier qu'il n'y avait pas de plantage complet et pour suivre l'exécution du code.....

[Thautheme]

Re,

Pour ce qui est de mon fichier, il s'agit de 5064 valeurs uniques répétées dans les 73787 lignes.....

Attention ! le message n'affiche que les valeurs qui ne se répètent pas ! Parce que j'avoue que ta notion de valeur unique répété reste très floue pour moi...
Est-ce que tu veux afficher ou lister toutes les valeurs différentes (5064) ou, parmi ces 5064 valeurs, n'afficher que celles qui ne se répètent pas ?...

[Kestion100]

En fait, j'ai 2 colonnes:

Carotte	Pommes de terre
Carotte	Poireaux
Carotte	Saucisses
Courgette	Lardons
Courgette	Poireaux
Courgette	Andouilles
...

Je cherche le nombre de valeurs mais sans répétition:

Carotte

Courgette

...

Pommes de terre

Poireaux

...

Et le nombre de couples uniques

[Kestion100]

J'ai testé ton code sur un autre onglet sur lequel la formule peut fonctionner puisque je n'y ai que 5000 lignes et il est OK apparemment.

D'après ton code, sur mes 73000 lignes, j'ai 5000 valeurs uniques:

Carotte

Pommes de terre

Saucisse

...

Qui avec leurs répétitions font 73000 lignes

[Kestion100]

A priori, toutes les valeurs se répètent

[Kestion100]

Après vérification, en ne gardant qu'une trentaine de ligne sur un onglet test, je trouve exactement ce qui est attendu