Formule sommeprod

[JJ1]

Bonjour,

J'ai un souci avec la formule sommeprod, avec laquelle je souhaite compter le nombre de fois où deux nombres sont sur 2 lignes adjacentes dans une plage (ex: 1 en ligne 1 et 6 en ligne 2 ou vice-versa 6 en ligne 1 et 1 en ligne 2)
Ma première partie de formule me donne 0 en décalant vers le bas?
je joins un exemple (le résultat attendu ici est 2 (cases jaune et bleue) .
merci de votre aide.
Bonne soirée à tous.

 

[david84]

Re : Formule sommeprod

Bonjour,
10 se trouve en A2:G2 et en A3:G3.
Doit-on le compter ?

 

[JJ1]

Re : Formule sommeprod

Bonsoir David84,
Dans mon exemple, je ne comptabilise la somme que des 1 et des 6 (saisis en $F$1: $G$1), donc le 10 ne compte pas.
Le résultat en H1 devrait être 2
je joins un nouvel exemple pour montrer les "2 sens" de recherche 1-6 et 6-1, la somme ferait 3 (bleu+jaune+rose).
Merci

 

[david84]

Re : Formule sommeprod

Re,
pas vraiment compris. A tester au cas où

=SOMMEPROD(NB.SI($A1:$E2;$F$1);NB.SI($A1:$E2;$G$1))

Si pas cela, mets-moi manuellement les résultats attendus plutôt que des couleurs partout.
A+

 

[JJ1]

Re : Formule sommeprod

Re,
Le total se fait sur toute la plage.
je joins un autre exemple sans coloriage avec explication (résultat attendu 4 en H1)
Merci à toi.

ps: le comptage se fait uniquement pour des 1 et 6 en lignes adjacentes.

 

[job75]

Re : Formule sommeprod

Bonjour JJ1, David84,

Essayez cette formule en H1 :

=SOMMEPROD(SIGNE(NB.SI(DECALER(A1:E1;LIGNE(1:100)-1;);F1))*SIGNE(NB.SI(DECALER(A2:E2;LIGNE(1:100)-1;);G1)))+SOMMEPROD(SIGNE(NB.SI(DECALER(A1:E1;LIGNE(1:100)-1;);G1))*SIGNE(NB.SI(DECALER(A2:E2;LIGNE(1:100)-1;);F1)))

Il y a donc 2 SOMMEPROD dont on fait la somme.

A+

 

[JJ1]

Re : Formule sommeprod

Bonsoir Job 75
merci pour cette formule, pas si évidente que ça.
Je vais l'adapter à mon fichier.
Merci beaucoup et bonne soirée à tous

 

[job75]

Re : Formule sommeprod

Re,

Dans la formule que j'ai donnée, si les valeurs F1 ou G1 ne peuvent exister qu'une seule fois par ligne, on peut bien sûr supprimer les fonctions SIGNE :

=SOMMEPROD(NB.SI(DECALER(A1:E1;LIGNE(1:100)-1;);F1);NB.SI(DECALER(A2:E2;LIGNE(1:100)-1;);G1))+SOMMEPROD(NB.SI(DECALER(A1:E1;LIGNE(1:100)-1;);G1);NB.SI(DECALER(A2:E2;LIGNE(1:100)-1;);F1))

J'ai aussi remplacé * par ; dans chaque SOMMEPROD.

A+

 

[david84]

Re : Formule sommeprod

Re,
bien vu Job !
On peut peut-être simplifier, mais sous réserve de tests.
sans la fonction signe :

=SOMMEPROD((NB.SI(DECALER(A1:E1;LIGNE(zone)-1;F1)*(NB.SI(DECALER(A2:E2;LIGNE(zone)-1;G1)))+(NB.SI(DECALER(A2:E2;LIGNE(zone)-1;F1)*(NB.SI(DECALER(A1:E1;LIGNE(zone)-1;G1))))

Avec la fonction signe :

=SOMMEPROD((SIGNE(NB.SI(DECALER(A1:E1;LIGNE(zone)-1;F1)*(NB.SI(DECALER(A2:E2;LIGNE(zone)-1;G1))))+SIGNE(NB.SI(DECALER(A2:E2;LIGNE(zone)-1;F1)*(NB.SI(DECALER(A1:E1;LIGNE(zone)-1;G1))))

Cordialement

 

[job75]

Re : Formule sommeprod

Bonjour David84,

En effet, et on peut même simplifier un peu plus en évitant deux -1 :

=SOMMEPROD(NB.SI(DECALER(A1:E1;LIGNE(1:100)-1;);F1)*NB.SI(DECALER([COLOR="Red"]A1:E1[/COLOR];LIGNE(1:100););G1)+NB.SI(DECALER(A1:E1;LIGNE(1:100)-1;);G1)*NB.SI(DECALER([COLOR="Red"]A1:E1[/COLOR];LIGNE(1:100););F1))

Mais on peut aller encore plus loin en insérant une 1ère ligne (fichier joint) :

=SOMMEPROD(NB.SI(DECALER(A1:E1;LIGNE(1:100););F2)*(NB.SI(DECALER(A1:E1;LIGNE(1:100)-1;);G2)+NB.SI(DECALER(A2:E2;LIGNE(1:100););G2)))

A+