Меню

Excel как найти наибольшее число в строке

Функция МАКС в Excel: как найти максимальное значение

В этой статье объясняется функция МАКС с множеством примеров формул, которые показывают, как найти наибольшее значение в Excel и выделить наибольшее число на листе.

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

Функция МАКС

Функция МАКС в Excel возвращает наибольшее значение в указанном вами наборе данных.

Синтаксис у неё следующий:

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

Число1 является обязательным, число2 и последующие аргументы необязательны.

Чтобы создать самую простую формулу, чтобы выбрать максимальное значение в таблице Excel, вы можете вводить числа непосредственно в список аргументов, например:

На практике это – очень редкий случай. По большей части вы будете иметь дело с диапазонами и ячейками.

Самый быстрый способ найти наибольшее значение в диапазоне, – это:

  1. В ячейке введите = МАКС(
  2. Выберите диапазон чисел с помощью мыши.
  3. Введите закрывающую скобку.
  4. Жмём Enter для завершения.

Например, чтобы найти максимальное значение в диапазоне A1: A6, формула будет выглядеть следующим образом:

Если ваши числа находятся в непрерывном диапазоне (как в этом примере), вы можете заставить Excel автоматически создать формулу МАКС. Вот как:

  1. Выделите ячейки со своими числами.
  2. На вкладке «Главная» в группе «Форматы» нажмите «Автосумма» и выберите «Максимум» из раскрывающегося списка.

5 фактов, которые нужно знать о функции МАКС –

  1. В текущих версиях Excel она может обрабатывать до 255 аргументов.
  2. Если аргументы не содержат ни одного числа, возвращается ноль.
  3. Если аргументы содержат одно или несколько значений ошибки, возвращается ошибка.
  4. Пустые ячейки игнорируются.
  5. Обрабатываются логические значения и текстовые представления чисел, предоставленные непосредственно в списке аргументов (ИСТИНА приравнивается к 1, ЛОЖЬ – к 0). Если используется ссылка на ячейку с логическим или текстовым значением, то они игнорируются.

Как найти в Excel наибольшее число – примеры.

Ниже мы покажем несколько стандартных вариантов использования функции МАКС.

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

Как выбрать максимальное значение в группе чисел.

Чтобы в Excel извлечь наибольшее число в группе чисел, передайте эту группу функции МАКС в качестве ссылки на диапазон. Диапазон может содержать любое количество строк и столбцов. Например, чтобы получить максимальное значение из C2: E7, используйте эту простую формулу:

Примечание. Если в вашей области поиска имеются пустые ячейки – не беда! Функция МАКС их просто проигнорирует и будет работать с имеющимися данными.

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

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

Наибольшее значение в несмежных ячейках или диапазонах Excel

Чтобы осуществить поиск в несмежных ячейках и диапазонах, необходимо включить в формулу ссылку на каждый отдельный адрес. Рассмотрим пошагово:

  1. Начните вводить функцию МАКС в ячейку.
  2. После того, как вы ввели открывающую скобку, нажмите и удерживайте Ctrl и выберите мышкой нужные ячейки и диапазоны.
  3. Отпустите Ctrl и введите закрывающую круглую скобку.
  4. Нажмите Enter .

Вы получите формулу максимального числа в Excel, подобную этому:

Как получить наибольшую (последнюю) дату в Excel

Во внутренней системе Excel даты – это не что иное, как порядковые номера дней, поэтому функция МАКС обрабатывает их без проблем.

Например, чтобы найти последнюю дату доставки из C2: C7, запишите обычную формулу, которую вы использовали бы для чисел:

Поскольку дата – это также число, всё работает замечательно.

Максимальное значение с условиями.

Довольно часто случается, что из большого объёма данных Excel нужно выбрать максимальное значение из какой-то его части. К примеру, найти самую большую партию какого-то определённого товара в огромном списке продаж. Или же найти наивысшую оценку по какому-то предмету среди большого набора данных оценок, набранных учащимися определенного класса по нескольким предметам в школе.

Если вы хотите получить максимальное значение Excel с учётом каких-то условий, то вы можете выбрать из нескольких предложенных ниже формул. Чтобы убедиться, что все они возвращают одинаковый результат, мы протестируем их на одном и том же наборе данных.

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

МАКС + ЕСЛИ

Если вы ищете решение, которое работает во всех версиях Excel, используйте функцию ЕСЛИ для проверки условия, а затем передайте полученный массив в функцию:

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

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

МАКС + СУММПРОИЗВ

Если вам не нравится использовать формулы массива в ваших листах, объедините МАКС с функцией СУММПРОИЗВ, которая умеет работать с массивами:

МАКСЕСЛИ

В Excel 2019 и Office 365 есть специальная функция МАКСЕСЛИ, которая предназначена для поиска наибольшего значения по 126 критериям.

В нашем случае используется только одно условие, поэтому формула очень проста:

На приведенном ниже скриншоте показаны все 3 формулы в действии:

Выбираем максимальное значение в Excel без учета нулей

Фактически, это разновидность условного МАКС, рассмотренного в предыдущем примере. Чтобы исключить нули, используйте логический оператор «не равно» и поместите выражение «<> 0» либо в критерий функции МАКСЕСЛИ, либо в конструкцию МАКС + ЕСЛИ.

Как вы понимаете, проверка этого условия имеет смысл только в случае наличия отрицательных чисел. Для положительных чисел эта проверка излишняя, потому что любое положительное число больше нуля.

Чтобы попробовать, давайте найдем самую маленькую скидку в диапазоне C2:C7. Поскольку все скидки представлены отрицательными числами, наименьшая скидка фактически является наибольшим значением.

Обязательно нажмите Ctrl + Shift + Enter , чтобы правильно заполнить эту формулу массива:

И вот что у нас получилось:

Как найти максимальное значение, игнорируя ошибки

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

В качестве обходного пути можно использовать МАКС вместе с ЕОШИБКА. Учитывая, что вы ищете в диапазоне A1: B5, формула принимает следующую форму:

Чтобы упростить формулу, используйте функцию ЕСЛИОШИБКА вместо комбинации ЕСЛИ + ЕОШИБКА. Это также сделает логику более очевидной — если есть ошибка в A1:B6, замените ее пустой строкой (»), а затем вычислите самое большое значение в диапазоне:

Неудобство состоит в том, что нужно не забывать нажимать Ctrl + Shift + Enter , потому что это работает только как формула массива.

В Excel 2019 и Office 365 функция МАКСЕСЛИ может быть хорошим решением при условии, что ваш набор данных содержит хотя бы одно положительное число или нулевое значение:

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

Также идеально подходит функция АГРЕГАТ, которая может выполнять ряд операций и игнорировать значения ошибок:

Число 4 в первом аргументе предписывает выполнить поиск максимального числа, шестёрка во втором аргументе – это параметр «игнорировать ошибки», а A1: B6 — ваш целевой диапазон.

Все формулы возвращают одинаковый результат:

Как найти абсолютное наибольшее значение в Excel

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

Первая идея, которая приходит в голову, — получить абсолютные значения всех чисел в диапазоне с помощью функции ABS и передать их в МАКС:

Это формула массива, поэтому не забудьте подтвердить ее с помощью Ctrl + Shift + Enter . Еще одно предостережение: он работает только с числами и приводит к ошибке в случае нечисловых данных.

Не нравится это решение? Тогда давайте построим что-нибудь более жизнеспособное 🙂

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

С исходными данными в A1: B5 расчёты выглядят следующим образом.

Формула массива (заполняется с помощью Ctrl + Shift + Enter ):

Обычная формула (заполняется клавишей Enter):

На скриншоте ниже показаны результаты:

Вернуть максимальное абсолютное значение с сохранением знака

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

Предполагая, что числа находятся в ячейках A1: B5, вот выражение, которое следует использовать:

Сложная на первый взгляд логика довольно проста. Сначала вы находите наибольшее и наименьшее числа в диапазоне и сравниваете их абсолютные значения. Если абсолютное (без учёта знака) максимальное значение больше абсолютного минимального значения, возвращается это наиболшее число, иначе – наименьшее число.

Поскольку формула возвращает исходное, а не абсолютное значение, информация о знаке сохраняется (см. скриншот выше).

Максимальное значение применительно к тексту.

Предположим, у нас есть список имен, и нам нужно вычислить их максимальную длину, то есть найти наибольшее количество символов в ячейке Excel.

Здесь мы должны применить функцию ДЛСТР для вычисления длины имени.

А затем применим функцию МАКС, чтобы узнать максимальную длину ФИО.

А можно обойтись без дополнительного столбца и вычислить максимальную длину текстового значения в одной ячейке. Для этого нужно объединить эти формулы, вложив ДЛСТР в МАКС:

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

Вычислить наибольшую разницу в сумме.

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

Ниже представлена ​​таблица Excel с небольшой выборкой данных за 4 месяца.

Если решать проблему «в лоб», то можно использовать функцию ЕСЛИ со множеством условий:

=ЕСЛИ(И((МАКС(B2:C2)-МИН(B2:C2))>(МАКС(C2:D2)-МИН(C2:D2)), (МАКС(B2:C2)-МИН(B2:C2))>(МАКС(D2:E2)-МИН(D2:E2))), МАКС(B2:C2)-МИН(B2:C2), ЕСЛИ((МАКС(C2:D2)-МИН(C2:D2))>(МАКС(D2:E2)-МИН(D2:E2)), МАКС(C2:D2)-МИН(C2:D2),МАКС(D2:E2)-МИН(D2:E2)))

Но при помощи функции МАКС можно значительно упростить расчет и не пользоваться такой длинной и сложной конструкцией.

Используя функцию ABS, мы будем искать максимальное изменение вне зависимости от знака. Попробуйте заменить большую формулу этой:

Как вы можете видеть в таблице ниже, этот вариант намного проще и возвращает те же результаты.

Как найти адрес ячейки Excel с максимальным числом.

Есть ли решение, которое сообщит адрес ячейки с наибольшим числом, а не само число? То есть, если A4 равно 9 и это значение является самым большим, как мне составить формулу, которая возвратит координаты A4 вместо 9?

Вы можете использовать функции ЯЧЕЙКА , ИНДЕКС и ПОИСКПОЗ в сочетании с функцией МАКС, чтобы вернуть адрес ячейки с наибольшим значением.

Например, если вы хотите найти ячейку с наивысшим значением в столбце от А1 до А7, вы можете использовать следующий расчёт:

Получаем результат в виде абсолютной ссылки $А$4, которая указывает на самое большое число из этого диапазона.

Однако учтите, что это выражение работает только с одним столбцом.

А вот еще альтернативные варианты поиска адреса наибольшего числа по столбцу, из которых вы можете выбрать наиболее подходящий для себя:

Обычная формула, в которой явным образом нужно указать столбец:

На скриншоте вы видите, что все они возвращают один и тот же адрес:

И ещё одна формула массива для вывода адреса, которая работает с диапазоном из нескольких строк и столбцов:

В данном случае, как видите, мы ищем адрес наибольшего числа в двумерном диапазоне C2:E7.

Напомню, что формула массива вводится с использованием комбинации клавиш Ctrl + Shift + Enter .

Как выделить наибольшее значение в Excel

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

Выделите максимальное число в диапазоне Excel

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

  1. Выберите диапазон чисел (в нашем случае C2: E8).
  2. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование»> «Новое правило».
  3. В диалоговом окне «Новое правило форматирования» выберите «Форматировать только первые или последние значения».
  4. На нижней панели выберите «первым» из раскрывающегося списка и введите 1 в поле рядом с ним (это означает, что вы хотите выделить только одну ячейку, содержащую наибольшее значение).
  5. Нажмите кнопку «Формат» и выберите стиль оформления.
  6. Дважды щелкните OK, чтобы закрыть оба окна.

Как выделить цветом максимальное число в каждой строке

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

  1. Выберите все строки, которые вы хотите проверить (C3:E8).
  2. Кликните Новое правило >Использовать формулу.
  3. В поле Форматировать значения, запишите:

Где C3 — крайняя верхняя левая ячейка, а $C3:$E3 — координаты первой строки. Чтобы правило работало, обязательно зафиксируйте координаты столбца в диапазоне знаком $.

  1. Нажмите кнопку «Формат» и выберите нужный формат.
  2. Дважды щелкните ОК.

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

Почему не получается найти наибольшее число?

МАКС — одна из самых простых в использовании функций Excel. Если вопреки всем ожиданиям она не работает, то, скорее всего, это одна из следующих проблем:

МАКС возвращает ноль

Если обычная формула МАКС возвращает 0, даже если в указанном диапазоне есть числа явно большие, скорее всего, эти числа отформатированы как текст.

Это особенно актуально, когда вы запускаете функцию МАКС для данных, получаемых другими формулами или же импортированных из других программ.

Вы можете проверить это с помощью функции ЕЧИСЛО, например:

Если приведенная выше формула возвращает ЛОЖЬ, то значение в A1 не является числовым. Это означает, что вы должны устранять неполадки с исходными данными, а не с формулой.

МАКС возвращает значение #Н/Д, #ЗНАЧ или другую ошибку.

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

Вот как можно найти максимальное значение в Excel. Благодарю вас за чтение!

Источник статьи: http://mister-office.ru/funktsii-excel/max-highest-value.html

Наибольший по условию в EXCEL

history 3 апреля 2013 г.

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

Пусть имеется таблица с двумя столбцами: текстовым и числовым (см. файл примера ).

Для удобства создадим два именованных диапазона : Текст ( A 3: A 27 ) и Числа ( B3:B27 ).

СОВЕТ: Создание формул для определения минимального и максимального значения с учетом условий рассмотрено в статье Максимальный и Минимальный по условию в MS EXCEL .

Определение наибольшего значения с единственным критерием

Найдем с помощью формулы массива второе наибольшее значение среди тех чисел, которые соответствуют значению Текст2 (находится в ячейке Е6 ) :

После набора формулы не забудьте вместо ENTER нажать CTRL+SHIFT+ENTER .

Чтобы разобраться в работе формулы, выделите в Строке формул выражение ЕСЛИ(Текст=E6;Числа) и нажмите клавишу F9 . Выделенная часть формулы будет заменена на результат, т.е. на массив значений :

Значение ЛОЖЬ соответствует строкам, в которых в столбце Текст нет значения Текст2. В противном случае выводится само число. Т.к. функция НАИБОЛЬШИЙ() игнорирует текстовые значения и значения ЛОЖЬ и ИСТИНА, то 2-е наибольшее будет искаться только среди чисел -95; -66; -20; 0; 4; 9. Результат: 4.

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

Определение наименьшего значения с несколькими критериями

Теперь найдем 3-е наименьшее значение среди тех чисел, которые соответствуют сразу 2-м критериям.

Пусть имеется таблица с тремя столбцами: Название фрукта, Поставщик и Количество.

Таблицу критериев разместим правее таблицы с данными.

Найдем 3-е наименьшее значение среди чисел, находящихся в строках, для которых Название фрукта = Яблоко, а Поставщик = ООО Рога с помощью формулы массива :

После набора формулы не забудьте вместо ENTER нажать CTRL+SHIFT+ENTER .

Если ни одна запись в таблице не удовлетворяет одновременно двум критериям (Условие И), то формула возвращает значение ошибки #ЧИСЛО!

СОВЕТ: Создание формул с множественными критериями подробно рассмотрено в разделах Сложения и Подсчета значений .

Источник статьи: http://excel2.ru/articles/naibolshiy-po-usloviyu-v-ms-excel

Вычисление минимального, максимального и среднего значения в Microsoft Excel

В Microsoft Office Excel можно работать с цифрами и узнавать любое числовое значение. Этот табличный процессор справится практически со всеми расчётами. Он идеально подходит для бухгалтерского учёта. Для вычислений существуют специальные инструменты — формулы. Их можно применять к диапазону или к отдельным ячейкам. Чтобы узнать минимальную или максимальную цифру в группе клеток, необязательно искать их самостоятельно. Лучше воспользоваться предназначенными для этого опциями. Также полезно будет разобраться, как посчитать среднее значение в Excel.

Это особенно актуально в таблицах с большим объёмом данных. Если в столбце, например, указаны цены на продукцию торгового центра. И вам надо узнать, какой товар самый дешёвый. Если искать его «вручную», уйдёт очень много времени. Но в Экселе это можно сделать буквально за несколько кликов. Утилита также высчитывает среднее арифметическое. Ведь это две простые операции: сложение и деление.

В этой статье мы расскажем, как вычислить различные значения в Эксель

Максимальное и минимальное

Вот как найти максимальное значение в Excel:

  1. Поставьте курсор-ячейку в любое место.
  2. Перейдите в меню «Формулы».
  3. Нажмите «Вставить функцию».
  4. В списке выберите «МАКС». Или напишите это слово в поле «Поиск» и нажмите «Найти».
  5. В окне «Аргументы» введите адреса диапазона, максимальное значение которого вам нужно узнать. В Excel имена клеток состоят из буквы и цифры («B1», «F15», «W34»). А название диапазона — это первая и последняя ячейки, которые в него входят.
  6. Вместо адреса можно написать несколько чисел. Тогда система покажет самое большее из них.
  7. Нажмите «OK». В клетке, в которой стоял курсор, появится результат.

Следующий шаг — укажите диапазон значений

Теперь будет легче разобраться, как найти минимальное значение в Excel. Алгоритм действий полностью идентичен. Просто вместо «МАКС» выберите «МИН».

Среднее

Среднее арифметическое вычисляется так: сложить все цифры из множества и поделить на их количество. В Экселе можно посчитать суммы, узнать, сколько ячеек в строке и так далее. Но это слишком сложно и долго. Придётся использовать много разных функций. Держать в голове информацию. Или даже что-то записывать на листочек. Но можно упростить алгоритм.

Вот как найти среднее значение в Excel:

  1. Поставьте ячейку курсор в любое свободное место таблицы.
  2. Перейдите на вкладку «Формулы».
  3. Нажмите на «Вставить функцию».
  4. Выберите «СРЗНАЧ».
  5. Если этого пункта нет в списке, откройте его с помощью опции «Найти».
  6. В области «Число1» введите адрес диапазона. Или напишите несколько цифр в разных полях «Число2», «Число3».
  7. Нажмите «OK». В ячейке появится нужное значение.

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

Другие способы

Максимальное, минимальное и среднее можно узнать и другими способами.

  1. Найдите панель функций с обозначением «Fx». Она над основной рабочей областью таблицы.
  2. Поставьте курсор в любую ячейку.
  3. Введите в поле «Fx» аргумент. Он начинается со знака равенства. Потом идёт формула и адрес диапазона/клетки.
  4. Должно получиться что-то вроде «=МАКС(B8:B11)» (максимальное), «=МИН(F7:V11)» (минимальное), «=СРЗНАЧ(D14:W15)» (среднее).
  5. Кликните на «галочку» рядом с полем функций. Или просто нажмите Enter. В выделенной ячейке появится нужное значение.
  6. Формулу можно скопировать непосредственно в саму клетку. Эффект будет тот же.

Впишите диапазон и нажмите Enter

Найти и вычислить поможет Excel-инструмент «Автофункции».

  1. Поставьте курсор в ячейку.
  2. Перейдите в раздел «Формулы».
  3. Найдите кнопку, название которой начинается на «Авто». Это зависит от выбранной в Excel опции по умолчанию («Автосумма», «Авточисло», «Автосмещ», «Автоиндекс»).
  4. Нажмите на чёрную стрелочку под ней.
  5. Выберите «МИН» (минимальное значение), «МАКС» (максимальное) или «СРЗНАЧ» (среднее).
  6. В отмеченной клетке появится формула. Кликните на любую другую ячейку — она будет добавлена в функцию. «Растяните» рамку вокруг неё, чтобы охватить диапазон. Или щёлкайте по сетке с зажатой клавишей Ctrl, чтобы выделять по одному элементу.
  7. Когда закончите, нажмите Enter. Результат отобразится в клетке.

В Excel вычислить среднее значение достаточно легко. Не нужно складывать, а потом делить сумму. Для этого существует отдельная функция. Также можно найти минимум и максимум в множестве. Это намного легче, чем считать вручную или выискивать цифры в огромной таблице. Поэтому Эксель популярен во многих сферах деятельности, где требуется точность: бизнес, аудит, кадровое делопроизводство, финансы, торговля, математика, физика, астрономия, экономика, наука.

Источник статьи: http://nastroyvse.ru/programs/review/srednee-minimalnoe-i-maksimalnoe-znachenie-v-excel.html

Формула для выборки нескольких наибольших значений в Excel

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

Пример выборки нескольких наибольших значений в Excel

Ниже на рисунке представлены результаты турниру по боулингу. Участники в турнирной таблице должны быть отсортированы по возрастанию по имени. Сложно проверить, который из них является победителем. Допустим нам необходимо узнать, которые участники заняли первые 3 места и какие их результаты. Ниже указанная формула, которая возвращает третье наибольшее число со списка результатов:

В формуле использованной для поиска имени участника используется комбинация функций ИНДЕКС и ПИСКПОЗ:

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

Если исходный диапазон данных содержит одинаковые максимальные значения, тогда функция НАИБОЛЬШИЙ и НАИМЕНЬШИЙ возвращает тот самый результат для порядковых максимальных и минимальных значений (K и K+1). Например, если бы два участника получили одинаковый результат по 588 пунктов каждый, тогда формулы =НАИБОЛЬШИЙ($B$2:$B$13;1) и =НАИБОЛЬШИЙ($B$2:$B$13;2) возвращали бы одно и тоже число 558.

В ячейке E2 используется функция СТРОКА(A1) определяющая значение аргумента «К». Функция строка возвращает номер строки листа для ячейки, указанной в аргументе – в данном примере строка №1. Как второй аргумент функции НАИБОЛЬШИЙ можно было бы просто указать число 1, однако благодаря функции СТРОКА(A1) номер строки листа будет увеличиваться автоматически во время копирования формулы в очередные ячейки в низ от начальной. Ссылка на ячейку A1 является относительной, когда потом формула будет скопирована в ячейку E3, функция будет уже иметь следующий вид: СТРОКА(A2). С таким аргументом функция уже будет возвращать значение 2, а функция НАИБОЛЬШИЙ в ячейке E3 возвратит второе наибольшее значение из столбца «Результат» в исходном списке данных.

Формула ранжирования в Excel для выборки наибольших значений

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

Чтобы узнать какое место занял участник с именем «Волчок Геннадий» необходимо заполнить аргументы для функции РАНГ. В первой ячейке следует указать время текущего участника (ползучем исходное время относительной ссылкой из ячейки B2), а во втором аргументе указать абсолютную ссылку на диапазон просматриваемых исходных значений $B$2:$B$13. В третьем аргументе указывается порядок сортировки. В данном примере это число 1, так как на первом месте должно быть наименьше время, то есть по возрастанию. Если бы на первом месте должно было бы быть наибольшее значение просматриваемого исходного диапазона ячеек, в таком случае в третьем аргументе функции РАНГ следует указывать число 0.

В отличии от функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ функция РАНГ возвращает фактический порядковый номер для каждого значения. Другими словами, функция РАНГ возвращает номер места исходного значения в списке просматриваемого диапазона, который отсортирован с учетом последнего аргумента функции. Чтобы получить не текущий номер, а значение найденной определенной ячейки следует использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ, подобно как в случае с формулой, которая ищет имена участников.

Формула в ячейке F3 возвращает время участника, который занял третье место – выглядит следующим образом:

Если просматриваемый диапазон исходных значений содержит два или более одинаковых значения, функция РАНГ два раза подряд возвращает один и тот же результат. Например, если у двоих участников одно и тоже время результата 20:35, в таком случае функция РАНГ для обоих возвращает в результате вычислений число 1. Следующее большее очередное время в списке результатов получит третье место. Ни одно значение не получит в таком случае второго места, так как два одинаковы исходных значения занимают первое и второе место в ранжировании – одновременно:

Функция РАНГ.СР для ранжирования списка с повторяющимися значениями

В программе Excel версии 2010 и старше появились две новые функции ранжирования значений: РАНГ.РВ и РАНГ.СР, в которых по-разному решается проблема с одинаковыми значениями в диапазоне ячеек. Формула РАНГ.РВ – это аналог функции РАНГ, она возвращает тот же самый результат при таких же условиях с теми же самыми одинаковыми ячейками. А вот функция РАНГ.СР возвращает средний результат вычисления для всех одинаковых значений.

Допустим в таблице находятся результаты четырех участников с одинаковым вторым наименьшим показателем времени 21:38:

Для наилучшего времени в данной таблице функция РАНГ.СР естественно возвращает номер 1, а для четырех последующих наилучших показателей времени возвращено дробное значение 3,5. Эти четыре участника одновременно занимают места: 2, 3, 4 и 5, в итоге все места разделены между ними поровну, поэтому каждый участник получил по 3,5 балла. То есть 2+3+4+5/4=3,5.

Источник статьи: http://exceltable.com/formuly/formula-naibolshiy-excel

Поиск минимального или максимального значения по условию

В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF) , СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?

Предположим, нам нужно найти минимальную цену для каждого товара в базе данных по поставщикам:

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

Для будущего удобства, конвертируем исходный диапазон с ценами в «умную таблицу». Для этого выделите его и выберите на вкладке Главная — Форматировать как таблицу (Home — Format as Table) или нажмите Ctrl+T. Наша «поумневшая» таблица автоматически получит имя Таблица1, а к столбцам можно будет, соответственно, обращаться по их именам, используя выражения типа Таблица1[Товар] или Таблица1[Цена]. При желании, стандартное имя Таблица1 можно подкорректировать на вкладке Конструктор (Design) , которая появляется, если щелкнуть в любую ячейку нашей «умной» таблицы. Подробнее о таких таблицах и их скрытых возможностях можно почитать здесь.

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016

Начиная с версии Excel 2016 в наборе функции Microsoft Excel наконец появились функции, которые легко решают нашу задачу — это функции МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS) . Синтаксис этих функции очень похож на СУММЕСЛИМН (SUMIFS) :

=МИНЕСЛИ( Диапазон_чисел ; Диапазон_проверки1 ; Условие1 ; Диапазон_проверки2 ; Условие2 . )

  • Диапазон_чисел — диапазон с числами, из которых выбирается минимальное или максимальное
  • Диапазон_проверки — диапазон, который проверяется на выполнение условия
  • Условие — критерий отбора

Например, в нашем случае:

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

Способ 2. Формула массива

В английской версии это будет, соответственно =MIN(IF(Table1[Товар]=F4;Table1[Цена]))

Не забудьте после ввода этой формулы в первую зеленую ячейку G4 нажать не Enter , а Ctrl + Shift + Enter , чтобы ввести ее как формулу массива. Затем формулу можно скопировать на остальные товары в ячейки G5:G6.

Давайте разберем логику работы этой формулы поподробнее. Функция ЕСЛИ проверяет каждую ячейку массива из столбца Товар на предмет равенства текущему товару (Бумага). Если это так, то выдается соответствующее ему значение из столбца Цена. В противном случае – логическое значение ЛОЖЬ (FALSE) .

Таким образом внешняя функция МИН (MIN) выбирает минимальное не из всех значений цен, а только из тех, где товар был Бумага, т.к. ЛОЖЬ функцией МИН игнорируется. При желании, можно выделить мышью всю функцию ЕСЛИ(…) в строке формул

. и нажать на клавиатуре F9, чтобы наглядно увидеть тот самый результирующий массив, из которого потом функция МИН и выбирает минимальное значение:

Способ 3. Функция баз данных ДМИН

Этот вариант использует малоизвестную (и многими, к сожалению, недооцененную) функцию ДМИН (DMIN) из категории Работа с базой данных (Database) и требует небольшого изменения результирующей таблицы:

Как видите, зеленые ячейки с результатами транспонированы из столбца в строку и над ними добавлена мини-таблица (F4:H5) с условиями. Логика работы этой функции следующая:

  • База_данных — вся наша таблица вместе с заголовками.
  • Поле — название столбца из шапки таблицы, из которого выбирается минимальное значение.
  • Критерий — таблица с условиями отбора, состоящая (минимально) из двух ячеек: названия столбца, по которому идет проверка (Товар) и критерия (Бумага, Карандаши, Ручки).

Это обычная формула (не формула массива), т.е. можно вводить и использовать ее привычным образом. Кроме того, в той же категории можно найти функции БДСУММ (DSUM) , ДМАКС (DMAX) , БСЧЁТ (DCOUNT) , которые используются совершенно аналогично, но умеют находить не только минимум, но и сумму, максимум и количество значений по условию.

Способ 4. Сводная таблица

Если в исходной таблице очень много строк, но данные меняются не часто, то удобнее будет использовать сводную таблицу, т.к. формула массива и функция ДМИН могут сильно тормозить Excel.

Установите активную ячейку в любое место нашей умной таблицы и выберите на вкладке Вставка — Сводная таблица (Insert — Pivot Table) . В появившемся окне нажмите ОК:

В конструкторе сводной таблицы перетащите поле Товар в область строк, а Цену в область значений. Чтобы заставить сводную вычислять не сумму (или количество), а минимум щелкните правой кнопкой мыши по любому числу и выберите в контекстном меню команду Итоги по — Минимум:

Источник статьи: http://www.planetaexcel.ru/techniques/2/241/

Поиск минимального значения в excel

Поиск наименьшего или наибольшего числа в диапазоне

​Смотрите также​ЕСЛИ(A1=AM654;0;ЕСЛИ(A1=AM654;1;»ошибка «))​: Спасибо. То, что​ условием 1?​ есть если во​ соответственными формулами прекрасно​ формулу: =МАКС($B$2:$B$10)=B2​БСЧЁТ (DCOUNT)​). Если это так,​или​

Для ячеек, расположенных подряд в одной строке или одном столбце

​ надстройка мастера подстановок.​ следует включать в​ (только Excel 2007)​ вернут адрес значения​Недостаток функции​

​ минимальное значение из​​ столбцов;​​Предположим, что требуется определить,​​Два совершенно одинаковых​​ надо. ​Маугли​​ втором аргументе мы​ ​ справляется с данной​​Нажмите на кнопку «Формат»,​​, которые используются совершенно​​ то выдается соответствующее​​Таблица1[Цена]​Загрузка надстройки мастера подстановок​ этот диапазон.​

Для ячеек, расположенных вразброс

​Для решения этой задачи​ идущего​).​ столбца Продажи.​

Пример

​критерии должны были оформлены​ кто из рабочих​

​ чтобы выделить максимальное​

​ можно использовать функцию​

​ на производстве допускает​

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

​ данными для примера:​ значение в столбце​

​ — это количество столбцов,​ ВПР или сочетание​

Функция ДМИН() — нахождение минимального значения по условию в MS EXCEL

​.​ Белова).​Создадим в диапазоне​ таблицы с заголовками;​

​ наименьшее количество ошибок,​A1=»AM654″​Помогите, пжл, разобраться​shevnk​ третье по очереди​Усложним немного задачу. Ячейка​ Excel. Для этого​ минимум, но и​Цена​

Синтаксис функции ДМИН()

  • ​ которое нужно отсчитать​ функций ИНДЕКС и​
  • ​Если необходимо найти адрес​В какой день​G5:G6​
  • ​заголовки таблицы критериев совпадали​ или узнать наибольшую​ВПР(C3;номера!$1:$65536;2;0) — это​ с формулой:​

​: Спасибо огромное ВСЕМ.​
​ наименьшее значение заданного​​ с наименьшим значением​ в появившемся окне​ сумму, максимум и​. В противном случае​можно подкорректировать на​ кнопку​ справа от начальной​​ ПОИСКПОЗ.​​ ячейки с минимальным​ Белов продал первую​табличку критериев (желательно​
​ с заголовками исходной​​ заработную плату в​ что, поиск по​Необходимо найти в​Извините, что раньше​ диапазона $B$2:$B$9. Если​​ должна автоматически получить​​ «Формат ячеек» на​

  • ​ количество значений по​ – логическое значение​ вкладке​​Параметры Excel​​ ячейки, чтобы получить​Дополнительные сведения см. в​ значением, то функцию​ партию товара?​
  • ​ над исходной таблицей​ таблицы (если критерий​ отделе. Существует несколько​​ всему листу? Какие​​ диапазоне максимальное значение,​ не отблагодарила. почему-то у​ результат вычисления функции​ красный цвет заливки.​
  • ​ вкладке «Заливка» выберите​ условию.​ ЛОЖЬ (FALSE).​

​Конструктор (Design)​​и выберите категорию​ столбец, из которого​ разделе, посвященном функции​ МАКС() нужно заменить​=ДМИН(B5:D11;D5;G5:G6)​ или сбоку, чтобы​ не задается формулой).​ способов поиска наименьшего​ столбцы?​

Задачи

​ но чтобы оно​​ меня код картинки​​ равен текущей ячейке​ Следующая ячейка с​​ желаемый цвет заливки​​Если в исходной таблице​​Таким образом внешняя функция​​, которая появляется, если​Надстройки​ возвращается значение. В​ ВПР.​

​ на МИН(). Аналогично​Решение аналогично предыдущей задаче,​

​ она не мешала​ДМИН(база_данных;поле;условия)​​ или наибольшего числа​kim​ удовлетворяло логику поиска​ не отображался, поэтому​ значит к ней​ вторым наименьшим значением​ для ячеек. Например,​ очень много строк,​МИН (MIN)​

  • ​.​​ этом примере значение​​Что означает:​ можно найти адрес​ только минимальное значение​ добавлению новых данных​База_данных​ в диапазоне.​: vikttur, мне особо​ — ЕСЛИ(НАИБОЛЬШИЙ(I3:I7;1);1) Здесь​ не могла ничего​ присваивается соответственный формат​ получит цвет заливки​ зеленый. И нажмите​
  • ​ но данные меняются​выбирает минимальное не​ ячейку нашей «умной»​В поле​ возвращается из столбца​=ИНДЕКС(нужно вернуть значение из​​ ячейки, например, с​​ ищется не в​ в таблицу), состоящую​представляет собой диапазон​Выделите ячейку, находящуюся под​ приглянулись диапазоны: 6пробел5536​ получается, что он​ ответить :(​ (цвет заливки). Важно​ оранжевый. А третье​ ОК на всех​ не часто, то​​ из всех значений​​ таблицы. Подробнее о​Управление​ D​

​ C2:C10, которое будет​​ третьим наибольшим значением​​ столбце​ из заголовка (совпадает​​ ячеек с данными​​ числами, наименьшее из​ и 65пробел536 ;)​ найдет первое наибольшее​Ekonom​ отметить что в​​ наименьшее значение –​​ открытых окнах.​ удобнее будет использовать​ цен, а только​

​ таких таблицах и​выберите значение​

​Продажи​ соответствовать ПОИСКПОЗ(первое значение​ в списке​С​ с названием заголовка​ связанными логически, т.е.​ которых требуется найти,​

​Guest​ число и, соответственно,​: Сделал все как​ формуле для просмотра​ желтый.​В результате мы выделили​ сводную таблицу, т.к.​ из тех, где​ их скрытых возможностях​Надстройки Excel​.​ «Капуста» в массиве​=АДРЕС(ПОИСКПОЗ(НАИБОЛЬШИЙ(A:A;3);A:A;0);1;4)​(Продажи), а в​ столбца исходной таблицы,​ таблицу. Верхняя строка​ или справа от​: ДА, скобки точно​​ проставит 1. Но​​ написано, все равно​

​ диапазона мы используем​Чтобы добиться данного эффекта​​ цветом ячейку с​ формула массива и​ товар был​

​и нажмите кнопку​К началу страницы​ B2:B10))​Для вывода самого значения​​ столбце​​ к которому применяется​ таблицы должна содержать​​ них.​​ не поставил. ​

​ у мне надо​ посчитало общую сумму,​

​ следует выполнить следующие​ максимальным числовым значением.​ функция ДМИН могут​Бумага​

​Начиная с версии Excel​Перейти​Для выполнения этой задачи​

​ можно использовать, например,​D​ критерий) и собственно​ заголовки всех столбцов.​На вкладке​Логика такова: Если​

​ проставить: Если число​ да оговорюсь искал​ а после оператора​

Недостаток функции

​ действия:​Чтобы выделить цветом минимальное​ сильно тормозить Excel.​, т.к. ЛОЖЬ функцией​ 2016 в наборе​.​ используется функция ГПР.​ первое значение, соответствующее​ формулу =ДВССЫЛ(АДРЕС(ПОИСКПОЗ(МАКС(A:A);A:A;0);1;4)), но​(Дата).​ критерия (условия отбора);​ В​Главная​ в ячейке A1​

​ в диапазоне/массиве первое​ не минимальное значение,​ сравнения =B2 –​Выделите диапазон ячеек B2:B9​ значение в Excel,​

​Установите активную ячейку в​МИН​ функции Microsoft Excel​В области​ См. пример ниже.​ значению​

Адрес ячейки в MS EXCEL, содержащей максимальное значение

​ это бессмысленно, т.к.​Вопрос 3 (Минимальные продажи​Условия отбора должны быть​

​Базе_данных​​в группе​​ стоит «BS», то​ по велечине, то​

Задача

​ а сумму по​ относительные, так как​ и выберите инструмент:​

Решение

​ наконец появились функции,​

​Доступные надстройки​Функция ГПР выполняет поиск​Капуста​ можно написать просто​ Мяса продавцом Белов)​ записаны в специальном​​строки называются записями,​​Редактирование​

​ должнл быть «1»,​ 1, если второе,​ условию, написал так:=СУММ(ЕСЛИ($A$2:$A$52=A2;$B$2:$B$52;»»))​ значение функции сравнивается​ «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать​ действий, описанных выше.​ умной таблицы и​ можно выделить мышью​ которые легко решают​
​установите флажок рядом​

​ по столбцу​(B7), и возвращает​ МАКС(A:A)​=ДМИН(A5:C11;C5;F5:G6)​ формате: =»=Белов» (будет​ а столбцы —​

​нажмите стрелку рядом​ а вот если​ то2 и т.д.​ — столбец «А»​
​ с каждой ячейкой​

​ формулу для определения​ Только вместо функции​​ выберите на вкладке​​ всю функцию ЕСЛИ(…)​

​ нашу задачу -​ с пунктом​Продажи​ значение в ячейке​Также можно определить позицию​В данном случае таблица​ производиться поиск в​ полями.​

​ AM, то надо​ Просто придется «городить»​​ — условие, «В»​​ выделенного диапазона. А​

Поиск значений в списке данных

​ форматируемых ячеек».​ МАКС, должна быть​Вставка — Сводная таблица​ в строке формул​ это функции​Мастер подстановок​и возвращает значение​ C7 (​ максимального значения в​ критериев представляет собой​ столбце Продавец только​Поле​Сумма​ поискать в листе​ очень длинную формулу.​ — область необходимой​ функция НАИБОЛЬШИЙ работает​В поле ввода введите​ функция МИН. Данная​ (Insert — Pivot​

В этой статье

​. и нажать на​МИНЕСЛИ (MINIFS)​и нажмите кнопку​

​ из строки 5 в​100​ списке (только первого​

​ 2 столбца Товар​ значений​ — заголовок столбца, в​и выберите команду​

​ «номера» кто это,​ Может кто знает​ суммы, и чего. ​

​ обратно пропорционально.​ формулу: =НАИМЕНЬШИЙ($B$2:$B$9;1)=B2 и​ функция позволяет выбрать​

Поиск значений в списке по вертикали по точному совпадению

​ОК​ указанном диапазоне.​).​ сверху):​ и Продавец.​

Примеры функции ВПР

​точно совпадающих​ котором ищется минимальное​Минимум​

Примеры функций ИНДЕКС и ПОИСКПОЗ

​ путь покороче??​ где косяк?​Полезный совет! Если нет​ нажмите на кнопку​ наименьшее значение в​

​. В появившемся окне​F9​МАКСЕСЛИ (MAXIFS)​​.​​Дополнительные сведения см. в​Дополнительные сведения см. в​=ПОИСКПОЗ(МАКС(Список);Список;0)​​Вопрос 4 (Минимальные продажи​​со словом Белов​

​ значение, если выполняется​(поиск наименьшего) или​ — должно быть​

Поиск значений в списке по вертикали по приблизительному совпадению

​VDM​ необходимости разбивать 3​

​ «Формат», чтобы задать​​ таблице Excel. А​ нажмите​, чтобы наглядно увидеть​

​. Синтаксис этих функции​Следуйте инструкциям мастера.​ разделе, посвященном функции​ разделах, посвященных функциям​Имя Список представляет собой​ среди продаж Белова​​ (или белов, БЕЛОВ,​​ условие. Существует несколько​Максимум​ 2, «хозтовары» -​: . ​: Вводили как формулу​ наименьших значения на​ красный цвет заливки​ вместо зеленого цвета​ОК​​ тот самый результирующий​​ очень похож на​

​К началу страницы​ ГПР.​ ИНДЕКС и ПОИСКПОЗ.​

Поиск значений по вертикали в списке неизвестного размера по точному совпадению

​ или Батурина)​ т.е. без учета​ вариантов заполнения аргумента​

​(поиск наибольшего), а​​ 3. Только надо​РАНГ?​ массива?​ разные цвета, тогда​ для ячейки. И​ заливки выберите красный​:​ массив, из которого​СУММЕСЛИМН (SUMIFS)​В Microsoft Excel давно​К началу страницы​

​К началу страницы​​A7:A25​=ДМИН(B5:C11;C5;G5:G7)​ регистра). Если написать​

​Поле​​ затем нажмите клавишу​​ искать не по​Igor67​court​ необязательно создавать 3​ нажмите ОК на​

​ цвет.​​В конструкторе сводной таблицы​ потом функция​:​ есть в стандартном​Для выполнения этой задачи​Для выполнения этой задачи​(см. файл примера).​В данном случае таблица​ условие не в​​:​​ ВВОД.​

Поиск значений в списке по горизонтали по точному совпадению

​: Может Вам РАНГ()​: Добрый день. а​ правила условного форматирования​

​ всех открытых окнах.​К одному и тому​​ перетащите поле​​МИН​=МИНЕСЛИ(Диапазон_чисел; Диапазон_проверки1; Условие1; Диапазон_проверки2;​ наборе функции​

​ используется функция ГПР.​ используется функция ВПР.​Как правило, позиция значения​

​ критериев представляет собой​

Поиск значений в списке по горизонтали по приблизительному совпадению

​ виде =»=Белов», а​можно ввести текстовое значение,​

​Для выполнения этой задачи​​ по первым двум​ нужно?​ если условий для​

​ для одного и​Не снимая выделения с​ же диапазону должно​Товар​и выбирает минимальное​ Условие2 . )​СЧЁТЕСЛИ (COUNTIF)​Важно:​Важно:​

​ в списке требуется​ 1 столбец Продавец​ просто ввести в​

​ представляющий собой название​

Создание формулы подстановки с помощью мастера подстановок (только Excel 2007)

​ используются функции МИН,​​ буквам.​yevgeniy​ поиска больше двух?​ того же диапазона.​ диапазона B2:B9 сразу​ быть применено 2​в область строк,​

​ значение:​где​,​ Значения в первой​ Значения в первой​ для вывода значения​ с 2-мя строками​ ячейку значение Белов,​ одного из заголовков​ МАКС, НАИМЕНЬШИЙ или​vikttur​: используйте =РАНГ()​Пример прикреплен.​ Достаточно просто немного​ же создаем второе​

​ правила условного форматирования.​

​ а​​Этот вариант использует малоизвестную​​Диапазон_чисел​​СУММЕСЛИ (SUMIF)​​ строке должны быть​​ строке должны быть​​ из той же​

​ (с фамилиями продацов​​ то условию будут​​Базы_данных​ НАИБОЛЬШИЙ.​

​Serge​​Владимир​ ​ изменить формулу добавив​ правило условного форматирования.​​ Чтобы проверить выберите​​Цену​​ (и многими, к​​- диапазон с​

​и​​ отсортированы по возрастанию.​​ отсортированы по возрастанию.​​ строки соседнего столбца.​​ Белов и Батурин).​​ удовлетворять текстовые строки,​​(исходной таблицы). Текст​

​Скопируйте указанные ниже данные​​ объяснения :)​​: Всем доброго вечера.​: >​​ всего один символ​​ Снова выберите инструмент:​​ инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление​​в область значений.​

​ сожалению, недооцененную) функцию​

Поиск минимального или максимального значения по условию

​СРЗНАЧЕСЛИ (AVERAGEIF)​В приведенном выше примере​В приведенном выше примере​​ Это можно сделать,​​В файле примера приведено​​ которые​​ указывается в двойных​​ на пустой лист.​​=ЕСЛИ(ЛЕВСИМВ(A1;2)=»Bs»;1;ЕСЛИ(ВПР(A1;номера!$A$1:$C$9;2;0)=»Гастроном»;2;3))​Помогите, пжл, разобраться​Для F, И​ оператора: =НАИМЕНЬШИЙ($B$2:$B$9;3)>=B2. То​ «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать​ правилами»​ Чтобы заставить сводную​ДМИН (DMIN)​ выбирается минимальное или​и их аналоги,​ функция ГПР ищет​

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

​1​Guest​​ с формулой:​​ F-1​​ есть – больше​​ формулу для определения​​Финальный эффект применения двух​​ вычислять не сумму​из категории​ максимальное​

​ позволяющие искать количество,​ значение 11 000 в строке 3​ имя первого учащегося​ следующей формулы:​ задач.​слово Белов, например,​​ или «Урожай»,​2​: Большое спасибо. ))))) Сам​​Необходимо найти в​​Для F, ИЛИ​​ или равно.​ форматируемых ячеек».​​ правил условного форматирования​​ (или количество), а​Работа с базой данных​Диапазон_проверки​ сумму и среднее​ в указанном диапазоне.​​ с 6 пропусками в​​=ИНДЕКС(Товар;ПОИСКПОЗ(МАКС(Список);Список;0))​​В случае, когда ни​​ «Белов Иван», «Иван​можно ввести число (без​​3​​ бы долго промучился))))​ диапазоне максимальное значение,​​ F-1​​Все описанные способы хороши​В поле ввода введите​ для выделения цветом​ минимум щелкните правой​ (Database)​- диапазон, который​ в таблице по​

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016

​ Значение 11 000 отсутствует, поэтому​ диапазоне A2:B7. Учащихся​Имя Товар представляет собой Именованный​ одна строка не​ Белов» и пр.)​ кавычек), задающее положение​4​​P.S. to kim​​ но чтобы оно​​Какое из условий​​ тем, что при​ формулу: =НАИМЕНЬШИЙ($B$2:$B$9;2)=B2 и​​ наибольшего и наименьшего​​ кнопкой мыши по​

​и требует небольшого​ проверяется на выполнение​

  • ​ она ищет следующее​​ с​ диапазон​ удовлетворяет условиям, то​Предполагаем, что​
  • ​ столбца в​​5​ — Сам удивился,​ удовлетворяло логику поиска​
  • ​ правильно?​​ изменении значений в​

​ нажмите на кнопку​ значения столбца B​

​ любому числу и​ изменения результирующей таблицы:​​ условия​​ условиям. Но что​​ максимальное значение, не​​6​B7:B25​ функция ДМИН() вернет​База_данных​Базе_данных​6​ может, из-за того,​ — ЕСЛИ(НАИБОЛЬШИЙ(I3:I7;1);1) Здесь​Владимир​

Способ 2. Формула массива

​ ячейках они все​ «Формат», чтобы задать​

​ (Расходы):​ выберите в контекстном​Как видите, зеленые ячейки​Условие​ если нужно найти​ превышающее 11 000, и возвращает​ пропусками в таблице нет,​.​ 0. Это может​(исходная таблица) находится​: 1 — для​

​7​ что сохранял из​ получается, что он​: Для ИЛИ:​ равно будут работать​​ оранжевый цвет заливки​​Принцип действий обоих правил​ меню команду​​ с результатами транспонированы​​- критерий отбора​ не сумму или​ 10 543.​ поэтому функция ВПР​​Предположим, что требуется найти​​ ввести в заблуждение,​ в​ первого столбца, 2​

​A​​ 2003 англ версии​​ найдет первое наибольшее​=МИН(ЕСЛИ(((H3=B3:B18)*(I3=C3:C18)+(H3=B3:B18)*(J3=E3:E18))>0;D3:D18))​ автоматически.​ для ячейки. И​ одинаковый. Они отличаются​​Итоги по — Минимум​​ из столбца в​​Например, в нашем​​ среднее, а минимум​Дополнительные сведения см. в​ ищет первую запись​ внутренний телефонный номер​

​ т.к. не понятно:​A5:D11С5​​ — для второго​​Данные​ в 2007. ;-))​ число и, соответственно,​Для И:​​shevnk​​ нажмите ОК на​ только функциями =МАКС()​

Способ 3. Функция баз данных ДМИН

​:​ строку и над​ случае:​​ или максимум по​​ разделе, посвященном функции​​ со следующим максимальным​ сотрудника по его​​ есть ли на​– это ссылка​

​ и т.д.​10​pavel159357​ проставит 1. Но​=МИН(ЕСЛИ(((H3=B3:B18)*(I3=C3:C18)*(J3=E3:E18)*D3:D18)>0;D3:D18))​: Коллеги, добрый день.​ всех окнах.​ и =МИН(). Более​

  • ​Вытаскивать данные из сводной​​ ними добавлена мини-таблица​Просто, красиво, изящно. Одна​ условию(ям)?​
  • ​ ГПР.​​ значением, не превышающим​ идентификационному номеру или​ самом деле строки,​ на заголовок столбца,​
  • ​можно ввести ссылку на​​7​: Большое спасибо. ))))) Сам​ у мне надо​court​Я новичок в​Создайте третье правило. А​​ того аргументы функций​​ в дальнейшие расчеты​​ (F4:H5) с условиями.​​ проблема — функции​

​Предположим, нам нужно найти​К началу страницы​ 6. Она находит​ узнать ставку комиссионного​ удовлетворяющие критерию и​ в котором ищется​ ячейку, содержащую заголовок​9​​ бы долго промучился))))​​ проставить: Если число​​: СПАСИБО. ​​ данном деле, поэтому​​ в поле ввода​​ идентичны. Первая функция​ теперь можно с​ Логика работы этой​МИНЕСЛИ​ минимальную цену для​Примечание:​ значение 5 и возвращает​

Способ 4. Сводная таблица

​ вознаграждения, предусмотренную за​ минимальное значение =0​ минимальное значение, если​ столбца.​27​P.S. to kim​ в диапазоне/массиве первое​[del]ovoi​ обращаюсь к вам​

​ введите формулу: =НАИМЕНЬШИЙ($B$2:$B$9;3)=B2​ =МАКС() ищет в​ помощью функции​ функции следующая:​​и​ каждого товара в​ Поддержка надстройки «Мастер подстановок»​​ связанное с ним​ определенный объем продаж.​​ или в исходной​​ выполняется условие.​

​Условия​0​​ — Сам удивился,​​ по велечине, то​: Возможно ли найти​​ за помощью.​​ и нажмите на​ несменяемом диапазоне с​ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA)​База_данных​МАКСЕСЛИ​ базе данных по​ в Excel 2010​ имя​ Необходимые данные можно​​ таблице отстутствуют строки,​​G5:G6​

​ — интервал ячеек, который​4​ может, из-за того,​ 1, если второе,​​ 10 максимальных (минимальных)​​Суть в том,​ кнопку «Формат», чтобы​

Как выделить наименьшее и наибольшее значение в Excel цветом

​ абсолютными ссылками $B$2:$B$10​, которую мы подробно​- вся наша​появились только начиная​ поставщикам:​ прекращена. Эта надстройка​Алексей​ быстро и эффективно​ удоблетворяющие критерию.​– ссылка на​ содержит задаваемые условия​Формула​ что сохранял из​ то2 и т.д.​ значений в таблице​ что есть два​ задать желтый цвет​

Как выделить цветом максимальное значение в Excel

​ максимальное значение. После​ разбирали ранее:​

​ таблица вместе с​ с 2016 версии​Таким образом, условием будет​

  1. ​ была заменена мастером​.​ находить в списке​Как показано на рисунке​
  2. ​ табличку критериев (см.​ (т.е. таблица критериев).​
  3. ​Описание (результат)​ 2003 англ версии​
  4. ​ Просто придется «городить»​ не прибегая к​ столбца 1 и2.​ заливки для ячейки.​ функции стоит оператор​В процессе подготовки разного​ заголовками.​ Excel. Если у​ наименование товара (​ функций и функциями​Дополнительные сведения см. в​ и автоматически проверять​

​ выше, Параметр3 вообще​ рисунок выше).​ Структура таблицы с​

Как выбрать минимальное значение в Excel

​=МИН(A2:A7)​ в 2007. ;-))​ очень длинную формулу.​ автофильтру? Эти значения​В первом столбце​ ОК на всех​ сравнения с изменяемой​ рода ранжирования или​Поле​ вас (или тех,​бумага​ для работы со​ разделе, посвященном функции​

​ их правильность. Значения,​ отстутствует в исходной​Итоговая формула выглядит так​ критериями отбора для​Поиск наименьшего числа в​Подскажите, пжл, использовал​ Может кто знает​

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

​ кто будет потом​,​ ссылками и массивами.​ ВПР.​ возвращенные поиском, можно​ таблице, но «минимальным»​ =ДМИН(B5:C11;C5;G5:G6)​ ДМИН() аналогична структуре​ диапазоне (0)​ «ранг», но он​ путь покороче??​ в дальнейших вычислениях.​ втором значения. Нужно​К одному и тому​ имеет относительную ссылку​ очень удобно если​ из шапки таблицы,​ работать с вашим​карандаши​В Excel 2007 мастер​К началу страницы​ затем использовать в​ значением является 0!​Как видно из исходной​ для Расширенного фильтра​=МАКС(A2:A7)​ не по порядку​Ничего не понял​Guest​ при соблюдении условия​ же диапазону должно​ начиная от B2.​ выделить цветом наилучший​

Выделение цветом трех наименьших значений подряд

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

​ проставляет рейтинг при​ (см.файл), выкладывайте пример.​: Сортировка «А-Я», «Я-А»​ из первого столбца,​ быть применено 3​ Правило условного форматирования​ или наихудший результат.​ минимальное значение.​ версии, то придется​ручки​

​ подстановки, основанную на​ используются функции СМЕЩ​ как результаты. Существует​

  1. ​ =НАИМЕНЬШИЙ(ЕСЛИ(A6:A15=D6;B6:B15;»»);1), которая возвращает​ 3 строки, поэтому​ БДСУММ().​ диапазоне (27)​ двух одинаковых значениях​
  2. ​22,5 кб​ и ручками​ найти минимальное значение​ правила условного форматирования.​ читает данную формулу​ Как выделить максимальное​Критерий​ шаманить другими способами.​
  3. ​), а диапазоном для​ данных листа, содержащих​ и ПОИСКПОЗ.​ несколько способов поиска​ ошибку #ЧИСЛО! в​ результатом будет минимальное​Предположим, что в диапазоне​=НАИМЕНЬШИЙ(A2:A7;2)​
  4. ​ и не хочет​kim​Guest​ из диапазона значений​ Чтобы проверить выберите​ так: если число​ значение в Excel​- таблица с​
  5. ​В английской версии это​ выборки — столбец​ названия строк и​Примечание:​ значений в списке​ случае, если ни​ значение из массива​A5:D11​Поиск второго наименьшего числа​
  6. ​ считать при пустых​: Так наверное как-то. ​: есть формулы сортировки,​ второго столбца.​ инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление​ больше всех, то​ цветом? Здесь существенно​

​ условиями отбора, состоящая​ будет, соответственно =MIN(IF(Table1[Товар]=F4;Table1[Цена]))​

​ с ценами.​ столбцов. С помощью​ Данный метод целесообразно использовать​ данных и отображения​ одна строка не​

​ (450; 6544; 1245),​имеется таблица продаж​ в диапазоне (4)​ ячейках(( Может можно​=РАНГ(A1;$A$1:$A$20)+СЧЁТЕСЛИ($A$1:$A1;A1)-1​ тут выкладывались​

Функция наименьший и наибольший в Excel

​Полагаю, что в​ правилами».​ выделить цветом. Как​ поможет условное форматирование.​ (минимально) из двух​Не забудьте после ввода​Для будущего удобства, конвертируем​ мастера подстановок можно​ при поиске данных​ результатов.​ удовлетворяет критерию.​ т.е. 450.​ (​=НАИБОЛЬШИЙ(A2:A7;3)​ как нибуть это​Guest​[del]ovoi​ файле полее понятно​Три наименьшие значения автоматически​ только совпадает максимальное​ Преимуществом такого подхода​ ячеек: названия столбца,​ этой формулы в​ исходный диапазон с​ найти остальные значения​ в ежедневно обновляемом​Поиск значений в списке​Найдем адрес ячейки, содержащей​Альтернативное решение — формула​База_данных​

​Поиск третьего наибольшего числа​ поправить? Буду очень​: Да, спасибо. это​: Я не против​ изложена задачка.​ выделились разными цветами.​ число (которое возвращает​ является сохранение работоспособности​ по которому идет​ первую зеленую ячейку​ ценами в «умную​ в строке, если​ внешнем диапазоне данных.​ по вертикали по​

​ максимальное значение в​ =НАИМЕНЬШИЙ(ЕСЛИ(B6:B11=G6;C6:C11;»»);1), которая вводится​), содержащая​ в диапазоне (9)​ благодарен за помощь..​ помогло.​

поиск минимального значения из диапазона при заданном условии

​ ручками :)), просто​​Помогите, пожалуйста. Пробовала​
​Внимание! Если просматриваемый диапазон​ функция МАКС) со​ автоматического выделения цветом​ проверка (​
​ G4 нажать не​ таблицу». Для этого​ известно значение в​
​ Известна цена в​ точному совпадению​ списке.​ как формула массива​Поля​Функция ДМИН(), английский вариант​kim​Не хочу показаться​
​ на основе этих​ через =ЕСЛИ(ПРОСМОТР();МИН()), не​ содержит несколько одинаковых​

​ значением текущей ячейки,​ лучших/худших показателей даже​Товар​ Enter, а Ctrl+Shift+Enter,​ выделите его и​

​ одном столбце, и​​ столбце B, но​
​Поиск значений в списке​Пусть в столбце​

​ (подробнее см. здесь).​​(столбцы) Товар, Продавец,​ DMIN(), находит в столбце​

​: Так наверное?​​ наглым, но мож​ десяти значений потом​ получается, находит просто​ минимальных значений все​ формула возвращает логическое​

​ при добавлении или​​) и критерия (​ чтобы ввести ее​

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

​Постарайтесь в следующий​​ кто глянет на​ форумулы вычисляются, каждый​ минимальное значение, без​ они будут выделены​ значение ИСТИНА и​ удалении данных из​Бумага, Карандаши, Ручки​ как формулу массива.​Главная — Форматировать как​ которые создает мастер​ данных возвратит сервер,​ приблизительному совпадению​

​имеется диапазон ячеек,​​ ни одна строка​ (см. файл примера​

​ заданным условиям.​​ раз прикреплять пример​ вторую вкладку, надо​ раз предеться ссылки​

​ цветом.​​ сразу же применяется​

​ таблицы.​).​
​ Затем формулу можно​ таблицу (Home -​

​ подстановок, используются функции​ а первый столбец​

​Поиск значений по вертикали​​ содержащий числа.​

​ не удовлетворяет условиям,​

​ xls (2003 Excel)​​ 2 формулы срастить,​

Поиск 10 максимальных значений в таблице

​ не них менять.:((​​Guest​Аналогичным способом можно выделять​ формат со соответствующим​Для примера возьмем таблицу​Это обычная формула (не​ скопировать на остальные​ Format as Table)​

​ ИНДЕКС и ПОИСКПОЗ.​​ не отсортирован в​ в списке неизвестного​

​Найдем адрес ячейки, содержащей​​ то формула вернет​Сформулируем задачи в виде​

​ той же группе​​DV​ а не получается..((​ А кроме меня​: =МИН(ЕСЛИ(B3:B15=1;C3:C15;»»))​ цветом наибольшие значения.​ цветом заливки. Так​ расходов:​ формула массива), т.е.​ товары в ячейки​или нажмите​Щелкните ячейку в диапазоне.​ алфавитном порядке.​ размера по точному​ максимальное значение в​

​ ошибку #ЧИСЛО! в​​ вопросов.​
​ функций, что и​

​Формула массива, вводится​​ Просто изменить в​
​ же срабатывает правило​Чтобы молниеносно выделить наибольшие​
​ можно вводить и​ G5:G6.​Ctrl+T​На вкладке​C1​ совпадению​ списке.​ отличие от ДМИН(),​Вопрос1 (Определить минимальные продажи​ БДСУММ(), БСЧЁТ() и​=СУММ(—(ЧАСТОТА(($B$2:$B$20>B2)*$B$2:$B$20;($B$2:$B$20>B2)*$B$2:$B$20)>0))​: В Вашем примере​ люди работать с​ одновременным нажатием Shift+Ctrl+Enter​ формуле функцию НАИМЕНЬШИЙ​ для умного форматирования​ и наименьшие расходы​ использовать ее привычным​Давайте разберем логику работы​

​. Наша «поумневшая» таблица​​Формулы​
​ — это левая верхняя​

​Поиск значений в списке​​Ниже приведено 3 решения:​ которая вернет 0​

​ Белова).​​ др. Поэтому составление​

​pavel159357​​ нет листа с​
​ документом будут, они​Маугли​
​ на НАИБОЛЬШИЙ. Например:​ ячеек столбца и​ делаем следующее:​ образом. Кроме того,​ этой формулы поподробнее.​ автоматически получит имя​в группе​ ячейка диапазона (также​ по горизонтали по​=АДРЕС(ПОИСКПОЗ(МАКС(A:A);A:A;0);1;4)​ (это может ввести​Т.е. найдем строки,​ формул и задание​: Спасибо. Так все​ названием «номера»​ о формулах вообще​: Так будет лучше,​ =НАИБОЛЬШИЙ($B$2:$B$9;3)=B2​ с функцией МИН.​

​Выделите диапазон с расходами​ в той же​

​Таблица1​​Решения​
​ называемая начальной ячейкой).​

​ точному совпадению​​=»A»&ПОИСКПОЗ(МАКС(A:A);A:A;0)​ в заблуждение, т.к.​
​ в которой в​ критериев производится для​ отлично работает. to​Guest​ ничего слышать не​ =МИН(ЕСЛИ($B$3:$B$15=B3;$C$3:$C$15;»»))​

​Функция =НАИМЕНЬШИЙ() ищет в​​​ B2:B10 и выберите​ категории можно найти​

​ каждую ячейку массива​​, а к столбцам​выберите команду​

​Формула​​Поиск значений в списке​=ЯЧЕЙКА(«АДРЕС»;ИНДЕКС(A:A;ПОИСКПОЗ(МАКС(A:A);A:A;0)))​
​ не понятно есть​
​ столбце Продавец содержится​ ДМИН() аналогично этим​ kim — конечно,​
​: Ээээ, сорри, это​
​ хотят.​Владимир​ заданном (в первом​В разных ситуациях полезно​

​ инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать​​ функции​ из столбца​ можно будет, соответственно,​

​Подстановка​​ПОИСКПОЗ(«Апельсины»;C2:C7;0)​ по горизонтали по​
​Если в диапазоне поиска​ ли в исходной​ значение Белов. Если​ функциям.​ сам мучаюсь с​ вкладка Значение. ))))​DL​: Я что-то не​ аргументе) диапазоне очередное​ применяется автоматическое выделение​ правило».​БДСУММ (DSUM)​Товар​ обращаться по их​.​ищет значение «Апельсины»​

​ приблизительному совпадению​​ имеется несколько значений,​ таблице строки, удоблетворяющие​
​ такие строки есть​

​Для использования этой функции​​ 2007 дома))​vikttur​
​: Формула массива​ понимаю, shevnk задаёт​ наименьшее значение. Очередь​ цветом трех ячеек​Выберите: «Использовать формулу для​,​

​на предмет равенства​​ именам, используя выражения​Если команда​
​ в диапазоне C2:C7.​Создание формулы подстановки с​ равных максимальному, то​ критерию или нет.​ в таблице, то​ требуется чтобы:​
​Еще раз всем​: Что Вы хотели​=НАИБОЛЬШИЙ($K$6:$K$29;СТРОКА(A1:A10))​ условие 4, а​ указана во втором​ с наименьшими значениями.​ определения форматируемых ячеек».​ДМАКС (DMAX)​ текущему товару (​ типа​Подстановка​

​ Начальную ячейку не​​ помощью мастера подстановок​
​ все вышеуказанные формулы​ См. раздел​ для них выведем​

​исходная таблица имела заголовки​​ спасибо!!)))​
​ сказать формулой​

​Guest​​ все отвечают с​ аргументе функции. То​ Условное форматирование со​В поле ввода введите​,​
​Бумага​Таблица1[Товар]​

Источник статьи: http://my-excel.ru/excel/poisk-minimalnogo-znachenija-v-excel.html

Наибольший excel

Поиск наименьшего или наибольшего числа в диапазоне

​Смотрите также​ принципами действия формул​ все номера строк​ B6:B18.​ это выбирать из​ A6:A18 для выборки​ постарались познакомить Вас​ нечетное количество значений,​Если требуется соблюсти несколько​ по 6 ячейкам,​

Для ячеек, расположенных подряд в одной строке или одном столбце

​ часть из которых​Cтрока формул показывает,​ которые нужно просуммировать,​ и логично, но​Формула​

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

Для ячеек, расположенных вразброс

​ вводится в ячейке​ иногда об этом​Описание (результат)​ ошибки #ЧИСЛО!.​

Пример

​ сможете легко модифицировать​

​ популярными статистическими функциями​

​ можно применить статистическую​

​ статистических расчетов и​

​ на производстве допускает​

​ строки. Эта же​ именно строках диапазона​

​ функция ИНДЕКС выберет​ Excel, которые Вы​

​ обычному рядовому пользователю​

​ в фигурные скобки​. Часть формулы СТРОКА(ДВССЫЛ(«A1:A»&E2))​

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

​ точки зрения третьим​=НАИМЕНЬШИЙ(A2:A10;4)​ число точек данных,​​0​​ наименьшее количество ошибок,​

Описание

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

Синтаксис

​Статистическая функция​ покажется темным лесом.​

​ <>. Их не​​ создает массив последовательных​ наибольшим будет все-таки,​Четвертое наименьшее число в​ функция НАИМЕНЬШИЙ возвращает​

​4​​ или узнать наибольшую​ решать много вычислительных​ первое наименьшее число,​ значение. Это нам​

Замечания

​ ИНДЕКС работает с​ Аргумент «диапазон» означает​ сможете применить на​

​ значений четное, то​, которая позволяет считать​СРЗНАЧ​ Мы же в​

​ нужно вводить самостоятельно.​ чисел, начиная с​ наверное, 3 (т.е.​ первом столбце (4)​ значение ошибки #ЧИСЛО!.​Формула​

Пример

​ заработную плату в​ задач.​ которое встречается в​ необходимо по причине​ учетом критериев определённых​ область ячеек с​ практике. Надеюсь, что​ функция возвращает среднее​ среднее арифметическое ячеек,​может использовать в​ рамках этого урока​ Они исчезнут, когда​ 1 и заканчивая​

​ отделе. Существует несколько​

​ определения именно первого​

​ качестве своих аргументов​

​ рассмотрим самые полезные​

​ способов поиска наименьшего​

​ наименьшего значения. Реализовывается​ строки внутри таблицы)​

​Например, на рисунке ниже​ более критериям.​

​ математические операторы и​

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

​ и распространенные функции​ формулу.​E2​ мы и будем​Второе наименьшее число во​ аргументе «массив», то​Поиск наименьшего числа в​

​ или наибольшего числа​​ пишется простейшая формула​​ строки функции ИНДЕКС​ данная задача с​ и третьем (номер​

​ первое наименьшее число.​ Удачи Вам и​ формула возвращает медиану​Статистическая функция​ различные функции Excel:​ данной категории.​Пояснение:​.​ применять в этой​

​ втором столбце (3)​ НАИМЕНЬШИЙ(массив;1) соответствует наименьшему​ диапазоне (0)​ в диапазоне.​ больше чем но​ выберет соответствующее значение​ помощью функции СТРОКА,​​ столбца в таблице)​​ В аргументе «заголовок_столбца»​ успехов в изучении​ для списка, состоящего​МАКС​Если необходимо вернуть среднее​В рамках данной статьи​Диапазон (массив констант), созданный​В приведенном примере, различия​ статье.​3​ значению, а НАИМЕНЬШИЙ(массив;n)​=МАКС(A2:A7)​Выделите ячейку, находящуюся под​ меньше чем. чтоб​ из таблицы A6:A18.​ она заполняет элементы​

​ аргументах. Так как​ для второй функции​ Excel.​ из 14 чисел.​​возвращает наибольшее значение​​ арифметическое значений, которые​ мы не будем​ с помощью функции​​ в подсчете суммы​​Для этого нужно сформировать​Сложим три (четыре, пять​ — наибольшему значению.​

​Поиск наибольшего числа в​ числами, наименьшее из​​ можно было отсортировать​​ В итоге формула​

​ наша исходная таблица​ СТРОКА, следует указать​Автор: Антон Андронов​Если отсортировать значения в​

​ в диапазоне ячеек:​ удовлетворяют определенному условию,​ затрагивать такие популярные​LARGE​ наибольших с учетом​ список уникальных (без​ и т.д.) наибольших​Скопируйте образец данных из​​ диапазоне (27)​​ которых требуется найти,​ только заданный диапазон​
​ возвращает это значение​

​ программы номерами строк​ A6:A18 имеет только​ ссылку на ячейку​​Если приходиться работать с​​ порядке возрастания, то​Статистическая функция​ то можно воспользоваться​ статистические функции Excel,​(НАИБОЛЬШИЙ), хранится в​​ и без учета​​ повторов) значений в​

​ значений из диапазона.​ следующей таблицы и​=НАИМЕНЬШИЙ(A2:A7;2)​ или справа от​ но не фильтром​ в ячейку B3​ листа. Но сначала​ 1 столбец, то​ с заголовком столбца,​

Суммирование наибольших чисел в Excel

​ большими таблицами определенно​ все становится на​МИН​ статистической функцией​

    ​ как​​ памяти Excel, а​​ повторов, начинаются с​ столбце​ Если в диапазоне​ вставьте их в​Поиск второго наименьшего числа​

​ в качестве результата​ от всех этих​​ третий аргумент в​​ который содержит диапазон​ найдете в них​

​ много понятней:​
​возвращает наименьшее значение​

​СРЗНАЧЕСЛИ​​СЧЕТ​​ не в ячейках​

​ имеются повторы, то​​ ячейку A1 нового​ в диапазоне (4)​На вкладке​Влад​ вычисления.​ номеров вычитается номер​ функции ИНДЕКС мы​ числовых значений.​ дублирующийся суммы разбросаны​

​Возвращает наиболее часто встречающееся​

  • ​ в диапазоне ячеек:​. Следующая формула вычисляет​​и​​ листа.​ наглядности суммируемые значения​с помощью формулы​ учитывать их не​
  • ​ листа Excel. Чтобы​=НАИБОЛЬШИЙ(A2:A7;3)​
  • ​Главная​: Простейшая: =ЕСЛИ((A1>0)*(A1​Поняв принцип действия формулы,​​ на против первой​​ не указываем.​Естественно эту формулу следует​

​ вдоль целого столбца.​ значение в массиве​
​Возвращает n-ое по величине​
​ среднее чисел, которые​

Статистические функции Excel, которые необходимо знать

​Массив констант выглядит следующим​​ выделены в каждом​​ =ЕСЛИ(СЧЁТЕСЛИ(A$2:$A2;A2)=1;A2;»») Напротив повторов​ будем.​ отобразить результаты формул,​Поиск третьего наибольшего числа​в группе​Аксинья​ теперь можно легко​ строки таблицы –​Чтобы вычислить номер строки​ выполнять в массиве.​

​ В тоже время​ числовых данных.​ значение из массива​ больше нуля:​, для них подготовлен​ образом: <22;10;8;6>.​ диапазоне разными цветами.​ в столбце​Пусть в диапазоне​ выделите их и​ в диапазоне (9)​Редактирование​: С примером​

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

СРЗНАЧ()

​Этот пример демонстрирует, как​​В​​А2:А20​ нажмите клавишу F2,​

​В этой статье описаны​нажмите стрелку рядом​Senya​ настраивать под другие​ число 5. Это​ числа в смежном​

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

​будут пустые ячейки,​имеется список числовых​ а затем — клавишу​ синтаксис формулы и​​ с кнопкой​​: Пардон, но формулы​ условия. Например, формулу​ делается потому, что​ диапазоне B6:B18 и​​ нажимать не просто​​ данные из таблицы​

​ все становится гораздо​​ мы нашли пятое​​ проверки условия используется​СРЗНАЧ​ для функции​ которая суммирует наибольшие​

СРЗНАЧЕСЛИ()

​ точнее со значением​ значений (см. файл​ ВВОД. При необходимости​ использование функции​Сумма​​ не сортируют и​​ можно изменить так,​ функция ИНДЕКС работает​ использовать его в​

​ клавишу Enter, а​ с первым наименьшим​ понятней:​ по величине значение​ один и тот​возвращает среднее арифметическое​SUM​ числа в диапазоне.​​ Пустой текст.​​ примера).​ измените ширину столбцов,​НАИМЕНЬШИЙ​и выберите команду​ не фильтруют. Формулы​ чтобы выбрать первое​ с номерами внутри​

​ качестве значения для​ целую комбинацию клавиш​ числовым значением, которое​Статистическая функция​ из списка.​ же диапазон, что​ своих аргументов.​(СУММ), давая результат​Используем функцию​Формула для подсчета 3-х​

​Вычислим сумму 3-х наибольших​ чтобы видеть все​в Microsoft Excel.​Минимум​​ подсчитываю, ищут, вставляют​​ максимальное значение в​ таблицы, а не​ второго аргумента, применяется​ CTRL+SHIFT+Enter. Если все​

​ имеет свои дубликаты.​​МОДА​​Чтобы убедиться в этом,​ не всегда удобно.​

​Данная функция может принимать​​ 46.​​LARGE​ наибольших​

НАИБОЛЬШИЙ()

​ без учета повторов​ данные.​Возвращает k-ое наименьшее значение​(поиск наименьшего) или​ и т.д.​ Excel:​ с номерами рабочего​

​ несколько вычислительных функций.​ сделано правильно в​ Нужна автоматическая выборка​

НАИМЕНЬШИЙ()

​на данный момент​ можно отсортировать числа​ На этот случай​ до 255 аргументов​Урок подготовлен для Вас​(НАИБОЛЬШИЙ). Чтобы найти,​

​без учета повторов​ (Как найти сумму​Данные 1​ в множестве данных.​

МЕДИАНА()

​Максимум​​Guest​​Если необходимо изменить условия​ листа Excel. В​Функция ЕСЛИ позволяет выбрать​ строке формул появятся​ данных по условию.​ устарела, точнее, устарела​ в порядке возрастания:​ у функции​ и находить среднее​ командой сайта office-guru.ru​ к примеру, второе​выглядит так:​ 3-х наибольших значений​Данные 2​

​ Эта функция используется​(поиск наибольшего), а​: вот вот подсчитать​ формулы так, чтобы​

​ тоже время функция​ значение из списка​ фигурные скобки.​ В Excel для​

​ ее форма записи.​Возвращает n-ое наименьшее значение​СРЗНАЧЕСЛИ​

​ сразу в нескольких​Источник: http://www.excel-easy.com/examples/sum-largest-numbers.html​ по величине число,​=СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B20;<1;2;3>))​

​ с учетом повторов​​3​​ для определения значения,​ затем нажмите клавишу​ кол-во значений удовлетворяющих​ можно было в​ СТРОКА умеет возвращать​​ по условию. В​​Обратите внимание ниже на​​ этой цели можно​​ Вместо нее теперь​ из массива числовых​

​существует третий необязательный​​ несмежных диапазонах и​​Перевела: Ольга Гелих​ нужно ввести следующую​В файле примера с​ показано в статье​1​ занимающего заданное относительное​ ВВОД.​ условию от . ​ Excel выбрать первое​ только номера строк​ ее первом аргументе​ рисунок, где в​ успешно использовать формулу​ используется функция​ данных. Например, на​ аргумент, по которому​ ячейках:​Автор: Антон Андронов​ формулу:​ помощью Условного форматирования​ Сумма трех наибольших).​

Выборка значений из таблицы Excel по условию

​ положение в множестве​Для выполнения этой задачи​ до . ​ максимальное, но меньше​ листа. Чтобы не​ указано где проверяется​ ячейку B3 была​ в массиве.​МОДА.ОДН​ рисунке ниже мы​ можно вычислять среднее.​Если в рассчитываемом диапазоне​Функции категории​=LARGE(A1:A11,2)​ подсчитываемые значения выделяются​ Для подсчета будем​4​ данных.​

Как сделать выборку в Excel по условию

​ используются функции МИН,​Sh_Alex​ чем 70:​ получилось смещение необходимо​ каждая ячейка в​ введена данная формула​Чтобы определить соответствующие значение​. Форма записи​ нашли четвертое наименьшее​

​ Т.е. по первому​ встречаются пустые или​Статистические​

​ красным.​ использовать функцию НАИБОЛЬШИЙ().​5​НАИМЕНЬШИЙ(массив;k)​ МАКС, НАИМЕНЬШИЙ или​: По-видимому Вы имели​=70;»»;B6:B18));СТРОКА(B6:B18)-СТРОКА(B5);»»)))’ >​ сопоставить порядок номеров​ диапазоне B6:B18 на​ в массиве:​ первому наименьшему числу​МОДА​ значение из списка.​ аргументу проверяем условие,​ содержащие текст ячейки,​предназначены в первую​Чтобы суммировать 4 наибольших​Если необходимо сложить три,​Необходимо помнить особенность функции​

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

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

​ ниже.​Скопируйте указанные ниже данные​ форматирования? Если это​ первое минимальное значение​ таблицы с помощи​ значения: ЕСЛИB6:B18=МИНB6:B18. Таким​ первым наименьшим числом:​

Как работает выборка по условию

​ Excel для совместимости.​ порядке возрастания, то​ находим среднее.​ В примере ниже​ диапазонов ячеек в​SUM​ т.д. наибольших значений,​ со списками чисел,​3​Массив​ на пустой лист.​ так, то посмотрите​ кроме нуля:​ вычитанием разницы. Например,​ способом в памяти​С такой формулой нам​ Допустим мы хотим​Как известно, категория​ все станет гораздо​Допустим, в таблице ниже​ среднее ищется по​

​ Excel. С помощью​(СУММ) и заменим​ то необходимо все​ среди которых имеются​3​ Обязательный. Массив или диапазон​1​ прикрепленный файл.​

​Как легко заметить, эти​ если таблица находится​ программы создается массив​ удалось выбрать минимальное​ узнать первый самый​Статистические​ очевидней:​ собрана статистика по​ четырем ячейкам, т.е.​ данных функций Вы​ 2 на <1;2;3;4>.​ время переписывать вышеуказанную​ повторы. Например, если​7​ числовых данных, для​2​С уважением, Александр.​ формулы отличаются между​ на 5-ой строке​ из логических значений​ значение относительно чисел.​

​ дешевый товар на​в Excel содержит​Статистическая функция​ стоимости лекарств в​(4+15+11+22)/4 = 13​ можете вычислить наибольшее,​=SUM(LARGE(A1:A11,<1,2,3,4>))​ формулу. Для придания​ имеется исходный массив​4​ которого определяется k-ое​3​Guest​ собой только функциями​ листа значит каждая​ ИСТИНА и ЛОЖЬ.​ Далее разберем принцип​ рынке из данного​ более 100 самых​МЕДИАНА​ городе. В одной​Если необходимо вычислить среднее,​ наименьшее или среднее​=СУММ(НАИБОЛЬШИЙ(A1:A11;<1;2;3;4>))​ динамизма (см. файл​ <1;2;3;​12​ наименьшее значение.​4​: Прошу прощения за​ МИН и МАКС​ строка таблицы будет​ В нашем случаи​ действия формулы и​ прайса:​ разноплановых функций. Но,​возвращает медиану из​ аптеке лекарство стоит​ учитывая все ячейки​ значение, подсчитать количество​Закончим нажатием​ примера, ячейка​

​6​6​k​5​ вопрос не по​ и их аргументами.​ на 5 меньше​ 3 элемента массива​ пошагово проанализируем весь​Автоматическую выборку реализует нам​ как показывает практика,​ заданного массива числовых​ дороже, в другой​ диапазона, то можно​ ячеек, содержащих заданную​Ctrl+Shift+Enter​E2​;6;7>, то третьим наибольшим​54​ Обязательный. Позиция (начиная с​

Как выбрать значение с наибольшим числом в Excel

​6​ теме : в​Скачать пример выборки из​ чем соответственная строка​ будут содержат значение​ порядок всех вычислений.​ формула, которая будет​ львиная доля этих​ данных. Медианой называют​

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

​4​

​ наименьшего числа) в​7​ формуле =ЕСЛИ((A1>0)*(A1​

​ таблицы в Excel.​ листа.​ ИСТИНА, так как​​ обладать следующей структурой:​

​ функций практически не​ число, которое является​

​ стоимость анальгина в​СРЗНАЧА​Данная категория содержит более​=SUM(LARGE(A1:A11,<1,2,3,4>))​ следующим образом:​ НАИБОЛЬШИЙ()) будет считаться​8​ массиве или диапазоне​A​Guest​

Формула больше чем но меньше чем

​Теперь Вас ни что​​После того как будут​ минимальное значение 8​Ключевую роль здесь играет​=ИНДЕКС(диапазон_данных_для_выборки;МИН(ЕСЛИ(диапазон=МИН(диапазон);СТРОКА(диапазон)-СТРОКА(заголовок_столбца);””)))​ применяется, а особенно​ серединой числового множества.​ среднем по городу,​. В следующем примере​

​ 100 самых различных​​=СУММ(НАИБОЛЬШИЙ(A1:A11;<1;2;3;4>))​

​=СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B20;СТРОКА(ДВССЫЛ(«A1:A»&E2))))​​ 6, а не​

​7​​ данных.​Данные​: Вместо ИЛИ можно​ не ограничивает. Один​ отобраны все минимальные​

​ содержит еще 2​​ функция ИНДЕКС. Ее​В месте «диапазон_данных_для_выборки» следует​ начинающими пользователями. В​ Если в списке​

​ воспользуемся следующей формулой:​​ среднее ищется уже​ функций Excel, большая​Примечание:​Теперь число наибольших значений,​ 3. Все правильно​
​23​

​Если массив пуст, функция​​10​ использовать + (плюс).​ раз разобравшись с​ значения и сопоставлены​

​ дубликата в столбце​​ номинальное задание –​ указать область значений​

Источник статьи: http://my-excel.ru/excel/naibolshij-excel.html

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

history 26 мая 2014 г.

Массив — ссылка на диапазон ячеек, содержащие данные, для которых определяется 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 последние формулы даже не обязательно вводить как формулы массива .

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

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

Источник статьи: http://excel2.ru/articles/funkciya-naibolshiy-v-ms-excel-naibolshiy

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *