Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов

Как создать таблицу:

• сочетание клавиш 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), но это инструмент форматирования (разные цвета у чередующихся строк), не более того.

Промежуточные итоги и функция АГРЕГАТ / AGGREGATE

Промежуточные итоги

Если мы установим фильтр, выберем определенные строки и после этого применим Автосумму (Alt + = или на ленте на вкладке «Формулы») — будет введена не функция СУММ / SUM, а ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL. Она же вводится автоматически в таблицах, о которых мы говорили выше, в строке итогов.

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

У нее такой синтаксис:

=SUBTOTAL(номер функции; диапазон; [еще диапазон];…)

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

Вот базовые функции (кроме них, есть еще стандартное отклонение и дисперсия):

• 1 и 101 — среднее;

• 2 и 102 — количество чисел;

• 3 и 103 — количество значений;

• 4 и 104 — максимум;

• 5 и 105 — минимум;

• 6 и 106 — произведение;

• 9 и 109 — сумма.

Каждая функция бывает в двух вариантах