MÓDULO AULA 01

Prévia do material em texto

0 
 
Curso Microsoft Excel Avançado 
 
 
 
 
 
 
 
 
 
 
 
Curso Microsoft Excel 2013 
Avançado 
 
 
1 
 
Curso Microsoft Excel Avançado 
 
 
CURSO EXCEL AVANÇADO 2013 
Aula 01 
INTRODUÇÃO ÀS FUNÇÕES FINANCEIRAS DO EXCEL 
NESTE CAPÍTULO 
• Introduzindo o conceito fundamental do valor do dinheiro no tempo; 
• Usando as funções financeiras básicas do Excel VP, VF, NPER, PGTO, e TAXA; 
• Convertendo taxas de juros efetiva e nominal; 
• Calculando custos efetivos de empréstimos usando diferentes sistemas de cotações de 
taxas. 
 
Funções Financeiras do Excel: 
• VP(taxa, nper, pgto, vf, tipo); 
• VF(taxa, nper, pgto, vp, tipo); 
• PGTO(taxa, nper, vp, vf, tipo); 
• TAXA(nper, pgto, vp, vf, tipo); 
• NPER(taxa, pgto, vp, vf, tipo). 
 
Terminologia Básica 
Valor Presente (VP): Esta é a quantia principal. Se você investir $5.000 em uma aplicação, esta 
quantia representa o principal, ou valor presente, do dinheiro que você investiu. Se você 
tomou emprestado $15.000 para compra de um carro, esta quantia representa o principal ou 
valor presente do empréstimo. Valor Presente pode ser positivo ou negativo. 
Valor Futuro (VF): Este é o principal mais os juros. Se você investir $5.000 por cinco anos e 
ganhar 6% de juros anuais, você receberá $6.312,38 no final do prazo de cinco anos. A quantia 
é o valor futuro do seu investimento de $5.000. Se você conseguir um empréstimo de 
automóveis de três-anos no valor de $15.000 e pagar 7% de juros anuais, você pagará um total 
de $16.673,16. Esta quantia representa o principal mais os juros que você pagou. O Valor 
Futuro pode ser ou positivo ou negativo. 
Pagamento (PGTO): Por exemplo o pagamento da parcela do financiamento do seu veículo. 
Taxa de juros: Juro é uma porcentagem do principal, usualmente expressa numa base anual. 
Por exemplo, você poderia ganhar 5,5% de juros anuais em uma aplicação. Ou sua hipoteca 
poderá ter uma taxa de juros de 7,75%. 
Período: Este representa o momento quando os juros são pagos ou ganhos. Por exemplo, uma 
aplicação que paga juros trimestralmente ou o financiamento de veículos que exige 
pagamentos mensais. 
 
 
 
2 
 
Curso Microsoft Excel Avançado 
 
EXEMPLO 1 
Se $1.100,00 capitalizou para $1.250,00 em vinte e um meses, qual foi a taxa de crescimento 
mensal? 
Função exigida: TAXA(nper;pgto;vp;vf; tipo;estimativa) 
Esta função retorna 0,61059%: 
=TAXA(21;0;-1100;2000;0) 
Este exemplo é da perspectiva do depositante, assim o argumento vp é negativo e o 
argumento vf (um direito a receber) é positivo. Devido o prazo ser expresso em anos, taxa é 
efetiva ao ano. 
 
EXEMPLO 2 
Se $1.000 capitalizou para $2.000 em nove anos, qual foi a taxa de crescimento anual média? 
Função exigida: TAXA(nper;pgto;vp;vf; tipo;estimativa) 
Esta função retorna 8,005974%: 
=TAXA(9;0;-1000;2000;0) 
Devido o prazo ser expresso em meses, taxa é efetiva ao mês. 
 
EXEMPLO 3 
Depositando $100.000 e ganhando 10% ao ano, quanto tempo levará para tornar-me um 
milionário? 
Função exigida: NPER(taxa; pgto; vp;vf; tipo) 
Esta função retorna 24,15886: 
=NPER(10%;0;-100000;1000000;0) 
Este exemplo é da perspectiva do depositante. Portanto, o argumento vp é negativo e o 
argumento vf (o direito a receber $1 milhão) é positivo. 
Devido à taxa ser cotada em termos efetivos anuais, o resultado está em anos. 
 
EXEMPLO 4 
Se eu tenho R$125.463,45 em minha conta, o banco informou que a taxa de juros mensal foi 
de 2% e sabendo que aplicação foi de exatos 12 meses, qual foi o depósito inicial? 
Função exigida: VP(taxa;nper;pgto;vf;tipo) 
Esta fórmula retorna –R$98.927,07: 
=VP(2%;12;0;125463,45;0) 
 
3 
 
Curso Microsoft Excel Avançado 
 
Com nenhum pagamento regular, o argumento pgto é 0 e o argumento tipo é irrelevante. 
Devido aos R$125.463,45 na conta ser um direito a receber, o argumento vf leva o sinal 
positivo e o valor presente calculado é negativo. 
 
EXEMPLO 5 
Se eu deposito $200 por mês (começando hoje) numa conta ganhando 1,5% por mês, quanto 
terei após três anos? 
Função exigida: VF(taxa;nper;pgto; vp; tipo) 
Esta fórmula retorna $8.172,96: 
=VF(1,5%;36;-200;0;1) 
Neste exemplo, o prazo é cotado em anos, mas os juros e os pagamentos são mensais. Isto 
exige um cálculo preliminar. A abordagem mais direta é converter anos para meses. Uma outra 
opção é converter a taxa de juros para uma taxa efetiva anual, e daí converter os $200 para 
uma quantia equivalente por ano. Isto produzirá o mesmo resultado, mas é uma abordagem 
excessivamente complicada. 
Note que pagamentos começam “hoje” e são, portanto, antecipados, consequentemente, o 
argumento tipo é 1. Nenhum saldo presente é estabelecido, então o argumento vp é 0. 
 
EXEMPLO 6 
Tomando emprestado $2.000 por quatro anos a 5% de juros, quanto você terá de pagar de 
volta? 
Função exigida: VF(taxa; nper; pgto;vp;tipo) 
Esta fórmula retorna –$2.431,01: 
=VF(5%;4;0;2000;0) 
Aqui a questão é da perspectiva do tomador do empréstimo, e a fórmula foi modificada tal que 
o empréstimo tomado inicialmente (o argumento vp) seja positivo. Nenhum pagamento 
regular foi feito, assim o argumento pgto é 0. Com nenhum pagamento, o argumento tipo é 
irrelevante. 
 
EXEMPLO 7 
Se $1.000 capitalizou para $4.000 em nove anos, qual foi a taxa de crescimento anual média? 
Função exigida: TAXA(nper;pgto;vp;vf;tipo; estimativa) 
Esta fórmula retorna 16,652904%: 
=TAXA(9;0;-1000;4000;0) 
 
4 
 
Curso Microsoft Excel Avançado 
 
Este exemplo é da perspectiva do depositante. Portanto, o argumento vp é negativo e o 
argumento vf (um direito a receber) é positivo. Devido ao prazo ser expresso em anos, a taxa é 
a taxa efetiva por ano. Com nenhum pagamento regular, o argumento é 0 e o argumento tipo 
é irrelevante. 
Função EFETIVA 
Este artigo descreve a sintaxe da fórmula e o uso da função EFETIVA no Microsoft Excel. 
 
Descrição 
 
Retorna a taxa de juros anual efetiva, dados a taxa de juros anual nominal e o número de 
períodos compostos por ano. 
 
Sintaxe 
EFETIVA(taxa_nominal; npera) 
A sintaxe da função EFETIVA tem os seguintes argumentos: 
• Taxa_nominal Obrigatório. A taxa de juros nominal. 
• Npera Obrigatório. O número de períodos compostos por ano. 
 
 
• EFETIVA (taxa_nominal;npera) é relativo à NOMINAL(taxa_efetiva;npera) através de 
taxa_efetiva=(1+(taxa_nominal/npera))*npera -1. 
Exemplo 
Copie os dados de exemplo da tabela a seguir e cole-os na célula A1 de uma nova planilha do 
Excel. Para as fórmulas mostrarem resultados, selecione-as, pressione F2 e pressione Enter. Se 
precisar, você poderá ajustar as larguras das colunas para ver todos os dados. 
Dados Descrição 
 
0,0525 A taxa de juros nominal 
 
4 O número de períodos compostos por ano 
 
Fórmula Descrição Resultado 
=EFETIVA(A2;A3) Taxa de juros efetiva com os termos acima 0,0535427 
Usar a função SOMA para somar os números de um intervalo 
Você pode usar uma fórmula simples para somar números em um intervalo (um grupo de 
células), mas a função soma é mais fácil de usar quando você estiver trabalhando com mais de 
 
5 
 
Curso Microsoft Excel Avançado 
 
alguns números. Por exemplo, =SOMA(A2:A6) é menos provável ter erros de digitação que = 
A2 + A3 + A4 + A5 + A6. 
 
Função MAIOR 
Descrição 
Retorna o maior valor k-ésimo de um conjunto de dados. Você pode usar esta função para 
selecionar um valor de acordo com a sua posição relativa. Por exemplo, você pode usar MAIOR 
para obter o primeiro, o segundo e o terceiro resultados. 
Sintaxe 
MAIOR(matriz,k) 
A sintaxe da função MAIOR tem os seguintes argumentos: 
A matriz ou intervalo de dados cujo maior valor k-ésimo você deseja determinar. 
K é a posição (do maior) na matriz ou intervalo de célula de dados a ser fornecida. 
 
Função MENOR 
Descrição 
Retorna o menor valor k-ésimo do conjunto de dados. Use esta função para retornar valores 
com uma posição específica relativa em um conjunto de dados. 
Sintaxe 
MENOR(matriz,k)A sintaxe da função MENOR tem os seguintes argumentos: 
Uma matriz ou intervalo de dados numéricos cujo menor valor k-ésimo você deseja 
determinar. 
K é a posição (a partir do menor) na matriz ou intervalo de dados a ser fornecido. 
 
Função MÉDIA 
Este artigo descreve a sintaxe da fórmula e o uso da função MÉDIA no Microsoft Excel. 
Descrição 
Retorna a média (média aritmética) dos argumentos. Por exemplo, se o intervalo A1:A20 
contiver números, a fórmula =MÉDIA(A1:A20) retornará a média desses números. 
 
 
 
 
6 
 
Curso Microsoft Excel Avançado 
 
Sintaxe 
MÉDIA(núm1, [núm2], ...) 
 
Função SOMASE 
Use a função SOMASE para somar os valores em um intervalo que atendem aos critérios que 
você especificar. Por exemplo, suponha que em uma coluna que contém números, você deseja 
somar apenas os valores maiores que 5. É possível usar a seguinte fórmula: 
=SOMASE(B2:B25;">5") 
Sintaxe 
SOMASE(intervalo; critérios; [intervalo_soma]) 
A sintaxe da função SOMASE tem os seguintes argumentos: 
INTERVALO: O intervalo de células que se deseja calcular por critérios. As células em cada 
intervalo devem ser números ou nomes, matrizes ou referências que contêm números. 
Espaços em branco e valores de texto são ignorados. O intervalo selecionado deve conter 
datas no formato padrão do Excel (exemplos abaixo). 
CRITÉRIOS: Os critérios na forma de um número, expressão, referência de célula, texto ou 
função que define quais células serão adicionadas. Por exemplo, os critérios podem ser 
expressos como 32, ">32", B5, "32", "maçãs" ou HOJE(). 
Importante: Qualquer critério de texto ou qualquer critério que inclua símbolos lógicos ou 
matemáticos deve estar entre aspas duplas ("). Se os critérios forem numéricos, as aspas 
duplas não serão necessárias. 
INTERVALO_SOMA: As células reais a serem adicionadas, se você quiser adicionar células 
diferentes das especificadas no argumento intervalo. Se o argumento intervalo_soma for 
omitido, o Excel adicionará as células especificadas no argumento intervalo (as mesmas células 
às quais os critérios são aplicados). 
Você pode usar os caracteres curinga – o ponto de interrogação (?) e o asterisco (*) – como o 
argumento critérios. O ponto de interrogação corresponde a qualquer caractere único; o 
asterisco corresponde a qualquer sequência de caracteres. Para localizar um ponto de 
interrogação ou asterisco real, digite um til (~) antes do caractere. 
Função SE 
A função se é uma das funções mais populares no Excel e ela permite fazer comparações 
lógicas entre um valor e o que você espera. Em sua forma mais simples, a função se diz: 
SE(Algo for Verdadeiro, faça tal coisa, caso contrário, faça outra coisa) 
Para que uma instrução IF possa ter dois resultados. O primeiro resultado é se sua comparação 
for verdadeiro, a segunda se sua comparação for falso. 
 
7 
 
Curso Microsoft Excel Avançado 
 
Se você quiser saltar para trabalhar com várias instruções IF, consulte: funções se avançadas - 
trabalhar com fórmulas aninhadas e evitando armadilhas. 
Exemplos de SE simples 
 
=SE(C2="Sim";1,2) 
No exemplo acima, a célula D2 diz: SE(C2 = Sim, a fórmula retorna um 1 ou um 2) 
 
=SE(C2=1;"Sim";"Não") 
Neste exemplo, a fórmula na célula D2 diz: SE(C2 = 1, a fórmula retorna Sim e, caso contrário, 
retorna Não) 
Como você pode ver, a função se pode ser usada para avaliar valores e o texto. Ele também 
pode ser usado para avaliar erros. Você não está limitado a verificação somente se algo é igual 
a outro e retornar um único resultado, você pode também usar operadores matemáticos e 
executar cálculos adicionais dependendo de seus critérios. Você também pode aninhar várias 
funções se juntos para realizar várias comparações. 
Observação: Se você for usar texto em fórmulas, você precisa quebrar o texto entre aspas (por 
exemplo, "texto"). A única exceção que está usando verdadeiro ou falso, que o Excel 
automaticamente entende. 
Introdução 
A melhor maneira de começar a escrever uma instrução IF é pensar sobre o que você está 
tentando realizar. Comparação de que você está tentando fazer? Muitas vezes, escrever uma 
instrução IF pode ser tão simple quanto pensando a lógica de cabeça: "o que acontece se essa 
condição é atendida versus o que acontece se não estiver?" Sempre que você desejará 
certificar-se de que seu etapas siga uma progressão lógica ou então a fórmula não é o que 
https://support.office.com/pt-br/article/fun%C3%A7%C3%B5es-se-avan%C3%A7adas--trabalhar-com-f%C3%B3rmulas-aninhadas-e-evitando-armadilhas-0b22ff44-f149-44ba-aeb5-4ef99da241c8
https://support.office.com/pt-br/article/fun%C3%A7%C3%B5es-se-avan%C3%A7adas--trabalhar-com-f%C3%B3rmulas-aninhadas-e-evitando-armadilhas-0b22ff44-f149-44ba-aeb5-4ef99da241c8
https://support.office.com/pt-br/article/avaliar-erros-5907aac3-a09d-4761-97a1-d4e07726eef4
 
8 
 
Curso Microsoft Excel Avançado 
 
você acha que deveria. Isso é especialmente importante quando você cria complexas 
instruções IF (aninhadas). 
Mais exemplos de SE 
 
=SE(C2>B2;"Acima do orçamento";"Dentro do orçamento") 
No exemplo acima, a função SE em D2 está dizendo SE(C2 é maior que B2, retorne "Acima do 
orçamento", caso contrário, retorne "Dentro do orçamento") 
 
=SE(C2>B2;C2-B2;0) 
Na ilustração acima, em vez de retornar um resultado de texto, vamos para retornar um 
cálculo matemático. Para que a fórmula na E2 está dizendo se (real é maior que orçados, 
depois de subtrair o valor de orçados em relação à quantidade real, caso contrário retorna 
nada). 
 
9 
 
Curso Microsoft Excel Avançado 
 
 
=SE(E7="Sim";F5*0,0825;0) 
Neste exemplo, a fórmula em F7 está dizendo SE(E7="Sim", calcule o Valor Total em F5 * 
8,25%, caso contrário, nenhum Imposto sobre Vendas é cobrado, retorne 0) 
Práticas Recomendadas – Constantes 
No último exemplo, você pode ver "Sim", tanto a taxa de imposto sobre vendas (0.0825) 
inseridos diretamente na fórmula. Não geralmente é uma boa prática colocar constantes 
literais (valores que talvez precise alterar do tempo) diretamente em fórmulas, pois elas pode 
ser difícil de localizar e alterar no futuro. É muito melhor colocar constantes em suas próprias 
células, onde elas são check-out na caixa Abrir e facilmente encontrado e alterado. Nesse caso, 
é Okey, porque não há apenas uma função se e a taxa de imposto sobre vendas raramente 
será alterada. Mesmo se for, será fácil alterá-lo na fórmula. 
Operadores de cálculo 
Se você quiser saber mais sobre os operadores de cálculo diferentes que você pode usar em 
fórmulas, (<menor do que, > maior que, = , <> não igual, etc), consulte este artigo: operadores 
de cálculo e precedência. 
Usar SE para verificar se uma célula está em branco 
Às vezes, é preciso verificar se uma célula está em branco, geralmente porque você pode não 
querer uma fórmula exiba um resultado sem entrada. 
https://support.office.com/pt-br/article/e-preced%C3%AAncia-48be406d-4975-4d31-b2b8-7af9e0e2878a
 
10 
 
Curso Microsoft Excel Avançado 
 
 
Nesse caso, usamos SE com a função ÉCÉL.VAZIA: 
=SE(ÉCÉL.VAZIA(D2);"Em branco";"Não está em branco") 
Que diz IF(D2 is blank, then return "Blank", otherwise return "Not Blank"). Você poderia 
facilmente usar sua própria fórmula para a condição "Não em branco" também. No próximo 
exemplo estamos usando "" em vez de ÉCÉL. A "" basicamente significa "nada". 
 
=SE(D3="";"Em branco";"Não está em branco") 
Essa fórmula diz se (D3 nada, depois, retornar "Em branco", caso contrário, "não em branco"). 
Aqui está um exemplo de um método muito comum de uso "" para impedir que uma fórmula 
de cálculo se uma célula dependente estiver em branco: 
=SE(D3="";"";SuaFórmula()) 
SE(D3 é nada, retorne nada, caso contrário, calcule sua fórmula). 
Exemplo de SE aninhada 
Em casos onde uma simples função SE tem apenas dois resultados (Verdadeiro ou Falso), as 
funções se aninhadas SE podem ter de 3 a 64 resultados. 
 
https://support.office.com/pt-br/article/%C3%89c%C3%89lvazia-0f2d7971-6019-40a0-a171-f2d86913566511 
 
Curso Microsoft Excel Avançado 
 
=SE(D2=1;"SIM";SE(D2=2;"Não";"Talvez")) 
Na ilustração acima, a fórmula em E2 diz: SE(D2 é igual a 1, retornar "Sim", caso contrário, 
SE(D2 é igual a 2, retornar "Não", caso contrário, retornar "Talvez")). Observe que existem dois 
parênteses de fechamento no final da fórmula. Eles são necessários para concluir as duas 
funções SE e, se você tentar inserir a fórmula sem os dois, o Excel tentará corrigi-la para você. 
Função ÍNDICE 
Se você deseja retornar o valor de uma célula especificada ou uma matriz de células, 
consulte Forma matricial. 
 
Se você deseja retornar uma referência a células especificadas, consulte o Formulário de 
referência. 
 
 
Forma matricial 
 
Descrição 
Retorna o valor de um elemento em uma tabela ou matriz selecionado pelos índices de 
número de linha e coluna. 
Use a forma de matriz se o primeiro argumento de ÍNDICE for uma constante de matriz. 
Sintaxe 
ÍNDICE(matriz; núm_linha; [núm_coluna]) 
A sintaxe da função ÍNDICE tem os seguintes argumentos. 
Matriz Obrigatório. Um intervalo de células ou uma constante de matriz. 
Se a matriz contiver apenas uma linha ou coluna, o argumento Núm_linha ou Núm_coluna 
correspondente é opcional. 
Se a matriz tiver mais de uma linha e mais de uma coluna e apenas Núm_linha ou Núm_coluna 
for usado, ÍNDICE retornará uma matriz referente à linha ou coluna inteira da matriz. 
Núm_linha Obrigatório. Seleciona a linha na matriz a partir da qual um valor deverá ser 
retornado. Se Núm_linha for omitido, Núm_coluna é obrigatório. 
Núm_coluna Opcional. Seleciona a coluna na matriz a partir da qual um valor deverá ser 
retornado. Se Núm_coluna for omitido, Núm_linha é obrigatório. 
https://support.office.com/pt-br/article/%C3%8Dndice-fun%C3%A7%C3%A3o-%C3%8Dndice-a5dcf0dd-996d-40a4-a822-b56b061328bd#bmarray_form
https://support.office.com/pt-br/article/%C3%8Dndice-fun%C3%A7%C3%A3o-%C3%8Dndice-a5dcf0dd-996d-40a4-a822-b56b061328bd#__reference_form
https://support.office.com/pt-br/article/%C3%8Dndice-fun%C3%A7%C3%A3o-%C3%8Dndice-a5dcf0dd-996d-40a4-a822-b56b061328bd#__reference_form
 
12 
 
Curso Microsoft Excel Avançado 
 
Comentários 
Se os argumentos Núm_linha e Núm_coluna forem usados, ÍNDICE retornará o valor contido 
na célula que estiver no ponto de interseção entre Núm_linha e Núm_coluna. 
Se você definir Núm_linha ou Núm_coluna como 0 (zero), ÍNDICE retornará a matriz de valores 
referente à coluna ou linha inteira, respectivamente. Para usar valores retornados como uma 
matriz, insira a função ÍNDICE como uma fórmula de matriz em um intervalo horizontal de 
células para uma linha e em um intervalo vertical de células para uma coluna. Para inserir uma 
fórmula de matriz, pressione Ctrl+Shift+Enter. 
Observação: No Excel Web App, não é possível criar fórmulas de matriz. 
Núm_linha e Núm_coluna devem fazer referência a uma célula dentro de uma matriz, caso 
contrário, ÍNDICE retornará o valor de erro #REF!. 
 
 
Exemplos: 
 
Exemplo 1 
Esses exemplos usam a função ÍNDICE para localizar o valor em uma célula em interseção onde 
a linha e a coluna se encontram. 
Copie os dados de exemplo da tabela a seguir e cole-os na célula A1 de uma nova planilha do 
Excel. Para as fórmulas mostrarem resultados, selecione-as, pressione F2 e pressione Enter. Se 
precisar, você poderá ajustar as larguras das colunas para ver todos os dados. 
Dados Dados 
 
Maçãs Limões 
 
Bananas Peras 
 
Fórmula Descrição Resultado 
=ÍNDICE(A2:B3;2;2) O valor na interseção da segunda linha com a segunda 
coluna no intervalo A2:B3. 
Peras 
=ÍNDICE(A2:B3;2;1) O valor na interseção da segunda linha com a primeira 
coluna no intervalo A2:B3. 
Bananas 
Exemplo 2 
Esse exemplo usa a função ÍNDICE em uma fórmula matriz para localizar os valores em duas 
células especificadas em uma matriz 2x2. 
Nesse caso, escolha duas células verticalmente, como A1:A2, cole a fórmula 
=ÍNDICE({1;2;3;4};0;2) na primeira célula e pressione Ctrl+Shift+Enter. Se for inserida como 
uma fórmula de matriz, o Excel colará a fórmula automaticamente na segunda célula. 
 
13 
 
Curso Microsoft Excel Avançado 
 
Fórmula Descrição Resultado 
=ÍNDICE({1;2;3;4};0;2) Valor encontrado na primeira linha, segunda coluna da matriz. A 
matriz contém 1 e 2 na primeira linha e 3 e 4 na segunda linha. 
2 
 
Valor encontrado na segunda linha, segunda coluna da matriz 
(mesma matriz do exemplo acima). 
4 
Observação: Esta é uma fórmula de matriz e deve ser inserida por meio do comando Ctrl+Shift+Enter. O Excel 
envolverá automaticamente a fórmula entre chaves {}. Se você tentar inseri-las por conta própria, o Excel exibirá a 
fórmula como texto. Se não usar o comando Ctrl+Shift+Enter, a fórmula retornará o erro #VALOR!. 
Forma de referência 
 
Descrição 
 
Retorna a referência da célula na interseção de linha e coluna específicas. Se a referência for 
formada por seleções não adjacentes, você pode escolher a seleção que deseja observar. 
 
Sintaxe 
ÍNDICE (referência, núm_linha, [núm_coluna], [núm_área]) 
A sintaxe da função ÍNDICE tem os seguintes argumentos. 
Referência Obrigatório. Uma referência a um ou mais intervalos de célula. 
Se você estiver inserindo um intervalo não adjacente para a ref, coloque ref entre parênteses. 
Se cada área na referência contiver apenas uma linha ou coluna, o argumento Núm_linha ou 
Núm_coluna, respectivamente, será opcional. Por exemplo, para uma referência de linha 
única, use ÍNDICE(ref;;núm_coluna). 
Núm_linha Obrigatório. É o número da linha em ref de onde será fornecida uma referência. 
Núm_coluna Opcional. É o número da coluna em ref de onde será fornecida uma referência. 
Núm_área opcional. Seleciona um intervalo na referência a partir do qual deve ser retornada 
a interseção de núm_linha com núm_coluna. A primeira área selecionada ou inserida recebe o 
número 1, a segunda recebe o número 2 e assim por diante. Se Núm_área for omitido, ÍNDICE 
usará a área 1. Todas as áreas listadas aqui devem estar localizadas em uma única planilha. Se 
você especificar áreas que não estejam na mesma planilha que as outras, isso poderá causar 
um erro #VALOR!. Se você precisar usar intervalos que estão localizados em planilhas 
diferentes, é recomendável usar a forma matricial da função ÍNDICE e usar outra função para 
calcular o intervalo que compõe a matriz. Por exemplo, você poderia usar a função ESCOLHER 
para calcular o intervalo a ser usado. 
Por exemplo, se Ref descrever as células (A1:B4;D1:E4;G1:H4), Núm_área 1 representará o 
intervalo A1:B4, Núm_área 2 representará o intervalo D1:E4 e Núm_área 3 representará o 
intervalo G1:H4. 
 
14 
 
Curso Microsoft Excel Avançado 
 
Comentários 
Depois que Ref e Núm_área tiverem selecionado um intervalo específico, Núm_linha e 
Núm_coluna selecionam uma célula específica: Núm_linha 1 é a primeira linha do intervalo, 
Núm_coluna 1 é a primeira coluna, e assim por diante. A referência que ÍNDICE retorna é a 
interseção entre Núm_linha e Núm_coluna. 
Se você definiu Núm_linha ou Núm_coluna como 0, ÍNDICE retorna a referência para a coluna 
ou linha inteira, respectivamente. 
Núm_linha, Núm_coluna e Núm_área devem apontar para uma célula na referência; senão, 
ÍNDICE retornará o valor de erro #REF!. Se Núm_linha e Núm_coluna forem omitidos, ÍNDICE 
retornará a área em referência especificada por Núm_área. 
O resultado da função ÍNDICE é uma referência e é interpretado como tal por outras fórmulas. 
Dependendo da fórmula, o valor retornado por ÍNDICE pode ser usado como uma referência 
ou como um valor. Por exemplo, a fórmula de macro CÉL("largura";ÍNDICE(A1:B2;1;2)) é 
equivalente a CÉL("largura";B1). A função CÉL usa o valor retornado por ÍNDICE como uma 
referência de célula. Por outro lado, uma fórmula tal como 2*ÍNDICE(A1:B2;1;2) traduz o valor 
retornado por ÍNDICE no número da célula B1. 
Observação: A função CÉL não está disponível no Excel Web App. 
Exemplos 
Copie os dados de exemplo da tabela a seguire cole-os na célula A1 de uma nova planilha do 
Excel. Para as fórmulas mostrarem resultados, selecione-as, pressione F2 e pressione Enter. Se 
precisar, você poderá ajustar as larguras das colunas para ver todos os dados. 
Fruta Andrade Contagem 
Maçãs $0,69 40 
Bananas $0,34 38 
Limões $0,55 15 
Laranjas $0,25 25 
Peras $0,59 40 
Amêndoas $2,80 10 
Cajus $3,55 16 
Amendoins $1,25 20 
Nozes $1,75 12 
Fórmula Descrição Resultado 
=ÍNDICE(A2:C6;2;3) A interseção da segunda linha com a terceira coluna no 
intervalo A2:C6, que é o conteúdo da célula C3. 
38 
=ÍNDICE((A1:C6;A8:C11);2;2;2) A interseção da segunda linha com a segunda coluna na 
segunda área de A8:C11, que é o conteúdo da célula B9. 
1,25 
=SOMA(ÍNDICE(A1:C11;0;3;1)) A soma da terceira coluna na primeira área do intervalo 
A1:C11, que é a soma de C1:C11. 
216 
 
15 
 
Curso Microsoft Excel Avançado 
 
Fruta Andrade Contagem 
=SOMA(B2:ÍNDICE(A2:C6;5;2)) A soma do intervalo que começa em B2 e termina na 
interseção da quinta linha com a segunda coluna no 
intervalo A2:A6, que é a soma de B2:B6. 
2,42 
 
Função CORRESP 
Aplicável a: Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel 2016 para Mac Mais... 
A função CORRESP procura um item especificado em um intervalo de células e retorna a 
posição relativa desse item no intervalo. Por exemplo, se o intervalo A1:A3 contiver os valores 
5, 25 e 38, a fórmula =CORRESP(25,A1:A3,0) retornará o número 2, porque 25 é o segundo 
item no intervalo. 
Sintaxe 
CORRESP(valor_procurado, matriz_procurada, [tipo_correspondência]) 
A sintaxe da função CORRESP tem os seguintes argumentos: 
valor_procurado Obrigatório. O valor que você deseja corresponder em matriz_procurada. 
Por exemplo, quando você procura o número de alguém na lista telefônica, está usando o 
nome da pessoa como valor de pesquisa, mas o número do telefone é o valor desejado. 
 
O argumento valor_procurado pode ser um valor (número, texto ou valor lógico) ou uma 
referência de célula para um número, texto ou valor lógico. 
matriz_procurada Obrigatório. O intervalo de células que estão sendo pesquisadas. 
tipo_correspondência Opcional. O número -1, 0 ou 1. O 
argumento tipo_correspondência especifica como o Excel faz a correspondência 
de valor_procurado a valores na matriz_procurada. O valor padrão para este argumento é 1. 
 
A tabela a seguir descreve como a função localiza valores com base na configuração do 
argumento tipo_correspondência. 
Tipo_correspondência Comportamento 
1 ou não especificado CORRESP localiza o maior valor que é menor do que ou igual a valor_procurado. Os 
valores no argumento matriz_procurada deve ser colocados em ordem crescente; por 
exemplo: ...-2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADEIRO. 
0 CORRESP localiza o primeiro valor que é exatamente igual a valor_procurado. Os valores 
no argumento matriz_procurada podem estar em qualquer ordem. 
-1 CORRESP localize o menor valor que é maior do que ou igual a valor_procurado. Os 
valores no argumento matriz_procurada devem ser colocados em ordem decrescente, 
por exemplo: VERDADEIRO, FALSO, Z-A, ...2, 1, 0, -1, -2, ... e assim por diante. 
javascript:
 
16 
 
Curso Microsoft Excel Avançado 
 
CORRESP retorna a posição do valor correspondido em matriz_procurada e não o valor 
propriamente dito. Por exemplo, CORRESP("b",{"a","b","c"},0) retorna 2, que é a posição 
relativa de "b" na matriz {"a","b","c"}. 
CORRESP não faz distinção entre letras maiúsculas e minúsculas ao fazer a correspondência 
entre valores de texto. 
Se CORRESP não conseguir localizar um valor coincidente, ele retornará o valor de erro #N/D. 
Se tipo_correspondência for 0 e valor_procurado for uma cadeia de texto, você poderá usar 
os caracteres curinga – o ponto de interrogação (?) e o asterisco (*) – no 
argumento valor_procurado. Um ponto de interrogação corresponde a qualquer caractere 
simples, enquanto um asterisco corresponde a qualquer cadeia de caracteres. Se você quiser 
localizar um ponto de interrogação ou asterisco real, digite um til (~) antes do caractere. 
Exemplo 
Copie os dados de exemplo da tabela a seguir e cole-os na célula A1 de uma nova planilha do 
Excel. Para as fórmulas mostrarem resultados, selecione-as, pressione F2 e pressione Enter. Se 
precisar, você poderá ajustar as larguras das colunas para ver todos os dados. 
Produto Contagem 
 
Bananas 25 
 
Laranjas 38 
 
 Maçãs 40 
 
Peras 41 
 
Fórmula Descrição Resultado 
=CORRESP(39;B2:B5;1) Como não existe uma correspondência exata, é retornada a posição do 
próximo valor mais baixo (38) no intervalo B2:B5. 
2 
=CORRESP(41;B2:B5;0) A posição do valor 41 no intervalo B2:B5. 4 
=CORRESP(40;B2:B5;-
1) 
Retorna um erro porque os valores no intervalo B2:B5 não estão em 
ordem decrescente. 
#N/D 
 
 
 
17 
 
Curso Microsoft Excel Avançado 
 
CRIANDO UMA LISTA SUSPENSA 
1. Em uma nova planilha, digite as entradas que você quer incluir na sua lista suspensa. Essas 
entradas devem estar em uma única coluna ou linha sem células em branco. Por exemplo: 
 
Dica: Agora é um ótimo momento para Classificar os dados na ordem em que você deseja que 
eles apareçam em sua lista suspensa. 
2. Selecione todas as suas entradas, clique nelas com o botão direito do mouse e depois clique 
em Definir Nome. 
3. Na caixa Nome, digite um nome para as suas entradas (por exemplo, ValidDepts) e clique 
em OK. Verifique se esse nome não contém espaços. Ele não aparece na lista, mas você precisa 
defini-lo para poder criar um vínculo com a lista suspensa. 
 
4. Clique na célula da planilha em que você quer colocar a lista suspensa. 
5. Clique em Dados >Validação de Dados. 
https://support.office.com/pt-br/article/classificar-os-dados-na-ordem-em-que-voc%C3%AA-deseja-que-eles-apare%C3%A7am-f92f26c6-470d-4b09-a0e9-e5c3b60a8a7a#__toc246836970
https://support.office.com/pt-br/article/classificar-os-dados-na-ordem-em-que-voc%C3%AA-deseja-que-eles-apare%C3%A7am-f92f26c6-470d-4b09-a0e9-e5c3b60a8a7a#__toc246836970
 
18 
 
Curso Microsoft Excel Avançado 
 
 
Dicas: Não consegue clicar em Validação de Dados? Veja a seguir alguns dos possíveis 
motivos. 
o Listas suspensas não podem ser adicionadas a tabelas que estejam vinculadas a um site do 
SharePoint. Desvincule a tabela ou remova a formatação de tabela e repita a etapa 5. 
o A planilha pode estar sendo compartilhada ou protegida. Remova a proteção ou interrompa o 
compartilhamento da planilha e repita a etapa 5. 
 
6. Na guia Configurações, na caixa Permitir, clique em Lista. 
7. Na caixa Origem, digite o sinal de igual (=) seguido imediatamente do nome que você atribuiu 
à sua lista na etapa 3. Por exemplo, =ValidDepts.. 
 
8. Marque a caixa Menu suspenso na célula. 
9. Se não for um problema deixar a célula vazia, marque a caixa Ignorar em branco. 
10. Clique na guia Mensagem de Entrada. 
11. Se quiser que uma mensagem seja exibida quando a célula for clicada, marque a caixa de 
seleção Mostrar mensagem de entrada ao selecionar célula e digite um título e uma 
mensagem nas caixas (até 225 caracteres). Se não quiser que uma mensagem apareça, 
desmarque essa caixa de seleção. 
https://support.office.com/pt-br/article/remova-a-prote%C3%A7%C3%A3o-54fedd95-28dc-4abc-a26c-6566917c1a59
 
19 
 
Curso Microsoft Excel Avançado 
 
 
12. Clique na guia Alerta de Erro. 
13. Marque a caixa Mostrar alerta de erro após a inserção de dados inválidos, escolha uma opção 
na caixa Estilo e digite um título e uma mensagem. Se não quiser que uma mensagem apareça, 
desmarque essa caixa de seleção. 
 
 
 
 
20 
 
Curso Microsoft Excel Avançado 
 
ADICIONAR OU REMOVER ITENS DE UMA LISTA SUSPENSA 
Após você criar uma lista suspensa, convém adicionar mais itens ou excluir itens. Neste artigo, 
mostraremos como fazer isso, dependendo de como a lista foi criada. 
Editar uma lista suspensa baseada em uma tabela do Excel 
Se você configurar sua fonte de lista como uma tabela do Excel, então tudo o que você 
precisaráfazer será adicionar ou remover itens da lista, e o Excel atualizará automaticamente 
qualquer lista suspensa associada para você. 
 
• Para adicionar um item, vá até o final da lista e digite o novo item. 
• Para remover um item, pressione Excluir. 
Editar uma lista suspensa baseada em uma tabela do Excel 
Se você configurar sua fonte de lista como uma tabela do Excel, então tudo o que você 
precisará fazer será adicionar ou remover itens da lista, e o Excel atualizará automaticamente 
qualquer lista suspensa associada para você. 
https://support.office.com/pt-br/article/criar-uma-lista-suspensa-7693307a-59ef-400a-b769-c5402dce407b
https://support.office.com/pt-br/article/tabela-do-excel-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c
https://support.office.com/pt-br/article/tabela-do-excel-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c
 
21 
 
Curso Microsoft Excel Avançado 
 
 
• Para adicionar um item, vá até o final da lista e digite o novo item. 
• Para remover um item, pressione Excluir. 
Dica: Se o item que você deseja excluir estiver em algum lugar no meio de sua lista, clique com 
o botão direito do mouse na célula e depois em Excluir. Em seguida, clique em OK para 
deslocar as células para cima. 
 
Editar uma lista suspensa baseada em um intervalo nomeado 
 
1. Selecione a planilha com o intervalo nomeado para sua lista suspensa. 
2. Siga um destes procedimentos: 
o Para adicionar um item, vá até o final da lista e digite o novo item. 
o Para remover um item, pressione Excluir. 
Dica: Se o item que você deseja excluir estiver em algum lugar no meio de sua lista, clique com 
o botão direito do mouse na célula e depois em Excluir. Em seguida, clique em OKpara 
deslocar as células para cima. 
3. Vá a Fórmulas > Gerenciador de Nomes. 
4. Na caixa Gerenciador de Nomes, clique no intervalo nomeado que você quer atualizar. 
javascript:
javascript:
 
22 
 
Curso Microsoft Excel Avançado 
 
 
5. Clique na caixa Refere-se a e, em seguida, na sua planilha, selecione todas as células que 
contenham as entradas para sua lista suspensa. 
6. Clique em Fechar e depois em Sim para salvar suas alterações. 
Dica: Se você não souber o nome de um intervalo nomeado, pode selecionar o intervalo e 
procurar seu nome na Caixa de nome. Para localizar um intervalo nomeado, confira Localizar 
intervalos nomeados. 
 
 
 
 
EDITAR UMA LISTA SUSPENSA BASEADA EM UM INTERVALO NOMEADO 
 
1. Selecione a planilha com o intervalo nomeado para sua lista suspensa. 
2. Siga um destes procedimentos: 
o Para adicionar um item, vá até o final da lista e digite o novo item. 
o Para remover um item, pressione Excluir. 
https://support.office.com/pt-br/article/selecione-todas-as-c%C3%A9lulas-23f64223-2b6b-453a-8688-248355f10fa9
https://support.office.com/pt-br/article/localizar-intervalos-nomeados-14513df5-0d79-4cd5-9a03-968905b244e6
https://support.office.com/pt-br/article/localizar-intervalos-nomeados-14513df5-0d79-4cd5-9a03-968905b244e6
 
23 
 
Curso Microsoft Excel Avançado 
 
Dica: Se o item que você deseja excluir estiver em algum lugar no meio de sua lista, clique com 
o botão direito do mouse na célula e depois em Excluir. Em seguida, clique em OKpara 
deslocar as células para cima. 
3. Vá a Fórmulas > Gerenciador de Nomes. 
4. Na caixa Gerenciador de Nomes, clique no intervalo nomeado que você quer atualizar. 
 
5. Clique na caixa Refere-se a e, em seguida, na sua planilha, selecione todas as células que 
contenham as entradas para sua lista suspensa. 
6. Clique em Fechar e depois em Sim para salvar suas alterações. 
Dica: Se você não souber o nome de um intervalo nomeado, pode selecionar o intervalo e 
procurar seu nome na Caixa de nome. Para localizar um intervalo nomeado, confira Localizar 
intervalos nomeados. 
 
 
 
 
 
https://support.office.com/pt-br/article/selecione-todas-as-c%C3%A9lulas-23f64223-2b6b-453a-8688-248355f10fa9
https://support.office.com/pt-br/article/localizar-intervalos-nomeados-14513df5-0d79-4cd5-9a03-968905b244e6
https://support.office.com/pt-br/article/localizar-intervalos-nomeados-14513df5-0d79-4cd5-9a03-968905b244e6
 
24 
 
Curso Microsoft Excel Avançado 
 
EDITAR UMA LISTA SUSPENSA BASEADA EM UM INTERVALO DE CÉLULAS 
1. Selecione a planilha com os dados para sua lista suspensa. 
2. Siga um destes procedimentos: 
o Para adicionar um item, vá até o final da lista e digite o novo item. 
o Para remover um item, clique em Excluir. 
Dica: Se o item que você deseja excluir estiver em algum lugar no meio de sua lista, clique com 
o botão direito do mouse na célula e depois em Excluir. Em seguida, clique em OKpara 
deslocar as células para cima. 
3. Na planilha em que você aplicou a lista suspensa, selecione uma célula que tenha a lista 
suspensa. 
4. Vá para Dados >Validação de Dados. 
5. Na guia Configurações, clique na caixa Origem e, na planilha que contém as entradas para sua 
lista suspensa, selecione todas as células com essas entradas. Você verá o intervalo de lista 
mudar na caixa Origem, conforme o selecionar. 
 
6. Para atualizar todas as células que têm a mesma lista suspensa aplicada, marque a 
caixa Aplicar alterações a todas as células com as mesmas configurações. 
 
Calcular Taxa de Juro Real 
Juro Nominal = A taxa de juros nominais é aquela divulgada pelas instituições financeiras 
quando você faz um investimento ou assume uma dívida sem considerar os efeitos da inflação. 
Juro Real = A taxa de juros reais são os juros nominais já com a inflação descontada. Exemplo: 
O banco divulgou que a rentabilidade de um determinado investimento foi de 10% de juros no 
ano. Este são os juros nominais. 
https://support.office.com/pt-br/article/selecione-todas-as-c%C3%A9lulas-6dc8a03a-fb80-4454-922e-965eb48a6d25
 
25 
 
Curso Microsoft Excel Avançado 
 
A inflação no mesmo período foi de 6%. 
Com estas duas informações (juro nominal e inflação) podemos calcular os juros reais, que são 
os juros recebidos acima da inflação. 
A fórmula para fazer este cálculo é: 
Juros Reais = (1 + Juros Nominais) / (1 + Inflação) – 1 
 
Exemplo de cálculo: 
Juros Nominais: 10% = 0,10 
Inflação: 6% = 0,06 
Primeiro dividimos 10% e 6% por 100 para encontrar 0,10 e 0,06 e depois substituir na 
fórmula: 
Juros Reais = (1 + 0,10) / (1 + 0,06) – 1 
Juros Reais = (1,10 / 1,06) – 1 
Juros Reais = 1,03773 – 1 
Juros Reais = 0,03773 
Agora multiplicamos 0,03773 por 100 para encontrar a taxa: 
Juros Reais = 3,773% 
 
Exercício: 
Um trabalhador teve seu salário reajustado em 6% ao ano nos últimos cinco anos, sabendo 
que a inflação acumulada dos últimos cinco anos foi de 23%, qual a taxa de juros real da 
valorização do salário deste trabalhador nos últimos cinco anos? 
 
Função Arrumar 
Aprenda a apagar todos os espaçamentos duplos que ficam entre as palavras nas linhas de 
texto do Excel. 
Com a função =Arrumar() será possível apagar todos os espaçamentos duplos que ficam entre 
as palavras. Este tipo de erro é bastante comum se você copia e cola muitas informações de 
sites, arquivos txt, dados copiados do sistema, etc. 
 
 
 
 
26 
 
Curso Microsoft Excel Avançado 
 
Vejamos abaixo um exemplo de dados de texto “errados”: 
 
Agora pegue uma célula vazia e digite a função =Arrumar(célula na qual se encontra o texto). 
Veja como ficou a função e o resultado no nosso exemplo: 
 
Agora você deve estar se perguntando: “Como esta função irá me poupar trabalho, já que terei 
de digitá-la linha por linha?” E eu respondo: Simples, você não precisa digitar linha por linha, 
faça o seguinte: selecione a célula onde você inseriu a função e coloque o mouse no canto 
inferior direito. Assim que aparecer este símbolo “ + ” clique e arraste até a célula onde você 
deseja arrumar. 
 
 
 
https://www.aprenderexcel.com.br/texto
 
27 
 
Curso Microsoft Excel Avançado 
 
Pronto, rápido e fácil. Confira o resultado: 
 
 
Função Pri.maiúscula 
 
Saiba mais sobre a função PRI.MAIÚSCULA e como utilizá-la, tornandoo ato de formatação 
mais prático. 
Já adicionou informações de certo modo e depois teve de deixá-las padronizadas? A 
modificação, nestes casos, é efetuada geralmente manualmente, muitas vezes devido ao não 
conhecimento de algumas funções que podem automatizar esta tarefa. Veremos a função 
PRI.MAIÚSCULA, a qual é responsável pela formatação do texto das células, deixando-o com a 
primeira letra maiúscula e o restante minúsculo. 
Copiar dados de diferentes formatações em tabelas pode se tornar um grande empecilho caso 
tenhas que realizar uma posterior edição destes, ainda mais se dispõem de uma 
grande quantidade. Como forma de facilitar esta tarefa, o Excel traz a função PRI.MAIÚSCULA, 
que é capaz de efetuar a formatação automática de qualquer célula de texto, colocando a 
primeira letra em maiúsculo e as demais em minúsculo. As informações dispostas nas células 
de texto, para poderem sofrer as modificações, podem estar escritas de qualquer forma, isto é, 
estar todas em letras maiúsculas, parte minúscula e parte maiúscula ou todas minúsculas. Em 
sua estrutura, a função é apresentada da seguinte maneira:=PRI.MAIÚSCULA(texto), onde 
somente deve ser selecionado a célula contendo o texto para efetuar a sua aplicação. 
Para testarmos a função, crie um documento contendo algumas palavras ou expressões, 
escritas de qualquer forma, e reserve uma coluna ou lado destas para que sejam dados os 
resultados com a nova formatação. 
https://www.aprenderexcel.com.br/texto
 
28 
 
Curso Microsoft Excel Avançado 
 
 
Na primeira célula destinada aos resultados, aplique a função =PRI.MAIÚSCULA(B3), referindo 
à célula B3 como sendo o alvo da formatação. 
 
Observe o resultado e expanda a função às demais células. Veja, então, que as novas 
formatações serão dadas. 
 
 
29 
 
Curso Microsoft Excel Avançado 
 
 
Com a utilização desta função, mesmo sendo considerada tão simples, poderá ser de imensa 
usabilidade em casos que se dispõe de uma quantidade muito extensa de dados, trazendo ao 
usuário mais praticidade e uma menor perca de tempo na execução desta tarefa. Bom 
trabalho! 
Formatação Condicional – Linha Cor sim cor não 
 
Como fazer para que as linhas fiquem coloridas no formato cor sim cor não, ou seja 
intercalando duas cores, uma linha cinza e outra branca. 
 
Fazer isso com 20 linhas é tranquilo, mas em uma planilha com 1000 linhas já dá um pouco 
mais de trabalho, então, dei uma procurada na internet e achei a seguinte solução. 
 
Selecione os dados e clique em Formatação Condicional – Nova Regra: 
 
 
 
Escolha a opção usar uma fórmula para determinar quais células devem ser formatadas, e 
colocar a função: =MOD(LIN();2)=0 
https://tecnologiaegestao.files.wordpress.com/2011/02/111.png
 
30 
 
Curso Microsoft Excel Avançado 
 
 
 
 
Clique em formatar, e faça a formatação desejada, que nesse caso, é o preenchimento da 
linha: 
 
 
 
Clique em Ok e pronto, sua formatação cor sim cor não está pronta: 
https://tecnologiaegestao.files.wordpress.com/2011/02/112.png
https://tecnologiaegestao.files.wordpress.com/2011/02/113.png
 
31 
 
Curso Microsoft Excel Avançado 
 
 
 
 
 
 
 
https://tecnologiaegestao.files.wordpress.com/2011/02/114.png