Tabela Dinâmica no Excel do Básico ao Avançado

fala impressionador beleza eu sou o Fred e na aula de hoje eu vou te ensinar tabela dinâmica do básico até o avançado para você saber tudo sobre essa ferramenta mega importante do Excel Então se quiser aprender sobre isso dá o seu like e fica aí que eu te explico rapidinho e para te explicar sobre o funcionamento da tabela dinâmica eu vou usar essa base de dados que vai est aqui embaixo na descrição do vídeo você pode clicar baixar e fazer exatamente as mesmas coisas que o fizer aqui na aula no seu computador é até bom para você praticar Tá bom eu tenho essa base de dados aqui que tem informações sobre vendas data vendedor produto forma de pagamento preço unitário quantidade de produtos vendidos e o valor total se você tem uma tabela assim desse jeito você consegue analisar as informações apenas linha a linha mas você não consegue fazer análises mais complexas você não consegue descobrir quais são os melhores vendedores os produtos que mais vendem né Qual que é a média de venda de cada forma de pagamento de cada produto tá então a tabela dinâmica é uma ferramenta que te possibilita isso te possibilita fazer várias análises Então como que eu faço para criar a tabela dinâmica só clicar em qualquer célula dessa tabela tá dessa base aqui eu vou inserir tabela dinâmica E aí ele vai perguntar se você quer inserir uma tabela dinâmica em uma planilha que já existe ou em uma nova planilha o legal dessa opção de Nova planilha é que fica uma planilha apenas com a sua tabela Então você tem ali o foco Total nela e nas análises que dela vão sair vou usar essa opção só dar um ok e tá lá tá criada minha tabela dinâmica repara uma coisa ele criou esse quadradinho aqui nesse retângulo e aqui do lado onde a gente tem Campos da tabela dinâmica a gente tem os títulos das colunas da nossa base repara ó na nossa base a gente tem as colunas data vendedor produto forma de pagamento preço unitário quantidade de produtos e valor total certo se você olhar aqui na tabela dinâmica você tem exatamente os mesmos Campos data vendedor produto forma de pagamento preço unitário Quantidade produzida e valor total tá então a gente tem exatamente os mesmos Campos e é uma forma que a gente tem de manipular as informações que estão nessa base aqui na tabela dinâmica Então olha só se eu quero quero descobrir quais são os vendedores que mais vendem primeiro de tudo eu vou pegar o campo vendedor e eu vou arrastar ele para esse quadradinho aqui ó que tem linhas Tá eu vou realmente arrastar eu vou clicar em vendedor e vou arrastando até aqui embaixo Olha só vendedor e soltei em linhas se eu soltei em linhas significa que ele vai escrever todos os vendedores que existem na minha base e colocar cada vendedor em uma linha diferente ó lá ó o o Anderson tem uma uma linha Camila tá em outra a Clara tá em outra e etc se eu tivesse colocado em colunas ó eu vou clicar e agora eu vou soltar em colunas ó lá agora ele tá escrevendo cada nome em colunas diferentes Beleza eu vou colocar em linhas porque eu acho que fica mais visual E lembra que eu quero descobrir quanto que cada um vendeu para saber quais foram os que venderam mais e que venderam menos eu vou pegar o campo de valor total né Afinal é o campo que eu tenho o total de cada venda vou clicar nele e vou soltar em valores Olha que interessante colocando em valores ele faz o quê o somatório de todos os valores para cada vendedor então eu tô vendo aqui que o Anderson vendeu essa quantidade aqui de dinheiro a Camila vendeu essa quantidade a Clara vendeu essa quantidade tá então a gente já consegue descobrir quem foi que vendeu mais ó a Diana e quem foi que vendeu menos foi o Felipe tá Então pessoal o básico da tabela dinâmica é esse é você montar uma tabela dinâmica E aí você conta com os campos linhas colunas e valores tá E você também tem o campo filtro qual que é o interessante do filtro eu vou botar aqui o campo produto tá vou arrastar produto e vou soltar em filtros o filtro pessoal ele não muda a estrutura em si da tabela mas ele filtra os valores ou seja se eu selecionar um produto aqui do filtro eu posso ver a análise apenas para esse produto Então agora eu vou selecionar apenas o produto a e eu vou ter a mesma análise ou seja quanto que cada vendedor Vendeu mas considerando apenas os produtos o produto a ó lá ó Ok repara a estrutura é a mesma mas os valores mudaram porque agora só tá considerando quanto que eles venderam do produto a posso selecionar dois produtos três 4 enfim posso fazer a análise que eu quiser qualquer combinação aqui que eu queira tá então isso daqui é o básico é você saber montar uma tabela dinâmica agora vamos começar a melhorar isso daqui olha uma coisa que na verdade eu vejo muita gente fazendo errado é o seguinte é você querer formatar isso daqui como por exemplo moeda tá então você vai selecionar os valores aqui e vai formatar como moeda tá vendo se você fizer isso na verdade você não tá formatando o campo de valor da tabela dinâmica você tá formatando o intervalo de células que você selecionou então Ó eu selecionei esse intervalo aqui que vai de B2 de perdão de B4 até B9 tá então é apenas esse intervalo que tá formatado o que que acontece se eu colocar outra informação na tabela dinâmica então por exemplo Além do valor total eu vou colocar quantidade de produto Se eu colocar assim ó quantidade de produto embaixo da soma de valor vai aparecer aqui tá vendo e ele já até formatou essa quantidade como moeda tá vendo é esse o tipo de problema que a gente tem a quantidade não era PR ser moeda Tá mas como eu formatei aquele intervalo de célula e ele criou alguma coisa do lado né ele já replicou a formatação tá então quando você for formatar alguma coisa na tabela dinâmica nunca formate ah direto nas células tá você vai fazer o seguinte vou tirar a formatação que eu tinha colocado vou voltar pra formatação numérica que eu tinha antes e agora ó vou tirar a quantidade de produto também e eu quero formatar apenas o valor total Olha o que que eu vou fazer ao invés de selecionar essas células e formatar eu vou vi aqui em valores tá afinal de contas é é esse o campo que eu quero formatar né o total do SOMA do valor total e eu vou em configurações do campo de valor lá eu vou em formato do número e eu vou selecionar a opção de moeda aqui sim tá cliquei em Ok e beleza agora olha o que que acontece se eu colocar quade de produto coloquei embaixo repara que agora ele não confundiu a quantidade de produto não veio formatada como moeda veio formatada como número normal por quê Porque eu formatei do jeito certo eu Formatei o valor dentro da tabela dinâmica e não na célula Onde está aplicada a tabela dinâmica Esse é um truque que evita muito retrabalho Eu já vi muita gente criando tabela dinâmica E aí formata um campo e aí vai criando outro e outro outro campo e depois tem que ir formatando de novo né né você tem retrabalho ou seja você tem que formatar desformatar formatar desformatar o tempo todo e com isso você não tem mais esse problema outra coisa imagina que ao invés de eu ver os a soma do valor total eu queira ver a média de venda o ticket médio em média quanto que o cada vendedor vendeu tá eu posso vir aqui em soma do valor total e eu vou de novo em configuração do campo de valor e lá vão ter algumas opções por padrão ele tá colocando soma Mas eu posso fazer a contagem né ou seja contar quantas vendas aconteceram e eu posso fazer a média também tá além além disso eu ten outras opções como produto máximo e mínimo eu vou fazer aqui a média do valor de venda Tá então vamos ver ó lá ó agora eu tô vendo a média de venda para cada vendedor eu consigo descobrir que em média né as vendas da Camila e da Diana são maiores às vezes quando elas estão vendendo elas conseguem oferecer um um outro produto então na mesma venda elas vendem mais e acaba que a média de venda fica maior Talvez isso poderia ser feito né Essa mesma técnica poderia ter sido feita pela Clara que tá vendendo eh que tem um valor médio de venda menor do que essas duas tá enfim quantidades enormes de análises que a gente consegue fazer da mesma forma a gente consegue ver as vendas máximas e mínimas tá então ó ao invés de média de valor total eu vou colocar máximo Tá então Ó vou em configuração de campo de valor eu vou colocar máximo ó lá ó vocês viram que todos têm o mesmo valor máximo Tá Apesar deles terem uma média diferente um somatório diferente todos têm um valor máximo Tá então se você pegar todas as vendas desses vendedores o valor máximo que foi vendido para cada um é 2277 vamos fazer uma coisa eu quero atualizar agora a minha tabela dinâmica imagina o seguinte eu vou na minha base e eu Tô vendo que a maior venda por exemplo do Anderson é 2277 assim como todo mundo né eu vou aqui voltar na minha base e eu vou procurar uma venda do Anderson achei ó eu vou pegar esse valor total aqui e eu vou mudar ele tá eu vou botar um valor total maior do que aquele que a gente tinha lá vou botar 5. 000 tá agora a maior venda do Anderson não é mais 2277 é 5. 000 tá vamos ver como é que tá na tabela dinâmica a tabela dinâmica não mudou continua aparecendo a mesma venda do Anderson tá como que eu faço pra tabela dinâmica refletir a mudança que eu fiz na base eu tenho que clicar com o botão direito e atualizar vamos ver não mudou porque eu esqueci de tirar os filtros aqui ó tá tá filtrando apenas esses produtos e aqui essa venda que eu coloquei do Anderson ó é de um produto H tá que ele não tá filtrado então por isso que não funcionou eu vou marcar todo mundo pronto agora vai ser maior venda considerando todos os valores e agora sim ó tenho a venda do Anderson como maior tá a venda máxima do Anderson é 5.

000 ao contrário dos outros então Eh é assim que a gente atualiza a tabela dinâmica tá a gente sempre tem que voltar na base né quando for mexer alguma coisa Você volta na tabela dinâmica Clica com o botão direito e atualiza e uma coisa importante que muita gente não dá o devido valor eu selecionei quando eu selecionei aquele intervalo tá para criar a tabela dinâmica né o intervalo dessa base aqui para criar a tabela dinâmica eu fui da primeira linha até a última que é essa aqui beleza por padrão o Excel faz isso O que acontece se eu colocar um novo dado aqui embaixo esse dado ele a princípio não vai aparecer na tabela dinâmica deixa eu mostrar para você ó vou botar uma venda no dia 1eo de janeiro de 2023 Tá vou botar os mesmos valores aqui em cima Beleza mas agora eu tenho uma venda em primeiro de janeiro de 2023 botar até um valor maior aqui para se destacar legal agora eu vou atualizar a tabela dinâmica tá E antes disso eu vou abrir as vendas por mês então Ó eu tô tirando os dados da tabela dinâmica E como que eu tiro os dados da tabela dinâmica É só você clicar e jogar para fora tá vendo você tá realmente jogando fora os valores vou pegar datas tá E ele tá abrindo todas as datas repara que ele vai até a última data lá da tabela dinâmica que a gente tinha beleza foi até dia 31/12 e vou botar o valor por data também legal se eu clicar com o botão direito atualizar vamos ver se aparece aquela venda do dia 31 do dia 1eo de janeiro de 2023 não aparece por que que não aparece quando você clica uma tabela dinâmica aparecem duas guias aqui em cima ó lá se eu não tô com uma tabela dinâmica selecionada não aparece e agora se eu selecionar aparecem essas duas guias são guias que tem informações específicas sobre tabela dinâmica tá na análise da tabela dinâmica a gente consegue descobrir a fonte de dados ou seja aonde tá a base que gerou essa tabela se você vi em alterar fonte olha qual que é a base que a gente tá usando ó na planilha base da célula A1 até a célula g981 até a célula G né e a linha 981 vamos ver a linha 981 é essa aqui e a informação nova que a gente colocou tá na linha 82 isso daqui pessoal pode causar um problemão para você imagina que você tem um relatório que funciona a partir de uma tabela dinâmica E aí você vai adicionando novas informações aqui você tem a sua tabela dinâmica funcionando ainda só que Conforme você atualiza essas informações não aparecem lá então você pode ter os seus dados desatualizados tá como que eu faço para resolver isso ao invés de você criar uma tabela dinâmica a partir de um intervalo com linha de iní iní e final definido você vai fazer o seguinte ó eu vou excluir isso daqui excluir a tabela dinâmica a planilha toda e antes de inserir a tabela dinâmica eu vou formatar a base como tabela ó inserir tabela tá eu inseri formatei isso como tabela vou tirar a formatação aqui botar uma formatação mais singela Pronto agora isso daqui é uma tabela e a tabela pessoal ela vai aumentando automaticamente Olha só eu vou seria a partir dela a tabela dinâmica Tá vou botar datas legal e valor total então a última data que tá aparecendo aqui agora é dia 1eo de janeiro de 2023 agora se eu colocar uma outra informação aqui no dia 2 de Janeiro 2 de janeiro de 2023 ó 2 de janeiro de 2023 botar a mesma informação eu vou atualizar a tabela dinâmica você vai ver que a data 2 de janeiro de 2020 três vai aparecer automaticamente aqui tá você não precisa ficar alterando aonde começa e termina a sua tabela dinâmica o tempo todo maravilha agora a gente pode colocar também mais de um campo em linhas e em colunas tá a gente já tinha colocado é valor total e quantidade de produtos em valores Mas se você quiser você pode fazer o seguinte ó vou tirar a data vou colocar vendedor e embaixo de vendedor produto vai aparecer Anderson e todos os produtos que o Anderson vendeu Camila todos os produtos que a Camila vendeu e assim por diante tá se eu colocar o valor total eu vou ter um relatório mais completo Ó o Anderson no total vendeu 90. 000 tá sendo que desses 90. 000 7.

000 foi do produto a 13. 000 do produto B 4.