Excel Avançado 2.0

Embora pouco conhecidas as funções de Banco de dados são extremamente úteis pois utilizam menos recursos que as funções matriciais e deixam as planilhas mais leves.

As principais são:

BDContar: Conta as células contendo números em uma coluna de uma lista ou de um banco de dados que corresponde às condições especificadas por você. Sintaxe: BDCONTAR(banco_dados;campo;critérios)

• Banco_dados: é o intervalo de células que constitui a lista ou o banco de dados. Um banco de dados é uma lista de dados relacionados na qual as linhas de informações relacionadas são os registros e as colunas de dados são os campos. A primeira linha da lista contém os rótulos de cada coluna.

• Campo: indica a coluna que será usada na função. Digite o rótulo da coluna entre aspas duplas, como Idade ou Rendimento, ou um número (sem aspas) que represente a posição da coluna dentro da lista: 1 para a primeira coluna, 2 para a segunda coluna e assim por diante.

• Critérios: é o intervalo de células que contém as condições especificadas. Você pode usar qualquer intervalo para o argumento de critérios, desde que ele inclua pelo menos um rótulo de coluna e ao menos uma célula abaixo do rótulo de coluna em que você especificar uma condição para a coluna.

BDMÁX: Retorna o maior número de uma coluna em uma lista ou banco de dados que coincide com as condições especificadas por você. Sintaxe: BDMÁX(banco_dados;campo;critérios)

• Banco_dados: é o intervalo de células que constitui a lista ou o banco de dados. Um banco de dados é uma lista de dados relacionados na qual as linhas de informações relacionadas são os registros e as colunas de dados são os campos. A primeira linha da lista contém os rótulos de cada coluna.

• Campo: indica a coluna que será usada na função. Digite o rótulo da coluna entre aspas duplas, como Idade ou Rendimento, ou um número (sem aspas) que represente a posição da coluna dentro da lista: 1 para a primeira coluna, 2 para a segunda coluna e assim por diante.

• Critérios: é o intervalo de células que contém as condições especificadas. Você pode usar qualquer intervalo para o argumento de critérios, desde que ele inclua pelo menos um rótulo de coluna e ao menos uma célula abaixo do rótulo de coluna em que você especificar uma condição para a coluna.

BDMédia: Calcula a média dos valores em uma coluna de uma lista ou banco de dados que corresponde às condições especificadas por você. Sintaxe: BDMÉDIA(banco_dados;campo;critérios).

• Banco_dados: é o intervalo de células que constitui a lista ou o banco de dados. Um banco de dados é uma lista de dados relacionados na qual as linhas de informações relacionadas são os registros e as colunas de dados são os campos. A primeira linha da lista contém os rótulos de cada coluna.

• Campo: indica a coluna que será usada na função. Digite o rótulo da coluna entre aspas duplas, como Idade ou Rendimento, ou um número (sem aspas) que represente a posição da coluna dentro da lista: 1 para a primeira coluna, 2 para a segunda coluna e assim por diante.

• Critérios: é o intervalo de células que contém as condições especificadas. Você pode usar qualquer intervalo para o argumento de critérios, desde que ele inclua pelo menos um rótulo de coluna e ao menos uma célula abaixo do rótulo de coluna em que você especificar uma condição para a coluna.

BDSOMA: Soma os números de uma coluna em uma lista ou banco de dados que coincidem com as condições especificadas por você. Sintaxe: BDSOMA(banco_dados;campo;critérios)

• Banco_dados: é o intervalo de células que constitui a lista ou o banco de dados. Um banco de dados é uma lista de dados relacionados na qual as linhas de informações relacionadas são os registros e as colunas de dados são os campos. A primeira linha da lista contém os rótulos de cada coluna.

• Campo: indica a coluna que será usada na função. Digite o rótulo da coluna entre aspas duplas, como Idade ou Rendimento, ou um número (sem aspas) que represente a posição da coluna dentro da lista: 1 para a primeira coluna, 2 para a segunda coluna e assim por diante.

• Critérios: é o intervalo de células que contém as condições especificadas. Você pode usar qualquer intervalo para o argumento de critérios, desde que ele inclua pelo menos um rótulo de coluna e ao menos uma célula abaixo do rótulo de coluna em que você especificar uma condição para a coluna.

Vamos a alguns exemplos. Vamos usar a planilha que criamos para a nossa tabela dinâmica. Insira algumas linhas acima dela e monte o seguinte cabeçalho.

Vamos criar um sistema de contagem que obedecerá aos critérios estabelecidos na linha 2.

Para isso, selecionamos a célula E2, que fornecerá o resultado da contagem, e a função BDCONTAR, da categoria banco de dados. Esta função utiliza 3 argumentos. O primeiro é o intervalo de células que constitui o banco de dados. Vamos selecionar então o intervalo A6:D13.

Como segundo argumento, podemos especificar um campo. No nosso caso, todos os campos serão utilizados. Então vamos deixar este argumento em branco. Como último argumento, devemos indicar o intervalo de células que contém as condições especificadas para a busca. Selecionamos, então, o intervalo A1:D2.

Clique em OK.

Exemplo prático: para sabermos a quantidade de registros de pessoas com 35 ou mais anos que pesam menos de 90 kg, digitamos os critérios na segunda linha, conforme mostrado abaixo.

Vamos agora montar um exemplo com a função BDSOMA. Abra a planilha Relação de Produtos. Insira algumas linhas acima dela. Defina alguns critérios como quantidade maior que 6 e valor unitário menor que s. I

Vamos ao cálculo. Chame o assistente de função.

Para calcular o BDMEDIA, BDMAX o processo é semelhante.

O histograma é uma forma gráfica de apresentar a distribuição de frequências de uma variável. O histograma é um gráfico de barras verticais construído com os resultados da tabela de frequências. Primeiro precisamos deixar visível o suplemento Análise de Dados. Clique na ABA arquivos e depois em opções. Dentre as opções disponíveis clique em Suplementos.

Nas versões 2007/2010 do Excel não se trabalham mais com barras de ferramentas. Então para se criar formulários, é preciso primeiro dizer ao Excel que se quer trabalhar com eles. Clique no botão Office 2007 ou Opção Arquivo se for o 2010. Clique em Opções.

No excel 2010 Na janela que aparece, marque a opção Mostrar guia Desenvolvedor na Faixa de Opções. No 2010 clique em Personalizar Faixa de opções e marque a opção Desenvolvedor.

Clique em OK.

Na dica acima aprendemos como mostrar a barra de formulário no Excel 2007/2010. Para as versões anteriores use a barra de ferramenta Formulários. Vamos criar a seguinte planilha A primeira ABA renomeie para Resumo de venda. E monte da seguinte forma.

Na planilha ao lado dê o nome de Dados e monte a seguinte estrutura.

Esta planilha contém 3 tabelas com áreas nomeadas que visam facilitar o trabalho com os objetos de formulários do Excel. A primeira tabela indica o fator referente às despesas de Frete e Seguro (intervalo de A2 até B5). A segunda tabela indica o número de prestações e a taxa de juros para cada parcela (intervalo de D2 até E15). A terceira tabela indica a lista de produtos e o seu respectivo preço (intervalo de G2 até H10).

Vamos agora montar então nosso formulário. Volte à planilha Resumo e desenhe uma Caixa de combinação na célula B2.

Clique com o botão direito do mouse nele e escolha Formatar Controle. Adicione conforme imagem.

Na célula B3, vamos inserir uma função que irá pesquisar o valor do produto na planilha Apoio, em uma área chamada Tabela. Para o nosso exemplo vamos usar a função ÍNDICE da seguinte forma: =ÍNDICE(produto;B2;2).

Na célula C4, vamos inserir um controle que irá informar a quantidade de produtos a serem adquiridos. Dessa forma insira um Controle giratório, usando o botão direito sobre ele.

Clique com o botão direito sobre ele e depois em Formatar Controle. Preencha da seguinte forma:

No exemplo fiz algumas modificações de ordem dos dados na planilha para me facilitar os cálculos. Eliminei linhas vazias. Na célula C6 adicione outro controle giratório com os seguintes valores.

Na célula B6, devemos inserir uma fórmula que irá exibir o valor da entrada em Percentual. Dessa forma insira a seguinte fórmula: =C6/100.

Em C7, desenhe um Barra de rolagem, usando o botão direito sobre ela.

Na célula B8 vamos inserir uma fórmula que faz o cálculo da taxa de juros, com base no número de parcelas selecionada. =ÍNDICE(dados!D4:E15;B7;2). Na célula B9 vamos inserir uma fórmula para exibir o valor da entrada, dessa forma digite: =B5*B6.

Na célula B10, devemos inserir uma fórmula que, com base no valor da entrada, Taxa de Juros, Número de parcela, Valor total e subtraindo a entrada informada irá exibir o valor da parcela. Dessa forma digite a seguinte fórmula: =SE(B6=1;0;PGTO(B8;B7;B5*(B6-1))).

Para finalizar devemos indicar o valor total da venda, de forma que na célula B11, insira a seguinte fórmula: =B10*B7+B9.

Formate a sua planilha da seguinte forma:

Na célula D3 insira uma Caixa de grupo e digite o seguinte título: Local da Entrega.

Na caixa de grupo, adicione 3 botões e aplique os nomes conforme relação de frete.

Selecione um dos botão e clique com o botão direito. Defina como célula Destina a célula E10.

Na célula B5, vamos inserir uma fórmula que irá exibir o valor total da compra, com base no local de entrega Dessa forma devemos inserir a fórmula: =(B3*B4)*ÍNDICE(dados!A3:B5;E10;2).

Para que os números exibidos nas células C6 e E10 sejam ocultos, selecione estas células e aplique o formato personalizado ;;; (três pontos e vírgulas) para ocultar a apresentação das informações das células.