Como encontrar e devolver o penúltimo valor numa determinada linha ou coluna no Excel?
Ao trabalhar com folhas de cálculo grandes do Excel, muitas vezes é necessário identificar não apenas o último valor numa linha ou coluna, mas especificamente o penúltimo. Por exemplo, como ilustrado na imagem abaixo, no intervalo A1:E16 poderá precisar de obter o penúltimo valor na 6.ª linha ou na coluna B. Esta necessidade surge frequentemente ao acompanhar alterações recentes, monitorizar séries temporais ou analisar as entradas mais recentes que não correspondam à atual num conjunto de dados. Comparativamente com a localização simples do último valor, encontrar o penúltimo pode ser menos direto — especialmente quando os dados contêm células vazias ou são atualizados regularmente. Este artigo oferece soluções claras e passo a passo para esta tarefa, tornando o seu fluxo de trabalho mais eficiente e ajudando-o a evitar armadilhas comuns.
- Encontrar e devolver o penúltimo valor numa determinada linha ou coluna com fórmulas
- Código VBA – Utilizar uma macro para encontrar o penúltimo valor numa linha ou coluna especificada
- Outros métodos incorporados no Excel – Filtrar todas as células em branco e identificar manualmente o penúltimo valor
Encontrar e devolver o penúltimo valor numa determinada linha ou coluna com fórmulas
Conforme ilustrado na imagem acima, ao precisar localizar e devolver o penúltimo valor — quer na 6.ª linha, quer na coluna B, dentro do intervalo A1:E16 — as fórmulas do Excel proporcionam uma solução dinâmica e eficiente. São ideais em cenários onde os seus dados são frequentemente atualizados ou a posição do penúltimo valor pode variar devido a adições ou eliminações. Ao contrário das abordagens manuais, as fórmulas garantem que o resultado se adapta automaticamente aos dados mais recentes.
Encontrar e devolver o penúltimo valor na coluna B
1. Selecione uma célula em branco onde pretenda apresentar o penúltimo valor. Introduza a seguinte fórmula matricial na Barra de Fórmulas e, em seguida, prima Ctrl + Shift + Enter (no Excel clássico) para a confirmar como fórmula matricial. No Excel 365 ou Excel 2021, basta premir Enter, já que as fórmulas matriciais são tratadas automaticamente.
=INDEX(B:B,LARGE(IF(B:B<>"",ROW(B:B)),2))
Nota: Nesta fórmula, B:B refere-se à coluna B. Se precisar do penúltimo valor noutra coluna, basta ajustar B:Bpara a referência da coluna pretendida (por exemplo, altere para)C:C para a coluna C). Este método funciona em intervalos com ou sem células em branco, mas, se existirem valores ocultos ou dados excluídos por filtro, certifique-se de considerar possíveis discrepâncias. Verifique sempre o seu intervalo de dados caso não obtenha o valor esperado.
Encontrar e devolver o penúltimo valor na linha 6
Selecione uma célula em branco para apresentar o resultado relativo à 6.ª linha. Em seguida, introduza a seguinte fórmula na Barra de Fórmulas e prima Enter para confirmar.
=OFFSET($A$6,0, COUNTA(6:6)-2,1,1)
Nota: Na fórmula acima, $A$6 é a primeira célula da linha 6, e 6:6designa a linha inteira 6. Ajuste estas referências para as alinhar com outras linhas, se necessário. A fórmula conta dinamicamente o número de células não vazias na linha 6 e, em seguida, desloca-se a partir da célula inicial para localizar a penúltima célula preenchida. Se a sua linha incluir fórmulas que devolvam cadeias vazias ()""), a função CONTAR.VAL pode ainda assim considerá-las como não vazias, o que poderá afetar o resultado. Para intervalos com uma mistura de constantes e fórmulas, verifique cuidadosamente os resultados para evitar erros.
Dicas para utilizar fórmulas:
- Com conjuntos de dados grandes, utilizar referências a colunas inteiras (por exemplo,)
B:B) pode comprometer o desempenho. Sempre que possível, limite o intervalo apenas à área necessária (por exemplo,B1:B100). - Se os seus dados incluírem linhas ocultas ou estiverem filtrados, as fórmulas continuarão a contar células ocultas e excluídas pelo filtro. Para dados filtrados, opte por utilizar funções especiais de subtotal ou colunas auxiliares.
- Se todos os valores numa linha ou coluna-alvo forem em branco, estas fórmulas poderão devolver um erro ou um resultado inesperado. Certifique-se de que os seus dados incluem pelo menos dois valores não em branco.
Código VBA – Utilizar uma macro para encontrar o penúltimo valor numa linha ou coluna especificada
Para utilizadores que frequentemente precisam de encontrar o penúltimo valor em intervalos variáveis ou extensos, automatizar o processo com uma macro VBA pode poupar um tempo considerável — especialmente ao trabalhar com tabelas dinâmicas ou conjuntos de dados complexos. As macros revelam-se particularmente úteis quando a dimensão dos seus dados muda regularmente e pretende evitar atualizações manuais e repetidas de fórmulas, ou quando necessita de extrair valores de múltiplas posições diferentes com base na mesma lógica. Abaixo encontra uma solução em VBA que lhe pede para indicar a linha ou coluna-alvo e, de imediato, devolve o penúltimo valor — eliminando ajustes manuais e contagens tediosas.
1. Aceda às Ferramentas de Programador no Friso do Excel, clique em Visual Basic para abrir o editor VBA. No editor, clique em Inserir > Módulo. Em seguida, cole o seguinte código VBA no módulo recém-criado:
Sub GetSecondToLastValue()
Dim rng As Range
Dim arr As Variant
Dim values As Collection
Dim i As Long
Dim secondLast As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the row or column range to analyze", xTitleId, Selection.Address, Type:=8)
If rng Is Nothing Then Exit Sub
arr = rng.Value
Set values = New Collection
If rng.Rows.Count = 1 Then
For i = 1 To rng.Columns.Count
If arr(1, i) <> "" Then
values.Add arr(1, i)
End If
Next i
ElseIf rng.Columns.Count = 1 Then
For i = 1 To rng.Rows.Count
If arr(i, 1) <> "" Then
values.Add arr(i, 1)
End If
Next i
Else
MsgBox "Please select a single row or single column range.", vbExclamation
Exit Sub
End If
If values.Count < 2 Then
MsgBox "There are less than two non-blank values in the selected range.", vbInformation
Exit Sub
End If
secondLast = values(values.Count - 1)
MsgBox "The second-to-last value is: " & secondLast, vbInformation
End Sub2. Após introduzir o código, regresse ao Excel e execute a macro utilizando o botão 
Executar, premindo Alt + F8 e selecionando GetSecondToLastValue da lista. Ser-lhe-á solicitado que selecione um intervalo de uma única linha ou coluna (por exemplo, B1:B16 para uma coluna ou A6:E6 para uma linha). Após confirmar a sua seleção, a macro apresentará o penúltimo valor numa caixa de diálogo.
Notas:
- Esta macro conta apenas as células não vazias, ignorando automaticamente as células em branco na linha ou coluna selecionada.
- Se a sua seleção contiver menos de dois valores não vazios, receberá uma mensagem de aviso e nenhum valor será devolvido.
- A macro destina-se a seleções de uma única linha ou coluna. Se selecionar várias linhas e colunas em simultâneo, será solicitado a ajustar a sua seleção.
- Para automatização, pode ainda aprimorar o código de modo a copiar o resultado diretamente para uma célula específica da folha de cálculo, em vez de exibir uma caixa de mensagem.
Esta solução VBA é especialmente vantajosa para utilizadores que trabalham com tabelas dinâmicas ou conjuntos de dados sujeitos a alterações frequentes, reduzindo erros manuais e eliminando tarefas repetitivas.
Outros métodos incorporados no Excel – Filtrar todas as células em branco e identificar manualmente o penúltimo valor
Embora as fórmulas e macros ofereçam formas automatizadas de encontrar o penúltimo valor, por vezes poderá preferir uma abordagem visual e rápida — especialmente com dados irregulares ou não contíguos, ou quando precisa apenas de verificar um valor ocasionalmente. As ferramentas de filtragem integradas do Excel permitem-lhe ocultar temporariamente células em branco ou irrelevantes, facilitando a identificação da entrada pretendida sem necessidade de editar ou escrever fórmulas.
Método de filtragem:
- Selecione o intervalo de dados da sua coluna ou linha (para colunas, escolha as células B1:B16; para linhas, selecione A6:E6 na sua folha de cálculo).
- No Friso do Excel, clique em Dados > Filtrar para ativar as setas de filtro. No cabeçalho da coluna ou junto ao seu intervalo selecionado, clique no menu pendente do filtro.
- Desmarque (Em branco) para ocultar células vazias. A visualização dos dados passará agora a listar apenas valores não em branco.
- Para uma coluna, percorra até ao fim da lista filtrada e visualize o penúltimo valor. Para uma linha (após filtragem, se aplicável), conte a partir da direita para identificar a penúltima entrada não vazia.
Notas e dicas:
- A filtragem é a opção mais rápida para listas pequenas ou de dimensão média, ou quando precisa de confirmar visualmente a posição dos valores.
- Se o seu conjunto de dados for muito grande, a filtragem pode comprometer o desempenho e não é ideal para automatização ou uso repetido.
- Quando os filtros estiverem aplicados, certifique-se de que está a visualizar o contexto correto, pois as listas filtradas podem ignorar linhas ou colunas ocultas.
- Para remover os filtros, clique em Limpar no separador Dados.
Este método é menos indicado para análises dinâmicas contínuas ou conjuntos de dados extensos, mas proporciona uma verificação manual transparente da penúltima entrada não vazia — ideal para resolução rápida de problemas ou validação.
Artigos relacionados:
- Como encontrar a posição do primeiro ou último número numa cadeia de texto no Excel?
- Como encontrar a primeira ou a última sexta-feira de cada mês no Excel?
- Como utilizar o PROCV para encontrar o primeiro, o segundo ou o enésimo valor correspondente no Excel?
- Como encontrar o valor que ocorre com maior frequência num intervalo no Excel?
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos