Apostila - Excel Avançado

EXCEL APLICADO À

GESTÃO EMPRESARIAL

Com inúmeros exemplos em Auditoria,

Recursos Humanos, Logística, Operações, Marketing e

Finanças

Adriano Leal Bruni

[email protected]

Salvador – BA

2002

S

UMÁRIO

1 INTRODUÇÃO AO EXCEL... 5

1.1 OBJETIVOS DO CAPÍTULO... 5

1.2 O MICROSOFT EXCEL E SUAS CONFIGURAÇÕES PRELIMINARES... 5

1.3 USOS DO EXCEL EM GESTÃO EMPRESARIAL... 6

1.4 COMPLEMENTOS IMPORTANTES E NECESSÁRIOS... 7

1.5 APLICAÇÕES GENÉRICAS DO EXCEL EM GESTÃO EMPRESARIAL... 8

2 OPERAÇÕES COM RECURSOS BÁSICOS ... 10

2.1 OBJETIVOS DO CAPÍTULO... 10

2.2 CÉLULAS, LINHAS E COLUNAS... 10

2.3 PRINCIPAIS COMPONENTES DO EXCEL... 13

3 PRINCIPAIS MENUS E SUBMENUS DO EXCEL ... 16

3.1 OBJETIVOS DO CAPÍTULO... 16 3.2 MENUS... 16 3.3 MENU ARQUIVO... 16 3.4 MENU EDITAR... 20 3.5 MENU EXIBIR... 26 3.6 MENU INSERIR... 27 3.7 MENU FORMATAR... 27 3.8 MENU FERRAMENTAS... 28 3.9 MENU DADOS... 28 3.10 MENU JANELA... 29 3.11 MENU AJUDA... 29

3.12 SIMPLIFICANDO O USO DOS MENUS ATRAVÉS DOS ATALHOS... 30

4 OPERAÇÕES COM FÓRMULAS SIMPLES ... 35

4.1 OBJETIVOS DO CAPÍTULO... 35

4.2 O QUE SÃO FÓRMULAS... 35

4.3 SINTAXE DA FÓRMULA... 36

4.4 CÓPIAS COM REFERÊNCIAS RELATIVAS VERSUS ABSOLUTAS... 38

4.5 EXERCÍCIOS DO CAPÍTULO... 40

5 FUNÇÕES DE PLANILHA ... 43

5.1 OBJETIVOS DO CAPÍTULO... 43

5.2 O QUE SÃO FUNÇÕES DE PLANILHA... 43

5.3 USO DE FUNÇÕES... 44

5.4 AS DIFERENTES CATEGORIAS DE FUNÇÕES... 45

5.4.1 Funções financeiras... 45

5.4.2 Funções de data e hora ... 50

5.4.3 Funções matemáticas e trigonométricas ... 58

5.4.4 Funções estatísticas... 68

5.4.5 Funções de pesquisa e referência... 96

5.4.6 Funções de gerenciamento de listas e bancos de dados... 109

5.4.7 Funções de texto ... 111

5.4.8 Funções lógicas ... 122

5.4.9 Funções de informação ... 125

5.4.10 Funções de engenharia... 131

5.4.11 Funções matriciais ... 132

5.4.12 Problemas de valores de erro e fórmulas... 132

5.5 EXERCÍCIOS DO CAPÍTULO... 134

6.1 OBJETIVOS DO CAPÍTULO... 136

6.2 INSERINDO GRÁFICOS NO EXCEL... 136

6.3 CURIOSIDADE : CONSTRUINDO PICTOGRAMAS NO EXCEL... 140

6.4 EXERCÍCIOS DO CAPÍTULO... 142

7 OPÇÕES DO MENU DADOS... 143

7.1 OBJETIVOS DO CAPÍTULO... 143 7.1.1 Opção Classificar ... 145 7.1.2 Opção Filtrar... 147 7.1.3 Opção Formulário... 148 7.1.4 Opção Subtotais... 148 7.1.5 Opção Validação ... 149 7.1.6 Opção Tabela ... 152

7.1.7 Opção Relatório da tabela dinâmica... 153

7.2 EXERCÍCIOS DO CAPÍTULO... 160

8 SUPLEMENTOS... 161

8.1 OBJETIVOS DO CAPÍTULO... 161

8.2 FERRAMENTAS DE ANÁLISE... 161

8.2.1 Ferramenta Estatística Descritiva... 161

8.2.2 Ferramenta Análise de Regressão... 164

8.2.3 Solver... 171

8.3 EXERCÍCIOS DO CAPÍTULO... 180

Versão de 4/12/2002 18:44:18. O AUTOR

Adriano Leal Bruni. Doutor em Administração (FEA/USP : Finanças, Globalização de Mercados Financeiros), Mestre em Administração (FEA/USP : Finanças, Teoria de Investimentos e Precificação de Risco em Mercados de Capitais), Pós-Graduado em Economia (FGV/EPGE : Finanças Empresariais), Administrador de Empresas. Autor de diversos trabalhos publicados ou apresentados no Brasil e no exterior, resultantes de pesquisas desenvolvidas em Mercados de Capitais, Derivativos e Risco de Crédito, Mercados Emergentes, Técnicas de Simulação Aplicadas às Finanças, Valuation, Teoria de Investimentos. Professor de cursos de pós-graduação ministrados em Brasília (USP), Belém, (UNAMA), Curitiba (FAE) e Salvador (ESPM, UFBA, UNIFACS, Faculdade Ruy Barbosa, Fundação Visconde de Cairú e Faculdade de Tecnologia Empresarial). Co-autor dos livros “Matemática Financeira com HP12C e Excel” e “Gestão de Custos e Formação de Preços”, ambos publicados e distribuídos nacionalmente pela Editora Atlas. Sócio-diretor da Infinita Consultoria, Treinamento e Editora Ltda. Home-Page: http://www.infinitaweb.com.br, e-mail:

albruni@ infinitaweb.com.br.

Observações importantes:

a) A leitura deste texto deve ser feita com os arquivos complementares disponíveis na minha home page (http://www.infinitaweb.com.br).

b) Versões atualizadas do texto estarão sendo gradualmente disponibilizadas.

c) Críticas, comentários e sugestões ao texto são sempre bem vindas. Favor enviá-las para albruni@ infinitaweb.com.br.

OUTRA IMPORTANTE FONTE DE INFORMAÇÕES

Para aprender sobre aplicações específicas do Excel em Matemática Financeira, leia o meu primeiro livro lançado em 2001, pela Editora Atlas.

1

I

NTRODUÇÃO AO

E

XCEL

“De invento em invento, por caminhos antes obscuros, atrevido se lança o pensamento.”

Javier de Burgos

1.1 O

No últimos anos, graças ao desenvolvimento da microinformática, muitas das técnicas quantitativas aplicáveis à gestão de custos e formação de preços tornaram-se mais simples e fáceis. Técnicas relativamente complexas ou trabalhosas, como as análises de regressão e correlação, puderam ser facilmente executadas com um simples clique de mouse.

Este capítulo tem o propósito de apresentar a planilha eletrônica Microsoft Excel alguns dos seus principais recursos aplicáveis ao processo de gestão empresarial. No decorrer do texto, diversas funções aplicáveis à administração são apresentadas e discutidas.

1.2 O

M

E

Desde que as planilhas eletrônicas se popularizaram, tornou-se muito mais fácil e simples fazer análises de investimentos, cálculos que envolvam a formação custos ou as análises de preços. Na prática, a modelagem de operações financeiras tornou indispensável o uso do Visicalc, Supercalc, Lotus 123, Quatro Pro e, mais recentemente, a planilha Microsoft Excel.

Para executar o Excel, tornando apto ao uso, basta selecionar a opção correspondente, após clicar na opção Iniciar -> Programas -> Microsoft Excel, conforme apresentado na Figura 1.

Figura 1. Como iniciar o Excel.

Outra opção para executar o Excel pode ser realizada através de um duplo clique sobre ícone de atalho, ilustrado na Figura 2.

Atalho para EXCEL.lnk

Figura 2. Atalho para o Excel.

Figura 3. A planilha Microsoft Excel.

1.3 U

E

G

E

No processo de gestão empresarial, o Excel pode ser utilizado em diversas áreas, dentre as quais podem ser destacadas as seguintes aplicações ou usos:

Na área administrativa: Folha de Pagamento, Salários, Contabilidade, Controle de Compras, Tabelas de Preços, Saldos etc.

Na área financeira: Análise de Investimentos, Custos, Projeção de Lucros, Fluxo de Caixa, Controle de Captação de Recursos, Controle de Contas a Pagar e a Receber, Simulação de Custos etc. Na área de produção: Controle de Produção, Controle de Produtividade, Controle de Estoque etc. Na área comercial: Plano de Vendas, Controle de Visitas, Análise de Mercado, Controle de Notas Fiscais, Emissão de Listagem de Preços etc.

No mercado existem inúmeros livros dedicados especificamente ao uso geral da planilha. Neste livro estão abordados alguns dos principais aspectos genéricos da planilha aplicáveis ao processo de tomada de decisões relativas ao processo de gestão empresarial, como as funções algébricas, editadas diretamente nas células, as funções financeiras, as ferramentas de análise da estatística e os recursos de otimização Atingir Meta e Solver.

Com o objetivo de facilitar a transmissão dos conteúdos e a visualização das aplicações desenvolvidas, este livro utiliza dados de diversos exemplos fictícios, apresentados no CD que acompanha o texto.

1.4 C

Para poder operar com todos os recursos que são ilustrados neste livro, é fundamental que o leitor confira a instalalação dos suplementos Ferramentas de Análise e Solver, antes de inserir fórmulas ou aplicar os recursos próprios para modelagem financeira. É importante destacar que a verificação da ativiação ou não dos suplementos Ferramentas de Análise e Solver deve ser feita antes da leitura das páginas seguintes.

Os suplementos Ferramentas de Análise e Solver podem ser instalados ou desinstalados através do menu Ferramentas -> Suplementos. Vide a figura seguinte.

Figura 4. Opções do menu Ferramentas.

Caso o Excel tenha sido instalado em sua versão completa, diversas opções de suplementos estarão disponíveis, conforme exibe a figura seguinte.

Figura 5. Ativação dos suplementos disponíveis.

É fundamental que, no momento de instalação do Excel no computador, seja selecionada a opção completa, que inclua os suplementos. Caso os suplementos Ferramentas de análise e Solver não estejam disponíveis, torna-se necessário a sua instalaçào completar. Nesta situação, sugere-se consulta ao manual do software.

Caso os suplementos estejam instalados e suas opções de ativação estejam disponibilizadas através da opção Suplementos, é necessário tornar disponível os suplementos Ferramentas de análise e Solver, ativando a opção Ferramentas de Análise (cuidado, existe uma outra opção de suplemento, denominada Ferramentas de Análise – VBA) e a opção Solver. Com os suplementos Ferramentas de Análise e Solver ativados, diversas funções e recursos mais elaborados passam a ser, também, disponibilizados pelo Excel.

1.5

A

E

O Excel é uma planilha eletrônica, ou seja, um software destinado especificamente a auxiliar processos que envolvam operações algébricas em microcomputadores. De um modo geral, pelo menos três grupos distintos de opções são úteis no processo de análise de custos e preços:

Recursos do Menu Dados : possibilitam diferentes tratamentos e análises de dados diversos.

Algumas das opções disponíveis consistem em :

Classificar : permite ordenar em forma crecente ou decrescente um conjunto de dados com

base em uma variável previamente selecionada;

Filtrar : permite selecionar os dados que serão exibidos com base em uma característica

predeterminada;

Formulário : facilita a digitação de dados no Excel, criando uma máscara para a entrada,

consulta ou alteração dos dados;

Subtotais : insere, automaticamente, subtotais na base de dados em análise, empregando

Validação : valida a digitação de determinadas informações em células previamente

formatadas.

Relatório de tabela dinâmica : facilita a síntese dos dados, permitindo a tabulação e o cálculo

de várias estatísticas. Deve ser acessado através do menu Dados -> Relatório da Tabela Dinâmica.

Funções de planilha : correspondem a recursos pré-elaborados e que simplificam a inserção de

fórmulas. Podem ser inseridas de diferentes maneiras : diretamente na célula; pela barra de edição, ou pelo menu Inserir -> Função. Vários grupos de funções de planilha estão disponibilizados pelo Excel, como, por exemplo, os grupos de funções financeiras, de data e hora, matemáticas e trigonométricas, estatísticas, procura e referência, banco de dados, texto, lógica, informações e engenharia.

Ferramenta Atingir Meta : consiste em um recurso aplicável a problemas que envolvam equações

algébricas. Com este recurso, pode-se, encontrar, por exemplo, quais devem ser as vendas de modo que o lucro total seja igual a valor predeterminado.

Suplemento Ferramentas de análise : deve ser usado com o objetivo de agilizar as etapas do

desenvolvimento de análises estatísticas ou de engenharia complexas. Fornecidos os dados e os parâmetros para cada análise; a ferramenta utiliza as funções automatizadas de macro1 de estatística ou engenharia adequadas e exibe os resultados em uma tabela de saída. Algumas ferramentas geram gráficos (como a ferramenta Histograma) além das tabelas de saída.

A lista das ferramentas de análise disponíveis pode ser vista através da opção Análise de dados no menu Ferramentas. Se o comando Análise de dados não estiver no menu Ferramentas, é necessário executar o Programa de Instalação para instalar o suplemento Ferramentas de Análise. Depois de instalar as Ferramentas de Análise, o suplemento Ferramentas de Análise deve ser selecionado no Gerenciador de Suplementos.

Suplemento Solver : como o próprio nome revela, o suplemento Solver corresponde a um eficiente

“solucionador” dos mais diferentes tipos de problemas algébricos. Seus usos e aplicações estão discutidos com maiores detalhes nas páginas seguintes.

É importante destacar que para facilitar a aplicação do conteúdo transmitido neste livro, foram elaboradas diversas planilhas, todas disponibilizadas no CD que acompanha o livro.

2

O

PERAÇÕES COM

R

ECURSOS

B

ÁSICOS

“Não há nada constante, exceto a mudança”.

Heráclito, filósofo grego, 600 AC

2.1 O

Este capítulo possui o objetivo de apresentar e conceituar os recursos mais simples do Excel.

2.2 C

, L

C

As planilhas do Excel correspondem à enormes tabelas, que podem ser utilizadas para efetuar as mais diversas operações matemáticas. As planilhas são agrupadas em pastas de trabalhos, que formam os aruivos mais usuais do Excel, quase sempre armazenados com a extensão .XLS.

Linha 5

Co

lu

na

B

Célula B5

Linha 5

Co

lu

na

B

Célula B5

Figura 6. Principais componentes da planilha: linhas, colunas e células.

Cada planilha de uma pasta de trabalho suporta 65.536 linhas numeradas (de 1 a 65.536) e 256 colunas referenciadas por letras (de A a IV). A intersecção entre uma linha e uma coluna é denominada célula. Cada célula corresponde a um “quadradinho” da tabela, conforme apresentado na Figura 6. O Excel suporta, portanto, 65.536 x 256 = 167.777.216 células por cada planilha. Uma pasta (ou um arquivo . XLS) pode conter um número ilimitado de planilhas.

No Excel cada célula é referenciada pela identificação de sua coluna e da sua linha. A célula B5, por exemplo, está localizada na coluna B, linha 5. Este tipo de referência costuma ser utilizado em todas as operações com o programa.

As opções de movimentação ao longo das células de uma planilha podem envolver o mouse ou o teclado. O mouse pode ser empregado para movimentar as barras de rolagem vertical e horizontal, clicando-se nas células desejadas.

Geralmente, usuários mais experientes optam pelo uso das teclas de movimentação através do teclado. A combinação de teclas como as setas e as teclas Home e End agilizam a movimentação dentro das planilhas do Excel. O quadro seguinte mostra as principais combinações de teclas e suas funções relacionadas com navegação entre células de planilhas no Excel.

Quadro 1. Opções para navegação em planilhas via teclado. Teclas

Função

Ou Tab Deslocar seletor 1 célula p/ a direita

Ou Shift+Tab deslocar seletor 1 célula p/a esquerda

↑ Deslocar seletor 1 célula p/cima

↓ Deslocar seletor 1 célula p/baixo

Ctrl+→ Deslocar seletor p/ a última célula preenchida à direita

Ctrl+← Deslocar seletor p/ a última célula preenchida à esquerda

Ctrl+↑ deslocar seletor p/a célula preenchida acima mais próxima da

posição atual

Ctrl+↓ deslocar seletor p/a célula preenchida abaixo mais próxima da

posição atual

Home Deslocar seletor p/ a coluna A

Ctrl + Home Deslocar seletor p/ a célula A1

Ctrl + End Deslocar seletor p/ a interseção da última coluna com a última linha

utilizada na planilha atual.

Page up Deslocar o seletor uma tela p/cima

Page Down Deslocar o seletor uma tela p/baixo

Em todos os programas voltados para o ambiente Windows, é fundamental saber selecionar elementos para posterior aplicação de recursos de formatação ou outros mais específicos para a obtenção dos resultados desejados. No caso do Excel, para dominar as técnicas de seleção de células, linhas e colunas que compõem as planilhas propriamente ditas, basta seguir os passos apresentados a seguir :

Seleção de células:

a) Para selecionar uma única célula basta dar um único clique sobre ela.

b) Para selecionar células em sequência (tanto na horizontal quanto na vertical) basta arrastar o mouse com o botão esquerdo pressionado ou utilizar a técnica da tecla Shift pressionada.

c) Para selecionar células aleatoriamente, basta dar um clique sobre a primeira célula desejada e, mantendo a tecla Ctrl pressionada, dar um clique sobre cada nova célula a ser selecionada. d) Para selecionar células aleatoriamente, basta dar um clique na primeira célula desejada e,

mantendo a tecla Ctrl pressionada, dar um clique sobre cada nova célula a ser selecionada. Seleção de linhas e colunas:

a) Para selecionar uma linha ou coluna inteira basta dar um clique em seu cabeçalho.

b) Para selecionar uma linha inteira utilizando apenas o teclado basta utilizar a combinação de teclas Shift+Espaço.

c) Para selecionar uma coluna inteira utilizando apenas o teclado basta utilizar a combinação de teclas Ctrl+Espaço.

d) Para selecionar toda a planilha basta dar um clique no botão Selecionar Tudo ou utilizar a combinação de teclas Ctrl+Shift+Espaço.

Outras formas de navegação podem ser empregadas na movimentação interna de regiões no Excel. Uma região corresponde a um grupo de células selecionado em uma planilha, para a aplicação de alguma operação que deve atuar sobre toda a seleção. O Quadro 2 mostra as principais combinações

Quadro 2. Teclas úteis em regiões selecionadas.

Teclas Função

Enter Desloca o seletor de células para a célula imediatamente inferior.

Shift + Enter Desloca o seletor de células para a célula imediatamente superior.

Tab Desloca o seletor de células para a célula localizada à direita.

Shift + Tab Desloca o seletor de células para a célula à esquerda.

Ctrl + . Movimenta o seletor de células entre os vértices da região

selecionada.

Ctrl + Enter Quando uma célula está em edição, copia o seu conteúdo para todas

as demais células da região selecionada.

Para navegar entre as diferentes planilhas da pasta de trabalho do Excel, basta usar as “abas” apresentadas na parte inferior do Excel, conforme ilustração da Figura 7. Os controles localizados na parte inferior da janela do Excel permitem que o usuário percorra as diversas planilhas existentes em uma pasta de trabalho.

A pasta é formada por

três planilhas. Para visualizar

o conteúdo da planilha, clique

na guia.

Figura 7. Planilhas componentes do Excel.

De forma similar à maioria dos elementos manipulados em programas voltados para o ambiente Windows, as guias de planilhas também precisam ser previamente selecionadas para só então tornar o usuário apto a executar as operações desejadas. Para selecionar uma guia basta dar um clique na sua identificação. Para selecionar várias guias em seqüência, dê um clique na primeira e, mantendo a tecla Shift pressionada, dê um clique na última. Para selecionar várias guias fora de ordem, mantenha a tecla Ctrl pressionada e vá dando cliques em cada uma das guias.

As denominações Plan1, Plan2... são, geralmente sugeridas pelo Excel como denominações inciais das planilhas. Porém, o usuário pode personalizar a denominação de cada guia para identificar de forma clara qual o conteúdo e finalidade de cada planilha. Para isso, basta dar um duplo clique sobre o nome que deseja alterar, digitando a nova denominação desejada.

Obviamente, O Excel não está limitado a apenas três planilhas. Sempre que o usuário julgar necessário, poderá acrescentar novas planilhas às suas pastas de trabalho. Basta dar um clique em uma das guias na parte inferior da janela e ativar a opção Planilha do menu Inserir. Uma nova guia será inserida imediatamente à esquerda da guia selecionada.

Porém, em outras situações, o usuário poderá achar conveniente remover uma ou mais planilhas de uma pasta de trabalho. Para isso, basta selecionar as guias correspondentes e ativar a opção Excluir planilha do menu Editar do Excel. Deve-se confirmar a operação quando o assistente do Office solicitar.

2.3 P

E

A tela principal da planilha Excel apresenta a aparência e componentes exibidos na figura seguinte.

Barras de rolagem Barra de status

Guia das pastas Botões de navegação entre pastas

Célula Ativa

Barra de edição Barra de título e Pasta em uso

Barra de ferramentas Barra de Menu

Figura 8. Principais componentes do Excel.

Os elementos destacados na figura anterior podem ser apresentados como:

a) Barra de Título: consiste em um menu comum a todos os programas desenvolvidos para o

ambiente operacional Windows. Consiste na área reservada para a identificação do programa e do arquivo que estiver sendo atualmente manipulado. Nesta barra estão presentes os botões Minimizar-Maximizar/Restaurar-Fechar, também comuns a todos os programas Windows. Suas funções são minimizar, maximizar, restaurar ou fechar a janela do programa, respectivamente.

Figura 9. Barra de título e menu.

b) Barra de Menus: Corresponde à área reservada para os menus que contém as opções do

programa para manipulação de documentos e demais recursos. Cada um dos principais menus do Excel está descrito com maior profundidade a seguir.

c) Barra de Ferramentas : corresponde à área que contém, na forma de botões, atalhos para

algumas das principais opções presentes na Barra de Menus do programa. As barras podem estar encaixadas, delimitadas a um espaço na tela da planilha – geralmente na parte superior da tela, embora, eventualmente, possa também estar na parte inferior da tela.

Figura 10. Barras de ferramenta Padrão e Formatação encaixadas.

Geralmente, as duas barras de ferramenta mais usuais do Excel são as barras de ferramentas Padrão e Formatação.

Barra de Ferramentas Padrão: Área que contém botões atalhos geralmente relacionados com as operações mais usuais com arquivos do Excel, como Novo, Abrir, Salvar e outros.

Figura 11. Barra de ferramenta Padrão flutuante.

Barra de Ferramentas Formatação: Área que contém, também na forma de botões e caixas de listagem, versões simplificadas para muitas opções disponíveis nos menus do programa relacionadas com a formatação de documentos.

Figura 12. Barra de ferramenta Formatação flutuante.

Outras barras de ferramenta comumente disponibilizadas pelo e que serão apresentadas com mais detalhes no decorrer deste livro são ######.

d) Barra de edição: e) Barra de status:

Figura 13. Barra de status.

Além das barras mencionadas, outros dos principais componentes do Excel são:

f) Cabeçalhos de Linhas e Colunas: no Excel as linhas são identificadas por números e as colunas

por letras, em seus respectivos cabeçalhos.

g) Seletor de Células: corresponde à barra de inserção do Word 2000. O seletor de células indica

qual célula está ativa.

i) Guias de Planilhas: Permitem que o usuário tenha várias planilhas em uma mesma pasta de

trabalho do Excel. Através desta guia pode-se trocar a planilha ativa.

j) Assistente do Office: Representa o sistema de ajuda on-line do Excel 2000. Consulte o Capítulo 1

para maiores informações sobre o seu funcionamento.

Além dos principais componentes do Excel, descritos anteriormente, um outro importante componente das telas de aplicativos do Windows consiste no Menu de Controle, que corresponde a um menu comum a todos os programas desenvolvidos para o ambiente operacional Windows, com opções relacionadas à movimentação, redimensionamento e fechamento da janela do programa.

3

P

RINCIPAIS

M

ENUS E SUBMENUS DO

E

XCEL

“Todas as minhas experiências me provaram que não existe outro deus a não ser a verdade”.

Gandhi

3.1 O

Este capítulo possui o objetivo de apresentar os principais menus do Excel, destacando as suas opções mais básicas.

3.2 M

Todas as operações realizadas pelo Excel estão acessíveis através dos Menus e Submenus ou através dos atalhos – colocados através dos botões das barras de ferramentas ou de atalhos de teclado.

Figura 14. Barra de Menus do Excel.

A seguir estão apresentadas as principais opções dos Menus do Excel.

3.3 M

A

A primeira opção de menus no Excel consiste no Menu Arquivo, que, como o próprio nome revela, possibita executar as operações mais comuns com os arquivos trabalhados no Excel.

As principais opções do Menu Arquivo consistem em :

Novo : possilita criar um novo arquivo no Excel. Dois grande grupos de opções estão disponíveis: os

modelos gerais, dentre os quais se detaca o modelo Pasta de Trabalho, mais utilizado no Excel, e as soluções de planilha, que apresentam modelos prontos da Microsoft com exemplos de Declarações de Despesas, Fatura e Pedido de Compra, conforme ilustrado na figura seguinte.

Figura 16. Arquivo novo – soluções de planilha.

Abrir : opção que possibilita acessar um aquivo salvo anteriormente. Para isso é preciso especificar o

Figura 17. Opção abrir arquivo do Excel.

Fechar: opção que possibilita encerrar o arquivo em uso. Caso diversos arquivos estejam abertos,

esta opção permite fechá-los um a um.

Salvar: opção que permite salvar o arquivo em uso.

Salvar como: alternativa que possibilita alterar as configurações de salvamento, como o nome, o

Salvar como Página da Web: alternativa mais elaborada, que permite salvar a planilha em formato

HTML, tornando mais fácil a sua posterior publicação eletrônica na internet.

Salvar Área de Trabalho: possibilita salvar informações armazenadas na área de trabalho do

computador, como áreas copiadas. Esta opção facilita o uso posterior com outros aplicativos do Windows.

Visualização de Página da Web: possibilita visualizar o arquivo em layout HTML, antes de ser salvo.

Possibilitando ajustes preliminares.

Configurar página: permite configurar a página, possibilitando ajustes preliminares de impressão. Visualizar Área de Impressão: possibilita configurar e visualizar o conteúdo selecionado para

impressão, antes da impressão propriamente dita.

Visualizar Impressão: permite verificar como será o resultado da impressão, possibilitando configurar

inúmeras opções de impressão.

Imprimir: descarrega o área de impressão previamente selecionada na impressora. Deve ser

empregado apenas após a área de impressão ter sido selecionada e as configurações prelimimares deimpresão terem sido ajustadas.

Propriedades: exibe as propriedades do aquivo. Embora, na prática, seja uma opção muito pouco

utilizada, torna-se um recurso útil para profissionais que trabalham rotineiramente com o Excel, já que inúmeras características do arquivo podem ser incluídas ou alteradas.

Lista dos últimos arquivos trabalhados: apresenta a relação dos últimos arquivos trabalhados no

Excel, facilitando a abertura e carregamento dos últimos trabalhos executados.

3.4 M

E

O menu Editar apresenta algumas opções relativas às operações rotineiras com a planilha.

Figura 18. Opções do Menu Editar.

Desfazer: possibilita dezfazer a última ação realizada no Ecel, corrigindo eventuais erros de

execução.

Repetir: no caso de tarefas que necessitem de repetições, este recurso possibilita realizar novamente

a útlima atividade excutada no Excel.

Recortar: permite recortar uma área de texto ou de planilha no Excel, movendo-a para um novo

destino.

Copiar: possibilita copiar uma determinada área de texto ou de planilha do Excel. Colar: duplica a área copiada ou move a área recortada para o novo destino.

Colar Especial: possibilita configurar melhor os recursos de colagem. Diversas opções de colar

especial estão disponíveis, como as que permitem diferentes configurações para a colagem de objetos - conforme apresentado na Figura 19 – ou diversas configurações para a colagem de áreas da planilha – segundo ilustração da Figura 20.

Figura 19. Opções de colar especial objeto.

A Figura 19 algumas opções disponíveis para a colagem diferenciada de objetos. Quando copiados do Word, por exemplo, os objetos podem ser colados como objeto do próprio editor de textos, figura, HTML, texto ou hyperlink.

Figura 20. Opções de colar especial área de planilha.

Quando a área selecionada para cópia provém de uma planilha, outros recursos de colar especial tornam-se disponíveis, conforme apresentado na Figura 20.

Colar como hiperlynk: através de recurso pode-se colar o que se deseja como um hyperlink, o que

facilita a navegação posterior entre os diferentes documentos. Por exemplo caso se deseje criar uma alternativa de navegação entre uma parte de um documento do Word denominada Planilhas Orçamentárias e os respectivos cálculos no Excel, deve-se selecionar o texto no documento de origem, posteriormente colando-o na planilha com a opção colar como hyperlink. Ao clicar sobre o link,

o usuário será remetido ditretamente para o arquivo no Word, onde se encontra o texto Planilhas Orçamentárias.

Figura 21. Colar como hyperlink.

Preencher: possibilita preencher células vazias de uma planilha. As opções de preenchimento no

Excel são as mais variadas possíveis.

Figura 22. Opções do menu Preencher.

Para facilitar a compreenção dos recursos disponíveis no menu preencher, veja o exemplo de uma relação de cidades, fornecido na figura seguinte.

B C D

2 Estado Cidade Ordem

3 BA Salvador 4 Feira de Santana 5 Ilhéus 6 SP São Paulo 7 São Bernardo 8 Diadema

Figura 23. Exemplo de relação de cidades.

Por exemplo, em relação à planilha apresentada na figura anterior, o usuário pode desejar completar a coluna de estados. Para isso, bastaria selecionar as áreas que individuais que deseja preeencher com BA e, posteriormente, com SP, ativando a opção preencher para baixo no menu.

Figura 24. Opções de preencher sequência.

Outra importante opção do menu consiste no recurso preencher sequência. Pode-se criar uma sequência de números, de diferentes formas. Por exemplo, caso se deseja preencher a ordem das cidades, iniciando em 1 e com tendência unitária (ordens iguais a 1, 2, 3, ....) basta configurar o recurso conforme exibido na Figura 24.

Limpar: consiste em opção que possibilita limpar as diferentes condições atribuídas a um intervalo de

célula. Pode-se limpar tudo – o que inclui formatos, contéudos e comentários – ou apenas um dos elementos. Pode-se limpar, por exemplo, apenas a formatação de um intervalo previamente selecionado de células.

Figura 25. Opções do menu limpar.

Excluir: permite diferentes opções de exclusão na planilha trabalhada do Excel, a exemplo de células

Figura 26. Opções do menu Excluir.

Excluir planilha: possibilita excluir uma planilha previamente selecionada. Diversas planilhas podem

ser marcadas para exclusão mediante o uso da tecla Control. Mantendo-a pressionada, seleciona-se o que se deseja excluir através de cliques nas guias das planilha. Posteriormente, pde-se a exclusão das plainhas selecionadas.

Por precaução, o Excel ainda exige uma confirmação do uso do recurso, conforme apresentado na figura seguinte.

Figura 27. Confirmação da exclusão de planilhas selecionadas.

Mover ou copiar planilha: permite que a planilha ativa ou selecionada seja movida ou copiada. Outra

forma mais fácil para duplicar ou mover planilhas consiste em clicar e arrastas as guias das planilhas. Caso a operação seja feita com a tecla Control pressionada, a planilha será duplicada.

Figura 28. Opções para mover ou copiar planilha. Localizar: permite localizar um conteúdo dentro da planilha trabalahada.

Figura 29. Opções do menu localizar.

Substituir: similar ao recurso localizar, permite encontrar um conteúdo desejado e, caso desejado,

substituir o seu valor por um outro.

Ir para: facilita a navegação interna na planilha. A opção pode ser acessada através da tecla F5.

Quando diferentes áreas são nomedas na planilha, o recurso agiliza a movimentação interna.

Figura 31. Opção Ir Para.

Vínculos: permite gerenciar, atualizando caso necessário, os diferentes vínculos existentes na

planilha.

Figura 32. Verificando vínculos da planilha ativa. Objeto:

Figura 33. Opções do Menu Exibir.

3.6 M

I

Figura 34. Opções do Menu Inserir.

Figura 35. Opções do Menu Formatar.

3.8 M

F

Figura 36. Opções do Menu Ferramentas.

Figura 37. Opções do Menu Dados.

3.10 M

J

Figura 38. Opções do Menu Janela.

Figura 39. Opções do Menu Ajuda.

3.12 S

Para facilitar a operação nos menus da planilha, existem diversas opções simplificadas para a execução de determinados procedimentos no Excel. Além dos botões apresentados na Barra de Ferramentas, existem recursos úteis presentes nos atalhos de teclado. Vide alguns dos principais atalhos apresentados no Quadro 3.

Quadro 3. Teclas de atalho do Excel.

Teclas de Atalho Ação Menu Equivalente

= (Sinal de Igual) Iniciar fórmula

Alt or F10 Ativa Menu

Alt+ Enter Inicia uma nova linha na mesma célula

Alt+= AutoSoma

Alt+F1 Inserir Gráfico Inserir, Gráfico...

Alt+F11 Editor Visual Basic Ferramentas, Macro, Visual Basic

Editaror

Alt+F2 Salvar Como Arquivo, Salvar Como

Alt+F4 Exit Arquivo, Exit

Alt+F8 Caixa de Dialogo de Macros Ferramentas, Macro, Macros

Alt+PgDn Mover uma tela à direita

Alt+PgUp Mover uma tela à esquerda

Alt+; (ponto e vírgula) Selecione somente as células visíveis na seleção atual

Alt+Shift+F1 Nova Planilha Inserir, Planilha

Alt+Shift+F2 Salvar Arquivo, Salvar

Backspace Excluir o caractere à esquerda do

ponto de inserção, ou excluir a seleção Ctrl ou Shift+Barra de

Espaços

Selecionar a coluna ou linha inteira

Ctrl+- Excluir Deletar, (Linhas,Colunas ou

Células) Depende da seleção.

Ctrl+: Inserir Hora Atual

Ctrl+[ Selecionar apenas as células a que são feitas referências diretas por fórmulas

Ctrl+] Selecionar somente as células

contendo fórmulas que se referem diretamente à célula

Ctrl++ Inserir Editar, Desfazer

Ctrl++ Inserir Inserir, (Linhas,Colunas ou

Células) Depende da seleção.

Ctrl+1 Formatar Células Formatar, Células

Ctrl+2 Negrito Formatar, Células, Fonte, Fonte

Estilo, Negrito

Ctrl+3 Itálico Formatar, Células, Fonte, Fonte

Estilo, Itálico

Ctrl+4 Sublinhado Formatar, Células, Fonte, Fonte

Estilo, Sublinhado

Ctrl+5 Tachado Formatar, Células, Fonte, Efeitos,

Tachado

Ctrl+6 Exibir/ Ocultar Objetos Ferramentas, Opções, Exibir,

Objects

Ctrl+7 Exibir/Ocultar Barra Ferramentas

Padrão

Exibir, Barra de Ferramentas ,Padrão

Ctrl+8 Mostrar Simbolos Tópicos

Ctrl+9 (ou 0) Ocultar Linhas Formatar, Linhas, Ocultar

Ctrl+A Abrir Arquivo, Abrir

Ctrl+B Salvar Arquivo, Salvar

Ctrl+C Copiar Editar, Copiar

Ctrl+D Preencher abaixo Editar, Preencher, Abaixo

Ctrl+Delete Excluir o texto ao final da linha

Ctrl+End ou End, Home

Mover até a última célula da planilha

Ctrl+F Copia Formula Abaixo $ Editar, Copiar, Colar

Ctrl+F10 Maximizar e restaurar janela XL, Maximize

Ctrl+F11 Inserir Planilha 4.0 Macro

Ctrl+F12 Arquivo Abrir Arquivo, Abrir

Ctrl+F3 Definir Nome Inserir, Nomes, Define

Ctrl+F4 Fechar Arquivo, Fechar

Ctrl+F5 Restaurar Janela ativa.

Ctrl+F6 (ou Tab) Próxima Pasta de Trabalho Janela, ...

Ctrl+F7 Mover Janela XL, Mover

Ctrl+F8 Redimensionar Janela

Ctrl+F9 Minimizar Pasta de Trabalho XL, Minimizar

Ctrl+H Copia Celula Acima e Cola Valor

Ctrl+I Itálico Formatar, Células, Fonte, Fonte

Estilo, Itálico

Ctrl+K Inserir Hyperlink Inserir, Hyperlink

Ctrl+L Localizar Editar, Localizar

Ctrl+N Negrito Formatar, Célula

Ctrl+O Nova Pasta de Trabalho Arquivo, Novo

Ctrl+O Nova Pasta de Trabalho Formatar, Colunas, Ocultar

Ctrl+P Imprimir Arquivo, Imprimir

Ctrl+PgDn Mover até a próxima planilha na pasta

de trabalho

Ctrl+PgUp Mover até a planilha anterior na pasta

Ctrl+R Preencher Direita Editar, Preencher Direita

Ctrl+S Sublinhar Arquivo, Salvar

Ctrl+Shift+! Formato 2 casas decimais Formatar, Células, Number,

Categoria, Number

Ctrl+Shift+# Formato Data Formatar, Células, Number,

Categoria, Data

Ctrl+Shift+$ Formato Moeda Formatar, Células, Number,

Categoria, Currency

Ctrl+Shift+% Formato Percentual Formatar, Células, Number,

Categoria, Percentual

Ctrl+Shift+& Borda externa na area selecionada Formatar, Células, Border

Ctrl+Shift+( Reexibir Linhas Formatar, Linhas, Reexibir

Ctrl+Shift+) Reexibir Colunas Formatar, Colunas, Reexibir

Ctrl+Shift+* Seleciona Região Atual Editar, Ir Para, Especial, Região

Atual Ctrl+Shift+: (dois

pontos)

Inserir a hora

Ctrl+Shift+@ Formato Hora Formatar, Células, Number,

Categoria, Hora

Ctrl+Shift+^ Formato Exponencial Formatar, Células, Number,

Categoria,

Ctrl+Shift+_ Remove borda externa Formatar, Células, Border

Ctrl+Shift+{ Selecionar todas as células a que é

feita referência por fórmulas na seleção

Ctrl+Shift+~ Formato Geral Formatar, Células, Number,

Categoria, General

Ctrl+Shift+A Inserir Nomes em argumentos das

formulas Ctrl+Shift+Barra de Espaços

Com um objeto selecionado, selecionar todos os objetos em uma planilha

Ctrl+Shift+End Estender a seleção até a última célula

usada na planilha (canto inferior direito)

Ctrl+Shift+Enter Inserir uma fórmula como fórmula de

matriz

Ctrl+Shift+F12 Imprimir Arquivo, Imprimir

Ctrl+Shift+F3 Criar Nomes usando Linhas e Colunas. Inserir, Nome, Create

Ctrl+Shift+F6 (ou Tab) Janela Anterior Janela, ...

Ctrl+Shift+Home Estender a seleção até o início da

planilha

Ctrl+Shift+O (a letra O) Selecionar todas as células contendo comentários

Ctrl+Tab Alterna entre Pastas de Trabalho

abertas Ctrl+Tab ou Ctrl+Shift+Tab

Selecionar a próxima barra de ferramentas ou a anterior

Ctrl+U Substituir Editar, Substituir

Ctrl+V Colar Editar, Colar

Ctrl+X Recortar Editar, Cortar

Ctrl+Y Ir Para Editar, Repetir

Ctrl+Z Desfazer Editar, Desfazer

End Mover até a célula no canto inferior

direito da janela End, Shift+ teclas de

direção

Estender a seleção até a última célula não-vazia na mesma coluna ou linha que a célula

End, tecla de direção Mover um bloco de dados dentro de uma linha ou coluna

Enter Finalizar comando. Completar uma

entrada de célula e mover para baixo na seleção

Esc Cancelar uma entrada na célula ou

barra de fórmulas

F1 Ajuda Ajuda, Ajuda do Excel

F10 Ativar barra Menu N/A

F11 Novo Gráfico Inserir, Gráfico

F12 Salvar Como Arquivo, Salvar Como

F2 Editar Célula

F3 Colar Nome Inserir, Nome, Colar

F4 Em Edição formulas alterna Ref.

absoluta/relativa.

F4 Repetir última ação Editar, Repetir. Quando não está

no modo de edição.

F5 Ir Para Editar, Ir Para

F7 Verificar Ortografia Ferramentas, Ortografia

F8 Modo Extend para selecionar células

F9 Racalcular todas as planilhas Ferramentas, Opções, Calcular,

Calc,Agora Home ou Ctrl+Home Mover até o início da linha/célula

SETA À ESQUERDA

ou SETA À DIREITA Rolar a tela/coluna para a esquerda ou para a direita uma coluna SETA ACIMA ou

SETA ABAIXO

Rolar a tela/linha para cima ou para baixo uma linha

Shift (5x) Ativa opções das teclas de aderência

Shift+ tecla de direção Estender a seleção em uma célula

Shift+Ctrl+F Font Drop Abaixo List Formatar, Células, Fonte

Shift+Ctrl+F+F Seleciona Fonte Formatar, Células, Fonte

Shift+Ctrl+F6 Alterna para Pasta Anterior Janela, ...

Shift+Ctrl+P Seleciona Tamanho da Fonte Formatar, Células, Fonte

Shift+End Estender a seleção até a célula no

canto inferior direito da janela

Shift+F1 O que é isso? Ajuda, O que é isso?

Shift+F10 Exibir Menu Editar

Shift+F11 Nova Planilha Inserir, Planilha

Shift+F12 Salvar Arquivo, Salvar

Shift+F2 Editar Célula Comentário Inserir, Editar Comentários

Shift+F3 Colar Função na formula Inserir, Função

Shift+F4 Localizar Próxima Editar, Localizar, Localizar

Próxima

Shift+F5 Localizar Editar, Localizar, Localizar

Próxima

Shift+F8 Adicionar à seleção

Shift+F9 Calcula Planilha Ativa Calc Plan

Shift+Home Estender a seleção até a célula no

canto superior esquerdo da janela

Shift+Home Estender a seleção até o início da linha

Shift+PgUp Estender a seleção para cima uma tela

Shift+Tab Celula Anterior

Shift+Tab Mover da direita para a esquerda

uma célula

Tab Completar uma entrada de célula e

Mover para a direita na seleção

Tab ou Shift+Tab Selecionar o próximo botão ou menu

na barra de ferramentas

Tecla de direção Mover uma célula/caractere/página em

4

O

PERAÇÕES

C

OM

F

ÓRMULAS

S

IMPLES

“Todo conhecimento vem da experiência”.

Immanuel Kant

4.1 O

Este capítulo possui o objetivo de apresentar algumas das mais simples operações do Excel, que envolvem fórmulas algébricas.

4.2 O

Uma fórmula é uma equação que analisa dados em uma planilha. As fórmulas efetuam operações, como adição, multiplicação e comparação em valores da planilha; além disso, podem combinar valores. As fórmulas podem referir-se a outras células na mesma planilha, a células em outras planilhas da mesma pasta de trabalho ou a células em planilhas em outras pastas de trabalho. O exemplo a seguir adiciona o valor da célula B4 e 25 e divide o resultado pela soma das células D5, E5 e F5.

Quadro 4. Exemplos de fórmulas no Excel. Descrição

Fórmula

Calcula o saldo parcial em um livro de registro. Neste exemplo, assume-se que a célula D7 contém o depósito de transação atual, a célula E7 contém qualquer valor de saque e a célula F6 contém o saldo anterior.

Para calcular o saldo atual da primeira transação (célula F7):=SOMA(F6;D7;–E7)Ao inserir novas transações, copie esta fórmula para a célula que contém o saldo atual da nova transação.

Agrupa o nome armazenado em uma célula com o sobrenome armazenado em outra célula. Neste exemplo, assuma que a célula D5 contém o nome e a célula E5 contém o sobrenome.

Para exibir o nome completo no formato

"nome sobrenome":=D5&" "&E5Para exibir o nome completo no formato

"nome, sobrenome":=E5&"; "&D5 Aumenta um valor numérico armazenado em uma

célula por uma percentagem, como 5 por cento. Neste exemplo, assuma que a célula F5 contém o valor original.

=F5*(1+5%)Se o valor da percentagem estiver armazenado em uma célula (por exemplo, célula F2)=F5*(1+$F$2)A referência à célula F2 é uma referência absoluta de célula de forma que a fórmula pode ser copiada para outras células sem alterar a referência à célula F2.

Cria um texto que agrupa uma data armazenada em uma célula com outro texto ¾ por exemplo, se a célula F5 contiver uma data de faturamento de 5-Jun-96, e você desejar exibir o texto "Data do demonstrativo: 5-Jun-96" na célula G50.

="Data do demonstrativo: "&TEXTO(F5; "d-mmm-aa")Observação Use a função TEXTO para formatar um valor de número, data ou hora como texto.

Cria um valor total para um intervalo com base em um valor em outro intervalo. Por exemplo, para cada célula no intervalo B5:B25 que contiver o valor "Northwind" você deseja calcular o total das

células correspondentes no intervalo F5:F25. Cria um valor total para um intervalo com base em duas condições. Por exemplo, você deseja calcular o valor total das células em F5:F25 onde B5:B25 contém "Northwind" e o intervalo C5:C25 contém a região denominada "Oeste".

=SOMA(SE(B5:B25="Northwind";

SE(C5:C25="Oeste";F5:F25)))Observação Esta é uma fórmula matricial e deve ser inserida

pressionando Ctrl+Shift+ENTER. Conta o número de ocorrências de um valor em

um intervalo de células ¾ por exemplo, o número de células no intervalo B5:B25 que contém o texto "Northwind".

=CONT.SE(B5:B25;"Northwind")

Conta o número de ocorrências de um valor em um intervalo de células, com base em um valor em outro intervalo ¾ por exemplo, o número de linhas no intervalo B5:B25 que contém o texto "Northwind" e o texto "Oeste" no intervalo C5:C25.

=SOMA(SE(B5:B25="Northwind";

SE(C5:C25="Oeste";1;0)))Observação:Esta é uma fórmula matricial e deve ser inserida pressionando Ctrl+Shift+ENTER.

As fórmulas calculam valores em uma ordem específica conhecida como sintaxe. A sintaxe da fórmula descreve o processo do cálculo. Uma fórmula no Microsoft Excel começa com um sinal de igual (=), seguido do cálculo da fórmula. Por exemplo, a fórmula a seguir subtrai 6 de 9. O resultado da fórmula é exibido na célula. [=9-6].

Uma fórmula pode referir-se a uma célula. Se você desejar que uma célula contenha o mesmo valor que outra célula, insira um sinal de igual seguido da referência da célula. A célula que contém a fórmula é denominada célula dependente. Logicamente, seu valor depende do valor de outra célula. Sempre que a célula à qual a fórmula fizer referência for alterada, a célula que contiver a fórmula também será alterada. A fórmula a seguir multiplica o valor na célula C23 por 40. A fórmula recalculará sempre que o valor na célula C23 for alterado. [=C23*40]

As fórmulas podem fazer referência a células ou intervalos de células, ou a nomes ou rótulos que representem as células ou intervalos.

O Microsoft Excel contém muitas fórmulas predefinidas ou internas conhecidas como funções. As funções podem ser usadas para efetuar cálculos simples ou complexos. A função mais comum em planilhas é a função SOMA, que é usada para adicionar intervalos de células. Embora você possa criar uma fórmula para calcular o valor total de algumas células que contêm valores, a função de planilha SOMA calculará diversos intervalos de células.

4.3 S

A sintaxe da fórmula é a estrutura ou ordem dos elementos em uma fórmula. As fórmulas no Microsoft Excel seguem uma sintaxe específica que inclui um sinal de igual (=) seguido dos elementos a serem calculados (os operandos) e dos operadores de cálculo. Cada operando pode ser um valor que não se altera (um valor constante), uma referência de célula ou intervalo, um rótulo, um nome ou uma função de planilha.

Por padrão, o Microsoft Excel calcula uma fórmula da esquerda para a direita, iniciando com o sinal de igual (=). Você pode controlar a maneira como os cálculos são efetuados, alterando a sintaxe da fórmula. Por exemplo, a fórmula a seguir fornece 11 como resultado, pois o Microsoft Excel calcula a multiplicação antes da adição. A fórmula multiplica 2 por 3 (tendo como resultado 6) e, em seguida, adiciona 5. [=5+2*3]. Por outro lado, se usar parênteses para alterar a sintaxe, você pode adicionar primeiro 5 e 2 e, em seguida, multiplicar este resultado por 3 para obter 21 como resultado. [=(5+2)*3]

Se o usuário combinar diversos operadores em uma única fórmula, o Microsoft Excel efetuará as operações na ordem mostrada no Quadro 5. Se uma fórmula contiver operadores com a mesma precedência, por exemplo, se uma fórmula contiver um operador de multiplicação e divisão, o Microsoft Excel avaliará os operadores da esquerda para a direita. Para alterar a ordem de avaliação, coloque a parte da fórmula a ser calculada primeiro entre parênteses. A ordem na qual o Microsoft Excel efetua operações em fórmulas está apresentada no quadro seguinte.

Quadro 5. Sequência de operações realizada pelo Excel.

Operador Descrição

: (dois-pontos) Operadores de referência

; (ponto-e-vírgula) Operadores de referência (espaço simples) Operadores de referência

– Negação (como em –1)

% Percentagem

^ Exponenciação

* e / Multiplicação e divisão

+ e – Adição e subtração

& Conecta duas seqüências de texto (concatenação)

= < > <= >= <> Comparação

Os operadores de cálculos em fórmulas podem ser diferentes tipos, possibilitando especificar o tipo de cálculo que o usuário deseja efetuar nos elementos de uma fórmula. O Microsoft Excel inclui quatro tipos diferentes de operadores de cálculo: aritméticos, de comparação, texto e referência.

Os operadores aritméticos efetuam operações matemáticas básicas, como adição, subtração ou multiplicação, combinam números e produzem resultados numéricos. Sua descrição está apresentada no quadro seguinte.

Operador aritmético Significado Exemplo

+ (sinal de adição) Adição 3+3

– (sinal de subtração) Subtração

Negação

3–1 –1

* (sinal de multiplicação) Multiplicação 3*3

/ (sinal de divisão) Divisão 3/3

% (símbolo de percentagem) Percentagem 20%

^ (sinal de exponenciação) Exponenciação 3^2 (igual a 3*3)

Os operadores de comparação possibilitam comparar dois valores, resultando no valor lógico

VERDADEIRO ou FALSO.

Operador de comparação Significado Exemplo

> (sinal de maior do que) Maior do que A1>B1

< (sinal de menor do que) Menor do que A1<B1

>= (sinal de maior ou igual a) Maior ou igual a A1>=B1 <= (sinal de menor ou igual a) Menor ou igual a A1<=B1

<> (sinal de diferente) Diferente A1<>B1

Outro operador razoavelmente útil do Excel, consiste no operador de texto "&", que permite combinar um ou mais valores de texto para produzir um único texto.

Operador de texto Significado Exemplo

& (e comercial) Conecta ou concatena dois valores para

produzir um valor de texto contínuo "Alfa" & "beto" produz "Alfabeto" O último grupo de operadores do Excel são classificados como operadores de referência, que possibilitam combinar intervalos de células para cálculos.

Operador de referência Significado Exemplo

: (dois-pontos) Operador de intervalo, que produz

uma referência a todas as células entre duas referências, incluindo as duas referências

B5:B15

; (ponto-e-vírgula) Operador de união, que combina

diversas referências em uma referência

SOMA(B5:B15;D5:D15)

(espaço simples) Operador de interseção, que produz

uma referência a células comuns a duas referências

SOMA(B5:B15 A7:D7)

Neste exemplo, a célula B7 é comum aos dois intervalos

4.4 C

R

A

As operações com fórmulas simples ou funções do Excel são muito facilitadas pelos recursos de copiar e colar da planilha.

Existem diferentes procedimentos possíveis para copiar e colar fórmulas no Excel. Provavelmente, o procedimento mais simples envolve o artifício de clicar e arrastar o mouse sobre o canto inferior direito da célula, também denominado alça de preenchimento, conforme ilustrado na Figura 40.

Posicione o mouse

aqui, clique e arraste

para copiar as células

Figura 40. Copiando células no Excel.

Outro procedimento envolve o uso do procedimento disponibilizado no menu Editar -> Copiar (ou o seu atalho correspondente: Ctrl + C). Vide a ilustração da Figura 41.

Figura 41. Recurso Editar Copiar.

Para colar a área copiada, basta selecionar a opção do menu Editar -> Colar (ou o seu atalho correspondente Ctrl + V). Vide a ilustração da Figura 42.

Figura 42. Recurso Editar Colar.

Covém ressaltar que o Excel possui diferentes opções associadas às células nas operações de copiar e colar, geralmente expressas através das referências relativas ou absolutas. Referências relativas, como aquelas apresentadas na figura seguinte, relativizam a cópia das fórmulas. Ou seja, movem as referências das células no momento da colagem das células copiadas.

B C D E

2 Preço Quantidade Valor Fórmula

3 3 4 12 =B3*C3

4 5 6 30 =B4*C4

5 7 3 21 =B5*C5

Figura 43. Copiando células destravadas.

No exemplo anterior copiou-se uma fórmula a partir da alça de preenchimento de sua célula para a obtenção de valores em colunas semelhantes. Neste caso o Excel tratou as referências a linhas e

colunas como relativas, alterando-as de acordo com as regiões a serem totalizadas no final de cada coluna.

Porém, nem sempre, esse tipo de situação proporciona resultados corretos. Em alguns casos, é necessário trabalhar com algumas referências absolutas – que não sejam alteradas. O Quadro 6 mostra a representação de algumas combinações de referências relativas e absolutas para uma célula. Para facilitar o entendimento, sempre que for necessário fazer uma referência absoluta em planilha, deve-se acrescentar o caractere $ à esquerda da linha e/ou coluna em questão.

As quatro possibilidades de referências relativas e absolutas para uma célula estão presentes no quadro seguinte. Convém ressaltar que forma mais fácil de colocação e retirada de referências absolutas no Excel com o auxílio do “$” consiste no uso da tecla de atalho F4.

Quadro 6. Referências de células de planilha.

Modo Descrição Utilização

B2 Endereço Relativo Replicar para linhas e/ou colunas muda tanto a letra

quanto o número.

$B$2 Endereço Absoluto Replicar para linhas e/ou colunas não muda a letra e nem

a coluna

B$2 Coluna relativa e Linha

Absoluta Replicar para outras colunas não letra: replicar para as outras linhas não muda o número.

$B2 Coluna Absoluta e Linha

Relativa

Replicar para outras colunas não muda a letra: replicar para outras linhas muda o número.

No exemplo anterior, caso fossem colocados os símbolos $ antes da identificação da linha ($B) e da coluna ($3), as referências das células copiadas não seriam alteradas, conforme ilustra a figura seguinte.

B C D E

2 Preço Quantidade Valor Fórmula

3 3 4 12 =$B$3*$C$3

4 5 6 12 =$B$3*$C$4

5 7 3 12 =$B$3*$C$5

Figura 44. Copiando células travadas com $$.

4.5 E

A Qualitas Prestadora de Serviços Ltda. registrou os seguintes valores referentes aos serviços executados no mês anterior. Pede-se para calcular no Excel qual o valor total da folha de pagamentos da empresa.

B C D E F

2 Funcionário Diária Dias Trab Sub-total

3 Ana 8,00 16

4 Pedro 12,00 25

5 Maria 10,00 14

6 Gabriel 8,00 18

8 João 12,00 24

9 Antônio 15,00 12

10

11 Total da folha de pagamento

12

Exercício 2

Em relação ao exercício anterior, sabe-se que a empresa costuma premiar seus funcionários com percentuais sobre o salário iguais a 5%, 10% ou 15%, a depender do resultado obtido pela empresa, como um todo. Pede-se para ajustar a planilha anterior, de modo a permitir a inclusão do prêmio.

B C D E F

G 2 Funcionário Diária Dias Trab Sub-total(1) Prêmio Sub-total(2)

3 Ana 8,00 16 4 Pedro 12,00 25 5 Maria 10,00 14 6 Gabriel 8,00 18 7 Maria 10,00 23 8 João 12,00 24 9 Antônio 15,00 12 10

11 Total da folha de pagamento

12 Prêmio em %

13

Exercício 3

A Corretora de Câmbio Dólar Real rotineiramente precisa converter cédulas nos valores de US$5,00, US$20,00, US$50,00 e US$100,00 em reais. Pede-se para criar uma planilha que, com base na cotação do câmbio do dia, efetue a conversão de dólares para reais. No exemplo criado, suponha a seguinte cotação cambial : US$1,00 = R$ 2,52.

Exercício 4

A Fábrica de Sorvetes Gelados Ltda. produz e comercializa cinco sabores de sorvetes em baldes de 10 litros, descritos na tabela seguinte. Com base nos números apresentados, pede-se para criar uma planilha de controle de pedidos, onde o funcionário apenas digitará a quantidade solicitada e a planilha fornecerá o total das compras.

Sabor Preço Unitário

Chocolate $25,00 Manga $18,00 Creme $22,00 Limão $17,00 Baunilha $20,00

Exercício 5

A Gerência Comercial da WQZ Equipamentos Industriais Ltda. apurou as seguintes vendas por regiões. Com base nos números fornecidos, pede-se para calcular os totais por produto e por região.

Região Motores Bombas Geradores

Norte 10 30 60

Sul 20 20 30

Leste 30 10 20

Oeste 40 40 50

De forma adicional, pede-se: a) Considerando US$ 1,00 = R$ 2,40, refaça o relatório para Moeda = US$; b) Construa um relatório que dê a participação % de cada região nas vendas dos produtos.

5

F

UNÇÕES DE

P

LANILHA

“Todo grande progresso da ciência resultou de uma nova audácia da imaginação”.

John Dewey

5.1 O

Este capítulo possui o objetivo de ilustrar o uso daquele que seja, talvez, o mais empregado recurso do Excel: as funções de planilha.

5.2 O

O Excel possui uma série de funções aplicáveis diretamente nas planilhas e que simplificam o processo de inserção de fórmulas algébricas. As funções de planilhas podem ser inseridas de diferentes modos. Um dos mais usuais consiste no emprego do menu Inserir -> Função, conforme exibido na figura seguinte.

Figura 45. Inserir função no Excel.

Outra alternativa consiste no uso direto do botão apropriado, apresentado a seguir, possível de ser disponibilizado na barra de ferramentas do Excel e que funciona como um atalho para a tarefa de inserir funções.

Figura 46. Tecla de atalho para inserir função.

Após solicitar no Excel a inserção de uma função de planilha, diversas opções se mostrarão disponíveis. Dentre as várias categorias de função do Excel, citam-se, por exemplo, os grupos de funções financeiras, de data e hora, matemáticas e trigonométricas, estatísticas, procura e referência, banco de dados, texto, lógica, informações e engenharia e outros. Os grupos e as funções estão ilustrados na figura seguinte.

Figura 47. Categorias e funções disponíveis no Excel.

Dois dos principais grupos de funções empregadas em gestão empresarial consistem nas funções financeiras e estatísticas, apresentadas a seguir.

5.3 U

As funções são fórmulas predefinidas que efetuam cálculos usando valores específicos, denominados argumentos, em uma determinada ordem, denominada sintaxe. Por exemplo, a função SOMA adiciona valores ou intervalos de células, e a função PGTO calcula os pagamentos de empréstimos com base em uma taxa de juros, na extensão do empréstimo e no valor principal do empréstimo. Os argumentos podem ser números, texto, valores lógicos como VERDADEIRO ou FALSO, matrizes, valores de erro como #N/D, ou referências de célula. O argumento atribuído deve produzir um valor válido para este argumento. Os argumentos também podem ser constantes, fórmulas ou outras funções. Para obter maiores informações sobre como usar uma função como um argumento para outra função, também conhecido como funções aninhadas.

A sintaxe de uma função começa com o nome da função, seguido de um parêntese de abertura, os argumentos da função separados por vírgulas e um parêntese de fechamento. Se a função iniciar uma fórmula, digite um sinal de igual (=) antes do nome da função. Quando você for criar uma fórmula que contém uma função, a Caixa de criação de fórmulas irá auxiliá-lo.

Para inserir uma fórmula que contém uma função basta seguir os passos apresentados no quadro seguinte.

Quadro 7. Como inserir funções no Excel.

Passo Descrição

1 Clique na célula na qual você deseja inserir a fórmula.

2 Para iniciar a fórmula com a função, clique em Editar fórmula (=) na barra de fórmulas. 3 Clique na seta abaixo próxima à caixa Funções SOMA .

4 Clique na função que você deseja adicionar à fórmula. Se a função não aparecer na lista, clique em Mais funções para obter uma lista de funções adicionais.

5 Insira os argumentos.

6 Ao concluir a fórmula, pressione ENTER.

5.4 A

O Excel disponibiliza diferentes conjuntos de funções já previamente elaboradas, que simplificam inúmeros trabalhos de utilização da planilha. As principais categorias de funções do Excel são: Funções estatísticas; Funções financeiras; Funções de data e hora; Funções matemáticas e trigonométricas; Funções estatísticas; Funções de pesquisa e referência; Funções de gerenciamento e listas e bancos de dados; Funções de texto; Funções lógicas; Funções de informação e Funções de engenharia. Outras categorias de funções podem ser elaboradas diretamente pelos usuários, com o auxílio do Visual Basic for Aplications.

Observação importante: destaca-se que os comentários sobre as funções

aui mencionadas foram adaptados da ajuda do Excel.

5.4.1 FUNÇÕES FINANCEIRAS

Um outro grupo de funções do Excel facilita as operações básicas da matemática financeira na planilha. De um modo geral, as funções financeiras do Excel operam no denominado regime dos juros compostos. Dentre as mais usuais funções financeiras, destacam-se as apropriadas para o cálculo do valor presente, do valor futuro, da taxa e do número de períodos. Vide os exemplos seguintes.

Uma leitura ampla sobre o uso de funções financeiras e aplicações diversas de finanças no Excel pode ser feita com o auxílio de um outro livro do autor, intitulado “Matemática Financeira com

HP12C e Excel”, publicado pela Editora Atlas.

Função VP : Esta função retorna o valor presente de um investimento, onde os fluxos de caixa são

homogêneos (valores nominais iguais). Seu resultado equivale ao retornado pela função [PV] das calculadoras financeiras. Sua sintaxe é representada da seguinte forma : VP(taxa; nper; pgto; vf;

tipo). Onde : taxa = taxa de juros por período (equivale à tecla [i] das calculadoras financeiras); nper

= número total de períodos de pagamento. Equivale à tecla [n] das calculadoras financeiras); pgto = pagamento feito a cada período e é assumido como homogêneo (iguais, equivale à tecla [PMT] das calculadoras financeiras); vf = valor futuro, ou um saldo de caixa, que você deseja obter depois do último pagamento (se vf for omitido, será considerado 0 - o valor futuro de determinado empréstimo, por exemplo, é 0 - Equivale à tecla [FV] das calculadoras financeiras; tipo = representado pelo número 0 ou 1 e indica as datas de vencimento dos pagamentos. Se for igual a 0 ou omitido, o Excel assume como uma série de pagamentos postecipados (no final do período). Se for igual a 1, o Excel assume como uma série de pagamentos antecipados (no início do período). Equivale às funções [g] [BEG] e [g] [End] das calculadoras financeiras.

B C D E F G

2 VP N I PMT VF Tipo

3 ? 5 10% 200

4 (R$124,18) =VP(D3;C3;;F3)

A figura anterior mostra a obtenção do valor presente de uma operação de investimento com valor futuro igual a $200,00, prazo igual a 5 períodos e taxa igual a 10% ao período. O valor foi obtido através do uso da função VP : $124,18. Note que o Excel, de forma similar às calculadoras financeiras, também emprega as convenções dos sinais (positivo para expressar entradas de caixa e negativo para expressar saídas de caixa).