Synthèse des onglets
Bonjour à tous,
Dans un fichier Excel regroupant des dizaines d'onglets à l'agencement identique, je cherche à créer un onglet de synthèse qui restitue le contenu des cellules en fonction du nom de l'onglet, du nom de la ligne et du nom de la colonne. J'ai créé un fichier exemple que je joins ici.
Je suis conscient que ce site regorge de données et que peut être la réponse à ma question s'y trouve mais, d'une part, je manque de temps et d'autre part, les sujets abordés et les solutions proposés sont bien trop complexes pour mon niveau.
Je vous remercie par avance pour l'aide que vous pourriez m'apporter.
Mathieu
Bonjour et bienvenue,
Un exemple Power Query.
Cdlt.
Merci pour vos réponses rapides! Dès que j'en aurais le temps, j'essaierai de comprendre comment vous avez procédé.
Je complète ma demande suite aux réponses qui m'ont été apportées, car mon fichier "exemple" initial était trop simple.
Je voudrais que l'onglet synthèse récupère les données des autres onglets en sélectionnant uniquement les lignes demandées. Dans l'exemple joint, la ligne "Olive" doit être ignorée.
Les onglets de données peuvent être plusieurs centaines alors je ne veux pas y toucher. En revanche, on peut modifier l'onglet synthèse au besoin.
Merci d'avance pour votre aide.
Bonjour,
Une mise à jour de la proposition Power Query et Tableau Croisé Dynamique (TCD).
Le champ "Olive" est masqué dans le TCD.
A te relire.
Cdlt.
Merci Jean-Eric!
J'aimerais avoir les mêmes compétences. Cela me faciliterait la vie.
Malheureusement, un message d'erreur apparaît si j'essaie d'utiliser ton fichier (Excel 2013).
Bon, grâce à vous, je me suis intéressé aux macros auxquelles je n'avais jamais touché et j'ai trouvé comment faire.
C'est un peu laborieux car je dois dupliquer mes formules pour chaque feuilles, mais ça me suffit pour l'instant.
Merci pour vos réponses!
Bonjour,
Pour Excel 2013, il faut installer le complément gratuit Power Query de Microsoft.
Power Query est natif pour les versions ultérieures.
Cdlt.
Merci pour ta précision Jean-Eric,
Je n'ai pas les droits pour installer des modules complémentaires, et on ne me les donnera pas. Je vais donc me contenter de ma version basique de 2013 et fonctionner avec VBA.
En attendant, j'aimerais vous montrer comment j'ai procédé pour arriver à mes fins. La solution est très laborieuse et je suis sûr que vous aurez des idées pour automatiser certains processus.
Worksheets("Onglet1").Range("B3:G3").Copy _
Destination:=Worksheets("OngletSynthèse").Range("B5")
Worksheets("Ongel2").Range("B3:G3").Copy _
Destination:=Worksheets("OngletSynthèse").Range("B6")
Etc. jusqu'à OngletX
Worksheets("Onglet1").Range("B3:G3").Copy _
Destination:=Worksheets("OngletSynthèse").Range("H5")
Worksheets("Ongel2").Range("B3:G3").Copy _
Destination:=Worksheets("OngletSynthèse").Range("H6")
Etc. jusqu'à OngletX
En résumé, je copie-colle des lignes de code. Je change simplement le nom des onglets d'origine et les cases de destination dans l'onglet de synthèse. Les cases des onglets d'origine et l'onglet de destination (synthèse ne change pas).
C'est fastidieux : au final, je gagne quand même beaucoup de temps mais j'en perds un peu quand même à bricoler les lignes de code.
Autre point négatif : les onglets de données sont tous identique (toujours les mêmes titres de ligne et de colonne) mais il arrive qu'il y ait des lignes en plus ou en moins. Cela m'oblige à reprendre chaque onglet pour que les lignes qui m'intéressent se trouvent bien aux numéros indiqué dans mon code...
Bon week end,
Hello,
modifier cette variable en ajoutant ou supprimant les chaines à exclure en respectant la même syntaxe
varexcluarray = Array("Olive", "Salade")Voici
Sub synthese()
Const strsh_synth As String = ("Synthèse")
Dim strname_sheet As String
Dim wkssh_temp As Worksheet
Dim rngsheet As Range
Dim varcells As Variant, vartab_temp As Variant, varexcluarray As Variant
Dim lnglast_row_synth As Long, lnglastcol_synth As Long, i As Long, lngnb_indi As Long, _
lngrow_sh_synth As Long, y As Long, lngcol_sh_synt2 As Long
Dim bytcol_sh_synt As Byte, bytf_row_sh_synt As Byte
Dim objmondico As Object, objmondico2 As Object
bytcol_sh_synt = 1
bytf_row_sh_synt = 3
varexcluarray = Array("Olive", "Salade")
With Sheets(strsh_synth)
lnglastcol_synth = Sheets(strsh_synth).Cells(bytf_row_sh_synt - 1, Columns.Count).End(xlToLeft).Column
Set objmondico = CreateObject("Scripting.Dictionary")
Set objmondico2 = CreateObject("Scripting.Dictionary")
vartab_temp = .Range(.Cells(bytf_row_sh_synt - 1, bytcol_sh_synt + 1), .Cells(bytf_row_sh_synt - 1, lnglastcol_synth))
For i = LBound(vartab_temp, 2) To UBound(vartab_temp, 2)
objmondico(vartab_temp(1, i)) = ""
Next i
vartab_temp = .Range(.Cells(bytf_row_sh_synt - 2, bytcol_sh_synt + 1), .Cells(bytf_row_sh_synt - 2, lnglastcol_synth))
For i = LBound(vartab_temp, 2) To UBound(vartab_temp, 2)
objmondico2(vartab_temp(1, i)) = ""
Next i
End With
lngnb_indi = objmondico.Count
For Each wkssh_temp In ThisWorkbook.Worksheets
If wkssh_temp.Name <> strsh_synth Then
With Sheets(strsh_synth)
lnglast_row_synth = .Cells(Rows.Count, bytcol_sh_synt).End(xlUp).Row
Set rngsheet = .Range(.Cells(bytf_row_sh_synt, bytcol_sh_synt), .Cells(lnglast_row_synth, bytcol_sh_synt))
For Each varcells In rngsheet
If varcells = wkssh_temp.Name Then lngrow_sh_synth = varcells.Row
Next varcells
y = 2
lngcol_sh_synt2 = bytcol_sh_synt + 1
For i = 1 To objmondico2.Count
Set rngsheet = wkssh_temp.Range(wkssh_temp.Cells(y, bytcol_sh_synt + 1), wkssh_temp.Cells(y, bytcol_sh_synt + lngnb_indi))
If Not wkssh_temp.Cells(y, bytcol_sh_synt) = varexcluarray(0) Then
.Cells(lngrow_sh_synth, lngcol_sh_synt2).Resize(, lngnb_indi).Value2 = rngsheet.Value2
y = y + 1
lngcol_sh_synt2 = lngcol_sh_synt2 + lngnb_indi
Else:
For Each varcells In varexcluarray
If wkssh_temp.Cells(y, bytcol_sh_synt) = varcells Then y = y + 1
Next varcells
End If
Next i
End With
End If
Next wkssh_temp
End SubMerci de ton aide, vraiment, mais ça ne fonctionne pas. En fait, j'aimerais en même temps comprendre ce que je fais, mais l'écart de niveau est trop important. Je voudrais y aller étape par étape, quitte à faire quelque chose d'un peu laborieux.
Pour commencer, mettons que je veuille, pour 20 onglets du classeur (sauf "synthèse"), copier les cellules B3 à G3 et les coller dans la plage de cellule B3:G22 de l'onglet "synthèse", est-ce qu'on s'approcherait de ceci:
For Each Worksheet Worksheets.Range("B3:G3").Copy _
Destination:=Worksheets("Synthèse").Range("B4:G22")
Hello,
Le plus simple pour 20 onglets c'est de mettre la feuille synthèse en première position et de faire ce code :
dim i as byte
for i = 2 to 21
sheets(i).range("B3:G3").copy sheets("synbthese").range("B3:G22")
next i Ou en dernière position :
dim i as byte
for i = 1 to 20
sheets(i).range("B3:G3").copy sheets("synbthese").range("B3:G22")
next i Merci de ta réponse.
J'essaierai demain car à la maison j'ai un MAC...
Bonjour,
Le problème avec ce code est qu'il écrase les précédentes données, et l'on se retrouve avec celles de la dernière feuilles dupliquées.
J'ai essayé de modifier le code comme cela:
Dim i As Byte
Dim a As Byte
a = 4
For i = 2 To 3
Sheets(i).Range("B3:G3").Copy Sheets("SYNT").Range("Ba:Ga")
a = a + 1
Next iMais ça ne fonctionne pas. Je me doute que ce qui cloche est l'appellation Ba et Ga qui n'est pas reconnu.
Hello,
Pour adapter la plage via une variable c'est comme ceci :
Dim i As Byte
Dim a As Byte
a = 4
For i = 2 To 3
Sheets(i).Range("B3:G3").Copy Sheets("SYNT").Range("B" & a & ":G" & a)
a = a + 1
Next iPour info la déclaration d'une variable en "byte" ne peut pas excéder un nombre de + de 255.
sinon utiliser une variable de type "integer" qui va jusque 32 000 environ ou le type "long" qui va jusque 2 millions je crois.
Merci,
C'est simple et cela me convient pour ce qu'il y a à faire pour le moment.