Умные таблицы в Excel: что это, как сделать и как все вернуть

Сейчас расскажем, как быстрее работать с упорядоченными данными, упрощать фильтрацию и поиск данных.

В чем суть умных таблиц

Обычно информация в столбцах Excel не структурирована. Она называется диапазоном данных.

Диапазон можно расширить, используя так называемую «Умную таблицу».

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

Чтобы превратить наш диапазон в умную таблицу, выделите любую ячейку с данными и на вкладке «Главная» выберите «Форматировать как таблицу». После этого можно выбрать любой из вариантов графического форматирования, в результате чего получится примерно следующее:

Теперь наш диапазон имеет свое имя, задать которое можно в главном меню:

А все данные внутри таблицы рассматриваются как единый диапазон. При добавлении информации в столбцы или строки, этот диапазон будет автоматически расширять всю таблицу.

Кроме этого вы можете изменять общий размер таблицы с помощью ползунка:

Фильтр в столбцах умной таблицы теперь применяется автоматически, в т.ч. и при добавлении новых колонок. Верхняя строка автоматически закрепляется при прокрутке.

Таким образом, данные начинают выглядеть более привлекательно и цельно. Но кроме этого добавляются и новые возможности.

Базовые возможности умной таблицы Excel

Поскольку вся структура умной таблицы взаимодействует как единое целое, то многие полезные вещи в ней происходят автоматически:

1. При добавлении новых строк в них автоматически будут скопированы все формулы, применяемые в ячейках до этого;
2. При добавлении нового столбца с формулой, она будет автоматически применяться ко всему столбцу;
3. Можно добавить итоговую строку, которая автоматически подсчитает итоги по всем значениям столбца:

Выставьте галочку на вкладке «Конструктор» — и строка появится в нижней части умной таблицы («Среднее» в столбце мы выбрали вручную):

По каждому столбцу можно выбрать формулу итогового подсчета, которая выдаст соответствующее значение в строке. В примере выше мы суммировали все доходы с разных каналов и вычислили среднее для всех средних чеков.

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

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

Для этого можно из любой ячейки вызвать формулу, указать в качестве источника данных имя умной таблицы Excel, затем в квадратных скобках подставить имя нужного столбца.

6. Работа с умной таблицей упрощает ввод новых формул. Можно, к примеру, использовать имена столбцов в квадратных скобках, чтобы указать формуле откуда брать значение ячейки:

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

8. Можно обращаться ко всей таблице или отдельным ее частям. Базовые формулы:

• Таблица1[#Все] — ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов;
• Таблица1[#Данные] — ссылка только на данные (без строки заголовка);
• Таблица1[#Заголовки] — ссылка только на первую строку таблицы с заголовками столбцов;
• Таблица1[#Итоги] — ссылка на строку итогов (если она включена);
• Таблица1[#Эта строка] — ссылка на текущую строку, например формула ;
• Таблица1[[#Эта строка];[Продажи]] — будет ссылаться на значение «Продажи» из текущей строки таблицы.

Добавление срезов к таблице

Если вы уже работали со сводными таблицами Excel, то могли иметь дело со срезами — это удобный инструмент для быстрой фильтрации данных и упрощения их анализа.

Срезы есть и в умных таблицах. Допустим, нам нужно увидеть значения продаж только по выбранному каналу. Можно применить фильтр, однако срез упрощает этот процесс — в ходе его использования у вас появится отдельная панель, в которой выбрать нужный канал можно будет простым одинарным кликом.

Кнопка вставки среза есть на вкладке «Конструктор» умной таблицы Excel:

В окне вставки среза выбираем один или несколько столбцов, по данным из которых мы будем получать сводную информацию.

Теперь, чтобы отфильтровать данные, достаточно кликнуть на нужном значении в панели:

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

С точки зрения аналитики, срезы в умных таблицах удобны для построения быстрых отчетов и перемещения по данным «на лету».

Чтобы снять фильтрацию с таблицы, щелкните по иконке «Удалить фильтр» на панели среза.

Можно добавить и другой срез, при этом первый добавленный будет считаться главным, и примененный на нем фильтр не даст второму срезу отобразить скрытые данные:

В нашем примере дата 12.04.2023 на панели срезов показывается как скрытая, потому что срез по каналу продаж не выдает ее в конечной таблице. Можно выбрать и ее, но тогда данных в таблице не будет.

В нашем же случае мы сначала сделали срез по Маркетплейсу 1, а затем выбрали дату 11 апреля, что и привело к выдаче единственного значения. Если бы значений по данному каналу за выбранную дату было больше, мы бы получили срез по двум фильтрам, то есть наиболее уточненные данные для последующего анализа.

Чтобы удалить панель срезов, кликните по ней правой кнопкой и выберите соответствующий пункт.

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

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

Другие полезные возможности

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

После удаления дубликатов все итоговые значения автоматически пересчитаются.

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

Вставляя диаграмму, вы сразу можете убедиться, насколько удобнее становится этот процесс:

Добавьте диаграмму на любой элемент таблицы, и уже при выборе типа визуализации появится пример ее внешнего вида. Так, в нашем случае система сообразила, что лучше всего построить соотношение суммы и среднего чека по каждой дате.

3. Добавьте срез или фильтр – и диаграмма автоматически обновится, демонстрируя показатели по выбранному пулу значений. Конечно же, можно вручную выбирать данные для показа на графиках и менять способы их отображения.
4. Передача данных из умных таблиц в Power Query и сводные таблицы также происходит намного удобнее обычного диапазона. Обновите умную таблицу Excel – обновится и сводная.

Как отменить создание умной таблицы и вернуть все как было

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

Но при его выборе вы получите лишь таблицу стандартного черно-белого вида, «умной» она быть не перестанет.

В настройках форматирования на главной вкладке вы вообще не найдете возможности что-либо сделать с умной таблицей.

Отменить умную таблицу можно в «Конструкторе» — для этого используйте пункт «Преобразовать в диапазон»:

Согласитесь с преобразованием и данные вернутся к обычному виду. При этом сохраняется и форматирование, и строка итогов, но пропадают фильтры:

Функции и возможности умной таблицы при возврате пропадают, исчезают срезы. Но в любой момент вы можете вновь применить форматирование и вернуться к использованию этого без сомнения мощного и удобного инструмента Microsoft Excel.

Кстати: В Google Docs (Гугл.Таблицах) подобного инструмента пока нет. Для его использования вам потребуется установленный пакет Microsoft Office.

Недостатки умных таблиц

При всех достоинствах инструмент имеет и ряд минусов, к которым стоит быть готовым:

• При большом количестве данных в виде умных таблиц, файл Excel может замедлять работу компьютера. Поскольку работает условное форматирование и постоянный пересчет значений, это активнее нагружает систему.
• Таблица перестает автоматически расширяться по строкам после добавления строки итогов. Это может доставить неудобство при частом обновлении данных.
• Под заголовки и «шапку» таблицы выделена только одна строка. Это лишает возможности делать многоэтажные и разветвленные заголовки, — то есть умные таблицы по своей сути являются еще и простыми.
• При использовании длинных имен столбцов формулы становятся еще более длинными и сложными для восприятия.
• Общий доступ к Excel-файлу с умными таблицами невозможен.

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

Читайте также:

17 фишек Excel, с которыми будет проще запустить рекламу

20+ функций Excel, которые ускорят работу в несколько раз

Анализ данных на автомате: гайд по Microsoft Power BI для начинающих