Команда наименьшее в excel

Функция

НАИМЕНЬШИЙ(

)

, английский вариант SMALL(),

возвращает k-ое наименьшее значение из массива данных. Например, если

диапазон

A1:А4

содержит значения 2;10;3;7, то

формула

=

НАИМЕНЬШИЙ

(A1:А4;2)

вернет значение 3 (второе наименьшее).

Синтаксис

НАИМЕНЬШИЙ

(

массив

;

k

)

Массив

— ссылка на диапазон ячеек, содержащие данные, для которых определяется k-ое наименьшее значение. Также возможен ввод в качестве аргумента

массива констант

, например, =

НАИМЕНЬШИЙ({10:20:30:40:50};1)

.Формула вернет значение 10 (1-ое нименьшее).

k

— позиция (начиная с наименьшей) в массиве или диапазоне ячеек. Если k < 0 или k больше, чем количество значений в

массиве

, то функция

НАИМЕНЬШИЙ

()

возвращает значение ошибки #ЧИСЛО!

Что бывает, если диапазон содержит не только числа?

Пустые ячейки, логические значения (ЛОЖЬ и ИСТИНА) и текст функцией игнорируются. Это видно из таблицы в

файле примера

.

Как видно из таблицы (столбец D), значение ошибки приводит к ошибке в формуле. Если такая ситуация возможна, то создайте с помощью формулы

=

ЕСЛИОШИБКА

(A1;»ф»)

новый диапазон, которая заменит ошибку на текст, который игнорируется функцией. Конечно, нужно также изменить ссылку на диапазон в соседнем столбце.

Что бывает, если диапазон не содержит числа?

Если в массиве нет ни одного числового значения, то функция вернет значение ошибки #ЧИСЛО!, что выгодно ее отличает от функции

МИН()

, возвращающую в этом случае 0!

Если такая ситуация возможна и необходимо вычислить минимальное значение, то вместо функции

МИН()

нужно использовать формулу

=

НАИМЕНЬШИЙ

(массив;1)

Примечание

: Формула

=

НАИМЕНЬШИЙ

(массив;n)

вернет наибольшее (максимальное) значение из массива размера n. Т.е. формула

=

НАИМЕНЬШИЙ

(массив;1)

эквивалентна

=МИН(массив)

, а

=

НАИМЕНЬШИЙ

(массив;n)

эквивалентна

=МАКС(массив)

Значение числа в текстовом формате

игнорируется функцией

НАИМЕНЬШИЙ

()

(см. столбец Е на рисунке выше). Перед нахождением наименьшего значения можно попытаться преобразовать все значения в числовой формат. Это можно сделать

формулой массива

=

НАИМЕНЬШИЙ

(ЕСЛИ(ЕЧИСЛО(E5:E9+0);E5:E9+0;»»);1)

Что бывает, если в диапазоне есть повторы

Если имеется исходный массив {1;3;

3

;6;6;7}, то третьим наименьшим (по версии функции

НАИМЕНЬШИЙ

()

) будет считаться 3, а не 6. Все правильно и логично, но иногда об этом забывают. С человеческой точки зрения третьим наименьшим будет все-таки, наверное, 6 (т.е. повторы мы не учитываем).

Наименьший с учетом условия

В отличие от функции

СУММ()

и

СЧЁТ()

у

НАИМЕНЬШИЙ

()

в MS EXCEL 2010 нет аналога

СУММЕСЛИ()

и

СЧЁТЕСЛИ()

, позволяющих выполнять вычисления с учетом условия. Но, с помощью

формул массива

можно получить формулу для нахождения наименьшего с учетом условия (как это сделать см.

здесь

).

Кстати, в

Excel для Office 365 есть функция МИНЕСЛИ().

Сумма 3-х наименьших

С помощью нестандартной записи второго аргумента можно расширить возможности функции

НАИМЕНЬШИЙ

()

. Например, найдем сумму 3-х наименьших значений из диапазона

A5:A9

=

СУММ(

НАИМЕНЬШИЙ

(A5:A9;{1;2;3}))

Второй аргумент введен как

константа массива

, что позволило найти 3 наименьших значения.

Аналогично можно найти, например, среднее 2-х наименьших:

=СРЗНАЧ(

НАИМЕНЬШИЙ

(A5:A9;{1;2}))

Удивительно, но 2 последние формулы даже не обязательно вводить как

формулы массива

.

Другие применения функции

Функция

НАИМЕНЬШИЙ

()

является достаточно часто используемой, т.к. она позволяет упорядочивать числовые массивы. Ее можно, например, использовать для

сортировки списков

и

таблиц

.

Функция НАИМЕНЬШИЙ (SMALL) в Excel используется для получения минимального значения из заданного диапазона ячеек.

Более того, с помощью функции НАИМЕНЬШИЙ в Excel вы сможете задать очередность наименьшего числа по величине. Например из диапазона (1,3,5) вы сможете получить с помощью функции второе по меньшинству число (3).

Что возвращает функция

Возвращает минимальное значение из заданного диапазона (включая заданную очередность наименьшего числа по величине).

Синтаксис

=SMALL(array, k) — английская версия

=НАИМЕНЬШИЙ(массив;k) — русская версия

• array (массив) — массив или диапазон ячеек из которого вы хотите вычислить минимальное значение;
• k — ранг (очередность наименьшего числа по величине), которую вам нужно вычислить из диапазона данных.

Дополнительная информация

• если аргумент функции array пустой, то функция выдаст ошибку;
• если аргумент K ≤ 0 или его значение больше чем количество чисел в диапазоне, то функция выдаст ошибку;
• вы можете указать значение «n» в аргументе k если вы хотите получить первое (наибольшее) число в диапазоне. Если вы укажете значение «1» в качестве аргумента k то по умолчанию получите минимальное значение из заданного диапазона;

Примеры использования функции НАИМЕНЬШИЙ в Excel

Пример 1. Вычисляем наименьшее число из списка

Представим, что у нас есть список чисел в диапазоне (A2:A4) и нам необходимо вычислить наименьшее число из этого списка. Для этого мы можем использовать формулу:

=SMALL(A2:A4,1) — английская версия

=НАИМЕНЬШИЙ(A2:A4;1) — русская версия

В этой функции значение аргумента K равно 1, таким образом функция понимает, что нам необходимо отразить минимальное значение.

Пример 2. Вычисляем второе наименьшее число из списка

Для того чтобы вычислить из указанного выше диапазона данных (A2:A4) второе по счету наименьшее число, нам поможет формула:

=SMALL(A2:A4,2) — английская версия

=НАИМЕНЬШИЙ(A2:A4;2) — русская версия

Так как значение аргумента функции K равно «2», то функция понимает что нам необходимо отразить второе по порядку наименьшее значение.

Пример 3. Использование функции НАИМЕНЬШИЙ с пустыми ячейками

Если в диапазоне данных, из которого вам нужно вычислить наименьшее число есть пустые ячейки, как указано на примере выше, то функция не учитывает их при вычислении.

Пример 4. Использование функции НАИМЕНЬШИЙ с текстовыми значениями в ячейках

Если в списке числе, среди которых нам нужно вычислить наименьшее число находятся ячейки с текстом, то функция, также как и в случае с пустыми ячейками, игнорирует их.

Пример 5. Использование функции НАИМЕНЬШИЙ с дублированными данными

В тех случаях, когда в диапазоне данных встречаются два одинаковых значения, функция определит их как последовательные значения. При выводе наименьшего и второго по порядку числа, функция выдаст одно и то же значение.

Пример 6. Использование функции НАИМЕНЬШИЙ в Excel с ошибками

В тех случаях, когда в указанном диапазоне есть ячейки с ошибками, функция  также выдаст ошибку.

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

Примечание: В Excel массивом является набор данных, представленный в виде единого объекта (например, диапазон ячеек). Массив в Excel может быть принят в качестве аргумента.

Особенности условий в функциях НАИБОЛЬШИЙ и НАИМЕНЬШИЙ

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

Примечание: для простого поиска наименьшего и наибольшего значений в диапазоне данных принято использовать функции МИН и МАКС, принимающие единственный параметр на вход – диапазон данных. НАИБОЛЬШИЙ и НАИМЕНЬШИЙ предлагают расширенный функционал для поиска 1-го, 2-го… k-го наибольшего/наименьшего значений в массиве.

Обе функции имеют схожий синтаксис, поэтому не будем рассматривать его отдельно для каждой функции. Рассмотрим синтаксис для НАИБОЛЬШИЙ:

=НАИБОЛЬШИЙ(массив; k)

Описание аргументов:

1. Массив – диапазон либо массив числовых значений, для которого вычисляется k-е наибольшее значение. Является обязательным аргументом.
2. K – аргумент, указывающий на позицию в наборе данных или массиве начиная с наименьшего значения. Также является обязательным аргументом функции.

Примечания:

1. Если значение аргумента k превышает количество элементов в массиве данных, равно нулю или взято из диапазона отрицательных чисел, результатом работы функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ будет ошибка #ЧИСЛО!;
2. Ошибка #ЧИСЛО! возникает также в случае, если массив окажется пустым;
3. Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ игнорируют текстовые данные, которые могут содержаться в массиве.
4. Если при использовании функции НАИМЕНЬШИЙ в качестве аргумента k указать 1 (единицу), результат будет тождественен результату работы функции МИН;
5. Если при использовании НАИМЕНЬШИЙ в качестве аргумента k указать размер массива (количество элементов, содержащихся в нем), будет получен результат, тождественный результату работы функции МАКС.



Примеры работы в Excel с функциями НАИБОЛЬШИЙ и НАИМЕНЬШИЙ

Пример 1. В конструкторском отделе предприятия работают 8 инженеров. Необходимо определить четвертую наибольшую и наименьшую зарплаты соответственно.

Внесем данные в таблицу:

Для определения наименьшей 4-й зарплаты в отделе введем следующую формулу в ячейку C2:

=НАИМЕНЬШИЙ(B3:B10;4)

Аргументами данной функции являются:

1. B3:B10 – массив значений заработной платы для всех сотрудников;
2. 4 – порядок искомого наименьшего значения в массиве.

Чтобы определить наибольшую 4-ю зарплату вводим формулу в ячейке D2:

=НАИБОЛЬШИЙ(B3:B10;4)

Аргументы этой функции соответствуют тем, которые принимала функция НАИМЕНЬШИЙ в рамках данного примера.

Получаем следующие результаты:

То есть, наименьшая и наибольшая четвертые зарплаты в отделе равны 3200 и 4000 денежных единиц соответственно.

Четвертое наименьшее значение в массиве чисел

Пример 2. Для наглядности работы функции определим 1-й, 2-й, 3-й, 4-й и 5-й элементы массива данных, состоящего из пяти элементов. Из полученных результатов составим новую таблицу, произведя таким образом, по сути, сортировку элементов массива по возрастанию.

Внесем данные в таблицу:

Для решения будем использовать функцию НАИМЕНЬШИЙ, находя последовательно наименьшее 1-е, 2-е, … ,5-е значения и занося их в новую таблицу. Для примера рассмотрим процесс нахождение наименьшего 1-го значения. В ячейке C2 введем следующую формулу:

=НАИМЕНЬШИЙ(B2:B6;1)

Функция принимает следующие аргументы:

1. B2:B6 – диапазон значений исходного массива;
2. 1 – порядок искомого наименьшего значения.

Аналогичным способом заполним ячейки C3, C4, C5 и C6, указывая в качестве аргумента k числа 2, 3, 4 и 5 соответственно.

В результате получим:

То есть, нам удалось отсортировать исходный массив и наглядно продемонстрировать работу функции НАИМЕНЬШИЙ.

Примечания:

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

Формула функций НАИБОЛЬШИЙ с массивом и СУММ

Пример 3. В фирме работают 10 сотрудников, включая генерального директора и его заместителя. Один из сотрудников предположил, что оба руководителя получают в целом больше, чем все остальные сотрудники. Необходимо определить, является это предположение истиной или ложью.

Внесем данные о зарплате сотрудников в таблицу:

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

=СУММ(НАИБОЛЬШИЙ(B3:B10;{1;2}))

Аргументами функции СУММ являются значения, которые вернет функция НАИБОЛЬШИЙ. Последняя принимает следующие аргументы:

1. B3:B10 – массив, хранящий данные о зарплатах всех работников фирмы;
2. {1;2} – интервал, соответствующий первому и второму искомым величинам.

Примечание: {1;2} – вариант записи массивов в Excel. С помощью этой записи было указано о необходимости вернуть первые два наибольших значения из массива B3:B10. Полученные значения будут просуммированы функцией СУММ.

В результате получим сумму зарплат директора и заместителя:

Теперь определим общую сумму зарплат оставшихся работников используя функцию СУММ в ячейке D2.

Визуально видно, что сотрудник оказался прав. Однако используем функционал Excel для отображения результата решения задачи в ячейке D6:

=ЕСЛИ(C3>D3;»Сотрудник оказался прав»;»Сотрудник неправ»)

Функция ЕСЛИ принимает следующие аргументы:

1. C3>D3 – логическое выражение, в котором C3 – суммарная з/п руководителей, D3 – суммарная з/п остальных сотрудников;
2. «Сотрудник оказался прав» – текст, который будет отображен в случае, если C3>D3 – истина;
3. «Сотрудник неправ» – текст, который отобразится в случае, если C3>D3 – ложь.

Скачать примеры функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ в Excel

То есть, оба руководителя получают больше денег, чем остальные сотрудники вместе взятые.