Décroisage de matrice + fonctions (recherchev/equiv): calcul trop long

Promax · 22 Septembre 2016

Bonjour à tous!

C'est donc une toute première discussion pour moi...

Je vous avoue que je ne suis pas du style à abandonner facilement avant de demander de l'aide mais là je n'ai plus de solution (si tenté qu'il y'en ait une qui me convienne ).

Pour annoncer la couleur, mon fichier pèse 120 Mo actuellement.........

Voici la mise en contexte:
je dois réaliser un fichier excel (que je ne gérerais plus à l'avenir!!!) qui permet au service RH de ma boite de mettre en forme des données d'horaires et d'absences afin de réaliser des graphes tous beaux tous propres et surtout qui sont amener à évoluer à terme.
Dans ce contexte, j'ai donc choisi d'éviter les macros (qui m'aurait permis de résoudre le problème mais que personne ne sera à même de faire évoluer à l'avenir...)et de réaliser mes calculs de la manière la plus évolutive possible (d'où la lourdeur du fichier, puisque j'ai fait en sorte que quasiment tout soit évolutif!!).

Le vif du sujet:
J'ai 2 extractions de logiciel qui sont reçues par mois: 1 avec les nombres d'heures, l'autres avec les absences. (onglets "Extraction tps roulant" et "Extraction absences").
Le responsable RH m'a donné la liste que lui entretien et fait varier dans le temps: il peut ajouter un nouveau chauffeur à la liste: j'ai défini la zone BDD_chauffeur pour ne pas avoir trop de calcul inutile (onglet "BDD Chauffeur").

Vous pouvez voir que les extractions n'ont pas le même format: l'extraction des temps a toujours le même format (les mois sont en colonnes) et dans celle des absences les mois sont mis les uns à la suite des autres verticalement.

1ère étape:
Je travail un peu les 3 onglets pour les mettre au format qui va bien (onglets avec les même noms mais avec "trav" ou "travaillé" derrière): ces calculs servent principalement à me mettre à dispo une colonne avec nom+prénom dans la même cellule pour pouvoir faire mes recherchev derrière mais je réalise déjà d'autres calculs...

2ème étape:
reformer une BDD "propre" et "dans le bon sens" avec les données à la fois des heures et des absences sur une seule ligne par chauffeur et par mois, pour pouvoir faire un TCD comme mon RH veut faire (la disposition de la BDD est fait de manière à pouvoir faire les TCDs comme sur l'onglet "TCDs pour graphiques")

je pense que c'est dans la construction de mon onglet "BDD pour TCD" que les formules sont trop lourdes (j'ai utilisé le recherchev avec un equiv qui rassemble 2 cellules pour déterminer le numéro de colonne à copier: problème de l'extraction des heures où les mois sont à la suite horizontalement et pas verticalement...)

Le problème peut aussi venir de la taille réel de mon extractions des absences qui comporte 22000 lignes: pour le moment il ne parvienne pas à faire une extraction plus ciblée donc bon...

Le fichier que j'ai uploadé est fortement raccourci et sans nom officiel (pour l'idée: l'extraction des absences est à plus de 20 000 lignes et celles des horaires 2000).
De la même manière toutes les formules que j'ai faites sont étirables à volonté (système de compteur etc...)

Donc voila: si un savant fou du forum a envie de tester ses connaissances et m'arranger les formules pour qu'elles soient moins gourmande je suis preneur!

Merci!

PS: j'oubliais, je suis sous 2016 (mais vu que j'utilise que des fonctions pas trop anciennes, je suis pas sûr qaue ça change quelque chose!)

vgendron · 22 Septembre 2016

Hello et bienvenue ici

bon. j'ai pas tout compris de ce que tu cherches exactement. mais si c'est alleger tes formules.., je crois savoir que la fonction SI est gourmande
et il me semble que tu fais beaucoup de tests inutiles
un exemple
onglet "BDD pour TCD)
colonne A
en A2=MOD(LIGNE()-2;11)+1
en B2=INDEX('BDD Chauffeur Travaillé'!$A$2:$A$12;A2)

tu tires vers le bas

est ce que le mot "fin" est utilisé quelque part pour détecter une fin de tableau?
si non; tu peux certainement supprimer tous tes tests si Ax ="fin"

dans ta colonne C: à part l'entete "mois" ligne 1, tu peux avoir autre chose que des dates?
si non. pas la peine non plus de surcharger avec un test sur le contenu
C2=MOIS.DECALER(DATE(2016;1;1);NB.SI($A$2:A2;1)-1)

Tu peux aussi donner des noms à tes zones de data pour les intégrer facilement dans tes formules
BDD_Chauf_Travaillé = DECALER('BDD Chauffeur Travaillé'!$A$2;;;NBVAL('BDD Chauffeur Travaillé'!$A:$A)-1;NBVAL('BDD Chauffeur Travaillé'!$1:$1))

et formule en D2=INDEX(BDD_Chauf_Travaillé;EQUIV(B2;INDEX(BDD_Chauf_Travaillé;;1);0);3)

bref.. surement beaucoup d'optimisations à apporter.. surtout si ton fichier fait 120Mo...

Promax · 23 Septembre 2016

Merci pour ces conseils, je regarde ça et je te tiens au courant!

vgendron · 23 Septembre 2016

Hello

je viens de regarder de plus près ton étape 1 qui fait que tu récupères 3 feuilles identiques à deux colonnes près..
si j'ai bien vu. tu te contentes de décaler les colonnes vers la droite pour y mettre en colonne A: Nom + Prénom
ca te fait donc des feuilles bourrées de formules. pour pas grand chose..

je sais que tu ne voulais pas de macro pour pb d'incompréhension par les futurs utilisateurs.
mais celle ci que je te propose est vraiment simple: j'y ai mis un commentaire à chaque ligne..
l'interet est que tu travailles directement sur les feuilles issues de l'importation. et donc plus de "doublon" chargé en formules
pour l'instant, cette macro doit etre lancée par un bouton qui serait placé dans une feuille..
mais on peut tout à fait imaginer de la lancer automatiquement en toute transparence..à l'ouverture du fichier..

VB:

Sub MiseEnForme()
'on désactive le refresh de l'écran pendant la macro pour gagner en rapidité
Application.ScreenUpdating = False
'on travaille sur la feuille BDD Chauffeur !  le nom doit etre exactement identique !
With Sheets("BDD Chauffeur")
    'on récupère le nombre de lignes utilisées de la feuille
    nb = .UsedRange.Rows.Count
    'on insère une colonne en A--> le reste est décalé vers la droite
    .Range("A:A").Insert
    'on met la formule qui permet de coller Nom et Prénom en colonnes C et D
    .Range("A2").FormulaLocal = "=D2&"" "" &E2"
    'on tire la formule jusqu'à la dernière ligne de la feuille
    .Range("A2:A" & nb).FillDown
    'on place l'entête
    .Range("A1") = "Nom + Prénom GXP"
End With


'on travaille sur la feuille Extraction tps roulant !  le nom doit etre exactement identique !
With Sheets("Extraction tps roulant")
    'on récupère le nombre de lignes utilisées de la feuille
    nb = .UsedRange.Rows.Count
    'on insère une colonne en A--> le reste est décalé vers la droite
    .Range("A:A").Insert
    'on met la formule qui permet de coller Nom et Prénom en colonnes F et G
    .Range("A2").FormulaLocal = "=F2&"" "" &G2"
    'on tire la formule jusqu'à la dernière ligne de la feuille
    .Range("A2:A" & nb).FillDown
    'on place l'entête
    .Range("A1") = "N+P"
End With

'on travaille sur la feuille Extraction absences !  le nom doit etre exactement identique !
With Sheets("Extraction absences")
    'on récupère le nombre de lignes utilisées de la feuille
    nb = .UsedRange.Rows.Count
    'on insère DEUX colonne en A et B--> le reste est décalé vers la droite
    .Range("A:B").Insert
    'on met la formule qui permet de coller Nom et Prénom en colonnes F et G
    .Range("A2").FormulaLocal = "=F2&"" "" &G2"
    'on met la formule qui permet de coller Nom et Prénom de la colonne A  plus matricule en colonne E
    .Range("B2").FormulaLocal = "=A2&"" "" &E2"
    'on tire la formule jusqu'à la dernière ligne de la feuille
    .Range("A2:B" & nb).FillDown
    'on place l'entête
    .Range("A1") = "N+P"
    .Range("B1") = "N+P+D"
'manque pour l'instant les colonnes tout à droite
'PS: tu perds aussi l'info code Etablissement..??
  
End With

'on réactive le refresh
Application.ScreenUpdating = True

End Sub

Promax · 23 Septembre 2016

vgendron à dit:
Hello

je viens de regarder de plus près ton étape 1 qui fait que tu récupères 3 feuilles identiques à deux colonnes près..
si j'ai bien vu. tu te contentes de décaler les colonnes vers la droite pour y mettre en colonne A: Nom + Prénom
ca te fait donc des feuilles bourrées de formules. pour pas grand chose..

'manque pour l'instant les colonnes tout à droite
'PS: tu perds aussi l'info code Etablissement..??

Merci pour l'aide et effectivement c'est comme ça que j'aurais fait si j'avais eu les mains libres...
Malheureusement malgré toutes les négociations avec la personne concernée, je crois que si elle doit faire "Activer les macros", elle a de grandes chances de faire un arrêt cardiaque..... !!! ^^
Et sinon c'est normal que je perde des données, elles ne sont pas utiles, j'en ai d'ailleurs supprimée beaucoup d'autres pour alléger encore!

Mais sinon j'aurais fait bien différemment, j'ai même essayé de "décroiser" la matrice des absences avec des formules directement dans l'onglet 'BDD pour TCD' mais c'est devenu encore plus lourd...

Sinon pour info en enlevant les formule SI en trop, en faisant les changements que tu m'as dit et en supprimant les 3/4 des lignes de l'onglet des absences je suis passé de 120 Mo à 8 Mo!!!

Pour info j'ai utilisé ça pour que ce soit dynamique:

Code:

=MOD(LIGNE()-2;LIGNES(BDD_Chauffeur)-1)+1

(j'ai juste utiliser le nom que j'avais créer pour que ce soit évolutif...)

Donc c'est plutôt sur la bonne voie!

Par curiosité: j'ai pas compris l'imbrication INDEX(EQUIV(INDEX))) dans ta formule? (bien qu'elle marche très bien!)

Autre piste:
tu aurais fait comment pour "décroiser" la matrice des heures?:
- mettre les mois à la suite les uns des autres verticalement et non horizontalement (c'est ce qu'il y'a dans les colonnes de J à P)
Et y'a-t-il mieux que les recherchev avec equiv des colonnes W à AG? (je fais avec des equiv car le nombre de colonnes des absences est variable...c'est ça qui m'a pas mal pris le choux...

Merci pour toute cette aide.
Je n'en suis qu'à mes premiers mois sur excel: merci pour la compréhension! ^^

Promax · 23 Septembre 2016

Ah et au fait autre question: j'avais fait plein de SI pour afficher "-" à la place de "#NA" ou "#VALEUR" parce que quand je fais des moyennes dans les tcd à la fin il peut pas me les calculer ...
Faut que je cherche un peu plus

vgendron · 23 Septembre 2016

pour les N/A et valeur..
il faut encader la formule avec un sierreur
sierreur(formule;"-")

en gros. si la formule envoi un bon résultat, c'est lui qui est affiché. sinon (si elle envoie un N/A ou Erreur), c'est le "-" qui est affiché

vgendron · 23 Septembre 2016

pour l'histoire des index equiv index
dans la formule =INDEX(BDD_Chauf_Travaillé;EQUIV($B2;INDEX(BDD_Chauf_Travaillé;;1);0);3)

Chauf_Travaillé est une zone à plusieurs colonnes de A à J

la partie Equiv cherche B2 dans la zone Chauf_Travaillé..
sauf que tu n'as besoin de chercher QUE dans la première colonne de cette zone
donc.. on retourne la première colonne avec index(BDD_Chauf_Travaillé;;1)

ce qui te retourne le numéro de ligne
et comme tu veux récuperer la valeur en colonne 3
tu n'as plus qu'à faire index(BDD_ChafTravaillé;NUMLIGNE;3

voila ;-)

Promax · 23 Septembre 2016

Ok je vois, le but est de réduire le champ de recherche!!

Et sinon bah le problème justement c'est que j'avais fait ces SIERREUR partout mais justement tu m'as dit que les fonctions SI prenaient pas mal de ressource et du coup je les ais enlever....

En plus si je me trompe pas en mettant un "-" lorsqu'il calcule la moyenne, il ne la prend pas en compte? (c'est ce que je veux: si un conducteur n'as pas conduit le mois en question alors je l'enlève du diviseur dans le calcul de ma moyenne)

Décroisage de matrice + fonctions (recherchev/equiv): calcul trop long

Promax

XLDnaute Nouveau

Pièces jointes

vgendron

XLDnaute Barbatruc

Promax

XLDnaute Nouveau

vgendron

XLDnaute Barbatruc

Promax

XLDnaute Nouveau

Promax

XLDnaute Nouveau

vgendron

XLDnaute Barbatruc

vgendron

XLDnaute Barbatruc

Promax

XLDnaute Nouveau

Discussions similaires