Как сделать умную таблицу в Excel: урок от Бородача

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

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

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

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

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

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

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

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

Ctrl + T

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

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

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

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

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

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

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

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

=B2*C2

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

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

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

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

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

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

Сортировка

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

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

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

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

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

Видео

YouTube