Programação para as
Ciências Experimentais
2007/8
Teórica 12
Ludwig Krippahl, 2008
Na aula de hoje... Excel





Usar dados dinâmicos
•
Trocar dados com o Octave
“Array formulas”, formulas com vectores ou
matrizes.
Gráficos
Consulta de tabelas
Algumas funções úteis
•
Macros, Histogramas, Referência dinâmica
(OFFSET), Formatação condicional, goal seek
Ludwig Krippahl, 2008
2
Excel e Octave

Exemplo: contar microorganismos no ar
(ver aula 7)
function cs=colonias(buracos, ufcs,
tentativas)
function
u=contaufcs(buracos,cs,tentativas)
Ludwig Krippahl, 2008
3
Contar microorganismos no ar




Bomba aspira ar.
Orifícios sobre placa.
Contar colónias.
Estimar UFCs.
Ludwig Krippahl, 2008
4
Contar microorganismos no ar

Problema:
• Podem entrar vários esporos ou bactérias
pelo mesmo orifício, resultando numa só
colónia.
Ar
Ludwig Krippahl, 2008
5
Excel e Octave
function cs=colonias(buracos, ufcs,
tentativas)
Devolve o número de colónias estimado
(média das tentativas) para o numero de
UFCs dado
Ludwig Krippahl, 2008
6
Excel e Octave
function
u=contaufcs(buracos,cs,tentativas)
Estima o numero de UFCs (média das
tentativas) a partir no numero de
colónias (usa a função anterior para
experimentar valores).
Ludwig Krippahl, 2008
7
Excel e Octave

Esquema:
• Temos os dados no Excel
• Gravamos num ficheiro txt
• O Octave lê, estima os UFCs, e grava um
•
ficheiro
O Excel actualiza a informação
Ludwig Krippahl, 2008
8
Excel e Octave

Exportar dados do Excel
• Forma mais prática: seleccionar, paste no
notepad, gravar.

Ler com o Octave
• Ter atenção ao formato do ficheiro...
Ludwig Krippahl, 2008
9
Excel e Octave


Exportar dados do Excel
Copy (ctrl+c)
Ludwig Krippahl, 2008
10
Excel e Octave




Exportar dados do Excel
Copy (ctrl+c)
Paste (ctrl+v)
Atenção aos tabs
No wordpad
Ficam duas
colunas
Ludwig Krippahl, 2008
11
Excel e Octave





Exportar dados do Excel
Copy (ctrl+c)
Paste (ctrl+v)
Atenção aos tabs
Gravar
• Dados.txt
Ludwig Krippahl, 2008
12
Excel e Octave, ler os dados
function [orif,cols]=lerdados(fich)
fid=fopen(fich,"r");
orif=fscanf(fid,"Orificios\t%i\n“,”C”);
cols=[];
fgetl(fid);
Abre o ficheiro e lê o
while !feof(fid)
número de orifícios.
c=fscanf(fid,"%i“,”C”);
cols=[cols,c];
endwhile
fclose(fid);
endfunction
Ludwig Krippahl, 2008
13
Excel e Octave, ler os dados
function [orif,cols]=lerdados(fich)
fid=fopen(fich,"r");
orif=fscanf(fid,"Orificios\t%i\n“,”C”);
cols=[];
fgetl(fid);
Salta “Colónias:”
while !feof(fid)
c=fscanf(fid,"%i“,”C”);
cols=[cols,c];
endwhile
fclose(fid);
endfunction
Ludwig Krippahl, 2008
14
Excel e Octave, ler os dados
function [orif,cols]=lerdados(fich)
fid=fopen(fich,"r");
orif=fscanf(fid,"Orificios\t%i\n“,”C”);
cols=[];
fgetl(fid);
Lê os valores
while !feof(fid)
c=fscanf(fid,"%i“,”C”);
cols=[cols,c];
endwhile
fclose(fid);
endfunction
Ludwig Krippahl, 2008
15
Excel e Octave, calcular
function calculaegrava(orif,tentat,fich,cols)
fid=fopen(fich,"w");
for f=1:length(cols)
u=contaufcs(orif,cols(f),tentat);
fprintf(fid,"%i\t%i\r\n",cols(f),u);
Calcula os UFCs para
endfor
cada valor no vector de
fclose(fid);
colónias, e grava no
endfunction
ficheiro
Ludwig Krippahl, 2008
16
Excel e Octave, calcular
function calculaegrava(orif,tentat,fich,cols)
fid=fopen(fich,"w");
for f=1:length(cols)
u=contaufcs(orif,cols(f),tentat);
fprintf(fid,"%i\t%i\r\n",cols(f),u);
Mudar de linha em DOS
endfor
e Windows (em Linux só
fclose(fid);
é preciso \n)
endfunction
Ludwig Krippahl, 2008
17
Excel e Octave, calcular
Ludwig Krippahl, 2008
18
Excel e Octave, actualizar

Basta agora um script para actualizar
(actualiza.m), que lê os dados de
dados.txt, calcula com 20 tentativas
cada e grava em ufcs.txt:
[orif,d]=lerdados("dados.txt")
calculaegrava(orif,20,"ufcs.txt",d)
Ludwig Krippahl, 2008
19
Excel e Octave, actualizar

Agora temos que importar os resultados
no Excel
Ludwig Krippahl, 2008
20
Excel e Octave, actualizar

Importar os resultados
Ludwig Krippahl, 2008
21
Excel e Octave, actualizar

Importar os resultados
Ludwig Krippahl, 2008
22
Excel e Octave, actualizar

Importar os resultados
Ludwig Krippahl, 2008
23
Excel e Octave, actualizar

Advanced para conversão do formato.
Por exemplo . em vez de , nos números.
Ludwig Krippahl, 2008
24
Excel e Octave, actualizar

Advanced para conversão do formato.
Por exemplo . em vez de , nos números.
Ludwig Krippahl, 2008
25
“Array formulas”

Dois tipos:
• Fórmulas que operam sobre um vector ou
•
matriz de elementos
Fórmulas que devolvem uma matriz de
elementos
Ludwig Krippahl, 2008
26
“Array formulas”


Fórmulas que operam sobre um vector
ou matriz de elementos
Exemplo: Quantos alunos melhoraram
do primeiro para o segundo teste.
• Teste 1 em A3:A13, teste 2 em B3:B13
• =SUM(IF(B3:B13>A3:A13;1;0))
• ctrl+shift+enter para indicar que é uma
fórmula para vectores
Ludwig Krippahl, 2008
27
“Array formulas”
• Teste 1 em A3:A13, teste 2 em B3:B13
• =SUM(IF(B3:B13>A3:A13;1;0))
• ctrl+shift+enter para indicar que é uma
•
fórmula para vectores
O Excel vai percorrer todas as células,
comparar elemento a elemento, somar 1 cada
vez que a condição é verdadeira e devolve a
soma.
Ludwig Krippahl, 2008
28
“Array formulas”

Fórmulas que devolvem uma matriz de
elementos

Exemplo: a inversa de uma matriz:
MINVERSE

Ludwig Krippahl, 2008
29
“Array formulas”

Exemplo: a inversa de uma matriz em
A1:B3
• Seleccionar as células onde aparecerá o
•
•
resultado
=MINVERSE(A1:B3)
ctrl+shift+enter
Ludwig Krippahl, 2008
30
Gráficos

Para fazer um gráfico basta seleccionar
as células e carregar no botão
• Alguns detalhes:
• Com valores de X e Y tem que ser sempre o
XY(Scatter)
• Se temos valores em colunas com formatos
diferentes, ou vários conjuntos X e Y o mais prático
é fazer o gráfico para a primeira série e depois
acrescentar outras com copy e paste special
Ludwig Krippahl, 2008
31
Gráficos

Exemplo: comparar x2 com x3
• Calcular pontos x, y
• Seleccionar os do x2, traçar gráfico
• Seleccionar os do x3, copy
• Seleccionar gráfico, Edit, Paste Special
Ludwig Krippahl, 2008
32
Gráficos

Exemplo: comparar x2 com x3
• Seleccionar gráfico, Edit, Paste Special
• Especificar nova série com valores de x na
primeira coluna:
Ludwig Krippahl, 2008
33
Consultar tabelas (VLOOKUP)

VLOOKUP(Valor a encontrar;
Tabela;
Índice da coluna a devolver;
Procura por intervalo (True/False))

Exemplo: encontrar a nota de um aluno
Ludwig Krippahl, 2008
34
Consultar tabelas (VLOOKUP)


Exemplo: encontrar a nota de um aluno
=VLOOKUP(C9;A1:B6;2;FALSE)
• Não queremos que devolva um valor dentro
do intervalo, mas apenas o valor exacto
Ludwig Krippahl, 2008
35
Consultar tabelas (VLOOKUP)





Exemplo: Altura versus peso
=VLOOKUP(H10;I3:J7;2;TRUE)
Se não for esse o valor exacto para a
altura, queremos o peso aproximado
Neste caso dá o último que encontrou
antes de passar o valor pedido.
(Ver folha excel-t10.xls, página Lookup)
Ludwig Krippahl, 2008
80
36
Outras funções úteis




SUM, AVERAGE, SUMIF, COUNT,
COUNTIF
AND, OR (para usar no IF, por exemplo)
LINEST para regressão linear
Receita:
80
• Seleccionar 2x3
• Linest(Ys, Xs, True, True), ctrl+shift+enter
Ludwig Krippahl, 2008
37
Outras funções úteis

LINEST para regressão linear
• Seleccionar 2x3
• Linest(Ys, Xs, True, True), ctrl+shift+enter
80
• Ver no help
m
b
erro m erro b
r^2
Ludwig Krippahl, 2008
erro y
38
Outras funções úteis

Como usar funções que não conhecem:
• Ver no help.
• Usar Insert, Function, e seguir as instruções.
• Procurar no Google.
Ludwig Krippahl, 2008
39
Macros

«Macro-instrução»
• Conjunto de instruções que é executado
•
como uma só (significado inicial, 1970s).
Hoje em dia tem um significado mais geral,
refere-se a qualquer programa escrito numa
linguagem usada para controlar uma
aplicação. No caso do Excel, Visual Basic.
Ludwig Krippahl, 2008
40
Macros

Gravar macro:
Ludwig Krippahl, 2008
41
Macros

Gravar macro:
• Tools->Macro->Record new Macro
• Dar nome (e.g. Ordenar).
Ludwig Krippahl, 2008
42
Macros

Gravar macro:
• Tools->Macro->Record new Macro
• Dar nome (e.g. Ordenar).
• No final, carregar no stop
Ludwig Krippahl, 2008
43
Macros

Alternativas para correr a macro:
• Tools->Macro->Macros->Run
• Usar a combinação de teclas definida quando
•
gravámos (ou definir uma em Tools->Macro>Macros->Options
Atribuir a macro a um objecto na folha (um
botão, um desenho). Para um desenho, click
botão da direita e “Assign Macro”.
Ludwig Krippahl, 2008
44
Macros

Bom para tarefas repetitivas de
formatação, ordenação, reposicionar
dados, etc.
Ludwig Krippahl, 2008
45
Macros

Para ver o código:
• Tools->Macro->Visual Basic Editor
• Modules
Ludwig Krippahl, 2008
46
Macros
Sub Ordena()
Columns("A:B").Select
Selection.Sort Key1:=Range("A2"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
Ludwig Krippahl, 2008
47
Histogramas

Função FREQUENCY(

É uma função que devolve um vector de
dimensão igual a um mais o número de
separadores, com as contagens dos
elementos entre os separadores (ou
maiores que o último)
• Dados;
• Separadores)
Ludwig Krippahl, 2008
48
Histogramas
Dados
Separadores
FREQUENCY
Ludwig Krippahl, 2008
49
Referências dinâmicas

OFFSET(
• Célula de referência, a partir da qual
•
•
•
•

contamos as linhas e colunas;
Linhas;
Colunas;
Altura;
Largura)
Tem que ser usada como argumento de
outra função (e.g. SUM, AVERAGE...)
Ludwig Krippahl, 2008
50
Referências dinâmicas


Exemplo:
OFFSET(A1,3,5,2,4)
• Grupo de 2x4 (altura x largura) células a
começar da 3ª linha e 5ª coluna a contar da
A1.
Ludwig Krippahl, 2008
51
Referências dinâmicas

Exemplo:
Ludwig Krippahl, 2008
52
Referências dinâmicas


É útil para fazer depender a referência
de um valor calculadoo número de
células não vazias. . Por exemplo, com
COUNTA, que conta
Exemplo
• =SUM(OFFSET(A1;1;1;COUNTA(B:B)-1;1))
• Soma a coluna dos gastos, qualquer que seja
o numero de entradas (o -1 desconta o
cabeçalho “Gastos”).
Ludwig Krippahl, 2008
53
Formatação Condicional



Seleccionar células
Format->Conditional Formatting
Especificar condições e formatação se
as condições forem preenchidas
(Exemplo: a vermelho e bold se o gasto
for maior que €100).
Ludwig Krippahl, 2008
54
Dúvidas
Ludwig Krippahl, 2008
55