Как сравнить данные в экселе. Как сравнить два столбца в Excel — методы сравнения данных Excel

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

Если вам нужно сравнить две таблицы Access и найти совпадающие данные, возможны два варианта действий.

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

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

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

В этой статье

Сравнение двух таблиц с помощью объединений

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

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

Подготовка примера данных

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

Access предоставляет несколько способов добавления этих таблиц образец базы данных. Можно ввести данные вручную, скопируйте каждую таблицу в электронную таблицу программы (например, Microsoft Office Excel 2007) и затем импортируйте листы в Access или можно вставлять данные в текстовом редакторе, например Блокнот и затем импортировать данные из результирующего текстовые файлы.

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

Специализации

Код учащегося		Специализация

Учащиеся

Код учащегося		Семестр	Учебный план	Номер предмета	Оценка

Если вы собираетесь вводить пример данных в электронной таблице, можете .

Ввод примеров данных вручную

Если вас не интересует создание листа на основе примера данных, пропустите следующий раздел ("Создание листов с примерами данных").

Создание листов с примерами данных

Создание таблиц базы данных на основе листов

Сравнение образцов таблиц и поиск соответствующих записей с использованием объединений

Теперь все готово для сравнения таблиц "Учащиеся" и "Специализации". Так как связи между двумя таблицами не определены, вам необходимо создать объединения соответствующих полей в запросе. Таблицы содержат по несколько полей, и вам потребуется создать объединение для каждой пары общих полей: "Код учащегося", "Год", а также "Учебный план" (в таблице "Учащиеся") и "Специализация" (в таблице "Специализации"). В данном случае нас интересует только математика, поэтому можно ограничить результаты запроса с помощью условия поля.

На вкладке Создание нажмите кнопку Конструктор запросов .

В диалоговом окне Добавление таблицы дважды щелкните таблицу, которая содержит нужные записи (Учащиеся ), а затем дважды щелкните таблицу, с которой ее сравниваете (Специализации ).

Закройте диалоговое окно Добавление таблицы .

Перетащите поле Код учащегося из таблицы Учащиеся в поле Код учащегося таблицы Специализации . В бланке запроса между двумя таблицами появится линия, которая показывает, что создано объединение. Дважды щелкните линию, чтобы открыть диалоговое окно Параметры объединения .

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

Вам нужно создать еще два объединения. Для этого перетащите поле Год из таблицы Учащиеся в поле Год таблицы Специализации , а затем - поле Учебный план из таблицы Учащиеся в поле Специализация таблицы Специализации .

В таблице Учащиеся дважды щелкните звездочку (* ), чтобы добавить все поля таблицы в бланк запроса.

Примечание: Учащиеся.* .

В таблице Специализации дважды щелкните поле Специализация , чтобы добавить его в бланк.

Показать столбца Специализация .

В строке Условие отбора столбца Специализация введите МАТЕМ .

На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить .

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

Иногда необходимо сравнить таблицы на основе полей с совпадающими значениями, но разными типами данных. Например, поле в одной таблице может иметь числовой тип данных, а сравнить его нужно с полем из другой таблицы, которое имеет текстовый тип данных. Поля, содержащие аналогичные данные разного типа, могут появиться при сохранении чисел в качестве текста (например, при импорте данных из другой программы). Так как создать объединения полей с данными разного типа невозможно, вам потребуется использовать другой способ. Для сравнения двух полей с данными разного типа можно использовать одно поле в качестве условия для другого.

Допустим, вы работаете в университете и хотите узнать, как недавние изменения в учебном плане по математике повлияли на оценки учащихся. В частности, вас интересуют оценки тех студентов, у которых профилирующим предметом является математика. У вас уже есть таблицы "Специализации" и "Учащиеся". Данные об оценках хранятся в таблице "Учащиеся", а данные о профилирующих предметах - в таблице "Специализации". Чтобы увидеть, как изменились оценки у тех, кто специализируется на математике, вам нужно просмотреть записи из таблицы "Учащиеся", соответствующие записям в таблице "Специализации". Однако у одного из полей, которые вы хотите использовать для сравнения таблиц, тип данных не такой, как у поля, с которым оно сопоставляется.

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

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

Изменение типа данных в поле "Код учащегося" таблицы "Специализации"

Откройте базу данных, в которой вы сохранили примеры таблиц.

В области навигации щелкните таблицу "Специализации" правой кнопкой мыши и выберите пункт Конструктор .

Таблица "Специализации" откроется в режиме конструктора.

В столбце Тип данных измените для поля Код учащегося тип данных Число на Текст .

Закройте таблицу "Специализации". Нажмите кнопку Да , когда вам будет предложено сохранить изменения.

Сравнение примеров таблиц и поиск соответствующих записей с использованием условия поля

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

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов .

В диалоговом окне Добавление таблицы дважды щелкните таблицу Учащиеся , а затем таблицу Специализации .

Закройте диалоговое окно Добавление таблицы .

Перетащите поле Год из таблицы Учащиеся в поле Год таблицы Специализации , а затем - поле Учебный план из таблицы Учащиеся в поле Специализация таблицы Специализации . Эти поля содержат данные одного типа, поэтому для их сравнения можно использовать объединения. Для сравнения полей с данными одного типа рекомендуется использовать объединения.

Дважды щелкните звездочку (* ) в таблице Учащиеся , чтобы добавить все поля таблицы в бланк запроса.

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

В таблице Специализации дважды щелкните поле Код учащегося , чтобы добавить его в бланк.

В бланке запроса снимите флажок в строке Показать столбца Код учащегося . В строке Условие отбора столбца Код учащегося введите Like [Учащиеся].[Код учащегося] .

В таблице Специализации дважды щелкните поле Специализация , чтобы добавить его в бланк.

В бланке запроса снимите флажок в строке Показать столбца Специализация . В строке Условие отбора введите МАТЕМ .

На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить .

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

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.

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

Домысливая условия задачи самыми распространенными обстоятельствами, дополнительно установим, что:

1. Разный порядок одних и тех же строк в двух таблицах не делает таблицы различными (в задачах, где порядок строк существенен, всегда можно добавить колонку с номером строки, чтобы заметить их перестановку);
2. В одной таблице не может быть двух одинаковых строк (а если такое есть, то всегда можно произвести свертку по всем колонкам с подсчетом одинаковых строк в добавленной колонке - это упростит интерпретацию результатов сравнения).
3. Таблицы сравниваются путем непосредственного сравнения значений их элементов или ссылок. Если элементы таблиц содержат коллекции, то сравниваются только ссылки на коллекции без попыток определить равенство их содержания.

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

Выделение колонок-измерений позволяет при сравнении таблиц установить не только факт удаления или добавления строки, но и факт изменения строки, если в том же наборе измерений изменились ресурсы.

Например, при сравнении таблиц значений, полученных по оборотно-сальдовой ведомости счета учета сырья и материалов, измерениями будут колонки, содержащие номенклатуру и склад, а ресурсами - остатки и обороты счета. А при сравнении табличных частей «Товары» измерениями будут номенклатура, характеристика и серия, а ресурсами - все остальные реквизиты этой табличной части. И тогда путем сравнения версий табличных частей можно будет сказать, что такая-то номенклатура была удалена или добавлена, а такая-то - изменена.

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

После долгих колебаний было принято следующее решение: результатом сравнения двух таблиц Таблица0 и Таблица1 должна быть таблица «Разница» той же структуры, что и сравниваемые таблицы. «Разница» должна содержать отличающиеся строки двух таблиц (удаленные, добавленные, измененные). При этом в дополнительном столбце «Знак» должна стоять отметка: 0 - если строка имеется в Таблице0 и 1 - если строка имеется в Таблице1. Это можно интерпретировать как 0 - строка удалена, 1 - добавлена, или 0 - строка до изменения, 1 - после. Кроме того (внимание!), строки с одинаковыми значениями измерений должны быть расположены друг под другом, что реализует удобный для визуального контроля способ «связывания» строк до и после изменения.

Например, если сравнить предлагаемым способом таблицу "7 класс" с таблицей "8 класс", то должна получиться таблица "Разница".

7 класс			8 класс			Разница
Предмет	Оценка		Предмет	Оценка		Предмет	Оценка	Знак
Пение	5		Литература	5		Пение	5	0
Литература	5		Алгебра	4		Алгебра	5	0
Алгебра	5		Физика	5		Алгебра	4	1
Физика	5		Химия	4		Химия	4	1

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

2.Критерии оценки и методика испытаний

Главным критерием оценки естественно выбрать время выполнения сравнения. Дополнительным критерием может служить простота функции сравнения. Время выполнения сравнения можно замерить специально созданной для этого обработкой. Простоту функций предлагается оценивать субъективно.

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

3.Краткое описание сравниваемых методов

Всего для детального тестирования было отобрано семь различных методов:

3.1. Свертка и сортировка

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

Функция РазницаТаблицЗначений(Таблица0, Таблица1, Измерения) Экспорт ВсеКолонки = ""; Для Каждого Колонка Из Таблица0.Колонки Цикл ВсеКолонки = ВсеКолонки + ", " + Колонка.Имя КонецЦикла; ВсеКолонки = Сред(ВсеКолонки, 2); Таблица = Таблица1.Скопировать(); Таблица.Колонки.Добавить("Знак", Новый ОписаниеТипов("Число")); Таблица.ЗаполнитьЗначения(1, "Знак"); Для Каждого Строка Из Таблица0 Цикл ЗаполнитьЗначенияСвойств(Таблица.Добавить(), Строка) КонецЦикла; Таблица.Колонки.Добавить("Счёт"); Таблица.ЗаполнитьЗначения(1, "Счёт"); Таблица.Свернуть(ВсеКолонки, "Знак, Счёт"); Ответ = Таблица.Скопировать(Новый Структура("Счёт", 1), ВсеКолонки + ", Знак"); Ответ.Сортировать(Измерения); Возврат Ответ КонецФункции

3.2 Трюк, свертка и сортировка

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

Функция РазницаТаблицЗначений(Таблица0, Таблица1, Измерения) Экспорт ВсеКолонки = ""; Для Каждого Колонка Из Таблица0.Колонки Цикл ВсеКолонки = ВсеКолонки + ", " + Колонка.Имя КонецЦикла; ВсеКолонки = Сред(ВсеКолонки, 2); Таблица = Таблица1.Скопировать(); Таблица.Колонки.Добавить("Знак", Новый ОписаниеТипов("Число")); Таблица.ЗаполнитьЗначения(1, "Знак"); Для ё = 1 По Таблица0.Количество() Цикл Таблица.Вставить(0) КонецЦикла; Для ё = 0 По Таблица0.Колонки.Количество() - 1 Цикл Таблица.ЗагрузитьКолонку(Таблица0.ВыгрузитьКолонку(ё), ё) КонецЦикла; Таблица.Колонки.Добавить("Счёт"); Таблица.ЗаполнитьЗначения(1, "Счёт"); Таблица.Свернуть(ВсеКолонки, "Знак, Счёт"); Ответ = Таблица.Скопировать(Новый Структура("Счёт", 1), ВсеКолонки + ", Знак"); Ответ.Сортировать(Измерения); Возврат Ответ КонецФункции

3.3. Соединение по индексу

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

Функция РазницаТаблицЗначений(Таблица0, Таблица1, Измерения) Экспорт Отбор = Новый Структура(Измерения); Ресурсы = Новый Массив; Для ИндексКолонки = 0 По Таблица0.Колонки.Количество() - 1 Цикл Если НЕ Отбор.Свойство(Таблица0.Колонки[ИндексКолонки].Имя) Тогда Ресурсы.Добавить(ИндексКолонки) КонецЕсли КонецЦикла; Таблица1.Колонки.Добавить("Знак", Новый ОписаниеТипов("Число")); Таблица1.ЗаполнитьЗначения(1, "Знак"); НовыйИндекс = Таблица1.Индексы.Добавить(Измерения); Разница = Таблица1.СкопироватьКолонки(); Для Каждого Строка0 Из Таблица0 Цикл ЗаполнитьЗначенияСвойств(Отбор, Строка0); Строки1 = Таблица1.НайтиСтроки(Отбор); Если Строки1.Количество() = 0 Тогда ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка0) Иначе Строка1 = Строки1; Для Каждого Ресурс Из Ресурсы Цикл Если Строка0[Ресурс] <> Строка1[Ресурс] Тогда ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка0); ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка1); Прервать КонецЕсли КонецЦикла; Строка1.Знак = 0 КонецЕсли КонецЦикла; Для Каждого Строка1 Из Таблица1.НайтиСтроки(Новый Структура("Знак", 1)) Цикл ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка1); КонецЦикла; Таблица1.Колонки.Удалить("Знак"); Таблица1.Индексы.Удалить(НовыйИндекс); Возврат Разница КонецФункции

3.4. Соединение по соответствию

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

Функция РазницаТаблицЗначений_(Таблица0, Таблица1, СтрокаИзмерений) Экспорт Таблица1.Колонки.Добавить("Знак", Новый ОписаниеТипов("Число")); Таблица1.ЗаполнитьЗначения(1, "Знак"); СтруктураИзмерений = Новый Структура(СтрокаИзмерений); Измерения = Новый Массив; Ресурсы = Новый Массив; Для Индекс = 0 По Таблица0.Колонки.Количество() - 1 Цикл ИмяКолонки = Таблица0.Колонки[Индекс].Имя; Если СтруктураИзмерений.Свойство(ИмяКолонки) Тогда Измерения.Добавить(Индекс) Иначе Ресурсы.Добавить(Индекс) КонецЕсли КонецЦикла; ИзмерениеПлюс = Измерения[Измерения.Количество() - 1]; Измерения.Удалить(Измерения.Количество() - 1); ХэшМап = Новый Соответствие; Для Каждого Строка1 Из Таблица1 Цикл Корень = ХэшМап; Для Каждого Измерение Из Измерения Цикл ЧастьКлюча = Строка1[Измерение]; Ветка = Корень[ЧастьКлюча]; Если Ветка = Неопределено Тогда Ветка = Новый Соответствие; Корень[ЧастьКлюча] = Ветка КонецЕсли; Корень = Ветка КонецЦикла; ЧастьКлюча = Строка1[ИзмерениеПлюс]; Корень[ЧастьКлюча] = Строка1 КонецЦикла; Измерения.Добавить(ИзмерениеПлюс); Разница = Таблица1.СкопироватьКолонки(); Для Каждого Строка0 Из Таблица0 Цикл Корень = ХэшМап; Для Каждого Измерение Из Измерения Цикл ЧастьКлюча = Строка0[Измерение]; Ветка = Корень[ЧастьКлюча]; Если Ветка = Неопределено Тогда ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка0); Прервать КонецЕсли; Корень = Ветка КонецЦикла; Если Ветка <> Неопределено Тогда Для Каждого Ресурс Из Ресурсы Цикл Если Строка0[Ресурс] <> Ветка[Ресурс] Тогда ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка0); ЗаполнитьЗначенияСвойств(Разница.Добавить(), Ветка); Прервать КонецЕсли КонецЦикла; Ветка.Знак = 0 КонецЕсли КонецЦикла; Для Каждого Строка1 Из Таблица1.НайтиСтроки(Новый Структура("Знак", 1)) Цикл ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка1); КонецЦикла; Таблица1.Колонки.Удалить("Знак"); Возврат Разница КонецФункции

3.5. Слияние

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

Функция РазницаТаблицЗначений_(Таблица0, Таблица1, СтрокаИзмерений) Экспорт Таблица1.Колонки.Добавить("Знак", Новый ОписаниеТипов("Число")); Таблица1.ЗаполнитьЗначения(1, "Знак"); Разница = Таблица1.СкопироватьКолонки(); СтруктураИзмерений = Новый Структура(СтрокаИзмерений); Измерения = Новый Массив; Ресурсы = Новый Массив; Для Индекс = 0 По Таблица0.Колонки.Количество() - 1 Цикл ИмяКолонки = Таблица0.Колонки[Индекс].Имя; Если СтруктураИзмерений.Свойство(ИмяКолонки) Тогда Измерения.Добавить(Индекс) Иначе Ресурсы.Добавить(Индекс) КонецЕсли КонецЦикла; Сравнение = Новый СравнениеЗначений; Индекс1 = Таблица0.Количество() - 1; Индекс2 = Таблица1.Количество() - 1; Строка1 = Таблица0[Индекс1]; Строка2 = Таблица1[Индекс2]; Пока Истина Цикл Для Каждого Измерение Из Измерения Цикл РезультатСравнения = Сравнение.Сравнить(Строка1[Измерение], Строка2[Измерение]); Если РезультатСравнения <> 0 Тогда Прервать КонецЕсли КонецЦикла; Если РезультатСравнения = 0 Тогда Для Каждого Ресурс Из Ресурсы Цикл Если Строка1[Ресурс] <> Строка2[Ресурс] Тогда ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка1); ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка2); Прервать КонецЕсли КонецЦикла; Индекс1 = Индекс1 - 1; Индекс2 = Индекс2 - 1; Если Мин(Индекс1, Индекс2) < 0 Тогда Прервать КонецЕсли; Строка1 = Таблица0[Индекс1]; Строка2 = Таблица1[Индекс2]; ИначеЕсли РезультатСравнения > 0 Тогда ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка1); Индекс1 = Индекс1 - 1; Если Индекс1 < 0 Тогда Прервать КонецЕсли; Строка1 = Таблица0[Индекс1] Иначе ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка2); Индекс2 = Индекс2 - 1; Если Индекс2 < 0 Тогда Прервать КонецЕсли; Строка2 = Таблица1[Индекс2] КонецЕсли КонецЦикла; Пока Индекс1 >= 0 Цикл Строка1 = Таблица0[Индекс1]; ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка1); Индекс1 = Индекс1 - 1 КонецЦикла; Пока Индекс2 >= 0 Цикл Строка2 = Таблица1[Индекс2]; ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка2); Индекс2 = Индекс2 - 1 КонецЦикла; Таблица1.Колонки.Удалить("Знак"); Возврат Разница КонецФункции

3.6. Запрос - полное соединение

Функция основана на передаче в запрос двух таблиц, где они соединяются по равенству значений в измерениях. Небольшое усложнение связано с последующей "разверткой" в две строки строк, отличающихся по ресурсам.

Функция СтрЧасти(Строка, Разделитель) Экспорт ПозицияРазделителя = Найти(Строка, Разделитель); Если ПозицияРазделителя = 0 Тогда Ответ = Новый Массив; Ответ.Добавить(Строка); Иначе Ответ = СтрЧасти(Сред(Строка, ПозицияРазделителя + СтрДлина(Разделитель)), Разделитель); Ответ.Вставить(0, Сред(Строка, 1, ПозицияРазделителя - 1)) КонецЕсли; Возврат Ответ КонецФункции Функция РазницаТаблицЗначений(Таблица0, Таблица1, Измерения) Экспорт Запрос = Новый Запрос("ВЫБРАТЬ | 0 КАК Знак{}, Т.Поле{} |ПОМЕСТИТЬ Т0 |ИЗ | &Таблица0 КАК Т |; | |//////////////////////////////////////////////////////////////////////////////// |ВЫБРАТЬ | 1 КАК Знак{}, Т.Поле{} |ПОМЕСТИТЬ Т1 |ИЗ | &Таблица1 КАК Т |; | |//////////////////////////////////////////////////////////////////////////////// |ВЫБРАТЬ | 0 КАК Знак |ПОМЕСТИТЬ Знаки | |ОБЪЕДИНИТЬ | |ВЫБРАТЬ | 1 |; | |//////////////////////////////////////////////////////////////////////////////// |ВЫБРАТЬ{} | ВЫБОР Знаки.Знак | КОГДА 0 | ТОГДА Т0.Поле | ИНАЧЕ Т1.Поле | КОНЕЦ КАК Поле,{} | Знаки.Знак |ИЗ | Т0 КАК Т0 | ПОЛНОЕ СОЕДИНЕНИЕ Т1 КАК Т1 | ПО (ИСТИНА) | {} И Т0.Поле = Т1.Поле{}, | Знаки КАК Знаки |ГДЕ | ({}Т0.Поле ЕСТЬ NULL И Знаки.Знак = 1 | ИЛИ Т1.Поле ЕСТЬ NULL И Знаки.Знак = 0 | {} ИЛИ Т0.Поле <> Т1.Поле{}) | |УПОРЯДОЧИТЬ ПО | {}Поле"); СтруктураИзмерений = Новый Структура(Измерения); Секции = СтрЧасти(Запрос.Текст, "{}"); Запрос.Текст = Секции; Для Каждого Колонка Из Таблица1.Колонки Цикл Запрос.Текст = Запрос.Текст + СтрЗаменить(Секции, "Поле", Колонка.Имя) КонецЦикла; Запрос.Текст = Запрос.Текст + Секции; Для Каждого Колонка Из Таблица1.Колонки Цикл Запрос.Текст = Запрос.Текст + СтрЗаменить(Секции, "Поле", Колонка.Имя) КонецЦикла; Запрос.Текст = Запрос.Текст + Секции; Для Каждого Колонка Из Таблица1.Колонки Цикл Запрос.Текст = Запрос.Текст + СтрЗаменить(Секции, "Поле", Колонка.Имя) КонецЦикла; Запрос.Текст = Запрос.Текст + Секции; Для Каждого Элемент Из СтруктураИзмерений Цикл Запрос.Текст = Запрос.Текст + СтрЗаменить(Секции, "Поле", Элемент.Ключ) КонецЦикла; Запрос.Текст = Запрос.Текст + Секции; Запрос.Текст = Запрос.Текст + СтрЗаменить(Секции, "Поле", Таблица1.Колонки.Имя); Для Каждого Колонка Из Таблица1.Колонки Цикл Если НЕ СтруктураИзмерений.Свойство(Колонка.Имя) Тогда Запрос.Текст = Запрос.Текст + СтрЗаменить(Секции, "Поле", Колонка.Имя) КонецЕсли КонецЦикла; Запрос.Текст = Запрос.Текст + Секции; Запрос.Текст = Запрос.Текст + СтрЗаменить(Секции, "Поле", Измерения); Запрос.УстановитьПараметр("Таблица0", Таблица0); Запрос.УстановитьПараметр("Таблица1", Таблица1); Возврат Запрос.Выполнить().Выгрузить() КонецФункции

3.7. Запрос - группировка

Эта функция построена ровно на той же идее, что и функция 3.1, только реализована внутри запроса

Функция РазницаТаблицЗначений(Таблица0, Таблица1, Измерения) Экспорт Запрос = Новый Запрос("ВЫБРАТЬ | 0 КАК Знак, | Т.Поле |ПОМЕСТИТЬ Т0 |ИЗ | &Таблица0 КАК Т |; | |//////////////////////////////////////////////////////////////////////////////// |ВЫБРАТЬ | 1 КАК Знак, | Т.Поле |ПОМЕСТИТЬ Т1 |ИЗ | &Таблица1 КАК Т |; | |//////////////////////////////////////////////////////////////////////////////// |ВЫБРАТЬ | Т.Знак, | Т.Поле |ПОМЕСТИТЬ Т |ИЗ | Т0 КАК Т | |ОБЪЕДИНИТЬ ВСЕ | |ВЫБРАТЬ | Т.Знак, | Т.Поле |ИЗ | Т1 КАК Т |; | |//////////////////////////////////////////////////////////////////////////////// |ВЫБРАТЬ | СУММА(Т.Знак) КАК Знак, | Т.Поле |ИЗ | Т КАК Т | |СГРУППИРОВАТЬ ПО | Т.Поле | |ИМЕЮЩИЕ | КОЛИЧЕСТВО(*) = 1 | |УПОРЯДОЧИТЬ ПО | Поле//"); ВсеКолонки = ""; Для Каждого Колонка Из Таблица1.Колонки Цикл ВсеКолонки = ВсеКолонки + ", Т." + Колонка.Имя КонецЦикла; Запрос.Текст = СтрЗаменить(Запрос.Текст, "Т.Поле", Сред(ВсеКолонки, 2)); Запрос.Текст = СтрЗаменить(Запрос.Текст, "Поле//", Измерения); Запрос.УстановитьПараметр("Таблица0", Таблица0); Запрос.УстановитьПараметр("Таблица1", Таблица1); Возврат Запрос.Выполнить().Выгрузить() КонецФункции

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

4. Результаты тестирования

4.1 Влияние числа строк

Исследуем зависимость времени сравнения от числа строк в таблицах. Для этого используем следующие значения параметров тестирования. Число строк - 20000, 40000, 60000, 80000, 100000, число колонок - 10, число ключевых колонок - 1, тип данных - строка, длина строки - 10, процент удалений, изменений, добавлений - 5, число повторов теста - 2. Получим следующую зависимость, которую удобнее представить в виде графика.

Эта зависимость для большинства методов практически линейна! Так и должно быть. Время работы метода НайтиСтроки при наличии индекса не зависит от числа строк, поэтому соединение по индексу выполняется за линейное время. То же самое при использовании соответствия и слияния. При полном соединении в запросе для соединения таблиц равного размера скорее всего используется хэш-матч.

Нелинейность времени сортировки относительно небольшого количества отличающихся строк чуть-чуть отклоняет от прямой зависимость для свертки. Хуже дела у метода с использованием объединения копированием колонок - именно этот способ копирования вносит существенную нелинейность вдобавок к небольшой нелинейности сортировки. Из-за этого выгода применения "трюка" объединения таблиц на числе строк более 60000 теряется.

4.2 Влияние длины значений

Теперь исследуем зависимость времени от длины значений типа строка. Число строк положим равным 50000. Остальные параметры такие же, как в 4.1. Результат представим в виде столбиковой диаграммы. Она лучше показывает соотношение времени работы разных методов и позволяет выделить лидера, которым в большинстве случаев яыляется метод свертки.

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

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

4.3 Влияние типов данных

Следующий интересный вопрос - отношение методов к типам данных. Его показывает следующая диаграмма. Здесь также число строк 50000, длина строкового и числового значения - 10. Остальное как в 4.1.

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

4.3 Влияние числа колонок

Еще одна зависимость - это зависимость времени сравнения от числа колонок. Ее показывает следующая диаграмма. Число строк здесь 50000, тип данных - строка длины 10, процент добавлений, искажений и удалений по 5. Одна ключевая колонка.

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

4.4 Влияние числа измерений

Более интересна зависимость от числа ключевых колонок, приведенная ниже. Число строк здесь 50000, тип данных - строка длины 10, процент добавлений, искажений и удалений по 5. Всего колонок 10.

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

4.5 Влияние разницы размеров таблиц

Теперь обратим внимание на несимметричность методов 1 - 4 (свертки и соединения) относительно размеров сравниваемых таблиц. Всем этим методам выгоднее, чтобы первая таблица была меньше! Это подтверждает следующая таблица, которая показывает время сравнения двух таблиц 50000 и 40000 строк в разном порядке.

На приведенной диаграмме заметен любопытный артефакт. При данном количестве строк и столбцов оказывается выгоднее добавлять в цикле 50 тысяч строк к таблице из 40 тысяч строк, чем наоборот. Возможно, это связано с особенносями выделения памяти для таблицы значений.

4.6 Влияние количества отличий

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

4.7 Влияние оборудования и программного окружения

Тесты выполнялись на платформе 8.3.5.1248 на ноутбуке VGN-Z51MRG. Полученные зависимости в целом подтверждаются на другом оборудовании, но есть и некоторые особенности, обобщить которые пока не удалось.

5. Выводы

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

5.2 При малом размере (до 50000 строк) можно получить дополнительное ускорение свертки, применив копирование столбцов при объединении таблиц (метод 3.2).

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

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

5.5 Для наибольшей эффективности методов 1-4 нужно выбирать правильный порядок указания таблиц при сравнении.

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

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

5.8 Если в таблицах преобладают числовые данные, даты, средние и длинные строки, то в запросах сравнения таблиц следует использовать группировку, и только для очень коротких строк - полное соединение.

6. Общие выводы

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

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

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

6.4 Время работы метода НайтиСтроки при наличии индекса по колонкам, входящим в отбор, не зависит от размера таблицы значений. Таким образом правильной оценкой быстродействия метода сравнения таблиц с использованием соединения по индексу является O(N).

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

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

Сравнение двух листов в Excel

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

Решить эту непростую задачу нам поможет условное форматирование. Для примера, возьмем данные за февраль и март, как показано на рисунке:

Чтобы найти изменения на зарплатных листах:

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



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

В определенном условии существенное значение имеет функция ПОИСКПОЗ. В ее первом аргументе находится пара значений, которая должна быть найдена на исходном листе следующего месяца, то есть «Март». Просматриваемый диапазон определяется как соединение значений диапазонов, определенных именами, в пары. Таким образом выполняется сравнение строк по двум признакам – фамилия и зарплата. Для найденных совпадений возвращается число, что по сути для Excel является истиной. Поэтому следует использовать функцию =НЕ(), которая позволяет заменить значение ИСТИНА на ЛОЖЬ. Иначе будет применено форматирование для ячеек значение которых совпали. Для каждой не найденной пары значений (то есть – несоответствие) &B2&$C2 в диапазоне Фамилия&Зарплата, функция ПОИСКПОЗ возвращает ошибку. Ошибочное значение не является логическим значением. Поэтому исползаем функцию ЕСЛИОШИБКА, которая присвоит логическое значение для каждой ошибки – ИСТИНА. Это способствует присвоению нового формата только для ячеек без совпадений значений по зарплате в отношении к следующему месяцу – марту.

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

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

Первый список - А2:В31 , этот диапазон называется OldList . Второй список - D2:E31 , диапазон называется NewList . Диапазоны были названы с помощью команды Формулы Определенные имена Присвоить имя . Давать названия диапазонам необязательно, но это облегчает работу с ними.

Начнем с добавления условного форматирования к старому списку.

1. Выделите ячейки диапазона OldList .
2. Выберите .
3. В окне Создание правила форматирования выберите пункт под названием Использовать формулу
4. Введите эту формулу в поле окна (рис. 164.2): =СЧЁТЕСЛИ(NewList;A2)=0 .
5. Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно. Лучше всего выбрать различные цвета заливки.
6. Нажмите кнопку ОК.

Ячейки в диапазоне NewList используют подобную формулу условного форматирования.

1. Выделите ячейки диапазона NewList .
2. Выберите Главная Условное форматирование Создать правило для открытия диалогового окна Создание правила форматирования .
3. В окне Создание правила форматирования выберите пункт Использовать формулу для определения форматируемых ячеек.
4. Введите эту формулу в поле окна: =СЧЕТЕСЛИ(OldList;D2)=0 .
5. Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно (другой цвет заливки).
6. Нажмите кнопку ОК.

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

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