Apostila Excel Avancado (0).pdf

Excel Avançado

2013

Sidney Verginio da Silva

EXCEL AVANÇADO

Página | 1

Sobre o autor:

SIDNEY VERGINIO DA SILVA

Bacharel em Sistemas de Informação e Pós-Graduado MBA em Gestão de Tecnologia da Informação. Atua na área de Planejamento e Gestão de Processos e Projetos, Desenvolvimento de sistemas, Auditoria de Sistemas e Segurança de TI, também ministrando palestras e cursos sobre estes temas. Professor de cursos de graduação nas disciplinas de Programação de Computadores, Sistemas de Informação e Pacote Office.

©Todos os direitos deste material são reservados ao autor. Permitida a reprodução desde que citada a fonte.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 2

Prezado aluno(a),

Os softwares do tipo “Office” são cada vez mais presentes em nosso dia-a-dia. Seja para pesquisa, apresentação de trabalhos, relatórios ou qualquer outra atividade rotineira, utilizamos diariamente estas poderosas ferramentas. Especificamente sobre o Microsoft Excel, este está presente na vida de milhões de pessoas, seja para armazenamento e análise de dados, utilização para demonstrações financeiras, planejamento orçamentário, controles de estoque e caixa, enfim, muitas são aplicações deste software. Pessoas e empresas utilizam desta ferramenta para as mais diversas finalidades. Mais do que isso: já encontrei empresas que eram gerenciadas utilizando planilhas do Excel. É para atender a esta constante necessidade que apresento este Guia de Excel Avançado. Os diversos conteúdos aqui abordados visam fornecer a você conhecimento sobre as ferramentas mais utilizadas do Excel, visando proporcionar um arcabouço que certamente lhe ajudará muito em seu dia-a-dia. Nas próximas página você encontrará diversas explicações e exemplos sobre como utilizar as ferramentas do Excel. Explore a fundo o conteúdo deste Guia, implemente os exemplos citados e utilize toda sua criatividade para potencializar ainda mais suas rotinas. Um grande abraço e mãos à obra! Prof. Sidney Verginio da Silva [email protected]

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 3

Sumário 1 PLANILHA ELETRÔNICA ...................................................................................................................... 8 1.1 Componentes de uma planilha ................................................................................................................ 8 1.2 Os tipos de planilha ...................................................................................................................................10

2 MICROSOFT OFFICE EXCEL................................................................................................................ 11 2.1 O ambiente de trabalho do Excel ............................................................................................................11 2.2 Faixa de Opções .........................................................................................................................................14 2.3 O Botão Office ............................................................................................................................................26 2.4 Trabalhando com Planilhas ......................................................................................................................28 2.5 Colunas, Linhas e Células..........................................................................................................................31 2.6 Tipos De Dados ..........................................................................................................................................35 2.7 Formatação de Células – Dimensões......................................................................................................37 2.8 Formatação de Células - Layout e dados ...............................................................................................37 2.8.1 Estilos de Célula ................................................................................................................................... 43 2.9 Espaço de Trabalho....................................................................................................................................46

3 REFERÊNCIAS ABSOLUTAS E RELATIVAS ............................................................................................ 48

4 FÓRMULAS E FUNÇÕES .................................................................................................................... 50 4.1 Fórmulas e Funções Matemáticas ..........................................................................................................50 4.2 Funções pré-definidas ..............................................................................................................................54 4.2.1 SOMA ..................................................................................................................................................... 54 4.2.2 MÉDIA .................................................................................................................................................... 57 4.2.3 MULT ...................................................................................................................................................... 57 4.2.4 MÍNIMO ................................................................................................................................................. 58 4.2.5 MÁXIMO ................................................................................................................................................ 58 4.2.6 DIVISÃO .................................................................................................................................................. 59 4.2.7 EXPONENCIAÇÃO (Exponencial) E RAIZ ......................................................................................... 59 4.2.8 PORCENTAGEM .................................................................................................................................... 60 4.2.9 ARRED .................................................................................................................................................... 60 Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 4

4.2.10 TRUNCAR ............................................................................................................................................ 61 4.2.11 INT ........................................................................................................................................................ 61 4.2.12 SUBTOTAL ............................................................................................................................................ 61 4.2.12 CONT.VALORES ................................................................................................................................... 63 4.2.13 CONT.SE ............................................................................................................................................... 63 4.2.14 SOMASE............................................................................................................................................... 64 4.2.15 CONT.NÚM ......................................................................................................................................... 65 4.3 Correção de Erros ......................................................................................................................................66 4.4 Funções de Data/Hora ..............................................................................................................................66 4.5 Funções de Texto .......................................................................................................................................70 4.6 Funções Lógicas .........................................................................................................................................72 4.6.1 SE ............................................................................................................................................................. 72 4.6.2 Função E ................................................................................................................................................ 75 4.6.3 Função OU ............................................................................................................................................ 76 4.6 4 SE com OU............................................................................................................................................. 77

5 TABELAS DO EXCEL ........................................................................................................................... 78 5.1 Criar ou excluir uma tabela do Excel em uma planilha........................................................................80 5.2 Formatar uma tabela do Excel .................................................................................................................82

6 COMENTÁRIOS ................................................................................................................................. 89

7 NOMEANDO INTERVALOS ................................................................................................................. 91 7.1 Definir um nome........................................................................................................................................91 7.2 Gerenciador de Nomes .............................................................................................................................92

8 VÍNCULOS COM OUTRAS PLANILHAS OU ARQUIVOS ........................................................................ 94

9 VALIDAÇÃO DE DADOS...................................................................................................................... 96

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 5

10 FILTROS ........................................................................................................................................ 100 10.1 Filtrar Texto.............................................................................................................................................100 10.2 Filtrar números ......................................................................................................................................103 10.3 Filtrar datas ou horas ............................................................................................................................106 10.4 Filtrar por cor de célula, cor de fonte ou conjunto de ícones ........................................................107 10.5 Filtrar por seleção..................................................................................................................................107 10.6 Removendo valores duplicados ..........................................................................................................108

11 CLASSIFICAÇÃO............................................................................................................................. 110 11.1 Classificar texto ......................................................................................................................................110 11.2 Classificar números ...............................................................................................................................111 11.3 Classificar datas ou horas .....................................................................................................................112 11.4 Classificar cor de célula, cor de fonte ou ícones ..............................................................................112 11.5 Classificar linhas ....................................................................................................................................114 11.6 Por cor de célula, cor de fonte ou ícone de célula ...........................................................................115 11.7 Classificar por mais de uma coluna ou linha.....................................................................................115 11.8 Classificar uma coluna em um intervalo de células sem afetar outros ........................................117

12 FORMATAÇÃO CONDICIONAL........................................................................................................ 119 12.1 Formatar todas as células usando uma escala em duas cores.......................................................119 12.2 Formatar todas as células usando uma escala de três cores .........................................................122 12.3 Formatar todas as células usando barras de dados.........................................................................125 12.4 Formatar todas as células usando um conjunto de ícones.............................................................128 12.5 Formatar células com textos, números ou valores de data e hora ...............................................131 12.6 Formatar apenas valores mais altos ou mais baixos........................................................................134 12.7 Formatar apenas valores acima ou abaixo de uma média .............................................................136 12.8 Formatar apenas valores únicos ou duplicados ...............................................................................139 12.9 Regras Avançadas ..................................................................................................................................140 12.10 Localizar células com formatos condicionais ..................................................................................143

13 PROTEÇÃO DE PLANILHAS ............................................................................................................ 145 13.1 Proteger elementos de uma planilha .................................................................................................145 13.2 Ocultar fórmulas ....................................................................................................................................147 Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 6

13.3 Proteger elementos de uma pasta de trabalho ................................................................................148 13.4 Desproteger a planilha .........................................................................................................................149 13.5 Para bloquear apenas algumas células da planilha .........................................................................149

14 LOCALIZAÇÃO E SUBSTITUIÇÃO..................................................................................................... 151 14.1 Localizar ..................................................................................................................................................151 14.2 Substituir .................................................................................................................................................151

15 FUNÇÕES DE BANCO DE DADOS ................................................................................................... 153 15.1 PROCH .....................................................................................................................................................153 15.2 PROCV .....................................................................................................................................................155

16 SUBTOTAIS .................................................................................................................................... 161

17 ESTRUTURAS DE TÓPICOS............................................................................................................. 165

18 TABELA DINÂMICA ....................................................................................................................... 173 18.1 Criar ou excluir um relatório de tabela dinâmica ou de gráfico dinâmico ................................... 174 18.2 Trabalhando com um relatório de tabela dinâmica .........................................................................178 18.3 Gráfico dinâmico ...................................................................................................................................179

19 FORMULÁRIO DE DADOS .............................................................................................................. 185

20 CONTROLES DE FORMULÁRIOS..................................................................................................... 189 20.1 Caixa de seleção ....................................................................................................................................189 20.2 Botão de opção ......................................................................................................................................191 20.3 Caixa de listagem e caixa de combinação..........................................................................................192 20.4 Barras de rolagem e botões de rotação.............................................................................................196 20.5 Rótulos e Caixas de Texto .....................................................................................................................201 20.6 Caixa de grupo .......................................................................................................................................202

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 7

21 MACROS ....................................................................................................................................... 204 21.1 Gravar uma macro .................................................................................................................................204 21.2 Atribuir uma macro a um objeto, a um elemento gráfico ou a um controle ..............................205 21.3 Excluir uma macro .................................................................................................................................206 21.4 Exemplo de criação de macro .............................................................................................................206 21.5 Referências Relativas e Absolutas em macros ..................................................................................208

22 GRÁFICOS ..................................................................................................................................... 210 22.1 Elementos de um gráfico .....................................................................................................................211 22.2 Criar um gráfico básico .........................................................................................................................211 22.3 Alterar o Layout ou o Estilo de um Gráfico .......................................................................................215 22.4 Adicionar ou remover títulos ou rótulos de dados ..........................................................................217 22.5 Legendas .................................................................................................................................................220 22.6 Exibir ou ocultar eixos ou linhas de grade do gráfico ......................................................................221 22.7 Mover ou Redimensionar um Gráfico ................................................................................................223 22.8 Salvar e aplicar modelos de gráficos ..................................................................................................224 22.9 Opções avançadas de gráficos ............................................................................................................225 22.10 Gráfico de combinação ......................................................................................................................226 22.11 Imprimir um gráfico em uma planilha .............................................................................................231

23 CONFIGURAÇÃO E IMPRESSÃO DE PÁGINA ................................................................................... 233

FINALIZANDO .................................................................................................................................... 237

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 8

1 PLANILHA ELETRÔNICA

Uma Planilha eletrônica é um tipo de programa de computador que utiliza tabelas para realização de cálculos ou apresentação de dados. Cada tabela é formada por uma grade composta de linhas e colunas. O nome eletrônica se deve à sua implementação por meio de programas de computador. As planilhas são utilizadas principalmente para aplicações financeiras e pequenos bancos de dados.

1.1 Componentes de uma planilha Célula Elemento indicado pelo cruzamento entre uma linha e uma coluna. Células são o componente elementar de uma planilha eletrônica. Toda a informação, como valores e fórmulas, deve ser colocada em alguma célula para poder ser utilizada. Para identificarmos uma célula, normalmente utilizamos o nome da coluna seguido do nome da linha. Por exemplo, se tomarmos a coluna de nome A e a linha de número 10, neste cruzamento teremos a célula A10.

Valores São os dados possíveis de serem digitados nas células, como valores numéricos (números inteiros ou decimais, data, hora), textos e fórmulas (expressões e funções).

Fórmulas e Operadores Uma fórmula define como deve ser calculado o valor de uma célula. O conceito de fórmula é basicamente o conceito de fórmula matemática, e dá as planilhas eletrônicas seu principal motivo de existência: simplificar e calcular automaticamente valores. As fórmulas podem ser fórmulas aritméticas ou mais avançadas, usando funções internas do Excel. Essas funções oferecem desde mecanismos de simplificação da construção de fórmulas (como a função SOMA) até funções matemáticas ou estatísticas bastante complexas. Um exemplo de fórmula é "=A1+A2+A3+A4+A5", que pode ser substituída pela função"=SOMA(A1:A5)", que é mais prático de usar. Importante: toda fórmula ou função deve sempre iniciar com sinal de igual ( = ). Já os operadores são os símbolos matemáticos que operam cálculos. Podem ser Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 9

para realizar as operações básicas ou para testes lógicos.

Operador + * /

Ação Subtração Adição Multiplicação Divisão

Operador > >= < <= = <>

Operação Maior que Maior ou igual Menor que Menor ou igual Igual Diferente

Tal como na Matemática, os operadores possuem uma ordem de execução:

Prioridade 1ª 2ª 3ª

Operação Operadores relacionais Multiplicação e Divisão Adição e Subtração

Para alterar esta ordem, usamos parênteses. Por exemplo: = 6 + 5 * 1 → resulta em 11 = ( 6 + 6 ) * 1 → resulta em 12

Testes Lógicos

Testes lógicos utilizam operadores lógicos ( <, >, =, etc.) para criar alguma relação entre valores ou células, devolvendo sempre valores do tipo Verdadeiro ou Falso. Ou seja, dado um teste qualquer, este sempre retornará FALSO ou VERDADEIRO. Por exemplo, ao digitar a fórmula = 7 < 6 e teclar ENTER, a planilha eletrônica mostrará o valor FALSO, pois é FALSO que 7 é menor que 6.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 10

1.2 Os tipos de planilha Existem vários softwares de Planilha Eletrônica no mercado: pagos, gratuitos, para plataformas Windows ou Linux e até mesmo planilhas eletrônicas que podem ser utilizadas pela web, por meio do navegador de internet. A preferência pelo uso fica a cargo do usuário. Neste guia, serão abordadas as funções de planilhas eletrônicas utilizando o Microsoft Office Excel, pois esse é sem dúvida o softwares de planilha eletrônica mais utilizado no mundo.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 11

2 MICROSOFT OFFICE EXCEL O Microsoft Office Excel (nome completo do Excel) é um programa de planilha eletrônica produzido pela Microsoft para computadores que utilizam o sistema operacional Microsoft Windows e também computadores Macintosh da Apple. Seus recursos incluem uma interface intuitiva e ferramentas de cálculo e de construção de gráficos que, juntamente com marketing agressivo da Microsoft, tornaram o Excel um dos mais populares aplicativos de computador até hoje. É, com grande vantagem, o aplicativo de planilha eletrônica dominante desde a versão 5 em 1993 e sua inclusão como parte do Microsoft Office. A primeira versão do Excel foi lançada para o Mac em 1985 e a primeira versão para Windows foi lançada em novembro de 1987. A Lotus foi lenta em trazer o 1-2-3 (até então concorrente do Excel) ao Windows e, por volta de 1988, o Excel havia começado a passar o 1-2-3 em vendas e ajudou a Microsoft a alcançar a posição de liderança no desenvolvimento de software para o PC. A Microsoft aumentou sua vantagem com lançamento regular de novas versões, aproximadamente a cada dois anos. A versão atual para a plataforma Windows é o Excel 15, também chamado de Microsoft Excel 2013. O Microsoft Office Excel oferece uma série de recursos para analisar, comunicar, compartilhar e gerenciar informações, a fim de que sejam tomadas decisões mais bem informadas.

2.1 O ambiente de trabalho do Excel Um documento (arquivo) do Excel chama-se pasta; cada pasta pode ter uma ou mais planilhas. A predefinição do programa é a de criar automaticamente três planilhas em branco por cada pasta nova. É possível criar novas planilhas ou mesmo excluir planilhas em cada pasta de trabalho.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 12

Área de trabalho do Excel

A quadrícula virtual da folha de cálculo eletrônica consiste numa série de células que são fruto do cruzamento de linhas horizontais, numeradas de 1 até 1.048.576, e de colunas verticais, com denominações de A at é XFD, num total de 17.179.869.184 células, que podem conter números, datas, texto e/ou fórmulas e funções. Um poderoso módulo de geração de gráficos possibilita a apresentação e análise de dados de forma visual. Os gráficos produzidos pelo Excel podem ser usados na própria folha de cálculo, exportados para outras aplicações (como o Word) ou até gravados como imagens. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 13

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 14

2.2 Faixa de Opções O Excel traz em seu layout Guias onde é possível ter acesso a todos os seus recursos. Dentro das guias, temos os grupos de comandos e dentro destes os comandos que realizam as operações desejadas:

1 – Guias: Existem sete guias na parte superior. Cada uma representa tarefas principais executadas no Excel. 2 – Grupos: Cada guia tem grupos que mostram itens relacionados reunidos. 3 – Comandos: Um comando é um botão, uma caixa para inserir informações ou um menu.

Os principais comandos do Excel estão na primeira guia, a guia Início. Os comandos dessa guia são aqueles que a Microsoft identificou como os mais comumente usados quando as pessoas realizam tarefas básicas com planilhas. Por exemplo, os comandos Colar, Recortar e Copiar são organizados primeiramente na guia Início, no grupo Área de Transferência. Os comandos de formatação de fonte estão próximos, no grupo Fonte. Comandos para centralizar o texto ou alinhar texto à esquerda ou à direita estão no grupo Alinhamento e comandos para inserir e excluir células, linhas, colunas e planilhas estão no grupo Células. Os grupos reúnem todos os comandos necessários para um tipo específico de tarefa e, durante toda a tarefa, eles permanecem em exibição e disponíveis, em vez de serem ocultados em menus. Esses comandos vitais ficam visíveis acima do seu espaço de trabalho. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 15

GUIA INÍCIO

- Grupo Área de transferência

- Grupo Fonte

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 16

- Grupo Alinhamento

- Grupo Número

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 17

- Grupo Estilo

- Grupo Células

- Grupo Edição

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 18

GUIA INSERIR

- Grupo Tabelas

- Grupo Ilustrações

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 19

- Grupo Gráficos

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 20

- Grupo Texto

GUIA LAYOUT

- Grupo Temas

Altera o design geral do documento inteiro, incluindo cores, fontes e efeitos.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 21

- Grupo Configurar Página

- Grupo Dimensionar para ajustar

Realiza o dimensionamento manual para impressão do documento desejado

- Grupo Opções de Planilha

Opções relacionadas às linhas de grade e título das células em razão de impressão ou visualização Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 22

- Grupo Organizar

GUIA FÓRMULAS

- Grupo Funções

Grupo destinado a trabalhar com diversas modalidades de funções a serem trabalhadas na planilha

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 23

- Grupos Nomes

- Grupo Auditoria de Fórmulas

- Grupo Opções de cálculo

Opções relacionadas a como o Excel trabalhará com cálculos digitados na planilha.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 24

GUIA DADOS

- Grupo Obter Dados Externos

Grupo destinado ao trabalho de obter dados externos de programas como Access, internet, arquivo de texto ou outros softwares de dados.

- Grupo Conexões

Trabalha diretamente com a fonte de dados externa interligada a planilha do Excel

- Grupo Classificar e Filtrar

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 25

- Grupo Ferramentas de Dados

- Grupo Estrutura de Tópicos

Os comandos na Faixa de Opções são aqueles utilizados com mais freqüência. Em vez de mostrar todos os comandos a todo momento, o Excel 2007 mostra alguns comandos quando eles podem ser necessários, em resposta a uma ação efetuada. Por exemplo, se não houver um gráfico em uma planilha, os comandos para trabalhar com gráficos não são necessários. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 26

2.3 O Botão Office

Do lado esquerdo do ambiente de trabalho do Excel, está um botão de forma circular com o símbolo do Office. Nele, se agrupam muitas funcionalidades que antes sem encontravam no menu “Arquivo”, tais como Abrir, Salvar, Imprimir, etc. As três primeiras opções (Novo, Abrir e Salvar) e a última (Fechar) são diretas; as restantes, que têm uma pequena seta (triângulo) à frente, têm sub-opções.

Comandos do botão Office

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 27

Comando Novo: cria uma nova pasta de trabalho no Excel, seja ela em branco ou baseada em algum modelo pré-definido.

Comando Abrir: abre uma pasta de trabalho já salva anteriormente.

Comando Salvar: salva a pasta ativa no momento.

Comando Salvar como: exibe opções adicionais, do lado direito. Pode clicar diretamente no botão Salvar Como (ou a tecla F12) para abrir o diálogo de gravação. Para salvar sua pasta de trabalho de forma que a mesma seja compatível com versões anteriores do Excel (97-2003), use o comando “Salvar Como”.

Comando Imprimir: pode ser usado diretamente ou pressionando simultaneamente Ctrl+P, o que fará surgir a caixa de diálogo de impressão – o formato varia ligeiramente conforme a sua impressora. Pode também escolher, do lado direito, uma das opções relacionadas com a impressão, nomeadamente impressão rápida (ou seja, direto para a impressora, sem nenhum diálogo adicional) e pré-visualização da página a imprimir.

Comando Preparar: reúne funcionalidades relacionadas com as propriedades do documento (autor, título, etc.) e possibilidades de proteção, entre outras.

Comando Enviar: destina-se a enviar o seu documento para outra pessoa através de correio eletrônico ou fax – neste caso, através de um serviço de fax da internet .

Comando Publicar: é especialmente útil para utilizadores empresariais que possuam servidores ou outras áreas partilhadas onde os seus documentos possam ser usados por mais do que uma pessoa.

Comando Fechar: fecha o documento ativo e mantém o Excel aberto.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 28

2.4 Trabalhando com Planilhas No Excel, cada novo arquivo é uma “Pasta” e cada “Pasta” tem várias “Planilhas”. Estas “Planilhas” são a área de trabalho do Excel propriamente dita. Na barra inferior da área de trabalho do Excel pode-se verificar quais as planilhas que se encontram na pasta. A predefinição é de três planilhas com os nomes Plan1, Plan2 e Plan3. Para trabalhar com alguma planilha basta clicar sob seu nome.

É possível alterar a ordem da sua exibição, bastando para tal clicar num dos separadores e, sem largar o botão esquerdo do mouse, arrastá-lo para outra posição.

Inserir uma nova planilha Para inserir uma nova planilha, siga um destes procedimentos: 

Para inserir rapidamente uma nova planilha ao final das planilhas existentes,

clique na guia Inserir Planilha na parte inferior da tela. 

Para inserir uma nova planilha na frente de uma planilha existente, clique

com o botão direito do mouse na guia de uma planilha existente e, em seguida, clique em Inserir. Na guia Geral, clique em Planilha e clique em OK.

Inserir várias planilhas ao mesmo tempo 1.

Mantenha a tecla SHIFT pressionada e selecione o mesmo número de guias

de planilha existentes das planilhas a serem inseridas na pasta de trabalho aberta. Por exemplo, se desejar adicionar três novas planilhas, selecione três guias de planilha das planilhas existentes. 2.

Na guia Página Inicial, no grupo Células, clique em Inserir e, em seguida,

clique em Inserir Planilha.

Renomear uma planilha 1.

Na barra da Guia de planilha, clique com o botão direito do mouse na guia

da planilha que deseja renomear e clique em Renomear.

2.

Digite o novo nome e tecle ENTER. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 29

Excluir uma ou mais planilhas 1.

Selecione a(s) planilha(s) que deseja excluir.Clique com o botão direito na

guia de uma ou mais planilhas que deseja excluir e, em seguida, clicar em Excluir.

Mover ou copiar uma planilha É possível mover ou copiar uma pasta de trabalho para outro local de uma pasta de trabalho (arquivo do Excel) ou para outra pasta de trabalho. Entretanto, tenha cuidado ao mover ou copiar uma planilha: os cálculos ou gráficos que estão baseados nos dados da planilha podem se tornar imprecisos se a planilha for movida. Da mesma forma, se uma planilha movida ou copiada for inserida entre as planilhas mencionadas por uma referência de fórmula 3Dos dados dessa planilha poderão ser incluídos no cálculo. 1.

Para mover ou copiar planilhas para outra pasta de trabalho, verifique se a

pasta de trabalho está aberta no Microsoft Office Excel. 2.

Na pasta de trabalho que contém as planilhas a serem movidas ou copiadas,

selecione as planilhas. 3.

Clique com o botão direito do mouse em uma guia de planilha selecionada e,

em seguida, clicar em Mover ou Copiar no menu de atalho. 4.

Na lista Para pasta, siga um destes procedimentos:



Clique na pasta de trabalho para a qual moverá ou copiará as planilhas

selecionadas. 

Clique em nova pasta a fim de mover ou copiar as planilhas selecionadas

para uma nova pasta de trabalho. 5.

Na lista Antes da planilha, siga um destes procedimentos: 

Clique na planilha antes da qual se deseja inserir as planilhas movidas ou

copiadas. 

Clique em mover para o fim para inserir as planilhas movidas ou copiadas

após a última planilha da pasta de trabalho e antes da guia Inserir Planilha. 6.

Para copiar as planilhas em vez de movê-las, marque a caixa de seleção

Criar uma cópia.

Para mover planilhas na pasta de trabalho atual, arraste as planilhas selecionadas ao longo da linha das guias das planilhas. Para copiá-las, mantenha pressionada a tecla CTRL e arraste as planilhas; solte o botão do mouse antes de soltar a tecla CTRL. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 30

Ocultar ou exibir planilhas ou pastas de trabalho

É possível ocultar qualquer planilha em uma pasta de trabalho para removê-la do modo de exibição. Também é possível ocultar a janela de uma pasta de trabalho para removê-la do seu espaço de trabalho. Os dados nas janelas de planilhas e pastas de trabalho ocultas não ficam visíveis, mas ainda poderão ser referenciados de outras planilhas e pastas de trabalho. É possível pode exibir janelas de planilhas ou pastas de trabalho ocultas conforme o necessário. Por padrão, todas as janelas de pastas de trabalho abertas serão exibidas na barra de tarefas, mas será possível ocultá-las ou exibi-las nessa barra conforme o necessário.

Ocultar uma planilha 1.

Selecione as planilhas que deseja ocultar.

2.

Na guia Início, no grupo Células, clique em Formatar.

3.

Em Visibilidade, clique em Ocultar e Reexibir e clique em Ocultar

Planilha.

Exibir uma planilha oculta 1.

Na guia Início, no grupo Células, clique em Formatar.

2.

Em Visibilidade, clique em Ocultar e Reexibir e clique em Reexibir

Planilha. 3.

Na caixa Reexibir planilha, clique duas vezes no nome da planilha oculta

que deseja exibir.

Ocultar uma janela de pasta de trabalho 

Na guia Exibir, no grupo Janela, clique em Ocultar.

Ao sair do Excel, será exibida uma solicitação para confirmar se as alterações deverão ser salvas na janela de pasta de trabalho oculta. Clique em Sim se desejar que a janela da pasta de trabalho esteja oculta na próxima vez em que for abrir a pasta de trabalho. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 31

Exibir uma janela de pasta de trabalho oculta 1.

Na guia Exibir, no grupo Janela, clique em Reexibir.

Se a opção Reexibir não estiver disponível, a pasta de trabalho não conterá janelas de pastas de trabalho ocultas. 2.

Em Reexibir pasta de trabalho, clique duas vezes na janela de pasta de

trabalho que se deseja exibir.

2.5 Colunas, Linhas e Células A área de trabalho do Excel é uma “folha” quadriculada formada pela interseção de linhas com designações numéricas (do lado esquerdo) e de colunas designadas por letras (no topo da folha).

Numa planilha com milhares de células poderá ser difícil encontrar a célula desejada. Para isso o Excel possui uma caixa de nome, onde pode é possível digitar diretamente a célula para onde pretende ir (seguido de Enter). Por exemplo, ao digitar na caixa de nome o valor B$, o cursor é movido para esta célula.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 32

Selecionar, cortar, copiar e colar

No Excel, as funções de seleção, corte, cópia e colagem de dados são parecidas com as do Microsoft Word. Quando pretende-se copiar um determinado valor que se encontra numa célula, valor esse que foi obtido de forma automática pelo Excel, através de uma fórmula, precisamos saber o que realmente queremos copiar: apenas o valor ou a fórmula? E, como as células podem ter comentários anexados, pretendemos também copiá-los? A primeira coisa que tem de saber é que a cópia de elementos de uma folha de cálculo funciona, à princípio, como a cópia de quaisquer outros elementos num documento de texto, por exemplo. Isto é, tudo o que aprendeu sobre clicar, arrastar o mouse e soltar, para selecionar uma área num texto, aplica-se igualmente numa folha de cálculo: clique numa célula e, sem largar o mouse, arraste-o na horizontal e na vertical, de forma a selecionar a área que pretende.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 33

E para selecionar células que não são contínuas basta, ao realiza as seleções com o mouse, pressionar a tecla Ctrl do teclado. Na figura acima, foram selecionadas as células de B5 até D5 e de B8 até D8. Note como nas réguas de designação das linhas e colunas há uma mudança de cor para ajudar a visualizar as células selecionadas. Depois de selecionadas as células pretendidas, é possível copiá-las para qualquer outra zona da mesma planilha ou até para outra planilha da mesma pasta ou noutra pasta de trabalho (arquivo) qualquer. É possível utilizar as teclas convencionais para copiar (Ctrl+C), cortar (Ctrl+X) e colar (Ctrl+V) ou recorrer ao menu de contexto com o botão direito do mouse. Este último é preferível no caso de pretender utilizar comando Colar especial:

Ao selecionar um conjunto de células, e depois de escolher a opção Copiar, surge de imediato, na célula copiada, uma borda tracejada em movimento que indica claramente qual a área a copiar. Para inserir os dados copiados, clique com o botão direito do mouse, na célula de destino e escolha a opção Colar. Uma vez que as células podem conter mais do que apenas dados simples, o comando Colar Especial permite escolher exatamente o que pretende-se colar. Clicando com o botão direito do mouse na célula de destino, e escolhendo a opção Colar Especial, surge uma caixa de diálogo que permite escolher o que deseja-se colar.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 34

Inserir e apagar linhas e colunas Pode ser necessário acrescentar linhas e colunas numa planilha onde já haja muitos dados e fórmulas.

1. Para introduzir uma linha no meio de valores já introduzidos, comece por clicar no número da linha, do lado esquerdo da folha. A nova linha será inserida acima da linha selecionada. Ou seja, a nova linha vai assumir a mesma numeração da linha selecionada e todos os valores descem uma posição.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 35

2. Depois, clique com o botão direito do mouse, e escolha a opção Inserir. O resultado é uma linha nova que, contudo, não altera os resultados anteriores. Muito embora estas células de totais tenham fórmulas com referências a determinadas células, e estas tenham mudado de posição, o Excel muda as fórmulas automaticamente, evitando assim uma alteração dos resultados. O mesmo processo é válido para as colunas.

2.6 Tipos De Dados Como já mencionado, é possível inserir 4 tipos de dados nas células:

- Numéricos: valores numéricos que podem ser introduzidos diretamente pelo usuário ou gerados automaticamente, através de fórmulas.

- Texto: valores não numéricos que Excel trata como sendo apenas texto. Regra geral, tudo o que o Excel não reconheça e trate como dados numéricos é assumido como texto.

- Datas e horas: dados numéricos ou alfanuméricos (conjugação de algarismos e letras) que são reconhecidos pelo Excel como data e/ou hora e que podem ate ser formatados de forma a serem automaticamente atualizados de acordo com a data real.

- Fórmulas: as fórmulas são o aspecto mais importante do Excel, pois é através delas que realizamos cálculos.

Introdução de dados Para Introduzir dados numa célula basta selecionar a célula pretendida com o mouse ou teclado, digitar os valores pretendidos e teclar ENTER. Os valores introduzidos na célula surgem também na Barra de fórmulas e vice-versa, o que permite introduzir os Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 36

valores a partir de qualquer destes pontos. Uma vez introduzido o valor pretendido, basta mudar o cursor para outro local, clicando noutra célula ou “saltar” de célula usando as teclas direcionais do teclado.

Introdução de dados num conjunto de células Pode também repetir a introdução de dados num conjunto mais alargado de células, sem ter de repetir célula a célula. Para isso basta selecionar as células pretendidas, introduzir o valor numa delas e concluir pressionando CTRL+ENTER. Introdução de séries de dados – dados repetidos Ocorre quando deseja-se introduzir os mesmos dados em mais do que uma célula, isto é, células que se estendem para cima, para baixo ou para cada um dos lados da célula selecionada. Para isso: 1. Introduza o valor pretendido numa célula à sua escolha.

2. Posicione o cursor no canto inferior direito da célula, de modo a que o cursor se transforme numa cruz.

3. Arraste o cursor para os lados, para cima ou para baixo de forma preencher as células com o mesmo valor. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 37

Note como surge um filete cinzento em torno das células afetadas pela sua seleção ou mesmo tempo que uma pequena caixa indica qual o valor que vai ser introduzido.

2.7 Formatação de Células – Dimensões Uma vez introduzidos novos valores nas células, estes poderão não caber no espaço predefinido. No Excel 2007 isto acontece quando a largura da coluna é inferior ao comprimento dos dados inseridos em alguma de suas células. Neste caso, o Excel apresenta a célula com o caractere #. Para alterar a largura da colua:

1. Posicione o mouse entre colunas até surgir o símbolo

;

2. Clique e arraste até a largura pretendida; 3. Solte o botão do mouse

O mesmo processo pode ser feito para as linhas. Neste caso, ao posicionar o cursor na base da linha 6 e arrastá-lo, muda-se a altura desta linha

2.8 Formatação de Células - Layout e dados Este recurso é útil para dar ênfase a determinados valores numa planilha ou apresentar de forma mais organizada os dados armazenados nas células. A forma mais simples de realizar isto é recorrer ao grupo de comandos Fonte da Guia Início. A atribuição de formatos é sempre feita após a seleção de células ou de grupo de células a formatar. Pode-se fazer várias formatações básicas de forma rápida apenas Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 38

com o mouse. Dentre eles: bordas, fonte (tamanho, tipo de letra e cor), alinhamento do texto na células, recuo, etc. A caixa de diálogo Formatar Células permite alterar o formato de números e do texto nas células selecionadas de uma planilha. Para acessar, selecione a(s) célula(s) que deseja alterar e clique com o botão direito -> Formatar células. Também é possível clicar na seta do grupo Fonte, Alinhamento ou Número.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 39

GUIA NÚMERO

Use as opções na guia Número para aplicar um formato de número específico aos números nas células da planilha. 

Categoria Clique em uma opção na caixa Categoria e selecione as opções

desejadas para especificar um formato de número. A caixa Exemplo mostra a aparência das células selecionadas com a formatação escolhida. Clique em Personalizado se quiser criar os seus próprios formatos personalizados para números, como códigos de produtos. Clique em Geral se quiser retornar para um formato de número não específico. 

Exemplo: Exibe o número na célula ativa na planilha de acordo com o formato

de número selecionado. 

Casas decimais: Especifica até 30 casas decimais. Esta caixa está disponível

apenas para as categorias Número, Moeda, Contábil, Porcentagem e Científico. 

Usar separador de milhar (,): Marque esta caixa de seleção para inserir um

separador de milhar. Esta caixa de seleção está disponível apenas para a categoria Número. 

Números negativos: Especifica o formato no qual deseja que os números

negativos sejam exibidos. Esta opção está disponível apenas para as categorias Número e Moeda. 

Símbolo: Selecione o símbolo da moeda que deseja usar. Esta caixa está

disponível apenas para as categorias Moeda e Contábil. 

Tipo: Selecione o tipo de exibição que deseja usar para um número. Essa lista

está disponível apenas para as categorias Data, Hora, Fração, Especial e Personalizado. 

Localidade (local) Selecione um idioma diferente que deseja usar para o tipo

de exibição de um número. Esta caixa de listagem está disponível apenas para as categorias Data, Hora e Especial.

GUIA ALINHAMENTO

Use as opções na guia Alinhamento para alterar o alinhamento do conteúdo da célula, posicionar o conteúdo na célula e alterar a direção desse conteúdo.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 40

Alinhamento de Texto 

Horizontal: Selecione uma opção na lista Horizontal para alterar o alinhamento

horizontal do conteúdo das células. Por padrão, o Microsoft Office Excel alinha texto à esquerda, números à direita, enquanto os valores lógicos e de erro são centralizados. O alinhamento horizontal padrão é Geral. As alterações no alinhamento dos dados não alteram os tipos de dados. 

Vertical: Selecione uma opção na caixa de listagem Vertical para alterar o

alinhamento vertical do conteúdo das células. Por padrão, o Excel alinha o texto verticalmente na parte inferior das células. O alinhamento vertical padrão é Geral. 

Recuo: Recua o conteúdo das células a partir de qualquer borda da célula,

dependendo das opções escolhidas em Horizontal e Vertical. Cada incremento na caixa Recuo equivale à largura de um caractere. 

Orientação: Selecione uma opção em Orientação para alterar a orientação do

texto nas células selecionadas. As opções de rotação poderão não estar disponíveis se forem selecionadas outras opções de alinhamento. 

Graus: Define o nível de rotação aplicado ao texto na célula selecionada. Use

um número positivo na caixa Graus para girar o texto selecionado da parte inferior esquerda para a superior direita na célula. Use graus negativos para girar o texto da parte superior esquerda para a inferior direita na célula selecionada.

Controle de texto 

Quebrar texto automaticamente: Quebra o texto em várias linhas dentro de

uma célula. O número de linhas depende da largura da coluna e do comprimento do conteúdo da célula. 

Reduzir para caber: Reduz o tamanho aparente dos caracteres da fonte para

que todos os dados de uma célula selecionada caibam dentro da coluna. O tamanho dos caracteres será ajustado automaticamente se alterar a largura da coluna. O tamanho de fonte aplicado não será alterado. 

Mesclar Células: Combina duas ou mais células selecionadas em uma única

célula. A referência de célula de uma célula mesclada será a da célula superior esquerda da faixa original de células selecionadas.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO 

Página | 41

Direção do Texto: Selecione uma opção na caixa Direção do Texto para

especificar a ordem de leitura e o alinhamento. A configuração padrão é Contexto, mas é possível alterá-la para Da Esquerda para a Direita ou Da Direita para a Esquerda.

GUIA FONTE

Use as opções na guia Fonte para alterar a fonte, o estilo de fonte, o tamanho da fonte e outros efeitos de fonte. 

Fonte: Selecione o tipo da fonte para o texto nas células selecionadas. A fonte

padrão é Calibri. 

Estilo da Fonte: Selecione o estilo da fonte para o texto nas células

selecionadas. O estilo de fonte padrão é Normal. 

Tamanho: Selecione o tamanho da fonte para o texto nas células selecionadas.

Digite qualquer número entre 1 e 1.638. O tamanho de fonte padrão é 11. Os tamanhos disponíveis na lista Tamanho dependem da fonte selecionada e da impressora ativa. 

Sublinhado: Selecione o tipo de sublinhado que deseja usar para o texto nas

células selecionadas. O sublinhado padrão é Nenhum. 

Cor: Selecione a cor que deseja usar para as células ou o texto selecionados. A

cor padrão é Automático. 

Fonte Normal: Marque a caixa de seleção Fonte Normal para redefinir o estilo,

o tamanho e os efeitos da fonte com o estilo Normal (padrão). 

Efeitos: Permite que sejam selecionados um dos seguintes efeitos de

formatação: - Tachado Marque esta caixa de seleção para exibir o texto em células selecionadas como tachado. - Sobrescrito Marque esta caixa de seleção para exibir o texto em células selecionadas como sobrescrito. - Subscrito Marque esta caixa de seleção para exibir o texto em células selecionadas como subscrito. 

Visualização Veja um exemplo de texto que é exibido com as opções de

formatação que selecionadas.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 42

GUIA BORDA

Use as opções na guia Borda para aplicar uma borda ao redor de células selecionadas em um estilo e uma cor. 

Linha Selecione uma opção em Estilo para especificar o tamanho e o estilo de

linha de uma borda. Para alterar o estilo de linha de uma borda já existente, selecione a opção de estilo de linha desejada e clique na área da borda no modelo de Borda onde quiser que o novo estilo de linha seja exibido. 

Predefinições: Selecione uma opção de borda predefinida para aplicar bordas

nas células selecionadas ou removê-las. 

Cor: Selecione uma cor da lista para alterar a cor das células selecionadas.



Borda: Clique em um estilo de linha na caixa Estilo e clique nos botões em

Predefinições ou em Borda para aplicar as bordas nas células selecionadas. Para remover todas as bordas, clique no botão Nenhuma. Note que também é possível clicar nas áreas da caixa de texto para adicionar ou remover bordas.

GUIA PREENCHIMENTO

Use as opções na guia Preenchimento para preencher as células selecionadas com cores, padrões e efeitos de preenchimento especiais. 

Plano de Fundo: Selecione uma cor de plano de fundo para células

selecionadas usando a paleta de cores. 

Efeitos de preenchimento: Selecione este botão para aplicar gradiente, textura

e preenchimentos de imagem em células selecionadas. 

Mais Cores Selecione este botão para adicionar cores que não estão

disponíveis na paleta de cores. 

Cor do Padrão: Selecione uma cor de primeiro plano na caixa Cor do Padrão

para criar um padrão que usa duas cores. 

Estilo do Padrão: Selecione um padrão na caixa Estilo do Padrão para

formatar células selecionadas com um padrão que usa as cores que são selecionadas nas caixas Cor de Plano de Fundo e Cor Padrão. 

Exemplo: Veja um exemplo das opções de cor, efeitos de preenchimento e de

padrões que selecionar.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 43

GUIA PROTEÇÃO

Use as opções na guia Proteção para bloquear ou ocultar células antes de proteger a planilha. 

Bloqueadas Impede que as células selecionadas sejam alteradas, movidas,

redimensionadas ou excluídas. O bloqueio das células apenas terá efeito quando a planilha estiver protegida. 

Ocultas: Oculta uma fórmula em uma célula para que ela não seja exibida na

barra de fórmulas quando a célula for selecionada. Se selecionar essa opção, ela apenas terá efeito depois que planilha estiver protegida.

Depois de bloquear e ocultar os dados nas células selecionadas, faça o seguinte para proteger a planilha: 1. Na guia Revisão, no grupo Alterações, clique em Proteger Planilha. 2. Certifique-se de que a caixa de seleção Proteger a planilha e o conteúdo de células bloqueadas esteja marcada. 3. Na caixa Permitir que todos os usuários desta planilha possam, selecione as atividades que os usuários poderão executar em uma planilha protegida que contém células bloqueadas.

2.8.1 Estilos de Célula Um estilo de célula é um conjunto definido de características de formatação, como fontes e tamanhos de fonte, formatos de número, bordas de célula e sombreamento de célula.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 44

O Microsoft Office Excel possui vários estilos internos de célula que podem ser aplicados ou modificados. Também é possível modificar ou duplicar um estilo de célula para criar um estilo de célula personalizado.

Aplicar um estilo de célula 1. Selecione as células que deseja formatar. 2. Na guia Início, no grupo Estilos, clique em Estilos de Célula.

3. Clique no estilo de célula que deseja aplicar.

Criar um estilo de célula personalizado

1. Na guia Início, no grupo Estilos, clique em Estilos de Célula. 2. Clique em Novo Estilo de Célula. 3. Na caixa Nome do estilo, digite um nome apropriado para o novo estilo de célula. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 45

4. Clique em Formatar. 5. Nas várias guias da caixa de diálogo Formatar Células, selecione a formatação que deseja e então clique em OK. 6. Na caixa de diálogo Estilo, em Estilo Inclui (Por Exemplo), desmarque as caixas de seleção de qualquer formatação que não deseja incluir no estilo de célula.

Criar um estilo de célula modificando o estilo de célula existente

1. Na guia Início, no grupo Estilos, clique em Estilos de Célula. 2. Siga um destes procedimentos: 

Para modificar um estilo de célula existente, clique com o botão direito do

mouse no estilo de célula e então clique em Modificar. 

Para criar uma duplicata do estilo de célula existente, clique com o botão

direito do mouse no estilo de célula e então clique em Duplicar.

3. Na caixa Nome do estilo, digite um nome apropriado para o novo estilo de célula. Um estilo de célula duplicada e um estilo de célula renomeado são adicionados à lista de estilos de célula personalizados. Se não renomear o estilo de célula interno, ele será atualizado com as alterações que forem efetuadas. 4. Para modificar o estilo de célula, clique em Formato. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 46

5. Nas várias guias da caixa de diálogo Formatar Células, selecione a formatação que deseja e então clique em OK. 6. Na caixa de diálogo Estilo, em Estilo Inclui, marque ou desmarque as caixas de seleção de qualquer formatação que deseja ou não incluir no estilo de célula.

Remover um estilo de célula dos dados da planilha Este procedimento “limpa” a formatação de estilo que foi dada a uma célula. 1. Selecione as células formatadas com o estilo de célula que você deseja remover. 2. Na guia Início, no grupo Estilos, clique em Estilos de Célula. 3. Em Bom, Ruim e Neutra, clique em Normal.

Excluir um estilo de célula predefinido ou personalizado

Pode-se excluir um estilo de célula predefinido ou personalizado para removê-lo da lista de estilos de célula disponíveis. Quando se exclui um estilo de célula, ele também é removido de todas as células que estão formatadas com ele. 1. Na guia Início, no grupo Estilos, clique em Estilos de Célula. 2. Para excluir um estilo de célula predefinido ou personalizado e removê-lo de todas as células formatadas com ele, clique com o botão direito no estilo da célula e clique em Excluir.

2.9 Espaço de Trabalho É possível salvar um instantâneo do layout atual de todas as janelas de pasta de trabalho abertas e organizadas em um arquivo de espaço de trabalho (.xlw). Quando um arquivo de espaço de trabalho for aberto, o Microsoft Office Excel abrirá todas as pastas de trabalho e as exibirá no layout que foi salvo, tal como uma fotografia das pastas e planilhas abertas. Para isso, 1. Abra as pastas de trabalho que deseja salvar em um espaço de trabalho. 2. Organize as janelas de pasta de trabalho exatamente como deseja exibi-las ao abrir o espaço de trabalho, alterando o tamanho de cada janela de pasta de trabalho conforme necessário. 3. Na guia Exibir, no grupo Janela, clique em Salvar Espaço de Trabalho. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 47

4. Na caixa Nome do arquivo, digite um nome para o arquivo de espaço de trabalho. O nome padrão é resume.xlw, mas é possível renomeá-lo. 5. Ao abrir o arquivo salvo, o Excel mostrará as planilhas organizadas tal como foram salvas no espaço de trabalho.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 48

3 REFERÊNCIAS ABSOLUTAS E RELATIVAS

Ao copiar uma fórmula =A1*B1 para as linhas abaixo, o Excel entende que as fórmulas devem ser relativas, ou seja, mudam de acordo com as linhas. No exemplo usado =A1*B1 sendo relativas, mudam para =A2*B2, =A3*B3 e assim por diante. Mas quando um determinado endereço não deve ser alterado (ou seja, absoluto), antes de copiar deve-se usar o símbolo $ (ou pressionar F4) na fórmula que deseja-se copiar.

Exemplo: Na fórmula =A1*B1 é desejável que, ao copiar para as linhas abaixo o endereço B1 permaneça absoluto, ou fixo: =A1*B$1.

Notee que o símbolo $ está antes do número 1 que identifica a linha. Depois de copiado, as fórmulas ficam assim: =A2*B$1 =A3*B$1 =A4*B$1 =A5*B$1

Ao copiar uma fórmula para outras colunas, a regra é a mesma, ou seja, deve-se colocar o símbolo $ na fórmula na frente da letra que identifica a coluna que deve permanecer fixa. Usando o exemplo =A1*A2, ao copiar para as outras colunas, as demais ficariam: =B1*B2 =C1*C2 =D1*D2

Colocando o símbolo $ antes do A1, =$A1*A2, depois de copiado, ficaria: =$A1*B2 =$A1*C2 =$A1*D2

Para fixar a coluna e a linha usa-se =$A$1*A2, pois assim, ao copiar esta fórmula Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 49

para baixo ou para o lado na planilha, o endereço $A$1 sempre ficará fixo. Note no exemplo abaixo que, ao copiar a fórmula para as linhas abaixo, o Excel atualizou a fórmula de acordo com as referências das linhas, o que neste caso, é um erro.

Para corrigir, basta inserir $ no endereço B7 da primeira linha, que é a célula que deve permanecer fixa neste cálculo, ficando a fórmula da seguinte forma: =B2/$B$7. Ao copiar para as próximas linhas, o Excel atualiza a referência da primeira célula (B3, B4...), mantendo fixa a referência da célula com o total ($B$7):

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 50

4 FÓRMULAS E FUNÇÕES

4.1 Fórmulas e Funções Matemáticas Uma das grandes vantagens em se utilizar planilhas eletrônicas para trabalhar com dados são os cálculos e análises que podemos fazer com os mesmos. Para tanto, podemos

criar

fórmulas

utilizando

operadores

aritméticos

(soma,

subtração,

multiplicação, divisão), ou ainda, utilizar as inúmeras funções internas do Excel. Para indicar que determinada célula vai ser utilizada para realizar um cálculo, devemos primeiramente digitar o sinal de igual “=”. Digitar qualquer fórmula ou função sem este sinal resulta em uma mensagem de erro, ou então, o Excel pode entender que está sendo digitado um texto qualquer, como “25-6”, sem que isso gere um resultado. Outro detalhe importante é sempre digitar a fórmula ou função onde se quer apresentar o resultado. Portanto, para exibir o resultado de uma soma na célula E5, devemos digitar a fórmula da soma nesta célula.

Fórmula e Função

Embora pareçam iguais, e por vezes são confundidas, fórmula e função são coisas diferentes. Vejamos:

FÓRMULA

Pode ser entendida como uma expressão matemática que realiza determinado cálculo. Deve ter Operadores e Operandos:

= 25 + 52

SINAL INDICATIVO DE CÁLCULO

OPERADOR

OPERANDO

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 51

Os operandos são os valores (números) que utilizamos para realizar o cálculo. Já Operadores são símbolos matemáticos que permitem fazer cálculos e comparações entre as células. Os operadores do Excel são:

Operador de texto

Significado

& (E comercial)

Exemplo

Conecta dois valores para produzir um valor de

("North"&"wind")

texto contínuo

Operador de comparação

Significado

Exemplo

=

Igual a

A1=B1

>

Maior que

A1>B1

<

Menor que

A1
>=

Maior ou igual a

A1>B1

<=

Menor ou igual a

A1
<>

Diferente de

A1<>B1

Operador aritmético

Significado

Exemplo

+

Adição

3+3

–

Subtração

3–1

Negação

–1

*

Multiplicação

3*3

/

Divisão

3/3

%

Porcentagem

20%

^ (acento circunflexo)

Exponenciação

3^2

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 52

FUNÇÃO

Funções são fórmulas pré-definidas pelo Excel. Permitem realizar de forma simples inúmeros cálculos e análises. As funções reduzem a carga de trabalho do usuário, já que trazem internamente diversas fórmulas que seriam extremamente trabalhosas de serem construídas. Uma função possui a seguinte estrutura:

= SOMA (25;52) SINAL INDICATIVO DE CÁLCULO

NOME DA FUNÇÃO

ARGUMENTOS DA FUNÇÃO

Toda função tem um nome. Os argumentos são dados que passamos para as funções para que estas realizem os cálculos. Podem ser passados através de valor (como número 25) ou por referência (como endereço da célula A5). As funções podem ter de 1 até vários argumentos, ou mesmo, nenhum, como no caso da função =AGORA(), que retorna a hora e data atual. A quantidade de argumentos é informada pelo Excel quando usamos o comando “Inserir Função” ou quando digitamos o nome da função:

Neste exemplo, o Excel informa que podemos inserir diversos argumentos (valores) para a função SOMA, já que o texto explicativo apresenta os valores

seguido

por

reticências

(num1, num2...).

Importante notar que toda função “retorna” um valor. Ao digitar a função e seus valores e teclar “ENTER”, no lugar da função será apresentado o resultado da mesma. A função pode ser vista na barra de funções.Nesta barra podemos editar a função. Também é possível editá-la selecionando a célula onde está a função e pressionando a tecla “F2”. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 53

As funções devem sempre ser inseridas na célula onde se deve mostrar o resultado. No exemplo acima, para exibir a soma acima dos valores, devemos digitá-la na célula C2. Para inserir qualquer função, basta começar a digitar o nome dela na célula que o Excel automaticamente sugere a função. Também é possível utilizar o assistente, clicando na aba “Fórmulas” e depois no botão “Inserir Função”.

Atenção !!  Para inserir apenas células específicas, os nomes devem ser inseridos separados por ponto-e-vírgula - ;  Para inserir todas as células de um intervalo, separamos os nomes das células por dois pontos - :

= SOMA (A1 ; E1) => resulta na soma apenas das células A1 e E1. = SOMA (A1 : E1) => resulta na soma de todas as células que existem entre A1 e E1 (A1, B1, C1, D1, E1).

Exemplos

Fórmulas Simples

No caso de realizar cálculos simples, com poucas células, é possível criar operações básicas indicando simplesmente o nome das células e a operação a realizar. Exemplo: ao introduzir =E5+E6, o Excel automaticamente soma os valores das células E5 e E6; quando alterar os valores em algumas destas células, o resultado alterase automaticamente.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 54

É possível introduzir o nome das células digitando manualmente através do teclado, ou clicando nelas com o mouse. O Excel indica através de cores diferentes quais são as células selecionadas.

4.2 Funções pré-definidas Além dos operadores aritméticos simples, o Excel suporta fórmulas mais avançadas através de funções. O Excel possui centenas de funções. Vejamos algumas fórmulas e funções mais utilizadas no dia-a-dia.

4.2.1 SOMA A função SOMA permite somar o conteúdo de duas ou mais células e é especialmente útil para séries de células. Vejamos um exemplo simples:

O objetivo desta planilha é calcular o valor total de cada produto (quantidade multiplicada por valor unitário) e depois o total de todos os produtos. Para o total de cada produto precisamos utilizar o operador de multiplicação (*). No caso do Tablet temos a quantidade que está na célula A8 e o valor unitário está na célula C8. Nosso cálculo será feito na célula D4, que é célula onde deverá constar o total. Poderíamos fazer o seguinte cálculo: =3*200. Isso traria o resultado, porém se a quantidade ou o Valor Unitário fossem alterados seria preciso fazer novamente o cálculo. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 55

O correto é então é fazer =A8*C8. Com isso multiplicamos referenciando as células, independente do conteúdo dela, e o Excel fará a multiplicação, desde que ali se tenha um número.

Observe que ao fazer o cálculo este é colocado também na barra de fórmulas, e mesmo após pressionar ENTER, ao clicar sobre a célula onde está o resultado, é possível ver como se chegou ao resultado pela barra de fórmulas.

Para o cálculo do teclado é necessário então fazer o cálculo da segunda linha A5*C5 e assim sucessivamente. Veja que a coluna representada pela letra não muda, muda-se somente o número que representa a linha, e se esta planilha tivesse uma grande quantidade de produtos, repetir o cálculo seria cansativo e com certeza sujeito a erros. Quando há uma sequência grande de cálculos, o Excel permite que se faça um único cálculo, e, ao posicionar o cursor do mouse no canto inferior direito da célula, o cursor se transforma em uma cruz. Ao arrastar esta cruz pelas células seguintes, o Excel copia a Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 56

fórmula nas células, alterando o número da linha de acordo com a posição da célula.

Calculando a Soma Total

Para calcular o total poderia se utilizar o seguinte cálculo: D4+D5+D6+D7+D8. Porém isso não seria nada pratico em planilhas maiores. É aqui que podemos utilizar o recurso das funções. No caso a função a ser utilizada é a função SOMA, e sua estrutura é =SOMA(CelIni:Celfim), ou seja, inicia-se com o sinal de igual (=), escreve-se o nome da função, abrem-se parênteses, clica-se na célula inicial da soma e arrasta-se até a última célula a ser somada (ou digita-se manualmente as células). Este intervalo é representado pelo sinal de dois pontos (:), e fecham-se os parênteses. Embora seja possível fazer manualmente na célula o Excel possui um assistente de função que facilita e muito a utilização das mesmas em sua planilha. Na ABA Inicio do Excel dentro do grupo Edição existe o botão de SOMA:

A primeira função é justamente Soma, então clique na célula vazia embaixo dos valores e clique no botão de função. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 57

Observe conforme a imagem que o Excel acrescenta a soma e o intervalo de células, pressione ENTER e o cálculo é realizado.

4.2.2 MÉDIA Esta função é escrita com acento no “e” (e, tal como todas as outras funções, pode ser escrita com caracteres maiúsculos ou minúsculos). Esta calcula a média aritmética simples dos valores constantes nas células indicadas como argumento. Em outras palavras, a função soma os valores das células e divide pelo número de células com valores.

4.2.3 MULT Esta função multiplica valores constantes nas células. Seria o mesmo que inserir uma fórmula = C4*D4*E4*F4. Neste caso, podemos simplificar escrevendo = MULT(C4:F4).

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 58

4.2.4 MÍNIMO Mostra o valor mínimo de uma seleção de células.

Exemplo: Vamos utilizar essa função para saber os valores mínimos nas características dos atletas. Nesta planilha clique na célula abaixo da coluna de idade na linha de valores máximos (E16), e monte a seguinte função =MÍNIMO(E4:E13). Com essa função O Excel busca no intervalo das células E4 à E13 qual é o valor mínimo encontrado.

4.2.5 MÁXIMO Similar a função anterior, esta mostra o MAIOR valor em uma seleção de células. Pode-se utilizar essa função para saber qual é a maior idade, o maior peso e a maior altura. Seguindo o mesmo exemplo anterior, basta clicar na célula abaixo da coluna de idade na linha de valores máximos (E15) e montar a seguinte função =MAXIMO(E4:E13). Com essa função O Excel no intervalo das células E4 à E13 qual é o valor máximo encontrado.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 59

4.2.6 DIVISÃO Para efetuar divisões, pode-se utilizar o operador de divisão: /. No entanto, ao realizar cálculos com este operador, o resultado será um número inteiro no caso de divisão exata, ou um número decimal caso contrário. Caso a intenção seja obter apenas a parte inteira de uma divisão, é possível utilizar a função = QUOCIENTE (). Veja a diferença:

= 20 / 3 => o resultado será 6,6667 = QUOCIENTE (20;3) => o resultado será 6.

= 20 / 2 => o resultado será 10 = QUOCIENTE (20;2) => o resultado será 10.

4.2.7 EXPONENCIAÇÃO (Exponencial) E RAIZ Também é possível calcular raízes e potências utilizando o Excel. Para calcular a raiz de um número, basta usar a função =RAIZ(), inserindo como argumento o número. Para potências, pode-se utilizar o operador de Exponenciação (^) ou a função = POTENCIA(num, potencia), da seguinte forma:

Calcular o quadrado de 4: = (4^2) => 16, ou = POTÊNCIA (4;2) = 16

Calcular o cubo de 3: = (3^3) => 27, ou = POTÊNCIA (3;3) => 27

Calcular a raiz de 64: = RAIZ (64) => 8

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 60

4.2.8 PORCENTAGEM Calcular porcentagem de valores pode ser feito usando o operador de Porcentagem %. Por exemplo, imagine que precisamos calcular uma comissão de 20% do valor total de venda dos itens abaixo:

Primeiramente, somamos o valor total da venda dos 2 itens. Multiplicamos então este valor por 20%. De forma mais prática, podemos utilizar uma função juntamente com o operador %. Vejamos:

Faça no Excel e veja o resultado.

4.2.9 ARRED A função ARRED(num; num_digitos) arredonda um número para um número especificado de dígitos. Por exemplo, se a célula A1 contiver 23,7825 e o objetivo é arredondar esse valor para duas casas decimais, poderá usar a seguinte fórmula: =ARRED(A1; 2) O resultado dessa função é 23,78. 

Se núm_dígitos for maior do que 0 (zero), o número será arredondado para o

número especificado de casas decimais. 

Se núm_dígitos for 0, o número será arredondado para o inteiro mais próximo.



Se núm_dígitos for menor do que 0, o número será arredondado para a

esquerda da vírgula decimal.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO 

Página | 61

Para sempre arredondar para cima (longe de zero), use a função

ARREDONDAR.PARA.CIMA. 

Para sempre arredondar para baixo (na direção de zero), use a função

ARREDONDAR.PARA.BAIXO.

4.2.10 TRUNCAR Trunca um número para um inteiro removendo a parte fracionária do número.

Exemplo: Considerando o valor 5,6963 na célula A1: =TRUNCAR(A1) resulta em 5.

4.2.11 INT Arredonda um número para baixo até o número inteiro mais próximo. Exemplo:

A Dados 19,5 Fórmula

Descrição (resultado)

=INT(8,9)

Arredonda 8,9 para baixo (8)

=INT(-8,9)

Arredonda -8,9 para baixo (-9)

=A2-INT(A2)

Retorna a parte decimal de um número real positivo na célula A2 (0,5)

4.2.12 SUBTOTAL Retorna um subtotal em uma lista ou em um banco de dados. Neste caso, podemos filtrar a lista e a fórmula automaticamente atualiza o resultado de acordo com os valores selecionados. Imagine a seguinte lista:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 62

Ao teclar ENTER na fórmula acima, o resultado será 104. No entanto, caso queiramos saber o valor da soma somente dos itens D e E, basta filtrá-los para que a função subtotal atualize os valores:

A sintaxe da função SUBTOTAL tem os seguintes argumentos: 

Núm_função: Obrigatório. O número de 1 a 11 (incluindo valores ocultos) ou

101 a 111 (ignorando valores ocultos) especifica qual função usar no cálculo de subtotais dentro de uma lista:

NÚM_FUNÇÃO

NÚM_FUNÇÃO

(INCLUINDO VALORES

(IGNORANDO VALORES

OCULTOS)

OCULTOS)

1

101

MÉDIA

2

102

CONTA

3

103

CONT.VALORES

4

104

MÁX

5

105

MÍN

6

106

MULT

7

107

DESVPAD

8

108

DESVPADP

FUNÇÃO

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO



Página | 63

9

109

SOMA

10

110

VAR

11

111

VARP

Ref1: Obrigatório. O primeiro intervalo nomeado ou referência cujo subtotal se

deseja. 

Ref2: Opcional. Intervalos nomeados ou referências de 2 a 254 cujo subtotal se

deseja.

4.2.12 CONT.VALORES Essa função conta a quantidade de valores contida na lista de argumentos ou no intervalo das células especificadas como argumento. Essa função aceita de 1 a 30 argumentos. Os argumentos devem ser números, ou matrizes ou referências que contenham números. Sintaxe: =CONT.VALORES(valor1;valor2;intervalo1;...)

Exemplo: Se todas as células em A1:A10 contiverem dados, quer sejam números, textos ou qualquer outro dado, exceto a célula A5, então: =CONT.VALORES(A1:A10) --> resulta 9

4.2.13 CONT.SE Essa função conta de acordo com um critério definido. Por exemplo, em uma planilha com dados sobre os funcionários, é possível contar quantos funcionários estão locados para o departamento de Contabilidade. Podemos usar a função CONT.SE, para, a partir da coluna Departamento, contar quantos funcionários pertencem ao departamento de Contabilidade. Sintaxe:=CONT.SE(FAIXA;Critério)

Exemplo: Se na faixa de A2 até A50 houver 10 vezes a palavra Contábil, indicando que o funcionário é da Contabilidade, então: =CONT.SE(B2:B50;"Contábil") --> Retorna 10

OBS.: o critério deve vir sempre entre aspas, mesmo que seja um teste numérico. Por Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 64

exemplo, para contar quantos valores maiores do que 20 existem na faixa de A1 até A50, utiliza-se a seguinte fórmula: =CONT.SE (A1:A50; " > 20 ").

4.2.14 SOMASE Essa função procura em uma coluna por determinados valores (por exemplo, procura em uma coluna pela Seção do funcionário) e, caso encontre o valor procurado, utiliza os valores de outra coluna para ir somando. Por exemplo, em uma planilha com dados sobre os funcionários, pode-se querer somar o total de salários para todos os funcionários que estão locados no departamento de Recursos Humanos. Pode-se usar a função SOMASE() para, a partir da coluna Departamento, verificar os funcionários que pertencem ao Recursos Humanos (RH) e somar os respectivos salários na coluna de Salários. Sintaxe: =SOMASE(FAIXA_DE_TESTE;Critério;FAIXA_VALORES_A_SOMAR)

Exemplo:

Para somar os salários correspondentes ao RH, utiliza-se a função SOMASE da seguinte forma: =SOMASE (A2:A13; "RH"; B2:B13). Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 65

O resultado será:

Em resumo, a função procura na faixa de A2:A13 pela palavra “RH”; ao encontrar, desloca-se para a coluna B (onde está o valor dos salários) e vai somando os valores dos salários para os funcionários do departamento de Recursos Humanos.

4.2.15 CONT.NÚM A função CONT.NÚM conta o número de células que contêm números e conta os números na lista de argumentos. Use a função CONT.NÚM para obter o número de entradas de números que estejam em um intervalo ou uma matriz de números. Por exemplo, pode-se inserir a seguinte fórmula para contar os números no intervalo A1:A20: =CONT.NÚM(A1:A20). Neste exemplo, se 3 das células no intervalo contiverem números, o resultado será 3.

OBS.: Em caso de datas no intervalo, o Excel também as somará na contagem.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 66

4.3 Correção de Erros O Excel possui um sistema de correção de erros que avisa quando algo poderá estar incorreto. Neste caso, o programa detectou que um valor de uma célula adjacente ao grupo a ser somado ficou de fora (a célula com o número que designa o ano).

Célula não inserida na formula de soma

A chamada de atenção para um problema potencial é feita com um pequeno triângulo verde no canto superior esquerdo da célula. Clique no triângulo para fazer aparecer o diálogo de controle de erros. Depois clique no sinal amarelo e verifique qual o erro. Neste caso não é propriamente um erro, mas sim a advertência para o fato de que a fórmula omite células adjacentes, o que poderá ter sido causado por uma distração.Neste caso, havia a intenção de deixar esta célula fora do cálculo. Podemos assim escolher a opção Ignorar erro, para que o Excel não volte a chamar a atenção para este fato.

4.4 Funções de Data/Hora Antes de falar sobre as funções de data/hora, cabe uma observação muito importante. O Microsoft Excel armazena datas como números de série sequenciais para que eles possam ser usados em cálculos. Por padrão, 1° de janeiro de 1900 é o número de série 1. Já 1° de janeiro de 2008 é o número de série 39448 porque está 39.447 dias após 1° de janeiro de 1900. Ao digitar numa célula o valor 40841 e formatar esta célula como DATA (formato DD/MM/AAAA), o resultado será 25/10/2011. É importante ter isso em mente ao trabalhar com datas para evitar resultados inesperados.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 67

=AGORA() Retorna a data e da hora atual. Se o formato da célula era Geral antes da função ter sido inserida, o Excel irá transformar o formato dessa célula no mesmo formato de data e hora especificado nas configurações regionais de data e hora do Painel de Controle do Windows. A função AGORA é útil quando se precisa exibir a data e a hora atuais em uma planilha, calcular um valor com base na data e na hora atuais ou ter esse valor atualizado sempre que abrir a planilha.

=ANO() Retorna o ano de um valor inserido como argumento, lembrando que o valor referese ao número de série da data atual. Por exemplo, ao digitar =ANO(40841), teremos como resultado 2011 (ano da data 40841 – data: 25/10/2011).

=HOJE() Retorna a data atual. A função HOJE é útil quando se precisa ter a data atual exibida em uma planilha, independentemente de quando a pasta de trabalho for aberta. Ela também é útil para o cálculo de intervalos. Por exemplo, para calcular a idade de uma pessoa que nasceu no ano de 1963: =ANO(HOJE())-1963

Essa fórmula usa a função HOJE como argumento da função ANO de forma a obter o ano atual e, em seguida, subtrai 1963, retornando a idade da pessoa.

=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). =MÊS(núm_série)

=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. =DIA(núm_série)

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 68

= 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.).

=MINUTO() Retorna os minutos de um valor de tempo. O minuto é dado como um número inteiro, que vai de 0 a 59

=DATA() A função DATA retorna o número de série seqüencial que representa uma data particular. Por exemplo, a fórmula =DATA(2008;8;7) retorna 39637, o número de série que representa 07/08/2008. A função DATA é bastante útil em situações nas quais o ano, o mês e o dia são fornecidos como fórmulas ou referências de célula.

= DIAS360() A função DIAS360 retorna o número de dias entre duas datas com base em um ano de 360 dias (doze meses de 30 dias). Use essa função para ajudar no cálculo de pagamentos, se o seu sistema contábil estiver baseado em doze meses de 30 dias. Exemplo: =DIAS360( 01/01/2010 ; HOJE() )

=DIATRABALHOTOTAL() 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 para calcular os benefícios aos empregados que recebem com base no número de dias trabalhados durante um período específico. Sintaxe: =DIASTRABALHOTOTAL(data_inicial;data_final;feriados)

Exemplo: =DIATRABALHOTOTAL( DATA(2011;10;1) ; DATA(2011;10;5) ; 1)

Obs: 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;23;5) para 23 de maio de 2008.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 69

=DIA.DA.SEMANA() Retorna o dia da semana correspondente a uma data. O dia é dado como um inteiro, variando de 1 (domingo) a 7 (sábado), por padrão

Exemplo:Considerando a data: 14/02/08 inserida na célula A2: =DIA.DA.SEMANA(A2) retornará o valor 5 (quinta-feira).

Adicionar ou subtrair dias em uma data

Suponha que uma fatura vença em 8 de fevereiro de 2008 e deseja-se transferir fundos à conta corrente de forma que estes cheguem 15 dias antes dessa data:

1. Na célula A1, digite 08/02/2013. 2. Na célula B1, digite =A1-15. O resultado será o dia em que deve-se transferir valores para a conta.

Adicionar ou subtrair meses em uma data

Pode-se usar a função DATAM para adicionar ou subtrair rapidamente um número específico de meses inteiros em uma data. A função DATAM requer dois valores: a data de início e o número de meses que se deseja adicionar ou subtrair. Para subtrair meses, insira um número negativo como segundo argumento:

=DATAM("15/02/08";-5). Essa fórmula resulta na data 15/09/07.

É possível especificar o valor da data de início fazendo referência a uma célula que contenha um valor de data ou inserindo uma data entre as aspas, como "15/02/08". Por exemplo: adicionar 16 meses a 16 de outubro de 2013. 1. Na célula A5, digite 16/10/2013. 2. Na célula B5, digite =DATAM(A5;16), ou 3. Na célula C5, digite =DATAM("16/10/2013";16). Nesse caso, a função usa um valor de data inserido diretamente, "16/10/2013". As células B5 e C5 devem mostrar a data 16/02/2015.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 70

Adicionar ou subtrair anos em uma data

Imagine a seguinte planilha: 1

A

2Data

B Anos a serem adicionados (ou subtraídos)

09.06.07

3

Para adicionar 3 anos a data 09/06/07:

=DATA(ANO(A2)+B2 ; MÊS(A2) ; DIA(A2))

Na fórmula, um número especificado de anos da coluna B é adicionado ao valor de ano que é derivado da data na coluna A. A função ANO é usada na data na célula A2 (09/06/2007) e retorna 2007 como ano. A fórmula adiciona então 3 (o valor na célula B2) ao valor de ano, que resulta em 2010. Na mesma fórmula, a função MÊS retorna o valor 6, e a função DIA retorna o valor 9. A função DATA combina então esses três valores em uma data que está três anos no futuro — 09/06/2010.

4.5 Funções de Texto TEXTO

A função TEXTO converte um valor numérico em texto e permite especificar a formatação de caracteres em formatos especiais. Essa função é útil em situações nas quais se deseja exibir números em um formato mais legível ou deseja combinar números com texto ou símbolos. Por exemplo, suponha que a célula A1 contenha o número 23,5. Para formatar o número como um valor monetário, pode-se usar a seguinte fórmula:

=TEXTO(A1;"R$0,00") Nesse exemplo, o Excel exibe R$23,50.

Também é possível formatar números usando os comandos no grupo Número da Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 71

guia Início da Faixa de Opções. Entretanto, esses comandos apenas funcionarão se a célula inteira for numérica. Se quiser formatar um número e combiná-lo a outro texto, a função TEXTO será a melhor opção. Por exemplo, para adicionar um texto à fórmula anterior: =TEXTO(A1;"R$0,00") & " por hora" O Excel exibe R$23,50 por hora.

MAIÚSCULA Converte o texto em letras maiúsculas: =MAIÚSCULA(texto)

MINÚSCULA Converte todas as letras maiúsculas do texto para minúsculas. =MINÚSCULA(texto)

PRI.MAIÚSCULA Coloca a primeira letra de uma seqüência de caracteres de texto em maiúscula e converte todas as outras letras para minúsculas. =PRI.MAIÚSCULA(texto)

OBS.: Em todos os casos, texto é o texto que se deseja converter para maiúsculas. Pode ser uma referência ou uma seqüência de caracteres de texto.

Veja abaixo a aplicação destas funções utilizando o exemplo da página anterior:

- mostrar a mensagem em maiúscula: = MAIÚSCULA (TEXTO(A1; "R$0,00") & " por hora")

- mostrar a mensagem em minúscula: = MINÚSCULA (TEXTO(A1; "R$0,00") & " por hora")

- mostrar a mensagem com a primeira letra de cada palavra em maiúscula (o texto está propositalmente com o inverso): = MAIÚSCULA (TEXTO(A1;"R$0,00") & " pOR hORA") Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 72

4.6 Funções Lógicas Uma Função lógica que retorna um valor de acordo com os testes realizados.

4.6.1 SE Retorna um valor se uma condição especificada avaliar como VERDADEIRO e um outro valor se for avaliado como FALSO. Use SE para efetuar testes condicionais com valores e fórmula:

=SE(teste_lógico;valor_se_verdadeiro;valor_se_falso)

teste_lógico é qualquer valor ou expressão que possa ser avaliado como VERDADEIRO ou FALSO. Por exemplo, A10=100 é uma expressão lógica; se o valor da célula A10 for igual a 100, a expressão será considerada VERDADEIRO. Caso contrário, a expressão será considerada FALSO. Esse argumento pode usar qualquer operador de cálculo de comparação.

valor_se_verdadeiro é o valor retornado se teste_lógico for VERDADEIRO. Por exemplo, se esse argumento for a sequência de caracteres de texto "Dentro do orçamento" e o argumento teste_lógico for considerado VERDADEIRO, a função SE exibirá o texto "Dentro do orçamento". Se teste_lógico for VERDADEIRO e valor_se_verdadeiro for vazio, o argumento retornará 0 (zero). Para exibir a palavra VERDADEIRO,

use

o

valor

lógico

VERDADEIRO

para

esse

argumento.

Valor_se_verdadeiro pode ser outra fórmula.

valor_se_falso é o valor retornado se teste_lógico for FALSO. Por exemplo, se esse argumento for a sequência de caracteres de texto "Acima do orçamento" e o argumento teste_lógico for considerado FALSO, a função SE exibirá o texto "Acima do orçamento". Se teste_lógico for FALSO e valor_se_falso for omitido (ou seja, se não houver vírgula após valor_se_verdadeiro), o valor lógico FALSO será retornado. Se teste_lógico for FALSO e valor_se_falso for vazio (ou seja, se houver uma vírgula após valor_se_verdadeiro seguida do parênteses de fechamento), o valor 0 (zero) será retornado. Valor_se_falso pode ser outra fórmula. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 73

Podem ser aninhadas até 64 funções SE como argumentos valor_se_verdadeiro e valor_se_falso

para

criar

testes

mais

elaborados.

Quando

os

argumentos

valor_se_verdadeiro e valor_se_falso são avaliados, SE retorna o valor que foi retornado por essas instruções.

Exemplo 1 A 1

Dados

2 50 Fórmula

Descrição (resultado)

=SE(A2<=100;"Dentro do

Se o número acima for menor ou igual a

orçamento";"Acima do orçamento")

100,

a

fórmula

exibirá

"Dentro

do

orçamento". Caso contrário, a função exibirá "Acima do orçamento" (Dentro do orçamento) =SE(A2=100;SOMA(B1:B2);"")

Se o número acima for 100, o intervalo B1:B2 será calculado. Caso contrário, o texto vazio ("") será retornado ()

Exemplo 2

1

A

B

Despesas reais

Despesas previstas

1500

900

500

900

500

925

Fórmula

Descrição (resultado)

=SE(A2>B2;"Acima do

Verifica se a primeira linha está acima do

orçamento";"OK")

orçamento (Acima do orçamento)

=SE(A3>B3;"Acima do

Verifica se a segunda linha está acima do

orçamento";"OK")

orçamento (OK)

2 3 4

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 74

Exemplo 3 A 1

Resultado

2 3 4

45 90 78 Fórmula

Descrição (resultado)

=SE(A2>89;"A";SE(A2>79;"B";

Atribui uma letra ao primeiro resultado (F)

SE(A2>69;"C";SE(A2>59;"D";"F")))) =SE(A3>89;"A";SE(A3>79;"B";

Atribui uma letra ao segundo resultado

SE(A3>69;"C";SE(A3>59;"D";"F"))))

(A)

=SE(A4>89;"A";SE(A4>79;"B";

Atribui uma letra ao terceiro resultado (C)

SE(A4>69;"C";SE(A4>59;"D";"F"))))

=SE(A2>89; "A"; SE(A2>79;"B"; SE (A2>69; "C"; SE(A2>59; "D"; "F"))))

No exemplo anterior, a segunda instrução SE também é o argumento valor_se_falso para a primeira instrução SE. Da mesma maneira, a terceira instrução SE é o argumento valor_se_falso para a segunda instrução SE. Por exemplo, se o primeiro teste_lógico (Média>89) for VERDADEIRO, "A" será retornado. Se o primeiro teste_lógico for FALSO, a segunda instrução SE é avaliada e assim por diante.

As letras são atribuídas a números da seguinte forma: SE RESULTADO FOR ENTÃO RETORNARÁ Maior do que 89

A

De 80 a 89

B

De 70 a 79

C

De 60 a 69

D

Menor do que 60

F

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 75

Veja o Vídeo em: http://office.microsoft.com/pt-br/excel-help/usando-mais-de-uma-funcao-seRZ102425926.aspx?CTT=3§ion=4

4.6.2 Função E Retorna VERDADEIRO se todos os seus argumentos forem avaliados como VERDADEIRO e retornará FALSO se um ou mais argumentos forem avaliados como FALSO. Um uso comum para a função E é expandir a utilidade de outras funções que realizam testes lógicos. Por exemplo, a função SE realiza um teste lógico e, em seguida, retornará um valor se o teste for avaliado como VERDADEIRO e outro valor se o teste for avaliado como FALSO. Usando a função E como argumento teste_lógico da função SE, pode-se testar várias condições diferentes em vez de apenas uma. = E(lógico1, [lógico2], ...)

Exemplo 1 A

B

C

Fórmula

Descrição

Resultado

3

=E(VERDADEIRO,

Todos os argumentos são

VERDADEIRO

4

VERDADEIRO)

VERDADEIRO

=E(VERDADEIRO,

Um argumento é FALSO

FALSO

Todos os argumentos são avaliados

VERDADEIRO

1 2

FALSO) =E(2+2=4, 2+3=5)

como VERDADEIRO

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 76

Exemplo 2: A

B

C

Fórmula

Descrição

Resultado

=E(1
Exibirá VERDADEIRO se o

1

Dados

2 50

3

104

4

5

número na célula A2 estiver entre 1 e 100. Caso contrário, exibirá

VERDADEIRO

FALSO.

6

=SE(E(1
Exibirá o número na célula A3, se

"O valor está fora do

estiver entre 1 e 100. Caso

intervalo.")

contrário, exibirá a mensagem "O valor está fora do intervalo".

7

=SE(E(1
Exibirá o número na célula A2, se

"O valor está fora do

estiver entre 1 e 100. Caso

intervalo.")

contrário, exibirá uma mensagem.

O valor está fora do intervalo.

50

4.6.3 Função OU Retorna VERDADEIRO se qualquer argumento for VERDADEIRO; retorna FALSO se todos os argumentos forem FALSOS.

= OU(logico1;logico2;...)

logico1,logico2,... são de um a 255 condições que se deseja testar e que podem resultar em VERDADEIRO ou FALSO.

Os argumentos devem ser avaliados como valores lógicos, como VERDADEIRO ou FALSO. Se um argumento ou referência contiver texto ou células vazias, esses valores

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 77

serão ignorados. Se o intervalo especificado não contiver valores lógicos, OU retornará o valor de erro #VALOR!.

Exemplo

1

A

B

Fórmula

Descrição (resultado)

=OU(VERDADEIRO)

Um argumento é VERDADEIRO

2 3

(VERDADEIRO)

4 =OU(1+1=1;2+2=5)

Todos os argumentos são avaliados como FALSO (FALSO)

=OU(VERDADEIRO;FALSO;VERDADEIRO)

Pelo menos um argumento é VERDADEIRO (VERDADEIRO)

4.6 4 SE com OU Também é possível utilizar a função OU juntamente com a função: =SE( OU (A8 rel="nofollow">5 ;B8>5); "Um dos números é maior que 5"; "Nenhum dos números é maior que 5”) O Excel faz a comparação entre as células utilizando a função OU. Se qualquer argumento desta for VERDADEIRO, ela retorna verdadeiro, fazendo a função SE escrever “Um dos números é maior que 5”. Caso contrário, a função OU deve retornar falso, ou seja, quando ambos números que ela compara for menor que 5.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 78

5 TABELAS DO EXCEL Para facilitar o gerenciamento e a análise de um grupo de dados relacionados, é possível transformar um intervalo de células em uma tabela do Microsoft Office Excel. Em geral, uma tabela contém dados relacionados em uma série de linhas e colunas de planilha que são formatadas como uma tabela. Ao usar os recursos de tabela, é possível gerenciar os dados nas linhas e colunas da tabela independentemente dos dados em outras linhas e colunas da planilha.

Uma tabela pode incluir os seguintes elementos: 

Linha de cabeçalho: Por padrão, uma tabela tem uma linha de cabeçalho.

Cada coluna de tabela possui a filtragem habilitada na linha de cabeçalho, para que se possa filtrar ou classificar os dados de tabela rapidamente.



Linhas em tiras: Por padrão, o sombreamento alternativo, ou faixa, é aplicado

às linhas em uma tabela para diferenciar melhor os dados.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO 

Página | 79

Colunas calculadas: Ao inserir uma fórmula em uma célula em uma coluna de

tabela, pode-se criar uma coluna calculada na qual essa fórmula é instantaneamente aplicada a todas as outras células nessa coluna de tabela.



Linha de total: é possível adicionar uma linha de total à tabela, que fornece

acesso a funções de resumo (como a função MÉDIA, CONT.NÚM ou SOMA). Uma lista suspensa é exibida em cada célula de linha de total, para que seja possível calcular rapidamente os totais desejados.



Alça de dimensionamento: Uma alça de dimensionamento no canto inferior

direito da tabela permite arrastar a tabela para o tamanho que se quiser.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 80

5.1 Criar ou excluir uma tabela do Excel em uma planilha Ao criar uma tabela em uma planilha do Microsoft Office Excel, é possível gerenciar e analisar os dados nessa tabela independentemente dos dados fora dela. Por exemplo, pode filtrar as colunas da tabela, adicionar uma linha para totais, aplicar formatação de tabela etc.

Se não quiser trabalhar com os dados em uma tabela, poderá converter a tabela em um intervalo regular mantendo a formatação de estilo de tabela que tiver aplicado. Quando não precisar mais de uma tabela, poderá excluí-la.

Inserir uma Tabela

1. Em uma planilha, selecione o intervalo de células que deseja incluir na tabela. As células podem estar vazias ou podem conter dados. 2. Na guia Inserir, no grupo Tabelas, clique em Tabela.

3. Se o intervalo selecionado contiver dados que deseja exibir como cabeçalhos da tabela, marque a caixa de seleção Minha tabela tem cabeçalhos. Cabeçalhos de tabela exibirão nomes padrão se não marcar a caixa de seleção Minha tabela tem cabeçalhos. É possível alterar os nomes padrão digitando o texto desejado. Se não quiser exibir cabeçalhos de tabela, é possível desativá-los. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 81

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 na guia Design para personalizar ou editar a tabela.

Formatar dados como uma tabela

1. Na planilha, selecione um intervalo de células vazias ou de células que contêm os dados que deseja formatar rapidamente como uma tabela. 2. Na guia Página Inicial, no grupo Estilos, clique em Formatar como Tabela.

3. Em Clara, Média ou Escura, clique no estilo que tabela que deseja usar.

Converter uma tabela em intervalo de dados

Clique em qualquer lugar da tabela. Na guia Design, no grupo Ferramentas, clique em Converter em Intervalo.

Após este procedimento, os recursos de tabela não estarão mais disponíveis depois a tabela for convertida em intervalo. Por exemplo, os cabeçalhos de linha não incluem mais as setas de classificação e filtro, e as referências estruturadas (referências que usam nomes de tabelas) que foram usadas em fórmulas se transformam em referências de célula comuns.

OBS.: Também é possível clicar com o botão direito do mouse na tabela, apontar para Tabela e clicar em Converter em Intervalo.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 82

Excluir uma tabela

Na planilha, selecione uma tabela. Pressione DELETE.

5.2 Formatar uma tabela do Excel O Microsoft Office Excel fornece vários estilos predefinidos de tabela (ou estilos rápidos) que podem ser usados para formatar rapidamente uma tabela. Também é possível criar estilos personalizados. Pode-se ajustar ainda mais a formatação da tabela escolhendo opções de estilos rápidos para elementos da tabela como linhas de cabeçalho e de totais, primeiras e últimas colunas e linhas e colunas em tiras.

Escolhendo um estilo de tabela ao criar uma tabela

Na planilha, selecione um intervalo de células que deseja formatar rapidamente como uma tabela. Na guia Página Inicial, no grupo Estilos, clique em Formatar como Tabela. Em Clara, Média ou Escura, clique no estilo que tabela que deseja usar. Ao usar a opção Formatar como Tabela, o Office Excel insere automaticamente uma tabela.

Aplicar um estilo de tabela a uma tabela existente

Na planilha, selecione a tabela à qual deseja aplicar um estilo de tabela. Na guia Design, no grupo Estilos de Tabela, siga um destes procedimentos: Clique no estilo de tabela que deseja usar. Clique no botão Mais

e em Clara, Média ou Escura, clique no estilo da tabela

que deseja usar.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 83

Criar um estilo de tabela Personalizado

Os estilos de tabela personalizados criados são armazenados somente na pasta de trabalho atual, não estando, portanto, disponíveis em outras pastas de trabalho. Na guia Página Inicial, no grupo Estilos, clique em Formatar como Tabela.

Selecione uma tabela existente para exibir as Ferramentas de Tabela e, na guia Design, no grupo Estilos de Tabela, clique no botão Mais . Clique em Novo Estilo de Tabela. Na caixa Nome, digite um nome para o novo estilo de tabela. Na caixa Elemento de Tabela, execute um dos seguintes procedimentos: 

Para formatar um elemento, clique nele e, em seguida, clique em Formato.



Para remover uma formatação existente de um elemento, clique nele e, em

seguida, clique em Limpar.

Nas guias Fonte, Borda e Preenchimento, selecione as opções de formatação desejadas e clique em OK. Em Visualização, pode-se ver como as alterações de formatação feitas afetam a tabela. Repita todas estas etapas para todos os elementos de tabela que deseje personalizar. Para usar o novo estilo de tabela como o estilo padrão na pasta de trabalho atual, marque a caixa de seleção Definir como estilo rápido de tabela padrão para este documento.

Excluir um estilo de tabela personalizado

1. Na guia Página Inicial, no grupo Estilos, clique em Formatar como Tabela. 2. Em Personalizado, clique com o botão direito do mouse no estilo de tabela que deseja excluir e clique em Excluir no menu de atalho. Ao excluir o estilo, todas as tabelas formatadas com este estilo serão formatadas com o estilo padrão.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 84

Remover um estilo de tabela

1. Na planilha, selecione a tabela da qual deseja remover o estilo de tabela atual. 2. Na guia Design, no grupo Estilos de Tabela, clique no botão Mais.

.

3. Clique em Limpar. A tabela será exibida no formato de tabela padrão. Remover um estilo de tabela não remove essa tabela. Se não desejar trabalhar com os seus dados em uma tabela, a tabela poderá ser convertida em um intervalo comum. Para obter mais informações, consulte Converter uma tabela do Excel em um intervalo de dados.

Escolher opções de estilo de tabela para formatar os elementos da tabela

Na planilha, selecione a tabela à qual deseja aplicar as opções de estilo de tabela. Na guia Design, no grupo Opções de Estilo Rápido, as opções são: 

Para ativar ou desativar a linha de cabeçalho, marque ou desmarque a caixa de

seleção Linha de Cabeçalho. 

Para ativar ou desativar a linha de totais, marque ou desmarque a caixa de

seleção Linha de Totais. 

Para exibir formatação especial para a primeira coluna da tabela, marque a caixa

de seleção Primeira Coluna. 

Para exibir formatação especial para a última coluna da tabela, marque a caixa

de seleção Última Coluna. 

Para exibir linhas pares e ímpares de forma diferente para facilitar a leitura,

marque a caixa de seleção Linhas em Tiras. 

Para exibir colunas pares e ímpares de forma diferente para facilitar a leitura,

marque a caixa de seleção Colunas em Tiras.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 85

Ativar ou desativar cabeçalhos de tabelas do Excel

Ao criar uma tabela do Microsoft Office Excel em uma planilha, cabeçalhos de tabela são automaticamente adicionados e exibidos por padrão.

Cabeçalhos de tabela exibem nomes padrão que podem ser alterados na planilha, ou pode-se especificar que eles mostrem os dados de cabeçalho que estão na planilha. Em uma tabela longa, cabeçalhos de tabela substituem títulos de coluna da planilha, para poderem permanecer visíveis quando rolar os dados da tabela. Se não quiser exibir cabeçalhos de tabela, será possível desativá-los. Para isso: 1. Clique em qualquer lugar na tabela para ter certeza de que a célula ativa esteja em uma coluna da tabela. 2. Na guia Design, no grupo Opções de Estilo de Tabela, marque ou desmarque a caixa de seleção Linha de Cabeçalho para ocultar ou exibir os cabeçalhos da tabela.

Adicionar uma linha ao final da tabela

Pressione TAB na última célula da última linha para adicionar uma linha em branco ao final da tabela.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 86

Incluir uma linha ou uma coluna de uma planilha em uma tabela

Para incluir uma linha de uma planilha na tabela, digite um valor ou um texto em uma célula logo abaixo da tabela. Para incluir uma coluna de uma planilha na tabela, digite um valor ou um texto em uma célula adjacente à direita da tabela. Para incluir linhas e colunas de uma planilha usando o mouse, arraste a alça de redimensionamento no canto inferior direito da tabela para baixo a fim de selecionar linhas e para a direita a fim de selecionar colunas.

Redimensionar uma tabela

1. Clique em qualquer lugar da tabela. 2. Na guia Design, no grupo Propriedades, clique em Redimensionar Tabela.

3. Na caixa Selecione o novo intervalo de dados da tabela, digite o intervalo a ser usado para a tabela. É possível também clicar no botão Recolher Caixa de Diálogo

na extremidade

direita da caixa Selecione novo intervalo de dados para a tabela e, em seguida, selecione o intervalo a ser usado para a tabela na planilha. Quando terminar, clique no botão Recolher Caixa de Diálogo novamente para exibir a caixa de diálogo inteira. Para

redimensionar

uma

tabela

usando

o

mouse,

arraste

a

alça

de

redimensionamento triangular no canto inferior direito da tabela para cima, para baixo, para a esquerda ou para a direita a fim de selecionar o intervalo a ser usado para a tabela.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 87

Inserir uma linha ou uma coluna em uma tabela 

Para inserir uma ou mais linhas de tabela, selecione uma ou mais linhas da

tabela acima das quais se deseja inserir uma ou mais linhas em branco. 

Para inserir uma ou mais colunas de tabela, selecione uma ou mais colunas da

tabela à esquerda das quais se deseja inserir uma ou mais colunas em branco. Na guia Início, no grupo Células, clique na seta próxima a Inserir .



Para inserir linhas de tabela, clique em Inserir Linhas de Tabela Acima.



Para inserir uma linha de tabela abaixo da última linha, clique em Inserir Linha

de Tabela Abaixo. 

Para inserir colunas de tabela, clique em Inserir Colunas de Tabela à

Esquerda. 

Para inserir uma coluna de tabela à direita da última coluna, clique em Inserir

Coluna de Tabela à Direita.

Também é possível clicar com o botão direito em uma ou mais linhas ou colunas de tabela, apontar para Inserir no menu de atalho e selecionar a ação desejada na lista de opções.

Excluir linhas ou colunas de uma tabela

1. Selecione uma ou mais linhas ou colunas de tabela a serem excluídas. 2. Na guia Início, no grupo Células, clique na seta ao lado de Excluir e clique em Excluir Linhas de Tabela ou Excluir Colunas de Tabela.

Também é possível clicar com o botão direito do mouse em uma ou mais linhas ou colunas, apontar para Excluir no menu de atalho e, em seguida, clicar em Colunas da Tabela ou Linhas da Tabela. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 88

Remover linhas duplicadas de uma tabela

Do mesmo modo que é possível remover duplicatas de dados selecionados no Excel, também é possível remover duplicatas de uma tabela facilmente. 1. Clique em qualquer lugar da tabela. 2. Na guia Design, no grupo Ferramentas, clique em Remover Duplicatas.

3. Na caixa de diálogo Remover Duplicatas, em Colunas, selecione as colunas que contêm duplicatas a serem removidas.

Remover linhas em branco de uma tabela

1. Certifique-se de que a célula ativa esteja em uma coluna da tabela. 2. Clique na seta

no cabeçalho da coluna.

3. Para filtrar por espaços em branco, no menu AutoFiltro, na parte superior da lista de valores, desmarque (Selecionar Tudo) e, em seguida, na parte inferior da lista de valores, selecione (Espaços em Branco). 4. Selecione as linhas em branco na tabela e pressione CTRL + - (hífen).

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 89

6 COMENTÁRIOS O uso de comentários pode ajudá-lo a tornar uma planilha mais fácil de entender fornecendo mais contexto para os dados nela contidos. É possível usar um comentário como uma anotação que fornece informações sobre os dados em uma célula individual ou pode adicionar um comentário ao cabeçalho de uma coluna para fornecer orientações sobre os dados que o usuário deve inserir. Quando uma célula tem um comentário, um indicador vermelho aparece no canto dela. Quando se coloca o ponteiro na célula, o comentário aparece.

Adicionar um comentário 1. Selecione a célula à qual se deseja adicionar um comentário. 2. Na guia Revisão, no grupo Comentários, clique em Novo Comentário.

Também é possível pressionar SHIFT+F2 ou, ao clicar com o botão direito na célula, escolher a opção “Inserir Comentário”. Um novo comentário é criado e o ponteiro passa para ele. Um indicador aparece no canto da célula. Por padrão, o novo comentário recebe um nome. Para usar outro nome, selecione o nome no comentário e digite um novo nome. Ou, para remover o nome, pressione DELETE. 3. No corpo do comentário, digite o texto. 4. Clique fora da caixa de comentário.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 90

A caixa de comentário vai desaparecer, mas o indicador de comentário é mantido. Para manter o comentário visível: 1. Selecione a célula. 2. No grupo Comentários na guia Revisão, clique em Mostrar/Ocultar Comentário.

Editar um comentário 1. Selecione a célula que contém o comentário que se deseja editar. 2. Na guia Revisão, no grupo Comentários, clique em Editar Comentário.

3. Clique duas vezes no texto no comentário e, na caixa de texto de comentário, edite o texto do comentário.

Excluir um comentário 1. Clique na célula que contém o comentário que deseja excluir. 2. Siga um destes procedimentos: 

Na guia Revisão, no grupo Comentários, clique em Excluir.



Na guia Revisão, no grupo Comentários, clique em Mostrar/Ocultar

Comentário para exibir o comentário, clique duas vezes na borda caixa de texto do comentário e pressione DELETE. Também é possível clicar com o botão direito e escolher a opção “Excluir comentário”.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 91

7 NOMEANDO INTERVALOS No Excel, um nome é uma palavra ou sequencia de caracteres que representa uma célula, um intervalo de células, uma fórmula ou mesmo um valor constante. A utilização de nomes facilita muito o entendimento e a manutenção das fórmulas. É possível definir um nome para um intervalo de células, uma função ou uma tabela. Depois, é possível atualizar, auditar e gerenciar facilmente esses nomes.

7.1 Definir um nome 1. Selecione a célula, o intervalo de células ou as seleções não-adjacentes que se deseja nomear; 2. Clique na caixa Nome na extremidade esquerda da

3. Digite o nome que deseja usar para referenciar sua seleção. Os nomes podem ter até 255 caracteres. 4. Pressione ENTER.

OBS.: Não é possível nomear uma célula enquanto estiver alterando o conteúdo da mesma. Também é possível nomear células selecionando-as, clicando com o botão direito e escolhendo a opção “Nomear Intervalo”, ou ainda, clicando na Guia “Fórmulas” e no botão Definir nome.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 92

7.2 Gerenciador de Nomes Permite gerenciar todos os nomes da planilha:

Coluna Ícone e

Exibe: Um destes procedimentos:

nome

Um nome definido, que é indicado por um ícone de nome definido. Um nome de tabela, que é indicado por um ícone de nome de tabela.

Valor

O valor atual do nome, como os resultados de uma fórmula, uma constante de cadeia de caracteres, um intervalo de células, um erro, uma matriz de valores ou um espaço reservado se a fórmula não puder ser avaliada. Os exemplos a seguir são representativos: "esta é minha constante de cadeia de caracteres" 3,1459 {2003;12,2002;23,;2001,18}

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO Refere-se a

Página | 93

A referência atual do nome, como por exemplo: =Plan1!$A$3 =8,3 =HR!$A$1:$Z$345 =SOMA(Plan1!A1,Plan2!B2)

Escopo

Um nome de planilha, se o escopo for o nível local de planilha. "Pasta de trabalho", se o escopo for o nível global de planilha.

Comentário

Informações adicionais sobre o nome com até 255 caracteres, como exemplo: Este valor expirará em 2 de maio de 2007. Não exclua! Nome crítico! Baseado nos números de exame de certificação ISO.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 94

8 VÍNCULOS COM OUTRAS PLANILHAS OU ARQUIVOS É possível vincular uma célula a outra célula que esteja localizada em outra planilha ou arquivo. Isso é útil para realizar cálculos cujos valores estão em outras planilhas. Exemplo: Na planilha 1 há os seguintes valores:

E os seguintes valores na planilha 2:

E na planilha 3 deverá ser exibido qual foi a maior média entre os semestres. Para isso, usamos a função MÁXIMO na planilha 3, referenciando as céluas das planilhas 1 e 2. Na planilha 3, posicione o cursor na célula onde será digitada a fórmula, digite =MÁXIMO e com o mouse clique na guia da Plan1 e clique na célula que consta o valor que deseja buscar (C10). Depois, digita-se ; para separar as células na função, e em seguida, seleciona-se a Plan2 e clica-se na célula correspondente a média do segundo semestre (C7). Por final basta teclar Enter.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 95

Sempre que for alterado o valor das médias automaticamente será atualizado o resultado na planilha 3, criando assim um vínculo. A fórmula na Plan 3 deverá ser:

=MÁXIMO(Plan1!C10;Plan2!C7) Pode-se digitar diretamente a fórmula caso saiba antes o endereço completo, mas deve-se tomar cuidado para respeitar as regras que o Excel exige. Caso as células desejadas estejam em outro arquivo, é necessário que ambos arquivos estejam abertos, e deve-se selecionar as células da planilha utilizando o comando “Alternar Janelas”, na Guia “Exibição.”

OBS.: Note que na fórmula, primeiro aparece o nome de Planilha seguido do nome da célula, separado por um ponto de exclamação ( ! ). Caso as planilhas ou células tenham sido nomeadas, o vínculo pode ser feito utilizando os nomes atribuídos, como por exemplo:

=MÁXIMO(Semestre1!media1;Semestre2!media2)

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 96

9 VALIDAÇÃO DE DADOS Ao trabalhar com dados, é importante que estes sejam inseridos em nas planilhas de forma correta e organizada, pois do contrário, qualquer cálculo e análise pode ser comprometida. O Excel fornece uma excelente ferramenta para controlar os dados inseridos nas planilhas: a Validação de Dados. Selecione as células as quais se deseja criar a validação. Na guia Dados, escolha a opção “Validação de Dados”:

A janela exibe 3 guias:

Configurações Nesta aba são inseridos os critérios de validação das células, ou seja, que tipos de dados ou valores podem ser inseridos. É possível permitir que o usuário digite somente letras, somente números inteiros ou mesmo uma função específica. Por exemplo: o critério é que o usuário só poderá digitar um valor decimal entre 0 e 20. Portanto, o usuário pode digitar 0,1 , 19,99 , 5... caso digite -1 ou mesmo 20,1, o Excel acusará erro.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 97

Mensagem de Entrada Nessa guia é possível digitar uma mensagem que aparece na célula quando o usuário a seleciona. Ao clicar na célula, o usuário verá a mensagem que foi digitada na caixa abaixo:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 98

Alerta de Erro Para tornar o processo de validação ainda mais correto, é possível exibir ao usuário uma mensagem alertando-o sobre o erro e como corrigi-lo. Neste exemplo, o usuário deve digitar somente números entre 0 e 20. Pode-se inserir um alerta explicando isso ao usuário. Caso o usuário digite um valor errado, a mensagem digitada nesta aba será exibida:

Opções: Parar: O Excel pára o cálculo e não aceita o valor, até que o usuário digite um valor correto. Aviso: O Excel avisa sobre o erro, mas caso o usuário ignore a mensagem o Excel aceita o valor errado. Informações: O Excel exibe a mensagem de erro e aceita o valor. Veja abaixo o que ocorre caso seja digitado em uma célula com validação o valor 20,001:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 99

A próxima figura traz outro exemplo: uma validação usando fórmula; ou seja, o Excel só aceitará o valor caso o usuário digite a fórmula tal como consta na aba de Configurações:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 100

10 FILTROS Os filtros servem para exibir somente as linhas que atendem a determinados critérios especificados, ocultando linhas que não se deseja exibir. Depois de filtrar os dados, é possível copiar, localizar, editar, formatar, fazer gráfico e imprimir o subconjunto de dados filtrados sem reorganizá-los nem movê-los. Também é possível filtrar por mais de uma coluna. Os filtros são aditivos, o que significa que cada filtro adicional baseia-se no filtro atual e ainda reduz o subconjunto de dados. Utilizando AutoFiltro, é possível criar três tipos de filtro: por valores de uma lista, por um formato ou por critérios. Cada um desses tipos de filtros é exclusivo para cada intervalo de célula ou tabela de coluna. Por exemplo, é possível filtrar por cor de célula ou por uma lista de números, mas não pelos dois; é possível filtrar por ícone ou por um filtro personalizado, mas não pelos dois. Para obter melhores resultados, não se deve misturar formatos de armazenamento, como texto e número ou número e data, na mesma coluna porque somente um tipo de comando de filtro está disponível para cada coluna. Se houver uma mistura de formatos de armazenamento, o comando exibido será o formato de armazenamento que ocorre com mais freqüência. Por exemplo, se a coluna contiver três valores armazenados como número e quatro como texto, o comando de filtro exibido será Filtros de Texto.

10.1 Filtrar Texto 1. Selecione um intervalo de célula que contenha dados alfanuméricos. 2. Na guia Página Inicial, no grupo Edição, clique em Classificar e Filtrar e, em seguida, clique em Filtrar.

1. Clique na seta

no cabeçalho da coluna.

2. Siga um destes procedimentos:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 101

 Selecionar a partir de uma lista de valores de texto

Na lista de valores de texto, marque ou desmarque um ou mais valores de texto que será utilizado no filtro. A lista de valores de texto pode ter até 10.000 itens. Se a lista for maior, desmarque (Selecionar Tudo) na parte superior e, em seguida, selecione os valores de texto específicos para serem utilizados no filtro. Para tornar o menu AutoFiltro mais largo ou comprido, clique e arraste a alça de grade na parte inferior.

 Criar critérios

1. Aponte para Filtros de Texto e, em seguida, clique em um dos comandos do operador de comparação ou clique em Filtro Personalizado. Por exemplo, para filtrar por texto que comece com um caractere específico, selecione Começar com ou para filtrar por texto que contenha caracteres específicos em qualquer lugar do texto, selecione Contém. 2. Na caixa de diálogo Personalizar AutoFiltro, na caixa à direita, digite o texto e selecione o valor de texto na lista. Por exemplo, para filtrar por texto que comece com a letra "J", digite J ou para filtrar por um texto que tenha "bell" em qualquer lugar no texto, digite bell. Se precisar localizar texto que compartilhe alguns caracteres mas não outros, use um caractere curinga:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 102

USE ? (ponto de interrogação)

PARA LOCALIZAR Qualquer caractere único Por exemplo, antoni? localizará "antonio" e "antonia"

* (asterisco)

Qualquer número de caracteres Por exemplo, *este localiza "Nordeste" e "Sudeste"

~ (til) seguido de ?, * ou ~ Um ponto de interrogação, asterisco ou til Por exemplo, fy06~? localizará "fy06?"

 Filtro por um ou mais critérios

Para filtrar a coluna da tabela ou a seleção, para que ambos os critérios sejam verdadeiros, selecione E. Para filtrar a coluna da tabela ou a seleção, para que um ou ambos os critérios possam ser verdadeiros, selecione OU. Na entrada secundária, selecione um operador de comparação e, em seguida, na caixa à direita, digite o texto ou selecione um valor de texto na lista.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 103

 Filtrar por espaços em branco ou espaços preenchidos

1. Na guia Página Inicial, no grupo Edição, clique em Classificar e Filtrar e, em seguida, clique em Filtrar.

1. Clique na seta 

no cabeçalho da coluna.

Para filtrar por espaços preenchidos, no menu AutoFiltro, na parte superior da

lista de valores, selecione (Selecionar Tudo) e, em seguida, na parte inferior da lista de valores, desmarque (Células Vazias). 

Para filtrar por espaços em branco, no menu AutoFiltro, na parte superior da lista

de valores, desmarque (Selecionar Tudo) e, em seguida, na parte inferior da lista de valores, selecione (Células Vazias). OBS.: A caixa de seleção (Espaços em Branco) estará disponível somente se o intervalo de células ou a coluna da tabela contiver no mínimo uma célula em branco.

10.2 Filtrar números 1. Selecione um intervalo de célula que contenha dados numéricos. 2. Na guia Página Inicial, no grupo Edição, clique em Classificar e Filtrar e, em seguida, clique em Filtrar.

Clique na seta

no cabeçalho da coluna. Siga um destes procedimentos:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 104

 Filtrar por número superior ou inferior

1. Selecione um intervalo de célula que contenha dados numéricos. 2. Na guia Página Inicial, no grupo Edição, clique em Classificar e Filtrar e, em seguida, clique em Filtrar.

Clique na seta

no cabeçalho da coluna.

Aponte para Filtros de Números e selecione 10 Primeiros.Na caixa de diálogo AutoFiltro - 10 Primeiros, faça o seguinte:

Na caixa à esquerda, clique em Primeiros ou em Últimos.Na caixa do meio, digite um número. Na caixa à direita, para filtrar por número, clique em Itens. Para filtrar por porcentagem, clique em Porcentagem. Os valores superior e inferior baseiam-se no intervalo original de células ou na coluna da tabela e não no subconjunto filtrado de dados. A porcentagem indica o percentual de itens que será filtrado dentre o total selecionado.  Filtrar por números acima ou abaixo da média 1. Selecione um intervalo de célula que contenha dados numéricos. 2. Na guia Página Inicial, no grupo Edição, clique em Classificar e Filtrar e, em seguida, clique em Filtrar.

3. Clique na seta

no cabeçalho da coluna.

4. Aponte para Filtros de Números. 5. Para filtrar por números que estão acima da média, clique em Acima da Média. Para filtrar por números que estão abaixo da média, clique em Abaixo da Média.

OBS.: Os números acima e abaixo da média baseiam-se no intervalo original de células Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 105

ou na coluna da tabela e não no subconjunto filtrado de dados.

 Selecionar a partir de uma lista de números Na lista de números, marque ou desmarque um ou mais números que serão utilizados no filtro. A lista de números pode ter até 10.000 itens. Se a lista for maior, desmarque (Selecionar Tudo) na parte superior e, em seguida, selecione os números específicos para serem utilizados no filtro.  Criar critérios 1. Aponte para Filtros de Números e, em seguida, clique em um dos comandos do operador de comparação ou clique em Filtro Personalizado. 2. Na caixa de diálogo Personalizar AutoFiltro, na caixa ou nas caixas à direita, digite o número ou selecione os números na lista. Por exemplo, para filtrar por um número superior a 25 e inferior a 50, digite 25 e 50. 3. Como opção, filtre por um ou mais critérios.  Como adicionar um ou mais critérios Para filtrar a coluna da tabela ou a seleção, para que ambos os critérios sejam verdadeiros, selecione E. Para filtrar a coluna da tabela ou a seleção, para que um ou ambos os critérios possam ser verdadeiros, selecione OU. Na entrada secundária, selecione um operador de comparação e, em seguida, na caixa à direita, digite o número ou selecione um número na lista.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 106

10.3 Filtrar datas ou horas 1. Selecione um intervalo de célula que contenha dados numéricos. 2. Na guia Página Inicial, no grupo Edição, clique em Classificar e Filtrar e, em seguida, clique em Filtrar.

Clique na seta

no cabeçalho da coluna.Siga um destes procedimentos:

 Selecionar a partir da uma lista de datas ou horas Na lista de datas ou horas, marque ou desmarque uma ou mais datas ou horas a serem utilizadas no filtro. Por padrão, todas as datas no intervalo de células são agrupadas por hierarquia de anos, meses e dias. Marcar ou desmarcar o nível mais alto na hierarquia marca ou desmarca todas as datas aninhadas abaixo daquele nível. Por exemplo, ao selecionar 2006, os meses serão listados abaixo de 2006 e os dias, abaixo de cada mês. A lista de valores pode ter até 10.000 itens. Se a lista for maior, desmarque (Selecionar Tudo) na parte superior e, em seguida, selecione os valores específicos para serem utilizados no filtro.  Filtro Comum Um filtro comum baseia-se em um operador de comparação. 1. Clique em um dos comandos do operador de comparação (Igual a, Anterior, Posterior ou Entre) ou clique em Personalizar Filtro. 2. Na caixa de diálogo Personalizar AutoFiltro, na caixa à direita, digite a data ou a hora, selecione uma data ou hora na lista ou clique no botão Calendário para localizar e inserir uma data. 3. Na caixa de diálogo Personalizar AutoFiltro, na caixa ou nas caixas à direita, digite a data ou a hora, selecione uma data ou hora na lista ou clique no botão Calendário para localizar e inserir uma data. Por exemplo, para filtrar por uma data anterior a "3/1/2006" e uma data posterior a "6/1/2006", digite 3/1/2006 e 6/1/2006. Ou, para filtrar por uma hora anterior a "08:00:00" e Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 107

uma hora posterior a "12:00 PM", digite 8:00 AM e 12:00 PM.

10.4 Filtrar por cor de célula, cor de fonte ou conjunto de ícones Caso um intervalo de células estejam formatadas, por cor de célula ou cor de fonte, é possível filtrar por essas cores. Também será possível filtrar por um conjunto de ícones criados por meio da formação condicional. Selecione um intervalo de células contendo formatação por cor de célula, cor de fonte ou conjunto de ícones. Na guia Página Inicial, no grupo Edição, clique em Classificar e Filtrar e, em seguida, clique em Filtrar.

Clique na seta

no cabeçalho da coluna.

Selecione Filtrar por Cor e, em seguida, dependendo do tipo de formatação, selecione Filtrar por Cor de Célula, Filtrar por Cor de Fonte ou Filtrar por Ícone de Célula. Dependendo do tipo de formato, selecione uma cor, cor de fonte ou ícone de célula.

10.5 Filtrar por seleção É possível filtrar dados rapidamente com critérios que são iguais ao conteúdo da célula ativa.Em um intervalo de célula ou na coluna da tabela, clique com o botão direito do mouse na célula que contém o valor, a cor, a cor de fonte ou o ícone que deseja utilizar no filtro. Clique em Filtrar e siga um destes procedimentos: 

Para filtrar por texto, número ou data ou hora, clique em Filtrar por Valor de

Célula Selecionado. 

Para filtrar por cor de célula, clique em Filtrar por Cor de Célula Selecionada.



Para filtrar por cor de fonte, clique em Filtrar por Cor de Fonte Selecionada.



Para filtrar por ícone, clique em Filtrar por Ícone de Célula Selecionada.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 108

10.6 Removendo valores duplicados Por vezes, é interessante remover da planilha dados que estão repetidos por várias linhas. No entanto, deve-se ter cautela ao usar este recurso, pois os dados removidos serão excluídos de permanentemente. Convém copiar o intervalo de células ou a tabela original para outra planilha ou pasta de trabalho antes de remover valores duplicados. Como alternativa, é possível filtrar valores únicos, temporariamente ocultando valores duplicados, mas ao remover valores duplicados, são excluídos permanentemente os valores duplicados. 1. Selecione o intervalo de células ou verifique se a célula ativa está na tabela. 2. Na guia Dados, no grupo Ferramentas de Dados, clique em Remover Duplicatas.

3. Em Colunas, selecione uma ou mais colunas.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 109

4. Ao marcar a opção “Meus dados contém cabeçalhos”, está sendo informado ao Excel que a primeira linha da seleção contém cabeçalhos de colunas, e portanto, esta linha não será incluída na exclusão de dados.

5. Clique em OK. É exibida uma mensagem indicando quantos valores duplicados foram removidos e quantos valores únicos permanecem, ou indicando que não foram removidos valores duplicados. Para terminar, clique em OK.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 110

11 CLASSIFICAÇÃO A classificação de dados é uma parte importante da análise de dados, como por exemplo, colocar uma lista de nomes em ordem alfabética, do mais alto para o mais baixo ou organizar linhas por cores ou ícones. A classificação de dados ajuda a visualizar e a compreender os dados de modo mais rápido e melhor, organizar e localizar dados desejados e por fim tomar decisões mais efetivas. É possível classificar dados por texto (A a Z ou Z a A), números (dos menores para os maiores ou dos maiores para os menores) e datas e horas (da mais antiga para a mais nova e da mais nova para o mais antiga) em uma ou mais colunas. Também é possível classificar por uma lista de clientes (como Grande, Médio e Pequeno) ou por formato, incluindo a cor da célula, a cor da fonte ou o conjunto de ícones. Os critérios de classificação são salvos com a pasta de trabalho de forma que se possa reaplicar a classificação toda vez que abrir a pasta de trabalho para uma tabela do Excel, mas não para um intervalo de células. Se deseja salvar os critérios de classificação de forma que possa reaplicar, periodicamente, uma classificação quando abrir uma pasta de trabalho, então, é recomendável usar uma tabela. Este procedimento será especialmente importante para classificações em várias colunas ou para classificações que demandam muito tempo para serem criadas. Outro ponto importante é diferenciar a Classificação do Filtro. Quando usamos filtros, estamos selecionando apenas alguns valores de uma lista. Quando classificamos valores, estamos trabalhando com todo o conjunto de dados da lista.

11.1 Classificar texto 1. Selecione uma coluna de dados alfanuméricos em um intervalo de células ou certifique-se de que a célula ativa está em uma coluna da tabela que contenha dados alfanuméricos. 2. Na guia Início, no grupo Editar e, em seguida, clique em Classificar e Filtrar

3. Siga um destes passos: Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 111

 Para

classificar em ordem alfanumérica crescente, clique em Classificar de A a Z.

 Para

classificar em ordem alfanumérica decrescente, clique em Classificar de Z a

A. 4. Como opção, pode-se fazer uma classificação que diferencie letras maiúsculas de minúsculas:  Na guia Página Inicial, no grupo Edição, clique em Classificar e Filtrar e, em seguida, clique em Personalizar Classificação.  Na caixa de diálogo Classificar, clique em Opções.  Na caixa de diálogo Classificar Opções, selecione Diferenciar Maiúsculas de Minúsculas.  Clique em OK duas vezes. OBS.: Se a coluna que deseja classificar contiver números armazenados como números e como texto, será necessário formatar todos como texto. Se não fizer isso, os números armazenados como números serão classificados antes que sejam armazenados como texto. Para formatar todos os dados selecionados como texto, na guia Início, no grupo Fonte, clique no botão Formatar Fonte da Célula, clique na guia Número e, em seguida, em Categoria, clique em Texto. Em alguns casos, dados importados de outro aplicativo podem apresentar espaços à esquerda, inseridos antes dos dados. Remova os espaços à esquerda antes de classificar os dados.

11.2 Classificar números 1. Selecione uma coluna de dados numéricos em um intervalo de células ou certifique-se de que a célula ativa está em uma coluna da tabela que contenha dados numéricos. 2. Na guia Início, no grupo Edição, clique em Classificar e Filtrar e, em seguida, siga um destes procedimentos: 

Para classificar de números baixos para números altos, clique em Classificar

do Menor para o Maior. 

Para classificar de números altos para números baixos, clique em Classificar

do Maior para o Menor. OBS.: Se os resultados não forem os esperados, a coluna pode conter números Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 112

armazenados como texto. Por exemplo, os números negativos importados de alguns sistemas de contabilidade ou um número digitado com um apóstrofo à esquerda (') são armazenados como texto.

11.3 Classificar datas ou horas 1. Selecione uma coluna de data ou hora em um intervalo de células ou certifiquese de que a célula ativa está em uma coluna da tabela que contenha data ou hora. 2. Na guia Início, no grupo Edição, clique em Classificar e Filtrar e, em seguida, siga um destes procedimentos: 

Para classificar de uma data e hora anterior para uma data ou hora mais

recente, clique em Classificar da Mais Antiga para a Mais Nova. 

Para classificar de uma data e hora recente para uma data ou hora mais

antiga, clique em Classificar da Mais Nova para a Mais Antiga. OBS.: Se o resultado não for o esperado, a coluna pode conter datas ou horas armazenadas como texto e não como datas ou horas. Para que o Excel classifique as datas e horas corretamente, todas as datas e horas em uma coluna devem ser armazenadas como um número de série de data ou hora. Se o Excel não puder reconhecer um valor como uma data ou hora, a data ou a hora será armazenada como texto. Se quiser classificar por dias da semana, formate as células para mostrar o dia da semana. Se quiser classificar por dia da semana independentemente da data, converta-as para texto utilizando a função TEXTO. Entretanto, a função TEXTO retorna um valor de texto e, portanto, a operação de classificação deve ser baseada em dados alfanuméricos

11.4 Classificar cor de célula, cor de fonte ou ícones Se formatou manual ou condicionalmente um intervalo de células ou uma tabela de coluna, por cor de célula ou cor de fonte, poderá classificar por essas cores. Também será possível classificar por um conjunto de ícones criados por meio da formação condicional. 1.

Selecione uma coluna de dados em um intervalo de células ou certifique-se

de que a célula ativa esteja em uma coluna da tabela. 2.

Na guia Página Inicial, no grupo Edição, clique em Classificar e Filtrar e,

em seguida, clique em Personalizar Classificação. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 113

A caixa de diálogo Classificar é exibida. 3.

Em Colunas, na caixa Classificar por, selecione a coluna que deseja

classificar. 4.

Em Classificar em, selecione o tipo de classificação. Siga um destes

procedimentos: 

Para classificar por cor de célula, selecione Cor de Célula.



Para classificar por cor de fonte, selecione Cor de Fonte.



Para classificar por um conjunto de ícones, selecione Ícone de Célula.

5.

Em Ordem, clique na seta ao lado do botão e, em seguida, dependendo do

tipo de formato, selecione uma cor de célula, cor de fonte ou ícone de célula. 6.

Em Ordenar, selecione como deseja fazer a classificação. Siga um destes

procedimentos: 

Para mover a cor da célula, a cor da fonte e o ícone para a parte superior ou

esquerda, selecione Na Parte Superior para uma classificação de coluna e À Esquerda para uma classificação de linha. 

Para mover a cor da célula, a cor da fonte e o ícone para a parte inferior ou

direita, selecione Na Parte Inferior para uma classificação da coluna e À Direita para uma classificação de linha. OBS.: Não há nenhuma cor de célula, cor de fonte ou ordem de classificação de ícone padrão. Deve-se definir a ordem desejada para cada operação de classificação. 7.

Para especificar a próxima cor de célula, cor de fonte ou ícone a serem

usados na classificação, clique em Adicionar Nível e, em seguida, repita as etapas de três a cinco. Certifique-se de selecionar a mesma coluna na caixa Em seguida por e de fazer a mesma seleção em Ordenar.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 114

11.5 Classificar linhas 1.

Selecione uma linha de dados em um intervalo de células ou certifique-se de

que a célula ativa esteja em uma coluna da tabela. 2.

Na guia Página Inicial, no grupo Edição, clique em Classificar e Filtrar e,

em seguida, clique em Personalizar Classificação.

A caixa de diálogo Classificar é exibida. 3.

Clique em Opções.

4.

Na caixa de diálogo Opções de Classificação, em Orientação, clique em

Classificar da esquerda para a direita e, em seguida, clique em OK. 5.

Em Coluna, na caixa Classificar por, selecione a linha que deseja

classificar.

6.

Siga um destes procedimentos:

Por valor Em Classificar em, selecione Valores. Em Ordem, siga um destes procedimentos: 

Para os valores de texto, selecione A a Z ou Z a A.



Para valores de número, selecione Do menor para o Maior ou Do

Maior para o Menor. 

Para valores de data ou hora, selecione Do Mais Antigo para o Mais

Novo ou Do Mais Novo para o Mais Antigo. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 115

11.6 Por cor de célula, cor de fonte ou ícone de célula 1. Em Classificar em, selecione Cor da Célula, Cor da Fonte ou Ícone de Célula. 2. Clique na seta próxima ao botão e, em seguida, selecione uma cor de célula, cor de fonte ou ícone de célula. 3. Em Ordenar, selecione À Esquerda ou À Direita.

11.7 Classificar por mais de uma coluna ou linha É possível classificar por mais de uma coluna ou linha quando houver dados que deseja agrupar pelo mesmo nível em uma coluna ou linha e, em seguida, classificar outra coluna ou linha dentro daquele grupo de valores iguais. Por exemplo, se tiver uma coluna Departamento e Funcionário, poderá classificar primeiro por Departamento (para agrupar todos os funcionários no mesmo departamento) e, em seguida, classificar por nome (para colocar os nomes em ordem alfabética dentro de cada departamento). É possível classificar por até 64 colunas. 1.

Selecione um intervalo de célula com duas ou mais colunas ou certifique-se

de que a célula ativa esteja em uma tabela com duas ou mais colunas. 2.

Na guia Página Inicial, no grupo Edição, clique em Classificar e Filtrar e,

em seguida, clique em Personalizar Classificação.

A caixa de diálogo Classificar é exibida. 3. Em Coluna, na caixa Classificar por, selecione a primeira coluna que deseja classificar. 4.

Em Classificar em, selecione o tipo de classificação.

5.

Em Ordenar, selecione como deseja fazer a classificação.

6.

Para adicionar outra coluna pela qual classificar, clique em Adicionar Nível

e, em seguida, repita as etapas de três a cinco.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO 7.

Página | 116

Para copiar uma coluna pela qual classificar, selecione a entrada e, em

seguida, clique em Copiar Nível. 8.

Para excluir uma coluna pela qual classificar, selecione a entrada e, em

seguida, clique em Excluir Nível. 9.

Para alterar a ordem na qual as colunas são classificadas, selecione uma

entrada e, em seguida, clique na seta Para Cima ou Para Baixo para alterar a ordem. As entradas superiores na lista são classificadas antes das entradas inferiores na lista. Antes:

Depois:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 117

11.8 Classificar uma coluna em um intervalo de células sem afetar outros Cuidado ao usar esse recurso. A classificação por uma coluna em um intervalo pode gerar resultados indesejados, como movimentação de células naquela coluna para fora de outras células na mesma linha. Selecione uma coluna em um intervalo de células contendo duas ou mais colunas. Para selecionar a coluna que deseja classificar, clique no título da coluna. Na guia Início, no grupo Edição, clique em Classificar e Filtrar e siga um destes procedimentos: A caixa de diálogo Aviso de Classificação é exibida. Selecione Continuar com a seleção atual. Clique em Classificar. Selecione outras opções de classificação desejadas na caixa de diálogo Classificar e, em seguida, clique em OK. Se os resultados forem diferentes do esperado, clique em Desfazer

.

OBS.: No exemplo acima, classificar somente os nomes dos funcionários por ordem A-Z causaria um erro, uma vez que o Excel iria ordenar somente a coluna dos nomes, alterando o respectivo departamento de cada funcionário.

Se obtiver resultados inesperados ao classificar dados  Verifique se os valores retornados por uma fórmula foram alterados. Se os dados classificados contiverem uma ou mais fórmulas, os valores retornados dessas fórmulas poderão ser alterados quando a planilha for recalculada. Nesse caso, certifiquese de reaplicar a classificação ou executar a classificação novamente para obter resultados atualizados.  Reexiba linhas e colunas antes de classificar. As colunas ocultas não são movidas quando se classifica colunas, e as linhas ocultas não são movidas quando se classifica linhas. Antes de classificar dados, é uma ótima idéia reexibir as colunas e as linhas ocultas.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 118

 Verifique a definição de localidade. Ordens de classificação variam de acordo com a definição de localidade. Verifique se está usando a configuração adequada em Configurações Regionais ou Opções Regionais e de Idioma no Painel de Controle do computador.  Insira títulos de colunas em apenas uma linha. Se desejar rótulos de várias linhas, quebre a linha dentro da célula.  Ative ou desative a linha de título. É normalmente melhor ter uma linha de título ao classificar uma coluna para tornar mais fácil compreender o significado dos dados. Por padrão, o valor no título não está incluso na operação de classificação. Ocasionalmente, pode ser necessário ativar ou desativar o título para que o valor no título seja ou não incluído na operação de classificação. Siga um destes procedimentos: 

Para excluir a primeira linha de dados da classificação porque ela é um título de

coluna, na guia Início, no grupo Edição, clique em Classificar e Filtrar, clique em Personalizar Classificação e, em seguida, selecione Meus dados têm cabeçalhos. 

Para incluir a primeira linha de dados na classificação porque ela não é um título

de coluna, na guia Início, no grupo Edição, clique em Classificar e Filtrar, clique em Personalizar Classificação e, em seguida, desmarque Meus dados têm cabeçalhos.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 119

12 FORMATAÇÃO CONDICIONAL Ao analisar dados, é possível deparar com questões como: 

Quem vendeu mais do que R$ 50.000 este mês?



Qual é a distribuição etária geral dos empregados?



Quais produtos têm aumentos de faturamento acima de 10%?



Quais são os alunos com melhor e pior desempenho na classe?

A formatação condicional ajuda a responder essas questões tornando mais fácil destacar células ou intervalos de células, enfatizar valores não-usuais e visualizar dados usando barras de dados, escalas de cores e conjuntos de ícones. Um formato condicional altera a aparência de um intervalo de células com base em uma condição (ou critério). Se a condição for verdadeira, o intervalo de células será formatado com base nessa condição; se a condição for falsa, o intervalo de células não será formatado com base nessa condição.

OBS.: Quando criar um formato condicional, pode-se fazer referência a outras células na mesma planilha, mas não poderá fazer referência a células em outras planilhas na mesma pasta de trabalho, ou utilizar referências externas a outra pasta de trabalho.

12.1 Formatar todas as células usando uma escala em duas cores Escalas de cores são guias visuais que ajudam a entender a distribuição e a variação de dados. Uma escala de duas cores ajuda a comparar um intervalo de células usando uma gradação de duas cores. O tom da cor representa valores maiores ou menores. Por exemplo, em uma escala de cores verde e vermelha, pode-se especificar que células de valores mais altos tenham cor verde e células de valores mais baixos tenham a cor vermelha.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 120

Formatação rápida

1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. 2. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional e, em seguida, clique em Escalas de Cor.

3. Selecione uma escala de duas cores.

OBS.: Passe o mouse sobre os ícones de escala de cores para ver qual deles corresponde a uma escala de duas cores. A cor da parte superior representa valores maiores; e a da parte inferior, valores menores.

Formatação avançada

Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional e, em seguida, clique em Gerenciar Regras. A caixa de diálogo Gerenciador de Regras de Formatação Condicional será exibida. Para adicionar um formato condicional, clique em Nova Regra. A caixa de diálogo Nova Regra de Formatação será exibida. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 121

Para alterar/criar um formato condicional

1. Verifique se a planilha, a tabela ou o relatório de Tabela Dinâmica apropriado está selecionado na caixa de listagem Mostrar regras de formatação para. 2. Como opção, altere o intervalo de células clicando em Recolher Caixa de Diálogo

na caixa Aplica-se a para ocultar temporariamente a caixa de diálogo,

selecionando o novo intervalo de células na planilha e, em seguida, selecionando Expandir Caixa de Diálogo

.

3. Selecione a regra e, em seguida, clique em Editar regra. A caixa de diálogo Editar Regra de Formatação será exibida. 4. Em Selecione um Tipo de Regra, clique em Formatar todas as células com base nos valores. 5. Em Edite a Descrição da Regra, na caixa de listagem Formatar Estilo, selecione Escala Bicolor.

6. Selecione um Tipo Mínimo e Máximo. Siga um destes procedimentos:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO 

Página | 122

Formatar valor mais alto e valor mais baixo: Selecione Valor Mais Baixo e Valor

Mais Alto. Neste caso, não se digita um Valor para Mínimo e Máximo. 

Formatar um valor numérico, de data ou hora: Selecione Número e digite um Valor

Mínimo e Máximo. 

Formatar uma porcentagem: Selecione Porcentagem e digite um Valor para Mínimo

e Máximo. Os valores válidos estão entre 0 (zero) e 100. Não digite o sinal de porcentagem. Use uma porcentagem quando desejar visualizar todos os valores proporcionalmente porque a distribuição de valores é proporcional. 

Formatar um percentil: Selecione Percentil e digite um Valor para Mínimo e

Máximo. Os percentuais válidos estão entre 0 (zero) e 100. Não é possível utilizar um percentual se o intervalo de células contiver mais de 8.191 pontos de dados. 

Formatar um resultado de fórmula: Selecione Fórmula e digite um Valor Mínimo e

Máximo. A fórmula deve retornar um número, uma data ou uma hora. Inicie a fórmula com um sinal de igual (=). Fórmulas inválidas resultam em nenhuma formatação aplicada. Convém testar a fórmula na planilha para assegurar que ela não retorne um valor de erro. Valores Mínimo e Máximo são os valores mínimo e máximo do intervalo de células. Verifique se o valor Mínimo é menor do que o valor Máximo. Pode-se escolher um Tipo diferente para Mínimo e Máximo. Por exemplo, é possível escolher um Número para Mínimo e um Percentual para Máximo.

7. Para escolher uma escala de cores para Mínimo e Máximo, clique em Cor para cada um e, em seguida, selecione uma cor. Se desejar escolher cores adicionais ou criar uma cor personalizada, clique em Mais Cores. A escala de cores selecionada será exibida na caixa Visualização.

12.2 Formatar todas as células usando uma escala de três cores Uma escala de três cores ajuda a comparar um intervalo de células usando uma gradação de três cores. O tom da cor representa valores maiores, médios ou menores. Por exemplo, em uma escala de cores verde, amarela e vermelha, é possível especificar que células de valores mais altos tenham a cor verde, valores médios tenham a cor amarela e células de valores mais baixos tenham a cor vermelha.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 123

Formatação rápida

1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. 2. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional e clique em Escalas de Cores.

3. Selecione uma escala de três cores. A cor da parte superior representa valores maiores, a cor do centro representa valores médios e a cor da parte inferior representa valores menores. Passe o mouse sobre os ícones de escala de cores para ver qual deles corresponde a uma escala de três cores.

Formatação avançada

1. Selecione uma ou mais células em um intervalo. 2. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional e clique em Gerenciar Regras. A caixa de diálogo Gerenciador de Regras de Formatação Condicional será exibida. Para adicionar um formato condicional, clique em Nova Regra. A caixa de diálogo Nova Regra de Formatação será exibida.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 124

Para alterar/criar um formato condicional

1. Verifique se a planilha, a tabela ou o relatório de Tabela Dinâmica apropriado está selecionado na caixa de listagem Mostrar regras de formatação para. 2. Como opção, altere o intervalo de células clicando em Recolher Caixa de Diálogo

na caixa Aplica-se a para ocultar temporariamente a caixa de diálogo,

selecionando o novo intervalo de células na planilha e, em seguida, selecionando Expandir Caixa de Diálogo

.

3. Selecione a regra e, em seguida, clique em Editar regra. A caixa de diálogo Editar Regra de Formatação será exibida. 4. Em Selecione um Tipo de Regra, clique em Formatar todas as células com base em seus respectivos valores. 5. Em Edite a Descrição da Regra, na caixa de listagem Formatar Estilo, selecione Escala Tricolor

. 6. Selecione um Tipo para Mínimo, Ponto Médio e Máximo. Siga um destes procedimentos: 

Formatar valor mais alto e valor mais baixo: Selecione apenas Ponto Médio.

Neste caso, se digita um Valor Mais Baixo e Mais Alto. 

Formatar um valor numérico, de data ou hora: Selecione Número e digite um

valor para Mínimo, Ponto Médio e Valor Máximo. 

Formatar uma porcentagem: Selecione Percentual e digite um valor para

Mínimo, Ponto Médio e Valor Máximo. Os valores válidos estão entre 0 (zero) e 100. Use uma porcentagem quando desejar visualizar todos os valores proporcionalmente porque a distribuição de valores é proporcional. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO 

Página | 125

Formatar um percentil Selecione Percentual e digite um valor para Mínimo,

Ponto Médio e Valor Máximo. Os percentuais válidos estão entre 0 (zero) e 100. Não se pode usar um percentual se o intervalo de células contiver mais de 8.191 pontos de dados. 

Formatar um resultado de fórmula: Selecione Fórmula e digite um valor para

Mínimo, Ponto Médio e Valor Máximo. A fórmula deve retornar um número, uma data ou uma hora. Inicie a fórmula com um sinal de igual (=). Fórmulas inválidas resultam em nenhuma formatação aplicada. Convém testar a fórmula na planilha para assegurar que ela não retorne um valor de erro.

Valores Mínimo, Ponto Médio e Máximo são os valores mínimo, de ponto médio e máximo do intervalo de células. Verifique se o valor Mínimo é menor do que o valor de Ponto Médio, que por sua vez deve ser menor do que o valor Máximo. Pode-se escolher um Tipo diferente para Mínimo, Ponto Médio e Máximo. Por exemplo, é possível escolher um Número para Mínimo, um Percentil para Ponto Médio e um Percentual para Máximo.Em muitos casos, o valor padrão de 50% para Ponto Médio funciona melhor, mas é possível ajustá-lo de acordo com exigências específicas. Para escolher uma escala de cores para Mínimo, Ponto Médio e Máximo, clique em Cor para cada um e, em seguida, selecione uma cor. Se desejar escolher cores adicionais ou criar uma cor personalizada, clique em Mais Cores. A escala de cores selecionada será exibida na caixa Visualização.

12.3 Formatar todas as células usando barras de dados Uma barra de dados ajuda a ver o valor de uma célula com relação a outras células. O comprimento da barra de dados representa o valor na célula. Uma barra mais longa representa um valor mais alto e uma barra mais curta representa um valor mais baixo. Barras de dados são úteis para indicar números mais altos e mais baixos, especialmente com grandes quantidades de dados, como brinquedos mais e menos vendidos em um relatório de vendas de fim de ano.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 126

Formatação rápida 1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. 2. Na guia Página Inicial, no grupo Estilo, clique na seta ao lado de Formatação Condicional, clique em Barras de Dados e, em seguida, selecione um ícone de barra de dados.

Formatação avançada

1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. 2. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional e, em seguida, clique em Gerenciar Regras. A caixa de diálogo Gerenciador de Regras de Formatação Condicional será exibida. Para adicionar um formato condicional, clique em Nova Regra.A caixa de diálogo Nova Regra de Formatação será exibida.

Para alterar/criar um formato condicional

1. Verifique se a planilha, a tabela ou o relatório de Tabela Dinâmica apropriado está selecionado na caixa de listagem Mostrar regras de formatação para. 2. Como opção, altere o intervalo de células clicando em Recolher Caixa de Diálogo

na caixa Aplica-se a para ocultar temporariamente a caixa de diálogo, Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 127

selecionando o novo intervalo de células na planilha e, em seguida, selecionando Expandir Caixa de Diálogo

.

3. Selecione a regra e, em seguida, clique em Editar regra.A caixa de diálogo Editar Regra de Formatação será exibida. 4. Em Selecione um Tipo de Regra, clique em Formatar todas as células com base nos valores. 5. Em Edite a Descrição da Regra, na caixa de listagem Formatar Estilo, selecione Barra de Dados.

Selecione um Tipo para Barra Mais Curta e para Barra Mais Longa. Os Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 128

procedimentos são os mesmos citados acima: Formatar valor mais alto e valor mais baixo, Formatar um valor numérico, de data ou hora, Formatar uma porcentagem, Formatar um percentil e Formatar um resultado de Fórmula.

OBS.: Verifique se o valor da Barra Mais Curta é menor do o valor da Barra Mais Longa. É possível escolher um Tipo diferente para Barra Mais Curta e Barra Mais Longa. Por exemplo, é possível escolher um Número para Barra Mais Curta e um Percentual para Barra Mais Longa.

Para escolher uma escala de cores para a Barra Mais Curta e a Barra Mais Longa, clique em Cor da Barra. Se desejar escolher cores adicionais ou criar uma cor personalizada, clique em Mais Cores. Para mostrar apenas a barra de dados e não o valor na célula, selecione Mostrar Barra Somente.

12.4 Formatar todas as células usando um conjunto de ícones Use um conjunto de ícones para anotar e classificar dados em três a cinco categorias separadas por um valor limite. Cada ícone representa um intervalo de valores. Por exemplo, no conjunto de ícones de 3 Setas, a seta verde para cima representa valores mais altos, a seta amarela lateral representa valores médios e a seta vermelha para baixo representa valores mais baixos.

Formatação rápida 1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. 2. Na guia Página Inicial, no grupo Estilo, clique na seta ao lado de Formatação Condicional, clique em Conjunto de Ícones e selecione um conjunto de ícones.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 129

Formatação avançada

1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. 2. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional e, em seguida, clique em Gerenciar Regras. A caixa de diálogo Gerenciador de Regras de Formatação Condicional será exibida. Para adicionar um formato condicional, clique em Nova Regra.A caixa de diálogo Nova Regra de Formatação será exibida.

Para alterar/criar um formato condicional

1. Verifique se a planilha apropriada está selecionada na caixa de listagem Mostrar regras de formatação para. 2. Como opção, altere o intervalo de células clicando em Recolher Caixa de Diálogo

na caixa Aplica-se a para ocultar temporariamente a caixa de diálogo,

selecionando o novo intervalo de células na planilha e, em seguida, selecionando Expandir Caixa de Diálogo

.

3. Selecione a regra e, em seguida, clique em Editar regra.A caixa de diálogo Editar Regra de Formatação será exibida.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 130

4. Em Selecione um Tipo de Regra, clique em Formatar todas as células com base nos valores. 5. Em Edite a Descrição da Regra, na caixa de listagem Formatar Estilo, selecione Conjunto de Ícones. Selecione um conjunto de ícones.

O padrão é 3 Semáforos (Não Coroados). O número de ícones e os operadores de comparação e valores limites padrão podem variar para cada conjunto de ícones. Se desejar, poderá ajustar os operadores de comparação e os valores limites. O intervalo padrão de valores para cada ícone é igual em tamanho, mas é possível ajustá-lo para atender a exigências específicas. Verifique se os limites estão em uma sequência lógica do mais alto para o mais baixo, do superior para o inferior. Os tipos são: Formatar um valor numérico, de data ou hora, Formatar uma porcentagem, Formatar um percentil, Formatar um resultado de fórmula. Para que o primeiro ícone represente valores mais baixos e o último represente valores mais altos, selecione Ordem Inversa de Ícones.Para mostrar apenas o ícone e não o valor na célula, selecione Mostrar Somente Ícone. OBS.: Pode ser necessário ajustar a largura da coluna para acomodar o ícone.Há três tamanhos de ícones. O tamanho do ícone exibido depende do tamanho da fonte usada na célula.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 131

12.5 Formatar células com textos, números ou valores de data e hora Para localizar mais facilmente células específicas em um intervalo de células, é possível formatar essas células específicas com base em um operador de comparação. Por exemplo, em uma planilha de estoque classificada por categorias, pode-se realçar em amarelo os produtos com menos de 10 itens disponíveis. Ou, em uma planilha de resumo de uma rede de lojas, pode-se identificar todas as lojas com lucros maiores do que 10%, volumes de venda menores do que R$ 100.000 e região igual a "Sudeste".

Formatação rápida 1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. 2. Na guia Página Inicial, no grupo Estilo, clique na seta ao lado de Formatação Condicional e clique em Realçar Células: 3. Selecione o comando desejado, como Está Entre, Igual a..., Texto que Contém ou Uma Data que Ocorra. 4. Digite os valores que deseja usar e selecione um formato.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 132

Formatação avançada

1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. 2. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional e, em seguida, clique em Gerenciar Regras.A caixa de diálogo Gerenciador de Regras de Formatação Condicional será exibida.Para adicionar um formato condicional, clique em Nova Regra.A caixa de diálogo Nova Regra de Formatação será exibida.

Para alterar/criar um formato condicional

1. Verifique se a planilha, a tabela ou o relatório de Tabela Dinâmica apropriado está selecionado na caixa de listagem Mostrar regras de formatação para. 2. Selecione a regra e, em seguida, clique em Editar regra. A caixa de diálogo Editar Regra de Formatação será exibida. 4. Em Selecione um Tipo de Regra, clique em Formatar apenas células que contêm.

5. Em Edite a Descrição da Regra, na caixa de listagem Formatar apenas células com, siga um destes procedimentos: 

Formatar por número, data ou hora: Selecione Valor da Célula, selecione um

operador de comparação e, em seguida, digite um número, uma data ou uma hora. Por exemplo, selecione Entre e digite 100 e 200, ou selecione Igual a e digite 01/01/2006. Também é possível digitar um fórmula que retorne um número, uma data ou uma hora. Se Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 133

digitar uma fórmula, comece com um sinal de igual (=). Fórmulas inválidas resultam em nenhuma formatação aplicada. 

Formatar por texto: Selecione Texto Específico, escolha um operador de

comparação e digite o texto.

Por exemplo, selecione Contém e, em seguida, digite

Prateado ou selecione Iniciado com e, em seguida, digite Tri. As aspas são incluídas na sequência de pesquisa e é possível usar caracteres curinga. O comprimento máximo da sequência de caracteres é de 255 caracteres. Também é possível digitar um fórmula que retorne texto. 

Formatar por data: Selecione Datas de Ocorrência e, em seguida, selecione uma

comparação de data. Por exemplo, selecione Ontem ou Próxima semana. 

Formatar células em branco ou sem espaços em branco: Selecione Em Branco ou

Sem Espaços em Branco. Um valor em branco é uma célula que não contém dados e é diferente de uma célula que contém um ou mais espaços (que são texto). 

Formatar células com valores de erro ou sem erros: Selecione Erros ou Sem

Erros. Os valores de erro são #####, #VALOR!, #DIV/0!, #NOME?, #N/D, #REF!, #NÚM! e #NULO!.

Para especificar um formato, clique em Formato. A caixa de diálogo Formatar Células será exibida. Selecione o formato de número, fonte, borda ou preenchimento que deseja aplicar quando o valor da célula satisfizer à condição e, em seguida, clique em OK.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 134

12.6 Formatar apenas valores mais altos ou mais baixos É possível localizar o valor mais alto e o valor mais baixo em um intervalo de células com base em um valor de corte especificado. Por exemplo, pode-se localizar os cinco produtos mais vendidos em um relatório regional, a faixa de produtos 15% inferiores em uma pesquisa com clientes ou os 25 maiores salários em uma análise do departamento de pessoal.

Formatação rápida 1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. 2. Na guia Página Inicial, no grupo Estilo, clique na seta ao lado de Formatação Condicional e clique em Regras de Primeiros/Últimos. 3. Selecione o comando desejado, como 10 Primeiros Itens ou 10% Abaixo. 4. Digite os valores que deseja usar e selecione um formato.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 135

Formatação avançada 1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. 2. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional e clique em Gerenciar Regras. A caixa de diálogo Gerenciador de Regras de Formatação Condicional será exibida. Para adicionar um formato condicional, clique em Nova Regra. A caixa de diálogo Nova Regra de Formatação será exibida.

Para alterar/criar um formato condicional

1. Verifique se a planilha, a tabela ou o relatório de Tabela Dinâmica apropriado está selecionado na caixa de listagem Mostrar regras de formatação para. 2. Selecione a regra e, em seguida, clique em Editar regra.A caixa de diálogo Editar Regra de Formatação será exibida. Em Selecione um Tipo de Regra, clique em Formatar apenas os primeiros ou últimos valores. Em Edite a Descrição da Regra, na caixa de listagem Formatar valores ordenados em, selecione Primeiros ou Últimos.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 136

 Para especificar um número superior ou inferior, digite um número e, em seguida, desmarque a caixa de seleção % do intervalo selecionado. Os valores válidos estão entre 1 e 100.  Para especificar um percentual superior ou inferior, digite um número e, em seguida, marque a caixa de seleção % do intervalo selecionado. Os valores válidos estão entre 1 e 100.  Para especificar um formato, clique em Formato.A caixa de diálogo Formatar Células será exibida.

4. Selecione o formato de número, fonte, borda ou preenchimento que deseja aplicar quando o valor da célula satisfizer à condição e, em seguida, clique em OK.

12.7 Formatar apenas valores acima ou abaixo de uma média Também é possível localizar valores acima ou abaixo de uma média ou de um desvio padrão em um intervalo de células. Por exemplo, é possível localizar os empregados acima da média em uma avaliação de desempenho anual ou os materiais manufaturados situados abaixo de dois desvios padrão em uma classificação de qualidade.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 137

Formatação rápida

1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. 2. Na guia Página Inicial, no grupo Estilo, clique na seta ao lado de Formatação Condicional e clique em Regras de Primeiros/Últimos. 3. Selecione o comando desejado, como Acima da Média ou Abaixo da Média. 4. Digite os valores que deseja usar e selecione um formato.

Formatação avançada

1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. 2. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional e, em seguida, clique em Gerenciar Regras. A caixa de diálogo Gerenciador de Regras de Formatação Condicional será exibida. Para adicionar um formato condicional, clique em Nova Regra.A caixa de diálogo Nova Regra de Formatação será exibida.

Para alterar/criar um formato condicional Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 138

1. Verifique se a planilha, a tabela ou o relatório de Tabela Dinâmica apropriado está selecionado na caixa de listagem Mostrar regras de formatação para. 2. Selecione a regra e, em seguida, clique em Editar regra. A caixa de diálogo Editar Regra de Formatação será exibida. 4. Em Selecione um Tipo de Regra, clique em Formatar apenas valores acima ou abaixo da média.

5. Em Edite a Descrição da Regra, na caixa de listagem Formatar valores, siga um destes procedimentos: 

Para formatar células que estão acima ou abaixo da média para todas as células do

intervalo, selecione um Acima ou Abaixo. 

Para formatar células que estão acima ou abaixo de um, dois ou três desvios padrão

para todas as células do intervalo, selecione um desvio padrão.

Clique em Formato para exibir a caixa de diálogo Formatar Células. Selecione o formato de número, fonte, borda ou preenchimento que deseja aplicar quando o valor da célula satisfizer à condição e, em seguida, clique em OK. É possível escolher mais de um formato.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 139

12.8 Formatar apenas valores únicos ou duplicados Formatação rápida

1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. 2. Na guia Página Inicial, no grupo Estilo, clique na seta ao lado de Formatação Condicional e clique em Realçar Células. 3. Selecione Valores Duplicados. 4. Digite os valores que deseja usar e selecione um formato.

Formatação avançada

1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de Tabela Dinâmica. 2. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional e, em seguida, clique em Gerenciar Regras.A caixa de diálogo Gerenciador de Regras de Formatação Condicional será exibida. Para adicionar um formato condicional, clique em Nova Regra. A caixa de diálogo Nova Regra de Formatação será exibida.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 140

Para alterar/criar um formato condicional

1. Verifique se a tabela ou a planilha apropriada está selecionada na caixa de listagem Mostrar regras de formatação para. 2. Selecione a regra e, em seguida, clique em Editar regra. A caixa de diálogo Editar Regra de Formatação será exibida. 4. Em Selecione um Tipo de Regra, clique em Formatar apenas valores únicos ou duplicados.

5. Em Editar a Descrição da Regra, na caixa de listagem Formatar tudo, selecione exclusivos ou duplicatas. 6. Clique em Formatar para exibir a caixa de diálogo Formatar Células. 7. Selecione o formato de número, fonte, borda ou preenchimento que deseja aplicar quando o valor da célula satisfizer à condição e, em seguida, clique em OK.

12.9 Regras Avançadas Se as necessidades de formatação condicional forem mais complexas, pode-se usar uma fórmula lógica para especificar os critérios de formatação. Por exemplo, comparar valores com um resultado retornado por uma função ou avaliar dados em células fora do intervalo selecionado. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional e, em seguida, clique em Gerenciar Regras. A caixa de diálogo Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 141

Gerenciador de Regras de Formatação Condicional será exibida. Para adicionar um formato condicional, clique em Nova Regra. A caixa de diálogo Nova Regra de Formatação será exibida.

Para alterar/criar um formato condicional

1. Verifique se a planilha, a tabela ou o relatório de Tabela Dinâmica apropriado está selecionado na caixa de listagem Mostrar regras de formatação para. 2. Selecione a regra e, em seguida, clique em Editar regra. A caixa de diálogo Editar Regra de Formatação será exibida. Em Selecione um Tipo de Regra, clique em Usar uma fórmula para determinar quais células devem ser formatadas.

Em Edite a Descrição da Regra, na caixa de listagem Formatar valores nos quais esta fórmula é verdadeira, digite uma fórmula. Deve-se iniciar a fórmula com um sinal de igual (=) e a fórmula deve retornar um valor lógico VERDADEIRO (1) ou FALSO (0).

Exemplo: use um formato condicional com vários critérios e referências de células fora do intervalo de células Na fórmula abaixo, um formato condicional com vários critérios aplicados ao intervalo A1:A5 formata as células em verde se o valor médio para todas as células do intervalo for maior do que o valor na célula C1 e qualquer célula no intervalo tiver um valor Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 142

mínimo maior ou igual ao valor em D1. As célula C1 e D1 estão fora do intervalo de células para o qual o formato condicional é aplicado. A função E combina vários critérios, e as funções MÉDIA e MÍNIMO calculam os valores.

FÓRMULA =E(MÉDIA($A$1:$A$5)>$B$1; MÍNIMOA($A$1:$A$5)>=$C$1)

FORMATO Cor de fonte verde

É possível inserir referências de célula em uma fórmula selecionando as células diretamente em uma planilha. Ao selecionar células na planilha, são inseridas referências de célula absolutas. Se desejar que o Microsoft Office Excel ajuste as referências para cada célula no intervalo selecionado, use referências relativas de célula OBS.: Para todas as formatações condicionais, conforme os valores das células são alterados, sua formatação é automaticamente alterada pelo Excel.No caso de formatação de células usando barras, conforme os valores são alterados em determinada célula, o tamanho da barra nas demais células também irá se alterar, principalmente se forem alterados valores próximos ao ponto central ou ao limite superior (barra mais longa).

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 143

12.10 Localizar células com formatos condicionais Se a planilha tiver uma ou mais células com formato condicional, é possível localizá-las rapidamente para copiar, alterar ou excluir os formatos condicionais. Será possível usar o comando Ir para Especial para localizar apenas células com formato condicional específico ou todas as células com formatos condicionais.

Localizar todas as células com um Formato Condicional 1. Clique em qualquer célula sem um formato condicional. 2. Na guia Página Inicial, no grupo Edição, clique na seta ao lado de Localizar e Selecionar e, em seguida, clique em Formatação Condicional.

Localizar apenas células com o mesmo formato condicional 1. Clique na célula que contém o formato condicional que se deseja localizar. 2. Na guia Página Inicial, no grupo Edição, clique na seta ao lado de Localizar e Selecionar e, em seguida, clique em Ir para Especial. 3. Clique em Formatos condicionais. 4. Clique Validação de dados, Mesmos.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 144

Limpar formatos condicionais  Planilha 1. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional e clique em Limpar Regras. 2. Clique em Planilha Inteira.  Um intervalo de células, uma tabela ou uma tabela dinâmica 1. Selecione o intervalo de células, a tabela ou a tabela dinâmica para que deseja limpar os formatos condicionais. 2. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional e clique em Limpar Regras. 3. Dependendo do que foi selecionado, clique em Células Selecionadas, Esta Tabela ou Esta Tabela Dinâmica.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 145

13 PROTEÇÃO DE PLANILHAS Para impedir que, por acidente ou deliberadamente, um usuário altere, mova ou exclua dados importantes, deve-se proteger determinados elementos de uma planilha ou pasta de trabalho, com ou sem uma senha.

13.1 Proteger elementos de uma planilha Para proteger a planilha

Na guia Revisão, no grupo Alterações, clique em Proteger Planilha.

Na lista Permitir a todos os usuários desta pasta de trabalho, selecione os elementos que deseja que os usuários possam alterar: ITEM

IMPEDE OS USUÁRIOS DE:

Selecionar células

Mover o ponteiro para células cuja caixa de seleção Bloqueado

bloqueadas

esteja marcada na guia Proteção da caixa de diálogo Formatar Células. Por padrão, os usuários podem selecionar células bloqueadas.

Selecionar células

Mover o ponteiro para células cuja caixa de seleção Bloqueado

desbloqueadas

esteja desmarcada na guia Proteção da caixa de diálogo Formatar Células. Por padrão, os usuários podem selecionar células desbloqueadas e podem pressionar a tecla TAB para se mover pelas células desbloqueadas de uma planilha protegida.

Formatar células

Alterar qualquer das opções nas caixas de diálogo Formatar Células ou Formatação Condicional. Se tiver aplicado formatos condicionais antes de proteger a planilha, a formatação continua a se alterar quando um usuário insere um valor que satisfaça outra condição. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO Formatar colunas

Página | 146 Usar qualquer comando de formatação de colunas, inclusive alterar a largura da coluna ou ocultar colunas (guia Início, grupo Células, botão Formato).

Formatar linhas

Usar qualquer comando de formatação de linhas, inclusive alterar a altura da linha ou ocultar linhas (guia Início, grupo Células, botão Formato).

Inserir colunas

Inserir colunas.

Inserir linhas

Inserir linhas.

Inserir hiperlinks

Inserir novos hiperlinks, mesmo em células desbloqueadas.

Excluir colunas

Excluir colunas. OBS.: Caso Excluir colunas esteja protegido e Inserir colunas não esteja também protegido, um usuário pode inserir colunas que não conseguirá excluir.

Excluir linhas

Excluir linhas. OBS.: Caso Excluir linhas esteja protegido e Inserir linhas não esteja também protegido, um usuário pode inserir linhas que não conseguirá excluir.

Classificar

Usar qualquer comando para classificar dados (guia Dados, grupo Classificar e Filtrar). OBS.: Os usuários não podem classificar intervalos que contenham células bloqueadas em uma planilha protegida, independentemente dessa configuração.

Usar AutoFiltro

Usar as setas suspensas para alterar o filtro em intervalos quando o AutoFiltro está aplicado. OBS.: os usuários não podem aplicar nem remover AutoFiltros em uma planilha protegida, independentemente dessa configuração.

Usar relatórios de

Formatar, alterar o layout, atualizar ou, de qualquer forma, modificar

tabela dinâmica

relatórios de tabela dinâmica ou criar novos relatórios.

Editar objetos

Fazer alterações a objetos gráficos - inclusive mapas, gráficos incorporados, formas, caixas de texto e controles - que não tenham sido desbloqueados antes de proteger a planilha. Por exemplo, se Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 147 uma planilha tem um botão que executa uma macro, será possível clicar nesse botão, mas não pode excluí-lo. Fazer qualquer alteração, como formatação, em um gráfico incorporado. O gráfico continua a ser atualizado quando seus dados de origem são alterados. Adicionar ou editar comentários.

Editar cenários

Exibir cenários que estejam ocultos, fazer alterações em cenários cuja alteração tenha sido impedida e excluir esses cenários. Os usuários podem alterar os valores das células variáveis, se não estiverem protegidas, e adicionar novos cenários.

Na caixa Senha para desproteger a planilha, digite uma senha para a planilha, clique em OK e digite novamente a senha para confirmá-la.

Por padrão, todas as células de uma planilha são bloqueadas ao usar o comando proteger planilha.

OBS.: A senha é opcional. Se não fornecer uma senha, qualquer usuário poderá desproteger a planilha e alterar os elementos protegidos. Certifique-se de escolher uma senha que seja fácil de lembrar já que, se ela for perdida, não será possível acessar os elementos protegidos na planilha.

13.2 Ocultar fórmulas Este comando possibilita esconder as fórmulas criadas quando uma planilha é protegida.

1. Selecione a planilha que deseja proteger 2. Na planilha, selecione as células que contêm as fórmulas que deseja ocultar. 3. Na guia Página Inicial, no grupo Células, clique em Formatar e, em seguida, clique em Formatar Células. 4. Na guia Proteção, marque a caixa de seleção Oculta e clique em OK. 5. Na guia Revisão, clique em “Proteger Planilha” (veja item acima). Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 148

13.3 Proteger elementos de uma pasta de trabalho Na guia Revisão, no grupo Alterações, clique em Proteger Pasta de Trabalho.

Em Proteger pasta de trabalho para, siga um destes procedimentos: 

Para proteger a estrutura de uma pasta de trabalho, marque a caixa de seleção

Estrutura. 

Para manter as janelas da pasta de trabalho com o mesmo tamanho e na

mesma posição a cada vez que a pasta de trabalho for aberta, marque a caixa de seleção Janelas.

ITEM Estrutura

IMPEDE OS USUÁRIOS DE 

Exibir planilhas ocultas.



Mover, excluir, ocultar ou alterar os nomes das planilhas.



Inserir novas planilhas ou planilhas de gráfico.



Mover ou copiar planilhas para outra pasta de trabalho.



Em relatórios de tabela dinâmica, exibir os dados de origem de uma célula

da área de dados ou exibir páginas de campo de página em planilhas separadas. 

Em cenários, criar um relatório de resumo do cenário.



Em Ferramentas de Análise, usar as ferramentas de análise que coloquem

resultados em uma nova planilha.

Janelas



Gravar novas macros.



Alterar o tamanho e a posição das janelas da pasta de trabalho quando

esta é aberta. 

Mover, redimensionar ou fechar as janelas.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 149

Para impedir que outros usuários removam a proteção da pasta de trabalho, na caixa Senha (opcional), digite uma senha, clique em OK, e, em seguida, digite novamente a senha para confirmá-la.

13.4 Desproteger a planilha Na guia Revisão, no grupo Alterações, clique em Desproteger Planilha.

OBS.: A opção Proteger Planilha será alterada para Desproteger Planilha quando uma planilha estiver protegida. Se solicitado, digite a senha para desproteger a planilha.

13.5 Para bloquear apenas algumas células da planilha Como dito, o Excel traz como padrão a opção de “Bloqueada” para todas as células de uma planilha. Caso a intenção seja bloquear apenas algumas células, faça o seguinte:

Selecione a planilha inteira clicando no botão Selecionar Tudo.

Na guia Início, no grupo Fonte, clique no iniciador de caixa de diálogo Formatar Fonte da Célula .

Na guia Proteção, limpe a caixa de seleção Bloqueada e, em seguida, clique em OK. Isso desbloqueia todas as células na planilha. Na planilha, selecione somente as células que deseja bloquear. Na guia Início, no grupo Fonte, clique no Iniciador de Caixa de Diálogo

ao lado de Fonte. Na guia Proteção, marque a caixa de seleção Bloqueada Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 150

e, em seguida, clique em OK.

Na guia Revisão, no grupo Alterações, clique em Proteger Planilha.

Na lista Permitir que todos os usuários desta planilha possam:, selecione os elementos que deseja que os usuários possam alterar (como explicado acima). Na caixa Senha para desproteger a planilha, digite uma senha para a planilha, clique em OK e digite novamente a senha para confirmá-la.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 151

14 LOCALIZAÇÃO E SUBSTITUIÇÃO 14.1 Localizar Quando trabalhamos com planilhas, muitas vezes é necessário localizar a célula onde está inserido determinado valor. Isso pode ser trabalhoso no caso de grandes planilhas. Para tanto, é possível utilizar o recurso de Localizar e Substituir. Este comando pode ser acessado na guia Início, Grupo Edição, comando Localizar e Selecionar, ou através do comando CTRL + L. Vejamos o exemplo abaixo: Nesta planilha devemos localizar onde está o nome José. Para isso, basta digitar a palavra desejada na caixa Localizar:

Note que o Excel automaticamente seleciona a célula com o valor. Caso houvesse mais células com esse valor, bastaria clicar no botão Localizar próxima.

14.2 Substituir Na mesma caixa, na Guia “Substituir”, é possível localizar valores e substituir por outros. Suponha que tenha sido digitado a palavra “Contabil” sem acento. Para corrigir, usa-se o comando Substituir: Na caixa localizar, digite a palavra tal como foi escrita (Contabil). Na caixa Substituir por, digite a nova palavra: Contábil. Existem 4 opções: Substituir tudo: o Excel automaticamente realiza a substituição; Substituir: o Excel busca cada célula e efetua a troca, uma por vez; Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 152

Localizar tudo: o Excel indica, na própria caixa, as células que possuem o valor procurado; Localizar próxima: o Excel busca a próxima célula com o valor procurado.

É possível restringir a busca a uma parte específica da planilha. Para isso, selecione somente as células onde estão os valores a serem localizados ou alterados, e repita os mesmos passos para localizar/substituir. No exemplo abaixo, foi selecionada somente a coluna com nomes para retirar todos os acentos da letra E:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 153

15 FUNÇÕES DE BANCO DE DADOS

O Excel possui 2 importantes funções que agilizam consultas e buscas em planilhas que são organizadas como banco de dados. Em verdade, estas funções podem ser utilizadas com sucesso em qualquer tipo de planilha. Trata-se das funções de busca e referência: PROCH e PROCV.

15.1 PROCH Localiza um valor na linha superior de uma tabela e retorna um valor na mesma coluna de uma linha especificada na função. Deve-se usar PROCH quando os valores de comparação estiverem localizados em uma linha ao longo da parte superior de uma tabela de dados e quiser observar um número específico de linhas mais abaixo. O H de PROCH significa "Horizontal".

=PROCH(valor_procurado ; matriz_tabela ; núm_lin ; procurar_intervalo)

Valor_procurado: é o valor a ser localizado na primeira linha da tabela. Valor_procurado pode ser um valor, uma referência ou uma seqüência de caracteres de texto. Matriz_tabela é uma tabela de informações onde os dados devem ser procurados. Use uma referência para um intervalo ou um nome de intervalo.  Os

valores na primeira linha de matriz_tabela podem ser texto, números ou

valores lógicos.  Se

procurar_intervalo for VERDADEIRO, os valores na primeira linha de

matriz_tabela deverão ser colocados em ordem ascendente: ...-2, -1, 0, 1, 2,... , A-Z, FALSO, VERDADEIRO. Caso contrário, PROCH pode não retornar o valor correto. Se procurar_intervalo for FALSO, matriz_tabela não precisará ser ordenada. Textos em maiúsculas e minúsculas são equivalentes.Classifique os valores em ordem crescente, da esquerda para a direita.

Núm_lin: é o número da linha em matriz_tabela de onde o valor correspondente deve ser retirado. Um núm_lin equivalente a 1 retorna o valor da primeira linha na Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 154

matriz_tabela, um núm_lin equivalente a 2 retorna o valor da segunda linha na matriz_tabela, e assim por diante. Se núm_lin for menor do que 1, PROCH retornará o valor de erro #VALOR!. Se núm_lin for maior do que o número de linhas na matriz_tabela, PROCH retornará o valor de erro #REF!.

Procurar_intervalo: é um valor lógico que especifica se PROCH deve localizar uma correspondência exata ou aproximada. Se VERDADEIRO ou omitido, uma correspondência aproximada é retornada. Se uma correspondência exata não for localizada, o valor maior mais próximo que seja menor que o valor_procurado é retornado. Se

FALSO,

PROCH

encontrará

uma

correspondência

exata.

Se

nenhuma

correspondência for localizada, o valor de erro #N/D será retornado.

OBS.: Se PROCH não localizar valor_procurado, e procurar_intervalo for VERDADEIRO, ela usará o maior valor que é menor do que o valor_procurado.

Se o valor_procurado for menor do que o menor valor na primeira linha de matriz_tabela, PROCH retornará o valor de erro #N/D. Se procurar_intervalo for FALSO e valor_procurado for texto, pode-se usar os caracteres curinga ponto de interrogação (?) e asterisco (*) em valor_procurado. Um ponto de interrogação coincide com qualquer caractere único; um asterisco coincide com qualquer seqüência de caracteres. Se quiser localizar um ponto de interrogação ou asterisco real, digite um til (~) antes do caractere. Exemplo:

A

1

B

C

2

Eixos Rolamentos Parafusos

3

4

4

9

4

5

7

10

6

8

11

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 155

Fórmula

Descrição (resultado)

=PROCH("Eixos";A1:C4;2;VERDADEIRO)

Pesquisa Eixos na linha 1 e retorna o valor que está na linha 2 da mesma coluna (4)

=PROCH("Rolamentos";A1:C4;3;FALSO)

Pesquisa Rolamentos na linha 1 e retorna o valor que está na linha 3 da mesma coluna (7)

=PROCH("Parafusos";A1:C4;4)

Pesquisa Parafusos na linha 1 e retorna o valor que está na linha 4 da mesma coluna (11)

15.2 PROCV Localiza um valor na primeira coluna de uma matriz de tabela e retorna um valor na mesma linha, mas de outra coluna na tabela. O V em PROCV significa vertical.

=ROCV (valor_procurado ; matriz_tabela ; núm_coluna ; procurar_intervalo)

Valor_procurado: O valor a ser procurado na primeira coluna da da tabela. O valor_procurado pode ser um valor ou uma referência. Matriz_tabela: São duas ou mais colunas de dados. Use uma referência para um intervalo ou um nome de intervalo. Os valores na primeira coluna de matriz_tabela são os valores procurados por valor_procurado. Os valores podem ser texto, números ou valores lógicos. Textos em maiúsculas e minúsculas são equivalentes. Núm_coluna: O número da coluna em matriz_tabela a partir do qual o valor correspondente deve ser retornado. Um núm_coluna de 1 retornará o valor na primeira coluna em matriz_tabela; um núm_coluna de 2 retornará o valor na segunda coluna em matriz_tabela, e assim por diante. Se núm_coluna for: menor que 1, PROCV retornará o valor de erro #VALOR!. Se for maior do que o número de colunas em matriz_tabela, PROCV retornará o valor de erro #REF!.

Procurar_intervalo: Um valor lógico que especifica se você quer que PROCV localize uma correspondência exata ou aproximada. Se VERDADEIRO ou omitido, uma correspondência exata ou aproximada é retornada. Se uma correspondência exata não for localizada, o valor maior mais próximo que seja menor que o valor_procurado é Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 156

retornado. Os valores na primeira coluna de matriz_tabela deverão ser colocados em ordem ascendente; caso contrário, PROCV poderá não retornar o valor correto.

Se

FALSO, PROCV encontrará somente uma correspondência exata. Nesse caso, os valores na primeira coluna da matriz_tabela não precisam ser classificados. Se houver dois ou mais valores na primeira coluna de matriz_tabela que coincidem com o valor_procurado, o primeiro valor encontrado será utilizado. Se nenhuma correspondência exata for localizada, o valor de erro #N/D será retornado.

OBS.: Ao procurar valores de texto na primeira coluna, certifique-se de que os dados na primeira não tenham espaços à esquerda ou de fim de linha, uso inconsistente de aspas normais ( ' ou " ) e curvas (‘ ou “) ou caracteres não-imprimíveis. Nesses casos, a função PROCV pode fornecer um valor correto ou não esperado. Ao procurar valores de número ou data, certifique-se de que os dados na primeira coluna da matriz_tabela não estejam armazenados como valores de texto. Nesse caso, a função PROCV pode fornecer um valor correto ou não esperado.

Se procurar_intervalo for FALSO e valor_procurado for texto, pode-se usar os caracteres curinga ponto de interrogação (?) e asterisco (*) em valor_procurado. Um ponto de interrogação coincide com qualquer caractere único; um asterisco coincide com qualquer seqüência de caracteres. Se quiser localizar um ponto de interrogação ou asterisco real, digite um til (~) antes do caractere.

Exemplo 1

Este exemplo procura a coluna Densidade da tabela de propriedades atmosféricas para localizar os valores correspondentes nas colunas Viscosidade e Temperatura.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 157 A 1

B

C

Densidade Viscosidade Temperatura

2

0,457

3,55

500

3

0,525

3,25

400

4

0,616

2,93

300

5

0,675

2,75

250

6

0,746

2,57

200

7

0,835

2,38

150

8

0,946

2,17

100

9

1,09

1,95

50

10

1,29

1,71

0

Fórmula =PROCV(1;A2:C10;2)

Descrição (resultado) Usando uma correspondência aproximada, procura o valor 1 na coluna A, localiza o maior valor menos que ou igual a 1 na coluna A, que é 0,946 e, então, retorna o valor da coluna B na mesma linha. (2,17)

=PROCV(1;A2:C10;3;VERDADEIRO)

Usando uma correspondência aproximada, procura o valor 1 na coluna A, localiza o maior valor menos que ou igual a 1 na coluna A, que é 0,946 e, então, retorna o valor da coluna C na mesma linha. (100)

=PROCV(0,7;A2:C10;3;FALSO)

Usando uma correspondência exata, procura o valor

0,7

na

coluna A.

Como

não

há

correspondência exata na coluna A, um erro é retornado. #N/D =PROCV(0,1;A2:C10;2;VERDADEIRO) Usando uma correspondência aproximada, Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 158 procura o valor 0,1 na coluna A. Como 0,1 é menor que o menor valor na coluna A, um erro é retornado. #N/D

=PROCV(2;A2:C10;2;VERDADEIRO)

Usando uma correspondência aproximada, procura o valor 2 na coluna A, localiza o maior valor menos que ou igual a 2 na coluna A, que é 1,29 e, então, retorna o valor da coluna B na mesma linha. (1,71)

Exemplo 2

Este exemplo procura a coluna Item-ID da tabela de produtos infantis e coincide os valores nas colunas Custo e Marcação para calcular os preços e testar as condições. 1

A

B

C

D

2

Item-ID

Item

Custo

Marcação

3

ST-340

Carrinho

R$ 145,67

30%

4

BI-567

Babador

R$ 3,56

40%

5

DI-328

Fraldas

R$ 21,45

35%

6

WI-989

Lenços

R$ 5,12

40%

AS-469

Aspirador

R$ 2,56

45%

Fórmula

Descrição (resultado)

= PROCV("DI-328"; A2:D6; 3;

Calcula

FALSO) * (1 + PROCV("DI-

adicionando a porcentagem de marcação ao

328"; A2:D6; 4; FALSO))

custo. (R$ 28,96)

o

preço

de

varejo

das

fraldas

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 159

Exemplo 3

Este exemplo procura a coluna ID da tabela de funcionários e coincide os valores em outros colunas para calcular idades e testa quanto a condições de erro.

A 1

B

C

D

E

ID Sobrenome

Nome

Cargo

1

Ribeiro

Laura

2

Lemos

Antonio Diretor de vendas

19/2/1952

3

Bento

Paula

Representante de vendas

30/8/1963

4

Martins

Isabel

Representante de vendas

19/9/1958

5

Farinha

Nuno

Gerente de vendas

4/3/1955

6

Ruivo

Pedro

Representante de vendas

2/7/1963

Data de nascimento

2 3 4 5 6 7

Representante de vendas

Fórmula

8/12/1968

Descrição (resultado) Se houver um funcionário com ID de 5, será exibido o sobrenome do funcionário;

caso

contrário,

será

=SE((PROCV(5;A2:E7;2;FALSO)) = "N/D";

exibida a mensagem "Funcionário

"Funcionário não encontrado";

não encontrado". (Farinha)

PROCV(5;A2:E7;2;FALSO))

A função É.NÃO.DISP retorna um valor

VERDADEIRO

quando

a

função PROCV retorna o valor de erro #ND. Se houver um funcionário com ID de =SE(É.NÃO.DISP(PROCV(15;A3:E8;2;FALSO)) = VERDADEIRO; "Funcionário não encontrado"; PROCV(15;A3:E8;2;FALSO))

15, será exibido o sobrenome do funcionário;

caso

contrário,

será

exibida a mensagem "Funcionário não encontrado". (Funcionário não encontrado) Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 160 A função É.NÃO.DISP retorna um valor

VERDADEIRO

quando

a

função PROCV retorna o valor de erro #ND. =PROCV(4;A2:E7;3;FALSO) & " " & PROCV(4;A2:E7;2;FALSO) & " é um " & PROCV(4;A2:E7;4;FALSO) & "."

Para o funcionário com ID de 4, concatena os valores de três células em

uma

frase

completa

(Isabel

Martins é representante de vendas.)

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 161

16 SUBTOTAIS É possível calcular subtotais e totais gerais automaticamente em uma lista para uma coluna, usando o comando Subtotal no grupo Estrutura de Tópicos da guia Dados. Atenção:

não

confundir

SUBTOTAIS

com

a

função

SUBTOTAL.

São

funcionalidades extremamente diferentes. Subtotais são calculados com uma função de resumo, como Soma ou Média, usando a função SUBTOTAL. É possível exibir mais de um tipo de função de resumo para cada coluna. Totais gerais são derivados de dados detalhados e não dos valores nos subtotais. Por exemplo, se usar a função de resumo Média, a linha de total geral exibirá uma média de todas as linhas detalhadas na lista e não uma média dos valores nas linhas de subtotal.

Para criar subtotais

Verifique se cada coluna tem um rótulo na primeira linha, se contém informações similares em cada coluna e se o intervalo não tem linhas ou colunas vazias. Selecione uma célula no intervalo.

Para inserir um nível de subtotais

Pode-se inserir um nível de subtotais para um grupo de dados, por exemplo, a cada alteração na coluna Mês, calcule o subtotal da coluna Quantidade. Para isso, classifique a coluna que forma o grupo. Na guia Dados, no grupo Tópicos, clique em Subtotal. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 162

Na caixa A cada alteração em, clique na coluna para calcular o subtotal. No exemplo acima: Mês. Na caixa Usar função, clique na função de resumo que deseja usar para calcular os subtotais. No exemplo acima, Soma. Na caixa Adicionar subtotal a, marque a caixa de seleção para cada coluna que contém valores que se deseja subtotalizar. No exemplo acima, Quantidade. Para inserir uma quebra de página automática depois de cada subtotal, marque a caixa de seleção Quebra de página entre grupos. Para especificar uma linha de resumo acima da linha de detalhes (que contém cada item), desmarque a caixa de seleção Resumir abaixo dos dados. Para especificar uma linha de resumo abaixo da linha de detalhes (que contém cada item), marque a caixa de seleção Resumir abaixo dos dados. No exemplo acima, foi marcada esta caixa de seleção. Opcionalmente, pode-se utilizar o comando Subtotais novamente repetindo as etapas acima para adicionar mais subtotais com diferentes funções de resumo. Para evitar sobrescrever os subtotais existentes, a cada nota etapa, desmarque a caixa de seleção Substituir subtotais atuais.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 163

Inserir níveis aninhados de subtotais

É possível inserir subtotais para grupos internos e aninhados dentro de seus grupos externos correspondentes, como mostra o exemplo a seguir. Na guia Dados, no grupo Tópicos, clique em Subtotal.

Na caixa A cada alteração em, clique na coluna dos subtotais externos. No exemplo acima: Funcionário. Na caixa Usar função, clique na função de resumo que deseja usar para calcular os subtotais. No exemplo acima, Soma. Na caixa Adicionar subtotal a, marque a caixa de seleção para cada coluna que contém valores que você deseja subtotalizar. No exemplo acima, Quantidade. Marque a caixa de seleção Resumir abaixo dos dados. Você pode usar o comando Subtotais novamente repetindo as etapas de um a seis para adicionar mais subtotais com diferentes funções de resumo. Para evitar sobrescrever os subtotais existentes, sempre desmarque a caixa de seleção Substituir subtotais atuais.

Para inserir subtotais aninhados

Na guia Dados, no grupo Tópicos, clique em Subtotal. Na caixa A cada alteração em, clique na coluna de subtotal aninhado. No exemplo acima, Produto. Na caixa Usar função, clique na função de resumo que deseja usar para calcular os subtotais. No exemplo acima, Soma. Desmarque a caixa de seleção Substituir subtotais atuais. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 164

Repita as etapas anteriores para mais subtotais aninhados, trabalhando a partir dos subtotais mais externos.

Dica: Para exibir um resumo apenas dos subtotais e dos totais gerais, clique nos símbolos de estrutura de tópicos

ao lado dos números de linha. Use os símbolos

e

para exibir ou ocultar as linhas de detalhes para subtotais individuais. Ao criar subtotais, automaticamente o Excel cria uma 17 ESTRUTURAS DE

TÓPICOS para a planilha.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 165

17 ESTRUTURAS DE TÓPICOS Se a planilha contiver uma lista de dados e for preciso agrupá-la e resumi-la, será possível criar uma estrutura de tópicos de até oito níveis, um para cada grupo. Cada nível interno, representado por um número mais alto nos símbolos da estrutura de tópicos exibe dados de detalhe do nível mais externo precedente, representado por um número mais baixo nos símbolos da estrutura de tópicos. Uma estrutura de tópicos é usada para exibir rapidamente linhas ou colunas de resumo ou para revelar dados de detalhe para cada grupo. Pode-se criar uma estrutura de tópicos de linhas (conforme mostrado no exemplo abaixo), uma estrutura de tópicos de colunas ou uma estrutura de tópicos tanto de linhas como de colunas.

Para exibir linhas para um nível, clique nos

Uma linha de estrutura de tópicos de dados

símbolos de estrutura de

tópicos apropriados.

de vendas agrupada por regiões geométricas e meses com várias linhas de resumo e detalhes exibidas.

O Nível 1 contém o total de vendas para todas as linhas de detalhes.

O Nível 2 contém o total de vendas para cada mês em cada região.

O Nível 3 contém linhas de detalhes (apenas linhas de detalhes 11 a 13 são visíveis no momento).

Para expandir ou recolher dados em sua estrutura de tópicos, clique nos símbolos de estrutura de tópicos

e

.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 166

Criando uma estrutura de tópicos de linhas

Verifique se cada coluna tem um rótulo na primeira linha, contém informações similares em cada coluna e se o intervalo não tem linhas ou colunas vazias. Selecione uma célula no intervalo. Classifique as colunas que formam os grupos. No exemplo abaixo, foram classificadas primeiro as colunas por produto e depois por marca:

Também foi inserida uma coluna que calcula a soma dos produtos nos meses. Insira linhas de resumo usando o recurso Subtotal (vide item anterior). Especifique se o local da linha de resumo está abaixo ou acima das linhas de detalhes: Na guia Dados, no grupo Tópicos, clique no Iniciador de Caixa de Diálogo Tópicos. Para especificar uma linha de resumo acima da linha de detalhes, desmarque a caixa de seleção Linhas de resumo abaixo do detalhe. Para especificar uma linha de resumo abaixo da linha de detalhes, marque a caixa de seleção Linhas de resumo abaixo do detalhe. Para organizar os dados em tópicos automaticamente, selecione uma célula no intervalo. Na guia Dados, no grupo Tópicos, clique na seta ao lado de Agrupar e, em seguida, clique em AutoTópicos.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 167

Organizar os dados em tópicos manualmente

Selecione todas as linhas de resumo subordinadas, bem como seus dados de detalhe relacionados. Na guia Dados, no grupo Tópicos, clique em Agrupar.

Para criar mais grupos internos (aninhados), selecione as linhas de detalhes adjacentes à linha que contém a linha de resumo .Na guia Dados, no grupo Tópicos, clique em Agrupar. Continue selecionando e agrupando linhas internas até ter criado todos os níveis que você deseja na estrutura de tópicos. Se desejar desagrupar linhas, selecione as linhas e, em seguida, na guia Dados, no grupo Estrutura de Tópicos, clique em Desagrupar.

Criar uma estrutura de tópicos de colunas

Verifique se cada linha tem um rótulo na primeira coluna, contém informações similares em cada linha e se o intervalo não tem linhas ou colunas vazias. Selecione uma célula no intervalo. Classifique as linhas que formam os grupos. Insira colunas de resumo com fórmulas imediatamente à direita ou à esquerda de cada grupo de colunas de detalhes. Especifique se o local da coluna de resumo está à direita ou à esquerda das colunas de detalhes: Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 168

Na guia Dados, no grupo Tópicos, clique no Iniciador de Caixa de Diálogo Tópicos. Para especificar uma coluna de resumo à esquerda da coluna de detalhes, desmarque a caixa de seleção Colunas de resumo à direita do detalhe. Para especificar uma coluna de resumo à direita da coluna de detalhes, marque a caixa de seleção Colunas de resumo à direita do detalhe.

 Para Organizar os dados em tópicos automaticamente: Selecione uma célula no intervalo. Na guia Dados,no grupo Tópicos, clique na seta ao lado de Agrupar e, em seguida, clique em AutoTópicos.  Organizar os dados em tópicos manualmente Selecione todas as colunas de resumo subordinadas, bem como seus dados de detalhe relacionados. Na guia Dados, no grupo Tópicos, clique em Agrupar.

Para criar um grupo interno aninhado, selecione as colunas de detalhes adjacentes à coluna que contém a coluna de resumo. Na guia Dados, no grupo Tópicos, clique em Agrupar. Continue selecionando e agrupando colunas internas até ter criado todos os níveis que você deseja na estrutura de tópicos.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 169

Se desejar desagrupar colunas, selecione as colunas e, em seguida, na guia Dados, no grupo Estrutura de Tópicos, clique em Desagrupar.. Também é possível desagrupar seções de uma estrutura de tópicos sem remover a estrutura de tópicos inteira. Mantenha a tecla SHIFT pressionada enquanto clica em

ou

do grupo e, em

seguida, na guia Dados, no grupo Estrutura de Tópicos clique em Desagrupar.

Exibir ou ocultar dados em uma estrutura de tópicos

Exibir ou ocultar os dados de detalhe de um grupo 

Para exibir os dados de detalhes dentro de um grupo, clique na



Para ocultar os dados de detalhe de um grupo, clique na

do grupo.

do grupo.

Expandir ou recolher a estrutura de tópicos inteira para um determinado nível Nos símbolos de estrutura de tópicos

, clique no número do nível desejado. Os

dados de detalhe dos níveis inferiores serão, então, ocultados. Por exemplo, se uma estrutura de tópicos tiver quatro níveis, pode-se ocultar o quarto nível enquanto exibe o restante dos níveis clicando em

.

Exibir ou ocultar todos os dados de detalhe organizados em tópicos  Para

exibir todos os dados de detalhe, clique no nível mais baixo nos símbolos de

estrutura de tópicos  Para

. Por exemplo, caso existam três níveis, clique em

ocultar todos os dados de detalhe, clique em

.

.

Personalizar uma estrutura de tópicos com estilos

O Microsoft Office Excel permite aplicar formatos de negrito, itálico e outros formatos de texto para diferenciar as linhas ou colunas de resumo nos dados. Alterando a maneira como cada um desses estilos é definido, pode-se aplicar diferentes formatos de texto e célula para personalizar a aparência da estrutura de tópicos. Ainda é possível aplicar um estilo a uma estrutura de tópicos tanto ao criar estrutura de tópicos como depois de criá-la.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 170

 Aplicar automaticamente um estilo a uma linha ou coluna de resumo Na guia Dados, no grupo Tópicos, clique no Iniciador de Caixa de Diálogo Tópicos. Marque a caixa de seleção Estilos automáticos.

 Aplicar um estilo a uma linha ou coluna de resumo existente Selecione as células às quais deseja aplicar estilos de estrutura de tópicos. Na guia Dados, no grupo Tópicos, clique no Iniciador de Caixa de Diálogo Tópicos.

Marque a caixa de seleção Estilos automáticos. Clique em Aplicar estilos.

Copiar dados de estrutura de tópicos

Use os símbolos de estrutura de tópicos

,

e

para ocultar os dados de

detalhe que não deseja que sejam copiados. Selecione o intervalo de linhas de resumo. Na guia Página Inicial, no grupo Edição, clique em Localizar e Selecionar e, em seguida, clique em Ir para.

Clique em Ir para Especial. Clique em Somente Células Visíveis. Clique em OK e, em seguida, copie os dados.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 171

Remover uma estrutura de tópicos

Clique na planilha. Na guia Dados, no grupo Tópicos, clique na seta ao lado de Desagrupar e, em seguida, clique em Limpar estrutura de tópicos.

Se as linhas ou colunas ainda estiverem ocultas, arraste até os títulos visíveis de linha ou coluna em ambos os lados das linhas e colunas ocultas, aponte para Ocultar & Reexibir no comando de grupo Formatar, no grupo Células da guia Início e, em seguida, clique em Reexibir Linhas ou Reexibir Colunas.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 172

Criar um relatório de resumo com um gráfico

Para criar um relatório de resumo de dados que apenas exibe totais acompanhados de um gráfico desses totais, siga um destes procedimentos:  Criar um relatório de resumo Oculte os detalhes clicando nos símbolos de estrutura de tópicos

,

e

para

mostrar apenas os totais conforme exemplo:

 Criar um gráfico do relatório de resumo Selecione os dados de resumo que deseja representar graficamente. Crie o gráfico. Por exemplo, se o gráfico for criado usando o Assistente de Gráfico, ele terá a aparência do exemplo a seguir:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 173

18 TABELA DINÂMICA Um relatório de tabela dinâmica permite resumir, analisar, explorar e apresentar dados de resumo. É um meio interativo de resumir rapidamente grandes quantidades de dados. Use um relatório de tabela dinâmica para analisar detalhadamente dados numéricos e responder perguntas inesperadas sobre seus dados. Um relatório de tabela dinâmica são projetados especialmente para: 

Consultar grandes quantidades de dados de várias maneiras amigáveis.



Subtotalizar e agregar dados numéricos, resumir dados por categorias e

subcategorias, bem como criar cálculos e fórmulas personalizados. 

Expandir e recolher níveis de dados para enfocar os resultados e fazer uma

busca detalhada dos dados de resumo das áreas de seu interesse. 

Mover linhas para colunas ou colunas para linhas (ou "dinamizar") para ver

resumos diferentes dos dados de origem. 

Filtrar, classificar, agrupar e formatar condicionalmente o subconjunto de dados

mais útil e interessante para permitir analisar somente as informações desejadas. 

Apresentar relatórios online ou impressos, concisos, atraentes e úteis.

Geralmente, um relatório de tabela dinâmica é utilizado quando deseja-se analisar totais relacionados, especialmente quando tiver uma longa lista de valores a serem somados e deseja-se comparar vários fatos sobre cada valor. No relatório de tabela dinâmica ilustrado a seguir, é possível ver com facilidade como as vendas de golfe do terceiro trimestre na célula F3 se comparam com as vendas de outro esporte, ou trimestre, ou com o total de vendas.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 174 Os dados de origem, neste caso, a partir de uma planilha.

Os valores de origem para o resumo de Golfe do 3º trimestre no relatório de tabela dinâmica

O relatório de tabela dinâmica completo

O resumo dos valores de origem em C2 e C8 a partir dos dados de origem

Em um relatório de tabela dinâmica, cada coluna ou campo nos dados de origem se torna um campo de tabela dinâmica que resume várias linhas de informações. No exemplo anterior, a coluna Esporte se transforma no campo Esporte e cada registro de Golfe é resumido em um único item Golfe.

18.1 Criar ou excluir um relatório de tabela dinâmica ou de gráfico dinâmico Para criar um relatório de tabela dinâmica ou de gráfico dinâmico, deve-se conectar à fonte de dados e inserir o local do relatório. Selecione uma célula em um intervalo de células ou coloque o ponto de inserção dentro da tabela do Microsoft Office Excel. Certifique-se de que o intervalo de células tenha títulos de coluna.

1 - Selecione o tipo de relatório a ser gerado 

Para criar um relatório de Tabela Dinâmica, na guia Inserir, no grupo Tabelas,

clique em Tabela Dinâmica e em Tabela Dinâmica. O Excel exibe a caixa de diálogo Criar Tabela Dinâmica. 

Para criar um relatório de tabela dinâmica e de gráfico dinâmico, na guia Inserir,

no grupo Tabelas, clique em Tabela Dinâmica e, em seguida, clique em Gráfico Dinâmico. O Excel exibe a caixa de diálogo Criar Tabela Dinâmica com Gráfico Dinâmico.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 175

2 - Selecione uma fonte de dados  Selecionar os dados que deseja analisar Clique em Selecionar uma tabela ou um intervalo. Digite o intervalo de células ou a referência do nome da tabela, na caixa Tabela/Intervalo. Se tiver selecionado uma célula em um intervalo de células ou se o ponto de inserção estava em uma tabela antes de ter iniciado o assistente, o Excel exibirá o intervalo de células ou a referência do nome da tabelas na caixa Tabela/Intervalo. Como alternativa, para selecionar um intervalo de células ou uma tabela, clique em Recolher Caixa de Diálogo

para ocultar temporariamente a caixa de diálogo.

Selecione o intervalo na planilha e pressione Expandir Caixa de Diálogo

.

OBS.: se o intervalo estiver em outra planilha na mesma pasta de trabalho ou em outra pasta de trabalho, digite o nome da pasta de trabalho e da planilha usando a seguinte sintaxe: ([nomedapastadetrabalho]nomedaplanilha!intervalo).  Utilizar dados externos Clique em Usar uma fonte de dados externa. Clique em Escolher Conexão. O Excel exibe a caixa de diálogo Conexões Existentes. Na caixa de listagem suspensa Mostrar na parte superior da caixa de diálogo, selecione a categoria de conexões para a qual deseja escolher uma conexão ou selecione Todas as Conexões Existentes (que é o padrão). Selecione uma conexão a partir da caixa de listagem Selecionar uma Conexão e clique em Abrir. Ao escolher uma conexão da categoria Conexões nesta Pasta de Trabalho, estará sendo reutilizada ou compartilhando uma conexão existente. Se escolher uma conexão a partir das categorias Arquivos de conexão na rede ou Arquivos de conexão neste computador, o Excel copiará o arquivo de conexão na pasta de trabalho como uma nova conexão de pasta de trabalho e usará esse arquivo como a nova conexão para o relatório de Tabela Dinâmica.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 176

3 - Especifique um local 

Para colocar o relatório de Tabela Dinâmica em uma nova planilha começando

na célula A1, clique em Nova Planilha. 

Para colocar o relatório de tabela dinâmica em um planilha existente, selecione

Planilha Existente e especifique a primeira célula no intervalo de células onde deseja posicionar o relatório de Tabela Dinâmica. Como alternativa, clique em Recolher Caixa de Diálogo

para ocultar

temporariamente a caixa de diálogo, selecione a célula inicial na planilha e pressione Expandir Caixa de Diálogo

. Clique então em OK.

O Excel adiciona um relatório de Tabela Dinâmica vazio ao local especificado e exibe a Lista de Campos da Tabela Dinâmica para que sejam adicionados campos, criar um layout e personalizar o relatório de Tabela Dinâmica.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 177

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 178

4- Arraste os campos desejados para a Tabela Dinâmica

Ao arrastar os campos para a tabela, é possível visualizar os resultados totais dos itens. Em Rótulos de Linhas, são exibidas as categorias de sua planilha (linhas). Em Rótulos de Coluna, são apresentados os valores totais de cada categoria, de acordo com as colunas selecionadas. É possível filtrar os dados a serem apresentados usando Filtros de relatório. Também é possível, dentro dos Rótulos de Linha, apresentar sub-categorias. Para isso, basta arrastar as categorias desejadas para Rótulos de Coluna. Para alternar a ordem como as categorias são exibidas, basta alterar sua ordem no campo “Rótulos de Coluna”.

18.2 Trabalhando com um relatório de tabela dinâmica Depois de criar o relatório de tabela dinâmica inicial, é possível executar as seguintes tarefas enquanto trabalha com um relatório de tabela dinâmica:

Explorar os dados seguindo o procedimento abaixo: 

Expanda e recolha os dados, e mostre os detalhes subjacentes que

pertencem aos valores. 

Classifique, filtre e agrupe campos e itens.



Altere as funções de resumo e adicione os cálculos e fórmulas

personalizados.

Alterar o layout do formulário e a organização dos campos seguindo o procedimento abaixo: 

Altere o formulário do relatório de tabela dinâmica: compacto, estrutura de

tópicos ou tabular. 

Adicione, reorganize e remova campos.



Altere a ordem dos campos ou itens.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 179

Alterar o layout de colunas, linhas ou subtotais seguindo o procedimento abaixo: 

Ative ou desative os cabeçalhos de campo de coluna e linha, ou exiba ou

oculte linhas em branco. 

Exiba os subtotais acima ou abaixo das respectivas linhas.



Ajuste as larguras de coluna na atualização.



Mova um campo de coluna para a área de linha ou um campo de linha

para a área da coluna. 

Mescle ou desfaça a mesclagem de células de itens de coluna e linha

externas.

Alterar a exibição de espaços em branco e erros seguindo o procedimento abaixo: 

Alterar o modo como os erros e as células vazias são exibidos.



Altere o modo como os itens e os rótulos sem dados são mostrados.



Exiba ou oculte linhas em branco.

Alterar a formatação seguindo o procedimento abaixo: 

Formate manual e condicionalmente células e intervalos.



Altere o estilo geral do formato da tabela dinâmica.



Altere o formato de número dos campos.

18.3 Gráfico dinâmico Um relatório de Gráfico Dinâmico oferece uma representação gráfica dos dados em um relatório de Tabela Dinâmica que, neste caso, é chamado de relatório de Tabela Dinâmica associado. Quando se altera o layout de uma tabela dinâmica, o gráfico também é alterado. Um relatório de Gráfico Dinâmico é interativo. Ao criar um relatório de Gráfico Dinâmico, o Painel Filtro de Gráfico Dinâmico é exibido. É possível usá-lo para classificar e filtrar os dados subjacentes do relatório de Gráfico Dinâmico. As alterações feitas no layout e nos dados do campo no relatório de Tabela Dinâmica associado refletem-se imediatamente no layout nos dados do Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 180

relatório de Gráfico Dinâmico. Um relatório de gráfico dinâmico exibe séries, categorias e marcadores de dados:

série de dados: pontos de dados relacionados plotados em um gráfico e provenientes de linhas ou colunas da folha de dados. Cada série de dados tem uma cor ou um padrão exclusivo. É possível plotar uma ou mais séries de dados em um gráfico. Os gráficos de pizza têm somente uma série de dados; marcadores: uma barra, uma área, um ponto, uma fatia ou outro símbolo em um gráfico que representa um único ponto de dados ou valor que se origina em uma célula de planilha. Os marcadores de dados relacionados em um gráfico constituem uma série de dados e eixos, exatamente como um gráfico padrão. Também pode-se alterar o tipo de gráfico e outras opções como os títulos, o posicionamento da legenda, os rótulos de dados, o local do gráfico etc.

Criar um relatório de gráfico

Clique no relatório de tabela dinâmica. Na guia Inserir, no grupo Gráficos, clique em um tipo de gráfico.

Pode-se usar qualquer tipo de gráfico exceto um gráfico de dispersão (xy), de bolha ou de ações. Um relatório de Gráfico Dinâmico e seu relatório de Tabela Dinâmica associado devem sempre estar na mesma pasta de trabalho. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 181

Converter um relatório de Gráfico Dinâmico em gráfico padrão

Este procedimento exclui a Tabela Dinâmica, permanecendo somente o gráfico. Localize o relatório de tabela dinâmica que tenha o mesmo nome do relatório de gráfico dinâmico. Para isso, clique no relatório de Gráfico Dinâmico. Um clique no relatório de Gráfico Dinâmico exibe as Ferramentas de Gráfico Dinâmico, adicionando a guia Design, Layout, Formate e Analisar. Para localizar o nome do relatório de Tabela Dinâmica associado, na guia Design, no grupo Dados, clique em Selecionar Dados para exibir a caixa de diálogo Editar Fonte de Dados e observe o nome do relatório de Tabela Dinâmica associado, que é o texto após o ponto de exclamação (!), na caixa de texto Intervalo de dados do gráfico e clique em OK. Para localizar o relatório de Tabela Dinâmica associado, clique em cada relatório de Tabela Dinâmica na pasta de trabalho e, na guia Opções, no grupo Tabela Dinâmica, clique em Opções até localizar o mesmo nome na caixa de texto Nome. 1. Clique em OK. 2. Na guia Opções, no grupo Ações, clique em Selecionar e em Tabela Dinâmica Inteira. 3. Pressione DELETE.

Criar um gráfico padrão a partir de algum ou todos os dados de um relatório de Tabela Dinâmica

Selecione os dados no relatório de tabela dinâmica que deseja usar no gráfico. Para incluir botões de campo e dados na primeira linha e coluna do relatório, inicie arrastando do canto inferior direito dos dados que estiver selecionando. Na guia Página Inicial, no grupo Área de Transferência, clique em Copiar

. Clique em uma célula em branco fora do relatório de tabela dinâmica. Na

guia Início, no grupo Área de Transferência, clique na seta próxima a Colar e clique em Colar Especial. Clique em Valores e em OK. Na guia Inserir, no grupo Gráficos, clique em um tipo de gráfico. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 182

Excluir um relatório de tabela dinâmica ou gráfico dinâmico

Excluir um relatório de tabela dinâmica Clique no relatório de tabela dinâmica. Na guia Opções, no grupo Ações, clique em Selecionar e em Tabela Dinâmica Inteira.Pressione DELETE.

OBS.: Excluir o relatório de tabela dinâmica associado de um relatório de gráfico dinâmico cria um gráfico padrão que não pode mais ser alterado.

Excluir um relatório de gráfico dinâmico Selecionar o relatório de gráfico dinâmico. Pressione DELETE. Excluir o relatório de gráfico dinâmico não excluir automaticamente o relatório associado de tabela dinâmica

Painel Filtro de Gráfico Dinâmico

Use o Painel Filtro de Gráfico Dinâmico para classificar e filtrar os dados subjacentes e alterar a aparência do relatório de Gráfico Dinâmico.

Caixa Campos de eixos (Categorias): Exibe itens do rótulo Linha do relatório de Tabela Dinâmica associado que formam categorias individuais para as quais pontos de dados são incluídos no gráfico. As categorias aparecem no eixo horizontal (categoria), também chamado de eixo x, do relatório de Gráfico Dinâmico. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 183

Caixa Campos de Legenda (Série) Exibe itens do rótulo Coluna do relatório de Tabela Dinâmica associado que formam as séries de dados individuais. Os nomes das séries aparecem na legenda do relatório de Gráfico Dinâmico.

Caixa Filtro de Relatório: Se o relatório de Tabela Dinâmica associado tiver um ou mais filtros de relatório, pode-se usar a caixa Filtro de Relatório como uma maneira conveniente de resumir e colocar o foco rapidamente em um subconjunto de dados sem modificar as informações de série e categoria. Por exemplo, é possível clicar em Todos em um Filtro de Relatório Anual para mostrar as vendas referentes a todos os anos e, em seguida, colocar o foco em anos específicos clicando em um ano de cada vez. Cada página de filtro de relatório do gráfico tem o mesmo layout de categorias e séries para anos diferentes. Assim, os dados de cada ano podem ser facilmente comparados. Além disso, a recuperação de uma página de filtro de relatório de cada vez pode poupar memória para uma grande fonte de dados externa.

Ocultar ou mostrar a Lista de Campos da Tabela Dinâmica: ativar/desative o botão Lista de Campos na parte superior do Painel Filtro de Gráfico Dinâmico para ver uma área maior dos relatórios de Gráfico Dinâmico e Tabela Dinâmica ou exibir novamente a Lista de Campos para reorganizar o layout da Tabela Dinâmica e alterar a aparência do Gráfico Dinâmico.

Selecionar dados de origem diferentes para um relatório de Tabela dinâmica

Clique no relatório de tabela dinâmica. Na guia Opções, no grupo Dados, clique em Alterar Fonte de Dados e, em seguida, clique em Alterar Fonte de Dados. A caixa de diálogo Alterar Fonte de Dados da Tabela Dinâmica é exibida. Siga um destes procedimentos: Para usar uma tabela ou intervalo de célula diferente do Microsoft Office Excel, clique em Selecionar uma tabela ou intervalo e, em seguida, digite a primeira célula na caixa de texto Tabela/Intervalo. Como alternativa, clique em Recolher Caixa de Diálogo

para ocultar

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 184

temporariamente a caixa de diálogo. Selecione a célula inicial na planilha e pressione Expandir Caixa de Diálogo

.

Para usar uma conexão diferente, selecione Usar uma fonte de dados externa e, em seguida, clique em Escolher Conexão. A caixa de diálogo Conexões Existentes é exibida. Na lista suspensa Mostrar na parte superior da caixa de diálogo, selecione a categoria de conexões para a qual deseja escolher uma conexão ou selecione Todas as Conexões Existentes (que é o padrão). Selecione uma conexão a partir da caixa de listagem Selecionar uma Conexão e, em seguida, clique em Abrir. Clique então em OK.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 185

19 FORMULÁRIO DE DADOS Quando uma linha de dados é muito ampla e requer movimentos repetidos de rolagem horizontal, considere a possibilidade de usar um formulário de dados para adicionar, editar, localizar e excluir linhas. Um formulário de dados fornece uma maneira conveniente de inserir ou exibir uma linha completa de informações em um intervalo ou tabela sem rolar horizontalmente. Usar um formulário de dados pode tornar o processo de entrada dos dados muito mais fácil do que passar de uma coluna de dados para a outra nos casos em que a quantidade delas é superior ao limite que pode ser visualizado na tela. Use um formulário de dados quando um formulário simples de caixas de texto listando os títulos de colunas como rótulos for suficiente, e não precisar de recursos sofisticados ou personalizados, como uma caixa de listagem ou um botão de rotação.

O Microsoft Office Excel pode gerar automaticamente um formulário de dados embutido para o intervalo ou sua tabela. Esse formulário exibe todos os cabeçalhos de coluna como rótulos em uma única caixa de diálogo, e cada rótulo apresenta uma caixa de texto em branco adjacente na qual é possível inserir dados para cada coluna, até um máximo de 32 colunas. Em um formulário de dados, é possível inserir novas linhas, localizar novas linhas navegando ou (com base no conteúdo da célula) atualizar e excluir linhas. Se uma célula contiver uma fórmula, o resultado desta será exibido no formulário de dados, sem ser possível alterar essa fórmula. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 186

OBS.: Não é possível imprimir um formulário de dados. Além disso, como esse tipo de formulário é uma caixa de diálogo modal, somente depois de fechá-lo é que se pode usar o comando Imprimir ou o botão Imprimir do Excel.

Criar um formulário de dados

Se necessário, adicione um cabeçalho de coluna a cada coluna no intervalo ou tabela. O Excel usa esses cabeçalhos de modo a criar rótulos para cada campo do formulário. Clique em uma célula no intervalo ou tabela ao qual deseja adicionar o formulário. Clique na seta ao lado de Barra de Ferramentas de Acesso Rápido e clique em Mais Comandos. Na caixa Escolher comandos em, clique em Todos os Comandos e selecione o botão Formulário

na lista.

Clique em Adicionar e em OK. Na Barra de Ferramentas de Acesso Rápido, clique em Formulário

.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 187

USANDO UM FORMULÁRIO DE DADOS

Adicionar uma nova linha de dados

No formulário de dados, clique em Novo. Digite os dados da nova linha. Quando terminar de digitar dados, pressione ENTER para adicionar a linha ao final do intervalo ou tabela. Antes de pressionar ENTER, é possível desfazer qualquer alteração clicando em Restaurar. Todos os dados que tiverem sido digitados nos campos serão descartados.

Localizar uma linha navegando

Para mover pelas linhas, uma de cada vez, use as setas da barra de rolagem no formulário de dados. Para mover 10 linhas de uma vez, clique na área da barra de rolagem entre as setas. Para avançar até a linha seguinte no intervalo ou tabela, clique em Localizar Próxima. Para retornar à linha anterior no intervalo ou tabela, clique em Localizar Anterior.

Localizar uma linha inserindo critérios de pesquisa

Clique em Critérios e insira os critérios de comparação no formulário de dados. Todos os itens que começarem com os critérios de comparação serão filtrados. Por exemplo, ao digitar o texto Dav como critério, o Excel localizará "Davidovski" e "Davis". Para encontrar valores de texto que compartilham alguns caracteres, mas não outros, use um caractere curinga como critério:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 188

USE ? (ponto de interrogação)

PARA LOCALIZAR Qualquer caractere único Por exemplo, sm?th localiza "smith" e "smyth"

* (asterisco)

Qualquer número de caracteres Por exemplo, *este localiza "Nordeste" e "Sudeste"

~ (til) seguido de ?, *, ou ~

Um ponto de interrogação, asterisco ou til Por exemplo, fy91~? localiza "fy91?"

Para localizar linhas que correspondem aos critérios, clique em Localizar Próxima ou Localizar Anterior. Para voltar ao formulário de dados de forma a adicionar, alterar ou excluir, clique em Formulário.

Alterar dados em uma linha

Localize a linha que deseja alterar. Altere os dados na linha. Quando terminar de alterar os dados, pressione ENTER para atualizar a linha. O Excel passa automaticamente para a próxima linha.

Excluir uma linha

No formulário de dados, localize a linha que deseja excluir. Clique em Excluir. O Excel solicita a confirmação da operação. Depois de confirmada, não é possível desfazer a exclusão de uma linha.

Fechar um formulário de dados

Para fechar o formulário de dados e retornar à planilha, clique em Fechar.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 189

20 CONTROLES DE FORMULÁRIOS

Os controles de formulário são botões, caixas de seleção, barras de rolagem, dentre outros, que visam auxiliar os usuários a entrar com dados nas planilhas. Use estes controles para dar mais dinamismo e facilidade para trabalhar com as planilhas. Para ativar os controle de formulário, ative a Guia Desenvolvedor no Excel. Para isso: 1. Clique no Botão Microsoft Office

e, em seguida, clique em Opções

do Excel. 2. Na categoria Mais usados, em Opções principais para o trabalho com o Excel, marque a caixa de seleção Mostrar guia Desenvolvedor na Faixa de Opções e clique em OK.

20.1 Caixa de seleção Ativa ou desativa um valor que indica uma opção. Pode-se marcar mais de uma caixa de seleção por vez em uma planilha ou em uma caixa de grupo. Por exemplo, é possível usar uma caixa de seleção para criar um formulário de pedido que contenha uma lista de itens disponíveis ou em um aplicativo de controle de estoque para mostrar se um item foi suspenso.

1. Na guia Desenvolvedor, no grupo Controles, clique em Inserir e, em Controles de Formulário, clique em Caixa de seleção

.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 190

2. Clique no local da planilha em que deseja exibir o canto superior esquerdo do controle. 3. Na guia Desenvolvedor, no grupo Controles, clique em Propriedades .

Para especificar as propriedades do controle

Em Valor, especifique o estado inicial da caixa de seleção, seguindo um destes procedimentos: 

Para exibir uma caixa de seleção preenchida com uma marca, clique em

Marcado. Uma marca indica que a caixa de seleção está marcada. 

Para exibir uma caixa de seleção desmarcada, clique em Desmarcado.



Para exibir uma caixa de seleção preenchida com sombreamento, clique

em Misto. O sombreamento indica uma combinação dos estados marcado e desmarcado; por exemplo, no caso de uma seleção múltipla.

Na caixa Vínculo da célula, insira uma referência de célula que contenha o estado atual da caixa de seleção: 

Quando a caixa de seleção for marcada, a célula vinculada retornará um

valor VERDADEIRO. 

Quando a caixa de seleção for desmarcada, a célula vinculada retornará

um valor FALSO. 

Quando a célula vinculada estiver vazia, o Microsoft Office Excel

interpretará o estado da caixa de seleção como FALSO. 

Se o estado da caixa de seleção for misto, a célula vinculada retornará um

valor de erro #N/D.

Use o valor retornado em uma fórmula para responder ao estado atual da caixa de seleção. Por exemplo, um formulário de pesquisa de viagem contém duas caixas de seleção rotuladas como Europa e Austrália em uma caixa de grupo Locais visitados. Essas duas caixas de seleção são vinculadas às células C1 (para Europa) e C2 (para Austrália). Quando um usuário marcar a caixa de seleção Europa, a seguinte fórmula na Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 191

célula D1 será avaliada como "Viajou para a Europa": =SE (C1=VERDADEIRO; "Viajou para Europa”; "Nunca viajou para Europa")

Quando um usuário desmarcar a caixa de seleção Austrália, a seguinte fórmula na célula D2 será avaliada como "Nunca viajou para a Austrália": =SE(C2=VERDADEIRO; "Viajou para Austrália”; "Nunca viajou para Austrália")

Se existirem três estados para avaliar (Marcado, Desmarcado e Misto) no mesmo grupo de opções, pode-se usar a função PROCV de maneira semelhante.

20.2 Botão de opção Permite uma única opção entre um conjunto limitado de opções mutuamente exclusivas. Um botão de opção (ou botão de rádio) está geralmente contido em um quadro ou uma caixa de grupo. Por exemplo, pode-se usar um botão de opção em um formulário de pedido para que um usuário possa selecionar um dos intervalos de tamanho, como pequeno, médio ou grande. Também pode usá-lo para escolher uma opção de remessa, como terrestre, expressa ou em 24hrs.

Na guia Desenvolvedor, no grupo Controles, clique em Inserir e, em Controles de Formulário, clique em Botão de opção

.

Clique no local da planilha em que deseja exibir o canto superior esquerdo do botão de opção. Na guia Desenvolvedor, no grupo Controles, clique em Propriedades

.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 192

Para definir as propriedades do controle

Em Valor, especifique o estado inicial do botão de opção, seguindo um destes procedimentos: 

Para exibir um botão de opção marcado, clique em Marcado.



Para exibir um botão de opção desmarcado, clique em Desmarcado.

Na caixa Vínculo da célula, insira uma referência de célula que contenha o estado atual do botão de opção.

A célula vinculada retorna o número do botão de opção selecionado no grupo de opções. Use a mesma célula vinculada para todas as opções em um grupo. O primeiro botão de opção retorna 1, o segundo botão de opção retorna 2 e assim por diante. Se houver dois ou mais grupos de opções na mesma planilha, use uma célula vinculada diferente para cada grupo de opções. Use o número retornado em uma fórmula para responder à opção selecionada. Por exemplo, um formulário de pessoal, como uma caixa de grupo de Tipo de cargo, contém dois botões de opções rotulados como Período integral e Meio período vinculados à célula C1. Depois que um usuário marcar uma das duas opções, a fórmula a seguir na célula D1 é avaliada como "Período integral", se o primeiro botão de opção for marcado, ou "Meio período", se o segundo botão de opção for marcado. =SE(C1=1; "Integral”; "Meio período")

20.3 Caixa de listagem e caixa de combinação Use uma caixa de listagem ou uma caixa de combinação (que são controles um pouco diferentes) para permitir que os usuários façam várias escolhas de itens ou insiram seus próprios valores em uma lista. Exemplos típicos de itens nesses tipos de listas são nomes de funcionários, taxas e itens de produto. Caixa de listagem: Exibe uma lista de um ou mais itens de texto na qual um usuário pode escolher uma opção. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 193

Caixa de listagem

Caixa de combinação: Combina uma caixa de texto com uma caixa de listagem de modo a criar uma caixa de listagem suspensa. Uma caixa de combinação é mais compacta do que uma caixa de listagem, mas requer que o usuário clique na seta para baixo para exibir a lista de itens. Use uma caixa de combinação para permitir que um usuário digite uma entrada ou escolha apenas um item de uma lista. O controle exibe o valor atual na caixa de texto, independentemente de como esse valor foi inserido.

Caixa de combinação

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 194

Adicionar uma caixa de listagem Na guia Desenvolvedor, no grupo Controles, clique em Inserir e, em Controles de Formulário, clique em Caixa de listagem

.

Clique no local da planilha em que deseja exibir o canto superior esquerdo da caixa de listagem. Na guia Desenvolvedor, no grupo Controles, clique em Propriedades

.

Na caixa Intervalo de entrada, insira uma referência de célula a um intervalo que contenha os valores para exibição na caixa de listagem (uma lista de valores). Na caixa Vínculo da célula, insira uma referência de célula que contenha a seleção da caixa de listagem. A célula vinculada retorna o número do item selecionado na caixa de listagem. O primeiro item no intervalo retorna um valor de 1, o segundo item no intervalo retorna um valor de 2 e assim por diante. Use esse número em uma fórmula para retornar o item real do intervalo de entrada. A lista de nomes de países tem uma caixa de listagem vinculada à célula K1, o intervalo de entrada da lista é J1:J3 e os itens no intervalo são: "Brasil" (J1), "Austrália" (J2) e "Itália" (J3). Veja a fórmula a seguir:

=ÍNDICE(J1:J3;K2)

Inserida na célula L1, retornará o valor "Itália" do intervalo J1:J3 se o valor de K2 for 3, com base na seleção atual da caixa de listagem.

Em Tipo de seleção, especifique como os itens podem ser selecionados na caixa de listagem: Para criar uma caixa de listagem de seleção única, clique em Simples. Para criar uma caixa de listagem de seleção múltipla, clique em Múltiplo. Para criar uma caixa de listagem de seleção estendida, clique em Estendido. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 195

Observação: Se for definido o tipo de seleção como Múltiplo ou Estendido, a célula especificada na caixa Vínculo da célula retornará um valor de 0 e será ignorada. Os tipos de seleção Múltiplo e Estendido exigem o uso de código do Microsoft VBA (Visual Basic for Applications).

Adicionar uma caixa de combinação Na guia Desenvolvedor, no grupo Controles, clique em Inserir e, em Controles de Formulário, clique em Caixa de combinação

.

Clique no local da planilha em que deseja exibir o canto superior esquerdo da caixa de combinação. A seta suspensa é exibida com a caixa de texto recolhida. Para exibir a caixa de texto, arraste a alça de dimensionamento no centro esquerdo até a direita. Na guia Desenvolvedor, no grupo Controles, clique em Propriedades . Para especificar as propriedades do controle: Na caixa Intervalo de entrada, insira uma referência de célula a um intervalo que contenha os valores para exibição (lista de valores) na lista suspensa da caixa de combinação. Na caixa Vínculo da célula, insira a referência de célula que contém a seleção na lista suspensa da caixa de combinação. A célula vinculada retorna o número do item selecionado na lista suspensa da caixa de combinação. O primeiro item no intervalo retorna um valor de 1, o segundo item no intervalo retorna um valor de 2 e assim por diante. Use esse número em uma fórmula para retornar o item real do intervalo de entrada. Pode-se usar o mesmo exemplo dos países: um formulário tem uma caixa de combinação vinculada as células com nomes de países e vinculada à célula K1. O intervalo de entrada da lista é J1:J3 e os itens no intervalo são: "Brasil" (J1), "Austrália" (J2) e "Itália" (J3). Veja a fórmula a seguir: Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 196

=ÍNDICE(J1:J3;K5)

Na caixa Linhas suspensas, insira o número de linhas a serem exibidas na lista suspensa da caixa de combinação. Se o valor for: 0, ele será ignorado e tratado como 1. Menor do que o número de itens no intervalo especificado na caixa Intervalo de entrada, uma barra de rolagem será exibida. Igual a ou maior do que o número de itens no intervalo especificado na caixa Intervalo de entrada, nenhuma barra de rolagem será exibida.

20.4 Barras de rolagem e botões de rotação A Barra de rolagem percorre um intervalo de valores quando se clica nas setas de rolagem ou quando arrasta a caixa de rolagem. É possível percorrer uma página (ou intervalo predefinido) de valores clicando na região entre a caixa de rolagem e a seta de rolagem. Normalmente, um usuário também pode digitar um valor de texto diretamente na célula ou na caixa de texto associada. Use uma barra de rolagem para definir ou ajustar um amplo intervalo de valores ou nos casos em que a precisão não é importante. Por exemplo, use uma barra de rolagem para um intervalo de porcentagens que sejam estimativas ou para ajustar a seleção de cores em graduações. Barra de rolagem

O Botão de rotação facilita o aumento ou a redução de um valor, como um incremento de número, um horário ou uma data. Para aumentar o valor, clique na seta para cima e, para diminuí-lo, clique na seta para baixo. Um usuário também pode digitar um valor de texto diretamente na célula ou na caixa de texto associada. Use um botão de rotação, por exemplo, para facilitar a inserção de um mês, de um dia, de um número de ano ou o aumento de um nível de volume. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 197

Botão de rotação

Adicionar uma barra de rolagem

Na guia Desenvolvedor, no grupo Controles, clique em Inserir e, em Controles de Formulário, clique em Barra de rolagem

.

Clique no local da planilha em que deseja exibir o canto superior esquerdo da barra de rolagem. A barra de rolagem é adicionada em orientação de cima para baixo. Para orientar a barra de rolagem da esquerda para a direita, arraste uma das alças de dimensionamento na diagonal. Na guia Desenvolvedor, no grupo Controles, clique em Propriedades

.

Para especificar as propriedades do controle: Na caixa Valor atual, insira o valor inicial no intervalo de valores permitidos abaixo, que corresponde à posição da caixa de rolagem na barra de rolagem. Esse valor não deve ser: 

Menor do que o Valor mínimo; caso contrário, o Valor mínimo será

usado. 

Maior do que o Valor máximo; caso contrário, o Valor máximo será

usado. Na caixa Valor mínimo, insira o menor valor que um usuário pode especificar ao posicionar a caixa de rolagem no ponto mais próximo do topo da barra de rolagem vertical ou na extremidade esquerda de uma barra de rolagem horizontal. Na caixa Valor máximo, insira o maior valor que um usuário pode especificar ao posicionar a caixa de rolagem no ponto mais distante do topo da barra de Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 198

rolagem vertical ou na extremidade direita de uma barra de rolagem horizontal. Na caixa Alteração incremental, insira a proporção em que o valor aumenta ou diminui e o grau em que a caixa de rolagem se move quando a seta em uma das extremidades da barra de rolagem é clicada. Na caixa Mudança de página, insira a proporção em que o valor aumenta ou diminui e o grau em que a caixa de rolagem se move quando se clica na área entre a caixa de rolagem e uma das setas de rolagem. Por exemplo, em uma caixa de rolagem com um valor mínimo de 0 e um valor máximo de 10, ao definir a propriedade Mudança de página como 2, o valor aumentará ou diminuirá em 2 (nesse caso, 20% do intervalo de valor da caixa de rolagem) ao clicar na área entre a caixa de rolagem e uma das setas de rolagem.

Na caixa Vínculo da célula, insira uma referência de célula que contenha a posição atual da caixa de rolagem. A célula vinculada retornará o valor atual correspondente à posição da caixa de rolagem. Use esse valor em uma fórmula para responder ao valor da célula especificada na caixa Vínculo da célula que corresponde à posição atual da caixa de rolagem.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 199

Imagine uma barra de rolagem de fator de risco com as propriedades a seguir: Propriedade

Valor

Valor atual

100

Valor mínimo

0

Valor máximo

100

Alteração incremental

1

Mudança de página

5

Vínculo da célula

C1

Com essas configurações, o usuário pode usar a barra de rolagem para inserir um número preciso ou clicar na área entre a barra de rolagem e a seta para alterar o valor em incrementos de 5. A fórmula a seguir na célula D1 retorna o valor exato baseado no valor atual da célula vinculada: =SE(C1 > 50, "Aceitável"; "Inaceitável") Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 200

Adicionar um botão de rotação

Na guia Desenvolvedor, no grupo Controles, clique em Inserir e, em Controles de Formulário, clique em Botão de Rotação

.

Clique no local da planilha em que deseja exibir o canto superior esquerdo do botão de rotação. Na guia Desenvolvedor, no grupo Controles, clique em Propriedades. Para definir as propriedades do controle:

Na caixa Valor atual, insira o valor inicial do botão de rotação no intervalo de valores permitidos a seguir. Esse valor não deve ser: 

Menor do que o Valor mínimo; caso contrário, o Valor mínimo será usado.



Maior do que o Valor máximo; caso contrário, o Valor máximo será

usado.

Na caixa Valor mínimo, insira o menor valor que um usuário pode especificar ao clicar na seta inferior do botão de rotação. Na caixa Valor máximo, insira o maior valor que um usuário pode especificar ao clicar na seta superior do botão de rotação. Na caixa Alteração incremental, insira a proporção em que o valor aumenta ou diminui quando as setas são clicadas. Na caixa Vínculo da célula, insira uma referência de célula que contenha a posição atual do botão de rotação. A célula vinculada retorna à posição atual do botão de rotação. Use esse valor em uma fórmula para responder ao valor da célula especificada na caixa Vínculo da célula que corresponde à posição atual do botão de rotação. Por exemplo, crie um botão de rotação para definir a idade atual de um funcionário, com as propriedades a seguir:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 201

Propriedade

Valor

Valor atual

1

Valor mínimo

0

Valor máximo

100

Alteração incremental

1

Vínculo da célula

C1

Com essas configurações, o usuário pode usar a barra de rolagem para inserir um número preciso ou clicar na área entre a barra de rolagem e a seta para alterar o valor em incrementos de 5. A fórmula a seguir na célula D1 retorna o valor exato baseado no valor atual da célula vinculada:

=SE(C1 > 50, "Aceitável"; "Inaceitável")

20.5 Rótulos e Caixas de Texto Use um rótulo e uma caixa de texto em conjunto para a entrada básica de texto. Um rótulo é um texto que define a finalidade de uma célula ou de uma caixa de texto ou que exibe texto descritivo, como títulos, legendas ou instruções breves. Além disso, um rótulo pode exibir uma imagem descritiva. Use um rótulo para o posicionamento flexível de instruções, para enfatizar texto e quando células mescladas ou um local de célula específico não for uma solução prática. Uma caixa de texto é uma caixa retangular na qual é possível exibir, inserir ou editar texto ou dados ligados a uma célula. Uma caixa de texto também pode se um campo de texto estático que apresenta informações somente leitura. Use-a como alternativa para inserir texto em uma célula, quando quiser exibir um objeto com flutuação livre. Também é possível usar uma caixa de texto para exibir ou visualizar um texto que não depende dos limites de linhas e colunas, preservando o layout de uma grade ou de uma tabela de dados na planilha.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 202

Rótulo

Adicionar um rótulo Na guia Desenvolvedor, no grupo Controles, clique em Inserir e, em Controles de Formulário, clique em Rótulo

.

Clique no local da planilha em que deseja exibir o canto superior esquerdo do rótulo. Para especificar as propriedades de controle, clique no controle com o botão direito e clique em Formatar Controle.

20.6 Caixa de grupo Caixas de grupo são objetos retangulares com rótulos opcionais. Use uma caixa de grupo ou um controle de quadro para organizar visualmente os itens relacionados em um formulário. Por exemplo, em um aplicativo de pedidos de clientes, é possível agrupar o nome, o endereço e o número da conta de um cliente. Ou, em um formulário de pedido, é possível agrupar uma lista de itens disponíveis.

1. Na guia Desenvolvedor, no grupo Controles, clique em Inserir e, em Controles de Formulário, clique em Caixa de grupo

. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 203

2. Clique no local da planilha em que deseja exibir o canto superior esquerdo da caixa de grupo. 3. Coloque controles relacionados dentro dos limites da Caixa de grupo. Por exemplo: 

Caixas de seleção, como uma lista de produtos relacionados para

compra. 

Rótulos e caixas de texto, como informações de nome e endereço.



Botões de opção que indicam um conjunto de opções mutuamente

exclusivas, como Pequeno, Médio ou Grande. 4. Para especificar as propriedades de controle, clique no controle com o botão direito e clique em Formatar Controle.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 204

21 MACROS Para automatizar uma tarefa repetitiva, é possível gravar uma macro rapidamente no Microsoft Office Excel. Após criar uma macro, é possível atribuí-la a um objeto (como um botão da barra de ferramentas, um elemento gráfico ou um controle) para poder executá-la clicando no objeto. Se não precisar mais usar a macro, pode-se excluí-la.

21.1 Gravar uma macro Quando se grava uma macro, o gravador de macro grava todas as etapas necessárias para concluir as ações a serem executadas por essa macro. A navegação na Faixa de Opções não é incluída nas etapas gravadas. Para iniciar a gravação confira se a Guia Desenvolvedor está disponível. Se não estiver disponível, faça o seguinte: Clique no Botão Microsoft Office

e, em seguida, clique em Opções do

Excel. Na categoria Mais usados, em Opções principais para o trabalho com o Excel, marque a caixa de seleção Mostrar guia Desenvolvedor na Faixa de Opções e clique em OK. Para definir o nível de segurança temporariamente e habilitar todas as macros, faça o seguinte: Na guia Desenvolvedor, no grupo Código, clique em Segurança de Macro.

Em Configurações de Macro, clique em Habilitar todas as macros (não recomendável; códigos possivelmente perigosos podem ser executados) e em OK.

OBS.: para ajudar a impedir a execução de códigos potencialmente perigosos, convém retornar para qualquer uma das configurações que desabilitam todas as macros depois de terminar de trabalhar com macros.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 205

Na guia Desenvolvedor, no grupo Código, clique em Gravar Macro. Na caixa Nome da macro, insira um nome para a macro. O primeiro caractere do nome da macro deve ser uma letra. Os demais caracteres podem ser letras, números ou caracteres sublinhados. Espaços não podem ser usados em um nome de macro; um caractere sublinhado funciona da mesma forma que um separador de palavras. Para atribuir uma tecla de atalho de combinação com CTRL para executar a macro, na caixa Tecla de atalho, digite a letra minúscula ou maiúscula que deseja usar. A tecla de atalho substituirá todas as teclas de atalho padrão equivalentes do Excel enquanto a pasta de trabalho que contém a macro estiver aberta. Na lista Armazenar macro em, selecione a pasta de trabalho onde deseja armazenar a macro. Se quiser que uma macro fique disponível sempre que usar o Excel, selecione Pasta de Trabalho Pessoal de Macros. Quando é selecionada a opção Pasta de Trabalho Pessoal de Macros, o Excel cria uma pasta de trabalho pessoal de macros oculta (Personal.xlsb), se ela ainda não existir, e salva a macro nessa pasta de trabalho. Na caixa Descrição, digite uma descrição da macro. Clique em OK para iniciar a gravação. Execute todas as ações que deseja gravar. Na guia Desenvolvedor, no grupo Código, clique em Parar Gravação Também é possível clicar em Parar Gravação

.

à esquerda da barra de status.

21.2 Atribuir uma macro a um objeto, a um elemento gráfico ou a um controle Em uma planilha, clique com o botão direito no objeto, no elemento gráfico ou no controle ao qual deseja atribuir uma macro existente e clique em Atribuir Macro. Na caixa Nome da macro, clique na macro que deseja atribuir.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 206

21.3 Excluir uma macro Na guia Desenvolvedor, no grupo Código, clique em Macros.

Na lista Macros em, selecione a pasta de trabalho que contém a macro que deseja excluir. Por exemplo, clique em Esta Pasta de Trabalho. Na caixa Nome da macro, clique no nome da macro que deseja excluir. Clique em Excluir.

21.4 Exemplo de criação de macro Como exemplo inicial, criaremos uma macro que consiste em classificar os dados da planilha. Veja os dados na figura abaixo:

A Classificação deveria começar no 1º e terminar no 5º - o que não ocorre pois ela não foi classificada com os critérios desejados. Clique na guia "Desenvolvedor” > "Gravar nova macro”. Aparecerá a tela de propriedades da Macro. Em Nome da macro deve-se colocar o nome que quer dar à macro e em Tecla de atalho. Em Armazenar macro em: será indicado aonde será salva a macro. Em Descrição: coloca-se qualquer observação ou descrição da macro.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 207

Depois de preencher os dados da macro, clique em "OK". A partir de agora, a macro já está sendo gravada, sendo que a gravação conterá apenas comandos executados no Excel. Selecione os dados da planilha. Clique na guia "Dados" > "Classificar" e surgirá a tela para definir os critérios de classificação:

Neste exemplo, classifiquemos a coluna B por valores, de A a Z. Clique em OK (os dados foram classificados) – e pare a gravação da macro, bastando clicar no botão “Parar Gravação. A macro está gravada. Agora será adicionado um botão para executar a macro. Na mesma guia Desenvolvedor, clique no botão “Inserir” e depois no ícone de “Botão”. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 208

Automaticamente o Excel abre a caixa para atribuir uma macro ao botão. Basta selecionar a macro criada e clicar em “OK”. O botão é inserido na planilha e basta que seja clicado para que a macro seja executada novamente.

Importante: Cada vez que esta macro for executada, ela classificará os dados constantes na coluna B em ordem crescente. Para alterar a ordem ou coluna a classificar, é preciso editar a macro.

21.5 Referências Relativas e Absolutas em macros Vimos em referências de células que o Excel trabalha com dois tipos de referências: Relativas e Absolutas. Em se tratando de Macros, a lógica de funcionamento é a mesma. Por exemplo: ao gravar uma macro que formate a célula na cor vermelha e Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 209

negrito, ou uma macro para mover dados, o Excel sempre manterá como valor as referência das células selecionadas. Isso significa que, se, ao gravar uma macro movendo os valores de A1 para B1, ao executar a macro na linha A2, o Excel moverá os dados de A1 para B1. Neste caso, queremos que o Excel mova para a próxima coluna o valor selecionado, não importa onde esteja. Pra isso, usamos a referência relativa, que informa, no momento de gravar a macro, que o Excel deverá considerar a célula selecionada no momento de execução da macro: Digite qualquer valor em A1;  Na Guia Desenvolvedor, clique no botão de Referências relativas, antes de gravar a macro:

 Clique em Gravar Macro. Dê um nome para sua macro, e coloque como tecla de atalho CTRL+M;  Mova o conteúdo de A1 para A2, e depois clique em Parar Gravação;  Faça 2 testes: apague o valor de A2 e digite algo em A1. Execute a macro. O valor será movido de A1 para A2;  Depois, digite algo em B5, mova o cursor para C5 e execute a macro. Note que o valor será movido para C5.  Caso não fosse usado Referencia Relativa, o valor seria movido para A2.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 210

22 GRÁFICOS

Gráficos são usados para exibir séries de dados numéricos em formato gráfico, com o objetivo de facilitar a compreensão de grandes quantidades de dados e do relacionamento entre diferentes séries de dados. Para criar um gráfico no Excel, comece inserindo os dados numéricos desse gráfico em uma planilha. Em seguida, faça a plotagem desses dados em um gráfico selecionando o tipo de gráfico que deseja utilizar na Faixa de Opções do Office (guia Inserir, grupo Gráficos).

Dados da planilha Gráfico criado a partir de dados da planilha

O Excel oferece suporte para vários tipos de gráficos com a finalidade de ajudá-lo a exibir dados de maneira que sejam significativas. Ao criar um gráfico ou modificar um gráfico existente, é possível escolher entre uma grande variedade de tipos de gráficos (como gráfico de colunas ou de pizza) e seus subtipos (como gráfico de colunas empilhadas ou gráfico de pizza em 3D). Também pode-se criar um gráfico de combinação usando mais de um tipo de gráfico, como este exemplo:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 211

22.1 Elementos de um gráfico Um gráfico possui vários elementos. Alguns deles são exibidos por padrão, enquanto outros podem ser adicionados conforme necessário. É possível alterar a exibição dos elementos do gráfico movendo-os para outros locais no gráfico, redimensionando-os ou alterando seu formato. Também é possível remover os elementos que não se deseja exibir.

A área do gráfico. A área de plotagem do gráfico. Os pontos de dados da série de dados que são plotados no gráfico. O eixo horizontal (categoria) e o eixo vertical (valor) ao longo dos quais os dados são plotados no gráfico. A legenda do gráfico.

Um título de gráfico e eixo que pode ser utilizado no gráfico. Um rótulo de dados que pode ser utilizado para identificar os detalhes de um ponto de dados em uma série de dados.

22.2 Criar um gráfico básico Para a maioria dos gráficos, como os de colunas e barras, pode-se utilizar os dados organizados em linhas ou colunas em uma planilha. Entretanto, alguns tipos de dados (como os de pizza e de bolhas) exigem uma organização específica dos dados, conforme descrito na tabela a seguir.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 212

TIPO DE GRÁFICO Gráfico de colunas,

ORGANIZAR DADOS Em colunas ou linhas, como:

barras, linhas, área,

LOREM

IPSUM

1

2

3

4

superfície ou radar

Ou: LOREM

1

3

Ipsum

2

4

Gráfico de pizza ou

Para uma série de dados, em uma coluna ou linha de dados e

rosca

em uma coluna ou linha de rótulos de dados, como: A

1

B

2

C

3

Ou: A

B

C

1

2

3

Para várias séries de dados, em várias colunas ou linhas de dados e em uma coluna ou linha de rótulos de dados, como: A

1

2

B

3

4

C

5

6

A

B

C

1

2

3

4

5

6

Ou:

Gráfico (dispersão) bolhas

XY Em colunas, posicionando valores x na primeira coluna e os ou

de valores y correspondentes e os valores de tamanhos de bolhas em colunas adjacentes, como: Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Gráfico de ações

Página | 213 X

Y

TAMANHO DE BOLHA

1

2

3

4

5

6

Nas colunas ou nas linhas na seguinte ordem, utilizando nomes ou datas como rótulos: valores altos, valores baixos e valores de fechamento Como: DATA

ALTA

BAIXA

FECHAR

01-01-2002

46.125

42

44.063

Ou: DATA Alta Baixa Fechar

01/01/2002 46.125 42 44.063

Adiante são descritos os passos para criação de um gráfico básico.

1 - Selecione as células que contêm os dados que se deseja usar no gráfico Se selecionar apenas uma célula, o Excel plotará1 automaticamente todas as células que contêm dados adjacentes a essa célula em um gráfico. Se as células que deseja plotar em um gráfico não estiverem em intervalo contíguo, será possível selecionar células não adjacentes ou intervalos, até que a seleção forme um retângulo.

2 - Na Guia Inserir, clique no tipo de gráfico e, em seguida, no subtipo de gráfico que deseja usar. Para visualizar todos os tipos de gráficos disponíveis, clique em um tipo de gráfico, clique em Todos os Tipos de Gráficos para exibir a caixa de diálogo Inserir Gráfico, clique nas setas para rolar pelos tipos e subtipos de gráficos disponíveis e, em seguida, clique na opção que deseja usar. 1

Plotar: desenhar em um gráfico dados de acordo com uma série de dados

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 214

Por padrão, o gráfico é colocado na planilha como um gráfico incorporado. Para colocá-lo em uma planilha de gráfico separada, altere a sua localização: 1. Clique no gráfico para selecioná-lo. Isso exibe as Ferramentas de Gráfico, adicionando as guias Design, Layout e Formatar. 2. Na guia Design, no grupo Local, clique em Mover Gráfico.

3. Em Escolha o local onde o gráfico deve ser posicionado. 4. Excel atribuirá automaticamente um nome ao gráfico, como Gráfico1 se este for o primeiro gráfico criado em uma planilha. Para alterar esse nome: Clique no gráfico. Na guia Layout, no grupo Propriedades, clique na caixa de texto Nome do Gráfico. Digite um novo nome. Pressione ENTER.

OBS.: Para criar rapidamente um gráfico que se baseie no tipo de gráfico padrão, selecione os dados desejados e pressione ALT+F1 para criar um gráfico incorporado ou F11. Para criar um gráfico em uma planilha de gráfico separada.

Ao criar um gráfico, o Excel determina a orientação das séries de dados com base no número de linhas e colunas da planilha que estão inclusas nesse gráfico. Após a criação do gráfico, é possível alterar a forma como essas linhas e colunas são plotadas no gráfico, alternando de linhas para colunas, ou vice-versa. Depois de criar um gráfico, é possível alterar rapidamente o tipo desse gráfico inteiro para proporcionar uma aparência diferente ou pode selecionar um tipo de gráfico diferente para qualquer série de dados individual, o que o transformará em um gráfico de combinação. Para excluir um gráfico: clique no gráfico para selecioná-lo e pressione DELETE.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 215

22.3 Alterar o Layout ou o Estilo de um Gráfico Depois de criar um gráfico, é possível alterar instantaneamente a sua aparência. Em vez de adicionar ou alterar manualmente os elementos ou a formatação do gráfico, é possível aplicar rapidamente um layout e um estilo predefinidos ao gráfico. O Excel fornece uma variedade de layouts e estilos úteis e predefinidos (ou layouts e estilos rápidos), mas é possível personalizar um layout ou estilo conforme necessário, alterando manualmente o layout e o formato de elementos individuais.

Aplicar um layout de gráfico predefinido

1. Clique no gráfico desejado; 2. Na guia Design, no grupo Layouts de Gráfico, clique no layout de gráfico que deseja usar.

Para ver todos os layouts disponíveis, clique em Mais .

Aplicar um estilo de gráfico predefinido

1. Clique no gráfico desejado; 2. Na guia Design, no grupo Estilos de Gráfico, clique no estilo de gráfico a ser usado.

Para ver todos os estilos de gráfico predefinidos, clique em Mais .

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 216

Alterar manualmente o layout dos elementos do gráfico

Clique no gráfico ou no elemento de gráfico que deseja alterar, ou; Clique em qualquer local do gráfico para exibir as Ferramentas de Gráfico. Na guia Formatar, no grupo Seleção Atual, clique na seta próxima à caixa Elementos de Gráfico e selecione o elemento de gráfico desejado.

Na guia Layout, no grupo Rótulos, Eixos ou Plano de Fundo, clique no elemento de gráfico que deseja alterar e clique na opção de layout desejada.

As opções de layout selecionadas são aplicadas ao elemento de gráfico selecionado. Por exemplo, se o gráfico inteiro estiver selecionado, rótulos de dados serão aplicados a todas as séries de dados. Se houver um único ponto de dados selecionado, os rótulos de dados serão aplicados somente às séries de dados selecionadas ou ao ponto de dados.

Alterar manualmente o formato dos elementos do gráfico

1. Clique no gráfico ou no elemento de gráfico desejado; 2. Isso exibe as Ferramentas de Gráfico, adicionando as guias Design, Layout e Formatar. 3. Na guia Formatar, siga qualquer um destes procedimentos:

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO 

Página | 217

Para formatar qualquer elemento do gráfico selecionado, no grupo Seleção

Atual, clique em Formatar Seleção e, em seguida, selecione as opções de formato que deseja. 

Para formatar a forma de um elemento do gráfico selecionado, no grupo Estilos

de Forma, clique no estilo que deseja ou clique em Preenchimento de Forma, Contorno da Forma ou Efeitos de Forma e, em seguida, selecione as opções de formato que deseja. 

Para formatar o texto de um elemento do gráfico selecionado utilizando o

WordArt, no grupo Estilos de WordArt, clique no estilo que deseja ou clique em Preenchimento do Texto, Contorno do Texto ou Efeitos de Texto e selecione as opções de formato que deseja.

Para utilizar a formatação de texto normal com o objetivo formatar o texto nos elementos do gráfico, clique com o botão direito ou selecione o texto e clique nas opções de formatação desejadas na Minibarra de ferramentas, ou ainda, os botões de formatação da Faixa de Opções (guia Início, grupo Fonte).

22.4 Adicionar ou remover títulos ou rótulos de dados Para facilitar o entendimento de um gráfico, é possível adicionar títulos, como um título de gráfico e títulos de eixo. Títulos de eixo estão geralmente disponíveis para todos os eixos que podem ser exibidos em um gráfico, incluindo eixos de profundidade (série) em gráficos 3D. Alguns tipos de gráfico (como gráficos de radar) possuem eixos, mas não podem exibir títulos de eixos. Os tipos de gráfico que não possuem eixos (como gráficos de pizza e de roscas) também não exibem títulos de eixo. Também é possível vincular o título do gráfico e os títulos de eixos ao texto correspondente nas células da planilha, criando uma referência a essas células. Títulos vinculados são automaticamente atualizados no gráfico quando se altera o texto correspondente na planilha. Para identificar rapidamente uma série de dados em um gráfico, adicione rótulos de dados aos pontos de dados desse gráfico. Por padrão, os rótulos de dados são vinculados a valores na planilha e são atualizados automaticamente quando são feitas alterações nesses valores.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 218

Adicionar um título de gráfico

1. Clique no gráfico desejado. Isso exibe as Ferramentas de Gráfico, adicionando as guias Design, Layout e Formatar. 2. Na guia Layout, no grupo Rótulos, clique em Título do Gráfico.

3. Clique em Título de Sobreposição Centralizado ou Acima do Gráfico. 4. Na caixa de texto Título do Gráfico exibida no gráfico, digite o texto desejado. 5. Para formatar o texto, selecione-o e clique nas opções de formatação desejadas na Minibarra de ferramentas.

Adicionar títulos de eixo

1. Clique no gráfico desejado. Isso exibe as Ferramentas de Gráfico, adicionando as guias Design, Layout e Formatar. 2. Na guia Layout, no grupo Rótulos, clique em Títulos dos Eixos.

3. Siga um destes procedimentos: 

Para adicionar um título a um eixo horizontal (categoria) principal, clique em

Título do Eixo Horizontal Principal e selecione a opção desejada. 

Para adicionar um título ao eixo vertical principal (valor), clique em Título do

Eixo Vertical Principal e selecione a opção desejada. 

Para adicionar um título a um eixo de profundidade (série), clique em Título do

Eixo de Profundidade e selecione a opção desejada.

4. Na caixa de texto Título do Eixo exibida no gráfico, digite o texto desejado. 5. Para formatar o texto, selecione-o e clique nas opções de formatação desejadas na Minibarra de ferramentas. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 219

Se o gráfico for alternado para outro tipo de gráfico que não oferece suporte a títulos de eixo (como um gráfico de pizza), esses títulos deixarão de ser exibidos. Os títulos de eixo exibidos para eixos secundários serão perdidos caso o gráfico seja alternado para um tipo de gráfico que não exibe eixos secundários.

Vincular um título a uma célula da planilha

1. Em um gráfico, clique no título de gráfico ou de eixo que você deseja vincular a uma célula da planilha. 2. Na planilha, clique na barra de fórmula e digite um sinal de igual (=). 3. Selecione a célula da planilha que contém os dados ou o texto que você deseja exibir no gráfico. 4. Pressione ENTER.

Adicionar rótulos de dados

Para adicionar um rótulo de dados a todos os pontos de dados de todas as séries de dados, clique na área do gráfico. Para adicionar um rótulo de dados a todos os pontos de dados de uma série de dados, clique em qualquer local da série de dados que deseja rotular. Para adicionar um rótulo de dados a um único ponto de dados em uma série de dados, clique na série de dados que contém o ponto de dados que deseja rotular e clique nesse ponto. Esses procedimentos exibem as Ferramentas de Gráfico, adicionando as guias Design, Layout e Formatar. Na guia Layout, no grupo Rótulos, clique em Rótulos de Dados e, em seguida, clique na opção de exibição desejada.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 220

Remover títulos ou rótulos de dados de um gráfico

1. Clique no gráfico. 2. Na guia Layout, no grupo Rótulos, siga um destes procedimentos: 

Para remover um título de gráfico, clique em Título de Gráfico e em Nenhum.



Para remover um eixo, clique em Título do Eixo, clique no tipo de eixo desejado

e clique em Nenhum. 

Para remover rótulos de dados, clique em Rótulos de Dados e clique em

Nenhum.

Também é possível remover rapidamente um título ou rótulo de gráfico: clique nele e pressione DELETE.

22.5 Legendas Ao criar um gráfico, uma legenda é exibida, mas é possível ocultá-la ou modificar o seu local em seguida. Para isso, clique no gráfico desejado. Na guia Layout, no grupo Rótulos, clique em Legenda.

Para ocultar a legenda, clique em Nenhum. Para ver opções adicionais, clique em Mais Opções de Legenda e selecione a opção de exibição desejada.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 221

22.6 Exibir ou ocultar eixos ou linhas de grade do gráfico Ao criar um gráfico, os eixos principais são exibidos para a maioria dos tipos de gráficos. É possível ativá-los ou desativá-los conforme necessário. Ao adicionar eixos, pode-se especificar o nível de detalhes que eles devem exibir. Um eixo de profundidade é exibido quando um gráfico 3D é criado. Quando os valores em um gráfico variam muito de uma série de dados para outra ou quando existem tipos mistos de dados (por exemplo, preço e volume), é possível plotar uma ou mais séries de dados em um eixo vertical secundário (valor). A escala do eixo vertical secundário reflete o valor da série de dados associada. Depois de adicionar um eixo vertical secundário a um gráfico, também pode-se adicionar um eixo horizontal secundário (categoria), o que pode ser útil em um gráfico xy (dispersão) ou em um gráfico de bolhas. Para facilitar a leitura de um gráfico, é possível exibir ou ocultar suas linhas de grade horizontais e verticais que se estendem de quaisquer eixos verticais e horizontais através da área de plotagem do gráfico.

Exibir ou ocultar eixos principais

Clique no gráfico desejado. Na guia Layout, no grupo Eixos, clique em Eixos e siga um destes procedimentos: 

Para exibir um eixo, clique em Eixo Horizontal Principal, Eixo Vertical

Principal ou Eixo de Profundidade (em um gráfico 3D) e clique na opção de exibição de eixo desejada. 

Para ocultar um eixo, clique em Eixo Horizontal Principal, Eixo Vertical

Principal ou Eixo de Profundidade (em um gráfico 3D). Em seguida, clique em Nenhum. 

Para exibir opções detalhadas de dimensionamento e exibição de eixos, clique

em Eixo Horizontal Principal, Eixo Vertical Principal ou Eixo de Profundidade (em um gráfico 3D). Em seguida, clique em Mais Opções de Eixo Horizontal Principal, Mais Opções de Eixo Vertical Principal ou Mais Opções de Eixo de Profundidade.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 222

Exibir ou ocultar eixos secundários

Em um gráfico, clique na série de dados que deseja plotar ao longo de um eixo vertical secundário ou, para selecionar a série de dados em uma lista de elementos do gráfico, clique no gráfico e na guia Formato, no grupo Seleção Atual, clique na seta ao lado da caixa Elementos do Gráfico e clique na série de dados que deseja plotar ao longo do eixo vertical secundário.

Na guia Formatar, no grupo Seleção Atual, clique em Formatar Seleção. Clique em Opções de Eixo, se não estiver selecionado, e, em Plotar Série no, clique em Eixo Secundário e em Fechar. Na guia Layout, no grupo Eixos, clique em Eixos.

Siga um destes procedimentos: 

Para exibir um eixo vertical secundário, clique em Eixo Vertical Secundário e

selecione a opção de exibição desejada. 

Para exibir um eixo horizontal secundário, clique em Eixo Horizontal

Secundário e selecione a opção de exibição desejada. 

Para ocultar um eixo secundário, clique em Eixo Vertical Secundário ou Eixo

Horizontal Secundário e clique em Nenhum. Também é possível clicar no eixo secundário desejado e pressionar DELETE.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 223

Exibir ou ocultar linhas de grade

Clique no gráfico desejado. Na guia Layout, no grupo Eixos, clique em Linhas de Grade.

Para adicionar linhas de grade horizontais ao gráfico, aponte para Linhas de Grade Horizontais Principais e clique na opção desejada. Se o gráfico tiver um eixo horizontal secundário, também pode-se clicar em Linhas de Grade Horizontais Secundárias. Para adicionar linhas de grade verticais ao gráfico, aponte para Linhas de Grade Verticais Principais e clique na opção desejada. Se o gráfico tiver um eixo vertical secundário, também pode-se clicar em Linhas de Grade Verticais Secundárias. Para adicionar linhas de grade de profundidade em um gráfico 3D, aponte para Linhas de Grade de Profundidade e clique na opção desejada. Essa opção está disponível somente quando o gráfico selecionado é um gráfico 3D real, como um gráfico de colunas 3D. Para ocultar as linhas de grade do gráfico, aponte para Linhas de Grade Horizontais Principais, Linhas de Grade Verticais Principais ou Linhas de Grade de Profundidade (em um gráfico 3D), e clique em Nenhum. Se o gráfico tiver eixos secundários, você também poderá clicar em Linhas de Grade Horizontais Secundárias ou Linhas de Grade Verticais Secundárias e clicar em Nenhum. Para remover rapidamente as linhas de grade do gráfico, selecione-as e pressione DELETE.

22.7 Mover ou Redimensionar um Gráfico É possível mover um gráfico para qualquer local em uma planilha ou para uma planilha nova ou existente. Também é possível alterar o tamanho do gráfico para ter um melhor ajuste.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 224

Redimensionar um gráfico

Para redimensionar um gráfico, clique no gráfico e arraste as alças de dimensionamento até o tamanho desejado; ou, na guia Formato, no grupo Tamanho, digite o tamanho nas caixas Altura da Forma e Largura da Forma.

Para ver mais opções de dimensionamento, na guia Formato, no grupo Tamanho, clique no Iniciador da Caixa de Diálogo

. Na caixa de diálogo Tamanho e

Propriedades, na guia Tamanho, é possível selecionar opções para dimensionar, girar ou escalar o gráfico. Na guia Propriedades, é possível especificar como deseja mover ou dimensionar esse gráfico com as células na planilha. Para mover um gráfico, clique sobre ele e arraste-o até o local desejado.

22.8 Salvar e aplicar modelos de gráficos Salvar um gráfico como modelo de gráfico

Para criar outro gráfico baseado no modelo já criado, salve o gráfico como um modelo que possa ser usado como base para outros gráficos semelhantes. Clique no gráfico que deseja salvar como um modelo. Na guia Design, no grupo Tipo, clique em Salvar como Modelo. Na caixa Nome do arquivo, digite um nome para o modelo.

Aplicar um modelo de gráfico

Para criar um novo gráfico com base no modelo, na guia Inserir, no grupo Gráficos, clique em qualquer tipo de gráfico e, em seguida, clique em Todos os Tipos de Gráfico.

Para que um gráfico selecionado corresponda a um modelo, na guia Design no Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 225

grupo Tipo clique em Alterar Tipo de Gráfico.

Clique em Modelos, na primeira caixa e, em seguida, clique no modelo que deseja usar na segunda caixa em Meus Modelos.

Remover ou excluir um modelo de gráfico

Na guia Inserir, no grupo Gráficos, clique em qualquer tipo de gráfico e, em seguida, clique em Todos os Tipos de Gráfico.

Clique em Gerenciar Modelos. Para remover o modelo de gráfico da pasta Gráficos, arraste-o para a pasta onde deseja armazená-lo. Para excluir o modelo de gráfico do computador, clique nele com o botão direito do mouse e, em seguida, clique em Excluir.

22.9 Opções avançadas de gráficos Dependendo do tipo de gráfico utilizado, é possível adicionar as seguintes linhas ou barras: 

Linhas de série: Essas linhas conectam a série de dados em gráficos de

colunas e barras empilhadas 2D para enfatizar a diferença na medida entre cada série de dados. Por padrão, os gráficos de pizza de pizza ou barra de pizza exibem linhas de série para conectar o gráfico de pizza principal ao gráfico de pizza ou barra secundário. 

Linhas de projeção Disponíveis em gráficos de linhas e de área 2D e 3D, essas

linhas se estendem dos pontos de dados para o eixo horizontal (categoria), a fim de esclarecer onde um marcador de dados termina e o próximo começa. Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO



Página | 226

Linhas de máximo/mínimo Disponíveis em gráficos de linhas 2D e exibidas,

por padrão, em gráficos de ações, as linhas de máximo/mínimo se estendem do valor mais alto para o valor mais baixo em cada categoria. 

Barras superiores/inferiores Úteis em gráficos de linhas com várias séries de

dados, as barras superiores/inferiores indicam a diferença entre os pontos de dados da primeira série de dados e da última série de dados.

Adicionar ou remover linhas ou barras

Clique no gráfico ao qual deseja adicionar linhas ou barras. Na guia Layout, no grupo Análise, clique em Linhas e, em seguida, clique na opção de tipo de linha desejada. Clique em Barras Superiores/Inferiores e, em seguida, clique em Barras Superiores/Inferiores. Clique em Linhas ou Barras Superiores/Inferiores e, em seguida, clique em Nenhum para remover linhas ou barras de um gráfico.

22.10 Gráfico de combinação Para enfatizar os diferentes tipos de informações em um gráfico, é possível combinar dois ou mais tipos de gráfico. Por exemplo, é possível combinar um gráfico de colunas com um gráfico de linhas para obter um efeito visual instantâneo que possa facilitar a compreensão do gráfico. Quando o intervalo de valores de diversas séries de dados no gráfico variar muito ou quando houver tipos mistos de dados, será possível plotar uma ou mais séries de dados de outro tipo de gráfico em um eixo vertical (valor) secundário.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 227

Criar um gráfico de combinação

O procedimento a seguir ajudará a criar um gráfico de combinação com resultados semelhantes aos mostrados neste exemplo. Copie os dados de exemplo da planilha abaixo:

A

B

C

Imóveis Vendidos Preço Médio Jan

280

410

Fev

150

450

Mar

220

430

Abr

275

425

Mai

155

410

Jun

255

400

Selecione os dados da planilha. Na guia Inserir, no grupo Gráficos, clique em Coluna.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 228

Em Coluna 2D, clique em Colunas Agrupadas. No gráfico, clique na série de dados. Cada série de dados em um gráfico tem uma cor ou um padrão exclusivo e é representada na legenda do gráfico. O recurso Ferramentas de Gráfico será exibido, com as guias Design, Layout e Formatar. Na guia Design, no grupo Tipo, clique em Alterar Tipo de Gráfico.

Em Linha, clique em Linha com Marcadores e, em seguida, clique em OK. No gráfico, clique na linha que representa o Preço Médio para selecionar a série de dados ou selecione-a em uma lista de elementos de gráfico (guia Layout, grupo Seleção Atual, caixa Elementos do Gráfico). Na guia Layout, no grupo Seleção Atual, clique em Formatar Seleção.

Na categoria Opções de Série, em Plotar Série no, clique em Eixo Secundário e, em seguida, clique em Fechar.

Clique na área do gráfico. Na guia Design, no grupo Estilos de Gráfico, clique no Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 229

estilo de gráfico a ser usado.

Para alterar o tamanho do gráfico: na guia Formatar, no grupo Tamanho, selecione o tamanho da forma desejado nas caixas Altura da Forma e Largura da Forma, e pressione ENTER.

Para adicionar, formatar e posicionar um título no gráfico: Na guia Layout, no grupo Rótulos, clique em Título do Gráfico e, em seguida, clique em Acima do Gráfico.

No gráfico, clique no título e digite o texto desejado.

Para reduzir o tamanho do título do gráfico: clique com o botão direito do mouse no título e digite o tamanho desejado na caixa Tamanho no menu de atalho.

Para mover a legenda: Clique na legenda para selecioná-la. Na guia Layout, no grupo Rótulos, clique em Legenda e, em seguida, clique na posição desejada.

Para adicionar títulos de eixo vertical: Na guia Layout, no grupo Rótulos, clique em Títulos dos Eixos. Clique em Título do Eixo Vertical Principal e, em seguida, clique na opção de título desejada. Clique em Título do Eixo Vertical Secundário e, em seguida, clique na opção de título desejada. Clique em cada título de eixo e digite o texto desejado para o título.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 230

Para alterar o tamanho da fonte dos títulos de eixo: clique em cada título de eixo e, em seguida clique no tamanho desejado na caixa Tamanho da Fonte.

Para alterar a aparência dos marcadores exibidos na linha Preço Médio: Clique com o botão direito do mouse em um marcador e, em seguida, clique em Formatar Série de Dados no menu de atalho. Clique em Opções de Marcador e, em Tipo de Marcador, clique em Interno. Na caixa Tipo, clique no tipo de marcador que deseja utilizar.

Para alterar o estilo do marcador: Clique na área do gráfico. Na guia Formatar, no grupo Estilos de Forma, clique no botão Mais

e, em seguida, clique no efeito

desejado.

Para usar diferentes temas: Na guia Layout da Página, no grupo Temas, clique em Temas. Em Interno, clique no tema desejado.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 231

22.11 Imprimir um gráfico em uma planilha Clique na planilha que contém o gráfico que deseja imprimir. Na guia Exibir, no grupo Modos de Exibição de Pasta de Trabalho, clique em Layout da Página ou Visualizar Quebra de Página.

Para mover o gráfico, arraste-o para um local de preferência na página a ser impressa.

Para redimensionar o gráfico: Clique no gráfico e arraste as alças de dimensionamento até o tamanho desejado, ou, na guia Formato, no grupo Tamanho, digite o tamanho nas caixas Altura da Forma e Largura da Forma.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO Clique na planilha. Clique no Botão Microsoft Office

Página | 232 e, em seguida, clique em

Imprimir.

OBS.: É possível alterar as configurações de impressão de página. Os procedimentos e opções são os mesmos para imprimir uma planilha comum. Para ver como o gráfico será impresso, clique no Botão do Office, Imprimir e depois Visualizar Impressão. Para uma impressão mais rápida, altere a qualidade da impressão do gráfico para rascunho ou preto-e-branco.

Imprimir um gráfico sem dados de planilha Clique no gráfico a ser impresso. Clique no Botão Microsoft Office

e, em

seguida, clique em Imprimir. Em Imprimir, a opção Gráfico Selecionado estará selecionada. Alterar a qualidade de impressão de um gráfico

Clique no gráfico a ser impresso. Na guia Layout da Página, no grupo Configurar Página, clique no Iniciador de Caixa de Diálogo

Na guia Gráfico, em Qualidade da impressão, marque a caixa de seleção Qualidade rascunho, a caixa de seleção Imprimir em preto e branco ou ambas. Clique em Imprimir.

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 233

23 CONFIGURAÇÃO E IMPRESSÃO DE PÁGINA Para configurar como a planilha será impressa, clique na guia “Layout da página” e abra o grupo “Configurar Página. Será exibida a seguinte janela:

A janela que se abre possui 4 abas:

Aba Página Nesta aba o usuário pode-se configurar vários itens, sendo que os itens abaixo são os mais comuns: 

Orientação: as opções são Retrato (papel na vertical) e Paisagem (Papel na

horizontal); 

Dimensionar: ajuste para um porcentual do tamanho normal, ou ajuste para que a

planilha impressa caiba em tantas páginas de largura por tantas páginas de altura; 

Tamanho do Papel: A4, A5, Ofício, Carta, entre outros;



Qualidade da impressão: quanto maior a quantidade de pontos por polegada (ppp),

melhor a qualidade da impressão; 

Número da Primeira Página: numeração das páginas da planilha. Nesta aba ainda há

4 botões: Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 234



OK: executa a formatação conforme as seleções efetuadas pelo usuário;



Cancelar: cancela todas as alterações efetuadas nessa aba;



Imprimir: imprime a planilha na impressora, conforme ajustes anteriores e/ou

configuração padrão da impressora; 

Visualizar: permite que o usuário visualize a planilha do mesmo jeito que seria

impressa; 

Opções: permite alterar a configuração atual da impressora, para adaptar a

necessidade do momento.

Aba Margens Nesta aba o usuário pode alterar cada uma das margens pré-configuradas para deixar a planilha do jeito exato que se deseja ver impresso. Tanto as margens Inferior, Superior, Esquerda e Direita podem ser alteradas, quanto as distâncias do Cabeçalho e do Rodapé em relação às bordas da página; Também é possível centralizar a planilha, tanto na Horizontal quanto na Vertical, de modo a deixá-la com um aspecto mais profissional, evitando-se a concentração da parte escrita muito para um dos lados.

Aba Cabeçalho e Rodapé Aqui o usuário consegue personalizar tanto o Cabeçalho quanto o Rodapé. O ato de se selecionar um dos dois botões de personalização leva o usuário a uma segunda janela onde ele poderá definir para o cabeçalho, e/ou para o rodapé até 3 seções em cada uma: 

Seção Esquerda: alinha-se à esquerda da folha de impressão;



Seção Central: alinha-se centralizada em relação a folha de impressão;



Seção Direita: alinha-se à direita da folha de impressão;

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 235

Para cada uma dessas seções o usuário poderá fazer uma simples digitação de um texto, ou poderá aplicar uma determinada formatação da fonte, ou se utilizar da inserção de certos campos especiais de código, que permitirão automatizar certos processos, como formatar fonte, inserir número de página, data/hora de modificação da planilha, nome da guia ou até inserir uma imagem.

Aba Planilha Nesta aba o usuário poderá configurar aspectos gerais, relativos à área de impressão, que melhoram o modo como (e o que) será impresso: 

Área de Impressão: digitando-se $A$1:$X$123 (ou selecionando-se tal área através

do mouse) restringe-se a área de impressão à região indicada, não importando se outras células possuem ou não qualquer coisa registrado; 

Imprimir Títulos: selecionando-se um certo número de linhas a repetir no topo da

planilha, ou então um certo número de colunas a repetir à esquerda da planilha, consegue-se repeti-las por todas as páginas impressas, o que serve para repetição de títulos importantes em planilhas muito longas em qualquer uma dessas direções; 

Imprimir Detalhes: é possível selecionar quais itens da planilha serão impressos;



Ordem da Página: permite que se imprimam as folhas na seqüência "de cima para

baixo e da esquerda para a direita" ou então na seqüência "da esquerda para a direita e de cima para baixo".

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 236

Sidney Verginio da Silva [email protected]

EXCEL AVANÇADO

Página | 237

FINALIZANDO

Caro aluno,

Chegamos ao final de nosso Guia de Excel Avançado. Como você pode perceber, o conteúdo é bem vasto e rico. Embora o Excel ainda tenha diversas ferramentas, esse conteúdo certamente lhe ajudará a potencializar o uso dessa poderosa ferramenta, aumentando ainda mais a produtividade do seu dia-a-dia. Espero que tenha gostado de tudo que foi abordado neste material. E não fique somente nisso: a internet possui inúmeros vídeos e conteúdos que podem lhe auxiliar sobre como explorar ainda mais todos os recursos do Excel. Desejo-lhe muito sucesso em sua vida. Grande abraços obrigado!

Prof. Sidney Verginio da Silva

Sidney Verginio da Silva [email protected]