SOMMEPROD et opérateurs logiques

[SkyCorp]

Bonjour à tous,

Je cherche à optimiser une formule du type SOMMEPROD.
Mon dossier Excel est notamment constitué de 2 feuilles, l'une disposant des données et la 2ème effectuant les calculs.
Sur la feuille de calcul, un des tableaux est constitué de la manière suivante :
* La première ligne donne l'année de vente d'un objet ("année", colonne B de la feuille de données et ligne 1 de la feuille de calcul)
* La première colonne donne le noms des pays de vente d'un objet ("pays 1", colonne C de la feuille de données et colonne A de la feuille de calcul)
* La 2nde colonne donne le noms des pays de production d'un objet ("pays 2", colonne D de la feuille de données et colonne B de la feuille de calcul).
* Le reste du tableau est évalué par la formule cherchée
* La colonne A de la feuille de données donne le nom d'un objet ("objet"), il s'agit de la colonne de référence de mon tableau, mais n'est pas triée.

Mon objectif est de déterminer le nombre d'objets par année pour un pays de vente et un pays de production. Comme une recherche est également faite sur un objet particulier ("objet_A") à la place des pays de production, la colonne B de la feuille de calcul contient également le nom de cet objet

La formule actuelle est la suivante (pour la case C2), avec quelques couleurs pour une meilleure lisibilité :
SI(
ESTNUM(TROUVE("objet_A";$B2));
SOMMEPROD((feuilledonnée!$A$5:$A$200="objet_A")*(feuilledonnée!$B$5:$B$200=C$1)*(feuilledonnée!$C$5:$C$200=$A2));
SOMMEPROD((feuilledonnée!$A$5:$A$200<>"objet_A")*(feuilledonnée!$B$5:$B$200=C$1)*(feuilledonnée!$C$6:$C$243=$A2)*(feuilledonnée!$D$5:$D$200=SI($B2="pays 2";"pays_2";SI($B2="national";$A2;"xxx"))))
)

Cette formule fonctionne tel quel. Cependant il ne s'agit pas tout à fait de la formule définitive. Le but est de modifier "pays_2" en "pays_2.1" OU "pays_2.2" ("xxx" sera modifié en conséquence pour traiter les autres pays).

Une solution qui fonctionne est la suivante si on ne prend pas en compte les autres pays (donc sans la condition "SI") :
SOMMEPROD((feuilledonnée!$A$5:$A$200<>"objet_A")*(feuilledonnée!$B$5:$B$200=C$1)*(feuilledonnée!$C$5:$C$200=$A2)*((feuilledonnée!$D$5:$D$200="pays_2.1")+(feuilledonnée!$D$5:$D$200="pays_2.2")))
Mais prendre en compte les autres situations finira par rendre la formule très compliquée et difficilement lisible (ce qui est déjà un peu le cas).

Par ailleurs, j'ai déjà tenté d'utiliser :
*(SI($B2="pays 2";(feuilledonnée!$D$5:$D$200="pays_2.1")+(feuilledonnée!$D$5:$D$200="pays_2.2");SI($B2="national";(feuilledonnée!$D$5:$D$200=$A2);"xxx")))
mais le calcul me fournit "0" à tous les coups.
J'ai également tenté SI($B2="pays 2";OU("pays_2.1";"pays_2.2");... et là j'obtiens "#VALEUR!".

Plus aucune solution simple ne me vient à l'esprit et je serais ravi d'avoir un peu d'aide ici. N'ayant découvert la fonction SOMMEPROD que très récemment, celà provient peut-être de là.
Si quelqu'un voit le moyen de réduire la complexité de la formule, je suis également preneur.


PS: désolé pour la taille de ce message, j'ai tenté de mettre toutes les infos pour expliquer la situation.

 

[Excel-lent]

Re : SOMMEPROD et opérateurs logiques

Bonsoir et bienvenu sur le forum SkyCorp,

Pas de soucis pour la longueur du post, l'important c'est la clarté!

Justement à propos de clarté, si tu pouvais mettre ton fichier nettoyé des informations personnels, ce serait plus facile à comprendre (et plus court à expliquer)

Sinon, en attendant... pour rendre ta formule plus clair, tu peux faire :
-> sélectionner l'onglet : "feuilledonnée"
-> sélectionner les données de la colonne A (sans le titre de colonne - soit sauf erreur : A5:A200)
-> "insertion" / "nom" / "définir..." / dans la zone "noms dans le classeur" : PaysVente
Tu noteras que je n'ai pas mis (volontairement) d'espace pour le nom des cellules sélectionnées, car c'est impossible!

-> sélectionner les données de la colonne B (sans le titre de colonne - soit sauf erreur : B5:B200)
-> "insertion" / "nom" / "définir..." / dans la zone "noms dans le classeur" : PaysProduction

Idem pour tes autres zones de cellules.

Ta formule :

SI(
ESTNUM(TROUVE("objet_A";$B2));
SOMMEPROD((feuilledonnée!$A$5:$A$200="objet_A")*(feuilledonnée!$B$5:$B$200=C$1)*(feuilledonnée!$C$5:$C$200=$A2));
SOMMEPROD((feuilledonnée!$A$5:$A$200<>"objet_A")*(feuilledonnée!$B$5:$B$200=C$1)*(feuilledonnée!$C$6:$C$243=$A2)*(feuilledonnée!$D$5:$D$200=SI($B2="pays 2";"pays_2";SI($B2="national";$A2;"xxx"))))
)

Deviendra donc :

SI(
ESTNUM(TROUVE("objet_A";$B2));
SOMMEPROD(([COLOR="Blue"][I]PaysVente[/I][/COLOR]="objet_A")*([COLOR="Red"][I]PaysProduction[/I][/COLOR]=C$1)*(feuilledonnée!$C$5:$C$200=$A2));
SOMMEPROD(([COLOR="Blue"][I]PaysVente[/I][/COLOR]<>"objet_A")*([COLOR="Red"][I]PaysProduction[/I][/COLOR]=C$1)*(feuilledonnée!$C$6:$C$243=$A2)*(feuilledonnée!$D$5:$D$200=SI($B2="pays 2";"pays_2";SI($B2="national";$A2;"xxx"))))
)

Qui sans simplifier la formule, la rend plus lisible et facile à comprendre.

Et le sera encore plus lorsque tu auras fais de même avec les autres zones :
-> feuilledonnée!$C$5:$C$200
-> feuilledonnée!$D$5:$D$200
-> ...

Bon WE

Edition : merci beaucoup James007. La gentillesse de tes mots font mouche à chaque fois! Tu es le digne remplaçant de notre très regretté James Bond 007

 

[James007]

Re : SOMMEPROD et opérateurs logiques

Bonjour SkyCorp et Excel-lent,

Comme toujours, la réponse d'Excel-lent est à afficher au panthéon des meilleures réponses du Forum ...

SkyCorp, une suggestion périphérique : lorsqu'on goûte à la douceur, et à la très grande du Tableau Croisé Dynamique ... on y revient ...

A +

 

[SkyCorp]

Re : SOMMEPROD et opérateurs logiques

Merci pour les conseils .

J'ai mis un peu de temps pour mettre en forme le fichier mais le voici à présent.

Le tableau de calculs commence un peu plus bas qu'indiqué précédemment (de A7 à N36) en raison de lignes de tests que j'ai déjà effectués (au passage, supprimer la 1ère, 2nde ou 3ème ligne modifie le calcul de la ligne 5 mais je ne comprends pas vraiment pourquoi).
En dessous de ce tableau, les valeurs présentes les données devant être trouvées.

Concernant les tableaux croisés dynamiques, je n'en avais jamais réellement utilité jusqu'à présent et n'ai donc jamais vraiment appris à les utiliser mais je vais me renseigner à ce sujet. Il faut surtout que je sois sûr que d'autres personnes puissent remplir et utiliser cette base, même sans avoir beaucoup de connaissances sur Excel.

J'espère que c'est plus clair à présent

 

[James007]

Re : SOMMEPROD et opérateurs logiques

Bonjour,

Exemple de TCD fait en quelques secondes ...

A +

 

[SkyCorp]

Re : SOMMEPROD et opérateurs logiques

Merci beaucoup pour ta suggestion James007.

Néanmoins je trouve ce système un peu compliqué à utiliser au premier abord, et sachant que des graphs devront être facilement et rapidement créés, que cette page regroupera d'autres tableaux de calculs et connaissant les autres personnes responsables de cette base, ce système ne sera sans doute pas évident à appliquer.

De plus, je cherche à regrouper plusieurs paramètres. Par exemple "EU" et "US / EU" devront ne former qu'un seul bloc "EU", tout comme "Asia", "CD", "CH" ... regroupé en un bloc "Other".

Bien entendu, ne maîtrisant pas encore les TCD, je m'avance peut-être un peu vite, mais c'est mon impression de départ.

 

[James007]

Re : SOMMEPROD et opérateurs logiques

Seule l'expérience personnelle compte ...

Mais entre nous, tu ne cites que des raisons supplémentaires pour utiliser le TCD ...
1. Souplesse immense .... tu peux jouer avec les entités en ligne en colonne
il suffit d'essayer
2. Utilisation simplifiée pour d'autre utilisateurs ... il suffit d'utiliser les listes déroulantes de chaque entité
2. Et cerise sur le gâteau ... tu as beaucoup de chances ... les graphiques des TCD ... sont ... automatiques ...!!!

A +

 

[SkyCorp]

Re : SOMMEPROD et opérateurs logiques

Je vais me renseigner alors. Merci pour tout.

Sinon, pour ma culture générale, n'existe-t-il aucun moyen d'associer des conditions logiques à un SOMMEPROD ?
Et quelqu'un sait-il pourquoi la suppression des 1ères lignes de la feuille calcul entraîne une erreur pour la ligne 5 ?

 

[James007]

Re : SOMMEPROD et opérateurs logiques

Sinon, pour ma culture générale, n'existe-t-il aucun moyen d'associer des conditions logiques à un SOMMEPROD ?

Pour info, sommeprod() ne fait que çà .... combiner des matrices vrai faux
pour compter les éléments cherchés ...

A +

 

[SkyCorp]

Re : SOMMEPROD et opérateurs logiques

Dans ce cas, j'ai du mal à comprendre pourquoi ma formule en ligne 5 ne fonctionne pas, contrairement à la ligne 4 . Je suis un peu perdu.

 

[James007]

Re : SOMMEPROD et opérateurs logiques

Dans ce cas, j'ai du mal à comprendre pourquoi ma formule en ligne 5 ne fonctionne pas, contrairement à la ligne 4 . Je suis un peu perdu.

Pour une réponse précise, il ne faut pas que tu hésites à poster ton fichier ou un bout significatif ...

A +

 

[SkyCorp]

Re : SOMMEPROD et opérateurs logiques

Le fichier joint précédemment ne suffit pas ? Les lignes dont je parle sont les premières lignes de la feuille "Calculs"

 

[James007]

Re : SOMMEPROD et opérateurs logiques

Le fichier joint précédemment ne suffit pas ? Les lignes dont je parle sont les premières lignes de la feuille "Calculs"

Désolé, j'ai totalement zappé le fait que tu avais posté un fichier temp ...
Je l'ouvre pour regarder ...

A +

 

[SkyCorp]

Re : SOMMEPROD et opérateurs logiques

Pas de problème et merci pour ton aide

 

[James007]

Re : SOMMEPROD et opérateurs logiques

Je n'arrive pas à deviner le changement de logique entre la ligne 9 et 10 ...

Peux-tu me l'expliquer ...?

A +