Při dalším používání Microsoft Excel sledovat popř Rozpočtování Nebo inventář, pravděpodobně se dozvíte hodně o dostupných funkcích a jejich vzorcích. Ale objevování něčeho nového může být matoucí nebo zastrašující. Tato příručka představuje několik užitečných funkcí a jejich vzorce, když jste připraveni ponořit se do nového území. Zde je 5 užitečných pokročilých vzorců Excelu, které byste měli znát.
1. Najděte hodnoty v tabulce: XLOOKUP
Pokud chcete hledat data na základě jiných dat ve velké tabulce, XLOOKUP je vaše běžná funkce. jiný než VLOOKUP , který umí vyhledávat pouze hodnoty zleva doprava, XLOOKUP umí vyhledávat hodnoty zleva doprava nebo zprava doleva, což z něj dělá flexibilnější nástroj.
Poznámka: V době psaní tohoto článku je XLOOKUP k dispozici pouze pro předplatitele Microsoft 365. Pro ostatní verze Excelu se podívejte na kombinaci INDEX a MATCH v další části.
Vzorec funkce je:
XLOOKUP(hodnota_hledání, rozsah_hledání, rozsah_návratu, nenalezeno, režim shody, režim_hledání)
Jsou vyžadovány pouze první tři argumenty. Níže je uveden popis každého argumentu:
- Vyhledávací_hodnota: Hodnota, kterou chcete vyhledat.
- rozsah_hledání: Rozsah obsahující hodnotu, kterou chcete vyhledat.
- Návratový_rozsah: Rozsah obsahující hodnotu, která má být vrácena.
- Nenalezeno: Text, který se má vrátit, pokud nebude hodnota nalezena. „#N/A“ je výchozí, pokud je vynecháno.
- match_mode: Typ shody používající „0“ pro přesnou shodu a „#N/A“ pro nenalezeno (výchozí, pokud je vynecháno), „1“ pro přesnou shodu a další menší prvek, pokud nebyl nalezen žádný, „-1“ pro přesnou shodu a větší prvek Další, pokud nebyl nalezen, nebo '2' pro shodu se zástupným znakem pomocí otazníku, hvězdičky nebo vlnovky.
- Search_mode: Režim vyhledávání s „1“ pro začátek od prvního prvku (výchozí, pokud je vynechán), „-1“ pro začátek od posledního prvku, „2“, když je „rozsah vyhledávání“ ve vzestupném pořadí, nebo „-2“, když je „rozsah vyhledávání“ v sestupné pořadí.
Například hledáme prodeje pro segment 2, takže použijeme následující vzorec:
=XLOOKUP(2,A19:A24,D19:D24)
Rozdělení vzorce, 2 je "hodnota_hledání", A19:A24 je "rozsah_hledání" a D19:D24 je argument "rozsah_návratu".
Výsledek, 74000, je správná odpovídající hodnota pro sektor 2.
Pokud chcete něco dynamičtějšího, kde hledáte hodnotu v buňce místo konstanty a poté obdržíte aktualizovaný výsledek, kdykoli změníte hodnotu v této buňce, podívejte se na následující příklad:
Nahradíme „lookup_value“ 2 buňkou F19:
=XLOOKUP(F19,A19:A24,D19:D24)
Když do buňky F2 zadáme 19, dostaneme výsledek 74000, a pokud zadáme jinou hodnotu, například 5, automaticky získáme aktualizovaný výsledek, což je 61000.
Argument můžete zahrnout "nenalezeno" Pokud nebyla nalezena žádná shoda, nemusíte se dívat na chybovou zprávu. K argumentu přidáváme „nereaguji“.
=XLOOKUP(F19,A19:A24,D19:D24,"No dice")
Když do buňky F19 zadáte hodnotu, která nemá shodu, zobrazí se vaše zpráva namísto „#NA“.
2. Najděte v listu alternativní hodnotu: INDEX a MATCH
Přestože XLOOKUP poskytuje výkonný způsob, jak vyhledávat hodnoty, je aktuálně dostupný pouze pro předplatitele Microsoft 365 Excel. Pokud používáte jinou verzi Excelu, můžete použít INDEX a MATCH k provedení stejné věci.
Pomocí funkce INDEX je vrácena hodnota v buňce na základě pozice, kterou jste zadali do vzorce. Vzorec INDEX je:
INDEX(rozsah; číslo_řádku; číslo_sloupce)
Pomocí funkce MATCH se vrátí pozice hodnoty, kterou zadáte do vzorce. Vzorec MATCH je:
MATCH(hodnota, rozsah, typ_shody)
Chcete-li zkombinovat tyto dvě funkce a jejich vzorce, vložte vzorec MATCH do vzorce INDEX jako místo hledání („číslo_řádku“ a „číslo_sloupce“).
Pomocí stejných dat jako ve výše uvedeném příkladu XLOOKUP chceme vidět prodej segmentu, který zadáme do buňky F19. Vzorec je:
=INDEX(D19:D24,MATCH(F19,A19:A24))
Abychom tento vzorec rozebrali, začneme s INDEXem a jeho argumentem „rozsah“, což je D19:D24. Toto je rozsah, který obsahuje požadovaný výsledek.
Vzorec MATCH používá F19 jako argument "hodnota" a A19:A24 jako argument "rozsah" obsahující tuto hodnotu.
Když do buňky F4 zadáme 19, dostaneme výsledek 75000, což je správně.
Pokud do buňky F19 zadáme jiný sektor, například 6, vzorec se automaticky aktualizuje, aby poskytl správnou hodnotu 58000.
3. PŘIDEJTE NEBO POČÍTAJTE S KRITÉRII: SUMIF, SUMIFS, COUNTIF, COUNTIFS
Funkce SUM a COUNT v Excelu jsou navrženy pro sčítání čísel a počítání buněk, ale jsou omezeny na jednoduché matematické operace. Variace těchto funkcí vám umožňují sčítat nebo počítat pomocí kritérií. Můžete například sčítat pouze čísla větší než 12 nebo počítat buňky obsahující název Bill.
Pomocí SUMIF a COUNTIF můžete sčítat nebo počítat s jednou podmínkou a pomocí SUMIFS a COUNTIFS můžete sčítat nebo počítat s více podmínkami. Níže je uvedena syntaxe každého z nich:
SUMIF
SUMIF(rozsah; kritéria; rozsah_součtu)
COUNTIF
COUNTIF(rozsah; podmínka)
SUMIFS
SUMIFS(rozsah_součtu, rozsah_podmínek1, podmínka1, rozsah_stavy2, podmínka2,…)
COUNTIFS
COUNTIFS(rozsah_podmínek1; podmínka1;rozsah_stavy2; podmínka2;…)
Níže naleznete příklad každé funkce.
K sečtení pouze čísel v našem rozsahu B2 až B7 pro čísla větší než 12 použijeme funkci SUMIF a tento vzorec:
=SUMIF(B2:B7;">12")
Výsledkem je 31, protože vzorec přidal 16 a 15, jediná dvě čísla větší než 12 v sadě dat.
V následujícím příkladu počítáme buňky s hodnotami menšími než 12 ve stejném rozsahu, B2 až B7, pomocí následujícího vzorce:
=COUNTIF(B2:B7,"<12")
Výsledek tohoto vzorce je 4, protože to je počet buněk v rozsahu s hodnotami menšími než 12.
Vezměme to SUMIFS a použijme dvě podmínky. Chcete-li přidat čísla v buňkách B2 až B7 pouze pro ty, kteří se přihlásili k odběru našeho zpravodaje (Y) v buňkách C2 až C7 a jejichž městem je San Diego v buňkách D2 až D7, použijeme následující vzorec:
=SUMIFS(B2:B7,C2:C7,"Y",D2:D7,"San Diego")
Výsledek tohoto vzorce je 19, protože pouze dva zákazníci mají Y pro newsletter a San Diego pro město. Bill Brown má 11 a Sue Smithová 8, což je celkem 19.
Pro příklad pomocí funkce COUNTIFS spočítáme počet buněk se stejnými dvěma podmínkami popsanými výše. Počítáme počet zákazníků, kteří se přihlásili k odběru newsletteru (Y) a jejich město San Diego.
=COUNTIFS(C2:C7,"Y"D2:D7,"San Diego")
Jak se očekávalo, skóre bylo 2 pro Billa Browna a Sue Smith, jediné předplatné newsletteru v San Diegu.
4. Testovací data za různých podmínek: IFS
Funkce IF aplikace Excel je výkonný nástroj pro testování dat podle podmínek. Můžete jej například použít k zobrazení písmenného hodnocení pro studentovo číselné skóre nebo „ano“, pokud prodejce přinesl dostatečné příjmy, aby získal svůj bonus, a „ne“, pokud ne.
Problém s funkcí IF spočívá v tom, že chcete-li svá data otestovat podle více kritérií, musíte zkombinovat všechny příkazy IF dohromady. K nápravě tohoto zmatku ve skupině použijte funkci IFS.
Podobně jako výše uvedené SUMIFS a COUNTIFS vám IFS umožňuje přidat do vzorce více kritérií jasným a snadno čitelným způsobem.
Syntaxe je IFS(test1, if_test1_true, test2, if_test2_true, ...), kde můžete testovat až 127 různých benchmarků.
Pomocí jednoho z našich příkladů jsme začali jednoduše mluvit o odměnách. Pokud je množství v buňce B2 větší než 20000 XNUMX, zobrazte "Ano". Pokud ne, ukaž "Ne". Vzorec se stává:
=IFS(B2>20000,"Ano",B2<20000,"Ne")
V prvním testu, pokud je hodnota v B2 větší než 20000, je výsledek, pokud je pravda, „ano“. Pokud je ve druhém testu hodnota v B2 menší než 20000 XNUMX, je výsledek pravdivý "Ne".
Naštěstí náš prodejce dostává svou odměnu. Jelikož má tržby 21000 XNUMX, je zadaná „نعم“ v kolonce bonusů.
Zkopírujte vzorec, pokud máte seznam jako náš příklad. Přetáhněte úchyt výplně v pravém dolním rohu buňky do zbývajících buněk ostatních prodejců. Vzorec se automaticky aktualizuje, aby vyhovoval různým odkazům na buňky.
Pro další příklad od IFS máme seznam vedoucích služeb pro každý den v týdnu. Při zobrazení aktuálního dne v buňce D2 se v buňce E2 zobrazí jméno odpovídajícího manažera. Vzorec se stává:
=IFS(D2="Monday",B2,D2="Tuesday",B3,D2="Wednesday",B4,D2="Thursday",B5,D2="Friday",B6)
To říká, že pokud je hodnota v buňce D2 pondělí, zobrazí se název v buňce B2, pokud je hodnota v buňce D2 úterý, zobrazí se název v buňce B3 a pokud je hodnota v buňce D2 středa, zobrazí se název v buňce B3 a tak dále.
Při změně dne v týdnu v buňce D2 se v buňce E2 zobrazí odpovídající jméno vedoucího ve službě.
S malým množstvím práce při vytváření vzorce předem můžete sklízet plody určité automatizace v listu Excel.
5. Filtrujte pomocí více kritérií: filtr
Jako uživatel Excelu pravděpodobně víte, že aplikace přichází s vestavěnou funkcí filtru. Pokud však chcete filtrovat data pomocí podmínky nebo dokonce více kritérií, budete muset použít funkci filtrování. Tato flexibilní funkce a její argumenty vám mohou pomoci zúžit velké soubory dat během několika minut.
Syntaxe funkce je FILTER(rozsah, rozsah=kritéria, pokud_prázdný), kde jsou potřeba pouze první dva argumenty datové sady plus kritéria a rozsah jejího kontejneru. Třetí argument je užitečný, pokud chcete vrátit něco konkrétního, pokud vzorec nevrací nulový výsledek, např "Neexistují žádná data" .و "nic".
Před přidáním více kritérií je důležité vědět, jak funguje funkce FILTER s jednou podmínkou. Můžete například filtrovat data v buňkách A2 až C9 podle Harolda Hilla, které jsou v B2 až B9. Vzorec na to je:
=FILTER(A2:C9,B2:B9="Harold Hill")
Máme dva výsledky pro Harold Hill. snadné že?
Přidejme do vzorce další výrazy. Více kritérií se přidá pomocí hvězdičky (*) pro AND a znaménka plus (+) pro NEBO.
Například filtrujeme obojí Harold Hill و Elektronika pomocí následujícího vzorce:
=FILTER(A2:C9,(B2:B9="Harold Hill")*(A2:A9="Electronics"))
Chcete-li rozdělit vzorec, A2:C9 je soubor dat, B2: B9 = "Harold Hill" je první podmínka a hvězdička představuje AND , a A2: A9 = „Elektronika“ je druhá podmínka.
S naším filtrem jsme obdrželi jeden výsledek, kde vzorec musí odpovídat dvěma podmínkám, Harold Hill a Electronics.
V dalším příkladu můžete filtrovat podle oblečení nebo aut:
=FILTER(A2:C9,(A2:A9="Apparel")+(A2:A9="Automotive"))
Po dělení tímto vzorcem je A2:C9 soubor dat, A2: A9 = „Oblečení“ je první podmínka a znaménko plus představuje OR , a A2: A9 = „Automaticky“ je druhá podmínka.
Tentokrát jsme obdrželi dva výsledky, kde vzorec musí odpovídat jedné z podmínek - ne oběma.
Často kladené otázky
Q1: Jak najdu strukturu vzorce funkce přímo v Excelu?
Odpovědět: Excel poskytuje vestavěný nástroj pro nalezení funkcí, které potřebujete. S ním můžete vidět syntaxi syntaxe této funkce.
Vyberte prázdnou buňku, přejděte na kartu Vzorce a na levé straně pásu karet zvolte Vložit funkci. Zadejte úlohu do vyhledávacího pole nahoře a klikněte na Přejít. Když uvidíte požadovanou funkci, vyberte ji, abyste viděli její popis a syntaxi v dolní části pole.
Q2: Jak mohu ladit vzorec, který nefunguje správně?
Odpovědět: Vyberte vzorec, který právě používáte, a přejděte na kartu Vzorce. V části Auditování formátu na pásu karet zvolte "Vyhodnocení formátu".
V okně Vyhodnotit vzorec uvidíte svůj vzorec s podtrženou částí. Kliknutím na "Vyhodnotit" zobrazíte výpočet podtržené části. Pokračujte v tomto procesu a přejděte každou částí vzorce k výsledku. To vám umožní vidět, jak je vzorec zpracován a kde může být chyba.
Q3: Jak zjistím, proč se mi zobrazuje chyba vzorce?
Odpovědět: Když uvidíte chybu ve vzorci, který jste vytvořili, můžete udělat dvě věci, abyste získali další informace. Nejprve klikněte na tlačítko chyby, které se zobrazí vedle buňky, a poté vyberte možnost Nápověda k této chybě, abyste získali další podrobnosti od společnosti Microsoft v zobrazeném postranním panelu.
Případně vyberte Zobrazit kroky výpočtu a otevřete okno vyhodnocení vzorce zobrazené výše. Uvidíte výsledek výpočtu a postup, jak se tam dostat, což by mělo pomoci určit příčinu chyby.
