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

— коротком (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 любых символа), нужно указать четыре знака вопроса в скобках:

(????)

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

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

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

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