Autres Alternative à formules matricielles complexes (fichier lent)

[THOMASD]

Bonjour,

Après avoir passé un (très très long) moment pour élaborer deux formules matricielles sur lesquelles je bloquais, je constate que celles-ci font ramer mon fichier, et le rende inutilisable. En effet, il est désormais impossible de rentrer de nouvelles données brutes (à cause des temps de recalcule à priori).

Ces deux formules ont un peu le même objectif, à savoir élaborer une liste sans doublon. Elles se trouvent dans le fichier Test joint (version très épurée du fichier initial) :
- colonne B de l'onglet Synthèse
- colonne R de l'onglet Données. C'est un calcul intermédiaire pour le petit tableau en colonnes T et U. L'objectif est ici de connaitre dans combien de Levées (colonne B) intervient chaque Pot (colonne D). J'ai rien trouvé de mieux que ce petit micmac de calculs intermédiaires avant la matricielle, mais je suis sur que quelqu'un saurait faire plus "propre" !

D'où ma question :
Y a t-il des alternatives à ces deux formules qui seraient supportables pour mon fichier ?
Avec deux contraintes :
1/ je n'ai pas de connaissance spécifique en VBA
2/le fichier sera diffusé à des utilisateurs qui maîtrisent très peu Excel : je souhaiterais donc éviter des solutions de type Arreter le recalcule automatique (je ne suis pas certains que celui soit éffectué dans de bonnes conditions ensuite).

Merci d'avance pour vos idées
Thomas

 

[CISCO]

Bonjour

Est-ce que c'est plus rapide si tu écris dans B7 = 0, et dans B9

=SIERREUR(INDEX(Données!$E$1:$E$5000;PETITE.VALEUR(SI(NB.SI(B$7:B8;Données!E$16:E$5000)=0;LIGNE($16:$5000);"");LIGNE($1:1)));"")

en matriciel, bien sûr...

@ plus

P.S : Sur 5000 lignes, c'est assez normal que cela rame...

 

[njhub]

Bonjour THOMASD,

On pourrait limiter le calcul aux lignes utiles en remplaçant $Q$16:$Q$5000 par

INDIRECT($Q$14;1)

qui contiendrait

=CONCATENER("$Q16:$Q$";NB.SI(B16:B5000;">="&1)+15)

. Il en va de mëme pour les autres formules calculant 5000 lignes au lieu de 25;

La condition

SI($Q$16:$Q$5000<>"";

n'est jamais réalisée, la somme

=D16+B16/1000

renvoyant zéro.. remplacer par

=SI(OU(B16="";D16="");"";D16+B16/1000)

Votre formule :

INDEX($Q$16:$Q$5000;PETITE.VALEUR(SI(FREQUENCE(SI($Q$16:$Q$5000<>"";EQUIV($Q$16:$Q$5000;$Q$16:$Q$5000;0)); LIGNE($Q$16:$Q$5000)-LIGNE($Q$16)+1);LIGNE($Q$16:$Q$5000)-LIGNE($Q$16)+1);LIGNES(R$16:R16)))

Deviendrait :

INDEX(INDIRECT($Q$14;1);PETITE.VALEUR(SI(FREQUENCE(SI(INDIRECT($Q$14;1)<>"";EQUIV(INDIRECT($Q$14;1);INDIRECT($Q$14;1);0));LIGNE(INDIRECT($Q$14;1))-LIGNE($Q$16)+1);LIGNE(INDIRECT($Q$14;1))-LIGNE($Q$16)+1);LIGNES(R$16:R16)))

 

[CISCO]

Bonsoir

Et puis, passer par FREQUENCE... ici, est-ce bien indispensable ?

@ plus

 

[Compte Supprimé 979]

Bonjour ThomasSD, le fil

Et comme ça, à essayer

 

[THOMASD]

Bonjour,

Merci à tous les deux, et bravo puisque vos solutions sont fonctionnelles !
Il y a effectivement du mieux, mais cela reste peu satisfaisant si on envisage le volume de saisie (certains utilisateurs auront en effet besoin de près de 5000 lignes.. et le lag n'est pas négligeable).
Je vais peut être envisager de supprimer au moins l'une de ses deux formules.

Cisco, pour la fonction Frequence, je ne saurais te répondre
Après avoir longtemps bloqué à rédiger la formule, je me suis contenté d'adapter une formule trouvée sur le forum, et qui correspondait +/- à mon cas de figure.

Merci encore en tout cas,
Bonne journée

 

[THOMASD]

Et merci Bruno, ta solution, en plus d'être bien plus propre, allège très nettement les calculs pour la première formule. Cela règle mon souci pour cette partie à priori !

 

[CISCO]

Bonsoir

Cf. post #2 une solution sans FREQUENCE, bien plus courte... Pour autant, dire que cela te fera gagner beaucoup en rapidité, cela serait exagérer...

@ plus

 

[THOMASD]

Oui oui, j'avais bien testé ta proposition Cisco, c'était un poil plus fluide mais restait laborieux.
En combinant avec la solution de Bruno pour l'autre formule, on commence à retrouver quelque chose d'acceptable

 

[CISCO]

Bonsoir

Peut-être plus rapide, toujours avec B7 = 0, et en B9, toujours en matriciel

SIERREUR(INDEX(Données!$E$16:$E$5000;EQUIV(0;NB.SI(B$7:B8;Données!$E$16:$E$5000);0));"")

@ plus

 

[Compte Supprimé 979]

@CISCO

Je ne veux pas mettre les pieds dans le plat, mais du matriciel restera toujours du matriciel... extrêmement long

 

[Danixdb]

Bonjour ThomasD, Le Forum,

Et une solution avec une requête Power query et des mesures crées avec Power pivot, plus un tableau de sortie proche de ton format (Synthèse) donne le même résultat et un simple Actualiser tout suffit à mettre à jour la synthèse (en fait Feuill1 dans ma version du fichier)
Espérant que cela t'aidera et t'ouvrira d'autres pistes et ce même sur des tableaux plus grands en nombre de lignes. Là j'ai limité le tableau à 41 lignes mais comme c'est un tableau structuré il va s'auto-étendre lors de l'insertion de données.
Cordialement
DanixDB

 

[CISCO]

Bonjour

@CISCO

Je ne veux pas mettre les pieds dans le plat, mais du matriciel restera toujours du matriciel... extrêmement long

Tout à fait d'accord.

@ plus