Realmente para você se destacar um novo ano já deixando também para você e paraa sua família o desejo de um Feliz Ano Novo feliz abençoado ano novo para você muito obrigado a todos os nossos alunos nossos clientes todos que nos acompanharam nesse ano ok então é isso vamos ao nosso vídeo vamos entender primeiro a nossa base de dados a nossa base de dados é composta aqui por uma tabela essa tabela tem o nome de T produtos e nela anemos a ID de produto produto a marca o preço o.
Link da imagem caso a gente vai acabar nem usando custo médio dela a categoria dele você tem a imagem quando você tivesse alguma imagem poderia colocar aqui uma função de imagem colocar imagem ali quantidade de vendas as vendas o valor de vendas Total faturamento o valor total de cm custo médio tá aí eu vou fazer aqui a quantidade de vendas vezes o custo médio tá E aqui em vendas eu tenho preço vezes a quantidade de vendas e aqui em Venda menos cm é seria o resultado antes de despesas antes de.
Impostos tudo mais é simplesmente as vendas menos o custo médio Só isso tá só isso mas já te disse quanto é que deu por exemplo aquela operação aquela venda ali é o indicador e Aqui nós temos o mês e o ano onde aconteceu essa venda tá então essa aqui é a nossa base de dados ela tá preparada desse jeito aqui e a gente vai fazer agora os nossos cálculos deixar para que a gente possa então utilizar no nosso dashboard então aqui a base vai ser sempre aqui quando quiser uma atualização alterações aqui sempre nessa tabela aqui então vai ficar nessa.
Planilha planilha base em cálculos é onde a gente vai inserir Então os nossos cálculos serão aplicados no dashboard então primeira coisa que eu vou aumentar aqui pra gente a gente vai trabalhar basicamente com tabela dinâmica hoje você vai clicar em inserir tabela dinâmica vou selecionar a nossa base aqui vou selecionar a base toda então ficou T produtos ali vou dar um ok e pronto então ele trouxe para cá nessa primeira a gente quer a quantidade vendida Esse vai ser o indicador que a gente vai botar lá uma e somente um rótulo lá que.
Então 20 tá 1000 x 1000 tá 20 milhões 300 x 1000 ou seja 20 milhões 300 1000 Tá bom então essa aqui quantidade total de vendas quantidade de vendas agora a gente vai fazer o faturamento no faturamento eu vou copiar essa tabela vou colar para cá e aqui em quantidade eu vou tirar então certo vou tirar ela daqui e vou colocar apenas o total de vendas Então vou pegar aqui valor de vendas vou puxar para cá e tá aí o nosso total de vendas nota também que ele Já copiou a nossa formatação certo então já ficou certinho ali no valor cm a gente.
Vai ter então o custo médio total que a gente teve na operação Então a gente vai copiar essa tabela vou colar para cá e aqui soma de vendas vou tirar e aqui valor de custo médio vou puxar para cá de novo e aí nós temos o nosso custo médio tá então tá indo bem fácil bem rapidinho né mais para frente a gente vai ter um pouco mais de trabalho tá Mas aqui por enquanto são só Eh esses indicadores eu quero só o total e depois a gente vai poder aplicar filtros nele lembrando continuando Então a gente vai ter agora o valor vendido menos o cm para isso eu vou vir aqui e vou copiar.
Para cá valor vendido menos cm eu vou colocar então eu vou mudar ele aqui né eu ten aqui ó valor venda menos cm tá aqui o valor seria como fosse um lucro direto né então seria ó já dá para notar que é esse menos esse vai dar esse valor aqui ok produto mais vendido aqui a gente vai fazer uma tabela diferente aqui a gente vai ter mais a dimensão de linha vou copiar ele aqui tá mesma tabela porque puxa os mesmos dados e aqui eu vou tirar então algumas informações o valor de venda vou tirar daqui a o valor sem né.
Vou colocar o valor de venda valor total de venda então valor total de venda e vou colocar também o produto o produto eu vou colocar em linhas então aqui a gente tem todos os produtos que a gente teve as vendas ali tá panto aqui e eu vou organizar ele vou classificar de Za o do mais vendido né mais opções de classificação decrescente por soma de valor de vendas vou dar um ok e agora ele vai mostrar então qual foi o o produto mais vendido então ele vai.
Aparecer aqui para cima OK então você pode colocar assim e você pode também fazer um filtro né Colocar aqui um top um se eu quero só o produto mais vendido eu posso fazer um filtro aqui ó filtro maior está entre está entre aqui mostrar os quais paraos quais rótulo filtro de rótulos filtro de valores 10 primeiros isso que eu tava procurando você vai colocar um aqui ó tá entre os primeiros um então ele vai puxar sempre o primeiro botão direito.
VEJA MAIS AQUI SOBRE EXCEL:
- Planilha de Cálculo de Fretes Cubados por Caminhão em Excel 6.0
- Como fazer uma boa apresentação no Power Point (Dicas de Slides)
- Como fazer um Cadastro de membros completo
Excel Online - Configurações de campo de valor aliás botão direito opções tabela dinâmica totais e filtros desmarca os totais e
Vai ficar somente assim é esse valor que a gente vai precisar tá Depois a gente vai montar a informação com esses dois aqui esses dois valores que a gente tem aqui eu vou colocar depois dentro do nosso indicador dizendo qual foi o item mais vendido com aquele filtro que a gente aplicou continuando Então vamos lá paraa quantidade de vendas por mês Ok por mês de novo eu vou copiar aquele.Que é mais fácil esse aqui menorzinho mais fácil para m gente trabalhar vou botar para cá colei ele aqui então e agora a gente vai fazer o seguinte filtro quantidade de vendas eu vou pegar agora por quantidade de vendas Tá total de quantidade mesmo que foi vendido quantidade de garrafas de itens né e agora eu vou pegar o mês então eu vou pegar o mês aqui já tá calculado o mês já tá preparadinho então tá aqui só isso botão direito opções tabela dinâmica tiros totais e filtros vai ficar somente assim tá então aqui o total que nós tivemos de vendas em cada um dos meses a.
Soma quantidade total de vendas tá bom aqui ao lado a gente vai fazer alguma coisa nós vamos fazer um calculo Zinho para ele puxar direto essas informações conforme cada um dos meses e de acordo com os filtros independente do filtro Tá bom então eu vou colocar aqui mês valor e aqui eu vou colocar um até 12 vou selecionar aqui vou arrastar opa vou selecionar com o botão direito vou arrastar preencher série 1 a 12 aqui ao.
Lado eu vou fazer um pr V para para puxar essa informação conforme o mês como eu te disse ele pode estar desorganizado Pode estar filtrado então não quer dizer que a gente vai ah esse vai casar com esse esse vai casar com aquele eu também não quero usar gráficos dinâmicos gráficos diretos na tabela dinâmica tá então eu vou fazer assim ó pro aqui V abr parênteses que tem algumas implicações quando a gente faz aquilo que eu acabei de falar de usar direto tá ponto vírgula 2 ponto vg0 Então fecha o parênteses Então esse proc V aqui vai fazer a informação vai buscar a informação da segunda coluna deixa eu.
Peguei duas três ali n então ele vai puxar da segunda coluna a informação que eu tiver ali tá conforme o item Opa faltou o valor procurado que é o mais importante valor procurado P1 né P5 ali nessa tabela aqui quero que retorne a segunda coluna e eu também vou botar um se erro abre parênteses ponto e vírgula zero então se ele não achar ele vai retornar zero Então esse aqui é um padrão de um proc V onde ele vai bu buscar a informação conforme essa chave que nós temos aqui à direita copiei colei aqui e pronto já temos a.
Informação quero pegar a formatação desse control 1 copio cont control c seleciona aqui control um de novo personalizado cont Crol v e OK tá se eu tiver indo muito rápido você pode também parar pode fazer o download dessa planilha pode comparar tudo aqui eu tô tentando fazer tudo bem eh passo a passo aqui para você mas pode ser que de repente fique um pouco rápido porque aqui a gente tá no YouTube Tem que ser um pouquinho mais dinâmico né mas também no nosso curso a gente faz tudo bem.
Passo a passo bem pausado como eu te disse são 10 cursos que vão desde o Excel básico até o VBA tem um curso de Excel dashboards tudo mais então é uma formação completa com acesso vitalício e lá muito bem didático aqui a gente também tenta ser muito didático também então continuando aqui eu vou buscar essa informação vou buscar aqui já tá pronto né a gente vai utilizar lembra que a gente tá fazendo estudo para utilizar tudo depois então a gente prepara tudo para depois a gente aplicar isso no dashboard Tá bom agora a gente a gente vai fazer então ah valor de venda.
Por mês e ano ok mesma coisa que a gente tem nesse anterior falar a verdade vou copiar esse também copiei vim para cá colei tá só que a diferença que ao invés de quantidade de vendas a gente vai ter o valor de vendas então eu vou tirar esse aqui e vou puxar esse e tá aí valor total de vendas certo já tá pronto na verdade né Eu simplesmente vou copiar essas fórmulas que eu tenho aqui vou passar aqui e vou colar aqui tá bom só isso agora onde ele tá apontando lá ó vou até diminuir aqui para você ver ele não tá apontando para cá no.
Vermelho eu simplesmente vou puxar para cá ó e agora ele já tá apontando para cá então e esse V5 já tá passando para cá porque ele já tava e não tava travado como eu tinha travado esse aqui ele não mudou eh ele não mudou automático né mas a gente precisa travar porque a gente vai copiar a fórmula nem copii aqui colei Pronto ele já pegou os meses ali conforme o mês e ano que a gente vai usar depois no nosso gráfico então aqui a gente tem o valor de venda por cm a gente também vai aplicar ele nesse controle Mas esse vai ser um.
Pouquinho diferente vai ser um gráfico um pouco diferente que a gente vai fazer alguns controles nele tá bom então vai - Excel Online
Ser assim ó primeira coisa que eu vou fazer vai ser o nosso gráfico vou copiar ele para cá copiei ele Nossa tabela aliás Nossa tabela dinâmica colei aqui e pronto agora a gente vai pegar então o valor do venda menos cm tá que ser ele o nosso resultado né ali antes de impostos tudo mais antes de tirar a nossa despesa tudo mais cm tá aqui tá então isso aqui só da C A soma da venda menos cm é esse.Resultado que nós temos aqui colocando pro lado então a gente tem todo esse espaço aqui porque a gente vai fazer alguns cálculos nele então primeiro eu vou fazer aqui na linha eh A5 eu vou escrever controle controle e aqui eu vou botar um número qualquer número que você quiser pode ser 1 2 3 4 5 67 esse número vai fazer com que a gente faça um deslocamento dos dados ali embaixo então ele vai mudando conforme os conforme as a os valores que a gente tem ali abaixo.
Faltou uma coisa faltou o ano aqui ao lado deixa eu puxar o ano também aqui ó ano e eu vou puxar ele aqui ao lado do mês antes do mês ó tá aqui ficou desse jeito assim você vai clicar o botão direito opções tabela dinâmica Prest atenção agora tá por favor desmarcado exibição layout clássico da tabela dinâmica Olha o que vai acontecer tá nesse formato né só que a gente precisa fique colunar então fico do ano e o mês agora de novo eu vou tirar agora os filtros ah os totais de novo né tem que tirar os totais tem que tirar o total na.
Verdade aqui ó configurações e Campo so totais nenhum configurações de campo de novo De valor sob totais nenhum para todos eles tá então se tiver aparecendo ali você tira então ficou assim e nesse aqui eu ainda preciso que fique aparecendo 2022 onde é a linha de 2022 pra gente poder usar depois o nosso cálculo para isso você vai fazer ass configurações de Campo nenhum né e aqui você tem repetir o rótulo de itens vai dar um OK e ele vai repetir então então ficando como se fosse uma tabela ali com os totais de cada mês de cada ano Então.
Isso que a gente precisava muito bem a gente já tem o nosso controle que tá ali em cima tá E esse controle vai fazer que Rode então esses dados no nosso gráfico que a gente vai fazer daqui a pouco eu gosto de fazer os cálculos todos primeiro e depois a gente só aplica aí você vai ver como é que vai funcionar legal esse controle que nós vamos fazer aqui tá então nele aqui a gente vai fazer o seguinte eu vou botar primeiro no campo na célula la7 V botar legenda mês.
Valor percentual de crescimento percentual de crescimento Então tá aqui legenda mês valor e percentual de crescimento então de acordo com o mês que a h de acordo com o mês ele vai aparecer aqui então o valor e quanto que ele cresceu de um mês em comparação ao outro tá então a primeira coisa que a gente vai fazer é o seguinte eu vou fazer um desloque aqui agora se você não souber usar o deslock não tem problema tem no guia do Excel aqui como você utiliza tá tem super aula e tem também no nosso curso.
De avançado tem lá sobre usar desloque mais muito mais avançado que vocês vamos fazer aqui o desloque basicamente ele vai deslocar linhas para baixo ou para cima e pra direita ou pra esquerda e é isso que a gente quer esse controle vai fazer exatamente isso e você já vai notar o que tá acontecendo Então V botar aqui ó abre parênteses eu vou pegar então a célula que eu quero deslocar que é a z5 que é essa aqui de cima vou travar ela então ponto e vírgula L que é a linha onde eu estou menos L abre pares A7 que.
É anterior vamos explicar isso aqui também tá mais esse número do controle que é o F5 que a gente tem aqui em cima Então tá mais o F5 que tá aqui em cima então travei fecha parênteses e dar um enter Opa faltou aqui alguma coisa Ah faltou ponto e vírgula porque o cols também é obrigatório tá então eu vou passar ponto e vírgula vazio vou dar um enter e tá ali dois se eu tivesse zero ia ser o um né E por aí vai ó 1 2 3 4 não tem nada a ver com isso tem a ver com isso ó 1 2 3 4 é o que ele tá deslocando para baixo tá se eu não.
Deslocar zero ele vai pegar então vai pegar a primeira linha que eu tenho aqui então eu vou pegar aqui e vou puxar ele para deixa eu ver quantas linhas que eu tenho aqui 12 linhas deixa eu ver se é isso mesmo 12 linhas Vou puxar ele aqui então tá deixa eu ver se deu 12 deu 12 e daí ele vai mudando ó 1 2 3 4 5 ele tá deslocando conforme essa esses valores aqui tá E é isso que tava fazendo E é isso que a gente vai terminar aqui.
Também no anterior no próximo também Então no próximo a gente vai pegar e vai fazer um pr pode fazer um proc V também ou pode fazer de várias formas essa informação aqui também tá mas eu acho que vai ficar melhor se a gente fazer direto pelo deslocamento também tá vou fazer um desloque aqui vai ficar melhor o desloque vou deslocar pelo Total Então vou pegar esse aqui o desloque total e vou pegar aqui o z5 tá que eu tinha e vou marcar aqui pelo aa5 que é esse aqui do total coloquei aqui e agora a gente.
Aplicou deixa eu aplicar também aqui ó control um copiei esse aqui apliquei de novo control um de novo personalizado e vou botar no geral aqui ok então tá mostrando aqui o valor que nós tivemos e aqui quando a gente muda né 1 2 3 4 0 né E por aí vai ele vai mudando Então os meses ali abaixo OK agora continuando então nós temos o percentual de crescimento na primeira vai ser sempre zero né porque comparar com o q né tem.
Como comparar então e aqui eu vou colocar 1 menos igual 1 Men abre parênteses a e8 menos a ae9 que é dividido por ae9 que é essa daqui que eu tô aqui então nós vamos saber aqui quanto que cresceu quanto que aumentou ou diminuiu conforme o mês atual copiei aqui pronto tá ali percentualmente posso plicar aqui o percentual para aparecer pra gente aqui tá E daí aqui voltando para baixo você vai ver o quanto que aumentou e o quanto diminuiu de um mês em comparação com o.
Outro então aqui ó caiu 1% aqui caiu 20 subiu 22 e por aí vai aqui em legenda a gente vai aplicar uma legenda então eu quero que apareça ali um texto uma legenda aonde vai botar então no nosso gráfico eu quero que fique lá o mês e o ano por exemplo tá então eu quero que fique o mês e o ano vou salvar isso aqui né gente não perder as coisas Opa salvar ele aqui como xlsm tá ok salvar como aula dois só para ficar M.
Fórmula hum não sei porque tá aparecendo isso 3 2 1 continuando então aqui na le legenda nós vamos fazer o seguinte eu vou aplicar ele assim ó igual texto abre parênteses e nele eu vou passar então o valor que eu tô querendo passar como texto vai ser o ad8 que eu tenho esse valor do mês aqui ad8 esse valor que eu tenho aqui ponto e vírgula 0 fecha aspas duplas fecha parênteses quando eu faço isso ele vai ficar com esse 01 na frente então é para isso que a gente tá fazendo agora a gente vai aplicar o ano também tá pra gente.
Aplicar o ano a gente vai pegar o ano pode pegar legendo pode pegar de várias formas essa informação aqui mas eu vou pegar a partir daqui ó a partir de 2022 ali desloque também então pode pegar aqui ó desloque também pode vir para cá e colocar e Comercial aspas duplas traço aspas duplas então isso aqui vai só fazer aqui ó só para você ver vai ficar com tracinho e agora com EC comercial novamente eu vou dar um desloque novamente e aqui ao invés de z5 eu vou mandar deslocar o ano Opa o ano travei.
Né dei um enter e ali ficou 01 2022 até para baixo 2023 2022 E por aí vai então ficou desse jeito aí ó 1 2 3 4 5 Mas eu não quero que fique tão grande também esse 2022 né não queria que ficasse desse tamanhão todo então eu consigo aqui com texto também ou direita também pode fazer tá assim ó direita abre parênteses ponto e vírgula do então eu vou pegar somente os dois últimos caracteres então na função de texto direita a gente consegue pegar só os.
Dois últimos caracteres Por isso que eu digo que é importante a gente conhecer as funções porque com elas a gente pode fazer qualquer coisa a gente pode trabalhar então com essas informações do jeito que a gente quiser né e fazer o que a gente precisa Esse é o foco também do nosso curso da forma como a gente ensina como você pode notar agora ele tá rodando ali se a gente criar um gráfico ele automáticamente vai fazer então essa mudança eh automaticamente ali com os o controle que a gente vai montar depois um desenvolvedor tá continuando então agora a gente vai.
Ter o valor total por produto também a gente vai aplicar um gráfico dinâmico aqui on gente vai usar controles diferentes Aqui também tá dep a gente vai aplicar Esse controle ali deixa eu pegar um outro gráfico aqui qualquer uma outra tabela dinâmica aqui vou aplicar aqui ao lado então h5 e nela a gente vai ter então o valor por produto no valor por produto vai ser produto e venda Então vou tirar aqui o o valor de venda já tá ali vou tirar o mês vou pegar o produto vou puxar para cá e é isso aí esse aqui foi o valor por produto então.
Quanto que a gente tem de vendas por cada um dos produtos tá e aqui a gente pode também fazer essa classificação Então vai ser um gráfico para mostrar Quais são os X Val x itens mais vendidos e a gente pode poder classificar ele ordenar ele da forma que eu quiser eu quero do maior pro menor eu quero o menor pro maior eu quero Qual foi o menos vendido Qual foi o mais vendido então bem legal isso aqui também também que a gente vai montar agora também então primeira coisa aqui em ak4 eu vou botar maior Opa maior e aqui.
Eu vou botar Opa maior e menor e aqui eu vou botar classificação e aqui eu vou colocar então uma não fórmula só botar verdadeiro verdadeiro ou falso então aqui vai ser verdadeiro que nós vamos usar um controle também tá verdadeiro ou falso vai ser aqui então eu vai botar aqui se eu quero classificar do maior para menor do menor pro maior e maior pro menor isso a gente vai aplicar isso dentro da nossa fórmula tá então nós vamos usar esse parâmetro que vai ser.
Mudado depois dinamicamente por controles aqui na nossa função Então a gente vai só aplicar ele aquiar mais para baixo aqui então na linha A na célula h6 eu vou colocar para ele aqui rtd que é quantidade quantidade né e aqui eu vou botar então ah quantos itens eu tenho tá tá então esse quantidade também vai ser um controle para saber quantos itens eu tenho selecionados porque às vezes você foi um filtro de itens e não tem aquela quantidade 10 então para evitar problemas então a gente faz isso aqui se for 10 eh se tiver 10 itens nessa lista.
Retorna a 10 senão retorna a quantidade que tem ali tá então é isso que a gente vai fazer então eu vou fazer assim ó igual C C abre parênteses conte pon valores abre parênteses aí eu vou pegar essa coluna aqui vou botar até é 1000 por exemplo tá acho que não tem 1000 itens não tem 1000 itens mas eu vou botar 1000 itens vou travar aqui então se contecer po L abre parênteses for maior que 10 então 10 sen não conte ponto valores abre parênteses e daí eu.
Vou pegar o mesmo período mesmo valor que eu tenho ali a mesma quantidade que eu tenho tá então é isso fecha fecha e é isso aí então se fosse tiver mais que 10 ele vai puxar 10 se tiver um vai puxar um se tiver dois vai puxar dois até 10 se for mais que 10 ele vai puxar sempre 10 para limitar também essa quantidade muito bem agora a gente vai pro cabeçado da Fórmula produto vai ser aqui Opa produto e valor certo produto e valor e no produto a gente vai usar a seguinte.
Fórmula na verdade vai ser uma fórmula que vai puxar toda a informação automaticamente pra gente para isso a gente vai usar a função filtro nós vamos usar a função classificar tá Então veja só perceba agora como a gente vai fazer isso aqui para puxar os itens ali do maior menor classificado tudo filtrinho então primeira coisa a gente vai fazer assim igual C abre parênteses al4 que é aquele item que a gente tem como maior menor ali então se el for igual a verdadeiro ponto e vírgula abre vou.
Darent aqui para quebrar fica melhor para você entender classificar então eu vou classificar essa informação veja que eu tô fazendo e vou abrir o filtro vou filtrar agora a informação que eu desejo tá então quem que eu vou classificar filtro abre parênteses agora eu vou selecionar então h6 aqui se não CCO né C Aqui para baixo até a deixa eu ver vou travar aqui vou travar até 1000 tinha 1000 por exemplo ali pode ser 1000 travei ali então então vou filtrar essa informação aqui ponto e.
Vírgula quem eu vou incluir tá aqui é importante então quem eu vou incluir aqui nessa informação nesse filtro eu quero somente os 10 maiores ou x maiores que eu tiver aqui em cima então eu vou vir aqui abre parênteses vou pegar a coluna de valor tem que ser a mesma quantidade de linhas 1000 no caso F4 F4 vou travar aqui vou até dar um enter para você ver melhor aqui ó então primeiro filtro que eu tô aplicando para ele aqui então tô aplicando que ele for maior maior igual.
Maior igual que maior abre parênteses e daí eu tô pesquisando Qual é o maior valor que eu tenho dentro dessa mesma lista então eu vou pegar aqui o maior valor ponto e vírgula e aqui ele tá perguntando esse k Qual é o k valor então tipo o décimo maior valor o x maior valor tá então ser maior ou igual que o décimo maior valor que eu tenho aqui tá esse item que a gente tem que selecionado em cima então F4 só isso daí aqui eu vou botar mais aqui.
Ó fecho parênteses né fecho parênteses e fecho parênteses de novo para fechar ali o outro né que tá ruim aqui para trabalhar por causa da da tela ok ponto e vírgula e agora se vazia eu vou mandar então o vazio então se não tiver nada manda um vazio e ok fecha parênteses ponto e vírgula ele tá perguntando agora qual é o índice de classificação eu quero que classifique pela coluna de valor de vendas ali coluna dois ponto p.
E vírgula ponto e vírgula e agora a gente vai botar a ordem de classificação tá tá vendo o cálculo ali ó ordem de classificação C abre parênteses al5 que é essa outra coluna que nós temos aqui em cima vou travar ela também po não precisa travar Mas é bom travar sempre for igual a verdadeiro tá então se ela for igual a verdadeiro ponto e vírgula 1 se não ponto e vírgula men1 então aqui tá falando se é crescente ou.
