Correction NB.SI --merci

[JJ1]

Bonjour à tous,
J'ai, dans mon fichier, une formule NB.SI qui compte la présence de 5 nombres dans une colonne de 6 nombres.
Je ne comprends pas le résultat:
Je cherche une suite de cinq "0" dans 9 1 0 16 3 4 et le résultat est 32?? (au lieu de 0)
l'* avec NB.SI est bien la condition ET ?
pouvez vous me dire ce qui cloche?
merci et bon AM

 

[mutzik]

Re : Correction NB.SI --merci

Bonjour JJ1,

si tu avais testé qu'une seule de tes conditions nb.si ..., tu aurais remarqué qu'elle renvoie 2, c'est à dire le nombre de 0 qu'il y a dans ta suite de nombre
comme tu fais ce test 6 fois, 2x2x2x2x2x2 = 2 puissance 6 = 32
CQFD

 

[abc]

Re : Correction NB.SI --merci

Bonjour,
pourquoi ne pas faire simplement
en CC =NB.SI(BO37:BT37;"0") ce qui te donnera =2.
Cherches-tu cela
A +

 

[JJ1]

Re : Correction NB.SI --merci

Bonjour Mutzik, abc,
merci pour vos 2 réponses, il me semblait que l' * faisait une condition ET , qu'il fallait alors avoir les 5 nombres présents dans la suite pour avoir 1
Mon but est de rechercher 5 nombres parmi 6 (quelle que soient leurs positions dans la chaîne)
voir nouveau exemple joint, si les chiffres ne se répètent pas, ça fonctionne très bien?
le viens de réaliser que le résultat est faux quand il y a des doublons ou plus, quand il n'y a que des nombres différents ça fonctionne , peut on rectifier?
merci encore

 

[abc]

Re : Correction NB.SI --merci

Bonjour,
je crois que tu as envoyé le même fichier car la formule que je t'ai proposée n'est pas inscrite.
A +

 

[JJ1]

Re : Correction NB.SI --merci

Re,
la formule est la même, mais tu remarqueras que le résultat est 1, car la combinaison des 5 nombres se retrouve bien une fois dans les 6 nombres
Dans le cas ou il n'y a pas de doublons (nombres tous différents)la formule fonctionne mais je ne sais pas l'adapter pour mon ex au dessus (en gardant la même formule) ?
merci et bon am

 

[JJ1]

Re : Correction NB.SI --merci

ps: je ne cherche pas un nombre particulier (comme le nb de 0 , d'où ta formule) mais l'occurence d'une combinaison de 5 nombres dans 6 nombres quelle que soient leurs positions.

 

[JeanMarie]

Re : Correction NB.SI --merci

Bonsoir tout le monde

Essaye avec la formule matricielle (à valider par Ctrl+Shift+Entrer)

=SOMMEPROD((FREQUENCE(SI(NB.SI(BO37:BT37;AK1:AO2)>0;LIGNE(A1:A2));LIGNE(A1:A2))>4)*1)

@+Jean-Marie

 

[JJ1]

Re : Correction NB.SI --merci

Bonsoir Jean Marie
merci pour ta formule matricielle .
Peux tu me dire pourquoi tu as rajouté la fonction "ligne"?
Dans mes ex au dessus EX2 et EX3, as-tu compris pourquoi la conjonction des nb.si et * donnait un résultat faux si uniquement doublons (autrement la formule fonctionne)
J'aimerais bien savoir le pourquoi de ce résultat faux?
merci à toi et bonne soirée

 

[JJ1]

Re : Correction NB.SI --merci

J'ai essayé ta formule matricielle, j'obtiens toujours 2 quel que soient les nombres?
-j'ai modifié la plage de recherche BO37:BO100
-j'ai modifié la plage des nombres AK1:AO1 (et non2)
faut il modifier ligne?
idem

merci

 

[JeanMarie]

Re : Correction NB.SI --merci

Re...

Arf, j'avais pris la plage AK1:AO1 pour la plage qui peut contenir plusieurs lignes.

Pour que le formule puisse fonctionner, il faut mettre la plage des numéros à analyser comme premier argument de la fonction NB.SI

=SOMMEPROD((FREQUENCE(SI(NB.SI(AK1:AO1;BO37:BT38)>0;LIGNE(A1:A2));LIGNE(A1:A2))>4)*1)

JJ1, pour comprendre la formule il faut la décomposer, je vais essayer de te l'expliquer, ci-dessous

Dans un premier temps, une plage de valeur

  |   A    |   B    |   C    |   D    |   E    |   F    | 
--|--------|--------|--------|--------|--------|--------|-
 1| Nombre à vérifier        |        |        |        | 
 2|     13 |      5 |     15 |      4 |     17 |      6 | 
 3|        |        |        |        |        |        | 
 4| Liste tirage du loto :   |        |        |        | 
 5|     11 |     13 |      3 |     16 |     15 |      8 | 
 6|      9 |     13 |     13 |      2 |      3 |     16 | 
 7|      9 |      5 |      4 |      1 |      8 |      8 | 
 8|     16 |      2 |      4 |     15 |      6 |     16 | 
 9|     17 |      9 |      4 |     14 |     16 |      5 | 
10|     12 |     15 |      2 |     17 |      1 |     15 | 
11|     16 |      9 |     11 |      4 |     11 |      8 | 
12|      1 |      8 |      7 |      3 |      5 |      9 | 
13|      7 |      1 |     14 |     19 |     19 |      4 | 
14|     12 |      3 |      8 |      1 |      6 |      5 | 
15|     11 |     17 |     19 |     15 |     15 |      4 | 
16|     14 |      7 |      7 |     14 |      4 |     16 | 
17|      5 |      6 |      5 |     10 |     13 |     12 | 
18|     16 |      1 |     15 |      2 |     11 |     11 | 
19|      2 |     17 |      1 |      7 |      9 |      7 | 
20|     11 |      9 |     17 |     16 |     18 |      1 |

La première instruction de la formule est le calcul de NB.SI,
Pour représenter la matrice retournée, il suffit de sélectionner la plage H5:M20, et de valider la formule =NB.SI(A2:F2;A5:F20) par les touches Ctrl+Shift+Entrer, tu vas obtenir ceci.

  |    H    |    I    |    J    |    K    |    L    |    M    | 
--|---------|---------|---------|---------|---------|---------|-
 4|  13=    |   5=    |  15=    |   4=    |  17=    |   6=    | 
 5|       0 |       1 |       0 |       0 |       1 |       0 | 
 6|       0 |       1 |       1 |       0 |       0 |       0 | 
 7|       0 |       1 |       1 |       0 |       0 |       0 | 
 8|       0 |       0 |       1 |       1 |       1 |       0 | 
 9|       1 |       0 |       1 |       0 |       0 |       1 | 
10|       0 |       1 |       0 |       1 |       0 |       1 | 
11|       0 |       0 |       0 |       1 |       0 |       0 | 
12|       0 |       0 |       0 |       0 |       1 |       0 | 
13|       0 |       0 |       0 |       0 |       0 |       1 | 
14|       0 |       0 |       0 |       0 |       1 |       1 | 
15|       0 |       1 |       0 |       1 |       1 |       1 | 
16|       0 |       0 |       0 |       0 |       1 |       0 | 
17|       1 |       1 |       1 |       0 |       1 |       0 | 
18|       0 |       0 |       1 |       0 |       0 |       0 | 
19|       0 |       1 |       0 |       0 |       0 |       0 | 
20|       0 |       0 |       1 |       0 |       0 |       0 |

Une valeur 0 dans la colonne H indique que la valeur A2 n'est pas présente dans la ligne 5, 6, ...., 20. Même chose pour la colonne I mais pour la valeur de la cellule B2, etc. (la ligne 4 est du commentaire, que j'ai rajouté)

Avec ces valeurs (0 et 1) il est pas encore possible de savoir si sur la même ligne il se trouve plus de 4 nombres à rechercher.

Continuons le développement, avec la partie
=NB.SI(A2:F2;A5:F20)*LIGNE(A5:A20)

  |    H    |    I    |    J    |    K    |    L    |    M    | 
--|---------|---------|---------|---------|---------|---------|-
 4|  13=    |   5=    |  15=    |   4=    |  17=    |   6=    | 
 5|       0 |       5 |       0 |       0 |       5 |       0 | 
 6|       0 |       6 |       6 |       0 |       0 |       0 | 
 7|       0 |       7 |       7 |       0 |       0 |       0 | 
 8|       0 |       0 |       8 |       8 |       8 |       0 | 
 9|       9 |       0 |       9 |       0 |       0 |       9 | 
10|       0 |      10 |       0 |      10 |       0 |      10 | 
11|       0 |       0 |       0 |      11 |       0 |       0 | 
12|       0 |       0 |       0 |       0 |      12 |       0 | 
13|       0 |       0 |       0 |       0 |       0 |      13 | 
14|       0 |       0 |       0 |       0 |      14 |      14 | 
15|       0 |      15 |       0 |      15 |      15 |      15 | 
16|       0 |       0 |       0 |       0 |      16 |       0 | 
17|      17 |      17 |      17 |       0 |      17 |       0 | 
18|       0 |       0 |      18 |       0 |       0 |       0 | 
19|       0 |      19 |       0 |       0 |       0 |       0 | 
20|       0 |       0 |      20 |       0 |       0 |       0 |

Tu peux voir que maintenant, si le nombre est trouvé la fonction retourne le numéro de la ligne.

Nous pourrions passer à la fonction FREQUENCE qui retourne pour chaque valeur du deuxième argument, le nombre de valeur connue dans la matrice. Sauf que cette formule prend le 0 comme la valeur MIN(A2:F2), il faut donc
contrôler que NB.SI soit égal à 1, c'est ce que fait =SI(NB.SI(A2:F2;A5:F20);LIGNE(A5:F20))

  |    H    |    I    |    J    |    K    |    L    |    M    | 
--|---------|---------|---------|---------|---------|---------|-
 4|  13=    |   5=    |  15=    |   4=    |  17=    |   6=    | 
 5|    FAUX |       5 |    FAUX |    FAUX |       5 |    FAUX | 
 6|    FAUX |       6 |       6 |    FAUX |    FAUX |    FAUX | 
 7|    FAUX |       7 |       7 |    FAUX |    FAUX |    FAUX | 
 8|    FAUX |    FAUX |       8 |       8 |       8 |    FAUX | 
 9|       9 |    FAUX |       9 |    FAUX |    FAUX |       9 | 
10|    FAUX |      10 |    FAUX |      10 |    FAUX |      10 | 
11|    FAUX |    FAUX |    FAUX |      11 |    FAUX |    FAUX | 
12|    FAUX |    FAUX |    FAUX |    FAUX |      12 |    FAUX | 
13|    FAUX |    FAUX |    FAUX |    FAUX |    FAUX |      13 | 
14|    FAUX |    FAUX |    FAUX |    FAUX |      14 |      14 | 
15|    FAUX |      15 |    FAUX |      15 |      15 |      15 | 
16|    FAUX |    FAUX |    FAUX |    FAUX |      16 |    FAUX | 
17|      17 |      17 |      17 |    FAUX |      17 |    FAUX | 
18|    FAUX |    FAUX |      18 |    FAUX |    FAUX |    FAUX | 
19|    FAUX |      19 |    FAUX |    FAUX |    FAUX |    FAUX | 
20|    FAUX |    FAUX |      20 |    FAUX |    FAUX |    FAUX |

Les FAUX étant exclus par la fonction FREQUENCE

Dans le tableau ci-dessous tu as dans la colonne O le résultat de la matrice de la fonction LIGNE(A5:A20), et dans la colonne Q, e résultat de la fonction
=FREQUENCE(SI(NB.SI(A2:F2;A5:F20);LIGNE(A5:F20));LIGNE(A5:A20))

  |       O       |  P  |       Q        | 
--|---------------|-----|----------------|-
 4| =LIGNE(A5:A30)|     | =FREQUENCE(……) | 
 5|             5 |     |              2 | 
 6|             6 |     |              2 | 
 7|             7 |     |              2 | 
 8|             8 |     |              3 | 
 9|             9 |     |              3 | 
10|            10 |     |              3 | 
11|            11 |     |              1 | 
12|            12 |     |              1 | 
13|            13 |     |              1 | 
14|            14 |     |              2 | 
15|            15 |     |              4 | 
16|            16 |     |              1 | 
17|            17 |     |              4 | 
18|            18 |     |              1 | 
19|            19 |     |              1 | 
20|            20 |     |              1 |

Après il suffit de contrôler que chaque valeur de la matrice FREQUENCE est supérieure à 4.

J'espère que cette longueur de post ne t'aura pas rebuté.

Pour la réponse sur le résultat faux de ta formule sur l'exemple EX2, la réponse de mutzik est absolument exact.

PS : J'avais remarqué mon erreur avant que tu postes.

@+Jean-Marie

 

[JJ1]

Re : Correction NB.SI --merci

Re Jean Marie et surtout merci pour cette explication logique.
J'ai essayé de mettre en pratique mais j'ai obtenu #N/A puis toujours 0 ?
ma formule matricielle en AP2 est:

=sommeprod((frequence(si(nb.si(AK1:AO1;BO37:BT50)>0;ligne(A37:A50));ligne(A37:A50))>4)*1)


J'ai un peu précisé mon exemple de dessus dans le fichier joint, existe t il une astuce pour éviter ces doublons ou bien je dois abandonner nb.si? Ca m'ennuie car tout mon tableau est construit avec nb.si(mais il n'y a pas ces doublons dans les autres plages)
Encore merci à toi pour ta formule et tes explications , c'est sympa.

 

[JeanMarie]

Re : Correction NB.SI --merci

Re...

Essaye avec la formule, j'ai rajouté une condition si la cellule est vide

=SOMMEPROD((FREQUENCE(SI(BO37:BT50<>"";SI(NB.SI(AK1:AO1;BO37:BT50)>0;LIGNE(A37:A50)));LIGNE(A37:A50))>4)*1)

@+Jean-Marie

 

[JJ1]

Re : Correction NB.SI --merci

Bonsoir Jean Marie,
je vais tenter ta nouvelle formule.
Je viens de faire un fichier "propre et clair" pour te montrer exactement le résultat attendu (j'ai #N/A dans ta formule?)
1--Horizontalement 3 nombres parmi 6 dans une ligne compte pour 1 , donc dans mon exemple résultat 2
2-Verticalement, 4 nombres en "descendant" dans des lignes successives comptent pour 1
pourrais tu avoir la gentillesse de m'installer ta 1ère formule horizontale?
As tu une idée pour transformer cette formule pour le cas vertical? ligne remplacé par colonne dans la formule?
Encore merci pour ton aide précieuse.
bonne soirée

 

[JeanMarie]

Re : Correction NB.SI --merci

Bonsoir

On recommence, on inverse les arguments de la fonction NB.SI, et il faut décomposer la plage de cellule BO36:BT3 en ligne unique, c'est le rôle de la partie DECALER(BO36:BT36;LIGNE(A1:A51)😉 dans la formule ci-dessous.

Formule à mettre en BN1 :

=SOMME((FREQUENCE(SI(NB.SI(DECALER(BO36:BT36;LIGNE(A1:A51););AK1:AP1)>0;LIGNE([B]A1:A51[/B]));LIGNE([B]A1:A51[/B]))=3)*1)

Petit commentaire, si tu places deux fois le même chiffre dans la plage AK:AP, le résultat ne sera pas bon !

Pour la formule en AO : tes explications sont trop succinctes. Dans ton commentaires 4 nombres parmi 4 verticalement avec un nombre par ligne descendante, que veux-tu dire par 4 verticalement ?

@+Jean-Marie