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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

О чем речь? Умная таблица в Excel – это особый способ форматирования, который позволяет получить больше возможностей для работы с упорядоченными данными. С ее помощью вы быстрее найдете нужную информацию, а вычисления станут гораздо проще.

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

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

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

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

Задачи и правила работы с умной таблицей

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

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

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

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

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

Excel 13 версии (Excel 2007) учитывает эти требования и предлагает пользователю опцию «умной таблицы», которая значительно облегчает или полностью автоматизирует их выполнение. Опытным юзерам эта функция сильно облегчает работу.

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

Чтобы включить режим «умной таблицы» в Excel, необходимо сделать следующее.

• В окне Excel, в котором открыта ранее сохраненная таблица или введены нужные данные, открываем вкладку «Главная», находим на ней раздел «Стили». В нем имеется кнопка «Форматировать как таблицу».
• На экране отобразится окно, где будет написан адрес таблицы, к которой применяется рассматриваемая функция. Если адрес выбран правильно, нужно кликнуть по кнопке «ОК».
• Для удобства не будет лишним задать для нее уникальное название, это поможет избежать путаницы. Для этого нужно кликнуть по любой ячейке выбранного диапазона и открыть вкладку «Конструктор». На ней есть раздел «Свойства», где помимо названия таблицы можно также изменить и ее размер.

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

Настройка умной таблицы в Excel

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

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

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

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

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

Автоматическая шапка умной таблицы

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

Скачать
файл

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

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

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

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

Вставка итоговых величин для колонок умной таблицы

Режим фильтров или срезов показывает итог по отображаемым на экране значениям. Это позволяет оперативно получить обобщенные данные по выбранным позициям.

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

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

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

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

Перемещение строк или колонок

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

Если нужно переместить строку, то порядок действий аналогичен.

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

Удаление дубликатов

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

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

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

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

Сводные таблицы

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

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

Единственное, что необходимо сделать – это обновить сводную таблицу, чтобы учесть изменения в умной таблице.

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

Аналогичные инструменты можно применять и к диаграммам.

Диаграммы и графики

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

Режим умных таблиц позволяет автоматически подхватывать вновь введённые данные и отобразить их в разделах диаграммы. Это освобождает пользователя от необходимости самостоятельно следить за изменениями диапазона.

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