Compréhension Nb.si

[Calvus]

Bonjour,

J'essaie de disséquer des formules en ma possession pour compréhension, et bute déjà sur des choses que je supposais simples.

Quelqu'un pourrait il m'expliquer les résultats obtenus sur le fichier joint ?

La formule est en E5 et copiée vers le bas. J'ai mis à droite les différentes valeurs obtenues lorsque j'entre a b ou c en E1.

Merci.

PS: je ne cherche rien de particulier sur ce fichier, mais à comprendre le fonctionnement de la formule nb.si

 

[Tibo]

Re : Compréhension Nb.si

Salut Calvus ,

Mais il est où le fichier ?

Voir la taille du fichier (< 48.8 ko), ne pas hésiter à le zipper

A te (re)lire

@+

 

[Calvus]

Re : Compréhension Nb.si

Hello Tibo

Evidemment, j'ai oublié le fichier !!

Voilà, c'est réparé.
A+

 

[Tibo]

Re : Compréhension Nb.si

re,

La formule NB.SI permet normalement de déterminer combien de fois une valeur est présente dans une plage de cellules.

Là, tu testes combien de fois les valeurs d'une plage de cellules sont présentes dans une autre plage de cellules.

Il faudrait d'ailleurs que ta formule soit plutôt écrite ainsi :

=NB.SI([COLOR=Red][B]$E$1:$E$4[/B][/COLOR];$A$1:$A$10)

avec les $ de façon à figer les références de la plage de cellules.

On aborde ici le domaine de la formule matricielle.

Pour t'en convaincre, tapes b en E2, puis sélectionne la formule dans la barre d'outils et fais F9

Tu vas voir apparaître une matrice qui va t'indiquer combien de fois les valeurs de A1:A10 sont trouvées dans E1:E4

On peut en obtenir la somme avec la fonction SOMMEPROD :

=SOMMEPROD((NB.SI($E$1:$E$4;$A$1:$A$10))*1)

Par contre, il n'y a pas vraiment d'intérêt, me semble-t-il à recopier la formule vers le bas

@+

 

[Calvus]

Re : Compréhension Nb.si

Merci Tibo,

Je ne connaissais pas cette façon de faire avec F9, c'est bien pratique.

Ok, si j'ai bien compris, la formule va compter combien de fois on va trouver une valeur en colonne A dans la colonne E.
J'ai réduit le champ pour plus de simplicité, à a1:a5 et e1:e5.

Donc effectivement, si j'ai "toto" en col A, et que je l'ai deux fois en col E, alors excel renvoie la valeur 2.

En revanche, là où ça se complique, c'est qu'en fonction de la formule matricielle ou non, et de la position du 1er "toto", on n'a plus du tout les mêmes résultats. Du coup je ne sais pas ce qui est testé, et j'en perds à nouveau mon latin.

En matricielle, il faut que A1 soit rempli, sinon rien
Non matricielle, il faut que A5 soit rempli !
Au milieu, excel s'en fout !

Je joins un nouveau fichier modifié où j'ai fait 4 tests qui renvoient des valeurs différentes.

 

[Tibo]

Re : Compréhension Nb.si

re,

Si tu sélectionnes chaque formule dans la barre de formule, tu verras que le résultat renvoyé correspond au 1er élément de la matrice. Mais, dans la cellule, on ne voit pas les autres éléments.

Normal, une matrice est l'équivalent de plusieurs cellules et on ne peut mettre plusieurs cellules dans une seule cellule.

Après, il faut savoir quelle information tu souhaites tirer de cette formule.

S'il s'agit de savoir combien de fois au maximum une valeur est présente dans l'autre plage, on utilisera la fonction MAX :

Par exemple, en H17 :

=MAX(NB.SI(H13:H16;G13:G17))

Formule matricielle à valider par CTRL + MAJ + ENTREE

Et là, la fonction MAX ne retourne qu'un seul résultat.

Je te laisse essayer

@+

 

[Calvus]

Re : Compréhension Nb.si

Re,

Bon, ça avance.
Mais si je remplace Max par Min, alors c'est à nouveau farfelu pour ma compréhension. Il faut que G13:G17 soient renseignées, sinon XL affiche zéro.
Alors comment interprêter le Min dans ce cas ?

Merci

 

[Tibo]

Re : Compréhension Nb.si

re,

En H17 par exemple, la formule :

=NB.SI(H13:H16;G13:G17)

retourne la matrice suivante :

={0;3;3;3;0}

Le MIN est bien 0

Ensuite, si on souhaite avoir la valeur MIN différente de 0, il faut intégrer un test dans la formule matricielle :

=MIN(SI(NB.SI(H13:H16;G13:G17)<>0;NB.SI(H13:H16;G13:G17)))

Toujours matricielle

@+

 

[Calvus]

Re : Compréhension Nb.si

Quel c.. je fais !
Bien sur, le Min est zéro, mais zéro n'est pas nul, évidemment !
Voilà une subtilité très intéressante.

Je te remercie.

Je continue donc.

A+

 

[Calvus]

Re : Compréhension Nb.si

Bonjour,

Tibo, ou quelqu'un d'autre évidemment, j'ai une nouvelle incompréhension.

Je joins un nouveau fichier, dans la continuité de ce post.

Voici la formule et comment je l'interprète, certainement là le problème :

=MIN(SI(NB.SI($B30:B30;A30:A34)=0;"DD"))

=Min ---> prendre le plus petit nombre

Si ---> à condition que

NB.SI($B30:B30;A30:A34)=0;---> compter combien de fois se trouve une valeur de la plage A30:A34 dans la cellule cellule B30 et vérifier que ça fasse 0

valeur si vrai ---> alors, inscrire DD

Or je me retrouve avec un 0 ! Et que j'entre une valeur en B30 ou pas ne change rien.

Qu'est ce que j'ai loupé ?

Merci

 

[phlaurent55]

Re : Compréhension Nb.si

Bonjour à tous,

Voici la formule et comment je l'interprète, certainement là le problème :

=MIN(SI(NB.SI($B30:B30;A30:A34)=0;"DD"))

=Min ---> prendre le plus petit nombre

Si ---> à condition que

NB.SI($B30:B30;A30:A34)=0;---> compter combien de fois se trouve une valeur de la plage A30:A34 dans la cellule cellule B30 et vérifier que ça fasse 0

valeur si vrai ---> alors, inscrire DD

Or je me retrouve avec un 0 ! Et que j'entre une valeur en B30 ou pas ne change rien.

Qu'est ce que j'ai loupé ?

pour ce que j'ai mis en caractères rouges, ce raisonnement est à l'opposé de toute forme de logique
Pourrais-tu REpréciser ta question et REposter un fichier avec les cellules nécessaires ( pas besoin du reste)



à+
Philippe

 

[Calvus]

Re : Compréhension Nb.si

pour ce que j'ai mis en caractères rouges, ce raisonnement est à l'opposé de toute forme de logique

Oui, je me suis mal exprimé en effet.

Ce que je voulais dire, NB.SI($B30:B34;A30:A34) teste si une valeur présente en A se retrouve en B. =0 vérifie qu'il n'y en a pas

Est ce plus logique ?

Nouveau fichier donc formule avec valeurs différentes

Merci
A+

 

[phlaurent55]

Re : Compréhension Nb.si

Re,

Mais que viens faire "MIN" dans la formule du fichier joint ???

essaie avec ceci .................... mais je n'ai toujours pas compris ta question et à quoi tu veux arriver
=SI(NB.SI($A30:A34;B30)=0;"DD";"")

à+
Philippe

 

[Calvus]

Re : Compréhension Nb.si

OK Phlaurent,

J'avais déjà essayé ça, et en effet, ça fonctionne.

Comme je le disais en début de fil, je voulais disséquer une formule pour tenter de bien comprendre et ensuite d'adapter.
Les personnes présentes sur ce forum, dont tu fais partie, m'ont aidé à plusieurs reprises, et m'ont dans bien des cas apporté des solutions à des problèmes complexes et devant lesquels je serais resté tout aussi dénué.

J'essaie donc de comprendre ce que l'on m'a parfois envoyé, et qui fonctionne, mais que pour partie je me bornais à copier ou adapter à mes fichiers, sans saisir les nuances ni être capable de les reproduire seul. Ce que je m'efforce d'essayer de faire.

Alors dans ce cas, effectivement, peut être que le problème posé un peu plus haut " est sorti de son contexte " et que ça ne peut pas donner quoi que ce soit.

Voici donc la formule originale

=INDEX($A$1:$A$20;MIN(SI(NB.SI(AF$8:AF8;$A$9:$A$20)=0;SI(($A$9:$A$20<>"")*($M$9:$M$20="complet")=1;LIGNE($A$9:$A$20)))))&""

et le 1er fichier.

Cette formule n'est pas adaptée à ce fichier, je m'en suis servi pour chercher la façon de ne prendre en compte que certaines plages
Ce que je veux obtenir à terme, c'est de faire une somme, ou un sous total en colonne C en fonction des col A et B. Cad, que pour une date donnée en B, faire la somme de A1 à A3, ensuite somme de D4, puis A5 A6, etc.

Je livre le fichier tel qu'il est, avec mes recherches en cours.
Ce que je souhaite maintenant n'est pas la solution, mais d'arriver tout seul à le faire. Mais ce n'est pas gagné. Ma demande est certainement particulière, mais elle a un caractère davantage pédagogique qu'une solution clés en main.

Pardon pour ces longues explications, mais il me semble qu'alors tu comprendras mieux mon cheminement.

Merci de ton attention, ainsi que celle des autres.

Cordialement

PS: si tu me livres la solution, je ne t'en voudrai pas, mais la lirai plus tard

 

[phlaurent55]

Re : Compréhension Nb.si

Re,

Pardon pour ces longues explications, mais il me semble qu'alors tu comprendras mieux mon cheminement.

toujours rien compris
voir fichier joint ( avec formules)
si tu ne veux pas de lignes vides, il faudra le faire par macro

à+
Philippe