Выделить строку с нижним 3 Значения с критериями

Posted on: 2020年10月22日 2020年11月12日
Tags: conditional-formatting

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

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

Общая формула в условном форматировании

=AND(check cell=criteria, value cell<=SMALL(IF(criteria range=criteria,value range),3))

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

Критерии: критерии, которые вы хотите отфильтровать.

Ячейка значения: это ячейка, содержащая числовое значение. Мы сравним его с 3-м наименьшим значением в своей категории в диапазоне значений. Диапазон критериев: это абсолютный диапазон, содержащий критерии.

Диапазон значений: диапазон, содержащий все значения.

Давайте посмотрим на примере, чтобы прояснить ситуацию.

Пример: выделите три последних продажи, осуществленных Отделом 1

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

Я хочу выделить три самых последних продажи, осуществленных Отделом 1.

Давайте определим переменные, которые у нас есть.

Проверить ячейку: $ B2 (* мы хотим проверить, содержит ли B2 «дилерский центр 1».

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

Критерии: «Отдел 1»

Ячейка значения: $ C2 Диапазон критериев: $ B $ 2: $ B $ 12 Диапазон значений: $ C $ 2: $ C $ 12 Теперь, когда мы знаем все аргументы, которые необходимо передать в формулу, пора реализовать приведенную выше общую формулу для данных установлен.

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

В текстовом поле ниже введите эту формулу. Выберите форматирование с помощью кнопки форматирования. Я использовала оранжевую (наверное, пол не судите) заливку.

Нажмите кнопку ОК. И дело сделано. Выделены три последних или, скажем, самые маленькие 3 продажи из отдела 1.

Как это работает?

Давайте разберемся в формуле изнутри.

Эта формула является формулой массива, но сочетание клавиш CTRL + SHIFT + ENTER не требуется в условном форматировании.

Поскольку у нас есть два условия, первое — это Дилерский центр 1, а второе — значения, меньшие или равные 3-му наименьшему значению в Дилерском центре 1. Оба должны быть ИСТИННЫ. Для этого мы используем link: / logic-formulas-microsoft-excel-and-function [AND function].

_ $ B2 = «Автосалон 1» _

Первый критерий легко проверить. Мы просто помещаем это выражение $ B2 = «Автосалон 1». Будет ИСТИНА, если текущая ячейка B2 содержит

Автосалон 1.

_ $ C2 ⇐ _ link: / statistics-formulas-excel-small-function [SMALL] _ (_ link: / tips-if-condition-in-excel [IF] _ ($ B $ 2: $ B $ 12 = «Дилерский центр 1», $ C $ 2: $ C $ 12), 3) _ * Для второго критерия нам нужно отфильтровать Дилерский центр 1 и найти в нем 3-е наименьшее значение. Затем сравните ячейку критериев с 3-м наименьшим значением. Это делается с помощью оператора $ C2 ⇐ link: / statistics-formulas-excel-small-function [SMALL] (link: / tips-if-condition-in-excel [IF] ($ B $ 2: $ B $ 12 = «Автосалон 1», $ C $ 2: $ C $ 12), 3)

Когда мы его вычислим, эта формула решит так:

Мы проверяем диапазон $ B $ 2: $ B $ 12 и получаем ячейки, содержащие «Дилерский центр 1»

используя оператор $ B $ 2: $ B $ 12 = «Дилерский центр 1». У нас будет массив ИСТИНА и ЛОЖЬ.

$C2<=SMALL(IF({TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE},$C$2:$C$12),3)

Для каждого ИСТИНА значение будет возвращено из диапазона значений $ C $ 2: $ C $ 12, и у нас будет другой массив.

$C2<=SMALL({12;FALSE;25;FALSE;FALSE;FALSE;FALSE;18;FALSE;FALSE;14},3)

Теперь link: / statistics-formulas-excel-small-function [SMALL Function] вернет третье наименьшее значение из этого массива. (FLASE игнорируется).

Теперь у нас есть:

$C2<=18

Наконец, формула

=AND($B2="Dealership 1",$C2<=18)

Теперь он проверяет, содержит ли B2 пункт «Дилерский центр» 1. Это действительно так. Следовательно, это ИСТИНА

=AND(TRUE,$C2<=18)

Затем он проверяет, меньше ли C2 или равно 18 (третье место по объему продаж в представительстве 1). Это ИСТИНА (12).

=AND(TRUE, TRUE)

Поскольку оба утверждения ИСТИНА, формула возвращает ИСТИНА, и наша первая СТРОКА выделяется цветом. То же самое происходит в каждом ряду.

Малоуу !!! Это было сложно объяснить. Надеюсь, я был достаточно ясен, чтобы вы поняли метод. На практике это просто.

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

Это сделает формулу более гибкой.

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

Формула в условном форматировании:

=AND($B2=$E$1,$C2<=SMALL(IF($B$2:$B$12=$E$1,$C$2:$C$12),3))

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

Статьи по теме:

link: / conditional-formatting-conditional-formatting-based-on-another-cell-value [Условное форматирование на основе другого значения ячейки]

link: / tips-conditional-formatting-with-if-statement [IF и условное форматирование]

link: / conditional-formatting-conditional-formatting-with-formula-2016 [Условное форматирование с формулой 2016]

link: / formating-in-vba-conditonal-formatting-using-microsoft-excel-vba [Условное форматирование с использованием VBA в Microsoft Excel]

link: / conditional-formatting-highlight-cells-that-contain-specific-text [Выделить ячейки, содержащие определенный текст]

link: / summing-how-to-sum-multiple-columns-with-condition [Как суммировать несколько столбцов с условием]