Esterreur !!

[Calvus]

Bonsoir,

Après avoir lu quasi tous les posts sur ce sujet dans le forum, je ne trouve toujours pas la solution à mon problème.

Comment imbriquer sommeprod et esterreur ?

J'ai testé plusieurs formules, dont les deux suivantes, sans succès.
Celles-ci ont le mérite de fonctionner s'il n'y a pas de condition d'erreur.

Condition d'erreur à mettre en B2 dans le fichier joint, taper une lettre par exemple.

=SOMMEPROD(SI(NON(ESTERREUR(B2)*1);(A1:A3="a")*(B1:B3)))

=SI(NON(ESTERREUR(B2));SOMMEPROD((A1:A3="a")*(B1:B3)))

Pour ceux qui auront la gentillesses de répondre, pourrez vous expliquer la procédure, car c'est ce qui est difficile dans les forums, c'est d'essayer de comprendre les messages avec toutes les imbrications de formules, qui restent du chinois pour quelqu'un comme moi.

Par ailleurs, j'ai cherché assez vainement sur le forum des explications simples sur les formules, mais c'est très vite très technique, et l'on est rapidement perdu, alors que je comprends que ce soit l'évidence même pour un initié.

Merci d'avance.

 

[Excel-lent]

Re : Esterreur !!

Bonsoir Calvus,

C'est vrai, je le reconnais, moi le premier, lorsque je sens que l'interlocuteur est d'un bon niveau, et apte à comprendre la réponse (voir l'adapter - quand sa question est trop général) "brute de décoffrage", je ne donne pas beaucoup d'explication.

Ainsi, j'ai plus de temps pour donner des réponses détaillés à ceux qui en ont besoin

Une solution :

=[COLOR="Red"][B]SI([/B][/COLOR]ESTERREUR(B2)[COLOR="Red"][B];[/B][/COLOR]""[COLOR="Red"][B];[/B][/COLOR][COLOR="Blue"][B]SOMMEPROD((A1:A3="a")*(B1:B3))[/B][/COLOR])

Détails :
=SI(condition;lorsque la condition est vrai faire cela;lorsque la condition est fausse faire ceci)

-> la condition : ESTERREUR(B2)
si la cellule B2 renvoi un "message d'erreur excel" : par exemple #VALEUR

-> lorsque la condition est vrai faire cela : ""
ne rien écrire dans la cellule où se trouve la formule

-> lorsque la condition est fausse faire ceci : SOMMEPROD((A1:A3="a")*(B1:B3))
rechercher dans les cellules de A1 à A3 toutes les cellules égale à "a"
prendre chaque valeur correspondantes (sur la même ligne) se trouvant dans la colonne B et les additionner entre elles pour afficher le total dans la cellule où se trouve la formule
Il est donc TRES important que tes deux zones A1:A3 et B1:B3 soient en face l'une de l'autre. Tu ne peut pas avoir A1:A10 et B5:B15 (elles sont de même taille, c'est vrai - c'est un bon début - mais ne sont pas en face)!

C'est suffisamment détaillé? Sinon n'hésite pas.

j'ai cherché assez vainement sur le forum des explications simples sur les formules

N'hésite pas à t'insérer dans un fil, pour demander plus de détail au sujet d'une explication/réponse (seul truc à éviter, poser une autre question à l'intérieur d'un fil déjà existant)!

Bon dimanche

 

[Excel-lent]

Re : Esterreur !!

Re,

Si je peux me permettre...

Plus de détail :
1. La fonction RECHERCHEV() ou RECHERCHEH() permet de chercher quelque-chose dans une zone et renvoyer le texte ou nombre se trouvant sur la même ligne sur la xième colonne.

Très utile quand il n'y a pas de doublons.

Si comme dans ton cas, il y a des doublons et que tu souhaite TOUS les trouver et additionner ces fameux nombre entre eux, tu peux utiliser SOMMEPROD() comme tu l'as fait, ou SOMME.SI()

2. La formule SOMME.SI() est utilisé lorsque tu veux faire le total de plusieurs montants (comme dans ton cas) selon UN critère : chercher les valeurs "a"

Ce qui donne dans ton cas :
=SI(ESTERREUR(B2);"";SOMME.SI(A1:A3;"a";B1:B3))

Explication de la formule SOMME.SI()
Faire la somme de tous les montant en B1:B3, lorsque sur la ligne correspondant, dans la colonne A (zone A1:A3), la valeur de la cellule est "a"
=SOMME.SI(zone où la condition doit être rempli;ta condition;zone où il faut prendre les chiffres pour les additionner)

3. La formule SOMMEPROD() est utilisé lorsque tu veux faire le total de plusieurs montants selon PLUSIEURS critères.

Mais comme tu as pu t'en rendre compte, SOMMEPROD fonctionne très bien, même lorsqu'il n'y a qu'un seul critère.

Alors que la fonction SOMME.SI ne fonctionne QUE si il n'y a qu'un seul critère.

Exemple de formule SOMMEPROD avec plusieurs critères :
=SOMMEPROD((A1:A3="a")*(G1:G3>5)*(B1:B3))

En rouge et bleu : les critères. Nous les reconnaissons car il y a une zone de cellule : zone 1 et zone 2 et la condition à réaliser : la condition 1 et la condition 2

En vert la zone de cellule où il faut effectuer la somme. Nous la reconnaissons car elle est à la fin et n'a pas de condition.

Chaque condition et la zone où faire la somme est séparé par le signe *

Bon dimanche

 

[Calvus]

Re : Esterreur !!

Bonjour,

Tout d'abord, un GRAND GRAND MERCI à Excel-Lent.
Je suis très touché que tu aies pris tout ce temps pour détailler toutes ces explications et si rapidement.
Cela m'a permis d'étudier ces détails, et je passerai encore du temps à essayer de comprendre les finesses.

Malgré cela, je n'arrive pas encore à obtenir ce que je souhaite. Je suis un cas désespéré !
Mais il me semble également que mon 1er message n'était pas suffisamment clair sur ce que je souhaitais.

Je renvois donc deux fichiers.

Test 1, l'exemple précédant, simple pour pouvoir justement détailler les étapes de calcul.

Test 2, l'exemple réel de ce que je souhaite faire et qui me pose problème.

J'ai mis des annotations pour souligner les points gênants.
Dans cette feuille, les formules concernées sont en col T.

T11 à 13, fonctionnent mais à double rallonge !
T25 à 27, mieux, mais encore trop long et compliqué, sur qu'on peut faire mieux.
T28, rien ne va plus !!

Je veille devant mon PC dans l'attente d'éclaircissements.

Merci

PS: Excel-Lent, tu vas te moquer car tu vas probablement voir que c'est évident, alors que moi je passe devant depuis des heures sans trouver...

 

[Tibo]

Re : Esterreur !!

Bonjour,

Un essai avec une formule matricielle en T11 :

=SOMME(SI($A$4:$A$63=$S11;$B$4:$Q$63))

Formule matricielle à valider par CTRL + MAJ + ENTREE

à recopier vers le bas et également en T25 et suivantes.

Je te laisse tester

@+

 

[Excel-lent]

Re : Esterreur !!

Bonjour Calvus,

tu vas te moquer

Non, c'est pas mon genre!

Tout d'abord, au vue de ton second post, j'ai compris que j'avais lu trop vite ton 1er post.

Avant de m'attaquer au second, je vais donc rectifier le tir concernant le 1er post.

Regarde la pièce ci-jointe :
-> ligne 1 à 3 : tes essais
-> ligne 11 à 20 : démonstration par l'exemple de mon premier post
-> ligne 21 à 35 : une nouvelle proposition, mais à mon avis, elle ne correspond pas à ta demande.
-> ligne 36 à 47 : quelques remarques générales
-> ligne 48 à 57 : une nouvelle proposition qui sauf erreur correspond à tes attentes.

Je te laisse étudier tout cela pendant que je regarde ton nouveau fichier : TEST 2.

Edition : pas vue, bonjour Tibo. Tu m'évites l'étude du fichier TEST 2!

Bon dimanche à tous

 

[Calvus]

Re : Esterreur !!

Eureka !!

Je crois avoir trouvé pour une partie du problème. Pas forcément compris tout ce que j'ai fait, mais ça a l'air de fonctionner pour le fichier Test 1.
Formule : =SOMMEPROD(SI(ESTNUM(B1:B3);(A1:A3="a")*(B1:B3)))
à valider par Ctrl Maj Entrée.

Alors pourquoi ça marche en matricielle et pas autrement je sais pas encore mais je cherche.

 

[Tibo]

Re : Esterreur !!

re,

Un SOMMEPROD que tu es amené à valider matriciellement (CTRL + MAJ + ENTREE) pour que ta formule donne le bon résultat peut être remplacé par un =SOMME(SI(... matriciel.

Comme indiqué en MP, je ne m'étendrais pas trop sur les explications pour les formules matricielles, de peur de dire des bêtises.

@+

 

[Excel-lent]

Re : Esterreur !!

Bonjour le fil,

pourquoi ça marche en matricielle et pas autrement

???

Pourtant la solution 5 : =SOMME.SI(A49:A51;"a";B49:B51)
fonctionne non?

Et pourtant, ce n'est pas une formule matricielle!

A moins que je n'ai pas testé tout les cas d'erreur possible?
(test à effectuer lignes 49 à 51).

Bon dimanche

 

[Calvus]

Re : Esterreur !!

Merci énormément à tous les deux.

Excel-Lent, formidable travail que tu as accompli pour me venir en aide.

Bonjour le fil,



???

Pourtant la solution 5 : =SOMME.SI(A49:A51;"a";B49:B51)
fonctionne non?



Bon dimanche

En écrivant mon message, je n'avais pas encore pris connaissance des vôtres, c'est pour ça que je disais que ça ne fonctionnait qu'en matriciel.

La solution 5 fonctionne donc, mais pas pour le fichier Test 2.

Sur ce dernier, la formule de Tibo fonctionne, et j'ai été très surpris de voir que la mienne également.
Ce qui permet de voir qu'on peut avoir au moins deux solutions pour arriver au même résultat.

Formule Calvus, matricielle
1: =SOMMEPROD(SI(ESTNUM(B4:B60);(A4:A60="alain")*(B4:Q60)))

Formule Tibo, matricielle
2: =SOMME(SI($A$4:$A$63="alain";$B$4:$Q$63))

Je suis donc très content de m'être débrouillé tout seul pour l'une d'elles.
Mais contrairement à vous, je cherche plutôt de façon intuitive que réfléchie.
Il me reste donc maintenant à essayer de comprendre ce que j'ai fait, ainsi que ce que vous m'avez envoyé.
Je vais pouvoir m'entraîner dessus.

Encore une fois, merci mille fois à tous les deux.
J'espère être capable un jour de vous rendre la pareille.

Cordialement

Calvus