Como encontrar rapidamente datas ausentes de uma lista no Excel?
Este tutorial apresenta várias maneiras de detectar e repor essas datas ausentes de forma eficiente no Excel:
Encontrar datas ausentes com Formatação Condicional
Encontrar datas ausentes com fórmula
Encontrar e preencher datas ausentes com Kutools para Excel
Usar VBA para identificar e inserir automaticamente datas ausentes
Destacar datas ausentes usando uma Tabela Dinâmica
Encontrar datas ausentes com Formatação Condicional
Uma abordagem para identificar lacunas na sua lista de datas é aproveitar a Formatação Condicional do Excel. Esse método destaca visualmente as células onde uma data está faltando em uma sequência, facilitando a identificação de descontinuidades à primeira vista.
1. Selecione o intervalo que contém suas datas, depois vá para Página Inicial > Formatação Condicional > Nova Regra. Veja a captura de tela:
2. Na Nova Regra de Formatação caixa de diálogo, escolha Use uma fórmula para determinar quais células formatar abaixo da seção Selecione um Tipo de Regra . Insira a seguinte fórmula: =A2<>(A1+1) (onde A1 é a primeira data e A2 é a próxima data na sua lista). Veja a captura de tela:
3. Clique no botão Formatar para abrir a Formatar Células caixa de diálogo. Sob a aba Preenchimento escolha uma cor para destacar as datas ausentes. Veja a captura de tela:
4. Após configurar a formatação, clique OK duas vezes para aplicar. Agora, as células onde há uma data ausente na sequência serão destacadas.
Observação: A data final da sua lista também pode ser destacada, pois essa fórmula verifica o que vem após cada data. Este método é especialmente útil para revisar rapidamente grandes conjuntos de dados, mas não preenche automaticamente as datas ausentes.
Encontrar datas ausentes com fórmula
Outra abordagem prática é usar uma fórmula do Excel para ajudar a identificar quaisquer lacunas diretamente na sua tabela. Este método cria uma nova coluna para indicar se um dia está faltando após cada data, tornando-o adequado para rastrear registros de presença, cronogramas de projetos ou registros de inventário.
Em uma coluna em branco ao lado da sua lista de datas (digamos, célula B1 se sua lista começar em A1), insira a fórmula: =SE(A2=A1+1;"";"Próximo dia ausente"). Pressione Enter, depois arraste a alça de preenchimento automático para baixo para copiar a fórmula ao longo de todas as datas. Veja as capturas de tela:
Sempre que você vir "Próximo dia ausente", saberá que há uma lacuna em sua lista. Essa é uma maneira fácil e visual de revisar as datas ausentes e pode ser especialmente útil se você quiser filtrar ou processar ainda mais as lacunas identificadas.
Observação: Assim como no método anterior, a fórmula marcará a linha após a última data (já que não há próxima data), que você pode ignorar ou limpar se não for necessário.
Encontre e preencha datas ausentes com Kutools para Excel
Para aqueles que usam Kutools para Excel, há um recurso embutido que pode encontrar e até preencher rapidamente datas ou números de sequência ausentes para você. Isso é especialmente útil quando você precisa não apenas localizar lacunas, mas também completar automaticamente seus dados para cálculos ou auditorias precisas.
Após instalar gratuitamente Kutools para Excel, siga os passos abaixo:
1. Selecione a lista de datas que deseja analisar, depois vá para Kutools > Inserir > Encontrar Número de Sequência Ausente. Veja a captura de tela:
2. No Encontrar Número de Sequência Ausente diálogo, você pode escolher entre várias opções, como encontrar ou inserir números ausentes, destacar ou criar uma coluna marcadora. Veja a captura de tela:
3. Após confirmar sua escolha, clique OK. Um aviso exibirá o número de datas ausentes encontradas. Veja a captura de tela:
4. Clique OK para concluir. Agora sua lista mostrará, ou até preencherá, as datas ausentes dependendo da opção selecionada. Essa abordagem é conveniente para grandes conjuntos de dados e minimiza erros de verificação manual ou má colocação de fórmulas.
| Inserir número de sequência ausente | Inserir linhas em branco ao encontrar números de sequência ausentes |
 |  |
| Inserir nova coluna com marcador ausente a seguir | Preencher cor de fundo |
 |  |
Essa ferramenta pode economizar tempo significativo e é especialmente benéfica para demonstrações financeiras, dados de presença ou qualquer cenário onde seja necessário um registro contínuo de datas. Certifique-se de que sua lista esteja classificada por data para obter melhores resultados.
Usar VBA para identificar e inserir automaticamente datas ausentes
Se você estiver trabalhando com listas de datas longas ou frequentemente atualizadas e quiser que o processo seja totalmente automatizado, pode usar uma macro VBA personalizada no Excel. Esse método examina sua coluna de datas, encontra datas ausentes na sequência e insere as datas ausentes como novas linhas diretamente na sua lista.
Isso é particularmente útil para grandes conjuntos de dados, relatórios recorrentes ou quando novos dados são adicionados regularmente e você precisa garantir integridade sem verificação manual.
Passos de operação:
- Clique Desenvolvedor > Visual Basic para abrir o editor VBA. Na janela Microsoft Visual Basic for Applications que aparece, clique Inserir > Módulo, depois cole o seguinte código na janela do módulo:
Sub InsertMissingDates()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim currentDate As Date, nextDate As Date
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
i = 2
While i < lastRow
currentDate = ws.Cells(i, 1).Value
nextDate = ws.Cells(i + 1, 1).Value
If nextDate > currentDate + 1 Then
ws.Rows(i + 1).Insert Shift:=xlDown
ws.Cells(i + 1, 1).Value = currentDate + 1
ws.Cells(i + 1, 1).NumberFormat = "yyyy-mm-dd"
lastRow = lastRow + 1
End If
i = i + 1
Wend
End Sub- Clique no
Executar botão (ou pressione F5) para executar o código. A macro verificará sua primeira coluna (Coluna A) para uma lista de datas e inserirá automaticamente as datas ausentes como novas linhas.
Executar botão (ou pressione F5) para executar o código. A macro verificará sua primeira coluna (Coluna A) para uma lista de datas e inserirá automaticamente as datas ausentes como novas linhas.Dicas práticas e observações:
– Certifique-se de que suas datas estejam classificadas em ordem crescente antes de executar a macro.
– A macro insere datas ausentes como novas linhas, então faça backup dos seus dados ou teste em uma cópia, se necessário.
– Se suas datas não estiverem na Coluna A, ajuste ws.Cells(i,1) para o número correto da coluna.
– Em caso de um conjunto de dados muito grande, a macro pode levar alguns momentos para ser concluída.
– Se você receber um erro, verifique se todas as células na sua coluna de datas são valores reais de data.
Destacar datas ausentes usando uma Tabela Dinâmica
Se você prefere não usar fórmulas ou código, pode aproveitar o recurso Tabela Dinâmica embutido do Excel para comparar visualmente sua lista de datas real com uma sequência completa esperada. Esse método é particularmente adequado quando você deseja analisar ou conferir registros de presença, transações ou registros diários onde todas as datas em um intervalo devem aparecer.
Passos de operação:
- Primeiro, crie uma coluna auxiliar que contenha uma sequência completa de datas esperadas cobrindo suas datas inicial e final. Insira a primeira data em uma célula (por exemplo, D2), depois arraste a alça de preenchimento para baixo para criar datas até que seu intervalo esteja completo.
- Copie tanto sua lista original de datas quanto sua nova lista auxiliar de datas para uma nova planilha, empilhando-as em uma coluna (por exemplo, coluna E).
- Selecione a lista combinada, depois vá para Inserir > Tabela Dinâmica. No diálogo, defina a tabela/intervalo e escolha uma nova planilha para a saída.
- Na Lista de Campos da Tabela Dinâmica, arraste o campo de data para a área Linhas e novamente para a área Valores, definindo a agregação para Contagem. As datas com apenas uma aparição na coluna de contagem indicam datas ausentes (ou seja, aquelas presentes apenas na sequência completa, mas não nos dados reais).
Dicas:
– Este método funciona melhor para revisar entradas ausentes em longos períodos.
– Para resultados ideais, certifique-se de que suas listas de datas não contenham duplicatas.
– Você pode filtrar ou destacar na Tabela Dinâmica para localizar rapidamente as datas ausentes.
– Usuários avançados podem combinar este método com Formatação Condicional para maior visibilidade.
Vantagens: Fácil de visualizar; sem fórmulas ou VBA necessários; ideal para relatórios.
Desvantagens: Não preenche automaticamente as datas ausentes, mas destaca o que está faltando.
Demonstração: Encontrar e inserir datas ausentes em uma lista
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