Tabelas dinâmicas do Excel: técnicas avançadas de tabela dinâmica para analisar e apresentar dados m

1. Introdução: Quando comecei minha carreira como analista financeiro e aprendi a usar tabelas dinâmicas, isso me deu uma maneira muito poderosa de explorar e analisar meus dados para encontrar tendências que eu poderia relatar à minha equipe e executiva para diferentes reuniões, relatórios e painéis. Sou Al Chen, arquiteto de soluções na Coda. A aula de hoje será sobre o uso tabelas dinâmicas com técnicas avançadas para atualização, classificação e filtragem e para adicionar campos calculados a tabelas dinâmicas. Eu continuo a usar tabelas dinâmicas depois de mais de 10 anos de ser um usuário poderoso do Excel porque eu sou constantemente confrontado com conjuntos de dados realmente grandes. Preciso de uma maneira rápida de resumir os dados em relatórios e painéis. Isso é o que tabelas dinâmicas, um recurso realmente clássico no Excel, me permite fazer. Aprenderemos algumas técnicas avançadas de tabela dinâmica nesta classe, como adicionar campos calculados às suas tabelas dinâmicas para fazer cálculos rápidos rapidamente. Também aprenderemos como você pode adicionar fórmulas aos arquivos do Excel usando a fórmula GETPIVOTDATA para extrair dados de suas tabelas dinâmicas. Também discutiremos como você pode estender suas tabelas dinâmicas usando gráficos dinâmicos, usando segmentações e tornar suas tabelas dinâmicas mais interativas. Além disso, como você pode atualizar suas tabelas dinâmicas usando macros e botões no Excel. Esta classe é toda sobre como você pode adicionar essas técnicas avançadas às tabelas dinâmicas para tornar suas tabelas dinâmicas mais interativas, mas também para ajudá-lo como analista de dados a explorar as tendências e insights sobre seus dados. Depois de fazer essa aula, espero que você participe do projeto de classe, que envolve olhar para um grande conjunto de dados de atrito de funcionários. Seu objetivo é pegar esses dados, resumi-los nesses painéis muito fáceis de usar para seu público final e tornar os dados muito mais interativos e perspicazes para seu público final. Espero que você adicione seu projeto à galeria de projetos de classe. Estou ansioso para ver suas perguntas, comentários e, mais importante, os insights que você coleta ao fazer este projeto. 2. Como criar e configurar tabelas dinâmicas: Vamos criar algumas tabelas dinâmicas. Isso pode ser uma revisão para alguns de vocês, mas depois de criá-los, vou mostrar-lhe algumas técnicas mais avançadas para configurar sua tabela dinâmica e para visualizar seus dados em seu arquivo Excel. Temos um arquivo do Excel em branco aqui. Na etapa 1, queremos selecionar todos os dados na planilha de dados brutos, que é a última planilha em nosso arquivo, e vamos criar uma tabela dinâmica e inseri-lo aqui abaixo. Vou clicar em “raw_data”. Este é um monte de dados relacionados a informações de telecomunicações, que os clientes que se inscreveram em um serviço de telecomunicações com Internet e telefone. Depois de selecionar esses dados, eu posso clicar em “Inserir”, “Tabela Dinâmica”, e eu vou clicar em “Planilha existente” e clicar em torno de “A14" volta nesta planilha e, em seguida, clique em “Ok”. Agora temos a nossa tabela dinâmica em branco. Vamos adicionar várias dimensões a esta tabela dinâmica. Vamos primeiro começar adicionando cobranças totais, que é o valor total que os clientes estão pagando por seu telefone e serviço de Internet, de modo que as cobranças totais entram em valores. Você percebe por padrão que ele muda para contagem, então eu vou clicar com o botão direito do mouse nisso, ir para Configurações de campo e transformar isso em uma soma. Vou adicionar o método de pagamento à coluna, então é assim que o cliente pagou por sua Internet ou serviço telefônico, então arraste o pagamento para colunas , finalmente, colocando o gênero nas linhas. Agora temos uma tabela dinâmica agradável dessas dimensões diferentes. Uma última coisa que eu gostaria de fazer é transformar esses números em uma moeda, porque agora é apenas um número normal absoluto. Eu vou para Configurações de campo novamente, e depois clique em “Número”, altere isso para uma moeda. Agora, isso é em um formato que eu gosto de ver porque agora sei que isso é real $s. Deixe-me apenas adicionar mais uma coluna ou dimensão para as linhas apenas para torná-lo um pouco mais extenso, então lá vamos nós. Agora, para a etapa 3, vamos adicionar a coluna de contrato ao filtro de relatório. Vejamos aqui, este é o tipo de contrato que o cliente tem com seu serviço telefônico. Se eu clicar neste filtro de relatório, eu posso filtrar minha tabela dinâmica apenas para um determinado tipo de contrato dependendo dos dados que eu quero ver. Esse é um recurso muito bom e útil para adicionar às suas tabelas dinâmicas. Agora, se você olhar para a etapa 4, vamos clicar duas vezes em uma célula em uma tabela dinâmica para encontrar os dados subjacentes por trás desse ponto de dados específico. Por exemplo, se eu quiser olhar para pagamentos com cartão de crédito que são feitos por nossos clientes que são do sexo feminino e não têm qualquer dependência, eu posso clicar duas vezes sobre esse valor “$654.000". Se eu clicar duas vezes nesse valor, você notará que o resultado são todas as linhas subjacentes do meu conjunto de dados brutos que compõem esse valor, você possa fazer drill-down nesse valor específico. Isso realmente ajuda com a análise exploratória de dados, que você possa encontrar os verdadeiros drivers para seus dados. Vou excluir esta planilha por enquanto. Finalmente, um recurso que eu gosto de fazer às vezes é em vez de criar várias tabelas dinâmicas onde eu quero ver um filtro para apenas mês para mês e, em seguida, outro para um ano. Às vezes eu separo estes em três planilhas separadas, se eu tiver três filtros diferentes bem aqui. O que eu posso fazer é clicar na “Tabela Dinâmica”, clique em “Análise de Tabela Dinâmica”, em seguida, clique neste menu suspenso “Opções”, e clique em “Mostrar páginas de filtro de relatório”. Se eu clicar em “Mostrar páginas de filtro de relatório”, ela vai me dar esta opção para criar apenas páginas fora de contratos, que é o filtro de relatório que eu adicionei aqui. Eu clico em “Contrato”, aperto “Ok”. Você verá o que surge são três planilhas individuais onde cada uma dessas três tabelas dinâmicas é filtrada para baixo para o contrato. Este é dois anos, este é um ano, e este é um mês a mês. Mais importante ainda, ele também renomeia as planilhas, então eu não tenho que entrar e renomear as planilhas por conta própria. Essa é uma maneira de ser mais produtivo com a criação de várias Tabelas Dinâmicas com base em um filtro definido no filtro de relatório. Algumas dicas sobre como olhar para tabelas dinâmicas. Eu gosto de olhar para as minhas tabelas dinâmicas no layout de tabela dinâmica clássico. A maneira como você pode fazer isso vai para Opções aqui. Na guia Exibição, você pode clicar em “Layout de tabela dinâmica clássica”, clique em “Ok”. A razão pela qual eu faço isso é porque, em seguida, eu posso ver um cabeçalho de coluna para cada uma das propriedades em minhas linhas e em minhas colunas, então ele apenas torna mais fácil saber que tipo de dados eu estou olhando para. Olhe para isso, compare com isso, o que, na minha opinião, não é tão fácil ver como meus dados se parecem. Uma última dica para chamar é a métrica que você arrasta para sua caixa de valores aqui, é provavelmente a parte mais importante de sua tabela dinâmica para obter direito. Esta é geralmente a moeda, a contagem de clientes, alguma métrica específica que você deseja calcular dentro de sua tabela dinâmica. Neste caso, para nós, vai ser cobranças totais, que é o valor total que nossos clientes estão pagando por telefone e serviço de Internet. Nesta lição, criamos uma tabela dinâmica e adicionamos um monte de campos para a tabela dinâmica para olhar para nossos dados, especificamente, neste caso, dados de telecomunicações para nossos clientes. Na próxima lição, vamos começar a filtrar, classificar e fatiar nossos dados, para que possamos analisar melhor as tendências em nosso conjunto de dados. 3. Filtrando, classificando e fatiando: Nesta lição, vamos falar sobre como você pode filtrar e classificar seus dados dentro de uma tabela dinâmica por diferentes dimensões. Também falaremos sobre como você pode adicionar segmentações à sua tabela dinâmica para filtrar sua tabela de uma forma muito interativa para que seja mais fácil de usar para seus companheiros de equipe e para seus colegas. Temos nossa tabela dinâmica aqui e é configurado com algumas dimensões já nas colunas e linhas. Vamos começar com a Etapa 1 e adicionar posse para as linhas como um filtro de relatório adicional para a tabela dinâmica. Vou primeiro clicar em Tenure, arrastar isso para linhas. Você pode ver isso é adicionado nas linhas aqui. Vamos adicionar um relatório adicional ao nosso filtro. Talvez possamos arrastar o PhoneService. Agora temos dois desses filtros, que temos em nossa tabela dinâmica. Esta é uma tabela dinâmica bastante longa agora. Agora eu vou verificar novamente se a cobrança total para um determinado método de pagamento está correspondendo com o que vemos nesta tabela dinâmica, basta verificar novamente. Vamos dar uma olhada no método de pagamento transferência bancária aqui com pagamento por mulheres sem parceiros. Se eu clicar duas vezes lá e rolar para a direita do meu conjunto de dados, verei minha coluna de encargos totais. Se eu resumir esses números muito rápido, são $1.245. Eu também posso adicionar um número de soma aqui, apenas para ter certeza 1245. Isso coincide com o que vemos nesta célula aqui mesmo, o que é bom para nós porque sabemos que a nossa tabela dinâmica é feito corretamente. Mas isso é apenas para enfatizar ainda mais o ponto que você pode sempre verificar os números na tabela dinâmica para ver o que os dados subjacentes é para um determinado número na tabela dinâmica. Vamos excluir essa planilha por enquanto. Vou remover parceiro agora da tabela dinâmica e adicionar a partir da etapa 4, adicionar no serviço de Internet. Vamos arrastar o serviço de Internet. Vou colocar isso bem entre Gênero e Posse. Vamos realmente pular de volta para este passo aqui, Passo 3. Queremos classificar por encargos totais decrescentes e filtrar para os únicos 10 valores principais. Você pode realmente classificar por duas coisas diferentes, pelos valores ao longo das colunas e valores ao longo das linhas. Eu só vou classificar pelo total de encargos por enquanto. Clique aqui, a Tenure, e depois classifique pela soma do total de encargos, e clique em “decrescente”. Você verá que agora a posse não está classificada, mas sim o total de encargos ao longo do lado direito do meu total geral está agora ordenado. Agora, talvez eu só queira ver as 10 principais acusações para mulheres com serviço de internet, posse, assim por diante e assim por diante. Outra maneira que eu posso filtrar rapidamente minha tabela dinâmica é clicando neste “filtro por valor”, top 10. Digamos que eu queira ver os 10 melhores itens aqui. Agora minha tabela dinâmica é ainda mais classificada e filtrada para mostrar apenas os dez principais encargos totais para cada uma dessas categorias que eu configurou na minha tabela dinâmica. Você pode brincar com esses filtros e classificações diferentes para mostrar apenas os dados que importam para você e para seu público. Vou desfazer alguns desses filtros por enquanto. Vamos para o Passo 5. Vamos adicionar uma segmentação agora na coluna Contrato, e vamos permitir várias seleções na segmentação. Se eu clicar na tabela dinâmica, Análise de tabela dinâmica, Inserir Slicer. Ele vai me perguntar em qual coluna eu quero adicionar uma segmentação. Vou clicar em Contrato. Está bem aqui em baixo. Aperte “Ok”. Você vai ter este pequeno menu suspenso aqui que você pode formatar ao seu gosto. O que é realmente interessante agora é se eu clicar em, digamos, deixe-me apenas estreitar essas colunas um pouco. Se eu clicar em “Mês-a-mês”, você notará que não só essa mudança de filtro, mas também filtra toda a minha tabela dinâmica também. Se você clicar em um ano, todos os dados serão alterados. Se eu quiser clicar em vários valores de cada vez, eu só tenho que clicar nessas caixas de seleção, e então eu posso clicar em e selecionar várias maneiras de filtrar meu conjunto de dados usando este segmentação. A segmentação é uma maneira muito mais interativa de filtrar dados vez de ter que clicar nessa lista suspensa e selecionar essas caixas de seleção. Essa é apenas uma maneira melhor de seus colegas filtrarem os dados que desejam ver em um painel ou relatório. Agora, eu estou indo posse desta tabela dinâmica, e agora temos uma segmentação simples que me permite cortar e dados este conjunto de dados. Se eu copiar esta tabela dinâmica e, em seguida, colocar aqui e apenas fazer uma pasta, você percebe que se eu clicar neste cortador, ele ainda filtra esta tabela dinâmica aqui em baixo. Eu quero talvez desconectar esta tabela dinâmica do meu slicer. Como posso fazer isso? Ao clicar com o botão direito do mouse na segmentação e ir para conexões de relatório, você verá que existem duas Tabelas Dinâmicas, essas duas que estão conectadas a essa segmentação. Se eu apenas desmarcar esta tabela dinâmica, que é tabela dinâmica 8, esta segmentação agora não terá impacto. Bem, assim como a tabela dinâmica 8 foi este, não vai impactar esse cortador. Você sempre pode ver o nome de uma tabela dinâmica clicando na tabela dinâmica, clicando em Tabela Dinâmica Analisar, e, em seguida, você vai ver o nome aqui no canto superior esquerdo. Finalmente, e se você quiser bloquear seu arquivo do Excel, mas permitir que seus colegas possam usar as segmentações? Esta é uma maneira muito comum se você não quer que seus companheiros de equipe se envolvam e baguncem seus dados ou baguncem a estrutura do seu arquivo Excel. O que você pode fazer é clicar na tabela dinâmica, clique com o botão direito sobre ele e vá para configurações de segmentação, desculpe, vamos clicar com o botão direito do mouse e, em seguida, vá para, vamos trazer este menu aqui. Tamanho e Propriedades, acredito que seja. Clique nas propriedades e desmarque bloqueado. Só algumas outras coisas aqui. Você também pode reposicionar sua tabela dinâmica aqui e adicionar colunas adicionais se você quiser para que ele parece mais fácil de usar. Às vezes você tem muitas propriedades diferentes em seu cortador, que você pode querer organizar um pouco melhor em seu cortador. Você pode desmarcar trancado aqui. Em seguida, em sua guia revisão, clique em Revisar, Proteger folha, você deseja permitir que os usuários selecionem células bloqueadas, vamos desmarcar isso, mas também usar gráfico de tabela dinâmica. Desmarque a seleção de células bloqueadas, marque o uso de relatórios de tabela dinâmica e, em seguida, clique em “Ok”. Agora, esta planilha está bloqueada, não consigo pegar nenhuma das células. Mas eu ainda posso usar o cortador, que é importante para sua equipe e seus colegas para ser capaz de dois ainda interagir com sua tabela dinâmica para ver os dados que eles querem ver. Fizemos alguma filtragem rápida e classificação em nossa tabela dinâmica, mas também adicionamos uma segmentação para tornar a nossa tabela dinâmica mais interativo para os nossos colegas. Nós também falamos sobre como você pode bloquear seus arquivos do Excel para que apenas as segmentações de tabela dinâmica disponíveis para usar para olhar e analisar os dados. Na próxima lição, vamos falar sobre como você pode adicionar campos calculados à sua tabela dinâmica para criar novas métricas que não estão disponíveis no seu conjunto de dados atual. 4. Adicionando campos calculados: Nesta lição, vamos falar tudo sobre campos calculados, que são fórmulas que estão constantemente em execução em suas tabelas dinâmicas como você está movendo dimensões em torno de sua tabela dinâmica. Temos uma tabela dinâmica aqui com base em nossos dados de telecomunicações do cliente. Temos método de pagamento, sexo nas linhas, serviço telefônico nas colunas, contrato no filtro e encargos totais nos valores. E se quisermos saber qual é a nossa taxa mensal média para todos os nossos clientes, não importa como estruturamos a nossa tabela dinâmica. Este é o lugar onde faríamos um campo calculado. Posso clicar em Tabela Dinâmica Analisar na faixa de opções. Clique em campos, itens e conjuntos, clique no campo calculado. A forma como podemos calcular a cobrança mensal é tomar as cobranças totais divididas por posse. Normalmente, quando você tem um campo calculado, você pode querer adicionar algo antes do nome dos campos, algo como cálculo ou até mesmo algo como uma estrela. Eu gosto de usar Calc e vamos chamar esta cobrança mensal. A fórmula que queremos dizer ao Excel para basicamente pegar o total de encargos, que é o total de encargos que nossos clientes estão nos pagando, e dividindo-o pela posse. Assim que tivermos esta fórmula em nosso campo CalcMonthlyCharge, eu posso acertar bem. Lá você pode ver o Excel adiciona automaticamente esta coluna de cobrança mensal para a minha tabela dinâmica e eu posso ver rapidamente o que a cobrança mensal média é para o meu conjunto de dados. Agora, muitas pessoas se perguntam por que tivemos que fazer esse campo calculado, por que não poderíamos fazer isso em nosso conjunto de dados. Poderíamos ir para nossa última guia, que são nossos dados brutos e você poderia, em teoria, adicionar uma coluna aqui que diz cobrança mensal média e você poderia pegar encargos totais iguais divididos pela posse, que é bem aqui e em seguida, arraste isso do lado negativo. Agora você tem a taxa média mensal no nível de linha para cada um de nossos clientes. Se eu voltar para a minha tabela dinâmica, posso alterar a fonte de dados para incluir essa coluna extra de dados que eu adicionei. Antes de ser na coluna T, eu só vou mudar isso para a letra U, bater bem. Você verá que há agora uma coluna de cobrança mensal média, que eu calculei manualmente no back-end. Mas você percebe que quando eu adicionar à minha tabela dinâmica, ele não coincide com o que está no meu campo calculado CalcMonthlyCharge. Isso ocorre porque não importa como você altere essa configuração, você pode mudar isso para uma média, ele realmente não corresponde com o que esse valor realmente é, e este é um conceito importante para entender porque o se aplica ao cálculo. Quando você adiciona a cobrança mensal média, que é a coluna que adicionamos aqui em nosso conjunto de dados no final, ela não corresponde ao que vemos em nosso campo calculado porque o campo calculado calcula no nível da linha, enquanto que quando você toma isso, você está apenas tomando uma média do cálculo. Esta é a maneira mais precisa de analisar a cobrança mensal média do nosso conjunto de dados. Vou arrastar isto para fora por enquanto e voltar a recolher estes comunicadores um pouco. É importante quando você faz campos calculados para fazê-los fora das métricas brutas em vez de fora das colunas que contêm fórmulas dentro do conjunto de dados bruto. Essa é uma das principais medidas para o segundo passo. Passo três, vamos criar um item calculado, que é diferente de um campo calculado combinando essas três categorias diferentes, transferência bancária, cheque eletrônico e cheque enviado. Vamos combiná-los em um grupo e vamos chamá-los de todos os cheques. Na verdade, nós vamos apenas combinar cheque enviado e cheque eletrônico em um grupo chamado all cheques porque queremos consolidar nosso relatório um pouco. Posso clicar em Tabela Dinâmica Analisar. Você tem que se certificar de que você está clicando dentro de um de seus campos. Vá para o campo, itens e conjuntos, item calculado, e nós vamos chamar isso, o nome de todos verificar. A fórmula é simplesmente vai ser verificação eletrônica, inserir item, além de verificação de correio, inserir item, bater bem. Você verá que eu tenho este adicional todos cheque calculado item que é um resumo das minhas propriedades de verificação de correio e verificação eletrônica. Nesse caso, eu provavelmente gostaria de desmarcar eletrônico e enviado porque meu grupo de verificação todos já contém esses dois valores. Vamos aprofundar em como podemos fazer alguns campos calculados mais adiantados. Vamos criar um campo calculado chamado CalcServiceFee para qualquer cliente que tenha uma cobrança mensal média de alguns dólares ou mais, vamos cobrar dois por cento para apenas gerenciar contas altas, vamos chamar isso. Eu vou voltar para tabela dinâmica, analisar, campos e conjuntos, campo calculado, e vamos chamar este CalcServiceFee. O que é interessante aqui é que você pode realmente adicionar instruções if aos seus campos calculados. Eu posso escrever se, parênteses esquerdos, a cobrança mensal, que é, se você se lembra, este é outro campo calculado que calculamos muito começando. Se a cobrança mensal calculada for maior que 50, então vamos pegar o CalcMonthlyCharge e multiplicá-lo por dois por cento. Se não for maior que 50, vamos apenas devolver um zero e depois fechar a nossa declaração se. Uma coisa importante a observar aqui é que você pode usar instruções if e você pode criar campos calculados fora de outros campos calculados em sua tabela dinâmica. Está tudo bem? Você pode ver aqui, é uma quantidade muito pequena, mas deixe-me apenas adicionar algumas casas decimais a este número. Vamos adicionar duas casas decimais. Você pode ver aqui que esses números são basicamente dois por cento da carga mensal média. Mas o importante é que você pode adicionar estas instruções if e campos calculados em cima de outros campos calculados. Vou arrastar a taxa de serviço de cálculo e as cobranças totais para fora por enquanto. Vamos fazer outro mais complicado que é criar um campo calculado chamado taxa de transação de cálculo apenas para clientes que pagam com cartão de crédito e isso vai ser equivalente a 2,9 por cento multiplicado pela cobrança mensal mais $0,30, que é uma cobrança bastante comum que você adiciona para clientes que pagam com um cartão de crédito. Vou clicar em Tabela Dinâmica Analisar, campos, itens, conjuntos, campo calculado. Vamos chamar isso de CalcTransactionFee e isso é se o método de pagamento, vamos olhar para isso aqui, inserir. Se este método de pagamento for igual ao cartão de crédito automático. Vamos nos certificar de acertar os casos. Em seguida, queremos pegar o CalcMonthlyCharge ou outro Campo Calculado, multiplicar isso por 2,9 por cento e também adicionar no cálculo de US $0,30 para cobranças de cartão de crédito, zero para fechar o caso falso da nossa declaração if. Vamos ver o que acontece se eu bater bem. Vais ver que te dá um monte de zeros. A razão para isso é que campos calculados, uma cópia sobre esses campos calculados mais avançados é que você não pode usar um cabeçalho de coluna ou um cabeçalho de campo, neste caso, PaymentMethod, dentro de sua instrução if em um campo calculado. A solução alternativa para este cenário é voltar aos seus dados brutos, e vamos adicionar uma coluna auxiliar chamada cartão de crédito? Vamos deletar isso relacionado por enquanto. Basicamente vamos adicionar um um ou um zero a esta coluna se o PaymentMethod for um cartão de crédito. Escreveremos iguais se a célula R2 for igual ao cartão de crédito automático. Dê um, caso contrário, dê-me um zero. Vamos arrastar esta fórmula para baixo. Agora isso é simplesmente nos dizer se o cliente pagou ou não com um cartão de crédito e isso é denotado por um 1 ou um zero. Agora, se eu voltar para a minha tabela dinâmica onde eu tenho a minha taxa de transação, deixe-me apenas certificar-se de que isso está fazendo referência ao conjunto de dados certo. Temos que ter certeza que vai até a coluna U, que é certo, acerte bem. Eu só vou atualizar esta vez só para ter certeza que eu tenho os dados mais atualizados. Agora eu vou voltar para o meu campo calculado, clique no menu suspenso aqui, taxa de transação de cálculo, e em vez de PaymentMethod é igual ao cartão de crédito, eu vou dizer excluir esta parte do if condicional declaração e dizer cartão de crédito? essa foi a nova coluna que adicionamos. Se isso for maior que zero, então faremos a cobrança mensal de cálculo, que é o nosso campo calculado para calcular múltiplas cobranças, multiplicado por 0,029 ou 2,9 por cento e adicionar 0,30 dólares, acerte bem. Agora você pode ver que a taxa de transação só mudou ligeiramente para nossas transações com cartão de crédito. Vou apenas adicionar algumas casas decimais a este número para que você possa ver mais detalhadamente o que isso parece. Você pode ver que nós só temos taxas de transação para o pagamento com cartão de crédito porque isso foi o que mostramos em nosso último conjunto de dados. Para transferência bancária e todos os cheques, ela não corresponde à condição if, portanto, só vemos as taxas de transação para este campo calculado. Falamos sobre como você pode adicionar um campos calculados para suas tabelas dinâmicas e também fazer algumas coisas mais avançadas para suas fórmulas de tabela dinâmica adicionando if instruções com outros campos calculados neles. Na próxima lição, vamos falar sobre as melhores maneiras de visualizar dados de uma tabela dinâmica, que está usando gráficos dinâmicos. 5. Criando gráficos dinâmicos: Nesta lição, falaremos sobre como você pode criar Gráficos Dinâmicos, que são tão flexíveis quanto Tabelas Dinâmicas para visualizar seus dados. Também discutiremos como você pode salvar modelos que correspondam às diretrizes de marca da sua empresa. Olhando para nossos dados de telecomunicações do cliente, queremos criar um gráfico dinâmico básico fora desta tabela dinâmica. Posso clicar em “Tabela Dinâmica Analisar” e, em seguida, “PivotCChart” aqui mesmo. Este é um gráfico dinâmico bastante básico fora de sua tabela dinâmica. Não parece super chique. Vamos apenas adicionar alguma formatação básica a isso. Talvez possamos mudar a cor da barra para uma cor diferente. Vamos mudar isso para uma cor de preenchimento diferente aqui. Vamos mudar este para talvez um bom verde, e talvez possamos tornar o fundo disso um pouco mais interessante de ver. Posso ir ao Format Axis. Na verdade, podemos deixar assim. Vamos deixar assim por enquanto. Nós também queremos ser capazes de filtrar esta tabela com base nesta coluna de contrato aqui que está em nosso filtro de relatório. Vamos adicionar uma segmentação à nossa tabela dinâmica como fizemos em uma lição anterior. Gráfico Dinâmico e Análise Dinâmica, Inserir Slicer. Vamos usar a coluna do contrato de novo. Aperte “Ok”. Vamos ampliar só um pouco aqui. Você notará que quando eu clicar em uma dessas opções, não só ele altera a tabela dinâmica, mas também altera o gráfico dinâmico. Esta é uma maneira que você pode conectar todos os seus dados, sua tabela dinâmica, e seus gráficos juntos em um só lugar para que tudo é super dinâmico. O importante aqui a saber é que você pode editar e adicionar imagens aos seus Gráficos Dinâmicos. Mas, o mais importante, você pode transformar isso em um modelo. Se eu clicar com o botão direito do mouse neste gráfico e ir para Salvar como modelo, seus modelos serão adicionados a essa pasta de modelos de gráfico em sua área de trabalho. Vou chamar isso de MyChartFormat. Aperte “Salvar”. Agora podemos reutilizar esse gráfico para uso como um modelo para um gráfico futuro que criamos. Vamos criar um novo gráfico dinâmico. Vá para Tabela Dinâmica Analisar, Gráfico Dinâmico. Temos este mesmo gráfico chato que obtemos por padrão. Mas agora vamos clicar com o botão direito do mouse no gráfico, ir para “Alterar tipo de gráfico” e, em seguida, ir para “Modelos” Você pode ver “MyChartFormat” que eu salvei na etapa anterior aparece aqui. Quando eu clico nisso, ele muda automaticamente o formato do gráfico para corresponder ao modelo que eu tinha configurado aqui. Você pode gastar muito tempo obtendo as diretrizes da marca e as cores da marca, adicionar logotipos da empresa parecem perfeitamente neste gráfico e, em seguida, reutilizar esse modelo em gráficos futuros como este aqui. Uma última coisa que eu gostaria de fazer com meus gráficos é tornar o título do gráfico muito dinâmico. Vamos clicar em “Design”, “Adicionar elemento do gráfico”, “Título do gráfico”, “Acima do gráfico” do gráfico. Agora, por padrão, ele diz título do gráfico, e normalmente, você provavelmente apenas digitaria manualmente algo neste título. Mas se você ir para a barra de endereço de fórmula, escrever iguais, e, em seguida, clique em “Célula A1" , neste caso, pressione “Enter”, você vai notar que o gráfico de tabela dinâmica vai mudar dependendo do que está nesta célula aqui. Eu posso dizer o meu gráfico legal e isso vai mudar automaticamente. É uma maneira muito mais dinâmica de editar os títulos de seus gráficos dinâmicos ou gráficos comuns à medida que você os cria para relatórios e painéis. Você não precisa clicar duas vezes nisso e alterar o título do gráfico todas as vezes. Acabamos de analisar como você pode adicionar Gráficos Dinâmicos às suas Tabelas Dinâmicas para tornar seus dados realmente visualmente agradáveis para seus colegas e para suas equipes. Agora vamos começar a usar a fórmula GETPIVOTDATA no Excel para extrair dados de suas tabelas dinâmicas para serem usados para relatórios e painéis. 6. GetPivotData para relatórios personalizados: Você aprenderá a usar a fórmula GETPIVOTDATA no Excel para obter dados da tabela dinâmica. Isso é realmente útil, pois sua tabela dinâmica está mudando e você deseja aplicar alguma formatação personalizada aos valores retirados da tabela dinâmica. Temos esta tabela dinâmica aqui com método de pagamento, StreamingMovies, TotalCharges, duração média e apenas para verificar novamente aqui, você pode ver aqui que a posse média não é uma soma, porque queremos encontrar a média de comprimento de todos os nossos clientes para esta empresa de telecomunicações para estas métricas fornecidas. Vamos para o Passo 1. Vamos apenas fazer referência a uma célula de tabela dinâmica na célula G15. Se eu ampliar aqui um pouco e escrever igual a uma célula, digamos, eu vou para 1,145, você percebe o que essa função GETPIVOTDATA me dá. O primeiro parâmetro para esta função é a soma da métrica em questão. Neste caso, é cobranças totais, quanto estavam sendo pagos total do nosso cliente. A12 é simplesmente a localização da nossa tabela dinâmica. Em seguida, os seguintes parâmetros são onde você costumava em uma fórmula SUMIFS onde você tem critérios diferentes e o intervalo de critérios reais. StreamingMovies é não, PaymentMethod é transferência bancária automática. É por isso que este 1.145 corresponde a este número. Soma do total de encargos onde é uma transferência bancária e o StreamingMovies é não. Nas células G18 e G19, eu quero que você encontre a soma do total de encargos e posse média, mas eu quero que você obtenha formulaicamente esses números da tabela dinâmica. Agora, nós íamos estar olhando também para clientes que pagam com um cartão de crédito e não têm serviço telefônico. Vamos escrever isto do zero. Vamos escrever igual a GETPIVOTDATA. Queremos obter a referência dinâmica da soma das cargas totais, que é esta célula aqui. Já escrevi isso para você. A tabela dinâmica é sim, na célula A12 e podemos fazer isso uma referência absoluta e temos isso olhando para a nossa função GETPIVOTDATA anterior. Queremos ver o método de pagamento para aqueles que pagam com cartão de crédito. É, creio eu, cartão de crédito automático. Então também para pessoas que não tinham streaming de filmes. Vamos escrever StreamingMovies é a coluna e a resposta é não. Você vai receber um erro. A razão é porque quando você faz referência ao F17, há várias cobranças na métrica que queremos somar, você tem que realmente adicionar um e comercial e duas aspas duplas depois dele para ter certeza que a função GETPIVOTDATA sabe como para fazer referência aos valores corretos. Se eu arrastar isso para baixo, você também verá que essa média de posse é 36, que corresponde com aqueles que pagam com um cartão de crédito, e não tiveram streaming de filmes, e tiveram um mandato médio de 36. Uma última coisa sobre a função GETPIVOTDATA em termos de opções métricas gerais é certificar-se de que você tem a função GETPIVOTDATA ativada em primeiro lugar, que você pode encontrar nas opções apenas certifique-se A função Gerar GETPIVOTDATA está ativada. Agora, vamos realmente construir um painel começando no H34 usando referências de célula para o tipo de pagamento, quer o cliente transmita filmes e a posse. Vamos começar aqui. Nós basicamente queremos preencher esta tabela em nosso arquivo Excel. A razão pela qual usaríamos essa tabela em vez de usar apenas a tabela dinâmica diretamente é porque podemos formatar melhor isso para nossas necessidades versus formatação em uma tabela dinâmica, que pode ser um pouco complicado. Vamos começar a escrever a função GETPIVOTDATA nesta célula aqui. Vamos apenas reutilizar esta função aqui porque já temos a função GETPIVOTDATA configurada para nós. Vou copiar isso, inserir aqui. Soma do total de encargos, queremos obter isso para StreamingMovies. Em vez de codificar esta nota aqui, podemos realmente referenciar esta coluna aqui, que eu codifiquei com sim e não como uma coluna auxiliar. Posso bloquear a referência da coluna. Então, para PaymentMethod, eu vou apenas selecionar esta célula aqui, que contém o PaymentMethod e bloquear a coluna, mas você também percebe que eu também preciso puxar no mandato. Isso é quebrado por esses números. Vamos realmente arrastar a coluna de posse para esta tabela dinâmica. Posse, vamos colocar isso em fileiras. Vamos remover o mandato médio por enquanto, não precisamos disso. Agora, temos o mandato quebrado por PaymentMethod e StreamingMovies para cobranças totais. Vamos voltar à nossa função de tabela dinâmica. StreamingMovies é sim. Isto é por algum motivo foi colocado entre aspas. Vamos fazer isso para G34 como referência e vamos bloquear esta coluna. Então, finalmente, vamos escrever “posse”, esse parâmetro vai ser esse número, que é apenas o número de meses de bloqueio de posse na linha. Se eu pressionar “Enter”, agora eu posso arrastar isso de todo o caminho para a direita e também arrastá-lo para baixo. Posso ver o total de encargos para esses métodos de pagamento e para esses tipos de streaming. Se eu arrastar copiar esta fórmula aqui para baixo, também vai funcionar corretamente porque eu estou referenciando o PaymentMethod e, neste caso, o StreamingMovies vai ser não. Eu posso arrastar isso para baixo e também para a direita. Muito fácil. É assim que você pode criar um painel muito personalizado com toda a formatação de cores que você precisa, retirando dados da tabela dinâmica, mesmo quando sua tabela dinâmica está mudando a estrutura. O importante a observar aqui é que quaisquer campos que você puxe ou faça referência à função GETPIVOTDATA, você tem que ter esses campos na própria tabela dinâmica, é por isso que eu tive que colocar posse nas linhas. A etapa final, vamos referenciar dinamicamente tabelas dinâmicas em outras planilhas usando a função INDIRETO. Você verá como você tem que usar aspas simples para usar a função INDIRETO. Em lições anteriores, lembra como nós dividimos esses filtros, esses relatórios em mês a mês, um ano e dois anos? Estas são apenas três tabelas dinâmicas diferentes, mas filtradas por contrato. Vamos voltar a esta tabela e queremos adicionar referências a esses valores para cobranças totais por sexo para esses filtros de relatório específicos. Se eu escrever iguais, quero ficar masculino mês a mês. Vamos para a conta mês a mês. O total para macho vai ser aqui. Se eu apertar “Enter”, vamos dar uma olhada na fórmula e ver como podemos tornar isso mais dinâmico. Podemos alterar esse valor, em vez de ser codificado, alterar isso para macho e fazer este cabeçalho coluna e bloquear na linha. Como mudamos este mês a mês para podermos referenciar dinamicamente estas células aqui? Bem, aqui é onde você usaria a função INDIRETO. Vou apagar o mês a mês aqui por enquanto. Eu vou escrever indiretos, parênteses esquerdos e eu vou escrever aspas duplas, aspas simples, aspas duplas, comercial e, seguida, referência mês a mês, esta célula aqui e eu vou bloquear na coluna referência, colocar outra aspas duplas, aspas duplas, aspas simples, aspas duplas, bater outro e comercial, e então colocar este parque de exclamação A3 dentro de aspas duplas também. Acerte nossos parênteses. Este INDIRETO agora irá referenciar corretamente essas células como o trabalho que eu quero fazer referência. Agora, vocês podem ver que isso me dá o mesmo valor, mas se eu arrastar isso para baixo, este 1.958 agora faz referência à planilha de um ano para o sexo masculino, que é 1.958 e eu posso basicamente arrastar isso para o feminino também, preencher corretamente usando o Função GETPIVOTDATA, mas também esta pequena solução alternativa para fazer referência a outras planilhas dentro do meu arquivo usando a função INDIRETO. Falamos sobre diferentes maneiras de usar a função GETPIVOTDATA para extrair dados da tabela dinâmica em relatórios formatados mais personalizados que você pode apresentar à sua equipe e aos seus colegas. Na próxima lição, falaremos sobre maneiras de atualizar sua tabela dinâmica usando macros. 7. Atualizando tabelas dinâmicas com macros: Nesta lição, vamos usar macros para atualizar automaticamente diferentes layouts e exibições de suas Tabelas Dinâmicas. Temos uma tabela dinâmica conduzida fora do nosso conjunto de dados principal. Mas uma coisa que eu quero fazer de forma diferente com esta tabela dinâmica, é ir para raw_data e transformar isso, nosso conjunto de dados brutos, em uma tabela do Excel chamada TelecomData. Basta selecionar tudo da célula A1 até o final. Temos cerca de 6.000 linhas de dados. Vá para “Inserir”, “Tabela” aperte “OK” com os cabeçalhos e vamos chamar isso de TelecomData. Agora, quando essa tabela muda, quando adicionamos novas linhas ou novas colunas, isso atualizará automaticamente nossa fonte. É realmente importante quando agora alteramos a fonte desta tabela dinâmica para referenciar essa nova tabela. Se eu for para “Alterar fonte” em vez de local, você pode simplesmente dizer TelecomData. Se eu atualizar isso, tudo funcionará como esperado. Nenhum dado foi alterado, então não veremos nenhuma alteração agora. Eu quero que você agora apenas adicione rapidamente uma nova função de dados a esta tabela, nossos dados brutos. Este é clique, desça aqui, aperte “Enter”. Vamos apenas dar-nos alguns valores aleatórios. Você notou como obtemos novos dados adicionados. Mas vamos para as cobranças totais. Vamos apenas adicionar um número realmente grande, como um milhão. Se eu voltar para a minha tabela dinâmica, você pode clicar com o botão direito do mouse em “Atualizar” ou apenas clicar em “Tabela Dinâmica Analisar” e clicar em “Atualizar”. Você verá este novo total em branco adicionado à parte inferior porque nossa tabela TelecomData mudou e , portanto, nossos dados brutos mudaram e, portanto, nossa tabela dinâmica mudou. Vamos excluir essa linha por enquanto, porque não queremos estragar nosso conjunto de dados. Excluir linha da nossa tabela. Vamos voltar para este set. Agora, vamos ativar a guia Desenvolvedor em nosso arquivo Excel. Isso vai ser diferente para usuários de PC e usuários de Mac. Mas no Mac, você pode clicar em “Excel”, “Preferências”, “ Faixa de Opções” e “Barra de ferramentas” e, em seguida, na parte inferior, você deve ver no lado direito, “Desenvolvedor”, pressione “Salvar”. Agora você verá a guia Desenvolvedor adicionada à sua Faixa de Opções. Eu quero que você clique em “Desenvolvedor” e, em seguida, clique em “Macro”. Sinto muito, eu quero que você em vez de clicar em “Gravar Macro” e chamar esta Macro de “PaymentOnly”. O que eu quero que você faça agora é apertar “OK” e então a Macro está gravando agora. O que uma macro é essencialmente estamos gravando um monte de passos para dizer ao nosso arquivo Excel para fazer isso toda vez que executar a macro. Agora está gravando. Eu quero que você vá clique em “Tabela Dinâmica”, “Análise de Tabela Dinâmica”. Vamos dizer “Limpar tudo”. Em seguida, clique também em “Atualizar”. Isso vai atualizar nossos dados. Agora vamos configurar a nossa tabela dinâmica para mostrar o layout que queremos. Vamos pegar “Total Encargos”, adicionar isso aos nossos “Valores”. Vamos nos certificar que isso é uma soma de nossos valores. Além disso, apenas certifique-se, as Configurações de campo temos o formato de número que queremos. Aperte “OK”. Agora vamos clicar em “Método de Pagamento”. Coloque isso nas fileiras. Nós apenas aumentamos o tamanho da fonte um pouco para que possamos ver esses dados um pouco melhor. Eu também posso ampliar aqui. Agora só temos as tabelas dinâmicas configurar o método de pagamento. Agora posso voltar para parar de gravar. Você não verá nada acontecer a partir daqui, mas vamos repetir os passos quatro e cinco com mais duas macros para o sexo da internet ou o interne. Serviços T em linhas. Gênero e colunas e outra Macro chamada ContractTenure onde a posse está nas linhas e contrato está nas colunas. Vamos seguir isto de novo. Gravar macro. Vamos gravar um agora, chamado “Gênero da Internet”. Aperte “OK”. Os passos são, vá para “Limpar tudo”, “Atualizar” e agora vamos adicionar Gênero nas colunas e, em seguida, Internet Service. Vamos onde está o serviço de Internet nas linhas. Em seguida, finalmente, Total de Encargos nos Valores. Então nós apenas nos certificamos de que isso também é uma soma. Em seguida, aperte “Parar” na gravação. Em seguida, faça isso uma última vez para o contrato de posse. Quero mostrar-lhe uma coisa no final. Gravar macro. Estamos chamando este contrato de posse. Aperte “OK”. Clique em “Tabela Dinâmica”, “Analisar” e vamos clicar em “Limpar Tudo” novamente. Provavelmente muito acostumado com isso agora, “Refresh”. Vamos colocar o Total Encargos novamente nos Valores. Vamos fazer disto um Soma. Em seguida, queremos ter Tenure nas linhas, Contratos nas colunas. Vamos clicar em “Parar” na Macro. Está bem. Agora temos três macros diferentes e vamos atribuir cada uma dessas macros a um botão em nosso arquivo Excel. Agora você pode usar um botão como este, um desses botões padrão da guia Desenvolver. Podemos chamar isso assim Button1_click. Atribua isso à Posse do Contrato. Nós temos nossas três macros bem aqui quando eu cliquei em “Botão”, eu posso atribuir este para contrato de posse. Posso clicar com o botão direito do mouse nisso e renomear isso para Contrato de Posse. Mas eu gosto de usar o tipo regular de opções de desenho dentro do Excel. Eu só acho que você pode editar os botões um pouco melhor e adicionar mais cores, então parece um pouco mais divertido de usar. Vamos para “Inserir”, “Desenhar uma Forma” e fazer uma forma básica como esta. Podemos chamar isso apenas de pagamento. O que você pode fazer aqui é, você pode clicar com o botão direito do mouse neste botão e Atribuir uma macro. Vamos atribuir este ao botão Somente Pagamento. Então vamos apenas criar outra forma para a nossa última Macro. Vamos atribuir a isso uma Macro de Gênero da Internet. Basta escrever na Internet Gênero. Agora, se eu clicar em qualquer um desses botões, minha tabela dinâmica irá atualizar automaticamente para mostrar esse tipo específico de layout que eu quero. A razão pela qual você pode fazer isso é porque às vezes você quer ter uma maneira rápida de alterar o layout muito rápido sem que sua equipe e colegas tenham que arrastar e soltar colunas na tabela. A alternativa é que você pode ter tabelas dinâmicas construídas em planilhas individuais como este, mas isso pode ser um pouco difícil de percorrer. Dependendo de seus casos de uso em que você pensa sobre como você deseja melhorar a experiência do usuário e a interface do usuário. O importante é que você não pode desfazer os resultados de nossas macros, então certifique-se de acertar na primeira vez quando estiver gravando suas macros. Finalmente, se você quiser ver o código para suas macros, você pode ir para “Desenvolvedor”, “Visual Basic” e, em seguida, clicar em “Módulos” aqui e você verá o código de todas as macros que você escreveu neste arquivo. Você pode ver aqui este é apenas pagamento. Isto é o género da Internet, e isto é o contrato de posse. Isso está além do escopo desta classe, mas você também pode entrar em realmente editar o código do nosso código Visual Basic para suas macros. Mostramos como você pode usar macros para automatizar a atualização de suas Tabelas Dinâmicas e como você também pode editar o código para suas macros e atribuí-las a botões para facilitar a atualização de seus colegas e equipes Tabelas dinâmicas. 8. Considerações finais: Parabéns por fazer esta aula sobre técnicas avançadas de tabela dinâmica no Excel. Algumas das principais opções desta aula, aprendemos sobre como você pode usar tabelas dinâmicas e torná-las muito mais interativas para sua equipe e para seus colegas. Também falamos sobre como você pode adicionar campos calculados para fazer cálculos em execução em suas tabelas dinâmicas, até macros para atualizar automaticamente suas tabelas dinâmicas para usar. Outra coisa que eu quero chamar é que as técnicas que você aprendeu nesta classe de tabelas dinâmicas avançadas podem ser aplicadas a muitas outras plataformas de análise de dados, como o Google Sheets e o Tableau. Eu realmente espero que você pegue as habilidades que você aprende esta classe e as aplique ao projeto da classe, onde você estará construindo relatórios de tabela dinâmica e gráficos e, finalmente, um painel de controle com dados de atrito de funcionários, que você pode usar para apresentar a alguém internamente em sua empresa, e também quero que você tenha insights que você possa obter a partir da análise de dados da tabela dinâmica. Estou ansioso para ver o seu projeto na galeria do projeto para esta aula. Sinta-se livre para postar sua análise, fazer perguntas um ao outro, eu também estou feliz em responder qualquer pergunta que você tem. Boa sorte com os projetos.