Excel выделение несовпадающих значений
Каждый торговый представитель на фирме получает определенный месячный план продаж, который нужно выполнить. За прошлый месяц били получены определенные показатели по выполнению плана продаж. Естественно некоторые торговые агенты перевыполнили план, а некоторые не достигли выполнения плана. Допустимая норма отклонения от плана 15%. Следует выделить все показатели, которые вложились в норму с учетом допустимого отклонения.
Выделение цветом значений с допустимыми отклонениями
Для примера возьмем простую таблицу плана продаж и показатели его выполнения:
Следует автоматически выделить цветом все значения в столбце «Продажи», которые на 15% меньше или больше установленной нормы в столбце «План». Для этого:
- Выделите диапазон ячеек C2:C10 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
- Выберите опцию: «Форматировать только ячейки, которые содержат».
- В разделе «Изменение описание правила:» в каждое поле вводится своя формула отклонения сначала в меньшую а потом в большую сторону. Заполняем все параметры: 1–Значение ячейки, 2–между, 3–=B2*0,85, 4–=B2*0,15.
- Нажмите на кнопку «Формат», чтобы задать желаемый цвет заливки для ячеек. Например, зеленый. И нажмите ОК на всех открытых окнах.
Все относительные сумы фактических продаж, которые вкладываются в норму плана с учетом отклонения 15% – выделились цветом:
Excel сначала устанавливает придел значений, а потом проверяет каждую ячейку выделенного диапазона на соответствие ее значения установленным границам в критериях правила условного форматирования. Обратите внимание, что в полях ввода мы используем формулы, которые позволяют нам оптимизировать условия для любых значений, указанных в плане продаж.
Добавим нашему отчету по выполнению плана продаж немного интерактивности. Для этого необходимо немного усовершенствовать формулы, чтобы быстро вычислять возможные отклонение в Excel при изменяемых значениях:
- Нижняя граница: =B2-(B2*$D$2)
- Верхняя граница: =B2+(B2*$D$2)
Обратите внимание, что ссылка на ячейку $D$2 должна быть абсолютной!
Так как мы используем в формулах относительные значение, у нас появилась возможность гибко настраивать пороги нормы отклонения просто указав их в ячейке D2 с процентным форматом:
Теперь наш отчет стал интерактивным и может взаимодействовать с пользователем. При изменении значения в ячейке D2 изменяется количество выделенных ячеек цветом в соответствии с новой границей допустимой нормы.
Как сравнить два столбца и выделить при обнаружении в другом столбце?
Например, у меня есть рабочий лист со списком A и списком B, которые имеют одни и те же значения в этих двух списках, теперь я хотел бы сравнить эти два столбца, а затем выделить значения в обоих списках из списка A в получите следующий результат. В этой статье я расскажу о некоторых методах решения этой задачи в Excel.
Выделите ячейки, если они дублируются в другом столбце с условным форматированием
В Excel мы можем применить Условное форматирование для выделения тех же значений, когда они обнаружены в другом столбце, выполните следующие действия:
1. Выделите ячейки в столбце A, в которых вы хотите выделить ячейки, из которых выходят значения, в столбце C.
2. Затем нажмите Главная > Условное форматирование > Новое правило, В Новое правило форматирования диалоговое окно, щелкните Используйте формулу, чтобы определить, какие ячейки следует форматировать. из Выберите тип правила раздел, а затем введите эту формулу = ПОИСКПОЗ (A2; $ C $ 2: $ C $ 12,0) в Формат значений, где эта формула истинна текстовое поле, см. снимок экрана:
Внимание: В приведенной выше формуле: A2 это первая ячейка столбца, в которой вы хотите выделить те же значения, C2: C12 - это еще один диапазон столбцов, содержащий повторяющиеся значения, с которыми нужно сравнивать.
3. Затем нажмите Формат кнопку, чтобы перейти к Формат ячеек диалоговом окне и выберите нужный цвет под Заполнять вкладку, см. снимок экрана:
4. Затем нажмите OK > OK чтобы закрыть диалоговые окна, и теперь вы можете видеть, что значения как в списке A, так и в списке B выделены из списка A, см. снимок экрана:
Внимание: Если вы хотите выделить повторяющиеся значения в списке B, вам просто нужно сначала выбрать список B и поменять местами ссылки на ячейки в формуле следующим образом: = ПОИСКПОЗ (C2; $ A $ 2: $ A $ 15,0) в Новое правило форматирования Диалог.
Сравните два столбца и выделите ячейки, если они дублируются в другом столбце
Если вам нужно сравнить два столбца и выделить значения ячеек, если они дублируются в другом столбце, Kutools for Excel's Выберите одинаковые и разные ячейки функция может помочь вам выполнить эту работу быстро и легко. Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!
Выделите ячейки, если они дублируются в другом столбце с помощью Kutools for Excel
Если вы не знакомы с формулами в условном форматировании, здесь я могу порекомендовать удобный инструмент -Kutools for Excel, С его Выберите одинаковые и разные ячейки утилита, вы можете быстро выделить то же значение в другом столбце без какой-либо формулы.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Нажмите Кутулс > Выберите > Выберите одинаковые и разные ячейки, см. снимок экрана:
2. В Выберите одинаковые и разные ячейки диалоговом окне выполните следующие действия:
(1.) Выберите два диапазона, которые вы хотите сравнить, из Найдите значения в и Согласно информации ящики отдельно;
(2.) Выберите Каждый ряд под на основании раздел;
(3.) Выберите Те же ценности сформировать Найдите раздел;
(4.) Затем укажите цвет фона или цвет шрифта, значения которого вы хотите выделить под Обработка результатов.
3. После завершения настроек нажмите Ok кнопка, и появится окно подсказки, чтобы напомнить вам, сколько совпадающих ячеек выбрано и выделено, нажмите OK чтобы закрыть диалоговые окна, повторяющиеся значения остаются выделенными и выделяются в списке A, см. снимок экрана:
Внимание: Чтобы выделить повторяющиеся значения в списке B, переключите диапазоны с Найдите значения в и Согласно информации в диалоговом окне.
Демо: выделите ячейки, если они дублируются в другом столбце с помощью Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Как изменить цвет, если две ячейки не равны в Excel?
На листе есть два списка, которые вам нужно сравнить, а затем изменить цвет строки, если две соседние ячейки не равны, как показано на скриншоте ниже. Есть ли у вас какие-либо хорошие методы для выполнения этой работы в Excel?
Цвет ячеек, если они не равны, с помощью "Сравнить ячейки"
Цвет ячеек, если они не совпадают с условным форматированием
1. Выберите два списка, которые вы сравниваете, если они равны друг другу, и нажмите Главная > Условное форматирование > Новое правило.
2. в Новое правило форматирования диалога, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать., и введите эту формулу = $ A1 <> $ B1 в Форматировать значения, в которых эта формула верна. Затем нажмите Формат , чтобы открыть Формат ячеек диалоговом окне и выберите один цвет под Заполнять таб. Смотрите скриншот:
3. Нажмите OK > OK. Тогда ячейки будут раскрашены, если две ячейки не равны друг другу.
Цвет ячеек, если они не равны, с помощью "Сравнить ячейки"
Если вы хотите выделить и раскрасить ячейки, если они не равны друг другу, вы можете применить Kutools for ExcelАвтора Выберите одинаковые и разные ячейки утилита.
После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите два списка отдельно, удерживая Ctrl ключ. Смотрите скриншот:
2. Нажмите Кутулс > Выберите > Выберите одинаковые и разные ячейки, во всплывающем диалог, проверьте Каждый ряд и Разные клетки варианты и проверьте Заполните цвет фона or Цвет заливки шрифта варианты по мере необходимости, выберите один цвет, который вы хотите. Смотрите скриншот:
3. Нажмите Okпоявится диалоговое окно, напоминающее, сколько различных ячеек было выбрано и выделено. Нажмите OK , чтобы закрыть диалоговое окно напоминания. Смотрите скриншот:
Как выделить ячейку на основе значения соседней ячейки в Excel?
Если вам нужно выделить ячейку, в которой соседняя ячейка равна или больше ее, конечно, вы можете сравнить их одну за другой, но есть ли какие-либо хорошие и быстрые методы для решения задачи в Excel?
With Kutools for Excel's Compare Cells feature, you can quickly highlight the cells if equal or difference of the adjacent cell. Download and free trial Kutools for Excel now!
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
Выделить ячейки, если они равны соседним ячейкам
Скажем, когда вы хотите выделить ячейку, если соседняя ячейка равна ей, функция условного форматирования может оказать вам услугу, пожалуйста, сделайте следующее:
1. Выделите ячейки, в которых вы хотите выделить ячейки, если они равны соседним ячейкам, а затем щелкните Главная > Условное форматирование > Новое правило, см. снимок экрана:
2. В Новое правило форматирования диалоговое окно, нажмите Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила список, а затем введите эту формулу: = $ A2 = $ B2 в Значение формата, в котором эта формула верна текстовое поле, см. снимок экрана:
3. Затем нажмите Формат кнопку, чтобы перейти к Формат ячеек диалог под Заполнять Вкладка, укажите цвет, который вы хотите использовать, см. снимок экрана:
4, Затем нажмите OK > OK чтобы закрыть диалоги, и ячейки, которые равны соседним ячейкам, были выделены сразу, см. снимок экрана:
Выделите ячейки, если они равны или не равны соседним ячейкам с помощью Kutools for Excel
Если у вас есть Kutools for Excel, С его Сравнить ячейки утилита, вы можете быстро сравнить два столбца и найти или выделить одинаковые или разные значения для каждой строки.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Нажмите Кутулс > Сравнить ячейки, см. снимок экрана:
2. В Сравнить ячейки диалоговом окне выполните следующие действия:
- (1.) Выберите два столбца из Найдите значения в и Согласно информации текстовое поле отдельно;
- (2.) Выберите Те же клетки выделить ячейки, равные соседней ячейке;
- (3.) Наконец, укажите цвет ячейки или цвет шрифта, необходимый для выделения ячеек.
- (4.) И все ячейки, которые равны соседним ячейкам, были выделены сразу.
3. Чтобы выделить ячейки, которые не равны значениям соседних ячеек, выберите Разные клетки в Сравнить ячейки диалоговое окно, и вы получите следующий результат, который вам нужен.
Демо: выделите ячейки, если они равны или не равны соседним ячейкам с помощью Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Выделите ячейки, если они больше или меньше соседних ячеек
Чтобы выделить ячейки, если они больше или меньше соседних ячеек, сделайте следующее:
1. Выберите ячейки, которые вы хотите использовать, и нажмите Главная > Условное форматирование > Новое правило, В Новое правило форматирования диалоговом окне выполните следующие операции:
(1.) Щелкните Используйте формулу, чтобы определить, какие ячейки следует форматировать. из Выберите тип правила список;
2. Затем нажмите Формат кнопку, чтобы перейти к Формат ячеек диалоговом окне и выберите цвет, чтобы выделить нужные ячейки под Заполнять вкладку, см. снимок экрана:
3. Затем нажмите OK > OK кнопки, чтобы закрыть диалоговые окна, и теперь вы можете видеть, что ячейки в столбце A, которые больше, чем соседние ячейки, были выделены по мере необходимости.
Выбирая инструменты на закладке: «ГЛАВНАЯ» в разделе «Стили» из выпадающего меню «Условное форматирование» нам доступна целая группа «Правила отбора первых и последних значений». Однако часто необходимо сравнить и выделить цветом ячейки в Excel, но ни один из вариантов готовых решений не соответствует нашим условиям. Например, в конструкции условия мы хотим использовать больше критериев или выполнять более сложные вычисления. Всегда можно выбрать последнюю опцию «Другие правила» она же является опцией «Создать правило». Условное форматирование позволяет использовать формулу для создания сложных критериев сравнения и отбора значений. Создавая свои пользовательские правила для условного форматирования с использованием различных формул мы себя ничем не ограничиваем.
Как сравнить столбцы в Excel и выделить цветом их ячейки?
Для наглядности разберем конкретный пример создания условного форматирования с формулами. Для примера возьмем простую таблицу отчета прибыльности магазинов за прошлый и текущий год. Наше правило должно заставить Excel выделить цветом при условии, что суммы магазинов текущего года имеют отрицательную прибыль (убыток) и они больше, чем в прошлом году:
Чтобы создать новое пользовательское правило делаем следующее:
- Выделите диапазон ячеек D2:D12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
- В появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматированных ячеек».
- В поле ввода введите формулу:
- Нажмите на кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите красный цвет для данного правила, а на вкладке «Шрифт» – белый цвет. После на всех открытых окнах жмем ОК.
Обратите внимание! В данной формуле мы используем только относительные ссылки на ячейки – это важно. Ведь нам нужно чтобы формула анализировала все ячейки выделенного диапазона.
Как выделить цветом ячейку в Excel по условию?
Теперь оранжевым цветом выделим те суммы магазинов, которые в текущем году меньше чем в прошлом и с отрицательной прибылью. Создадим второе правило для этого же диапазона D2:D12:
- Не снимая выделения с диапазона D2:D12 снова выберите инструмент «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
- Так же в появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматированных ячеек».
- В поле ввода введите формулу:
- Нажмите на кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите оранжевый цвет. На всех открытых окнах жмем ОК.
Мы видим, что получили не совсем ожидаемый результат, так как созданное новое правило всегда имеет высший приоритет по сравнению со старыми правилами условного форматирования в Excel. Необходимо снизить приоритет для нового правила. Чтобы проанализировать данную особенность наглядно и настроить соответствующим образом необходимо выбрать инструмент: ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».
Выберите новое оранжевое правило в появившемся окне «Диспетчер правил условного форматирования» и нажмите на кнопку «Вниз» (CTRL+стрелка вниз), как показано на рисунке:
Как видите последовательность правил очень важна если их много присвоено для одного и того же диапазона ячеек:
На первый взгляд может показаться что несколько правил могут форматировать одну и туже ячейку одновременно. В принципе это так, но при определенном условии, что все правила будут использовать разные типы форматирования. Например, правило 1 – изменяет шрифт, 2 – меняет заливку, 3 – добавляет границу, 4 – узор и т.д. Но если после выполнения любого правила, когда его условие выполнено, было проверено следующее правило для данной ячейки, тогда следует в окне диспетчера отметить галочкой в колонке «Остановить если истина»:
И наконец добавим третье правило для выделения цветом ячеек сумм магазинов, где положительная прибыль и больше чем в прошлом году. Введите новую формулу:
0;D2>C2)' >
Этим ячейкам будет присвоен зеленый цвет и жмем везде ОК.
Примечание. В формуле можно использовать любые ссылки для текущего листа. В версии Excel 2010 можно ссылаться и на другие листы. А в Excel 2007 к другим листам можно обращаться только через имена диапазонов. Мы рекомендуем во всех версиях Excel ссылаться на другие листы через имена, так как это позволяет избежать множество ошибок при создании пользовательских правил для условного форматирования.
Читайте также: