Страницы: 1
RSS
Поиск по фразам макросом
 
Добрый день! Уважаемые специалисты! Нужна помощь наверняка кто-то сталкивался! Суть вопроса:
Есть столбец, в него макросом загоняется информация в приложенном файле это столбец А лист 1, значений может быть 1 а может быть 1500 на листе 2 есть "библиотека" по клиентам там порядка 20000 тысяч клиентов. Мы коллективно сделали формулы которые позволяют подставить соответствие и id из "библиотеки". Для лучшего понимания вопроса красным выделено то что формула ищет. Столкнулись с проблемой так как библиотека очень большая формула отрабатывает ооочень медленно! Возможно у кого то есть идей как ускорить процесс подборки с помощью макроса? ОГРОМНОЕ ВСЕМ СПАСИБО!!!!
 
Можно макросом просматривать с помощью Like сравнивать значения в ячейках.  
 
К сожалению я только в начале пути по изучению vba, спасибо буду изучать;)
 
А, если будут однофамильцы?
 
Цитата
DopplerEffect: с помощью Like
InStr пошустрее будет

WanderKat, ищите по тэгам "неточный поиск" и "поиск соответствий по списку". Вот, например…
Посмотрите также недавнюю похожую тему по неточному поиску с выводом совпадений…
Изменено: Jack Famous - 15.08.2018 11:08:10
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Находясь на листе 1 запустите макрос, Id  в столбце D
Код
Sub PoiskFIO()
Dim i As Long
Dim iLastRow As Long
Dim FoundFIO As Range
Application.ScreenUpdating = False
With Worksheets("2")
 iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  For i = 2 To iLastRow
    Set FoundFIO = Columns("A").Find(.Cells(i, "A"), , xlValues, xlPart)
    If Not FoundFIO Is Nothing Then
      Cells(FoundFIO.Row, "D") = .Cells(i, "B")
    End If
  Next
End With
Application.ScreenUpdating = True
End Sub
 
Цитата
Kuzmich написал:
А, если будут однофамильцы?
Спасибо за вопрос, однофамильцев нет так как изначально библиотека сделана без совпадений, и даже если бы такие совпадения и были это не страшно так как в дальнейшем готовый результат попадает в другую таблицу где данный минус нивелируется другими формулами.
 
Доброе время суток.
Коллеги, а будет ли на VBA быстрее? Тот же самый просмотр 20000 тысяч клиентов :excl:  в 1500 строках. Из-за чего должно быть быстрее?
 
Цитата
Kuzmich Находясь на листе 1 запустите макрос, Id  в столбце D
Да это то что нужно, но время обработки сопоставимо с отработкой формулой, скорее всего придется работать дальше с формулой:(
БОЛЬШОЕ ВАМ СПАСИБО!
 
Через массивы на VBA должно быть быстрее... по идее... возможно
C2-C4
 
Цитата
Михаил Комиссаров написал:
VBA должно быть быстрее... по идее... возможно
На чём основана гипотеза?
 
Андрей VG, ну как минимум файл вроде должен весить меньше и проще открываться, когда формул нет.  
 
Не загонял такое кол-во строк в массивы, но при сравнении со стандартным до 10 тыс строк, разница между обычным циклом и циклом, когда диапазон в массив загнал, составляла что то около 2 секундуд против 17 сек. Стандартное умножение на 20 (200 тыс строк = 40 сек, против 340 сек). Формулы при этом не проверял, но при равенстве стандартного VBA и формул, получается что через массивы можно ускорить. Но это теоритически
C2-C4
 
Цитата
Михаил Комиссаров написал:
разница между обычным циклом и циклом, когда диапазон в массив загнал
Разговор то всё же не об это цикл по ячейкам или цикл по массиву данных, извлечённых из этих ячеек. Тут то всё давно ясно. Я о фактологическом. Поиск для 1500 значений подстроки из 20000 вариантов формулами. Видимо формула
Код
=ПРОСМОТР(2;1/ПОИСК('2'!$A$2:$A$5;A2);'2'!$A$2:$A$5) 

не очень эффективна, мягко говоря.
Протестировал. Формульный вариант - 96 секунд. VBA за 2,5 секунды.
 
WanderKat,
1. Почему где-то в справочнике стоит пробел перед фамилией, а где-то нет?
2. Можно получить более полный файл. Есть формульная идея, но требует проверки.

проверил на синтетических данных (многократное копирование исходных), 14 сек. если использовать промежуточный столбец с номером найденной строки. порядка 14 при использовании ВПР для поиска ИД в 3м столбце и 27 при двух независимых формулах. Единственное, в справочнике везде добавил пробел в начале.

номер строки
=MIN(IFERROR(MATCH(" "&TRIM(MID(SUBSTITUTE(" "&TRIM(A5);" ";REPT(" ";LEN(A5)));ROW($A$1:INDEX($A:$A;LEN(TRIM(A5))-LEN(SUBSTITUTE(TRIM(A5);" ";""))))*LEN(A5);LEN(A5)))&" "&LEFT(TRIM(MID(SUBSTITUTE(" "&TRIM(A5);" ";REPT(" ";LEN(A5)));(ROW($A$1:INDEX($A:$A;LEN(TRIM(A5))-LEN(SUBSTITUTE(TRIM(A5);" ";""))))+1)*LEN(A5);LEN(A5)));1);'2'!A:A;0);FALSE()))

В формуле можно LEN(A5) заменить на константу, близкую или равную максимальной длине строки, но скорости почти не добавляет, а вот подсчет "слов" влияет на скорость очень сильно.
Не скажу, что обыграл VBA, но реальный пример, когда длинная формула работает быстрее чем короткая.
Изменено: БМВ - 16.08.2018 08:06:41
По вопросам из тем форума, личку не читаю.
 
БМВ, что-то не смог приспособить вашу формулу на свой пример :(  - ну не формулист я. Вариант несколько покороче. Время обновления на компе 9,4 секунды. Сможете проверить - сколько по сравнению с вашим вариантом?

P. S. Папаразитировал на вашем подходе.
Изменено: Андрей VG - 16.08.2018 09:53:05
 
БМВ Спасибо за интерес к теме!

1) данные будут заносить разные люди, соответственно такие недочеты имеют место быть. конечно же все решается "СЖПРОБЕЛ" но лучше сразу разобрать более сложную задачу!?
2) Более большой файл к сожалению еще не готов.

Андрей VG Спасибо за макрос ваш отрабатывает в разы быстрее сейчас пробую протестировать

ОГРОМНЕЙШЕЕ  ВСЕМ СПАСИБО!!!!!
 
Андрей VG, Дома тестил на ноутбуке,  сейчас на десктопе i7 ваши данные  - 5 сек  , единственное, так как был расчет на первую букву следующего слова, то изменил чуток.

Цитата
Андрей VG написал:
P. S. Папаразитировал на вашем подходе.
Подходы разные. Я сперва выделяю возможные фразы, опираясь на слова, и потом ищу их в справочнике. Собственно поиск в справочнике - встроенная и быстрая функция, поиск слов и прочего , помедленне но выполнить надо 1500 раз и в зависимости от количества слов поискать Nx1500 среди 20000. Казалось бы на N больше поиска, но разница именно в обработке большого массива встроенной функцией.
Изменено: БМВ - 16.08.2018 11:16:07
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
Copy of Пример219_t.zip  
Ребята, ну меняйте чуть названия в новых примерах и решениях, что б было возможным открыть сразу несколько, для сравнения!
 
Цитата
БМВ написал:
Подходы разные
Самое главное, что ПОДСТАВИТЬ куда как быстрее ПОИСК в комбинации НЕ(ЕОШИБКА(ПОИСК... Собственно это и взял именно для ответа на вопрос -
есть ли данная подстрока в данной строке. А по словам, тут в общем случае может быть сложно. Уж больно размытое понятие - слово.
Изменено: Андрей VG - 16.08.2018 12:00:09
 
Цитата
Михаил С. написал:
Ребята, ну меняйте чуть названия
Вроде все разные?
По вопросам из тем форума, личку не читаю.
 
Андрей VG скажите пожалуйста как отключить чувствительность к регистру в вашем макросе?
 
WanderKat, пожалуйста, почитайте справку по Функция InStr
 
Уточните название  темы
 
Андрей VG спасибо за наставление!

Решение проблемы:
Код
Option Compare Text
Изучая работу макроса столкнулись с таким моментом:
Если искомое значение в столбце ФИО листа "Текстовый" Вашего примера расположено вначале ячейки то макрос его не отрабатывает пример (Иванов Иван Иванович дог....) -макрос не распознает данного клиента. Но если (*Иванов Иван Иванович дог...) где *-любое значение все работает отлично. Добавить пробел перед назначением не проблема но странно что макрос так работает! БОЛЬШОЕ СПАСИБО!
 
WanderKat, #24 не игнорируйте.

По факту . Всякие маски и прочее придуманы как раз для того чтоб было понятно , где и что что искать. соответственно ваш справочник без таких указаний, как что до, что после - будет работать только по тому алгоритму что заложен, то есть или точное совпадение, или любое вхождение фразы, или начинается с фразы, или заканчивается на фразу. Без оного уникальность не соблюсти ибо Наберете "Иванов И" , что при нечувствительности к регистру будет "иванов и"  и найдется   строка ".... диванов и….."
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх