Baixe o app para aproveitar ainda mais
Prévia do material em texto
3/7/2015 1 BANCO DE DADOS BASE DE DADOS: UMA BASE DE DADOS ou (BANCO DE DADOS) é um conjunto de registros (DADOS) agrupados e organizados com um critério preestabelecido. Esses registros podem ser classificados, pesquisados, alterados ou apagados. Com o programa Microsoft Excel é possível: manipular registros. trabalhar com funções específicas para banco de dados . usar filtros 3/7/2015 2 Características De Uma Base De Dados Para utilizar uma estrutura de banco de dados é necessário considerar três aspectos importantes: 1. Uma base de dados em uma planilha é uma tabela retangular em que as linha e colunas utilizadas são continuas, ou seja, não se pode ter espaços em branco. 2. As colunas dessa tabela são consideradas os campos que, quando combinados, apresentam as informações desejadas. 3. As linhas são consideradas os registros dessa base de dados, ou seja, o conjunto de dados que podem ser avaliados. Cada coluna deve possuir um nome de identificação que indique seu conteúdo. As linhas que serão os registros devem conter o conjunto de dados que formam o banco de dados. O nome dos campos (colunas) devem ser sempre definidos como rótulos de identificação. Caso deseje colocar o nome do campo como número, não se esqueça de definir antes da entrada do caractere numérico um prefixo de rótulo (‘). Campos de mesmo nome deve ser seriado. Exemplo: número1, número2, número3. 3/7/2015 3 CAMPOS R E G I S T R O S EXEMPLO Abaixo 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 de uma loja de informática. Assim sendo, os campos, devem ser escritos como serão apresentados, lembrando que os rótulos de identificação são Vendedor, Depto., Data Venda, Produto e Valor, Quant., Total os quais devem estar definidos a partir da linha 3 da planilha, ou seja, respectivamente nas células A3 , B3 , C3 , D3 , E3 , F3 , G3. Monte a tabela a seguir. 3/7/2015 4 TABELA 7x25 Classificação De Registros Quando se utiliza uma base de dados, geralmente há necessidade de classificá-la em certa ordem. Para que isso seja possível, é necessário deixar o cursor posicionado em qualquer célula da base de dados. 3/7/2015 5 Procedimento Deixe o cursor em qualquer lugar da área da tabela (A3) Execute o comando Guia: Dados Grupo: classificar e filtrar Botão classificar. Caixa de Diálogo “Classificar” Observe que quando a caixa de diálogo é apresentada o cabeçalho da tabela é desconsiderado. Isso ocorre porque na caixa de diálogo está selecionado a opção “MEUS DADOS CONTÊM CABEÇALHOS” 3/7/2015 6 RECURSOS DA CAIXA DE DIÁLOGO CLASSIFICAR Botão que permite a inclusão de mais níveis de classificação até o limite de 64 chaves classificatórias Botão que retira algum nível em qualquer ponto da classificação Botão que copia parâmetros referentes ao nível selecionado. Quando selecionada, a área de nome de campos é desconsiderada da base de dados Botão que apresenta a caixa de dialogo opções de classificação Altera a ordem De cima p/ baixo Da dir. p/ esquerda Permite escolher a coluna segundo a qual se deseja classificar os dados Utilizado quando se classifica uma célula condicionalmente. Permite escolher a forma de classificação dos dados “Crescente ou decrescente” “Ordem alfabética” Move para cima E Move para baixo Os níveis Coloque a área classificar por com o campo vendedor. selecione a opção de A para Z, e dê OK. 3/7/2015 7 Observe que o campo vendedor ficou com os nomes em ordem alfabética. Exemplo II Classificar a base de dados pelo campo DEPARTAMENTO como primeira chave e, dentro desse campo , o campo VENDEDOR, como segunda chave de classificação. Mantenha o cursor em qualquer ponto da tabela Execute o comando Guia: Dados Grupo: Classificar e Filtrar Botão: Classificar 3/7/2015 8 Observe que a chave primária indicada na opção CLASSIFICAR POR está posicionada em VENDEDOR. Em CLASSIFICAR POR selecione o título DPTO. Em Ordem selecione de A a Z. Clique no botão ADICIONAR NÍVEL para que apareça uma nova chave de classificação; No botão e E depois por. Selecione Vendedor de Z a A Acione o botão OK 3/7/2015 9 Caixa com três níveis Observe a nova ordem de classificação! 3/7/2015 10 Retorne a planilha a ordem de classificação anterior com apenas duas chaves de classificação. Classificação Baseada em Cores No Excel, é possível estabelecer regras para uma série de situações, de acordo com as necessidades do usuário, que precisa manter o controle dessas regras. Quanto mais intuitiva forem, melhor, pois as análises ocorrerão de forma mais rápida e precisa. Parte das regras do Excel pode ser criada de maneira muito simples, sem esforço, com resultados extremamente poderosos, como é o caso da Formatação Condicional. Por meio desse recurso, é possível estabelecer uma gama imensa de controles que cerquem as possibilidades de um determinado problema e, com isso, é possível identificar o que se deseja e, possivelmente, separar essas informações. 3/7/2015 11 Formatação Condicional Exemplo: Considere que para a base de dados existente, devam-se estabelecer as regras a seguir: Valores totais acima de R$ 3.500,00 são considerados altos e têm a cor Verde. Valores totais entre R$ 1.750,00 e R$ 3.500,00 são considerados razoáveis e tem a cor Amarela. Valores totais abaixo de R$ 1.750,00 são considerados baixos e têm a cor Vermelha. Procedimento Selecione a faixa de célula G4:G26. 3/7/2015 12 Procedimento Guia: PÁGINA INICIAL Grupo: Estilo Botão: Formatação Condicional Dentre as opções listadas, opte por Realçar Regras das Células, que oferece a opção É maior do que ... Para seleção. Surge a caixa de dialogo É maior do que: Estabeleça o valor 3500 Aplique Preenchimento Verde e Texto Verde Escuro Acione OK 3/7/2015 13 Para Valores entre R$ 1.750,00 e R$ 3.500,00 use a opção Está Entre... E defina a cor Amarela; Para valores abaixo de R$1.750,00, use a opção É menor do que... E use a cor Vermelha. As regras foram aplicadas às células selecionadas e agora é possível efetuar a classificação com base em cores. Veja o resultado a seguir: 3/7/2015 14 Classificação com resultado da formatação condicional Uma vez aplicadas cores ou qualquer outra formatação às células de uma base de dados, é possível usá-las como elemento no processo de classificação. Posicione o cursor sobre qualquer parte de sua base de dados . Execute o comando: Guia: Dados Grupo: Classificar e Filtrar Botão: Classificar 3/7/2015 15 Clique no botão Excluir Nível, até excluir todos os campos Clique no campo Adicionar Nível, o que acrescenta o campo Total. 3/7/2015 16 Assim que se aplica um nível de classificação, caso haja semelhança entre os campos, convém usar Copiar Nível, para ganhar tempo na construção das regras de classificação. É importante que Classificar Em seja mudado para Cor Da Célula conforme segue: Verde no primeiro nível Vermelho no segundo nível Amarelo no terceiro nível Acione o Botão OK 3/7/2015 17 Combinação de Filtros A ordem dos valores está espalhada dentro de cada faixa de cores, não havendo uma sequência crescente ou decrescente. Para organizá-los de acordo com a necessidade, convém incluir outro campo que estabeleça esse critério na classificação.Procedimento Com o cursor em qualquer célula da base de dados, execute o comando: Guia: DADOS Grupo: Classificar e Filtrar Botão: Classificar 3/7/2015 18 Clique no terceiro nível de classificação Por meio do botão Adicionar Nível, Adicione um novo nível. Defina o campo Total. Valores deve esta selecionado para Classificar em Escolha do Menor para o Maior ou vice - versa 3/7/2015 19 Operação de Busca e Troca Um recurso importante na base de dados é a operação de busca e troca de informações que permite atualizar dados ou efetuar correções. EXEMPLO As operações de venda não incluem mais o produto Hard Disk 450 GB, pois além de oferecer menor capacidade de armazenamento, é mais caro que o outro disco rígido. Portanto serão consideradas apenas as vendas para o produto mais moderno, mais barato e com maior capacidade. Para testar esse recurso, posicione o cursor sobre qualquer célula da base de dados. Execute o comando: Guia: PÁGINA INICIAL Grupo: Edição Botão: Localizar e Selecionar Opção: Substituir... Escreva o nome do produto que você deseja substituir. Escreva o nome do produto substituto Acione o Botão Substituir Tudo 3/7/2015 20 Vamos repetir o processo de substituição para o valor dos produtos, pois o disco de 450GB era mais caro. Seu valor deve ser substituído pelo mais barato, localizando-se as ocorrências de R$ 450,00 e alterando-as para R$ 335,00. Observação: nesse caso vamos selecionar apenas a coluna E da tabela. ANTES ANTES 3/7/2015 21 DEPOIS DEPOIS A caixa de diálogo Localizar e Substituir quando em uso apresenta os seguintes botões de ação: Permite a localização e substituição de todas as ocorrências Efetua a substituição dos caractere pesquisado na célula ativa pelos novos caracteres. Localiza na lista de dados a informação desejada que deve ser tratada. Localiza a próxima ocorrência Permite determinar se as palavras escritas devem coincidir letras maiúscula e minúscula 3/7/2015 22 Faça o exercício número 2 da lista. Relatório De Uma Base De Dados (filtro) É uma forma de fazer pesquisa em uma base de dados usando a filtragem dos dados por meio de avaliações condicionais. 3/7/2015 23 Procedimento Com o cursor posicionado em qualquer área da tabela, execute o comando. Guia: Página Inicial Grupo: Edição Botão: Classificar Opção: Filtro 3/7/2015 24 Observe que do lado de cada coluna aparece um botão do tipo drop-down. A partir desse instante, o recurso de filtragem de dados pode ser utilizado. Dê um clique no botão de Filtragem do campo Dpto. Selecione a opção Informática para que seja desativada como segue. 3/7/2015 25 Observe que está selecionado apenas o Departamento de Ótica Acione o botão OK. Observe que só aparecem as vendas do departamento Ótica! 3/7/2015 26 Para mais um teste selecione apenas os registros do Vendedor João Carlos. Retorne ao campo vendedor para a definição (SELECIONAR TUDO) A partir desse recurso é possível obter resposta para a pergunta: Quais os vendedores que venderam notebooks? Quais as vendas realizadas em 18 de janeiro? Quais vendedores tiveram resultados entre R$ 1000,00 e R$ 3800,00? Quais as vendas realizadas na semana anterior a 18 de janeiro? Quais as vendas são consideradas baixas? Quantas vendas ocorreram no 1° trimestre deste ano? 3/7/2015 27 No botão filtragem do campo Produto selecione a opção filtros de texto. Selecione a opção Contém. Caixa de diálogo Personalizar Auto Filtro Escreva o rótulo “Óculos” Acione o botão OK Observe o resultado apresentado com todos os produtos que contém a palavra Óculos, que pode estar em qualquer posição, como porta- óculos, óculos de grau, óculos de sol, limpador de óculos. 3/7/2015 28 Agora vamos obter, na listagem atual, os registros com valores concentrados entre R$ 1500,00 e R$ 2500,00. Selecione o botão do campo Total, escolha a opção Filtros de Número e, da listagem de opções exibida, opte pela Está Entre... Aplique o valor 1500 Aplique o valor 2500 Acione OK 3/7/2015 29 Observe o Resultado! Desfaça o filtro em cada campo existente, sempre com o botão drop-down e a opção (Selecionar Tudo). Salve o Arquivo! Observação! Se preferir Desligue o Filtro. 3/7/2015 30 Filtragem por Cor Como as cores aplicadas à listagem também são elementos de análise, pois separam e distinguem situações e resultados, é possível utilizá-las para filtragem. Procedimento Posicione o cursor sobre qualquer célula da base de dados. Execute o comando: Guia: DADOS Grupo: Classificar e Filtrar Botão: Filtro No Campo Total, em que há a separação por cores, é que se efetua a filtragem. Escolha a opção Filtrar por Cor. 3/7/2015 31 Permite a filtragem pela cor do preenchimento Aplica a filtragem pela cor da fonte. Opção escolhida Acione OK Resultado Salve o arquivo! Desfaça todo o recurso do filtro Observação! O recurso do filtro também pode ser executado pela sequência de comandos, tanto para aplicá-los como para desligá-los. Guia: Página Inicial Grupo: Edição Botão: Classificar e Filtrar Opção: Filtro 3/7/2015 32 Extrato De Uma Base De Dados 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 de base de dados. Procedimento Copie os dados de cabeçalho conforme o exemplo a seguir: 3/7/2015 33 Definidas as duas áreas, sendo a de critério e a de extração, mantenha o cursor em qualquer parte dentro da base de dados e execute o comando. Guia: dados Grupo: Classificar e Filtrar Botão: Avançado É apresentada a caixa de diálogo a seguir: Selecione copiar para outro local e no campo intervalo de critérios informe a área $I$3:$O$4 Agora no campo (COPIAR PARA) informe a área $I$10:$O$10 em seguida dê ok. Após o acionamento do botão OK da caixa de diálogo (filtro Avançado) são apresentados todos os dados na área de extração. Isso ocorre devido à ausência de algum critério de seleção. Veja a seguir: 3/7/2015 34 Observe, Não houve critério de seleção Para a área de critério foram definidas duas linhas, sendo a primeira com o nome dos campos e a outra linha para critérios de pesquisa a serem implementados. A área de critério pode ter suas linhas alteradas a partir da necessidade de mais ou menos critérios. Como a linha de dados de critérios está em branco, a extração ocorreu para todos os registros. Suponha que queira pesquisar todas as ocorrências de vendas efetuados pela funcionária Maria Izabel. 3/7/2015 35 Informe para a Célula I4: O rótulo Maria Izabel e execute os comandos. Guia: Dados Grupo: Classificar e filtrar Botão: Avançado 3/7/2015 36 Como utilizar a pesquisa do tipo “E” Para executar o operador lógico do tipo “E”, os dados definidos na área de critério devem ser informados na mesma linha. Exemplo Listar os produtos Hard Disk 1TB vendidos pelo vendedor João Carlos. Obs. O vendedor e o produto vendido pertencem a campos diferentes, devido a isso estamos utilizando a lógica “E”. 3/7/2015 37 Procedimento Escreva nacélula I4 o nome do vendedor João Carlos e na célula L4: escreva o rótulo Hard Disk 1TB. Em Seguida execute o Comando: Guia: Dados Grupo: Classificar e filtrar Botão: Avançado Acione 3/7/2015 38 Pesquisa do tipo “OU” A pesquisa do tipo “OU” é baseada em operações lógicas. Para executar a operação “OU”, os dados definidos na área de critério devem ser informados em mais de uma linha. Exemplo Vamos utilizar a lógica “OU” Para listar os produtos vendidos pelos funcionários Maria Izabel e José Augusto. Note que os vendedores são dados diferentes de mesmo campo, usa-se o conceito “OU”. 3/7/2015 39 Procedimento Altere o nome do vendedor em I4: para Maria Izabel Posicione o cursor na célula I5: escreva o nome do vendedor José Augusto. Não se esqueça de remover o conteúdo da célula L4: e execute na sequência o comando: Guia: Dados Classificar Avançado Selecione na caixa de diálogo filtro avançado a opção Intervalo de Critérios, Altere também em intervalo de Critérios o endereço da faixa de critério p/ $I$3:$O$5. 3/7/2015 40 Observação! Quando usamos condições do tipo “OU”, é necessário estabelecer mais linhas na área de critério, para que os dados possam ser pesquisados, nunca se esquecendo de atualizá-los no intervalo de critérios. 3/7/2015 41 Uso do Campo Calculado na Extração de Dados Campo calculado 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 que estamos utilizando criaremos 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 faixas de valores, como listar todos os valores entre 5000 e 9000, listar todas as vendas efetuadas em outubro e assim por diante com aplicação de operações lógicas. 3/7/2015 42 Procedimento Posicione o cursor na célula P3: e escreva o rótulo de identificação “calculado”. 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. EXEMPLO Imagine que haja a necessidade de extrair todos os registros cujas vendas tenham sido superiores ao valor de R$ 4.000,00. Para tanto, posicione o cursor na célula P4: e digite a fórmula lógica =G4>1000. Observe que o resultado é o valor lógico Verdadeiro. Isso indica que o primeiro registro da lista está dentro deste critério. OBSERVAÇÃO! Não se esqueça de limpar a região de Células I4: até I5: para que o novo critério seja aplicado em um ambiente sem interferências anteriores. 3/7/2015 43 Em seguida execute o comando: Guia: Dados Grupo: classificar e filtrar Botão: Avançado Selecione a opção Copiar para outro local e no campo Intervalo de Critérios informe o endereço $I$3:$P$4 (esse endereço considera o campo calculado como parte do critério). Importante: deixe o campo vendedor da área de critério vazio. Acione OK 3/7/2015 44 Exemplo II Queremos obter a extração das vendas maiores que R$ 900,00 e inferiores a R$ 1200,00. Procedimento: Posicione o cursor na célula P4: e escreva a seguinte fórmula lógica: =E(G4>=900;G4<=1200) e execute o comando: 3/7/2015 45 Guia: Dados Grupo: Classificar e Filtrar Botão: Avançado Selecione a opção Copiar para outro local e acione o botão OK. 3/7/2015 46 EXEMPLO III Vamos obter a extração das vendas inferiores a R$ 1000 e das superiores a R$ 6000. Na célula P4 insira a fórmula =OU(G4<=1000;G4>=6000) Execute o comando: Guia: DADOS Grupo: Classificar e Filtrar Botão: Avançado Selecione a opção Copiar para outro local acione o botão OK 3/7/2015 47 EXTRAÇÃO EM CAMPOS COM DATAS Em relação às extrações efetivadas com campos que possuam datas, é necessário utilizar funções que manipulem esse tipo de dados, como: =dia =mês =ano =data 3/7/2015 48 EXEMPLO I Vamos realizar a extração de todos os registros de vendas efetuadas no dia 3. PROCEDIMENTO Posicione o cursor na célula P4: e escreva a fórmula lógica =DIA(C4)=3. Execute o comando. Guia: Dados Grupo: Classificar e Filtrar Botão: Avançado Selecione a opção Copiar para outro local acione o botão ok. 3/7/2015 49 EXEMPLO II Vamos cercar a apresentação de registros de uma determinada data, por exemplo, todas as vendas executadas entre o dia 1 e o dia 7. Informe na célula P4: =E(DIA(C4)>=1;DIA(C4)<=7) 3/7/2015 50 Execute o comando: Guia: Dados Grupo: Classificar e Filtrar Botão: Avançado Selecione a opção Copiar para outro local acione o botão ok. 3/7/2015 51 O Uso de Subtotais Para trabalhar com o recurso de Subtotais, é 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. Para garantir que a base de dados esteja assim ordenada execute os seguintes comandos: Guia: Dados Grupo: Classificar e Filtrar Botão: Classificar Na caixa de diálogo classificar remova as colunas denominadas “E depois por”, deixando somente com o nível Classificar por vendedor de A a Z. 3/7/2015 52 PROCEDIMENTO Faça ordenação alfabética dos nomes. 3/7/2015 53 mantenha o cursor posicionado na área de base de dados e execute o comando: Guia: Dados Grupo: Estrutura de tópicos Botão: Subtotal Caixa de diálogo Subtotais 3/7/2015 54 Caixa de diálogo subtotal Local em que se determina por qual campo é feito o subtotal 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 Nesse local são determinados quais os campos a terem abaixo um subtotal. Quando ligada, substitui subtotais que já tenham sido calculado anteriormente Esse recurso é muito interessante, pois a cada mudança de dados de um mesmo campo ele automaticamente quebra a página. É a colocação do total geral de todos os grupos abaixo destes. Quando se deseja desligar o recurso aplicado a base de dados. Acione o ok 3/7/2015 55 Observe que na esquerda apareceram três botões numerados como: 1,2,3. Visão executiva, nela obtém-se o total geral da listagem com a qual se está trabalhando Visão gerencial mostra somente os totais dos elementos existentes na listagem 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 passa a apresentar vários botões com o sinal “+” à esquerda da visão dos subtotais. Exemplo Acione o Botão 2 e clique no botão “+” referente ao vendedor João Carlos. 3/7/2015 56 Para desfazer os subtotais execute o comando Guia: Dados Grupo: Estrutura de tópicos Botão: Subtotal E selecione o botão Remover Todos. FUNÇÕES BASE DE DADOS Existem funções exclusivas para manipulação 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). 3/7/2015 57 Procedimento Acrescente na planilha Relatório de Vendas asseguintes linhas: Na célula A29: digite Total Venda Na célula A30: digite Média venda Na célula A31: digite Maior Venda Na célula A32: digite Menor Venda Posicione o cursor na célula B29: e escreva a função de base de dados : =BDSOMA(A3:G26;7;I3:P4) Repita o procedimento para as demais linhas ajustando cada função ao seu caso particular. A Média da Célula B30 =BDMÉDIA(A3:G26;7;I3:P4) O MAIOR valor EM B31 =BDMÁX(A3:G26;7;I3:P4) O MENOR valor EM B32 =BDMÍN(A3:G26;7;I3:P4) 3/7/2015 58 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 funcionário João Carlos?” Posicione o cursor na célula I4: e escreva João Carlos depois limpe a célula P4: 3/7/2015 59 CONCLUSÃO DA MATÉRIA BASE DE DADOS.
Compartilhar