Cheat Sheet pre vzorce Excel: Sprievodca pre pokročilých

Takže ste prešli základmi, zvládli ste stredne pokročilé veci a teraz ste pripravení zápasiť s veľkými zbraňami – pokročilými vzorcami Excelu! Excel má veľa pokročilých funkcií a vzorcov pre sofistikované výpočty, takže je užitočné mať sprievodcu, ktorý presne určí tie, ktoré potrebujete.

Tento cheat pre vzorce Excel obsahuje pokročilé vzorce na predpovedanie, štatistickú analýzu, funkcie manipulácie s údajmi, spracovanie chýb a ďalšie.

Táto príručka vás vybaví znalosťami, ako používať tieto pokročilé funkcie. Každý vzorec je doplnený jasnými vysvetleniami, syntaxou a praktickými príkladmi, ktoré pomôžu stredne pokročilým používateľom Excelu stať sa pokročilými používateľmi.

Stiahnite si a vytlačte cheat sheet a majte ho po ruke.

Ok, začnime.

Najprv sa pozrime na vzorce Array.

Obsah

• Vzorce poľa
  • 1. UNIKÁTNA funkcia
  • 2. Funkcia SORT
  • 3. Funkcia FILTER
• Randomizácia funkcií Excelu
  • 1. Funkcia RANDBETWEEN
  • 2. Funkcia RANDARRAY
• Pokročilé vzorce predpovedí v programe Microsoft Excel
  • 1. Funkcia FORECAST.ETS
  • 2. Funkcia TREND
• Pokročilé štatistické vzorce
  • 1. PERCENTIL Funkcia
  • 2. Funkcia QUARTILE
• Pokročilá analýza údajov a manipulačné vzorce
  • 1. Funkcia TRANSPOSE
  • 2. Funkcia FREKVENCIE
  • 3. Funkcia GEPTIVOTDATA
• Pokročilé spracovanie chýb
• Vzorce rozšíreného vyhľadávania
  • 1. Funkcia XLOOKUP
  • 2. Funkcia XMATCH
• Záverečné myšlienky

Vzorce poľa

Náš ukazuje, ako manuálne triediť a filtrovať údaje. Pokročilí používatelia to robia programovo pomocou vzorcov poľa.

Vzorce poľa umožňujú vykonávať výpočty na viacerých bunkách súčasne. Toto sú tri kľúčové funkcie:

1. UNIKÁTNY

2. TRIEDIŤ

3. FILTER

Niektoré z týchto funkcií sú dostupné iba v najnovších verziách programu Microsoft Excel.

1. UNIKÁTNA funkcia

Funkcia UNIQUE akceptuje rozsah a vráti zoznam jedinečných hodnôt.

Predpokladajme, že máte údaje o predaji odevov. Ak chcete nájsť jedinečné položky v stĺpci A, použite tento vzorec:

= UNIQUE(A2:A6)

2. Funkcia SORT

Funkcia SORT triedi obsah rozsahu. Syntax je:

SORT(pole; [index_triedenia]; [poradie_triedenia]; [podľa_stĺpec])

• pole : rozsah hodnôt, ktoré sa majú zoradiť.

• sort_index : stĺpec, ktorý sa má zoradiť (predvolene 1)

• poradie_triedenia : 1 pre vzostupné (predvolené) alebo 2 pre zostupné).

• by_col : TRUE na triedenie podľa stĺpca (predvolené) alebo FALSE na triedenie podľa riadka.

Posledné tri argumenty sú voliteľné a predvolené hodnoty sú zvyčajne také, aké chcete.

Ak chcete zoradiť vzorové údaje podľa prvého stĺpca, použite tento vzorec:

=SORT(A2:C6)

3. Funkcia FILTER

Funkcia FILTER vám umožňuje filtrovať rozsah podľa konkrétnych podmienok. Toto je syntax:

=FILTER(pole, zahrnúť, [ak_prázdne])

• pole : rozsah, ktorý sa má filtrovať.

• include : podmienka, ktorá určuje, ktoré hodnoty sa majú filtrovať.

• if_empty : určuje, čo sa má vrátiť, ak žiadne hodnoty nespĺňajú kritériá filtrovania (predvolená hodnota je „“).

Predpokladajme, že chcete filtrovať riadky vo vzorových údajoch tak, aby zobrazovali iba predajné hodnoty vyššie ako 15 USD. Použite tento vzorec:

=FILTER(A2:C6; C2:C6>15)

Randomizácia funkcií Excelu

Náš prechodný cheat ukazuje, ako používať funkciu RAND, ktorá vytvára náhodné číslo medzi 0 a 1.

Pokročilí používatelia Excelu vedia, ako používať náhodné funkcie na rýchle generovanie vzorových údajov.

1. Funkcia RANDBETWEEN

Funkcia RANDBETWEEN je flexibilnejšia ako funkcia RAND, pretože môžete zadať spodné a najvyššie čísla ako niečo iné ako 0 a 1.

Ak chcete vygenerovať údaje s číslami od 1 do 100, zadajte tento vzorec do bunky A1:

=RANDBETWEEN(1; 100)

Potom skopírujte bunku do ľubovoľného počtu riadkov a stĺpcov. Vytvorenie mriežky náhodných čísel trvá niekoľko sekúnd:

2. Funkcia RANDARRAY

Možno si myslíte, že by bolo pekné vyhnúť sa manuálnemu kopírovaniu funkcie RANDBETWEEN. Ak chcete dosiahnuť super pokročilosť, môžete použiť novú funkciu RANDARRAY v najnovšej verzii programu Microsoft Excel.

Syntax je:

RANDARRAY([riadky]; [stĺpce]; [min]; [max]; [celé číslo])

• riadky : počet riadkov

• stĺpce : počet stĺpcov

• min : najnižšie číslo

• max : najvyššie číslo

• celé číslo : predvolená hodnota je TRUE, inak používa desatinné čísla.

Ak chcete vygenerovať náhodné čísla od 1 do 100 v šiestich riadkoch a deviatich stĺpcoch, použite tento vzorec:

=RANDARRAY(6; 9; 1; 100; TRUE)

Pokročilé vzorce predpovedí v programe Microsoft Excel

Prognostické funkcie Excelu sa používajú na predpovedanie budúcich hodnôt na základe existujúcich trendov údajov. Tieto funkcie pomáhajú identifikovať vzory a trendy projektu na základe vašich údajov.

1. Funkcia FORECAST.ETS

Staršia funkcia FORECAST bola v Exceli 2016 nahradená sadou novších funkcií.

Funkciu si vyberiete na základe konkrétneho modelu prognózy, ktorý chcete. Napríklad funkcia FORECAST.ETS používa algoritmus exponenciálneho vyhladzovania .

Syntax je:

FORECAST.ETS(cieľový_dátum, hodnoty, časová os)

• target_date : dátum, pre ktorý chcete vypočítanú hodnotu.

• hodnoty : historické údaje.

• časová os : rozsah dátumov

Predpokladajme, že v stĺpci A máte dátumy od 1. do 5. januára a v stĺpci B sumy predaja. Tento vzorec predpovedá ďalšiu sumu predaja:

=FORECAST.ETS(“6. január 2023”, B2:B6, A2:A6)

2. Funkcia TREND

Funkcia TREND premieta množinu hodnôt na základe metódy najmenších štvorcov. Vracia pole. Syntax je:

TREND(známy_y; [známy_x]; [nový_x]; [konšt.])

• známy_y : rozsah hodnôt y

• známy_x : rozsah hodnôt x

• new_x : rozsah vypočítaných hodnôt

Známe_y sú často dátové body, kým známe_x sú dátumy.

Pomocou rovnakých údajov ako v predchádzajúcom príklade môžete do bunky C2 zadať vzorec uvedený nižšie. Vygeneruje sa súbor hodnôt.

=TREND(B2:B6; A2:A6)

Pokročilé štatistické vzorce

Pokročilé štatistické funkcie zahŕňajú výpočet percentilov a kvartilov. Niektoré matematické funkcie sú k dispozícii pre spätnú kompatibilitu, ale odporúča sa používať najaktuálnejšie verzie.

1. PERCENTIL Funkcia

Táto funkcia vypočíta percento údajových bodov, ktoré spadajú pod určitú hodnotu. Syntax je:

PERCENTILE.INC(pole; k)

• pole : rozsah buniek

• k : percentil od 0 do 1

Predpokladajme, že chcete vypočítať 70. percentil údajov v stĺpci B. Použite tento vzorec:

=PERCENTIL.INC(B2:B6; 0,7)

2. Funkcia QUARTILE

Táto funkcia je variáciou percentilovej funkcie, ale na rozdelenie údajov používa štvrtiny. Toto je syntax:

QUARTILE.INC(pole; kvart)

• pole : rozsah údajov

• kvart : 1 pre 25. percentil, 2 pre 50. percentil, 3 pre 75. percentil a 4 pre maximum.

Vzorec uvedený nižšie vypočíta prvý kvartil údajov v stĺpci A.

=QUARTILE.INC(A2:A5; 1)

Pokročilá analýza údajov a manipulačné vzorce

Niekoľko pokročilých funkcií vám umožňuje prepínať formát údajov, analyzovať rozloženie frekvencie a extrahovať údaje z kontingenčných tabuliek.

1. TRANSPOSE

2. FREQUENCY

3. GETPIVOTDATA

1. Funkcia TRANSPOSE

Niekedy chcete presunúť údaje v riadkoch do stĺpcov a naopak. Môžete to urobiť manuálne alebo namiesto toho použiť funkciu TRANSPOSE.

Predpokladajme, že máte položky „Tričko“, „Mikina“ a „Džínsy“ v bunkách A2, A3 a A4. Chcete z nich urobiť nadpisy stĺpcov. Táto funkcia vráti hodnoty v jednom riadku:

=TRANSPOSE(A2:A4)

2. Funkcia FREKVENCIE

Táto funkcia vypočítava frekvenčné rozdelenie hodnôt v rámci množiny údajov. Toto je syntax:

FREQUENCY(data_array; bins_array)

• data_array : rozsah hodnôt.

• bins_array : intervaly, ktoré sa majú použiť.

Predpokladajme, že máte v stĺpci B údaje o predaji a chcete analyzovať rozdelenie frekvencie hodnôt na základe toho, koľko súm:

• pod 20.

• od 20 do 80.

• nad 80.

To predstavuje tri zásobníky a možno ho vypočítať pomocou tohto vzorca:

=FREQUENCY(A2:A6; {20,80})

Ďalšie informácie o distribúcii frekvencií v Exceli nájdete v tomto videu:

3. Funkcia GEPTIVOTDATA

Táto funkcia vám umožňuje extrahovať súhrnné informácie z kontingenčných tabuliek. Toto je syntax:

GEPTIVOTDATA(údajové_pole, kontingenčná_tabuľka, [pole1, položka1], [pole2, položka2], …)

• data_field : dátové pole alebo hodnota, ktorú chcete získať z kontingenčnej tabuľky.

• kontingenčná_tabuľka : odkaz na kontingenčnú tabuľku.

• pole1, položka1 atď. : dvojice polí a položiek, ktoré sa majú filtrovať.

Predpokladajme, že máte kontingenčnú tabuľku založenú na farbe predávaných položiek. Ak chcete extrahovať tržby za červené položky, použite tento vzorec:

=GETPIVOTDATA("Predaj", A1, "Farba", "Červená")

Pokročilé spracovanie chýb

Dokonca aj tie najzákladnejšie vzorce Excelu môžu spôsobiť chyby. Stredne pokročilí používatelia by mali vedieť, ako používať ISERROR na spracovanie chýb. Pokročilí používatelia by mali poznať aj funkciu ERROR.TYPE na identifikáciu chýb.

Funkcia ERROR.TYPE pomáha identifikovať špecifický typ chyby v bunke alebo vzorci.

Vráti číselnú hodnotu zodpovedajúcu rôznym typom chýb, ako napríklad #N/A, #HODNOTA!, #REF! a ďalšie.

Predpokladajme, že máte chybu v bunke A1 a chcete identifikovať jej typ chyby. Nasledujúci vzorec vráti číslo, ktoré zodpovedá konkrétnej chybe:

=ERROR.TYPE(A1)

Môžete to skombinovať s viacerými funkciami, aby ste reagovali odlišne v závislosti od typu chyby. Toto sú najčastejšie chyby a ich hodnoty:

1. #NULOVÝ! (v rozsahu sa nenašla žiadna spoločná bunka)

2. #DIV/0! ( delenie nulou alebo prázdna bunka)

3. #HODNOTA! (nevhodný typ údajov alebo argument vo vzorci)

4. #REF! (odkazovaná bunka bola odstránená alebo existuje )

5. #NÁZOV? (Excel nerozpozná funkciu alebo rozsah)

6. #NUM! (neplatná číselná hodnota)

7. #N/A (hodnota sa nedá nájsť)

Predpokladajme, že chcete spracovať tri špecifické typy chýb. Tento vzorec použite na zobrazenie konkrétneho chybového hlásenia na základe typu:

=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, “Rozdelenie nulovou chybou”, IF(ERROR.TYPE(C2)=3, “Chyba neplatnej hodnoty”, IF(ERROR.TYPE(C2) )=7, “Chyba nenašla hodnota”, “Iná chyba”))), C2)

Vzorce rozšíreného vyhľadávania

Naše cheaty pre začiatočníkov a stredne pokročilých pokryli . Tu je niekoľko pokročilých možností:

1. XLOOKUP

2. XMATCH

1. Funkcia XLOOKUP

Táto vyhľadávacia funkcia vám umožňuje vyhľadať hodnotu v rozsahu a vrátiť zodpovedajúcu hodnotu z iného stĺpca alebo rozsahu.

Ponúka viac univerzálnosti ako jednoduchšie vyhľadávacie funkcie ako . Toto je syntax:

XLOOKUP(vyhľadávacia_hodnota, vyhľadávacie pole, návratové pole, [režim_zhody], [režim_vyhľadávania], [ak_nenájdený])

• lookup_value : hodnota, ktorú chcete vyhľadať.

• lookup_array : rozsah pre vyhľadávanie.

• return_array : rozsah, ktorý zobrazí zodpovedajúcu hodnotu.

• match_mode : presná zhoda (0), nasledujúca menšia (1), nasledujúca väčšia (-1) alebo zhoda so zástupným znakom (2).

• search_mode : -1 pre zhora nadol, 1 pre zdola nahor alebo 2 pre binárne vyhľadávanie.

• if_not_found : nastaví hodnotu, ktorá sa vráti, ak sa nenájde žiadna zhoda.

Predpokladajme, že chcete vyhľadať rozsah údajov pre prvý výskyt položky oblečenia a vrátiť sumy predaja. Tento vzorec vyhľadá text „Mikina“ a vráti hodnotu v susednej bunke, ak sa nájde:

=XLOOKUP("Mikina s kapucňou", A2:A6, B2:B6)

2. Funkcia XMATCH

Táto funkcia vám umožňuje nájsť polohu zadanej hodnoty v rámci rozsahu alebo poľa. Toto je syntax:

XMATCH(hodnota_hľadania; pole_hľadania; [typ_zhody]; [režim_vyhľadávania])

• lookup_value : hodnota, ktorú chcete nájsť.

• lookup_array : Rozsah, ktorý chcete vyhľadať.

• match_type : presná zhoda (0), ďalšia najmenšia (-1), ďalšia najväčšia (1).

• search_mode : binárne vyhľadávanie (1) alebo lineárne vyhľadávanie (2).

Predpokladajme, že chcete nájsť prvý výskyt žltej položky v rozsahu v stĺpci B. Použite tento vzorec:

=XMATCH("Žltá", B2:B6; 0)

Záverečné myšlienky

Tento cheat pokrýva širokú škálu funkcií, od štatistickej analýzy, vyhľadávacích vzorcov, techník manipulácie s údajmi a stratégií spracovania chýb.

Poskytnuté príklady a vysvetlenia pomáhajú demystifikovať tieto pokročilé vzorce a sprístupňujú ich aj tým, ktorí majú obmedzené skúsenosti.

Keď ich začnete začleňovať do svojich úloh programu Excel, ste na dobrej ceste zvýšiť svoje zručnosti v Exceli na pokročilú úroveň.

Ale pamätajte, že tento cheat je len špičkou ľadovca. Skutočne úžasné veci sa stanú, keď budete kreatívni, zmiešate a spojíte tieto vzorce a prispôsobíte ich tak, aby vyriešili vaše jedinečné výzvy. Excel je ako plátno a tieto vzorce sú vašou paletou – tak pokračujte a namaľujte svoje majstrovské dielo!