Примеры, приведенные в этом разделе, многим пользователям покажутся более сложными, чем те, что были рассмотрены ранее. Но убедитесь сами, сколько полезных действий с текстом можно осуществить с их помощью.
Подсчет заданных символов в ячейке
Приведенная ниже формула подсчитывает в строке ячейки А1 количество символов В, учитывая только символы верхнего регистра.
=ДЛСТР(А1)-ДЛСТР(ПОДСТАВИТЬ(А1;“В”;“”))
В формуле используется функция ПОДСТАВИТЬ, создающая в памяти новую строку, из которой удалены все имеющиеся символы В. После этого длина созданной строки вычитается из длины первоначальной строки. В результате формула возвращает количество символов В, содержащихся в первоначальной строке.
Следующая формула более универсальна. Она подсчитывает количество символов В одновременно и верхнего, и нижнего регистра, содержащихся в ячейке А1.
=ДЛСТР(А1)-ДЛСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(А1;“В”;“”);“b”;“”))
Подсчет строк, содержащихся в ячейке
Формулы, приведенные в предыдущем разделе, подсчитывают, сколько раз определенный символ встречается в заданной ячейке. Следующая формула работает сразу с несколькими символами и возвращает количество экземпляров определенной подстроки (заданной в ячейке В1) в пределах строки, содержащейся в ячейке А1. Заданная подстрока может состоять из любого количества символов.
= (ДЛСТР(А1)-ДЛСТР(ПОДСТАВИТЬ(А1;В1;“”)))/ДЛСТР(В1)
Например, если ячейка А1 содержит текст блондинистый блондин, а ячейка В1 – текст блондин, то данная формула вернет значение 2.
Имейте в виду, что в данном случае при сравнении текста учитывается регистр символов, поэтому если ячейка В1 будет содержать текст Блондин, то формула вернет значение 0. Следующая формула – просто модифицированная версия предыдущей, которая осуществляет поиск без учета регистра (без различия строчных и заглавных букв).
=(ДЛСТР(А1)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(А1);ПРОПИСН(В1);“”)))/ДЛСТР(В1)
Удаление замыкающих символов минуса
В некоторых вычислительных системах отрицательное значение числа обозначается знаком “минус”, расположенным не перед числом, а после него. Если импортировать такие значения в Excel, замыкающий символ “минус” будет интерпретироваться как текст, а число – как положительное.
Приведенная ниже формула проверяет наличие замыкающего минуса, удаляет его и возвращает отрицательное число. Например, если ячейка А1 содержит 198,43-, формула возвращает -198,43.
=ЕСЛИ((ПРАВСИМВ(А1;1))=“-”;ЛЕВСИМВ(А1;ДЛСТР(А1)-1)*-1;А1)
Отображение заданного числа в виде порядкового числительного
В некоторых случаях вам может потребоваться записать определенное число как порядковый номер. Например, в английском тексте Today is the 21st day of the month (сегодня 21-е число месяца) число 21 отображено как порядковый номер, т.е. в конец числа добавлен суффикс st.
Решить эту проблему для текста на русском языке было бы намного сложнее. Например, суффикс, добавляемый к числу 1, зависит не только от числа, но и смысла предложения: 1-е, 1-го, 1-му и т.д.
Символы, которые в данном примере будут добавляться в конец числа, зависят только от самого числа. Поэтому невозможно привести стандартный пример этой формулы. Пользователи могут каждый раз создавать собственную сложную конструкцию. В большинстве порядковых номеров в английском языке используется суффикс th. Исключение составляют номера, заканчивающиеся числами 1, 2 или 3. То же самое касается номеров, в которых используется первый разряд – число 1, например, 11, 12 или 13. Эти числа требуют применения определенных правил, которые при желании могут быть трансформированы в формулу Excel.
Формула, которая приведена ниже, преобразует число, содержащееся в ячейке А1 (предполагается, что это целое число), в порядковый номер.
=А1&ЕСЛИ(ИЛИ(ЗНАЧЕН(ПРАВСИМВ(А1;2))={11;12;13});“th”;
ЕСЛИ(ИЛИ(ЗНАЧЕН(ПРАВСИМВ(А1))={1;2;3});ВЫБОР(ПРАВСИМВ(А1);
“st”;“nd”;“rd”);“th”))
Это довольно сложная формула, поэтому рассмотрим ее компоненты более подробно. В общем случае формула работает следующим образом.
1. Суффикс th добавляется, если последние две цифры порядкового номера представляют собой числа 11, 12 или 13.
2. Если первое правило не подходит к имеющемуся числу, то проверяется последняя цифра. Если последняя цифра 1, то используется суффикс st, если 2 – суффикс nd, если 3 – суффикс rd.
3. Если ни первое, ни второе правило не подходят, применяется суффикс th.
Определение литеры столбца по заданному номеру
Следующая формула возвращает литеру столбца рабочего листа (в диапазоне от А до XFD) в соответствии со значением, введенным в ячейку А1. Например, если ячейка А1 содержит число 29, то формула возвратит значение АС.
=ЛЕВСИМВ(АДРЕС(1;А1;4);НАЙТИ(1;АДРЕС(1;А1;4))-1)
Имейте в виду, что данная формула не проверяет правильность введенного числового значения номера столбца. Другими словами, если ячейка А1 содержит значение меньше 1 или больше 16 384, то формула все равно будет давать результат, хотя уже не имеющий никакого смысла. Ниже приведена модифицированная версия этой формулы, в которую введена новая функция ЕСЛИОШИБКА, отображающая текст “Некорректный столбец” вместо значения ошибки.
=ЕСЛИОШИБКА(ЛЕВСИМВ(АДРЕС(1;А1;4);НАЙТИ(1;АДРЕС(1;А1;4))-1);
“Некорректный столбец”)
Извлечение имени файла из заданного маршрута
Ниже приведена формула, которая возвращает имя файла, извлеченное из маршрута. Например, если ячейка А1 содержит маршрут с:\windows\desktop\myfile.xlsx, то данная формула вернет текст myfile.xlsx.
=ПСТР(A1;НАЙТИ(“*”;ПОДСТАВИТЬ(А1;“\”;“*”;ДЛСТР(А1)-
ДЛСТР(ПОДСТАВИТЬ(А1;“\”;“”))))+1;ДЛСТР(А1))
Данная формула предполагает, что разделитель маршрута представляет собой обратную косую черту (\). По существу, эта формула возвращает весь текст, следующий за последним знаком обратной косой черты. Если ячейка А1 не содержит этого символа, формула возвращает значение ошибки.
Извлечение первого слова строки
Чтобы извлечь первое слово строки, в формуле необходимо задать поиск позиции первого пробела. Найденная позиция будет использоваться как аргумент функции ЛЕВСИМВ. Приведенная ниже формула выполняет именно эти действия:
=ЛЕВСИМВ(А1;НАЙТИ(“ ”;А1)-1)
Данная формула возвращает весь текст в ячейке А1, находящийся до первого пробела. Однако и в этой формуле существует один недочет: она возвращает значение ошибки в том случае, если ячейка А1 состоит из единственного слова. Чтобы решить эту проблему, достаточно ввести в формулу новую функцию ЕСЛИОШИБКА, которая будет проверять наличие ошибки.
=ЕСЛИОШИБКА(ЛЕВСИМВ(А1;НАЙТИ(“ ”;А1)-1);А1)
Функция ЕСЛИОШИБКА появилась только в версии Excel 2007. Для совместимости с предыдущими версиями программы можно использовать другую формулу:
=ЕСЛИ(ЕОШ(НАЙТИ(“ ”;А1));А1;ЛЕВСИМВ(А1;НАЙТИ(“ ”;А1)-1))
Извлечение последнего слова строки
Извлечение последнего слова – более сложная задача, поскольку функция НАЙТИ работает только в направлении слева направо. Задача сводится к определению местонахождения последнего пробела. Приведенная ниже формула позволяет решить эту задачу. Данная формула возвращает последнее слово строки, другими словами, весь текст, расположенный после последнего пробела.
=ПРАВСИМВ(А1;ДЛСТР(А1)-НАЙТИ(“*”;ПОДСТАВИТЬ(А1;“ ”;“*”;
ДЛСТР(А1)-ДЛСТР(ПОДСТАВИТЬ(А1;“ ”;“”)))))
Тем не менее эта формула имеет ту же проблему, что и приведенная в предыдущем разделе: она возвращает значение ошибки в том случае, если строка не содержит ни одного пробела. В следующей формуле эта проблема решается с помощью использования функции ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ПРАВСИМВ(А1;ДЛСТР(А1)-НАЙТИ(“*”;ПОДСТАВИТЬ(А1;“ ”;“*”;
ДЛСТР(А1)-ДЛСТР(ПОДСТАВИТЬ(А1;“ ”;“”)))));А1)
Для совместимости с предыдущими версиями можете использовать следующую формулу:
=ЕСЛИ(ЕСОШ(НАЙТИ(“ ”,А1));А1;ПРАВСИМВ(А1;
ДЛСТР(А1)-НАЙТИ(“*”;ПОДСТАВИТЬ(А1;“ ”;“*”;ДЛСТР(А1)
-ДЛСТР(ПОДСТАВИТЬ(А1;“ ”;“”))))))
Извлечение всех слов строки, кроме первого
Следующая формула полностью возвращает содержимое ячейки А1, за исключением первого слова:
=ПРАВСИМВ(А1;ДЛСТР(А1)-НАЙТИ(“ ”;А1;1))
Например, если ячейка А1 содержит текст Текущий бюджет 2002, формула возвращает текст бюджет 2002.
Данная формула возвращает ошибку, если в ячейке содержится только одно слово. Следующая формула решает эту проблему и возвращает пустую строку, если ячейка содержит одно или несколько слов:
=ЕСЛИОШИБКА(ПРАВСИМВ(А1,ДЛСТР(А1)-НАЙТИ(“. ”,А1,1)),“”)
Для совместимости с предыдущими версиями можно воспользоваться следующей формулой:
=ЕСЛИ(ЕСОШ(НАЙТИ(“ ”;А1))ПРАВСИМВ(А1;ДЛСТР(А1)-НАЙТИ(“ ”;А1;1)))
Извлечение имени, отчества и фамилии
Предположим, что в одном из столбцов рабочего листа содержится список полных имен сотрудников фирмы, включающий имя, отчество и фамилию. Нам нужно разделить содержимое этого столбца на три отдельных: для имени, для отчества и для фамилии. Эта задача более сложная, чем может показаться на первый взгляд, поскольку отчество содержится не во всех строках, тем не менее ее можно решить.
Примечание
Задача становится более сложной, если имена в списке содержат титулы и звания (например, г-н, проф.) или сопровождаются некоторыми дополнительными подробностями (например, мл. или III). Приведенные ниже формулы не помогут в таких сложных случаях. Тем не менее они позволяют существенно продвинуться в решении этой задачи. Вам остается внести лишь несколько корректировок, чтобы вручную обработать частные случаи.
В приведенных ниже формулах предполагается, что имя и фамилия человека находятся в ячейке А1. Формула, возвращающая имя человека, выглядит весьма просто:
=ЛЕВСИМВ(А1;НАЙТИ(“ ”;А1)-1)
Сложнее ситуация обстоит со списками отчеств людей, так как эта информация может быть введена не во все ячейки. Данная формула возвращает отчество человека (если таковое имеется), в противном случае формула не вернет ничего.
=ЕСЛИ(ДЛСТР(А1)-ДЛСТР(ПОДСТАВИТЬ(А1;“ ”;“”))>1;
ПСТР(А1;НАЙТИ(“ ”;А1)+1;НАЙТИ(“ ”;А1;НАЙТИ(“ ”;
А1)+1)-(НАЙТИ(“ ”;А1)+1));“”)
И наконец, следующая формула возвращает фамилию:
=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(А1)-НАЙТИ(“*”;ПОДСТАВИТЬ(А1;
“ ”;“*”;ДЛСТР(А1)-ДЛСТР(ПОДСТАВИТЬ(А1;“ ”;“”)))));“”)
Однако существует формула, которая позволяет более простым способом извлечь из списка отчества людей. Следующая формула может быть весьма полезна, если изначально использовать две другие формулы для получения имени и фамилии. Необходимо поместить имя человека в ячейку В1, а фамилию – в ячейку D1.
=ЕСЛИ(ДЛСТР(B1&D1)+2>=ДЛСТР(А1);“ ”;ПСТР(А1;ДЛСТР(В1)+2;
ДЛСТР(А1)-ДЛСТР(B1&D1)-2))
Как видно эти формулы работают достаточно хорошо. Тем не менее некоторые проблемы все же существуют, особенно с именами, которые содержат четыре слова. Но, как отмечается ниже, такую ситуацию можно исправить вручную.
Удаление титулов и званий из имен
Приведенная далее формула позволяет избежать двух часто употребляемых обращений: Госп. и Г-жа. Например, если ячейка А1 содержит текст Госп. Сергей Новиков, следующая формула возвращает Сергей Новиков:
=ЕСЛИ(ИЛИ(ЛЕВСИМВ(А1;4)=“Госп.”;ЛЕВСИМВ(А1;4)=“Г-жа”);
ПРАВСИМВ(А1;ДЛСТР(А1)-НАЙТИ(“ ”;А1));А1)
Разделение текстовых строк без использования формул
Во многих случаях можно отказаться от применения формул и разобрать строки на составляющие их элементы с помощью команды Данные→Работа с данными→Текст по столбцам. Будет вызван мастер текстов, содержащий несколько последовательных окон (одно из них показано ниже). Мастер позволит по шагам пройти процесс преобразования одного столбца данных в несколько. В общем случае в первом окне мастера нужно установить переключатель С разделителями и в качестве разделителя во втором окне выбрать символ пробела.
Подсчет количества слов в ячейке
Следующая формула возвращает количество слов в ячейке А1:
=ДЛСТР(СЖПРОБЕЛЫ(А1))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(А1);“ ”;“”))+1
В этой формуле используется функция СЖПРОБЕЛЫ, которая удаляет лишние пробелы. После этого с помощью функции ПОДСТАВИТЬ в памяти создается новая строка без пробелов. Затем длина созданной строки вычитается из длины исходной строки, в результате чего получается количество пробелов. Это значение увеличивается на единицу и таким образом подсчитывается количество слов.
Имейте в виду, что приведенная выше формула возвращает значение 1, если заданная ячейка не содержит данных. Решить эту проблему можно, усовершенствовав формулу следующим образом:
=ЕСЛИ(ДЛСТР(А1)=0;0;ДЛСТР(СЖПРОБЕЛЫ(А1))-
ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(А1);“ ”;“”))+1).