compter les occurences distinctes

Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11

Entraide Applications Microsoft Office Excel compter les occurences distinctes - SOMMEPROD

3 réponses

Mat

06/12/2006 à 09:29

bonjour =E0 tous,

* y-a-t'il un grand sorcier qui pourrait m'expliquer comment fonctionne
cette formule "magique" ?
l'aide d'Excel sur SOMMEPROD reste cantonn=E9e =E0 la multiplication des
valeurs des deux matrices, puis =E0 l'addition de ces produits ... et
ici je ne vois pas plusieurs matrices en r=E9f=E9rence dans la formule ?

=3DSOMMEPROD((nom<>"")/(NB.SI(nom;nom)+(nom=3D"")))

* plus compliqu=E9 : pour chaque ligne j'aurai besoin de la liste des
=E9l=E9ments distincts relatifs =E0 la personne, comment faire pour
obtenir le r=E9sultat ?
=3DSOMMEPROD(((type<>"")/(NB.SI(type;type)+(type=3D"")))*(nom=3DA1)) ne
marche pas ...

NOM TYPE NB DISCTINCTS PAR PERSONNE
dupont =E9l=E9ment 1 -> 3
dupont =E9l=E9ment 2 -> 3
dupont =E9l=E9ment 1 -> 3
roger =E9l=E9ment 1 -> 1
dupont =E9l=E9ment 5 -> 3
dupont =E9l=E9ment 2 -> 3
roger =E9l=E9ment 1 -> 1

merci,
Mat

3 réponses

AV

06/12/2006 à 10:45

.....qui pourrait m'expliquer comment fonctionne
cette formule "magique" ?

Tu l'auras voulu.....

Préambule : pour trouver les résultats intermédiaires, sélectionner la
partie de la formule concernée dans la barre de formule et appuyer sur F9
Soit la plage (A1:A7) nommée "Nom" et comprenant les valeurs suivantes
dupont;dupont;dupont;roger;dupont;dupont;roger
Utilisation de SOMMEPROD..
Question de base : Dénombrer les occurrences uniques d'une plage ne
comprenant pas de cellules vides
Une solution : =SOMMEPROD(1/NB.SI(Nom;Nom))
Explications....
La partie NB.SI(Nom;Nom) renvoie la matrice de constantes suivante :
{5;5;5;2;5;5;2}
qui représente le nbre de fois où chacun des éléments de la plage est
représenté
L'astuce de la formule consiste à diviser 1 par chacun des éléments de
cette matrice
Résultat : {0.2;0.2;0.2;0.5;0.2;0.2;0.5}
Ce qui correspond à "la fraction de représentativité" de chacun des
éléments de la plage
Reste donc à utiliser SOMMEPROD pour faire la somme de ces "fractions" et
obtenir 2 qui représente le nbre d'occurrences uniques (dupont;roger)

Là où l'affaire se complique c'est lorsque la plage "Nom" comprend une ou
des cellules vides
Exemple avec les valeurs suivantes en A1:A7 (A3 est vide) :
dupont;dupont;"vide";roger;dupont;dupont;roger
la partie NB.SI(Nom;Nom) va renvoyer la matrice {4;4;0;2;4;4;2}
L'opération 1/{4;4;0;2;4;4;2} va renvoyer une valeur d'erreur lorsqu'il va
falloir diviser 1 par 0 !
Résultat : {0.25;0.25;#DIV/0!;0.5;0.25;0.25;0.5}
SOMMEPROD ne sachant pas sommer une matrice contenant des valeurs
d'erreurs >> Plouf !

L'intérêt de la formulation
=SOMMEPROD((nom<>"")/(NB.SI(nom;nom)+(nom="")))
est de supprimer ce problème
D'abord la première partie (Nom<>"") qui est un test logique, renvoie la
matrice {VRAI;VRAI;FAUX;VRAI;VRAI;VRAI;VRAI}
Rappel : VRAI équivaut à 1 FAUX équivaut à 0 donc cette matrice équivaut à
: {1;1;0;1;1;1;1}
Ce qui présente l'intérêt de renvoyer une matrice dont on va pouvoir
diviser chacun des éléments...
Si l'on se contentait de la formulation :
=SOMMEPROD((Nom<>"")/(NB.SI(Nom;Nom)))
Résultat : =SOMMEPROD({1;1;0;1;1;1;1}/{4;4;0;2;4;4;2})
la présence de cellules vides entraînerait la même erreur (division par 0)
L'astuce va consister à faire en sorte qu'il n'y ai plus de 1 divisé par 0
mais 0 divisé par 1, opération qui, elle, ne renvoie pas d'erreur mais 0, valeur
qui n'aura aucune incidence dans la somme faite, in fine, par SOMMEPROD !
Explication :
Si l'on prend la partie NB.SI(nom;nom)+(nom=""))
elle renvoie : {4;4;0;2;4;4;2}+{0;0;1;0;0;0;0}
la partie test logique (nom="") renvoie la matrice
{FAUX;FAUX;VRAI;FAUX;FAUX;FAUX;FAUX}
autrement dit : {0;0;1;0;0;0;0}
L'addition des 2 matrices {4;4;0;2;4;4;2}+{0;0;1;0;0;0;0}) a donc pour
résultat :
{4;4;1;2;4;4;2}
On voit là que l'élément contenant la cellule vide n'est plus égal à 0
mais à 1 !
Résumé :
=SOMMEPROD((nom<>"")/(NB.SI(nom;nom)+(nom="")))
renvoie comme résultat intermédiaire :
=SOMMEPROD({1;1;0;1;1;1;1}/{4;4;1;2;4;4;2})
On revoit que la division des 3° éléments de chacune des matrices( 0/1) ne
renverra que 0 et non plus une erreur
Résultat intermédiaire final (division de chacun des élément de la matrice
1 par l'élément correspondant de la matrice 2) :
{0.25;0.25;0;0.5;0.25;0.25;0.5}
Donc =SOMMEPROD({0.25;0.25;0;0.5;0.25;0.25;0.5}) --> 2 qui est bien le
résulat attendu

AV Pour la SPA (certains se souviennent surement..)

.....qui pourrait m'expliquer comment fonctionne
cette formule "magique" ?

Tu l'auras voulu.....

Préambule : pour trouver les résultats intermédiaires, sélectionner la
partie de la formule concernée dans la barre de formule et appuyer sur F9
Soit la plage (A1:A7) nommée "Nom" et comprenant les valeurs suivantes
dupont;dupont;dupont;roger;dupont;dupont;roger
Utilisation de SOMMEPROD..
Question de base : Dénombrer les occurrences uniques d'une plage ne
comprenant pas de cellules vides
Une solution : =SOMMEPROD(1/NB.SI(Nom;Nom))
Explications....
La partie NB.SI(Nom;Nom) renvoie la matrice de constantes suivante :
{5;5;5;2;5;5;2}
qui représente le nbre de fois où chacun des éléments de la plage est
représenté
L'astuce de la formule consiste à diviser 1 par chacun des éléments de
cette matrice
Résultat : {0.2;0.2;0.2;0.5;0.2;0.2;0.5}
Ce qui correspond à "la fraction de représentativité" de chacun des
éléments de la plage
Reste donc à utiliser SOMMEPROD pour faire la somme de ces "fractions" et
obtenir 2 qui représente le nbre d'occurrences uniques (dupont;roger)

Là où l'affaire se complique c'est lorsque la plage "Nom" comprend une ou
des cellules vides
Exemple avec les valeurs suivantes en A1:A7 (A3 est vide) :
dupont;dupont;"vide";roger;dupont;dupont;roger
la partie NB.SI(Nom;Nom) va renvoyer la matrice {4;4;0;2;4;4;2}
L'opération 1/{4;4;0;2;4;4;2} va renvoyer une valeur d'erreur lorsqu'il va
falloir diviser 1 par 0 !
Résultat : {0.25;0.25;#DIV/0!;0.5;0.25;0.25;0.5}
SOMMEPROD ne sachant pas sommer une matrice contenant des valeurs
d'erreurs >> Plouf !

L'intérêt de la formulation
=SOMMEPROD((nom<>"")/(NB.SI(nom;nom)+(nom="")))
est de supprimer ce problème
D'abord la première partie (Nom<>"") qui est un test logique, renvoie la
matrice {VRAI;VRAI;FAUX;VRAI;VRAI;VRAI;VRAI}
Rappel : VRAI équivaut à 1 FAUX équivaut à 0 donc cette matrice équivaut à
: {1;1;0;1;1;1;1}
Ce qui présente l'intérêt de renvoyer une matrice dont on va pouvoir
diviser chacun des éléments...
Si l'on se contentait de la formulation :
=SOMMEPROD((Nom<>"")/(NB.SI(Nom;Nom)))
Résultat : =SOMMEPROD({1;1;0;1;1;1;1}/{4;4;0;2;4;4;2})
la présence de cellules vides entraînerait la même erreur (division par 0)
L'astuce va consister à faire en sorte qu'il n'y ai plus de 1 divisé par 0
mais 0 divisé par 1, opération qui, elle, ne renvoie pas d'erreur mais 0, valeur
qui n'aura aucune incidence dans la somme faite, in fine, par SOMMEPROD !
Explication :
Si l'on prend la partie NB.SI(nom;nom)+(nom=""))
elle renvoie : {4;4;0;2;4;4;2}+{0;0;1;0;0;0;0}
la partie test logique (nom="") renvoie la matrice
{FAUX;FAUX;VRAI;FAUX;FAUX;FAUX;FAUX}
autrement dit : {0;0;1;0;0;0;0}
L'addition des 2 matrices {4;4;0;2;4;4;2}+{0;0;1;0;0;0;0}) a donc pour
résultat :
{4;4;1;2;4;4;2}
On voit là que l'élément contenant la cellule vide n'est plus égal à 0
mais à 1 !
Résumé :
=SOMMEPROD((nom<>"")/(NB.SI(nom;nom)+(nom="")))
renvoie comme résultat intermédiaire :
=SOMMEPROD({1;1;0;1;1;1;1}/{4;4;1;2;4;4;2})
On revoit que la division des 3° éléments de chacune des matrices( 0/1) ne
renverra que 0 et non plus une erreur
Résultat intermédiaire final (division de chacun des élément de la matrice
1 par l'élément correspondant de la matrice 2) :
{0.25;0.25;0;0.5;0.25;0.25;0.5}
Donc =SOMMEPROD({0.25;0.25;0;0.5;0.25;0.25;0.5}) --> 2 qui est bien le
résulat attendu

AV Pour la SPA (certains se souviennent surement..)

Vous avez filtré cet utilisateur ! Consultez son message

.....qui pourrait m'expliquer comment fonctionne
cette formule "magique" ?

Tu l'auras voulu.....

Préambule : pour trouver les résultats intermédiaires, sélectionner la
partie de la formule concernée dans la barre de formule et appuyer sur F9
Soit la plage (A1:A7) nommée "Nom" et comprenant les valeurs suivantes
dupont;dupont;dupont;roger;dupont;dupont;roger
Utilisation de SOMMEPROD..
Question de base : Dénombrer les occurrences uniques d'une plage ne
comprenant pas de cellules vides
Une solution : =SOMMEPROD(1/NB.SI(Nom;Nom))
Explications....
La partie NB.SI(Nom;Nom) renvoie la matrice de constantes suivante :
{5;5;5;2;5;5;2}
qui représente le nbre de fois où chacun des éléments de la plage est
représenté
L'astuce de la formule consiste à diviser 1 par chacun des éléments de
cette matrice
Résultat : {0.2;0.2;0.2;0.5;0.2;0.2;0.5}
Ce qui correspond à "la fraction de représentativité" de chacun des
éléments de la plage
Reste donc à utiliser SOMMEPROD pour faire la somme de ces "fractions" et
obtenir 2 qui représente le nbre d'occurrences uniques (dupont;roger)

Là où l'affaire se complique c'est lorsque la plage "Nom" comprend une ou
des cellules vides
Exemple avec les valeurs suivantes en A1:A7 (A3 est vide) :
dupont;dupont;"vide";roger;dupont;dupont;roger
la partie NB.SI(Nom;Nom) va renvoyer la matrice {4;4;0;2;4;4;2}
L'opération 1/{4;4;0;2;4;4;2} va renvoyer une valeur d'erreur lorsqu'il va
falloir diviser 1 par 0 !
Résultat : {0.25;0.25;#DIV/0!;0.5;0.25;0.25;0.5}
SOMMEPROD ne sachant pas sommer une matrice contenant des valeurs
d'erreurs >> Plouf !

L'intérêt de la formulation
=SOMMEPROD((nom<>"")/(NB.SI(nom;nom)+(nom="")))
est de supprimer ce problème
D'abord la première partie (Nom<>"") qui est un test logique, renvoie la
matrice {VRAI;VRAI;FAUX;VRAI;VRAI;VRAI;VRAI}
Rappel : VRAI équivaut à 1 FAUX équivaut à 0 donc cette matrice équivaut à
: {1;1;0;1;1;1;1}
Ce qui présente l'intérêt de renvoyer une matrice dont on va pouvoir
diviser chacun des éléments...
Si l'on se contentait de la formulation :
=SOMMEPROD((Nom<>"")/(NB.SI(Nom;Nom)))
Résultat : =SOMMEPROD({1;1;0;1;1;1;1}/{4;4;0;2;4;4;2})
la présence de cellules vides entraînerait la même erreur (division par 0)
L'astuce va consister à faire en sorte qu'il n'y ai plus de 1 divisé par 0
mais 0 divisé par 1, opération qui, elle, ne renvoie pas d'erreur mais 0, valeur
qui n'aura aucune incidence dans la somme faite, in fine, par SOMMEPROD !
Explication :
Si l'on prend la partie NB.SI(nom;nom)+(nom=""))
elle renvoie : {4;4;0;2;4;4;2}+{0;0;1;0;0;0;0}
la partie test logique (nom="") renvoie la matrice
{FAUX;FAUX;VRAI;FAUX;FAUX;FAUX;FAUX}
autrement dit : {0;0;1;0;0;0;0}
L'addition des 2 matrices {4;4;0;2;4;4;2}+{0;0;1;0;0;0;0}) a donc pour
résultat :
{4;4;1;2;4;4;2}
On voit là que l'élément contenant la cellule vide n'est plus égal à 0
mais à 1 !
Résumé :
=SOMMEPROD((nom<>"")/(NB.SI(nom;nom)+(nom="")))
renvoie comme résultat intermédiaire :
=SOMMEPROD({1;1;0;1;1;1;1}/{4;4;1;2;4;4;2})
On revoit que la division des 3° éléments de chacune des matrices( 0/1) ne
renverra que 0 et non plus une erreur
Résultat intermédiaire final (division de chacun des élément de la matrice
1 par l'élément correspondant de la matrice 2) :
{0.25;0.25;0;0.5;0.25;0.25;0.5}
Donc =SOMMEPROD({0.25;0.25;0;0.5;0.25;0.25;0.5}) --> 2 qui est bien le
résulat attendu

AV Pour la SPA (certains se souviennent surement..)

AV

06/12/2006 à 11:08

plus compliqué : pour chaque ligne j'aurai besoin de la liste des
éléments distincts relatifs à la personne

Pour la question subsidiaire...
Si en A2:A8 tu as la liste des noms et en B2:B8 la liste des "éléments" :
En C2 et recopie > C8
Matricielle (valider par ctrl+maj+entrée)
=NB(1/FREQUENCE(SI((A2:A8¢);EQUIV(Trucs;Trucs;0)+CELLULE("Row";A2:A8)-1);LIGNE(A2:A8)))

Avis perso : il est préférable d'utiliser un TCD en ajoutant un champ dans
une colonne masquée
Voir l'exemple ci-joint

http://cjoint.com/?mglkK2P2Lf

AV

Mat

06/12/2006 à 11:52

Tu l'auras voulu.....

magistral ! ! !

merci beaucoup pour la démonstration et pour la formule matricielle
répondant au deuxième point,
Mat =:c)

compter les occurences distinctes - SOMMEPROD

3 réponses

Veuillez sélectionner un problème