Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Поиск в какой диапазон входит значение, ускорить расчет макросом или новой формулой
 
#1
12.07.2018 19:44:07
Всем привет.
Есть файл в котором вносятся большой список номеров, а в соседнем столбце считается формула (если номер входит в один из диапазонов на листе Kody_ABC-DEF, то ему присваивается соответственный регион в который он входит, если нет пишет что номер некорректный). Сам файл урезанный так как не мог приложить к письму из-за большого веса. Когда добавляю 10 000 номеров, формула считает около 6 минут. сейчас необходимо проверить больше 3 миллионов номеров. В связи с этим обращаюсь к Вам за помощью. Как можно ускорить вычисление данных?
 
 
 
#2
13.07.2018 08:45:39
tolik.shigarev, формула неоптимальна, можно ускорить на 2 порядка с помощью вспомогательного столбца.
Но сначала #2.
 
 
 
#3
13.07.2018 09:46:35
tolik.shigarev, на листе Проверка номеров ст. В - вспомогательный, ст. С - новая формула, ст. Д - старая формула.
Различия подсвечиваются с помощью условного форматирования - потом его надо удалить.
Различия обусловлены тем, что номер входит в два (или более) интервалов, но старая формула ищет последний интервал, а новая - первый.
Данные на листе Kody_ABC-DEF должны быть отсортированы по первому столбцу!

Если в формулах заменить ссылки на весь столбец на фактические диапазоны, должно быть быстрее:
Код
=ПОИСКПОЗ(A2;'Kody_ABC-DEF'!$A$2:$A$232)
=ЕСЛИОШИБКА(ЕСЛИ(A2<=ИНДЕКС('Kody_ABC-DEF'!$B$2:$B$232;B2);ИНДЕКС('Kody_ABC-DEF'!$C$2:$C$232;B2);"некорректный");"некорректный")
Изменено: Казанский - 13.07.2018 09:51:22
 
 
 
#4
13.07.2018 09:54:25
Попробовал набить словарь всеми номерами - тут максимум чуть более 2х миллионов номеров, заполнение словаря заняло... да не дождался, нереально...
Померю - напишу. Если дождусь :)
И это ведь ещё не весь список номеров? Значит словарь отпадает...
 
 
 
#5
13.07.2018 09:59:29
Hugo, если всеми номерами, то зачем словарь? Логический массив или байтовый (меньше памяти, но медленней) или битовый (еще меньше памяти и еще медленней :)).
 
 
 
#6
13.07.2018 10:01:17
Так, я так понял нужно подключатся, или ещё пока нет?))
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#7
13.07.2018 10:10:25
Я думал в словарь собрать все номера и каждому индекс, по которому затем брать описание.
Но вижу что долго, действительно т.к. номера то можно простой массив использовать, ну и пусть дырявый :)
 
 
 
#8
13.07.2018 10:15:49
Цитата
Казанский написал:
Различия обусловлены тем, что номер входит в два (или более) интервалов, но старая формула ищет последний интервал, а новая - первый.
Номер не может входить в два и более интервалов. Формула не совсем корректна получается
 
 
 
#9
13.07.2018 10:19:34
Хм, а массив такой и не создать...
Алексей, как на массиве делать?
 
 
 
#10
13.07.2018 10:23:11
tolik.shigarev, а, в вашей формуле опечатка - она значение из следующей строки возвращает  :idea:
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/(A2*1>='Kody_ABC-DEF'!$A$2:$A$232)/(A2*1<='Kody_ABC-DEF'!$B$2:$B$232);'Kody_ABC-DEF'!$C$3:$C$232);"некорректный")
 
 
 
#11
13.07.2018 10:25:21
Цитата
Hugo написал:
Я думал в словарь собрать все номера и каждому индекс, по которому затем брать описание.
Все номера это 693 196 460 штук. не слабо так))
 
 
 
#12
13.07.2018 10:26:21
Цитата
Казанский написал:  в вашей формуле опечатка )
Да, спасибо, вчера тоже заметил)
 
 
 
#13
13.07.2018 10:32:01
Цитата
Казанский написал:
что номер входит в два (или более) интервалов
Коллега, а как вы установили, что диапазоны номеров в Kody_ABC-DEF перекрываются?
 
 
 
#14
13.07.2018 10:35:58
Хотелось уйти от циклов при поиске пар для этих 3000000, но...
 
 
 
#15
13.07.2018 10:52:44
Если диапазоны не перекрываются, то не проще ли так? Тогда будет бинарный поиск и худо бедно не медленно.
 
 
 
#16
13.07.2018 10:54:14
А PQ долго сверять будет?
 
 
 
#17
13.07.2018 11:04:31
Цитата
Dark1589 написал:
А PQ долго сверять будет?
3 млн строк сгенерил, 15 минут подождал и бросил. Щас на 1 млн пробую.
З.Ы. в общем 1 млн строк победил за 8 мин на 8 потоках 2.6 ГГц
Вариант с формулой АНдрея тот же массив побеждает секунды за 3-4 на древненьком i3
Изменено: PooHkrd - 13.07.2018 11:13:57
Вот горшок пустой, он предмет простой...
 
 
 
#18
13.07.2018 11:10:26
PooHkrd, у себя 1млн номеров проверил за 30сек

Думаю на 3млн нужно не выгружать на лист, а добавлять в модель данных и работать там
Изменено: Dark1589 - 13.07.2018 11:11:45
 
 
 
#19
13.07.2018 11:15:13
Цитата
Dark1589 написал:
у себя 1млн номеров проверил за 30сек
Ну, на исходной мелкомерке диапазонов у меня ваш код для миллиона поиска и 9 секунд отработал. Но вы попробуйте на серьёзных исходных диапазонах Выписка из реестра Российской системы и плана нумерации, например. Такое лучше в нормальных БД делать всё же, на "человеческих" индексах, тогда будет быстро.
 
 
 
#20
13.07.2018 11:16:21
Dark1589,
Блин, пробовал списки напрямую сгенерить - он ругался, надо было через генератор жахнуть. Мое почтение.
Изменено: PooHkrd - 13.07.2018 11:16:34
Вот горшок пустой, он предмет простой...
 
 
 
#21
13.07.2018 11:18:35
Андрей VG, ну я и накидал запрос за пару минут. Если из инструментов только Excel, то думаю тут PQ+PP справятся быстрее остальных методов (хотя естественно могу ошибаться)
 
 
 
#22
13.07.2018 11:18:47
Подожду пока, если быстрых решений не появится - подключусь)
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#23
13.07.2018 11:44:47
Цитата
Андрей VG написал:
Коллега, а как вы установили, что диапазоны номеров в Kody_ABC-DEF перекрываются?
Никак :) Скорее всего они не перекрываются, но я не мог придумать другую причину, почему формулы, основанные на ПОИСКПОЗ и ПРОСМОТР, дают разные значения.
Цитата
Андрей VG написал:
Выписка из реестра Российской системы и плана нумерации
Афигеть 8-0 Оказывается, эти данные доступны.
Почему-то наш корпоративный мобильный с московским номером отнесен к МТС, хотя это Билайн  :qstn:
 
 
 
#24
13.07.2018 11:48:07
Возникла мысль сделать индекс (4 первых знака), отсортировать по нему, найти место начала и конца индекса
=ПОИСКПОЗ(ЛЕВСИМВ(A2;4)+0;'Kody_ABC-DEF'!A:A;0)
=ПОИСКПОЗ(ЛЕВСИМВ(A2;4)+1;'Kody_ABC-DEF'!A:A;0)
И в формулу вместо диапазонов min / max запихать уже усечённые дипазоны
ДВССЫЛ("'Kody_ABC-DEF'!B" & ссылка на мин знач инд &":B" & ссылка на макс знач индекса)
Но у меня что-то пошло не так + задёргали и не дают доделать. + не пойму будет ли такой изврат быстрее...
Я не волшебник, я только учусь.
 
 
 
#25
13.07.2018 11:51:24
Цитата
Казанский написал:
Почему-то наш корпоративный мобильный с московским номером отнесен к МТС, хотя это Билайн
А зачем вообще это всё теперь, когда я могу оператора сотовой связи менять раз в 2 недели не меняя номер :-)
Я не волшебник, я только учусь.
 
 
 
#26
13.07.2018 14:56:04
Цитата
bedvit написал:
Подожду пока, если быстрых решений не появится - подключусь)
Подключайтесь))  
 
 
 
#27
13.07.2018 15:49:03
Цитата
Андрей VG написал:
Если диапазоны не перекрываются, то не проще ли так? Тогда будет бинарный поиск и худо бедно не медленно.
Формула действительно работает быстрее, но выводит значение, которая находится на строку ниже.
Можно как то это поправить?
 
 
 
#28
13.07.2018 15:56:01
tolik.shigarev, правильно ли я понимаю, что диапазоны не пересекаются? Диапазоны непрерывные, или есть "дырки" между диапазонами? Тогда как предложил Андрей VG, их можно отсортировать - будет проще работать.
Задача в том, что бы проверить входит/не входит ли заданный номер в приложенные диапазоны?
Далее это только часть данных, можно где-то посмотреть полный рабочий вариант?
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#29
13.07.2018 16:05:45
Если дырок нет, по-моему это просто решается сортировкой и ВПР() с интервальным просмотром-1 и отсечением по верхней и нижней границы. Если дырки есть, можно их отсекать. Если их много и нужна принципиально новая функция, можно обдумать и написать функцию. Для этого жду ваших ответов.
У вас какой Excel (разрядность, версия?)
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#30
13.07.2018 16:32:40
Цитата
tolik.shigarev написал:
Можно как то это поправить?
У вас проблемы с изучением формулы ПОИСКПОЗ  :qstn:  :excl:  8-0  
Протестировал на базе интервалов по ссылке (почти 348 тысяч) и тестовому набору номеров на миллион. Считало где-то около минуты.
Цитата
bedvit написал:
Если дырки есть, можно их отсекать
Привет, Виталий.
Думаю, что это не принципиально в данном случае - главное, на мой бездоказательный взгляд, отсутствие перекрытия интервалов. Тут да, может быть засада. Ну, и не корректность данных, конечно, если от больше до.
Изменено: Андрей VG - 13.07.2018 16:34:00
 
 
 
Страницы: 1 2 След.
Наверх