Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов
Короткий вариант — подсчет всех видимых строк (отфильтрованных) и скрытых вручную (через скрытие или группировку) строк.
Длинный вариант — подсчет только отфильтрованных строк, без скрытых вручную.
Если внутри диапазона уже есть другие функции 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 любых символа), нужно указать четыре знака вопроса в скобках:
(????)
Если вам нужно найти именно звездочки или знаки вопроса (например, чтобы удалить все звездочки в какой-то таблице), поставьте перед символом тильду (~):
~* — поиск звездочки,
— ? — поиск знака вопроса,
~~ — поиск самой тильды.
