desempenho do excel: Lookup Vs Getpivotdata

Question

Eu construo uma planilha do Excel 2007 que contém uma tabela maior com dados de origem (cerca de 500.000 linhas e 10 colunas). Eu preciso extrair dados desta tabela grande para minha análise. Para extrair e agregar dados, geralmente uso as funções sumif, vlookup / hlookup e index + match.

Recentemente, aprendi sobre a existência da função getpivotdata, que permite extrair dados de uma tabela dinâmica. Para poder usá-lo, primeiro preciso converter minha tabela de origem grande em uma tabela dinâmica e depois disso, posso extrair dados usando a função getpivotdata.

Você esperaria uma melhoria de desempenho se eu usasse getpivotdata para extrair e agregar dados? Eu esperaria que, dentro do objeto Pivot subjacente, os valores agregados sejam pré-calculados e, portanto, o desempenho seria melhor.

Se o desempenho fosse melhor, há alguma razão para não seguir essa abordagem? Para ser claro, não há necessidade de atualizar a tabela dinâmica porque ela contém dados de origem (que estão localizados no início da cadeia de cálculo).

    

Answer 1

Eu fiz alguns testes de desempenho em um desktop dual core de 2.33 GHz e 2 GB de RAM com Excel 2007.

A pesquisa foi feita em uma tabela com 241.000 registros. Os resultados são (o mais rápido primeiro e o mais lento por último):

1. Com a função index-match em uma lista classificada , o número de pesquisas por segundo foi: 180.000 !! (baseado em 1.440.000 pesquisas em 8 segundos). Mais informações sobre como implementar pesquisas classificadas no Excel podem ser encontradas aqui e desça até a seção INDEX-MATCH em uma fórmula, dados ordenados

2. Com a função getpivotdata , o número de pesquisas por segundo foi: 6.000 (com base em 250.000 consultas em 40 segundos)

3. Com a função getpivotdata usando uma sintaxe de cadeia de argumento único muito flexível (consulte aqui ) o número de pesquisas por segundo era: 2.000 (com base em 250.000 consultas em 145 segundos)

4. Com a função index-match em uma lista não classificada , o número de pesquisas por segundo foi: 500 (com base em 20.000 pesquisas em 35 segundos)

Os resultados não são alterados quando a função de pesquisa se refere a uma Tabela de dados em vez de um intervalo nomeado.

Então, para responder a pergunta. As pesquisas por getpivotdata são cerca de 10 vezes mais rápidas que a pesquisa de correspondência de índice regular, mas a melhor melhoria de desempenho é obtida classificando os dados de origem. A classificação de seus dados de origem pode tornar sua pesquisa 400 vezes mais rápida.

    

Answer 2

Fazer as pesquisas com o VBA (usando um dicionário) é de longe o caminho mais rápido. Veja isto: link

    

Answer 3

Usando GetPivotData só lhe dá acesso ao que estiver visível no relatório de Tabela Dinâmica. Se você é o único usuário desta planilha, esta pode ser uma abordagem viável para você.

Se você puder criar o Pivot para fazer a maioria de suas agregações para você, usar GetPivotData será mais rápido.

Eu não testei o desempenho de GetPivotData, mas seria de esperar que fosse mais lento do que uma pesquisa / correspondência de pesquisa binária em dados ordenados.

    

Answer 4

Eu tenho o mesmo problema diariamente. Grande número de linhas em várias tabelas de dados no Excel.

Atualmente, a única solução que torna as tabelas extremamente grandes utilizáveis é exportá-las para um servidor de banco de dados e fazer / gravar consultas SQL para fazer o Sumif, o Vlookups e a agregação

Você pode usar o Excel para criar as consultas SQL

Ao longo dos anos, tenho exportado planilhas / tabelas para " MySQL " e " MS SQL Server express " e, em seguida, conecte-se a eles com o Excel e escreva consultas SQL

O servidor faz o Processamento mais rápido que o excel e se o banco de dados estiver em um servidor diferente, o desempenho aumenta, já que ele não está usando os recursos do seu PC para fazer os cálculos.

Existem outros benefícios para essa solução também.

Como automação ETL e o Compartilhamento de uma string de conexão em vez de uma planilha "BIG".