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, o que podem realizar cálculos que você não pode fazer usando fórmulas de matriz não. O seguinte artigo é baseado em uma série de colunas do Excel energia usuário escrito por Colin Wilcox e adaptada a partir dos capítulos 14 e 15 das Fórmulas do Excel 2002, um livro escrito por John Walkenbach, MVP do Excel.

Neste artigo

Aprender 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 no uso de fórmulas no Excel, você sabe que você pode executar algumas operações relativamente sofisticadas. Por exemplo, você pode calcular o custo total de um empréstimo em qualquer determinado número de anos. No entanto, se você realmente deseja mestres fórmulas no Excel, você precisa saber como usar fórmulas de matriz. Você pode usar fórmulas de matriz para realizar 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ê pressionar CTRL + SHIFT + ENTER para inseri-los em 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 encontrar o termo matriz. Para nossos propósitos, uma matriz é uma coleção de itens. No Excel, esses itens podem residir em uma única linha (chamada de uma matriz unidimensional de horizontal), uma coluna (vertical unidimensional), ou várias linhas e colunas (uma matriz bidimensional). Você não pode criar matrizes tridimensionais ou fórmulas de matriz no Excel.

Uma fórmula de matriz é uma fórmula que pode desempenhar 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 inserir uma fórmula de matriz em um intervalo de células e use a fórmula matricial para calcular uma coluna ou linha de subtotais. Você pode também inserir 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 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 única.

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. Copiar os dados da planilha de exemplo e, em seguida, colá-lo na nova pasta começando pela 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 as Opções de colagem botão Imagem do botão que aparece 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 coloca a fórmula com chaves ({}) e coloca uma instância da fórmula em cada célula do intervalo selecionado. Isso acontecerá muito rapidamente, portanto, o que você vê na coluna E é o valor total das vendas 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 o Total de vendas.

  2. Na célula B13, digite a seguinte fórmula e, em seguida, 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 US $111,800 em vendas. Este exemplo mostra como poderoso esse tipo de fórmula pode ser. Por exemplo, suponha que você tenha de 15.000 linhas de dados. Você pode somar parte ou todos os dados, criando uma fórmula de matriz em uma única célula.

Além disso, observe que a fórmula de célula única (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 fazer qualquer número de ações, como as fórmulas na coluna E a alteração ou exclusão dessa coluna, sem afetar a fórmula de célula única.

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 com 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 toda a matriz ou deixar a matriz como está. Como uma medida de segurança adicional, você deve pressionar CTRL + SHIFT + ENTER para confirmar a alteração para a fórmula.

  • Tamanhos de arquivo menores    Você também pode usar uma única fórmula de matriz em vez de diversas 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), você teria que usar 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 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 suas fórmulas de matriz. A principal diferença é que, ao usar uma fórmula de matriz, você deve pressionar CTRL + SHIFT + ENTER para inserir a fórmula. Quando você fizer isso, o Excel coloca a fórmula de matriz com chaves — se você digitar as chaves manualmente, sua fórmula será convertida em uma seqüência de texto e não funcionará.

A próxima coisa que você precisa entender é que as 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 única na célula B13 condensa todas as operações de multiplicação, mais a aritmética necessárias para adicionar os subtotais: = E2 + E3 + E4e assim por diante.

Regras para inserir e alterar fórmulas de matriz

A regra principal para criar uma fórmula de matriz é a pena repetir: pressione CTRL + SHIFT + ENTER sempre que você precisa inserir ou editar uma fórmula de matriz. Essa regra se aplica às fórmulas de célula única e várias células.

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

  • Você deve selecionar o intervalo de células para armazenar os resultados antes de inserir a fórmula. Você fez isso na etapa 3 do exercício fórmulas de matriz com várias células quando você seleciona 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 testar 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, primeiro exclua a fórmula existente e inicie novamente.

    Dica   Para excluir uma fórmula de matriz, selecione a fórmula inteira (por exemplo, =C2:C11*D2:D11), pressione 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 com várias células.

Expandindo uma fórmula de matriz

Às vezes, talvez seja necessário expandir uma fórmula de matriz. (Lembre-se de que uma fórmula de matriz não podem ser reduzidos.) O processo não é complicado, mas você deve se lembrar as regras listadas na seção anterior.

  1. Na pasta de trabalho de exemplo, desmarque qualquer texto e fórmulas de célula única localizados abaixo da tabela principal.

  2. Cole essas linhas adicionais de dados na pasta de trabalho, começando pela célula A12. Use as Opções de colagem botão Imagem do botão que aparece 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, 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

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

  • Ocasionalmente, você pode esquecer de pressionar CTRL + SHIFT + ENTER. Lembre-se de pressionar essa 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 as fórmulas de matriz ou verifique se esses usuários compreendem 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 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

As constantes de matriz são um componente de fórmulas de matriz. Criar constantes de matriz inserindo uma lista de itens e manualmente ao redor da lista com chaves ({}), da seguinte forma:

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

Neste artigo, enfatizamos a necessidade de pressionar CTRL + SHIFT + ENTER ao criar fórmulas de matriz. Como as constantes de matriz são um componente de fórmulas de matriz, você deve colocar as constantes com chaves manualmente digitando-os. 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ê delimita os itens em cada linha usando vírgulas e você delimita cada coluna usando ponto e vírgula.

Assim como acontece com as fórmulas de matriz, você pode usar as constantes de matriz com qualquer uma das funções internas fornecidas pelo Excel. As seções a seguir explicam como criar cada tipo de constante e como usar essas constantes com funções do 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ê deve estar imaginando por que você não pode simplesmente digitar os números manualmente. A seção usar constantes em fórmulas , mais adiante neste artigo, demonstra as vantagens de usar as 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 digitação de constantes de matriz, aqui está um exemplo simples que utiliza o que discutimos:

  1. Abra uma planilha em branco.

  2. Copie a tabela a seguir começando pela célula A1. Use as Opções de colagem botão Imagem do botão que aparece próximo para corresponder à formatação de destino.

3

4

5

6

7

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

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

Observe que o Excel coloca 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 é a constante de matriz: {12,3,4,5}. Lembre-se de que o Excel não coloque constantes de matriz com chaves; Você deve fazer isso. Lembre-se também de que, depois de adicionar uma constante para 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 testar 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, em seguida, pressione CTRL + SHIFT + ENTER. Você verá 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

As constantes de matriz podem conter números, texto, valores lógicos (como verdadeiro e FALSO) e valores de erro (por exemplo, # n/d). Você pode usar números em formatos de número inteiro, decimal e científicos. Se você incluir texto, circunde o texto com aspas duplas (").

As constantes de matriz não podem conter conjuntos adicionais, fórmulas ou funções. Em outras palavras, elas podem conter apenas texto ou números que são separados por vírgulas ou ponto e vírgula. Excel exibe uma mensagem de aviso quando você inserir uma fórmula, como a1: D4 {1,2} 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

Provavelmente, a melhor maneira de usar as constantes de matriz é nomeá-los. Constantes nomeadas podem ser muito mais fácil de usar, e eles podem ocultar alguns a complexidade de suas 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, 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

  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ê usar uma constante nomeada como uma fórmula de matriz, lembre-se de digitar o sinal de igual. Se não o fizer, o Excel interpreta a matriz como uma seqüência 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 correto. Se você omitir uma vírgula ou ponto e vírgula ou que são colocados no lugar errado, constante de matriz pode não ser criada corretamente ou você poderá ver uma mensagem de aviso.

  • Talvez você tenha selecionado um intervalo de células que não corresponde ao número de elementos na sua constante. Por exemplo, se você selecionar uma coluna de seis células para uso com uma constante de célula de cinco, o valor de erro # n / aparece na célula vazia. Por outro lado, se você selecionar um número muito pequeno de células, 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. Copiar os dados na tabela a seguir e cole-a na planilha, começando pela célula A1.

4:00

o rápido

1

2

3

4

1200

raposa marrom

5

6

7

8

3200

pulou sobre

9

1,0

11

1,2

475

o quantificador lazy

13

1,4

1,5

16

5:00

usuário avançado

2000

6:00

1700

800

2700

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

Dados de exemplo completos

  1. Nomeie a primeira planilha dadose nomear 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. Na sua pasta de trabalho de exemplo, selecione a planilha de Arrays.

  2. Selecione o intervalo de célula C1 pelo 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 links de fórmulas para os valores armazenados nas células E1 a G3 na planilha de dados. A alternativa para essa fórmula de matriz com várias células é colocar uma única fórmula em cada célula da planilha Arrays, 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 de dados, essas alterações aparecerão na planilha Arrays. Lembre-se de que, para alterar os valores na planilha de dados, você precisa seguir 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 de 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 para 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.

Excel substitui os dados de =! E1:G3 a fórmula com a seguinte constante de matriz de matriz:

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

O link foi quebrado entre as planilhas de dados e matrizes e a fórmula matricial 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 de dados, digite 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 caracteres de texto em cada uma das células no intervalo. Em seguida, a função soma adiciona os valores juntos 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 de dados, selecione as células A12 a A14.

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

  2. Insira a seguinte fórmula na barra de fórmulas e pressione Ctrl+Shift+Enter:

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

Os valores de 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 ( pequeno (a1: a10, {1; 2; 3}))

= Médio ( pequeno (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 de dados, selecione as células A12 a A14.

  2. Pressione DELETE para limpar a fórmula existente, mas deixar as células selecionadas.

  3. Insira a seguinte fórmula na barra de fórmulas 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.

Nesse ponto, pode ser útil para saber um pouco sobre a linha e funções indireto . Você pode usar a função de linha 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, em seguida, 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"))) — desde os parênteses internos e trabalhar para fora: A função indireto retorna um conjunto de valores de texto, nesse caso, os valores de 1 a 3. A função lin sucessivamente gera uma matriz de células de três coluna. 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 retornada pela função linha . Os valores de 3200, 2700 e 2000 são retornados à matriz célula três coluna. Se você desejar localizar valores mais, você adicionar um intervalo de células maior para a 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 seqüência mais longa de texto 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 de dados, desmarque a fórmula existente na célula C7, digite a seguinte fórmula na célula e, em seguida, pressione CTRL + SHIFT + ENTER:

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

O valor pulou sobre aparece na célula C7.

Vamos examine a fórmula, a partir de elementos internos e trabalhar para fora. A função LEN retorna o tamanho 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 à seqüência 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 utiliza 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(SE(ÉERROS(Dados),"",Dados))

A fórmula cria uma nova matriz que contém os valores originais menos quaisquer valores de erro. Início das funções internas e trabalhar para fora, o ISERROR 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 falso. Nesse caso, ele retorna cadeias de caracteres vazias ("") para erro de todos os valores porque eles avaliar como verdadeiro e retorna o restante valores do intervalo (dados) porque eles retornam FALSE, que significa que eles não contêm 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(SE(ÉERROS(Dados),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 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 IF , da seguinte forma:

SOMA(SE(ÉERROS(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(ÉERROS(Dados)*1))

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

Somar valores com base em condições

Talvez seja necessário soma valores com base em condições. Por exemplo, a fórmula de matriz soma 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)*(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   Você não pode usar as funções AND e OR em fórmulas de matriz diretamente porque essas funções para retornar um único resultado TRUE ou FALSE e funções de array requerem conjuntos 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 atendam ou ou condição AND.

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

Essa fórmula de matriz compara os valores em dois intervalos de células nomeados MyData e YourData e retorna o número de diferenças entre os dois. Se o conteúdo de dois intervalos forem idêntico, a fórmula retornará 0. Para usar esta fórmula, os intervalos de células devem ser do mesmo tamanho e de 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

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.

Aplicável a: Excel 2010



Essas informações foram úteis?

Sim Não

Como podemos melhorá-lo?

255 caracteres restantes

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!

Recursos de suporte

Alterar idioma