formule sommeprod ou beaucoup de nb.si?

[JJ1]

Bonjour à vous tous,
Je dois compter dans un fichier un nombre d'occurences de 3 nombres alignés verticalement.
Pouvez-vous m'aider car j'ai commencé avec nb.si et j'en ai 216 à écrire.....une bagatelle!
- La base dans laquelle je recherche le nombre d'occurences est plagée: A9:F500
- En W8:AB8: 6 nombres (appelons les a b c d e f)
- en K1313: 6 nmbres (appelons les g h i j k l)
- K1414 6 autres nombres ( m n o p q r )

je recherche les combinaisons verticales de ces 3 séries de nombres (en partant de la ligne 14 puis 13 puis 8) dans la base soit (6*6)*6 =216 combinaisons de 3 nombres
de:
mga, mgb, mgc...mgf, mha,mhb...mhf, mja..mjf, mka..mkf,mla..mlf
puis
nga..ngf, nha..nhf, nia..nif...nla..nlf
puis
oga..ogf, oha..ohf.....
puis
pga..pgf, pha..phf...
puis
qga..qgf, qha..qhf, qja..qjf....
puis
rga..rgf...jusqu'à finalement rla..rlf OUF !

Peut-être qu'avec sommeprod, le travail serait plus simple?
si quelqu'un a une idée de formule? merci
BON AM

 

[Tibo]

Re : formule sommeprod ou beaucoup de nb.si?

Salut jj1,

Je viens de te lire et de te relire.

Je crois qu'un petit bout de fichier serait plus facile à comprendre (on est samedi après-midi ...)

A te lire avec un fichier exemple

@+

 

[jeanpierre]

Re : formule sommeprod ou beaucoup de nb.si?

Bonjour JJ1, re le forum,

Puisque tu parles de SOMMEPROD, as-tu essayé avec ?

Il me semble que cela devrait fontionner, mais sur ton texte les nief, bouf, paf et autres xx f, n'aident pas trop à la compréhension...

Ou alors un fichier joint, bien structuré à ta problèmatique...

Bon week-end.

Jean-Pierre

Edit : Salut Tibo....comme moi...

 

[JJ1]

Re : formule sommeprod ou beaucoup de nb.si?

Bonjour Tibo, Jean Pierre
oui on est samedi AM je comprends....Hélàs chez mes parents je n'ai pas excel....
En fait mon probléme est de compter dans la base toutes les combinaisons de ces 18 nombres alignés par 3
je fais un ex concret:
W8:AB8= 1 4 5 8 9 10
K1313= 3 6 8 9 10 12
K1414= 2 4 8 9 10 11

je vais chercher dans ma base le nombre de fois ou il y aura verticalement (en descendant): 2 3 1, puis 2 3 4, puis 2 3 5......puis 2 6 1 , 2 6 4 ... jusqu a 11 12 10
Est ce plus clair?
merci en tout cas de vos réponses

 

[jeanpierre]

Re : formule sommeprod ou beaucoup de nb.si?

Re,

Tu es dans ton truc, donc pour toi c'est clair...

Pour nous, enfin et surtout pour moi, cela ne l'est pas....

Tu es chez tes parents.... Profites-en beaucoup, beaucoup.... Excel n'est que petite chose....

A te lire, plus tard...

Jean-Pierre

 

[JJ1]

Re : formule sommeprod ou beaucoup de nb.si?

Bonsoir à tous, Jean Pierre,
je viens de réaliser un petit exemple pour le comptage des occurences des 244 combinaisons.
Peut-être un somme prod ?
bonne soirée et merci

 

[Monique]

Re : formule sommeprod ou beaucoup de nb.si?

Bonjour,

Je ne sais pas si j'ai compris.
Tu peux essayer, de toute façon.

Ligne par ligne, en DV1 puis copier-coller :
=(NB.SI(A$9:F$14;DQ1)>0)*(NB.SI(A$10:F$15;DR1)>0)*(NB.SI(A$11:F$16;DS1)>0)

En 1 seule formule :
=SOMMEPROD((NB.SI(A$9:F$14;$DQ$1:$DQ$288)>0)*(NB.SI(A$10:F$15;$DR$1:$DR$288)>0)*(NB.SI(A$11:F$16;$DS$1:$DS$288)>0))

 

[JJ1]

Re : formule sommeprod ou beaucoup de nb.si?

Bonsoir Monique, Le Forum
Merci pour ces 2 formules, j'ai essayé ta formule globale qui me donne comme résultat 160.
En fait c'est impossible, car le résultat en colonne DT est à 1 (ou plus, ou 0 si pas dans le fichier) si les 3 nombres en face (ici DQ, DR, DS) sont dans 3 lignes contigües, comme je le montre sur mon "dessin", par ex 1 7 et 13 (en rouge) ne se trouvent qu'une fois à la suite en descendant le fichier A9:F16, donc résultat 1
idem pour 1 7 et 14 (violet)
la formule à "trouver" est donc la colonne DT (la somme de cette colonne se fera en DU1)
merci pour ton aide et bonne soirée

 

[ninbihan]

Re : formule sommeprod ou beaucoup de nb.si?

Bonsoir le Forum, Monique JP Tibo et JJ1

Peux tu préciser si dans la combinaison cible il faut toujours que les éléments soient dans une ligne différente (exemple le 1;7;13 peux t'il etre former de A9;C10;F9) ?

Bonne soirée,

Ninbihan

 

[Monique]

Re : formule sommeprod ou beaucoup de nb.si?

Bonjour,

Pourquoi est-ce que 160 est impossible ?
Il y a 288 lignes

Si on met ça en DV1V288
=(NB.SI(A$9:F$14;DQ1)>0)*(NB.SI(A$10:F$15;DR1)>0)* (NB.SI(A$11:F$16;DS1)>0)
la somme = 160
=SOMME(DV1V288) = 160

Et quand NB.SI = 1, ce sont toujours 3 lignes consécutives

Question : quels sont les critères pour que les 3 chiffres des colonnes DQ, DR et DS soient pris en compte ?
Et puis question 2 : à quel total faut-il arriver ?

 

[Gael]

Re : formule sommeprod ou beaucoup de nb.si?

Bonsoir JJ1, bonsoir à tous,

De mon coté, j'ai compris un peu différemment, il faut compter le nombre de fois ou 1 ligne contient DQ1, la suivante DR1 et la suivante DS1, quelle que soit la position du chiffre dans la ligne.

J'ai rajouté une série de 1 en colonne V que j'utilise comme matrice unité. Pour ontenir une matrice de 0 ou 1 selon que la ligne contient ou non DQ1 par exemple j'utilise:

PRODUITMAT(($A$9.$F$14=$DQ1)*1;$V$9.$V$14), V9.V14 étant la matrice unité ce qui permet d'avoir une matrice d'une seule colonne.

On répète 3 fois l'opération en décalant d'1 ligne et avec DR1 puis DS1, on multiplie les 3 matrices obtenues ce qui donne en final une matrice avec 1 si les 3 chiffres sont présents sur 3 lignes consécutives et 0 sinon. Sommeprod de tout ça donne le total. soit en final:

=SOMMEPROD((PRODUITMAT(($A$9.$F$14=$DQ1)*1;$V$9.$V$14))*(PRODUITMAT(($A$10.$F$15=$DR1)*1;$V$10.$V$15))*(PRODUITMAT(($A$11.$F$16=$DS1)*1;$V$11.$V$16)))

Les ":" ont été remplacés par des "." pour que le message passe correctement.

J'ai modifié quelques chiffres dans la base pour faire des tests et j'ai supprimé dans les colonnes DQ, DR, DS des éléments redondants (commençant par 5 et par 6) car au final on devrait bien avoir 216 combinaisons et non pas 288.

@+

Gael

En fait, la matrice unité doit toujours avoir le même nombre de lignes qu'il y a de colonnes dans les matrices de A à F soit 6 lignes. on peut donc toujours utiliser $V$9.$V$14 ou créer à un autre endroit une colonne de 6 "1" ou remplacer $V$9.$V$14 par {1;1;1;1;1;1} dans la formule.

Gael

 

[JJ1]

Re : formule sommeprod ou beaucoup de nb.si?

Bonsoir Gaël, bonsoir à tous,
C'est exactement ce que je souhaitais, je ne connaissais pas l'association somme prod et produit mat?
En tout cas merci à tous pour votre aide précieuse, il me fallait( 216*3*nb de lignes du fichier) nb.si !!!
D'ailleurs je vous joins le fichier exemple avec la méthode nb.si pour une seule combinaison.(qui fonctionne aussi)
Bonne soirée

 

[Gael]

Re : formule sommeprod ou beaucoup de nb.si?

Bonjour JJ1, bonjour à tous,

En fait Sommeprod travaille avec des matrices de même dimension que l'on peut générer par toute formule permettant d'obtenir des matrices. Dans ce cas, il fallait réduire le résultat d'une matrice 6*6 à une matrice 1*6, j'ai essayé de simplifier mais je n'ai pas trouvé d'autre solution.

Petite précision, avec cette formule, si tu as 2 fois le chiffre 1 dans la première ligne, la séquence 1,7,13 sera comptée 2 fois et le résultat sera 3 au lieu de 2 dans mon exemple. Si tu veux compter la série une seule fois, il faut mettre:

((PRODUITMAT(($A$9.$F$14=$DQ1)*1;$V$9.$V $14)>0*1) de façon à remplacer le 2 éventuel par un 0 ou 1 selon qu'il est >0 ou non.

@+

Gael

 

[JJ1]

Re : formule sommeprod ou beaucoup de nb.si?

Bonjour Gaël, Le forum
Ta formule fonctionne parfaitement.
C'est un bon exemple pour ceux qui demandent s'il vaut mieux une formule ou une macro:
ici,en effet, dès que la plage de recherche grandit (>1000 lignes) Excel se met en recalcul permanent. (j'ai un doublecore 3GHz et 2Go de Ram)
On atteint les limites d'une formule.
Juste pour info.
Bonne journée et merci encore à tous