CRM в Excel для малого бизнеса: клиенты, сделки, формулы и воронка
Практическая CRM в Excel должна не просто хранить контакты, а показывать сделки, следующий шаг, воронку, деньги и просрочки. Ниже — структура файла, реальные формулы и пример, который можно повторить в обычном Excel.
Частые вопросы
Можно ли использовать Excel как CRM для нескольких менеджеров?
Да, если процесс относительно простой и команда соблюдает единые правила заполнения. При сложных правах доступа, большом числе одновременных пользователей и автоматизациях специализированная CRM обычно удобнее.
Что хранить отдельно: клиентов или сделки?
Если клиент может покупать повторно, храните клиентов и сделки на разных листах и связывайте их через ID клиента. Тогда одна компания может иметь несколько сделок без перезаписи истории.
Как считать воронку в Excel?
Используйте СЧЁТЕСЛИМН для количества сделок по этапам и СУММЕСЛИМН для их суммы. Конверсию перехода считайте как число сделок следующего этапа, делённое на число предыдущего.
Как находить просроченные контакты?
Храните дату следующего действия отдельным полем и сравнивайте её с СЕГОДНЯ(). Просроченные активные сделки можно выделять условным форматированием и считать по менеджерам.
Нужно ли удалять проигранные сделки?
Нет. Сохраняйте результат, дату закрытия и стандартизированную причину отказа. Иначе воронка и анализ причин потерь будут искажены.
Как прогнозировать выручку по открытым сделкам?
Назначьте этапам исторические вероятности закрытия и умножайте сумму каждой открытой сделки на вероятность её текущего этапа. Взвешенный прогноз показывайте отдельно от факта и полного pipeline.
1. Когда CRM в Excel действительно подходит бизнесу
Excel подходит как лёгкая CRM, когда процесс продаж уже понятен, но отдельная система пока избыточна. Обычно это небольшая команда, где важно не потерять обращение, закрепить ответственного, видеть этап сделки, следующий контакт и сумму в работе. Главный критерий — не количество строк, а сложность процесса: один файл должен оставаться понятным и управляемым для всех, кто с ним работает.
Не стоит превращать Excel в имитацию большой CRM. Если нужны сложные права доступа, автоматические звонки и рассылки, история каждого изменения, десятки одновременных пользователей и глубокие интеграции, специализированная система надёжнее. Но до этого момента Excel полезен тем, что позволяет быстро отладить саму модель продаж: какие этапы реально нужны, какие поля менеджеры заполняют и какие показатели руководитель использует.
Рабочая CRM должна отвечать на пять вопросов: кто клиент, какая сделка сейчас в работе, кто отвечает, что нужно сделать дальше и сколько денег находится на каждом этапе. Если файл не отвечает хотя бы на один из них без ручного поиска по комментариям, структуру лучше пересобрать.
2. Структура файла: пять листов вместо одной таблицы
Практичная книга состоит из пяти листов. «Клиенты» хранит карточку клиента: ID, компания, контакт, телефон, email, город и источник первого обращения. «Сделки» — отдельный журнал возможностей продаж. «Справочники» содержит менеджеров, этапы, источники, причины отказа и вероятности. «Задачи» — следующие действия и сроки. «Дашборд» собирает воронку, выручку, pipeline, просрочки и результаты менеджеров.
Разделять клиентов и сделки важно, если один клиент может покупать повторно. Например, ООО «Альфа» должно существовать в справочнике один раз, а сделки «Внедрение», «Продление» и «Дополнительные лицензии» — тремя строками с разными ID, суммами и датами. Иначе повторные продажи начинают перезаписывать историю клиента.
На листе «Сделки» используйте минимум такие поля: ID сделки, ID клиента, дата создания, менеджер, источник, этап, сумма, вероятность, дата следующего действия, дата закрытия, результат и причина отказа. Даты храните как даты Excel, суммы — как числа, а этапы и менеджеров выбирайте из выпадающих списков.
3. Статусы, справочники и формулы, которые не ломаются
Свободный ввод статусов — одна из самых частых причин грязной аналитики. «Предложение», «КП отправлено» и «коммерческое» могут означать одно и то же, но Excel посчитает их разными значениями. Поэтому этапы задавайте справочником, например: Новый → Квалификация → Встреча → Предложение → Переговоры → Оплата. Для каждой стадии зафиксируйте понятный критерий перехода.
Если этап находится в столбце F листа «Сделки», количество сделок на этапе из A2 считается так: =СЧЁТЕСЛИМН(Сделки!$F:$F;A2). Денежный объём этапа: =СУММЕСЛИМН(Сделки!$G:$G;Сделки!$F:$F;A2). Для конкретного менеджера добавьте второй критерий: =СУММЕСЛИМН(Сделки!$G:$G;Сделки!$D:$D;$B2;Сделки!$F:$F;C$1). Так один журнал превращается в отчёт по сотрудникам и этапам без ручных итогов.
Вероятность закрытия лучше хранить в справочнике этапов и подтягивать через ПРОСМОТРX: =ПРОСМОТРX(F2;Справочники!$A$2:$A$7;Справочники!$B$2:$B$7;0). Тогда менеджер не сможет вручную поставить сделке 95% только потому, что хочет улучшить прогноз.
Создать CRM в Excel
Опишите этапы, поля и показатели — SmartXLSX соберёт персональный XLSX с формулами и аналитикой.
Открыть инструмент →4. Практический пример: 40 лидов, 12 предложений и 6 оплат
Допустим, за месяц в CRM появилось 40 новых лидов. 30 прошли квалификацию, 20 дошли до встречи, 12 получили предложение, 8 вошли в переговоры и 6 оплатили. Общая конверсия лид → оплата равна 6 / 40 = 15%. Переход Квалификация → Встреча составляет 20 / 30 = 66,7%, а Предложение → Переговоры — 8 / 12 = 66,7%.
Средний чек шести оплаченных сделок — 120 000 ₽, поэтому фактическая выручка равна 720 000 ₽. В открытом pipeline остаются сделки на 1 350 000 ₽. Если по их текущим этапам взвешенная сумма составляет 610 000 ₽, на дашборде нужно показывать отдельно 720 000 ₽ факта, 1 350 000 ₽ открытого pipeline и 610 000 ₽ ожидаемого результата. Эти показатели нельзя складывать как уже заработанные деньги.
Предположим, у менеджера Анны 18 лидов и 4 оплаты, у Бориса — 22 лида и 2 оплаты. Конверсия Анны 22,2%, Бориса 9,1%. Но перед выводами проверьте средний чек, источники и качество лидов: если Борис ведёт более крупные или холодные сделки, один процент не объясняет всю разницу.
5. Следующий контакт: как CRM помогает не терять сделки
У каждой активной сделки должно быть следующее действие и дата. Это важнее длинного свободного комментария. Если дата следующего контакта находится в I2, а результат в K2, просрочку можно проверить формулой =И(I2<СЕГОДНЯ();I2<>"";K2=""). Такое условие удобно использовать в условном форматировании, чтобы просроченные сделки подсвечивались автоматически.
На отдельном листе «Задачи» полезно вывести сегодняшние и просроченные действия. Для простой проверки количества просрочек по менеджеру используйте СЧЁТЕСЛИМН по ответственному и дате. Например: =СЧЁТЕСЛИМН(Сделки!$D:$D;A2;Сделки!$I:$I;"<"&СЕГОДНЯ();Сделки!$K:$K;""). Руководитель сразу видит не только продажи, но и дисциплину работы с активной базой.
Не заменяйте дату следующего действия цветом ячейки. Цвет нельзя надёжно фильтровать как бизнес-данные, а его смысл быстро забывается. Сначала храните дату и статус в отдельных полях, затем используйте цвет только как визуальный сигнал.
6. Воронка, pipeline и прогноз выручки
Воронка показывает движение количества сделок, pipeline — их денежный объём. Для руководителя нужны оба разреза. Если на этапе «Предложение» находятся 10 сделок на 2 млн ₽, а историческая вероятность закрытия этого этапа 50%, взвешенный прогноз равен примерно 1 млн ₽. Для отдельной строки формула проста: =G2*H2, где G2 — сумма, H2 — вероятность.
Не путайте прогноз с планом. План — целевой результат, факт — уже закрытые оплаты, pipeline — все открытые возможности, а взвешенный прогноз — оценка вероятной части pipeline. На дашборде эти числа должны стоять рядом, но отдельно. Для сравнения цели и результата используйте соседнее руководство по плану продаж, а для детального анализа переходов — материал о воронке продаж.
Полезный контроль качества прогноза — сравнивать прошлые прогнозы с реальным закрытием. Если этап «Предложение» исторически закрывается в 30% случаев, а в справочнике стоит 70%, CRM систематически завышает ожидания. Вероятности лучше пересматривать по накопленной статистике, а не по ощущениям.
7. Отчёты по менеджерам, источникам и клиентам
Минимальный дашборд руководителя может содержать семь KPI: новые лиды, активные сделки, оплаты, выручку, общую конверсию, открытый pipeline и просроченные действия. Ниже добавьте таблицу менеджеров с количеством лидов, оплатами, выручкой, средним чеком и конверсией. Этого достаточно, чтобы увидеть отклонение и перейти к деталям.
Источники сравнивайте не только по числу лидов. Например, реклама дала 25 лидов и 2 оплаты, а рекомендации — 10 лидов и 3 оплаты. Второй канал меньше по объёму, но сильнее по конечной конверсии. Если известны расходы, добавьте стоимость лида и стоимость оплаченного клиента.
Для повторных продаж полезен отдельный разрез по клиентам: первая покупка, число сделок, общая выручка и дата последней оплаты. Так CRM начинает работать не только как список текущих лидов, но и как база для возврата существующих клиентов.
8. Типичные ошибки и контрольные проверки
Первая ошибка — одна строка на клиента при нескольких сделках. Вторая — ручные статусы без справочника. Третья — отсутствие уникальных ID. Четвёртая — удаление проигранных сделок: вместе с ними исчезают причины потерь и реальная конверсия. Пятая — хранение следующего шага только в комментарии без даты.
Добавьте контрольные проверки: ID сделки не должен повторяться; активная сделка должна иметь менеджера и следующее действие; закрытая сделка — дату закрытия и результат; проигранная — причину отказа; сумма оплат на дашборде должна совпадать с суммой закрытых сделок в журнале. Дубли можно находить формулой =СЧЁТЕСЛИ($A:$A;A2)>1.
Отдельно проверяйте старые открытые сделки. Если возможность висит 90 дней без контакта, она завышает pipeline. Возраст сделки можно считать как =СЕГОДНЯ()-C2, где C2 — дата создания. Порог зависит от вашего цикла продаж, но сам показатель должен быть видимым.
9. Как внедрить CRM в ежедневную работу
Начните не с оформления, а с правил. Определите владельца файла, список этапов, обязательные поля и момент, когда менеджер обязан обновить сделку. Например: после каждого контакта обновить этап, комментарий и следующую дату; закрытую сделку в тот же день отметить как «Оплата» или «Проиграна». Без такого регламента даже идеальные формулы быстро получают устаревшие данные.
Утром менеджер должен видеть свои просроченные и сегодняшние действия, а руководитель раз в неделю — воронку, крупные зависшие сделки, конверсию и прогноз. В конце месяца фиксируйте итоговые показатели, но не очищайте журнал: история нужна для сравнения периодов и расчёта реальных вероятностей этапов.
Если не хочется вручную собирать листы, справочники и формулы, используйте инструмент SmartXLSX для CRM в Excel. Для соседних задач пригодятся подробные руководства по воронке продаж, плану продаж и KPI менеджеров — вместе они связывают клиентскую базу, процесс, цель и результат.