Note: The other languages of the website are Google-translated. Back to English

Como inserir números ou linhas para números sequenciais ausentes no Excel?

Supondo que você tenha uma lista de números sequenciais em uma planilha, mas faltem alguns números na sequência, e agora você precise inserir os números ausentes ou linhas em branco para garantir que a sequência seja completa (mostrada nas imagens a seguir). Como você poderia resolver esse problema rapidamente no Excel?

doc-insert-missing-number1 -2 doc-insert-missing-number2

Insira os números ausentes para a sequência com o recurso Classificar e Remover Duplicados

Insira os números ausentes para a sequência com código VBA

Insira linhas em branco para a sequência ausente com o código VBA

Insira números ausentes ou linhas em branco para a sequência com o Kutools para Excel


seta azul bolha direita Insira os números ausentes para a sequência com o recurso Classificar e Remover Duplicados

Pode ser que você possa encontrar os números que faltam um por um e depois inseri-los, mas é difícil para você identificar a localização dos que faltam se houver centenas de números sequenciais. No Excel, posso usar o recurso Classificar e Remover Duplicados para lidar com essa tarefa.

1. Após o final da lista de sequência, preencha outros números de sequência de 2005023001 a 2005023011. Veja a captura de tela:

doc-insert-missing-number3

2. Em seguida, selecione o intervalo dos dois números de sequência e clique Dados > Classificar de A a Z, veja a captura de tela:

doc-insert-missing-number4

3. E os dados selecionados foram classificados da seguinte forma:

doc-insert-missing-number5

4. Então você precisa remover as duplicatas por clique Dados > Remover Duplicados, e no estalou Remover Duplicados caixa de diálogo, verifique a Coluna nome que você deseja remover as duplicatas, consulte as capturas de tela:

doc-insert-missing-number6 -2 doc-insert-missing-number7

5. Então clique OK, as duplicatas em Coluna A foi excluído e os números em falta na lista de sequência foram inseridos, consulte a captura de tela:

doc-insert-missing-number8


seta azul bolha direita Insira os números ausentes para a sequência com código VBA

Se você acha que há tantas etapas com os métodos acima, aqui também o código VBA pode ajudá-lo a resolver o problema. Faça o seguinte:

1. Segure o ALT + F11 chaves, e abre o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole o seguinte código no Módulo janela.

VBA: insira números ausentes para a sequência

Sub InsertValueBetween()
'Updateby Extendoffice
Dim WorkRng As Range
Dim Rng As Range
Dim outArr As Variant
Dim dic As Variant
Set dic = CreateObject("Scripting.Dictionary")
'On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
num1 = WorkRng.Range("A1").Value
num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
interval = num2 - num1
ReDim outArr(1 To interval + 1, 1 To 2)
For Each Rng In WorkRng
    dic(Rng.Value) = Rng.Offset(0, 1).Value
Next
For i = 0 To interval
    outArr(i + 1, 1) = i + num1
    If dic.Exists(i + num1) Then
        outArr(i + 1, 2) = dic(i + num1)
    Else
        outArr(i + 1, 2) = ""
    End If
Next
With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
    .Value = outArr
    .Select
End With
End Sub

3. Então aperte F5 para executar este código e uma caixa de prompt aparecerá, selecione o intervalo de dados em que deseja inserir os números ausentes (não selecione o intervalo de título), consulte a captura de tela:

doc-insert-missing-number9

4. E, em seguida, clique em OK, os números ausentes foram inseridos na lista de sequência. Veja as capturas de tela:

doc-insert-missing-number1 -2 doc-insert-missing-number2

seta azul bolha direita Insira linhas em branco para a sequência ausente com o código VBA

Às vezes, você só precisa localizar o local dos números que faltam e inserir linhas em branco entre os dados, para que possa inserir as informações conforme necessário. Obviamente, o código VBA a seguir também pode ajudá-lo a resolver esse problema.

1. Mantenha pressionada a ALT + F11 chaves, e abre um Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole o seguinte código no Módulo janela.

VBA: insira linhas em branco para a sequência ausente

Sub InsertNullBetween()
'Updateby Extendoffice
Dim WorkRng As Range
Dim Rng As Range
Dim outArr As Variant
Dim dic As Variant
Set dic = CreateObject("Scripting.Dictionary")
'On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
num1 = WorkRng.Range("A1").Value
num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
interval = num2 - num1
ReDim outArr(1 To interval + 1, 1 To 2)
For Each Rng In WorkRng
    dic(Rng.Value) = Rng.Offset(0, 1).Value
Next
For i = 0 To interval
    If dic.Exists(i + num1) Then
        outArr(i + 1, 1) = i + num1
        outArr(i + 1, 2) = dic(i + num1)
    Else
        outArr(i + 1, 1) = ""
        outArr(i + 1, 2) = ""
    End If
Next
With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
    .Value = outArr
    .Select
End With
End Sub

3. Então aperte F5 para executar este código e uma caixa de prompt será exibida e, em seguida, selecione o intervalo de dados em que deseja inserir linhas em branco para a sequência ausente (não selecione o intervalo de título), consulte a captura de tela:

doc-insert-missing-number9

4. E, em seguida, clique em OK, as linhas em branco foram inseridas para a lista de sequência ausente. Veja as capturas de tela:

doc-insert-missing-number1 -2 doc-insert-missing-number10

seta azul bolha direita Insira números ausentes ou linhas em branco para a sequência com o Kutools para Excel

Aqui, vou apresentar uma ferramenta fácil e útil - Kutools for Excel, Com o seu Encontrar número de sequência ausente recurso, você pode inserir rapidamente o número de sequência ausente ou linhas em branco entre a sequência de dados existente.

Kutools for Excel : com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias

Se você instalou Kutools for Excel, faça o seguinte:

1. Selecione a sequência de dados em que deseja inserir os números ausentes.

2. Clique Kutools > inserção > Encontrar número de sequência ausente, veja a captura de tela:

3. No Encontrar número de sequência ausente caixa de diálogo, verifique Inserindo número de sequência ausente para inserir os números que faltam ou euinserir linhas em branco ao encontrar números de sequência ausentes para inserir linhas em branco conforme necessário. Veja a imagem:

doc-insert-missing-number10

4. E, em seguida, clique em OK botão, e os números de sequência ausentes ou linhas em branco foram inseridos nos dados, consulte as capturas de tela:

doc-insert-missing-number10 2 doc-insert-missing-number10 2 doc-insert-missing-number10

Baixe e teste grátis Kutools para Excel agora!


seta azul bolha direita  Demonstração: insira números ausentes ou linhas em branco para a sequência com o Kutools para Excel

Kutools for Excel: com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias. Baixe e teste grátis agora!

Artigo relacionado:

Como identificar a seqüência de números ausentes no Excel?


As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2021 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Recursos completos de avaliação gratuita de 30 dias. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
parte inferior da aba do escritório
Comentários (10)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
Obrigado ! Ótimo roteiro! Como posso modificar este script se eu disser que precisamos processar não apenas a coluna ID + coluna NAME, mas a coluna ID + coluna NAME + coluna NOVA? Como posso adicionar novas colunas neste script?
Este comentário foi feito pelo moderador no site
A seguir está a macro modificada para incluir uma coluna adicionada - Outro ponto importante é que, quando solicitado a selecionar o intervalo, você deve selecionar apenas a primeira coluna - isso me levou algumas horas! espero economizar o tempo dos outros

SubInserirValorEntre()
'Atualizar por Extendoffice
Dim WorkRng As Range
Dim Rng como intervalo
Dim outArr As Variant
Dim dic como variante
Set dic = CreateObject("Scripting.Dictionary")
Dim dic2 como variante
Set dic2 = CreateObject("Scripting.Dictionary")

'Em Erro Continuar Próximo
xTitleId = "KutoolsforExcel"
Definir WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
num1 = WorkRng.Range("A1").Value
num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
intervalo = num2 - num1
ReDim outArr(1 para intervalo + 1, 1 para 3)
Para cada Rng em WorkRng
dic(Rng.Value) = Rng.Offset(0, 1).Value
dic2(Rng.Value) = Rng.Offset(0, 2).Value
Seguinte
Para i = 0 Para intervalo
outArr(i + 1, 1) = i + num1
Se dic.Existe(i + num1) Então
outArr(i + 1, 2) = dic(i + num1)
outArr(i + 1, 3) = dic2(i + num1)
Outro
outArr(i + 1, 2) = ""
outArr(i + 1, 3) = ""

Se acabar
Seguinte
Com WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
.Value = outArr
.Selecionar
Terminar com
End Sub
Este comentário foi feito pelo moderador no site
isso funcionou e foi muito fácil concluir a tarefa. Obrigada.
Este comentário foi feito pelo moderador no site
Muito obrigado. Como faço para alterar o script se os incrementos forem apenas 0.02 e não 1 Isso é para o script InsertNullBetween()
Este comentário foi feito pelo moderador no site
E se eu quiser selecionar 6 colunas e, em seguida, verificar a 1ª coluna para datas e se as datas estiverem faltando, adicione uma linha (células em branco) para todas as 6 colunas
Este comentário foi feito pelo moderador no site
Oi, eu quero usar "Inserindo número de sequência ausente", mas não está suportando se o não. de dígitos são mais de 12 você pode ajudar?
Este comentário foi feito pelo moderador no site
Eu quero usar o recurso "Inserindo número de sequência ausente", mas não é compatível com dígitos maiores que 12? existem muitos conjuntos em que eu quero inserir a sequência entre (é um dígito alfanumérico) você pode ajudar
Este comentário foi feito pelo moderador no site
E se eu quiser selecionar 6 colunas e, em seguida, verificar a 1ª coluna para datas e se as datas estiverem faltando, adicione uma linha (células em branco) para todas as 6 colunas
Este comentário foi feito pelo moderador no site
Obrigado incrível
Este comentário foi feito pelo moderador no site
Estou tentando usar o VBA para números sequenciais. Eu tenho várias colunas ao lado dos números dos quais números também. Ou seja
1. Medidor HL 34
2. Medidor de Água HL 40
4. HL CO2metro 24

Quando eu uso o código, ele funciona para as 3 primeiras colunas, mas fica confuso se eu incluir a 4ª coluna, pois também inclui números.
Como posso alterar o código para garantir que os números na coluna 4 permaneçam os mesmos?
Não há comentários postados aqui ainda
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL