Microclimate.su

IT Новости
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как найти наименьшее число в 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 с ошибками

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

Функция НАИМЕНЬШИЙ() в EXCEL

Массив — ссылка на диапазон ячеек, содержащие данные, для которых определяется k-ое наименьшее значение. Также возможен ввод в качестве аргумента массива констант , например, = НАИМЕНЬШИЙ(<10:20:30:40:50>;1) .Формула вернет значение 10 (1-ое нименьшее).

k — позиция (начиная с наименьшей) в массиве или диапазоне ячеек. Если k НАИМЕНЬШИЙ () возвращает значение ошибки #ЧИСЛО!

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

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

Как видно из таблицы (столбец D), значение ошибки приводит к ошибке в формуле. Если такая ситуация возможна, то создайте с помощью формулы = ЕСЛИОШИБКА (A1;»ф») новый диапазон, которая заменит ошибку на текст, который игнорируется функцией. Конечно, нужно также изменить ссылку на диапазон в соседнем столбце.

Читать еще:  Vlc media player потоковое видео

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

Если в массиве нет ни одного числового значения, то функция вернет значение ошибки #ЧИСЛО!, что выгодно ее отличает от функции МИН() , возвращающую в этом случае 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 последние формулы даже не обязательно вводить как формулы массива .

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

Функция НАИМЕНЬШИЙ () является достаточно часто используемой, т.к. она позволяет упорядочивать числовые массивы. Ее можно, например, использовать для сортировки списков и таблиц .

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

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

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

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

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

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

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

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



Примеры работы в 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 – ложь.

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

Функции МАКС и МИН в Excel по условию

Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.

Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.

Пусть имеются данные

Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:

Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.

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

Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.

Указанное выражение отбирает только те значения, для которых название группы совпадает с условием в ячейке D2. Вот, как это видит Excel

На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.

Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.

Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.

Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)

В MS Excel добавили новые статистические функции — МАКСЕСЛИ и МИНЕСЛИ. Обе функции имеют возможность учитывать несколько условий и некоторое время в их названиях в конце были буквы -МН. Потом убрали, хотя в скриншотах ниже используется вариант названий с -МН.

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

Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.

Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.

Ссылка на основную публикацию
Adblock
detector