Умная таблица в Excel

Узнаем все особенности умных таблиц в Excel, чем же они так хороши и какие преимущества они дают при работе с данными.


Приветствую всех, дорогие читатели блога TutorExcel.Ru!

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

Чтобы восполнить этот пробел и хочется подробно рассмотреть все возможности умных таблиц. И вообще понять почему эти таблицы так называются, чем же они все-таки умные и как ее сделать?

Начнем.

Как сделать умную таблицу в Excel?

Давайте рассмотрим стандартную таблицу (не умную) и на ее основе поймем какие преимущества мы получим при создании умной таблицы:

Исходная таблица

Имеем на вид вполне стандартную таблицу и первым шагом для превращения таблицы в умную будет ее преобразование.

Для этого встаем в любую ячейку нашей таблицы и в панели вкладок идем в Главная -> Стили -> Форматировать как таблицу и выбираем подходящий стиль оформления (или можно просто воспользоваться комбинацией клавиш Ctrl + T):

Преобразование в умную таблицу

Перед нами появляется диалоговое окно, где нужно задать диапазон для таблицы (при этом обратите внимание, что Excel автоматически определяет размеры таблицы если начальная ячейка находится внутри исходной таблицы). Параметр Таблица с заголовками означает, что заголовки нашей таблицы (в данном случае это данные из строки 1) будут использованы как заголовки для умной таблицы. Нажимаем ОК и получаем умную таблицу:

Умная таблица в Excel

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

  • В заголовках таблицы автоматически добавляет фильтр;
  • Таблица получает имя, которое можно использовать для ссылки на таблицу;
  • Таблица изменяет размер при добавлении новых строк или столбцов;
  • При добавлении нового столбца формула копируется на весь столбец;
  • При добавлении новой строки копируются все формулы;
  • При прокрутке таблицы заголовки встают вместо названия столбцов (т.е. аналог закрепления верхней строчки);
  • Отдельные элементы таблицы также получают имена.

Свойств достаточно много, теперь давайте о каждом немного поподробнее.

Добавление фильтра

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

Добавление фильтра

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

Имя умной таблицы

Использование имени таблицы очень удобно, к примеру, при создании сводных таблиц в качестве источника данных нужно указать диапазон и как раз для этого отлично подходит имя таблицы (по умолчанию название имеет вид типа Таблица1, в данном же случае указал название Продажи так как в таблице именно данные по продажам):

Задание имени умной таблицы

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

  • Начинается с буквы или символа подчеркивания («_»);
  • Не содержит пробел или другие недопустимые знаки;
  • Не совпадает с уже существующими именами в книге.

Автоматическое изменение размера

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

Добавление нового столбца

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

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

Обозначение границ таблицы

Копирование формул для всего столбца

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

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

Копирование формул на весь столбец

Добавление формул для новой строки

Аналогичная история происходит и при добавлении новой строчки в таблицу. Если в строках таблицы есть какие-либо формулы, то при добавлении строки они в нее будут скопированы:

Копирование формул для новой строки

Закрепление заголовков при прокрутке

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

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

Дублирование заголовков вместо названия столбцов

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

Возврат названия столбцов при перемещении ячейки

Отдельные элементы таблицы

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

Вид записи формул в умной таблице

В общем и целом это ссылки для упрощения работы с таблицей:

  • Название_таблицы[#Все] — ссылка на всю таблицу;
  • Название_таблицы[#Данные] — ссылка на данные (вся таблица без заголовков);
  • Название_таблицы[#Заголовки] — ссылка на заголовки;
  • Название_таблицы[@] — ссылка на текущую строку из таблицы;
  • Название_таблицы[@Название_заголовка] — ссылка на ячейку из текущей строки в столбце Название_заголовка;
  • и т.д.

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

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

Дополнительные настройки

При работе с таблицей в панели вкладок активируется дополнительная вкладка Конструктор с несколькими блоками команд внутри:

Вкладка Конструктор в панели вкладок

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

  • Свойства;
    Можно задать Имя таблицы и ее размер.
  • Инструменты;
    Позволяет построить сводную таблицу на основе умной, добавить срезы в таблицу (аналог фильтров) и сделать из умной таблицы обычную.
  • Данные из внешней таблицы;
    Настройка параметров для подключения к внешним данным.
  • Параметры стилей таблиц;
    Возможность добавления строки итогов для таблицы и выделение определенных частей таблицы (первый столбец, последний столбец, чередование строк и т.д.).
    Обычно применяется для оформления внешнего вида таблицы и улучшения читаемости данных из нее.
  • Стили таблиц.
    Настройка внешнего вида таблицы с точки зрения цветового оформления.

Как преобразовать умную таблицу в обычную?

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

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

Несмотря на все преимущества при использовании умных таблиц у них также есть и недостатки.

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

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

На этом все!

Скачать файл с примером.

Спасибо за внимание!
Если у вас есть какие-либо вопросы или мысли по умным таблицам — спрашивайте и пишите в комментариях.

Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!

Поделиться с друзьями:
Поиск по сайту:

Одна идея о “Умная таблица в Excel

  1. Глеб

    Николай, подскажите пожалуйста, при копировании данных (значениями) из неумной таблицы в умную, данные вставляются не из нужного столбца, а из столбца совпадающего со столбцом, куда хочу вставить данные. Например, если скопировать в обычной таблице столбец «С», а вставить скопированные данные в умную таблицу в столбец «D», то произойдет вставка данных из столбца «D» обычной таблицы, а не из «C», как планировалось. Как этого избежать и как заставить Excel вставлять данные, те которые мне нужны, а не ему Блин, столкнулся с такой же проблемой, только у меня пустые ячейки вставляются, весь интернет облазил так и не нашел решение((

Добавить комментарий

Ваш адрес email не будет опубликован.