Atalhos do Excel

Para os usuários do Excel, esta é uma boa planilha para tornar mais rápido o acesso às funcionalidades e comandos do Excel 2007/2010.


Clique aqui para baixar a planilha.


Até a próxima!

Usando formatação condicional para criação de Dashboards em formato de tornado

O site dos resultados do Censo 2010 do IBGE é uma excelente fonte de dados e ideias para construção de Gráficos, Indicadores e Dashboards. Navegando pelo site, um gráfico em especial chamou a minha atenção: é o que trata da distribuição da população brasileira por sexo, segundo os grupos de idade (clique aqui para visualizar o gráfico). Este gráfico apresenta os dados demográficos do país de maneira bem simples, possibilitando a análise da distribuição da população brasileira atual.

Pegando carona nos dados do IBGE, vou mostrar neste post como replicar as mesmas informações, porém, ao invés de utilizar gráficos, vou mostrar como montar o Dashboard utilizando os recursos de formatação condicional.

Em primeiro lugar, entre no site e copie os dados para uma planilha do Excel:

Em seguida, selecione todos os dados da distribuição apenas dos homens. Clique em Página Inicial à Formatação Condicional à Nova Regra:

Mantenha a opção Formatar todas as células com base em seus respectivos valores. Na seção Edite a Descrição da Regra, escolha o Estilo de Formatação Barra de Dados e marque a opção Mostrar Barra Somente:

Na seção Aparência da Barra, escolha uma cor de sua preferência. Além disso, em Direção da Barra, modifique a opção para Da Direita para a Esquerda:

Clique OK:

Agora você pode repetir o mesmo processo de criação de uma nova regra de formatação condicional com os dados das mulheres. Modifique apenas a cor da barra para uma cor de sua preferência (neste caso, você não precisará modificar a Direção da Barra):

Vale a pena aumentar um pouco a largura das colunas e remover as linhas de grade da planilha:

Se quiser, você também pode fazer o download do post em formato PDF, clicando aqui.

Até a próxima.

Funções do Excel em Inglês

Se você já utilizou o Excel em Inglês alguma vez, deve ter percebido que a tradução das funções nem sempre é uma coisa fácil de descobrir. Isso acontece porque algumas funções são escritas com siglas, e não há uma tradução imediata para elas – caso do PROCV, que no inglês vira VLOOKUP.

Por isso, é bom sempre ter a mão um tradutor de funções, para uma consulta rápida nos casos que você não lembra – ou não sabe – a tradução de uma determinada função.

Clique aqui para baixar o arquivo de tradução das fórmulas do Excel.

Até a próxima

Controles de formulário em Dashboards: caixa de combinação

Pegando carona no post sobre a utilização de controles de formulários para criar Dashboards, um tipo de controle de formulário muito interessante que pode ser utilizado na construção de Dashboards dinâmicos é a Caixa de Combinação. Ela pode ser utilizada para selecionar uma determinada informação dentre uma lista de valores, possibilitando ao usuário a análise individual de suas informações.

Vejamos o exemplo abaixo:

Esta planilha apresenta a quantidade mensal de voos em atraso por companhia aérea no ano de 2010. O objetivo será criar uma visualização gráfica das informações, comparando os dados de todas as companhias em um gráfico de linhas.

Para iniciar o trabalho, selecione os dados das companhias de Janeiro a Dezembro (sem incluir a linha Total Geral) e insira um gráfico de linha:

Para melhorar a apresentação visual das informações, efetue os seguintes passos:

1. Insira o título “Atrasos por Companhia – 2010” no gráfico;
2. Selecione a linha de cada uma das companhias aéreas e aplique uma cor sólida, de preferência com um tom cinza. Esse tom deverá ser aplicado às linhas de todas as companhias;
3. Remova a legenda do gráfico;
4. Se desejar, aplique uma cor clara à área de plotagem do gráfico;
5. Ajuste o tamanho e posicione o gráfico da maneira que desejar.

A esta altura, você deve estar se perguntando: “mas como as pessoas conseguirão comparar os dados se todas as linhas do gráfico possuem a mesma cor e não há legenda para diferenciá-las?”. É aí que entra a caixa de combinação. Conforme dito no início do tópico, a caixa de combinação pode ser utilizada para selecionar uma determinada informação dentre uma lista de valores.

Inserindo e configurando a caixa de combinação

Vamos agora começar a trabalhar com o controle de formulário Caixa de combinação. Para utilizá-lo, clique na guia Desenvolvedor e, na seção Controles, clique no botão Inserir > Caixa de Combinação:

Posicione o mouse onde deseja que a caixa de combinação seja inserida e efetue um clique:

Pode ser que o formulário fique maior do que o esperado. No entanto, você pode utilizar as alças nos cantos do botão para ajustar o seu tamanho:

Agora, você precisa efetuar as configurações de funcionalidades da caixa de combinação. Clique com o botão direito do mouse na caixa de combinação e escolha Formatar Controle:

As opções de configuração do botão são as seguintes:

• Intervalo de entrada: neste local, você deve fornecer a lista de itens que o botão irá exibir. Como neste trabalho deseja-se comparar os atrasos das companhias aéreas, selecione o intervalor $C$7:$C$12, que é a lista com o nome das empresas;
• Vínculo de célula: conforme vimos na criação da caixa de seleção, toda vez que um botão de formulário é inserido na planilha, ele precisa ser vinculado a uma célula. Relembrando, esse vínculo terá a função de garantir que, sempre que o botão for utilizado, as células que dele são dependentes também recebam esta atualização. A título de exemplo, escolha a célula $Q$1 como vínculo para a caixa de combinação;
• Linhas suspensas: este item é responsável por determinar a quantidade itens que serão exibidos quando o usuário clicar no botão. Por exemplo, você pode ter uma lista de 10 valores, mas deseja que apenas 5 sejam exibidos quando o usuário clicar no botão. Para visualizar os outros 5 valores restantes, o usuário poderá utilizar a barra de rolagem vertical disponibilizada no botão;
• Sombreamento 3D: marque esta caixa caso deseje aplicar um formato 3D para o botão.

Ao final da configuração, clique OK. Em seguida, clique no botão para visualizar as companhias aéreas:

Ao escolher uma determinada companhia (Web Air, por exemplo), a célula Q1 irá exibir o número de classificação desta companhia em relação às demais, ou seja, qual posição ela ocupa (partindo de cima para baixo) na lista selecionada na caixa de combinação:

Veja que o número inserido na célula Q1 foi o número 4, porque a companhia Web Air é a quarta linha das companhias aéreas selecionadas na caixa de combinação.

Configuração do gráfico

Uma vez que a caixa de combinação foi criada e configurada, é necessário passar à segunda parte, para configurar a maneira como os novos dados serão interpretados pelo gráfico. A ideia é que uma nova linha colorida seja acrescida ao gráfico, de modo a destacar a companhia selecionada na caixa de combinação. Para isso, iremos utilizar uma nova fórmula, que será criada a partir da célula C32 (clique nesta célula).

A função ÍNDICE será utilizada para obtermos os valores desejados. De modo geral, a função índice retorna um valor posicionado na intersecção de uma linha e coluna específica, em um intervalo de dados especificado. Caso você nunca tenha utilizado a função índice, sugiro que utilize a Ajuda do Excel para obter maiores informações sobe esta função.

Posicionado na célula C32, clique em Inserir Função e escolha e função Índice. Entre as duas opções de função, escolha a primeira, que contém os argumentos matriz;núm_linha;núm_coluna:

• No argumento Matriz, você deve especificar a tabela de referência que contém os seus dados. Selecione a tabela com todas as informações sobre as companhias, presente no intervalo $C$7:$O$12;
• Em Núm_linha, você deve determinar qual a linha da planilha que será utilizada como referência para o cruzamento das informações. Como esta linha depende da companhia aérea escolhida na caixa de combinação, selecione a célula que funciona como vínculo desta célula –$Q$1;
• Em Núm_coluna é necessário definir qual coluna será utilizada como referência para o cruzamento das informações, assim como foi feito no argumento num_linha. Como desejamos, neste momento, trazer a informação do nome da companhia, digite o número 1 (pois dentro da matriz selecionada, a primeira coluna selecionada é a coluna que contém o nome da companhia).

Perceba que o Excel já informa que o resultado da fórmula trará Web Air. Clique OK para finalizar. Em seguida, você pode modificar a caixa de combinação para outra companhia, de modo a validar o funcionamento da função índice:

Para finalizar o passo de configuração da função Índice, aplique a mesma função nas próximas 12 colunas a direita (da coluna D até a coluna O), tomando o cuidado de mudar a referência da posição de cada coluna para que reflitam o resultado do mês esperado:

Agora, falta apenas introduzir os novos dados no gráfico, para que fiquem destacados. Selecione os dados criados com a função Índice (da célula C32 até a célula O32) e pressione CTRL C (copiar). Em seguida, clique na área de plotagem do gráfico e clique CTRL V (colar). Uma nova linha será criada no gráfico, num tom azul suave:

Selecione a nova linha e aplique um tom mais escuro e destacado, como por exemplo vermelho. Se achar interessante, você pode também aumentar a espessura da linha, na seção Design:

Para melhorar a apresentação dos dados, pinte com a cor branca as células da linha 32 e da coluna Q, pois as mesmas são utilizadas apenas como referência, não havendo necessidade de estarem visíveis aos usuários. Em seguida, você poderá dar os toques pessoais que desejar e modificar a caixa de combinação para destacar as companhias conforme sua preferência:

Para fazer o download do arquivo digital do post, clique aqui.

Para fazer o download da planilha utilizada como referência para este post, clique aqui.

Até a próxima!

Minigráficos

Conforme visto no post sobre Indicadores e Formatação Condicional, é bastante comum aos usuários do Microsoft Excel imaginar a utilização dos gráficos disponibilizados pelo software como opção primária (e às vezes até única) para a construção de Dashboards. Entretanto, conforme vimos, esta não é a única alternativa disponível para criar um painel de indicadores dinâmico e com excelente aspecto visual. Além dos Indicadores e Formatação Condicional já citados, também podemos utilizar (e bem) os Minigráficos.

Disponível para utilização apenas a partir da versão 2010 do Microsoft Excel, um minigráfico é um pequeno gráfico em uma célula de planilha que fornece uma representação visual de dados. Você pode utilizar minigráficos para exibir tendências em uma série de valores, como evolução de vendas ao longo de um determinado período, ciclos econômicos e etc. Por não serem objetos, os minigráficos representam uma boa alternativa quando se deseja exibir de maneira rápida e simples a evolução de dados ao longo do tempo, pois eles são inseridos junto das células da planilha.

Observe a planilha abaixo. Ela apresenta a relação de despesas de uma determinada empresa no período de um ano, comparando, ao final, a média das despesas anuais com uma meta pré-estabelecida:

Neste caso específico, os minigráficos poderiam ajudar os usuários a entender de maneira mais intuitiva a evolução das despesas, agregando um aspecto visual e funcional à planilha.

Para inserir um minigráfico, clique na célula R7 (célula que especifica a Linha do tempo da despesa Salário). Em seguida, clique em Inserir > Minigráficos. Para a situação atual, escolha entre minigráficos de Linha ou Coluna.

Na caixa de diálogo Criar Minigráficos, especifique o intervalo de dados que irá alimentar o minigráfico (C7:N7). O resultado se dará conforme exemplo abaixo:

Como os minigráficos são objetos que pertencem às células do Excel, você pode utilizar o comando de autopreenchimento para aplicar seu resultado para as demais células da planilha:

Você também pode aplicar os minigráficos em formato de coluna, caso deseje comparar qual dos dois melhor se encaixa na sua necessidade:

Particularmente, achei que o minigráfico do tipo linha ficou mais bacana nesta planilha.

Personalizando os minigráficos

Depois de criar seus minigráficos, você poderá controlar quais pontos de valor serão exibidos (alto, baixo, primeiro, último ou qualquer valor negativo). Você também poderá aplicar estilos de uma galeria ou definir opções de formatação individuais, além de definir opções no eixo vertical e controlar como os valores vazio ou zero serão mostrados no minigráfico.

Para isso, selecione um ou mais minigráficos que deseja alterar e utilize as Ferramentas de Minigráfico, clicando na guia Design:

Minigráfico de ganhos e perdas

O terceiro modelo de minigráficos oferecido pelo Excel é o de ganhos e perdas. Este modelo exibe, dentre os valores selecionados, apenas os anos nos quais se obteve ganho ou perda.

Este tipo de minigráfico poderia ser utilizado, por exemplo, para comparar o desempenho de um grupo de ações ao longo do tempo, como na ilustração abaixo:

Se combinados com um modelo de formatação condiciona, como por exemplo a escala de cores, os minigráficos poderiam apresentar um resultado visual interessante:

Se desejar, você pode fazer o download do arquivo em formato PDF clicando aqui.

Até a próxima!

Importando dados de arquivos texto

Olá pessoal,

No post anterior, iniciei a discussão sobre como a conexão com dados externos pode ser uma forte aliada na construção de Dashboards e indicadores no Excel. Lá, falamos sobre a importação de dados do Access. Se você quiser visualizar o post, clique aqui.

Outro tipo bastante comum de fonte de dados externos que pode servir como referência para a criação de vínculos no Microsoft Excel são dados oriundos de arquivos de texto (txt). Há no mercado diversos sistemas corporativos que, por não possuírem conexão direta com o Excel, exportam os dados para este tipo de arquivo, possibilitando que o usuário o utilize para manipular suas informações no Microsoft Excel.

Neste tópico, iremos tratar do estabelecimento de conexões de dados entre arquivos de texto e o Microsoft Excel.

Tipos de dados

Ao importar dados de um arquivo de texto, você deve considerar como os dados estão organizados. Os arquivos de texto podem estar organizados em dois tipos distintos: Delimitado ou Largura fixa. Abaixo uma breve descrição das duas opções:

• Delimitado: neste tipo de arquivo, caracteres como vírgulas ou tabulações separam cada campo. Você deve utilizar esta opção quando o arquivo de origem possuir um campo comum que faça a separação das informações de cada coluna;
• Largura fixa: neste tipo de arquivo, os campos são alinhados em colunas com espaços entre cada campo. Você deve utilizar esta opção quando o arquivo de origem não possuir um campo comum de separação das colunas. Normalmente, arquivos de largura fixa são aqueles que possuem campos com valores exatamente iguais, como números de CPF, CNPJ e etc.

Importação: tipo delimitado

Imagine que sua empresa possua um sistema de gerenciamento de chamados para a equipe de suporte técnico. Este sistema armazena os dados do chamado e acompanha seu ciclo de vida, desde sua abertura até o fechamento. Todavia, o sistema não possui conexão direta com o Excel, e a única maneira de se visualizar os dados em outro software é efetuando sua exportação para um arquivo de texto. Sua missão será estabelecer a conexão entre o Excel e este arquivo de texto, para que assim possa construir os relatórios gerenciais.

Se você abrir o arquivo de texto, é assim que visualizará os dados:

Perceba que a primeira linha do arquivo é o cabeçalho dos dados, apontando qual informação está presente em cada coluna. Notem também que a separação de uma coluna para outra se dá pelo caractere ponto e vírgula.

Para iniciar a importação dos dados, no Microsoft Excel, clique na guia Dados. Você irá visualizar, a esquerda da Faixa de Opções, a seção Obter Dados Externos. Clique na opção De Texto. Em seguida, aponte o local onde o arquivo está salvo e clique em Importar.

O Microsoft Excel irá abrir o Assistente de importação, que irá ajudá-lo no processo de obtenção dos dados externos. Na primeira etapa, você deve definir o tipo dos dados originais. Como sabemos previamente que os dados são separados pelo porto e vírgula, marque a opção Delimitado:

Clique em Avançar. Na segunda etapa, você deve definir qual(is) delimitador(es) será(ão) utilizado(s). A parte esquerda da caixa de diálogo permite que você especifique o delimitador, podendo escolher entre os delimitadores oferecidos pelo Excel ou digitando o seu próprio valor:

Selecione o delimitador ponto e vírgula e veja como seus dados são afetados na janela Visualização dos dados:

Clique em Avançar. Na etapa número três, você poderá especificar a formatação dos dados de cada coluna. Você também poderá optar por não importar colunas que não sejam necessárias. A título de exemplo, vá para a última coluna do arquivo – Status Final. Clique na coluna e marque a opção Não importar coluna (ignorar). Em seguida, clique em concluir:

Ao finalizar o processo de importação dos dados, você deverá especificar a célula onde deseja colocar os dados. Além disso, você também pode efetuar as definições de gerenciamento para atualização dos dados, clicando no botão Propriedades:

Após definir as propriedades do intervalo de dados externos, clique OK e OK novamente:

De posse dos dados, você poderá realizar as ações necessárias, como aplicação de formatação e geração de tabelas dinâmicas e relatórios visuais:

Importação: tipo largura fixa

A segunda opção existente para a importação de dados do tipo texto é a de largura fixa. Normalmente, os dados armazenados neste sistema possuem um tamanho padrão, onde o usuário é responsável por determinar a quebra será realizada.

Neste exemplo específico, temos um arquivo com os registros das vendas de uma determinada empresa ao longo de um período. Os dados armazenados no arquivo de texto estão configurados da seguinte maneira:

• Número do CNPJ do cliente
• Mês de referência
• Código do vendedor
• Código do estado da loja
• Valor da venda

Para iniciar a importação dos dados, repita os mesmos passos da importação anterior. Clique na guia Dados > Obter Dados Externos > De Texto. Em seguida, aponte o local onde o arquivo está salvo e clique em Importar.

A diferença para esta importação é que agora você deve selecionar o tipo de dados Largura Fixa:

Clique Avançar. Na segunda etapa de importação dos dados, você pode utilizar as setas para indicar onde o Excel deve realizar a quebra das colunas. Como o primeiro campo dos dados é o campo CNPJ, posicione a quebra da primeira coluna logo após o último dígito do CNPJ, antes do item Mês de referência (são 14 campos da esquerda para a direita):

Repita a mesma ação para as posições Mês de referência, Código do vendedor, Código do estado da loja e Valor da venda:

Clique em Avançar. Na etapa número três você poderá definir o formato dos dados conforme sua necessidade, como também poderá ignorar a importação de algumas colunas, caso desejar. Clique em Avançar e em seguida em Concluir:

Você agora pode formatar a sua planilha e gerar os Dashboards e indicadores de que necessita:

Se desejar, faça o download do arquivo digital deste post, em formato PDF, clicando aqui.


Até a próxima!

Importando dados do Access

Obtendo dados externos

Quando se pretende utilizar o Microsoft Excel para a criação de Dashboards dinâmicos, um dos pontos mais importantes é o estabelecimento e a manutenção da conexão com os dados que irão alimentar a planilha. O estabelecimento de conexões com dados externos (oriundos de um site na web ou de um banco de dados, por exemplo) traz um ganho real de produtividade para os usuários, pois permite que os dados que irão alimentar as planilhas sejam atualizados automaticamente sempre que a fonte (o lugar onde os dados originais estão armazenados) sofra uma atualização, como a inclusão, edição ou exclusão de dados.

O Microsoft Excel possui um excelente conjunto de recursos para o estabelecimento de conexões com diferentes fontes de dados, facilitando a obtenção das informações e a construção de gráficos e elementos visuais que compõem os Dashboards. Este será o tópico de estudo deste capítulo.

Obtendo dados do Access

Imagine que você possui um arquivo que contém o histórico dos pagamentos e transações financeiras da sua empresa ao longo de um determinado período. Você precisa gerar um relatório com base nesses dados, importando as informações do Access para o Excel e mantendo-as atualizadas sempre que modificações forem realizadas na base primária.

Um dos tipos de conexão externa mais comum é a importação de dados de um banco de dados em Access. Por serem ferramentas que compõem o pacote Office da Microsoft, a comunicação entre os dois softwares (Access e Excel) é simples e amigável, tornando o processo de importação de dados mais fácil para os usuários.

Para iniciar a importação dos dados, no Microsoft Excel, clique na guia Dados. Você irá visualizar, a esquerda da Faixa de Opções, a seção Obter Dados Externos. Clique na opção Do Access:

Ao clicar no botão para obter dados do Access, você deverá informar o local onde o arquivo de banco de dados está salvo. Aponte o local do arquivo e clique em Abrir:

Você irá visualizar a janela que determina a fonte de dados que está sendo utilizada como referência para a criação da conexão. Se o arquivo de banco de dados possuir restrições de acesso, você terá de inserir a credencial que possui a permissão para estabelecer a conexão. Do contrário, mantenha as informações conforme sugeridas pelo Excel e clique OK:

Se o arquivo do Access que estiver sendo utilizado como referência para importação dos dados possuir mais do que uma tabela ou consulta, você deverá apontar em qual dos itens as informações que deseja importar está armazenada:

Neste exemplo, utilizarei a tabela Ocorrencia – Jan a Jul 2011. Selecione a consulta e clique OK. Em seguida, você poderá definir o modo como os dados serão exibidos na planilha:

• Tabela: os dados serão copiados à planilha no formato original do Access, com os registros distribuídos em linhas. Selecione esta opção caso seja necessário manipular os dados antes de construir os relatórios, como por exemplo a inclusão/exclusão de linhas, colunas ou a criação de fórmulas;
• Relatório de Tabela Dinâmica: os dados serão transferidos à planilha diretamente no formato de tabela dinâmica. Selecione esta opção caso não haja necessidade de efetuar edições nos dados originais;
• Relatório de Tabela e Gráfico Dinâmico: este item possui as mesmas características do item anterior, com a diferença de que um gráfico dinâmico também é gerado na construção da tabela dinâmica.

Selecione a opção Relatório de Tabela Dinâmica e clique OK:

Agora que você possui todos os dados necessários à criação do relatório, você só precisa construir a tabela dinâmica para exibir os resultados:

Gerenciando a conexão de dados

Após estabelecer a conexão com os dados externos, você pode tomar algumas ações de gerenciamento para melhorar o vínculo de comunicação entre o Access e o Excel. No Microsoft Excel, clique em Dados > Conexões > Propriedades.

Na guia Uso você poderá marcar a opção Atualizar dados ao abrir o arquivo. Isso irá determinar que, sempre que o Excel for aberto, a comunicação seja estabelecida e os dados atualizados sejam carregados no arquivo.

Se a entrada de informações no Microsoft Access for diária, você também poderá marcar as seguintes opções:

• Habilitar a atualização em segundo plano: selecione esta opção para que você possa continuar trabalhando com o Excel enquanto os dados são atualizados;
• Atualizar a cada...: a opção anterior só deve ser selecionada caso esta opção também seja definida. Aqui você poderá definir o intervalo de tempo para a atualização da sua planilha.

Se desejar, você pode clicar na guia Definição para visualizar a configuração estabelecida para a conexão dos dados. Esta é uma opção interessante se você desejar saber a cadeia de conexão construída, pois assim poderá utilizá-la futuramente para a construção de um código VBA. Além disso, caso a origem dados seja modificada, você poderá especificar o novo local onde o arquivo está armazenado.

Se desejar, faça o download deste post em formato digital, clicando aqui.


Até a próxima!