Folha de cálculo - Manuais Informática - IES San Clemente.
1 Cálculo básico
1.2 Dados das células
1.3 Referencias ás células
1.4 Copiar arrastando
2 Estilos e formatação condicional
2.2 Formatação condicional
2.2.1 Em base ao valor da própria célula
2.2.2 Em base ao valor de outra célula
2.2.3 Ordenação de condições
3 Fórmulas complexas
4.2 COUNTIF
4.3 SUMIF
6.2 Criação de gráficos do tipo adequado, segundo os valores a apresentar
6.3 Personalização dos gráficos criados
6.4 Criação de gráficos utilizando tudo o aprendido até ao momento
7 Uso como base de dados
7.2 Validação de dados
7.3 Ordenação de dados
7.4 Filtros
7.4.1 Filtro automático
7.4.2 Filtro padrão
7.4.3 Filtro avançado
7.5 Tabela Dinâmica
8 Funções de texto
9 Google Sheets
Configurar interface em português.
Configurar fórmulas em inglês.
Configurar que os livros novos tenham apenas uma folha.
Dados das células
Constantes (texto, numéricas, formato moeda, formato data, etc).
Fórmulas utilizando os operadores + - / *
Funções (podem procurar-se no assistente):
Referencias ás células
Referencias relativas. Ao copiar (arrastando) uma fórmula, atualizar-se.
Referencias fixas. Pode-se evitar que se atualize usando $ antes da fila, a coluna ou ambas. Exemplo: $A$1
Intervalos de células, por exemplo: A1:A5
Copiar arrastando
Quando estamos a trabalhar numa tabela, no geral, vamos realizar o cálculo na primeira fila e depois vamos arrastar cara abaixo de maneira que a fórmula se atualize automaticamente em cada célula. Podemos copiar com esta técnica:
Números, texto, fórmulas.
Utilização e criação de listas personalizadas, por exemplo, os dias da semana noutra língua.
A formatação condicional permite mudar automaticamente o aspeto de uma célula em função de:
O valor da própria célula.
O valor de outra célula.
Por exemplo, é possível configurar uma tabela de temperaturas deste jeito:
Se o valor de uma célula for negativo, automaticamente será mostrado em vermelho,
se for zero, em cinzento,
e em qualquer outro caso, em azul.
O processo recomendado segue esta ordem:
Criação dos estilos que se vão utilizar.
Configurar a formatação condicional.
Numa zona qualquer da folha de cálculo, criamos amostras do aspeto de cada célula. No exemplo anterior, criamos uma célula com texto vermelho, outra com texto cinzento e outra com texto azul. Nesta última, também modificamos a formatação do número para que mostre o "+": menu contextual, Formatar células, Números, Código do formato, escrevemos "+0".
A seguir, abrimos a barra lateral de Estilos com F11 e criamos os três estilos, um a um, a partir da seleção. Por exemplo, colocamo-nos na célula com texto vermelho, clicamos no botão "Novo estilo a partir da seleção", nome do estilo: "TempNegativa".
Após termos criado os três estilos, podemos apagar as células de mostra.
Em base ao valor da própria célula
Há várias formas de configurá-lo. Recomenda-se definir a formatação na primeira célula e depois copiar o formato no resto com a trincha (botão "Clonar formatação").
Clicamos no menu Formatar, Condicional, Condição.
Escolhemos "O valor da célula é", "menor que" e escrevemos o valor "0". Aplicar estilo "TempNegativa". Na direita vemos uma amostra do estilo.
Clicamos em Adicionar e criamos a segunda condição: O valor da célula é igual a zero", aplicar o estilo correspondente.
Ainda faltaria criar uma condição para as temperaturas positivas, mas vamos imaginar que clicamos em Aceitar por erro. O primeiro que vamos fazer é verificar que o que foi feito funciona bem. Inserimos um valor negativo na célula e comprovamos se o texto se mostra em vermelho automaticamente. Depois verificamos se ao inserir um zero se mostra em cinzento. Com valores positivos, por enquanto, deixa o texto em preto.
Podemos adicionar ou corrigir uma condição clicando no menu contextual no botão "Formatação condicional". Mas se houver várias formatações no mesmo documento, recomenda-se aceder a cada uma delas no menu Formatar, Condicional, Gerir.
O último passo é copiar a formatação da primeira célula no resto com a trincha. É importante mudar os valores das células para verificar que os formatos se adaptam automaticamente aos conteúdos.
Em base ao valor de outra célula
Completamos o exemplo: destacamos com fundo amarelo os nomes dos municípios cuja temperatura seja superior à média.
Numa célula, calculamos a temperatura média com a função AVERAGE.
Começamos por criar o novo estilo a partir de uma célula de amostra, como foi explicado anteriormente.
Colocamo-nos na célula do primeiro município e abrimos o assistente de Formatação condicional.
A seguir, escolhemos "A fórmula é" e escrevemos uma desigualdade como esta: "G8 > $G$15" onde G8 seria a temperatura correspondente a esse município e G15 a temperatura média. Atenção! A referência da temperatura é relativa e a da média é fixa.
Escolhemos o estilo criado previamente e aceitamos.
Copiamos a formatação com a trincha.
Para finalizar, mudamos os valores das temperaturas para verificar se os formatos das células se adaptam aos novos valores.
Se for preciso que se cumpram várias condições para aplicarmos um estilo, devemos utilizar a função AND. No exemplo, se queremos ressaltar em amarelo as localidades com temperatura superior à media, sempre e quando sejam positivas, temos que usar esta fórmula:
AND(C8 > $C$15; C8 > 0)
Uma fonte de erros muito frequentes é a ordem das condições. A aplicação avalia cada condição uma a uma. Quando cumpre uma, aplica o estilo correspondente, e não continua avaliando o resto. É por isso que devemos ordená-las bem.
No nosso exemplo, se quisermos destacar com fundo azul claro as células com temperaturas maiores que 30 ºC, esta primeira solução não funciona, porque ao avaliar como certa a segunda condição, nunca vai chegar até à quarta.
Temos que "subir" a nova condição, assim:
É frequente realizar cálculos em células que dependem de outros cálculos realizados noutras células, em cadeia. Por vezes, os resultados intermédios não interessam, só os finais, isso quer dizer que temos que construir fórmulas complexas numa soa célula, mas é difícil criá-la de zero. O truque é saber criar fórmulas complexas a partir de cálculos separados.
Neste exemplo, o cálculo do total em F3 faz-se a partir doutro cálculo feito na célula D3. Imaginemos que não nos interessa mostrar o dado do "Preço sem IVA". Tal e qual está feito, não poderíamos eliminar essa coluna. Primeiro devemos copiar o conteúdo da célula D3 e colá-lo na célula F3, substituindo a sua referência:
Feito isto, já podemos apagar a coluna D.
Em conclusão, devemos substituir as referências às células pelo conteúdo dessas células.
Existem inúmeras funções acessíveis no assistente, classificadas por categorias.
Vamos aprender a trabalhar com IF e as suas variantes.
A função tem três argumentos: IF(valorTF; resultadoT; resultadoF)
valorTF: é um valor lógico TRUE ou FALSE. Também pode ser uma condição que depois de ser avaliada devolva um desses dois valores, por exemplo, F5>0.
resultadoT: constante (de qualquer tipo), referência a outra célula, outra função, etc. que vai devolver o IF se no primeiro argumento há um TRUE. É obrigatório.
resultadoF: constante (de qualquer tipo), referência a outra célula, outra função, etc. que vai devolver o IF se no primeiro argumento há um FALSE. É optativo. É frequente encontrar um IF dentro de outro IF. Nesse caso, há que ter muito cuidado com a ordem das condições (ver exemplo).
=IF(A2="vendido"; B2)
=IF(E2>0; E20+100; E20-100)
=IF(D10>100; "gasoso"; IF(D10>0; "líquido"; "sólido"))
A função tem dois argumentos: COUNTIF(intervalo; valor)
intervalo: células que vão ser contadas sempre que o conteúdo seja igual ao valor do segundo argumento.
valor: dado que vai ser procurado no intervalo anterior.
=COUNTIF(D5:D20; "ok")
=COUNTIF($D$5:$D$20; A2)
Muito parecida a COUNTIF mas têm um terceiro argumento que é optativo, mas muito frequente: COUNTIF(intervalo; valor; intervaloSoma)
intervaloSoma: dados que se vão somar quando se encontre o valor no intervalo.
=SUMIF($D$5:$D$20; A2; $E$5:$E$20)
Em Calc podem-se indicar os valores permitidos numa célula ou intervalo de células. Por exemplo: valores inteiros positivos, valores reais entre -5 e 5, cadeias de texto de 9 caracteres de máximo, etc.
Isto é útil para preparar uma folha que vão utilizar outras pessoas para inserir dados.
No menu Dados, Validação:
O separador "Critérios" serve para indicar o tipo de dado (e limites) permitido na célula.
O separador "Ajuda de dados" serve para mostrar uma mensagem num retângulo amarelo, ao clicar na célula.
O separador "Aviso de erro" serve para indicar o tipo de erro e o texto que se mostrará ao usuário quando insira um valor que não calhe com os critérios assinalados. A única "Ação" que garante que não se vai inserir um valor incorreto é "Parar". Os outros dois tipos de mensagem só servem para avisar, e não forçam a que o utilizador corrija.
É importante entender que não se trata de que nós insiramos dados na folha, senão de preparar uma folha para que outras pessoas possam inseri-los sem erros.
Para isso é necessário identificar visualmente a zona para que os utilizadores saibam exatamente onde devem introduzir os dados.
Qualquer relatório ou estudo que se realize no contexto de uma empresa precisa de estar acompanhado de dados. Para facilitar a interpretação desses dados, são imprescindíveis os gráficos que constituem a expressão visual das informações existentes nas tabelas de Calc.
Os gráficos podem realizar-se de muitas formas diferentes segundo o tipo de dados que se apresentem, mas também segundo o que se queira destacar.
Vamos aprender a criar e personalizar diferentes tipos de gráficos segundo as informações que queiramos apresentar.
Para além disso, como técnicos informáticos, temos de saber ensinar aos utilizadores finais a criar, a adaptar e a interpretar gráficos.
Criação de gráficos do tipo adequado, segundo os valores a apresentar
Existe um grande leque de tipos de gráficos conforme as nossas necessidades, mas também segundo o tipo de dados que representam as informações. De facto, não podemos usar qualquer gráfico para apresentar qualquer dado. Assim, temos os seguintes gráficos:
barras, representam a relação entre séries de dados que evoluem no tempo. Podem-se representar em forma de colunas, pirâmides ou cones. Podem ser:
normais, comparam valores de diferentes categorias.
empilhados, comparam o valor mantendo a proporção com o resto do conjunto.
percentagem empilhada, mostram a influência sobre o total.
circular, representam a proporção de cada valor relativamente a um total. Divide-se o círculo em partes que representam cada um dos conceitos da tabela e podem ser:
circular dividido, destacam os valores individuais.
anel, extrai-se um valor do gráfico e gera-se um novo gráfico.
linhas, representam a tendência de várias séries de dados em intervalos regulares. Classificam-se em:
normais, evolução dos valores.
série empilhada em cima, contributo de cada valor no tempo.
série empilhada percentagem, tendência da percentagem com o contributo de cada valor.
Outros tipos de gráficos mais específicos são os de área, dispersão, de cotação, de bolha ou rede.
Os passos para a criação de um gráfico simples são os seguintes:
selecionamos as células que contêm a série de nomes e dados que formarão o gráfico.
vamos à barra superior e clicamos em Inserir → Gráfico.
escolhemos o tipo de gráfico no assistente de gráficos e à direita selecionamos o subtipo mais adequado.
na folha aparece uma pré-visualização que ajuda a defini-lo.
Clicamos em fim para concluir a criação do gráfico.
Colocamos o gráfico onde fique melhor para efeitos de apresentação. Deste modo, dirigimos o cursor até à borda do gráfico (ponteiro de quatro setas) e arrastamo-lo.
É possível também mudar o tamanho do gráfico arrastando as setas que aparecem quando passamos o cursor sobre os quadrados que há na borda do gráfico.
► Tarefa 1. Criação de gráficos do tipo adequado, segundo os valores a apresentar.
Personalização dos gráficos criados
Depois de criados os gráficos, a melhor forma de apresentá-los de um jeito atrativo, é personalizando-os. Para isso, selecionamos as colunas que têm o texto que queremos apresentar como rótulos e também os dados, escolhemos o tipo de gráfico, clicamos em seguinte e na caixa de verificação em primeira linha como etiqueta e primeira coluna como etiqueta. Avançamos para elementos do gráfico e escrevemos o título a mão. Também escrevemos os títulos dos eixos horizontal e vertical. Finalizamos com a inserção do gráfico.
Com o fim de lhe dar um toque mais profissional, há uma série de ferramentas que vamos ver de seguida:
Para entrar no modo de edição do gráfico, fazemos dois cliques sobre ele. A barra de ferramentas muda e permite inserir e alterar títulos, legendas, grelhas ou eixos. Cada vez que ativamos um elemento do gráfico, a barra permite mudá-lo com os ícones correspondentes.
No gráfico, clica sobre o título, e no menu contextual seleciona formatar título e modifica a cor, tamanho e sublinhado da letra. Com dois cliques sobre o texto ou F2 podemos mudar o conteúdo.
Mudamos a posição e tamanho dos rótulos de eixo vertical e o eixo horizontal, fazendo dois cliques sobre ele, ou então selecionando-o e premindo F4.
De modo a variar o ângulo dos rótulos do eixo horizontal, premimos o botão direito sobre ele e selecionamos Formatar eixo. No separador Etiqueta, mudamos o ângulo para 90º.
Eliminamos as grelhas horizontais e verticais na barra de ferramentas.
Quando o gráfico apenas apresenta dados de uma variável, não é necessário mostrar a legenda. Selecionamos a legenda e na barra de ferramentas premimos sobre ativar/desativar legenda.
De modo a agregar uma cor de fundo do gráfico, premimos o botão direito sobre Formatar parede e/ou Formatar área do gráfico. No separador Área podemos selecionar Gradiente de tipo Radial, por exemplo, com duas cores diferentes. Para que esse gradiente se aplique, devemos clicar em Adicionar.
Mudamos o Eixo Y para que tenha uma escala diferente. Premimos sobre ele com o botão direito e selecionamos Formatar eixo e mudamos o Intervalo principal para 10000.
Como resumo do anterior devemos ter em conta que, se a entidade estiver marcada, a barra superior de ferramentas muda de modo a adaptar-se. Também clicando no botão direito, permite aceder às opções de modificação do gráfico.
► Tarefa 2. Personalização dos gráficos criados.
Criação de gráficos utilizando tudo o aprendido até ao momento
No seguinte passo, devemos utilizar as ferramentas de modificação aprendidas para criar um gráfico o mais atrativo possível para inserir no relatório correspondente.
Podemos investigar mais sobre as possibilidades que oferecem os gráficos de modo a atingir uma maior qualidade, bem como facilidade de interpretação para o utilizador.
Devemos prestar atenção à correção dos seguintes aspetos para evitar gráficos erróneos ou incompletos:
As etiquetas têm que mostrar valores (gráfico de barras) ou percentagens (gráfico circular).
As legendas devem mostrar informação relevante.
Normalmente não se mostra o total como série (numa coluna, por exemplo).
► Tarefa 3. Desempenho dos empregados e vencimentos recebidos.
Uso como base de dados
Embora o LibreOffice incorpore uma ferramenta de gestão de banco de dados chamada Base, é possível usar o Calc para gerir registos numa única tabela e operar com informações de maneira semelhante.
Vamos aprender a utilizar uma folha de cálculo como base de dados simples e a executar ações de validação, ordenação e filtragem. Também vamos aprender a utilizar tabelas dinâmicas como alternativa ao cálculo através de funções complexas.
Como técnicos informáticos, temos que saber utilizar estas ferramentas e ensinar os utilizadores finais a usá-las.
A validação de dados permite garantir que as informações armazenadas nas células da nossa folha atendem aos critérios exigidos. Quando trabalhamos com folhas com muita informação, é possível que isso não seja uniforme e que alguns dados sejam de um tipo diferente (letras e números, por exemplo). Também pode ser o caso de várias pessoas trabalharem com o mesmo documento e nem todos conhecerem a estrutura dele ou que haja erros na introdução dos dados.
Por esse motivo, é possível estabelecer certos critérios para garantir que as células contenham o tipo de informação que desejamos. No menu Dados → Validação podemos indicar a origem do mesmo entre números inteiros, decimal, data, hora ou comprimento do texto ao qual podemos aplicar uma limitação por valor (maior, menor, igual, etc.) e também podemos escolher o intervalo de células, predefinindo qual será o valor que podemos escrever numa célula no futuro.
► Tarefa 1. Validação de dados.
Quando temos uma tabela com várias informações, às vezes é importante ordená-la seguindo alguns critérios para analisar melhor os dados.
Por exemplo, se estivermos incorporando uma lista de vendas e compras feitas, poderemos estruturar a folha ordenando alguns dos campos, como pode ser o valor da venda ou da data.
Para ordenar por uma coluna, apenas temos que clicar numa das células da coluna pela qual queremos classificar e clicar no botão de ordem ascendente ou descendente de acordo com as nossas preferências. No caso do texto, o pedido será em ordem alfabética. Se queremos estabelecer um maior controle sobre a ordem, então devemos selecionar a opção no menu Dados → Ordenar...
Na caixa de diálogo que nos abre a seguir, podemos escolher os critérios de classificação e estabelecer uma ordem por vários campos juntos. Com esta opção podemos combinar critérios com diferente prioridade, por exemplo: ordena as vendas por cliente, data e valor; o que fará com que primeiro tenhamos uma ordem por cliente; e para o mesmo cliente ordenará por data; e para o mesmo cliente e data ordenará por valor.
Além das opções de classificação, podemos apontar critérios adicionais que nos permitem diferenciar entre letras maiúsculas e minúsculas ou definir uma ordem específica, como os dias da semana, entre outros.
► Tarefa 2. Ordenação de dados.
Outra maneira de organizar as nossas informações é através do uso de filtros. Os filtros permitem separar as informações para que possamos ver apenas os dados que nos interessam. Isso não significa que os dados desapareçam, mas simplesmente não são exibidos. Para voltar a vê-los, basta desativar o filtro.
O filtro automático simplesmente cria uma caixa de combinação em cada cabeçalho de cada campo (coluna) para indicar quais os dados que queremos mostrar ou não. É uma ferramenta muito eficaz para criar critérios de forma simples que filtrem os dados para mostrar apenas os que cumprem os critérios.
O filtro padrão estabelece critérios mais avançados sobre os valores dos registos de cada campo e pode indicar condições específicas que devem obedecer aos dados como valores iguais, maiores que, etc. Uma das principais vantagens é o acúmulo de filtros para que possamos estabelecer vários critérios de simplificação para mostrar os dados que nos interessam.
Finalmente, o filtro avançado permite coletar informações da tabela noutro espaço do mesmo. Assim, podemos estabelecer novos registos que compilam uma subcoleção de dados da base geral.
► Tarefa 3. Filtragem de dados.
A tabela dinâmica permite combinar, comparar e analisar grandes volumes de dados, obtendo resumos. Uma tabela criada como tabela dinâmica é interativa. Os dados podem ser reorganizados ou resumidos de acordo com vários pontos de vista. Siga estes passos para criar uma tabela dinâmica.
Posicione o cursor num intervalo de células que inclua valores, títulos de linha e títulos de coluna.
Clique em Inserir → Tabela dinâmica. Na caixa de diálogo Selecionar origem, escolha Seleção atual e aceite. Os títulos da tabela são mostrados como botões na caixa de diálogo Tabela dinâmica. Arraste estes botões conforme necessário e largue-os nas áreas Campos de página, Campos de coluna, Campos de linha e Campos de dados.
Arraste os botões pretendidos para uma das quatro áreas.
Arraste um botão para a área Campos de página para criar um botão e uma caixa de lista por cima da tabela dinâmica gerada. A caixa de lista pode ser usada para filtrar a tabela dinâmica através do conteúdo do item selecionado. Pode usar a função arrastar e largar na tabela dinâmica gerada para utilizar outro campo de página como filtro.
Se o botão for largado na área Campos de dados, será atribuída uma legenda que mostra a fórmula utilizada para calcular os dados.
Ao clicar duas vezes num dos campos da área Campos de dados, pode invocar a caixa de diálogo onde pode selecionar os cálculos a realizar. Para uma seleção múltipla, prima a tecla Ctrl ao clicar no cálculo pretendido.
A ordem dos botões pode ser alterada a qualquer altura movendo-os com o rato para uma posição diferente na área.
Remova um botão arrastando-o novamente para a área dos restantes botões à direita da caixa de diálogo.
Para abrir a janela de Campo de dados, clique duas vezes num dos botões na área Campos de linha ou Campos de coluna. Utilize a caixa de diálogo para selecionar as condições e o alcance com que o LibreOffice calculará o subtotal exibido.
Feche a janela Tabela dinâmica, premindo Aceitar. Será inserido um botão Filtro ou um botão de página para cada campo de dados largado na área Campos de página. A tabela dinâmica é inserida mais abaixo.