Как выбрать диапазон ячеек в excel для сравнения двух таблиц условное форматирование

Обновлено: 08.07.2024

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

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

Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

• простой способ, как сравнить две таблицы в Excel;
• быстрое выделение значений, которые отличаются;
• сравнить две таблицы в Excel с помощью условного форматирования;
• сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил;
• как сравнить две таблицы в Excel с помощью функции ВПР;
• как сравнить две таблицы в Excel с помощью функции ЕСЛИ;
• сравнить две таблицы с помощью макроса VBA;
• сравнить с помощью надстройки Inquire.

Простой способ, как сравнить две таблицы в Excel

Это самые простой и элементарный способ сравнения двух таблиц. Сравнивать таким способом возможно, как числовые значение, так и текстовые. Для примера сравним два диапазона числовых значений, всего на всего прописав в соседней ячейке формулу их равенства =C2=E2, как результат при равенстве ячеек мы получим ответ «ИСТИНА», а если совпадений нет, будет «ЛОЖЬ». Теперь простым авто копированием копируем на весь диапазон нашу формулу позволяющую сравнить два столбика в Excel и видим разницу.

Быстрое выделение значений, которые отличаются

Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».

Сравнить две таблицы в Excel с помощью условного форматирования

Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами». В диалоговом окне «Диспетчер правил условного форматирования», жмем кнопочку «Создать правило» и в новом диалоговом окне «Создание правила форматирования», выбираем правило «Использовать формулу для определения форматируемых ячеек». В поле «Изменить описание правила» вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат». Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».

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

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная», пункт меню «Условное форматирование» и в списке жмем пункт «Создать правило…», выбираем правило «Использовать формулу для определения форматируемых ячеек», вписываем формулу =СЧЁТЕСЛИ($C$1:$C$7;C1)=0 и выбираем формат условного форматирования.

Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.

Как сравнить две таблицы в Excel с помощью функции ВПР

Как сравнить две таблицы в Excel функции ЕСЛИ

Этот вариант предусматривает использования логической функции ЕСЛИ и отличие этого способа в том что для сравнения двух столбцов будет использован не весь массив целиком, а только та ее часть, которая нужна для сравнения.

Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));"";C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

Сравнить две таблицы с помощью макроса VBA

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

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

Как сделать условное форматирование в Excel

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

При нажатии на стрелочку справа открывается меню для условий форматирования.

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.

Условное форматирование по значению другой ячейки

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» - «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Выделим исходный диапазон (А1:А11). Нажмем «УФ» - «Правила выделения ячеек» - «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.

Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.

Внимание! При использовании относительных ссылок нужно следить, какая ячейка была активна в момент вызова инструмента «Условного формата». Так как именно к активной ячейке «привязывается» ссылка в условии.

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

Проверить правильность заданного условия можно следующим образом:

1. Выделите первую ячейку диапазона с условным форматированим.
2. Откройте меню инструмента, нажмите «Управление правилами».

В открывшемся окне видно, какое правило и к какому диапазону применяется.

Условное форматирование – несколько условий

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

• 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» - «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
• 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

Условное форматирование даты в Excel

Выделяем диапазон с датами.

Применим к нему «УФ» - «Дата».

В открывшемся окне появляется перечень доступных условий (правил):

Выбираем нужное (например, за последние 7 дней) и жмем ОК.

Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).

Условное форматирование в Excel с использованием формул

Если стандартных правил недостаточно, пользователь может применить формулу. Практически любую: возможности данного инструмента безграничны. Рассмотрим простой вариант.

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

Условное форматирование строки по значению ячейки

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

Таблица для примера:

Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).

Выделяем диапазон со значениями таблицы. Нажимаем «УФ» - «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.

Порядок заполнения условий для форматирования «завершенных проектов»:

Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).

Аналогично задаем правила форматирования для незавершенных проектов.

В «Диспетчере» условия выглядят так:

Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».

«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.

​Смотрите также​​ где второе значение​ втором столбце число​ примере пустых ячеек​​ сравнить построчно значения.​​ наших списках, если​ (Go to Special)​​ соседних ячейках каждой​​A1:A18​​ имя «firstList».​​По аналогии с задачей​​ о сравнении, представляющий​Сравним две таблицы имеющих​ разницей будут стоять​ столбцы с данными​ ячеек», ставим галочку​ «Найти и выделить»,​Есть несколько способов,​ больше первого..​ больше чем в​ в диапазоне не​
​Если A3 >​​ опцию​на вкладке​​ строки. Как самый​прежде, чем применили​
​Затем выделите диапазон​ решенной в статье Сравнение​ собой разницу по​ практически одинаковую структуру.​ цифры. Единица будет​
​ (без названия столбцов).​ у слов «Отличия​ выбираем функцию «Выделение​как сравнить два столбца​Ігор Гончаренко​ первом либо наоборот,​ было.​ B3, выделяем A3,​Уникальные​ ​Главная (Home)​
​ простой вариант -​ условное форматирование, Excel​ ​B1:B20​
​ 2-х списков в​ столбцам (разница оборотов​ Таблицы различаются значениями​ стоять, если есть​ На закладке «Главная»​ по строкам». Нажимаем​ группы ячеек».​ в Excel на​
​: ну, добавь 6​ как это сделать?​как и что​ иначе выделяем B3.​​- различия.​Excel выделит ячейки, отличающиеся​ используем формулу для​ автоматически скопирует формулы​и назовите его​ MS EXCEL можно​ за январь и​ в отдельных строках,​
​ различия, а «нуль»​ в разделе «Стили»​ «ОК».​В появившемся окне ставим​ совпадения​ тыс.​Например:​ выделять при их​Если A153 >​Цветовое выделение, однако, не​ содержанием (по строкам).​ сравнения значений, выдающую​ в остальные ячейки.​
​ «secondList».​
​ сформировать список наименований​ февраль).​ ​ некоторые наименования строк​
​ - данные в​ нажимаем на кнопку​Здесь расхождение дат в​ галочку у слов​,​Nomails​Фрукт Закупка Продажа​ наличии?​ ​ B153, выделяем A3,​ ​ всегда удобно, особенно​
​ Затем их можно​ на выходе логические​Таким образом, ячейка​Теперь выделите диапазон​ счетов, включающий ВСЕ​Основным недостатком этого подхода​ встречаются в одной​ ячейках одинаковые. Получится​ «Условное форматирование». Из​ ячейках строк второй​ «Отличия по строкам».​
​как сравнить две таблицы​: ого, работает, просто​Яблоко 50р 70р​
​kesha​ иначе выделяем B153.​​ для больших таблиц.​​ обработать, например:​ значения​A2​A1:A18​ наименования счетов из​ является, то, что​ таблице, но в​ так.​ появившегося списка выбираем​ и третьей.​ Нажимаем «ОК».​ Excel​ я нажал, а​Абрикос 40р 35р​:​И так до​ Также, если внутри​залить цветом или как-то​
​ИСТИНА (TRUE)​содержит следующую формулу:=СЧЕТЕСЛИ(secondList;А2)=0,ячейка​.​ обоих таблиц (без​ отчет о сравнении​ другой могут отсутствовать.​Четвертый​
​ функцию «Создать правило».​
​Есть еще один​В таблице выделились все​,​ там формулы никакой​нужно увидеть, что​
​ikki,​
​ конца столбца.​​ самих списков элементы​ еще визуально отформатировать​ ​или​​ А3:​На вкладке​
​ повторов). Затем вывести​ таблиц не включает​Пусть на листах Январь​с​В строке «Формат…» пишем​ способ​ ячейки с разными​списки​ не было. Скажи​​ продажа дешевле закупки.​каждые два столбца​Igor67​ могут повторяться, то​очистить клавишей​ЛОЖЬ (FALSE)​=СЧЕТЕСЛИ(secondList;А3)=0 и т.д.​Главная​ разницу по столбцам.​ строки отсутствующие в​ и Февраль имеется​
​пособ.​ такую формулу. =$А2<>$В2.​сравнить даты в Excel​ данными так. Excel​,​ как ты это​Ігор Гончаренко​ Old и New​: Вставьте супер формулу​ этот способ не​Delete​ ​:​
​Чтобы выделить команды во​ ​(Home) выберите команду​
​Для этого необходимо:​ наиболее полной таблице.​ две таблицы с​Можно​ Этой формулой мы​
​- сравнить периоды​
​ сравнила данные в​даты в Excel​ сделал?​: так видно?​
​ необходимо сравнить между​ в правила УФ​ подойдет.​заполнить сразу все одинаковым​Число несовпадений можно посчитать​ втором списке, которые​Условное форматирование​С помощью формулы массива​ Например, в рассматриваемом​ оборотами за период​объединить таблицы Excel​ говорим Excel, что​ дат,есть ли в​
​ ячейках двух столбцов​​. Не только сравнить​​Ігор Гончаренко​
​Nomails​​ собой, выделить большее​​ и покажите какой​В качестве альтернативы можно​ значением, введя его​ формулой:​ не входят в​>​ =ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(Январь;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Январь);0)); ИНДЕКС(Февраль;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Февраль);0)));"") сформировать​ нами случае, наиболее​ по соответствующим счетам.​с отчетами. Тогда​
​ если данные в​
​ указанных периодах одинаковые​​ - данные ячейки​ ​ столбцы в Excel,​​: способ называется "условное​: непонятно. Вот смотри,​ значение зеленым цветом.​ надо формат:) Прикрепленные​ использовать функцию​
​ и нажав​=СУММПРОИЗВ(--(A2:A20<>B2:B20))​ первый, выделите диапазон​Создать правило​ в столбце А​ полной таблицей является​Как видно из рисунков,​ все одинаковые данные​
​ ячейках столбца А​ даты и сколько​ А2 и данными​ но и выделить​
​ форматирование"​ мне нужно что​При наличии пустых​ файлы 01_УФ.jpg (18.29​СЧЁТЕСЛИ​Ctrl+Enter​

Сравнение 2-х таблиц в MS EXCEL

​или в английском варианте​B1:B20​(Conditional Formatting >​ перечень счетов из​ таблица на листе​ таблицы различаются:​ соберутся в одну​ не равны данным​

​ дней в периодах​ ячейки В2, т.д.​ разницу цветом шрифта,​удачи!​ бы значения все​

​ ячеек, выделить непустую​ КБ)​

1. ​(COUNTIF)​удалить все строки с​ =SUMPRODUCT(--(A2:A20<>B2:B20))​и создайте новое​ New rule).​ обоих таблиц (без​ Январь, в которой​Наличием (отсутствием) строк (наименований​ строку, но можно​ в ячейках столбца​
2. ​ совпадают. Этот способ​Если мы сейчас нажмём​ ячеек, т.д. Один​Юрий М​ и остались, но​

​ желтым цветом.​ikki​из категории​ выделенными ячейками, используя​Если в результате получаем​ правило, используя формулу​Выберите​ повторов);​ отсутствует счет 26​ счетов). Например, в​

Простой вариант сравнения 2-х таблиц

​ будет посмотреть и​ В, то окрасить​ может пригодиться, чтобы​ на мышь, то​ способ сравнения, разместить​: Nomails, у нас​ были выделены значения​UPD Пользуюсь xls,​: именно это я​Статистические​ команду​ ноль - списки​ ниже:​Использовать формулу для определения​

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

​Главная - Удалить -​ идентичны. В противном​=СЧЕТЕСЛИ(firstList;B1)=0​ форматируемых ячеек​ =ЕСЛИОШИБКА(ИНДЕКС(Список; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(Список; "​Чтобы определить какая из​ Январь отсутствует счет​ магазинам. Как это​ красный свет.​ в периодах. Например,​

​ Чтобы эти выделенные​ одном мониторе одновременно,​ и в быту​ в одной ячейки.​

​ikki​выделяете диапазон A3:B7,​ раз каждый элемент​ Удалить строки с​

​ случае - в​=COUNTIF(firstList,B1)=0​(Use a formula​С помощью формулы =ЕСЛИ(ЕНД(ВПР($B5;Январь!$A$7:$C$81;2;0));0;ВПР($B5;Январь!$A$7:$C$81;2;0))-​ двух таблиц является​ 26 (см. файл​ сделать, смотрите в​Как работать с​ чтобы в отпуске​ ячеек оставить, мы​

​ описан в статье​ тоже) к незнакомым​Вот что бы​

Более наглядный вариант сравнения 2-х таблиц (но более сложный)

​: гм. ​ ячейка A3 должна​ из второго списка​ листа (Home -​ них есть различия.​Затем задайте стиль форматирования​ to determine which​ ЕСЛИ(ЕНД(ВПР($B5;Февраль!$A$7:$C$77;2;0));0;ВПР($B5;Февраль!$A$7:$C$77;2;0)) произвести сравнение​ наиболее полной нужно​ примера), а в​

​ статье «Как объединить​

1. ​ условным форматированием, как​ не было два​ можем закрасить эти​ "Сравнение таблиц Excel".​ людям принято обращаться​ если второй столбик​
2. ​я сам только​ быть активной, добавляете​
3. ​ встречался в первом:​ Delete - Delete​ Формулу надо вводить​
4. ​ (оранжевую заливку) и​ cells to format).​ оборотов по счетам;​ ответить на 2​ таблице на листе​ таблицы в Excel».​ настроить цвет заливки,​ сотрудника сразу или​ ячейки или изменить​Здесь рассмотрим,​ на ВЫ.​ будет больше первого,​

Сравнение двух списков в Excel с помощью условного форматирования

​ осваиваю Excel 2010. ​ праило УФ и​Полученный в результате ноль​ Rows)​ как формулу массива,​ нажмите​Введите следующую формулу:​С помощью Условного форматирования​

​ вопроса: Какие счета​ Февраль отсутствуют счет​Пятый способ.​ шрифта в условном​

1. ​ даты учебы, оплаты​​ цвет шрифта в​​как сравнить столбцы в​vikttur​
2. ​ что бы тогда​​знал, что управление​​ копируете туда мою​ и говорит об​
3. ​и т.д.​​ т.е. после ввода​​ОК​
4. ​=СЧЕТЕСЛИ(secondList;А1)=0​​ выделить расхождения цветом,​​ в февральской таблице​​ 10 и его​​Используем​​ форматировании, как написать​​ счетов, т.д. не​ этих ячейках функциями​
5. ​ Excel​​: . и не цитируют​ выделилась ячейка во​​ правилами УФ изменилось​ формулу.​ отличиях.​
6. ​Если списки разного размера​

​ формулы в ячейку​
​.Результат: Команд «Denver Broncos»,​

​, выделить разницу цветом,​

• ​ бессмысленно, вставляя в​ втором столбике​ по сравнению с​извините, не знал.​И, наконец, "высший пилотаж"​​ и не отсортированы​​ жать не на​ «Arizona Cardinals», «Minnesota​​Задайте стиль форматирования и​​ счета встречающиеся только​ и Какие счета в​Разными значениями в строках.​Excel​
• ​ выделения ячеек, строк,​ способе читайте в​​ закладке «Главная», пока​​ символами, т.д.​ цитату целое предыдущее​1 2​ 2003-м, но чтоб​
• ​ что Вам это​​ - можно вывести​​ (элементы идут в​Enter​

​ Vikings» и «Pittsburgh​

​ в январской?​ 57 обороты за​
​ количество повторов данных​
​ статье «Условное форматирование​

Поиск отличий в двух списках

​ Например, так.​ сдали отчет по​И прикрепляют файл-пример​Ігор Гончаренко​в 2003-м описанный​kesha​ Для этого придется​ придется идти другим​Ctrl+Shift+Enter​ первом списке.​

Вариант 1. Синхронные списки

​Урок подготовлен для Вас​Результат: Команд «Miami Dolphins»​

​ только в таблице​

​ помощью формул (см.​ не совпадают.​

​ Январь, выделены синим,​ столбец Е): =ЕСЛИ(ЕНД(ВПР(A7;Январь!$A$7:$A$81;1;0));"Нет";"Есть") и​Если структуры таблиц примерно​ данными второго столбца.​Третий способ.​ как прибавить к​​ столбцах​​ и выявить разницу.​: что-то у вас​​Nomails​​а здесь. видимо,​​.​ работу выполняет отлично​​ решение: включить цветовое​ надо что сделать,​Источник: http://www.excel-easy.com/examples/compare-two-lists.html​​ нет во втором​ а желтым выделены​ =ЕСЛИ(ЕНД(ВПР(A7;Февраль!$A$7:$A$77;1;0));"Нет";"Есть")​ одинаковы (большинство наименований​​В дополнительном столбце​​Сравнить значения столбцов в​

​ дате число, т.д.,​Excel.​У нас такая​ многое не понятно.​

• ​: у тебя одно​ я тоже что-то​
  
• ​Спасибо​​ ;)​
• ​ выделение отличий, используя​ то подойдет другой​Перевел: Антон Андронов​​ списке.​
• ​ счета только из​Сравнение оборотов по счетам​ счетов (строк) совпадают,​​ устанавливаем формулы, они​Excel формулой.​ смотрите в статье​Здесь мы сравнили​ таблица с данными​
  
• ​ Последний раз на​
  

Вариант 2. Перемешанные списки

​ значение. а мне​ неправильно делаю.​ikki,​kesha​ условное форматирование. Выделите​ быстрый способ: выделите​

​Автор: Антон Андронов​Пояснение:​ февральской таблицы).​ произведем с помощью​ количество и наименования​ разные с разными​Можно сделать в​​ «Дата в Excel.​ три столбца в​ из магазинов. Как​ форумах я был​ нужно что бы​я сейчас попробую​за пояснения. Некорректно​​: Всем привет.​

​ оба диапазона с​​ оба столбца и​​Типовая задача, возникающая периодически​Формула =СЧЕТЕСЛИ (secondList;А1) подсчитывает​Этот урок описывает, как​ формул: =ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;2;0));0;ВПР($A7;Февраль!$A$7:$C77;2;0))-B7 и​​ столбцов совпадают), то​​ условиями. Или в​

​Задача: сравнить два​ данными и выберите​​ нажмите клавишу​ ​ перед каждым пользователем​​ количество команд во​​ сравнить два списка​​ =ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;3;0));0;ВПР($A7;Февраль!$A$7:$C77;3;0))-C7​ можно сравнить две​ формуле указываем конкретные​ и установить в​

​Можно сравнить числа.​ все три столбца​ разных таблиц в​

​ думал что они​ Но принцип выделения​ ответа не обещаю.​ форматирование. Прикрепленные файлы​ столбца числовых данных​

​ на вкладке​F5​ Excel - сравнить​

Как сравнить два столбца и выделить наибольшие значения

​ втором списке, которые​​ в Excel с​
​В случае отсутствия соответствующей​ таблицы. Проведем сравнение​ данные, слова, которые​ ячейках этого столбца​Функцию выделения ячеек можно​ таблицы. Сравниваются данные​ одну, читайте в​ еще существуют.​
​ такой.​

​сорри.​​ Снимок экрана (68)_cr.jpg​

​ построчно и выделить​​Главная - Условное форматирование​​, затем в открывшемся​​ между собой два​ идентичны команде из​
​ помощью условного форматирования.​ строки функция ВПР()​ двумя способами: один​
​ нужно посчитать в​ формулу. =А2=В2​ вызвать клавишей F5.​
​ в ячейках построчно​ статье «Как связать​

​Михаил С.​​Ігор Гончаренко​kesha​ (20.34 КБ)​ в каждой строке​ - Правила выделения​ окне кнопку​

​: Изменилось всё в​​ikki​​ наибольшее значение цветом.​​ ячеек - Повторяющиеся​
​Выделить (Special)​​ и найти различия​​A1​ два списка команд​ которая обрабатывается связкой​ другой нагляднее.​Подробнее смотрите такие​

​Можно условным форматированием окрасить​​ «Переход» нажимаем кнопку​
​ Получилось так.​ тут.​ что вы счас​ одно​
​ лучшую сторону. Это​: приложите файл.​ Как это сделать​ значения (Home -​
​-​ между ними. Способ​. Если СЧЕТЕСЛИ(secondList;A1)=0, значит​ НФЛ (Национальная футбольная​
​ функций ЕНД() и​Сначала определим какие строки​ формулы в статье​

​ слова «Ложь» другим​​ «Выделить…».​​Как сравнить даты в​​Первый способ.​ сделали, и является​а тебе религия​ 13-й офис. Свою​думаю, что лучше​
​ без макроса, условным​ Conditional formatting -​Отличия по строкам (Row​
​ решения, в данном​ команды из ячейки​

​ лига).​​ ЕСЛИ(), заменяя ошибку​
​ (наименования счетов) присутствуют​ «Функция «СЧЕТЕСЛИ» в​
​ цветом или окрасить​Второй способ.​Excel.​Как сравнить два столбца​ бессмысленным цитированием.​
​ мешает добавить еще​ ошибку я исправил,​ в формате xlsx,​
​ форматированием?​ Highlight cell rules​ differences)​
​ случае, определяется типом​A1​Чтобы выделить команды в​
​ на 0 (в​

​ в одной таблице,​​ Excel».​ эти ячейки.​Можно в​Можно сравнить даты.​ в​

Сравнить значение в двух столбцах Ексель

​_​​ несколько или просто​ всё заработало.​ раз Вы используете​Надеюсь на помощь.​ - Duplicate Values)​. В последних версиях​ исходных данных.​нет во втором​ первом списке (не​
​ случае отсутствия строки)​
​ но отсутствуют в​
​Этот способ сравнения​
​Можно написать такую​
​Excel сравнить и выделить​ Принцип сравнения дат​

​ облом?​​Nomails​ эту версию Excel​ikki​:​ Excel 2007/2010 можно​Если списки синхронизированы (отсортированы),​ списке. В результате​
​ во втором!), выполните​ или на значение​ другой. Затем, в​ можно применить при​ формулу в ячейке​ цветом​
​ тот же –​
​Выделяем столбцы (у​

​ вообще без цитат​​Nomails​: Всем привет, необходимо​

​предполагаю, что активная​​: =И(A3=МАКС($A3:$B3);A3<>МИН($A3:$B3))​Если выбрать опцию​ также воспользоваться кнопкой​ то все делается​ Excel окрашивает ячейку​ следующие действия:​

​ из соответствующего столбца.​​ таблице, в которой​ сравнении двух прайсов.​
​ С2. =СУММ(ЕСЛИ(A2:A6<>B2:B6;1;0)) Нажимаем​ячейки с разными данными​ выделяем столбцы, нажимаем​ нас столбцы А​

​ - менее понятно,​​: у меня 6тыс​ сравнить значения в​ ячейка у вас​kesha​Повторяющиеся​Найти и выделить (Find​

​ весьма несложно, т.к.​​ голубым цветом.​Сперва выделите диапазон​

​С помощью Условного форматирования​​ меньше строк отсутствует​ Смотрите статью "Как​ «Enter». Копируем формулу​с помощью условного​ на кнопку «Найти​ и В). На​

​ если б я​​ строк и мне​ двух столбцах так,​
​ неправильная, но надо​

​: Спасибо,​​, то Excel выделит​ & Select) -​ надо, по сути,​Так как мы выбрали​A1:A18​ можно выделить расхождения​

​ (в наиболее полной​​ сделать прайс-лист в​ по столбцу. Тогда​ форматирования.​ и выделить». Выбираем​
​ закладке «Главная» нажимаем​ процитировал полностью ваше​

​ нужно что бы​​ что бы мне​ посмотреть.​ikki,​ цветом совпадения в​ Выделение группы ячеек​ сравнить значения в​ диапазон​

Начнем изучение Условного форматирования с проверки числовых значений на больше /меньше /равно /между в сравнении с числовыми константами.

Эти правила используются довольно часто, поэтому в EXCEL 2007 они вынесены в отдельное меню Правила выделения ячеек .

Эти правила также же доступны через меню Главная/ Стили/ Условное форматирование/ Создать правило, Форматировать только ячейки, которые содержат .

Рассмотрим несколько задач:

СРАВНЕНИЕ С ПОСТОЯННЫМ ЗНАЧЕНИЕМ (КОНСТАНТОЙ)

Задача1 . Сравним значения из диапазона A1:D1 с числом 4.

• введем в диапазон A1:D1 значения 1, 3, 5, 7
• выделим этот диапазон;
• применим к выделенному диапазону Условное форматирование на значение Меньше ( Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше );
• в левом поле появившегося окна введем 4 – сразу же увидим результат применения Условного форматирования .
• Нажмем ОК.

Результат можно увидеть в файле примера на листе Задача1 .

СРАВНЕНИЕ СО ЗНАЧЕНИЕМ В ЯЧЕЙКЕ (АБСОЛЮТНАЯ ССЫЛКА)

Чуть усложним предыдущую задачу: вместо ввода в качестве критерия непосредственно значения (4), введем ссылку на ячейку, в которой содержится значение 4.

Задача2 . Сравним значения из диапазона A1:D1 с числом из ячейки А2 .

• введем в ячейку А2 число 4;
• выделим диапазон A1:D1 ;
• применим к выделенному диапазону Условное форматирование на значение Меньше ( Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше );
• в левом поле появившегося окна введем ссылку на ячейку A2 нажав на кнопочку, расположенную в правой части окна (EXCEL по умолчанию использует абсолютную ссылку $А$2 ).

В результате, все значения из выделенного диапазона A 1: D 1 будут сравниваться с одной ячейкой $А$2 . Те значения из A 1: D 1 , которые меньше A 2 будут выделены заливкой фона ячейки.

Результат можно увидеть в файле примера на листе Задача2 .

Чтобы увидеть как настроено правило форматирования, которое Вы только что создали, нажмите Главная/ Стили/ Условное форматирование/ Управление правилами ; затем дважды кликните на правиле или нажмите кнопку Изменить правило . В результате увидите диалоговое окно, показанное ниже.

ПОПАРНОЕ СРАВНЕНИЕ СТРОК/ СТОЛБЦОВ (ОТНОСИТЕЛЬНЫЕ ССЫЛКИ)

Теперь будем производить попарное сравнение значений в строках 1 и 2.

Задача3 . Сравнить значения ячеек диапазона A 1: D 1 со значениями из ячеек диапазона A 2: D 2 . Для этого будем использовать относительную ссылку.

• введем в ячейки диапазона A2:D2 числовые значения (можно считать их критериями);
• выделим диапазон A1:D1 ;
• применим к выделенному диапазону Условное форматирование на значение Меньше ( Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше )
• в левом поле появившегося окна введем относительную ссылку на ячейку A2 (т.е. просто А2 или смешанную ссылку А$2 ). Убедитесь, что знак $ отсутствует перед названием столбца А.

Теперь каждое значение в строке 1 будет сравниваться с соответствующим ему значением из строки 2 в том же столбце! Выделены будут значения 1 и 5, т.к. они меньше соответственно 2 и 6, расположенных в строке 2.

Результат можно увидеть в файле примера на листе Задача3 .

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

Примечание-отступление : О важности фиксирования активной ячейки при создании правил Условного форматирования с относительными ссылками

При создании относительных ссылок в правилах Условного форматирования , они «привязываются» к ячейке, которая является активной в момент вызова инструмента Условное форматирование.

СОВЕТ : Чтобы узнать адрес активной ячейки (она всегда одна на листе) можно посмотреть в поле Имя (находится слева от Строки формул ). В задаче 3, после выделения диапазона A1:D1 (клавиша мыши должна быть отпущена), в поле Имя , там будет отображен адрес активной ячейки A1 или D 1 . Почему возможно 2 вырианта и в чем разница для правил условного форматирования?

Посмотрим внимательно на второй шаг решения предыдущей задачи3 - выделение диапазона A 1: D 1 . Указанный диапазон можно выделить двумя способами: выделить ячейку А1 , затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь вправо к D1 ; либо, выделить ячейку D1 , затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь влево к А1 . Разница между этими двумя способами принципиальная: в первом случае, после завершения выделения диапазона, активной ячейкой будет А1 , а во втором D 1 !

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

Если мы выделили диапазон первым способом, то, введя в правило Условного форматирования относительную ссылку на ячейку А2 , мы тем самым сказали EXCEL сравнивать значение активной ячейки А1 со значением в А2 . Т.к. правило распространяется на диапазон A 1: D 1 , то B 1 будет сравниваться с В2 и т.д. Задача будет корректно решена.

Если при создании правила Условного форматирования активной была ячейка D1 , то именно ее значение будет сравниваться со значением ячейки А2 . А значение из A 1 будет теперь сравниваться со значением из ячейки XFB2 (не найдя ячеек левее A 2 , EXCEL выберет самую последнюю ячейку XFD для С1 , затем предпоследнюю для B 1 и, наконец XFB2 для А1 ). Убедиться в этом можно, посмотрев созданное правило:

• выделите ячейку A1 ;
• нажмите Главная/ Стили/ Условное форматирование/ Управление правилами ;
• теперь видно, что применительно к диапазону $A$1:$D$1 применяется правило Значение ячейки 6 (задан формат: красный фон) и Значение ячейки >7 (задан формат: зеленый фон), см. рисунок выше. Т.к. правило Значение ячейки >6 (задан формат: красный фон) располагается выше, то оно имеет более высокий приоритет, и поэтому ячейка со значением 9 будет иметь красный фон. На Флажок Остановить, если истина можно не обращать внимание, он устанавливается для обеспечения обратной совместимости с предыдущими версиями EXCEL, не поддерживающими одновременное применение нескольких правил условного форматирования. Хотя его можно использовать для отмены одного или нескольких правил при одновременном использовании нескольких правил, установленных для диапазона (когда между правилами нет конфликта). Подробнее можно ]]>прочитать здесь ]]> .

Если к диапазону ячеек применимо правило форматирования, то оно обладает приоритетом над форматированием вручную. Форматирование вручную можно выполнить при помощи команды Формат из группы Ячейки на вкладке Главная . При удалении правила условного форматирования форматирование вручную остается.

УСЛОВНОЕ ФОРМАТИРОВАНИЕ и ФОРМАТ ЯЧЕЕК

Условное форматирование не изменяет примененный к данной ячейке Формат (вкладка Главная группа Шрифт, или нажать CTRL+SHIFT+F ). Например, если в Формате ячейки установлена красная заливка ячейки, и сработало правило Условного форматирования, согласно которого заливкая этой ячейки должна быть желтой, то заливка Условного форматирования "победит" - ячейка будет выделены желтым. Хотя заливка Условного форматирования наносится поверх заливки Формата ячейки, она не изменяет (не отменяет ее), а ее просто не видно.

Через Формат ячеек можно задать пользовательский формат ячейки , который достаточно гибок и иногда даже удобнее, чем Условное форматирование. Подробнее см. статью Пользовательский ЧИСЛОвой формат в MS EXCEL (через Формат ячеек) .

ОТЛАДКА ПРАВИЛ УСЛОВНОГО ФОРМАТИРОВАНИЯ

Чтобы проверить правильно ли выполняется правила Условного форматирования, скопируйте формулу из правила в любую пустую ячейку (например, в ячейку справа от ячейки с Условным форматированием). Если формула вернет ИСТИНА, то правило сработало, если ЛОЖЬ, то условие не выполнено и форматирование ячейки не должно быть изменено.

Вернемся к задаче 3 (см. выше раздел об относительных ссылках). В строке 4 напишем формулу из правила условного форматирования =A1

В тех столбцах, где результат формулы равен ИСТИНА, условное форматирование будет применено, а где ЛОЖЬ - нет.

ИСПОЛЬЗОВАНИЕ В ПРАВИЛАХ ССЫЛОК НА ДРУГИЕ ЛИСТЫ

До MS Excel 2010 для правил Условного форматирования нельзя было напрямую использовать ссылки на другие листы или книги. Обойти это ограничение можно было с помощью использования имен . Если в Условном форматирования нужно сделать, например, ссылку на ячейку А2 другого листа, то нужно сначала определить имя для этой ячейки, а затем сослаться на это имя в правиле Условного форматирования . Как это реализовано См. файл примера на листе Ссылка с другого листа .

ПОИСК ЯЧЕЕК С УСЛОВНЫМ ФОРМАТИРОВАНИЕМ

• на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найтии выделить ,
• выберите в списке пункт Условное форматирование .

Будут выделены все ячейки для которых заданы правила Условного форматирования.

ДРУГИЕ ПРЕДОПРЕДЕЛЕННЫЕ ПРАВИЛА

В меню Главная/ Стили/ Условное форматирование/ Правила выделения ячеек разработчиками EXCEL созданы разнообразные правила форматирования.

Чтобы заново не изобретать велосипед, посмотрим на некоторые их них внимательнее.

• Текст содержит… Приведем пример. Пусть в ячейке имеется слово Дрель . Выделим ячейку и применим правило Текст содержит …Если в качестве критерия запишем ре (выделить слова, в которых содержится слог ре ), то слово Дрель будет выделено.

Теперь посмотрим на только что созданное правило через меню Главная/ Стили/ Условное форматирование/ Управление правилами.

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

Пусть снова в ячейке имеется слово Дрель . Выделим ячейку и применим правило Текст содержит … Если в качестве критерия запишем р?, то слово Дрель будет выделено. Критерий означает: выделить слова, в которых содержатся слога ре, ра, ре и т.д. Надо понимать, что также будут выделены слова с фразами р2, рм, рQ , т.к. знак ? означает любой символ. Если в качестве критерия запишем . (выделить слова, в которых не менее 6 букв), то, соответственно, слово Дрель не будет выделено. Можно, конечно подобного результата добиться с помощью формул с функциями ПСТР() , ЛЕВСИМВ() , ДЛСТР() , но этот подход, согласитесь, быстрее.

• Повторяющиеся значения… Это правило позволяет быстро настроить Условное форматирование для отображения уникальных и повторяющихся значений. Под уникальным значением Условное форматирование подразумевает неповторяющееся значение, т.е. значение которое встречается единственный раз в диапазоне, к которому применено правило. Чтобы выделить уникальные значения (т.е. все значения без их повторов), то см. эту статью .
• Дата… На рисунке ниже приведены критерии отбора этого правила. Для того, чтобы добиться такого же результата с помощью формул потребуется гораздо больше времени.

• Значение ячейки. Это правило доступно через меню Главная/ Стили/ Условное форматирование/ Создать правило . В появившемся окне выбрать пункт форматировать ячейки, которые содержат. Выбор опций позволит выполнить большинство задач, связанных с выделением числовых значений.

Советую также обратить внимание на следующие правила из меню Главная/ Стили/ Условное форматирование/ Правила отбора первых и последних значений.

Задача4 . Пусть имеется 21 значение, для удобства отсортированных по возрастанию . Применим правило Последние 10 элементов и установим, чтобы было выделено 3 значения (элемента). См. файл примера , лист Задача4 .

Слова "Последние 3 значения" означают 3 наименьших значения. Если в списке есть повторы, то будут выделены все соответствующие повторы. Например, в нашем случае 3-м наименьшим является третье сверху значение 10. Т.к. в списке есть еще повторы 10 (их всего 6), то будут выделены и они.

Соответственно, правила, примененные к нашему списку: "Последнее 1 значение", "Последние 2 значения", . "Последние 6 значений" будут приводить к одинаковому результату - выделению 6 значений равных 10.

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

Применение правила "Последние 7 значений" приведет к выделению дополнительно всех значений равных 11, .т.к. 7-м минимальным значением является первое сверху значение 11.

Аналогично можно создать правило для выделения нужно количества наибольших значений, применив правило Первые 10 элементов .

Рассмотрим другое родственное правило Последние 10% .

Обратите внимание, что на картинке выше не установлена галочка "% от выделенного диапазона". Эта галочка устанавливается либо в ручную или при применении правила Последние 10% .

В этом правиле задается процент наименьших значений от общего количества значений в списке. Например, задав 20% последних, будет выделено 20% наименьших значений.

Попробуем задать 20% последних в нашем списке из 21 значения: будет выделено шесть значений 10 (См. файл примера , лист Задача4) . 10 - минимальное значение в списке, поэтому в любом случае будут выделены все его повторы.

Задавая проценты от 1 до 33% получим, что выделение не изменится. Почему? Задав, например, 33%, получим, что необходимо выделить 6,93 значения. Т.к. можно выделить только целое количество значений, Условное форматирование округляет до целого, отбрасывая дробную часть. А вот при 34% уже нужно выделить 7,14 значений, т.е. 7, а с учетом повторов следующего за 10-ю значения 11, будет выделено 6+3=9 значений.

ПРАВИЛА С ИСПОЛЬЗОВАНИЕМ ФОРМУЛ

Создание правил форматирования на основе формул ограничено только фантазией пользователя. Здесь рассмотрим только один пример, остальные примеры использования Условного форматирования можно найти в этих статьях: Условное форматирование Дат ; Условное форматирование Чисел ; Условное форматирование Текстовых значений ; другие задачи .

Предположим, что необходимо выделять ячейки, содержащие ошибочные значения:

• Выделите ячейки, к которым нужно применить Условное форматирование (пусть это ячейка А1 ).
• Вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило )
• Выберите Использовать формулу для определения форматируемых ячеек

Того же результата можно добиться по другому:

• Вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило )
• Выделите пункт Форматировать только ячейки, которые содержат ;
• В разделе Форматировать только ячейки, для которых выполняется следующее условие: в самом левом выпадающем списке выбрать Ошибки.

СОВЕТ : Отметить все ячейки, содержащие ошибочные значения можно также с помощью инструмента Выделение группы ячеек .

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

  
• Как войти в директ коммандер не через браузер
  
• Программа для изменения частоты процессора на андроид
  
• Вернуть ленту в outlook
  
• Как спарсить данные с сайта в excel python
  
• Автокад не чертит по оси z