A
partir desta lição, até a Lição 6 estudaremos uma série de
funções utilizadas para analisar dados em uma lista do Excel.
Por exemplo, podemos usar a função CONT.SE para detectar
quantas células não vazias, dentro de uma faixa de células,
atendem a um determinado critério. A função CONT.SE retorna
um número o qual indica quantas células contém um valor que
corresponde ao critério especificado. Podemos usar a função
SOMASE para efetuar uma soma condicional, onde os valores de uma
coluna são ou não acrescentados a soma, com base nos valores
de uma outra coluna. Por exemplo, poderíamos efetuar uma soma
condicional para obter o total de vendas apenas para o mês de
Outubro ou somente as vendas feitas para um determinado cliente
e assim por diante.
Nesta
lição estudaremos as seguintes funções:
-
CONT.SE(
)
-
SOMASE(
)
-
BDMÉDIA(
)
A
função CONT.SE:
Esta função calcula o número de células não vazias em um
intervalo que corresponde a determinados critérios.
Sintaxe
para a função:
CONT.SE(intervalo;critérios)
Intervalo:
é o intervalo de células no qual se deseja contar células
não vazias.
Critérios:
é o critério na forma de um número, expressão ou
texto que define quais células serão contadas. Por exemplo, os
critérios podem ser expressos como 32, "32",
">32", "José da Silva",”Pedro
Pereira”, etc.
Vamos
apresentar alguns exemplos de uso da função CONT.SE:
Suponha
que o intervalo A1:A10 contenha os seguintes valores
"CONTAB", "ADM", "ADM",
"FINAN", “CONTAB”, “CONTAB”, “ADM”,
“FINAN”, “CONTAB”, “ADM” respectivamente.
A
seguir temos alguns exemplos de uso da função CONT.SE neste
intervalo:
-
CONT.SE(A1:A10;"CONTAB")
irá retornar 4
-
CONT.SE(A1:A10;"ADM")
irá retornar 4
-
CONT.SE(A1:A10;"FINAN")
irá retornar 2
O
que retorna a função a seguir:
CONT.SE(A1:A100;”>200”)
???
R:
Retorna o número de células, na faixa de A1 até A100, nas
quais existe um valor maior do que 200. Observe que mesmo quando
o critério é do tipo numérico, devemos coloca-lo entre aspas
– “>200”. Todos os operadores de comparação que foram
vistos no Curso Básico de Excel em 120 Lições (www.juliobattisti.com.br),
podem ser utilizados com a função CONT.SE.
A
função SOMASE:
A função SOMASE faz uma soma condicional. Por exemplo, podemos
usar a função SOMASE para obter a soma de todos os pedidos
para um determinado funcionário. Temos que fornecer três parâmetros
para a função SOMASE, conforme descrito a seguir:
Sintaxe:
SOMASE(intervalo
de pesquisa ;critérios;intervalo de soma)
Intervalo
de critério: É
o intervalo de células onde será feita a pesquisa de acordo
com o critério especificado. Por exemplo, vamos supor que o
nome dos funcionários está na faixa A1:A100. Este seria o
intervalo de critério, no qual a função SOMASE irá procurar
pelo funcionário definido no parâmetro critérios.
Critérios:
são os critérios na forma de um número, expressão ou
texto, que define quais células serão adicionadas. Por
exemplo, os critérios podem ser expressos como 32,
"32", ">32", "maçãs". É
importante salientar que, nos critérios da função SOMASE, não
é possível usar a função E e a função OU. Para maiores
detalhes sobre as funções E e OU, consulte o Curso Básico de
Excel em 120 Lições, no site: www.juliobattisti.com.br.
No nosso exemplo, se quiséssemos apenas os pedidos para o
funcionário Carlos Mota da Costa, especificaremos o seguinte
critérios: “Carlos Mota da Costa”.
Intervalo
de soma: são
as células que serão realmente somadas. As células em
intervalo de soma são somadas somente se suas células
correspondentes em intervalo de pesquisa coincidirem com os critérios
estipulados. Se intervalo de soma for omitido, as células em
intervalo de pesquisa serão somadas, ou seja, a coluna de
pesquisa é a mesma onde os valores serão somados. No nosso
exemplo, vamos supor que o valor das vendas esteja na faixa de
B1:B100. Neste caso a função SOMASE, para calcular o total de
vendas para o funcionário José da Silva, ficaria assim:
=SOMASE(A1:A100;”José
da Silva”;B1:B100)
A
função inicia na linha 1. Procura na célula A1 o nome José
da Silva, caso encontre, o valor da célula B1 será somado na
soma parcial, caso contrário a função segue para a segunda
linha e assim por diante. No final, o resultado será a soma de
todos os pedidos para o funcionário José da Silva, que é
exatamente o que queremos.
Vamos
a mais um exemplo contido no Help do Excel:
Suponha
que A1:A4 contém os seguintes valores de propriedade para
quatro casas: R$ 100.000, R$ 200.000, R$ 300.000, R$ 400.000,
respectivamente. B1:B4 conterá as seguintes comissões de
vendas em cada um dos valores de propriedade correspondentes: R$
7.000, R$ 14.000, R$ 21.000, R$ 28.000.
SOMASE(A1:A4;">160000";B1:B4)
é igual a R$ 63.000
A
função BDMÉDIA:
A
função BDMÉDIA é utilizada para calcular a média dos
valores em uma coluna de uma lista ou banco de dados que
coincidirem com as condições especificadas por você. Ou seja,
usamos a função BDMÉDIA para calcular a média dos valores de
uma coluna, valores estes que coincidem com um critério
especificado.
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. É o conceito
de Lista que vimos na Lição 1 do Módulo 1.
Campo:
indica a coluna que será usada na função. O campo pode
ser dado como texto com o rótulo da coluna entre aspas, como
"País" ou "Cidade", ou como um número 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
para especificar uma condição para a coluna. Para maiores
detalhes sobre a criação de Faixas de Critérios consulte a Lição
14 do Módulo 1 -> Definindo e usando faixas de critérios
– Introdução.
Considere
o exemplo da Figura 2.8 e a fórmula a seguir:
Figura
2.8 – Planilha de exemplo para a função DBMÉDIA( ):
BDMÉDIA(A5:E11;"Rendimento";A1:B2)
é igual a 12, o rendimento médio das macieiras acima de 10 pés
de altura. Observe que A1:B2 é a faixa onde estão os critérios:
(Árvore=Maçã) E (Altura>10). Neste caso estamos calculando
uma média somente para as linhas que atendem aos critérios
especificados na faixa de critérios.
Algumas
observações sobre o uso de funções de Banco de Dados:
O
Microsoft Excel inclui 12 funções de planilha que analisam os
dados armazenados em listas ou bancos de dados. Cada uma dessas
funções, citadas genericamente como BDfunções, usa três
argumentos: banco de dados, campo e critérios. Esses argumentos
se referem aos intervalos de planilha usados pela função.
Sintaxe:
BDfunção(banco_dados;campo;critérios)
Banco_dados:
é o intervalo de células que constitui a lista ou o
banco de dados. No Microsoft Excel, um banco de dados é uma
lista de dados relacionados em que 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. A referência pode ser inserida como um intervalo de células
ou como um nome representando o intervalo que contém a lista.
Para detalhes sobre o conceito de listas consulte as lições do
Módulo 1. Para detalhes sobre a nomeação de intervalos
consulte o Curso de Excel Básico em 120 lições.
Campo:
Indica a coluna que será usada na função. As colunas de dados
na lista devem ter um rótulo de identificação na primeira
linha. O campo pode ser dado como texto com o rótulo de coluna
entre aspas, como "Idade" ou "Rendimento" no
exemplo de lista apresentado abaixo, ou como um número que
represente a posição da coluna na lista: 1 para a primeira
coluna (Árvore, no exemplo da Figura 2.8), 2 para a segunda
(Altura) e assim por diante.
Critérios:
É uma referência a um intervalo de células que especifica as
condições para a função. A função retorna informações da
lista que atendem às condições especificadas no intervalo de
critérios. O intervalo de critérios inclui uma cópia do rótulo
da coluna na lista para a coluna que você deseja que a função
resuma. A referência de critérios pode ser inserida como um
intervalo de células, como A1:F2 no exemplo de banco de dados
da Figura 2.8, ou como um nome que represente o intervalo, como
"Critérios." Para obter outros exemplos de condições
que você possa especificar como critérios, clique em.
Dicas:
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 para especificar a condição.
Por
exemplo, se o intervalo G1:G2 contiver o rótulo de coluna
Receita em G1 e a quantia 10.000 em G2, você poderá definir o
intervalo como CoincidirReceita e usar este nome como o
argumento de critérios nas funções de banco de dados.
Embora
o intervalo de critérios possa ser colocado em qualquer local
na planilha, não o posicione abaixo da lista. Se você
adicionar outras informações à lista, usando o comando Formulário
do menu Dados, as novas informações serão adicionadas na
primeira linha abaixo da lista. Se a linha abaixo da lista não
estiver em branco, o Microsoft Excel não poderá adicionar as
novas informações.
Certifique-se
de que o intervalo de critérios não se sobreponha à lista.
Para
efetuar uma operação em uma coluna inteira em um banco de
dados, insira uma linha em branco abaixo dos rótulos de coluna
no intervalo de critérios.
Todos
os direitos reservados: ® Júlio Battisti, 2002