Excel Dashboards - Instituto do Excel

Prévia do material em texto

Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
2 
 
 
 
Sumário 
 
1. O que são dashboards?..................................................................... 03 
2. Funções matemáticas e estatísticas.................................................... 04 
2.1. Funções estatísticas condicionais....................................................... 04 
3. Funções condicionais......................................................................... 05 
3.1. Valores lógicos e testes lógicos........................................................... 05 
3.2. Função condicional SE....................................................................... 06 
3.3. Função auxiliar E............................................................................... 07 
3.4. Função auxiliar OU............................................................................ 07 
3.5. Função auxiliar XOR.......................................................................... 08 
3.6. Função auxiliar NÃO.......................................................................... 08 
3.7. Função SE aninhada.......................................................................... 08 
3.8. Funções de informação...................................................................... 10 
3.9. Função SEERRO................................................................................ 11 
3.10 Função SENÃODISP.......................................................................... 12 
3.11 A tabela da verdade.......................................................................... 13 
3.12 Precedência dos operadores.............................................................. 13 
4. Funções de procura e referência......................................................... 14 
4.1. Função PROCV.................................................................................. 14 
4.2. Função PROCH.................................................................................. 14 
4.3. Função PROC.................................................................................... 15 
4.4. Função CORRESP.............................................................................. 15 
4.5. Função ÍNDICE................................................................................. 16 
4.6. Funções ÍNDICE e CORRESP usadas em conjunto................................ 17 
4.7. Função ESCOLHER............................................................................ 20 
4.8. Função DESLOC................................................................................ 21 
4.8.1 Usando DESLOC para obter um intervalo deslocado........................... 22 
4.8.2. Usando DESLOC para recuperar uma célula numa matriz.................... 25 
4.8.3. Usando DESLOC para criar intervalos dinâmicos................................. 26 
4.9. Função ENDEREÇO............................................................................ 30 
4.10 Função INDIRETO............................................................................ 31 
4.11. Funções LIN, COL, LINS, COLS.......................................................... 33 
5. Funções de texto............................................................................... 33 
5.1. Concatenação de texto...................................................................... 34 
5.2. Maiúsculas e minúsculas.................................................................... 34 
5.3. Operações com caracteres................................................................. 35 
5.4. Formatação de números.................................................................... 36 
5.5. Extração de texto.............................................................................. 37 
5.6. Localização e substituição de caracteres............................................. 38 
5.7. Outras funções.................................................................................. 38 
5.8. Pontos principais............................................................................... 41 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
3 
 
 
 
1. O que são dashboards? 
 
O Excel é uma ferramenta excepcional, capaz de efetuar cálculos complexos e tornar 
uma grande quantidade de números em informações úteis. Quando você analisa esses 
números e é capaz de tomar ações em função deles, você está colocando em prática a 
Inteligência dos Negócios (Business Intelligence ou simplesmente BI). A BI auxilia os 
gestores a identificar novas oportunidades de negócios, localizar áreas onde podem 
cortar custos, reconhecer constantes mudanças no mercado, identificar anomalias nos 
dados e criar uma vasta gama de relatórios gerenciais. 
 
Há anos o conceito de BI vem tomando conta das empresas, onde os gestores precisam 
rapidamente tornar a grande quantidade de dados em conhecimento útil, o qual 
resultará em ações para benefício da corporação. Como resultado dessa tendência, 
surgiu a necessidade da elaboração de mecanismos que informem de maneira imediata 
os pontos mais críticos para que ações possam ser tomadas, caso necessário. A forma 
mais prática de apresentar tais mecanismos é a montagem de um Painel de Indicadores, 
ou simplesmente dashboard. 
 
Da mesma maneira que o painel de um automóvel exibe diversos indicadores 
(combustível, óleo, quilometragem rodada, etc.), um painel criado no computador 
apresenta dados críticos numa variedade de formatos. Um dashboard proporciona uma 
visualização instantânea das principais métricas relevantes a um objetivo específico ou a 
um processo de negócios. 
 
Há programas de computador dedicados à criação de tais painéis, mas o Excel se mostra 
vantajoso por causa de sua capacidade de cálculo, manipulação de bases de dados, 
criação de gráficos, automação e muito mais. Sem contar que está presente em milhões 
de computadores em todo o mundo e é o programa padrão para edição de planilhas 
eletrônicas em quase todas as corporações. 
 
Mesmo que a empresa possua um poderoso sistema integrado de gestão (Enterprise 
Resource Planning – ERP) que geralmente custa centenas de milhares de reais, muitos 
relatórios gerenciais acabam sendo feitos ou finalizados em planilhas. 
 
O Excel contém tantas funções e recursos que é difícil saber por onde começar. O 
objetivo deste material é tornar o Excel a sua ferramenta de BI pessoal, transformando 
simples tabelas e bancos de dados em painéis de indicadores que fazem mais sentido a 
qualquer gestor. 
 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
4 
 
 
 
2. Funções matemáticas e estatísticas 
 
As funções matemáticas e estatísticas mais comuns no Excel e que trabalham 
com intervalos de células são: 
 
 SOMA, MÉDIA, MÁXIMO, MÍNIMO para estatísticas básicas; 
 
 CONT.VALORES, CONT.NÚM, CONTAR.VAZIO para realizar contagens 
diversas. 
 
O foco neste tópico são as funções matemáticas e estatísticas que trabalham 
com condições (critérios), as quais são particularmente úteis na montagem de 
dashboards. Essas funções podem calcular uma única informação, ou fazer 
parte de uma tabela com fórmulas mais complexas, na qual nem sempre uma 
tabela dinâmica é a melhor alternativa. 
 
Na apresentação da sintaxe de uma função, quando aparecer algum 
argumento entre colchetes, isso indica que ele é opcional e pode ser omitido. 
Se, no final da função, a sintaxe mostrar reticências (...), isso indica que a 
função poderá continuar com mais argumentos. 
 
2.1. Funções estatísticas condicionais 
 
As funções que realizam somas, médias e contagens condicionais são 
resumidas a seguir: 
 
 Função SOMASE: Adiciona as células especificadas por um determinado 
critério dentro de um intervalo: Função MÉDIASE: Retorna a média aritmética de todas as células de um 
intervalo que atendem a um determinado critério: 
 
 Função CONT.SE: Calcula o número de células não vazias em um 
intervalo que corresponde a determinado critério: 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
5 
 
 
 
 Função SOMASES: Adiciona as células em um intervalo, as quais 
atendem a vários critérios: 
 
 Função MÉDIASES: Retorna a média aritmética de todas as células que 
atendem a vários critérios: 
 
 Função CONT.SES: Conta o número de células não vazias dentro de um 
intervalo que atende a múltiplos critérios: 
 
 
3. Funções condicionais 
 
As funções condicionais estão presentes no dia a dia de quem trabalha com o 
Excel, desde o nível básico. A seguir você verá um resumo de como realizar 
testes lógicos e a sintaxe para as principais funções condicionais, além de 
algumas outras que podem ser úteis na elaboração de painéis de indicadores. 
 
 
3.1. Valores lógicos e testes lógicos 
 
Uma célula do Excel é capaz de conter um único dado. Esta pode ser um 
número, um texto ou uma fórmula. Dentre as possibilidades numéricas, estão 
os valores lógicos VERDADEIRO e FALSO. Quando digitados, estes valores 
aparecem em maiúsculas e centralizados nas células. 
 
Valores lógicos são usados principalmente nas seguintes circunstâncias: 
 
 Função condicional SE: Para decidir se será retornado um resultado 
quando o valor lógico for VERDADEIRO, ou se será retornado outro 
resultado quando o valor lógico for FALSO; 
 
 Formatação condicional baseada em fórmula: Um intervalo de células será 
formatado quando o resultado da fórmula for VERDADEIRO, caso 
contrário, o intervalo não será formatado; 
 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
6 
 
 
 
 Validação de dados personalizada, baseada em fórmula: Durante a 
digitação de dados, uma mensagem de erro aparecerá para o usuário 
quando o resultado da fórmula for FALSO. Caso contrário, nenhuma 
mensagem de erro será exibida. 
 
Os testes condicionais (ou lógicos) são necessários para a tomada de decisões 
nas aplicações mencionadas acima. Um teste sempre compara dois valores, os 
quais podem ser números, textos, endereços de células ou fórmulas. O 
resultado de um teste lógico é sempre um valor lógico VERDADEIRO ou 
FALSO. Você pode usar os seguintes sinais para fazer os testes lógicos: = 
(igual a), > (maior que, < (menor que), >= (maior ou igual a), <= (menor ou 
igual a), <> (diferente de). 
 
Exemplos de testes lógicos: 
 
 B5 > 0; 
 
 SOMA(C10:C18) >= B5 * B6; 
 
 NomeCliente <> "". 
 
Há situações em que serão necessários dois ou mais testes lógicos. Neste 
caso, você deve usar duas funções auxiliares: E e OU. Estas funções 
conseguem avaliar simultaneamente até 255 testes lógicos. 
 
 
3.2. Função condicional SE 
 
A função SE retornará um valor se o resultado do teste lógico for avaliado 
como VERDADEIRO e outro valor se o resultado do teste lógico for avaliado 
como FALSO. 
 
A sintaxe é a seguinte: 
 
=SE(teste_lógico; valor_se_verdadeiro; valor_se_falso) 
 
Veja um exemplo: 
 
=SE(B5>B4; 5%*B5; 0) 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
7 
 
 
 
A função SE também pode ser usada para escolher intervalos: 
 
=SOMA(SE(C5>0; D:D; E:E)) 
 
 
3.3. Função auxiliar E 
 
Essa função retornará VERDADEIRO se todos os seus argumentos forem 
avaliados como VERDADEIRO e retornará FALSO se um ou mais argumentos 
forem avaliados como FALSO. 
 
Sua sintaxe é a seguinte: 
 
=E(teste_lógico1; teste_lógico2; ...) 
 
Veja um exemplo: 
 
=SE(E(C5>0; D5>0); C5/D5; "") 
 
 
3.4. Função auxiliar OU 
 
Esta função retornará VERDADEIRO se ao menos um de seus argumentos for 
avaliado como VERDADEIRO e retornará FALSO se todos os argumentos forem 
avaliados como FALSO. 
 
Sua sintaxe é a seguinte: 
 
=OU(teste_lógico1; teste_lógico2; ...) 
 
Veja um exemplo: 
 
=SE(OU(C5>0; D5>0); C5/D5; "") 
 
 
 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
8 
 
 
 
3.5. Função auxiliar XOR 
 
Essa função retornará VERDADEIRO se a quantidade de argumentos avaliados 
como VERDADEIRO for ímpar, senão, retornará FALSO. 
 
Sua sintaxe é a seguinte: 
 
=XOR(teste_lógico1; teste_lógico2; ...) 
 
Veja um exemplo: 
 
=SE(XOR(C5>0; D5>0); C5/D5; ""5) 
 
 
3.6. Função auxiliar NÃO 
 
A função NÃO inverte o resultado de um teste lógico. 
 
Veja a sua sintaxe: 
 
NÃO(teste_lógico) 
 
 
3.7. Função SE aninhada 
 
A função SE testa uma condição e traz duas respostas possíveis. Para testar 
mais condições e trazer mais respostas, você deve usar a função SE de forma 
aninhada (uma dentro da outra), para testar outras condições. Pode-se 
aninhar até 64 funções SE. 
 
Condições (lógica) Sintaxe para a função SE 
Se teste_lógico1 = VERDADEIRO; 
Então valor1; 
=SE( teste_lógico1; 
valor1; 
Senão, se teste_lógico2 = VERDADEIRO; 
Então valor2; 
SE( teste_lógico2; 
valor2; 
Senão, se teste_lógico3 = VERDADEIRO; 
Então valor3; 
SE( teste_lógico3; 
valor3; 
... 
Senão valorN 
... 
valorN ) ) ) 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
9 
 
 
 
Sintaxe completa: 
 
=SE( teste_lógico1; valor1; SE( teste_lógico2; valor2; SE( teste_ 
lógico3; valor3; ... valorN ) ) ) 
 
No exemplo a seguir, imagine que o valor das vendas está na célula B5 e que 
seja necessário escrever uma fórmula que calcule o valor do bônus em função 
do valor das vendas, de acordo com várias faixas de valores. 
 
Observe as condições escritas e compare-as com a função que será usada: 
 
Condições (lógica) Sintaxe 
Se as vendas forem inferiores a 1000; 
Então o bônus será 0; 
=SE(B5<1000; 
0; 
Senão, se as vendas forem inferiores a 2000; Então 
o bônus será de 10% das vendas; 
SE(B5<2000; 
10%*B5; 
Senão, o bônus será de 20% das vendas 20%*B5)) 
 
Ou seja, a fórmula seria: 
 
=SE(B5<1000; 0; SE(B5<2000; 10%*B5; 20%*B5)) 
 
Uma fórmula que usa testes lógicos com as funções auxiliares E e OU avalia 
todos os testes simultaneamente. Uma fórmula que usa a estrutura da função 
SE aninhada avalia um teste de cada vez. Neste caso, quando um teste for 
VERDADEIRO, o resultado é trazido e nenhum outro teste na sequência será 
avaliado. 
 
 
 
 
 
 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
10 
 
 
 
3.8. Funções de informação 
 
Algumas funções do Excel trazem como resultado um valor lógico. Essas 
funções também possuem alguma utilidade na montagem de fórmulas porque 
você pode testar o conteúdo de uma célula para saber se é de um 
determinado tipo ou não. Os nomes dessas funções iniciam com É. 
 
A seguir, uma relação das funções de informação do Excel que retornam 
VERDADEIRO ou FALSO: 
 
Função Descrição 
É.NÃO.DISP Retorna VERDADEIRO se o valor for o erro #N/D. 
É.NÃO.TEXTO Retorna VERDADEIRO se o valor for diferente de texto. 
ÉCÉL.VAZIA Retorna VERDADEIRO se o valor for vazio. 
ÉERRO 
Retorna VERDADEIRO se o valor for um valor de erro diferente 
de #N/D. 
ÉERROS Retorna VERDADEIRO se o valor for um valor de erro. 
ÉIMPAR Retorna VERDADEIRO se o número for ímpar. 
ÉLÓGICO Retorna VERDADEIRO se o valor for um valor lógico. 
ÉNÚM Retorna VERDADEIRO se o valor for um número. 
ÉPAR Retorna VERDADEIRO se o número for par. 
ÉREF Retorna VERDADEIRO se o valor for uma referência. 
ÉTEXTO Retorna VERDADEIRO se o valor for texto. 
ÉFÓRMULA Retorna VERDADEIRO se a referência contiver umafórmula. 
 
Exemplo: 
 
A célula B3 contém as vendas da Filial 1. A célula B4 contém as vendas de 
todas filiais. A célula B5 deverá ter a fórmula =B3/B4 que calcula a 
participação das vendas da Filial 1 sobre o total. 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
11 
 
 
 
Porém, a divisão somente deverá ser efetuada se ambos os valores não forem 
textos e se o denominador for um número maior que zero. Caso contrário B5 
ficará em branco. 
 
A fórmula em B5 pode ser escrita desta maneira: 
 
=SE(E(É.NÃO.TEXTO(B3); É.NÃO.TEXTO(B4); B4>0); B3/B4; "") 
 
 
3.9. Função SEERRO 
 
O objetivo da função SEERRO (introduzida no Excel 2007) é avaliar uma 
expressão ou cálculo. Se o resultado for um valor de erro, então ela retornará 
o que você usar como 2º argumento. Caso contrário, o próprio resultado da 
expressão será retornado à célula. 
 
Sua sintaxe é a seguinte: 
 
=SEERRO(expressão; valor_se_erro) 
 
Adiante, estão descritos os argumentos: 
 
 expressão: É o argumento verificado quanto ao erro (geralmente uma 
fórmula); 
 
 valor_se_erro: É o valor a ser retornado se a fórmula gerar um erro. Os 
seguintes tipos de erro são avaliados #N/D, #VALOR!, #REF!, #DIV/0!, 
#NÚM!, #NOME? ou #NULO!. 
 
O exemplo a seguir calculará a expressão C5/D5 normalmente, caso não haja 
nenhum erro (#DIV/0!, por exemplo). Se houver, o texto nulo será 
retornado à célula: 
 
=SEERRO(C5/D5; "") 
 
Antes do Excel 2007, a expressão anterior só poderia ser escrita desta 
maneira: 
 
=SE(ÉERRO(C5/D5); ""; C5/D5) 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
12 
 
 
 
A função SEERRO traz uma boa economia de digitação, principalmente se a 
expressão for grande. 
 
 
3.10. Função SENÃODISP 
 
O objetivo da função SENÃODISP (introduzida no Excel 2013) é avaliar uma 
expressão ou cálculo. Se o resultado for um valor #N/D, então ela retornará 
o que você usar como 2º argumento. Caso contrário, o próprio resultado da 
expressão será retornado à célula. 
 
Sua sintaxe é a seguinte: 
 
=SENÃODISP(expressão; valor_se_não_disp) 
 
Os argumentos estão descritos adiante: 
 
 expressão: É o argumento verificado quanto ao erro (geralmente uma 
fórmula); 
 
 valor_se_não_disp: É o valor a ser retornado se a fórmula gerar 
#N/D. 
 
O exemplo a seguir retornará o texto “Produto indisponível” se PROCV não 
conseguir encontrar um valor; caso contrário, retornará o resultado de 
PROCV. 
 
 =SENÃODISP(PROCV(C5;H3:J23;2;FALSO); Produto indisponível.) 
 
 
 
 
 
 
 
 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
13 
 
 
 
3.11. A tabela da verdade 
 
Veja a combinação dos diversos resultados, quando são usados dois testes 
lógicos e as funções auxiliares vistas neste tópico. 
 
Função Teste lógico 1 Teste lógico 2 Resultado 
E VERDADEIRO VERDADEIRO VERDADEIRO 
E VERDADEIRO FALSO FALSO 
E FALSO VERDADEIRO FALSO 
E FALSO FALSO FALSO 
OU VERDADEIRO VERDADEIRO VERDADEIRO 
OU VERDADEIRO FALSO VERDADEIRO 
OU FALSO VERDADEIRO VERDADEIRO 
OU FALSO FALSO FALSO 
XOR VERDADEIRO VERDADEIRO FALSO 
XOR VERDADEIRO FALSO VERDADEIRO 
XOR FALSO VERDADEIRO VERDADEIRO 
XOR FALSO FALSO FALSO 
NÃO VERDADEIRO — FALSO 
NÃO FALSO — VERDADEIRO 
 
 
3.12. Precedência dos operadores 
 
A seguinte tabela mostra a ordem de execução dos cálculos e avaliação de 
fórmulas no Excel. Os parênteses permitem alterar essa sequência: 
 
Símbolo Operador 
Intervalo (:), União (;), Intersecção ( ) Referência 
– Negação 
% Porcentagem 
^ Exponenciação 
* e / Multiplicação e divisão 
+ e – Adição e subtração 
& Concatenação de texto 
=, <, >, <=, >=, e <> Comparação 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
14 
 
 
 
4. Funções de procura e referência 
 
Uma função que pertence à categoria de procura e referência retorna um valor 
de uma tabela ou matriz, através da localização de outro valor, ou então por 
sua posição de linha e coluna dentro da matriz. 
 
Um exemplo de fácil associação é uma lista telefônica: se você deseja 
encontrar o número de telefone de alguém, primeiro você procura pelo nome 
dessa pessoa e, em seguida, verá o telefone na mesma linha. 
 
Existem várias funções úteis para efetuar buscas em tabelas e bancos de 
dados e são bastante usadas na montagem de dashboards, conforme 
resumido a seguir. 
 
4.1. Função PROCV 
 
A função toma o valor_procurado e faz uma busca ao longo da 1ª coluna da 
matriz_tabela. Quando encontrado, segue na mesma linha para a direita, 
até a coluna de número índice_coluna. O argumento opcional 
tipo_procura informa se a procura será feita de maneira aproximada (1 ou 
VERDADEIRO) ou exata (0 ou FALSO). 
 
• Sintaxe: 
 
=PROCV(valor_procurado; matriz_tabela; índice_coluna; [tipo_ 
procura]) 
 
4.2. Função PROCH 
 
A função toma o valor_procurado e faz uma busca ao longo da 1ª linha da 
matriz_tabela. Quando encontrado, segue na mesma coluna para baixo, até 
a linha de número índice_linha. O argumento opcional tipo_procura 
informa se a procura será feita de maneira aproximada (1 ou VERDADEIRO) 
ou exata (0 ou FALSO). 
 
• Sintaxe: 
 
=PROCH(valor_procurado; matriz_tabela; índice_linha; [tipo_procu- 
ra]) 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
15 
 
 
 
4.3. Função PROC 
 
A função toma o valor_procurado e faz uma busca ao longo do intervalo 
vetor_ de_procura (pode ser uma linha ou coluna). Quando encontrado, o 
valor que está na mesma posição do vetor_de_resultado será retornado. 
 
• Sintaxe: 
 
=PROC(valor_procurado; vetor_de_procura; [vetor_de_resultado]) 
 
O vetor de procura precisa estar classificado em ordem crescente. 
 
4.4. Função CORRESP 
 
A função procura um item (valor_procurado) num intervalo 
(matriz_procurado) e retorna a posição relativa em que este item se 
encontra na lista. 
 
 Sintaxe: 
 
=CORRESP(valor_procurado; matriz_procurada; [tipo_correspondên- 
cia]) 
 
O tipo_correspondência pode ser exata (0) ou aproximada (-1 ou 1). Use 1 
quando o valor a se retornado for menor que o valor procurado ou use -1 
quando o valor a se retornado for maior que o valor procurado. 
 
• Exemplo: 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
16 
 
 
 
Na imagem anterior, existe um intervalo com nomes de regiões. Na célula E3, 
está escrito o nome de uma das regiões e a célula E4 contém a fórmula que 
determina a posição relativa da região escolhida na lista de regiões: 
 
=CORRESP(E3; B3:B11; 0) 
 
 
4.5. Função ÍNDICE 
 
A função retorna o valor de um elemento em uma tabela ou matriz 
selecionado pelos índices de número de linha e coluna. Quando a matriz for 
apenas uma linha ou uma coluna, o último argumento pode ser omitido. 
 
 Sintaxe: 
 
=ÍNDICE(matriz; [núm_linha]; [núm_coluna]) 
 
 Exemplos: 
 
 
 
Na imagem anterior, existe um intervalo com nomes de regiões. A célula C5 
contém uma posição de região dentro da tabela e a célula C6 contém a 
fórmula que traz o valor da tabela que se encontra na posição especificada em 
C5: 
 
=ÍNDICE(B3:J3; ;C5) 
 
Como a matriz tem apenas uma linha (também chamada de vetor), o segundo 
argumento foi omitido e a posição indica a coluna de interesse dentro da 
matriz. A fórmula também funcionaria com apenas dois argumentos neste 
caso: 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
17 
 
 
 
=ÍNDICE(B3:J3; C5) 
 
Quando a matrizfor bidimensional, será necessário especificar os índices de 
linha e coluna, como no exemplo a seguir: 
 
 
 
Neste caso, as posições desejadas de linha e coluna estão escritas nas células 
B2 e B3. A matriz bidimensional de números está no intervalo D2:K6. A 
fórmula em B5 traz o valor que se encontra nas posições de linha e coluna 
especificada: 
 
=ÍNDICE(D2:K6; B2; B3) 
 
 
4.6. Funções ÍNDICE e CORRESP usadas em conjunto 
 
O verdadeiro poder dessas funções é notado quando elas são usadas em 
conjunto. A finalidade é localizar um valor numa matriz, cuja posição da linha 
é obtida pela correspondência de um item na lista de títulos de linha e a 
posição da coluna é obtida pela correspondência de um item na lista de títulos 
de coluna. A matriz será apenas o interior da tabela, sem esses títulos. 
 
 
 
 
 
 
 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
18 
 
 
 
• Exemplo: 
 
Observe a tabela com departamentos escritos na primeira coluna e meses 
escritos na primeira linha: 
 
 
 
A célula C8 contém o nome do departamento que se deseja localizar e a 
célula C10 indica o mês de interesse. A localização do valor desejado é obtida 
pelo cruzamento da linha que contém o departamento e da coluna que contém 
o mês. 
 
 
 
A localização do valor procurado pode ser feita de duas maneiras: 
 
• Em etapas: Através da função CORRESP, calcula-se a posição relativa que 
cada item ocupa na coluna de título e na linha de título. Em seguida, a função 
ÍNDICE será capaz de recuperar o elemento que se encontra na linha e 
coluna calculadas anteriormente; 
 
• Em uma única fórmula: Use a função ÍNDICE, identificando a matriz de 
números. Como segundo argumento (posição da linha), use CORRESP para 
localizar a posição que o departamento (C8) ocupa na lista de departamentos 
da coluna F. Como terceiro argumento (posição da coluna), use CORRESP 
para localizar a posição que o mês (C10) ocupa na lista de meses da linha 6. 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
19 
 
 
 
Solução com várias fórmulas: 
 
 A célula D8 contém a posição do departamento (C8) na lista de 
departamentos (F7:F14): 
 
 =CORRESP(C8;F7:F14;0) 
 
 A célula D10 contém a posição do mês (C10) na lista de meses (G6:L6): 
 
=CORRESP(C10;G6:L6;0) 
 
 Finalmente, a célula C12 poderá obter o valor na matriz de números 
(G7:L14), em função das posições de linha e coluna calculadas 
anteriormente: 
 
=ÍNDICE(G7:L14; D8; D10) 
 
Solução com uma única fórmula, usando funções aninhadas: 
 
 Em C14, a função ÍNDICE precisará da matriz de números (G7:L14), 
da posição da linha e da posição da coluna. No local do 2º e 3º 
argumentos da função, você precisará incluir a função CORRESP para 
realizar a tarefa de obter as posições: 
 
=ÍNDICE(G7:L14; CORRESP(C8;F7:F14;0); CORRESP(C10;G6:L6;0)) 
 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
20 
 
 
 
4.7. Função ESCOLHER 
 
Use ESCOLHER para selecionar um valor entre 254 valores que se baseie no 
número de índice. 
 
 Sintaxe: 
 
 =ESCOLHER(núm_índice; valor1; [valor2], ...) 
 
 Exemplo: 
 
A célula C2 contém um número de 1 a 7, representando o dia da semana 
(domingo a sábado). Imagine que você necessita escrever o nome do dia da 
semana em espanhol, baseado no número em C2. Uma solução seria usar a 
função SE de forma aninhada: 
 
=SE(C2=1; "Domingo"; SE(C2=2; "Lunes"; SE(C2=3; "Martes";SE(C2=4; 
"Miércoles"; SE(C2=5; "Jueves"; SE(C2=6; "Viernes"; 
"Sábado")))))) 
 
A fórmula a seguir, mais simples, retorna o nome do dia da semana em 
espanhol, baseado no número em C2, usando a função ESCOLHER: 
 
=ESCOLHER(C2; "Domingo"; "Lunes"; "Martes"; "Miércoles"; 
"Jueves"; "Viernes"; "Sábado") 
 
 
Se você desejar que o dia da semana seja retornado em função da data de 
hoje: 
 
=ESCOLHER(DIA.DA.SEMANA(HOJE()); "Domingo"; "Lunes"; "Martes"; 
"Miércoles"; "Jueves"; "Viernes"; "Sábado") 
 
 
 
 
 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
21 
 
 
 
4.8. Função DESLOC 
 
Esta função retorna uma referência para um novo intervalo, que é um 
deslocamento especificado em linhas e colunas a partir de uma célula de 
referência e que pode possuir um novo tamanho em termos de linhas e 
colunas. A referência retornada pode ser uma única célula ou um intervalo de 
células. 
 
 Sintaxe: 
 
 =DESLOC(ref; lins; cols; [altura]; [largura]) 
 
 Argumentos: 
 
 Ref: A referência a partir da qual você deseja basear o deslocamento. 
Ref deve ser uma referência a uma célula ou intervalo de células 
adjacentes; caso contrário, DESLOC retornará o valor de erro #VALOR!; 
 
 Lins: O número de linhas, acima ou abaixo, onde começará o intervalo 
deslocado. Lins pode ser um número positivo (que significa abaixo da 
referência inicial) ou negativo (acima da referência inicial); 
 
 Cols: O número de colunas, à esquerda ou à direita, onde começará o 
intervalo deslocado. Cols pode ser um número positivo (que significa à 
direita da referência inicial) ou negativo (à esquerda da referência inicial); 
 
 Altura (Opcional): A altura, em número de linhas, que se deseja para a 
referência fornecida; 
 
 Largura (Opcional): A largura, em número de colunas, que se deseja 
para a referência fornecida. 
 
A função DESLOC é extremamente útil na criação de intervalos dinâmicos, os 
quais variam, conforme a quantidade de dados existentes. 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
22 
 
 
 
 Exemplo: 
 
No caso a seguir deseja-se somar um intervalo de números que está 
deslocado de B2 em 4 linhas abaixo e 2 colunas à direita. O novo intervalo 
possui 3 células de altura por 2 células de largura: 
 
 
 
A soma na célula B12 será: 
 
=SOMA(DESLOC(B2; 4; 2; 3; 2)) 
 
 
4.8.1. Usando DESLOC para obter intervalo deslocado 
 
Observe no banco de dados abaixo com estados brasileiros, capitais, 
população e quantidade de municípios: 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
23 
 
 
 
Na célula C5 você deverá escolher uma das cinco regiões. Defina uma 
validação de dados por lista para facilitar a escolha. 
 
Nas células abaixo, você criará fórmulas que retornem a maior população 
dentro da região escolhida e seu estado correspondente. Da mesma forma, a 
menor população e o maior número de municípios e seus estados 
correspondentes também serão determinados. 
 
Se fosse uma soma, a tarefa seria facilmente resolvida com a função 
SOMASE. Mas, neste caso, são máximos e mínimos. Como o Excel não possui 
as funções MÁXIMOSE e MÍNIMOSE, então uma solução neste caso será o 
uso da função DESLOC para encontrar o intervalo correto para obter os 
valores de máximo e mínimo. 
 
A fórmula para a maior população da região escolhida (célula C7) será obtida 
pela função MÁXIMO: 
 
=MÁXIMO(intervalo) 
 
O intervalo de valores de População, porém, deverá ser descoberto no banco 
de dados e será deslocado para baixo (partindo da célula I5), conforme a 
região e quantidade de estados que compõem a região: 
 
=MÁXIMO(DESLOC(I5; núm_linhas; 0; altura; 1)) 
 
Pela fórmula anterior: 
 
 O deslocamento será a partir da célula I5 (cabeçalho da coluna 
População); 
 
 A quantidade de linhas de deslocamento para baixo (núm_linhas) ainda 
é desconhecida; 
 
 O deslocamento de colunas será 0, pois o intervalo será na mesma coluna 
da célula de referência I5; 
 
 A altura do novo intervalo (quantidade de células) também édesconhecida; 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
24 
 
 
 
 Por fim, a largura do novo intervalo será de 1 coluna. 
 
O deslocamento para baixo (núm_linhas) pode ser obtido pela localização da 
primeira ocorrência do nome da região escolhida (C5) ao longo do intervalo 
E6:E32: 
 
CORRESP(C5; E6:E32; 0) 
 
A altura do intervalo deslocado resultante será determinado pelo número de 
ocorrências do nome da região escolhida (C5) ao longo do intervalo E6:E32: 
 
CONT.SE(E6:E32; C5) 
 
Finalmente, a fórmula que obtém a maior população será montada, 
aninhando- se as duas funções anteriores dentro da expressão original: 
 
=MÁXIMO(DESLOC(I5;CORRESP(C5;E6:E32;0);0;CONT.SE(E6:E32;C5);1)) 
 
Da mesma forma, a menor população da região escolhida (célula C10) será 
obtida pela fórmula: 
 
=MÍNIMO(DESLOC(I5;CORRESP(C5;E6:E32;0);0;CONT.SE(E6:E32;C5);1)) 
 
O maior número de municípios da região (célula C13) será calculado de 
maneira semelhante: 
 
=MÁXIMO(DESLOC(J5;CORRESP(C5;E6:E32;0);0;CONT.SE(E6:E32;C5);1)) 
 
Para o cálculo do nome do estado que corresponde à população calculada 
(célula C8), não será possível usar PROCV porque a coluna da População 
não é a primeira da tabela. Também não é possível usar PROC porque a 
coluna da população não está em ordem crescente. 
 
A solução neste caso é usar a função CORRESP para localizar a posição que 
a população calculada (C7) ocupa na coluna das populações (I6:I32): 
 
CORRESP(C7; $I$6:$I$32; 0) 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
25 
 
 
 
Em seguida, essa posição será usada na função ÍNDICE para obter o estado 
correspondente: 
 
=ÍNDICE($F$6:$F$32; CORRESP(C7; $I$6:$I$32; 0)) 
 
Alternativamente, você também pode usar esta fórmula: 
 
=DESLOC(F5; CORRESP(C7; I6:I32; 0); 0) 
 
Como os intervalos da fórmula com ÍNDICE e CORRESP usam 
endereçamento absoluto, é possível copiá-la para as células C11 e C14. 
 
A planilha finalizada, com escolha da região Sudeste ficará assim: 
 
 
 
O exemplo supõe que não há repetição de valores de população. 
 
4.8.2. Usando DESLOC para recuperar uma célula numa 
matriz 
 
Quando o intervalo deslocado for de apenas uma célula, a função DESLOC 
pode ser usada sozinha, como meio de obter um valor numa matriz, na qual 
são fornecidos os valores do deslocamento de linha e de coluna, partindo do 
canto superior esquerdo da tabela. 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
26 
 
 
 
Acompanhe o exemplo abaixo: 
 
 
 
O intervalo numérico (matriz) é C7:N16. A célula de referência é B6. Na 
célula D4, deverá ser digitado o número da linha e, na célula G4, o número 
da coluna. Esses números representam, na realidade, os deslocamentos a 
partir da célula de referência. Como o intervalo resultante será de apenas uma 
célula, os dois últimos argumentos de DESLOC podem ser omitidos. 
 
A célula J4 conterá a fórmula que traz o valor que se encontra na linha e 
coluna especificada: 
 
=DESLOC(B6; D4; G4) 
 
 
4.8.3. Usando DESLOC para criar intervalos dinâmicos 
 
Uma das maiores vantagens de DESLOC é a montagem de um intervalo 
elásticos para os bancos de dados. A vantagem é que, se o banco de dados 
crescer em número de linhas, você não precisará reajustar as fórmulas para as 
novas dimensões do banco. 
 
 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
27 
 
 
 
Como exemplo, veja o caso abaixo: 
 
 
 
A coluna C contém algumas fórmulas que calculam valores diretamente dos 
dados da tabela: 
 
 
 
Quando linhas novas são inseridas dentro da tabela, os intervalos são 
aumentados automaticamente. Porém, quando novas informações são 
digitadas na continuação do banco de dados, os intervalos não são ajustados 
automaticamente. 
 
A função DESLOC pode ser usada com esse benefício. Quando o intervalo 
usa a função, quaisquer novos dados acrescentados ao final do banco são 
imediatamente incluídos no intervalo e, portanto, as fórmulas não precisam 
ser reajustadas manualmente. 
 
No exemplo da planilha acima, você criará três intervalos nomeados: Data, 
Região e Vendas, correspondentes às colunas do banco de dados. 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
28 
 
 
 
1. Na guia Fórmulas, grupo Nomes Definidos, clique no botão 
Gerenciador de Nomes (ou use o atalho CTRL + F3). Em seguida, 
clique no botão Novo...; 
 
2. Na caixa de diálogo Novo Nome, na caixa Nome, escreva Data. No 
campo 
Refere-se a, digite a seguinte fórmula: 
 
=DESLOC(Plan5!$E$4; 1; 0; CONT.NÚM(Plan5!E:E); 1) 
 
Os argumentos para a função DESLOC são: 
 
• Plan5!$E$4: A célula de referência está na planilha DESLOC3, célula E4 
(cabeçalho da coluna Data); 
 
 1: A quantidade de linhas abaixo da célula de referência, onde se inicia o 
intervalo; 
 
 0: A quantidade de colunas à direita da célula de referência, onde se 
inicia o intervalo (nenhuma; é a própria coluna); 
 
 CONT.NÚM(Plan5!E:E): Essa função calcula a quantidade de 
valores numéricos da coluna E. Isso significa a quantidade de linhas de 
altura; 
 
 1: O novo intervalo terá 1 coluna de largura. 
 
3. Da mesma forma, crie o nome Região com a seguinte definição de 
intervalo: 
 
=DESLOC(Plan5!$F$4; 1; 0; CONT.NÚM(Plan5!E:E); 1) 
 
4. Crie também o nome Vendas com o seguinte intervalo dinâmico: 
 
=DESLOC(Plan5!$G$4; 1; 0; CONT.NÚM(Plan5!E:E); 1) 
 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
29 
 
 
 
Se você observar, apenas a célula de referência muda (coluna), uma vez que 
as datas serão usadas para a contagem de números, o que fornece a 
quantidade de registros do banco de dados. 
 
As fórmulas serão agora alteradas para: 
 
 
 
Experimente acrescentar dados no final da base. Como exemplo, copie as 
quatro últimas linhas da tabela para abaixo dela. Você verá que o total de 
registros calculados em C4 mudou automaticamente de 14 para 18, assim 
como as outras fórmulas da coluna C. 
 
 
 
Quando criar intervalos dinâmicos, o único argumento da função DESLOC que 
você precisará calcular será a altura do novo intervalo (em linhas). Para isso, 
use funções de contagem e tome cuidado para não selecionar valores fora dos 
dados que possam interferir com a contagem, como títulos e totais, por 
exemplo. 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
30 
 
 
 
4.9. Função ENDEREÇO 
 
Esta função monta uma referência de célula, baseada no número de linha e 
número de coluna fornecidos. Opcionalmente, pode-se fornecer a informação 
sobre o endereçamento (absoluto, relativo ou misto), sobre o estilo de 
representação do endereço (A1 ou L1C1) e se o intervalo virá de uma pasta de 
trabalho externa. O resultado da função é um texto que representa o endereço 
desejado. 
 
 Sintaxe: 
 
=ENDEREÇO(Linha; Coluna; [Tipo_ref]; [Estilo_A1?]; [Texto_ 
planilha]) 
 
 Argumentos: 
 
o Linha: Especifica o número de linha a ser usado na referência de 
célula; 
 
o Coluna: Especifica o número de coluna a ser usado na referência de 
célula; 
 
o Tipo_ref: Valor numérico que especifica o tipo de referência: 1 
(absoluta), 2 (linha absoluta, coluna relativa), 3 (linha relativa, coluna 
absoluta) ou 4 (relativa); 
 
o Estilo_A1?: VERDADEIRO se o estilo de endereçamento for A1, 
FALSO se o estilo for L1C1; 
 
o Texto_planilha: Um texto que especifica o nome da planilha a ser 
usada como referênciaexterna. Se este argumento for omitido, 
nenhum nome de planilha será usado e o endereço retornado pela 
função fará referência a uma célula da planilha ativa. 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
31 
 
 
 
4.10. Função INDIRETO 
 
Retorna a referência especificada por uma sequência de caracteres de texto 
(Texto_ref). O argumento Estilo_A1?, se VERDADEIRO (padrão), indica que 
os endereços serão no estilo A1; caso seja FALSO, os endereços serão no 
estilo L1C1. As referências são imediatamente avaliadas para exibir seu 
conteúdo. A função é útil para mudar a referência a uma célula dentro uma 
fórmula, sem mudar a própria fórmula. O resultado da função é um endereço 
de uma célula ou intervalo. 
 
 Sintaxe: 
 
=INDIRETO(Texto_ref; [Estilo_A1?]) 
 
No exemplo abaixo, você verá uma tabela com cinco produtos e valores para 
os doze meses do ano: 
 
 
 
O objetivo será escolher um produto na célula C5 e um mês na célula C6. As 
células C8 e C9 deverão conter fórmulas que calculem as somas para o 
produto escolhido e para o mês escolhido. A célula C10 deverá trazer o valor 
que se encontra na intersecção da linha do produto escolhido com a coluna do 
mês escolhido. 
 
Para resolver essa questão, é necessário seguir os passos adiante: 
 
1. Crie nomes automaticamente para cada intervalo da tabela. Na guia 
Fórmulas, grupo Nomes Definidos, clique no botão Criar a partir da 
Seleção; 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
32 
 
 
 
 
 
2. Mantenha selecionadas as caixas Linha superior e Coluna esquerda e 
clique em OK. Uma série de nomes será criada automaticamente. Você 
poderá conferir os nomes clicando na seta da Caixa de nome ou 
acionando o Gerenciador de Nomes na guia Fórmulas; 
 
3. Na célula C8, preencha a fórmula que calcula a soma para o produto 
escolhido em C5: 
 
=SOMA(INDIRETO(C5)) 
 
4. Na célula C9, preencha a fórmula que calcula a soma para o mês 
escolhido em C6: 
 
=SOMA(INDIRETO(C6)) 
 
5. Na célula C10, preencha a fórmula que obtém o valor na intersecção da 
linha do produto com a coluna do mês. Você também pode usar a 
combinação das funções DESLOC com CORRESP: 
 
=DESLOC(E5; CORRESP(C5;E6:E10;0); CORRESP(C6;F5:Q5;0)) 
 
Alternativamente, você também poderia usar esta fórmula: 
 
=ÍNDICE(F6:Q10; CORRESP(C5;E6:E10;0); CORRESP(C6;F5:Q5;0)) 
 
Após a criação das fórmulas sua planilha ficará assim: 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
33 
 
 
 
4.11. Funções LIN, COL, LINS, COLS 
 
 Sintaxe para LIN: 
 
=LIN(referência_de_célula) 
 
 Sintaxe para COL: 
 
=COL(referência_de_célula) 
 
 Sintaxe para LINS: 
 
=LINS(intervalo) 
 
 Sintaxe para COLS: 
 
=COLS(intervalo) 
 
LIN retorna o número da linha e COL o número da coluna de uma referência 
de intervalo. Caso o parâmetro seja omitido, LIN() traz o número da linha em 
que está a fórmula e COL() traz o número da coluna em que está a fórmula. 
LINS e COLS retornam, respectivamente, o número total de linhas e colunas 
de um intervalo. 
 
 
5. Funções de texto 
 
As funções de texto são uma excelente ferramenta para tratar uma grande 
quantidade de dados, em que as edições manuais ficariam impraticáveis ou 
muito demoradas. Use funções de texto em bancos de dados para efetuar 
extração de informações, limpeza de textos com excesso de caracteres 
irrelevantes, concatenações, substituições e outras transformações. Após obter 
os textos desejados, eles podem ser copiados de volta para a base de dados 
como valores. 
 
Os subtópicos a seguir relacionarão brevemente as principais funções de texto. 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
34 
 
 
 
5.1. Concatenação de texto 
 
A função de concatenação une diversos conteúdo em um único texto. Quando 
um número é concatenado, este aparecerá sem qualquer formato: 
 
CONCATENAR(texto1; texto2; texto3;...) 
 
Exemplos: 
 
 =CONCATENAR(A5;"— ";B5); 
 
 = A5&" — "&B5 (usando o operador de concatenação “&”). 
 
A sintaxe a seguir repete o texto especificado no 1º argumento de acordo com 
o número de vezes dado pelo segundo parâmetro: 
 
REPT(texto; número) 
 
Exemplo: =REPT("X-"; 5) retorna o texto X-X-X-X-X-. 
 
 
5.2. Maiúsculas e minúsculas 
 
A função Maiúscula retorna o mesmo texto com todas as letras maiúsculas. 
Veja a sua sintaxe: 
 
MAIÚSCULA(texto) 
 
 Exemplo: =MAIÚSCULA("Excel") retorna o texto EXCEL. 
 
Já a função Minúscula retorna o mesmo texto com todas as letras 
minúsculas. Veja a sua sintaxe: 
 
MINÚSCULA(texto) 
 
 Exemplo: =MINÚSCULA("Excel") retorna o texto excel. 
 
A função a seguir retorna o mesmo texto com cada palavra iniciando com letra 
maiúscula: 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
35 
 
 
 
PRI.MAIÚSCULA(texto) 
 
 Exemplo: =PRI.MAIÚSCULA("aula de Excel") retorna o texto Aula 
De Excel. 
 
 
5.3. Operações com caracteres 
 
A função a seguir retorna o número de caracteres para o texto especificado: 
 
NÚM.CARACT(texto) 
 
 Exemplo: =NÚM.CARACT("Aula de Excel") retorna o valor 13. 
 
A próxima função retorna o caractere (ou símbolo) que possui o código 
numérico especificado pela tabela ASCII: 
 
CARACT(código) 
 
 Exemplo: =CARACT(174) retorna o caractere ® (marca registrada). 
 
A função Código retorna o código numérico ASCII do primeiro caractere do 
texto especificado. Veja sua sintaxe: 
 
CÓDIGO(texto) 
 
 Exemplo: =CÓDIGO("Aula de Excel") retorna o número 65 (código 
para a letra A). 
 
A função adiante retorna o caractere Unicode referenciado pelo determinado 
valor numérico: 
 
CARACTUNICODE(número) 
 
 Exemplo: =CARACTUNICODE(8771) retorna o caractere ≃. 
 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
36 
 
 
 
A função a seguir retorna o código Unicode do primeiro caractere em texto: 
 
UNICODE(texto) 
 
 Exemplo: =UNICODE("ABC") retorna o código 65. 
 
 
5.4. Formatação de números 
 
A função Valor retorna o conteúdo de texto fornecido como número (deve 
conter apenas dígitos numéricos, símbolo de moeda R$, pontos separadores 
de milhar e vírgula): 
 
VALOR(texto) 
 
 Exemplo: =VALOR("1.234,56") retorna o número 1234,56. 
 
A próxima função retorna o conteúdo do texto fornecido como número. O 
argumento separador_decimal define qual é o caractere que separa a parte 
inteira da decimal; já o argumento separador_grupo define qual é o caractere 
que separa os milhares. 
 
VALORNUMÉRICO(texto; [separador_decimal], [separador_grupo]) 
 
 Exemplo: =VALORNUMÉRICO("1,234.56";".";",") retorna o número 
1234,56. 
 
Esta função retorna um texto que representa um número formatado com a 
quantidade de decimais especificada e define se haverá ou não separador de 
milhar: 
 
DEF.NÚM.DEC(número; [decimais] ; [sem_sep_milhar]) 
 
 Exemplo: =DEF.NÚM.DEC(12345,6789; 2; FALSO) retorna o texto 
12.345,68. 
 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
37 
 
 
 
A função a seguir toma um número e retorna um texto formatado como 
moeda, com duas casas decimais (por omissão do 2º argumento): 
 
MOEDA(número; [decimais]) 
 
 Exemplo: =MOEDA("2355,6") retorna o texto R$ 2.355,60. 
 
A função Texto toma um número e retorna um texto formatado segundo o 
formato especificado: 
 
TEXTO(número; formato) 
 
 Exemplo: =TEXTO(C5; "dddd, dd/mm/aaaa") retorna uma data 
formatadacomo texto. Se C5 contém a data 01/01/2014, a expressão 
retorna o texto quarta-feira, 01/01/2014; 
 
 Exemplo: =TEXTO(300/1800; "0,0%") retorna o texto 16,7%. 
 
5.5. Extração de texto 
 
A função adiante extrai os n primeiros caracteres em texto: 
 
ESQUERDA(texto; n) 
 
 Exemplo: =ESQUERDA("Aula de Excel"; 4) retorna o texto Aula. 
 
A função a seguir extrai os n últimos caracteres em texto: 
 
DIREITA(texto; n) 
 
 Exemplo: =DIREITA("Aula de Excel"; 5) retorna o texto Excel. 
 
A próxima função extrai n caracteres em texto, a partir da posição inicial 
especificada: 
 
EXT.TEXTO(texto; pos_inicial; n) 
 
 Exemplo: =EXT.TEXTO("Aula de Excel";6;2) retorna o texto de. 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
38 
 
 
 
5.6. Localização e substituição de caracteres 
 
A função a seguir retorna a posição que texto1 ocupa em texto2, partindo da 
posição núm_inicial. Maiúsculas e minúsculas são ignoradas. 
 
LOCALIZAR(texto1; texto2; [núm_inicial]) 
 
 Exemplo: =LOCALIZAR("EXCEL"; "Aula de Excel";1) retorna o valor 
9. 
 
A função adiante extrai em texto1, a partir da posição especificada 
(pos_inicial), a quantidade de caracteres definida por núm_caract e insere no 
local os caracteres definidos em texto2. 
 
MUDAR(texto1; pos_inicial; núm_caract; texto2) 
 
 Exemplo: =MUDAR("Aula de Excel"; 1; 4; ("Curso") retorna o texto 
Curso de Excel. 
 
A função a seguir substitui, dentro de texto1, os caracteres do texto2 para os 
do texto3. 
 
SUBSTITUIR(texto1; texto2; texto3) 
 
 Exemplo: =SUBSTITUIR("Curso de Excel";"curso";"Aula") retorna o 
texto Aula de Excel. 
 
 
5.7. Outras funções 
 
A função adiante remove todos os espaços extras de um texto. 
 
ARRUMAR(texto) 
 
 Exemplo: =ARRUMAR("Aula de Excel ") retorna Aula de Excel. 
 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
39 
 
 
 
A próxima função remove todos os caracteres não imprimíveis de um texto 
(geralmente caracteres de controle, tabulações, quebras de linha, quebras de 
página, etc.): 
 
TIRAR(texto) 
 
 Exemplo: =TIRAR(B5) retorna o texto da célula B5 sem os caracteres 
que não podem ser impressos. 
 
A função a seguir retorna VERDADEIRO se os dois textos são idênticos, caso 
contrário retorna FALSO. Maiúsculas e minúsculas são consideradas letras 
diferentes. 
 
EXATO(texto1; texto2) 
 
 Exemplo: =SE(EXATO(B2; C2); "Confere"; "Não Confere”). 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
40 
 
 
 
5.8. Pontos importantes 
 
• A base para a montagem de fórmulas mais sofisticadas é o perfeito 
conhecimento de como os endereços de células e intervalos são referenciados. 
O Excel trabalha com endereçamento (referência) absoluto, relativo e misto; 
 
• Frequentemente as bases de dados do seu dashboard ficarão em planilhas 
diferentes ou mesmo em arquivos diferentes. A montagem das fórmulas pode 
seguir o mesmo princípio da facilidade: aponte para os endereços enquanto 
digita a fórmula. Isso garante que você selecionará o intervalo correto. No 
final da digitação, tecle ENTER para que a fórmula seja encerrada e deixada 
na planilha de origem; 
 
• O uso avançado de muitos recursos do Excel fica mais evidente quando as 
fórmulas ou referências são feitas com nomes ao invés de intervalos digitados 
explicitamente. As vantagens do uso de intervalos nomeados são claras: 
facilita a leitura das fórmulas, podem ser empregados em outras planilhas do 
mesmo arquivo e são facilmente organizados pelo Gerenciador de Nomes 
(acionado pela guia Fórmulas ou pelo atalho CTRL + F3); 
 
• As funções matemáticas e estatísticas mais comuns no Excel e que 
trabalham com intervalos de células são: SOMA, MÉDIA, MÁXIMO, 
MÍNIMO para estatísticas básicas; CONT.VALORES, CONT.NÚM, 
CONTAR.VAZIO para realizar contagens diversas. As funções matemáticas e 
estatísticas que trabalham com condições (critérios) são particularmente úteis 
na montagem de dashboards. Elas podem calcular uma única informação, ou 
fazer parte de uma tabela com fórmulas mais complexas, na qual nem sempre 
uma tabela dinâmica é a melhor alternativa; 
 
 As funções condicionais estão presentes no dia a dia de quem trabalha 
com o Excel, desde o nível básico. As principais são: SE, E, OU, XOR, 
NÃO, SE aninhada, SEERRO e SENÃODISP; 
 
 Uma função que pertence à categoria de procura e referência retorna um 
valor de uma tabela ou matriz, através da localização de outro valor, ou 
então por sua posição de linha e coluna dentro da matriz; 
 
 
Fórmulas e funções avançadas para criação de dashboards no Excel 
 ________________________________________________________________________ 
41 
 
 
 
 As funções de texto são uma excelente ferramenta para tratar uma 
grande quantidade de dados, em que as edições manuais ficariam 
impraticáveis ou muito demoradas. Use funções de texto em bancos de 
dados para efetuar extração de informações, limpeza de textos com 
excesso de caracteres irrelevantes, concatenações, substituições e outras 
transformações. Após obter os textos desejados, eles podem ser copiados 
de volta para a base de dados como valores.