Здравствуйте. Недавно я рассказывал, как получить наибольшее N-е значение в массиве с использованием функции НАИБОЛЬШИЙ. Сегодня усложним задачу, будем искать, с учетом критерия.

Например, в списке продаж (по менеджерам), требуется найти тройку лучших, но в определённом регионе — Юг:

Формула для вычисления:

=НАИБОЛЬШИЙ(ЕСЛИ(список_значений_критерия= критерий; список_значений_рейтинга);1)

  • список_значений_критерия – массив данных, которые будут проверяться на соответствие заданному фильтру (список регионов $C$3:$C$17)
  • критерий – значение фильтра (название региона, в примере – ячейка $G$2)
  • список_значений_рейтинга – массив данных, из которых выбираются наибольшие (у нас – продажи, $D$3:$D$17)

Готовый расчет выглядит так:

Фигурные скобки, как на рисунке, печатать не нужно. Они означают, что используется формула массива. Введите всё, что между фигурными скобками и нажмите Ctrl+Shift+Enter, чтобы получить правильный результат.

Эта формула работает так:

  1. Функция ЕСЛИ сравнивает все названия регионов в таблице с тем, который нам нужен. При совпадении, выводит объем продаж в этой же строке таблицы. Получается массив результатов такого вида: {ЛОЖЬ, ЛОЖЬ, ЛОЖЬ, ЛОЖЬ, 77705, 89157, …}
  2. Функция НАИБОЛЬШИЙ выбирает n-й наибольший элемент в этом массиве и выводит его на экран

Кстати, выбор критерия (региона) удобно делать с помощью выпадающего списка. Что это и как его сделать – читайте в этой статье!

Если нужно найти наименьшие значения, очевидно, вместо функции НАИБОЛЬШИЙ используем НАИМЕНЬШИЙ.