В Excel смету обычно строят вокруг трёх вещей: списка позиций, прайса и формул. Для разового расчёта этого хватает. Сложности начинаются на типовых объектах, где один и тот же этап включает десятки работ, а объёмы зависят от площади, длины, количества слоёв или других исходных параметров.
Формулы Excel по-прежнему полезны: они помогают проверить арифметику и собрать простой файл. Для повторяющихся расчётов удобнее параметрический шаблон. В 101 такой шаблон хранит готовый набор позиций из прайс-листа и правила расчёта количества по каждой позиции. Вы вводите параметры конкретного объекта, а сервис пересчитывает объёмы, цену и себестоимость.
Содержание:
Как устроить смету в Excel?
Для небольшого расчёта создайте три листа. В основном примере на листе «Прайс» используем схему A = код, B = название, C = единица измерения, D = цена, E = себестоимость. Строка 1 содержит заголовки, данные начинаются со строки 2. Цену и себестоимость вводите числами. На листе «Смета» укажите позиции и объёмы конкретного объекта. На листе «Итоги» соберите стоимость по разделам.
| Строка | A — код | B — название | C — единица | D — цена, ₽ | E — себестоимость, ₽ |
|---|---|---|---|---|---|
| 2 | P-001 | Грунтование стен | м² | 100 | 50 |
| 3 | P-002 | Окраска стен | м² | 200 | 100 |
Это вымышленные учебные данные, не рыночные расценки. Код позиции должен быть уникальным.
На основном листе «Смета» используем семь столбцов: A — код, B — категория, C — наименование, D — единица измерения, E — количество, F — цена, G — сумма. Код связывает строку с прайсом, количество задаёт объём, а сумма показывает стоимость позиции. Цена из D листа «Прайс» подставляется в F листа «Смета»: это разные листы.
Эта иллюстрация показывает назначение трёх листов, а не буквенную разметку столбцов. Точный порядок колонок для формул основного примера задан в таблице выше: на листе «Прайс» цена находится в D, на листе «Смета» — в F.
Какие формулы Excel нужны для сметы?
Стоимость позиции
Если количество стоит в E2, а цена в F2, сумма строки рассчитывается формулой =E2*F2. Эту формулу протягивают вниз по всем позициям.
Итог по категории через СУММЕСЛИ
На листе «Итоги» введите формулу =СУММЕСЛИ(Смета!B:B;"Электрика";Смета!G:G). Excel проверит категорию в столбце B листа «Смета» и сложит суммы из столбца G для строк «Электрика». Ссылки без имени листа берут данные с текущего листа. Для нескольких условий используйте СУММЕСЛИМН.
На этой иллюстрации показан отдельный упрощённый фрагмент таблицы: A — категория, B — работа, C — сумма. Поэтому здесь формула =СУММЕСЛИ(A:A;"Электрика";C:C) даёт 3 500 + 7 200 + 12 300 = 23 000 ₽. Для основной сметы введите формулу выше на листе «Итоги». Она берёт категории из столбца B и суммы из столбца G листа «Смета».
Цена из прайса через ВПР или ПРОСМОТРX
Основной пример использует пятиколоночный лист «Прайс» из предыдущего раздела. Если в ячейке A2 листа «Смета» указан код P-001, в F2 для цены введите одну из двух формул: =ЕСЛИОШИБКА(ВПР(A2;Прайс!A:D;4;ЛОЖЬ);"Проверьте код") или =ПРОСМОТРX(A2;Прайс!A:A;Прайс!D:D;"Проверьте код"). Обе возвращают 100 из D2 листа «Прайс». ВПР возвращает четвёртый столбец диапазона A:D, а ЛОЖЬ включает точное совпадение. ПРОСМОТРX ищет код в A и возвращает цену из D; точное совпадение используется по умолчанию. Код P-002 вернёт 200. При отсутствии кода показывается «Проверьте код».
ПРОСМОТРX недоступна в Excel 2016 и 2019; для них используйте вариант с ВПР. Синтаксис описан в справке Microsoft по ВПР и справке по ПРОСМОТРX.
Отдельный упрощённый пример: A = код, B = цена
Следующий пример выполняйте в другой учебной книге. В ней также есть листы «Прайс» и «Смета», но лист «Прайс» содержит только два столбца. Не применяйте эти формулы к пятиколоночному прайсу выше.
| Строка | A — код | B — цена, ₽ |
|---|---|---|
| 2 | P-001 | 100 |
| 3 | P-002 | 200 |
В A2 листа «Смета» этой отдельной книги введите P-001, а в F2 — =ЕСЛИОШИБКА(ВПР(A2;Прайс!A:B;2;ЛОЖЬ);"Проверьте код") или =ПРОСМОТРX(A2;Прайс!A:A;Прайс!B:B;"Проверьте код"). Обе вернут 100 из B2 этого прайса; для P-002 результат равен 200. При другом расположении цены измените диапазон и номер возвращаемого столбца ВПР либо возвращаемый диапазон ПРОСМОТРX. В пятиколоночном примере цена находится в D, поэтому для него нужны A:D и номер 4 либо возвращаемый диапазон D:D.
Почему копии Excel-шаблона начинают мешать?
Обычный Excel-шаблон — это файл с заранее настроенными листами и формулами. Для нового объекта его копируют, заменяют объёмы и при необходимости добавляют строки. Вместе с каждой копией появляется отдельная версия прайса и отдельный набор формул.
Если сотрудник вставил строку мимо диапазона, заменил формулу числом или открыл старую копию прайса, итог может выглядеть правдоподобно. Ошибка обнаружится только при сверке. Чем больше типовых расчётов ведёт компания, тем больше времени уходит на контроль структуры файлов.
Самая трудная часть типовой сметы связана с количеством. Для отделки стен одной площади мало: расход некоторых позиций зависит от количества слоёв, длины примыканий, числа проёмов или толщины материала. В Excel эти зависимости приходится распределять по ячейкам и переносить в каждую новую копию.
Что такое параметрический шаблон в 101?
В 101 шаблон — это готовый набор позиций из выбранного прайс-листа. Его можно собрать для этапа или типа работ: электромонтажа, штукатурки, окраски стен, устройства пола. Базовый порядок создания шаблона описан в инструкции 101 по формулам и переменным шаблона.
Параметрический шаблон дополняет этот набор правилами расчёта. У него есть базовое количество и дополнительные параметры, которые вы называете по смыслу своей технологии. Для позиции можно оставить фиксированное количество или задать формулу. В формулу вставляются базовое количество, параметры, числа и арифметические операторы.
У дополнительных параметров можно задать значение по умолчанию и допустимые границы. При добавлении шаблона в смету 101 проверяет обязательные значения, рассчитывает количество каждой позиции и возвращает итоговую цену и себестоимость.
Если часть позиций уже есть в смете или отчёте, интерфейс предлагает сложить объёмы либо заменить их. После вставки результат остаётся редактируемым: можно изменить количество отдельной позиции, убрать работу или добавить новую.
Пример расчёта по параметрам
Представьте шаблон «Окраска стен». Базовым количеством служит площадь стен — 120 м². Дополнительный параметр «Количество слоёв» равен 2. Внутри шаблона можно задать такие правила:
| Позиция | Формула количества | Результат |
|---|---|---|
| Грунтование стен | Базовое количество | 120 м² |
| Окраска стен по расценке за один слой | Базовое количество × количество слоёв | 240 м² (два прохода по 120 м²) |
| Защита поверхностей | Базовое количество × 0,3 | 36 м² |
Это учебный пример. Площадь стен составляет 120 м². Если расценка за 1 м² уже включает два слоя, возьмите для этой позиции базовое количество 120 м². Состав позиций, коэффициенты и единицы измерения вы задаёте по своей технологии выполнения работ. Цены и себестоимость берутся из связанного прайс-листа, поэтому одна и та же логика расчёта применяется к актуальным расценкам.
Для следующего объекта достаточно указать новые исходные значения. Например, при площади 85 м² и трёх слоях шаблон пересчитает связанные позиции по тем же правилам. Ручное протягивание формул и проверка диапазонов не требуются.
Как собрать шаблон и смету в 101?
- Создайте прайс-лист в 101 или загрузите позиции из Excel.
- Добавьте шаблон для повторяющегося этапа и включите в него нужные позиции прайса.
- В редакторе формулы создайте именованный параметр «Площадь стен» и соберите выражение «Площадь стен × 2». Значения для конкретного расчёта вы введёте при добавлении шаблона в смету.
- Добавьте параметры, которые влияют на объёмы: количество слоёв, число точек, толщину или другой показатель.
- Для каждой позиции выберите фиксированное количество либо соберите формулу из параметров и операторов.
- Создайте событие «Смета», выберите проект и статью расходов, затем добавьте шаблон. В блоке «Укажите параметры» для этого учебного примера введите 1 в поле без видимой подписи, а в «Площадь стен» — 10. Заполните оба поля: без них результат может остаться нулевым.
- Для этого учебного примера проверьте, что выражение «Площадь стен × 2» дало 20 м² в количестве позиции. Затем проверьте остальные рассчитанные позиции, цену и себестоимость перед согласованием.
Пошаговый сценарий создания события доступен в справке 101 по созданию сметы. Если исходные позиции уже находятся в таблице или документе, используйте сценарий переноса сметы с помощью 101 AI, а затем свяжите повторяющиеся работы со своим прайсом и шаблонами.
Excel и 101: что меняется в работе?
| Задача | Excel | 101 |
|---|---|---|
| Хранение цен | Отдельный лист или файл прайса | Связанный прайс-лист с ценой и себестоимостью |
| Повтор типового этапа | Копия файла или диапазона | Один шаблон с готовым набором позиций |
| Расчёт объёмов | Формулы в ячейках | Формулы количества внутри позиций шаблона |
| Исходные данные | Значения в разных ячейках | Базовое количество и именованные параметры |
| Повторное использование | Проверка ссылок и диапазонов в каждой копии | Ввод параметров нового объекта и пересчёт позиций |
| Одинаковые позиции | Ручная сверка строк | Выбор: сложить объёмы или заменить их |
| Дальнейшая работа | Передача файла и ручной перенос данных | Смета остаётся связанной с проектом, статьёй расходов и последующими событиями |
Excel остаётся полезным для разового расчёта, нестандартной аналитики и обмена таблицами. 101 удобнее там, где компания регулярно считает похожие этапы, поддерживает единый прайс и использует смету дальше в работе по проекту.
Рекомендация: где считать рабочую смету
Для учебного или разового расчёта используйте Excel и четыре базовых инструмента: умножение, СУММЕСЛИ, ВПР или ПРОСМОТРX и ЕСЛИОШИБКА. Для рабочих коммерческих смет считайте в 101: храните расценки в одном прайсе, собирайте повторяющиеся этапы в параметрические шаблоны и вводите только исходные данные конкретного объекта.
Начните с одного знакомого этапа. Перенесите его позиции в 101, задайте базовое количество и одну формулу, затем сравните результат с проверенной сметой. Если расчёт совпал, добавляйте остальные этапы и используйте шаблон на следующих объектах. Откройте 101 и соберите первый параметрический шаблон.





