Настраиваем таблицу продаж за выходные: гайд из 10 формул для РОПов

Зачем бизнесу вести таблицу продаж в Excel (и какие ошибки она помогает избежать)

Представьте, что у вас небольшой магазин спортивного питания. Три менеджера принимают заказы, а вы пытаетесь понять, почему прибыль то растёт, то проваливается. Один говорит, что продал на 300 000 ₽. Второй уверяет, что его клиенты самые лояльные. Третий вообще молчит и просто выполняет план.

Таблица продаж в Excel покажет, кто действительно приносит деньги, какие товары улетают со склада, а какие пылятся месяцами. Сможете отследить, на каком этапе теряете клиентов. Может, они бросают корзину, узнав стоимость доставки? Или просто забывают оплатить заказ? 

Что включить в таблицу продаж, чтобы не терять заказы и клиентов

Минимальный набор: 

• дата сделки;

• имя клиента;

• контакты;

• товар;

• количество;

• цена;

• сумма заказа;

• статус оплаты;

• менеджер.

Для старта этого достаточно. Но если хотите видеть больше, добавьте такие данные:

• Откуда пришёл клиент. Из рекламы «ВКонтакте», по рекомендации друга, из поиска? Так поймёте, куда стоит вкладывать больше денег на продвижение.

• На каком он этапе воронки продаж. Клиент только оставил заявку? Получил счёт? Оплатил? Забрал товар? Когда видите, на каком шаге люди отваливаются, сразу понимаете, что чинить.

• Детали сделки. Например, клиент попросил перезвонить через неделю или хочет другой цвет товара. Мелочь, которая может спасти от потери заказа.

Структура таблицы продаж

Справочники данных

У директора небольшой компании по доставке цветов появилась проблема: менеджеры вводят названия букетов как попало. «Роза красная», «Красная роза», «розы красн.» — всё это один товар, но Excel видит три разных. Анализ превращается в муку.

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

Как это сделать:

1. Откройте новый файл Excel.

2. В столбец A внесите список товаров: Товар 1, Товар 2, Товар 3.

3. В столбец B — имена менеджеров: Иванов, Петрова, Сидоров.

4. В столбец C — статусы: Новый, В работе, Оплачен, Отгружен.

5. Перейдите во вкладку «Данные» → «Проверка данных».

6. Выберите «Список» и укажите диапазон A1:A10 из справочника.

Теперь при вводе данных вы просто выбираете нужное значение из списка — никаких опечаток и разночтений.

Список продаж

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

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

Как это сделать:

1. Создайте новый лист «Продажи».

2. В первой строке сделайте шапку: Дата — Клиент — Телефон — Товар — Количество — Цена — Сумма — Менеджер — Статус — Источник.

3. Начиная со второй строки, заполняйте данные по сделкам.

4. В столбец «Товар» добавьте выпадающий список из справочника, как делали до этого.

5. То же самое примените к столбцам «Менеджер» и «Статус».

Продвинутые техники включают автоматическую загрузку информации из внешних источников и валидацию через пользовательские правила.

Формулы и функции

Менеджер по продажам мебели каждый месяц вручную считает выручку по своим клиентам. Открывает калькулятор, складывает числа, записывает. И каждый раз — минус два часа. А вот если он узнает про формулу СУММЕСЛИ(), задача будет занимать три минуты.

Как это сделать:

1. В ячейке G2 (столбец «Сумма») введите формулу: =E2*F2 (Количество × Цена).

2. Протяните формулу вниз на все строки с данными.

3. Создайте отдельный блок для подсчёта итогов. Например, в ячейке K2 напишите: =СУММ(G2:G100) — это общая выручка.

4. Чтобы рассчитать выручку конкретного менеджера, используйте: =СУММЕСЛИ(H2:H100;"Иванов";G2:G100).

5. Для подсчёта количества сделок примените: =СЧЁТЕСЛИ(H2:H100;"Иванов").

Шпаргалка по базовым формулам для анализа продаж

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

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

Хорошо, что он узнаёт о сводной таблице. Так он улавливает суть: в успешной точке работает опытный бариста, который к тому же блестяще продаёт десерты. А в отстающей точке — новичок, который стесняется предлагать дополнительные позиции.

Как это сделать:

1. Выделите любую ячейку в таблице продаж.

2. Перейдите во вкладку «Вставка» → «Сводная таблица».

3. Excel предложит диапазон данных — оставьте автоматический.

4. Выберите, где разместить сводную: на новом листе или текущем.

5. В панели справа перетащите поле «Менеджер» в область «Названия строк».

6. Поле «Сумма» перетащите в область «Значения». Так вы получите таблицу с выручкой по каждому менеджеру.

7. Для визуализации выделите сводную таблицу и нажмите «Вставка» → «Диаграмма» → выберите столбчатую или круговую.

Эти функции — лишь верхушка айсберга. Excel предлагает сотни других инструментов для расчётов: от условного форматирования до сложных математических моделей. Освоите их — и ваша таблица станет настоящим аналитическим центром.

Продвинутые пользователи создают интерактивные дашборды с фильтрами и автообновлением, которые показывают полную картину бизнеса в реальном времени.

Пример таблицы продаж в Excel шаг за шагом

Соберем всё вместе и создадим рабочую таблицу с нуля.

Как это сделать:

1. Откройте Excel и создайте три листа: «Справочники», «Продажи», «Анализ».

2. На листе «Справочники» в столбец A внесите товары: Ноутбук Lenovo, Мышь Logitech, Клавиатура Keychron.

3. В столбец B — менеджеров: Антонов, Борисова, Васильев.

4. В столбец C — статусы: Новый, В работе, Оплачен, Отгружен.

5. Перейдите на лист «Продажи» и создайте шапку: Дата — Клиент — Телефон — Товар — Количество — Цена — Сумма — Менеджер — Статус — Источник.

6. Заполните несколько строк тестовыми данными:

• 10.11.2025 — ООО «Альфа» — 7-900-111-22-33 — Ноутбук Lenovo — 2 — 45000 — Антонов — Новый — Google.

• 11.11.2025 — ИП Соколов — 7-900-222-33-44 — Мышь Logitech — 10 — 1500 — Борисова — В работе — Соцсети.

1. В столбец G «Сумма» введите формулу: =E2*F2 и протяните вниз.

2. Для столбцов «Товар», «Менеджер» и «Статус» настройте выпадающие списки из справочников (через «Данные» → «Проверка данных»).

7. На листе «Анализ» создайте блок итогов:

• Общая выручка: =СУММ(Продажи!G:G)

• Выручка Антонова: =СУММЕСЛИ(Продажи!H:H;"Антонов";Продажи!G:G)

• Количество сделок: =СЧЁТ(Продажи!A2:A100)

8. Выделите ячейку C2, выберите «Формат ячеек» → «Числовой».

9. Добавьте сводную таблицу: «Вставка» → «Сводная таблица», перетащите «Менеджер» в «Названия строк», а «Сумма» в «Значения». Постройте диаграмму на основе сводной.

Всё, базовая таблица готова к работе. Но это только фундамент — дальше вы можете добавлять условное форматирование, прогнозные модели и интеграции с внешними сервисами.

Как анализировать продажи в Excel: фильтры, сводные таблицы и диаграммы

Фильтрация данных

Руководитель отдела продаж замечает снижение конверсии. Стоит ему включить фильтр по статусам, перед глазами рисуется ясная картина: 40% заявок зависли на этапе «Счёт выставлен». 

Оказывается, менеджеры частенько забывают напоминать клиентам об оплате. Если же внедрить простое правило — звонить через день после отправки счёта, конверсия тут же вырастает на 15%.

Как это сделать:

1. Выделите любую заполненную ячейку в таблице продаж.

2. Перейдите во вкладку «Данные» → «Фильтр».

3. В шапке таблицы появятся стрелочки.

4. Нажмите на стрелку в столбце «Статус» и выберите только «Оплачен» — увидите только оплаченные заказы.

5. Нажмите на стрелку в столбце «Менеджер» и выберите конкретного сотрудника.

6. Комбинируйте фильтры для детального анализа.

Сортировка данных

Директор рекламного агентства каждый понедельник смотрит на таблицу продаж и не может понять: кто из клиентов приносит реальные деньги? Данные растут хаотично — сегодня мелкий заказ на 5000 ₽, завтра — крупный на 200 000 ₽, потом снова мелочь. Цифры сливаются в кашу.

Однажды коллега показывает ему сортировку по убыванию суммы заказа. Один клик — и картина меняется: 80% выручки приносят всего 6 постоянных клиентов из 45. Остальные 39 — это суета, которая отнимает время менеджеров, но почти не влияет на прибыль.

Как это сделать:

1. Выделите столбец «Сумма».

2. Нажмите «Данные» → «Сортировка» → «Сортировка в пределах указанного выделения».

3. Выберите сортировку по убыванию — самые крупные сделки окажутся сверху.

4. Примените сортировку по столбцу «Дата» → «Сортировка в пределах указанного выделения» → «От новых к старым» — увидите хронологию продаж.

5. Используйте многоуровневую сортировку: сначала по менеджеру, потом по сумме — так поймёте, кто приносит самые дорогие сделки.

Сортировка кажется простой, но в связке с условным форматированием и пользовательскими представлениями вы превращаете её в мощный аналитический инструмент.

Сводный отчёт

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

Как это сделать:

1. Выделите таблицу продаж.

2. Нажмите «Вставка» → «Сводная таблица».

3. Перетащите «Дата» в область «Названия строк» — Excel автоматом сгруппирует по месяцам.

4. Перетащите «Товар» в область «Названия столбцов».

5. Перетащите «Сумма» в область «Значения».

6. Получите матрицу: по строкам месяцы, по столбцам товары, в ячейках — выручка.

7. Добавьте «Менеджер» в область «Фильтры» — сможете смотреть отчёт по конкретному сотруднику.

Кстати, специалист, который знает, что такое прогнозирование продаж в Excel, виртуозно владеет сводными таблицами и понимает, как на их основе строить управленческие отчёты, может рассчитывать на зарплату от 80 000 ₽ в регионах и от 150 000 ₽ в Москве. 

#INNER2#

Сводная диаграмма

Руководитель отдела продаж IT-компании готовит презентацию для генерального директора — нужно показать, как меняется выручка по продуктам за год. Неравнодушный коллега из соседнего отдела советует сделать сводную диаграмму.

График сразу показывает главное: два продукта стабильно растут, один проседает с июня, а четвёртый перестал приносить выручку ещё с января, и его пора закрывать.

Как это сделать:

1. На основе созданной сводной таблицы нажмите «Вставка» → «Сводная диаграмма».

2. Выберите тип «Гистограмма с группировкой» для сравнения продаж по менеджерам. Или «График» для отслеживания динамики по месяцам.

3. Настройте цвета и подписи через «Конструктор».

4. Разместите диаграмму на отдельном листе «Дашборд» для быстрого доступа. Для этого выделите гистограмму, нажмите «Копировать» и вставьте на новый лист.

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

Анализ данных и формирование сводного отчёта

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

Создайте отдельный лист «Отчёт» и соберите там ключевые метрики:

• общая выручка за период;

• средний чек;

• конверсия по этапам воронки;

• топ-5 товаров по выручке;

• топ-3 менеджера.

Используйте формулу: =СРЗНАЧ(Продажи!G2:G100) для расчёта среднего чека.

Для подсчёта конверсии примените: =СЧЁТЕСЛИМН(Продажи!I2:I100;"Оплачен")/СЧЁТ(Продажи!A2:A100)*100. Для ячейки выберите числовой формат.

Чтобы посчитать общую выручку за конкретный период, например за ноябрь 2025, используйте: =СУММЕСЛИМН(Продажи!G2:G100;Продажи!A2:A100;">=01.11.2025";Продажи!A2:A100;"<=30.11.2025").

Для топ-5 товаров по выручке создайте сводную таблицу: выделите данные на листе «Продажи», нажмите «Вставка» → «Сводная таблица», перетащите «Товар» в «Названия столбцов», «Сумма» в «Значения». Щёлкните правой кнопкой по любой ячейке в столбце с суммой, выберите «Сортировка» → «Сортировка от максимального к минимальному». Первые пять строк покажут самые прибыльные позиции.

Для топ-3 менеджеров действуйте аналогично: создайте новую сводную таблицу, перетащите «Менеджер» в «Названия строк», «Сумма» в «Значения», отсортируйте по убыванию. Три верхних имени — ваши звезды продаж. Можете добавить второй показатель — перетащите «Количество» (или любое поле) в «Значения», сортировка по убыванию, чтобы видеть не только выручку, но и число закрытых каждым менеджером сделок.

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

Продвинутые техники построения прогнозных моделей включают автоматизацию отчётов через Power Query и создание профессиональных дашбордов, которые выглядят как дорогие бизнес-решения.

Excel vs CRM: что выбрать для учёта продаж

Excel хорош на старте. Вы быстро создаёте таблицу, настраиваете под себя, работаете без подписки.

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

CRM-системы решают эти проблемы. Все работают в едином пространстве. История взаимодействия с клиентом хранится в одном месте. Система сама напоминает о задачах, отправляет письма, строит отчёты. Интеграции с почтой, телефонией, мессенджерами делают работу продавца комфортнее.

Но CRM стоит денег. Нужно время на внедрение и обучение сотрудников. Настройка под специфику бизнеса требует либо ваших усилий, либо помощи специалиста.

Золотая середина: начните с Excel, освойте учёт и анализ. Когда поток сделок вырастет и Excel начнёт трещать по швам, переходите на CRM. Многие системы позволяют импортировать данные из Excel, так что ваша работа не пропадёт.

Способы учёта, планирования и анализа продаж

Google Таблицы

Как Excel, но от Google и онлайн. Главное преимущество — несколько человек могут работать одновременно. Менеджеры вносят данные с разных компьютеров, и изменения сразу видны всем. Не нужно пересылать файл и путаться в версиях.

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

Минус — меньше функций по сравнению с Excel, особенно в области сложных вычислений и работы с большими массивами данных. Но малому и среднему бизнесу возможностей обычно хватает с запасом.

CRM-система

«Битрикс24», amoCRM, «Мегаплан» — популярные российские сервисы. Они заточены под продажи: встроенная воронка, автоматические напоминания, интеграция с почтой и телефонией.

CRM подходит, когда у вас больше трёх менеджеров, много повторных продаж и длинный цикл сделки. Система берёт на себя рутину, а сотрудники концентрируются на общении с клиентами.

Сервисы бизнес-аналитики

Power BI, Tableau, Yandex DataLens — инструменты для тех, кто хочет создавать красивые и наглядные данные. Их можно подключить к разным источникам (Excel, базы данных, CRM), объяснить с их помощью информацию и строить интерактивные дашборды.

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

Другие источники

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

Статья по прогнозированию продаж (она еще в работе, ссылки нет, давайте просто укажем местечко)

Доработка программы и разработка индивидуальных проектов

Стандартная таблица покрывает 80% потребностей. Но если у вас специфичный бизнес — например, вы продаёте оборудование с монтажом и гарантийным обслуживанием, — понадобятся доработки.

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

Или можно самостоятельно освоить VBA — язык программирования для Excel. Он открывает практически безграничные возможности: от автоматической отправки отчётов по почте до создания полноценных приложений внутри Excel.

Выберите своё обучение на ближайшие недели: прокачать таблицы или целый отдел продаж.

FAQ

Как вести таблицу продаж, если менеджеров много?

Быстрый способ: перейти в Google Таблицы — там все работают одновременно, и изменения синхронизируются автоматически. 

Медленный способ: сохраните файл Excel (Эксель) в OneDrive / SharePoint и настройте совместный доступ. Каждому менеджеру назначьте отдельный лист или область в таблице. Защитите ячейки с формулами от редактирования («Рецензирование» → «Защитить лист»), чтобы никто случайно не сломал расчёты. Создайте также общий свод на отдельном листе, куда будут автоматически стягиваться данные со всех листов через формулы типа =СУММ(Менеджер1!G:G;Менеджер2!G:G).

Можно ли подключить Excel к «1С»?

Да, «1С» позволяет выгружать данные в Excel и загружать обратно. Используйте встроенные обработки для выгрузки отчётов. Главное — следите за форматом данных и соответствием справочников, иначе загрузка будет выдавать ошибки.

Какие формулы лучше использовать для анализа?

Начните с базовых: СУММ, СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧ. Для более глубокого анализа изучите ВПР (поиск значений в таблицах), СУММЕСЛИМН (сумма по нескольким условиям), ИНДЕКС и ПОИСКПОЗ (гибкий поиск данных). Для работы с датами пригодятся МЕСЯЦ, ГОД, РАЗНДАТ. 

Как посчитать прогноз продаж автоматически?

Используйте формулу прогноза продаж: ПРОГНОЗ или ТЕНДЕНЦИЯ — они строят линейный прогноз на основе исторических данных. Освойте также массивы — они упрощают работу с большими объёмами информации и делают формулы универсальными.

Как посчитать коэффициент сезонности продаж в Excel?

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

Как нейросети помогают в прогнозировании?

Нейросети анализируют исторические данные, учитывают сезонность, тренды и внешние факторы, выдавая более точные прогнозы по сравнению с классической формулой прогноза.

Для начала попробуйте встроенные инструменты Excel: «Анализ данных» → «Экспоненциальное сглаживание». Если нужна высокая точность, экспортируйте данные в Python с библиотеками Prophet или LSTM и вернитесь с готовым прогнозом.