В этой статье мы поговорим о функции НАИМЕНЬШИЙ в Excel, о том, как она работает и как ее использовать для поиска N-го наименьшего значения числа, даты или времени.
Формула НАИМЕНЬШИЙ представляет собой один из ключевых инструментов для анализа данных, предоставляя пользователям возможность легко находить значения, удовлетворяющие определенным критериям. Она особенно полезна для тех, кто часто работает с большими объемами данных, такими как аналитики, исследователи и финансисты, поскольку позволяет быстро и эффективно извлекать необходимые данные из массива. Основной особенностью функции является ее способность находить n-е наименьшее число в указанном диапазоне данных.
Функция НАИМЕНЬШИЙ находит широкое применение в различных областях. В финансовом анализе, она помогает определять минимальные значения курсов акций или других финансовых показателей за определенный период времени. В исследовательской деятельности, она используется для выявления минимальных значений различных параметров, таких как температура, давление или степень загрязнения. В управлении данными, функция может применяться для оценки производительности, выявления отклонений и оптимизации процессов.
Нужно найти несколько наименьших чисел в столбце? Это довольно легко сделать с помощью функции сортировки Excel. Не хотите тратить время на повторную сортировку данных при каждом их изменении? Формула НАИМЕНЬШИЙ поможет вам быстро найти в Excel самое наименьшее значение, второе, третье наименьшее и т. д.
В целом, способность быстро и точно находить наименьшие числа делает ее важным инструментом для оптимизации рабочего процесса и повышения эффективности анализа данных. В следующих разделах мы подробно рассмотрим возможные практические применения ее в различных сценариях.
- Формула Excel НАИМЕНЬШИЙ
- Как найти наименьшее значение
- Важные замечания
- Формула НАИМЕНЬШИЙ - примеры
- Наименьшие числа с условием
- Как игнорировать нули при поиске наименьших значений
- Наименьший с несколькими условиями
- Наименьший с одним из нескольких условий
- Как выделить наименьшие значения в Excel
- Сравнение функции НАИМЕНЬШИЙ с другими функциями поиска
- Почему не работает
Формула Excel НАИМЕНЬШИЙ
НАИМЕНЬШИЙ (SMALL по-английски) — статистическая функция, которая возвращает n-е наименьшее значение в наборе данных Excel.
Синтаксис функции включает два аргумента, оба из которых являются обязательными.
НАИМЕНЬШИЙ(массив; k)
Где:
- Массив — массив или диапазон ячеек, из которого необходимо извлечь наименьшее число.
- K — целое число, указывающее позицию от наименьшего значения, которое необходимо вернуть, т.е. k-е наименьшее значение.
Функция доступна во всех версиях Excel.
Например, предположим, что у нас есть набор данных, содержащий следующие числа: 7, 5, 3, 8, 6, 2, 9. Чтобы выбрать третье наименьшее число, мы введем формулу: =НАИМЕНЬШИЙ(А1:А7; 3). В данном случае, A1:A7 представляет собой диапазон ячеек, содержащих наши данные. Когда мы применяем эту формулу, Excel возвращает значение 5, являющееся третьим наименьшим числом.
Функция НАИМЕНЬШИЙ крайне полезна при анализе больших объемов данных, так как она позволяет быстро и эффективно находить определенные значения. Например, аналитик может использовать её для определения третьего наименьшего продажного числа за месяц, что позволяет мгновенно выявить продукцию с низкой популярностью.
Однако формула НАИМЕНЬШИЙ может вызывать ошибки, если в массиве данных присутствуют пустые ячейки или текстовые значения. Чтобы избежать таких проблем, рекомендуется использовать формулы предварительной очистки данных или условное форматирование, удаляя некорректные значения заранее.
Например, можно применять функцию =ЕСЛИ(ЧИСЛО(ячейка); значение; ""), чтобы убедиться, что все ячейки содержат только числовые данные.
Как найти наименьшее значение
Формулу НАИМЕНЬШИЙ в ее базовой форме построить очень легко — вам просто нужно указать диапазон значений и позицию от самого наименьшего числа, которое нужно вернуть.
Предположим, в списке чисел в B2:B10 вы хотите выбрать третье наименьшее значение. Формула очень проста:
=НАИМЕНЬШИЙ(B2:B10; 3)
Чтобы вам было легче проверить результат, столбец B на скриншоте ниже отсортирован по возрастанию:
Важные замечания
Эта информация поможет вам лучше понять поведение функции НАИМЕНЬШИЙ и избежать путаницы при построении собственных формул.
- Любые пустые ячейки, текстовые значения и логические значения ИСТИНА и ЛОЖЬ функция игнорирует.
- Если массив содержит одну или несколько ячеек с ошибками, то возвращается ошибка.
- В случае, если в массиве есть дубликаты, ваша формула все их возвратит. Например, если две ячейки содержат число 1, а функция настроена чтобы получить первое и второе наименьшие значения, вы получите обе единицы.
- Предполагая, что n — это количество значений в массиве, выражение НАИМЕНЬШИЙ(массив;1) вернет самое маленькое, а НАИМЕНЬШИЙ(массив; n) покажет вам самое большое число.
Формула НАИМЕНЬШИЙ - примеры
А теперь давайте рассмотрим еще несколько примеров использования формулы НАИМЕНЬШИЙ в Excel, чтобы показать все ее возможности.
Найти наименьшее 3-е, 5-е, 10-е число и т. д.
Как вы уже знаете, функция НАИМЕНЬШИЙ предназначена для вычисления n-го наименьшего значения в Excel. Вот как можно сделать это наиболее эффективно.
Предположим, что вы хотите найти 3 самых маленьких числа. Для этого введите числа 1, 2 и 3 в отдельных ячейках (D3, D4 и D5 в нашем случае). Затем запишите следующую формулу в E3 и перетащите ее вниз до E5:
=НАИМЕНЬШИЙ($B$2:$B$10; D3)
В E3 формула извлекает наименьшее значение, используя число в D3 для аргумента k . Главное — указать правильные ссылки на ячейки Excel, благодаря которым формула правильно копируется в другие ячейки: абсолютные ссылки для аргумента массив и относительные для k .
Можно не создавать дополнительный столбец, если это по каким-то причинам нецелесообразно.
При помощи функции ЧСТРОК мы построим своего рода счетчик, который будет возвращать порядковые номера от 1 и далее.
=НАИМЕНЬШИЙ($B$2:$B$10;ЧСТРОК(B$2:B2))
Скопируйте эту формулу вниз по столбцу, и получите столько самых минимальных значений, сколько вам необходимо.
Не хотите утруждать себя копированием?
При помощи функции СТРОКА вы можете создать массив из нескольких чисел. При помощи этой формулы вы можете получить сразу 3 наименьших балла:
=НАИМЕНЬШИЙ($B$2:$B$10; СТРОКА(A1:A3))
Чтобы получить каждое третье наименьшее число, вы можете записать в ячейках числа 3, 6 и 9, либо немного изменить эту формулу:
=НАИМЕНЬШИЙ($B$2:$B$10; СТРОКА(A1:A3)*3)
Сумма N наименьших значений в Excel
Хотите найти сумму нескольких наименьших чисел в наборе данных? Если вы уже извлекли значения, как показано в предыдущем примере, самым простым решением будет формула СУММ , например:
=СУММ(E3:E5)
Или вы можете сделать все при помощи одной формулы, используя НАИМЕНЬШИЙ вместе с функцией СУММПРОИЗВ:
=СУММПРОИЗВ(НАИМЕНЬШИЙ(массив; {1;2;3…n}))
Чтобы получить сумму трех нижних значений в нашем наборе данных, формула принимает следующий вид:
=СУММПРОИЗВ(НАИМЕНЬШИЙ(B2:B10; {1;2;3}))
Функция СУММ даст тот же результат:
=СУММ(НАИМЕНЬШИЙ(B2:B10; {1;2;3}))
Также вы можете использовать ссылки на ячейки, как в предыдущем примере. Например:
=СУММПРОИЗВ(НАИМЕНЬШИЙ(B2:B10; СТРОКА(A1:A3)))
Как получить данные по наименьшим значениям
В ситуации, когда вы хотите получить некоторые данные, связанные с наименьшим значением, используйте классическую комбинацию ИНДЕКС ПОИСКПОЗ в сочетании с НАИМЕНЬШИЙ для поиска искомого:
ИНДЕКС( возвращаемый_массив ; ПОИСКПОЗ(НАИМЕНЬШИЙ( просматриваемый_массив ; n ), просматриваемый_массив ; 0))
Где:
- Возвращаемый_массив — это диапазон, из которого извлекаются связанные данные.
- Просматриваемый_массив — это диапазон, в котором выполняется поиск n-го наименьшего.
- n — позиция наименьшего интересующего значения.
Например, чтобы получить имена студентов с наименьшими баллами, формула может выглядеть так:
=ИНДЕКС($A$2:$A$10; ПОИСКПОЗ(НАИМЕНЬШИЙ($B$2:$B$10; D3); $B$2:$B$10; 0))
Где A2:A10 — имена студентов, B2:B10 — баллы, а D3:D5 — позиция от самого минимальнгого числа.
Скопируйте формулу в ячейки ниже (E4 и E5), и вы получите имена студентов с самыми низкими баллами:
Примечание: Это решение отлично работает для набора данных, в котором нет дубликатов. Однако два или более повторяющихся значения в числовом столбце могут создать путаницу, что приведет к неправильным результатам.
Поэтому рекомендуется использовать чуть более сложную формулу:
=ИНДЕКС($A$2:$A$10; ПОИСКПОЗ(1; ($B$2:$B$10=НАИМЕНЬШИЙ($B$2:$B$10; D3)) * (СЧЁТЕСЛИ(E$2:E2; $A$2:$A$10)=0); 0))
Теперь мы можем видеть имена всех студентов, которые набрали одинаковое количество баллов:
В Excel 365 и Excel 2021 эта задача может быть выполнена с помощью новых динамических функций массива. Помимо того, что этот подход намного проще, он автоматически решает проблему дубликатов.
=СОРТ(ФИЛЬТР(A2:B10; B2:B10<=НАИМЕНЬШИЙ(B2:B10; 3)); 2; 1)
При помощи функции ФИЛЬТР мы получаем имена и баллы тех, у кого балл не выше, чем третий с конца. Функция СОРТ упорядочивает этот список по возрастанию баллов.
Сортировать числа от наименьшего к наибольшему с помощью формулы
Я думаю, что все знают, как упорядочивать числа с помощью функции сортировки Excel . Но гораздо удобнее выполнять сортировку с помощью формулы. Пользователи Excel 365 могут сделать это простым способом с помощью новой функции СОРТ. В Excel 2019, 2016 и более ранних версиях СОРТ, увы, не работает.
И тут как раз нам может помочь формула НАИМЕНЬШИЙ.
Как и в примерах выше, мы используем функцию ЧСТРОК с динамической ссылкой на диапазон для увеличения k на 1 в каждой строке, куда копируется формула:
=НАИМЕНЬШИЙ($A$2:$A$10; ЧСТРОК(A$2:A2))
Введите формулу в первую ячейку, а затем перетащите ее вниз на столько ячеек, сколько строк в исходном наборе данных (в этом примере C2:C10):
Совет. Для сортировки по убыванию используйте функцию НАИБОЛЬШИЙ вместо НАИМЕНЬШИЙ.
Формула НАИМЕНЬШИЙ для дат и времени
Формула НАИМЕНЬШИЙ в Excel может быть особенно полезной при работе с временными данными и датами. Она позволяет извлекать n-ю наименьшую дату из набора данных, что может быть полезным при анализе последовательностей событий, определении старейших записей или управлении временными рамками проектов.
Поскольку даты и время также являются числами (во внутренней системе Excel даты хранятся в виде последовательных чисел, а время — в виде десятичных дробей), функция может обрабатывать их без особых проблем.
Как вы можете видеть на скриншотах ниже, базовая формула, которую мы использовали для чисел, прекрасно работает и для дат и времени:
=НАИМЕНЬШИЙ($B$2:$B$10; D2)
Формула НАИМЕНЬШИЙ для поиска 3 самых ранних дат:
Формула НАИМЕНЬШИЙ для получения 3 самых коротких времени:
После ввода этой формулы и копирования ее вниз по столбцу, Excel вернёт первое, второе и третье наименьшее время из указанного диапазона. Такая возможность может быть полезна для оптимизации процесса планирования, когда нужно выявить промежутки времени, которые повторяются или анализировать минимальное время выполнения задач.
Использование функции НАИМЕНЬШИЙ с датами может быть полезным в ряде случаев. Например, при анализе временных данных можно легко определить ключевые события, такие как начало проектов или сроки выполнения задач. Для календарных событий функция поможет выявить дату ближайших встреч или мероприятий.
Эта функция также важна для анализа данных в динамике, где требуется найти самые ранние записи о клиентах, транзакциях или инцидентах. В контексте управления проектами, например, можно быстро определить ближайшие контрольные даты для оценки прогресса и своевременного выполнения задач.
Следующий пример показывает, как функция НАИМЕНЬШИЙ может помочь вам выполнить более конкретную задачу, связанную с датами.
Найти предыдущую дату, ближайшую к сегодняшней или указанной дате
В списке дат, предположим, вы хотите найти ближайшую дату перед указанной датой. Это можно сделать, используя НАИМЕНЬШИЙ в сочетании с функцией СЧЕТЕСЛИ .
При наличии списка дат в ячейках B2:B10 и целевой даты в ячейках E1 следующая формула вернет предыдущую дату, ближайшую к целевой дате:
=НАИМЕНЬШИЙ($B$2:$B$10; СЧЁТЕСЛИ($B$2:$B$10; "<"&$E$1))
Чтобы извлечь следующую после ближайшей дату, формула выглядит следующим образом:
=НАИМЕНЬШИЙ($B$2:$B$10;СЧЁТЕСЛИ($B$2:$B$10;"<"&$E$1)-1)
Чтобы найти дату в прошлом, ближайшую к сегодняшнему дню, используйте функцию СЕГОДНЯ для критериев СЧЕТЕСЛИ:
=НАИМЕНЬШИЙ($B$2:$B$10; СЧЁТЕСЛИ($B$2:$B$10; "<"&СЕГОДНЯ()))
Совет. Чтобы предотвратить ошибки в ситуации, когда дата, соответствующая вашим критериям, не найдена, вы можете обернуть функцию ЕСЛИОШИБКА вокруг вашей формулы, например:
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ($B$2:$B$10; СЧЁТЕСЛИ($B$2:$B$10; "<"&$E$1));"Дата не найдена")
Наименьшие числа с условием
Пытаетесь получить нижнее значение на основе одного или нескольких условий? Просто используйте функцию НАИМЕНЬШИЙ вместе с ЕСЛИ.
Чтобы получить n-е наименьшее число, соответствующее определенным условиям, можно использовать следующую общую формулу:
{=НАИМЕНЬШИЙ(ЕСЛИ( диапазон_критериев = критерий ; значения ), n )}
Где n — это 1- е , 2 -е , 3 -е и т. д. наименьшее возвращаемое значение.
Для корректной работы формулы ее следует ввести как формулу массива, нажав Ctrl + Shift + Enter одновременно. Когда вы это сделаете, Excel заключит формулу в фигурные скобки. В Excel 365 это также работает как обычная формула благодаря поддержке динамических массивов.
Предположим, нам нужно получить самые низкие баллы тестирования для двух групп студентов. Используем такую формулу:
=НАИМЕНЬШИЙ(ЕСЛИ($B$2:$B$10=F$2; $C$2:$C$10); $E3)
Результат вы видите на скриншоте ниже.
Если структура вашего рабочего листа не предусматривает создание дополнительного столбца с номерами, вы можете сгенерировать их непосредственно в формуле, используя функцию СТРОКА (как мы делали это в предыдущих примерах):
=НАИМЕНЬШИЙ(ЕСЛИ($B$2:$B$10=F$2; $C$2:$C$10); СТРОКА(A1:A3))
В этом случае вы получите в столбце сразу 3 наименьших значения для выбранной группы.
В случае, если n-ое наименьшее значение с заданными условиями не найдено, формула вернет ошибку #ЧИСЛО. Чтобы перехватить эту ошибку и заменить ее любым подходящим символом («-» в нашем случае), можно использовать функцию Excel ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ($B$2:$B$10=F$2; $C$2:$C$10); $E3);"-")
Как игнорировать нули при поиске наименьших значений
Предположим, некоторые студенты пропустили тест и имеют оценку 0. Если использовать формулы, которые мы рассматривали выше, именно они и будут показаны нам как самые маленькие величины набоанных баллов.
Чтобы игнорировать нули при поиске наименьшего числа, используйте такое условие:
=НАИМЕНЬШИЙ(ЕСЛИ($B$2:$B$10<>0;$B$2:$B$10); D3)
Этот же подход вы можете использовать в формулах с несколькими условиями, о которых речь пойдет ниже.
Наименьший с несколькими условиями
Чтобы найти наименьшее значение с несколькими условиями, изменим предыдущую формулу и применим умножение условий:
{=НАИМЕНЬШИЙ(ЕСЛИ( (диапазон_критериев1 = критерий1) * (диапазон_критериев2 = критерий2); значения ), k )}
Обратите внимание, что эта формула также является формулами массива, поэтому не забудьте нажать Ctrl + Shift + Enter, чтобы правильно ее записать. В Excel 365 она будет работать как обычная формула.
Чтобы проверить работу этой формулы, расширим нашу таблицу с условными данными тестирования студентов и добавим туда еще один столбец – Дисциплина.
=НАИМЕНЬШИЙ(ЕСЛИ(($B$2:$B$15=G$1)*($C$2:$C$15=G$2); $D$2:$D$15); $F8)
Думаю, из скриншота ниже будет вполне понятно, как это работает:
Эту формулу вы копируете вниз по столбцу.
Или же при помощи функции СТРОКА получаем сразу 3 наименьших:
=НАИМЕНЬШИЙ(ЕСЛИ(($B$2:$B$15=G$1)*($C$2:$C$15=G$2); $D$2:$D$15); СТРОКА(A1:A3))
В Excel365 и 2021 эта задача может быть решена при помощи функции ФИЛЬТР:
=НАИМЕНЬШИЙ(ФИЛЬТР($D$2:$D$15;($B$2:$B$15=G$1)*($C$2:$C$15=G$2)); $F8)
Здесь мы создаем массив значений, которые соответствуют нескольким условиям, и затем из них выбираем самое минимальное.
Наименьший с одним из нескольких условий
Предыдущий пример показывает, как найти наименьшие значения на основе нескольких критериев с использованием логики И, т. е. когда все условия выполнены. Чтобы получить данные, которые удовлетворяют любому из условий, вам нужно построить формулу НАИМЕНЬШИЙ+ЕСЛИ с логикой ИЛИ. Для этого сложите критерии вместо того, чтобы перемножать их.
В общем виде это выглядит так:
{=НАИМЕНЬШИЙ(ЕСЛИ( (диапазон_критериев1 = критерий1) + (диапазон_критериев2 = критерий2); значения ), k )}
В качестве примера давайте выберем самые низкие баллы теста по двум разным предметам, скажем, математика и экономика.
Если дисциплины указаны в столбце С, баллы – в столбце D, а названия нужных дисциплин – в G1 и G2, то вот формула, которая выберет самые малые числа по одному из двух условий:
=НАИМЕНЬШИЙ(ЕСЛИ(($C$2:$C$15=G$1)+($C$2:$C$15=G$2); $D$2:$D$15); $F8)
Как выделить наименьшие значения в Excel
Чтобы выделить наименьшие значения в таблице с помощью условного форматирования Excel , вы можете использовать встроенную опцию Правила отбора первых и последних значений или настроить собственное правило при помощи формулы. Первый метод быстрее и проще в применении, а второй обеспечивает больше контроля и гибкости. Рассмотрим это пошагово:
- Выберите диапазон, в котором вы хотите выделить последние значения. В нашем случае числа находятся в диапазоне B2:B10, поэтому мы выбираем его. Если вы хотите выделить целые строки, выберите A2:B10.
- На вкладке Главная в группе Стили нажмите Условное форматирование > Создать правило.
- В диалоговом окне «Новое правило форматирования» выберите « Использовать формулу для определения форматируемых ячеек».
- В поле Форматировать значения, для которых эта формула является истинной , введите формулу, например:
=B2<=НАИМЕНЬШИЙ($B$2:$B$10; 3)
Где B2 — первая ячейка числового диапазона, который необходимо проверить, $B$2:$B$10 — весь диапазон, а 3 — n самых минимальных значений, которые необходимо выделить.
В вашей формуле обратите внимание на типы ссылок: начальная ячейка — это относительная ссылка (B2), а диапазон — это абсолютная ссылка ($B$2:$B$10).
- Нажмите кнопку «Формат» и выберите любой понравившийся вам формат.
- Дважды нажмите кнопку «ОК», чтобы закрыть оба диалоговых окна.
Готово! Наименьшие 3 числа в столбце B выделены:
Более подробную информацию можно найти в статье Как изменить цвет ячейки в зависимости от значения?
Сравнение функции НАИМЕНЬШИЙ с другими функциями поиска
Функция НАИМЕНЬШИЙ в Excel предоставляет эффективный способ нахождения n-го наименьшего значения в наборе данных. Однако важно понимать, чем она отличается от других, схожих функций, таких как МИН и НАИБОЛЬШИЙ, и в каких случаях каждая из них наиболее полезна.
Функция МИН – одна из самых базовых функций Excel, используемых для поиска самого маленького значения в диапазоне данных. Она особенно эффективна при необходимости быстрого определения минимального значения без учета порядка остальных данных. Однако, в отличие от функции НАИМЕНЬШИЙ, МИН не позволяет выбрать второе, третье или n-ое минимальное, что может быть существенным ограничением в более сложных анализах данных.
Функция НАИБОЛЬШИЙ, аналогичная по своей структуре функции НАИМЕНЬШИЙ, позволяет найти n-ое по величине значение в наборе данных, но не находит минимальные. Это полезно для задач, требующих определения позиций лидеров или топовых значений, но не так применимо, если основной целью является анализ самых мелких чисел. Как и НАИМЕНЬШИЙ, НАИБОЛЬШИЙ предоставляет гибкость выбора позиции, в отличие от МИН и МАКС.
Кроме того, есть функция МАКС, работающая аналогично функции МИН, но направленная на поиск самого большого числа. МАКС удобна для определения самих верхних значений, но, как и МИН, не позволяет более тонкого анализа, связанного с поиском вторых, третьих или последующих величин. Для глубокого анализа данных могут отсутствовать дополнительные возможности, которые может предоставить НАИМЕНЬШИЙ или НАИБОЛЬШИЙ.
Таким образом, каждая из этих функций имеет свои преимущества и ограничения. МИН и МАКС идеальны для простых задач, требующих нахождения первого наименьшего или наибольшего значения. Функции НАИМЕНЬШИЙ и НАИБОЛЬШИЙ предоставляют более расширенные возможности для анализа при необходимости выбора определённых позиций в наборе данных. Выбор функции в конечном итоге зависит от вашей конкретной задачи и схемы анализа данных.
Почему не работает
Как вы только что увидели из наших примеров, использовать функцию НАИМЕНЬШИЙ в Excel довольно просто, и у вас вряд ли возникнут с ней какие-либо трудности. Если ваша формула не работает, скорее всего, это будет ошибка #ЧИСЛО!, которая может возникнуть по следующим причинам:
- Массив пуст или не содержит ни одного числа. Обратите внимание, что функция исправно работает только с числами или датами. Если в массиве присутствуют текст или пустоты, это может повлиять на результат.
- Значение n меньше нуля (глупая опечатка может стоить вам кучи хлопот) или оно превышает количество данных в массиве.
В заключение, функция НАИМЕНЬШИЙ в Excel представляет собой мощный инструмент для осуществления анализа данных, позволяя пользователям быстро и эффективно находить n-е наименьшее число, дату или время. Она значительно упрощает процессы анализа данных, предоставляя точные результаты, необходимые для принятия обоснованных решений.
Мы рассмотрели основные методы использования функции НАИМЕНЬШИЙ, подчеркнув ее важность в различных сценариях, будь то аналитика больших наборов данных или простые вычисления в повседневной работе.
Если вы знаете какие-либо другие примеры, как еще можно найти наименьшее значение в Excel, вы можете поделиться ими в комментариях.
