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.
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.
Dernière édition:
