Comment tenir compte d'une partie d'une cellule dans SOMMEPROD()?

[Aloha]

Bonjour,

Je m'explique: Dans une formule SOMMEPROD() il y a un certain nombre de critères, comme p.ex. un code, une chaîne de lettres etc. Maintenant, il faut que j'ajoute un critère consistant à rechercher si dans une cellule il y a le mot "sud", pour exclure le nord, l'est et l'ouest.

Comment faire pour que Excel tienne compte de toutes les lignes où "sud" apparaît dans une certaine cellule?

J'ai fabriqué un exemple où il y a une formule SOMMEPROD() qui contient un argument "(C2:C6 contient "sud")" qu'il s'agit d'exprimer en termes d'Excel.

Merci pour toute aide

Aloha

 

[tototiti2008]

Re : Comment tenir compte d'une partie d'une cellule dans SOMMEPROD()?

Bonjour Aloha,

un essai

=SOMMEPROD((B2:B6=154)*(F2:F6="interne")*(ESTNUM(CHERCHE("sud";C2:C6)))*D2:D6)

 

[Monique]

Re : Comment tenir compte d'une partie d'une cellule dans SOMMEPROD()?

Bonjour,

A essayer :

[FONT=Verdana]=SOMMEPROD((B2:B6=154)*(F2:F6="interne")*(ESTNUM(CHERCHE("Sud";C2:C6)));D2:D6)[/FONT]

 

[Aloha]

Re : Comment tenir compte d'une partie d'une cellule dans SOMMEPROD()?

Re,

Merci beaucoup à vous deux qui me proposez tous les deux exactement la même solution, ce qui me semble une garantie que c'est la bonne solution!

Merci

Aloha

 

[ROGER2327]

Re : Comment tenir compte d'une partie d'une cellule dans SOMMEPROD()?

Bonjour à tous

(...)

Merci beaucoup à vous deux qui me proposez tous les deux exactement la même solution, ce qui me semble une garantie que c'est la bonne solution!

(...)

... sauf que les deux formules sont très différentes...
Voyez le classeur joint.​

ROGER2327
#4297

Dimanche 8 Haha 138 (Fête du Haha, SS)
22 Vendémiaire An CCXIX
2010-W41-3T10:11:52Z

 

[tototiti2008]

Re : Comment tenir compte d'une partie d'une cellule dans SOMMEPROD()?

Bonjour Roger,

Excellente remarque
La partie "à calculer" du SOMMEPROD (ici D2 à D6), est-ce toujours mieux de la mettre après un ";" ?

 

[ROGER2327]

Re : Comment tenir compte d'une partie d'une cellule dans SOMMEPROD()?

Re...

Bonjour Roger,

Excellente remarque
La partie "à calculer" du SOMMEPROD (ici D2 à D6), est-ce toujours mieux de la mettre après un ";" ?

Bonjour tototiti2008

Je vais être très-clair : Ça dépend...​

Votre formule à l'avantage de renvoyer une erreur si une donnée est non numérique.
Celle de Monique a le mérite d'ignorer les données non numériques.
La seule question qui se pose est : "Que veux-je faire ?"...​

ROGER2327
#4300

Dimanche 8 Haha 138 (Fête du Haha, SS)
22 Vendémiaire An CCXIX
2010-W41-3T10:31:27Z

 

[tototiti2008]

Re : Comment tenir compte d'une partie d'une cellule dans SOMMEPROD()?

Re,

en tout cas toujours bon à savoir 😉

Merci

 

[Aloha]

Re : Comment tenir compte d'une partie d'une cellule dans SOMMEPROD()?

Re,

très différente? La formule en elle est si peu différente que je n'avais pas remarqué la différence.

Je n'avais pas remarqué qu'il y avait un ; et j'ai mis un * dans ma vraie formule, et elle marche quand-même!


Je savais que les ; sont une alternative aux * dans les formules SOMMEPROID(), mais j'ignorais qu'on peut mélanger les deux.

Des fois j'ai le problème qu'une formule ne fonctionne pas et lorsque j'en trouve la raison, c'est qu'une personne a fait une mauvaise saisie (p.ex. un . au lieu de , dans un chiffre) ou qu'une cellule apparemment vide ne l'est pas aux yeux d'Excel (je dois dire que je n'ai jamais compris en quoi elle n'était pas vide).

Est-ce qu'il y a une préférence pour * ou ; pour éviter ce genre de problèmes, resp. y a-t-il d'autres combines pour éviter cela?

Bonne après-midi

Aloha

 

[Monique]

Re : Comment tenir compte d'une partie d'une cellule dans SOMMEPROD()?

Bonjour ou re,

Pour ma part, c'est le point-virgule systématique,
qu'il y ait ou non du texte dans la plage à sommer,
quitte à ajuster par la suite.

Ne fonctionne pas (avec ou sans texte en D):

[FONT=Verdana]=SOMMEPROD((B2:B6=154);D2:D6)[/FONT]

Celle-ci renvoie #VALEUR! s'il y a du texte en D2: D6, fonctionne s'il n'y a que des valeurs numérique ou des cellules vides

[FONT=Verdana]=SOMMEPROD((B2:B6=154)*D2:D6)[/FONT]

Celle-là va bien, qu'il y ait ou non du texte

[FONT=Verdana]=SOMMEPROD((B2:B6=154)[COLOR=blue]*1[/COLOR];D2:D6)[/FONT]

Il faut rendre numérique le résultat de (B2:B6=154)

=[FONT=Verdana]SOMMEPROD(({FAUX;VRAI;FAUX;VRAI;VRAI})[COLOR=blue]*1[/COLOR];D2:D6)[/FONT]

(*1 ou +0 ou /1, etc.)

 

[ROGER2327]

Re : Comment tenir compte d'une partie d'une cellule dans SOMMEPROD()?

Re...

(...)
Des fois j'ai le problème qu'une formule ne fonctionne pas et lorsque j'en trouve la raison, c'est qu'une personne a fait une mauvaise saisie (p.ex. un . au lieu de , dans un chiffre) ou qu'une cellule apparemment vide ne l'est pas aux yeux d'Excel (je dois dire que je n'ai jamais compris en quoi elle n'était pas vide).

Est-ce qu'il y a une préférence pour * ou ; pour éviter ce genre de problèmes
(...)

Je croyais avoir déjà répondu à cela.
Supposons que le séparateur décimale soit, pour vous, la virgule et qu'un opérateur saisisse 3.5.
Avec la formule de Monique, vous ne vous en apercevrez pas. La donnée sera ignorée.
Avec la formule de tototiti2008, vous en serez informé puisque sa formule renverra #VALEUR!.
Par conséquent, si votre idée est de forcer la saisie de données exclusivement numériques, vous devez opter pour cette dernière formule.
Si, au contraire, vous souhaitez autoriser l'opérateur à saisir autre chose que des nombres, par exemple un texte genre "donnée indisponible" pour une donnée inconnue, et que vous souhaitez que le calcul s'effectue malgré tout sur les données disponibles, vous choisirez la première formule.

Finalement :

La seule question qui se pose est : "Que veux-je faire ?"...

(...)
y a-t-il d'autres combines pour éviter cela?
(...)

Probablement. Toute la difficulté réside dans la définition exacte des types de données qu'on accepte ou qu'on refuse.​

ROGER2327
#4301

Dimanche 8 Haha 138 (Fête du Haha, SS)
22 Vendémiaire An CCXIX
2010-W41-3T13:05:51Z

 

[Aloha]

Re : Comment tenir compte d'une partie d'une cellule dans SOMMEPROD()?

Bonjour,

Un merci tardif pour l'aide et pour les bons conseils

Aloha