Vzorec kontingenční tabulky v Excelu Kroky k použití vzorce kontingenční tabulky v Excelu

Obsah:

Anonim

Vzorec kontingenční tabulky v Excelu (obsah)

  • Vzorec kontingenční tabulky v Excelu
  • Vlastní pole pro výpočet výše zisku
  • Pokročilý vzorec ve vypočteném poli

Vzorec kontingenční tabulky v Excelu

Kontingenční tabulka je nástroj, který nám umožňuje analyzovat velké rozsahy dat. Můžeme analyzovat, interpretovat a dělat mnoho dalších věcí, aniž bychom si zlomili hlavu a pot. Může poskytnout téměř vše, co je ve zdrojových datech.

Pokud jsou ve zdrojových datech některé informace, možná to budeme muset vypočítat sami. Například, pokud máme celkovou prodejní částku a celkové náklady, možná budeme muset vypočítat celkový zisk nebo ztrátu sami.

Toto nemusíme dělat ve zdroji, ale můžeme to udělat uvnitř samotné kontingenční tabulky, která se v kontingenční tabulce nazývá Vypočítaná pole. Můžeme použít vlastní vzorce k tomu, abychom data mohli z těchto dat vyprávět více příběhů. Vypočítaná pole nám umožňují vytvořit nový vypočítaný sloupec, který ve skutečném zdroji dat neexistuje.

V tomto článku si ukážeme způsoby, jak pomocí výpočtových polí kontingenční tabulky vytvořit nové sloupce na základě našich požadavků.

Vlastní pole pro výpočet výše zisku

Tuto šablonu kontingenční tabulky vzorců Excel si můžete stáhnout zde - šablonu kontingenční tabulky vzorců Excel

Toto je nejčastěji používané vypočtené pole v kontingenční tabulce. Podívejte se na níže uvedené údaje, mám sloupec Název země, Název produktu, Prodané jednotky, Jednotková cena, Hrubý prodej, COGS (Náklady na prodané zboží), Datum a Rok.

Dovolte mi použít kontingenční tabulku, abych zjistil celkový prodej a celkové náklady pro každou zemi. Níže je kontingenční tabulka pro výše uvedená data.

Problém je v tom, že ve zdrojových datech nemám sloupec zisku. Potřebuji zjistit zisk a procento zisku pro každou zemi. Tyto dva sloupce můžeme přidat do samotné kontingenční tabulky.

Krok 1: Vyberte buňku v kontingenční tabulce. Na pásu karet přejděte na kartu Analyzovat a vyberte pole, položky a sady. V tomto poli vyberte Vypočítané pole.

Krok 2: V níže uvedeném dialogovém okně zadejte název nového vypočítaného pole.

Krok 3: V části Vzorec použijte vzorec k nalezení zisku. Vzorec k nalezení zisku je hrubý prodej - COGS.

Jděte dovnitř lišty vzorců> Z níže uvedeného pole vyberte Hrubý prodej a dvakrát na něj klikněte.

Nyní zadejte symbol mínus (-) a vyberte COGS> Dvakrát klikněte.

Krok 4: Klepnutím na PŘIDAT a OK dokončete vzorec.

Krok 5: Nyní máme v kontingenční tabulce sloupec CELKEM ZISKU.

Toto vypočtené pole je flexibilní, neomezuje se pouze na analýzu jednotlivých zemí, ale můžeme jej použít pro všechny druhy analýz. Pokud chci vidět analýzu podle jednotlivých zemí a produktů, musím jen přetáhnout sloupec produktu do pole ROW, což ukáže rozdělení zisku pro každý produkt v každé zemi.

Krok 6: Nyní musíme vypočítat procento zisku. Vzorec pro výpočet procenta zisku je celkový zisk / hrubý prodej.

Přejděte na Analyzovat a znovu vyberte Vypočítané pole v polích, položkách a sadách.

Krok 7: Nyní musíme vidět nově vložené vypočítané pole Celkový zisk v seznamu Pole. Vložte toto pole do vzorce.

Krok 8: Zadejte symbol děliče (/) a vložte pole hrubého prodeje.

Krok 9: Pojmenujte toto vypočtené pole jako procento zisku.

Krok 10: Klepnutím na PŘIDAT a OK dokončete vzorec. Jako nový sloupec máme procento zisku.

Pokročilý vzorec ve vypočteném poli

Všechno, co jsem teď ukázal, je základní věc vypočteného pole. V tomto příkladu vám ukážu pokročilé vzorce v vypočítaných polích kontingenční tabulky. Nyní chci vypočítat motivační částku na základě procenta zisku.

Pokud je zisk%> 15%, pobídka by měla představovat 6% celkového zisku.

Pokud je zisk%> 10%, pobídka by měla představovat 5% celkového zisku.

Pokud je zisk% <10%, pobídka by měla být 3% z celkového zisku.

Krok 1: Přejděte na Vypočítané pole a otevřete níže uvedené dialogové okno. Zadejte jméno jako motivační částka.

Krok 2: Nyní použiji podmínku IF k výpočtu motivační částky. Použijte níže uvedené vzorce podle obrázku.

= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))

Krok 3: Pro dokončení klikněte na PŘIDAT & OK. Nyní máme sloupec Incentive Amount.

Omezení vypočteného pole

Viděli jsme zázrak Calculated Fields, ale má také některá omezení. Nyní se podívejme na níže uvedený obrázek, pokud chci vidět rozpis motivační částky podle produktu, budeme mít chybný SUB TOTAL & GRAND TOTAL INCENTIVE AMOUNT.

Při zobrazování mezisoučtu vypočtených polí proto buďte opatrní. Ukáže vám nesprávné částky.

Získejte seznam všech vypočtených vzorců pole

Pokud nevíte, kolik vzorců existuje v poli vypočtené kontingenční tabulky, můžete získat shrnutí všech těchto vzorců v samostatném pracovním listu.

Přejděte na Analyzovat> Pole, položky a sady -> Seznam vzorců.

Poskytne vám shrnutí všech vzorců v novém listu.

Důležité informace o vzorci kontingenční tabulky v Excelu

  • Můžeme smazat, upravit všechna vypočtená pole.
  • Ve vypočtených polích nemůžeme použít vzorce jako VLOOKUP, SUMIF a mnoho dalších vzorců zahrnutých do rozsahu, tj. Nelze použít všechny vzorce, které vyžadují rozsah.

Doporučené články

Toto byl průvodce vzorcem kontingenční tabulky v Excelu. Zde jsme diskutovali kroky k použití vzorce kontingenční tabulky v Excelu spolu s příklady a šablonou Excel ke stažení. Můžete se také podívat na tyto užitečné funkce v Excelu -

  1. Výukové programy na kontingenčním grafu v Excelu
  2. Vytváření kontingenční tabulky v Excelu
  3. Výukový program VLOOKUP v Excelu
  4. Excel vytvořit databázi