Práticas de Excel, Power Pivot e Power Query: Análise de Dados by Grupo Lidel

E dição

FCA – Editora de Informática

Av. Praia da Vitória, 14 A – 1000-247 LISBOA

Tel: +351 213 511 448 fca@fca.pt www.fca.pt

d istribuição

Lidel – Edições Técnicas, Lda.

R. D. Estefânia, 183, R/C Dto. – 1049-057 LISBOA

Tel: +351 213 511 448 lidel@lidel.pt www.lidel.pt

L ivraria

Av. Praia da Vitória, 14 A – 1000-247 LISBOA

Tel: +351 213 541 418 livraria@lidel.pt

® Marca registada da FCA PACTOR Editores, Lda.

ISBN edição impressa: 978-972-722-928-4

1.ª edição impressa: maio 2023

Paginação: Carlos Mendes

Impressão e acabamento: Tipografia Lousanense, Lda. – Lousã

Depósito Legal n.º 516163/23

Capa: José M. Ferrão – Look-Ahead

Todos os nossos livros passam por um rigoroso controlo de qualidade, no entanto aconselhamos a consulta periódica do nosso site (www.fca.pt) para fazer o download de eventuais correções.

Não nos responsabilizamos por desatualizações das hiperligações presentes nesta obra, que foram verificadas à data de publicação da mesma.

Os nomes comerciais referenciados neste livro têm patente registada.

Reservados todos os direitos. Esta publicação não pode ser reproduzida, nem transmitida, no todo ou em parte, por qualquer processo eletrónico, mecânico, fotocópia, digitalização, gravação, sistema de armazenamento e disponibilização de informação, sítio Web, blogue ou outros, sem prévia autorização escrita da Editora, exceto o permitido pelo CDADC, em termos de cópia privada pela AGECOP –– Associação para a Gestão da Cópia Privada, através do pagamento das respetivas taxas.

V © FCA

Sobre a Autora XI Prólogo ............................................................................................................................ XIII Capítulo 1 Tabelas 1 Exercício 1.1 – Tabela de compras ........................................................................ 5 Exercício 1.2 – Ilíquido, desconto, IVA e valor a pagar (1) 7 Exercício 1.3 – Ilíquido, desconto, IVA e valor a pagar (2)..................................... 9 Exercício 1.4 – Totais do ilíquido, do desconto, do IVA e do valor a pagar (3) 10 Exercício 1.5 – Atualização das taxas de desconto e do IVA ................................ 11 Exercício 1.6 – Só impressoras e digitalizadores 12 Exercício 1.7 – Segmentação de produtos............................................................ 13 Exercício 1.8 – Promoção numa emissora de rádio .............................................. 14 Exercício 1.9 – Desconto de quantidade (1) 16 Exercício 1.10 – Desconto de quantidade (2) ........................................................ 17 Exercício 1.11 – Classificação de serviços 19 Exercício 1.12 – Procura dos dados de um serviço .............................................. 20 Exercício 1.13 – Procura dos melhores serviços 21 Exercício 1.14 – Total de vendas mensais ............................................................. 23 Exercício 1.15 – Total de vendas mensais por vendedor 24 Exercício 1.16 – Vendas de um vendedor num certo mês .................................... 25 Exercício 1.17 – Câmbios para transações .......................................................... 26 Exercício 1.18 – Procurar por produto, rubrica e ano 28 Exercício 1.19 – Vários hotéis na mesma cidade .................................................. 29 Exercício 1.20 – Procurar dados com máscaras 31

Índice

VI Práticas de Excel, Power Pivot e Power Query: Análise de Dados Exercício 1.21 – Ordenação por coluna 32 Exercício 1.22 – Mais vendas ordenadas .............................................................. 34 Exercício 1.23 – Ordenação por coluna por ordem de preferência do utilizador .. 35 Exercício 1.24 – Ordenação de uma lista de moedas ........................................... 36 Exercício 1.25 – Ordenação por duas colunas 38 Exercício 1.26 – Filtro de vendas por região 39 Exercício 1.27 – Filtro por região com ordenação por localidade 41 Exercício 1.28 – Filtros por região e volume de vendas ........................................ 42 Exercício 1.29 – Filtros por região ou volume de vendas ...................................... 43 Exercício 1.30 – Filtros por totais regionais .......................................................... 44 Exercício 1.31 – Filtro por apelido 45 Exercício 1.32 – Filtro com máscaras 47 Exercício 1.33 – Filtro para encomendas de semana 49 Exercício 1.34 – Filtro para encomendas de trimestre .......................................... 50 Exercício 1.35 – As dez encomendas de mais baixo valor ................................... 52 Exercício 1.36 – Várias agregações ....................................................................... 53 Capítulo 2 Tabelas Dinâmicas 57 Exercício 2.1 – Totais por vendedor ...................................................................... 61 Exercício 2.2 – Totais por mês e vendedor ............................................................ 63 Exercício 2.3 – Percentagens dos totais de vendas 65 Exercício 2.4 – Média mensal das vendas por vendedor 66 Exercício 2.5 – Alteração do volume de vendas 67 Exercício 2.6 – Inserção do registo de vendas ...................................................... 68 Exercício 2.7 – Valor total com IVA ........................................................................ 69 Exercício 2.8 – Desconto e IVA .............................................................................. 70 Exercício 2.9 – Agregação de dois produtos 71 Exercício 2.10 – Saldo final 73 Exercício 2.11 – Alguns departamentos 75 Exercício 2.12 – Funções por departamento......................................................... 77 Exercício 2.13 – Coluna escolhida pelo utilizador ................................................. 79 Exercício 2.14 – Duas tabelas ............................................................................... 80 Exercício 2.15 – Formato tabular 82 Exercício 2.16 – Compras por fornecedor 83

VII Índice © FCA Exercício 2.17 – Um relatório por folha de cálculo 86 Exercício 2.18 – Segmentação para todas as tabelas dinâmicas ........................ 87 Exercício 2.19 – Grupos de clientes ...................................................................... 89 Exercício 2.20 – Escalões de rendimento .............................................................. 90 Capítulo 3 Power Pivot 93 Exercício 3.1 – Suplemento Microsoft Power Pivot .............................................. 99 Exercício 3.2 – Vista de dados do Power Pivot ..................................................... 99 Exercício 3.3 – Relação entre faturas e produtos 102 Exercício 3.4 – Valor dos produtos faturados 102 Exercício 3.5 – Peso relativo dos produtos faturados 103 Exercício 3.6 – Carregamento de faturas com o valor ilíquido .............................. 105 Exercício 3.7 – Valor a pagar com IVA ................................................................... 107 Exercício 3.8 – Valor total por produto .................................................................. 108 Exercício 3.9 – Valores a pagar por datas 109 Exercício 3.10 – Valor total por dia da semana 111 Exercício 3.11 – Valor total por trimestre 113 Exercício 3.12 – Valor total por ano, mês e dia ..................................................... 114 Exercício 3.13 – Valores agregados das faturas ................................................... 116 Exercício 3.14 – Modelo com medidas explícitas ................................................. 117 Exercício 3.15 – IVA das faturas 118 Exercício 3.16 – IVA por produto e mês 120 Exercício 3.17 – Horas de tradução por grupo linguístico 121 Exercício 3.18 – Horas de tradução de Romeno por intérprete ............................ 123 Exercício 3.19 – Receitas por hotel e comunidade ............................................... 124 Exercício 3.20 – Receitas, custos e lucro por hotel............................................... 126 Exercício 3.21 – Contagem de noites de turistas franceses ou alemães 127 Exercício 3.22 – Contagem do número de hóspedes 128 Exercício 3.23 – Contagem de hóspedes por meio de pagamento 129 Exercício 3.24 – Número de noites de um intervalo .............................................. 131 Exercício 3.25 – Receitas para subida de preços ................................................. 132 Exercício 3.26 – Atualização dos aumentos .......................................................... 134 Exercício 3.27 – Key Performance Index (KPI) para o volume de vendas (1) 135 Exercício 3.28 – Key Performance Index (KPI) para o volume de vendas (2) 137

VIII Práticas de Excel, Power Pivot e Power Query: Análise de Dados Exercício 3.29 – Key Performance Index (KPI) para o desvio do objetivo 139 Exercício 3.30 – Key Performance Index (KPI) para lucro ..................................... 141 Exercício 3.31 – Gráfico dinâmico de meio de pagamento ................................... 143 Exercício 3.32 – Gráfico dinâmico para Key Performance Index (KPI) .................. 145 Exercício 3.33 – Dashboard com tabela e gráfico 147 Exercício 3.34 – Dashboard com quatro gráficos 149 Capítulo 4 Power Query 153 Exercício 4.1 – Lista de proprietários 157 Exercício 4.2 – Lista de arrendatários 159 Exercício 4.3 – Liga dos campeões 160 Exercício 4.4 – Mapa de aulas ............................................................................... 162 Exercício 4.5 – Horário das aulas .......................................................................... 164 Exercício 4.6 – Aulas com os professores ............................................................. 165 Exercício 4.7 – Número de passageiros por rota 168 Exercício 4.8 – Número total de passageiros por rota 171 Exercício 4.9 – Classificações médias 172 Exercício 4.10 – Transposição de linhas e colunas ............................................... 174 Exercício 4.11 – Divisão das rendas ...................................................................... 175 Exercício 4.12 – Divisão do movimento dos navios .............................................. 178 Exercício 4.13 – Divisão da coluna Cliente em três 180 Exercício 4.14 – Preenchimento da coluna Fatura 182 Exercício 4.15 – Valor dos produtos vendidos 183 Exercício 4.16 – Participantes em congresso........................................................ 185 Exercício 4.17 – Atualização dos participantes ..................................................... 186 Exercício 4.18 – Distâncias percorridas por semana ............................................ 187 Exercício 4.19 – Atualização das distâncias percorridas 189 Exercício 4.20 – Vendas por zona e trimestre 190 Exercício 4.21 – Vendas por classes e subclasses 192 Exercício 4.22 – Quantidades insuficientes ........................................................... 194 Exercício 4.23 – Notas com parâmetros ............................................................... 196 Exercício 4.24 – Alteração do limite para distinção ............................................... 198 Exercício 4.25 – Vendas de imóveis por vendedor 200 Exercício 4.26 – Filtros por cidades com parâmetro 201

IX Índice © FCA Exercício 4.27 – Filtrar os dados de outra cidade 203 Exercício 4.28 – Juntar tabelas do mesmo ficheiro ............................................... 204 Exercício 4.29 – Juntar tabelas de ficheiros da mesma pasta 206 Exercício 4.30 – De colunas para linhas 208 Exercício 4.31 – Criar um novo tipo de dados ...................................................... 210 Exercício 4.32 – Consulta dos dados dos produtos 212 Exercício 4.33 – Entradas em armazém 213 Exercício 4.34 – Vendas por produto e mês .......................................................... 215 Bibliografia ...................................................................................................................... 219 Índice Remissivo ............................................................................................................ 221

Sobre a Autora

Adelaide Carvalho

Doctor of Philosophy in Management Science (Lancaster University, Reino Unido), Reg. Doutora em Economia e Gestão (Universidade do Porto), Master of Science in Management Science (University of Kent at Canterbury, Reino Unido), Master of Science in Computing Science (University of London, Reino Unido), Eq. Mestre em Ciência dos Computadores (Universidade do Porto), Licenciada em Economia (Universidade do Porto). Docente, desde 1983, em diversos estabelecimentos de ensino superior.

Organizou e ministrou, desde 1991 até ao presente, vários cursos de aplicação da Informática à Economia e à Gestão em Portugal, Grécia, Finlândia, Moçambique, Macau e Timor-Leste.

Autora de vários livros publicados pela FCA, nomeadamente:

• Access para Gestão: 71 Exercícios;

• Automatização em Excel: 69 Exercícios;

• Cálculos Elementares com Excel: 74 Exercícios;

• Excel para Gestão: 65 Exercícios;

• Exercícios de Excel para Estatística;

• Exercícios de Java: Algoritmia e Programação Estruturada;

• Exercícios de Power BI: Importação, Edição e Visualização de Dados;

• Exercícios Resolvidos com Excel para Economia & Gestão (2.ª Ed. At. e Aum.);

• Gráficos com Excel: 95 Exercícios;

• Métodos Quantitativos com Excel: 52 Exercícios;

• Práticas de C#: Algoritmia e Programação Estruturada;

• Práticas de C#: Programação Orientada por Objetos;

• Práticas de Python: Algoritmia e Programação;

• Programação com Excel para Economia & Gestão (2.ª Ed. At. e Aum.).

XI © FCA

Prólogo

Na senda das tecnologias de business intelligence, a Microsoft tem vindo a enfatizar a importância do armazenamento e tratamento rápido de grandes volumes de dados sobre os quais se possam desenhar cenários de evolução que fundamentem a tomada de decisões consentâneas com a missão e objetivos das organizações.

As versões mais recentes do Excel têm vindo a favorecer as tabelas dotando-as de propriedades e funcionalidades, que aumentam a capacidade de processamento dos dados em tamanho e velocidade. Assim, têm vindo a ser definidas mais funções embutidas, que recebem e recriam tabelas como um todo, ou como subconjuntos de linhas e colunas extraídos consoante os critérios enunciados pelo utilizador.

Estas novas funções diminuem a intervenção do utilizador, através da interface gráfica, para que a velocidade de processamento aumente sobretudo quando as tabelas são grandes. Muitas operações foram automatizadas introduzindo, por exemplo: o preenchimento relâmpago, o cálculo automático de fórmulas para as células seguintes e o redimensionamento das tabelas. A necessidade crescente da análise cruzada de dados veio reforçar a importância das tabelas dinâmicas e do suplemento Power Pivot que, através da definição do modelo de dados, permite o cruzamento de dados de várias tabelas.

Como os dados que tratamos, hoje, têm origem em diversas aplicações, o Power Query passou a integrar a componente Dados do Excel. O Power Query é um subconjunto do Microsoft Power BI (https://powerbi.microsoft.com), com a mesma filosofia de business intelligence, que alargou a capacidade de obter dados dispersos e de os transformar para que uniformemente pudessem ser tratados pelo Excel como se nele todos tivessem origem.

Este livro, Práticas de Excel, Power Pivot e Power Query: Análise de Dados, foi organizado em quatro capítulos, sendo que os dois primeiros constituem a infraestrutura do estudo do suplemento Power Pivot e da componente Power Query.

XIII © FCA

Esta obra dá continuidade ao objetivo de outras da mesma autora – a promoção da utilização das folhas de cálculo ilustrando as facilidades do Microsoft Excel, através da resolução de exercícios de aplicação variada, às áreas da economia, gestão e disciplinas auxiliares. As soluções e respetivas resoluções foram escolhidas principalmente pela facilidade de compreensão da solução e aplicação da metodologia de aproximação descendente (top-down) em detrimento de outras soluções, porventura, mais eficientes, mas mais complexas.

Este livro constitui, assim, um instrumento precioso para a motivação dos formandos e da aceleração do seu processo de aprendizagem nas novas funcionalidades do Microsoft Excel.

XIV

Práticas de Excel, Power Pivot e Power Query: Análise de Dados

1. Tabelas 36 exercícios 2. Tabelas Dinâmicas 20 exercícios 3. Power Pivot 34 exercícios 4. Power Query 34 exercícios

Tabelas

As versões mais recentes do Microsoft Excel, nomeadamente o Excel 365 e o Excel 2019, trouxeram para primeiríssimo plano a organização em tabelas de dados que se interrelacionam. As tabelas são instanciações de variáveis do tipo objeto que, encapsulando caraterísticas e funcionalidades, facilitam o tratamento do conjunto dos dados ou dos seus subconjuntos.

Uma tabela contém os dados relevantes sobre uma entidade-tipo, por exemplo, funcionário, cliente, fornecedor, departamento ou fatura. As tabelas são implementáveis em intervalos de células (ranges) com linhas designadas por registos ou tuplos, que representam as instanciações da entidade-tipo, e colunas designadas por campos ou atributos, que contém os dados recolhidos sobre cada instante da entidade-tipo. Assim, uma tabela é um conjunto de registos (ou linhas) e um registo é um conjunto de campos (ou colunas). A tabela Funcionários, por exemplo, com dez linhas e cinco colunas contém registos de dez funcionários sobre os quais guarda o número, o nome, o número de telemóvel, o endereço de email e a categoria profissional:

Uma tabela guarda, portanto, dados – alfanuméricos, numéricos inteiros e reais, lógicos ou booleanos, datas ou horas. Tipicamente, uma tabela representa os dados como conjuntos bidimensionais com linhas e colunas encimadas por uma linha de cabeçalho que identifica cada coluna por um nome.

1 © FCA 1

Práticas de Excel, Power Pivot e Power Query: Análise de Dados

Os comandos Inserir e Dados do menu principal do Excel permitem criar tabelas a partir de um intervalo de células vazio ou preenchido com dados. Se o intervalo de células não contiver uma linha de cabeçalho ao inserir a tabela, é criada uma linha com nomes genéricos para as colunas:

As tabelas têm várias propriedades e funcionalidades incluindo um nome, segmentação, atualização e exportação de dados e uma reconversão para intervalo de células acessíveis, através do menu Estrutura da Tabela, que fica disponível sempre que o cursor seleciona qualquer célula da tabela:

Os nomes da tabela e das colunas permitem construir referências estruturais para identificar, entre outros elementos, cada coluna e linha, um conjunto contíguo de colunas, os cabeçalhos das colunas e os dados e os totais das linhas da tabela. As referências estruturais, ao invés dos endereços das células e dos intervalos de células, acompanham a dinâmica de redimensionamento das tabelas resultante de inserções, remoções e alterações das linhas e colunas.

As referências estruturadas podem ser utilizadas em fórmulas, no contexto da tabela ou fora dela, e permitem a atualização automática das fórmulas sempre que ocorram alterações dos nomes da tabela ou das colunas e o preenchimento automático das fórmulas para todas as linhas da tabela.

Exemplificamos as referências estruturais principais a partir da tabela Vendas que contém a designação do produto (Prod), a quantidade vendida (Qtd), o preço unitário de venda (PUV) e o valor da venda (Valor):

Exercício 1.11 Classificação de serviços

Pretendem-se classificar 45 serviços relativamente a cinco itens. Nesse sentido, atribuiu-se a cada serviço cinco pontuações de 0 a 100 e gravaram-se os dados no ficheiro Cap1Ex11.xlsx Estabeleceram-se os seguintes escalões de classificação:

Pontuação Média Classificação

0<= P<30 Mau

30<=P<50 Medíocre

50<=P<75 Suficiente

P>=75 Bom

Calcule a pontuação média dos 45 serviços e proceda à sua classificação.

Fórmulas

=MÉDIA(Pontuações[@[Item_1]:[Item_5]])

Calcula a média aritmética das cinco pontuações atribuídas a um serviço.

=PROCX([@Média]; Escalões[Pontuações]; Escalões[Classificação]; Mensagem; Tipo de correspondência)

Procura a pontuação média de um serviço, na coluna Pontuações, da tabela Escalões e devolve a classificação correspondente; devolve a mensagem apropriada se a pontuação média não pertencer a nenhum escalão. O Tipo de correspondência tem o código -1 para obtermos o escalão: limite inferior<=Pontuação média<limite inferior do escalão seguinte.

Resolução do problema

1. Abra o ficheiro CapEx11.xlsx

2. Determine a pontuação média dos serviços

2.1. Digite na folha de cálculo Serviços

2.1.1. Média em G3

2.1.2. =MÉDIA(Pontuações[@[Item_1]:[Item_5]]) em G4

3. Classifique os serviços

3.1. Crie a tabela Escalões

3.1.1. Digite na folha de cálculo Classes

19 Tabelas © FCA

Práticas de Excel, Power Pivot e Power Query: Análise de Dados

3.1.1.1. O cabeçalho da tabela de classificações em A3:B3

3.1.1.2. Os limites inferiores dos escalões e as classificações em A4:B7

3.1.2. Selecione

3.1.2.1. A3:B7

3.1.2.2. Inserir – Tabelas – Tabela

3.1.3. Atribua-lhe o nome Escalões

3.2. Selecione a folha de cálculo Serviços

3.2.1. Digite

3.2.1.1. Classificação em H3

3.2.1.2. =PROCX([@Média];escalões[Pontuação Média];escalões [Classificação];"";-1) em H4

4. Grave o ficheiro com o nome Cap1Ex11_Sol.xlsx

RESULTADO ESPERADO

Parte dos dados:

Exercício 1.12

Procura dos dados de um serviço

Procure na tabela Pontuações do Exercício 1.11, todos os dados do serviço S11

Tabelas Dinâmicas

As tabelas dinâmicas apresentam dados agregados – contagens, totais, médias, variâncias ou outras medidas de agregação – que resultam da análise cruzada dos campos de uma ou mais tabelas. Assim, suponhamos que temos uma tabela Faturas que regista, para cada venda efetuada, o número e data da fatura, o nome e a localidade de residência do cliente e o valor da fatura emitida:

A partir desta tabela, podemos formar tabelas dinâmicas que apresentem os totais faturados por cliente e localidade, ou os valores médios mensais faturados.

Além de resumirem os dados originais, as tabelas dinâmicas permitem fazer a análise cruzada dos valores possíveis dos campos de uma base de dados.

No exemplo seguinte, a tabela dinâmica cruza as idades com os cursos e o sexo dos estudantes, contando o número de estudantes consoante a idade, o curso e o sexo e apresentando os totais em linha e coluna:

57 © FCA 2

Práticas de Excel, Power Pivot e Power Query: Análise de Dados

Os subcomandos Tabelas e Gráficos do comando Inserir do menu principal do Excel permitem criar tabelas e gráficos dinâmicos:

Criar uma tabela dinâmica consiste em identificar os dados fonte e a folha de cálculo onde queremos criar a tabela dinâmica e, em seguida, escolher os campos que queremos representar nas linhas, os campos que queremos representar nas colunas e definir a função de agregação que queremos calcular e cujo resultado será colocado na interseção das linhas e colunas.

No seguinte exemplo, contamos o número de alunos por idade e sexo:

3 Power Pivot

O Power Pivot é um suplemento (ou add-in) que acrescenta funcionalidades ao Excel, aproximando-o de um Sistema de Gestão de Bases de Dados Relacionais (SGBDR).

Podemos entender o Power Pivot como um processador de dados, que usa o Excel como interface gráfica, no qual vai buscar os dados que processa e coloca os resultados do processamento no próprio Excel.

Resultados: Tabelas, Gráficos dinâmicos Importação de tabelas de dados

Power Pivot

Relações entre tabelas Ordenação e Filtros Cálculo de novos campos (DAX) Cálculo de medidas (DAX)

O Power Pivot permite, assim, criar e analisar modelos de dados. Aqui, um modelo de dados é um conjunto de dados organizados em tabelas bidimensionais com linhas que correspondem a registos e colunas que correspondem a campos. As tabelas podem interrelacionar-se duas a duas, abrindo caminhos de consulta de dados para que possam ser pesquisados em várias tabelas, evitando a sua repetição.

93 © FCA

Excel

Práticas de Excel, Power Pivot e Power Query: Análise de Dados

Vejamos a tabela Faturas que guarda os dados das faturas que emitimos:

O número de cliente, o nome e o número de identificação fiscal repetem-se várias vezes sempre que uma fatura é emitida para um dado cliente. As faturas 100, 130, 160 e 180 repetem os dados 10, Ana Gomes e 123445556, porque foram emitidas para o mesmo cliente.

A repetição de dados, além de implicar uma perda de tempo e dinheiro pela sua redundância, pode gerar inconsistência dos mesmos, pois sempre que atualizarmos os dados de um cliente pode falhar a atualização de uma das suas ocorrências.

Evitar a repetição de dados numa tabela é tão importante que é um dos principais objetivos da normalização de estruturas de dados em Sistemas de Gestão de Bases de Dados (SGBD).

Recomendamos, portanto, que se transforme a tabela Faturas em duas – Clientes e Faturas – separando os dados dos clientes dos dados das faturas:

NumFat NumCli Nome Cliente NIF Valor a pagar 100 10 Ana Gomes 123445556 450 110 30 Diogo Freitas 123999556 300 120 20 António Lima 523445556 120 130 10 Ana Gomes 123445556 200 140 30 Diogo Freitas 123999556 250 150 20 António Lima 523445556 789 160 10 Ana Gomes 123445556 800 170 30 Diogo Freitas 123999556 500 180 10 Ana Gomes 123445556 320

Faturas

Clientes

Exercício 3.24

Número de noites de um intervalo

Dada a base de dados do Exercício 3.19, filtre os dados de modo a totalizar, por hotel, o número de hóspedes que aí passaram entre duas e cinco noites.

Resolução do problema

1. Abra o ficheiro Cap3Ex19.xlsx

2. Defina as medidas ContaNoites e Entre2e5

2.1. Selecione

2.1.1. Power Pivot – Cálculos – Medidas – Nova Medida

2.1.2. Hosp em Nome da Tabela

2.2. Digite

2.2.1. ContaNoites em Nome da medida

2.2.2. =COUNTA([N.º Noites]) em Fórmula

2.3. Selecione

2.3.1. Power Pivot – Cálculos – Medidas – Nova Medida

2.3.2. Hosp em Nome da Tabela

2.4. Digite

2.4.1. Entre2e5 em Nome da medida

2.4.2. =VAR Inf=2

VAR Sup=5

RETURN SUMX(filter(Hosp; Hosp[N.º Noites]>=Inf && Hosp[N.º Noites] <=Sup); Hosp[ContaNoites]) em Fórmula

3. Defina a tabela dinâmica de contagem de noites do intervalo numa Nova folha de cálculo

3.1. Selecione

3.1.1. Modelo de Dados – Gerir – Base – Tabela Dinâmica – Tabela Dinâmica

3.1.2. Nova folha de cálculo

3.2. Coloque o cursor na nova tabela dinâmica

3.3. Arraste

3.3.1. Alojamento da tabela Alojamentos para Linhas

3.3.2. Entre2e5 da tabela Hosp para Σ Valores

Práticas de Excel, Power Pivot e Power Query: Análise de Dados

4. Defina cabeçalhos apropriados para a nova tabela dinâmica

5. Feche o Power Pivot

6. Grave o ficheiro com o nome Cap3Ex24_Sol.xlsx

RESULTADO ESPERADO

Exercício 3.25

Receitas para subida de preços

Considere a base de dados do Exercício 3.19 e suponha que o preço por noite subirá 8%, 10% ou 12% para todos os alojamentos.

Determine o montante atual das receitas e após a subida de preços.

Resolução do problema

1. Abra o ficheiro Cap3Ex19.xlsx

2. Crie uma tabela com os aumentos percentuais a considerar

2.1. Defina numa folha de cálculo a tabela Aumentos

2.2. Atribua à folha de cálculo o nome SubidaPreços

3. Adicione a tabela Aumentos ao modelo de dados

3.1. Coloque o cursor na tabela Aumentos

3.2. Selecione Power Pivot – Tabelas – Adicionar a Modelo de Dados

132

Power Query

Inicialmente um suplemento (ou add-in) do Excel, o Power Query passou, a partir da versão 2016, a integrar a componente Dados, disponibilizando funcionalidades para a importação de dados com origem em aplicações diversas, a organização em tabelas e a transformação sob a orientação do utilizador em formas e formatos desejáveis. As tabelas, uma vez transformadas, são carregáveis em folhas de cálculo onde são operáveis pelo utilizador como “vulgares” tabelas do Excel.

Hoje integra as ferramentas de business intelligence no sentido de preparar os dados do Excel e outras aplicações-fonte para melhor se interligarem com os sistemas BI da Microsoft, nomeadamente o Power BI (https://powerbi.microsoft.com/pt-pt/).

O Power Query é um editor de consultas que vai buscar dados a diversas fontes, por exemplo, a ficheiros do Excel (a partir de tabelas ou intervalos de células), ficheiros do Access, ficheiros de texto, ficheiros PDF, bases de dados, fluxos de dados e websites. Uma consulta é uma tabela com os dados e o formato requeridos pelo utilizador.

Na versão do Excel 365, integra os submenus Obter e Transformar Dados e Consultas e Ligações do menu Dados:

Se, por exemplo, pretendermos obter dados a partir de um ficheiro PDF, basta selecionarmos Dados – Obter e Transformar Dados – Obter Dados – De Ficheiro – De PDF e, em seguida, selecionarmos o ficheiro PDF e a tabela de valores que queremos importar:

153 © FCA

Práticas de Excel, Power Pivot e Power Query: Análise de Dados

Uma vez obtidos os dados, o Power Query permite editá-los para que satisfaçam os requisitos da análise de dados que o utilizador quer realizar. O Power Query abre-se numa janela própria que oferece quatro grandes grupos de funcionalidades: básicas, transformação, adição de colunas e caraterísticas da visualização de dados.

As funcionalidades básicas permitem, entre outras, fechar o editor e carregar consultas ou ligações no Excel, definir caraterísticas da consulta – nome e descrição da consulta; remover linhas e colunas; ordenar dados; transformar dados – divisão de colunas, agrupamento de dados e definição de tipos de dados; combinar e adicionar consultas; gerir parâmetros e definir as origens de dados:

As funcionalidades de transformação de dados permitem alterar o formato da consulta, –transformar a primeira linha em cabeçalho; trocar linhas por colunas; dividir colunas; preencher espaços vazios; elaborar ou anular tabelas dinâmicas – alterar o formato dos dados, agrupá-los, criar novos tipos de dados, expandir ou agregar colunas estruturadas.

Podemos, por exemplo, transformar a tabela:

154

Resolução do problema

1. Abra o ficheiro Cap4Ex4.xlsx

2. Execute o editor de consultas

2.1. Coloque o cursor na tabela MapaAulas

2.2. Selecione Dados – Obter e Transformar Dados – Da Tabela/Intervalo

3. Atribua o nome RegistoAulas à consulta

3.1. Selecione MapaAulas no painel lateral da esquerda

3.2. Prima o botão direito do rato

3.3. Selecione Mudar o nome

3.4. Digite RegistoAulas

4. Altere o tipo de dados do campo Hora para Hora

4.1. Selecione

4.1.1. A coluna Hora

4.1.2. Base – Transformar Tipo de Dados – Hora

4.2. Prima Substituir atual

5. Transforme o RegistoAulas numa tabela com três campos

5.1. Selecione

5.1.1. A coluna Hora

5.1.2. Transformar – Qualquer Coluna – Anular Dinamização das Colunas – Anular Dinamização de Outras Colunas

6. Altere os cabeçalhos dos campos

6.1. Digite

6.1.1. Dia da Semana em Atributo

6.1.2. Atividade em Valor

7. Feche e carregue a consulta

7.1. Selecione

7.1.1. Base – Fechar – Fechar & Carregar – Fechar & Carregar Para …

7.1.2. Tabela

7.1.3. Nova folha de cálculo

7.1.4. Adicionar estes dados ao Modelo de Dados

8. Grave o ficheiro com o nome Cap4Ex4_Sol.xlsx

Onde quer que esteja – no escritório, em casa, em território nacional ou no estrangeiro – a qualquer hora do dia ou da noite, as nossas obras estão sempre a um clique de distância…