Para se tornar um usuário avançado do Excel, você precisa saber como usar as fórmulas de matriz, que podem executar cálculos que não podem ser feitos através de fórmulas não-matriz. O artigo a seguir baseia-se em uma série de colunas para usuários avançados do Excel escritas por Colin Wilcox e adaptada dos capítulos 14 e 15 de Excel 2002 Formulas (Em inglês), um livro escrito por John Walkenbach, MVP em Excel. Para obter mais informações sobre os outros livros de John, consulte a página de livro (Em inglês) correspondente.
Uma fórmula de matriz pode executar vários cálculos em um ou mais itens de uma matriz. As fórmulas de matriz podem retornar um ou vários resultados. Por exemplo, você pode inserir uma fórmula de matriz em um intervalo de células e usar a fórmula de matriz para calcular uma coluna ou linha de subtotais. Você também pode inserir uma fórmula de matriz em uma única célula e calcular um valor único. Uma fórmula de matriz que reside em várias células é denominada fórmula com várias células, enquanto uma fórmula de matriz que reside em uma única célula é denominada fórmula com uma única célula.
Os exemplos da seção a seguir mostram como criar fórmulas de matriz com uma ou várias células.
Além disso, observe que a fórmula com célula única (na célula B13) é completamente independente da fórmula com várias células (nas células E2 a E11). Isso aponta uma outra vantagem das fórmulas de matriz: a flexibilidade. Você pode executar quantas ações desejar, como alterar as fórmulas na coluna E ou excluir essa coluna, sem afetar a fórmula com célula única.
As fórmulas de matriz também oferecem as seguintes vantagens:
O próximo aspecto que você precisa entender é que as funções de matriz são uma forma abreviada. Por exemplo, a função com várias células utilizada anteriormente é o equivalente de:
=C2*D2
=C3*D3
e assim por diante. A fórmula com célula única contida na célula B13 condensa todas essas operações de multiplicação, mais a aritmética necessária para adicionar esses subtotais: =E2+E3+E4 e assim sucessivamente.
Sempre que você trabalha com fórmulas com várias células, também precisa seguir estas regras:
={1,2,3,4,5}
Anteriormente neste artigo, enfatizamos a necessidade de pressionar CTRL+SHIFT+ENTER durante a criação das fórmulas de matriz. Como as constantes de matriz são um componente das fórmulas de matriz, delimite manualmente as constantes com chaves digitando-as. Em seguida, use CTRL+SHIFT+ENTER para inserir a fórmula inteira.
Se você delimitar (separar) os itens com vírgulas, criará uma matriz horizontal (uma linha). Se você delimitar os itens com ponto-e-vírgulas, criará uma matriz vertical (uma coluna). Para criar uma matriz bidimensional, delimite os itens em cada linha usando vírgulas e delimite cada linha usando ponto-e-vírgulas.
Assim como acontece com as fórmulas de matriz, você pode usar constantes de matriz com qualquer função interna do Excel. As seções a seguir explicam como criar cada tipo de constante e como usar essas constantes com as funções do Excel.
Talvez você deseje saber por que não pode simplesmente digitar os números manualmente. Prossiga, pois a seção Usar constantes em fórmulas, posteriormente neste artigo, demonstra as vantagens do uso de constantes de matriz.
O valor 85 aparece na célula A3. A próxima seção explicará como a fórmula funciona.
Função
Matriz armazenada
Operador
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 delimita as constantes de matriz com chaves. Você é que deve fazer isso. Lembre-se também de que, após adicionar uma fórmula de matriz, deve pressionar CTRL+SHIFT+ENTER para inserir a fórmula.
Como o Excel executa as operações entre parênteses primeiro, os próximos dois elementos a serem considerados são os valores armazenados na pasta de trabalho (A1:E1) e o operador. Neste ponto, a fórmula multiplica os valores da matriz armazenada pelos valores correspondentes da constante. É o equivalente de:
=SOMA(A1*1,B1*2,C1*3,D1*4,E1*5)
Finalmente, a função SOMA adiciona os valores e a soma 85 aparece na célula A3:
Para evitar o uso da matriz armazenada e manter a operação na memória, substitua a matriz armazenada por outra constante de matriz:
=SOMA({3,4,5,6,7}*{1,2,3,4,5})
Tente fazer isso. Copie a função, selecione uma célula em branco na pasta de trabalho, cole a fórmula na barra de fórmulas e pressione CTRL+SHIFT+ENTER. Você verá o mesmo resultado do exercício anterior que usou a fórmula de matriz =SOMA(A1:E1*{1,2,3,4,5}).
As constantes de matriz não podem obter matrizes, fórmulas ou funções adicionais. Em outras palavras, elas podem conter somente texto ou números separados por vírgulas ou ponto-e-vírgulas. O Excel exibe uma mensagem de aviso quando você insere uma fórmula como {1,2,A1:D4} ou {1,2,SOMA(Q2:Z8)}. Além disso, os valores numéricos não podem conter sinais de porcentagem, cifrões, vírgulas ou parênteses.
Ao usar uma constante nomeada como uma fórmula de matriz, lembre-se de digitar o sinal de igual. Se você não fizer isso, o Excel interpretará a matriz como uma cadeia de texto. Finalmente, tenha em mente que é possível usar combinações de texto e números.
A fórmula se vincula aos valores armazenados nas células E1 a G3 na planilha Dados. A alternativa para esta fórmula de matriz com várias células é colocar uma fórmula exclusiva em cada célula da planilha Matrizes, conforme mostrado a seguir.
={1,2,3;5,6,7;9,10,11}
O vínculo entre as planilhas Dados e Matrizes foi quebrado e a fórmula de matriz foi substituída por uma constante de matriz.
Neste caso, a função NÚM.CARACT retorna o tamanho de cada cadeia de texto em cada uma das células do intervalo. Em seguida, a função SOMA adiciona esses valores e exibe os resultado na célula que contém a fórmula, C7.
Esta fórmula usa uma constante de matriz para avaliar a função MENOR três vezes e retornar o primeiro menor (1), o segundo menor (2) e o terceiro menor (3) membros da matriz contida nas células A1:A10. Para localizar mais valores, adicione mais argumentos à constante e um número equivalente das células de resultado ao intervalo A12:A14. Você também pode usar funções com esta fórmula, como SOMA ou MÉDIA. Por exemplo:
=SOMA(MENOR(A1:A10,{1;2;3}))
=MÉDIA(MENOR(A1:A10,{1;2;3}))
Neste ponto, pode ser útil saber um pouco sobre as funções LIN e INDIRETO. Você pode usar a função LIN para criar uma matriz de números inteiros consecutivos. Por exemplo, selecione uma coluna vazia de 10 células na pasta de trabalho da prática, insira esta fórmula de matriz nas células A1:A10 e pressione CTRL+SHIFT+ENTER:
=LIN(1:10)
A fórmula cria uma coluna de 10 números inteiros consecutivos. Para ver um problema possível, 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 linha e a fórmula gera números inteiros de 2 a 11. Para resolver esse problema, adicione a função INDIRETO à fórmula:
=LIN(INDIRETO("1:10"))
A função INDIRETO usa cadeias de texto como argumentos (é por isso que o intervalo 1:10 é delimitado por aspas duplas). O Excel não ajusta os valores de texto quando você insere linhas ou move a fórmula de matriz. Conseqüentemente, a função LIN sempre gera a matriz de números inteiros desejados.
Vamos examinar a fórmula usada anteriormente — =MAIOR(A1:A10,LIN(INDIRETO("1:3"))) — começando pelos parênteses internos até chegar aos externos: a função INDIRETO retorna um conjunto de valores de texto; nesse caso, os valores de 1 a 3. A função LIN, por sua vez, gera uma matriz de coluna de três células. A função MAIOR usa os valores do intervalo de células A1:A10 e é avaliada três vezes, uma para cada referência retornada pela função LIN. Os valores 3200, 2700 e 2000 são retornados na matriz de coluna de três células. Se você deseja localizar mais valores, adicione um intervalo de células maior à função INDIRETO.
Finalmente, você pode usar esta fórmula com outras funções, como SOMA e MÉDIA.
Vamos examinar a fórmula, começando pelos elementos internos até chegar aos externos. A função NÚM.CARACT retorna o tamanho de cada item do intervalo de células C1:C5. A função MÁXIMO calcula o maior valor entre esses itens, que corresponde à maior cadeia de texto, que está na célula C3.
É aqui que as coisas ficam um pouco mais complicadas. A função MATCH calcula o deslocamento (a posição relativa) da célula que contém a maior cadeia de texto. Para fazer isso, são necessários três argumentos: um valor de pesquisa, uma matriz de pesquisa e um tipo de correspondência. A função MATCH procura o valor de pesquisa especificado na matriz de pesquisa. Nesse caso, o valor de pesquisa é a maior cadeia de texto:
(MÁXIMO(NÚM.CARACT(C1:C5))
e essa cadeia de caracteres reside nessa matriz:
NÚM.CARACT(C1:C5)
O argumento de tipo de correspondência é 0. O tipo de correspondência pode ser composto pelo valor 1, 0 ou -1. Se você especificar 1, MATCH retornará o maior valor menor ou igual ao valor de pesquisa. Se você especificar 0, MATCH retornará o primeiro valor exatamente igual ao valor de pesquisa. Se você especificar -1, MATCH localizará o menor valor maior ou igual ao valor de pesquisa especificado. Se você omitir um tipo de correspondência, o Excel assumirá 1.
Finalmente, a função INDEX adota estes argumentos: uma matriz e um número de linha e coluna dentro dessa matriz. O intervalo de células C1:C5 fornece a matriz, a função MATCH oferece o endereço da célula e o argumento final (1) especifica que o valor é proveniente da primeira coluna da matriz.
Para obter mais informações sobre as funções discutidas aqui, consulte a Ajuda do Excel.
=SOMA(SE(ÉERROS(Dados),"",Dados))
A fórmula cria uma nova matriz que contém os valores originais menos quaisquer valores de erro. Começando pelas funções internas até chegar às externas, a função ÉERROS procura erros no intervalo de células (Dados). A função SE retornará um valor específico se uma condição especificada for avaliada como VERDADEIRO e outro valor se for avaliada como FALSO. Nesse caso, ela retornará cadeias de caracteres vazias ("") para todos os valores de erro porque são avaliadas como VERDADEIRO e retornará os valores restantes do intervalo (Dados) porque são avaliadas como FALSO. Isso significa que elas não contém valores de erro. Em seguida, a função SOMA calcula o total da matriz filtrada.
=SOMA(SE(ÉERROS(Dados),1,0))
Esta fórmula cria uma matriz que contém o valor 1 para as células que contém erros e o valor 0 para as células que não contém erros. Você pode simplificar a fórmula e obter o mesmo resultado removendo o terceiro argumento da função SE:
=SOMA(SE(ÉERROS(Dados),1))
Se você não especificar o argumento, a função SE retornará FALSO se uma célula não contiver um valor de erro. Você pode simplificar ainda mais a fórmula:
=SOMA(SE(ÉERROS(Dados)*1))
Esta versão funciona porque VERDADEIRO*1=1 e FALSO*1=0.
=SOMA(SE(Vendas>0,Vendas))
A função SE cria uma matriz de valores positivos e falsos. A função SOMA ignora essencialmente os valores falsos porque 0+0=0. O intervalo de células usado nesta fórmula pode ser composto por qualquer número de linhas e colunas.
Você também pode somar valores que atendem a 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 mente que esta fórmula retornará um erro se o intervalo contiver uma ou mais células não-numéricas.
Você também pode criar fórmulas de matriz que usam um tipo de condição OU. Por exemplo, você pode somar valores menores que 5 e maiores que 15:
=SOMA(SE((Vendas<5)+(Vendas>15),Vendas))
A função SE localiza todos os valores menores que 5 e maiores que 15, e passa esses valores para a função SOMA.
Importante Você não pode usar diretamente as funções E e OU nas fórmulas de matriz porque essa funções retornam um único resultado, VERDADEIRO ou FALSO, e as funções de matriz requerem matrizes de resultados. Você pode solucionar o problema usando a lógica mostrada na fórmula anterior. Em outras palavras, você executa operações matemáticas, como adição ou multiplicação, em valores que atendem à condição OU e E.
=MÉDIA(SE(Vendas<>0,Vendas))
A função SE cria uma matriz de valores que não são iguais a 0 e passa esses valores para a função MÉDIA.
=SOMA(SE(MeusDados=SeusDados,0,1))
A fórmula cria uma nova matriz do mesmo tamanho dos intervalos que você está comparando. A função SE preenche a matriz com o valor 0 e o valor 1 (0 para diferenças e 1 para células idênticas). Em seguida, a função SOMA retorna a soma dos valores da matriz.
Você pode simplificar a fórmula da seguinte forma:
=SOMA(1*(MeusDados<>SeusDados))
Assim como a fórmula que conta valores de erro em um intervalo, esta fórmula funciona porque VERDADEIRO*1=1 e FALSO*1=0.
=MÍNIMO(SE(Dados=MÁXIMO(Dados),LIN(Dados),""))
A função SE cria uma nova matriz que corresponde ao intervalo Dados. Se uma célula correspondente contiver o valor máximo do intervalo, a matriz conterá o número de linha. Do contrário, a matriz conterá uma cadeia de caracteres vazia (""). A função MÍNIMO usa a nova matriz como segundo argumento e retorna o menor valor, que corresponde ao número de linha do valor máximo em Dados. Se o intervalo Dados contiver valores máximos idênticos, a fórmula retornará retornará a linha do primeiro valor.
Se você deseja retornar o endereço de célula real de um valor máximo, use esta fórmula:
=ENDEREÇO(MÍNIMO(SE(Dados=MÁXIMO(Dados),LIN(Dados),"")),COLUNA(Dados))
Ocultar tudoNeste artigo
- Conheça as fórmulas de matriz
- Conheça as constantes de matriz
- Colocando as fórmulas de matriz básicas em funcionamento
- Colocando as fórmulas de matriz avançadas em funcionamento
Conheça as fórmulas de matriz
Esta seção fornece uma introdução às fórmulas de matriz e explica como inseri-las, editá-las e solucionar seus problemas.Por que usar as fórmulas de matriz?
Se você tem experiência com fórmulas do Excel, sabe que pode executar operações um tanto sofisticadas. Por exemplo, você pode calcular o custo total de um empréstimo em um dado número de anos. No entanto, se você realmente deseja conhecer a fundo as fórmulas do Excel, precisa saber como usar as fórmulas de matriz. Use-as para realizar tarefas complexas, como:- Contar o número de caracteres contidos em um intervalo de células.
- Somar apenas números que atendam a determinadas condições, como os valores inferiores de um intervalo ou os números que estão entre um limite inferior e superior.
- Somar cada valor n em um intervalo de valores.
Uma introdução rápida às matrizes e fórmulas de matriz
Se você conhece ao menos um pouco de programação, provavelmente já se deparou com o termo matriz. Para as finalidades deste artigo, basta dizer que uma matriz é um conjunto de itens. No Excel, esses itens podem residir em uma única linha (denominada matriz horizontal unidimensional), em uma coluna (matriz vertical unidimensional) ou em várias linhas e colunas (matriz bidimensional). Você não pode criar matrizes tridimensionais ou fórmulas de matriz no Excel.Uma fórmula de matriz pode executar vários cálculos em um ou mais itens de uma matriz. As fórmulas de matriz podem retornar um ou vários resultados. Por exemplo, você pode inserir uma fórmula de matriz em um intervalo de células e usar a fórmula de matriz para calcular uma coluna ou linha de subtotais. Você também pode inserir uma fórmula de matriz em uma única célula e calcular um valor único. Uma fórmula de matriz que reside em várias células é denominada fórmula com várias células, enquanto uma fórmula de matriz que reside em uma única célula é denominada fórmula com uma única célula.
Os exemplos da seção a seguir mostram como criar fórmulas de matriz com uma ou várias células.
Experimente!
Este exercício mostra como usar fórmulas de matriz com uma ou várias células para calcular um conjunto de números de vendas. O primeiro conjunto de etapas usa uma fórmula com várias células para calcular um conjunto de subtotais. O segundo conjunto usa uma fórmula com uma única célula para calcular um total geral.Criar uma fórmula de matriz com várias células
- Abra uma nova pasta de trabalho em branco.
- Copie os dados de exemplo da planilha e cole-os na nova pasta de trabalho começando pela célula A1.
Como copiar os dados de uma planilha de exemplo Observação Não selecione os cabeçalhos da linha ou da coluna.
Selecionando um exemplo na Ajuda
- Pressione CTRL+C.
- Na planilha, selecione a célula A1 e pressione CTRL+V.
| Vendedor | Tipo de Carro | Número Vendido | Preço Unitário | Total de Vendas |
|---|---|---|---|---|
| Barnhill | Sedan | 5 | 2200 | |
| Coupe | 4 | 1800 | ||
| Ingle | Sedan | 6 | 2300 | |
| Coupe | 8 | 1700 | ||
| Jordan | Sedan | 3 | 2000 | |
| Coupe | 1 | 1600 | ||
| Pica | Sedan | 9 | 2150 | |
| Coupe | 5 | 1950 | ||
| Sanchez | Sedan | 6 | 2250 | |
| Coupe | 8 | 2000 |
- Use o botão Opções de Colagem
exibido para fazer a correspondência da formatação de destino. - Para multiplicar os valores da matriz (o intervalo de células C2 a D11), selecione as células E2 a E11 e insira a fórmula a seguir na barra de fórmulas:
=C2:C11*D2:D11
- Pressione CTRL+SHIFT+ENTER.
Criar uma fórmula de matriz com uma única célula
- Na célula A13 da pasta de trabalho, digite Total de Vendas.
- Na célula B13, digite a fórmula a seguir e pressione CTRL+SHIFT+ENTER:
=SOMA(C2:C11*D2:D11)
Neste caso, o Excel multiplica os valores da matriz (o intervalo de células C2 a D11) e usa a função SOMA para adicionar os totais. O resultado é um total geral de US$ 111.800 em vendas. Este exemplo mostra até onde vai a eficiência deste tipo de fórmula. Por exemplo, suponhamos que você tenha 15.000 linhas de dados. Você pode somar os dados total ou parcialmente criando uma fórmula de matriz em uma única célula.Além disso, observe que a fórmula com célula única (na célula B13) é completamente independente da fórmula com várias células (nas células E2 a E11). Isso aponta uma outra vantagem das fórmulas de matriz: a flexibilidade. Você pode executar quantas ações desejar, como alterar as fórmulas na coluna E ou excluir essa coluna, sem afetar a fórmula com célula única.
As fórmulas de matriz também oferecem as seguintes vantagens:
- Consistência Se você clicar em qualquer uma das células de E2 para baixo, verá a mesma fórmula. Essa consistência pode conferir maior precisã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 pressione DELETE. Você precisa selecionar todo o intervalo de células (E2 a E11) e alterar a fórmula da matriz inteira ou deixar a matriz como está. Como medida de segurança adicional, pressione CTRL+SHIFT+ENTER para confirmar a alteração da fórmula.
- Menores arquivos Você geralmente pode usar uma única fórmula de matriz em vez de várias fórmulas intermediárias. Por exemplo, a pasta de trabalho criada para este exercício usa uma única fórmula de matriz para calcular os resultados na coluna E. Se você tivesse usado fórmulas padrão (como =C2*D2), teria usado 11 fórmulas diferentes para calcular os mesmos resultados.
Uma análise da sintaxe de fórmula de matriz
Na maioria das vezes, as fórmulas de matriz usam a sintaxe de fórmula padrão. Todas elas começam com um sinal de igual e você pode usar qualquer função interna do Excel nas fórmulas de matriz. A diferença-chave é que, ao usar uma fórmula de matriz, você deve pressionar CTRL+SHIFT+ENTER para inserir a fórmula. Quando você faz isso, o Excel delimita a fórmula de matriz com chaves — se você digitar as chaves manualmente, a fórmula será convertida em uma cadeia de texto e não funcionará.O próximo aspecto que você precisa entender é que as funções de matriz são uma forma abreviada. Por exemplo, a função com várias células utilizada anteriormente é o equivalente de:
=C2*D2
=C3*D3
e assim por diante. A fórmula com célula única contida na célula B13 condensa todas essas operações de multiplicação, mais a aritmética necessária para adicionar esses subtotais: =E2+E3+E4 e assim sucessivamente.
Regras para a inserção e alteração de fórmulas de matriz
Vale a pena repetir a principal regra de criação de uma fórmula de matriz: pressione CTRL+SHIFT+ENTER sempre que precisar inserir ou editar uma fórmula de matriz. Essa regra se aplica a fórmulas com uma e várias células.Sempre que você trabalha com fórmulas com várias células, também precisa seguir estas regras:
- Selecione o intervalo de células para reter os resultados antes de inserir a fórmula. Você fez isso na etapa 3 do exercício de fórmula de matriz com várias células quando selecionou as células E2 a E11.
- Não altere o conteúdo de uma célula individual em uma fórmula de matriz. Como experiência, selecione a célula E3 na pasta de trabalho de exemplo e pressione DELETE.
- Mova ou exclua toda a fórmula de matriz, mas não mova ou exclua parte dela. Em outras palavras, para reduzir uma fórmula de matriz, primeiro exclua a fórmula existente e comece novamente.
Dica Para excluir uma fórmula de matriz, selecione a fórmula inteira (por exemplo, =C2:C11*D2:D11), pressione DELETE e, em seguida, pressione CTRL+SHIFT+ENTER.
- Não insira células em branco nem exclua células em uma fórmula de matriz com várias células.
Expandindo uma fórmula de matriz
Pode ser que você precise expandir uma fórmula de matriz. (Lembre-se de que não é possível reduzir uma fórmula de matriz.) O processo não é complicado, mas você deve ter em mente as regras listadas na seção anterior.- Na pasta de trabalho de exemplo, limpe qualquer texto e fórmula com célula única que esteja localizado abaixo da tabela principal.
- Cole essas linhas de dados adicionais na pasta de trabalho começando pela célula A12. Use o botão Opções de Colagem exibido para fazer a correspondência da formatação de destino.
| Toth | Sedan | 6 | 2500 |
|---|---|---|---|
| Coupe | 7 | 1900 | |
| Wang | Sedan | 4 | 2200 |
| Coupe | 3 | 2000 | |
| Young | Sedan | 8 | 2300 |
| Coupe | 8 | 2100 |
- Selecione o intervalo de células que contém a fórmula de matriz atual (E2:E11), mais as células vazias (E12:E17) que estão ao lado dos novos dados. Em outras palavras, selecione as células E2:E17.
- Pressione F2 para alternar para o modo de edição.
- Na barra de fórmulas, altere C11 a C17, altere D11 a D17 e pressione CTRL+SHIFT+ENTER. O Excel atualiza a fórmula nas células E2 a E11 e insere uma instância da fórmula nas novas células, E12 a E17.
Desvantagens do uso das fórmulas de matriz
As fórmulas de matriz podem parecer mágicas, mas elas também têm algumas desvantagens:- Ocasionalmente, você pode se esquecer de pressionar CTRL+SHIFT+ENTER. Lembre-se de pressionar essa combinação de teclas sempre que inserir ou editar uma fórmula de matriz.
- Pode ser que outros usuários não compreendam suas fórmulas. As fórmulas de matriz são relativamente não-documentadas. Portanto, se outras pessoas precisarem modificar suas pastas de trabalho, evite as fórmulas de matriz ou verifique se esses usuários sabem como alterá-las.
- Dependendo da velocidade do processamento e da memória do computador, as fórmulas de matriz grandes podem tornar os cálculos lentos.
Conheça as constantes de matriz
Esta seção fornece uma introdução às constantes de matriz e explica como inseri-las, editá-las e solucionar seus problemas.Uma breve introdução às constantes de matriz
As constantes de matriz são um componente das fórmulas de matriz. Para criar constantes de matriz, insira uma lista de itens e delimite-a manualmente com chaves ({ }), da seguinte maneira:={1,2,3,4,5}
Anteriormente neste artigo, enfatizamos a necessidade de pressionar CTRL+SHIFT+ENTER durante a criação das fórmulas de matriz. Como as constantes de matriz são um componente das fórmulas de matriz, delimite manualmente as constantes com chaves digitando-as. Em seguida, use CTRL+SHIFT+ENTER para inserir a fórmula inteira.
Se você delimitar (separar) os itens com vírgulas, criará uma matriz horizontal (uma linha). Se você delimitar os itens com ponto-e-vírgulas, criará uma matriz vertical (uma coluna). Para criar uma matriz bidimensional, delimite os itens em cada linha usando vírgulas e delimite cada linha usando ponto-e-vírgulas.
Assim como acontece com as fórmulas de matriz, você pode usar constantes de matriz com qualquer função interna do Excel. As seções a seguir explicam como criar cada tipo de constante e como usar essas constantes com as funções do Excel.
Criar constantes unidimensionais e bidimensionais
Com o procedimento a seguir, você poderá praticar a criação de constantes horizontais, verticais e bidimensionais.Criar uma constante horizontal
- Use a pasta de trabalho da coluna anterior ou inicie uma nova pasta de trabalho.
- Selecione as células A1 a E1.
- Na barra de fórmulas, digite a fórmula a seguir e pressione CTRL+SHIFT+ENTER:
={1,2,3,4,5}
Observação Neste caso, você deve digitar as chaves de abertura e fechamento ({ }).
Você verá o seguinte resultado. Talvez você deseje saber por que não pode simplesmente digitar os números manualmente. Prossiga, pois a seção Usar constantes em fórmulas, posteriormente neste artigo, demonstra as vantagens do uso de constantes de matriz.
Criar um constante vertical
- Na pasta de trabalho, selecione uma coluna de cinco células.
- Na barra de fórmulas, digite a fórmula a seguir e pressione CTRL+SHIFT+ENTER:
={1;2;3;4;5}
Você verá o seguinte resultado. Criar uma constante bidimensional
- Na pasta de trabalho, selecione um bloco de células com quatro colunas largura por três linhas de altura.
- Na barra de fórmulas, digite a fórmula a seguir e pressione CTRL+SHIFT+ENTER:
={1,2,3,4;5,6,7,8;9,10,11,12}
Você verá o seguinte resultado: Usar constantes em fórmulas
Agora que você já está familiarizado com a inserção de constantes de matriz, veja a seguir um exemplo simples do que discutimos:- Abra uma planilha em branco.
- Copie a tabela a seguir começando pela célula A1. Use o botão Opções de Colagem exibido para fazer a correspondência da formatação de destino.
| 3 | 4 | 5 | 6 | 7 |
- Na célula A13, digite a fórmula a seguir e pressione CTRL+SHIFT+ENTER:
=SOMA(A1:E1*{1,2,3,4,5})
Observe que o Excel delimita a constante com outro conjunto de chaves, pois você a inseriu como uma fórmula de matriz. O valor 85 aparece na célula A3. A próxima seção explicará como a fórmula funciona.
Uma análise da sintaxe de constante de matriz
A fórmula que você acabou de usar contém várias partes. Função Matriz armazenada Operador Constante de matrizO último elemento dentro dos parênteses é a constante de matriz: {1,2,3,4,5}. Lembre-se de que o Excel não delimita as constantes de matriz com chaves. Você é que deve fazer isso. Lembre-se também de que, após adicionar uma fórmula de matriz, deve pressionar CTRL+SHIFT+ENTER para inserir a fórmula.
Como o Excel executa as operações entre parênteses primeiro, os próximos dois elementos a serem considerados são os valores armazenados na pasta de trabalho (A1:E1) e o operador. Neste ponto, a fórmula multiplica os valores da matriz armazenada pelos valores correspondentes da constante. É o equivalente de:
=SOMA(A1*1,B1*2,C1*3,D1*4,E1*5)
Finalmente, a função SOMA adiciona os valores e a soma 85 aparece na célula A3:
Para evitar o uso da matriz armazenada e manter a operação na memória, substitua a matriz armazenada por outra constante de matriz:
=SOMA({3,4,5,6,7}*{1,2,3,4,5})
Tente fazer isso. Copie a função, selecione uma célula em branco na pasta de trabalho, cole a fórmula na barra de fórmulas e pressione CTRL+SHIFT+ENTER. Você verá o mesmo resultado do exercício anterior que usou a fórmula de matriz =SOMA(A1:E1*{1,2,3,4,5}).
Elementos que você pode usar em constantes
As constantes de matriz podem conter números, texto, valores lógicos (como VERDADEIRO e FALSO) e valores de erro (como #N/D). É possível usar números nos formatos inteiro, decimal e científico. Se você incluir texto, deverá delimitá-lo com aspas duplas (").As constantes de matriz não podem obter matrizes, fórmulas ou funções adicionais. Em outras palavras, elas podem conter somente texto ou números separados por vírgulas ou ponto-e-vírgulas. O Excel exibe uma mensagem de aviso quando você insere uma fórmula como {1,2,A1:D4} ou {1,2,SOMA(Q2:Z8)}. Além disso, os valores numéricos não podem conter sinais de porcentagem, cifrões, vírgulas ou parênteses.
Nomeando constantes de matriz
Possivelmente, a melhor maneira de usar constantes de matriz é nomeando-as. As constantes nomeadas podem ser muito mais fáceis de usar, ocultando dos usuários iniciantes a complexidade das fórmulas de matriz. Para nomear uma constante de matriz e usá-la em uma fórmula, faça o seguinte:A caixa de diálogo Definir Nome é exibida.
- Na caixa Nome, digite Trimestre1.
- Na caixa Refere-se a, digite a seguinte constante (lembre-se de digitar as chaves manualmente):
={"Janeiro","Fevereiro","Março"}
O conteúdo da caixa de diálogo deve ter a seguinte aparência:
- Clique em OK.
- Na planilha, selecione uma linha de três células em branco.
- Digite a fórmula a seguir e pressione CTRL+SHIFT+ENTER.
=Trimestre1
Você verá o seguinte resultado. Ao usar uma constante nomeada como uma fórmula de matriz, lembre-se de digitar o sinal de igual. Se você não fizer isso, o Excel interpretará a matriz como uma cadeia de texto. Finalmente, tenha em mente que é possível usar combinações de texto e números.
Solucionando problemas de constantes de matriz
Procure os seguintes problemas quando as constantes de matriz não funcionarem:- Alguns elementos podem não ser separados com o caractere apropriado. Se você omitir uma vírgula ou ponto-e-vírgula, ou se você colocar um desses sinais no lugar errado, é possível que a constante de matriz não seja criada corretamente ou que você receba uma mensagem de aviso.
- Talvez você tenha selecionado um intervalo de células que não corresponde ao número de elementos da constante. Por exemplo, se você selecionar uma coluna de seis células a ser usada com uma constante de cinco células, o valor de erro #N/D aparecerá na célula vazia. Ao contrário, se você selecionar poucas células, o Excel omitirá os valores que não possuem uma célula correspondente.
Constantes de matriz em ação
Os exemplos a seguir demonstram algumas formas de aplicar constantes de matriz a fórmulas de matriz. Alguns exemplos usam a função TRANSPOR para converter linhas em colunas e vice-versa.Multiplicar cada item em uma matriz
- Selecione um bloco de células vazia com quatro colunas de largura por três linhas de altura.
- Digite a fórmula a seguir e pressione CTRL+SHIFT+ENTER.
={1,2,3,4;5,6,7,8;9,10,11,12}*2
Acomodar os itens em uma matriz
- Selecione um bloco de células vazia com quatro colunas de largura por três linhas de altura.
- Digite a fórmula de matriz a seguir e pressione CTRL+SHIFT+ENTER.
={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}
Se desejar, insira esta fórmula de matriz, que usa o operador circunflexo (^):
={1,2,3,4;5,6,7,8;9,10,11,12}^2
Transpor uma linha unidimensional
- Selecione uma coluna de cinco células em branco.
- Digite a fórmula a seguir e pressione CTRL+SHIFT+ENTER:
=TRANSPOR({1,2,3,4,5})
Mesmo que você tenha inserido uma constante de matriz horizontal, a função TRANSPOR converte a constante de matriz em uma coluna.Transpor uma coluna unidimensional
- Selecione uma linha de cinco células em branco.
- Digite a fórmula a seguir e pressione CTRL+SHIFT+ENTER:
=TRANSPOR({1;2;3;4;5})
Mesmo que você tenha inserido uma constante de matriz vertical, a função TRANSPOR converte a constante em uma linha.Transpor uma constante bidimensional
- Selecione um bloco de células com três colunas de largura por quatro linhas de altura.
- Digite a constante a seguir e pressione CTRL+SHIFT+ENTER.
=TRANSPOR({1,2,3,4;5,6,7,8;9,10,11,12})
A função TRANSPOR converte cada linha em uma série de colunas.Colocando as fórmulas de matriz básicas em funcionamento
Esta seção fornece exemplos de fórmulas de matriz básicas.Introdução
Use os dados desta seção para criar duas planilhas de exemplo.- Abra uma pasta de trabalho existente ou crie uma nova pasta de trabalho, e verifique se ela contém duas planilhas em branco.
- Copie os dados da tabela a seguir e cole-o na planilha iniciando pela célula A1.
| 400 | como pode | 1 | 2 | 3 | 4 | ||
| 1200 | um peixe vivo | 5 | 6 | 7 | 8 | ||
| 3200 | viver fora | 9 | 10 | 11 | 12 | ||
| 475 | de água fria | 13 | 14 | 15 | 16 | ||
| 500 | usuário avançado | ||||||
| 2000 | |||||||
| 600 | |||||||
| 1700 | |||||||
| 800 | |||||||
| 2700 | |||||||
- A planilha concluída deve ter a seguinte aparência.
- Nomeie a primeira planilha Dados e nomeie uma segunda planilha em branco Matrizes.
Criar matrizes e constantes de matriz a partir de valores existentes
O exemplo a seguir explica como usar fórmulas de matriz para criar vínculos entre intervalos de células em diferentes planilhas. Ele também mostra como criar uma constante de matriz a partir do mesmo conjunto de valores.Criar uma matriz a partir de valores existentes
- Na pasta de trabalho de exemplo, selecione a planilha Matrizes.
- Selecione o intervalo de célula C1 a E3.
- Digite a fórmula a seguir na barra de fórmulas e pressione CTRL+SHIFT+ENTER:
=Dados!E1:G3
Você verá o seguinte resultado. A fórmula se vincula aos valores armazenados nas células E1 a G3 na planilha Dados. A alternativa para esta fórmula de matriz com várias células é colocar uma fórmula exclusiva em cada célula da planilha Matrizes, conforme mostrado a seguir.
| =Dados!E1 | =Dados!F1 | =Dados!G1 |
| =Dados!E2 | =Dados!F2 | =Dados!G2 |
| =Dados!E3 | =Dados!F3 | =Dados!G3 |
Criar uma constante de matriz a partir de valores existentes
- Na planilha Matrizes, selecione as células C1 a E3.
- Pressione F2 para alternar para o modo de edição.
- Pressione F9 para converter as referências de célula em valores. O Excel converte os valores em uma constante de matriz.
- Pressione CTRL+SHIFT+ENTER para inserir a constante de matriz como uma fórmula de matriz.
={1,2,3;5,6,7;9,10,11}
O vínculo entre as planilhas Dados e Matrizes foi quebrado e a fórmula de matriz foi substituída por uma constante de matriz.
Contar caracteres em um intervalo de células
O exemplo a seguir mostra como contar o número de caracteres, incluindo espaços, em um intervalo de células.- Na planilha Dados, insira a seguinte fórmula na célula C7 e pressione CTRL+SHIFT+ENTER:
=SOMA(NÚM.CARACT(C1:C5))
O valor 47 aparece na célula C7.Neste caso, a função NÚM.CARACT retorna o tamanho de cada cadeia de texto em cada uma das células do intervalo. Em seguida, a função SOMA adiciona esses valores e exibe os resultado na célula que contém a fórmula, C7.
Localizar os n menores valores de um intervalo
Este exemplo mostra como localizar os três menores valores de um intervalo de células.- Na planilha Dados, selecione as células A12 a A14.
Este conjunto de células reterão os resultados retornados pela fórmula de matriz.
- Na barra de fórmulas, digite a fórmula a seguir e pressione CTRL+SHIFT+ENTER:
=MENOR(A1:A10,{1;2;3})
Os valores 400, 475 e 500 aparecem nas células A12 a A14, respectivamente.Esta fórmula usa uma constante de matriz para avaliar a função MENOR três vezes e retornar o primeiro menor (1), o segundo menor (2) e o terceiro menor (3) membros da matriz contida nas células A1:A10. Para localizar mais valores, adicione mais argumentos à constante e um número equivalente das células de resultado ao intervalo A12:A14. Você também pode usar funções com esta fórmula, como SOMA ou MÉDIA. Por exemplo:
=SOMA(MENOR(A1:A10,{1;2;3}))
=MÉDIA(MENOR(A1:A10,{1;2;3}))
Localizar os n maiores valores de um intervalo
Para localizar os maiores valores de um intervalo, substitua a função MENOR pela função MAIOR. Além disso, o exemplo a seguir usa as funções LIN e INDIRETO.- Na planilha Dados, selecione as células A12 a A14.
- Pressione DELETE para limpar a fórmula existente e deixar as células selecionadas.
- Na barra de fórmulas, digite esta fórmula e pressione CTRL+SHIFT+ENTER:
=MAIOR(A1:A10,LIN(INDIRETO("1:3")))
Os valores 3200, 2700 e 2000 aparecem nas células A12 a A14, respectivamente.Neste ponto, pode ser útil saber um pouco sobre as funções LIN e INDIRETO. Você pode usar a função LIN para criar uma matriz de números inteiros consecutivos. Por exemplo, selecione uma coluna vazia de 10 células na pasta de trabalho da prática, insira esta fórmula de matriz nas células A1:A10 e pressione CTRL+SHIFT+ENTER:
=LIN(1:10)
A fórmula cria uma coluna de 10 números inteiros consecutivos. Para ver um problema possível, 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 linha e a fórmula gera números inteiros de 2 a 11. Para resolver esse problema, adicione a função INDIRETO à fórmula:
=LIN(INDIRETO("1:10"))
A função INDIRETO usa cadeias de texto como argumentos (é por isso que o intervalo 1:10 é delimitado por aspas duplas). O Excel não ajusta os valores de texto quando você insere linhas ou move a fórmula de matriz. Conseqüentemente, a função LIN sempre gera a matriz de números inteiros desejados.
Vamos examinar a fórmula usada anteriormente — =MAIOR(A1:A10,LIN(INDIRETO("1:3"))) — começando pelos parênteses internos até chegar aos externos: a função INDIRETO retorna um conjunto de valores de texto; nesse caso, os valores de 1 a 3. A função LIN, por sua vez, gera uma matriz de coluna de três células. A função MAIOR usa os valores do intervalo de células A1:A10 e é avaliada três vezes, uma para cada referência retornada pela função LIN. Os valores 3200, 2700 e 2000 são retornados na matriz de coluna de três células. Se você deseja localizar mais valores, adicione um intervalo de células maior à função INDIRETO.
Finalmente, você pode usar esta fórmula com outras funções, como SOMA e MÉDIA.
Localizar a maior cadeia de texto de um intervalo de células
Este exemplo localiza a maior cadeia de texto de um intervalo de células. Esta fórmula funciona somente quando um intervalo de dados contém uma única coluna de células.- Na planilha Dados, limpe a fórmula existente da célula C7, insira a seguinte fórmula nessa célula e pressione CTRL+SHIFT+ENTER:
=INDEX(C1:C5,MATCH(MÁXIMO(NÚM.CARACT(C1:C5)),NÚM.CARACT(C1:C5),0),1)
O valor viver fora aparece na célula C7. Vamos examinar a fórmula, começando pelos elementos internos até chegar aos externos. A função NÚM.CARACT retorna o tamanho de cada item do intervalo de células C1:C5. A função MÁXIMO calcula o maior valor entre esses itens, que corresponde à maior cadeia de texto, que está na célula C3.
É aqui que as coisas ficam um pouco mais complicadas. A função MATCH calcula o deslocamento (a posição relativa) da célula que contém a maior cadeia de texto. Para fazer isso, são necessários três argumentos: um valor de pesquisa, uma matriz de pesquisa e um tipo de correspondência. A função MATCH procura o valor de pesquisa especificado na matriz de pesquisa. Nesse caso, o valor de pesquisa é a maior cadeia de texto:
(MÁXIMO(NÚM.CARACT(C1:C5))
e essa cadeia de caracteres reside nessa matriz:
NÚM.CARACT(C1:C5)
O argumento de tipo de correspondência é 0. O tipo de correspondência pode ser composto pelo valor 1, 0 ou -1. Se você especificar 1, MATCH retornará o maior valor menor ou igual ao valor de pesquisa. Se você especificar 0, MATCH retornará o primeiro valor exatamente igual ao valor de pesquisa. Se você especificar -1, MATCH localizará o menor valor maior ou igual ao valor de pesquisa especificado. Se você omitir um tipo de correspondência, o Excel assumirá 1.
Finalmente, a função INDEX adota estes argumentos: uma matriz e um número de linha e coluna dentro dessa matriz. O intervalo de células C1:C5 fornece a matriz, a função MATCH oferece o endereço da célula e o argumento final (1) especifica que o valor é proveniente da primeira coluna da matriz.
Para obter mais informações sobre as funções discutidas aqui, consulte a Ajuda do Excel.
Colocando as fórmulas de matriz avançadas em funcionamento
Esta seção fornece exemplos de fórmulas de matriz avançadas.Somar um intervalo que contém valores de erro
A função SOMA do Excel não funciona quando você tenta somar uma intervalo que contém um valor de erro, como #N/D. Este exemplo mostra como somar os valores em um intervalo chamado Dados que contém erros.=SOMA(SE(ÉERROS(Dados),"",Dados))
A fórmula cria uma nova matriz que contém os valores originais menos quaisquer valores de erro. Começando pelas funções internas até chegar às externas, a função ÉERROS procura erros no intervalo de células (Dados). A função SE retornará um valor específico se uma condição especificada for avaliada como VERDADEIRO e outro valor se for avaliada como FALSO. Nesse caso, ela retornará cadeias de caracteres vazias ("") para todos os valores de erro porque são avaliadas como VERDADEIRO e retornará os valores restantes do intervalo (Dados) porque são avaliadas como FALSO. Isso significa que elas não contém valores de erro. Em seguida, a função SOMA calcula o total da matriz filtrada.
Contar o número de valores de erro em um intervalo
Este exemplo é similar à fórmula anterior, mas retorna o número de valores de erro em um intervalo chamado Dados, em vez de filtrá-los:=SOMA(SE(ÉERROS(Dados),1,0))
Esta fórmula cria uma matriz que contém o valor 1 para as células que contém erros e o valor 0 para as células que não contém erros. Você pode simplificar a fórmula e obter o mesmo resultado removendo o terceiro argumento da função SE:
=SOMA(SE(ÉERROS(Dados),1))
Se você não especificar o argumento, a função SE retornará FALSO se uma célula não contiver um valor de erro. Você pode simplificar ainda mais a fórmula:
=SOMA(SE(ÉERROS(Dados)*1))
Esta versão funciona porque VERDADEIRO*1=1 e FALSO*1=0.
Somar valores com base em condições
Talvez você precise somar valores com base em condições. Por exemplo, esta fórmula de matriz soma apenas os números inteiros positivos em um intervalo chamado Vendas:=SOMA(SE(Vendas>0,Vendas))
A função SE cria uma matriz de valores positivos e falsos. A função SOMA ignora essencialmente os valores falsos porque 0+0=0. O intervalo de células usado nesta fórmula pode ser composto por qualquer número de linhas e colunas.
Você também pode somar valores que atendem a 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 mente que esta fórmula retornará um erro se o intervalo contiver uma ou mais células não-numéricas.
Você também pode criar fórmulas de matriz que usam um tipo de condição OU. Por exemplo, você pode somar valores menores que 5 e maiores que 15:
=SOMA(SE((Vendas<5)+(Vendas>15),Vendas))
A função SE localiza todos os valores menores que 5 e maiores que 15, e passa esses valores para a função SOMA.
Importante Você não pode usar diretamente as funções E e OU nas fórmulas de matriz porque essa funções retornam um único resultado, VERDADEIRO ou FALSO, e as funções de matriz requerem matrizes de resultados. Você pode solucionar o problema usando a lógica mostrada na fórmula anterior. Em outras palavras, você executa operações matemáticas, como adição ou multiplicação, em valores que atendem à condição OU e E.
Calcular uma média que exclua zeros
Este exemplo mostra como remover zeros de um intervalo quando é necessário calcular a média dos valores nesse intervalo. A fórmula usa um intervalo de dados chamado Vendas:=MÉDIA(SE(Vendas<>0,Vendas))
A função SE cria uma matriz de valores que não são iguais a 0 e passa esses valores para a função MÉDIA.
Contar o número de diferenças entre dois intervalos de células
Esta fórmula de matriz compara os valores em dois intervalos de células chamados MeusDados e SeusDados, e retorna o número de diferenças entre os dois. Se o conteúdo dos dois intervalos forem idênticos, a fórmula retornará 0. Para usar essa fórmula, os intervalos de células devem ser de mesmo tamanho e mesma dimensão:=SOMA(SE(MeusDados=SeusDados,0,1))
A fórmula cria uma nova matriz do mesmo tamanho dos intervalos que você está comparando. A função SE preenche a matriz com o valor 0 e o valor 1 (0 para diferenças e 1 para células idênticas). Em seguida, a função SOMA retorna a soma dos valores da matriz.
Você pode simplificar a fórmula da seguinte forma:
=SOMA(1*(MeusDados<>SeusDados))
Assim como a fórmula que conta valores de erro em um intervalo, esta fórmula funciona porque VERDADEIRO*1=1 e FALSO*1=0.
Encontrar o local do valor máximo de um intervalo
Esta fórmula de matriz retorna o número de linha do valor máximo em um intervalo de coluna única chamado Dados:=MÍNIMO(SE(Dados=MÁXIMO(Dados),LIN(Dados),""))
A função SE cria uma nova matriz que corresponde ao intervalo Dados. Se uma célula correspondente contiver o valor máximo do intervalo, a matriz conterá o número de linha. Do contrário, a matriz conterá uma cadeia de caracteres vazia (""). A função MÍNIMO usa a nova matriz como segundo argumento e retorna o menor valor, que corresponde ao número de linha do valor máximo em Dados. Se o intervalo Dados contiver valores máximos idênticos, a fórmula retornará retornará a linha do primeiro valor.
Se você deseja retornar o endereço de célula real de um valor máximo, use esta fórmula:
=ENDEREÇO(MÍNIMO(SE(Dados=MÁXIMO(Dados),LIN(Dados),"")),COLUNA(Dados))
Fonte: http://office.microsoft.com/pt-br/excel-help/diretrizes-e-exemplos-de-formulas-de-matriz-HA010228458.aspx
Nenhum comentário:
Postar um comentário