Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск значения по нескольким ячейкам
 
#1
21.03.2023 18:32:19
Коллеги, всем привет! Помогите, пожалуйста, справиться новичку с задачей, сколько ни искал по форуму, ответа не нашел, а голову уже сломал...
В приложенном файле 2 вкладки: Лиды и Сделки
Нужно найти по номеру телефона лида строку со сделкой.
Казалось бы, ВПР в помощь... Но проблема в том, что телефоны в сделках могут быть записаны либо в разных ячейках, либо в одной ячейке 2 телефона, либо 2 телефона в одной ячейке + 3-й телефон в соседней.
И нужна формула, которая будет искать любое совпадение телефона из лида в телефонах по сделке.
Я наворотил формулу в столбце G (в лидах), которая должна по очереди проверять, встречается ли телефон в одном из столбцов со сделками, но она почему-то не хочет работать.
Та же история со второй попыткой - столбец H (в лидах) - тут я попытался искать именно значение из лида. Но тоже пишет, что что-то не так..

Бонус для самых отзывчивых: может, есть решение, чтобы не сливать в одну ячейку все номера телефонов, а потом разделять их по столбцам? За решение отсыплю кармы во все карманы))

Огромное спасибо!
Изменено: Дмитрий - 21.03.2023 18:37:47
 
 
 
#2
21.03.2023 18:54:12
пример у Вас не очень понятный, куча лишнего и повторы номеров - советую его поменять на более наглядный без лишних придуманных доп. столбцов
Искать можно например функцией поиск в массиве
=Сделки!A2:A11&", "&Сделки!B2:B11&", "&Сделки!C2:C11&", "&Сделки!D2:D11
Вычислять позицию где больше нуля и индексом тащить ID
Изменено: Тимофеев - 21.03.2023 19:00:23
 
 
 
#3
22.03.2023 12:14:38
Тимофеев, Спасибо за отклик. Обошелся без поиска в массиве - сколько ни тыкался, не смог осилить Индекс и Поискпоз...

Может, кому-то пригодится моё решение:

Итак, у нас есть 2 страницы: Лиды и Сделки. В Лидах указан номер лида, в сделках - 4 столбца телефонов, т.к. в сделке они могут быть указаны в разном составе: 1, 2 или даже 3. Ну и положение в столбце - как бог на душу положит - как менеджеры заведут.

Задача: нужно найти номер телефона лида в каком-то из 4-х столбцов сделок и вернуть соответствующее значение ID сделки.

Решение: Берем первый столбец в сделках, ВПРим по нему искомый телефон. Если он находится в первом столбце, то теперь уже ВПРим по этому номеру ID сделки. Если нет, то идем в следующий столбец. И т.д. Особое внимание обращаю на оператор ЕСЛИОШИБКА. Без него при отсутствии телефона в первом столбце формула возвращала значение #Н/Д.

Итого формула теперь выглядит так:
=ЕСЛИ(ЕСЛИОШИБКА(ВПР(A2;Сделки!A:A;1;0);0);ВПР(A2;Сделки!A:E;5;0);ЕСЛИ(ЕСЛИОШИБКА(ВПР(A2;Сделки!B:B;1;0);0);ВПР(A2;Сделки!B:E;4;0);ЕСЛИ(ЕСЛИОШИБКА(ВПР(A2;Сделки!С:С;1;0);0);ВПР(A2;Сделки!С:(E);3;0);ЕСЛИ(ЕСЛИОШИБКА(ВПР(A2;Сделки!D:D;1;0);0);ВПР(A2;Сделки!D:E;2;0)))))

Прикладываю файл для понимания. Учитывая замечание уважаемого Тимофеев, убрал лишние столбцы.

Всем удачи!
 
 
 
#4
22.03.2023 16:49:53
Привет Дмитрий,  держи формулу
Учитывает 2 номера телефона в ячейке, но не более и номера наши, т.е. 10 цифр (без +7, 8 )
И ищет последнее ID сделки если телефон повторяется
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/((МУМНОЖ((ПРАВСИМВ(A2;10)=ПРАВСИМВ(тлф;10))+(A2=--ЛЕВСИМВ(ПОДСТАВИТЬ(тлф;"+";"");11));{1:1:1:1})>0)*стр);сдел);" ")
тлф - база телефонов
сдел - ID сделки
стр - номер строки
Изменено: Павел с Востока - 22.03.2023 16:50:39
 
 
 
#5
22.03.2023 17:08:14
Цитата
написал:
не смог осилить Индекс и Поискпоз
Например так, но не думаю что нужно Вам все 1 048 576 строк использовать
Код
=ЕСЛИОШИБКА(
ИНДЕКС(Сделки!E:E;
ПОИСКПОЗ(0;--ЕОШ(ПОИСК(A2;Сделки!A:A&Сделки!B:B&Сделки!C:C&Сделки!D:D));0));
"-")
 
 
 
#6
23.03.2023 11:17:47
Павел с Востока, Тимофеев, коллеги, спасибо! Пошел осваивать))

Кстати, не знаете, есть ли какое-то решение по автоматическому приведению формата номеров к единому образцу, кроме последовательной замены знаков?

Просто в одном источнике может быть телефон формата +7 (910) 123-45-67, а в другом - 79101234567. И они никак не хотят дружить друг с другом... Можно, конечно, последовательно убрать заменой лишние символы, но, сами понимаете, это не автоматизация...

Еще раз спасибо))
 
 
 
Страницы: 1
Наверх