Como acelerar a pesquisa de colunas grandes no Excel?

Question

Eu tenho 2 planilhas em um documento do Excel. Uma planilha que eu quero calcular se um valor está em uma coluna da segunda planilha. A coluna da segunda planilha é uma lista de números de telefone na lista de não fazer chamadas. O objetivo é descobrir quais números de telefone na primeira planilha estão na lista de não chamadas.

Minha fórmula usa a função MATCH() :

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

O problema que estou tendo é que a coluna de dados que a fórmula está olhando é muito grande (~ 100.000 linhas). Eu estou calculando esta fórmula em torno de 25.000 vezes. O resultado é que o Excel é muito lento para abrir, salvar e rolar o arquivo.

Existe uma maneira mais eficaz de fazer esse tipo de pesquisa? Talvez haja uma maneira que eu possa armazenar em cache o não chamar números em uma estrutura de dados?

    

Answer 1

Há uma maneira de acelerar isso significativamente (fator 3.000, veja a explicação abaixo): Se você classificar os dados na coluna C na planilha DNC Worksheet , poderá executar o MATCH sem o 0 no final , ou seja, =MATCH(A2,'DNC Worksheet'!$C:$C) . (Nota: O Excel 2007 e superior é muito inteligente quando se trata de usar a coluna completa, então não é necessário especificar o 100000 aqui!).

A desvantagem inicial dessa abordagem é que você também obterá uma correspondência, mesmo que A2 não esteja incluído na coluna C. No entanto, isso pode ser resolvido usando a seguinte fórmula:

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

Claro, você poderia metade do tempo de cálculo disso, tendo a correspondência em uma célula (digamos, B2) - e então usar isso na próxima célula =IF(INDEX('DNC Worksheet'!$C:$C,B2)=B2,B2,"no match") .

Alguns antecedentes:

Se você fornecer MATCH (ou VLOOKUP ) com o parâmetro 0/FALSE no final, o Excel realizará uma pesquisa exata, isto é, começará com a primeira célula em C2, verificará se ela corresponde. Caso contrário, continua até o próximo até encontrar o valor - ou produz um erro N/A! . Portanto, se você tiver 100.000 dataset, ele terá uma média de 50.000 comparação até que o valor seja encontrado (= n / 2) - portanto, no seu caso, o número total de cálculos é 1.25B!

Se o parâmetro FALSE for omitido, o Excel assume que o intervalo está classificado e aplica um algoritmo de pesquisa binária: inicia no meio do intervalo, ou seja, célula 50.000 e verifica se seu valor é maior ou menor. Supondo que seja maior o valor que você está tentando corresponder, deve estar entre a célula 1 e 50.000. Agora ele verifica novamente no meio deste intervalo, etc. Portanto, será necessária a comparação log2 (n), no seu caso ~ 17. Portanto, o número total de cálculos é de meros 425K, ou seja, calculará 3.000x mais rápido! : -)

Para mais ajustes de leitura / desempenho, recomendo este site !