Vzorec kontingenčnej tabuľky v Exceli (obsah)
- Kontingenčná tabuľka vzorca v Exceli
- Vlastné pole na výpočet výšky zisku
- Pokročilý vzorec vo vypočítanom poli
Kontingenčná tabuľka vzorca v Exceli
Kontingenčná tabuľka je nástroj, ktorý nám umožňuje analyzovať veľké rozsahy údajov. Môžeme analyzovať, interpretovať a robiť mnoho ďalších vecí bez toho, aby sme si zlomili väčšinu hlavy a potu. Môže poskytnúť takmer všetko, čo sa nachádza v zdrojových údajoch.
Ak sú niektoré informácie obsiahnuté v zdrojových údajoch, možno bude potrebné ich vypočítať pre seba. Napríklad, ak máme celkovú sumu predaja a celkové náklady, možno budeme musieť sami vypočítať celkový zisk alebo stratu.
Toto nemusíme robiť v zdroji, ale môžeme to urobiť vnútri samotnej kontingenčnej tabuľky, ktorá sa nazýva Vypočítané polia vo vnútri kontingenčnej tabuľky. Môžeme použiť vlastné vzorce na to, aby údaje vypovedali viac príbehov z údajov. Vypočítané polia nám umožňujú zostaviť nový vypočítaný stĺpec, ktorý v skutočnom zdroji údajov neexistuje.
V tomto článku si ukážeme spôsoby použitia polí vypočítaných kontingenčnou tabuľkou na vytvorenie nových stĺpcov na základe našich požiadaviek.
Vlastné pole na výpočet výšky zisku
Túto šablónu kontingenčnej tabuľky vzorcov Excel si môžete stiahnuť tu - šablónu kontingenčnej tabuľky vzorcov ExcelToto je najčastejšie používané vypočítané pole v kontingenčnej tabuľke. Pozrite sa na údaje uvedené nižšie, mám stĺpec Názov krajiny, Názov produktu, Predané jednotky, Jednotková cena, Hrubý predaj, COGS (Náklady na predaný tovar), Dátum a Rok.
Dovoľte mi použiť kontingenčnú tabuľku, aby som zistil celkový predaj a celkové náklady pre každú krajinu. Nižšie je uvedená kontingenčná tabuľka pre vyššie uvedené údaje.
Problém je v tom, že v zdrojových údajoch nemám stĺpec zisku. Potrebujem zistiť percento zisku a zisku pre každú krajinu. Tieto dva stĺpce môžeme pridať do samotnej kontingenčnej tabuľky.
Krok 1: Vyberte bunku v kontingenčnej tabuľke. Prejdite na kartu Analyzovať na páse s nástrojmi a vyberte položku Polia, položky a sady. V tomto poli vyberte Vypočítané pole.
Krok 2: V nižšie uvedenom dialógovom okne pomenujte nové vypočítané pole.
Krok 3: V časti Vzorec použite vzorec na nájdenie zisku. Vzorec na zistenie zisku je hrubý predaj - COGS.
Vstúpte do lišty vzorcov> Z nižšie uvedeného poľa vyberte položku Hrubý predaj a dvakrát kliknite na ikonu, ktorá sa objaví na lište vzorcov.
Teraz napíšte znamienko mínus (-) a vyberte COGS> Dvakrát kliknite.
Krok 4: Kliknutím na tlačidlo PRIDAŤ a OK dokončite vzorec.
Krok 5: Teraz máme v kontingenčnej tabuľke stĺpec CELKOM ZISKU.
Toto vypočítané pole je flexibilné, neobmedzuje sa iba na analýzu podľa krajiny, ale môžeme ju použiť na všetky druhy analýz. Ak chcem vidieť analýzu z hľadiska krajiny a produktu, musím len presunúť stĺpec produktu do poľa ROW, a to zobrazí rozdelenie zisku pre každý produkt v každej krajine.
Krok 6: Teraz musíme vypočítať percento zisku. Vzorec na výpočet percenta zisku je celkový zisk / hrubý predaj.
Prejdite na Analyzovať a znova vyberte Vypočítané pole v poliach, položkách a množinách.
Krok 7: Teraz musíme v zozname Polia vidieť novo vložené vypočítané pole Celkový zisk. Toto pole vložte do vzorca.
Krok 8: Zadajte symbol deliča (/) a vložte pole hrubého predaja.
Krok 9: Pomenujte toto vypočítané pole ako percento zisku.
Krok 10: Kliknutím na tlačidlo PRIDAŤ a OK dokončite vzorec. Ako nový stĺpec máme percento zisku.
Pokročilý vzorec vo vypočítanom poli
Všetko, čo som teraz ukázal, je základné veci vypočítaného poľa. V tomto príklade vám ukážem pokročilé vzorce v vypočítaných poliach kontingenčnej tabuľky. Teraz chcem vypočítať motivačnú sumu na základe percenta zisku.
Ak je zisk%> 15%, motivácia by mala predstavovať 6% celkového zisku.
Ak je zisk%> 10%, motivácia by mala byť 5% z celkového zisku.
Ak je zisk% <10%, motivácia by mala byť 3% z celkového zisku.
Krok 1: Prejdite na Vypočítané pole a otvorte nižšie uvedené dialógové okno. Názov uveďte ako motivačná čiastka.
Krok 2: Teraz použijem podmienku na výpočet stimulačnej sumy. Použite nasledujúce vzorce podľa obrázka.
= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))
Krok 3: Dokončite kliknutím na tlačidlo PRIDAŤ a OK. Teraz máme stĺpec Motivačná suma.
Obmedzenie vypočítaného poľa
Videli sme zázrak Calculated Fields, ale má aj niektoré obmedzenia. Teraz sa pozrite na obrázok nižšie, ak chcem vidieť rozdelenie stimulačnej sumy podľa produktu, budeme mať chybný SUB TOTAL & GRAND TOTAL z INCENTIVE AMOUNT.
Pri zobrazovaní medzisúčtu vypočítaných polí buďte opatrní. Ukáže vám nesprávne sumy.
Získajte zoznam všetkých vypočítaných vzorcov poľa
Ak neviete, koľko vzorcov existuje v poli vypočítanej kontingenčnej tabuľky, môžete získať ich súhrn v samostatnom pracovnom hárku.
Prejdite na položku Analyzovať> Polia, položky a množiny -> Vzorce vzorcov.
Poskytne vám zhrnutie všetkých vzorcov v novom pracovnom hárku.
Dôležité informácie o vzorci kontingenčnej tabuľky v Exceli
- Môžeme vymazať, upraviť všetky vypočítané polia.
- Vo vypočítaných poliach nemôžeme použiť vzorce ako VLOOKUP, SUMIF a mnoho ďalších vzorcov zahrnutých do rozsahu, tj nie je možné použiť všetky vzorce, ktoré vyžadujú rozsah.
Odporúčané články
Toto bol sprievodca vzorcom kontingenčnej tabuľky v Exceli. Tu sme diskutovali o krokoch na použitie vzorca kontingenčnej tabuľky v Exceli spolu s príkladmi a šablónou Excel na stiahnutie. Tieto užitočné funkcie sa môžete tiež pozrieť v exceli -
- Výukové programy na kontingenčnom grafe v Exceli
- Vytváranie kontingenčnej tabuľky v Exceli
- Výukový program VLOOKUP v Exceli
- Vytvorenie databázy Excel