Konzistentní vytváření Excelu, práce datových prací, odstranění

Excel je poměrně výkonný nástroj, který vám umožní pracovat s velkými datovými polími a provádět s nimi řadu operací. Lze tvrdit, že tabulky Excelu jsou relační databáze, s nimiž můžete pracovat bez vlastnictví dovedností programátora. Pokud je však program používán profesionálně, základní funkce nestačí k vytvoření zpráv o různých strukturách. Pro tyto účely má tablečný procesor funkčnější a výkonnější nástroj - souhrnné tabulky.

Proč Excel potřebuje souhrnné tabulky

V originále tento termín zní jako otočný tabulka a překlad fráze je poněkud mlhavý: „tabulku, kterou lze změnit, zkroutit, demonstrovat v různých projekcích“. Co to znamená? Interpretace tabulkových dat - Úkol není snadný. To se obvykle provádí pomocí vzorců a přijímá konečné výsledky na konci tabulky.

Pokud potřebujete vytvářet komplexní zprávy pomocí filtrů a změnit zdrojová data ve správné sekvenci, udělejte to se standardním prostředkem Excel, pokud je to možné, je to velmi obtížné. Ale díky konsolidovaným tabulkám je takové zprávy mnohem snazší vytvářet, jako jsou různé diagramy, což umožňuje pouze pomocí myši seskupovat linky a sloupce, měnit je místy, používat různé ovládací prvky.

Většina pravidelných uživatelů Excelu si ani neuvědomuje přítomnost tak mocného nástroje. Pro jasnost dáváme malý a indikativní příklad použití konsolidovaných tabulek Excel „pro figuríny“.

Předpokládejme, že jste manažerem společnosti a jste zodpovědní za údržbu prodejní dokumentace. Všechny transakce jsou zadány do tabulky, které uvádějí všechny podrobnosti (prodávající, kupující, množství, částka, datum prodeje, adresa kupujícího atd. D.). Vůdce požádá o zprávu o prodeji podle regionu.

Jak to děláme? Vytváříme rozložení nové tabulky, v jejich čepici, kterou označujeme celou škálu prodaného zboží a v řádcích - jména regionů. Za tímto účelem zkopírujeme ze zdrojové tabulky sloupec se zbožím a odstraníme duplikáty. Pomocí režimu speciální vložky převedeme tento sloupec do klobouku konsolidované stránky. Přicházíme stejným způsobem s regiony, ale protože se nacházejí v konsolidované tabulce svisle, jednoduše kopírujeme bez transpozice.

V každé buňce shrnujeme příjmy pomocí funkce Summailu. Nakonec přidejte konečné výsledky ke dnu a pošlete zprávu manažerovi. Vůdce žádá regiony, aby uspořádaly nahoře a zboží - na řádcích. Uvádíte novou zprávu za 5 minut a získáte nový úkol: Poskytněte údaje o zisku, nikoli příjmy, to znamená, s ohledem na náklady. No, trochu změníme vzorec a výsledek je opět připraven během několika minut. Konečně existuje žádost o přípravu takových zpráv pro každého prodejce, aby vyhodnotil jejich účinnost. A to není problém pro souhrnnou tabulku, pokud víte, kde jsou umístěny potřebné počáteční údaje!

Mezitím každý uživatel Excelu ví, jak obtížné je provádět takové manipulace v rámci zdrojové tabulky.

Vytváření a práce s konsolidovanými tabulkami v Excelu

Předpokládejme, že máme zdrojovou tabulku prodeje:

Zvažte, jak vytvořit konsolidovanou tabulku na základě těchto tabulkových dat. Chcete -li to provést, vyberte libovolnou buňku, po níž vybereme kartu „Vložit“ na hlavním panelu. Zobrazí se dvě další karty: „Souhrnná tabulka“ a vlevo - „Doporučené souhrnné tabulky“. Pokud jste začátečník a nechápejte, jak můžete zorganizovat zdrojová data, doporučujeme použít druhé tlačítko. Toto je mistr, který vám nabídne připravené -vyráběné možnosti zprávy. Musíte si vybrat příslušnou možnost a získat hotový konsolidovaný stůl k dispozici. Zbývá ji pouze napravit, pokud vás podle některých kritérií neuspokojuje.

Druhý, hlavní způsob, jak vytvořit konsolidované tabulky, je „od nuly“, pro které stiskneme první tlačítko.

Otevře se nové okno, ve kterém je nutné nastavit dva parametry: počáteční rozsah buňky a umístění ST. Protože jsme před vytvořením tabulky otočení vybrali buňku, rozsah bude stanoven automaticky, výchozí hodnota pro druhý parametr je nový list. Takže nic neměníme a klikneme na OK. Výsledkem je, že se otevře nový list s neúplným rozvržením.

Chcete -li jej nakonfigurovat, použijte pravý panel s názvem „Pole souhrnné tabulky“. Zde v horní jednotě je uveden seznam dostupných polí odpovídajících sloupci zdrojové tabulky. Pole můžete přidat dvěma způsoby: nasazením zaškrtnutí naproti požadované hodnotě a poté se automaticky dostane do rozvržení. Ale zdaleka vždy na místo, které potřebujete. V takových případech stačí přetáhnout pole do nezbytné polohy rozvržení. Odstranění polohy v rozvržení se provádí podobným způsobem, odstranění zaškrtávacího označení nebo přetažení na pravém panelu.

Obsahuje níže čtyři regiony, na jejichž základě bude vytvořena souhrnná tabulka. Zvažte je podrobněji:

• Oblast hodnot je hlavní součástí ST, protože je zde uvedeno, jak zpracovávat sloupce a řádky. Obvykle se jedná o souhrn - tato metoda agregace bude nastavena ve výchozím nastavení za předpokladu, že všechny buňky rozsahu přijímají numerické hodnoty. V přítomnosti alespoň jedné prázdné buňky nebo s daty ve formátu, který se liší od numerického, namísto součtu hodnot buněk se jejich počet vypočítá, takže tato skutečnost musí být zohledněna v úvahu. Pro náš příklad bude pro každý produkt provedeno sčítání v odpovídající oblasti. Kromě shrnutí můžete nastavit jakoukoli jinou metodu výpočtů, které mají asi dva tucty (výpočet průměrné hodnoty, sdílení atd.). Nejjednodušší způsob, jak toho dosáhnout, je přímo v souhrnné tabulce, kliknutím na PKM pro jakoukoli buňku a výběr nezbytné metody výpočtů;
• Oblast řádků - sestává z jedinečných hodnot buněk umístěných v levém sloupci. Pokud zde vložíte několik polí, souhrnná tabulka již nebude plochá, ale víceúrovňová;
• Oblast sloupce také obsahuje hodnoty pole, ale již vzhledem k horní linii počáteční tabulky;
• Oblast filtru umožňuje ukládat omezení na podmínky výpočtu hodnot buněk. Obvykle je zde umístěno pole, podle kterého by měl být vzorek nastaven ve třech dalších oblastech.

Díky přítomnosti těchto oblastí můžete nakonfigurovat vzorek z počáteční tabulky téměř jakékoli složitosti a několik minut strávit.

Abychom to vyjasnili, vysvětlíme výše uvedené pomocí příkladu popsaného v předchozí části.

Takže, krok -by -krok pokyny, jak vyrobit konsolidovaný stůl v Excelu:

• Ze seznamu polí v souhrnné tabulce přetáhneme pole „příjmy“ do oblasti hodnot;
• Pole „region“ (region kupujícího) je vtažen do linek;
• Polní „produkt“ vložený do oblasti sloupce.

Pouze tři akce - a souhrnná tabulka vhodná pro odeslání úřadům, jsou připraveny! Sestavení nám trvalo asi 10 sekund.

Jak splnit další přání ředitele?

Abychom dosáhli zisku namísto příjmů, stačí umístit odpovídající pole do oblasti hodnot místo příjmů, odpovídající výpočty v shrnutí budou automaticky provedeny.

Druhý úkol se také provádí v sekundách - pro změnu regionů a zboží, stačí je přetáhnout z jedné oblasti na druhou (změna míst).

Chcete -li získat výsledky pro každého manažera, stačí umístit pole „Správce“ do oblasti filtru, pak se v horní části objeví selektor, ve kterém musíte určit požadované příjmení, abyste získali zprávu. Existuje příležitost kombinovat zprávu o několika prodejcích.

Samozřejmě jsme nepřinesli nejobtížnější, ale velmi indikativní příklad, který vám umožní pochopit, jak vytvořit souhrnné tabulky v Excelu. Metoda výpočtu hodnot v buňkách může být složitější, možná budete muset použít vypočítaná pole a také použít podmíněné formátování - možnosti v tomto ohledu v Excelu jsou působivé.

O zdroji dat

Již jsme zmínili, že určité požadavky jsou uloženy na počáteční údaje použité ke správnému vytvoření konsolidovaných tabulek v programu Excel. Uveďte je:

• Hlavní omezení je spojeno s povinnou přítomností jmen nad sloupci zapojenými do výpočtů. Takové identifikátory jsou nezbytné pro vytvoření výsledných zpráv - Při přidání nových záznamů (řetězců) do počáteční tabulky není formát pro změnu formátu ST nutný a výsledky budou přepočítávány automaticky;
• Ujistěte se, že v buňkách linek a sloupců účastnících se vzorku jsou zavedeny numerické parametry. Pokud jsou prázdné nebo obsahují textové hodnoty, tyto řádky vypadnou z výpočtů, které zkreslují výsledky výpočtů;
• Sledujte korespondenci formátů linek a obsahu buněk. Pokud je definováno jako datum, musí mít všechny hodnoty ve sloupci stejný formát, jinak se filtrování a nesprávné výpočty nesprávné.

Pokud dodržujete tato pravidla, pak by se s konstrukcí problémů s souhrnnou tabulkou nemělo nastat.

Aktualizace dat v tabulce sady Excel

Přestože jsme hovořili o automatickém přepočtu konsolidovaných tabulek při úpravách zdrojových dat nebo struktuře samotné ST, ve skutečnosti z důvodů ukládání počítačových zdrojů musíte provést určité akce.

Data můžete aktualizovat v naší tabulce expozice Excel dvěma způsoby:

• Kliknutím na pravé tlačítko myši na libovolné buňce a výběrem položky „Aktualizovat“ v kontextové nabídce;
• Použití karty „Data“ a tlačítka „Aktualizovat vše“ “.

Ukládání tabulek je obzvláště odůvodněné, pokud mají velké velikosti, v tomto případě se rychlost programu výrazně zvýší, zejména pokud pravidelně vstupujete do nových řádků a s vysokou frekvencí.

Přidání sloupce nebo řádku do Excelu

Obecně je operace triviální, pokud je požadovaný parametr ve zdrojové tabulce. Musíte jen přetáhnout správné pole do správné oblasti. Pokud ve startovní tabulce není žádný řetězec nebo sloupec, přidejte jej, v případě potřeby zadejte hodnoty. Dále klikněte na kartu „Analýza“ a otevřete naši změněnou tabulku. Zbývá pouze aktualizovat data (viz. Předchozí část), v důsledku toho se seznam polí změní v pravém panelu ST - do ní bude přidán nový parametr.

Souhrnná tabulka často nemá zcela pohodlné druhy pro vizualizaci. Například, když v řádcích je několik polí. Pak se jeden z nich zobrazí v plném rozsahu a hodnoty druhého musí být uvedeny v horní části tabulky. Pokud existuje jen málo takových hodnot (řekněme, v našem příkladu prodejních oblastí pouze 6), má smysl přidat do odpovídající oblasti sloupec „regiony“ otočné tabulky a převést ji z oblasti řádky.

Změna postupu pro sledování sloupců je také snadno dosažitelná, jednoduše přeneste sloupec do požadované pozice pomocí myši.

Tvorba souhrnné tabulky z několika souborů (tabulek) Excel

Toto je složitější, ale docela úplný úkol. Použijte Master ST, který je třeba přidat do panelu rychlého přístupu.

Vlevo tedy v blízkosti položky „Soubor“ klikněte na šipku nasměrované dolů a vyberte položku „Ostatní příkazy“. Otevře se nové okno, na středním panelu klikneme na možnosti „Všechny týmy“. V seznamu v seznamu najdete v seznamu „Master of Consolidated Table“, který se otevírá, vyberte jej a klikněte na tlačítko „Přidat“. Odpovídající ikona se objeví na horním panelu.

Spustíme Master. Předpokládejme, že musíme kombinovat data ze dvou listů, května a v červnu. Hlavní podmínkou je, že se musí shodovat ve struktuře. Pokud ano, pak v prvním okně hlavního masa vybereme možnost „Nákladní tabulka“ a další parametr - „Několik konsolidačních rozsahů“.

Ve druhém kroku naznačujeme pánovi, že potřebujeme několik polí. Třetí - vyberte rozsah buněk pro shrnutí prvního listu, klikněte na „Přidat“ a udělat to samé pro druhý list Excel. Nyní musíte dát jména dvě pole. Uděláme to nejprve pro první pole (klikněte na selektor "1", ve sloupci "1 pole" zadáme "květen"), pak analogií pro druhé pro druhé. V tomto případě bude nutné uvést v seznamu rozsahů odpovídající linii, první nebo druhá.

Po kliknutí na tlačítko „Ready“ se otevře nové okno, ve kterém označíme „nový list“ a znovu stiskneme tlačítko „Ready“.

POZORNOST. Přijetí vícerozměrných tabulek je nepohodlné v tom, že je obtížné navigovat s velkým počtem polí a musí být provedeny potřebné výpočty, přičemž pokaždé označují nezbytné hodnoty z horní oblasti tabulky.

Jak odstranit umění

Nejjednodušší případ - Když jste vytvořili konsolidovaný stůl, poslal výsledky šéfkuchaři a už to nepotřebujete. Pokud jste si tím jisti, vyberte tabulku a klikněte na klávesu Delete. Jednoduché a efektivně.

Najednou však může být v budoucnu zapotřebí struktura stolu? Excel má příležitost odstranit pouze výsledky nebo data z buněk. Zvažte, jak se to dělá.

Pro odstranění výsledků výpočtu provádíme následující kroky:

• Označujeme jakoukoli buňku ST;
• Vyberte kartu „Analýza“ na horním panelu;
• Ze seznamu dostupných akcí vyberte „Clear“;
• Klikněte na bod „Vymazat vše“.

Ale co dělat, pokud chcete výsledky uložit, ale nepotřebujete data sami, to znamená, že chcete uvolnit stůl? Tato situace často nastává, pokud vedení potřebuje pouze výsledky. Algoritmus akcí:

• Znovu vyberte libovolnou buňku, klikněte na kartu „Analýza“;
• Vyberte položku nabídky „Akce“, klikněte na „Vyberte“, označte celou konsolidovanou tabulku myší;
• Klikněte na PCM uvnitř vybrané oblasti;
• Z nabídky kontextu vyberte položku „Kopírovat“;
• Jdeme na kartu „Home“, znovu klikněte na PKM a vyberte „Vložit“;
• Vyberte kartu „Vložit hodnotu“, v ní zaznamenáváme parametr „Vložit jako hodnotu“.

V důsledku toho bude souhrnná tabulka vymazána při zachování výsledků.

RADA. Postup můžete urychlit pomocí kombinace klíčů. Chcete -li vybrat tabulku, použijte Ctrl + A, pro kopírování - Ctrl + C. Poté stiskněte ALT + E, Alt + S, Alt + V a dokončete postup stisknutím klávesy Enter.

Chcete -li odstranit konsolidované tabulky v Excelu 2007/2010, musíte použít jiný algoritmus:

• Vyberte ST navržený pro odstranění;
• Klikněte na kartu „Parametry“;
• Ve „skupině akcí“ vyberte položku „čisté“ a poté - „Vyčistěte vše“.

Pokud váš šéf miluje vizualizaci dat, je zřejmé, že budete muset použít konsolidované diagramy. Protože v stole zabírají hodně prostoru, po použití jsou obvykle odstraněny.

Ve starých verzích programu je třeba vybrat diagram, klikněte na kartu „Analýza“, vyberte skupinu dat a klikněte na postupně „Clean“ a „Clean Everything“.

Navíc, pokud je diagram spojen s nejvyšší souhrnnou tabulkou, po jeho vymazání ztratíte všechna nastavení tabulky, jejích polí a formátování.

U verzí starších než Excel 2010 musíte vybrat diagram, vybrat „Akce“ na kartě „Analýza“ a kliknout na „Clean“ a „Vyčistit vše“. Výsledek bude podobný.

Doufáme, že naše lekce o konsolidovaných tabulkách vám umožní objevit tuto dostatečně silnou funkčnost. Pokud máte stále dotazy, zeptejte se jich v komentářích, pokusíme se na ně odpovědět.