Usando uma função de banco de dados ~ Ultra Tutoriais

Na planilha abaixo informações de peso, altura e idade de várias pessoas foram relacionadas em forma de banco de dados. Vamos criar um sistema de contagem que obedecerá aos critérios estabelecidos na linha 2.

Excel -Planilha de banco de dado

Para isso, selecionamos a célula E2, que fornecerá o resultado da contagem, e a função BDCONTAR, da categoria banco de dados.

Excel - Função BDCONTAR

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 A4:D17.

Função BDCONTAR - selecionando o banco de dados

Como segundo argumento, podemos especificar um campo. No nosso caso, todos os campos serão utilizados. Então vamos deixar este argumento em branco.

Função BDCONTAR - argumento "Campo" 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.

Função BDCONTAR - selecionando os critérios

Observe que inicialmente todos os registros foram contatos, pois a função não encontrou nenhum critério na segunda linha.

Excel - Resultado da função BDCONTAR

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

Fornecendo critérios para a função BDCONTAR

Excel – Usando uma função matemática

Na planilha abaixo precisamos obter o total de vendas da “Filial1” mas os valores estão misturados com os das outras lojas. Para que o Excel some somente os valores da Filial1, são necessários critérios de soma.

Excel - Usando uma função matemática

Vamos selecionar a célula que fornecerá o resultado (1) e acionar a caixa de funções (2).Neste exemplo, a função “SOMASE” (3, 4) é a que adicionará os valores por um determinado critério ou condição.

Excel - Usando uma função matemática

Os argumentos da função são os elementos necessários para que a fórmula retorne o valor correto. No primeiro campo (“Intervalo”) devemos adicionar o intervalo de células que terão os seus valores analisados, ou seja, que contém a identificação das lojas. Vamos, então, selecionar o intervalo de células B3:B17.

Função SOMASE - Intervalo No segundo campo vamos estabelecer o critério, que será “=Filial1”.

Função SOMASE - Critérios Por último vamos selecionar o intervalo de células com os valores que serão somados sempre que o critério for verdadeiro, ou seja, C3:B17.

Função SOMASE - Intervalo_soma

Observe o resultado:

Resultado da função matemática SOMASE

Excel – Fórmulas com nomes e rótulos

Podemos nomear os dados de uma planilha para que a escrita das fórmulas seja feita de maneira mais simples. Os “NOMES” podem fazer referência a um valor, a uma fórmula ou até mesmo a um conjunto de células. Na planilha abaixo temos que calcular o volume de vários objetos com diferentes alturas. Todos os objetos têm a mesma base, que é o resultado da multiplicação da largura pelo comprimento.

Excel - Fórmulas com rótulos e nomes

Através do comando INSERIR>NOME>DEFINIR… vamos definir o nome “Base” e a ele atribuir a multiplicação da largura pelo comprimento, selecionando as células que contêm estes valores e separando-as pelo operador de cálculo “*” (multiplicação).

Excel - Comando INSERIR NOME

Sempre que o nome que acabamos de criar for utilizado numa fórmula, os valores das células A3 e A5 serão multiplicados e fornecidos na mesma fórmula. Agora vamos selecionar a coluna que contém o rótulo “ALTURA” e novamente acionar o comando INSERIR>NOME>DEFINIR… para definir o nome dos dados desta coluna. Quando os dados de uma planilha são organizados em forma de listas, os rótulos de coluna são automaticamente reconhecidos pelo programa. Observe que o nome do campo já está digitado e as células também já foram selecionadas. Vamos simplesmente dar “OK”.

Excel - Comando INSERIR NOME

Com os dois nomes que acabamos de criar, podemos montar a fórmula que calculará os valores do campo “VOLUME”. Na primeira célula vamos inserir a fórmula “Base” vezes “Altura”, onde “Base” fornecerá o resultado da multiplicação das células A3 e A5, enquanto que a “Altura” fornecerá o valor deste campo na mesma linha da fórmula.

Excel - Fórmula "Base*Altura", que corresponde a "(A3*A5)*B"

Observe a fórmula estendida:

Excel - fórmula estendida com a alça de preenchimento

Excel – Fórmulas matriciais

A grande vantagem em utilizar o formato de banco de dados para criar planilhas é que os valores podem ser retornados de forma mais prática. Num banco de dados, cada coluna contém um campo com uma lista de valores.

Excel - Dados organizados em forma de banco de dados

Os valores da mesma linha formam um registro, ou seja, são informações relacionadas.

Excel - Linha / Registro do banco de dados

Assim, o Excel pode retornar valores de um registro simplesmente se deslocando horizontal e verticalmente.

Excel - Retornando um valor do banco de dados

No exemplo abaixo, precisamos calcular a média de preço obtida por cada vendedor. Para isto, teremos que utilizar uma FÓRMULA MATRICIAL.

Excel - Planilha para cálculo da média de preço de cada vendedor

As fórmulas matriciais realizam cálculos usando argumentos matriciais, que são conjuntos de células equivalentes e que possuem o mesmo número de linhas e colunas. Para o exemplo, utilizaremos como argumentos matriciais o intervalo de células da coluna “A” e o intervalo com o mesmo número de células da coluna “D”.

Excel - Argumentos matriciais A2:A12 e D2:D12

Vamos selecionar a célula onde será fornecido o valor. Para calcular a média, escolhemos a função estatística “MÉDIA”.

Excel - função estatística "MÉDIA"

Como argumento, vamos utilizar a função “SE”, que verificará quais as células do primeiro argumento matricial (A2:A12) contém o valor “James” e retornará os respectivos valores do segundo argumento (D2:D12) para que seja calculada a média.

Excel - Onde houver "James" na matriz A2:A13, retornar o valor na mesma linha da matriz D2:D12

Para que a fórmula matricial funcione, temos que selecioná-la e acionar as teclas CTRL + SHIFT + ENTER. Após este comando, a fórmula passa a ser exibida entre dois colchetes que indicam o seu comportamento matricial. A fórmula final ficará então com a seguinte sintaxe: {=MÉDIA(SE(A2:A12=”James”;D2:D12))}

Excel - fórmula matricial

Para que a fórmula possa ser estendida para as células ao lado através da alça de preenchimento, vamos utilizar referências absolutas (“$”) na fórmula. Assim, os argumentos matriciais não serão modificados. Lembre-se: sempre que você alterar uma fórmula matricial, as teclas CTRL + SHIFT + ENTER devem ser novamente pressionadas.

Para saber mais sobre referências absolutas, veja ESTE TUTORIAL.

Excel - fórmula com referências absolutas "$"

Vamos estender a fórmula.

Para saber mais sobre a alça de preenchimento, acesse ESTE TUTORIAL.

Excel - Estendendo a fórmula com a alça de preenchimento

Agora basta alterar os nomes para que as fórmulas copiadas forneçam os resultados desejados, mas lembre-se novamente: sempre que você alterar uma fórmula matricial, as teclas CTRL + SHIFT + ENTER devem ser novamente pressionadas.

Excel - Adaptando o critério das fórmulas para cada vendedor

Veja o resultado:

Excel - Planilha com fórmulas matriciais

Operações com data e hora

A planilha abaixo deverá calcular o total de dias úteis trabalhados num projeto em andamento.

Para isto utilizaremos duas funções muito úteis: HOJE e DIASTRABALHOTOTAL. Se a segunda função não estiver disponível na caixa de funções do Excel, você deverá acessar o menu

FERRAMENTAS > SUPLEMENTOS e ativar a opção “Ferramentas de análise” com o CD de instalação do Excel inserido no computador.

Excel - planilha para o cálculo de dias trabalhado

No campo “Data Inicial”, vamos informar a data em que foi iniciado o projeto.

Informando a data inicial

Para que o campo “Hoje” seja automaticamente atualizado, vamos utilizar a função HOJE, da categoria Data e hora. Esta é uma função que não possui argumentos, apenas fornece a data do computador na célula.