Как в экселе найти число больше или меньше другого
Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ в Excel являются противоположными по своему смыслу и используются для определения соответственно наибольшего и наименьшего числового элемента в массиве данных.
Примечание: В Excel массивом является набор данных, представленный в виде единого объекта (например, диапазон ячеек). Массив в Excel может быть принят в качестве аргумента.
Особенности условий в функциях НАИБОЛЬШИЙ и НАИМЕНЬШИЙ
Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ возвращают k-е максимальное и минимальное значения соответственно в выбранном массиве данных. Данные функции применяют для поиска значений, которые занимают определенное относительное положение в множестве данных.
Примечание: для простого поиска наименьшего и наибольшего значений в диапазоне данных принято использовать функции МИН и МАКС, принимающие единственный параметр на вход – диапазон данных. НАИБОЛЬШИЙ и НАИМЕНЬШИЙ предлагают расширенный функционал для поиска 1-го, 2-го… k-го наибольшего/наименьшего значений в массиве.
Обе функции имеют схожий синтаксис, поэтому не будем рассматривать его отдельно для каждой функции. Рассмотрим синтаксис для НАИБОЛЬШИЙ:
- Массив – диапазон либо массив числовых значений, для которого вычисляется k-е наибольшее значение. Является обязательным аргументом.
- K – аргумент, указывающий на позицию в наборе данных или массиве начиная с наименьшего значения. Также является обязательным аргументом функции.
Примеры работы в Excel с функциями НАИБОЛЬШИЙ и НАИМЕНЬШИЙ
Пример 1. В конструкторском отделе предприятия работают 8 инженеров. Необходимо определить четвертую наибольшую и наименьшую зарплаты соответственно.
Внесем данные в таблицу:
Для определения наименьшей 4-й зарплаты в отделе введем следующую формулу в ячейку C2:
Аргументами данной функции являются:
- B3:B10 – массив значений заработной платы для всех сотрудников;
- 4 – порядок искомого наименьшего значения в массиве.
Чтобы определить наибольшую 4-ю зарплату вводим формулу в ячейке D2:
Аргументы этой функции соответствуют тем, которые принимала функция НАИМЕНЬШИЙ в рамках данного примера.
Получаем следующие результаты:
То есть, наименьшая и наибольшая четвертые зарплаты в отделе равны 3200 и 4000 денежных единиц соответственно.
Четвертое наименьшее значение в массиве чисел
Пример 2. Для наглядности работы функции определим 1-й, 2-й, 3-й, 4-й и 5-й элементы массива данных, состоящего из пяти элементов. Из полученных результатов составим новую таблицу, произведя таким образом, по сути, сортировку элементов массива по возрастанию.
Внесем данные в таблицу:
Для решения будем использовать функцию НАИМЕНЬШИЙ, находя последовательно наименьшее 1-е, 2-е, … ,5-е значения и занося их в новую таблицу. Для примера рассмотрим процесс нахождение наименьшего 1-го значения. В ячейке C2 введем следующую формулу:
Функция принимает следующие аргументы:
- B2:B6 – диапазон значений исходного массива;
- 1 – порядок искомого наименьшего значения.
Аналогичным способом заполним ячейки C3, C4, C5 и C6, указывая в качестве аргумента k числа 2, 3, 4 и 5 соответственно.
В результате получим:
То есть, нам удалось отсортировать исходный массив и наглядно продемонстрировать работу функции НАИМЕНЬШИЙ.
- Подобным способом можно выполнить обратную сортировку (от большего к меньшему) используя функцию НАИБОЛЬШИЙ;
- Для сортировки лучше использовать другие возможности Excel, данный пример приведен лишь с целью наглядной демонстрации работы.
Формула функций НАИБОЛЬШИЙ с массивом и СУММ
Пример 3. В фирме работают 10 сотрудников, включая генерального директора и его заместителя. Один из сотрудников предположил, что оба руководителя получают в целом больше, чем все остальные сотрудники. Необходимо определить, является это предположение истиной или ложью.
Внесем данные о зарплате сотрудников в таблицу:
Очевидно, что зарплата у любого из двух руководителей больше, чем у любого из остальных сотрудников. Поэтому мы можем использовать функцию НАИБОЛЬШИЙ для поиска значений зарплаты гендиректора и заместителя. Для решения запишем следующую формулу:
Аргументами функции СУММ являются значения, которые вернет функция НАИБОЛЬШИЙ. Последняя принимает следующие аргументы:
- B3:B10 – массив, хранящий данные о зарплатах всех работников фирмы;
- – интервал, соответствующий первому и второму искомым величинам.
Примечание: – вариант записи массивов в Excel. С помощью этой записи было указано о необходимости вернуть первые два наибольших значения из массива B3:B10. Полученные значения будут просуммированы функцией СУММ.
В результате получим сумму зарплат директора и заместителя:
Теперь определим общую сумму зарплат оставшихся работников используя функцию СУММ в ячейке D2.
Визуально видно, что сотрудник оказался прав. Однако используем функционал Excel для отображения результата решения задачи в ячейке D6:
=ЕСЛИ(C3>D3;"Сотрудник оказался прав";"Сотрудник неправ")
Функция ЕСЛИ принимает следующие аргументы:
- C3>D3 – логическое выражение, в котором C3 – суммарная з/п руководителей, D3 – суммарная з/п остальных сотрудников;
- "Сотрудник оказался прав" – текст, который будет отображен в случае, если C3>D3 – истина;
- "Сотрудник неправ" – текст, который отобразится в случае, если C3>D3 – ложь.
То есть, оба руководителя получают больше денег, чем остальные сотрудники вместе взятые.
В прошлом выпуске мы рассмотрели задачу, в которой нужно было определить сотрудника с максимальным возрастом и вывести его имя в отдельную ячейку.
Предложенное решение этой задачи имеет один существенный недостаток. Дело в том, что функция МАКС возвращает первое максимальное значение из диапазона и на этом весь расчет прекращается. Это означает, что если в списке будет два или больше сотрудников с одинаковым возрастом, то в результатах мы увидим лишь первого по списку.
Давайте решим задачу таким образом, чтобы получился список всех сотрудников, имеющих максимальный возраст.
Итак, первым делом мы по-прежнему должны определить максимальный возраст. Для этого вполне подойдет функция МАКС , которую мы уже использовали ранее.
Ну а далее нам нужно сравнивать возраст каждого сотрудника с максимальным и в случае совпадения выводить имя сотрудника в столбце F ниже.
На первый взгляд ничего сложного, но не так все просто, как кажется и сейчас вы в этом убедитесь.
Дело в том, что все стандартные функции Экселя, которые сразу приходят на ум, выдадут лишь первое значение из диапазона, удовлетворяющее условию. Поэтому нам придется воспользоваться формулой массива .
Формула массива является частью стандартного функционала Экселя и многие даже не догадываются об ее существовании.
По сути, формула массива позволяет выполнять несколько вычислений с одним или несколькими элементами диапазона ячеек, то есть элементами массива. В итоге формула массива возвращает либо один результат вычислений, либо несколько результатов, то есть массив значений, что нам и нужно.
Не буду сейчас вдаваться в подробности, так как эта заметка и так будет довольно длинной. Если вы хотите узнать больше о формуле массива, то напишите об этом в комментариях и я раскрою тему подробнее в одной из следующих заметок.
Итак, давайте решим задачу.
Как и в прошлый раз , воспользуемся функцией ИНДЕКС , которая возвращает значение ячейки, заданного номером строки и номером столбца.
Первый ее аргумент - массив значений. Выбираем все ячейки с именами сотрудников.
Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.
Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.
Пусть имеются данные
Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:
Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.
Теперь, меняя название группы, можно без всяких фильтров и сводных таблиц видеть максимальное значение внутри этой группы.
Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.
Указанное выражение отбирает только те значения, для которых название группы совпадает с условием в ячейке D2. Вот, как это видит Excel
На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.
Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.
Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.
Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)
Есть ряд значений, каждое из которых входит в некоторую группу. Нужно рассчитать максимальное значение по группе А. Используем формулу МАКСЕСЛИ.
Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.
Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.
Найти, например, второе наибольшее значение в списке можно с помощью функции НАИБОЛЬШИЙ() . В статье приведено решение задачи, когда наибольшее значение нужно найти не среди всех значений списка, а только среди тех, которые удовлетворяют определенным критериям.
Пусть имеется таблица с двумя столбцами: текстовым и числовым (см. файл примера ).
Для удобства создадим два именованных диапазона : Текст ( 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-м критериям.
Пусть имеется таблица с тремя столбцами: Название фрукта, Поставщик и Количество.
Таблицу критериев разместим правее таблицы с данными.
Найдем 3-е наименьшее значение среди чисел, находящихся в строках, для которых Название фрукта = Яблоко, а Поставщик = ООО Рога с помощью формулы массива :
После набора формулы не забудьте вместо ENTER нажать CTRL+SHIFT+ENTER .
СОВЕТ: Создание формул с множественными критериями подробно рассмотрено в разделах Сложения и Подсчета значений .
Читайте также: