Encontra a última linha com dados – Excel e Google Sheets

Este tutorial demonstrará como encontrar a última linha não vazia em um conjunto de dados no Excel e no Google Sheets.

Encontrar a última linha com dados

O primeiro método usa as funções LIN e MÁXIMO e pode ser usado com qualquer tipo de dados:

=MÁXIMO((B:B<>"")*LIN((B:B)))

Vamos analisar essa fórmula.

Começamos usando um teste lógico na coluna de dados. Nossa fórmula analisa a coluna inteira (B:B) e gera VERDADEIRO para células não vazias e FALSO para células vazias.

=B:B<>""

A função LIN produz o número da linha de uma determinada célula. Se não fornecermos uma entrada de célula específica, ela fornecerá o número da linha da célula em que se encontra.

=LIN()

Multiplicando cada número de linha pelos valores VERDADEIRO (= 1) ou FALSO (= 0) correspondentes, retorna o número da linha para uma célula preenchida e zero para uma célula em branco.

=E3*F3

A função MÁXIMO fornece o máximo de um conjunto de números. Neste exemplo, como todas as células em branco produzem um valor zero, o máximo é o número da linha mais alta.

=MÁXIMO(G3:G12)

A combinação dessas etapas resulta em nossa fórmula original:

=MÁXIMO((B:B<>"")*(LIN(B:B)))

Observe que essa é uma fórmula de matriz, portanto, se estiver usando o Excel 2019 ou anterior, será necessário pressionar CTRL + SHIFT + ENTER para acioná-la.

Método para intervalo de texto

Se o intervalo (não contínuo) contiver apenas valores de texto e células em branco, você poderá usar uma fórmula mais simples que contenha as funções REPT e CORRESP:

=CORRESP(REPT("z";50);B:B)

Vamos ver como essa fórmula funciona.

Função REPT

A função REPT repete uma cadeia de texto em um determinado número de vezes. Para este exemplo, podemos usá-la para criar uma cadeia de texto que seria a última em qualquer lista ordenada alfabeticamente. Se repetirmos “z” 50 vezes, isso deve funcionar para quase todas as colunas de dados de texto; nenhuma entrada viria antes de “zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz” em ordem alfabética.

=REPT("z",50)

Função CORRESP

A função CORRESP localiza um determinado valor de pesquisa em uma matriz.

Realizamos uma pesquisa em toda a coluna de dados para nossa cadeia de texto 50-z. A omissão da entrada do tipo de correspondência na função CORRESP faz com que ela encontre uma correspondência aproximada em vez de uma exata.

=CORRESP(REPT("z";50);B:B)

A função CORRESP pesquisa a coluna B e procura nossa sequência de texto de 50 “z “s. Como não a encontra, a fórmula retorna a posição da última célula não vazia. Essa célula contém o último valor na matriz de pesquisa que é menor que (ou igual a) o valor de pesquisa.

Lembre-se de que essa fórmula funciona somente quando seu intervalo contém exclusivamente texto e células em branco (ou pelo menos o valor da última célula não é numérico).

Localizar a última linha com dados no Google Sheets

Essas fórmulas funcionam exatamente da mesma forma no Planilhas Google e no Excel.