У нас имеется две таблицы заказов, скопированных в один рабочий лист. Необходимо выполнить сравнение данных двух таблиц в Excel и проверить, которые позиции есть первой таблице, но нет во второй. Нет смысла вручную сравнивать значение каждой ячейки.
Как сделать сравнение значений в Excel двух столбцов? Для решения данной задачи рекомендуем использовать условное форматирование, которое быстро выделить цветом позиции, находящиеся только в одном столбце. Рабочий лист с таблицами:
В первую очередь необходимо присвоить имена обоим таблицам. Благодаря этому легче понять, какие сравниваются диапазоны ячеек:
Для второго списка выполните те же действия только имя присвойте – Таблица_2. А диапазон укажите C2:C15 – соответственно.
Полезный совет! Имена диапазонов можно присваивать быстрее с помощью поля имен. Оно находится левее от строки формул. Просто выделяйте диапазоны ячеек, а в поле имен вводите соответствующее имя для диапазона и нажмите Enter.
Теперь воспользуемся условным форматированием, чтобы выполнить сравнение двух списков в Excel. Нам нужно получить следующий результат:
Позиции, которые есть в Таблице_1, но нет в Таблцие_2 будут отображаться зеленым цветом. В тоже время позиции, находящиеся в Таблице_2, но отсутствующие в Таблице_1, будут подсвечены синим цветом.
При определении условий для форматирования ячеек столбцов мы использовали функцию СЧЕТЕСЛИ. В данном примере эта функция проверяет сколько раз встречается значение второго аргумента (например, A2) в списке первого аргумента (например, Таблица_2). Если количество раз = 0 в таком случае формула возвращает значение ИСТИНА. В таком случае ячейке присваивается пользовательский формат, указанный в параметрах условного форматирования.
Ссылка во втором аргументе относительная, значит по очереди будут проверятся все ячейки выделенного диапазона (например, A2:A15). Например, для сравнения двух прайсов в Excel даже на разных листах . Вторая формула действует аналогично. Этот же принцип можно применять для разных подобных задач.
Пожалуй, каждый, кто работает с данными в Excel сталкивается с вопросом как сравнить два столбца в Excel на совпадения и различия. Существует несколько способов как это сделать. Давайте рассмотрим подробней каждый из них.
Сравнивая два столбца с данными часто необходимо сравнивать данные в каждой отдельной строке на совпадения или различия. Сделать такой анализ мы можем с помощью функции . Рассмотрим как это работает на примерах ниже.
Для того, чтобы сравнить данные в каждой строке двух столбцов в Excel напишем простую формулу . Вставлять формулу следует в каждую строку в соседнем столбце, рядом с таблицей, в которой размещены основные данные. Создав формулу для первой строки таблицы, мы сможем ее протянуть/скопировать на остальные строки.
Для того чтобы проверить, содержат ли два столбца одной строки одинаковые данные нам потребуется формула:
=ЕСЛИ(A2=B2; “Совпадают”; “”)
Формула, определяющая различия между данными двух столбцов в одной строке будет выглядеть так:
=ЕСЛИ(A2<>B2; “Не совпадают”; “”)
Мы можем уместить проверку на совпадения и различия между двумя столбцами в одной строке в одной формуле:
=ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”)
=ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”)
Пример результата вычислений может выглядеть так:
Для того чтобы сравнить данные в двух столбцах одной строки с учетом регистра следует использовать формулу:
=ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)
В Excel есть возможность сравнить данные в нескольких столбцах одной строки по следующим критериям:
Представим, что наша таблица состоит из нескольких столбцов с данными. Наша задача найти строки в которых значения совпадают во всех столбцах. В этом нам помогут функции Excel и . Формула для определения совпадений будет следующей:
=ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “)
Если в нашей таблице очень много столбцов, то более просто будет использовать функцию в сочетании с :
=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “)
В формуле в качестве “5” указано число столбцов таблицы, для которой мы создали формулу. Если в вашей таблице столбцов больше или меньше, то это значение должно быть равно количеству столбцов.
Представим, что наша задача выявить из таблицы с данными в несколько столбцов те строки, в которых данные совпадают или повторяются как минимум в двух столбцах. В этом нам помогут функции и . Напишем формулу для таблицы, состоящей из трех столбцов с данными:
=ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “)
В тех случаях, когда в нашей таблице слишком много столбцов – наша формула с функцией будет очень большой, так как в ее параметрах нам нужно указать критерии совпадения между каждым столбцом таблицы. Более простой способ, в этом случае, использовать функцию .
=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)
=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)
Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.
Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.
Когда мы ищем совпадения между двумя столбцами в Excel, нам может потребоваться визуализировать найденные совпадения или различия в данных, например, с помощью выделения цветом. Самый простой способ для выделения цветом совпадений и различий – использовать “Условное форматирование” в Excel. Рассмотрим как это сделать на примерах ниже.
В тех случаях, когда нам требуется найти совпадения в нескольких столбцах, то для этого нам нужно:
Поиск совпадающих ячеек с данными в двух, нескольких столбцах и поиск совпадений целых строк с данными это разные понятия. Обратите внимание на две таблицы ниже:
В таблицах выше размещены одинаковые данные. Их отличие в том, что на примере слева мы искали совпадающие ячейки, а справа мы нашли целые повторяющие строчки с данными.
Рассмотрим как найти совпадающие строки в таблице:
=A2&B2&C2&D2
Во вспомогательной колонке вы увидите объединенные данные таблицы:
Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:
Довольно часто перед пользователями Excel стоит задача сравнения двух таблиц или списков для выявления в них отличий или недостающих элементов. Каждый юзер справляется с этой задачей по своему, но чаще всего на решение указанного вопроса тратится довольно большое количество времени, так как далеко не все подходы к данной проблеме являются рациональными. В то же время, существует несколько проверенных алгоритмов действий, которые позволят сравнить списки или табличные массивы в довольно сжатые сроки с минимальной затратой усилий. Давайте подробно рассмотрим данные варианты.
Существует довольно много способов сравнения табличных областей в Excel, но все их можно разделить на три большие группы:
Именно исходя из этой классификации, прежде всего, подбираются методы сравнения, а также определяются конкретные действия и алгоритмы для выполнения задачи. Например, при проведении сравнения в разных книгах требуется одновременно открыть два файла Excel.
Кроме того, следует сказать, что сравнивать табличные области имеет смысл только тогда, когда они имеют похожую структуру.
Самый простой способ сравнения данных в двух таблицах – это использование простой формулы равенства. Если данные совпадают, то она выдает показатель ИСТИНА, а если нет, то – ЛОЖЬ. Сравнивать можно, как числовые данные, так и текстовые. Недостаток данного способа состоит в том, что ним можно пользоваться только в том случае, если данные в таблице упорядочены или отсортированы одинаково, синхронизированы и имеют равное количество строчек. Давайте посмотрим, как использовать данный способ на практике на примере двух таблиц, размещенных на одном листе.
Итак, имеем две простые таблицы со списками работников предприятия и их окладами. Нужно сравнить списки сотрудников и выявить несоответствия между столбцами, в которых размещены фамилии.
Хотя, конечно, в каждом конкретном случае координаты будут отличаться, но суть останется одинаковой.
Процедуру копирования легче всего выполнить при помощи маркера заполнения. Наводим курсор на правый нижний угол ячейки, где мы получили показатель «ИСТИНА» . При этом он должен преобразоваться в черный крестик. Это и есть маркер заполнения. Жмем левую кнопку мыши и тянем курсор вниз на количество строчек в сравниваемых табличных массивах.
СУММПРОИЗВ(массив1;массив2;…)
Всего в качестве аргументов можно использовать адреса до 255 массивов. Но в нашем случае мы будем использовать всего два массива, к тому же, как один аргумент.
Ставим курсор в поле «Массив1» и выделяем на листе сравниваемый диапазон данных в первой области. После этого в поле ставим знак «не равно» (<> ) и выделяем сравниваемый диапазон второй области. Далее обворачиваем полученное выражение скобками, перед которыми ставим два знака «-» . В нашем случае получилось такое выражение:
--(A2:A7<>D2:D7)
Щелкаем по кнопке «OK» .
Таким же образом можно производить сравнение данных в таблицах, которые расположены на разных листах. Но в этом случае желательно, чтобы строки в них были пронумерованы. В остальном процедура сравнения практически точно такая, как была описана выше, кроме того факта, что при внесении формулы придется переключаться между листами. В нашем случае выражение будет иметь следующий вид:
B2=Лист2!B2
То есть, как видим, перед координатами данных, которые расположены на других листах, отличных от того, где выводится результат сравнения, указывается номер листа и восклицательный знак.
Сравнение можно произвести при помощи инструмента выделения групп ячеек. С его помощью также можно сравнивать только синхронизированные и упорядоченные списки. Кроме того, в этом случае списки должны располагаться рядом друг с другом на одном листе.
Произвести сравнение можно, применив метод условного форматирования. Как и в предыдущем способе, сравниваемые области должны находиться на одном рабочем листе Excel и быть синхронизированными между собой.
Существует ещё один способ применения условного форматирования для выполнения поставленной задачи. Как и предыдущие варианты, он требует расположения обоих сравниваемых областей на одном листе, но в отличие от ранее описанных способов, условие синхронизации или сортировки данных не будет являться обязательным, что выгодно отличает данный вариант от ранее описанных.
При желании можно, наоборот, окрасить несовпадающие элементы, а те показатели, которые совпадают, оставить с заливкой прежним цветом. При этом алгоритм действий практически тот же, но в окне настройки выделения повторяющихся значений в первом поле вместо параметра «Повторяющиеся» следует выбрать параметр «Уникальные» . После этого нажать на кнопку «OK» .
Таким образом, будут выделены именно те показатели, которые не совпадают.
Также сравнить данные можно при помощи сложной формулы, основой которой является функция СЧЁТЕСЛИ . С помощью данного инструмента можно произвести подсчет того, сколько каждый элемент из выбранного столбца второй таблицы повторяется в первой.
Оператор СЧЁТЕСЛИ относится к статистической группе функций. Его задачей является подсчет количества ячеек, значения в которых удовлетворяют заданному условию. Синтаксис данного оператора имеет такой вид:
СЧЁТЕСЛИ(диапазон;критерий)
Аргумент «Диапазон» представляет собой адрес массива, в котором производится подсчет совпадающих значений.
Аргумент «Критерий» задает условие совпадения. В нашем случае он будет представлять собой координаты конкретных ячеек первой табличной области.
Конечно, данное выражение для того, чтобы сравнить табличные показатели, можно применять и в существующем виде, но есть возможность его усовершенствовать.
Сделаем так, чтобы те значения, которые имеются во второй таблице, но отсутствуют в первой, выводились отдельным списком.
После этого переходим к полю «Значение если истина» . Тут мы воспользуемся ещё одной вложенной функцией – СТРОКА . Вписываем слово «СТРОКА» без кавычек, далее открываем скобки и указываем координаты первой ячейки с фамилией во второй таблице, после чего закрываем скобки. Конкретно в нашем случае в поле «Значение если истина» получилось следующее выражение:
СТРОКА(D2)
Теперь оператор СТРОКА будет сообщать функции ЕСЛИ номер строки, в которой расположена конкретная фамилия, и в случае, когда условие, заданное в первом поле, будет выполняться, функция ЕСЛИ будет выводить этот номер в ячейку. Жмем на кнопку «OK» .
В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений» , который мы ранее преобразовали с помощью функции ЕСЛИ . Делаем все ссылки абсолютными.
В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Щелкаем по кнопке «OK» .
Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ . От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.
В поле «Массив» указываем адрес диапазона значений второй таблицы. При этом все координаты делаем абсолютными, то есть, ставим перед ними знак доллара уже ранее описанным нами способом.
Жмем на кнопку «OK» .
При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.
Как видим, существует целый ряд возможностей сравнить таблицы между собой. Какой именно вариант использовать зависит от того, где именно расположены табличные данные относительно друг друга (на одном листе, в разных книгах, на разных листах), а также от того, как именно пользователь желает, чтобы это сравнение выводилось на экран.
Сравним две таблицы имеющих практически одинаковую структуру. Таблицы различаются значениями в отдельных строках, некоторые наименования строк встречаются в одной таблице, но в другой могут отсутствовать.
Пусть на листах Январь и Февраль имеется две таблицы с оборотами за период по соответствующим счетам.
Как видно из рисунков, таблицы различаются:
Если структуры таблиц примерно одинаковы (большинство наименований счетов (строк) совпадают, количество и наименования столбцов совпадают), то можно сравнить две таблицы. Проведем сравнение двумя способами: один проще в реализации, другой нагляднее.
Сначала определим какие строки (наименования счетов) присутствуют в одной таблице, но отсутствуют в другой. Затем, в таблице, в которой меньше строк отсутствует (в наиболее полной таблице), выведем отчет о сравнении, представляющий собой разницу по столбцам (разница оборотов за январь и февраль).
Основным недостатком этого подхода является, то, что отчет о сравнении таблиц не включает строки отсутствующие в наиболее полной таблице. Например, в рассматриваемом нами случае, наиболее полной таблицей является таблица на листе Январь, в которой отсутствует счет 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 (в случае отсутствия строки) или на значение из соответствующего столбца.
С помощью можно выделить расхождения (например, красным цветом).
По аналогии с задачей решенной в статье можно сформировать список наименований счетов, включающий ВСЕ наименования счетов из обоих таблиц (без повторов). Затем вывести разницу по столбцам.
Для этого необходимо:
Пусть имеется две таблицы значений, имеющих одинаковый состав и типы колонок. Требуется сравнить эти таблицы с целью определения различий, имеющихся между ними.
Домысливая условия задачи самыми распространенными обстоятельствами, дополнительно установим, что:
Второе уточнение автоматически приводит к тому, что в таблице всегда будут одна или более колонок, значение (комбинация значений) в которых будут уникальными и могут служить идентификатором строки. Такую колонку (набор колонок) можно называть ключом: простым в случае одной колонки или составным в более сложном случае. А еще лучше, по аналогии с регистрами, упомянутые колонки называть измерениями таблицы, а оставшиеся - ресурсами.
Выделение колонок-измерений позволяет при сравнении таблиц установить не только факт удаления или добавления строки, но и факт изменения строки, если в том же наборе измерений изменились ресурсы.
Например, при сравнении таблиц значений, полученных по оборотно-сальдовой ведомости счета учета сырья и материалов, измерениями будут колонки, содержащие номенклатуру и склад, а ресурсами - остатки и обороты счета. А при сравнении табличных частей «Товары» измерениями будут номенклатура, характеристика и серия, а ресурсами - все остальные реквизиты этой табличной части. И тогда путем сравнения версий табличных частей можно будет сказать, что такая-то номенклатура была удалена или добавлена, а такая-то - изменена.
При постановке задачи также определим форму представления результатов сравнения. Это наиболее уязвимое для критики решение. Поскольку от него зависит результат соревнования методов. Одна форма может быть удобной для одного метода, вторая для другого, третья для третьего, а практика в силу разнообразия задач и ситуаций ответу не помогает.
После долгих колебаний было принято следующее решение: результатом сравнения двух таблиц Таблица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 |
Ну и последнее. Не так часто, но все же встречаются случаи, когда сравнению подвергаются уже упорядоченные по ключевым полям таблицы. Добавим это условие к задаче, чтобы расширить набор тестируемых алгоритмов методом, который специально заточен под этот случай.
Главным критерием оценки естественно выбрать время выполнения сравнения. Дополнительным критерием может служить простота функции сравнения. Время выполнения сравнения можно замерить специально созданной для этого обработкой. Простоту функций предлагается оценивать субъективно.
Обработка, с озданная для испытаний, генерирует таблицу значений с заданным числом строк и столбцов и заданным количеством измерений. Тип данных элементов выбирается из ограниченного списка примитивных типов: строка, число и дата, также можно задать длину значения. Значения элементов таблицы формируются случайным образом. Путем изменения первой таблицы формируется вторая. Количество изменений задается в процентном отношении к числу строк первой таблицы тремя различными показателями: процент удалений, изменений и добавлений. Также задается число повторений для определения среднего времени работы метода. Все тестируемые методы запускаются один за другим на одних и тех же тестовых таблицах. Используемая при тестировании обработка прикреплена к данной публикации, чтобы результаты можно было перепроверить на другом оборудовании и в другом программном окружении.
Всего для детального тестирования было отобрано семь различных методов:
Суть метода заключается в объединении таблиц путем дописывания в цикле по одной строке из первой таблицы ко второй. Затем делается добавление дополнительного столбца "Счёт" для последующего подсчета одинаковых строк. Подсчет делается сверткой по всем столбцам. Так определяются одинаковые и разные строки в первой и второй таблице. Те строки, которые встретились в объединенной таблице по одной, переписываются в таблицу разниц, которая затем сортируется по измерениям, чтобы строки до и после изменений оказались рядом. Вот код данной функции
Функция РазницаТаблицЗначений(Таблица0, Таблица1, Измерения) Экспорт ВсеКолонки = ""; Для Каждого Колонка Из Таблица0.Колонки Цикл ВсеКолонки = ВсеКолонки + ", " + Колонка.Имя КонецЦикла; ВсеКолонки = Сред(ВсеКолонки, 2); Таблица = Таблица1.Скопировать(); Таблица.Колонки.Добавить("Знак", Новый ОписаниеТипов("Число")); Таблица.ЗаполнитьЗначения(1, "Знак"); Для Каждого Строка Из Таблица0 Цикл ЗаполнитьЗначенияСвойств(Таблица.Добавить(), Строка) КонецЦикла; Таблица.Колонки.Добавить("Счёт"); Таблица.ЗаполнитьЗначения(1, "Счёт"); Таблица.Свернуть(ВсеКолонки, "Знак, Счёт"); Ответ = Таблица.Скопировать(Новый Структура("Счёт", 1), ВсеКолонки + ", Знак"); Ответ.Сортировать(Измерения); Возврат Ответ КонецФункции
Данная функция является небольшой модификацией предыдущей функции за счет того, что дописывание первой таблицы ко второй идет не по строкам, а по столбцам. Это в определенном диапазоне условий ускоряет операцию объединения таблиц
Функция РазницаТаблицЗначений(Таблица0, Таблица1, Измерения) Экспорт ВсеКолонки = ""; Для Каждого Колонка Из Таблица0.Колонки Цикл ВсеКолонки = ВсеКолонки + ", " + Колонка.Имя КонецЦикла; ВсеКолонки = Сред(ВсеКолонки, 2); Таблица = Таблица1.Скопировать(); Таблица.Колонки.Добавить("Знак", Новый ОписаниеТипов("Число")); Таблица.ЗаполнитьЗначения(1, "Знак"); Для ё = 1 По Таблица0.Количество() Цикл Таблица.Вставить(0) КонецЦикла; Для ё = 0 По Таблица0.Колонки.Количество() - 1 Цикл Таблица.ЗагрузитьКолонку(Таблица0.ВыгрузитьКолонку(ё), ё) КонецЦикла; Таблица.Колонки.Добавить("Счёт"); Таблица.ЗаполнитьЗначения(1, "Счёт"); Таблица.Свернуть(ВсеКолонки, "Знак, Счёт"); Ответ = Таблица.Скопировать(Новый Структура("Счёт", 1), ВсеКолонки + ", Знак"); Ответ.Сортировать(Измерения); Возврат Ответ КонецФункции
Данная функция построена на простой и ясной идее. В цикле перебираются строки первой таблицы. Для каждой строки делается попытка найти строку во второй таблице, соответствующую ей по значению измерений, с помощью метода "НайтиСтроки". Ресурсы найденных строк затем сравниваются на предмет наличия расхождений, найденная строка во второй таблице помечается нулем, чтобы затем отобрать непомеченные "единичные" строки как отсутствующие в первой таблице. Чтобы метод НайтиСтроки работал быстро, для второй таблицы создается один индекс по всей совокупности измерений.
Функция РазницаТаблицЗначений(Таблица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.Индексы.Удалить(НовыйИндекс); Возврат Разница КонецФункции
Данная функция алгоритмически повторяет предыдущую, за исключением того, что вместо обычного индекса используется "самодельный" индекс на основе соответствия. Для этого вторая таблица предварительно обходится, в результате чего ссылки на ее строки запоминаются в дереве поиска, построенном на основе соответствия
Функция РазницаТаблицЗначений_(Таблица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.Колонки.Удалить("Знак"); Возврат Разница КонецФункции
Эта функция предполагает отсортированность сравниваемых таблиц по ключевым измерениям. В ходе ее работы строки двух таблиц читаются по очереди, сравниваясь между собой так, чтобы в итоге на выходе получалась слитая упорядоченная таблица без одинаковых строк.
Функция РазницаТаблицЗначений_(Таблица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.Колонки.Удалить("Знак"); Возврат Разница КонецФункции
Функция основана на передаче в запрос двух таблиц, где они соединяются по равенству значений в измерениях. Небольшое усложнение связано с последующей "разверткой" в две строки строк, отличающихся по ресурсам.
Функция СтрЧасти(Строка, Разделитель) Экспорт ПозицияРазделителя = Найти(Строка, Разделитель); Если ПозицияРазделителя = 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.1, только реализована внутри запроса
Функция РазницаТаблицЗначений(Таблица0, Таблица1, Измерения) Экспорт Запрос = Новый Запрос("ВЫБРАТЬ | 0 КАК Знак, | Т.Поле |ПОМЕСТИТЬ Т0 |ИЗ | &Таблица0 КАК Т |; | |//////////////////////////////////////////////////////////////////////////////// |ВЫБРАТЬ | 1 КАК Знак, | Т.Поле |ПОМЕСТИТЬ Т1 |ИЗ | &Таблица1 КАК Т |; | |//////////////////////////////////////////////////////////////////////////////// |ВЫБРАТЬ | Т.Знак, | Т.Поле |ПОМЕСТИТЬ Т |ИЗ | Т0 КАК Т | |ОБЪЕДИНИТЬ ВСЕ | |ВЫБРАТЬ | Т.Знак, | Т.Поле |ИЗ | Т1 КАК Т |; | |//////////////////////////////////////////////////////////////////////////////// |ВЫБРАТЬ | СУММА(Т.Знак) КАК Знак, | Т.Поле |ИЗ | Т КАК Т | |СГРУППИРОВАТЬ ПО | Т.Поле | |ИМЕЮЩИЕ | КОЛИЧЕСТВО(*) = 1 | |УПОРЯДОЧИТЬ ПО | Поле//"); ВсеКолонки = ""; Для Каждого Колонка Из Таблица1.Колонки Цикл ВсеКолонки = ВсеКолонки + ", Т." + Колонка.Имя КонецЦикла; Запрос.Текст = СтрЗаменить(Запрос.Текст, "Т.Поле", Сред(ВсеКолонки, 2)); Запрос.Текст = СтрЗаменить(Запрос.Текст, "Поле//", Измерения); Запрос.УстановитьПараметр("Таблица0", Таблица0); Запрос.УстановитьПараметр("Таблица1", Таблица1); Возврат Запрос.Выполнить().Выгрузить() КонецФункции
Все приведенные здесь функции были достаточно тщательно оттюнингованы для достижения максимального быстродействия. С учетом опыта, полученного в ходе совместного тюнинга на форуме функций для одномерного случая. Однако это было сделано не так тщательно как там, поэтому, возможно, из некоторых функций можно выжать еще немного быстродействия.
Исследуем зависимость времени сравнения от числа строк в таблицах. Для этого используем следующие значения параметров тестирования. Число строк - 20000, 40000, 60000, 80000, 100000, число колонок - 10, число ключевых колонок - 1, тип данных - строка, длина строки - 10, процент удалений, изменений, добавлений - 5, число повторов теста - 2. Получим следующую зависимость, которую удобнее представить в виде графика.
Эта зависимость для большинства методов практически линейна! Так и должно быть. Время работы метода НайтиСтроки при наличии индекса не зависит от числа строк, поэтому соединение по индексу выполняется за линейное время. То же самое при использовании соответствия и слияния. При полном соединении в запросе для соединения таблиц равного размера скорее всего используется хэш-матч.
Нелинейность времени сортировки относительно небольшого количества отличающихся строк чуть-чуть отклоняет от прямой зависимость для свертки. Хуже дела у метода с использованием объединения копированием колонок - именно этот способ копирования вносит существенную нелинейность вдобавок к небольшой нелинейности сортировки. Из-за этого выгода применения "трюка" объединения таблиц на числе строк более 60000 теряется.
Теперь исследуем зависимость времени от длины значений типа строка. Число строк положим равным 50000. Остальные параметры такие же, как в 4.1. Результат представим в виде столбиковой диаграммы. Она лучше показывает соотношение времени работы разных методов и позволяет выделить лидера, которым в большинстве случаев яыляется метод свертки.
Видно, что зависимость времени от метода при изменении длины строки практически не меняется. Растет только время выполнения запросов.
Чтобы повысить информативность этой диаграммы в отношении запросных методов, здесь выделено в отдельные измерения время ввода таблиц в запрос. Для этого создана функция-пустышка, выполняющая только ввод таблиц в запрос и не выполняющая больше никакой другой работы. Большое время на ввод таблиц показывает, что запросной технике очень трудно конкурировать с методами-лидерами. Во многих случаях лидеры уже закончили работу к тому времени, когда исходные данные только оказались в запросе.
Следующий интересный вопрос - отношение методов к типам данных. Его показывает следующая диаграмма. Здесь также число строк 50000, длина строкового и числового значения - 10. Остальное как в 4.1.
Из нее видно, что типданных сильнее всего сказывается на времени запросных методов. Для чисел лучше подходит группировка. И очень хорошо запросами обрабатываются даты.
Еще одна зависимость - это зависимость времени сравнения от числа колонок. Ее показывает следующая диаграмма. Число строк здесь 50000, тип данных - строка длины 10, процент добавлений, искажений и удалений по 5. Одна ключевая колонка.
Видно, что число колонок не сильно меняет сравнительную скорость методов. В наибольшей степени увеличение числа колонок замедляет работу запросов.
Более интересна зависимость от числа ключевых колонок, приведенная ниже. Число строк здесь 50000, тип данных - строка длины 10, процент добавлений, искажений и удалений по 5. Всего колонок 10.
Видно, что метод на основе соответствия, ранее показывавший неплохие результаты, теперь оказывается в аутсайдерах. Также ухудшается слияние. А вот поиск по индексу улучшается - за счет того, что сравнивать остается меньшее число колонок.
Теперь обратим внимание на несимметричность методов 1 - 4 (свертки и соединения) относительно размеров сравниваемых таблиц. Всем этим методам выгоднее, чтобы первая таблица была меньше! Это подтверждает следующая таблица, которая показывает время сравнения двух таблиц 50000 и 40000 строк в разном порядке.
На приведенной диаграмме заметен любопытный артефакт. При данном количестве строк и столбцов оказывается выгоднее добавлять в цикле 50 тысяч строк к таблице из 40 тысяч строк, чем наоборот. Возможно, это связано с особенносями выделения памяти для таблицы значений.
Ну и, наконец, исследуем зависимость времени сравнения от степени отличия таблиц. Видно, что при увеличении процента расхождений время работы свертки замедляетс. Так как начинает играть роль нелинейность сортировки.
Тесты выполнялись на платформе 8.3.5.1248 на ноутбуке VGN-Z51MRG. Полученные зависимости в целом подтверждаются на другом оборудовании, но есть и некоторые особенности, обобщить которые пока не удалось.
5.1. Самый простой метод свертки оказывается в большинстве случаев и наиболее производительным. Его и нужно использовать как универсальный метод, но не в специальных случаях.
5.2 При малом размере (до 50000 строк) можно получить дополнительное ускорение свертки, применив копирование столбцов при объединении таблиц (метод 3.2).
5.3 В специальном случае одной ключевой колонки, значительного количества различий и существенной разницы размеров таблиц следует использовать соединение по соответствию. Так же следует поступать, даже если ключевых таблиц несколько, но сравнение производится с одной и той же таблицей, для которой можно заранее подготовить "дерево решений" на основе соответствия, настроенное на ее особенности.
5.4 В специальном случае нескольких ключевых колонок при значительном количестве различий и не отсортированности сравниваемых таблиц нужно использовать метод соединения по индексу.
5.5 Для наибольшей эффективности методов 1-4 нужно выбирать правильный порядок указания таблиц при сравнении.
5.6 В специальном случае отсортированности сравниваемых таблиц при значительном количестве различий следует использовать слияние.
5.7. В специальном случае больших (зависит от оборудования) и примерно равных по размеру таблиц, которые, к тому же, имеют значительные отличия и состоят из коротких строк и предельно малого числа колонок, возможно использовать запросы.
5.8 Если в таблицах преобладают числовые данные, даты, средние и длинные строки, то в запросах сравнения таблиц следует использовать группировку, и только для очень коротких строк - полное соединение.
6.1 В любом случае перед решающим выбором лучше по-возможности сравнивать несколько методов в реальных условиях их применения. Например, при помощи приложенной к статье обработки.
6.2 Учет особенностей данных в таблицах позволяет произвести целенаправленную дополнительную оптимизацию большинства приведенных методов. Для этого остается немало возможностей, оставшихся за пределами рассмотренного круга вопросов.
6.3 Ввод таблиц значений в запросы может занимать значительное время, что в большинстве случаев сводит на нет эффективность их применения в задачах, когда данные берутся из памяти, а не из базы. Бездумное использование запросов в этой задаче - вредное заблуждение.
6.4 Время работы метода НайтиСтроки при наличии индекса по колонкам, входящим в отбор, не зависит от размера таблицы значений. Таким образом правильной оценкой быстродействия метода сравнения таблиц с использованием соединения по индексу является O(N).
, которые предложили, реализовали и отладили свои методы для одномерного случая, вносили множество полезных поправок и соображений, а также активно участвовали во всех обсуждениях. Отдельное спасибо спонсорам той самой ветки и - за интересный вопрос.
