Komplexné vzorce v Exceli (obsah)
- Úvod do zložitých vzorcov v Exceli
- Vyberte funkciu slúžiť ako funkcia vyhľadávania
- VLOOKUP S ZVOLIŤ
- IF s funkciou AND & OR
Úvod do zložitých vzorcov v Exceli
Excel prichádza s mnohými užitočnými vzorcami, máme kategóriu Finančné, Logické, Textové, Dátum a čas, Vyhľadávanie a Odkazy a Matematika a Trig ”. Každý individuálny vzorec je dosť dobrý pre stredne pokročilých používateľov, ktorí čelia priamym situáciám, ale keď postupujete na ďalšiu úroveň, vaša zručnosť musí zdvihnúť výšku, takže pri riešení zložitých situácií hrá dôležitú úlohu použitie zložitejších vzorcov vo vynikajúcej podobe. Ak vezmeme do úvahy tento článok, prevedieme vás niektorými dôležitými a veľmi potrebnými komplexnými vzorcami. Pokračuj v čítaní.
Vyberte funkciu slúžiť ako funkcia vyhľadávania
Nie veľa z vás používalo funkciu VYBERTE, ale je veľmi užitočné slúžiť aj ako funkcia vyhľadávania.
Túto šablónu zložitých vzorcov Excel si môžete stiahnuť tu - zložité vzorce vzorcov Excel Napríklad pozrite sa na nižšie uvedené údaje.
V rámci tejto tabuľky máme mesačne údaje o zákazníkoch, máme každú zákaznícku tabuľku. V tejto tabuľke som v bunke B13 vytvoril rozbaľovací zoznam mesiaca, takže keď sa táto rozbaľovacia ponuka zmení, mali by sme získať súčet vybraného mesiaca.
Vytvorte jednu tabuľku, ako je uvedené nižšie.
Aplikujte VLOOKUP na bunku D13.
Teraz v bunke B14 otvorte funkciu VYBERTE.
Vyberte číslo INDEX ako bunku VLOOKUP a urobte z neho absolútny odkaz.
Teraz vyberte pre každý argument predajné čísla každý mesiac.
Zatvorte konzolu a stlačte kláves Enter.
Teraz skopírujte a prilepte vzorec aj pod bunky.
Teraz vzorec ZVOLIŤ bude zobrazovať čísla vybraného mesiaca.
VLOOKUP S VOĽKOU funkciou
Videli sme tradíciu VLOOKUP, ale môžeme ju kombinovať aj s funkciou ZVOLIŤ. Napríklad pozri nižšie uvedené údaje.
V bunkách A1 až B11 máme predajnú tabuľku a v bunke D2 som vytvoril rozbaľovací zoznam mesiacov av bunke E2 potrebujeme použiť VLOOKUP a získame vybranú mesačnú predajnú hodnotu. Problém s aplikáciou VLOOKUP je ale dátová štruktúra, kde hodnota vyhľadávania je napravo od stĺpca s výsledkami, takže VLOOKUP nemôže načítať údaje sprava doľava, v tomto prípade môžeme použiť funkciu ZVOLIŤ.
Aby sme mohli vybrať pole tabuľky, musíme tu otvoriť funkciu ZVOLIŤ.
V argumente INDEX Number v kvete zátvorky zadajte 1 a 2. Teraz vyberte prvý rozsah ako stĺpec Month.
Ako druhý rozsah vyberte stĺpec Predaj.
Uveďte indexové číslo stĺpca ako 2 a vyhľadávanie rozsahu ako 0.
Takže sme dostali výsledok za mesiac Apr z tabuľky. Takže vždy, keď nemáte presnú štruktúru údajov, ktorú si môžeme vybrať, VYBERTE funkciu.
IF s funkciou AND & OR
Logické funkcie sú plné logiky pre výpočet veci. Pozrite sa na nižšie uvedené príklady.
Z vyššie uvedenej tabuľky musíme vypočítať bonus na základe nižšie uvedených podmienok.
- Ak je služba dlhšia ako 1, 5 roka a oddelenie je buď predaj alebo podpora, potom bonus je 1 000.
- Ak niečo iné, potom bonus je 3000.
Na výpočet bonusu musíme použiť IF, AND, & OR spolu. Podmienky otvoreného IF v bunke D2.
Potrebujeme otestovať viaceré podmienky, otvorené a funkčné.
Teraz je ďalšou logikou buď predaj alebo podpora pre tento otvorený ALEBO stav.
Otestujte, či je nadpisom Predaj alebo podpora.
Ak sú teda všetky dodané podmienky PRAVÉ, potom bonus by mal byť 10 000.
Ak sú dodané logické testy FALSE, potom bonus je 3000.
Zatvorte zátvorku a stlačte kláves Enter, aby sme dostali výsledok.
Skopírujte a prilepte vzorec do buniek nižšie.
Takže podľa stavu sme dostali výsledok. Ak je služba> 1, 5 roka a názov je buď Predaj alebo podpora, dostaneme 10 000, inak získame 3 000.
Komplexný vzorec s zástupnými znakmi
VLOOKUP vyžaduje, aby sa pri načítaní údajov zistila presná hodnota vyhľadávania. Toto je tradičný slogan, ale údaje môžeme získať pomocou hodnoty vyhľadávania častíc. Napríklad, ak je vyhľadávaná hodnota „VIVO“ a v hlavnej tabuľke, ak ide o „VIVO Mobile“, môžeme sa stále zhodovať pomocou zástupných znakov. Teraz uvidíme jeden z príkladov, nižšie sú príklady údajov.
V stĺpci A máme vyhľadávaciu tabuľku. V stĺpci C máme vyhľadávacie hodnoty, tieto vyhľadávacie hodnoty nie sú úplne rovnaké ako hodnoty vyhľadávacej tabuľky. Uvidíme, ako aplikovať VLOOKUP pomocou zástupných kariet.
Najprv otvorte funkciu VLOOKUP v bunke D1.
Prvým argumentom je hodnota vyhľadávania . Jedným z problémov s hodnotou vyhľadávania je, že nemáme presnú zhodu, takže túto hodnotu vyhľadávania musíme uzavrieť hviezdičkou pred a za hodnotou vyhľadávania.
Tu sme použili dva hviezdičkové znaky „*“ a C2 & „*“. Hviezdička označuje, že medzi zástupnými znakmi by malo byť niečo zhodné a vrátiť príslušný výsledok.
Aj keď sme mali len znak hviezdičky „Infosys“, zhodoval sa s hodnotou v vyhľadávacej tabuľke a presný výsledok sme vrátili ako „Infosys Ltd“.
Podobne v bunke D6 sme dostali chybovú hodnotu ako # HODNOTA! pretože vo vyhľadávacej tabuľke nie je slovo „Mintra“.
Odporúčané články
Toto je príručka pre zložité vzorce v Exceli. Tu diskutujeme niektoré dôležité a veľmi potrebné komplexné vzorce s príkladmi a vynikajúcou šablónou. Viac informácií nájdete aj v ďalších navrhovaných článkoch -
- SEARCH Formula v Exceli (tipy a triky)
- Výukové programy o funkcii Excel NORMSINV
- Menovka a jej použitie v Exceli
- Zreťazené reťazce v Exceli