Jak aktualizovat mezipaměť pivot v Excelu VBA

Mezipaměť pivot v Excelu VBA je dočasná tabulka vytvořená na základě zdrojových dat a používaná k vytváření sestav a analýze dat. Někdy je však nutné aktualizovat mezipaměť pivot, aby bylo možné provést změny ve zdrojových datech nebo aktualizovat výsledky analýzy.

K aktualizaci mezipaměti pivot v Excelu VBA lze použít různé metody a vlastnosti. Jednou z takových metod je metoda RefreshTable, která aktualizuje tabulku a zahrnuje změny provedené ve zdrojových datech nebo návrhu obvodu externí databáze.

Příklad kódu:

Sub RefreshPivotTable()

Dim ws As Worksheet

Dim pt As PivotTable

Set ws = ThisWorkbook.Worksheets("Sheet1")

Set pt = ws.PivotTables("PivotTable1")

pt.RefreshTable

End Sub

Tento příklad kódu poskytuje obecný přístup k aktualizaci mezipaměti pivot v Excelu VBA. Po oznámení proměnných a přiřazení požadovaných hodnot je pro aktualizaci dat povolána metoda RefreshTable.

Pomocí metody RefreshTable a dalších metod a vlastností je tedy možné aktualizovat mezipaměť pivot v Excelu VBA a získat aktuální výsledky analýzy dat.

Krok 1: Otevřete Visual Basic editor

1. Otevřete Excel soubor obsahující tabulku s pivot.
2. V nabídce vyberte "vývojář" - to může být nutné aktivovat v nastavení aplikace Excel, pokud není vidět.
3. V části vývojář klikněte na tlačítko" Visual Basic " - tím se otevře editor.
4. V editoru se zobrazí okno projekty, které obsahuje seznam všech otevřených knih a modulů.
5. Vyberte knihu, která obsahuje tabulku s pivotem, a poklepáním na ni otevřete okno s kódem pro tuto knihu.

Nyní jste připraveni napsat kód ve editoru Visual Basic pro aktualizaci mezipaměti pivot v Excelu VBA.

Krok 2: Najděte kód pro aktualizaci mezipaměti pivot

Chcete-li aktualizovat mezipaměť pivot v Excelu VBA, musíte najít příslušný kód. Kód pro aktualizaci mezipaměti pivot může vypadat takto:

To je jen několik příkladů kódu pro aktualizaci mezipaměti pivot v Excelu VBA. Pokud chcete aktualizovat mezipaměť všech pivotových tabulek na něm, musíte nahradit "Název_pivot_tablity" skutečným názvem pivotových tabulek nebo zadat konkrétní list.

Nyní, když máte kód pro aktualizaci mezipaměti pivot, můžete jej použít ve svém programu na VBA nebo v postupu události k automatické aktualizaci mezipaměti při určitých událostech.

Krok 3: Změňte kód a aktualizujte požadovanou mezipaměť

Po vytvoření mezipaměti pivot jej možná budete muset aktualizovat, abyste zobrazili nová data nebo provedli změny ve stávajících datech. K tomu budete muset změnit kód Excel VBA.

1. V modulu VBA, který se otevře, vyhledejte funkci, ve které je vytvořena mezipaměť pivot.

2. Najděte řádek kódu, který je zodpovědný za vytvoření mezipaměti pivot, může vypadat takto:

Set ptCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wsData.Range("A1").CurrentRegion)

3. Chcete-li aktualizovat mezipaměť pivot, nahraďte tento řádek kódu následujícím:

Set ptCache = ActiveSheet.PivotTables("Имя_таблицы").PivotCache

4. Zde místo" Jméno_tablic " zadejte název tabulky pivot, kterou chcete aktualizovat.

5. Uložte změny v kódu a zavřete okno VBA.

Váš kód nyní aktualizuje pouze požadovanou mezipaměť pivot. Když spustíte kód, aktualizuje data tabulky pivot podle zadané mezipaměti.

Krok 4: Uložit změny a zavřít Visual Basic editor

Jakmile provedete všechny požadované změny kódu, uložte změny a zavřete Editor Visual Basic.

Chcete-li uložit změny kódu, klikněte na tlačítko Uložit na panelu nástrojů Visual Basic editoru nebo použijte kombinaci kláves "Ctrl + S". Můžete také vybrat položku nabídky Soubor → Uložit.

Po uložení změn zavřete Editor Visual Basic. Chcete-li to provést, můžete kliknout na křížek v pravém horním rohu okna editoru nebo se pomocí kombinace kláves "Alt + Q" vrátit zpět na pracovní list aplikace Excel.

V tomto kroku jste dokončili práci s editorem Visual Basic a jste připraveni otestovat změny ve vašem sešitu Excel.

Krok 5: Spusťte makro pro aktualizaci mezipaměti

Jakmile do aplikace Excel VBA přidáte kód pro aktualizaci mezipaměti pivot, můžete spustit makro, abyste jej mohli spustit. To umožní aktualizovat data v souhrnné tabulce a aktualizovat všechna související data a grafy.

Chcete-li spustit makro pro aktualizaci mezipaměti, postupujte takto:

1. Otevřete sešit aplikace Excel obsahující kontingenční tabulku, pro kterou chcete aktualizovat mezipaměť.
2. Stiskněte ALT + F11 otevřít Editor VBA.
3. V editoru VBA vyhledejte svůj projekt v okně" Project Explorer " vpravo.
4. Otevřete strom projektu a najděte modul, který obsahuje kód pro aktualizaci mezipaměti pivot.
5. Kliknutím dvojitým kliknutím na modul jej otevřete.
6. Stiskněte F5 nebo vyberte "Run" z nabídky "Debug" pro spuštění makra.

Po provedení makra bude kontingenční tabulka aktualizována a všechna související data a grafy budou také aktualizovány.

Ujistěte se, že si po spuštění makra uložíte sešit aplikace Excel pro aktualizaci mezipaměti, abyste uložili všechny změny.

Krok 6: Zkontrolujte, zda je mezipaměť úspěšně aktualizována

Jakmile aktualizujete mezipaměť pivot v Excelu VBA, je důležité zkontrolovat, zda byla aktualizace úspěšná. K tomu lze provést několik akcí:

1. Otevřete list obsahující souhrnnou tabulku, kterou jste aktualizovali. Ujistěte se, že hodnoty v souhrnné tabulce se změnily podle provedených změn v původních datech. Pokud se hodnoty v souhrnné tabulce nezměnily, Zkontrolujte správnost aktualizace mezipaměti a správnost původních dat.
2. Zkontrolujte, zda se také aktualizovaly vzorce využívající hodnoty z kontingenční tabulky. Pokud vzorce neposkytují očekávané výsledky, podívejte se na chyby.
3. Ujistěte se, že kontingenční tabulka a vzorce jsou správně zobrazeny ve všech listech, které závisí na aktualizované kontingenční tabulce. Pokud se hodnoty na některých listech neaktualizovaly nebo se nezobrazovaly správně, zkontrolujte vazby mezi listy a aktualizujte mezipaměť na těchto listech.

Pokud jsou všechny kontroly úspěšné a hodnoty v souhrnné tabulce a vzorcích jsou správné, lze předpokládat, že mezipaměť byla úspěšně aktualizována.

Krok 7: Použít aktualizace na všechna související data

Jakmile aktualizujete data v mezipaměti pivot, musíte tyto aktualizace použít na všechna související data ve vašem dokumentu Excel. Zobrazí se tedy aktuální hodnoty a souhrnné tabulky zobrazí nová data.

K tomu můžete ve svém postupu VBA použít následující kód:

Sub ApplyPivotUpdates()
Dim pivotCache As PivotCache
Dim pivotTable As PivotTable
Dim worksheet As Worksheet

Set worksheet = ThisWorkbook.Worksheets ("název listu")

'Identifikujeme mezipaměť pivot
Set pivotCache = ThisWorkbook.PivotCaches ("název vaší mezipaměti pivot")

For Each pivotTable In worksheet.PivotTables
'Nastavte zdroj dat
pivotTable.ChangePivotCache pivotCache
'Aktualizujeme tabulku souhrnných dat
pivotTable.RefreshTable
Next pivotTable
End Sub

V tomto kódu identifikujeme mezipaměť pivot, kterou jsme aktualizovali v předchozím kroku, a poté tyto aktualizace aplikujeme na všechny související tabulky souhrnných dat na listu pomocí cyklu. Tento kód můžete také nakonfigurovat tak, aby aplikoval aktualizace na všechny tabulky souhrnných dat v celém souboru aplikace Excel přidáním smyčky napříč všemi listy.

Po provedení tohoto postupu budete moci vidět, jak se aktualizovaná data odrážejí v souhrnných tabulkách.