Excel avançado (gráficos, tabela dinâmica, dashboards, power query e macros)

Prévia do material em texto

GRÁFICOS 
Para criar os gráficos com as informações que desejamos, devemos selecioná-las, e então, selecionar na guia 
Inserir o tipo de gráfico desejado. Outra forma possível é primeiro selecionar o gráfico desejado, e então, clicar sobre 
a caixa em branco gerada com o botão direito, e então Selecionar Dados. Para selecionar um conjunto de dados não 
contíguos, deve-se utilizar a tecla CTRL. Para adicionar mais dados, como uma coluna ou linha adicional, clicamos com 
botão direito sobre o gráfico e selecionamos a opção selecionar dados. 
Para títulos de gráficos, pode-se utilizar títulos presentes em células na planilha, através de referências. Dessa 
forma, alterar o título na célula, alterará também no gráfico. Para isso, deve-se selecionar o campo de título do gráfico, 
em seguir na barra de fórmulas, e então “=” e inserir a referência de célula desejada. 
É possível adicionar um filtro no seu banco de dados, e então criar um gráfico. Assim, ao filtrar seu banco de 
dados, filtrará automaticamente seu gráfico também, obtendo um grafico dinâmico. 
Gráficos Avançados: 
1. Para criar um gráfico do tipo termômetro, deve-se inserir um gráfico de coluna empilhadas, sem selecionar 
nenhum dado. Então, adicionando as séries manualmente, inserir os valores parciais. Formatando o gráfico (quadrado) 
pode-se selecionar formato cilíndrico. Para que o gráfico tenha um aspecto de vidro a ser preenchido, pode-se 
selecionar uma cor mais clara para a parte superior, e aumentar sua transparência, na barra lateral de formatação do 
gráfico. 
2. Para criar um gráfico do tipo velocímetro, novamente 
se cria primeiro o gráfico para então selecionar os dados. Nesse 
caso, o gráfico selecionado deve ser do tipo rosca. Para que o 
velocímetro possua divisões igualitárias de tamanho, ao longo 
do display, o valor de cada “informação” deve ser o mesmo. 
Nessa visualização, a real informação de interesse do gráfico 
será adicionada posteriormente, agora estamos apenas 
preparando o gráfico. Assim, os valores passados serão iguais 
(por exemplo: 1,1,1.. quantas divisões forem necessárias) e 
seus respectivos “nomes de categoria”, ou seja, o dado que 
deve estar selecionado pelo ponteiro. Para construir o ponteiro, 
é utilizado um gráfico de pizza com os reais dados do problema. 
Por exemplo, a empresa alcançou 80% da meta anual no ano X. 
Assim, os dados são: 100% (meta), 80% valor alcançado, 0% de 
largura atribuída ao ponteiro e 20% diferença. Para mostar o 
ponteiro, inicialmente se atribui uma largura diferente de zero, para melhor entendimento, então aumentamos a 
espessura da borda do ponteiro, e então setamos seu valor a 0%. Lembre-se de que a informação referente ao ponteiro 
deve estar entre os valor alcançado e a diferença, para que esse atue como um medidor. Após criado o novo gráfico 
para o ponteiro, selecione as demais fatias e para elas selecione as opções “Sem Preenchimento” e “Sem bordas” para 
que o gráfico fique transparente, apenas com o ponteiro á mostra. 
Por exemplo: 5% (o valor relativo ao ponteiro deve se localizar entre a informação do valor e da diferença, 
para que, no gráfico, esse se apresente entre os dois). Ao reduzir a porcentagem relativa ao ponteiro a zero, há um 
colapso da fatia da pizza, no entanto, o contorno da fatia permanece. Assim, aumentando a espessura da fatia, e 
ocultando os demais dados (além de alinhar o ângulo de inicio do ponteiro), temos um ponteiro dinâmico, de acordo 
com as porcentagens informada. Para um velocímetro com menor angulação (240º ou 180º por exemplo), basta 
selecionar fatias para se tornar ocultas ( 1/3 e ½ respectivamente). 
TABELA DINÂMICA 
Uma tabela dinâmica é uma opção simples e rápida para realizar operações sobre os dados de um determinado 
banco de dados. Pode ser uma alternativa mais rápida e confiável que manualmente digitar fórmulas simples como 
média, soma, e contagem. Para criar uma tabela dinâmica, basta ir na guia Inserir → Tabela Dinâmica e preencher os 
campos com as informações necessárias, como o nome da tabela, ou o intervalo de células. Após criada, você pode 
personalizá-la simplesmente arrastando os campos contidos na tabela de origem para os campos Coluna, Linha, Filtros 
ou Valores, que correspondem a disposição de tais dados na tabela a ser criada. 
Outra opção (mais dinâmica) ao uso do campo Filtros numa tabela dinâmica, é a inserção de uma Segmentação 
de Dados, que permite a seleção dos itens desejados. Para isso, basta selecionar alguma célula da tabela dinâmica e 
então Análise de tabela dinâmica → Inserir segmentação de dados. Para que uma mesma segmentação de dados possa 
controlar mais de uma tabela dinâmica, basta clicar sobre a segmentação de dados com o botão direito e então 
selecionar Conexões de relatório, e por fim selecionar as tabelas dinâmicas que devem ser controladas com base 
naquela segmentação de dados em questão. Através dos filtros da tabela dinâmica também, é possivel uma análise 
continuada sobre piores ou melhores produtos/vendedores, por exemplo, apenas utilizando filtro “10 primeiros 
filtros”. 
Obs.: Copiar uma segmentação de dados é permitido, e funciona exatamente de forma análoga. Pode-se 
também copiar a segmentação em outra planilha, e apagar a original. Caso após criar a tabela dinâmica e a 
segmentação, ao clicar neste ultimo o Excel desconfigure automaticamente a largura das colunas, para solucionar 
basta clicar com o botão direito sobre a tabela, e em Opções de Tabela dinâmica, desmarcar a caixa de seleção Ajustar 
automaticamente a largura das colunas ao atualizar. Por isso, é necessária a existência de uma tabela dinâmica para 
que possamos adicionar uma segmentação de dados. Caso contrário, devemos utilizar uma caixa de listagem. 
Em uma tabela dinâmica, pode-se adicionar mais de um campo em cada área especificada. Fazendo isso, 
obtem-se uma relação de hierarquia entre os campos, lembrando que um duplo clique numa célula de dados da tabela 
dinâmica permite a visualização de todos os dados (de forma detalhada) do banco de dados referente aquela célula. 
Para facilitar o entendimento, os títulos dos campos da tabela podem ser livremente modificados. 
Para uma análise de período, ou de uma semana, mês, trimestre, podemos utilizar uma base de dados com 
informações de data, apenas clicando sob uma célula com botão direito, e então agrupando no período desejado 
especificado (estático). Para uma dinamicidade maior do conteúdo, pode-se optar por uma linha do tempo, clicando 
na tabela dinâmica, e então Inserir Linha do tempo. 
Para criar uma porcentagem com uma tabela dinâmica, pode-se selecionar com o botão direito uma célula 
(em campo de soma de valores) e então selecionar a opção adequada em Mostrar valores como [...], ou então 
seleciona a guia Análise de tabela dinâmica → Configuracões de campo → Mostrar valores como [...]. 
Pode-se também criar um campo totalmente personalizado na tabela dinâmica, simplesmente clicando sobre 
a tabela Análise de tabela dinamica → Campos itens e conjuntos → Campo calculado e então criar a fórmula desejada, 
dessa forma o campo criado é disponibilizado automaticamente e de forma totalmente personalizada para o usuário. 
Obs.: ao criar uma tabela dinâmica em uma planilha que contem mais informações além da propria tabela, 
deve-se prefererir colocar os dados acima da tabela dinâmica, para evitar que a tabela dinâmica, ao atualizar seu 
conteudo, sobrescreva dados abaixo ou a direita. 
 Caso não seja de interesse os totais gerais (colunas/linhas), pode-se desativar essa opção. Basta clicar em uma 
célula qualquer da tabela dinâmica e então Design → Totais Gerais → Desabilitado para linhas e colunas. 
Pode-se criar uma tabela dinâmica tambem através do assistente de criação de tabela dinâmica, 
disponibilizado na barra de acesso rápido. E então adicionar o/os campos desejados. Essa ferramenta é particulamente 
util para adicionar dados de várias planilhas formatadasde mesmo forma, como por exemplo filiais de uma mesma 
empresa e seus respectivos faturamentos e despesas, pois o Excel entende o padão e o formato da seleção do 
intervalo, e o replica ao longo das diversas planilhas, bastando para isso, confirmar a inserção, selecionando o botão 
“adicionar.” 
DIFERENCIAL: Inserir controles → Permite uma interação dinâmica entre o usuário e a planilha. Para 
dimensionar tais ferramentas, pode-se manter pressionada a tecla ALT para ajustá-la ao tamanho da célula. 
Para adicionar uma funcionalidade ao botão, deve-se clicar com o botão direito sobre o mesmo, e então 
“formatar controle”, e então configurá-lo ao seu interesse. 
IMPORTANTE: criar uma validação de dados com base em opções de um dado banco de dados. É interessante 
utilizar a função ÚNICO para fazer uma listagem de todas as possíveis opções, e ao selecionar a opção validação 
de dados, ao invés de delimitar o intervalo com todas as possíveis opções, pode-se selecionar somente a 
primeira célula, e então adicionar o símbolo “#”, dessa forma, qualquer novo produto que for inserido abaixo 
da listagem atual da função ÚNICO, será também adicionado a validação de dados. 
Obs.: O estilo de formatação dos dados (de uma validação de dados, por exemplo, que aceita CPF) pode ser adequado 
ao formato ###.###.###-## que é o modelo adotado no Brasil. Para que o excel formate tal validação dessa forma, 
deve-se selecionar o campo pertencente ao CPF, e pressionar CTRL + 1, abrindo a janela “formatar células”, e então 
digitar o modo de exibição adequado. Ex: 0##”.”###”.”###”-“## (o zero no incio permite que o cpf começe com o 
numero 0). Em uma tabela dinâmica, utilizar “formato de número” ao inves de “formatar célula” pois o primeiro é 
aplicado a todas os campos, já o ultimo somente a celula celecionada, e é fixo. 
Ao formatar seu banco de dados como uma tabela antes de criar uma tabela dinâmica, ao ter um valor 
adicionado, ele automaticamente é adicionado a tabela, no entanto, para que tal dado seja levado em consideração 
na tabela dinâmica, esta deve ser atualizada manualmente. Análise de tabela dinâmica → Atualizar. 
 Pode-se utilizar a formatação condicional também para dados de tabelas dinâmicas. O processo é o mesmo, 
seleciona-se as células desejadas, Página Inicial → Formatação Condicional, e cria-se a regra desejada. No entanto, 
após a confirmação da criação da regra, um pequeno símbolo representante da tabela dinâmica aparece no canto 
inferior direito das células selecionadas. Através desse ícone conseguimos tornar a formatação condicional também 
dinâmica (ajustada a tabela), clicando sob o ícone e selecionando o opção Todas as células. 
Em uma tabela dinâmica, dê preferência a utilizar “formato de número” ao invés de “formatar célula” pois o 
primeiro é aplicado a todas os campos, já o último somente a célula celecionada, e é fixo. 
 As vezes, para evitar o excesso de dados em uma mesma planilha, os dados relacionados são divididos em 
duas ou mais planilhas. No entanto, para criar uma tabela dinâmica que utilize todas as informações que eram 
inicialmente relacionadas, precisamos informar ao Excel que tais dados são relacionados. Assim, tendo em mãos as 
diferentes planilhas com os dados, selecionamos a guia Dados → Relações e então criamos a relação desejada. 
Informando os dados de forma adequada (de cada planilha, sua coluna ao lado), tais dados estão instrísecamente 
relacionados. Por exemplo, ao invés de colocar todas as informações de um determinado pedido numa mesma tabela, 
pode-se compartimentarizar: uma planilha sobre o cliente (localização, telefone de contato, identificador), uma 
planilha sobre o pedido (produto, identificador do cliente, preço, data, status do pedido). Diferentemente, ao criar a 
tabela dinâmica, na opção Escolha os dados que quer Analisar deve-se selecionar Usar modelo de dados desta pasta 
de trabalho. As categorias agora estão segregadas de acordo com a localização na pasta de trabalho, e as planilas com 
relações apresentam nas miniaturas da Lista de campos um título escuro. 
 
DASHBOARD 
 Um Dashboard nada mais é que uma visualização atraente de um banco de informações, sejam elas dadas por 
tabelas dinâmicas ou fórmulas e campos feitos á mão. O passo a passo básico para se montar um Dashboard é 
enunciado a seguir. 
1. Delimitação da área de apresentação: para fazer a parte visual do dashboard, vai em Inserir > Ilustrações 
> Formas e cria uma retângulo para servir de base enquanto você adiciona as ferramentas do seu 
Dashboard. Durante a apresentação, somente os dados colocados dentro desse retângulo inicial será 
visualizado (Zoom fixo). 
Sua área total de trabalho então será mostrada selecionando Exibir → Mostrar → Retirar linhas de grade, 
Título e Barra de fórmula e então em Opções de exibição da faixa de opções e selecionar Ocultar 
automaticamente a faixa de opções. 
2. Definida assim a área de trabalho, podemos retornar a visualização padrão do excel, clicando em Mostrar 
guias e comando, em seguida Exibir → Mostrar linha de grade, Fórmulas, e Título, para poder trabalhar na 
área disponível. 
3. Para criar as caixas e formas desejadas, basta ir adiconando á área de trabalho (retângulo inicial). 
Ex.: Uma caixa de informações básicas pode ser criada com um retângulo de bordas arredondadas, e então 
com uma cópia do mesmo retângulo, reduzindo sua dimensão para ter apenas o tamanho do título 
desejado. Para tornar uma borda arredondada uma borda reta, pode-se utilizar um retângulo de bordas 
retas da mesma cor. Após ter finalizado a formatação visual da caixa, seleciona-se as componentes 
pressionando CTRL, e então seleciona a guia Formato da forma → Agrupar. Para duplicar uma forma, além 
do atalho copiar e colar, pode-se manter pressionado a tecla CTRL clicar sobre a forma, e arrastar para o 
lugar desejado. 
4. Após criar seus campos, o retângulo incial pode ser rearrumado para ficar mais agradável a visualização, 
pois seu papel como delimitador do espaço já foi utilizado. Para criar uma impressão dinâmica 3D das 
caixas, selecionamos utilizando o CTRL, e então, Formatos da forma → Efeitos da forma → Sombra. Para 
deixar as caixas alinhadas, novamente deve-se selecionar as caixas (CTRL), e então Formato da forma → 
alinhar → alinhar ao meio/acima, entre outros. 
5. Para fazer alterações na segmentação de dados como sumir títulos e alteração de coloração, clica com o 
botão direito sob a segmentação, e então em Configurações da segmentação de dados e então formatar 
de acordo com sua vontade, como preenchimento, fonte, e cor de seleção. 
6. Para adicionar ilustrações para tornar mais atraente suas informações, pode-se adicionar em Inserir → 
Ilustrações → Ícones. Para adicionar os dados em si, adiciona uma caixa de texto, e ao invés de escrever 
na caixa, utiliza a barra de fórmulas para referenciar a célula que contem a informação em outra planilha. 
7. Para adicionar os gráficos, pode ser criado um gráfico em branco no Dashboard (sem nenhuma célula 
selecionada), e ao clicar sobre o gráfico em branco com o botão direito, selecionar a opção Selecionar 
Dados e então selecionar os dados da tabela dinâmica (prefira utilizar as referências tradicionais como B5 
e não os comandos do excel como INFODATABELA[...]). 
8. Ao invés de uma segmentação de dados, também é possível adicionar uma caixa de listagem, através da 
guia Desenvolvedor → Inserir → Caixa de Listagem. Essa caixa de listagem possui um intervalo de entrada 
(valores a serem visualizados na lista) e um vínculo, que corresponde a posição selecionada da lista, 
começando em 1,2... Esse vínculo, pode retornar a opção selecionada da maneira que o usuário visualiza 
através do uso conjunto com a função ÍNDICE. 
9. Para resumir os dados, e não poluir a visualização, podemos reduzir casas decimais, arredondar valores 
ou então alterar a formatação dos número, através do atalho CTRL + 1. Para manter dados apenas com as 
casas de milhar, utilizamos a notação R$#. “mil”. Recomenda-se que a formatação da tabela dinâmica seja 
através desse atalho, ou clicando com o botão direito sob as céluas e selecionando Formatar Células, pois 
a formatação feita através da página inicial é uma formatação estática, ao trocarmos o campo, a 
formatação se mantém somente nas células selecionadas. 
 
POWER QUERY 
O Power Query é uma ferramenta que permite a organiza a importação de dados externos para a pasta de 
trabalho do Excel. As ferramenas do Power Query estão disponiveis na guia Dados, na subseção Obter e transformar 
Dados. Selecionando Obter dados → Arquivo → Pasta de trabalho e então selecionando o arquivo desejado, o Excel 
permite uma pré visualização das possíveis informações a serem importadas. Para editar antes de importar, deve-se 
Tranformar dados. 
Caso ao importar, o Power Query não identifique a primeira linha como como cabeçalho da tabela 
automaticamente, voçê pode fazer isso manualmente em Página Inicial→ Usar primeira linha como cabeçalho (na 
janela própria do Power Query). 
Após todas as modificações desejadas terem sido feitas, deve-se pressionar a opção “Fechar e Carregar” na 
guia Página Inicial. Assim como uma tabela dinâmica deve ser atualizada frequentemente para que esta sempre 
mostre os dados corretos, o mesmo acontece com planilhas importadas. A opção “fechar e carregar”, por padrão, 
produz uma tabela do excel. No entanto, podemos selecionar a opção “ fechar e carregar para...” nos permitindo criar 
uma tabela dinâmica, um gráfico dinâmico, ou apenas criar uma conexão entre os documentos (selecionar a opção 
“Adicionar estes dados ao modelo de dados”). 
Pode-se também utilizar o banco de dados proveniente de outra pasta de trabalho (ou outro tipo de 
documento compatível) para criar uma tabela dinâmica, para isto, deve-se utilizar Inserir tabela dinâmica → usar uma 
fonte de dados externo → escolher conexão e então selecionar o arquivo/planilha fonte, assim como a planilha destino 
da tabela dinâmica. 
Obs.: Para células em branco, o Power Query atribui valor “null”. É possível substituir todas as ocorrências 
dessa palavra nos campos da planilha, deve-se selecionar na guia Página Inicial → Substituir Valores. 
Na janela do Power Query, perçeba que as ferramentas disponibilizadas pelas guias “tranformar” e “adicionar 
coluna” são majoritariamente as mesmas, a diferença se dá porque ao utilizar um comando na guia “adicionar coluna” 
a informação desejada é adicionada a tabela como uma nova coluna, e não sobrescrevendo a informação contida 
anteriormente. 
Obs.: Durante a importação, pode-se ajustar a quantidade de linhas mantidas, ou colunas, se mantêm 𝑥 
primeiras ou 𝑦 últimas. 
O recurso do Power Query é compatível com arquivos de texto (*.txt), CSV (onde cada coluna é separada por 
vírgulas, e linhas são separadas por quebras de linha), textos da WEB, bancos de dados como SQL e Acess, entre outros. 
Para importar dados da web, é necessário um banco de dados estruturados num determinado site, para podermos 
então copiar o URL do site, e inserir na subseção do Power Query, Obter e transformar. Após gerada as opções 
disponíveis para importar, selecionar uma por uma permite uma pré-visualização da informação. Ao utilizar dados da 
Web, assim que tais dados forem atualizados, ao pressionar o botão Atualizar no Excel, tais dados serão sincronizados. 
Para consultar o painel com as conexões, basta selecionar a guia Dados → Consultas e Conexões, nesse painel, é 
possível atualizar manualmente as conexões feitas, uma por uma. 
Para conexões, como por exemplo, ao invés de todos os dados se apresentarem em uma única planilha, estes 
se apresentam separados em mais de uma planilha, e com um identificador único (ID) presente em mais de uma da 
planilhas, pode-se estabelecer tal conexão antes de adicionar os dados, através do power query. Isso é possível 
selecionando Página Inicial → Combinar → Mesclar consultas. Então, seleciona-se as colunas que serão relacionadas, 
e pressiona “OK”. É criado então novas colunas, unindo informações de ambos os arquivos. Para manter apenas as 
colunas relacionadas desejadas, deve-se expandir somente essas. 
Para um relatório periódico (mensal ou trimestral), feito a partir de relatorios menores (diários e semanais, 
por exemplo) pode-se utilizar o Excel para fazer a junção de tais dados. Para isso deve-se seguir o mesmo 
procedimento Dados → Obter e transformar dados → de CSV. Então, importar os arquivos desejados. Após adicionar 
os documentos como conexões, clicar em editar alguma dessas conexões, e então, na Página Inicial selecionar 
Combinar → Acrescentar consultas (ao invés de mesclar). 
 
MACROS 
 
Macros são uma ferramenta útil quando se deseja realizar uma mesma função repetidas vezes, de forma 
automatizada, com um simples clique num botão. Tal ferramenta é basicamente uma gravação, que ao ser iniciada, 
coleta todos os cliques dados, e todos os comandos utilizados, para que, após finalizada, a mesma operação possa ser 
repetida inúmeras vezes apenas executando essa macro. Dessa forma, para que a macro seja executada de forma mais 
suave possível, deve-se evitar cliques e comandos desnecessários durante a gravação de uma macro. O documento 
capaz de executar uma macro é de extensão .xlsm, e deve-se estar atento com documentos externos, pois macros de 
outras pessoas pode conter comandos nocivos. A macro pode ser vista como um conjunto de instruções gravadas a 
ser repetida. Para gravarmos uma macro, podemos utilizar o gravador de macro, ou programar os comandos 
diretamente no VBA, a linguagem de programação nativa do Excel. 
 A execução da macro realizará a mesma sequência de comandos realizado durante a gravação, como: após 
começar a gravar a macro, selecionar a célula B2 da planilha, implicará que sempre que executar a macro, esta 
selecionará a célula B2 da corrente planilha. Após criada a macro de interesse, caso esta deva ser executada diversas 
vezes, é interessante adicioná-la na barra de ferramentas de acesso rápido, podendo assim, executá-la com apenas 
um clique. Para pastas de trabalho que possuam duas ou mais macros, podemos adicionar um ícone exclusivo para 
tais macros, para podermos distinguí-las rapidamente. 
 A macro por padrão utiliza referências absolutas. Ou seja, ao gravar a macro você seleciona a célula B2 de uma 
planilha. Na execução ela também selecionará a mesma célula, da planilha onde a execução foi chamada. Para que a 
macro seja executada somente numa planilha, ao iniciar a gravação, o usuário deve clicar sobre a planilha (mesmo 
que esta já esteja ativa) e prosseguir com os comandos. 
 Macros são comandos gravados e escritos automaticamente na linguagem de programação nativa do Excel 
(VBA). Podemos atribuir tais comandos a um botão (guia Desenvolvedor) por exemplo, ou a uma forma/imagem/ícone 
(guia Inserir), para que este seja executado sempre que tais objetos forem selecionados. Assim, para automatizar um 
processo feito diversas vezes, podemos criar uma macro, e atribuí-la a uma foma ou botão, para facilitar a utilização 
por parte do usuário. 
É possível alterar a macro após ela ter sido gravada, através do editor VBA. Nele, é possível unir duas macros 
criadas de forma independentes uma da outra, simplesmente copiando um codigo, e o adicionando ao código 
primário.