Dzień dobry,
Czy jest sposób na utoworzenie tabeli przestawnej, która będzie brać dane z każdej zakładki, w których jest ten sam układ kolumn? Zamysł jest taki, żeby tworzyło się to automatycznie, może być za pomocą kodu VBA. Zakładki będą dochodzić nowe, więc taka tabela musiałaby dodawać je do istniejącego już zakresu.
Dziękuję za pomoc! :)
Jeżeli dane w arkuszach masz w formie tabel (lub możesz je na takie zamienić np. skrótem ctrl+T), to te kilka kroków w Power Query załatwi sprawę:
1) Zaznacz dowolną komórkę w pierwszej tabeli źródłowej i z menu wybierz Dane -> Z tabeli.
2) W edytorze PQ usuń krok Zmieniono typ, a w kroku Źródło zostaw tylko funkcję Excel.CurrentWorkbook() (skasuj tekst po nawiasach; domyślnie będzie to np. '{[Name="Tabela1"]}[Content]').
3) Kliknij na ikonę dwóch strzałek przy nazwie kolumny Content i odznacz na dole opcję "Użyj oryginalnej nazwy...". W efekcie powinieneś otrzymać zapytanie jak poniżej:
let
Źródło = Excel.CurrentWorkbook(),
#"Rozwinięty element Content" = Table.ExpandTableColumn(Źródło, "Content", {"kol1", "kol2", "kol3"}, {"kol1", "kol2", "kol3"})
in
#"Rozwinięty element Content"
4) Zamknij edytor PQ. Jeżeli wynikowa tabela załaduje Ci się do nowego arkusza, usuń go.
5) Z menu wybierz Wstawianie -> Tabela przestawna i w opcji Użyj zewnętrznego źródła danych wskaż połączenie PQ.
Teraz po dodaniu nowego arkusza z danymi wystarczy zamienić je na tabelę (ctrl+T) i odświeżyć tabelę przestawną.
Problemy mogą pojawić się, gdy w arkuszu dodatkowo zdefiniujesz nazwę zakresu lub wstawisz tabelę z innym układem danych, której nie będziesz chciał włączać do tabeli przestawnej, ale można to obejść np. zakładając filtr na układ danych według jakiejś tabeli wzorcowej (w poniższym przykładzie to Tabela1). Dodatkowy plus: nie trzeba będzie poprawiać kwerendy przy zmianie układu/nazw kolumn, oczywiście o ile zadzieje się ona dla wszystkich tabel źródłowych.
let
Źródło = Excel.CurrentWorkbook(),
kolumny = Table.ColumnNames(Źródło{[Name="Tabela1"]}[Content]),
#"Przefiltrowano wiersze" = Table.SelectRows(Źródło, each Table.ColumnNames([Content]) = kolumny),
#"Rozwinięty element Content" = Table.ExpandTableColumn(#"Przefiltrowano wiersze", "Content", kolumny)
in
#"Rozwinięty element Content"
PS: Jeżeli w jakimkolwiek zadaniu dopuszczasz zastosowanie VBA, to nigdy nie musisz pytać "czy", od razu zacznij od "jak" :)
Dodaj poniższy kod do modułu Ten_skoroszyt, będzie się uruchamiał każdorazowo przy dodaniu arkusza. W tej postaci ma jedną słabość: jeżeli na wcześniejszych arkuszach wystąpią jakieś dane poza tabelami, które chcesz skopiować (szczególnie pod nimi), może to zepsuć tabelę wynikową. Można to obejść np. przez sprawdzenie szerokości nagłówka pierwszej tabeli i badając jakąś zawsze niepustą kolumnę w każdej z nich, ale to już kosmetyka.
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim r As Long, i As Byte, src_Sh As Worksheet
r = 1
i = 0
For Each src_Sh In ThisWorkbook.Sheets
If Sh.Name = src_Sh.Name Then Exit For
With src_Sh.UsedRange
Sh.Cells(r, 1).Resize(.Rows.Count - i, .Columns.Count).Value2 = .Offset(i).Resize(.Rows.Count - i).Value2
r = r + .Rows.Count - i
End With
i = 1
Next
End Sub
Nie zapomnij zapisać pliku jako xlsm lub xlsb :)
