Renat už není poprvé ve prospěch guestwritera na Lifehaku. Dříve jsme z něj zveřejnili velký materiál o tom, jak provést plán tréninku: a online zdroje, stejně jako vytvoření tréninkovému plánu.

Tento článek obsahuje nekomplikované techniky, které umožňují. Zejména budou užitečné pro ty, kteří se angažují v oblasti managementu, připravuje řadu analytických zpráv založených na vykládání z 1C a dalších zpráv, formuláře prezentací a diagramů pro manuál. Nepředstírám, že absolutní novinkou - v jednom nebo jiném, tyto techniky pravděpodobně diskutovaly na fórech nebo.

Jednoduché alternativy k HDP a GRP, pokud požadované hodnoty nejsou v prvním sloupci tabulky: zobrazení, index + vyhledávací deska

Výsledek:

V tomto případě, kromě funkce, znak (char) (zobrazit citace) používá funkci, pokud (pokud), která umožňuje změnit text v závislosti na tom, zda je pozorována kladná dynamika prodeje a textová funkce (text) ), což umožňuje zobrazit číslo v libovolném formátu. Jeho syntaxe je popsána níže:

Text ( hodnota; formát)

Formát je označen v uvozovkách stejným způsobem, jako kdyby jste zadali vlastní formát v okně "Formát buňky".

Můžete automatizovat složitější texty. V mé praxi bylo dlouhé, ale rutinní komentáře k manažerům ve formátu "ukazatel klesl / zvýšil XX ve vztahu k plánu především v důsledku růstu / poklesu faktoru1 na XX, růst / pokles faktoru2 na yy .. . "S měnícím se seznamem faktorů. Pokud tyto komentáře píšete často a proces psaní může být algorithmized - to jednou stojí za to vytvořit vzorec nebo makro, které se vás zbaví alespoň z části práce.

Jak uložit data v každé buňce po Unii

Při kombinaci buněk je uložena pouze jedna hodnota. Excel upozorňuje, když se snaží kombinovat buňky:

V souladu s tím, pokud jste měli vzorec, který závisí na každé buňce, přestane pracovat po jejich fúze (chyba # N / D v řádcích 3-4 příkladu):

Pro kombinování buněk a zároveň uloží data v každém z nich (možná máte vzorec, jako v tomto abstraktním příkladu; můžete sloučit buňky, ale uložte všechna data pro budoucnost nebo je skrýt úmyslně je skrýt úmyslně), Kombinujte všechny buňky na list, zvýrazněte je a potom pomocí příkazu "Vzorový formát", přenos formátování do těchto buněk, které potřebujete kombinovat:

Jak vytvořit souhrn několika zdrojů dat

Pokud potřebujete sestavit konsolidovanou bezprostředně z více zdrojů dat, budete muset přidat na pásu karet nebo rychlého přístupového panelu "Master konsolidovaných tabulek a grafů", ve kterém je taková možnost.

To lze provést následujícím způsobem: "Soubor" → "Parametry" → "Rychlý přístupový panel" → "Všechny příkazy" → "Master of souhrn tabulek a grafů" → "Přidat":

Poté se na pásku objeví příslušná ikona, jehož stisknutím je průvodce sám:

Po klepnutí na něj se zobrazí dialogové okno:

V něm musíte vybrat položku "v několika rozsahech konsolidace" a klikněte na tlačítko "Další". V další položce můžete vybrat možnost "Vytvořit jednu stránku" nebo "Vytvořit pole stránek". Pokud chcete samostatně přijít s názvem pro každou z datových zdrojů - vyberte druhou položku:

V dalším okně přidejte všechny rozsahy na základě toho, který bude konsolidován a nastavte ji názvy:

Poté, v posledním dialogovém okně určete tam, kde bude zpráva o konsolidované tabulce zaslána - na existujícím nebo novém listu:

Zpráva konsolidovaného stolu je připravena. V části "Strana 1" můžete v případě potřeby vybrat pouze jednu z datových zdrojů:

Jak vypočítat počet okrajů textu A na text B ("MTS Treasury Supermts" - dva záznamy Zkratky MTS)

V tomto příkladu, ve sloupci A existuje několik textových řetězců a naším úkolem je zjistit, kolikrát v každém z nich se nachází v buňce E1:

Chcete-li tento problém vyřešit, můžete použít komplexní vzorec sestávající z následujících funkcí:

1. Dlstr (LEN) - Vypočítá délku textu, jediným argumentem je text. Příklad: DLSTR ("Stroj") \u003d 6.
2. Náhrada - nahrazuje specifický text v textovém řádku ostatním. Syntaxe: Náhrada ( text; Star_text; New_text.). Příklad: Náhrada ("auto"; "Auto"; "") \u003d "Mobile".
3. Registrované (horní) - nahrazuje všechny znaky v řádku kapitálu. Jediným argumentem je text. Příklad: propir ("stroj") \u003d "stroj". Tato funkce bude nutná pro vyhledávání bez registrace. Konec konců, registrovaný ("stroj") \u003d registrovaný ("stroj")

Chcete-li najít zadání definitivního textového řetězce na druhou, musíte odstranit veškerý jeho vstup do originálu a porovnat délku výsledného řádku se zdrojem:

Dlstr (tarif "MTS Super MTS") - DLSTR ("Super tarif) \u003d 6

A pak rozdělit tento rozdíl pro délku linky, kterou jsme hledali:

6 / DLSTR ("MTS") \u003d 2

To bylo dvakrát linie "MTS" je zahrnuta v počátečním.

Zbývá zaznamenávat tento algoritmus v jazyce vzorců (označujeme text, který hledáme záznamy, a "ISKIM" je počet položek, které nás zajímají):

\u003d (Dlstr (dlstr ( text) -Dlster (náhrada (registrovaná (registrovaná (registrovaná) text); Registrovaný (registrovaný) hloupý); ""))) / Dlstr ( hloupý)

V našem příkladu vzorec vypadá takto:

\u003d (Dlstr (A2) -dlster (substituce (registrovaná (registrovaná (A2); registrovaný ($ E $ 1); "")) / Dlstr ($ E $ 1)

Souhrnné tabulky v aplikaci Excel - výkonný nástroj pro vytváření přehledů. To je zvláště užitečné v případech, kdy uživatel dobře pracuje s vzorce a je pro něj obtížné samostatně provádět analýzu dat. V tomto článku se podíváme na to, jak vytvářet podobné tabulky a které příležitosti v editoru aplikace Excel existují. Žádné soubory ke stažení. Školení je k dispozici online.

Nejdříve musíte vytvořit nějaký stůl. Je žádoucí, aby bylo několik sloupců. V tomto případě by se měly opakovat některé informace, protože pouze v tomto případě bude možné učinit jakoukoliv analýzu zadaných informací.

Například zvažte stejné finanční výdaje v různých měsících.

Vytváření souhrnných tabulek

Chcete-li vytvořit podobnou tabulku, musíte nakreslit následující akce.

1. Chcete-li začít, musí být plně přiděleno.

1. Pak přejděte na kartu "Vložit". Klikněte na ikonu "Tabulka". V zobrazené nabídce vyberte možnost "Souhrnná tabulka".

1. V důsledku toho se zobrazí okno, ve kterém musíte určit několik základních parametrů pro vytváření konsolidované tabulky. Především musíte vybrat oblast dat na základě které bude analýza provedena. Pokud jste tabulku dříve identifikovali, bude odkaz na něj automaticky nastaven. Jinak bude muset být přidělen.

1. Pak budete vyzváni k označení, kdy se stavba stane. Je lepší si vybrat položku "na existujícím listu", protože bude nepohodlná analyzovat informace, když je vše rozptýleno do několika listů. Poté musíte zadat rozsah. Chcete-li to provést, klikněte na ikonu v blízkosti vstupního pole.

1. Ihned po tom bude průvodce vytváření přijde do malé velikosti. Kromě toho se vzhled kurzoru změní. Budete muset udělat levým kliknutím myši na libovolné místo pro vás pohodlné.

1. V důsledku toho bude automaticky substituován odkaz na zadanou buňku. Poté musíte kliknout na ikonu na pravé straně okna, abyste jej obnovili do velikosti zdroje.

1. Chcete-li dokončit nastavení, musíte kliknout na tlačítko "OK".

1. V důsledku toho uvidíte prázdnou šablonu pro práci s konsolidovanými tabulkami.

1. V této fázi musíte určit, které pole bude:
   1. sloupec;
   2. řádek;
   3. hodnota analýzy.

Můžete si vybrat cokoliv. To vše záleží na tom, jaké informace, které chcete získat.

1. Chcete-li přidat libovolné pole, musíte udělat levou kliknutím myši a bez vydání prstů přetáhněte do požadované oblasti. V tomto případě kurzor změní svůj vzhled.

1. Můžete uvolnit prst pouze tehdy, když zkřížený kruh zmizí. Stejně tak musíte přetáhnout všechna pole, která jsou v tabulce.

1. Chcete-li zobrazit výsledek celku, můžete zavřít boční panel nastavení. Chcete-li to udělat, stačí kliknout na kříž.

1. V důsledku toho uvidíte následující. S tímto nástrojem můžete snížit výši výdajů v každém měsíci pro každou pozici. Kromě toho jsou informace k dispozici na obecném výsledku.

1. Pokud se vám stůl nelíbilo, můžete se pokusit postavit to trochu jinak. Chcete-li to udělat, musíte změnit pole v oblastech výstavby.

1. Zavřete asistent, aby se znovu vybudoval.

1. Tentokrát vidíme, že konsolidovaná tabulka se stala mnohem více, protože nyní to není měsíce sloupců, ale kategorie výdajů.

Pokud nemůžete postavit stůl sami, můžete se vždy počítat s pomocí editoru. V aplikaci Excel je možnost vytvářet takové objekty v automatickém režimu.

Chcete-li to provést, musíte provést následující, ale nejprve vyberte všechny celé informace.

1. Klepněte na kartu Vložit. Poté klikněte na ikonu "Tabulka". V zobrazené nabídce vyberte druhou položku.

1. Ihned poté se objeví okno, ve kterém budou pro konstrukci různé příklady. Tyto možnosti jsou nabízeny na základě několika sloupců. Počet šablon přímo závisí na jejich množství.

1. Když se odehráváte pro každou položku, bude k dispozici náhled výsledku. Takže práce je mnohem pohodlnější.

1. Můžete si vybrat, co se vám nejvíce líbí.

1. Chcete-li vložit vybranou možnost, klepněte na tlačítko "OK".

1. V důsledku toho získáte následující výsledek.

Poznámka: tabulka byla vytvořena na novém listu. To nastane pokaždé pomocí návrháře.

Jakmile přidáte (bez ohledu na to, jak) souhrnnou tabulku, zobrazí se na panelu nástrojů nová karta "Analýza". Obsahuje obrovské množství různých nástrojů a funkcí.

Zvažte každého z nich podrobněji.

Kliknutím na tlačítko označené ve snímku Screenshot můžete provést následující:

• změnit název;

• zavolejte okno Nastavení.

V okně Možnosti uvidíte spoustu zajímavých věcí.

Aktivní pole

S tímto nástrojem můžete provést následující:

1. Nejprve musíte zvýraznit nějakou buňku. Poté klikněte na tlačítko "Aktivní pole". V zobrazené nabídce klikněte na pole "Parametry pole".

1. Ihned po tom se zobrazí následující okno. Zde můžete zadat typ operace pro použití pro informace ve vybraném poli.

1. Kromě toho můžete konfigurovat numerický formát. Chcete-li to provést, klikněte na příslušné tlačítko.

1. V důsledku toho se zobrazí okno "Formát buňky".

Zde můžete určit, v jakém typu formuláře potřebujete k výstupu výsledku analýzy informací.

Díky tomuto nástroji můžete konfigurovat seskupení podle vyhrazených hodnot.

Vložte plátek

Editor aplikace Microsoft Excel umožňuje vytvářet interaktivní souhrnné tabulky. Zároveň není těžké nic dělat.

1. Zvýrazněte některý sloupec. Poté klikněte na tlačítko "Vložit spánek".
2. V okně, které se zobrazí jako příklad vyberte jednu z navrhovaných polí (v budoucnu je můžete přidělit v neomezeném množství). Po výběru něčeho je tlačítko "OK" okamžitě aktivováno. Klikněte na to.

1. V důsledku toho se zobrazí malé okno, které lze kdykoliv přesunout. To nabídne všechny možné jedinečné hodnoty, které jsou v této oblasti. Díky tomuto nástroji můžete vybrat částku pouze za určité měsíce (v tomto případě). Výchozí informace se zobrazují po celou dobu.

1. Můžete kliknout na některý z položek. Ihned po tom změní částka veškeré hodnoty v poli.

1. Tak se to ukáže, aby si vybral časovou prodlevu.

1. Vše může být vše vraceno na původní vzhled. Chcete-li to provést, klikněte na ikonu v pravém horním rohu tohoto okna.

V tomto případě jsme byli schopni sestavu třídit v měsících, protože jsme měli odpovídající pole. Ale pracovat s daty je silnější nástroj.

Pokud kliknete na příslušné tlačítko na panelu nástrojů, budete s největší pravděpodobností vidět tuto chybu. Faktem je, že v naší tabulce nejsou žádné buňky, které bude explicitně datový formát "Datum".

Jako příklad vytvořte malý stůl s různými daty.

Pak budete muset vytvořit konsolidovaný stůl.

Přejděte na kartu "Vložit". Klikněte na ikonu "Tabulka". V podnabídce, která se zobrazí, vyberte možnost, kterou potřebujete.

1. Pak budeme vyzváni k výběru rozsahu hodnot.

1. Chcete-li to udělat, stačí zdůraznit celý stůl úplně.

1. Ihned po tom bude adresa automaticky nahrazena. Všechno je zde velmi jednoduché, protože je určen pro figuríny. Chcete-li dokončit konstrukci, klikněte na tlačítko "OK".

1. Excel Editor nám nabídne pouze jednu možnost, protože tabulka je velmi jednoduchá (například již není nutná).

1. Zkuste znovu kliknout na ikonu "Insert TimeLine" (nachází se na kartě "Analýza").

1. Tentokrát nebudou chyby. Budete nabídnuti, abyste si vybrali třídicí pole. Zaškrtněte a klikněte na tlačítko "OK".

1. Díky tomu se zobrazí okno, ve kterém můžete vybrat požadované datum pomocí běžce.

1. Vybereme další měsíc a žádná data, protože všechny náklady v tabulce jsou uvedeny pouze po březnu.

Pokud jste provedli nějaké změny zdrojových dat a z nějakého důvodu se nezobrazí v konsolidované tabulce, můžete jej vždy aktualizovat ručně. Chcete-li to provést, stačí kliknout na odpovídající tlačítko na panelu nástrojů.

Pokud se rozhodnete změnit pole, na jejichž základě by se stavba měla dojít, je mnohem snazší provést toto v nastavení, a neodstranit tabulku a vytvořit ji znovu s novými předvolbami.

Chcete-li to provést, klikněte na ikonu zdroje dat. Poté vyberte položku nabídky.

V důsledku toho se zobrazí okno, ve kterém můžete znovu identifikovat požadované množství informací.

Akce

S tímto nástrojem můžete:

• Čirý stůl;
• přidělit;
• přesunout.

Výpočty

Pokud výpočty v tabulce nestačí nebo nesplňují vaše potřeby, můžete vždy provádět změny. Kliknutím na ikonu tohoto nástroje se zobrazí následující možnosti.

Tyto zahrnují:

• vypočtené pole;

• vypočítaný předmět;

• postup pro výpočty (přidané vzorce se zobrazí v seznamu);

• odstraňte vzorec (neexistují žádné informace, protože neexistují žádné přidané vzorce).

Zde můžete vytvořit souhrnný diagram nebo změnit typ doporučené tabulky.

Pomocí tohoto nástroje můžete konfigurovat vzhled vývoje prostor editoru.

Díky tomu můžete:

• nakonfigurujte zobrazení postranního panelu se seznamem polí;

• povolit nebo zakázat tlačítka plus / mini C;

• nakonfigurujte zobrazení záhlaví pole.

Při práci s konsolidovanými tabulkami, kromě karty "Analýza" se také zobrazí ještě jedna další "návrhář". Zde můžete změnit vzhled objektu, dokud nepoznatelný ve srovnání s výchozí volbou.

Můžete konfigurovat:

• subtotals:
  • neukazuj;
  • zobrazit všechny výsledky na dně;
  • zobrazit všechny výsledky v názvu.

• obecné výsledky:
  • zakázat struny a sloupce;
  • povolit pro řádky a sloupce;
  • umožnit pouze pro struny;
  • povolit pouze pro sloupce.

• rozvržení sestavy:
  • zobrazit v komprimované formě;
  • show ve formě struktury;
  • zobrazit v tabulkové formě;
  • opakujte všechny podpisy prvků;
  • neopakujte podpisy prvků.

• prázdné řádky:
  • po každém prvku vložte prázdný řetězec;
  • po každém prvku smažte prázdný řetězec.

• nastavení stylu tabulky (zde můžete povolit / zakázat každou položku):
  • záhlaví řetězců;
  • sloupcové titulky;
  • střídavé linie;
  • střídavé sloupce.

• konfigurace stylu konstrukčních prvků.

Chcete-li zobrazit více různých možností, musíte kliknout na trojúhelník v pravém dolním rohu tohoto nástroje.

Ihned po tom se objeví obrovský seznam. Můžete si vybrat cokoliv. Když se odehráváte pro každou ze šablon, bude tabulka změna (to se provádí pro náhled). Změny se nezajímají, když neentudujete žádnou z navrhovaných možností.

Kromě toho, pokud si přejete, můžete si vytvořit svůj vlastní designový styl.

Také zde můžete změnit pořadí zobrazovacích řádků. Někdy je nutné pro pohodlí analýzy nákladů. Zvláště pokud je seznam velmi velký, protože požadovaná poloha je snazší najít abecedně než překlopení seznamu několikrát.

Chcete-li to udělat, proveďte následující.

1. Klikněte na trojúhelník v blízkosti požadovaného pole.
2. V důsledku toho se zobrazí další nabídka. Zde si můžete vybrat požadované třídění ("z A do Z" nebo "z I do A").

Pokud standardní volba nestačí, můžete kliknout na položku "Upřesnit nastavení třídění" ve stejném menu.

V důsledku toho se zobrazí následující okno. Pro podrobnější nastavení musíte kliknout na tlačítko "Upřesnit".

Zde je vše nakonfigurováno v automatickém režimu. Pokud zrušíte toto klíště, můžete zadat potřebný klíč.

Souhrnné tabulky v aplikaci Excel 2003

Výše popsané akce jsou vhodné pro moderní editory (2007, 2010, 2013 a 2016). Ve staré verzi vypadá všechno jinak. Příležitosti, samozřejmě je mnohem méně.

Aby bylo možné vytvořit konsolidovaný stůl v aplikaci Excel 2003, musíte provést následující.

1. Přejděte do sekce "Data" a vyberte příslušnou položku.

1. V důsledku toho se vytvoří hlavní objekty.

1. Po kliknutí na tlačítko "Další" se otevře okno, ve kterém chcete zadat rozsah buněk. Poté znovu stiskněte tlačítko "Další".

1. Chcete-li dokončit nastavení, klepněte na tlačítko "Ready."

1. V důsledku toho uvidíte následující. Zde musíte přetáhnout pole do příslušných oblastí.

1. Tento výsledek se například může ukázat.

Je zřejmé, že je mnohem lepší vytvořit takové zprávy v moderních editorech.

Závěr

Tento článek se týkal všech podrobností o práci s konsolidovanými tabulkami v Editoru aplikace Excel. Pokud něco nefunguje, můžete přidělit ne tyto pole nebo velmi málo z nich - vytvořit takový objekt existuje několik sloupců s opakováním hodnot.

Pokud tento tutoriál nestačí pro vás, další informace naleznete v nápovědě online.

Video instrukce

Pro ty, kteří mají stále nějaké dotazy bez odpovědí, je připojeno video s komentáři k výše popsaným pokynům.

Sledujte video k článku:

Typické zprávy, které jsou zahrnuty v našem moduly obchodních inteligence (bi) modulů připravených Nechte reagovat na 99% nejoblíbenějších uživatelských požadavků.

Pokud je to žádoucí, analytici nebo všichni ostatní uživatelé mohou provést své zprávy v aplikaci Excel na Cubes OLAP.

Díky v srdci každého obchodního modulu - analytici (bi) lži OLAP CubePřipojením, ke kterým mohou uživatelé snadno vytvořit jakoukoliv zprávu v aplikaci Excel pomocí souhrnných tabulek.

Také vestavěný v aplikaci Excel zprávy o Cubes OLAP lze uložit, a když se znovu otevírá, budou automaticky aktualizovány s aktuálními daty obchodní analytika firemní systém (bi) \\ t .

Příklady zpráv postavených na OLAP kostky:

Popis přehledu:

(Proces stavebních zpráv se podíváme na video k tomuto článku)

1. Zpráva o prodeji v rublech a jednotkách:

• doba: Pro aktuální rok
• indikátory: Skutečnost skutečnosti
• podrobně popisující: Sloupce / čtvrtina, měsíc
• podrobně popisující: Řádky / kanály Prodej, klienty, značky, produkty

2. Zpráva o obchodní struktuře v rublech a jednotkách (filtr):

• doba: Pro aktuální rok
• indikátory: Prodej skutečnosti, prodej skutečnosti v procentech
• podrobně popisující: Sloupce / čtvrtina, měsíc
• podrobně popisující: Řádky / prodejní kanály, značky

3. Zpráva o provádění prodejního plánu v rublech a jednotkách (filtr):

• doba: Pro aktuální rok
• indikátory: Prodejní plán, prodej skutečnosti, realizační plán
• podrobně popisující: Sloupce / čtvrtina, měsíc
• podrobně popisující: Řádky / kanály Prodej, klienty, kategorie, produkty

4. Zpráva o zisku prodeje v rublech a jednotkách (filtr):

• doba
• indikátory
• podrobně popisující: Řádky / federální okresy, regiony, kategorie, produkty

5. Zpráva o zvýšení doby prodeje v rublech a jednotkách (filtr):

• doba: Pro aktuální rok ve srovnání s předchozím
• indikátory: Prodej předchozího roku, prodejního plánu, prodejní skutečnosti, realizace plánu, zvýšení prodeje v absolutním a relativním vyjádřením do předchozího roku
• podrobně popisující: Řádky / čtvrtletí, měsíc, kategorie, produkty

6. Zpráva o pohledávkách:

• doba: Na aktuálním datu
• indikátory: Částka dokumentu, celkové pohledávky, aktuální pohledávky, platné pohledávky za období - do 15 dnů, do 30 dnů, do 60 dnů, do 60 dnů, do výše 90 dnů, do 180 dnů, až 365 dní nebo více , struktura pohledávek
• podrobně popisující: Struny / prodejní kanály, protistrana, dokument dokumentu, datum dokumentu, dokument

7. Zpráva o pohledávkách:

• doba: Pro aktuální rok
• indikátory: Obecné pohledávky, aktuální pohledávky, pohledávky po splatnosti, struktura řízení dluhu
• podrobně popisující: Řádky / rok, týden, protistrana

8. Zpráva Příjezd plateb podle Datum dokumentu:

• doba: Pro aktuální rok
• indikátory: Výše \u200b\u200bdokumentu, částka zaplacení dokumentu, odchylky pro platbu v absolutních a relativních podmínkách
• podrobně popisující: struny / rok, měsíc, protistrana, typ dokumentu, datum dokumentu, číslo dokumentu

9. Platby Zpráva Příjezd podle data platby:

• doba: Pro aktuální rok
• indikátory: Částka platby, výši distribuce plateb, distribuční odchylky v absolutních a relativních podmínkách
• podrobně popisující: Řádky / rok, měsíc, protistrana, typ platby, datum platby, platební místnost

10. Zpráva o komoditní rezervě:

• doba: Na aktuálním datu
• indikátory: Zůstatek zboží, reziduální datum vypršení platnosti v procentech (DSO), zůstatek zboží z hlediska vhodnosti - termín 0%, termín o 10-30%, termín 40-60%, termín 70-90% , termín 99%
• podrobně popisující: struny / kategorie, produkt, stav, sklad, farní dokument Datum

Ahoj všichni! Dnešní materiál pro ty, kteří pokračují v rozvoji práce s aplikacemi, a neví, jak udělat konsolidovaný stůl v aplikaci Excel.

Po vytvoření společné tabulky v některém z textových dokumentů je možné provést jeho analýzu provedením konsolidovaných tabulek v aplikaci Excel.

Vytvoření konsolidovaného tabulky Excel vyžaduje dodržování určitých podmínek:

1. Data zapadají do tabulky, kde jsou sloupce a seznamy s názvy.
2. Žádné prázdné formy.
3. Nedostatek skrytých objektů.

Jak vytvořit konsolidovaný stůl v aplikaci Excel: Krok za krokem

Chcete-li vytvořit pivotový stůl, který potřebujete:

Byl vytvořen prázdný list, kde jsou viditelné seznamy regionů a polí. Titulky se staly poli v naší nové tabulce. Konsolidovaný stůl bude tvořen přetažením polí.

Budou zaškrtnuta a pro pohodlí analýzy je změníte v místech v tabulkách.


Rozhodl jsem se, že analýza údajů by byla provedena prostřednictvím filtru prodejců, která by měla být viděna, komu a kolik každý měsíc byl prodán, a jaký produkt.

Vyberte si konkrétní prodejce. Klikněte na myš a přeneste pole "Prodávající" na "Filtr sestav". Nové pole je označeno a pohled na tabulku se mírně změní.


Kategorie "Zboží" dodáme ve formě řádků. V "linkách titulů" přenášíme pole, které potřebujete.


Chcete-li zobrazit rozevírací seznam, má hodnotu, ve které sekvence označujeme název. Je-li původně v řádcích volbou ve prospěch zboží, a pak zaznamenat cenu, pak je zboží jen rozevírací seznamy a naopak.

Sloupec "Jednotky", být v hlavní tabulce, zobrazeny počet zboží prodávaných konkrétním prodejcem za určitou cenu.


Chcete-li zobrazit prodej, například pro každý měsíc, budete potřebovat pole "Datum", aby se umístěno "názvy sloupců". Kliknutím na datum vyberte příkaz "Grind".


Uveďte období dat a krok. Potvrďte volbu.

Vidíme takový stůl.


Přeneseme pole "Množství" na oblast "Hodnota".


Je viditelné pro zobrazení čísel a potřebujeme numerický formát


Chcete-li správně správně vyberte buňky voláním okna pomocí myši, vyberte "numerický formát".

Číselný formát si vybereme na další okno a oslavujeme "Separator skupin výbojů". Potvrďte tlačítko "OK".

Registrace pivotové tabulky

Pokud dodáváme klíště, které potvrzuje alokaci několika objektů najednou, můžeme data zpracovat najednou na několik prodejců.


Použití filtru je možné pro sloupce a řádky. Uvedení klíště na jeden z odrůd zboží, můžete zjistit, kolik je realizován jedním nebo více prodejcům.


Samostatně nakonfigurovat parametry pole. Na příkladu vidíme, že určitý prodejce Romů v konkrétním měsíci prodal košile do určité částky. Stisknutím myši v řádku "Součet nad polem ..." Voláme nabídku a vyberte "Parametry pole pole".


Vedle těchto dat v poli vyberte "Množství". Potvrďte volbu.

Podívejte se na stůl. Je jasně vidět, že v jednom z měsíců prodávající prodal košili ve výši 2 kusů.


Nyní změníme stůl a činí filtr pracoval v měsících. Pole "Date" je přeneseno do "Filtr sestav" a kde "názvy sloupců" bude "prodávající". Tabulka zobrazuje celé prodejní období nebo pro konkrétní měsíc.

Výběr buněk v otočném stole povede k vzhledu takové záložky jako "Práce s konsolidovanými tabulkami" a budou další dvě záložky "Parametry" a "Designer".


Můžete skutečně vyprávět o nastavení konsolidovaných tabulek po velmi dlouhou dobu. Proveďte změny podle svého vkusu, což je pro vás užitečné. Nebojte se stisknout a experimentovat. Jakákoli akce Můžete vždy změnit stisknutím kombinace kláves Ctrl + Z.

Doufám, že jste se naučil všechny materiály, a teď víte, jak vytvořit konsolidovaný stůl v aplikaci Excel.

Pro analýzu velkých a komplexních tabulek se obvykle používají. S pomocí vzorců můžete také implementovat seskupení a analýzu dostupných dat. Vytvořit nekomplikované zprávy pomocí vzorců.

Jako začátek budeme používat ( Vložka / tabulka / tabulka) obsahující informace o prodeji dávek výrobků. V tabulkových řadách jsou uvedeny údaje o dodávce šarže produktu a jeho marketingu. Podobná tabulka byla použita v článku .

Tabulka má sloupce:

• Produkt - název šarže zboží, například " Pomeranče»;
• Skupina - například skupina zboží " Pomeranče"Zadejte skupinu" Ovoce»;
• termín dodání - Dodací datum zboží Dodavatelem;
• Oprava regionu - oblast, ve kterém byla provedena šarže zboží;
• Odbyt - náklady, jejichž bylo možné realizovat šarži zboží;
• Odbyt - termín skutečného prodeje zboží v regionu (ve dnech);
• Zisk - Značka, zda byl zisk přijat od realizované šarže zboží.

Přes Jméno Dispatcher. Opravte tabulky na " Oficiální_tablitsa."(Viz příklad Soubor).

S pomocí vzorců vytvořte 5 jednoduchých zpráv, které budou umístěny na samostatných listech.

Zpráva №1 Celkový prodej zboží

Celkové prodeje každého produktu nalezneme.
Úkol je řešen zcela jednoduše pomocí funkce tichého (), nicméně, výstavba zprávy vyžaduje určité dovednosti pracovat s některými nástroji Excel.

Tak pokračujte. Za prvé, musíme vytvořit seznam jmen produktů. Protože Ve sloupci se opakuje název zdrojové tabulky, pak z něj musíme vybrat pouze hodnoty. To lze provést několika způsoby: vzorce (viz článek), přes menu Data / Data / Delete Duplikáty nebo s. Pokud používáte první způsob, pak při přidávání nových produktů do zdrojové tabulky, budou nové názvy automaticky zahrnuty do seznamu. Ale tady pro jednoduchost bude používat druhý způsob. Pro tohle:

• Jít na list se zdrojovou tabulkou;
• Volání ( Data / Seřadit a filtr / Advanced);
• Vyplňte pole podle obrázku níže: přepínač nastaven na polohu Zkopírujte výsledek na jiné místo; Ve zdrojovém rozsahu pole zadejte $ 4: $ A $ 530; Zkontrolujte vlajku Pouze unikátní položky.

• Zkopírujte výsledný seznam na listu, ve kterém bude zpráva umístěna;
• Seřadit seznam zboží ( Data / třídění a filtr / třídění z A do Z).

Mělo by to být následující seznam.

V buňce. B6. Zavedeme následující vzorec, pak jej zkopírujte na konec seznamu:

Summage (Source_tablik [Produkt]; A6; Source_tablik [PRODEJ])

Plány (Source_tablik [produkt]; A6)

Zpráva č. 2 Prodej zboží podle krajů

Celkové prodeje každého výrobku nalezneme v regionech.
Používáme seznam zboží vytvořených pro zprávu č. 1. Podobně získáme seznam názvů oblastí (ve zdrojovém rozsahu pole, zadejte $ d $ 4: $ d $ 530).
Zkopírujte výsledný vertikální rozsah Schránka A je to horizontální. Výsledný rozsah obsahující názvy oblastí je umístěn do záhlaví zprávy.

V buňce. B. 8 Zavedeme následující vzorec:

Smerevymn (Source_tablik [Prodej];
Source_tablik [produkt]; $ A8;
Source_table [prodej regionu]; B $ 7)

Formule vrátí celkové prodej zboží, jehož jméno se nachází v buňce A8. V oblasti z buňky V 7 . Věnujte pozornost použití (odkazy $ A8 a B $ 7), bude zapotřebí při kopírování vzorce pro ostatní prázdné tabulkové buňky.

Zkopírujte výše uvedený vzorec v buňce vpravo, nebude fungovat (to bylo provedeno pro zprávu č. 1), protože V tomto případě v buňce C8. Vzorec bude vypadat takto:

Smerevymn (Source_tablik [prodej, dny];
Source_tablik [skupina]; $ A8;
Source_table [Prodej]; C $ 7)

Zpráva č. 3 Filtrování produktů pro ziskovost

Vraťme se do zdrojové tabulky. Každá šarže zboží buď přinesla zisk nebo nepřinesla (viz sloupec zisku ve zdrojové tabulce). Vypočítat prodej skupinami zboží v závislosti na ziskovosti. Chcete-li to udělat, bude filtrovat pomocí zdrojové tabulky nahrávání vzorce na pole zisku.

Frekvence (Source_tablik [Prodej, dny]; A7: A12)

Zadejte vzorce, zvýrazněte rozsah C6: C12. , pak B. Řádkové vzorce Zadejte výše uvedený vzorec a klepněte na tlačítko Ctrl.+ Posun.+ Enter..

Stejný výsledek lze získat pomocí obvyklé funkce DIMMPROT ():
\u003d SHUMPAZACE ((Source_tablik [Prodej, dny]\u003e A6) *
(Source_tablik [Prodej, dny]<=A7))

Zpráva №5 statistiky dodávek zboží

Nyní připravte zprávu o dodávkách zboží za měsíc.
Nejprve vytvořte seznam měsíců podle roku. V zdrojové tabulce nejdříve datum dodání 07/11/2009. Lze jej vypočítat pomocí vzorce:
\u003d Min (Source_tablik [Datum dodání])

Vytvořit seznam dat - od nejbližšího data dodání. K tomu použijeme vzorec:
\u003d CONMINES ($ C $ 5; -1) +1

V důsledku toho obdržíme seznam dat - první dny měsíců:

Použití příslušného formátu buněk, změňte zobrazení data:

Vzorec pro počítání počtu zboží dodaného za měsíc:

Summie ((Source_tablik [podrobnosti]\u003e \u003d B9) *
(Source_tablik [Datum dodání]

Nyní přidejte řádky pro počítání celkového počtu stran pro každý rok. Chcete-li to provést, změníte tabulku mírně, zvýraznění roku v samostatném sloupci, který byl dodáván pomocí funkce roku ().

Nyní pro výstup do roku vytvoříme strukturu prostřednictvím položky nabídky:

• Zvýrazněte libovolnou buňku modifikované tabulky;
• Volání okna prostřednictvím položky nabídky Data / Struktura / Prozatímní výsledky;
• Vyplňte pole podle obrázku:

Po stisknutí OK se tabulka změní následovně:

Střední výsledky budou vytvořeny podle roku.
Stisknutím malých tlačítek v levém horním rohu listu můžete spravovat zobrazení dat v tabulce.

Souhrn :

Zprávy podobné vytvořenému, lze provádět přirozeně s nebo pomocí Filtr Do zdrojové tabulky nebo s jinými funkcemi BDSMM () (), kůra (), BSC (), atd. Volba přístupu závisí na konkrétní situaci.