Planilhas Eletrônicas

Introdução

Suponha que você precisa resolver o seguinte problema: calcular a distância entre as cidades de Belo Horizonte e Rio de Janeiro. Você tem então um problema de processamento de dados. A distância em quilômetros é o resultado (saída) desejado. Foram fornecidos os seguintes dados de entrada:

• a distância em milhas de Belo Horizonte a Juiz de Fora.

• a distância em metros de Juiz de Fora ao Rio de Janeiro.

Para computar a distância total em quilômetros, você deve primeiramente fazer cálculos intermediários: convertendo as distâncias de Belo Horizonte a Juiz de Fora e de Juiz de Fora ao rio de Janeiro para quilômetros. Você pode utilizar os resultados destes cálculos intermediários para computar a distância total em quilômetros.

Quando você tem um problema para resolver como o descrito acima, você normalmente possui um conjunto de dados e um conjunto de resultados desejáveis. A melhor forma de fazer essa tarefa é:

1. examinar a saída desejada.

2. examinar os dados de entrada.

3. determinar que cálculos intermediários são necessários para obter os resultados desejados. Se você não pode diretamente obter a saída através dos dados de entrada, você deve ter que fazer alguns cálculos com os dados de entrada antes de você computar os dados de saída.

Podemos esquematizar o problema descrito anteriormente da seguinte forma:

Problema:Calcular a distância entre as cidades de Belo Horizonte e Rio de Janeiro.

Dados de entrada:

• a distância em milhas de Belo Horizonte a Juiz de Fora.

• a distância em metros de Juiz de Fora ao Rio de Janeiro.

Cálculos (Fórmulas):

• conversão de milhas para quilômetros.

• conversão de metros para quilômetros.

• soma de duas distâncias em quilômetros.

Dados de saída:

• distância em quilômetros entre Belo Horizonte e Juiz de Fora.

Daremos agora um outro exemplo de aplicação do uso de planilhas. Suponha que você seja dono de uma pequena mercearia. Seu problema é calcular o valor total de produtos que você possui em estoque. Como dados de entrada, você sabe a quantidade de cada produto está armazenada e o preço unitário de cada um em reais. O cálculo que você deverá executar é somar todos os valores obtidos da multiplicação da quantidade de cada produto pelo seu preço unitário. Este valor final é o dado de saída.

Problema:Calcular o valor total de produtos em estoque de uma mercearia.

Dados de entrada:

• a quantidade de cada produto armazenado.

• o preço unitário de cada produto armazenado.

Fórmulas:

• multiplicação da quantidade de cada produto pelo seu preço unitário.

• soma de quantias monetárias.

Dados de saída:

• o valor total em reais de produtos em estoque.

O problema descrito pode ser representado pelos dados da tabela abaixo:

Nome do produto	Quantidade	Preço unitário	Valor em estoque
Arroz	5	8,50	42,50
Saco para lixo	18	2,00	36,00
Detergente	3	1,80	5,40
		Total em estoque	83,90

A estrutura de uma planilha eletrônica é bastante similar `a de uma tabela. Depois que você examinou os dados de entrada e determinou quais cálculos intermediários serão necessários, você será capaz de construir uma planilha semelhante `a tabela mostrada acima.

Veja a descrição do problema abaixo. Tente identificar os dados de entrada, cálculos (fórmulas) necessários e os dados de saída desejados.

Cenário 1 - Necessidade de controlar uma coleção de livros

Você é um comprador obsessivo de livros. Sua coleção cresce com o passar do tempo. Existe a necessidade de você registrar todas as compras feitas para permitir um melhor controle e evitar perdas.

Seu problema pode então ser descrito assim:

Problema

Você possui um certo número de livros em sua casa que deseja catalogar. Você quer registrar alguns dados dos seus livros tais como título, autor, editora, tipo e preço. Inicialmente, você deseja saber quanto já gastou comprando estes livros, ou seja, a soma total das compras.

Dados de Entrada

Para cada livro devem ser informados:

• título

• autor

• editora

• tipo

• preço

Fórmulas

Você deve utilizar a fórmula de soma para calcular a soma dos preços de todos os livros.

Dados de Saída

O total gasto com a compra dos livros.

Conceitos Básicos

Você deve utilizar um editor de planilhas eletrônicas como o Excel para resolver este problema. A base de uma planilha eletrônica é uma grade de espaços de armazenamento de dados, chamada planilha (mostrada na figura abaixo). Nela você insere os dados que quer guardar, manipular ou analisar. Os espaços são chamados de células. Quando se cria uma planilha as células estão vazias. Ao inserir dados nelas, você constrói e desenvolve uma planilha específica para seu problema.

Antes de qualquer operação em uma planilha eletrônica, como digitar dados nas células, colori-las ou apagar o que foi digitado nelas, você precisa escolher as células nas quais vai trabalhar. Você pode selecionar uma única célula, um bloco de células, uma linha ou uma coluna, ou várias linhas e colunas ao mesmo tempo. Pode também selecionar vários grupos de células. Para selecionar uma célula, basta clicar com o mouse sobre a mesma. A(s) célula(s) selecionada(s) é(são) chamada(s) de célula(s) ativa(s). A importância da célula ativa está em que, após feita a seleção, tudo o que você digitar aparecerá exclusivamente nela. Outras ações que você execute depois da seleção (como colorir células ou apagar seus conteúdos) aplicam-se a todas as células da área selecionada. Selecionamos múltiplas células para disparar ações em bloco, tais como formatar, limpar células ou duplicar dados existentes de uma célula para outras.

Inserção de textos e valores numéricos em células de uma planilha

Há três tipos de dados que você pode inserir em uma planilha: texto, números e fórmulas. Em geral, as planilhas são usadas como tabelas numéricas; o texto é usado também para dar título a linhas ou colunas. É prática comum digitar primeiro esses títulos, de maneira a organizar uma estrutura para os dados e fórmulas que serão inseridos.

No problema acima, temos cinco dados de entrada. Para cada livro, temos seu título, autor, editora, tipo e preço. Portanto, para começarmos a construção desta planilha, devemos criar 5 colunas. É prática comum também colocar um título para a planilha. A planilha deve ficar como a mostrada abaixo:

Cada célula da planilha possui um endereço associado a ela. Este endereço é composto pelo índice da linha e da coluna onde a célula se encontra. Por exemplo, a célula ativa na figura acima se encontra na célula cujo endereço é A4. Depois da inserção dos títulos da tabela, dá-se início ao preenchimento dos valores de cada célula. Para a planilha da pequena biblioteca, poderíamos ter os dados mostrados abaixo:

Repare que a planilha mostrada acima, possui células que foram formatadas, por exemplo, utilizando negrito ou itálico. Há também células de valor numérico que foram formatadas de forma a exibir duas casas decimais. Os guias de utilização contém informações e exercícios sobre como formatar células em uma planilha.

Entre os valores numéricos incluem-se números inteiros e decimais, frações, cifras monetárias, porcentagens, datas e horários. A planilha eletrônica aplica várias regras para detectar se uma determinada seqüência de caracteres digitada numa célula constitui um valor numérico e, caso positivo, de que tipo. Reconhecendo a seqüência digitada como um valor numérico, o programa alinha automaticamente esse valor a direita na célula. É importante ressaltar que a planilha eletrônica só é capaz de realizar cálculos com o conteúdo de uma célula se este foi inserido e reconhecido como valor numérico.

Mesmo nas mais simples planilhas você vai logo querer utilizar fórmulas. Uma fórmula calcula e exibe um valor numa célula baseando-se em números que você lhe fornece, operadores aritméticos (como os de soma e de multiplicação) e referências de células (valores numéricos presentes em outras células da planilha). Quando você decide mudar o valor de uma célula referenciada por uma ou mais fórmulas, todas essas fórmulas que dependem desta referência são automaticamente recalculadas.

Neste exemplo, devemos calcular a soma dos valores dos livros da pequena biblioteca. Colocaremos esse valor na célula de endereço E13 (como mostrado abaixo). Para isso utilizaremos a fórmula de soma.

A fórmula está destacada na planilha mostrada acima. A fórmula SOMA recebe como parâmetro o conjunto (intervalo) de células que devem ter seu valor somado (este intervalo é representado pelo símbolo ":"). Neste caso, as células de endereço E4 até E8 terão seus valores somados. O total (soma) é guardado na célula na qual a fórmula foi inserida.

Existem outras fórmulas que as planilhas eletrônicas normalmente disponibilizam para você. As planilhas eletrônicas costumam trazer diversos tipos de fórmulas tais como matemáticas, estatísticas, financeiras e outras mais. As fórmulas das planilhas facilitam muito seu trabalho pois você não precisa implementar novamente a funcionalidade que elas trazem - elas já estão prontas para serem usadas. Dentre as fórmulas mais usadas podemos citar as que calculam média aritmética, desvio padrão, maior e menor valores de um certo conjunto.

As fórmulas normalmente possuem um ou mais parâmetros e um valor de retorno. Podemos representar esta característica das fórmulas assim:

nome_da_formula(lista_de_parametros): valor_de_retorno

Poderíamos representar a fórmula que calcula uma média aritmética assim:

media(conjunto de valores): média aritmética

Suponha que precisamos calcular a média aritmética dos valores 7, 3, 8, 5, 1, 6 que estão nas células de endereço B4 a B9. Para calcular a média aritmética, basta "chamar" a função passando este conjunto de valores como parâmetro:

media(B4:B9). O valor de retorno seria igual a 5.

Inserção de seqüências numéricas em uma planilha

Vamos supor que você queira dar um número seqüencial para seus livros. Imagine o trabalho que teria se você tivesse 200 livros e quisesse dar um código seqüencial para cada um deles! As planilhas eletrônicas normalmente possuem recursos para facilitar seu trabalho quando você necessita criar seqüências de números. Basta você inserir os dois primeiros números da série e "arrastar" esses valores para as outras células. Assim, todas as células serão preenchidas com valores seqüenciais aos valores iniciais que você digitou. A sua planilha com os códigos seqüenciais ficaria assim:

Cenário 2 - Controle de compra e venda - Livros sofrem depreciação

Suponha agora que você passe a ter interesse em vender alguns livros de sua coleção. Logo, você percebe que não é possível vender os livros pelo mesmo preço pelo qual você os adquiriu (preço de compra). Você decide então vender qualquer livro por 20% a menos do que o seu preço de compra.

Seu problema agora pode ser descrito assim:

Problema

Você possui um certo número de livros em sua casa que deseja catalogar. Você quer registrar alguns dados dos seus livros tais como título, autor, editora, tipo e preço. Você deseja saber quanto já gastou comprando estes livros, ou seja, a soma dos preços de todos. Assumindo que seus livros sofreram uma depreciação de 20%, você deve calcular também o valor que você obteria vendendo todos os livros.

Dados de Entrada

Para cada livro devem ser informados:

• título

• autor

• editora

• tipo

• preço

• taxa de depreciação

Fórmulas

• Cálculo do valor total dos preços de compra dos livros.

• Cálculo do valor atual dos livros considerando a depreciação.

• Soma total dos valores de venda dos livros.

Dados de Saída

• O total gasto com a compra dos livros.

• O valor total da possível venda de todos os livros.

Efetuando operações matemáticas

Para tanto, você deve criar uma nova coluna para armazenar o valor dos livros levando em conta a perda por depreciação. Você obterá uma planilha como a mostrada abaixo:

Repare que para obter o novo valor de cada livro, basta multiplicar o seu preço original por 0,8 (pois os livros sofreram uma depreciação de 20%). Para inserir em sua planilha o valor da taxa de depreciação, você pode criar uma célula contendo este valor e fazer as outras células se referirem a ela.

É interessante observar que você não precisa editar a fórmula em todas as células da coluna, basta editar na primeira e arrastar para as demais. Repare o uso do símbolo $ na fórmula mostrada na planilha acima. Se você não usá-lo, o endereço da célula onde está a taxa de depreciação vai sendo incrementado. O símbolo $ é usado para que isto não aconteça, ou seja, para manter o endereço fixo. No exemplo acima, se não usássemos o $, ao colar a fórmula (1-F15)*E4) da célula F4 para a F5, a fórmula passaria a ser (1-F$16)*E5) . Como queremos que o endereço da célula F15 seja mantido em todas as fórmulas, utilizamos o símbolo $.

Finalmente, basta somar os novos valores depreciados para obter o valor total de venda dos livros.

Cenário 3 - Controle de compra e venda - Livros sofrem depreciação variável

Você está tendo sucesso com a venda dos seus livros. Como bom comerciante, você repara que poderá vender mais livros se o valor da depreciação for maior para os livros mais caros.

Assim, seus livros devem sofrer uma depreciação variável, ou seja, cada livro terá seu valor depreciado de forma diferente. A depreciação será em função do preço original de cada livro. Se o livro custar originalmente até R$ 35,00, a depreciação será de 15%. Se custar mais que R$ 35,00, a depreciação será de 25%. Seu problema agora pode ser estruturado da seguinte maneira:

Problema

Você possui um certo número de livros em sua casa que deseja catalogar. Você quer registrar alguns dados dos seus livros tais como título, autor, editora, tipo e preço. Você deseja saber quanto já gastou comprando estes livros, ou seja, a soma total dos preços. Cada livro terá seu valor depreciado de forma diferente. A depreciação será em função do preço original de cada livro. Se o livro custar originalmente até R$ 35,00, a depreciação será de 15%. Se custar mais que R$ 35,00, a depreciação será de 25%.

Dados de Entrada

Para cada livro devem ser informados:

• título

• autor

• editora

• tipo

• preço

Fórmulas

• Cálculo da soma dos preços de todos os livros.

• Cálculo do valor atual dos livros considerando o novo critério de depreciação.

• Valor total de venda de todos os livros.

Dados de Saída

• O total gasto com a compra dos livros.

• O valor total da possível venda de todos os livros.

Utilizando a fórmula SE

Para fazer o cálculo do valor de venda de cada livro, deveremos:

• verificar o valor de compra do livro

• se esse valor for menor ou igual a R$35,00, aplicar a depreciação de 15%.

• se for maior que R$35,00, aplicar a depreciação de 25%.

Para isto, na coluna "Valor depreciado" em cada célula, utilizaremos a fórmula SE (conforme mostrado na figura abaixo). A fórmula SE possui como parâmetros uma cláusula que é avaliada, uma expressão que é retornada se a expressão for avaliada como verdadeira e outra se for avaliada como falsa.

Vamos analisar os parâmetros da fórmula SE destacada na figura acima:

Ordenação dos dados de uma planilha

Suponha agora que você deseje ordenar os seus livros pela coluna Título.

Para ordenar os dados de sua planilha basta selecionar as células que serão ordenadas e em seguida acionar o menu Data - Sort e escolher a opção "crescente". A planilha ordenada é mostrada na figura abaixo:

Cenário 4 - Controle de compra e venda - Livros sofrem depreciação variável de acordo com uma tabela auxiliar

Seu negócio de venda de livros cresceu. Há necessidade de um maior controle assim como de estatísticas de vendas efetuadas. Para melhorar as vendas, você decide aprimorar o critério de cálculo da depreciação dos livros

Você decide então que o cálculo da depreciação seja dado de acordo com uma tabela auxiliar como a mostrada abaixo:

Preço original	Depreciação
Até R$ 37,00	12%
De R$ 37,01 a R$ 44,00	16%
Acima de R$ 44,01	20%

Seu problema pode ser descrito assim:

Problema

Você possui um certo número de livros em sua casa que deseja catalogar. Você quer registrar alguns dados dos seus livros tais como título, autor, editora, tipo, status de venda e preço. Você deseja saber quanto já gastou comprando estes livros, ou seja, a soma total dos preços. Cada livro terá seu valor depreciado de forma diferente. A depreciação será em função do preço original de cada livro. Se o livro custar originalmente até R$ 37,00, a depreciação será de 12%. Se custar entre R$ 37,01, a depreciação será de 16% e se custar mais que R$ 44,01, a depreciação será de 20%. Os dados devem ser exibidos ordenados de forma ascendente pelo título dos livros. Você deve produzir estatísticas em relação 'a venda dos livros.

Este problema pode ser representado pelo seguinte mapa conceitual.

Dados de Entrada

Para cada livro devem ser informados:

• título

• autor

• editora

• tipo

• status de venda

• preço

Fórmulas

• Cálculo da soma dos preços de todos os livros.

• Cálculo do valor atual dos livros em função da depreciação.

• Valor total de venda de todos os livros.

Dados de Saída

• O total gasto com a compra dos livros.

• O valor total de venda de cada livro levando em conta a nova regra de depreciação.

• Valor total da possível venda de todos os livros.

• Valor total dos livros vendidos efetivamente.

• Estatísticas da venda dos livros em forma de gráfico.

• Livros vendidos e não vendidos.

• Livros de cada tipo.

Utilizando a função LOOKUP

Neste caso, podemos utilizar a função LOOKUP que faz uma pesquisa em uma tabela auxiliar. Neste caso, pesquisaremos nesta tabela auxiliar em qual intervalo o preço original se encontra para cada um dos livros da tabela. Para isso, temos que construir em nossa planilha esta tabela auxiliar e utilizar a função LOOKUP como mostrado abaixo:

Os parâmetros da fórmula LOOKUP podem ser descritos assim:

Contando células que possuem um certo valor (usando COUNTIF)

Suponha que você deseja saber qual o número de livros vendidos e não vendidos. Para que este cálculo seja possível, criamos (como você pode ver na planilha abaixo), um novo dado de entrada: status de venda, que deve conter o valor "vendido" se o livro já foi vendido e "não vendido", caso contrário.

Para contar o número de células com um determinado valor, utilizamos a fórmula COUNTIF(destacada na planilha abaixo). Esta fórmula conta o número de células cujo conteúdo é igual a um certo valor. No exemplo, contamos quantas células dentre as de endereço entre G4 e G11 possuem status de venda igual a "vendido" e "não vendido".

Os parâmetros desta fórmula podem ser assim descritos:

Para obter o número total de livros de cada tipo, podemos fazer um cálculo similar utilizando a função COUNTIF.

Construindo Gráficos

Finalmente, suponha que você deseje ver graficamente como se relacionam os livros vendidos e os não vendidos e quantos livros de cada tipo você possui. No primeiro caso, vamos construir um gráfico do tipo torta e, no segundo, um gráfico do tipo coluna (como mostramos abaixo).

Para construir gráficos como este você deve acionar o menu Insert - Chart e seguir os passos para construir um gráfico personalizado.

Disponivel em: http://terra.dcc.ufmg.br/material_referencia/mrg_Planilhas/mrg_Planilhas.html