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

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

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

(*)

А если нужно удалить все скобки, в которых внутри слова строго из 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 можно использовать в сочетании с функциями поиска. Допустим, нам нужно извлечь цифровую часть артикула.

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