Criação de resultados 2D dinâmicos no Planilhas Google com as funções MAP e LAMBDA

Observação: Esta postagem foi publicada originalmente em nosso fórum da comunidade.

O Planilhas Google oferece um conjunto robusto de recursos que permitem aos usuários lidar com dados de forma dinâmica e eficiente. Com a adição das funções LAMBDA e MAP, você pode simplificar operações complexas em fórmulas de célula única que geram dinamicamente resultados bidimensionais (2D). Neste blog, exploraremos como aproveitar essas funções usando um exemplo prático de um cenário de rastreamento de produtos.

Visão geral do cenário

Imagine que você esteja acompanhando os preços de vários produtos durante vários meses. Seu conjunto de dados registra detalhes do produto e atualizações de preço em momentos diferentes. O desafio é resumir as atualizações mensais de preços de cada produto em uma única tabela de resumo, exibindo o preço mais recente de cada produto no final de cada mês.

Estrutura de dados

Nosso conjunto de dados principal, denominado `Product`, é formatado da seguinte forma:

Objetivo

O objetivo é criar uma tabela `Monthly Product Summary` que seja atualizada dinamicamente para mostrar o preço mais recente de cada produto no final de cada mês. Essa tabela deve se adaptar aos novos dados à medida que são adicionados à guia `Product`.

Implementação passo a passo

1. Configuração dos cabeçalhos de data

Primeiro, geramos os cabeçalhos para cada mês na tabela de resumo:

Célula B1:

=FÓRMULA ARRAY(IF($B$2:$2="", "", TEXTO($B$2:$2,"yy")&"-"&TEXTO(MÊS($B$2:$2),"00")))

Essa fórmula usa `ARRAYFORMULA` para aplicar a formatação de texto em cada data da linha 2, criando um formato AAA-MM para os cabeçalhos dos meses.

2. Geração de intervalos de datas mensais

Em seguida, preparamos uma lista de datas de fim de mês para cada mês dentro de nosso intervalo desejado:

Célula C2:

=FÓRMULA ARRAY(EOMONTH($B$2,SEQUÊNCIA(1,15+12,1,1)))

Aqui, `EOMONTH` e `SEQUENCE` geram datas de fim de mês a partir da data em `B2`, abrangendo um intervalo de potencialmente 27 meses (15 + 12 como exemplo).

3. Listagem de produtos exclusivos

Identificamos todos os produtos exclusivos na guia `Product`:

Célula A4:

=ÚNICO(Produto!$B$2:$B)

4. Mapeamento dinâmico de preços

A parte mais complexa é criar um resumo dinâmico dos preços. Isso envolve o mapeamento da lista de produtos e, para cada produto, o mapeamento da lista de meses para obter o preço mais recente até o final de cada mês:

Célula B4:

=MAPA($A$4:$A, LAMBDA(produto,
  IF(produto="","", 
    MAPA($B$1:$1, LAMBDA(mês, 
      IF(mês="", "", IFNA(PESQUISA(FILTRO(Produto!$B$2:$D, TEXTO(Produto!$D$2:$D,"YY-MM")=mês, Produto!$B$2:$B=produto),"SELECT Col2 ORDER BY Col3 DESC LIMIT 1"),)
      )
    ))
  )
))

Essa fórmula mapeia cada produto e, em seguida, mapeia cada mês. Ela usa `FILTER` para restringir os registros ao produto e mês específicos e, em seguida, `QUERY` para buscar o preço mais recente para aquele mês usando o carimbo de data/hora (`ORDER BY Col3 DESC LIMIT 1`).

Conclusão

Ao integrar o `MAP` e o `LAMBDA` com outras funções do Google Sheets, criamos uma tabela de resumo dinâmica e facilmente atualizável que se adapta à medida que novos dados são adicionados. Essa abordagem minimiza a necessidade de atualizações manuais e permite uma ampla escalabilidade no gerenciamento de dados de produtos ao longo do tempo. Esse é apenas um exemplo de como essas funções podem ser poderosas para transformar seu manuseio e análise de dados no Planilhas Google.