Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Найти код товара (коды перечислены через запятую) и проставить значения из соседней правой ячейки
 
#1
27.10.2018 22:44:20
Доброго времени товарищи. Прошу помочь!
Перекопал много информации но решения так и не удалось найти.

Задача казалось бы проста, необходимо найти код товара и проставить значения из соседней правой ячейки.
Проблема в том что коды товара перечислены через точку с запятой а ВПР как и ИНДЕКС+ПОИСКПОЗ отказываются искать нужное значение в ячейках в которых указано более одного значения.
Пример на яблоках прилагаю.
 
 
 
#2
27.10.2018 22:52:56
Если коды всегда двузначные, то
=VLOOKUP("*"&D2&"*";A:B;2;0) а вот если нет, то с учетом разделителя наверно так
=IFERROR(VLOOKUP(D2;A:B;2;0);IFERROR(VLOOKUP(D2&";*";A:B;2;0);IFERROR(VLOOKUP("*;"&D2&";*";A:B;2;0);IFERROR(VLOOKUP("*;"&D2;A:B;2;0);""))))
или массивно так
=INDEX(B:B;MIN(IFERROR(MATCH(CHOOSE({1;2;3;4};D2;D2&";*";"*;"&D2&";*";"*;"&D2);A:A;);1=0)))
=IFERROR(INDEX(B:B;IFERROR(1/(1/MIN(IFERROR(MATCH(CHOOSE({1;2;3;4};D7;D7&";*";"*;"&D7&";*";"*;"&D7);A:A;);1=0)));NA()));"")
или без трех кнопок
=INDEX(B:B;MIN(IFERROR(INDEX(MATCH(CHOOSE({1;2;3;4};D2;D2&";*";"*;"&D2&";*";"*;"&D2);A:A;););1=0)))
=IFERROR(INDEX(B:B;AGGREGATE(15;6;1/(1/MATCH(CHOOSE({1;2;3;4};D2;D2&";*";"*;"&D2&";*";"*;"&D2);A:A;));1));"")
Изменено: БМВ - 27.10.2018 23:36:17
По вопросам из тем форума, личку не читаю.
 
 
 
#3
27.10.2018 23:13:21
Поучу-ка я формулы. А если код товара 99?
Владимир
 
 
 
#4
27.10.2018 23:31:46
Если не только двузначные (формула массива)
=ПОДСТАВИТЬ(ВПР("*;"&D2&";*";";"&$A$2:$B$6&";";2;);";";)
 
 
 
#5
27.10.2018 23:41:48
Здравствуйте, коллеги! Во второй формуле в #2, мне кажется, последний IFERROR лишний.
Владимир
 
 
 
#6
27.10.2018 23:46:50
sokol92,  Владимир, ну скажем так, если  ничего нет, то чтоб ошибка не лезла. Четыре поиска, четыре IFERROR.
По вопросам из тем форума, личку не читаю.
 
 
 
#7
27.10.2018 23:56:00
Михаил, я думаю, что правильней выдавать #Н/Д при ненахождении (хотя это, конечно, дело вкуса). Кстати, #4 будет работать некорректно, если значение какой-нибудь ячейки из столбца B содержит точку с запятой (она исчезнет в ответе).
Изменено: sokol92 - 27.10.2018 23:57:46
Владимир
 
 
 
#8
28.10.2018 00:03:34
Цитата
sokol92 написал: что правильней выдавать #Н/Д при ненахождении
Вообще, показывать ошибки - неправильно. На форуме не раз появлялись темы с просьбами помочь обойти ошибки или с недоумением "почему не работает?".
Если уж нужно, то лучше вывести ругательство какое-нибудь.

Цитата
#4 будет работать некорректно, если значение какой-нибудь ячейки из столбца B содержит точку с запятой
О точке с запятой - да, пропадет. Можно вместо ПОДСТАВИТЬ применить ПСТР  с обрезанием крайних символов.
Слепим два разделителя:
=ПОДСТАВИТЬ(ВПР("*;"&D2&";*";";;"&$A$2:$B$6&";;";2;);";;";)
 
 
 
#9
28.10.2018 00:04:56
Спасибо за обучение! :) Успехов!
Изменено: sokol92 - 28.10.2018 00:12:53
Владимир
 
 
 
#10
28.10.2018 08:29:06
Цитата
vikttur написал:
Если уж нужно, то лучше вывести ругательство какое-нибудь.
именно так, и в данном случае все готово, только вставить и все.
Цитата
vikttur написал:
О точке с запятой - да, пропадет.
на самом дел , подумав об этом я сразу отказался от варианта наподобии №4, правда еще от него меня отталкивает необходимость указать диапазон ограниченный, но это дело вкуса. Кто-то делает с запасом, кто-то предпочитает менять в формуле.

В пользу моих длинных вариантов наверно можно отнести возможность работы с данными, в которых после и/или перед разделителя, может появится пробел Можно указать варианты. Убрать его не получится , так не вернуть его обратно во второй столбец. Но тогда надо переходить на Match
=INDEX(B2:B6;MATCH("*;"&D2&";*";";"&SUBSTITUTE(A2:A6;" ";)&";";))
По вопросам из тем форума, личку не читаю.
 
 
 
#11
30.10.2018 21:12:21
Немного почувствовал себя криптоаналитиком :)  
В первых двух удалось разобрался полностью, с последними сложнее...
В итоге почему-то ни одна формула не сработала...
Изменено: Esminec - 31.10.2018 14:26:00
 
 
 
#12
30.10.2018 21:15:45
Esminec, а зачем Вы продублировали в цитате формулу Михаила? Думаете, он её не помнит? Вернитесь в своё сообщение и удалите то, что Вы ошибочно считаете цитатой.
 
 
 
#13
31.10.2018 11:04:41
Цитата
Юрий М написал:
Думаете, он её не помнит?
Юрий, я как на сессии -  сдал, забыл :-)
Изменено: БМВ - 31.10.2018 11:05:36
По вопросам из тем форума, личку не читаю.
 
 
 
#14
31.10.2018 14:36:25
Цитата
Юрий М написал:
Вернитесь в своё сообщение и удалите то, что Вы ошибочно считаете цитатой.
Удалил.
Но так и не понял как можно цитату ошибочно считать цитатой?
 
 
 
#15
31.10.2018 14:58:01
Цитата
Esminec написал: Но так и не понял как можно цитату ошибочно считать цитатой?
, если вы всё произведение Л.Н. Толстого "Война и мир" вставите в сообщение это будет считаться цитатой, если да то у вас неверное представление об этом понятии.
Лень двигатель прогресса, доказано!!!
 
 
 
#16
31.10.2018 19:17:49
Esminec, исходя из ЛС, у Вам не удается использовать формулы в рабочем файле. Причин может быть море. Если не справиться, то помощь может быть оказана, но нужен файл.
UPD не увидел файла
Наверно самое универсальное
=INDEX($B:$B;MIN(IF(ISNUMBER(FIND(";"&D2&";";";"&TRIM($A$1:INDEX($A:$A;COUNTA($A:$A)))&";"));ROW($A$1:INDEX($A:$A;COUNTA($A:$A))))))

варианты в файле, указанный - последний, но он массивный. тоже можно через Aggregate(15;6 сделать.
Изменено: БМВ - 31.10.2018 20:55:58
По вопросам из тем форума, личку не читаю.
 
 
 
#17
31.10.2018 19:41:46
Насколько я могу судить (на темной стороне), в #11 проблемы с тем, что длина текстов в ячейках столбца A больше 255 символов.
Владимир
 
 
 
#18
31.10.2018 20:24:46
Цитата
sokol92 написал:
Насколько я могу судить (на темной стороне),
не судите, да не судимы будете :-) .
Владимир, добрый вечер. Я на компе файл не открывал а писал с мобилки. Выше подправил.
По вопросам из тем форума, личку не читаю.
 
 
 
#19
31.10.2018 20:49:35
Здравствуйте, Михаил! Посветлело...
Владимир
 
 
 
Страницы: 1
Наверх