Как сравнить два прайса и два списка в Excel
Два списка в Excel сравнивают по общему ключу, обычно по артикулу или коду. Для каждой строки первого списка формула ВПР или связка ИНДЕКС и ПОИСКПОЗ ищет такую же строку во втором списке и возвращает из неё нужное значение, например цену. Если пары нет, формула выдаёт ошибку #Н/Д, и по ней видно строки, которых во втором списке нет. Способ надёжен, пока ключ записан в обоих списках одинаково до символа.
Подготовка: ключ и два листа
Положите списки на два листа одной книги, например «Новый» и «Старый». В обоих должен быть столбец-ключ: артикул, код или наименование. Формулы ниже считают, что ключ стоит в столбце A, наименование в B, цена в C.
Выберите ключ, который у списков общий. Артикул производителя подходит лучше всего. Внутренний код поставщика годится, только если оба прайса от одного поставщика. Наименование как ключ работает хуже всего: достаточно одного лишнего пробела, и пара потеряется.
Работайте в копии файлов. Вспомогательные столбцы и сортировку придётся переделывать не раз. Исходные прайсы пригодятся для сверки.
Какие строки есть в обоих списках
В столбце D листа «Новый» напишите:
=ЕСЛИ(ЕНД(ПОИСКПОЗ(A2;Старый!$A:$A;0));"нет в старом";"есть")
ПОИСКПОЗ с нулём в последнем аргументе ищет точное совпадение. ЕНД превращает ошибку #Н/Д в понятную метку. Протяните формулу вниз и отфильтруйте столбец. Строки «нет в старом» это новые позиции прайса. Та же формула на листе «Старый» со ссылкой на лист «Новый» покажет позиции, которые из прайса пропали.
Как сравнить цены в двух прайсах
Подтяните старую цену к каждой строке нового прайса. В столбце E:
=ЕСЛИОШИБКА(ВПР(A2;Старый!$A:$C;3;ЛОЖЬ);"")
Цифра 3 означает третий столбец диапазона, то есть цену. ЛОЖЬ включает точный поиск. ЕСЛИОШИБКА оставляет ячейку пустой, если строки в старом прайсе нет.
Дальше в столбце F посчитайте разницу =C2-E2, а в G изменение в процентах =ЕСЛИ(E2="";"";C2/E2-1). Задайте G процентный формат и отсортируйте по убыванию. Наверху окажутся позиции, которые подорожали сильнее всего.
Та же выборка через ИНДЕКС и ПОИСКПОЗ выглядит так:
=ИНДЕКС(Старый!$C:$C;ПОИСКПОЗ(A2;Старый!$A:$A;0))
Связка не зависит от номера столбца и умеет брать значение из столбца левее ключа. В новых версиях Excel есть ПРОСМОТРX, которая делает то же одной функцией и сразу подставляет текст вместо ошибки: =ПРОСМОТРX(A2;Старый!$A:$A;Старый!$C:$C;"нет"). Последний аргумент задаёт, что вывести, если пары нет. Названия функций приведены по русской версии Excel, в вашей версии набор функций может отличаться.
Почему ВПР не находит значение, хотя оно есть
Строки выглядят одинаково, а формула выдаёт #Н/Д или чужую цену. Сначала проверьте простое: напишите в свободной ячейке =A2=Старый!A15 для двух строк, которые должны совпасть. Если результат ЛОЖЬ, значит, они отличаются хотя бы одним символом. Причины обычно такие.
| Причина | Как заметить | Что сделать |
|---|---|---|
| Четвёртый аргумент пропущен или равен ИСТИНА | Ошибки нет, но цена чужая | Всегда ставьте ЛОЖЬ или 0 |
| Число в одном списке, текст в другом | Артикул 12345 прижат то к правому, то к левому краю ячейки | Приведите оба столбца к тексту, например формулой =A2&"" |
| Пробел в конце или двойной пробел | ДЛСТР показывает больше символов, чем видно | Функция СЖПРОБЕЛЫ во вспомогательном столбце |
| Неразрывный пробел из выгрузки или с сайта | СЖПРОБЕЛЫ не помогла | =ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ") |
| Латинская буква среди русских | КОДСИМВ даёт разные коды у внешне одинаковых букв | Замена через ПОДСТАВИТЬ, кроме настоящей латиницы вроде 2RS |
| Разные знаки в размерах | «3х2,5» и «3x2.5», дефис и длинное тире | Свести знаки к одному виду до сравнения |
| Ключ стоит не в первом столбце диапазона | #Н/Д на всех строках | ИНДЕКС и ПОИСКПОЗ вместо ВПР |
| Диапазон съехал при протягивании | Верхние строки находятся, нижние нет | Закрепите диапазон знаками $ |
Регистр букв на результат не влияет. Для ВПР и ПОИСКПОЗ «ПОДШИПНИК» и «подшипник» одинаковы. Если регистр всё-таки важен, сравнивайте функцией СОВПАД.
Ещё одна ловушка: ключ повторяется во втором списке. ВПР вернёт первую найденную строку и промолчит про остальные. Перед сравнением посчитайте повторы ключа функцией СЧЁТЕСЛИ и разберите строки, где их больше одного.
Где точное совпадение заканчивается
Все формулы выше отвечают на один вопрос: одинаковы ли символы ключа. Пока поставщик пишет артикул так же, как вы, этого хватает. Трудности начинаются со строк, где ключ записан иначе.
- Другое обозначение того же товара. «Подш. 180205» и «Подшипник 6205-2RS» означают один подшипник по старой и новой системе обозначений. Общих символов для формулы у них почти нет.
- Похожая запись разных товаров. Поиск по части строки с «*» найдёт «Задвижка 30с41нж Ду80» и у Ру16, и у Ру25. Формула возьмёт ту, что выше в списке.
- Артикул с разными разделителями. «6205-2RS», «6205 2RS» и «62052RS» для формулы три разных ключа. Убрать дефисы и пробелы можно, но тогда легко склеить артикулы, которые различаются именно ими.
- Разная фасовка. Крепёж в одном прайсе указан поштучно, в другом упаковками. Ключ совпал, но цены относятся к разному количеству товара, и сравнивать их напрямую нельзя. Сначала пересчитайте одну из цен на единицу измерения второго прайса.
Очистка пробелов и латиницы добавляет находок, но такие строки она не сведёт. Как приводить список к одному виду перед сравнением, разобрано в статье о дублях с разным написанием. Задачу шире, как найти одну позицию в разных списках, описывает статья про матчинг товаров.
Строки, которых нет во втором списке
После формул список делится на три части. Строки с точной парой можно брать в работу. Строки «нет в старом» могут оказаться новыми позициями или старыми под другим обозначением. Строки с повторами ключа требуют решения человека.
Вторую и третью часть сверяют уже по смыслу: по типу товара и ключевым характеристикам, с учётом единицы измерения. Пошагово это описано в статье как сопоставить прайс с каталогом.
Предполагаю, что у вас таких строк набираются сотни на каждый прайс. Так ли? Тогда сверку можно отдать нам. Мы проверяем каждую строку по смыслу и ключевым характеристикам и возвращаем тот же Excel: найденные пары с уверенностью, спорные строки отдельно с причиной. Подробнее на странице про сверку прайсов поставщиков с вашим справочником. Начать можно с бесплатной пробы на ваших строках.
Вопросы про сравнение прайсов
- Как подсветить строки, которых нет во втором списке?
- Выделите столбец ключей и создайте правило условного форматирования с формулой =ЕНД(ПОИСКПОЗ(A2;Старый!$A:$A;0)). Строки без пары закрасятся. Путь к правилам в вашей версии Excel может отличаться.
- Что выбрать: ВПР или ИНДЕКС с ПОИСКПОЗ?
- Для разовой сверки хватит ВПР с ЛОЖЬ в конце. Для таблицы, в которую будут вставлять столбцы, надёжнее ИНДЕКС с ПОИСКПОЗ: у неё нет номера столбца, который сбивается при вставке.
- Почему ВПР вернула цену соседнего товара?
- Почти всегда виноват пропущенный четвёртый аргумент. Без него ВПР ищет приблизительно и на несортированном списке берёт случайную близкую строку. Второй вариант: ключ повторяется, и формула взяла первое вхождение.
- Как сравнить прайсы, если у поставщика нет артикулов?
- Формулами по наименованию найдутся только точные совпадения. Остальное придётся сверять по типу товара и характеристикам вручную или отдать на построчную проверку.
Сверим два ваших прайса по смыслу
Пришлите 5–10 тыс строк справочника номенклатуры в Excel или CSV, можно обезличенных. В течение дня вернём файл с группами дублей, единым наименованием, классом, уверенностью и статусом. Бесплатно.
Прислать строки на пробу