Curso_calc

˜ E ENSINO SUPERIOR DO PARANA ´ FACULDADE NACIONAL DE EDUCAC ¸ AO ´ INESUL – ARAUCARIA

BrOffice Calc Planilhas Eletrˆ onicas

Prof. Resp. Rodrigo Hermont Ozon 1

Junho de 2009

1

Endere¸co eletrˆ onico [email protected] Vide meu Curriculum Lattes . Erros e omiss˜ oes s˜ ao de minha completa responsabilidade.

E como toda a vida humana ´e um grande jogo de azar!... – Voltaire, Les d´elices (24 de novembro de 1755.)

Ou ´e um Universo muito bem organizado ou um caos que se encolheu e abra¸cou a si mesmo, por´em, em qualquer caso, ´e um Universo, mesmo assim. Mas pode uma certa ordem substituir dentro de ti, enquanto se encontra a desordem no Todo ? – As medita¸co ˜es de Marco Aur´elio, IV:27 (S´eculo II)

Sum´ ario 1 Introdu¸ c˜ ao

4

2 Comparando com o Microsoft Excel

4

3 Elementos B´ asicos do Calc 3.1 Conversor de documentos 3.2 Vers˜oes . . . . . . . . . . . 3.3 Enviar . . . . . . . . . . . 3.4 Modelos . . . . . . . . . . 3.5 Menu Editar . . . . . . . . 3.6 Menu Exibir . . . . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

4 O Assistente de Fun¸ c˜ oes 4.1 Exemplos de Algumas Fun¸c˜oes . . . . . . . . . . . . . . . . . . 4.1.1 F´ormula da Porcentagem . . . . . . . . . . . . . . . . . 4.1.2 Exemplos de F´ormulas Estat´ısticas B´asicas . . . . . . . 4.1.3 Como escrever o resultado de uma f´ormula numa c´elula 4.2 Fun¸c˜oes L´ogicas . . . . . . . . . . . . . . . . . . . . . . . . . . 4.3 Fun¸c˜oes de Busca . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

5 5 7 8 9 9 10

11 . . . . . . . 11 . . . . . . . 11 . . . . . . . 11 com um texto 12 . . . . . . . 13 . . . . . . . 14

5 Fun¸ c˜ oes Matriciais

14

6 O uso do Solver

15

7 Cria¸ c˜ ao de Macros

19

4

1

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas

Introdu¸ c˜ ao

O BrOffice Calc ´e uma planilha eletrˆonica, sendo portanto uma ferramenta para fazer c´alculos, analisar informa¸c˜oes e dar tratamento a grandes massas de dados. Com essa ferramenta, podemos realizar desde tarefas elementares, como a digita¸c˜ao e impress˜ao de uma planilha simples, at´e tarefas mais elaboradas como a cria¸c˜ao de tabelas mais sofisticadas, com dados relacionados e c´alculos complexos. Ele faz parte de um pacote de softwares denominado BrOffice.org [1], no qual se destacam, dentre outros, o editor de textos Writer e um software para apresenta¸c˜ao de slides denominado Impress. Uma planilha ´e simplesmente um conjunto de linhas e colunas, dividida em 256 colunas e 65.536 linhas, as quais podem armazenar textos e n´ umeros. Sua vantagem ´e que os dados podem ser manipulados atrav´es de f´ormulas dispon´ıveis para serem usadas a qualquer momento. A unidade b´asica de uma planilha chama-se c´elula, que ´e formada pela jun¸c˜ao de uma linha com uma coluna. Cada c´elula possui o seu pr´oprio endere¸co, o qual ´e composto pela letra da coluna e pelo n´ umero da linha. Neste manual, objetivamos apresentar e ensinar a utiliza¸c˜ao das funcionalidades b´asicas do Calc, permitindo assim ao leitor conhecer e fazer uso dos recursos necess´arios para a elabora¸c˜ao de planilhas com o aux´ılio deste software. Antes de iniciarmos propriamente as explica¸c˜oes, vamos relembrar alguns conceitos fundamentais para o estudo que vamos realizar.

Figura 1: C´elula A1 (Coluna A, linha 1)

2

Comparando com o Microsoft Excel

As fun¸c˜oes do Calc e do Ms. Excel s˜ao muito semelhantes. No entanto, uns possuem alguns recursos que um ou outro possa n˜ao ter, como a cria¸c˜ao direta de arquivos .pdf ou a obten¸c˜ao de dados externos da web por exemplo.

Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

5

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas

Figura 2: Excel 2007 (acima) e Calc (abaixo)

Elementos B´ asicos do Calc

3

• Arquivo → novo (ou tecla Ctrl+N): Para criar um documento a partir de um modelo, escolha Novo - Modelos e Documentos. Um modelo ´e um arquivo que cont´em os elementos de design para um documento, incluindo estilos de formata¸c˜ao, planos de fundo, quadros, figuras, campos, layout de p´agina e texto. • Abrir → (ou tecla Ctrl+O); • Arquivo → Assistentes: Possui assistentes que guia vocˆe na cria¸c˜ao de cartas comerciais e pessoais, fax, agendas, apresenta¸c˜oes etc. • Barra de Menus;

3.1

Conversor de documentos

O conversor de documentos converte arquivos do formato do StarOffice ou do Microsoft Office para o formato do BrOffice.org. [1]. Para realizar a convers˜ao no menu suspenso, v´a em Arquivo → Assistente → Conversor de documentos. Selecione a op¸c˜ao Microsoft Office. Na sub-op¸c˜ao selecione o tipo Documento do Excel Ap´os selecionar as op¸c˜oes desejadas clique em Pr´oximo. Especifique se ser˜ao convertidos Modelos e/ou Documentos. Para converter os modelos do Excel selecione a op¸c˜ao Modelos Excel. Para converter as planilhas selecione a op¸c˜ao Documentos Excel.

Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

6

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas

Figura 3: Conversor de documentos No campo Importar de selecione o diret´orio onde os arquivos a serem convertidos se encontram e no campo Salvar em selecione o diret´orio onde eles ser˜ao guardados ap´os a convers˜ao.

Figura 4: Conversor de documentos Ap´os selecionar as op¸c˜oes desejadas clique no bot˜ao Pr´oximo. Na caixa de di´alogo ser˜ao confirmadas as as op¸c˜oes de convers˜ao selecionadas. Clique no bot˜ao Converter para iniciar a convers˜ao dos arquivos. Ap´os o fim da convers˜ao clique em Terminado. A convers˜ao est´a conclu´ıda. Feche o documento atual sem sair do programa. O comando Fechar fecha todas as janelas abertas do documento atual. Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

7

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas

Se foram efetuadas altera¸c˜oes no documento atual, vocˆe ser´a perguntado se deseja salvar as altera¸c˜oes. Se abrir um documento para impress˜ao e n˜ao efetuar nenhuma altera¸c˜ao, mesmo assim, vocˆe ser´a solicitado a salvar as altera¸c˜oes ao fechar o documento. Isto se deve ao fato de o manter um registro de quando um documento ´e impresso.

3.2

Vers˜ oes

Salva e organiza v´arias vers˜oes do documento atual no mesmo arquivo. Vocˆe tamb´em pode abrir, excluir e comparar vers˜oes anteriores. Esse recurso ´e bastante u ´til quando se deseja manter o controle sobre vers˜oes de um documento. Para utilizar esse recurso ´e necess´ario que a planilha que est´a sendo utilizada tenha sido salva pelo menos uma vez. Caso o arquivo seja rec´em criado salve-o primeiro. No menu suspenso, v´a em Arquivo → Vers˜oes

Figura 5: Vers˜oes Esse recurso ´e bastante u ´til quando se deseja manter o controle sobre as vers˜oes de um documento. Para utilizar esse recurso ´e necess´ario que o arquivo que est´a sendo utilizado tenha sido salvo pelo menos uma vez, com extens˜ao do BrOffice.org [1]. Se vocˆe salvar uma c´opia de um arquivo que contenha informa¸c˜oes de vers˜ao (escolhendo Salvar como - Arquivo), as informa¸c˜oes da vers˜ao n˜ao s˜ao salvas com o arquivo. Para criar a primeira vers˜ao do documento clique em Salvar Nova Vers˜ao. Novas vers˜ oes Define as op¸c˜oes de salvamento para uma nova vers˜ao do documento. Salvar Nova Vers˜ ao Salva o estado atual do documento como nova vers˜ao. Caso deseje, antes de salvar a nova vers˜ao, insira tamb´em coment´arios na caixa de di´alogo Inserir coment´ario da vers˜ao.

Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

8

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas Inserir Coment´ ario da Vers˜ ao

Insira um coment´ario aqui quando estiver salvando uma nova vers˜ao. Se vocˆe tiver clicado em Mostrar para abrir esta caixa de di´alogo, n˜ao poder´a editar o coment´ario.

3.3

Enviar

Envia uma c´opia do documento atual para diferentes aplicativos. Permite enviar o arquivo atual atrav´es de um cliente de email. Ao selecionar essa op¸c˜ao o seu cliente de e-mail padr˜ao ´e aberto com o arquivo j´a anexado `a mensagem. Vocˆe apenas precisar´a escrever o destinat´ario e o corpo da mensagem. Salvar sempre uma vers˜ ao ao fechar Se vocˆe tiver feito altera¸c˜oes no documento, o BrOffice.org salvar´a automaticamente uma nova vers˜ao quando vocˆe o fechar. Vers˜ oes existentes Lista as vers˜oes existentes do documento atual, a data e a hora em que elas foram criadas, o autor e os coment´arios associados. Fechar Fecha a caixa de di´alogo e salva todas as altera¸c˜oes. Abrir Abre a vers˜ao selecionada em uma janela somente leitura. Mostrar Exibe todo o coment´ario da vers˜ao selecionada. Excluir Exclui a vers˜ao selecionada. Comparar Compare as altera¸c˜oes feitas em cada vers˜ao.Caso deseje, poder´a Aceitar ou rejeitar altera¸c˜oes. Documento como e-mail Envia o documento atual como um anexo de e-mail com o programa padr˜ao de e-mail. Documento como Anexo PDF Exporta todo o arquivo para o formato PDF (Portable Document Format) e, depois, envia o resultado como um anexo em uma mensagem de e-mail.

Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

9

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas

Figura 6: Enviar

3.4

Modelos

Modelos s˜ao documentos que n˜ao podem ser modificados e somente s˜ao utilizados como base para documentos que seguem a algum padr˜ao. O importante a saber sobre modelos em um editor de textos ´e que os modelos evitam erros do tipo: abrir um documento existente e realizar as altera¸c˜oes e ao inv´es de ”‘Salvar como” o usu´ario escolhe ”‘Salvar” fazendo com que o documento original seja alterado, portanto perdendo suas caracter´ısticas originais. Os modelos surgiram tamb´em para minimizar o tempo na realiza¸c˜ao de algumas tarefas que s˜ao identificadas como iguais em mais de um documento. O que anteriormente era feito com o comando salvar como, a´ı lembrem-se do erro de substitui¸c˜ao, agora ser´a feito atrav´es da utiliza¸c˜ao de modelos. No menu suspenso, v´a em Arquivo → Modelos → Salvar e dˆe um nome.

3.5

Menu Editar

Utilize os comandos deste menu para editar planilhas do Calc; por exemplo, para copiar, colar e excluir o conte´ udo de uma c´elula. • Desfazer (Crtl+Z) e Refazer (Ctrl+Y) • Repetir: Repete o u ´ltimo comando • Recortar (Ctrl+X) • Copiar (Ctrl+C) • Colar (Ctrl+V) • Colar Especial (Ctrl+Shift+V) • Selecionar tudo (Ctrl+A) • Altera¸c˜oes: Lista os comandos que est˜ao dispon´ıveis para rastrear as altera¸c˜oes em seu arquivo. Registro → Rastreia cada altera¸c˜ao feita no documento atual, por autor e por data. Com essa op¸c˜ao habilitada todas as altera¸c˜oes efetuadas na planilha ser˜ao destacadas em vermelho. Proteger registros → Impede que um usu´ario desative o recurso de registro de altera¸c˜oes ou aceite ou rejeite altera¸c˜oes, a n˜ao ser que inclua uma senha. Enquanto os registros estiverem protegidos n˜ao ser´a poss´ıvel aceitar ou rejeitar as altera¸c˜oes nem finalizar a grava¸ca˜o das altera¸c˜oes. Dessa forma vocˆe ter´a a garantia que todas as altera¸c˜oes que vierem a ser realizadas ser˜ao registradas e s´o poder˜ao ser aceitas ou rejeitadas por vocˆe. Mostrar Altera¸c˜ oes → Mostra ou oculta as altera¸c˜oes gravadas. Pode-se visualizar somente as altera¸c˜oes desejadas. Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

10

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas • Localizar substituir (Ctrl+F) • Navegador: Selecionando essa op¸c˜ao pode-se ativar ou desativar a visualiza¸c˜ao do navegador. O navegador ´e um recurso que permite percorrer a planilha de forma mais f´acil, podendo-se encontrar rapidamente elementos como imagens ou outras planilhas no documento. • Cabe¸calhos e Rodap´es • Mapa de Imagem → Permite que vocˆe anexe URLs a ´areas espec´ıficas, denominadas pontos de acesso, em uma figura ou em um grupo de figuras. Um mapa de imagem ´e um grupo com um ou mais pontos de acesso. Atrav´es deste recurso vocˆe pode definir ´areas em uma imagem que possuir˜ao links para um determinado endere¸co.

3.6

Menu Exibir

Menu que possibilita a personaliza¸c˜ao do Calc.

Figura 7: Menu Exibir

Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

11

4

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas

O Assistente de Fun¸ c˜ oes

Abre o Assistente de Fun¸c˜oes, que ajuda a criar f´ormulas de modo interativo. Antes de iniciar o Assistente, selecione a c´elula ou o intervalo de c´elulas da planilha atual, a fim de determinar a posi¸c˜ao na qual a f´ormula ser´a inserida. O Assistente de Fun¸c˜oes possui duas guias: a guia Fun¸c˜oes ´e usada para criar f´ormulas e a guia Estrutura ´e usada para verificar a constru¸c˜ao da f´ormula. Por exemplo, digite em uma c´elula abaixo da outra os n´ umeros 3, 2, 2, 3 e numa c´elula ao lado digite =soma(”a sele¸c˜ao das c´elulas que cont´em estes n´ umeros”) em seguida tecle enter. Ver´a que o resultado da f´ormula ser´a 10.

4.1

Exemplos de Algumas Fun¸c˜ oes

A seguir seguem alguns exemplos r´apidos da utiliza¸c˜ao de algumas f´ormulas b´asicas; 4.1.1

F´ ormula da Porcentagem

O c´alculo se realiza da mesma maneira como numa m´aquina de calcular, a diferen¸ca ´e que vocˆe adicionar´a endere¸cos na f´ormula. Veja o exemplo. Um cliente de sua loja, fez uma compra no valor de R$ 1.500,00 e vocˆe deseja dar a ele um desconto de 5% em cima do valor da compra. Veja como ficaria a f´ormula no campo Desct. 1 2 3

4.1.2

A Cliente M´ arcio

B Tcompra 1500

C Desct. =B2*5%

D Vl. a Pagar =B2-C2

Exemplos de F´ ormulas Estat´ısticas B´ asicas

Exerc´ıcio Dada a tabela com as idades a seguir (ver arquivo em anexo ), calcule as estat´ısticas descritivas dos indiv´ıduos; A m´edia aritm´etica que ´e a soma dos valores dividida pelo n´ umero de valores ´e calculada da seguinte maneira:

X=

n X

Xi

i=1

n

Sendo X = m´edia aritm´etica da amostra, n = tamanho da amostra, Xi = i−´esima n X observa¸c˜ao da vari´avel aleat´oria X e Xi = somat´orio de todos os valores de Xi na i=1

amostra. O c´alculo da mediana que ´e o valor do meio de uma sequˆencia ordenada de dados, ´e desenvolvido da seguinte maneira: Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

12

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas D=

n+1 2

A variˆancia da popula¸c˜ao ´e igual `a soma das diferen¸cas ao quadrado em torno da m´edia aritm´etica da popula¸c˜ao, divida pelo tamanho da popula¸c˜ao:

σ2 =

N X

(Xi − µ)2

i=1

N

onde N = tamanho da popula¸c˜ao e µ = m´edia aritm´etica da popula¸c˜ao enquanto o desvio padr˜ao ´e σ 2 . A raiz quadrada da variˆancia ´e igual ao desvio padr˜ao. Idades 15 16 25 30 23 33 19 22 45 32 29 26 23 24 25 31 30 36 35 38 43 47 52

4.1.3

Como escrever o resultado de uma f´ ormula numa c´ elula com um texto

Neste subt´opico explico brevemente como proceder para incorporar o resultado de uma f´ormula dentro de um texto numa u ´nica c´elula. Para isto, adotamos como exemplo a tabela de idades para calcularmos o desvio m´edio das idades; Assim, numa c´elula de seu interesse, digite ´ =”Desvio M´edio ”&DESV.MEDIO(a sele¸ca˜o de todas as idades da tabela)&””

Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

13

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas

4.2

Fun¸c˜ oes L´ ogicas

Suponhamos que desejasse criar um Controle de Notas de Aluno, onde ao se calcular a m´edia, ele automaticamente especificasse se o aluno fora aprovado ou n˜ao. Ent˜ao Veja o exemplo abaixo. Primeiramente, vocˆe precisa entender o que desejar fazer. Por exemplo: quero que no campo situa¸c˜ao ele escreva Aprovado somente se o aluno tirar uma nota Maior ou igual a 7 na m´edia, caso contr´ario ele dever´a escrever Reprovado, j´a que o aluno n˜ao atingiu a condi¸c˜ao para passar. Veja como vocˆe deve escrever a f´ormula utilizando a fun¸c˜ao do SE. Aluno Nota Situa¸c˜ao Renato 6 =SE(B4>=7;“Aprovado”;“Reprovado”) Fernanda 5 Reprovado Paula 4 Reprovado Mauricio 7 Aprovado Luciano 8 Aprovado Rodrigo 7 Aprovado

Exerc´ıcio Fun¸ ca ˜o Se(ou(...)) Encontre os valores para a fun¸c˜ao  0 se x = −3 ou x = 3 f (x) = 3x + 2 Exerc´ıcio Fun¸ ca ˜o Se(se(...)) Crie uma fun¸c˜ao para avaliar os alunos com as notas dispostas no site com a seguinte classifica¸c˜ao: • Se nota >= 9 ent˜ao ´otimo; • Se nota >= 8 ent˜ao bom; • Se nota >= 7 ent˜ao aprovado; • Se nota < 7 ent˜ao insuficiente Exerc´ıcio Fun¸ ca ˜o Se(e(...)) Agora vocˆe tem uma planilha onde tem a idade e altura de seus alunos. Haver´a uma competi¸c˜ao e somente aqueles que tem Idade Maior que 25 e Altura maior ou igual que 1,70 participaram da competi¸c˜ao. Neste caso vocˆe utilizar´a a condi¸c˜ao SE e a condi¸c˜ao E. Porque? ´ simples, porque para o aluno participar ele dever possuir a idade maior Respondo: E que 15 e altura maior ou igual 1,70. As duas condi¸c˜oes devem ser verdadeiras, caso uma seja falsa, ele n˜ao participar´a. Entendeu menino(a)!. Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

14

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas Exerc´ıcio Fun¸ ca ˜o cont.se(...)

Agora vocˆe possui uma planilha onde tem o nome dos alunos e as suas m´edias (segunda aba). E vocˆe desejasse agora saber quantos alunos tiraram m´edias maior e igual a 9. A f´ormula ent˜ao ficaria assim: =CONT.SE(“sele¸c˜ao de c´elulas das notas”;“>= 9”) Exerc´ıcio Fun¸ ca ˜o somase(...) Vocˆe gostaria de soma as faturas que foram pagas. Ent˜ao vocˆe tem uma planilha onde na coluna A vocˆe coloca o nome do cliente, na coluna B o valor da fatura e na coluna C, a situa¸c˜ao se foi paga ou n˜ao. Vocˆe gostaria de somar somente as faturas que estivessem pagas, assim vocˆe saberia o quanto j´a recebeu. Logo a f´ormula seria a seguinte: =SOMASE(Sele¸ca˜o de Argumentos de Texto;”Crit´erios de Texto”;Valores da coluna adjacente)

4.3

Fun¸c˜ oes de Busca

Exerc´ıcio Fun¸ ca ˜o procv(...) Suponhamos que vocˆe tivesse uma planilha onde controla-se a entrada e a sa´ıda de clientes do seu hotel. Nela vocˆe deseja colocar o Nome, Entrada, Saida, Classe e o Valor da Classe. Vocˆe deseja que ao digitar o nome da Classe, automaticamente apare¸ca o valor da Classe. Na verdade vocˆe ter´a que criar 2(duas) planilhas: A 1a Primeira planilha chamaremos de Matriz Tabela, porque nela vocˆe colocar´a o nome das Classe e o seu Valor A 2a Segunda planilha vocˆe controlar´a a entrada e a sa´ıda do cliente.

5

Fun¸ c˜ oes Matriciais

S˜ao u ´teis nos c´alculos de proje¸c˜oes de vendas, demanda, custos, pre¸cos, etc. Exerc´ıcio Fun¸ co ˜es Matriciais Suponha que o volume de vendas em toneladas de banana da empresa AlfaBeta seja o seguinte (vide a aba chamada matricial). O Administrador da empresa deseja realizar uma proje¸c˜ao estat´ıstica destas vendas para os pr´oximos per´ıodos. Inicialmente, construa um gr´afico de dispers˜ao para avaliar as poss´ıveis tendˆencias das vendas ao longo dos meses. Em seguida, construa um modelo de proje¸c˜ao pelo m´etodo de m´ınimos quadrados ordin´arios seguindo os passos. Nas c´elulas que est˜ao pintadas, selecione-as e siga a ordem dos n´ umeros ao lado das 0 opera¸c˜oes matriciais. Por exemplo na c´elula G2, temos a matriz X que corresponde a matriz transposta dos valores das vari´aveis explicativas do modelo de proje¸c˜ao (no caso somente o tempo e uma constante, ou seja, a matriz de valores 1). Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

15

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas

Deste modo para obtermos a transposta da matriz, selecionamos com o mouse todas as c´elulas pintadas e em seguida digitamos a f´ormula: =transpor(sele¸c˜ao dos valores X) e em seguida teclamos ao mesmo tempo Ctrl+Shift+Enter Assim, obtemos a matriz com os valores dentro de uma f´ormula com chaves indicando que o Calc a interpretou como c´alculo matricial. 0 Em seguida temos a matriz X X, ou seja a multiplica¸c˜ao da matriz transposta pela matriz de explicativas. Para isso selecionamos as c´elulas pintadas e em seguida entramos com a f´ormula =matriz.mult(sele¸ca˜o da matriz transposta;sele¸ca˜o da matriz de explicativas) Ctrl+Shift+Enter 0

0

Em seguida temos o c´alculo da matriz inverso da matriz X X, ou seja temos (X X)−1 . 0

=matriz.inverso(sele¸ca˜o da matriz X X)

E finalmente temos os parˆametros do modelo de proje¸c˜ao Yt = α + βXt que ´e dado 0 0 por (X X)−1 X Y 0

0

=matriz.mult(sele¸ca˜o da matriz (X X)−1 ;sele¸ca˜o da matriz X Y )

Agora encontramos os valores dos parˆametros α e β e se fizermos agora a multiplica¸c˜ao destes valores pelos valores da matriz de X temos a proje¸c˜ao de vendas. Para isto utilize a seguinte f´ormula na c´elula F5: =$J$18+$J$19*E5

Agora arraste-a at´e o final e plote um gr´afico para avaliar o ajuste da proje¸c˜ao linear:

Figura 8: Proje¸c˜ao de Vendas

6

O uso do Solver

Antes de mais nada precisamos saber o que ´e o Solver; ”‘Solver” literalmente traduzido para o portuguˆes ´e ”‘resolvente”. em outras palavras, ´e aquilo que resolve algo. Aquilo que ´e capaz de uma solu¸c˜ao, resolver uma solu¸c˜ao. O Solver foi criado para exatamente resolver os mais variados problemas matem´aticos. Um sinˆonimo de Solver em inglˆes ´e optimizer (otimizador), pois na maioria dos casos o Solver ´e utilizado para otimizar a aloca¸c˜ao de v´arios recursos. Imagine-se na situa¸c˜ao onde vocˆe possui uma f´abrica onde 3 produtos s˜ao fabricados. Se os produtos competem pelas mesmas mat´erias-primas, possuem diferentes pre¸cos no Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

16

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas

mercado, levam diferentes prazos de tempo para serem produzidos, vocˆe certamente deseja saber qual a melhor escala de produ¸c˜ao. Isto ´e, a escala que resulta na otimiza¸c˜ao do tempo, da mat´eria-prima e sem d´ uvida seus lucros. Existem v´arios tipos de Solver e o Excel e o Calc possuem o Atingir Meta o qual ´e uma ferramenta pr´opria sua mas com capacidade limitada de resolu¸c˜ao de problemas. Por exemplo, vocˆe n˜ao pode definir restri¸c˜oes no Atingir Meta o que muitas vezes ´e crucial para a solu¸c˜ao do mesmo. Se vocˆe fosse abrir uma f´abrica de m´oveis, hoje, vocˆe teria v´arias restri¸c˜oes e uma delas ´e de capital. O Atingir Meta n˜ao nos possibilita inserir essas vari´aveis ao passo que o Solver abre novos leques de possibilidades. Exerc´ıcio – O Caso da F´ abrica de Past´ eis e Pastel˜ oes Ltda.2 A Past´eis e Pastel˜oes Ltda. fabrica past´eis de forno a partir de dois ingredientes b´asicos: massa semipronta e recheio congelado. A empresa pretende estabelecer um modelo para previs˜ao de seu lucro operacional mensal. Desconsiderando a hip´otese de altera¸c˜ao de tamanho e da qualidade dos past´eis, a diretoria considera que o pre¸co unit´ario do pastel e o pre¸co m´edio praticado pela concorrˆencia s˜ao os u ´nicos fatores relevantes na determina¸c˜ao da demanda, a qual comporta-se segundo a equa¸c˜ao z = 15.000 − 5.000x + 5.000y, onde x ´e o pre¸co do pastel da Past´eis e Pastel˜oes e y ´e o pre¸co m´edio dos past´eis vendidos pelos concorrentes. Tendo em vista que o pre¸co dos past´eis vendidos pela concorrˆencia ´e uma vari´avel fora do controle da Past´eis e Pastel˜oes, somente o pre¸co unit´ario do pastel vendido pela empresa configura-se como vari´avel de decis˜ao do problema. Assim, sendo o pre¸co m´edio praticado pela concorrˆencia, os custos de mat´eria-prima, os custos de processamento e os custos fixos s˜ao os parˆametros do modelo. Agora abra a aba denominada Past´eis e visualize o problema. Verifique que se trabalharmos com um pre¸co de venda de R$ 6,00 ´e mais interessante do que com um pre¸co de R$4,00 ou de R$ 8,00. Isto ocorre porque com um pre¸co unit´ario de R$ 4,00 n˜ao gera demanda suficiente para compensar a pequena margem de contribui¸c˜ao do produto, resultando em um lucro pequeno; e o pre¸co de R$ 8,00 retrai a demanda de tal forma que se apresenta menos lucrativo do que o estabelecimento do pre¸co de venda de R$ 6,00. Todavia, ser´a que o pre¸co de R$ 6,00 ´e realmente o que garante o maior lucro ? E se o pastel for vendido a R$ 7,00, como se comportar˜ao a demanda e o lucro operacional ? Resolvemos esta d´ uvida simulando os resultados para diferentes n´ıveis de pre¸co, tarefa que pode ser realizada mais facilmente atrav´es de uma ferramenta chamada de proje¸c˜ao do tipo “Se ent˜ao”, que veremos mais adiante. Com a ajuda do Calc, temos condi¸c˜oes de confrontar graficamente os resultados apresentados pelo modelo com dados reais ocorridos. Esta visualiza¸c˜ao ´e extremamente u ´til para a verifica¸c˜ao da eficiˆencia do modelo, pois podemos facilmente observar se os dados previstos est˜ao pr´oximos ou n˜ao do que acontece na realidade, e assim, afirmar se o modelo ´e bom ou deve ser substitu´ıdo por outro. Uma auditoria na f´abrica de past´eis constatou, atrav´es dos dados cont´abeis, que o custo unit´ario de processo ´e vari´avel de acordo com o n´ umero de past´eis produzidos, ou seja, se comporta de forma diferente da que o modelo havia assumido (R$ 0,40/pastel independente no n´ıvel de produ¸c˜ao). Esta informa¸c˜ao revela que h´a uma falha no modelo inicial, pois um dos parˆametros do problema (o custo do processo no caso) n˜ao est´a sendo eficientemente representado. Representar erroneamente o comportamento de uma 2

Adaptado de Lachtermacher, 2006 [2]

Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

17

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas

vari´avel relevante significa tornar o modelo pouco representativo da realidade e portanto, inadequado como suporte ´a tomada de decis˜ao. Desta forma, para que o nosso modelo de lucros mensais represente o comportamento do custo unit´ario de processo em rela¸c˜ao ao n´ umero de past´eis produzidos. O Calc nos ajudar´a a descobrir esta equa¸c˜ao. Neste sentido, precisamos criar uma tabela em que constem dados cont´abeis coletados durante o processo de auditoria do custo mensal de processo para diferentes n´ıveis de produ¸c˜ao, bem como a previs˜ao destes custos de acordo com o modelo inicial (n´ umero de past´eis produzidos no mˆes × R$ 0,40), para podermos compar´a-los. A tabela ´e chamada de Dados cont´abeis do processo de auditoria e o nosso pr´oximo passo ´e solicitar um gr´afico de dispers˜ao selecionando as trˆes colunas. O gr´afico selecionado ir´a mostrar sobre o mesmo plano cartesiano, os pontos referentes aos custos reais e aos projetados de forma que podemos visualizar os erros de proje¸c˜ao dos custos de processo. Apenas observando o grafico da Figura 1.13 concluimos que no modelo escolhido para o calculo, o custo de processo n˜ao est´a representando bem os dados reais. Al´em de prever valores muito diferentes dos verdadeiros, o modelo e tendencioso, pois abaixo de determinada quantidade de past´eis produzidos, ele superestima os valores do custo de processo e, acima deste ponto, ele subestima. J´a que a equa¸c˜ao custo de processo = R$ 0,40 × numero de pasteis produzidos n˜ao ´e um bom modelo para explicar o comportamento real dos custos de processo, precisamos encontrar uma equacao que melhor represente os dados reais. Uma t´ecnica muito u ´til para descobrirmos fun¸c˜oes que expliquem satisfatoriamente as relacoes entre vari´aveis consiste em, a partir do gr´afico com os dados reais, solicitar ao Excel a adi¸c˜ao de uma linha de tendˆencia (trend line). Esta ferramenta tentara encontrar uma curva (e sua equa¸c˜ao) que melhor se aproxime dos dados reais. Como n˜ao dispomos desta op¸c˜ao no Calc, precisamos elaborar os c´alculos matriciais para encontrar esta equa¸c˜ao. No Excel o procedimento para a inclusao de uma linha de tendencia e muito simples. Devemos apenas: 1) clicar com o botao direito do mouse sobre os pontos dos dados reais representados no grafico e selecionar a opcao de adicionar linha de tendencia; 2) escolher a curva que mais se assemelha ao desenho formado pelos dados reais; e 3) solicitar na tela de opcoes que seja exibida a equacao da curva adicionada. Feito isso, o Excel automaticamente ira exibir sobre o grafico a linha de tendencia calculada e sua equacao. Escolhemos primeiramente verificar o ajuste de uma linha de tendencia linear. Observando o grafico, notamos rapidamente que a linha adicionada representa os dados reais de uma forma muito superior a da equacao que estavamos utilizando para calcular o custo de processo. Assim, se substituirmos a formula anterior (custo de processo = numero de pasteis produzidos x R$ 0,40) pela equacao da linha de tendencia linear (custo de processo = 0,7868 x numero de pasteis produzidos - 6372,7), teremos um modelo final de lucros mensais para a Pasteis e Pasteloes Ltda. mais adequado. Contudo, sera que a linha de tendencia linear e realmente a que melhor representa o custo de processo? Os dados reais marcados no grafico nao formam urna reta perfeita; eles apresentam urna certa curvatura. Assim, repetindo o procedimento anterior e solicitando diferentes tipos de linhas de tendencia, podemos, atraves da analise grafica, compara-las e escolher a melhor. Se compararmos seu ajustamento aos dados reais com o ajustamento da linha linear calculada anteriormente, constatamos que a equacao potencial explica melhor o custo de Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

18

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas

processo, pois gera resultados mais proximos da realidade do que a equacao linear. Comparando as tres linhas de tendencia adicionadas, constatamos que a equacao exponencial e a que melhor se ajusta aos dados reais, sendo, portanto, a que melhor representa o comportamento do custo de processo. Por conseguinte, se substituirmos a formula utilizada anteriormente para o calculo do custo de processo (custo de processo = R$ 0,40 x numero de pasteis produzidos) pela equacao exponencial encontrada (custo de processo =13, 0536e0,0001×nro. de past´eis produzidos ), refinaremos o modelo de lucros mensais da Pasteis e Pasteloes Ltda., tornando-o mais representativo da realidade. Proje¸ ca ˜o do Tipo Se-Ent˜ ao Uma projecao do tipo Se-Entao (If-Then) e exatamente o que esse nome sugere. Ap´os termos definido o modelo e todas as relacoes entre as variaveis, podemos fazer uma analise do comportamento da(s) variavel(eis) de saida a partir de diferentes entradas. Este tipo de projecao permite que facamos uma analise da sensibilidade do modelo, isto e, o quanto e em que proporcao o resultado final e alterado a partir de pequenas alteracoes nos valores das variaveis de decisao. Agora que ja obtivemos o modelo definitivo para o problema da empresa, podemos verificar a sensibilidade do lucro mensal a modificacoes no preco de venda do pastel. Tornar possivel esta analise e extremamente simples; precisamos apenas copiar as celulas com as relacoes do modelo em algumas colunas seguintes e, entao, estabelecer precos de venda diferentes e crescentes ao longo das colunas. Conforme podemos observar na planilha, o comportamento do lucro mensal da Pasteis e Pasteloes Ltda. em relacao ao preco de venda do pastel nao e linear, pois o mesmo e crescente ate o preco de venda unitario de R$ 7,00 (em torno de) e decrescente ap´os este ponto. Com o gr´afico visualizamos esta relacao entre o preco de venda do pastel e o lucro mensal. Atraves do grafico, torna-se ainda mais facil constatar que o preco de venda que maximiza o lucro mensal da empresa e em torno de R$ 7,00 por unidade de pastel. Comando Atingir Meta (Goal Seek) Uma maneira precisa de encontrar valores de saida especificos para um modelo consiste no use de um comando do Excel denominado Atingir Meta ou, em ingles, Goal Seek. Este comando procura automaticamente o valor solicitado para uma unica variavel de saida a partir de uma unica variavel de entrada. Uma aplicacao bastante util desta ferramenta e na analise do ponto de equilibrio do negocio (Break Even Point), ou seja, neste caso, o preco de venda que gera um lucro mensal igual a zero. Para obter este valor, devemos, a partir do modelo definido na Figura 1.20, solicitar que o Comando Atingir Meta ajuste a celula que contem o resultado do lucro mensal para o valor zero, variando o valor da celula do preco de venda unitario do pastel.

Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

19

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas Exerc´ıcio Transformadores Um fabricante de transformadores produz dois tipos de transformadores, tipo X e tipo

Y. O transformador X requer 8,75 horas na linha de produ¸c˜ao ao passo que a produ¸c˜ao do transformador Y requer 3,5 horas na linha de produ¸c˜ao. O tempo para montagem de cada transformador ´e de 5,25 horas para X e 21 horas para o transformador Y. Completada a montagem, os produtos s˜ao embalados em containeres especiais e o transformador X leva 7 horas para embalar e o transformador Y leva o dobro do tempo. Sabendo que a empresa disp˜oe de 70 horas quinzenais para produ¸c˜ao, 105 horas quinzenais para montagem e 84 horas quinzenais para embalagem, qual a quantidade que a empresa deve produzir de cada transformador para maximizar sua receita se o transformador X ´e vendido no mercado por R$ 12,25 e o transformador Y ´e vendido por R$ 36,75? Dica: Desenvolva uma fun¸c˜ao Receita e depois monte as inequa¸c˜oes que representam as restri¸c˜oes impostas na produ¸c˜ao, montagem e embalagem.

7

Cria¸ c˜ ao de Macros

Existem situa¸c˜oes onde n˜ao conseguimos resolver um determinado problema, simplesmente utilizando os comandos e f´ormulas do Excel (embora existam milhares de fun¸c˜oes dispon´ıveis no Excel). Nessas situa¸c˜oes temos que fazer o uso de recursos como Macros e Programa¸c˜ao. A linguagem de programa¸c˜ao do Excel e do BrOffice por exemplo ´e o VBA - Visual Basic for Applications. O VBA ´e a linguagem de programa¸c˜ao para todos os aplicativos do Microsoft Office: Word, Excel, Access e PowerPoint. Caso vocˆe execute uma tarefa v´arias vezes no Microsoft Excel ou no Calc, ´e poss´ıvel automatiz´a-la com uma macro. Uma macro ´e uma seq¨ uˆencia de comandos e fun¸c˜oes armazenados em um m´odulo de c´odigo do VBA e pode ser executada sempre que vocˆe precisar executar a tarefa. Quando vocˆe grava uma macro, o Excel armazena informa¸c˜oes sobre cada etapa realizada `a medida que vocˆe executa uma seq¨ uˆencia de comandos. Em seguida, vocˆe executa a macro para repetir, ou “reproduzir”, os comandos. Por exemplo, vamos supor que, seguidamente, vocˆe precisa formatar uma c´elula com Negrito, cor de fonte Vermelha, It´alico, Fonte Verdana de Tamanho 13, com quebra autom´atica de linha. Ao inv´es de ter que executar todos os comandos de formata¸c˜ao em cada c´elula, vocˆe pode criar uma Macro que aplica todos os comandos de formata¸c˜ao. Ap´os criada a Macro, cada vez que vocˆe tiver que aplicar o conjunto de comandos de formata¸c˜ao, basta executar a Macro, o que normalmente ´e feito atrav´es da associa¸c˜ao de uma combina¸c˜ao de teclas com a Macro, como por exemplo Ctrl+L. No nosso exemplo, cada vez que vocˆe quisesse formatar uma c´elula com os formatos descritos, bastaria clicar na c´elula e pressionar Ctrl+L. Bem mais f´acil do que aplicar cada comando individualmente. Vocˆe pode Gravar uma macro para realizar uma tarefa em uma etapa: Antes de gravar uma macro, planeje as etapas e os comandos que vocˆe deseja que a macro execute. Se cometer um erro durante a grava¸c˜ao da macro, as corre¸c˜oes feitas tamb´em ser˜ao gravadas como comandos da macro. Ao gravar macros, o VBA armazena cada macro em um novo m´odulo de c´odigo VBA, anexado a uma pasta de trabalho (arquivo .xls). Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

20

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas

Neste curso r´apido veremos um procedimento simples de cria¸c˜ao de uma macro no Calc.

Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria

21

Prof. Econ. Rodrigo Ozon – BrOffice Calc Planilhas Eletrˆ onicas

Referˆ encias [1] BrOffice Calc, Writer, Math, Impress e demais (download, instala¸c˜ao e manuais). Dispon´ıvel em: < http://www.broffice.orghttp://www.broffice.org >. Acesso em: Junho de 2009. [2] Lachtermarcher, G. Pesquisa Operacional na Tomada de Decis˜ oes: Modelagem em Excel. 3.a Ed. Campus , S˜ao Paulo, 2006.

Cursos Juninos – Faculdade Nacional de Educa¸ca ˜o e Ensino Superior do Paran´ a– INESUL/FANEESP – Arauc´ aria