COMO TRAZER O VALOR DA ÚLTIMA CÉLULA PREENCHIDA
Usando a função DESLOCComo determinar a última célula preenchida de uma coluna
Agora, mostraremos como determinar a última célula preenchida sem o uso do VBA, apenas com a utilização de uma função do Excel, a função DESLOC (você pode ver outros exemplos de uso dela aqui).
Suponha que você tenha uma planilha que é atualizada freqüentemente pela adição de novas linhas e/ou colunas, e para isto você precise retornar o valor que se encontre na última célula preenchida, que pode ser o último valor da coluna como o último valor de uma linha.
Veja o exemplo a seguir:
Queremos retornar o valor da última célula preenchida de determinada coluna. Se for a Coluna 1, queremos trazer o 150, da 2 o 110, etc.
Para podermos trazer o valor da última célula preenchida, a função deverá ser capaz de:
- Saber qual é a coluna
- Determinar qual a última linha preenchida
- Retornar o valor daquela linha x coluna
A função DESLOC possui um total de 5 argumentos. No exemplo, usaremos os 3 primeiros (você pode ver o uso desta função com todos os argumentos no tutorial de criação deste gráfico).
Os 3 argumentos que usaremos da função são:
=DESLOC(célula inicial; linhas a subir ou descer; colunas para a direita ou esquerda)
A célula inicial pode ser qualquer uma. Para facilitar a compreensão, usaremos a célula A6, que é a linha inicial de nossa tabela.
No 2º argumento informamos quantas linhas devemos descer (ou subir, se fosse o caso). Se for a coluna 1, são 15 linhas; a 2, 2 linhas, etc.
Veja que na linha 5 inserimos uma função que irá contar o total de células preenchidas. A função chama-se CONT.VALORES (o exemplo só funcionará se NÃO tivermos linhas em branco no meio dessa listagem).
O que precisamos fazer é informar qual dos valores vamos querer, se é o da Coluna 1, 2 ou 3.
Na célula B1 vamos digitar o número da coluna que queremos. No exemplo acima, queremos a coluna 1.
Na célula B2 inserimos uma função que irá trazer, da lista de número de linhas preenchidas (o intervalo que vai de B5 a D5) o valor correspondente à coluna desejada. Se for coluna 1, ele trará o 1º valor. Se for coluna 2, o 2º valor e da coluna 3, o 3º valor.
A função ÍNDICE faz exatamente isto: a partir de uma lista, indicamos qual ítem queremos.
Então até agora temos a célula onde tudo vai começar e o total de linhas a se moverem para baixo.
Falta informar quantas colunas deveremos nos deslocar para a direita. Mas já temos isto: é o número que digitamos na célula A2.
Então, nossa função ficará assim:
=DESLOC(A6;B2;A2)
Como lemos a função: a iniciando na célula B6, vá para cima / baixo pela quantidade de linhas informada na célula B2, e vá para a direita/esquerda pela quantidade de colunas informada na célula B1 (você já deve ter notado que, se quiséssemos ir para cima deveríamos informar o número com sinal negativo, bem como se fôssemos para a esquerda).
Essa função é uma das mais estranhas do Excel, mas ao mesmo tempo é uma das versáteis. Recomendo fortemente que você conheça-a mais. Você vi se surpreender 🙂
OUTROS ARTIGOS DE INTERESSE
quatro motivos para voce parar de usar o procv
Já são 40 anos de idade O Excel 1.0 foi criado em 1982. São quase 40 anos de existência. Apesar de ter começado minha carreira usando o Excel 5.0 tenho certeza de que a função PROCV existia nela (pelo menos no 5.0 tinha!). Então estamos hoje, no séc. XXI obcecados em...
Como utilizar a Função Filtro
Certamente você no mínimo já ouviu falar do recurso de FILTRO do Excel. Nele você consegue mostrar somente linhas de uma planilha que atendem a um ou mais critérios. Além disso você poderia usar também o filtro avançado, cuja principal diferença é a possibilidade de...
Função PROCX
Como usar a função PROCX Recentemente a Microsoft incluiu uma nova função em seu arsenal de funções de procura a referência, a função PROCX. Além de servir como um substituto para o famoso PROCV, ela também utiliza a nova funcionalidade, que é apresentar a resposta em...
Especialista em Relatórios e Dashboards no Excel e no PowerBI.
- 6 maneiras de usar o preenchimento relâmpago - março 12, 2021
- quatro motivos para voce parar de usar o procv - fevereiro 16, 2021
- Como Criar um Gráfico de Waterfall no Excel - fevereiro 2, 2021
Nossas Redes Sociais
Recebi uma planilha preenchida no estilo de tabela dinâmica (com as seguintes informações: NOME; ADMISSAO; DATA)
Servidores Temporários ADM 01/2015 02/2015
ABELARDO MIRANDA 1/1/2021 0.00 0.00
ABELCIO SANTOS PAES 1/1/2015 2,127.60 2,127.60
ABELCIO SANTOS PAES 1/1/2018 0.00 0.00
ABILDES GONCALVES 1/1/2019 0.00 0.00
Minha primeira missão é: saber qual é o primeiro valor e qual a data; saber qual o último valor e a data.
Por fim, preciso transformar a tabela para o seguinte formato:
NOME; ADMISSÃO; DATA e VALOR que resultaria no seguinte.
NOME ADMISSAO COMPETENCIA REMUNERACAO
ABELARDO MIRANDA MORAES 1/1/2020 1/1/2020 1539.00
ABELARDO MIRANDA MORAES 1/1/2020 2/1/2020 1545.00
ABELARDO MIRANDA MORAES 1/1/2020 3/1/2020 1545.00
ABELARDO MIRANDA MORAES 1/1/2020 4/1/2020 1545.00
ABELARDO MIRANDA MORAES 1/1/2020 5/1/2020 1545.00
ABELARDO MIRANDA MORAES 1/1/2020 6/1/2020 1545.00
Não consigo imaginar de que forma fazer isso. Consegui encontrar a primeira e a última linha preenchida mas, não consigo relacionar com a data. Já a mudança de leiaute não tenho ideia.
Consegui!!
Para a data da primeira célula preenchida:
=ÍNDICE($1:$1;1;CÉL(“col”;ÍNDICE(F2:CK2;CORRESP(VERDADEIRO;ÍNDICE((F2:CK2″”);0);0))))
Para a data da última célula preenchida:
=ÍNDICE($1:$1;1;PROC(2;1/(F2:CK2>0);COL(F2:CK2)))
Agora só falta uma forma para modificar o leiaute.
Ótima explicação….
Mas se eu tiver u trabalhando com uma tabela que preciso filtrar por mês e vir o ultimo valor filtrado
Qual seria a formula?
PARABÉNS PELA EXPLICAÇÃO, MUITO BOA! E BEM CRIATIVA A SAÍDA ENCONTRADA!
Boa tarde, preciso de uma formula que me diga qual o ultimo valor da ultima célula em linha ( na horizontal) com células entre eles vazias. é possível?
Quando tudo está certo é possível juntar tudo em uma fórmula.
Por exemplo, considerando que a coluna A começa a ter valores na linha 1, para ter o último valor preenchido a fórmula
=DESLOC(A1;CONT.VALORES(A:A)-1;0) dá conta do recado.
Importante não esquecer do “-1” para chegar na última célula preenchida.
Obrigado, deu certinho!
Abs,
Show, mano! Vc resumiu o artigo em 1 função! Parabéns!!!
como fazer encontrar valores e quando chegar na celula vazia ele para,
por exemplo ele vai contar o numero de celulas preenchidas logo abaixo e quando encontrar a primeira vazia ele para de contar
Bom dia, estou quebrando a cabeça e ainda não econtrei nenhuma função (ou consegui elaborar uma) que me ajude no seguinte problema:
tenho uma matriz que pode variar de tamanho conforme os dados são atualizados (inclusão de novas linhas e colunas), e esta matriz tem algumas informações que eu preciso que sejam somadas. basicamente, eu digo que tipo de produto é (e ele pode se repetir), qual a data da última atualização (essa é a coluna que é incluída) e quanto ele custou naquele dia (os novos preços são incluídos na linha subsequente. vou tentar ilustrar:
AÇÃO SETOR 01/02/19 02/02/19 … 15/02/19
PETR3 OLEO 30,00 31,00 27,00
ABCB4 BANCO 19,00 18,00 20,00
CNSL3 MINERAÇÃO 10,00 12,00 12,50
ITSA4 BANCO 16,00 16,50 15,90
VALE3 MINERAÇÃO 45,00 43,00 45,00
Eu estou tentando encontrar uma fórmula que, some todos os últimos lançamentos em cada setor, de forma que eu vou atualizando a última coluna e cada vez que ele encontre o arguento (ex.) Banco, ele some a última coluna (que seria a última data), de forma que eu saiba quanto vale cada setor.
Consegue me dar uma luz nisso?