Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 25
Тема! Спасибо!
Дмитрий, первый параметр в формуле у меня работает, только если я ссылаюсь на ячейку. текст в кавычках например не могу прописать — не срабатывает. так и должно быть?
на массиве в 2^20 строк обычный бинарный поиск будет делать ~10 вычислений, формула выше — около 20, в то время, как линейный поиск — ~500.000
Как было получено ~500.000? 2^20/2? VLOOKUP в Excel идёт в 2 потока?
Нет, речь о чисто математическом расчете, если не учитывать никакие внешние факторы (многопоточность, обслуживание функции, запись в файл и т.д.).
Если массив 2^20 строк и все поиски разнородные, то при стремлении количества поисков к бесконечности среднее количество операций на один поиск стремится к числу, равному половине массива. Т.к. экстремумы — 1 и 2^20 (искомое может быть как в первой, так и в последней строке), среднее на большом количестве где-то посередине, т.е. 2^10, или ~500.000
Если массив 2^20 строк и все поиски разнородные, то при стремлении количества поисков к бесконечности среднее количество операций на один поиск стремится к числу, равному половине массива. Т.к. экстремумы — 1 и 2^20 (искомое может быть как в первой, так и в последней строке), среднее на большом количестве где-то посередине, т.е. 2^10, или ~500.000
аналогично при бинарном поиске, искомое может быть найдено как на 1-й, так и на 20-й итерации. На бесконечном количестве поисков среднее время поиска будет 10 итераций.
Спасибо!
Есть ещё set() — он меньше в памяти занимает. Грубо говоря, это словарь, в котором есть только ключи, а значений нет.
на текущий момент быстрее стандартного поиска по словарю не нашел, буду рад комментариям и по этой части.
Есть ещё set() — он меньше в памяти занимает. Грубо говоря, это словарь, в котором есть только ключи, а значений нет.
продумывал этот вариант, поскольку стажа программирования практически нет, еще не разобрался, даст ли прирост в моем кейсе.
+ критичным является не оптимизация объема занимаемой памяти, а оптимизация процессорного времени.
Массивы в моих задачах большие, но не настолько большие, чтобы не хватило памяти.
+ критичным является не оптимизация объема занимаемой памяти, а оптимизация процессорного времени.
Массивы в моих задачах большие, но не настолько большие, чтобы не хватило памяти.
Первое.
.
.
.
Второе. Поведение отличается от ВПР, что не есть хорошо.
http://prntscr.com/cyoibc
Например, при расширении семантического ядра искусственной семантикой 100% будут дубли. Вместо классического инструмента «удалить дубликаты» я иногда использую ВПР для поиска и удаления таких дублей (причину этого и детали расписывать лень, в данном контексте это не важно).
Полагаю, улучшенная формула должна иметь такое же поведение на массивах, содержащих дубликаты, что и исходный ВПР=)
С единственным ограничением — функция работает корректно только на массиве, сортированном от меньшего к большему.На мой взгляд, если уж написали пользовательскую функцию, так почему бы сразу не включить в код предварительную сортировку массива?
.
.
.
Второе. Поведение отличается от ВПР, что не есть хорошо.
http://prntscr.com/cyoibc
Например, при расширении семантического ядра искусственной семантикой 100% будут дубли. Вместо классического инструмента «удалить дубликаты» я иногда использую ВПР для поиска и удаления таких дублей (причину этого и детали расписывать лень, в данном контексте это не важно).
Полагаю, улучшенная формула должна иметь такое же поведение на массивах, содержащих дубликаты, что и исходный ВПР=)
по 1-му пункту — так и было задумано, если хотите свой вариант — вполне можете переработать код под ваши нужды. По мне это предложение уже чересчур кастомное.
по 2-му — тут интереснее, так не было задумано, буду фиксить вместе с первым параметром, который не принимает вручную добавленные или вычисляемые строки (что хотелось бы и мне в том числе). Апдейт кода выложу.
по 2-му — тут интереснее, так не было задумано, буду фиксить вместе с первым параметром, который не принимает вручную добавленные или вычисляемые строки (что хотелось бы и мне в том числе). Апдейт кода выложу.
апдейт кода выложил, но вышеописанное поведение (скриншот) — корректное при бинарном поиске, поэтому там ничего не корректировалось. И в целом если корректировать, это ухудшит производительность.
Дмитрий, спасибо большое за статью. В принципе фишка с двойным ВПРом очень ускорила пересчёты в файлах.
Подскажите, может вы знаете что-нибудь такого же уровня эффективности для поиска по 2 критериям? У меня сейчас подобный поиск реализован через ИНДЕКС+ПОИСКПОЗ, но работает данная связка омерзительно медленно. Можно как-либо ускорить процесс без добавления столбца с конкатенацией критериальных столбцов в исходную таблицу?
Подскажите, может вы знаете что-нибудь такого же уровня эффективности для поиска по 2 критериям? У меня сейчас подобный поиск реализован через ИНДЕКС+ПОИСКПОЗ, но работает данная связка омерзительно медленно. Можно как-либо ускорить процесс без добавления столбца с конкатенацией критериальных столбцов в исходную таблицу?
ВПР, к сожалению, требует слишком много условий для нормальной работы:
— ключ в первом столбце
— ключ сортированный по возврастанию
— повторый ключа вносят неопределенность
Для составления чего-то, похожего на реляционную базу с контролем ошибок неприменима.
Поэтому
— либо СУММПРОИЗВ — для работы с числовыми результирующими столбами и отбор по любому количеству параметров
— либо связка ПОИСКПОЗ+СЦЕПИТЬ+ИНДЕКС — для опятьже нескольких условий и получения текстового значения (в этом варианте дубли ключа также вносят непореденность)
— либо формула массива для высталения парамета по столбцам, а не только по строкам. Но это довольно медленно
— ключ в первом столбце
— ключ сортированный по возврастанию
— повторый ключа вносят неопределенность
Для составления чего-то, похожего на реляционную базу с контролем ошибок неприменима.
Поэтому
— либо СУММПРОИЗВ — для работы с числовыми результирующими столбами и отбор по любому количеству параметров
— либо связка ПОИСКПОЗ+СЦЕПИТЬ+ИНДЕКС — для опятьже нескольких условий и получения текстового значения (в этом варианте дубли ключа также вносят непореденность)
— либо формула массива для высталения парамета по столбцам, а не только по строкам. Но это довольно медленно
кому как)
первые два пункта — трудно представить как нечто непреодолимое.
последний в моих проектах не актуален (ключи уникальны), и непонятно, как вопрос с неопределенностью решают другие алгоритмы.
Несколько условий — целый отдельный кейс реализации.
первые два пункта — трудно представить как нечто непреодолимое.
последний в моих проектах не актуален (ключи уникальны), и непонятно, как вопрос с неопределенностью решают другие алгоритмы.
Несколько условий — целый отдельный кейс реализации.
1. Не совсем согласен с вами.
.
.
Итого, ВПР проигрывает ИНДЕКС+ПОИСКПОЗ только по требованию «ищу только в первом столбце».
.
.
.
2. Вы не могли бы привести конкретный пример, в каких случаях используете «СЦЕПИТЬ» в этой конструкции:
ВПР, к сожалению, требует слишком много условий для нормальной работы:Если в формулу последним параметром ставить «0» («ЛОЖЬ») то поиск ведется до победного и сортировка для ВПР не нужна.
— ключ сортированный по возврастанию
ВПР, к сожалению, требует слишком много условий для нормальной работы:Так ИНДЕКС+ПОИСКПОЗ при наличие дублей тоже вырнет только первое найденное вхождение.
— повторый ключа вносят неопределенность
.
.
Итого, ВПР проигрывает ИНДЕКС+ПОИСКПОЗ только по требованию «ищу только в первом столбце».
.
.
.
2. Вы не могли бы привести конкретный пример, в каких случаях используете «СЦЕПИТЬ» в этой конструкции:
— либо связка ПОИСКПОЗ+СЦЕПИТЬ+ИНДЕКС — для опятьже нескольких условий и получения текстового значения?
Так делаем, когда надо получить текстовое значение, т.е. без агрегатных функций. Обычно в таких слкчаях наборка небольшая (сотни-тысячи записей), поэтому скорость не критична.
Например, 3 столбца отбора, 1 столбец данных. Добавляем 5-й столбец, который соединяет значения этих 3-х столбцов отбора: = СЦЕПИТЬ(Столбец1;"-"; Столбец2;"-"; Столбец3)
Потом при поиске пишем =Индекс(Столбец4; ПОИСКПОЗ(СЦЕПИТЬ(Условие1;"-"; Условие2;"-"; Условие3); Столбец5;0))
Например, 3 столбца отбора, 1 столбец данных. Добавляем 5-й столбец, который соединяет значения этих 3-х столбцов отбора: = СЦЕПИТЬ(Столбец1;"-"; Столбец2;"-"; Столбец3)
Потом при поиске пишем =Индекс(Столбец4; ПОИСКПОЗ(СЦЕПИТЬ(Условие1;"-"; Условие2;"-"; Условие3); Столбец5;0))
«1. Не сосем согласен с вами. Если в формулу последним параметром ставить «0» («ЛОЖЬ») то поиск ведется до победного и сортировка для ВПР не нужна».
Это понятно. Но тут ведь описывается вундервафля с ускорением в 25 раз. Только кто-то должен не забыть отсортировать данные. Для разовой обработки набора данных — пойдет. Для рабочего файла, которые используется регулярно, да ещё и несколькими пользователями — это выстрел в ногу. Такойже как сводные таблицы.
Кстати, ни автор статьи, ни в тех статья, на которые он ссылается, не указывается, за сколько с задачей справится сводная таблица — это может быть самый простой путь для разовой обработки данных.
Вообще, для разработки именно приложений многократного использования пока лучше всего показывают себя СУММПРОИЗВ (быстрее, но требуют данных одинакового размера) и формулы массивов (они медленне, но позволяют разноразмерные данные) в связке с таблицам (именование области данных с именованными столбцами)
Это понятно. Но тут ведь описывается вундервафля с ускорением в 25 раз. Только кто-то должен не забыть отсортировать данные. Для разовой обработки набора данных — пойдет. Для рабочего файла, которые используется регулярно, да ещё и несколькими пользователями — это выстрел в ногу. Такойже как сводные таблицы.
Кстати, ни автор статьи, ни в тех статья, на которые он ссылается, не указывается, за сколько с задачей справится сводная таблица — это может быть самый простой путь для разовой обработки данных.
Вообще, для разработки именно приложений многократного использования пока лучше всего показывают себя СУММПРОИЗВ (быстрее, но требуют данных одинакового размера) и формулы массивов (они медленне, но позволяют разноразмерные данные) в связке с таблицам (именование области данных с именованными столбцами)
«Итого, ВПР проигрывает ИНДЕКС+ПОИСКПОЗ только по требованию «ищу только в первом столбце».»
Также он проигрывает ещё в критичной зависимости к структуре данных. Добавление одного столбца в таблицу данных может все поломать.
Кстати, в стетьях, на которые ссылается автор, сравниваются в том числе и ВПР и ИНДЕКС+ПОИСКПОЗ. Вторая связка произрывает на 5-10% (на данных в 200 тыс строк 50 сек вместо 45), что не критично, зато поддерживает целостность данных.
Также он проигрывает ещё в критичной зависимости к структуре данных. Добавление одного столбца в таблицу данных может все поломать.
Кстати, в стетьях, на которые ссылается автор, сравниваются в том числе и ВПР и ИНДЕКС+ПОИСКПОЗ. Вторая связка произрывает на 5-10% (на данных в 200 тыс строк 50 сек вместо 45), что не критично, зато поддерживает целостность данных.
зависимость от структуры при желании легко обойти. помимо ВПР есть ГПР — всегда можно ссылаться не на номер столбца, а на индекс его заголовка.
В других случаях можно просто ссылаться на индекс нужного столбца через подсчет ширины массива — при добавлении столбцов будет смещаться и индекс.
Речь вообще идет о феномене бинарного поиска, а не о функциях — индекс или ВПР. И та и та функция умеют искать бинарным поиском по сортированному массиву.
В других случаях можно просто ссылаться на индекс нужного столбца через подсчет ширины массива — при добавлении столбцов будет смещаться и индекс.
Речь вообще идет о феномене бинарного поиска, а не о функциях — индекс или ВПР. И та и та функция умеют искать бинарным поиском по сортированному массиву.
на данных в 200 тыс строк 50 сек вместо 45), что не критично, зато поддерживает целостность данных.
это если делать немного поисков.
А если нужно одновременно в массиве из 1 млн значений найти другие 60.000 — это совершенно другая история. тут нужна сортировка и бинарный.
Тоже всегда пользуюсь Индекс + ПоискПоз, т.к. они универсальны, особенно при работе с таблицами формата 2007 и старше — нет зависимости от местоположения столбцов и их всегда можно менять.
Но, кстати, возникает вопрос — можно ли оптимизировать ПоискПоз?
Но, кстати, возникает вопрос — можно ли оптимизировать ПоискПоз?
ПОИСКПОЗ тоже бывает бинарный, принимает последний аргумент 1 и -1 для сортировки а-я и я-а соответствено.
Поэтому совмещение ВПР и Индекс-ПОИСКПОЗ дает убер-решение, которое такое же быстрое, но в то же время не обладает недостатками ВПР. В статье выше об этом тоже ведется речь, даже выложен пример функции. UDF тоже напишу, попозже, опубликую, если до меня кто-нибудь не опубликует :)
Статья:
http://analystcave.com/excel-vlookup-vs-index-match-vs-sql-performance/
Поэтому совмещение ВПР и Индекс-ПОИСКПОЗ дает убер-решение, которое такое же быстрое, но в то же время не обладает недостатками ВПР. В статье выше об этом тоже ведется речь, даже выложен пример функции. UDF тоже напишу, попозже, опубликую, если до меня кто-нибудь не опубликует :)
Статья:
http://analystcave.com/excel-vlookup-vs-index-match-vs-sql-performance/
Добавил усовершенствование.
https://habrahabr.ru/post/314302/
https://habrahabr.ru/post/314302/
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Упрощаем бинарный поиск в Excel — реализация Double VLOOKUP Trick с помощью UDF