Функция наибольший в excel

Функция НАИБОЛЬШИЙ() в EXCEL

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

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

Если n — количество значений в массиве , то формула =НАИБОЛЬШИЙ(массив;1) вернет наибольшее (максимальное) значение, а =НАИБОЛЬШИЙ(массив;n) — наименьшее (минимальное). Т.е. формула =НАИБОЛЬШИЙ(массив;1) эквивалентна =МАКС(массив) , а =НАИБОЛЬШИЙ(массив;n) эквивалентна =МИН(массив)

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

Значение ошибки в ячейке приводит к ошибке в формуле. Прежде чем применять функцию НАИБОЛЬШИЙ () – обработайте ошибку, например с помощью функции ЕСЛИОШИБКА() .

Если в массиве нет ни одного числового значения, то функция вернет значение ошибки #ЧИСЛО!, что выгодно ее отличает от функции МАКС() , возвращающую в этом случае 0!

Значение числа в текстовом формате игнорируется функцией НАИБОЛЬШИЙ () (см. столбец Е на рисунке выше). Перед нахождением наибольшего значения можно попытаться преобразовать все значения в числовой формат. Это можно сделать формулой массива = НАИБОЛЬШИЙ(ЕСЛИ(ЕЧИСЛО(E5:E9+0);E5:E9+0;””);1)

Необходимо помнить особенность функции НАИБОЛЬШИЙ() при работе со списками чисел, среди которых имеются повторы. Например, если имеется исходный массив <1;2;3; 6 ;6;7>, то третьим наибольшим (по версии функции НАИБОЛЬШИЙ() ) будет считаться 6, а не 3. Все правильно и логично, но иногда об этом забывают. С человеческой точки зрения третьим наибольшим будет все-таки, наверное, 3 (т.е. повторы не учитываются).

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

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

Сумма 3-х наибольших

С помощью нестандартной записи второго аргумента можно расширить возможности функции НАИБОЛЬШИЙ() . Например, найдем сумму 3-х наибольших значений из диапазона A5:A9 = СУММ(НАИБОЛЬШИЙ(A5:A9;<1;2;3>))

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

Аналогично можно найти, например, среднее 2-х наибольших: =СРЗНАЧ(НАИБОЛЬШИЙ(A5:A9;<1;2>))

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

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

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

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

Функция LARGE (НАИБОЛЬШИЙ) в Excel. Как использовать?

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

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

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

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

Синтаксис

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

=НАИБОЛЬШИЙ(массив;k) – русская версия

Аргументы функции

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

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

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

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

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

На примере выше в диапазоне данных A2:A4 у нас есть числа “1”,”8″,”9″. Для того чтобы вычислить наибольшее число из этого диапазона нам поможет формула:

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

=НАИБОЛЬШИЙ(A2:A4;1) – русская версия

Так как аргумент “k” равен “1”, функция вернет наибольшее число “9”.

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

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

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

=НАИБОЛЬШИЙ(A2:A4;2) – русская версия

Так как значение аргумента “k” мы указали “2”, то функция вернет второе по величине значение из диапазона – “8”.

Пример 3. Использование функции LARGE (НАИБОЛЬШИЙ) с пустыми ячейками

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

Как показано на примере выше, указав диапазон данных для вычисления “ A2:A5″ , функция без проблем выдает наибольшее значение “9”.

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

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

Пример 5. Использование функции LARGE (НАИБОЛЬШИЙ) в Excel с дублированными данными

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

Читайте также:  Если функция в эксель

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Только числа

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

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

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

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 примеры

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

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

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

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

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

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

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

Источник: exceltip.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 сотрудников, включая генерального директора и его заместителя. Один из сотрудников предположил, что оба руководителя получают в целом больше, чем все остальные сотрудники. Необходимо определить, является это предположение истиной или ложью.

Читайте также:  В excel функция разделить

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

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

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

  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

Функции 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