Súčty kontingenčnej tabuľky programu Excel podľa riadkov. Zobrazenie údajov „prvej desiatky“. Podrobné zobrazenie údajov

Jednou z nepríjemnejších vecí na kontingenčných tabuľkách je to, že celkový súčet, kde sú dáta zhrnuté, je vždy v spodnej časti tabuľky, čo znamená, že musíte prejsť celou tabuľkou, aby ste videli čísla. Presuňme celkový súčet na vrchol, kde je ľahšie ho nájsť.

Aj keď sú kontingenčné tabuľky skvelým nástrojom na sumarizáciu údajov a na zvýraznenie zmysluplných informácií, nemajú zabudovanú funkciu na presunutie celkového súčtu na vrchol, kde je ľahké ho nájsť. Predtým, ako popíšeme veľmi všeobecný spôsob presunu celkového súčtu na začiatok, pozrime sa najskôr, ako to urobiť pomocou funkcie GETPIVOTDATA, ktorá je špeciálne na načítanie údajov z kontingenčnej tabuľky.

Túto funkciu je možné použiť takto: \u003d GETPIVOTDATA („Suma sumy“; $ B $ 5), v ruskej verzii Excelu \u003d GET.DATA.PERSONAL.TABLE („Suma podľa poľa“; $ B $ 5) alebo takže: \u003d GETPIVOTOATA ("Suma"; $ B $ 5), v ruskej verzii programu Excel GET.DATA.COMPLETE.TABLES ("Suma"; $ v $ 5).

Obidve funkcie zvýraznia požadované údaje a sledujú celkový súčet, keď ich posúvate nahor, nadol, doľava alebo doprava. Použili sme adresu bunky $ B $ 5, ale ak zadáte ľubovoľnú bunku v kontingenčnej tabuľke, vždy získate celkovú hodnotu.

Prvá funkcia používa pole Suma čiastky a druhá používa pole Suma. Ak sa v oblasti údajov kontingenčnej tabuľky nachádza pole Suma, musíte pole Názov pomenovať. Ak sa však pole Množstvo použije v oblasti Údajov viackrát, musíte zadať názov, ktorý ste mu priradili, alebo predvolený názov (Obrázok 4.5).

Ak chcete zmeniť tieto polia, musíte na ne dvakrát kliknúť. To môže byť mätúce, ak ešte úplne nerozumiete kontingenčným tabuľkám. Našťastie v programe Excel 2002 a novších je tento proces oveľa jednoduchší, pretože do bunky môžete umiestniť argumenty a pomocou myši použiť správnu syntax funkcie. Do ľubovoľnej bunky zadajte \u003d (znamienko rovnosti) a kliknite na bunku, ktorá obsahuje celkový súčet. Excel automaticky vloží požadované argumenty.

Bohužiaľ, ak použijete Sprievodcu funkciami alebo najskôr napíšete \u003d GET.PIVOTDATA () (\u003d GETPIVOTDATA ()) a potom kliknete na bunku, ktorá obsahuje celkový súčet, Excel vytvorí neporiadok tak, že sa do tejto bunky pokúsi vložiť ešte jednu funkciu ZÍSKAŤ PIVOTNÉ ÚDAJE.

Pravdepodobne najjednoduchší a menej mätúci spôsob, ako získať celkový súčet, je použitie funkcie \u003d МАХ (PivGTCol) v ruskej verzii programu Excel \u003d MAKC (PivGTCol), kde sa stĺpec obsahujúci celkový súčet nazýva PivGTCol.

Okrem toho môžete pomocou funkcií VEĽKÉ a MALÉ načítať zbierku čísel z kontingenčnej tabuľky na základe ich veľkosti. Napríklad nasledujúci vzorec vyberie z kontingenčnej tabuľky druhé najväčšie číslo: \u003d LARGE (PivGTCol; 2), v ruskej verzii Excelu \u003d LARGE (PivGTCol; 2).

Okamžite nad kontingenčnú tabuľku môžete pridať viac riadkov a umiestniť tam tieto vzorce, aby ste videli tento typ informácií okamžite bez toho, aby ste museli rolovať do dolnej časti kontingenčnej tabuľky.

Pre prácu s kontingenčnými tabuľkami existuje špeciálny panel s nástrojmi Kontingenčná tabuľka , ktorá umožňuje rýchle vykonávanie základných operácií súvisiacich s úpravami a formátovaním kontingenčných tabuliek.

Rozbaľovacia ponuka Kontingenčná tabuľka panel nástrojov obsahuje operácie, ktoré sa vykonávajú na samotnom paneli, a niektoré ďalšie príkazy. Napríklad pomocou podponuky Vzorce môžete vytvoriť vypočítané pole a nastaviť poradie výpočtov.

Tím možnosti umožňuje definovať názov kontingenčnej tabuľky, možnosti zobrazenia a formátovania, ako aj konfigurovať možnosti externých údajov, ak sa tabuľka vytvára na externom zdroji údajov.

Extrakcia dát.

V kontingenčnej tabuľke sú dva režimy zvýrazňovania údajov: Môžete vybrať časti tabuľky alebo použiť normálny výber. V predvolenom nastavení je režim výberu povolený pre celé časti kontingenčnej tabuľky. V tomto režime kliknutím na hlavičku poľa ju vyberiete spolu s prvkami poľa a po kliknutí na prvok poľa sa vyberú aj údaje zodpovedajúce tomuto prvku. Okrem toho zvýraznením častí kontingenčnej tabuľky zvýrazníte hlavičky tabuľky aj samotné údaje. Môžete zvoliť iba hlavičky alebo iba údaje výberom príslušného nástroja na paneli nástrojov Kontingenčná tabuľka :

Môžete tiež nastaviť spôsob výberu častí kontingenčnej tabuľky pomocou príkazu Vyberte rozbaľovaciu ponuku Kontingenčná tabuľka na paneli nástrojov. Tu môžete vybrať celú tabuľku ako celok výberom príkazu Celá tabuľka ... Ak chcete prepnúť do režimu normálneho výberu (tj. Nevyberať časti, ale bunky tabuľky), stlačte tlačidlo v rovnakom menu. Na zachovanie formátov jeho regiónov po aktualizácii a zmene štruktúry kontingenčnej tabuľky slúži tlačidlo Povoliť výber musí byť stlačené.

Polia, položky poľa a súčty je možné z kontingenčnej tabuľky odstrániť. Za týmto účelom vyberte časť tabuľky, ktorá sa má vymazať (tlačidlo Povoliť výber musí byť stlačené) a vyberte príkaz Odstrániť z kontextového menu.

Aktualizácia pôvodných údajov.

Ak ste zmenili údaje v pôvodných tabuľkách, musíte kontingenčnú tabuľku aktualizovať na základe týchto údajov. Kliknite na toto tlačidlo panely nástrojov Kontingenčná tabuľka alebo zvoľte príkaz Obnoviť údaje z rozbaľovacej ponuky Kontingenčná tabuľka ... Údaje sa tiež aktualizujú, ak ste pridali riadky (alebo stĺpce v prípade konsolidácie rozsahov) vo vnútri pôvodné rozsahy.

Ak boli pridané riadky alebo stĺpce do pôvodné rozsahy, musíte zavolať sprievodcu kontingenčnou tabuľkou kliknutím na tlačidlo Sprievodca kontingenčnou tabuľkou na paneli nástrojov. Návratom k druhému kroku sprievodcu môžete pôvodné rozsahy prepísať.

Všeobecné a medzisúčty.

Výberom príkazu možnosti z rozbaľovacej ponuky Kontingenčná tabuľka , Môžete určiť, či kontingenčná tabuľka vyžaduje súčet podľa riadkov a / alebo stĺpcov. Ak má kontingenčná tabuľka pole stránky a niektoré z hodnôt tohto poľa sú skryté, tu môžete tiež objasniť, či súčty obsahujú údaje zodpovedajúce skrytým hodnotám polí stránky. Ak je začiarkavacie políčko Medzisúčet skryté položky na stránke (zahŕňajú skryté hodnoty) vypnuté, pri prezeraní kontingenčnej tabuľky, ktorá sa zhoduje so všetkými hodnotami poľa stránky, nebudú skryté hodnoty zahrnuté do celkových súčtov.

Ak kontingenčná tabuľka má viac polí orientovaných na stĺpec alebo viac polí orientovaných na stránku, môže užívateľ definovať medzisúčty pre každé také pole.

Ak chcete definovať medzisúčty pre pole, jednoducho na ne dvakrát kliknite alebo vyberte ikonu Pole kontingenčnej tabuľky na paneli nástrojov. Je možné zvoliť medzisúčty pre pole

• automatické - suma sa vypočíta pre každý prvok poľa. Automatické súčty je možné zvoliť iba pre externé pole (v našom príklade toto pole rok);
• vypočítané pomocou štandardnej funkcie vybranej zo zoznamu (môžete vybrať niekoľko takýchto súčtov pre vonkajšie aj vnútorné pole);
• úplne opustiť medzivýsledky.

Ak ste definovali medzisúčty, pre každú položku poľa sa pridá stĺpec medzisúčtu (riadok). Ak existuje viac súčtov, počet ďalších stĺpcov pre každú položku sa vynásobí počtom súčtov.
Zoskupovanie údajov.

Môže sa stať, že potrebujete súčty nie pre všetky prvky poľa, ale pre niektoré skupiny údajov v poli (napríklad prvkami poľa sú názvy mesiacov a súčty sa musia sčítať podľa štvrťrokov). V takom prípade možno prvky poľa zoskupiť tak, že ich vyberiete a kliknete na ikonu Skupina na paneli nástrojov. Budete mať ešte jedno pole, ktorého prvkami budú hlavičky skupín. Okrem toho každý nezoskupený prvok vytvára samostatnú skupinu, ktorej názov sa zhoduje s názvom prvku. Vo vyššie uvedenom príklade je jedno pole stĺpca, ktoré obsahuje štyri prvky: januára, februára, marca a apríla... Tri prvky ( januára, februára, marca) boli zoskupené. Prvok apríla nebol zahrnutý v skupine. Vo výsledku sa objavilo ďalšie pole mesiac2obsahuje dva prvky: Skupina1 a apríla... Lúka mesiac stále obsahuje názvy mesiacov: januára, februára, marca a apríla... Lúka mesiac2 je externé pole, t.j. zoskupuje vnútorné pole mesiac... Môžete definovať medzisúčty pre nové pole a požadované súčty sa vypočítajú pre každú skupinu údajov. Ak chcete zoskupiť prvky poľa, jednoducho vyberte požadovanú skupinu a kliknite na ikonu Oddeliť na paneli nástrojov. Ak nezostanú žiadne zoskupené položky, ďalší vonkajší okraj zmizne.

Vypočítané polia a členovia poľa.

Štruktúra tabuľky sa zvyčajne vytvára z hlavičiek polí pôvodného zoznamu v prípade jedného rozsahu údajov alebo z objektov Riadok, Stĺpec, Hodnota, Stranaak existuje viac rozsahov. Často nie je k dispozícii dostatok polí na analýzu údajov obsiahnutých v zdrojových tabuľkách. Kontingenčná tabuľka má napríklad dve údajové polia: množstvo a súčet, a musíte určiť priemernú cenu produktu, ktorá sa určí ako kvocient týchto dvoch polí. V takom prípade si musíte vytvoriť svoj vlastný vypočítané pole - Pole kontingenčnej tabuľky, ktoré používa používateľom definovaný vzorec, ktorý umožňuje vykonávať výpočty na základe hodnôt iných polí v kontingenčnej tabuľke. K tomu stačí postupne zvoliť príkazy Vzorce , Vypočítané pole z rozbaľovacej ponuky Kontingenčná tabuľka ... Pomocou polí, ktoré môžete vybrať zo zoznamu, môžete vytvoriť vlastný vzorec pre vypočítané pole. Pomocou Sprievodcu kontingenčnou tabuľkou môžete umiestniť nové pole do požadovanej oblasti kontingenčnej tabuľky.

Správy niekedy vyžadujú nielen ďalšie polia, ale aj prvky poľa. Stáva sa to v prípadoch, keď potrebujete pole, ktoré nejakým spôsobom analyzuje údaje existujúcich prvkov poľa. Napríklad riadkové pole alebo stĺpcové pole obsahuje prvky množstvo a súčet, a musíte určiť priemernú cenu produktu, ktorá sa určí ako podiel týchto dvoch prvkov. V takýchto prípadoch by ste nemali vytvárať vypočítané pole, ale vypočítaná položka - prvok poľa kontingenčnej tabuľky, ktorý používa používateľom definovaný vzorec, ktorý umožňuje vykonávať výpočty na základe hodnôt ostatných prvkov poľa kontingenčnej tabuľky. Ak chcete vytvoriť vypočítanú položku, musíte vybrať pole alebo položku poľa, do ktorej sa pridá nový objekt, a postupne zvoliť príkazy Vzorce , Vypočítaná položka z rozbaľovacej ponuky Kontingenčná tabuľka ... Potom by ste mali určiť názov nového prvku a nastaviť vzorec, podľa ktorého sa má vypočítať. Vzorce, ktoré počítajú ďalšie položky, používajú názvy existujúcich položiek poľa kontingenčnej tabuľky. Ak má viac polí prvky s rovnakým názvom, musíte pred názov prvku vložiť názov poľa. Nová položka sa pridá do poľa, ktoré ste vybrali (alebo ktorého položku ste vybrali) pred vytvorením vypočítaného objektu.

Doplnenie programu Excel o nástroje na spracovanie údajov (doplnky Správca správ a Hľadanie riešenia ), v dialógovom okne Doplnky Ponuka Služby skontrolujte príslušný prepínač a kliknite na ikonu Ok (Obr. 1).

Ak sa zobrazí výzva, že vybratý doplnok nemožno nájsť, pravdepodobne sa nenainštaluje. V takom prípade nainštalujte doplnok z inštalačnej diskety.

Obr. Dialógové okno Doplnky.

Vypracovanie záverečných správ

Účel zadania: naučiť sa zostavovať medzisúčty.

Vykonajme napríklad analýzu hodnôt pre tabuľku obsahujúcu údaje o nákupe tlačiarní a skenerov podnikom pre jeho oddelenia (tabuľka 1). Tento problém je možné vyriešiť použitím bežných vzorcov, ale použitie funkcie automatického výpočtu súčtov môže jeho implementáciu výrazne zjednodušiť.

stôl 1

Výrobok		názov	cena	Číslo- v	Množstvo
	Matrix
	Matrix
	Matrix
	Jet	Epson Stylus 1520
	Jet	Epson Stylus 3000
	Jet	Epson Stylus 1500
	Jet	Epson Stylus Photo 700
	Laserové	HP Laser Jet 4000
	Laserové	HP Laser Jet 5000
	Laserové	HP Laser Jet Color 8500
	List	Paragon Strana 630
	List	Stránka Paragon ľahko
	Tableta	Paragon 800IIEP
	Tableta	ScanExpress 6000 SP
	Tableta	Paragon 1200 SP
	Tableta	ScanExpress A3 str

Sumy vynaložené na nákup nájdeme osobitne pre všetky tlačiarne a zvlášť pre všetky skenery pomocou automatického výpočtu súčtov. Pre to:

Ak ešte neboli zoradené, najskôr zoraďte údaje tabuľky podľa typu produktu. Ako druhé kritérium triedenia môžete nastaviť napríklad názov;

Vyberte tabuľku alebo aspoň jednu bunku tabuľky;

V ponuke Údaje vyberte tím Výsledky . Otvorí sa dialógové okno Medzisúčty (Obr. 2.).

Rozbaľovacia ponuka S každou zmenou v vyberte hlavičku stĺpca, pre ktorý chcete vypočítať medzisúčty po každej zmene údajov v hárku. (vo vašom prípade by ste mali zvoliť prvok Výrobok).Aby bolo možné dáta pri určovaní súčtov sčítať, z poľa zoznamu Prevádzka vyberte funkciu Množstvo. V tomto dialógovom okne musíte tiež určiť stĺpec, ktorého bunky sa používajú na výpočet súčtov. V našom prípade, aby sme zhrnuli ukazovatele množstva tovaru a čiastok vynaložených na nákup, v teréne Pridajte súčty do začiarknite políčka vedľa riadkov Množstvo a Množstvo.

V dôsledku vykonania funkcie bude tabuľka doplnená o riadky, ktoré zobrazia súčty pre každú skupinu zvlášť (obr. 3). Posledný riadok vložený do tabuľky obsahuje informácie o celkovom súčte.

Obr. Tabuľka je príkladom použitia funkcie automatických súčtov.

Pre údaje každej skupiny stĺpcov vybratej v dialógovom okne Medzisúčty , je možné vykonávať nasledujúce funkcie:

Funkcia	Vymenovanie
	Sčíta všetky hodnoty a dá celkový súčet
Počet hodnôt	Určuje počet prvkov v skupine
	Určuje aritmetický priemer skupiny
Maximum	Určuje najväčšiu hodnotu v skupine a najväčšiu hodnotu v celom stĺpci
	Určuje najmenšiu hodnotu pre každú skupinu a pre celý stĺpec
Zloženie	Určuje súčin všetkých hodnôt v skupine a súčin celého stĺpca
Počet čísel	Určuje počet buniek obsahujúcich číselné hodnoty v skupine a celkový počet buniek s číselnými hodnotami pre všetky skupiny stĺpcov
Nestranná odchýlka	Určuje hodnotu štandardnej odchýlky pre populáciu, ak sa údaje vzorkujú
Odchýlka odchýlky	Určuje štandardnú odchýlku populácie, ak údaje tvoria populáciu
Nestranný rozptyl	Určuje hodnotu odchýlky, ak sa údaje vzorkujú
Posunutý rozptyl	Určuje hodnotu odchýlky pre populáciu, ak údaje tvoria populáciu

Pri výpočte súčtov bola tabuľka štruktúrovaná - môžete to vidieť na obrazovke. Pomocou úrovní štruktúr môžete poskytnúť lepšiu vizuálnu kontrolu nad svojimi údajmi. Pre zobrazenie iba súhrnných údajov na obrazovke kliknite na tlačidlo pre druhú úroveň štruktúry, v dôsledku čoho sa skryjú údaje tretej úrovne (jednotlivé hodnoty) (obr. 4, 5). Ak chcete znova zobraziť jednotlivé hodnoty, musíte kliknúť na tlačidlo pre tretiu úroveň

Obr. Možnosť zobrazenia súhrnných údajov (tretia úroveň je skrytá).

Obr. Možnosť zobrazenia súčtov.

Ak chcete vymazať zadané medzisúčty, stačí zvoliť príkaz Výsledky v ponuke Údaje a kliknite na tlačidlo Odobrať všetky .

Vypočítajte si sami :

Priemerná cena tlačiarní a skenerov;

Počet druhov mien tlačiarní a skenerov;

Sumy vynaložené na nákup každého typu produktu (obr. 6)

Obr. Sumy vynaložené na nákup každého typu produktu.

Predvolene sa pod riadky s hodnotami jednotlivých skupín údajov vkladajú riadky obsahujúce celkové hodnoty. Ak sa majú súčty uvádzať pred údajmi o skupine, táto možnosť by mala byť vypnutá. Výsledky pod údajmi v dialógovom okne Medzisúčty .

Pri tlači môže byť každá skupina údajov so súčtom uvedená na samostatnej stránke. Za týmto účelom nastavte túto možnosť Koniec stránky medzi skupinami v dialógovom okne Medzisúčty .

Príprava konsolidovaných správ

Účel zadania: naučte sa, ako zostaviť konsolidované správy.

Funkcia konsolidácie sa používa, keď potrebujete vypočítať súčty pre údaje umiestnené v rôznych oblastiach tabuľky. Pomocou funkcie konsolidácie môžete vykonávať rovnaké operácie s hodnotami v nesúvislých oblastiach buniek ako s funkciou automatického medzisúčtu. Rozsahy buniek, ktoré sa majú konsolidovať, môžu byť umiestnené na rovnakom pracovnom hárku alebo na rôznych hárkoch, ako aj v rôznych zošitoch.

Ako príklad použijeme predchádzajúci príklad. Rozdeľte iba údaje o tlačiarni a skeneri na dvoch pracovníkov list, premenovať “ List1”Do“ Tlačiarne ”a“ Hárok2„v“ Skenery”.

Zostavíme konsolidovanú správu o nákupoch tlačiarní a skenerov (údaje umiestnené na rôznych listoch zošita). Pre to:

Vyberte „ List3„A daj tomu nové meno“ správa“. Vytvorte „hlavičku“ pre novú tabuľku (obr. 8);

Vyberte bunku A 7 (“Výrobok”), A v ponuke Údaje vyberte tím Konsolidácia . V dialógovom okne s rovnakým názvom, ktoré sa otvorí, zadajte rozsahy buniek, ktoré sa majú konsolidovať, a typ operácie (funkcie) (obr. 7.):

Obr. Dialógové okno Konsolidácia

uistite sa, že v krabici Funkcia je funkcia Množstvo ,

kliknite do poľa Odkaz a potom na karte hárok „ Tlačiarne”, Vyberte blok buniek s informáciami o tlačiarňach vrátane nadpisu, kliknite na tlačidlo Pridať k . Rovnakým spôsobom postupujte aj pri údajoch pre skenery.

v krabici Použiť ako mená možnosti Titulky v hornom riadku a Hodnoty ľavého stĺpca nastaviť konsolidáciu podľa názvu a hodnoty v riadkoch s rovnakými štítkami, aj keď sa nachádzajú v nesúvislých oblastiach buniek, sa spočítajú,

Kliknite na Ok , vo výslednej konsolidovanej správe sú stĺpce „ Typ” a „ názov” možno skryť (obr. 8)

8. Obr. Konsolidovaná správa

Ak zmeníte hodnoty v pôvodných rozsahoch buniek, je potrebné konsolidáciu zopakovať. Ak sa štruktúra zdrojových tabuliek (rozsahy buniek) nezmení, potom sa dá zabrániť neustálemu opakovaniu tohto postupu prepojením konsolidovaných údajov s originálom. V takom prípade bude konsolidovaný rozsah buniek obsahovať bunky s externými odkazmi na pôvodné údaje. Keď sa zmenia údaje v pôvodnom rozsahu buniek, automaticky sa zmenia aj konsolidované údaje. Ak chcete vytvoriť väzby medzi konsolidovanými a pôvodnými údajmi, nastavte túto možnosť Vytvorte odkazy na zdrojové údaje v dialógovom okne Konsolidácia (Obr. 7) .

V takom prípade po stlačení tlačidla Ok medzi pôvodnými údajmi a výsledkami konsolidácie sa nastaví dynamický pokles, aby sa zabezpečila automatická aktualizácia údajov. Konsolidácia s prepojením má ďalšiu výhodu: v tomto prípade sa údaje konsolidujú pomocou štruktúrovacej funkcie a druhá úroveň štruktúry predstavuje jednotlivé hodnoty, z ktorých sa konsolidované hodnoty počítajú.

Ak sa pôvodné údaje nachádzajú v tom istom zošite, aktualizácia sa uskutoční automaticky. Pre pôvodné údaje nachádzajúce sa v iných zošitoch bude ale potrebné aktualizáciu údajov vynútiť pomocou príkazu Pripojenia Ponuka Upraviť .

Vytváranie kontingenčných tabuliek a kontingenčných grafov

Kontingenčné tabuľky sú určené na analýzu veľkého množstva údajov. S ich pomocou môžu byť údaje analyzovanej tabuľky selektívne prezentované vo forme, ktorá najlepšie odráža závislosti medzi nimi.

Kontingenčná tabuľka sa používa na analýzu údajov uložených v programe Excel, to znamená, že na prácu potrebujeme údaje, ktoré navrhujeme skopírovať z nasledujúcej tabuľky na obr. 9 do nášho pracovného hárka.

Obr. Údaje pre kontingenčnú tabuľku.

Vytvorte kontingenčnú tabuľku.

Vyberte tím Kontingenčná tabuľka Ponuka Údaje alebo kliknite na tlačidlo Kontingenčné tabuľky paneli nástrojov na spustenie kontingenčnej tabuľky a Sprievodcu grafom. Na obrazovke sa zobrazí dialógové okno Sprievodcovia kontingenčnými tabuľkami (Obr. 10) .

Obrázok: 10 Sprievodca kontingenčnou tabuľkou.

V skupine Vytvorte tabuľku na základe nájdených údajov : Vyberte prepínač zodpovedajúci dostupným zdrojom údajov. V našom príklade sú údaje uvedené v zozname programu Microsoft Excel.

V skupine Typ vygenerovaného prehľadu: nainštalovať kontingenčnú tabuľku. Kliknite na tlačidlo Ďalej - objaví sa okno:

V teréne Rozsah : (Obr. 11) Zadajte rozsah obsahujúci pôvodné údaje. Môžete to urobiť dvoma spôsobmi: zadajte adresu alebo názov rozsahu buniek pomocou klávesnice alebo vyberte rozsah buniek v hárku pomocou myši.

Vyberte umiestnenie vytvorenej kontingenčnej tabuľky (obr. 12). Ak ste vybrali umiestnenie na rovnakom hárku, zadajte rozsah umiestnenia alebo iba začiatočnú bunku.

Kliknite na tlačidlo Rozloženie…. Zobrazí sa dialógové okno na zostavenie kontingenčnej tabuľky (obr. 13).

Pri označovaní rozloženia potiahnite myšou tlačidlá polí v oblasti:

Tlačidlo potiahnutia názov do regiónu Riadok , tlačidlo Výrobca do regiónu Strana tlačidlo Štvrťrok do regiónu Stĺpec , tlačidlá Obrata čiastka do regiónu Údaje (Obr. 14).

Kliknite na tlačidlo Ok , a potom hotový ... Dostanete kontingenčnú tabuľku (obr. 15.)

Obr. 15 Súhrnná tabuľka.

Konfigurácia možností kontingenčnej tabuľky.

Pravým tlačidlom myši kliknite na ľubovoľnú bunku už vytvorenej kontingenčnej tabuľky a v zobrazenej ponuke kliknite na ikonu Parametre tabuľky. Na obrazovke sa objaví okno Možnosti kontingenčnej tabuľky (Obr. 16).

Obr. Okno parametrov kontingenčnej tabuľky.

V tomto dialógovom okne názov je nastavený názov kontingenčnej tabuľky. Začiarknuté políčka Spolu podľa stĺpcov a Spolu podľa riadkov oznamená, že sa vytlačí kontingenčná tabuľka úhrn a Súčty polí... Začiarknuté políčko Automatický formát znamená, že na kontingenčnú tabuľku sa použije predvolené nastavenie Automatický formát... Začiarknuté políčko Zahrňte skryté hodnoty znamená, že hodnoty skrytých buniek sa zohľadnia v súčtoch. Kombinovať bunky s titulkami všetkých vonkajších riadkov a stĺpcov treba zaškrtnúť políčko Skombinujte bunky hlavičky ... Zaškrtnite políčko, aby ste chránili formáty buniek kontingenčnej tabuľky pred zmenami, ktoré sa obnovujú alebo menia rozloženie Pokračujte vo formátovaní ... V poliach Pre zobrazenie chýb , Prázdne bunky zobrazte zadajte hodnoty, ktoré sa majú zobraziť v bunkách v prípade chyby alebo chýbajúcich údajov. Tieto polia budú k dispozícii, iba ak sú k dispozícii príslušné začiarkavacie políčka. Začiarknite políčko Uložte údaje spolu s tabuľkou skopírovať pôvodné údaje, z ktorých boli vytvorené táto tabuľkamať k nim vždy prístup. Začiarknite políčko Nasadenie povolené aby bolo možné zobraziť podrobné údaje dvojitým kliknutím na dátovú oblasť kontingenčnej tabuľky. Začiarknite políčko Obnoviť pri otvorení obnovenie kontingenčnej tabuľky pri otvorení zošita programu Excel, ktorý ho obsahuje. Ak pri vytváraní kontingenčnej tabuľky používate externé zdroje, nastavte v skupine potrebné parametre Externé údaje .

Zatvorte okno kliknutím na tlačidlo Ok .

Filtrovanie údajov.

Kliknite na Ok , vo vašom zošite sa objavili ďalšie hárky. Excel umiestni každú skupinu údajov do samostatného hárka.

Podrobné zobrazenie údajov.

Dvakrát kliknite na prvok poľa, pre ktorý chcete zobraziť podrobnosti. Na obrazovke sa objaví okno Zobraziť podrobnosti (Obr. 18).

Obr. Okno pre zobrazenie podrobných údajov.

Z poskytnutého zoznamu vyberte pole obsahujúce časti, ktoré chcete zobraziť, a kliknite na tlačidlo Ok ... Zodpovedajúce riadky alebo stĺpce sa vložia do kontingenčnej tabuľky.

Obr. Podrobné údaje o kontingenčnej tabuľke.

Ak nechcete zobraziť podrobné údaje, znova dvakrát kliknite na položku údajového poľa.

Triedenie údajov.

Polia v kontingenčnej tabuľke sú automaticky zoradené vzostupne podľa ich mien. Poradie zoradenia môžete zmeniť neskôr.

Zoraďte údaje podľa poľa úhrn... Ak to chcete urobiť, vyberte údaje na triedenie, vyberte z ponuky Údaje prvok Triedenie ... Na obrazovke sa zobrazí dialógové okno. Triedenie ... Nastavte rozsah v skupine Triediť podľa a v skupine Triediť nastavte prepínač význam ... Kliknite na tlačidlo Ok (Obr. 20).

Obr. 20 Okno triedenia údajov.

Ak chcete pri aktualizácii (zmene) tabuľky automaticky triediť údajové polia podľa názvu, dvakrát kliknite na tlačidlo poľa, ktoré sa má zoradiť, a v okne, ktoré sa zobrazí. kliknite na tlačidlo Ďalej ... Na obrazovke sa objaví okno (Obr. 21). V skupine Možnosti triedenia nastavte prepínač vzostupne alebo zostupne, v zozname pomocou poľa vyberte pole, ktorého prvky budú zoradené. Zavrieť okno.

Obr. Okno ďalších parametrov.

Na manuálne zoradenie položiek kontingenčnej tabuľky podľa hodnôt buniek dátovej oblasti v dialógovom okne Pokročilé možnosti poľa kontingenčnej tabuľky v skupine Možnosti triedenia nastavte prepínač ručne , potom zatvorte okno. Vyberte pole na zoradenie a zvoľte príkaz Triedenie ponuka Údaje . V okne, ktoré sa zobrazí v poli triediť od zadajte pole dátovej oblasti, ktorá sa má triediť. Začnite triediť. Ak kontingenčná tabuľka obsahuje položky, ktoré sú zvyčajne zoradené nie abecedne, ale chronologicky, napríklad nedeľa, pondelok, utorok atď. Excel automaticky používa vlastné poradie zoradenia. Excel obsahuje viac zoznamov zoradenia a použitie karty Zoznamy okno možnosti (prvok možnosti ponuka Služby ) môžete definovať vlastný triediaci zoznam.

Zobrazenie údajov „prvej desiatky“.

V vytvorenej kontingenčnej tabuľke môžete odrážať zadaný počet položiek iba s maximálnymi alebo minimálnymi hodnotami.

Dvakrát kliknite na dátové pole, ktorého položky sa budú selektívne zobrazovať.

V okne, ktoré sa zobrazí Výpočet poľa kontingenčnej tabuľky kliknite na tlačidlo Ďalej ... Na obrazovke sa objaví okno Pokročilé možnosti poľa kontingenčnej tabuľky .

V skupine Možnosti zobrazenia nastavte prepínač automatické(Obr. 21). V teréne displej zadajte počet položiek na zobrazenie a v zozname displej vyberte požadovanú možnosť triedenia: najväčší alebo najmenší ... V zozname pomocou poľavyberte dátové pole, ktorého položky sa zobrazia. Kliknite na tlačidlo Ok ... Zavrieť okno.

Zmeny funkcie na výpočet celkových súčtov.

Pri vytváraní kontingenčnej tabuľky program Excel automaticky zobrazuje celkové súčty, pomocou funkcie súčtu počíta v poliach s číselnými hodnotami a pomocou tejto funkcie počíta počet hodnôt v poliach s inými údajmi. Tieto funkcie je možné neskôr zmeniť.

Kliknite na tlačidlo Parametre poľa na paneli nástrojov Kontingenčné tabuľky ... Na obrazovke sa objaví okno Výpočet poľa kontingenčnej tabuľky.

Vyberte vhodnú funkciu zo zoznamu Prevádzka (Obr. 22) a kliknite na tlačidlo Ok .

Obr. Aplikácia funkcií na výpočet celkových súčtov.

Zadanie ďalších výpočtov pri sčítaní.

Môžete tiež použiť inú skupinu funkcií na sumarizáciu, ktorá umožňuje vypočítať súčty hodnôt v údajovej oblasti na základe iných hodnôt v údajovej oblasti a porovnať ich.

Vyberte pole v kontingenčnej tabuľke, ktoré obsahuje automaticky sumarizovanú tabuľku.

Kliknite na tlačidlo Parametre poľa na paneli nástrojov Kontingenčné tabuľky . Na obrazovke sa objaví okno Výpočet poľa kontingenčnej tabuľky ... Kliknite na tlačidlo Dodatočne , rozbaliť okno.

Vyberte zo zoznamu Dodatočné výpočty požadovaná funkcia. Ak je to potrebné, v zozname lúka vyberte názov poľa a v zozname prvok - prvok používaný ako základná hodnota pri vykonávaní ďalších výpočtov. Kliknite na tlačidlo Ok (Obr. 23.)

Obr. Vykonávanie ďalších výpočtov.