Умные Таблицы Excel – секреты эффективной работы

В MS Excel есть много отличных инструментов, о которых большинство пользователей либо не знают, либо сильно недооценивают. К ним относятся таблицы Excel. Вы хотите сказать, что весь Excel представляет собой электронную таблицу? Нет. Рабочая область листа — это просто набор ячеек. Одни полные, другие пустые, но по сути и функциональности все они одинаковы.

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

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

доступен обычный диапазон данных о продажах.

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

Есть горячая клавиша Ctrl + T.

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

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

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

Структура и ссылки на Таблицу Excel

У каждой таблицы есть собственное имя. Это можно увидеть на вкладке «Дизайн», которая появляется, когда вы выбираете любую ячейку в таблице. По умолчанию это будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько таблиц, имеет смысл дать им более описательные имена. Это упростит их использование в дальнейшем (например, при работе в Power Pivot или Power Query). Я изменю заголовок на «Отчет». Таблица «Отчет» отображается в Диспетчере имен диспетчера имен форм.

А также при вводе формулы вручную.

Но самое интересное, что Excel видит не только всю таблицу, но и отдельные ее части: столбцы, заголовки, итоги и т.д. Ссылки выглядят так.

= Отчет [#All] — для всей таблицы
= Отчет [#Data] — только для данных (без строки заголовка)
= Отчет [# заголовков] — только в первой строке заголовков
= Отчет [# итогов] — для итогов
= Отчет [@] — для всей текущей строки (где вводится формула)
= Отчет [Продажи] — для всего столбца «Продажи»
= Отчет [@Sales] — по ячейке из текущей строки столбца «Продажи»

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

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

Если в какой-то ячейке вы напишите формулу для добавления ко всему столбцу «Продажи»

= СУММ (D2: D8)

тогда он будет автоматически преобразован в

= [Продажи] соотношение

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

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

А теперь о том, как таблицы облегчают жизнь и работу.

Свойства Таблиц Excel

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

2. Если таблица большая, при прокрутке вниз имена столбцов таблицы заменяют имена столбцов листа.

это очень удобно, специально ремонтировать участки не нужно.

3. По умолчанию в таблицу добавлен автоматический фильтр, который можно отключить в настройках. Подробнее об этом ниже.

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

5. Новые столбцы также будут автоматически включены в таблицу.

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

Помимо указанных свойств, вы можете сделать дополнительные настройки.

Настройки Таблицы

Контекстная вкладка «Дизайн» содержит дополнительные инструменты и настройки анализа.

Использование флажков в группе параметров стиля таблицы

вы можете внести следующие изменения.

— Удалить или добавить строку заголовка

— Добавить или удалить строку с итогами

— Создать формат чередующихся строк

— Выделите первый столбец жирным шрифтом

— Выделите последний столбец жирным шрифтом

— Выполнять наполнение поочередно

— Удалить автоматический фильтр по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

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

Однако самое интересное — это нарезка.

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

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

Для фильтрации таблиц выберите интересующую категорию.

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

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

Ограничения Таблиц Excel

Несмотря на неоспоримые преимущества и колоссальные возможности, Excel Spreadsheet имеет недостатки.

1. Визуализации не работают. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки / столбцы и другие).

2. Текущая книга не может быть опубликована.

3. Невозможно вставить промежуточные итоги.

4. Формулы массива не работают.

5. Ячейки не могут быть объединены. Правда, делать это в обычном промежутке не стоит.

Однако на фоне свойств и возможностей Таблиц эти недостатки практически не заметны.

Вы найдете еще много секретов Excel в онлайн-курсе.