Cap 04 - Excel 2010 Avançado

Prévia do material em texto

Excel 2010 – Avançado 
 
 
 
Senac São Paulo 75 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
OBJETIVOS 
 Utilizar as funções de banco de dados: BDCONTARA, 
BDEXTRAIR, BDMÁX, BDMÍN, BDMÉDIA e BDSOMA. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
76 Senac São Paulo 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 77 
Funções de BANCO DE DADOS 
Nesse capítulo você vai aprender a utilizar funções de banco de dados para manipular informações em 
planilhas com características de banco de dados. 
 
 
Atividade 1 – Utilizando as funções BDSOMA e BDMÉDIA 
 
Objetivos : • Utilizar as funções BDSOMA e BDMÉDIA. 
Tarefas : • Abrir a pasta Relatório Produtos.xlsx. 
 • Preencher um relatório com o valor total de vendas efetuadas por fornecedor. 
 • Preencher um relatório com o valor total de vendas efetuadas por fabricante. 
 • Preencher um relatório com o valor da média de vendas efetuadas por 
fornecedor. 
 • Preencher um relatório com o valor da média de vendas efetuadas por 
fabricante. 
 
A pasta Relatório Produtos.xlsx contém informações sobre itens fornecidos por vários fornecedores. Seu 
trabalho será gerar relatórios para resumir e consolidar informações sobre os fornecedores e fabricantes 
dos produtos. 
 
Função BDSOMA 
Essa função tem como objetivo somar os valores de uma coluna de acordo com critérios estabelecidos. 
• Sintaxe: BDSOMA(banco de dados;campo;critérios) 
• Argumentos: 
o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas 
e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. 
o campo: identificação da coluna que contém os dados que serão somados. Pode ser indicada 
pelo nome do campo entre aspas, o endereço da célula que contém o nome da coluna ou 
pelo número da coluna dentro da lista. 
o critérios: intervalo de células que contém a condição especificada. 
 
1. Abra a pasta Relatório Produtos.xlsx. 
Excel 2010 – Avançado 
 
 
 
 
78 Senac São Paulo 
 
 
2. O primeiro passo é somar os valores de venda por fornecedor, para isso você vai criar seus critérios 
para utilizá-los com a função BDSOMA. Selecione a planilha Critérios. 
3. Como se deseja efetuar a soma por fornecedor, os critérios devem conter a identificação do nome do 
campo (FORNECEDOR) e o conteúdo que se deseja procurar no campo. Digite os valores mostrados 
a seguir: 
 
 
 
CUIDADO! Tanto o nome do campo quanto o conteúdo a ser procurado devem estar digitados 
exatamente como na planilha onde se fará a procura. É muito comum, durante a digitação, 
inserir um espaço em branco a mais ou mesmo pressionar alguma tecla que não gera caractere 
imprimível e, na hora da procura, não se obter nenhum resultado. Se isso acontecer, digite 
novamente as informações dos critérios. 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 79 
4. Selecione a planilha Relatório e clique na célula B4. Você vai calcular a soma de vendas para o 
fornecedor 3 Manos Hipermercados. 
5. Digite: 
= BDSOMA( 
 
6. O primeiro argumento refere-se à região da planilha que será considerada como o banco de dados. 
Selecione a planilha Controle de Produtos e selecione a região A1:D24. 
= BDSOMA('Controle de Produtos'!A1:D24; 
 
7. O próximo argumento é o nome do campo que será somado. No caso, é o campo Total e que está na 
célula D1 da planilha Controle de Produtos. 
= BDSOMA('Controle de Produtos'!A1:D24;'Controle de Produtos'!D1; 
 
8. O último parâmetro é o critério para efetuar a soma. Nesse caso o critério é: Fornecedor igual a 3 
Manos Hipermercados. Esse critério está indicado na planilha Critérios na região A4:A5. 
= BDSOMA('Controle de Produtos'!A1:D24;'Controle de Produtos'!D1;Critérios!A4:A5) 
 
9. Preencha a coluna SOMA do relatório por fornecedor calculando a soma para os outros fornecedores. 
 
 
Função BDMÉDIA 
Essa função tem como objetivo calcular a média aritmética entre os valores de uma coluna de acordo com 
critérios estabelecidos. 
• Sintaxe: BDMÉDIA(banco de dados;campo;critérios) 
• Argumentos: 
o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas 
e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. 
o campo: identificação da coluna que contém os dados para os quais a média será calculada. 
Pode ser indicada pelo nome do campo entre aspas, o endereço da célula que contém o 
nome da coluna ou pelo número da coluna dentro da lista. 
o critérios: intervalo de células que contém a condição especificada. 
 
1. Agora você vai calcular a média de vendas para cada fornecedor. Para facilitar, você vai atribuir um 
nome à região da planilha Controle de Produtos que corresponde ao banco de dados, um nome à 
célula que contém a indicação do campo onde será efetuado o cálculo e nomes às regiões que contêm 
os critérios. Selecione a região A1:D24 da planilha Controle de Produtos e atribua o nome Geral. 
2. Selecione a célula D1 da planilha Controle de Produtos e atribua o nome Coltotal. 
3. Selecione a região A1:A2 da planilha Critérios e atribua o nome Carretel. 
4. Selecione a região A4:A5 da planilha Critérios e atribua o nome Manos. 
5. Selecione a região A7:A8 da planilha Critérios e atribua o nome Doces. 
6. Selecione a região A10:A11 da planilha Critérios e atribua o nome Flores. 
Excel 2010 – Avançado 
 
 
 
 
80 Senac São Paulo 
7. Selecione a planilha Relatório e clique na célula C4. Você vai calcular a média de vendas para o 
fornecedor 3 Manos Hipermercado. Digite a fórmula: 
= BDMÉDIA(Geral;Coltotal;Manos) 
 
Onde: 
Geral identificação do banco de dados. 
Coltotal célula que contém o título da coluna onde será calculada a média. 
Manos critério. 
 
8. Calcule a média de vendas para os outros fornecedores. 
9. Agora você vai calcular a soma por fabricante. Na planilha Critérios digite os critérios para os 
fabricantes: 
 
 
10. Nomeie os critérios: garoto, granja, maeterra, nestle, parmalat, renata, royal e união. 
11. Selecione a planilha Relatório e clique na célula B13. 
12. Digite a fórmula: 
=BDSOMA(Geral;Coltotal;garoto) 
 
13. Calcule a soma para os outros fabricantes. 
14. Seguindo o mesmo raciocínio utilizado para os fornecedores, calcule a média de vendas para os 
fabricantes. 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 81 
 
Atividade 2 – Utilizando as funções BDMÁX e BDMÍN 
 
Objetivo : • Deslocar e rotacionar um objeto pelo teclado. 
 
Objetivos : • Utilizar as funções BDMÁX e BDMÍN. 
Tarefas : • Preencher um relatório com o valor máximo de vendas efetuadas por 
fornecedor. 
 • Preencher um relatório com o valor máximo de vendas efetuadas por fabricante. 
 • Preencher um relatório com o valor mínimo de vendas efetuadas por 
fornecedor. 
 • Preencher um relatório com o valor mínimo de vendas efetuadas por fabricante. 
 
Função BDMÁX 
Essa função tem como objetivo retornar o valor máximo dentro de uma lista de valores de acordo com as 
condições especificadas. 
• Sintaxe: BDMÁX(banco de dados;campo;critérios) 
• Argumentos: 
o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas 
e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. 
o campo: identificação da coluna que contém os dados para os quais o valor máximo será 
calculado. Pode ser indicada pelo nome do campo entre aspas, o endereço da célula quecontém o nome da coluna ou pelo número da coluna dentro da lista. 
o critérios: intervalo de células que contém a condição especificada. 
 
Função BDMÍN 
Essa função tem como objetivo retornar o valor mínimo dentro de uma lista de valores de acordo com as 
condições especificadas. 
• Sintaxe: BDMÍN(banco de dados;campo;critérios) 
• Argumentos: 
o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas 
e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. 
o campo: identificação da coluna que contém os dados para os quais o valor mínimo será 
calculado. Pode ser indicada pelo nome do campo entre aspas, o endereço da célula que 
contém o nome da coluna ou pelo número da coluna dentro da lista. 
o critérios: intervalo de células que contém a condição especificada. 
 
1. Selecione a planilha Relatório e clique na célula D4. 
2. Os parâmetros da função BDMÁX são os mesmos das funções BDSOMA e BDMÉDIA. Digite a 
fórmula: 
= BDMÁX(Geral;Coltotal;Manos) 
 
Excel 2010 – Avançado 
 
 
 
 
82 Senac São Paulo 
3. Calcule o valor máximo de vendas para os outros fornecedores. 
4. Calcule o valor máximo de vendas para os fabricantes. 
5. Agora calcule o valor mínimo de vendas para os fornecedores. Clique na célula F4 e digite a fórmula: 
= BDMÍN(Geral;Coltotal;Manos) 
 
6. Calcule o valor mínimo de vendas para os outros fornecedores. 
7. Calcule o valor mínimo de vendas para os fabricantes. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 83 
 
Atividade 3 – Utilizando a função BDEXTRAIR 
 
Objetivos : • Utilizar a função BDEXTRAIR. 
Tarefas : • Preencher um relatório com o nome dos produtos que apresentam o valor 
máximo de vendas efetuadas por fornecedor. 
 • Preencher um relatório com o nome dos produtos que apresentam o valor 
máximo de vendas efetuadas por fabricante. 
 • Preencher um relatório com o nome dos produtos que apresentam o valor 
mínimo de vendas efetuadas por fornecedor. 
 • Preencher um relatório com o nome dos produtos que apresentam o valor 
mínimo de vendas efetuadas por fabricante. 
 
Sua planilha Relatório mostra os valores máximo e mínimo de vendas por fornecedor e por fabricante. No 
entanto você precisa também saber quais são os produtos responsáveis por essas vendas. Para isso você 
utilizará a função BDEXTRAIR. 
 
Função BDEXTRAIR 
Essa função tem como objetivo retornar um único valor em uma coluna de uma lista que coincida com as 
condições especificadas. 
• Sintaxe: BDEXTRAIR(banco de dados;campo;critérios) 
• Argumentos: 
o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas 
e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. 
o campo: identificação da coluna que contém os dados para a extração do valor desejado. 
Pode ser indicado pelo nome do campo entre aspas, o endereço da célula que contém o 
nome da coluna ou pelo número da coluna dentro da lista. 
o critérios: intervalo de células que contém a condição especificada. 
 
1. Você vai procurar no banco de dados pelos produtos responsáveis pelas maiores e menores vendas 
por fornecedor. O seu critério agora é selecionar o fornecedor e o valor da sua maior venda. Para isso 
você deve modificar sua planilha de critérios. Selecione a planilha Critérios e clique na célula B1. 
2. Nessa célula você vai digitar o nome do campo que contém o valor da maior venda do fornecedor 
Carretel Hipermercados na planilha Controle de Produtos. Esse campo é o campo de nome Total. 
Portanto, na célula B1 digite: 
Total 
 
3. Selecione a célula B2. Nessa célula você vai indicar o valor da maior venda efetuada pelo fornecedor 
Carretel Hipermercados. Esse valor está sendo calculado na planilha Relatório na célula D5. Portanto, 
na célula B2 da planilha Critérios, digite: 
= Relatório!D5 
 
4. Modifique os critérios para os outros fornecedores. 
Excel 2010 – Avançado 
 
 
 
 
84 Senac São Paulo 
 
 
5. Selecione a planilha Relatório e clique na célula E4. 
6. Digite a seguinte fórmula: 
= BDEXTRAIR(Geral;'Controle de Produtos'!A1;Critérios!A4:B5) 
 
Note que a coluna a ser considerada nessa função é a que contém o nome do produto na planilha Controle 
de Produtos (célula A1), e a região que indica o critério agora contém o campo Valor. 
7. Mostre os nomes dos produtos de maior valor para todos os fornecedores. 
8. Modifique a planilha Critérios para que você possa mostrar os nomes dos produtos de menor valor 
para todos os fornecedores: 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 85 
9. Preencha a planilha Relatório procurando pelos produtos de menor valor. 
10. Utilize o mesmo raciocínio para extrair os nomes dos produtos de maior e menor valor para cada 
fabricante. Construa os critérios na planilha Critérios. 
 
 
Se a função BDEXTRAIR retornar o erro #Valor!, significa que o valor procurado não foi 
encontrado; se retornar o erro #Num!, significa que existe mais de um valor que atende o 
critério. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
86 Senac São Paulo 
 
ANOTAÇÕES 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 87 
 
Atividade 4 – Utilizando a função BDCONTARA 
 
Objetivos : • Utilizar a função BDCONTARA. 
Tarefas : • Preencher um relatório com o número de produtos por fornecedor. 
 • Preencher um relatório com o número de produtos por fabricante. 
 
Função BDCONTARA 
Essa função tem como objetivo contar as células não vazias em uma coluna de uma lista de acordo com as 
condições especificadas 
• Sintaxe: BDCONTARA(banco de dados;campo;critérios) 
• Argumentos: 
o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas 
e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. 
o campo: identificação da coluna que contém os dados que devem ser contados. Pode ser 
indicada pelo nome do campo entre aspas, o endereço da célula que contém o nome da 
coluna ou pelo número da coluna dentro da lista. 
o critérios: intervalo de células que contém a condição especificada. 
 
1. Selecione a planilha Relatório. 
2. Clique na célula H4. 
3. Os parâmetros da função BDCONTARA são os mesmos das funções anteriores. Logo, digite na célula 
H4 a fórmula: 
= BDCONTARA(Geral;'Controle de Produtos'!$B$1;Manos) 
 
4. Preencha o relatório contando o número de produtos para os demais fornecedores e fabricantes. 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
88 Senac São Paulo 
 Exercício Proposto 
 
Uma empresa vende produtos para diversos estados. De acordo com a localidade, os valores do frete e 
ICMS variam. Na pasta Bebidas e Alimentos.xlsx temos três planilhas: 
a) Nota: deve ser preenchida com os cálculos dos valores dos produtos e a forma de pagamento; 
b) Critérios: planilha que contém os critérios para o cálculo das funções no Relatório; 
c) Relatório: planilha que deve ser preenchida, por estado, com os cálculos da soma, média, produtos 
de menor e maior valor, quantidade dos produtos de menor e maior valor e quantidade total de 
produtos.