Diretrizes e exemplos de fórmulas de matriz

Diretrizes e exemplos de fórmulas de matriz

Uma fórmula de matriz é uma fórmula que pode realizar cálculos múltiplos em um ou mais itens numa matriz. Pode pensar numa matriz como uma linha ou coluna de valores, ou uma combinação de linhas e colunas de valores. As fórmulas de matriz podem devolver múltiplos resultados, ou um único resultado.

Começando com a atualização de setembro de 2018 para o Microsoft 365, qualquer fórmula que possa devolver múltiplos resultados irá automaticamente destitá-los para baixo ou em todas as células vizinhas. Esta mudança de comportamento é também acompanhada por várias novas funções dinâmicas de matriz. As fórmulas dinâmicas de matriz, quer estejam a utilizar funções existentes ou as funções dinâmicas da matriz, só precisam de ser incorporadas numa única célula, e depois confirmadas pressionando enter. Anteriormente, as fórmulas de matriz antigas requerem primeiro a seleção de toda a gama de saída, confirmando depois a fórmula com Ctrl+Shift+Enter. São comumente referidas como fórmulas de CSE.

Pode utilizar fórmulas de matriz para executar tarefas complexas, tais como:

  • Crie rapidamente conjuntos de dados de amostras.

  • Conte o número de caracteres contidos numa gama de células.

  • Soma apenas números que atendam a determinadas condições, tais como os valores mais baixos de uma gama, ou números que caem entre um limite superior e inferior.

  • Soma cada valor de Nº numa gama de valores.

Os exemplos que se seguem mostram como criar fórmulas de matriz multicelular e unicelular. Sempre que possível, incluímos exemplos com algumas das funções dinâmicas da matriz, bem como as fórmulas de matriz existentes entraram como matrizes dinâmicas e antigas.

Transferir os nossos exemplos

Descarregue um livro de exemplo com todos os exemplos de fórmula de matriz neste artigo.

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

    Função de matriz multicelular na célula H10 =F10:F19*G10:G19 para calcular o número de carros vendidos por preço unitário

  • Aqui estamos a calcular a Venda Total de coupes e sedans para cada vendedor, entrando =F10:F19*G10:G19 na célula H10.

    Quando premir Enter,verá os resultados a escorrer para as células H10:H19. Note que a gama de derrames é realçada com uma borda quando selecionar qualquer célula dentro do intervalo de derrame. Também pode notar que as fórmulas nas células H10:H19 estão cinzentas. Só estão lá para referência, por isso, se quiseres ajustar a fórmula, terás de selecionar a célula H10, onde vive a fórmula principal.

  • Fórmula de matriz unicelular

    Fórmula de matriz unicelular para calcular um grande total com =SUM (F10:F19*G10:G19)

    Na célula H20 do livro de exemplo, escreva ou copie e cole =SUM (F10:F19*G10:G19)e, em seguida, prima Enter.

    Neste caso, o Excel multiplica os valores na matriz (a gama celular F10 a G19), e depois utiliza a função SUM para adicionar os totais em conjunto. 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. Além disso, note que a fórmula unicelular na célula H20 é completamente independente da fórmula multicelular (a fórmula nas células H10 a H19). Esta é outra vantagem subjacente à utilização de fórmulas de matriz:  a flexibilidade. Pode alterar as outras fórmulas na coluna H sem afetar a fórmula em H20. Também pode ser uma boa prática ter totais independentes como este, uma vez que ajuda a validar a precisão dos seus resultados.

  • As fórmulas dinâmicas de matriz também oferecem estas vantagens:

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

    • Segurança    Não se pode substituir um componente de uma fórmula de matriz multi-células. Por exemplo, clique na célula H11 e prima Delete. O Excel não vai alterar a saída da matriz. Para o alterar, é necessário selecionar a célula superior esquerda na matriz ou a célula H10.

    • 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 exemplo de vendas de automóveis usa uma fórmula de matriz para calcular os resultados na coluna E. Se tivesse usado fórmulas padrão como =F10*G10, F11*G11, F12*G12, etc., teria usado 11 fórmulas diferentes para calcular os mesmos resultados. Não é nada de mais, mas e se tivesses milhares de filas para o total? Então pode fazer uma grande diferença.

    • Eficiência    Funções de matriz podem ser uma forma eficiente de construir fórmulas complexas. A fórmula de matriz =SUM (F10:F19*G10:G19) é a mesma que esta: =SUM (F10*G10,F11*G11,F12*G12,,,,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Derramamento    As fórmulas dinâmicas de matriz derramar-se-ão automaticamente na gama de saída. Se os seus dados de origem estiverem numa tabela Excel, então as suas fórmulas dinâmicas de matriz serão automaticamente redimensionadas à medida que adiciona ou remove dados.

    • #SPILL! erro    Arrays dinâmicos introduziram o erro #SPILL!, o que indica que o intervalo de derrame pretendido está bloqueado por alguma razão. Quando resolver o bloqueio, a fórmula derramará automaticamente.

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} ou ={"janeiro", "fevereiro","março"}

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, delimita os itens em cada linha com vírgulas e delimita cada linha com pontos e vírgulas.

O procedimento que se segue dar-lhe-á alguma prática na criação de constantes horizontais, verticais e bidimensionais. Mostraremos exemplos usando a função SEQUENCE para gerar automaticamente constantes de matriz, bem como constantes de matriz inseridas manualmente.

  • Criar uma constante horizontal

    Utilize o livro dos exemplos anteriores ou crie um novo livro. Selecione qualquer célula vazia e introduza =SEQUÊNCIA(1,5). A função SEQUÊNCIA constrói uma matriz de 1 linha por 5 colunas da mesma forma que ={1,2,3,4,5}. É apresentado o seguinte resultado:

    Crie uma constante de matriz horizontal com =SEQUÊNCIA(1,5) ou ={1,2,3,4,5}

  • Criar uma constante vertical

    Selecione qualquer célula em branco com espaço por baixo e introduza =SEQUÊNCIA(5), ou ={1;2;3;4;5}. É apresentado o seguinte resultado:

    Criar uma constante de matriz vertical com =SEQUÊNCIA(5), ou ={1;2;3;4;5}

  • Criar uma constante bidimensional

    Selecione qualquer célula em branco com espaço à direita e por baixo dela, e introduza =SEQUÊNCIA(3,4). Verá o seguinte resultado:

    Criar uma constante de 3 linhas por 4 colunas com =SEQUÊNCIA(3,4)

    Também pode entrar: ou ={1,2,3,4;5,6,7,8;9,10,11,12}, mas vai querer prestar atenção ao local onde coloca semi-cólons versus vírgulas.

    Como pode ver, a opção SEQUENCE oferece vantagens significativas ao entrar manualmente nos valores constantes da sua matriz. Em primeiro lugar, poupa-lhe tempo, mas também pode ajudar a reduzir os erros da entrada manual. Também é mais fácil de ler, especialmente porque os semi-cólons podem ser difíceis de distinguir dos separadores de vírgula.

Aqui está um exemplo que usa constantes de matriz como parte de uma fórmula maior. No livro de amostras, vá ao Constant numa folha de cálculo de fórmula, ou crie uma nova folha de cálculo.

Na célula D9, entramos =SEQUÊNCIA(1,5,3,1), mas também pode entrar 3, 4, 5, 6 e 7 nas células A9:H9. Não há nada de especial na seleção de números em particular, escolhemos algo que não seja 1-5 para diferenciação.

Na célula E11, introduza =SUM(D9:H9*SEQUENCE(1,5)), ou =SUM (D9:H9*{1,2,3,4,5}). As fórmulas voltam 85.

Use constantes de matriz em fórmulas. Neste exemplo, usamos =SUM(D9:H(*SEQUÊNCIA(1,5))

A função SEQUÊNCIA constrói o equivalente à constante da matriz {1,2,3,4,5}. Como o Excel realiza operações em expressões fechadas em parênteses primeiro, os dois seguintes elementos que entram em jogo são os valores celulares em D9:H9, e o operador de multiplicação (*). Nesse ponto, a fórmula multiplica os valores da matriz armazenada pelos valores correspondentes da constante. É o equivalente a:

=SUM (D9*1,E9*2,F9*3,G9*4,H9*5), ou =SUM (3*1,4*2,5*3,6*4,7*5)

Finalmente, a função SUM adiciona os valores e devolve 85.

Para evitar a utilização da matriz armazenada e manter a operação inteiramente na memória, pode substituí-la por outra constante de matriz:

=SOMA (SEQUÊNCIA(1,5,3,1)*SEQUÊNCIA(1,5)), ou =SUM ({3,4,5,6,7}*{1,2,3,4,5})

Elementos que pode usar em constantes de matriz

  • As constantes de matriz podem conter números, texto, valores lógicos (como TRUE e FALSE), e valores de erro como #N/A. Pode usar números em formatos inteiros, decimais e científicos. Se incluir texto, tem de rodeá-lo com aspas ("texto").

  • 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.

Uma das melhores formas de usar constantes de matriz é nomeá-las. 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:

Vá a Fórmulas > Nomes Definidos > Definir Nome. Na caixa de nomes, tipo Quarter1. Na caixa Refere-se a, introduza a seguinte constante (não se esqueça de escrever as chavetas manualmente):

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

A caixa de diálogo deve agora ser assim:

Adicione uma constante de matriz nomeada de Fórmulas > Nomes Definidos > Gestor de Nomes > Novo

Clique OK,depois selecione qualquer linha com três células em branco e introduza =Quarter1.

É apresentado o seguinte resultado:

Use uma constante de matriz nomeada numa fórmula, como =Quarter1, onde o Quarter1 foi definido como ={"janeiro", "fevereiro", "março"}

Se pretender que os resultados se derramem verticalmente em vez de horizontalmente, pode utilizar =TRANSPOSE(Quarto1).

Se quiser apresentar uma lista de 12 meses, como pode utilizar na construção de uma demonstração financeira, pode basear uma fora do ano em curso com a função SEQUENCE. A coisa mais nítida desta função é que, apesar de apenas o mês estar a ser exibido, há uma data válida por trás dela que pode suster noutros cálculos. Você encontrará estes exemplos na constante de conjunto de matriz nomeado e folhas de dados de amostrarápida rápida no livro de exemplo.

=TEXTO(DATA(ANO(HOJE),SEQUÊNCIA (1,12),1"mmm")

Utilize uma combinação das funções de Texto, DATA, ANO, HOJE e SEQUÊNCIA para construir uma lista dinâmica de 12 meses

Isto utiliza a função DATE para criar uma data baseada no ano em curso, a SEQUENCE cria uma constante de matriz de 1 a 12 de janeiro a dezembro, então a função TEXT converte o formato de exibição para "mmm" (jan, Feb, Mar, etc.). Se quisesse exibir o nome do mês inteiro, como janeiro, usaria "mmmm".

Quando utilizar uma constante de nome como fórmula de matriz, lembre-se de introduzir o sinal igual, como em =Quarter1, e não apenas Quarter1. 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 mente que pode utilizar combinações de funções, texto e números. Tudo depende de quão criativo quer ser.

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 TRANSPOSE para converter linhas em colunas e vice-versa.

  • Múltiplos cada item em uma matriz

    Insira =SEQUÊNCIA(1,12)*2, ou ={1,2,3,4;5,6,7,8;9,10,11,12}*2

    Também pode dividir com (/), adicionar com (+), e subtrair com. .

  • Elevar os itens de uma matriz ao quadrado

    Introduza =SEQUÊNCIA(1,12)^2, ou ={1,2,3,4;5,6,7,8;9,10,11,12}^2

  • Encontre a raiz quadrada de itens quadrados em uma matriz

    Inserir =SQRT(SEQUÊNCIA(1,12)^2), ou =SQRT ({1,2,3,4;5,6,7,8;9,10,11,12}^2)

  • Transpor uma linha unidimensional

    Insira =TRANSPOSE(SEQUÊNCIA(1,5)), ou =TRANSPOSE({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

    Insira =TRANSPOSE(SEQUÊNCIA(5,1)), ou =TRANSPOSE({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

    Insira =TRANSPOSE(SEQUÊNCIA(3,4)), ou =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

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

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

  • Criar uma matriz a partir de valores existentes

    O exemplo que se segue explica como usar fórmulas de matriz para criar uma nova matriz a partir de uma matriz existente.

    Insira =SEQUÊNCIA(3,6,10,10), ou ={10,20,30,40,50,60;70,80,90,100,110,120;130,140,150,160,170.180}

    Certifique-se de escrever { (aparelho de abertura) antes de escrever 10 e } (cinta de fecho) depois de escrever 180, porque está a criar uma série de números.

    Em seguida, introduza =D9#, ou =D9:I11 numa cela em branco. Uma matriz de 3 x 6 células aparece com os mesmos valores que se vê em D9:D11. O sinal # chama-se operador de gama derramado,e é a forma do Excel referenciar toda a gama de matrizes em vez de ter que digitá-lo.

    Utilize o operador de gama derramado (#) para fazer referência a uma matriz existente

  • Criar uma constante de matriz a partir de valores existentes

    Pode pegar nos resultados de uma fórmula de matriz derramada e convertê-lo em partes componentes. Selecione a célula D9 e, em seguida, prima F2 para mudar para o modo de edição. Em seguida, pressione F9 para converter as referências celulares em valores, que o Excel converte em uma constante de matriz. Quando premir Enter, a fórmula, =D9#, deve agora ser ={10,20,30;40,50,60;70,80,90}.

  • Contar carateres num intervalo de células

    O exemplo que se segue mostra como contar o número de caracteres numa gama de células. Isto inclui espaços.

    Conte o número total de caracteres numa gama, e outras matrizes para trabalhar com cordas de texto

    =SOMA(LEN(C9:C13))

    Neste caso, a função LEN devolve o comprimento de cada cadeia de texto em cada uma das células da gama. A função SUM adiciona então esses valores e exibe o resultado (66). Se quisesse saquear um número médio de caracteres, poderia usar:

    =MÉDIA(LEN(C9:C13))

  • Conteúdo da célula mais comprida na gama C9:C13

    =INDEX(C9:C13,MATCH(MAX(C9:C13)),LEN(C9:C13),0,1)

    Esta fórmula apenas funciona quando um intervalo de dados contém uma única coluna de células.

    Examinemos agora a fórmula, começando pelos elementos internos e trabalhando em direção ao exterior. A função LEN devolve o comprimento de cada um dos itens da gama celular D2:D6. A função MAX calcula o maior valor entre esses itens, que corresponde à corda de texto mais comprida, que está na célula D3.

    É neste ponto que a questão fica algo complexa. A função MATCH calcula a contrapartida (a posição relativa) da célula que contém a corda de texto mais longa. 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:

    MAX (LEN(C9:C13)

    e essa cadeia encontra-se na seguinte matriz:

    LEN (C9:C13)

    O argumento do tipo jogo neste caso é 0. O tipo de jogo pode ser um valor de 1, 0 ou -1.

    • 1 - devolve o maior valor que é inferior ou igual ao valor de lookup

    • 0 - devolve o primeiro valor exatamente igual ao valor de procura

    • -1 - devolve o menor valor que é maior ou igual ao valor de procura especificado

    • Se omitir um tipo de correspondência, o Excel assumirá o valor 1.

    Finalmente, a função INDEX toma estes argumentos: uma matriz, e um número de linha e coluna dentro dessa matriz. A gama celular C9:C13 fornece a matriz, a função MATCH fornece o endereço celular, e o argumento final (1) especifica que o valor vem da primeira coluna da matriz.

    Se quisesse obter o conteúdo da menor cadeia de texto, substituiria max no exemplo acima por MIN.

  • Encontrar os n valores menores de um intervalo

    Este exemplo mostra como encontrar os três valores mais pequenos numa gama de células, onde foi criado um conjunto de dados de amostranas células B9:B18 com: =INT(RANDARRAY(10,1)*100). Note que o RANDARRAY é uma função volátil, por isso receberá um novo conjunto de números aleatórios cada vez que o Excel calcular.

    Fórmula de matriz excel para encontrar o nº menor valor: =SMALL(B9#,SEQUENCE(D9))

    Insira =SMALL (B9#,SEQUÊNCIA(D9)= SMALL (B9:B18,{1;2;3})

    Esta fórmula usa uma constante de matriz para avaliar a função SMALL três vezes e devolver os 3 membros mais pequenos da matriz que está contida nas células B9:B18, onde 3 é um valor variável na célula D9. Para encontrar mais valores, pode aumentar o valor na função SEQUENCE, ou adicionar mais argumentos à constante. Também pode utilizar funções adicionais com esta fórmula, como, por exemplo, SOMA ou MÉDIA. Por exemplo:

    =SOMA (SMALL(B9#,SEQUENCE(D9))

    =MÉDIA (B9#,SEQUÊNCIA(D9))

  • Encontrar os n valores maiores de um intervalo

    Para encontrar os maiores valores de uma gama, pode substituir a função SMALL pela função LARGE. Para além disso, o exemplo que se segue utiliza as funções LIN e INDIRETO.

    Insira =LARGE (B9#,ROW(INDIRECT("1:3"))ou =LARGE(B9:B18,ROW("INDIRECT("1:3"))

    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 um vazio e introduza:

    =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 da linha, e a fórmula gera agora inteiros de 2 a 11. Para corrigir esse problema, deverá adicionar a função INDIRETO à fórmula:

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

    A função INDIRECT usa as cordas de texto como argumentos (razão pela qual o intervalo 1:10 está rodeado de aspas). 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. Poderia facilmente usar a SEQUÊNCIA:

    =SEQUÊNCIA(10)

    Vamos examinar a fórmula que usou anteriormente — =LARGE (B9#,ROW(INDIRECT("1:3")) — a partir dos parênteses internos e a trabalhar para fora: A função INDIRECT devolve um conjunto de valores de texto, neste caso os valores 1 a 3. A função ROW, por sua vez, gera uma matriz de colunas de três células. A função LARGE utiliza os valores na gama celular B9:B18, e é avaliada três vezes, uma vez por cada referência devolvida pela função ROW. Se quiser encontrar mais valores, adicione uma maior gama de células à função INDIRECT. Finalmente, tal como acontece com os pequenos exemplos, pode utilizar esta fórmula com outras funções, como SUM e AVERAGE.

  • Somar um intervalo que contém valores de erro

    A função SUM no Excel não funciona quando se tenta resumir uma gama que contenha um valor de erro, como #VALUE! ou #N/A. Este exemplo mostra-lhe como resumir os valores numa gama chamada Data que contém erros:

    Use matrizes para lidar com erros. Por exemplo, =SUM(IFERROR(DATA)""Dados) resumirá a gama denominada Data, mesmo que inclua erros, como #VALUE! ou #NA!.

  • =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 é como a fórmula anterior, mas devolve o número de valores de erro numa gama chamada Data 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.

Poderá necessitar de somar valores baseados em condições.

Pode utilizar matrizes para calcular com base em determinadas condições. =SUM (IF(Vendas>0,Vendas)) somará todos os valores superiores a 0 numa gama chamada Vendas.

Por exemplo, esta fórmula de matriz resume apenas os inteiros positivos numa gama chamada Sales, que representa as células E9:E24 no exemplo acima:

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

A função IF cria uma série de valores positivos e 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 valores superiores a 0 e menos de 2500:

=Soma((>de Vendas 0)*(Vendas<2500)*(Vendas))))))(Vendas))))))(Vendas)))))(Vendas)))))(Vendas))))))(Vendas)))))(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 resumir valores superiores a 0 ou menos de 2500:

=SOMA(IF((>0)+(Vendas<2500)Vendas))

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. Ou seja, realiza operações matemáticas, como adição ou multiplicação de valores que atendam ao ESTADO Ou à condição.

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.

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, as gamas celulares têm de ter o mesmo tamanho e a mesma dimensão. Por exemplo, se o MyData for um intervalo de 3 linhas por 5 colunas, o YourData também deve ser de 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*(MyData<>YourData))

À 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.

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))

Encontrará exemplos semelhantes no livro de amostras sobre as Diferenças entre a folha de cálculo de conjuntos de dados.

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

Copie a tabela inteira abaixo e cole-a na célula A1 numa folha de cálculo em branco.

Pessoa De Vendas

Tipo de carro Type

Número Vendido

Preço Unitário Price

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)

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

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

Quando premir Ctrl+Shift+Enter,o Excel rodeia a fórmula com aparelhos ({ }) e insere uma instância da fórmula em cada célula da gama selecionada. 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 D13 do livro, digite a seguinte fórmula e, em seguida, prima Ctrl+Shift+Enter:

=SOMA(C2:C11*D2:D11)

Neste caso, o Excel multiplica os valores da matriz (a gama celular C2 a D11) e, em seguida, utiliza a função SUMpara adicionar os totais em conjunto. 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.

Além disso, note que a fórmula unicelular na célula D13 é completamente independente da fórmula multicelular (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 na coluna E ou apagar completamente essa coluna, sem afetar a fórmula em D13.

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, tem de pressionar ctrl+Shift+Enter para confirmar qualquer alteração na 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.

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 diferença fundamental é que ao utilizar uma fórmula de matriz, prima Ctrl+Shift+Enter para introduzir a sua 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á.

Funções de matriz podem ser uma forma eficiente de construir fórmulas complexas. 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).

Importante: Prima Ctrl+Shift+Enter sempre que precisar de introduzir 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 toda a gama de fórmulas (por exemplo, E2:E11), em seguida, prima Delete.

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

Por vezes, poderá ser necessário expandir uma fórmula de matriz. Selecione a primeira célula na gama de matrizes existente e continue até selecionar toda a gama a que pretende estender a fórmula. Prima F2 para editar a fórmula e, em seguida, prima CTRL+SHIFT+ENTER para confirmar a fórmula depois de ajustar a gama de fórmulas. A chave é selecionar toda a gama, começando pela célula superior esquerda na matriz. A célula superior esquerda é a que é editada.

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

  • Pode ocasionalmente 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.

  • Outros utilizadores do seu livro podem não entender as suas fórmulas. Na prática, as fórmulas de matriz geralmente não são explicadas numa folha de cálculo. Portanto, se outras pessoas precisarem de modificar os seus livros, deve ou evitar fórmulas de matriz ou certificar-se de que essas pessoas sabem sobre quaisquer fórmulas de matriz e entender como mudá-las, se for 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.

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}

Por esta altura, já sabe que precisa de pressionar Ctrl+Shift+Enter quando 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, utilize 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.

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

Criar uma constante horizontal

  1. Numa folha de cálculo em branco, selecione as células A1 a E1.

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

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

    Neste caso, deve digitar o aparelho de abertura e fecho({ }), e o Excel adicionará o segundo conjunto para si.

    É 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. Na barra de fórmula, introduza a seguinte fórmula 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órmula, 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, digite 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.

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. Lembre-se também que depois de adicionar uma constante a uma fórmula de matriz, prima 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 experimentar isto, copie a função, selecione uma célula em branco no seu livro de trabalho, cole a fórmula na barra de fórmula 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})

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.

Uma das melhores formas de usar constantes de matriz é nomeá-las. 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.
    Aparece a caixa de diálogo Defini 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. Digite a fórmula seguinte 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.

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 evívio, ou se colocar uma no lugar errado, a constante da matriz pode não ser criada corretamente, ou poderá ver 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.

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 TRANSPOSE 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. Digite 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. Digite 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. Digite 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. Introduza 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.

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 gama de números na gama celular 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órmula e, em seguida, prima Ctrl+Shift+Enter:

    =C8:E10

    Uma matriz 3x3 de células aparece nas células C1 a E3 com os mesmos valores que se vê em C8 até E10.

Criar uma constante de matriz a partir de valores existentes

  1. Com as células C1:C3 selecionadas, prima F2 para mudar para o modo de edição. 

  2. Pressione F9 para converter as referências celulares em valores. O Excel converte os valores para uma constante de matriz. A fórmula deve agora ser ={10,20,30;40,50,60;70,80,90}.

  3. Prima Ctrl+Shift+Enter para introduzir a constante da matriz como uma 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.

    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)

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

  3. Selecione a célula A10 e, em seguida, prima Ctrl+Shift+Enter para ver o conteúdo das células Mais longas A2:A6 (célula A3).

A fórmula seguinte é usada na célula A8 conta o número total de caracteres (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 SUM adiciona então esses valores e exibe o resultado (66).

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. Introduza alguns números aleatórios nas células A1:A11.

  2. Selecione as células C1 a C3. Este conjunto de células irá conter os resultados devolvidos pela fórmula de matriz.

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

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

Esta fórmula usa uma constante de matriz para avaliar a função SMALL três vezes e devolver os menores (1), segundo menor (2) e terceiro menor (3) membros na matriz que está contida nas células A1:A10 Para encontrar mais valores, adiciona mais argumentos à constante. Também pode utilizar funções adicionais com esta fórmula, como, por exemplo, SOMA ou MÉDIA. Por exemplo:

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

=MÉDIA (SMALL(A1:A10,{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 D1 a D3.

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

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

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 seu livro de trabalho de prática, introduza esta fórmula de matriz 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.

Vamos ver a fórmula que usou anteriormente — =LARGE(A5:A14,ROW("INDIRECT("1:3")) — a partir dos parênteses internos e trabalhando para fora: A função INDIRECT devolve um conjunto de valores de texto, neste caso os valores 1 a 3. A função ROW, por sua vez, gera uma matriz colunaar de três células. A função LARGE utiliza os valores na gama celular A5:A14, e é avaliada três vezes, uma vez por cada referência devolvida pela função ROW. Os valores 3200, 2700 e 2000 são devolvidos à matriz colunaar de três células. Se quiser encontrar mais valores, adicione uma maior gama de células à função INDIRECT.

Tal como acontece com exemplos anteriores, pode utilizar esta fórmula com outras funções, tais como SUM e AVERAGE.

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

Volte ao exemplo de cadeia de texto anterior, introduza a seguinte fórmula numa célula vazia e prima Ctrl+Shift+Enter:

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

O texto "bando de células que" aparece.

Examinemos agora a fórmula, começando pelos elementos internos e trabalhando em direção ao exterior. A função LEN devolve o comprimento de cada um dos itens na gama celular A2:A6. A função MAX calcula o maior valor entre esses itens, que corresponde à corda de texto mais comprida, que está na célula A3.

É 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:

(MAX (LEN(A2:A6))

e essa cadeia encontra-se na seguinte matriz:

LEN (A2:A6)

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. A gama celular A2:A6 fornece a matriz, a função MATCH fornece o endereço celular, e o argumento final (1) especifica que o valor vem da primeira coluna da matriz.

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*(MyData<>YourData))

À 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))

Reconhecimento

Partes deste artigo foram baseadas numa série de colunas excel Power User escritas por Colin Wilcox, e adaptadas dos capítulos 14 e 15 de Excel 2002 Formulas, um livro escrito por John Walkenbach, um ex-Excel MVP.

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 UserVoice do Excel.

Consulte Também

Matrizes dinâmicas e comportamento de matrizes transpostas

Fórmulas de matriz dinâmica vs. fórmulas de matriz CSE legados

Função FILTRAR

Função MATRIZALEATÓRIA

Função SEQUÊNCIA

Função ORDENAR

Função ORDENARPOR

Função EXCLUSIVOS

Erros de #TRANSPOSIÇÃO! no Excel

Operador de intersecção implícito: @

Descrição geral das fórmulas

Nota:  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 indicar-nos se estas informações foram úteis? Eis o artigo em inglês para sua referência.​

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.

×