Baixe a apostila de exemplo
Este tutorial demonstrará como listar os números ausentes em um intervalo.
Listar números ignorados na sequência
Para encontrar os números ausentes em uma lista, podemos usar a seguinte fórmula:
| 1 | = PEQUENO (SE (CONTAR ($ B $ 3: $ B $ 8, LINHA ($ 1: $ 6)) = 0, LINHA ($ 1: $ 6), ""), LINHA (B1)) |
Notas:
- Esta é uma fórmula de matriz! Ao usar o Excel 2022 e anterior, você deve inserir a fórmula pressionando CTRL + SHIFT + ENTER. Você saberá que a fórmula foi inserida corretamente quando vir colchetes ao redor da fórmula.
- Esta fórmula só funciona com números inteiros positivos (inteiros).
Como funciona a fórmula?
Primeiro, a função ROW é usada para gerar uma matriz de números:
| 1 | {1;2;3;4;5;6} |
Na verdade, você pode até inserir essa matriz de números manualmente:
| 1 | = PEQUENO (SE (CONTAR ($ B $ 3: $ B $ 8, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6}, ""), ROW (B1)) |
Em seguida, a função CONT.SE contará os valores “se” eles corresponderem a um valor encontrado na matriz.
Para visualizar isso, primeiro converteremos o intervalo $ B $ 3: $ B $ 8 para sua matriz de valores {3; 2; 0; 6; 0; 5}:
| 1 | = PEQUENO (SE (CONT.SE ({3; 2; 0; 6; 0; 5}, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6 }, ""), ROW (B1)) |
Em seguida, realizaremos a operação CONT.SE:
| 1 | = PEQUENO (SE ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), LINHA (B1)) |
Se um valor não for encontrado, 0 será emitido. A função IF verifica se o valor NÃO foi encontrado e, em caso afirmativo, retorna o valor da matriz, caso contrário, sai em branco:
| 1 | = PEQUENO ({1; ""; ""; 4; ""; ""}, LINHA (B1)) |
Por último, a função SMALL retorna o menor valor ausente na célula selecionada.
| 1 | = PEQUENO ({1; ""; ""; 4; ""; ""}, {1}) |
| 1 | ={1} |
Gorjeta:
Para evitar o #NUM! erro, envolva sua fórmula com a função IFERROR:
| 1 | = IFERROR (PEQUENO (SE (CONTAR. (B3: B8, LINHA (1: 6)) = 0, LINHA (1: 6), ""), LINHA (B1)), "") |
Listar números ignorados em sequência no Planilhas Google
Todos os exemplos explicados acima funcionam da mesma forma nas planilhas do Google e no Excel.
