Недавно на одной фриланс-бирже видел задание - нужно написать формулу для эксель, которая будет "нежно" сокращать текст, не разрезая слово. И рядом с этим заданием была задача реализовать поиск по строке с обратной стороны. Расскажу подробнее в этой статье.
Задача 1
Нужно подготовить файл для загрузки на сайт, и удовлетворить ограничению по размеру строки. Например, 30 символов. Нужно сократить строку, но не грубо обрезав по длине 30 символов, а более аккуратно - убрав последнее слово. Например, строка "новая блог-платформа yandex zen" имеет длину 31 символ, и ее нужно сократить до "новая блог-платформа yandex".
Отбросим случаи, когда вся строка - это одно слово и когда первое слово длиннее 30 символов.
Чтобы сделать то, что требуется, нужно обрезать строку до 30 символов, найти самый последний пробел в строке. К сожалению, функция ПОИСК (FIND) умеет искать только в прямом направлении, и хотя она может искать начиная с нужной позиции, нам все равно это не очень поможет.
Зато поможет функция ПОДСТАВИТЬ (SUBSTITUTE). Она может заменить как все вхождения фрагмента текста на другой фрагмент, так и определенное вхождение.
Сначала подсчитаем сколько пробелов в строке есть:
=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))
=LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))
Для нашего примера результат этой формулы будет 3. Мы можем заменить третье вхожение пробела на какой-нибудь символ. Например, на символ перевода строки (код символа 10):
=ПОДСТАВИТЬ(A1;" ";СИМВОЛ(10))
=SUBSTITUTE(A1;" ";CHAR(10))
А потом найти этот символ с помощью функции ПОИСК и обрезать строку с помощью ЛЕВСИМВ:
=ЛЕВСИМВ(A1;ПОИСК(СИМВОЛ(10);A1))
=LEFT(A1;FIND(CHAR(10);A1))
Теперь мы можем собрать окончательную формулу и добавить в нее проверку на длину, убирание лишних пробелов в конце и начале СЖПРОБЕЛЫ (TRIM):
=ЕСЛИ(ДЛСТР(СЖПРОБЕЛЫ(A1))>30; ЛЕВСИМВ(СЖПРОБЕЛЫ(A1); ПОИСК(СИМВОЛ(10); ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВСИМВ(A1;30)); " ";СИМВОЛ(10); ДЛСТР(СЖПРОБЕЛЫ(ЛЕВСИМВ(A1;30)))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВСИМВ(A1;30)); " "; ""))))); A1)
=IF(LEN(TRIM(A1))>30;LEFT(TRIM(A1);FIND(CHAR(10);SUBSTITUTE(TRIM(LEFT(A1;30));" ";CHAR(10);LEN(TRIM(LEFT(A1;30)))-LEN(SUBSTITUTE(TRIM(LEFT(A1;30));" ";"")))));A1)
Задача 2
Отразить строку. Чтобы вместо "Привет" было "тевирП". Метод будет работать только в эксель, установленном по подписке Office 365, так как там есть новая функция ОБЪЕДИНИТЬ (TEXTJOIN).
Сначала мы разобьем текст на массив, каждый элемент которого содержит одну букву:
=ПСТР(A1;1+ДЛСТР(A1)-СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)
=MID(A1;1+LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)));1)
А потом обернем все в функцию ОБЪЕДИНИТЬ:
=ОБЪЕДИНИТЬ("";ЛОЖЬ;ПСТР(A1;1+ДЛСТР(A1)-СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1))
=TEXTJOIN("";FALSE;MID(A1;1+LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)));1))
Задача 3
Обратный поиск. Например, обратный поиск буквы "ам" по строке "мама мыла раму" должен вернуть 12. Здесь возможны два подхода - как в задаче 1 или как в задаче 2. Первый способ будет более универсальный, так как будет работать и в старых версиях эксель. План действий:
- Посчитаем количество букв (или фрагментов), которые мы ищем.
- Если их больше 1, то заменим последний фрагмент на непечатный символ (например, символ с кодом 10)
- Найдем этот символ в получившиейся строке.
Преобразуем все это в формулу:
=ЕСЛИ(A1="";ПОИСК("ам";A1);ЕСЛИ((ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;"ам";"")))/ДЛСТР("ам")>1; ПОИСК(СИМВОЛ(10);ПОДСТАВИТЬ(A1;"ам";СИМВОЛ(10);(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;"ам";"")))/ДЛСТР("ам")));ПОИСК(A1;"ам")))
=IF(A1="";FIND("ам";A1);IF((LEN(A1)-LEN(SUBSTITUTE(A1;"ам";"")))/LEN("ам")>1; FIND(CHAR(10);SUBSTITUTE(A1;"ам";CHAR(10);(LEN(A1)-LEN(SUBSTITUTE(A1;"ам";"")))/LEN("ам")));FIND(A1;"ам")))
Ну вот и все :-) Пишите другие интересные задачки в комментариях, разберем вместе.
