Как ускорить поиск больших столбцов в Excel?

Question

У меня есть 2 листа в документе Excel. На одном листе я хочу вычислить, находится ли значение в столбце второго листа. Столбец второго листа - это список телефонных номеров в списке «Не звонить». Цель состоит в том, чтобы найти, какие номера телефонов на первом листе находятся в списке не звонить.

Моя формула использует функцию MATCH() :

=MATCH(A2, 'DNC Worksheet'!$C$2:$C$100000, 0)

У меня проблема в том, что столбец данных, который просматривает формула, очень большой (~ 100 000 строк). Я вычисляю эту формулу около 25 000 раз. В результате Excel очень медленно открывает, сохраняет и прокручивает файл.

Есть ли более эффективный способ сделать этот вид поиска? Может быть, есть способ, которым я могу кэшировать номера не звонить в структуре данных?

Answer 1

Существует способ значительно ускорить это (коэффициент 3000, см. Пояснение ниже): если вы сортируете данные в столбце C на рабочем листе DNC Worksheet , вы можете запустить MATCH без 0 в конце, то есть =MATCH(A2,'DNC Worksheet'!$C:$C) . (Примечание: Excel 2007 и более поздние версии достаточно умны, когда дело доходит до использования полного столбца, поэтому нет необходимости указывать здесь 100000!).

Первоначальный недостаток этого подхода заключается в том, что вы также получите совпадение, даже если A2 не включен в столбец C. Однако это можно решить с помощью следующей формулы:

=IF(INDEX('DNC Worksheet'!$C:$C,MATCH(A2,'DNC Worksheet'!$C:$C))=A2,MATCH(A2,'DNC Worksheet'!$C:$C),"no match")

Конечно, вы можете наполовину вычислить это время, поместив совпадение в одну ячейку (скажем, B2), а затем использовать его в следующей ячейке =IF(INDEX('DNC Worksheet'!$C:$C,B2)=B2,B2,"no match") .

Немного предыстории:

Если вы предоставите MATCH (или VLOOKUP) с параметром 0/FALSE в конце, Excel выполнит точный поиск, то есть начнет с первой ячейки в C2, проверит, соответствует ли она. Если нет, он продолжается до следующего, пока не найдет значение или не получит N/A! ошибка. Следовательно, если у вас есть 100 000 наборов данных, то в среднем потребуется 50 000 сравнений, пока не будет найдено значение (= n/2) - поэтому в вашем случае общее количество вычислений равно 1,25 B!

Если параметр FALSE пропущен, Excel предполагает, что диапазон отсортирован, и применяет двоичный алгоритм поиска: он начинается в середине диапазона, то есть в ячейке 50 000, и проверяет, является ли его значение большим или меньшим. Предполагая, что оно больше, значение, которое вы пытаетесь найти, должно быть между ячейкой 1 и 50000. Теперь он снова проверяет середину этого диапазона и т.д. Следовательно, для сравнения потребуется log2(n), в вашем случае ~ 17. Таким образом, общее количество вычислений составляет всего 425 КБ, то есть оно будет вычисляться в 3000 раз быстрее! :-)

Для дальнейшего чтения / настройки производительности, я рекомендую этот сайт !