Utilizando a Linha do Tempo para aplicar filtros nas Tabelas Dinâmicas

Olá pessoal

Dois dos posts mais acessados do blog envolvem dicas de produtividade na utilização do Excel:

O post sobre Tabelas Dinâmicas – link aqui;

E o post sobre a aplicação de filtros através da Segmentação de Dados – link aqui.

Pois hoje vou falar um pouquinho mais sobre um recurso complementar da Segmentação de Dados (disponível a partir do Excel 2013) – a Linha do Tempo.

Sobre a Linha do tempo

A Linha do Tempo é uma Segmentação de Dados desenhada especificamente para permitir a aplicação de filtros em valores do tipo data. A partir da utilização da Linha do Tempo, você poderá aplicar filtros às Tabelas Dinâmicas para que seja possível analisar as informações com base em tendências de datas. Veja a tabela abaixo:

Esta planilha contém a lista de chamados para as áreas de suporte técnico de uma empresa em um determinado período. Suponha que você tenha que gerar um relatório com as informações dos atendimentos realizados e, após a criação de Tabelas e Gráficos Dinâmicos, obtenha o seguinte resultado:

Agora, suponha que você tenha que aplicar um filtro ao relatório que permita a visualização e agrupamento dos dados de acordo com as datas de abertura dos chamados. Para isso, selecione um dos Gráficos Dinâmicos (ou uma das Tabelas Dinâmicas usadas como referência pelos gráficos) e, na guia Inserir da Faixa de Opções, clique em Linha do Tempo. A caixa de diálogo Inserir Linhas do Tempo será exibida, e você poderá escolher o campo de data que deseja utilizar como referência para aplicação do filtro (neste cenário, há apenas um campo do tipo data disponível – Abertura. Caso houvesse mais do que um campo deste tipo, todos seriam apresentados e você poderia escolher o campo conforme sua necessidade):

Após selecionar o campo Abertura, a Linha do Tempo estará disponível na planilha:

Após inserir a Linha do Tempo, é importante garantir que ela está conectada a todas as Tabelas Dinâmicas existentes (ou às Tabelas Dinâmicas que você desejar). Clique com o botão direito do mouse na Linha do Tempo e escolha a opção Conexões do Relatório. Perceba que, por padrão, a Linha do Tempo mantém conexão apenas com a Tabela Dinâmica que foi usada inicialmente para sua criação. Para que todas as Tabelas Dinâmicas (e consequentemente os Gráficos Dinâmicos) sejam filtradas a partir da Linha do Tempo, selecione as demais Tabelas Dinâmicas que desejar:

Assim, você poderá aplicar os filtros para visualizar o resumo dos dados de acordo com o período que desejar. Por exemplo, suponha que você tenha de exibir o resumo dos chamados abertos no mês de Maio/2013:

Por padrão, a Linha do Tempo utiliza a escala de meses como padrão de unidades. Caso necessário, você poderá modificar a unidade de exibição (para anos, trimestres, meses ou dias) clicando na caixa de combinação disponível no canto superior direito da Linha do Tempo:

Assim você poderá aplicar filtros anuais com a Linha do Tempo, como no exemplo abaixo onde estão resumidos todos os chamados do ano de 2012:

É isso. Para baixar a planilha e testar a Linha do Tempo você mesmo, clique aqui.

Para baixar este arquivo em formato digital, clique aqui.

Espero que tenha gostado.

Um abraço e até a próxima.

Power Pivot - Estabelecendo Relações

Olá pessoal

Este é o terceiro post de uma série na qual procuro falar um pouco sobre as principais funcionalidades do Power Pivot. Se você perdeu os posts anteriores, vale a pena dar uma olhada:

Introdução ao Power Pivot

Criando campos calculados no Power Pivot

O cenário

Como foi possível visualizar nos dois primeiros posts, o Power Pivot é uma poderosa ferramenta para manipulação, organização e modelagem de dados. Entretanto, até o momento, temos trabalhado com apenas uma única fonte de dados para realizar as análises e obter as informações necessárias. Pois agora é hora de cavar um pouquinho mais fundo.

O cenário é o seguinte: temos em mãos um banco de dados do Access, no qual estão catalogadas todas as medalhas já distribuídas nos jogos olímpicos, desde 1900:

O banco possui uma única tabela, na qual estão armazenadas as seguintes informações: 

• Edição dos jogos
• Se eram jogos de inverno ou verão
• Nome do atleta que recebeu a medalha, seu país de origem e sexo
• O código da disciplina competida
• Tipo de medalha obtida (Ouro, Prata e Bronze)

 Já numa segunda fonte de dados (uma planilha do Excel), temos a listagem com o nome das disciplinas e seus respectivos códigos:

O nosso trabalho será criar um Dashboard com a distribuição das medalhas, por país, sexo, disciplina, tipo de medalhas e outras informações que forem relevantes.

Para acompanhar este post na prática, você pode baixar os dois arquivos nos links abaixo:

Banco Access – Medalhas Olímpicas

Planilha Excel – DisciplinasOlímpicas

Importando os dados

Para gerar o Dashboard com todas as informações, será necessário unificar as duas fontes de dados – pois no banco do Access temos apenas o código das disciplinas, os quais iremos utilizar como referência para que seja estabelecido um relacionamento com a planilha do Excel, que contém efetivamente os nomes que desejamos exibir.

Nesse contexto, abra um novo arquivo do Excel. Clique em Power Pivot > Gerenciar. O Power Pivot permite que você importe dados externos diretamente para o modelo de dados, sem ter que necessariamente importá-los para o Excel e depois tratá-los no Power Pivot. Na seção Obter Dados Externos, clique em Do Banco de Dados e escolha Do Access:

Utilize o Assistente de Importação de Tabela para localizar e importar os dados do Access. Ao terminar a importação, o modelo de dados irá apresentar as informações de medalhas.

Agora, será necessário importar também as informações da planilha do Excel. No Power Pivot, em Obter Dados Externos, clique no botão De Outras Fontes e escolha a opção Arquivo do Excel:

Não se esqueça de marcar a opção Usar primeira linha como cabeçalhos de coluna:

Ao finalizar o processo de importação, teremos as duas listas que foram importadas para o modelo de dados.

Estabelecendo o relacionamento

Pois bem, agora que já temos os dados importados, vamos estabelecer o relacionamento entre as fontes. Conforme vimos, há uma informação oriunda do Access que será utilizada para se conectar ao Excel – o campo DisciplineID. Para criar uma relação, na Faixa de Opções do Power Pivot, clique no botão Exibição de Diagrama:

A visualização do diagrama apresenta uma tela conforme abaixo:

Você poderá estabelecer o relacionamento de duas formas distintas: 

1. Na fonte de dados Medals, arraste o campo DisciplineID até a fonte de dados Plan1; ou
2. Clique com o botão direito do mouse na fonte de dados Medals e escolha a opção Criar Relação:

Em seguida, na caixa de diálogo Criar Relação, defina a relação entre os campos DisciplineID das fontes Medals e Plan1 e clique em Criar:

O resultado será:

Criando o Dashboard

Bem, agora é com você. Crie o Dashboard de acordo com a sua criatividade. Eu utilizei o Power View, o e resultado foi:

Agora sim, ein! Isso aí é o Big Data do Povão! Agora qualquer pessoa pode criar os mais dinâmicos, modernos e impactantes Dashboards, sem necessidade de ferramentas auxiliares, caras e inacessíveis.

Um golaço da Microsoft J

Como já é tradição, o post em formato digital pode ser baixado aqui.

Espero que tenha gostado da série sobre o Power Pivot!

Um abraço e até a próxima.

Power Pivot: Criando Campos Calculados

Olá pessoal

Este é o segundo post de uma série em que discutirei algumas funcionalidades e recursos do Power Pivot. O primeiro post, onde foi realizada uma apresentação do Power Pivot e suas funcionalidades iniciais, está disponível aqui.

Recapitulando....

No primeiro post o foco principal esteve em como importar informações de um banco de dados externo para o Power Pivot, assim como na aplicação de filtros nos dados importados e na exclusão de informações desnecessárias para melhoria da performance e organização da planilha. Caso já não o tenha, você pode baixar o arquivo e simular a importação aqui.

O cenário

Uma vez que os dados foram importados, é hora de entender o cenário atual e como o Power Pivot pode ajudar a resolvê-lo. O banco de dados importado possui uma série de tabelas e consultas que segmentam informações sobre um determinado projeto. A tabela que foi trazida para o Power Pivot, MSP_EpmTask, é responsável por armazenar informações específicas do cronograma do projeto. Vejamos:

Digamos que no seu relatório você tenha que demonstrar graficamente quantas tarefas do cronograma estão concluídas, quantas estão em andamento e quantas ainda não foram iniciadas. Ao importar os dados brutos da tabela, esta não é uma informação disponível de maneira nativa. Entretanto, você pode utilizar uma função personalizada que utilize como referência a coluna ‘Porcentagem Concluída’, com a seguinte regra:

Porcentagem concluída = 0 : ‘Tarefa não iniciada’

Porcentagem concluída = 100 : ‘Tarefa concluída’

Porcentagem concluída > 0 e < 100 : ‘Tarefa em andamento’

Assim como no Excel, o Power Pivot possui uma lista de funções internas que podem ser utilizadas em diferentes situações. Neste link você poderá encontrar a lista de funções que são suportadas pelo Power Pivot. Para resolver a questão que o cenário atual apresenta, iremos utilizar a função IF.

Criando o campo calculado

Na janela do Power Pivot, procure a coluna Porcentagem Concluída. Clique na coluna à direita da coluna Porcentagem Concluída com o botão direito do mouse e escolha a opção Inserir Coluna:

Uma nova coluna chamada CalculatedColumn1 será criada. Digite o sinal de igual para iniciar a fórmula e, em seguida, digite =IF( . Perceba que a Caixa de Nome do Power Pivot passará a exibir a função IF, assim como acontece ao criar uma nova função no Excel:

Em seguida, clique na primeira célula da coluna Porcentagem Concluída e digite =0 ; “Tarefa não iniciada”; Em seguida, digite:

IF([Porcentagem Concluída] = 1; “Tarefa concluída” ; “Tarefa em andamento”))

O resultado da criação da função será:

=IF([Porcentagem Concluída]=0;"Tarefa não iniciada";IF([Porcentagem Concluída]=1;"Tarefa concluída";"Tarefa em andamento"))

Pressione ENTER para que a função seja criada em todas as linhas da coluna:

Para finalizar, renomeie a coluna para ‘Status da Tarefa’.

Com o campo personalizado criado, você pode optar por exibir as informações na sua planilha no Excel do modo que desejar. Neste caso, eu utilizarei o Power View para construir um Dashboard dinâmico, com uma boa aparência visual:

Suponha que agora você queira saber também qual o número de tarefas a serem concluídas por mês. Você pode criar um novo campo calculado baseado na coluna Término, utilizando a função MID, que tem como objetivo desmembrar um texto em partes.

A expressão que você deve utilizar na função é:

="20"&MID([Término];11;2)&"/"&MID([Término];8;2)

Assim que criar o campo, você poderá adicioná-lo ao seu Dashboard:

A grande vantagem de utilizar o Power View é a possibilidade de realizar drill-down nos dados ao clicar nas colunas dos gráficos (por exemplo, filtrando apenas as Tarefas Concluídas):

Espero que você tenha gostado do post. Em breve, falarei um pouco mais sobre o Power Pivot.

Como de costume, você pode baixar este post neste link: https://www.box.com/s/ytuyr0cb9hryohdrcw4h

Um abraço e até a próxima!

Power Pivot

Olá pessoal

Conforme havia prometido neste post, hoje vou dar continuidade ao trabalho que venho fazendo sobre a divulgação das ferramentas de Power BI – dando ênfase agora ao Power Pivot.

Antes de começar, verifique se a sua versão do Excel é compatível com o Power Pivot. Em seguida, você deve também instalar o suplemento: http://office.microsoft.com/pt-br/excel-help/iniciar-o-suplemento-power-pivot-no-microsoft-excel-2013-HA102837097.aspx

O Power Pivot foi um dos primeiros suplementos de Power BI introduzidos no Microsoft Excel, disponibilizado a partir da versão 2010. Este suplemento tem como principal objetivo permitir que o usuário realize análises profundas em um conjunto extenso de dados. Com a utilização do Power Pivot você pode importar, organizar, vincular e analisar um grande volume de dados, os quais podem ser oriundos de diferentes origens, tendo assim condições de efetuar análises rápidas e seguras para apoio ao processo de tomada de decisão.

O grande diferencial do Power Pivot é que ele trabalha com Modelos de Dados (todas as informações podem ser importadas, estruturadas e organizadas nos modelos antes de serem levadas à planilha do Excel). No final do dia, isso significa que apenas as informações relevantes são transferidas à planilha, já estando preparadas para serem apresentadas em Tabelas Dinâmicas, Gráficos e/ou Power View.

O problema

Imagine a seguinte situação: você possui um banco de dados estruturado com uma série de tabelas e consultas, onde estão armazenadas as informações sobre um importante projeto da sua empresa. Você precisa importar essas informações para o Excel, e assim realizar uma série de análises de como está o andamento deste projeto.

Num primeiro momento, você poderia simplesmente importar as informações diretamente do Access para o Excel. Pronto, problema resolvido. Porém....

Digamos que a tabela que armazena os dados que você irá analisar contenha um número muito grande de colunas e linhas, mas que você precise apenas de uma porção desses dados. A primeira situação desconfortável ao importar dados diretamente de um banco de dados para o Excel é a impossibilidade de organizar de maneira preliminar as informações desejadas. O que acontece de fato é que todas as linhas e colunas são importadas para sua planilha, para que só depois você tenha condições de manipulá-las. Se o seu banco de dados for muito grande, o impacto no Excel é sentido imediatamente, com possível lentidão, travamento do arquivo e etc.

A solução

Dentre os incontáveis recursos e funcionalidades oferecidas pelo Power Pivot (os quais vou discutir em futuros posts), a manipulação e organização de dados é a que devemos aprender de imediato.

Primeiro, baixe aqui o banco de dados que será utilizado como referência para este cenário (o banco foi criado em Microsoft Access).

Com o suplemento do Power Pivot ativado, clique em Power Pivot > Gerenciar. Isto fará com que a janela do Power Pivot seja aberta para que você possa importar os dados. Já na janela do Power Pivot, em Obter Dados Externos, clique em Do Banco de Dados > Do Access:

A caixa de diálogo Assistente para Importação de Tabela será aberta. Nela você deverá apontar o local onde o seu banco de dados está salvo:

Após informar onde o arquivo a ser importado está salvo, você poderá clicar em Testar Conexão para se certificar que a conexão está ativa e funcional. Em seguida, clique em Avançar. Na caixa de diálogo seguinte, mantenha a opção default Selecionar itens em uma lista de tabelas e exibições para escolher os dados a serem importados para determinar manualmente quais as tabelas devem ser importadas. Caso opte pela segunda opção, você deverá construir uma expressão em SQL (query) para obter os dados. Clique em Avançar.

Na caixa de diálogo seguinte, você deverá escolher a tabela ou consulta que contém os dados que deseja importar. Selecione a tabela MSP_EpmTask. Aqui o Power Pivot começa a mostrar a que veio: caso fizéssemos uma importação simples da tabela MSP_EpmTask, todos os campos existentes na tabela (e são muitos!) seriam importados... seria a mesma coisa que fazer uma importação padrão do Access para o Excel. Como será necessário filtrar, organizar e manipular os dados no Modelo de Dados do Power Pivot antes de entregá-los ao Excel, clique em Visualizar e Filtrar:

Ao clicar em Visualizar e Filtrar, o Assistente de Importação de Tabela abrirá uma nova caixa de diálogo. Nela você terá como determinar quais colunas devem ser importadas, bem como se filtros serão aplicados para resumir os dados. Você pode utilizar a caixa de seleção para apontar as colunas desejadas e a caixa de combinação (combo-box) para especificar os critérios de filtros:

Neste exemplo, clique na opção que desmarca todas as colunas e faça a seleção das colunas abaixo:

• TaskIntUID
• Custo da Linha de Base
• Duração da Linha de Base
• Término da Linha de Base
• Início da Linha de Base
• Custo
• Variação de Custo
• Duração
• Variação da Duração
• Término
• Variação do Término
• É Crítico
• É Marco
• É Resumo
• Nome
• Porcentagem Concluída
• Início
• TaskUID
• EDT

Após selecionar as colunas, clique em OK. Após a finalização da importação dos dados, você receberá a confirmação de êxito:

Ao clicar em Fechar, você será novamente direcionado à janela do Modelo de Dados, onde irá visualizar as informações importadas e onde poderá também realizar as manipulações desejadas:

É importante perceber que a janela do Modelo de Dados possui aparência muito semelhante à de uma planilha do Excel. Na parte inferior da janela está a guia de planilha, que contém o nome da tabela que foi importada do Access. Na parte superior, o Ribbon exibe os comandos que podem ser aplicados para manipulação, formatação e organização dos dados – algumas das ações são as mesmas que as oferecidas pelo Excel, como Formatação, Classificação, Localizar e etc.

Caso você já esteja preparado para enviar os dados à planilha, para que sejam consolidados e exibidos através de gráficos e tabelas dinâmicas, você deve clicar no botão Tabela Dinâmica, disponível no Ribbon do Power Pivot:

Em seguida, especifique onde deseja distribuir os dados:

E ao clicar em OK os dados serão disponibilizados no Excel:

Nos próximos posts, irei tratar de outros recursos e funcionalidades do Power Pivot, como a criação de fórmulas, manipulação dos dados, criação de vínculos e etc.

A versão digital do post está disponível neste link.

Nos links abaixo, um pouco mais sobre o Power Pivot:

Sobre o Power Pivot:

https://support.office.com/pt-br/article/Power-Pivot-Powerful-data-analysis-and-data-modeling-in-Excel-d7b119ed-1b3b-4f23-b634-445ab141b59b

Tutorial do Power Pivot – análise de dados da Tabela Dinâmica usando um Modelo de Dados no Excel 2013:

https://support.office.com/pt-br/article/tutorial-f9ad8310-3b5b-48a3-913d-5cc1b364ee46

Um abraço e até a próxima!

Construindo um Dashboard com dados do Facebook

Olá pessoal

Sempre que é possível, tento compartilhar aqui no blog informações que julgo relevantes sobre as novas ferramentas de BI que estão disponíveis junto do Office 2013. Já falei sobre o Power Query, sobre o Power View e sobre o Power Maps. Ainda estou devendo um post sobre o Power Pivot, mas isso vai ficar para outro momento.

A ideia desse post é tentar mostrar a potencialidade da utilização e combinação de diferentes funcionalidades do Power BI para criar Dashboards visualmente inovadores, e que ofereçam um alto nível de interatividade aos usuários. Para isso, irei efetuar uma conexão com o Facebook para montar um painel com o histórico de acesso de um determinado usuário.

Isso mesmo, você leu bem. Se você possui o Office365 Pro Plus ou o Microsoft Office Professional Plus 2013, você pode utilizar o Power Query para estabelecer conexões com inúmeras fontes de dados, entre elas o Facebook.

Começando os trabalhos

Para começar, instale o suplemento Power Query. Após instalado e disponível o suplemento, clique na aba Power Query e, em seguida, External Data > From Other Sources > From Facebook:

Na caixa de diálogo aberta, peça para efetuar o login e entre com os dados da sua conta:

Em seguida, após concordar com as Políticas de Privacidade, clique em OK. Você irá visualizar a tabela com todo seu histórico de acesso e ações no Facebook:

Você poderá manipular, tratar, filtrar e organizar os dados conforme sua necessidade utilizando o próprio Power Query. Após finalizar as manipulações, clique em Apply & Close para enviar as informações de volta ao Microsoft Excel, para que assim você visualize os dados em formato de tabela:

Após obter os dados em formato de tabela no Microsoft Excel, clique em Inserir > Power View. Dessa maneira, você poderá montar o seu Dashboard e verificar o que você faz, além de saber se está dedicando tempo além da conta no Facebook:

O Power Query permite inclusive que você crie funções personalizadas para manipular os dados disponíveis de maneira mais adequada:

Neste caso, criei uma fórmula para extrair apenas o Ano a partir da coluna created_time. Ao voltar ao Excel e atualizar os dados, será possível adicionar essas informações aos gráficos:

Agora fica por sua conta aplicar as formações que desejar e filtrar os dados com o dinamismo que o Power View oferece, como por exemplo neste cenário, onde apliquei um drill-down para verificar as ações do ano de 2011:

Simplesmente sensacional, não é mesmo?  :-D

Imagine isso aplicado a nível profissional! Agora é por sua conta.

Esse post você pode baixar aqui.

Um abraço e até a próxima!