Excel Fórmula para mostrar números não usados ou ausentes.

[RickJF1976]

Boa noite a todos!

Primeiramente gostaria de me desculpar se postei no local errado, sou novo por aqui e estou aprendendo a usar o site, enfim...
Gostaria muitíssimo de ajuda de vocês.
Criei uma planilha para controle de números digitados e queria uma fórmula para números ausentes.
Acontece que não existe um número correto a ser digitado para cada sequência.

Exemplo:
Sequência 01: (01 05 07 10 33 45 80 90 92 53 88 17 36 22 15 18 23 51 53 57 59 63 66 44 55) 25 números usados e a sequência vai de 1 a 100

Sequência 02: ( 30 230 150 155 80 46 70 90 110 130 180 188 190 199 200 198 36 55 66 77) 20 números usados e a sequência vai de 30 a 200
Sequência 03: ( 20 30 10 18 22 16 50) 7 números usados e a sequência vai de 10 a 50.

 

Ou seja, não existe uma quantidade fixa a ser usada nem uma sequência correta.
Vocês sabem como criar uma fórmula que atenda a esses requisitos? 
A única coisa que sei é que cada sequência de números nunca exceda a 30 números usados. 

 

Podem me ajudar?

Desde já agradeço a ajuda.
Ah, se criarem a fórmula eu faço adaptação para a planilha.

 

Muito Obrigado! 

[deciog]

@RickJF1976 , Bom Dia.

 

Poste um modelo com o resultado esperado, assim podemos testar as formulas

 

Decio

 

[RickJF1976]

Boa noite!

Obrigado pela ajuda.

Segue planilha para que entenda como é a formula.

 

 

Material usado.xlsx

[deciog]

@RickJF1976 , Bom Dia.

 

Qual o resultado esperado pois não consegui entender o resultado onde colocar, tenta digitar o resultado esperado

 

Notei que pintou de vermelho números repetidos

 

voce pede números ausentes onde devo colocar o resultados que são muitos

 

Decio

 

[RickJF1976]

Oi Décio,

Na verdade iria criar uma capa e colocar o resultado de números ausentes lá para entender o que falta.
Por isso que não deixei espaço. Peço até desculpas por não avisar, me perdoe.

Enfim, essa é a ideia.

Ah, permita-me lhe agradecer a ajuda dada.

Obrigado!

[Midori]

@RickJF1976 No caso da coluna #001 a sequencia que deve ser gerada seria assim?

 

 

Se for isso essas fórmulas funcionam no Excel 365 e 2021.

[RickJF1976]

Boa noite Midori!
Isso, agora deixe-me lhe perguntar...
Ela pode ser acrescentada em uma capa, ou seja, vou digitando na planilha e na capa já vai aparecendo os números que faltam?
Se você puder fazer lhe agradeço demais.

Pode ser?

 

Obrigado!

[Midori]

@RickJF1976 Veja se assim resolve, a fórmula é compatível com as versões de Excel que comentei.

 

=CLASSIFICAR(SE(CONT.SE(B6:B34;SEQUÊNCIA(MÁXIMO(B6:B34)-MÍNIMO(B6:B34)-1;;MÍNIMO(B6:B34)+1))=0;SEQUÊNCIA(MÁXIMO(B6:B34)-MÍNIMO(B6:B34)-1;;MÍNIMO(B6:B34)+1);""))

 

[RickJF1976]

Oi Midori, boa noite!
Obrigado pela ajuda, deu erro.
utilizo a versão 2013 do excel e também o libre calc.

Caso queira que eu coloque mais uma vez a tabela aqui basta informar.

fico grato por sua ajuda, viu.

 

 

[Midori]

@RickJF1976 Outra forma de fazer é com macro, assim será compatível com qualquer versão do Excel. Para testar o código selecione a planilha do Acompanhamento e digite Alt+F11 para abrir o editar de macro. Crie um novo módulo no menu Inserir > Módulo e cole o código.

 

Deve ficar assim,

 

 

 

 

Selecione a sub Executa e F5 para rodar. Os ausentes vão aparecer na coluna AH. Se assim resolver, a macro pode ser usada para criar a capa e processar todas as outras colunas, aí é só passar os argumentos para a sub.

 

Sub Executa()
    Call Ausentes([B6:B34], [AH6])
End Sub

Sub Ausentes(Area As Range, Destino As Range)
    Dim Minimo  As Long
    Dim Maximo  As Long
    Dim Numero  As Long
    
    Minimo = WorksheetFunction.Min(Area) + 1
    Maximo = WorksheetFunction.Max(Area) - 1
    
    For Numero = Minimo To Maximo
        If Area.Find( _
            What:=Numero, _
            LookIn:=xlValues, _
            LookAt:=xlWhole) _
        Is Nothing Then
            Destino.Value = Numero
            Set Destino = Destino(2)
        End If
    Next Numero
End Sub

 

[RickJF1976]

@Midori Oi Midori,

Obrigado por sua ajuda, fico feliz em ver pessoas empenhadas em ajudar como você.
Não deu certo não, não apareceu nenhum resultado, não sei o que está havendo.
O código enviado por você deu errado e também a criação da makro. 
não sei como resolver essa questão mas irei continuar testando até encontrar a resolução.
Permita-me lhe agradecer.
Não quero incomodar dando trabalho.
 

[Scofieldgyn]

@RickJF1976

 

Você não conseguiu executar a macro porque os números estão como texto, ai tem duas opções pra resolver, uma é a @Midorimodificar a macro pra capturar o formato texto ou você @RickJF1976converter todas as colunas para números.

[Midori]

@RickJF1976 É como o @Scofieldgyn comentou, não apareceu nenhum número porque a sua lista está no formato texto. Mesmo assim a macro não devia apresentar nenhum erro de execução.

 

Para converter o range para número substitua a Sub Executa por esta,

 

Sub Executa()
    Dim Area As Range
        
    Set Area = [B6:B34]
    
    Area.NumberFormat = "General"
    Area.Value = Area.Value
    
    Call Ausentes(Area, [AH6])
End Sub

 

[RickJF1976]

@MidoriBoa noite Midori!
Deu certo, graças a Deus e a você, fiz a alteração para número e apareceu na coluna AH, como citado.

Se não for pedir muito, me explique como faço para mostrar os números na capa, ah, e para abranger as outras células basta eu alterar o código acrescentando as outras células, é isso?
Obrigado @Scofieldgynpela interação nesse post em informar o que deveria ser alterado.

 

Sub Executa()

Call Ausentes([B6:AB34], [AH6])

End Sub

[RickJF1976]

Pessoal, boa noite!
Consegui adaptar o código e resolvi completamente a planilha.
Agradeço imensamente seus ensinamentos @Midori, foi através deles que solucionei esse problema de minha planilha.

Fico muito grato, buscarei aprender mais e logicamente ajudar com aquilo que sei e aprendi.

Agradeço, que Deus abençoe vocês.

Ah, conseguiu um eterno aluno viu!
Abraço.

 

29 minutos atrás, RickJF1976 disse:

Pessoal, boa noite!
Consegui adaptar o código e resolvi completamente a planilha.
Agradeço imensamente seus ensinamentos @Midori, foi através deles que solucionei esse problema de minha planilha.

Fico muito grato, buscarei aprender mais e logicamente ajudar com aquilo que sei e aprendi.

Agradeço, que Deus abençoe vocês.

Ah, conseguiu um eterno aluno viu!
Abraço.

 

 

 

 

Pessoal, me deparei com outro problema.

Apliquei o código porém quando crio outra planilha o código permanece e os números ausentes também.

como posso tornar esse código específico de cada planilha?

com isso resolvo completamente o problema.

Segue planilha.

Material usado.xlsx

[Midori]

Em 13/01/2023 às 23:30, RickJF1976 disse:

Apliquei o código porém quando crio outra planilha o código permanece e os números ausentes também.

como posso tornar esse código específico de cada planilha?

Como assim específico? Se salvar a planilha no formato xlsm a macro vai ficar no módulo. Sobre os números ausentes, foi um exemplo de como podem ser criados, mas eles podem ser adicionados em qualquer outra aba e também removidos via macro.

[RickJF1976]

Oi Midori,
Deu certo e na verdade me expressei mau.
Na verdade quando eu criava outra tabela já aparecia os números ausentes da tabela ao qual eu tinha aplicado o código.

converti para a extensão que você falou e resolveu.

Grato,