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 2007. 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 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 colocá-lo entre aspas – “>200”.

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 o 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 Ajuda do Excel 2007:
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:

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.

Figura 2.8 – Planilha de exemplo para a função DBMÉDIA( ):

Algumas observações sobre o uso de funções de Banco de Dados:

O Microsoft Excel 2007 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 2007, 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.

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, 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 2007 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.

VOCÊ CONHECE A UNIVERSIDADE DO EXCEL?

UNIVERSIDADE PRÁTICA DO EXCEL - Domine o Excel, do Básico até a Criação de Planilhas Profissionais com Excel e VBA!

Aplica-se ao Excel 2019, 2016, 2013 e 2010!

22 Cursos - 925 Vídeo-Aulas - 120:42 horas - Só R$ 97,00

Para Todos os Detalhes, Acesse:

https://juliobattisti.com.br/excel-curso-completo.asp