Excel для ритейла, часть I: поиск по ключу, сводные таблицы и проверка данных
Первая часть трека «Excel для ритейла» учит трём приёмам, на которых держится большинство отчётов байера, планировщика и аналитика: связать две таблицы по ключу (ВПР, ИНДЕКС и ПОИСКПОЗ, ПРОСМОТРX), свести тысячи строк сводной таблицей и проверить, что данные не сломаны. Документ показывает формулы на условной выгрузке, порядок действий и контрольные проверки, чтобы итог сводной совпадал с исходной таблицей; формулы записаны для русского интерфейса Excel с разделителем «;».
Зачем этот документ
Большую часть времени аналитик тратит не на анализ, а на то, чтобы собрать данные из разных выгрузок и убедиться, что они сходятся. Один сбитый артикул или число, записанное текстом, превращает отчёт в красивую ошибку, а решение о закупке принимается по ней. Документ даёт минимум приёмов, после которых таблица становится надёжной: ключ, поиск, сводная, контроль. Читают его байер, планировщик, аналитик и коммерческий директор бренда, который сводит продажи по каналам.
Коротко
- Что это за документ и зачем он нужен?
- Первая часть трека «Excel для ритейла» учит трём приёмам, на которых держится большинство отчётов байера, планировщика и аналитика: связать две таблицы по ключу (ВПР, ИНДЕКС и ПОИСКПОЗ, ПРОСМОТРX), свести тысячи строк сводной таблицей и проверить, что данные не сломаны. Документ показывает формулы на условной выгрузке, порядок действий и контрольные проверки, чтобы итог сводной совпадал с исходной таблицей; формулы записаны для русского интерфейса Excel с разделителем «;».
- Кто отвечает за документ и как часто его пересчитывать?
- Ведёт аналитик или планировщик; файл работает как рабочая книга: исходные выгрузки на отдельных листах, расчёты на своих, контрольный лист с проверками; передаётся байеру и руководителю вместе с контролем.
- Какие ошибки встречаются чаще всего?
- Бренд: Искать артикулы без очистки: пробел в конце или число-текст дают #Н/Д, и строка выпадает из отчёта без предупреждения. Бренд: Использовать ВПР без нуля в последнем аргументе: поиск выдаёт ближайшее значение, а не точное, и цена подтягивается чужая. Бренд: Прятать ошибки ЕСЛИОШИБКА и не считать их число: данные теряются, а отчёт выглядит чистым.
Для кого
- Бренд
- Бренду нужно свести продажи по каналам (опт, сайт, площадка), склад и производство: ключ — артикул модели с размером и цветом, а источников больше, чем у магазина.
- Ритейл
- Ритейлеру нужно свести продажи по магазинам и категориям с остатками по складам и справочником соответствий артикулов бренда и штрихкодов магазина; данных больше, а ключи разные у разных поставщиков.
Из чего состоит
Подготовка данных: плоская таблица и ключ
- Плоская таблица: один заголовок в первой строке, одна запись на строку, без объединённых ячеек, пустых строк и итогов внутри; такая таблица принимается сводной и функциями поиска.
- Умная таблица (Вставка → Таблица): диапазон растёт сам, формулы заполняются вниз, а в формулах можно писать имена столбцов вместо адресов.
- Бренд: Ключ — артикул модели, при необходимости с размером и цветом через дефис; одно правило записи для всех листов, иначе поиск не найдёт строку.
- Ритейл: Ключ — штрихкод или SKU магазина; артикул бренда лежит в справочнике соответствий, а не вписан руками в продажи.
- Очистка: СЖПРОБЕЛЫ убирает лишние пробелы, ПЕЧСИМВ непечатаемые символы, ЗНАЧЕН превращает текст в число; очищенные значения кладут в соседний столбец, исходный не трогают.
Поиск по ключу
- ВПР(ключ; таблица; номер столбца; 0): ищет ключ в первом столбце таблицы и возвращает значение из указанного столбца; последний аргумент 0 означает точное совпадение, без него поиск даёт случайный ответ.
- ИНДЕКС(столбец результата; ПОИСКПОЗ(ключ; столбец ключей; 0)): ищет влево и не ломается при вставке столбцов; надёжнее ВПР в рабочих книгах, которые меняют.
- ПРОСМОТРX(ключ; столбец ключей; столбец результата; «нет»): современная замена обоих, есть в Excel 365 и новых версиях; в старых версиях её нет, проверьте перед передачей файла.
- ЕСЛИОШИБКА(формула; «нет данных»): прячет ошибку #Н/Д, но не должна скрывать проблему: число таких строк считают отдельно в контроле.
- СУММЕСЛИМН(сумма; диапазон1; условие1; диапазон2; условие2): суммирует продажи по нескольким условиям, например категории и месяцу; СЧЁТЕСЛИ считает строки по условию.
- Бренд: Поиск подтягивает цену и себестоимость модели из справочника к продажам канала, чтобы считать маржу по строкам.
- Ритейл: Поиск подтягивает остаток склада и статус позиции к продажам магазина, чтобы увидеть, где нужен дозаказ.
Сводные таблицы
- Вставка → Сводная таблица: строки — категория или бренд, столбцы — месяц или неделя, значения — сумма продаж и сумма остатка; формул нет, итоги считает сама таблица.
- Группировка дат по месяцам, срезы для фильтра по бренду или магазину, значения как долю от итога: три приёма, которых хватает для большинства отчётов.
- После правки исходных данных сводную обновляют (Данные → Обновить всё); таблица не пересчитывается сама, и устаревшая сводная — частая причина расхождений.
- Бренд: Сводная по каналам и месяцам показывает, где падает выручка и растут возвраты; по ней строят данные для медиаплана.
- Ритейл: Сводная по магазинам и категориям показывает, где продажи и запас расходятся; от неё идёт список позиций для перемещения и дозаказа.
Проверка данных и контроль
- Данные → Проверка данных: список допустимых значений для категории и канала, число не меньше нуля для остатка, дата в допустимом диапазоне; сообщение об ошибке объясняет, что вводить.
- Проверка ввода не ловит то, что вставлено готовым: выгрузки проверяют формулами, например СЧЁТЕСЛИ(A:A; A2) > 1 для дублей и условным форматированием.
- Контрольный лист: число строк в исходной и в сводной, сумма продаж в обеих, число ключей без совпадения, число ошибок; во всех проверках разность должна быть нулём.
- Бренд: Контроль включает сверку с данными площадки: итог по каналу в таблице совпадает с отчётом площадки, расхождение объясняют до использования.
- Ритейл: Контроль включает сверку с кассой и учётной системой: итог по магазину совпадает с кассовым итогом за день.
Откуда берутся данные
| Источник данных | Поля |
|---|---|
| Выгрузки из учётных систем и площадок | Продажи по артикулам, остатки по складам, цены, возвраты; даты и ключи в одном формате. |
| Справочник артикулов | Ключ, категория, бренд, цена, себестоимость, статус, штрихкоды и соответствия. |
| Правила проверки | Допустимые значения, пороги, число строк и контрольные суммы источника. |
Как собрать по шагам
- Шаг 1: Бренд: выпишите все источники (учёт, склад, сайт, площадки) и определите общий ключ: артикул модели, при необходимости с размером.
- Шаг 2: Ритейл: определите ключ магазина (штрихкод или SKU) и создайте справочник соответствий с артикулами брендов.
- Шаг 3: Приведите выгрузки к плоским таблицам: уберите объединения и итоги, оформите как умные таблицы, добавьте столбец очищенного ключа.
- Шаг 4: Посчитайте число строк и итоги исходных таблиц и запишите на контрольном листе: это эталон для всех последующих проверок.
- Шаг 5: Подтяните данные справочника поиском (ИНДЕКС и ПОИСКПОЗ или ПРОСМОТРX); посчитайте число ключей без совпадения и разберите каждый.
- Шаг 6: Постройте сводную таблицу по нужным полям и проверьте, что её итог равен итогу исходной таблицы с точностью до нуля.
- Шаг 7: Добавьте проверку данных на листах ввода (списки, числа не меньше нуля, даты) и условное форматирование дублей.
- Шаг 8: Посчитайте показатель по строкам, например покрытие в неделях, и добавьте статус по порогам; пороги вынесите в отдельные ячейки, а не в формулу.
- Шаг 9: Защитите листы с формулами, оставьте ввод в отдельных ячейках и сохраните контрольный лист с датой выгрузки; следующий пересчёт начните с проверки.
Формулы
| Показатель | Формулы | Пояснение |
|---|---|---|
| Покрытие в неделях | =ЕСЛИОШИБКА(остаток / продажи в неделю; «нет продаж») | Делить на ноль нельзя: нулевые продажи дают отдельный статус. |
| Статус по порогам | =ЕСЛИ(покрытие < нижний; «дозаказ»; ЕСЛИ(покрытие > верхний; «избыток»; «норма»)) | Пороги (здесь условные 4 и 16 недель) лежат в ячейках: их задают по категории и сроку поставки. |
| Сумма по двум условиям | =СУММЕСЛИМН(продажи; категория; «Платья»; месяц; «март») | Критерии даны ссылками на ячейки, чтобы не править формулу. |
| Контрольная разность | =СУММ(исходные продажи) − сумма в сводной таблице | Должна быть нулём; любое иное значение — повод остановиться. |
Формулы в записи
- Покрытие
K = Q / S
Q — остаток в штуках; S — продажи в неделю; K — покрытие в неделях; при S = 0 покрытие не определено.
Пример с числами
Сверка артикулов: продажи, остаток, покрытие и статус, условная выгрузка
| Артикул | Продажи в неделю, шт. | Остаток, шт. | Покрытие, нед. | Статус |
|---|---|---|---|---|
| D-101 | 42 | 300 | 7,1 | норма |
| D-102 | 15 | 420 | 28 | избыток |
| T-210 | 60 | 180 | 3 | дозаказ |
| T-211 | 8 | 20 | 2,5 | дозаказ |
| S-305 | 25 | 125 | 5 | норма |
| S-306 | 0 | 90 | — | нет продаж |
Покрытие равно остатку, делённому на продажи недели; пороги условные: меньше 4 недель — дозаказ, больше 16 — избыток. Из шести артикулов один без продаж (покрытие не определено, остаток 90 штук лежит), дозаказ нужен 2 артикулам, избыток у 1. Все числа условные.
Рисунки
Путь данных от выгрузки до отчёта. Контроль стоит на каждом шаге, а не в конце: ошибка, найденная после сводной, обходится дороже.
- Выгрузка → Очистка и ключ
- Очистка и ключ → Поиск по справочнику; Контрольный лист
- Поиск по справочнику → Сводная таблица; Контрольный лист
- Сводная таблица → Контрольный лист
- Контрольный лист → Отчёт и решение
- Отчёт и решение
Данные таблицей
| Показатель | Строк |
|---|---|
| Пробелы в артикулах | 38 |
| Число записано текстом | 21 |
| Дубли артикула | 12 |
| Пустые ключи | 9 |
| Разное написание | 17 |
Ошибки в условной выгрузке на 1 000 строк, число строк. Строк с ошибками 97 из 1 000: чаще всего мешают пробелы и текстовые числа; все они ловятся очисткой и проверкой до поиска. Число строк условное: это пример структуры ошибок, а не измеренная доля.
Особенности по категориям
- Одежда
- В одежде ключ часто составной (модель, размер, цвет), поэтому первым шагом делают единый столбец ключа и проверяют дубли. Сводные строят по моделям и размерам, а покрытие считают по модели, а не по размеру, иначе редкие размеры дают ложный избыток.
- Обувь
- В обуви ключ включает размер, а размерный ряд даёт много строк: сводная по размерам показывает дыры в ряду. Покрытие считают по паре модель-размер и по модели в целом; контроль данных важен из-за размеров, записанных текстом («38,5» против числа).
- Аксессуары
- В аксессуарах мало размеров, но много цветов и материалов, и ключ часто строится по цвету; строк на модель меньше, а оборачиваемость выше. Пороги покрытия для аксессуаров иные, и их задают в ячейках, а не в формуле.
- Детское
- В детском размеры идут по росту, и ключ включает возрастную группу; сезон сдвигается из-за роста ребёнка. Контроль данных проверяет, что размер по росту и размер по возрасту не перепутаны, а покрытие считают по возрастным группам.
- Мужское
- В мужском сегменте больше базовых позиций с долгой жизнью: продаж в неделю стабильнее, и покрытие в неделях читается надёжнее. Поиск идёт по артикулу, а пороги для базовых позиций выше, чем для модных.
- Женское
- В женском сегменте больше модных позиций и коротких сезонов: продажи в неделю скачут, и по таблице считают скользящее среднее, а не одну неделю. Пороги покрытия для модных позиций ниже, а сводная по категориям и срезы по сезону нужны каждую неделю.
Типичные ошибки
- Бренд: Искать артикулы без очистки: пробел в конце или число-текст дают #Н/Д, и строка выпадает из отчёта без предупреждения.
- Бренд: Использовать ВПР без нуля в последнем аргументе: поиск выдаёт ближайшее значение, а не точное, и цена подтягивается чужая.
- Бренд: Прятать ошибки ЕСЛИОШИБКА и не считать их число: данные теряются, а отчёт выглядит чистым.
- Ритейл: Не обновить сводную после новой выгрузки: цифры в отчёте старые, а решение принимают по свежим на вид.
- Ритейл: Объединять ячейки и вставлять итоги внутри таблицы: сводная и поиск такую таблицу не читают.
- Ритейл: Не сверять итог с кассой или учётной системой: расхождение копится, пока его не найдут на закрытии периода.
Особенности России
- Бренд: Артикулы и штрихкоды в вашей учётной системе и в маркировке должны совпадать: расхождение ключа между таблицей и системой маркировки ломается на этапе отгрузки; правила маркировки проверьте по группе товара.
- Ритейл: Данные кассы подпадают под требования Закона № 54-ФЗ о контрольно-кассовой технике; выгрузки для Excel берут из учётной системы, а не правят кассовые данные руками. Норма: закон 54-ФЗ от 22.05.2003: Закон от 22.05.2003 № 54-ФЗ «О применении контрольно-кассовой техники».
- Бренд: Таблицы с персональными данными клиентов (имена, телефоны) в Excel хранят с ограничением доступа; требования закона № 152-ФЗ о персональных данных применяются и к рабочим файлам. Норма: закон 152-ФЗ от 27.07.2006: Закон от 27.07.2006 № 152-ФЗ «О персональных данных».
- Ритейл: Формулы записаны для русского интерфейса Excel (разделитель «;», запятая как десятичный знак); в английском интерфейсе разделитель «,», имена функций английские, названия и доступность ПРОСМОТРX проверьте в своей версии.