Article #34470

Сравнение таблиц — довольно распространенное действие в Excel. Например, у нас есть старый прайс-лист и новая версия. Вам необходимо просмотреть цены на какие товары и на сколько изменились.

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

Итак, вот наши необработанные данные.

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

Для этого мы воспользуемся несколькими способами.

1. Использование функции ВПР для каждого продукта в прайс-листе № 2, мы будем искать цену в первом прайс-листе и отображать ее рядом с новым. Это облегчит выявление различий.
2. Давайте построим сводную таблицу на основе данных из обоих прайс-листов и увидим там эти различия.
3. Мы используем стандартную операцию сравнения.
4. Применяем формулу массива.

1. Используем ВПР, чтобы сравнить две таблицы.

Создадим диапазон B4: C19 и назовем его «цена1». Это упростит нам обращение к исходным данным.

Давайте добавим еще один столбец к новым данным и назовем его «Старая цена». Для каждой позиции в прайс-листе нет. 2 найдем соответствующую цену в n. 1.

В 4 вставляем формулу

= ВПР (F4; цена1; 2; 0)

и скопируйте его по столбцу.

Мы видим, что цена тут и там изменилась, и в четырех именах формула ВПР вернула ошибку # N / A. Это означает, что раньше этих товаров не было в наличии и цены на них найти не удалось.

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

Для этого используйте функцию ESLIOSHIBKA и отобразите ноль вместо # N / A.

Изменим нашу формулу:

= SEERROR (ВПР (F4; цена1,2,0); 0)

Теперь мы можем рассчитать отклонение новой цены от старой.

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

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

Формула в J4:

= ЕСЛИ (SEERROR (VLOOKUP (F4; price1,2,0); 0) = G4; «»; SEERROR (VLOOKUP (F4; price1,2; 0); 0))

Звучит сложно и громоздко, но на самом деле все просто. Основа такая же, как и раньше: посмотрите в первой таблице «старую» цену каждого товара из новых данных.

То есть ключевым выражением является ЕСЛИОШИБКА (ВПР (F4; цена1; 2; 0); 0).

Если найденное значение равно «новой» цене из ячейки G4, выведите пробел “”.

С этим результатом связаны значения соседних ячеек.

Если ячейка J4 пуста, в другом месте ничего не отображается:

Формула в I4:

= ЕСЛИ (J4 «»; F4;»»)

в K4:

= ЕСЛИ (J4 «»; G4;»»)

в L4:

= ЕСЛИ (J4 «»; K4-J4;»»)

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

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

То есть сравнивать придется по двум направлениям.

Согласитесь, не всегда хочется выполнять двойную работу.

2. Сравнение при помощи сводной таблицы.

Поскольку структура сравниваемых данных одинакова, мы можем их комбинировать. Чтобы различать, откуда берутся значения, добавьте еще один столбец и укажите там источник данных — price1 или price2.

Используя наш предыдущий пример, это можно сделать следующим образом:

Теперь через меню Insert-PivotTable создайте сводку, которую вы можете на том же листе для наглядности.

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

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

Это еще один пример того, как ВПР во многих случаях имеет довольно приличные альтернативы.

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

Также следует отметить, что сводные таблицы могут работать с большими объемами данных намного быстрее, чем VLOOKUP.

3. Стандартное сравнение.

Это самый простой и элементарный способ сравнить два столбца Excel на предмет совпадений. Таким образом можно работать как с числовыми, так и с текстовыми значениями.

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

Например, сравним две цены, отметив условие совпадения цен в столбце I

= G4 = C4

В случае ничьей мы получим ответ «ИСТИНА», а при отсутствии совпадений — «ЛОЖЬ». Скопируйте из I4 в столбец.

Этот способ сравнения таблиц является наиболее простым, поэтому мы не будем вдаваться в подробности.

4. Использование формул массива вместе с ВПР.

Здесь все намного сложнее. Вернемся к нашим исходным данным и разместим списки товаров и цен на двух листах рабочей книги: «Цена1» и «Цена2».

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

Назовем их «цена_1» и «цена_2» соответственно. Это упростит понимание формул.

Мы также вынесем результаты сравнения таблиц на отдельный лист «Сравнение».

В ячейке A5 запишите формулу

= ЕСЛИОШИБКА (ЕСЛИОШИБКА (ИНДЕКС (цена_1; ПОИСК (0; СЧЁТЕСЛИ (A $ 4: $ A4; цена_1); 0))), ИНДЕКС (цена_2; ПОИСК (0; СЧЁТЕСЛИ (A $ 4: $ A4; 0)));»»)

Поскольку это формула массива, не забудьте завершить ее комбинацией клавиш Ctrl + Shift + Enter.

В результате мы получим список уникальных (неповторяющихся) значений из всех имеющихся у нас наименований продуктов.

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

Функция ПОИСКПОЗ вычисляет номер позиции этого уникального значения и передает его в функцию ИНДЕКС, которая, в свою очередь, через номер позиции извлекает значение из массива и записывает его в ячейку.

Поскольку это формула массива, мы постоянно просматриваем весь список от начала до конца, повторяя все эти операции.

Если первая таблица завершена, возникает ошибка. ЕСЛИОШИБКА реагирует на это и таким же образом начинает перебирать значения второй таблицы. Когда ошибка возникает и там, возвращается пустая строка “”.

Скопируйте эту формулу столбец за столбцом. Список уникальных ценностей готов.

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

Пишем в B5:

= ЕСЛИ (UND (ВПР (A5; цена_1; 1; 0)); «Нет»; «Да»)

Не забывайте, что это тоже формула массива (Ctrl + Shift + Enter).

вы можете выделить расхождения цветом для наглядности, используя условное форматирование.

Помните, что для этого вам нужно использовать Главное меню — Условное форматирование — Правила выбора ячеек — Текст содержит…

Что ж, если значение существует в таблице, имеет смысл отобразить его в таблице сравнения.

Давайте заменим значение «Да» в нашей формуле на функцию ВПР:

ВПР (A5, Price1! $ B $ 5: $ C $ 20, 2, 0)

В результате наша формула преобразуется в вид:

= ЕСЛИ (UND (ВПР (A5; цена_1; 1; 0)); «Нет»; ВПР (A5; Цена1! $ B $ 5: $ C $ 20, 2, 0))

Аналогично в C5 :

= ЕСЛИ (UND (ВПР (A5; цена_2; 1; 0)); «Нет»; ВПР (A5; Цена2! $ B $ 5: $ C $ 23, 2, 0))

Напомним, что наши сравнительные таблицы можно найти в таблицах Price1 и Price2.

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