Эксель выбор наибольшего значения

Эксель выбор наибольшего значения

Найти, например, второе наибольшее значение в списке можно с помощью функции НАИБОЛЬШИЙ() . В статье приведено решение задачи, когда наибольшее значение нужно найти не среди всех значений списка, а только среди тех, которые удовлетворяют определенным критериям.

Пусть имеется таблица с двумя столбцами: текстовым и числовым (см. файл примера ).

Для удобства создадим два именованных диапазона : Текст ( A 3: A 27 ) и Числа ( B3:B27 ).

СОВЕТ: Создание формул для определения минимального и максимального значения с учетом условий рассмотрено в статье Максимальный и Минимальный по условию в MS EXCEL .

Определение наибольшего значения с единственным критерием

Найдем с помощью формулы массива второе наибольшее значение среди тех чисел, которые соответствуют значению Текст2 (находится в ячейке Е6 ) :

После набора формулы не забудьте вместо ENTER нажать CTRL+SHIFT+ENTER .

Чтобы разобраться в работе формулы, выделите в Строке формул выражение ЕСЛИ(Текст=E6;Числа) и нажмите клавишу F9 . Выделенная часть формулы будет заменена на результат, т.е. на массив значений :

Значение ЛОЖЬ соответствует строкам, в которых в столбце Текст нет значения Текст2. В противном случае выводится само число. Т.к. функция НАИБОЛЬШИЙ() игнорирует текстовые значения и значения ЛОЖЬ и ИСТИНА, то 2-е наибольшее будет искаться только среди чисел -95; -66; -20; 0; 4; 9. Результат: 4.

СОВЕТ: Задачу можно решить без использования формулы массива . Для этого потребуется создать дополнительный столбец, в котором будут выведены только те значения, которые удовлетворяют критерию. Затем, среди отобранных значений с помощью функций НАИБОЛЬШИЙ() , определить нужное значение.

Определение наименьшего значения с несколькими критериями

Теперь найдем 3-е наименьшее значение среди тех чисел, которые соответствуют сразу 2-м критериям.

Пусть имеется таблица с тремя столбцами: Название фрукта, Поставщик и Количество.

Читайте также:  Нужен ли телевизор 4к вообще

Таблицу критериев разместим правее таблицы с данными.

Найдем 3-е наименьшее значение среди чисел, находящихся в строках, для которых Название фрукта = Яблоко, а Поставщик = ООО Рога с помощью формулы массива :

После набора формулы не забудьте вместо ENTER нажать CTRL+SHIFT+ENTER .

Если ни одна запись в таблице не удовлетворяет одновременно двум критериям (Условие И), то формула возвращает значение ошибки #ЧИСЛО!

СОВЕТ: Создание формул с множественными критериями подробно рассмотрено в разделах Сложения и Подсчета значений .

Скорее всего вам известны простые функции МИН (MIN) и МАКС (MAX) , позволяющие быстро найти минимальное или максимальное значение в таблице. Но что если нам требуется найти, например, не самое большое, а 2-е или 5-е значение в ТОПе? Здесь помогут функции НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL) .

Синтаксис этих функций похож:

=НАИБОЛЬШИЙ( Диапазон ; Позиция )

=НАИМЕНЬШИЙ( Диапазон ; Позиция )

  • Диапазон – диапазон ячеек с числами, которые мы проверяем.
  • Позиция – целое число, представляющее собой позицию (ранг, номер в рейтинге) извлекаемого элемента.

Например, если у нас есть таблица с прибылями по товарам, то с помощью функции НАИБОЛЬШИЙ можно легко определить максимальное (первое в рейтинге) значение из диапазона прибыли (B2:B22), которое фактически будет тождественно формуле МАКС(B2:B22):

Аналогичным образом, функция

… выдаст следующее за ним максимальное (2-е в рейтинге) и т.д.

Массив констант и сумма ТОПов

Что интересно, аргумент Позиция может быть не просто числом, а набором чисел — массивом констант в фигурных скобках. Так, например, для получения суммы первых трех максимальных значений в диапазоне можно использовать формулу с прописанным внутри массивом констант для первых трех позиций (сочетание Ctrl+Shift+Enter в конце можно не нажимать, хотя по факту это и формула массива):

Читайте также:  Внешний 3g 4g роутер rf link r850

Сортировка формулой

Функцию НАИМЕНЬШИЙ (SMALL) очень удобно использовать для сортировки формулой набора числовых значений. Для этого достаточно сделать вспомогательную нумерацию (1, 2, 3. ) и ссылаться на нее во втором аргументе:

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

Только числа

Еще одной полезной особенностью этих функций является то, что они игнорируют все, кроме чисел, т.е. "не видят" текст и логические значения (ИСТИНА, ЛОЖЬ). Это бывает полезно использовать, например, для извлечения всех сумм по заданному наименованию, когда товар встречается больше одного раза и ВПР (VLOOKUP) уже не поможет:

В этом примере функция ЕСЛИ (IF) в столбце С проверяет наименование на соответствие заданному (Огурцы) и выводит сумму или логическую ЛОЖЬ. А для извлечения потом всех полученных сумм из столбца С используется наша функция НАИМЕНЬШИЙ, которая игнорирует ЛОЖЬ — и мы получаем список стоимостей всех сделок по нужному товару.

Вопреки мнению большинства людей в первый раз услышавших название этих функций, функции «НАИБОЛЬШИЙ» и «НАИМЕНЬШИЙ» не находят наибольшее и наименьшее значение функций. Вернее находят не только эти значения, возможности этих функций гораздо шире.

«НАИБОЛЬШИЙ» или «НАИМЕНЬШИЙ» находят заданное по счету наибольшее или наименьшее число.

Например, имеется таблица с результатами турнира по метанию молота.

В таблице отображены фамилии метателей и их результаты.

Чтобы найти победителя достаточно найти наибольший результат, но чтобы найти номинантов на второе и третье место необходимо найти не самый больший результат, а второй и третий по величине показатель. В этом нам поможет функция «НАИБОЛЬШИЙ», которая позволяет искать результаты второй и третий по величине от наибольшего.

Применение функции «НАИБОЛЬШИЙ»:

  1. Поставить курсор в ячейку , которой будет присвоено значение;
  2. Выбрать функцию «НАИБОЛЬШИЙ» в мастере функций;
  3. В появившейся форме указать диапазон значений и, какое по счету от наибольшего значения число следует искать;
  4. Нажать «ENTER» или «ОК».
Читайте также:  Смартфоны с мощным приемом сети

Аналогичным образом можно применять и функцию «НАИМЕНЬШИЙ», которая в отличии от функции «НАИБОЛЬШИЙ» находит наименьшие значения.

Ссылка на основную публикацию
Характеристики процессора интел пентиум 4
Количество ядер - 1. Благодаря технологии Hyper-Threading, количество потоков 2, что вдвое больше числа физических ядер и увеличивает производительность многопоточных...
Сканер ricoh sp 220snw
Компания Ricoh — далеко не новичок на рынке печатающих устройств. Это глобальная корпорация со штаб-квартирой в Токио и представительствами во...
Сколько дают на ютубе за 1000 просмотров
Многих пользователей YouTube, а также начинающих видеоблогеров справедливо интересует вопрос: «А сколько YouTube платит за тысячу или миллион просмотров?» Если...
Хлебопечка мулинекс ow1101 инструкция и рецепты
Инструкция MOULINEX OW 1101 на русском языке в формате pdf для устройства: хлебопечь. Прочитайте инструкцию для ознакомления с функциями и...
Adblock detector