Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Поиск ВПР - не всего тексту, а только по первым символам - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск ВПР - не всего тексту, а только по первым символам (Формулы/Formulas)
Поиск ВПР - не всего тексту, а только по первым символам
odeon16 Дата: Четверг, 21.09.2017, 10:31 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 85
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
День добрый, уважаемые программисты. Помогите с решением.

На листе располагается формула ВПР, которая ищет текст в ячейках столбца U15:U40, и выхватывает числа соответствующие столбцу - из диапазона S15:S40.
(Результат работы ВПР - находится в диапазоне L40:L46)
Однако поиск осуществляется успешно, если "запросное слово" - не слишком длинное.
А вот если оно чересчур длинное - то формула уже не может осуществить поиск по нему.

Подскажите - как все-таки заставить формулу осуществлять поиск ?
(допустим, чтобы поиск осуществлялся - не по всему тексту целиком, а только по первым ста символам)

(Расположение столбцов менять нельзя, диспетчер имен использовать - тоже нельзя)
К сообщению приложен файл: 4565466.xlsx (13.7 Kb)
 
Ответить
СообщениеДень добрый, уважаемые программисты. Помогите с решением.

На листе располагается формула ВПР, которая ищет текст в ячейках столбца U15:U40, и выхватывает числа соответствующие столбцу - из диапазона S15:S40.
(Результат работы ВПР - находится в диапазоне L40:L46)
Однако поиск осуществляется успешно, если "запросное слово" - не слишком длинное.
А вот если оно чересчур длинное - то формула уже не может осуществить поиск по нему.

Подскажите - как все-таки заставить формулу осуществлять поиск ?
(допустим, чтобы поиск осуществлялся - не по всему тексту целиком, а только по первым ста символам)

(Расположение столбцов менять нельзя, диспетчер имен использовать - тоже нельзя)
Автор - odeon16
Дата добавления - 21.09.2017 в 10:31
_Boroda_ Дата: Четверг, 21.09.2017, 10:38 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=ПРОСМОТР(;-1/(K40=U$15:U$40);S$15:S$40)

Код
=СУММЕСЛИ(U$15:U$40;ЛЕВБ(K40;99)&"*";S$15:S$40)


Кстати, ВПР не по первому столбцу можно написать как связку ИНДЕКС-ПОИСКПОЗ
Код
=ИНДЕКС(S$15:S$40;ПОИСКПОЗ(K40;U$15:U$40;))

Там, правда, тоже не сработает со строкой, большей 255 символов, но я не про это, а про замену ВПРу

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Код
=ПРОСМОТР(;-1/(K40=U$15:U$40);S$15:S$40)

Код
=СУММЕСЛИ(U$15:U$40;ЛЕВБ(K40;99)&"*";S$15:S$40)


Кстати, ВПР не по первому столбцу можно написать как связку ИНДЕКС-ПОИСКПОЗ
Код
=ИНДЕКС(S$15:S$40;ПОИСКПОЗ(K40;U$15:U$40;))

Там, правда, тоже не сработает со строкой, большей 255 символов, но я не про это, а про замену ВПРу
Автор - _Boroda_
Дата добавления - 21.09.2017 в 10:38
sboy Дата: Четверг, 21.09.2017, 10:38 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
Вариант без ВПР
Код
=СУММПРОИЗВ($S$15:$S$40*($U$15:$U$40=K40))
К сообщению приложен файл: 0820451.xlsx (13.7 Kb)

Яндекс: 410016850021169
 
Ответить
СообщениеДобрый день.
Вариант без ВПР
Код
=СУММПРОИЗВ($S$15:$S$40*($U$15:$U$40=K40))
Автор - sboy
Дата добавления - 21.09.2017 в 10:38
Gopronotmore Дата: Четверг, 21.09.2017, 10:42 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
Боюсь больше 255 символов нельзя прописать, ни 1 из ваших формул не работает ....
 
Ответить
СообщениеБоюсь больше 255 символов нельзя прописать, ни 1 из ваших формул не работает ....
Автор - Gopronotmore
Дата добавления - 21.09.2017 в 10:42
Gopronotmore Дата: Четверг, 21.09.2017, 10:44 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
Код
=VLOOKUP(K43&"*";INDEX(S$15:U$40;N(INDEX(ROW(S$15:S$40)-14;));N(INDEX({3\1};)));2;)

просто дописать &"*" будет искать по всему оставшемуся тексту но не более 255 символов
 
Ответить
Сообщение
Код
=VLOOKUP(K43&"*";INDEX(S$15:U$40;N(INDEX(ROW(S$15:S$40)-14;));N(INDEX({3\1};)));2;)

просто дописать &"*" будет искать по всему оставшемуся тексту но не более 255 символов
Автор - Gopronotmore
Дата добавления - 21.09.2017 в 10:44
odeon16 Дата: Четверг, 21.09.2017, 10:46 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 85
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_, sboy, в принципе работают конечно эти варианты.

Подскажите - как мою формулу изменить, чтобы там оставалось ВПР (но было ограничение символов поиска) ?
 
Ответить
Сообщение_Boroda_, sboy, в принципе работают конечно эти варианты.

Подскажите - как мою формулу изменить, чтобы там оставалось ВПР (но было ограничение символов поиска) ?
Автор - odeon16
Дата добавления - 21.09.2017 в 10:46
Gopronotmore Дата: Четверг, 21.09.2017, 10:52 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
odeon16,

Код
=VLOOKUP(K40;CHOOSE(({1\2});$U$15:$U$40;$S$15:$S$40);2;0)
К сообщению приложен файл: Test_vpr.xlsx (13.6 Kb)


Сообщение отредактировал Gopronotmore - Четверг, 21.09.2017, 10:54
 
Ответить
Сообщениеodeon16,

Код
=VLOOKUP(K40;CHOOSE(({1\2});$U$15:$U$40;$S$15:$S$40);2;0)
Автор - Gopronotmore
Дата добавления - 21.09.2017 в 10:52
odeon16 Дата: Четверг, 21.09.2017, 10:56 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 85
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Gopronotmore, посмотрел файл - вы пишите, что если текст больше 255 - то будет ошибка.
Так мне как раз и нужно осуществить поиск текста в котором - больше 255 знаков.


Сообщение отредактировал odeon16 - Четверг, 21.09.2017, 11:00
 
Ответить
СообщениеGopronotmore, посмотрел файл - вы пишите, что если текст больше 255 - то будет ошибка.
Так мне как раз и нужно осуществить поиск текста в котором - больше 255 знаков.
Автор - odeon16
Дата добавления - 21.09.2017 в 10:56
Gopronotmore Дата: Четверг, 21.09.2017, 10:57 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
odeon16, поставьте к поиску позиции &"*" я в примере указал, но как написано выше если текст больше 255 символов то будет ошибка
 
Ответить
Сообщениеodeon16, поставьте к поиску позиции &"*" я в примере указал, но как написано выше если текст больше 255 символов то будет ошибка
Автор - Gopronotmore
Дата добавления - 21.09.2017 в 10:57
odeon16 Дата: Четверг, 21.09.2017, 11:04 | Сообщение № 10
Группа: Пользователи
Ранг: Участник
Сообщений: 85
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Gopronotmore, я имел ввиду такую схему :
Текст в целевой ячейке K43 изначально имеет больше 255 знаков.
Поэтому поиск - это не обрезание вручную текста в этой ячейке, для поиска (потому что весь текст там - нужный), а поиск по первым ста символам этого текста (по первым ста символам текста из ячейки K43).


Сообщение отредактировал odeon16 - Четверг, 21.09.2017, 11:05
 
Ответить
СообщениеGopronotmore, я имел ввиду такую схему :
Текст в целевой ячейке K43 изначально имеет больше 255 знаков.
Поэтому поиск - это не обрезание вручную текста в этой ячейке, для поиска (потому что весь текст там - нужный), а поиск по первым ста символам этого текста (по первым ста символам текста из ячейки K43).
Автор - odeon16
Дата добавления - 21.09.2017 в 11:04
Gopronotmore Дата: Четверг, 21.09.2017, 11:08 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
odeon16,
Код
=IF(LEN(VLOOKUP(K40;U:U;1;0))<=100;VLOOKUP(K40;CHOOSE(({1\2});$U$15:$U$40;$S$15:$S$40);2;0);0)


И то думаю что это не то, что вам нужно, на этом мои познания заканчиваются .... Если символов в ячейке больше 255, то не знаю как прописать что бы найти.

Логика моей формулы если значение меньше 100 символов или равно в ячейке то он подставит формулу в противном случае 0


Сообщение отредактировал Gopronotmore - Четверг, 21.09.2017, 11:11
 
Ответить
Сообщениеodeon16,
Код
=IF(LEN(VLOOKUP(K40;U:U;1;0))<=100;VLOOKUP(K40;CHOOSE(({1\2});$U$15:$U$40;$S$15:$S$40);2;0);0)


И то думаю что это не то, что вам нужно, на этом мои познания заканчиваются .... Если символов в ячейке больше 255, то не знаю как прописать что бы найти.

Логика моей формулы если значение меньше 100 символов или равно в ячейке то он подставит формулу в противном случае 0
Автор - Gopronotmore
Дата добавления - 21.09.2017 в 11:08
odeon16 Дата: Четверг, 21.09.2017, 11:14 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 85
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Gopronotmore, вот смотрите - применяю вашу формулу.
По-прежнему не работает - выдает ошибку #ЗНАЧ!
К сообщению приложен файл: 2910669.xlsx (13.7 Kb)
 
Ответить
СообщениеGopronotmore, вот смотрите - применяю вашу формулу.
По-прежнему не работает - выдает ошибку #ЗНАЧ!
Автор - odeon16
Дата добавления - 21.09.2017 в 11:14
Gopronotmore Дата: Четверг, 21.09.2017, 11:16 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
odeon16, в экселе максимальное значение в ячейке для работы формул не может превышать 255 знаков если это так будет давать значение с ошибкой пишите
Код
=IFERROR(IF(LEN(VLOOKUP(K40;U:U;1;0))<=100;VLOOKUP(K40;CHOOSE(({1\2});$U$15:$U$40;$S$15:$S$40);2;0);0);"а сюда как-то дописать что бы он брал значение из столбца напртоив")


Тогда если будет текст больше 255 символов будет ошибка и работать будет формула которая подставит значение из столбца напротив которого стоит значение, но тут я вам не помогу ...
 
Ответить
Сообщениеodeon16, в экселе максимальное значение в ячейке для работы формул не может превышать 255 знаков если это так будет давать значение с ошибкой пишите
Код
=IFERROR(IF(LEN(VLOOKUP(K40;U:U;1;0))<=100;VLOOKUP(K40;CHOOSE(({1\2});$U$15:$U$40;$S$15:$S$40);2;0);0);"а сюда как-то дописать что бы он брал значение из столбца напртоив")


Тогда если будет текст больше 255 символов будет ошибка и работать будет формула которая подставит значение из столбца напротив которого стоит значение, но тут я вам не помогу ...
Автор - Gopronotmore
Дата добавления - 21.09.2017 в 11:16
sboy Дата: Четверг, 21.09.2017, 11:24 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
odeon16,
если очень хочется ВПР, то можно так (формула массива)
Код
=ВПР(ЛЕВБ(K40;99);ВЫБОР({1;2};ЛЕВБ($U$15:$U$40;99);$S$15:$S$40);2;0)
К сообщению приложен файл: 4877602.xlsx (13.6 Kb)

Яндекс: 410016850021169
 
Ответить
Сообщениеodeon16,
если очень хочется ВПР, то можно так (формула массива)
Код
=ВПР(ЛЕВБ(K40;99);ВЫБОР({1;2};ЛЕВБ($U$15:$U$40;99);$S$15:$S$40);2;0)
Автор - sboy
Дата добавления - 21.09.2017 в 11:24
odeon16 Дата: Четверг, 21.09.2017, 11:25 | Сообщение № 15
Группа: Пользователи
Ранг: Участник
Сообщений: 85
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_, помогло решение с функцией ПРОСМОТР.
Похоже - это наиболее подходящее решение. (И небольшая, и способная читать текст свыше 255 символов)
Спасибо за совет.


Сообщение отредактировал odeon16 - Четверг, 21.09.2017, 11:29
 
Ответить
Сообщение_Boroda_, помогло решение с функцией ПРОСМОТР.
Похоже - это наиболее подходящее решение. (И небольшая, и способная читать текст свыше 255 символов)
Спасибо за совет.
Автор - odeon16
Дата добавления - 21.09.2017 в 11:25
sboy Дата: Четверг, 21.09.2017, 11:25 | Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Цитата Gopronotmore, 21.09.2017 в 11:16, в сообщении № 13 ()
но тут я вам не помогу ...

а стоило ли начинать?...

Яндекс: 410016850021169
 
Ответить
Сообщение
Цитата Gopronotmore, 21.09.2017 в 11:16, в сообщении № 13 ()
но тут я вам не помогу ...

а стоило ли начинать?...
Автор - sboy
Дата добавления - 21.09.2017 в 11:25
Gopronotmore Дата: Четверг, 21.09.2017, 11:28 | Сообщение № 17
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
sboy, а почему нет ? интересно же сталкиваться с сложными задачами ..... Я предложил свой вариант ... А так можно сидеть и ничего вообще не писать а просто смотреть, а так когда пытаешься помочь человеку, для себя что-то новое узнаешь.

Так что не понял к чему этот камень в мой огород
 
Ответить
Сообщениеsboy, а почему нет ? интересно же сталкиваться с сложными задачами ..... Я предложил свой вариант ... А так можно сидеть и ничего вообще не писать а просто смотреть, а так когда пытаешься помочь человеку, для себя что-то новое узнаешь.

Так что не понял к чему этот камень в мой огород
Автор - Gopronotmore
Дата добавления - 21.09.2017 в 11:28
odeon16 Дата: Четверг, 21.09.2017, 11:29 | Сообщение № 18
Группа: Пользователи
Ранг: Участник
Сообщений: 85
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
sboy, вот.... я про это говорил.
Эта формула мне тоже пригодится. Как раз еще дополнительный столбец надо заполнять.

Спасибо за совет.
 
Ответить
Сообщениеsboy, вот.... я про это говорил.
Эта формула мне тоже пригодится. Как раз еще дополнительный столбец надо заполнять.

Спасибо за совет.
Автор - odeon16
Дата добавления - 21.09.2017 в 11:29
odeon16 Дата: Четверг, 21.09.2017, 11:30 | Сообщение № 19
Группа: Пользователи
Ранг: Участник
Сообщений: 85
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Gopronotmore, а я ничего и не говорю.
Было интересно и ваш вариант посмотреть.
 
Ответить
СообщениеGopronotmore, а я ничего и не говорю.
Было интересно и ваш вариант посмотреть.
Автор - odeon16
Дата добавления - 21.09.2017 в 11:30
Gopronotmore Дата: Четверг, 21.09.2017, 11:31 | Сообщение № 20
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 199
Репутация: 3 ±
Замечаний: 0% ±

Excel 2007
odeon16, да не Вы sboy, начал говорить)
 
Ответить
Сообщениеodeon16, да не Вы sboy, начал говорить)
Автор - Gopronotmore
Дата добавления - 21.09.2017 в 11:31
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск ВПР - не всего тексту, а только по первым символам (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!