Ficha 02 · Procura, filtros, tabelas dinâmicas, gráficos, ligações e macros · Ficha de trabalho (soluções)

Aluno(a)

Turma

Nº

Data

Os exercícios 1 a 6 usam o registo de pedidos de apoio a pessoas idosas durante uma onda de calor, escrito a partir de A1 (cabeçalhos na linha 1, dados nas linhas 2 a 9). Os valores são fictícios.

Nota sobre as fórmulas. Escrevem-se com os nomes ingleses das funções. No Excel em português, escreve os nomes portugueses (SUM é SOMA, IF é SE, COUNTIF é CONTAR.SE, VLOOKUP é PROCV, e assim por diante), porque com o nome inglês o Excel devolve #NOME?. A tabela completa está no capítulo 7 da sebenta.

ID	Zona	Tipo de apoio	Pessoas	Dias
P-01	Alta	Transporte	4	2
P-02	Baixa	Água e alimentos	7	3
P-03	Alta	Água e alimentos	5	1
P-04	Ribeirinha	Transporte	3	2
P-05	Baixa	Contacto telefónico	9	4
P-06	Ribeirinha	Água e alimentos	6	2
P-07	Alta	Contacto telefónico	2	1
P-08	Baixa	Transporte	8	3

Exercício 1 · Procura com VLOOKUP

Numa folha Custos existe a tabela seguinte, em A1:B4: Transporte 6,00; Água e alimentos 4,50; Contacto telefónico 1,00 (custo por pessoa e por dia, em euros). (a) Escreve a fórmula, na célula F2 do registo, que procura o custo do tipo de apoio da linha 2. (b) Escreve a fórmula do custo total do pedido (Pessoas × Dias × custo) e calcula-o para P-02 e P-08. (c) Se uma linha tiver o tipo "Transporte " (com espaço no fim), o que aparece e como se evita?

(a) =VLOOKUP(C2; Custos!$A$2:$B$4; 2; FALSE). O FALSE pede correspondência exata e os cifrões fixam a tabela quando a fórmula é arrastada. Em P-01 devolve 6,00.

(b) =D2*E2*F2. - P-02: 7 pessoas × 3 dias × 4,50 = 94,50 euros. - P-08: 8 × 3 × 6,00 = 144,00 euros.

(c) Aparece #N/A (ou #N/D), porque "Transporte " não é igual a "Transporte". Evita-se limpando os dados com TRIM, e sobretudo impedindo o problema à entrada com uma lista pendente (validação de dados) na coluna Tipo de apoio. Para uma mensagem amigável: =IFERROR(VLOOKUP(C2; Custos!$A$2:$B$4; 2; FALSE); "Rever tipo").

Exercício 2 · Escalões e procura à esquerda

(a) Constrói uma tabela de escalões do nível de pedido em H2:I4: de 0 pessoas "Baixo", de 5 pessoas "Médio", de 8 pessoas "Alto". Escreve a fórmula que classifica P-03 (5 pessoas), P-05 (9) e P-07 (2) e indica os resultados. (b) Numa folha Equipas existe: A2 "Equipa 1" com B2 "Alta"; A3 "Equipa 2" com B3 "Baixa"; A4 "Equipa 3" com B4 "Ribeirinha". Escreve a fórmula que devolve a equipa responsável pela zona "Baixa" e explica por que não serve o VLOOKUP.

(a) =VLOOKUP(D4; $H$2:$I$4; 2; TRUE) (correspondência aproximada, primeira coluna ordenada de forma crescente). - P-03, 5 pessoas: Médio. - P-05, 9 pessoas: Alto. - P-07, 2 pessoas: Baixo.

Com exatamente 5 pessoas, o escalão é "Médio", porque 5 é o maior limite que não excede o valor.

(b) =INDEX(Equipas!$A$2:$A$4; MATCH("Baixa"; Equipas!$B$2:$B$4; 0)) devolve Equipa 2. MATCH encontra a posição 2 de "Baixa" na coluna B e INDEX devolve o valor que ocupa a posição 2 na coluna A. No Microsoft 365, no Excel 2021 e no Excel 2024 serve também =XLOOKUP("Baixa"; Equipas!$B$2:$B$4; Equipas!$A$2:$A$4), que não existe no Excel 2019 nem em versões anteriores.

O VLOOKUP só devolve valores à direita da coluna de procura; aqui a equipa está à esquerda da zona, pelo que a procura teria de ser invertida.

Exercício 3 · Filtros

Indica as linhas que ficam visíveis em cada pedido e calcula o que se pede. (a) Autofiltro: Tipo de apoio = Transporte e Pessoas maior ou igual a 4. (b) A soma das Pessoas visíveis dessa alínea, com a função adequada. (c) Filtro avançado: Zona = Alta ou Dias maior ou igual a 4. Descreve como se escreve a zona de critérios. (d) Porque é que =SUM(D2:D9) não serve na alínea (b)?

(a) Transporte tem P-01 (4 pessoas), P-04 (3) e P-08 (8). Com 4 ou mais pessoas ficam P-01 e P-08.

(b) =SUBTOTAL(109; D2:D9) = 4 + 8 = 12 pessoas.

Zona	Dias
Alta
	`>=4`

Condições em linhas diferentes combinam-se com OU. Ficam P-01, P-03, P-07 (zona Alta) e P-05 (4 dias): quatro linhas.

(d) SUM soma todas as células do intervalo, incluindo as linhas escondidas pelo filtro, e devolveria 44. SUBTOTAL(109; ...) soma só as linhas visíveis.

Exercício 4 · Formatação condicional

Pretende-se (a) realçar as células de Pessoas com valor maior ou igual a 7 e (b) realçar a linha inteira (A a E) dos pedidos de Transporte. Indica a regra de cada alínea, a fórmula necessária na alínea (b) e as linhas ou células que ficam realçadas. Explica depois porque é que o cifrão tem importância na fórmula.

(a) Seleciona D2:D9, regra Realçar Células > Maior ou igual a 7. Ficam realçadas as células de P-02 (7), P-05 (9) e P-08 (8): 3 células.

(b) Seleciona A2:E9 e cria uma regra por fórmula: =$C2="Transporte". Ficam realçadas as linhas P-01, P-04 e P-08.

O cifrão antes de C fixa a coluna: em qualquer célula da linha, a regra olha para a coluna C. A falta de cifrão antes de 2 deixa a linha deslocar-se: na linha 5 a regra lê C5, e assim por diante. Sem o cifrão da coluna, a regra desalinhava-se e realçava células ao acaso.

Exercício 5 · Tabela dinâmica

Cria uma tabela dinâmica a partir do registo com Zona em Linhas, Tipo de apoio em Colunas e Soma de Pessoas em Valores. (a) Preenche os totais por zona e o total geral. (b) Calcula o peso de cada zona no total, em percentagem. (c) Quantos pedidos tem cada zona (campo ID em Contagem)? (d) Alteras o valor de Pessoas de P-07 de 2 para 12 na folha de dados. O que tens de fazer para o relatório refletir a alteração e qual passa a ser o total da zona Alta?

(a) Totais por zona: Alta 11 (4 + 5 + 2), Baixa 24 (7 + 9 + 8), Ribeirinha 9 (3 + 6). Total geral: 44, igual a =SUM(D2:D9).

(b) Alta: 11 ÷ 44 = 25%. Baixa: 24 ÷ 44 = cerca de 54,5%. Ribeirinha: 9 ÷ 44 = cerca de 20,5%. A soma é 100%.

(d) É necessário atualizar a tabela dinâmica (botão direito > Atualizar, ou Alt+F5), porque não se atualiza sozinha. Depois da alteração, a zona Alta passa a 4 + 5 + 12 = 21, e o total geral a 54.

Leitura: a zona Baixa concentra mais de metade das pessoas apoiadas, com o mesmo número de pedidos da zona Alta.

Exercício 6 · Escolher e criticar gráficos

(a) Para cada situação, escolhe o tipo de gráfico e justifica: (i) pessoas apoiadas por zona (11, 24 e 9); (ii) evolução diária do número de pedidos ao longo de dez dias; (iii) relação entre número de pessoas e número de dias de cada pedido. (b) Um colega fez um gráfico de colunas das três zonas com o eixo vertical a começar em 8. Que problema há? (c) Indica cinco elementos que o gráfico (i) deve ter para ser claro.

(a) (i) Colunas ou barras: compara três categorias. Um circular também serviria, porque são poucas categorias que somam um total (44), mas as colunas permitem comparar melhor. (ii) Linhas: mostra a evolução no tempo. (iii) Dispersão: relação entre duas variáveis numéricas.

(b) O eixo cortado exagera as diferenças: a coluna da zona Ribeirinha (9) ficaria quase invisível face à da zona Baixa (24), quando na realidade a Baixa tem pouco mais de 2,5 vezes o valor da Ribeirinha. Em colunas e barras, o eixo deve começar em zero.

(c) Por exemplo: título que diga o que mostra, com o tipo de dados e as unidades ("Pessoas apoiadas por zona"); título do eixo vertical com unidade (pessoas); rótulos de dados (11, 24, 9); colunas ordenadas do maior para o menor; poucas cores, uma só se há uma série; sem legenda quando há uma só série; eixo a começar em zero. Confirma ainda que a soma dos rótulos (11 + 24 + 9) é 44.

Exercício 7 · Ligar folhas e consolidar

Um livro tem as folhas Semana1, Semana2 e Semana3, com a mesma estrutura, e em cada uma a célula D20 contém o total de pessoas apoiadas: 44, 31 e 27. (a) Escreve a fórmula, numa folha Resumo, que soma o total das três semanas com uma referência 3D e indica o resultado. (b) Que condição tem de se verificar para a referência 3D funcionar? (c) Um colega envia por email, a uma entidade externa, o livro Resumo, que vai buscar valores a outro ficheiro por ligação. O destinatário vê erros. Explica porquê e o que devia ter feito.

(a) =SUM(Semana1:Semana3!D20) = 44 + 31 + 27 = 102 pessoas.

(b) As folhas têm de ter exatamente a mesma estrutura, com o total no mesmo endereço (D20), e estar consecutivas no livro. Uma folha acrescentada entre Semana1 e Semana3 entra no cálculo; uma folha à direita de Semana3 não entra.

(c) As ligações a outros ficheiros dependem do caminho e da existência do ficheiro de origem. No computador do destinatário a origem não existe, por isso a atualização falha e aparecem erros ou valores antigos. O colega devia converter as fórmulas em valores (Colar especial > Valores) antes de enviar, ou enviar também a origem. Mover ou mudar o nome de um ficheiro de origem também quebra a ligação (resolve-se em Dados > Editar Ligações > Alterar Origem).

Exercício 8 · Macros e partilha segura

(a) Lê esta macro e explica o que faz e o que escreve para o registo anterior (os pedidos estão nas linhas 2 a 9; a coluna D tem Pessoas e a coluna G é livre):

Sub MarcarPedidosGrandes()
    Dim linha As Long
    For linha = 2 To 9
        If Cells(linha, 4).Value >= 8 Then
            Cells(linha, 7).Value = "Alto"
        End If
    Next linha
End Sub

(b) Que formato de ficheiro é preciso para guardar esta macro e que três cuidados deves ter antes de a executar? (c) Uma entidade externa pede só "o número de pessoas apoiadas por zona", mas o teu livro tem também uma folha Contactos com nomes e telefones. Indica quatro passos para partilhar com segurança.

(a) A macro percorre as linhas 2 a 9 e, se o valor da coluna 4 (Pessoas) for maior ou igual a 8, escreve "Alto" na coluna 7 (G). No registo são P-05 (9 pessoas) e P-08 (8 pessoas): aparece "Alto" em G6 e em G9. P-02 tem 7 pessoas e fica de fora. Uma melhoria: em vez de fixar a linha 9, detetar a última linha preenchida com Cells(Rows.Count, 1).End(xlUp).Row, para a macro funcionar com mais pedidos.

(b) Livro com macros, .xlsm; em .xlsx a macro perde-se. Cuidados: guardar uma cópia antes de executar (não há anular), testar numa cópia com poucos dados, e só ativar macros de ficheiros de origem de confiança.

(c) Quatro passos: (1) aplicar a minimização, enviando só o resumo de totais por zona e não a folha Contactos; (2) criar uma cópia do livro, apagar a folha de contactos e converter as fórmulas em valores para não restarem ligações; (3) inspecionar o documento e remover metadados, comentários e folhas ocultas; (4) proteger o ficheiro com palavra-passe de abertura, enviada por um canal diferente do ficheiro. Se uma zona tiver muito poucas pessoas, convém agregá-la para ninguém ser identificável.