Syntha Lab EN

Excel для ритейла, часть I: поиск по ключу, сводные таблицы и проверка данных

Первая часть трека «Excel для ритейла» учит трём приёмам, на которых держится большинство отчётов байера, планировщика и аналитика: связать две таблицы по ключу (ВПР, ИНДЕКС и ПОИСКПОЗ, ПРОСМОТРX), свести тысячи строк сводной таблицей и проверить, что данные не сломаны. Документ показывает формулы на условной выгрузке, порядок действий и контрольные проверки, чтобы итог сводной совпадал с исходной таблицей; формулы записаны для русского интерфейса Excel с разделителем «;».

Производство, логистика, каналы Бренд и ритейл Проверено: 2026-11-15

Открыть интерактивно →

Зачем этот документ

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

Коротко

Что это за документ и зачем он нужен?
Первая часть трека «Excel для ритейла» учит трём приёмам, на которых держится большинство отчётов байера, планировщика и аналитика: связать две таблицы по ключу (ВПР, ИНДЕКС и ПОИСКПОЗ, ПРОСМОТРX), свести тысячи строк сводной таблицей и проверить, что данные не сломаны. Документ показывает формулы на условной выгрузке, порядок действий и контрольные проверки, чтобы итог сводной совпадал с исходной таблицей; формулы записаны для русского интерфейса Excel с разделителем «;».
Кто отвечает за документ и как часто его пересчитывать?
Ведёт аналитик или планировщик; файл работает как рабочая книга: исходные выгрузки на отдельных листах, расчёты на своих, контрольный лист с проверками; передаётся байеру и руководителю вместе с контролем.
Какие ошибки встречаются чаще всего?
Бренд: Искать артикулы без очистки: пробел в конце или число-текст дают #Н/Д, и строка выпадает из отчёта без предупреждения. Бренд: Использовать ВПР без нуля в последнем аргументе: поиск выдаёт ближайшее значение, а не точное, и цена подтягивается чужая. Бренд: Прятать ошибки ЕСЛИОШИБКА и не считать их число: данные теряются, а отчёт выглядит чистым.

Для кого

Бренд
Бренду нужно свести продажи по каналам (опт, сайт, площадка), склад и производство: ключ — артикул модели с размером и цветом, а источников больше, чем у магазина.
Ритейл
Ритейлеру нужно свести продажи по магазинам и категориям с остатками по складам и справочником соответствий артикулов бренда и штрихкодов магазина; данных больше, а ключи разные у разных поставщиков.

Из чего состоит

Подготовка данных: плоская таблица и ключ

  • Плоская таблица: один заголовок в первой строке, одна запись на строку, без объединённых ячеек, пустых строк и итогов внутри; такая таблица принимается сводной и функциями поиска.
  • Умная таблица (Вставка → Таблица): диапазон растёт сам, формулы заполняются вниз, а в формулах можно писать имена столбцов вместо адресов.
  • Бренд: Ключ — артикул модели, при необходимости с размером и цветом через дефис; одно правило записи для всех листов, иначе поиск не найдёт строку.
  • Ритейл: Ключ — штрихкод или SKU магазина; артикул бренда лежит в справочнике соответствий, а не вписан руками в продажи.
  • Очистка: СЖПРОБЕЛЫ убирает лишние пробелы, ПЕЧСИМВ непечатаемые символы, ЗНАЧЕН превращает текст в число; очищенные значения кладут в соседний столбец, исходный не трогают.

Поиск по ключу

  • ВПР(ключ; таблица; номер столбца; 0): ищет ключ в первом столбце таблицы и возвращает значение из указанного столбца; последний аргумент 0 означает точное совпадение, без него поиск даёт случайный ответ.
  • ИНДЕКС(столбец результата; ПОИСКПОЗ(ключ; столбец ключей; 0)): ищет влево и не ломается при вставке столбцов; надёжнее ВПР в рабочих книгах, которые меняют.
  • ПРОСМОТРX(ключ; столбец ключей; столбец результата; «нет»): современная замена обоих, есть в Excel 365 и новых версиях; в старых версиях её нет, проверьте перед передачей файла.
  • ЕСЛИОШИБКА(формула; «нет данных»): прячет ошибку #Н/Д, но не должна скрывать проблему: число таких строк считают отдельно в контроле.
  • СУММЕСЛИМН(сумма; диапазон1; условие1; диапазон2; условие2): суммирует продажи по нескольким условиям, например категории и месяцу; СЧЁТЕСЛИ считает строки по условию.
  • Бренд: Поиск подтягивает цену и себестоимость модели из справочника к продажам канала, чтобы считать маржу по строкам.
  • Ритейл: Поиск подтягивает остаток склада и статус позиции к продажам магазина, чтобы увидеть, где нужен дозаказ.

Сводные таблицы

  • Вставка → Сводная таблица: строки — категория или бренд, столбцы — месяц или неделя, значения — сумма продаж и сумма остатка; формул нет, итоги считает сама таблица.
  • Группировка дат по месяцам, срезы для фильтра по бренду или магазину, значения как долю от итога: три приёма, которых хватает для большинства отчётов.
  • После правки исходных данных сводную обновляют (Данные → Обновить всё); таблица не пересчитывается сама, и устаревшая сводная — частая причина расхождений.
  • Бренд: Сводная по каналам и месяцам показывает, где падает выручка и растут возвраты; по ней строят данные для медиаплана.
  • Ритейл: Сводная по магазинам и категориям показывает, где продажи и запас расходятся; от неё идёт список позиций для перемещения и дозаказа.

Проверка данных и контроль

  • Данные → Проверка данных: список допустимых значений для категории и канала, число не меньше нуля для остатка, дата в допустимом диапазоне; сообщение об ошибке объясняет, что вводить.
  • Проверка ввода не ловит то, что вставлено готовым: выгрузки проверяют формулами, например СЧЁТЕСЛИ(A:A; A2) > 1 для дублей и условным форматированием.
  • Контрольный лист: число строк в исходной и в сводной, сумма продаж в обеих, число ключей без совпадения, число ошибок; во всех проверках разность должна быть нулём.
  • Бренд: Контроль включает сверку с данными площадки: итог по каналу в таблице совпадает с отчётом площадки, расхождение объясняют до использования.
  • Ритейл: Контроль включает сверку с кассой и учётной системой: итог по магазину совпадает с кассовым итогом за день.

Откуда берутся данные

Источник данныхПоля
Выгрузки из учётных систем и площадокПродажи по артикулам, остатки по складам, цены, возвраты; даты и ключи в одном формате.
Справочник артикуловКлюч, категория, бренд, цена, себестоимость, статус, штрихкоды и соответствия.
Правила проверкиДопустимые значения, пороги, число строк и контрольные суммы источника.

Как собрать по шагам

  1. Шаг 1: Бренд: выпишите все источники (учёт, склад, сайт, площадки) и определите общий ключ: артикул модели, при необходимости с размером.
  2. Шаг 2: Ритейл: определите ключ магазина (штрихкод или SKU) и создайте справочник соответствий с артикулами брендов.
  3. Шаг 3: Приведите выгрузки к плоским таблицам: уберите объединения и итоги, оформите как умные таблицы, добавьте столбец очищенного ключа.
  4. Шаг 4: Посчитайте число строк и итоги исходных таблиц и запишите на контрольном листе: это эталон для всех последующих проверок.
  5. Шаг 5: Подтяните данные справочника поиском (ИНДЕКС и ПОИСКПОЗ или ПРОСМОТРX); посчитайте число ключей без совпадения и разберите каждый.
  6. Шаг 6: Постройте сводную таблицу по нужным полям и проверьте, что её итог равен итогу исходной таблицы с точностью до нуля.
  7. Шаг 7: Добавьте проверку данных на листах ввода (списки, числа не меньше нуля, даты) и условное форматирование дублей.
  8. Шаг 8: Посчитайте показатель по строкам, например покрытие в неделях, и добавьте статус по порогам; пороги вынесите в отдельные ячейки, а не в формулу.
  9. Шаг 9: Защитите листы с формулами, оставьте ввод в отдельных ячейках и сохраните контрольный лист с датой выгрузки; следующий пересчёт начните с проверки.

Формулы

ПоказательФормулыПояснение
Покрытие в неделях=ЕСЛИОШИБКА(остаток / продажи в неделю; «нет продаж»)Делить на ноль нельзя: нулевые продажи дают отдельный статус.
Статус по порогам=ЕСЛИ(покрытие < нижний; «дозаказ»; ЕСЛИ(покрытие > верхний; «избыток»; «норма»))Пороги (здесь условные 4 и 16 недель) лежат в ячейках: их задают по категории и сроку поставки.
Сумма по двум условиям=СУММЕСЛИМН(продажи; категория; «Платья»; месяц; «март»)Критерии даны ссылками на ячейки, чтобы не править формулу.
Контрольная разность=СУММ(исходные продажи) − сумма в сводной таблицеДолжна быть нулём; любое иное значение — повод остановиться.

Формулы в записи

Покрытие
K = Q / S
Q — остаток в штуках; S — продажи в неделю; K — покрытие в неделях; при S = 0 покрытие не определено.

Пример с числами

Сверка артикулов: продажи, остаток, покрытие и статус, условная выгрузка

АртикулПродажи в неделю, шт.Остаток, шт.Покрытие, нед.Статус
D-101423007,1норма
D-1021542028избыток
T-210601803дозаказ
T-2118202,5дозаказ
S-305251255норма
S-306090—нет продаж

Покрытие равно остатку, делённому на продажи недели; пороги условные: меньше 4 недель — дозаказ, больше 16 — избыток. Из шести артикулов один без продаж (покрытие не определено, остаток 90 штук лежит), дозаказ нужен 2 артикулам, избыток у 1. Все числа условные.

Рисунки

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

  1. Выгрузка → Очистка и ключ
  2. Очистка и ключ → Поиск по справочнику; Контрольный лист
  3. Поиск по справочнику → Сводная таблица; Контрольный лист
  4. Сводная таблица → Контрольный лист
  5. Контрольный лист → Отчёт и решение
  6. Отчёт и решение
Ошибки в условной выгрузке на 1 000 строк, число строк
Столбчатая диаграмма: Пробелы в артикулах 38, Число записано текстом 21, Дубли артикула 12, Пустые ключи 9, Разное написание 17010203040Пробелы в артикулахПробелы в артикулахСтрок: 3838Число записано текстомЧисло записанотекстомСтрок: 2121Дубли артикулаДубли артикулаСтрок: 1212Пустые ключиПустые ключиСтрок: 99Разное написаниеРазное написаниеСтрок: 1717строк
Данные таблицей
ПоказательСтрок
Пробелы в артикулах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 проверьте в своей версии.

Открыть интерактивно →