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

Обновлено: 01.07.2024

Я пытаюсь создать инструмент сравнительного анализа цен на отдельном листе от моей таблицы данных. В таблице данных клиенты находятся вверху, артикулы внизу, а их цены на пересечениях (слишком много артикулов, чтобы поместить их вверху, и у меня слишком много других функций, работающих вне этой таблицы, чтобы изменить курс). Я использую большие и маленькие функции, чтобы получить 3 самых высоких и самых низких значения, и эти значения вычисляются непосредственно в моей таблице данных. Я перетаскиваю их на другой лист, используя vlookup в этой таблице, ссылаясь на номер артикула и эти конкретные столбцы. Я могу вернуть номер строки, в которой найдены эти значения, с помощью функции Match, которая ссылается на SKU, но не могу понять, как вернуть столбец, в котором фактическая цена зависит от возвращаемого значения для строки. Думаю, что есть более простой способ сделать это, о котором я просто не знаю или не знаю.

Ik zoek een формула с максимальным/минимальным значением в пределах диапазона. Это могелийк? Я использую Excel 2010.

необходимо извлечь меньшую сумму из двух строк, например
51000
5000
меньшую сумму следует вырезать и вставить в следующую строку.
он должен продолжаться на весь лист. Обычно это 0 после каждых двух сумм, и его следует пропустить, если есть только одно число (0, 250, 0), как показано ниже, пример удара
51000
5000
0
10000
1000

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

Пример 1: 3 | Пример 3 имеет наибольшее значение.
Пример 2: 2 | -
Пример 3: 4 | -
Пример 4: 1 | -

Здравствуйте, Дирк,
Чтобы решить вашу проблему, примените следующую формулу:
=ИНДЕКС(A1:A7,MATCH(MAX(B1:B7),B1:B7,FALSE),) &" имеет наибольшее значение: "&MAX(B1:B7)
см. скриншот ниже.

Пожалуйста, попробуйте, надеюсь, это поможет вам!

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

Здравствуйте, Арслан,
Можете ли вы дать более подробную информацию о вашей проблеме, или вы можете вставить скриншот здесь. Спасибо!

У меня есть лист Excel с различными столбцами - Дата - Названия предметов - Кол-во - Цена - Общая цена. Этот товар был куплен на несколько дат по разной цене. Как узнать максимальную цену каждого товара?

Привет, R NAGARAJAN,
Чтобы найти наибольшее значение на основе критериев, вам может помочь приведенная ниже формула: (пожалуйста, измените ссылки на ячейки в соответствии с вашими потребностями)

=MAX(($A$2:$A$10=E2)*$C$2:$C$10) (нажмите одновременно клавиши Ctrl + Shift + Enter, чтобы получить правильные результаты. )

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

Привет, Азад,
Чтобы получить наименьшее значение на основе критериев, примените следующую формулу:
=MIN(IF(A2:A11=D2,B2:B11))
Пожалуйста, не забудьте одновременно нажать клавиши ctrl+shift+enter. И измените ссылки на ячейки в соответствии с вашими потребностями.

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

Функция Excel МАКС

Функция MAX в Excel возвращает максимальное значение в указанном наборе данных.

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

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

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

Функция MAX доступна во всех версиях Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 и более ранних версиях.

Как составить формулу MAX в Excel

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

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

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

В ячейке введите =MAX(
Выберите диапазон чисел с помощью мыши.
Введите закрывающую скобку.
Нажмите клавишу Enter, чтобы завершить формулу.

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

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

Выберите ячейки со своими номерами.
На вкладке Главная в группе Форматы нажмите Автосумма и выберите Максимум в раскрывающемся списке. (Или нажмите Автосумма >Максимум на вкладке Формулы в группе Библиотека функций.)

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

5 вещей, которые нужно знать о функции MAX

Чтобы успешно использовать формулы Max на своих листах, запомните следующие простые факты:

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

Как использовать функцию MAX в Excel — примеры формул

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

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

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

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

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

Начните вводить формулу Max в ячейку.
После ввода открывающей скобки, удерживая нажатой клавишу Ctrl, выберите ячейки и диапазоны на листе.
Выбрав последний элемент, отпустите клавишу Ctrl и введите закрывающую скобку.
Нажмите Enter.

Excel автоматически использует соответствующий синтаксис, и вы получите формулу, похожую на эту:

Как показано на снимке экрана ниже, формула возвращает максимальное значение промежуточного итога из строк 5 и 9:

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

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

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

Функция MAX в Excel с условиями

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

Задача. Имея товары, перечисленные в B2:B15, и данные о продажах в C2:C15, мы стремимся найти самую высокую продажу для определенного товара, введенного в F1 (см. конец этого раздела).

Формула Excel МАКС ЕСЛИ

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

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

Также можно оценить несколько условий в одной формуле, и в следующем руководстве показано, как это сделать: MAX IF с несколькими условиями.

Формула MAX IF без массива

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

Для получения дополнительной информации см. MAX IF без массива.

Функция МАКСИФМ

В Excel 2019 и Excel для Office 365 есть специальная функция MAXIFS, предназначенная для поиска наибольшего значения по 126 критериям.

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

=МАКСЕСЛИ(C2:C15, B2:B15, F1)

Подробное объяснение синтаксиса см. в разделе Excel MAXIFS с примерами формул.

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

Получить максимальное значение, игнорируя нули

Фактически это разновидность условного MAX, рассмотренного в предыдущем примере. Чтобы исключить нули, используйте логический оператор «не равно» и поместите выражение «<>0» либо в критерий MAXIFS, либо в логическую проверку MAXIFS.

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

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

МАКС. ЕСЛИ

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

МАКСИФЫ

Это обычная формула, достаточно обычного нажатия клавиши Enter.

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

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

В качестве обходного пути можно использовать MAX IF вместе с ISERROR. Учитывая, что вы ищете в диапазоне A1:B5, формула принимает следующий вид:

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

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

В Excel 2019 и Excel для Office 356 функция MAXIFS может быть решением, если ваш набор данных содержит хотя бы одно положительное число или нулевое значение:

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

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

=ОБЪЕДИНИТЬ(4, 6, A1:B5)

Число 4 в первом аргументе указывает на функцию MAX, число 6 во втором аргументе – параметр "игнорировать ошибки", а A1:B5 – ваш целевой диапазон.

В идеальном случае все три формулы вернут один и тот же результат:

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

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

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

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

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

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

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

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

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

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

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

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

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

=IF(ABS(MAX(A1:B5))>ABS(MIN(A1:B5)), MAX(A1:B5), MIN(A1:B5))

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

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

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

Выделить наибольшее число в диапазоне

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

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

Готово! Наибольшее значение в выбранном диапазоне выделяется автоматически. Если имеется несколько максимальных значений (дубликатов), Excel выделит их все:

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

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

Выберите все строки, в которых вы хотите выделить максимальные значения (в данном примере C2:C7).
На вкладке Главная в группе Стили нажмите Новое правило >Используйте формулу, чтобы определить, какие ячейки следует форматировать .
В поле Форматировать значения, в которых эта формула верна, введите следующую формулу:

Совет. Аналогичным образом вы можете выделить наибольшее значение в каждом столбце. Шаги точно такие же, за исключением того, что вы пишете формулу для диапазона первого столбца и фиксируете координаты строки: =C2=MAX(C$2:C$7)

Функция Excel MAX не работает

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

Формула MAX возвращает ноль

Если обычная формула MAX возвращает 0, несмотря на то, что в указанном диапазоне есть более высокие числа, скорее всего, эти числа отформатированы как текст. Это особенно актуально, когда вы запускаете функцию MAX для данных, управляемых другими формулами. Вы можете проверить это, используя функцию ISNUMBER, например:

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

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

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

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

Загрузить рабочую тетрадь

5 способов найти максимальное значение в Excel с условием

1. Найдите максимальное значение с условием, используя функцию MAX

В этом разделе мы будем использовать функцию MAX. Давайте сначала рассмотрим концепцию этой функции.

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

Предположим, у нас есть набор данных о некоторых студентах с их идентификаторами, именами, факультетами, семестрами, CGPA и сборами. Наша задача — узнать максимальную плату за любой конкретный семестр.

Шаг 1. Введите формулу в ячейку J4 и нажмите Ctrl+Shift+Enter

Объяснение формулы

Здесь мы используем формулу массива. Во-первых, используя часть (F4:F16=K3), мы ищем семестр, соответствующий введенному нами семестру. Затем полная функция max ищет максимальное число среди выбранных ячеек.

Шаг 2. Введите любой номер семестра в ячейку J3 и нажмите Enter

2. Найдите максимальное значение с условием, используя формулу MAX IF

Давайте посмотрим, как узнать максимальное число с условиями, подобными предыдущему, но здесь мы будем использовать функции MAX IF в формуле. Давайте посмотрим на синтаксис функции ЕСЛИ

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

Для этого процесса мы найдем максимальную CGPA для определенного отдела.

Шаг 1. Введите формулу в ячейку J4 и нажмите Ctrl+Shift+Enter

Объяснение формулы

ЕСЛИ(D4:D16=J3, F4:F16) Эта часть сопоставляет ячейки отдела с данными, введенными в ячейку J3. Также находит совпадающие ячейки CGPA, а затем функция MAX находит из них наибольшее значение.

Шаг 2. Введите название любого отдела в ячейку J3 и нажмите Enter

3. Использование MAX IF без массива

В этом разделе давайте посмотрим, как можно выполнить ту же задачу, что и в методе 2, без формулы массива. Для этого нам понадобится функция СУММПРОИЗВ.

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

И снова наш набор данных будет таким же, как указано выше. Но добавим еще один. Таким образом, наша задача состоит в том, чтобы найти самый большой CGPA для любого конкретного факультета, а семестр должен быть меньше введенного нами числа.

Шаг 1. Введите формулу в ячейку J5

Объяснение формулы

MAX(((D4:D16=J3)*(E4:E16=J4)*(F4:F16))) Эта часть сопоставила данные из столбца "Кафедра и семестр" с нашими введенными данными. Затем, собрав их все, СУММПРОИЗВ возвращает сумму.

[Примечание: это альтернатива методу 2]

Шаг 2. Введите название факультета и семестр и нажмите Enter, чтобы увидеть результат

4.Использование формулы МАКС ЕСЛИ с логикой ИЛИ

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

Шаг 1. Введите формулу в ячейку J4 и нажмите Ctrl+Shift+Enter

Объяснение формулы

Мы использовали здесь несколько условий и добавили к ним знак +. (E4:E16=J3) + (E4:E16=L3) Эта часть выполняет операцию или. После этого IF((E4:E16=J3) + (E4:E16=L3), F4:F16) находит совпадающие ячейки и, наконец, функция MAX возвращает максимальное значение из совпавших ячеек.

Шаг 2. Введите любые два семестра и посмотрите результат

5. Найдите максимальное значение с условием, используя функцию MAXIFS

В Excel есть функция MAXIFS. Эта функция условно возвращает максимальное значение из любого набора данных.

Max_range -> Требуется. Фактический диапазон ячеек, в котором будет определяться максимум.

Criteria_range1 -> Это также необходимо. Набор ячеек для оценки с помощью правил.

Criteria1 -> Это еще один обязательный аргумент. Критерий в виде числа, выражения или текста, определяющий, какие ячейки будут оцениваться как максимальные. Тот же набор критериев работает для функций МИНИМН, СУММЕСЛИМН и СРЗНАЧЕСЛИМН.

[диапазон_критериев2, критерии2], … -> Эта часть не является обязательной. Мы можем передать Дополнительные диапазоны и связанные с ними критерии. Вы можете ввести до 126 пар "диапазон/критерий".

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

Теперь давайте посмотрим, как мы можем использовать функцию МАКСИФМ для предыдущих примеров.

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

Шаг 1. Введите формулу в ячейку J5

Объяснение формулы

Сначала мы прошли диапазон, в котором мы найдем максимальное значение G4:G16, затем первый диапазон критериев, который равен D4:D16, затем номер ячейки входного значения для первого критерия J3. Точно так же E4:E16, J4 делает то же самое для второго критерия.

Шаг 2. Введите название факультета и семестр. Затем нажмите Enter

Проблемы пользователей

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

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

Введите формулу, нажав Ctrl+Shift+Enter (сделайте ее формулой массива)

2. Как найти максимальное значение в одном столбце, но только если число является целым

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

Введите формулу, нажав Ctrl+Shift+Enter (сделайте ее формулой массива)

3. Как найти максимальные значения без учета нулей

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

Введите эту формулу и нажмите Ctrl+Shift+Enter (сделайте ее формулой массива)

(Без формулы массива)

Заключение

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

Дополнительная литература

Excel MIN и MAX в одной формуле
Как установить минимальное и максимальное значение в Excel (6 простых способов)
Расчет среднего, минимального и максимального значений в Excel (4 простых способа)

Мд. Абдулла Аль Мурад

Здравствуйте! Добро пожаловать в мой профиль. В настоящее время я работаю и изучаю Microsoft Excel, и здесь я буду публиковать статьи, связанные с этим. Моя последняя образовательная степень — бакалавр наук, а моей программой были компьютерные науки и инженерия Американского международного университета в Бангладеш. Я выпускник компьютерных наук с большим интересом к исследованиям и разработкам. Всегда старайтесь собирать знания из разных источников и старайтесь находить инновационные решения.

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

Загрузить практическую рабочую тетрадь

4 быстрых способа найти максимальное значение и соответствующую ячейку в Excel

Давайте посмотрим на этот набор данных. У нас есть сведения о сотрудниках компании North Group.

У нас есть идентификаторы сотрудников, имена сотрудников и их зарплаты в столбцах B, C и D соответственно.

1. Найдите максимальное значение и соответствующую ячейку с помощью функции ВПР

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

Этого можно добиться с помощью функции Excel VLOOKUP().

Чтобы узнать больше о функции ВПР() в Excel, посетите этот пост.

Мы будем использовать следующую формулу:

Смотрите, у нас есть Кейн Симпсон, сотрудник с максимальным ID.

Но есть два аспекта, на которые стоит обратить внимание.

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

Но если он слева, например, если мы хотим узнать фамилию сотрудника с максимальной зарплатой, ВПР() не сработает.

Во-вторых, если вы хотите узнать ссылку на ячейку сотрудника с максимальным идентификатором, а не имя, ВПР() вам не подходит.

Наилучшим выбором для обоих этих случаев является комбинация функций ИНДЕКС() и ПОИСКПОЗ() Excel.

2. Найдите максимальное значение и соответствующую ячейку с помощью функций ИНДЕКС-ПОИСКПОЗ

Чтобы узнать больше о функциях ИНДЕКС() и ПОИСКПОЗ() в Excel, перейдите по этой ссылке.

Давайте сначала попробуем узнать имя Сотрудника с максимальной зарплатой.

Можете ли вы угадать формулу?

Да. Ты прав. Формула будет такой:

Смотрите, у нас есть сотрудник с максимальной зарплатой. Джек Альфред.

Но теперь, если вас спросят, что такое ссылка на ячейку сотрудника с максимальной зарплатой, то?

Можно ли извлечь это с помощью формулы?

Да. На самом деле, вы можете довольно удобно извлечь это, используя комбинацию функций Excel() и ИНДЕКС-ПОИСКПОЗ.

Чтобы узнать подробности о функции CELL в Excel, перейдите по этой ссылке.

Мы будем использовать следующую формулу:

Смотрите, мы получили ссылку на ячейку сотрудника с максимальной зарплатой. Это Джек Альфред в C14.

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

Да. Ты прав. Формула будет такой:

Смотрите, зарплата сотрудника с максимальным ID составляет $20000. Он находится в ячейке D18.

3. Найти несколько максимальных значений с помощью функции ФИЛЬТР

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

Когда мы получили сотрудника с максимальной зарплатой, мы получили Джека Альфреда.

Но есть еще один сотрудник, который получает максимальную зарплату, $35000. Это Алиша Стоукс в камере C15.

Но формула ИНДЕКС-ПОИСКПОЗ предоставила нам только первую из них.

Так как же решить эту проблему?

Вы можете использовать функцию ФИЛЬТР() Excel, чтобы извлечь все значения вместе.

Чтобы узнать подробности о функции FILTER(), перейдите по этой ссылке.

Мы будем использовать следующую формулу:

Смотрите, у нас есть оба сотрудника с максимальной зарплатой, Джек Альфред и Алиша Стоукс.

Примечание. Функция ФИЛЬТР() доступна только в Office365.

4. Найдите максимальное значение с помощью критериев, используя функции MAX и IF

Теперь последняя проблема. Если кто-то спросит вас, как зовут сотрудника с максимальным ID, но зарплатой больше $20000, что вы будете делать?

Очень просто. Вы можете использовать функции ИНДЕКС-ПОИСКПОЗ() или ФИЛЬТР(), как указано выше, с комбинацией функций МАКС() и ЕСЛИ() Excel.

Следуйте приведенным ниже примерам.

Случай 1: одиночные критерии

Чтобы извлечь имя сотрудника с максимальным идентификатором по одному критерию, зарплата которого превышает 20 000 долларов США, формула будет следующей:

Итак, сотрудник с максимальным идентификатором, но с окладом выше 20 000 – это Джек Альфред.

И, очевидно, если вы хотите, вы можете извлечь ссылку на ячейку сотрудника с максимальным идентификатором, но зарплатой выше 20000 долларов США.

Формула будет такой:

Смотрите, необходимая ссылка на ячейку – C14.

Теперь, если хотите, вы также можете написать формулу, используя функцию ФИЛЬТР. Это пригодится, когда есть несколько сотрудников, соответствующих схожим критериям.

Формула будет такой:

Видите, теперь у нас есть и Джек Альфред, и Грегори Миллс, сотрудники с максимальным ID, но с зарплатой выше 20 000 долл. США.

Случай 2: несколько критериев

Я. И введите несколько критериев

Теперь давайте сделаем еще одну интересную вещь.

Попробуйте выяснить, у какого сотрудника максимальная зарплата, но идентификатор от 100 до 500.

Это проблема множественных критериев типа AND.

Формула будет такой:

Итак, это снова Джек Альфред.

При желании вы можете извлечь ссылку на ячейку, используя эту формулу:

Мы видим, что необходимая ссылка на ячейку — C14.

ii. ИЛИ Введите несколько критериев

А чтобы узнать сотрудника с максимальной зарплатой, но ID либо меньше 200, либо больше 400, формула будет такой:

Смотрите, это Стив Мур.

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

Смотрите, необходимая ссылка на ячейку – C11.

Заключение

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

Дополнительная литература

Как рассчитать среднее, минимальное и максимальное значения в Excel (4 простых способа)
Установка минимального и максимального значения в Excel (6 простых способов)
Excel MIN и MAX в одной формуле

Рифат Хассан

Здравствуйте! Добро пожаловать в мой профиль. Здесь я буду публиковать статьи, связанные с Microsoft Excel. Я страстный инженер-электрик со степенью бакалавра в области электротехники и электронной техники Бангладешского инженерно-технологического университета. Помимо академических занятий, я всегда люблю идти в ногу с революцией в технологиях, к которой мир стремится изо дня в день. Я прилежна, ориентирована на карьеру и готова дорожить знаниями на протяжении всей жизни.

Предположим, что у меня есть следующий диапазон данных, столбец A содержит названия продуктов, а столбец B содержит количество заказов. Теперь я хочу найти максимальную стоимость заказа продукта KTE, как показано на следующем снимке экрана. Как в Excel мы можем извлечь максимальное или минимальное значение на основе одного или нескольких критериев?

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

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

<р>1. Введите эту формулу: =MAX((A2:A13=D2)*B2:B13) в нужную ячейку, см. снимок экрана:

Советы. В этой формуле: A2:A13 — это ячейки диапазона, содержащие критерии, D2 — это критерий, на основе которого вы хотите найти максимальное значение, B2:B13 — это диапазон, который возвращает соответствующее значение.

<р>2. Затем нажмите клавиши Ctrl + Shift + Enter вместе, чтобы получить максимальное значение KTE, см. снимок экрана:

Примечание: чтобы получить минимальное значение на основе определенного критерия, введите эту формулу =MIN(IF(A2:A13=D2,B2:B13)) , после ввода формулы необходимо нажать Ctrl + Shift + Enter ключи вместе, то вы получите следующий результат:

Найти максимальное или минимальное значение по нескольким критериям

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

Возьмем, к примеру, следующие данные: мне нужно найти максимальный или минимальный заказ KTE в январе:

<р>1. Введите эту формулу в ячейку, в которую вы хотите поместить результат: =MAX(IF(A2:A13=F1,IF(B2:B13=F2,C2:C13))), см. снимок экрана:

Советы. В этой формуле: A2:A13 — данные, содержащие критерий1, B2:B13 — диапазон данных, содержащий критерий2, F1 и F2 — это критерии, на которых вы основываетесь, C2:C13 относится к диапазону, который вы хотите вернуть максимальное значение.

<р>2. Затем одновременно нажмите клавиши Ctrl + Shift + Enter, максимальное значение, при котором соответствующее значение в A2:A13 равно значению в F1, и соответствующее значение в B2 :B13 равно возвращенному значению F2.

Примечание. Чтобы получить минимальное значение на основе этих критериев, примените следующую формулу: =MIN(IF(A2:A13=F1,IF(B2:B13=F2,C2:C13))) и не забудьте нажать Сочетание клавиш Ctrl + Shift + Enter.

С помощью Kutools for Excel's Advanced Combine Rows можно быстро объединить несколько повторяющихся строк в одну запись на основе ключевых столбцов, а также применить некоторые вычисления, такие как сумма, среднее, количество и т. д., для других столбцов.

1. Укажите ключевой столбец, на основе которого вы хотите найти максимальное или минимальное значение другого столбца;
2. Выберите один расчет, который вам нужен.

Kutools for Excel: с более чем 200 удобными надстройками Excel, которые можно бесплатно попробовать без ограничений в течение 60 дней. Скачать и бесплатно попробовать прямо сейчас!

Читайте также: