No ano retrasado as Planilhas Google receberam 10 novas funções, incluído funções lambda e as funções nomeadas. Com todas essas novas funções já conseguimos fazer inúmeras dinâmicas diferentes dentro de nossa planilha para diversas situações.
Esse novo anúncio do Google que ocorreu recentemente, anuncia um novo grupo de novas funções para as Planilhas Google. Incluindo neste novo lote de funções estão a tão esperada função LET, 8 novas funções de manipulação de array, uma nova função estatística e uma nova função datetime.
Nesse post veremos um pouco mais sobre essas funções e como aplica-las em sua planilha. Estou ansioso para ver o que você achou dessas funções.
1. Função EPOCHTODATE
A primeira função que vamos ver é a EPOCHTODATE, ela converte um carimbo de data e hora de época Unix em uma data e hora regular (Fuso Horário Coordenado Universal, UTC).
O tempo da época Unix corresponde ao número de segundos desde a meia-noite de 01/01/1970. Esta data é considerada como o início dos tempos (na realidade é a data aproximada da criação do projeto que daria origem ao Unix).
Então teríamos aproximadamente a seguinte correlação:
- 01/01/1970 00:00:00 UTC -> 0
- 01/01/1970 00:01:00 UTC -> 60
- 01/01/1970 01:00:00 UTC -> 3.600
- 27/07/2023 15:20:37 UTC -> 1.690.471.237
A função EPOCHTODATE basicamente recebe dois argumentos em sua sintaxe:
EPOCHTODATE(indicação_de_tempo; [unidade])
Onde:
- indicação_de_tempo Registro de data e hora da época do Unix, é uma indicação de tempo em segundos, milissegundos ou microssegundos.
- [unidade] (opcional) é a unidade de tempo em que a indicação de tempo é expressa. 1 indica que a unidade de tempo é segundos. 2 indica que a unidade de tempo é milissegundos. 3 indica que a unidade de tempo é microssegundos.
Um carimbo de data/hora do Unix se parece com o que vimos acima, uma numeração de segundos contados a partir de determinada data.
Aplicando essa função em uma célula que contenha um carimbo de data/hora do Unix ele retornara essa DateTime.
O DateTime convertido (carimbo de data/hora) estará em UTC. Mas você pode obter a hora local adicionando um deslocamento de fuso horário.
2. Função MARGINOFERROR
A função MARGINOFERROR calcula a margem de erro de um intervalo, isso com base em um intervalo de valores e em um nível de confiança. Uma função muito interessante para se utilizar dentro de nossa Planilha Google.
Em sua sintaxe devemos informar o conjunto de dados representado por “intervalo” e o “nível de confiança” desse conjunto:
MARGINOFERROR(intervalo, nível_de_confiança)
Onde:
- intervalo é o intervalo de valores a ser usado para calcular a margem de erro.
- nível_de_confiança é um valor entre 0 e 1 que representa o nível de confiança desejado.
Por exemplo, um nível de confiança de 0,95 indica que há 95% de probabilidade de que o intervalo de valores real esteja dentro da margem de erro. Para exemplificar aqui vamos utilizar o seguinte conjunto de dados, com nomes e valores:
Utilizando esses dados vamos criar uma margem de erro ou um nível de confiança para esses valores. Para isso na célula B10 iremos aplicar a função MARGINOFERROR com o intervalo de B2 à B9 e usando um nível de confiança de 99%.
Para isso vamos aplica-la da seguinte forma:
=MARGINOFERROR(B2:B9; 0,99)
Veja que ao aplica-la obtemos o resultado de R$ 954,23 que em tese seria o nível de confiança dessa base. Claro, considerando esses valores e o nível em 99%:
Bom, você deve pensar que talvez isso muito complexo de entender e não sabe o que realmente isso quer dizer. Para você entender melhor, a margem de erro é uma estatística que expressa a quantidade de erro amostral aleatório nos resultados.
Isto é, a margem de erro é a medida do raio ou a metade da largura do intervalo de confiança para a mesma estatística.
Ainda assim acredito que seja mais fácil de você entender aplicando o mesmo principio em um gráfico. Com essa mesma dinamica, mostrando a margem das barras de erro (1 desvio padrão) e o valor médio (linha vermelha):
Para entender mais sobre essa função e ver na prática, eu gravei uma aula sobre essa função como você leu aqui e pode complementar seu conhecimento. Dê uma estudada nesta aula abaixo e compreenda melhor a aplicação:
Como vimos, a margem de erro é uma medida de estatística. Ela é usada principalmente para determinar a quantidade de erros causados por acaso em uma amostra aleatória de uma população.
Uma estimativa do parâmetro de uma determinada amostra pode não representar o parâmetro de toda a população, conhecida como o calculo da “Margem de Erro”.
Para saber mais sobre essa função veja a aula acima e leia mais sobre ela na própria ajuda da Google acessando: Função MARGINOFERROR
3. Função TOROW
A nova função TOROW transforma um intervalo de dados de linhas e colunas em uma única linha. Podendo ser usada para criar um resumo de vendas por exemplo, usando a função SOMA para calcular o total de vendas por produto…
A sintaxe da função TOROW é a seguinte:
TOROW(intervalo)
Onde:
- intervalo é o intervalo de células a ser transformado.
Esta função transforma por exemplo, pode pegar a nossa matriz de entrada em A1:C2 e transformar em uma única linha:
Neste exemplo onde temos três colunas e duas linha A1:C2 podemos aplicar na célula A4 a seguinte função:
=TOROW(A1:C2)
Ela tem argumentos opcionais que podem determinar como lidar com células em branco ou até valores de erro. Também se deve varrer/percorrer colunas ou linhas ao varrer/percorrer o intervalo de entrada.
4. Função TOCOL
Irmã da função TOROW, a função TOCOL tem sua sintaxe semelhante a sua irmã. A diferença é que ao invés de transformar um intervalo de dados em uma única linha ela coloca esses dados alinhados em uma única coluna. Ela se comporta da mesma forma que a função FLATTEN com alguns argumentos a mais em sua sintaxe.
A sintaxe da função TOCOL é a seguinte:
TOCOL(matriz_ou_intervalo; [ignorar]; [analisar_por_coluna])
Onde:
- matriz_ou_intervalo é a matriz ou o intervalo de células a ser transformado.
- [ignorar] (opcional) é um número que especifica quais valores devem ser ignorados na transformação.
- [analisar_por_coluna] (opcional) é um valor booleano que especifica se a análise deve ser feita por coluna ou por linha. O padrão é VERDADEIRO, o que significa que a análise é feita por coluna.
Aplicando essa função na célula A4, utilizando apenas a matriz_ou_intervalo, ela transpõe cada linha em um formato de coluna e as coloca uma sobre a outra. A função utiliza é:
=TOCOL(A1:C2)
Dessa forma obtemos o seguinte resultado:
Ela também tem argumentos opcionais que podem determinar como lidar com células em branco ou até valores de erro. Também se deve varrer/percorrer colunas ou linhas ao varrer/percorrer o intervalo de entrada.
Nesta aula explico mais sobre essas duas novas funções. Essas duas novas funções trabalham com matrizes em uma Planilha do Google trazendo o resultado em uma única linha e em uma única coluna respectivamente.
5. CHOOSEROWS
A função CHOOSEROWS retorna um número especificado de linhas de uma matriz. Ela é usada para extrair um intervalo de linhas de uma matriz, ou para copiar ou mover um intervalo de linhas de uma matriz para outra.
Sua sintaxe é a seguinte:
CHOOSEROWS(array, row_num1, [row_num2])
Onde:
- array é a matriz de onde as linhas serão extraídas.
- row_num1 é a linha que deve ser extraída.
- [row_num2] (opcional) são as demais linhas que devem ser extraídas.
Por exemplo, a seguinte fórmula retornará as linhas 1 e 3 da matriz A1:C4:
=CHOOSEROWS(A1:C4;{1\3})
Note nesse caso que eu adicionei entre chaves as linhas que eu gostaria de trazer com a função CHOOSEROWS. Caso seja apenas uma linha não precisa utilizar esse método, colocando apenas o numeral após o ponto e virgula.
6. CHOOSECOLS
Semelhante a sua “irmã” que retorna as linhas desejadas, a função CHOOSECOLS seleciona as colunas, retorna um número especificado de colunas de uma matriz. Ela é usada para extrair um intervalo de colunas de uma matriz, ou para copiar ou mover um intervalo de colunas de uma matriz para outra.
Permitindo assim selecionar colunas específicas de um intervalo, o que anteriormente exigiria uma função QUERY ou trabalhar com matrizes mais complexas. O que era mais complicado porque você usava referências de letras para as colunas no caso da função QUERY.
A sintaxe da função CHOOSECOLS é a seguinte:
CHOOSECOLS(matriz; [col_num1; …])
Onde:
- array é a matriz de onde as colunas serão extraídas.
- [col_num1; …] são as colunas que devem ser extraídas.
Por exemplo, a seguinte fórmula retornará as colunas 1 e 3 da matriz A1:C4.
=CHOOSECOLS(A1:C4;{1\3})
Como na função anterior usamos os números das colunas entre as chaves. Caso seja apenas uma linha, também não é preciso utilizar esse método, colocando apenas o numeral após o ponto e virgula.
7. WRAPROWS
A função WRAPROWS do Google Planilhas agrupa linhas ou colunas de células fornecidas com base em um número especificado de elementos para formar uma nova matriz.
A sintaxe da função WRAPROWS é a seguinte:
WRAPROWS(intervalo, contagem_de_agrupamentos, [preenchimento_com])
Onde:
- intervalo é o intervalo de células a ser agrupado.
- contagem_de_agrupamentos é o número máximo de células em cada linha.
- [preenchimento_com] (opcional) é o valor usado para preencher as células extras no intervalo.
No exemplo abaixo que utilizaremos, a função WRAPROWS envolve a primeira linha em várias linhas com um máximo de dois elementos em cada linha. No caso, cada linha terá o máximo de duas colunas.
Observe o segundo ponto e vírgula na função, ela quem define o valor “hífen” como em branco, o que significa que a célula B5 está em branco.
=WRAPROWS(A1:E1;2;"-")
8. WRAPCOLS
A função WRAPCOLS do Google Planilhas agrupa linhas ou colunas de células fornecidas com base em um número especificado de elementos para formar uma nova matriz.
A sintaxe da função WRAPCOLS é a seguinte:
WRAPCOLS(intervalo, contagem_de_agrupamentos, [preenchimento_com])
Onde:
- intervalo é o intervalo de células a ser agrupado.
- contagem_de_agrupamentos é o número máximo de células em cada coluna.
- [preenchimento_com] (opcional) é o valor usado para preencher as células extras no intervalo.
Usando a mesma base do exemplo anterior, podemos observar abaixo que utilizaremos praticamente a mesma sintaxe. A função WRAPCOLS envolve a primeira linha em várias colunas com um máximo de dois elementos em cada coluna.
No caso, cada coluna terá o máximo de duas linhas. Observe o segundo ponto e vírgula na função, ela quem define o valor “hífen” como em branco, o que significa que a célula C4 está em branco.
=WRAPCOLS(A1:E1;2;"-")
Se quiser aprender mais sobre o método utilizado, nesta aula abaixo vamos aprender sobre as duas funções WRAPROWS & WRAPCOLS.
9. HSTACK
A função HSTACK do Google Planilhas anexa intervalos horizontalmente e em sequência para retornar uma matriz maior. Pode ser usada para organizar dados, criar tabelas ou relatórios mais fáceis de ler, ou mesmo para criar efeitos visuais interessantes. Uma ferramenta versátil que pode ser usada para uma variedade de propósitos.
A sintaxe da função HSTACK é a seguinte:
HSTACK(intervalo1; [intervalo2, …])
Onde:
- intervalo1 é o primeiro intervalo a ser anexado.
- [intervalo2, …] (opcional) são outros intervalos a serem adicionados em intervalo1.
No exemplo abaixo vamos supor que eu queira apenas extrair os valores da base A1:G4. Com a função HSTACK é possível selecionarmos as colunas facilmente:
=HSTACK(A1:B4;D1:D4;F1:F4)
10. VSTACK
A função VSTACK como sua “irmã” que anexa intervalos horizontalmente e em sequência para retornar uma matriz maior. Ela anexa intervalos verticalmente e em sequência para retornar uma matriz maior.
Pode ser usada para organizar dados, criar tabelas ou relatórios mais fáceis de ler, ou mesmo para criar efeitos visuais interessantes. Se os intervalos a serem anexados não tiverem o mesmo número de linhas, as linhas extras serão preenchidas com valores nulos. Além de poder ser usada com intervalos de qualquer tamanho.
A sintaxe da função VSTACK é a seguinte:
VSTACK(intervalo1; [intervalo2, …])
Onde:
- intervalo1 é o primeiro intervalo a ser anexado.
- intervalo2, … (opcional) são outros intervalos a serem adicionados em intervalo1.
Ele pega os dados de minha base no intervalo A1:B6 e acrescenta os dados de D2:E4 abaixo, para que você tenha tudo em uma única base:
=VSTACK(A1:B6;D2:E4)
Para aprender mais sobre as duas funções você pode dar uma conferida na aula completa abaixo:
11. LET
A função LET do Planilhas Google permite que você defina variáveis nomeadas dentro de uma fórmula. Isso pode ser útil para tornar suas fórmulas mais claras e concisas, ou para reutilizar resultados em diferentes partes da fórmula.
Uma uma ferramenta poderosa que pode ser usada para melhorar a eficiência e a clareza de suas fórmulas. Podendo ser usada para calcular a média de um conjunto de números, usando uma variável para armazenar o número de valores no conjunto.
A sintaxe da função LET é a seguinte:
LET(nome1, valor_da_expressão1, [nome2, valor_da_expressão2, …], expressão_da_fórmula)
Onde:
- nome1 é o nome da primeira variável a ser definida.
- valor_da_expressão1 é a expressão que avalia para o valor da primeira variável.
- [nome2, valor_da_expressão2, …] (opcional) são outros nomes de variáveis a serem definidas e suas expressões.
- expressão_da_fórmula é a expressão que usa as variáveis definidas.
Por exemplo, a seguinte fórmula usa a função LET para definir uma variável chamada soma e, em seguida, usa a variável para calcular a soma de um conjunto de números.
Vamos supor que temos uma tabela de metas de 0 à 30mil que ficaria assim:
- Acima de 30 mil = Atingimos a meta
- Entre 20 e 30 mil = Quase na Meta
- Abaixo de 20 mil = Longe Meta
Com base nessas informações e com nossa base de dados com os seguintes valores abaixo, podemos formular a nossa função.
=LET(total;SOMA(B7:B14);IFS(total>30000;"Atingiu a meta";total>20000;"Quase atingindo a meta";
total<20000;"Precisamos vender mais!"))
O nome “total” é vinculado a função SOMA que vimos logo na sequencia da função. Após isso nós validamos o resultado de SOMA com a função IFS que irá verificar se os valores estão entre o especificado. Veja o resultado abaixo:
Também deixarei uma aula com a função LET caso você queira aprender melhor sobre essa função maravilhosa das Planilhas Google.
Espero que essas funções lhe ajudem muito no seu dia a dia pois pra mim serão de grande valia. Principalmente a função LET que deixará as formulas complexas bem mais simples.
Além de aprender sobre as funções básicas você pode ir para um próximo nível aprendendo a deixar ela visualmente mais interessante como por exemplo criar um Gráfico de Grade nas Planilhas Google.
No mais lhe desejo muito sucesso em seus projetos e na vida 😎
