Fonctions matricielles fonctionnelles...mais fichier bien trop lent
Bonjour,
J'ai réalisé ce tableau qui me donne exactement le résultat escompté. Problème: mon fichier rame comme pas possible, voir même il plante.
Est il possible d'améliorer le temps de calcul? Pourquoi rame t il comme cela ce fichier?
Infos sur le document:
Dans l'onglet convert j'ai un tableau avec des données sur 3 axes: En A les variables, En colonne R, le nom des salariés et sur une ligne horizontale les mois allant de 1 à 12 et le total. Il s'agit d'un import qui est copié-collé ici. Je ne peux donc pas modifier sa forme. Le nombre de salarié varie de 1 à 1000. Les variables ne sont pas forcement les mêmes, elles peuvent être vides aussi.
J'ai crée un onglet "Feuille1" qui me permet de faire la liste des salariés et des variables en valeur unique.
Ensuite dans l'onglet Importtotal j'ai finalement des mêmes donnée que dans convert mais sous une autre présentation. En H et I je me donne la possibilité de choisir via un menu déroulant la variable dont j'ai besoin. J'ai utilisé les fonctions matricielles car je ne sais pas écrire les macros.
Si le nombre de données est trop important pour les fonctions matricielles, je veux bien tenter le VBA. Etape 1 : lister les variables et les salariés en valeur unique et dans l'ordre : J'ai trouvé cette macro sur le célèbre site de Jacques Boisgontier. Mais il classe sur la même feuille et je ne sais pas comment lui demander de classer sur "Feuille 1"
Public Sub ListeSansDoublonsTriée()
Dim ws As Worksheet
Dim Dict As Object
Dim n As Long
Dim Cell As Range
Set ws = ActiveSheet
Set Dict = CreateObject("Scripting.Dictionary")
With ws
.Cells(5).CurrentRegion.Offset(1).ClearContents
n = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each Cell In .Cells(2, 1).Resize(n)
If Cell <> "" Then Dict(Cell.Value) = ""
Next Cell
.Cells(2, 5).Resize(Dict.Count, 1) = Application.Transpose(Dict.keys)
.Cells(5).Sort Key1:=.Cells(2, 5), Order1:=xlAscending, Header:=xlYes
End With
Set Dict = Nothing: Set ws = Nothing
End Sub
Etape 2 : Aller chercher les infos dans Convert et les "classer" comme je veux.
Voila, quelle est votre opinion par rapport à cela? Pourriez vous me guider vers ce que je dois faire? Merci par avance
Bonjour,
Je n'ai pas regardé votre fichier mais j'imagine que vous formules intègrent des colonnes entières, ce qui implique un nombre considérable de calculs.
Si cette intuition était vérifiée, il faudrait alors songer à mettre vos données sous forme de tableaux structurés et adapter vos formules matricielles en conséquence. L'avantage du tableau structuré est qu'il est dynamique (se restructure seul) et ne nécessite pas de mettre à jour les références lorsque sa taille varie.
Exemple de formule valable quelle que soit le nombre de lignes du tableau :
=SOMME(SI(Tableau1[Colonne1]<>"";1))On compte les non vides de la colonne Colonne1 du tableau Tableau1.
Si ça rame toujours, il faudra éventuellement envisager une solution VBA.
Cdlt,
Bonjour à tous !
Avez-vous exploré la piste Power Query (pour intégrer et nettoyer vos données importées) accompagné d'un TCD ?
Bonjour
En effet 3GB votre intuition est juste et votre remarque sur les tableau structurés également.
Voici tout de même la macro pour adapter le code de Jacques Boisgontier.
Sub Listes()
Dim tb()
Dim i!
Dim dl
Dim dico As Object
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("CONVERT")
Set sh2 = Sheets("Feuil1")
sh2.Range("A:C").ClearContents
Set dico = CreateObject("Scripting.dictionary")
dl = sh1.Range("A" & Rows.Count).End(xlUp).Row
tb = sh1.Range("A3:A" & dl).Value2
For i = 1 To UBound(tb)
If tb(i, 1) <> "" Then dico(tb(i, 1)) = ""
Next i
sh2.Range("A1").Resize(dico.Count) = Application.Transpose(dico.keys)
dico.RemoveAll
dl = sh1.Range("Q" & Rows.Count).End(xlUp).Row
tb = sh1.Range("Q3:R" & dl).Value2
For i = 1 To UBound(tb)
If tb(i, 1) <> "" Then dico(CStr(tb(i, 1))) = tb(i, 2)
Next i
sh2.Range("B1").Resize(dico.Count) = Application.Transpose(dico.keys)
sh2.Range("C1").Resize(dico.Count) = Application.Transpose(dico.items)
End SubCeci dit je ne saurais trop vous conseiller de repartir d'un classeur vide et de tenir compte des remarques de 3GB
En prime un classeur un peu nettoyé avec le code ci-dessus
j'ai fais ce tableau comme cela car j'avais besoin qu'il soit "prêt à l'emploi".
on fait un copié-coller de Convert et hop on a le résultat dans importtotal.
Et oui j'ai pris des colonnes entières pour aller chercher mes résultats dans les fonctions matricielles :-/
1048576 lignes x 30 colonnes. Peut être faut il envisager d'investir dans un ordinateur quantique
En vba : clic sur un bouton et hop le copier / collé en fait et le traitement aussi.
Merci
Bonsoir de nouveau !
Et ma suggestion ?
Inopérante ?
JFL, je trouve l'utilisation de PowerQuery complexe. Le problème c'est que mon tableau convert n'est pas fixe, c'est un import que je vais être amené à copier dans convert.
Ta solution implique de devoir tout retravailler à chaque nouvel import non?
Bonsoir à tous !
JFL, je trouve l'utilisation de PowerQuery complexe. Le problème c'est que mon tableau convert n'est pas fixe, c'est un import que je vais être amené à copier dans convert.
Ta solution implique de devoir tout retravailler à chaque nouvel import non?
Osez...osez ! Vous en serez le bénéficiaire.
C'est l'objet même de Power Query que de s'occuper de l'intégration (avec enrichissements si besoin est...) des données externes. Une fois la (ou les) requête(s) mise(s) en place, vous êtes libéré des tâches ingrates et chronophages.
Bonjour JFL,
Après quelques tutos sur Youtube, je me suis lancée sur Power Query.
Bon maintenant je n'ai plus qu'a trouver comment géré mon fichier source qui du coup va être différent pour chaque utilisateurs. Le fameux "Et hop!" :-) un process super simple pour mes utilisateurs afin que eux n'aient pas à aller sur Power query mais qu'ils puissent avoir les TCD clé en main.
Merci pour le tuyau et cela m'ouvre aussi pas mal de perspectives je crois.