Экономия пробега и топливаExcel подходит для управления перевозками в небольшой и средней компании: одна таблица рейсов с расходами и несколько формул дают стоимость километра, загрузку машины, прибыль на рейс и грузооборот. Для этого хватает 5–6 функций и сводной таблицы. Когда рейсов становятся сотни в день, нужны несколько диспетчеров с одновременным доступом или автоматические маршруты по окнам доставки, пора переходить на TMS.
Какие таблицы вести
| Лист | Что в нём | Ключ |
|---|---|---|
| Рейсы | Дата, маршрут, машина, километры, масса груза, выручка, расходы | Одна строка равна одному рейсу |
| Машины | Госномер, тип кузова, грузоподъёмность, норма расхода, даты ТО и страховки | Госномер |
| Тарифы | Клиент, направление, ставка, условия оплаты | Клиент плюс направление |
| Расходы постоянные | Страховка, налоги, амортизация, аренда, административные затраты по месяцам | Месяц |
Рейсы лучше вести плоским списком без пустых строк и объединённых ячеек. Тогда его можно фильтровать и разворачивать в сводную таблицу без правок.
Формулы для листа «Рейсы»
Допустим, лист устроен так: A дата, B госномер, C маршрут, D километры, E масса груза в тоннах, F выручка, G расход топлива в литрах, H цена литра, I платные дороги, J оплата водителя, K прочие расходы. Дальше столбцы с формулами для строки 2.
| Столбец | Что считает | Формула |
|---|---|---|
| L | Расходы на рейс | =G2*H2+I2+J2+K2 |
| M | Прибыль рейса | =F2-L2 |
| N | Расходы на километр | =L2/D2 |
| O | Грузоподъёмность машины | =ВПР(B2;Машины!A:C;3;0) |
| P | Загрузка машины | =E2/O2 |
| Q | Грузооборот, т·км | =E2*D2 |
| R | Пометка убыточного рейса | =ЕСЛИ(M2<0;”убыток”;””) |
В Microsoft 365 и Excel 2021 вместо ВПР можно использовать ПРОСМОТРX: она ищет в любом направлении и не ломается при вставке столбцов. В Excel 2019 и старше этой функции нет, поэтому там остаётся ВПР или связка ИНДЕКС и ПОИСКПОЗ.
Пример расчёта по трём рейсам
Допущения условные: цена топлива 2,6 руб. за литр, суммы в рублях, грузоподъёмность машины 22 т.
| Рейс | Км | Масса, т | Выручка | Расходы | Прибыль | Руб. за км | Загрузка |
|---|---|---|---|---|---|---|---|
| Минск, Брест | 350 | 20 | 650 | 433,0 | 217,0 | 1,24 | 91% |
| Минск, Гомель | 300 | 12 | 420 | 369,0 | 51,0 | 1,23 | 55% |
| Минск, Витебск | 290 | 18 | 520 | 351,2 | 168,8 | 1,21 | 82% |
| Итого | 940 | 50 | 1590 | 1153,2 | 436,8 | 1,23 | 76% |
Расходы первого рейса: топливо 105 л × 2,6 = 273 руб., плюс платные дороги 40, оплата водителя 90 и прочее 30, всего 433. Остальные рейсы считаются так же. Итоговые показатели по трём строкам:
- Расходы на километр: 1153,2 / 940 = 1,23 руб.
- Грузооборот: 7000 + 3600 + 5220 = 15 820 т·км, а расходы на тонно-километр 1153,2 / 15 820 = 0,073 руб.
- Рентабельность: 436,8 / 1590 = 27,5%.
- Средняя прибыль рейса до постоянных расходов: 436,8 / 3 = 145,6 руб.
- Точка безубыточности. При постоянных расходах 2400 руб. в месяц нужно 2400 / 145,6 = 16,5, то есть минимум 17 таких рейсов в месяц.
Рейс на Гомель слабый из-за загрузки 55%: машина шла наполовину пустой, хотя цена километра у него такая же, как у остальных. Именно такие сравнения таблица показывает быстро. Свою цену километра можно проверить в калькуляторе стоимости километра.
Итоги по месяцам, машинам и клиентам
- СУММЕСЛИМН: сумма с несколькими условиями. Расходы машины за месяц: =СУММЕСЛИМН(L:L;B:B;”1234 AB-7″;A:A;”>=01.10.2026″;A:A;”<=31.10.2026″).
- СЧЁТЕСЛИ: число убыточных рейсов: =СЧЁТЕСЛИ(R:R;”убыток”).
- СУММПРОИЗВ: взвешенный показатель, например средняя загрузка с весом по километрам.
- ЕСЛИОШИБКА: прячет ошибки деления, если километраж ещё не внесён.
- Сводная таблица: итоги по машинам, клиентам и месяцам в несколько кликов без формул.
Как учесть платные дороги и валюту
В расходах рейса платные участки лучше выделить отдельным столбцом. В Беларуси работает система BelToll, с 1 сентября 2026 года для двухосных машин свыше 3,5 т она берёт 0,121 евро за километр. В России «Платон» с 1 марта 2026 года берёт 5,19 руб. за километр с грузовиков свыше 12 т. Если ставка заказчика в евро или российских рублях, добавьте столбцы «Валюта» и «Курс на дату рейса» и пересчитывайте в рубли белорусские. Курс берут по официальным данным Национального банка Республики Беларусь, а ставки сборов сверяют на сайтах операторов.
Беларусь и Россия: что добавить в таблицу
Файл для российских рейсов отличается набором граф. Для них понадобятся столбцы с номером электронной транспортной накладной (ЭТрН) и сбором за проезд, а расчёты идут в российских рублях. Для белорусских рейсов столбец сбора остаётся, а расчёты ведутся в белорусских рублях. Формулы расходов, загрузки и грузооборота одинаковы для обеих стран, меняются только справочники ставок и курсов. При рейсах между странами ставки и курсы удобно держать на отдельном листе.
Защита от ошибок
- Выпадающие списки для машин, клиентов и маршрутов, чтобы названия не писались по-разному.
- Блокировка листов с формулами: пользователи вводят данные только в столбцы от A до K.
- Условное форматирование: красным подсвечиваются убыточные рейсы и загрузка ниже 60%.
- Проверка данных: километры и масса должны быть положительными числами.
- Копия файла с датой в названии раз в неделю и хранение на общем диске с историей версий.
Порядок работы с файлом
- В день рейса диспетчер вносит дату, машину, маршрут и плановые километры.
- После возвращения машины добавляют фактический пробег, литры по данным заправок и платные дороги.
- Бухгалтерия или логист вносит выручку после выставления счёта.
- В конце недели смотрят сводную таблицу: убыточные рейсы, загрузка по машинам, отклонение расхода топлива от нормы.
- В конце месяца сравнивают прибыль с постоянными расходами и пересматривают ставки для слабых направлений.
Где Excel перестаёт справляться
- Несколько диспетчеров одновременно вносят данные, и файлы расходятся.
- Нужен автоматический расчёт маршрута с окнами погрузки и ограничениями по габаритам.
- Требуется связь с GPS-мониторингом, бухгалтерией и заказами клиентов.
- Ошибка в одной формуле искажает отчёты за месяц, а заметить её в большом файле трудно.
Тогда смотрят на специализированные системы. Переход идёт мягко: сначала данные приводят в порядок в таблицах, затем переносят в систему. Про следующий шаг читайте в материале о переходе от Excel к WMS, а другие решения для перевозчиков собраны в разделе TMS и CRM.
Частые вопросы
Можно ли построить оптимальный маршрут в Excel?
Для 4–6 точек можно перебрать варианты вручную или использовать надстройку «Поиск решения». Для десятков адресов с окнами доставки лучше сервисы маршрутизации. Как выбирают путь, описано в статье про оптимальный маршрут перевозки.
Есть ли готовый шаблон?
Шаблон собирается из описанных выше листов. Готовые файлы из интернета проверяйте: формулы в них часто устроены под чужие данные.
Excel или Google Таблицы?
Для совместной работы в реальном времени удобнее онлайн-таблицы, для объёмных расчётов и сводных таблиц Excel. Основные формулы в них одинаковы.
Как посчитать грузооборот?
Умножьте массу груза в тоннах на расстояние в километрах для каждого рейса и сложите. Методика расчёта разобрана в статье про грузооборот в тонно-километрах.
Проверено: октябрь 2026. Источники: справка Microsoft по функциям Excel, тарифы оператора системы «Платон» (Российская Федерация), постановления Совета Министров Республики Беларусь о системе BelToll, официальные курсы Национального банка Республики Беларусь. Требования к электронной транспортной накладной в Российской Федерации.
Читайте также
- Как вести учёт перевозок в Excel
- От Excel к WMS
- Как рассчитать грузооборот т/км
- Выбор оптимального маршрута
Раздел: Технологии и сервисы / TMS, CRM и автоматизация