Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск части текста и подстановка значений
 
#1
15.10.2020 08:59:59
Всем доброго времени суток. На работе столкнулся с необходимостью автоматизации одного процесса.

1. Получение отчета по поступлениям за дату.
2. Сопоставление счета на листе export в столбце С на соответствие одному из счетов на листе БАЗА в столбце B.
3. Если совпадение то необходимо сумму поступления учитывать и суммировать. (то есть export платежей идет по разным подразделениям,  а БАЗА счетов содержит уже выборку по одному конкретному подразделению. Собственно необходимо автоматом суммировать платежи по номеру счета.

Проблема в оптимизации ячеек содержащих по разному написанных номеров счетов будь то "№ 10000011467" "№ 11750" "№10000011798" итд (это референс который заполняют контрагенты при оплате).
То есть вопрос у меня в том как в соседнем столбце в листе EXPORT сделать ячейки содержащие то, что можно сравнивать через ВПР с данными в Базе по номеру счета.

Остальное сам)

Спасибо всем. К сожалению нет времени читать весь форум. задача вообще на сегодня стоит.
 
 
 
#2
15.10.2020 09:06:30
Рамиль Камалитдинов, сколько из приведенных данных в 1 листе имеется в листе база? 2 шт?
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#3
15.10.2020 09:11:32
Доброго дня

в D11 и вниз
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/ПОИСК(База!$B$2:$B$1122;C11);База!$B$2:$B$1122);"Нет в базе")

целых 5 совпадений
 
 
 
#4
15.10.2020 09:12:43
Цитата
Рамиль Камалитдинов написал:
То есть вопрос у меня в том как в соседнем столбце в листе EXPORT сделать ячейки содержащие то, что можно сравнивать через ВПР с данными в Базе по номеру счета.
Вставьте в D11 и протяните формулу
Код
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(C11;"№ ";"№");" ";ПОВТОР(" ";99));ПОИСК("№";ПОДСТАВИТЬ(ПОДСТАВИТЬ(C11;"№ ";"№");" ";ПОВТОР(" ";99)))+1;99))
Правда у вас не везде на листе export в колонке C, есть номера счетов
Изменено: Александр П. - 15.10.2020 09:15:44
 
 
 
#5
15.10.2020 09:19:20
Цитата
Mershik написал:
сколько из приведенных данных в 1 листе имеется в листе база? 2 шт?
Может и не быть на сегодняшний день к примеру
 
 
 
#6
15.10.2020 09:20:43
Цитата
Александр П. написал:
Правда у вас не везде на листе export в колонке C, есть номера счетов
Спасибо, поэтому я подумал, что номер счета (совпадения) можно искать по последним пяти цифрам?
 
 
 
#7
15.10.2020 09:22:18
Цитата
Wild.Godlike написал:
целых 5 совпадений
Кажись здорово. Попробую Ваш вариант как базу. Спасибо огромное
 
 
 
#8
15.10.2020 09:26:41
Рамиль Камалитдинов,Не понял вашего вопроса. Вариант предложенный Mershik гораздо лучше.
 
 
 
#9
15.10.2020 09:34:52
Цитата
Александр П. написал: Вариант предложенный  Mershik  гораздо лучше.
НЕ вижу варианта
Цитата
Александр П. написал: Не понял вашего вопроса
я предположил, что поиск соответствия можно вести по последним пяти символам в счете.
 
 
 
#10
15.10.2020 09:39:29
Цитата
Рамиль Камалитдинов написал:
НЕ вижу варианта
прошу прощения, вариант предложенный в сообщении 3 от Wild.Godlike
Мой вариант не вытащит из колонки С если перед номером счёта не будет стоять знак номера №
Изменено: Александр П. - 15.10.2020 09:42:23
 
 
 
#11
15.10.2020 09:45:00
Всем спасибо огромное.
 
 
 
#12
15.10.2020 10:17:09
последние 5 на основе №
=IFERROR(RIGHT(TRIM(LEFT(SUBSTITUTE(TRIM(MID(TRIM(C11);FIND("№";TRIM(C11))+1;99));" ";REPT(" ";20));20));5);"")
ну и
=IF(D11="";"";IFERROR(INDEX(Исходник[Дата];MATCH(10^10+export!D11;Исходник[Номер];));""))

Цитата
Александр П. написал:
3 от Wild.Godlike
не вытащит если номер не указан полностью. Если делать обратное преобразование, то есть искать последние 5цифр, то даже с пробелом, можно получить некорректный результат
см пример.
Изменено: БМВ - 15.10.2020 10:40:06
По вопросам из тем форума, личку не читаю.
 
 
 
#13
15.10.2020 10:37:43
БМВ, Спасибо за предложенный вариант. Он действительно более детально прорабатывает. Я понимаю, что наглею но все же, можно ли добавить ф формулу критерии посика по варианту в строках 45,46?
 
 
 
#14
15.10.2020 11:10:42
да просто в самом начале пути добавить замену " N" на " №".
ну и еще добавить поиск 1000
=IFERROR(RIGHT(--TRIM(LEFT(SUBSTITUTE(TRIM(MID(TRIM(C11);IFERROR(FIND("№";TRIM(SUBSTITUTE(C11;" N"; " №")))+1;FIND(10^3;TRIM(SUBSTITUTE(C11;" N"; " №"))));99));" ";REPT(" ";20));20));5);"")

Из этих вариантов можно слепить более менее находящую номер формулу. Но когда указаны номера через запятую и без пробела, то ....
Изменено: БМВ - 15.10.2020 11:12:19
По вопросам из тем форума, личку не читаю.
 
 
 
#15
15.10.2020 14:51:16
Результат приобретает желаемый вид. Соответственно пользователю будут доступны столбцы A,B,C,M,N. Остальные закрою.
Отправил на тест пользователям. Не могу понять в чем беда. У некоторых один в один как у меня (см фаил ДДС), у некоторых как на картинке. Там где картинка версия 2016 prof +.
В чем может быть беда?

Цитата
Рамиль Камалитдинов написал: У некоторых один в один как у меня (см фаил ДДС), у некоторых как на картинке. Там где картинка версия 2016 prof +.
https://yadi.sk/i/ybxtSO3Pk4CCDg


Вопрос снят. Формат ячеек  :oops: .
Всем спасибо
Изменено: Рамиль Камалитдинов - 16.10.2020 07:17:54
 
 
 
Страницы: 1
Наверх