Baixe o app para aproveitar ainda mais
Prévia do material em texto
148 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Unidade III 7 Planilha ElEtrônica Nesta unidade, vamos trabalhar os conceitos em planilhas eletrônicas, um dos mais poderosos softwares aplicativos já desenvolvidos e de fácil utilização que, devido à sua importância, está presente em quase todos os computadores do mundo e em 98% dos microcomputadores e seus derivados portáteis, bem como em versões somente leitura nos smartphones e celulares que se conectam à internet. Existem diversas planilhas eletrônicas disponíveis no mercado, apresentarei a você a mais utilizada, que é um produto da Microsoft, o MS-Excel, planilha que domina o mercado mundial, pois está integrada ao pacote Office da Microsoft. Devido à sua popularidade, o Excel foi escolhido para explicar os conceitos, mas com uma grande vantagem: a maneira que vou desenvolver servirá para entender o Excel em qualquer versão e lhe dará base para utilizar as outras planilhas existentes, com pequenos ajustes e adaptações dos comandos e botões, por similaridade. Saiba mais Indico o livro de James o´Brien, conhecido como a “bíblia dos analistas de sistemas”, por meio do qual você pode aprofundar os conceitos aqui trabalhados. O’BRIEN, J. A. Sistemas de informação e as decisões gerenciais na era da Internet. 2ª ed. São Paulo: Saraiva, 2007. 7.1 conceituação, premissas e método para planilhas Olá, bem-vindo! Eu sou o Borg, seu agente inteligente. Como você já me conhece, vamos nesta unidade utilizar a planilha eletrônica MS-Excel para demonstrar os conceitos e fórmulas que nos vão trazer uma facilidade enorme e a vantagem de trabalhar com velocidade e produtividade, necessidade que as empresas valorizam e requerem dos profissionais. Requisitos básicos: • familiarização com ambiente Windows; • conhecimentos de manipulação de arquivos. 149 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação Conceito: planilha eletrônica é um software aplicativo destinado a cálculos matemáticos, estatísticos, financeiros, lógicos, data e referência, gráficos e banco de dados, em ambiente gráfico Windows, com todas as facilidades ao usuário. Premissas: desenvolveremos planilhas com o máximo de automatização, utilizando, para isso, fórmulas e endereçamento eletrônico, buscando a menor utilização de memória possível. Este material considera uma instalação típica por completo, padrão para microcomputadores. Algumas opções podem não funcionar devido a restrições de rede, instalações customizadas (notebook) e handheld (micros de mão tipo palm e smartphones). Os comandos aqui destacados funcionam em todas as versões do Excel e, adequando-se ao botão e guias da versão 2007 e com a mesma facilidade na versão 2010, você também conseguirá elaborar planilhas, o que lhe dará uma agilidade muito grande se você já é usuário do software. Método: para a digitação de dados e elaboração de fórmulas, sempre que possível, trabalhe por colunas, utilizando fórmulas predefinidas ou digitadas, e utilize os procedimentos de arrastar a alça da célula para copiar as fórmulas e de autopreenchimento e formatos já predefinidos, pois essas dicas proporcionam produtividade e maior velocidade na confecção da planilha. 7.2 iniciando sua planilha Vamos partir de um exemplo com um arquivo novo e digitar dados, elaborar fórmulas. A partir dessa demonstração, você poderá elaborar suas planilhas agregando esses conhecimentos à sua necessidade. 7.2.1 Carregando o Excel Você pode carregar o Excel a partir do desktop de seu computador ou a partir de: Botão Iniciar > Programas > MS-Office ou MS-Excel (depende da instalação no Windows). 7.2.2 Abrindo arquivo Ao carregar o Excel, você encontra uma planilha aberta pronta para uso (nosso caso), mas você pode abrir um arquivo já existente (já salvo em qualquer unidade, selecionando-o e escolhendo o arquivo da lista na caixa abrir). 7.2.3 Tela, menu e botões A tela do Excel é padronizada em ambiente Windows; na primeira linha, encontramos, à esquerda, o nome do aplicativo e o nome do arquivo que estamos manipulando; à direita, os botões para minimizar, maximizar e fechar o aplicativo. Na segunda linha, encontramos os comandos à esquerda, iniciando com o arquivo, editar, exibir, inserir, formatar, ferramentas, dados, janela e ajuda. Reparem que existe uma letra sublinhada para acesso, via teclado, digitando a tecla Alt, à esquerda da barra de espaço mais a 150 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 letra sublinhada. Por exemplo: Alt + A é o comando arquivo. Pode-se selecionar com o mouse e também a partir de atalhos representados por Ctrl + A. Mas, observe: atalhos podem mudar dependendo da instalação do Windows. Quadro Comando Teclado Atalho Arquivo Alt + A Ctrl + A Editar Alt + E Ctrl + C Ctrl + V Exibir Alt + Z Inserir Alt + I Formatar Alt + F Ferramentas Alt + M Dados Alt + D Janela Alt + J Ajuda Alt + U Para os demais atalhos, observe a cortina do menu, à esquerda de cada comando, somente para os que os possuem. Na terceira linha, encontramos os botões que representam os comandos mais utilizados, como novo, salvar, imprimir, visualizar impressão, recortar, colar, autossoma, assistente de função, gráficos, ordenação e visualização da área de trabalho (planilha). Eles estão dispostos por uso, os mais utilizados à esquerda; os menos, à direita, assim como os comandos. Na quarta linha, estão as caixas de diálogo que são mais utilizadas nas opções de formatação de células, negrito, itálico, alinhamento, botão de moeda, formatação de conteúdo, bordas e cores de fundo da célula e da fonte. Na quinta linha, temos a barra de fórmulas, na qual visualizamos o conteúdo de uma célula, sua fórmula. Podemos editar o conteúdo, clicando com o mouse e editando. A seguir, temos a área de trabalho da planilha composta pelas colunas e linhas, formando as células e endereçadas como uma batalha naval. São IV colunas pelo alfabeto, 1024 colunas por 65.536 linhas — muito espaço para trabalho. Se você for usuário da versão 2010, contará com 3380 colunas e 1048576 linhas o que é muito, muito mais área de trabalho disponível. A navegação pode ser com o mouse e também pelas teclas direcionadoras: direita, esquerda, acima e abaixo, conjugadas com a tecla End. • Posicione o cursor na célula A1, digite a tecla End, seta para baixo: endereço “A 65536”. • Agora tecle End e seta à direita: endereço “IV 65536”. • Tecle End e seta para cima: endereço “IV 1”. 151 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação • Tecle End e seta para à esquerda: endereço “A1”. Você observou que navegou pelos extremos da planilha. 7.2.4 Nomeando planilhas (guias) e arquivos Coloque o cursor do mouse sobre a aba no canto inferior esquerdo de sua tela, onde está escrito “Plan1”. Dê dois cliques rápidos e digite o nome desejado. Por exemplo: “Ex.1” > enter (nomeando a planilha como “exercício 1”). Para reposicionar, escolha a planilha, dê um clique, segure o botão esquerdo do mouse sobre a guia e arraste para outra posição. Para inserir outra guia, dê o comando inserir planilha ou posicione o ponteiro do mouse sobre uma guia. Clique uma vez com o botão direito e escolha a opção inserir planilha (na caixa aberta). Nomeando arquivo: dê o comando arquivo > salvar como. Altere o nome do arquivo e depois salve, ou, pelo Windows Explorer, utilize a opção renomear. • Autopreenchimento (alfabético e numérico) — Autopreenchimento alfabético: - Posicione o cursor em B1 e digite JAN > enter. - Posicione o cursor em B1 e, na alça à direita inferior da célula, arraste para C1 e D1. Observe que o Excel preencheucom a sequência de meses; se colocar o ano, ele também altera a cada passagem de DEZ a JAN. — Autopreenchimento numérico: - Posicione o cursor em A2 e digite 1 > enter. - Posicione o cursor em A3 e digite 2 > enter. Com o mouse, selecione as duas células, arraste pela alça até o endereço A6 e observe o preenchimento numérico estabelecido pelas duas células iniciais. • Inserindo dados qualitativos e quantitativos O preenchimento de dados é feito diretamente nas células (intersecção entre linhas e colunas), posicionando o endereço com o mouse ou utilizando as setas direcionais do teclado para movimentação. Em seguida, confirma-se a inserção com o enter, já posicionando-se na próxima célula que desejar. — Posicione o cursor em A1 e digite Filiais. — Posicione o cursor em A7 e digite Total mês. 152 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 — Insira os dados de janeiro. — Posicione o cursor em B2 e digite 100 enter. — Posicione o cursor em B3 e digite 230 enter. — Posicione o cursor em B4 e digite 250 enter. — Posicione o cursor em B5 e digite 360 enter. — Posicione o cursor em B6 e digite 270 enter. — Insira os dados de fevereiro. — Posicione o cursor em C2 e digite 250 enter. — Posicione o cursor em C3 e digite 330 enter. — Posicione o cursor em C4 e digite 280 enter. — Posicione o cursor em C5 e digite 320 enter. — Posicione o cursor em C6 e digite 300 enter. — Insira os dados de março. — Posicione o cursor em D2 e digite 390 enter. — Posicione o cursor em D3 e digite 300 enter. — Posicione o cursor em D4 e digite 450 enter. — Posicione o cursor em D5 e digite 150 enter. — Posicione o cursor em D6 e digite 230 enter. — Posicione o cursor em E2 e digite Total trim. — Respeite as colunas e linhas. Sua planilha deve estar assim: Figura 34 – Modelo de dados inseridos • Inserção de fórmulas (autossoma, subtração, multiplicação e divisão) Pode-se digitar uma fórmula pelo teclado, ou utilizar o assistente de função F(x) caso tenha a fórmula, ou, no caso de autossoma, utilizar o botão de somatória para que automaticamente o Excel reconheça o intervalo de dados e calcule o resultado. — Posicione o cursor em B7 e dê um clique no botão autossoma; observe o intervalo selecionado automaticamente e dê o enter. A fórmula foi calculada e o resultado, totalizado. 153 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação — Posicione o cursor em B7 e, na alça da célula, arraste até o endereço E7. A fórmula estará copiada para todos os meses. Se quiser, pode fazer uma a uma, ou ainda fazer em B7 e depois colar e copiar para os demais. — Posicione o cursor em E2 e digite: =B2 + C2 + D2 enter A fórmula foi construída pelo teclado. Também pode-se utilizar suas setas para escolher cada parcela participante da soma (também para subtração, multiplicação e divisão). — Posicione o cursor em E2 e, na alça da célula, arraste até E7; como é uma fórmula, é possível arrastar, que será copiada e adequada aos novos endereços das outras colunas. Sua planilha deve estar assim: Figura 35 – Modelo de planilha • Salvando sua planilha Se for a primeira vez que vai salvar a planilha, utilize os comandos: — Arquivo > salvar como. Uma caixa de diálogo se abre. Por meio dessa, pode-se escolher a unidade a salvar, a pasta e um nome padronizado para o arquivo, pois é uma interface gráfica do Windows. Nas demais vezes, pode-se utilizar o comando ou o botão disquete, que salva, mas sem a chance de alteração do nome ou local. 7.3 aprimorando sua planilha 7.3.1 Assistente de funções, estatísticas, financeiras, data Para a construção de outras fórmulas, pode-se utilizar o assistente de funções, o botão F(x), na quarta linha do Excel, onde uma caixa de diálogo colar função se abre com as opções de fórmulas 154 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 à esquerda contendo as mais recentemente usadas. É possível visualizá-las por categoria da função. Observe que à direita aparecem as funções e, na parte de baixo da caixa, aparece a sintaxe e uma breve explicação sobre a função. Após escolher uma opção, abre-se “caixas”, onde se selecionam as células participantes até se concluir, ou pode-se demarcar intervalos de endereços para os cálculos. • Ajuste de largura de colunas — Coloque o cursor na fronteira das colunas que fica entre a barra de fórmulas e a área de trabalho; dê dois cliques rápidos e, automaticamente, a coluna é ajustada. — Se quiser, pode segurar o botão esquerdo do mouse e ajustar manualmente, ou ainda utilizar o comando formatar > coluna > largura, abertos no menu de cortina. Endereços relativos, mistos e absolutos O Excel possui três endereçamentos, que podem ser construídos digitando-se ou utilizando a tecla especial [F4] na parte superior dos teclados: Quadro 5 Endereço absoluto $C $L Fixa coluna e linha. Endereço misto C $L Fixa somente LINHA. Endereço misto $C L Fixa somente coluna. Endereço relativo C L Coluna e linha estão livres. Observação Utilize a tecla especial [F4] para mudar os endereços na confecção da fórmula, ou após, editando-a na barra de fórmulas. Quando você arrastar ou copiar uma fórmula, o Excel leva o endereçamento junto, atualizando. Mas em alguns casos uma parte da fórmula não pode variar (coluna ou linha ou ambos), daí a necessidade de alterar o endereço para se conseguir calcular com a lógica da fórmula correta e gastando-se menor quantidade de memória, o que deixa a planilha mais rápida. lembrete Quando você for arrastar ou copiar no sentido horizontal, fixam-se as colunas, e quando for arrastar ou copiar no sentido vertical, fixam-se as linhas. 155 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação Como exemplo, vamos calcular a distribuição percentual de cada mês em relação ao trimestre de nossas filiais: — Posicione o cursor em A8, digite DISTRIB % e dê o enter. — Posicione o cursor em B8, digite =B7/E7 e dê o enter. O cálculo é feito. Se for arrastar para os demais, ocorrerá erro, pois o endereço E8 desloca-se também à direita, daí a necessidade de fixar a coluna $E7. Você pode clicar na célula B8 e, na barra de fórmulas, com o mouse posicionar na parte = E7; digite a tecla especial [F4] até aparecer um cifrão somente antes da coluna E, ficando = b7/$E7. Dê o enter e está pronto, pode arrastar que o cálculo será feito para todos. Sua planilha deve ficar assim: Figura 36 – Modelo de planilha E com as fórmulas: Figura 37 – Modelo de planilha com fórmulas • Formatação de células A formatação de células determina a unidade dos valores e aprimora o entendimento. Pode-se utilizar os botões para formato de moeda e percentual, bem como ajustes de casas decimais, ou pelo comando: 156 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 — Formatar > células. Uma caixa de diálogo se abre e nela é possível escolher diversos formatos com modelos e ajustes decimais, como: — Posicione o cursor em B7 e, com o botão esquerdo do mouse pressionado, marque até a célula E7. Solte o botão do mouse e escolha na quarta linha do Excel o botão estilo moeda: os valores serão apresentados já com duas casas decimais. Sua planilha ficará assim: Figura 38 – Modelo de planilha com resultados — Posicione o cursor em B8 e selecione até E8, escolha o botão estilo porcentagem e sua planilha ficará assim: Figura 39 – Modelo de planilha com resultados formatados Pode-se também alinhar, centralizar e reposicionar o conteúdo das células por meio dos botões na quarta linha da tela do Excel. Experimente e salve seu trabalho como “EX1”.Exemplo de aplicação Exercícios para fixação: 1. Calcule em B9 a média mensal para todos os meses. 157 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação 2. Calcule em B10 o maior valor para todos os meses. 3. Calcule em B11 o menor valor para todos os meses. 4. Calcule em F2 a média do trimestre para todas as filiais. 5. Calcule em G2 o máximo valor do trimestre para todas as filiais. 6. Calcule em H2 o mínimo valor do trimestre para todas as filiais. Cuidado com os intervalos e não inclua os totais, porcentuais e outros valores indevidamente. Utilize o assistente de função F(x) para os cálculos com funções. Para as funções maior e menor, é necessário determinar a partir de qual parcela deve-se considerar; portanto, é preciso montar a fórmula/função, determinando com “n” a parcela. Exemplo: =maior(B2:B6;1) 7. Formate os valores e ajuste a largura das colunas. 8. Salve seu arquivo. Sua planilha deve ter ficado com estas fórmulas: Figura 40 – Modelo de planilha com os resultados formatados Respostas (em formato de fórmulas, estão para os endereços iniciais): 1. B9: =MÉDIA(B2:B6) arraste para os demais. 2. B10: =MAIOR(B2:B6;1) arraste para os demais. 158 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 3. B11: =MENOR(B2:B6;1) arraste para os demais. 4. F2: =MÉDIA(B2:D2) arraste para os demais. 5. G2: =MÁXIMO(B2:D2) arraste para os demais. 6. H2: =MÍNIMO(B2:D2) arraste para os demais. Aprimore seus conhecimentos com o exemplo seguinte: Clique na guia Plan2 e renomeie para “Ex2”. Digite a seguinte planilha: Figura 41 – Modelo de fluxo de caixa pessoa física Para realizar os cálculos, utilize sempre fórmulas e os dados a seguir, elaborando para cada item a respectiva fórmula a partir do endereço B9: Figura 42 – Percentuais para os cálculos do fluxo Os demais valores são digitados (B4 até B6). Execute os cálculos com fórmulas e procure usar índices para os percentuais. 159 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação Exemplo: 30% é 0,3 (vírgulas nas fórmulas). Em B7 e B15, utilize a função autossoma para cálculo dos totais (receita e despesa). Para calcular o saldo anterior, primeiro calcule o saldo atual em janeiro (total receitas – total despesas). Posicione o cursor em C4 e digite = B16 > enter. Sua planilha deve ter ficado assim: Figura 43 – Inserindo as fórmulas Caso sua planilha não tenha ficado dessa forma, acompanhe estas dicas: 7.3.2 Endereçamento automático, vinculando dados e planilhas O endereçamento automático, vinculando dados e planilhas, é utilizado para interligar células, levar valores para outras células, evitando, assim, uma nova digitação; pode ser utilizado também para transferir um valor para outra guia. Posicione o cursor com o mouse no endereço C4 e digite = B16. Repare que o saldo anterior, que vem do cálculo do saldo atual em janeiro, é transferido e automatiza a planilha. Pode ser usado também para trazer um texto, números, enfim, qualquer conteúdo. Utilize o endereçamento automático para trazer os valores fixos de salário, outros e internet, ficando a coluna de fevereiro totalmente com fórmulas. A partir daí, é só arrastar 160 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 fevereiro para todos os outros meses e, no total, finalizar conta a conta, considerando os saldos, obviamente. Sua planilha deve ter ficado assim: Figura 44 – Resultados do fluxo, sem formatação E com as fórmulas: Figura 45 – Modelo de fórmulas Observação Repare que aqui estão as colunas de janeiro (B), fevereiro (C) e total (H). Utilize seus conhecimentos e formate as células com valores de moeda. 161 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação 7.3.3 Funções lógicas (SE, SE com E/OU) Essas funções estão no assistente de função F(x) e são utilizadas de maneira que o Excel analise e entregue uma resposta calculada ou um texto. • Funções lógicas SE: Sintaxe: — Para numérico: =SE(condição; casoverdadeiro; casofalso). — Para textos: =SE(condição; ”casoverdadeiro”; ”casofalso”). Exemplo: =SE(E5>=7; ”aprovado”; ”reprovado”). Observação Para textos, “caso verdadeiro” e “caso falso” devem estar entre aspas com o texto a ser utilizado. • Funções lógicas SE com E/OU Sintaxe: — Numérico: =SE(E(condição1; condição2) ;casoverdadeiro; casofalso). — Texto: =SE(E(condição1; condição2); ”casoverdadeiro”; ”casofalso”). Exemplo: =SE(E(E5>=7;F5<=4,5); ”aprovado”; ”reprovado”). Observação Quando se utiliza SE com E / OU, temos que usar duas condições. lembrete Para textos, “caso verdadeiro” e “caso falso” devem estar entre aspas com o texto a ser utilizado. Para cálculo numérico, colocar fórmula sem as aspas. Para os casos com OU, substitua no local do E. Mas, cuidado, pois o Excel considera válida a primeira ocorrência encontrada. 162 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Utilizando a planilha “EX2”, vamos demonstrar um condicional SE simples textual. — Posicione o cursor em A17 e digite “análise saldo”. — Posicione o cursor em B17 e digite: =SE(B16>0;”APLICAR”;”RESGATAR”) — Agora arraste para os demais meses, incluindo a coluna H total. Sua planilha deve estar assim: Figura 46 – Resultado da função =SE Podemos também utilizar o condicional SE para um cálculo; quando o saldo atual for maior que R$ 1000,00, calcule 30% para reinvestir; caso contrário, não faça nada: — Posicione o cursor em A18 e digite investir. — Posicione o cursor em B18 e digite: =SE(B16>1000;B16*0,3;0) — Agora arraste para os demais meses, incluindo coluna H (total). Observação No condicional SE, podem-se encaixar no caso verdadeiro e falso outros condicionais, desde que se respeite sempre uma condição para uma resposta válida, verdadeira, e uma opção inválida, falsa, ou simplesmente zera-se o caso falso, como fizemos anteriormente. 163 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação Sua planilha deve estar assim: Figura 47 – Resultado da função =SE com exemplo numérico Observação Borg: Observe que, ao se utilizar a função =SE, o Excel lê os endereços, decide pelos critérios estabelecidos e decide se aplicar. No outro exemplo, calcula o valor e a remuneração. Essa função é importantíssima, pois traz um nível de automação na medida em que também decide por meio dos critérios estabelecidos, dentro da função. • Funções de procura e referência (PROCV, PROCH) Essas funções servem quando se necessita analisar uma tabela com faixas e/ou intervalos para análise, uma vez que, com o condicional, não se consegue utilizar mais de uma faixa de dados. Também pode-se utilizar o assistente de função F(x) ou digitar diretamente a função, como segue. Sintaxe: — para procura vertical: =PROCV (valor_procurado;matriz_tabela;num_índice_coluna) — para procura horizontal: =PROCH (valor_procurado;matriz_tabela;num_índice_linha) 164 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Onde: — valor procurado é a chave que liga as planilhas; — matriz tabela é a tabela auxiliar com as faixas e índices procurados; e número índice linha ou coluna é a posição em que a coluna ou índice está na matriz tabela (uma tabela auxiliar, criada na mesma planilha ou em uma outra guia). A procura e referência é uma função que automatiza o trabalhode se procurar valores, índices e correspondências na mesma planilha ou em outra, como exemplo, um banco de dados, encontrando valores ou textos e trazendo-os para a planilha ativa (área de trabalho). 7.4 Planilhas profissionais Olá, bem-vindo! Eu sou o Borg. Como você já me conhece, vamos, neste tópico, utilizar planilhas eletrônicas e, para isso, utilizaremos os comandos para formatação de planilhas e configuração de páginas, a partir de comandos que atuam na planilha toda, e mais adiante uma abordagem com gráficos e banco de dados. 7.4.1 Configuração de páginas Na versão 2007 do Excel, esse comando está na aba layout da página, na qual temos os botões para margens, orientação, tamanho, área de impressão, plano de fundo, impressão de títulos, largura e altura das colunas e quebras. Na caixa opções de planilha, temos opções de linha de grade e títulos, e na caixa de organização, os elementos de direcionamento dos desenhos. Esses comandos atuam em toda a sua planilha. Aba layout da página: Figura 48 – Menu de comandos Excel Já nas versões anteriores, com o comando arquivo > configurar página, acessa-se uma caixa de diálogo em que se pode, por meio de guias, selecionar: • Páginas: — mudar orientação do papel em retrato e paisagem; 165 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação — ajustar a planilha para toda a cobertura do papel; — ajustar em uma só página; — selecionar tamanho do papel a partir da seta para opções; — configurar a qualidade de impressão; — configurar o número na primeira página; — visualizar impressão, também por botões na terceira linha do Excel. • Margens: Na aba Layout da página, temos os botões para margens, no qual, clicando na flechinha para baixo, abrem-se as opções para: Figura 49 – Menu de layout da página — definir todas as margens, com medidas: pode-se utilizar as medidas preestabelecidas ou determinadas pelo usuário; — ajustar tamanho do papel e a área de impressão; — centralizar a planilha horizontal e verticalmente. • Cabeçalho e rodapé: Uma atenção importante nessa opção para cabeçalho e rodapé. Na versão 2007, esses comandos estão na aba Inserir, na caixa texto, como exibido a seguir: Figura 50 – Menu de comandos da aba inserir 166 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 — para definir cabeçalho e rodapé padrão e personalizado, inserir número de páginas em diversos formatos com data e hora, inserir o nome do arquivo ou uma imagem, temos opções para preenchimento com botões e com caixas de texto, com diversas opções de design (na aba de mesmo nome), como segue: Figura 51 – Menu de comandos da aba ferramentas de cabeçalho e rodapé • Planilha: — definir manualmente a área de impressão; — impressão de títulos, com a repetição na parte superior de cada página impressa; — impressão de colunas à esquerda, em cada página impressa; — impressão de linhas de grade, p&reto e branco, cabeçalhos de linha e coluna; — ordenação da página. Na versão 2007, temos as opções na aba layout da página: Figura 52 – Menu layout da página > área de impressão Seleciona-se a opção área de impressão e definir área de impressão. Para as versões mais antigas, no comando arquivo > área de impressão, essa é reconhecida automaticamente como a planilha inteira, mas, se for necessário imprimir somente parte da planilha, pode-se, com esse comando, selecionar e redefinir uma nova área de impressão. Também nesse comando, temos a opção para limpar a área de impressão e redefinir outra posteriormente. 167 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação Se for preciso uma quebra de página fora da proposta pelo comando, pode-se utilizar o comando: — Inserir quebra de página na posição em que estiver o ponteiro do mouse (linha). A impressão, ao chegar naquele ponto, muda de página automaticamente, contando normalmente a paginação. Figura 53 - Menu de layout da página > inserir quebra de página Na versão 2007, encontramos, na aba layout da página, o botão de quebra, e determinamos o local desejado na planilha. 7.4.2 Montagem de relatório Com os comandos de impressão, já se consegue dar um layout profissional para a planilha que, com os cabeçalhos, adquire um aspecto moderno. Juntamente com os comandos de banco de dados > subtotais, podem-se montar relatórios com grupos e subgrupos de informações, uma vez que a planilha obedeça aos critérios de banco de dados, que estudaremos mais adiante. 7.4.3 Autoformatação Na versão 2007 do Excel, temos, na Aba início, as opções de formatação: Figura 54 – Menu aba início Encontramos diversos modelos já predeterminados, a partir dos quais se pode escolher e atribuir à planilha ativa na área de trabalho, com diversas opções e tons de cores definidas em: clara, média e escura. 168 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Figura 55 – Menu formatar como tabela > opções de formatos Nas versões anteriores, com o comando formatar > autoformatação, pode-se escolher um formato de layout predefinido que será aplicado na impressão e na área de trabalho. Utilize esse comando nas planilhas “EX1” e “EX2”, como exercício para conhecer e aprimorar seus conceitos em planilhas eletrônicas. 7.4.4 Gráficos Para a versão 2007, utilizaremos uma planilha já digitada, como a utilizada em nosso livro-texto como segue. Caso você não a tenha salvado, será necessário digitá-la e, com ela, na aba inserir, encontramos as opções de gráficos, na caixa de opção. Figura 56 – Menu inserir com opções de gráficos 169 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação A montagem do gráfico, é extremamente simples e imediata usando essa nova versão do Excel, basta seguir estas instruções: 1. Estacione seu cursor em uma célula ativa da planilha (dentro da área de dados, as instruções digitadas), ou determine a área com o mouse, selecionando-a com as células A1:E7, como segue: Figura 57 – Modelo de dados selecionados na planilha 2. Selecione a aba inserir; 3. Escolha o tipo de gráfico entre os disponíveis, o Excel constrói automaticamente o gráfico. Observação Tome cuidado somente com os gráficos circulares, pois nesses é necessário incluir somente um evento de cada vez. • Tipos de gráficos: Figura 58 – Botão de gráfico tipo colunas Esse tipo de gráfico expressa mais claramente comparações entre eventos estudados a partir dos quais se deseja demonstrar crescimento ou declínio, um comparativo de desempenho ou uma comparação entre meses, como neste exemplo: Figura 59 – Exemplo de gráfico de colunas 170 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Observe que o Excel inclui automaticamente as informações de total do mês que, nesse caso, foram incluídas manualmente no intervalo, para excluir a linha em que estão calculados os percentuais. Figura 60 – Botão de gráfico tipo linhas Esse tipo de gráfico de linhas na horizontal expressa mais claramente comparações entre eventos estudados nos quais se deseja demonstrar o crescimento ou declínio, mas com um comparativo de desempenho com uma análise de um período mais longo e, nesse caso, as filiais e os meses dão uma noção de espaço ou tempo decorrido, como neste exemplo: Figura 61 - Exemplo de gráfico de linhas Figura 62 – Modelo de botão de gráfico de barras O gráfico de barras expressa comparações entre eventos estudados nos quais se deseja demonstrar um comparativo de desempenho com uma visão da performance,da liderança entre as filiais e dos meses, dando uma noção de quem está à frente, como neste exemplo: Figura 63 – Exemplo de gráfico de barras 171 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação Figura 64 – Botão de gráfico de área O gráfico de área expressa comparações entre eventos estudados nos quais se deseja demonstrar um comparativo de desempenho com uma visão da performance, da liderança entre as filiais e dos meses, dando uma noção de quem está à frente e também de espaço ou tempo decorrido, assim como no de linhas. Veja neste exemplo: Figura 65 – Exemplo de gráfico de área Figura 66 – Botão de gráfico de dispersão Gráfico de dispersão, também conhecido como “gráfico X Y”, compara pares de valores; use-o quando os valores empregados não estiverem em ordem no eixo X, ou quando representarem medidas separadas. Veja no exemplo: Figura 67 – Exemplo de gráfico de dispersão 172 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Figura 68 – Botão de gráfico tipo pizza Como sabemos, o Excel monta automaticamente os gráficos, o que é uma imensa ajuda, mas, neste caso dos gráficos circulares, isso gera um problema, pois inclui todos os eventos na montagem do gráfico, não expressando a verdadeira informação, pois, ao se usar esse gráfico, a somatória de um evento não deve ultrapassar 360 graus, ou 100% do evento, e, como inclui o total do mês em uma fatia, isso provoca um erro matemático. Observe, no gráfico a seguir, a fatia da esquerda, de cor marrom-claro, e a seleção de dados na planilha, que está toda em azul (selecionada). Figura 69 – Exemplo de planilha e gráfico de pizza Para montar um gráfico corretamente, selecione os dados que devem participar do gráfico com o mouse ou teclado (observe que, na planilha a seguir, esses dados de cada mês e do total do trimestre de todas as filiais estão demarcados em tons de azul, demonstrando a diferenciação) e, depois disso, por meio da aba inserir, escolha o gráfico e o modelo que mais lhe agradar, da mesma forma que escolhemos os de pizza, no formato 3D. 173 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação Figura 70 – Exemplo de dados selecionados na planilha Figura 71 – Exemplos de gráficos de pizza, mês a mês e total do trimestre Como nesse caso, se for preciso demonstrar as três filiais ou totais, selecione o evento que traz a somatória das filiais ou dos meses e, no caso da necessidade de trabalhar por filiais ou meses, faça um gráfico para cada um separadamente, como foi demonstrado. Para mudar de cor cada fatia: 1. clique com o mouse no contorno da fatia duas vezes para selecionar somente a desejada, repare que aparece uma marca de seleção; 2. selecione a opção preenchimento da forma no menu de opções que fica disponível ao selecionar a fatia; 3. ao clicarmos na flechinha para baixo, aparecem as opções de cores e as opções de usar texturas e outras figuras no preenchimento; 4. se desejar inserir uma figura ou foto no fundo do gráfico, selecione o fundo e repita o item 2. 174 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Figura 72 – Menu de ferramentas de gráfico Observe que a fatia da filial 3, no gráfico do total do trimestre, mudou da cor verde para a amarela. Para explodir uma fatia: 1. selecione a fatia desejada; 2. segurando o botão esquerdo do mouse em cima da fatia, arraste para fora do gráfico. Automaticamente a fatia será destacada, veja a seguir: Figura 73 – Exemplo de gráfico com fatia explodida Para desfazer a explosão da fatia, somente arraste para dentro do gráfico novamente ou use o botão voltar, no canto superior esquerdo de sua tela. O Excel monta diversos outros tipos de gráficos e diversos modelos de apresentações, como os demonstrados a seguir, nas opções de inserir gráfico, ou pelo botão: 175 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação Figura 74 – Botão de gráfico outros gráficos A partir desse botão, estão disponíveis as opções de ações, superfície, rosca, bolhas e a opção de todos os tipos de gráficos, como a seguir: Figura 75 – Menu inserir gráficos > gráficos Ainda utilizando as opções de gráficos e formatos, encontramos modelos com legendas, títulos e demais itens que tornam mais claro a demonstração das informações. Para as versões anteriores do Excel, entre no comando inserir > gráfico, ou utilize o botão assistente de gráfico para escolher as opções de gráficos, formatos e efeitos disponíveis em uma caixa de diálogo chamada assistente de gráficos e, com este, em sete etapas você seleciona e monta um gráfico com extrema facilidade. Atenção somente para os gráficos do tipo torta, pizza e rosca, pois o assistente de gráfico monta sozinho tais gráficos, e corre-se o risco de considerar colunas indevidamente na montagem do gráfico, como já explicado anteriormente. Utilize a combinação da tecla CTRL e a seleção via mouse para definir os intervalos válidos para o gráfico e monte-os livremente. Pelo assistente de gráficos, nas versões anteriores, podemos, em quatro e até no máximo sete etapas, montar gráficos completos, de aparência profissional, ricos e limpos, com títulos e legendas para os eventos e os eixos X e Y. 176 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Quanto à impressão de gráfico, o Excel o reconhece tal como uma planilha; deve-se estar próximo aos dados de montagem ou em outra guia escolhida, na qual o programa montará o gráfico. Isso funciona igualmente em todas as versões. lembrete Borg: Essas considerações valem para as versões anteriores do Excel. Nos casos de explosão de fatias, clique com o cursor duas vezes rapidamente sobre a fatia e arraste para fora com o mouse para ser automaticamente explodido. Para troca de cores, basta clicar uma vez sobre a curva ou barra, escolher nova cor na palheta e alterar. Quando o gráfico é inserido na planilha, podemos mudar seu tamanho, posicionando o mouse nos vértices (cantos) e arrastando, pois a ampliação e/ou redução é proporcional; ou pelos nós, no meio das bordas. Nesse caso, a ampliação ou redução é executada somente na posição escolhida. Essa opção funciona em todas as versões. Saiba mais Para aprimorar seus conhecimentos em tecnologias de redes e afins e demonstrar uma inter-relação do conteúdo, segue esta dica, assita ao filme: A REDE social. Titulo original: The social network. Direção: David Fincher, EUA: Sony Pictures, 2010, 121 min. 8 BancO dE dadOS 8.1 conceito de banco de dados Um banco de dados é a reunião de informações em uma planilha, como uma lista de informações organizadas, obedecendo à distribuição em uma planilha da seguinte maneira: 1. nas colunas, com os títulos do conteúdo, recebem o nome de campo; 2. nas linhas, temos os registros, que devem ser do mesmo proprietário; 3. ao se acionar os comandos de banco de dados, automaticamente o Excel utilizará essas referências como instruções para “procurar” os dados que atendam aos critérios e as pesquisas a serem executadas. 177 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação O Excel executa a leitura do banco de dados como os ocidentais, da esquerda para a direita, de cima para baixo. Ao encontrar uma coluna em branco, ele lê a próxima linha e, ao encontrar uma linha em branco, ele entende que o banco de dados acabou. Nas versões anteriores do Excel, bem como no atualExcel 2007, com o comando dados, podemos classificar, filtrar e fazer subtotais; basta, para isso, a planilha obedecer aos seguintes critérios, rigidamente: • não ter linhas e colunas em branco; • possuir em todas as colunas cabeçalho, que serão o nome de campo; • não pular linhas, pois o Excel entenderá que o banco de dados terminou. 8.2 Uso de banco de dados Classificar dados: É a ordenação alfabética de registros, de A para Z (crescente) e de Z para A (decrescente). Também podem-se utilizar os botões da terceira linha da tela nas versões anteriores, ou no Excel 2007 com a aba dados e o botão classificar, como a seguir: Figura 76 – Menu de dados Ao utilizar o comando ou botão de classificar, colocamos em ordem alfabética no caso dos textos e em ordem numérica no caso de valores numéricos, facilitando o uso e a organização dos dados nas ordens crescente e decrescente, e facilitando análises dos dados, como a montagem de uma curva ABC, a montagem de um Balance ScoreCard, ou uma analise estatística. Ao acionar o botão, surge a caixa classificar, na qual temos as opções de: 1. Coluna > classificar como, com as opções de nomes de campos, escolha a que necessitar. 2. Classificar em valores, cor da célula, cor da fonte e ícone, escolha de acordo com sua necessidade. 3. Ordenar de A para Z (crescente), ou Z para A (decrescente), conforme sua escolha. 178 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 A seguir, um exemplo da caixa classificar: Figura 77 – Menu de dados > opção classificar Mais uma missão para fixar seus conhecimentos: Agora experimente esse comando ou o uso do botão, dependendo da versão que tem à sua disponibilidade, e treine o uso dessa opção para fixar esse importante recurso do Excel. 8.2.1 Filtrar dados Nas versões anteriores, com o comando dados > filtrar > autofiltro: em uma planilha, abrem-se botões com flechas para selecionar opções ou até mesmo personalizar a busca com intervalos conjugados com e e ou. Na versão Excel 2007, encontramos, na aba dados, o botão filtro, que abrirá as flechas nos nomes de campos (linha 1). Figura 78 – Menu dados, opção filtro 179 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação A partir desses botões com as flechinhas, selecione o item que procura como critério de pesquisa, neste caso, filtro de número: Figura 79 – Menu de filtros, opção filtros de número Ou, na opção personalizar filtro (a última da lista), na qual podemos escolher diversas opções de personalização, procurando: • valores iguais; • maiores; • maiores ou igual. Provocando assim um intervalo, veja o exemplo a seguir: Figura 80 – Menu de dados, opção personalizar autofiltro 180 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Obtendo assim, como resultado: Sabemos que essa é uma planilha de banco de dados e filtrada, pois os números de linhas aparecem na cor azul. Figura 81 – Exemplo de planilha com o autofiltro ativado Veja que, no banco de dados filtrado, o Excel entrega a planilha com os valores pertencentes ao critério estabelecido com os registros pertencentes à pesquisa. Observação Borg: Sempre antes de filtrar novos dados, limpe a pesquisa feita, com a opção LIMPAR. Figura 82 – Exemplo de planilha filtrada A planilha volta ao formato íntegro do banco de dados, pronto para uma nova pesquisa. 181 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação Figura 83 – Exemplo de planilha com o filtro desativado “limpo” Observe que o número das linhas está em preto novamente. 8.2.2 Subtotais Pode-se quebrar o relatório em níveis (na caixa A cada alteração em:), totalizar dados (na caixa Usar função, escolha a operação matemática), pela escolha de campos (como vemos na caixa Adicionar subtotal a:) escolhendo a quebra pelo nome de campo. Relatórios profissionais, usando em seu relatório a opção que encontramos na aba Dados no Grupo Estrutura de tópicos, a opção Subtotal. Figura 84 – Menu de dados, opção botão subtotal Ao selecionar a opção de quebra na caixa de subtotal, o Excel elabora a quebra no campo indicado e a soma (calcula a operação desejada) nas parcelas que pertencem ao critério. Uma dica: desligue o filtro e observe que nosso banco de dados está sem aquela linha de totais e do cálculo dos percentuais. 182 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Ao executar o comando clicando no botão ok, temos como resultado: Figura 85 – Exemplo de planilha com o subtotal Temos a quebra por filial, gerando um total enumerado (1 total) e o total geral, automaticamente gerado pelo Excel. Com esses comandos, botões e dicas, espero que consiga aumentar o uso do Excel e, com isso, aumentar sua produtividade, elaborando planilhas, gráficos, analisando banco de dados e montando relatórios mais profissionais. Para que esses objetivos sejam alcançados, também é necessária a prática dessas instruções com o Excel em um microcomputador, pois somente a pratica levará à excelência que o mercado de trabalho exige dos profissionais. lembrete Borg: Espero ter apresentado essa ferramenta de produtividade de maior importância no mercado de trabalho em todas as áreas, e ainda mais requisitada na área de negócios, como administração e ciências contábeis. 183 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação Exemplo de aplicação Case 1 – Comparando planilhas utilizando a função =SE Publicado em: 19 jan. 2009, por Fábio Vianna Situação: Temos duas planilhas com a mesma estrutura, mas valores diferentes, e queremos comparar valores entre os dois meses (Exemplo: Janeiro.xls e Fevereiro.xls). Podemos confrontar duas planilhas em arquivos diferentes usando novas planilha e fórmulas. Veja como: • Crie uma nova pasta de trabalho (Exemplo: Comparação.xls). • Na célula A1 da nova planilha criada, insira a seguinte fórmula: =SE([Janeiro.xls]Plan1!A1<>[Fevereiro.xls]Plan1!A1,”Diferente”,”Igual”) Resultado: Na célula A1 da nova planilha criada, teremos o valor diferente, caso os valores da célula A1 das planilhas Janeiro.xls e Fevereiro.xls não coincidam, ou teremos valor igual, caso coincidam. Repare que podemos utilizar vários operadores. Se houver a necessidade de descobrir a diferença entre dois valores numéricos, a fórmula ficaria da seguinte maneira: =([Janeiro.xls]Plan1!A1-[Fevereiro.xls]Plan1!A1) Fonte: <http://dicasdeexcel.com.br/site/2009/01/comparando-planilhas-utilizando-a-formula-se/>. Acesso em: 29 mar. 2011. Case 2 – Separando as palavras em mais de uma coluna Publicado em: 23 jul. 2010, por Fábio Vianna. Um desafio no Excel, separar palavras. Dessa vez temos uma ferramenta que pode nos ajudar. Digite a seguinte planilha, respeitando as células e endereços, como a seguir: 184 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Figura 86 Selecione a coluna clicando na letra A: Figura 87 No menu dados, clique em texto para colunas: Figura 88 Vamos usar a separação por tipo delimitado, pois o que determina que seja separado é a existência de espaços.. 185 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação Figura 89 Clique em avançar. Escolha a opção de delimitador: espaço. Figura 90 Clique em avançar. Determine o início da separação partindo da célula B1, para não perder os dados originais.Figura 91 186 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Clique em concluir. E você terá os textos separados pelas colunas como no exemplo anterior. Fonte: <http://dicasdeexcel.com.br/site/2010/07/separando-as-palavras/>. Acesso em: 29 mar. 2011. Case 3- Trabalhando com horas Publicado em: 20 jul. 2010, por Fábio Vianna Criaremos uma planilha de cálculo de horas trabalhadas e valor a receber. Figura 92 187 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação Primeiro, precisamos calcular a quantidade de horas trabalhadas. Considerando a parada teremos que somar os dois períodos trabalhados. Figura 93 É preciso utilizar os parênteses para priorizar cálculos. Arraste a lógica até a linha 22. Para saber o total de horas trabalhadas, basta utilizarmos a função soma. Figura 94 Ao finalizarmos a função, o Excel retorna o valor 10:35, pelo fato de calcular como relógio, sempre que chegar às 23:59, ele zera e começa a contar novamente. 188 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 É preciso fazer uma formatação que aceite mais de 24 horas. Figura 95 Selecione a célula a ser formatada, em formatação de número, escolha a opção 37:30:55. Figura 96 Agora, vamos calcular o valor a receber. Para facilitar a criação da fórmula, vamos nomear a célula de valor hora. Selecione a célula de valor, e na caixa de nome especifique um nome para a célula, não se esqueça de pressionar enter. Figura 97 Já com a célula nomeada, criaremos a fórmula. Figura 98 189 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação Ao criarmos a fórmula de multiplicação de valor moeda por valor em hora, o Excel automaticamente interpreta que um dos dados é equivocado e corrige, fazendo uma divisão. Ao acreditar que o valor por hora (expresso em moeda) é um valor equivocado, o valor hora é dividido por 24, sendo assim convertido para horas. Ou seja, o Excel pega o valor R$ 25,00, e divide por 24 (número de horas do dia), resultando 1,04166666666667. Ao multiplicar por 9 o valor: R$ 9,38 é retornado. É preciso reverter o cálculo feito pelo Excel, ou seja, multiplicar por 24. O cálculo ficará da seguinte forma (retornando o valor correto): Figura 99 Na célula G23, faremos o cálculo da soma: Figura 100 Fonte: <http://www.dicasdeexcel.com.br/site/2010/07/trabalhando-com-horas/>. Acesso em: 29 mar. 2010. 190 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Observação Borg: Espero que você tenha um ótimo estudo sobre a teoria e prática do Excel em microcomputador, pois somente a leitura deste não é suficiente para prepará-lo aos desafios de sua profissão, desejo-lhe, caro leitor e aluno, sucesso. resumo Nesta unidade, mostramos os comandos, atalhos e botões para a utilização de planilhas eletrônicas, exemplificando o uso com o software MS - Excel da empresa Microsoft, por meio do qual podemos usar toda a potencialidade e facilidade para execução de gráficos. Partindo da premissa de elaboração de planilhas eletrônicas, nas quais, com algumas entradas, podemos ter cálculos atualizados automaticamente, ou com poucas atualizações. Numa primeira abordagem, explicou-se a planilha, a navegação, os menus de comandos e os botões, abordando seu uso e aplicação, a construção de planilhas automáticas a partir de fórmulas e a correta utilização de endereçamentos dessas fórmulas. Na segunda abordagem, o uso de gráficos para a visualização dos dados de maneira visual e com os diversos modelos disponíveis no Excel. Por fim, tivemos uma abordagem visando aprimorar os relatórios com formatos mais modernos, além dos predeterminados nos botões da planilha e, com o uso da ferramenta de banco de dados, explorar a seleção específica de dados e a montagem de relatórios automáticos. Esta unidade não pretendeu explorar todas as funções da planilha, mas mostra as mais utilizadas e importantes para a atuação dos profissionais da área de negócios. Exercícios Questão 1 (prova de Analisa de Sistemas Tipo1, FGV 2010 – CODESP/SP). Um rapaz deseja usar o Excel para analisar as condições de financiamento para compra de uma TV LCD em uma grande loja no 191 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação shopping. A figura abaixo mostra uma parte da planilha Excel, assim como a caixa de diálogo Atingir Meta já preenchida com as seguintes informações e referências a células: Definir célula: $B$4 Para valor: 200 Alternando célula: $B$ A fórmula na célula B4 é =PGTO (B3/12;B2;B1). Ao se pressionar o botão OK na caixa Atingir Meta, qual é o resultado esperado? A) O valor da prestação mensal resultante do financiamento de uma TV de R$ 50,00, num plano de 36 meses com juros mensais de 6,00%. B) O valor da prestação mensal resultante do financiamento de uma TV de R$ 50,00, num plano de 36 meses com juros anuais de 6,00%. C) #VALOR, porque a célula B1, onde deveria ser informado o valor do preço da TV, está em branco. D) O valor do preço da TV que resultará numa prestação mensal de R$ 50,00, num plano de 36 meses com juros mensais de 6,00%. E) O valor do preço da TV que resultará numa prestação mensal de R$ 50,00, num plano de 36 meses com juros anuais de 6,00%. Resposta correta: Alternativa E. Análise das alternativas A) Alternativa Incorreta. Justificativa: o resultado esperado é o valor da TV e não o da prestação, sendo este um valor conhecido e é informado na caixa Para valor. 192 Unidade III Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 B) Alternativa Incorreta. Justificativa: o resultado esperado é o valor da TV e não o da prestação, sendo este um valor conhecido e é informado na caixa Para valor. C) Alternativa Incorreta. Justificativa: o resultado esperado é o valor da TV, logo ele não deveria estar preenchido, de maneira que o resultado da função Atingir Meta o preencherá. D) Alternativa Incorreta. Justificativa: o resultado esperado é o valor da TV, porém a taxa de juros da prestação é anual, pois a célula B3 é dividida por 12 na formula da célula B4. E) Alternativa Correta. Justificativa: o resultado esperado é o valor da TV (R$ 1.643,55), pois têm-se os valores das outras variáveis e a fórmula =PGTO(B3/12;B2;B1) define a taxa de juros como anual, sendo a célula B3 é dividida por 12. Questão 2 (FFC2011 – INFRAERO). Considere que, em uma planilha eletrônica do Microsoft Excel/2003, do pacote de aplicativos Office XP, versão em português, na sua configuração padrão, o valor da célula B1 foi obtido através da aplicação da fórmula =2+3. Um usuário seleciona e copia a célula B1 e, em seguida, seleciona a célula C1 e executa a operação de “colar”. Com base nessas informações, após a operação, é correto afirmar: A) A célula C1 passa a exibir o valor 5. B) A célula C1 fica bloqueada, impossibilitando alterar seu conteúdo. C) A célula B1 fica bloqueada, não sendo possível alterar seu conteúdo. D) Se o valor da célula B1 for alterado para 10, a célula C1 também passará a exibir o valor 10. E) O Excel exibe uma mensagem de erro informando que não é possível copiar células que contenham fórmulas. Resolução desta questão na plataforma. 193 Re vi sã o: L ua nn e Ba tis ta / Vi rg ín ia B ila tt o - Di ag ra m aç ão : L éo - 1 4/ 08 /2 01 2 Tecnologias da informação FIgURAS E IlUSTRAçõES Figura 1 Diagrama para visão sistêmica. CHIAVENATO,I. Administração de Empresas. São Paulo: Makron, 2001, p. 108. Figura 2 Principais Papéis dos Sistemas de Informação e o apoio que podem dar às empresas. O’BRIEN, J. A. Sistemas de informação e as decisões gerenciais na era da internet. 2ª ed. São Paulo: Saraiva, 2007, p. 53. Figura 3 Componentes de um Sistema de Informação. O’BRIEN, J. A. Sistemas de informação e as decisões gerenciais na era da internet. 2ª ed. São Paulo: Saraiva, 2007, p. 151. Figura 4 Hierarquia de memória de cinco níveis. Disponível em: <http://ivairsouza.com/hierarquia-de-memoria. html>. Acesso em: 15 mai. 2010. Figura 5 Placa de rede Fast Ethernet. Disponível em: <http://www.hardware.com.br/livros/hardware-manual/ cabo-par-trancado.html>. Acesso em: 29 mar. 2011. Figura 6 Cabo de par trançado. Disponível em: <http://www.hardware.com.br/livros/hardware-manual/cabo- par-trancado.html>. Acesso em: 29 mar. 2011. Figura 7 Cabo Coaxial descascado. Disponível em: <http://www.hardware.com.br/livros/hardware-manual/cabo- coaxial.html>. Acesso em: 29 mar. 2011. Figura 8 Cabo de par trançado e cabo coaxial. Disponível em: <http://www.hardware.com.br/livros/hardware- manual/cabo-coaxial.html>. Acesso em: 29 mar. 2011. 194 Figura 20 Concentração de redes de fibra óptica nos EUA. Disponível em: <http://www.07011979.org/page/2>. Acesso em: 19 jul. 2012. Figura 31 ERP e seus componentes principais para empresa de pequeno porte. NANINI, U. J. V. O impacto dos sistemas de ERP na competitividade da empresa industrial de médio porte. Dissertação de mestrado em Administração do Instituto de Ciências Sociais e Comunicação da Universidade Paulista. São Paulo, 2001, p. 70. Figura 32 Estrutura conceitual dos sistemas de ERP e sua evolução desde o MRP. Fonte: CORRÊA, H. et al. Planejamento, programação e controle da produção. 4ª ed. São Paulo: Atlas, 2001, p. 400. Figura 33 Fórmula da produtividade. Fonte: NANINI, U. J. V. O impacto dos sistemas de ERP na competitividade da empresa industrial de médio porte. Dissertação de mestrado em Administração do Instituto de Ciências Sociais e Comunicação da Universidade Paulista. São Paulo, 2001. REFERênCIAS Audiovisuais HACKERS, piratas da informática / Piratas de computador. Titulo original: Pirates of silicon valley. Direção: Martyn Burke. EUA: Warner Home Video, 1999, 97 min. FÁBRICA de Loucuras. Titulo original: Gung Ho. Direção: Ron Howard. EUA: Paramount Pictures, 1986, 108 min. Textuais ALECRIM, E. Diferenças entre hub, switch e roteador. Publicado em 8 nov. 2004. Disponível em: <http://www.infowester.com/hubswitchrouter.php>. Acesso em: 29 mar. 2011. BATISTA, E. O. Sistemas de informação – o uso consciente da tecnologia para o gerenciamento. São Paulo: Saraiva, 2004. BOGHI, C. Sistemas de informação: um enfoque dinâmico. São Paulo: Érica, 2002. CHIAVENATO, I. Administração de empresas. São Paulo: Makron, 2001. 195 CHURCHMAN, C. W. Introdução à teoria dos sistemas. Petrópolis: Vozes, 1971. CORRÊA, H. et al. Planejamento, programação e controle da produção. 4ª ed. São Paulo: Atlas, 2001. DRUCKER, P. The coming of the next organization. Boston: Harvard Business, jan./fev. 1988. FÉ, A. L. M. Reforma geral na rede. Publicado em 9 set. 2008. Disponível em: <http://info.abril.com.br/ corporate/infraestrutura/reforma-geral-na-rede.shtml>. Acesso em: 10 jul. 2012. GATES, B. A empresa na velocidade do pensamento. São Paulo: Schwarcz Ltda., 1999. MICROSOFT. Manual do Microsoft Excel 2007. Publicado em fev. 2006. Microsoft Press. Disponível em: <http://www.microsoft.com/brasil/2007office/programs/excel/guide.mspx>. Acesso em: 19 jul. 2012. MICROSOFT. Manual do Microsoft Excel 2003. Atualizado em 19 jun. 2012. Microsoft Press. Disponível em: <http://support.microsoft.com/ph/2512/pt-br>. Acesso em: 19 jul. 2012. MICROSOFT. Guia do Excel 2007: guia interativo de referência de comandos do Excel 2003 para Excel 2007. Publicado em 7 ago. 2007. Microsoft Press. Disponível em: <http://www.microsoft.com/pt-br/ download/details.aspx?id=14650>. Acesso em 19 jul. 2012. MEIRELLES, F. S. Informática – novas aplicações com microcomputadores. São Paulo: Makron Books, 2004. NANINI, U. J. V. O impacto dos sistemas de ERP na competitividade da empresa industrial de médio porte. Dissertação de mestrado em Administração do Instituto de Ciências Sociais e Comunicação da Universidade Paulista. São Paulo, 2001. O’BRIEN, J. A. Sistemas de informação e as decisões gerenciais na era da internet. 2ª ed. São Paulo: Saraiva, 2007. ______. Sistemas de informação. São Paulo: Saraiva, 2005. PINHEIRO, J. M. S. Topologias de redes de comunicação. Publicado em 17 jun. 2006. Disponível em: <http://www.projetoderedes.com.br/artigos/artigo_topologias_de_rede.php>. Acesso em: 10 jul. 2012. PORTER, M. E. Estratégia competitiva – técnicas para análise de indústria e de concorrência. Rio de Janeiro: Campus, 1991. INFO Exame. Edições de maio, junho e julho de 2007. São Paulo: Editora Abril, 2007. REZENDE, D. A.; ABREU, A. F. Tecnologia de informação integrada à inteligência empresarial: o papel estratégico da informação e dos sistemas de informação nas empresas. 5ª ed. São Paulo: Atlas, 2008. 196 Sites <http://dicasdeexcel.com.br>. <http://informatica.hsw.uol.com.br/lan-switch2.htm>. <http://ivairsouza.com/hierarquia-de-memoria.html>. <HTTP://pt.shvoong.com/exact-sciences/25419-planejamento-da- informa%c3%a7%ce%a3o/#ixzz1hxCxjzwz>. http://www.businesslink.gov.uk/bdotg/action/layer?topicId=1074298168 acesso em 29.03.2011 <http://www.gartnergroup.com>. <http://www.hardware.com.br/livros/redes/fibra-optica.html>. <http://www.infowester.com/hubswitchrouter.php> <http://www.juliano.com.br/artigos/universidade/Plano%20Diretor%20de%20Informatica%20-%20 PDI.htm>. <http://www.projetoderedes.com.br/artigos/artigo_topologias_de_rede.php>. <http://www.technowbr.com>. <www.vivo.com.br>. <http://www.gnu.org/philosophy/philosophy.pt-br.html>. Exercícios Unidade I – Questão 1: INSTITUTO NACIONAL DE ESTUDOS E PESQUISAS EDUCACIONAIS ANÍSIO TEIXEIRA (INEP). Exame Nacional de Desempenho dos Estudantes (Enade) 2008: Computação. Questão 30. Disponível em: <http://download.inep.gov.br/download/Enade2008_RNP/COMPUTACAO.pdf>. Acesso em: 09 ago. 2012. Unidade I – Questão 2: INSTITUTO NACIONAL DE ESTUDOS E PESQUISAS EDUCACIONAIS ANÍSIO TEIXEIRA (INEP). Exame Nacional de Desempenho dos Estudantes (Enade) 2008: Computação. Questão 35. Disponível em: <http://download.inep.gov.br/download/Enade2008_RNP/COMPUTACAO.pdf>. Acesso em: 09 ago. 2012. 197 Unidade II – Questão 1: INSTITUTO NACIONAL DE ESTUDOS E PESQUISAS EDUCACIONAIS ANÍSIO TEIXEIRA (INEP). Exame Nacional de Desempenho dos Estudantes (Enade) 2008: Computação. Questão 72. Disponível em: <http://download.inep.gov.br/download/Enade2008_RNP/COMPUTACAO.pdf>. Acesso em: 09 ago. 2012. Unidade II – Questão 2: adaptada de UNIVERSIDADE FEDERAL DE ALAGOAS. UNIVERSIDADE ABERTA DO BRASIL (UAB/Ufal). Instituto de Física – IF. Processo Seletivo de Tutores Presencial e a Distância da Universidade Aberta do Brasil da UFAL: Caderno de prova. (Modalidade do exercício da tutoria: PRESENCIAL). EDITAL N.º 01/2012. Disponível em: <http://www.ufal.edu.br/cied/informes/ Cadenodeprova_FsicaPresencial_EAD_COMPLETO.pdf>. Acesso em: 09 ago. 2012. Unidade III – Questão 1: FUNDAÇÃO GETÚLIO VARGAS. Companhia Docas do Estado de São Paulo. Concurso Público 2010 23/5/2010 – TARDE: Caderno de provas objetivas. Analisa de Sistemas Tipo1. Questão 41. Disponível em: <http://www.fgv.br/fgvprojetos/concursos/codesp10/Arq/provas/Codesp_Analista%20de%20 Sistemas_tipo_1.pdf>. Acesso em: 09 ago. 2012. Unidade III – Questão 2: FUNDAÇÃO CARLOS CHAGAS. INFRAERO. Concurso Público para provimento de cargos de Analista Superior IV: Caderno de prova. Engenheiro Civil – Orçamentação. Questão 59. Disponível em: <http://www.questoesdeconcursos.com.br/prova/ arquivo_prova/24131/fcc-2011-infraero-engenheiro-civil-orcamentacao-prova.pdf>. Acesso em: 09 ago. 2012.198 199 200
Compartilhar