office_excel_intermediario (2)

Prévia do material em texto

ÍNDICE 
 
 1. FUNÇÕES.........................................................................................................................1 
1.1 Anatomia de uma Função................................................................................................1 
1.2 Argumentos .....................................................................................................................2 
 2. CONVENÇÕES UTILIZADAS NA APOSTILA ............................................................3 
2.1 Comandos de Menu.........................................................................................................3 
2.2 Mouse..............................................................................................................................3 
 Dicas................................................................................................................................3 
 3. APERFEIÇOANDO A CRIAÇÃO DE FÓRMULAS......................................................4 
3.1 Criando fórmulas com endereços de outras pastas de trabalho .......................................4 
 4. TRABALHANDO COM BANCO DE DADOS ..............................................................12 
4.1 Classificar por uma lista personalizada ...........................................................................12 
4.2 Filtro Avançado...............................................................................................................14 
4.3 Subtotais..........................................................................................................................17 
4.3.1 Inserindo subtotais na lista ...............................................................................17 
4.3.2 Removendo subtotais ......................................................................................20 
4.4 Tabela e Gráfico Dinâmicos...........................................................................................20 
4.4.1 Compreendendo a Tabela dinâmica .................................................................25 
 5. FUNÇÕES LÓGICAS ......................................................................................................28 
 6. FUNÇÕES ESTATÍSTICAS ............................................................................................35 
6.1 Contando itens em uma lista .............................................................................................35 
6.1.1 Funções CONT.NÚM e CONTAR.VAZIO.....................................................35 
6.1.2 Condicionando uma contagem pela função CONT.SE ....................................36 
 7. FUNÇÕES MATEMÁTICAS E TRIGONOMÉTRICAS.................................................38 
7.1 Somando com uma condição.............................................................................................38 
 8. FUNÇÕES DE BANCO DE DADOS ...............................................................................40 
8.1 Contando pelas funções BDCONTAR e BDCONTARA .................................................47 
8.2 Somando pela função BDSOMA ......................................................................................50 
8.3 Conhecendo as funções BDMÉDIA, BDMÁX e BDMÍN................................................50 
 
Esse material é de autoria de Edna Araújo dos Santos 
 
Excel Intermediário
 
1.0 FUNÇÕES 
 
Usando apenas os operadores matemáticos apresentados há pouco, você certamente já poderia criar 
planilhas para resolver praticamente todas as tarefas realizadas manualmente hoje e que envolvam 
cálculos diversos. Mas, o EXCEL 7 vai mais além, ele contém uma série de funções predefinidas 
que economizam muito trabalho para você. 
 
Genericamente uma função consiste em uma série de operações matemáticas que agem sobre 
valores fornecidos pelo usuário e retorna obrigatoriamente algum resultado. 
 
No exemplo anterior tivemos que somar apenas o conteúdo de quatro células, mas imagine que em 
vez de quatro fossem cem referências. Seria um enorme trabalho de digitação, sujeito a muitos 
erros. 
 
Para facilitar esse trabalho, o EXCEL 7 possui uma função chamada "SOMA", que pede a 
especificação apenas da referência da primeira e da última célula da faixa que será somada. 
 
No exemplo anterior, em vez de digitar a referência das células, poderíamos digitar 
=SOMA(C4:C7). Além de economizar digitação, a utilização dessa função beneficia o usuário 
quando ele precisa alterar a estrutura da planilha. 
 
 
1.1 Anatomia de uma Função 
 
Uma função se caracteriza pela seguinte estrutura genérica: 
 
 
 
 
 
 
Toda função é composta por um nome que é sucedido obrigatoriamente por parênteses. 
Dependendo da função, dentro dos parênteses podem existir argumentos , ou seja valores ou 
referências a células e que serão usados pela função para retornar o resultado da função. 
Esse material é de autoria de Edna Araújo dos Santos 1
 
 
Excel Intermediário
 
A função SOMA( ), por exemplo, exige como argumentos a especificação da célula inicial e da 
célula final separadas por dois-pontos ou, então, uma série de endereços de células separados por 
ponto-e-vírgula. 
 
Cada função possui uma sintaxe própria, ou seja, espera-se que os seus argumentos sejam 
especificados em uma ordem determinada. Se isso não ocorrer, haverá um erro que faz com que o 
resultado não seja produzido ou uma mensagem de advertência seja exibida. 
 
 
1.2 Argumentos 
 
O número e tipo de argumentos requeridos variam de função para função. Um argumento pode ser: 
 
 
### Números 
 
Esse material é de autoria de Edna Araújo dos Santos 2
to 
os 
ro 
ias 
es 
###
###
### Tex
 
### Valores lógic
 
### Valores de er
 
### Referênc
 
### Matriz
 
 
Usando referências a outras células, você pode especificar diretamente o endereço de uma célula ou 
então uma faixa de células, usando o símbolo de dois-pontos para separar a célula inicial e final. 
 
Exemplo: 
 
 SOMA(A1:A20) 
 
 
Quando uma função possuir mais de um argumento, eles devem ser separados por um ponto-e-
vírgula. 
 
Exemplos: 
 
 SOMA(A1;A15;A30) Soma as três células especificadas. 
 
 SOMA(C1:C15;B1:B15) Soma as duas faixas especificadas. 
 
 
 
 
 
Excel Intermediário
 
2. CONVENÇÕES UTILIZADAS NA APOSTILA 
 
2.1 Comandos de Menu 
 
Quando houver um comando de menu, este será referenciado pela palavra escolha e depois o 
caminho do menu em negrito, sendo que cada parte será separada por símbolo . Por exemplo, 
para acessar o comando Filtro avançado, que está no submenu do item Filtro do menu Dados, 
será referenciado da seguinte forma: escolha Dados Filtrar Filtro avançado. As letras 
sublinhadas são as mesmas que estão sublinhadas no menu, também chamadas de teclas de 
aceleração. 
 
Havendo uma caixa de diálogo que contenha várias guias, o nome desta será precedido pela 
palavra guia entre os sinais de menor e maior <guia>. Um exemplo é a caixa de Opções, que tem 
8 guias e é acessada pelo item Opções do menu Ferramentas. Para chegar à guia Geral, a 
referência será: escolha Ferramentas Opções<guia>Geral. 
 
2.2 Mouse 
 
Quando for referenciada a utilização do mouse, você deve utilizar o botão esquerdo, como em: 
clique na barra de rolagem e... Quando for necessário utilizar o botão direito, será especificado, 
como em: clique com o botão direito do mouse na célula... Estamos considerando que o botão 
esquerdo do mouse esteja configurado como botão primário no seu computador. 
 
Dicas 
 
Na própria narrativa você encontrará várias dicas do Excel, porém as principais estarão 
destacadas como esta, que contém um dos Assistentes do Office na frente: 
 
 
☺ Pressione SHIFT+F2 para inserir uma nota na célula 
 
Esse material é de autoria de Edna Araújo dos Santos 3
 
 
Excel Intermediário
 
3. APERFEIÇOANDO A CRIAÇÃO DE FÓRMULAS 
 
Vamosrecapitular um tema do treinamento básico, que é a elaboração de fórmulas com 
referências de outra planilha da mesma pasta de trabalho: 
Para criar fórmulas utilizando endereços de outras planilhas da mesma pasta de trabalho a sintaxe 
é: 
 
 
=Planilha!Célula 
 
 
Na qual o nome da planilha é seguido do ponto de exclamação (!) e, após, é inserida a referência 
da célula, como por exemplo: 
 
 
=Plan1!A1 
 
 
Se no nome da planilha houver espaço, como Valor Mensal, este nome surgirá entre apóstrofos. 
Para referenciar a célula C5 dessa planilha, a fórmula é: 
 
 
= ‘Valor Mensal ‘ ! C5 
 
 
A fórmula a seguir soma os valores das células A5 de Plan1 e B8 de Plan3: 
 
 
=SOMA(Plan1!A5;Plan3!B8) 
 
 
 
3.1 Criando fórmulas com endereços de outras pastas de trabalho 
 
Você também pode elaborar fórmulas que se baseiem em células de outras pastas de trabalho, ou 
seja, de outros arquivos. 
 
O modo de criar a fórmula é bastante simples: 
 
1- Abra as pastas de trabalho a serem referenciadas; 
2- Selecione a célula onde fará a fórmula e digite o sinal de igual (=). Pelo menu Janela escolha 
a pasta de trabalho (arquivo) a ser referenciada e clique na célula cuja referência você deseja 
inserir. Se for continuar a fórmula, digite um operador e clique em outra de qualquer pasta de 
trabalho. Faça assim para cada referência da fórmula: 
3- Quando terminar pressione a tecla ENTER. 
Esse material é de autoria de Edna Araújo dos Santos 4
A sintaxe de uma fórmula com referência de outra pasta de trabalho é: 
 
 
Excel Intermediário
 
 
 
= [Arquivo] Planilha!Célula 
 
 
Na qual, no caminho, o nome do arquivo deve estar entre colchetes e o nome da planilha seguido 
do ponto de exclamação (!). Tudo isso estará entre apóstrofo, com exceção do ponto de 
exclamação, se houver espaço no nome da planilha, como Tabela de Custos (com 2 espaços). 
Após, surge o endereço da célula. Um exemplo é: 
 
 
=C:\Valores\ [Mensal.xls] Plan1!C5 
 
 
A célula referenciada é C5 da planilha Plan1, do arquivo Mensal.xls que está na pasta (diretório) 
Valores da unidade C do computador. 
 
Outro exemplo é: 
 
 
=SOMA ( ‘A: \ [Cursos.xls]Data Inicial ‘!A6:A14) 
 
 
Que soma o intervalo A6:A14 da planilha Data Inicial da pasta Cursos.xls. Neste caso, o arquivo 
se encontra no drive A (disquete). 
 
Crie fórmulas com referências de outras planilhas simplesmente clicando nas células, deixando 
que o Excel insira o caminho completo. Se, após criar a fórmula, não surgir o nome do disco e da 
pasta (diretório), é porque a pasta de trabalho referenciada está aberta. Quando fechar, o caminho 
surgirá na fórmula. 
Se preferir, vicê também pode digitar o caminho completo para referenciar a célula, porém é mais 
trabalhoso. 
 
Na figura a seguir há uma pasta de trabalho cujo nome é Preços.xls que está localizada na pasta 
(diretório) Custos. Na coluna B da planilha Valores, a partir da linha 4 estão inseridos os preços 
de custo dos produtos cujos nomes estão na coluna A . Para calcular o preço de venda na colula C 
será necessário utilizar a margem de lucro que está na planilha Margens de outra pasta de 
trabalho, nomeada de Percentual.xls. Essa pasta de trabalho está no diretório Lucro e é exibida 
na próxima figura. 
Esse material é de autoria de Edna Araújo dos Santos 5
 
 
Excel Intermediário
 
Esta pasta de trabalho é Preços.xls. Na coluna C, os preços de venda deverão ser calculados baseados na... 
 
 
...planilha Margens da pasta de trabalho Percentual.xls 
 
 
 
O preço da venda será calculado baseado no lucro sobre o custo, ou seja, será multiplicado o 
preço de custo por 1+margem de lucro. 
Esse material é de autoria de Edna Araújo dos Santos 6
 
 
Excel Intermediário
 
Vamos dar mais importância agora para o modo de criação da fórmula. Para criar esta fórmula, 
selecione a célula C4 da planilha Valores da Pasta Preços.xls. Certifique-se que a pasta 
Percentual.xls também esteja aberta e siga estes passos: 
 
 
 
1- Digite o sinal de (=) e insira a referência do preço de custo, célula B4. Depois, digite o sinal 
de multiplicação (*), abra parêntese e digite 1+; 
 
=B4*(1+ 
 
 
 
 
2. Escolha Janela e clique no nome da pasta Percentual.xls (CTRL+F6); 
 
 
2 Clique na célula C4 da planilha Margens. Isso faz com que essa referência seja inserida 
como absoluta (estão fixas a coluna e a linha) na fórmula. Feche parêntese e pressione a tecla 
ENTER. 
 
 
 
 
 
 
 
 
Esse material é de autoria de Edna Araújo dos Santos 7
 
 
Excel Intermediário
 
Após clicar na pasta Percentual.xls e na célula C4 da planilha Margens, essa referência é inserida na fórmula 
 A fórmula contém a referência da pasta Percentual.xls 
 
Esse material é de autoria de Edna Araújo dos Santos 8
 
 
Excel Intermediário
 
A fórmula será: 
=B4*(1+ [Percentual.xls] Margens!$C$4) 
 
Veja que o nome da pasta de trabalho surge entre colchetes. Percebe como é simples, você só 
precisa clicar na célula que o Excel se encarrega de montar a sintaxe. Se for preciso tornar a 
referência da célula mista (C$4 ou $C4) ou relativa (C4), basta tirar os cifrões que desejar. 
 
Esse material é de autoria de Edna Araújo dos Santos 9
 
Crie fórmulas com referências de outras pastas de 
trabalho simplesmente clicando na célula a 
referenciar. Se achar mais fácil, exiba as janelas 
das pastas de trabalho lado a lado, que pode ser 
feito pelo menu Janela 
 
Agora é só preencher para as demais células da coluna, que deverá ficar conforme a próxima 
figura. 
 
Após preencher a fórmula para as demais células, o resultado será este 
 
Abra a pasta de trabalho Percentual.xls e na planilha Margens altere para 30% o valor da célula 
C4. Salve e feche a pasta de trabalho. 
 
 
 
Excel Intermediário
 
 
 
Depois, abra a pasta Preços.xls. Como esta pasta tem vínculos externos, será exibida a mensagem 
perguntando se você desejar atualizar as informações. Clique no botão Sim. 
 
 
 
Este alerta é exibido ao abrir uma pasta de trabalho com vínculos externos. 
Esse material é de autoria de Edna Araújo dos Santos 10
 
 
Excel Intermediário
 
 
 
Agora veja os valores das células do intervalo C4:C8 foram alterados, considerando 30% como 
margem de lucro. 
 
 
Os valores da coluna C foram atualizados 
 
 
Para que não seja mais exibida a mensagem de atualização ao abrir a pasta, escolha 
Ferramentas Opções<guia>Editar e desmarque a caixa Confirmar atualização de vínculos 
automáticos. 
 
 
 
Esse material é de autoria de Edna Araújo dos Santos 11
 
 
Excel Intermediário
 
4. TRABALHANDO COM BANCO DE DADOS 
 
Neste capítulo você conhecerá mais recursos para trabalhar com listas. Uma lista no Excel tem o 
mesmo conceito de banco de dados, sendo a nomenclatura a única diferença. Portanto, quando 
você for consultar a literatura referente ao sotware, notará a frequência o nome lista, que pode ser 
entendido como banco de dados. 
 
Lista é um conjunto de dados organizados em linhas e colunas que têm uma relação entre si. Cada 
coluna recebe um rótulo, também chamado de nome do campo, que estará na primeira linha da 
lista. Abaixo dos rótulos, ou seja, a partir da segunda linha, são inseridos os registros. Portanto, as 
colunas são os campos e as linhas os registros. 
A lista da figura a seguir possui 4 campos, que são: Produto, Valor, Qtde e Origem. O número de 
registros desta lista é 6, que são os dados da linha 2 até 7. 
 
Rótulo dos Campos 
 
Registros 
 
Quase tudo o que se cria no Excel é uma lista. 
São exemplos de lista: o cadastro de clientes, a lista de produtos, a tabela de preços de uma 
empresa, o cadastro de alunos de uma escola, etc... 
 
4.1 Classificar por uma lista personalizada 
 
Você verá como classificar por uma lista personalizada, como os dias da semana, os meses do 
ano ou uma lista com nomes de produtos. 
Escolha Ferramentas Opções<guia>Listas cliqueem Entradas da lista e digite cada entrada 
pressionando ENTER até completar a lista. Depois clique em Adicionar e OK. 
 
Para classificar po uma lista personalizada, selecione a região a classificar, conforme a regra de 
seleção para classificar e siga os passos: 
Esse material é de autoria de Edna Araújo dos Santos 12
 
 
Excel Intermediário
 
1. escolha Dados Classificar; 
2. em Classificar por escolha o campo que contém a lista e clique em Opções; 
3. em Ordem de classificação da primeira chave escolha a lista desejada e confirme. 
 
Clique no botão OK e veja o resultado. 
 
A classificação por uma lista personalizada 
somente pode ser inserida como a primeira 
chave da classificação 
 
Clique aqui para 
Escolher a lista 
Normalmente a classificação é 
feita de cima para baixo. Da 
esquerda para a direita muda a 
ordem das colunas 
Permite distinguir letras 
maiúsculas de minúsculas 
na classificação 
 
 
 
Esse material é de autoria de Edna Araújo dos Santos 13
 
 
Excel Intermediário
 
Esta lista está classificada pelos dias da semana 
 
 
A lista da figura anterior está classificada pelos dias da semana em ordem crescente. Se não fosse 
escolhida a opção de classificação pela lista personalizada, a ordem crescente colocaria qua em 
primeiro lugar, que seria a ordem alfabética. 
 
 
4.2 Filtro Avançado 
 
Em uma lista contendo diversos registros, às vezes é necessário exibir ou extrair apenas aqueles 
que atendam a determinadas condições. Por exemplo, em uma lista com diversos produtos, datas, 
quantidades e preços é preciso exibir apenas os dados referentes a um determinado produto 
dentro de um certo período. Para isso é preciso filtrar os dados da lista e o Excel possui dois tipos 
de filtros: o Auto filtro, e o Filtro avançado. A diferença entre eles é que o filtro avançado 
permite especificar mais de duas condições por campo da lista e o local de extração pode ser uma 
região diferente daquela que contém os dados de origem. 
 
Antes de aplicar o filtro avançado em uma lista, algumas regras devem ser seguidas: 
 
1- A lista deve conter os rótulos que identificam os campos, ou seja, a primeira linha da lista 
deve ter os nomes dos campos; 
2- É necessário que exista um intervalo de critérios na mesma planilha com os dados a serem 
filtrados. 
 
 
Um intervalo de critérios é criado da seguinte forma: 
 
A primeira linha deve conter o nome dos campos a serem filtrados, podendo ser desde 1 até todos 
os campos da lista. Da segunda linha em diante são inseridas as condições. 
Na lista da figura a seguir, os nomes dos campos estão no intervalo A1:G1. Um intervalo de 
critérios para filtrar esta lista dever ser criado a partir de qualquer célula da planilha Movimento, 
que é a planilha que contém os dados a serem filtrados. 
Todas as condições que estiverem na mesma linha são cumulativas, ou seja, se em um intervalo 
de critérios para a lista a seguir for digitado um nome em uma célula e em outra célula da mesma 
linha o nome de um produto, significa que serão filtrados os registros com aquele nome e com 
Esse material é de autoria de Edna Araújo dos Santos 14
 
 
Excel Intermediário
 
aquele produto. Caso o nome esteja em uma linha e o produto em outra, serão filtrados os 
registros com aquele nome ou com aquele produto. Portanto, o que estiver na mesma linha 
significa E e em linhas diferentes significa OU. Veja mais adiante alguns exemplos. 
 
 
 
Você pode utilizar os operadores de comparação nas condições, que são: 
 
 
> Maior que 
< Menor que 
>= Maior ou igual a 
<= Menor ou igual a 
= Igual a 
<> Diferente de 
 
Veja nas figuras a seguir alguns exemplos: 
Esse material é de autoria de Edna Araújo dos Santos 15
 
O intervalo de critérios está em A11:B12 
 
Nessa figura, o intervalo de critérios está em A11:B12 e extrai os registros cujo Produto seja 
Trompete e Qtde acima de 6. Veja que as condições são inseridas respectivamente em cada 
campo. Por estarem na mesma linha (12), as condições são cumulativas, significando que, para 
ser extraído, o registro tem que ter necessariamente esse nome com esse produto e essa 
quantidade. 
 
Na figura a seguir, o intervalo de critérios está em A11:B13. Veja que o nome Adriana, podendo 
está em uma linha (12) e Saxofone, assim como >5 estão na linha de baixo (13). Isso significa 
que serão extraídos os registros cujo nome seja Adriana, podendo haver qualquer dado nos outros 
campos. Também serão extraídos os registros com Saxofone e, como a quantidade está na 
mesma lnha, as quantidades dos Saxofones extraídos têm que ser maior que 5. 
 
 
16 
 
Excel Intermediário
 
 4.3 Subtotais 
 
Conforme são digitados os dados em uma lista e ela vai ficando extensa, chega um momento em 
que torna-se necessário ter uma visualização geral dos valores para melhor compreensão dos 
dados. O Excel dispõe de ferramentas que fazem o gerenciamento de listas, como a extração de 
registros através da filtragem ou a classificação dos dados. Um dos recursos existentes no 
software eu permite exibir os valores totais de cada item diferente da lista é chamado de 
Subtotais, que devem ser aplicados na existência de itens repetidos em um campo. Lembra-se da 
lista que nós utilizamos no Filtro avançado? 
Desta vez ela será utilizada para você entender como aplicar subtotais. 
Antes de inserir subtotais em uma lista, classifique-a (em ordem crescente ou descrescente) pelo 
campo a ser subtotalizado. Isso é fundamental, pois ao utilizar esta ferramenta, surgirão os 
valores totais sempre que um novo item for localizado. Nesta lista, os subtotais serão aplicados 
no campo Nome. Portanto, primeiramente classificamos por este campo, conforme a figura a 
seguir. 
 
 
A lista está classificada pelos nomes 
 
 
4.3.1 Inserindo subtotais na lista 
 
Após classificada, selecione qualquer célula da lista e escolha Dados Subtotais. 
A caixa Subtotais possui os seguintes itens: 
 
Esse material é de autoria de Edna Araújo dos Santos 17
A cada alteração em: selecione o campo em que, ao notar mudança de dado, será inserida uma 
linha com o total do item. Sempre deve ser escolhido o campo pelo qual a lista está classificada 
como primeira chave. Nesta lista será o campo Produto. 
 
 
Excel Intermediário
 
Usar função: exibe uma lista com 11 funções. Escolha uma compatível com o tipo de dado do 
campo onde será adicionado o subtotal. Neste caso, selecione Soma. 
 
Adicionar subtotal a: marque os campos que terão os subtotais. Como foi escolhida a função 
SOMA, o correto é marcar um ou mais campos que contenham números. Se escolher a função 
Cont.valores, então pode ser adicionado um subtotal em campos com textos, pois esta função faz 
a contagem dos itens existentes. Marque apenas Total. 
 
Substituir subtotais atuais: caso já exista subtotais aplicados à lista, se marcar esta caixa, aqueles 
que estiverem sendo configurados neste momento substituirão os existentes, caso contrário, os 
novos subtotais serão adicionados aos já existentes. Não marque esta caixa. 
 
Quebra de página entre grupos: se marcar esta caixa, será criada uma quebra de página para cada 
item com subtotal na lista. Não marque. 
 
Resumir abaixo dos dados: insere os subtotais abaixo dos dados da lista. Se não estiver marcada, 
os subtotais são colocados acima dos dados. Deixe marcada. 
 Caixa Subtotais 
 
Clique no botão OK para confirmar. 
 
 
Veja que surgiram os símbolos de estrutura de tópicos e uma linha com o subtotal de cada nome, 
calculando a soma do campo Total (coluna G). 
Em uma lista com subtotais, na célula com o valor é inserida uma fórmula com a função 
SUBTOTAL, cuja sintaxe é: 
 
=SUBTOTAL (Núm_função;Ref1;Ref2;...Ref29) 
 
Na célula G4 a fórmula que calcula o valor total da é: 
Esse material é de autoria de Edna Araújo dos Santos 18
=SUBTOTAL(9;G2:G9) 
 
 
Excel Intermediário
 
 
 
Onde 9 é o número da funçãoSOMA seguido do intervalo a ser somado. Cada uma das 11 
funções tem um número que a identifica. Veja a tabela: 
 
Nº da função Função 
1 MÉDIA 
2 CONT.NÚM 
3 CONT.VALORES 
4 MÁXIMO 
5 MÍNIMO 
6 MULT 
7 DESVPAD 
8 DESVPADP 
9 SOMA 
10 VAR 
11 VARP 
 
Símbolo de 
estrutura de 
tópicos 
Linha com 
subtotal
 Os subtotais foram adicionados a cada mudança em Produto (Coluna D) 
Esse material é de autoria de Edna Araújo dos Santos 19
 
4.3.2 Removendo subtotais 
 
Para remover subtotais selecione uma célula da lista, escolha Dados Subtotais e clique no 
botão Remover todos. 
 
 
4.4 Tabela e Gráfico Dinâmicos 
 
Você já viu diante de uma grande massa de informações simplesmente digitadas e precisou ter 
uma visão geral desses dados? Muito provavelmente a resposta será Sim. 
Um exemplo são os registros do setor de vendas de uma empresa que possui diversos itens. 
Imagine se cada item vendido ocupar uma linha em um banco de dados. Quantos registros não 
haverá no final de um período! E essa lista pode ter diversos campos, como data, produto, 
vendedor, preço, quantidade, total, e até muitos outros. Chegará um momento que será necessário 
resumir os dados para se ter uma idéia geral daquilo que existe na lista. Você já conheceu os 
recursos do filtro avançado, dos subtotais e verá mias adiante, no capítulo FUNÇÕES, as 
funções de banco de dados. Todos esses excelentes recursos auxiliam bastante no gerenciamento 
das informações e devem ser empregados quando necessário. Porém, agora você vai conhecer 
uma ferramenta que torna o Excel um dos melhores e mais versáteis softwares existentes no 
mercado. É a Tabela dinâmica. 
 
Uma Tabela dinâmica deve ser criada quando uma lista tiver vários itens repetidos, como na lista 
da próxima figura, na qual existem produtos repetidos na coluna A, operações de Importação ou 
Exportação na B, países na coluna C, prazos de entrega na D, estendendo-se a lista até a coluna 
G, com o campo Total. 
 
20 
 
Excel Intermediário
 
 
O objetivo de uma Tabela dinâmica é resumir os dados permitindo ter uma visão geral das 
informações. Um exemplo é verificar qual a soma das unidades referentes a Calçados, na 
operação Exportação cujo país é a Alemanha. 
 
 
 
Tabela Dinâmica 
 
4.2.1.1 Criando uma Tabela dinâmica 
 
A lista da figura anterior se estende até a linha 351. Portanto, nessa lista há 350 registros. Para 
criar uma Tabela dinâmica baseada nesses dados, selecione qualquer célula na lista (isso facilida 
o trabalho) e siga os passos: 
 
1- Escolha Dados Relatório de tabela e gráfico dinâmicos; 
 
2- Será aberto um assistente na primeira de três etapas. Nessa etapa você deve indicar onde 
estão os dados a partir dos quais a tabela dinâmica deve ser criada, que podem ser: 
 
• Banco de dados ou lista do Microsoft Excel: os dados estão organizados em uma planilha 
do Excel. Podem estar inclusive em outra pasta de trabalho. 
 
• Fonte de dados externos: permite obter os dados de outras fontes, como arquivos do tipo 
.DBF, de tabelas ou consultas de um arquivo do Microsoft Access, tabelas do Visual FoxPro 
ou consultas criadas a partir do Microsoft Query. 
Esse material é de autoria de Edna Araújo dos Santos 21
 
 
Excel Intermediário
 
• Vários intervalor de consolidação: permite obter dados de planilhas diferentes do Excel. 
Esta opção deve ser escolhida quando os dados não estiverem na mesma lista, como por 
exemplo, quando houver dados referentes a vários meses, estando os meses distribuídos em 
planilhas diferentes. 
 
Neste caso, como os dados estão em uma única lista do Excel, escolha Banco de dados ou lista 
do Microsoft Excel. Na parte inferior dessa caixa, é preciso escolher o tipo de relatório a ser 
criado. Escolha Tabela dinâmica. Posteriormente criaremos um gráfico dinâmico. Clique no 
botão Avançar para passar para a segunda etapa do assistente; 
 
Primeira etapa do assistente. Indique a origem dos dados e o tipo de relatório 
 
 
 
3- Nesta etapa você deve especificar onde se encontram os dados a serem utilizados. Como 
inicialmente foi selecionada uma célula da lista, toda a lista já é sugerida como origem de 
dados, que corresponde ao intervalo $A$1:$G$17. Veja que é necessário incluir os rótulos 
das colunas, que estão na primeira linha da lista. Aceite esse intervalo. Se os dados estiverem 
em outra pasta de trabalho clique no botão Procurar para selecionar a pasta e a planilha. 
Clique no botão Avançar; 
 
 
Esse material é de autoria de Edna Araújo dos Santos 22
 
 
Excel Intermediário
 
 Determine o intervalo da lista a ser utilizada como base de dados 
 
 
 
4- Ainda nesta estapa existem os botões Layout e Opções na parte inferior da caixa de diálogo. 
As configurações das caixas a serem abertas por esses botões podem ser especificadas 
também posteriormente. Porém, clique no botão Layout para diagramar a tabela. 
Campos da lista 
com a origem de 
dados 
4 campos da Tabela 
dinâmica 
 
 
Nessa caixa, existem os campos da lista com a origem de dados do lado direito e os 4 campos de 
uma Tabela dinâmica, que são: 
 
 
Esse material é de autoria de Edna Araújo dos Santos 
 
23
 
Excel Intermediário
 
PÁGINA: Deve conter o campo a ser filtrado. 
COLUNA: Deve conter o campo a ser exibido na coluna da Tabela dinâmica. 
LINHA: Deve conter o campo a ser exibido em linhas na Tabela dinâmica. 
DADOS: Deve conter o campo em que os cálculos serão feitos 
 
Para elaborar o layout você deve arrastar o botão que contém o nome do campo da lista e soltá-lo 
em cima do campo da Tabela dinâmica em que este deverá ficar. Então, arraste o botão Produto, 
soltando-o no campo da PÁGINA. Depois, arraste o botão País soltando-o na LINHA. O botão 
Operação deve ficar na COLUNA e Unidades em DADOS. Ao soltar o botão Unidades, ele 
será Soma de Unidades. 
 
Arraste os botões em cima dos campos para criar o layout 
 
 
 
 
Esse material é de autoria de Edna Araújo dos Santos 24
 
 
Excel Intermediário
 
5- Na quarta e última etapa deve-se especificar onde a Tabela dinâmica será criada. Se escolher 
Nova planilha, quando clicar no botão Concluir, surgirá uma nova planilha na pasta de 
trabalho e a partir da célula A1 será criada a Tabela Dinâmica. Neste exercício, escolha Nova 
Planilha e clique em Concluir. 
 
 
Surgirá uma nova planilha onde será criada uma Tabela dinâmica resumindo os dados do 
intervalo, contendo os nomes dos países dispostos em linhas, as operações, Importação e 
Exportação, ocupando uma coluna cada uma; os números da área de dados referem-se à soma 
das unidades. Na linha 1 na nova planilha existe o campo de PÁGINA da Tabela dinâmica, 
contendo os produtos, onde inicialmente aparece Tudo. 
 
A partir da linha 1 da nova planilha foi criada uma Tabela dinâmica 
 
 
4.4.1 Compreendendo a Tabela dinâmica 
 
Essa tabela exibe os valores referentes à soma das unidades de todos os produtos de acordo com 
o país e a operação. Veja na linha 9 que o valor total das importações para a Itália é de R$ 11,00. 
As importações tem um total Global de R$ 309,00, e assim como esse valor, há os valores dos 
demais países e, na coluna D estão os totais globais. 
Antes de fazer alterações de layout ou exibição dos dados, vamos formatar os números para que 
sejam exibidos os separadores de milhares. Para isso, selecione qualquer número existente na 
Tabela Dinâmica e clique no botão Configurações de campo, na barra de ferramentas Tabela 
dinâmica. Será exibida a caixa Campo da Tabela dinâmica com o campo previamente 
selecionado, que é Unidades. Clique no botão Número. Será exibida a caixa Formatar células. 
Esse material é de autoria de Edna Araújo dos Santos 25
 
 
Excel Intermediário
 
Clique na categoria Número e marque a caixa Usar separador de 1000 (.). Como casas 
decimais, digite 0 (zero). Clique no botão OK para voltar à caixa anterior. Clique no botão OK. 
 
 
 
 Barrade ferramentas Tabela dinâmica 
 
 
 
Caixa com configurações do campo selecionado 
Esse material é de autoria de Edna Araújo dos Santos 26
 
 
Excel Intermediário
 
 Formatação do número 
 
 
Isso faz com que os valores do campo de dados sejam exibidos com o separador de milhares sem 
casas decimais, melhorando a compreensão desses dados. 
 
Você pode escolher o ítem cujos valores serão calculados na Tabela dinâmica. No campo de 
PÁGINA há os nomes dos produtos da lista. 
 
Esse material é de autoria de Edna Araújo dos Santos 27
 
5. FUNÇÕES LÓGICAS 
 
Inserindo mais de uma condição em um teste lógico 
 
No treinamento básico você conheceu a função SE, que permite inserir uma condição e dar uma 
resposta caso a condição tenha um resultado o valor lógico VERDADEIRO e dar outra resposta 
para o caso do teste lógico ser FALSO. Também no treinamento básico você aprendeu de 
maneira superficial a inserir várias condições no teste lógico. Porém, neste treinamento vamos 
tratar deste assunto com mais detalhe. 
 
Existem algumas técnicas utilizadas para inserir várias condições em um teste lógico. A forma 
mais prática na maioria dos casos é através da função E. Esta função permite especificar até 30 
(trinta) condições, retornando VERDADEIRO somente se TODAS as condições forem 
satisfeitas. Basta que uma delas não seja satisfeita para que o resultado seja FALSO. 
 
Uma faculdade estabeleceu que somente serão aprovados os alunos que obtiverem média final 
maior ou igual a 7, frequência maior ou igual a 70% e a nota do 4º Bimestre tem que ser maior 
que 6. Os demais estarão reprovados. A estrutura da fórmula para esta questão deve ser esta: 
 
=SE(E(condição1;condição2;condição3);VERDADEIRO;FALSO) 
 
Segundo a figura a seguir, para calcular a situação dos alunos, selecione a célula H3 e siga estes 
passos: 
 
 
Esta fórmula obriga que todas as condições sejam satisfeitas 
(média>=7;frequência>=70%;4ºBim>6)para que o Teste_lógico seja VERDADEIRO e o 
aluno Aprovado 
28 
 
Excel Intermediário
 
1- Digite =SE e pressione CTRL+T (ou digite o sinal de igual (=) e clique na função SE pela 
Caixa de nome). Como haverá mais de uma condição para aprovação, com o cursor pulsando 
na caixa de edição do Teste_lógico (quando for aberto o assistente o cursor já vede estar lá), 
clique no drop-down do lado do X vermelho na barra de fórmulas e assim que localizar a 
funcão E dê um clique nela; 
 
2- Nesta etapa, note que os argumentos da função E são Lógico1 e Lógico2. Porém, esta função 
contém 30 argumentos, que são Lógico1, Lógico2,...até Lógico30, que são as condições a 
serem testadas. Insira as três condições, uma em cada argumento, ou seja, Lógico1 será 
F2>=7, Lógico2 G2>=70%, Lógico3, que será aberto quando clicar em Lógico2, será E2>6; 
 
Digite cada condição em caixas de edição diferentes. O valor que aparece à direita dos 
testes indica o resultado de cada um. Neste caso todos são VERDADEIRO 
 
 
5. Após, clique em SE que está na Barra de fórmulas para voltar para a etapa anterior. Note que 
o Teste_lógico contém E(F3>=7;G3>=70%;E3>6), ou seja, o nome da função E e todas as 
condições entre parênteses e separadas por ponto e vírgula. Em Valor_se_verdadeiro digite 
“Aprovado” e “Reprovado” em Valor_se_falso. Clique no botão OK. 
 
 
A fórmula será esta: 
 
=SE(E(F3>=7;G3>=70%;E3>6);”Aprovado”;”Reprovado”) 
 
 
Para saber o resultado parcial de uma fórmula, selecione 
pela Barra de fórmulas a parte a ser verificada e tecle F9. 
Na fórmula acima, se você selecionar 
E(F3>=7;G3>=70%;E3>6) e teclar F9, verá que surgirá o 
valor lógico VERDADEIRO ou FALSO, dependendo do 
resultado do teste. Porém não confirme, tecle ESC para 
cancelar, caso contrário a seleção será uma constante. 
Esse material é de autoria de Edna Araújo dos Santos 29
 
Selecione parcialmente uma fórmula e tecle F9 para... 
 
 ...saber o valor da seleção 
 
 
Se você achar mais simples, digite a fórmula. Para certas funções fica mais fácil digitar do que 
utilizar o assistente. Se digitar a fórmula acima faça assim: 
Digite o sinal de igual, o nome da função SE (pois é ela que permite testar e dar respostas para o 
teste) e abra parêntese; como será utilizado o resultado de outra função para o Teste_lógico, 
digite E e abra parêntese da funcão SE e tecle ENTER. 
 
30 
 
Excel Intermediário
 
E para o aluno ficar de exame? 
 
Se a média for maior ou igual a 5,5 e menor que 7, o aluno ficará para “Exame”. Não 
considerando a frequência e a nota do 4º Bimestre, a fórmula será: 
 
 
=SE(E(F3>=7;G3>=70%;E3>=6); ”Aprovado”;SE(F3>=5,5;”Exame”;”Reprovado”)) 
 
Note que o segundo SE pertence ao Valor_se_falso do primeiro SE. A condição para “Exame” é 
F2>=5,5 onde não é necessário especificar <7 pois, por eliminação, isso já é feito devido ao 
primeiro teste lógico, que prevê a aprovação. 
 
Inserindo condições alternativas 
 
Em situações onde existe mais de uma condição, bastando que qualquer uma delas seja satisfeita 
para que o objetivo seja alcançado, utilize a função OU, que, assim como E, também permite 
inserir até 30 condições, com a diferença de que, se qualquer uma das condições testadas for 
satisfeita, o resultado será VERDADEIRO. Veja o exemplo a seguir: 
 
Uma loja pretende fazer uma promoção, oferecendo um desconto de 10% para quem comprar 
acima de 4 unidades de um produto ou se o valor total da compra for maior que R$ 100,00. 
Para verificar se haverá desconto a estrutura da fórmula será esta: 
 
=SE(OU(QUANTIDADE>4;TOTAL>100);10%;0) 
 
A primeira função é SE, pois é ela que permite especificar um teste e determinar respostas para o 
resultado desse teste. Como haverá mais de uma alternativa para o caso do teste ser 
VERDADEIRO, no Teste_lógico utilizamos a função OU para especificar as duas alternativas. 
Note que a sintaxe de OU é a mesma de E, ou seja, insira até 30 argumentos que correspondem às 
condições a serem testadas. Como o próprio nome sugere, se uma OU outra condição for 
satisfeita, o resultado será VERDADEIRO, retornando a fórmula o valor especificado em 
Valor_se_verdadeiro, que neste caso é 10%. Caso todas as condições não sejam satisfeitas, o 
retorno será o Valor_se_falso, zero no nosso exemplo. 
 
 
 
 
 
 
 
 
 
Esse material é de autoria de Edna Araújo dos Santos 31
 
 
Excel Intermediário
 
Esta fórmula concede desconto se qualquer condição for VERDADEIRO 
 
 
Portanto, a fórmula da célula E3 da figura anterior é: 
 
 
=SE(OU(B3>$B$9;D3>$B$10);D3*$B$11;0) 
 
Utilizamos a célula B9 como base para a quantidade, B10 para o valor da compra e B11 para a 
porcentagem de desconto. Assim, quando precisar modificar as condições, basta alterar o valor 
destas células. 
 
Mas, o “seu” Virgílio, que é o dono da loja quer atrair a freguesia para melhorar as vendas. Para 
isso resolveu aumentar a promoção. Vai dar um desconto especial de 15% para quem, além de 
comprar nas condições anteriores, pagar à vista. Para isso, inserimos mais uma coluna com o 
rótulo de Tipo de Pgto, cujo valor será v para pagamento à vista ou p para pagamento a prazo. E, 
abaixo do desconto, criamos o desconto especial, cujo valor é de 15% (célula B12). Veja a figura 
a seguir: 
 
Esse material é de autoria de Edna Araújo dos Santos 32
 
 
Excel Intermediário
 
A coluna E indica o pagamento à vista “v” ou a prazo “p” 
 
E agora? 
Agora temos que analisar os dois descontos: 
Desconto de 15%: quantidade acima de 4 unidades ou valor acima de 100,00 e pagamento à 
vista; 
Desconto de 10% : quantidade acima de 4 unidades ou valor acima de 100,00. 
Sem desconto: os demais 
 
Vamos construir esta fórmula por partes: 
 
A primeira função é SE, portanto a fórmula inicia-se por ela: 
 
=SE( 
 
 
Agora vamos analisar o desconto de 15%. 
Para receber este desconto uma condição é obrigatória: o pagamento à vista. 
Como haverá outra condição, devemos inserira função E e inserir a primeira condição: 
 
=SE(E(E3=”v”; 
 
O “v” deve estar entre aspas por ser um texto. Se não estiver, o Excel “pensará” que trata-se de 
um nome. 
A próxima etapa é colocar a próxima condição. Como serão duas bastando que apenas uma seja 
satisfeita para conceder o desconto de 15%, inserimos as duas através da função OU: 
=SE(E(E3=”v”; OU(B3>$B$9;D3>$B$10)); 
Esse material é de autoria de Edna Araújo dos Santos 33
 
 
Excel Intermediário
 
Fecha-se parêntese para OU e para E, pois as condições já estão colocadas. Agora vamos inserir a 
resposta para o valor VERDADEIRO, ou seja, se estas condições forem satisfeitas, que será o 
desconto de 15%, calculado por D3*B12: 
 
=SE(E(E3=”v”;OU(B3>$B$9;D3>$B$10));D3*$B$12; 
 
A próxima etapa é determinar a resposta para o valor FALSO, que podem ser dois valores: o 
desconto de 10% ou nenhum desconto. Como existe mais de uma hipótese, começamos pela 
função SE e depois, como teremos que colocar duas condições alternativas para o conceder o 
desconto de 10%, inserimos estas condições através da função OU: 
 
=SE(E(E3=”v”;OU(B3>$B$9;D3>$B$10));D3*$B$12;SE(OU(B3>$B$9;D3>$B$10); 
 
Finalmente resta colocar a resposta para a condição verdadeira, que será o desconto de 10%, 
calculado por D3*B11 e 0 (zero) para a condição falsa: 
 
=SE(E(E3=”v”;OU(B3>$B$9;D3>$B$10));D3*$B$12;SE(OU(B3>$B$9;D3>$B$10);D3*$B$
11;0)) 
 
Desconto de 15% para compras à vista, com ou quantidades maiores que 4 ou valores totais 
maiores que 100,00 
 
Quando as vendas melhorarem o “seu” Virgílio disse que já sabe como trocar OU por E. 
 
Esse material é de autoria de Edna Araújo dos Santos 34
 
 
Excel Intermediário
 
6. FUNÇÕES ESTATÍSTICAS 
 
Nesta categoria há funções que fazem cálculos estatísticos, e algumas delas você já conheceu no 
treinamento básico, como MÉDIA, que retorna a média aritmética. A função MÁXIMO e 
MÍNIMO, que retornam o maior ou o menor valor existente em uma lista de valores, 
CONT.VALORES, que conta itens não vazios existem em uma lista, dentre outras. 
 
6.1 Contando itens em uma lista 
 
6.1.1 Funções CONT.NÚM e CONTAR.VAZIO 
 
Além de CONT.VALORES, existem outras funções de contagem, como CONT.NÚM, que conta 
somente o número de células com valores numéricos, ou CONTAR.VAZIO, que conta o número 
de células vazias de um intervalo. 
 
A sintaxe dessas funções é: 
 
=CONT.NÚM(Valor1;Valor2;...Valor30) 
 
Onde Valor1 até Valor30 são os argumentos, que podem fazer referência a vários tipos de dados, 
como números, textos, valores lógicos. Porém, o resultado será a contagem somente dos 
números. 
 
=CONTAR.VAZIO(Intervalo) 
 
O intervalo é a região da planilha a ser contada. Essa região pode conter diversos tipos de 
valores, porém somente as células vazias serão contadas. 
 
A célula D2 da planilha da figura a seguir contém a fórmula que calcula quantas células com 
números há no intervalo A2:B9, cujo resultado é 4: 
 
=CONT.NÚM(A2:B9) 
 
 
 
Esse material é de autoria de Edna Araújo dos Santos 35
 
 
Excel Intermediário
 
Em D3 a contagem é do número de células vazias existentes no mesmo intervalo, resultando em 
6: 
 
=CONTAR.VAZIO(A2:B9) 
 
Se o cálculo fosse feito através da função CONT.VALORES, através da fórmula a seguir, o 
resultado seria 10, pois seriam contadas quantas células não vazias existem no intervalo: 
 
=CONT.VALORES(A2:B9) 
 
Para CONT.NÚM e CONT.VALORES, você pode especificar até 30 argumentos, como: 
 
=CONT.NÚM(A2:B9;C20:E30;G5:J10) 
 
Nessa fórmula há 3 argumentos. 
 
 
6.1.2 Condicionando uma contagem pela função CONT.SE 
 
E para contar quantos itens existem que satisfaçam a uma condição? Como contar quantos itens 
existem na lista referentes a um determinado produto? Quantos registros há com quantidades 
maiores que 10? 
Para solucionar questões como essas existe a função CONT.SE, que conta quantos itens existem 
no intervalo de acordo com um critério. 
 
Sua sintaxe é: 
 
=CONT.SE(Intervalo;Critérios) 
 
 
Os argumentos são: 
 
 
Argumento Descrição 
Intervalo É a região onde a contagem será feita 
Critérios É a condição para fazer a contagem, 
podendo ser 
Número, texto ou uma expressão em que 
podem ser utilizados os operadores de 
comparação 
 
 
Esse material é de autoria de Edna Araújo dos Santos 36
 
 
Excel Intermediário
 
Na lista da figura a seguir há nomes de vendedores na coluna A . Na célula H2 existem o nome 
de uma vendedora, Luciana. Para contar quantos registros existem com esse nome, na célula I2 
há a fórmula: 
 
=CONT.SE(A2:A30;H2) 
 
Onde A2:A30 é o intervalo a ser pesquisado, ou seja, o nome “Luciana” deverá ser localizado 
nesta região. H2 é o critério a ser considerado para contar. Então, é pesquisado o valor da célula 
H2, “Luciana”, no intervalo A2:A30 e o resultado será a quantidade de itens existentes com esse 
nome. 
É lógico que o critério também pode ser digitado. Nesse caso, ele deve estar entre aspas e a 
fórmula seria: 
 
=CONT.SE(A2:A30;”Luciana”) 
 
Caso não digite as aspas, o Excel entende Luciana como um nome que pode ser dado a uma 
célula. 
 
Para contar quantos itens diferentes de Luciana, digite na célula H2 “<>Luciana” (sem as aspas). 
Nesse caso, estamos fazendo referência à fórmula anterior, em que a referência H2 é o critério. 
 
Para contar quantos registros existem com quantidades maiores que 18, considerando que o 
intervalo D2:D30 tem o nome de Qtde, a fórmula será: 
Esse material é de autoria de Edna Araújo dos Santos 37
 
 
Excel Intermediário
 
 
=CONT.SE(Qtde;”>18”) 
Se precisar inserir mais de uma condição para contar, utilize a função BDCONTAR ou 
BDCONTARA, da categoria de banco de dados. 
 
7. FUNÇÕES MATEMÁTICAS E TRIGONOMÉTRICAS 
 
Esta categoria engloba funções que fazem cálculos matemáticos, como SOMA, que soma os 
argumentos, SOMASE, que faz a soma condicional, ABS, que retorna o valor absoluto de um 
número (um número sem sinal), COMBIM, que calcula o número de combinações possíveis em 
um conjunto de números, funções de arredondamento, forçando que o cálculo seja feito com um 
certo número de casas decimais, e trigonométricas, como SEM e COS, que calculam 
respectivamente o seno e o cosseno de um ângulo, dentre outras. 
 
 
7.1 Somando com uma condição 
 
Para somar valores é utilizada a função SOMA, que você já conhece. Porém, quando precisar 
somar de acordo com uma condição, ou seja, quando for necessário colocar um critério, como 
calcular a soma da quantidade vendida de um determinado produto em uma lista de produtos, 
existe a função SOMASE. 
Sua sintaxe é: 
 
=SOMASE(Intervalo;Critérios;Invervalo_Soma) 
 
Os argumentos são: 
 
Argumento Descrição 
Intervalo É o intervalo onde se encontram os valores 
a serem pesquisados, ou seja, em uma lista 
de produtos, é o intervalo de células onde 
estão todos os produtos 
Critérios É a condição a ser imposta, podendo fazer 
uso dos operadores de comparação. Na lista 
de produtos, será o nome do produto a ser 
pesquisado para que seja feita a soma de 
suas quantidades. 
Intervalo_soma É o intervalo onde estão os números a 
serem somados. Neste dos produtos, são as 
células que contém todas as quantidades 
 
Na planilha da figura a seguir, existe uma lista com nomes de produtos e suas quantidades. Para 
calcular a soma das quantidades do produto que está na célula D2, em E2 há a fórmula: 
 
Esse material é de autoria de Edna Araújo dos Santos 38
 
 
Excel Intermediário
 
=SOMASE(A2:A20;D2;B2:B20) 
 
A fórmula da célula E2 calcula a quantidade total do produto que está em D2 
 
 
A2:A20 é o intervalo onde será pesquisado o produto digitado em D2, Uva. Quando encontrado, 
é somado o valor das células que estão no intervalo B2:B20, na mesma linha do produto 
encontrado na coluna A. 
Se digitar um critério, este deve estar entre aspas. É lógico que, no exemplo anterior, D2, que é o 
critério, não pode conter aspas porque é uma referência de célula.Porém, se ao invés de indicar a 
célula D2 como critério, utilizar o texto “Uva”, este deve estar entre aspas. Nesse caso a fórmula 
seria: 
 
 
=SOMASE(A2:A20;”uva”;B2:B20) 
 
 
Você também pode utilizar os caracteres curinga, como o asterísco (*) e o ponto de interrogação 
(?), para colocar condições. 
Veja alguns exemplos de critérios: 
 
 
 
 
 
 
Esse material é de autoria de Edna Araújo dos Santos 39
 
 
Excel Intermediário
 
 
 
“>F” Soma os valores dos itens que cuja palavra seja maior que F em 
ordem alfabética crescente 
“<>uva” Soma os valores dos itens que sejam diferentes de “Uva” 
“m*” Soma os valores dos itens que começam com a letra “M” 
“M??” Soma os valores dos ítens que começam com a letra “M” e que 
tenham 3 caracteres 
“<”&B5 Soma os valores dos itens que sejam menores que o valor da célula 
B5. Neste caso é preciso concatenar o sinal de maior com B5 
porque esta é uma referência de célula e não pode estar entre 
aspas 
 
 
 
A função SOMASE não diferencia letras 
maiúsculas de minúsculas, porém não se 
esqueça que: se digitar espaços em branco, 
estes SÃO considerados 
 
 
8. FUNÇÕES DE BANCO DE DADOS 
 
Você já se deparou com uma questão na qual precisou calcular a quantidade de itens existentes 
em uma lista que atendessem a mais de uma condição? Um exemplo é contar quantos registros 
existem referentes a um determinado produto em uma lista de produtos, com quantidades entre 
10 e 15 e preços iguais a R$ 32,00. 
Para resolver questões como essa existem as funções de banco de dados que executam diversos 
tipos de cálculos em uma lista baseados em critérios. 
Para utilizar uma destas funções é necessário haver uma lista na qual os cálculos serão baseados e 
uma região de critérios, que podem estar na mesma ou em outra planilha de qualquer pasta de 
trabalho. 
 
A sintaxe das funções que serão apresentadas a seguir é: 
 
=Nome da função(Banco_dados;Campo;Critérios) 
Esse material é de autoria de Edna Araújo dos Santos 40
 
 
Excel Intermediário
 
Os argumentos são: 
 
Argumento Descrição 
Banco_dados É a lista que contém os dados a serem pesquisados. É 
imprescindível referenciar também a primeira linha da lista, que 
deve conter os nomes dos campos, como Nome, Telefone, 
Cidade. Também pode ser um intervalo nomeado. 
Campo É o rótulo do campo da lista onde será feito o cálculo. Ele deve 
estar entre aspas. Também pode ser o número da posição deste 
campo na lista. Por exemplo, se for 2, o cálculo será feito no 
campo “Telefone” 
Critérios É um intervalo de células que contém as condições a serem 
consideradas para calcular. Para criar um intervalo de critérios, 
siga a mesma regra utilizada na elaboração do intervalo de 
critérios do filtro avançado, com a diferença de que este pode 
estar em qualquer planilha ou até mesmo em outra pasta de 
trabalho 
 
Antes de criar a fórmula, crie o intervalo de critérios, que deve conter o nome dos campos na 
primeira linha e as condições a partir da segunda. Se tiver dúvidas de como criar um intervalo de 
critérios, consulte o ítem Filtro Avançado do capítulo TRABALHANDO COM BANCO DE 
DADOS. 
A partir do intervalo A1:D20, que é o banco de dados, da planilha da figura a seguir, vamos 
aplicar várias funções de banco de dados. 
 
 
 
Esse material é de autoria de Edna Araújo dos Santos 46
 
 
Excel Intermediário
 
 
8.1 Contando pelas funções BDCONTAR e BDCONTARA 
 
Primeiramente, vamos criar um intervalo de critérios a partir da célula F1 para contar quantos 
registros existem com datas iguais ou superiores a 1/1/99, da categoria Atacado, com o produto 
Camisa e quantidades maiores que 20. Veja na próxima figura que os nomes dos campos estão 
em F1 até I1, que é a primeira linha do intervalo. Abaixo destes, são inseridas condições de 
campo. 
Lembre-se que um intervalo de critérios as condições que estiverem na mesma linha são 
cumulativas, ou seja, têm que ser atendidas a todas as condições. E, quando estiverem em linhas 
diferentes, não são cumulativas, significando que será atendida a uma ou a outra condição. 
Resumindo, o que estiver na mesma linha significa E e em linhas diferentes OU. 
Se houver apenas uma condição para contar, utilize a função CONT.SE. 
 
 O intervalo de critérios está em F1:I2 
 
 
 
 
Esse material é de autoria de Edna Araújo dos Santos 47
 
 
Excel Intermediário
 
As funções de banco de dados que fazem contagem são BDCONTAR e BDCONTARA. Note 
que a diferença nos seus nomes é que a primeira não tem a letra “A” no final. 
 
BDCONTAR conta quantas células com números existem em um intervalo de acordo com as 
condições do intervalo de critérios. Já BDCONTARA, conta quantas células não vazias, ou seja, 
que contenham qualquer tipo de dado existentes em um intervalo. 
 
Neste caso, vamos utilizar BDCONTARA. 
 
A fórmula da célula F5 é: 
 
=BDCONTARA(A1:D20;C1;F1:I2) 
 
 
Onde Banco_dados é A1:D20, ou seja, toda a lista (banco de dados) com os dados. Campo é C1, 
pois serão contadas as células existentes nesta coluna com a condição especificada no intervalo 
de critérios. Neste caso, poderia também ser digitado o nome do campo “Produto” entre aspas ou 
o número do campo, 3, pois este é o terceiro campo da lista. Esta regra serve para qualquer 
função desta categoria. Critérios é F1:I2, que é o intervalo com as condições para que a 
contagem seja feita. 
O resultado é 2, isto é, existem dois registros na lista que atendam às condições do intervalo de 
critérios. 
Quanto a essa fórmula, no segundo argumento, poderíamos ter especificado qualquer um dos 
quatro campos, pois a função BDCONTARA não diferencia o tipo de dado existente na célula. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Esse material é de autoria de Edna Araújo dos Santos 48
 
 
Excel Intermediário
 
 
As fórmulas de F5 e F6 obtém o mesmo resultado, contando o número de registros existentes na lista 
conforme o intervalo de critérios, que está em F1:I2 
 
Se empregar BDCONTAR, a fórmula que obtém o mesmo o resultado é: 
 
=BDCONTAR(A1:D20;D1;F1:I2) 
 
Com a diferença que o campo é D1, “Qtde”. Este é um campo numérico. 
 
Se alterar intervalo de critérios, o resultado da 
fórmula também será modificado de acordo 
com as novas condições. Isso significa que a 
fórmula mantém um vínculo com o intervalo 
de critérios. Se necessário, crie outro intervalo 
para os critérios de outras fórmulas. 
 
 
 
 
 
 
 
 
 
Esse material é de autoria de Edna Araújo dos Santos 49
 
 
Excel Intermediário
 
8.2 Somando pela função BDSOMA 
 
Agora vamos calcular a quantidade existente do produto Jaqueta na categoria Atacado. A função 
de banco de dados que soma valores de acordo com um intervalo de critérios é BDSOMA. 
O intervalo de critérios será G1:H2 e a fórmula, que está na célula F5 da figura a seguir é: 
 
=BDSOMA(A1:D20;”Qtde”;G1:H2) 
 
O resultado é 4. 
 
 
Para somar as quantidades existentes de jaquetas no atacado ou meias no varejo, o intervalo de 
critérios deve conter mais de uma linha, onde as condições estarão em linhas diferentes. A figura 
a seguir contém os critérios no intervalo G1:H3 e a fórmula é: 
 
=BDSOMA(A1:D20;”Qtde”;G1:H3) 
 
Na célula F5 são somadas as quantidades de jaquetas no atacado ou meias no varejo 
 
O resultado é 97. 
 
 
Esse material é de autoria de Edna Araújo dos Santos 50
8.3 Conhecendo as funções BDMÉDIA, BDMÁX e BDMÍN 
 
 
Excel Intermediário
 
 
A função que calcula a média aritmética segundo condições é BDMÉDIA. 
Na fórmula anterior, foi calculada a soma das quantidades de 1996. Para calcular a média desse 
ano, podemos utilizar o mesmo intervalo de critérios através da fórmula: 
 
 
=BDMÉDIA(A1:D20;”Qtde”;G1:H2) 
 
 
 Calculando a média aritmética de 1996 
 
 
Para calcular o maior valor, utilize a função BDMÁX, pela fórmula: 
 
=BDMÁX(A1:D20;”Qtde”;G1:H2) 
 
Retornando 645. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
O menor valor de 1996 deve sercalculado pela funcão BDMÍN e a fórmula é: 
Esse material é de autoria de Edna Araújo dos Santos 51
 
 
Excel Intermediário
 
 
=BDMÍN(A1:D20;”Qtde”;G1:H2) 
 
Cujo resultado é 2. 
Esse material é de autoria de Edna Araújo dos Santos 52
 
No
Pasta: C:\APOSTILAS\Cu
M
Títu
Assunt
Aut
Pal
Co
Data de criação
Núm
Últim
Sal
Tem
Últim
Co
 
 
 
 
me do arquivo: Apostila de Excel Intermediário.DOC 
rsos\Excel Intermediário 
odelo: C:\Documents and Settings\ske6022\Dados de aplicativos\Microsoft\Templates\Normal.dot 
lo: excel 5 
o: 
or: Marcelo Mariano da Silva 
avras-chave: 
mentários: 
: 22/6/1997 22:36:00 
ero de alterações: 198 
a gravação: 12/6/2006 10:24:00 
vo por: Serasa 
po total de edição: 1.593 Minutos 
a impressão: 12/6/2006 10:25:00 
mo a última impressão 
Número de páginas: 48 
Número de palavras: 7.953 (aprox.) 
Número de caracteres: 40.004 (aprox.) 
	1.0 FUNÇÕES 
	1.1 Anatomia de uma Função 
	1.2 Argumentos 
	2. CONVENÇÕES UTILIZADAS NA APOSTILA 
	2.1 Comandos de Menu 
	2.2 Mouse 
	Dicas 
	3. APERFEIÇOANDO A CRIAÇÃO DE FÓRMULAS 
	3.1 Criando fórmulas com endereços de outras pastas de trabalho 
	Após preencher a fórmula para as demais células, o resultado será este 
	Os valores da coluna C foram atualizados 
	4. TRABALHANDO COM BANCO DE DADOS 
	 
	4.1 Classificar por uma lista personalizada 
	4.2 Filtro Avançado 
	 4.3 Subtotais 
	4.3.1 Inserindo subtotais na lista 
	4.3.2 Removendo subtotais 
	4.4 Tabela e Gráfico Dinâmicos 
	4.4.1 Compreendendo a Tabela dinâmica 
	5. FUNÇÕES LÓGICAS 
	6. FUNÇÕES ESTATÍSTICAS 
	6.1 Contando itens em uma lista 
	6.1.1 Funções CONT.NÚM e CONTAR.VAZIO 
	6.1.2 Condicionando uma contagem pela função CONT.SE 
	7. FUNÇÕES MATEMÁTICAS E TRIGONOMÉTRICAS 
	7.1 Somando com uma condição 
	8. FUNÇÕES DE BANCO DE DADOS 
	8.1 Contando pelas funções BDCONTAR e BDCONTARA 
	8.2 Somando pela função BDSOMA 
	8.3 Conhecendo as funções BDMÉDIA, BDMÁX e BDMÍN