Rozšírené vzorce programu Excel (obsah)
- Úvod do pokročilých vzorcov v Exceli
- Príklady pokročilého vzorca v Exceli
Úvod do pokročilých vzorcov v Exceli
Akonáhle dosiahnete strednú úroveň v Exceli, musíte tvrdo pracovať, aby ste postúpili na vyššiu úroveň. Aby ste sa dostali na pokročilú úroveň, musíte si byť vedomí niektorých často používaných pokročilých vzorcov. V tomto článku sa budem venovať 10 najdôležitejším formám, ktoré musia všetci vynikajúci študenti vedieť. Ak sa chcete dozvedieť a preskúmať, postupujte podľa tohto článku.
Príklady pokročilého vzorca v Exceli
Poďme pochopiť, ako používať pokročilé vzorce v Exceli s niekoľkými príkladmi.
Túto šablónu rozšírených vzorcov Excel si môžete stiahnuť tu - šablónu rozšírených vzorcov ExcelPríklad č. 1 - Čiastočná funkcia VLOOKUP
Museli ste sa stretnúť so situáciou, keď VLOOKUP vyvolá chybu, aj keď vo vyhľadávacej hodnote došlo k malému počtu chýb. Napríklad, ak hľadáte plat mena Abhishek Sinha a ak máte iba Abhishek, potom VLOOKUP nemôže načítať údaje.
Použitím hviezdičky na oboch koncoch hodnoty vyhľadávania však môžeme údaje načítať pre hodnotu čiastočného vyhľadávania.
V tabuľke 1 mám celé meno a plat, ale v tabuľke 2 mám iba čiastočné mená a musím nájsť plat každého zamestnanca.
Krok 1: Otvorte vzorec VLOOKUP v bunke E3. Pred výberom hodnoty vyhľadávania umiestnite hviezdičku (*) na obe strany hodnoty vyhľadávania.
Krok 2: Ako obvykle, môžete vyplniť vzorec VLOOKUP teraz a my budeme mať výsledky.
Po použití vyššie uvedeného vzorca je výstup uvedený nižšie.
Rovnaký vzorec sa používa v iných bunkách.
Príklad č. 2 - COUNTIFS so symbolmi operátora
Na počítanie vecí v zozname musíte použiť funkciu COUNTIF a IFS. Môžeme tiež počítať na základe symbolov operátora, ako sú väčšie ako (>) menšie ako (<) a znamienko rovnosti (=).
Teraz sa pozrite napríklad na nižšie uvedené údaje. Ak chcete spočítať celkový počet faktúr za región JUH po dátume 10. januára 2018, ako sa počíta?
Príklad č. 3 - Stav IF s podmienkami AND & OR
Logické funkcie sú súčasťou našich každodenných aktivít. Musíte ich zvládnuť, aby ste prešli na ďalšiu úroveň. Ak vypočítavate bonus na základe viacerých podmienok, musíte na splnenie úlohy vnoriť podmienky AND alebo OR s podmienkou IF.
Predpokladajme, že musíte vypočítať bonusovú sumu pre každé oddelenie na základe oddelenia a rokov služby, ktoré potrebujete. Na základe nižšie uvedených kritérií musíme vypočítať bonus.
Ak je služba nad 4 roky a oddelenie je buď bonusom za predaj alebo podporu, je 50000 alebo inak bonus je 25 000.
Použite nasledujúci vzorec a získajte bonusovú sumu.
Po použití vyššie uvedeného vzorca výstup uvedený nižšie.
Rovnaký vzorec platí v bunke E3 až E9.
Príklad č. 4 - TEXT Funkcia, vďaka ktorej budete smerovať dynamicky
Povedzme, že udržiavate dennú tabuľku predaja a tabuľku musíte každý deň aktualizovať. Na začiatku tabuľky máte jednu záhlavie s názvom „Konsolidované údaje o predaji z DD-MM-RRRR na DD-MM-RRRR“. Keď sa tabuľka aktualizuje, musíte zmeniť dátum nadpisu. Nie je to frustrujúce robiť to isté znova a znova? Túto hlavičku môžeme dynamizovať pomocou funkcií TETX, MIN a Max spolu so zreťazeným symbolom operátora ampersand (&).
Príklad č. 5 - INDEX + MATCH + MAX na nájdenie najvyššej predajnej osoby
Máte zoznam predajcov a predajov uskutočnených na základe ich mena. Ako zistíte, kto je najlepším alebo najvyšším predajcom v zozname? Samozrejme, máme niekoľko ďalších techník na vyjadrenie výsledku, ale táto funkcia môže vrátiť najvyšší predajca zo zásielky.
Použite funkciu nižšie a získajte najvyššie meno predajcu.
Po použití vyššie uvedeného vzorca výstup uvedený nižšie.
Príklad č. 6 - Získajte počet jedinečných hodnôt zo zoznamu
Ak máte veľa duplicitných hodnôt a ste povinní povedať, koľko jedinečných hodnôt existuje, ako to poviete? Môžete to zistiť odstránením duplicitnej hodnoty, ale nejde o dynamický spôsob, ako zistiť počet jedinečných hodnôt.
Pomocou tejto funkcie poľa môžeme zo šarže zistiť jedinečné hodnoty.
Ak chcete získať zoznam jedinečných hodnôt, použite nasledujúci vzorec.
Príklad č. 7 - Pomocou dynamického rozbaľovacieho zoznamu použite pomenovaný rozsah
Ak často pracujete s rozbaľovacím zoznamom a aktualizujete rozbaľovací zoznam, musíte sa vrátiť do rozsahu zdrojového zoznamu, aby ste odstránili alebo pridali hodnoty. Potom sa musíte vrátiť do rozbaľovacích buniek a znova aktualizovať rozsah rozbaľovacieho zoznamu.
Ak však pre svoj rozbaľovací zoznam môžete vytvoriť pomenovaný rozsah, môžete rozbaľovací zoznam urobiť dynamickým a aktuálnym.
Vytvorte rozsah mien tak, ako je to znázornené na obrázku nižšie.
Teraz prejdite na rozbaľovaciu bunku a otvorte rozbaľovacie dialógové okno.
V zdroji stlačte kláves F3, aby sa zobrazili všetky definované názvy, vyberte názov vášho rozbaľovacieho zoznamu.
Ok, rozbaľovací zoznam je pripravený a automaticky aktualizuje hodnoty, keď dôjde k zmene rozsahu rozbaľovacieho zoznamu.
Príklad č. 8 - Zbavte sa chybových hodnôt pomocou funkcie IFERROR
Som si istý, že ste pri práci s výpočtami divízií VLOOKUP zaznamenali chybové hodnoty. Zaobchádzanie s týmito chybovými hodnotami je únavná úloha. Tieto chybové hodnoty sa však môžeme zbaviť použitím funkcie IFERROR vo vzorci.
Po použití vyššie uvedeného vzorca výstup uvedený nižšie.
Rovnaký vzorec používaný v iných bunkách.
Príklad č. 9 - Na vytvorenie vlastného grafu EMI použite funkciu PMT
V súčasnosti nie je možnosť EMI pre nás všetkých čudná. Vo vynikajúcej miere dokážeme odhadnúť náš vlastný EMI graf pomocou funkcie PMT. Postupujte podľa krokov nižšie a vytvorte si svoj vlastný graf.
Na získanie množstva EMI použite vzorec v bunke B4.
Príklad č. 10 - INDEX + MATCH ako alternatíva k funkcii VLOOKUP
Dúfam, že viete o obmedzení funkcie VLOOKUP. Jedným z hlavných obmedzení je VLOOKUP, ktorý dokáže načítať údaje zľava doprava, nie sprava doľava. Nie vždy sú údaje dobre usporiadané a pripravené na použitie. Často hľadaný stĺpec hodnôt je na ľavej strane vyhľadávacej hodnoty, takže VLOOKUP v týchto prípadoch nepomáha.
Kombinácia funkcie INDEX + MATCH slúži ako alternatíva k funkcii VLOOKUP.
Po použití vyššie uvedeného vzorca je výstup uvedený nižšie.
Rovnaký vzorec sa používa v iných bunkách.
Na základe ID produktu musíme extrahovať predajné hodnoty. V hlavnej tabuľke je ID produktu na pravej strane stĺpca predaja. VLOOKUP teda nemôže načítať údaje. Na získanie údajov použite nasledujúci vzorec.
Odporúčané články
Toto je príručka pre pokročilé vzorce v Exceli. Tu diskutujeme o tom, ako používať rozšírené vzorce v Exceli spolu s praktickými príkladmi a šablónou Excel na stiahnutie. Môžete si tiež prečítať naše ďalšie navrhované články -
- Ako používať funkciu Excel INDEX?
- Rozšírené Excel Funkcia databázy
- Príklady vzorcov TRIM v Exceli
- Sprievodca programom Excel OFFSET