Формулы Excel для сметы: СУММЕСЛИ, ВПР/XLOOKUP и готовый шаблон

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

Будем работать на примере ремонта: у каждой позиции есть код, категория, объём, цена и итог. Это не госcмета и не замена специализированному ПО, а управленческий шаблон для расчёта предложения и контроля денег по объекту.

Как устроить рабочий шаблон сметы

Не храните цены внутри каждой сметы. Сделайте три листа: «Смета» — для позиций конкретного объекта, «Прайс» — для актуальных расценок, «Итоги» — для сводных цифр. Тогда достаточно обновить одну цену в прайсе, и все связанные строки пересчитаются.

На листе «Смета» заведите столбцы: код, категория, наименование, единица, количество, цена, сумма. Для материалов и работ используйте разные категории: это избавит от ручной сортировки перед отправкой предложения.

Формула №1: считаем стоимость строки

Самая базовая формула — количество умножить на цену. Если количество в ячейке E2, а цена в F2, в G2 пишем:

=E2*F2

Протяните формулу вниз. Если используете фиксированную ставку накладных расходов из отдельной ячейки, закрепляйте её знаком $: =G2*$K$2. Иначе при протягивании ссылка «уедет» вниз.

Формула №2: СУММЕСЛИ для итогов по категории

СУММЕСЛИ складывает только те строки, которые подходят под условие. В смете это удобно, когда нужно быстро понять бюджет электрики, сантехники или материалов.

=СУММЕСЛИ(B:B;"Электрика";G:G)

Здесь B — столбец с категорией, «Электрика» — условие, G — столбец с суммой. Для двух условий используйте СУММЕСЛИМН. Например, чтобы сложить электрику только по одному объекту:

=СУММЕСЛИМН(G:G;B:B;"Электрика";A:A;"Квартира на Тверской")

Формула №3: ВПР для подстановки цены из прайса

В прайсе оставьте как минимум два столбца: код позиции и цену. Если код из сметы находится в A2, а прайс расположен на листе «Прайс» в столбцах A:B, формула будет такой:

=ЕСЛИОШИБКА(ВПР(A2;Прайс!A:B;2;ЛОЖЬ);"Проверьте код")

ЛОЖЬ означает точное совпадение. Это важно: приблизительное совпадение в смете может незаметно подставить не ту цену. ЕСЛИОШИБКА не прячет проблему — она показывает понятную подсказку, если позиции нет в прайсе.

Формула №4: XLOOKUP / ПРОСМОТРX как более гибкая замена ВПР

В новых версиях Excel можно использовать ПРОСМОТРX (XLOOKUP). Ей не нужно считать номер столбца, она ищет в любом направлении и сразу умеет выводить сообщение, если код не найден.

=ПРОСМОТРX(A2;Прайс!A:A;Прайс!B:B;"Проверьте код")

Если файл открывают коллеги со старой версией Excel, оставьте ВПР как совместимый вариант. В статье используем оба способа: это честнее, чем объявлять одну формулу универсальной.

Как посчитать накладные и итог для клиента

В отдельной ячейке укажите ставку накладных расходов, например 10%. Если прямые затраты рассчитаны в G50, формула выглядит так:

=G50*$K$2

Итог для клиента — это прямые затраты, накладные и согласованная маржа. Не смешивайте эти показатели в одной строке: когда заказчик просит изменить объём, вы сразу увидите, что поменялось в себестоимости и прибыли.

Готовая структура шаблона: что скопировать в свой файл

1. Создайте лист «Прайс» и внесите коды, названия, единицы и цены.
2. Создайте лист «Смета» и добавьте позиции объекта.
3. В столбец «Цена» поставьте ВПР или ПРОСМОТРX.
4. В столбец «Сумма» поставьте количество × цена.
5. На листе «Итоги» соберите СУММЕСЛИ по категориям, накладные и итог.

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

Пять проверок перед отправкой сметы

• Цена подтягивается из одного прайса, а не набрана вручную в разных местах.
• Количество записано числом, а не текстом.
• Пустые и неверные коды заметны по сообщению «Проверьте код».
• Накладные расходы посчитаны отдельной строкой.
• Итог не складывает одну и ту же строку дважды.

Когда одного Excel уже мало

Excel остаётся удобным расчётным инструментом. Но когда к смете добавляются договоры, оплаты, задачи бригады, закупки и изменения по объекту, таблица перестаёт быть единственным источником правды. В Приложении 101 можно связать деньги, документы и задачи по объекту, а Excel оставить для привычного расчёта.

Коротко: какие формулы нужны в первую очередь

• Количество × цена — стоимость каждой позиции.
• СУММЕСЛИ — итог по одной категории.
• СУММЕСЛИМН — итог по нескольким условиям.
• ВПР — подстановка цены из прайса в совместимых версиях Excel.
• ПРОСМОТРX / XLOOKUP — более гибкая подстановка цены в новых версиях.
• ЕСЛИОШИБКА — понятная подсказка вместо непонятной ошибки.