Diretrizes e exemplos de fórmulas de matriz

Diretrizes e exemplos de fórmulas de matriz

Nota:  Queremos fornecer-lhe os conteúdos de ajuda mais recentes o mais rapidamente possível e no seu idioma. Esta página foi traduzida automaticamente e pode conter erros gramaticais ou imprecisões. O nosso objetivo é que estes conteúdos lhe sejam úteis. Pode informar-nos se as informações foram úteis no final desta página? Eis o artigo em inglês para referência.

Para se tornar um utilizador avançado do Excel, tem de saber como utilizar fórmulas de matriz, que podem efetuar cálculos que não consegue efetuar utilizando fórmulas de matriz-de-não. O seguinte artigo baseia-se numa série de colunas de utilizador do Excel Power escritos por Carlos Grilo e adaptada a partir dos capítulos 14 e 15 de Fórmulas do Excel 2002, um livro escrito por João por Walkenbach, MVP do Excel.

Obter mais informações sobre fórmulas de matriz

As fórmulas de matriz são, muitas vezes, chamadas de fórmulas CSE (Ctrl+Shift+Enter) porque prime as teclas Ctrl+Shift+Enter para completar a fórmula em vez de premir ENTER.

Porquê utilizar fórmulas de matriz?

Se tem experiência em utilizar fórmulas no Excel, sabe que é possível efetuar operações razoavelmente sofisticadas. Por exemplo, pode calcular o custo total de um empréstimo ao longo de um número de anos. Pode utilizar fórmulas de matriz para efetuar tarefas complexas, como, por exemplo:

  • Contar o número de carateres contidos num intervalo de células.

  • Somar apenas números que satisfaçam determinadas condições, como, por exemplo, os valores mais baixos de um intervalo ou números que se encontrem entre um limite superior e inferior.

  • Somar cada n-ésimo valor de um intervalo de valores.

Breve introdução às matrizes e fórmulas de matriz

Uma fórmula de matriz é uma fórmula capaz de efetuar vários cálculos num ou mais itens de uma matriz. Pode considerar uma matriz como uma linha de valores, uma coluna de valores ou a combinação de linhas e colunas de valores. As fórmulas de matriz podem devolver vários resultados ou um único resultado. Por exemplo, pode criar uma fórmula de matriz num intervalo de células e utilizá-la para calcular uma coluna ou linha de subtotais. Também pode colocar uma fórmula de matriz numa única célula e, em seguida, calcular uma única quantidade. Uma fórmula de matriz que se encontre em várias células é denominada fórmula com várias células e uma fórmula de matriz que se encontre numa única célula é denominada fórmula de uma célula.

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

Experimente!

Este exercício mostra como utilizar fórmulas de matriz de várias células e de uma célula para calcular um conjunto de números de vendas. O primeiro conjunto de passos utiliza uma fórmula de várias células para calcular um conjunto de subtotais. O segundo conjunto utiliza uma fórmula de uma célula para calcular um total geral.

Fórmula de Matriz com várias células

Este artigo possui um livro incorporado no browser. O livro contém informação de exemplo, mas deverá ter em conta que não é possível criar ou alterar fórmulas de matriz num livro incorporado - para isso é necessário o programa Excel. As respostas podem ser vistas no livro incorporado e um texto explica como funcionam as fórmulas de matriz, mas para observar as fórmulas de matriz, o livro deve ser aberto no Excel.

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

  1. Copie toda a tabela abaixo e cole dentro da célula A1 numa folha de cálculo do Excel em branco.

    Representante de
    Vendas

    Tipo de
    Carro

    Número
    de Vendas

    Preço por
    Unidade

    Total de
    Vendas

    Andrade

    Sedan

    5

    33000

    Coupe

    4

    37000

    Correia

    Sedan

    6

    24000

    Coupe

    8

    21000

    Neves

    Sedan

    3

    29000

    Coupe

    1

    31000

    Martins

    Sedan

    9

    24000

    Coupe

    5

    37000

    Sousa

    Sedan

    6

    33000

    Coupe

    8

    31000

    Fórmula (Total Geral)

    Total Geral

    '=SOMA(C2:C11*D2:D11)

    =SOMA(C2:C11*D2:D11)

  2. Para ver o Total Geral de coupes e sedans de cada vendedor, selecione as células E2:E11, introduza a fórmula =C2:C11*D2:D11 e, em seguida, prima Ctrl+Shift+Enter.

  3. Para ver o Total Geral de todas as vendas, selecione a célula F11, introduza a fórmula =SOMA(C2:C11*D2:D11) e, em seguida, prima Ctrl+Shift+Enter.

Este livro pode ser transferido ao clicar no botão verde do Excel, situado na barra preta na base do livro. Abra o ficheiro no Excel, selecione as células que contêm as fórmulas de matriz e prima as teclas Ctrl+Shift+Enter para que a fórmula funcione.

Ao trabalhar no Excel, certifique-se de que a Folha1 está ativa e, em seguida, selecione as células E2:E11. Prima a tecla F2 e escreva a fórmula =C2:C11*D2:D11 na célula atual, a E2. Prima Enter e verá que a fórmula é aplicada na célula E2 e apresenta o número 165000. Em vez disso, após escrever a fórmula, pressione Ctrl+Shift+Enter em vez de Enter. Os resultados serão apresentados nas células E2:E11. Repare que na barra de fórmulas a fórmula aparece como {=C2:C11*D2:D11}. Isto indica que é uma fórmula de matriz. O processo é demonstrado abaixo.

Ao premir Ctrl+Shift+Enter, o Excel coloca a fórmula entre chavetas ({ }) e introduz uma ocorrência da fórmula em cada célula do intervalo selecionado. Isto acontece de forma muito rápida e, por esta razão, o que vê na coluna E é o total de vendas de cada tipo de carro por cada vendedor. Ao selecionar E2 e, em seguida, E3, E4 e por diante, observará que é apresentada a mesma fórmula: {=C2:C11*D2:D11}.

Os totais na coluna E são calculados por uma fórmula de matriz

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

Na célula F10 do livro, escreva 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 entre C2 e D11) e, em seguida, utiliza a função SOMA para adicionar os totais. O resultado é um total geral de 1.590.000,00 € em vendas. Este exemplo mostra o poder deste tipo de fórmula. Por exemplo, suponhamos que tem 1000 linhas de dados. Pode somar esses dados de forma parcial ou total criando uma fórmula de matriz numa única célula em vez de arrastar a fórmula para baixo pelas 1000 linhas.

Para além disso, tenha em atenção que a fórmula com uma célula (na célula G11) é completamente independente da fórmula com várias células (a fórmula nas células E2 a E11). Esta é outra vantagem subjacente à utilização de fórmulas de matriz:  a flexibilidade. Pode alterar as fórmulas da coluna E ou eliminar essa coluna por completo sem afetar a fórmula na G11.

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

  • Consistência    Se clicar em qualquer uma das células abaixo de E2, verá a mesma fórmula. Essa consistência pode ajudar a garantir uma maior exatidão.

  • Segurança    Não é possível substituir um componente de uma fórmula de matriz com várias células. Por exemplo, clique na célula E3 e prima Delete. Terá de selecionar o intervalo de células completo (E2 a E11) e alterar a fórmula para toda a matriz ou deixar a matriz tal como está. Como medida de segurança adicional, deverá premir Ctrl+Shift+Enter para confirmar a alteração da fórmula.

  • Ficheiros de tamanho mais pequeno    Muitas vezes, é possível utilizar uma única fórmula de matriz em vez de várias fórmulas intermédias. Por exemplo, o livro que utiliza uma fórmula de matriz para calcular os resultados na coluna E. Se tivesse utilizado fórmulas padrão (como por exemplo =C2*D2, C3*D3, C4*D4...), teria utilizado 11 fórmulas diferentes para obter os mesmos resultados.

Sintaxe da fórmula de matriz

No geral, as fórmulas de matriz utilizam uma sintaxe de fórmula padrão. Todas começam com um sinal de igual (=) e é possível utilizar a maioria das funções incorporadas do Excel nas fórmulas de matriz. A principal diferença reside no facto de, ao utilizar uma fórmula de matriz, ter de premir Ctrl+Shift+Enter para introduzir a fórmula. Ao fazê-lo, o Excel coloca a fórmula de matriz entre chavetas  - se escrever as chavetas manualmente, a fórmula será convertida numa cadeia de texto e não funcionará.

As funções de matriz são uma maneira eficiente de construir uma fórmula complexa. A fórmula de matriz =SOMA(C2:C11*D2:D11) é o mesmo que: =SOMA(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Introduzir e alterar fórmulas de matriz

Importante    Prima as teclas Ctrl+Shift+Enter sempre que necessitar de introduzir ou editar uma fórmula de matriz. Esta regra aplica-se tanto a fórmulas com uma célula como a fórmulas de matriz com várias células.

Sempre que trabalhar com fórmulas com várias células, lembre-se:

  • Selecione o intervalo de células que irá conter os resultados antes de introduzir a fórmula. Efetuou este procedimento quando criou a fórmula de matriz 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 numa fórmula de matriz. Para tentar fazê-lo, selecione a célula E3 no livro e prima DELETE. O Excel apresenta uma mensagem a indicar que não é possível alterar parte de uma matriz.

  • É possível mover ou eliminar a totalidade de uma fórmula de matriz, mas não é possível mover ou eliminar parte dessa fórmula. Noutras palavras, para reduzir uma fórmula de matriz, deverá eliminar primeiro a fórmula existente e, em seguida, começar de novo.

  • Para eliminar uma fórmula de matriz, selecione a fórmula completa (por exemplo, =C2:C11*D2:D11), prima Delete e, em seguida, prima Ctrl+Shift+Enter.

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

Expandir uma fórmula de matriz

Por vezes, poderá ser necessário expandir uma fórmula de matriz. O processo não é complicado, mas deverá lembrar-se das regras enumeradas na secção anterior.

Nesta folha de cálculo adicionámos mais algumas linhas de vendas nas linhas 12 a 17. Aqui, queremos atualizar as fórmulas de matriz de modo a que incluam estas linhas adicionais.

Certifique-se de que faz isto no programa de ambiente de trabalho do Excel (após transferir o livro para o computador).

Expandir uma fórmula de matriz

  1. Copie toda esta tabela para a célula A1 de uma folha de cálculo Excel.

    Representante de
    Vendas

    Tipo de
    Carro

    Número
    de Vendas

    Preço por
    Unidade

    Total de
    Vendas

    Andrade

    Sedan

    5

    33000

    165000

    Coupe

    4

    37000

    148000

    Correia

    Sedan

    6

    24000

    144000

    Coupe

    8

    21000

    168000

    Neves

    Sedan

    3

    29000

    87000

    Coupe

    1

    31000

    31000

    Martins

    Sedan

    9

    24000

    216000

    Coupe

    5

    37000

    185000

    Sousa

    Sedan

    6

    33000

    198000

    Coupe

    8

    31000

    248000

    Botas

    Sedan

    2

    27000

    Coupe

    3

    30000

    Gouveia

    Sedan

    4

    22000

    Coupe

    1

    41000

    Cunha

    Sedan

    5

    32000

    Coupe

    3

    36000

    Total Geral

  2. Selecione a célula E18, introduza a fórmula Total Geral = SOMA(C2:C17*D2*D17) na célula A20 e prima Ctrl+Shift+Enter.
    A resposta deverá ser 2 131 000.

  3. Selecione o intervalo de células que contém a fórmula de matriz atual (E2:E11), bem como as células vazias (E12:E17) junto aos novos dados. Por outras palavras, selecione as células E2:E17.

  4. Prima F2 para mudar para o modo de edição.

  5. Na barra de fórmulas, altere C11 para C17, altere D11 para D17 e, em seguida, prima Ctrl+Shift+Enter.
    O Excel atualizará a fórmula nas células E2 a E11 e colocará uma ocorrência da fórmula nas novas células E12 a E17.

  6. Escreva a fórmula de matriz = SOMA(C2:C17*D2*D17) na célula F17 para que abranja as células da linha 2 à linha 17 e prima Ctrl+Shift+Enter para introduzir a fórmula de matriz.
    O novo total geral deverá ser 2 131 000.

Desvantagens de utilizar fórmulas de matriz

As fórmulas de matriz são ótimas, mas também têm algumas desvantagens:

  • Por vezes, poderá esquecer-se de premir Ctrl+Shift+Enter. Pode acontecer até aos utilizadores de Excel mais experientes. Não se esqueça de premir esta combinação de teclas sempre que introduzir ou editar uma fórmula de matriz.

  • Os outros utilizadores do livro poderão não compreender as suas fórmulas. Na prática, as fórmulas de matriz não aparecem documentadas numa folha de cálculo, pelo que se os seus livros necessitarem de ser modificados por outras pessoas, deverá evitar fórmulas de matriz ou certificar-se de que esses utilizadores sabem como alterá-las, caso seja necessário.

  • Dependendo da velocidade de processamento e da memória do computador, as fórmulas de matriz de grandes dimensões poderão diminuir a velocidade dos cálculos.

Início da Página

Obter mais informações sobre constantes de matriz

As constantes de matriz são um componente das fórmulas de matriz. As constantes de matriz são criadas através da introdução de uma lista de itens e, em seguida, da colocação manual da lista entre chavetas ({ }) da seguinte forma:

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

Nesta altura já sabe que tem de premir Ctrl+Shift+Enter ao criar fórmulas de matriz. Uma vez que as constantes de matriz são um componente das fórmulas de matriz, deverá colocar manualmente as constantes entre chavetas escrevendo-as. Em seguida, deverá utilizar Ctrl+Shift+Enter para introduzir toda a fórmula.

Se separar os itens utilizando vírgulas, criará uma matriz horizontal (uma linha). Se separar os itens utilizando pontos e vírgulas, criará uma matriz vertical (uma coluna). Para criar uma matriz bidimensional, deverá delimitar os itens em cada linha utilizando vírgulas e delimitar cada linha utilizando pontos e vírgulas.

Aqui está uma matriz numa única linha: {1,2,3,4}. Aqui está uma matriz numa única coluna: {1;2;3;4}. E aqui está uma matriz de duas linhas e quatro colunas: {1,2,3,4;5,6,7,8}. Na matriz de duas linhas, a primeira linha é 1, 2, 3 e 4 e a segunda linha é 5, 6, 7 e 8. Um único ponto e vírgula separa as duas linhas, entre 4 e 5.

À semelhança do que acontece com as fórmulas de matriz, pode utilizar as constantes de matriz com a maioria das funções incorporada oferecida pelo Excel. As secções que se seguem explicam como criar cada tipo de constante e como utilizar essas constantes com funções do Excel.

Início da Página

Criar constantes unidimensionais e bidimensionais

O procedimento que se segue dar-lhe-á alguma prática na criação de constantes horizontais, verticais e bidimensionais.

Criar uma constante horizontal

  1. Utilize o livro dos exemplos anteriores ou crie um novo livro.

  2. Selecione as células A1 a E1.

  3. Na barra de fórmulas, introduza a seguinte fórmula e, em seguida, prima Ctrl+Shift+Enter:

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

    Nesse caso, deverá escrever as chavetas inicial e final ({ }).

    É apresentado o seguinte resultado.

    Constante de matriz horizontal numa fórmula

Criar uma constante vertical

  1. No livro, selecione uma coluna com cinco células.

  2. Introduza a seguinte fórmula na barra de fórmulas e, em seguida, prima Ctrl+Shift+Enter:

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

    É apresentado o seguinte resultado.

    Constante de matriz vertical numa fórmula de matriz

Criar uma constante bidimensional

  1. No livro, selecione um bloco de células com uma largura de quatro colunas e uma altura de três linhas.

  2. Na barra de fórmulas, introduza a seguinte fórmula e, em seguida, prima Ctrl+Shift+Enter:

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

    Verá o seguinte resultado:

    Constante de matriz bidimensional numa fórmula de matriz

Utilizar constantes em fórmulas

Eis um exemplo simples que utiliza constantes:

  1. No livro de exemplo, crie uma nova folha de cálculo.

  2. Na célula A1, escreva 3 e, em seguida, escreva 4 em B1, 5 em C1, 6 em D1 e 7 em E1.

  3. Na célula A3, escreva a seguinte fórmula e, em seguida, prima Ctrl+Shift+Enter:

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

    Note que o Excel coloca outro conjunto de chavetas à volta da constante, dado que a introduziu como uma fórmula de matriz.

    Fórmula de matriz com constante de matriz

    O valor 85 é apresentado na célula A3.

A secção seguinte explica como funciona a fórmula.

Sintaxe da constante de matriz

A fórmula que acabou de utilizar 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: {1,2,3,4,5}. Lembre-se que o Excel não coloca constantes de matriz entre chavetas - terá de as escrever manualmente. Para além disso, lembre-se que após adicionar uma constante a uma fórmula de matriz, pressiona Ctrl+Shift+Enter para introduzir a fórmula.

Uma vez que o Excel efetua operações sobre as expressões entre parênteses em primeiro lugar, os dois elementos seguintes a entrar em ação são os valores armazenados no livro (A1:E1) e o operador. Nesse ponto, a fórmula multiplica os valores da matriz armazenada pelos valores correspondentes da constante. É o equivalente a:

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

Por fim, a função de SOMA adiciona os valores e a soma85 aparece na célula A3.

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

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

Para efetuar esta operação, copie a função, selecione uma célula em branco no livro, cole a fórmula na barra de fórmulas e, em seguida, prima Ctrl+Shift+Enter. Verá o mesmo resultado que viu anteriormente no exercício que utilizava a fórmula de matriz:

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

Elementos que é possível utilizar em constantes

Constantes de matriz podem conter números, textos, valores lógicos (como VERDADEIRO e FALSO) e valores de erro (como #N/A). Pode utilizar números de formato inteiro, decimal e científico. Se incluir texto, deverá colocar o texto entre aspas (").

Constantes de matriz não podem conter matrizes, fórmulas ou funções adicionais. Por outras palavras, só podem conter texto ou números separados por vírgulas ou pontos e vírgulas. O Excel mostra uma mensagem de aviso ao colocar uma fórmula como {1,2,A1:D4} ou {1,2,SOMA(Q2:Z8)}. Para além disso, valores numéricos não podem conter sinais, cifrões, vírgulas ou parênteses.

Atribuir nomes a constantes de matriz

Possivelmente a melhor maneira de usar constantes de matriz é atribuir-lhes nomes. Constantes com nomes atribuídos podem tornar-se muito mais fáceis de usar e podem omitir alguma da complexidade das fórmulas de matriz de outros. Para atribuir um nome a uma constante de matriz e usá-lo numa fórmula, faça o seguinte:

  1. No separador Fórmulas, no grupo Nomes Definidos, clique em Definir Nome.
    É apresentada a caixa de diálogo Definir Nome.

  2. Na caixa Nome, escreva Trimestre1.

  3. Na caixa Refere-se a, introduza a seguinte constante (não se esqueça de escrever as chavetas manualmente):

    ={"janeiro","fevereiro","março"}

    Agora, o conteúdo da caixa de diálogo tem o seguinte aspeto:

    Caixa de diálogo Editar Nome com fórmula

  4. Clique OK e selecione uma linha de três células brancas.

  5. Escreva a seguinte fórmula e, em seguida, prima Ctrl+Shift+Enter.

    =Trimestre1

    É apresentado o seguinte resultado.

    Matriz com nome introduzida como fórmula

Quando utilizar uma constante com nome como fórmula de matriz, não se esqueça de introduzir o sinal de igual. Se não o fizer, o Excel interpretará a matriz como sendo uma cadeia de texto e a fórmula não funcionará. Por fim, tenha em atenção que é possível utilizar combinações de texto e números.

Resolução de problemas de constantes de matriz

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

  • Alguns elementos poderão não estar separados pelo caráter adequado. Se omitir uma vírgula ou um ponto e vírgula, ou se os colocar no local errado, a constante de matriz poderá não ser criada corretamente ou poderá ser apresentada uma mensagem de aviso.

  • Poderá ter selecionado um intervalo de células que não corresponda ao número de elementos da constante. Por exemplo, se selecionar uma coluna de seis células para utilizar com uma constante de cinco células, será apresentado o valor de erro #N/A na célula vazia. Por outro lado, se selecionar células a menos, o Excel omitirá os valores que não tenham uma célula correspondente.

Constantes de matriz em ação

Os exemplos que se seguem demonstram algumas formas de utilização de constantes de matriz em fórmulas de matriz. Alguns dos exemplos utilizam a função TRANSPOR para converter linhas em colunas e vice-versa.

Multiplicar cada item numa matriz

  1. Crie uma nova folha de cálculo e, em seguida, selecione um bloco de células vazias com uma largura de quatro colunas e uma altura de três linhas.

  2. Escreva a seguinte fórmula e, em seguida, prima Ctrl+Shift+Enter:

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

Elevar os itens de uma matriz ao quadrado

  1. Selecione um bloco de células vazias com uma largura de quatro colunas e uma altura de três linhas.

  2. Escreva a seguinte fórmula de matriz e, em seguida, prima 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}

    Em alternativa, introduza esta fórmula de matriz, que utiliza o operador de acento circunflexo (^):

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

Transpor uma linha unidimensional

  1. Selecione uma coluna com cinco células em branco.

  2. Escreva a seguinte fórmula e, em seguida, prima Ctrl+Shift+Enter:

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

Embora tenha introduzido uma constante de matriz horizontal, a função TRANSPOR converte a constante de matriz numa coluna.

Transpor uma coluna unidimensional

  1. Selecione uma linha com cinco células em branco.

  2. Escreva a seguinte fórmula e, em seguida, prima Ctrl+Shift+Enter:

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

Embora tenha introduzido uma constante de matriz vertical, a função TRANSPOR converte a constante numa linha.

Transpor uma constante bidimensional

  1. Selecione um bloco de células com uma largura de três colunas e uma altura de quatro linhas.

  2. Introduza a seguinte constante e, em seguida, prima Ctrl+Shift+Enter:

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

A função TRANSPOR converte cada linha numa série de colunas.

Início da Página

Colocar fórmulas de matriz básicas em funcionamento

Esta secção oferece exemplos de fórmulas de matriz básicas.

Criar matrizes e constantes de matriz a partir de valores existentes

O exemplo que se segue explica como utilizar fórmulas de matriz para criar hiperligações entre intervalos de células em diferentes folhas de cálculo. O exemplo mostra também como criar uma constante de matriz a partir do mesmo conjunto de valores.

Criar uma matriz a partir de valores existentes

  1. Numa folha de cálculo no Excel, selecione as células C8:E10 e introduza esta fórmula:

    ={10,20,30;40,50,60;70,80,90}

    Certifique-se de que introduz { (chaveta de abertura) antes de introduzir 10 e } (chaveta de fecho) depois de introduzir 90, porque está a criar uma matriz de números.

  2. Prima Ctrl+Shift+Enter, que introduz esta matriz de números no intervalo de células C8:E10 utilizando uma fórmula de matriz.
    Na sua folha de cálculo, o intervalo entre C8 e E10 têm o seguinte aspeto:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Selecione o intervalo de células entre C1 e E3.

  4. Introduza a seguinte fórmula na barra de fórmulas, em seguida, prima Ctrl+Shift+Enter:

    =C8:E10

    Uma matriz de células de 3x3 aparece nas células C1 a E3 com os mesmos valores das células C8 a E10.

Criar uma constante de matriz a partir de valores existentes

  1. Com as células C1:C3 selecionadas, prima F2 para alterar para modo de edição.
    A fórmula de matriz ainda deverá ser = C8:E10.

  2. Prima F9 para converter as referências de células para valores. O Excel converte os valores para uma constante de matriz. A fórmula deverá agora ser ={10,20,30;40,50,60;70,80,90}, como as células C8:E10.

  3. Prima Ctrl+Shift+Enter para introduzir a constante de matriz como fórmula de matriz.

Contar carateres num intervalo de células

O exemplo que se segue mostra como contar o número de carateres, incluindo espaços, de um intervalo de células.

  1. Copie esta tabela toda e cole-a numa folha de cálculo na célula A1.

  2. Selecione a célula A9 e, em seguida, prima Ctrl+Shift+Enter para ver o número total de carateres nas células A2:A6 (66).

  3. Selecione a célula A12 e, em seguida, prima Ctrl+Shift+Enter para ver os conteúdos da célula mais longa de todas as células A2:A6 (célula A3).

Dados

Isto é um

conjunto de células

unidas

para formar uma

única frase.

Carateres totais em A2:A6

=SOMA(NÚM.CARAT(A2:A6))

Conteúdos da célula mais longa (A3)

=ÍNDICE(A2:A6,CORRESP(MÁXIMO(NÚM.CARAT(A2:A6)),NÚM.CARAT(A2:A6),0),1)

A seguinte fórmula usada na célula A9 conta o número total de carateres (66) nas células A2 a A6.

=SOMA(NÚM.CARAT(A2:A6))

Neste caso, a função NÚM.CARAT devolve o tamanho de cada cadeia de texto de cada uma das células do intervalo. A função SOMA soma os valores e apresenta o resultado (66) na célula que contém a fórmula, a A9.

Encontrar os n valores menores de um intervalo

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

  1. Selecione as células entre A16 e A18.
    Este conjunto de células irá conter os resultados devolvidos pela fórmula de matriz.

  2. Introduza a seguinte fórmula na barra de fórmulas e, em seguida, prima Ctrl+Shift+Enter:

    =MENOR(A5:A14,{1;2;3})

Os valores 400, 475 e 500 são apresentados nas células A16 a A18, respetivamente.

Esta fórmula utiliza uma constante de matriz para avaliar a função MENOR três vezes e devolver o membro menor (1), o segundo membro menor (2) e o terceiro membro menor (3) da matriz contida nas células A1:A10. Para encontrar mais valores, adicione mais argumentos à constante e um número equivalente de células de resultado ao intervalo A12:A14. Também pode utilizar funções adicionais com esta fórmula, como, por exemplo, SOMA ou MÉDIA. Por exemplo:

=SOMA(MENOR(A 5 :A1 4 ,{1;2;3}))

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

Encontrar os n valores maiores de um intervalo

Para encontrar os valores maiores de um intervalo, pode substituir a função MENOR pela função MAIOR. Para além disso, o exemplo que se segue utiliza as funções LIN e INDIRETO.

  1. Selecione as células A1 a A3.

  2. Na barra de fórmulas, introduza esta fórmula e, em seguida, prima Ctrl+Shift+Enter:

    =MAIOR(A5:A14,LIN(INDIRETO("1:3")))

Os valores 3200, 2700 e 2000 são apresentados nas células A1 a A3, respetivamente.

Neste momento, poderá ser útil conhecer um pouco das funções LIN e INDIRETO. Pode utilizar a função LIN para criar uma matriz de números inteiros consecutivos. Por exemplo, selecione uma coluna vazia de 10 células no livro de exercícios práticos, introduza esta fórmula de matriz nas células A5:A14 e, em seguida, prima Ctrl+Shift+Enter:

=LIN(1:10)

A fórmula cria uma coluna de 10 números inteiros consecutivos. Para ver um potencial problema, insira uma linha acima do intervalo que contém a fórmula de matriz (ou seja, acima da linha 1). O Excel ajusta as referências de linhas e a fórmula gera números inteiros de 2 a 11. Para corrigir esse problema, deverá adicionar a função INDIRETO à fórmula:

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

A função INDIRETO utiliza cadeias de texto como argumentos (motivo pelo qual o intervalo 1:10 está entre aspas duplas). O Excel não ajusta valores de texto quando insere linhas nem move de qualquer outra forma a fórmula de matriz. Como resultado, a função LIN gera sempre a matriz de números inteiros pretendida.

Examinemos agora a fórmula utilizada anteriormente  - =MAIOR(A5:A14,LIN(INDIRETO("1:3")))  - começando pelos parênteses internos e trabalhando em direção ao exterior. A função INDIRETO devolve um conjunto de valores de texto, neste caso, os valores 1 a 3. A função LIN gera, por seu turno, uma matriz de coluna com três células. A função MAIOR utiliza os valores no intervalo de células A5:A14 e é avaliada três vezes: uma vez por cada referência devolvida pela função LIN. Os valores 3200, 2700 e 2000 são devolvidos à matriz de coluna com três células. Se pretender encontrar mais valores, deverá adicionar um intervalo de células maior à função INDIRETO.

Por fim, pode utilizar esta fórmula com outras funções, como, por exemplo, SOMA e MÉDIA.

Encontrar a cadeia de texto maior num intervalo de células

Esta fórmula apenas funciona quando um intervalo de dados contém uma única coluna de células. Na Folha3, introduza a seguinte fórmula nas célula A16 e, em seguida, prima Ctrl+Shift+Enter:

=ÍNDICE(A6:A9,CORRESP(MÁXIMO(NÚM.CARAT(A6:A9)),NÚM.CARAT(A6:A9),0),1)

O texto "conjunto de células que" é apresentado na célula A16.

Examinemos agora a fórmula, começando pelos elementos internos e trabalhando em direção ao exterior. A função NÚM.CARAT devolve o comprimento de cada um dos itens do intervalo de células A6:A9. A função MÁXIMO calcula o valor maior entre esses itens, o que corresponde à cadeia de texto maior que se encontra na célula A7.

É neste ponto que a questão fica algo complexa. A função CORRESP calcula o deslocamento (a posição relativa) da célula que contém a maior cadeia de texto. Para o fazer, requer três argumentos: um valor de pesquisa, uma matriz de pesquisa e um tipo de correspondência. A função CORRESP pesquisa na matriz de pesquisa o valor de pesquisa especificado. Neste caso, o valor de pesquisa é a maior cadeia de texto:

(MÁXIMO(NÚM.CARAT( A6 : A9 ))

e essa cadeia encontra-se na seguinte matriz:

NÚM.CARAT( A6:A9 )

O argumento do tipo de correspondência é 0. O tipo de correspondência pode ser constituído por um valor de 1, 0 ou -1. Se especificar 1, CORRESP devolverá o maior valor que seja menor ou igual ao valor de pesquisa. Se especificar 0, CORRESP devolverá o primeiro valor que seja exatamente igual ao valor de pesquisa. Se especificar -1, CORRESP encontrará o menor valor que seja maior ou igual ao valor de pesquisa especificado. Se omitir um tipo de correspondência, o Excel assumirá o valor 1.

Por fim, a função ÍNDICE utiliza os seguintes argumentos: uma matriz e uma linha e número de coluna dentro dessa matriz. O intervalo de células A6:A9 indica a matriz, a função CORRESP indica o endereço da célula e o argumento final (1) especifica que o valor provém da primeira coluna da matriz.

Início da Página

Colocar fórmulas de matriz avançadas em funcionamento

Esta secção oferece 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 tenta somar um intervalo que contém um valor de erro, como, por exemplo, #N/D. Este exemplo mostra como somar os valores de um intervalo com o nome Dados que contém erros.

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

A fórmula cria uma nova matriz que contém os valores originais menos os valores com erros. Começando pelas funções internas e trabalhando em direção ao exterior, a função É.ERRO procura erros no intervalo de células (Dados). A função SE devolve um valor específico se uma condição especificada devolver o valor VERDADEIRO e outro valor diferente se devolver FALSO. Neste caso, devolverá cadeias vazias ("") para todos os valores de erro, uma vez que devolvem VERDADEIRO e devolve os valores restantes do intervalo (Dados), pois devolvem FALSO, o que significa que não contêm valores de erro. A função SOMA calcula em seguida o total da matriz filtrada.

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

Este exemplo é semelhante à fórmula anterior, mas devolve o número de valores de erro de um intervalo com o nome Dados em vez de os filtrar:

=SOMA(SE(É.ERRO(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 as células que não contêm erros. É possível simplificar a fórmula e alcançar o mesmo resultado removendo o terceiro argumento da função SE da seguinte forma:

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

Se não especificar o argumento, a função SE devolverá FALSO se uma célula não contiver um valor de erro. É possível simplificar ainda mais a fórmula:

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

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

Somar valores baseados em condições

Poderá necessitar de somar valores baseados em condições. Por exemplo, esta fórmula de matriz soma apenas os números inteiros positivos de um intervalo com o nome Vendas:

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

A função SE cria uma matriz de valores positivos e valores falsos. A função SOMA ignora essencialmente os valores falsos, pois 0+0=0. O intervalo de células utilizado nesta fórmula pode ser constituído por qualquer número de linhas e colunas.

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

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

Tenha em atenção que esta fórmula devolverá um erro se o intervalo contiver uma ou mais células não numéricas.

Também pode criar fórmulas de matriz que utilizem um tipo de condição OU. Por exemplo, pode somar valores que sejam inferiores a 5 e superiores a 15:

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

A função SE encontra todos os valores inferiores a 5 e superiores a 15 e, em seguida, transmite esses valores para a função SOMA.

Não pode utilizar as funções E e OU diretamente em fórmulas de matriz, pois essas funções devolvem um único resultado (VERDADEIRO ou FALSO) e as funções de matriz requerem matrizes de resultados. Pode contornar o problema utilizando a lógica apresentada na fórmula anterior. Por outras palavras, são efetuadas operações matemáticas, como por exemplo adições ou multiplicações, em valores que satisfaçam a condição OU ou E.

Calcular uma média que exclua zeros

Este exemplo mostra como remover zeros de um intervalo quando necessitar de calcular a média dos valores desse intervalo. A fórmula utiliza um intervalo de dados com o nome Vendas:

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

A função SE cria uma matriz de valores que não sejam iguais a 0 e, em seguida, transmite 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 com os nomes OsMeusDados e OsSeusDados e devolve o número de diferenças entre os dois. Se os conteúdos dos dois intervalos forem idênticos, a fórmula devolverá 0. Para utilizar esta fórmula, os intervalos de células deverão ter o mesmo tamanho e pertencer à mesma dimensão (por exemplo, se OsMeusDados for um intervalo de 3 linhas por 5 colunas, OsSeusDados deve também ter 3 linhas por 5 colunas.

=SOMA(SE( OsMeusDados =OsSeusDados,0,1))

A fórmula cria uma nova matriz com o mesmo tamanho dos intervalos que está a comparar. A função SE preenche a matriz com o valor 0 e o valor 1 (0 para incompatibilidades e 1 para células idênticas). A função SOMA devolve em seguida a soma dos valores na matriz.

É possível simplificar a fórmula do seguinte modo:

=SOMA(1*( OsMeusDados <> OsSeusDados ))

À semelhança da fórmula que conta valores de erro de um intervalo, esta fórmula funciona porque VERDADEIRO*1=1 e FALSO*1=0.

Encontrar a localização do valor máximo de um intervalo

Esta fórmula de matriz devolve o número de linha do valor máximo de um intervalo com uma coluna denominado Dados:

=MÍNIMO(SE(Dados=MÁXIMO(Dados),LIN(Dados),""))

A função SE cria uma nova matriz que corresponde ao intervalo denominado Dados. Se uma célula correspondente contiver o valor máximo do intervalo, a matriz conterá o número de linha. Caso contrário, a matriz conterá uma cadeia vazia (""). A função MÍNIMO utiliza a nova matriz como segundo argumento e devolve o valor mínimo, o qual corresponde ao número de linha do valor máximo de Dados. Se o intervalo denominado Dados contiver valores máximos idênticos, a fórmula devolverá a linha do primeiro valor.

Se pretender que seja devolvido o endereço de célula real de um valor máximo, utilize esta fórmula:

=ENDEREÇO(MÍNIMO(SE(Dados=MÁXIMO(Dados),LIN(Dados),"")),COL(Dados))

Início da Página

Precisa de mais ajuda?

Pode sempre perguntar a um especialista na Comunidade Tecnológica do Excel, obter suporte na Comunidade de Respostas ou sugerir uma nova funcionalidade ou melhoria no User Voice do Excel.

Consulte Também

Descrição geral das fórmulas

Aumente os seus conhecimentos do Office
Explore as formações
Seja o primeiro a obter novas funcionalidades
Adira ao Office Insider

As informações foram úteis?

Obrigado pelos seus comentários!

Obrigado pelo seu feedback! Parece que poderá ser benéfico reencaminhá-lo para um dos nossos agentes de suporte do Office.

×