Como usar ARRAYFORMULA no Google Sheets (exemplos de fórmulas de matriz)

Como o nome sugere, ARRAYFORMULA transforma uma fórmula comum em uma fórmula de matriz no Google Sheets. Com ela, você pode adicionar fórmulas que se expandem automaticamente; Elas são aplicadas à medida que novos dados são introduzidos na planilha. Isso significa que você não precisará copiar e colar fórmulas manualmente à medida que novas linhas de dados aparecem.

Em um nível fundamental, ele obtém os valores de um intervalo de células e os gera em várias linhas e colunas. Isso significa que ele permite que você usar matrizes em fórmulas que normalmente não as aceitam, incluindo SUMIF, VLOOKUP ou FILTER.

Este guia explica como a ARRAYFORMULA funciona, por que usá-la, nove exemplos de fórmulas de matriz (incluindo IF, SUMIF, VLOOKUP e FILTER) e os erros que você tem mais chance de encontrar.

O que é a ARRAYFORMULA?

A ARRAYFORMULA é uma função de matriz expansível. Isso significa que você só precisa inserir a função em uma célula e, em seguida, a função é aplicada automaticamente às linhas subsequentes em toda a extensão de uma coluna inteira.

Como resultado, você pode retornar vários valores de um intervalo de células com apenas uma ARRAYFORMULA, em vez de retornar um único valor de uma fórmula que não seja de matriz.

Vamos dar uma olhada na sintaxe para entender como ela funciona e como combiná-la com outras funções.

Sintaxe

A sintaxe da ARRAYFORMULA é a seguinte:

fórmula da matriz - esse parâmetro pode ser:

• uma gama
• uma expressão matemática que usa um intervalo de células ou vários intervalos do mesmo tamanho
• uma função que retorna um resultado maior que uma célula

Também é possível adicionar a FÓRMULA ARRAY a uma função que não seja de matriz existente em sua planilha. Basta pressionar o atalho de teclado Ctrl/Cmd + Shift + Enter, e o Planilhas Google envolverá automaticamente a ARRAYFORMULA() em sua fórmula.

Como você pode ver, a sintaxe da ARRAYFORMULA é mais simples do que o esperado. Vamos explorar mais benefícios dessa função.

Por que usar a ARRAYFORMULA no Planilhas Google?

Além de economizar tempo e esforço manual ao oferecer uma alternativa aos cálculos manuais, o ARRAYFORMULA também oferece outros benefícios.

Vamos dar uma olhada em apenas alguns deles:

• Cálculos de tamanho de lote: Em vez de ter que usar funções semelhantes que calculam valores individualmente, você pode usar uma única fórmula para todo o seu conjunto de dados, independentemente do tamanho. Isso evita o risco de planilhas pesadas que contêm várias fórmulas em um único cálculo.
• Flexibilidade: Uma única alteração ou modificação em qualquer célula afetará automaticamente o valor do cálculo da ARRAYFORMULA. Isso significa que você pode editar facilmente os valores das células, e quaisquer valores que dependam dessas células serão automaticamente ajustados de acordo.
• Dinâmico: Quando você adicionar uma nova linha no intervalo de células, a ARRAYFORMULA será aplicada automaticamente a ela, de modo que não será necessário inserir manualmente outra função.

Embora o Planilhas Google possa oferecer o “Preenchimento automático sugerido” para aplicar a função ao restante de suas linhas, o preenchimento automático não poderá oferecer os mesmos benefícios mencionados acima. Por exemplo, se você alterar os valores, há o risco de que o preenchimento automático não consiga atualizar todos os dados da célula corretamente. Isso o deixaria com informações desatualizadas e imprecisas.

Agora que você entende os muitos benefícios de usar a ARRAYFORMULA em cálculos de massa, vamos explorar como usar a ARRAYFORMULA do Google Sheets nos exemplos a seguir.

Como usar a ARRAYFORMULA do Google Sheets

Digamos que eu seja um professor e tenha um conjunto de dados com uma lista de alunos e suas notas em diferentes projetos. Quero calcular a pontuação média de cada aluno em todos os projetos.

Normalmente, eu somaria os valores das células individualmente, como mostrado abaixo:

No entanto, esse método logo se tornaria problemático em duas circunstâncias recorrentes:

• Aumento de dados e fórmulas: Se eu decidir aumentar o tamanho dos dados ou usar mais fórmulas, minha planilha não funcionará tão bem.
• Atualizações regulares: Qualquer modificação em meus dados significa ter que alterar todas as fórmulas usadas neles.

Usando a ARRAYFORMULA, posso somar rapidamente os resultados de todos os alunos, independentemente do tamanho do conjunto de dados ou do número de fórmulas usadas. Posso adicionar, remover ou editar o conteúdo conforme desejar, e a fórmula será aplicada aos dados automaticamente.

Exemplo 1: FÓRMULA ARRAY básica

Com base no exemplo anterior, para calcular os totais, você pode usar a ARRAYFORMULA, conforme mostrado abaixo. Observe que agora estou adicionando os intervalos, enquanto antes eu adicionava células individuais.

Essa nova fórmula deve ser semelhante a esta:

Para que a ARRAYFORMULA funcione corretamente, certifique-se de que todas as células abaixo dela estejam vazias.

Observe: Ao terminar o intervalo de cada coluna com a letra (D2:D), você pode adicionar a coluna inteira como a matriz. Isso significa que, se você adicionar novas linhas de dados nas colunas, a função ARRAYFORMULA será aplicada automaticamente.

Como você pode ver, usar a fórmula básica é muito simples.

Exemplo 2: IF com ARRAYFORMULA

Você provavelmente notou uma sequência de zeros no final da coluna após aplicar a fórmula do exemplo anterior. Você pode evitar isso adicionando uma função IF à fórmula.

Essa instrução IF informa à fórmula para deixar a coluna ‘Total’ em branco se não houver nenhum nome na coluna A.

Agora há apenas células em branco, sem zeros, após a última linha de dados na coluna A. Vamos incluir uma linha para outro aluno após a linha 50 para testar sua natureza expansível.

Assim que você começar a digitar o conteúdo, a última coluna incluirá automaticamente um zero. Esse é um benefício significativo do uso da ARRAYFORMULA. Ao trabalhar com um conjunto de dados que muda com frequência, a ARRAYFORMULA permite que você faça alterações sem ajustar constantemente a planilha.

Exemplo 3: SUMIF com ARRAYFORMULA

Digamos que eu tenha agrupado os alunos em 3 grupos diferentes e queira encontrar o total do grupo no ‘Projeto D’. Posso usar a ARRAYFORMULA em conjunto com a função SUMIF para obter o total desses 3 grupos.

A sintaxe para SUMIF e ARRAYFORMULA é:

Como você pode ver, agora tenho o total de pontos por grupo para o Projeto D, conforme mostrado na coluna J. Posso usar esse novo resultado para calcular qualquer outra métrica de avaliação de forma rápida e eficiente.

Como antes, aparecem zeros depois da última célula com conteúdo na coluna 'Total'. Para ocultá-los, envolva a SUMIF no mesmo teste IF usado no exemplo 2:

=ARRAYFORMULA(IF(I2:I<>"", SUMIF(B2:B, I2:I, H2:H), ""))

Para obter mais informações sobre como a função SUMIF funciona, dê uma olhada nesta postagem do blog sobre como usar SUMIF no Google Sheets.

Exemplo 4: VLOOKUP com ARRAYFORMULA

Digamos que eu queira procurar dados de desempenho específicos de um aluno específico. Normalmente, eu poderia usar a função VLOOKUP para fazer isso. No entanto, eu teria de inserir a função várias vezes para cada critério, pois a função VLOOKUP retorna apenas um único valor. No entanto, ao combinar essa função com a ARRAYFORMULA, posso encontrar vários valores ao mesmo tempo.

A sintaxe para VLOOKUP com ARRAYFORMULA é:

Neste exemplo, quero encontrar o nome, o sobrenome e os resultados do projeto A e B do aluno número 10. Com ‘ID do aluno’ como chave, eu usaria a seguinte fórmula:

Para procurar várias chaves de uma vez, passe para a VLOOKUP (PROCV) um intervalo de chaves de pesquisa em vez de uma única célula. Com os IDs de aluno que você quer em K2:K4, esta fórmula retorna uma linha de resultados para cada ID:

=ARRAYFORMULA(VLOOKUP(K2:K4, $A$1:$I$52, {1,3,4,5,6}, FALSE))

Para obter mais informações sobre como a função VLOOKUP funciona, consulte este artigo em Como a função VLOOKUP funciona no Planilhas Google.

Exemplo 5: FILTRO com ARRAYFORMULA

Um dos usos mais comuns da combinação de FILTER e ARRAYFORMULA é a identificação de duplicatas. Para que isso funcione, você precisa incorporar duas fórmulas básicas: COUNTIF e UNIQUE.

Digamos que eu queira encontrar conteúdo duplicado para sobrenomes de alunos para procurar possíveis parentes. Eu precisaria digitar a seguinte fórmula:

Como você pode ver, ‘Dimmer’ apareceu na célula. Isso significa que a fórmula encontrou mais de uma célula na coluna ‘Last Name’ contendo o nome Dimmer. Isso significa que tenho dois alunos com esse mesmo sobrenome.

Você também pode usar essa função para ajudar a localizar e remover dados duplicados. Para saber mais sobre como essas funções funcionam individualmente, leia estas postagens do blog sobre o ÚNICO, CONT.SE e FILTRO funções.

Exemplo 6: Matrizes horizontais

Até agora, todos os exemplos foram aplicados a matrizes verticais. A ARRAYFORMULA pode ser usada com a mesma facilidade para matrizes horizontais, o que permite que você a use independentemente da estrutura dos dados.

Vamos calcular o ‘Total’ de todos os projetos para cada aluno, mas em um formato horizontal. Posso fazer isso inserindo a seguinte fórmula:

Em vez de dizer à função para percorrer a lista (por exemplo, de A2:A, o que significa começar em A2 e descer pela coluna A), eu disse a ela para percorrer os dados (por exemplo, de B5:5, o que significa começar em B5 e percorrer a linha 5).

Ao fazer essa simples alteração, a ARRAYFORMULA agora funciona horizontalmente. E, assim como no exemplo anterior, posso adicionar uma instrução IF para me livrar dos zeros nas colunas em branco:

Exemplo 7: Matrizes multidimensionais

Você já viu a ARRAYFORMULA ser usada nas orientações vertical e horizontal. Mas e quanto a uma tabela que inclui esses dois tipos de matriz? Você também pode usar a ARRAYFORMULA para essas tabelas!

Aqui, criei uma tabela de multiplicação que multiplica a coluna A pela linha 1, simplesmente digitando o seguinte:

Exemplo 8: Usando ARRAYFORMULA para combinar colunas

A ARRAYFORMULA não apenas ajuda a calcular dados numéricos, mas também pode ajudar a combinar texto de diferentes colunas.

Digamos que eu queira combinar as colunas ‘Nome’ e ‘Sobrenome’ para criar uma coluna ‘Nome completo’. Inseri uma coluna para ‘Nome completo’ para incluir a seguinte fórmula:

Observe: Essa fórmula só funcionará se as duas matrizes tiverem o mesmo tamanho (por exemplo.‘C2:C52&" "&D2:D52’). Caso contrário, o Planilhas Google não conseguirá realizar o cálculo.

Como criar uma fórmula de matriz passo a passo

Os exemplos acima mostram fórmulas prontas. Este cria uma fórmula de matriz a partir da primeira célula e adiciona a ARRAYFORMULA no final com o atalho de teclado. A tabela lista os agentes de vendas e o valor que cada um vendeu no mês. Se o valor for maior ou igual a US$ 4000, o agente recebe um bônus de 5%. Quero calcular e retornar o valor do bônus para vendas iguais ou maiores que US$ 4000, e deixar a célula em branco se o valor for menor que US$ 4000.

1. Abra no Google Sheets a planilha com seus dados.

2. Na primeira linha da coluna onde você quer os resultados, digite a Função IF.

3. Adicione a condição: o valor da coluna B deve ser de US$ 4000 ou mais (B2:B6>=4000).

4. Depois da vírgula, adicione o cálculo que você quer retornar se a condição for TRUE.

5. Adicione outra vírgula e, em seguida, o valor que você quer retornar se a condição for FALSE.

6. Feche o parêntese e use o atalho da ARRAYFORMULA para adicionar a função: pressione Ctrl + Shift + Enter no Windows ou Cmd + Shift + Enter no Mac.

7. Pressione Enter para ver a matriz resultante.

A fórmula pronta é =ARRAYFORMULA(IF(B2:B6>=4000,B2:B6*0.05,"")). Bob, Janet e Sarah recebem o bônus de 5%, e as células de Jane e Tim ficam em branco.

Como adicionar o nome de uma coluna mesmo que haja uma ARRAYFORMULA nela

Às vezes, pode ser necessário adicionar um nome de coluna acima dos dados processados por uma função ARRAYFORMULA. Nomear as colunas corretamente é fundamental para a clareza e a colaboração. Felizmente, você pode fazer isso sem alterar a estrutura de dados existente.

Por exemplo, suponha que você tenha duas listas de valores numéricos nas colunas A e B e queira somá-las, incluindo um nome de coluna. Para fazer isso, você pode usar a seguinte fórmula:

Há algumas coisas que você deve ter em mente ao fazer isso:

• Certifique-se de deslocar os intervalos de células em ARRAYFORMULA para a linha abaixo. Caso contrário, ele tentará aplicar a fórmula aos cabeçalhos das colunas.
• Os colchetes mantêm matrizes dentro deles; os itens separados por ponto e vírgula serão expandidos verticalmente. Neste exemplo, se você usar uma vírgula em vez de um ponto e vírgula, a fórmula será interrompida.

Erros comuns da ARRAYFORMULA e como corrigi-los

Quando uma fórmula de matriz para de funcionar, a causa costuma ser uma destas.

• #REF! O resultado da matriz não foi expandido porque substituiria dados: uma célula do intervalo de saída já contém um valor. Limpe as células abaixo ou ao lado da fórmula e ela volta a se expandir.
• Os argumentos de matriz para ADD têm tamanhos diferentes: os intervalos da fórmula cobrem linhas diferentes, como A2:A10 + B2:B11. Faça todos os intervalos começarem e terminarem nas mesmas linhas.
• Um único resultado em vez de uma coluna: as funções que resumem um intervalo, como SUM, AND e OR, retornam um único valor mesmo dentro da ARRAYFORMULA. Para um teste linha a linha, use * no lugar de AND e + no lugar de OR: =ARRAYFORMULA(IF((B2:B>=4000)*(C2:C="Yes"), "Bonus", ""))
• #N/A em algumas linhas: uma pesquisa que não encontra correspondência retorna #N/A nessa linha. Envolva a pesquisa com IFERROR para mostrar uma célula em branco: =ARRAYFORMULA(IFERROR(VLOOKUP(K2:K4, $A$1:$I$52, {1,3,4,5,6}, FALSE), ""))
• Uma planilha lenta: intervalos abertos como D2:D cobrem todas as linhas da página. Em arquivos grandes, termine o intervalo na última linha de que você precisa ou ignore as linhas em branco com IF, como no exemplo 2.

Como faço para criar uma ARRAYFORMULA no Planilhas Google?

E aí está! A ARRAYFORMULA é uma ferramenta extremamente poderosa e versátil que pode ajudar a simplificar significativamente os dados da sua planilha.

Use-a para retornar vários valores com apenas uma única função. Combine-a também com outras funções, como IF, VLOOKUP e SUMIF, para criar fórmulas avançadas.

Deseja restringir o resultado de uma matriz a um tamanho específico? Saiba mais Como usar a fórmula ARRAY_CONSTRAIN no Planilhas Google.