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
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?
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins