Folha de Cálculo Microsoft Excel - Instituto Superior de Agronomia

Metodologias de apoio à decisão em
Sumário
Ci ências Agrárias
Folha de cálculo
a. Armazenamento
d. Fórmulas
- ficheiro
Folha de Cálculo
- referências relativas a células
- folha de cálculo
- ref. absolutas a células
- ref. mistas a células
e. Copy e Paste de células/ranges
b. Conceitos
Mi cros oft Excel
- referência a célula
- conteúdo da célula: valores e fórmulas
- com valores
c. Operacionalidade básica
- com fórmulas
- activar uma célula
Ferna nda Valente
f. Funções
- registar dados
- argumentos
- seleccionar células
- inserir/remover linhas/colunas
- resultado
- funções imbricadas
Junho/Julho 2014
Instituto Superior de Agronomia
Universidade de Lisboa
Folha de cálculo
2
• Ficheiro e folha de cálculo
– No Excel, ao ficheiro em que se trabalha e onde são
guardados os dados também é dado o nome de
workbook (ou livro). Cada workbook pode conter
várias folhas de cálculo.
– A folha de cálculo, também designada worksheet ou
quadrícula, é o documento primário utilizado para
guardar e manipular dados. Uma folha de cálculo é
composta por células organizadas em linhas (mais de
1 milhão) e colunas (mais de 16 mil) e faz sempre
parte de um workbook.
– armazenar constantes
– utilizar fórmulas
– utilizar funções estatísticas, matemáticas, ...
– criar gráficos
– imprimir dados
–…
• O Microsoft Excel 2010 é um exemplo de
aplicação do tipo folha de cálculo
Junho/Julho 2014
Junho/Julho 2014
Conceitos básicos
• É uma aplicação que permite :
Metodologias de apoio à decisão em Ciências Agrárias
Metodologias de apoio à decisão em Ciências Agrárias
3
Conceitos básicos
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
4
Conceitos básicos
• Endereço e conteúdo de células
• Principal vantagem das folhas de cálculo:
– O endereço de uma célula permite a sua
identificação e é constituído pela letra da coluna e
pelo número da linha em que a célula se
encontra.
• Exemplos: A4, B5, C50
– utilização de fórmulas que são automaticamente recalculadas e o seu resultado é
actualizado, sempre que se altera os valores
das quais dependem.
– Os dados são inseridos nas células e podem ser de
vários tipos: valores numéricos, alfanuméricos ou
datas e fórmulas (com ou sem a utilização de
funções).
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
5
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
6
1
Operacionalidade básica
Operacionalidade básica
• Nomear e salvar o ficheiro (workbook)
• Movi mentos dentro de uma folha de cá lculo
• Selecção de linhas e colunas
– Teclas de setas, caixa de deslocamento na barra de deslocamento
vertical/horizontal, ...
– Posicionar numa célula de uma folha de cálculo, tornando a célula
activa: clicar sobre essa célula ou usar as teclas de setas.
• Registar dados
• Selecção de células
– Uma célula: activar a célula.
– Uma range de células: clicar sobre a primeira célula do intervalo e
arrastar até à última (ou clicar sobre a primeira célula do intervalo
e depois premir SHIFT e clicar sobre a última célula do intervalo).
– Células não adjacentes: seleccionar a primeira célula ou intervalo
de células e depois premir CTRL e seleccionar as outras células.
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
7
– Uma l inha inteira: clicar sobre o número de linha.
– Uma col una i nteira: clicar s obre a letra de coluna.
– Li nhas ou colunas adjacentes: a rrastar pelas i dentificações
de l i nha ou coluna (ou ... premir SHIFT e ...).
– Li nhas ou colunas não adjacentes: s eleccionar a primeira
l i nha ou coluna e a s eguir premir CTRL e s eleccionar as
outra s linhas ou colunas.
– Toda s as células de uma folha de cálculo: cl icar s obre o
botã o do canto superior esquerdo da folha de cálculo onde
a s i dentificações de linha e coluna se intersectam.
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
8
Operacionalidade básica
Fórmulas
• Modos de Operação: o modo de operação corrente encontras e i ndicado na Barra de Es tados
– Ready i ndi ca que está pronto a executar qualquer tarefa:
i ns erção de dados, selecção de células,....
– Enter é a ctiva do quando s e inicia a introdução de dados
numa célula. Pa ra sair deste modo premir a tecla ENTER (ou
ESC).
– Edit é a cti vado quando se i nicia a alteração do conteúdo de
uma célula (para isso, duplo clique na célula). Pa ra sair
des te modo premir a tecla ENTER (ou ESC).
– Point é a cti vado quando é cri ada uma fórmula utilizando
referências a células. Pa ra s air deste modo premir a tecla
ENTER (ou ESC).
• Uma fórmula começa sempre com o sinal =.
• Uma fórmula pode ser composta por valores,
referências a células, funções ou operadores
e produz um novo valor.
• Uma célula pode guardar uma fórmula
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
9
– Exemplo em B2 guardar a fórmula: = A2+2
• Operadores
– aritméticos: +, -, /, *, ^
– de comparação: =, >, <, >=, <=, <>
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
10
Operacionalidade básica
Referências relativas vs. absolutas
• Copy / Paste (de fórmulas)
• Cut / Paste (de fórmulas)
• A distinção entre referência relativa e
referência absoluta a células é importante
quando uma fórmula é copiada de uma célula
para outra(s).
• Quando é copiada uma fórmula, as referências
relativas são ajustadas automaticamente para a
nova localização.
Metodologias de apoio à decisão em Ciências Agrárias
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
11
– a referência relativa numa fórmula copiada refere-se a
células diferentes das referidas na fórmula original
– a relação entre as células referidas na fórmula copiada
e as células referidas na fórmula original é a mesma
que existe entre a célula para onde foi copiada a
fórmula e a célula que contém a fórmula original
Junho/Julho 2014
12
2
Referências relativas vs. absolutas
• Quando se copia uma fórmula, as referências
absolutas são copiadas exactamente como
aparece na célula origem.
• Inserção de Linha(s)/Coluna(s)
• Remoção de Linhas/Colunas
– Nas fórmulas, as referências a células “ajustamse” automaticamente quando se
inserem/removem linhas/colunas
– Quando é removida uma linha/coluna com células
que são referidas numa fórmula, esta fica inválida
(erro de referência - #REF!)
– A existência de um sinal $ antes da identificação
de linha e de coluna indica uma referência
absoluta.
• exemplo: $A$2
• Quando apenas a linha (ou coluna) de uma
referência é absoluta a referência é mista
• exemplos: $A2
Operacionalidade básica
A$2
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
13
Operacionalidade básica
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
14
Funções
• Mudar a folha visível
• Referência a célula(s) de outra Folha de Cálculo
• Uma função é sempre utilizada numa fórmula.
– Os va l ores sobre os quais uma função efectua operações são
cha mados argumentos.
– O va l or que uma função origina é chamado resultado.
– Si ntaxe: nomeDaFunção (argumento(s))
– exemplo: Sheet1!A1:B2
• Referência a célula(s) de outra Folha de Cálculo
de outro Workbook
• Cada função encontra-se definida com um determinado
número de argumentos
– exemplo: Outro.xlsSheet1!A1:B2
Nota: Se o nome da outra folha de cálculo ou
workbook contiver caracteres não-alfabéticos, tem
de incluir o nome (ou o caminho) entre plicas (' ')
– Há funções que não têm qualquer a rgumento, por ex. pi()
• Os valores dos argumentos podem ser constantes,
referências a células, ranges, funções ou expressões.
̶ exemplo: 'Ainda Outro.xlsSheet 1'!A1:B2
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
15
Funções
– quando os argumentos têm significados idênticos, a
ordem por que são indicados os valores dos
argumentos não é significativa;
– quando cada argumento possui um significado
próprio, a ordem por que são indicados os valores dos
argumentos é fundamental.
Junho/Julho 2014
Junho/Julho 2014
16
Funções
• Quando uma função requer mais do que um
argumento, os argumentos são separados por ;
ou , (dependendo do separador de listas que foi
adoptado na configuração do sistema operativo)
Metodologias de apoio à decisão em Ciências Agrárias
Metodologias de apoio à decisão em Ciências Agrárias
17
• Algumas funções possuem argumentos para
os quais se encontram pré-definidos valores
de omissão, isto é, valores que são utilizados
no caso do utilizador não fornecer valores.
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
18
3
Exemplos de Funções
•
•
•
•
•
•
•
•
•
•
Função IF
SUM – devolve a soma de todos os argumentos
SUMPRODUCT – devolve a soma do produto dos valores correspondentes em
conjuntos de células (de igual dimensão)
COUNT – devolve o número de células do(s) argumento(s) que têm valores numéricos
IF – devolve um valor se a condição especificada devolver o valor TRUE e outro valor
se essa condição devolver o valor FALSE
OR – devolve a disjunção lógica dos argumentos
AND – devolve a conjunção lógica dos argumentos
SUMIF – devolve a soma dos valores de um conjunto de células que verificam a
condição especificada
COUNTIF – devolve o número de células num conjunto que verificam a condição
especificada
MMULT – devolve o produto matricial de dois conjuntos de células (encadeados) (a
utilizar numa fórmula de matriz)
FREQUENCY – devolve a frequência com que os valores de um conjunto de células
ocorrem num conjunto de classes (a utilizar numa fórmula de matriz)
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
19
Composição de funções (f. imbricadas)
• Quando um ou mais argumentos de uma
função é uma função
• Sintaxe
IF(teste_lógico; [valor_se_verdadeiro]; [valor_se_falso])
• Exemplo
C31 := IF(C19>0;”aumentou”;”diminuiu”)
falso
C19 >0
C31  “di minuiu”
verdade
C31  “di minuiu”
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
20
Duas funções IF imbricadas
• Exemplo
C33: =IF(C32>10;"ma is de 10%";IF(C32>0;"a té 10%";"diminuiu"))
– exemplos:
•
•
•
•
= i f (s um (b2 : g2) > = 9.5 , “Aprova do”, “Reprovado”)
= i f (count (a1:a10) >=5 , ma x (a 1:a10) , mi n (a1:10))
= i f ( i s error (a4) , “----” ; a 4)
= i f (g5 >= 16 , “Ora l ” , i f (g5 >= 10 , “Aprova do” , “Rep.”) )
C32 >10
verdade
C33  “mais de 10%”
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
21
falso
C32 >0
falso C33  “diminuiu”
verdade
C33  “até 10%”
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
22
Microsoft Excel avançado
• Para maximizar as potencialidades oferecidas é
necessário o uso de ferramentas de
programação.
– O Visual Basic for Applications (VBA) é uma
linguagem de programação que permite um total
controlo da folha de cálculo.
– As macros são pequenos programas, escritos em
VBA, que podem ser criadas de forma automática e
permitem a execução de tarefas específicas.
Metodologias de apoio à decisão em Ciências Agrárias
Junho/Julho 2014
23
4