ТЕКСТОВЫЕ ФУНКЦИИ
В этом разделе мы рассмотрим основные текстовые функции.
На скриншоте ниже вы увидите четыре следующих столбца:
1) название функции;
2) исходный текст;
3) текст после применения функции (ячейка с формулой);
4) текст самой формулы.
TRIM (СЖПРОБЕЛЫ) удаляет лишние пробелы между словами, а также пробелы до первого и после последнего слова в тексте. Формула оставляет лишь по одному пробелу между словами. Единственный аргумент — ячейка с текстом (можно указать текст в кавычках прямо в самой формуле, но на практике такое вряд ли понадобится).
UPPER (ПРОПИСН) и LOWER (СТРОЧН) меняют регистр текста на верхний и нижний соответственно. У них тоже один аргумент.
Функция LEN (ДЛСТР) определяет длину текста. Учитываются все символы, включая пробелы. Ее можно использовать как формулу массива — см. . В таком случае удастся посчитать сумму длин текстов из целого диапазона ячеек.
Функция FIND определяет позицию вхождения слова или символа в текст (в Excel есть два аналога — НАЙТИ учитывает регистр, ПОИСК — нет).
Первый аргумент — текст, который мы ищем; второй — ячейка с текстом, в котором будем искать; третий — необязательный — аргумент: позиция начала поиска. Искать можно не с начала. В примере слово «кефир» в исходном тексте стоит на 26-й позиции.
Функция SUBSTITUTE (ЗАМЕНИТЬ) меняет в тексте одно слово (символ, текст) на другое.
Например, функция ниже:
=SUBSTITUTE("пробежал я давеча марафон за 3:15";"3:15";"2:55")
выдаст такой приятный результат:
пробежал я давеча марафон за 2:55
Функция TEXT (ТЕКСТ) нужна в тех случаях, когда с помощью формул вы составляете текстовую фразу, в которой используются числовые значения или даты из ячеек вашей таблицы.
В примере:
="сегодня "&B13
выдает дату в неотформатированном виде, то есть как число:
сегодня 42413
А функция TEXT (ТЕКСТ):
="сегодня "&TEXT(B14;"DD/MM/YYYY")
="сегодня "&ТЕКСТ(B14;"DD/MM/YYYY")
позволяет получить красивый результат:
сегодня 13/02/2016
Функции LEFT (ЛЕВСИМВ) и RIGHT (ПРАВСИМВ) вырезают из текста (первый аргумент) определенное количество знаков (второй аргумент).
=LEFT("Слово";3) = Сло
=ЛЕВСИМВ("Слово";3) = Сло
Функция MID (ПСТР) вырезает из текста заданное количество символов, начиная с определенной позиции (второй аргумент):
=MID("Машина";3;2) = ши
=ПСТР("Машина";3;2) = ши
Функция SPLIT (аналог в Excel — инструмент «Текст по столбцам», вкладка «Данные» на панели) разделяет текст (первый аргумент) по определенному символу (второй аргумент).
Разделитель нужно указать в кавычках, ибо это текст (в примере это пробел):
=SPLIT(B21;" ")
Функция SPLIT возвращает результаты сразу в несколько ячеек — по столбцам:
Если вам нужно ее использовать внутри другой функции (например, внутри ВПР (VLOOKUP)), учитывайте, что в таком случае по умолчанию будет возвращаться только первое слово из исходного текста.
РАЗБИВАЕМ ТЕКСТ ПО СТОЛБЦАМ
Как и в Excel, в Таблицах можно разбивать текст по столбцам. Эта функция находится в разделе меню Данные и называется Разделить на колонки:
Разделим фамилии, имена и отчества из одного столбца на три:
Для этого выделим данные, нажмем на пункт меню Разделить на колонки… и выберем разделитель в появившемся списке (в нашем случае разделитель — Пробел):
Результат:
КЕЙС «КАК ПОЛУЧИТЬ ИЗ ЯЧЕЙКИ С АДРЕСОМ НОМЕР ДОМА ИЛИ НАЗВАНИЕ УЛИЦЫ»
Допустим, у вас есть ячейки с адресами в более-менее одинаковом формате:
м. Чист. Пруды, Потаповский пер., д. 7, стр. 2, оф. 523
197371, г. Санкт-Петербург, Комендантский пр., д. 53, к. 2, кв. 984
Можно использовать сочетание текстовых функций, чтобы получить в отдельной ячейке номер дома или название улицы (и любые другие данные — лишь бы их можно было найти в исходной ячейке по какому-нибудь признаку, например по букве «д» с точкой в случае с номером дома).
Итак, рассмотрим формулы для двух случаев (предполагается, что исходный текст с адресом в ячейке A1):
Номер дома (число, следующее за буквой д с точкой):
=IFERROR (MID(A1;FIND("д. ";A1)+3;find(",";A1;FIND("д. ";A1))-FIND("д. ";A1)-3);"")
=ЕСЛИОШИБКА(ПСТР(A1;НАЙТИ("д. ";A1)+3;НАЙТИ(",";A1;НАЙТИ("д. ";A1))-НАЙТИ("д. ";A1)-3);"")
Название улицы (слово, следующее за буквами ул с точкой):
=ЕСЛИОШИБКА(ПСТР(A1;НАЙТИ("ул. ";A1)+4;НАЙТИ(",";A1;НАЙТИ("ул. ";A1))-НАЙТИ("ул. ";A1)-4);"")
И в том, и в другом случае все начинается с IFERROR (ЕСЛИОШИБКА) — если в исходном тексте не окажется нужных символов или он будет пустым, в ячейке с формулой ошибка не отобразится.
Далее мы вырезаем нужную нам информацию, используя функцию MID (ПСТР).
С какого места вырезать, определим с помощью функции FIND (НАЙТИ), которая ищет позицию элемента д. или ул. в ячейке с текстом.
Прибавляя +3 или +4 в примерах, мы корректируем количество символов, которые вырежет функция ПСТР ("д. " — три символа с точкой и пробелом; "ул. " — четыре символа с точкой и пробелом).
ПОДСЧЕТ НЕПУСТЫХ ЯЧЕЕК С ТЕКСТОМ
Следующая функция позволяет подсчитать количество непустых текстовых ячеек в диапазоне:
=COUNTIF(диапазон;">""")
=СЧЁТЕСЛИ(диапазон;">""")
Расшифруем условие:
сочетание символов >"" значит больше пустоты — не равно текстовой строке нулевой длины.
КАК РАССЧИТАТЬ КОЛИЧЕСТВО ВХОЖДЕНИЙ ТЕКСТА A В ТЕКСТ B
В данном примере наша задача — выяснить, сколько раз в каждой из ячеек столбца А встречается искомый текст, расположенный в ячейке E1:
Для решения этой задачи можно воспользоваться сложной формулой, состоящей из следующих функций:
- ДЛСТР (LEN) — вычисляет длину текста, единственный аргумент — текст. Пример: ДЛСТР("машина") равно 6.
- ПОДСТАВИТЬ (SUBSTITUTE) — заменяет в текстовой строке определенный текст другим.
Синтаксис:
SUBSTITUTE(текст; стар_текст; нов_текст)
ПОДСТАВИТЬ(текст; стар_текст; нов_текст)
Пример:
SUBSTITUTE("автомобиль";"авто";"") = мобиль
ПОДСТАВИТЬ("автомобиль";"авто";"") = мобиль
- ПРОПИСН (UPPER) — заменяет все символы в строке на прописные. Единственный аргумент — текст. Пример: ПРОПИСН("машина") равно МАШИНА. Эта функция понадобится нам, чтобы делать поиск без учета регистра. Ведь ПРОПИСН("машина") равно ПРОПИСН("Машина").
Чтобы найти вхождение определенной текстовой строки в другую, нужно удалить все ее вхождения в исходный текст и сравнить длину полученной строки с исходной:
LEN("Тариф МТС Супер МТС")-LEN("Тариф Супер") = 8
ДЛСТР("Тариф МТС Супер МТС")-ДЛСТР("Тариф Супер") = 8
А затем разделить эту разницу на длину той строки, которую мы искали:
8 / LEN ("МТС") = 2,6
8 / ДЛСТР ("МТС") = 2,6
Именно два раза (мы получили две целых) строка «МТС» входит в исходную.
Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» — тот, число вхождений которого нас интересует):
=(LEN(текст)-LEN(SUBSTITUTE(UPPER(текст);UPPER(искомый)"")))/LEN(искомый)
=(ДЛСТР(текст)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(текст);ПРОПИСН(искомый);"")))/ДЛСТР(искомый)
В нашем примере формула выглядит следующим образом:
=(LEN(A2)-LEN(SUBSTITUTE(UPPER(A2);UPPER($E$1);"")))/LEN($E$1)
=(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A2);ПРОПИСН($E$1);"")))/ДЛСТР($E$1)
КАК СТРОИТЬ ШАБЛОННЫЕ ФРАЗЫ АВТОМАТИЧЕСКИ?
Используя базовые принципы работы с текстом и несколько простых функций, можно готовить шаблонные фразы для отчетов. Несколько принципов работы с текстом:
- Текст объединяется с помощью знака & (амперсанд).
- Текст всегда записывается в кавычках, ссылки на ячейки с текстом — всегда без.
- Чтобы получить символ «кавычки» (который нельзя использовать напрямую — он служебный и необходим для ввода текста в формулах), применим функцию СИМВОЛ (CHAR) с аргументом 34.
Пример создания шаблонной фразы с помощью формул:
Формула:
="Продажи книги "& CHAR(34)&A3&CHAR(34)&IF(D3>0;" выросли на ";" упали на ")&TEXT(D3;"#,0%;#,0%")
="Продажи книги "&СИМВОЛ(34)&A3&СИМВОЛ(34)&ЕСЛИ(D3>0;" выросли на ";" упали на ")&ТЕКСТ(D3;"#,0%;#,0%")
В данном случае, кроме функции СИМВОЛ (CHAR), для отображения кавычек используются функция ЕСЛИ (IF), изменяющая текст в зависимости от динамики продаж, и функция ТЕКСТ (TEXT), отображающая число в любом формате. Ее синтаксис описан ниже:
ТЕКСТ(значение; формат)
Формат указывается в кавычках точно так же, как если бы вы вводили пользовательский формат в окне Формат ячеек.
Разберем формулу по фрагментам:
"Продажи книги " — текст (заметьте, что мы взяли в кавычки пробел после словосочетания);
&CHAR(34) — кавычки;
&A3 — название книги из ячейки A3;
&CHAR(34) — кавычки;
&IF(D3>0;" выросли на ";" упали на ") — текст, который зависит от числа в ячейке D3 (если положительное — то продажи «выросли», иначе — «упали»);
&TEXT(D3;"#,0%;#,0%") — значение прироста в процентном формате с одним знаком после запятой и без знака «минус» для отрицательных значений.
Ренат: Автоматизировать можно и более сложные тексты. В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчетности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать — стоит один раз озадачиться созданием формулы или скрипта (или макроса в Excel), которые избавят вас хотя бы от части работы.
ИСПРАВЛЯЕМ РЕГИСТР: ПРЕДЛОЖЕНИЕ С ЗАГЛАВНОЙ БУКВЫ
В начале этой главы мы писали про функции UPPER и LOWER, позволяющие менять регистр. Давайте разберемся, как применять их для исправления регистра предложения — оставим прописной только первую букву, сделав строчными все остальные.
Для этого возьмем первый символ из ячейки:
LEFT(ячейка; 1)
ЛЕВСИМВ(ячейка; 1)
Переведем его в верхний регистр:
UPPER(LEFT(ячейка; 1))
ПРОПИСН(ЛЕВСИМВ(ячейка; 1))
А теперь возьмем весь остальной текст без первого символа:
RIGHT(ячейка; LEN(ячейка)-1)
ПРАВСИМВ(ячейка; ДЛСТР(ячейка)-1)
Переведем его в нижний регистр:
LOWER(RIGHT(ячейка; LEN(ячейка)-1))
СТРОЧН(ПРАВСИМВ(ячейка; ДЛСТР(ячейка)-1))
Объединим все в одну формулу:
=UPPER(LEFT(A1; 1))&LOWER(RIGHT(A1; LEN(A1)-1))
=ПРОПИСН(ЛЕВСИМВ(A1; 1))&СТРОЧН(ПРАВСИМВ(A1; ДЛСТР(A1)-1))
РЕГУЛЯРНЫЕ ВЫРАЖЕНИЯ
Регулярные выражения — это язык для поиска подстрок в тексте. С их помощью можно находить и извлекать определенные символы и подстроки, соответствующие заданному шаблону. В Google Таблицах есть несколько встроенных функций, позволяющих использовать регулярные выражения.
Мы не будем подробно раскрывать тему самих регулярных выражений и синтаксиса, но рассмотрим соответствующие функции Google Таблиц.
REGEXCTRACT
С помощью функции REGEXEXTRACT можно извлечь из текста в ячейке (первый аргумент) фрагмент текста, соответствующий регулярному выражению (второй аргумент).
Например:
=REGEXEXTRACT(A1;"[0-9]+")
извлекает из ячейки A1 число (если их несколько — то первое).
Если у вас есть ряд ячеек, в которых количественные данные указаны по-разному (то в виде числа, то в виде текста и числа), как в примере, то эта функция поможет извлечь только числа, чтобы в будущем их обрабатывать (суммировать и т. п.):
В примере мы сначала проверяем, является ли ячейка текстом; если да, то извлекаем значение с помощью REGEXEXTRACT (функция VALUE/ЗНАЧЕН конвертирует его в числовой формат), а если нет — просто возвращаем значение из ячейки.
Текст формулы:
=IF(istext(A2);VALUE(REGEXEXTRACT(A2;"[0-9]+"));A2)
=ЕСЛИ(ЕТЕКСТ(A2);ЗНАЧЕН(REGEXEXTRACT(A2;"[0-9]+"));A2)
REGEXMATCH
Функция REGEXMATCH не извлекает, а только проверяет текст из ячейки (первый аргумент) на соответствие шаблону (второй аргумент) и возвращает логические значения TRUE (ИСТИНА) и FALSE (ЛОЖЬ).
В примере мы будем проверять, есть ли в ячейках столбца A даты:
Текст функции:
=REGEXMATCH(A7;"\d{1,2}\.\d{1,2}\.\d{4}")
REGEXREPLACE
Функция REGEXREPLACE заменяет в ячейке (первый аргумент) текст, соответствующий шаблону (второй аргумент), на другой текст (третий аргумент).
В примере будем заменять слово «Яндекс» в разных падежах на слово Yandex:
Текст функции:
=REGEXREPLACE(A13;"Яндекс[^0-9]";"Yandex")
ПОДСЧЕТ ПУСТЫХ ЯЧЕЕК. СOUNTBLANK
Функция COUNTBLANK (СЧИТАТЬПУСТОТЫ) определяет количество пустых ячеек в диапазоне. Ее единственный аргумент — ссылка на диапазон:
КАК ВЫДЕЛИТЬ УНИКАЛЬНЫЕ ЭЛЕМЕНТЫ ИЗ СПИСКА В GOOGLE ТАБЛИЦАХ?
Дано: список с текстовыми значениями в Google Таблице. Задача: получить из исходного список, содержащий только уникальные значения.
Для этого понадобится функция UNIQUE, единственный аргумент которой — исходный список:
Если перед вами стоит задача вычислить количество уникальных элементов в списке, понадобится функция COUNTUNIQUE. Она работает аналогично UNIQUE, но возвращает лишь количество уникальных элементов.
Но что если исходный список со временем будет расширяться? Чтобы функция UNIQUE автоматически обновляла список уникальных значений при обновлении исходного (а COUNTUNIQUE, соответственно, обновляла количество), в качестве аргумента укажем открытый диапазон:
=UNIQUE($A$2:$A)
ФУНКЦИЯ JOIN: СОБИРАЕМ АДРЕС ИЗ РАЗНЫХ СТОЛБЦОВ
Функция JOIN позволяет объединить текст из разных ячеек, разделив его определенным символом.
Возьмем, например, фрагменты адреса из разных столбцов и соединим их в одной ячейке, используя запятую в качестве разделителя:
Синтаксис функции:
=JOIN(разделитель; текст или диапазон 1; [текст или диапазон 2];....)
В нашем примере разделителем выступает запятая с пробелом.
Мы можем получить две запятые подряд, если одна из ячеек строки будет пустовать. Этого можно избежать с помощью текстовой функции ПОДСТАВИТЬ (SUBSTITUTE):
=SUBSTITUTE(JOIN(", ";A2:D2);", ,";", ")
=ПОДСТАВИТЬ(JOIN(", ";A2:D2);", ,";", ").
Такая формула будет отлавливать и менять двойные запятые на одну.
