Excel для менеджера по продажам: таблица сделок и контроль воронки
В Excel можно собрать рабочую воронку для одного менеджера или небольшой команды: одна строка на сделку, единые этапы, сумма и вероятность, обязательное следующее действие и контроль просрочки. Ниже — точная структура, формулы и проверенный пример, а также граница, после которой таблицу пора заменять CRM.
- Когда Excel подходит для учёта сделок
- Структура таблицы: одна строка — одна сделка
- Как настроить таблицу и единые этапы
- Формулы для суммы, вероятности и просрочки
- Проверенный пример из четырёх сделок
- Как собрать сводку и не ошибиться с конверсией
- Ежедневный ритм менеджера
- Когда пора переходить в CRM
- Вопросы и ответы
Когда Excel подходит для учёта сделок
Excel подходит одному менеджеру или небольшой команде, если сделок немного, процесс прост, файл имеет одного ответственного, а задача — не автоматизировать коммуникации, а видеть текущий статус и не терять следующий шаг. Таблица особенно полезна как первый прототип: по ней команда понимает, какие поля и этапы действительно нужны.
Excel не заменяет CRM, когда несколько сотрудников одновременно меняют данные, нужны права доступа, история каждого действия, письма и звонки, автоматические напоминания, интеграции, обязательный аудит или большой объём записей. Не пытайтесь исправить организационную проблему десятками сложных формул: сначала определите процесс, затем выберите инструмент.
Если вы только осваиваете ввод данных и формулы, сначала проверьте базовые навыки Excel для начинающих. Для таблицы сделок важно уверенно работать с диапазонами, типами данных, ссылками и фильтрами.
Структура таблицы: одна строка — одна сделка
Не объединяйте несколько клиентов в одной строке и не создавайте отдельный лист на каждого менеджера. Одна строка должна описывать одну сделку, а одинаковые поля — находиться в одних и тех же столбцах. Тогда фильтры, формулы и сводные отчёты будут работать предсказуемо.
| Столбец | Что хранить | Правило |
|---|---|---|
| ID сделки | Уникальный короткий код | Не менять после создания |
| Клиент | Название организации или контакт | Без нескольких клиентов в одной ячейке |
| Ответственный | Один менеджер | Выбирать из справочника |
| Этап | Текущее состояние сделки | Выбирать из единого списка |
| Сумма сделки | Ожидаемая сумма | Число в рублях без приписанного вручную текста |
| Вероятность | Условная вероятность этапа | Процент по правилам команды, не личная догадка для каждой строки |
| Взвешенная сумма | Сумма × вероятность | Формула, а не ручной ввод |
| Следующее действие | Звонок, письмо, встреча, расчёт | Конкретный глагол и результат |
| Дата действия | Когда выполнить | Настоящий тип даты |
| Последний контакт | Дата фактического общения | Обновлять после события |
| Источник | Откуда пришёл клиент | Единый справочник |
| Причина проигрыша | Согласованная категория | Заполнять только для проигранной сделки |
Телефон, почта и заметки можно добавить, но не превращайте одну ячейку в дневник. Для персональных данных заранее определите законное основание, доступ и срок хранения; учебный файл создавайте только на вымышленных данных.
Как настроить таблицу и единые этапы
Введите заголовки, добавьте несколько строк и превратите диапазон в объект «Таблица». В Excel для Windows это можно сделать сочетанием Ctrl+T или командой форматирования как таблицы. Microsoft Support указывает, что при создании нужно проверить диапазон и наличие заголовков. Дайте объекту понятное имя, например Сделки.
На отдельном листе «Справочники» создайте списки:
- этапы: Новый, Контакт установлен, Потребность подтверждена, Предложение, Согласование, Успешно, Проиграно;
- ответственные;
- источники;
- причины проигрыша.
Для столбца «Этап» настройте проверку данных со списком. Это предотвращает варианты «предложение», «КП», «отправлено КП» в одном поле. Официальная справка Microsoft по проверке данных поясняет настройку списков и важное ограничение: при копировании и вставке проверка не всегда блокирует недопустимое значение, поэтому файл всё равно нужно контролировать.
Формулы для суммы, вероятности и просрочки
Если столбцы таблицы называются «Сумма сделки» и «Вероятность», формула взвешенной суммы в строке выглядит так:
=[@[Сумма сделки]]*[@Вероятность]
Вероятность должна быть числом в процентном формате: 20%, 50% или 70%, а не текстом «средняя». Значения этапов задаёт команда на основе своего процесса. Взвешенная сумма — не обещанный доход и не бухгалтерский прогноз; это единый способ сравнивать текущий портфель при принятых допущениях.
Для контроля просроченного действия добавьте столбец «Контроль»:
=ЕСЛИ(И([@[Следующее действие]]<>"";[@[Дата действия]]<СЕГОДНЯ();[@Этап]<>"Успешно";[@Этап]<>"Проиграно");"Просрочено";"")
Формула помечает открытую сделку, если действие указано, но его дата уже прошла. Пустое следующее действие тоже требует контроля: его удобнее находить отдельным фильтром или правилом условного форматирования.
Проверенный пример из четырёх сделок
Ниже учебные данные. Суммы и клиенты вымышлены; расчёт нужен только для проверки формулы.
| Клиент | Этап | Сумма | Вероятность | Взвешенная сумма |
|---|---|---|---|---|
| Альфа | Контакт установлен | 120 000 ₽ | 20% | 24 000 ₽ |
| Бета | Потребность подтверждена | 80 000 ₽ | 50% | 40 000 ₽ |
| Гамма | Предложение | 150 000 ₽ | 70% | 105 000 ₽ |
| Дельта | Согласование | 60 000 ₽ | 90% | 54 000 ₽ |
Общий открытый объём равен 120 000 + 80 000 + 150 000 + 60 000 = 410 000 рублей. Взвешенная сумма равна 24 000 + 40 000 + 105 000 + 54 000 = 223 000 рублей. Это математический результат заданных вероятностей, а не гарантия, что команда получит именно 223 000 рублей.
Как собрать сводку и не ошибиться с конверсией
Для оперативной сводки вынесите названия этапов в отдельный диапазон. Количество текущих сделок на этапе можно считать формулой:
=СЧЁТЕСЛИМН(Сделки[Этап];A2)
Сумму текущих сделок на этапе:
=СУММЕСЛИМН(Сделки[Сумма сделки];Сделки[Этап];A2)
Где A2 содержит название этапа. Альтернатива — сводная таблица, которая быстрее показывает разрез по менеджеру, источнику и этапу. Когда данные станут стабильными, можно собрать дашборд в Excel по сводным таблицам.
Важно: текущий снимок этапов не показывает историческую конверсию. Если сегодня на этапе «Предложение» пять сделок, неизвестно, сколько сделок когда-либо дошло до него и сколько затем выиграно. Для конверсии нужна история переходов или регулярные снимки с датами. Не делите механически число текущих сделок одного этапа на другой и не называйте результат конверсией.
Ежедневный ритм менеджера
Таблица работает только при регулярном обновлении. В начале дня отфильтруйте просроченные и сегодняшние действия. После каждого контакта обновите этап, дату последнего контакта, следующее действие и его срок. В конце дня проверьте открытые сделки без следующего шага.
- Фильтр «Контроль = Просрочено» — решить или перенести каждую задачу осознанно.
- Фильтр по сегодняшней дате — выполнить запланированные контакты.
- Пустое «Следующее действие» у открытых этапов — заполнить.
- Успешные и проигранные сделки — закрыть, зафиксировать итог и причину.
- Раз в неделю — проверить дубликаты, ошибочные этапы, даты как текст и строки без ответственного.
План продаж — отдельная управленческая задача. Сравнивать воронку с целью лучше после того, как определены правила расчёта и декомпозиции; они разобраны в статье про расчёт и декомпозицию плана продаж.
Когда пора переходить в CRM
Переход оправдан, если таблица больше не обеспечивает дисциплину и прозрачность:
- файл регулярно содержит конфликтующие копии;
- нужны разные права доступа и журнал изменений;
- менеджеры забывают действия без автоматических напоминаний;
- нужно связывать сделки с письмами, звонками и задачами;
- объём ручного обновления мешает работе с клиентами;
- руководитель не может восстановить историю движения сделки;
- нужны стабильные интеграции и сквозные отчёты.
Перед переносом очистите справочники, удалите дубликаты, определите обязательные поля и согласуйте этапы. Грязная таблица не станет качественным процессом только потому, что её импортировали в более сложную систему.
Рабочая таблица продаж держится на простой логике: одна строка — одна сделка, единые этапы, числовая сумма, обязательное следующее действие и дата. Превратите диапазон в таблицу, добавьте проверку данных и формулы, ежедневно закрывайте просрочки и не называйте текущий срез исторической конверсией. Когда совместная работа и история перестанут помещаться в этот формат, переходите в CRM с уже понятным процессом.
Освойте Excel для сделок, отчётов и управленческих задач
Курс Excel + «Google Таблицы» с нуля до PRO помогает системно освоить формулы и функции, очистку данных, сводные таблицы, отчёты, объединение источников и автоматизацию. Выдаваемый документ — Удостоверение о повышении квалификации.
Вопросы и ответы
- Можно ли использовать Excel вместо CRM?
Да, для одного менеджера или небольшой простой воронки, если есть ответственный за файл и не требуются сложные права, история коммуникаций и автоматизация. При росте команды, объёма и интеграций CRM обычно надёжнее.
- Какие столбцы обязательны в таблице продаж?
Минимум нужны ID сделки, клиент, ответственный, этап, сумма, следующее действие и его дата. Для анализа добавьте вероятность, взвешенную сумму, источник, последний контакт и причину проигрыша.
- Почему взвешенная сумма не равна прогнозу продаж?
Она зависит от условных вероятностей, заданных этапам, и показывает лишь оценку портфеля при этих допущениях. Реальный прогноз требует проверенной истории конверсий, сроков, качества данных и факторов конкретного бизнеса.
- Как посчитать конверсию между этапами в текущей таблице?
Одного текущего среза недостаточно: он не хранит, сколько сделок проходило каждый этап. Нужна история переходов или регулярные снимки с датами; иначе отношение текущих остатков будет не конверсией, а случайным соотношением.