Cheat Sheet pre vzorce Excel: Stredne pokročilý sprievodca

Takže sa vám podarilo zvládnuť – česť vám! Teraz ste sa rozhodli nakopnúť veci o stupeň vyššie a ponoriť sa do pokročilejších vecí, však? No, máte šťastie, pretože sme pre vás zostavili šikovný hárok Excel pre stredne pokročilých!

Tento cheat pre pokročilých v programe Microsoft Excel pokrýva širokú škálu tém, ktoré zvýšia vaše zručnosti v oblasti numerickej analýzy, spracovania textu a komplexnej logiky dátumu a času.

Ak máte hárok Excel Intermediate Cheat Sheet ako referenciu na pracovnej ploche, môžete sa ponoriť hlboko do výkonnejších vzorcov a funkcií programu Microsoft Excel.

Teraz sa uistite, že čítajte ďalej, ale tiež si vytlačte a uložte cheat nižšie!

Začnime!

Obsah

• Stredne pokročilé matematické vzorce
  • Podmienená logika
• Stredné štatistické vzorce
  • 1. MINA a MAXA
  • 2. COUNTA a COUNTIF
• Vzorce programu Excel pre finančnú analýzu
  • 1. Funkcia PRODUKT
  • 2. Funkcie QUOTIENT a LOG
  • Funkcie pre investičné scenáre
• Stredne pokročilé vzorce dátumu a času v Exceli
  • 1. Medzimesačné funkcie
  • 2. Funkcie stredného týždňa
  • 3. Medzičasové funkcie
• Excel funkcie pre podmienenú a logickú algebru
  • 1. Funkcia IF
  • 2. Funkcia AND
  • 3. Funkcia ALEBO
  • 4. NIE Funkcia
• Odkazujúce bunky
  • 1. Funkcia NEPRIAME
  • 2. Funkcia INDEX
  • 3. Funkcia OFFSET
• Stredné textové vzorce
  • 1. Funkcia TEXTJOIN
  • 2. Funkcia REPLACE
  • 3. Funkcia SUBSTITUTE
• Vzorce stredného vyhľadávania
• Vzorce na spracovanie chýb programu Microsoft Excel
  • 1. Funkcia IFERROR
  • 2. Funkcia IFNA
• Skratky vzorcov a funkcií
• Stredné formátovanie buniek
• Záverečné myšlienky

Stredne pokročilé matematické vzorce

Náš cheat pre začiatočníkov pokrýval najbežnejšie matematické funkcie pre začiatočníkov, ako je funkcia SUM. Stredne pokročilí používatelia by sa mali oboznámiť s týmito zložitejšími funkciami:

• ABS() vráti absolútnu hodnotu čísla.

• SQRT() vráti druhú odmocninu čísla

• RAND() poskytuje náhodné číslo medzi 0 a 1

Podmienená logika

Stredne pokročilí používatelia by tiež mali vedieť, ako vykonávať matematické výpočty na základe viacerých podmienok pomocou týchto funkcií:

• SUMIFS(rozsah_sumu; rozsah_kritérií1; kritérium1; [….])

• COUNTIFS(rozsah_kritérií1; kritérium1; [rozsah_kritérií2; kritériá2]; …)

• AVERAGEIFS(rozsah_kritérií1; kritérium1; [rozsah_kritérií2; kritériá2]; …)

Funkcie vyhodnocujú poskytnuté kritériá a vytvárajú logickú hodnotu TRUE alebo FALSE.

Povedzme, že máte v stĺpcoch A, B a C nasledujúce údaje o predaji:

Na výpočet sumy predajov za červené tričká použite vzorec SUMIFS:

=SUMIFS(C2:C6; A2:A6; “Tričko”, B2:B6; “Červené”)

Ak chcete spočítať počet riadkov s červenými tričkami, použite vzorec COUNTIFS:

=COUNTIFS(C2:C6; A2:A6; “Tričko”, B2:B6; “Červené”)

Na výpočet priemerného predaja červených tričiek použite vzorec AVERAGEIFS:

=AVERAGEIFS(C2:C6; A2:A6; “Tričko”, B2:B6; “Červené”)

Stredné štatistické vzorce

Okrem základných funkcií ako MIN a MAX má Excel širšiu škálu štatistických vzorcov pre stredne pokročilých používateľov. Tu sú niektoré z najužitočnejších:

• MINA

• MAXA

• COUNTA

• COUNTIF

1. MINA a MAXA

Bežnejšie používané funkcie MIN a MAX ignorujú textové hodnoty.

Alternatívne funkcie MINA a MAXA zohľadňujú pri hľadaní najvyšších alebo najnižších hodnôt text aj čísla. Textové hodnoty sa vyhodnocujú, ako keby boli nulové.

Rozdiely sú znázornené na obrázku nižšie. Prvý riadok vyhodnotí minimálnu hodnotu 10, zatiaľ čo druhý riadok sa vyhodnotí ako 0 z dôvodu prítomnosti textovej hodnoty.

2. COUNTA a COUNTIF

Funkcia COUNTA sa používa v rozsahu.

Funkcia COUNTIF je menej špecifická v tom, že sa používa na počítanie počtu buniek v rámci rozsahu, ktoré spĺňajú špecifickú podmienku alebo kritériá.

Tieto funkcie môžete použiť aj na spočítanie počtu odlišných hodnôt v stĺpci. Toto video ukazuje, ako na to.

Vzorce programu Excel pre finančnú analýzu

Existuje niekoľko vzorcov, ktoré budete chcieť použiť pri vykonávaní finančnej analýzy, ako je predpovedanie investícií:

• PRODUKT(číslo1, [číslo2…])

• QUOTIENT(čitateľ, menovateľ)

• LOG(číslo; [základ])

1. Funkcia PRODUKT

Predpokladajme, že chcete vypočítať budúcu hodnotu počiatočnej investície 1 000 USD (bunka B1) pri ročnej úrokovej sadzbe 4 % (bunka B2) po piatich rokoch (bunka B3).

Pomocou funkcie PRODUKT vypočítajte budúcu hodnotu pomocou tohto vzorca:

=B1 * PRODUKT(1 + B2)^B3

Tento vzorec vráti budúcu hodnotu vašej investície.

2. Funkcie QUOTIENT a LOG

Ďalším krokom je vypočítať, koľko rokov bude trvať, kým sa investícia pri danej úrokovej sadzbe zdvojnásobí.

Použite funkciu QUOTIENT v kombinácii s funkciou LOG ​​na výpočet takto:

=QUOTIENT(LOG(2) / LOG(1 + B2), 1)

Tento vzorec vráti 17 so vzorovými údajmi, čo naznačuje, že bude trvať 17 rokov, kým sa hodnota investície zdvojnásobí pri ročnej úrokovej sadzbe 4 %.

Tento obrázok :

Funkcie pre investičné scenáre

Toto sú niektoré funkcie, ktoré sú vhodné pre konkrétne finančné scenáre. Jeden z nich môže byť presne to, čo potrebujete:

• NPV – Funkcia NPV (Čistá súčasná hodnota) vypočíta čistú súčasnú hodnotu investície na základe série budúcich peňažných tokov na základe diskontnej sadzby.

• ACCRINT – Funkcia ACCRINT vypočítava naakumulovaný úrok cenného papiera, z ktorého sa pravidelne úročí. Je to užitočné pri určovaní úrokov z cenného papiera od dátumu poslednej platby do daného dňa vyrovnania.

• INTRATE – Funkcia INTRATE vypočíta úrokovú sadzbu pre plne investovaný cenný papier.

• PMT – Funkcia PMT vypočíta celkovú splátku dlhového cenného papiera.

• IRR – Funkcia IRR poskytuje vnútornú mieru návratnosti.

• YIELD – funkcia YIELD poskytuje výnos cenného papiera na základe úrokovej sadzby, nominálnej hodnoty a splatnosti.

Stredne pokročilé vzorce dátumu a času v Exceli

Medzi základné funkcie dátumu a času v Exceli patria funkcie TERAZ a DNES pre aktuálny dátum. Stredne pokročilí používatelia by tiež mali vedieť, ako extrahovať komponenty z daného dátumu pomocou:

• DAY(dátum)

• MESIAC(dátum)

• YEAR(dátum)

Vzorec =MONTH(“23. apríl 2023″) vráti výsledok 4 pre štvrtý mesiac. Podobne funkcie DEŇ a ROK vrátia hodnoty 23 a 2023.

1. Medzimesačné funkcie

Stredne pokročilí používatelia sa niekedy budú zaoberať pridávaním alebo hľadaním konca mesiaca.

• EDATE(dátum_začiatku, počet_mesiacov)

• EOMONTH(dátum_začiatku; počet_mesiacov)

Napríklad vzorec =EDATE(“23. apríl 2023”, 2) vypočíta dátum o dva mesiace neskôr.

Vzorec =EOMONTH(23 April 2023”, 2) vypočíta koniec mesiaca o dva mesiace neskôr.

Výsledkom je „30. jún 2023“, ktorý zohľadňuje, že v júni je iba tridsať dní. Ak zadáte 3 mesiace, výsledok bude „31. júl 2023“.

2. Funkcie stredného týždňa

Na spočítanie počtu celých pracovných dní v rámci rozsahu dátumov môžete použiť kombináciu funkcií SUM a WEEKDAY.

Funkcia WEEKDAY vráti deň v týždni pre daný dátum. Druhý parameter určuje systém číslovania s predvoleným nastavením od nedele do soboty.

Ak máte sériu dátumov v rozsahu buniek B1:B40, použite tento vzorec na výpočet pracovných dní:

=SUM(–(WEEKDAY(B1:B40;2)>5))

3. Medzičasové funkcie

Funkcia YEARFRAC vypočítava zlomok roka medzi dvoma dátumami.

Funkcia TIMEVALUE sa používa na prevod času vyjadreného ako text na desatinné číslo predstavujúce podiel 24-hodinového dňa.

Výsledkom je sériové číslo programu Excel, kde 1 predstavuje celý 24-hodinový deň, 0,5 predstavuje 12 hodín, 0,25 predstavuje 6 hodín atď.

Predpokladajme, že máte v bunke A1 čas v textovom formáte „17:45“ a chcete ho previesť na desatinné číslo. Toto je vzorec:

=TIMEVALUE(A1)

Hodnota 0,74 predstavuje podiel 24-hodinového dňa, ktorý uplynul v čase pätnásť minút pred šiestou hodinou.

Pre zmenu časovej bunky z formátu času na textový formát. Možno to budete chcieť naformátovať ako percento, aby to bolo jasnejšie.

Excel funkcie pre podmienenú a logickú algebru

Podmienené a logické funkcie sú nevyhnutné pre rozhodovanie v Exceli. Nasledujúce funkcie je možné kombinovať so základnejšími vzorcami Excelu pre výkonnú logiku:

1. IF(podmienka, hodnota_ak_pravda, hodnota_ak_nepravda):

2. AND(podmienka1, podmienka2, …)

3. ALEBO(podmienka1, podmienka2,…)

4. NIE (podmienka 1, podmienka 2, …)

Funkcia IF vyhodnotí podmienku a vráti rôzne hodnoty v závislosti od toho, či je podmienka pravdivá alebo nepravdivá.

Funkcie AND, OR a NOT kontrolujú, ktoré podmienky sú pravdivé, a umožňujú vám podľa toho sa rozhodovať.

Tu je niekoľko príkladov založených na údajoch o predaji, ktoré sme použili predtým.

1. Funkcia IF

Predpokladajme, že chcete zobraziť text „Nízky predaj“, ak je predaj položiek nižší ako 15. Ak je predaj vyšší, chcete v novom stĺpci zobraziť text „Vysoký predaj“.

Použite tento vzorec a skopírujte ho do ďalších riadkov:

=IF(C2<15, “Nízky predaj”, “Vysoký predaj”)

2. Funkcia AND

Predpokladajme, že chcete zobraziť PRAVDA alebo NEPRAVDA pre tričká s aspoň 15 predajmi. Použite tento vzorec:

=AND(A3=”Tričko”, C3>=15)

3. Funkcia ALEBO

Predpokladajme, že chcete otestovať viacero podmienok a vrátiť hodnotu TRUE, ak je niektorá z podmienok splnená, a FALSE v opačnom prípade, potom by ste použili funkciu OR.

Napríklad, ak máme skóre testu v bunkách A1 a B1 a chceme vedieť, či je niektoré skóre vyššie ako 80, mohli by sme použiť: =OR( A1>80, B1>80) . Toto vráti hodnotu TRUE, ak je jedno zo skóre (alebo obe) vyššie ako 80, a FALSE, ak sú obe hodnoty 80 alebo menej.

4. NIE Funkcia

Predpokladajme, že chcete skontrolovať, či položka nie je mikina s kapucňou. Použite tento vzorec:

=NOT(A2=”Mikina s kapucňou”)

Odkazujúce bunky

Začiatočník sa rýchlo zoznámi s používaním absolútnych a relatívnych odkazov, ako sú $A$1 alebo B2. Stredne pokročilí používatelia by sa mali oboznámiť s používaním nepriamych odkazov, indexov a posunov:

1. NEPRIAME(ref_text)

2. INDEX(rozsah; číslo_riadka; číslo_stĺpca)

3. OFFSET(referenčná_bunka, riadky, stĺpce):

1. Funkcia NEPRIAME

Funkcia NEPRIAME vráti hodnotu zadaného odkazu na bunku zadanú ako text. Ak chcete odkazovať na bunku B3, použite tento vzorec:

=PRIAME(“B3”)

Výhodou oproti používaniu jednoduchých odkazov na bunky je, že táto funkcia vám poskytuje dynamický odkaz. To znamená, že Excel automaticky aktualizuje odkaz, keď sa zmení štruktúra tabuľky, napr. vymaže sa riadok.

2. Funkcia INDEX

Funkcia INDEX sa používa na odkazovanie na bunky v zadanom rozsahu na základe čísel riadkov a stĺpcov.

Ak chcete odkazovať na bunku B3 v tabuľke so šiestimi riadkami, použite tento vzorec:

=INDEX(B1:B6; 3)

3. Funkcia OFFSET

Funkcia OFFSET vráti bunku alebo rozsah, ktorý je o zadaný počet riadkov a stĺpcov vzdialený od referenčnej bunky.

Ak chcete zobraziť hodnotu bunky, ktorá má dve bunky naprieč a jednu bunku nižšie od A1, použite tento vzorec:

=OFFSET(A1; 2; 1)

Tu sú vzorce v akcii:

Stredné textové vzorce

Začiatočníci by mali poznať textové funkcie ako LEFT, ktorá extrahuje jeden alebo viac znakov z ľavej strany reťazca. Skúsený používateľ by mal byť oboznámený s funkciami, ako sú tieto:

1. TEXTJOIN(oddeľovač, ignorovať_prázdny, text1, [text2, …])

2. REPLACE(starý_text, počiatočné_číslo, počet_znakov, nový_text)

3. SUBSTITUTE(text; starý_text; nový_text; [číslo_inštancie])

1. Funkcia TEXTJOIN

Funkcia TEXTJOIN spája bunky s oddeľovačom, ktorý zadáte. Môžete tiež určiť, kde sa majú ignorovať prázdne bunky.

Ak chcete vytvoriť zoznam niektorých položiek v stĺpci A oddelených čiarkami, použite tento vzorec:

=TEXTJOIN(“, “, TRUE, A2:A4)

2. Funkcia REPLACE

Funkcia nahradiť vám umožňuje zadať počiatočnú pozíciu a dĺžku reťazca, ktorý chcete zameniť za cieľ.

Ak chcete nahradiť prvé dva znaky reťazca znakom „XX“, použite tento vzorec:

=TEXTJOIN(A2; 1; 2; “XX”)

3. Funkcia SUBSTITUTE

Náhradná funkcia nahradí zadané výskyty textového reťazca v rámci iného textového reťazca novým textovým reťazcom.

Ak chcete nahradiť výskyty slova „Džínsy“ výrazom „Legíny“, použite tento vzorec:

=SUBSTITUTE(A3; “Džínsy”, “Legíny”)

Tu sú vzorce v akcii:

Vzorce stredného vyhľadávania

Bežné funkcie vyhľadávania ako VLOOKUP a MATCH sú zahrnuté v cheat sheet pre začiatočníkov.

Stredne pokročilí používatelia by sa mali oboznámiť s funkciou vyhľadávania VYBRAŤ:

• VYBRAŤ(index_číslo; hodnota1; [hodnota2; …])

Funkcia CHOOSE vráti hodnotu zo zoznamu hodnôt na základe zadaného čísla indexu.

Predpokladajme, že máte stĺpec veľkostí tričiek označený ako 1, 2 alebo 3. V rovnakom riadku chcete zobraziť kategóriu malých, stredných a veľkých. Použite tento vzorec:

=VYBERTE(A1; “Malý”, “Stredný”, “Veľký”)

Vzorce na spracovanie chýb programu Microsoft Excel

Cheatsheet pre začiatočníkov uvádzal typické chybové hlásenia, ktoré uvidíte pri práci s Excelom. Stredne pokročilí používatelia by mali byť schopní používať vzorce na elegantné spracovanie chýb.

1. IFERROR(hodnota, ak_chyba)

2. IFNA(hodnota; hodnota_ak_na)

1. Funkcia IFERROR

Chyby delenia nulou sú bežné, ale predvolené zobrazenie Excelu je #DIV/0! nebude jasné každému používateľovi.

Pomocou funkcie IFERROR nahraďte štandardnú chybu za „Nedá sa deliť nulou“ týmto vzorcom:

=IFERROR(A1/B1; “Nedá sa deliť nulou”)

Tento obrázok ukazuje nespracovanú chybu v treťom riadku a funkciu IFERROR obsluhujúcu štvrtý riadok.

2. Funkcia IFNA

Funkcia IFNA v Exceli sa používa na zachytenie a spracovanie chýb #N/A.

Ďalšia časť tohto cheat sheetu zobrazuje funkciu CHOOSE. Táto funkcia vráti N/A chybu, ak indexové číslo nie je celé číslo.

Predpokladajme, že veľkosť trička bola zadaná ako 1,5, pričom používateľ si mylne myslel, že sa zobrazí kategória niekde medzi malým a stredným. Namiesto toho sa zobrazí chyba #N/A .

Ak chcete poskytnúť užitočnejšiu chybu, použite tento vzorec:

=IFNA(VYBRAŤ(A2; “Nízka”, “Stredná”, “Vysoká”); “Neplatný index”)

Skratky vzorcov a funkcií

Existuje niekoľko klávesových skratiek, ktoré vám môžu zefektívniť prácu s pracovným hárkom.

• F2 : Upravte aktívnu bunku a umiestnite kurzor na koniec obsahu bunky.

• F9 : Výpočet a zobrazenie výsledku vybranej časti vzorca.

• Ctrl + Shift + Enter : Zadajte vzorec poľa.

• Shift + F3 : Otvorí dialógové okno Vložiť funkciu.

• Esc : Zrušenie zadania vzorca a návrat k pôvodnému obsahu bunky.

Niektoré z týchto skratiek programu Excel nemusia byť dostupné vo všetkých jazykoch alebo rozložení klávesnice.

Stredné formátovanie buniek

Vo finančnej analýze je kľúčové správne formátovanie buniek obsahujúcich čísla, dátumy a meny. Ak chcete formátovať bunky, postupujte takto:

1. Vyberte bunky, ktoré chcete formátovať.

2. Kliknite pravým tlačidlom myši na vybrané bunky a vyberte „Formátovať bunky“.

3. Vyberte príslušnú kategóriu (napr. Číslo, Mena, Dátum) a použite požadovaný formát.

Niektoré bežné typy formátovania, ktoré je potrebné zvážiť vo financiách, zahŕňajú:

• Mena : 1 234,56 USD

• Percento : 12,34 %

• Účtovníctvo : (1 234,00 USD)

• Dátum : 10. mája 2023

Záverečné myšlienky

Keď sa budete lepšie orientovať v rôznych vzorcoch a funkciách zvýraznených v tomto , zistíte, že budete lepšie vybavení na zvládanie čoraz zložitejších úloh a projektov.

Prax je kľúčom k zvládnutiu akejkoľvek zručnosti, takže sa nebojte experimentovať s týmito vzorcami vo svojej každodennej práci. Prinesú vašu odbornosť od úrovne začiatočníkov až po stredne pokročilú úroveň.

Toto je len ochutnávka toho, čo môžu stredné funkcie Excelu urobiť, aby vám zjednodušili prácu, zefektívnili analýzy a pomohli vám odhaliť prehľady z vašich údajov.

Skutočné kúzlo sa stane, keď začnete kombinovať funkcie v našom cheat sheete a prispôsobíte ich tak, aby vyhovovali vašim jedinečným potrebám. Takže sa tu nezastavujte. Pokračujte v skúmaní, neustále experimentujte a zistíte, že Excel nie je len nástroj, ale mení hru!

Máte pocit, že chcete posunúť veci na ďalšiu úroveň? Skontrolujte naše