AMOSTRA INTRODUÇÃO INTRODUÇÃO
KARINE LAGO
1ª EDIÇÃO
BELO HORIZONTE KARINE GOUVÊA DIBAI DO LAGO 2018
SUMÁRIO Prefácio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Como Ler Esse Es se Livro. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Capítulo 1: Introdução ao Excel 1.1 Como para Aprender Excel de Verdade . . . . . . . . . . . . . . . . . . . . . . . . 19 1.2 Compreendendo a Interfa rface da das Pl Planilhas do do Ex Excel . . . . . . . . . . . . . . . 21 Guias Principais . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Guias de Contexto. Contexto. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Barra de de Ferramentas de de Acesso Rá Rápido . . . . . . . . . . . . . . . . . . . . . . . . . 27 27 Painéis de Tarefas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Menus de Contexto . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Utilizando a Barra de Status . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 33 1.3 Menu Menu Arqu Arquiv ivo o: Inf Infor orma maçõ ções es e Adm Admin inis istr traç ação ão da Plan Planililha ha . . . . . . . . . . . 35 1.4 Recuperando Planilhas Não Salvas . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 1.5 A Área de Trabalho da Planilha . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 1.6 Movendo-se Entre Células e Planilhas . . . . . . . . . . . . . . . . . . . . . . . . . . 46 1.7 Seleção dos Melhores Atalhos do Excel . . . . . . . . . . . . . . . . . . . . . . . . 49 1.8 Atalhos Com a Tecla Alt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 1.9 1.9 Gerenciando Planilhas em Pastas Pas tas de Trabalho. . . . . . . . . . . . . . . . . . . . 52 1.10 Planilha Super Escondida . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 Capítulo 2: Guia Página Inicial 2.1 Área de Transferência . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Colando Além do Ctrl+V. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63 Copiando Valores e Imagens . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 6 O Poderoso Pincel de Formatação . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 68 Acess ssaando o Hist stó órico da da Ár Área de de Tr Transferência . . . . . . . . . . . . . . . . . .69 2.2 Fonte de Texto . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 2.3 Alinhamento de Texto . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 Mesclar e Quebrar Texto . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 74 Orientação do Texto . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 2.4 Formato de Número . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 Tipos de Formato de Número . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 79 Cap Ca paci cida dade dess de de Ar Arm maz azen enam ameento de Val alor ores es do Ex Exce cell . . . . . . . . . . . . . 81 Personalização de Formatos de Número . . . . . . . . . . . . . . . . . . . . . . . . .8 .833 Formatar Decimais Exibidos. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .84 Inserir Textos Na Formatação de Números . . . . . . . . . . . . . . . . . . . . . . .8 .866
Repetir Elemento De Acordo Com o Tamanho da Célula . . . . . . . . . . .86 Repetir Elementos com O Arroba . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 Anular o Efeito de um Caractere de Formatação . . . . . . . . . . . . . . . . . .87 Caracteres Especiais: + - / ( ) : $ [Espaço] e Letras . . . . . . . . . . . . . . . . .88 Aplicação de Cores Em Formatos Personalizados . . . . . . . . . . . . . . . . .88 Personalização de Tempo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .89 Formato de Número Com Condicionais . . . . . . . . . . . . . . . . . . . . . . . . . . 90 2.5 Formatação Condicional . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 Criando Regras de Formatação Personalizadas. . . . . . . . . . . . . . . . . . . .97 Condicional de Violação de Regras de Negócio . . . . . . . . . . . . . . . . . . .98 Destacar Linha Baseada em uma Condição . . . . . . . . . . . . . . . . . . . . . .101 Condicional de Efeito Alternado em Linhas . . . . . . . . . . . . . . . . . . . . . .103 2.6 Formatar como Tabela . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 2.7 Segmentação de Dados em Tabelas . . . . . . . . . . . . . . . . . . . . . . . . . . .114 2.8 Estilos Pré-Configurados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 2.9 Células . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .116 Inserir e Excluir Células . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 Formatar Células . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 Alterar o Tamanho de Linhas e Colunas . . . . . . . . . . . . . . . . . . . . . . . . . 121 Relações Entre Largura e Altura da Célula . . . . . . . . . . . . . . . . . . . . . . .122 Ocultar Linhas e Colunas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 2.10 Edição . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 Atalho AutoSoma . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 Preencher Dados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 Limpar Planilha . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130 Classificar Dados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Lista Personalizada de Classificação . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Filtrar Dados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Localizar e Selecionar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143 Caracteres Coringa na Localização . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 Caractere: Interrogação . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 Caractere: Asterisco . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148 Caractere: TIL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150 Utilização de Caracteres Coringa em Fórmulas. . . . . . . . . . . . . . . . . . . 152 Utilização de Caracteres Coringa em Filtros . . . . . . . . . . . . . . . . . . . . .154 Ferramenta Ir Para . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156 Preenchimento de Células em Branco com o Ir Para . . . . . . . . . . . . . . 163
Capítulo 3:
Guia Inserir
3.1 Tabelas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 3.2 Ilustrações . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Inserindo Imagens no Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 Remover Plano de Fundo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 Edição de Contraste, Brilho, Cor e Efeitos em Imagens . . . . . . . . . . . . 176 Compactador de Imagens . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182 Organizando Imagens . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 Alinhamento Entre Imagens e Células . . . . . . . . . . . . . . . . . . . . . . . . . . 185 Agrupamento de Imagens . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186 Girar Imagens . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 Alterando Tamanho e Cortando Imagens . . . . . . . . . . . . . . . . . . . . . . .190 Trabalhando com Formas no Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196 Ícones SVG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198 SmartArt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199 Construindo um Organograma Hierárquico no Excel. . . . . . . . . . . . . .200 PrintScreen instantâneo no Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .204 3.3 Suplementos do Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Loja de Suplementos. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .205 Indicações de Suplementos Gratuitos . . . . . . . . . . . . . . . . . . . . . . . . . . 206 3.4 Hiperlink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210 3.5 Botão Interativo Utilizando Hiperlinks . . . . . . . . . . . . . . . . . . . . . . . . . 213 3.6 Texto no Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Caixa de Texto e WordArt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216 Caixa de Texto Dinâmica . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218 Cabeçalhos e Rodapés em Planilhas . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219 Linha de Assinatura Automática . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .222 3.7 Equações e Símbolos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225 Capítulo 4:
Tabela Dinâmica
4.1 Criando Tabelas Dinâmicas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.2 Cálculos Dentro da Tabela Dinâmica . . . . . . . . . . . . . . . . . . . . . . . . . . 4.3 Alterando o Layout dos Dados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.4 Alterando o Layout de Uma Tabela Dinâmica . . . . . . . . . . . . . . . . . . . 4.5 Nomes e Rótulos de Tabelas Dinâmicas . . . . . . . . . . . . . . . . . . . . . . . 4.6 Relatórios Filtrados Automaticamente . . . . . . . . . . . . . . . . . . . . . . . . . 4.7 Drill Down em Tabelas Dinâmicas . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.8 Agrupando Itens na Tabela Dinâmica . . . . . . . . . . . . . . . . . . . . . . . . . 4.9 Atualizar uma Tabela Dinâmica . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
229 237 243 249 251 252 254 255 261
4.10 Alterar Fonte de Dados de uma Tabela Dinâmica . . . . . . . . . . . . . . . 263 4.11 Reset, Seleção e Transferência da Tabela Dinâmica . . . . . . . . . . . . . 264 Capítulo 5:
Gráficos
5.1 Quando Utilizar um Gráfico? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269 5.2 Selecionando Dados Para Criação de Gráficos . . . . . . . . . . . . . . . . . . 274 5.3 Componentes de um Gráfico no Excel . . . . . . . . . . . . . . . . . . . . . . . . 282
Eixos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284 Títulos dos Eixos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .285 Título do Gráfico . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .286 Rótulos de Dados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287 Tabela de Dados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .290 Barra de Erros. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .291 Linhas de Grade. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .292 Legendas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295 Linhas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .295 Linha de Tendência . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .296 5.4 Tipos de Gráficos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Gráfico de Coluna . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .297 Gráfico de Barra . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .298 Gráfico de Linha . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .299 Gráfico de Combinação . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .300 Gráfico de Área . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .301 Gráfico de Pizza . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .302 Gráfico de Rosca . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .304 Gráfico de Superfície e Contorno . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .306 Gráfico de Radar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .308 Gráfico Mapa de Árvore . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .309 Gráfico Explosão Solar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310 Gráfico de Dispersão. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 Gráfico de Bolha . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315 Gráfico de Cascata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319 Gráfico de Histograma e Pareto. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .321 Gráfico de Ações . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324 5.5 Editando Aspectos Visuais do Gráfico . . . . . . . . . . . . . . . . . . . . . . . . . 326 5.6 Princípios Recomendados para Utilização de Gráficos . . . . . . . . . . . . 337 5.7 Gráficos Dinâmicos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341 5.8 Simplifique com MiniGráficos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
Capítulo 6: Guia Layout da Página 6.1 Temas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6.2 Configuração de Página Para Impressão . . . . . . . . . . . . . . . . . . . . . . . 6.3 Imprimindo no Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6.4 Exibição de Linhas de Grade e Títulos . . . . . . . . . . . . . . . . . . . . . . . . 6.5 Organizar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
354 357 363 366 367
Capítulo 7: Guia Fórmulas 7.1 Fórmulas e Funções São a Mesma Coisa? . . . . . . . . . . . . . . . . . . . . . . 370 7.2 Operadores no Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371 Operadores Aritméticos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371 Operadores Comparativos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372 Operadores Lógicos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372 Operadores de Concatenação . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373 Operadores de Referência . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373 Regras e Prioridade de Cálculos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374 7.3 Referências de Células . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375 Tipos de Estilo de Referência. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375 Localização na Referência . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376 Referências Absolutas e Relativas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379 7.4 Sintaxe das Funções . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382 7.5 Inserindo Funções no Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383 7.6 Utilizando o Assistente de Funções . . . . . . . . . . . . . . . . . . . . . . . . . . . 385 7.7 Compreendendo Erros em Fórmulas . . . . . . . . . . . . . . . . . . . . . . . . . . 387 7.8 Como Estudar as Funções . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388 7.9 Funções Matemáticas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389 Cálculos Matemáticos Simples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392 Somar Valores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .397 Definir Condições para Somar Valores . . . . . . . . . . . . . . . . . . . . . . . . . . 399 Somar Valores por Produtos de Matrizes . . . . . . . . . . . . . . . . . . . . . . . . 402 Calcular Subtotais . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .407 Arredondar Números . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411 Inserir Números Aleatoriamente . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415 Transformar um Número Negativo em Positivo . . . . . . . . . . . . . . . . . . 417 Converter Números Aritméticos Para Romanos . . . . . . . . . . . . . . . . . . 418 7.10 Funções Estatísticas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419 Contar Números e Valores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420 Contar Apenas Células Vazias . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422 Definir Condições para Contagem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423
Descobrir o Número Máximo e Mínimo do Intervalo . . . . . . . . . . . . . .425 Descobrir o Maior e Menor Número do Intervalo . . . . . . . . . . . . . . . . .427 Calcular a Média e Mediana de um Intervalo. . . . . . . . . . . . . . . . . . . . .429 Definir Condições Para Calcular a Média . . . . . . . . . . . . . . . . . . . . . . . .431 Calcular o Desvio do Padrão . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .434 Contar a Frequência de Números em um Intervalo . . . . . . . . . . . . . . .437 7.11 Funções de Lógica . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
Realizar Testes Lógicos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .440 Definir Condições em Testes Lógicos . . . . . . . . . . . . . . . . . . . . . . . . . .444 Adicionar Vários Testes Lógicos e Retornos . . . . . . . . . . . . . . . . . . . . . . 451 Tratar Erros com Testes Lógicos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454 7.12 Funções de Data e Hora . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457
Inserir Data e Hora Atual em Células. . . . . . . . . . . . . . . . . . . . . . . . . . . .458 Extrair Horas, Minutos e Segundos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461 Formar Horário Com Horas, Minutos e Segundos Separados . . . . . . .462 Extrair Dia, Mês e Ano de uma Data . . . . . . . . . . . . . . . . . . . . . . . . . . . .463 Formar Data Com Dia, Mês e Ano Separados . . . . . . . . . . . . . . . . . . . .463 Retornar o Último Dia do Mês de Uma Data . . . . . . . . . . . . . . . . . . . . .465 Descobrir o Dia da Semana de uma Data. . . . . . . . . . . . . . . . . . . . . . . .466 Extrair o Número da Semana do Ano . . . . . . . . . . . . . . . . . . . . . . . . . . .466 Calcular Número de Dias Úteis Entre Duas Datas . . . . . . . . . . . . . . . . .468 Adicionar ou Subtrair Dias Úteis de uma Data . . . . . . . . . . . . . . . . . . . . 471 Adicionar ou Subtrair Meses de uma Data . . . . . . . . . . . . . . . . . . . . . . .473 Fração de Anos Entre Duas Datas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 474 Fração do Tempo em um Dia . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475 7.13 Funções de Texto . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477
Remover Espaços Adicionais em Textos . . . . . . . . . . . . . . . . . . . . . . . . .478 Extrair Trechos de Textos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479 Localizar a Posição de Letras ou Palavras . . . . . . . . . . . . . . . . . . . . . . . .480 Contar Quantos Caracteres Uma Célula Têm . . . . . . . . . . . . . . . . . . . .485 Juntar Textos Em Células Diferentes . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487 Alterar Texto Para Maiúsculo e Minúsculo . . . . . . . . . . . . . . . . . . . . . . .488 Descobrir Se Dois ou Mais Textos São Exatamente Iguais . . . . . . . . . .489 Formatação de Número em Textos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 490 Retornar Apenas Textos e Ignorar Outros Formatos. . . . . . . . . . . . . . . 494 Substituir Textos com Funções . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 494 7.14 Funções de Pesquisa e Referência . . . . . . . . . . . . . . . . . . . . . . . . . . . 498
Procurar e Retornar Dados Em Diferentes Tabelas . . . . . . . . . . . . . . . .499 Procurar e Retornar Dados Em Diferentes Tabelas e Posições . . . . . .508
Procurar Vários Valores e Retornar o Correspondente . . . . . . . . . . . . . 513 Inverter Valores em Colunas Para Linhas e Vice-Versa . . . . . . . . . . . . . 516 Deslocar Células a Partir de uma Referência . . . . . . . . . . . . . . . . . . . . . 518 Retornar a Quantidade de Linhas e Colunas em Referências . . . . . . .522 Retornar o Número da Linha e Coluna da Posição . . . . . . . . . . . . . . . .523 Extrair Dinamicamente Valores de uma Tabela Dinâmica . . . . . . . . . . 524 Escrever a Função Utilizada na Célula . . . . . . . . . . . . . . . . . . . . . . . . . . . 528 Inserir Textos Específicos Aleatoriamente . . . . . . . . . . . . . . . . . . . . . . .529 Inserir Hiperlinks para Websites e Locais na Planilha e Windows . . . .530 Utilizar Textos como Referências . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531 7.15 Funções Informações . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 536
Retornar Informações Gerais do Windows e da Planilha . . . . . . . . . . .538 Verificar Se Célula é Par ou Ímpar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .544 Identificando e Utilizando Células em Branco . . . . . . . . . . . . . . . . . . . .546 7.16 Funções Financeiras . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 548
Descobrir a Taxa de Juros de um Empréstimo ou Investimento . . . . .549 Calcular o Valor da Parcela do Empréstimo . . . . . . . . . . . . . . . . . . . . .550 Calcular o Valor a Investir Para Acumular um Valor “X” . . . . . . . . . . . .552 Quanto Tempo Investir para Acumular um Valor “X” . . . . . . . . . . . . . .553 Calcular o Valor Futuro de uma Aplicação . . . . . . . . . . . . . . . . . . . . . . .555 Descobrir o Valor Inicial a Partir do Valor Atual do Investimento. . . . .558 Calcular a Viabilidade de Investimentos . . . . . . . . . . . . . . . . . . . . . . . . .560 7.17 Gerenciador de Nomes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 562 7.18 Auditoria de Fórmulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 572
Rastrear Células Precedentes e Dependentes em Fórmulas . . . . . . . . 572 Mostrar Todas as Fórmulas da Planilha . . . . . . . . . . . . . . . . . . . . . . . . . . 574 Ajuda na Verificação de Erros em Fórmulas . . . . . . . . . . . . . . . . . . . . . .575 Executar Fórmulas Por Etapas e Avaliar . . . . . . . . . . . . . . . . . . . . . . . . .578 7.19 Opções Cálculos de Fórmulas na Planilha . . . . . . . . . . . . . . . . . . . . . 579 7.20 Fórmulas Matriciais . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 581
Vantagens e Desvantagens das Fórmulas Matriciais . . . . . . . . . . . . . . . 581 Regras de Utilização . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .582 Criando Sua Primeira Fórmula Matricial . . . . . . . . . . . . . . . . . . . . . . . . .583 Expandindo Uma Fórmula Matricial . . . . . . . . . . . . . . . . . . . . . . . . . . . . 586 Matrizes Constantes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .589 Criando Loops Com Matriciais e Constantes . . . . . . . . . . . . . . . . . . . . .592 Função TRANSPOR Utilizando Matricial . . . . . . . . . . . . . . . . . . . . . . . . .595
Capítulo 8:
Guia Dados
8.1 Introdução ao Editor de Consultas. . . . . . . . . . . . . . . . . . . . . . . . . . . . 599 8.2 Consultar Dados do Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601 8.3 Consultar Dados da Pasta de Trabalho . . . . . . . . . . . . . . . . . . . . . . . . 604 8.4 Consultar Dados do Arquivo CSV . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606 8.5 Consultar Pastas e Combinar Planilhas . . . . . . . . . . . . . . . . . . . . . . . . 608 8.6 Consultar de Dados da Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615 8.7 Consultar de Dados do Facebook . . . . . . . . . . . . . . . . . . . . . . . . . . . . 618 8.8 Segmentação de Dados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 622 8.9 Atualização de Conexões e Consultas . . . . . . . . . . . . . . . . . . . . . . . . . 623 8.10 Propriedades da Conexão . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 626 8.11 Gerenciar Consultas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 627 8.12 Mesclar Consultas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629 8.13 Acrescentar Consultas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631 8.14 Alteração do Caminho da Fonte de Dados . . . . . . . . . . . . . . . . . . . . 632 8.15 Armazenamento de Dados no Excel . . . . . . . . . . . . . . . . . . . . . . . . . 633 Quando Utilizar o Access ao Invés do Excel. . . . . . . . . . . . . . . . . . . . . .636 Impor tar Dados do Excel para o Access . . . . . . . . . . . . . . . . . . . . . . . . 638 8.16 Ferramentas de Dados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645 Transformar Textos em Colunas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .646 Preenchimento Relâmpago . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651 Remover Dados Duplicados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .654 Validação de Dados: Criação e Configuração . . . . . . . . . . . . . . . . . . . .657 Validação de Dados: Gerenciamento . . . . . . . . . . . . . . . . . . . . . . . . . . .666 Validação de Dados com Dependência Dinâmica. . . . . . . . . . . . . . . . .668 Validação de Dados com Intervalo Dinâmico. . . . . . . . . . . . . . . . . . . . . 671 Consolidar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 675 8.17 Teste de Hipóteses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 680 Gerenciador de Cenários . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 681 Atingir Meta . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .688 8.18 Planilha de Previsão . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 690 8.19 Agrupamento por Tópicos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 697 8.20 Subtotais . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 706 Capítulo 9:
Guia Revisão
9.1 Ortografia . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9.2 Verificador de Acessibilidade . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9.3 Pesquisa Inteligente . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9.4 Traduzir Planilhas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
709 713 714 714
9.5 Comentários . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 716 Comentário com Outros Formatos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 718 9.6 Proteção de Células e Planilhas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 721 9.7 Permitir Que Os Usuários Editem Intervalos . . . . . . . . . . . . . . . . . . . . 726 9.8 Compartilhar Pasta de Trabalho Com Outros Usuários . . . . . . . . . . . . 730 9.9 Controlar Alterações em Células . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 733 Capítulo 10: Guia Exibir
10.1 Modos de Exibição das Planilhas . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10.2 Ocultar Elementos do Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10.3 Ampliação e Redução de Conteúdos (Zoom) . . . . . . . . . . . . . . . . . . 10.4 Organização de Janelas e Arquivos de Excel abertos. . . . . . . . . . . . 10.5 Congelamento de Painéis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
738 739 741 742 747
Capítulo 11: Guia Desenvolvedor
11.1 Suplementos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 753 Ferramentas de Análise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 754 Solver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 756 11.2 Controles de Formulários . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 764 11.3 Utilizando Controles de Formulários em Planilhas . . . . . . . . . . . . . . . 766 Criando um Formulário de Consulta . . . . . . . . . . . . . . . . . . . . . . . . . . . . 769 Criando um Formulário de Cadastro. . . . . . . . . . . . . . . . . . . . . . . . . . . . 771 11.4 Exportando Arquivos XML no Excel. . . . . . . . . . . . . . . . . . . . . . . . . . 773 Capítulo 12: Introdução ao Visual Basic For Application (VBA) Exibição de Macros e Extensão do Arquivo. . . . . . . . . . . . . . . . . . . . . .779 Segurança sobre Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 781
12.1 Gravando Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 784 Usando Referências Relativas ao Gravar Macros . . . . . . . . . . . . . . . . . . 787 12.2 Estrutura do IDE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 792 12.3 Estrutura e Elementos da Linguagem VBA . . . . . . . . . . . . . . . . . . . . 793 12.4 Variáveis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 796 12.5 If, Then, Else, ElseIf . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 800 Operadores Lógicos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .803 12.6 Select Case . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 806 12.7 With e End With . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 808 12.8 Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 809 Loop For . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 810 Loop For Each . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 812
Loop Do While . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 812
12.9 UserForms de Cadastro de Clientes. . . . . . . . . . . . . . . . . . . . . . . . . . 814 12.10 Materiais Para Aprender Mais VBA . . . . . . . . . . . . . . . . . . . . . . . . . . 829 Nota Especial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 830
MICROSOFT EXCEL DE A ATÉ XFD
16
PREFÁCIO Por Felipe Gualberto (Microsoft Excel MVP). Fiquei muito honrado em ser convidado a escrever neste espaço de um livro que foi preparado com tanto cuidado e carinho. Conheço a Karine há pouco tempo, já tive a satisfação de encontrá-la algumas vezes e desde então mantemos contato. Ela surpreendeu todos pela sua ascensão rápida nos meios de discussão de Excel e Power BI, justificados pelo seu interesse e competência. O Microsoft Excel é o software mais versátil do mundo. Com ele, você pode controlar desde suas finanças pessoais até gerenciar a construção de um shopping center. Ele está presente em centenas de países, dezenas de idiomas e instalado em cerca de um bilhão de máquinas. Os softwares de elaboração de planilhas eletrônicas começaram a ser comercializados no final dos anos 70 e tinham o objetivo principal de resolver problemas de contabilidade. Muita coisa aconteceu desde então até cheg armos na versão atual do Excel. Podemos dizer, seguramente, que a Microsoft investiu bilhões de dólares em homem-hora até o produto chegar no formato de hoje. Muitos profissionais e especialistas das mais diversas áreas - de matemáticos a designers - trabalharam várias horas para chegar à tela que você enxerga ao abrir o aplicativo hoje. A quantidade de elementos na interface do Excel pode intimidar um usuário iniciante. No entanto, a autora Karine escreveu os capítulos em uma sequência lógica fácil de acompanhar, pois seguem a ordem desses elementos da interface. Com isso, o leitor que não tiver tempo de ler o livro na íntegra poderá saltar até um capítulo específico para aprender um determinado comando de acordo com sua necessidade. Além disso, este livro possui um capítulo que dá uma base sólida ao usuário que deseja especializar posteriormente em outra solução da Microsoft que está tendo uma adoção forte no mercado: o Power BI. Atualmente, é muito interessante participar de seminários e encontros de entusiastas do Excel. Os perfis profissionais são os mais diferenciados: engenheiros, administradores, contadores, analistas de marketing, de inteligência de mercado, desenvolvedores e professores. A flexibilidade que o Excel proporciona consegue unir pessoas de diferentes formações e segmentos de mercado. Escrever um livro como este é um desafio para qualquer pessoa. A Karine tem uma grande paixão com o Excel, e o fato de ela ter aceitado este desafio é mais uma prova do seu entusiasmo e vasto conhecimento da plataforma. O revisor técnico do livro, Laennder Alves, é também um grande expoente de Excel no Brasil. Ao terminar de estudar este livro, você estará apto a resolver problemas que nunca imaginou que o Excel pudesse resolver. Aproveite!
17
COMO LER ESSE LIVRO Esse livro foi desenvolvido respeitando, conforme a possibilidade, a estrutura do Excel linearmente, caminhando e comentando as funcionalidades das guias e grupos
de ferramentas individualmente. Essa estratégia de ensino utilizada pela autora tem como objetivo vasculhar todas as ferramentas, apresentá-las, detalhá- las e ensiná-las.
Depois de ler o Microsoft Excel de A até XFD, seu nível de conhecimento sobre as possibilidades do Excel será elevado a um nível sistêmico e completo. A versão do Excel utilizada para escrevê-lo foi o Excel 2016 do Office 365 para Windows atualizado até julho de 2017. Devido às atualizações no software, há diferenças nas interfaces entre versões. Se você utilizar os arquivos práticos ou tentar executar técnicas ensinadas em versões antigas (por exemplo, Excel 2003, 2007, 2010 ou 2013), é importante ressaltar que as ferramentas, ícones e funções podem não
estar localizados onde são mostrados ou até mesmo não existirem. Os capítulos foram estruturados conforme as guias (“menus”) do Excel. Alguns assuntos importantes, como Gráficos e Tabelas Dinâmicas, ganharam capítulos
exclusivos por sua relevância. Cada guia é composta por diversos grupos de ferramentas dentro delas. Esses grupos foram detalhados linearmente em subcapítulos dentro dos capítulos de suas respectivas guias. Alguns destaques em palavras serão encontrados ao longo da leitura. Aquelas que
estiverem em negrito ressaltam nomes de ferramentas, janelas ou textos em interfaces do Excel. Palavras e números utilizados em exemplos também são formatados em negrito. Fórmulas e códigos no meio de parágrafos serão destacados com outro tipo de fonte, assim: SOMA. Fórmulas e códigos completos serão destacados em uma linha separada dessa forma: =SOMA(A1:C120)
Durante a leitura, dois tipos de destaques aparecerão em alguns capítulos. Quando deparar-se com o quadro PRATIQUE! significa que há uma pasta de trabalho de
exemplo disponibilizada juntamente com esse livro para fins de treinamento. É recomendável que sempre leia esse livro com o respectivo arquivo do capítulo aberto para aprender a técnica praticando. A prática é um dos “truques” para realmente aprender. Um exemplo de PRATIQUE! é esse: PRATIQUE! ARQUIVO: CAPÍTULO 7 – GRÁFICOS.XLSX
AMOSTRA CAPÍTULO SOBRE FÓRMULAS MICROSOFT EXCEL DE A ATÉ XFD
406
Os argumentos matriz1 e matriz2 na tabela anterior tem a formação conforme exemplificado na Figura 7–49. A matriz1 retorna o número 1 ( VERDADEIRO) para cada linha que continha Alberto e 0 (FALSO) para aquelas que não continham Alberto.
Figura 7–49: Exemplo de lógica realizada na função SOMARPRODUTO com condicional
A matriz2 não possui condições e, portanto, todos os valores do intervalo foram considerados. Quando a função SOMARPRODUTO executou a multiplicação entre as matrizes, as linhas com valores "zero" resultaram em "zero" na multiplicação. Ao somar o resultado individual das linhas, o resultado final da matriz foi igual a 71.000. Observe a utilização duplo sinal negativo antes da primeira matriz utilizada nessa fórmula: =SOMARPRODUTO(--(E11:E23 = “Alberto”);F11:F23)
Essa prática tem o efeito de fazer com que os resultados VERDADEIRO e FALSO sejam forçados a serem considerados como 1, para VERDADEIRO e 0, para FALSO. A multiplicação explicada anteriormente não seria possível se os resultados da matriz1 não fossem convertidos para 1 e 0, por isso, a conversão deles é fundamental para um resultado correto da função SOMARPRODUTO utilizando condicionais.
GUIA FÓRMULAS
407
Figura 7–50: Tradução de uma função sem a utilização dos -- e sua tradução ao utiliza-los.
CALCULAR SUBTOTAIS
A função SUBTOTAL é capaz de executar diversos cálculos matemáticos parametrizados nela com a característica de retornar um subtotal de um total geral. A utilidade dessa função está relacionada com o cálculo do total geral que, ao identificar que funções de subtotal foram utilizadas no meio de um intervalo, ela automaticamente as ignorará e retornará um somatório baseado nas outras células do intervalo. Sua sintaxe exige o número de uma função e a referência a ser calculada. =SUBTOTAL(núm_função;ref1;[ref2];...])
O argumento núm _ função deve ser preenchido com um dos números da Figura 7–51, de acordo com sua respec tiva lógica de cálculo. A função SUBTOTAL possui a capacidade de realizar onze tipos de cálculos diferentes, sendo que desses, é possível escolher se considerarão ou desconsiderarão valores em linhas
MICROSOFT EXCEL DE A ATÉ XFD
408
ocultas pelos números utilizando os códigos de
101 a 111.
Figura 7–51: Lista de funções que podem ser utilizadas na função SUBTOTAL
No exemplo da Figura 7–52, foi calculado o subtotal por categoria de linha de produto da tabela, totalizando a Categoria 1 em R$ 14.700, a Categoria 2 em R$ 19.200 e a Categoria 3 em R$ 70.400. Também foi utilizada a mesma função para totalizar o intervalo completo, na linha “Total com SUBTOTAL”.
Figura 7–52: Intervalo utilizado no SUBTOTAL
GUIA FÓRMULAS
409
Ao utilizar a função SUBTOTAL para totalizar todos os valores no intervalo, as células com a função SUBTOTAL serão ignoradas dessa soma. Observe na Figura 7–53 que, ao utilizar uma função de SOMA, o total do mesmo intervalo ( G28:G43) resultou em R$ 138.200, enquanto que, ao utilizar a função SUBTOTAL , o resultado foi R$ 104.300 (que é o valor correto, já que 14.700 + 19.200 + 70.400 resultam em 104.300). Uma das vantagens da SUBTOTAL é essa: na identificação de fórmulas que subtotalizam o intervalo e que não devem ser incluídas no somatório geral.
Figura 7–53: SUBTOTAL “Total” somando apenas valores que não tem SUBTOTAL
A função SUBTOTAL possui dois tipos de cálculos: aqueles que consideram células ocultas no cálculo (números da função de 1 a 11) e aquelas que ignoram células ocultas e as desconsideram do cálculo (números da função de 101 a 111). No exemplo da Figura 7–54, a função SUBTOTAL foi utilizada com o argumento núm _ função 109, que significa que serão somadas as referências informadas desconsiderando aquelas que estão ocultas nas linhas 56 e 57 nesse exemplo. O resultado da função foi de R$ 100.800, que corresponde as linhas de 51 a 55 e de 58 a 63. Já a função SOMA, apenas para efeitos de comparação, resultou em R$ 104.300, pois ela considerou as linhas ocultas do intervalo.
410
MICROSOFT EXCEL DE A ATÉ XFD
Figura 7–54: Subtotal somando apenas células visíveis
O mesmo comportamento ocorre em situações onde um intervalo de dados é filtrado. Ao utilizar os números de função de 101 a 111, células filtradas serão desconsideradas do cálculo na SUBTOTAL . No exemplo abaixo, a função configurada dessa
maneira resultou em uma soma do intervalo de R$ 85.900, enquanto que a função SOMA apresentou um resultado considerando aquelas células filtradas no intervalo (R$ 104.300).
Figura 7–55: Subtotal somando apenas células visíveis no filtro
GUIA FÓRMULAS
411
ARREDONDAR NÚMEROS A função ARRED arredonda números com decimais para a quantidade de dígitos especificados. Sua sintaxe exige a declaração do número a ser arredondado e a quantidade de dígitos que o arredondamento deve considerar. =ARRED(núm;núm_dígitos)
Na Figura 7–56 os valores da primeira coluna foram arredondados para uma casa decimal (depois da vírgula). Observe que o arredondamento do número 3,11 resultou em 3,10. Mesmo adicionando duas casas decimais depois da vírgula no formato do número, o arredondamento fez com que valor tivesse apenas um valor após a vírgula, resultado da função ARRED com uma casa decimal parametrizada.
Figura 7–56: Exemplos de usabilidade da função ARRED com uma casa d ecimal
Para arredondar um valor para um número inteiro, configure a função ARRED para zero casas decimais, conforme a Figura 7–57. Os arredondamentos utilizando essa função seguirão as regras de arredondamento padrão: números abaixo de 5 serão arredondados para baixo e aqueles acima de 5 serão arredondados para cima.
Figura 7–57: Exemplos de usabilidade da função ARRED com zero casas decimais
AMOSTRA CAPÍTULO SOBRE GRÁFICOS MICROSOFT EXCEL A ATÉ XFD
300
GRÁFICO DE COMBINAÇÃO O gráfico de combinação "combina" as características do gráfico de coluna com o gráfico de linha e são especialmente úteis para comparar duas categorias diferentes, mas que possuem relação direta, como por exemplo, valores de vendas versus a meta financeira de uma empresa, conforme exemplo abaixo.
Figura 5–49: Gráfico de combinação de colunas e linhas
A leitura do gráfico anterior pode ser feita da seguinte forma: "A meta financeira foi atingida nos meses de fevereiro, março, julho, agosto, novembro e dezembro. A maior diferença entre vendas e meta foi no mês de maio, seguido do mês de setembro e outubro. Os outros meses não mencionados, apesar de não terem atingido a meta, ficaram próximos de seu atingimento". Para criar o gráfico anterior, o conjunto de dados abaixo foi utilizado e sua constru ção é similar a um gráfico de linha ou de coluna.
Figura 5–50: Dados e configuração utilizada para criação do gráfico do exemplo
A única diferença de configuração do gráfico de combinação é que uma de suas séries é configurada com um outro tipo de gráfico. Na imagem abaixo, a série Vendas
GRÁFICOS
301
utiliza o gráfico de colunas agrupadas e a série Meta utiliza o gráfico de linhas com marcadores.
Figura 5–51: Configuração de um gráfico combinado de colunas e linhas
GRÁFICO DE ÁREA Os gráficos de área são iguais aos gráficos de linha, com a diferença que o espaço abaixo das linhas é preenchido por uma cor sólida que se sobrepõe entre as séries e, por isso, é chamado de gráfico de área. Escolha esse tipo de gráfico quando for necessário ressaltar uma mudança em um determinado período.
O gráfico abaixo utiliza a mesma tabela de dados do gráfico de linha. É nitidamente ressaltado que a performance do Produto 2 foi muito superior que a performance do Produto 1 entre os anos de 2018, 2019 e 2020.
Figura 5–52: Exemplo de gráfico de área
MICROSOFT EXCEL A ATÉ XFD
302
GRÁFICO DE PIZZA O tipo de gráfico de pizza tem como principal objetivo exibir a representatividade de categorias sobre a soma total dos valores, que representa 100% da proporção. Os valores individuais são relacionados a esse total, representando sua proporção sobre ele. Por exemplo, uma tabela de dados que compara as vendas de seis tipos de produtos como essa:
Figura 5–53: Valores de vendas por produto
Ao aplicar a tabela acima em um gráfico de pizza, o resultado pod e ser visualizado abaixo, demonstrando claramente a alta representação do Produto 5 e Produto 6 sobre os demais.
Figura 5–54: Exemplo de gráfico de pizza
GRÁFICOS
303
Para criar o gráfico acima, é como se fosse adicionada “implicitamente” uma coluna de proporção dos valores individuais sobre o total da tabela, como essa mostrada abaixo (a coluna Proporção foi adicionada apenas para fins de exemplo. Não é necessário adicioná-la para criar um gráfico de pizza).
Figura 5–55: Valores de vendas de produtos com proporção
Utilize o gráfico de pizza quando possuir até seis categorias de dados e precisar mostrar a proporção (ou peso) que cada uma dessas categorias representa sobre a soma delas juntas. Caso tenha dados com mais de seis categorias, uma solução é somar aquelas que são menos representativas e agrupá-las em uma categoria chamada Outros para não adicionar muitas categorias em um gráfico de pizza e torná-lo ilegível. Para destacar uma das fatias do gráfico, basta selecioná-lo e clicar duas vezes em cima da fatia que deseja selecionar. Depois, clique e arraste para fora do gráfico até que ela seja posicionada na posição de destaque desejada ( Figura 5–56).
Figura 5–56: Fatia destacada em um gráfico de pizza
MICROSOFT EXCEL A ATÉ XFD
304
O ângulo das fatias mostradas no gráfico também pode ser alterado. Ao clicar nas fatias, o painel de tarefas do lado direito do Excel será aberto com configurações exclusivas para esse tipo de gráfico. Em Opções de Série, é possível definir o Ângulo da primeira fatia, conforme mostrado na Figura 5–57. Observe que a fatia do Produto 5 está posicionada em outro local se comparada ao primeiro gráfico desse exemplo ( Figura 5–54).
Figura 5–57: Alteração do ângulo de um gráfico de pizza
GRÁFICO DE ROSCA O gráfico de rosca tem sua aplicabilidade semelhante ao gráfico de pizza, mas permite a utilização de duas séries diferentes em um mesmo gráfico. No exemplo abaixo, apenas uma série de dados foi utilizada mostrando a representatividade por produto.
Figura 5–58: Gráfico de rosca
