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

Suponha que você tenha uma lista de números sequenciais em uma planilha, mas existem alguns números ausentes na sequência, e agora você precisa inserir os números ausentes ou linhas em branco para garantir que a sequência fique completa (conforme mostrado nas capturas de tela a seguir). Como você poderia resolver rapidamente esse problema no Excel?

Inserir números ausentes para a sequência com a função Classificar e Remover Duplicados Inserir números ou linhas em branco para a sequência com Kutools para Excel Inserir números ausentes para a sequência com código VBA Inserir linhas em branco para sequências ausentes com código VBA

Inserir números ausentes para a sequência com a função Classificar e Remover Duplicados

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

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

2. Em seguida, selecione o intervalo das duas sequências de números e clique em Dados > Classificar A a Z, veja a captura de tela:

3. E os dados selecionados foram classificados conforme a captura de tela a seguir:

4. Em seguida, você precisa remover os duplicados clicando em Dados > Remover Duplicados, e na caixa de diálogo Remover Duplicados que aparece, marque o nome da Coluna que deseja remover os duplicados, veja as capturas de tela:

5. Em seguida, clique em OK, os duplicados na Coluna A foram excluídos, e os números ausentes na lista de sequências foram inseridos, veja a captura de tela:

Inserir números ou linhas em branco para a sequência com Kutools para Excel

Aqui, vou apresentar uma ferramenta fácil e prática - Kutools para Excel, com seu recurso Encontrar Número Sequencial Ausente, você pode inserir rapidamente o número sequencial ausente ou linhas em branco entre a sequência de dados existente.

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

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

2. Clique em Kutools > Inserir > Encontrar Número Sequencial Ausente, veja a captura de tela:

3. Na caixa de diálogo Encontrar Número Sequencial Ausente, marque Inserindo número sequencial ausente para inserir os números ausentes ou Inserindo linhas em branco ao encontrar números sequenciais ausentes para inserir linhas em branco conforme necessário. Veja a captura de tela:

4. E então clique no botão OK, e os números sequenciais ausentes ou linhas em branco serão inseridos nos dados, veja as capturas de tela:

Baixe e experimente gratuitamente o Kutools para Excel agora!

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

Se você achar que há muitas etapas nos métodos acima, aqui também há um código VBA que pode ajudá-lo a resolver este problema. Por favor, faça o seguinte:

1. Mantenha pressionadas as teclas ALT + F11, e abrirá a janela Microsoft Visual Basic for Applications.

2. Clique em Inserir > Módulo, e cole o seguinte código na janela Módulo.

VBA: inserir 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. Em seguida, pressione a tecla F5 para executar este código, e uma caixa de prompt aparecerá, por favor, selecione o intervalo de dados onde deseja inserir os números ausentes (não selecione o intervalo do título), veja a captura de tela:

4. E então clique em OK, os números ausentes terão sido inseridos na lista de sequências. Veja as capturas de tela:

Inserir linhas em branco para sequências ausentes com código VBA

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

1. Mantenha pressionadas as teclas ALT + F11, e abrirá uma janela Microsoft Visual Basic for Applications.

2. Clique em Inserir > Módulo, e cole o seguinte código na janela Módulo.

VBA: inserir linhas em branco para sequências ausentes

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. Em seguida, pressione a tecla F5 para executar este código, e uma caixa de prompt será exibida, e então selecione o intervalo de dados onde deseja inserir linhas em branco para a sequência ausente (não selecione o intervalo do título), veja a captura de tela:

4. E então clique em OK, as linhas em branco terão sido inseridas para a lista de sequências ausentes. Veja as capturas de tela:

Artigo relacionado:

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