XL 2019 Alternative à la fonction INDEX(EQUIV) qui fait planter Excel

kohta

XLDnaute Occasionnel
Bonjour à tous,

Tout d'abord, merci à la communauté pour tous ces services rendus, c'est toujours agréable de se faire guider quand on est dans une impasse.


Voici mon problème:

Je récupère une extraction .csv que je colle dans un classeur et qui contient la liste des tournées camion affectées à des clients.

Le format des tournées est le suivant: 3 chiffres: un chiffre correspondant au jour de la semaine (1-7) suivi de 2 chiffres correspondant au numéro de tournée (01-99).
Soit 215 pour la tournée 15 du MARDI.

La table ressemble donc à:

Client n°NomTournée
1​
Client 1
407​
3​
Client 3
103​
3​
Client 3
305​
3​
Client 3
505​
4​
Client 4
107​
4​
Client 4
403​
5​
Client 5
104​
9​
Client 9
499​
10​
Client 10
303​



J'ai besoin de récupérer ces données sous la forme d'un tableau avec une ligne par numéro client et une colonne par jour, avec les numéros de tournée par jour:

Client n°NomLUNMARMERJEUVENSAM
1​
Client1
407​
3​
Client3
103​
305​
505​
4​
Client4
107​
403​
5​
Client5
104​
9​
Client9
499​
10​
Client10
303​


Voici donc la formule que j'ai placée dans chaque cellule, ici pour la colonne LUN:

=SI(ESTNA(INDEX(Extraction!$C:$C;EQUIV(1;(Extraction!$A:$A=$A2)*(GAUCHE(Extraction!$C:$C;1)="1");0)));"";INDEX(Extraction!$C:$C;EQUIV(1;(Extraction!$A:$A=$A2)*(GAUCHE(Extraction!$C:$C;1)="1");0)))



Je vérifie donc le numéro client puis le premier chiffre du numéro de tournée pour savoir si c'est un Lundi (1xx), Mardi (2xx) etc.

La formule fonctionne très bien mais lorsque je lance le calcul de ma page via un bouton, c'est très très long à calculer, puis Excel, puis tout mon pc, plantent.

Je vous sollicite donc afin de savoir si vous auriez une idée pour faire la même chose en moins gourmand?

Merci par avance à tous, je met un fichier exemple en pj.
kohta
 

Pièces jointes

  • Demo_tri_tournées.xlsm
    107.7 KB · Affichages: 11
Solution
re,

@kohta , voila la correction !

Bien cordialement, @+
VB:
=SI(ESTNA(INDEX(Extraction!$C$2:DECALER(Extraction!$C$1;EQUIV(9^9;Extraction!$A:$A)-1;0);EQUIV(1;(Extraction!$A$2:DECALER(Extraction!$A$1;EQUIV(9^9;Extraction!$A:$A)-1;0)=$A2)*(GAUCHE(Extraction!$C$2:DECALER(Extraction!$C$1;EQUIV(9^9;Extraction!$A:$A)-1;0);1)="1");0)));"";INDEX(Extraction!$C$2:DECALER(Extraction!$C$1;EQUIV(9^9;Extraction!$A:$A)-1;0);EQUIV(1;(Extraction!$A$2:DECALER(Extraction!$A$1;EQUIV(9^9;Extraction!$A:$A)-1;0)=$A2)*(GAUCHE(Extraction!$C$2:DECALER(Extraction!$C$1;EQUIV(9^9;Extraction!$A:$A)-1;0);1)="1");0)))
Bonjour Kohta, Jha, le forum

en reprenant et en dimensionnant ta formule de recherche, tu peux même laisser le calcul en automatique, c'est quasi instantané, voir fichier.
Faire des recherches avec Index et Equiv sur une colonne entière implique une recherche sur la totalité des lignes soit 1048576 lignes, quand on les multiplie, cela met rapidement à genoux n'importe quel ordinateur personnel.

[édition : petit oubli de $ dans la formule, je mets à jour !]

Bien cordialement, @+
 
re,

@kohta , voila la correction !

Bien cordialement, @+
VB:
=SI(ESTNA(INDEX(Extraction!$C$2:DECALER(Extraction!$C$1;EQUIV(9^9;Extraction!$A:$A)-1;0);EQUIV(1;(Extraction!$A$2:DECALER(Extraction!$A$1;EQUIV(9^9;Extraction!$A:$A)-1;0)=$A2)*(GAUCHE(Extraction!$C$2:DECALER(Extraction!$C$1;EQUIV(9^9;Extraction!$A:$A)-1;0);1)="1");0)));"";INDEX(Extraction!$C$2:DECALER(Extraction!$C$1;EQUIV(9^9;Extraction!$A:$A)-1;0);EQUIV(1;(Extraction!$A$2:DECALER(Extraction!$A$1;EQUIV(9^9;Extraction!$A:$A)-1;0)=$A2)*(GAUCHE(Extraction!$C$2:DECALER(Extraction!$C$1;EQUIV(9^9;Extraction!$A:$A)-1;0);1)="1");0)))
 

Pièces jointes

  • Demo_tri_tournées.xlsm
    113.3 KB · Affichages: 3

Hasco

XLDnaute Barbatruc
Bonjour,

Puisque excel 2019 :
Une proposition par power query

La première requête interroge et traite les données à partir du classeur.
La deuxième interroge le fichier sur disque puis traite les données (Feuille 'A partir du csv').
Avant d'actualiser cette dernière, changer la source (chemin du fichier) en double-cliquant sur l'étape nommée 'Source' de la requête.

Le fichier est un .txt pour être accepté ici.

cordialement
 

Pièces jointes

  • Classeur1.xlsx
    32.8 KB · Affichages: 3
  • Datas.txt
    201 bytes · Affichages: 2
Dernière édition:

kohta

XLDnaute Occasionnel
Bonjour à tous,

Merci pour vos trois propositions ainsi que pour la rapidité de réponse, j'ai essayé chaque solution et je me suis rabattu sur la solution de Yeahou avec l'usage de la fonction DECALER, car j'ai eu plus de mal a comprendre l'usage de PowerQuery ou de la formule proposée par JHA avec les fonctions PETITE.VALEUR et COLONNE.

Sujet résolu en tout cas, bravo et merci👍
 

Discussions similaires

Haut Bas