Выделите Top Sales через условное форматирование

Posted on: 2020年10月22日 2020年11月12日
Tags: tips

В этой статье мы научимся выделять самые популярные продажи с помощью условного форматирования.

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

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

Пример:

Все это может сбивать с толку. Давайте разберемся, как пользоваться функцией, на примере. Здесь в диапазоне A2: C7 у меня есть формула = СЛУЧМЕЖДУ (10,100). Эта формула генерирует случайное число от 10 до 100 в каждой ячейке всякий раз, когда в файл вносятся изменения. Я хочу выделить 3 наименьших числа в этом диапазоне.

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

Выберите диапазон.
Идти домой ? Условное форматирование? Новое правило.
Здесь выберите «форматировать только верхнее или нижнее значение»
В раскрывающемся списке выберите внизу.
В поле «Значение» напишите 3.
Выберите форматирование ячейки для трех нижних значений. Я выбрал красную заливку.

Хит ОК. Сделано. Теперь три нижних значения в этом диапазоне будут динамически выделяться красной заливкой.

Это было легко. Но все становится немного сложнее, когда мы добавляем еще один критерий для выделения нижних значений.

Легко просто выделить 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 Теперь, когда мы знаем все аргументы, которые необходимо передать в формулу, пора реализовать приведенную выше общую формулу для данных установлен.

Выберите данные. Идти домой ? условное форматирование? Новое правило

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

In the text box below, write this formula

=AND($B2=»Dealership

1″,$C2⇐SMALL(IF($B$2:$B$12=»Dealership

1″,$C$2:$C$12),3))

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

Нажмите кнопку ОК. И дело сделано. Выделены три последних или, скажем, самые маленькие 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 можно найти здесь. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected].

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

link: / lookup-formulas-find-the-partial-match-number-from-data-in-excel [Найти частичное совпадение-число из данных в Excel]: найти подстроку, совпадающую со значениями ячеек, используя формулу в Excel.

link: / conditional-formatting-highlight-cells-that-contain-specific-text [Как выделить ячейки, содержащие определенный текст в Excel]: выделить ячейки на основе формулы, чтобы найти конкретное текстовое значение в ячейке в Excel .

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

link: / tips-conditional-formatting-with-if-statement [Функция ЕСЛИ и условное форматирование в Excel]: Как использовать условие ЕСЛИ в условном форматировании с формулой в Excel.

link: / conditional-formatting-conditional-formatting-with-formula-2016 [Выполнить условное форматирование с формулой 2016]: изучите все функции условного форматирования по умолчанию в Excel.

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