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

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

Добрый день!

Эта статья посвящена решению такого вопроса, как сравнить две таблицы в 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 и ввести код:

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

Пусть на листах Январь и Февраль имеется две таблицы с оборотами за период по соответствующим счетам.

Как видно из рисунков, таблицы различаются:

  1. Наличием (отсутствием) строк (наименований счетов). Например, в таблице на листе Январь отсутствует счет 26 (см. файл примера ), а в таблице на листе Февраль отсутствуют счет 10 и его субсчета.
  2. Разными значениями в строках. Например, по счету 57 обороты за январь и февраль не совпадают.

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

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

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

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

Читайте также:  Тоновый режим телефона как включить на мобильном

Чтобы определить какая из двух таблиц является наиболее полной нужно ответить на 2 вопроса: Какие счета в февральской таблице отсутствуют в январской? и Какие счета в январской таблице отсутствуют в январской?

Это можно сделать с помощью формул (см. столбец Е): = ЕСЛИ(ЕНД(ВПР(A7;Январь!$A$7:$A$81;1;0));"Нет";"Есть") и = ЕСЛИ(ЕНД(ВПР(A7;Февраль!$A$7:$A$77;1;0));"Нет";"Есть")

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

В случае отсутствия соответствующей строки функция ВПР() возвращает ошибку #Н/Д, которая обрабатывается связкой функций ЕНД() и ЕСЛИ() , заменяя ошибку на 0 (в случае отсутствия строки) или на значение из соответствующего столбца.

С помощью Условного форматирования можно выделить расхождения (например, красным цветом).

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

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

Для этого необходимо:

  1. С помощью формулы массива = ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(Январь;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Январь);0)); ИНДЕКС(Февраль;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Февраль);0)));"") сформировать в столбце А перечень счетов из обоих таблиц (без повторов);
  2. С помощью формулы массива = ЕСЛИОШИБКА(ИНДЕКС(Список; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(Список; " , где Список — именованный диапазон представляет собой перечень счетов из обоих таблиц (столбец А), отсортировать список счетов, полученный на предыдущем этапе;
  3. С помощью формулы = ЕСЛИ(ЕНД(ВПР($B5;Январь!$A$7:$C$81;2;0));0;ВПР($B5;Январь!$A$7:$C$81;2;0))- ЕСЛИ(ЕНД(ВПР($B5;Февраль!$A$7:$C$77;2;0));0;ВПР($B5;Февраль!$A$7:$C$77;2;0)) произвести сравнение оборотов по счетам;
  4. С помощью Условного форматирования выделить расхождения цветом, а также выделить счета встречающиеся только в одной таблице (например, на рисунке выше счета, содержащиеся только в таблице Январь, выделены синим, а желтым выделены счета только из февральской таблицы).

Статья даёт ответы на следующие вопросы:

  • Как сравнить две таблицы в Excel?
  • Как сравнивать сложные таблицы в Excel?
  • Как производить сравнение таблиц в Excel с использованием функции ВПР()?
  • Как формировать уникальные идентификаторы строк, если их уникальность изначально определяется набором значений в нескольких столбцах?
  • Как фиксировать значения ячеек в формулах при копировании формул?

При работе с большими объемами информации пользователь может столкнуться с такой задачей, как сравнение двух табличных источников данных. При хранении данных в единой системе учета (например, системы на базе 1С Предприятие, системы, использующие SQL базы данных), для сравнения данных могут использоваться встроенные в систему или СУБД возможности. Как правило, для этого достаточно привлечь программиста, который напишет запрос к базе данных, или программный механизм отчета. С запросом может справиться и опытный пользователь, владеющий навыком написания запросов 1C, либо SQL.

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

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

Читайте также:  Значок звездочка возле слайда что означает

Рассмотрим решение задачи сравнения таблиц в Excel на примере. Мы имеем две таблицы, содержащие списки квартир. Источники выгрузки — 1С Предприятие (учёт строительства) и таблица в Excel (учёт продаж). Таблицы размещены в рабочей книге Excel на первом и втором листах соответственно.

Перед нами стоит задача сравнить эти списки по адресу. В первой таблице — все квартиры дома. Во второй таблице — только проданные квартиры и имя покупателя. Конечная цель — отобразить в первой таблице по каждой квартире имя покупателя (для тех квартир, которые были проданы). Задача осложняется тем, что адрес квартиры в каждой таблице является строительным и состоит из нескольких полей: 1) адрес корпуса (дома), 2) секция (подъезд), 3) этаж, 4) номер на этаже (например, от 1 до 4).

Для сравнения двух таблиц Excel нам нужно добиться того, чтобы в обеих таблицах каждая строка идентифицировалась бы одним полем, а не четырьмя. Получить такое поле можно объединив значения четырех полей адреса функцией Сцепить(). Назначение функции Сцепить() — объединение нескольких текстовых значений в одну строку. Значения в функции перечисляются через символ ";". В качестве значений могут выступать как адреса ячеек, так и произвольный текст , заданный в кавычках.

Шаг 1. Вставим в начале первой таблицы пустую колонку "A" и пропишем в ячейке этой колонки напротив первой строки с данными формулу:
=СЦЕПИТЬ(B3;"-";C3;"-";D3;"-";E3)
Для удобства визуального восприятия между значениями объединяемых ячеек мы установили символы "-".

Шаг 2. Скопируем формулу в нижеследующие ячейки колонки А.

Шаг 3. Далее выполним те же операции (шаг 1 и шаг 2) для таблицы 2 на втором листе рабочей книги. Теперь можно переходить к сравнению двух таблиц по значениям колонки "A".

Шаг 4. Для сравнения таблиц Excel по значениям следует воспользоваться функцией ВПР(). Назначение функции ВПР() — поиск значения в крайнем левом столбце таблицы и возвращение значения ячейки, находящейся в указанном столбце той же строки. Первый параметр — искомое значение. Второй параметр — таблица, в которой будет осуществляться поиск значения. Третий параметр — номер столбца, из ячейки которого в найденной строке будет возвращено значение. Четвертый параметр — тип поиска: ложь — точное совпадение, истина — приближенное совпадение. Поскольку выходная информация должна быть размещена в первой таблице (именно в нее требовалось добавить имена покупателей), то формулу будем прописывать в ней. Сформируем в свободной колонке справа от таблицы напротив первой строки данных формулу:
=ВПР(A3;Лист2!$A$3:$F$10;6;ЛОЖЬ)
При копировании формул "умный" Excel автоматически изменяет адресацию ячеек. В нашем случае искомое значение для каждой строки будет меняться: A3,A4 и т.д., а адрес таблицы, в которой ведется поиск, должен оставаться неизменным. Для этого зафиксируем ячейки в параметре адреса таблицы символами "$". Вместо "Лист2!A3:F10" делаем "Лист2!$A$3:$F$10".

Шаг 5. Скопируем формулу в нижеследующие ячейки результирующей колонки.

Результат решения задачи:

Ссылка на основную публикацию
Как сделать ссылку в статусе вконтакте
Всем привет! Эта статья будет небольшой, но полезной. Сегодня я вам расскажу о некоторой возможности ВКонтакте, а именно: ссылка словом....
Как прозвонить видеокарту мультиметром
Вначале статьи сразу сделаю оговорку. Статья не для профи, а для начинающих мастеров-компьютерщиков и для тех, кто самостоятельно хочет найти...
Как прошить андроид самому без компьютера
Операционная система андроид, установленная на подавляющем большинстве смартфонов, отличается стабильностью работы и многими другими преимуществами. Однако, в силу стечения некоторых...
Как сделать формат cdr
Рассмотрим основные варианты, с помощью которых можно создать файл формата CDR (CorelDRAW Image File). Зачастую выполнение данной задачи не потребудет...
Adblock detector