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

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

В ячейках 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.