Шпаргалка по формулам Excel

Ниже — набор базовых формул Excel, разложенный по блокам. Формулы показаны на русском языке (как в русской локализации Excel). Если Excel на английском, имена функций будут другие, при этом логика останется прежней. Разделители аргументов тоже могут отличаться: чаще встречается «;» в русской версии и «,» в английской.

Если нужны примеры именно под смету ремонта, загляни в материал 101 «Как посчитать в экселе?». Там хорошо показан базовый сценарий: количество × цена, протяжка формулы, итоговая строка.

Как читать формулы Excel, чтобы меньше ошибаться?

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

Первая — сразу решать, какие ссылки должны «ехать» при протяжке, какие должны оставаться на месте. Для фиксации используется знак $: $A$1 фиксирует и столбец, и строку; $A1 фиксирует столбец; A$1 фиксирует строку.

Вторая — держать числа в числах. Если цена случайно стала текстом, часть формул начнёт выдавать ошибки или нули. Проверка простая: число обычно выравнивается по правому краю, текст — по левому (в стандартном формате).

Арифметика и ссылки на ячейки

Это формулы, с которых начинается почти любая таблица со сметой или учётом.

Для примера возьмём строку сметы: в B2 количество, в C2 цена, в D2 сумма.

• =B2*C2 — умножение (сумма по строке).
• =B2+C2, =B2-C2, =B2/C2 — сложение, вычитание, деление.
• =ОКРУГЛ(D2;0) — округление до рублей. Для копеек: =ОКРУГЛ(D2;2).
• =ABS(D2) — модуль, помогает, если где-то появились отрицательные значения.

Округление стоит применять осознанно: если округлять каждую строку, итог может отличаться от округления общего итога. В сметах чаще округляют итог, при этом строковые суммы держат с копейками.

Суммы, средние, минимумы, максимумы

Этот блок закрывает «сколько всего» и «какая типовая цена». В смете это итог по разделу или по всему объекту. В расходах это сумма за неделю, месяц, проект.

• =СУММ(D2:D200) — общий итог по диапазону.
• =СРЗНАЧ(C2:C200) — средняя цена.
• =МИН(C2:C200), =МАКС(C2:C200) — нижняя и верхняя границы.
• =СЧЁТ(D2:D200) — количество ячеек с числами.
• =СЧЁТЗ(D2:D200) — количество непустых ячеек (и числа, и текст).

Если таблица длинная и в ней есть пустые строки, лучше брать диапазон «с запасом» и считать по столбцу, который заполняется всегда (к примеру, «Сумма»).

Как считать и суммировать по критериям?

Когда появляется классификация (раздел, тип работ, объект, подрядчик, статья расходов), обычной суммы уже мало. Нужны условные итоги: «посчитать только материалы», «суммировать только объект №3», «найти расходы по плиточнику».

• =СУММЕСЛИ(A:A;"Материалы";D:D) — сумма по одному критерию.
• =СЧЁТЕСЛИ(A:A;"Работы") — количество строк по условию.
• =СРЗНАЧЕСЛИ(A:A;"Доставка";C:C) — среднее по условию.

Если критериев несколько, используй варианты с «…ЕСЛИМН».

• =СУММЕСЛИМН(D:D;A:A;"Материалы";B:B;"Объект 12") — сумма по двум условиям.
• =СЧЁТЕСЛИМН(A:A;"Материалы";E:E;">0") — количество строк с двумя условиями.

В учёте расходов похожий подход описан в статье 101 «Как вести учет расходов в Excel?»: когда расходов много, без группировки по статьям и условиям цифры быстро теряют смысл.

Логика и ошибки

Логика помогает автоматизировать правила, которые часто проговариваются словами: «если скидка есть, применяем», «если цена пустая, сумму не считаем», «если деление на ноль, показываем пусто».

• =ЕСЛИ(B2="";"";B2*C2) — считать сумму только если количество заполнено.
• =И(A2<>"";C2>0) — проверка сразу двух условий.
• =ИЛИ(A2="Материалы";A2="Работы") — условие «одно из».
• =ЕСЛИОШИБКА(выражение;"") — вернуть пусто или текст вместо ошибки.

В смете часто всплывает деление, когда считается скидка в процентах или маржа. При нуле в знаменателе Excel вернёт ошибку. ЕСЛИОШИБКА в таких местах экономит время на ручной чистке таблицы.

Поиск и справочники

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

• =ВПР(A2;Прайс!A:D;4;ЛОЖЬ) — найти значение в первом столбце справочника и вернуть нужный столбец.
• =ГПР(A2;Прайс!A1:Z3;3;ЛОЖЬ) — горизонтальный вариант, когда справочник разложен по строкам.
• =ИНДЕКС(Прайс!D:D;ПОИСКПОЗ(A2;Прайс!A:A;0)) — связка, которая часто заменяет ВПР в гибких справочниках.

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

Если прайсы и сметы уже собраны в Excel, при переходе в Приложение 101 их не требуется перепечатывать. Есть сценарий импорта: «Быстрый импорт сметы из Excel в Приложение 101».

Текст, даты, динамические массивы

Текстовые формулы нужны, когда данные приходят «грязными»: лишние пробелы, склейка ФИО, артикулы, комментарии. Даты нужны почти всегда: период расходов, срок работ, план-факт.

• =ДЛСТР(A2) — длина текста.
• =ЛЕВСИМВ(A2;5), =ПРАВСИМВ(A2;5), =ПСТР(A2;3;4) — взять часть строки.
• =СЖПРОБЕЛЫ(A2) — убрать лишние пробелы.
• =ТЕКСТ(A2;"ДД.ММ.ГГГГ") — привести дату к формату текстом (полезно в выгрузках).
• =СЕГОДНЯ() и =СЕЙЧАС() — текущая дата и дата-время.
• =ДАТА(2026;2;3) — собрать дату из частей. Удобно, когда год, месяц, день лежат в разных столбцах.

Если Excel поддерживает динамические массивы, появляются формулы для «живых» списков. Они помогают собрать выдачу без сводных таблиц: отфильтровать, отсортировать, получить уникальные значения.

• =ФИЛЬТР(A2:D200;A2:A200="Материалы";"") — вернуть строки по условию.
• =СОРТ(A2:D200;4;-1) — сортировка по 4-му столбцу по убыванию.
• =УНИК(A2:A200) — список уникальных значений.

Как собрать мини-шаблон сметы на формулах?

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

1. Сделай таблицу с колонками: Раздел, Позиция, Ед., Кол-во, Цена, Сумма, Комментарий.
2. В «Сумма» поставь формулу умножения: =D2*E2. Протяни вниз.
3. Добавь итог по таблице: =СУММ(F2:F200).
4. Добавь итог по разделу через СУММЕСЛИ: =СУММЕСЛИ(A:A;"Черновые";F:F).
5. Если есть справочник цен, подтяни цену через ВПР или ИНДЕКС+ПОИСКПОЗ.
6. Закрой типовые ошибки: =ЕСЛИОШИБКА(…;"") в местах, где возможны пустые значения или деление.

Когда таких файлов становится много, начинается борьба за «последнюю версию», за права доступа, за целостность формул. Эту сторону таблиц мы разбирали в материале «Что использовать для составления сметы и ведения финансового учета: Excel или Приложение 101» и продолжили в статье «Excel или Приложение 101 для проектов».

Если хочется показать заказчику понятный расчёт без ручной сборки отчётов, посмотри, как устроен онлайн-расчет сметы и как собрать смету с нуля в Приложении 101.