Как создать таблицу:
• сочетание клавиш 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 — сумма.
Каждая функция бывает в двух вариантах — коротком (9 или 11, например) и длинном из трех цифр (109 или 111).
Короткий вариант — подсчет всех видимых строк (отфильтрованных) и скрытых вручную (через скрытие или группировку) строк.
Длинный вариант — подсчет только отфильтрованных строк, без скрытых вручную.
Если внутри диапазона уже есть другие функции SUBTOTAL, такие вложенные подытоги не будут учитываться. То есть задвоения в таком случае не будет.
Для столбцов функция работать не будет. То есть если применить ее к горизонтальному диапазону и скрыть столбцы, то они все равно попадут в расчет при любом коде функции.
В Google Таблицах функция тоже есть, и у нее аналогичный синтаксис.
Функция АГРЕГАТ / AGGREGATE, как и ПРОМЕЖУТОЧНЫЕ.ИТОГИ, может выполнять разные вычисления (операция тоже задается первым аргументом) и игнорировать другие функции АГРЕГАТ, что удобно, чтобы промежуточные итоги и расчеты не попадали в общую сумму. Что именно нужно пропускать, задается во втором аргументе функции АГРЕГАТ:
Работа с текстом
Файл с примерами: Текст.xlsx
Длина текстовой строки — это число символов (пробелов, цифр, букв, знаков, переходов на следующую строку).
Определить его можно функцией ДЛСТР / LEN.
Максимальная длина текста в одной ячейке Excel — 32 767 символов (на практике столько обычно и не нужно, это полноценная глава из книги по объему). В Google Таблицах ограничение еще больше — 50 000 символов.
В ячейках и формулах можно вставлять переход на следующую строку:
(Alt + Enter / ^ + + Return). Такой переход — это отдельный символ (то есть ячейка с ним и без него не равны друг другу, даже если остальные символы совпадают).
Текст в формулах можно:
• указывать прямо в формуле, и тогда он (текст) обязательно должен быть в кавычках;
• брать из других ячеек, и тогда нужно просто ссылаться на эти ячейки без кавычек.
То есть =A7 — это ссылка на ячейку A7, а вот ="A7" — это формула, которая будет возвращать текст из двух символов A7.
Апостроф (') в начале ячейки превращает ее содержимое в текст (формула перестает вычисляться, число становится текстом и т. д.).
Для объединения нескольких значений в одну текстовую строку используется амперсанд (&).
Его можно использовать и чтобы соединить данные из нескольких ячеек, и чтобы объединить какой-то фиксированный текст и значение из ячейки: например, дату или сумму и подпись к ним.