Как выделить первые 3, 5, 10 значений в Excel

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

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

Выделите верхние или нижние значения N в диапазоне

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

Выделение верхних и нижних значений с помощью встроенного правила

Самый быстрый способ выделить первые 3, 5, 10 (или последние n) значений в Excel — использовать встроенное правило условного форматирования. Вот как:

Выберите диапазон, в котором вы хотите выделить числа.
На Дом вкладка, в Стили щелкните Условное форматирование.
В раскрывающемся меню выберите Верхние/нижние правила, а затем нажмите 10 первых элементов… или 10 последних элементов…
В открывшемся диалоговом окне укажите количество элементов для выделения и выберите один из предопределенных параметров форматирования. Чтобы применить форматирование, которого нет в списке, щелкните Пользовательский формат… . Вносимые здесь изменения немедленно отражаются в вашем наборе данных. Итак, если вы довольны результатами, нажмите ХОРОШО.

В этом примере мы решили выделить 3 первых значения со значением по умолчанию. Светло-красная заливка и получить этот результат:

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

Если вам нужно больше возможностей, чем предусмотрено в Верх/Низ пресеты, вы можете создать новое правило с нуля:

Выберите диапазон с числовыми значениями.
На Дом вкладка, нажмите Условное форматирование > Новое правило.
в Новое правило форматирования диалоговом окне выберите «Форматировать только верхние или нижние ранжированные значения».
В раскрывающемся списке выберите «Сверху» или «Снизу» и введите количество значений для выделения в поле рядом с ним.
Нажмите кнопку «Формат» и выберите любое форматирование, которое вы хотите для Шрифт, Граница а также Наполнять.
Нажмите ХОРОШО.

Например, вот как можно выделить 5 лучших значений зеленым цветом фона.

Выделение верхних или нижних значений с помощью формулы

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

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

Функция LARGE для затенения верхних чисел:

знак равноupper_left_cell>=БОЛЬШОЙ(диапазон, н)

Функция SMALL для затенения нижних чисел:

знак равноupper_left_cell<=МАЛЕНЬКИЙ(диапазон, н)

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

Введите количество значений для выделения в предопределенной ячейке ввода. В этом примере мы вводим количество верхних значений в F2 и количество нижних значений в F3.
Выберите диапазон чисел (A2:C8).
На Дом вкладка, нажмите Условное форматирование > Новое правило.
в Новое правило форматирования диалоговом окне выберите Использовать формулу, чтобы определить, какие ячейки следует форматировать.
в Форматировать значения, где эта формула верна введите одну из следующих формул:
Чтобы выделить первые n значений:
=A2>=НАИБОЛЬШИЙ($A$2:$C$8, $F$2)
Чтобы выделить нижние n значений:
=A2<=МАЛЕНЬКИЙ($A$2:$C$8, $F$3)
Где $A$2:$C$8 — применяемый диапазон, A2 — крайняя левая ячейка диапазона; F2 и F3 — значения n.
Обратите внимание, что мы блокируем исходный диапазон и входные ячейки абсолютными ссылками и используем относительную ссылку для верхней левой ячейки.
Нажмите кнопку «Формат» и выберите нужное форматирование.
Нажмите ХОРОШО дважды, чтобы закрыть оба окна.

Более подробные инструкции см. в разделе Создание правила условного форматирования с формулой.

Сделанный! Верхние 3 и нижние 3 значения выделены разными цветами.

Позже, если вы захотите выделить, скажем, первые 5 значений, вы просто наберете 5 в F2, и Excel автоматически применит изменение.

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

Функция НАИБОЛЬШИЙ возвращает n-е наибольшее значение в указанном диапазоне. В нашем случае она находит 3-е по величине значение в A2:C8, равное 92. Формула сравнивает каждое число в выбранном диапазоне с 3-м по величине значением. Для любой ячейки, которая больше или равна 92, возвращается ИСТИНА, и применяется правило условного форматирования.

Как закрасить строки, содержащие верхние или нижние значения N

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

Чтобы показать первые n строк:

=$B2>=НАИБОЛЬШИЙ($B$2:$B$15, $E$2)

Чтобы показать нижние n строк:

=$B2<=МАЛЕНЬКИЙ($B$2:$B$15, $E$3)

Где:

$B2 — самая верхняя ячейка числового столбца.
$B$2:$B$15 — это числа для ранжирования.
$E$2 — это количество верхних строк для выделения.
$E$3 — количество нижних строк для выделения.

Правила применяются ко всей таблице, кроме строки заголовка (A2:B15).

Как и в предыдущем примере, эти правила являются гибкими. Формулы будут пересчитаны автоматически, как только вы измените числа во входных ячейках (E2 и E3), и Excel немедленно отразит эти изменения и выделит соответствующее количество строк.

Как выделить первые N значений в каждой строке

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

Например, чтобы выделить первые 3 числа в каждой строке таблицы ниже, используйте формулу:

=B2>=НАИБОЛЬШИЙ($B2:$G2, 3)

Шаги по созданию правила описаны в этот пример, поэтому здесь мы показываем только результат. Правило применяется ко всем числовым ячейкам (B2:G2):

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

=B2<=МАЛЕНЬКИЙ($B2:$G2, 3)

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

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

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

В этом примере правило применяется ко всем числовым ячейкам (B2:G10), но формула написана для строки 2:

=B2>=НАИБОЛЬШИЙ($B2:$G2, 3)

Функция НАИБОЛЬШИЙ находит третье по величине значение в B2:G2, равное 257. Формула проверяет, больше ли B2 или равно 275, и, если ИСТИНА, применяет условное форматирование к этой ячейке. Поскольку B2 является относительной ссылкой, Excel затем фактически «копирует» формулу в C2, D2 и т. д. Поскольку координаты столбца заблокированы знаком $ ($B2:$G2), диапазон не изменяется при сравнении других ячеек в тот же ряд.

Например, для C2 Excel будет оценивать эту формулу:

=C2>=НАИБОЛЬШИЙ($B2:$G2, 3)

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

=B3>=БОЛЬШОЙ($B3:$G3, 3)

И так далее.

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

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