Excel Módulo II

Microsoft Excel 365
2º Módulo - Intermediário
Sobre este Curso

Descubra na prática porque conhecer o
Excel e aplicar da forma correta os
processos é a melhor forma para
otimizar sua rotina.
Autoria
Gabriel Setubal Duo
Kevin Macaulay de Oliveira Lorena
_____________________________________________________________________________
APRESENTAÇÃO
O MATERIAL
Este material foi construído pensando em seu processo de aprendizagem. Nele você encontrará
dicas importantes para aprimorar seus conhecimentos sobre o Microsoft Excel 365.
O CURSO
Este curso, tem como objetivo dar sequência em demonstrar as ferramentas que são de uso
crucial no ambiente corporativo.
Serão abordados assuntos sobre Técnica de Intervalo, Funções Estatística, Funções Data,
Funções Lógica, Funções Pesquisa Referência, Funções Texto e Gráficos. Ao final do seu
treinamento, você estará capacitado a realizar as tarefas mais comuns em um ambiente
empresarial, além de criar planilhas eletrônicas que tragam resultados mais rápidos e eficaz,
analisar informações e apresentar relatórios de forma sofisticada.
APRENDIZAGEM
A equipe Clarify pesquisou amplamente as necessidades no uso do Excel nos diversos setores
de negócios, resultando nas atividades que são ministradas ao longo do Curso. É importante que
essas sejam realizadas com dedicação e interesse, para que se alcance o aprendizado desejado.
PRÉ-REQUISITOS
Ter realizado o treinamento de Excel FUNDAMENTOS ou ter conhecimentos equivalentes.
Indicamos o treinamento de Excel Fundamentos da CLARIFY, para melhor aproveitamento e
contextualização dos tópicos apresentados neste treinamento, já que nossos cursos são
conectados módulo a módulo.
2
_____________________________________________________________________________
Sumário
Sobre este Curso ....................................................................................................................... 1
Autoria....................................................................................................................................... 1
APRESENTAÇÃO............................................................................................................................. 2
O MATERIAL .............................................................................................................................. 2
O CURSO .................................................................................................................................... 2
APRENDIZAGEM ........................................................................................................................ 2
PRÉ-REQUISITOS ........................................................................................................................ 2
CAPÍTULO 01 – INTERVALOS ......................................................................................................... 7
INTRODUÇÃO ............................................................................................................................ 7
NOMES DEFINIDOS.................................................................................................................... 7
CONFIGURANDO NOMES ...................................................................................................... 7
GERENCIANDO NOME ......................................................................................................... 10
TABELAS................................................................................................................................... 11
FORMATANDO COMO TABELA............................................................................................ 12
FERRAMENTAS DE TABELA .................................................................................................. 13
ALTERANDO NOME DE UMA TABELA.................................................................................. 13
ENTENDENDO O COMPORAMENTO DA TABELA ................................................................ 14
CAPÍTULO 02 – IMPORTANDO DADOS ........................................................................................ 19
TEXTO PARA COLUNAS ............................................................................................................ 19
TEXTO DELIMITADO ............................................................................................................ 19
TEXTO LARGURA FIXA ......................................................................................................... 21
DADOS EXTERNOS ................................................................................................................... 22
DE TEXTO ............................................................................................................................. 22
DO ACCESS........................................................................................................................... 23
DA WEB ............................................................................................................................... 24
XML...................................................................................................................................... 25
CONSOLIDAR DADOS ............................................................................................................... 26
ETAPAS DE CONSOLIDAÇÃO ................................................................................................ 26
CAPITULO 03 - FUNÇÕES DE TEXTO ............................................................................................ 29
INTRODUÇÃO .......................................................................................................................... 29
CONCAT ................................................................................................................................... 29
3
_____________________________________________________________________________
ARRUMAR................................................................................................................................ 30
MINÚSCULA............................................................................................................................. 30
MAIÚSCULA ............................................................................................................................. 31
PRI.MAIÚSCULA ....................................................................................................................... 32
LOCALIZAR ............................................................................................................................... 32
PROCURAR............................................................................................................................... 33
FUNÇÕES DE EXTRAÇÃO ......................................................................................................... 34
ESQUERDA ........................................................................................................................... 34
EXT.TEXTO ........................................................................................................................... 35
DIREITA ................................................................................................................................ 36
NÚM.CARACT .......................................................................................................................... 37
SUBSTITUIR .............................................................................................................................. 38
TEXTO ...................................................................................................................................... 40
CAPÍTULO 04 – FUNÇÕES DE DATA ............................................................................................. 42
INTRODUÇÃO .......................................................................................................................... 42
DIA ........................................................................................................................................... 42
MÊS.......................................................................................................................................... 42
ANO ......................................................................................................................................... 43
HORA ....................................................................................................................................... 43
MINUTO................................................................................................................................... 44
SEGUNDO ................................................................................................................................ 44
DIA.DA.SEMANA ...................................................................................................................... 45
NÚMSEMANA .......................................................................................................................... 46
DATA........................................................................................................................................ 47
AGORA() .................................................................................................................................. 48
HOJE() ...................................................................................................................................... 49
DATAM .................................................................................................................................... 49
FIMMÊS ................................................................................................................................... 50
DIATRABALHO.INTL ................................................................................................................. 51
DIATRABALHOTOTAL.INTL....................................................................................................... 53
CAPITULO 05 - FUNÇÕES ESTASTÍSTICAS .................................................................................... 56
INTRODUÇÃO .......................................................................................................................... 56
MAIOR ..................................................................................................................................... 56
MENOR .................................................................................................................................... 57
4
_____________________________________________________________________________
ORDEM.EQ .............................................................................................................................. 58
SOMASES, MÉDIASES .............................................................................................................. 59
CONT.SES ................................................................................................................................. 61
CAPÍTULO 06 – FUNÇÕES LÓGICAS ............................................................................................. 64
INTRODUÇÃO .......................................................................................................................... 64
SE ANINHADO .......................................................................................................................... 64
E ............................................................................................................................................... 66
OU ........................................................................................................................................... 67
CAPÍTULO 07 – FUNÇÕES PESQUISA E REFERÊNCIA ................................................................... 69
INTRODUÇÃO .......................................................................................................................... 69
PROCV ..................................................................................................................................... 69
PROCV POR EXATIDÃO ........................................................................................................ 69
PROCV POR APROXIMAÇÃO ................................................................................................ 71
PROC ........................................................................................................................................ 72
CORRESP .................................................................................................................................. 77
CORRESP EXATIDÃO ............................................................................................................ 78
CORRESP MAIOR DO QUE ................................................................................................... 79
CORRESP MENOR DO QUE .................................................................................................. 80
PROCV COM SE/ PROCV COM CORRESP ................................................................................. 81
PROCV COM CHAVE ................................................................................................................ 83
CAPÍTULO 08 – MINI GRÁFICOS .................................................................................................. 84
INTRODUÇÃO .......................................................................................................................... 84
INSERIR E EDITAR .................................................................................................................... 84
CAPÍTULO 09 – GRÁFICOS ........................................................................................................... 87
INTRODUÇÃO .......................................................................................................................... 87
ESCALA LOGARÍTIMICA............................................................................................................ 87
EIXO SECUNDÁRIO................................................................................................................... 89
GRÁFICO DE COMBINAÇÃO COM EIXO SECUNDÁRIO ............................................................ 90
GRÁFICO DE RADAR................................................................................................................. 90
GRÁFICO DE BOLHAS ............................................................................................................... 91
NOVOS GRÁFICOS ................................................................................................................... 91
GRÁFICO DE HISTOGRAMA ..................................................................................................... 92
GRÁFICO DE PARETO ............................................................................................................... 92
GRÁFICO DE CASCATA ............................................................................................................. 93
5
_____________________________________________________________________________
GRÁFICO DE FUNIL .................................................................................................................. 93
GRÁFICO MAPA DE ÁRVORE ................................................................................................... 93
GRÁFICO DE EXPLOSÃO SOLAR ............................................................................................... 94
6
_____________________________________________________________________________
CAPÍTULO 01 – INTERVALOS
INTRODUÇÃO
Neste capítulo, são exibidas as diferenças entre as técnicas de intervalos normal de dados e
Tabelas.
Após concluir este capítulo, você estará apto para:
1. Usar Nomes Definidos em fórmulas;

2. Comparar a técnica anterior com ferramenta de Tabela.
NOMES DEFINIDOS
Um Nome Definido, é um ‘apelido’ dado para uma célula ou um conjunto de células, também
conhecido como intervalo. Quando você faz uso desta técnica, facilita o entendimento e a
manutenção das fórmulas.
Depois de adotar a prática de uso de nomes na pasta de trabalho, poderá atualizar, auditar e
gerenciar esses nomes de forma simples. Observe a tabela a seguir. Ela apresenta um
comparativo entre diferentes aplicabilidades do dia a dia, que podem ser substituídas pelo uso
de Nomes Definidos.
Tipo Referência Clássica Nomes Definidos

Referência =SOMA(A2:A20) =SOMA(DESPESAS)
Constante =PROC(A10; F15:L30) =PROC(Preço; BaseDeDados)
Fórmula =PROC(A2;F10;F20)*3,42 =PROC(Preço;BaseDeDados)*Dolar
Tabela =A2:F20 =BaseDeJaneiro
CONFIGURANDO NOMES
Para configurar nomes, separamos uma planilha contendo três tabelas disponíveis. Para isso,
faça o seguinte:
1. Ative a planilha Intervalo Nomeado;

2. Você definirá um nome para cada coluna/célula colorida na planilha;
3. Selecione o intervalo C3:C9;
4. Em seguida, clique na guia Fórmulas;
5. No grupo Nomes Definidos, clique em Definir Nome;
7
_____________________________________________________________________________
6. Observe que a caixa Novo Nome, é apresentada;
Nessa caixa, você pode:
Nome: Criar o novo nome para célula, intervalo de células, entre outros. Saiba que, um nome
não pode ter caracteres especiais, tais como, vírgula, ponto-vírgula, espaço, asterisco (*), hífen,
entre outros.
Caracteres possíveis: _ (sublinhado), . (ponto), entre outros.
Escopo: No Escopo, você configura qual planilha usará o nome definido. Ao escolher uma
planilha específica, o nome só poderá ser usado dentro dela. Ao configurar o escopo como Pasta
de Trabalho, você poderá usar o nome definido de qualquer planilha.
Obs.
Após confirmar as configurações de nome e escopo, o Excel não permite que o escopo seja
alterado.
Comentário: Nesta área, você pode realizar a descrição do nome definido. Por exemplo, quais
são os valores que ele representa, qual o contexto de uso, entre outras que desejar.
Refere-se à: Resume o intervalo de dados ou célula usada no nome a ser definido. Você também
pode criar funções/fórmulas nesta área.
7. Digite ValorDespesas na caixa Nome;

8. Em Escopo, selecione a planilha Intervalo Nomeado;
9. Em Comentário, digite: “Representa os valores de despesas do Condomínio”.
8
_____________________________________________________________________________
10. Pressione OK;
11. Aparentemente, nada aconteceu. No entanto, internamente temos um novo nome que
já pode ser usado na planilha Intervalo Nomeado.
Você pode também atribuir o nome a uma célula usando a Caixa de Nome. Para isso, faça o
seguinte:
12. Clique na célula C10 (Subtotal);

13. Clique na Caixa de Nome e digite o novo nome. Em nosso exemplo, digite
TotalDespesas;
14. Pressione ENTER;
15. Perceba que a Caixa de Nome apresenta o nome atribuído a célula.
Obs.
Nomes Definidos pela Caixa de Nomes tem o escopo definido como Pasta de Trabalho
16. Clique na célula E3;

17. Defina o nome como FundoDeReserva;
18. Clique na célula F3;
19. Defina o nome como Total;
20. Selecione o intervalo H3:H6;
21. Defina o nome como Quantidade;
22. Selecione o intervalo K3:K6;
23. Defina o nome como Cotas;
24. Clique na célula J7;
9
_____________________________________________________________________________
25. Defina o nome como Soma;
26. Em Subtotal (C10), calcule a soma dos valores da coluna Valor;
27. Sua fórmula ficará: =SOMA(TotalDespesas);
28. O Fundo de Reserva (E3), é composto por 15% do Subtotal (B9);
29. Sua fórmula ficará: =15%*FundoDeReserva;
30. O Total (F3), é a soma entre Subtotal e Fundo de Reserva;
31. Fórmula: TotalDesespeas+FundoDeReserva
32. Total Geral, é a soma das multiplicações entre Quantidade e Cotas;
33. Fórmulas: =SOMARPRODUTO(Quantidade;Cotas);
34. Para finalizar, na coluna condomínio você deve realizar o cálculo de rateio, que consiste
na divisão proporcional do total de despesas entre os condôminos;
35. A fórmula ficará assim: =Total/Soma*Cotas, após aplicar para a primeira célula K3
arraste a fórmula para as demais;
GERENCIANDO NOME
Se em algum momento você criou um nome incorreto, deseja alterar o intervalo a que ele se
refere ou excluir um nome, você pode gerenciar os nomes criados. Para isso, você deve fazer
uso do recurso Gerenciador de Nomes. Para conhece-lo, faça o seguinte:
1. Clique na guia Fórmulas;

2. Em seguida, no grupo Nomes Definidos clique em Gerenciador de Nomes;
3. Perceba que a janela do Gerenciador de Nomes é exibida, resumindo os nomes criados;
10
_____________________________________________________________________________
4. Localize o nome desejado;

5. Clique no botão Editar, para alterar qualquer propriedade do nome (exceto o Escopo);
6. Para remover qualquer nome, clique no botão Excluir.
7. Após realizar os testes, salve as alterações realizadas na pasta de trabalho.
TABELAS
Para conhecer essa ferramenta, faça o seguinte:
1. Na planilha FORMATANDO COMO TABELA;

2. Selecione todos os valores da tabela disposta na planilha;
3. Clique na Guia Página Inicial;
4. Depois, vá ao grupo estilos e acione a ferramenta ‘Formatar como Tabela’;
11
_____________________________________________________________________________
5. Escolha um estilo disponível;
6. Poderá analisar que ele lhe trará uma pré-visualização de seus dados.
7. Para efetivar o resultado, basta clicar na opção.
FORMATANDO COMO TABELA

você acaba conhecerá um “paradigma” que oferecerá muita produtividade e facilidade na
administração de seus dados. Ao criar uma planilha, você pode gerenciar e analisar os dados
dela. Por exemplo, é possível filtrar as colunas, adicionar uma linha para TOTAIS, aplicar
formatação e muito mais.
12
_____________________________________________________________________________
Depois de criar uma tabela, as FERRAMENTAS DA TABELA se tornarão disponíveis e a guia
DESIGN será exibida. É possível usar as ferramentas dessa guia para personalizar ou editar a
tabela
FERRAMENTAS DE TABELA
A guia DESIGN só fica visível quando um dado (célula) é selecionado na tabela. Então, para
visualizar a guia contextual FERRAMENTAS DE TABELA, faça o seguinte:
1. Clique em qualquer dado da tabela;

2. Observe que a guia contextual é exibida com a barra de título;
Em Ferramentas de Tabela, é possível:

• Alterar o nome da tabela
• Expandir a área da tabela
• Transformar o intervalo atual em tabela dinâmica
• Remover dados duplicados
• Converter a tabela atual em um intervalo regular
• Inserir segmentação de dados
• Exportar os dados da tabela
• Alterar o estilo de tabela
ALTERANDO NOME DE UMA TABELA
O nome da tabela é utilizado para identificar de forma simples seus dados em uma pasta de
trabalho, ele não pode ser repetido e não pode ter espaços. Para alterar o nome de uma tabela,
você deve fazer o seguinte:
1. Clique na guia contextual DESIGN.

2. No grupo PROPRIEDADES, clique no campo NOME DA TABELA.
3. Digite TblAnáliseHoras e pressione ENTER.
13
_____________________________________________________________________________
ENTENDENDO O COMPORAMENTO DA TABELA
Nesta etapa, você aprenderá a construir soluções otimizadas utilizando da ferramenta “Formato
Tabela”. No Exemplo, você criará fórmulas de multiplicação usando Intervalo normal de dados
e Tabela. Você deve calcular o TOTAL para cada PEDIDO realizar a conversão do valor em dólar.
1. Na planilha COMPORTAMENTO DA TABELA;

2. Clique na célula I6;
3. Digite = (igual);
4. Clique no primeiro valor do campo PREÇO($) (H6);
5. Digite * (asterisco/vezes);
6. Clique no valor do campo COTAÇÃO (G3);
7. Congele a referência utilizando da técnica F4 ($G$3)
8. Sua fórmula deve estar assim: =H6*$G$3
9. Pressione ENTER;
10. Observe o resultado;
11. Espalhe os valores para as demais linhas da tabela;
12. Em seguida, vá a primeira célula de total (K6);
14
_____________________________________________________________________________
13. Digite = (Igual);
14. Clique na primeira célula da coluna Valor (R$) (I6);
15. Digite * (asterisco/multiplicação);
16. Clique na célula de quantidade (J6);
19. Arraste a fórmula para as demais linhas;
20. Veremos agora, o mesmo cálculo usando a Tabela de Dados. Clique na célula I21;
22. Clique no primeiro valor do campo VALOR ($);
23. Digite * (asterisco/vezes);
24. Clique no primeiro valor do campo COTAÇÃO;
25. Sua fórmula deve estar assim: =[@[Valor ($)]]*Tbl_Cotação[Cotação]
27. Perceba que a Tabela já executará o preenchimento das fórmulas para as demais linhas,
sem a necessidade de congelar as referências;
15
_____________________________________________________________________________
Neste outro exemplo, você deve calcular a porcentagem de participação de cada departamento
no total das despesas da empresa. Para isso, faça o seguinte:
1. Clique na célula D3, para realizar o cálculo do departamento Comercial;

3. Clique no Valor (C3);
4. Digite / (Barra de data);
5. Clique no valor do campo TOTAL DESPESAS (C13) e congela a referência.
6. Sua fórmula deverá ficar assim: =B2/$B$13;
7. Em seguida, você deve espalhar a fórmula para o restante da coluna.
Para comparar as técnicas, agora você aplicará o mesmo cálculo usando Tabela de Dados. Para
isso, faça o seguinte:
9. Clique na célula D16;
11. Clique no valor do departamento COMERCIAL (C16);
16
_____________________________________________________________________________
12. Digite / (Barra de data);
13. Clique no valor do campo TOTAL DESPESAS (C27);
14. Sua fórmula deve estar assim: =[@Valor]/Divisão[[#Totais];[Valor]];
16. Observe que a Tabela de dados espalhou a fórmula e automaticamente congelou as
referências.
17. Salve as alterações na pasta de trabalho.
17
_____________________________________________________________________________
18
_____________________________________________________________________________
CAPÍTULO 02 – IMPORTANDO DADOS
Neste capítulo, você conhecerá ferramentas que permitirão importar e administrar grande
volume de dados.
Ao final do capitulo você estará apto a utilizar as ferramentas:
1. TEXTO PARA COLUNAS

2. OBTENDO DADOS EXTERNOS
2.1. De Texto
2.2. Do Access
2.3. Da Web
2.4. XML
3. Consolidação de Dados
TEXTO PARA COLUNAS

Divide uma única coluna de texto em várias colunas, respeitando os caracteres delimitadores,
que podem ser representados por quaisquer símbolos.
TEXTO DELIMITADO
Para conhecer essa ferramenta, faça o seguinte:
1. Clique na planilha Texto Delimitado;

2. Clique na Coluna A;
3. Clique na guia Dados;
19
_____________________________________________________________________________
4. No grupo Ferramentas de Dados, clique em Texto para Colunas;
5. Na janela do assistente para conversão de texto em colunas, selecione Delimitado.
6. Clique em Avançar;
7. Na próxima etapa, em Delimitadores, selecione Ponto e vírgula;
8. Clique em Concluir.
20
_____________________________________________________________________________
TEXTO LARGURA FIXA
Divide uma única coluna de texto em várias colunas, no entanto, não tem caractere delimitador,
esse é um processo manual.
Para conhece-lo, faça o seguinte:
1. Clique na planilha Texto Largura Fixa;

2. Clique na Coluna A;
4. No grupo Ferramentas de Dados, clique em Texto para Colunas;
5. Na janela do assistente para conversão de texto em colunas, selecione Largura Fixa.
6. Clique em Avançar;
7. Na próxima etapa, você pode clicar nas medidas da régua para divisão de cada campo
para delimitar. Você pode clicar e arrastar para movimentar a divisão. Para remover,
basta um clique-duplo na linha de corte.
8. Navegue nas barras de rolagem e repita esse procedimento quando necessário. Ao final,
clique em Concluir.
21
_____________________________________________________________________________
DADOS EXTERNOS
Permite importar dados de fontes de dado externas a sua pasta de trabalho para construção de
análises.
DE TEXTO
Para realizar uma importação de dados de arquivo de texto, faça o seguinte:
1. Ative a planilha De Texto;

3. No grupo Obter Dados Externos, clique em De Texto;
4. Em seguida, na janela Importar Dados, navegue até o arquivo desejado. Em nosso

exemplo, navegaremos até o arquivo ArquivoTextoDelimitadoTab.txt localizado na
pasta do curso.
5. Depois de localizar o arquivo, clique no botão Importar;
6. Na janela do assistente, acione a seta ao lado do botão carregar e solicite a ferramenta
carregar para;
22
_____________________________________________________________________________
7. Mantenha selecionado a importação de dados no modelo ‘Tabela’;
8. Indique a importação para a planilha existente;
9. Em seguida, clique em OK;
10. Observe o resultado e salve as alterações realizadas na pasta de trabalho.
DO ACCESS
Para realizar uma importação de dados de arquivo do Access, faça o seguinte:
1. Ative a planilha Do Access;

3. No grupo Obter Dados Externos, clique em Bando de dados, e escolha Do Banco de
Dados Microsoft Access;
4. Em seguida, na janela Importar Dados, navegue até o arquivo desejado. Em nosso

exemplo, navegaremos até o arquivo ArquivoAccess.accdb localizado na pasta do curso.
5. Depois de localizar o arquivo, clique no botão Importar;
6. Selecione a tabela desejada (Para selecionar mais de uma tabela, marque a opção
Habilitar a seleção de várias tabelas);
23
_____________________________________________________________________________
7. Selecione a tabela Cidades Brasileiras

carregar para;
9. Mantenha selecionado a importação de dados no modelo ‘Tabela’;
10. Indique a importação para a planilha existente;
11. Em seguida, clique em OK;
DA WEB
1. Para importar os dados de uma página da WEB, faça o seguinte:
2. Ative a planilha Da Web;
4. No grupo Obter Dados Externos, clique em Da Web;
5. Na barra de Endereço, digite http://www.guialog.com.br/salarios.htm
6. Clique no botão OK;
7. Escolha a tabela desejada, em nosso caso, será a Table0;
carregar para;
9. Repita os processos semelhantes as demais importações;
10. Observe o resultado e salve as alterações na pasta de trabalho.
Obs.
Para importar dados da Web as informações devem estar estruturadas como uma tabela no site.
24
_____________________________________________________________________________
XML
Para realizar uma importação de dados de arquivo XML, faça o seguinte:
1. Ative a planilha XML;

3. No grupo Obter Dados Externos, clique em De Arquivo
4. Em seguida, clique na opção Da Importação de Dados XML;
5. Selecione o arquivo XML;

6. Caso o Excel envie uma mensagem de erro no XML, clique em OK para que ele ajuste os
dados.
7. Selecione a célula para o armazenamento dos dados e clique em OK;
25
_____________________________________________________________________________
CONSOLIDAR DADOS
Para resumir e relatar resultados de dados em planilhas separadas você pode consolidar os
dados em uma única planilha (ou planilha mestra). As planilhas consolidadas podem estar na
mesma pasta de trabalho que a planilha mestra ou em outras pastas de trabalho.
ETAPAS DE CONSOLIDAÇÃO
Em cada planilha que contém os dados que você deseja consolidar, configure os dados seguindo
estes procedimentos:
1. Verifique se cada intervalo de dados está no formato de lista: cada coluna tem um rótulo na
primeira linha e contém informações semelhantes, e não existem linhas ou colunas em branco
dentro da lista.
Coloque cada intervalo em uma planilha separada, mas não insira nenhum intervalo na planilha
onde você planeja colocar a consolidação.
2. Na planilha mestre, clique na célula superior esquerda da área em que você deseja que os
dados consolidados sejam exibidos.
Observação:
Para evitar substituir dados existentes na planilha de destino pelos dados que você está
consolidando, verifique se você deixou células suficientes à direita e abaixo dessa célula para os
dados consolidados.
3. Na guia Dados, vá até o grupo Ferramentas de Dados e clique em Consolidar.
4. Na caixa Função, clique na função resumo que você deseja que o Excel utilize para consolidar
os dados.
26
_____________________________________________________________________________
5. Na caixa Referência, clique no botão Recolher Caixa de Diálogo para selecionar os dados na
planilha.
6. Clique na planilha que contém os dados que deseja consolidar, selecione-os e clique no botão
Expandir Caixa de Diálogo.
7. Na caixa de diálogo ‘Consolidar’, clique em Adicionar e repita as etapas 6 e 7 para adicionar

todos os intervalos desejados.
7.1 Referência '2016'!$A$1:$D$7, '2017'!$A$1:$E$9, '2018'!$A$1:$E$7;
8. Usar rótulos na:
8.1 Selecionar Linha superior;

8.2 Coluna esquerda;
9. Clique OK
27
_____________________________________________________________________________
10. Observe os resultados e salve a pasta de trabalho
28
_____________________________________________________________________________
CAPITULO 03 - FUNÇÕES DE TEXTO
INTRODUÇÃO
Nesta etapa, você aprenderá a manipular os dados de texto em suas tabelas. Para modelagem
e organização de bases.
Após concluir este capítulo, você estará apto para usar as seguintes funções:
1. ARRUMAR()
2. MAIÚSCULA()
3. MINÚSCULA()
4. PRI.MAIÚSCULA()
5. CONCAT()
Com a base nas informações disponíveis você irá ajustar os textos e uni-los para criar um campo
de endereço.
CONCAT
A função CONCAT agrupa até 255 cadeias de texto em um resultado. Os itens agrupados podem
ser textos, números, referências de células ou uma combinação desses itens.
Sintaxe: CONCAT(texto1; texto2; texto3; ...)
1. Ative a planilha CONCAT;
4. Digite CONCAT;
5. Tecle TAB para completar a função;
6. Clique na célula B6 (1º Critério);
7. Digite ; (ponto-vírgula);
8. Clique na célula C6 (2º Critério);
9. Sua função deve estar assim:
=CONCAT(B6;C6)
29
_____________________________________________________________________________
12. Poderá observar que os valores de Cidade e Endereço estão unidos, para poder
contornar isso teremos que concatenar um terceiro valor, que será um caractere
espaço.
13. Então nossa sintaxe ficará assim: =CONCAT(B6;" ";C6)
Todo texto referenciado em uma fórmula deverá estar entre aspas (“ “).
14. Com a alça de preenchimento aplique a fórmula para as demais linhas.
ARRUMAR
Remove todos os espaços do texto exceto os espaços únicos entre palavras. Use ARRUMAR() no
texto que recebeu de outro aplicativo que pode ter espaçamento irregular.
SINTAXE : ARRUMAR(texto)
1. Ative a planilha ARRUMAR, MINÚSCULA, MAÍUSCULA;

2. Clique na célula C5;
4. Digite ARRU;
6. Clique na célula B5;
7. Sua função deve estar assim: =ARRUMAR(B5)
8. Pressione ENTER;
MINÚSCULA
Converte o texto em minúsculas.
30
_____________________________________________________________________________
SINTAXE :MINÚSCULA (texto)
Ainda na planilha FUNÇÕES DE TEXTO, faça o seguinte:

3. Digite MI;
6. Sua função deve estar assim: =MINÚSCULA(B5)
7. Pressione ENTER;
MAIÚSCULA
Converte o texto em maiúsculas.
SINTAXE: MAIÚSCULA (texto).
Em nosso exemplo, você deve ajustar os dados do campo Endereço. Para isso faça o seguinte:
Faça o seguinte:

3. Digite MAIÚ;
4. Abrirá uma caixa com diversas referencias (Funções; Tabelas; Intervalos Nomeados);
7. Sua função deve estar assim: =MAIÚSCULA(B5)
8. Pressione ENTER;
31
_____________________________________________________________________________
PRI.MAIÚSCULA
Coloca a primeira letra de uma cadeia de texto em maiúscula, e todas as outras letras do texto
depois de qualquer caractere diferente de uma letra. Converte todas as outras letras para
minúsculas.
SINTAXE: PRI.MAIÚSCULA (texto)
Ainda na planilha FUNÇÕES DE TEXTO, faça o seguinte:

3. Digite PRI;
6. Sua função deve estar assim: =PRI.MAIÚSCULA(C5)
7. Pressione ENTER;
LOCALIZAR
Retorna a posição de um caractere em um texto. Não diferencia maiúsculas de minúsculas.
Sintaxe: =LOCALIZAR(texto_procurado,no_texto,[núm_inicial])
As funções LOCALIZAR têm os seguintes argumentos:
texto_procurado Obrigatório. O texto que você deseja localizar.
no_texto Obrigatório. O texto no qual você deseja procurar o valor do argumento

texto_procurado.
núm_inicial Opcional. O número do caractere no argumento no Texto em que você deseja iniciar
a busca.
Para testá-lo, você deve fazer o seguinte:
1. Ative a planilha Localizar e Procurar;

2. Selecione a Célula D3;
3. Acione a função Localizar;
4. Para o argumento, texto_procurado: clique na célula B3;
32
_____________________________________________________________________________
5. no_texto: clique em C3;
6. núm_inicial: deixar vazio;
7. Sua sintaxe deverá ficar assim: =LOCALIZAR([@[TEXTO PROCURADO]];[@TEXTO]);
8. Pressione Enter;
PROCURAR
Retorna a posição de um caractere em um texto. Diferencia maiúsculas e minúsculas.
Sintaxe: =PROCURAR (texto_procurado,no_texto,[núm_inicial])
As funções PROCURAR têm os seguintes argumentos:
texto_procurado Obrigatório. O texto que você deseja localizar.
no_texto Obrigatório. O texto no qual você deseja procurar o valor do argumento

texto_procurado.
núm_inicial Opcional. O número do caractere no argumento no_texto em que você deseja iniciar
a busca.
1. Ative a planilha Localizar e Procurar;

2. Selecione a Célula E3;
3. Acione a função Procurar;
4. Para o argumento, texto_procurado: clique na célula B3;
5. no_texto: clique em C3;
6. núm_inicial: deixar vazio;
7. Sua sintaxe deverá ficar assim: =PROCURAR([@[TEXTO PROCURADO]];[@TEXTO]);
8. Pressione Enter;
9. Observe os resultados:
10. Salve as alterações da pasta de trabalho.
33
_____________________________________________________________________________
FUNÇÕES DE EXTRAÇÃO
As próximas funções apresentar, servem pra extrair um trecho de um texto de uma sentença
conforme necessidade.
No contexto do exemplo temos a planilha DIREITA,ESQUERDA, EXT.TEXTO que nos apresenta

informações cadastrais, todavia, os valores são dados diversos imputados em uma mesma
célula, sendo necessário a separação dos valores.
ESQUERDA
Retorna o primeiro caractere ou caracteres em uma cadeia de texto baseado no número de
caracteres especificado por você.
Sintaxe: =ESQUERDA(texto,[núm_caract])
A função ESQUERDA têm os seguintes argumentos:
Texto Obrigatório. A cadeia de texto que contém os caracteres que você deseja extrair.
Núm_caract Opcional. Especifica o número de caracteres que ESQUERDA deve extrair.
1. Ative a planilha DIREITA,ESQUERDA, EXT.TEXTO;

3. Acione a função Esquerda;
4. Para o argumento Texto: clique em B3;
5. Núm_caract: digite 2;
6. Sua sintaxe deverá ficar assim: = ESQUERDA([@[UF-MUNICÍPIO]];2)
7. Pressione Enter;
34
_____________________________________________________________________________
EXT.TEXTO
Retorna um número específico de caracteres de uma cadeia de texto, começando na posição
especificada, com base no número de caracteres especificado.
Sintaxe: =EXT.TEXTO(texto, núm_inicial,núm_caract)
A sintaxe das funções EXT.TEXTO tem os seguintes argumentos:
Núm_inicial Obrigatório. A posição do primeiro caractere que você deseja extrair no texto. O
primeiro caractere no texto possui núm_inicial1 e assim por diante.
Núm_caract Obrigatório. Especifica o número de caracteres que EXT.TEXTO deve retornar do

texto.
Para testá-la, você deve fazer o seguinte:
1. Selecione a Célula E3;

2. Acione a função EXT.TEXTO;
3. No argumento Texto: clique em C3;
4. Núm_inicial: Digite 4
5. Núm_caract(Quantidade de Caracteres): como não sabemos o maior valor de
caracteres necessário, podemos usar um valor alto neste exemplo usaremos 99;
6. Sua sintaxe deverá ficar assim: =EXT.TEXTO([@[UF-MUNICÍPIO]];4;99)
7. Pressione Enter;
35
_____________________________________________________________________________
DIREITA
Retorna o último caractere ou caracteres em uma cadeia de texto, com base no número de
caracteres especificado.
Sintaxe: =DIREITA(texto,[núm_caract])
A função DIREITA têm os seguintes argumentos:
Núm_caract Opcional. Especifica o número de caracteres que DIREITA deve extrair.
1. Ative a planilha DIREITA,ESQUERDA, EXT.TEXTO;

2. Selecione a Célula F3;
3. Acione a função Direita;
4. Na argumentação, para o argumento Texto: clique em B3;
5. Núm_caract: digite 3;
6. Sua sintaxe deverá ficar assim: =DIREITA([@CÓDIGO];3)
7. Pressione Enter;
9. No final sua base de dados, deverá apresentar-se assim:
36
_____________________________________________________________________________
NÚM.CARACT
Retorna o número de caracteres em uma cadeia de texto.
Sintaxe: =NÚM.CARACT(texto)
A sintaxe da função NÚM.CARACT tem os seguintes argumentos:
Texto - Obrigatório. O texto cujo comprimento você deseja determinar. Os espaços contam
como caracteres
No contexto usaremos a função para consultar uma base cadastral onde trabalhamos com
fornecedores PJ e PF e com base no tamanho ID(CPF/CNPJ) do Fornecedor iremos descobrir.
1. Ative a planilha NÚM.CARACT;

3. Acione a função NÚM.CARACT;
4. No argumento da função Texto: clique na célula B3;
5. Sua fórmula deve estar assim: =NÚM.CARACT([@ID]);
6. Pressione Enter;
7. Observe os resultados;
8. Agora na célula E3 iremos fazer a definição do tipo de fornecedor com base no

número de caracteres que pode ser 11 para CPFs (ou seja PF), ou 14 para CNPJs (PJ),
para isso usaremos a função SE();
9. Acione a função SE para a determinação;
10. Sua sintaxe pode ficar assim: =SE([@[NÚM.CARACT]]=11;"PF";"PJ")
37
_____________________________________________________________________________
SUBSTITUIR
Coloca novo texto no lugar de texto antigo em uma cadeia de texto. Use SUBSTITUIR quando
quiser substituir texto específico em uma cadeia de texto.
Sintaxe: =SUBSTITUIR(texto, texto_antigo, novo_texto, [núm_da_ocorrência])
A sintaxe da função SUBSTITUIR tem os seguintes argumentos:
Texto Obrigatório. O texto ou a referência a uma célula que contém o texto no qual deseja
substituir caracteres.
Texto_antigo Obrigatório. O texto que se deseja substituir.
Novo_texto Obrigatório. O texto pelo qual deseja substituir texto_antigo.
Núm_da_ocorrência Opcional. Especifica que ocorrência de texto_antigo se deseja substituir

por novo_texto. Se especificar núm_da_ocorrência, apenas aquela ocorrência de texto_antigo
será substituída. Caso contrário, cada ocorrência de texto_antigo no texto é alterada para
novo_texto.
No contexto da atividade modelo iremos fazer uma restruturação dos código de produtos, então
iremos remover todos os caracteres especiais, como “-“, “ / “ ou “.”
38
_____________________________________________________________________________
1. Ative a planilha Substituir;

2. Selecione a Célula C6;
3. Acione a função SUBSTITUIR;
4. Para o argumento Texto: Clique em B6;
5. Texto_antigo: Digite entre aspas -;
6. Novo_texto: você indicará vazio abrindo e fechando as aspas, nada deve estar entre
elas;
7. Núm_da_ocorrência: Deixar vazio
8. Sua fórmula deverá ficar assim: =SUBSTITUIR([@[ID PRODUTO]];"-";"")
9. Na célula podemos D6 podemos repetir o processo mas desta vez podemos
substituir as barras “/”;
10. Sua sintaxe deverá ficar assim: =SUBSTITUIR([@[ID PRODUTO]];"/";"")
11. Para remover ambos os caracteres podemos usar a técnica de funções
aninhadas(uma função dentro de outra), vamos até a célula E6 e acionaremos a
função substituir, com a função aberta acionaremos novamente a função substituir;
12. No argumento texto da segunda função substituir clicaremos na célula B6;
13. Texto_antigo: Digite entre aspas -;
elas;
39
_____________________________________________________________________________
15. Feche as aspas e notará que voltará para a argumentação da primeira função
substituir, a sintaxe acusará que esta no argumento texto então digite um ponto e
virgula ;
16. Informe no argumento Texto_antigo: barras entre aspas
elas;
18. Feche novamente o parênteses;
19. Sua fórmula deverá ficar assim:
=SUBSTITUIR(SUBSTITUIR([@[ID PRODUTO]];"-";"");"/";"")
20. Observe os resultados e salva as alterações na pasta de trabalho
TEXTO
A função TEXTO converte um valor numérico em texto e permite especificar a formatação de
exibição usando cadeias de formato especiais.
Sintaxe: =TEXTO(valor, formato_texto)
A sintaxe da função TEXTO tem os seguintes argumentos:
Valor Obrigatório. Um valor número, uma fórmula que é avaliada como um valor numérico ou
uma referência a uma célula contendo um valor numérico.
Formato_texto Obrigatório. Um formato numérico como uma cadeia de texto entre aspas. Por
exemplo, "m/d/aaaa" ou "#,##0.00". Consulte as seções a seguir para conhecer diretrizes
específicas de formatação.
1. Ative a planilha Texto;
40
_____________________________________________________________________________
3. Acione a função: TEXTO;
4. Para o argumento Valor: clique em B3;
5. Formato_texto: clique em C3;
6. Sua fórmula deve ficar assim: =TEXTO([@VALOR];[@FORMATO]);
7. Observe o resultado:
41
_____________________________________________________________________________
CAPÍTULO 04 – FUNÇÕES DE DATA
INTRODUÇÃO
Neste capítulo, você conhecerá as funções que auxiliam na construção de cálculos mais
elaborados envolvendo data e hora, aprenderá também tratar intervalos de dados envolvendo
feriados, finais de semana e dias úteis.
Após concluir este capítulo, você estará apto para:
1. Realizar cálculos aplicando a função DATAM();

2. Realizar a projeção de datas usando a função FIMMÊS();
3. Calcular datas usando DIATRABALHOTOTAL.INTL();
4. Calcular dias úteis usando DIATRABALHOTOTAL.INTL().
DIA
Retorna o dia de uma data representado por um número de série. O dia é dado como um inteiro
que varia de 1 a 31.
Sintaxe: =DIA (Data)
1. Ative a planilha Dia, Mês e Ano;

4. Digite DIA;
5. Pressione TAB;
6. Clique no DATA, célula B3 ;
7. Sua fórmula deve estar assim: =DIA([@[DATA E HORA]]);
8. Pressione ENTER;
MÊS
Retorna o mês de uma data representado por um número de série. O mês é fornecido como um
inteiro, variando de 1 (janeiro) a 12 (dezembro).
Sintaxe: =MÊS (Data)

3. Digite MÊS;
4. Pressione TAB;
5. Clique no DATA, célula B3;
42
_____________________________________________________________________________
6. Sua fórmula deve estar assim: =MÊS([@[DATA E HORA]]);
7. Pressione ENTER;
ANO
Retorna o ano correspondente a uma data. O ano é retornado como um inteiro no intervalo de
1900-9999.
Sintaxe: =ANO (Data)
Faça o seguinte:

3. Digite ANO;
4. Pressione TAB;
6. Sua fórmula deve estar assim: =ANO([@[DATA E HORA]]);
7. Pressione ENTER ;
8. Ao final sua tabela deve estar como a imagem a seguir:
HORA
Retorna a hora de um valor de tempo. A hora é retornada como um inteiro, variando de 0 (12:00
A.M.) a 23 (11:00 P.M.).
Sintaxe: =HORA (núm_série)
Para aplica-la, faça o seguinte:
43
_____________________________________________________________________________
3. Digite HORA;
4. Pressione TAB;
6. Sua fórmula deve estar assim: =HORA([@[DATA E HORA]]);
7. Pressione ENTER;
MINUTO
Retorna os minutos de um valor de tempo. O minuto é dado como um número inteiro, que vai
de 0 a 59.
Sintaxe: =MINUTO (núm_série)
1. Clique na célula G3;

3. Digite MINUTO;
4. Pressione TAB;
6. Sua fórmula deve estar assim: =MINUTO([@[DATA E HORA]]);
7. Pressione ENTER;
SEGUNDO
Retorna os segundos de um valor de hora. O segundo é fornecido como um inteiro no intervalo
de 0 (zero) a 59.
Sintaxe: =SEGUNDO(núm_série)
1. Clique na célula H3;

3. Digite SEGUNDO;
4. Pressione TAB;
6. Sua fórmula deve estar assim: =SEGUNDO([@[DATA E HORA]]);
7. Pressione ENTER;
44
_____________________________________________________________________________
9. Ao final, sua tabela deve estar assim:
DIA.DA.SEMANA
Retorna o dia da semana como um número inteiro, respeitando o informado no tipo.
SINTAXE: =DIA.DA.SEMANA (núm_série,[retornar_tipo])
A sintaxe da função DIA.DA.SEMANA tem os seguintes argumentos:
Núm_série Obrigatório. Um número sequencial que representa a data do dia que você está
tentando encontrar.
Retornar_tipo Opcional. Um número que determina o tipo do valor retornado.
Tipo Retorno
1 Números 1 (domingo) a 7 (sábado). - Modo de Compatibilidade

2 Números 1 (segunda-feira) a 7 (domingo).
12 Números 1 (terça-feira) a 7 (segunda-feira).
13 Números 1 (quarta-feira) a 7 (terça-feira).
14 Números 1 (quinta-feira) a 7 (quarta-feira).
15 Números 1 (sexta-feira) a 7 (quinta-feira).
45
_____________________________________________________________________________
16 Números 1 (sábado) a 7 (sexta-feira).
17 Números 1 (domingo) a 7 (sábado).
1. Selecione a Célula I3;

2. Acione a função: DIA.DA.SEMANA;
3. No Argumento Num_série: Informar a DATA célula B3;
4. Retornar_tipo: 1
5. Sua fórmula deve estar assim: =DIA.DA.SEMANA([@[DATA E HORA]];1);
6. Pressione Enter;
NÚMSEMANA
Retorna o número da semana no ano, respeitando o informado no tipo. Há dois sistemas usados
para essa função:
Sistema 1 A semana que contém 1 de janeiro é a primeira semana do ano e é numerada semana
1.
Sistema 2 A semana que contém a primeira quinta-feira do ano é a primeira semana do ano e
é numerada como semana 1. Este sistema é a metodologia especificada na norma ISO 8601,
normalmente conhecida como sistema europeu de numeração de semanas.
SINTAXE: =NÚMSEMANA(núm_série,[retornar_tipo])
A sintaxe da função NÚMSEMANA tem os seguintes argumentos:
Núm_série Obrigatório. Uma data na semana. As datas devem ser inseridas com a função DATA
ou como resultado de outras fórmulas ou funções. Por exemplo, use DATA(2008;5;23) para 23
de maio de 2008. Poderão ocorrer problemas se as datas forem inseridas como texto.
Tipo_retorno Opcional. É um número que determina em que dia a semana começa. O valor
padrão é 1.
46
_____________________________________________________________________________
Tipo Retorno Sistema
1 Domingo 1
2 Segunda-feira 1
11 Segunda-feira 1
12 Terça-feira 1
13 Quarta-feira 1
14 Quinta-feira 1
15 Sexta-feira 1
16 Sábado 1
17 Domingo 1
21 Segunda-feira 2
1. Selecione a Célula J3;

2. Acione a função NÚMESEMANA;
3. Para o argumento Num_série: selecione a célula B3 [@Data],
4. Tipo_retorno: 1
5. Sua fórmula deverá ficar assim: =NÚMSEMANA([@[DATA E HORA]];1)
DATA
A função DATA() retorna o número de série sequencial que representa uma data. Por exemplo,
a fórmula =DATA(2008;7;8) retorna 39637, o número de série que representa 7/8/2008.
A função DATA() é bastante útil em situações onde o ano, o mês e o dia são fornecidos como
fórmulas ou referências de célula. Por exemplo, você pode ter uma planilha contendo datas em
um formato não reconhecido pelo Excel, como AAAAMMDD. É possível usar a função DATA junto
com outras funções para converter as datas em um número de série que o Excel reconheça.
Sintaxe: =DATA(ano; mês; dia)
Dica
Recomendamos o uso de quatro dígitos para o argumento Ano, para impedir resultados
indesejados. Por exemplo, usar "07" retorna "1907" como valor de ano.
Se ano estiver entre 0 (zero) e 1899 (inclusive), o Excel adicionará esse valor a 1900 para calcular
a ano.
Se ano for menor do que 0 ou for 10000 ou superior, o Excel retornará o valor de erro #NÚM!.
47
_____________________________________________________________________________
Se mês for maior do que 12, mês adicionará esse número de meses ao primeiro mês no ano
especificado. Por exemplo, DATA(2008;14;2)retorna o número de série que representa 2 de
fevereiro de 2009.
Se mês for menor do que 1, mês subtrairá a amplitude desse número de meses, mais 1, do
primeiro mês no ano especificado. Por exemplo, DATA(2008;-3;2)retorna o número de série que
representa 2.
Se dia for maior do que o número de dias no mês especificado, dia adicionará esse número de
dias ao primeiro dia do mês. Por exemplo, DATA(2008;1;35)retorna o número de série que
representa 4 de fevereiro de 2008.
Se dia for menor do que 1, dia subtrairá a amplitude desse número de dias, mais 1, do primeiro
dia do mês especificado. Por exemplo, DATA(2008;1;-15)retorna o número de série que
representa 16 de dezembro de 2007.
Para aplicá-la, faça o seguinte:
1. Clique na célula K3;

3. Digite DA;
4. Pressione TAB;
7. Clique no mês, célula C3;
9. Clique no dia, célula B3 (dia 12);
10. Sua fórmula deve estar assim: =DATA([@Ano];[@Mês];[@Dia])
AGORA()
Retorna o número de série da data e da hora atual. Se o formato da célula era Geral antes de a
função ter sido inserida, o Excel transformará o formato dessa célula para que ele corresponda
ao mesmo formato de data e hora de suas configurações regionais.
Observação
Se a função AGORA() não atualizar os valores de células conforme esperado, talvez seja
necessário alterar as configurações que controlam quando a pasta de trabalho ou à planilha é
recalculada. Essas configurações podem ser alteradas no Painel de Controle para o aplicativo de
desktop do Excel.
Sintaxe =AGORA()
48
_____________________________________________________________________________
Os números à direita da vírgula decimal no número de série representam a hora; os números à
esquerda representam a data. Os resultados da função AGORA() apenas serão alterados quando
a planilha for calculada ou quando uma macro que contém essa função for executada. A função
não é atualizada continuamente.
Para aplicar a função, faça o seguinte:
1. Clique na célula N6;

3. Digite AG;
4. Pressione TAB;
5. Sua função deve estar assim: =AGORA(
6. Pressione ENTER;
HOJE()
Retorna o número de série da data atual. O número de série é o código de data/hora usado pelo
Excel para cálculos de data e hora. Se o formato da célula era Geral antes de a função ser
inserida, o Excel irá transformar o formato da célula em Data.
Se quiser exibir o número de série, será necessário alterar o formato das células para Geral ou
Número.
1. Clique na célula N3;

3. Digite HO;
4. Pressione TAB;
5. Sua função deve estar assim: =HOJE(
6. Pressione ENTER;
DATAM
Retorna uma data, baseada em um número de meses. Use DATAM para calcular datas de
liquidação ou datas de vencimento que caem no mesmo dia do mês da data de emissão.
SINTAXE: =DATAM(data inicial; meses)
Data inicial - Obrigatório.
49
_____________________________________________________________________________
É uma data que representa a data inicial. As datas devem ser inseridas com a função DATA ou
como resultado de outras fórmulas ou funções. Por exemplo, use DATA(2016;5;23) para 23 de
maio de 2016. Poderão ocorrer problemas se as datas forem inseridas como texto.
Meses - Obrigatório.
É o número de meses antes ou depois de data inicial. Um valor positivo para meses gera uma
data futura; um valor negativo gera uma data passada.
Para testar a função DATAM, faça o seguinte:
1. Ative a planilha DATAM_FIMMÊS;

2. Clique na célula D3 para ativá-la;
3. Digite =DATAM(
4. Clique na data de início (B3);
5. Digite ; (ponto e vírgula) para separar os argumentos;
6. Clique na quantidade de meses (C3);
7. Sua função deve assim: =DATAM([@Início];[@Meses]
8. Pressione ENTER;
FIMMÊS
Retorna o número de série para o último dia do mês que é o número indicado de meses antes
ou depois de data inicial. Use FIMMÊS para calcular as datas de vencimento que caem no último
dia do mês.
SINTAXE: =FIMMÊS(data inicial; meses)
Data inicial - Obrigatório.
Uma data que representa a data inicial.
Meses - Obrigatório.
50
_____________________________________________________________________________
O número de meses antes ou depois de data inicial. Um valor positivo para meses gera uma data
futura, um valor negativo gera uma data passada de data inicial. Um valor positivo para meses
gera uma data futura; um valor negativo gera uma data passada.
1. Clique na célula E3 para ativá-la;

2. Digite =FIM
3. Pressione TAB;
6. Clique na quantidade de meses (C3);
7. Sua função deve assim: =FIMMÊS([@Início];[@Meses]
8. Pressione ENTER;
9. Salve as alterações realizadas na pasta de trabalho.
DIATRABALHO.INTL
Retorna um número que representa uma data que é o número indicado de dias úteis antes ou
após uma data (a data inicial). Os dias úteis excluem fins de semana e quaisquer datas
identificadas como feriados. Use DIATRABALHO para excluir os fins de semana ou feriados ao
calcular as datas de vencimento de fatura, horas de entrega esperadas ou o número de dias de
trabalho executado.
SINTAXE: =DIATRABALHO.INTL(data inicial; dias; [fimdesemana];[feriados])
Data inicial – Obrigatório.
Dias – Obrigatório.
Quantidade de dias a serem calculados.
51
_____________________________________________________________________________
Fim de semana – Opcional.
Dias que são considerados como finais de semana que devem ser pulados. Você pode fazer uso
dos feriados através dos tipos definidos pela Microsoft de 1 a 17. Além disso, você pode
personalizar usando texto para finais de semana, onde 1 é o dia da semana a ser pulado. Para
definir os textos de finais de semana, você deve seguir o padrão ISO 8601. Ficando assim:
Número de fim de semana Dias de fim de semana Texto de fim de semana

1 ou não especificado Sábado, Domingo 0000011
2 Domingo, Segunda 1000001
3 Segunda-feira, Terça-feira 1100000
4 Terça-feira, Quarta-feira 0110000
5 Quarta-feira, Quinta-feira 0011000
6 Quinta-feira, Sexta-feira 0001100
7 Sexta-feira, Sábado 0000110
11 Apenas domingo 0000001
12 Apenas segunda-feira 1000000
13 Apenas terça-feira 0100000
14 Apenas quarta-feira 0010000
15 Apenas quinta-feira 0001000
16 Apenas sexta-feira 0000100
17 Apenas sábado 0000010
Todos os dias 0000000
Segundas, Quartas e Sexta 1010100
Terça e Quinta 0101000
Segunda à Quarta 1110000
Feriados – Opcional.
Lista relativa contendo as datas de cada feriado.
Para testar a função DIATRABALHO.INTL pulando folgas, faça o seguinte:
1. Clique na célula D6 (Coluna Pula Folgas) para ativá-la;

2. Digite =DIATRABALHO.INTL(
5. Clique na quantidade de dias (C6);
6. Digite ; (ponto e vírgula) novamente;
7. Agora, você deve especificar os dias de semana para folga.
8. Digite 1, para Sábado e Domingo.
9. Sua função deve assim: =DIATRABALHO.INTL([@Início];[@Dias];1
52
_____________________________________________________________________________
Para testar a função DIATRABALHO.INTL pulando folgas e feriados, faça o seguinte:
1. Clique na data de início (E6);

3. Digite DIATRABALHO.INTL(
7. Digite ; (ponto-vírgula) novamente;
9. Selecione somente as datas da tabela de Feriados;
10. Sua função deve assim:
=DIATRABALHO.INTL([@Início];[@Dias];1; Feriados[Feriado]
DIATRABALHOTOTAL.INTL
Retorna o número de dias úteis inteiros entre Data Inicial e Data Final. Os dias úteis excluem os
fins de semana e quaisquer datas identificadas em feriados. Use DIATRABALHOTOTAL.INTL para
53
_____________________________________________________________________________
calcular os benefícios aos empregados que recebem com base no número de dias trabalhados
durante um período específico.
SINTAXE: =DIATRABALHOTOTAL.INTL(data inicial; data final; [fimdesemana];[feriados])
Data inicial – Obrigatório.
Data final – Obrigatório.
Data limite.
Fim de semana – Opcional.
Feriados – Opcional.
Para testar a função DIATRABALHOTOTAL.INTL pulando folgas, faça o seguinte:
1. Clique na célula D14 (Coluna Pula Folgas) para ativá-la;

2. Digite =DIATRABALHOTOTAL.INTL(
9. Sua função deve assim: = DIATRABALHOTOTAL.INTL([@Início];[@Dias];1
Para testar a função DIATRABALHOTOTAL.INTL pulando folgas e feriados, faça o seguinte:
1. Clique na célula E14 (Coluna Pula Folgas e Feriados) para ativá-la;

3. Digite DIATRABALHOTOTAL.INTL(
54
_____________________________________________________________________________
10. Sua função deve assim:
=DIATRABALHOTOTAL.INTL([@Início];[@Dias];1; Feriados[Feriado]
55
_____________________________________________________________________________
CAPITULO 05 - FUNÇÕES ESTASTÍSTICAS
INTRODUÇÃO
Nesta unidade, você será convidado a realizar tarefas usando funções estatísticas. Depois de
concluir este capítulo, você poderá criar Rankings e descobrir a posição de valores dentro de
uma tabela, sem alterar a estrutura dela, além disso aprenderá a fazer cálculos adicionado
critérios/condições.
Ao final deste cápitulo você aplicara as funções:
MAIOR
Retorna o maior valor k-ésimo de um conjunto de dados.
SINTAXE: =MAIOR(matriz,k)
A sintaxe da função MAIOR tem os seguintes argumentos:
Matriz Obrigatório. A matriz ou intervalo de dados cujo maior valor k-ésimo você deseja
determinar.
K Obrigatório. A posição (do maior) na matriz ou intervalo de célula de dados a ser

fornecida.
No contexto da atividade modelo iremos usar a função para descobrir os 5 melhores

vendedor analisando o valor de vendas.
8. Ative a planilha Maior, Menor, Ordem.Eq;

9. Selecione a Célula K6;
10. Acione a função MAIOR;
11. No argumento Matriz: selecione o intervalo de dados C5:C24;
12. K: Selecione a posição na célula J6
13. Sua sintaxe deverá ficar assim:
=MAIOR(BASEDEVENDASEATENDIMENTO[VALOR];[@POSIÇÃO])
6. Pressione Enter;
56
_____________________________________________________________________________
MENOR
Retorna o menor valor k-ésimo de um conjunto de dados.
SINTAXE: =MENOR(matriz,k)
A sintaxe da função MENOR tem os seguintes argumentos:
Matriz Obrigatório. A matriz ou intervalo de dados cujo maior valor k-ésimo você deseja
determinar.
K Obrigatório. A posição (do maior) na matriz ou intervalo de célula de dados a ser fornecida.
No contexto da atividade modelo iremos usar a função para descobrir os 5 melhores

atendentes analisando os menores números de reclamação.

2. Selecione a Célula K16;
3. Acione a função MENOR;
4. No argumento Matriz: selecione o intervalo de dados C5:C24;
5. K: Selecione a posição na célula J16
=MENOR(BASEDEVENDASEATENDIMENTO[VALOR];[@POSIÇÃO])
7. Pressione Enter;
57
_____________________________________________________________________________
ORDEM.EQ
Retorna a posição de um valor em relação aos demais, respeitando a classificação AZ/ZA.
SINTAXE: =ORDEM.EQ(número,ref,[ordem])
A sintaxe da função ORDEM.EQ tem os seguintes argumentos:
Núm Obrigatório. O número cuja posição se deseja encontrar.
Ref Obrigatório. Uma matriz ou referência a uma lista de números. Valores não numéricos
em Ref são ignorados.
Ordem Opcional. Um número que especifica como posicionar um número em uma ordem.

2. Selecione a Célula H5;
3. Acione a função: ORDEM.EQ;
4. No argumento Núm: selecione C5;
5. Ref: Selecione o intervalo de dados C5:C24
6. Na Ordem: 1 ou 0, sendo 1 ordem Crescente e 0 Decrescente;
7. Sua fórmula deverá ficar assim: =ORDEM.EQ([@VALOR];[VALOR];0)
8. Observe os resultados
58
_____________________________________________________________________________
SOMASES, MÉDIASES
As funções SOMASES e MÉDIASES, são funções para realizar soma e média adicionando
condições. A única diferença entre as duas funções é o resultado pois a construção é
semelhante.
Sintaxe da função: SOMASES (intervalo_soma; intervalo_critérios1; critérios1)
Intervalo_soma: É um argumento obrigatório em que apontamos o intervalo de valores a serem

somados.
Intervalo_critérios1: É um argumento obrigatório, onde é informado o intervalo de valores que

servirá para a pesquisa ou filtro do(s) critério(s) definidos.
Critérios1: É um argumento obrigatório, onde definimos quais células dentro do

INTERVALO_CRITÉRIOS1 podem ser considerados para o cálculo no INTERVALO_SOMA.
Praticando a Função SOMASES() e MÉDIASES() - Simplificada
SOMASES()
59
_____________________________________________________________________________
1. Na planilha FUNÇÕES DE ANÁLISES SEGREGADAS();
2. Clique na célula L7;
3. Em seguida, digite a função e selecione o INTERVALO_SOMA da coluna VALOR;
=SOMASES ($I$2:$I$35;
4. Continuando a função agora informe o seu INTERVALO_CRITÉRIOS1, selecionando os
valores da coluna Funcionário;
=SOMASES ($I$2:$I$35; $F$2:$F$35;
5. Para finalizar a função, insira o CRITÉRIOS1 para o seu INTERVALO_CRITÉRIOS1 e feche
PARÊNTESES;
=SOMASES ($I$2:$I$35; $F$2:$F$35;K7)
6. Pressione o ENTER, arraste a fórmula para a linha abaixo e observe o resultado:
MÉDIASES()
3. Em seguida, digite a função e selecione o INTERVALO_MÉDIA da coluna VALOR;
=MÉDIASES ($I$2:$I$35;
valores da coluna FUNCIONÁRIO;
=MÉDIASES ($I$2:$I$35; $F$2:$F$35;
5. Para finalizar a função, insira o CRITÉRIOS1 para o seu INTERVALO_CRITÉRIOS1 e feche
PARÊNTESES;
=MÉDIASES ($I$2:$I$35; $F$2:$F$35;K12)
6. Pressione o ENTER, arraste a fórmula para a linha abaixo e observe o resultado:
60
_____________________________________________________________________________
CONT.SES
A função CONT.SES serve para calcular quantidade de ocorrências dentro de um intervalo,
respeitando as condições ou critérios.
Sintaxe da função: CONT.SES (intervalo_critérios1; critérios1;...)
Intervalo_critérios1: É um argumento obrigatório, onde é informado o intervalo de valores que

servirá para a pesquisa ou filtro do(s) critério(s) definidos.
Critérios1: É um argumento obrigatório, onde definimos quais células dentro do

INTERVALO_CRITÉRIOS1 poder ser utilizado no cálculo de ocorrências.
CONT.SES()
3. Em seguida, digite a função e selecione o seu INTERVALO_CRITÉRIOS1 os valores da
coluna GRAVIDADE;
=CONT.SES ($G$2:$G$35;
4. Para finalizar a função, insira o CRITÉRIOS1 para o INTERVALO_CRITÉRIOS1 e feche
PARÊNTESES;
=CONT.SES ($G$2:$G$35; K17)
5. Pressione o ENTER, arraste a fórmula para as linhas abaixo e observe o resultado:
Praticando a Função SOMASES() e MÉDIASES() - Múltipla
61
_____________________________________________________________________________
SOMASES()
2. Clique na célula O7;
3. Em seguida, digite a função e selecione o INTERVALO_SOMA da coluna VALOR;
=SOMASES ($I$2:$I$35;
=SOMASES ($I$2:$I$35; $F$2:$F$35;
5. Insira o CRITÉRIOS1 para o INTERVALO_CRITÉRIOS1
=SOMASES ($I$2:$I$35; $F$2:$F$35;N7;
6. Defina o INTERVALO_CRITÉRIOS2, selecionando os valores da coluna REGIÃO;
=SOMASES ($I$2:$I$35; $F$2:$F$35;N7; $D$2:$D$35;
= SOMASES ($I$2:$I$35; $F$2:$F$35;N7; $D$2:$D$35; $O$2)
MÉDIASES()
3. Em seguida, digite a função e selecione o INTERVALO_MÉDIA da coluna VALOR;
=MÉDIASES ($I$2:$I$35;
=MÉDIASES ($I$2:$I$35; $F$2:$F$35;
=MÉDIASES ($I$2:$I$35; $F$2:$F$35;N12;
=MÉDIASES ($I$2:$I$35; $F$2:$F$35;N12; $D$2:$D$35;
62
_____________________________________________________________________________
=MÉDIASES ($I$2:$I$35; $F$2:$F$35;N12; $D$2:$D$35; $O$2)
CONT.SES()
3. Em seguida, digite a função e selecione o seu INTERVALO_CRITÉRIOS1; selecionando os
valores da coluna GRAVIDADE;
=CONT.SES (GI$2:$G$35;
=CONT.SES (GI$2:$G$35; N17;
=CONT.SES (GI$2:$G$35; N17;$D$2:$D$35;
= CONT.SES (GI$2:$G$35; N17;$D$2:$D$35;$O$2)
63
_____________________________________________________________________________
CAPÍTULO 06 – FUNÇÕES LÓGICAS
INTRODUÇÃO
Neste capítulo você será convidado a usar o raciocínio lógico e desenvolver experiência, usando
funções que ajudam a resolver problemas muito comuns no dia a dia de um ambiente
corporativo
SE ANINHADO
No nosso curso de Fundamentos aprendemos a utilizar a Função SE, ou seja, a verificar se uma
condição for satisfeita o mesmo retorna VERDADEIRO e retorna um outro valor se for falso,
agora vamos aprender que é possível realizamos mais de uma verificação, a isso damos o nome
de função SE aninhado, sendo que é possível realizarmos até 64 verificações.
Valor se verdadeiro
Valor se verdadeiro
Teste Lógico
Valor se falso Teste Lógico
Valor se falso
SINTAXE
SE(teste_lógico, valor_se_verdadeiro, [valor_se_falso]; SE(teste_lógico,

valor_se_verdadeiro, [valor_se_falso];...
A sintaxe da função SE tem os seguintes argumentos:
teste_lógico Obrigatório. A condição que você deseja testar.
valor_se_verdadeiro – obrigatório. O valor que você deseja retornar se o resultado do

teste_lógico for VERDADEIRO.
valor_se_falso – opcional. O valor que você deseja retornar se o resultado do teste_lógico

for FALSO.
No contexto da atividade modelo iremos usar a solução para determinar o desempenho do

vendedor e sua comissão.
1. Ative a planilha SES;
64
_____________________________________________________________________________
2. Selecione a Célula H5;
3. Acione a função: SE;
4. Para o argumento Teste lógico: vamos construir a comparação afim de descobrir se
o valor de vendas é superior ou igual a meta, então selecione a célula G5 e digite o
operador de comparação maior ou igual(>=), em seguida selecione a célula M4;
5. Valor_se_verdadeiro: Digite “Ótimo”
6. Valor_se_falso: Digite novamente a função SE, para utilizar da técnica aninhada,
agora vamos descobrir se o valor de vendas é inferior a 60% da meta atribuída,
então selecione a célula G5, em seguida digite >= e selecione a célula M4 (valor da
meta) e multiplique por 60%;
7. No valor_se_verdadeiro: Digite “Ruim” ;
8. Valor_se_Verdadeiro: Ou seja os demais casos nos quais se aplicam o valores de
vendas inferiores a meta e superior a 60% da meta, digite: “Razoavel”;
9. Feche os dois parênteses das funções SEs;
=SE(G5>=METAGERAL[[#Tudo];[Coluna2]];"ÓTIMO";SE(G5<METAGERAL[[#Tudo];[
Coluna2]]*60%;"RUIM";"RAZOAVEL"))
8. Pressione Enter e observe o resultado;
9. Você deverá fazer o mesmo processo para definição da comissão e alterar as
respostas para a realizar os cálculos de comissionamento;
10. Sua sintaxe deve ficar assim:
11. =SE([@[TOTAL VENDAS]]>=METAGERAL[[#Tudo];[valor]];[@[TOTAL VENDAS]]*5%;
SE([@[TOTALVENDAS]]<=METAGERAL[[#Tudo];[valor]]*60%;[@[TOTAL
VENDAS]]*2%;[@[TOTAL VENDAS]]*3%))
12. Observe o resultado e salve a pasta de trabalho.
65
_____________________________________________________________________________
E
Função Lógica. Analisa os testes lógicos informados e retorna Verdadeiro ou Falso. A função E
retornará VERDADEIRO somente quando todos os testes forem atendidos.
SINTAXE: =E(Pergunta 1; Pergunta 2; Pergunta 3;... Pergunta 255)
APLICANDO A FUNÇÃO E()
Para conhecer essas funções na prática, faça o seguinte:
1. Ative a planilha E, OU;
Neste exemplo, você deve verificar se, o valor de cada mês está a partir de 5.000, caso
esteja, a fórmula apresentará a mensagem OK, caso contrário deixe a célula vazia. Aqui,
você usará as funções SE e E aninhadas.
2. Em seguida, clique na célula G5 (Status 1º TR);

3. Digite =SE(E(
4. Clique no primeiro valor do campo Jan (C5);
5. Digite >=
6. Clique no valor da meta F5 (5.000)
7. Digite ; para separar os argumentos da função E;
8. Clique no primeiro valor do campo Fev (D5);
9. Digite >=
11. Digite ; para separar os argumentos E;
12. Clique no primeiro valor de Mar (E5);
13. Digite >=
15. Em seguida, você deve fechar o parêntese da função E;
16. Sua fórmula deve estar como segue:
=SE(E([@Jan]>=[@[Meta/Mês 1º TR]];[@Fev] >=[@[Meta/Mês 1º TR]];[@Mar]

>=[@[Meta/Mês 1º TR]])
17. Ao fechar o parêntese da função E, você deve continuar a função SE. Para isso digite ;
para separar os argumentos da função SE;
18. Digite “OK”;
19. Digite ; para separar o argumento FALSO da função SE;
20. Digite “” (abre e feche aspas);
66
_____________________________________________________________________________
=SE(E([@Jan]>=[@[Meta/Mês 1º TR]];[@Fev] >=[@[Meta/Mês 1º TR]];[@Mar]

>=[@[Meta/Mês 1º TR]]);“OK”;“”)

23. Confira o resultado:
Repita o procedimento para o campo Status 2º TR
OU
Função Lógica. Analisa os testes lógicos informados e retorna Verdadeiro ou Falso. A função OU
retornará VERDADEIRO quando ao menos um teste for atendido.
SINTAXE: =OU(Pergunta 1; Pergunta 2; Pergunta 3;... Pergunta 255)
APLICANDO A FUNÇÃO OU()
No próximo exemplo, você deve verificar se o vendedor atingiu a meta em pelo menos um dos
trimestres. Se ele atingiu, exiba a mensagem Prêmio, caso contrário, exiba Sem Prêmio. Para
aplicar este exemplo, faça o seguinte:
1. Clique na célula M5;

2. Digite =SE(OU(
3. Em seguida, clique no primeiro valor do campo Status 1º TR (H5);
4. Digite =”OK”;
5. Digite ; para separar os argumentos da função OU;
6. Clique no primeiro valor do campo Status 2º TR (J5);
7. Digite =”OK”;
8. Feche parêntese da função OU;
9. Sua fórmula deve estar assim:
=SE(OU([@[Status 1º TR]]="OK";[@[Status 2º TR]]="OK")
10. Digite ; para separar os argumentos da função SE;

11. Digite “Prêmio”;
12. Digite ; para separar o argumento FALSO da função SE;
13. Digite “Sem Prêmio”;
14. Sua fórmula deve estar assim:
=SE(OU([@[Status 1º TR]]="OK";[@[Status 2º TR]]="OK");"Prêmio";"Sem Prêmio")
67
_____________________________________________________________________________
16. Confira o resultado;
68
_____________________________________________________________________________
CAPÍTULO 07 – FUNÇÕES PESQUISA E REFERÊNCIA
INTRODUÇÃO
Este capítulo, reúne algumas das funções mais usadas e exigidas no mercado de trabalho, seja
para quem já está incluso no ambiente corporativo ou quem pleiteia o primeiro emprego.
PROCV
Use a função PROCV, uma das funções de pesquisa e referência do Excel quando precisar
localizar coisas em linhas de uma tabela ou de um intervalo.
Dica: O segredo de PROCV é organizar seus dados de forma que o valor procurado esteja à
esquerda do valor de retorno que você deseja localizar.
SINTAXE: =PROCV(valor_procurado, matriz_tabela, núm_índice_coluna, [intervalo_pesquisa])
A sintaxe da função PROCV tem os seguintes argumentos:
valor_procurado Obrigatório. O valor que você deseja procurar. O valor que você deseja
procurar deve estar na primeira coluna do intervalo de células especificado na matriz_tabela.
Por exemplo, se a matriz_tabela abrange as células B2:D7, então valor_procurado deverá estar
na coluna B. Consulte o gráfico a seguir. O valor_procurado pode ser um valor ou uma referência
a uma célula.
matriz_tabela Obrigatório. O intervalo de células em que PROCV procurará pelo

valor_procurado e pelo valor de retorno.
A primeira coluna no intervalo de células deve conter o valor_procurado (por exemplo, o

Sobrenome na imagem abaixo.) O intervalo de células também deve incluir o valor de retorno
que você deseja localizar (por exemplo, o Nome na imagem abaixo).
Saiba como selecionar intervalos em uma planilha.
núm_índice_coluna Obrigatório. O número da coluna (começando em 1 para a coluna mais à

esquerda da matriz-tabela) que contém o valor de retorno.
intervalo_procurado Opcional. Um valor lógico que especifica se você quer que PROCV localize
uma correspondência exata ou aproximada:
VERDADEIRO supõe que a primeira coluna na tabela é classificada em ordem alfabética ou

numérica e, em seguida, procura o valor mais próximo. Esse será o método padrão, se você não
especificar um.
FALSO procura o valor exato na primeira coluna.
PROCV POR EXATIDÃO

Para contextualização usaremos a atividade modelo PROCV por Exatidão que consiste em um
relatório valor e quantidade comprada assim como comissão e tempo para conversão da venda,
69
_____________________________________________________________________________
porém algumas informações lhe faltam como nome do vendedor, porcentagem de comissão e
data do orçamento, então com o PROCV descobriremos essas informações.
1. Ative a planilha PROCV Exatidão;

2. Selecione a Célula C5;
3. Acione a função: PROCV;
4. No argumento da Função, informe na opção Valor_procurado: selecione a célula
C5, lembre-se que o valor procurado é a informações e comum que usará para
descobrir o que deseja;
5. Matriz_tabela: Troque de planilha(Vá para BASE COMISSÃO) e selecione o intervalo
de célula de A1:F31
6. Número_índice_coluna: 2(refere-se a qual coluna da dua base de dados quer
retornar) e Procurar_intervalo: 0;
7. Sua fórmula deverá ficar assim: =PROCV([@CÓDIGO];Tabela13[#Tudo];2;0)
8. Pressione Enter;
9. Repita o processo para as demais colunas (Comissão e Orçamento), você pode
copiar a fórmula e mudar o número índice para 4 e 6 respectivamente na barra de
fórmula.
70
_____________________________________________________________________________
PROCV POR APROXIMAÇÃO

Para contextualização usaremos a atividade modelo PROCV POR APROXIMAÇÃO que consiste
em um relatório para pagamentos dos funcionários, precisamos descobrir qual é o desconto
correto para a faixa salarial de cada funcionário.
1. Ative a planilha PROCV Exatidão;

2. Selecione a Célula F5;
3. Acione a função: PROCV;
4. No argumento Valor_procurado: selecione a célula C5
5. Matriz_tabela: Vá para BASE Descontos e selecione o intervalo de célula de A1:B14
6. Número_índice_coluna: 2;
7. Procurar_intervalo: 1, como não temos uma linha com o valor exato do salário
usaremos o cenário aproxima que retornará o menor mais próxima para
trabalharmos com intervalos de valores;
8. Sua fórmula deverá ficar assim: =PROCV([@BRUTO];TblDescontos;2;1)
9. Pressione Enter;
71
_____________________________________________________________________________
PROC
Utilize PROC, uma das funções de pesquisa e referência, quando for necessário analisar uma
única linha ou coluna e localizar um valor na mesma posição em uma segunda linha ou coluna.
Há duas maneiras de usar PROC: Forma vetorial e forma matricial
Forma vetorial: Use esta forma de PROC para pesquisar um valor em uma linha ou coluna. Use
a forma vetorial quando quiser especificar o intervalo que contém os valores que você deseja
corresponder.
Forma matricial: É altamente recomendável usar PROCV ou PROCH em vez da forma matricial.
A forma matricial é fornecida para fins de compatibilidade com outros programas de planilha,
mas sua funcionalidade é limitada.
Uma matriz é uma coleção de valores em linhas e colunas (como uma tabela) que você deseja
pesquisar.
Sintaxe: =PROC (valor_proc, vetor_proc, [Vetor_resultado])
A sintaxe da forma vetorial da função PROC apresenta os seguintes argumentos:
valor_proc Obrigatório. Um valor que PROC procura no primeiro vetor. valor_procurado pode
ser um número, um texto, um valor lógico ou um nome ou uma referência associada a um valor.
vetor_proc Obrigatório. Um intervalo que contém apenas uma linha ou uma coluna. Os valores
em vetor_proc podem ser um texto, números ou valores lógicos.
Importante: Os valores em vetor_proc devem ser colocados em ordem crescente: ..., -2, -1, 0,
1, 2, ..., A-Z, FALSO, VERDADEIRO; caso contrário, PROC poderá não retornar o valor correto.
Textos em maiúsculas e minúsculas são equivalentes.
vetor_resultado Opcional. Um intervalo que contém apenas uma linha ou coluna. O argumento
do vetor_resultado deve ter o mesmo tamanho do valor_proc. Ele deve ser do mesmo tamanho.
11. Ative a planilha PROC (Vetorial);
72
_____________________________________________________________________________
12. Na célula G2, selecione Fórmulas > Biblioteca de Funções > Pesquisa e Referência >
PROC
Figura 209 – Janela Resultado Fórmulas > Biblioteca de Funções > Pesquisa e Referência > PROC
13. Selecione valor_procurado;vetor_pro;vetor_result, OK;
Figura 210 – Janela Resultado Fórmulas > Biblioteca de Funções > Pesquisa e Referência > PROC >
valor_procurado;vetor_pro;vetor_result, OK
14. Na janela argumento da função Valor_procurado: selecione [@Média];
73
_____________________________________________________________________________
Figura 211 – Janela Resultado Valor_procurado: [@Média]
15. Vetor_proc: FaixaDeSituações[[De]:[Até]]
Figura 212 – Janela Resultado Valor_procurado: FaixaDeSituações[[De]:[Até]]
16. Vetor_result: FaixaDeSituações[Situação];
Figura 213 – Janela Resultado Vetor_result: FaixaDeSituações[Situação]
74
_____________________________________________________________________________
Figura 214 – Janela Resultado Argumentos preenchidos
17. OK
Figura 215 – Janela Resultado após clicar OK
Sintaxe
PROC (valor_procurado, matriz)
A sintaxe da forma matricial de PROC apresenta os seguintes argumentos:
valor_procurado Obrigatório. Um valor que PROC pesquisa em uma matriz. O argumento

valor_procurado pode ser um número, um texto, um valor lógico ou um nome ou uma referência
associada a um valor.
Se PROC não conseguir localizar o valor de valor_procurado, ela usará o maior valor na matriz
que for menor do que ou igual a valor_procurado.
Se o valor de valor_procurado for menor do que o menor valor na primeira linha ou coluna
(dependendo das dimensões da matriz), PROC retornará o valor de erro #N/D.
Matriz Obrigatório. Um intervalo de células que contém texto, números ou valores lógicos que
se deseja comparar com valor_procurado.
A forma matricial de PROC é muito semelhante às funções PROCH e PROCV. A diferença está no
fato de que PROCH pesquisa o valor de valor_procurado na primeira linha, PROCV pesquisa a
primeira coluna e PROC pesquisa de acordo com as dimensões da matriz.
Se a matriz abranger uma área cuja largura é maior do que a altura (mais colunas do que linhas),
PROC procurará o valor de valor_procurado na primeira linha.
Se uma matriz for quadrada ou tiver a altura maior do que a largura (mais linhas do que colunas),
PROC pesquisará na primeira coluna.
75
_____________________________________________________________________________
Com as funções PROCH e PROCV, você pode indexar para baixo ou na transversal, mas PROC
sempre seleciona o último valor na linha ou coluna.
Importante: Os valores em matriz devem ser coloca dos em ordem crescente: ..., -2, -1, 0, 1, 2,
..., A-Z, FALSO, VERDADEIRO; caso contrário, PROC poderá não retornar o valor correto. Textos
em maiúsculas e minúsculas são equivalentes.
18. Ative a planilha PROC (Matricial);

19. Na célula: G2, selecione Fórmulas > Biblioteca de Funções > Pesquisa e Referência >
PROC
Figura 216 – Janela Resultado Fórmulas > Biblioteca de Funções > Pesquisa e Referência > PROC
20. Selecione valor_procurado; matriz, OK;
Figura 217 – Janela Resultado Fórmulas > Biblioteca de Funções > Pesquisa e Referência > PROC, valor_procurado;
matriz, OK;
21. No Argumento da função Valor_procurado: [@Média]
76
_____________________________________________________________________________
Figura 218 – Janela Resultado Valor_procurado: [@Média]
22. No argumento da função Matriz: FaixaDeSituaçõesMatricial
Figura 219 – Janela Resultado Matriz: FaixaDeSituaçõesMatricial
Figura 220 – Janela Resultado Argumentos da função preenchidos

23. OK
CORRESP
Essa função, é responsável por retornar a posição relativa de um item em uma lista. Ela é muito
útil quando aninhada com outras funções.
SINTAXE: =CORRESP(Valor_Procurado; Matriz_Procurada;[Tipo_Correspondência])
Valor Procurado: O que deve ser pesquisado…
77
_____________________________________________________________________________
Matriz Procurada: Onde deve ser pesquisado…
Tipo Correspondência: Exato, Maior que, Menor que…
Em nosso exemplo, você aprenderá os três tipos. Para o tipo de pesquisa exata, usaremos os
dados com dias da semana, para os demais, usaremos intervalos numéricos.
CORRESP EXATIDÃO
Para conhecer a função CORRESP, faça o seguinte:
1. Ative a planilha Corresp;

2. Clique na célula D5 (Posição);
3. Digite =CORRESP(
4. Para o primeiro argumento (Valor_Procurado), clique na célula D4;
5. Digite ; para separar o primeiro argumento;
6. Em seguida, para o segundo argumento, selecione a matriz onde esse valor deve ser
procurado. Em nosso exemplo, selecione a lista com os dias da semana.
7. Digite ; para separar o segundo argumento;
8. O tipo de correspondência para este exemplo, deve ser EXATO, pois, trata-se de um
texto.
9. Sua função deve estar como abaixo:
=CORRESP(D4;TblSemana[Semana];0


14. Digite =CORRESP(
15. Clique na célula D7 para pesquisar o mês;
78
_____________________________________________________________________________
16. Selecione a matriz onde esse valor deve ser procurado. Em nosso exemplo, selecione a
lista com os meses.
17. Use o tipo de correspondência EXATO.
=CORRESP(D7;TblMeses[Mês];0

CORRESP MAIOR DO QUE

No tipo Maior do que, o CORRESP fará a pesquisa primeiramente pelo valor informado, caso o
valor não exista na lista, ele retorna a posição de um valor maior do que o procurado que seja
mais próximo ao procurado.
Para conhecer o CORRESP usando o tipo maior do que, faça o seguinte:
1. Clique na célula i12;

2. Em seguida, digite =CORRESP(
3. Clique na célula H12 (número 20);
4. Digite ; para definir a matriz;
5. Selecione onde o CORRESP deve procurar pelo número selecionado. Em nosso exemplo,
selecione o intervalo i2:i7;
79
_____________________________________________________________________________
6. Digite ; para definir o tipo de pesquisa;
7. Selecione a opção -1 – Maior do que;
8. Sua função deve estar assim: =CORRESP([@Valor];TblCrescente[Contagem];-1

9. Pressione ENTER;
10. Confira o resultado.
CORRESP MENOR DO QUE

No tipo Menor que, o CORRESP fará a pesquisa primeiramente pelo valor informado, caso o
valor não exista na lista, ele retorna a posição de um valor menor do que o procurado que seja
mais próximo ao procurado.
Para conhecer o CORRESP usando o tipo menor do que, faça o seguinte:

2. Em seguida, digite =CORRESP(
3. Clique na célula K12 (número 39);
4. Digite ; para definir a matriz;
5. Selecione onde o CORRESP deve procurar pelo número selecionado. Em nosso exemplo,
selecione o intervalo L2:L7;
6. Digite ; para definir o tipo de pesquisa;
7. Selecione a opção 1 – Menor do que;
8. Sua função deve estar assim: =CORRESP([@Valor];TblDecrescente[Contagem];1

9. Pressione ENTER;
10. Confira o resultado.
80
_____________________________________________________________________________
PROCV COM SE/ PROCV COM CORRESP

O aninhamento das funções irá lhe permitir retornar colunas distintas de maneira automática
com base em sua necessidade, no exemplo devemos usar a solução para determinar qual será
o valor de venda em função da condição de pagamento.
1. Ative a planilha PROCV com CORRESP;

2. Na célula E3: é necessário que descobrir o número índice da coluna que será usado
no PROCV para isso, podemos realizar de duas, com a Função SE() ou com a função
CORRESP(), neste exemplo faremos das duas maneiras:
3. A começar pelo exemplo com SE, na célula E3, Acione a função: SE;
4. No Teste_lógico: [@[Condição de Pagamento]]="À Vista";
5. Valor_se_verdadeiro: 3;
6. Valor_se_falso: 4
7. Sua fórmula deverá fica assim: =SE([@[CONDIÇÃO DE PAGAMENTO ]]="À vista";3;4)
8. Caso realize com a função CORRESP, na célula E3 acione a função CORRESP;
9. No argumento valor_procurado: [@[Condição de Pagamento]];
10. Matriz_tabela: selecione o Mercado[#Cabeçalhos];
11. Tipo_corrrespondencia: digite 0;
12. Sua fórmula ficará assim: =CORRESP([@[CONDIÇÃO DE PAGAMENTO
]];Mercado[#Cabeçalhos];0);
81
_____________________________________________________________________________
14. Agora na célula F3, acione a função PROCV;

15. No Valor_procurado: [@Código];
16. Na Janela Argumento da Função Matriz: Mercado;
17. Núm_índice_coluna: Selecionar a célula E3, resultado da correspondência
desejada;
18. Procurar_Intervalo: 0;
19. Sua fórmula deverá ficar assim:
=PROCV([@CÓDIGO];Mercado;[@[NÚMERO ÍNDICE DA COLUNA]];0)
20. Pressione Enter;
21. Observe o Resultado;
22. Lembre-se que você pode abrir mão da coluna auxiliar aninhando as soluções e
depois excluindo a coluna auxiliar;
23. As sintaxes poderão ficar assim:
=PROCV([@CÓDIGO];Mercado;CORRESP([@[CONDIÇÃO DE PAGAMENTO
]];Mercado[#Cabeçalhos];0);0)
=PROCV([@CÓDIGO];Mercado;SE([@[CONDIÇÃO DE PAGAMENTO ]]="À
Vista";3;4);0)
24. Faça o procedimento informado acima;
82
_____________________________________________________________________________
PROCV COM CHAVE

A solução apresentada se faz necessário quando nosso valor procurado não é exclusivo dentro
da base, então criamos uma chave para distinguir as possibilidades.
1. Ative a planilha PROCV com Chave;

2. Na célula B5: concatenar Vendedor com Setor; =[@SETOR]&[@VENDEDOR]
a. Dica: Também podemos utilizar preenchimento relâmpago:
3. Agora em sua análise, na célula M5: concatenar Setor com Vendedor;
=[@SETOR]&[@VENDEDOR]
4. Na célula N5, acione a função: PROCV;
5. No argumento Valor_procurado: clique na célula M5;
6. Função Matriz: selecione o intervalo de dados B4:E22;
7. Argumento da Função Número_Índice_coluna: 4;
8. Procurar_intervalor: 0
9. Sua fórmula deverá ficar assim: =PROCV([CHAVE];ListaDeVendedoresPorSetor;4;0)
10. Pressione Enter;
12. Aproveite e faça um PROCV para descobrir o nome do vendedor usando o código;
13. A sintaxe ficará: =PROCV([VENDEDOR];ListaDeVendedores;2;0)
83
_____________________________________________________________________________
CAPÍTULO 08 – MINI GRÁFICOS
INTRODUÇÃO
Neste capítulo, você aprenderá a usar Mini Gráficos (Sparklines) para representar visualmente
a tendência de seus dados. Por serem gráficos minúsculos, pois ficam dentro de uma célula, você
pode chamar atenção de maneira sofisticada, sem correr o risco de um problema muito comum
quando usamos objetos e dados no mesmo local, a sobreposição e a falta de espaço, abaixo
exemplo da utilização de minigráficos:
TOTAL NO
MÊS JAN FEV MAR AB R MAI JUN JUL AGO SET OUT NOV D EZ J . F. M. A
ANO
Caixa mensal R$ 150,00 R$ 675,00 R$ 861,00 R$ 1.641,00 R$ 1.045,00 R$ 2.625,00 R$ 20,00 -R$ 255,00 R$ 1.314,00 R$ 994,00 R$ 1.987,00 R$ 127,00 R$ 11.184,00
Renda R$ 4.307,00 R$ 4.153,00 R$ 4.065,00 R$ 4.497,00 R$ 4.164,00 R$ 4.228,00 R$ 4.383,00 R$ 4.101,00 R$ 4.304,00 R$ 4.413,00 R$ 4.068,00 R$ 4.163,00 R$ 50.846,00
Despesas R$ 4.157,00 R$ 3.478,00 R$ 3.204,00 R$ 2.856,00 R$ 3.119,00 R$ 1.603,00 R$ 4.363,00 R$ 4.356,00 R$ 2.990,00 R$ 3.419,00 R$ 2.081,00 R$ 4.036,00 R$ 39.662,00
TOTAL NO
NOV D EZ J . F. M. A. M. J . J . A. S. O. N. D
ANO
1.987,00 R$ 127,00 R$ 11.184,00
4.068,00 R$ 4.163,00 R$ 50.846,00
2.081,00 R$ 4.036,00 R$ 39.662,00
INSERIR E EDITAR
1. Ative a planilha INSERINDO MINNIGRÁFICOS;

2. Selecione a célula: C7;
3. Selecione Faixa de Opções > Inserir > Minigráficos COLUNA;
4. Seleciona a faixa na qual deseja criar o Minigráfico: C4:N6;
5. Após clicar OK;
84
_____________________________________________________________________________
6. Após ter criado os Minigráficos você pode estar personalizando, para tanto,
selecione a faixa onde foram criados os Minigráficos, clicar nas Ferramentas de
Minigráficos, aba Design;
7. Configure Pontos Negativos e Marcadores;

8. Faça o mesmo para o gráfico de linha e perdas e ganho nas linhas 8 e 9;
Dica você pode utilizar a Análise Rápida para criação de Gráficos e Minigráficos que é
bem mais rápido.
85
_____________________________________________________________________________
86
_____________________________________________________________________________
CAPÍTULO 09 – GRÁFICOS
INTRODUÇÃO
Nesta unidade, você conhecerá os novos modelos de gráfico da versão 2016 ou mais recente e
aprenderá, qual modelo deve ser usado e, em qual situação.
ESCALA LOGARÍTIMICA
Por padrão, o Microsoft Office Excel determina os valores de escala mínimo e máximo do eixo
vertical (valor), também conhecido como eixo y, quando você cria um gráfico. Entretanto, é
possível personalizar a escala para melhor atender às suas necessidades. Quando os valores
plotados no gráfico cobrirem um intervalo muito amplo, você também poderá alterar o eixo de
valor para uma escala logarítmica.
Vendas
100.000.000.000.000,00
1.000.000.000.000,00
10.000.000.000,00
100.000.000,00
1.000.000,00
10.000,00
100,00
1,00
Meia
T-Shirt
Polo
Short
Casaco
Cinto
Calça
Boné
Sapato
Bermuda
Colete
Regata
Camisa
Cueca
1. Ative a planilha Escala Logarítmica;

2. Em seguida utilizando as Teclas Alt+F1, crie o gráfico;
87
_____________________________________________________________________________
Vendas
1.000.000.000.000.000,00
900.000.000.000.000,00
800.000.000.000.000,00
700.000.000.000.000,00
600.000.000.000.000,00
500.000.000.000.000,00
400.000.000.000.000,00
300.000.000.000.000,00
200.000.000.000.000,00
100.000.000.000.000,00
-
Polo
Meia
Cinto
T-Shirt
Short
Calça
Boné
Sapato
Bermuda
Casaco
Colete
Regata
Camisa
Cueca
3. Observe que quando criamos um gráfico onde os intervalos são muito amplos nos
deparamos com esse resultado, como fazer?
4. Dê um duplo clique em qualquer valor do eixo principal vertical;
Vendas
1.000.000.000.000.000,00
900.000.000.000.000,00
800.000.000.000.000,00
700.000.000.000.000,00
600.000.000.000.000,00
500.000.000.000.000,00
400.000.000.000.000,00
300.000.000.000.000,00
200.000.000.000.000,00
100.000.000.000.000,00
-
5. Opções de eixo serão exibidas, clique na Escala Logarítmica Base 10;
88
_____________________________________________________________________________
6. Ao clicar na Escala logarítmica Base 10 o gráfico ficará com o resultado desejado.
EIXO SECUNDÁRIO
Este é recurso irá auxiliar quando houver a necessidade de demonstrar tipos mistos de dados
nosso gráfico, como exemplo abaixo:
89
_____________________________________________________________________________
GRÁFICO DE COMBINAÇÃO COM EIXO SECUNDÁRIO
1. Usando os valores da planilha Gráfico de combinação insira o Gráfico de
Combinação, Coluna Clusterizada - Linha;
2. Selecione o gráfico, clicar na opção Ver Todos os Gráfico; ou em Design Alterar Tipo
de Gráfico
3. Na janela Alterar Tipo de Gráfico, selecione a aba Todos os Gráfico;
4. Escolha o Eixo Secundário para Realizado;
GRÁFICO DE RADAR
Gráfico para comparar multivariáveis, permitindo analisar diversas características em uma única
base gráfica
Usado por exemplo, para analisar competências de um profissional.
90
_____________________________________________________________________________
GRÁFICO DE BOLHAS
Deve ser usado quando você quer apresentar mais de duas variáveis, onde um gráfico X e Y não
lhe permitiria apresentar.
1. Na planilha BOLHAS;
2. Selecione os intervalo de dados, B3:D7;
3. Inserir o GRÁFICO DE BOLHAS;
4. Ajuste os EIXOS;
5. A aparência deve ficar próxima ao exemplo abaixo:
NOVOS GRÁFICOS
Na Versão Microsoft versão 2016, foram lançados novos Gráficos e vamos conhece-los.
91
_____________________________________________________________________________
GRÁFICO DE HISTOGRAMA
Um histograma é um gráfico de colunas que mostra dados de frequência, agrupando os valores
com base no histórico exemplo:
GRÁFICO DE PARETO
Gráfico de Pareto ou Diagrama de Pareto é um gráfico de coluna onde os valores de frequências
são ordenados, outra característica interessante é a linha de porcentagem acumulada onde
pode analisar o número de ocorrências em detrimento dos tipos para priorização das ações,
exemplo:
92
_____________________________________________________________________________
GRÁFICO DE CASCATA
Um gráfico de cascata mostra um total cumulativo à medida que valores são adicionados ou
subtraídos. Ele é útil para você entender como um valor inicial (por exemplo, a receita líquida)
é afetado por uma série de valores positivos e negativos, exemplo:
GRÁFICO DE FUNIL
Gráfico utilizados para estágios, muito utilizado no cenário de vendas para prospecção por
exemplo:
GRÁFICO MAPA DE ÁRVORE

Utilizado para:
• Comparar valores nos níveis de hierarquia;

• Mostrar proporções no nível de hierárquicos como retângulo;
• Exemplo:
93
_____________________________________________________________________________
GRÁFICO DE EXPLOSÃO SOLAR

O gráfico de explosão solar é ideal para exibir dados hierárquicos. Cada nível da hierarquia é
representado por um anel ou círculo, com o círculo mais interno na parte superior da hierarquia,
Exemplo:
94

Excel Módulo II

Enviado por

Direitos autorais:

Formatos disponíveis

Excel Módulo II

Enviado por

Dados do documento

Título original

Direitos autorais

Formatos disponíveis

Compartilhar este documento

Compartilhar ou incorporar documento

Opções de compartilhamento

Você considera este documento útil?

Este conteúdo é inapropriado?

Direitos autorais:

Formatos disponíveis

Excel Módulo II

Enviado por

Direitos autorais:

Formatos disponíveis

Microsoft Excel 365

Sobre este Curso

Após concluir este capítulo, você estará apto para:

1. Usar Nomes Definidos em fórmulas;

Tipo Referência Clássica Nomes Definidos

1. Ative a planilha Intervalo Nomeado;

6. Observe que a caixa Novo Nome, é apresentada;

Nessa caixa, você pode:

Caracteres possíveis: _ (sublinhado), . (ponto), entre outros.

7. Digite ValorDespesas na caixa Nome;

12. Clique na célula C10 (Subtotal);

16. Clique na célula E3;

1. Clique na guia Fórmulas;

3. Perceba que a janela do Gerenciador de Nomes é exibida, resumindo os nomes criados;

4. Localize o nome desejado;

1. Na planilha FORMATANDO COMO TABELA;

5. Escolha um estilo disponível;

7. Para efetivar o resultado, basta clicar na opção.

FORMATANDO COMO TABELA

1. Clique em qualquer dado da tabela;

Em Ferramentas de Tabela, é possível:

1. Clique na guia contextual DESIGN.

1. Na planilha COMPORTAMENTO DA TABELA;

11. Espalhe os valores para as demais linhas da tabela;

12. Em seguida, vá a primeira célula de total (K6);

1. Clique na célula D3, para realizar o cálculo do departamento Comercial;

17. Salve as alterações na pasta de trabalho.

Ao final do capitulo você estará apto a utilizar as ferramentas:

1. TEXTO PARA COLUNAS

TEXTO PARA COLUNAS

1. Clique na planilha Texto Delimitado;

3. Clique na guia Dados;

5. Na janela do assistente para conversão de texto em colunas, selecione Delimitado.

Para conhece-lo, faça o seguinte:

1. Clique na planilha Texto Largura Fixa;

1. Ative a planilha De Texto;

4. Em seguida, na janela Importar Dados, navegue até o arquivo desejado. Em nosso

10. Observe o resultado e salve as alterações realizadas na pasta de trabalho.

1. Ative a planilha Do Access;

4. Em seguida, na janela Importar Dados, navegue até o arquivo desejado. Em nosso

7. Selecione a tabela Cidades Brasileiras

1. Ative a planilha XML;

5. Selecione o arquivo XML;

7. Selecione a célula para o armazenamento dos dados e clique em OK;

3. Na guia Dados, vá até o grupo Ferramentas de Dados e clique em Consolidar.

7. Na caixa de diálogo ‘Consolidar’, clique em Adicionar e repita as etapas 6 e 7 para adicionar

7.1 Referência '2016'!$A$1:$D$7, '2017'!$A$1:$E$9, '2018'!$A$1:$E$7;

8. Usar rótulos na:

8.1 Selecionar Linha superior;

10. Observe os resultados e salve a pasta de trabalho

1. Ative a planilha ARRUMAR, MINÚSCULA, MAÍUSCULA;

Ainda na planilha FUNÇÕES DE TEXTO, faça o seguinte:

1. Clique na célula D5;

SINTAXE: MAIÚSCULA (texto).

1. Clique na célula E5;

SINTAXE: PRI.MAIÚSCULA (texto)

Ainda na planilha FUNÇÕES DE TEXTO, faça o seguinte:

1. Clique na célula F5;

As funções LOCALIZAR têm os seguintes argumentos: