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

Обновлено: 21.11.2024

Эксперт Office Сьюзен Харкинс научит вас двум способам выделения наименьшего и наибольшего значения в строке или столбце Excel.

Поиск наименьшего или наибольшего значения в строке или столбце Excel
прост, используя функции MIN() и MAX() соответственно. Просто вставьте
функцию и укажите диапазон.

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

Существует несколько способов достижения
результатов, которых вы хотите; метод, который вы выбираете, зависит от вас. Загружаемые демонстрационные файлы (в форматах .xls и .xlsx) включают рабочую книгу Excel, используемую в этой статье.

Легкий способ

Самый простой способ применить этот конкретный условный формат
состоит в том, чтобы ввести соответствующую функцию, а затем создать условный формат на основе
результатов этой функции. Например, предположим, что мы хотим выделить
самую низкую цену для каждой детали на листе (рис. A). Как видно на рисунке A,
функции MIN() в столбце F возвращают минимальное значение для каждой строки.

После добавления функции MIN() вы готовы добавить
правило условного форматирования. Выполните следующие действия.

<р>1. Выберите диапазон данных. В данном случае это
B2:E4.

<р>2. На вкладке "Главная" нажмите "Условное форматирование"
в группе "Стили". В Excel 2003 выберите "Условное форматирование" в меню
Формат и перейдите к шагу 5.

<р>3. В появившемся диалоговом окне выберите «Новое правило».

<р>4. На верхней панели выберите параметр «Использовать формулу для
определения форматируемых ячеек».

<р>5. В
нижней панели введите формулу =B2=$F2. В Excel 2003 сохраните условие по умолчанию «Формула равно» и
введите ту же формулу.

<р>6. Нажмите кнопку «Формат».

<р>7. В появившемся диалоговом окне щелкните вкладку «Заливка». В
Excel 2003 перейдите на вкладку "Шаблоны".

<р>8. Выберите цвет и нажмите ОК. На рисунке B показана
формула и предварительный просмотр форматирования.

<р>9. Нажмите OK, чтобы применить новый условный формат. Как
вы можете видеть на рисунке C, выделенное значение в каждой строке совпадает со значением,
возвращаемым функцией MIN() в столбце F.

Чтобы выделить наибольшее значение, используйте MAX() вместо MIN().
Чтобы найти минимальное или максимальное значение в столбце, добавьте MIN() или MAX(),
соответственно, в конец диапазона данных и используйте эту формулу
правило на шаге 5 < i>=B2=B$5.

Минус столбца F

Добавление нового столбца (или строки) обычно не вызывает проблем; если это проблема, вы можете скрыть новый столбец или строку. (Я не рекомендую скрывать данные как
правило: легко забыть данные, которые вы не видите.) Если вы не хотите добавлять
функцию на лист, вы можете добавить функцию к правилу. Чтобы использовать этот
метод, выполните предыдущие шаги, но на шаге 5 введите эту формулу
rule =B2=MIN($B2:$E2). (Если вы работаете с одним и тем же образцом листа, удалите
первое правило форматирования, прежде чем применять новое.)

Результаты те же, но правило не зависит от
функций в столбце F. Если вы удалите эти функции, условное форматирование
продолжит работать. Как и прежде, чтобы выделить максимальное значение, используйте функцию MAX()
. Чтобы оценивать столбцы вместо строк, используйте эту формулу правила =B2=MAX(B$2:B$4).

Рычажный ключ

Одна из ячеек в
диапазоне данных (E3) пуста, так как ввод 0 для обозначения того, что поставщик
не предлагает этот продукт, отрицательно влияет на формат (рис. D). . Условный формат выделяет 0, но 0 не является самой низкой
ценой; в этом случае 0 означает, что поставщик не предлагает эту конкретную
запчасть.

Если вы работаете с набором данных, для которого требуется 0, но
хотите, чтобы условное форматирование их не учитывало, используйте это правило формулы =B2=(MIN(IF($B2:$E2 >0,$B2:$E2))).

Как видно на рис. E, это правило игнорирует значение 0, а функция MIN() в F3 — нет. Вы можете использовать аналогичную формулу в столбце F как массив =MIN(IF($B2:$E2>0,$B2:$E2)).

Чтобы ввести формулу в виде массива, введите ее как обычно,
но вместо нажатия [Enter] нажмите [Ctrl]+[Shift]+[Enter]; это отобразит квадратные скобки (<>) вокруг формулы, чтобы указать, что это формула массива
. Затем скопируйте массив, чтобы завершить столбец (или строку).

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

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

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

Функция МАЛЕНЬКИЙ в Excel

МАЛЕНЬКИЙ – это статистическая функция, которая возвращает n-е наименьшее значение в наборе данных.

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

  • Массив — массив или диапазон ячеек, из которых извлекается наименьшее значение.
  • K — целое число, указывающее позицию от наименьшего возвращаемого значения, т. е. k-го наименьшего значения.

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

Основная формула МАЛЕНЬКИЙ в Excel

Формулу МАЛЕНЬКИЙ в ее базовой форме очень легко построить — достаточно указать диапазон и позицию от наименьшего элемента, который нужно вернуть.

В списке чисел в B2:B10 предположим, что вы хотите извлечь 3-е наименьшее значение. Формула проста:

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

4 вещи, которые вы должны знать о функции SMALL

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

  1. Любые пустые ячейки, текстовые значения и логические значения TRUE и FALSE в аргументе массив игнорируются.
  2. Если массив содержит одну или несколько ошибок, возвращается ошибка.
  3. Если в массиве есть дубликаты, ваша формула может привести к "ничьям". Например, если две ячейки содержат число 1, а функция НАИМЕНЬШИЙ настроена на возврат наименьшего и второго наименьшего значения, вы получите 1 в обоих случаях.
  4. Предполагая, что n – это количество значений в массиве, МАЛЕНЬКИЙ(массив,1) вернет наименьшее значение, а МАЛЕНЬКИЙ(массив,n) выберет наибольшее значение.

Как использовать функцию МАЛЕНЬКИЙ в Excel — примеры формул

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

Найти последние 3, 5, 10 и т. д. значения

Как вы уже знаете, функция НАИМЕНЬШИЙ предназначена для вычисления n-го наименьшего значения. В этом примере показано, как это сделать наиболее эффективно.

В приведенной ниже таблице предположим, что вы хотите найти 3 нижних значения. Для этого наберите в отдельные ячейки (в нашем случае D3, D4 и D5) числа 1, 2 и 3. Затем введите следующую формулу в E3 и перетащите ее вниз через E5:

В E3 формула извлекает наименьшее значение, используя число в D3 в качестве аргумента k. Главное — указать правильные ссылки на ячейки, благодаря которым формула правильно копируется в другие ячейки: абсолютные для массива и относительные для k.

Не хотите вручную вводить ранги? Используйте функцию ROWS с расширением ссылки на диапазон, чтобы указать значение k. Для этого мы делаем абсолютную ссылку для первой ячейки (или только блокируем координату строки, например B$2) и относительную ссылку для последней ячейки:

В результате ссылка на диапазон расширяется по мере того, как формула копируется вниз по столбцу. В D2 ROWS(B$2:B2) возвращает 1 для k, и формула возвращает наименьшую стоимость. В D3 ROWS(B$2:B3) возвращает 2, и мы получаем 2-е место с наименьшей стоимостью и т. д.

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

Суммировать N нижних значений

Хотите найти сумму наименьших n значений в наборе данных? Если вы уже извлекли значения, как показано в предыдущем примере, самым простым решением будет формула СУММ, например:

Или вы можете создать независимую формулу, используя функцию МАЛЕНЬКИЙ вместе с СУММПРОИЗВ:

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

Функция СУММ даст тот же результат:

Примечание. Если вы используете ссылки на ячейки, а не константы массива для k, вам нужно нажать Ctrl + Shift + Enter, чтобы сделать его формулой массива. В Excel 365, поддерживающем динамические массивы, СУММ МАЛЕНЬКИЙ в любом случае работает как обычная формула.

Как работает эта формула:

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

Функция СУММПРОИЗВ или СУММ складывает числа в массиве и выводит итог. Вот и все!

Формула INDEX MATCH SMALL для получения наименьших совпадений

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

  • Return_array – это диапазон, из которого извлекаются связанные данные.
  • Lookup_array — это диапазон, в котором нужно искать наименьшее n-е значение.
  • N – позиция с наименьшим интересующим значением.

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

=ИНДЕКС($A$2:$A$10, MATCH(МАЛЕНЬКИЙ($B$2:$B$10, D3), $B$2:$B$10, 0))

Где A2:A10 — названия проектов, B2:B10 — затраты, а D3 — ранг от наименьшего.

Скопируйте формулу в ячейки ниже (E4 и E5), и вы получите названия 3 самых дешевых проектов:

  • Это решение отлично подходит для набора данных, в котором нет дубликатов. Однако два или более повторяющихся значения в числовом столбце могут создать «связь» в ранжировании, что приведет к неправильным результатам. В этом случае, пожалуйста, используйте немного более сложную формулу, чтобы разорвать связи.
  • В Excel 365 эту задачу можно выполнить с помощью новых функций динамического массива. Помимо того, что этот подход намного проще, он автоматически решает проблему связей. Подробную информацию см. в разделе Как отфильтровать нижние значения N в Excel.

Сортировка чисел от меньшего к большему с помощью формулы

Я думаю, все знают, как упорядочивать числа с помощью функции сортировки Excel. Но знаете ли вы, как выполнить сортировку с помощью формулы? Пользователи Excel 365 могут легко сделать это с помощью новой функции СОРТИРОВКИ. В Excel 2019, 2016 и более ранних версиях СОРТИРОВКА не работает, увы. Но немного веры, и МАЛЕНЬКИЙ придет на помощь :)

Как и в первом примере, мы используем функцию ROWS с расширяющейся ссылкой на диапазон для увеличения k на 1 в каждой строке, в которую копируется формула:

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

МАЛЕНЬКАЯ формула Excel для даты и времени

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

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

МАЛЕНЬКАЯ формула для поиска трех самых ранних дат:

МАЛЕНЬКАЯ формула для получения кратчайшего 3 раза:

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

Найти предыдущую дату, ближайшую к сегодняшней или указанной дате

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

Со списком дат в B2:B10 и целевой датой в E1 следующая формула вернет предыдущую дату, ближайшую к целевой дате:

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

Чтобы найти прошлую дату, ближайшую к сегодняшней, используйте функцию СЕГОДНЯ для критериев СЧЁТЕСЛИ:

Совет. Чтобы предотвратить ошибки в ситуации, когда дата, соответствующая вашим критериям, не найдена, вы можете обернуть формулу функцией ЕСЛИОШИБКА, например так:

Как работают эти формулы:

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

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

Если бы в нашем наборе данных появилось 1 августа 2020 года (целевая дата в E1), это была бы седьмая по величине дата в списке. Следовательно, есть шесть дат меньше, чем это. Это означает, что 6-я наименьшая дата — это предыдущая дата, ближайшая к целевой дате.

Итак, сначала мы вычисляем, сколько дат меньше даты в E1 (результат равен 6):

А затем подставьте счетчик во 2-й аргумент SMALL:

Чтобы получить предыдущую дату (в нашем случае это пятая по величине дата), мы вычитаем 1 из результата COUNTIF.

Как выделить нижние значения в Excel

Чтобы выделить наименьшие n значений в таблице с условным форматированием Excel, вы можете использовать либо встроенный параметр Верх/Низ, либо настроить собственное правило на основе формулы МАЛЕНЬКИЙ. Первый метод быстрее и проще в применении, а второй обеспечивает больший контроль и гибкость. Следующие шаги помогут вам создать собственное правило:

  1. Выберите диапазон, в котором вы хотите выделить нижние значения. В нашем случае числа находятся в формате B2:B10, поэтому мы выбираем его. Если вы хотите выделить строки целиком, выберите A2:B10.
  2. На вкладке Главная в группе Стили нажмите Условное форматирование > Новое правило.
  3. В диалоговом окне Новое правило форматирования выберите Использовать формулу для определения форматируемых ячеек.
  4. В поле Формат значений, где эта формула верна, введите формулу, подобную этой:

Где B2 — это крайняя левая ячейка числового диапазона, который нужно проверить, $B$2:$B$10 — весь диапазон, а 3 — n нижние значения для выделения.

Готово! Нижние 3 значения в столбце B выделены:

Функция МАЛЕНЬКИЙ Excel не работает

  • Массив пуст или не содержит ни одного числового значения.
  • Значение k меньше нуля (глупая опечатка может стоить вам нескольких часов устранения неполадок!) или превышает количество значений в массиве.

Вот как использовать МАЛЕНЬКУЮ формулу в Excel для поиска и выделения последних чисел в наборе данных. Если вы знаете какие-либо другие сценарии, в которых эта функция пригодится, вы можете поделиться ими в комментариях. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

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

См. решение в других версиях Excel:

Вопрос: есть ли в Microsoft Excel 2010 способ закрасить одну ячейку зеленым цветом, если это самое высокое значение в диапазоне ячеек, и закрасить другую ячейку красным, если это наименьшее число в диапазоне ячеек?< /p>

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

Сначала выделите диапазон ячеек. В этом примере мы выбрали ячейки с A1 по A5.

Выберите вкладку «Главная» на панели инструментов в верхней части экрана. Затем в группе «Стили» щелкните раскрывающийся список «Условное форматирование» и выберите Управление правилами.

Когда появится окно диспетчера правил условного форматирования, нажмите кнопку "Новое правило", чтобы ввести первое условие.

Когда появится окно "Новое правило форматирования", выберите в качестве типа правила "Форматировать только верхние или нижние значения".

Затем выберите Верх в первом раскрывающемся списке и введите 1 в последнем поле. В нашем примере мы выбрали только первое верхнее значение.

Далее нам нужно выбрать форматирование, которое будет применяться при выполнении этого условия. Для этого нажмите кнопку Формат.

Когда появится окно «Формат ячеек», выберите вкладку «Заполнить». Затем выберите цвет, который вы хотели бы видеть в качестве максимального значения в диапазоне. В этом примере мы выбрали зеленый. Затем нажмите кнопку ОК.

Когда вы вернетесь в окно «Новое правило форматирования», вы должны увидеть предварительный просмотр форматирования в поле «Предварительный просмотр». В этом примере поле предварительного просмотра показывает зеленый цвет в качестве цвета заливки. Затем нажмите кнопку ОК.

Вы вернетесь в окно Диспетчера правил условного форматирования.

Вам нужно будет снова нажать кнопку "Новое правило".

Когда появится окно «Новое правило форматирования», нам нужно настроить второе условие.

В качестве типа правила выберите Форматировать только верхние или нижние ранговые значения.

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

Далее нам нужно выбрать форматирование, которое будет применяться при выполнении этого второго условия. Для этого нажмите кнопку Формат.

Когда появится окно «Формат ячеек», выберите вкладку «Заполнить». Затем выберите цвет, который вы хотели бы видеть для наименьшего значения в диапазоне. В этом примере мы выбрали красный. Затем нажмите кнопку ОК.

Когда вы вернетесь в окно «Новое правило форматирования», вы должны увидеть предварительный просмотр форматирования в поле «Предварительный просмотр». В этом примере поле предварительного просмотра показывает красный цвет в качестве цвета заливки. Затем нажмите кнопку ОК.

Теперь ваше окно условного форматирования должно выглядеть так.

Нажмите кнопку ОК.

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

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

Допустим, вы хотите выделить зеленым цветом ячейку с наибольшим значением. Поскольку 49 – это максимальное число в столбце B, ячейка B4 должна быть выделена.

  1. Выберите диапазон данных, в котором вы хотите выделить наибольшее значение. Затем на ленте выберите Главная > Условное форматирование > Новое правило…

  1. В окне условного форматирования (1) выберите «Форматировать только верхние или нижние значения для типа правила». Затем (2) введите 1 рядом с Top (поскольку вы хотите выделить одно значение — самое высокое) и (3) нажмите Format…

  1. В окне «Формат ячеек» (1) перейдите на вкладку «Заливка», (2) выберите зеленый цвет и (3) нажмите «ОК».
    Обратите внимание, что вы также можете перейти на вкладки "Шрифт", "Граница" и/или "Число", чтобы настроить другие параметры форматирования.

  1. В окне «Новое правило форматирования» есть предварительный просмотр форматирования ячейки, к которому будет применяться это правило. Нажмите "ОК".

Теперь ячейка B4 выделена зеленым цветом, потому что 49 — это самое высокое значение в диапазоне B2:B10.

Выделение основных n значений

Вы также можете использовать условное форматирование, чтобы выделить первые значения n в диапазоне. Выделим первые три значения в диапазоне B2:B10.

  1. Выберите диапазон данных, для которого вы хотите выделить первые три значения, и на ленте перейдите в раздел Главная > Условное форматирование > Верхние/нижние правила > Первые 10 элементов…

  1. По умолчанию во всплывающем окне форматируются первые 10 элементов, но вам нужны только три.
    (1) Введите 3 для верхних ячеек и (2) нажмите "ОК", оставив форматирование по умолчанию (светло-красная заливка с темно-красным текстом).

Выделяются три первых значения в диапазоне данных (49, 32 и 21).

Если вы вернетесь к шагу 1, то увидите, что в правилах Top/Bottom также есть вариант для 10 последних элементов. Выберите этот параметр, чтобы выделить нижние значения n в диапазоне.

Выделить наибольшее значение в Google Таблицах

Чтобы выделить наибольшее значение в диапазоне в Google Таблицах, выполните следующие действия:

  1. Выберите диапазон данных, для которого вы хотите выделить наибольшее значение, и в меню выберите Формат > Условное форматирование.

  1. В окне условного форматирования справа (1) выберите Пользовательская формула в разделе Правила формата и (2) введите формулу:

Наконец, (3) нажмите OK, оставив стиль форматирования по умолчанию (зеленая заливка фона). Это можно изменить позже, щелкнув значок цвета заливки.

Условное форматирование на основе приведенной выше формулы будет применяться, если результат формулы равен TRUE. В этом случае проверяется, равна ли каждая ячейка в диапазоне максимальному значению в диапазоне (результат функции MAX). Поскольку наибольшее значение находится в ячейке B4, результатом формулы для этой ячейки является TRUE, и ячейка B4 выделяется.

Выделить наименьшее значение в Google Таблицах

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

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