Neste artigo, aprenderemos como obter a última entrada por mês no Excel.
Cenário:
Ao trabalhar com conjuntos de dados, às vezes precisamos extrair alguns resultados com base em critérios diferentes. Aqui, o critério do problema é que o valor deve ser a última entrada correspondente a um determinado mês. O problema pode ser interpretado de duas maneiras.
- Última entrada na coluna por mês
- Último valor de data do mês na coluna
Você deve estar pensando qual é a diferença entre os dois. A diferença é que o primeiro problema extrai a última entrada da coluna que corresponde ao nome do mês, enquanto o segundo problema extrai o valor de data mais próximo do final do valor de data do mês. Vamos entender as duas situações uma por uma. Em primeiro lugar, consideraremos como obter a última entrada na coluna por mês.
Como obter a última entrada em coluna por mês no Excel?
Para isso, usaremos a função TEXT e a função LOOKUP. Aqui, usamos um atributo de pesquisa da função LOOKUP para o último valor. A função LOOKUP leva 3 argumentos, valor de pesquisa, matriz de pesquisa e matriz de resultado. Portanto, usamos o argumento lookup array como 1 / lookup array. Para obter mais detalhes, consulte a sintaxe da fórmula abaixo:
sintaxe da fórmula:
| = LOOKUP (2,1 / (TEXT (datas, "mmyyyy") = TEXT (mês, "mmyyyy")), valores) |
datas: matriz de datas em dados
valores: matriz de resultado em dados
mês: critérios de mês
Exemplo :
Tudo isso pode ser confuso de entender. Vamos entender como usar essa fórmula em um exemplo. Aqui temos dados com data e valores de preço. Para isso, precisamos da última entrada do mês de janeiro de 2019, que será a última entrada com a data de janeiro de 2019. Aqui, para a matriz de data e a matriz de preço, usamos intervalos nomeados.
Use a fórmula:
| = LOOKUP (2,1 / (TEXT (date, "mmyyyy") = TEXT (F9, "mmyyyy")), Price) |
Explicação:
- TEXT (datas, "mmyyyy") irá retornar a matriz de valores no formato "mmyyyy" após convertê-lo em valores de texto que é
{ "012019"; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- TEXT (F9, "mmyyyy") retornará o valor de data (na célula F9) no formato "mmyyyy" após converter o valor de data de pesquisa que é "012019" e esse valor será correspondido com a matriz indicada acima.
- Fazer a divisão com 1 converterá a matriz de valores verdadeiros em 1s e os valores falsos em erro # DIV / 0. Portanto, obteremos o array retornado como.
{1; 1; 1; 1; # DIV / 0!; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! }
- Agora a função LOOKUP encontra o valor 2 na matriz que não será encontrado. Portanto, ele retorna a entrada correspondente ao último valor 1.
Intervalos nomeados usados
datas: C3: C15
Preço: D3: D15
O último Preço é de 78,48 correspondendo a 31-01-2019. Copie a fórmula para outras células usando Ctrl + D ou arrastando para baixo a partir do canto inferior direito da célula.
Como você pode ver, a fórmula retorna todos os valores necessários. A função retorna o erro # N / A, se o valor do mês de pesquisa não corresponder a nenhuma entrada de data. No exemplo acima, as entradas de data estão ordenadas. Portanto, a última entrada do mês corresponde à última entrada de data do mês. Agora, se as entradas de data não forem classificadas, primeiro classificaremos os valores de data e usaremos a fórmula acima.
Como obter a última entrada de data por mês na coluna do Excel?
Para isso estaremos usando as funções VLOOKUP e EOMONTH. A função EOMONTH obtém o valor da data do mês e retorna a última data do mês necessário. A função VLOOKUP encontra a última data do mês ou a data anterior mais próxima e retorna um valor correspondente a esse valor.
Use a fórmula:
| = VLOOKUP (EOMONTH (F3,0), tabela, 2) |
Explicação:
- EOMONTH (F3,0) retorna a última data do mesmo mês. 0 argumento usado para retornar a data do mesmo mês.
- Agora, o valor de pesquisa torna-se a última data do mês determinado.
- Agora o valor será procurado na primeira coluna da tabela e procurará a última data, se encontrado retorna o valor correspondente ao valor e se não encontrado retorna o último resultado correspondente, mais próximo do valor de pesquisa e retorna o resultado correspondente.
- A função retorna o valor da 2ª coluna da tabela, pois o 3 ° argumento é 2.
Aqui, a fórmula retorna 78,48 como resultado. Agora copie a fórmula para outras células usando o atalho Ctrl + D ou arrastando para baixo a partir do canto inferior direito da célula.
Como você pode ver, a fórmula funciona bem. Só existe um problema. Ao procurarmos o último dia correspondente ao mês de abril, a função retorna o valor correspondente ao mês de março, pois não há entrada do dia de abril na tabela. Certifique-se de pegar esses resultados.
Aqui estão todas as notas observacionais sobre o uso da fórmula.
Notas :
- Use a fórmula correspondente ao problema indicado no artigo.
- As funções VLOOKUP e LOOKUP são funções quando o valor de pesquisa é um número e não está presente na matriz de pesquisa, retorna o valor correspondente apenas ao número mais próximo menor que o valor de pesquisa.
- O Excel armazena valores de data como números.
- As duas funções indicadas acima retornam apenas um valor.
- Use o quarto argumento na função PROCV como 0 para obter a correspondência exata do valor de pesquisa na tabela.
Espero que este artigo sobre Como obter a última entrada por mês no Excel seja explicativo. Encontre mais artigos sobre como extrair valores da tabela usando fórmulas aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva-nos no site de e-mail
Encontre a última linha com dados mistos no Excel : trabalhando com números, texto ou célula em branco na mesma matriz. Extraia a última linha com célula não em branco usando a função CORRESPONDÊNCIA no Excel.
Encontrando o último dia de um determinado mês : A função EOMONTH retorna a última data do mês, com o valor de data fornecido.
Como obter o último valor na coluna : Última entrada em dados grandes, uma combinação das funções CELL e INDEX retorna o último valor em uma coluna ou lista de dados.
Diferença com a última célula não em branco : obtém a diferença alternativa do último valor na lista com números usando as funções IF e LOOKUP no Excel.
Encontre a última linha de dados com valores de texto no Excel : Em uma matriz de valores de texto e células em branco, retorne o último valor na matriz usando as funções MATCH e REPT no Excel.
Como usar a função SUMPRODUCT no Excel: Retorna a soma após obter o produto dos valores correspondentes em várias matrizes no Excel.
Como usar curingas no excel : Pesquisa, Contagem, soma, média e mais operação matemática em células que combinam frases usando os curingas no Excel.
Artigos populares:
Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.
Como usar a função PROCV no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.