230 likes | 408 Vues
Folha de Cálculo 2. Pedro Barahona DI/FCT/UNL Introdução aos Computadores e à Programação 2º Semestre 2005/2006. Folha de Cálculo. As possibilidades de acesso e tratamento avançado de informação numa folha de cálculo serão ilustradas através de alguns exemplos. Ordenação de Registos
E N D
Folha de Cálculo 2 Pedro Barahona DI/FCT/UNL Introdução aos Computadores e à Programação 2º Semestre 2005/2006 Folha de Cálculo
Folha de Cálculo • As possibilidades de acesso e tratamento avançado de informação numa folha de cálculo serão ilustradas através de alguns exemplos. • Ordenação de Registos • Agregações (médias, médias ponderadas, extremos) • Contagens e contagens condicionais • Determinação da ordem • Descoberta de um valor numa tabela • Selecção de valor numa tabela através da sua linha e coluna Folha de Cálculo
Ordenação de Registos • Começamos por considerar a apresentação ordenada de um conjunto de registos, dispostos em várias linhas. • No EXCEL tal pode ser facilmente obtido através da operação “sort” na entrada “data” do menu. • Nesta ordenação podem ser seleccionados • os “campos” por onde se faz a ordenação, • A sua prioriades (um campo só é utilizado em caso de empate nos campos mais prioritários), • a ordem (crescente ou decrescente) utilizada, e Folha de Cálculo
Ordenação de Registos • Dada a pauta da turma abaixo ela pode, por exemplo, ser ordenada • Por ordem crescente dos números ou nomes dos alunos; • Por ordem decrescente das suas notas (1, 2 ou 3) Folha de Cálculo
Ordenação de Registos • Por ordem crescente dos números dos alunos • Seleccionar a tabela • Menu: Data - Sort • Opções: num, ascending Folha de Cálculo
Ordenação de Registos • Por ordem decrescente dos nomes dos alunos (notar o correcto tratamento dos acentos) • Seleccionar a tabela • Menu: Data - Sort • Opções: nome, descending Folha de Cálculo
Ordenação de Registos • Por ordem decrescente da nota 3 • Seleccionar a tabela • Menu: Data - Sort (menu) • Opções: nota 3, descending Folha de Cálculo
Agregações (Médias, Somas) • A determinação de agregações de dados é feita através da utilização das operações apropriadas em células adicionais. • Por exemplo para determinar a médias das notas de uma aluno, pode utilizar-se a sua soma dividida por 3. • Mais genericamente podem utilizar-se as funções predefinidas sum e count, ou mais directamente a função average. = (D3+D4+D5)/3 = average(D3:D5) = sum(D3:D5) / count(D3:D5) Folha de Cálculo
Agregações (Médias, Somas) • As várias médias (por disciplinas e por alunos) podem ser acrescentadas à folha inicial o que permite eventualmente a posterior apresentação ordenada por médias dos alunos Folha de Cálculo
Agregações (Extremos) • Outras funções de agregação podem ser usadas para determinar por exemplo os máximos e mínimos = min(D6:F6) = max(D3:D11) Folha de Cálculo
Agregações (Médias Ponderadas) • A média ponderada de n valores vi, cada um com peso pi, é obtida através da fórmula • Em EXCEL tal pode ser implementado pelas fórmulas Nota: Array expression CTRL-SHIFT-ENTER = { SUM(C5:E5*C$7:E$7)/SUM(C5:E5) } = sumproduct(C5:E5*C$7:E$7)/SUM(C5:E5) Folha de Cálculo
Agregações (Médias Ponderadas) • Eis o exemplo anterior com médias e médias ponderadas. De notar o endereçamento absoluto na linha dos pesos. = {sum(D9:F9*D$2:F$2)/sum(D$2:F$2)} = sumproduct(D9:F9,D$2:F$2)/sum(D$2:F$2) Folha de Cálculo
Arredondamentos • Tal como em OCTAVE, o Excel considera todos os dados numéricos como reais. No entanto eles podem ser convertidos em inteiros (operações, floor, ceiling e round), ou simplesmente mostrados com a precisão desejada (não alterando o seu valor)... Format: cells: number: decimal places: 0 = round(B6) Folha de Cálculo
Contagens • Outras operações de agregação´muito utilizadas são operações de contagem, especialmente as condicionais. Para tal poderão ser utilizadas as funções prédefinidas sum / sumif ou, mais directamente, count / countif. = sumif(int(H$4:H$12= J11)) = countif(H$4:H$12,J4) Folha de Cálculo
Contagens • As comparações podem ser feitas não apenas para valores iguais, mas também para valores que satisfaçam uma certa condição. = countif(H$4:H$12,”>12”) Ou com a operação sobre vectores = { sum(int(H$4:H$12>12)) } Folha de Cálculo
Parametrização com texto • Para parametrizar os campos de texto, podem ser usadas operações sobre strings, nomeadamente a concatenação. = countif(H$4:H$12,concatenate(">",J4)) ou = countif(H$4:H$12,concatenate(K$3,J4)) Folha de Cálculo
Parametrização com texto • Para expressar critérios de selecção mais complexos podem utilizar-se expressões booleanas ou a sua conversão em expressões numéricas (em Excel,as operações booleanas não são muito “fiáveis”). = { sum(int(H4:H12 >= J3) * int(H4:H12 <= L3)) } = { sum(int(H4:H12 < J9) + int(H4:H12 > L9)) } Folha de Cálculo
Classificações • A classificação (“ranking”) de valores num vector/matriz é igualmente uma operação frequente e que pode ser obtida por contagens condicionais, ou directamente pela função rank. = {sum(int(G$4:G$12 >= G6))} = rank(G8,G$4:G$12 ) Folha de Cálculo
Procura de valores em tabelas • A instrução vlookup permite que dado um valor de referência, se seleccione um valor numa tabela, na linha começada por esse valor, e colocado na coluna especificada. Por exemplo, dado o número de um aluno, pode obter-se o seu nome = vlookup(H5,B4:F12,2,false) Folha de Cálculo
Procura de valores em tabelas • De notar que a tabela tem de ter o valor de referência na coluna mais à esquerda. Por exemplo, se pretendermos obter os nomes dos primeiros 5 alunos, temos de colocar a coluna de classificação à esquerda da tabela = vlookup(J6,B$4:D$12,3,false) Folha de Cálculo
Seleccão por linha e coluna • Um valor pode ser seleccionado de uma tabela através da função index, se for indicada a linha e coluna pretendida. Em alguns casos, a posição (linha ou coluna) pretendida pode ser obtida pela função auxiliar match. = match(K5,H4:H12,0) 5ª posição que também pode ser obtida através da utilização da função row = {sum(int(K5=H$4:H$12)*(row(H$4:H$12)-row(H3)))} Folha de Cálculo
Seleccão por linha e coluna • A utilização conjunta das funções index e match permite determinar não só a posição do elemento máximo de um vector mas outros campos relacionados registados noutras colunas. 5ª posição = max(H4:H12) = index(B4:D12, match(L6,H4:H12,0), 2) = index(B4:D12,match(L6,H4:H12,0), 3) Folha de Cálculo
Seleccão por linha e coluna • A determinação da posição de um valor máximo numa tabela deve ser feita pelos “equivalentes” da função match. = max(C4:G12) ={sum(int(K5=H$4:H$12)*(row(H$4:H$12)-row(H3)))} ={sum(int(K5=H$4:H$12)*(column(H$4:H$12)-column(H3)))} Folha de Cálculo