Do Excel à nave espacial com dois complementos 😎

O Excel, usado em 90% das empresas e criticado, na verdade usa apenas uma fração de suas capacidades. Os dados são inseridos manualmente, os tipos são corrigidos à mão, e a pergunta "vai atualizar sozinho?" provoca um suspiro profundo. Porque duas guias no menu permanecem intocadas.

A rotina da maioria é: baixou o arquivo, limpou manualmente, adicionou fórmulas, enviou para o chefe. No dia seguinte, tudo de novo. No outro dia, tudo de novo. Um tédio mortal. 😔
Quarenta minutos diários para um trabalho que deveria levar três cliques.

Agora adicione 2 milhões de linhas. O Excel atinge o limite em um milhão de linhas e trava. Ok, você divide o arquivo em partes. Perde as relações entre as tabelas, a tabela dinâmica começa a mentir.

Mas você pode ativar 2 complementos e usar o Excel ao máximo.
Conheci esses complementos em 2017. Trabalhava como freelancer. Sites em WordPress e outras rotinas. Chegou uma tarefa. Precisava extrair apenas certos grupos de produtos de 60 arquivos Excel pesados e combinar tudo em um único arquivo. Deram dois dias. Tentei juntar 2 arquivos manualmente. Horrível, chato e tedioso. Comecei a pesquisar na internet, talvez houvesse uma pílula mágica. E encontrei o Power Query. Depois de mexer um pouco na interface, conectei todos os arquivos da pasta e montei o arquivo final em 10 minutos. Fiquei impressionado. Ok, vou explicar que bichos são esses complementos 💪

1️⃣ Power Query: dados sem mão de obra

Ele já vem embutido. Guia "Dados", seção "Obter e Transformar". Conecte a fonte uma vez, configure a transformação, depois clique em "Atualizar". O processo que levava uma hora agora leva dez segundos.

Mas tem um probleminha. O Power Query "adivinha" os tipos de dados olhando apenas as primeiras 200–1000 linhas, dependendo da fonte. Para CSV, bastam 200, para outros formatos um pouco mais. Mas a questão é: se os dados não forem homogêneos, números viram texto silenciosamente, sem nenhum aviso.

Por isso, para dormir tranquilo, sempre defina os tipos de dados manualmente. Não confie na detecção automática. Sim, demora mais. Mas depois não precisa revisar configurações antigas e procurar o problema.

E mais. Não faça uma consulta gigante. Sempre use uma abordagem iterativa.
A cadeia funciona melhor: uma etapa separada para limpeza, outra para transformação. O carregamento também separado. Quando algo quebrar (e vai quebrar), você encontra o problema em um minuto, não em uma hora.

E sim, o PQ permite conectar bancos de dados diretamente ✅

2️⃣ Power Pivot: Excel sem limites

1.048.576 linhas é o limite do Excel comum. O Power Pivot suporta dezenas de milhões através da compactação VertiPaq (o mesmo motor do Power BI). O arquivo xlsx fica mais pesado, pois um banco de dados é empacotado internamente. Mas na memória, os dados ocupam muito menos: o VertiPaq compacta cerca de 10 vezes (bem, por aí — o número exato depende dos dados), armazena em colunas, não em linhas. Grandes dados voam.

O principal que existe são as medidas. Não coloque tudo em colunas calculadas: elas são calculadas sempre e incham o modelo, enquanto uma medida é calculada apenas quando você a vê na tabela dinâmica, sob o contexto de filtro específico. A diferença é enorme.

O coração do Power Pivot é a função CALCULATE. Ela altera o contexto de cálculo. "Vendas sem devoluções", "plano apenas para Moscou", "e se o preço subisse 10%". Tudo isso é CALCULATE.

Para os amantes da maçã (Apple), como eu 😁
O Power Pivot não está disponível no macOS. É triste, mas é assim. Se na equipe há colegas no Mac, eles simplesmente não verão essa maravilha.

Se seus relatórios giram em torno do Excel — experimente esses dois complementos 👇

#excel

@data_dzen 🙂