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

Таблица[[#Заголовки];[название_столбца]]

Таблица[[#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 — сумма.

Каждая функция бывает в двух вариантах — коротком (9 или 11, например) и длинном из трех цифр (109 или 111).

Короткий вариант — подсчет всех видимых строк (отфильтрованных) и скрытых вручную (через скрытие или группировку) строк.

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

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

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

В Google Таблицах функция тоже есть, и у нее аналогичный синтаксис.

Функция АГРЕГАТ / AGGREGATE