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

Функция АГРЕГАТ / 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 можно использовать символы подстановки:

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