Localiza entradas ausentes de uma lista de números sequenciais em uma coluna?

Question

Eu tenho uma planilha do Excel contendo os resultados de 66.000 casos de teste. Ou, pelo menos, deveria ...

Agora, como eu estava executando os testes de forma assíncrona e continuei parando e iniciando, tive a previsão de garantir que os números de teste fossem colocados na saída. Agora, tendo usado o recurso Remover Duplicados do Excel 2007 com base nesses números de teste, descubro que tenho 65997 linhas de dados. Então, três deles estão faltando.

O trabalho aqui é localizar os números das tarefas em falta.

Os números de teste estão na coluna A, em ordem crescente e não há duplicações garantidas. Outros dados são colocados em outras colunas, e estes devem permanecer com o número do teste.

  |    A
--+---------
1 | testNum
2 | 1
3 | 2
4 | 3
5 | ...

Suponha que há muitos casos de teste para fazer essa pesquisa manualmente , pois tenho outro conjunto de dados de mais de um milhão de itens que precisarei fazer um trabalho semelhante em breve.

Eu poderia resolver isso com o VBA, mas me pergunto se há uma solução mais direta que estou perdendo?

    

Answer 1

Supondo que seus dados estejam na Coluna A e iniciem na linha 2, selecione um intervalo em uma coluna livre começando na linha 2 e incluindo pelo menos tantas linhas quanto você deve ter resultados (isso é importante : se o seu intervalo incluir menos células do que a sua sequência total, a listagem dos resultados em falta será truncada) ¹ . Na barra de fórmulas - não diretamente nas células! - insira a seguinte fórmula:

=IFERROR(
   SMALL(
     IF(
       ISNA(MATCH(ROW(A1:A66000)-1,A2:A66001,0)),
       ROW(A1:A66000)-1,
     ""),
   ROW()-1),
 "")

- note que isso precisa ser escrito em uma linha ; o recuo é apenas por uma questão de legibilidade aqui. Guarde-o como uma fórmula de matriz pressionando Ctrl + Shift + Enter . Se você fez isso corretamente, a fórmula será exibida entre chaves e o Excel irá reclamar quando você tentar editar células individuais no intervalo da matriz.

Para os valores na coluna A da captura de tela abaixo, a fórmula armazenada como uma fórmula de matriz em B2:B21 (20 linhas porque a sequência completa seria de 1 a 20) retornará os seguintes valores na coluna B:

Como você pode ver, estes são os números que faltam na sequência (destacados na captura de tela).

1. você pode errar no lado generoso ao selecionar o intervalo a ser aplicado, contanto que a referência ROW(A1:AXXXXX) inclua exatamente um número de linhas igual à sua sequência total (66000 linhas no exemplo, portanto A1:A66000 ).

Answer 2

Em uma coluna vazia (suponha que Z ) comece na linha 2, coloque uma fórmula e copie para todas as linhas usadas

=A2=A1+1

Pesquise na coluna Z por FALSE . vai encontrar o teste na linha após o número que falta

Você também pode tentar

= SE (A2 = A1 + 1, "", A1 + 1)

    

Answer 3

Esta solução requer duas colunas auxiliares. Na Coluna B, preencha os números consecutivos de 1 a 66.000. Em C2 (na primeira linha de dados), digite

=IF(A2=B2,0,1)

Em C3, digite

=IF(B3=OFFSET(A3,-1*SUM(C$2:C2),0),0,1)

Preencha o C3 para C66001.

A partir daqui, você pode filtrar B: C para registros com um valor de 1 na coluna C. Isso mostrará os números que faltam.

OR

Se você quiser se interessar na última etapa, use essa fórmula de matriz e preencha o que precisar:

=IFERROR(SMALL(IF($C$2:C$32<>0,$B$2:$B$32),ROW()-1),"")

Digite a fórmula com Ctrl + Shift + Enter. Este exemplo foi inserido na linha 2. Você precisará ajustar o ROW()-1 na fórmula tal que isso é igual a 1 para a primeira entrada. Por exemplo, se você iniciar sua lista de números ausentes no E5, será necessário alterá-lo para ROW()-4 . [Obrigado a @kopischke por este método: link ]