Excel 2010 Avançado Mod 02 Rev

AN02FREV001/REV 4.0

39

PROGRAMA DE EDUCAÇÃO CONTINUADA A DISTÂNCIA Portal Educação

CURSO DE

MICROSOFT® OFFICE EXCEL 2010 AVANÇADO

Aluno:

EaD - Educação a Distância Portal Educação

AN02FREV001/REV 4.0

40

CURSO DE

MICROSOFT® OFFICE EXCEL 2010 AVANÇADO

MÓDULO II

Atenção: O material deste módulo está disponível apenas como parâmetro de estudos para este Programa de Educação Continuada. É proibida qualquer forma de comercialização ou distribuição do mesmo sem a autorização expressa do Portal Educação. Os créditos do conteúdo aqui contido são dados aos seus respectivos autores descritos nas Referências Bibliográficas.

AN02FREV001/REV 4.0

41

Módulo 2

8 OBJETIVOS

9 AS CARACTERÍSTICAS DE UMA BASE DE DADOS

Para utilizar uma estrutura de banco de dados no Excel 2010, é necessário

considerar três aspectos importantes:

Um banco de dados em uma planilha é uma tabela retangular cujas linhas e

colunas utilizadas são contínuas.

As colunas dessa tabela são consideradas os campos que, quando

combinados, apresentam as informações desejadas.

As linhas são consideradas os registros dessa base de dados, ou seja, o

conjunto de dados que podem ser avaliados.

Abordar os recursos de gerenciamento de uma Base de Dados.

Oportunizar de forma elementar os conceitos de Base de Dados.

AN02FREV001/REV 4.0

42

Toda base de dados, como mencionado, deve ter o formato de uma tabela

retangular, com as colunas indicando os campos e as linhas, os registros. Cada

coluna deve possuir um nome de identificação (Vendedor, Departamento, Dados

Venda, Produto e Valor) que indique seu conteúdo e as linhas que serão os

registros (o conjunto de dados de cada elemento tema da base de dados).

A tabela de base de dados é uma estrutura que não admite linhas e/ou colunas em branco, ou seja, a base de dados deve ser sempre contínua.

Os nomes dos campos devem ser sempre definidos como rótulo de

identificação. Caso deseje colocar o nome de um campo como número, não se

esqueça de digitar antes do caractere numérico um prefixo de rótulo, representado

pelo caractere apóstrofo (‘).

Jamais utilize dois campos com o mesmo nome. Caso tenha necessidade,

utilize nomes seriados, como FONE1, FONE2, FONE3, VALOR1, VALOR2,

VALOR3 e assim por diante.

Não utilize nomes de campos que se pareçam com nomes de endereços de

células, como A1, AX34, IV8000, para não gerar confusão e inconsistências.

Adiante segue a relação completa dos registros a serem utilizados na tabela

de base de dados de exemplo.

Considera-se uma planilha de vendas onde estão registrados os movimentos

de vendas realizados pelos vendedores da loja. Assim sendo, os campos devem ser

escritos como estão apresentados, lembrando que os rótulos de identificação são

Vendedor, Depto., Data Venda, Produto e Valor, os quais devem estar definidos a

partir da linha 3 da planilha, ou seja, respectivamente nas células A3:, B3:, C3:, D3

e E3:.

AN02FREV001/REV 4.0

43

Vendedor Depto. Data Venda Produto Valor

André Luiz Informática 08/07/10 Pentium Dual Core 1.500,00

José Augusto Informática 08/07/10 Placa de Som 467,00

André Luiz Informática 17/10/10 Hard Disk 1 Tb 335,00

Antônio Carlos Ótica 17/10/10 Óculos de Sol 120,00

João Carlos Informática 17/10/10 Hard Disk 1 Tb 335,00

José Augusto Informática 17/10/10 Pack DVD 9,90

Maria Izabel Ótica 17/10/10 Lente de Contato 150,00

Ana Maria Ótica 12/10/10 Óculos de Gráu 8,50

João Carlos Informática 12/10/10 Pentium Dual Core 1500,00

Solange Brandão Informática 09/10/10 Pack DVD 9,90



Carlos Brandão Ótica 05/09/10 Óculos de Sol 150,00

André Luiz Informática 17/08/10 Hard Disk 450 Gb 450,00

Ana Maria Ótica 15/08/10 Lente de Contato 90,00

Solange Brandão Informática 15/08/10 Pentium Dual Core 1.500,00

Ana Maria Ótica 12/08/10 Lente de Contato 120,00

José Augusto Informática 10/08/10 Placa de Som 533,00

André Luiz Informática 07/08/10 Pentium Dual Core 1.500,00

Carlos Brandão Ótica 05/08/10 Lente de Contato 120,00


Maria Izabel Ótica 15/07/10 Óculos de Grau 85,00

João Carlos Informática 03/07/10 Pack DVD 9,90

Após ter escrito as informações anteriores na planilha, grave-a com o nome

Relatório de Vendas. Procure deixá-la visualmente semelhante à planilha da Figura

16.

AN02FREV001/REV 4.0

44

FIGURA 16 – BASE DE DADOS PARA O RELATÓRIO DE VENDAS

FONTE: Arquivo pessoal do autor (Renato Nicodemos).

9.1 A CLASSIFICAÇÃO DE REGISTROS

Quando utiliza-se uma base de dados, geralmente há necessidade de

classificá-la em uma certa ordem. Para que isso seja possível, é necessário deixar o

cursor posicionado em qualquer célula da base de dados.

AN02FREV001/REV 4.0

45

Não há necessidade de selecionar a faixa de dados de uma listagem no Excel 2010. Sempre que for utilizar algum comando baseado em dados, não precisa selecionar nada; basta deixar o cursor em qualquer célula pertencente à base de dados.

Procedimentos práticos:

Passo 01: Deixe o cursor em qualquer célula da faixa de células A2:E24, por

exemplo posicione-o na célula A2:, ou então selecione toda a faixa de células, o que

de fato não é necessário.

Passo 02: Clique na Guia “Dados”, Grupo “Classificar e Filtrar”, Botão

“Classificar”.

Após a execução do comando de classificação, surge a caixa de diálogo

“Classificar”, mostrado na Figura 17.

FIGURA 17 – CAIXA DE DIÁLOGO CLASSIFICAR


Observe que, assim que a Caixa de Diálogo “Classificar” é apresentada, a faixa de células marcada desconsidera automaticamente a primeira linha, que é linha de títulos dos campos. Isso ocorre porque essa linha não pode ser classificada como as demais, uma vez

AN02FREV001/REV 4.0

46

que está ativada a opção “Meus dados contém cabeçalhos”.

Para efetivar o processo de classificação em ordem ascendente dos dados

selecionados, o Excel 2010 usa a seguinte ordem:

1 - Células vazias;

2 - Números;

3 -Texto;

4 - Valores de erro;

5 - Valores lógicos.

A classificação em ordem descendente inverte a ordem anterior, com

exceção das células vazias que sempre são classificadas por último. Caso o

resultado não seja o esperado, examine o tipo dos dados que está classificado.

Para obter melhores resultados, certifique-se de que todas as células de

uma coluna contenham os mesmos tipos de dados. Essa caixa possui como recurso

as seguintes opções:

Adicionar Nível: Botão que permite a inclusão de mais níveis de classificação

até o limite de 64 chaves classificatórias.

Excluir Nível: Botão que retira algum nível em qualquer ponto da

classificação.

Copiar Nível: Botão que copia parâmetro referente ao nível selecionado,

ajudando em escolhas muito próximas em que apenas os detalhes se

diferenciam.

AN02FREV001/REV 4.0

47

Mover para Cima/Mover para Baixo: Botões com setas que possibilitam

mudar os níveis de classificação, atribuindo maior ou menor prioridade aos

campos.

Opções...: Botão que apresenta a caixa de diálogo “Opções de

Classificação”, que permite alterar a orientação da ordem de classificação,

que pode ser “De cima para baixo” ou “Da esquerda para a direita”. É

possível determinar a ordem de classificação da primeira chave. Permite

também estabelecer uma distinção entre letras maiúscula e minúscula.

Meus Dados Contêm Cabeçalhos: Opção de seleção ou não que pode ser

expandida a todos os dados de uma listagem. Quando selecionada, a área de

Nome de Campos é desconsiderada da base de dados.

Classificar em: Caso tenha formatado manualmente ou condicionalmente um

intervalo de células ou tabela de coluna pela cor da célula ou da fonte, pode

classificar por estas cores. Também é possível por um conjunto de ícones

criados por meio da formação condicional.

Ordem: Permite escolher a forma de classificação dos dados, está vinculada

diretamente a “Classificar em”, pois se ambas forem Valores, a escolha

acontece na ordem crescente ou decrescente. Caso seja feita por “Cor da

Célula”, “Cor da Fonte” ou “Ícone de Célula”, outros argumentos de

classificação devem ser apontados.


Passo 01: Com a Caixa de diálogo “Classificar” em uso, certifique-se de

que a área “Classificar por” esteja com o campo “Vendedor” selecionado com a

opção “De A a Z”, como mostra a Figura 18. Em seguida acione o botão “OK”.

Nesse momento a tabela deve ser classificada com os nomes em ordem alfabética

(crescente), conforme a Figura 19.

AN02FREV001/REV 4.0

48

FIGURA 18 – CAIXA DE DIÁLOGO CLASSIFICAR COM A OPÇÃO “CLASSIFICAR

POR” SELECIONADA


AN02FREV001/REV 4.0

49

FIGURA 19 – CLASSIFICAÇÃO DOS REGISTROS POR MEIO DO CAMPO VENDEDOR


Passo 02: Mantenha o cursor posicionado em qualquer célula da faixa de células

A2:E24.

Passo 03: Clique na Guia “Dados”, Grupo “Classificar a Filtrar”, Botão “Classificar”.

AN02FREV001/REV 4.0

50

Ao ser apresentada a caixa de diálogo “Classificar”, observe que a chave

primária indicada na opção “Classificar por” está posicionada em Vendedor.

Passo 04: Dê um clique no Botão sobressalente drop-down do botão

seleção “Classificar por” e selecione o título “Dpto.” em “De A a Z”.

Passo 05: Clique no Botão “Adicionar Nível” para que apareça uma nova

chave de classificação; surge o Botão “E depois por”. Selecione para esse segundo

botão o título “Vendedor” e selecione “De Z a A”, como ilustra a Figura 20.

FIGURA 20 – APRESENTAÇÃO DA CAIXA DE DIÁLOGO “CLASSIFICAR” COM OS

PARÂMETROS DE CLASSIFICAÇÃO PARA AS DUAS CHAVES


Em seguida acione o Botão “OK” e veja na Figura 21 o resultado obtido.

AN02FREV001/REV 4.0

51

FIGURA 21 - CLASSIFICAÇÃO EFETUADA COM NOVOS PARÂMETROS


INSERIR SIMULAÇÃO DE AMBIENTE AQUI

AN02FREV001/REV 4.0

52

9.2 AS OPERAÇÕES DE BUSCA E TROCA

Outro recurso presente em uma base de dados são as operações de busca

e troca de informações para atualizar dados e/ou efetuar correções.

A seguir, desenvolveremos um Estudo de Caso para aprender a utilizar o

recurso de Busca e Troca do Excel 2010.

Estudo de Caso 01:

As operações de vendas da vendedora Ana Maria foram acidentalmente

cadastradas para a vendedora Maria Izabel. Assim sendo, é necessário alterar o

nome Maria Izabel para Ana Maria. Observe os procedimentos a seguir.

Passo 01: Selecione a faixa de células A2:E24 ou deixe o cursor

posicionado na célula A2.

Passo 02: Clique na Guia “Página Inicial”, Grupo “Edição”, Botão

“Localizar e Selecionar”, Opção “Substituir.”.

FIGURA 22 – CAIXA DE DIÁLOGO LOCALIZAR E SUBSTITUIR


AN02FREV001/REV 4.0

53

Passo 03: No campo “Localizar:”, escreva o nome Maria Izabel, depois posicione o

cursor no campo “Substituir por:” Ana Maria. A Figura 23 mostra a Caixa de

diálogo “Localizar e substituir” com as definições das informações a serem

trocadas.

FIGURA 23 – CAIXA DE DIÁLOGO LOCALIZAR E SUBSTITUIR COM DADOS A

SEREM TROCADOS


Passo 04: Acione o Botão “Substituir tudo” e observe que o nome do

vendedor Maria Izabel foi trocado por Ana Maria. Assim que a operação é

efetivada, aparece uma caixa de advertência informando a execução da ação, como

indica a Figura 24. Para fechar o recurso acione o botão “OK” da caixa de

advertência e acione também o Botão “Fechar” da Caixa de diálogo “Localizar e

substituir”.

FIGURA 24 – CAIXA DE ADVERTÊNCIA


AN02FREV001/REV 4.0

54

A Caixa de diálogo Localizar e substituir , quando em uso, apresenta os seguintes

botões de ação:

Substituir tudo: permite a localização e substituição de todas as ocorrências

dos caracteres especificados no campo “Localizar” pelos caracteres

especificados no campo “Substituir por” de uma única vez.

Substituir: efetua a substituição dos caracteres pesquisados na célula ativa

pelos novos caracteres e então localiza a próxima ocorrência dos caracteres

pesquisados. A tecla <SHIFT> pode ser usada para inverter a direção da

pesquisa. Para a substituição dos caracteres sem localizar a próxima célula,

mantenha a tecla <CTRL> pressionada enquanto é dado o clique no botão

“Substituir”.

Localizar tudo: localiza na lista de ocorrência de dados a informação

desejada e que deve ser tratada, sem pular até a sua devida localização,

como ocorre com “Localizar próxima”.

Localizar próxima: encontra a próxima ocorrência dos caracteres informados

no campo “Localizar”. Se a tecla <SHIFT> estiver pressionada, quando der o

clique no botão “Localizar próxima”, é possível localizar a ocorrência

anterior.

Opções: permite determinar se as palavras escritas devem coincidir letras

maiúsculas e minúsculas, bem como o seu conteúdo íntegro, formatações etc.

AN02FREV001/REV 4.0

55

9.3 OS RELATÓRIOS DE UMA BASE DE DADOS

Existe uma forma de fazer pesquisa em uma base de dados usando a

filtragem dos dados por meio de avaliações condicionais. Para usar esse recurso, é

necessário estar com a base de dados selecionada.


Passo 01: Com o cursor posicionado em qualquer uma das células da faixa A2:E24,

preferencialmente na célula A2:, execute o passo 02.

Passo 02: Clique na Guia “Página Inicial”, Grupo “Edição”, Botão “Classificar e

Filtrar”, Opção “Filtro”. Aparecerá ao lado direito de cada campo (coluna) um

botão do tipo drop-down, indicado na Figura 25. A partir desse instante, o recurso de

filtragem de dados pode ser utilizado. Dê um clique no botão de filtragem do campo

do Depto. Observe a Figura 26.

AN02FREV001/REV 4.0

56

FIGURA 25 – BOTÕES DE FILTRAGEM À DIREITA DO NOME DOS CAMPOS


AN02FREV001/REV 4.0

57

FIGURA 26 – BOTÃO DROP-DOWN NO CAMPO DEPARTAMENTO


Passo 03: Selecione a opção “Informática” para que seja desativada,

ficando semelhante ao que exibe a Figura 27.

AN02FREV001/REV 4.0

58

FIGURA 27 – OPÇÃO ÓTICA ATIVADA


Passo 04: Finalize a seleção acionando o Botão “OK” e veja o resultado

obtido na Figura 28.

AN02FREV001/REV 4.0

59

FIGURA 28 – RESULTADO DA PESQUISA


Passo 05: Para mais um teste, selecione somente os registros do vendedor

Carlos Brandão. A Figura 29 mostra o resultado da pesquisa.



Passo 06: Retorne o campo “Vendedor” para a definição “Selecionar tudo”.

Perceba que a partir do uso desse recurso é possível obter resposta para a pergunta: - Quais são os venderes que venderam computadores?

AN02FREV001/REV 4.0

60

Passo 07: No botão de filtragem do campo “Produto” selecione a opção

“Filtro de Texto”, conforme a Figura 30.

FIGURA 30 – LISTA DE OPÇÕES REFERENTES AOS FILTROS DE TEXTO


Passo 08: Nesse momento escolha a opção “Começa com...”, surge então

a Caixa de diálogo “Personalizar AutoFiltro”, como ilustra a figura 31, e observe o

detalhe dos recursos de operação lógica de conjunção (E) e de disjunção inclusiva

(OU).

AN02FREV001/REV 4.0

61

FIGURA 31 – CAIXA DE DIÁLOGO PERSONALIZAR AUTOFILTRO


Passo 09: No campo onde se encontra posicionado o cursor, escreva o

rótulo “Pent” em seguida acione o botão “OK”. A Figura 32 apresenta o resultado

dessa ação. Note que são apresentados os vendedores que venderam

computadores.

FIGURA 32 – RESULTADO DA PESQUISA DE TODOS OS PRODUTOS QUE

COMEÇAM COM “PENT”


AN02FREV001/REV 4.0

62

9.4 OS EXTRATOS DE UMA BASE DE DADOS

A Extração de dados é um recurso de pesquisa que diferentemente do filtro

faz a extração dos dados para outra área da planilha. Para que esse recurso

funcione, é necessário criar:

Uma área de critério (em que são estabelecidas as condições);

Uma área de extração (em que os dados são apresentados).

Essas áreas devem possuir os mesmos campos da lista da base de dados.


Passo 01: Selecione a faixa de células A1:E1.

Passo 02: Clique na Guia “Página Inicial”, Grupo “Área de

Transferência”, Botão “Copiar”.

Passo 03: Posicione o cursor na célula G2: em seguida clique na Guia

“Página Inicial”, Grupo “Área de Transferência”, Botão “Colar”.

Passo 04: Posicione o cursor na célula G10: Clique na Guia “Página

Inicial”, Grupo “Área de Transferência”, Botão “Colar”.

AN02FREV001/REV 4.0

63

Passo 05: Posicione o cursor na célula G1: e escreva o título de

identificação Área de Critério. Em seguida posicione o cursor na célula G9: e

escreva Área de Extração. A Figura 33 mostra como deve ser a tela após a

colagem e a definição dos títulos.

FIGURA 33 – NOVAS ÁREAS COLADAS


Para que esse comando funcione, é necessário sempre manter o cursor em qualquer parte dentro da base de dados, ou seja, no exemplo apresentado o cursor deve estar posicionado em

qualquer célula da faixa A2:E24. Preferencialmente deixe o cursor na célula A#.

Passo 06: Definida as duas áreas, sendo a de critério e a de extração,

mantenha o cursor em qualquer parte dentro da base de dados.


“Avançado”.

É apresentada a Caixa de diálogo “Filtro avançado”, como mostra a Figura

34, indicando a área da seleção da base de dados.

AN02FREV001/REV 4.0

64

FIGURA 34 – CAIXA DE DIÁLOGO FILTRO AVANÇADO


Passo 08: Na Caixa de diálogo “Filtro avançado” selecione a opção

“Copiar para outro local” e no campo “Intervalo de critérios” informe a área

$G$3:$K$4. Note que, além dos títulos da área de critérios, está sendo selecionada

mais uma linha para colocação dos dados a serem pesquisados. O tamanho da área

de critério pode ser ajustado de acordo com as necessidades.

Passo 09: Agora no campo “Copiar para” informe a área $G$10:$K$10 e

para finalizar a ação, pressione o botão “OK”. A Figura 35 mostra as definições

solicitadas.

AN02FREV001/REV 4.0

65

FIGURA 35 – CAIXA DE DIÁLOGO FILTRO AVANÇADO COM OPÇÕES DE

CRITÉRIO E EXTRAÇÕES JÁ PREENCHIDAS


FIGURA 36 – RESULTADO DA EXTRAÇÃO


AN02FREV001/REV 4.0

66

Para a área de critério foram definidas quatro linhas, sendo uma com o

nome dos campos e as outras três para critérios de pesquisa a serem implantados.

Como as linhas de dados de critério estão em branco, à extração ocorreu em

todos os registros. Suponha que queira pesquisar todas as ocorrências de vendas

efetuadas pela vendedora Ana Maria.

Passo 10: Informe para a célula G3: o rótulo Ana Maria.


“Avançado”.

Passo 12: Selecione a opção “Copiar para outro local” e acione o Botão

“OK”. A Figura 37 mostra o resultado da pesquisa.



AN02FREV001/REV 4.0

67

9.5 COMO UTILIZAR PESQUISA DO TIPO “E”

A pesquisa do tipo “E” é baseada em operações lógicas de conjunção. Para

executar o operador lógico “E”, os dados definidos na área de critério devem ser

informados na mesma linha.

Para um teste dessa operação será executada uma pesquisa do tipo “E”,

por exemplo, para listar os produtos Hard Disk vendidos pelo vendedor André Luiz.

Note que o vendedor e o produto vendido são dados que pertencem a campos diferentes e por esta razão usa-se o

conceito “E”.


Passo 01: Escreva a célula G3: o nome do vendedor André Luiz, e na célula J3:

escreva o rótulo Hard Disk.

Passo 02: Clique Guia “Dados”, no Grupo “Classificar e Filtrar”, Botão

“Avançado”. Selecione a opção “Copiar para outro local” e acione o Botão “OK”.

Observe a Figura 38.

AN02FREV001/REV 4.0

68

FIGURA 39 – RÓTULOS A SEREM PESQUISADOS COMO CONDIÇÃO “E”


9.6 COMO UILIZAR A PESQUISA DO TIPO “OU”.

A pesquisa de tipo “OU” é baseada em operações lógicas de disjunção

inclusiva. Para executar o operador lógico “OU”, os dados definidos na área de

critério devem ser informados em mais de uma linha.

Para testar essa operação será executada uma pesquisa do tipo “OU”, por

exemplo, para listar os produtos vendidos pelos vendedores André Luiz e José

Augusto.

AN02FREV001/REV 4.0

69

Note que os vendedores são dados que pertencem ao mesmo campo e por esta razão quando se deseja listar dados diferentes de mesmo campo, usa-se o conceito “OU”.


Passo 01: Posicione o cursor na célula G4:, escreva o nome do vendedor

José Augusto. Não se esqueça de remover o conteúdo da célula J3:


“Avançado”.

Passo 03: Selecione na Caixa de diálogo “Filtro avançado” a opção

“Intervalo de critérios”, altere também em intervalo de critério o endereço da

faixa de critério de $G$3:$K$4 para $G$3:$K$5, como mostra a Figura 40.

FIGURA 40 – FILTRO AVANÇADO COM NOVA ÁREA DE CRITÉRIO


Passo 04: Em seguida acione o Botão “OK” e observe na área de extração da

planilha a apresentação dos dados consultados, conforme a Figura 41.

AN02FREV001/REV 4.0

70

FIGURA 41 – RESULTADO DA EXTRAÇÃO


Quando se deseja usar condições do tipo “OU”, é necessário estabelecer mais linha na área de critério para que seus dados possam ser pesquisados.

AN02FREV001/REV 4.0

71

9.7 USO DE CAMPO CALCULADO NA EXTRAÇÃO DE DADOS

O campo calculado no Excel 2010 caracteriza-se como uma célula em que

se cria uma fórmula lógica que pode auxiliar as operações de extração de dados

relacionados ao uso de valores numéricos.

Na planilha em foco será criado um “campo calculado” no sentido de

auxiliar as operações imputadas nos campos “Valor” e “Data Venda”.

As informações obtidas a partir de um campo calculado podem ser extraídas

com as seguintes operações relacionais:

= (igual a)

> (maior que)

< (menor que)

>= (maior ou igual a)

<= (menor ou igual a)

< > (diferente de)

Além disso, também é possível cercar faixa de valores, como listar todos os

valores entre 5.000 e 9.000, listar todas as vendas efetuadas em outubro e assim

por diante com aplicações de operações lógicas de conjunção e disjunção inclusiva.

Vamos aos procedimentos:


Passo 01: Posicione o cursor na célula da planilha L2:, escreva o rótulo de

identificação “Calculado”.

AN02FREV001/REV 4.0

72

Com o novo campo definido, basta estabelecer as fórmulas lógicas a serem

utilizadas para avaliação dos critérios de pesquisa. Quando uma fórmula lógica é

estabelecida, ela retorna como resultado a indicação VERDADEIRO ou FALSO e

deve sempre estar referenciada ao primeiro registro da lista, não importa a condição

utilizada. Não se esqueça de limpar a região de células de G3: até G4:.

Por exemplo, imagine que haja a necessidade de extrair todos os registros

cujas vendas tenham sido superior ao valor de R$ 1.000,00. Para tanto, posicione o

cursor na célula L3 e digite a fórmula lógica =E2>1000.

Note que se obtém como resultado o valor lógico FALSO, que indica que o primeiro registro da lista está fora deste critério estabelecido.

Na sequência, basta realizar a extração para obter todos os registros que

satisfaçam a condição estabelecida no campo calculado. Assim sendo, execute o

comando:


“Avançado”.

Passo 03: Selecione a opção “Copiar para outro local” e no campo

“Intervalo de critérios” informe o endereço $G$3:$L$3 (este endereço considera o

campo calculado como parte do critério). A Figura 42 mostra como deve ficar a

Caixa de diálogo “Filtro avançado”.

Passo 04: Acione o Botão “OK” e compare com a Figura 43 que exibe o

resultado da extração dentro do critério determinado, bem como a apresentação da

fórmula lógica com o valor de retorno.

AN02FREV001/REV 4.0

73

FIGURA 42 – CAIXA DE DIÁLOGO FILTRO AVANÇADO COM DEFINIÇÃO DE

NOVA ÁREA DE CRITÉRIO PARA CAMPO CALCULADO


FIGURA 43 – RESULTADO DA EXTRAÇÃO E APRESENTAÇÃO DA FÓRMULA

LÓGICA

FONTE: Arquivo pessoal do auto (Renato Nicodemos).

AN02FREV001/REV 4.0

74

9.8 USO DE SUBTOTAIS

Para o uso do recurso de Subtotais no Excel 2010, é imprescindível manter

a base de dados organizada segundo a ordem em que se deseja obter um subtotal.

Neste caso, a base de dados deve estar ordenada pelos nomes dos vendedores.

Refaça a execução desse comando, garantindo que a base de dados esteja assim ordenada por nome de vendedores.



“Classificar”.

Passo 02: Quando apresentada a Caixa de diálogo “Classificar” por meio do

Botão “Excluir Nível”, remova as colunas denominadas “E depois por”, deixando

apenas a coluna com nível “Classificar por” como “Vendedor”. Observe a Figura

44:

FIGURA 44 – CAIXA DE DIÁLOGO CLASSIFICAR


AN02FREV001/REV 4.0

75

Passo 03: Após a ordenação alfabética pelo Campo “Vendedor”,

mantenha o cursor posicionado na área de base de dados.

Passo 04: Clique na Guia “Dados”, Grupo “Estrutura de Tópicos”, Botão

“Subtotal”. Observe a Figura 45:

FIGURA 45 – CAIXA DE DIÁLOGO SUBTOTAIS


Essa caixa apresenta as seguintes características:

A cada alteração em: local em que se determina por qual campo é feito o

subtotal.

Usar função: indica a função estatística a ser usada em cada intervalo de

subtotal. O padrão é Soma, mas também pode ser Média, Máximo etc.

AN02FREV001/REV 4.0

76

Adicionar subtotal: nesse local é (são) determinado(s) qual (is) o(s)

campo(s) a ter(em) abaixo um subtotal. Normalmente, é atribuído à última

coluna, pois ela trata de valores totais.

Substituir subtotais atuais: quando ligada, substitui subtotais que já tenham

sido calculados anteriormente.

Quebra de página entre grupos: esse recurso é muito interessante, pois a

cada mudança de dados de um mesmo campo, ele automaticamente quebra

a página, facilita a impressão de um relatório por grupo de dados, ou seja, a

cada campo “Vendedor”, por exemplo.

Resumir abaixo dos dados: é a colocação do total geral de todos os grupos

abaixo destes; caso contrário, o total geral é colocado como a primeira

informação da base de dados, abaixo somente dos nomes de campos.

Remover todos: quando se deseja desligar o recurso aplicado na base.

Passo 05: Mantendo-se as informações apresentadas, acione o Botão “OK” e

observe como fica a apresentação das informações da base de dados na Figura 46.

AN02FREV001/REV 4.0

77

FIGURA 46 – PLANILHA COM OS SUBTOTAIS ATIVADOS


Observe que à esquerda aparecem três novos botões numerados como 1,2 e 3.

Passo 06: Clique em cada botão para obter a seguinte relação de visão:

- Botão 1: Visão Executiva, pois nela obtém-se o total geral da listagem com

a qual se está trabalhando.

- Botão 2: Visão Gerencial, que mostra somente os totais dos elementos

existentes na listagem.

AN02FREV001/REV 4.0

78

- Botão 3: Visão Operacional, pois mostra a listagem dos elementos e

simultaneamente seus respectivos totais.

Quando se trabalha com o Botão 2 ativado, o Excel 2010 passa a apresentar vários botões com o sinal + (adição ou mais) à esquerda da visão dos subtotais.

Passo 07: Acione o Botão 2. Clique, por exemplo, no botão + referente ao

vendedor Ana Maria e repare que são apresentados apenas os dados desse

vendedor, observe a Figura 47

FIGURA 47 – VISÃO A PARTIR DO BOTÃO 2


AN02FREV001/REV 4.0

79

Passo 08: Para que sejam desfeitos os subtotais de uma planilha, basta

posicionar o cursor na área de base de dados e, em seguida, clicar na Guia

“Dados”, Grupo “Estrutura de Tópicos”, Botão “Subtotal”, selecionar o Botão

“Remover todos”.

9.9 AS FUNÇÕES DE BASE DE DADOS

É importante que você entenda de forma clara o uso das funções =SOMA( ),

=MÉDIA( ), =MÁXIMO( ), =MÍN( ), entre outras, pois ajudarão bastante na

compreensão desta seção de estudos, na qual abordará os estudos das funções

exclusivas para a manipulação de base de dados.

As funções dessa categoria são idênticas às demais, tendo como diferença o

prefixo “BD”. Uma função de base de dados possui como sintaxe o formato:

=BDfunção(banco;campo;critério) ,

Onde:

BDfunção É uma das funções: =BDSOMA, =BDMÉDIA, =BDMÁX,

=BDMÍN, entre outras.

Banco Faixa de células ou o nome de uma faixa que contém o endereço

do banco de dados e o nome do campo.

Campo

É o número da coluna que contém o campo a ser calculado. A

primeira coluna sempre é 1 (um) , a segunda 2 (dois) e assim por

diante. Este pode ser um número inteiro positivo a partir de um, ou

o endereço de uma célula ou fórmula que retorne como resultado

o número (inteiro positivo) de deslocamento de uma coluna.

Critério

É a faixa de células que contém o critério de procura de, no

mínimo, duas linhas. A primeira linha da faixa de critério deve ter

os nomes exatos dos campos da base de dados. A partir da

segunda linha, deve ser informado um ou mais critérios como

condição para selecionar os dados a serem utilizados pela função.

AN02FREV001/REV 4.0

80


Passo 01: Acrescente à planilha Relatório de Vendas as seguintes linhas:

1. Na célula A27: digite “Total de venda”. 2. Na célula A28: digite “Média da venda”. 3. Na célula A29: digite “Maior venda”. 4. Na célula A30: digite “Menor venda”.

Passo 02: Posicione o cursor na célula B27: e escreva a função de base de dados

=BDSOMA(A3:E26;5;G3:K4). Repita a operação para as demais linhas, ajustando

cada função a sua execução particular. Assim sendo, utilize para:

1. a média, na célula BD30: a função =BDMÉDIA(A3:E26;5;G3:K4); 2. o maior valor, a função =BDMÁX(A3:E26;5;G3:K4) em B31:; 3. o menor valor, a função =BDMÍN(A3:E26;5;G3:K4) em B32:; No final, a aparência da planilha deve ser semelhante à Figura 48.

AN02FREV001/REV 4.0

81

FIGURA 48 –TELA DA PLANILHA APÓS A INSERÇÃO DAS FUNÇÕES DE BANCO

DE DADOS.


Passo 03: Agora que todas as funções foram definidas, indique na área de

critério a informação que deseja calcular. Por exemplo: “Quanto vendeu o

vendedor João Carlos?” Posicione o cursor na célula G3: e escreva “João

Carlos”, depois limpe a célula L3: e observe na Figura 49 o resultado da operação

com as funções de base de dados.

Não se esqueça de salvar a planilha após as últimas ocorrências.

AN02FREV001/REV 4.0

82

FIGURA 48 – NOVOS RESULTADOS EM FUNÇÃO DE NOVOS CRITÉRIOS


AN02FREV001/REV 4.0

83

10 EXERCÍCIOS

Agora chegou o momento de fazermos alguns exercícios para

fixarmos o conteúdo estudado neste segundo módulo. Procure desenvolver

todos os exercícios propostos e peça auxílio sempre que necessitar ao seu

Tutor.

Questão 01: O que é base de dado?

Questão 02: Quantas chaves de classificação são possíveis utilizar no

Microsoft Office Excel?

Questão 03: Quantos e quais são os parâmetros a serem utilizados na

função de base de dados?

Questão 04: O que faz a função BDMÉDIA?

Questão 05: Descreva uma área de critérios e sua finalidade.

Questão 06: O que é área de extração?

Questão 07: O que faz a função lógica E e qual sua finalidade?

Questão 08: Para que servem as funções DIA, MÊS E ANO?

Questão 09: Qual a finalidade da função

E(C3>=DATA(08;6;1);C3<=DATA(08;;6;20)?

Questão 10: Como se utilizam os recursos de subtotais do Microsoft Office Excel?

FIM DO MÓDULO II

Documents

Excel 2010 Avançado Mod 02 Rev