Como usar a ARRAYFORMULA do Google Sheets

Como o próprio nome sugere, FÓRMULA ARRAY é uma função de matriz. Ela permite que você 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.

Vamos explorar como a ARRAYFORMULA funciona e por que você precisaria dela em sua vida.

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, os zeros aparecem sequenciados após a última célula de conteúdo na coluna ‘Total’. Vamos dar uma olhada em como a combinação de SUMIFS e ARRAYFORMULA pode ajudar a corrigir isso rapidamente, como fizemos com a instrução IF.

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

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:

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, CONSELHO 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 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.

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.