Autres Alternative à formules matricielles complexes (fichier lent)

THOMASD

XLDnaute Nouveau
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
 

Pièces jointes

  • Fichier test.xlsx
    536.2 KB · Affichages: 25

CISCO

XLDnaute Barbatruc
Bonjour

Est-ce que c'est plus rapide si tu écris dans B7 = 0, et dans B9
Code:
=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

XLDnaute Occasionnel
Bonjour THOMASD,

On pourrait limiter le calcul aux lignes utiles en remplaçant $Q$16:$Q$5000 par
Code:
INDIRECT($Q$14;1)
qui contiendrait
Code:
=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
Code:
SI($Q$16:$Q$5000<>"";
n'est jamais réalisée, la somme
Code:
=D16+B16/1000
renvoyant zéro.. remplacer par
Code:
=SI(OU(B16="";D16="");"";D16+B16/1000)

Votre formule :
Code:
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 :
Code:
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)))
 

THOMASD

XLDnaute Nouveau
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
 

Danixdb

XLDnaute Nouveau
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
 

Pièces jointes

  • Fichier test.xlsx
    660.8 KB · Affichages: 10

Discussions similaires

Réponses
7
Affichages
3 K

Membres actuellement en ligne

Statistiques des forums

Discussions
311 725
Messages
2 081 940
Membres
101 845
dernier inscrit
annesof