Temos que formatar esse intervalo como tabela você sabe como fazer isso não para que eu possa formatar esse intervalo como tabela é só eu selecionar qualquer célula do intervalo nesse momento eu estou na célula a 2 então Agora resta eu ir aqui na guia inserir e clicar aqui em tabela Observe que o Excel já reconhece aqui o meu intervalo ele já me mostra de onde vai iniciar e onde vai terminar esse intervalo inclusive ele faz uma pergunta.
Para mim ó minha tabela tem cabeçalho sim que é a primeira linha vou dar aqui o Ok pronto a partir desse momento esse intervalo agora é uma tabela tanto é que aparece a guia design aqui a cima ao clicar fora ela some ao clicar em qualquer C desse dessa tabela aparece a guia design ainda não formatamos como tabela dinâmica apenas formatamos o intervalo como tabela agora no formato tabela a.
Gente vaiar uma tabela dinâmica e é da mesma forma eu seleciono qualquer C desse intervalo cli inserir e agora em tabela dinmica repare ó aqui é o nome da minha tabela ela vai se chamar tabela um eu posso renomear o Excel me pergunta ó nova planilha ou planilha existente Se eu der um Ok vai ser inserido uma nova planilha nesse arquivo mas eu quero falar para o Excel.
Qual é a planilha que eu quero aproveitar inserir essa tabela dinâmica então eu vou marcar essa segunda opção repare que eu vou clicar aqui na planilha do eu posso clicar em qualquer cé desse dessa planilha vou clicar por exemplo aqui na ca B2 repar que já aparece aqui a referência da localização aonde vai ser inserido a tabela e basta dar um ok ao fazermos isso reparo já aparece aqui para mim ó esse intervalo e aqui a minha direita já.
Mês Observe que eu vou arrastar aqui o mês para filtros Observe Agora sim eu vou pegar a minha data vou arrastar para linhas repar como é que tá sendo construída aqui a nossa tabela eu vou expandir aqui só para que você vej por enquanto não tem valores ó relacionados e para que eu possa ver os valores obviamente eu vou precisar aqui do tempo de troca ó e eu vou arrastar.
Aqui para valores então aqui eu tenho o meu tempo de troca Lembrando que esse tempo de troca ele é em horas repare nesse momento ele está a números até porque repara aqui no cabeçalho ó Contagem tempo de troca Então o que eu vou fazer aqui seleciono qualquer cé dessa coluna clico o botão direito e vou na opção configurações do campo de valor e aqui ele me mostra ó ele tá fazendo uma contagem mas eu quero que ele.
Some Se eu der um Ok vamos ver o que acontece ó ele fez uma soma repare que tá trazendo em números decimais Não é dessa forma que eu quero exibir os valores eu quero no formato de hora então novamente eu vou clicar em qualquer dessa coluna o botão direito configurações do campo de valor e repare que agora eu vou aqui na opção formato do número eu vou mudar de geral para hora aqui ele já me mostra como vai exibir a.
Informação Só que tem um detalhe interessante como as minhas horas pode ultrapassar 24 horas a soma delas então eu não vou utilizar essa primeira opção a gente utiliza essa daqui ó que é a opção que o Excel interpreta acima de 24 horas vou dar aqui o ok e vou dar aqui um ok ó repara agora o que é que a gente vai fazer ó só para que você entenda aqui é o tempo total de troca de equipamento por mês o gráfico que vamos criar no.
Vídeo de hoje é um gráfico diário então eu quero tempo médio por dia Então vamos entender aqui ó aqui ele já me mostra a quantidade de Horas de cada dia aqui é o total de horas por dia não é o tempo médio então para eu fazer um cálculo de tempo médio tem um detalhe eu poderia ter utilizado essa mesma coluna e ter mudado de soma para média só que mais para frente quando criarmos Campos calculados Eu Já Fiz alguns testes e não iria dar certo fazer os.
VEJA MAIS AQUI:
- COMO ALCANÇAR A PROSPERIDADE - Hernandes Dias Lopes
- HDL Podcast - ARIVAL DIAS CASIMIRO - Hernandes Dias Lopes
- Como Fazer PROCV Avançado no Excel
Cálculos então para isso agora o que que a gente vai fazer ó para que eu possa calcular o tempo médio primeiro eu vou
Trazer aqui para essa tabela a minha quantidade serviços Então vamos pegar aqui ó a quantidade e retornar aqui para o campo valores ó Então qual é a minha leitura aqui ó a minha leitura desses dados é a seguinte ó no mês de janeiro eu tive aí pouco mais do que 50 horas de troca de equipamento e dessas 50 e.Poucas horas foram 134 trocas então quanto é a minha média a minha média nada mais é do que eu pegar o total de horas e dividir pela quantidade de trocas então para isso que que a gente faz a gente arrasta um outro campo para valores só que eu não tenho uma coluna que faz esse cálculo Então vamos criar um campo calculado então Observe ó como eu estou aqui na minha tabela selecionada ela aparece essa aba aqui para mim ó análise de tabela din din então eu clico sobre.
Ela eu tenho aqui a opção ó Campos itens e conjuntos clico nessa opção e tá aqui ó Campo calculado então o nome já é bem sugestivo ele é um campo que vai realizar um cálculo em nome é o nome que eu vou dar o campo então vamos chamar ele de média aqui em Fórmula igual a zero eu vou apagar esse zero e eu vou informar dois Campos e vou fazer uma divisão entre eles eu vou pegar aqui ó o tempo de troca basta eu dar um duplo clique.
Sobre ele vou colocar o sinal de divisão e vou pegar quantidade ó duplo clique pronto quando eu clicar em adicionar esse campo chamado média vai aparecer aqui e quando eu der o ok vai aparecer uma nova coluna fazendo esse cálculo de média então Observe ó vou clicar em adicionar já aparece aqui para mim ó a média e vou dar um ok ó e tá feito ó.
Novamente vem números decimais basta eu clicar sobre qualquer célula desse Campo clico o botão direito vou na opção configurações do campo de valor Observe que eu vou já está em soma OK e eu vou agora na opção formato do número vamos novamente escolher a opção hora e novamente essa última opção ele já me mostra aqui Como vai os dados ó vou dar um ok e posso dar um ok novamente ou seja aqui a minha média eu fiz 134 trocas no total de 50 hor 47.
Minutos e 3 segundos cada troca durou em média 22 minutos e 44 Essa é a minha leitura agora quando a gente expande ó eu tenho aqui o tempo médio ó repar esse é o meu tempo médio diário ó eu tive 38 minutos e 13 segundos acumulados no dia 8 aonde eu fiz apenas duas trocas e cada troca durou em média 19 minutos e 7 Segundos e essa é a mesma Leitura para todos os dados ó se eu Expandir os demais mesmos.
Vai aparecer da mesma forma ah a distribuição dos valores então o que que eu faço agora para que eu possa inserir um gráfico dinâmico eu tenho pelo menos duas formas de fazer isso Observe que eu já estou aqui em uma área selecionada da tabela análise de dados da tabela já aparece aqui para mim então eu posso clicar sobre ela e tenho aqui a opção gráfico Como eu posso vir aqui no inserir e escolher o tipo de gráfico que eu vou fazer e também vai vir no formato.
Dinâmico então vou clicar aqui nesses nessas Barrinhas e vou escolher essa primeira opção Observe que o meu gráfico veio dessa forma aqui eu tenho botões ó botão de mês também tenho outros botões tenho essa legenda então meu gráfico aqui tá bem desconfigurado e tem um detalhe interessante depois que a gente criou esses cálculos não é necessário eu exibir todas as colunas na minha tabela até porque quando eu insiro o gráfico essa.
Informação aqui tá vindo também no gráfico ó provavelmente D essa barra maior Deixa eu adicionar aqui rótulo de dados aqui tá trazendo a quantidade ó eu não quero ver a quantidade eu quero ver apenas o meu tempo médio e uma meta e detalhe eu quero ver isso diáriamente então vou apagar esse gráfico não tem problema aliás eu posso até mantê-lo vou manter aqui o gráfico e o que que eu vou fazer aqui ó só para que você entenda eu preciso de mais algumas informações para que a gente possa ah criar o nosso gráfico de.
Tempo médio diário então o que que eu vou fazer aqui ah essa coluna aqui eu vou ocultá-la eu
Não vou precisar dela então ela é o meu tempo de troca não preciso dessa coluna também não vou precisar mais da coluna da quantidade então basta desmarcar agora vamos criar uma coluna para mostrar a meta Então vamos inserir uma linha que vai ser a minha meta para ver se eu estou acima ou abaixo da meta e é bem simples como eu não tenho essa coluna também na minha planilha não vai aparecer aqui na tabela dinâmica Então a.Gente vai criar um campo calculado novamente análise tabela dinâmica vou criar vou clicar em Campos itens conjuntos novamente e Campo calculado aqui eu vou chamar de meta aqui na fórmula basta eu colocar entre aspas duplas o valor dois zeros a minha meta vai ser de 15 minutos então eu vou colocar aqui dois zeros dois pontos 15 dois pontos e 0 zer novamente e fecho com asas duplas eu vou clicar em adicionar já aparece a meta aqui à minha.
Direita e ao dar o OK foi inserido aqui a minha coluna Lembrando que eu não preciso novamente fazer aquela formatação seg aquele espaço eu posso aproveitar essa formatação aqui selecionando uma célula dessa coluna vindo aqui para Inicial clicar aqui no pincel e aplicar para as demais células ó repare ó não aplicou para essa porque eu acabei não arrastrando até o final então rapidamente vamos aqui aplicar também até essa ó agora sim ó então eu já tenho aqui ó repar que.
Quando a gente removeu aquelas colunas inseriu novas o nosso gráfico se movimentou porque ele é um gráfico de dinâmico então o que que eu vou fazer agora repare eu vou pegar essa linha vou clicar sobre ela vou na opção alterar tip gráfico da série Observe soma da Meta ela está em Barras né eu vou escolher aqui no formato linha já fica aqui ó obser vou.
Dar um ok essas barrinhas vou colocar vou clicar com o botão direito sobre uma delas e escolher a opção adicionar rota de dados e aqui já aparece os valores eu não vou precisar aqui dessa legenda vou apagá-la também eu não vou precisar desses botões Ah mas antes de eu removê-los eu só quero mostrar uma coisa a você ó se eu fizer isso aqui ó Cliquei no botão ao marcar Janeiro e dar um ok ó pare ó eu consigo fazer um filtro diretamente no gráfico e não é isso que eu quero eu vou clicar aqui em tudo e.
Dou um ok e pronto a mesma coisa vai acontecer se eu fizer aqui ó repare que tá Como tudo se eu marcar Masso e de um ok ó repare vai fazer aqui o filro de Masso eu quero não um gráfico mensal Eu quero um gráfico diário então repare que eu marquei Masso e eu posso clicar nesse mais e expandir e vai agora aparecer todos os dias de Março Ou seja eu fiz duas operações eu filtrei por mês e depois expandi esse mês abreviado então ao recolher.
Novamente aparece o mês por completo Ó o mês fechado então eu vou fazer algo semelhante a isso ã utilizando o VBA e eu vou te explicar o porquê ó repare ó eu vou desmarcar aqui escolhendo a opção tudo e antes que você pense Ah mas por que que ele vai utilizar o VBA se ele poderia utilizar uma segmentação de dados verdade até meia parte isso procede por exemplo ó ao clicar nessa tabela ao vir aqui em análise tabela.
Dinâmica eu tenho aqui a opção inserir segmentação de dados e o que que vai acontecer ó clico sobre ela ela vai me sugerir aqui umas caixinhas com segmentações de dados e eu posso selecionar mês e eu posso selecionar mais de uma coluna eu vou selecionar apenas um mês por agora vou dar um ok e tá aqui os meses ó e o que que vai acontecer algo semelhante ao que eu fiz aqui manualmente lembra que eu vim aqui marquei um mês dei um OK também vim aqui marquei um mês e deu ok Aqui é a mesma.
Lógica ó a diferença que eu não eu não preciso fazer lá eu posso clicar aqui nos botões ao clicar em Janeiro ele vai trazer out dado janeiro fevereiro março abril e assim sucessivamente ao clicar nesse x com filtro ó ele remove o filtro até aí Ok só que tem um detalhe interessante Observe ó se eu clicar em Janeiro que acontece ele traz apenas o mês de Janeiro para que eu possa ver os dias de janeiro eu ainda tenho que vir aqui ó e.
Marcar Observe que V aqui e expandir eu consigo ver os dados de Janeiro e não é dessa forma que eu quero trabalhar e aí você pode estar se perguntando Mas por que que ele não insere um controle de segmentação de dados com os dias vamos tentar fazer isso análise tabela dinâmica inserir segmentação de dados e e vou escolher aqui data e dou um ok Observe como é que vem a data ó ela vem dessa forma ao clicar no dia 8/01 ele.
Vai trazer os dados do dia 8/01 9/01 10/01 ou seja ele não me dá a opção de eu vir aqui e expandir como eu estou fazendo agora manualmente ó repare Então para mim não é interessante trabalhar dessa forma então o que que eu vou fazer aqui ó eu não vou não vou trabalhar com segmentação de dados para esse exemplo apaguei os controles Observe que eu vou aqui marcar opção tudo e o que que vamos fazer ó eu não.
Vou trabalhar agora momentaneamente aqui com a minha tabela dinâmica então eu posso vir aqui ó e fechá-la agora vamos aprender como fazer a substituição da segmentação de dados por um comando VBA Então antes de eu criar uma macro com você do zero eu acho mais pertinente eu fazer uma gravação de uma macro você vai ver como ela se comporta e a gente faz os ajustes em cima dessa macro gravada vai ficar mais fácil para você compreender então como é.
A gente faz isso Ó Observe que eu tenho essa guia desenvolvedor habilitado caso você não tenha é bem simples ó basta você vir aqui em arquivo opções e vem na opção ó personalizar faixa de opções a guia desenvolvedor você marca a minha já está marcada e você dá um ok e pronto ao fazer isso ela vai aparecer aqui para você Aí você salva o arquivo toda vida que você abrir qualquer outro arquivo.
Excel a guia des sempre vai vir habilitar então o que que a gente vai fazer ó repare ó eu vou vir aqui na guia desed e vou clicar em gravar Macro o Excel me sugere que eu não da macro vai se chamar macro um vou dar um ok a partir desse momento tudo que eu fizer aqui no Excel está sendo gravado Então vamos entender como é que o Excel entende quando eu mudo aqui o mês e expando o nome do mês na tabela dinâmica vamos lá então vou mudar aqui ó para o.
Mês de fevereiro vou dar um OK agora eu vou clicar aqui em fevereiro expandido repara Observe que eu vou voltar lá na guia den vetor e vou parar a gravação como é que a gente vai agora encontrar o que foi gravado basta eu vir em desor clicar em Visual Basic posso clicar aqui em exibir Project explore e aqui ó dentro desse desse projeto.
Personal vou expandi-lo aqui vai ter o módulo e aqui tem o o módulo um duplo Clique nele tal vez para você não apareça esse esse nome aqui personal apareça simplesmente só uma parte com B Mas vai ser as mesmas etapas então o que que a nossa macro fez ó vamos entender aqui ó tabela dinâmica um é quem essa tabela repar ó análise tabela dinâmica aqui Eló tabela dinâmica um Então vamos.
Voltar lá para macro então ative sheet ou seja ela ativou a minha sheet sheet é a planilha Pivot tables Pivot tables nada mais é que tabela dinâmica em inglês então ele ativou a minha tabela dinâmica chamada tabela dinâmica um Pivot quer dizer dinâmico Fi filtro ou seja o filtro da da tabela dinâmica chamado mês ele Limpou ou seja ele limpou quem ele limpou essa primeira opção aqui ó e depois o que que ele fez.
Ele aplicou um filtro ó Active sheet Pivot tables aí ele vem formando o o mesmo comando até aqui e aí ele faz faz o seguinte ó corrente ou seja corrente pedio a página fevereiro ou seja ele selecionou Fevereiro é exatamente o que está aqui ó fevereiro em seguida o que que ele fez ele selecionou a célula B4 vem para o segundo comando bem interessante ó para que ele possa expandir o mês o que.
Que ele faz ó é o mesmo comando de cima até a aqui o que muda é o cabeçalho da coluna lá da tabela dinâmica se chama meses data e a ação que ele faz ó essa daqui ó pivotes itens e muita atenção agora aqui eu estou vendo o nome do mês abreviado em inglês aqui está o nome do mês em português e aí o que que ele faz ó Show mostrar detalhe ou seja ele mostra.
Detalhes e tá dizendo como trui Ou seja eu estou mostrando os detalhes de Fevereiro ficou complexo mas vamos agora aproveitar esses mesmos comandos você vai observar que não é nada difícil por quê eu vou copiar basicamente tudo isso aqui vou copiar eu não quis fazer do zero até porque talvez ficaria mais complexo de você entender eu fui escrevendo linha por linha do do que eu gravar a macro.
Realizando e depois só tentar te explicar o que que ele fez eu posso pegar essa macro que eu copiei e atribuir a uma caixa de texto A uma imagem a um botão através daqu desenvolvedor Como eu posso atribuir a outros objetos Então vamos utilizar aqui uma imagem ó Observe vou clicar aqui no inserir formas eu posso atribuir a qualquer uma dessas fmas eu gosto aqui do retângulo e can o dado eu vou clicar sobre ele vou contornar aqui não tem problema aonde eu.
Colocar dá certo mas eu vou colocar por exemplo aqui clico sobre essa forma atribui macro e vou clicar em novo e vou colar e por enquanto a gente não faz nada o que que a gente vai fazer aqui ó repare ó eu vou desmarcar ó clicando em tudo dou um ok vou recolher aqui o mês de fevereiro repara o que vai acontecer ó vou clicar nessa forma e ela vai fazer a mesma ação por ela tá repetindo aquela mesma macro que o Excel gravou agora a.
Gente vai personalizar essa macro E para isso eu vou utilizar essas informações auxiliares Você lembra que no início do vídeo eu havia comentado que eu iria utilizar essas informações e no momento mais propício Eu iria lhe explicar Então chegou a hora que demais ai eu vou criar uma validação de dados nessa célula I2 pegando as informações dos meses então muita atenção vou aqui em dados vou na opção validação de.
Dados vou mudar de qualquer valor para lista e aqui em fonte eu vou selecionar aqui os meses e posso clicar n setinha e dar ok obser que foi inserido essa setinha e aqui eu posso escolher os meses que eu queira ó para fevereiro Por enquanto nada acontece ó Março e assim sucessivamente o que que eu vou fazer ó olha só a jogada que eu vou fazer aqui ó eu vou atribuir essa informação dessa célula I2 lá para essa Macro o miserável é um gênio Então como é que eu faço isso.
Ó clico sobre ela atribui macro e edito então vamos fazer o seguinte ó esse comando aqui eu vou deixar comentado e o que que eu vou fazer aqui ó repare ó tá vendo esse nome fevereiro ele tá estático por quê Porque ele tá fixo eu quero deixar ele dinâmico ou seja o texto que eu colocar nessa célula eu quero que apareça aqui como é que eu faço isso eu posso fazer isso diretamente aqui nesse local Como eu.
Posso utilizar uma variável que é isso que eu vou fazer ó então vou fazer o seguinte ó muita atenção eu vou criar uma variel chamada V mês não precisa classificar e aí o que que eu faço com a variel v mês ó V mês vai ser igual a como é o nome dessa planilha se chama planilha do e muita atenção quando você fizer isso ó planilha do é o nome dessa aba da planilha esse nome parcial tá Qualquer pessoa pode vir aqui mudar o nome da planilha mas eu vou utilizar o codame.
Que é o nome por trás desse nome planilha do Observe ó exibi Project explore e aqui ó nesse momento coincidentemente os dois nomes são iguais ó planilha 2 2 mas aquele nome planilha do tá lá na aba do arquivo é esse nome entre parênteses tanto é que eu vou mudar lá e você vai observar vou chamar aqui ó de tb vamos voltar lá o código só para que você veja que vai mudar aquele nome.
Entre parênteses e mudou ó então o nome que eu vou utilizar aqui vai ser esse nome fora do parêntese Justamente por isso ó se eu utilizar esse nome entre parênteses qualquer pessoa pode renomear planilha e vai ah quebrar o nosso código o nosso código não vai mais funcionar então sabendo disso eu vou utilizar esse número fora do parêntese ó que Dificilmente uma pessoa lega vai vir aqui e mudar esse nome então vamos lá vou fazer o seguinte ó vou informar aqui ó planilha dois.
Ponto para eu pegar informação de uma célula eu posso fazer de mais mais de uma forma uma delas é passa no Ranger que quer dizer intervalo abro parêntese entre aspas duplas eu vou informar a célula a informação que eu quero está na célula I2 fecho com asas duplas fecho com parênteses e vou passar aqui o cels então o que que eu estou fazendo aqui eu estou pegando essa informação da ca I2 e armazenando na variável verm Observe que eu vou copiar o nome da minha variável e vou substituir por isso daqui ó repar.
CLique no Link do Vídeo: https://www.youtube.com/watch?v=iyNWBLREWYw
Postar um comentário