Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11

Addition de cellules contenant la formule SOMMEPROD

15 réponses
Avatar
gejoun
Bonjour,

Je cherche depuis un petit moment si il est possible d'additionner des
montants calculés avec des SOMMEPROD, sans tenir compte des autres
lignes, et en définissant une plage (dans mon exemple C1:C18) ; plutôt
qu'en utilisant =somme(C12;C4) ou =C12+C4).

Le but étant de me permettre d'insérer des nouvelles SOMMEPROD sans
avoir à modifier la formule du bas en C19.

Le lien du fichier : https://www.cjoint.com/c/IGypJUTtJem

Est-ce possible ?

Merci !

--
géjoun

10 réponses

1 2
Avatar
gejoun
Le 24/07/2019 à 19:01, Jacquouille a écrit :
Bonjour
Chez moi, mon vieux 2003 me donne ceci:
=SOMMEPROD(A5:A18;B5:B18)  ----->5195

Bonjour,
C'est pas le résultat de la sommeprod que je recherche ;-)
--
géjoun
Avatar
MichD
La formule matricielle doit se lire comme suit, adapte la plage de
cellules selon ton environnement.
=SOMME(SI(CHERCHE("SommeProd";FORMULETEXTE(A1:A10))>0;A1:A10))
MichD
Avatar
MichD
Le 30/07/19 à 06:21, MichD a écrit :
La formule matricielle doit se lire comme suit, adapte la plage de
cellules selon ton environnement.
=SOMME(SI(CHERCHE("SommeProd";FORMULETEXTE(A1:A10))>0;A1:A10))
MichD

J'ai fait une petite vérification, et la fonction "FormuleTexte" est
apparue dans la version Excel 2013.
MichD
Avatar
MichD
La formule la plus complète afin d'éviter les N/A si la plage ne
contient pas au moins une formule "SommeProd" :
Adapte la plage de cellules.
Toujours une validation matricielle : Ctrl + Maj + Enter
=SIERREUR(SOMME(SI(NON(ESTNA(CHERCHE("SommeProd";FORMULETEXTE(A1:A10))>0));A1:A10));0)
MichD
Avatar
gejoun
Le 30/07/2019 à 12:18, MichD a écrit :
***** Simplement, si les formules "SommeProd" sont dans une autre
colonne que celle où tu saisis les données, tu n'aurais qu'à faire
l'addition de cette colonne pour avoir le résultat.

D'accord. Oui c'est tout bête et ça pourrait fonctionner, mais ça me
rajoute encore des colonnes dans un tableau déjà chargé :-(
Si tu tiens à utiliser une formule, il y a ceci. La fonction
"FORMULETEXTE" sauf erreur, existe seulement depuis Excel 2016. Si tous
tes usagers n'utilisent pas cette version ou plus récent, cela ne
fonctionnera pas.

On est tous avec Office 365 donc pas de pb de ce côté là.
Validation Matricielle : Maj + Ctrl + Enter
=SOMME(SI(CHERCHE("SommeProd";FORMULETEXTE(A1:A10))>0;A4:A6;9))
Pour le type de fichier .xlsm, dans les options d'Excel / enregistrement
/ tu peux choisir le type de fichier .xlsx ou .xlsm par défaut lors de
l'enregistement par l'usager. Ainsi, tous les fichiers seront au format
.xlsm. La macro fonctionne sur toutes les versions d'Excel.

Mais pour ça, il faut régler ce paramètre poste par poste la première
fois ? Cette valeur n'est pas enregistrée dans le fichier mais bien dans
le poste de travail ?
--
géjoun
Avatar
gejoun
Le 30/07/2019 à 12:24, MichD a écrit :
J'ai fait une petite vérification, et la fonction "FormuleTexte" est
apparue dans la version Excel 2013.

Ok, merci pour la précision.
--
géjoun
Avatar
gejoun
Le 30/07/2019 à 13:18, MichD a écrit :
La formule la plus complète afin d'éviter les N/A si la plage ne
contient pas au moins une formule "SommeProd" :
Adapte la plage de cellules.
Toujours une validation matricielle : Ctrl + Maj + Enter
=SIERREUR(SOMME(SI(NON(ESTNA(CHERCHE("SommeProd";FORMULETEXTE(A1:A10))>0));A1:A10));0)

Je l'ai rajouté sur le tableau, et elle m'additionne toutes les cellules
: https://www.cjoint.com/c/IGEnlMFJVRm
J'ai loupé un truc ?
Merci !
--
géjoun
Avatar
MichD
Le 30/07/19 à 09:11, gejoun a écrit :
Le 30/07/2019 à 13:18, MichD a écrit :
La formule la plus complète afin d'éviter les N/A si la plage ne
contient pas au moins une formule "SommeProd" :
Adapte la plage de cellules.
Toujours une validation matricielle : Ctrl + Maj + Enter
=SIERREUR(SOMME(SI(NON(ESTNA(CHERCHE("SommeProd";FORMULETEXTE(A1:A10))>0));A1:A10));0)

Je l'ai rajouté sur le tableau, et elle m'additionne toutes les cellules
: https://www.cjoint.com/c/IGEnlMFJVRm
J'ai loupé un truc ?
Merci !

Je ne suis résolu à tester la formule et j'en suis arrivé à celle-là.
Validation matricielle : Maj+ Ctrl + Enter
=SOMME(SI(NON(ESTERREUR(NON(ESTNA(CHERCHE("SommeProd";FORMULETEXTE(C1:C18))))*CHERCHE("SommeProd";FORMULETEXTE(C1:C18))>0)*C1:C18);C1:C18))
MicD
Avatar
MichD
L'inactivité aidant, on finit par perdre la main!
;-)
On peut réduire la formule à ceci :
=SOMME(SI(NON(ESTERREUR(CHERCHE("SommeProd";FORMULETEXTE(C1:C18))>0)*C1:C18);C1:C18))
MichD
Avatar
MichD
Explication de la formule :
=SOMME(SI(NON(ESTERREUR(CHERCHE("SommeProd";FORMULETEXTE(C1:C18))>0)*C1:C18);C1:C18))

FORMULETEXTE(C1:C18) retourne le texte de chacune des cellules de la
formule dans la colonne c1:C18
=(CHERCHE("SommeProd";FORMULETEXTE(C1:C18))>0) : Recherche l'expression
"SommeProd" dans chacune des formules des cellules. Lorsque l'expression
est présente, la fonction "Recherche" retourne la position de la
première lettre de "Sommeprod" dans le texte de chacune des cellules.
Par conséquent, cette section de l'équation retourne "VRAI" pour chacune
des cellules où la fonction "Sommeprod" est présente. Lorsque
l'expression n'est pas présente, la fonction "Recherche" retourne #Valeur!
=(CHERCHE("SommeProd";FORMULETEXTE(C1:C18))>0)*C1:C18
Ceci permet de multiplier le résultat "Vrai" obtenu précédemment par la
valeur des cellules de la plage c1:C18.
Pour additionner toutes les lignes de la colonne, il faut trouver un
moyen d'éliminer ces valeurs d'erreur, car, dès que l'on a dans une
plage à sommer une valeur d'erreur, le résultat sera #Valeur!. C'est ce
que fait cette section de la formule NON(ESTERREUR( afin de retenir
seulement les cellules contenant une valeur numérique pour les additionner.
La fonction matricielle permet à Excel d'utiliser une colonne non
visible en mémoire vivre et retenir pour chaque ligne seulement les
valeurs numériques que la fonction "somme" additionne. Cela rend la
compréhension de ces formules plus abstraites.
MichD
1 2