Ищем изменения: три способа сравнить две таблицы

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

ВЫПУСК №3 (МАРТ, 2019)

 

Есть три способа выполнить подобного рода задачи, и далее детальнее рассмотрим их на аналогичных таблицах.

К примеру, есть два прайса на продукцию: старый и новый. Наша задача — выяснить, по каким товарным позициям изменилась цена и на сколько процентов.

Ищем изменения: три способа сравнить две таблицы

Рис. 1. Пример двух прайсов на продукцию

 

Способ 1

Самый простой и часто применимый способ — с помощью формул.

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

1. «Искомое значение» из первой таблицы должно присутствовать во второй таблице, иначе формула выдаст ошибку #Н/Д — нет данных.

2. Столбец с «искомым значением» в первой таблице может находиться в любом месте таблицы, а вот во второй таблице — обязательно левее от столбца с возвращаемыми значениями.

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

4. Аргумент «таблица» нужно не забывать закреплять абсолютными ссылками с помощью клавиши F4 для предотвращения сползания ссылки на диапазон при копировании формулы.

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

 

Перед тем как приступить к применению формулы, необходимо определить для себя, какой из прайсов будет основной таблицей (куда подтягиваем данные), какой — вспомогательной таблицей (откуда вытягиваем цену). Например, подтягивать будет к новым ценам старые. Подготовим две колонки: Старая цена (колонка К) и Новый vs Старый ( колонка L). Ключевым полем для поиска будут значения колонки Код

 

В ячейке К8 вводим формулу:

=ЕСЛИ(H8="";"";ВПР(H8;$B:$D;3;0)), где

 

Формула ЕСЛИ — логическая формула проверки выполнения условия. Она введена здесь для того, чтобы напротив строк, в которых нет цен и соответственно кода исключить вывод ошибок.

 

Аргументы формулы ЕСЛИ несут следующий смысл:

H8="" — аргумент «логическое выражение». Сравниваем ячейку с кодом с пустым значением.

"" — аргумент «значение если истина». Если в ячейке с кодом все же пусто (нет кода товара), то выводим пусто.

 

ВПР(H8;$B:$D;3;0) — аргумент «значение если ложь» записан формулой ВПР. То есть если в ячейке с кодом не пусто, то по коду подтягиваем значение цены из старого прайса.

 

Теперь детальнее по аргументам формулы ВПР:

H8 — «искомое значение». Ключевое значение выделенное в основной таблице. Тут код товара.

 

$B:$D — аргумент «таблица». Выделен диапазон в старом прайсе, начиная с колонки, в которой содержаться коды товаров, по колонку, из которой нужно получить значение цены. Знак $ позволяет осуществить закрепление диапазона.

 

3 — номер столбца. Это порядковый номер столбца в старом прайсе относительно колонки с кодами товара.

0 — аргумент «интервальный просмотр» — необязательный аргумент. Указывается как 0/ЛОЖЬ или 1/ИСТИНА. Логическое значение, которое определяет, какое соответствие должна искать функция — точное или приблизительное. Если этот аргумент имеет значение ИСТИНА или опущен, возвращается приблизительное соответствие; при отсутствии точного соответствия возвращается наибольшее из значений, меньших, чем «искомое_значение». Если этот аргумент имеет значение ЛОЖЬ, функция ищет точное соответствие.

 

В ячейке L8 введем формулу для расчета прироста новой цены относительно старой. Для исключения ошибок в ячейках, которые не содержат цены (в тех строках, где прописано названия брендов) также дополним формулой ЕСЛИ по аналогии с примером выше:

=ЕСЛИ(H8="";"";J8/K8-1)

J8/K8-1 — формула прироста цены, где

J8 — новая цена

K8 — старая цена

 

Вычитаем 1 для того, чтобы вывести отличие прироста от 100 %.

Так как в новом прайсе могли произойти и другие изменения, кроме цены (изменения в ассортименте: вывод из линейки устаревших продуктов, добавление новых позиций), то в результате расчетов могут возникнуть ошибки #Н/Д — нет данных. Для нашего случая такая ошибка говорит о том, что в новом прайсе добавились коды товаров, которых нет в старом прайсе (рис. 2, в 14-15 строках).

 

Приобрести полную версию статьи