Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 73235 Skopiuj do schowka Tworzenie tabeli przestawnej przy pomocy VBA
Autor Wiadomość
Leon M 
Stały bywalec Excelforum


Wersja: Win Office 2016
Posty: 448
Wysłany: 14-08-2022, 22:54   Tworzenie tabeli przestawnej przy pomocy VBA

Szanowni Forumowicze,

Zwracam się z wielką prośbą o napisanie makra, które tworzyć będzie 2 tabele przestawne, dla których źródłem danych będzie dynamiczny zakres arkuszowy (zmienna ilość wierszy w zależności od ilości danych).
Założenie jest takie, że po zmianie danych w tym zakresie wywołanie procedury spowoduje utworzenie na nowo tabeli przestawnej obejmującej nową zawartość danych w zakresie źródłowym.

W załączeniu zamieszczam plik z zawartością, na której przewidziane jest działanie makra.
W arkuszu "Produkt" znajduje się krótki opis zadania.

Za odpowiedzi z góry serdecznie dziękuję.

Tabela przestawna w VBA.xlsm
Pobierz Plik ściągnięto 11 raz(y) 91.12 KB

ID posta: 419680 Skopiuj do schowka
 
 
Tajan


Pomógł: 5091 razy
Posty: 11145
Wysłany: 15-08-2022, 10:03   

A czy konieczne jest tworzenie od podstaw tabeli przestawnej? Nie wystarczy uaktualnienie zakresu źródła danych dla istniejącej tabeli przestawnej?
Przykładowo, dodaj na końcu procedury "WyszukajProdukt":
Kod:
With Sheets("Analiza1").PivotTables(1)
        .ChangePivotCache _
        ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=Range("D5:F" & ostW), _
        Version:=xlPivotTableVersion15)
        .RefreshTable
End With
co po zmianie zawartości arkusza "Produkty" spowoduje odświeżenie i uaktualnienie tabeli przestawnej w arkuszu "Analiza1".
ID posta: 419683 Skopiuj do schowka
 
 
Leon M 
Stały bywalec Excelforum


Wersja: Win Office 2016
Posty: 448
Wysłany: 15-08-2022, 10:59   

Tajan, bardzo dziękuję za odpowiedź.

Tak, wystarczającą będzie możliwość odświeżenia istniejących tabel. Nie pomyślałem o takim sposobie rozwiązania zadania.

Chciałbym zapytać, czy PivotTables(1) to jest pierwsza tabela przestawna w arkuszu, czy w całym skoroszycie. Ponadto, czy można tabelę przestawną opatrzyć indywidualną nazwą i jeśli tak, to jak rzecz wykonać?
ID posta: 419686 Skopiuj do schowka
 
 
Tajan


Pomógł: 5091 razy
Posty: 11145
Wysłany: 15-08-2022, 11:40   

Obiekt "PivotTables" odnosi się do arkusza. Zamiast poprzez indeks, czyli "Sheets("Analiza1").PivotTables(1)", możesz do tabeli odwoływać się poprzez jej nazwę, np. "Sheets("Analiza1").PivotTables("MojaTabela"), przy czym jej nazwę zmienisz w menu "Analiza tabeli przestawnej", opcja: "Tabela przestawna", jak widać to w załączonym obrazie.

NazwaTabeli.png
Plik ściągnięto 15 raz(y) 52.65 KB

ID posta: 419687 Skopiuj do schowka
 
 
Leon M 
Stały bywalec Excelforum


Wersja: Win Office 2016
Posty: 448
Wysłany: 15-08-2022, 13:32   

Tajan, bardzo dziękuję za przedstawione wyjaśnienia.
ID posta: 419697 Skopiuj do schowka
 
 
Leon M 
Stały bywalec Excelforum


Wersja: Win Office 2016
Posty: 448
Wysłany: 20-08-2022, 18:34   

Szanowni Forumowicze,

Chciałbym dopytać odnośnie kodu przedstawionego powyżej przez kolegę Tajana.

Otóż, kod ten dotyczył przypadku, gdy źródłem danych dla odświeżanej tabeli przestawnej był zwykły zakres arkuszowy.
Chciałbym poprosić o przedstawienie, jaka byłaby składnia tej części kodu, która odnosi się do wskazania źródła danych, w przypadku, gdy źródłem tym byłaby tabela.

Za odpowiedzi z góry bardzo dziękuję.
ID posta: 419828 Skopiuj do schowka
 
 
Tajan


Pomógł: 5091 razy
Posty: 11145
Wysłany: 20-08-2022, 20:20   

Po prostu jako źródło danych wskaż daną tabelę. Czyli w kodzie:
Kod:
With Sheets("Analiza1").PivotTables(1)
        .ChangePivotCache _
        ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=Range("D5:F" & ostW), _
        Version:=xlPivotTableVersion15)
        .RefreshTable
End With
fragment:
Kod:
SourceData:=Range("D5:F" & ostW)
zamień na odwołanie do tabeli, np.:
Kod:
SourceData:=ActiveSheet.ListObjects(1)
czy
Kod:
SourceData:=ActiveSheet.ListObjects("Tabela1")
ID posta: 419829 Skopiuj do schowka
 
 
Leon M 
Stały bywalec Excelforum


Wersja: Win Office 2016
Posty: 448
Wysłany: 20-08-2022, 22:17   

Tajan, bardzo dziękuję za wyjaśnienia.
ID posta: 419834 Skopiuj do schowka
 
 
Leon M 
Stały bywalec Excelforum


Wersja: Win Office 2016
Posty: 448
Wysłany: 23-08-2022, 07:54   

Chciałbym jeszcze poprosić o wyjaśnienie znaczenia poniższej linii kodu:
Kod:
Version:=xlPivotTableVersion15)


Za odpowiedzi z góry bardzo dziękuję.
ID posta: 419870 Skopiuj do schowka
 
 
Leon M 
Stały bywalec Excelforum


Wersja: Win Office 2016
Posty: 448
Wysłany: 23-08-2022, 08:10   

Pojawiła się jeszcze jedna kwestia, względem której chciałbym zadać pytanie.

Otóż, jak można pozyskać wartość, która jest liczbą tabeli przestawnych znajdujących się w danym arkuszu?
ID posta: 419871 Skopiuj do schowka
 
 
ple4
Stały bywalec Excelforum


Wersja: Win Office 2003
Pomógł: 58 razy
Posty: 261
Wysłany: 23-08-2022, 09:25   

Leon M napisał/a:
znaczenia poniższej linii kodu:
Version:=xlPivotTableVersion15)

Leon M napisał/a:
liczbą tabeli przestawnych znajdujących się w danym arkuszu


Ot słodkie lenistwo ...

Ad1. Np. tutaj:
https://docs.microsoft.com/en-us/office/vba/api/excel.xlpivottableversionlist

Ad 2. Jest w pomocy podręcznej
Kod:
    Dim npvt&
    npvt = ThisWorkbook.Worksheets("Arkusz2").PivotTables.Count
ID posta: 419875 Skopiuj do schowka
 
 
Kaper 



Zaproszone osoby: 2
Wersja: Win Office 365
Pomógł: 4362 razy
Posty: 8612
Wysłany: 23-08-2022, 09:27   

PivotTables w danym arkuszu to kolekcja, więc zadziała metoda Count
np. sprawdź wynik
Kod:
msgbox activesheet.pivottables.count
_________________
Kaper Jej Królewskiej Mości :boss

Jeśli dane będą torturowane dość długo, przyznają się do wszystkiego
ID posta: 419876 Skopiuj do schowka
 
 
Leon M 
Stały bywalec Excelforum


Wersja: Win Office 2016
Posty: 448
Wysłany: 23-08-2022, 11:19   

ple4, Kaper,

Bardzo dziękuję za kod i wyjaśnienia.

Wyłoniła się kolejna kwestia związana z odświeżaniem tabeli przestawnej, z którą nieporadnie się borykam.
Otóż chodzi o formatowanie warunkowe tabeli.
W przypadku, gdy zmiana danych źródłowych powoduje po odświeżeniu dodanie wierszy w tabeli przestawnej, powstałe w ten sposób nowe pola nie otrzymują formatowania warunkowego, jakie mają komórki w danej kolumnie tabeli.

Zatem moje pytanie jest takie: jak w VBA nadać nowo powstałym polom formatowanie warunkowe, jakim były sformatowane poszczególne kolumny przed odświeżeniem?

Za odpowiedzi bardzo dziękuję.
ID posta: 419880 Skopiuj do schowka
 
 
ple4
Stały bywalec Excelforum


Wersja: Win Office 2003
Pomógł: 58 razy
Posty: 261
Wysłany: 24-08-2022, 10:12   

... :-/ ...
Tabela przestawna, tak jak i obiekt tabeli ('ListObject') w jednym z twoich innych tematów, również ma właściwość "DataBodyRange" - zastosuj ją po odświeżeniu danych - coś a'la:
Kod:
Sub pomidorowa_z_grochem()
    Dim sh As Worksheet
    Dim pvttbl As PivotTable
    Dim okeyshadoky As Boolean
   
    Set sh = ThisWorkbook.Worksheets("Analiza1")
    Set pvttbl = sh.PivotTables(1)
   
    okeyshadoky = pvttbl.RefreshTable
    If Not okeyshadoky Then Exit Sub
   
    With pvttbl.DataBodyRange
        ' ... Formatowanie warunkowe, inne, etc.
    End With
End Sub
ID posta: 419921 Skopiuj do schowka
 
 
Leon M 
Stały bywalec Excelforum


Wersja: Win Office 2016
Posty: 448
Wysłany: 24-08-2022, 12:16   

ple4,

Bardzo dziękuję za kod i wyjaśnienia.

Chciałbym zapytać, czy poniższe linie kodu:
Kod:
   
okeyshadoky = pvttbl.RefreshTable
If Not okeyshadoky Then Exit Sub

Mają za zadanie sprawdzić, czy miało miejsce odświeżenie tablicy?
ID posta: 419937 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.wip.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe