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

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

Промежуточные итоги и функция АГРЕГАТ / 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

Функция АГРЕГАТ / AGGREGATE, как и ПРОМЕЖУТОЧНЫЕ.ИТОГИ, может выполнять разные вычисления (операция тоже задается первым аргументом) и игнорировать другие функции АГРЕГАТ, что удобно, чтобы промежуточные итоги и расчеты не попадали в общую сумму. Что именно нужно пропускать, задается во втором аргументе функции АГРЕГАТ:

Работа с текстом

Файл с примерами: Текст. xlsx

ТЕКСТ В ЯЧЕЙКАХ EXCEL

Длина текстовой строки – это число символов (пробелов, цифр, букв, знаков, переходов на следующую строку).

Определить его можно функцией ДЛСТР / LEN.

Максимальная длина текста в одной ячейке Excel – 32 767 символов (на практике столько обычно и не нужно, это полноценная глава из книги по объему). В Google Таблицах ограничение еще больше – 50 000 символов.

В ячейках и формулах можно вставлять переход на следующую строку:

(Alt + Enter / ^ +  + Return). Такой переход – это отдельный символ (то есть ячейка с ним и без него не равны друг другу, даже если остальные символы совпадают).

ТЕКСТ В ФОРМУЛАХ

Текст в формулах можно:

• указывать прямо в формуле, и тогда он (текст) обязательно должен быть в кавычках;

• брать из других ячеек, и тогда нужно просто ссылаться на эти ячейки без кавычек.

То есть =A7 – это ссылка на ячейку A7, а вот ="A7" – это формула, которая будет возвращать текст из двух символов A7.

Апостроф (') в начале ячейки превращает ее содержимое в текст (формула перестает вычисляться, число становится текстом и т. д.).

Для объединения нескольких значений в одну текстовую строку используется амперсанд (&).

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

РАЗДЕЛЕНИЕ ТЕКСТА ПО СТОЛБЦАМ

Для разделения текста по столбцам используется инструмент «Текст по столбцам» (Text to Columns) из вкладки «Данные» (Data).

Выделите ячейки с текстом, который нужно разделить, и вызовите инструмент.

После выбора формата – с разделителем или фиксированной ширины – нужно будет указать разделитель или ширину (на скриншоте пример с разделителем).

В Google Таблицах этот инструмент называется «Разделить текст на столбцы» (Split text to columns) и находится в меню «Данные» (Data).

Кроме того, в Google Таблицах есть функция SPLIT, разделяющая текст (первый аргумент) по разделителю (второй аргумент).

А в Excel аналог SPLIT – TEXTSPLIT / ТЕКСТРАЗД – появился только в 2022 году у подписчиков Microsoft 365 (то есть этой функции нет даже в Excel 2021).

Вместе с ней в Excel появились функции TEXTBEFORE / ТЕКСТДО и TEXTAFTER / ТЕКСТПОСЛЕ, извлекающие текст до/после разделителя.

Если «Разделить текст на столбцы» разделяет данные как значения (один раз – при изменении исходных данных ничего не произойдет), то SPLIT или ТЕКСТРАЗД, будучи функцией, не влияют на исходный столбец, и результат ее вычисления будет пересчитываться при изменении исходных данных.

Если вам нужно разделить по переносу строк в Excel, введите в качестве разделителя Ctrl + J.

В Google Таблицах: введите в качестве разделителя в функции SPLIT другую функцию – СИМВОЛ(10) / CHAR(10). Эта функция возвращает разные символы по их кодам – коду 10 соответствует перенос строки.

ОЧИСТКА ТЕКСТА

«Найти и заменить»

Инструмент «Найти и заменить» (Find and Replace) во многих случаях помогает решить задачи по обработке текстовых значений (и не только) без применения сложных функций и формул. Это окно позволяет исправить большое количество формул, поменять форматирование всех однотипных ячеек, удалить определенные слова или символы из диапазона или из всей книги Excel.

Его можно вызвать сочетаниями клавиш Ctrl + F ( + F) или Ctrl + H (^ + H) – в обоих случаях откроется одно и то же диалоговое окно, но в первом случае на вкладке «Найти» (Find), а во втором – «Заменить» (Replace).

Если вы предварительно выделили диапазон ячеек, то поиск/замена будут производиться в пределах этого диапазона. Если же нет, то на листе или в книге (изменить этот параметр можно в поле «Искать» (Within) в окне «Найти и заменить»; по умолчанию будет лист).

Удаление и замена символов/слов

Чтобы удалить любой символ или слово из диапазона или всей книги, просто укажите его в поле «Найти» (Find what) и заменяйте его на ничто, то есть оставьте поле «Заменить» (Replace with) пустым.

Символы подстановки

В «Найти и заменить» (Find and Replace) в Excel можно использовать символы подстановки:

• * – любой текст, в том числе нулевой длины (то есть на месте звездочки может не быть ничего);

•? – один любой символ (на месте знака вопроса обязательно должен быть символ).

Например, если вам нужно удалить любой текст в скобках (вместе с самими скобками), то в поле «Найти» нужно ввести:

(*)

А если нужно удалить все скобки, в которых внутри слова строго из 4 букв (или 4 цифры, или же 4 любых символа), нужно указать четыре знака вопроса в скобках:

(????)

Если вам нужно найти именно звездочки или знаки вопроса (например, чтобы удалить все звездочки в какой-то таблице), поставьте перед символом тильду (~):

~* – поиск звездочки,

– ? – поиск знака вопроса,

~~ – поиск самой тильды.

Удаление переносов строк

В ячейках Excel и Google Таблиц можно переходить на новую строку – сочетание клавиш Alt + Enter. Это отдельный символ, а не визуальный перенос, который задается с помощью параметра «Переносить текст» (Wrap Text).

Если вам нужно удалить переносы строк, введите в поле «Найти» Ctrl + J.

Изменение формул с помощью окна «Найти и заменить»

Окно «Найти и заменить» позволяет произвести изменения сразу с большим количеством формул. Например, вам нужно поменять диапазон или функцию во многих формулах. Выделите диапазон с формулами, вызовите окно «Найти и заменить» и введите в поле «Найти» (Find what) тот фрагмент формул, который вы хотите изменить, а в «Заменить на» (Replace with) – то, на что хотите его изменить. Убедитесь, что в списке «Область поиска» (Look in) заданы «Формулы» (Formulas).

Допустим, у вас есть несколько формул с расчетом сумм с условиями – через функцию СУММЕСЛИМН / SUMIFS. Вы решили изменить все расчеты на вычисление среднего – это делает функция с таким же синтаксисом, но другим названием – СРЗНАЧЕСЛИМН / AVERAGEIFS. Чтобы в целом диапазоне с формулами изменить все функции СУММЕСЛИМН на СРЗНАЧЕСЛИМН, достаточно будет следующих манипуляций в окне «Найти и заменить»:

Обратите внимание, что достаточно было бы заменять «СУММ» на «СРЗНАЧ». Но только в том случае, если вы уверены, что в выделенном диапазоне нет отдельных функций СУММ, которые в таком случае бы заменились на функции СРЗНАЧ. В нашем случае мы точно уверены, что будут изменяться только функции СУММЕСЛИМН.

Удаление пробелов

Для удаления лишних пробелов (в начале, в конце и всех, кроме одного между слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент – текст (ссылка на ячейку с текстом, как правило).

Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите.

Удаление непечатаемых символов

Для удаления непечатаемых символов (табуляция, перенос строки, другие символы, которые не отображаются на экране) используйте функцию ПЕЧСИМВ / CLEAN.

ФУНКЦИИ ДЛЯ ИЗМЕНЕНИЯ РЕГИСТРА ТЕКСТА

СТРОЧН / ПРОПИСН / ПРОПНАЧ (LOWER / UPPER / PROPER)

Меняют регистр текста на нижний (СТРОЧН), верхний (ПРОПИСН) или на каждое слово с заглавной (ПРОПНАЧ).

Единственный аргумент в каждом случае – текст (обычно заданный в виде ссылки на ячейку, чтобы протянуть формулу по всей таблице, но технически это может быть и текст в кавычках, и результат объединения текста из нескольких ячеек/функций).

TEXTJOIN / ОБЪЕДИНИТЬ

Объединяет все значения из диапазона в один текст через указанный в первом аргументе разделитель. Второй аргумент – «пропускать пустые». Если он равен ИСТИНА, то не будут вставляться два подряд разделителя при наличии в диапазоне пустых ячеек.

Функция появилась только в Excel 2019.

В Google Таблицах она есть и называется TEXTJOIN (нет названия на русском).

ФУНКЦИИ ДЛЯ ИЗВЛЕЧЕНИЯ ФРАГМЕНТОВ ТЕКСТОВОЙ СТРОКИ

ЛЕВСИМВ / ПРАВСИМВ / ПСТР (LEFT / RIGHT / MID)

Извлекают из текста заданное число символов (из начала, конца или середины соответственно).

У функций ЛЕВСИМВ / LEFT и ПРАВСИМВ / RIGHT:

• первый аргумент – текст;

• второй аргумент – число символов (если его пропустить, то будет извлекаться один).

Если вам нужно извлечь фиксированное количество символов из ячейки в начале или конце строки – эти функции подойдут.

У функции ПСТР / MID:

• первый аргумент – текст;

• второй аргумент – с какой позиции в тексте извлекаются символы;

• третий аргумент – число символов.

ФУНКЦИИ ДЛЯ ПОИСКА ПОЛОЖЕНИЯ СИМВОЛА/ПОДСТРОКИ В ТЕКСТЕ

ПОИСК / НАЙТИ

(SEARCH / FIND)

Находят положение символа или текста в текстовой строке (ПОИСК – без учета регистра, НАЙТИ – с учетом):

• первый аргумент – что ищем;

• второй аргумент – где ищем;

• третий (необязательный) – с какого символа в тексте начинаем поиск.

ПСТР / MID можно использовать в сочетании с функциями поиска. Допустим, нам нужно извлечь цифровую часть артикула.

ПРАВСИМВ уже не подойдет, потому что у некоторых артикулов справа есть «хвост» из дефиса и букв.

ПСТР с фиксированным положением не подойдет: цифры идут после косой черты, и это всегда разная позиция в тексте – иногда 5, иногда 6, иногда 7, в зависимости от числа букв до черты.

Значит, нужно сначала находить положение косой черты с помощью функций НАЙТИ или ПОИСК. К этому результату можно добавить единицу, чтобы получить позицию первой цифры.

=НАЙТИ("/";ячейка с текстом) + 1

А далее подставим эту позицию в функцию ПСТР / MID в качестве начальной позиции:

=ПСТР(ячейка с текстом; НАЙТИ("/";ячейка) + 1; число извлекаемых символов)

ФУНКЦИЯ ПОДСТАВИТЬ / SUBSTITUTE

Заменяет один текст или символ на другой:

• первый аргумент – текст;

• второй аргумент – что в нем заменяем;

• третий аргумент – на что мы это заменяем.

Ее можно использовать для очистки текста от каких-то символов/значений. Если третий аргумент пустой (пустые кавычки), то мы просто удаляем подстроки, соответствующие второму аргументу.

ФУНКЦИЯ TEXT / ТЕКСТ ДЛЯ ФОРМАТИРОВАНИЯ ЧИСЕЛ

При объединении нескольких значений в одну текстовую строку (например, когда вы хотите к какой-то надписи добавить результат вычисления функции) исходные числовые форматы не сохраняются. Это значит, что у чисел не будет разделителей групп разрядов, будут все знаки после запятой (если они есть), даты превратятся в числа и так далее.

В таких случаях можно использовать функцию ТЕКСТ / TEXT, которая превращает значение (первый аргумент) в текстовую строку в заданном вами формате (во втором аргументе):

=ТЕКСТ(значение, которое нужно отформатировать; код формата)

Код формата указывается в кавычках. Это те же самые пользовательские форматы – во всем, кроме кодов цветов в квадратных скобках (цвета в функции ТЕКСТ не будут работать).

КАК ПРЕВРАТИТЬ ЧИСЛО В ТЕКСТОВОМ ФОРМАТЕ В НАСТОЯЩЕЕ ЧИСЛО

Если функция возвращает текст, то результат вычисления будет текстовым, даже если состоит только из цифр. Например, если мы извлекаем суммы из текстовой строки, они не будут готовы к употреблению сразу – это будут текстовые значения, пусть и похожие на числа. См. сумму в столбце B.

Там ноль, хотя внешне вроде бы числа извлеклись правильные.