Изтеглете примерната работна книга
Този урок ще покаже как да се изчисли „голям ако“ или „малък ако“, като се извлече n-тата най-голяма (или най-малката) стойност въз основа на критерии.
ГОЛЕМИ И МАЛКИ функции
Функцията LARGE се използва за изчисляване на n -тата най -голяма стойност (k) в масив, докато функцията SMALL връща най -малката n -та стойност.
За да създадем „Голямо ако“, ще използваме функцията LARGE заедно с функцията IF във формула на масив.
ГОЛЯМ АКО
Комбинирайки LARGE (или SMALL) и IF във формула на масив, можем по същество да създадем функция „LARGE IF“, която работи подобно на начина, по който работи вградената формула SUMIF. Нека преминем през пример.
Имаме списък с оценките, постигнати от учениците по два различни предмета:
Предполага се, че сме помолени да намерим първите три оценки, постигнати за всеки предмет, така:
За да постигнем това, можем да вмъкнем IF функция с предмет като нашите критерии вътре в функцията LARGE функционират така:
= ГОЛЯМ (АКО (=,),)
= ГОЛЯМ (АКО ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
Когато използвате Excel 2022 и по -ранни версии, трябва да въведете формулата, като натиснете CTRL + SHIFT + ENTER за да завъртите фигурните скоби около формулата.
Как действа формулата?
Формулата работи, като оценява всяка клетка в нашия диапазон от критерии като TRUE или FALSE.
Намиране на най -високата оценка (k = 1) в математиката:
= ГОЛЯМ (АКО ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
= ГОЛЯМ (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)
След това функцията IF заменя всяка стойност с FALSE, ако нейното условие не е изпълнено.
= ГОЛЯМ ({0,81; FALSE; FALSE; 0,42; FALSE; 0,63; FALSE; 0,58; FALSE}, 1)
Сега функцията LARGE пропуска стойностите FALSE и изчислява най -голямата (k = 1) от останалите стойности (0,81 е най -голямата стойност между 0,42 и 0,81).
МАЛКО АКО
Същата техника може да се приложи и с функцията SMALL.
= МАЛКО (АКО ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
ГОЛЯМ АКО с множество критерии
За да използвате LARGE IF с множество критерии (подобно на начина, по който работи вградената формула SUMIFS), просто вложете повече IF функции във функцията LARGE по следния начин:
= ГОЛЯМ (IF (=, IF (=,)),)
= ГОЛЯМ (АКО ($ D $ 2: $ D $ 18 = $ H3, АКО ($ B $ 2: $ B $ 18 = $ G3, $ E $ 2: $ E $ 18)), I $ 2)
Друг начин да включите множество критерии е да умножите критериите заедно, както е показано в тази статия
Съвети и трикове:
- Когато е възможно, винаги посочвайте позицията (k) от помощната клетка и референтната точка (F4), тъй като това ще улесни формулите за автоматично попълване.
- Ако използвате Excel 2022 или по -нова версия, можете да въведете формулата без Ctrl + Shift + Enter.
- За да извлечете имената на ученици, постигнали най -добрите оценки, комбинирайте това с INDEX MATCH
