- Обычная составная формула
- Формулы массива для поиска символа с конца строки
- С помощью МАКС
- С помощью ПОИСКПОЗ
Поиск позиции последнего вхождения значения внутри ячейки Excel – довольно сложная задача.
Тем не менее, иногда ее необходимо решить, например, чтобы удалить или заменить это вхождение.
Так а в чем же проблема?
Все дело в том, что функции поиска позиции ПОИСК и НАЙТИ ищут только с начала ячейки, и у них нет параметра переключения на поиск с конца.
Процедура Найти и Заменить также не подойдет. Она ищет не с конца строки, а просто находит (и заменяет) все вхождения.
Ниже я покажу пару способов, как осуществить поиск с конца строки.
Обычная составная формула
Формула, похожая на ту, что ниже, рассматривается подробно в статье о том, как удалить последнее слово в ячейке Excel. Поиск пробела с конца строки как раз является необходимым в этом случае.
Формула ниже ищет пробел с конца ячейки A1:
=ПОИСК(ЮНИСИМВ(23456);ПОДСТАВИТЬ(A1;" ";ЮНИСИМВ(23456);ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))
Как она работает:
- Фрагмент, обозначенный красным цветом, вычисляет количество пробелов в ячейке. Подробнее можно почитать в описании функции ПОДСТАВИТЬ.
- Это количество является аргументом еще одной подстановки, где заменяется лишь последний пробел (выделено жирным)
- Вместо пробела в его последнюю позицию вставляется достаточно редкий символ. В данном случае это иероглиф, который создается функцией СИМВОЛ. Но можно и прописать символ вручную. В формуле ниже это обратная косая черта:
=ПОИСК("";ПОДСТАВИТЬ(A1;" ";"";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))
- Функция ПОИСК находит позицию этого символа. Поскольку он встречается в строке всего один раз и стоит на месте последнего пробела, это и позволяет нам сказать, что поиск произведен справа налево.
Если вам необходимо найти с конца строки какой-то другой символ или текстовый фрагмент, замените пробелы в этой формуле на него.
Формулы массива для поиска символа с конца строки
Поиск слева направо с помощью функции ПОДСТАВИТЬ, описанный выше, имеет пару недостатков.
Первый – регистрозависимость этой функции, но это легко учесть, обернув нужные фрагменты функциями изменения регистра.
А второй уже серьезнее – формула не очень универсальна, т.к. использует замену на символ, который может оказаться в строке, и тогда она выдаст неверное значение.
От обоих проблем избавят формулы массива. Они обе создают массивы значений, внутри которых функция выбирает последнее.
С помощью МАКС
Формула ниже находит позицию символа “а” в любом регистре.
={МАКС((ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)="а")*СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1))))}
ВАЖНО: Это формула массива! Она вводится без фигурных скобок. Но не клавишей Enter а сочетанием: Ctrl+Shift+Enter После этого фигурные скобки появятся сами. Если ввести формулу обычным способом, она не сработает.
Механика ее работы пошагово:
- Функция ДЛСТР измеряет длину ячейки в символах
- ДВССЫЛ создает из текстового представления длины ссылку на диапазон строк с 1 по строку, равную этой длине
- Функция СТРОКА возвращает массив чисел, соответствующих этим длинам, соответственно, {1;2;3;…”длина строки”}
- Функция ПСТР, обрабатывая этот массив, возвращает для каждого числа символ, стоящий на этой позиции в строке
- Текстовое сравнение с символом “а” возвращает булевый массив (значения ИСТИНА или ЛОЖЬ)
- Этот массив умножается на повторно созданный массив чисел (пункты 1:3). ЛОЖЬ эквивалентна нулю, а ИСТИНА – единице, поэтому для всех символов, не равных “а”, в результирующем числовом массиве будут нули, а для равных – их позиции
- Функция МАКС возвращает наибольшее число в этом массиве.
С помощью ПОИСКПОЗ
Чуть более хитрая механика делает формулу короче, вместе с тем существенно быстрее, а задействует функцию ПОИСКПОЗ:
{=ПОИСКПОЗ(2;1/(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)="а");1)}
ВАЖНО: Это формула массива! Она вводится без фигурных скобок. Но не клавишей Enter а сочетанием: Ctrl+Shift+Enter После этого фигурные скобки появятся сами. Если ввести формулу обычным способом, она не сработает.
Здесь алгоритм такой:
- Как и в предыдущем варианте, с помощью тех же функций ДЛСТР, СТРОКА, ДВССЫЛ, ПСТР и текстового сравнения создается булевый массив;
- Но на этом этапе единица делится на него. ЛОЖЬ эквивалентна нулю и выдается ошибка деления на ноль. ИСТИНА возвращает единицу.
- Функция ПОИСКПОЗ с последним параметром “1” при поиске 2 (на месте 2 может быть любое число больше 1) возвращает позицию последнего наибольшего числа, меньшее, чем 2. Т.е. последней единицы, которой и соответствует последний найденный в строке символ.
Смотрите также по теме:
Формулы массива в Excel
Удалить последнее слово в ячейке
Найти и заменить первую букву в ячейке на заглавную
Предыдущая статья о формулах массива:
Учимся формулам массива 3/4:
Извлечь текст до первой цифры в ячейке
Часто сталкиваетесь с этой или похожими задачами при работе в Excel?
Сотни инструментов надстройки для Excel !SEMTools помогут вам упростить их решение и сэкономят ваше время!
Как найти первый символ в конце строки?
Найти позицию последнего вхождения значения в ячейке Excel — довольно сложная задача.
Однако иногда это необходимо исправить, например, чтобы удалить или заменить это вхождение.
Так в чем проблема?
Дело в том, что функции поиска местоположения SEARCH и FIND выполняют поиск только с начала ячейки и не имеют возможности перейти к поиску с конца.
Процедура «Найти и заменить» также не будет работать. Он не ищет с конца строки, он просто находит (и заменяет) все вхождения.
Ниже я покажу вам несколько способов поиска с конца строки.
Обычная составная формула
Формула, аналогичная приведенной ниже, подробно описана в статье Как удалить последнее слово в ячейке Excel. В этом случае необходимо искать пробел с конца строки.
Следующая формула ищет пробел от конца ячейки A1:
= ПОИСК (UNISIMV (23456); ПОДСТАВИТЬ (A1; «»; UNISIMV (23456); DLSTR (A1) -LSTR (ПОДСТАВИТЬ (A1;» «;»»))))
Как это работает:
- В разделе, выделенном красным, рассчитывается количество пробелов в ячейке. Более подробную информацию можно найти в описании функции ЗАМЕНИТЬ.
- Этот номер является аргументом в пользу другой замены, где заменяется только последний пробел (выделенный жирным шрифтом)
- Вместо пробела на его последней позиции вставляется довольно редкий символ. В данном случае это иероглиф, созданный функцией SYMBOL. Но вы также можете зарегистрировать символ вручную. В приведенной ниже формуле это обратная косая черта:
= ПОИСК («»; ПОДСТАВИТЬ (A1; «»; «»; DLSTR (A1) -LSTR (ПОДСТАВИТЬ (A1;» «;»»))))
- Функция ПОИСК находит позицию этого символа. Поскольку он встречается только один раз в строке и находится на месте последнего пробела, это позволяет нам сказать, что поиск выполнялся справа налево.
Если вам нужно найти другие символы или фрагменты текста с конца строки, замените им пробелы в этой формуле.
Примеры использования составной формулы для поиска с конца строки
Формулы массива для поиска символа с конца строки
Поиск слева направо с помощью описанной выше функции REPLACE имеет несколько недостатков.
Первый — это регистрозависимый регистр этой функции, но это легко учесть, заключив необходимые фрагменты в функции изменения регистра.
А второе уже посерьезнее: формула не очень универсальна, т.к использует подстановку символа, который может появиться в строке и, таким образом, вернет неверное значение.
Формулы массива избавляют от обеих этих проблем. Оба создают массивы значений, в которых функция выбирает последнее.
С помощью МАКС
В любом случае следующая формула находит положение символа «а.
= {MAX ((MID (A1; LINE (INDIRECT («1:» & DLSTR (A1)))); 1) = «a») * LINE (INDIRECT («1:» & DLSTR (A1)))))) ВАЖНО: Это формула массива! Вы входите без фигурных скобок. Но не клавишей Enter, а комбинацией: Ctrl + Shift + Enter После этого фигурные скобки появятся сами собой. Если вы введете формулу обычным способом, она не сработает.
Механика его работы пошаговая:
- Функция DLSTR измеряет длину ячейки в символах
- INDIRECT создает из текстового представления длины ссылку на диапазон строк от 1 до строки, равной этой длине
- Функция СТРОКА возвращает массив чисел, соответствующих этим длинам, соответственно, {1; 2; 3;… «длина строки»}
- Функция MID, обрабатывая этот массив, возвращает символ в этой позиции в строке для каждого числа
- Текстовое сравнение с символом «a» возвращает логический массив (значения TRUE или FALSE)
- Эта матрица умножается на воссозданную матрицу чисел (точки 1: 3). FALSE эквивалентно нулю, а TRUE эквивалентно единице, поэтому для всех символов, которые не равны «a», результирующий числовой массив будет содержать нули, а для равных — их позиции
- Функция MAX возвращает наибольшее число в этом массиве.
С помощью ПОИСКПОЗ
Чуть более умный механизм делает формулу короче, но в то же время намного быстрее и использует функцию СРАВНЕНИЕ:
{= SEARCH (2; 1 / (MID (A1; LINE (INDIRECT («1:» & DLSTR (A1)))); 1) = «a»); 1)} ВАЖНО: это формула массива! Вы входите без фигурных скобок. Но не клавишей Enter, а комбинацией: Ctrl + Shift + Enter После этого фигурные скобки появятся сами собой. Если вы введете формулу обычным способом, она не сработает.
Вот алгоритм:
- Как и в предыдущей версии, с использованием тех же функций DLSTR, STRING, INDIRECT, MID и сравнения текста создается логический массив;
- Но на данном этапе на него делится единство. FALSE эквивалентно нулю, и выводится ошибка деления на ноль. ИСТИНА возвращает единицу.
- Функция ПОИСКПОЗ с последним параметром «1» при поиске 2 (на месте 2 может быть любое число больше 1) возвращает позицию последнего наибольшего числа меньше 2) То есть последнюю единицу, которая соответствует последней в строке найден символ.
mirvaal Пользователь Сообщений: 86 |
Необходимо что-то типа «ПОИСК» или «НАЙТИ» только не слева направо, а наоборот. |
слэн Пользователь Сообщений: 5192 |
|
mazayZR Пользователь Сообщений: 950 |
слэн, а вот твой же вариант решения проблемы =ПСТР(СЖПРОБЕЛЫ(A1);1+НАЙТИ(«~~~»;ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);» «;»~~~»;ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);» «;»»))));ДЛСТР(A1)) а вот формула массива от DL =ПРАВСИМВ($A$1;ДЛСТР($A$1)-МАКС(ЕСЛИ(ПСТР($A$1;СТРОКА(ДВССЫЛ(«2:»&ДЛСТР($A$1)-1));1)=» «;СТРОКА(ДВССЫЛ(«2:»&ДЛСТР($A$1)-1));0))) |
mirvaal Пользователь Сообщений: 86 |
|
слэн Пользователь Сообщений: 5192 |
но эта по-моему короче? =ПСТР(B5;МАКС(ЕСЛИ(ПСТР(B5;СТРОКА(СМЕЩ($A$1;0;0;ДЛСТР(B5);1));1)=» «;СТРОКА(СМЕЩ($A$1;0;0;ДЛСТР(B5);1))))+1;9999) 112 против 141 символа =ПСТР(СЖПРОБЕЛЫ(A1);1+НАЙТИ(«~~~»;ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);» «;»~~~»;ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);» «;»»))));ДЛСТР(A1)) |
ZVI Пользователь Сообщений: 4338 |
85 символов и не формула массива |
mazayZR Пользователь Сообщений: 950 |
забавно |
ytk5kyky Пользователь Сообщений: 2410 |
{quote}{login=ZVI}{date=14.01.2009 10:58}{thema=}{post}85 символов и не формула массива |
ZVI Пользователь Сообщений: 4338 |
Тема сегодня такая http://www.planetaexcel.ru/forum.php?thread_id=5027 |
Если бы все звёзды были одного размера, то для того чтобы узнать какая ближайшая совсем не надо измерять расстояние до всех звёзд а потом их сравнивать, достаточно выбросить что нибудь за борт и посмотреть в какую сторону оно будет двигаться Спасибо за формулу, а также за все посты в «сортировка массива в VBA» |
|
{quote}{login=Лузер™}{date=15.01.2009 10:11}{post}Есть одно ограничение. Строка символов после последнего пробела должна быть короче 60.{/post}{/quote} |
|
слэн Пользователь Сообщений: 5192 |
вот вам 84 символа без существенных ограничений ПОДСТАВИТЬ(A1;ЛЕВСИМВ(A1;МАКС(ЕСЛИ(ПСТР(A1;СТРОКА(1:999);1)=» «;СТРОКА(1:999))));»») кто меньше ? :)) |
ytk5kyky Пользователь Сообщений: 2410 |
Слэн, Ваша формула но на один символ короче |
слэн Пользователь Сообщений: 5192 |
:)) ну тогда уж : ПСТР(A1;1+МАКС(ЕСЛИ(ПСТР(A1;СТРОКА(1:999);1)=» «;СТРОКА(1:999)));999) ваш ход |
слэн Пользователь Сообщений: 5192 |
и еще есть резерв .. но ваш ход, сударь |
ytk5kyky Пользователь Сообщений: 2410 |
Кстати, у ZVI 85 знаков с «=», а у слэна без оного. |
ytk5kyky Пользователь Сообщений: 2410 |
Нету у меня ходов. Просто я давно заметил, что ПСТР короче ЛЕВСИМВ |
слэн Пользователь Сообщений: 5192 |
ПСТР(A1;1+МАКС((ПСТР(A1;СТРОКА(1:999);1)=» «)*СТРОКА(1:999));999) 65 символов без равно и фиг скобок ЗАМЕНИТЬ(A1;1;МАКС((ПСТР(A1;СТРОКА(1:999);1)=» «)*СТРОКА(1:999));»») 68 символов |
Плюс АДЫН!!! замечательным пионерам Excelя нашего форума!!! |
|
{quote}{login=слэн}{date=15.01.2009 12:01}{thema=}{post}вот вам 84 символа без существенных ограничений |
|
написал в ответ целую повесть, с иллюстрациями, алеегориями — а оно мне «код не совпадает» зол!!!! для поставленной задачи формула ZVI может быть урезана так: что дает 68 символов.. |
|
ytk5kyky Пользователь Сообщений: 2410 |
{quote}{login=dl@kartoshka.ru}{date=16.01.2009 08:20}{post}Разумеется остаётся в силе замечание Лузера о том, что существуют нехорошие фразы, для которых формула будет давать ошибки, но является ли это таким уж страшным недостатком?{/post}{/quote}На самом делее этот «недостаток» легко исправляется. Формула, правда, становится длиннее, но этот «перфекционизм (совершенство)» мне напоминает старый тезис программистов, что «любую программу можно сократить на одну строку». |
слэн Пользователь Сообщений: 5192 |
остается еще ограничение на длину строки, что в переводе около 500 «слов» в изначальной фразе.. |
ytk5kyky Пользователь Сообщений: 2410 |
{quote}{login=слэн}{date=16.01.2009 11:08}{thema=}{post}остается еще ограничение на длину строки, что в переводе около 500 «слов» в изначальной фразе..{/post}{/quote} Ну если у Вас «слово» длиной в среднем 65 символов, то 500 слов. |
слэн Пользователь Сообщений: 5192 |
я имел в виду, что слова разделены пробелами, т.е пробелов может быть как минимум на 1 меньше слов, а каждый пробел(после сжпробелы) заменяется в формуле ZVI на 60 таких же.. но еще и сами слова — отсюда примерно 500 с другой стороны трудно ожидать таких длинных слов или фраз — может и 60 достаточно, но 999-то уж точно? и уж совсем точно, что раз строка не может превышать 32767, то и 65536 не нужны.. а если у меня 999 заменить на 60, то это ж целых три символа экономии |
ytk5kyky Пользователь Сообщений: 2410 |
А, теперь понял откуда 500 взялось. Логично. Мне лень было менять 65536 на 32767 — количество символов одинаковое. |
слэн Пользователь Сообщений: 5192 |
|
ytk5kyky Пользователь Сообщений: 2410 |
Время — да. Но этого критерия еще не вводилось. Только количество символов в формуле. |
слэн Пользователь Сообщений: 5192 |
кстати, DL, с универсальностью тоже не все так просто.. не получится так, как Вы предложили.. попробуйте на такой строчке: 1 2 3 44444444444444444444444444444444444444444444444444444444444444444444 5 |
dl@kartoshka.ru Гость |
#30 20.01.2009 07:05:20 Поэтому я и уточнил что замечание Лузера остаётся в силе. а вот канувшие в лету аллегории действительно очень жаль, что поделать борьба с киборгами перманентна…. |
|
|||
misha122062
06.05.09 — 11:51 |
надо в строке найти первый нужный символ справа. На ум пришло — перебор по «Найти», пока не последний. |
||
mikecool
1 — 06.05.09 — 11:51 |
Прав отменили? |
||
mikecool
2 — 06.05.09 — 11:51 |
+1 а , пропустил «нужный» |
||
gr13
3 — 06.05.09 — 11:52 |
Прав(Строка, 1)? |
||
НЕА123
4 — 06.05.09 — 11:57 |
строка= стрзаменить(Стр,Символ, Символы.НПП); |
||
misha122062
5 — 06.05.09 — 11:57 |
Ну причем тут Прав. |
||
НЕА123
6 — 06.05.09 — 11:58 |
+(4) ежели нет то 0. |
||
misha122062
7 — 06.05.09 — 11:58 |
ище по первому справа «» |
||
misha122062
8 — 06.05.09 — 11:59 |
(4)не понял к чему |
||
H A D G E H O G s
9 — 06.05.09 — 12:01 |
Элегантно:
UserКаталог=СтрЗаменить(UserКаталог,»»,Символы.ПС); |
||
НЕА123
10 — 06.05.09 — 12:02 |
строка= стрзаменить(Стр,Символ, Символы.НПП); |
||
misha122062
11 — 06.05.09 — 12:02 |
(9)ШЕДЕВВР!! |
||
НЕА123
12 — 06.05.09 — 12:03 |
(10) тьфу… Символы.НПП = Символы.ПС |
||
H A D G E H O G s
13 — 06.05.09 — 12:04 |
(11) Вон, у (10) — лучше |
||
misha122062
14 — 06.05.09 — 12:04 |
согласен! Спасибо |
||
H A D G E H O G s
15 — 06.05.09 — 12:05 |
СтрПолучитьСтроку() |
||
НЕА123 16 — 06.05.09 — 12:13 |
(15) |
TurboConf — расширение возможностей Конфигуратора 1С
0 / 0 / 0 Регистрация: 23.11.2010 Сообщений: 153 |
|
1 |
|
Поиск первого символа справа или последнего слева05.07.2006, 21:59. Показов 10371. Ответов 5
Всем здрасти!
0 |
Programming Эксперт 94731 / 64177 / 26122 Регистрация: 12.04.2006 Сообщений: 116,782 |
05.07.2006, 21:59 |
5 |
VladConn 5 / 5 / 3 Регистрация: 17.10.2007 Сообщений: 1,119 |
||||
05.07.2006, 23:05 |
2 |
|||
0 |
0 / 0 / 0 Регистрация: 23.11.2010 Сообщений: 153 |
|
06.07.2006, 17:08 [ТС] |
3 |
Извиняюсь, как обычно неправильно формулирую вопрос. Нужно найти первый опеределенный символ справа, допустим букву «А»
0 |
vlth 14 / 14 / 2 Регистрация: 23.03.2010 Сообщений: 635 |
||||
06.07.2006, 18:40 |
4 |
|||
0 |
0 / 0 / 0 Регистрация: 23.11.2010 Сообщений: 153 |
|
06.07.2006, 20:07 [ТС] |
5 |
SUPER! SPASIBO!
0 |
vlth 14 / 14 / 2 Регистрация: 23.03.2010 Сообщений: 635 |
||||
07.07.2006, 17:30 |
6 |
|||
Или последний слева.
0 |