Stiahnite si ukážkový zošit
Tento tutoriál predvedie, ako vypočítať „veľký if“ alebo „malý if“, pričom získa n-tu najväčšiu (alebo najmenšiu) hodnotu na základe kritérií.
VEĽKÉ A MALÉ funkcie
Funkcia LARGE sa používa na výpočet n -tej najväčšej hodnoty (k) v poli, zatiaľ čo funkcia SMALL vracia najmenšiu n -tú hodnotu.
Na vytvorenie „veľkého if“ použijeme vo vzorci poľa funkciu LARGE spolu s funkciou IF.
VEĽKÉ, AK
Kombináciou LARGE (alebo SMALL) a IF vo vzorci poľa môžeme v podstate vytvoriť funkciu „LARGE IF“, ktorá funguje podobne ako vstavaný vzorec SUMIF. Poďme sa pozrieť na príklad.
Máme zoznam známok, ktoré študenti dosiahli v dvoch rôznych predmetoch:
Predpokladá sa, že nájdeme tri najlepšie známky dosiahnuté pre každý predmet takto:
Aby sme to dosiahli, môžeme vnoriť funkciu IF pomocou predmet ako naše kritériá vo vnútri VEĽKEJ funkcie fungujú takto:
= VEĽKÉ (IF (=,),)
= VEĽKÉ (IF ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
Keď používate Excel 2022 a starší, musíte vzorec zadať stlačením CTRL + SHIFT + ENTER aby ste okolo vzorca dostali kučeravé zátvorky.
Ako funguje vzorec?
Vzorec funguje tak, že vyhodnotí každú bunku v našom rozsahu kritérií ako PRAVDU alebo NEPRAVDU.
Nájdenie najvyššej hodnoty hodnotenia (k = 1) v matematike:
= VEĽKÉ (IF ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
= VEĽKÉ (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)
Ďalej funkcia IF nahradí každú hodnotu hodnotou FALSE, ak nie je splnená jej podmienka.
= VEĽKÉ ({0,81; FALSE; FALSE; 0,42; FALSE; 0,63; FALSE; 0,58; FALSE}, 1)
Teraz funkcia LARGE preskočí FALSE hodnoty a vypočíta najväčšiu (k = 1) zo zostávajúcich hodnôt (0,81 je najväčšia hodnota medzi 0,42 a 0,81).
MALÉ, AK
Rovnakú techniku možno namiesto toho použiť aj s funkciou SMALL.
= MALÉ (IF ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
VEĽKÝ IF s viacerými kritériami
Ak chcete použiť LARGE IF s viacerými kritériami (podobne ako funguje vstavaný vzorec SUMIFS), jednoducho vnorte do funkcie LARGE viac funkcií IF:
= VEĽKÉ (IF (=, IF (=,)),)
= VEĽKÉ (IF ($ D $ 2: $ D $ 18 = $ H3, IF ($ B $ 2: $ B $ 18 = $ G3, $ E $ 2: $ E $ 18)), I $ 2)
Ďalším spôsobom, ako zahrnúť viacero kritérií, je ich znásobiť, ako je uvedené v tomto článku
Tipy a triky:
- Pokiaľ je to možné, vždy odkazujte na polohu (k) z pomocnej bunky a referencie zámku (F4), pretože to uľahčí automatické vypĺňanie vzorcov.
- Ak používate Excel 2022 alebo novší, vzorec môžete zadať bez Ctrl + Shift + Enter.
- Ak chcete získať mená študentov, ktorí dosiahli najlepšie známky, skombinujte to s INDEX MATCH