Excel: técnicas avançadas de tabela dinâmica para criar um painel coeso

1. Apresentação: Por mais de 15 anos, tenho sido um usuário avançado do Excel e, ao longo dos anos, aprendi muitas dicas e truques sobre como transformar minhas tabelas dinâmicas e gráficos dinâmicos em um painel agradável e coeso. Oi lá. Meu nome é Al e atualmente sou arquiteto de soluções na Coda, mas também sou um usuário ávido do Excel há muitos anos. Comecei a usar o Excel em uma equipe financeira interna Google há mais de dez anos. E estou muito empolgado dar essa aula porque quero mostrar a vocês todas as minhas tabelas dinâmicas avançadas e truques de gráfico dinâmico que uso para criar um painel coeso e interativo. Depois dessa aula, você parecerá um herói em sua equipe por poder criar um painel e também automatizá-lo com todas as dicas e truques que vou mostrar nesta aula. Esta aula é sobre como usar recursos avançados em tabelas dinâmicas para criar um painel que se destina a ser platelets. Você trabalhará em um painel que poderá eventualmente mostrar à sua equipe. Será possível interagir com ele porque é muito dinâmico e todos os dados serão atualizados à medida que eles estiverem jogando com ele. Um painel tem tudo visualizações de dados e você aprenderá a criar várias visualizações diferentes incluindo gráficos, minigráficos, barras de dados, com o objetivo de ser capaz de se comunicar conte uma história sobre seus dados para as partes interessadas internas, para seus colegas de equipe e para seus colegas. Outro grande argumento que eu quero que você vá embora é a automação. Quero que você pense todas as tarefas manuais que você precisa fazer para atualizar suas tabelas dinâmicas e painéis atuais. E nesta aula, você pode aprender algumas dicas e truques para automatizar todas essas tarefas manuais. Eu não sei sobre você, mas uma das melhores sensações que tenho ao usar o Excel como Nike é clicar em um botão ou digitar algo na célula e tudo é atualizado de uma só vez. No projeto de classe, você receberá um conjunto de dados que precisará criar cortadores para tabelas dinâmicas, gráficos dinâmicos e painel. E o objetivo é poder atualizá-lo uma só vez, como falamos com a automação. E, por favor, publique perguntas e projetos posteriores no fórum da turma. E ficarei feliz em fornecer feedback e responder a quaisquer perguntas que você tenha. 2. Criando vários relatórios dinâmicos: Esta lição ajuda você a criar relatórios individuais com base um filtro em sua tabela dinâmica. E não precisa de macros sofisticadas ou fórmulas sofisticadas. Este é um atalho e uma dica muito úteis para criar rapidamente um monte de relatórios que você não precise gastar tempo copiando e colando e clicando em vários menus suspensos para criá-los relatórios para você intimidados. Antes de entrarmos na lição um, revisarei rapidamente os dados na guia Dados da haste na pasta de trabalho. Como toda a tabela dinâmica que estamos construindo nesta classe, estamos usando nesta classe são todos expulsos desse conjunto de dados principal. E é um conjunto de dados que consiste em clientes que pagam pelo serviço de Internet e pelo serviço telefônico de uma empresa de telecomunicações. E só passa pelo ID do cliente. Há cerca de 6 mil linhas de dados, e eu vou até aqui à direita. E há uma pequena lenda aqui em amarelo que diz o que cada coluna significa. Mas, essencialmente, é cada vez maior em clientes que pagam pela Internet ou pelo serviço telefônico com essa empresa de telecomunicações. Voltando à lição um, essa tabela dinâmica bem aqui, é bem básica. Tem o sexo do cliente nas filas e também o método de pagamento, basicamente a forma como eles pagam pelo serviço de Internet ou serviço telefônico ou o que quer que seja. E o total é a soma das cobranças totais, que é esse campo aqui. Portanto, o valor total que foi pago a essa empresa de telecomunicações grande para esses diferentes métodos de pagamento. Agora, com as tabelas dinâmicas, você sabe que pode arrastar qualquer uma dessas colunas ou campos para a seção de filtros. Então, vou arrastar o campo do contrato a guia de filtros. E, como você sabe, tabelas dinâmicas, depois de fazer isso, você pode clicar neste menu suspenso e selecionar, talvez eu só queira olhar para os clientes que pagam mês a mês. E você pode ver a receita total que foi impulsionada por contratos mensais ou incluindo um ano. Vou deixar todos esses três marcados. E o objetivo desta lição é mostrar como você pode criar rapidamente relatórios de tabela dinâmica com base no que você tem neste país, nesta seção de filtros da tabela dinâmica. Então, no Mac, você pode clicar em Análise de Tabela Dinâmica e, ao lado do botão Opções, há um pequeno menu suspenso aqui e clicar em Opções de Tabela Dinâmica. Clique em Mostrar relatório, páginas de filtro. E você verá por padrão que diz Mostrar todas as páginas do contrato porque já tínhamos arrastado campo do contrato para a seção de filtros, eu pressionei OK. E o Excel cria lindamente essas três planilhas que filtram automaticamente para o contrato específico. Ele apenas percorre todas as opções e apenas cria planilhas dinâmicas, cria planilhas com tabelas dinâmicas baseadas em cada opção no menu suspenso. Portanto, lembre-se de como, na lista suspensa de contratos , temos mês a mês, um ano a ano. Ele criou automaticamente três planilhas com base nessas três opções no campo do contrato. Se você quiser criar rapidamente esses relatórios com tabelas dinâmicas, em vez de copiar e colar tabelas dinâmicas ou menu selecionando cada opção. Gosto de usar essa opção para criar relatórios rapidamente quando sei que queria ter várias tabelas dinâmicas para várias opções diferentes usando algum campo específico no meu conjunto de dados. Portanto, essas páginas da web são uma maneira rápida de ver os dados. 3. Criando barras de dados dinâmicas: Às vezes, você deseja criar um gráfico de barras para suas tabelas dinâmicas para mostrar a distribuição de valores em seu conjunto de dados. As barras de dados também são uma maneira muito rápida de compartilhar com seus colegas de equipe tendências rápidas sobre os dados em seu conjunto de dados. Um dos recursos mais subutilizados com tabelas dinâmicas, na minha opinião, eles são as barras de dados dinâmicas porque permitem que você mostre rapidamente tendências em seus dados para seus colegas de equipe sem que eles precisem construam seus próprios gráficos ou gráficos. E assim, com esta tabela dinâmica, vamos construir algumas barras de dados básicas na coluna de encargos totais saint PivotTable que usamos da lição um. Portanto, o primeiro passo é selecionar os dados e a coluna total quando for como todos esses números aqui, apenas esses valores em dólares. Em seguida, vá para Início Barras de dados de formatação condicional. E eu vou selecionar as barras de dados de cor verde por enquanto, que é esta aqui embaixo. E observe como as barras são redimensionadas automaticamente com base em quanto, exemplo, o tamanho do número e a coluna total. E eu definitivamente experimentaria brincar com as diferentes barras de cores e variações aqui. Por exemplo, se você for aqui, barras de dados. O campo gradiente também é um pequeno toque agradável porque você percebe como ele fica um pouco mais claro perto do final, eu recebo apenas essas pequenas coisas que fazem seus gráficos parecerem mais com, um um pouco mais agradável esteticamente. Mas o legal é que esse não é um gráfico separado. Isso é feito dentro da própria tabela dinâmica. E eu também clicaria em ir para Barras de Dados e depois em mais regras aqui. Essa opção. E aqui estão diferentes maneiras pelas quais você pode realmente personalizar a aparência de suas barras de dados. Uma coisa que você pode fazer aqui é marcar a caixa mostrar somente barras de dados. E vamos ver bem aqui. É isso. Bem aqui. Está meio escondido lá. Acertar, ok? E o que eu gosto nessa visualização é que, em vez de as pessoas estarem focadas especificamente nos valores em dólares, esse foco está apenas no próprio gráfico de barras. E assim, as pessoas que podem ver rapidamente que o cheque Mel foi a opção de método de pagamento menos usada para esse conjunto de dados. Então, eu vou desfazer isso por enquanto, só para trazer de volta os números que eu brincaria com as outras opções aqui para encontrar aquele que combina com seu gosto por diferentes barras de dados. Eu também apontaria para as escalas de cores desejadas, se você não quiser ir com uma cor sólida, você pode, é claro, personalizar as cores e gradientes aqui para uma cor sólida. Mas se você quiser passar rapidamente do verde para o vermelho, que é uma opção bastante comum para mostrar como bom para ruim ou alto para baixo, é clicar em escalas de cores. E eu costumo usar a segunda opção aqui. Flips, eu tenho que remover toda a forma condicional. M vai desfazer tudo aqui. Vá para formatação condicional, escalas de cores. Segunda opção. E eu sinto muito por isso. Eu deveria escolher a primeira opção. Somos verdes é bom, vermelho é ruim. Nesse caso, mostra que o cheque eletrônico é o maior número de todos os diferentes métodos de pagamento. E então o cheque Mel fica vermelho, indicando que ele tem a menor quantidade que realmente derreterá. Verifique aqui, na verdade, o menor número aqui. Mas você pode ver como você pode brincar com essas diferentes opções com escalas de cores e barras de dados. Pessoalmente, gosto mais de usar as barras de dados porque, novamente, quero transmitir rapidamente quanto dinheiro estamos recebendo com esses diferentes métodos de pagamento. E então normalmente eu vou usar um preenchimento gradiente porque ele apenas dá toque extra agradável que faz com que pareça um pouco mais profissional. Gerenciar regras, barra de dados é mais regras. Eu também me livraria dos números que eu realmente quero que as pessoas se concentrem especificamente nas próprias barras. E por um valor positivo, talvez eu possa voltar para este belo verde aqui. E vamos ver. Eu não acho que implique que o gradiente aqui, vamos bater bem. E um gráfico de barras tão simples, como um gráfico de barras simples que mostra qual é esse valor. Mas o importante é que isso aconteça diretamente na própria tabela dinâmica sem que você precise criar um gráfico separado. 4. Usando o filtro da linha do tempo: O fatiador de linha de tempo é um cortador exclusivo para tabelas dinâmicas porque permite filtrar seus dados com base em um período de tempo específico definido no fatiador. Às vezes, porém, você quer ver esse caso específico que está filtrando sua cabeça no cortador de linha do tempo. Então, este truque da tabela dinâmica ou oferta para mostrar aos seus colegas de equipe as datas específicas que você está definindo para o seu cortador de linha do tempo. Temos nossa tabela dinâmica aqui e ela é dividida por gênero novamente, mas desta vez você verá meses nas linhas também. E a maneira como você pode obter esses meses é quando nosso campo de data realmente cada data individual para quando essas transações foram feitas. Arrastando isso para as linhas. Você verá que quando eu expandir janeiro, verei todas as datas aqui para janeiro e quanto dinheiro foi ganho. Mas quando você arrasta um campo de data para uma tabela dinâmica no Excel, você pode resumir automaticamente os dados por mês ou por data específica, dependendo do que você está procurando no momento. Só estou mostrando os dados por mês. É exibido apenas de janeiro a junho para meu conjunto de dados. Agora, quando você joga com datas na tabela dinâmica e às vezes quer ser capaz de filtrar rapidamente entre datas específicas. E há um fatiador nas tabelas dinâmicas no Excel, que permite filtrar por data, mas não é muito fácil de usar. Eu vou te mostrar o que parece em um segundo. Portanto, temos a data de início e a data de término aqui, onde teremos datas individuais que queremos filtrar por quatro nossa tabela dinâmica. Mas, por enquanto, tudo está apenas mostrando todos os dados no momento. Então, número um, clique na tabela dinâmica abaixo e clique em PivotTable Analyze e na faixa de opções. Então, vamos clicar aqui na faixa de opções. E então vamos inserir uma linha do tempo no campo de data e tentar filtrar a tabela dinâmica usando o cortador de linha do tempo. Então clique em Inserir linha do tempo aqui. E nós vamos, vamos usar o campo de data do nosso hit de tabela dinâmica. OK. Você notará que pegou esse carinha, que é nosso cortador de linha do tempo. E como eu disse antes, não é super amigável, infelizmente, mostra automaticamente todos os meses, mesmo que só tenhamos dados de janeiro a junho. E se eu quiser expandir isso um pouco mais para que essa barra de rolagem não apareça. Eu posso mover isso aqui um pouco mais. Uma coisa que eu gostaria de fazer é aqui, clicar na linha do tempo, na faixa e remover o cabeçalho. Então clique aqui na linha do tempo. E então eu apenas movo o cabeçalho porque eu ainda não sei que essa é a data. Você também pode remover a seleção rotulada se quiser, mas vou deixar isso por enquanto. Mas você pode ver como personalizar isso ao seu gosto clicando com o botão direito do mouse. Você também pode dizer Tamanho e Propriedades, editar ainda mais a aparência do cortador de linha do tempo. Mas eu não vou fazer muito aqui. Eu só vou manter o padrão como está. E você notará que quando eu clicar em, digamos fevereiro e janeiro, quando eu expandir isso um pouco mais, ele automaticamente filtra como minha tabela dinâmica para as datas específicas que eu procuro isso aqui e olhe apenas de março a abril. E as coisas são filtradas automaticamente, o que é ótimo. Mas que tal querer filtrar minha tabela dinâmica para gostar de 1º de fevereiro a 13 de março. Como se eu tivesse que ir aqui até 1º de fevereiro, mas então como eu sei exatamente onde marchas é meio difícil de fazer. Eu poderia mudar esse menu suspenso para dias, mas isso fica um pouco louco demais para você rolar demais nessa coisa. Não consigo nem lembrar que a barra de rolagem está bem aqui. E, portanto, não é muito fácil de usar, como você pode ver, porque eu tenho que gostar de rolar, rolar muito longe e voltar lá. Portanto, não é muito fácil de usar. Eu geralmente deixo isso como meses ou trimestres. Fenômenos deixam isso como meses. Para este exemplo, vamos configurar um filtro específico ou podemos usar para filtrar essa tabela dinâmica. E de uma maneira mais específica do que nosso cortador de linha do tempo, eu só quero mostrar que esse menor está disponível para filtrar seus dados se você quiser esse tipo de sensação interativa. Mas, às vezes, você quer que as pessoas possam se aprofundar nos seus dados. Então, vamos mover essa tabela dinâmica aqui para o lado. Por enquanto, vamos fazer uma cópia dessa tabela dinâmica. E eu deveria excluir isso porque não quero mais filtrar minha tabela dinâmica. Tudo bem. Isso ainda é filtrado por algum motivo. Vou remover esse filtro, escolha um. Então, isso volta ao estado não filtrado. Selecione as unidades desta tabela meio que selecione manualmente as linhas da tabela dinâmica aqui. Vou copiar isso e colocar aqui ao lado. Novamente, isso é apenas para demonstrar algo. Então, vamos remover todos os campos da tabela dinâmica secundária e adicionar um campo de data duas vezes a seção de valor da tabela dinâmica. Então, vamos aqui. Vá em frente aqui, mostre a lista de campos e vamos remover tudo. Então, como as instruções dizem, vou adicionar o campo de data duas vezes às linhas, desculpe, para desfazer isso, desfaça isso na seção de valores. E, por padrão, ele fornece a contagem que vamos fazer é clicar com o botão direito do mouse primeiro dia e clicar em Configurações do campo e resumir a caixa. Clique em homens para resumir este campo de data e fazer a mesma coisa no segundo dia também. Vou clicar com o botão direito do mouse e depois ir para Configurações de campo. Resumir por homens, acertar. Está bem? E vou reformatar isso clicando com o botão direito do mouse nas configurações de formato. Vamos ver aqui. Número e, em seguida, clique na data. Então, agora esse é um formato de data. Portanto, essa é a data mínima do meu conjunto de dados. A mesma coisa aqui, mas para este eu vou dizer o Mac. Então, vou encontrar o estado máximo e fazer um formato de divisão especial de copiar e colar. Assim, podemos ver a data máxima e nosso conjunto de dados é 61, o que faz sentido. Temos dados de janeiro a 30 de maio em nosso conjunto de dados brutos em nossa guia Dados. Agora, na célula C3, vamos definir a referência da célula para o mandato e uma tabela dinâmica fictícia de sua criação. E então a célula C42 para o estado Mac para o DW criado. Então, o que vou fazer aqui é escrever iguais e dizer que a referência celular é homens bem aqui. Em vez de fazer a fórmula GETPIVOTDATA, vou remover isso e dizer H 18. E isso será 18. E agora, o que é bom nisso é que agora posso ver quais datas específicas estão sendo filtradas quando eu tiver a linha do tempo. Então, se eu clicar de volta para a tabela dinâmica, analise e vá para inserir linha do tempo, data, ok? Agora veja o que acontece quando eu filtro para datas específicas e minha linha do tempo. Então, observe como quando eu mudo meu cortador de linha do tempo, agora posso dizer como, ok, como se eu estivesse olhando especificamente para o primeiro para o quarto de fevereiro , porque quando eu adicionei esse cortador de linha do tempo, ele automaticamente altera essa tabela dinâmica, que é como uma cópia de uma tabela dinâmica, mas tem apenas as datas máxima e mínima do meu conjunto de dados para o que estou filtrando. E então isso alimenta esses manuais para datas, campos de data de vencimento. Então, se eu quiser selecionar uma data específica ou um trimestre de aviso, posso ser mais específico com o que seleciono aqui com o meu conjunto de dados. Então, apenas deixa um pouco mais claro quais são as datas em vez de ter que olhar para este fatiador de tabela dinâmica e tentar estimar que isso é como o final do início de março até o final de maio. Isso só deixa bem claro quais são os dados baseados nesse pequeno hack da tabela dinâmica. E pode encobrir isso assim para que você não possa ver. Ou você pode até mesmo mover essa tabela dinâmica auxiliar para uma planilha separada onde as pessoas não possam ver o que é. Mas é assim que você pode fazer seu cortador de linha do tempo funcionar para seus colegas de equipe e colegas, mas também mostrar as datas exatas aqui nas células 1314. 5. Criando um mapa de mapa: Uma das visualizações mais poderosas no Excel é o gráfico de mapas. Esta lição ensinará como conectar um mapa à sua tabela dinâmica para que você possa mostrar seus colegas de equipe ou espírito de dados cruzar uma região geográfica específica de um gráfico de mapa é um dos meus favoritos para mostrar dados baseados na geografia. Mas o problema é que você não pode realmente construir gráficos de mapas diretamente de tabelas dinâmicas. Mas você pode criar um gráfico dinâmico regular a partir de dados regulares, que você pode vincular de volta à tabela dinâmica. Portanto, é um pequeno truque que mostrarei aqui, que permite visualizar os dados do mapa. Mapeie os dados do gráfico extraídos de uma tabela dinâmica para que seus colegas e colegas de equipe possam ver os dados com mais clareza. Portanto, o primeiro passo é arrastar a coluna de estado para a seção de linha da nossa tabela dinâmica. Vou clicar aqui para mostrar a lista de campos e, em seguida, arrastar a coluna de estado para a seção de linha. E tudo o que temos aqui é apenas olhar para a receita da empresa de telecomunicações por estado. E aqui está o pequeno truque que você pode fazer para criar um gráfico de mapa a partir de uma tabela dinâmica. Copie a tabela dinâmica aqui. Só isso, essas duas colunas copiam isso e colam como valores aqui por enquanto. E vou converter esses números em um formato de moeda para que fiquem bem no meu gráfico. Então você tem esses dados colados, dados codificados aqui. E agora selecione esses dados e vá para Inserir mapas e, em seguida, mapa preenchido. E é claro que você terá que estar conectado à Internet para que isso mostre algo, porque isso é construído como a Microsoft, como mapeamento em geral, o que quer que seja. E confira este gráfico muito, muito legal que você obtém do Excel. E posso renomear esse gráfico para receita, receita total por estado. Mas o problema aqui, então você pode obviamente personalizar a aparência da legenda, a aparência dos números e das cores. Eu gosto de brincar com a cor de fundo aqui, você pode fazer com que um padrão todo preto seja muito bom também. Você pode alterar as cores para um tipo diferente de conjunto de cores. Novamente, o padrão por enquanto está bom para mim. E como eu volto a isso? Este é outro olhar sobre a aparência. Eu só vou voltar para o conjunto de dados renal ou desculpe, a cor original. E o problema aqui agora é que este gráfico, este gráfico de mapa é construído a partir deste conjunto de dados bruto como pizza, o que não é realmente ótimo porque queremos, medida que novos dados entram em uma tabela dinâmica, nós deseja que esse gráfico seja atualizado dinamicamente. Obviamente, seus colegas de equipe sempre obtêm as informações mais atualizadas. Então, o que você pode fazer é depois de criar este gráfico de mapa, clicar com o botão direito do mouse em Selecionar Dados. E então tudo o que você precisa fazer é apenas alterar o intervalo do gráfico para ser esse conjunto de dados da tabela dinâmica. Isso verificará novamente se eles estão escolhendo os dados certos. Portanto, é qualquer uma das equipes até o AF19. Então eu acho que tudo o que você precisa fazer é ir aqui, selecionar dados e, em seguida, alterar esse intervalo do gráfico apenas para ser tudo daqui para lá. Clique em OK. E sim, ele corretamente construído corretamente remove essa coluna de estado. Mas agora você pode ver que esse gráfico está vinculado a essa tabela dinâmica. Então, eu posso realmente excluir esses dados aqui que eu meio que colei como valores. E agora, à medida que novos dados entram no meu conjunto de dados na guia de dados brutos, eles alimentam minhas tabelas dinâmicas, que são atualizadas automaticamente. E então esse gráfico de barras também será atualizado automaticamente porque vem da tabela dinâmica. Tabela dinâmica como dados de origem. Visualização realmente legal e você precisa estar conectado à Internet, é claro, para que isso apareça, porque isso olha para o mundo inteiro e automaticamente descobre de forma inteligente qual parte do mundo mostra seus dados com base no que você tem em sua coluna de localização. 6. Visualizando tendências rápidas com linhas de faísca: sparklines são uma maneira rápida de mostrar aos seus colegas de equipe tendências diárias ou semanais em seus dados. Você também pode manter os minigráficos atualizados usando tabelas dinâmicas para que, quando seus dados forem atualizados em uma tabela dinâmica, as linhas de faísca também serão atualizadas. Temos feito muitas visualizações e gráficos diferentes criados a partir de tabelas dinâmicas até agora nesta classe. Outra das minhas visualizações favoritas são os sparklines. É uma maneira muito rápida e fácil de visualizar seus dados sem ter que criar custos enormes e gráficos de linhas para ver tendências em seus dados de séries temporais. Vamos construir alguns sparklines agora mesmo. A primeira etapa é adicionar o campo de data, a tabela dinâmica abaixo e deixar o campo meses na tabela dinâmica. Então, vou clicar na tabela dinâmica, data do dragão nas colunas. E então vou remover o campo de data, mas deixo o mês. Então, isso agrega minha receita total por estado e por mês. E agora vou selecionar as células 017032 e, em seguida, inserir minigráficos indo para Inserir minigráficos, depois linha. Então, vamos para outro e para 2017 e vamos chamar isso de sparklines. Foi ousado. E selecione tudo de 017032, que é o mesmo tipo de altura da minha tabela dinâmica. E então basta ir para Inserir linha de minigráficos. Agora, aqui ele diz que está perguntando qual intervalo de dados você deseja para os sparklines. Vou apenas selecionar as células C7 a H3N2, que são apenas todos os dados disponíveis do meu conjunto de dados agora a partir de uma tabela dinâmica e, em seguida, clicar em OK. E você verá que você obtém esses pequenos brilhos aumentam essa tendência com base nos dados. Observe como tudo meio que cai em janeiro porque, em geral, desculpe, em junho, em junho, só temos um dia de dados, acredito que seja por isso. É por isso que os números são tão pequenos. Mas o problema aqui é se eu quiser atualizar esses sparklines, é um novo dado que chega para julho e agosto. À medida que minha tabela dinâmica muda. Esse é o sprite, julho, agosto, setembro. Imagine, por enquanto, que isso é como novos dados entrando em nosso botão de tabela dinâmica. Ele vai digitar isso manualmente por enquanto. Se eu digamos que vai de julho vai para $50 mil e depois 60 mil. Observe como meus minigráficos não estão mudando nesta primeira linha porque, novamente, dissemos que o intervalo, o único é de C à coluna H. Então, uma opção é, essa é uma das desvantagens de usar sparklines com intervalos nomeados, seja, queremos que o Excel inclua automaticamente dados dinamicamente se novos dados chegarem, mas não inclua dados se não houver novos dados aqui, vou limpar os minigráficos por enquanto, selecionando isso e, em seguida, vá para o sparkline e depois limpe. E então eu vou para Fórmulas name ranger e em seguida, criar um novo intervalo nomeado chamado my range. E então vou copiar e colar essa fórmula por enquanto e vou explicar o que isso significa em um segundo. Mas essa será a fórmula para o meu intervalo nomeado. Então, voltando ao passo cinco, vamos para Fórmulas chamado range, o gerente. E então eu compartilho que os alunos já iriam deletar isso por enquanto. Clique em OK. E então diga que isso vai ser chamado de meu arranjo. E então se refere a eu só vou remover isso e colar o que eu tenho aqui embaixo. Clique em OK e Fechar. Então, apenas para recapitular o que essa fórmula faz e se você quiser aprender mais sobre funções de deslocamento, eu verificaria meu outro curso avançado de Excel. É tudo sobre fórmulas e recursos avançados no Excel. Esta é a função de deslocamento, essencialmente, diz ao Excel iniciar na célula C7 adolescente e, em seguida, olhar apenas para esta primeira linha de dados e, em seguida contar o número de células que têm, eles não os fizeram de C7 lá, eu sou 17, então está contando tudo daqui até aqui. E eu realmente quero que isso aconteça, mas vou ignorar isso por enquanto. Mas, essencialmente, o que estou fazendo agora é, digamos que eu queira criar um sparkline apenas para esta linha aqui. Eu posso ir para agora, eu posso ir para Insert sparkline, line. E eu vou dizer que meu alcance atingiu. OK. E observe como eu tenho esse brilho lá, que inclui julho, agosto e setembro. Se eu excluir esses números, o minigráfico é redimensionado automaticamente para mostrar apenas os dados disponíveis. Então, se eu colocar esses números de volta, então ele começa a mudar o sparkline. Bem, isso é ótimo, mas e se eu quiser incluir isso, eu quero isso, faça isso para todos esses dados. O problema é que a forma como o Excel funciona agora é que eu teria que criar um intervalo nomeado. Literalmente para cada uma dessas células. Eu tenho 16 estados para os quais tenho que criar intervalos de nomes. Portanto, não é um bom uso do tempo basicamente criar intervalos de nomes um de cada vez, 15 outras vezes. E, em seguida, alterando a função de deslocamento, não gosto, ajustando-a para contar para cada uma dessas linhas. Você não pode simplesmente fazer um intervalo nomeado para toda a sua coisa, o que é lamentável. Então, o que eu normalmente faço nessa situação, vai colocar mais alguns dados aqui. Este exemplo é, vou apenas selecionar dados extras para meus minigráficos. Então, o que isso significa? Eu só vou remover isso por enquanto. E você também pode seguir as instruções aqui. Vamos adicionar novos dados de colunas que devemos tentar fazer em vez de usar o intervalo nomeado, assim como as células C7 a N32 para minigráficos na coluna O. Então, o que isso significa é que estou selecionando minhas células minigráficas aqui. Insira a linha sparklines. Aqui. Vou selecionar tudo, de C7 a 32. Então, basicamente, todos esses dados vazios são incluídos. E agora vamos ver o que acontece com meus brilhos. Portanto, não é a melhor visualização para os sparklines porque ele percebe como , na maioria desses meses, estamos apenas na metade do ano. Então isso significa que a linha só passa pelo meio da célula. Há um monte de espaços em branco à direita da linha. Mas o importante é que, quando estou começando a adicionar novos dados aqui, o sparkline será atualizado à medida que os dados forem adicionados. E esta é a maneira mais rápida de construir um monte de brilhos, um de cada vez, desculpe, de uma só vez. Versus a opção de intervalo de nomes, sobre a qual falamos em etapas como oito a seis. Você teria que criar um intervalo nomeado, um de cada vez para cada célula. Agora, se você tiver apenas três minigráficos para criar, então sim, usar a opção de intervalo de nomes faria sentido porque você não tem tantos minigráficos para mostrar. Mas eu tenho 16 aqui. Se tivéssemos 50 sparklines, acabaria não sendo uma opção escalável. É por isso que geralmente acabo apenas construindo meus sparklines usando esse tipo de método hacky de selecionar todas as colunas vazias que conterão dados para meses futuros ou trimestres futuros. Para que meus minigráficos sejam atualizados automaticamente quando novos dados forem adicionados nos próximos meses. Na minha tabela dinâmica. Imagine que isso virá de uma tabela dinâmica eu escrever manualmente esses grandes números inserindo esses números nas células. 7. Personalizando rótulos de dados: Criaremos alguns gráficos dinâmicos bastante padronizados nesta lição. No entanto , há momentos em que a legenda no gráfico não é exatamente o que você quer ou precisa. Portanto, nesta lição, mostrarei como criar rótulos de dados muito personalizados para seus gráficos, de modo que você e seus colegas de equipe passem menos tempo tentando criar a legenda perfeita ou ela será menos tempo tentando criar a rotulada para o seu gráficos. Vamos criar um monte de novos gráficos dinâmicos a partir da nossa tabela dinâmica. Olá, nesta lição e também brinque com um rótulo de dados personalizado quando quiser apenas adicionar um pouco mais, mais informações ao seu rótulo de dados no gráfico para torná-lo um pouco mais informativo do que o que vem com a legenda padrão no gráfico. Então, veremos o que isso significa no último gráfico que construímos ao lado de cada tabela dinâmica. Novamente, essa tabela dinâmica é construída a partir do nosso principal conjunto de dados brutos na guia de dados brutos. Tudo o que vamos fazer para o primeiro passo aqui é apenas criar gráficos baseados que reflitam o que é observado aqui no rótulo. Então, vamos em frente e vamos para porque quando clico na tabela dinâmica, clique em Inserir e clique em Gráfico Dinâmico. E você percebe que a primeira coisa que obteremos é, por padrão, um gráfico de barras para esse conjunto de dados, porque temos um campo simples com um campo simples com vários valores para um determinado campo. E eu não vou passar pela personalização desse gráfico de barras agora. Mas normalmente o que eu gosto de fazer como primeira coisa é que eu gostaria de mover a lenda Bem no topo. Eu apenas gostei da aparência para que ela não afaste as coisas ou mova as linhas da grade também. E também talvez torná-los, tornar esses números um pouco mais fáceis de ler em vez de tê-los totalmente escritos por 1 milhão a 2 milhões. Então, vamos fazer isso agora. Então, agora, para este gráfico de linhas, observe como os dados são séries temporais agora. Então isso é tudo que vamos fazer. Um gráfico dinâmico aqui também. Mas quando clicarmos com o botão direito do mouse, vamos apenas alterar o tipo de gráfico para uma linha e clicar no gráfico de linhas básico. E, novamente, apenas algumas super básicas de formatação de gráficos coisas super básicas de formatação de gráficos que vou fazer por enquanto. E não vá muito fundo nessas coisas, os gráficos de formatação, porque isso pode ser uma lição totalmente nova por si só. Finalmente, para o gráfico de pizza, temos apenas três categorias de dados para o campo de contrato que você viu no passado. Insira gráficos dinâmicos. Vamos apenas mudar esse tipo de gráfico também para um gráfico de pizza, que eu não uso com muita frequência, mas ele está lá, eu quero que esteja lá. Então, essa lenda simplesmente diz como é o nome de. Isso ajuda você a descobrir qual cor é qual cor. E o que eu gostaria de fazer é talvez adicionar os rótulos de dados aqui para que eu possa ver qual é o valor real. Mas aqui está o que vamos fazer, algo um pouco mais criativo para obter o rótulo de dados. Isso é um pouco mais interessante para nós lermos. Em vez de ter que usar o padrão, a legenda padrão aqui. Então, vamos voltar ao topo e ver as instruções. Vamos criar um gráfico dinâmico para cada tipo de gráfico. Agora vamos criar uma fórmula de concatenação do usuário. Vamos escrever isso quando Kennedy de algum lugar fora da tabela dinâmica que concatena o nome da série, o valor, a porcentagem do total, e também insere novas linhas em, inserir uma nova quebra de linha nessa fórmula de concatenação. Então, os fazendeiros do Condado de Cook, e parece algo assim. Vou copiar e colar isso para que possamos começar com algo. E vamos preencher isso abaixo e você começará a ver como isso se parece em termos de construção disso. Então eu vou direto aqui e escrever iguais e depois colá-los. O que eu tenho até agora em instruções. E você percebe que o que eu tenho aqui é que estou concatenando. Vamos seguir essa fórmula muito rapidamente. Estou concatenando B47, que é esse texto mês a mês com um traço. Então eu estou pegando o símbolo do dólar, convertendo ver 47 em um dólar ou formato. Então isso é $1.47. Então corra. E estou arredondando C4, T7, que é esse número dividido por C5, que é o total. E multiplicando e executando isso para 0 casas decimais e adicionando um sinal de porcentagem ao final que qualquer nova quebra de linha. Vamos pressionar Enter e ver o que resulta disso, porque será mais interessante ser mais informativo para ver como isso se parece depois de vermos o resultado. Então, o que temos aqui é novamente o nome da série mais esse traço, mais o valor real em dólares, que corresponde ao mês a mês aqui. E então 33%, o que é simplesmente 4,4 milhões dividido por 13,7 milhões. O importante que eu quero mostrar é esta tenda de gráficos. Este gráfico dez diz ao Excel para inserir uma nova quebra de linha na fórmula. Então, vou fazer a mesma coisa, mas por um ano para esse valor específico na coluna do contrato. Então, vou selecionar esses dados aqui. Eu vou literalmente escrever vírgula e depois colar tudo. E vamos apenas editar isso para ser para a próxima série, que é B48. Estamos pegando um cifrão. Estamos usando o formato em dólar para C4H8, que é de um ano, C4H8. E então simplesmente arredondando. Veja 4850. Pressione Enter. E observe como você não vê a nova quebra de linha na fórmula aqui. Observe que eu vou de 30% diretamente para um ano. E então o valor aumenta. Vamos ver o que isso parece. Deseja realmente inserir uma caixa de texto e ouvir isso mais uma vez. Para dar conta da última série. Vou editar isso para ser quatro antes E9, C4H9, rodada C4H9. E agora eu tenho todos os três meses a mês, um, ano, dois anos junto com o valor montar o valor percentual. E agora vou inserir uma caixa de texto bem aqui. Por enquanto. E na barra de endereço da fórmula da caixa de texto, vou dizer que igual à fórmula é creditada com a fórmula concatenada aqui em baixo, que é F, C6, T2. E veja o que acontece na caixa de texto. Se eu expandir isso até a direita, observe como há novas linhas enterradas inseridas na caixa de texto, o que é muito legal. Agora, essa nova linha é causada por esse caractere dez na fórmula concatenada. Então, digamos que se eu excluir esse gráfico dez e o primeiro da série e ver o que acontece. Observe como esse ano é automaticamente concatenado à primeira série. Então, se eu apenas desfizer isso, desfazer isso agora eu tenho o gráfico dezenas aqui. O gráfico tende basicamente a dizer ao Excel, insira uma nova quebra de linha quando você estiver colocando essa fórmula em uma caixa de texto. E então o que você pode fazer aqui é essa caixa de texto, e pode ser um novo rótulo de dados que você pode sobrepor no gráfico para torná-lo mais informativo sobre o significado dos dados. Então, isso ficará mais claro mais tarde nesta aula, mas vou deletar esses rótulos por enquanto. E talvez eu mova isso para a direita aqui só para fazer isso, e eu vou fazer isso muito menor porque isso é muito grande. E vamos aqui. Vamos me ver um pouco menor. E, em seguida, oferece aos seus colegas, colegas equipe e clientes uma maneira melhor de analisar dados específicos sobre seu gráfico de pizza ou o que quer que seja. E concluímos cálculos personalizados, que é essa porcentagem, que informa ao público quanto a fatia mensal ocupa em toda a coisa total. Você pode imaginar colocar outros vários pontos de dados nesse rótulo de dados que ajudarão a comunicar algum aspecto sobre o seu conjunto de dados que a legenda básica ou os rótulos de dados não podem informar no gráfico de pizza. Se eu for para Rótulos de dados, acredito que você pode alterar esses rótulos de dados para incluir outras coisas que eu possa valorizar nas células. Ah não, eu não quero fazer isso. Posso incluir o nome da série, que não é muito útil porque essa é a série um megabit, o nome da categoria que é semelhante ao que tenho aqui. Eu também gostaria da porcentagem que é boa aqui. Mas o importante é que eu posso personalizar esse rótulo de dados para ser o que eu quiser. Enquanto este, se você estiver restrito a tudo o que o Excel tem e às opções padrão. E eu normalmente gosto de usar este. Quero chamar números específicos, meu conjunto de dados. Portanto, pode não incluir, como um ano e dois anos, essas duas linhas e incluir apenas mês a mês. Mas o importante aqui é que você também pode gostar dos rótulos de dados em um gráfico de pizza ou qualquer cobrança que você tenha. Este único condado é atualizado automaticamente com base nesta tabela dinâmica aqui. Assim, à medida que esses dados são atualizados, essa fórmula concatenar também é atualizada, o que atualiza essa caixa de texto, que faz referência a essa fórmula concatenada. 8. Consolidação para um painel: criamos uma variedade de gráficos Até agora, criamos uma variedade de gráficos e visualizações nessa classe. Então, vamos unir tudo em um painel coeso com o qual você e seus colegas, colegas equipe e outras partes interessadas internas podem interagir e brincar para visualizar seus dados. Se você está acompanhando todas as lições desta aula, criamos vários gráficos de visualizações a partir dessas tabelas dinâmicas e vamos juntar tudo em um único painel que podemos compartilhar com nossos colegas e equipes para colaborar melhor em nossos dados. E esta lição também alimentará menos de oito e como podemos tornar esse painel muito dinâmico. Então, a primeira coisa que vamos fazer é usar os gráficos e visualizações que construímos até agora, nas últimas lições, vamos recortar e colar esses gráficos e simplesmente colocá-los em nossa área principal do painel. Então, vejamos que a barra de dados é menor que dois. Vamos realmente não filtrar isso por enquanto. Vamos escolher. Tudo. Opa, isso não está certo. Vamos dar uma olhada no Mel. Eu vou basicamente copiar e colar isso. Coloque na lição sete e cole e talvez torne essa fonte um pouco menos. Isso enfraquecerá, manterá tudo limpo e arrumado. E há algumas maneiras de fazer isso. Uma maneira é simplesmente copiar e colar na tabela dinâmica. Mas então você pode precisar, você verá esses menus suspensos, o que também é meio irritante. Então, outra maneira de fazer isso é o que eu gosto de fazer é, na verdade, apenas referenciar os dados. Então, o que vou fazer é selecionar apenas esses valores que foram referenciados , a série real. Então agora eu só tenho as categorias do meu pedacinho mais ousado. Aqui, vou fazer referência aos dados reais. Bem aqui. Estou começando a ter 13, então não entendo esse GETPIVOTDATA. Vou mudar isso para vender a equipe Dieter. Esse é o nosso número real de receita. E eu arrasto isso para baixo. Eles convertem isso em um formato de moeda que eu gosto 0. E chamaremos isso de receita total. Isso. Então, agora eu posso realmente aplicar as barras de dados que temos na tabela dinâmica original, que farei apenas como barras de dados de formatação condicional. Eu vou fazer isso como verde com um gradiente que eu gosto. Os brilhos. Sparklines vêm de menos de cinco. Vou copiar esses estados por enquanto, assim como fiz com o método de pagamento aqui. Vamos passar isso para aqui. Ele tem valores especiais. Na verdade, vamos inserir mais algumas colunas aqui e nosso painel aqui, também vou fazer a mesma coisa que fizemos com os minigráficos e menos de cinco, vamos fazer com que todos tenham o mesmo tamanho de fonte. Você seleciona. As células nas quais você deseja inserir os minigráficos. Vá para Inserir linha de minigráficos. E vamos selecionar os dados de menos de cinco, que eram todos dados de C7 até eu acredito que eram n 32. Está tudo bem. Os dados de junho foram filtrados. Vamos trazer de volta junho. Lá vamos nós. Agora temos um total de janeiro a junho. E agora nossas linhas de pico agora serão atualizadas automaticamente com base nesse pivô, na tabela dinâmica e no cortador de linha de tempo de menos de três, se você se lembra, tínhamos um cortador que nos permite filtrar dados. Na verdade, vou copiar e colar essa tabela dinâmica porque gosto de como o fatiador pode alterar automaticamente os dados, a tabela dinâmica no painel. Então, vamos colar essa tabela dinâmica aqui. Vamos tornar isso menor. E vou inserir um novo cortador de linha do tempo. Eles bateram em Ok. Isso, você viu isso em menos de três. Eu só vou mover isso aqui para a direita, a cabeça. E eu tenho um bom cronograma onde posso basicamente filtrar por meses específicos. Pode usar, como você pode ver aqui, que eles filtrem automaticamente a tabela dinâmica com base no que você selecionar. E vamos selecionar tudo por enquanto. Podemos ver nossos dados. Agora, o gráfico do mapa, vamos realmente mover esse nível para baixo um pouco porque temos nossos dados em nível de estado aqui. Vou apenas copiar e colar este gráfico que criamos e menos de quatro na lição sete. Vai ficar um pouco amassado, mas está tudo bem. Vamos deixar isso um pouco menor. Talvez possamos mover isso para baixo também. Tudo bem, agora começando a ficar juntos um pouco. E então temos um gráfico de pizza. Vamos baixar um pouco essa coisa de fatiadores também. Temos nosso gráfico de pizza com legenda dinâmica de menos de seis. Mais uma vez, vou copiar e colar esse gráfico de nós e seis, colocá-lo aqui abaixo deste rótulo. E observe como a caixa de texto que éramos originalmente remove esses rótulos de dados. Observe como a caixa de texto que criamos originalmente, como parece que eles foram removidos de alguma forma e vamos voltar e inserir de volta essa caixa de texto. Insira o retardatário da caixa de texto aqui. Lembra como fazemos isso? Nós apenas dizemos igual a essa célula. E agora temos nossa caixa de texto. Não tenho certeza se cortei essa caixa de texto e a coloquei na lição sete. E lá vamos nós. Eu vou fazer isso menor também, que possamos encaixar tudo isso dentro do gráfico. E isso é realmente uma boa maneira de ver seus dados e ver os valores reais do seu gráfico. Se você quiser criar valores personalizados que a legenda padrão não seja exibida. Agora, na segunda etapa, vamos criar um ou dois gráficos de barras adicionais ou gráficos dinâmicos de linha com base nos dados brutos e colocá-los abaixo desse cabeçalho aqui. Tudo o que vou fazer aqui é pegar isso, isso eu não deveria entender. Ele vai criar outro gráfico de barras com menos de dois zeta, que é essa coisa bem aqui. Eu só vou dizer woops vá para alguém no arquivo errado. Agora. Eu vou dizer inserções, criar um gráfico dinâmico. E isso criará automaticamente como troca para mim isso, recortar e colar isso, e colocar isso em nossa lição sete. E esse será o nosso gráfico de barras. Novamente, há várias maneiras de formatar isso. Novamente, a coisa mais básica a fazer é colocar a lenda no topo. E observe como esses rótulos são bem longos. Na verdade, estou invertendo o tipo de gráfico para torná-lo um gráfico de barras horizontais. Então, vamos ver aqui. Já faz um tempo desde que eu fiz esse formato de mudança. Há uma maneira de alterar o tipo de gráfico. Lá vamos nós coluna. E, em seguida, gráfico de pizza 2D. Eu gosto de fazer isso porque é mais fácil do que aqui ler os rótulos longos reais aqui. E então também podemos corrigir esses rótulos de eixo. Mas vou deixar isso por enquanto para as linhas de grade. E na próxima etapa, alinhe os gráficos selecionando forma, Forma, Formato, alinhamento ou também usando Alt e deslocamentos. Então o que isso é, o que isso significa, quando você tem um monte de gráficos empilhados uns sobre os outros assim, este é um pouco importante demais para nós. Agora, esses dois, quando você tem dois gráficos, eu quero que você queira se alinhar. A maneira mais fácil de fazer com que eles se alinhem é apenas selecionar, digamos que este esteja aqui, este aqui. Você pode segurar Shift, selecionar os dois gráficos, Formato da Forma e, em seguida, ir para Alinhar. Clique em Alinhar à esquerda. E isso automaticamente coloca os dois gráficos nivelados à esquerda. Você também estaria alinhado em cima em qualquer lugar. Esta é a maneira mais rápida que eu gosto de mover gráficos para alinhá-los juntos, de modo que eles fiquem como todos descarregados, nivelados juntos. Outra maneira de fazer isso é mantendo pressionada a tecla Alt, que permite mover o gráfico com base nas células reais da planilha. Então, se eu segurar a tecla Alt e começar a mover o chara, observe como eles gostam. Ele se encaixa na grade essencialmente. Por alguma razão, também cria uma cópia que é meio estranha. Não sei por que. Se eu segurar a tecla Shift agora e mover o mouse para a esquerda e para a direita, isso não me permite mover o gráfico para cima e para baixo com muita facilidade. Então, é outra maneira de alinhar as coisas ao longo de um eixo. Se eu segurar a tecla Shift para cima e para cima e para baixo, é difícil para mim movê-la para a esquerda e para a direita , a menos que eu realmente mova meu mouse. Portanto, é outra maneira de alinhar as coisas corretamente. Eu normalmente só faço isso. Selecione o gráfico. Eu só faço a coisa de Alinhar aqui porque é a maneira mais fácil mover as coisas para um lado. É assim que você pode alinhar as coisas como está, seu painel começa a receber mais e mais gráficos. O alinhamento é muito importante para que fique bonito e arrumado. Este não é muito bem formatado, mas você meio que entende a essência. Vamos torná-lo menor também. E outra coisa que eu gostaria de fazer, que não vou fazer neste painel, é mover as legendas de todo o meu gráfico. Então, se todos os meus gráficos têm receita aqui e diz apenas receita total aqui. Se isso também fosse receita total, digamos que eu tivesse um gráfico. Encontre outro gráfico de receita que eu tinha da lição anterior. Na verdade, eu não tenho um. Mas o que eu faria neste caso, se eu tivesse um monte de gráficos que se parecem com isso, se eu tivesse que virar assim, eu vou mover isso para cá, apenas inventando por enquanto. Se eu tiver essa mesma cor azul para receita, eu realmente excluiria essa legenda aqui e, literalmente, apenas subia aqui e criaria minha própria legenda personalizada. A razão é porque você não quer repetir essa lenda repetidamente. Múltiplos gráficos que podemos fazer é literalmente desenhar seu próprio login, como fazer assim e apenas como fazer assim alguma fronteira em torno disso, assim. E há maneiras melhores de formatar isso. Bem, você poderia dizer que a receita total é igual a e apenas fazer isso como o tom de azul que você gosta. Então, quando as pessoas olharem para a legenda, você verá qualquer coisa neste painel que tenha um azul no rótulo do gráfico. Isso será porque a receita total deles. Então, se eu olhar aqui embaixo, você obviamente terá o título do gráfico renomeado para receita por tipo de contrato. Mas as pessoas saberão que o azul corresponde à receita dessa lenda personalizada que você tem aqui. A última maneira que eu gostaria, fazer isso parecer um pouco mais arrumado. Novamente, isso ainda é muito difícil nas bordas, é simplesmente ir para Exibir e remover as linhas de grade. Só temos cinco. E agora você só tem esse belo fundo branco liso que você pode compartilhar em uma chamada do Zoom ou em uma reunião. E parece um pouco melhor. E analisar os dados quando você não tem essas linhas de grade meio que distrai o, seu colega de equipe ou suas partes interessadas do painel principal. Outra maneira de fazer o, as linhas de grade desaparecerem, que é outro truque que eu costumava fazer antes saber que as linhas de grade estavam apenas selecionando esses dados aqui. Às vezes eu gosto de fazer isso porque não quero que toda a minha planilha seja removida. As linhas da grade, vou pintar a cor de fundo das células para branco. É isso aí. E isso faz com que tudo pareça novo, tudo bonito e arrumado. Mas então você diria que eu perco meus rótulos porque na verdade eram textos em preto e branco em rótulos pretos. Então não quero fazer isso. Talvez neste caso eu possa apenas remover as linhas de grade. E é assim que você pode basicamente colocar todas as coisas que fizemos até agora nas últimas lições em um painel. E é por isso que você também deve considerar ao criar seus próprios painéis para sua própria empresa ou organização. 9. Como vincular os fatiadores interativos: Vamos criar alguns slicers e adicioná-los ao nosso painel para que possamos filtrar e classificar rapidamente nossos dados e gráficos dinâmicos para nosso painel. Agora, uma coisa que queremos fazer é poder conectar nossos slicers a todos os gráficos dinâmicos e tabelas dinâmicas em nosso painel para que quando um de nossos colegas de equipe atualizar os fatiadores, conectar nossos slicers a todos os gráficos dinâmicos e tabelas dinâmicas em nosso painel para que, quando um de nossos colegas de equipe atualizar os fatiadores, atualizará automaticamente todo o painel. E assim, tudo é automatizado sem que você precise fazer nenhum manual, copiar e colar ou clicar em menus suspensos ou até mesmo Mac. Vamos pegar o painel que criamos em menos de sete e torná-lo um pouco mais interativo para nossos colegas de equipe usarem, para que eles possam dividir e dividir os dados da maneira que quiserem. Falando em fatiar e cortar dados, vamos realmente adicionar alguns slicers que fazem com que você não torne seus dados mais interativos em nosso painel. Então, na primeira etapa, vamos voltar para a consulta do painel e menos de sete e adicionar um slicers de visualização abaixo na seção que diz slicers. Então, vamos voltar para a lição sete. E nós vamos estar aqui em baixo e nesta pequena seção bem aqui. Isso é excluir este gráfico por enquanto. É, é uma cópia disso. E vamos fazer alguns cortadores. Portanto, temos essa tabela dinâmica aqui, que é novamente uma cópia de todas as tabelas dinâmicas nesta pasta de trabalho. Eu posso simplesmente ir para PivotTable, Analyze, Insert Slicer. E digamos que eu queira inserir um cortador de gênero. Acertar, ok? E então nós temos isso menor aqui. E se você fez minhas outras aulas, você sabe, você pode fazer muitas personalizações nesses fatiadores, como redimensioná-los, mudar as cores. E tudo isso pode ser feito clicando com o botão direito do mouse e depois indo para Tamanho e propriedades. E você pode conferir todas essas coisas diferentes que você pode fazer para personalizar o fatiador. Vamos fazer mais um slide por enquanto, só para que possamos mostrar o poder dos Slicers. Vamos para Inserir fatiador de tabela dinâmica. E vamos fazer um desta vez sobre o tipo de contrato. Agora temos esse cortador. Vamos arrastá-lo até a parte mais fina do painel. Essa parte do painel é como se você diminuir um pouco o zoom, quando compartilhamos esse painel com nossos colegas de equipe e esse arquivo do Excel, eles podem usar esses cortadores para controlar o que realmente aparece no painel em termos de dados. Agora, o problema é que esses slides, não estão necessariamente conectados a todos esses dados. Então, como realmente fazemos isso? Então, vamos voltar para a lição oito etapas. Vamos clicar com o botão direito do mouse em cada fatiador e selecionar conexões de relatório e marcar todas as tabelas dinâmicas e a lista de conectar o fatiador a essas tabelas dinâmicas. Essencialmente, o que queremos fazer é conectar esses cortadores a todos os dados neste painel. Então, novamente, quando as pessoas clicam nessas opções, ele filtra automaticamente os dados porque no momento não está fazendo nada. Como você pode ver. Eu posso realmente, se eu quiser ser capaz de selecionar várias opções aqui, eu posso clicar nesta caixa de seleção e agora eu posso selecionar ambos masculino, masculino e feminino. Ok, então vamos acompanhar isso. Portanto, se clicarmos com o botão direito do mouse no fatiador, vá para relatar conexões. Vamos ver o que aparece aqui. Você verá cada tabela dinâmica à qual você pode conectar esse cortador específico neste arquivo menos de um pivô. Vamos fazer barras de dados. Você pode ver aqui que todos esses são os números da lição por esse número. E vamos voltar para a lição um, lição para lição três e dar uma olhada no que eles representam muito rapidamente. Se eu for para a aula um. Se eu clicar nesta tabela dinâmica e depois clicar em Análise de Tabela Dinâmica. Você percebe aqui que eu renomeio isso para dinamizar a lição um pivô. Por padrão, ele também lhe dará um nome aleatório, como um nome aleatório, como Tabela dinâmica, uma tabela dinâmica. Mas dando a ele um nome exclusivo como este, menos de duas barras de dados, porque esta representa a tabela dinâmica para barras de dados. Isso significa que, quando clico com o botão direito do mouse neste cortador, vou relatar conexões, esses nomes são um pouco mais descritivos quanto ao que essas tabelas dinâmicas estão presentes. Você pode descobrir se deseja ou não conectar essas tabelas dinâmicas a esse cortador. Então, eu vou em frente e verificar todas essas outras tabelas para que este cortador controle todas as tabelas dinâmicas em todas as minhas aulas. E então, como você sabe, em nosso painel, todos esses gráficos, essas visualizações estão todas conectadas às tabelas dinâmicas originais nas lições anteriores. Então, agora vou diminuir o zoom para mostrar a vocês o poder desse menor. Mostre o máximo possível da tela. Se eu clicar agora no correio ou feminino. Veja como todos esses dados mudam dinamicamente. Porque conectamos esse cortador a todos os dados, todas as tabelas dinâmicas em nosso arquivo. Vamos fazer a mesma coisa com esse cortador também. Relate conexões, expanda isso, isso é verificar toda a tabela dinâmica. Tabelas dinâmicas. Tudo bem. Agora, se eu desmarcar algumas dessas opções de contrato, você verá como esses dados são todos filtrados com base no que eu sugei nisso, neste fatiador. O que é muito legal, porque agora, quando você compartilha esse painel com seus colegas de equipe ou colegas, eles podem limpar os dados, filtrar e classificar esses dados sem precisar realmente ir para o tabelas dinâmicas subjacentes e selecione os filtros e os menus suspensos. Isso torna esse painel muito mais interativo e tudo está contido em uma única página. E, claro, quando você realmente organizar seu painel, espero que o torne um pouco mais organizado do que isso. Mas eu só queria mostrar o que o poder dos slicers pode fazer pelos seus painéis. Na etapa três, já fizemos isso, mas você pode simplesmente clicar em algumas das opções e nos cortadores para ver como eles podem alterar dinamicamente os dados em seu painel coeso. E isso é menos de oito. 10. Atualizando seu painel: Um dos maiores problemas com tabelas dinâmicas é que, às vezes os dados nem sempre são atualizados quando você tem novos dados chegando. Em. Nesta lição final, mostraremos como você pode atualizar suas tabelas dinâmicas de dados com um clique, convertendo sua lista de dados em uma tabela do Excel em vez de uma lista regular. Portanto, isso automatizará tudo para você e seus colegas de equipe e exigirá menos trabalho manual todos os dias ou todas as semanas ou por quanto tempo você estiver atualizando este painel. Nesta última e última lição desta aula, falaremos sobre como você pode atualizar os dados do seu painel conosco e sete, para garantir que esses dados sejam sempre atualizados quando você inserir novos dados em seu Arquivo Excel. Essencialmente, o que estou tentando dizer é que, quando novos dados entram em nossa planilha de dados brutos, queremos tudo nas tabelas dinâmicas, todos os relatórios são atualizados automaticamente sem que precisemos tocar qualquer coisa. E essa é uma lição muito importante porque isso vai para o tipo de infraestrutura de back-end em torno de como seu painel pode se manter atualizado. Porque, tipicamente falando, você atingirá todos os tipos de novos dados adicionados aos seus dados de origem aqui, que é um arquivo chamado dados brutos. Novos dados chegarão ao final desta planilha. E você quer ter certeza de que suas tabelas dinâmicas possam coletar esses novos dados. No momento, ele para em torno de 6005987 para ser exato. E na primeira etapa, vamos fingir que temos alguns dados novos, que podem ser os novos dados desta semana, os novos dados deste mês, e estão na nova guia de dados. Então, temos essa nova guia de dados e isso é ampliado um pouco para que possamos ver alguns dos dados. Ele tem exatamente os mesmos cabeçalhos de coluna que nossos dados brutos. E temos cerca de 100 novas linhas de dados, que representam dados para junho. Nossos dados são dados brutos só vão até 1º de junho, e isso contém todos os dados para o mês de junho, de 1º de junho a 30 de junho. Portanto, a primeira coisa que queremos fazer é copiar os dados ou novos dados da nova planilha de dados para a guia de dados brutos. E vamos apenas fingir que acabamos de receber um novo despejo de dados de nosso banco de dados e vamos adicionar os dados ao fundo de Deus trouxe isso aqui. Então, vamos aqui para novos dados. Eu só vou copiar tudo da linha dois e abaixo. Então eu copiei tudo da linha dois para 100. Vá até a guia de dados brutos, que é o nosso tipo de dados de origem, onde todas as nossas tabelas dinâmicas são criadas. Faça uma pasta regular aqui. E você pode pensar que seu trabalho está feito. Vamos tentar atualizar e PivotTable e ver se ele pega ou não novas 100 linhas de dados que acabamos de adicionar à parte inferior do conjunto de dados. Então, vai de agora 5987 até 6.086. Então, se eu for ao nosso painel muito rápido e digamos que eu olhe para esta tabela. Se eu clicar em PivotTable e se eu apenas clicar com o botão direito, eu mudo da atualização de primeira ordem. Nada acontece. Devemos esperar ver mais dados para o mês de junho, mas nada está acontecendo. Se eu clicar em Tabela Dinâmica, Analisar e clicar em alterar a fonte, veja o que aconteceu. Olhe para nada que realmente tenha. E se você observar os dados brutos, eles ainda vão de A1 a Q 5987 em nossa planilha de dados brutos. E não pegou essas novas linhas de dados de 5988 e abaixo. Então você pode estar se perguntando como faço para manter essa tabela dinâmica, essa tabela dinâmica, todas essas tabelas dinâmicas atualizadas. Se ele conseguir pegar essas novas 100 linhas de dados, é um problema, certo? Porque terei que entrar em cada tabela dinâmica e, em seguida, alterar a fonte de dados e alterá-la para ser de K1 a Q 600 e o que quer que seja como um 86. Agora, existem algumas maneiras hackeadas de fazer isso. Você poderia fazer disso um intervalo de nomes e usar a contagem, um truque de deslocamento, sobre o qual falamos , acho que na lição, lição sete ou cinco, tentando encontrar, sim, usamos essa função de deslocamento para criar dinamicamente um intervalo. Existem algumas maneiras hackeadas de fazer isso, mas uma maneira mais fácil, uma maneira mais nativa de fazer isso no Excel é criar uma tabela. Se você quiser aprender mais sobre tabelas, eu tenho outra aula avançada do Excel sobre tabelas do Excel, que é realmente um dos meus recursos favoritos no Excel. Vamos agora para a etapa três. E isso excluiu novos dados da nossa tabela principal e da guia Dados da haste que acabamos de adicionar. Então, vamos em frente e excluir todos esses dados por enquanto. Nós fomos de 598598826086. Esses foram nossos novos dados que adicionamos. Vamos excluir tudo isso por enquanto. Então, estamos de volta à estaca zero do nosso conjunto de dados bruto original. Etapa quatro, vamos transformar os dados em dados brutos em uma tabela selecionando tudo e pressionando Control T Vamos nomear a tabela como dados de sublinhado bruto também. Se você não sabe quais tabelas são um Excel muito rapidamente, elas são simplesmente uma maneira adicionar alguma formatação aos seus dados, sua lista de dados. Mas, o mais importante, ele fornece um tipo nomeado de objeto de tabela com o qual você pode trabalhar e outras partes do seu arquivo do Excel. Então, novamente, faça meu outro curso de tabelas do Excel. Aprenderemos mais sobre as tabelas do Excel. Mas vamos seguir as etapas desta lição por enquanto. Vamos transformar os dados brutos em uma tabela que pressiona Control T depois de selecionar a tabela inteira. Então vá para dados brutos, e eu vou selecionar tudo, desde a linha um até 500988. Eu vejo tudo selecionado. E agora vou pressionar Control T. E você percebe como essa opção diz onde estão os dados da sua tabela? A1 a Q 57. Minha tabela tem cabeçalhos. Temos cabeçalhos na linha um. Acertar, ok? E vamos ver o que o Excel acontece. Excel. Observe como nossos dados são bem parecidos, mas agora eles têm essas cores de linha alternadas. Tem esses menus suspensos que podemos filtrar. Pode parecer que você acabou fazer alguma formatação básica em nossa tabela, mas é importante saber que essa tabela pode ter um nome. Agora, vamos chamar essa tabela de dados de sublinhado brutos. Então, agora eu posso realmente usar essa tabela em fórmulas em outro lugar no meu arquivo do Excel. Vamos voltar para a Lição Nove. E agora vamos fazer, vamos copiar e colar os dados da nova guia de dados novamente na planilha de dados brutos que contém uma tabela de dados brutos. Então, vamos fazer a mesma coisa que fizemos na etapa um, que é copiar tudo da linha dois para a linha 100 em nossos novos dados. Novamente, isso representará novos dados provenientes de nosso banco de dados para os dados desta semana ou os dados da próxima semana? Bem, eu acho que esta semana porque próxima semana é no futuro. Vamos copiar esses novos dados. Volte para nossa guia de dados brutos e vá para a parte inferior da nossa tabela que acabamos de criar. E vamos ver o que acontece quando eu colo esses dados do 5988 e abaixo do comando regular V para colar. E observe como o Excel sabe que estamos tentando adicionar dados adicionais a essa tabela. Por isso, adiciona as cores de linha alternadas a tudo abaixo de 26086. Agora vamos para a etapa final e atualizar a tabela dinâmica para ver como ela inclui novos dados que foram adicionados à parte inferior e nossa planilha de dados brutos. Portanto, lembre-se de que, quando atualizamos essa tabela dinâmica, ela não atualizou os dados. Não incluí as novas linhas de dados. Vamos ver o que acontece agora quando eu atualizar esta tabela. Clique com o botão direito e atualize Observe como os dados de junho aumentaram um pouco de trinta mil, duzentos e oitenta mil. Para verificar se pegamos os novos dados. Posso ir para Tabela Dinâmica, Analisar, alterar os dados de origem e ver como as referências de A1 a Q 6086. Então isso significa que pegaram os novos dados porque originalmente converti minha tabela original de dados brutos em uma tabela. E isso agora diz à tabela dinâmica. Quaisquer novas linhas que eu adicionar a essa tabela de dados brutos serão incluídas automaticamente no conjunto de dados. E isso alimentará as tabelas dinâmicas. Então, agora, se eu voltar para cada lição, acho que esses múltiplos já foram atualizados por padrão. Mas se eu atualizar isso, provavelmente já atualizo porque acho que quando você atualiza um atualiza tudo. Há alguma configuração para isso. Posso verificar a fonte de dados para ter certeza de que a fonte vai até o final dos meus dados brutos. Também posso verificar isso com esta tabela. E isso é novamente um truque muito legal porque à medida que você obtém novos dados entrando em seu arquivo do Excel, se você adicionar a parte inferior desta tabela, suas tabelas dinâmicas pegarão automaticamente todas essas novas linhas. de dados sem que você precise ir aqui manualmente, clique com o botão direito do mouse e altere a fonte de dados e altere essas linhas, o número da linha para selecionar todas essas novas linhas de dados. Esse truque permite que você tenha uma maneira mais dinâmica de atualizar seu painel aqui, com o objetivo de remover a necessidade de fazer muitas fórmulas manuais, editar ou copiar e colar em seu Arquivo Excel. E isso facilita a atualização do painel no futuro, quando novos dados chegam. 11. Considerações finais: Muito obrigado por fazer esta aula avançada do Excel sobre tabelas dinâmicas e criar um painel. Espero que você tenha aprendido muito sobre como automatizar muitos relatórios e gráficos dinâmicos e tabelas dinâmicas em seus arquivos atuais do Excel. A maior conclusão novamente, como Automatizar, automatizar, como faço para reduzir o trabalho manual em minhas tabelas dinâmicas e painel. Assim, posso passar mais tempo analisando tendências, fornecendo insights e, finalmente, gerando impacto nos negócios para sua empresa. Espero que você tenha a chance de fazer o projeto da turma. Faça perguntas no fórum da turma. Publique seu projeto. Na seção do projeto são supostas capturas de tela. E ficarei feliz em fornecer feedback e responder a quaisquer perguntas que você tenha. Espero que você se torne um Excel crescido em sua equipe e impressione todos os seus colegas de equipe com todas as suas novas habilidades de tabela dinâmica e painel do Excel .