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

«Умная таблица» решает эти проблемы одним сочетанием клавиш. Excel начинает воспринимать данные как единый объект, который сам растягивается, сам копирует формулы и сам обновляет всё, что на него ссылается. Покажем, как её создать и чем она полезна.

Коротко
  • Выделите любую ячейку списка и нажмите Ctrl+T (или «Вставка» → «Таблица»), подтвердите диапазон и галочку «Таблица с заголовками».
  • Таблица сама расширяется при добавлении строк и столбцов.
  • Формула, введённая в одну ячейку, автоматически заполняет весь столбец.
  • Строка итогов включается сочетанием Ctrl+Shift+T и считает только видимые строки.
  • Вернуть обычный диапазон можно командой «Преобразовать в диапазон».

Как создать умную таблицу

Сначала подготовьте данные: в первой строке — заголовки столбцов (без повторов), внутри нет полностью пустых строк и столбцов, нет объединённых ячеек.

  1. Щёлкните по любой ячейке внутри списка.
  2. Нажмите Ctrl+T. Через меню то же самое: вкладка «Вставка» → «Таблица» или «Главная» → «Форматировать как таблицу» с выбором оформления.
  3. В окне «Создание таблицы» проверьте, что диапазон определён верно и стоит галочка «Таблица с заголовками». Нажмите «ОК».

Список получит чередующуюся заливку строк и кнопки фильтра в шапке, а на ленте появится вкладка «Конструктор таблицы» (в старых версиях — «Работа с таблицами» → «Конструктор»). Она видна, только когда выделена ячейка таблицы.

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

Умная таблица в Excel: формула со структурированными ссылками и строка итогов

Что умеет умная таблица

Сама расширяется

Начните печатать в первой пустой строке под таблицей или в соседнем столбце справа — таблица захватит новые ячейки вместе с оформлением и формулами. Ещё один способ добавить строку: встать в последнюю ячейку и нажать Tab. Размер можно менять и вручную — потяните за маленький уголок в правой нижней ячейке.

Если автоматическое расширение сработало некстати, нажмите Ctrl+Z или выберите «Отменить авторазвёртывание таблицы» в появившемся значке с молнией.

Формулы с понятными именами

Внутри таблицы вместо адресов вроде C2*D2 Excel подставляет названия столбцов:

=[@Цена]*[@Количество]

Значок @ означает «значение из этой же строки». Такие записи называются структурированными ссылками. Их легче читать, и они не ломаются при вставке столбцов.

На таблицу можно ссылаться и снаружи. Например, сумма по столбцу:

=СУММ(Продажи[Сумма])

Функция СУММ (SUM) возьмёт весь столбец «Сумма» таблицы «Продажи» — сколько бы строк в нём ни было сегодня и ни появилось завтра.

Формула заполняет столбец сама

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

Строка итогов

Включите галочку «Строка итогов» в «Конструкторе таблицы» или нажмите Ctrl+Shift+T. Внизу появится строка, где в каждой ячейке есть выпадающий список: сумма, среднее, количество, максимум, минимум. Excel использует здесь функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL), поэтому итог считается только по видимым строкам: применили фильтр — увидели сумму по отобранному.

Фильтры, сортировка и срезы

Кнопки фильтра в шапке появляются автоматически. А для частых отборов удобнее срезы — панели с кнопками: «Конструктор таблицы» → «Вставить срез», отметьте нужные столбцы. Щёлкнули по кнопке «Казань» — в таблице остались только казанские строки. Несколько значений выбираются с зажатым Ctrl.

Ещё одна мелочь: при прокрутке длинной таблицы её заголовки заменяют собой буквы столбцов A, B, C — шапка остаётся перед глазами без команды «Закрепить области».

Источник для диаграмм, сводных и списков

Всё, что построено на умной таблице, растёт вместе с ней:

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

Как изменить оформление или убрать его

В «Конструкторе таблицы» есть галерея «Стили таблиц» и галочки: «Чередующиеся строки», «Первый столбец», «Кнопка фильтра». Если полосатая заливка не нужна, выберите в галерее самый первый стиль — «Нет» — или нажмите «Очистить». Свойства умной таблицы при этом сохранятся.

Как превратить таблицу обратно в диапазон

Щёлкните внутри таблицы → «Конструктор таблицы» → «Преобразовать в диапазон» → «Да». Данные и цвета останутся, а авторасширение, структурированные ссылки и срезы пропадут. Формулы с названиями столбцов Excel сам переведёт в обычные адреса.

Ограничения

  • В умной таблице нельзя объединять ячейки.
  • Заголовки должны быть уникальными и текстовыми: формулу в шапку поставить не получится.
  • Функции с динамическими массивами (ФИЛЬТР, СОРТ, УНИК) внутри таблицы выдают ошибку #ПЕРЕНОС! — их размещают рядом, на обычных ячейках.
  • Структурированные ссылки не «закрепляются» знаком $, поэтому при протягивании формулы вбок столбцы сдвигаются. Копируйте такие формулы через Ctrl+C и Ctrl+V — тогда ссылка останется на месте.

Частые вопросы

В каких версиях Excel это работает?

Во всех современных: Excel 2007 и новее, Microsoft 365, Excel для Mac и веб-версия. В Mac вместо Ctrl+T нажимайте ⌘+T.

Можно ли сделать несколько умных таблиц на одном листе?

Да, но оставляйте между ними хотя бы один пустой столбец или строку и не ставьте таблицы одну под другой, если верхняя будет расти: ей некуда будет расширяться.

Чем умная таблица отличается от сводной?

Умная таблица хранит исходные данные и упрощает работу с ними. Сводная — это отчёт, который группирует и подсчитывает эти данные. Лучше всего они работают в паре.

© kak2.ru