Diretrizes e exemplos de fórmulas de matriz

Para se tornar um usuário avançado do Excel, você precisa saber como usar fórmulas de matriz, que podem fazer cálculos impossíveis de serem feitos com fórmulas que não sejam de matriz. O artigo a seguir baseia-se em uma série de colunas Excel Power User escritas por Colin Wilcox e adaptadas dos capítulos 14 e 15 do livro Excel 2002 Formulas, escrito por John Walkenbach, MVP do Excel.

Neste artigo


Aprender sobre fórmulas de matriz

Esta seção apresenta as fórmulas de matriz e explica como inseri-las, editá-las e solucionar problemas relativos a elas.

Por que usar fórmulas de matriz?

Se tiver experiência no uso de fórmulas do Excel, você saberá que pode executar algumas operações bastante sofisticadas. Por exemplo, é possível calcular o custo total de um empréstimo ao longo de um dado número de anos. Porém, para realmente dominar as fórmulas do Excel, você precisa saber como usar fórmulas de matriz. Elas podem ser usadas para executar tarefas complexas como, por exemplo:

  • Contar o número de caracteres contidos em um intervalo de células.
  • Somar apenas números que atendam a certas condições, como os valores mais baixos de um intervalo ou números que estejam entre um limite superior e inferior.
  • Somar todo n-ésimo valor em um intervalo de valores.

 Observação   As fórmulas de matriz podem ser chamadas de "fórmulas CSE", porque você pressiona CTRL+SHIFT+ENTER para inseri-las nas pastas de trabalho.

Uma rápida introdução a matrizes e fórmulas de matriz

Se tiver alguma experiência em programação, ainda que mínima, você provavelmente já ouviu o termo matriz. No nosso caso, matriz é um conjunto de itens. No Excel, esses itens podem estar em uma única linha (chamada de matriz horizontal unidimensional), em uma coluna (matriz vertical unidimensional) ou em várias linhas e colunas (matriz bidimensional). Não é possível criar matrizes tridimensionais ou fórmulas de matriz no Excel.

Uma fórmula de matriz é capaz de executar vários cálculos em um ou mais dos itens de uma matriz. As fórmulas de matriz podem retornar vários resultados ou um único resultado. Por exemplo, você pode colocar uma fórmula de matriz em um intervalo de células e usá-la para calcular uma coluna ou linha de subtotais. Também é possível colocar uma fórmula de matriz em uma única célula e calcular um único valor. No primeiro caso, temos a chamada fórmula de várias células; já o segundo tipo é conhecido como fórmula de uma célula.

Os exemplos da próxima seção mostram como criar fórmulas de matriz de várias células e de uma célula.

Experimente!

Este exercício mostra como usar fórmulas de matriz de várias células e de uma célula para calcular um conjunto de estatísticas de vendas. O primeiro conjunto de etapas usa uma fórmula de várias células para calcular um conjunto de subtotais. O segundo conjunto usa uma fórmula de uma célula para calcular um total geral.

Criar uma fórmula de matriz de várias células

  1. Abra uma nova pasta de trabalho em branco.
  2. Copie os dados da planilha de exemplo e cole-os na nova pasta de trabalho, começando na célula A1.
Vendedor Tipo de Carro Qtd Vendida Preço Unitário Total de Vendas
Barnhill Sedan 5 2200  
  Coupe 4 1800  
Ingle Sedan 6 2300  
  Coupe 8 1700  
Jordan Sedan 3 2000  
  Coupe 1 1600  
Pica Sedan 9 2150  
  Coupe 5 1950  
Sanchez Sedan 6 2250  
  Coupe 8 2000  

  1. Use o botão Opções de Colagem Imagem do botão exibido próximo para fazer correspondência com a formatação de destino.
  2. Para multiplicar os valores da matriz (o intervalo de células de C2 a D11), selecione as células E2 a E11 e insira a seguinte fórmula na barra de fórmulas:

=C2:C11*D2:D11

  1. Pressione CTRL+SHIFT+ENTER.

O Excel coloca a fórmula entre chaves ({ }) e coloca uma instância da fórmula em cada célula do intervalo selecionado. Isso acontece muito depressa, por isso, o que você vê na coluna E é o valor total das vendas de cada tipo de carro para cada vendedor.


Dados de exemplo


Criar uma fórmula de matriz de uma célula

  1. Na célula A13 da pasta de trabalho, digite Total de Vendas.
  2. Na célula B13, digite a seguinte fórmula e pressione CTRL+SHIFT+ENTER:

=SOMA(C2:C11*D2:D11)

Neste caso, o Excel multiplica os valores da matriz (o intervalo de células C2 a D11) e usa a função SOMA para somar os totais. O resultado é um total geral de R$ 111.800 em vendas. Esse exemplo mostra como esse tipo de fórmula pode ser eficiente. Por exemplo, vamos supor que você tenha 15.000 linhas de dados. Você pode somar esses dados no todo ou em parte criando uma fórmula de matriz em uma única célula.

Além disso, observe que a fórmula de uma célula (na célula B13) é completamente independente da fórmula de várias células (a fórmula nas células E2 a E11). Isso demonstra outra vantagem de usar fórmulas de matriz — flexibilidade. Você pode executar diversas ações, como alterar as fórmulas na coluna E ou excluir toda a coluna, sem afetar a fórmula de uma célula.

As fórmulas de matriz também oferecem estas vantagens:

  • Consistência    Se clicar em qualquer das células de E2 para baixo, você verá a mesma fórmula. Essa consistência pode ajudar a garantir mais precisão.
  • Segurança    Não é possível substituir um componente de uma fórmula de matriz de várias células. Por exemplo, clique na célula E3 e pressione DELETE. Você terá que selecionar o intervalo de células inteiro (E2 a E11) e alterar a fórmula da matriz inteira, ou então deixar a matriz como está. Como uma medida de segurança a mais, é necessário pressionar CTRL+SHIFT+ENTER para confirmar a alteração na fórmula.
  • Arquivos menores    Você pode usar uma única fórmula de matriz em vez de várias fórmulas intermediárias. Por exemplo, a pasta de trabalho que você criou para este exercício usa uma fórmula de matriz para calcular os resultados na coluna E. Se tivesse usado fórmulas padrão (como =C2*D2), você teria usado 11 fórmulas diferentes para calcular os mesmos resultados.

Visão geral da sintaxe das fórmulas de matriz

Em geral, as fórmulas de matriz usam a sintaxe padrão de fórmulas. Todas elas começam com um sinal de igual (=) e é possível usar qualquer uma das funções internas do Excel nas fórmulas de matriz. A principal diferença é que, quando se usa uma fórmula de matriz, é necessário pressionar CTRL+SHIFT+ENTER para inserir a fórmula. Quando você faz isso, o Excel coloca a fórmula de matriz entre chaves — se você digitar as chaves manualmente, a fórmula será convertida em uma cadeia de texto e não funcionará.

O próximo aspecto que você precisa entender é que as funções de matriz são uma forma de taquigrafia. Por exemplo, a função de várias células que você usou anteriormente é o equivalente de:

=C2*D2
=C3*D3

e assim por diante. A fórmula de uma célula na célula B13 condensa todas essas operações de multiplicação, mais a aritmética necessária para somar esses subtotais: =E2+E3+E4 e assim por diante.

Regras para inserir e alterar fórmulas de matriz

Vale a pena repetir a principal regra para criar uma fórmula de matriz: pressione CTRL+SHIFT+ENTER sempre que você precisar inserir ou editar uma fórmula de matriz. Essa regra se aplica tanto a fórmulas de uma célula quanto de várias células.

Sempre que trabalhar com fórmulas de várias células, você também precisará seguir estas regras:

  • É necessário selecionar o intervalo de células que receberá os resultados antes de inserir a fórmula. Você fez isso na etapa 3 do exercício de fórmula de várias células quando selecionou as células E2 a E11.
  • Não é possível alterar o conteúdo de uma célula individual em uma fórmula de matriz. Para tentar fazer isso, selecione a célula E3 na pasta de trabalho de exemplo e pressione DELETE.
  • Você pode mover ou excluir uma fórmula de matriz inteira, mas não pode mover ou excluir parte dela. Em outras palavras, para reduzir uma fórmula de matriz, primeiro exclua a fórmula existente e então comece do zero.

 Dica   Para excluir uma fórmula de matriz, selecione a fórmula inteira (por exemplo, =C2:C11*D2:D11), pressione DELETE e pressione CTRL+SHIFT+ENTER.

  • Não é possível inserir células em branco ou excluir células de uma fórmula de matriz de várias células.

Expandindo uma fórmula de matriz

Às vezes, pode ser necessário expandir uma fórmula de matriz. (Lembre-se de que não é possível reduzir uma fórmula de matriz.) O processo não é complicado, mas você precisa se lembrar das regras listadas na seção anterior.

  1. Na pasta de trabalho de exemplo, limpe o texto e as fórmulas de uma célula localizadas abaixo da tabela principal.
  2. Cole estas linhas de dados adicionais na pasta de trabalho, começando na célula A12. Use o botão Opções de Colagem Imagem do botão exibido próximo para fazer correspondência com a formatação de destino.

Toth Sedan 6 2500
  Coupe 7 1900
Wang Sedan 4 2200
  Coupe 3 2000
Young Sedan 8 2300
  Coupe 8 2100

  1. Selecione o intervalo de células que contém a fórmula de matriz atual (E2:E11), mais as células vazias (E12:E17) que vêm em seguida aos novos dados. Em outras palavras, selecione as células E2:E17.
  2. Pressione F2 para alternar para o modo de edição.
  3. Na barra de fórmulas, altere C11 para C17, altere D11 para D17 e pressione CTRL+SHIFT+ENTER. O Excel atualizará a fórmula nas células E2 a E11 e colocará uma instância da fórmula nas novas células, E12 a E17.

Dados de exemplo


Desvantagens de usar fórmulas de matriz

As fórmulas de matriz podem parecer mágicas, mas elas também têm algumas desvantagens:

  • Pode acontecer de você se esquecer de pressionar CTRL+SHIFT+ENTER. Lembre-se de pressionar essa combinação de teclas sempre que inserir ou editar uma fórmula de matriz.
  • Outros usuários podem não entender as fórmulas. Na prática, normalmente não há documentação sobre as fórmulas de matriz em uma planilha; por isso, se outros usuários precisarem modificar suas pastas de trabalho, evite fórmulas de matriz ou verifique se esses usuários sabem como alterá-las.
  • Dependendo da velocidade de processamento e da memória do computador, fórmulas de matriz grandes podem reduzir a velocidade dos cálculos.

Para obter mais informações sobre como trabalhar com fórmulas de matriz, consulte os seguintes tópicos:

Início da página Início da página

Aprender sobre constantes de matriz

Esta seção apresenta as constantes de matriz e explica como inseri-las, editá-las e solucionar problemas relativos a elas.

Uma breve introdução a constantes de matriz

As constantes de matriz são um componente das fórmulas de matriz. Você cria constantes de matriz inserindo uma lista de itens e colocando a lista entre chaves manualmente ({ }), como se segue:

={1;2;3;4;5}

Anteriormente neste artigo, nós enfatizamos a necessidade de pressionar CTRL+SHIFT+ENTER ao criar fórmulas de matriz. Como as constantes de matriz são um componente das fórmulas de matriz, coloque as constantes entre chaves digitando-as manualmente. Em seguida, use CTRL+SHIFT+ENTER para inserir a fórmula inteira.

Se delimitar (separar) os itens usando vírgulas, você criará uma matriz horizontal (uma linha). Se delimitar os itens usando ponto-e-vírgulas, você criará uma matriz vertical (uma coluna). Para criar uma matriz bidimensional, delimite os itens em cada linha usando vírgulas e delimite cada linha usando ponto-e-vírgulas.

Como nas fórmulas de matriz, você pode usar as constantes de matriz com quaisquer das funções internas que o Excel oferece. As seções a seguir explicam como criar cada tipo de constante e como usar essas constantes com funções no Excel.

Criar constantes unidimensionais e bidimensionais

O procedimento a seguir oferece alguma prática na criação de constantes horizontais, verticais e bidimensionais.

Criar uma constante horizontal

  1. Use a pasta de trabalho dos exemplos anteriores ou crie uma nova pasta de trabalho.
  2. Selecione as células A1 a E1.
  3. Na barra de fórmulas, insira a seguinte fórmula e pressione CTRL+SHIFT+ENTER:

={1,2,3,4,5}

 Observação   Neste caso, você deve digitar as chaves de abertura e fechamento ({ }).

O seguinte resultado será exibido:


Constante de matriz horizontal em uma fórmula de matriz


Talvez você fique imaginando por que não pode digitar os números manualmente. A seção Usar constantes em fórmulas, mais adiante neste artigo, demonstra as vantagens de usar constantes de matriz.

Criar uma constante vertical

  1. Na pasta de trabalho, selecione uma coluna de cinco células.
  2. Na barra de fórmulas, insira a seguinte fórmula e pressione CTRL+SHIFT+ENTER:

={1;2;3;4;5}

O seguinte resultado será exibido:


Constante de matriz vertical em uma fórmula de matriz


Criar uma constante bidimensional

  1. Na pasta de trabalho, selecione um bloco de células de quatro colunas de largura e três linhas de altura.
  2. Na barra de fórmulas, insira a seguinte fórmula e pressione CTRL+SHIFT+ENTER:

={1,2,3,4;5,6,7,8;9,10,11,12}

O seguinte resultado será exibido:


Constante de matriz bidimensional em uma fórmula de matriz


Usar constantes em fórmulas

Agora que você está familiarizado com a inserção de constantes de matriz, aqui vai um exemplo simples que usa o que acaba de ser descrito:

  1. Abra uma planilha em branco.
  2. Copie a tabela a seguir, começando na célula A1. Use o botão Opções de Colagem Imagem do botão exibido próximo para fazer correspondência com a formatação de destino.

3 4 5 6 7

  1. Na célula A3, insira a seguinte fórmula e pressione CTRL+SHIFT+ENTER:

=SOMA(A1:E1*{1,2,3,4,5})

Observe que o Excel colocará a constante entre um segundo par de chaves, porque você a digitou como uma fórmula de matriz.


Fórmula de matriz com constante de matriz


O valor 85 será exibido na célula A3. A próxima seção explica como a fórmula funciona.

Visão geral da sintaxe das constantes de matriz

A fórmula que você acabou de usar contém várias partes.


Sintaxe de fórmula de matriz com constante de matriz

Texto explicativo 1 Função
Texto explicativo 2 Matriz armazenada
Texto explicativo 3 Operador
Texto explicativo 4 Constante de matriz

O último elemento dentro dos parênteses é a constante de matriz: {1,2,3,4,5}. Lembre-se de que o Excel não coloca constantes de matriz entre chaves; você precisa fazê-lo. Lembre-se também de que, depois de adicionar uma constante a uma fórmula de matriz, você pressiona CTRL+SHIFT+ENTER para inserir a fórmula.

Como o Excel executa as operações das expressões entre parênteses primeiro, os próximos dois elementos a serem processados são os valores armazenados na pasta de trabalho (A1:E1) e o operador. Neste ponto, a fórmula multiplica os valores da matriz armazenada pelos valores correspondentes da constante. Isso equivale a:

=SOMA(A1*1,B1*2,C1*3,D1*4,E1*5)

Por fim, a função SOMA somará os valores e o resultado 85 será exibido na célula A3:

Para evitar usar a matriz armazenada e manter a operação inteiramente na memória, substitua a matriz armazenada por outra constante de matriz:

=SOMA({3,4,5,6,7}*{1,2,3,4,5})

Para tentar isso, copie a função, selecione uma célula em branco na pasta de trabalho, cole a fórmula na barra de fórmulas e pressione CTRL+SHIFT+ENTER. Você verá o mesmo resultado do exercício anterior que usou a fórmula de matriz =SOMA(A1:E1*{1,2,3,4,5}).

Elementos que podem ser usados em constantes

As constantes de matriz podem conter números, texto, valores lógicos (como VERDADEIRO e FALSO) e valores de erro (como #N/D). Você pode usar números nos formatos inteiro, decimal e científico. Se incluir texto, você terá que colocá-lo entre aspas duplas (").

As constantes de matriz não podem conter outras matrizes, fórmulas ou funções. Em outras palavras, elas só podem conter texto ou números que sejam separados por vírgula ou ponto-e-vírgula. O Excel exibirá uma mensagem de aviso quando você inserir uma fórmula como {1,2,A1:D4} ou {1,2,SOMA(Q2:Z8)}. Além disso, os valores numéricos não podem conter sinais de percentual, cifrões, vírgulas ou parênteses.

Nomeando constantes de matriz

Talvez o melhor modo de usar constantes de matriz seja nomeá-las. As constantes nomeadas podem ser muito mais fáceis de usar e escondem um pouco da complexidade das fórmulas de matriz dos usuários principiantes. Para nomear uma constante de matriz e usá-la em uma fórmula, siga este procedimento:

  1. Na guia Fórmulas, no grupo Nomes Definidos, clique em Definir Nome.
    A caixa de diálogo Definir Nome será exibida.
  2. Na caixa Nome, digite Trimestre1.
  3. Na caixa Refere-se a, insira a seguinte constante (lembre-se de digitar as chaves manualmente):

={"Janeiro","Fevereiro","Março"}

O conteúdo da caixa de diálogo deve ter esta aparência:

Caixa de diálogo Editar Nome com fórmula

  1. Clique em OK.
  2. Na planilha, selecione uma linha de três células em branco.
  3. Digite a seguinte fórmula e pressione CTRL+SHIFT+ENTER:

=Trimestre1

O seguinte resultado será exibido:


Constante de matriz nomeada inserida como fórmula


Quando você usar uma constante nomeada como uma fórmula de matriz, lembre-se de digitar o sinal de igual. Caso contrário, o Excel interpretará a matriz como uma cadeia de texto. Para concluir, lembre-se de que você pode usar combinações de texto e números.

Solução de problemas de constantes de matriz

Procure os seguintes problemas quando suas constantes de matriz não funcionarem:

  • Talvez alguns elementos não estejam separados pelo caractere correto. Se vírgulas ou pontos-e-vírgulas forem omitidos ou colocados no lugar errado, a constante de matriz poderá não ser criada corretamente ou uma mensagem de aviso poderá ser exibida.
  • Talvez você tenha selecionado um intervalo de células que não corresponda ao número de elementos da constante. Por exemplo, se você selecionar uma coluna de seis células para ser usada com uma constante de cinco células, o valor de erro #N/D será exibido na célula vazia. De maneira inversa, se você selecionar células de menos, o Excel omitirá os valores que não tenham uma célula correspondente.

Constantes de matriz em ação

Os exemplos a seguir demonstram algumas das formas pelas quais você pode colocar constantes de matriz para utilização em fórmulas de matriz. Alguns dos exemplos usam a função TRANSPOR para converter linhas em colunas e vice-versa.

Multiplicar cada item de uma matriz

  1. Selecione um bloco de células vazias com quatro colunas de largura e três linhas de altura.
  2. Digite a seguinte fórmula e pressione CTRL+SHIFT+ENTER:

={1,2,3,4;5,6,7,8;9,10,11,12}*2

Elevar os itens de uma matriz ao quadrado

  • Selecione um bloco de células vazias com quatro colunas de largura e três linhas de altura.
  • Digite a seguinte fórmula de matriz e pressione CTRL+SHIFT+ENTER:

={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

Como alternativa, digite esta fórmula de matriz, que utiliza o operador circunflexo (^):

={1,2,3,4;5,6,7,8;9,10,11,12}^2

Transpor uma linha unidimensional

  1. Selecione uma coluna de cinco células em branco.
  2. Digite a seguinte fórmula e pressione CTRL+SHIFT+ENTER:

=TRANSPOR({1,2,3,4,5})

Embora você tenha inserido uma constante de matriz horizontal, a função TRANSPOR converterá a constante em uma coluna.

Transpor uma coluna unidimensional

  1. Selecione uma linha de cinco células em branco.
  2. Insira a seguinte fórmula e pressione CTRL+SHIFT+ENTER:

=TRANSPOR({1;2;3;4;5})

Embora você tenha inserido uma constante de matriz vertical, a função TRANSPOR converterá a constante em uma linha.

Transpor uma constante bidimensional

  1. Selecione um bloco de células com três colunas de largura e quatro linhas de altura.
  2. Insira a seguinte constante e pressione CTRL+SHIFT+ENTER:

=TRANSPOR({1,2,3,4;5,6,7,8;9,10,11,12})

A função TRANSPOR converterá cada linha em uma série de colunas.

Início da página Início da página

Colocando fórmulas de matriz básicas para funcionar

Esta seção contém exemplos de fórmulas de matriz básicas.

Introdução

Use os dados desta seção para criar duas planilhas de exemplo.

  1. Abra uma pasta de trabalho existente ou crie uma pasta de trabalho nova, que precisa conter duas planilhas em branco.
  2. Copie os dados da tabela abaixo e cole-os na planilha, começando na célula A1.
400 como pode 1 2 3 4
1200 um peixe 5 6 7 8
3200 vivo viver 9 10 11 12
475 fora de 13 14 15 16
500 água fria
2000
600
1700
800
2700

Sua planilha concluída deve ter esta aparência:

Dados de exemplo completos

  1. Nomeie a primeira planilha como Dados e uma segunda planilha em branco como Matrizes.

Criar matrizes e constantes de matriz com base em valores existentes

O exemplo a seguir explica como usar fórmulas de matriz para criar vínculos entre intervalos de células em planilhas diferentes. Também mostra como criar uma constante de matriz com base no mesmo conjunto de valores.

Criar uma matriz com base em valores existentes

  1. Na pasta de trabalho de exemplo, selecione a planilha Matrizes.
  2. Selecione o intervalo de células C1 a E3.
  3. Insira a seguinte fórmula na barra de fórmulas e pressione CTRL+SHIFT+ENTER:

=Dados!E1:G3

O seguinte resultado será exibido:

Resultados na planilha Matrizes

A fórmula é vinculada aos valores armazenados nas células E1 a G3 na planilha Dados. A alternativa para essa fórmula de matriz de várias células é colocar uma fórmula única em cada célula da planilha Matrizes, desta forma:


=Dados!E1 =Dados!F1 =Dados!G1
=Dados!E2 =Dados!F2 =Dados!G2
=Dados!E3 =Dados!F3 =Dados!G3

Se você alterar alguns dos valores na planilha Dados, essas alterações serão exibidas na planilha Matrizes. Lembre-se de que, para alterar qualquer valor na planilha Dados, você precisa seguir as regras para editar fórmulas de matriz. Para obter mais informações sobre essas regras, consulte a seção Aprender sobre fórmulas de matriz.

Criar uma constante de matriz com base em valores existentes

  1. Na planilha Matrizes, selecione as células C1 a E3.
  2. Pressione F2 para alternar para o modo de edição.
  3. Pressione F9 para converter as referências de célula em valores. O Excel converterá os valores em uma constante de matriz.
  4. Pressione CTRL+SHIFT+ENTER para inserir a constante de matriz como uma fórmula de matriz.

O Excel substituirá a fórmula de matriz =Dados!E1:G3 pela seguinte constante de matriz:

={1,2,3;5,6,7;9,10,11}

O vínculo entre as planilhas Dados e Matrizes foi desfeito e a fórmula de matriz foi substituída por uma constante de matriz.

Contar caracteres em um intervalo de células

O exemplo a seguir mostra como contar o número de caracteres, inclusive espaços, em um intervalo de células.

  • Na planilha Dados, insira a fórmula a seguir na célula C7 e pressione CTRL+SHIFT+ENTER:

=SOMA(NÚM.CARACT(C1:C5))

O valor 47 será exibido na célula C7.

Neste caso, a função NÚM.CARACT retornará o comprimento de cada cadeia de texto em cada uma das células do intervalo. Em seguida, a função SOMA somará esses valores e exibirá o resultado na célula que contém a fórmula, C7.

Localizar os n menores valores de um intervalo

Este exemplo mostra como localizar os três menores valores de um intervalo de células.

  1. Na planilha Dados, selecione as células A12 a A14.

Esse conjunto de células conterá os resultados retornados pela fórmula de matriz.

  1. Na barra de fórmulas, insira a seguinte fórmula e pressione CTRL+SHIFT+ENTER:

=MENOR(A1:A10,{1;2;3})

Os valores 400, 475 e 500 serão exibidos nas células A12 a A14, respectivamente.

Essa fórmula usa uma constante de matriz para avaliar a função MENOR três vezes e retornar o menor (1), o segundo menor (2) e o terceiro menor (3) membro da matriz contido nas células A1:A10. Para localizar mais valores, adicione mais argumentos à constante e um número equivalente de células de resultado ao intervalo A12:A14. Você também pode usar funções adicionais com essa fórmula, como SOMA ou MÉDIA. Por exemplo:

=SOMA(MENOR(A1:A10,{1;2;3}))

=MÉDIA(MENOR(A1:A10,{1;2;3}))

Localizar os n maiores valores de um intervalo

Para localizar os maiores valores de um intervalo, você pode substituir a função MENOR pela função MAIOR. Além disso, o exemplo a seguir usa as funções LINHA e INDIRETO.

  1. Na planilha Dados, selecione as células A12 a A14.
  2. Pressione DELETE para limpar a fórmula existente, mas deixar as células selecionadas.
  3. Na barra de fórmulas, insira a seguinte fórmula e pressione CTRL+SHIFT+ENTER:

=MAIOR(A1:A10,LINHA(INDIRETO("1:3")))

Os valores 3200, 2700 e 2000 serão exibidos nas células A12 a A14, respectivamente.

Neste ponto, pode ser útil saber um pouco sobre as funções LINHA e INDIRETO. Você pode usar a função LINHA para criar uma matriz de inteiros consecutivos. Por exemplo, selecione uma coluna vazia de 10 células na pasta de trabalho do exercício, insira esta fórmula de matriz nas células A1:A10 e pressione CTRL+SHIFT+ENTER:

=LINHA(1:10)

A fórmula cria uma coluna de 10 inteiros consecutivos. Para ver um possível problema, insira uma linha acima do intervalo que contém a fórmula de matriz (ou seja, acima da linha 1). O Excel ajustará as referências de linha e a fórmula irá gerar inteiros de 2 a 11. Para corrigir esse problema, adicione a função INDIRETO à fórmula:

=LINHA(INDIRETO("1:10"))

A função INDIRETO usa cadeias de texto como argumentos (é por isso que o intervalo 1:10 é colocado entre aspas duplas). O Excel não ajusta valores de texto quando você insere linhas ou move a fórmula de matriz de alguma outra maneira. Como resultado, a função LINHA sempre gera a matriz de inteiros desejada.

Vamos examinar a fórmula que você usou anteriormente — =MAIOR(A1:A10,LINHA(INDIRETO("1:3"))) — dos parênteses internos para os externos: a função INDIRETO retorna um conjunto de valores de texto, neste caso os valores 1 a 3. A função LINHA, por sua vez, gera uma matriz colunar de três células. A função MAIOR usa os valores no intervalo de células A1:A10 e é avaliada três vezes, uma vez para cada referência retornada pela função LINHA. Os valores 3200, 2700 e 2000 são retornados para a matriz colunar de três células. Para localizar mais valores, adicione um intervalo de células maior à função INDIRETO.

Para concluir, você pode usar essa fórmula com outras funções, como SOMA e MÉDIA.

Localizar a cadeia de texto mais longa de um intervalo de células

Este exemplo localiza a cadeia de texto mais longa de um intervalo de células. Esta fórmula só funcionará quando um intervalo de dados contiver uma única coluna de células.

  • Na planilha Dados, limpe a fórmula existente da célula C7, insira a seguinte fórmula nessa célula e pressione CTRL+SHIFT+ENTER:

=ÍNDICE(C1:C5,CORRESP(MÁX(NÚM.CARACT(C1:C5)),NÚM.CARACT(C1:C5),0),1)

O valor vivo viver será exibido na célula C7.

Vamos examinar a fórmula, dos elementos internos para os externos. A função NÚM.CARACT retorna o comprimento de cada um dos itens do intervalo de células C1:C5. A função MÁX calcula o maior valor entre esses itens, que corresponde à cadeia de texto mais longa, que está na célula C3.

É neste ponto que as coisas ficam um pouco mais complexas. A função CORRESP calcula o deslocamento (a posição relativa) da célula que contém a cadeia de texto mais longa. Para fazer isso, são necessários três argumentos: um valor de pesquisa, uma matriz de pesquisa e um tipo de correspondência. A função CORRESP procura na matriz de pesquisa o valor de pesquisa especificado. Neste caso, o valor de pesquisa é a cadeia de texto mais longa:

(MÁX(NÚM.CARACT(C1:C5))

e essa cadeia de caracteres reside nesta matriz:

NÚM.CARACT(C1:C5)

O argumento tipo de correspondência é 0. O tipo de correspondência pode consistir em um valor 1, 0 ou -1. Se você especificar 1, CORRESP retornará o maior valor que seja menor ou igual ao valor de pesquisa. Se você especificar 0, CORRESP retornará o primeiro valor que seja exatamente igual ao valor de pesquisa. Se você especificar -1, CORRESP localizará o menor valor que seja maior ou igual ao valor de pesquisa especificado. Se você omitir um tipo de correspondência, o Excel adotará o valor 1.

Para concluir, a função ÍNDICE opera com estes argumentos: uma matriz e um número de linha e coluna dentro dessa matriz. O intervalo de células C1:C5 fornece a matriz, a função CORRESP fornece o endereço de célula e o argumento final (1) especifica que o valor vem da primeira coluna da matriz.

Início da página Início da página

Colocando fórmulas de matriz avançadas para funcionar

Esta seção contém exemplos de fórmulas de matriz avançadas.

Somar um intervalo que contém valores de erro

A função SOMA do Excel não funciona quando você tenta somar um intervalo que contém um valor de erro, como #N/D. Este exemplo mostra como somar os valores em um intervalo chamado Dados que contém erros.

=SOMA(SE(ÉERRO(Dados),"",Dados))

A fórmula cria uma nova matriz contendo os valores originais menos quaisquer valores de erro. Das funções internas para as externas, a função ÉERRO pesquisa o intervalo de células (Dados) em busca de erros. A função SE retornará um valor específico se uma condição especificada for avaliada como VERDADEIRO e outro valor se ela for avaliada como FALSO. Neste caso, ela retorna cadeias de caracteres vazias ("") para todos os valores de erro, pois eles são avaliados como VERDADEIRO, e retorna os valores restantes do intervalo (Dados), pois eles são avaliados como FALSO, o que significa que eles não contêm valores de erro. Em seguida, a função SOMA calcula o total da matriz filtrada.

Contar o número de valores de erro em um intervalo

Este exemplo é semelhante à fórmula anterior, mas retorna o número de valores de erro em um intervalo chamado Dados em vez de filtrá-los:

=SOMA(SE(ÉERRO(Dados),1,0))

Essa fórmula cria uma matriz contendo o valor 1 para as células que contêm erros e o valor 0 para as que não contêm erros. Você pode simplificar a fórmula e conseguir o mesmo resultado removendo o terceiro argumento da função SE, deste modo:

SOMA(SE(ÉERRO(Dados),1))

Se você não especificar o argumento, a função SE retornará FALSO se uma célula não contiver um valor de erro. Você pode simplificar a fórmula ainda mais:

=SOMA(SE(ÉERRO(Dados)*1))

Essa versão funciona porque VERDADEIRO*1=1 e FALSO*1=0.

Somar valores com base em condições

Você pode precisar somar valores com base em condições. Por exemplo, esta fórmula de matriz soma apenas os inteiros positivos de um intervalo chamado Vendas:

=SOMA(SE(Vendas>0,Vendas))

A função SE cria uma matriz de valores positivos e falsos. A função SOMA essencialmente ignora os falsos valores, pois 0+0=0. O intervalo de células que você usa nessa fórmula pode consistir em qualquer número de linhas e colunas.

Você também pode somar valores que atendam a mais de uma condição. Por exemplo, esta fórmula de matriz calcula valores maiores que 0 e menores ou iguais a 5:

=SOMA((Vendas>0)*(Vendas<=5)*(Vendas))

Lembre-se de que essa fórmula retornará um erro se o intervalo contiver uma ou mais células não numéricas.

Você também pode criar fórmulas de matriz que usem um tipo de condição OU. Por exemplo, você pode somar valores que sejam menores que 5 e maiores que 15:

=SOMA(SE((Vendas<5)+(Vendas>15),Vendas))

A função SE localiza todos os valores menores que 5 e maiores que 15 e passa esses valores para a função SOMA.

 Importante   Não é possível usar as funções E e OU diretamente em fórmulas de matriz, pois essas funções retornam um único resultado, VERDADEIRO ou FALSO, e as funções de matriz exigem matrizes de resultados. Você pode contornar o problema usando a lógica mostrada na fórmula anterior. Em outras palavras, execute operações matemáticas, como adição ou multiplicação, em valores que atendam à condição OU ou E.

Calcular uma média que exclua zeros

Este exemplo mostra como remover zeros de um intervalo quando você precisar calcular a média dos valores desse intervalo. A fórmula usa um intervalo de dados chamado Vendas:

=MÉDIA(SE(Vendas<>0,Vendas))

A função SE cria uma matriz de valores que não são iguais a 0 e passa esses valores para a função MÉDIA.

Contar o número de diferenças entre dois intervalos de células

Esta fórmula de matriz compara os valores de dois intervalos de células chamados MeusDados e SeusDados e retorna o número de diferenças entre os dois. Se o conteúdo dos dois intervalos for idêntico, a fórmula retornará 0. Para usar esta fórmula, os intervalos de células devem ter o mesmo tamanho e a mesma dimensão:

=SOMA(SE(MeusDados=SeusDados,0,1))

A fórmula criará uma nova matriz do mesmo tamanho dos intervalos que você está comparando. A função SE preencherá a matriz com o valor 0 e o valor 1 (0 para células que não correspondam e 1 para células idênticas). Em seguida, a função SOMA retornará a soma dos valores da matriz.

Você pode simplificar a fórmula deste modo:

=SOMA(1*(MeusDados<>SeusDados))

Como a fórmula que conta valores de erro em um intervalo, essa fórmula funciona porque VERDADEIRO*1=1 e FALSO*1=0.

Localizar o valor máximo em um intervalo

Esta fórmula de matriz retorna o número de linha do valor máximo em um intervalo de uma única coluna chamado Dados:

=MÍN(SE(Dados=MÁX(Dados),LINHA(Dados),""))

A função SE cria uma nova matriz que corresponde ao intervalo chamado Dados. Se uma célula correspondente contiver o valor máximo no intervalo, a matriz conterá o número da linha. Caso contrário, a matriz conterá uma cadeia de caracteres vazia (""). A função MÍN usa a nova matriz como segundo argumento e retorna o menor valor, que corresponde ao número da linha do valor máximo em Dados. Se o intervalo chamado Dados contiver valores máximos idênticos, a fórmula retornará a linha do primeiro valor.

Para retornar o endereço de célula real de um valor máximo, use esta fórmula:

=ENDEREÇO(MÍN(SE(Dados=MÁX(Dados),LINHA(Dados),"")),COLUNA(Dados))

Início da página Início da página

 
 
Aplica-se a:
Excel 2010