24 Novembro 2006Folha de Cálculo 11 Jorge Cruz DI/FCT/UNL Introdução aos Computadores e à Programação 1º Semestre 2006/2007

24 Novembro 2006 Folha de Cálculo 1 1

Folha de Cálculo 1

Jorge CruzDI/FCT/UNL

Introdução aos Computadores e à Programação1º Semestre 2006/2007


Folha de Cálculo• A informação numa folha de cálculo está estruturada com

base em matrizes, ou tabelas.

• Numa folha simples, existe uma única matriz, cuja dimensão é arbitrária. Nessa matriz podem ser definidas submatrizes, ou subvectores.

• Qualquer posição na matriz é uma variável, identificada pela sua coluna (A, B, Z, AA, ... – uma ou mais letras) e sua linha (1, 2, 3, ... - um inteiro). Por exemplo B2 = “Pedro Vieira”

A B C D1 610 Paul o Fer nandes Lopes 2341. 36 15/ 04/ 19962 825 Pedr o Vi ei r a 989. 24 25/ 06/ 19993 316 Mar t a Cost a Mar t i ns 1389. 17 05/ 01/ 19924 34 Rui Vasco Per ei r a 5310. 32 15/ 04/ 19965 723 J or ge Bar at a 767. 26 03/ 09/ 2002


Folha de Cálculo• Tal como numa linguagem de programação, a uma variável

podem ser atribuidos valores, constantes ou dependentes de outras variáveis através de fórmulas.

A B1 1 32 2 63 3 94 4 125 5 15

B1 = 3 * A1B2 = 3 * A2....B5 = 3 * A5

• Uma folha de cálculo tem uma natureza reactiva. Sempre que uma variável muda de valor, as que são dependentes mudam igualmente de valor.

• Por exemplo, se A1 passar para 2, B2 passará automáticamente para B1 = 2* A1 = 2* 2 = 4.

A1 = 1A2 = 2....A5 = 5


Folha de Cálculo• Por esta razão, não são permitidas fórmulas que introduzam

dependências circulares

– directas ( A1 = 2 * A1);

– ou indirectas (A1 = 3 * B1 e B1 = 4 * A1).

• Qualquer destas fórmulas levaria a computações eventualmente infinitas.

A1 = 2 * 2 * 2 * 2 * 2 * ...

A1 = 3 * 4 * 3 * 4 * 3 * ...

• Nota: As folhas de cálculo permitem controlar o número de computações mas, em geral, as referências circulares devem ser evitadas.


Condicionais em Folhas de Cálculo• Em folhas de cálculo há instruções condicionais de atribuição

de valores (não condicionais de controle de execução)

• A sua sintaxe (em EXCEL) é

if(condition, then_value, else_value)A B

1 1 32 2 63 3 94 4 05 5 0

B1 = if (A1 <=3, 3* A1,0)B2 = if (A2 <=3, 3* A2,0)....B5 = if (A5 <=3, 3* A5,0)

• Estas instruções podem encadear-se. Por exemploif(condition1, then_value1,

if(condition2, then_value2, if(condition3, then_value3,value_4)))

A1 = 1A2 = 2....A5 = 5


Iterações em Folhas de Cálculo• O conceito de iteração numa linguagem imperativa implica a

repetição temporal de instruções. Por exemplo, com a instrução

pretende-se fazer executar as <instruções> (de atribuição de valores a variáveis) n vezes, em sequência.

• Numa folha de cálculo, essa iterações podem ser obtidas através de uma repetição espacial das instruções.

• A título de exemplo, vamos calcular xn.

para i de 1 até n fazer

<instruções>

fimfazer;


Iterações em Folhas de Cálculo• Numa linguagem imperativa, por exemplo em Octave, o

cálculo pode ser feito através do programa

• Neste programa, a variável y vai tomando os diversos valores (1, x, x2, x3, ..., xn), ao longo do tempo, começando com o valor 1, e terminando no valor xn.

• Numa folha de cálculo, os diferentes valores que y toma, podem ser arrumados, ao longo do espaço, por exemplo num conjunto de células contíguas (por exemplo, uma coluna).

y = 1 ; x = <valor>for i = 1:n y = y * x fimfazer;


Iterações em Folhas de Cálculo• Numa linguagem imperativa, por exemplo em Octave, o

cálculo pode ser feito através do programa

• De notar que, na realidade, o mesmo programa tem de ser “escrito” n vezes, uma vez em cada célula. Para evitar este inconveniente, a interface das folhas de cálculo permite “copiar” o conteúdo de uma célula para outras, fazendo automáticamante o ajuste das referências.

y = 1 ; x = <...>for i = 1:n y = y * x fimfazer;

A1 22 43 84 165 32

A1 = <...>A2 = A1 * A1;A3 = A2 * A1;…A5 = A4 * A1;

X = <...>

Y1 = 1 * X

Y2 = Y1 * X;Y3 = Y2 * X;…Y5 = Y4 * X;


Iterações em Folhas de Cálculo• Por exemplo, se tivermos a célula B1 definida como

B1 = 2* A1

e a copiarmos “para baixo”, i.e. para B2, como a cópia é feita para uma célula com nº de linha superior em 1, (B1 para B2), o valor 1 é acrescentado a todas as linhas na fórmula, obtendo-se

B2 = 2*A2

• Igualmente ao copiar “para o lado” uma célula, a diferença de colunas entre a célula origem e a célula destino é acrescentada às referências a colunas. Se copiar a célula B1 para a posição D1 (2 colunas de diferença) obtem-se

D1 = 2*C1– Nota: A cópia pode ser feita com os habituais comandos <ctrl-c> e

<ctrl-v>, ou por comandos de “arrastamento”.


Referências em Folhas de Cálculo

• As referências a linhas e colunas que são ajustadas nas cópias de células são chamadas referências relativas (à célula de onde são copiadas – a célula varia n colunas/linhas em relação às célula “de cima” ou “do lado”).

• No entanto, há situações em que não queremos que estas referências a linhas e colunas sejam relativas mas sim absolutas, e que não sejam alteradas na cópia.

Por exemplo, ao copiar o conteúdo da célula A2 para A3, queremos alterar a 1ª referência a A1 para A2 (referência relativa), mas não a 2ª referência (referência absoluta).

A1 = <...>

A2 = A1 * A1;A3 = A2 * A1;…A5 = A4 * A1;


Referências em Folhas de Cálculo

• Para explicitarmos que uma referência é absoluta, e não se altera quando copiada para uma linha/coluna diferentes, antecedemos a linha/coluna pelo símbolo ‘$’.

• As referências podem ser relativas a uma dimensão e absolutas na outra dimensão, como no exemplo presente (a linha é absoluta, mas a coluna não é).

• Assim, se copiarmos as células A2 a A5 para as células B2 a B5, iremos calcular B1^5.

A cópia da célula A2 para as células abaixo altera a referência à linha relativa (1 acima), mas não à referência absoluta (a célula A1).

A1 = <...>

A2 = A1 * A$1;A3 = A2 * A$1;…A5 = A4 * A$1;


Referências em Folhas de Cálculo• As referências relativas/absolutas podem ser igualmente

utilizadas em matrizes.A B

1 2 32 4 93 8 274 16 815 32 243

A1 = 2

A2 = A1 * A$1;A3 = A2 * A$1;…A5 = A4 * A$1;

B1 = 3

B2 = B1 * B$1;B3 = B2 * B$1;…B5 = B4 * B$1;

• A iteração (temporal) pode pois ser substituída pela iteração (espacial) mas depende do utilizador o número de células que copia, isto é, o número de iterações a efectuar.

• Os ciclos enquanto não são assim directamente representáveis numa folha de cálculo, se o utilizador não souber à partida o número de iterações que devem ser efectuadas.


Referências em Folhas de Cálculo• Podem ainda ser utilizadas

referências “mistas” , que são particularmente úteis no caso de se pretenderem preencher tabelas relativas a uma linha e uma coluna.

A B C D E1 * 1 2 3 42 1 1 2 3 43 2 2 4 6 84 3 3 6 9 125 4 4 8 12 16

• Por exemplo, a tabela de multiplicação acima pode ser obtida por

– Obter a célula B2 através da multiplicação A2 * B1

– Tornar absolutas as referências à linha 1 em B1 e à coluna A, em A2, ficando $A2 * B$1

– Copiar esta célula para todas as células na matriz B2:E5.


Soma de um Vector• Os valores de um vector ou tabela numa folha de cálculo

podem ser agregados (somas, médias, etc..) de uma forma semelhante ao que se faz numa linguagem de programação como o Octave, com a implementação de iteração.

• Consideremos a soma de o vector nas posições A1 a E1. Em Octave poderíamos escrever (para um vector de 5 posições).

s = 0; for i = 1:5 s = s+a(i); endfor.

• A variável s cujo valor vai sendo iterado, pode ser substituída pelo vector B1 a B5 que vai tomando os valores de s nas diferentes iterações A B C D E

1 3 5 4 2 62 3 8 12 14 20

A2 = A1 + 0;B2 = B1 + A2;C2 = C1 + B2;D2 = D1 + C2;E2 = E1 + D2;


Soma Condicional de um Vector• Em Octave podemos somar valores de um vector que

satisfaçam ums condição através de uma instrução condicional dentro do ciclo.

• Consideremos a soma apenas dos valores positivos de um vector nas posições A1 a E1. Em Octave poderíamos escrever (para um vector de 5 posições).

s = 0; for i = 1:5 if a(i) > 0 then d = a(i) else d = 0; s = s+d; endfor.

• Este estilo de programação pode ser adaptado a um folha de cálculo, utilizando-se uma linha adicional, onde se colocam ou os valores do vector original ou o valor 0.


Soma Condicional de um Vectors = 0; for i = 1:5 if a(i) > 0 then d = a(i) else d = 0; s = s+d; endfor.

A B C D E1 3 -5 4 0 62 3 0 4 0 63 3 3 7 7 13

A3 = A2 + 0;B3 = B2 + A3;C3 = C2 + B3;D3 = D2 + C3;E3 = E2 + D3;

A2 = if(A1 > 0, A1, 0);B2 = if(B1 > 0, B1, 0);C2 = if(C1 > 0, C1, 0);D2 = if(E1 > 0, D1, 0);E2 = if(E1 > 0, E1, 0);


Funções para Somas de Vectores• Tal como em Octave, também as folhas de cálculo permitem

a utilização de funções. O paralelo é ainda maior do que noutras linguagens de programação, porque em Octave vectores e matrizes são tipos básicos, permitindo referências a subvectores e submatrizes.

• Assim em Octave, a soma dos elementos de um vector V com n elementos é obtida através da função

s = sum(V)• Ainda em Octave, se se pretender somar apenas os valores

do subvector constituído pelos elementos 3 a 7, podemos usar a referência a esse subvector na função através de

s = sum(V(3:7))


Funções para Somas de Vectores• Numa folha de cálculo, podemos usar igualmente a função

sum, sendo o vector delimitado entre a sua célula inicial e final.

– Quer no caso de um vector linha F1 = SUM(A1:E1)

– Quer no caso de um vector coluna A6 = SUM(A1:A5)

A B C D E F G1 3 -5 4 0 6 8 13

A1 32 -53 44 05 66 86 13

• Estas funções podem ainda utilizar-se para somas condicionais:

G1 = SUMIF(A1:E1,">0")

A7 = SUMIF(A1:A5,">0")


Operações de Vectores “Ponto a Ponto”• Tal como em Octave, operações sobre elementos de um

vector/matriz, podem ser condicionadas através da utilização de vectores/matrizes booleanas, que filtram os elementos a utilizar.

• Por exemplo,em Octave, a soma de todos os elementos positivos de um vector A pode ser efectuada através da instrução

B = sum(A.*(A>0))

que pode ser decompostas nas seguintes operações:– Criação de um vector booleano, A > 0, com 1s nas posições correspondentes

a elementos positivos do vector A

– Obtenção do vector A.*(A>0), igual a A nas posições em que A é positivo e com 0s nas outras posições.

– Soma dos elementos deste vector, que corresponde à soma dos elementos positivos do vector A (os outros foram “filtrados” pela multiplicação por 0).


Operações de Vectores “Ponto a Ponto”

• As mesmas operações “ponto a ponto” podem ser efectuadas na folha de cálculo, como ilustrado acima.

– Sendo o vector A constituído pelas células A1:E1, na linha 2 é criado o vector A>0, indicando para cada célula Bi a operação Bi = Ai > 0

– Na linha 3 são multiplicados os dois vectores, ponto a ponto.

– A soma dos elementos positivos do vector A é assim obtida na célula E3 através da função sum(A3:E3).

• De notar, que o mesmo conjunto de operações podia ser efectuado através de uma única função entre vectores

{=sum(A3:E3*(A3:E3>0))}Nota: As {} são inseridas através de CTRL-SHIFT-ENTER em vez do simples ENTER.

A B C D E somas1 3 - 5 4 0 6 82 TRUE FALSE TRUE FALSE TRUE3 3 0 4 0 6 13


Soma de uma Matriz• As mesmas técnicas podem ser utilizadas no caso de

matrizes, sendo mais “prático” o uso de funções.

A B C D E F1 0 - 3 4 2 3 62 - 3 1 7 9 14 173 2 0 3 - 4 1 54 - 1 - 2 14 7 18 285 2 1 14 11 28 28

E1 = SUM(A1:D1)E2 = SUM(A2:D2)E3 = SUM(A3:D3)

F1 = SUMIF(A1:D1,”>0”)F2 = SUMIF(A1:D1,”>0”)F3 = SUMIF(A1:D1,”>0”)

A4 = SUM(A1:A3).....D4 = SUM(D1:D3)

A4 = SUMIF(A1:A3,”>0”)...D4 = SUMIF(D1:D3,”>0”)

E4 = SUM(A1:D4)

F4 = SUM(F1:F3)E5 = SUM(E1:E4)F5 = SUMIF(A1:D4,”>0”)


Ciclos de Simulação – Trajectória de um Projéctil• A técnica usada no exemplo da simulação da trajectória de um

projéctil pode ser adaptada para uma folha de cálculo.

• Em geral, é necessário

– Utilizar uma coluna (ou linha) para as variáveis que estão a ser simuladas

– Inicializar as “linhas de cima” com as constantes do modelo e com os valores iniciais das variáveis

– Na linha seguinte obter os valores das variáveis a partir dos valores anteriores (i.e. da linha anterior) e das constantes.

– Tendo em atenção as referências relativas e/ou absolutas (constantes), copiar a 2ª linha para as linhas seguintes.

– Copiar tantas linhas quantas as necessárias


Apresentação do Problema• Um projéctil é lançado de uma altura de y0 metros, com um ângulo inicial de

lançamento de radianos e com uma velocidade inicial de v0 metros por segundo.

• A trajectória do projéctil em coordenadas (x,y) pode ser modelada através da seguinte equação:

02

2202

yx)θ(cosv

g)θtan(x)x(fy

v0

(0,0)x

y

y0

a

f(a)

• Problema: determinar a distância máxima (dmax) e a altura máxima (hmax) atingidas pelo projéctil.

dmax

hmax


Resolução Informal• Problema: determinar a distância máxima (dmax) e a altura

máxima (hmax) atingidas pelo projéctil.

• Pode-se simular a trajectória do projéctil usando a função f para calcular o valor de y correspondente a cada valor de x.

– Considera-se o ponto inicial da trajectória x0=0: (x0,y0)

– Considera-se uma sequência de valores de x (x1,x2,…) para os quais se calcula o respectivo valor de y usando a função f: (x1,f(x1)), (x2,f(x2)), …

– Termina-se o cálculo quando aparecer o primeiro ponto da trajectória com o valor de y negativo.

• Os valores da distância máxima e da altura máxima podem ser aproximados respectivamente pelos valores maximos de x e y obtidos nos pontos calculados da trajectória.


Resolução Informal• Graficamente a simulação da trajectória do projéctil corresponde a:

• O último ponto considerado é o x11 uma vez que é o primeiro a aparecer com y negativo: f(x11)<0

• A distância máxima (dmax) é aproximada pelo maior valor de x: dmax x11

• A altura máxima (hmax) é aproximada pelo maior valor de y: hmax f(x6)

(x0,y0)

x

y

(x1,f(x1))

(x2,f(x2))

(x11,f(x11))

(x6,f(x6))hmax^

dmax^

• A precisão das aproximações depende dos pontos da trajectória calculados: se a distância (em x) entre cada dois pontos consecutivos for dx então, em geral, a precisão aumenta quando dx diminui.


Programa Octave% Inicialização de Variáveis

g = 9.8; % aceleração da gravidadey0 = input(" Qual a altura inicial (m)? ");v0 = input(" Qual a velocidade inicial (m/s)? ");

tet = input(" Qual o angulo inicial (rad)? ");dx = input(" Qual a precisao (m)? ");dmax = 0; % distância máxima da trajectóriahmax = 0; % altura máxima da trajectória

% Ciclo de Simulaçãox = 0;y = y0;while y > 0

x = x + dx; y = x*tan(tet)-(g*x^2)/(2*v0^2*cos(tet)^2)+y0;

hmax = max(hmax,y);endwhile

% Apresentação de Resultadosdmax = x;disp("Distância maxima da trajectoria (m):"); disp(dmax);disp("Altura maxima da trajectoria (m):"); disp(hmax);


Constantes e Parâmetros do Modelog = 9.8 aceleração da gravidade y0 = 10 altura inicial (m) v0 = 5 velocidade inicial (m/s) tet = 1 angulo inicial (rad)dx = 0.1 precisao (m)

Resultados da Simulaçãodmax = 5.2 distância máxima da trajectória hmax = 10.9 altura máxima da trajectória

x y Aux0 100.1 10.10.2 10.30.3 10.4

Simulação

=MAX(C20:C200)

=MAX(E21:E200)

=C4

=B21*TAN(C$6)-(C$3*B21^2)/ (2*C$5^2*COS(C$6)^2)+C$4

=B20+C$7

=IF(AND(C20>0,C21<=0),B21,"")

Folha de Cálculo


Gráficos da Simulação

• Uma vez obtidas as tabelas de simulação, podem obter-se gráficos, seleccionando as colunas apropriadas e inserindo um gráfico (insert graph) com base nessa tabela.

Trajectória de um Projéctil

-10

0

10

20

0 5 10

x (m)

y (m

)


Ajuste de Parâmetros• Em geral, se forem dados os parâmetros de um modelo físico

pode ser simulado o seu comportamento. Mas se se pretender determinar os parâmetros que conduzem a um certo comportamento a situação não é em geral simples.

• Em muitos casos, a melhor solução é tentar as várias alternativas (em um ou mais ciclos encadeados) e verificar qual a adequada. Por exemplo o maior alcance pode ser tentado variando o ângulo, a velocidade inicial e a altura inicial.

• Nestas situações uma folha de cálculo não é muito adequada para resolver o problema pois exige que o utilizador tente (manualmente) os vários parâmetros.

• No caso do alcance podem existir 30 ângulos para testar (entre 31º e 60º com passo 1), 20 velocidades iniciais (de 11 a 20 com passo 0.5) e 10 alturas iniciais (de 0 a 10 com passo 1) o que dá um total de 30*20*10 = 6000 possibilidades!!!


Tratamento de Dados

• O tratamento de dados numéricos, nomeadamente por regressão linear entre duas variáveis X e Y, pode ser feito através da folha de cálculo.

• Para esse efeito deverão ser utilizadas duas colunas (uma para a variável) onde se guardam os valores X e Y observados

• Para se obter os parâmetros da recta que melhor aproxima os pontos observados podem calcular-se esses valores utilizando as funções slope e intercept.

• Pode depois construir-se uma nova coluna, com os valores dos Y esperados, baseados nos valores dos X e dos parâmetros da recta.

• Finalmente pode obter-se o gráfico dos valores dos Xs e Ys observados e dos Ys esperados.


Tratamento de Dadosm b r

2.75 43.83 0.93X

observado observado esperado188 606.4 561.140 161.4 153.9145 396.2 442.8113 445.7 354.788 248.6 285.957 209.7 200.6... ... ...

139 357.3 426.2

Y

0.0

100.0

200.0

300.0

400.0

500.0

600.0

700.0

800.0

0 50 100 150 200 250

Nota: O Excel permite obter uma ideia qualitativa da recta através da utilização da tendência no desenho do gráfico (opção add trendline).

Documents

24 Novembro 2006Folha de Cálculo 11 Jorge Cruz DI/FCT/UNL Introdução aos Computadores e à Programação 1º Semestre 2006/2007