Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)
Назад: Функции рабочего листа Excel
Дальше: Промежуточные итоги и функция АГРЕГАТ / AGGREGATE
Таблицы Excel (Tables)
Файл с примерами: Таблицы.xlsxТаблица Excel (Tables) — это диапазон (область), у которого есть имя, границы (саморасширяющиеся при добавлении данных снизу и справа), на который можно ссылаться из формул по именам этого диапазона (таблицы) и его столбцов. Таблицы появились в Excel 2007 (до этого они назывались списками и имели меньший функционал).
Конечно, тут может возникнуть непонимание: что это за «таблица» внутри Excel, когда весь Excel и есть табличный редактор и мы привыкли воспринимать диапазоны в нем как таблицы (и называть так же)?
Возможно, поэтому в русскоязычном пространстве прижилось название «умные таблицы» — они действительно имеют много преимуществ и заслуживают такого названия. Так что в обучающих видео, публикациях и книгах на русском вы можете встретить такое название, но в Excel это просто «таблица» (Table), а несколько обычных ячеек, не превращенных в таблицу, — это диапазон (Range).
Как создать таблицу:
• сочетание клавиш Ctrl + T (^ + T) или Ctrl + L (^ + L);
• лента: Главная → Форматировать как таблицу (Home → Format as Table);
• лента: Вставка → Таблица (Insert → Table).
При создании через сочетание клавиш у вас не будет возможности выбрать стиль таблицы, как при создании с ленты, где все стили представлены наглядно.
Но можно выбрать тот стиль, который будет применяться к таблицам автоматически при создании через горячие клавиши: просто выберите стиль в списке на ленте и щелкните правой кнопкой → «По умолчанию» (Set As Default).
У таблиц много преимуществ, главное из которых можно охарактеризовать так: таблица — это именованная область рабочего листа Excel, границы которой расширяются автоматически при добавлении новых данных (а в рамках этих границ автоматически продлеваются формулы, форматирование, проверка данных).
С таблицами можно не переживать о том, протянутся ли формулы в новых строках, попадут ли все новые данные в сводную таблицу, сохранятся ли форматирование и проверка данных в новых строках и так далее.
При перемещении по листу заголовки таблицы (из первой строки) будут заменять собой стандартные заголовки столбцов A, B, C — такое встроенное закрепление первой строки.
Добавлять новые строки и столбцы в таблицу можно, дописывая данные внизу и справа. Но, если в таблице есть строка итогов (Total Row), просто дописать строки не получится. В таком случае имеются несколько вариантов.
• Щелчок правой кнопкой на номере строки → Вставить (Insert).
• Щелчок правой кнопкой на ячейке таблицы → Вставить → Вставить строки таблицы (Insert → Table Rows).
• Лента: Главная → Вставить → Вставить строки таблицы (Home → Insert → Insert Table Rows).
В таблицах при добавлении данных автоматически продлеваются:
• чередование строк и/или столбцов, если оно есть;
• формулы;
• числовое и стилевое форматирование в ячейках таблицы;
• проверка данных;
• если вы строите диаграмму или спарклайн на основании данных из таблицы, она тоже будет автоматически обновляться при добавлении новых строк в таблицу.
На таблицы можно ссылаться в формулах извне (из ячеек, не лежащих в пределах таблицы, в том числе находящихся на других листах) — по названию таблицы и названиям столбцов. Синтаксис этих ссылок следующий (здесь «Таблица» — название таблицы).
Ссылка на все данные (без заголовков и строки итогов):
Таблица
Ссылка на все данные в таблице, включая заголовки и строку итогов:
Таблица[#Все]
Таблица[#All]
Ссылка только на строку итогов:
Таблица[#Итоги]
Таблица[#Headers]
Ссылки на отдельные столбцы в таблице
Ссылка на все данные в столбце (без заголовка). Такую ссылку можно при вводе формулы сформировать очень легко, выделив любую ячейку в столбце (кроме итогов и заголовков) и нажав Ctrl + пробел.
Таблица[название_столбца]
Ссылка на ячейку с итогами в конкретном столбце:
Таблица[[#Итоги];[название_столбца]]
Таблица[[#Totals];[название_столбца]]
Ссылка на ячейку с заголовком в столбце:
Таблица[[#Заголовки];[название_столбца]]
Таблица[[#Headers];[название_столбца]]
Cсылка на все данные в столбце (с итогами и заголовками):
Таблица[[#Все];[название_столбца]]
Таблица[[#All];[название_столбца]]
Ссылка на диапазон (несколько столбцов) в таблице. Через двоеточие указываются первый (левый) и последний (правый) столбец в диапазоне:
Таблица[[Первый_столбец]: [Последний столбец]]
Ссылка такого вида, как и ссылка на отдельный столбец, тоже может быть только на заголовки, на итоги, на все вместе или только на данные.
В самих таблицах в формулах можно использовать и обычные ссылки на ячейки, и ссылки на столбцы, в том числе ссылку на значение из определенного столбца в той же (текущей) строке, что и формула, — в такой ссылке добавляется символ @:
[@[название_столбца]]
Срезы — удобные и наглядные фильтры, которые находятся на графическом слое листа Excel (то есть «плавают» поверх ячеек) — появились в Excel 2010 и доступны как в таблицах, так и в сводных таблицах (Pivot Tables).
Когда у вас есть таблица, при активации любой ее ячейки появляется контекстная вкладка ленты «Конструктор таблиц» (Table Design) — на ней и можно вставить срез (Insert Slicer).
После нажатия кнопки появится список столбцов — выбираем, по каким хотим фильтровать.
Допустим, мы выбрали два — «Продукт» и «Канал». Появятся два среза, и можно фильтровать данные.
Пока ничего в срезах не выбрано, отображаются все строки таблицы.
Выберем один продукт и увидим, что в срезах сразу видна связь: если какое-то значение при фильтрации стало бледным (но не белым — так выглядят исключенные нами из фильтрации значения), значит, в текущей выборке это значение не встречается.
У срезов есть своя контекстная вкладка на ленте. Там можно менять внешний вид среза, а в настройках можно поменять заголовок (ну не нравится вам, как называется столбец в таблице, тут можно назвать иначе) и сортировку.
А еще на этой вкладке можно изменить число столбцов в срезе. Пригодится, если вам надо сделать срез горизонтальной ориентации или просто значений много и в один столбец они не помещаются.
В правом верхнем углу среза есть две кнопки: возможность выбора нескольких элементов (Alt + S; в старых версиях кнопки нет, но всегда можно зажать Ctrl и выделить несколько объектов или снять выделение с некоторых) и очистка фильтра (Alt + C).
Чтобы удалить срез, выделите его и нажмите Delete.
В Google Таблицах нет аналога таблиц Excel. Есть чередование строк (Alternating colours), но это инструмент форматирования (разные цвета у чередующихся строк), не более того.
