Preciso recuperar o valor máximo de um número variável de linhas e colunas onde o cabeçalho da linha é igual a um determinado valor, 300 no exemplo acima retornaria um máximo de 15.
Estou tentando criar uma fórmula onde o cabeçalho da linha (por exemplo, 300) seria uma variável para que o máximo pudesse ser retornado para os cabeçalhos de linha 300 ou 600 e assim por diante.
Como obter um intervalo ou matriz de todas as linhas com 300 como cabeçalho para que eu possa usar a função Max nesse intervalo?
Responder1
Coloque o seu300emF1, então emD1coloque a fórmula da matriz:
=MAX(IF(A1:A100=F1,B1:C100))
Fórmulas de matrizdeve ser inserido com Ctrl+ Shift+ Enterem vez de apenas com a Entertecla. Se isso for feito corretamente, a fórmula aparecerá com chaves ao redor na Barra de Fórmulas.
Por causa da colunaAcritérios, o101na linha14é ignorado.
Responder2
Se você possui o Office 365 Excel, a fórmula normal:
=MAX(MAXIFS(B:B,A:A,300),MAXIFS(C:C,A:A,300))
Você pode substituir ambos 300 por um endereço de célula. Como esta não é uma fórmula de matriz, ela permitirá referências completas de colunas sem prejuízo.
Responder3
Aqui está uma resposta um pouco mais geral:
Suponha que você queira calcular os MAXes (ou MINs, ou MEDIANs, etc.) de valores numéricos que aparecem nas colunas C e D para o subconjunto contíguo de linhas i a j em uma matriz que se estende das linhas 6 a N, com o valor de " linha" inserida na coluna B. Deixe o valor explícito de i ser especificado em B2 e o de j em B3 (-esses valores talvez tenham sido calculados em outro lugar em sua planilha, a priori); aqui, por uma questão de transparência, sejam i = 9 e j = 14, com n = 15.
Aqui está o código Excel2003 que desenvolvi, que parece funcionar muito bem:
{=MAX(IF(B6:B15>=B2,IF(B6:B15<=B3,C6:C15),""))} , que retorna MAX(C9:C14) ; e,
{=MAX(IF(B6:B15>=B2,IF(B6:B15<=B3,D6:D15),""))} , que retorna MAX(D9:D14) .
Basta alterar MAX para MIN ou outro nome de função para fazer cálculos análogos. Mas não se esqueça de reinstalar (Ctrl-Shift-Enter) aquelas "colchetes", que desaparecerão toda vez que você manipular a instrução.