Skip to content
В этом руководстве вы познакомитесь с различными методами сравнения таблиц Excel и определения различий между ними. Узнайте, как просматривать две таблицы рядом, как использовать формулы для создания отчета о различиях, выделить несовпадения с помощью условного форматирования и многое другое.
Когда у вас есть две похожие книги Эксель или, лучше сказать, две версии одной и той же книги, что вы обычно хотите с ними делать в первую очередь? Сравнить их на предмет различий, а затем, возможно, объединить в один файл. Кроме того, такая операция может помочь вам обнаружить потенциальные проблемы, такие как битые ссылки, повторяющиеся записи, несогласованные формулы.
- Визуальное сравнение таблиц.
- Быстрое выделение различий.
- Использование формулы сравнения.
- Как вывести различия на отдельном листе.
- Как можно использовать функцию ВПР.
- Выделение различий условным форматированием.
- Сопоставление при помощи сводной таблицы.
- Сравнение таблиц при помощи Pover Query.
- Инструмент сравнения таблиц Ultimate Suite.
Итак, давайте более подробно рассмотрим различные методы сравнения таблиц Excel и выявления различий между ними.
Просмотр рядом, чтобы сравнить таблицы.
Если у вас относительно небольшие файлы и вы внимательны к деталям, этот быстрый и простой способ сравнения может вам подойти. Я говорю о режиме «Просмотр рядом», который позволяет расположить два окна Excel рядом. Вы можете использовать этот метод для визуального сравнения двух таблиц или двух листов из одной книги.
Сравните 2 книги.
Предположим, у вас есть отчеты о продажах за два месяца, и вы хотите просмотреть их оба одновременно, чтобы понять, какие товары показали лучшие результаты в этом месяце, а какие — в прошлом.
Чтобы просмотреть два файла Эксель рядом, сделайте следующее:
- Откройте оба файла.
- Перейдите на вкладку «Вид» и нажмите кнопку «Рядом». (1) Это оно!
По умолчанию два отдельных окна Excel отображаются горизонтально.
Чтобы разделить окна по вертикали, нажмите кнопку «Упорядочить все» (3) и выберите «Рядом» (4):
В результате два отдельных окна будут расположены, как на скриншоте.
Если вы хотите прокручивать оба листа одновременно, чтобы сравнивать данные строка за строкой, убедитесь, что параметр синхронной прокрутки (2) включен. Он обычно включается автоматически, как только вы активируете режим одновременного просмотра двух книг.
Расположите рядом несколько таблиц Excel.
Чтобы просматривать более двух файлов одновременно, откройте все книги, которые вы хотите сравнить, и нажмите кнопку «Рядом».
Появится диалоговое окно «Сравнить рядом», в котором вы выберете файлы, которые будут отображаться вместе с активной книгой.
Чтобы просмотреть все открытые файлы одновременно, нажмите кнопку «Упорядочить все» и выберите предпочтительное расположение: мозаичное, горизонтальное, вертикальное или каскадное.
Для небольших таблиц вы легко сможете визуально сравнить их данные. Хотя, конечно, риск ошибки из-за человеческого фактора здесь присутствует.
Сравните два листа в одной книге.
Иногда 2 листа, которые вы хотите сравнить, находятся в одной книге. Чтобы просмотреть их рядом, выполните следующие действия.
- Откройте файл, перейдите на вкладку «Вид» и нажмите кнопку «Новое окно».
- Это действие откроет тот же файл в дополнительном окне.
- Включите режим просмотра «Рядом», нажав соответствующую кнопку на ленте.
- Выберите лист 1 в первом окне и лист 2 во втором окне.
Быстрое выделение значений, которые различаются.
Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии или же отсутствии отличий между записями, вам нужно на вкладке «Главная», выбрать кнопку «Найти и выделить», предварительно выделив диапазон, где надо сравнить данные в Эксель.
В открывшемся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».
К сожалению, это нормально работает только для сравнения 2 столбцов (или строк), а не всей таблицы целиком. Кроме того, строки должны быть одинаковым образом отсортированы, поскольку ячейки сравниваются построчно. Если у вас товары отсортированы по-разному, либо вообще различный ассортимент, то никакой пользы от этого метода не будет.
Формула сравнения.
Это самый простой способ соотнесения таблиц в Excel, который позволяет идентифицировать в них ячейки с разными значениями.
Простейший вариант – сопоставление двух таблиц, находящихся на одном листе. Можно соотносить как числовые, так и текстовые значения, всего-навсего прописав в одной из соседних ячеек формулу их равенства. В результате при тождестве ячеек мы получим сообщение ИСТИНА, в противном случае — ЛОЖЬ.
Предположим, у нас имеется два прайс-листа (старый и новый), в которых на некоторые товары различаются цены. При этом порядок следования товаров одинаков. Поэтому мы можем при помощи простейшей формулы прямо на этом же листе сравнить идентичные ячейки с данными.
=G3=C3
Результатом будет являться либо ИСТИНА (в случае совпадения), либо ЛОЖЬ (при отрицательном результате).
Таким же образом можно производить сравнение данных в таблицах, которые расположены на разных листах. Процедура сравнения практически точно такая, как была описана выше, кроме того факта, что при создании формулы придется переключаться между листами. В нашем случае выражение будет иметь следующий вид:
=G3=Лист2!C3
Если ваши таблицы достаточно велики, то довольно утомительно будет просматривать колонку I на предмет поиска слова ЛОЖЬ. Поэтому может быть полезным сразу определить — а есть ли вообще несовпадения?
Можно подсчитать общее количество расхождений и сразу вывести это число где-нибудь отдельно.
=СУММПРОИЗВ(—(C3:C25<>G3:G25))
или можно сделать это формулой массива
{=СУММ(—(C3:C25<>G3:G25))}
Если формула возвращает ноль, значит, данные полностью совпадают. Ну а ежели результат положительный, то нужны более детальные исследования. О них мы и поговорим далее.
Как произвести сравнение на отдельном листе.
Чтобы сравнить два листа Эксель на предмет различий, просто откройте новый пустой лист, введите следующую формулу в ячейку A1, а затем скопируйте ее вниз и вправо, перетащив маркер заполнения:
=ЕСЛИ(Лист1!A1 <> Лист2!A1; «Лист1:»&Лист1!A1&» — Лист2:»&Лист2!A1; «»)
Поскольку мы используем относительные ссылки на ячейки, формула будет меняться в зависимости от расположения столбца и строки. В результате формула в A1 будет сравнивать ячейки A1 в Лист1 и Лист2, формула в B1 будет сравнивать ячейку B1 на обоих листах и так далее. Результат будет выглядеть примерно так:
В результате вы получите отчет о различиях на новом листе. Думаю, это достаточно информативно.
Как вы можете видеть на приведенном выше рисунке, формула сравнивает 2 листа, находит ячейки с разными значениями и отображает различия в соответствующих местах.
Обратите внимание, что в отчете о различиях (ячейка D4) даты представлены числами, поскольку в таком виде они хранятся во внутренней системе Excel, что не очень удобно для анализа различий между ними.
Как сравнить две таблицы при помощи формулы ВПР.
Предположим, у нас снова 2 прайс-листа. Однако, в отличие от предыдущего примера, они содержат разное количество товаров, да и сами товары расположены в произвольном порядке. Поэтому описанный выше способ, когда мы построчно сравнивали две таблицы, здесь не сработает.
Нам необходимо последовательно взять каждый товар из одной таблицы, найти его во второй, извлечь оттуда его цену и сравнить с первоначальной ценой. Здесь нам не обойтись без формул поиска. Поможет нам функция ВПР.
Для наглядности расположим обе таблицы на одном листе.
Формула
=ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$18;2;0);0)
берёт наименование товара из второго прайса, ищет его в первом, и в случае удачи извлекает соответствующую цену из первой таблицы. Она будет записана рядом с новой ценой в столбце H. Если поиск завершился неудачей, то есть такого товара ранее не было, то ставим 0. Таким образом, старая и новая цена оказываются рядом, и их легко сравнить простейшей операцией вычитания. Что и сделано в столбце I.
Аналогично можно сопоставлять и данные на разных листах. Просто нужно соответствующим образом изменить ссылки в формуле, указав в них имя листа.
Вот еще один пример. Возьмём за основу более новую информацию, то есть второй прайс. Выведем только сведения о том, какие цены и на какие товары изменились. А то, что не изменилось, выводить в итоговом отчёте не будем.
Разберём действия пошагово. Формула в ячейке J3 ищет наименование товара из первой позиции второй таблицы внутри первой. Если таковое найдено, извлекается соответствующая этому товару старая цена и сразу же сравнивается с новой. Если они одинаковы, то в ячейку записывается пустота «».
=ЕСЛИ(ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$18;2;0);0)=G3;»»;ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$18;2;0);0))
Таким образом, в ячейке J3 будет указана старая цена, если ее удастся найти, а также если она не равна новой.
Далее если ячейка J3 не пустая, то в I3 будет указано наименование товара —
=ЕСЛИ(J3<>»»;F3;»»)
а в K3 – его новая цена:
=ЕСЛИ(J3<>»»;G3;»»)
Ну а далее в L3 просто найдем разность K3-J3.
Таким образом, в отчёте сравнения мы видим только несовпадения значений второй таблицы по сравнению с первой.
И еще один пример, который может быть полезен. Попытаемся сравнить в итоговой таблице оба прайс-листа с эталонным общим списком товаров.
В ячейке B2 запишем формулу
=ЕСЛИ(ЕНД(ВПР(A2;Прайс1!$B$3:$B$19;1;0));»Нет»;ВПР(A2;Прайс1!$B$3:$C$19;2;0))
Так мы выясним, какие цены из второй таблицы встречаются в первой.
Для каждой цены из первого прайса проверяем, совпадает ли она с новыми данными —
=ЕСЛИ(ЕНД(ВПР(A2;Прайс2!$B$3:$B$22;1;0));»Нет»;ВПР(A2;Прайс2!$B$3:$C$22;2;0))
Эталонный список находится у нас в столбце A. В результате мы получили своего рода сводную таблицу цен – старых и новых.
Еще несколько примеров использования функции ВПР для сравнения таблиц вы можете найти в этой статье.
Выделение различий между таблицами цветом.
Чтобы закрасить ячейки с разными значениями на двух листах выбранным вами цветом, используйте функцию условного форматирования Excel:
- На листе, где вы хотите выделить различия, выберите все используемые ячейки. Для этого щелкните верхнюю левую ячейку используемого диапазона, обычно A1, и нажмите
Ctrl + Shift + End, чтобы расширить выделение до последней использованной ячейки. - На вкладке Главная кликните Условное форматирование > Новое правило и создайте его со следующей формулой:
=A1<>Лист2!A1
Где Лист2 — это имя другого листа, который вы сравниваете с текущим.
В результате ячейки с разными значениями будут выделены выбранным вами цветом:
Если вы не очень хорошо знакомы с условным форматированием, вы можете найти подробные инструкции по созданию правила в следующем руководстве: Условное форматирование Excel в зависимости от значения ячейки.
Сравнение при помощи сводной таблицы.
Хороший вариант сравнения — объединить таблицы в единую сводную, и там уже сопоставлять данные между собой.
Вернемся к нашему примеру с двумя прайс-листами. Объединим наши данные на одном листе. Чтобы отличить данные одной таблицы от другой, добавим вспомогательный столбец D и укажем в нем, откуда именно взяты данные:
А теперь приступим к созданию сводной таблицы. Я не буду подробно останавливаться на том, как мы это будем делать. Все шаги подробно описаны в статье Как сделать сводную таблицу в Excel.
Поместим поле Товар в область строк, поле Прайс в область столбцов и поле Цена в область значений.
Как видно на скриншоте ниже, для каждого товара, встречающегося хотя бы в одном из прайсов, указана цена.
Сводная таблица автоматически сформирует общий список всех товаров из старого и нового прайсов и сортирует их по алфавиту. Причём, без повторов. У новых товаров нет старой цены, у удаленных товаров — новой цены. Легко увидеть изменения цен, если таковые были.
Общие итоги здесь смысла не имеют, и их можно отключить на вкладке Конструктор — Общие итоги — Отключить для строк и столбцов.
Если изменятся цены, то достаточно просто обновить созданную сводную, щелкнув по ней правой кнопкой мыши — Обновить. А вот если изменится список товаров или добавится новый файл для сравнения, то придется заново формировать исходный массив или же добавлять в него новые данные.
Плюсы: такой подход на порядок быстрее работает с большими объемами данных, чем ВПР. Можно сравнить данные нескольких таблиц.
Минусы: надо вручную копировать данные в одну большую таблицу и добавлять столбец с названием исходного файла.
Сравнение таблиц с помощью Power Query
Power Query — это бесплатная надстройка для Microsoft Excel, позволяющая загружать в него данные практически из любых источников и преобразовывать потом их желаемым образом. В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке Данные, а для более ранних версий ее нужно отдельно скачать с сайта Microsoft и установить.
Перед загрузкой наших прайс-листов в Power Query их необходимо преобразовать сначала в умные таблицы. Для этого выделим диапазон с данными и нажмем на клавиатуре сочетание Ctrl+T или выберем на ленте вкладку Главная — Форматировать как таблицу. Имена созданных таблиц можно изменить на вкладке Конструктор (я оставлю стандартные Таблица1 и Таблица2, которые генерируются по умолчанию).
Загрузите первый прайс в Power Query с помощью кнопки Из таблицы/диапазона на вкладке Данные.
После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить — Закрыть и загрузить в…
В появившемся затем окне выбираем «Только создать подключение».
Повторите те же действия с новым прайс-листом.
Теперь создадим третий запрос, который будет объединять и сравнивать данных из предыдущих двух. Для этого выберем на вкладке Данные — Получить данные — Объединить запросы — Объединить. Все шаги вы видите на скриншоте ниже.
В окне объединения выберем в выпадающих списках наши таблицы, выделим в них столбцы с названиями товаров и в нижней части определим способ объединения — Полное внешнее.
После нажатия на ОК должна появиться таблица из четырёх столбцов, где в четвертой колонке нужно развернуть вложенное содержимое с помощью двойной стрелки в шапке.
После нажатия вы увидите список столбцов из второго прайса. Выбираем Товар и Цена. Получаем следующую картину:
А теперь сравним цены. Идем на вкладку Добавление столбца и жмем на кнопку Условный столбец. А затем в открывшемся окне вводим несколько условий проверки с соответствующими им значениями, которые нужно отобразить:
Теперь осталось вернуться на вкладку Главная и нажать Закрыть и загрузить.
Получаем новый лист в нашей рабочей книге:
Примечание. Если в будущем в наших прайс-листах произойдут любые изменения (добавятся или удалятся строки, изменятся цены и т.д.), то достаточно будет лишь обновить наши запросы сочетанием клавиш Ctrl+Alt+F5 или кнопкой Обновить все на вкладке Данные.
Ведь все данные извлекаются из «умных» таблиц Excel, которые автоматически меняют свой размер при добавлении либо удалении из них какой-либо информации. Однако, помните, что имена столбцов в исходных таблицах не должны меняться, иначе получим ошибку «Столбец такой-то не найден!» при попытке обновить запрос.
Это, пожалуй, самый красивый и удобный способ из всех стандартных. Шустро работает с большими таблицами. Не требует ручных правок при изменении размеров.
Как видите, есть несколько способов сравнить две таблицы Excel, используя формулы или условное форматирование. Однако эти методы не подходят для комплексного сравнения из-за следующих ограничений:
- Они находят различия только в значениях, но не могут сравнивать формулы или форматирование ячеек.
- Многие из них не могут идентифицировать добавленные или удаленные строки и столбцы. Как только вы добавите или удалите строку / столбец на одном листе, все последующие строки / столбцы будут отмечены как отличия.
- Они хорошо работают на уровне листа, но не могут обнаруживать структурные различия на уровне книги Excel, к примеру добавление и удаление листов.
Эти проблемы решаются путем использования дополнений к Excel, о чем мы поговорим далее.
Как сравнить таблицы при помощи Ultimat Suite для Excel
Последняя версия Ultimate Suite включает более 60 новых функций и улучшений, самым интересным из которых является «Сравнение таблиц» — инструмент для сравнения листов или диапазонов данных в Excel.
Чтобы сделать сравнение более интуитивным и удобным, надстройка разработана следующим образом:
- Мастер шаг за шагом проведет вас через процесс и помогает настраивать различные параметры.
- Вы можете выбрать алгоритм сравнения, наиболее подходящий для ваших наборов данных.
- Вместо отчета о различиях сравниваемые листы отображаются в режиме просмотра различий, чтобы вы могли сразу просмотреть все различия и управлять ими по очереди.
Теперь давайте попробуем использовать этот инструмент на наших примерах электронных таблиц из предыдущего примера и посмотрим, отличаются ли результаты.
- Нажмите кнопку «Сравнить листы (Compare Two Sheets)» на вкладке «Данные Ablebits » в группе « Объединить »:
- Появится окно мастера с предложением выбрать два листа, которые вы хотите сравнить на предмет различий.
По умолчанию выбираются все листы, но вы также можете выбрать текущую таблицу 
или определенный диапазон 
, нажав соответствующую кнопку:
- На следующем шаге вы выбираете алгоритм сравнения:
- Без ключевых столбцов (по умолчанию) — лучше всего подходит для сложных документов, таких как счета-фактуры или контракты.
- По ключевым столбцам — подходит для таблиц, организованных по столбцам, которые имеют один или несколько уникальных идентификаторов, таких как номера заказов или артикулы товаров.
- По ячейке — лучше всего использовать для сравнения таблиц с одинаковым макетом и размером, таких как балансы или статистические отчеты.
Совет. Если вы не уверены, какой алгоритм подходит вам, выберите вариант по умолчанию (без ключевых столбцов). Какой бы алгоритм вы ни выбрали, надстройка найдет все различия, только выделит их по-разному (целые строки или отдельные ячейки).
На этом же шаге вы можете выбрать предпочтительный тип соответствия:
- Первое совпадение (по умолчанию) — сравнивает строку на листе 1 с первой найденной строкой на листе 2, которая имеет хотя бы одну совпадающую ячейку.
- Наилучшее совпадение — сравнивает строку на листе 1 со строкой на листе 2, которая имеет максимальное количество совпадающих ячеек.
- Полное совпадение — находит на обоих листах строки, которые имеют одинаковые значения во всех ячейках, и отмечает все остальные строки как уникальные.
В этом примере мы сначала будем искать наилучшее совпадение, используя режим сравнения без ключевых столбцов, который установлен по умолчанию.
- На следующем шаге укажите, какие различия следует выделить, а какие игнорировать, и как помечать различия.
Скрытые строки и столбцы не имеют значения, и мы говорим надстройке игнорировать их:
- Нажмите кнопку «Сравнить (Compare)» и подождите немного, пока программа обработает ваши данные и создаст их резервные копии. Резервные копии всегда создаются автоматически, поэтому вы можете не беспокоиться о сохранности своих данных.
После обработки листы открываются друг рядом с другом в специальном режиме просмотра различий с выбранным способом выделения отличий:
На скриншоте выше различия выделены цветами по умолчанию:
- Красные строки — строки, существующие только на Листе 2 (справа).
- Зеленые ячейки — различные ячейки в частично совпадающих строках.
А вот если мы выберем второй алгоритм сравнения — по ключевому столбцу, то нам будет предложено указать его. В нашем случае вполне можно ключевым столбцом обозначить «Товар».
После этого мы видим немного другой результат сравнения:
Как видите, основным здесь действительно является факт совпадения значений в столбцах B. Строки, в которых нет такого совпадения, сразу выделяются красным или фиолетовым. А вот если совпадение есть, тогда идем в столбец С и сравниваем записанную там цену. Зелёные ячейки как раз и показывают нам товары, которые имеются в обоих прайс-листах, но цена на них изменилась.
Не знаю как вам, но мне второй вариант представляется более информативным.
А что же дальше делать с этим сравнением?
Чтобы помочь вам просматривать различия и управлять ими, на каждом листе есть собственная вертикальная панель инструментов. Для неактивного рабочего листа (справа на нашем скриншоте) эта панель отключена. Чтобы активировать панель инструментов, просто выберите любую ячейку на соответствующем листе.
Используя её, вы последовательно просматриваете найденные различия и решаете, объединить их или игнорировать:
Как только последнее различие будет устранено, вам будет предложено сохранить книги и выйти из режима просмотра различий.
Если вы еще не закончили обработку различий, но хотели бы сделать перерыв, нажмите кнопку «Выйти из просмотра различий» в нижней части панели инструментов и выберите один из следующих вариантов:
- Сохраните внесенные вами изменения и сохраните оставшиеся различия (Save workbooks and keep difference marks),
- Сохраните внесенные вами изменения и удалите оставшиеся различия (Save workbooks and remove difference marks),
- Восстановите исходные книги из резервных копий (Restore workbooks from backup copies).
Вот как вы можете сравнить два листа в Excel при помощи инструмента сравнения Compare Two Sheets (надеюсь, он вам понравился 
Если вам интересно попробовать, полнофункциональная ознакомительная версия доступна для загрузки здесь .
Колонки сравнивают для того, чтобы, например, в отчетах не было дубликатов. Или, наоборот, для проверки правильности заполнения — с поиском непохожих значений. И проще всего выполнять сравнение двух столбцов на совпадение в Excel — для этого есть 6 способов.
1 Сравнение с помощью простого поиска
При наличии небольшой по размеру таблицы заниматься сравнением можно практически вручную. Для этого достаточно выполнить несколько простых действий.
- Перейти на главную вкладку табличного процессора.
- В группе «Редактирование» выбрать пункт поиска.
- Выделить столбец, в котором будет выполняться поиск совпадений — например, второй.
- Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения.
Если значение обнаружено, результатом станет выделение нужной ячейки. Однако с помощью такого способа можно работать только с небольшими столбцами. И, если это просто цифры, так можно сделать и без поиска — определяя совпадения визуально. Впрочем, если в колонках записаны большие объемы текста, даже такая простая методика позволит упростить поиск точного совпадения.
2 Операторы ЕСЛИ и СЧЕТЕСЛИ
Еще один способ сравнения значений в двух столбцах Excel подходит для таблиц практически неограниченного размера. Он основан на применении условного оператора ЕСЛИ и отличается от других методик тем, что для анализа совпадений берется только указанная в формуле часть, а не все значения массива. Порядок действий при использовании методики тоже не слишком сложный и подойдет даже для начинающего пользователя Excel.
- Сравниваемые столбцы размещаются на одном листе. Не обязательно, чтобы они находились рядом друг с другом.
- В третьем столбце, например, в ячейке J6, ввести формулу такого типа: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(H6;$I$6:$I$14;0));»;H6)
- Протянуть формулу до конца столбца.
Результатом станет появление в третьей колонке всех совпадающих значений. Причем H6 в примере — это первая ячейка одного из сравниваемых столбцов. А диапазон $I$6:$I$14 — все значения второй участвующей в сравнении колонки. Функция будет последовательно сравнивать данные и размещать только те из них, которые совпали. Однако выделения обнаруженных совпадений не происходит, поэтому методика подходит далеко не для всех ситуаций.
Еще один способ предполагает поиск не просто дубликатов в разных колонках, но и их расположения в пределах одной строки. Для этого можно применить все тот же оператор ЕСЛИ, добавив к нему еще одну функцию Excel — И. Формула поиска дубликатов для данного примера будет следующей: =ЕСЛИ(И(H6=I6); «Совпадают»; «») — ее точно так же размещают в ячейке J6 и протягивают до самого низа проверяемого диапазона. При наличии совпадений появится указанная надпись (можно выбрать «Совпадают» или «Совпадение»), при отсутствии — будет выдаваться пустота.
Тот же способ подойдет и для сравнения сразу большого количества колонок с данными на точное совпадение не только значения, но и строки. Для этого применяется уже не оператор ЕСЛИ, а функция СЧЕТЕСЛИ. Принцип написания и размещения формулы похожий.
Она имеет вид =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3; «Совпадают»;») и должна размещаться в верхней части следующего столбца с протягиванием вниз. Однако в формулу добавляется еще количество сравниваемых колонок — в данном случае, три.
Если поставить вместо тройки двойку, результатом будет поиск только тех совпадений с первой колонкой, которые присутствуют в одном из других столбцов. Причем, тройные дубликаты формула проигнорирует. Так же как и совпадения второй и третьей колонки.
3 Формула подстановки ВПР
Принцип действия еще одной функции для поиска дубликатов напоминает первый способ использованием оператора ЕСЛИ. Но вместо ПОИСКПОЗ применяется ВПР, которую можно расшифровать как «Вертикальный Просмотр». Для сравнения двух столбцов из похожего примера следует ввести в верхнюю ячейку (J6) третьей колонки формулу =ВПР(H6;$I$6:$I$15;1;0) и протянуть ее в самый низ, до J15.
С помощью этой функции не просто просматриваются и сравниваются повторяющиеся данные — результаты проверки устанавливаются четко напротив сравниваемого значения в первом столбце. Если программа не нашла совпадений, выдается #Н/Д.
4 Функция СОВПАД
Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:
- В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
- Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
- Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.
Результатом такого сравнения будет вывод уже не найденного совпадающего значения, а булевой переменной. В случае нахождения это будет «ИСТИНА». Если ни одного совпадения не было обнаружено — в ячейке появится надпись «ЛОЖЬ».
Стоит отметить, что функция СОВПАД сравнивает и числа, и другие виды данных с учетом верхнего регистра. А одним из самых распространенных способом использования такой формулы сравнения двух столбцов в Excel является поиска информации в базе данных. Например, отдельных видов мебели в каталоге.
5 Сравнение с выделением совпадений цветом
В поисках совпадений между данными в 2 столбцах пользователю Excel может понадобиться выделить найденные дубликаты, чтобы их было легко найти. Это позволит упростить поиск ячеек, в которых находятся совпадающие значения. Выделять совпадения и различия можно цветом — для этого понадобится применить условное форматирование.
Порядок действий для применения методики следующий:
- Перейти на главную вкладку табличного процессора.
- Выделить диапазон, в котором будут сравниваться столбцы.
- Выбрать пункт условного форматирования.
- Перейти к пункту «Правила выделения ячеек».
- Выбрать «Повторяющиеся значения».
- В открывшемся окне указать, как именно будут выделяться совпадения в первой и второй колонке. Например, красным текстом, если цвет остальных сообщений стандартный черный. Затем указать, что выделяться будут именно повторяющиеся ячейки.
Теперь можно снять выделение и сравнить совпадающие значения, которые будут заметно отличаться от остальной информации. Точно так же можно выделить, например, и уникальную информацию. Для этого следует выбрать вместо «повторяющихся» второй вариант — «уникальные».
6 Надстройка Inquire
Начиная с версий MS Excel 2013 табличный процессор позволяет воспользоваться еще одной методикой — специальной надстройкой Inquire. Она предназначена для того, чтобы сравнивать не колонки, а два файла .XLS или .XLSX в поисках не только совпадений, но и другой полезной информации.
Для использования способа придется расположить столбцы или целые блоки информации в разных книгах и удалить все остальные данные, кроме сравниваемой информации. Кроме того, для проверки необходимо, чтобы оба файла были одновременно открытыми.
Процесс использования надстройки включает такие действия:
- Перейти к параметрам электронной таблицы.
- Выбрать сначала надстройки, а затем управление надстройками COM.
- Отметить пункт Inquire и нажать «ОК».
- Перейти к вкладке Inquire.
- Нажать на кнопку Compare Files, указать, какие именно файлы будут сравниваться, и выбрать Compare.
- В открывшемся окне провести сравнения, используя показанные совпадения и различия между данными в столбцах.
У каждого варианта сравнения — свое цветовое решение. Так, зеленым цветом на примере выделены отличия. У совпадающих данных отсутствует выделение. А сравнение расчетных формул показало, что результаты отличаются все — и для выделения использован бирюзовый цвет.
Читайте также:
- 5 программ для совместной работы с документами
-
Как в Экселе протянуть формулу по строке или столбцу: 5 способов
У нас имеется две таблицы заказов, скопированных в один рабочий лист. Необходимо выполнить сравнение данных двух таблиц в Excel и проверить, которые позиции есть первой таблице, но нет во второй. Нет смысла вручную сравнивать значение каждой ячейки.
Сравнение двух столбцов на совпадения в Excel
Как сделать сравнение значений в Excel двух столбцов? Для решения данной задачи рекомендуем использовать условное форматирование, которое быстро выделить цветом позиции, находящиеся только в одном столбце. Рабочий лист с таблицами:
В первую очередь необходимо присвоить имена обоим таблицам. Благодаря этому легче понять, какие сравниваются диапазоны ячеек:
- Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя».
- В появившемся окне в поле «Имя:» введите значение – Таблица_1.
- Левой клавишей мышки сделайте щелчок по полю ввода «Диапазон:» и выделите диапазон: A2:A15. И нажмите ОК.
Для второго списка выполните те же действия только имя присвойте – Таблица_2. А диапазон укажите C2:C15 – соответственно.
Полезный совет! Имена диапазонов можно присваивать быстрее с помощью поля имен. Оно находится левее от строки формул. Просто выделяйте диапазоны ячеек, а в поле имен вводите соответствующее имя для диапазона и нажмите Enter.
Теперь воспользуемся условным форматированием, чтобы выполнить сравнение двух списков в Excel. Нам нужно получить следующий результат:
Позиции, которые есть в Таблице_1, но нет в Таблцие_2 будут отображаться зеленым цветом. В тоже время позиции, находящиеся в Таблице_2, но отсутствующие в Таблице_1, будут подсвечены синим цветом.
- Выделите диапазон первой таблицы: A2:A15 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
- В поле ввода введите формулу:
- Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет. На всех окнах жмем ОК.
- Выделите диапазон первого списка: C2:C15 и снова выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
- В поле ввода введите формулу:
- Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите синий цвет. На всех окнах жмем ОК.
Принцип сравнения данных двух столбцов в Excel
При определении условий для форматирования ячеек столбцов мы использовали функцию СЧЕТЕСЛИ. В данном примере эта функция проверяет сколько раз встречается значение второго аргумента (например, A2) в списке первого аргумента (например, Таблица_2). Если количество раз = 0 в таком случае формула возвращает значение ИСТИНА. В таком случае ячейке присваивается пользовательский формат, указанный в параметрах условного форматирования.
Скачать пример сравнения 2 таблицы в Excel
Ссылка во втором аргументе относительная, значит по очереди будут проверятся все ячейки выделенного диапазона (например, A2:A15). Например, для сравнения двух прайсов в Excel даже на разных листах. Вторая формула действует аналогично. Этот же принцип можно применять для разных подобных задач.
Сравнение двух таблиц
Имеем две таблицы (например, старая и новая версия прайс-листа), которые надо сравнить и оперативно найти отличия:
С ходу видно, что в новом прайсе что-то добавилось (финики, чеснок…), что-то пропало (ежевика, малина…), у каких-то товаров изменилась цена (инжир, дыня…). Нужно быстро найти и вывести все эти изменения.
Для любой задачи в Excel почти всегда есть больше одного решения (обычно 4-5). Для нашей проблемы можно использовать много разных подходов:
- функцию ВПР (VLOOKUP) — искать названия товаров из нового прайс-листа в старом и выводить старую цену рядом с новой, а потом ловить отличия
- объединить два списка в один и построить по нему потом сводную таблицу, где наглядно будут видны отличия
- использовать надстройку Power Query для Excel
Давайте разберем их все последовательно.
Способ 1. Сравнение таблиц функцией ВПР (VLOOKUP)
Если вы совсем не знакомы с этой замечательной функцией, то загляните сначала сюда и почитайте или посмотрите видеоурок по ней — сэкономите себе пару лет жизни.
Обычно эту функцию используют для подтягивания данных из одной таблицы в другую по совпадению какого-либо общего параметра. В данном случае, мы применим ее, чтобы подтянуть старые цены в новый прайс:
Те товары, напротив которых получилась ошибка #Н/Д — отсутствуют в старом списке, т.е. были добавлены. Изменения цены также хорошо видны.
Плюсы этого способа: просто и понятно, «классика жанра», что называется. Работает в любой версии Excel.
Минусы тоже есть. Для поиска добавленных в новый прайс товаров придется делать такую же процедуру в обратную сторону, т.е. подтягивать с помощью ВПР новые цены к старому прайсу. Если размеры таблиц завтра поменяются, то придется корректировать формулы. Ну, и на действительно больших таблицах (>100 тыс. строк) все это счастье будет прилично тормозить.
Способ 2. Сравнение таблиц с помощью сводной
Скопируем наши таблицы одна под другую, добавив столбец с названием прайс-листа, чтобы потом можно было понять из какого списка какая строка:
Теперь на основе созданной таблицы создадим сводную через Вставка — Сводная таблица (Insert — Pivot Table). Закинем поле Товар в область строк, поле Прайс в область столбцов и поле Цена в область значений:
Как видите, сводная таблица автоматически сформирует общий список всех товаров из старого и нового прайс-листов (без повторений!) и отсортирует продукты по алфавиту. Хорошо видно добавленные товары (у них нет старой цены), удаленные товары (у них нет новой цены) и изменения цен, если были.
Общие итоги в такой таблице смысла не имеют, и их можно отключить на вкладке Конструктор — Общие итоги — Отключить для строк и столбцов (Design — Grand Totals).
Если изменятся цены (но не количество товаров!), то достаточно просто обновить созданную сводную, щелкнув по ней правой кнопкой мыши — Обновить (Referesh).
Плюсы: такой подход на порядок быстрее работает с большими таблицами, чем ВПР.
Минусы: надо вручную копировать данные друг под друга и добавлять столбец с названием прайс-листа. Если размеры таблиц изменяются, то придется делать все заново.
Способ 3. Сравнение таблиц с помощью Power Query
Power Query — это бесплатная надстройка для Microsoft Excel, позволяющая загружать в Excel данные практически из любых источников и трансформировать потом эти данные любым желаемым образом. В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке Данные (Data), а для Excel 2010-2013 ее нужно отдельно скачать с сайта Microsoft и установить — получите новую вкладку Power Query.
Перед загрузкой наших прайс-листов в Power Query их необходимо преобразовать сначала в умные таблицы. Для этого выделим диапазон с данными и нажмем на клавиатуре сочетание Ctrl+T или выберем на ленте вкладку Главная — Форматировать как таблицу (Home — Format as Table). Имена созданных таблиц можно подкорректировать на вкладке Конструктор (я оставлю стандартные Таблица1 и Таблица2, которые получаются по-умолчанию).
Загрузите старый прайс в Power Query с помощью кнопки Из таблицы/диапазона (From Table/Range) с вкладки Данные (Data) или с вкладки Power Query (в зависимости от версии Excel). После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить — Закрыть и загрузить в… (Close & Load — Close & Load To…):
… и в появившемся затем окне выбрем Только создать подключение (Connection Only).
Повторите то же самое с новым прайс-листом.
Теперь создадим третий запрос, который будет объединять и сравнивать данных из предыдущих двух. Для этого выберем в Excel на вкладке Данные — Получить данные — Объединить запросы — Объединить (Data — Get Data — Merge Queries — Merge) или нажмем кнопку Объединить (Merge) на вкладке Power Query.
В окне объединения выберем в выпадающих списках наши таблицы, выделим в них столбцы с названиями товаров и в нижней части зададим способ объединения — Полное внешнее (Full Outer):
После нажатия на ОК должна появиться таблица из трех столбцов, где в третьем столбце нужно развернуть содержимое вложенных таблиц с помощью двойной стрелки в шапке:
В итоге получим слияние данных из обеих таблиц:
Названия столбцов в шапке лучше, конечно, переименовать двойным щелчком на более понятные:
А теперь самое интересное. Идем на вкладку Добавить столбец (Add Column) и жмем на кнопку Условный столбец (Conditional Column). А затем в открывшемся окне вводим несколько условий проверки с соответствующими им значениями на выходе:
Останется нажать на ОК и выгрузить получившийся отчет в Excel с помощью все той же кнопки Закрыть и загрузить (Close & Load) на вкладке Главная (Home):
Красота.
Причем, если в будущем в прайс-листах произойдут любые изменения (добавятся или удалятся строки, изменятся цены и т.д.), то достаточно будет лишь обновить наши запросы сочетанием клавиш Ctrl+Alt+F5 или кнопкой Обновить все (Refresh All) на вкладке Данные (Data).
Плюсы: Пожалуй, самый красивый и удобный способ из всех. Шустро работает с большими таблицами. Не требует ручных правок при изменении размеров таблиц.
Минусы: Требует установленной надстройки Power Query (в Excel 2010-2013) или Excel 2016. Имена столбцов в исходных данных не должны меняться, иначе получим ошибку «Столбец такой-то не найден!» при попытке обновить запрос.
Ссылки по теме
- Как собрать данные из всех файлов Excel в заданной папке с помощью Power Query
- Как найти совпадения между двумя списками в Excel
- Слияние двух списков без дубликатов
Сравнение 2-х таблиц в MS EXCEL
Смотрите такжеGTH-NV: Так?? Какая из десяти объектных средах. протокола полная информацияИли макросом -Оставьте в них
, то Excel выделит идентичны. В противном ПОИСКПОЗ. В ее сравнение данных в в Таблцие_2 будут
в Excel»». окна расположатся ровно
- на мониторе, заходимЭто можно сделать сСравним две таблицы имеющих.Тоже что и разбита?Обучаться этому уже о строках «ДА») но его нужно по 2 десятка
- цветом совпадения в случае — в первом аргументе находится Excel на разных отображаться зеленым цветом.
Есть еще другой на половину экрана на закладку «Вид» помощью формул (см. практически одинаковую структуру.Файлов нет, вероятно уvladimir-rok нет смысла, поскольку со сквозной нумерацией.
Простой вариант сравнения 2-х таблиц
писать строк, среди которых наших списках, если них есть различия. пара значений, которая листах. Воспользуемся условным В тоже время способ сравнить данные каждое (если их в раздел «Окно». столбец Е): =ЕСЛИ(ЕНД(ВПР(A7;Январь!$A$7:$A$81;1;0));»Нет»;»Есть») и Таблицы различаются значениями превышен объём.vikttur
: Идем на вкладку эра программистов «кустарей-одиночек»2.2. В ячейкеДа и фильтровать должно быть несколько опцию Формулу надо вводить должна быть найдена форматированием. Таким образом позиции, находящиеся в в столбцах, подсветив два). У такогоКнопка «Разделить»
=ЕСЛИ(ЕНД(ВПР(A7;Февраль!$A$7:$A$77;1;0));»Нет»;»Есть») в отдельных строках,Прочитайте правила форума, но в отдельном «Их» и смотрим закончилась. А1 «ПРОТОКОЛ-ДА» - по цвету в строк, удовлетворяющих условиямУникальные
как формулу массива, на исходном листе мы не только Таблице_2, но отсутствующие
их. О таком окна есть полосы(обведена красным цветом)Сравнение оборотов по счетам
некоторые наименования строк — там это столбце строки 6 иВ «серьезные» программистские количество строк «ДА». 2003 нельзя! поиска.- различия. т.е. после ввода
следующего месяца, то автоматически найдем все в Таблице_1, будут
Более наглядный вариант сравнения 2-х таблиц (но более сложный)
способе читайте в прокрутки. На картинке – делит одну произведем с помощью встречаются в одной тоже есть.vladimir-rok 7 столбца «С» коллективы уже никогда2.3. «ПРОТОКОЛ-НЕТ» -
_Boroda_
- МатрёнаЦветовое выделение, однако, не формулы в ячейку есть «Март». Просматриваемый отличия в значениях подсвечены синим цветом.
- статье «Как сравнить полосы прокрутки в
- большую таблицу Еxcel формул: =ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;2;0));0;ВПР($A7;Февраль!$A$7:$C77;2;0))-B7 и таблице, но в
- GTH-NV: Всем огромное спасибо! они как раз не попаду: возрастной строчки из «ЭТАЛОНА»,: Если обязательно хочется: Такая работа, надо всегда удобно, особенно жать не на диапазон определяется как ячеек, но иВыделите диапазон первой таблицы:
excel2.ru
Сравнение таблиц Excel.
два столбца в верхнем окне. Но, на отдельные фрагменты. =ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;3;0));0;ВПР($A7;Февраль!$A$7:$C77;3;0))-C7 другой могут отсутствовать.: Hugo, добрый вечер.GTH-NV таки и составляют ценз. которые покрасить, то можно
полагать, не разовая, для больших таблиц.Enter соединение значений диапазонов, выделим их цветом. A2:A15 и выберите
Excel на совпадение» если мы нажмем Границы этих фрагментовВ случае отсутствия соответствующейПусть на листах Январь Файлы прикрепляла. Оба: Доброго времени суток, сумму ячейки 6
ЦитатаНЕ нашли СЕБЯ по сделать так: а регламентная? Также, если внутри, а на определенных именами, вВ фирме может быть инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать
здесь. на нижнее окно можно двигать. Можно строки функция ВПР() и Февраль имеется по 21 кб, уважаемые форумчане!
или 7 столбца
(Hugo, 08.08.2013 в ИНФОРМАЦИИ ИЗ СВОЕГОВ книге 2Chibert самих списков элементыCtrl+Shift+Enter пары. Таким образом более ста сотрудников,
правило»- «Использовать формулу
Можно на основании (сделаем активным нижнюю перемещать содержимое этих возвращает ошибку #Н/Д, две таблицы с пробую еще раз.Имеется две таблицы «Е», мысль что
15:09, в сообщении СТОЛБА А в сбоку написать ссылки: Закончить нужно через могут повторяться, то. выполняется сравнение строк среди которых одни
для определения форматированных данных таблицы для таблицу), то полоса фрагментов таблицы. которая обрабатывается связкой
оборотами за периодGTH-NV эксель в разных бы автоматом Е №12)200?’200px’:»+(this.scrollHeight+5)+’px’);»>сравнивать только по ИНФОРМАЦИИ ИЗ СТОЛБА
на столбец А неделю. этот способ неЕсли с отличающимися ячейками по двум признакам
увольняются другие трудоустраиваются, ячеек:». большей визуализации, сделать прорутки появится в
Перед тем, как нажать функций ЕНД() и по соответствующим счетам.
: Уважаемые форумчане ! файлах (т.е. две «минус» С но названию улицы, когда
А в «Для книги 1 иВ любом случае подойдет. надо что сделать, – фамилия и третьи уходят вВ поле ввода введите график. Читайте в нижнем (активном) окне. кнопку «Разделить» ЕСЛИ(), заменяя ошибкуКак видно из рисунков,Откликнитесь пожалуйста на выгрузки счетов-фактур из конечно с привязкой в списке разные контроля» потом уже использовать
только только половинаВ качестве альтернативы можно то подойдет другой зарплата. Для найденных
отпуск или на формулу:
статье «Как сделатьВ активном окне, установите курсор в на 0 (в таблицы различаются: просьбу о помощи разных систем - на код (столбец города из разных
(на листе этого условное форматирование совпадет (чуть больше использовать функцию быстрый способ: выделите совпадений возвращается число, больничный и т.п.Щелкните по кнопке «Формат» график в Excel». таблицы Excel работают
нужном месте таблицы. случае отсутствия строки)Наличием (отсутствием) строк (наименований ! бухгалтерского и управленческого D) районов — неправильно протокола полная информацияСм. файл1 — знаю этоСЧЁТЕСЛИ
оба столбца и что по сути В следствии чего и на вкладкеУ нас имеется две все функции строки Границы фрагментов таблицы или на значение
счетов). Например, вGTH-NV учета), которые необходимоvladimir-rokТакой критерий был нужен о строках «НЕТ»)А вообще, проще заранее), а остальное
(COUNTIF) нажмите клавишу для Excel является могут возникнуть сложности «Заливка» укажите зеленый таблицы заказов, скопированных меню. располагаются выше и
из соответствующего столбца. таблице на листе: Прошу модератора закрыть сверить. Пока додумалась: Таких цифр большинство,
автору заявки. со сквозной нумерацией. всего написать сбоку надо будет делатьиз категорииF5 истиной. Поэтому следует со сравнением данных цвет. На всех
в один рабочийСейчас эти полосы прокрутки левее курсора.С помощью Условного форматирования Январь отсутствует счет тему — буду
excel-office.ru
Сравнение двух таблиц в Excel на совпадение значений в столбцах
только до сводной цель отсеять теИ этот критерий2.4. В ячейке формулу и растянуть руками. Но хотяСтатистические, затем в открывшемся использовать функцию =НЕ(), по зарплате. Например, окнах жмем ОК. лист. Необходимо выполнить
Сравнение двух столбцов на совпадения в Excel
передвигают таблицы синхронно.Пример. можно выделить расхождения 26 (см. файл просить помощи в таблицы, но предварительно что отнимаются в в данной ситуации А1 «ПРОТОКОЛ-НЕТ» - ее вниз
бы сократить ручную, которая подсчитывает сколько окне кнопку которая позволяет заменить фамилии сотрудников будут
- Выделите диапазон первого списка: сравнение данных двух
- ЧтобыТаблица «Домашй бюджет» (например, красным цветом).
- примера), а в другом форуме. приходиться копировать данные «0» (Е «минус» не может быть
количество строк «НЕТ».200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПОИСКПОЗ(A1;[Книга1.xls]Лист1!$A$1:$A$5000;) работу до 1700-1800 раз каждый элементВыделить (Special) значение ИСТИНА на
постоянно в разной C2:C15 и снова таблиц в Excelтаблицы Excel перемещать независимо большая. Мы ееПо аналогии с задачей таблице на листеВсем кто зашел в один файл, С) для выяснения иным.
2.5. Ячейка А1 «ПРОТОКОЛ-ДА»Там, где не строк! из второго списка-
ЛОЖЬ. Иначе будет
последовательности. Как сделать выберите инструмент: «ГЛАВНАЯ»-«Условное и проверить, которые друг от друга разделим на фрагменты. решенной в статье Сравнение Февраль отсутствуют счет и почитал, спасибо. сцеплять текстовые поля
- причин отклонения поВообще-то, каноны традиционного + Ячейка А1 ошибка, а любоеСделал два файлика встречался в первом:
- Отличия по строкам (Row применено форматирование для
- сравнение двух таблиц форматирование»-«Создать правило»- «Использовать позиции есть первой, нажимаем кнопку «Сихронная Нам нужно увидеть
- 2-х списков в 10 и его Делайте выводы!!! (код контрагента, № оставшимся. контроля 2-х информаций
- «ПРОТОКОЛ-НЕТ» = КОЛИЧЕСТВО число, там есть
- для примера («1″Полученный в результате ноль differences) ячеек значение которых Excel на разных
Принцип сравнения данных двух столбцов в Excel
формулу для определения таблице, но нет прокрутка» (на рисунке графы дохода и MS EXCEL можно субсчета.AndreTM договора, № документа,vikttur всегда требуют исполнения СТРОК В ФАЙЛЕ повтор. Можно отфильтровать. — из «образца», и говорит об. В последних версиях совпали. Для каждой листах? форматированных ячеек:».
во второй. Нет чуть выше, обведена
итоговые цифры. Ставим сформировать список наименованийРазными значениями в строках.: А что выводы-то дату документа) и: В Вашем файле двухстороннего контроля при «Эталон».или «2» — из отличиях. Excel 2007/2010 можно не найденной пары
exceltable.com
Сравнение данных в Excel на разных листах
Решить эту непростую задачуВ поле ввода введите смысла вручную сравнивать зеленым цветом). Теперь курсор на ячейку счетов, включающий ВСЕ Например, по счету делать? Рабочая неделя, только потом формировать нет данных в оптимальном критерии сравнения.3. В алгоритме контроляКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—ЕЧИСЛОПОИСКПОЗ(A1;[Книга1.xls]Лист1!$A$1:$A$5000;)) того, что нужноИ, наконец, «высший пилотаж» также воспользоваться кнопкой значений (то есть нам поможет условное формулу: значение каждой ячейки. каждое окно прокручиваестя
Сравнение двух листов в Excel
«Продукты». наименования счетов из 57 обороты за между прочим… сводную таблицу и столбце Е. Выdimsput файл «Эталон» яМатрёна проверить). Соображаю уже — можно вывестиНайти и выделить (Find – несоответствие) &B2&$C2 форматирование. Для примера,Щелкните по кнопке «Формат»Как сделать сравнение значений самомтоятельно.
Нажимаем кнопку «Разделить». Получилось: обоих таблиц (без январь и февральЯ вот вчера по ней выявлять о каком файле?: Добрый день
использовала в качестве: Chibert!
- плохо (надо домой отличия отдельным списком. & Select) - в диапазоне Фамилия&Зарплата,
- возьмем данные за и на вкладке в Excel двухПосле работы с таблицами,
- Голубые границы разделили нашу повторов). Затем вывести не совпадают.
- утром начал было расхождения.Вы не понялиПомогите пожалуйста сделать
- ВЕДУЩЕГО, а файлПосмотрите мои
- идти…), примеры собрал Для этого придется Выделение группы ячеек функция ПОИСКПОЗ возвращает
- февраль и март, «Заливка» укажите синий столбцов? Для решения закрывают их кнопкой
- таблицу на черыре разницу по столбцам.
- Если структуры таблиц примерно делать вам примерныйДавно ищу менее моего вопроса о
- сравнение в Excel «Для контроля» в
»5 копеек»! так-себе, но в использовать формулу массива: (Go to Special) ошибку. Ошибочное значение как показано на цвет. На всех
данной задачи рекомендуем
Принцип сравнения двух диапазонов данных в Excel на разных листах:
«Закрыть» (Х), но части. Эти границыДля этого необходимо: одинаковы (большинство наименований макрос, но… поскольку трудоемкий вариант и цифрах. Цифра -есть две таблицы качестве ВЕДОМОГО.Макрос в файле целом картину ониВыглядит страшновато, но своюна вкладке не является логическим рисунке: окнах жмем ОК. использовать условное форматирование, именно той таблицы, можно двигать. НажимаемС помощью формулы массива счетов (строк) совпадают, хочется, чтобы было на Вашем форуме это 0, 1, на разных вкладкаChibert: Матрёна, Вам МЕГА-респект!!! Chibert.xls: запускать «кнопкой» отражают. работу выполняет отличноГлавная (Home) значением. Поэтому исползаемЧтобы найти изменения наПри определении условий для которое быстро выделить которую хотим закрыть на границу левой =ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(Январь;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Январь);0)); ИНДЕКС(Февраль;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Февраль);0)));»») сформировать количество и наименования ещё и «красиво», наткнулась на похожую 2,…9надо их сравнить ОЧЕНЬ помогло!!! можно с листовChibert ;)Excel выделит ячейки, отличающиеся
exceltable.com
Поиск отличий в двух списках
функцию ЕСЛИОШИБКА, которая зарплатных листах: форматирования ячеек столбцов цветом позиции, находящиеся (обведена желтым цветом) мышкой и , в столбце А столбцов совпадают), то а у вас тему [url=http://www.excelworld.ru/forum/2-3300-1#top], где
Вариант 1. Синхронные списки
vladimir-rok и сделать заливкуОстальным тоже спасибо «ПРОТОКОЛ-ДА» и «ПРОТОКОЛ-НЕТ»,: Работа скорее всеChibert содержанием (по строкам). присвоит логическое значениеПерейдите на лист с мы использовали функцию только в одном или красной кнопкой удерживая мышку, перемещаем перечень счетов из можно сравнить две там код хоть
Матрена предложила очень: Я говорил про
в той и
за советы!!! либо — ALT
же разовая. Потому: Нужно сделать следующее Затем их можно для каждой ошибки именем «Март» и СЧЕТЕСЛИ. В данном столбце. Рабочий лист «Закрыть» вверху экрана границу. обоих таблиц (без таблицы. Проведем сравнение и простой, но на мой взгляд файл 3744043.xlsx уважаемого
другой таблицеHugo и F8 на отчаявшись и взялся в обработать, например: – ИСТИНА. Это выберите инструмент: «ФОРМУЛЫ»-«Определенные примере эта функция с таблицами: справа (тогда закроетсяЭто внешний вид листа повторов); двумя способами: один за пять минут изящное решение через SLAVICK так какнадоело каждый месяц: любом листе. «как обезьяна» делатьExcel 2003
залить цветом или как-то способствует присвоению нового имена»-«Присвоить имя». проверяет сколько раз
- В первую очередь необходимо активное окно).
- Excel разделенный наС помощью формулы массива
- проще в реализации, его не сделать, Макрос. К сожалению набор его формул
- делать в ручную,МатрёнаМакрос: все ручками -: еще визуально отформатировать формата только дляВ окне «Создание имени»
- встречается значение второго
Вариант 2. Перемешанные списки
присвоить имена обоимЧтобы области. Здесь видно =ЕСЛИОШИБКА(ИНДЕКС(Список; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(Список; « другой нагляднее. поскольку там не
собственных познаний не отлично смог соотнести а кол-во ячеек, строки1. «Спросит» файл но чисто физическиЕсть 2 таблицыочистить клавишей ячеек без совпадений для поля «Имя:» аргумента (например, A2) таблицам. Благодаря этомувернуть окно в первоначальный две горизонтальные иС помощью формулы =ЕСЛИ(ЕНД(ВПР($B5;Январь!$A$7:$C$81;2;0));0;ВПР($B5;Январь!$A$7:$C$81;2;0))-
Сначала определим какие строки три строчки хватает, чтобы переделать 2 таблицы, теперь растёт.200?’200px’:»+(this.scrollHeight+5)+’px’);»>ReDim MATR_ETL(sch_VERT_ETL, 4) «Эталон» и файл внимание рассеивается через
похожей структуры: вDelete значений по зарплате введите значение – в списке первого легче понять, какие вид две вертикалные полосы
ЕСЛИ(ЕНД(ВПР($B5;Февраль!$A$7:$C$77;2;0));0;ВПР($B5;Февраль!$A$7:$C$77;2;0)) произвести сравнение (наименования счетов) присутствуютТак что, если макрос под себя вопрос о сопоставленииПожалуйста буду благодаренReDim MATR_KTR(sch_VERT_KTR, 4) «Для контроля». какой-то момент от первой около 4000
заполнить сразу все одинаковым в отношении к Фамилия.
аргумента (например, Таблица_2). сравниваются диапазоны ячеек:, нажимаем кнопку «Упорядочить прокрутки. Каждую часть оборотов по счетам;
в одной таблице, не надо -поэтому прошу Вашей
planetaexcel.ru
Сравнить таблицы и выделить совпадения цветом (Формулы)
цифр в соответствииЧитаем Правила форума,лишние. Более того,1.1. Имена файлов такой монотонной работы
строк и она значением, введя его следующему месяцу –Ниже в поле ввода Если количество разВыберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить все». Если окно таблицы можно перемещать
С помощью Условного форматирования но отсутствуют в нам же проще. помощи.
с аргументом с создаём свою тему. они неправильные - могут быть любыми; (не привык видимо, является «образцом», во и нажав марту. «Диапазон:» введите следующую = 0 в имя». одно, то оно
относительно других. выделить расхождения цветом, другой. Затем, вИ да, ещёПосле срабатывания макроса столбце D. прикладываем файл-пример. Эта отследите результат в нужно просто обычным
обычно более «творческое» второй около 5000
Ctrl+Enter
Типовая задача, возникающая периодически ссылку:
таком случае формулаВ появившемся окне в расположиться по экрануТеперь нам нужно а также выделить таблице, в которой
вчера существовал примерно хотелось бы загрузитьvikttur тема закрыта
Locals. способом поиска файлов что-то приходится делать)
строк и ееудалить все строки с перед каждым пользователемВыберите инструмент «ФОРМУЛЫ»-«Присвоить имя» возвращает значение ИСТИНА. поле «Имя:» введите ровно. Если оконсравнить счета встречающиеся только меньше строк отсутствует
такой вариант: два файла (файлы: Покажите на примере,vladimir-rokА чтоб не в каталоге найти и нереально заставить нужно проверить. выделенными ячейками, используя Excel — сравнить и в поле
В таком случае значение – Таблица_1. несколько, то выбратьдве таблицы Excel (можно в одной таблице (в наиболее полнойRussel прилагаю) и получить что Вы хотите: Добрый день. перебирать цикл в эти файлы по себя прямо ВЕСЬСовпадают (естественно только команду между собой два «Имя:» введите значение ячейке присваивается пользовательскийЛевой клавишей мышки сделайте вариант расположения окон
больше) (например, на рисунке таблице), выведем отчет:
в файле сверки получить.Необходимо сверить 2
цикле — применяйте очереди (в диалоговом день этим заниматься.
частично — процентовГлавная — Удалить - диапазона с данными — Зарплата. формат, указанный в щелчок по полю на экране.
. выше счета, содержащиеся о сравнении, представляющий
AndreTM два листа :И еще раз: массива данных на
словарь, будет значительно окошке макрос указывает Иначе все руками
на 50-60) первые Удалить строки с и найти различия
В поле «Диапазон:» введите параметрах условного форматирования. ввода «Диапазон:» иИногда, во время
В нашем примере только в таблице собой разницу по, я сделал выводы 1) «Совпадения»; 2) не называйте числа
предмет совпадения оных.
быстрее, и код имя файла, который сделать теоретически вполне столбцы таблиц (А).
листа (Home -
между ними. Способ ссылку:Скачать пример сравнения 2 выделите диапазон: A2:A15.
работы с несколькими
будем сравнивать семейный
Январь, выделены синим, столбцам (разница оборотов
и чуть не «Расхождения»
цифрами. В обоих таблицах короче/проще. он «просит») и реально (хоть иХотелось бы выделить Delete — Delete
решения, в данном
Теперь перейдите на лист таблицы в Excel И нажмите ОК.
таблицами, возникает необходимость бюджет за два а желтым выделены за январь и заплакал!Совпадения необходимы поvladimir-rok совпадает код, ноТ.к. думаю, что «топнуть» по ним глупо). определенным цветом все
Rows) случае, определяется типом
с именем «Февраль»Ссылка во втором аргументеДля второго списка выполните скрыть одно окно.
года. Это две счета только из февраль).
Tachkin всем 5-ти позициям: Извиняюсь за косноязычие.
в обоих файлах сравнивать только по
мышой.Chibert совпадения в первоми т.д. исходных данных. и выделите диапазон относительная, значит по те же действия Для этого существует разные таблицы на февральской таблицы).Основным недостатком этого подхода
: : код контрагента, конечно же числа.
имеется разная структура названию улицы, когда1.2. «Прочтет» информацию: Заранее ОГРОМНОЕ спасибо столбце (А) второйЕсли списки разного размераЕсли списки синхронизированы (отсортированы), ячеек B2:C12. очереди будут проверятся только имя присвойте кнопка «Скрыть окно» разных страницах Excel.Когда нужно
является, то, чтоЦитата № договора, №
Прикладываю пример. В написания, поэтому ВПР в списке разные из них. за идеи и
таблицы с первым и не отсортированы то все делаетсяА на панели «ГЛАВНАЯ» все ячейки выделенного – Таблица_2. А
— скрывается текущееОткрываем первую таблицу.
сравнить две таблицы Excel отчет о сравнении
Russel, 13.11.2014 в документа, дата документа, столбце «Н» ячейки не работает. Кто
города из разных советы!!! столбцом (А) первой (элементы идут в весьма несложно, т.к.
1.3. Скопирует ее
выберите «Условное форматирование»-«Создать диапазона (например, A2:A15). диапазон укажите C2:C15 окно. Затем нажимаем кнопкуили, когда работаем
таблиц не включает 16:52, в сообщении сумма документа. Если 5,6,7,8 хотел что ни будь сталкивался районов — неправильно, на «транзитные» листы:Hugo
таблицы. При этом разном порядке), то надо, по сути, правило»-«Использовать формулу для
Например, для сравнения – соответственно.Чтобы отобразить окно «Новое окно», затем с большими таблицами,
строки отсутствующие в № 7200?’200px’:»+(this.scrollHeight+5)+’px’);»>я сделал хотя бы одна бы тут(Н) показывались с такой проблемой?
то код писать «Эталон» и «Для: Вообще в примере выделить цветом желательно
придется идти другим сравнить значения в определения форматированных ячеек:». двух прайсов в
Полезный совет! Имена диапазонов снова, нажимаем кнопку с помощью кнопки сложно держать в
наиболее полной таблице. выводы из позиций не либо нули либо Кто может помочь не буду. контроля». совпадений нет.
сразу всю строку, путем.
соседних ячейках каждойВ поле ввода формул Excel даже на можно присваивать быстрее
«Отобразить окно» и «Открыть» уме все цифры Например, в рассматриваемомRussel
совпадает, то строчка отклонения так как
в решении даннойДа и работа
1.4. «Посчитает» количествоНо одно я
где было совпадениеСамое простое и быстрое строки. Как самый вводим следующее:
разных листах. Вторая с помощью поля из предложенного перечняНаходим нужный документ
из разных ячеек
нами случае, наиболее, а где начальные должна попасть в число из Наш
excelworld.ru
Сравнение 2 таблиц в которых данные частично совпадают (Формулы/Formulas)
проблемы. разовая — я
строк на этих сделал искусственно - с первой таблицей. решение: включить цветовое простой вариант -Щелкните по кнопке «Формат» формула действует аналогично. имен. Оно находится названий окон (иногда и открываем его. таблицы. Для того, полной таблицей является условия? Из чего лист «Расхождения».
дважды попадает вSLAVICK
её уже вчера
листах. добавил город. Реально ли? выделение отличий, используя используем формулу для и на вкладке Этот же принцип левее от строки несколько окон скрыто)
Он появляется в чтобы облегчить этот таблица на листе выводить? Какая формулаПри этом один
выборку. Надеюсь понятливо: Здравствуйте, Так? сделал1.5. Выполнит СОРТИРОВКУСперва вручную заменамиПростите, пожалуйста, чайника, условное форматирование. Выделите сравнения значений, выдающую «Заливка» укажите зеленый можно применять для формул. Просто выделяйте выбираем нужное и нашем новом окне. процесс существуют специальные Январь, в которой
была? и тот же написал.=ПОДСТАВИТЬ(ПРАВБ(B3;ДЛСТР(B3)-37);»)»;)Матрёна по столбу А привести всё к объяснил как смог.
оба диапазона с на выходе логические цвет. разных подобных задач. диапазоны ячеек, а
нажимем «ОК».Можно просто обычным функции. отсутствует счет 26AndreTM
документ может бытьviktturvladimir-rok: Как только «разберусь» на каждом листе. одному стандарту, затем Искал решение сам данными и выберите значенияНа всех окнах жмемКаждый месяц работник отдела
в поле именЧтобы сравнить данные способом открыть обеСначала вспомним, какими
из февральской таблицы.: Вот и некий отражен в БУ
: Формула столбца D:: Во вкладке «Наш» с синтаксисом и2. Построит 2 слить в одну и не нашел. на вкладкеИСТИНА (TRUE) ОК. кадров получает список вводите соответствующее имя таблицы, можно сделать таблицы.
способами можно сделатьЧтобы определить какая из
окончательный вариант...
и УУ несколькими200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ВПР(«*»&D2&»*»;наш!$B$3:$D$25;3;0)-СУММЕСЛИ($D$1:D1;D2;$E$1:E1);»») цифра одна а требованиями оного, так
протокола.
ячейку одинаково в Начал уже делатьГлавная — Условное форматирование
илиПосле ввода всех условий
сотрудников вместе с для диапазона и диаграмму для наглядности.Теперь, когда таблицы таблицу в Excel.
двух таблиц являетсяДля будущих пользующихся
excelworld.ru
Сравнение двух таблиц по нескольким столбцам (Формулы/Formulas)
строками (т.к. вСтоп! Так всегда во вкладке «Их»
сразу.2.1. «ПРОТОКОЛ-ДА» - обоих файлах поля, вручную — строк — Правила выделенияЛОЖЬ (FALSE) для форматирования Excel их окладами. Он нажмите Enter. Как сделать такие открыты,нажимаем кнопку «Рядом» Для этого смотрим наиболее полной нужно — внимательно изучайте системах разные принципы будет 0 в она разбита наКак рассматривать Ваше строчки из «ЭТАЛОНА», далее СЧЁТЕСЛИ()
300 сравнил и ячеек — Повторяющиеся: автоматически выделил цветом копирует список наТеперь воспользуемся условным форматированием, диаграммы, смотрите в (на картинке выделена статью «Как сделать ответить на 2 код. VBA спрашивает, деления сумм) -
повторяющихся строках. В несколько, как бы
конструктивное замечание относительно которыеНу а красить больше не могу значения (Home -Число несовпадений можно посчитать тех сотрудников зарплаты новый лист рабочей
чтобы выполнить сравнение статье «Как сделать красным цветом). таблицу в Excel». вопроса: Какие счета какой именно из сверка должна идти этом случае достаточно: их так сравнить, «длины» кода, еслинашли СЕБЯ по ИНФОРМАЦИИ
зачем? Фильтруйте по так жить… Conditional formatting - формулой: которых изменились по книги Excel. Задача двух списков в сравнительную диаграмму вВсе раскрытые книги ExcelДля того, чтобы в февральской таблице
файлов вы пытаетесь
по общей суммеКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(СЧЁТЕСЛИ($D$1:D1;D2);;ЕСЛИОШИБКА(ВПР(«*»&D2&»*»;наш!$B$3:$D$25;3;0);»»)) что бы выявить макрос будет записан
ИЗ СВОЕГО СТОЛБА >0 и порядок.
Матрёна Highlight cell rules=СУММПРОИЗВ(—(A2:A20<>B2:B20))
сравнению с предыдущим следующая: сравнить зарплату Excel. Нам нужно Excel» тут. размещаются в одном
разместить одновременно несколько таблиц отсутствуют в январской?
открыть. документа.нужно ли как-то
отклонения? с помощью макрорекордера? А в ИНФОРМАЦИИМожно конечно и: И абсолютно ПРАВЫ!
— Duplicate Values)или в английском варианте месяцем.
сотрудников, которая изменилась получить следующий результат:Как сделать таблицу окне. Затем можно
Excel и Какие счета вДля тестеров -Заранее спасибо. учитывать значения столбцаviktturЯ — не ИЗ СТОЛБА А покрасить с помощьюКогда сдавать?: =SUMPRODUCT(—(A2:A20<>B2:B20)) по отношению к семейного бюджета, смотрите
нажать кнопку «Восстановитьили январской таблице отсутствуют
проверяйте на файлахHugo С?: Кто посмел разбить профи, совсем не в «Для контроля»
УФ — ноМатрёнаЕсли выбрать опциюЕсли в результате получаемВ определенном условии существенное предыдущему месяцу. ДляПозиции, которые есть в
в статье «Таблица расположение окон» ичасти одной большой таблицы в январской? из сообщения №3.
: Здравствуйте,SLAVICKцифру
умею программировать в(на листе этого это долго.: Где таблицы?Повторяющиеся ноль — списки
значение имеет функция этого необходимо выполнить Таблице_1, но нет
excelworld.ru
«Домашний, семейный бюджет