Às vezes, em vez de apenas encontrar o maior ou o maior número para todos os seus dados; você precisa encontrar o maior número em um subconjunto - como o maior número positivo ou negativo.
Se a quantidade de dados for pequena, a tarefa pode ser fácil de ser feita selecionando-se manualmente o intervalo correto para a função MAX.
Em outras circunstâncias, como uma grande amostra de dados não separados, selecionar o intervalo corretamente pode ser difícil, se não impossível.
Combinando a função IF com o MAX em uma fórmula de matriz, as condições - como números positivos ou negativos somente - podem ser facilmente definidas para que apenas os dados correspondentes a esses parâmetros sejam testados pela fórmula.
Divisão de Fórmula de Matriz MAX IF
A fórmula usada neste tutorial para encontrar o maior número positivo é:
= MAX (SE (A1: B5> 0, A1: B5))
Nota: O argumento value_if_false da função IF, que é opcional, é omitido para encurtar a fórmula. No caso de os dados no intervalo selecionado não atenderem ao critério definido - números maiores que zero - a fórmula retornará um zero ( 0 )
O trabalho de cada parte da fórmula é:
- A função IF filtra os dados de modo que apenas os números que atendem ao critério escolhido sejam passados para a função MAX
- a função MAX encontra o valor mais alto para os dados filtrados
- A fórmula de matriz - indicada pelas chaves { } em torno da fórmula - permite que o argumento de teste lógico da função IF pesquise todo o intervalo de dados de uma correspondência - como números maiores que zero - em vez de apenas uma única célula de dados
Fórmulas CSE
As fórmulas de matriz são criadas pressionando o Ctrl, Mudançae Entrar teclas no teclado ao mesmo tempo, uma vez que a fórmula tenha sido digitada.
O resultado é que toda a fórmula - incluindo o sinal de igual - é cercada por chaves. Um exemplo seria:
{= MAX (SE (A1: B5> 0, A1: B5))}
Por causa das teclas pressionadas para criar a fórmula de matriz, elas são às vezes chamadas de CSE fórmulas.
Exemplo de fórmula da matriz MAX IF do Excel
Como visto na imagem acima, este exemplo de tutorial usa a fórmula de matriz MAX IF para encontrar os maiores valores positivos e negativos em um intervalo de números.
Os passos abaixo primeiro criam a fórmula para encontrar o maior número positivo seguido pelos passos necessários para encontrar o maior número negativo.
Inserindo os dados do tutorial
- Digite os números vistos na imagem acima nas células A1 a B5 de uma planilha
- Nas células A6 e A7, digite os rótulos Max Positivo e Negativo Máximo
Inserindo a fórmula aninhada do MAX IF
Como estamos criando uma fórmula aninhada e uma fórmula de matriz, precisaremos digitar a fórmula inteira em uma única célula da planilha.
Depois de ter inserido a fórmula NÃO aperte o Entrar tecla no teclado ou clique em uma célula diferente com o mouse, pois precisamos transformar a fórmula em uma fórmula de matriz.
- Clique na célula B6 - o local onde os primeiros resultados da fórmula serão exibidos
- Digite o seguinte:
= MAX (SE (A1: B5> 0, A1: B5))
Criando a fórmula da matriz
- Pressione e mantenha pressionado Ctrl e Mudança teclas no teclado
- aperte o Entrar tecla no teclado para criar a fórmula de matriz
- A resposta 45 deve aparecer na célula B6, pois este é o maior número positivo na lista
- Se você clicar na célula B6, a fórmula completa da matriz
{= MAX (SE (A1: B5> 0, A1: B5))}
pode ser visto na barra de fórmulas acima da planilha
Encontrando o maior número negativo
A fórmula para encontrar o maior número negativo difere da primeira fórmula somente no operador de comparação usado no argumento de teste lógico da função IF.
Como o objetivo é agora encontrar o maior número negativo, a segunda fórmula usa o operador menor que ( < ), em vez do operador maior que ( > ), para testar apenas os dados que são menores que zero.
- Clique na célula B7
- Digite o seguinte:
= MÁXIMO (SE (A1: B5 <0, A1: B5))
- Siga as etapas acima para criar a fórmula de matriz
- A resposta -8 deve aparecer na célula B7, pois este é o maior número negativo na lista
Obtendo #VALUE! por uma resposta
Se as células B6 e B7 exibirem o valor de erro #VALUE! valor de erro em vez das respostas indicadas acima, provavelmente é porque a fórmula de matriz não foi criada corretamente.
Para corrigir este problema, clique na fórmula na barra de fórmulas e pressione o botão Ctrl, Mudança e Entrar teclas no teclado novamente.
