Aide pour réduire une formule excel
gl0ugl0u Messages postés 41 Date d'inscription Statut Membre Dernière intervention -
Bonjour à tous,
A nouveau je fais appel au forum cette fois-ci pour m'aider à réduire une formule qui en serait interminable si je devais l'écrire bêtement en fonction des conditions (en jaune) comme je l'ai fait pour le moment juste en fonction de la périodicité. Le résultat de la formule est en vert
=SI(OU(B$14="";$A$22="");"";SOMME(SOMME.SI.ENS(ZSHIP_PS!$E:$E;ZSHIP_PS!$F:$F;"PCE";ZSHIP_PS!$N:$N;B$1);SOMME.SI.ENS(ZSHIP_PS!$E:$E;ZSHIP_PS!$F:$F;"PCE";ZSHIP_PS!$O:$O;B$1);SOMME.SI.ENS(ZSHIP_PS!$E:$E;ZSHIP_PS!$F:$F;"PCE";ZSHIP_PS!$M:$M;B$1);SOMME.SI.ENS(ZSHIP_PS!$E:$E;ZSHIP_PS!$F:$F;"PCE";ZSHIP_PS!$Y:$Y;B$1);SOMME.SI.ENS(ZSHIP_PS!$E:$E;ZSHIP_PS!$F:$F;"PCE";ZSHIP_PS!$P:$P;B$1)-SOMME(SOMME.SI.ENS(ZSHIP_PS!$E:$E;ZSHIP_PS!$Q:$Q;"Transport";ZSHIP_PS!$P:$P;B$1);SOMME.SI.ENS(ZSHIP_PS!$E:$E;ZSHIP_PS!$Q:$Q;"Transport";ZSHIP_PS!$N:$N;B$1);SOMME.SI.ENS(ZSHIP_PS!$E:$E;ZSHIP_PS!$Q:$Q;"Transport";ZSHIP_PS!$O:$O;B$1);SOMME.SI.ENS(ZSHIP_PS!$E:$E;ZSHIP_PS!$Q:$Q;"Transport";ZSHIP_PS!$M:$M;B$1);SOMME.SI.ENS(ZSHIP_PS!$E:$E;ZSHIP_PS!$Q:$Q;"Transport";ZSHIP_PS!$Y:$Y;B$1))))
Etant donné toutes les occurrences, il y a certainement possibilité d'optimiser.
J'aimerais pouvoir partager le fichier mais c'est malheureusement impossible car données sensibles.
Si vous avez une piste je suis preneur :)
- Menu de restaurant l'héritage
- Comment réduire la taille d'un fichier - Guide
- Formule si et excel - Guide
- Formule moyenne excel plusieurs colonnes - Guide
- Formule excel pour additionner plusieurs cellules - Guide
- Excel mise en forme conditionnelle formule - Guide
7 réponses
Bonjour,
Ca serait bien si :
1. on pouvait avoir une idée de la position des lignes et des ccolonnes
2. si on était sur que la feuille que tu montres, c'est bien ZSHIP_PS (parce que sinon...)
et 3. surtout pour faire plaisir à djidji, indique ta version d'Excel.
Daniel
Essaie :
=SOMMEPROD(ZSHIP_PS!E:E*SI((ZSHIP_PS!F:F="pce")*((ZSHIP_PS!M:M=B$1)+(ZSHIP_PS!N:N=B$1)+(ZSHIP_PS!O:O=B$1)+(ZSHIP_PS!P:P=B$1)+(ZSHIP_PS!Y:Y=B$1))>1;1;0))
Daniel
Pareil pour "transport" :
=SOMMEPROD(ZSHIP_PS!E:E*SI((ZSHIP_PS!F:F="pce")*((ZSHIP_PS!M:M=B$1)+(ZSHIP_PS!N:N=B$1)+(ZSHIP_PS!O:O=B$1)+(ZSHIP_PS!P:P=B$1)+(ZSHIP_PS!Y:Y=B$1))>1;1;0))-SOMMEPROD(ZSHIP_PS!E:E*SI((ZSHIP_PS!F:F="transport")*((ZSHIP_PS!M:M=B$1)+(ZSHIP_PS!N:N=B$1)+(ZSHIP_PS!O:O=B$1)+(ZSHIP_PS!P:P=B$1)+(ZSHIP_PS!Y:Y=B$1))>1;1;0))
Daniel
Bonjour,
Je ne vois pas en quoi ton image peut aider ? J'ai créé un classeur de test minimaliste. Les formules suivantes donnent le même résultat que la tienne. Peux-tu les vérifier ?
=SI(OU(B$14 = ""; $A$22 = ""); ""; SOMMEPROD(ZSHIP_PS!E:E * (ZSHIP_PS!F:F = "pce") * ((ZSHIP_PS!M:M = B$1) + (ZSHIP_PS!N:N = B$1) + (ZSHIP_PS!O:O = B$1) + (ZSHIP_PS!P:P = B$1) + (ZSHIP_PS!Y:Y = B$1))) - SOMMEPROD(ZSHIP_PS!E:E * SI((ZSHIP_PS!F:F = "transport") * ((ZSHIP_PS!M:M = B$1) + (ZSHIP_PS!N:N = B$1) + (ZSHIP_PS!O:O = B$1) + (ZSHIP_PS!P:P = B$1) + (ZSHIP_PS!Y:Y = B$1)) > 1; 1; 0)))
--------------
=SI(OU(B$14 = ""; $A$22 = ""); ""; SOMMEPROD(ZSHIP_PS!E:E * (ZSHIP_PS!F:F = "pce") * ((ZSHIP_PS!M:P = B$1) + (ZSHIP_PS!Y:Y = B$1))) - SOMMEPROD(ZSHIP_PS!E:E * SI((ZSHIP_PS!F:F = "transport") * ((ZSHIP_PS!M:P = B$1) + (ZSHIP_PS!Y:Y = B$1)) > 1; 1; 0)))
Daniel
Re,
J'ai réussi en modifiant l'adresse des colonnes comme ceci
=SOMMEPROD(((zship_ps[UQ]="PCE")*(zship_ps[Trimestres]=B1)-(zship_ps[Type]="Transport")*(zship_ps[Trimestres]=B1))*zship_ps[Qté livraison])
Du coup, sommeprod fait la même chose qu'un somme.si.ens mais on a l'air de pouvoir y mettre des conditions ?
En tout cas merci pour la piste, cela allège bien. Reste à voir comment je vais me démerder avec 84 conditions possibles
"Si tu veux de l'aide là-dessus, explique le problème"
C'est le problème initial en fait, je cherche à réduire car j'ai 84 possibilités en fonction des conditions (A3,A6,A8,A10,A11 dans la capture d'écran)
La formule ne tient en compte pour le moment que A3 les 4 autres conditions peuvent s'accumuler indépendamment les unes des autres.
Dans le meilleur des mondes, il faudrait que le contenu de la formule soit dynamique en fonction des conditions mais à part en vba - que je ne maitrise pas du tout - je ne vois pas trop comment faire
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionD'accord. A ce stade, il faudrait impérativement que tu fournisses un classeur. Anonymise tout ce qui est confidentiel ou supprime les parties qui ne servent pas et partage le classeur.
Daniel
Cela fonctionne partiellement avec cette formule
=SOMMEPROD(((zship_ps[UQ]="PCE")*((zship_ps[Trimestres]=B1)+(zship_ps[Jours]=B1)+(zship_ps[Mois]=B1)+(zship_ps[Semaine]=B1)+(zship_ps[Année]=B1))*(zship_ps[Client]=B6)*(zship_ps[Véhicule]=B8)*(zship_ps[OTP]=B10)*(zship_ps[Utilisateurs]=B11)*zship_ps[Qté livraison]))
mais uniquement dans le cas où chaque condition est renseignée. Si l'une des conditions est vide, le résultat est erroné
J'ai tenté comme cela pour pallier à l'erreur mais c'est comme si il ne prenait pas en compte le SI
=SOMMEPROD(((zship_ps[UQ]="PCE")*((zship_ps[Trimestres]=B1)+(zship_ps[Jours]=B1)+(zship_ps[Mois]=B1)+(zship_ps[Semaine]=B1)+(zship_ps[Année]=B1))*SI(B6="";1;(zship_ps[Client]=B6))*SI(B8="";1;(zship_ps[Véhicule]=B8))*SI(B10="";1;(zship_ps[OTP]=B10))*SI(B11="";1;(zship_ps[Utilisateurs]=B11))*zship_ps[Qté livraison]))
J'ai une autre méthode fonctionnelle mais que je ne veux pas utiliser issue d'une mauvaise pratique : la concatenation de B6;B8;B10;B11 en D5 ce qui m'oblige a créer une multitude de colonnes dans la feuille ZSHIP_PS
Je peux te proposer une solution VBA (ou formule, d'ailleurs)... à condition d'avoir un classeur.
Daniel
C'est vraiment très gentil de ta part mais si je n'arrive pas à comprendre ce que tu fais cela perd tout son intérêt pour moi dans le sens où je ne pourrais pas l'appliquer ultérieurement :)
C'est tout l'intérêt de CCM pour moi, assimiler l'aide proposée
Tu m'as déjà retiré une énorme épine avec sommeprod, j'ai réussi à réduire à 5 lignes max - avec concaténation malheureusement mais c'est mieux que rien en attendant de trouver mieux
=SOMMEPROD((((zship_ps[UQ]="PCE")-(zship_ps[Type]="Transport")-(zship_ps[Type]="Moule"))*((zship_ps[Trimestres]=B$1)+(zship_ps[Jours]=B$1)+(zship_ps[Mois]=B$1)+(zship_ps[Semaine]=B$1)+(zship_ps[Année]=B$1))*((zship_ps[CONCATENER client/véhicule/otp/user]=$D$5)+(zship_ps[CONCATENER véhicule/otp/user]=$D$5)+(zship_ps[CONCATENER client/véhicule/otp]=$D$5)+(zship_ps[CONCATENER client/véhicule/user]=$D$5)+(zship_ps[CONCATENER client/véhicule]=$D$5)+(zship_ps[CONCATENER otp/user]=$D$5)+(zship_ps[CONCATENER client/otp]=$D$5)+(zship_ps[CONCATENER client/user]=$D$5)+(zship_ps[CONCATENER véhicule/otp]=$D$5)+(zship_ps[CONCATENER véhicule/user]=$D$5)+(zship_ps[Véhicule]=$D$5)+(zship_ps[Utilisateurs]=$D$5)+(zship_ps[OTP]=$D$5)+(zship_ps[Client]=$D$5)+(zship_ps[Colonne11]=$D$5)))*zship_ps[Qté livraison])
Bonjour et merci pour ton retour
1. Pas sûr que cela t'aide, mais voici. Les lignes vont jusqu'à 35 en fonction du type de transaction et les colonnes à Z selon l'affichage max
2. Non, ce n'est pas la même feuille. C'est un fichier d'indicateurs avec trois type de feuilles :
- extractions brutes mises sous forme de tableau avec des colonnes attenantes situées à droite qui font quelques calculs (calcul du trimestre par exemple)
- une base de données qui sert notamment à gérer des listes en cascade ou aller chercher des infos qui ne sont pas extraites de l'ERP
- la page d'indicateurs dont la capture d'écran est tirée
3. Désolé, je voulais la mettre dans le message initial et j'ai oublié :) C'est une licence pro, donc forcément obsolète jusqu'à l'arrêt complet du support : Excel 2016