Таблицы для РОПа: план-факт по менеджерам, воронка, мотивация и дашборд продаж
Руководитель отдела продаж ведёт в Excel или Google Таблицах пять связанных расчётов: план-факт по каждому менеджеру с выполнением в процентах, конверсию этапов воронки из выгрузки CRM, премии по шкале с порогами, прогноз закрытия месяца и короткий отчёт собственнику. Все они опираются на лист с сырой выгрузкой и справочник планов. Нужны СУММЕСЛИМН и СЧЁТЕСЛИМН, ПРОСМОТРX, работа с датами и рабочими днями, сводные таблицы и привычка проверять формулы на пограничных значениях: 69,9 % и 70 % плана — это разные премии.
Содержание

Частое заблуждение — что раз в CRM есть отчёты, таблица не нужна. Встроенный отчёт покажет сделки по этапам, но не посчитает премию по вашей шкале и не сравнит прогноз с планом с учётом рабочих дней. Первая задача нового РОПа скромнее дашборда: раз в неделю выгружать оплаченные сделки и видеть, кто из менеджеров идёт по плану.
Какие задачи решают таблицы и когда нужен другой инструмент
Таблица у РОПа — посредник между CRM и деньгами. Из CRM приходят сделки с менеджером, этапом, суммой и датами, а на выходе получаются цифры, по которым платят премию и решают, кого подтянуть, какой этап проседает и хватит ли открытых сделок до конца месяца. Рядом часто живут ABC-анализ клиентов и список дебиторки.
Другой инструмент нужен, когда отчёт должен обновляться сам и его ежедневно смотрят несколько человек: тогда данные подключают к BI-системе вроде Yandex DataLens. Выгрузку в сотни тысяч строк из нескольких систем удобнее собирать в Power Query, а саму воронку и поля сделки настраивают в CRM, а не в таблице.
Учебный пример: отдел из пяти менеджеров, оборудование для кофеен
Ситуация учебная. Компания продаёт кофемашины и оборудование кофейням, в отделе пять менеджеров, план на октябрь 2026 года — 7 000 000 ₽. РОП держит файл из трёх листов. «Выгрузка» — сделки из CRM как есть, оформленные умной таблицей; руками этот лист не правят. «Планы» — менеджер и его месячный план. «План-факт» — расчёт.
Факт по менеджеру собирает одна формула: сумма оплаченных сделок с датой оплаты внутри месяца.
=СУММЕСЛИМН(Выгрузка[Сумма]; Выгрузка[Менеджер]; [@Менеджер]; Выгрузка[Дата оплаты]; ">="&Начало; Выгрузка[Дата оплаты]; "<="&КОНМЕСЯЦА(Начало; 0))
Выполнение считается как =ЕСЛИ([@План]=0; "нет плана"; [@Факт]/[@План]). Проверка на ноль нужна для новичка без плана — иначе появится #ДЕЛ/0! и сломает итог отдела. По итогам октября у менеджеров получилось 105 %, 79 %, 99 %, 68,5 % и 120,8 %, всего 6 591 000 ₽, или 94,2 % плана отдела.
Воронка: конверсию считают по когорте
Частая ошибка — делить оплаты месяца на сделки, созданные в этом же месяце: часть октябрьских оплат пришла из сентябрьских сделок, и такая «конверсия» скачет без причины. Правильнее брать когорту — сделки, созданные в октябре, — и смотреть, до какого этапа они дошли.
Выгрузка показывает только текущий этап, поэтому в справочник этапов добавляют порядковый номер, а для проигранных сделок — этап, на котором их закрыли. Сделка «дошла» до этапа 3, если её максимальный номер не меньше трёх: =СЧЁТЕСЛИМН(Выгрузка[Макс. этап]; ">="&3; Выгрузка[Месяц создания]; Отчёт). В примере из 120 октябрьских сделок до демонстрации оборудования дошли 54, до коммерческого предложения — 30, до счёта — 15, до оплаты — 9. Конверсии этапов: 45 %, 55,6 %, 50 % и 60 %. Слабое место — первый контакт: до демонстрации доходят лишь 45 % сделок.
Мотивация с порогами и ловушки в формулах
Шкала в примере такая: ниже 70 % плана премии нет, от 70 % — 3 % от факта, от 100 % — 5 %, от 120 % — 6 %. Её выносят на отдельный лист «Шкала» с двумя столбцами, порогом и ставкой, а ставку подтягивают формулой =ПРОСМОТРX([@Выполнение]; Шкала[Порог]; Шкала[Ставка]; 0; -1). Последний аргумент −1 означает «точное совпадение или ближайшее меньшее». Менеджер с 79 % получает 3 %, с 120,8 % — 6 %, то есть 87 000 ₽.
Ловушек здесь три. Вложенные ЕСЛИ проверяются по порядку, и формула, начатая с условия «не меньше 70 %», отдаст 3 % и тому, кто сделал 130 %. ВПР с приблизительным совпадением работает только на шкале, отсортированной по возрастанию. Третья ловушка — формат: при факте 1 399 500 ₽ и плане 1 400 000 ₽ выполнение 99,96 %, ячейка без десятичных показывает «100 %», а премия считается по ставке 3 %. Правило округления лучше прописать в формуле через ОКРУГЛ до подстановки в шкалу и показывать процент с одним знаком.
Ступенчатая шкала создаёт ещё и скачок. У менеджера с 99 % плана премия 41 580 ₽, а при 100 % была бы 70 000 ₽: лишние 14 000 ₽ выручки стоят 28 420 ₽ премии, и на таком пороге сделки начинают переносить между месяцами. Если это видно в данных, повышенную ставку применяют только к части факта сверх порога.
Прогноз закрытия месяца
К середине месяца собственник спрашивает, выполнит ли отдел план. Простейший ответ — темп по рабочим дням: =Факт/ЧИСТРАБДНИ(Начало; ДатаОтчёта; Праздники)*ЧИСТРАБДНИ(Начало; КОНМЕСЯЦА(Начало; 0); Праздники). Список праздников берут из производственного календаря, иначе январь и май дадут завышенный прогноз. В октябре 2026 года 22 рабочих дня; к 15 октября прошло 11, факт — 3 150 000 ₽, темп даёт 6 300 000 ₽, то есть 90 % плана.
Вторую оценку дают открытые сделки, взвешенные по историческим конверсиям из воронки. Счета на 1 200 000 ₽ при конверсии 60 % дают 720 000 ₽, предложения на 2 000 000 ₽ при 50 % до счёта и 60 % до оплаты — 600 000 ₽. Вместе с фактом это 4 470 000 ₽. Разрыв с темпом в 1,8 млн ₽ должны закрыть сделки, которых пока нет в воронке, и при цикле сделки длиннее двух недель это риск, о котором собственнику лучше узнать в середине месяца.
Встроенная функция ПРЕДСКАЗ.ETS выглядит соблазнительнее, но на выгрузке из CRM её легко обмануть. По справке Microsoft ей нужна временная шкала с постоянным шагом. Несколько значений с одной датой она объединяет по умолчанию через среднее, а не сумму, а пропуски до 30 % точек заполняет сама средним соседних значений. Подайте ей строки сделок — она спрогнозирует средний чек дня, а не выручку. Подайте суммы только по рабочим дням — выходные станут пропусками: в октябре 2026 года это 9 дней из 31, около 29 %, порог не превышен, и функция молча допишет отделу продажи в субботы и воскресенья. Поэтому дневные суммы собирают по всем календарным дням с явными нулями или ставят пятый аргумент, заполнение данных, равным нулю. А для прогноза месяца по двум неделям данных надёжнее темп и взвешенная воронка.
Отчёт собственнику на одном экране
Собственнику не нужны пять листов. Ему нужен экран с датой выгрузки сверху, выполнением плана отдела, прогнозом двумя оценками, конверсиями этапов против прошлого месяца, выполнением по менеджерам и тремя крупными сделками под риском. Каждая цифра берётся из того же расчёта, по которому платят премию: если в отчёте одно выполнение, а в ведомости другое, доверие к таблице пропадёт.
Графиков хватит двух: столбцы плана и факта по менеджерам и линия накопленного факта против плана по дням. Отчёт уходит раз в неделю в один и тот же день, чтобы собственник видел динамику, а не случайные снимки.
С чего начать и в каком порядке осваивать
Каждая следующая таблица опирается на предыдущую: премию не посчитать без надёжного план-факта, прогноз по воронке требует конверсий, а отчёт собственнику собирается из всего сразу. Начать с дашборда — значит красиво показать цифры, которым пока нельзя верить.
- Выгрузите из CRM оплаченные сделки за прошлый месяц, оформите их умной таблицей и проверьте типы: даты должны быть датами, суммы — числами.
- Соберите план-факт через СУММЕСЛИМН и сверьте итог с суммой столбца в выгрузке.
- Добавьте справочник этапов с номерами и посчитайте конверсию по когорте месяца.
- Вынесите шкалу премий на отдельный лист и подключите её через ПРОСМОТРX.
- Посчитайте прогноз темпом через ЧИСТРАБДНИ и взвешенной воронкой.
- Соберите экран для собственника из уже проверенных расчётов.
Пройти этот путь вместе с управленческой частью можно на онлайн-курсе Skillbox «Как вырастить продажи, прокачать команду и захватить рынок»: в траектории руководителя отдела есть планирование, мотивация, контроль показателей, отчётность и работа с Excel, CRM и управленческими дашбордами.
Упражнения и самопроверка
Тренируйтесь на собственной выгрузке с обезличенными менеджерами. Итог план-факта должен совпасть с суммой оплаченных сделок в выгрузке, а число сделок в воронке — с числом строк когорты. Для премий заведите тестовый лист с выдуманными менеджерами на 0 %, 69,9 %, 70 %, 99,96 %, 100 % и 120 % и пересчитайте их вручную: расхождение укажет на ошибку в шкале или округлении. Прогнозы в начале следующего месяца сравните с фактом и запишите, какая оценка оказалась ближе.
Типичные ошибки новичков — правки прямо в листе выгрузки, планы, вбитые в формулы числами, и сравнение месяцев с разным числом рабочих дней. Прогнозированию продаж и системе мотивации команды уделено место на курсе Skillbox «Коммерческий директор»; на нём работают с Excel, amoCRM и Битрикс24. Это программа следующего уровня — для РОПа, который готовится отвечать за выручку целиком.
Как навык влияет на доход
Отдельной надбавки «за Excel» у руководителей отделов продаж не бывает: таблицы — часть ежедневной работы. Но переменная часть дохода РОПа обычно привязана к плану отдела, и тот, кто видит провал воронки в середине месяца, успевает что-то изменить.
Для ориентира — отдельные предложения на hh.ru, а не статистика. В Москве вакансия от 5 августа 2026 года предлагала от 160 000 до 250 000 ₽ на руки при опыте от года до трёх лет, другая, от 6 августа, — от 250 000 ₽ на руки, в Улан-Удэ вакансия от 2 сентября — от 70 000 до 150 000 ₽ на руки. Требований в открытой части страниц не видно, так что неизвестно, просили ли там Excel. Это предложения работодателей, а не фактические выплаты, и по трём вакансиям о рынке не судят.
Что проверить при выборе обучения
На онлайн-курсе НАДПО «Руководитель отдела продаж» аналитике посвящён третий модуль, «Аналитика и CRM»: внедрение CRM, дашборды в Yandex DataLens, ABC- и XYZ-анализ клиентов и автоматизация отчётности, 19 часов теории и 16 часов практики. Excel в темах модуля прямо не назван, шаблоны планирования и анализа продаж указаны среди рабочих материалов. Условия о виде итогового документа противоречивы, его стоит уточнить у школы.
Смотрите, на каких данных идут задания: учебная таблица без пропусков и дублей не научит разбирать реальную выгрузку CRM с проигранными сделками и пустыми датами. Спросите, будет ли расчёт премии по шкале с проверкой преподавателем и можно ли в итоговом проекте собрать свой план-факт.
Если нужен широкий взгляд на школы, обратите внимание на рейтинг онлайн-школ по продажам и коммерческому управлению — он охватывает всё направление, а не только таблицы для РОПа. Начать же можно без курса: выгрузите прошлый месяц и попробуйте получить из него план-факт, который сойдётся с CRM до рубля.