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

Методы сравнения таблиц в Microsoft Excel

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

Способы сравнения

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

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

Кроме того, следует сказать, что сравнивать табличные области имеет смысл только тогда, когда они имеют похожую структуру.

Способ 1: простая формула

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

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

Для этого нам понадобится дополнительный столбец на листе. Вписываем туда знак «=». Затем кликаем по первому наименованию, которое нужно сравнить в первом списке. Опять ставим символ «=» с клавиатуры. Далее кликаем по первой ячейке колонки, которую мы сравниваем, во второй таблице. Получилось выражение следующего типа:

Всего в качестве аргументов можно использовать адреса до 255 массивов. Но в нашем случае мы будем использовать всего два массива, к тому же, как один аргумент.

Ставим курсор в поле «Массив1» и выделяем на листе сравниваемый диапазон данных в первой области. После этого в поле ставим знак «не равно» (<>) и выделяем сравниваемый диапазон второй области. Далее обворачиваем полученное выражение скобками, перед которыми ставим два знака «-». В нашем случае получилось такое выражение:

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

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

Способ 2: выделение групп ячеек

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

Выделяем сравниваемые массивы. Переходим во вкладку «Главная». Далее щелкаем по значку «Найти и выделить», который располагается на ленте в блоке инструментов «Редактирование». Открывается список, в котором следует выбрать позицию «Выделение группы ячеек…».

Способ 3: условное форматирование

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

1. Прежде всего, выбираем, какую табличную область будем считать основной, а в какой искать отличия. Последнее давайте будем делать во второй таблице. Поэтому выделяем список работников, находящийся в ней. Переместившись на вкладку «Главная», щелкаем по кнопке «Условное форматирование», которая имеет месторасположение на ленте в блоке «Стили». Из выпадающего списка переходим по пункту «Управление правилами».
2. Активируется окошко диспетчера правил. Жмем в нем на кнопку «Создать правило».
3. В запустившемся окне производим выбор позиции «Использовать формулу». В поле «Форматировать ячейки» записываем формулу, содержащую адреса первых ячеек диапазонов сравниваемых столбцов, разделенные знаком «не равно» (<>). Только перед данным выражением на этот раз будет стоять знак «=». Кроме того, ко всем к координатам столбцов в данной формуле нужно применить абсолютную адресацию. Для этого выделяем формулу курсором и трижды жмем на клавишу F4. Как видим, около всех адресов столбцов появился знак доллара, что и означает превращение ссылок в абсолютные. Для нашего конкретного случая формула примет следующий вид:

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

1. Производим выделение областей, которые нужно сравнить.
2. Выполняем переход во вкладку под названием «Главная». Делаем щелчок по кнопке «Условное форматирование». В активировавшемся списке выбираем позицию «Правила выделения ячеек». В следующем меню делаем выбор позиции «Повторяющиеся значения».
3. Запускается окно настройки выделения повторяющихся значений. Если вы все сделали правильно, то в данном окне остается только нажать на кнопку «OK». Хотя при желании в соответствующем поле данного окошка можно выбрать другой цвет выделения.
4. После того, как мы произведем указанное действие, все повторяющиеся элементы будут выделены выбранным цветом. Те элементы, которые не совпадают, останутся окрашенными в свой изначальный цвет (по умолчанию белый). Таким образом, можно сразу визуально увидеть, в чем отличие между массивами.

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

Таким образом, будут выделены именно те показатели, которые не совпадают.

Способ 4: комплексная формула

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

Оператор СЧЁТЕСЛИ относится к статистической группе функций. Его задачей является подсчет количества ячеек, значения в которых удовлетворяют заданному условию. Синтаксис данного оператора имеет такой вид:

Аргумент «Диапазон» представляет собой адрес массива, в котором производится подсчет совпадающих значений.

Аргумент «Критерий» задает условие совпадения. В нашем случае он будет представлять собой координаты конкретных ячеек первой табличной области.

1. Выделяем первый элемент дополнительного столбца, в котором будет производиться подсчет количества совпадений. Далее щелкаем по пиктограмме «Вставить функцию».
2. Происходит запуск Мастера функций. Переходим в категорию «Статистические». Находим в перечне наименование «СЧЁТЕСЛИ». После его выделения щелкаем по кнопке «OK».
3. Происходит запуск окна аргументов оператора СЧЁТЕСЛИ. Как видим, наименования полей в этом окне соответствуют названиям аргументов.

Устанавливаем курсор в поле «Диапазон». После этого, зажав левую кнопку мыши, выделяем все значения столбца с фамилиями второй таблицы. Как видим, координаты тут же попадают в указанное поле. Но для наших целей следует сделать данный адрес абсолютным. Для этого выделяем данные координаты в поле и жмем на клавишу F4.

Как видим, ссылка приняла абсолютную форму, что характеризуется наличием знаков доллара.

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

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

1. Прежде всего, немного переработаем нашу формулу СЧЁТЕСЛИ, а именно сделаем её одним из аргументов оператора ЕСЛИ. Для этого выделяем первую ячейку, в которой расположен оператор СЧЁТЕСЛИ. В строке формул перед ней дописываем выражение «ЕСЛИ» без кавычек и открываем скобку. Далее, чтобы нам легче было работать, выделяем в строке формул значение «ЕСЛИ» и жмем по иконке «Вставить функцию».
2. Открывается окно аргументов функции ЕСЛИ. Как видим, первое поле окна уже заполнено значением оператора СЧЁТЕСЛИ. Но нам нужно дописать кое-что ещё в это поле. Устанавливаем туда курсор и к уже существующему выражению дописываем «=0» без кавычек.

После этого переходим к полю «Значение если истина». Тут мы воспользуемся ещё одной вложенной функцией – СТРОКА. Вписываем слово «СТРОКА» без кавычек, далее открываем скобки и указываем координаты первой ячейки с фамилией во второй таблице, после чего закрываем скобки. Конкретно в нашем случае в поле «Значение если истина» получилось следующее выражение:

В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений», который мы ранее преобразовали с помощью функции ЕСЛИ. Делаем все ссылки абсолютными.

Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ. От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.

В поле «Массив» указываем адрес диапазона значений второй таблицы. При этом все координаты делаем абсолютными, то есть, ставим перед ними знак доллара уже ранее описанным нами способом.

Способ 5: сравнение массивов в разных книгах

При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.

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

Сравнение двух таблиц в Excel на совпадение значений в столбцах

У нас имеется две таблицы заказов, скопированных в один рабочий лист. Необходимо выполнить сравнение данных двух таблиц в Excel и проверить, которые позиции есть первой таблице, но нет во второй. Нет смысла вручную сравнивать значение каждой ячейки.

Сравнение двух столбцов на совпадения в Excel

Как сделать сравнение значений в Excel двух столбцов? Для решения данной задачи рекомендуем использовать условное форматирование, которое быстро выделить цветом позиции, находящиеся только в одном столбце. Рабочий лист с таблицами:

В первую очередь необходимо присвоить имена обоим таблицам. Благодаря этому легче понять, какие сравниваются диапазоны ячеек:

1. Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя».
2. В появившемся окне в поле «Имя:» введите значение – Таблица_1.
3. Левой клавишей мышки сделайте щелчок по полю ввода «Диапазон:» и выделите диапазон: A2:A15. И нажмите ОК.

Для второго списка выполните те же действия только имя присвойте – Таблица_2. А диапазон укажите C2:C15 – соответственно.

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

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

Позиции, которые есть в Таблице_1, но нет в Таблцие_2 будут отображаться зеленым цветом. В тоже время позиции, находящиеся в Таблице_2, но отсутствующие в Таблице_1, будут подсвечены синим цветом.

1. Выделите диапазон первой таблицы: A2:A15 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
2. В поле ввода введите формулу:
3. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет. На всех окнах жмем ОК.
4. Выделите диапазон первого списка: C2:C15 и снова выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
5. В поле ввода введите формулу:
6. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите синий цвет. На всех окнах жмем ОК.

Принцип сравнения данных двух столбцов в Excel

При определении условий для форматирования ячеек столбцов мы использовали функцию СЧЕТЕСЛИ. В данном примере эта функция проверяет сколько раз встречается значение второго аргумента (например, A2) в списке первого аргумента (например, Таблица_2). Если количество раз = 0 в таком случае формула возвращает значение ИСТИНА. В таком случае ячейке присваивается пользовательский формат, указанный в параметрах условного форматирования.

Ссылка во втором аргументе относительная, значит по очереди будут проверятся все ячейки выделенного диапазона (например, A2:A15). Например, для сравнения двух прайсов в Excel даже на разных листах. Вторая формула действует аналогично. Этот же принцип можно применять для разных подобных задач.

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

Добрый день!

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

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

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

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

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

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

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

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

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

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

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

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

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

В этом варианте мы будем использовать функцию ВПР, которая позволит нам сравнить две таблицы на предмет совпадений. Для сравнения двух столбиков, введите формулу =ВПР(C2;$D$2:$D$7;1;0) и скопируйте ее на весь сравниваемый диапазон. Эта формула последовательно начинает проверять есть ли повторы значения из столбика А в столбике В, ну и соответственно возвращает значение элемента, если оно было там найдено если же значение не найдено получаем ошибку #Н/Д.

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

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

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

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

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

Сравнить два столбца в Excel на совпадения: полный урок

Всем привет! В сегодняшнем нашем уроке мы рассмотрим все возможные варианты сравнить две таблицы на совпадения. Мы будем работать в нашем любимом Excel. Мы рассмотрим, как сравнение таблиц, так и столбцов. Будем использовать сложные формулы, а также простые методы для небольшого количества данных. Урок будет сложным, но интересным. Я постарался описать все максимально подробно. Если у вас возникнут дополнительные вопросы или дополнения – пишите в комментариях.

Способ 1: Логическое сравнение

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

Как только таблицы уже будут одинаковые по основному столбцу, тогда уже приступаем к сравнению:

1. Представим себе, что у нас есть таблицы, и нам нужно сравнить какие-то два столбца. Выделяем свободную ячейку, параллельную этим строкам. Далее начинаем вводить формулу вычисления, начав со знака равно (=). Теперь с помощью левой кнопкой мыши выделяем одну ячейку первой таблицы, ставим знак равно (=) для сравнения, а потом выделяем аналогичную параллельную ячейку второй таблички.
2. Чтобы применить вычисления, просто кликните в любое место или нажмите по клавише:

1. Отлично! Из примера ниже мы видим, что первая строчка, первого столбца обеих таблиц совпадает, и мы видим значение:

1. Теперь нам нужно применить эту же формулу сравнения и для других ячеек. Для этого воспользуемся маркером автозаполнения – наведите на данную ячейку, зажмите левой кнопкой мыши (ЛКМ) по квадратику и тяните его до конца таблицы или до того места, где вы хотите закончить сравнение.

1. Ради интереса посмотрите какие формулы применили в других ячейках. Маркер автоматически сдвинул адреса ячеек для правильного подсчета.

СОВЕТ! Я вам настоятельно рекомендую изучить урок – про маркер автозаполнения. Это один из самых важных уроков, и его должен знать каждый, кто работает с Excel.

А теперь вам небольшое самостоятельное задание. Вам нужно сделать то же самое, но сравнить таблицы, которые находятся на разных листах документа. Создайте новый лист (если у вас еще его пока нет) и скопируйте туда ту табличку, которую вы хотите сравнить. По сути у вас будет точно такая же формула, только при сравнении для адреса второй ячейки нужно указать первый или второй лист (смотря на каком вы будете выполнить сравнение). Пример формулы:

Обязательно не забудьте поставить восклицательный знак после адреса листа. Попробуйте выполнить это задание самостоятельно.

Способ 2: Функция

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

1. Установите курсор в любую ячейку, в которой будет отображаться результат.

ПРИМЕЧАНИЕ! По поводу результата – мы будем выводить количество несовпадений.

1. Далее нажмите по кнопке вставки функции, которая находится рядом с адресной строкой.

1. Находим функцию СУММПРОИЗВ, она находится в «Математической» категории.

1. А теперь давайте разберем по шагам, что нам нужно делать. Выделяем строчку «Массив1».
2. Ставим два знака минус (—), открываем скобку и выделяем столбец первой части таблицы.
3. Ставим знак неравенства (<>)
4. Теперь точно также выделяем вторую часть таблицы и закрываем скобку. Ориентируйтесь на скриншот ниже.

1. Теперь мы увидим число, которое отображает количество несовпадений. Если оба столбца будут точно совпадать друг с другом, то вы увидите ноль (0).

Способ 3: Работа с несколькими столбцами

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

1. Выделяем две таблицы – они должны быть одинакового размера.
2. Переходим на вкладку «Главная».

1. В правом верхнем углу в разделе «Редактирование» кликните по кнопке «Найти и выделить» – «Выделить группу ячеек».

1. Выделяем настройку «Отличия по строкам» и жмем «ОК».

1. Теперь вы увидите, как выделятся только те ячейки, которые отличаются друг от друга.

Способ 4: Форматирование по условию

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

1. Выделите данные второй таблицы.

1. На «Главной» в разделе «Стили» кликаем ЛКМ по кнопке «Условное форматирование» – «Управление правилами…».

1. Создаем правило!

1. Выбираем правило «Использовать формулу для определения форматируемых ячеек».
2. Теперь нам нужно указать формулу. Начинаем со знака равно (=). Далее выделяем сначала первую ячейку первой таблицы. Смотрите, чтобы по столбцам адрес был абсолютным, а вот строки менялись. Далее ставим знак неравенства (<>) и указываем ячейку второй таблицы. Пример:

1. Если у вас таблица находится на другом листе, то формула будет примерно аналогичная, только не забудьте указать адрес листа:

1. Теперь наша задача как-то выделить и подкрасить эти ячейки – кликните по кнопке «Формат…». На вкладке «Заливка» выделите яркий цвет, отличающийся от фонового. Можно выбрать красный, голубой или желтый. Жмем «ОК».

1. Применяем настройки, еще раз два раза нажав «ОК».

1. Как видите ячейки теперь подкрашены другим цветом.

Способ 5: Совпадения и отличия

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

1. Выделите область из двух таблиц.

1. «Условное форматирование» – «Правила выделения ячеек» – «Повторяющиеся значения…».

1. Слева в блоке поставьте настройку «Повторяющиеся» и нажмите «ОК».

1. Далее вы увидите, что все одинаковые ячейки будут подкрашены.

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

Способ 6: Сложные формулы

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

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

СЧЁТЕСЛИ(массив;условие)

• Массив – это диапазон ячеек, где мы производим подсчет.
• Условие – это условие по которому мы производим подсчет выделенных ячеек.

Если вам пока ничего не понятно, ничего страшного, сейчас мы постараемся все разобрать на конкретном примере.

ПРИМЕЧАНИЕ! Я буду показывать на примере двух таблиц, расположенных на одном листе, вы же можете использовать несколько листов. Вы уже знаете, как использовать адрес в таком случае.

1. Можете создать отдельный столбец, куда мы будем выводить результаты сравнения.
2. Ставим курсор в ячейку и вставляем формулу.

1. Находим СЧЁТЕСЛИ – формула находится в «Статической» категории.

1. А теперь очень внимательно действуем по шагам. Сначала ставим курсор в строку «Диапазон».
2. Выделяем диапазон второй таблицы. ОЧЕНЬ ВАЖНО, чтобы все адреса были абсолютными – то есть перед адресом строки и столбца стоял знак доллар ($). Чтобы быстрее это сделать и не вводить вручную, выделите строчку и нажмите по клавише:

1. Выделяем «Критерий».
2. Выделяем первую ячейку первой таблицы.

1. Ставим «ОК».

1. Копируем формулу с помощью маркера автозаполнения.

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

1. Установите курсор в первую ячейку и допишите после знака равно (=) формулу:

1. Не забудьте закрыть кавычки, а потом сразу же нажмите по знаку «Функции», чтобы открыть настройки.

1. Должны открыться «Аргументы функции» ЕСЛИ. Если открылось не то окошко – два раза кликните левой кнопкой мыши по надписи «ЕСЛИ» в строке адреса. Далее в первой строке (Лог_выражение) дописываем в самом конце:

1. Во второй строке используем формулу СТРОКА и выделяем первую ячейку второй таблички.

1. Вы увидите значение «ЛОЖЬ», если обе строчки одинаковые.

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

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

1. Ставим курсор на первую строчку соседнего пустого столбца.

1. Вставляем формулу:

1. В первой строке выделяем столбец, где мы выводили номера строк. А во втором указываем только первый элемент нашего пронумерованного столбца.

1. Копируем формулу.

1. Теперь в этой строке добавляем:

1. Заходим в настройки формулы.

1. В окне ничего менять не нужно, оставляем настройку по умолчанию.

1. В качестве первого массива указываем столбец второй таблицы и делаем адреса абсолютными. Можете воспользоваться клавишу F4, чтобы не вводить знак доллара ($) вручную.
2. В «Номер строки» в конце ставим:

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