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.

Uma fórmula de matriz é uma fórmula que pode executar cálculos vários itens de uma ou mais de uma matriz. Poderá pensar de uma matriz como uma linha ou coluna de valores ou uma combinação de linhas e colunas de valores. Fórmulas de matriz podem devolver resultados de várias ou um único resultado.

Começando com a atualização de Setembro de 2018 para o Office 365, qualquer fórmula que pode devolver resultados de várias automaticamente ajustá-los vertical ou na horizontal para células vizinhas. Esta alteração no comportamento é também acompanhar por novas várias funções de matriz dinâmica. Fórmulas de matriz dinâmicos, se estão a utilizar existentes ou as funções matriz dinâmicos, só precisam de ser introduzidos numa única célula, em seguida, confirmado, prima Enter. Fórmulas de matriz anteriores, legado requerem pela primeira vez selecionar o intervalo de saída inteira, em seguida, confirmar a fórmula com Ctrl + Shift + Enter. Está a frequentemente referidos para como fórmulas CSE .

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

  • Crie rapidamente conjuntos de dados de exemplo.

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

  • Somar apenas números que cumprem determinadas condições, como os valores mais baixos num intervalo ou números que se inserem entre um limite superior e inferior.

  • Some todos os valores Enésimos num intervalo de valores.

Os exemplos seguintes mostram como criar fórmulas de matriz com várias células e com uma célula. Sempre que possível, podemos incluiu exemplos com algumas das funções de matriz dinâmico, bem como fórmulas de matriz existentes introduzidas como dinâmicas e legadas matrizes.

Transferir os nossos exemplos

Transfira um livro de exemplo com todos os os matriz exemplos de fórmulas 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 matriz com várias células na célula H10 = F10:F19 * G10:G19 para calcular o número de carros vendidos pelo preço unitário

  • Aqui vamos está a calcular o Total de vendas de coupes e sedans de cada vendedor introduzindo = F19:F19 * G10:G19 na célula H10.

    Quando prime Enter, verá os resultados ajustar-se para baixo até H10:H19 de células. Repare que o intervalo de retorno está realçado com um limite ao clicar em qualquer célula no intervalo de retorno. Também poderá reparar em que as fórmulas em células H10:H19 estão a cinzento. Apenas lá estejam para sua referência, para que se pretende ajustar a fórmula, terá de selecionar a célula H10, onde a fórmula mestra encontra-se.

  • Fórmula de matriz de uma célula

    Fórmula de matriz de uma célula para calcular um total geral 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 (o intervalo de células F10 e G19) e, em seguida, utiliza a função SOMA para adicionar os totais em conjunto. O resultado é um total de $1,590,000 nas vendas.

    Este exemplo mostra como poderoso pode ser este tipo de fórmula. Por exemplo, suponha que tem 1.000 linhas de dados. Pode somar parte ou todo esses dados criando uma fórmula de matriz numa única célula em vez de arrastar a fórmula para baixo, através das linhas de 1.000. Além disso, repare que a fórmula de uma célula na célula H20 é completamente independente da fórmula com várias célula (a fórmula nas células H10 a H19). Este é outra vantagem de utilizar fórmulas de matriz — flexibilidade. Pode alterar outras fórmulas na coluna H sem afetar a fórmula em H20. Também pode ser aconselhável para os totais independentes semelhante a esta, tal como ajuda-o a validar a precisão dos resultados da.

  • Fórmulas de matriz dinâmico também oferecem estas vantagens:

    • Consistência    Se clicar em qualquer uma das células a partir do H10 para baixo, verá a mesma fórmula. Esse consistência pode ajudar a assegurar a exatidão maior.

    • 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 H11 e prima Delete. Excel não será alterada saída a matriz. Para alterar, é necessário selecionar a célula do canto superior esquerdo na matriz ou célula H10.

    • Tamanho de ficheiro mais pequeno    Pode utilizar frequentemente uma única fórmula de matriz em vez de várias fórmulas intermédias. Por exemplo, o exemplo de vendas de carro utiliza uma fórmula de matriz para calcular os resultados na coluna E. Se tivesse utilizado fórmulas padrão tal como = F10 * G10, F11 * G11, F12 * G12, etc., que seria utilizou 11 fórmulas diferentes para calcular os mesmos resultados. Que não é um negócio big, mas o que acontece se tinha milhares de linhas para total? Em seguida, pode fazer uma grande diferença.

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

    • Capaz    Fórmulas de matriz dinâmico ajustar automaticamente para o intervalo de saída. Se os dados de origem forem numa tabela do Excel, fórmulas de matriz dinâmico automaticamente redimensionar à medida que adiciona ou remove dados.

    • #SPILL! erro    Matrizes dinâmicas introduzidas o #SPILL! erro, que indica que o intervalo de retorno pretendida está bloqueado por algum motivo. Quando a resolver o bloqueio, a fórmula automaticamente ajustar.

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, pode criar uma matriz horizontal (uma linha). Se separar os itens utilizando o ponto e vírgula, pode criar uma matriz vertical (uma coluna). Para criar uma matriz bidimensional, delimitar os itens em cada linha com vírgulas e delimitar cada linha com ponto e vírgula.

Os seguintes procedimentos irão dar-lhe alguma prática na criação de constantes horizontais, verticais e bidimensionais. Vamos mostrar-exemplos de como utilizar a função de sequência para gerar automaticamente constantes de matriz, bem como introduzidos manualmente constantes de matriz.

  • Criar uma constante horizontal

    Utilizar o livro dos exemplos anteriores ou crie um novo livro. Selecione uma célula vazia e introduza =SEQUENCE(1,5). A função de sequência cria uma 1 linha ao 5 matriz de coluna igual a = {1,2,3,4,5}. É apresentado o seguinte resultado:

    Criar uma constante de matriz horizontal com =SEQUENCE(1,5) ou = {1,2,3,4,5}

  • Criar uma constante vertical

    Selecione qualquer célula em branco com a opção sala abaixo da mesma e introduza =SEQUENCE(5)ou = {1; 2; 3; 4; 5}. É apresentado o seguinte resultado:

    Criar uma constante de matriz vertical com = SEQUENCE(5) ou = {1; 2; 3; 4; 5}

  • Criar uma constante bidimensional

    Selecione qualquer célula em branco com a opção sala para a direita e abaixo da mesma e introduza =SEQUENCE(3,4). Consulte o seguinte resultado:

    Criar uma linha 3 por constante de matriz 4 colunas com =SEQUENCE(3,4)

    Também pode introduzir: ou = {1,2,3,4; 5,6,7,8; 9,10,11,12}, mas irá pretende pagar a atenção para o local onde coloca as ponto e vírgula versus vírgulas.

    Como pode ver, a opção de sequência oferece vantagens significativas manualmente introduzindo os valores da constante de matriz. Basicamente, poupa-lhe tempo, mas também pode ajudar reduzir erros de introdução manual. Também é mais fácil de ler, especialmente à medida que o ponto e vírgula pode ser difícil diferenciar entre os separadores de ponto e vírgula.

Eis um exemplo que utiliza constantes de matriz como parte de uma fórmula maior. No livro de exemplo, aceda à folha de cálculo constante numa fórmula ou crie uma nova folha de cálculo.

Na célula D9, podemos introduzidos =SEQUENCE(1,5,3,1), mas também pode introduzir 3, 4, 5, 6 e 7 na A9:H9 de células. Não há nada especial sobre essa selecção de número específica, basta optamos por algo que não 1-5 para diferenciação.

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

Utilize constantes de matriz em fórmulas. Neste exemplo, utilizámos = soma (D9:H(*SEQUENCE(1,5))

A função de sequência cria o equivalente a matriz constante {1,2,3,4,5}. Uma vez que o Excel executa operações em expressões entre parênteses pela primeira vez, os elementos de dois que entram na play são os valores das células no D9:H9 e o operador de multiplicação (*). Neste momento, a fórmula multiplica os valores na matriz armazenada pelos valores correspondentes a constante. É 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)

Por fim, a função SOMA adiciona os valores e devolve 85.

Para evitar utilizar a matriz armazenada e manter a operação totalmente na memória, pode substitui-la por outra constante de matriz:

=SUM(Sequence(1,5,3,1)*Sequence(1,5))ou =SUM({3,4,5,6,7}*{1,2,3,4,5})

Elementos que pode utilizar em constantes de matriz

  • Constantes de matriz podem conter números, texto, valores lógicos (tais como VERDADEIRO e FALSO) e valores de erro, como # n/d. Pode utilizar números em formatos de número inteiro, casas decimais e científico. Se incluir texto, terá de colocá-la entre 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 utilizar constantes de matriz é para atribuir um nome-los. Constantes com nome podem ser muito mais fácil de utilizar e podem ocultar algumas da complexidade da fórmulas de matriz a partir de outras pessoas. Para atribuir um nome a uma constante de matriz e utilizá-la numa fórmula, faça o seguinte:

Aceda a fórmulas > nomes definidos > Definir nome. Na caixa nome, escreva Trimestre1. 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 deverá agora o seguinte aspeto:

Adicionar uma constante de matriz com nome novo a partir de fórmulas > nomes definidos > > Gestor de nomes

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

É apresentado o seguinte resultado:

Utilizar uma constante de matriz com nome numa fórmula, tal como = Trimestre1, onde Trimestre1 tenha sido definida como = {"Janeiro", "Fevereiro", "Março"}

Se pretender que os resultados para ajustar verticalmente em vez de na horizontal, pode utilizar =transpor(Trimestre1).

Se quiser apresentar uma lista de 12 meses, tal como poderá utilizar durante a criação de uma instrução financeira, pode basear um desativar o ano atual com a função de sequência. A coisa colocada informações sobre esta função é que apesar apresenta apenas o mês, existe uma data válida por trás do mesmo que pode utilizar noutros cálculos. Encontrará estes exemplos nas folhas de cálculo denominada constante de matriz e conjunto de dados de exemplo rápida no livro de exemplo.

=TEXT(Date(YEAR(Today()),Sequence(1,12),1),"mmm")

Utilize uma combinação das funções de texto, data, ano, hoje e sequência para criar uma lista dinâmica de 12 meses

Esta opção utiliza a função data para criar uma data com base num ano atual, sequência cria uma constante de matriz a partir de 1 a 12 de Janeiro a Dezembro, em seguida, a função texto converte o formato de apresentação para "mmm" (Jan, Fev, Mar, etc.). Se quiser apresentar o nome do mês completo, tal como Janeiro, utilizaria "mmmm".

Quando utiliza uma constante com nome como fórmula de matriz, lembre-se introduzir o sinal de igual, como em = Trimestre1, não apenas Trimestre1. Se não, o Excel interpreta a matriz, como uma cadeia de texto e a fórmula não irá funcionar como esperado. Por fim, tenha em atenção que pode utilizar combinações de funções, texto e números. Todos os depende creative como pretende obter.

Os exemplos seguintes demonstram algumas das formas na qual pode colocar constantes de matriz para utilizar em fórmulas de matriz. Alguns dos exemplos utilizam a função transpor para converter linhas para colunas e vice-versa.

  • Vários cada item numa matriz

    Introduza = sequência (1,12) * 2, ou = {1,2,3,4; 5,6,7,8; 9,10,11,12} * 2

    Também pode dividir com (/), adicione 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

  • Localizar a raiz quadrada de itens dentro de um quadrado numa matriz

    Introduza =RAIZQ(SEQUENCE(1,12)^2), ou =SQRT({1,2,3,4;5,6,7,8;9,10,11,12}^2)

  • Transpor uma linha unidimensional

    Introduza =TRANSPOSE(SEQUENCE(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

    Introduza =TRANSPOSE(SEQUENCE(5,1))ou = 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

    Introduza =TRANSPOSE(SEQUENCE(3,4))ou = 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 uma matriz a partir de valores existentes

    O exemplo seguinte explica como utilizar fórmulas de matriz para criar uma nova matriz a partir de uma matriz existente.

    Introduza =SEQUENCE(3,6,10,10)ou = {10,20,30,40,50,60; 70,80,90,100,110,120; 130,140,150,160,170,180}

    Não se esqueça de escrever {(chaveta de abertura) antes de introduzir 10 e} (chaveta de fecho) depois de escrever 180, uma vez que está a criar uma matriz de números.

    Em seguida, introduza = D9 #ou = D9:I11 numa célula em branco. É apresentada uma matriz de 3 x 6 de células com os mesmos valores que vê na D9: D11. O sinal # chama- espalhado operador de intervaloe fique forma do Excel de referenciar o intervalo de matriz inteira em vez de escrever os dados de saída.

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

  • Criar uma constante de matriz a partir de valores existentes

    Pode ter os resultados de uma fórmula de matriz espalhado e converter que partes da mesma componente. Selecione a célula D9 e, em seguida, prima F2 para mudar para modo de edição. Em seguida, prima F9 para converter as referências de célula para valores, que o Excel, em seguida, converte uma constante de matriz. Quando prime Enter, a fórmula = D9 #, agora deve ser = {10,20,30; 40,50,60; 70,80,90}.

  • Contar carateres num intervalo de células

    O exemplo seguinte mostra como contar o número de carateres num intervalo de células. Isto inclui espaços.

    Contar o número total de carateres no intervalo e outras matrizes para trabalhar com cadeias de texto

    = SOMA (LEN(C9:C13))

    Neste caso, a função NÚM. CARAT devolve o comprimento de cada cadeia de texto em cada uma das células no intervalo. A função SOMA, em seguida, adiciona esses valores em conjunto e apresenta o resultado (66). Se pretende que obter o número médio de carateres, pode utilizar:

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

  • Conteúdos da célula mais longa intervalo C9:C13

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

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

    Vamos mais detalhada a fórmula, começando pelos elementos internos e trabalhar para fora. A função NÚM. CARAT devolve o comprimento de cada um dos itens no intervalo de células D2: D6. A função máximo calcula o maior valor entre esses itens, que corresponde à célula mais longa cadeia de texto, que se encontra na célula D3.

    Eis onde obter um pouco complexas coisas. A função CORRESP calcula o desvio (a posição relativa) da célula que contém a cadeia de texto maior. Para o fazer, requer três argumentos: um valor de procura, uma matriz de pesquisa e um tipo de correspondência. A função CORRESP procura a matriz de pesquisa para o valor de procura especificado. Neste caso, o valor de procura é a cadeia de texto maior:

    MAX(LEN(C9:C13)

    e essa cadeia encontra-se na seguinte matriz:

    LEN(C9:C13)

    Neste caso, o argumento de tipo de correspondência é 0. O tipo de correspondência pode ser uma 1, 0 ou valor-1.

    • 1 - devolve o maior valor que é menor ou igual a pesquisa val

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

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

    • Se omitir um tipo de correspondência, o Excel assume 1.

    Por fim, a função índice tem os seguintes argumentos: uma matriz e um número de linha e coluna dentro desse matriz. O intervalo de células C9:C13 fornece a matriz, a função CORRESP fornece o endereço de célula e o argumento final (1) Especifica a que o valor provém a primeira coluna na matriz.

    Se quisesse obter os conteúdos da cadeia de texto menor, teria substituir máximo no exemplo acima com mínimo.

  • Encontrar os n valores menores de um intervalo

    Este exemplo mostra como encontrar os três valores menores de um intervalo de células, sempre que uma matriz de dados de exemplo na células B9:B18has foi criado com: = INT (RANDARRAY(10.1) * 100). Note que RANDARRAY é uma função voláteis, para que irá obter um novo conjunto de números aleatórios sempre que o Excel calcula.

    Fórmula de matriz para localizar o menor valor enésimo do Excel: =SMALL(B9#,SEQUENCE(D9))

    Introduza =SMALL(B9#,SEQUENCE(D9), = minúscula (B9:B18, {1; 2; 3})

    Esta fórmula utiliza uma constante de matriz para avaliar a função menor três vezes e voltar os menor 3 membros na matriz contida na B9:B18 de células, onde 3 é um valor na célula D9 variável. Para localizar valores de mais, pode aumentar o valor na função sequência ou adicionar mais argumentos para a constante. Também pode utilizar funções adicionais com esta fórmula, tal como Soma ou média. Por exemplo:

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

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

  • Encontrar os n valores maiores de um intervalo

    Para localizar os valores mais altos num intervalo, pode substituir a função pequenas com a função maior. Além disso, o exemplo seguinte utiliza as funções de linha e INDIRETO .

    Introduza = grande (B9 #, linha (INDIRETO ("1:3"))), ou = grande (B9:B18,ROW(INDIRECT("1:3")))

    Neste momento, poderá ser útil saber um pouco sobre as funções de linha e INDIRETO. Pode utilizar a função LIN para criar uma matriz de números inteiros consecutivos. Por exemplo, selecione uma vazia e introduza:

    =ROW(1:10)

    A fórmula cria uma coluna de números inteiros consecutivas 10. Para ver um potencial problema, inserir uma linha acima do intervalo que contém a fórmula de matriz (ou seja, acima da linha 1). Excel ajusta as referências de linha e, a fórmula gera agora 11 números inteiros de 2. Para resolver esse problema, adicione a função indirecta à fórmula:

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

    Função INDIRETO utiliza cadeias de texto, como os respetivos argumentos (que é a razão pela qual o intervalo 1:10 está rodeado por aspas). Excel não se ajusta valores de texto quando inserir linhas ou caso contrário, deslocar-se a fórmula de matriz. Como resultado, a função LIN gera sempre a matriz de números inteiros que pretende. Pode utilizar apenas tão facilmente sequência:

    =SEQUENCE(10)

    Vamos examinar a fórmula que utilizou anteriormente — = grande (B9 #, linha (INDIRETO ("1:3"))) — começando pelos parênteses internos e trabalhar passivo: função indirecto devolve um conjunto de valores de texto, neste caso os valores de 1 a 3. A função LIN sucessivamente gera uma matriz de células de três colunas. Função maior utiliza os valores no intervalo de células B9:B18 e é avaliado três vezes, uma vez para cada referência devolvido pela função de linha. Se pretender localizar mais valores, adicionar um intervalo de células maior a função INDIRETO. Por fim, tal como acontece com os exemplos pequenas, pode utilizar esta fórmula com outras funções, como soma e média.

  • Somar um intervalo que contém valores de erro

    A função soma no Excel não funciona quando tenta somar um intervalo que contém um valor de erro, tal como #VALUE! ou # n/d. Este exemplo mostra-lhe como somar os valores de um intervalo denominado dados que contém erros:

    Utilize matrizes para lidar com erros. Por exemplo, =SUM(IF(ISERROR(Data),"",Data) irão somar um intervalo denominado dados 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 é semelhante a fórmula anterior, mas devolve o número de valores de erro de um intervalo denominado 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.

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

Pode utilizar matrizes para calcular com base em determinadas condições. =SUM(IF(Sales>0,Sales)) irão somar todos os valores de maiores que 0 num intervalo denominado de vendas.

Por exemplo, esta fórmula de matriz soma apenas os números inteiros positivos num intervalo com nome de vendas, que representa as células E9:E24 no exemplo acima:

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

A função se cria uma matriz de valores positivos e falsos. A função soma essencialmente ignora os valores de falsos porque 0 + 0 = 0. O intervalo de células que utilizar esta fórmula pode consistir qualquer número de linhas e colunas.

Também pode somar os valores que correspondem a mais do que uma condição. Por exemplo, esta fórmula de matriz calcula valores maiores do que 0 e menor que 2500:

=SUM((Sales>0)*(Sales<2500)*(Sales))

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 utilizam um tipo de condição ou. Por exemplo, pode somar valores maiores do que 0 ou menos de 2500:

=SUM(IF((Sales>0)+(Sales<2500),Sales))

Não é possível utilizar o operador e e ou funções em fórmulas de matriz diretamente uma vez que essas funções devolvem um único resultado, VERDADEIRO ou FALSO e funções de matriz necessitam de matrizes de resultados. Pode resolver o problema utilizando a lógica apresentada na fórmula anterior. Por outras palavras, efetuar operações matemáticas, tal como a adição ou multiplicação nos valores que correspondam ao ou ou condição e.

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 em dois intervalos de células com o nome Osmeusdados e Osseusdados e devolve o número de diferenças entre as duas. Se o conteúdo dos dois intervalos é idêntico, a fórmula devolve 0. Para utilizar esta fórmula, os intervalos de células tem de ser o mesmo tamanho e da mesma dimensão. Por exemplo, se Osmeusdados for um intervalo de 3 linhas por 5 colunas, Osseusdados também têm de ser 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.

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á semelhantes exemplos no livro de exemplo na folha de cálculo de diferenças entre os 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 toda a tabela abaixo e cole-a na célula A1 numa folha de cálculo em branco.

Vendas Pessoa

Carro Tipo

Número Vendidos

Unidade Preço

Total 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 o Total de vendas de coupes e sedans de cada vendedor, selecione células E2:E11, introduza a fórmula = C2: C11 * D2: D11, e, em seguida, prima Ctrl + Shift + Enter.

  2. Para ver o Total geral 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.

Ao premir Ctrl + Shift + Enter, o Excel rodeia a fórmula com a chavetas ({}) e insere uma instância da fórmula em cada célula do intervalo selecionado. Isto acontece muito rapidamente, por isso o que vê na coluna E é a quantidade total de vendas para cada tipo de carro de cada vendedor. Se selecionar E2, em seguida, selecione E3, E4 e assim sucessivamente, verá que a mesma fórmula é apresentada: {= 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, escreva a seguinte fórmula e, em seguida, prima Ctrl + Shift + Enter:

=SOMA(C2:C11*D2:D11)

Neste caso, o Excel multiplica os valores na matriz (o intervalo de células C2 e D11) e, em seguida, utiliza a função somapara adicionar os totais em conjunto. O resultado é um total de $1,590,000 nas vendas. Este exemplo mostra como poderoso pode ser este tipo de fórmula. Por exemplo, suponha que tem 1.000 linhas de dados. Pode somar parte ou todo esses dados criando uma fórmula de matriz numa única célula em vez de arrastar a fórmula para baixo, através das linhas de 1.000.

Além disso, repare que a fórmula de uma célula na célula D13 é completamente independente da fórmula com várias célula (a fórmula nas células E2 a E11). Este é outra vantagem de utilizar fórmulas de matriz — flexibilidade. Pode alterar as fórmulas na coluna E ou eliminar essa coluna completamente, sem afetar a fórmula na 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 a tecla Delete. Tem de ou selecione o intervalo de células inteiro (E2 através de E11) e altere a fórmula para a matriz inteira ou deixar a matriz, tal como está. Como uma medida de segurança adicional, tem de premir Ctrl + Shift + Enter para confirmar a qualquer alteração à 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.

Em geral, as fórmulas de matriz utilizam a sintaxe da fórmula padrão. Todos são começam com um sinal de igual (=) e pode utilizar a maioria das funções do Excel incorporadas nas fórmulas de matriz. A diferença de chave é que ao utilizar uma fórmula de matriz, prima Ctrl + Shift + Enter para introduzir a sua fórmula. Após fazer isto, o Excel rodeia a fórmula de matriz com chavetas — se escrever as chavetas manualmente, a fórmula será convertido numa cadeia de texto e não irá funcionar.

Funções de matriz podem ser uma forma eficaz para criar fórmulas complexas. A matriz fórmula =SUM(C2:C11*D2:D11) é semelhante ao seguinte: =SUM(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 introduzir uma fórmula de matriz. Aplica-se uma célula e com várias células fórmulas.

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 o intervalo de fórmula completo (por exemplo, E2:E11), em seguida, prima a tecla Delete.

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

Por vezes, poderá ter de expandir uma fórmula de matriz. Selecione a primeira célula no intervalo de matriz existentes e continue até que selecionou a todo o intervalo que pretende expandir a fórmula para. Prima F2 para editar a fórmula, em seguida, prima CTRL + SHIFT + ENTER para confirmar a fórmula assim que tiver ajustado o intervalo de fórmula. A chave é selecionar todo o intervalo, começando com a célula do canto superior esquerdo na matriz. A célula do canto superior esquerdo é que é editado.

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

  • Poderá, ocasionalmente, se esqueça de premir Ctrl + Shift + Enter. Pode ocorrer a para o mesmo os utilizadores mais experientes do Excel. Lembre-se a premir esta combinação de teclas sempre que introduzir ou edita uma fórmula de matriz.

  • Outros utilizadores do seu livro poderão não compreender nas suas fórmulas. Na prática, as fórmulas de matriz geralmente não explicadas numa folha de cálculo. Por isso, se precisar de outras pessoas modificar os seus livros, deve quer evitar fórmulas de matriz ou certifique-se essas pessoas saibam quaisquer fórmulas de matriz e compreender como alterá-las, se precisam de.

  • 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 agora, sabe que precisa de premir Ctrl + Shift + Enter quando criar fórmulas de matriz. Uma vez que as constantes de matriz são um componente de fórmulas de matriz, escritos entre as constantes com chavetas ao escrevê-los manualmente. Em seguida, utilize Ctrl + Shift + Enter para introduzir a fórmula completa.

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.

Eis uma matriz numa única linha: {1,2,3,4}. Eis uma matriz numa única coluna: {1; 2; 3; 4}. Seguem- se uma matriz de duas linhas e quatro colunas: {1,2,3,4; 5,6,7,8}. A matriz de duas linha, a primeira linha é 1, 2, 3 e 4 e da segunda linha é 5, 6, 7 e 8. Um ponto e vírgula única 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órmulas, introduza a seguinte fórmula e, em seguida, prima Ctrl + Shift + Enter:

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

    Neste caso, deverá escrever as chavetas ({}) e, e o Excel vai 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órmulas, 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ó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.

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 de que o Excel não rodear constantes de matriz com chavetas; Escreva realmente-los. Também não se esqueça de que depois de adicionar uma constante para 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, copiar a função, selecione uma célula em branco no seu livro, cole a fórmula na barra de fórmulas e, em seguida, prima Ctrl + Shift + Enter. Verá o mesmo resultado como fez no exercício anterior que utilizado 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 da melhor forma de utilizar constantes de matriz é para atribuir um nome-los. Constantes com nome podem ser muito mais fácil de utilizar e podem ocultar algumas da complexidade da fórmulas de matriz a partir de outras pessoas. Para atribuir um nome a uma constante de matriz e utilizá-la 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.

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

  • Alguns elementos não podem ser separados com o caráter inicial maiúscula. Se omitir uma vírgula ou ponto e vírgula ou se coloca um no local errado, a constante de matriz não pode ser criada correctamente 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 seguintes demonstram algumas das formas na qual pode colocar constantes de matriz para utilizar em fórmulas de matriz. Alguns dos exemplos utilizam a função transpor para converter linhas para 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. 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 matriz de números no intervalo de células C8: E10, utilizando uma fórmula de matriz. Na folha de cálculo, C8 a E10 deverá ter o aspeto da seguinte forma:

    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 e, em seguida, prima Ctrl + Shift + Enter:

    =C8:E10

    É apresentada uma 3x3 matriz de células nas células C1 a E3 com os mesmos valores, que consulte o artigo C8 a E10.

Criar uma constante de matriz a partir de valores existentes

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

  2. Prima F9 para converter as referências de célula aos valores. Excel converte os valores numa constante de matriz. Deverá agora estar a fórmula = {10,20,30; 40,50,60; 70,80,90}.

  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.

    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 carateres nas células a2: a6 (66).

  3. Selecione a célula A10 e, em seguida, prima Ctrl + Shift + Enter para ver os conteúdos da célula mais longa das células a2: a6 (célula A3).

É utilizada a seguinte fórmula na célula A8 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. Caract devolve o comprimento de cada cadeia de texto em cada uma das células no intervalo. A função Soma , em seguida, adiciona esses valores em conjunto e apresenta 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 na A1:A11 de células.

  2. Selecione as células C1 a C3. Este conjunto de células vai introduzir 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 utiliza uma constante de matriz para avaliar a função pequenas três vezes e voltar a menor (1), o segundo menor (2) e o terceiro menor (3) membros na matriz contidos nas células a1: a10 para localizar valores mais, adicionar mais argumentos para a constante. Também pode utilizar funções adicionais com esta fórmula, tal como Soma ou média. Por exemplo:

= SOMA (MENOR (A1: A10; {1.2.3})

= MÉDIA (MENOR (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órmulas, introduza esta fórmula e, em seguida, prima Ctrl + Shift + Enter:

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

Neste momento, poderá ser útil saber um pouco sobre as funções de INDIRETO e linha . 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 exercícios práticos, 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 dar uma fórmula que utilizou anteriormente — = grande (A5:A14,ROW(INDIRECT("1:3"))) — começando pelos parênteses internos e trabalhar passivo: A função indirecto devolve um conjunto de valores de texto, neste caso os valores de 1 a 3. A função Lin sucessivamente gera uma matriz de colunas de três células. A função de grande utiliza os valores no intervalo de células A5:A14 e é avaliado três vezes, uma vez para cada referência devolvido pela função de linha . Os valores 3200, 2700 e 2000 são devolvidos à célula de três colunas matriz. Se pretender localizar mais valores, adicione um intervalo de células maior da função INDIRETO .

Como com exemplos anteriores, pode utilizar esta fórmula com outras funções, como Soma e média.

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

Aceda novamente para o 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 "conjunto de células que" é apresentado.

Vamos mais detalhada a fórmula, começando pelos elementos internos e trabalhar para fora. A função Núm. Caract devolve o comprimento de cada um dos itens no intervalo de células a2: a6. A função máximo calcula o maior valor entre esses itens, que corresponde à célula mais longa cadeia de texto, que se encontra 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:

(MÁX (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 tem os seguintes argumentos: uma matriz e um número de linha e coluna dentro desse matriz. O intervalo de células a2: a6 fornece a matriz, a função CORRESP fornece o endereço de célula e o argumento final (1) Especifica a que o valor provém a primeira coluna na 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*(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))

Confirmação

Partes deste artigo foram com base 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 Excel 2002 fórmulas, um livro escrito por João por Walkenbach, MVP do Excel antigo.

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âmico vs. CSE legado fórmulas de matriz

Função FILTRAR

Função MATRIZALEATÓRIA

Função SEQUÊNCIA

Função SIMPLIFICAR

Função ORDENAR

Função ORDENARPOR

Função EXCLUSIVOS

Erros de #TRANSPOSIÇÃO! no Excel

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.

×