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.xls, conforme orientações da Lição
anterior.
2. Para alterar a
consulta clique em uma das células que contenha dados e
selecione o seguinte comando: Dados -> Obter dados
externos -> 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 seleciona-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 cria-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.59:
Figura
2.59 – 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.60:
Figura
2.60 – 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 TotalGeral, 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.
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.61:
Figura
2.61 – Fazendo a soma dos itens do pedido.
15. Clique em OK e observe os
resultados, indicados na Figura 2.62. Cada Cidade aparece uma única
vez e é exibido o total geral por cidade:
Figura
2.62 – 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
TotalGeral 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.63:
Figura
2.63 – Exibindo a média dos itens por Cidade.
16. 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. O Excel abre a janela
solicitando que você informe a faixa da planilha onde os dados
da consulta devem ser exibidos. Por padrão o Excel sugere a
partir da célula A1. Clique em OK. O Excel executa a consulta e
dentro de alguns instantes os dados serão exibidos na planilha,
conforme indicado na Figura 2.64:
Figura
2.64 – Resultados da consulta, retornados para o Excel.
17. 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: http://www.juliobattisti.com.br/excel120/excel120.asp.
18. Salve a planilha com o nome de Exercício
23.xls, na pasta C:\ExcelAvançado.
19.
Mantenha o Microsoft Excel aberto, pois iremos alterar
esta consulta na próxima lição.
Todos
os direitos reservados: ® Júlio Battisti, 2002