Cursos Livres - Excel Avançado - Apostila III

Prévia do material em texto

EXCEL AVANÇADO 3
SUBTOTAIS, OPERADORES DE BUSCA 
E TROCA E FUNÇÕES DE BANCO DE 
DADOS
Olá! Neste capítulo daremos continuidade aos nossos estudos de Excel 
Avançado, fazendo uso de subtotais, operadores de busca e troca e funções 
de bancos de dados.
As ferramentas apresentadas aqui são de suma importância para que você 
possa adquirir versatilidade e eficiência no tratamento de dados utilizando 
o Excel. Elas exigem uma intimidade com os princípios fundamentais dos 
operadores lógicos que foram explorados no Capítulo 1. Portanto, se você 
ficou com alguma dúvida, este é o momento de fazer uma breve revisão 
deste capítulo. Por meio do uso destas ferramentas, você poderá implementar 
comandos de buscas e operações condicionais, pesquisa por referência e 
operações com data e hora dinâmicas.
Organizamos o capítulo de forma a apresentar o conceito geral do grupo 
de funções utilizadas e, em seguida, detalhar cada uma destas funções. Tenha 
em mente que este é o capítulo mais extenso deste curso, portanto faça/
replique todos os exemplos apresentados e tenha certeza de seguir adiante 
somente quando os conceitos da seção atual estiverem bem assimilados.
Sem mais, mãos à obra: temos um vasto universo de funções a explorar!
APRESENTAÇÃO
Organização
Greisse Moser 
Badalotti
Reitor da 
UNIASSELVI
Prof. Hermínio Kloch
Pró-Reitora do EAD
Prof.ª Francieli Stano 
Torres
Autor
Paolo Moser
Edição Gráfica 
e Revisão
UNIASSELVI
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
ESTRUTURA E 
FUNCIONAMENTO DO 
ENSINO NO BRASIL
.03
3 INTRODUÇÃO
Olá! Neste capítulo daremos continuidade aos nossos estudos de Excel 
Avançado, fazendo uso de subtotais, operadores de busca e troca e funções 
de bancos de dados.
As ferramentas apresentadas aqui são de suma importância para que você 
possa adquirir versatilidade e eficiência no tratamento de dados utilizando 
o Excel. Elas exigem uma intimidade com os princípios fundamentais dos 
operadores lógicos que foram explorados no Capítulo 1. Portanto, se você 
ficou com alguma dúvida, este é o momento de fazer uma breve revisão 
deste capítulo. Por meio do uso destas ferramentas, você poderá implementar 
comandos de buscas e operações condicionais, pesquisa por referência e 
operações com data e hora dinâmicas.
Organizamos o capítulo de forma a apresentar o conceito geral do grupo 
de funções utilizadas e, em seguida, detalhar cada uma destas funções. Tenha 
em mente que este é o capítulo mais extenso deste curso, portanto faça/
replique todos os exemplos apresentados e tenha certeza de seguir adiante 
somente quando os conceitos da seção atual estiverem bem assimilados.
Sem mais, mãos à obra: temos um vasto universo de funções a explorar!
3.1 SUBTOTAIS
A função SUBTOTAL pode ser utilizada para realizar operações que envolvem 
somatórios e contagens sobre um conjunto de células. Com ela é possível calcular, 
por exemplo, o valor médio de uma determinada coluna. Apesar de existirem funções 
especiais para isto (como a função SOMA e MÉDIA, por exemplo), o SUBTOTAL oferece 
mais versatilidade, principalmente quando utilizado concomitantemente com o autofiltro.
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
A sintaxe do Subtotal é =SUBTOTAL(núm_função,ref1,[ref2],...]), 
onde núm_função refere-se à operação desejada, enquanto os demais 
parâmetros se referem aos intervalos de dados desejados.
O sucesso no uso desta ferramenta baseia-se na escolha adequada 
do parâmetro núm_função. A TABELA 3.1 apresenta os códigos relativos 
às funções desejadas. Explicaremos a função “média” no Exemplo 3.1 (o 
funcionamento das demais funções é análogo).
TABELA 3.1 – ARGUMENTOS DA FUNÇÃO SUBTOTAL
Núm_função
(inclui valores ocultos)
Núm_função
(ignora valores ocultos)
Função
1 101 MÉDIA
2 102 CONTA
3 103
CONT.
VALORES
4 104 MÁXIMO
5 105 MÍNIMO
6 106 MULT
7 107 DESVPAD
8 108 DESVPADP
9 109 SOMA
10 110 VAR
11 111 VARP
FONTE: Disponível em: <http://bit.ly/2l2If7L>. Acesso em: 5 maio 2017.
Exemplo 3.1. Considere que os dados da FIGURA 3.1 (fictícios) representam 
registros de usuários de um site de e-commerce que atua na América do Sul. Suponha 
que você queira saber a idade média dos brasileiros cadastrados na plataforma.
FIGURA 3.1 – USUÁRIOS (FICTÍCIOS) DE UM SITE DE VENDAS OPERANTE 
NA AMÉRICA DO SUL
FONTE: O autor
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
Resolução 3.1. Admitindo que os dados estão nas mesmas células 
apresentadas na FIGURA 3.1, digite na célula F13 (arbitrária) a seguinte 
expressão: =SUBTOTAL(101;C:C). Neste caso, o parâmetro 101 indica que 
o valor a ser calculado é a média, omitindo valores ocultos (vide TABELA 3.1). 
Neste instante, a célula F13 deve mostrar o valor 38,7. Esta é a média de idade 
de todos os registros. Agora, habilite o autofiltro na coluna “Nacionalidade” e 
selecione apenas o atributo “Brasil”. Pronto: a célula F13 mudou o valor para 
31,75, o que corresponde à média de idade dos consumidores brasileiros. Note 
que, se tivéssemos usado a função MÉDIA, os valores ocultados continuariam 
contabilizando para o valor da média. Esta é a versatilidade possibilitada pela 
função SUBTOTAL.
3.2 FUNÇÕES DE PROCURA
A funções de procura permitem obter valores a partir de buscas baseadas 
em critérios dentro da própria planilha, ou de planilhas externas. Exploraremos 
cada uma destas funções nas seções que seguem. Para a maioria dos exemplos, 
utilizaremos os mesmos dados utilizados no Exemplo 3.1 (FIGURA 3.1). 
Para tanto, esteja certo de que eles estão inseridos exatamente nas células 
apresentadas aqui, pois as referências das funções dependem disso.
3.2.1 Função PROCV
A função PROCV (“procura vertical”) retorna um atributo de uma coluna 
específica, baseado em um valor encontrado em uma coluna de referência.
A sintaxe do comando PROCV é =PROCV(valor_procurado; matriz_
tabela; núm_índice_lin; [procurar_intervalo]). Os argumentos 
são autoexplicativos e serão detalhados no Exemplo 3.2.
Chamamos atenção para o campo “procurar intervalo”. Primeiramente, 
note que este campo é opcional, pois está entre chaves. Sua opção padrão 
(quando omitido) é FALSO. Se você optar por modificá-lo para VERDADEIRO, a 
função retorna o valor para o registro mais próximo do pesquisado, caso não 
exista o valor exato. Esta funcionalidade normalmente não é do interesse do 
usuário, pois, normalmente, estamos procurando por valores exatos e bem 
determinados.
Exemplo 3.2. Utilizando a base de dados da FIGURA 3.1, utilize a função 
PROCV para obter a idade do usuário “Mateus Oliveira”.
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
Resolução 3.2. Escolha a célula, de acordo com sua necessidade, 
que receberá a função PROCV. Para este exemplo (e para os exemplos 
subsequentes, quando possível), utilizaremos por padrão a célula G3. Nesta 
célula, digite a seguinte função: =PROCV(A4;A1:C11;3). A4 faz referência 
ao nome procurado (“Mateus Oliveira”). A1:C11 representa o intervalo onde 
faremos a procura. Note que o nome procurado deve estar na primeira coluna 
deste intervalo. Por fim, “3” representa a coluna do intervalo que contém o 
valor procurado (“Idade”). O argumento opcional “Procurar intervalo” pode 
ser omitido, pois ele é FALSO, por padrão, coincidindo com nosso objetivo 
de buscar o valor exato, e não aproximado. Após teclar ENTER, a célula G3 
deverá apresentar o valor 25 – a idade de “Mateus Oliveira”.
3.2.2 Função PROCH
A função PROCH (procura horizontal) faz exatamente a mesma coisa que 
a função PROCV – retorna um atributo específico baseado em um valor de 
referência –, com a particularidade de que agora os valores são procurados 
horizontalmente (nas linhas), e não nas colunas.
A sintaxe do PROCH é =PROCV(valor_procurado; matriz_
tabela; núm_índice_lin; [procurar_intervalo]). Os argumentos, 
bem comoo opcional “procurar intervalo”, têm a mesma funcionalidade de 
seus análogos na função PROCV.
Exemplo 3.3. Suponha que a tabela da FIGURA 3.1 esteja transposta, 
conforme FIGURA 3.2. Utilize a função PROCH para obter a idade do mesmo 
usuário “Mateus Oliveira”, do exemplo anterior.
FIGURA 3.2 – USUÁRIOS (FICTÍCIOS) DE UM SITE DE VENDAS OPERANTE NA AMÉRICA DO SUL 
(TRANSPOSIÇÃO DA TABELA DA FIGURA 3.1).
FONTE: O autor
Resolução 3.3. Na célula B5, digite a seguinte função: =PROCH(D1;A1:K3;3). 
Os dois primeiros argumentos são idênticos à função PROCV. Quanto ao 
terceiro argumento (“3”), sua única diferença é que agora ele faz referência à 
linha onde encontra-se o valor procurado (na função PROCV, esta referência 
era para a coluna desejada). Novamente, após teclar ENTER, a célula B5 deverá 
apresentar o valor 25 – a idade de “Mateus Oliveira”.
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
3.2.3 Função PROC
PROC tem funcionalidade semelhante a funções anteriores PROCV e PROCH, 
com a diferença de que agora especificamos diretamente o campo de pesquisa e 
o campo de resultados, sem o uso de índices. A opção “procurar intervalo”, neste 
caso, é implícita, sendo que, caso não encontrar o valor procurado, a função 
PROC retorna o resultado para o valor menor mais próximo do valor pesquisado.
Esta função naturalmente oferece suporte para aplicações vetoriais 
ou matriciais. Como utilização direta da função, da mesma forma que as 
anteriores, exemplificaremos a forma vetorial da função (uma aplicação de 
função matricial pode ser conferida na Seção 3.3.5, a respeito da função 
FREQUÊNCIA).
A sintaxe de PROC vetorial é =PROC(valor_procurado; vetor_proc; 
[vetor_result]) . O significado e a configuração de cada um destes 
parâmetros estão detalhados no Exemplo 3.4.
Exemplo 3.4. Obter a idade do usuário “Mateus Oliveira” da base de dados 
da FIGURA 3.1, agora utilizando a função PROC.
R e s o l u ç ã o 3 . 4 . N a c é l u l a G 3 , d i g i t e a s e g u i n t e f u n ç ã o : 
=PROC(A4;A2:A11;C2:C11). A4 faz referência ao nome procurado (“Mateus 
Oliveira”). A1:A11 representa o intervalo onde procuraremos este nome. C2:C11 
representa o intervalo que contém o valor procurado (“Idade”). Note que 
informamos diretamente os intervalos (que não precisam ser adjacentes) e 
não fizemos uso de índices. Conforme esperado, após teclar ENTER, a célula 
G3 deverá apresentar o valor 25 – a idade de “Mateus Oliveira”.
3.2.4 Funções ÍNDICE e CORRESP
As funções PROCV, PROCH e PROC resolvem a maioria dos problemas 
de pesquisa de valores por referência, porém: (i) PROCV procura a referência 
na primeira coluna da matriz; (ii) PROCH procura a referência na primeira 
linha da matriz e (iii) PROC exige que a coluna de pesquisa seja explicitada.
Uma combinação das funções ÍNDICE e CORRESP imprime maior 
versatilidade às opções de procura por referência, com a necessidade de 
informar um único intervalo que, eventualmente, contenha mais opções de 
pesquisa.
A sintaxe para esta combinação é dada por =ÍNDICE(matriz; 
CORRESP(valor_procurado; matriz_procurada; [tipo_
correspondência]); [núm_coluna]). Muitos parâmetros? Calma, 
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
é mais simples do que parece. Optaremos por detalhar cada passo desta 
construção no Exemplo 3.5, para não confundir argumentos como “matriz” e 
“matriz_procurada”. Olhando para uma base de dados, tudo fica mais claro!
Exemplo 3.5. Utilizando as funções ÍNDICE e CORRESP combinadas, 
obtenha a idade do usuário uruguaio da FIGURA 3.1.
Resolução 3.5. Na célula G3, digite a seguinte função: =ÍNDICE(A1:C11; 
CORRESP(B3; B1:B11;0); 3). A1:C11 representa o intervalo onde ocorrerá 
a pesquisa. B3 indica o nome ao qual associaremos a pesquisa (“Uruguai”). 
B1:B11 é o intervalo que contém o nome pesquisado. O parâmetro “0” indica 
que queremos a correspondência exata ao nome procurado (normalmente, 
esta é a opção desejada – outras opções podem ser consultadas na página 
de ajuda (help) do Excel). Enfim, “3” representa a coluna do intervalo A1:C11 
que contém o valor final, procurado (“Idade”). Note que, com esta construção, 
não nos limitamos a utilizar como referência de pesquisa valores na coluna 
mais à esquerda do intervalo – utilizamos a coluna do meio (“Nacionalidade”). 
Isto é muito versátil, principalmente se tivéssemos várias colunas no mesmo 
intervalo e pretendêssemos realizar múltiplas buscas. Após teclar ENTER, a 
célula G3 deverá apresentar o valor 24 – a idade associada à nacionalidade 
“Uruguai”.
3.2.5 Função Fresquequência
Suponha agora que queremos contar o número de ocorrências de um 
atributo quantitativo (por exemplo, o atributo “Idade”) dentro de uma faixa de 
valores (por exemplo, “Quantos indivíduos têm de 20 a 30 anos?”. A função 
para isto chama-se FREQUÊNCIA.
A sintaxe desta função é =FREQUÊNCIA(matriz_dados; matriz_
bin). Por “matriz_dados”, entendem-se os dados a serem pesquisados e 
contabilizados. Por “matriz_bin”, entendem-se os limites superiores das classes 
a serem criadas (no caso de 20 a 30, o limite superior é 30). Note que o limite 
superior é incluído na classe.
Esta ferramenta requer uso matricial de funções. Na forma matricial, 
uma função é aplicada a um conjunto de células predefinidas, ou seja, o 
processamento é realizado levando em consideração as células adjacentes. 
Para tanto, ao invés de confirmar o cálculo da função simplesmente com a 
tecla ENTER, devemos usar a combinação CTRL+SHIFT+ENTER. Acompanhe o 
Exemplo 3.6 para entender o funcionamento matricial da função FREQUÊNCIA.
Exemplo 3.6. Suponha que desejamos uma contagem dos usuários listados 
na FIGURA 3.1, segundo sua faixa de idade. Utilize a função FREQUÊNCIA para 
obter este relatório, utilizando classes com amplitude de 10 anos.
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
Resolução 3.6. Primeiramente, preparamos manualmente as classes de 
idades nas quais serão agrupados os usuários. Para tanto, devemos tomar 
o cuidado de garantir que os valores mínimo e máximo dos dados estejam 
inclusos nestas classes. Como desejamos utilizar classes com amplitude de 
10 anos e a menor idade é 18 e a maior idade é 57. Uma configuração de 
classes possível é apresentada na FIGURA 3.3.
FIGURA 3.3 – INTERVALOS DE CLASSE PARA UTILIZAÇÃO DA FUNÇÃO 
FREQUÊNCIA, COM BASE NOS DADOS DA FIGURA 3.1.
FONTE: O autor
Agora, utilizaremos a função FREQUÊNCIA, na sua forma matricial. 
Para isto, selecionamos o intervalo I3:I7. Agora, digite na linha de fórmulas 
a seguinte expressão: =FREQUÊNCIA(C1:C11;H3:H7) . C1:C11 indica o 
intervalo onde estão os dados a serem agrupados. H3:H7 é o intervalo com 
os limites superiores das classes (lembre-se de que estes serão inclusos na 
respectiva classe). Agora, você deve aplicar a forma matricial desta função, 
por meio de CTRL+SHIFT+ENTER. Isto faz com que a função seja aplicada a 
todas as células de forma conjunta (matricial), para que haja distinção entre 
as classes (note que ela ficou escrita entre chaves – {}). Pronto, o intervalo 
H3:H7 deve ter sido preenchido com os valores 1, 2, 2, 2 e 3. Estes valores 
são o número de ocorrências de usuários em cada uma das cinco classes de 
idade criadas.
3.2.6 Função INDIRETO
A função INDIRETO permite retornar o conteúdo de uma determinada 
célula fazendo referência direta a esta célula (através de seu “endereço”). Por 
exemplo, imagine que você queira fazer referência constante à célula A2, que 
contém seu nome; INDIRETO permite que você explicitamente aponte para 
esta célula a partir da própria expressão A2. A vantagem disto é o dinamismo. 
A célula a ser pesquisada pode estar registrada em outra posição da planilha, 
de modo que você pode modificar esta pesquisa de acordo com sua própria 
necessidade.
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DEBANCO DE DADOS
A s in taxe do comando INDIRETO é mui to s imples , dada por 
=INDIRETO(texto_ref; [a1]). Os parâmetros são os seguintes: “texto_ref” 
refere-se a uma célula que contém o endereço da célula a ser pesquisada; 
“a1” (opcional – padrão VERDADEIRO) indica se a referência está na forma A1 
ou L1C1 (linha 1, coluna 1 – muito utilizada em programação de macros). Para 
aplicações gerais, não há necessidade de modificar o parâmetro [a1].
Exemplo 3.7. Suponha que você deseja obter o primeiro nome da lista 
de usuários da FIGURA 3.1. Utilize a função INDIRETO para solucionar esta 
necessidade.
Resolução 3.7. Esta função é muito simples. Basta digitar na célula G3 
a seguinte expressão =INDIRETO(“A2”) . A2 representa a célula da qual 
desejamos obter o valor. Note que ela está entre aspas porque o Excel entende 
este campo como um texto, e textos sempre são informados entre aspas. 
Após ENTER, a célula G3 deve apresentar o valor “João da Silva”.
3.3 FUNÇÕES ENCADEADAS
Funções encadeadas são simplesmente funções “dentro” de outras 
funções, ou seja, funções sendo usadas como argumentos de outras. Por 
exemplo, pense que você deseja sair de casa e não sabe se deve levar 
guarda-chuva. Antes de olhar fora da janela, você pensa: “SE estiver escuro 
E úmido, ENTÃO levarei o guarda-chuva”. Pronto, seu cérebro acabou de 
encadear a função E na função SE. Simples, não é? Note que já fizemos uso 
de encadeamento na Seção 3.3.4. Lá, encadeamos a função CORRESP na 
função ÍNDICE.
Nesta seção, veremos como o encadeamento pode ser útil quando 
lidamos com operadores lógicos como condicionais, conjunções e disjunções.
3.3.1 Função E
A função E implementa o funcionamento do operador lógico homônimo 
estudado no primeiro capítulo deste curso. Ela recebe um (ou mais) argumento 
e retorna um valor chamado booleano1, isto é, Verdadeiro ou Falso. A sintaxe 
deste operador é dada por =E(lógico1; [lógico2];...).
1 Esta variável recebe este nome em homenagem ao lógico/matemático George Boole (Inglaterra, 1815-1864).
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
Os argumentos “lógico” são muito versáteis, permitindo comparações 
e cálculos de expressões matemáticas. Conforme já estudado, a função E 
retornará valor Verdadeiro quando todas as expressões lógicas avaliadas 
forem verdadeiras. Apesar de que sua utilidade é potencializada com seu 
uso encadeado na função SE, o exemplo a seguir apresenta uma abordagem 
simplista/inicial, da função E.
Exemplo 3.8. Para os dados da FIGURA 3.1, suponha que você deseja 
saber quais usuários são brasileiros E têm mais de 30 anos. Utilize a função 
E para isso.
Resolução 3.8. Criaremos um novo campo, na coluna D (você pode 
dar o título que quiser para este campo – aqui, chamaremos simplesmente 
de “Operador E”). Na célula D2, criaremos a condição desejada, através da 
expressão =E(B2=”Brasil”; C2>30). Os parâmetros são autoexplicativos, 
não é mesmo? Estamos buscando pelos registros cujos usuários são brasileiros 
E têm mais de 30 anos (B2 e C2 representam a “Nacionalidade” e a “Idade”, 
respectivamente). Clique em Enter e, em seguida, arraste a fórmula até a última 
linha do intervalo (célula D11). Pronto: as células D2 e D8 devem estar com 
o atributo “VERDADEIRO”, as demais, “FALSO”. Note que são exatamente estes 
registros que atendem aos critérios pesquisados. Note também que “Mateus 
Oliveira” recebeu atributo FALSO (D4). Isso porque apenas um dos critérios 
foi atendido (ser brasileiro), mas o operador E exige que ambos os critérios 
sejam simultaneamente verdadeiros.
3.3.2 Função OU
Analogamente à função E, a função OU implementa o funcionamento 
do operador lógico homônimo estudado no primeiro capítulo deste curso. Ela 
também recebe um (ou mais) argumento e retorna um Verdadeiro ou Falso. 
A sintaxe deste operador é dada por =OU(lógico1; [lógico2];...).
A função OU retorna valor Verdadeiro quando ao menos uma das 
expressões lógicas avaliadas for verdadeira. Isto inclui a situação extrema em 
que todas as expressões são verdadeiras simultaneamente. Assim como a 
função E, sua utilidade é potencializada com seu uso encadeado na função SE, 
mas seu funcionamento simplificado é apresentado no exemplo que segue.
Exemplo 3.8. Para os dados da FIGURA 3.1, suponha que você deseja 
saber quais usuários são argentinos OU têm menos de 25 anos. Utilize a 
função OU para isso.
Resolução 3.8. Fazemos exatamente o mesmo procedimento do Exemplo 
3.7 (anterior), com a diferença de que agora substituímos a função pela 
seguinte expressão =OU(B2=”Argentina”;C2<25). Expanda novamente a 
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
função até a célula D11 e pronto: quatro valores atenderam ao seu critério de 
pesquisa. Note que o número de resultados VERDADEIRO é maior do que para 
a função E. Apesar disto depender diretamente do conjunto de dados, este 
resultado é esperado, pois a função OU é mais flexível, aceitando que apenas 
uma das condições seja verdadeira para atribuir valor lógico VERDADEIRO 
ao registro.
3.3.3 Função SE
Esta função implementa o comportamento lógico do operador “se ... 
então”, também explorada conceitualmente no primeiro capítulo deste material. 
O SE recebe como argumento uma condição a ser testada; se esta condição 
retornar valor Verdadeiro, então o SE retornará um valor determinado; caso 
contrário, outro valor será retornado. Complexo? Vamos raciocinar...
Suponha que você deseja fazer uma consulta em uma base de dados 
de uma escola. Suponha também que esta base de dados tenha duas colunas 
(atributos), a saber: frequência percentual e média geral. Os registros (linhas) 
correspondem às informações dos diversos alunos matriculados nesta escola. 
Agora, imagine que a direção da escola gostaria de oferecer um prêmio aos 
alunos com frequência superior a 90% e média geral superior a 9,5, e para 
isso necessita obter um relatório com os alunos contemplados. Neste caso, 
a direção poderia criar uma terceira coluna nesta tabela com o cabeçalho 
“CONTEMPLADO”, por exemplo. Esta coluna recebe “sim” ou “não”. Agora, 
basta testar os atributos das duas primeiras colunas simultaneamente e aferir 
o resultado. A lógica deste teste é: “Se frequência percentual for maior do 
que 90% e média geral for superior a 9,5, então atribuir ‘sim’ ao aluno, senão 
atribuir ‘não’ (na coluna CONTEMPLADO)”. Simples, não?
No exemplo acima você pode perceber o potencial da função SE quando 
utilizada conjuntamente com as funções E e OU2. Para implementar SE no 
Excel, utilizamos a seguinte sintaxe: =SE(teste_lógico; [valor_se_
verdadeiro]; [valor_se _falso]) . Os argumentos da função são 
autoexplicativos, mas serão detalhados minuciosamente no Exemplo 3.9, a 
seguir.
Exemplo 3.9. Suponha agora que a empresa que gerencia os dados da 
FIGURA 3.1 oferece seus serviços apenas para brasileiros maiores de 30 anos. 
Você deseja obter um relatório onde os registros que obedecem a estas 
condições estão listados como “APROVADO” e os demais como “REPROVADO”. 
Utilize a função SE e o operador lógico necessário para esta tarefa.
2 No caso do exemplo anterior, foi utilizada apenas a função E encadeada na função SE. O encadeamento da função 
OU é análogo.
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
Resolução 3.9. Após os mesmos procedimentos iniciais dos exemplos 
anteriores, substitua a função pela seguinte expressão =SE(E(B2=”Brasil”; 
C2>30);”APROVADO”;”REPROVADO”). Expanda a função até a célula D11. 
Pronto, agora as células D2 e D8 devem estar marcadas como “APROVADO”, 
enquanto as demais devem estar marcadas como “REPROVADO”.
3.3.4 Função SOMASE
Conforme a sintaxe sugere, a função SOMASE é um encadeamento 
nativo do Excel para as funções SOMA e SE. Ela permite somar valores em um 
intervalo a partir do atendimento de um determinado critério. Por exemplo, 
imagine que você tenha em mãosuma planilha com valores que variam de 0 
a 100. Agora, suponha que você queira somar somente os valores maiores do 
que 70. Este é um caso típico do uso da função SOMASE: ela só contabilizará 
como parcela, na soma, os valores que atenderem ao critério “ser maior do 
que 70”. 
A sintaxe do SOMASE é dada por =SOMASE(intervalo; critérios; 
[intervalo_soma]). Os dois primeiros argumentos são autoexplicativos. 
O último argumento (opcional) “intervalo_soma” refere-se ao intervalo real a 
ser somado; caso omitido, o Excel considera-o igual ao argumento “intervalo”. 
Na maioria dos casos, este último argumento é desnecessário.
Exemplo 3.10. Agora, suponha que você deseja somar as idades de todos 
os argentinos presentes na FIGURA 3.1. Utilize a função SOMASE para isso.
Resolução 3 . 10. Na cé lu l a G3 , d ig i te a segu in te exp res são : 
=SOMASE(B1:B11;”Argentina”;C1:C11) . B1:B11 é o intervalo onde 
procuraremos o critério desejado (“Argentina”). C1:C11 é o intervalo onde 
procuraremos os valores a serem somados (“Idades”). Após ENTER, a célula 
G3 deve apresentar o valor 84 (38+46).
3.3.5 Função CONT.SE
Suponha agora que você esteja diante da mesma planilha com valores 
entre 0 e 100, que havíamos hipotetizado para exemplificar a função SOMASE, 
na seção anterior. Suponha também que, agora, ao invés de somar, você 
deseja contar o número de ocorrências com valor maior do que 70. A função 
CONT.SE faz exatamente isto: Contabiliza apenas os registros que atendem a 
um critério especificado. Neste contexto, ela é um encadeamento nativo do 
Excel para as funções CONT.NÚM e SE.
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
A sintaxe do CONT.SE é muito simples e objetiva: =CONT.SE(intervalo; 
critérios), e sua aplicação está exemplificada a seguir.
Exemplo 3.11. Deseja-se saber quantos colombianos estão cadastrados 
na base de dados da FIGURA 3.1. Vamos obter esta contagem por meio da 
função CONT.SE.
Resolução 3.11. Na célula G3, digite a seguinte expressão: =CONT.
SE(B1:B11; “Colômbia”) . B1:B11 é o intervalo onde procuraremos o 
critério desejado (“Colômbia”). Após ENTER, a célula G3 deve apresentar o 
valor 2, indicando que existem dois colombianos no intervalo pesquisado.
3.3.6 Função SEERRO
Esta função é mais uma implementação da função SE, que permite a 
automatização daquilo que os programadores chamam de “tratamento de 
exceção”. Uma exceção, neste caso, é um erro devido a uma impossibilidade 
lógica. Por exemplo, imagine que você está dividindo, um a um, os valores 
da coluna A pelos valores da coluna B. Suponha também que alguns valores/
registros em B sejam iguais a zero, o que impossibilitará a divisão. Se você 
não estabelecer nenhuma condição, o Excel retornará #DIV/0 (erro por 
divisão por zero). Entretanto, eventualmente, gostaríamos de uma mensagem 
personalizada, do tipo “Valor da coluna B ausente”. É neste contexto que 
SEERRO se insere – retornando uma mensagem pré-especificada pelo usuário.
A sintaxe da função é =SEERRO(valor; valor_se_erro), onde 
“valor” pode ser a expressão ser avaliada e “valor_se_erro” a mensagem (ou 
valor) a ser retornado, caso “valor” ocasione um erro. Acompanhe o exemplo 
a seguir.
Exemplo 3.12. Considere que os dados abaixo (FIGURA 3.4) referem-se ao 
número de horas trabalhadas de diversos funcionários da empresa X. A terceira 
coluna traz o valor da hora trabalhada de cada funcionário (presumidamente, 
de acordo com sua função). Suponha que você deseja obter o valor a ser pago 
para cada um dos funcionários, de acordo com estes dados. Como você faria 
isso, tomando o cuidado de que a planilha foi preenchida pelo pessoal do 
Recursos Humanos da empresa e pode estar suscetível às falhas humanas?
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
FIGURA 3.4 – RELAÇÃO DE HORAS TRABALHADAS E VALOR/HORA 
DO FUNCIONÁRIOS DA EMPRESA X (FICTÍCIA)
FONTE: O autor
Resolução 3.12. Criaremos uma nova coluna (D), que chamaremos de 
“Salário”. Para saber quanto cada funcionário deverá receber, precisamos 
multiplicar o número de horas trabalhadas pelo valor monetário da hora. Note 
que a célula C5 tem um dado faltante, o que ocasionará um erro na multiplicação. 
Para tanto, na célula D2, digite a seguinte expressão: =SEERRO(B2*C2;"Dados 
Faltantes"). Em uma “tradução livre”, esta expressão “diz” para o Excel: “Se 
a multiplicação de B2 por C2 for possível, retorne este valor, caso contrário, 
informe “Dados Faltantes”. Expanda a fórmula até a célula D6. Note que a 
célula D5 apresentou a informação “Dados Faltantes”, enquanto as demais 
apresentaram o resultado da multiplicação Horas x Valor. Desta forma, fica 
fácil encontrar erros no preenchimento da planilha.
3.3.7 Função SOMARPRODUTO
A função SOMARPRODUTO corresponde ao encadeamento da função 
PRODUTO na função SOMA. Ela funciona da seguinte maneira: fornece-se 
duas (ou mais) colunas de uma matriz à função; esta multiplica os valores 
pareados (que estão na mesma linha) e, ao final, retorna a soma dos resultados 
destas multiplicações.
A s intaxe do SOMARPRODUTO é =SOMARPRODUTO(matriz1; 
[matriz2]; [matriz3]; ...), onde as matrizes (1,2,3...) correspondem 
aos intervalos que contêm colunas a serem multiplicadas e somadas. Se 
houver valores faltantes ou “não-interpretáveis” (letras, por exemplo), a função 
negligenciará essa linha no processo de soma, ou seja, será atribuído o valor 
zero ao produto desta linha. 
Exemplo 3.13. Considere os mesmos dados da FIGURA 3.4, mas substitua 
o valor faltante, na célula C5, por R$ 15,00. Suponha agora que você deseja 
saber qual o total a ser pago aos funcionários, de acordo com o valor da hora 
e as horas trabalhadas apresentadas. Lembre-se de que, para isto, você deve 
primeiramente ponderar o número de horas trabalhadas pelo seu respectivo 
valor monetário, de acordo com cada funcionário.
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
Resolução 3.13. Poderíamos primeiramente criar uma coluna com a 
multiplicação Horas x Valor, e depois somá-la. Entretanto, vamos omitir este 
passo com a função SOMARPRODUTO. Na célula G3, digite a expressão: 
=SOMARPRODUTO(B2:B6;C2:C6). Os intervalos representam os valores a 
serem multiplicados antes da soma – Hora e valor, respectivamente). Após 
ENTER, a célula G3 deverá apresentar o valor 5080. Isso quer dizer que o total 
que a empresa gastará com a folha de pagamento dos funcionários será R$ 
5080,00, de acordo com os dados informados.
3.4 FORMULÁRIOS
A ferramenta “Formulários” tem como objetivo facilitar a entrada de dados 
em uma planilha por meio de uma caixa de diálogo amigável e automatizada. 
Para utilizar esta ferramenta, aconselhamos primeiramente habilitar o acesso 
rápido a ela. Para tanto, adote o procedimento a seguir.
Na barra de ferramentas de acesso rápido, clique na seta para baixo 
e, em seguida, acesse a opção “Mais comandos...”, conforme explicitado na 
FIGURA 3.5.
FIGURA 3.5 – OPÇÃO “MAIS COMANDOS...” NA BARRA DE FERRAMENTAS DE ACESSO 
RÁPIDO DO EXCEL
FONTE: O autor
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
Em seguida, mude o campo “Escolher comandos em” para a opção “Todos 
os comandos”. Localize o comando “Formulário...” e, em seguida, clique em 
“Adicionar”, seguido de OK, conforme a FIGURA 3.6.
FIGURA 3.6 – ADICIONANDO O BOTÃO “FORMULÁRIO” À BARRA DE FERRAMENTAS DE ACESSO RÁPIDO 
DO EXCEL
FONTE: O autor
Pronto, agora o botão deve ter sido adicionado à barra de ferramentas 
de acesso rápido. Este será o botão utilizado para criar formulários. Para 
tanto, devemos informar a região da tabela que receberá o formulário. A 
primeira linha desta região deverá ser preenchida com os nomes dos atributos 
desejados. Uma vez feito isto, a região deve ser selecionada e o formulário 
deve ser criado. O desenvolvimento deste procedimento fica mais clarocom 
a prática efetiva, portanto pormenorizaremos a criação de formulários no 
Exemplo 3.14.
Exemplo 3.14. Utilize a ferramenta “formulários” para construir a base 
de dados da FIGURA 3.1 em uma nova planilha do Excel.
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
Resolução 3.14. Considerando que o botão formulários já tenha sido 
adicionado à barra de ferramentas de acesso rápido, abra uma planilha em 
branco. Crie os cabeçalhos desejados, no caso: Nome, Nacionalidade, Idade 
(nas células A1:C1). Agora, selecione estas três colunas e clique no botão 
“Formulário”, na barra de ferramentas de acesso rápido e clique em OK. 
Pronto, a janela que apareceu é a interface para você criar, rapidamente, 
seu formulário. Utilize a FIGURA 3.1. para preencher os respectivos dados. 
Para agilizar o processo, você pode mudar de campo utilizando a tecla TAB 
e, após digitar os três atributos de um dado registro, utilize a tecla ENTER. 
Neste momento, o registro é adicionado à planilha e os campos são limpos, 
permitindo que você insira os atributos de um novo registro. Após digitar 
todos os registros, clique em FECHAR e seu formulário estará completo. Note 
que, para um dado formulário, você sempre poderá adicionar novos registros 
selecionando as respectivas colunas e repetindo o procedimento indicado 
nesta resolução. Simples, não?
3.5 FUNÇÕES DE DATA E HORA (CÁLCULO COM DATA E HORA)
Suponha agora que você deseja fazer operações matemáticas com 
registros que tenham atributos quantitativos (como “horas”, por exemplo) 
e atributos qualitativos (“mês”, por exemplo). Você já identificou o entrave, 
certo? Atributos qualitativos são restritos quanto ao tratamento matemático, 
uma vez que operações algébricas não podem ser aplicadas a eles. Pensando 
nisso, os desenvolvedores do Excel implementaram uma série de ferramentas 
que permitem atingir este objetivo, chamadas popularmente de Funções de 
Data e Hora (apesar de mais atributos serem contemplados). Exploraremos 
estas funções nesta seção. Como estas funções possuem funcionalidades 
que dependem da data vigente em que o aluno estiver praticando, não 
apresentaremos um exemplo específico nesta seção. Acreditamos que as 
instruções da parte conceitual sejam suficientes para que o aluno possa 
executar estas funções no momento em que lhe for conveniente.
3.5.1 Função HOJE
A melhor parte das funções de data e hora é que o nome atribuído à 
função normalmente é muito intuitivo e fácil de lembrar. A função HOJE, 
por exemplo, retorna a data vigente, de acordo com a configuração do 
sistema operacional. Simples, não? Pois é, e tem mais: como a data é obtida 
diretamente do sistema, argumentos adicionais são desnecessários, sendo 
que sua sintaxe fica reduzida a =HOJE().
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
3.5.2 Função AGORA
A função AGORA é uma extensão direta da função HOJE, com o acréscimo 
da hora atual no valor retornado. Novamente, este valor é lido diretamente da 
configuração do sistema operacional, o que faz com que esta função também 
dispense argumentos, sendo sintaticamente definida como =AGORA().
3.5.3 Função DIA.DA.SEMANA
Suponha que você deseja saber em que dia da semana seu aniversário 
foi comemorado 10 anos atrás. Com um pouco de lógica e uma boa dose de 
paciência (ou com o auxílio de um calendário antigo), é possível obter esta 
informação – não esqueça de considerar os anos bissextos. Entretanto, com 
o auxílio do Excel e da função DIA.DA.SEMANA, você pode obter este dado 
de forma rápida e segura!
A sintaxe da função é =DIA.DA.SEMANA(núm_série; [retornar_tipo]). 
Aqui, considerações sobre os argumentos da função fazem-se necessários. 
Vamos lá:
• núm_série: Para poder realizar operações com datas, o Excel atribui um 
valor único e sequencial para cada data, desde o dia primeiro de janeiro 
de 1900, que corresponde ao número 1. Após esta data, a cada novo dia, 
este número sequencial é incrementado em um (i.e., dia 2 de janeiro de 
1900 corresponde ao número sequencial 2, e assim por diante). O atributo 
“núm_série” espera que você insira este número sequencial. Você pode fazer 
isso indiretamente, através do encadeamento da função DATA, que recebe 
uma data normal e converte, automaticamente, em seu número sequencial. 
A sintaxe da função DATA é =DATA(ano; mês; dia).
• [retornar_tipo]: Este argumento opcional ajusta a numeração para o dia 
da semana específico. Na sua forma-padrão, a numeração atribui “um” ao 
domingo, “dois” à segunda e assim por diante. A lista completa de opções 
é fornecida pelo Excel no momento em que você está implementando a 
função.
3.5.4 Função DIAS360
A função DIAS360 retorna o número de dias entre duas datas , 
considerando o ano comercial de 360 dias (12 meses de 30 dias cada). Para 
tanto, basta informar as duas datas desejadas. A sintaxe desta função é dada 
por =DIAS360(data_inicial; data_final; [método]). Os dois primeiros argumentos 
são autoexplicativos. O terceiro argumento [método] – opcional – configura 
a forma de tratar as ocorrências de dias 31: método americano ou europeu. 
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
O método padrão do Excel é o método americano; você pode consultar 
diferenças entre os dois métodos na página de suporte da Microsoft para 
o Excel: basta procurar pela função DIAS360. Não esqueça também que se 
espera que as datas inicial e final sejam informadas em função de seu número 
sequencial, então o uso encadeado da função DATA, conforme mencionado 
na seção anterior, novamente é bem-vindo.
3.5.5 Função DIATRABALHO
Esta função retorna o número sequencial associado a uma data que 
ocorrerá “n” dias úteis a contar a partir de uma data inicial. O Excel exclui 
automaticamente sábados e domingos do cômputo de dias úteis. Se desejar 
excluir também feriados, estes deverão ser informados através de uma lista. 
Então, a sintaxe desta função é dada por =DIATRABALHO(data_inicial; dias; 
[feriados]). Lembre-se de que, por padrão, a “data_inicial” deverá ser um 
número sequencial (use a função DATA para facilitar as coisas).
 CURSO LIVRE - EXCEL AVANÇADO 3 - SUBTOTAIS, OPERADORES DE BUSCA E TROCA E FUNÇÕES DE BANCO DE DADOS
REFERÊNCIAS
CINTO, Antonio Fernando; GÓES, Wilson Moraes. Excel avançado. Editora: 
Novatec, 2015.
MARTINS, António. Excel aplicado à gestão. Edições Sílabo, 2003.
PERES, Paula. Excel avançado. Edições Sílabo, 2011.