Compter le nombre de données différentes après un filtre

Bonjour,

J'ai un tableau avec 3 colonnes : Prénom (A), Ville (B), Passion (C). J'ai réussi, grâce à une formule SommeProd à filtrer les personnes habitant Paris et ayant pour passion la musique

=SOMMEPROD((B2:B9="Paris")*(C2:C9="Musique"))

Ca marche aussi avec un nb.Si.ens

=NB.SI.ENS(B2:B9;"Paris";C2:C9;"Musique")

Une fois ces filtres appliqués, je veux compter combien d’occurrences différentes se retrouve dans la colonne A et j'ai pu trouver :

=SOMMEPROD(1/NB.SI(A2:A5;A2:A5))

Maintenant, j'aimerai combiner, concatener, assembler ces deux formules ! Pour qu'il me dise : "Bon quand tu as fait tes filtres, il te reste x personnes en colonne A"

J'ai voulu les accoler et là, nombre décimal ! Quand il est arrondi, il est faux ! J'ai chercher tout l'après-midi ! Comment faire ?

Si quelqu'un connait la réponse, je le remercie d'avance et... infiniment !

Stéphane

Bonsoir,

suite aux filtres il suffit de regarder dans la barre d'état à gauche :

@ bientôt

LouReeD

Bonjour Stephane17620

Tu peux essayer

=SOUS.TOTAL(3;C1) -1

avec

le -1 (moins 1) pour décompter la ligne d'entête et

le 3 (au début de la fonction) pour utiliser la fonction NBVAL qui compte le NomBre de VALeurs

Attention ton tableau doit être "libre" je veux dire rien d'autre que le tableau dans les colonnes de celui-ci.

Actuellement tes commentaires (cellules fusionnées) en dessous perturbe le calcul !

stephane17620 a écrit :

"Bon quand tu as fait tes filtres, il te reste x personnes en colonne A"

="Bon quand tu as fait tes filtres, il te reste  "&TEXTE(SOUS.TOTAL(3;A2:A10);"# ##0")&" personnes en colonne A"

Bonjour à tous,

en tout il y a 4 nom,

matricielle,

=SOMMEPROD(1/NB.SI(A2:A9;A2:A9))

et il a 3 nom dans le filtre

=SOMMEPROD((B2:B9="Paris")*(C2:C9="Musique"))

alors le 1er moins le 2èm donne 1 nom restant

matricielle

=SOMMEPROD(1/NB.SI(A2:A9;A2:A9))-SOMMEPROD((B2:B9="Paris")*(C2:C9="Musique"))

Bonsoir,

Je ne comprends pas bien ta problématique ! Il n'y a pas de doublon complet sur les 3 colonnes, donc il n'y a pas lieu de vouloir dénombrer les valeurs différentes en A après application de critères sur les deux autres colonnes, elles seront différentes et d'ailleurs tu as bien le même résultat !

La question ne peut se poser que si tu n'appliques qu'un critère, laissant des doublons sur la colonne A...

Dans un tel cas, la formule classique est inopérante (NB.SI n'accepte que des plages comme matrices...)

Cela conduit en général à des calculs intermédiaires sur d'autres colonnes... ou bien à créer une fonction personnalisée.

Je répugne un peu à en proposer une, car comme dit au départ, dans ton exemple il n'y en a pas d'application réelle ! Il faudrait donc soit préciser ce que tu cherches à faire, soit fournir des exemples ou le calcul du nombre de valeurs distinctes aura un résultat différent de celui du dénombrement brut selon critères, quite le cas échéant à étoffer un peu tes listes...

Cordialement.

(re)

@MFerrand

Je pense qu'il s'agit juste d'un exercice... histoire de voir comment on peut faire dans la théorie... cependant je reste d'accord avec toi... !

Mais j'étais content de sortir la fonction sous.total() qui ne revient pas souvent

Bé ! Ce qui m'a interrogé, c'est qu'il parle de filtre mais ne fait pas de filtrage !

Auquel cas SOUS.TOTAL est la meilleure réponse...

@MFerrand

Bonjour à tous et merci pour vos réponses. Effectivement ce tableau est un exemple, dans le vrai tableau, après le filtrage, ma colonne A est remplie de doublons et je dois rapporter dans une cellule le nombre d'occurrences différentes de cette colonne par une formule, occurrences restantes après le filtrage.

Je vais déjà essayé toutes vos propositions.

Merci encore de vous être penchez sur mon problème !

Bonjour,

J'aurais bien aimé un exemple probant... !

Quoi qu'il en soit, tu pourras toujours aussi tester ça :

Function NBDIFFSI(plgNb As Range, plgCr1 As Range, cr1, Optional plgCr2 As Range, _
 Optional cr2, Optional plgCr3 As Range, Optional cr3)
    Dim d As Object, i&, c%, pl(), cr(), crok As Boolean
    ReDim pl(1): ReDim cr(1)
    Set pl(1) = plgCr1
    cr(1) = cr1
    If Not plgCr2 Is Nothing Then
        ReDim Preserve pl(2): ReDim Preserve cr(2)
        Set pl(2) = plgCr2
        cr(2) = cr2
        If Not plgCr3 Is Nothing Then
            ReDim Preserve pl(3): ReDim Preserve cr(3)
            Set pl(3) = plgCr3
            cr(3) = cr3
        End If
    End If
    Set d = CreateObject("Scripting.Dictionary")
    For i = 1 To plgNb.Cells.Count
        crok = True
        For c = 1 To UBound(pl)
            If pl(c).Cells(i) <> cr(c) Then crok = False: Exit For
        Next c
        If crok Then d(plgNb.Cells(i).Value) = ""
    Next i
    NBDIFFSI = d.Count
End Function

Je n'ai pas pris le temps d'affiner outre mesure... Elle permet de prendre en compte 3 critères. Les différentes plages doivent en principe être égales : la fonction se base sur la dimension de la plage de valeurs à dénombrer, si les plages critères sont plus grandes, le surplus sera ignoré, si elles sont plus petites, cela provoquera une erreur. VBA étant par défaut sensible à la casse (à l'inverse d'Excel), les comparaisons identifieront "Musique" et "musique" comme deux valeurs différentes...

Utilisation dans l'exemple :

=NBDIFFSI(A2:A9;B2:B9;"Paris";C2:C9;"Musique")

Cordialement.

Bon, rien n'a fonctionné... snif

Là tu m'étonnes ! Qu'as donc tu fait ?

J'ai essayé de cumuler mais je trouve ça quand même incroyable qu'on ne puisse pas compter le nombre de valeurs différentes avec un critère !

Bonjour,

Il n'y a pas de problème particulier à dénombrer le nombre de valeurs différentes sur une plage ! Tu as vu que tu pouvais utiliser pour cela les fonctions natives d'Excel SOMMEPROD et NB.SI pour construire la formule. Sachant que cette formule comportant NB.SI au dénominateur ne supportera pas des cellules vides dans la plage, sous peine de renvoyer #DIV/0!

Mais lorsque tu veux opérer ce dénombrement, assorti d'un critère excluant préalablement certaines valeurs, les choses se corsent ! Parce que tu ne dénombre plus sur la plage mais seulement sur une partie de celle-ci... Dans un tel cas on s'essaie à contruire une matricielle qui prendra en compte une matrice de cellules de la plage répondant à un ou des critères, et excluant les autres. Si beaucoup de fonctions natives acceptent ce traitement, ce n'est pas le cas de NB.SI qui réclame que la matrice soit une plage de cellules !

Tu n'es cependant pas dépourvu de moyen dans un tel cas ! Tu extrais dans une colonne annexe les valeurs répondant aux conditions, valeur "" pour les valeurs n'y répondant pas. Ceci fait, tu peux faire le dénombrement des valeurs distinctes dans cette colonne, en ôtant 1 du résultat pour les valeurs "" (hors conditions).

Le passage par des calculs intermédiaires n'a rien d'une anomalie ! Il peut même être préférable à l'utilisation d'une matricielle complexe ramant à chaque recalcul, en effectuant plus rapidement les calculs à partir de fonctions plus simples...

Mais dès lors que tu souhaites pouvoir obtenir un résultat avec une seule formule (ou même l'obtenir avec une formule plus simple d'utilisation que si elle est construite avec les fonctions natives), tu disposes de VBA qui te permet de créer des fonctions personnalisées utilisables en feuille de calcul dans les mêmes conditions que les fonctions natives ! Il convient évidemment de se mettre en mesure de programmer une telle fonction répondant à un besoin particulier...

Il y a environ 450 fonctions (il s'en ajoute quelques unes à chaque nouvelle version), qui généralement permettent des calculs génériques que chacun peut se trouver en position de devoir utiliser, quelques unes concernent des calculs plus spécialisés, mais il serait malvenu de reprocher à Microsoft de ne pas avoir une fonction pour chaque calcul particulier imaginé par un utilisateur ! Il y a bien des carences que l'on peut imputer à Microsoft, anciennes et qui aurait pu être comblées depuis longtemps... mais je ne vois pas que ce soit le cas dans le domaine qui nous occupe ! Les outils existent pour parvenir au résultat recherché !

Cordialement.