Excel: obtenha o valor máximo do intervalo de variáveis

Question

Eu 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 em que o cabeçalho da linha (por exemplo, 300) seria uma variável, para que o máximo possa ser retornado para os cabeçalhos de linha 300 ou 600 e assim por diante.

Como obter um intervalo ou uma matriz de todas as linhas com 300 como cabeçalho para que eu possa usar a função Max nesse intervalo?

    

Answer 1

Coloque seu 300 em F1 e, em D1 , coloque a fórmula do array:

=MAX(IF(A1:A100=F1,B1:C100))

Fórmulas de matriz devem ser digitadas com Ctrl + Deslocar + Enter em vez de apenas o Enter tecla . Se isso for feito corretamente, a fórmula aparecerá com chaves em torno dela na barra de fórmulas.

Devido aos critérios A da coluna, o 101 na linha 14 é ignorado.

    

Answer 2

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 os dois 300 por um endereço de célula. Como essa não é uma fórmula de matriz, ela permite referências completas a colunas sem prejuízo.

    

Answer 3

Aqui está uma resposta um pouco mais geral:

Suponha que você queira computar os MAXes (ou MINs, ou MEDIANs, etc.) dos valores numéricos que aparecem nas colunas C & 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" inserido em col B. Deixe o valor explícito de i ser especificado em B2 e de j em B3 (- -os valores talvez tenham sido computados em outra parte da sua planilha, a priori); aqui, por uma questão de transparência, vamos i = 9 ej = 14, com n = 15.

Aqui está o código Excel2003 que desenvolvi, que parece funcionar bem:

{= MAX (SE (B6: B15 > = B2, IF (B6: B15 < = B3, C6: C15), ""))}}, que retorna MAX (C9: C14); e,

{= MAX (SE (B6: B15 > = B2, IF (B6: B15 < = B3, D6: D15), ""))}, que retorna MAX (D9: D14).

Apenas mude 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) as "chaves", que desaparecerão toda vez que você manipular a instrução.