Obsah
Slicery v kontingenčních tabulkách lze použít nejen klasickým způsobem – k filtrování zdrojových dat, ale také k přepínání mezi různými typy výpočtů v oblasti hodnot:
Implementace je velmi snadná – vše, co potřebujete, je pár vzorců a pomocná tabulka. No, to vše uděláme ne v obvyklém souhrnu, ale v souhrnu sestaveném podle datového modelu Power Pivot.
Krok 1. Připojení doplňku Power Pivot
Pokud se karty doplňku Power Pivot v Excelu nezobrazují, budete je muset nejprve povolit. K tomu existují dvě možnosti:
- Tab vývojka - knoflík COM doplňky (Vývojář — doplňky COM)
- Soubor – Možnosti – Doplňky – Doplňky COM – Přejít (Soubor — Možnosti — Doplňky — Doplňky COM — Přejít na)
Pokud to nepomůže, zkuste restartovat Microsoft Excel.
Krok 2: Načtěte data do datového modelu Power Pivot
Jako počáteční data budeme mít dvě tabulky:
První je tabulka s tržbami, podle které později sestavíme souhrn. Druhá je pomocná tabulka, kde se zadávají názvy pro tlačítka budoucího řezu.
Obě tyto tabulky je třeba převést na „chytré“ (dynamické) pomocí klávesové zkratky Ctrl+T nebo tým Domů – Formát jako tabulka (Domů — Formátovat jako tabulku) a je žádoucí dát jim rozumná jména na kartě Stavitel (Design). Ať je to např. Prodej и Služby.
Poté je třeba každou tabulku načíst do datového modelu – k tomu používáme kartu Powerpivot tlačítko Přidat do datového modelu (Přidat do datového modelu).
Volají se vypočítaná pole v kontingenční tabulce podle datového modelu opatření. Vytvořme míru, která zobrazí název stisknutého tlačítka na budoucím řezu. Chcete-li to provést, v kterékoli z našich tabulek vyberte libovolnou prázdnou buňku ve spodním panelu výpočtu a do řádku vzorců zadejte následující konstrukci:
Zde je na prvním místě název opatření (Stisknuté tlačítko) a poté za dvojtečkou a rovnítkem vzorec pro výpočet pomocí funkce HODNOTY DAX zabudovaný do Power Pivot.
Pokud to zopakujete ne v Power Pivot, ale v Power BI, pak dvojtečka není potřeba a místo toho HODNOTY můžete využít jeho modernější obdobu – funkci VYBRANÁ HODNOTA.
Chybám ve spodní části okna, které se objeví po zadání vzorce, nevěnujeme pozornost – vznikají, protože ještě nemáme souhrn a výřez, ve kterém se něco kliká.
Dalším krokem je vytvoření míry pro různé možnosti výpočtu v závislosti na hodnotě předchozí míry Stisknuté tlačítko. Zde je vzorec trochu složitější:
Pojďme si to rozebrat kousek po kousku:
- funkce SWITCH – obdoba vnořeného IF – kontroluje splnění zadaných podmínek a vrací různé hodnoty v závislosti na splnění některé z nich.
- funkce SKUTEČNÝ() – dává logickou „true“, takže podmínky zkontrolované později funkcí SWITCH fungují pouze v případě, že jsou splněny, tj. pravda.
- Poté zkontrolujeme hodnotu stisknutého tlačítka a vypočítáme konečný výsledek pro tři různé možnosti – jako součet nákladů, průměrné kontroly a počtu unikátních uživatelů. Chcete-li počítat jedinečné hodnoty, použijte funkci DISTINCTCOUNTa pro zaokrouhlování – KOLO.
- Pokud není splněna žádná z výše uvedených tří podmínek, zobrazí se poslední argument funkce SWITCH – nastavíme ji jako figurínu pomocí funkce PRÁZDNÝ().
Krok 5. Vytvoření souhrnu a přidání řezu
Zbývá se vrátit z Power Pivotu do Excelu a tam sestavit kontingenční tabulku pro všechna naše data a měření. Chcete-li to provést, v okně Power Pivot zapněte Hlavní příkaz vybrat tab souhrnná tabulka (Domů — kontingenční tabulka).
Pak řekl:
- Házíme pole Produktový z tabulky Prodej do oblasti Řádky (Řádky).
- Házet tam pole Výsledek z tabulky Služby.
- Klikněte pravým tlačítkem na pole Výsledeka vybrat tým Přidejte jako plátek (Přidat jako průřez).
- Házení druhého taktu Uzavření z tabulky Služby do oblasti Hodnoty (hodnoty).
Zde jsou ve skutečnosti všechny triky. Nyní můžete kliknout na tlačítka výřezu – a součty v kontingenční tabulce se přepnou na funkci, kterou potřebujete.
Krása 🙂
- Výhody Pivot by Data Model
- Analýza plánů v kontingenční tabulce na Power Pivot
- Vytvořte databázi v Excelu pomocí doplňku Power Pivot