Neste item vamos editar a consulta criada na Lição 16. Agora vamos fazer o cálculo do total de vendas por PaísDeDestino e, dentro do PaísDeDestino, por Cidade. Para que possamos fazer estas agregações, é importante que os campos estejam nesta posição, ou seja, primeiro (mais à esquerda) o PaísDeDestino e depois, (a sua direita), o campo CidadeDeDestino.

Não vamos alterar os filtros da consulta.

Exercício 23: Alterar a consulta criada na Lição 16. Observe que os campos PaísDeDestino e CidadeDeDestino estão em posições inversas ao que devem estar (primeiro PaísDeDestino e depois CidadeDeDestino), para que possamos fazer os cálculos propostos. Neste exemplo iremos excluir todos os campos existentes e adicionar os campos na ordem correta.

Em seguida faremos a totalização por PaísDeDestino e, dentro do PaísDeDestino por CidadeDeDestino.

1.      Você já deve estar com a planilha Exemplo 22.xlsx, conforme orientações da Lição anterior.

2.      Para alterar a consulta utilize os passos do exercício 21 para Editar consulta.

3.      O Microsoft Query será aberto com a consulta criada na lição anterior.

4.      Agora vamos excluir todos os campos da consulta. Para excluir um campo clique no nome do campo para selecioná-la, depois pressione a tecla Delete. Pronto, o campo será excluído. Utilizando este procedimento exclua todos os campos da consulta, inclusive o campo calculado, pois iremos criá-lo novamente.

5.      Adicione novamente o campo PaísDeDestino e em seguida o campo CidadeDeDestino.

6.      Agora vamos acrescentar um campo que faz o cálculo do total geral, usando a fórmula proposta anteriormente. Observe que este campo não existe diretamente na tabela Detalhes do Pedido, ou seja, teremos que acrescentar um campo que será calculado (montado) a partir de outros campos disponíveis em uma ou mais tabelas da consulta. No nosso exemplo vamos criar um campo calculado chamado Total Geral, o qual é composto pela seguinte fórmula:

(Quantidade*PreçoUnitário)*(1-Desconto)

7.    Para criar um campo calculado, basta digitar a expressão de cálculo diretamente na primeira coluna em branco, após o último campo da consulta. Não use um sinal de igual (=) no início da expressão. Por exemplo, para criar o campo Total Geral, utilize a expressão indicada anteriormente. Após digitar a expressão pressione Enter e observe que você já obtém os resultados indicados na Figura 2.76:

Figura 2.76 – Campo Total Geral já calculado. Falta agrupar por cidade.

8.      Observe que a expressão torna-se o nome da coluna. Para atribuir um nome para a coluna, basta dar um clique duplo na expressão de cálculo. Será exibida a janela Editar coluna. Nesta janela preencha o campo Cabeçalho da coluna conforme indicado na Figura 2.77:

Figura 2.77 – Definindo um cabeçalho para o campo Calculado.

9.      Clique em OK para fechar a janela Editar coluna. Observe que a coluna já aparece com o Cabeçalho Total Geral, ao invés da expressão de cálculo.

10.    Exclua todos os critérios que estiverem definidos.

11.    Vamos classificar a listagem por PaísDeDestino e, dentro do país, por CidadeDeDestino. Clique na coluna PaísDeDestino para seleciona-la. Pressione a tecla Shift e mantenha-a pressionada. Clique na coluna CidadeDeDestino. As duas colunas ficarão selecionadas. Agora clique no botão (). Com isso a listagem ficará classificada pelo PaísDeDestino e, dentro do PaísDeDestino, pela CidadeDeDestino.

12.       Agora temos que fazer com que o Query totalize pelo campo CidadeDeDestino. Se você observar os resultados, verá que aparecem vários registros para a mesma cidade. Cada registro é um item de pedido. Por exemplo, para a cidade Aachen, aparecem 10 registros. Estes são todos os itens de pedidos enviados para esta cidade. O que queremos é que sejam agrupados todos os itens de cada cidade e calculada a soma destes itens, o que na prática será o total geral de vendas para a cidade.

Figura 2.78 – Vários registros para a mesma cidade

14.    Para fazer este agrupamento vamos utilizar a função Soma, na coluna Total Geral. Dê um clique duplo em Total Geral (no nome da coluna). Será exibida a janela Editar coluna. Nesta janela, na lista Total selecione Soma, conforme indicado na Figura 2.79:

 
Figura 2.79 – Fazendo a soma dos itens do pedido.

15.    Clique em OK e observe os resultados, indicados na Figura 2.80. Cada Cidade aparece uma única vez e é exibido o total geral por cidade:

 
Figura 2.80 – Fazendo a soma das vendas por Cidade.

16.    Ao invés da Soma por cidade você poderia, por exemplo, calcular o valor médio dos pedidos por cidade. Para isso dê um clique duplo no cabeçalho da coluna Total Geral para exibir a janela com propriedades da coluna. Observe que na lista Campo é exibida a seguinte expressão: Soma((Quantidade*PreçoUnitário)*(1-Desconto)). Basta alterar a função Soma para Avg (Average significa média, em inglês). Altere a função para Avg e clique em OK. Será exibida a média do valor dos itens por cidade, conforme indicado na Figura 2.81:

 
Figura 2.81 – Exibindo a média dos itens por Cidade.

17.    O próximo passo é fechar o Microsoft Query, retornando estes dados para a planilha do Excel. Para fazer isso utilize o comando: Arquivo -> Retornar dados ao Microsoft Excel. Você voltará a Caixa de diálogo Propriedade de Conexão (Figura 2.62) - > clique em OK. O Excel 2007executa a consulta e dentro de alguns instantes os dados serão exibidos na planilha, conforme indicado na Figura 2.82:

Figura 2.82 – Resultados da consulta, retornados para o Excel 2007.

18. Observe que é retornada somente uma linha para cada cidade, com a média dos itens por cidade. Você pode utilizar o comando Formatar -> Células para formatar os dados da coluna G, definindo um número de casas decimais e a exibição ou não do símbolo da moeda (R$). Para maiores detalhes sobre formatação de células no Excel, consulte o Curso de Excel em 120 Lições, disponível no seguinte endereço: https://juliobattisti.com.br/cursos/excelbasico.

19. Salve a planilha com o nome de Exercício 23.xlsx, na pasta C:\Excel 2007 Avançado\Exemplos.

VOCÊ CONHECE A UNIVERSIDADE DO EXCEL?

UNIVERSIDADE PRÁTICA DO EXCEL - Domine o Excel, do Básico até a Criação de Planilhas Profissionais com Excel e VBA!

Aplica-se ao Excel 2019, 2016, 2013 e 2010!

22 Cursos - 925 Vídeo-Aulas - 120:42 horas - Só R$ 97,00

Para Todos os Detalhes, Acesse:

https://juliobattisti.com.br/excel-curso-completo.asp