Как сделать умную таблицу в Excel с рабочим итогом
Как сделать умную таблицу в Excel: задай заголовки, создай вычисляемый столбец и строку итогов. Проверь новую запись и различие общей суммы с отфильтрованной.
Ольга
Основатель малого бизнеса, партнёр
8 мин чтения
Вся упаковка стоит 1160 рублей, а после фильтра по ленте внизу остаётся 360. Обе суммы верны: одна относится ко всему списку, другая к выбранной позиции. На этом примере я разберу, как сделать умную таблицу в Excel, чтобы понимать её формулы и итог, когда состав закупки меняется.
Я собрала смету для партии заказов: пакеты, бирки и наклейки, затем добавила ленту. Числа небольшие, их легко проверить отдельно. Цены здесь нужны для разбора расчёта. Сначала создадим сам объект таблицы, потом зададим правило для суммы и добавим ещё одну позицию.
Как сделать умную таблицу из исходного списка
Ниже шаги для настольного Excel в Windows. В справке Microsoft таблицы и вычисляемые столбцы описаны для Excel Microsoft 365, Excel 2024 и Excel 2021. Здесь используются обычная таблица Excel и её конструктор, без макросов.
Поставь заголовки в A1:E1. Данные займут строки со второй по четвёртую. Последний столбец пока оставь пустым: там появится одна формула для всех позиций.
Позиция · Единица · Количество · Цена · Сумма
Бумажный пакет — шт. — 20 — 18
Бирка — шт. — 40 — 6
Наклейка — шт. — 50 — 4
Я проверила исходные поля до создания объекта. Количество хранится отдельным числом, цена означает стоимость указанной единицы. Если написать в одной ячейке «20 пакетов», дальнейшее умножение потребует сначала отделить текст от числа.
Внутри списка не нужны объединённые ячейки и разделительные пустые строки. Общий заголовок вроде «Упаковка к ярмарке» можно оставить отдельно. Строка полей должна содержать названия столбцов, а не название всего документа.
В товарные записи я не добавляю ручную позицию «Итого». Она смешала бы разные сущности: отдельные материалы и результат расчёта по ним. Для общего значения ниже списка мы позднее включим специальную строку итогов. Это не ещё одна закупленная позиция.
Дальше создай таблицу:
Выдели A1:E4 целиком, включая пустые ячейки будущей суммы.
Нажми Ctrl+T.
Проверь предложенный диапазон в окне создания.
Укажи, что таблица содержит заголовки.
Подтверди создание.
Важна именно граница A1:E4. Если выделить только заполненные числовые поля, можно потерять названия позиций или будущую вычисляемую колонку. Не полагайся на случайно выбранную ячейку, когда впервые собираешь такой пример.
После создания щёлкни внутри объекта и открой вкладку «Конструктор таблиц». В поле имени таблицы замени стандартное название на Упаковка. Оно пригодится для обращения ко всему списку. Имя пишется без пробелов и должно быть уникальным в книге.
Таблицу часто называют «умной» из-за поведения ссылок, формул и расширения. Цвет полос сам по себе ничего не вычисляет. Ещё этот объект не следует путать со сводной таблицей: здесь мы продолжаем работать с исходными строками сметы.
Что меняется в формулах
Одна строка: количество умножается на цену
В первой пустой ячейке столбца «Сумма», то есть E2, введи:
```text
=[@Количество]*[@Цена]
```
Нажми Enter. Excel создаёт вычисляемый столбец: одно правило распространяется на остальные строки. Здесь знак @ означает значение из текущей записи. Поэтому каждая позиция умножает собственное количество на собственную цену.
У пакетов должно получиться 360, у бирок 240, у наклеек 200. Я проверила арифметику отдельно: 20 × 18, 40 × 6 и 50 × 4. Общая сумма этих трёх позиций равна 800 рублей.
Формула со знаком @ легче читается по смыслу, чем случайная пара координат. Видно, какие поля участвуют в расчёте. Если вводишь её выбором ячеек мышью, Excel может сам подставить структурированные ссылки по названиям заголовков.
Проверь не только первое число. При выделении E3 и E4 посмотри выражения для бирок и наклеек в строке формул. Там должен быть расчёт, а не вручную напечатанное значение. Красивые одинаковые ячейки легко скрывают смесь формул с числами.
Весь столбец: ссылка с именем таблицы
В свободной ячейке H1 за пределами таблицы введи:
```text
=СУММ(Упаковка[Сумма])
```
Это обращение ко всему столбцу данных «Сумма» указанной таблицы. Оно не означает сумму одной текущей строки. Рядом в G1 подпиши результат «Весь список». Я выбрала первую строку, чтобы фильтрация строк данных не скрывала этот контроль вместе с товарами.
Пока H1 показывает 800. При расширении таблицы структурированная ссылка учитывает её изменившийся состав. Это избавляет от необходимости помнить последний номер строки в формуле вроде суммы E2:E4.
Названия полей здесь становятся частью расчёта. Поэтому я использую ясные заголовки «Количество» и «Цена», а не несколько одинаковых «Значение». Если формула не принимается, первым делом сравни её названия с реальными заголовками своей таблицы.
Как проверить добавленную запись и итог
Новая лента должна попасть в объект
Строку итогов пока не включай. Введи сразу под последней записью, начиная с A5: Лента, рулон, 3, 120. Столбец суммы руками не заполняй.
Excel умеет расширять таблицу при вводе непосредственно под её последней строкой. После добавления проверь принадлежность записи к объекту: новая позиция должна входить в диапазон таблицы. Если этого не произошло, используй «Конструктор таблиц» → «Изменить размер таблицы» и задай A1:E5.
В вычисляемой колонке новой строки ожидается 360. Внешняя формула H1 теперь должна учитывать четыре позиции и давать 1160. Так проверяются сразу две вещи: правило расчёта на новой записи и охват всего списка.
Я сделала ещё один арифметический контроль. Если количество рулонов изменить с 3 на 4, сумма ленты станет 480, а вся смета 1280. Для дальнейшего разбора верни исходные 3 рулона: общий результат снова будет 1160.
Если числа на листе не следуют за исходными данными, сначала посмотри формулу и включение записи в таблицу. Автоматический расчёт не заменяет правильный состав объекта. И он не определяет, верно ли ты внёс цену.
Строка итогов может учитывать фильтр
Теперь щёлкни внутри таблицы. На вкладке конструктора включи «Строка итогов». В нижней ячейке столбца «Сумма» открой список вычислений и выбери «Сумма». Подтверждать, что там действительно стоит нужная функция, полезнее, чем просто увидеть новое число снизу.
Для такого итога Excel использует функцию промежуточных итогов. В русской записи формула выглядит так:
```text
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;[Сумма])
```
Теперь в фильтре заголовка «Позиция» оставь только ленту. Нижний итог должен показать 360, а внешняя H1 останется равной 1160. Отфильтрованные записи не удалены: они продолжают входить в таблицу и в обычную сумму её столбца.
Вот контрольные точки этого примера:
Состояние списка · Общий расчёт в H1 · Итог видимых строк
Первые три позиции — 800 — 800, если включить строку итогов
Добавлены 3 рулона ленты — 1160 — 1160 без фильтра
Фильтр оставил только ленту — 1160 — 360
Фильтр снят — 1160 — 1160
Здесь важно заранее назвать, какой итог нужен. Для общей закупки я смотрю «Весь список». Для выбранной позиции использую итог видимого набора. Число без подписи может быть правильным и всё равно привести к неверному решению.
Что сохранить перед следующей закупкой
Вычисляемый столбец полезен, пока в нём одно понятное правило. Если одна позиция считается иначе, не подменяй её сумму незаметным ручным числом. Уточни саму модель: возможно, для скидки или фиксированной платы потребуется отдельное поле.
Я оставляю небольшой список приёмки:
у каждого поля своё название и понятная единица;
колонка суммы содержит расчёт для каждой позиции;
добавленная запись входит в объект;
общий итог отличается по назначению от итога после фильтра;
контрольное изменение количества даёт ожидаемый вклад.
Если нужен разбор суммирования, пригодится материал о сумме в Excel. В чате LibraChat можно обсудить названия своих колонок и правило расчёта. Открой чат и предложи конкретную задачу:
Разбери расчёт упаковки для заказов в Excel. Таблица названа «Упаковка». Объясни формулу =[@Количество]*[@Цена] и отличие от =СУММ(Упаковка[Сумма]). Помоги составить проверку для новой позиции: 3 рулона по 120. Первые три позиции дают 800. Отдельно объясни, почему итог видимого набора после фильтра может отличаться от общей суммы.
Сопоставь объяснение с формулами и результатами в своей книге. Если выбираешь возможности для таких обсуждений, детали собраны в описании доступа.
Когда решаешь, как сделать умную таблицу для своей работы, начни с одной новой позиции. Она должна попасть в расчёт по тому же правилу, а итог должен отвечать на твой вопрос. Тогда таблица остаётся понятной и после того, как список перестал помещаться в первоначальные три строки.
