Загрузите образец книги
В этом руководстве будет показано, как вычислить «большое если» или «маленькое если», получив n-е наибольшее (или наименьшее) значение на основе критериев.
БОЛЬШИЕ И МАЛЕНЬКИЕ функции
Функция НАИБОЛЬШИЙ используется для вычисления n-го наибольшего значения (k) в массиве, а функция НАИБОЛЬШИЙ возвращает наименьшее n-е значение.
Чтобы создать «большое если», мы будем использовать функцию НАИБОЛЬШИЙ вместе с функцией ЕСЛИ в формуле массива.
БОЛЬШОЙ ЕСЛИ
Комбинируя LARGE (или SMALL) и IF в формуле массива, мы можем создать функцию «LARGE IF», которая работает аналогично встроенной формуле СУММЕСЛИ. Давайте рассмотрим пример.
У нас есть список оценок, полученных учащимися по двум разным предметам:
Предположим, нас просят найти три лучших результата по каждому предмету, например:
Для этого мы можем вложить функцию ЕСЛИ с тема в качестве наших критериев внутри функции НАИБОЛЬШИЙ:
= НАИБОЛЬШИЙ (ЕСЛИ (=,),)
= БОЛЬШОЙ (ЕСЛИ ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
При использовании Excel 2022 и более ранних версий необходимо ввести формулу, нажав CTRL + SHIFT + ВВОД чтобы заключить формулу в фигурные скобки.
Как работает формула?
Формула работает, оценивая каждую ячейку в нашем диапазоне критериев как ИСТИНА или ЛОЖЬ.
Как найти высшую оценку (k = 1) по математике:
= БОЛЬШОЙ (ЕСЛИ ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
= НАИБОЛЬШИЙ (ЕСЛИ ({ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ}; {0,81; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 1)
Затем функция IF заменяет каждое значение на FALSE, если его условие не выполняется.
= НАИБОЛЬШИЙ ({0,81; ЛОЖЬ; ЛОЖЬ; 0,42; ЛОЖЬ; 0,63; ЛОЖЬ; 0,58; ЛОЖЬ}; 1)
Теперь функция НАИБОЛЬШИЙ пропускает значения ЛОЖЬ и вычисляет наибольшее (k = 1) из оставшихся значений (0,81 - наибольшее значение между 0,42 и 0,81).
МАЛЫЙ ЕСЛИ
Тот же метод можно применить вместо этого с функцией МАЛЕНЬКИЙ.
= МАЛЕНЬКИЙ (ЕСЛИ ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
БОЛЬШОЙ ЕСЛИ с несколькими критериями
Чтобы использовать БОЛЬШОЙ ЕСЛИ с несколькими критериями (аналогично тому, как работает встроенная формула СУММЕСЛИМН), просто вложите больше функций ЕСЛИ в функцию НАИБОЛЬШИЙ следующим образом:
= НАИБОЛЬШИЙ (ЕСЛИ (=, ЕСЛИ (=,)),)
= БОЛЬШОЙ (ЕСЛИ ($ D $ 2: $ D $ 18 = $ H3, ЕСЛИ ($ B $ 2: $ B $ 18 = $ G3, $ E $ 2: $ E $ 18)), I $ 2)
Другой способ включить несколько критериев - перемножить критерии, как показано в этой статье.
Секреты и уловки:
- По возможности всегда указывайте позицию (k) из вспомогательной ячейки и блокируйте ссылку (F4), так как это упростит автоматическое заполнение формул.
- Если вы используете Excel 2022 или новее, вы можете вводить формулу без Ctrl + Shift + Enter.
- Чтобы получить имена учащихся, получивших наивысшие оценки, объедините это с ИНДЕКСОМ МАТЧ
