Neste novo artigo vou mostrar-te como podes em poucos minutos criar um dashboard com as funções de Arrays Dinâmicos e a função IMAGEM para apresentar uma imagem de forma dinâmica em função de um valor selecionado.

Neste artigo vamos então utilizar várias funções juntamente com a função IMAGEM, nomeadamente:

• A função INDIRETO [INDIRECT]
• A função FILTRAR [FILTER]
• A função EXCLUIR [DROP]
• A função EXCLUSIVOS [UNIQUE]
• A função ORDENAR [SORT]

E ainda lista pendente e gráficos com intervalos dinâmicos. Fica por aí!

Dados base do relatório

O ficheiro base apresenta 2 folhas de base, uma folha com a base de dados com os registos de transações de cada colaborador, onde o utilizador acrescenta dados e uma segunda folha (auxiliar) que apresenta uma lista de colaboradores e os respetivos links para as imagens alojadas na internet – esta hiperligação é importante para a função imagem.

Definir os dados auxiliares para a estrutura do relatório

Associar a hiperligação a cada um dos nomes dos colaboradores

Para podermos associar a hiperligação a cada um dos colaboradores, uma das possibilidades é atribuir um nome às células, a partir da seleção. Acedemos ao menu Fórmulas [Formulas] e no grupo de Nomes Definidos [Defined Names] encontramos a opção Criar a partir da Seleção [Create from selection]

Escolhemos a opção – Coluna da Esquerda

E obtemos um conjunto de nomes para cada colaborador.

Converter a base de dados numa tabela

Convertemos também a base de dados numa tabela para permitir mais facilmente aceder aos seus campos nas fórmulas e principalmente atualizar os dados de forma dinâmica.

Na folha Base, selecionamos uma das células e inserimos a tabela através do Menu Inserir [Insert] e escolhemos a opção Tabela [Table].

Nomeamos a tabela como “Dados”

E temos a base definida.

Definir os intervalos para o gráfico dinâmico

Começamos por definir o intervalo da zona, pretendido para o gráfico através da função FILTRAR [FILTER].

=FILTRAR(Dados[Zona];Dados[Agente]=$D$2)

E de seguida o intervalo do Preço de Compra, pretendido para o gráfico também através da função FILTRAR [FILTER].

=FILTRAR(Dados[Preço de Compra];Dados[Agente]=$D$2)

Desta forma obtemos os 2 intervalos necessários para o Gráfico, que mudam em função do nome selecionado na célula D2.

Criar o Gráfico através dos dados auxiliares.

O gráfico será movido para a folha do relatório, contudo a preparação do mesmo pode ser feita na folha auxiliar.

Definir o intervalo de dados do gráfico

O intervalo dos dados do gráfico vai ser modificado para que o mesmo seja dinâmico. Para tal devemos atribuir um nome a cada intervalo. O nome é definido pela fórmula que resulta do cálculo do intervalo.

Nomes definidos:

Zona_Grafico: =FILTRAR(Dados[Zona];Dados[Agente]=$D$2)

Preco_compra: =FILTRAR(Dados[Preço de Compra];Dados[Agente]=$D$2)

Definimos o primeiro nome:

E a seguir o segundo nome:

De seguida alteramos os dados do gráfico, atualizando-os com os novos intervalos, de acordo com os nomes definidos.

Definimos o intervalo da serie:

E de seguida o intervalo das categorias…

Ainda na folha auxiliar criamos ainda uma lista com os agentes, através da função EXCLUSIVOS [UNIQUE] e aproveitamos para ordenar a lista com a função ORDENAR [SORT].

=ORDENAR(EXCLUSIVOS(Dados[Agente]))

Criar o relatório

Criamos uma folha e vamos começar então a criar o relatório.

Na nova folha colocamos na célula B2 “Agente” e na célula C2 através de uma lista pendente com validação de dados: Menu Dados [Data] > Validação de Dados [Data Validation]. Escolhemos a validação do tipo lista e associamos ao intervalo da folha auxiliar gerado pelo Array Dinâmico.

Agora a célula do nome, na folha auxiliar, será ligada à célula C2 do relatório.

Associar uma imagem ao agente

Num conjunto de células fundidas, junto ao nome do agente vamos então inserir a função IMAGEM [IMAGE] que insere imagens nas células a partir de uma localização de origem (url) juntamente com um texto alternativo.

A sua sintaxe é muito simples:

=IMAGEM(origem; [alt_text]; [tamanho]; [altura]; [largura])

• Origem (obrigatório): O caminho do URL, utilizando um protocolo “https”, do ficheiro de imagem. Os formatos de ficheiro suportados incluem BMP, JPG/JPEG, GIF, TIFF, PNG, ICO e WEBP.
• texto_alternativo: Texto alternativo que descreve a imagem para acessibilidade. Este argumento é opcional.
• Dimensionamento: Especifica as dimensões da imagem.
  • Escolhendo a opção 0, ajusta a imagem na célula e mantém a sua a proporção.
  • A opção 1 preenche a célula com a imagem, mas ignora a proporção da mesma.
  • A opção 2, mantém o tamanho da imagem original, que pode exceder o limite da célula.
  • A opção 3 permite personalizar o tamanho da imagem com os argumentos de altura e largura, definidos nos próximos 2 argumentos.

Neste caso em especifico, o primeiro argumento da origem, é definido pela função INDIRETO [INDIRECT] que é bastante útil, pois permite associar o nome do agente ao conteúdo do url definido na folha auxiliar.

Criar a tabela com os dados

Para a tabela com os dados vamos utilizar novamente a função FILTRAR [FILTER] para obter apenas os registos de acordo com o Agente indicado na célula C2.

= FILTRAR(Dados;Dados[Agente]=$C$2)

Contudo, no intervalo resultante, vamos excluir uma das colunas, neste caso a primeira que contem o nome do agente e não é necessária para o relatório. Assim incluímos a função FILTRAR dentro da função EXCLUIR [DROP] para excluirmos a primeira coluna do Array dinâmico resultante.

= EXCLUIR(FILTRAR(Dados;Dados[Agente]=$C$2);;1)

Mover o Gráfico para o Relatório e Formatar

O último passo é o de mover o gráfico para a folha do relatório e alterar alguns formatos a gosto para o mesmo.

Na folha auxiliar, selecionamos o gráfico e no menu da Estrutura escolhemos a opção Mover Gráfico.

Alteramos de seguida o título do gráfico para ligar à célula onde temos o nome do agente…

Acrescentamos alguns elementos ao gráfico, como a etiqueta de dados.

E formatamos os restantes elementos a gosto.

Retiramos as linhas de grelha da folha: Menu Ver [View] > Linhas de Grelha [Gridlines]

Ocultamos as colunas e linhas do resto da folha e temos o relatório apto a analisar os dados.