Os termos “Power Query”, “Power Pivot”, “Power BI” e outros “powers” estão cada vez mais surgindo em artigos e materiais sobre o Microsoft Excel. Na minha experiência, nem todos entendem claramente o que está por trás desses conceitos, como eles estão interconectados e como podem ajudar um simples usuário do Excel.
Vamos esclarecer a situação.
Consulta de energia
Em 2013, um grupo especialmente criado de desenvolvedores da Microsoft lançou um suplemento gratuito para o Excel. Consulta de energia (outros nomes são Data Explorer, Get & Transform), que podem fazer muitas coisas úteis para o trabalho diário:
- Escolher arquivo dados em Excel de quase 40 fontes diferentes, incluindo bancos de dados (SQL, Oracle, Access, Teradata…), sistemas ERP corporativos (SAP, Microsoft Dynamics, 1C…), serviços de Internet (Facebook, Google Analytics, quase todos os sites).
- Colete dados de Arquivos todos os principais tipos de dados (XLSX, TXT, CSV, JSON, HTML, XML…), tanto individualmente quanto em massa – de todos os arquivos na pasta especificada. De pastas de trabalho do Excel, você pode baixar dados automaticamente de todas as planilhas de uma só vez.
- limpar dados recebidos de “lixo”: colunas ou linhas extras, repetições, informações de serviço no “cabeçalho”, espaços extras ou caracteres não imprimíveis, etc.
- Traga dados para ordem: corrigir maiúsculas e minúsculas, números como texto, preencher lacunas, adicionar o “cap” correto da tabela, analisar o texto “adesivo” em colunas e colá-lo de volta, dividir a data em componentes, etc.
- de todas as maneiras possíveis transformar tabelas, trazendo-as para a forma desejada (filtrar, classificar, alterar a ordem das colunas, transpor, adicionar totais, expandir tabelas cruzadas para planas e recolher).
- Substitua dados de uma tabela para outra combinando um ou mais parâmetros, ou seja, uma boa função de substituição VPR (PROCV) e seus análogos.
O Power Query é encontrado em duas versões: como um suplemento separado para o Excel 2010-2013, que pode ser baixado do site oficial da Microsoft, e como parte do Excel 2016. No primeiro caso, após a instalação, uma guia separada aparece em Excel:
No Excel 2016, toda a funcionalidade do Power Query já está incorporada por padrão e está na guia Data (Encontro: Data) como um grupo Obtenha e converta (Obter e transformar):
As possibilidades dessas opções são completamente idênticas.
O recurso fundamental do Power Query é que todas as ações de importação e transformação de dados são armazenadas na forma de uma consulta – uma sequência de etapas na linguagem de programação interna do Power Query, que é sucintamente chamada de “M”. As etapas sempre podem ser editadas e repetidas quantas vezes (atualizar consulta).
A janela principal do Power Query geralmente se parece com isso:
Na minha opinião, este é o complemento mais útil listado neste artigo para uma ampla gama de usuários. Muitas tarefas para as quais você teve que distorcer terrivelmente com fórmulas ou escrever macros agora são feitas com facilidade e beleza no Power Query. Sim, e com posterior atualização automática dos resultados. E considerando que é gratuito, em termos de relação preço-qualidade, o Power Query está simplesmente fora da concorrência e é absolutamente obrigatório para qualquer usuário de Excel intermediário avançado nos dias de hoje.
pivô
O Power Pivot também é um suplemento para o Microsoft Excel, mas projetado para tarefas ligeiramente diferentes. Se o Power Query estiver focado na importação e processamento, o Power Pivot será necessário principalmente para análises complexas de grandes quantidades de dados. Como uma primeira aproximação, você pode pensar no Power Pivot como uma tabela dinâmica sofisticada.
Os princípios gerais de trabalho no Power Pivot são os seguintes:
- Nós somos a primeira carregando dados no Power Pivot – 15 fontes diferentes são suportadas: bancos de dados comuns (SQL, Oracle, Access…), arquivos Excel, arquivos de texto, feeds de dados. Além disso, você pode usar o Power Query como fonte de dados, o que torna a análise quase onívora.
- Em seguida, entre as tabelas carregadas as conexões estão configuradas ou, como dizem, é criado Modelo de dados. Isso permitirá no futuro construir relatórios sobre quaisquer campos de tabelas existentes como se fosse uma tabela. E nada de VPR novamente.
- Se necessário, cálculos adicionais são adicionados ao Modelo de Dados usando colunas calculadas (semelhante a uma coluna com fórmulas em uma “tabela inteligente”) e medidas (um análogo do campo calculado no resumo). Tudo isso é escrito em uma linguagem interna especial do Power Pivot chamada DAX (Data Analysis eXpressions).
- Na planilha Excel, de acordo com o Modelo de Dados, os relatórios de nosso interesse são construídos na forma tabelas dinâmicas e diagramas.
A janela principal do Power Pivot se parece com isso:
E é assim que o Data Model se parece, ou seja, todas as tabelas carregadas com relacionamentos criados:
O Power Pivot possui vários recursos que o tornam uma ferramenta exclusiva para algumas tarefas:
- Em Pivot sem limite de linha (como no Excel). Você pode carregar tabelas de qualquer tamanho e trabalhar facilmente com elas.
- O Power Pivot é muito bom em comprimir dados ao carregá-los no modelo. Um arquivo de texto original de 50 MB pode facilmente se transformar em 3-5 MB após o download.
- Como o Power Pivot “sob o capô”, na verdade, possui um mecanismo de banco de dados completo, ele lida com grandes quantidades de informações Muito rápido. Precisa analisar de 10 a 15 milhões de registros e criar um resumo? E tudo isso em um laptop antigo? Sem problemas!
Infelizmente, o Power Pivot ainda não está incluído em todas as versões do Excel. Se você tiver o Excel 2010, poderá baixá-lo gratuitamente no site da Microsoft. Mas se você tiver o Excel 2013-2016, tudo depende da sua licença, porque. em algumas versões está incluído (Office Pro Plus, por exemplo), e em outras não (Office 365 Home, Office 365 Personal, etc.) Você pode ler mais sobre isso aqui.
Mapas de energia
Este complemento apareceu pela primeira vez em 2013 e foi originalmente chamado de GeoFlow. Destina-se à visualização de geo-dados, ou seja, informações numéricas em mapas geográficos. Os dados iniciais para exibição são obtidos do mesmo modelo de dados do Power Pivot (consulte o parágrafo anterior).
A versão demo do Power Map (quase não diferente da versão completa, aliás) pode ser baixada de forma totalmente gratuita novamente no site da Microsoft. A versão completa está incluída em alguns pacotes do Microsoft Office 2013-2016 junto com o Power Pivot – na forma de um botão Mapa 3D aba inserção (Inserir — mapa 3D):
Principais recursos do Power Map:
- mapas pode ser plana e volumosa (globo).
- Você pode usar vários tipos de visualização (histogramas, gráficos de bolhas, mapas de calor, preenchimentos de área).
- Você pode adicionar medição do tempo, ou seja, animar o processo e vê-lo se desenvolver.
- Os mapas são carregados do serviço Bing Maps, ou seja, você precisa de uma conexão de internet muito rápida para visualizar. Às vezes, há dificuldades com o reconhecimento correto de endereços, porque. os nomes nos dados nem sempre correspondem aos do Bing Maps.
- Na versão completa (não demo) do Power Map, você pode usar seu próprio mapas para download, por exemplo, para visualizar os visitantes de um shopping center ou preços de apartamentos em um edifício residencial diretamente na planta do edifício.
- Com base nas geo-visualizações criadas, você pode criar vídeos diretamente no Power Map (exemplo) para compartilhá-los posteriormente com quem não possui o add-in instalado ou incluí-los em uma apresentação em Power Point.
visão de poder
Introduzido pela primeira vez no Excel 2013, este suplemento foi projetado para dar vida aos seus dados com gráficos, tabelas, mapas e tabelas interativos. Às vezes, os termos são usados para isso. painel de instrumentos (painel de controle) or painel de instrumentos (tabela de desempenho). A linha inferior é que você pode inserir uma planilha especial sem células em seu arquivo do Excel – um slide do Power View, onde você pode adicionar texto, imagens e muitos tipos diferentes de visualizações com base em seus dados do modelo de dados do Power Pivot.
Vai parecer algo assim:
As nuances aqui são:
- Os dados iniciais são retirados do mesmo local – do modelo de dados do Power Pivot.
- Para trabalhar com o Power View, você precisa instalar o Silverlight em seu computador – o análogo do Flash da Microsoft (gratuito).
No site da Microsoft, a propósito, há um curso de treinamento muito decente em Power View em .
Power BI
Ao contrário dos anteriores, o Power BI não é um add-in para Excel, mas um produto separado, que é todo um conjunto de ferramentas para análise e visualização de negócios. É composto por três elementos principais:
1. Área de Trabalho do Power BI – um programa para analisar e visualizar dados, que inclui, entre outras coisas, todas as funcionalidades dos complementos Power Query e Power Pivot + mecanismos de visualização aprimorados do Power View e Power Map. Você pode baixá-lo e instalá-lo gratuitamente no site da Microsoft.
No Power BI Desktop, você pode:
- Carregar dados de mais de 70 fontes diferentes (como no Power Query + conectores adicionais).
- vincular tabelas para modelar (como no Power Pivot)
- Adicione cálculos adicionais aos dados com medidas и colunas calculadas no DAX (como no Power Pivot)
- Crie belos dados baseados relatórios interativos com diferentes tipos de visualizações (muito semelhantes ao Power View, mas ainda melhores e mais poderosos).
- Publique criou relatórios no site do Power BI Service (veja o próximo ponto) e compartilhe-os com colegas. Além disso, é possível conceder diferentes direitos (leitura, edição) a diferentes pessoas.
2. Serviço online do Power BI – para simplificar, este é um site onde você e cada usuário da sua empresa terão seu próprio “sandbox” (espaço de trabalho) onde você poderá carregar relatórios criados no Power BI Desktop. Além da visualização, permite inclusive editá-los, reproduzindo quase todas as funcionalidades do Power BI Desktop online. Você também pode emprestar visualizações individuais de relatórios de outras pessoas aqui, coletando os painéis de seu próprio autor deles.
Parece algo assim:
3. Power BI Móvel é um aplicativo para iOS/Android/Windows para se conectar ao Power BI Service e visualizar convenientemente (não editar) os relatórios e dashboards criados diretamente na tela do seu telefone ou tablet. Você pode baixá-lo (totalmente gratuito) aqui.
Em um iPhone, por exemplo, o relatório gerado acima se parece com isso:
E tudo isso mantendo a interatividade e animação + aprisionada pelo toque e desenho na tela com caneta. Muito confortavelmente. Assim, a inteligência de negócios fica disponível para todas as pessoas-chave da empresa a qualquer hora e em qualquer lugar – apenas o acesso à Internet é necessário.
Planos de preços do Power BI. O Power BI Desktop e o Mobile são gratuitos e a maioria dos recursos do Power BI Service também são gratuitos. Portanto, para uso pessoal ou dentro de uma pequena empresa, você não precisa pagar um centavo por todos os itens acima e pode permanecer no plano com segurança Gratuito. Se você quiser compartilhar relatórios com colegas e administrar seus direitos de acesso, você terá que ir para BESS (US$ 10 por mês por usuário). Existe um pouco mais Premium – para grandes empresas (> 500 usuários) que requerem armazenamento separado e capacidades de servidor para dados.
- Projeto Gráfico de Gantt no Excel com Power Query
- Como criar um banco de dados no Excel usando o Power Pivot
- Visualização do movimento ao longo da rota no mapa no Power Map