Excel COUNTIF Slow

Question

Estou tendo um problema estranho no excel que não corri antes. Eu tenho uma planilha de excel que estou tentando extrair alguns registros específicos, exigindo que eu saiba de duplicatas. Eu estou usando um COUNTIF para filtrar minhas duplicatas, em seguida, puxando com base em outros filtros; no entanto, o COUNTIF parece estar desligando o excel. Eu descrevi meu processo abaixo.

1. Crie uma coluna com = COUNTIF (A: A, A2)
2. Copiar a fórmula na coluna
3. Salvar a planilha (trava / demora para concluir) ou Copiar coluna e colar valores (trava / demora para terminar

Eu fiz isso no Excel 2010 e 2013 com os mesmos resultados em dois computadores diferentes. A planilha contém apenas 172000 linhas. A fórmula executa bem para obter os valores, mas trava quando tento salvar ou copiar e colar os valores da coluna. Sem essa coluna, a planilha se comporta normalmente. Eu nunca encontrei isso antes.

Obrigado antecipadamente!

    

Answer 1

Tente o seguinte:

1. Desativar cálculos automáticos. (Guia Fitas da faixa de opções, grupo Cálculo, menu suspenso Opções de cálculo, manual)
2. Desativar "Recalcular pasta de trabalho antes de salvar" (guia Opções, Fórmulas, seção Opções de cálculo)
3. Altere a referência à coluna A para uma referência a um intervalo de nome dinâmico .

Answer 2

Por que não apenas aplicar um filtro em vez de usar um countif() ?

1. Selecione seus dados, vá para Data - Sort & Filter - Advanced para colocar um filtro avançado.
2. Filtre a lista, no local
3. Selecione a tabela inteira como List range
4. Selecione a coluna A como Criteria range
5. Marque a caixa ao lado de Somente registros exclusivos

Answer 3

Tente usar uma fórmula de matriz, ela deve ter um desempenho significativamente melhor.

1. Se a sua fórmula é a coluna b, então: selecione B2: B172000
2. Digite =COUNTIF($A$1:$A$172000,A1:A172000)
3. Pressione Ctrl-Shift-Enter

Answer 4

Você poderia fazer isso de uma maneira diferente para resolver seu problema - Use uma Tabela Dinâmica na Coluna A. Suponho que você tenha um cabeçalho em A1 e os dados se estendam de A2 para alguma linha variável além de A2.

1. Realce a coluna A. Clique em Inserir, Tabela dinâmica.
2. Selecione o local de destino da sua Tabela dinâmica e clique em OK.
3. Na caixa de diálogo "Campos de Tabela Dinâmica" (não sei exatamente como isso se parece em 2010; estou usando 2013), arraste o cabeçalho para a coluna A na caixa "Linhas" e a caixa "Valores" .
4. Na minha instância do Excel, os valores para o cabeçalho padrão contam. Caso contrário, clique na linha na caixa de cabeçalho "soma / conta / média / ?? ... do cabeçalho" e clique em "Configurações do campo de valor". Em seguida, em "Resumir campo de valor por", selecione Contagem.

Você pode ativar o cálculo automático. Tabelas dinâmicas devem ser atualizadas explicitamente. Experimente e veja se funciona para você.

    

Answer 5

Outra opção que você pode examinar é copiar os dados (somente valores) para uma nova pasta de trabalho e verificar se o problema de desempenho ainda existe. Pode ser que a pasta de trabalho esteja corrompida de alguma forma.

    

Answer 6

1. Tabela dinâmica com a coluna na qual você está tentando encontrar duplicatas.

2. Conte o número de vezes que seu valor é exibido.

3. Copie qualquer coisa com contagem > 1 para outra folha

4. Use essa planilha como uma tabela VLOOKUP para marcar duplicatas na planilha original.

5. Copiar / colar valores do seu VLOOKUP (porque a fórmula é volátil e você não deseja que o Excel seja recalculado toda vez que uma alteração é feita).

6. Filtre por duplicatas.

A operação Vlookup ainda leva um pouco de tempo, mas é significativamente mais rápida do que COUNTIF.

    

Answer 7

Descobri que minha contagem de countif = COUNTIF ($ E $ 2: $ E $ 111111, E2) é extremamente lenta, por isso fiz o seguinte,

Dinamize seus dados

Coloque na linha o ID que você deseja contar Coloque em valores a contagem do ID que você quer contar

Volte para seus dados e insira uma coluna e crie um índice e uma correspondência que corresponda à coluna do código nos dados à coluna do seu pivô com o código nas linhas.

Em seguida, indexe de volta a contagem da coluna "id" no seu pivot original seria: = COUNTIF ($ E $ 2: $ E $ 111111, E2)

usando índice e correspondência

= ÍNDICE (Pivot! B: B, MATCH (Dados! E: E, Pivô! A: A, 0))

Isso acelerou os cálculos massivamente

Obrigado

    

Answer 8

Eu esbarrei no mesmo problema e procurei por solução neste thread. Minha fórmula na coluna B estava seguindo: = COUNTIF ($ A1 $: A, A2)

para que a contagem de cada nova linha copiada sempre comece do início do conjunto de dados.

O countif leva algum tempo (na minha planilha + 100k linhas), mas fez o trabalho. Meu Excel falha se eu quiser Copiar como valores , e eu tenho um computador rápido com 16gig de RAM.

Então eu acabei de descobrir a solução mais simples! Adicione uma coluna ao lado da coluna de fórmula (por exemplo, coluna C) e faça uma fórmula simples fazendo referência à coluna B, como = B1 * 1 ou = VALUE (B1) e copie até o fim. Depois disso, basta selecionar essa coluna de fórmula simples e Copiar como valores .

Depois que os valores da Coluna C forem copiados, você poderá excluir a coluna fórmula pesada original OU criar uma nova planilha do Excel com apenas os valores colados nela.

    

Answer 9

Eu tenho experimentado o mesmo problema com uma fórmula CountIf em uma tabela com vários milhares de linhas. Seguindo essas etapas, consegui reduzir os tempos para menos da metade (de ~ 96 segundos para menos de 40 segundos):

1. Criamos uma coluna adicional com numeração sequencial (apenas para possibilitar a classificação).
2. Ordenou a coluna usada no CountIf conforme necessário (ascendente ou descendente)
3. Calculado o CountIf
4. Converteu os resultados do CountIf de fórmulas para valores.
5. Ordenou os dados de volta usando a coluna de numeração sequencial (mencionada no passo 1).

No meu caso, eu estava usando o CountIf para obter o número da linha de um valor na lista (como o row_number () do SQL). A lista que usei para executar as etapas tinha valores duplicados.