Статья даёт ответы на следующие вопросы:
- Как сравнить две таблицы в Excel?
- Как сравнивать сложные таблицы в Excel?
- Как производить сравнение таблиц в Excel с использованием функции ВПР()?
- Как формировать уникальные идентификаторы строк, если их уникальность изначально определяется набором значений в нескольких столбцах?
- Как фиксировать значения ячеек в формулах при копировании формул?
Проблемы начинаются, когда требуется выполнить задачу сравнения данных срочно, а привлечение программиста и написание им запроса или программного отчета по времени может превысить установленные для решения задачи сроки. Еще одной не менее распространенной проблемой является потребность сравнения информации из различных источников. В таком случае постановка задачи для программиста будет звучать как интеграция двух систем. Решение такой задачи потребует более высокой квалификации программиста а также займет больше времени, чем разработка в единой системе.
Для решения обозначенных проблем идеальным приемом является использование для сравнения данных табличного редактора Microsoft Excel. Большинство распространенных систем управленческого и регламентированного учета поддерживают выгрузку в формат Excel. Эта задача потребует всего лишь определенной квалификации пользователя по работе с этим офисным пакетом и не потребует навыков программирования.
Рассмотрим решение задачи сравнения таблиц в Excel на примере. Мы имеем две таблицы, содержащие списки квартир. Источники выгрузки — 1С Предприятие (учёт строительства) и таблица в Excel (учёт продаж). Таблицы размещены в рабочей книге Excel на первом и втором листах соответственно.
Для сравнения двух таблиц Excel нам нужно добиться того, чтобы в обеих таблицах каждая строка идентифицировалась бы одним полем, а не четырьмя. Получить такое поле можно объединив значения четырех полей адреса функцией Сцепить(). Назначение функции Сцепить() – объединение нескольких текстовых значений в одну строку. Значения в функции перечисляются через символ «;». В качестве значений могут выступать как адреса ячеек, так и произвольный текст , заданный в кавычках.
Шаг 1. Вставим в начале первой таблицы пустую колонку «A» и пропишем в ячейке этой колонки напротив первой строки с данными формулу: =СЦЕПИТЬ(B3;"-";C3;"-";D3;"-";E3) Для удобства визуального восприятия между значениями объединяемых ячеек мы установили символы «-».
Шаг 2. Скопируем формулу в нижеследующие ячейки колонки А.
Шаг 3. Далее выполним те же операции (шаг 1 и шаг 2) для таблицы 2 на втором листе рабочей книги. Теперь можно переходить к сравнению двух таблиц по значениям колонки «A».
Шаг 4. Для сравнения таблиц Excel по значениям следует воспользоваться функцией ВПР(). Поскольку выходная информация должна быть размещена в первой таблице (именно в нее требовалось добавить имена покупателей), то формулу будем прописывать в ней. Сформируем в свободной колонке справа от таблицы напротив первой строки данных формулу:=ВПР(A3;Лист2!$A$3:$F$10;6;ЛОЖЬ) При копировании формул «умный» Excel автоматически изменяет адресацию ячеек. В нашем случае искомое значение для каждой строки будет меняться: A3,A4 и т.д., а адрес таблицы, в которой ведется поиск, должен оставаться неизменным. Для этого зафиксируем ячейки в параметре адреса таблицы символами «$». Вместо «Лист2!A3:F10» делаем «Лист2!$A$3:$F$10».
Шаг 5. Скопируем формулу в нижеследующие ячейки результирующей колонки.
Результат решения задачи:
