Excel Dashboards e Relatórios: List Box Exemplo

Uma das maneiras mais úteis para usar uma caixa de listagem em seus painéis e relatórios do Excel é controlar vários gráficos com um selector. A figura seguinte ilustra um exemplo deste. Como uma seleção de região é feita na caixa de lista, todos os três gráficos são alimentados os dados para aquela região, ajustando as tabelas para corresponder com a seleção feita.

Felizmente, tudo isso é feito sem o código VBA, apenas um punhado de fórmulas e uma caixa lista.

Para criar este exemplo, começar com três conjuntos de dados brutos - como mostrado na figura a seguir - que contêm três categorias de dados: receitas, do resultado líquido%, e margem bruta. Cada conjunto de dados contém uma linha separada para cada região, incluindo uma para todas as regiões.

Em seguida, adicione uma caixa de lista que devolve o número de índice do item selecionado para P2 celular, como mostrado na figura a seguir.

Em seguida, criar uma tabela de teste que consiste em todas as fórmulas. Nesta tabela de teste, você usa o Excel de ESCOLHER funcionar para seleccionar o valor correcto a partir das tabelas de dados não processados ​​com base na região seleccionada.

No Excel, o ESCOLHER função retorna um valor a partir de uma lista específica de valores com base em um número de posição especificado. Por exemplo, a fórmula ESCOLHA (3, "Red", "Yellow", "Green", "Blue") retorna verde porque o verde é o terceiro item na lista de valores. A fórmula ESCOLHA (1, "Red", "Yellow", "Green", "Blue") retorna vermelho.

Como você pode ver na figura a seguir, o ESCOLHER fórmula recupera o número da posição do alvo a partir de P2 célula (a célula onde a caixa de lista emite o número de índice do item selecionado) e, em seguida, corresponde ao número de posição na lista de referências de célula dada. As referências de células vêm diretamente da tabela de dados brutos.

No exemplo mostrado na figura acima, os dados que serão obtidos com este ESCOLHER fórmula é 41767. Por quê? Porque P2 célula contém o número 3, e a referência de célula terceiro dentro do ESCOLHER fórmula é a célula B9.

Você digite o mesmo tipo de ESCOLHER fórmula para a coluna de Janeiro e, em seguida, copiá-lo do outro lado, como mostrado na figura a seguir.

Para testar se suas fórmulas estão trabalhando, alterar o valor de P2 celular manualmente, digitando 1, 2, 3, 4 ou 5. Quando as fórmulas trabalhar, tudo o que resta a fazer é criar os gráficos usando a tabela de preparação.

Se as funções do Excel como ESCOLHER ou ÍNDICE são um pouco intimidante para você, não se preocupe. Há literalmente centenas de maneiras de usar várias combinações de controles de formulário e funções do Excel para conseguir relatórios interativos. Não existem regras definidas em quais controles de formulário ou funções do Excel que você precisa para usar em seu modelo.

Comece com melhorias básicas para o seu dashboard, utilizando controles e fórmulas que você está confortável. Depois, gradualmente, tentar introduzir alguns dos controles e funções mais complexas. Com um pouco de imaginação e criatividade, você pode fazer o básico e personalizar seus próprios dashboards dinâmicos.