Logo GNT
Actualités Tests & Guides Bons Plans
Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11
Entraide Applications Microsoft Office Excel Formule magique plus

Formule magique plus

32 réponses
Avatar
HB
Bonsoir,

Je ne crois pas être totalement nul
mais certaines formules me laissent pantois ...

Pour obtenir le nombre de valeurs distinctes dans B2:B700
je sais que la formule "matricielle" suivante fonctionne

{=SOMME(SI(B2:B700<>"";1/NB.SI(B2:B700;B2:B700)))}

Toutefois, je ne comprends pas du tout comment elle fonctionne.

En plus il faudrait que j'ajoute un argument conditionnel
faisant référence à la colonne A.

En effet je voudrais le
nombre de valeurs distinctes de B2:B700
parmi celles qui correspondent à la valeur "XXX" dans la colonne A
(les valeurs dans la colonne A seront au format texte...).

(if you see what I mean)

Merci d'avance aux supers cracks des formules qui voudront bien avoir
pitié de mes pauvres neurones...

Cordialement,

HB

---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
Signaler un problème avec ce contenu

10 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
1 2 3 4
Avatar
DanielCo
Bonjour,
SOMME(1/NB.SI(B2:B700;B2:B700))
est la formule la plus célèbre du monde. Elle est plus facile à
comprendre en surlignant 1/NB.SI(B2:B700;B2:B700) - de préférence une
plage beaucoup plus petite - et en appuyant sur F9. Le principe est
que, si une valeur est présente deux fois, la fonction NB.SI renvoie 2,
1/nb.si renvoie 0,5 pour chacune des deux valeurs. Leur somme est donc
égale à 1 quelque soit le nombre de fois où la valeur est présente. Tu
obtienss ainsi le nombre de valeurs distinctes.
Simple, mais il fallait y penser

Bonsoir,

Je ne crois pas être totalement nul
mais certaines formules me laissent pantois ...

Pour obtenir le nombre de valeurs distinctes dans B2:B700
je sais que la formule "matricielle" suivante fonctionne

{=SOMME(SI(B2:B700<>"";1/NB.SI(B2:B700;B2:B700)))}

Toutefois, je ne comprends pas du tout comment elle fonctionne.

En plus il faudrait que j'ajoute un argument conditionnel
faisant référence à la colonne A.

En effet je voudrais le
nombre de valeurs distinctes de B2:B700
parmi celles qui correspondent à la valeur "XXX" dans la colonne A
(les valeurs dans la colonne A seront au format texte...).

(if you see what I mean)

Merci d'avance aux supers cracks des formules qui voudront bien avoir
pitié de mes pauvres neurones...

Cordialement,

HB

---
L'absence de virus dans ce courrier électronique a été vérifiée par
le logiciel antivirus Avast.
https://www.avast.com/antivirus
Avatar
Jacquouille
Bonsoir,
Et en prenant une petite dose de Sommeprod?
=sommeprod((a2:a700="XXX")*(SOMME(1/NB.SI(B2:B700;B2:B700)))

Quant à : "Je ne crois pas être totalement nul" --> parfois, on se
méconnait, --> mais c'est de l'humour.... -))
Quant à : "if you see what I mean" --> c'est du grand breton --> ignoré.

Jacques
" Le vin est au repas ce que le parfum est à la femme."
.
"HB" a écrit dans le message de groupe de discussion :
574c7ee7$0$3343$

Bonsoir,

Je ne crois pas être totalement nul
mais certaines formules me laissent pantois ...

Pour obtenir le nombre de valeurs distinctes dans B2:B700
je sais que la formule "matricielle" suivante fonctionne

{=SOMME(SI(B2:B700<>"";1/NB.SI(B2:B700;B2:B700)))}

Toutefois, je ne comprends pas du tout comment elle fonctionne.

En plus il faudrait que j'ajoute un argument conditionnel
faisant référence à la colonne A.

En effet je voudrais le
nombre de valeurs distinctes de B2:B700
parmi celles qui correspondent à la valeur "XXX" dans la colonne A
(les valeurs dans la colonne A seront au format texte...).

(if you see what I mean)

Merci d'avance aux supers cracks des formules qui voudront bien avoir
pitié de mes pauvres neurones...

Cordialement,

HB

---
L'absence de virus dans ce courrier électronique a été vérifiée par le
logiciel antivirus Avast.
https://www.avast.com/antivirus


---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
http://www.avast.com
Avatar
MichD
Bonjour,

Validation matricielle : Ctrl + Maj + Enter
=SOMME(SI((B1:B7<>"")*(NB.SI(A1:A7;B1:B7)>0)=1;1/NB.SI(B1:B7;B1:B7)))

Tu as le nombre total de valeurs distinctes contenues en B1:B7 seulement pour les valeurs
présentes en A1:A7 et qui ne sont pas égales à "" (nul)

MichD
Avatar
HB
La célébrité tient donc à peu de choses ;o)


Si j'ai bien compris
La somme calculée est en fait la somme des
SI( Bx<>""; 1/NB.SI(B2:B700;Bx))

avec x variant de 2 à 700


En plus j'aurais besoin d'un conditionnement

mais un premier test avec
{=somme(SI( A2:A700="Toto"; 1/NB.SI(B2:B700;B2:B700)))}

ne fonctionne pas ... et en y réfléchissant c'est normal ..
J'obtiens des valeurs décimales car le test de nb.si ne tiens pas compte
du tri attendu ...


une idée ( de génie)


Le 30/05/2016 à 20:10, DanielCo a écrit :
Bonjour,
SOMME(1/NB.SI(B2:B700;B2:B700))
est la formule la plus célèbre du monde. Elle est plus facile à
comprendre en surlignant 1/NB.SI(B2:B700;B2:B700) - de préférence une
plage beaucoup plus petite - et en appuyant sur F9. Le principe est que,
si une valeur est présente deux fois, la fonction NB.SI renvoie 2,
1/nb.si renvoie 0,5 pour chacune des deux valeurs. Leur somme est donc
égale à 1 quelque soit le nombre de fois où la valeur est présente. Tu
obtienss ainsi le nombre de valeurs distinctes.
Simple, mais il fallait y penser

Bonsoir,

Je ne crois pas être totalement nul
mais certaines formules me laissent pantois ...

Pour obtenir le nombre de valeurs distinctes dans B2:B700
je sais que la formule "matricielle" suivante fonctionne

{=SOMME(SI(B2:B700<>"";1/NB.SI(B2:B700;B2:B700)))}

Toutefois, je ne comprends pas du tout comment elle fonctionne.

En plus il faudrait que j'ajoute un argument conditionnel
faisant référence à la colonne A.

En effet je voudrais le
nombre de valeurs distinctes de B2:B700
parmi celles qui correspondent à la valeur "XXX" dans la colonne A
(les valeurs dans la colonne A seront au format texte...).

(if you see what I mean)

Merci d'avance aux supers cracks des formules qui voudront bien avoir
pitié de mes pauvres neurones...

Cordialement,

HB

---
L'absence de virus dans ce courrier électronique a été vérifiée par le
logiciel antivirus Avast.
https://www.avast.com/antivirus






---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
Avatar
Jacquouille
Bonjour
Avez-vous testé Sommeprod?

Jacques
" Le vin est au repas ce que le parfum est à la femme."
.
"HB" a écrit dans le message de groupe de discussion :
574ca120$0$3321$

La célébrité tient donc à peu de choses ;o)


Si j'ai bien compris
La somme calculée est en fait la somme des
SI( Bx<>""; 1/NB.SI(B2:B700;Bx))

avec x variant de 2 à 700


En plus j'aurais besoin d'un conditionnement

mais un premier test avec
{=somme(SI( A2:A700="Toto"; 1/NB.SI(B2:B700;B2:B700)))}

ne fonctionne pas ... et en y réfléchissant c'est normal ..
J'obtiens des valeurs décimales car le test de nb.si ne tiens pas compte
du tri attendu ...


une idée ( de génie)


Le 30/05/2016 à 20:10, DanielCo a écrit :
Bonjour,
SOMME(1/NB.SI(B2:B700;B2:B700))
est la formule la plus célèbre du monde. Elle est plus facile à
comprendre en surlignant 1/NB.SI(B2:B700;B2:B700) - de préférence une
plage beaucoup plus petite - et en appuyant sur F9. Le principe est que,
si une valeur est présente deux fois, la fonction NB.SI renvoie 2,
1/nb.si renvoie 0,5 pour chacune des deux valeurs. Leur somme est donc
égale à 1 quelque soit le nombre de fois où la valeur est présente. Tu
obtienss ainsi le nombre de valeurs distinctes.
Simple, mais il fallait y penser

Bonsoir,

Je ne crois pas être totalement nul
mais certaines formules me laissent pantois ...

Pour obtenir le nombre de valeurs distinctes dans B2:B700
je sais que la formule "matricielle" suivante fonctionne

{=SOMME(SI(B2:B700<>"";1/NB.SI(B2:B700;B2:B700)))}

Toutefois, je ne comprends pas du tout comment elle fonctionne.

En plus il faudrait que j'ajoute un argument conditionnel
faisant référence à la colonne A.

En effet je voudrais le
nombre de valeurs distinctes de B2:B700
parmi celles qui correspondent à la valeur "XXX" dans la colonne A
(les valeurs dans la colonne A seront au format texte...).

(if you see what I mean)

Merci d'avance aux supers cracks des formules qui voudront bien avoir
pitié de mes pauvres neurones...

Cordialement,

HB

---
L'absence de virus dans ce courrier électronique a été vérifiée par le
logiciel antivirus Avast.
https://www.avast.com/antivirus






---
L'absence de virus dans ce courrier électronique a été vérifiée par le
logiciel antivirus Avast.
https://www.avast.com/antivirus


---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
http://www.avast.com
Avatar
isabelle
bonjour HD,

une matricielle à valider avec ctrl+shift+enter

=NB(SI((A1:A700="XXX")*(B1:B700>0)*(LIGNE(B1:B700));1/FREQUENCE(EQUIV(B1:B700&A1:A700;B1:B700&A1:A700;0);LIGNE(INDIRECT("1:"&LIGNES(B1:B700))))))

http://www.cjoint.com/c/FEFmt1OLKOa

isabelle

Le 2016-05-30 à 16:22, HB a écrit :
La célébrité tient donc à peu de choses ;o)


Si j'ai bien compris
La somme calculée est en fait la somme des
SI( Bx<>""; 1/NB.SI(B2:B700;Bx))

avec x variant de 2 à 700


En plus j'aurais besoin d'un conditionnement

mais un premier test avec
{=somme(SI( A2:A700="Toto"; 1/NB.SI(B2:B700;B2:B700)))}

ne fonctionne pas ... et en y réfléchissant c'est normal ..
J'obtiens des valeurs décimales car le test de nb.si ne tiens pas compte du tri
attendu ...


une idée ( de génie)


Le 30/05/2016 à 20:10, DanielCo a écrit :
Bonjour,
SOMME(1/NB.SI(B2:B700;B2:B700))
est la formule la plus célèbre du monde. Elle est plus facile à
comprendre en surlignant 1/NB.SI(B2:B700;B2:B700) - de préférence une
plage beaucoup plus petite - et en appuyant sur F9. Le principe est que,
si une valeur est présente deux fois, la fonction NB.SI renvoie 2,
1/nb.si renvoie 0,5 pour chacune des deux valeurs. Leur somme est donc
égale à 1 quelque soit le nombre de fois où la valeur est présente. Tu
obtienss ainsi le nombre de valeurs distinctes.
Simple, mais il fallait y penser

Bonsoir,

Je ne crois pas être totalement nul
mais certaines formules me laissent pantois ...

Pour obtenir le nombre de valeurs distinctes dans B2:B700
je sais que la formule "matricielle" suivante fonctionne

{=SOMME(SI(B2:B700<>"";1/NB.SI(B2:B700;B2:B700)))}

Toutefois, je ne comprends pas du tout comment elle fonctionne.

En plus il faudrait que j'ajoute un argument conditionnel
faisant référence à la colonne A.

En effet je voudrais le
nombre de valeurs distinctes de B2:B700
parmi celles qui correspondent à la valeur "XXX" dans la colonne A
(les valeurs dans la colonne A seront au format texte...).

(if you see what I mean)

Merci d'avance aux supers cracks des formules qui voudront bien avoir
pitié de mes pauvres neurones...

Cordialement,

HB

---
L'absence de virus dans ce courrier électronique a été vérifiée par le
logiciel antivirus Avast.
https://www.avast.com/antivirus






---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel
antivirus Avast.
https://www.avast.com/antivirus

Avatar
HB
Bonjour,

Je réponds à tous sous le dernier message :

Pour le p"tit coup de somme prod : cela ne marche pas, désolé.

Pour la proposition de MichD : cela ne correspond pas
à ce que je cherche ; sans doute ai-je mal expliqué le but.

Mais mais mais,

la formule (looooongue) proposée par Isabelle fonctionne parfaitement
après adaptation à la situation réelle ( colonne, longueur ...)

Permettez moi d'expliquer cette histoire :

Pour illustrer de façon "concrète" le "paradoxe des anniversaires"
j'ai récupéré des données anonymes brutes sur un collège.
Les données sont dans deux colonnes
En A : les dates de naissances
En B ; les classes

Il y a 745 élèves répartis dans 27 classes
( j'avais dit B2:B700 pour donner un ordre d'idée)

Les données sont en vrac (aucun rangement)
En colonne C je met la date anniversaire
( de l'année 1908 ) et l'affichage est "j mmmm"
ÚTE(1908;MOIS(A2);JOUR(A2))

Ensuite je fais un tableau avec
les noms des 27 classes (E2:E28)
l'effectif (F2:F28) (un nb.si facile)

et pour G2:G28 ....
le nombre de dates distinctes prises dans la partie de C2:C746
qui correspond aux élèves de la classe concernée
et là, trône la loooongue formule :

Celle de G2 est donc :
{=NB(SI((B$2:B$746â)*(C$2:C$746>0)*(LIGNE(C$2:C$746));1/FREQUENCE(EQUIV(C$2:C$746&B$2:B$746;C$2:C$746&B$2:B$746;0);LIGNE(INDIRECT("1:"&LIGNES(C$2:C$746))))))}



Si ce nombre est inférieur à l'effectif, cela signifie que dans cette
classe, au moins deux élèves fêtent leurs anniversaires le même jour.

Bien sûr on trouve des coïncidences dans plus de la moitié des classes
et dans plusieurs classes il y a plus d'une coïncidence.

Cette histoire est célèbre car ces résultats
sont contraires à l'intuition :

Si on prend N personnes "au hasard" dans la population,
la probabilité que, sur les N, deux au moins fêtent leur anniversaires
le même jour augmente très vite... voici quelques valeurs :

nb de personnes probabilité en pourcentage (arrondie)

15 25,3%
23 50,7%
32 73,3%
41 90,3%
57 99,0%
70 99,92%

C'est parce que ces résultats sont très surprenants
que ce phénomène est nommé "Paradoxe des anniversaires".

Les calculs sont bien sûr théoriques et supposent que les naissances
sont parfaitement réparties sur l'année.
Comme ce n'est pas vrai, les fréquences constatées peuvent être
facilement supérieures aux probas calculées.

Dans le cas de ce collège, comme les effectifs des classes sont entre 25
et 30, il est normale que la coïncidence soit présente plus de la moitié
des classes ...


Merci de vous être penchés sur mon problème...


Bien sûr, on peut aussi
-ranger les données selon les classes
puis
-récupérer les nom des plages pour les 27 classes
puis
-utiliser le célèbre somme(1/nb.si
pour chaque plage

Je l'avais fait mais je voulais savoir si on pouvait éviter
ces intermédiaires

La réponse d'Isabelle prouve que la réponse est
"Oui, mais c'est plutôt compliqué."

Cordialement,

HB


Le 31/05/2016 à 14:20, isabelle a écrit :
bonjour HD,

une matricielle à valider avec ctrl+shift+enter

=NB(SI((A1:A700="XXX")*(B1:B700>0)*(LIGNE(B1:B700));1/FREQUENCE(EQUIV(B1:B700&A1:A700;B1:B700&A1:A700;0);LIGNE(INDIRECT("1:"&LIGNES(B1:B700))))))






---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
Avatar
Jacquouille
Bonsoir
Il serait intéressant de connaître le % de différence entre un échantillon
de x personnes d'une même classe, donc sensiblement du même âge, comparé à
un échantillon de x personnes prises au hasard (entre 0 et 100 ans), cela
s'entend....
-))

Jacques
" Le vin est au repas ce que le parfum est à la femme."
.
"HB" a écrit dans le message de groupe de discussion :
574dbec3$0$3320$

Bonjour,

Je réponds à tous sous le dernier message :

Pour le p"tit coup de somme prod : cela ne marche pas, désolé.

Pour la proposition de MichD : cela ne correspond pas
à ce que je cherche ; sans doute ai-je mal expliqué le but.

Mais mais mais,

la formule (looooongue) proposée par Isabelle fonctionne parfaitement
après adaptation à la situation réelle ( colonne, longueur ...)

Permettez moi d'expliquer cette histoire :

Pour illustrer de façon "concrète" le "paradoxe des anniversaires"
j'ai récupéré des données anonymes brutes sur un collège.
Les données sont dans deux colonnes
En A : les dates de naissances
En B ; les classes

Il y a 745 élèves répartis dans 27 classes
( j'avais dit B2:B700 pour donner un ordre d'idée)

Les données sont en vrac (aucun rangement)
En colonne C je met la date anniversaire
( de l'année 1908 ) et l'affichage est "j mmmm"
ÚTE(1908;MOIS(A2);JOUR(A2))

Ensuite je fais un tableau avec
les noms des 27 classes (E2:E28)
l'effectif (F2:F28) (un nb.si facile)

et pour G2:G28 ....
le nombre de dates distinctes prises dans la partie de C2:C746
qui correspond aux élèves de la classe concernée
et là, trône la loooongue formule :

Celle de G2 est donc :
{=NB(SI((B$2:B$746â)*(C$2:C$746>0)*(LIGNE(C$2:C$746));1/FREQUENCE(EQUIV(C$2:C$746&B$2:B$746;C$2:C$746&B$2:B$746;0);LIGNE(INDIRECT("1:"&LIGNES(C$2:C$746))))))}



Si ce nombre est inférieur à l'effectif, cela signifie que dans cette
classe, au moins deux élèves fêtent leurs anniversaires le même jour.

Bien sûr on trouve des coïncidences dans plus de la moitié des classes
et dans plusieurs classes il y a plus d'une coïncidence.

Cette histoire est célèbre car ces résultats
sont contraires à l'intuition :

Si on prend N personnes "au hasard" dans la population,
la probabilité que, sur les N, deux au moins fêtent leur anniversaires
le même jour augmente très vite... voici quelques valeurs :

nb de personnes probabilité en pourcentage (arrondie)

15 25,3%
23 50,7%
32 73,3%
41 90,3%
57 99,0%
70 99,92%

C'est parce que ces résultats sont très surprenants
que ce phénomène est nommé "Paradoxe des anniversaires".

Les calculs sont bien sûr théoriques et supposent que les naissances
sont parfaitement réparties sur l'année.
Comme ce n'est pas vrai, les fréquences constatées peuvent être
facilement supérieures aux probas calculées.

Dans le cas de ce collège, comme les effectifs des classes sont entre 25
et 30, il est normale que la coïncidence soit présente plus de la moitié
des classes ...


Merci de vous être penchés sur mon problème...


Bien sûr, on peut aussi
-ranger les données selon les classes
puis
-récupérer les nom des plages pour les 27 classes
puis
-utiliser le célèbre somme(1/nb.si
pour chaque plage

Je l'avais fait mais je voulais savoir si on pouvait éviter
ces intermédiaires

La réponse d'Isabelle prouve que la réponse est
"Oui, mais c'est plutôt compliqué."

Cordialement,

HB


Le 31/05/2016 à 14:20, isabelle a écrit :
bonjour HD,

une matricielle à valider avec ctrl+shift+enter

=NB(SI((A1:A700="XXX")*(B1:B700>0)*(LIGNE(B1:B700));1/FREQUENCE(EQUIV(B1:B700&A1:A700;B1:B700&A1:A700;0);LIGNE(INDIRECT("1:"&LIGNES(B1:B700))))))






---
L'absence de virus dans ce courrier électronique a été vérifiée par le
logiciel antivirus Avast.
https://www.avast.com/antivirus


---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
http://www.avast.com
Avatar
HB
Bonsoir,

Bien sûr la répartition égale sur l'année reste un modèle très simplifié...

Ce document ( un peu long) est éclairant :

https://www.ined.fr/fichier/s_rubrique/19823/popf.1_re.gnier_2010.fr.pdf

Chaque année, certains jours (WE, fériés) ont bien moins de naissance.
sur une large tranche d'âge ces effets peuvent se compenser
Sur une petite tranche d'âge, ces effets sont présents.

Donc logiquement, avec des échantillons plus variés, les pourcentages de
coïncidence devraient être plus proche de la fréquence théorique.

Pour comparer sérieusement il faudrait prendre de nombreux échantillons
(30 personnes par exemple) et comparer la moyenne des fréquences
observées selon la nature de ces échantillons
(même tranche d'âge ou pas) ...


Bref ... tout ça ne remet pas en cause le principe initial puisque même
dans le cas idéal, la fréquence est bien plus élevée que ce à quoi on
s'attend en général...

Cordialement,

HB

Le 31/05/2016 à 21:51, Jacquouille a écrit :
Bonsoir
Il serait intéressant de connaître le % de différence entre un
échantillon de x personnes d'une même classe, donc sensiblement du même
âge, comparé à un échantillon de x personnes prises au hasard (entre 0
et 100 ans), cela s'entend....
-))





---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
Avatar
isabelle
Le 2016-05-31 à 12:41, HB a écrit :

la formule (looooongue) proposée par Isabelle fonctionne parfaitement après
adaptation à la situation réelle ( colonne, longueur ...)



c'est super HD, bien contente que cette formule réponde à ton besoin,
en plus il est facile d'y ajouter d'autre conditions sans trop l'allonger ;-)

par exemple:
=NB(SI((A1:A700="XXX")*(B1:B700="a")*(C1:C700>0)*(LIGNE(C1:C700));1/FREQUENCE(
EQUIV(A1:A700&B1:B700&C1:C700;A1:A700&B1:B700&C1:C700;0);LIGNE(INDIRECT("1:"&LIGNES(C1:C700))))))

à noter:
l'ordre des arguments de SI
(A1:A700="XXX")*(B1:B700="a")*(C1:C700>0)
n'a pas d'importance

l'ordre des arguments de EQUIV
A1:A700&B1:B700&C1:C700
n'a pas d'importance sauf qu'il doivent être identique pour le 1er et le 2ème
argument de cette fonction

http://www.cjoint.com/c/FFbbeaNIMja

isabelle
1 2 3 4