Diretrizes e exemplos de fórmulas de matriz

Importante :  Este artigo foi traduzido por um sistema de tradução automática, leia o aviso de isenção de responsabilidade. Para sua referência, veja a versão em inglês deste artigo aqui.

Para se tornar um usuário avançado do Excel, você precisa saber como usar fórmulas de matriz, que podem executar cálculos que você não pode fazer usando fórmulas não-matriz. O seguinte artigo baseia-se em uma série de colunas de usuário do Excel Power escrito por Colin Wilcox e adaptados de capítulos 14 e 15 de Fórmulas do Excel 2002, um livro escrito por John Walkenbach, MVP do Excel.

Neste artigo

Saiba mais sobre fórmulas de matriz

Aprender sobre constantes de matriz

Criar constantes unidimensionais e bidimensionais

Colocando fórmulas de matriz básicas para funcionar

Colocando fórmulas de matriz avançadas para funcionar

Aprender sobre fórmulas de matriz

Esta seção apresenta as fórmulas de matriz e explica como inserir, editar e solucioná-los.

Por que usar fórmulas de matriz?

Se você tiver experiência usando fórmulas no Excel, você sabe que você pode realizar algumas operações razoavelmente sofisticadas. Por exemplo, você pode calcular o custo total de um empréstimo sobre qualquer determinado número de anos. No entanto, se você quiser realmente mestres fórmulas no Excel, você precisa saber como usar fórmulas de matriz. Você pode usar fórmulas de matriz faça tarefas complexas, como:

  • 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 : Você pode ver as fórmulas de matriz denominadas "Fórmulas CSE," porque você deve pressionar CTRL + SHIFT + ENTER para inseri-las nas suas pastas de trabalho.

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

Se você já fez programação um pouco, você provavelmente já tiver executado em todo o termo matriz. Para nossas finalidades, uma matriz é uma coleção de itens. No Excel, esses itens podem residem em uma única linha (chamado de uma matriz horizontal unidimensional), uma coluna (vertical unidimensional), ou várias linhas e colunas (uma matriz bidimensional). É possível criar matrizes tridimensionais ou fórmulas de matriz no Excel.

Uma fórmula de matriz é uma fórmula que pode executar vários cálculos em um ou mais dos itens em uma matriz. 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 usar a fórmula de matriz para calcular uma coluna ou linha de subtotais. Você pode também posicionar uma fórmula de matriz em uma única célula e, em seguida, calcular um valor único. Uma fórmula de matriz que reside em várias células é chamada de uma fórmula de várias célula e uma fórmula de matriz que reside em uma única célula é chamada de uma fórmula de 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, em seguida, cole a nova pasta de trabalho começando na célula A1.

Vendedor

Tipo de Carro

Qtd Vendida

Preço Unitário

Total de Vendas

Oliveira

Sedã

5

2200

Cupê

4

1800

Ingle

Sedã

6

2300

Cupê

8

1700

Jordan

Sedã

3

2000

Cupê

1

1600

Gonçalo

Sedã

9

2150

Cupê

5

1950

Sanchez

Sedã

6

2250

Cupê

8

2000

  1. Use o botão Opções de colagem Imagem do botão exibido próximo para corresponder à formatação de destino.

  2. Para multiplicar os valores na matriz (o intervalo de células C2 a D11), selecione as células E2 a E11 e, em seguida, insira a seguinte fórmula na barra de fórmulas:

    =C2:C11*D2:D11

  3. Pressione CTRL + SHIFT + ENTER.

O Excel envolve a fórmula com chaves ({}) e coloca uma instância da fórmula em cada célula do intervalo selecionado. Isso acontece rapidamente, assim que você vê na coluna E é a quantidade total de venda para 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)

Nesse caso, o Excel multiplica os valores na matriz (o intervalo de células C2 a D11) e, em seguida, usa a função soma para adicionar os totais juntos. O resultado é um total geral de r $111,800 em vendas. Este exemplo mostra como poderosos esse tipo de fórmula pode ser. Por exemplo, suponha que você tenha 15.000 linhas de dados. Você pode somar parte ou todos os dados criando uma fórmula de matriz em uma única célula.

Observe também que a fórmula da célula (na célula B13) é completamente independente da fórmula de várias célula (a fórmula nas células E2 a E11). Isso demonstra outra vantagem de usar fórmulas de matriz — flexibilidade. Você pode criar qualquer número de ações, como alterar as fórmulas da coluna E ou excluir essa coluna totalmente, sem afetar a fórmula da 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    Você não pode 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ê precisa selecionar todo o intervalo de células (E2 através de E11) e alterar a fórmula de matriz inteira ou deixe a matriz como ela está. Como uma medida de segurança adicional, você deve pressionar CTRL + SHIFT + ENTER para confirmar a alteração à fórmula.

  • Arquivos menores    Muitas vezes, 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 você tivesse usado fórmulas padrão (como = C2 * D2), seria usada 11 fórmulas diferentes para calcular os mesmos resultados.

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

Em geral, fórmulas de matriz usam sintaxe de fórmula padrão. Todos eles começam com um sinal de igual (=) e você pode usar qualquer uma das funções internas do Excel em fórmulas de matriz. A principal diferença é que, ao usar uma fórmula de matriz, você deve pressionar CTRL + SHIFT + ENTER para inserir sua fórmula. Quando você fizer isso, o Excel colocará a fórmula de matriz com chaves — se você digita as chaves manualmente, sua fórmula será convertida em uma cadeia de texto e ele não funcionará.

A próxima coisa que você precisa compreender é que funções de matriz são uma forma abreviada. Por exemplo, a função de várias célula que você usou anteriormente é o equivalente:

= C2 * D2
= C3 * D3

e assim por diante. A fórmula de célula na célula B13 condensa todas essas operações de multiplicação, além da aritmética necessários para adicionar esses subtotais: = E2 + E3 + E4e assim por diante.

Regras para inserir e alterar fórmulas de matriz

A regra principal para a criação de uma fórmula de matriz vale repetição: pressione CTRL + SHIFT + ENTER sempre que você precisar inserir ou editar uma fórmula de matriz. Essa regra se aplica a fórmulas de célula e várias células.

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

  • Você deve selecionar o intervalo de células para armazenar seus resultados antes de inserir a fórmula. Você fez isso na etapa 3 do exercício de fórmula de matriz de várias células quando selecionado células E2 a E11.

  • Você não pode alterar o conteúdo de uma única célula em uma fórmula de matriz. Para tentar fazer isso, selecione a célula E3 na pasta de trabalho de exemplo e pressione Excluir.

  • Você pode mover ou excluir uma fórmula de matriz inteira, mas você não pode mover ou excluir parte dele. Em outras palavras, para reduzir uma fórmula de matriz, você primeiro excluir a fórmula existente e, em seguida, recomeçar.

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

  • Você não pode 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, talvez você precise expandir uma fórmula de matriz. (Lembre-se de que você não pode reduzir uma fórmula de matriz.) O processo não é complicado, mas você deve lembrar as regras listadas na seção anterior.

  1. A pasta de trabalho de exemplo, desmarque qualquer texto e fórmulas de célula que se encontram abaixo da tabela principal.

  2. Cole a pasta de trabalho começando na célula A12 essas linhas adicionais de dados. Use o botão Opções de colagem Imagem do botão exibido próximo para corresponder à formatação de destino.

Toth

Sedã

6

2500

Cupê

7

1900

Wang

Sedã

4

2200

Cupê

3

2000

Young

Sedã

8

2300

Cupê

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, alterar C11 para C17, altere D11 para D17e, em seguida, pressione CTRL + SHIFT + ENTER. O Excel atualiza a fórmula nas células E2 a E11 e coloca uma instância da fórmula nas células novo, E12 a E17.

    Dados de exemplo

Desvantagens de usar fórmulas de matriz

Fórmulas de matriz podem parecer mágicos, mas também têm algumas desvantagens:

  • Ocasionalmente, você pode esquecer a pressionar CTRL + SHIFT + ENTER. Lembre-se ao pressionar esta combinação de teclas, sempre que você inserir ou edita uma fórmula de matriz.

  • Outros usuários podem não entender suas fórmulas. Na prática, fórmulas de matriz são geralmente não documentadas em uma planilha, para que se outras pessoas precisarem modificar suas pastas de trabalho, você deve evitar fórmulas de matriz ou tornar sabe esses usuários compreendam como alterá-los.

  • 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 fórmulas de matriz de trabalho, consulte o seguinte:

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

Constantes de matriz são um componente de fórmulas de matriz. Criar constantes de matriz inserindo uma lista de itens e, em seguida, manualmente ao redor a lista com chaves ({}), assim:

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

Neste artigo, vamos enfatizado a necessidade de pressionar CTRL + SHIFT + ENTER quando você cria fórmulas de matriz. Como constantes de matriz são um componente de fórmulas de matriz, coloque as constantes com chaves manualmente digitando-as. Você, em seguida, use CTRL + SHIFT + ENTER para inserir a fórmula inteira.

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

Como com fórmulas de matriz, você pode usar constantes de matriz com qualquer uma das funções internas que o Excel fornece. 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

Você pode estar se perguntando por que você não pode apenas 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ê estiver familiarizado com a inserção de constantes de matriz, aqui está um exemplo simples que usa o que discutimos:

  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 corresponder à 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 fórmula com outro conjunto de chaves, porque ele foi inserido 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

1. Função

2. Matriz armazenada

3. Operador

4. Constante de matriz

O último elemento dentro dos parênteses é constante de matriz: {12,3,4,5}. Lembre-se de que o Excel não envolvem constantes de matriz com chaves; Você deve fazê-lo. Lembre-se também de que, depois de adicionar uma constante em 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)

Finalmente, a função soma adiciona os valores e a soma 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 experimentar isso, copie a função, selecione uma célula em branco na sua pasta de trabalho, cole a fórmula na barra de fórmulas e pressione CTRL + SHIFT + ENTER. Você vê o mesmo resultado como você no exercício anterior que usou a fórmula de matriz = SUM(A1:E1*{1,2,3,4,5}).

Elementos que podem ser usados em constantes

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 número inteiro, decimal e Científica. Se você incluir texto, você deve colocar o texto com aspas duplas (").

Constantes de matriz não podem conter conjuntos adicionais, fórmulas ou funções. Em outras palavras, eles podem conter apenas texto ou números separados por vírgulas ou ponto e vírgula. O Excel exibe uma mensagem de aviso quando você insere uma fórmula como {1,2, a1: D4} ou {1,2,SUM(Q2:Z8)}. Além disso, valores numéricos não podem conter sinais de porcentagem, cifrões, vírgulas ou parênteses.

Nomeando constantes de matriz

Possivelmente a melhor maneira de usar constantes de matriz é nomeá-los. Constantes nomeadas podem ser muito mais fácil de usar, e eles podem ocultar alguns da complexidade fórmulas de matriz de usuários iniciantes. Para nomear uma constante de matriz e usá-lo em uma fórmula, faça o seguinte:

  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, digiteTrimestre1.

  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

  4. Clique em OK.

  5. Na planilha, selecione uma linha de três células em branco.

  6. 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ê usa uma constante nomeada como uma fórmula de matriz, lembre-se de digitar o sinal de igual. Se não estiver, o Excel interpretará a matriz como uma cadeia de caracteres de texto. Finalmente, tenha em mente 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:

  • Alguns elementos não podem ser separados com o caractere adequado. Se você omitir uma vírgula ou ponto e vírgula, se você colocar um no lugar errado constante de matriz pode não ser criada corretamente ou você pode ver uma mensagem de aviso.

  • Talvez você tenha selecionado um intervalo de células que não corresponde ao número de elementos em sua constante. Por exemplo, se você selecionar uma coluna de seis células para uso com uma constante de cinco células, o valor de erro # n / aparece na célula vazia. Por outro lado, se você selecionar células poucos, o Excel omite os valores que não têm 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

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 nesta seção para criar duas planilhas de exemplo.

  1. Abrir uma pasta de trabalho existente ou criar uma nova pasta de trabalho e verifique se que ele contém duas planilhas em branco.

  2. Copie os dados na tabela a seguir e cole-a na planilha, começando na célula A1.

400

rápido.

1

2

3

4

1200

raposa marrom

5

6

7

8

3200

saltou sobre

9

10

11

12

475

a preguiça

13

14

15

16

500

usuário avançado

2000

600

1700

800

2700

Sua planilha terminada deve ter esta aparência.

Dados de exemplo completos

  1. Nome da primeira planilha a dadose o nome de uma segunda matrizesde planilha em branco.

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. Em sua pasta de trabalho de exemplo, selecione a planilha de 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

Os fórmulas links para os valores armazenados nas células E1 a G3 da planilha de dados. A alternativa para esta fórmula de matriz de várias células é colocar uma fórmula exclusiva em cada célula da planilha matrizes, da seguinte maneira.

= 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 aparecem na planilha matrizes. Lembre-se de que, para alterar os valores da planilha de dados, você precisa seguem as regras para edição de fórmulas de matriz. Para obter mais informações sobre essas regras, consulte a seção Aprenda sobre fórmulas de matriz.

Criar uma constante de matriz com base em valores existentes

  1. Na planilha matrizes, selecione 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 aos valores. Excel converte 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 substitui os = dados! E1:G3 fórmula com a seguinte constante de matriz de matriz:

= { 1.2.3; 5, 6,7; 9,10,11}

O link foi desfeito entre as planilhas de dados e matrizes 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 seguinte fórmula na célula C7 e, em seguida, pressione CTRL + SHIFT + ENTER:

    = SOMA (LEN(C1:C5))

O valor 47 aparece na célula C7.

Nesse caso, a função LEN retorna o comprimento de cada cadeia de texto em cada uma das células no intervalo. A função soma somará os valores e exibe 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.

    Este conjunto de células manterá os resultados retornados pela fórmula de matriz.

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

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

Os valores 400, 475e 500 sã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 ( minúscula (a1: a10, {1; 2; 3}))

= Médio ( minúscula (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 esta fórmula e pressione CTRL + SHIFT + ENTER:

    = LARGE(A1:A10,ROW(INDIRECT("1:3")))

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

Neste ponto, isso pode ajudar a saber um pouco sobre as funções de indireto e linha . Você pode usar a função lin para criar uma matriz de inteiros consecutivos. Por exemplo, selecione uma coluna vazia de 10 células na sua pasta de trabalho de prática, 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 — =grande (A1:A10,ROW(INDIRECT("1:3"))) — iniciando a partir dos parênteses internos e trabalhando para fora: A função indireto retornará um conjunto de valores de texto, nesse caso, os valores de 1 a 3. A função lin alternadamente gera uma matriz de coluna de três células. A função de grande usa os valores no intervalo de células a1: a10, e ele é avaliado três vezes, uma vez para cada referência retornado pela função linha . Os valores 3200, 2700 e 2000 são retornados à matriz célula três coluna. Se você quiser localizar mais valores, você adicionar 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 em um intervalo de células. Essa fórmula funciona apenas quando um intervalo de dados contiver uma única coluna de células.

  • Na planilha dados, desmarque a fórmula existente da célula C7, insira a seguinte fórmula na célula e pressione CTRL + SHIFT + ENTER:

    =INDEX(C1:C5,MATCH(MAX(LEN(C1:C5)),LEN(C1:C5),0),1)

O valor saltou sobre aparece na célula C7.

Vamos examine a fórmula, iniciando dos elementos internos e trabalhando para fora. A função LEN retorna o comprimento de cada um dos itens no intervalo de células C1: C5. A função máximo 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:

( MAX ( LEN(C1:C5))

e essa cadeia de caracteres reside nesta matriz:

LEN ( 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.

Por fim, a função índice leva esses 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 da célula e o argumento final (1) Especifica que o valor vem da primeira coluna da matriz.

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 ( IF(ISERROR(Data),"",Data))

A fórmula cria uma nova matriz que contém os valores originais menos quaisquer valores de erro. Iniciando das funções internas e trabalhando para fora, o ÉERROS função pesquisa o intervalo de células (dados) erros. A função se retorna um valor específico se uma condição especificada for avaliada como verdadeiro e outro valor se for avaliado como FALSE. Nesse caso, ela retorna cadeias de caracteres vazias ("") para todos os erros valores porque eles são avaliados como verdadeiro e retornará o restante valores do intervalo (dados) porque eles são avaliados como falso, indicando que eles não contenham valores de erro. A função soma calcula o total para a 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 ( IF(ISERROR(Data),1,0))

Esta fórmula cria uma matriz que contém o valor 1 para as células que contêm erros e o valor 0 para as células que não contêm erros. Você pode simplificar a fórmula e obter o mesmo resultado, removendo o terceiro argumento para a função se , assim:

= Soma ( IF(ISERROR(Data),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 ( IF(ISERROR(Data)*1))

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

Somar valores com base em condições

Você talvez precise somar valores com base em condições. Por exemplo, esta fórmula de matriz soma apenas os inteiros positivos em 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)*(Sales))

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 ((Sales<5) +(Sales>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 : Você não pode usar as funções AND e OR em fórmulas de matriz diretamente porque essas funções retornam um único resultado TRUE ou FALSE e 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, você executar operações matemáticas, como adição ou multiplicação, em valores que atendem ou ou condição 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édio ( IF (<> 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 em dois intervalos de células denominados meusdados e Seusdados e retorna o número de diferenças entre os dois. Se o conteúdo dos dois intervalos é idêntico, a fórmula retorna 0. Para usar esta fórmula, os intervalos de células devem ser do mesmo tamanho e da mesma dimensão:

= Soma ( IF ( 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:

= MIN ( IF(Data=MAX(Data),ROW(Data),""))

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 ( MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))

Início da página

Observação : Aviso de Isenção de Tradução Automática: Este artigo foi traduzido por computador, sem intervenção humana. A Microsoft oferece essas traduções automáticas para ajudar as pessoas que não falam inglês a aproveitar os textos escritos sobre produtos, serviços e tecnologias da Microsoft. Como este artigo foi traduzido automaticamente, é possível que contenha erros de vocabulário, sintaxe ou gramática.

Essas informações foram úteis?

Como podemos melhorá-lo?

Como podemos melhorá-lo?

Para proteger sua privacidade, não inclua informações de contato em seus comentários. Avalie nosso política de privacidade.

Obrigado por seus comentários!