Exploiter des données par un tableau créé en VBA
Bonjour,
Je cherche à écrire une macro permettant d'analyser des données et les reporter dans un tableau.
Mes essais ont consisté à utiliser des boucles FOR-NEXT imbriquées, mais la procédure est beaucoup trop longue et je pense que la gestion par des variables "tableau" est plus économe en temps, seulement, je ne la connais pas du tout.
Ma demande précise est expliquée dans le tableau EXCEL simplifié ci-joint et que j'ai volontairement mis en XLSX et non XLSM puisque je n'ai pas compliqué en mettant l'ensemble de mon fichier avec plusieurs onglets et les autres macros déjà créées. le contenu global n'étant pas utile pour comprendre ma demande.
D'avance merci
Cordialement
Bonjour
Pourquoi une macro ?
Un TCD ou PowerQuery font cela très bien en quelques clics
Bonjour,
2 essais à tester (sans variable tableau, avec variables tableau) :
Sub TriCroisé()
Dim L As Long, C As Integer, Lig As Long
Application.Calculation = xlCalculationManual
With Sheets("Feuil1")
For Lig = 12 To .Range("B" & Rows.Count).End(xlUp).Row 'Boucle sur données source
L = .Range("B" & Lig) + 11 '11 correspond à ligne d'en-tête du tableau
C = .Range("C" & Lig) + 5 '5 correspond à la colonne E du tableau
.Cells(L, C) = .Cells(L, C) + 1
Next Lig
End With
Application.Calculation = xlCalculationAutomatic
End SubSub TriCroisé2()
Dim L As Long, C As Integer, Lig As Long
Dim Source() As Variant, Resultat() As Integer, LMax As Long, CMax As Integer
With Sheets("Feuil1")
Source = .Range("B12:C" & .Range("B" & Rows.Count).End(xlUp).Row).Value 'Affecte les données au tableau source
LMax = Application.Max(.Range("B:B")) 'Type d'info max
CMax = Application.Max(.Range("C:C")) 'Vendeur max
ReDim Resultat(1 To LMax, 1 To CMax) 'Dimensionnement du tableau vierge
For Lig = LBound(Source) To UBound(Source) 'Boucle sur données source
L = Source(Lig, 1)
C = Source(Lig, 2)
Resultat(L, C) = Resultat(L, C) + 1
Next Lig
.Range(.Cells(12, 6), .Cells(LMax + 11, CMax + 5)).Value = Resultat
End With
'[Facultatif] : code supplémentaire pour supprimer les 0 des résultats
End SubMerci vous deux.
Concernant la 2ème solution proposée par Pedro22, je l'ai testée et j'ai une erreur d'exécution 13
sur "L = Source(Lig, 1)"
Qu'en pensez-vous ?
Bonjour,
2 essais à tester (sans variable tableau, avec variables tableau) :
Sub TriCroisé() Dim L As Long, C As Integer, Lig As Long Application.Calculation = xlCalculationManual With Sheets("Feuil1") For Lig = 12 To .Range("B" & Rows.Count).End(xlUp).Row 'Boucle sur données source L = .Range("B" & Lig) + 11 '11 correspond à ligne d'en-tête du tableau C = .Range("C" & Lig) + 5 '5 correspond à la colonne E du tableau .Cells(L, C) = .Cells(L, C) + 1 Next Lig End With Application.Calculation = xlCalculationAutomatic End SubSub TriCroisé2() Dim L As Long, C As Integer, Lig As Long Dim Source() As Variant, Resultat() As Integer, LMax As Long, CMax As Integer With Sheets("Feuil1") Source = .Range("B12:C" & .Range("B" & Rows.Count).End(xlUp).Row).Value 'Affecte les données au tableau source LMax = Application.Max(.Range("B:B")) 'Type d'info max CMax = Application.Max(.Range("C:C")) 'Vendeur max ReDim Resultat(1 To LMax, 1 To CMax) 'Dimensionnement du tableau vierge For Lig = LBound(Source) To UBound(Source) 'Boucle sur données source L = Source(Lig, 1) C = Source(Lig, 2) Resultat(L, C) = Resultat(L, C) + 1 Next Lig .Range(.Cells(12, 6), .Cells(LMax + 11, CMax + 5)).Value = Resultat End With '[Facultatif] : code supplémentaire pour supprimer les 0 des résultats End Sub
Je reviens sur votre 1ère proposition:
Le résultat s'affiche mais avec une erreur puisqu'il m'indique par exemple la valeur "10" pour le vendeur 5 alors que le vendeur 5 n'a alimenté l'info '5' que 5 fois, de même pour les autres où le résultat doit être "1" et non "2".
Par ailleurs, j'ai une erreur d'exécution 13 sur "L = .Range("B" & Lig) + 11 '11 correspond à ligne d'en-tête du tableau"
Merci de votre éclairage.
Bonjour
Un TCD ou PowerQuery font cela très bien en quelques clics
Merci de cette réponse, et c'est effectivement vers quoi j'avais effectué mes premières recherches, sauf que les tutos sur Internet ne m'ont pas permis de comprendre comment le faire.
J'ai ajouté le fichier avec les 2 solutions à mon post précédent
Merci vous deux.
Concernant la 2ème solution proposée par Pedro22, je l'ai testée et j'ai une erreur d'exécution 13
sur "L = Source(Lig, 1)"
Qu'en pensez-vous ?
Quelle est la valeur contenue dans Source(Lig, 1) au moment où l'erreur se présente (visible au passage de la souris ou par le biais d'un espion sur la variable tableau).
Merci vous deux.
Concernant la 2ème solution proposée par Pedro22, je l'ai testée et j'ai une erreur d'exécution 13
sur "L = Source(Lig, 1)"
Qu'en pensez-vous ?
Quelle est la valeur contenue dans
Source(Lig, 1)au moment où l'erreur se présente (visible au passage de la souris ou par le biais d'un espion sur la variable tableau).
Je n'ai rien pu observer en passant la souris, aussi, pouvez-vous m'indiquer comment mettre un espion, car je suppose que ce n'est pas un Msgbox qui s'afficherait à chaque boucle ? Par ailleurs, la procédure s'arrête avant tout affichage.
Bonjour
Un TCD ou PowerQuery font cela très bien en quelques clics
Merci de cette réponse, et c'est effectivement vers quoi j'avais effectué mes premières recherches, sauf que les tutos sur Internet ne m'ont pas permis de comprendre comment le faire.
J'ai ajouté le fichier avec les 2 solutions à mon post précédent
Effectivement, merci je n'avais pas vu.
Aussi je vois que le résultat est conforme à mon attente, ce serait parfait si je savais transposer ce tableau dans mon fichier réel, ce qui n'est pas le cas. Pouvez-vous m'y aider car je ne vois pas ni où ni comment on définit la zone de recherche, ni comment on sélectionne les données souhaitées dans le tableau résultat.
D'avance merci
Je n'ai rien pu observer en passant la souris, aussi, pouvez-vous m'indiquer comment mettre un espion, car je suppose que ce n'est pas un Msgbox qui s'afficherait à chaque boucle ? Par ailleurs, la procédure s'arrête avant tout affichage.
Lorsque VBA est en mode débogage (= exécution en pause), il est possible de voir la valeur contenue dans une variable par simple passage de la souris (voir illustration). Sinon, dans débogage-->ajouter un espion-->expression:Source, vous pouvez consulter le contenu de la variable dans l'encart en bas de l'éditeur VBA.
J'ai testé les 2 macros sans soucis dans votre fichier test, après avoir supprimé la ligne de texte (ligne 34).
Les 2 macros sont à adapter dans le cas où les tableaux Excel débutent à des lignes et/ou colonnes différentes.
Je n'ai rien pu observer en passant la souris, aussi, pouvez-vous m'indiquer comment mettre un espion, car je suppose que ce n'est pas un Msgbox qui s'afficherait à chaque boucle ? Par ailleurs, la procédure s'arrête avant tout affichage.
Lorsque VBA est en mode débogage (= exécution en pause), il est possible de voir la valeur contenue dans une variable par simple passage de la souris (voir illustration). Sinon, dans
débogage-->ajouter un espion-->expression:Source, vous pouvez consulter le contenu de la variable dans l'encart en bas de l'éditeur VBA.illu1.jpg
J'ai testé les 2 macros sans soucis dans votre fichier test, après avoir supprimé la ligne de texte (ligne 34).
Les 2 macros sont à adapter dans le cas où les tableaux Excel débutent à des lignes et/ou colonnes différentes.
Effectivement, ça marche. ce qui gênait c'était les cellules B34 et C34 fusionnées
Grand merci pour votre aide.
Cordialement
Je n'ai rien pu observer en passant la souris, aussi, pouvez-vous m'indiquer comment mettre un espion, car je suppose que ce n'est pas un Msgbox qui s'afficherait à chaque boucle ? Par ailleurs, la procédure s'arrête avant tout affichage.
Lorsque VBA est en mode débogage (= exécution en pause), il est possible de voir la valeur contenue dans une variable par simple passage de la souris (voir illustration). Sinon, dans
débogage-->ajouter un espion-->expression:Source, vous pouvez consulter le contenu de la variable dans l'encart en bas de l'éditeur VBA.illu1.jpg
J'ai testé les 2 macros sans soucis dans votre fichier test, après avoir supprimé la ligne de texte (ligne 34).
Les 2 macros sont à adapter dans le cas où les tableaux Excel débutent à des lignes et/ou colonnes différentes.
Effectivement, ça marche. ce qui gênait c'était les cellules B34 et C34 fusionnées
Grand merci pour votre aide.
Cordialement
Par contre, quand je transpose cette macro dans mon fichier réel, et en adaptant les adresses des cellules (Range("B12:C") au contexte de mon fichier réel, je provoque malgré tout une erreur d'exécution 13 et je pense que cela est dû à: " .Range(.Cells(12, 6), .Cells(LMax + 11, CMax + 5)).Value = Resultat" que je n'ai pas modifié pour l'adapter à mon fichier. Cependant, je ne sais ce que je dois mettre en remplacement car je ne sais d'où viennent les valeurs 12 et 6 ?
merci de votre aide
Lorsque VBA est en mode débogage (= exécution en pause), il est possible de voir la valeur contenue dans une variable par simple passage de la souris (voir illustration). Sinon, dans
débogage-->ajouter un espion-->expression:Source, vous pouvez consulter le contenu de la variable dans l'encart en bas de l'éditeur VBA.illu1.jpg
J'ai testé les 2 macros sans soucis dans votre fichier test, après avoir supprimé la ligne de texte (ligne 34).
Les 2 macros sont à adapter dans le cas où les tableaux Excel débutent à des lignes et/ou colonnes différentes.
Effectivement, ça marche. ce qui gênait c'était les cellules B34 et C34 fusionnées
Grand merci pour votre aide.
Cordialement
Par contre, quand je transpose cette macro dans mon fichier réel, et en adaptant les adresses des cellules (Range("B12:C") au contexte de mon fichier réel, je provoque malgré tout une erreur d'exécution 13 et je pense que cela est dû à: " .Range(.Cells(12, 6), .Cells(LMax + 11, CMax + 5)).Value = Resultat" que je n'ai pas modifié pour l'adapter à mon fichier. Cependant, je ne sais ce que je dois mettre en remplacement car je ne sais d'où viennent les valeurs 12 et 6 ?
merci de votre aide
Je complète mon dernier message: J'ai compris que les 12 et 6 correspondent aux coordonnées de la 1ere cellule de copie des résultats, je les ai donc adaptées à mon tableau réel, mais j'ai maintenant une erreur et me demande si cela n'est pas dû au fait que mon tableau de données de départ ne contient environ que 1000 lignes remplies sur les 5000 dédiées en théorie lorsque le tableau continuera à se remplir. Mon hypothèse est-elle la bonne ?
La dimension du tableau de départ n'est pas censée jouer quelque chose.
Il faut effectivement adapter le 12 et 6 correspondant aux coordonnées de départ, mais aussi le +11 et +5 correspondant au décalage de la cellule inférieur droit du tableau des résultats, qui tient également compte des coordonnées de la cellule de départ.
Exemple dans votre fichier test, il y a 24 vendeurs, mais comme le 1er est placé en 6ème colonne, le 24ème et dernier se retrouve en 29ème colonne (CMax + 5). Idem pour les lignes (LMax + 11).
La dimension du tableau de départ n'est pas censée jouer quelque chose.
Il faut effectivement adapter le 12 et 6 correspondant aux coordonnées de départ, mais aussi le +11 et +5 correspondant au décalage de la cellule inférieur droit du tableau des résultats, qui tient également compte des coordonnées de la cellule de départ.
Exemple dans votre fichier test, il y a 24 vendeurs, mais comme le 1er est placé en 6ème colonne, le 24ème et dernier se retrouve en 29ème colonne (CMax + 5). Idem pour les lignes (LMax + 11).
Merci de ces précisions; j''avais bien compris et pourtant, j'ai toujours une erreur 13.
Cela peut-il venir du fait que les cellules à la suite des dernières remplies et qui contiennent comme les autres des formules de calcul, aboutissent au résultat qui est un blanc "" et non une valeur numérique ?
La formule est =SI(I1001<>"";RECHERCHEV(I1001;'Motifs et Traitements Rejets'!C$3:D$101;2;FAUX);"")
Les données non nulles de mon tableau des données de départ vont de la ligne 4 à 1000 (sur les 5000 potentielles). J'ai donc utilisé cette valeur "4" qui remplace le "12" de mon exemple.
Toutefois, lorsque l'erreur se produit, en débogage, j'affiche que la ligne 998 a une valeur "". Est-ce l'origine de l'erreur ? et que dois-je faire ?
Qu'en pensez-vous ?
RE
Aussi je vois que le résultat est conforme à mon attente, ce serait parfait si je savais transposer ce tableau dans mon fichier réel, ce qui n'est pas le cas. Pouvez-vous m'y aider car je ne vois pas ni où ni comment on définit la zone de recherche, ni comment on sélectionne les données souhaitées dans le tableau résultat.
Pour le TCD :
il faut mettre la source de données sous forme de tableau structuré sans ligne vide (donc enlever les lignes avec juste des formules, qui ne servent strictement à rien dans un tableau structuré et mettent la pagaille. Dan sun tableau structuré, les nouvelle slignes héritent automatiquement des formules) : se placer dans une cellule de la source, Accueil, Mettre sous forme de tableau : la plage proposée doit comprendre la ligne de titres (Type d'infos et vendeur) et les lignes de données.
Laisser la cellule active dans la cellule du tableau, Outils de tableau, Synthétiser avec un TCD , valider les options proposées
puis dans le volet à droite de construction du TCD, glisser
- le champ Type d'info en étiquette de lignes,
- le champ Vendeur en étiquette de colonne,
- le champ Type d'info en Valeurs puis clic sur ce champ placé, Paramètres de champs de valeurs, Remplacer Somme par Nombre
Cela peut-il venir du fait que les cellules à la suite des dernières remplies et qui contiennent comme les autres des formules de calcul, aboutissent au résultat qui est un blanc "" et non une valeur numérique ?
La formule est =SI(I1001<>"";RECHERCHEV(I1001;'Motifs et Traitements Rejets'!C$3:D$101;2;FAUX);"")
C'est très certainement l'origine du problème. Il faut que toutes les lignes parcourues par la macro contiennent une valeur numérique, ou éventuellement ajouter une condition :
Sub TriCroisé2()
Dim L As Long, C As Integer, Lig As Long
Dim Source() As Variant, Resultat() As Integer, LMax As Long, CMax As Integer
With Sheets("Feuil1")
Source = .Range("B12:C" & .Range("B" & Rows.Count).End(xlUp).Row).Value 'Affecte les données au tableau source
LMax = Application.Max(.Range("B:B")) 'Type d'info max
CMax = Application.Max(.Range("C:C")) 'Vendeur max
ReDim Resultat(1 To LMax, 1 To CMax) 'Dimensionnement du tableau vierge
For Lig = LBound(Source) To UBound(Source) 'Boucle sur données source
If IsNumeric(Source(Lig, 1)) And IsNumeric(Source(Lig, 2)) Then
L = Source(Lig, 1)
C = Source(Lig, 2)
Resultat(L, C) = Resultat(L, C) + 1
End If
Next Lig
.Range(.Cells(12, 6), .Cells(LMax + 11, CMax + 5)).Value = Resultat
End With
'[Facultatif] : code supplémentaire pour supprimer les 0 des résultats
End SubEncore grand merci pour ces dernières indications qui étaient très clairement exposées. Mon problème est résolu.
RE
Aussi je vois que le résultat est conforme à mon attente, ce serait parfait si je savais transposer ce tableau dans mon fichier réel, ce qui n'est pas le cas. Pouvez-vous m'y aider car je ne vois pas ni où ni comment on définit la zone de recherche, ni comment on sélectionne les données souhaitées dans le tableau résultat.
Pour le TCD :
il faut mettre la source de données sous forme de tableau structuré sans ligne vide (donc enlever les lignes avec juste des formules, qui ne servent strictement à rien dans un tableau structuré et mettent la pagaille. Dan sun tableau structuré, les nouvelle slignes héritent automatiquement des formules) : se placer dans une cellule de la source, Accueil, Mettre sous forme de tableau : la plage proposée doit comprendre la ligne de titres (Type d'infos et vendeur) et les lignes de données.
Laisser la cellule active dans la cellule du tableau, Outils de tableau, Synthétiser avec un TCD , valider les options proposées
puis dans le volet à droite de construction du TCD, glisser
- le champ Type d'info en étiquette de lignes,
- le champ Vendeur en étiquette de colonne,
- le champ Type d'info en Valeurs puis clic sur ce champ placé, Paramètres de champs de valeurs, Remplacer Somme par Nombre
Grand merci pour ces explications qui me seront utiles car je ne connaissais rien des TCD. Encore faut-il que je m'entraîne pour pouvoir exploiter correctement cette fonctionnalité.