Guru do Excel e Power BI

Prezados, na planilha acima, preciso descobrir a linha da 5ª última ocorrência de, por exemplo, "médias" na coluna estratégia, de maneira que eu consiga usar funções do tipo "soma.se" somente para os valores da coluna resultados. Não consegui definir esse intervalo. Por exemplo: eu calcularia qual a soma de resultados das última 5 linhas da estratégia "médias", e cada vez que uma nova linha for adicionada, a leitura continuaria sendo sempre dinâmica, considerando sempre as 5 últimas ocorrências.

Obrigado

Boa tarde.
Pelo que entendi vc quer uma fórmula para retornar o valor da última ocorrência de determinado item da coluna "M" com base em um item na coluna "P".
Por exemplo uma fórmula que retorne o último valor laçado para o item "médias" que no caso da imagem seria o valor R$20,00.
Se o item pesquisado fosse "defesaJ" o retorno seria R$ 48,00.
É realmente isso que precisa?
Porque pergunto:
A 5ª ocorrência de um lançamento nem sempre é o último lançamento. Se vc quer como retorno a 5ª ocorrência aí é necessário um outro tipo de construção.
Sugestão:
Anexe uma planilha de exemplo .
Informe sua versão do excel.

Acho que não consegui explicar bem.
O que desejo é encontrar qual a linha da última enésima ocorrência de determinado valor, de maneira que defina, a partir dela, um intervalo até a última coluna.

Coloquei em anexo uma planilha exemplo, com os cálculos manuais do que desejo encontrar (versão 2016).

Veja se o que fiz te ajuda.

Funcionou sim, obrigado. Vou entender a composição da fórmula e farei tb a média dos valores positivos, a médias dos valores negativos, a contagem de valores positivos e a contagem de valores negativos. Postarei aqui o meu resultado para avaliação, pois preciso entender a sintaxe de algumas funções utilizadas.

Bom dia.
Fiz um vídeo demonstrando o passo a passo que utilizei para alcançar os resultados.
Veja se te ajuda:

https://youtu.be/0D-1iBaVALg

andrelongo07, Bom Dia.

Primeiramente adorei a formula que o colega gfranco fez, esta num nível excelente, porem fazendo testes notei que tem erro quando seleciona uma estrategia e a mesma tem duas ocorrência ou uma ou nenhuma, a formula mostra um erro de calculo, então mudei para não acontecer esse erro, confere, a media talvez esteja errada pois ainda calcula sobre os três últimos, mas isso também pode ser alterado.

As formula agora é matricial deve ser encerrado com CTRL+SHIFT+ENTER

Nota fiz validação de lista e formatação condicional quando seleciona só para testes

Esperando ter ajudado

Decio

Excelente ideia décio de adicionar uma previsão de erro. Deixa o resultado de erro (quando houver) mais elegante.
Gostei do acréscimo mas não o colocaria nesta posição.

Décio, observei que o seerro está devolvendo valor incorreto quando seleciona (por exemplo) um K maior que o existente na lista.
Acho que seria mais prudente então, deixá-lo no extremo da fórmula, devolvendo em branco ou mensagem em caso de erro, o que vc acha?

Veja que na sua construção, a soma para os últimos 6 e ultimos 12 (respectivamente) está devolvendo o mesmo resultado...
E ,esse item pesquisado, só tem 6 ocorrencias.

gfranco

Descilpa a demora estava dando suporte ao cliente, mas alterei a célula M1 e coloquei formatação personalizada,

Mas quando coloca valor 12 últimos ela ira sempre colocar, quando não tem a célula, valor igual a zeros, confere

Acredito que esteja correto

Decio

Respeito seu ponto de vista mas penso que não está correto.
Concordo que os zeros excedentes não interferem no resultado de soma (isso é óbvio) mas o cenário (na minha opinião) passa a falsa informação de que há na lista mais dados do que realmente existe para aquele item, pois não retorna erro.
Mas o exposto acima é mera opinião, contudo, para o calculo da média (no meu entendimento) não se pode fazer a mesma leitura.
Se vc observar sua construção e apontar um valor superior ao numero de itens que existe na lista, os zeros produzidos pelo seerro no meio da fórmula serão computados e o resultado da média será comprometido.

Pessoa, vou avaliar durante essa semana e retorno por aqui em breve. Estou conseguindo entender a lógica da composição das funções, pois ainda não tinha trabalhado com algumas delas. Minha idéia é manter essa planilha com zero uso de VBA, para compartilhar uma versão default com algumas pessoas não tão familiarizadas com excel. Parabéns pelo alto nível!!

Contextualizando: é uma planilha para controle de operações de day-trade no mercado futuro. O usuário poderá obter o resultado recente de uma determinada estratégia (n últimos, personalizável), para comparara com o resultado histórico da própria estratégia, avaliando se recentemente está melhor/pior do que o normal. A necessidade surgiu pois a planilha monitora cronologicamente todas as operações, de todas as estratégias.

Bom dia André.
Visto que é uma base que cresce continuadamente, sugiro firmemente que seja usado um intervalo dinâmico para acomodar os lançamentos pois, desta forma, a fórmula (depois de ajustada) sempre percorrerá de baixo para cima partindo da última linha de lançamento e capturando os "N" últimos valores lançados.
Faça seus testes e nos dê retorno.
Giovani.

Pessoal, segue a solução que achei a mais funcional para o meu objetivo. Defini um intervalo a partir da linha da n última ocorrência, escolhida pelo usuário. Nesse intervalo, faço os cálculos desejados (contagem, soma, médias etc) usando as funções condicionais básicas.