Simplification formules Excel Résolu/Fermé

Question

Salut la team,

J'ai besoin de l'aide d'un expert en simplification formule excel svp.

J'ai créé un fichier de suivi logistique pour la société pour laquelle je bosse.

J'ai réussi à me faire aider pour trouver des formules qui m'affichent clairement si un article logistique est complet et s'il a été livré en complet, partiel ou non livré.

Mais... Ces formules sont un peu trop hardcore pour moin PC portable pro et je perds énormément de temps à attendre qu'il finisse de calculer les formules. Le fichier peut faire jusqu'à 85000 lignes sans filtres.

Voici les formules en questions:

=SI(OU(ET(+NB.SI(B:B;B2)=NB.SI.ENS(K:K;"OUI";B:B;B2));ET(+NB.SI(B:B;B2)=NB.SI.ENS(X:X;"OUI";B:B;B2)));"OUI";"NON") --> ARTICLE COMPLET OUI OU NON

=SI.CONDITIONS(NB.SI(B:B;B2)=NB.SI.ENS(K:K;"OUI";B:B;B2);"COMPLET";NB.SI(B:B;B2)=NB.SI.ENS(K:K;"NON";B:B;B2);"NON LIVRÉ";VRAI;"PARTIEL") --> ARTICLE LIVRÉ EN PARTIEL COMPLET OU TOTAL

Ce sont ces deux formules qui me plombent le PC.

J'ai pensé aussi à essayer de faire des formules en M dans Power Query mais je ne comprends pas le fonctionnement de M... Si quelqu'un sait au passage traduire les formules en M je pourrais les essayer sur mon fichier...

Voici un exemple pour le fichier logistique pour voir comment il se comporte.

https://cjoint.com/c/LGukdunhCUp

Merci d'avance,

Raymond PENTIER · Answer

Bonjour Chris.

Ta question semble intéressante ... Mais tu aurais dû indiquer quelle version d'Excel tu utilises ; ainsi tu aurais épargné à certains CCMistes, comme moi, de passer un certain temps avant de réaliser qu'avec nos versions 2010 ou 2013 nous ne disposions pas encore de la fonction SI.CONDITIONS !

La seule aide que nous puissions t'apporter, pour diminuer les temps de calcul de tes formules, est de remplacer A:A par A2:A30, dans l'exemple simplifié, ou par A2:A90000 dans le vrai fichier ; et pareil pour B:B, C:C, K:K ...

ChrisGHT · Answer

Bonjour Raymond,

Franchement désolé je pensais que toutes les versions de Excel avaient les mêmes fonctions.

Merci en tout cas pour le bout de solution je testerai ça demain.

Aux premiers abords je me dis qu'étant donné que le fichier est dynamique je ne voulais pas mettre de limites, mais en appliquant des filtres chronologique je peux réduire à moins de 15000 lignes. Ça peut passer...

A bientôt

Raymond PENTIER · Answer

Non : appliquer ou non un filtre ne change rien au nombre de calculs à opérer, donc au temps de ces calculs.

J'ai pris une marge de 5 000 lignes. Si tu sais que tu ne dépasseras jamais 500 000 lignes, tu remplaces mon 90 000 par 499 999. Je te rappelle qu'Excel comporte 1 048 576 lignes !

Tu peux aussi attribuer un Nom défini à tes plages ; cela accélère un peu les calculs ...

Ainsi A2:A90000 (ou A2:A499999) recevrait le nom P_A, B2:B90000 (ou B2:B499999) recevrait le nom P_B, etc.
Et ta formule
=SI(OU(ET(+NB.SI(B:B;B2)=NB.SI.ENS(K:K;"OUI";B:B;B2));ET(+NB.SI(B:B;B2)=NB.SI.ENS(X:X;"OUI";B:B;B2)));"OUI";"NON")
s'écrirait
=SI(OU(ET(NB.SI(P_B;B2)=NB.SI.ENS(P_K;"OUI";P_B;B2));ET(NB.SI(P_B;B2)=NB.SI.ENS(P_X;"OUI";P_B;B2)));"OUI";"NON")

en supprimant au passage les + inutiles devant les fonctions NB.SI !

ChrisGHT · Answer

Merci Raymond, ça a l'air top j'ai hâte de tester ça demain .

yclik · Answer

Bonsoir

voir peut être avec la fonction sommeprod

eriiic · Answer

Bonjour à tous,

si tu es toujours trié par 'Contenant' tu peux travailler avec des noms dynamiques.
On regarde où il commence avec un Equiv() et combien avec un Nb.Si(). Ainsi tu travailles avec des plages réduites au minimum nécessaire.
Pour A:A, en ligne 2, remplace par :

DECALER($A$1;EQUIV(A2;A:A;0)-1;;NB.SI(A:A;A2))

Si tu as besoin de E:E il faut donc décaler A:A de 4 colonnes :

DECALER($A$1;EQUIV(A2;A:A;0)-1;4;NB.SI(A:A;A2))

etc

N'ayant pas 365 je te laisse faire les modif et tester le gain.
Possible que le gain théorique soit bouffé par la volatilité de Decaler() 
eric

Simplification formules Excel

6 réponses

Discussions similaires