Ficha 02 · Procura, filtros, tabelas dinâmicas, gráficos, ligações e macros

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.

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?

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.

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)?

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.

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?

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.

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.

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.