XL 2010 Compter les valeurs distinctes d'une colonne

[Olivier67000]

Bonjour à tous,
Je fais appel à vous car je tourne en rond : je cherche à calculer le nombre de valeur distinctes d'une colonne : le tableau contient une colonne contenant des num clients, d'autres colonnes des dates et une dernière colonne des numéros de commande
J'ai pu calculer le nombre de commande distincte en utilisant la fonction sommeprod mais je souhaiterai pouvoir filtrer le tableau et que mon résultat soit recalculer dynamiquement....
Vu l'heure je suis pas certain d’être claire.... (même apres relecture).... Me suis permis d'ajouter un fichier exemple.
Un grand merci à toute âme charitable qui pourrait m'aider...
Excellente soirée à toi

 

[job75]

Bonsoir Olivize67000, bienvenue sur XLD,

Voyez le fichier .xlsm joint et cette fonction VBA :

Function NbUnique(r As Range) As Long
Dim d As Object
Set d = CreateObject("Scripting.Dictionary")
For Each r In r
    If r <> "" And Not r.Rows.Hidden Then d(r.Value) = ""
Next
NbUnique = d.Count
End Function

Le code doit être placé impérativement dans un module standard.

Formule en A1 =NbUnique(Tableau1[Num_commande])

Le tableau est un tableau structuré.

Bonne nuit.

 

[Olivier67000]

Bonjour et un énorme merci
Je suis resté buté sur les formules.....))))
Après avoir essayé votre fichier en retour cela fonctionne comme souhaité ))))
Je l'intègre à mon fichier.....
Encore merci
Excellente journée

 

[soan]

Bonjour job75, Olivier,

dans ton code VBA, je vois cette ligne : For Each r In r

ça me paraît très bizarre que le r de Each soit comme le r de In r ! c'est normal ? peut-être que ça a un sens caché du genre .Value = .Value ? qui fait un copier / coller valeurs à partir des formules (je sais que tu le sais, mais je l'ajoute pour les autres lecteurs de ce post qui peut-être ne le savent pas).​

soan

 

[job75]

Bonsoir soan,

Il faut savoir que la ligne For Each r In r n'est exécutée qu'une fois.

On peut donc utiliser la même variable plutôt que d'en créer une autre.

Je fais toujours ça quand c'est possible.

A+

 

[soan]

Bonsoir job75,

ah ? ok ; mais si c'est exécuté qu'une seule fois,
alors pourquoi mettre une boucle For ?

car dans ce cas, j'aurais remplacé ça :

For Each r In r
  If r <> "" And Not r.Rows.Hidden Then d(r.Value) = ""
Next

par ça :

  If r <> "" And Not r.Rows.Hidden Then d(r.Value) = ""

y'a plus de boucle For, et on fait
le travail qu'une seule fois.

soan

 

[job75]

Tu n'as pas compris.

Exécute une macro avec une boucle For en pas à pas et tu verras que la ligne du For est exécutée une fois.

 

[soan]

ok, je vais essayer.

 

[soan]

@job75

ah non, que ce soit en pas à pas ou non, ça me fait 10 exécutions ! j'en suis sûr car ça m'affiche bien les nombres de 1 à 10 ; mon code VBA est celui-ci :​

Option Explicit

Sub Essai()
  Dim r As Range, i%
  Set r = Range("A1:A10")
  For Each r In r
    i = i + 1: MsgBox i
  Next r
End Sub

même si y'a For Each r In r ça fait 10 exécutions car ça affiche 1 à 10 ; si c'était exécuté qu'une seule fois, ça afficherait 1 seulement, et pas 2 à 10 ! bien sûr, je sais que si ça exécute 10× c'est car y'a 10 cellules dans le Range r "A1:A10" ; c'est pour simplifier que j'ai fait une sub plutôt qu'une fonction.​

soan

 

[job75]

ah non, que ce soit en pas à pas ou non, ça me fait 10 exécutions !

Bah tu ne comprends toujours pas, c'est pourtant évident en pas à pas avec le surlignage.

Ce sont les lignes sous la ligne du For qui sont exécutées 10 fois.

La ligne du For, elle, n'est exécutée qu'une seule fois.

Bonne nuit.

 

[soan]

on s'est croisés : j'allais le dire :

edit : oh, attends, j'ai compris ! alors oui, effectivement : en pas à pas, on se rend bien compte que la ligne du For n'est exécutée qu'une seule fois, dans le sens où après le Next r, ça va sur la ligne juste au dessus et pas 2 lignes plus haut ; mais le corps de la boucle est quand même exécuté 10× dans mon exemple !

dans ton programme, le corps de la boucle de For Each r in r est exécuté combien de fois ? si c'est qu'une seule fois, la boucle devient inutile !

voici une boucle classique : For Each cel In Plage ; cel est une variable qui prend les valeurs de Plage ; mais avec For Each r In R le r de gauche n'est pas un sous-élément du r de droite ! c'est surtout ça que je ne comprends pas !​

soan

 

[job75]

si c'est qu'une seule fois, la boucle devient inutile !​

Tu dois être fatigué ce soir.

On travaille sur un tableau, pour l'étudier il faut une boucle, un point c'est tout.

 

[soan]

oui, tu as raison : c'est vrai qu'je fatigue ; j'vais aller dormir ! bonne nuit !

 

[soan]

Bonjour job75, le fil,

hier j'étais très fatigué, et j'ai mal expliqué c'qui m'paraît bizarre ; alors je reformule :​

1) voici une boucle For .. Each classique :

Sub Essai()
  Dim Ws As Worksheet
  For Each Ws In Worksheets
    'corps de la boucle
  Next Ws
End Sub

Ws est 1 feuille, et c'est un sous-élément de Worksheets,
qui est l'ensemble des feuilles ; ça j'comprends.

2) pour ta boucle qui m'semble bizarre :

je remets d'abord ici ton code VBA :

Function NbUnique(r As Range) As Long
Dim d As Object
Set d = CreateObject("Scripting.Dictionary")
For Each r In r
    If r <> "" And Not r.Rows.Hidden Then d(r.Value) = ""
Next
NbUnique = d.Count
End Function

r est donc un Range passé comme argument à la fonction NbUnique() ; mais plus bas :

For Each r In r : r n'est pas un sous-élément d'un ensemble plus grand puis que c'est aussi r ! donc pour moi, c'est aussi bizarre que si, par rapport à mon 1) tu avais mis For Each Ws In Ws : pour chaque feuille dans une feuille ! c'est ça que j'comprends pas !​

soan

 

[job75]

Bonjour soan,

Avec For Each r in r les r sont tous deux des objets Range, déclarés une seule fois.

pour moi, c'est aussi bizarre que si, par rapport à mon 1) tu avais mis For Each Ws In Ws

On ne peut pas écrire ça car le 1er Ws est un objet Worksheet et le 2ème une collection Worksheets.

A+