Макрос для Excel, который обновляет цены и остатки по прайс-листам поставщиков. Понимает кривые артикулы, работает с двумя ключами, не ломает твою скидку.
' Три кнопки — весь процесс
Sub AutoLoadData()
' Загружает выгрузку сайта и прайс поставщика
' Автоматически создаёт копию файла
End Sub
Sub NormalizeAndComparePrices()
' ВПР по двум артикулам
' Нормализация: А→A, пробелы, дефисы
' Учитывает суффиксы, не трогает скидки
End Sub
Sub ExportUpdatedData()
' Сохраняет в копию файла выгрузки
' Готово к загрузке на сайт
End SubЯ администрировал интернет-магазин и каждый день — обновлял цены по прайсам. Делаешь эту формулу в Excel, протягиваешь, двигаешь-копируешь-вставляешь. Потом оказывается, что у этого поставщика пробелы в артикулах — надо убирать, а здесь наши контентщики накосячили и поставили русскую "А" вместо английской. И так без конца.
В конце концов решил, что проще разобраться в VBA и сделать один раз хороший инструмент и закрыть проблемы навсегда.
Сначала написал простой макрос. Потом добавил нормализацию артикулов. Потом — второй артикул для обработки. Потом решил, что настройки можно вынести из кода VBA в лист, чтобы можно было легко что-то подправить. А потом понял, что этот инструмент может пригодится кому-то еще. Так появился этот сайт.
Сейчас этот макрос доступен тебе. Бесплатно, до 5000 строк. Если нужно больше или моих базовых настроек не хватает для твоих задач — пиши, я буду рад помочь.
Не просто ВПР, а полноценное решение для обновления цен
Сначала ищет по основному артикулу. Если не нашёл — по второму (штрих-коду, например). Двойная подстраховка от ошибок.
Убирает лишние пробелы, заменяет русские буквы на английские (А→A, В→B, С→C), приводит к верхнему регистру. Настраивается.
Добавь суффикс к артикулу — и макрос не будет трогать склад или цену у этих позиций. Твоя наценка останется.
Поддерживает формат "новая цена / старая цена". Если у поставщика есть старая цена — макрос перенесёт её. Нет — оставит твою.
При загрузке данных макрос создаёт копию файла выгрузки. Все изменения сохраняются в неё — оригинал не трогается.
Показывает, сколько цен изменилось, сколько не нашлось, есть ли сильные скачки (>20%). Пишет логи по брендам.
Почему стандартный ВПР не справляется
AB-12345 — у тебя на сайте
АВ-12345 — в прайсе поставщика (русские А и В!)
AB-12345 — после нормализации совпадут
Это самая частая ошибка. Визуально идентично, для Excel — разные. Макрос заменяет все русские аналоги на английские: А→A, В→B, С→C, Е→E и т.д.
FR.4521.12 — товар А, цена 5000₽
FR-4521-12 — товар Б, цена 8500₽
Встречал у одного бренда: артикулы идентичны по цифрам, но разница в одном символе — и это разные товары с разными ценами. Поэтому нормализацию можно настраивать точечно.
abc-123 — у тебя
ABC-123 — у поставщика
Технический нюанс: стандартная функция ВПР нечувствительна к регистру. Но в VBA сравнение строк по умолчанию чувствительное — «abc» и «ABC» разные. Макрос приводит всё к верхнему регистру, если тебе это подходит.
"12345 " — пробел в конце
"12 345" — пробел в середине
"12345 " — неразрывный пробел (Alt+0160)
Поставщики любят копировать из 1С или других систем — и тащат за собой «мусор». Макрос вычищает все невидимые символы перед сравнением.
Три кнопки, два файла, одна настройка
Выбираешь два файла: сначала выгрузку с сайта, потом прайс поставщика. Макрос автоматически:
Макрос запускает ВПР по двум артикулам с нормализацией:
Макрос переносит обновлённые данные в файл-копию:
Когда много прайсов от разных поставщиков, удобнее создать в каждом файле отдельный лист и скопировать туда столбцами артикул, цену, склад и т.д. — именно то, что обновляешь. Так меньше кликов при выборе листа.
P.S. В мире пока не существует системы, которая автоматически понимает, где в прайсе поставщика нужные данные. Поставщики слишком креативные.
Укажи, в каких столбцах лежат данные
| Параметр | Пример значения | Описание |
|---|---|---|
Имя листа Выгрузки | brands_santehnica | Название листа в рабочем файле для данных сайта |
Имя листа Поставщика | Прайс | Название листа для данных поставщика |
Р-С_Артикул1 | A | Столбец с основным артикулом сайта |
Р-С_Артикул2 | B | Столбец со вторым артикулом (штрих-код и т.п.) |
Р-С_Цена новая | C | Столбец с текущей ценой на сайте |
Р-С_Цена старая | D | Столбец со старой (зачёркнутой) ценой |
Р-С_Склад | E | Столбец с остатками |
Р-П_Артикул1 | A | Столбец с артикулом в прайсе поставщика |
Р-П_Цена новая | B | Столбец с ценой поставщика |
И-С_Имя листа в файле | Sheet1 | Название листа в файле выгрузки с сайта |
И-П_Столбец Артикул основной | 1 | Номер столбца в файле прайса поставщика |
В разделе «ЗАМЕНА И УДАЛЕНИЕ СИМВОЛОВ» можно настроить, какие символы удалять или заменять:
"." → "" ' удалить точки
"-" → "" ' удалить дефисы
" " → "" ' удалить пробелыВ разделе «ДОПОЛНИТЕЛЬНЫЕ ФУНКЦИИ»:
Замена русских букв на английские — Да/НетПреобразовать в верхний регистр — Да/НетСуффиксы позволяют защитить некоторые товары от обновления. Добавь суффикс к артикулу на своём сайте — и макрос будет знать, что делать:
| Тип | Поведение макроса |
|---|---|
| 1 | Не обновляет склад (цена обновляется) |
| 2 | Не обновляет ни склад, ни цену |
Пример: артикул ABC-123_wh с типом 1 — цена обновится, склад останется твоим.
Это не онлайн-сервис. Макрос работает только на твоём компьютере, в твоём Excel. Никакие данные не уходят в интернет.
Код VBA защищён паролем по нескольким причинам:
Ты можешь убедиться в безопасности файла самостоятельно. Вот что можно сделать:
Перед открытием файла проверь его антивирусом:
Можешь убедиться, что макрос не лезет в интернет:
Windows блокирует файлы, скачанные из интернета. Это стандартная защита. Чтобы разрешить выполнение:
Это безопасно — ты просто разрешаешь Excel запустить этот конкретный файл.
Открой файл в Excel и посмотри листы:
Если у тебя остались сомнения — напиши мне. Могу предоставить открытый код для аудита (под NDA или просто так — обсудим). Также можешь найти знакомого, кто разбирается в VBA, и попросить его проверить код после снятия пароля.
Прозрачность — лучший способ доверия. Я не прячусь и открыт к диалогу.
Ещё один макрос в комплекте — для контент-менеджеров
Сравнивает прайс поставщика с товарами на твоём сайте и находит позиции, которых у тебя ещё нет.
' До: прайс поставщика (500 строк)
Артикул | Название | Цена
A-001 | Смеситель | 5000
A-002 | Кран | 3000
A-003 | Раковина NEW | 8000 ' ← новинка!
B-100 | Унитаз | 12000
' После: только новинки
Артикул | Название | Цена
A-003 | Раковина NEW | 8000Удобно, когда поставщик присылает «всё подряд», а тебе нужно добавить только то, чего ещё нет.
Напиши — отвечу на всё, что связано с автоматизацией Excel
Даже если макрос просто помог тебе в работе — напиши. Буду рад отзыву, знакомству или просто обсуждению твоих задач по обработке данных.