Identifique lacunas em uma sequência em uma célula - Excel

Question

Tenho uma lista de itens em minha planilha Excel que estão numerados, junto com um número total, da seguinte forma:

Exemplo:

   | Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1  | 01 - 13              |         ?          | 20
2  | 02 - 09              |         ?          | 10
3  | 01 - 04, 06 - 11     |         ?          | 11

Agora, quero que a coluna B exiba os números dos itens ausentes, assim:

   | Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1  | 01 - 13              | 14 - 20            | 20
2  | 02 - 09              | 01, 10             | 10
3  | 01 - 04, 06 - 11     | 05                 | 11

Consegui criar uma fórmula prolixa para casos como na linha 1, mas isso só funciona verificando os dois últimos dígitos de uma célula na coluna A, subtraindo-o do valor da coluna C nessa linha e, em seguida, preenchendo a coluna B com o resultado.

(basicamente algo assim (com alguns casos especiais não afetados pelo "problema de lacuna" em questão)): $C1-Right($A1;2)&" - "&$C1)

Mas minha abordagem (obviamente) não consegue lidar com as lacunas como nas linhas 2 e 3, e quero saber se existe uma maneira de resolver meu problema.

PS: Eu não tinha certeza de qual título dar a esse problema, então peço desculpas se for enganoso.

Answer 1

Seu problema pode ser resolvido usando a função TREND:

1. Tomando a primeira amostra de dados na Coluna A, você precisa criar1ª sequência de 1 a 13e2º de 1 a 20.
2. Escreva esta fórmula de matriz em Cell C1, termine com Ctrl+Shift+Entere preencha.

{=TREND(A1:A13,B1:B13,B14:B20)}

1. Você obtém séries de 14 to 20.

2. Insira D1esta fórmula para obter o arquivo 1st Cell value. =INDEX(C1:C7,MATCH(TRUE,INDEX((C1:C7<>0),0),0))

3. Insira esta fórmula D2para obter last Cell value.

=LOOKUP(2,1/(C1:C7<>""),C1:C7)

4. Em Cell, D4digite este =D1&"-"&D2, você obtém 14-20.

Observação: Para a segunda amostra de dados, você precisa criar 2 séries.

1- 2 to 9.

2- 1 to 10.

A terceira amostra de dados precisa de 3 séries.

1- 1 to 4.

2- 6 to 11.

3- 1 to 11.

1. Repita as etapas mostradas acima para outras amostras de dados.
2. Ajuste as referências de células na fórmula conforme necessário.