Функция в excel наименьший

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

Функция НАИМЕНЬШИЙ (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 с ошибками

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

Источник: excelhack.ru

Анализ топовых значений функциями НАИБОЛЬШИЙ и НАИМЕНЬШИЙ

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

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

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

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

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

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

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

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

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

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

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

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

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

Только числа

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

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

Читайте также:  Excel с функцией впр в excel

Источник: www.planetaexcel.ru

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

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  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 – ложь.

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

Источник: exceltable.com

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ Excel

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

Описание функции НАИМЕНЬШИЙ

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

Массив – массив или диапазон числовых данных, для которых определяется k-ое наименьшее значение.

K – позиция искомого наименьшего значения (начиная с наименьшей) в массиве или диапазоне.

Обратите внимание, что функция НАИМЕНЬШИЙ вернет ошибку #ЧИСЛО!, если массив данных будет пустым или k будет больше, чем количество элементов в массиве. Если в диапазоне данных будут находиться текстовые значения, Excel будет игнорировать эти данные.

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

Рассмотрим на примере, как функция возвращает 1-й, 2-й, 3-й, 4-й и 5-й наименьший элемент из диапазона данных A1:A5.

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

Обратите внимание, что функция со значением параметра k=1 дает тот же результат, что и функция МИН. И наоборот, если мы укажем значение k равным количеству элементов в массиве, функция будет работать аналогично функции МАКС.

Сумма первых трех наибольших значений

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

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

Здесь мы указали Excel, что нам необходимо вернуть первые три наибольших значения <1;2;3>, из диапазона A1:A5 и просуммировать их.

Фигурные скобки <> обычно используются в формулах массивов, тем не менее, в данном случае вам не нужно использовать сочетания клавиш Shift+Enter, чтобы указать Excel, что это формула массива. А просто вводить их так же, как и любые символы в формулах.

Вам также могут быть интересны следующие статьи

4 комментария

Добрый день! Проблема с использованием данной функции. Сортирует от большего к меньшему массив данных, но не учитывает отрицательные значения (после нуля; сортирую для дашборда). Что делаю неверно? Заранее большое спасибо!

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

Добрый день!
Не улавливаю как формулу построить. Нужно в таблице по условию (М или Ж) вывести рейтинг с ФИО и результатом (документ прилагаю, нужно заполнить желтую область).
Как то вывела сортировку по значению, но как к нему подставить ФИО? И догадываюсь, что сортировку значений можно сделать гораздо проще.
Буду благодарна за помощь.

Источник: exceltip.ru

Функция в excel наименьший

Информация о сайте

Инструменты и настройки

Excel Windows
и
Excel Macintosh

Вопросы и решения

Работа и общение

Работа форума и сайта

Функции листа Excel

= Мир MS Excel/Статьи об Excel

  • 1
  • 2
  • 3
  • Приёмы работы с книгами, листами, диапазонами, ячейками [6]
    Приёмы работы с формулами [13]
    Настройки Excel [3]
    Инструменты Excel [4]
    Интеграция Excel с другими приложениями [4]
    Форматирование [1]
    Выпадающие списки [2]
    Примечания [1]
    Сводные таблицы [1]
    Гиперссылки [1]
    Excel и интернет [1]
    Excel для Windows и Excel для Mac OS [2]

    Исходные данные
    Исходными данными может быть любой числовой набор данных, таких как горизонтальный или вертикальный диапазон, двумерный массив или даже не смежные диапазоны или отдельно заполненные ячейки. Для удобства я взял одномерный вертикальный массив А1:А5

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

    1. Решение с дополнительным столбцом

    В В1 напишите формулу

    и скопируйте её на диапазон В2:В5. Эта промежуточная формула в дополнительном столбце будет “убирать” из исходных данных нули, заменяя их на пустую строку “”

    В С1 напишите формулу

    Она и вернёт искомый результат – число 3

    Если в диапазоне есть отрицательные числа, то вместо формулы

    Как это работает: Вот эта часть формулы A1:A5<>0 формирует массив , после чего применяя функцию ЕСЛИ() мы преобразуем массив в следующий: . Так как функция МИН() игнорирует текстовые и логические значения, то в итоге получаем искомое – число 3

    Если в диапазоне есть отрицательные числа, то вместо формулы


    Как это работает: С помощью функции СЧЁТЕСЛИ мы подсчитываем количество нулей в диапазоне и прибавив к этому количеству единицу мы возвращаем первое наименьшее число более нуля с помощью функции НАИМЕНЬШИЙ

    Если в диапазоне есть отрицательные числа, то вместо формулы

    Источник: www.excelworld.ru

    Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

    Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

    Функция ЕСЛИ в Excel

    Функция имеет следующий синтаксис.

    ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

    лог_выражение – это проверяемое условие. Например, A2 30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

    Однако это был демонстрационный пример. Чаще формулу Эксель ЕСЛИ используют для более сложных проверок. Допустим, есть средненедельные продажи товаров и их остатки на текущий момент. Закупщику нужно сделать прогноз остатков через 2 недели. Для этого нужно от текущих запасов отнять удвоенные средненедельные продажи.

    Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

    В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

    Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться. Заинтересованным рекомендую прочитать статью о том, как рассчитать максимальное и минимальное значение по условию. Правда, расчет в той статье более не актуален, т.к. в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.

    Формула ЕСЛИ в Excel – примеры нескольких условий

    Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

    Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

    Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2 =1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.

    Теперь вы знаете, как пользоваться функцией ЕСЛИ в Excel, а также ее более современным вариантом для множества условий ЕСЛИМН.


    Источник: statanaliz.info