Как создать умную таблицу в excel пошаговая инструкция

Умные таблицы появились в Excel, начиная с 2007 версии. Это привычные многим полосатые таблицы, которые выглядят примерно так:

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

Чтобы из обычной таблицы Excel сделать “умную” таблицу, нужно:

1. Щелкнуть на любой ячейке таблицы. 

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

2. Нажать сочетание клавиш Ctrl + T. Вокруг таблицы появится рамочка- выделение и окно Создать таблицу, в котором указан выделенный диапазон

Если вдруг это выделение не захватило какую-то нужную область, можно выделить область таблицы вручную.

Обратите внимание на предыдущую картинку — если ваша таблица уже имеет заголовки (“шапку”), то обязательно должна быть установлена “галочка” Таблица с заголовками. Иначе заголовки будут добавлены автоматически в формате Столбец1, Столбец2 и т.д.

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

3. Нажать Ок или клавишу Enter. Готово, у нас получилось преобразовать обычный диапазон в умную таблицу Excel.

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

1. Также щелкаем в любой ячейке таблицы.

2. Вкладка Главная — Блок Стили — Форматировать как таблицу — в Выпадающем окне выбираем стиль таблицы.

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

Плюсы и минусы инструмента умные таблицы в Excel

Преимущества инструмента Умные таблицы в Excel

1. Таблица автоматически расширяется при добавлении строк или столбцов. 

Главное требование при этом — новый столбец или строка должны добавляться “впритык” к таблице — без пропусков строк или столбцов.

2. Формулы, введенные в ячейку, после нажатия Enter автоматически копируются на весь столбец.

3. Автоматически добавляются автофильтры ко всем столбцам. И на столбцы, добавленные уже после создания таблицы, фильтры также “навешиваются” автоматически. При этом фильтр можно убрать (см. в разделе Форматирование умной таблицы)

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

4. Умные таблицы в Excel имеет имя, которое присваивается автоматически, но его можно изменить. 

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

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

6. Структурированные формулы, которые легко читать.

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

7. Автоматическое добавление итогов к таблице с возможностью выбора вида итога (сумма, среднее и т.д.).

8. Удобно создавать сводные таблицы на основании умных таблиц. При добавлении новых столбцов или строк в исходную таблицу, они автоматически добавляются в сводную таблицу (после ее обновления) без необходимости настраивать диапазон.

9. Умные таблицы в Excel удобно загружать в Power Query для последующей обработки. 

10. Ну и конечно, красивый “полосатый” внешний вид без “танцев с бубнами” с условным форматированием. 

Еще одно преимущество “внешности” — легкая настройка форматирования умной таблицы в Excel. На вкладке Конструктор таблиц его можно быстро настроить (см. раздел Форматирование умной таблицы)

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

Минусы инструмента Умные таблицы в Excel

1. При достаточно большом размере таблицы и большом количестве формул файл с умной таблицей может тормозить. Это связано с тем, что форматирование умной таблицы сделано по типу условного форматирования, которое “нагружает” файл.
2. Если добавить строку итогов, то функция автоматического расширение «умной таблицы» по строкам при добавлении данных перестает работать. Это логично, т.к. не могут же новые строки с данными быть после строки итогов. Расширить таблицу в этом случае можно только растягиванием за уголок.
3. Заголовки можно располагать только в первой строке умной таблицы. Это лишает возможность делать сложные «шапки» таблиц, а также использовать одинаковые названия столбцов — если столбцы будут называться одинаково, Excel автоматически добавить к ним цифру 2, 3 и т.д. 
4.  Формулы могут становятся очень длинными, поскольку в формулу прописывается наименование столбца. Особенно если заголовки длинные сами по себе. Этот момент ограничивает возможность давать сложные названия столбцам.
5. Нет возможности закрепить ячейки или столбцы абсолютными/относительными ссылками, и при “протягивании” формулы ссылки съезжают.
6. Нельзя установить режим совместного доступа в файл, который содержит хотя бы одну “умную” таблицу.

Форматирование умной таблицы

Можно достаточно гибко настраивать форматирование умной таблицы в Excel. 

Настройка внешнего вида умной таблицы

1. Откроем вкладку Конструктор таблиц (при этом должна быть активна любая ячейка внутри таблицы)
2. В блоке Стили таблиц можно выбрать подходящий стиль из выпадающего списка.

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

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

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

Также можно очистить стиль умной таблицы. 

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

Для этого нужно щелкнуть на любой ячейке таблицы — вкладка Конструктор таблиц — блок Стили таблиц — выпадающий список — Очистить.

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

Параметры стиля умных таблиц в Excel

Если вы выбрали предустановленный стиль умной таблицы в Excel, то можно сделать его поднастройку. На вкладке Конструктор таблиц в блоке Параметры стилей таблиц можно настроить параметры установкой/снятием “галочек”

• Строка заголовков: включает и отключает заголовки в таблице
• Строка итогов: включает/отключает строку итогов. 
• Чередующиеся строки — включает/отключает “полосатость” таблицы, все строки становятся бесцветными.
• Чередующиеся столбцы — включает/отключает чередования цвета столбцов. Со включенным параметром столбцы окрашиваются цветом “через один”
• Первый столбец и Последний столбец — включает/отключает жирный шрифт для первого и последнего столбца в таблице.
• Кнопка фильтра — включает/отключает автофильтр.

Как изменить имя умной таблицы в Excel

Также на вкладке Конструктор таблиц можно изменить имя таблицы. По умолчанию Excel присваивает таблицам имена типа Таблица1, Таблица2… Однако, лучше их менять на более релевантные, это удобно для использования в формулах.

Чтобы изменить имя умной таблицы, заходим на вкладку Конструктор таблиц — Блок Свойства — в поле Имя таблицы пишем новое имя.

Добавляем срезы (фильтры) к умной таблице

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

Чтобы добавить срез к умной таблице в Excel, нужно перейти на вкладку Конструктор таблиц (должна быть активна любая ячейка внутри таблицы) — блок Инструменты — Вставить срез.

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

Срезы добавлены. Теперь можно фильтровать данные в умной таблице Excel с их помощью.

Как убрать умную таблицу

Иногда нужно преобразовать умную таблицу обратно в диапазон — это значит, что нужно убрать умную таблицу и оставить просто данные.

Для этого переходим на вкладку Конструктор таблиц — блок Инструменты — Преобразовать в диапазон

В появившемся окне нажимаем кнопку Да.

Таблица не изменила свой внешний вид — но она перестала быть умной таблицей (т.е. лишилась всех ее функций — см. раздел Плюсы и минусы умной таблицы в Excel).

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

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

Читаем – что такое маркер автозаполнения.

Одно дело, если количество столбцов и свойств не так много, но вот с большими данными у вас могут возникнуть проблемы. Да и времени вы на это будете тратить куда больше. Именно для этих целей и придумали «Умные таблицы». Умная таблица – это целый объект, объединения ячеек, в которой сохраняется постоянное форматирование и расчет в формулах по выделенным столбцам и ячейкам. Также там есть постоянная шапка, а все расчеты подтягиваются из соседних смежных строк. В сегодняшнем уроке мы посмотрим, как проще всего сделать умную таблицу в Excel.

Для создания умной таблицы вам нужно выбрать нужный диапазон ячеек, который уже заполнен. Самое главное, чтобы у этих ячеек уже были заголовки, и они были уникальны – это важно для работы подобного элемента в Excel. После этого перейдите на вкладку «Главная». Далее у нас есть два способа создания умной таблицы.

Первый способ – найдите раздел «Стили» и кликните по кнопке «Форматировать как таблицу».

Выберите стили, которые вам нравится.

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

Ctrl + T

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

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

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

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

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

Читаем – как добавить строку в Excel.

Работа с формулами

Еще один момент при работе с умной таблицей в том, что вы можете оперировать не отдельными ячейками, а целыми столбцами. Например, в моем случае мне нужно в столбце «Прибыль» посчитать произведение «Цены» и количество «Всего» проданного товара. Мы можем использовать формулу:

=B2*C2

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

=[@Цена]*[@Всего]

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

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

В умную таблицу можно вставить «Строку итогов» – для этого кликните по любой ячейке таблички, перейдите на вкладку «Конструктора» и поставьте галочку «Строка итогов».

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

Сортировка

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

Как убрать умную таблицу Excel

Чтобы преобразовать умную таблицу в обычный вариант в Excel – выделите её и перейдите на вкладку «Конструктора» и в разделе «Инструменты» нажмите по кнопке «Преобразовать в диапазон».

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

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

Видео

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

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

Для создания умной таблицы в Excel необходимо предварительно кликнуть по любой ячейке внутри таблицы, затем перейти на вкладку Вставка – Таблица (или нажать сочетание клавиш Ctrl + T).

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

Готово, ваша таблица из обычной превратилась в умную.

Авторасширение умной таблицы

При добавлении строк/столбцов умная таблица будет автоматически расширяться и включать их в себя.

Такое поведение таблицы можно отключить. При добавлении новой строки/столбца около заполненной ячейки появится значок с возможностью выбора дополнительных опций. Кликните по нему и выберите пункт Отменить авторазвертывание таблицы.

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

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

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

Конвертация умной таблицы обратно в диапазон данных

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

Расписание ближайших групп:

Загружаю…