Информацию, оформленную в виде таблиц, гораздо удобнее анализировать и использовать в различных расчетах, но когда необходимо сравнить данные нескольких похожих таблиц, визуально все это сделать очень сложно. Подходящее программное обеспечение всегда может выручить в подобной ситуации, и далее мы рассмотрим, как сравнить две таблицы в Excel, используя для этого разные методы анализа.
Сравнить таблицы в Excel нажатием одной кнопки к сожалению, не получится, и мало того, возможно для сравнения придется еще и некоторым образом подготовить данные, а также написать формулу.
В зависимости от необходимого результата подбирается и способ сравнения данных из таблиц. Самый простой способ, это сравнение двух на первый взгляд одинаковых столбцов для выявления строк, в которых это отличие все же есть. Сравнивать таким образом можно как числовые значения, так и текст.
Сравним два столбца цифровых значений, в которых отличие имеется только в нескольких ячейках. Записав простую формулу в соседнем столбце, условие равенства двух ячеек «=B3=C3» , мы получим результат «ИСТИНА» , если содержимое ячеек одинаковое, и «ЛОЖ» , если содержимое ячеек отличается. Растянув формулу по всей высоте столбца сравниваемых значений очень легко будет найти отличающуюся ячейку.
Если нужно просто удостоверится в наличии или отсутствии отличий в столбцах, можно воспользоваться пунктом меню «Найти и выделить» , на вкладке «Главная» . Для этого нужно предварительно выделить сравниваемые столбцы, после чего выбирать уже необходимый пункт меню. В выпадающем списке необходимо выбрать «Выделить группу ячеек…» , а в появившемся окошке выбрать «отличия по строкам» .
Условное форматирование отличий в упорядоченных значениях
При желании можно применить к отличающимся ячейкам условное форматирование, делая заливку ячейки, меняя цвет текста и т.д. В этом случае нужно выбирать пункт «Условное форматирование»
, в выпадающем списке которого выбираем «Управление правилами»
.
В диспетчере правил выбираем пункт «Создать правило» , а в создании правил выбираем . Теперь мы можем задать формулу «=$B3<>$C3» для определения форматируемой ячейки, и задать для нее формат, нажав на кнопку «Формат» .
Теперь у нас имеется правило отбора ячеек, задано форматирование, и определен диапазон сравниваемый ячеек. После нажатия на кнопку «OK» , заданное нами правило будет применено.
Сравнение и форматирование отличий в неупорядоченных значениях
Сравнение таблиц Эксель не ограничивается сравнением упорядоченных значений. Иногда приходится сравнивать диапазоны перемешанных значений, в которых необходимо определить вхождение одного значения в диапазон других значений.
Например, у нас имеется набор значений, оформленный в виде двух столбцов, и еще один такой же набор значений. В первом наборе у нас имеются все значения от 1 до 20, а во втором некоторые значения отсутствуют и продублированы другими значениями. Наша задача выделить условным форматированием в первом наборе значения, которых нет во втором наборе.
Порядок действий следующий, выделяем первый набор данных, именуемый у нас «Столбец 1» , и в меню «Условное форматирование» выбираем пункт «Создать правило…» . В появившемся окошке выбираем , вписываем необходимую формулу «=СЧЁТЕСЛИ($C$3:$D$12;A3)=0» и выбираем способ форматирования.
В нашей формуле используется функция «СЧЁТЕСЛИ» , которая подсчитывает количество повторений значения из определенной ячейки «A3» в заданном диапазоне «$C$3:$D$12» , которым выступает наш второй столбец. В качестве сравниваемой ячейки необходимо указывать первую ячейку из диапазона значений, к которым будет применяться форматирование.
После применения созданного правила все ячейки с неповторяющимися значениями в другом наборе значений будут выделены указанным цветом.
Конечно же, есть и более сложные варианты сравнения двух таблиц в Excel, как например, сравнение цент товаров в новом и старом прайсах. Допустим, имеется две таблицы с ценами, и рядом с ценами в новой таблице нужно указать и старые цены для каждого товара, причем порядок товаров в списках не соблюдается.
Рядом с ценами в новой таблице в ячейке следующего столбца необходимо записать формулу, которая и будет производить выборку значений. В формуле мы будем применять функция «ВПР» , которая может вернуть значение из любого столбца в строке, в которой выполнилось условие поиска. Чтобы функция работала правильно, необходимо чтобы в столбце в каждой строке находились уникальные значения, по которым будет производится поиск. Если значения будут повторяться, учитываться будет только первое найденное.
Необходимая нам формула будет выглядеть следующим образом: «=ВПР(B18;$B$3:$C$10;2;ЛОЖЬ)» . Первое значение «B18» соответствует первой ячейке искомого наименования товара. Второе значение «$B$3:$C$10» означает постоянный адрес диапазона таблицы старого прайса, значения из которой нам понадобятся. Третье значение «2» означает номер столбца из выделенного диапазона, в ячейке которого мы и будем брать старую цену товара. И последнее значение «ЛОЖЬ» задает поиск только по точному совпадению значений. После протаскивания формулы по всему столбцу новой таблицы мы получим в этом столбце старые значения цен по каждой позиции, имеющейся в новой таблице. Напротив наименования последнего товара формула выводит сообщение ошибки «#Н/Д» , что свидетельствует об отсутствии данного наименования в старом прайсе.
Вариантов сравнения таблиц в Экселе может быть бесчисленное множество, причем некоторые из них можно провести только при помощи надстройки VBA.
После установки надстройки у вас появится новая вкладка с командой вызова функции. При нажатии на команду Сравнение диапазонов появляется диалоговое окно для ввода параметров.
Данный макрос позволяет сравнить таблицы любого объема и с любым количеством столбцов. Сравнение таблиц можно проводить по одному, двум или трем столбцам одновременно.
Диалоговое окно поделено на две части: левая для первой таблицы и правая для второй.
Чтобы сравнить таблицы необходимо выполнить следующие действия:
- Указать диапазоны таблиц.
- Установить чекбокс (галку/птичку) под выбранным диапазоном таблиц в том случае если таблица включает в себя шапку (строку заголовка).
- Выбрать столбцы левой и правой таблицы, по которым будет вестись сравнение (в случае, если диапазоны таблиц не включают заголовки столбцы будут пронумерованы).
- Указать тип сравнения.
- Выбрать вариант выдачи результатов.
Тип сравнения таблиц
Программа позволяет выбрать несколько типов сравнения таблиц:
Найти строки одной таблицы, которые отсутствуют в другой таблице
При выборе данного типа сравнения программа ищет строки одной таблицы, которые отсутствуют в другой. Если вы сопоставляете таблицы по нескольким столбцам, то результатом работы будут строки, в которых имеется различие хотя бы по одному из столбцов.
Найти совпадающие строки
При выборе данного типа сравнения, программа находит строки, которые совпадают в первой и второй таблицах. Совпадающими считаются строки, в которых значения в выбранных столбцах сравнения (1, 2, 3) одной таблицы полностью совпадают со значениями столбцов второй таблицы.
Пример работы программы в данном режиме представлен справа на картинке.
Сопоставить таблицы на основе выбранной
В данном режиме сравнения, напротив каждой строки первой таблицы (выбранной в качестве основной), копируются данные совпадающей строки второй таблицы. В случае если совпадающие строки отсутствуют, строка напротив основной таблицы остается пустой.
Сравнение таблиц по четырем и более столбцам
Если вам не хватает функционала программы и необходимо сопоставить таблицы по четырем или более столбцам, то вы можете выйти из положения следующим образом:
- Создайте в таблицах по пустому столбцу.
- В новых столбцах используя формулу =СЦЕПИТЬ объедините столбцы, ко которым хотите провести сравнение.
Таким образом, вы получите 1 столбец, содержащий значения нескольких столбцов. Ну а как сопоставить один столбец вы уже знаете.
Say you want to compare versions of a workbook, analyze a workbook for problems or inconsistencies, or see links between workbooks or worksheets. If Microsoft Office 365 or Office Professional Plus 2013 is installed on your computer, the Spreadsheet Inquire add-in is available in Excel.
You can use the commands in the Inquire tab to do all these tasks, and more. The Inquire tab on the Excel ribbon has buttons for the commands described below.
If you don"t see the Inquire tab in the Excel ribbon, see Turn on the Spreadsheet Inquire add-in .
Compare two workbooks
The Compare Files command lets you see the differences, cell by cell, between two workbooks. You need to have two workbooks open in Excel to run this command.
Results are color coded by the kind of content, such as entered values, formulas, named ranges, and formats. There"s even a window that can show VBA code changes line by line. Differences between cells are shown in an easy to read grid layout, like this:
The Compare Files command uses Microsoft Spreadsheet Compare to compare the two files. In Windows 8, you can start Spreadsheet Compare outside of Excel by clicking Spreadsheet Compare on the Apps screen. In Windows 7, click the Windows Start button and then > All Programs > Microsoft Office 2013 > Office 2013 Tools > Spreadsheet Compare 2013 .
To learn more about Spreadsheet Compare and comparing files, read Compare two versions of a workbook .
Analyze a workbook
The Workbook Analysis command creates an interactive report showing detailed information about the workbook and its structure, formulas, cells, ranges, and warnings. The picture here shows a very simple workbook containing two formulas and data connections to an Access database and a text file.
Show workbook links
Workbooks connected to other workbooks through cell references can get confusing. Use the to create an interactive, graphical map of workbook dependencies created by connections (links) between files. The types of links in the diagram can include other workbooks, Access databases, text files, HTML pages, SQL Server databases, and other data sources. In the relationship diagram, you can select elements and find more information about them, and drag connection lines to change the shape of the diagram.
This diagram shows the current workbook on the left and the connections between it and other workbooks and data sources. It also shows additional levels of workbook connections, giving you a picture of the data origins for the workbook.
Show worksheet links
Got lots of worksheets that depend on each other? Use the to create an interactive, graphical map of connections (links) between worksheets both in the same workbook and in other workbooks. This helps give you a clearer picture of how your data might depend on cells in other places.
This diagram shows the relationships between worksheets in four different workbooks, with dependencies between worksheets in the same workbook as well as links between worksheets in different workbooks. When you position your pointer over a node in the diagram, such as the worksheet named "West" in the diagram, a balloon containing information appears.
Show cell relationships
To get a detailed, interactive diagram of all links from a selected cell to cells in other worksheets or even other workbooks, use the Cell Relationship tool. These relationships with other cells can exist in formulas, or references to named ranges. The diagram can cross worksheets and workbooks.
This diagram shows two levels of cell relationships for cell A10 on Sheet5 in Book1.xlsx. This cell is dependent on cell C6 on Sheet 1 in another workbook, Book2.xlsx. This cell is a precedent for several cells on other worksheets in the same file.
To learn more about viewing cell relationships, read See links between cells .
Clean excess cell formatting
Ever open a workbook and find it loads slowly, or has become huge? It might have formatting applied to rows or columns you aren"t aware of. Use the Clean Excess Cell Formatting command to remove excess formatting and greatly reduce file size. This helps you avoid "spreadsheet bloat," which improves Excel"s speed.
Manage passwords
If you"re using the Inquire features to analyze or compare workbooks that are password protected, you"ll need to add the workbook password to your password list so that Inquire can open the saved copy of your workbook. Use the Workbook Passwords command on the Inquire tab to add passwords, which will be saved on your computer. These passwords are encrypted and only accessible by you.
Статья даёт ответы на следующие вопросы:
- Как сравнить две таблицы в 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. Скопируем формулу в нижеследующие ячейки колонки А.
Шаг 4.
Для сравнения таблиц Excel по значениям следует воспользоваться функцией ВПР().
Назначение функции ВПР() - поиск значения в крайнем левом столбце таблицы и возвращение значения ячейки, находящейся в указанном столбце той же строки. Первый параметр - искомое значение. Второй параметр - таблица, в которой будет осуществляться поиск значения. Третий параметр - номер столбца, из ячейки которого в найденной строке будет возвращено значение. Четвертый параметр - тип поиска: ложь - точное совпадение, истина - приближенное совпадение.
Поскольку выходная информация должна быть размещена в первой таблице (именно в нее требовалось добавить имена покупателей), то формулу будем прописывать в ней.
Сформируем в свободной колонке справа от таблицы напротив первой строки данных формулу:
=ВПР(A3;Лист2!$A$3:$F$10;6;ЛОЖЬ)
При копировании формул "умный" Excel автоматически изменяет адресацию ячеек. В нашем случае искомое значение для каждой строки будет меняться: A3,A4 и т.д., а адрес таблицы, в которой ведется поиск, должен оставаться неизменным. Для этого зафиксируем ячейки в параметре адреса таблицы символами "$". Вместо "Лист2!A3:F10" делаем "Лист2!$A$3:$F$10".
Добрый день!
Эта статья посвящена решению такого вопроса, как сравнить две таблицы в 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 и ввести код:
Sub Find_Matches() Dim CompareRange As Variant, x As Variant, y As Variant " Установка переменной CompareRange равной сравниваемому диапазону Set CompareRange = Range("B1:B11") " Если сравниваемый диапазон находится на другом листе или книге, " используйте следующий синтаксис " Set CompareRange = Workbooks("Книга2"). _ " Worksheets("Лист2").Range("B1:B11") " " Сравнение каждого элемента в выделенном диапазоне с каждым элементом " переменной CompareRange For Each x In Selection For Each y In CompareRange If x = y Then x.Offset(0, 2) = x Next y Next x End Sub
Sub Find_Matches () Dim CompareRange As Variant , x As Variant , y As Variant " Установка переменной CompareRange равной сравниваемому диапазону Set CompareRange = Range("B1:B11") " Еслисравниваемыйдиапазоннаходитсянадругомлистеиликниге, " используйте следующий синтаксис " Set CompareRange = Workbooks ("Книга2" ) . _ Горячее сочетание клавиш Alt+F8 . В новом диалоговом окне выбираете ваш макрос Find_similar и выполняете его. Сравнение
с помощью надстройки Inquire
Этот вариант сравнить стал доступен с релизом 2013 версии Excel, редактору добавили надстройку Inquire , которая позволит проанализировать и сравнить два файла Excel. Этот способ хорош, когда у вас есть необходимость сравнить два файла, в случае, когда ваш коллега работал над книгой и ввел некоторые изменения. Вот для определения этих изменений вам необходим инструмент WorkbookCompare в надстройкеInquire. Ну вот мы и рассмотрели 8 способов как сравнить две таблицы в Excel, эти варианты помогут вам решить свои аналитические задачи и упростят вашу работу. Был рад вам помочь!
|
