Konzistentné vytváranie programu Excel, práca s údajmi, odstránenie

Excel je pomerne výkonný nástroj, ktorý vám umožňuje pracovať s veľkými dátovými poliami a vykonávať s nimi množstvo operácií. Dá sa tvrdiť, že tabuľky Excel sú relačné databázy, s ktorými môžete pracovať bez toho, aby ste mali zručnosti programátora. Ak sa však program používa profesionálne, základné funkcie nestačia na generovanie správ o rôznych štruktúrach. Na tieto účely má tabletový procesor funkčnejší a výkonnejší nástroj - súhrnné tabuľky.

Prečo Excel potrebuje súhrnné tabuľky

V origináli tento výraz znie ako otočná tabuľka a preklad vety je trochu hmlistý: „Tabuľka, ktorú je možné zmeniť, skrútiť, demonštrovať v rôznych projekciách“. Čo to znamená? Interpretácia tabuľkových údajov - úloha nie je ľahká. Zvyčajne sa to robí pomocou vzorcov, dostávajúce konečné výsledky na konci tabuľky.

Ak potrebujete vytvárať komplexné správy pomocou filtrov a zmeny zdrojových údajov v správnom poradí, urobte to so štandardnými prostriedkami Excel, ak je to možné, je to veľmi ťažké. Ale vďaka konsolidovaným tabuľkám sa takéto správy oveľa ľahšie vytvárajú, rovnako ako rôzne diagramy, čo umožňuje iba pomocou myši na zoskupenie riadkov a stĺpcov, meniť ich na miestach, používať rôzne ovládacie prvky, používať rôzne ovládacie prvky.

Väčšina pravidelných používateľov programu Excel si ani neuvedomuje prítomnosť takého výkonného nástroja. Z dôvodu prehľadnosti uvádzame malý a indikatívny príklad použitia konsolidovaných tabuliek Excel „pre figuríny“.

Predpokladajme, že ste manažérom spoločnosti a ste zodpovední za udržiavanie predajnej dokumentácie. Všetky transakcie sú zadané v tabuľke, v ktorej sú uvedené všetky podrobnosti (predávajúci, kupujúci, množstvo, suma, dátum predaja, adresa kupujúceho atď. D.). Vedúci žiada o správu o predaji podľa regiónu.

Ako to robíme? Vytvárame rozloženie novej tabuľky, v ktorej uvádzame celú škálu predaných tovarov a v riadkoch - názvy regiónov. Aby sme to dosiahli, skopírujeme zo zdrojovej tabuľky stĺpca s tovarom a odstraňuje duplikáty. Pomocou režimu špeciálnej vložky prepravujeme tento stĺpec do klobúku konsolidovanej stránky. S regiónmi prichádzame rovnakým spôsobom, ale keďže sa nachádzajú v konsolidovanej tabuľke vertikálne, jednoducho skopírujeme bez transponovania.

V každej bunke sumarizujeme príjmy pomocou funkcie SUMMAIL. Nakoniec pridajte konečné výsledky na dno a pošlite správu správcovi. Vodca žiada regióny, aby zariadili vrchol a tovar - na linkách. Urobíte novú správu za 5 minút a získate novú úlohu: poskytnúť údaje o zisku, nie príjmy, to znamená, berúc do úvahy náklady. No, trochu zmeníme vzorec a výsledok je opäť pripravený v minútach. Nakoniec existuje žiadosť o prípravu takýchto správ pre každého predajcu s cieľom vyhodnotiť jeho efektívnosť. A to nie je problém pre súhrnnú tabuľku, ak viete, kde sa nachádzajú potrebné počiatočné údaje!

Medzitým každý používateľ programu Excel vie, aké ťažké je vykonať takéto manipulácie v rámci zdrojovej tabuľky.

Tvorba a práca s konsolidovanými tabuľkami v Exceli

Predpokladajme, že máme zdrojovú tabuľku predaja:

Zvážte, ako vytvoriť konsolidovanú tabuľku na základe týchto tabuľkových údajov. Ak to chcete urobiť, vyberte akúkoľvek bunku, po ktorej vyberieme kartu „Vložte“ na hlavnom paneli. Zobrazia sa dve ďalšie karty: „Súhrnná tabuľka“ a doľava - „Odporúčané súhrnné tabuľky“. Ak ste začiatočník a nechápete, ako môžete usporiadať zdrojové údaje, odporúčame vám použiť druhé tlačidlo. Toto je majster, ktorý vám ponúkne pripravené možnosti správy. Stačí si vybrať príslušnú možnosť a získať hotovú konsolidovanú tabuľku k dispozícii. Zostáva len napraviť, ak podľa niektorých kritérií vás nespĺňa.

Druhým, hlavným spôsobom vytvorenia konsolidovaných tabuliek je „od nuly“, pre ktoré stlačíme prvé tlačidlo.

Otvorí sa nové okno, v ktorom je potrebné nastaviť dva parametre: počiatočný rozsah bunky a umiestnenie ST. Pretože sme pred vytvorením otočnej tabuľky vybrali bunku, rozsah sa stanoví automaticky, predvolená hodnota pre druhý parameter je nový hárok. Takže nič nemeníme a klikneme na OK. Výsledkom je, že sa otvorí nový hárok s neúplným rozložením.

Ak chcete ho nakonfigurovať, použite pravý panel s názvom „Pole súhrnnej tabuľky“. Tu v hornej jednotke je uvedený zoznam dostupných polí zodpovedajúcich stĺpcovi zdrojovej tabuľky. Polia môžete pridať dvoma spôsobmi: umiestnením kontrolného znaku oproti požadovanej hodnote a potom sa automaticky dostane do rozloženia. Ale ďaleko od vždy na miesto, ktoré potrebujete. V takýchto prípadoch stačí pretiahnuť pole do potrebnej polohy rozloženia. Odstránenie polohy v rozložení sa vykonáva podobným spôsobom, odstránenie značky alebo ťahanie na pravom paneli.

Obsahuje štyri regióny nižšie, na základe ktorej sa vytvorí súhrnná tabuľka. Zvážte ich podrobnejšie:

• Oblasť hodnôt je hlavnou časťou ST, pretože je tu uvedené, ako spracovať stĺpce a čiary. Zvyčajne je to zhrnutie - táto metóda agregácie bude predvolene nastavená, za predpokladu, že všetky bunky rozsahu prijímajú číselné hodnoty. V prítomnosti aspoň jednej prázdnej bunky alebo s údajmi vo formáte, ktorý sa líši od číselnej bunky, namiesto súčtu hodnôt buniek sa ich číslo vypočíta, takže táto skutočnosť sa musí zohľadniť. Pre náš príklad sa sčítanie uskutoční pre každý produkt v príslušnej oblasti. Okrem zhrnutia môžete nastaviť aj inú metódu výpočtov, ktoré majú približne dve desiatky (výpočet priemernej hodnoty, zdieľania atď.). Najjednoduchší spôsob, ako to dosiahnuť, je priamo v súhrnnej tabuľke, kliknutím na PKM pre akúkoľvek bunku a výber potrebnej metódy výpočtov;
• Oblasť riadkov - pozostáva z jedinečných hodnôt buniek umiestnených v ľavom stĺpci. Ak tu vložíte niekoľko polí, súhrnná tabuľka už nebude plochá, ale viacúrovňa;
• Oblasť stĺpca obsahuje aj hodnoty poľa, ale už vzhľadom na hornú líniu štartovacej tabuľky;
• Filtrovacia plocha vám umožňuje uvaliť obmedzenia na podmienky výpočtu hodnôt buniek. Zvyčajne sa tu umiestni pole, podľa ktorého by sa mala filtrovať vzorka nastavená v ďalších troch oblastiach.

Vďaka prítomnosti týchto oblastí môžete nakonfigurovať vzorku zo štartovacej tabuľky takmer akejkoľvek zložitosti a stráviť niekoľko minút.

Aby sme to objasnili, vysvetlíme vyššie uvedený príklad opísaný v predchádzajúcej časti.

Takže krok -By -rýchlostné pokyny, ako vytvoriť konsolidovanú tabuľku v Exceli:

• Zo zoznamu polí v súhrnnej tabuľke pretiahneme pole „príjmy“ do oblasti hodnôt;
• Pole „región“ (oblasť kupujúceho) je vtiahnuté do riadkov;
• „Produkt“ vložený do oblasti stĺpca.

Iba tri akcie - a súhrnná tabuľka vhodná na odosielanie orgánov, je pripravená! Trvalo nám to asi 10 sekúnd, kým sme ho zostavili.

Ako splniť ďalšie želania riaditeľa?

Aby sa dosiahol zisk namiesto príjmov, stačí umiestniť zodpovedajúce pole do oblasti hodnôt namiesto príjmov, zodpovedajúce výpočty v zhrnutí sa automaticky uskutočnia.

Druhá úloha sa vykonáva aj v sekundách - zmeniť regióny a tovar, jednoducho ich pretiahnite z jednej oblasti na druhú (zmeny miesta).

Ak chcete získať výsledky pre každého manažéra, stačí umiestniť pole „Manager“ do oblasti filtra, potom sa v hornej časti, v ktorom musíte zadať požadované priezvisko, aby ste dostali správu, musíte zadať požadované priezvisko, aby ste dostali správu. Existuje možnosť kombinovať správu o niekoľkých predajcoch.

Samozrejme sme nepriniesli najťažšie, ale veľmi indikatívny príklad, ktorý vám umožní pochopiť, ako vytvoriť súhrnné tabuľky v Exceli. Metóda výpočtu hodnôt v bunkách môže byť zložitejšia, možno budete musieť použiť vypočítané polia, ako aj použiť podmienené formátovanie - možnosti v tomto ohľade sú pôsobivé.

O zdroji údajov

Už sme spomenuli, že určité požiadavky sa ukladajú na počiatočné údaje použité na správne vytvorenie konsolidovaných tabuliek v programe Excel. Uveďme ich:

• Hlavné obmedzenie je spojené s povinnou prítomnosťou mien v stĺpcoch zapojených do výpočtov. Takéto identifikátory sú potrebné na vytvorenie výsledných správ - pri pridávaní nových záznamov (reťazcov) do štartovacej tabuľky nie je potrebný na zmenu formátu ST a výsledky sa automaticky prepočítajú;
• Uistite sa, že v bunkách čiary a stĺpcov, ktoré sa zúčastňujú na vzorke, sa zavádzajú číselné parametre. Ak sú prázdne alebo obsahujú hodnoty textu, tieto riadky vypadnú z výpočtov, čo skreslia výsledky výpočtov;
• Postupujte podľa korešpondencie formátov čiar a obsahu buniek. Ak je definovaný ako dátum, potom všetky hodnoty v stĺpci musia mať rovnaký formát, inak filtrovanie a prepočítanie budú nesprávne.

Ak dodržiavate tieto pravidlá, nemalo by sa vzniknúť pri zostavení problémov so zhrnutím tabuľky.

Aktualizácia údajov v tabuľke Excel Set Tabuľka

Aj keď sme hovorili o automatickej prepočítaní konsolidovaných tabuliek pri úpravách zdrojových údajov alebo o štruktúre samotnej ST, v skutočnosti z dôvodu ukladania počítačových zdrojov musíte vykonať určité akcie.

Údaje môžete aktualizovať v našej tabuľke expozície Excel dvoma spôsobmi:

• Kliknutím na tlačidlo pravého myši na akejkoľvek bunke a výber položky „Aktualizácia“ v kontextovej ponuke;
• Pomocou karty „Data“ a tlačidla „Aktualizujte všetko“.

Ukladanie do vyrovnávacej pamäte tabuliek je obzvlášť opodstatnené, ak majú veľkú veľkosť, v tomto prípade sa rýchlosť programu výrazne zvýši, najmä ak pravidelne a s vysokou frekvenciou vstúpite do nových riadkov.

Pridanie stĺpca alebo riadku do programu Excel

Všeobecne platí, že operácia je triviálna, ak je požadovaný parameter v zdrojovej tabuľke. Stačí vtiahnuť správne pole do správnej oblasti. Ak v štartovej tabuľke nie je žiadny reťazec alebo stĺpec. Ďalej kliknite na kartu „Analýza“ a otvorte našu zmenenú tabuľku. Zostáva iba na aktualizáciu údajov (pozri. V predchádzajúcej časti), v dôsledku toho sa zoznam polí zmení na pravom paneli ST - k nej sa pridá nový parameter.

Súhrnná tabuľka často nemá celkom pohodlné druhy na vizualizáciu. Napríklad, keď je v riadkoch niekoľko polí. Potom je jeden z nich zobrazený v plnom rozsahu a hodnoty druhého musia byť uvedené v hornej časti tabuľky. Ak je málo takýchto hodnôt (povedzme, v našom príklade predajných oblastí iba 6), potom má zmysel pridať stĺpec „regióny“ otočnej tabuľky do zodpovedajúcej oblasti a preniesť ju z oblasti z oblasti čiarka.

Zmena postupu na sledovanie stĺpcov je tiež ľahko dosiahnuteľná, jednoducho prenesie stĺpec smerujúci do požadovanej polohy myšou.

Tvorba súhrnnej tabuľky z niekoľkých súborov (tabuľky) Excel

Toto je komplikovanejšia, ale celkom úplná úloha. Použite Master ST, ktorý je potrebné pridať na panel s rýchlym prístupom.

Takže vľavo, v blízkosti položky „Súbor“, kliknite na šípku nasmerovanú nadol, vyberte položku „Ostatné príkazy“. Otvorí sa nové okno, na strednom paneli klikneme na možnosti „všetky tímy“. V zozname „Master of Consolidated Tabuls“ nájdeme v zozname, ktorý sa otvára, vyberte ho a kliknite na tlačidlo „Pridať“. Zodpovedajúca ikona sa zobrazí v hornom paneli.

Spúšťame Majster. Predpokladajme, že musíme kombinovať údaje z dvoch listov, máj a v júni. Hlavnou podmienkou je, že sa musia zhodovať v štruktúre. Ak áno, potom v prvom okne Master vyberieme možnosť „Tabuľka látok“ a ďalší parameter - „niekoľko konsolidácií“.

V druhom kroku uvádzame pánovi, že potrebujeme niekoľko polí. Na treťom - vyberte rozsah buniek na zhrnutie prvého hárku, kliknite na tlačidlo „Pridať“ a urobiť to isté pre druhý Excel Leaf Excel. Teraz musíte dať mená dvom poliam. Urobíme to najskôr pre prvé pole (kliknite na selektor „1“, v stĺpci „1 pole“, ktoré zadávame „máj“), potom analogicky pre druhý. V takom prípade bude potrebné uviesť v zozname rozsahov príslušnú čiaru, prvý alebo druhý.

Po kliknutí na tlačidlo „Ready“ sa otvorí nové okno, v ktorom označujeme „nový list“ a znova stlačte tlačidlo „Ready“.

Pozornosť. Prijatie viacrozmerných tabuliek je nepohodlné v tom, že je ťažké navigovať s veľkým počtom polí a musia sa vykonať potrebné výpočty, zakaždým, keď označujú potrebné hodnoty z hornej oblasti tabuľky.

Ako odstrániť umenie

Najjednoduchší prípad - keď ste vytvorili konsolidovanú tabuľku, poslali výsledky šéfkuchárovi a už ho nepotrebujete. Ak ste si istí, jednoducho vyberte tabuľku a kliknite na tlačidlo Odstrániť. Jednoduché a efektívne.

Ale zrazu môže byť v budúcnosti potrebná štruktúra tabuľky? Excel má príležitosť odstrániť iba výsledky alebo údaje z buniek. Zvážte, ako sa to robí.

Na odstránenie výsledkov výpočtu vykonávame nasledujúce kroky:

• Označujeme každú bunku ST;
• Vyberte kartu „Analýza“ na hornom paneli;
• Zo zoznamu dostupných akcií vyberte „Clear“;
• Kliknite na bod „Vymažte všetko“.

Ale čo robiť, ak chcete uložiť výsledky, ale sami nepotrebujete údaje, to znamená, že chcete uvoľniť tabuľku? Táto situácia často vzniká, ak vedenie potrebuje iba výsledky. Algoritmus akcií:

• Opäť vyberte ľubovoľnú bunku, kliknite na kartu „Analýza“;
• Vyberte položku ponuky „Akcia“, kliknite na „Vybrať“, označte celú konsolidovanú tabuľku myšou;
• Kliknite na PCM vo vybranej oblasti;
• V ponuke kontextu vyberte položku „Kopír“;
• Prejdeme na kartu „Domov“, znova kliknite na PKM a vyberte „Vložte“;
• Vyberte kartu „Vložte hodnotu“, v nej si všimneme parameter „vložiť ako hodnotu“.

Výsledkom je, že súhrnná tabuľka sa vymaže pri zachovaní výsledkov.

Poradiť. Postup môžete urýchliť pomocou kombinácie kľúčov. Na výber tabuľky použite Ctrl + A na kopírovanie - Ctrl + C. Potom stlačte ALT + E, ALT + S, ALT + V a dokončite postup stlačením Enter.

Ak chcete odstrániť konsolidované tabuľky v programe Excel 2007/2010, musíte použiť iný algoritmus:

• Vyberte ST navrhnuté na odstránenie;
• Kliknite na kartu „Parametre“;
• V „Skupine akcií“ vyberte položku „Clean“ a potom - „Vyčistite všetko“.

Ak váš šéf miluje vizualizáciu údajov, je zrejmé, že budete musieť použiť konsolidované diagramy. Pretože zaberajú veľa miesta v tabuľke, po použití sa zvyčajne odstránia.

V starých verziách programu musíte zvoliť diagram kliknite na kartu „Analýza“, vyberte skupinu údajov a kliknite na tlačidlo „Clean“ a „Vyčistiť všetko“.

Okrem toho, ak je diagram spojený s najhrznutejšou tabuľkou, po jej vymazaní stratíte všetky nastavenia tabuľky, jej polia a formátovanie.

V prípade verzií starších ako Excel 2010 musíte vybrať diagram, na karte „Analýza“ vyberte „Akcie“ a kliknite na tlačidlo „Clean“ a „Vyčistiť všetko“. Výsledok bude podobný.

Dúfame, že naše hodiny na konsolidovaných tabuľkách vám umožnia objaviť túto dostatočne silnú funkčnosť. Ak máte stále otázky, opýtajte sa ich v komentároch, pokúsime sa im odpovedať.