DICAS Excel PRO Parte 5: tabelas dinâmicas

1. Introdução ao dicas de tabela dinâmica: Ei, todo mundo. Esta próxima seção do curso é sobre explorar e analisar dados com tabelas dinâmicas . Vou mostrar que você teve um grupo datas e valores. Defina suas próprias listas de classificação personalizadas e até reviva os dados de origem excluídos da caixa dinâmica. Temos muito para cobrir, então vamos começar. 2. Personalizando a lista de campos: Quero compartilhar uma tabela dinâmica. Dica profissional , pessoal. É uma dica muito básica. Uma estrela, mas muito útil, no entanto, pode falar sobre como usar algumas opções específicas da lista de campos da tabela dinâmica para ajudá-lo a se manter organizado. Agora, por padrão, a lista de campos da tabela dinâmica geralmente é empilhada, então você vê uma lista de campos na parte superior e suas áreas são dores na parte inferior, seus filtros, linhas, colunas e valores. Então, no topo dessa lista de sensação você verá um ícone de engrenagem que lhe dará um menu de opções. E assim é assim que o padrão geralmente se parece, o que para muitos casos será muito bom. Mas nos casos em que você tem um número muito grande, tabelas de origem como Se você estiver usando energia, gire em cima de um modelo de dados complexo ou vinculando a uma planilha com muitos, muitos, muitos campos ou colunas. Há algumas outras opções que podem ser úteis para você. número um está mostrando seus campos e áreas lado a lado, que é esta segunda opção aqui. O que eu gosto sobre isso é que ele dá a você toda a dor vertical que abriga seus campos então torna muito mais fácil pesquisar e percorrer essas colunas se você tiver um número muito grande deles, e então a segunda opção é classificando a para C em oposição à ordem da fonte de dados. Portanto, se você tiver suas colunas alinhadas de forma significativa na tabela de origem, você pode não se importar com essa opção, mas, caso contrário, classificar alfabeticamente pode ajudá-lo a acessar esses campos um pouco mais rapidamente. Casos de uso tão comuns novamente. Atualizando esse layout para mostrar todos os seus campos em um único problema vertical, especialmente para lidar com modelos de dados e classificar a maioria dos campos em ordem alfabética nos casos em que sua ordem de origem não é necessariamente significativa. Então vamos entrar em nossa pasta de trabalho de dicas profissionais se você quiser acompanhar e eu vou mostrar-lhe alguns exemplos muito rápidos de como isso se parece. Tudo bem, então ele está na pasta de trabalho de dicas profissionais na guia Índice para navegação rápida. Estamos na seção de dicas de tabela dinâmica cinza, e vou procurar a demonstração de opções de lista de campos novamente. Uma estrela. Demonstração muito básica. É como sair, e aqui temos uma tabela dinâmica contra o banco de dados de filmes I Am Devi. Estamos olhando para títulos de filmes, gêneros e classificações, juntamente com alguns valores como receita bruta e orçamento. Aqui está a minha lista de campos no lado direito da minha tela. Se você não vir sua lista de campos, você pode clicar com o botão direito na célula dinâmica e alternar ish hide ou show, geralmente o último item no menu. Ou você pode entrar em ferramentas de tabela dinâmica e este show pain aqui todo o caminho à direita. Bem, enquanto você ativa e desativa essa lista de campos. Agora, dentro dessa lista de campos, aqui está o ícone de engrenagem que permitirá que você personalize o layout e, por padrão, onde empilhar nossas áreas e campos. Aqui está o segundo item, que basicamente cria para Collins. Podemos arrastá-lo para nos dar um pouco mais de espaço. Agora temos todas as nossas colunas ou campos aqui nesta coluna da esquerda, e temos nossas quatro dores ou áreas à direita ou filtros, linhas, colunas e valores. Agora, se olharmos para os nossos campos, veremos que eles não são classificados em qualquer tipo de ordem alfabética, têm o título e data de lançamento cor linguagem, e neste caso isso é muito bom, porque eu colocar meus campos calculados em minhas medidas numéricas no final, que é um hábito que eu geralmente sigo. Mas se esse não for o caso, você pode voltar e dizer, Vamos classificar esses campos em um país de cor de elenco de orçamento tonto, etcetera, que pode tornar essa lista de campos um pouco mais fácil de navegar, especialmente se Você tem centenas de “vem” aqui. Você também tem uma barra de pesquisa, o que torna muito fácil detalhar e encontrar campos individuais dessa forma também. Então é realmente um simples é que você tem algumas outras opções aqui, como apenas mostrar os campos ou apenas mostrar as áreas. Eu, pessoalmente, não acho esses muito útil, então eu geralmente ficar com uma dessas primeiras 2 opções. Neste caso, vamos voltar para a nossa opção empilhada tipo de arrastá-lo de volta para fora. E lá você tem um campo de personalização ou tabela dinâmica. Ouça, layout 3. Largura de colunas automáticas: Tudo bem. Eu quero tirar alguns minutos e falar sobre como podemos impedir que nossas colunas de tabela dinâmica encaixem automaticamente lá com agora por padrão, cada campo e um pivô irá ajustar a coluna subjacente com automaticamente para acomodar o valor mais longo nesse campo. E normalmente isso é útil porque significa que todo o texto em nossos campos será visível por padrão. Mas pode ser realmente inconveniente se você estiver trabalhando com vários campos de texto muito longos que podem ter centenas de caracteres ou palavras. Então você pode acabar com algo parecido com isso aqui. Nós temos um campo de descrição com base em texto longo, e nós puxamos em uma linha rótulos, e como resultado, ela é esticada coluna B bem além da largura de nossa janela. Ela está fazendo com que seja muito difícil trabalhar com correção agora simples aqui para evitar que o dimensionamento re automático ou ajuste automático aconteça. Tudo o que temos que fazer é saltar para a nossa guia de opções ou botão de opções dentro de sua guia analisada, e vamos clicar ou desmarcar esta caixa que diz Auto ajustar larguras de coluna na atualização. E isso está na cidade de layout e formato da caixa de diálogo Opções de Tabela Dinâmica. E então, uma vez que você fizer isso, basicamente impedirá que a coluna B redimensione ou seja qual for a chamada neste campo viva enquanto você arrasta esse campo para seus rótulos de linha ou coluna. Então, uma outra dica aqui, você pode usar a opção de quebra de texto da sua cidade natal para criar algo parecido isso, onde você ainda é capaz de ver todo o texto nesse campo. Ele apenas meio que rola em uma nova rosa em vez de colocar todo aquele texto em uma única linha . Então, os casos de uso aqui, trabalhando com campos de texto como resultados de pesquisas ou publicações de mídia social caso de uso muito comum. Ou se você quiser apenas forçar a coluna da tabela dinâmica com para permanecer fixa, não importa como a saída da tabela foi alterada ou ajustada. Então vamos saltar para uma pasta de trabalho de dicas profissionais e eu vou mostrar-lhe como esta opção se parece. Tudo bem, então se você quiser acompanhar, vá em frente e abra sua pasta de trabalho do Excel Protect. Estou no índice e procuro a seção de dicas de tabela dinâmica cinza. Especificamente a coluna Auto Fit com demonstração e vá em frente e link direto para tentar. Agora o que temos aqui é um pivô simples. Os campos aqui, com base em dados de degustação de vinhos, temos informações sobre vinhos individuais e vinícolas, país de origem e província. Temos também os nomes do provador e este texto baseado descrição da degustação, bem como a sua classificação de pontos e um preço por garrafa de vinho. Agora, se pegarmos este longo campo de descrição baseado em texto e puxá-lo para nossos rótulos de função após o nome do provador, ver o que acontece aqui É esticado a coluna B caminho, fora, meio que além da janela. E isso tornou muito difícil e estranho trabalhar com, como você pode ver, apenas tentando até navegar pela vista aqui, o que é bastante irritante porque se encaixa na descrição mais longa em todo este campo. Então veja o que vou fazer aqui. Vou retirar a descrição, que vai encolher a coluna B de volta. Vou entrar em ferramentas de tabela dinâmica, analisar e aprofundar minhas opções aqui e dentro dessa guia de layout e formato. Esta caixa que diz ajuste automático das larguras da coluna na atualização é geralmente marcada por padrão. Tudo o que temos que fazer é desmarcar essa caixa pressione OK, e então veja o que acontece quando puxamos a descrição de volta. Lá vamos nós agora. Coluna B é uma coluna média com ele não esticá-lo para ajuste automático, e agora eu tenho algumas opções. Aiken Esticá-lo eu mesmo com que eu me sinto confortável com, mas note que ele ainda está cortando meu texto. Quem é uma descrição? O Perot. Uma atualização que muitas vezes gosto de fazer casos como este. É selecionar toda a coluna B cabeça para a minha cidade natal e ativar esta opção de texto de quebra na dor de alinhamento e que basicamente apenas estende ou envolve o texto para várias linhas nos casos em que ele teria sido cortado. E agora eu sou capaz de ver todo o texto nessas descrições e personalizar exatamente como olhos largos como coluna B dois b. Então, aqui você tem. Dica simples, mas eficaz. Impedindo que os campos da tabela dinâmica sejam ajustados automaticamente. Essas larguras de coluna 4. Esboços e layouts tabulares: Tudo bem, Esta próxima dica profissional é extremamente importante para quem trabalha com tabelas dinâmicas no Excel. Mostraremos como podemos exibir tabelas dinâmicas em diferentes tipos de layouts de tabela ou formulários, especificamente em forma de contorno ou tabular. Agora, maioria das tabelas dinâmicas por padrão mostrará no que é chamado de forma compacta e que agrupa todos os seus rótulos de função juntos ou aninha, hum, em uma única coluna. Portanto, na guia de ferramenta de tabela dinâmica, o grupo de design, especificamente para ver um layout de relatório, mostrando seus diferentes layouts de tabela ou opções de formulário. E esta é a forma compacta normalmente parece por padrão. Nós temos uma coluna de valor aqui, o preço médio, e nós temos que linha rótulos, país e um campo de província. Agora o problema é que, em forma compacta, esses dois campos ficam meio aninhados juntos e ambos vivem dentro da coluna A. E isso significa que temos um cabeçalho de coluna acessível para coisas como classificação e filtragem. E não podemos aplicar nossas próprias regras de classificação ou filtragem a cada um desses dois campos individualmente, modo que isso limita seriamente nossa capacidade de fatiar e explorar esses diferentes rótulos de função . E por essa razão. Eu recomendo que você costuma usar a segunda opção aqui. Formulário delineado. Uma forma de contorno parece muito, muito semelhante à forma compacta. A única diferença é que ele pega essas etiquetas em rolo e as divide em suas próprias colunas. O que significa que agora temos acesso a cabeçalhos de coluna adicionais e rótulos de coluna, incluindo aquelas ferramentas de classificação e filtragem. Então forma delineada é um formato fantástico para usar qualquer momento que você realmente precisa para explorar ou analisar dados do Irã, e então você também tem forma tabular. E neste caso, eu estou olhando para o layout tabular com rótulos de itens repetidos e forma tabular, essencialmente formatos. Seu pivô, como uma mesa, livra-se desse espaçamento extra. Rose e formatação regras coisas assim. E com um pouco de formatação inteligente e algumas opções como repetir os rótulos dos itens escondendo os subtotais, optando por não exibir totais gerais, podemos acabar com algo parecido com isso, que essencialmente ele se parece apenas com uma tabela de dados de fonte limpa agradável que podemos usar para análise posterior. Agora, Uma coisa a observar, você pode alterar o comportamento da tabela dinâmica e ajustar o padrão de forma compacta para algo como contorno ou tabular. Vou mostrar-lhe apenas o que isso parece em um segundo, mas novamente, mas novamente, para recapitular esses casos de uso comum formulário esboço. ótima maneira de explorar sua classificação de dados e filtrar vários campos individualmente e forma tabular com rótulos repetidos e sem totais gerais ou subtotais pode ser a abordagem perfeita para criar novos dados de origem que você pode empacotar cópia, colar e usar para outros fins analíticos. Então, com isso, vamos entrar no Excel. Vou mostrar-lhe como cada uma dessas formas de tabela ou layouts se parece. E então como podemos usar nosso menu de opções para alterar o comportamento padrão. Tudo bem, então vá em frente e abra sua pasta de trabalho de dicas profissionais e vamos aprofundar o esboço e demonstração de layout tabular em nossa seção de dicas de tabela dinâmica cinza. Vá em frente e conecte-se a essa folha, e o que estamos vendo aqui é uma mesa dinâmica simples construída sobre nossos dados de degustação de vinhos . Agora, se você tem seguido o curso, isso deve parecer bem familiar. Países, províncias, vinhos, provadores e alguns valores como pontos e preço. Agora você vai notar é que eu puxei em dois campos para os rótulos aqui, país e província. Mas como estamos no layout compacto padrão, ambos os campos foram aninhados juntos na coluna A e porque eu só tenho este menu suspenso, isso torna muito, muito difícil aplicar diferentes ordenações ou regras de filtragem para cada campo individualmente. Como, por exemplo, se eu quisesse mostrar apenas os cinco principais países, mas também classificar minhas províncias por uma determinada métrica, esses tipos de combinações ou configurações de classificação de filtros individuais realmente não funcionam muito bem neste layout compacto. Então eu vou fazer é cabeça para ferramentas de tabela dinâmica projetado tam indo para relatório layout e mudá-lo para mostrar um formulário de esboço. Tudo o que vai fazer tudo o resto vai ficar o mesmo exceto aqueles dois campos que tinham sido aninhados na coluna A estão agora divididos em uma coluna de país e uma coluna de província. E mais importante, agora tenho rótulos atribuídos a cada uma dessas colunas, que tornará muito mais fácil aplicar essas diferentes combinações de regras de filtragem e classificação. Então, um modo geral, eu uso forma delineada quase exclusivamente. Eu raramente uso forma compacta por essa razão exata. Agora vamos tirar um minuto e falar sobre o formato tabular. Então temos dados nos bastidores ou fonte. Os dados são muito granulares, vão até o nível individual do vinho. E vamos dizer que queríamos rolar que os dados foram agregados em um nível mais alto de granularidade, como talvez só queiramos esses quatro campos, por exemplo. Nós só nos preocupamos com dados no nível da província em casos como essa tabela dinâmica, assim como estamos fazendo aqui, pode fazer um excelente trabalho agregando esses números. Então, estamos olhando para a classificação geral de pontos e preço médio geral por província e por país. O problema é que, se eu copiasse e pegasse os dados desse pivô, eu ainda teria muito trabalho manual a fazer para colocá-lo em um formato agradável, limpo e tabular para análise. Eu tenho esse tipo de cabeçalho e subtotal rosa aqui. Eu tenho linhas em branco que eu teria que preencher, e é aqui que formulário tabular realmente entra em jogo. Então, aqui em nossa cidade de design, vamos mudar do contorno para a forma tabular, e o que vamos fazer é voltar ao menu e escolher repetir todos os rótulos de itens que preenchem esses espaços em branco. E estamos chegando perto do que precisamos aqui. O único problema é que ainda temos essa rosa extra total, que realmente não é uma coisa boa quando se trata de produzir um conjunto de dados brutos e tabulares para análise. Então tudo o que precisamos para fazer subtotais e dizer que não queremos mostrar subtotais e totais gerais. Não queremos o total. Então fora para linhas e colunas. E lá vai você. À medida que percorre. Isso parece um bom, limpo, limpo, não formado, emaranhado, conjunto de dados tabulares. Então o que eu faria aqui é então pegar isso. Copie os dados e a mudança de controle de pivô para baixo para pegar a lista inteira. Copie-o, colou esses valores em outro lugar. E então você costuma correr. Você pode analisar esses dados , como achar adequado. Então eu tenho uma ótima maneira de converter um conjunto de dados muito granulares em dois formatos diferentes ou diferentes níveis de granularidade para diferentes análises ou propósitos diferentes. Agora, última coisa que quero mostrar a vocês muito rapidamente aqui. Se você quiser alterar o comportamento padrão e dizer às suas tabelas dinâmicas que nunca predefinam esse formulário compacto. Você pode realmente fazer isso em suas opções de arquivo do Excel principal. Então opções e ele vai para a sua guia de dados aqui e ver aqui no topo, ele diz. Faça alterações no layout padrão das tabelas dinâmicas. Vá em frente e clique no botão de edição, e aqui é onde você pode dizer, Tudo bem, aqui está eu quero tratar subtotais, totais gerais e o layout do relatório. Esta é a chave aqui. Observe como ele diz mostrar em forma compacta. Isso é provavelmente o que sua versão do Excel está mostrando também o que você pode fazer. Alterne para outra opção aqui e recomendo vivamente o formulário de contorno. Então lá vai você. Você pode alterar seu layout, alterar seu comportamento padrão e criar o layout ou estilo de tabela que melhor se adapte às suas necessidades. 5. Contando campos não numéricos: Tudo bem para esta dica profissional. Quero compartilhar com vocês como podemos usar tabelas dinâmicas para contar campos não numéricos ou baseados em texto . Então, quando você está trabalhando com um pivô que valoriza a dor em sua lista de campos é quase sempre usado para campos numéricos quantitativos. Seus números, esses aeródromos que você pode adicionar, multiplica rastreados e resumem de diferentes tipos de maneiras. Mas aqui está a coisa. Ele também pode ser usado para analisar a contagem ou a frequência de campos não numéricos de campos baseados em texto . E como você não pode realmente agregar ou resumir texto baseado em um verão em média ou Max, o que vamos fazer é usar a contagem de estação de verão, que é o padrão para exibir freqüências para cada um desses valores baseados em texto. Então deixe-me mostrar-lhe um exemplo do que eu quero dizer com isso. Vamos olhar para o banco de dados de filmes dela aqui, e a idéia é entender a distribuição de nossos dados de nossos títulos. Com base em outros campos como gênero, por exemplo, puxamos gênero em rótulos de linha e valores como conta. Podemos produzir uma exibição como esta e porque sabemos que nossos dados de origem estão no nível de título individual do filme, significa que cada linha representa um título diferente. Isso nos diz que existem 900 títulos que se enquadram no gênero de ação. Existem 362 títulos ou subiu que caem no gênero de aventura e assim por diante e assim por diante, e você pode seguir essa mesma abordagem para qualquer campo não numérico como classificações. Por exemplo, siga esse mesmo processo de classificação ruim em ambos os rótulos de lançamento e valores como conta. E aqui podemos ver que há 91 G títulos classificados lá, 553 PG classificados títulos e assim por diante. Então, uma ótima maneira de explorar nossos dados de um ângulo diferente para realmente entender a distribuição de títulos com base em diferentes categorias aqui. Agora, uma coisa a notar. Se você puxar um campo numérico para que valorize a dor e o padrão é contabilizado assim , quando você espera que seja algo como um pouco que normalmente indica que há um problema nos bastidores que valoriza o ar formatado como texto. Ou você pode ter rosa em branco ou ausente que estão confundindo o Excel. Então tenha isso em mente para recapitular esses casos de uso comuns. Número um analisando o número de linhas ou observações em um conjunto de dados que se enquadram em categorias ou buckets específicos, exatamente como estamos mostrando aqui. Também muito útil para gerar dados de origem para gráficos de estatísticas como Sised A Grams ou gráficos de Pareto , que são projetados para trabalhar com freqüências e distribuições. Então, com isso, vamos entrar no Excel e praticar a contagem de alguns dos nossos campos milagrosos não novos. Tudo bem, então se você quiser acompanhar, eu estou no índice aqui, vá para a demonstração dos campos de texto de contagem na seção da tabela dinâmica cinza. Vá em frente e link para fora para essa folha e aqui tem uma tabela dinâmica de layout básico que criaram no meu banco de dados de filmes I mdb. E isso é algo como você esperaria ver direito. Eu tenho um campo baseado em texto, não numérico, não numérico,como país aqui em meus rótulos de função, e eu tenho um campo de valor, uma coluna numérica como receita bruta aqui, meus valores e mostrando essas receitas como um filho. Agora, se eu não me importasse com a receita total que cada país produziu? Na verdade, só quero ver quantas vezes cada país aparece no meu conjunto de dados. Bem, o que eu posso fazer é retirar a receita bruta, e tudo que eu preciso fazer é pegar outra instância do país, colocá-la em valores que será padrão para contar, porque eu realmente não posso resumi-lo de outra forma. E lá vai você. Então agora essas contagens estão basicamente me dizendo o número de vezes que este país apareceu na rosa dos meus dados fonte. Então o Afeganistão apareceu uma vez Austrália 39 França 104. E agora, conhecendo meus dados de origem porque meus dados de origem estão no nível de título, eu posso interpretar essa contagem de país como o número de títulos que foram produzidos por cada um desses países e coluna A. Então, à medida que rolamos através, vejo EUA compõe a grande maioria, 2944 títulos ou subiu no meu conjunto de dados, e eu posso ajustar para modificar essa contagem como qualquer outro valor numérico. Você sabe, eu posso classificar meus países decrescentes por essa contagem, por exemplo, para realmente entender a distribuição de títulos com base no país e novamente o mesmo processo se aplica, não importa qual campo não numérico Você está olhando aqui. Então, em vez de países, podemos puxar ambos para fora e dizer, OK, vamos puxar em idiomas Em vez disso, temos uma lista única de todos os idiomas aqui quando eu puxá-lo para os meus rótulos de papel. Em seguida, pegamos a segunda instância em valores como conta como você esperaria. Aqui vemos a grande maioria dos títulos caindo na língua inglesa 3543 37 títulos franceses , 12 japoneses e assim por diante. Agora, uma última coisa que quero te mostrar. Se puxamos a linguagem para fora e nós realmente pegar título e puxou título em surgiu, bem como seus valores. O que veremos aqui é um para cada fila. E se lembra por que esse é o caso? Porque esta é a granularidade dos nossos dados. Nossos dados estão no nível do título, que significa que cada linha é representada exclusivamente por um título de filme, e é por isso que vemos uma contagem de um em cada linha desta tabela. Então você tem uma ótima ferramenta para manter em seu bolso traseiro. Excelente maneira de observar e analisar seus dados de uma forma ligeiramente diferente e entender a distribuição contando esses campos ou categorias não numéricas. 6. Reunindo datas: Esta próxima dica profissional é popular porque é um tópico muito comum que eu ouço falar o tempo todo . Qual é a forma como agrupo as minhas datas, ou como faço para lidar com o meu encontro? Campos específicos dentro de minhas tabelas dinâmicas agora são dinâmicos por padrão. Inclua ferramentas padrão para agrupar campos de data. Você pode transformar dias em meses ou trimestres ou anos, etc. Agora, às vezes os seus encontros agruparão automaticamente, e às vezes não. E isso depende de suas configurações do Excel. Vou mostrar-lhe como podemos ir para o nosso menu de opções para ver se o recurso de agrupamento automático de datas está ou não ativado. Agora, para esta demo, vamos trabalhar com datas de lançamento do título do filme, que é obviamente no nível do dia, e vou mostrar-vos duas abordagens. Primeiro, vamos mostrar como o dedo agrupa automaticamente essas datas ajustando nossas configurações. E então vamos clicar com o botão direito e perfurar manualmente essas opções de agrupamento. E neste caso, estamos agrupando datas por meses, trimestres e anos. E quando fazemos isso, acabamos com algo assim. Você notará que criamos novos campos que só existem em nosso pivô, não os dados de origem intitulados Anos e Trimestres. E esses campos podem ser usados como qualquer outro. Você pode retirar moedas. Você pode extrair a data de lançamento, que agora está capturando o nome do mês e basicamente manipulá-los como qualquer outra dimensão em sua tabela. Mas se você é como eu, você pode achar esse tipo de desajeitado e pesado tipo de ninhos todos juntos. Isso torna muito difícil acessar seu campo de data de lançamento original uma vez que você os agrupou. Então eu vou realmente mostrar a vocês uma abordagem alternativa também, que eu acho ser um pouco mais flexível, criando alguns novos campos em nossos dados de origem através de colunas calculadas usando algumas funções de data e hora simples. Então, os casos de uso aqui, independentemente da abordagem que você tomar. É tudo sobre a totalização de dados granulares, como dados diários, a fim de analisar tendências ou padrões em um nível mais alto, como mês ou trimestre ou ano, ou criar tabelas de resumo de alto nível ou gráficos que são provenientes de fontes mais granulares dados para que vamos abrir nossa pasta de trabalho pro tip. Vamos analisar essas datas de lançamento e explorar algumas maneiras diferentes de agrupar ou agregar essas datas em diferentes níveis. Tudo bem, então a partir do índice, vá em frente e procure as datas de agrupamento Demo na seção de dicas de tabela dinâmica cinza e link direto para essa folha. E o que temos aqui é uma tabela dinâmica em branco. Vai ser proveniente do nosso banco de dados de filmes. Na verdade, tornaram esses dados de origem acessíveis na próxima guia. Agrupamento de datas, parênteses, dados. Então, um conjunto de dados bastante simples aqui. Só uma amostra rápida do nosso banco de dados de filmes. Temos títulos e coluna a, as datas de lançamento e a coluna B e, em seguida, algumas métricas numéricas diferentes . Número total de avaliações e a receita bruta na coluna C e D Então, de volta à nossa guia dinâmica. A guia datas de agrupamento porque minhas configurações do Excel são definidas para agrupar automaticamente datas e tabelas dinâmicas. Veja o que acontece quando eu pegar essa coluna de data de lançamento e puxá-la para Rose. Veja como ele o criou meio que explodiu em três campos diferentes anos, trimestres e data de lançamento, e é meio que aninhou os dados todos juntos em trimestres de anos e neste caso meses E o que você vai notar é que mesmo que eu vá na minha guia design, altere isso para um formulário de contorno. Ainda é meio que entra em colapso e enrolado de uma maneira estranha que eu pessoalmente não gosto. Agora ele poderia retirar trimestres e data de lançamento e apenas agregar dados por ano, por exemplo, você sabe, puxando minhas métricas numéricas como alguma receita ou o que eu vou mostrar a vocês em apenas um minuto. Eu poderia definir uma coluna de ano em meus dados de origem reais e torná-la acessível para mim, bem como minhas datas de lançamento originais. Então eu vou apertar Control Z para desfazer algumas vezes todo o caminho de volta para o meu pivô original sem nada em minhas áreas dores agora, dependendo de suas configurações, você pode não ter visto o grupo de datas de lançamento da maneira que eles fizeram quando eu puxei em rótulos de linha. O que vamos fazer para verificar que é ir para o nosso menu de arquivo drill para nossas opções e teve para as opções de dados aqui agora na seção de opções de dados bem aqui na parte inferior, você verá uma caixa de seleção que diz desativar agrupamento automático de colunas de data e hora em tabelas dinâmicas. Portanto, na maioria dos casos, isso será desmarcado, que significa que o agrupamento automático está habilitado. Se você não quiser que suas datas agrupe, basta ir em frente e marcar essa caixa e pressionar OK. Eu realmente prefiro manter essa caixa marcada porque eu gosto de definir os agrupamentos por conta própria exatamente como eu quero que eles. Então, agora, se eu pegar a data de lançamento e puxá-la para Rose, ele está apenas ficando lá como data de lançamento em sua forma original. Não agrupados. Então, a partir daqui, se quisermos um grupo, esses dados, digamos que até o nível do ano ou mês, poderíamos clicar com o botão direito e acessar são ferramentas de agrupamento , que você também pode acessar em sua tabela dinâmica. Ferramentas de agrupamento de guias analisadas aqui mesmo grupo sentir Ambos irá levá-lo para esta caixa de diálogo de agrupamento , que basicamente permite que você determine. Ok, aqui está o intervalo de datas que eu me importo. Datas de lançamento de 1923 2015 e selecione campos individuais que você deseja incluir em seu agrupamento. Então, por exemplo, dias, meses, trimestres e anos pressionando OK e ele criou três colunas extras aqui para cada um desses níveis de agrupamento, e ele os aninhou bem aqui Na coluna A. Agora, novamente, isso pode ser exatamente o que você quer. Isso pode ser muito bom. Você pode usar qualquer um desses campos individualmente, mas vou mostrar-lhe uma abordagem que geralmente costumo tomar mais vezes. Então vou controlar o Z para desfazer isso. E o que eu vou fazer é realmente navegar para os meus dados fonte, que é nesta data de agrupamento, guia de dados, e vamos inserir duas colunas aqui logo após Colin ser, e eu vou nomear um deles mês lançado, um deles ano de lançamento. E aqui ele poderia apenas usar funções básicas de data e hora para fazer referência a essa data de lançamento e categorizar um mês e um ano. Então coluna veja, seria um mês de B dois e ano seria igual ano a e que esses ar formatado tipo de bobagem porque eles basicamente herdaram o formato de data de lançamento aqui. Então é isso mesmo. Clique em Formatar as células como imprensa geral. OK, lá vai você. Então mês é 9 setembro lançamentos 1920 boom, DoubleClick E agora temos meses e anos como uma nova coluna aqui no nosso conjunto de dados fonte . E se voltarmos à nossa tabela dinâmica, as ferramentas podem ser atualizadas. Desde que fizemos uma alteração dentro dos limites da nossa tabela original, e agora temos esses dois campos disponíveis para nós. Assim, como qualquer outro enfraquecer o mês de agarrar, puxá-lo em rótulos de linha. Há todas as datas em janeiro e todas as datas que rolamos para baixo em fevereiro, março , abril, etc? Ou podemos tirar esses campos individuais e dizer, quer saber? Eu só quero organizar ou agregar meus dados por ano. Vejamos a soma total da receita bruta com base no ano da data de lançamento aqui, então eu prefiro essa abordagem me dá um pouco mais de flexibilidade agora. Esses campos são realmente definidos em meus dados de origem se eu quiser usá-los em outro lugar também . Mas novamente, não há certo ou errado aqui, totalmente com você. Duas maneiras diferentes de agrupar suas datas usando tabelas dinâmicas do Excel 7. Como habilitar vários filtros: Certo, hora de compartilhar uma das minhas dicas profissionais favoritas da tabela dinâmica, aplicando vários filtros a um único campo de tabela dinâmica. Agora, por padrão, você pode aplicar filtros de rótulo, que são baseados em filtros de texto ou valor, que são baseados em números a um único campo em uma tabela dinâmica, mas não ambos ao mesmo tempo. Então, em outras palavras, se você tivesse um filtro de rótulo aplicado e, em seguida, adicionasse um filtro de valor, esse novo filtro de valor substituiria ou apagaria o filtro de rótulo existente. Então, para mudar isso, o que vamos fazer é ir para nossas opções de tabela dinâmica. Vamos perfurar nossos totais e filtros guia e marcar esta caixa que diz, permitir vários filtros por campo. Agora, como você pode esperar, o que isso nos permitirá fazer é aplicar filtros que são baseados em atributos de texto e valor ao mesmo tempo. Então, neste caso, o que vamos fazer é filtrar para títulos que terminam no número dois, que é um filtro de etiquetas e também gerou um certo volume de receita, que é um filtro de valor. Então, o caso de uso aqui é realmente qualquer momento que você quiser. Aplique filtros mais complexos ou personalizados às tabelas dinâmicas que incorporam essas condições ou critérios baseados em texto e valor. Então vamos entrar no Excel e na prática, filtrando alguns campos com base em filtros de rótulo e valor. Tudo bem. Agora, se você estiver acompanhando, vá para sua pasta de trabalho de dicas profissionais e vá para a demonstração habilitação de vários filtros na excelente seção Dicas de Tabela Dinâmica. E quando nos conectarmos diretamente, o que você encontrará são alguns dados do título do filme aqui. Temos títulos e coluna A. Temos a receita bruta que cada título produzido é gerado aqui na coluna B, e como você pode ver, estamos classificados decrescente por receita bruta. Estes filmes de maior bilheteria aqui no topo incluem Avatar, Titanic, Mundo Jurássico, Os Vingadores, etc. Então, digamos que estamos explorando esses dados um pouco, e queremos ver o desempenho para nossas sequelas especificamente para que pudéssemos simplesmente perfurar cabeçalho da coluna dela. Aqui, neste caso, aplicaria um filtro de rótulo, vez que é um filtro baseado em texto e nós dizíamos, Vamos mostrar os títulos que terminam com um espaço e, em seguida, um dois clique direito OK, e lá vamos nós, Os nossos títulos filtraram o Onley. Estes títulos terminam em dois, que na maioria dos casos seriam Sequels, e isso é útil por conta própria. Mas o que eu quero fazer, realmente, é produzir uma lista de filtragem baseada na receita bruta também. Então eu não quero mostrar todas as sequelas aqui. Eu só quero mostrar as sequelas que, você sabe, neste caso, digamos, digamos, levou mais de US $200 milhões de receita para que ele deve produzir você sabe, esses 10 itens principais neste caso para que possamos voltar para o nosso cabeçalho da coluna e vamos saltar em filtros de valor. Desta vez, eu digo, vamos filtrar títulos que são maiores do que onde a receita bruta é maior que 200 milhões, que é um dois, seguido por oito zeros. Riggio e pressione OK, e veja o que aconteceu aqui. Nossa etiqueta de título, Filtrar para sequelas, agora se foi, e se rolarmos para baixo ou a coluna Receita bruta, um, termina bem acima de 200 milhões para que o filtro de valor esteja funcionando. Mas o nosso filtro de rótulo agora desapareceu, e se olharmos para o nosso cabeçalho, você verá que irá marcar a caixa ao lado dos filtros de valor que nos diz que temos um filtro maior que aplicado. Mas a verificação que estava ao lado dos filtros de etiquetas agora desapareceu completamente. Agora, o que isso nos diz é que precisamos habilitar vários filtros aqui, a fim de fazer o que precisamos . Então vamos aprofundar nossas opções de tempo de ferramenta de tabela dinâmica aqui à esquerda, vamos entrar em nossos totais e filtros Tab e aqui no meio, na seção de filtro, você verá uma caixa de seleção que diz, permitir vários filtros por campo. Vá em frente e dê um cheque e pressione OK. E agora, em teoria, ainda temos nosso filtro de valor aplicado aqui. Então agora devemos ser capazes de voltar e aplicar esse filtro de rótulo termina com para detalhar apenas são sequelas. Então, novamente termina com o espaço para pressionar. OK, e aí está. Agora ficamos com apenas esses 10 títulos que terminam no número para rotular filtrar e geraram pelo menos 200 milhões em receita nosso filtro de valor e olhando para o cabeçalho aqui vemos essas marcas de seleção aplicadas a ambos os tipos de filtros. Então, obviamente, você pode usar isso de muitas, muitas maneiras diferentes, usando qualquer combinação de texto e critérios baseados em valor. Então, mantenha essa no bolso de trás. Ótima maneira de criar critérios de filtro mais complexos ou personalizados ativando vários filtros em suas pivots. 8. Agrupando valores: Eu quero tirar alguns minutos para falar sobre uma tabela dinâmica pro dica que eu realmente não descobri até muito recentemente. E é assim que usar as tabelas dinâmicas para agrupar valores numéricos. Agora, muitas pessoas estão familiarizadas com o agrupamento de campos de texto ou datas e tabelas dinâmicas. Mas o que a maioria dos usuários não percebe é que você também pode agrupar campos numéricos em compartimentos ou buckets personalizados . Agora, para fazer isso, o que vamos fazer nesta demonstração, vamos puxar um campo numérico em nossos rótulos de papéis como este. Neste caso, estamos olhando para o ponto de preço por garrafa de vinho, e isso parece um pouco errado porque sempre somos ensinados, você sabe, a puxar esses campos quantitativos, numéricos para o valores dor de nossa lista de campos, não em linhas ou colunas. Mas neste caso, é exatamente o que queremos. E o que vamos fazer é clicar com o botão direito do mouse em um dos valores nesta coluna e navegar para a opção de grupo, exatamente como faria com uma data ou um campo de texto. Mas a diferença é que esta caixa de diálogo que obtemos é específico para lidar com valores que possamos determinar um valor inicial um valor final e o grupo ou tamanho sido. Então, neste caso particular estavam dizendo que nossos preços do vinho começam em US $0. Quero estender para 1000. Queremos um grupo desses preços em caixas ou baldes de 200 dólares, e isso cospe algo que se pareça com isso. Agora note que em vez de mostrar cada ponto de preço individual que foi traduzido para esses agrupamentos e puxando em um campo como a contagem do nome do vinho, isso está nos dizendo que existem 48.383 vinhos e são amostrados que são preços de 0 a US $99 lá, 1282 que são preços de 100 pés 1 99 e, em seguida, todo o caminho para baixo. No outro extremo do espectro, temos três vinhos únicos que têm um preço superior a US $1000 por garrafa. Agora, se você quiser desfazer e voltar para seus pontos de preço exclusivos originais, clique com o botão direito do mouse nesses agrupamentos e escolha um novo grupo para voltar onde você começou. Então esse tipo de agrupamento de valores é uma excelente maneira de analisar coisas como a freqüência de observações que caem em diferentes baldes. Na verdade, é uma maneira perfeita de criar compartimentos para usar como dados de origem para um gráfico HIST um grama ou Pareto, que são projetados especificamente para visualizar distribuições. Agora, recapitulando, esses casos de uso novamente analisando a freqüência de contador de observações que caem em grupos ou compartimentos de valores específicos, ou criando tabelas de resumo de nível superior ou gráficos de fontes de dados mais granulares. Então vamos saltar para uma pasta de trabalho de dicas profissionais. Vamos abrir este pivô com base em nossos dados de degustação de vinhos e praticar agrupar ou agrupar os valores dos preços. Tudo bem, então se você quiser acompanhar, abra sua pasta de trabalho de dicas profissionais, vá para o índice e procure os valores de agrupamento Demo na seção Dicas de Tabela Dinâmica cinza e vá em frente e faça um link direto para Essa folha. E o que você encontrará aqui é um layout básico de tabela dinâmica. Ele é construído sobre nossos dados de degustação de vinhos, e nós apenas temos campos em vista agora. Tem variedades de vinho e coluna A. Esses são os nossos rótulos de papel, e temos o preço médio por garrafa na coluna B. Esses são valores, e este é um tipo muito comum de layout de tabela dinâmica muito padrão. Obteve um campo baseado em texto como rótulos de função. Você tem um campo numérico ou quantitativo como valores. Então, a partir daqui podemos aplicar, você sabe, alguns agrupamentos baseados em texto, você sabe, talvez queira selecionar tudo com cabernet sauvignon no título do tipo de vinho e criar um grupo contendo apenas aqueles itens. É um tipo muito comum de agrupamento com tabelas dinâmicas, mas não vamos fazer isso. Vamos adotar uma abordagem ligeiramente diferente e vamos realmente puxar nosso campo de valor para nossos rótulos de papéis. E nós vamos puxar um campo de texto neste caso, o nome do vinho em nossos valores. Então nós vamos tipo de inverter o script aqui para criar uma visão totalmente diferente de nossos dados. Então, para fazer isso, vamos retirar a variedade de vinho, e vamos pegar esse campo de preço e puxá-lo direto dos valores para Rose. E quando fazemos isso, agora vemos uma lista única de pontos de preço listados linha por linha. E daqui, o que podemos fazer é basicamente dizer OK para cada um desses pontos de preço por US $4 ventos, US $5 vinho, US $6 vinhos. Quantas observações são rosa existem em nosso conjunto de dados, e desde que nosso conjunto de dados está no nível individual do vinho, eu posso pegar esta coluna nome do vinho, puxá-lo para os valores e representado como uma contagem. Isso está me dizendo que há sete vinhos que custam US$4 a garrafa. Temos 16 preço de US $5 e assim por diante e assim por diante enquanto percorrer esta tabela. Então isso é ótimo. Já é uma forma útil de entender a distribuição dos preços do vinho na amostra, mas é um pouco esmagador porque eu tenho tantos pontos de preço diferentes aqui. Tentar entender isso é um ótimo caso de uso para agrupar esses preços em caixas ou baldes. Então vamos clique direito pode selecionar qualquer um desses preços grupo queijo e aqui recebeu esta caixa de diálogo que é específico para agrupamento de campos numéricos. Então vamos dizer que queremos iniciar o nosso balde primeiro em US $0 e, neste caso, temos algumas linhas que vão até lá acima, mas muito, muito poucas. Então vamos terminar em 1000 e a opção de compra, onde diz 100 que é o tamanho de cada sido. Então queremos grupos de preços em incrementos de $10 ou 100 ou 1000? Vamos começar com 100 ver o que parece e simplesmente pressione. OK, então o que isso faz é basicamente converter esses pontos de preço individuais nessas caixas ou baldes? E daqui temos contas de nomes de vinhos para que você possa ver a grande maioria cair abaixo US $100. Temos cerca de 1200 quase 1300 que caem 100 a 200 pontos e assim por diante, para que possamos tratar esses valores como qualquer coisa que possamos classificar nossos baldes. Podemos criar um gráfico dinâmico com base nessas frequências, copiá-lo, copiá-lo, colá-los em uma nova folha e criar um HIST, um grama ou gráfico de Pareto. Mas a linha de fundo é que, usando essas ferramentas de agrupamento de valores foram capazes de explorar e analisar este conjunto de dados de um ângulo totalmente diferente ou através de uma lente totalmente diferente, e realmente obter uma noção de como esses preços são distribuídos ao longo nosso conjunto de dados. Então, você tem um curso rápido sobre o uso de ferramentas de agrupamento de valores de tabela dinâmica 9. Como adicionar cálculos de valor: Tudo bem, vamos falar sobre uma das ferramentas de tabela dinâmica mais úteis e poderosas analisando seus dados usando cálculos de valor Agora. Em poucas palavras, os cálculos de valor permitem exibir valores brutos subjacentes de todos os tipos de ângulos diferentes . Então, em vez de apenas exibir um padrão alguns ou contagem de ou média, você pode mostrar esses campos baseados em valor de maneiras muito interessantes, como o percentual de uma determinada coluna ou linha ou o percentual de uma determinada categoria pai, você pode mostrar valores como a diferença de ou percentual de um determinado item base. Então isso é ótimo para coisas como mês, mais de mês ou ano após ano. Você pode calcular totais em execução, que exibe esses valores como um total cumulativo dentro de um determinado campo base. Ou você pode mostrar aqueles como classificações que exatamente como ele soa, não mostra o volume real. Mas ele mostra a classificação desse volume com base em baixo a alto ou ocultar muitas regras. Agora tenha em mente que esta não é uma lista abrangente. Há opções adicionais também, mas basicamente o que isso nos permitirá fazer é mostrar algo assim, ou estamos olhando para os totais de receita bruta por mês neste caso, mês um a nove. Mas em vez de apenas parar por aí, podemos realmente clicar com o botão direito do mouse nesses totais de receita ou criar cópia duplicada e mostrar esses valores de maneiras diferentes. Então aqui estão algumas das opções. Falamos sobre o percentual de grandioso, percentual total da linha percentual da diferença pai do total de execução e assim por diante e assim por diante . Então, aqui você pode selecionar uma dessas opções, como executar Total, por exemplo, selecionado com base no por exemplo, Campo do mês de lançamento, que não é a coluna A. O que isso faz é exibir essas receitas como um em execução total de mês a mês após mês. Agora, algumas coisas rápidas para anotar aqui número um uma dica rápida e recomendar arrastar em várias cópias do campo que você está olhando para analisar para que você possa manter um original como estamos fazendo aqui. E em comparação com essas versões de cálculo de valor desse campo e a segunda nota aqui, lembre-se que você não está realmente alterando os valores em si. Os valores brutos subjacentes do núcleo não estão mudando. Tudo o que estamos fazendo é mudar a maneira como esses valores são exibidos dentro de nossa tabela dinâmica e Onley dentro de sua tabela dinâmica. Casos de uso tão comuns aqui. Um deles é analisar dados sérios de tempo como um total de execução por dia, mês ou ano ou tempo de cálculo. Tendências sérias como mês após mês, ano após ano , trimestre sobre trimestre, etc. Segundo caso de uso comum explorando a composição de seus valores em termos de porcentagem participação por coisas como categoria mês, ano, etc. Então o que vamos fazer é saltar para o Excel e vamos dar uma olhada em alguns dados de medalha olímpica prática analisando e explorando esses dados usando esses tipos de cálculos de valor. Tudo bem, assim como seu livro de dicas profissionais. Estamos na seção de dicas de tabela dinâmica aqui, e vamos perfurar o link de demonstração de cálculos de valor direto para aquela folha. E o que estamos vendo aqui são dados de seis jogos olímpicos diferentes que tiveram o inverno de 2006 até os jogos de verão de 2016. E neste caso, estamos olhando para a contagem de medalhas de ouro, prata e bronze para os Estados Unidos especificamente. Então esses valores que você vê na coluna B há tipo de não calculado apenas a contagem básica de metais. Com base em cada um desses anos, podemos ver que os EUA EUA 1 992 total de medalhas durante este período. Mas e se quisermos melhorar este ponto de vista um pouco analisado? Estes metais contam de alguns tipos diferentes de maneiras. Adicione um pouco mais de contexto ou aprenda um pouco mais sobre o que está acontecendo aqui. Bem, a melhor maneira de fazer isso é com valores calculados. Mas antes, basta clicar com o botão direito e mostrar valores e diferentes tipos de maneiras, que substituiria esses valores originais não calculados e coluna B. O que eu vou fazer é apenas ir para a minha lista de campos, pegar esse campo de metal e eu vou puxar em mais quatro cópias duplicadas dessa coluna. Então eu tenho contagem de metal contado mental 234 e cinco, todos os quais estão atualmente exibindo a mesma contagem bruta. Agora, a primeira pergunta que eu gostaria de responder é que eu conheço o total de medalhas dos EUA de 1 992. Mas me mostre como esse total se quebra para cada um desses seis Jogos Olímpicos. E se eu quiser mostrar isso como uma porcentagem ou composição de contagens de metal que pode clicar com o botão direito nesta coluna C de segunda geração e vamos mostrar esses valores como a porcentagem do total da coluna. Agora você vê que a linha total geral somam 100% e que 100% é dividido por cada um dos seis rótulos de linha. Assim, podemos ver que em 2008 os Estados Unidos ganharam 31% de todos os metais que ganharam nesta amostra inteira em comparação com 2006, onde apenas 14,84%. E em vez de manter o cabeçalho algo que é muito confuso, como contagem de metal, também, pode realmente apenas clicar na barra de fórmulas e dar a este um título personalizado como porcentagem uh, coluna neste caso. E agora para Colin D. Esta contagem de metal três diz que queremos contar uma história semelhante, mas em vez de mostrar a porcentagem de um a cada ano, gostaria de classificar esses anos com base no volume de metais. Então, para algo assim, para simples como mostrar valores como uma classificação e porque um grande número é uma coisa boa neste caso , queremos classificar de maior para menor, e nosso campo base aqui, em que estamos classificando é baseado na imprensa anos OK e a mesma história tipo de vem à tona. 2008 foi o número um dos Jogos Olímpicos nesta amostra para os EUA. EUA Ganhamos 309 medalhas, e por outro lado, 2006 foi a menor contagem de medalhas, que é o sexto ano classificado e a mesma história Aqui. Podemos mudar nosso cabeçalho para algo como classificação. E agora se não nos importarmos com as medalhas ganhas a cada ano? Tudo o que nos importa neste caso particular é o total e como esse total está mudando ao longo do tempo. Bem, para algo assim, o cálculo total em execução é uma ótima opção, então ele pode mostrar esses valores como um total em execução também pode usar o percentual total em execução, que apenas adiciona a 100%. Vamos em frente e fazer apenas o total de execução simples em nosso campo base novamente é o nosso papel. Etiquetas são anos. Pressione OK, e agora 2006 mostra as 48 medalhas 2008. Nós adicionamos 309 ao 48 2010. Acrescentamos mais 89. Você pode ver como essa contagem total de medalhas está crescendo ao longo do tempo como este cone cumulativo. Portanto, há uma rápida mudança total de execução, esse cabeçalho executando total. E como exemplo final agora, o que eu realmente quero entender é como a contagem de medalhas mudou ano após ano ou, neste caso, neste caso, jogo olímpico sobre os Jogos Olímpicos. Uma vez que é um intervalo de dois anos, e o que podemos fazer aqui é usar algumas opções diferentes. Podemos usar algo como a diferença de ou a diferença percentual de. Então, neste caso, se você se importa com porcentagens, você pode escolher esta opção para se preocupar com a contagem real de metais, sua cerveja. Você poderia usar esta primeira opção. Vamos em frente e fazer isso. E agora o nosso campo base, como sempre, são rótulos das regras do ano. Agora temos uma outra opção aqui também, que é o item base, e esta é a base a partir da qual você está calculando essa diferença. Então, neste caso, nem sempre queremos calcular nossa diferença em relação a um ano específico, como 2006 ou 2008. Nós sempre queremos calcular essa diferença com base no item anterior em nossos rótulos de linha, então vamos selecionar anterior aqui e pressionar. OK, e agora veja isso. Está nos dizendo que em 2008 os Estados Unidos ganharam 261 medalhas a mais do que em 2006 . Mas então, em 2010, eles ganharam 220 medalhas, menos de 2008. Então isso está mostrando agora que o padrão de ano sobre ano diferenças na contagem de metais. Então vamos em frente e mudar esse cabeçalho para a diferença de. E lá vai você. Portanto, há opções adicionais aqui, algumas mais complicadas que irão cobrir e outros cursos como a opção de índice. Mas esta é uma ótima demonstração de como algumas das opções mais comuns podem ser usadas para analisar e explorar seus dados em uma tabela dinâmica de várias maneiras diferentes interessantes. 10. Mostrando itens vazios: Vamos demorar alguns minutos para falar sobre como podemos personalizar erros e linhas em branco dentro de uma tabela dinâmica. Agora, por padrão. Como seria de esperar, tabelas dinâmicas não exibirão rótulos de linha para itens em que os valores não existem. No entanto, você pode forçar o Excel a mostrar esses itens, mesmo nos casos em que não há dados na tabela de origem. Então deixe-me mostrar o que quero dizer com isso. Aqui estamos olhando para esses títulos de filmes cruzados de receita bruta categorizados por países e gêneros. E temos casos como a Austrália, onde temos muitos títulos em nossa amostra em muitos gêneros diferentes. Ação de aventura, animação, biografia, etc e outros países como Afeganistão, Argentina ou Aruba. Isso pode ter apenas um pequeno número de títulos em nossa amostra que abrangem Onley um ou dois gêneros como drama, crime ou ação se quisermos o gênero. Colin coluna B Neste caso, para exibir todos os gêneros possíveis para cada país. O que poderíamos fazer é simplesmente certo. Clique nesse cabeçalho de coluna neste gênero de caso. Navegue até nossas configurações de campo e marque esta caixa na parte inferior da lista que diz mostrar itens sem dados e o que isso fará é forçar a tabela dinâmica a exibir o conjunto completo de gêneros para cada país no layout da tabela. E a partir daqui, o que podemos fazer é navegar para nossas opções de layout e formato e personalizar exatamente como esses valores vazios, bem como valores de erro potenciais, são exibidos. Agora você pode estar se perguntando por que você realmente quer fazer isso e tipo de criar Rose para dados que serão apenas iguais a zero de qualquer maneira. E não é muito comum. Mas um caso de uso é para exibir todos esses itens possíveis dentro de seu layout de tabela, mesmo aqueles espaços em branco ou zeros, que você possa criar um layout ou modelo consistente para outros fins ou caso de uso semelhante . Talvez você espere que todos esses itens sejam preenchidos e, se eles não estiverem, você deseja sinalizá-los ou chamar a atenção para esses valores ausentes. Outra opção aqui. Reformatação de espaços em branco ou erros simplesmente para limpar relatórios ou painéis voltados para o usuário criados com uma tabela dinâmica. Então vamos entrar na nossa pasta de trabalho de dicas profissionais. Vou mostrar como podemos adicionar esses itens sem dados a uma tabela dinâmica e depois personalizar exatamente como eles são exibidos. Tudo bem, então se você quiser acompanhar, vá para o seu índice, vamos perfurar a demonstração de itens vazios mostrando na nossa seção de dicas de tabela dinâmica cinza. Vá em frente e ligue diretamente para fora. E assim como descrevemos, estamos olhando para o banco de dados de filmes do IMDB. Aqui tem a soma da receita e alguns do orçamento e coluna C e D, dividido por país e por gênero. E assim como descrevemos, você tem países como Austrália, Canadá e França que geraram títulos em vários gêneros diferentes e outros países como Colômbia ou Finlândia, onde talvez apenas um ou dois gêneros são representados aqui em nosso conjunto de dados. Então, neste caso, digamos que eu gostaria de incluir uma linha para cada gênero possível em cada país. Para fazer isso, eu posso clicar com o botão direito do mouse no cabeçalho da coluna neste caso, ser um para o gênero. Vá para minhas configurações de lista de campos ou configurações de campo, e na guia de layout e impressão, tudo o que eu preciso fazer é marcar esta caixa que diz mostrar itens sem pressionar dados, OK, e lá vamos nós. Agora, todos os 17 gêneros possíveis estão representados para cada país dentro da nossa tabela . E uma vez que temos isso no lugar, o que podemos fazer é personalizar exatamente o que essa rosa em branco é, Rose sem dados realmente se parece. Então, para fazer isso, vamos entrar em opções de ferramentas de tabela dinâmica e aqui mesmo na guia de layout e formato, temos que marcar caixas aqui, uma para personalizar como formatar valores de erro e outra para personalizar como formatar células vazias . Então, por padrão, essas células vazias foram formatadas como você adivinhou, células vazias. Mas podemos mudar isso para um valor diferente. Se quisermos, poderia usar texto como um A poderia usar um valor como zero aqui, vez que, tecnicamente, isso é preciso. Você sabe, filmes de comédia no Afeganistão deram $0 em receita, mas também é um pouco enganador porque o fato é que esses valores estão faltando no nosso conjunto de dados. Então vamos saltar para trás. E acho que prefiro a célula em branco aqui para aquelas células vazias. Vá em frente e pressione OK, e agora se tivéssemos um campo calculado aqui na nossa lista de valores também? Algo como o nosso aliado, por exemplo, que é simplesmente a receita bruta dividida pelo orçamento. Nesse caso, isso deve ser uma porcentagem. Então vamos alterar as configurações do campo de valor. Trump's Vamos mudar o formato do número. Desculpe-me, 2%. Aqui vamos nós. E agora, como esse cálculo está sendo executado em cada linha em nosso pivô, incluindo rosa com denominadores em branco, recebemos todas essas mensagens div zero aqui. E este é o comportamento padrão. O que podemos fazer é voltar às nossas opções e personalizar a aparência dessas mensagens de erro . Então é marcar essa caixa e podemos mostrá-los como um espaço em branco, assim que eu acho que eu realmente prefiro. Ou, se você quiser, você pode personalizar o valor aqui algo como um A E novamente, este é um caso em que eu não substituiria essas mensagens de erro por um zero, porque isso vai fazer coisas como distorcer qualquer média você pode tomar, ou quaisquer cálculos que você pode fazer contra isso são Y. Campo. E ele conta uma espécie de ah, história enganosa porque eles são por isso que o retorno sobre o investimento não foi 0%. Para esses gêneros, simplesmente não existia. Então vamos voltar para as opções preferem usar espaços em branco para ambos, de modo que tudo o que vemos são as estradas que são preenchidas com dados da nossa tabela de origem. Então lá temos ele mostrando itens em sua tabela dinâmica com dados vazios. Não é a ponta de mesa dinâmica mais comum no mundo, mas uma que pode ser de grande ajuda quando você precisar. 11. Configurando Slicers e linhas temporais: Quero tirar alguns minutos e falar sobre um dos meus recursos favoritos da tabela dinâmica, adicionando filtros visuais com segmentadores e linhas de tempo. Agora, essasferramentas parecem muito sofisticadas e complexas, ferramentas parecem muito sofisticadas e complexas, mas na verdade são bem simples. Slicers são basicamente apenas versões interativas de um filtro, e linhas de tempo são apenas segmentadores que funcionam com datas. Então é um simples é inserir? Estes são muito simples também. Você pode soltar homens diretamente da lista de campos clicando com o botão direito do mouse em um determinado campo ou coluna e escolhendo em uma segmentação. Ou, se você estiver olhando para um campo específico de data, adicione como linha do tempo. E quando você adicionar essa segmentação, ele criará um novo objeto de pasta de trabalho contendo uma lista de possíveis seleções de usuário. E essas seleções irão vincular dinamicamente e filtrar a própria tabela dinâmica como um filtro normal em uma célula suspensa. Agora, por padrão segmentadores, vai Onley interagir com a tabela dinâmica a partir da qual eles foram criados. Dito isto, se você tiver várias tabelas dinâmicas geradas a partir dos mesmos dados de origem, você pode usar as opções de conexão de relatório do menu Slicer Tools para essencialmente vincular uma única segmentação a várias tabelas dinâmicas e na demonstração são prestes a mergulhar. Nós vamos fazer exatamente isso. Vamos controlar um atleta, pivô e um pivô de eventos usando um único conjunto de segmentadores e cronogramas. Agora lembre-se, cada um desses pivôs precisa compartilhar os mesmos dados de origem ou estar relacionado de alguma forma. Não é possível apenas tentar controlar duas tabelas não relacionadas usando uma única segmentação. Agora nota final aqui, por padrão, as seleções ou os itens que você vê em uma segmentação, indicarão visualmente casos em que nenhum dado está disponível. Então, se você tivesse um cortador para a temporada e um cortador para o esporte e você selecionou o verão como a temporada, você não gostaria de mostrar todas as opções de esportes de inverno nesse segundo cortador porque elas não gerarão valores. Na maioria dos casos, eles serão ótimos por padrão ou, em alguns casos, nem mesmo visíveis na segmentação. E isso é certamente uma característica útil. Mas uma coisa a ter em mente é que ele pode retardar o desempenho se você estiver lidando com muito, tabelas muito,muito grandes ou complexas com muitas interações entre muitas segmentações Agora, casos de uso comuns aqui para um, adicionando filtros visuais amigáveis para relatórios ou painéis criados com tabelas dinâmicas e gráficos dinâmicos. E segundo, usando fatias para indicar claramente e visualmente exatamente como uma tabela estava sendo filtrada. Então, com isso, vamos saltar para o Excel e praticar, filtrando alguns dados usando essas segmentações e linhas de tempo. Tudo bem, então se você quiser acompanhar, vá em frente e abra seu livro de dicas profissionais e vamos para a demonstração de fatias e cronogramas na seção de dicas de tabela dinâmica cinza. Então vá em frente e salte direto para aquela conta. Slicers e cronogramas e o que você verá aqui é girar. As mesas não estão no lugar. Um da esquerda é chamado de atletas e aquele que direita é chamado de eventos e em ambos os casos estavam apenas olhando para a contagem de medalhas de ouro, prata e bronze obtidas por evento ou por atleta individual. E criamos um pouco de espaço acima dessas duas tabelas para que possamos essencialmente criar um painel de controle com segmentações e linhas de tempo para filtrar dinamicamente as tabelas abaixo. Então é quase como criar um mini pequeno tipo de painel aqui. Então vamos começar com o nosso tempo de atleta cabeça para a nossa lista de campo, eo primeiro cortador que eu gostaria de inserir é um simples para temporada apenas duas opções verão ou inverno clique direito adicionar como cortador e lá vamos nós. Está inserido. Este novo objeto de planilha que poderia ser tratado como qualquer outro objeto poderia conter Ault . Encaixe-o à sua grade se você quiser, pode redimensioná-lo, arrastando ao redor exatamente assim, e veja o que acontece enquanto eu faço seleções diferentes para esta tabela dinâmica segmentadora a partir da qual ele foi criado. Filtros exatamente como ele teria este sido em filtro normal em vez de um cortador. Então, quando eu tiver selecionado verão, eu vejo os melhores ganhadores de metal para esportes olímpicos de verão. Michael Phelps e Ryan trancaram os dois nadadores dos EUA. Neste caso, mesmo vale para o inverno. Agora vemos em Lee os Atletas de Inverno no pivô, então vamos em frente e sem filtro. Vamos voltar para o primeiro pivô dela. Quero adicionar uma segunda segmentação agora para esportes, então clique com o botão direito do mouse em Adicionar uma segmentação, arraste-a no lugar. E uma coisa a notar aqui é que neste caso eu tenho uma lista muito alta e longa tipo de embalado em um pequeno pedaço de imóveis. Então o que podemos fazer é alterar as ferramentas de segmentação para ajustar coisas como este arranjo de alinhamento de estilo e o número de botões. Então, neste caso, vamos fazer essa cor diferente para diferenciá-la do nosso cortador de temporada. Vamos arrastá-lo um pouco aqui e em vez de uma única coluna de botões, vamos fazer isso algo como três colunas que apenas o torna um pouco mais legível, e isso torna a barra de rolagem um pouco mais fácil de interagir com Aqui. Agora, veja isso. Se eu selecionar o verão da minha temporada slicer e rolar para baixo na parte inferior do meu slicer esportes , você verá essas grandes opções de esqui alpino nome em bobsleigh e você vai notar que sem coincidência, aqueles são os esportes de inverno. Então se destaca visualmente identificar as opções inválidas aqui. Porque se escolhermos hóquei no gelo, por exemplo, note que não há estradas no pivô porque nenhum atletas competiu em hóquei no gelo durante os Jogos Olímpicos de Verão, então podemos ir em frente e sem filtro. Isso e uma coisa que podemos fazer para personalizar como isso funciona é selecionar o cortador entrar em nossas ferramentas de segmentação e configurações de segmentação aqui à esquerda e ver esta caixa de seleção. Estamos visualmente indicando itens sem dados. Esse era o estilo desbotado que acabamos de ver. Outra opção que tende a funcionar muito bem é ocultar itens completamente do que não ter dados . Então, se marcarmos essa caixa e pressionarmos OK agora, esses esportes de inverno nem aparecem nesta lista. A mesma coisa se clicarmos no inverno. Agora vemos em Lee os esportes de inverno e as opções de verão não existem. Então, esta é uma boa opção de abordagem estilo limpo para usar. Mas, novamente, lembre-se que pode abrandar as coisas se você estiver trabalhando com tabelas muito grandes ou complexas. E uma outra coisa a observar aqui por padrão, você tem a seleção única ativada, o que significa que, à medida que você altera sua seleção, você só está lidando com um item específico ou seleção fora do tempo. esta caixa com as marcas de seleção que permite a seleção múltipla para que eu possa ver atletas que competiram em esqui alpino ou biatlo ou bobsleigh, e isso é meio que até você, dependendo dos dados. Se você deseja ou não ativar esta opção de seleção múltipla neste caso. Vamos mantê-lo como solteiro. Selecione por agora. Eu vou ir em frente e limpar isso, e nós vamos fazer mais um exemplo aqui do nosso atleta. Volte para a nossa lista de campo. Desta vez eu quero trabalhar com o campo ano quando eu clicar com o botão direito do mouse, observe que agora este último anúncio item como linha do tempo está finalmente ativo. E não era para outros dois casos. Temporada no esporte. Se clicarmos, adicionar como linha do tempo, ele cria outro cortador como objeto, exceto com um tipo diferente de aparência. Porque esta é uma linha do tempo projetada especificamente para trabalhar com datas, algumas coisas para chamar aqui você pode ver que é uma espécie de enrolar as coisas de uma forma engraçada. Nós só temos dados por anos, então nós realmente não nos importamos com meses aqui. Então você pode mudar esse pequeno menu suspenso dois anos, e podemos entrar em ferramentas de linha do tempo, que são muito semelhantes às ferramentas de segmentação. E podemos fazer coisas como ajustar o estilo, talvez querer que isso seja verde e depois interagir com essa linha do tempo. É um simples é clicar. Arrastando. Você pode estender as alças que você pode clicar no dedo Olhe para Onley anos individuais, e você vai notar que, assim como as outras versões, as segmentações, a tabela dinâmica filtra dinamicamente. Então, essencialmente, o que fizemos foi criar um conjunto de controles visuais que os usuários podem usar para explorar qualquer combinação diferente de dados deste pivô, com base na temporada, no esporte e em um período de tempo específico. Você pode olhar para atletas de verão, por exemplo, que competiram em tiro com arco e apenas de 2014 a 2016 e instantaneamente foram capazes de perfurar nossa tabela dinâmica para mostrar a combinação exata de Rose. Agora, a última coisa que quero mostrar é essa opção de conexões de relatório. Então isso não é filtrado. Cada uma dessas segmentações e linhas de tempo existentes e o que vamos fazer um por um, selecione o cortador e em nossas ferramentas de segmentação. Clique nesta opção de conexão de relatório. Agora isso me mostra quaisquer pivôs válidos na worksheet que eu possa conectar essa segmentação também. E como você pode ver, você tem uma caixa de seleção ao lado de atletas e uma caixa de seleção em branco ao lado de eventos, que é este segundo pivô bem aqui. Tudo o que precisamos fazer é marcar essa caixa pressionar OK e agora ver o que acontece? Selecionamos o verão e ambos os atletas e os eventos filtram em conformidade. O mesmo com o inverno. E nós basicamente apenas amarramos este cortador a ambos os pivôs. Vamos fazer exatamente a mesma coisa por esporte. Relate conexões apertadas para eventos também. E a linha do tempo, linha do tempo, as ferramentas, as conexões de relatórios. Sim, é o primeiro. Ok. E agora, assim como antes, você pode filtrar qualquer combinação de valores. E agora os dois campos. Ambas as tabelas dinâmicas serão atualizadas para refletir essas seleções. Então eles são atrás segmentadores e linhas do tempo. Ótima maneira de adicionar filtros visuais e ferramentas amigáveis para suas tabelas dinâmicas do Excel. 12. Formatação condicional: Certo, vamos falar sobre como podemos dar vida às nossas tabelas dinâmicas com formatação condicional. Agora esta é uma dica de quatro estrelas, bastante avançada, e isso é apenas porque existem algumas nuances aqui que tornam a formatação condicional com tabelas dinâmicas um pouco mais complicada do que a formatação condicional com valores de célula padrão. Então vamos começar adicionando regras de formatação condicional. Essas mesmas velhas regras familiares das escalas de cores das barras de dados da cidade natal. Icahn define, etcetera para destacar padrões ou tendências na própria tabela dinâmica. Tipo assim. Neste caso, adicionamos formatação para visualizar padrões em preços médios e classificações médias de pontos para diferentes vinhos para um determinado país filtrado, neste caso, Canadá. Agora, ao contrário da formatação de célula tradicional, podemos especificar algumas opções para como o formato da tabela dinâmica reage às alterações no layout da tabela. Então, em outras palavras, essas opções aqui nos permitem determinar o escopo da formatação que aplicamos, você possa aplicar o escopo dessa formatação para um intervalo selecionado de células para todas as células para essa métrica dada neste caso preço médio ou para todas as células para essa métrica dada, e aqueles rótulos de papel específico, que é nome de vinho neste caso Agora, modo geral, Eu prefiro que a segunda opção todas as células mostrando esses valores particulares. E o que isso vai fazer é permitir que esse formato fique preso. Mesmo se tirarmos vinho, nome fora da linha rótulos e um campo diferente como a variedade de vinho, eu vou mostrar exatamente como isso se parece. Presume que saltamos para o Excel, mas rápido resumo dos casos de uso comuns, usando escalas de cores para chamar a atenção para padrões ou tendências nos dados e aplicando regras de formatação em geral que ficam independentemente de como o layout da tabela dinâmica muda. Então, com isso, vamos entrar no Excel e praticar, aplicando alguns formatos condicionais personalizados à nossa tabela dinâmica. Tudo bem, então se você quiser acompanhar, abra sua pasta de trabalho de dicas profissionais e vá para a demonstração de formatação condicional na seção de dicas da Tabela Dinâmica cinza , e aqui você encontrará um pivô relacionado ao vinho. Estamos olhando para nomes de vento em rótulos Roe e para cada um desses vinhos, estão rastreando o preço médio e a classificação de pontos médios. E, como você pode ver, aqui foram filtrados Onley toe Olhe para linhas canadenses especificamente. Agora a idéia é transformar isso de uma simples tabela velha em uma ferramenta visual que podemos usar para identificar as melhores combinações de preço e classificação de pontos. Em outras palavras, eu quero criar algum tipo de ferramenta de rastreamento para me ajudar a chamar a atenção para aqueles vinhos que são acessíveis e muito bem classificados. E para fazer isso, vamos aplicar algumas regras de formatação condicional, como escalas de cores e barras de dados. Então vamos começar com a nossa coluna de preço médio aqui. modo geral, eu sugeriria selecionar a primeira linha aqui e usar algo como o atalho de seta de deslocamento de controle para baixo para destacar todos os vales com a coluna NYT. Mas neste caso, por uma questão de demonstração, vou apenas selecionar um pedaço de dados aqui cerca de 10 linhas ou mais. Agora, veja o que acontece quando eu aplico um formato condicional neste caso, uma escala de cores. E como os preços baixos são bons neste caso, eu quero que esses preços baixos sejam verdes. Eu vou escolher a segunda opção aqui vermelho, amarelo, verde, e quando eu clicar, você vai notar este pequeno pop-up aparecer exatamente onde nós normalmente veríamos algo como as opções de preenchimento automático e Este é um menu especial de formatação de tabela dinâmica que nos permite determinar o escopo desta regra de formatação. Então, por padrão, ele está definido para ser selecionado células que eu tinha arrastado para fora. Mas também tenho duas outras opções. Qualquer célula que mostre valores médios de preço, independentemente dos rótulos de papel, ou qualquer célula que mostre valores médios de preço especificamente para nomes de vinhos. Então, para mostrar um exemplo, vamos escolher essa terceira opção. Você pode ver que ele é aplicado para baixo para cada venda na coluna, mesmo que eu só tinha selecionado cerca de 10 ou 11 células. Mas veja o que acontece agora se tirarmos o meu nome. Puxe variedade de vinho em que a formatação é perdida porque tínhamos determinado o formato Scope toe Onley quando estamos olhando para ambos preço médio e o nome do vento especificamente. Então vamos em frente e trocar a variedade de volta. Por que o nome de volta e lá está nossa formatação novamente. Agora não podemos mais chegar ao mesmo menu pop-up agora, mas podemos acessar essas mesmas ferramentas, mas direcionar para regras de gerenciamento de formatação condicional, e podemos editar essa escala de cores que acabamos de criar, e você verá os mesmos três aqui no topo da nossa lista. Então, em vez desta terceira opção aqui, vamos selecionar todas as células mostrando valores médios de preço novamente, independentemente dos rótulos de linha ou coluna pressione O'Kane. Aplique essa regra. Como podemos ver, nada muda. Mas agora veja o que acontece. Mas por que nomear variedade de vinho em e confira as varas de formatação, mesmo que trocamos em diferentes rótulos de papel. Então, modo geral, esse é o escopo que eu gosto de aplicar quando estou jogando com formatos em tabelas dinâmicas. E vamos reverter o que estamos olhando. Por que nomes de novo? Agora vamos nos concentrar nesta coluna de classificação de pontos aqui. Então, a mesma coisa que eu posso selecionar uma célula. Posso selecionar várias células. A coluna inteira. O que você escolher. Neste caso, quero barras de dados. Então, em vez de uma opção padrão de barra de dados, há alguns outros valores aqui. Outras opções que eu gostaria de personalizar. Então eu estou realmente indo para ir para mais regras na parte inferior do menu, e aqui novamente, nós podemos acessar as opções de escopo aqui. Vamos escolher a segunda opção, e em vez de mostrar a classificação de pontos real como texto, vamos Onley mostrar as barras de dados marcando essa caixa. E porque há uma faixa muito estreita de pontuações aqui, é realmente nada abaixo de 75 ou 80 e nada acima de 100. Vamos definir um número mínimo de 75. Só mais duas claramente. Veja as diferenças entre essas barras agora por último, mas não menos importante, indo para dar-lhe uma espécie de cor azul escuro. Isso foi totalmente com você e pressione OK, e aí está. É aplicado essas barras de dados personalizadas ao longo da coluna, exatamente como você esperaria. Então agora o propósito da nossa ferramenta está começando a surgir direito. Estamos à procura de linhas de preço baixo no topo da lista que também tenham uma classificação de pontos altos com base no tamanho desta barra. Então, vemos algumas opções muito boas aqui. Ótimas opções lá. Boa aqui. Uma última coisa que podemos fazer para realmente levar isso para a linha de chegada é aplicar. Mais uma regra para essa coluna de classificação de pontos que vai para formatação condicional destaca regras de célula. E vamos basicamente dizer que qualquer uma dessas células que são maiores do que 90 pontos vamos dar-lhe um preenchimento verde com um texto verde. Pressione OK e, em seguida, clique no pop-up e aplique-o a todas as células que mostram valores médios de classificação de pontos e confira. Isso apenas destacou qualquer uma dessas células aqui com uma classificação de pontos de 91 ou superior, possamos ver que estes vinhos canadenses obter alguns realmente bons vinhos de alta qualidade por preços muito baixos . Apenas $17 nestes casos, $18 nestes. E agora, porque construímos essa ferramenta e porque aplicamos essas regras de formatação a umatabela dinâmica e flexível, tabela dinâmica e flexível, isso significa que podemos fazer algo assim em vez de olhar para os vinhos canadenses. Talvez queira ver as linhas italianas. Pressione OK. Todas as nossas mesmas regras de formatação aplicam-se automaticamente, o que é incrível. Então agora vamos percorrer estes vinhos italianos como não podemos ver nada acima dos 90. No entanto, até chegarmos aqui vamos nós. Então aqui está a primeira estrada que foi sinalizada como um vinho de 91 pontos. Este é um 2010 Chianti Classico por US $12 então, basicamente, apenas criou uma pequena ferramenta agradável que nos permite identificar essas grandes oportunidades para obter linhas de alta classificação por um preço muito acessível. E aí você está trazendo seus pivôs vida usando formatos condicionais 13. Removendo e revivendo dados de fonte: Tudo bem. Esta próxima dica profissional é incrivelmente importante para quem trabalha com tabelas dinâmicas e excel . Vamos falar sobre o dinheiro da tabela dinâmica e especificamente como esse dinheiro pode ser usado para remover e reviver seus dados de origem da tabela dinâmica. Agora, o que a maioria dos usuários não percebe é que sempre que você insere uma nova tabela dinâmica, uma caixa dinâmica também é gerada. Portanto, pense nesse dinheiro dinâmico como essencialmente uma cópia duplicada e compactada dos dados de origem que vive fora da vista. Nos bastidores sobre esse dinheiro é incrivelmente importante porque o que ele permite que você faça é realmente excluir seus dados originais sem realmente impactar o pivô em si. E isso parece loucura. Você pode realmente excluir a tabela ou o intervalo de células que você usou para criar uma tabela dinâmica e continuar a usar essa tabela dinâmica exatamente como você faria normalmente. Você pode modificar seus layouts de tabela, seus rótulos de linha e coluna em segmentações e gráficos dinâmicos, tudo porque agora você está trabalhando com os dados em dinheiro. E há algumas razões pelas quais você pode querer fazer isso, uma delas é reduzir significativamente os tamanhos dos arquivos, especialmente se você estiver lidando com tabelas grandes que não precisam ser atualizadas ou atualizadas ao longo do tempo. Então, para fazer isso funcionar, precisamos ajustar algumas configurações muito específicas. Eu diria que temos uma tabela dinâmica básica como esta para esta demonstração. Vamos ver os salários de São Francisco. Então, estamos olhando para o salário base médio para 2011 2012 e o total geral. E se perfurarmos nossa tabela dinâmica, as ferramentas são opções. Menu. Olhe para a guia de dados. Você verá três opções diferentes aqui no menu de dados da tabela dinâmica, e queremos marcar as 2 primeiras caixas aqui. O 1º 1 salvar os dados de origem com arquivos garante que o dinheiro da tabela dinâmica seja salvo com sua pasta de trabalho. Em segundo lugar, habilite mostrar detalhes. Esta é basicamente a sua linha de vida no caso de você precisar que os dados de origem de volta, porque o que ele permite que você faça é clicar duas vezes em qualquer célula total não filtrada na tabela dinâmica como esta, e recria toda a tabela de origem do zero de dinheiro em uma nova planilha. Vou mostrar-te exactamente o que isto parece. Um excelente, mas obviamente muito, muito poderoso, ferramenta muito útil para ter em mente. Agora, quanto aos casos de uso comuns, há dois que vêm à mente primeiro, como mencionei, como mencionei, removendo dados estáticos de origem para reduzir o tamanho do arquivo ou aumentar a velocidade de processamento. E segundo, é uma ótima maneira de limitar a acessibilidade, a fim de impedir que os usuários realmente acessem ou alterem os próprios dados brutos enquanto ainda permitem que eles explorem ou analisem esses dados através da interface da tabela dinâmica. Então vamos saltar para uma pasta de trabalho de dica profissional que vai mostrar exatamente como funciona essa tabela dinâmica dinheiro . Tudo bem, então se você quiser seguir adiante, abra sua pasta de trabalho de dicas profissionais, vá para o conteúdo da tabela e vamos aprofundar a demonstração de remoção e reanimação de dados que forçou nossa demonstração em uma tabela dinâmica cinza seção dicas Clique no link. E o que você vai encontrar é uma tabela de tabelas de dados chamada SANFRAN salários. Temos nomes de funcionários por ano, seu cargo e duas métricas que estamos trabalhando aqui com pagamento base e pagamento de horas extras. E se eu apenas usasse a seta de controle para baixo atalho para saltar para o fundo, eu poderia ver que nós temos pouco mais de 16.000 linhas ou observações de dados aqui 16.085. Agora mantenha esse número na sua mente, porque vamos voltar a esse número daqui a pouco. Então não é uma grande quantidade de dados, mas é considerável o suficiente para tipo de salvar algum tamanho de arquivo se eu fosse capaz de tipo de limpar isso fora. Então a idéia aqui é criar um pivô baseado nos dados de origem e, em seguida, livrar-se da tabela em si para que possamos apenas trabalhar com dinheiro e reduzir o tamanho do nosso arquivo. Então, com qualquer célula aqui na tabela selecionada indo para inserir Tibbett, solte em uma nova planilha e vamos apenas nomeá-lo algo significativo. Clique duas vezes. Talvez seja amigo da areia. Dê isso. Lá vamos nós. Vamos apenas criar uma visão básica aqui, mas um ano em filtros, talvez títulos de emprego em Rose base pagam em valores. E vamos mostrá-los como uma média aqui parece bom, e, ah, ah, quando filtramos para baixo apenas 2012 tudo bem, então eu tenho um bom ponto de partida aqui. Layout básico da tabela dinâmica. Não se preocupe muito com os detalhes. O que realmente precisamos ter certeza é que nossas configurações são especificadas de uma forma que nos permitirá remover ou excluir essa tabela de origem real. Então vamos para as ferramentas de tabela dinâmica. As opções analisam a guia de dados, e essas três caixas de seleção são as que nos importam agora. número um salvou os dados de origem com o arquivo Marque essa caixa porque, sim, queremos que o dinheiro seja salvo com este número da pasta de trabalho para habilitar os detalhes do show. Marque essa caixa porque, sim, essa é a nossa linha de salvação no caso de precisarmos ter nossa fonte informada de volta. E, em seguida, esta terceira caixa atualizar dados ao abrir o arquivo que quando você não precisa verificar porque atualizar os dados não será possível uma vez que excluímos esta tabela de origem porque o Excel não será capaz de encontrar a tabela ou intervalo de células a partir do qual tabela dinâmica foi criada. Então não terá nada para atualizar. Na verdade, se você tentou atualizar alguns dos outros pivôs ao longo deste curso, você pode ter recebido um erro de referência inválido. E isso é exatamente o que você vê aqui porque o que eu fiz foi retirar todos os dados de origem para reduzir o tamanho do arquivo para esta pasta de trabalho do projeto. Então vamos em frente e pressione OK. Aqui. envios parecem bons. Agora veja isso. Vá para nossos dados de origem reais. Podemos clicar com o botão direito do mouse na guia e excluir a guia inteira contendo essa tabela inteira de dados. Voltamos para San Fran. Pivot, confira. Talvez não queiramos 2012. Os dados podem querer 2011. Boom. Parece bom. Vamos fazer o pagamento de horas extras também. Vamos ver isso como uma média. Não é um filho. Parece bom. E então, ei, em vez de títulos de trabalho, é polos em filtros e realmente trazer o nome dos funcionários de nossa lista de campos. Lá vamos nós. Então, como você pode ver, somos capazes de modificar e jogar apenas com este pivô exatamente como faríamos se nossos dados de origem ainda existissem. Mas a nossa conta desapareceu. Foi completamente apagado, e é por isso que é tão poderoso trabalhar com dinheiro quando você está lidando com mesas estáticas como esta. Agora tenha em mente se fomos para ferramentas de tabela dinâmica tentando atualizar. Aqui está o erro que estamos recebendo. Referência não é válida porque X L não consegue mais encontrar a tabela Sanford e salários, e tudo bem, porque o que podemos fazer aqui é claro. Quaisquer filtros que temos como este ou você pode realmente ir para a opção clear e dizer Limpar quaisquer filtros E não importa mesmo como a nossa lista de campos se parece. Os valores estão aqui, o que estamos olhando para os rótulos Roar COLUMN Tudo o que precisamos fazer é pular para qualquer célula total . Seja 15 7 18 ou C e clique duas vezes e veja o que acontece Nós criamos em nova folha. No meu caso, chama-se ela 12 e inclui todos os dados fonte, o ano do nome, o cargo baseado, pagando o pagamento de horas extras e confira se percorrer todo o caminho até o final 16.085. Então, revivemos todos os dados de origem usando o recurso Detalhes DoubleClick. Então, a partir daqui, talvez nós adicionemos algumas novas colunas calculadas. Talvez possamos empilhar alguns dados que precisamos. Podemos atualizar nosso pivô agora que nossa tabela existe novamente e, em seguida, ir em frente e excluí-la mais uma vez. Então, obviamente, uma ferramenta incrivelmente poderosa e uma com a qual muitos usuários do Excel realmente não estão muito confortáveis trabalhando. Então aí você tem que é o seu curso intensivo sobre o dinheiro da tabela dinâmica e como ele pode ser usado para realmente excluir e, em seguida, reviver seus dados de origem. 14. Como adicionar listas de classificação personalizada: Quero compartilhar com vocês uma dica de mesa dinâmica de cinco estrelas. Vamos falar sobre a organização de campos usando listas de classificação personalizadas. Agora, a razão pela qual esta é uma dica de nível de especialista não tem nada a ver com o nível de dificuldade. Na verdade, é muito fácil de implementar e tudo a ver com o fato de que 99% dos usuários do Excel por aí simplesmente não têm idéia de que essa opção existe. Portanto, por padrão, Excel tem um número construído em listas de classificação para ajudar a organizar campos que não podem ser classificados com regras tradicionais. Coisas como nomes de semanas, dias e meses, que não seguem qualquer tipo de lógica alfabética ou alfabética inversa. Você só tem que saber que segunda-feira vem antes de terça. Março vem antes de abril, e assim por diante e assim por diante, e é exatamente para isso que as listas de classificação personalizadas são projetadas. E nesta demonstração, vamos usar um processo muito semelhante para classificar medalhas olímpicas porque, assim como nos dias de semana, há uma ordem muito particular aqui que não necessariamente segue nenhuma regra padrão. Então o que vamos fazer é ter que arquivar opções avançadas e, em seguida, perfurar todo o caminho até a seção geral e clique neste botão editar lista personalizada onde ele diz criar listas para uso em classificações e preencher sequências. E o que vamos fazer é criar uma nova lista e dizer ao Excel, , essas medalhas olímpicas aéreas, elas são significativas. Eu sempre quero que o ouro seja o primeiro, seguido de prata, depois bronze, depois n A. E uma vez que você tenha salvo essa lista e você retornar aos seus dados ou sua tabela dinâmica agora, essa classificação personalizada terá precedente sobre as regras de classificação alfabética padrão. E você pode usar esse mesmo velho uma opção tonto para agora classificar na ordem que você definiu ferramenta tão útil, especialmente se você tem um monte de itens e você não pode clicar e arrastá-los manualmente para a ordem que você precisa. Então casos de uso comuns quaisquer campos que não podem ser classificados com meios tradicionais como tamanhos de vestuário pequeno, médio e grande ou como vemos aqui, medalhas olímpicas de ouro. Silver Bronze também pode ser realmente útil para criar calendários fiscais personalizados para substituir a classificação de meses padrão. Assim, por exemplo, se o ano fiscal começar em maio, você poderá criar uma lista personalizada para definir a ordem mensal para o ano fiscal específico. Então vamos saltar para o Excel e praticar realmente criar e aplicar uma dessas listas de classificação personalizada . Tudo bem, agora, se você quiser me acompanhar, vá em frente e abra seu livro de dicas profissionais, vá para o seu índice e procure as listas de classificação da alfândega demonstração de cinco estrelas no Pivot cinza Seção de dicas de tabela. E quando você ligar para aquela ovelha o que, você deve ver seus dados dos Jogos Olímpicos de Verão de 2016 e nós estamos olhando para a contagem de medalhas , a contagem de medalhas concedidas com base no tipo de metal ouro, prata, bronze ou qualquer, e também com base nesses esportes. Agora o desafio aqui é que este campo de metal e coluna B tentam classificá-lo em ordem alfabética . Um tonto. Acabamos com bronze em cima e prata em baixo, o que não faz muito sentido. A mesma história se revertermos o dia Tizzy. Agora só temos prata em cima, seguido de qualquer e, em seguida, ouro e bronze. Então, temos duas opções. Em casos como este, Opção um é selecionar os campos, mouse sobre a borda até obter a seta de quatro pontas e clique e arrastá-los para uma ordem diferente e isso é bom. Para casos como este, apenas quatro itens no campo. Eu sou definitivamente viável. A outra opção, que é um pouco mais escalável, é criar uma lista de classificação personalizada. Então deixe-me mostrar como exatamente isso funcionaria. Nós vamos entrar em nosso arquivo tam perfurar nossas opções, tivemos que avançar e então rolar quase todo o caminho até o fundo até você chegar a esta seção geral. E o último item na seção geral diz criar listas para uso em classificações e sequências de preenchimento , Editar listas personalizadas. Isso é o que você quer verificar e você chegar a esta caixa de diálogo Aqui você pode ver aquelas listas personalizadas de nome de semana, dia e mês que eu mencionei anteriormente já cozido aqui e para se destacar . E tudo o que vamos fazer é adicionar uma nova lista. Vamos digitar a ordem das medalhas separando cada linha usando a tecla Enter. É uma prata dourada. Digite bronze, digite um A e vamos em frente e clique. Adicionar e é adicionado essa lista aqui em uma janela de lista personalizada pressione OK em. Está bem, outra vez. Agora, quando voltarmos ao nosso pivô, vocês notarão que ele não foi classificado automaticamente, então você pode precisar voltar para aquela coluna e agora pressionar um tonto e ver como ele classifica. Agora é ouro, prata, prata, bronze e um porque a lista personalizada que acabamos de criar tem precedentes. E nós basicamente anulamos a ordem que teria sido gerada usando a classificação alfabética tradicional . Qual caso Bronze teria aparecido no topo. Então, aí está. Este foi um exemplo bastante simples, mas uma ótima dica para manter no bolso de trás sempre que você tiver uma lista que segue uma ordem de classificação não tradicional. 15. Resolva fórmulas de ordem e lista: Tudo bem para esta dica profissional. Quer falar sobre como alterar essa ordem de resolução para priorizar seus cálculos de tabela dinâmica. Agora esta é uma dica de nível de cinco estrelas, especialista. As probabilidades são que isso é algo que você pode nunca precisar usar, mas é uma ferramenta útil para ter em seu bolso traseiro apenas no caso. Então, onde isso entra em jogo é se você está pivô. A tabela contém vários itens calculados e campos, que podem se sobrepor. Nesses casos, você usaria essa caixa de diálogo Ordem de resolução para determinar a prioridade na qual essas fórmulas são avaliadas. Assim, a partir de suas ferramentas de tabela dinâmica, você pode soltar no menu de itens e conjuntos de campos e clicar no botão ordem de resolução acessar essa caixa de diálogo agora dentro da janela da caixa de diálogo, a última fórmula nessa lista substituirá aqueles acima. Vou mostrar-vos um exemplo de porque é que isso é tão importante na nossa demo. Em apenas um segundo agora, você deve ter notado que dentro desses campos, itens e conjuntos caiu. Há outra opção. Eles são chamados de fórmulas de lista, e o que fórmulas de lista realmente faz é gerar em nova guia no Excel para documentar todos os seus itens calculados e campos, bem como essa ordem de classificação. Então, isso pode ser uma ferramenta realmente útil para fornecer documentação para seus usuários finais, especialmente se você estiver lidando com tabelas muito complexas. Então, recapitulando aqui, caso de uso número um determinando quais cálculos devem ter prioridade em casos conflitantes e número dois novamente. Se você precisar produzir alguma documentação para suas tabelas dinâmicas, especialmente se eles contêm um grande número de itens calculados e campos que listam fórmulas , Ferramenta é uma ótima maneira de fazer isso. Então, com isso, vamos entrar em nossa demo e excel, e eu vou mostrar exatamente quando, por que e como essa ferramenta de ordem de resolução pode ser usada. Tudo bem, então vá em frente e abra seu livro de dicas profissionais. Se você estiver acompanhando, vá para o seu índice e estamos procurando a seção Cinza dicas de tabela dinâmica aqui , especificamente a ordem de resolução e a demonstração de fórmulas de lista. Então vá em frente e ligue para aquela ovelha, e o que encontramos é o que parece ser um layout de tabela dinâmica bastante simples construído em nosso banco de dados de filmes I.M. I.M. D. B. Estamos olhando para os valores de receita bruta aqui, discriminados por idioma em regiões de rosas em capturas de colunas. Também temos alguns itens calculados aqui em jogo. Especificamente, este cálculo percentual de inglês no Roast Sete e este cálculo regional da América do Norte aqui na coluna D. Agora, para ver o que está acontecendo aqui, podemos perfurar nossas ferramentas de tabela dinâmica, campos, itens e conjuntos e abra nossa caixa de diálogo de ordem de resolução. Aqui. Você pode ver que há de fato dois desses itens calculados que estão presentes em nosso layout percentual Inglês, que está tomando os valores de Inglês e dividindo pelo total em todos os quatro idiomas Inglês, Francês, Japonês e Espanhol. E então temos este cálculo regional da América do Norte que está tomando os valores do Canadá e adicionando-os aos valores dos EUA. Agora lembre-se de como esta caixa de diálogo de ordem de resolução funciona. O valor em uma célula é determinado pela última fórmula na ordem de resolução. E agora essa fórmula da América do Norte vive abaixo do cálculo percentual do inglês. Então, a América do Norte neste caso, terá prioridade. Então vamos fechar esta caixa. Voltar ao nosso pivô, mostrar o que está acontecendo aqui. Então, neste ponto são itens calculados por cento Inglês. É tecnicamente suposto impactar as três células que são destaque em verde B 7, C 7 e D 7 ao mesmo tempo são América do Norte. O cálculo está afetando essas células d 3456 e 7 e observe o Intersect aqui, que é uma célula que é D 7. Então você tem duas coisas conflitantes acontecendo aqui em D 7. Você é inglês por cento. Cálculo está dizendo Excel, Ei, pegue este valor em D três e dividido pela soma de D 3 36 e me dê a porcentagem. Ao mesmo tempo. O cálculo da América do Norte diz: “ Não, “ Não, não, não faça isso. Pegue o valor em B sete e adicione-o ao valor em C sete Agora ambos os cálculos não podem ser verdadeiros. Não pode acontecer ao mesmo tempo, e é exatamente aí que a ordem de resolução entra em jogo. E lembre-se que porque a fórmula da América do Norte ocorreu mais baixa na lista, essa tem prioridade. E, na verdade, é exatamente isso que está acontecendo aqui. Estamos apenas adicionando essas duas porcentagens, que gera 1 98,69% e isso realmente não faz sentido aqui, porque a porcentagem do cálculo em inglês deve ser uma porcentagem abaixo de 100%. E, em teoria, esse deve ser realmente o único a ter prioridade aqui, oposição ao cálculo da América do Norte. Então, tudo o que precisamos fazer é voltar para nossas ferramentas, sentir itens e definir ordem resolvida, e vamos fazer este cálculo inglês ou percentual de inglês, e não podemos arrastar. Ele irá clicar no botão Mover para baixo, movendo-se todo o caminho na parte inferior, para que ele sempre tenha prioridade. Então vamos em frente e clique em Fechar, e agora confira. Esse valor muda de 1 98 para 99,94, que é o cálculo correto baseado nessa lógica percentual em inglês. Então essa é a ordem de resolução e por isso é importante. A última coisa que eu vou mostrar rapidamente é a opção de fórmulas de lista, que você pode acessar aqui mesmo a partir do mesmo menu, campos, itens e conjuntos. Quando você clica nessas fórmulas de lista, Excel criará uma nova planilha para você que lista todos os campos calculados todos os itens calculados, bem como a ordem resolvida específica na qual essas fórmulas são avaliadas. Então, novamente, ferramenta de documentação muito útil, especialmente quando você está lidando com tabelas dinâmicas muito confusas ou complexas Então vamos em frente e excluir essa planilha. Não precisamos da documentação agora, e aí você está usando as ferramentas de ordem de resolução e lista de fórmulas.