Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak vypočítat „velký if“ nebo „malý if“, přičemž získá n-tu největší (nebo nejmenší) hodnotu na základě kritérií.
VELKÉ & MALÉ funkce
Funkce LARGE se používá k výpočtu n -té největší hodnoty (k) v poli, zatímco funkce SMALL vrací nejmenší n -tu hodnotu.
K vytvoření „velkého if“ použijeme ve vzorci pole funkci LARGE spolu s funkcí IF.
VELKÝ IF
Kombinací LARGE (nebo SMALL) a IF ve vzorci pole můžeme v podstatě vytvořit funkci „LARGE IF“, která funguje podobně jako funguje vestavěný vzorec SUMIF. Pojďme se podívat na příklad.
Máme seznam známek dosažených studenty ve dvou různých předmětech:
Předpokládáme, že jsme požádáni, abychom našli tři nejlepší známky dosažené pro každý předmět takto:
Abychom toho dosáhli, můžeme vnořit funkci IF pomocí předmět jako naše kritéria uvnitř funkce LARGE takto:
= VELKÉ (IF (=,),)
= VELKÉ (IF ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
Pokud používáte Excel 2022 a starší, musíte vzorec zadat stisknutím CTRL + SHIFT + ENTER dostat složené závorky kolem vzorce.
Jak funguje vzorec?
Vzorec funguje tak, že vyhodnotí každou buňku v našem rozsahu kritérií jako PRAVDU nebo NEPRAVDU.
Nalezení hodnoty nejvyššího stupně (k = 1) v matematice:
= VELKÉ (IF ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
= VELKÉ (IF ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {0,81; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 1)
Dále funkce IF nahradí každou hodnotu FALSE, pokud není splněna její podmínka.
= VELKÉ ({0,81; FALSE; FALSE; 0,42; FALSE; 0,63; FALSE; 0,58; FALSE}, 1)
Nyní funkce LARGE přeskočí FALSE hodnoty a vypočítá největší (k = 1) ze zbývajících hodnot (0,81 je největší hodnota mezi 0,42 a 0,81).
MALÉ, KDYŽ
Stejnou techniku lze místo toho použít také s funkcí SMALL.
= MALÝ (IF ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
VELKÝ IF s více kritérii
Chcete-li použít LARGE IF s více kritérii (podobně jako funguje vestavěný vzorec SUMIFS), jednoduše vnořte více funkcí IF do funkce LARGE takto:
= VELKÉ (IF (=, IF (=,)),)
= VELKÝ (IF ($ D $ 2: $ D $ 18 = $ H3, IF ($ B $ 2: $ B $ 18 = $ G3, $ E $ 2: $ E $ 18)), I $ 2)
Dalším způsobem, jak zahrnout více kritérií, je znásobit kritéria dohromady, jak je uvedeno v tomto článku
Tipy a triky:
- Pokud je to možné, vždy odkazujte na polohu (k) z pomocné buňky a referenční hodnoty zámku (F4), protože to usnadní automatické vyplňování vzorců.
- Pokud používáte Excel 2022 nebo novější, můžete vzorec zadat bez Ctrl + Shift + Enter.
- Chcete -li získat jména studentů, kteří dosáhli nejlepších známek, zkombinujte to s INDEX MATCH
