E-Book - Fórmulas e funções avançadas

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? ............................................................................................................ 3 
2. TIPOS DE REFERÊNCIA.................................................................................................................... 3 
2.1 REFERÊNCIAS A OUTRAS PLANILHAS E PASTAS DE TRABALHO ......................................................................................... 4 
2.1.1 Referências a outras planilhas do mesmo arquivo..................................................................................... 4 
2.1.2 Referências a outros arquivos ...................................................................................................................... 5 
3. INTERVALOS NOMEADOS E FÓRMULAS NOMEADAS ........................................................................ 6 
3.1 NOMEANDO CÉLULAS E INTERVALOS ........................................................................................................................... 6 
4. FUNÇÕES MATEMÁTICAS E ESTATÍSTICAS ....................................................................................... 9 
4.1 FUNÇÕES ESTATÍSTICAS CONDICIONAIS ..................................................................................................................... 10 
5. FUNÇÕES CONDICIONAIS ..............................................................................................................11 
5.1 FUNÇÃO CONDICIONAL SE ....................................................................................................................................... 11 
5.2 FUNÇÃO AUXILIAR E ................................................................................................................................................ 11 
5.3 FUNÇÃO AUXILIAR OU ............................................................................................................................................ 12 
5.4 FUNÇÃO AUXILIAR XOR........................................................................................................................................... 12 
5.5 FUNÇÃO AUXILIAR NÃO .......................................................................................................................................... 13 
5.6 FUNÇÃO SE ANINHADA ........................................................................................................................................... 13 
6. FUNÇÕES DE PROCURA E REFERÊNCIA ...........................................................................................14 
6.1 FUNÇÃO PROCV .................................................................................................................................................... 14 
6.2 FUNÇÃO PROCH.................................................................................................................................................... 15 
6.3 FUNÇÃO PROC ...................................................................................................................................................... 15 
6.4 FUNÇÃO CORRESP ................................................................................................................................................ 15 
6.5 FUNÇÃO ÍNDICE .................................................................................................................................................... 16 
6.6 FUNÇÕES ÍNDICE E CORRESP USADAS EM CONJUNTO ............................................................................................. 17 
6.7 FUNÇÃO DESLOC .................................................................................................................................................. 19 
6.7.1 Usando DESLOC para obter intervalo deslocado ...................................................................................... 21 
6.7.2 Usando DESLOC para recuperar uma célula numa matriz ...................................................................... 25 
6.7.3 Usando DESLOC para criar intervalos dinâmicos ..................................................................................... 27 
6.8 FUNÇÃO INDIRETO ............................................................................................................................................... 30 
7. FUNÇÕES DE TEXTO ......................................................................................................................33 
7.1 CONCATENAÇÃO DE TEXTO ...................................................................................................................................... 33 
7.2 FUNÇÃO REPT ....................................................................................................................................................... 33 
7.3 MAIÚSCULAS E MINÚSCULAS ................................................................................................................................... 34 
7.4 EXTRAÇÃO DE TEXTO ............................................................................................................................................... 35 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
3 
 
 
1. O que são Dashboards? 
 
 
Dashboards são ilustrações gráficas que fornecem visualizações rápidas dos 
indicadores mais importantes para um negócio específico. 
 
Os Dashboards possuem três atributos principais: 
 
• Os Dashboards são normalmente de natureza gráfica, fornecendo 
visualizações que ajudam a concentrar a atenção nas principais tendências, 
comparações e exceções. 
 
• Os Dashboards geralmente exibem apenas dados relevantes para o objetivo 
do negócio. 
 
• Como os Dashboards são projetados com um propósito ou objetivo 
específico, eles contêm, inerentemente, conclusões predefinidas que 
dispensam o usuário final de realizar sua própria análise. 
 
 
2. Tipos de referência 
 
O Excel trabalha com os seguintes tipos de endereçamento (referência) de 
células em suas fórmulas: 
 
• Relativo: É uma referência a uma célula ou intervalo que sempre se ajusta, 
conforme a coluna e linha para onde a fórmula é movida ou copiada. 
Exemplo: A5; 
 
• Absoluto: É uma referência a uma célula ou intervalo que nunca se ajusta, 
conforme a coluna e linha para onde a fórmula é movida ou copiada. 
Exemplo: $A$5; 
 
• Misto: É uma referência a uma célula ou intervalo que ajusta apenas a 
linha ou coluna para onde a fórmula é movida ou copiada. Exemplo: $A5 
(coluna fixa, linha variável) ou A$5 (coluna variável, linha fixa). 
 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
4 
 
 
A imagem a seguir resume os quatros tipos de endereçamento no Excel. A célula 
B4 contém a fórmula =(A1-B1)*C1. Veja o que acontece com a referência C1 
quando essa fórmula é copiada para a célula C7, assumindo as quatro 
possibilidades de endereçamento: C1 (relativo), $C$1 (absoluto) $C1 (misto) 
e C$1 (misto). 
 
 
 
 
 
 
2.1 Referências a outras planilhas e pastas de trabalho 
 
A base de dados de um Dashboard pode estar localizada em uma planilha 
separada ou até mesmo em outras pastas de trabalho. A organização dessas 
informações facilitará e muito o trabalho de planejamento e desenvolvimento 
dos painéis de controles interativos. 
 
 
2.1.1 Referências a outras planilhas do mesmo arquivo 
 
A sintaxe para você referenciar um intervalo que está em outra planilha é: 
 
Nome_da_planilha!Intervalo 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________5 
 
 
A fórmula seguinte calcula a soma do intervalo A1 até A89, obtido da planilha 
Plan1: 
 
=SOMA(Plan1!A1:A89) 
 
Um nome de planilha poderá ter espaços. Neste caso, este nome ficará entre 
aspas simples. Exemplo: =’Auxiliar’!A1. 
 
 
 
 
 
 
 
 
 
 
2.1.2 Referências a outros arquivos 
 
Para se referenciar intervalos em outros arquivos, o princípio é o mesmo, basta 
apontar para o intervalo desejado e clicar ENTER para concluir. Entretanto, 
nesse caso, teremos um passo adicional que é acessar o outro arquivo durante 
a elaboração da fórmula. 
 
 
 
 
 
 
 
 
 
 
 
A sintaxe correspondente a uma referência em outra pasta de trabalho que está 
aberta é: 
 
'[Nome da pasta de trabalho]Nome da Planilha'!Intervalo 
 
Quando finalizar a digitação de uma fórmula que referencie intervalos 
de outras planilhas, tecle ENTER para concluir. Nunca clique 
novamente na guia onde está sua fórmula, pois, a referência que 
estava correta pode não funcionar. 
 
• Utilize CTRL + TAB para alternar entre arquivos abertos sem sair 
do Excel; 
• Para visualizar todos os arquivos abertos simultaneamente clique 
na guia Exibição, grupo Janela, clique no botão Organizar Tudo e 
escolha a opção Lado a lado. 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
6 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
3. Intervalos nomeados e fórmulas nomeadas 
 
A utilização de Intervalos nomeados e fórmulas nomeadas, é uma das 
evidências mais claras do uso de recursos avançados no Microsoft Excel. Fazer 
uso desse tipo de ferramenta, facilita muito o desenvolvimento e a interpretação 
de fórmulas mais complexas, além de poder ser gerenciados com facilidade 
através do Gerenciador de Nomes (acionado pela guia Fórmulas ou pelo atalho 
CTRL + F3). 
 
 
3.1 Nomeando células e intervalos 
 
Para nomear uma célula ou um intervalo, de forma rápida e fácil, basta utilizar 
a Caixa de nomes, localizada à esquerda da Barra de fórmulas. 
 
As etapas são muito simples, basta selecionar a célula ou o intervalo desejado, 
clicar na Caixa de nomes, digite o nome desejado e tecle ENTER. O Excel não 
diferencia maiúsculas de minúsculas nas definições de nomes. Não é possível 
atribuir nomes que se pareçam com endereços de células, como A1, C200, etc. 
 
O seguinte exemplo atribui o nome Sapato à célula C2, que representa o preço 
de um determinado Sapato: 
 
 
 
 
 
Após a criação dos vínculos entre dois ou mais arquivos, é importante 
NÃO alterar os locais onde esses arquivos estão salvos e nem seus 
nomes, pois caso essa regra não seja cumprida, os vínculos serão 
perdidos, e as fórmulas deixarão de funcionar corretamente. 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
7 
 
 
 
 
Para usar um nome criado dentro de uma fórmula, simplesmente comece a 
digitá-lo no momento da definição do intervalo nomeado. Na lista suspensa, 
selecione o nome desejado: se ele já estiver selecionado, complete-o 
pressionando a tecla TAB, ou dê um duplo-clique nele. 
 
 
 
Para ser exibido a relação de nomes já criados na sua pasta de trabalho, basta 
pressionar a tecla F3. 
 
Para atribuir uma grande quantidade de nomes de uma só vez, aproveite os 
rótulos existentes nas células vizinhas aos intervalos que serão nomeados: 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
8 
 
 
1. Selecione o intervalo que deseja nomear, incluindo os rótulos de linha ou 
coluna; 
 
2. Na guia Fórmulas, grupo Nomes Definidos, clique em Criar a partir da 
Seleção; 
 
3. Na caixa de diálogo Criar Nomes a Partir da Seleção, indique a localização 
que contém os rótulos marcando a caixa de seleção Linha superior, Coluna 
esquerda, Linha inferior ou Coluna direita; 
 
4. Clique em OK para concluir. 
 
Veja o seguinte exemplo, no qual há uma tabela de valores, com rótulos de 
grupos de produtos na coluna B e meses do ano na linha 3: 
 
 
 
Após a criação de nomes a partir da seleção (tabela toda), serão criados 
automaticamente os seguintes nomes: 
 
• Janeiro, Fevereiro, Março, Abril, Maio e Junho, referindo-se aos 
números abaixo de cada rótulo de mês; 
 
• Camisa, Calça, Bermuda, Meia e Cueca, referindo-se aos números à 
direita de cada rótulo de produto; 
 
• Produtos, referindo-se a todo o intervalo numérico, compreendido pelas 
linhas abaixo e colunas à direita. 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
9 
 
 
Para incluir, excluir ou alterar algum nome criado na sua pasta de trabalho, 
basta acessar Fórmulas no menu principal, em seguida selecionar no sub menu 
Nomes Definidos, escolher a opção Gerenciador de Nomes. Também é 
possível acessar essa opção através da tecla de atalho Ctrl + F3. 
 
 
 
 
4. Funções matemáticas e estatísticas 
 
O Microsoft Excel possui inúmeras funções matemáticas e estatísticas que estão 
à disposição dos seus usuários, porém, as mais usadas são as seguintes: 
 
• SOMA, MÉDIA, MÁXIMO, MÍNIMO para estatísticas básicas; 
 
• CONT.VALORES, CONT.NÚM, CONTAR.VAZIO para realizar contagens 
diversas. 
 
 
 
 
 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
10 
 
 
 
 
 
 
 
 
 
 
 
4.1 Funções estatísticas condicionais 
 
As funções que realizam suas operações com base em um ou mais critério: 
 
• 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: 
 
• 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: 
 
 
 
 
 
 
 
 
Ao analisar a 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 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
11 
 
 
5. Funções condicionais 
 
Presentes diariamente na rotina dos usuários do Microsoft Excel, as funções 
condicionais são recursos extremamente úteis e indispensáveis durante a 
criação de Painéis de Controles Interativos. Por isso, iremos relacionar as 
funções mais importantes a seguir: 
 
 
5.1 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(A1>A2;A3*A4;0) 
 
A função SE também pode ser usada para escolher intervalos: 
 
=SOMA(SE(A1>0; B:B; C:C)) 
 
 
5.2 Função auxiliar E 
 
A função E 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; ...) 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________12 
 
 
Veja um exemplo: 
 
=SE(E(A1>0; A2>0); B1*B2; "") 
 
 
5.3 Função auxiliar OU 
 
A função OU 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(A1>0; A2>0); B1/B2; "") 
 
 
5.4 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(A1>0; A2>0); B1/B2; "") 
 
 
 
 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
13 
 
 
 
5.5 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) 
 
 
5.6 Função SE aninhada 
 
A palavra aninhada significa “uma dentro da outra”, por isso, quando 
mencionamos a Função SE aninhada estamos nos referindo a uma fórmula 
contendo duas ou mais funções SE, uma dentro da outra, conforme estrutura a 
seguir: 
 
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: 
 
Se a idade de uma pessoa na célula A1 for menor que 13 a classificação será 
“criança”, se a idade estiver entre 13 e 17 a classificação será “adolescente” e 
se a idade for maior que 17 será considerado “adulto. 
 
Ou seja, a fórmula seria: 
 
=SE(A1<13;”criança”;SE(A1<18;”adolescente”;”adulto”)) 
 
 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
14 
 
 
 
 
 
 
 
 
 
 
 
 
6. Funções de procura e referência 
 
As funções que se enquadram na categoria de procura e referências, retornam 
informações em uma determinada matriz, com base em critérios de buscas. 
 
Podemos citar o exemplo de uma tabela de preços, onde através do nome do 
produto, conseguimos retornar seu respectivo valor de venda. 
 
 
6.1 Função PROCV 
 
Essa função leva como base 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]) 
 
 
 
 
 
 
 
 
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 
 ________________________________________________________________________ 
15 
 
 
6.2 Função PROCH 
 
Essa função leva como base 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]) 
 
 
6.3 Função PROC 
 
Essa função leva como base 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
]) 
 
 
 
 
 
 
 
 
6.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. 
 
Nesse caso, o vetor de procura precisa obrigatoriamente estar 
classificado em ordem crescente. 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
16 
 
 
• 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: 
 
 
 
Na imagem anterior, existe um intervalo com nomes de cores. Na célula F2, 
está escrito o nome de uma das cores e a célula F3 contém a fórmula que 
determina a posição relativa da região escolhida na lista de regiões: 
 
=CORRESP(F2;C3:C8;0) 
 
 
6.5 Função ÍNDICE 
 
Essa função retorna informação de uma célula 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]) 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
17 
 
 
• Exemplos: 
 
 
 
As posições desejadas de linha e coluna estão escritas nas células H3 e H4. A 
matriz bidimensional de números está no intervalo B3:E12. A fórmula em H6 
traz o valor que se encontra nas posições de linha e coluna especificada: 
 
=ÍNDICE(B3:E12;H3;H4) 
 
6.6 Funções ÍNDICE e CORRESP usadas em conjunto 
 
A aplicação dessas duas funções simultaneamente, possibilita uma busca 
avançada de um resultado em uma matriz, em decorrência do posicionamento 
das variáveis utilizadas como referências, no cabeçalho e coluna à esquerda 
dessa mesma matriz. 
 
• Exemplo: 
 
Observe a tabela com alunos escritos na primeira coluna e matérias escritos na 
primeira linha: 
 
 
 
 
 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
18 
 
 
 
 
A célula D4 contém o nome do aluno que se deseja localizar e a célula D5 indica 
a matéria 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. 
 
Para encontrar o resultado desejado, use a função ÍNDICE, identificando a 
matriz de notas. Como segundo argumento (posição da linha), use CORRESP 
para localizar a posição que o aluno (D4) ocupa na lista de alunos da coluna G. 
Como terceiro argumento (posição da coluna), use CORRESP para localizar a 
posição que a matéria (D5) ocupa na lista de matérias da linha 4. 
 
Solução com várias fórmulas: 
 
• A célula E4 contém a posição do aluno (D4) na lista de alunos (G5:G14): 
 
 =CORRESP(D4;G5:G14;0) 
 
• A célula E5 contém a posição da matéria (D5) na lista de meses (H4:M4): 
 
 =CORRESP(D5;H4:M4;0) 
 
• Finalmente, a célula D8 poderá obter o valor na matriz de números 
(H5:M14), em função das posições de linha e coluna calculadas 
anteriormente: 
 
 =ÍNDICE(H5:M14;E4;E5) 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
19 
 
 
Solução com uma única fórmula, usando funções aninhadas: 
 
• Em D10, a funçãoÍNDICE precisará da matriz de números (H5:M14), 
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(H5:M14;CORRESP(D4;G5:G14;0);CORRESP(D5;H4:M4;0)) 
 
 
 
 
6.7 Função DESLOC 
 
Retorna uma referência para um intervalo, que é um número especificado de 
linhas e colunas de uma célula ou intervalo de células. A referência retornada 
pode ser uma única célula ou um intervalo de células. Você pode especificar o 
número de linhas e de colunas a serem retornadas. 
 
• Sintaxe: 
 
 =DESLOC(ref; lins; cols; [altura]; [largura]) 
 
 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
20 
 
 
• Argumentos: 
 
• Ref: Obrigatório. A referência da qual você quer basear o deslocamento. A 
referência deve ser de uma célula ou intervalo de células adjacentes. Caso 
contrário, DESLOC retornará #VALOR! como valor de erro. 
 
 
• Lins: Obrigatório. O número de linhas, acima ou abaixo, a que se deseja 
que a célula superior esquerda se refira. Usar 5 como o argumento de 
linhas, especifica que a célula superior esquerda na referência está cinco 
linhas abaixo da referência. Lins podem ser positivas (que significa abaixo 
da referência inicial) ou negativas (acima da referência inicial). 
 
• Cols: Obrigatório. O número de colunas, à esquerda ou à direita, a que se 
deseja que a célula superior esquerda do resultado se refira. Usar 5 como 
o argumento de colunas, especifica que a célula superior esquerda na 
referência está cinco colunas à direita da referência. Cols pode ser 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. Altura deve ser um número positivo. 
 
• Largura Opcional. A largura, em número de colunas, que se deseja para a 
referência fornecida. Largura deve ser um número positivo. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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 
 ________________________________________________________________________ 
21 
 
 
 
• Exemplo: 
 
No caso a seguir deseja-se somar um intervalo de números que está deslocado 
de E4 em 6 linhas abaixo e 2 colunas à direita. O novo intervalo possui 4 células 
de altura por 3 células de largura: 
 
 
 
A soma na célula E15 será: 
 
=SOMA(DESLOC(E4;6;2;4;3)) 
 
 
6.7.1 Usando DESLOC para obter intervalo deslocado 
 
Observe no banco de dados abaixo com as regiões brasileiras, capitais, e 
informações da população de acordo com cada região: 
 
 
 
 
 
 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
22 
 
 
 
 
 
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 
Masculina/Feminina e a menor população Urbana/Rural, dentro da região 
escolhida e seus estados correspondentes. 
 
Se fosse uma soma, a tarefa seria facilmente resolvida com a função SOMASE. 
Mas, neste caso, são máximos. 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 masculina da região escolhida (célula C7) 
será obtida pela função MÁXIMO: 
 
=MÁXIMO(intervalo) 
 
O intervalo de valores de População Masculina, porém, deverá ser descoberto 
no banco de dados e será deslocado para baixo (partindo da célula G5), 
conforme a região e quantidade de estados que compõem a região: 
 
=MÁXIMO(DESLOC(G5; núm_linhas; 0; altura; 1)) 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
23 
 
 
 
Pela fórmula anterior: 
 
• O deslocamento será a partir da célula G5 (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; 
 
• 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(G5;CORRESP(C5;E6:E32;0);0;CONT.SE(E6:E32;C5
);1)) 
 
Da mesma forma, a maior população feminina da região escolhida (célula C10) 
será obtida pela fórmula: 
 
=MÁXIMO(DESLOC(H5;CORRESP(C5;E6:E32;0);0;CONT.SE(E6:E32;C5
);1)) 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
24 
 
 
 
O menor número da população urbana da região (célula C13) será calculado de 
maneira semelhante: 
 
=MÍNIMO(DESLOC(I5;CORRESP(C5;E6:E32;0);0;CONT.SE(E6:E32;C5
);1)) 
 
O menor número da população rural da região (célula C16) será calculado de 
maneira semelhante: 
 
=MÍNIMO(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 (G6:G32): 
 
CORRESP(C7;G6:G32;0) 
 
Em seguida, essa posição será usada na função ÍNDICE para obter o estado 
correspondente: 
 
=ÍNDICE(F6:F32;CORRESP(C7;G6:G32;0);0) 
 
Utilizando a mesma analogia, as fórmulas das células C11 C14 e C17 ficarão 
da seguinte forma: 
 
C11 
=ÍNDICE(F6:F32;CORRESP(C10;H6:H32;0);0) 
 
C14 
=ÍNDICE(F6:F32;CORRESP(C13;I6:I32;0);0) 
 
C17 
=ÍNDICE(F6:F32;CORRESP(C16;J6:J32;0);0) 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
25 
 
 
 
A planilha finalizada, com escolha da região Nordeste ficará assim: 
 
 
 
 
6.7.2 Usando DESLOC para recuperar uma célula numa 
matriz 
 
Também podemos utilizar a função DESLOC para retornar o valor de apenas 
uma célula, nesse caso informaremos o número de linhas e colunas a serem 
deslocados, partindo do canto superior esquerdo da matriz. 
 
 
Acompanhe o exemplo a seguir: 
 
 
 
 
 
 
 
 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
26 
 
 
 
 
Para esse nosso exemplo iremos definir a célula C5 como sendo a nossa 
referência, a matriz será definida pelo intervalo D6:O20. Para definir o 
deslocamento da função, na célula D3, deverá ser digitado o número da linha 
e, na célula E3, o número da coluna. Os dois últimos argumentosdessa função 
poderá ser omitido, pois o intervalo é de apenas uma célula. 
 
A célula F3 conterá a fórmula que traz o valor que se encontra na linha e coluna 
especificada: 
 
=DESLOC(C5;D3;E3) 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
27 
 
 
6.7.3 Usando DESLOC para criar intervalos dinâmicos 
 
Criar intervalos dinâmicos é umas das principais aplicações para a função 
DESLOC, com a utilização desse recurso, suas fórmulas continuarão 
funcionando perfeitamente mesmo quando novas informações são inseridas no 
seu banco de dados. 
 
Acompanhe o exemplo abaixo: 
 
 
 
A coluna C contém algumas fórmulas que calculam valores diretamente dos 
dados da tabela: 
 
 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
28 
 
 
As fórmulas exibidas na coluna C estão funcionando perfeitamente, e continuará 
assim mesmo que novas informações sejam inseridas no meio da base de dados, 
porém, caso novas informações sejam inseridas no final da base de dados, essas 
fórmulas não considerarão tais registros. 
 
Por isso, a função DESLOC resolve esse tipo de problema, criando intervalos 
dinâmicos, que acompanham o crescimento dos seu banco de dados. 
 
 
No exemplo da planilha acima, você criará três intervalos nomeados: Data, 
Produto e Valor, correspondentes às colunas do banco de dados. 
 
1. Na guia Fórmulas, grupo Nomes Definidos, clique no botão 
Gerenciador de Nomes. 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(Planilha1!$F$4;1;0;Planilha1!$F:$F;1) 
 
Os argumentos para a função DESLOC são: 
 
• Plan1!$F$4: A célula de referência está na planilha Plan1, célula F4 
(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(Plan1!F:F): Essa função calcula a quantidade de 
valores numéricos da coluna F. Isso significa a quantidade de linhas de 
altura; 
 
• 1: O novo intervalo terá 1 coluna de largura. 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
29 
 
 
3. Da mesma forma, crie o nome Produto com a seguinte definição de 
intervalo: 
 
=DESLOC(Planilha1!$G$4;1;0;Planilha1!$F:$F;1) 
 
4. Crie também o nome Valor com o seguinte intervalo dinâmico: 
 
=DESLOC(Planilha1!$H$4;1;0;Planilha1!$F:$F;1) 
 
Se você observar, apenas a célula de referência muda, 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: 
 
 
 
Agora você já pode acrescentar novos dados na sua base e acompanhar a 
alteração das informações criadas com intervalos dinâmicos. 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
30 
 
 
 
 
 
 
 
 
 
 
 
 
 
6.8 Função INDIRETO 
 
Retorna a referência especificada por uma cadeia de texto. As referências são 
imediatamente avaliadas para exibir seu conteúdo. Use INDIRETO quando 
quiser mudar a referência a uma célula em uma fórmula sem mudar a própria 
fórmula. 
 
• Sintaxe: 
 
=INDIRETO(texto_ref, [a1]) 
 
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. 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
31 
 
 
No exemplo abaixo, você verá uma tabela com vinte e seis estados mais o 
Distrito Federal e suas respectivas populações por sexo: 
 
 
 
O objetivo será escolher um Estado na célula C5 e um Sexo na célula C7. As 
células C9 deverá conter fórmula que calcule a população total do Estado 
escolhido. A célula C11 deverá trazer a população na intersecção da linha do 
estado escolhido com a coluna do sexo escolhido. 
 
Para resolver essa questão, é necessário seguir os passos adiante: 
 
1. Crie nomes automaticamente para cada intervalo da tabela. Selecione o 
intervalo de F5:H32. 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 C9, preencha a fórmula que calcula a soma da população para o 
Estado escolhido em C5: 
 
=SOMA(INDIRETO(C5)) 
 
4. Na célula C11, preencha a fórmula que calcula a população na intersecção 
da linha do estado escolhido com a coluna do sexo escolhido. 
 
=DESLOC(F5;CORRESP(C5;F6:F32;0);CORRESP(C7;G5:H5;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 
 
 
7. Funções de texto 
 
As funções de texto permitem que você altere a maneira como a informação de 
uma determinada célula é exibida, esse recurso é muito utilizado para tratar 
informações do banco de dados, que em muitos casos, não estão dispostas da 
maneira que gostaríamos. 
 
A seguir estudaremos brevemente as principais funções de texto. 
 
 
7.1 Concatenação de texto 
 
A função CONCATENAR é utilizada para unir informações de duas ou mais 
células em uma única célula. 
 
Sintaxe: 
 
CONCATENAR(texto1; texto2; texto3;...) 
 
Exemplos: 
 
• =CONCATENAR(A1;"— ";A5); 
 
Ou então: 
 
• = A1&" — "&a5 (usando o operador de concatenação “&”). 
 
 
7.2 Função REPT 
 
A função REPT repete o texto um determinado número de vezes. Utilize essa 
função para preencher uma célula com um número de repetições de uma cadeia 
de texto.: 
 
=REPT(texto, núm_vezes) 
 
Exemplo: =REPT("*_"; 8) retorna o texto *_*_*_*_*_*_*_*_. 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
34 
 
 
7.3 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(“Instituto do Excel”) retorna o texto 
INSTITUTO DO 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(“Instituto do Excel”) retorna o texto 
instituto do excel. 
 
A função a seguir retorna o mesmo texto com cada palavra iniciando com letra 
maiúscula: 
 
PRI.MAIÚSCULA(texto) 
 
• Exemplo: =PRI.MAIÚSCULA(“instituto do excel”) retorna o texto 
Instituto Do Excel. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 Fórmulas e Funções Avançadas para criação de Dashboards no Excel 
 ________________________________________________________________________ 
35 
 
 
7.4 Extração de texto 
 
A função adiante extrai os n primeiros caracteres em texto: 
 
=ESQUERDA(texto; n) 
 
• Exemplo: =ESQUERDA("Instituto do Excel"; ) retorna o texto 
Instituto.A função a seguir extrai os n últimos caracteres em texto: 
 
=DIREITA(texto; n) 
 
• Exemplo: =DIREITA("Instituto do 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("Instituto do Excel";10;2) retorna o texto 
do.