Como mostrar as vendas do mês até a data em uma tabela dinâmica. Este é um episódio Dueling Excel.
Assistir vídeo
- Método de Bill
- Adicione uma célula auxiliar com uma fórmula MTD
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))- Adicione esse campo como um Slicer onde = True
- Dica bônus: agrupe datas diárias até anos
- Adicione um cálculo fora da tabela dinâmica, evitando GetPivotData
- Abordagem de Mike:
- Transforme os dados em uma tabela usando Ctrl + T. Isso permite que mais dados sejam adicionados à tabela e a atualização das fórmulas.
- SUMIFS com funções DATE, MONTH, DAY
- Pressionar F4 três vezes bloqueia uma referência apenas à coluna.
- Cuidado - se você arrastar uma fórmula de tabela para os lados, as colunas mudam. Copiar e colar - sem problemas
- Usando TEXT (data, formato. Belo truque com 1 para inserir o número 1 no texto
Transcrição de vídeo
Bill Jelen: Ei, bem-vindo de volta. É hora de outro Podcast de duelo do Excel. Sou Bill Jelen de. Terei a companhia de Mike Girvin, do Excel Is Fun.
Este é nosso Episódio 181: Tabela dinâmica do mês até a data.
Bem, ei, pergunta de hoje - a ideia de hoje para este duelo foi enviada por Mike. Ele diz: “Você pode criar um relatório do mês até a data em uma tabela dinâmica?”
Certo, vamos. Então aqui está o que temos, temos dois anos de datas de janeiro de 2016 até 2017. Agora é claro que estou gravando isso em abril, é 15 de abril agora quando estou gravando minha parte do duelo. E aqui temos uma tabela dinâmica mostrando Dias no lado esquerdo, Categoria na parte superior e Receita no centro da tabela dinâmica.
Agora, para criar um relatório Mês até a data, o que vou fazer é dizer que vou adicionar uma nova coluna auxiliar aqui aos meus dados originais e isso vai verificar duas coisas. E como estou verificando duas coisas, vou usar a função AND, ambas as coisas precisam ser True para que seja Mês até a data. E vou usar uma função aqui chamada HOJE. HOJE, tudo bem, então eu quero saber se o MÊS de HOJE ()) é = ao MÊS daquela data ali na Coluna A. Se for verdade, se for o mês atual, então em outras palavras se for abril, então verifique e veja se o dia daquela data ali em A2 é <= DIA de HOJE. O bonito é que, quando abrirmos esta apostila amanhã ou daqui a uma semana, o dia de hoje será atualizado automaticamente e clicaremos duas vezes para copiá-lo.
Tudo bem agora, temos que colocar esses dados extras em nossa tabela dinâmica, então eu venho aqui Tabela Dinâmica, Analisar e não é tão difícil mudar a fonte de dados, basta clicar naquele botão grande lá e dizer que queremos ir para a Coluna D , Clique OK. Tudo bem, agora que temos esse campo extra, vou inserir um Slicer com base nesse campo Mês até a data e só quero ver como nosso Mês até a data é verdadeiro. Agora, precisamos que o Slice seja tão grande? Não, provavelmente podemos fazer com que sejam duas colunas e simplesmente mantê-lo discreto no lado direito. Então agora o que temos são todas as datas de 2016 e todas as datas de 2017; embora, seria muito legal compará-los lado a lado. Vou pegar esse campo Data e Analisar. Vou agrupar o campo, vou agrupá-lo em apenas anos. Eu não'Eu realmente me importo com os dias individuais. Eu só quero saber o mês até a data. Agora, onde estamos? Então, vou agrupar em anos e terminaremos com esses 2 anos lá e eu irei reorganizar isso, colocar esses anos para ver, categorias para descer. E agora eu vejo onde estávamos no ano passado e neste ano. Tudo bem agora, como eu fiz o agrupamento, não tenho mais permissão para criar um campo calculado dentro da tabela dinâmica. Se eu quisesse ter uma quantia ano após ano ali, eu clicaria com o botão direito, Remover Grande Total, certo, e agora estamos, então,% de variação, estamos fora de uma tabela dinâmica apontando para dentro da tabela dinâmica . Temos que nos certificar de desligar GetPivotData ou apenas construir uma fórmula como esta: = J4 / I4-1 e isso cria uma fórmula que podemos copiar sem qualquer aborrecimento, como essa.Tudo bem, Mike, vamos ver o que você tem.
Mike Girvin: Obrigado. Sim, enviei a pergunta para porque fiz isso com fórmulas e não conseguia descobrir como fazer isso com uma tabela dinâmica padrão e então me lembrei de ter visto, ao longo dos anos, um monte de vídeos legais sobre colunas auxiliares e tabelas dinâmicas . Essa é uma bela fórmula e uma bela solução. Então é assim que se faz com uma tabela dinâmica, vamos ver como fazer com uma fórmula.
Agora, estou fazendo isso dois dias depois que ele fez. F2 Eu tenho a função TODAY que sempre vai ser a informação de data para a data atual de hoje que será usada pelas fórmulas aqui porque queremos que seja atualizada. Também usei uma tabela do Excel com o nome FSales. Se eu Ctrl + Seta para baixo, vejo que é 14/4, mas quero poder adicionar os registros mais recentes e ter nossa atualização de fórmulas incluída quando saltarmos para o próximo mês. Ctrl + Seta para cima. Tudo bem, eu tenho Critérios de ano como cabeçalhos de coluna, Categoria como cabeçalhos de linha e, em seguida, os detalhes para mês e dia virão dessa célula. Então, vou simplesmente usar a função SUMIFS, já que estamos adicionando com várias condições, o intervalo de soma é a receita, vamos usar esse ótimo truque para uma tabela do Excel.Bem no topo, vemos aquela seta preta apontando para baixo, BAM! Isso coloca o nome da tabela adequado e, em seguida, entre colchetes o nome do campo, vírgula. Intervalo de critérios, teremos que usar Data duas vezes, então começarei com Data. Clique, aqui está a coluna de data, vírgula. Agora estou em abril, então preciso criar a condição> = para 1º de abril. Portanto, os operadores comparativos “> =” entre aspas duplas e vou juntá-los. Agora tenho que criar alguma fórmula de data que sempre olhe aqui e crie o primeiro dia do mês para este ano específico. Vou usar a função DATE. Ano, bem, eu tenho o ano correto como o cabeçalho da coluna e vou apertar a tecla F4 uma, duas vezes para bloquear a linha, mas não a coluna, então, quando ela passar para aqui, passaremos para 2017, vírgula, a Mês - eu 'Vou usar a função MONTH para obter o número do mês de 1 a 12. Isso é o mês que está acima na célula, F4 para travá-lo em todas as direções, feche parênteses e vírgula, 1 sempre será o primeiro do mês, não importa que mês seja, feche parênteses.
Tudo bem, então esse é o critério. Sempre será> = o primeiro dia do mês, vírgula, intervalo de critérios dois, vou obter minha coluna Data, vírgula. Critério dois, bem, isso vai ser <= o limite superior, então em “<=” e o &. Eu vou trapacear, veja isso. Vou apenas copiar daqui de cima, pois é a mesma coisa, Ctrl-C Ctrl-V exceto para o dia, temos que usar a função DAY e sempre obter como nosso limite superior qualquer que seja o dia deste mês em particular . F4 para travá-lo em todas as direções, feche os parênteses em Data. Tudo bem, esse é o nosso segundo critério: vírgula. Intervalo de critérios 3, é categoria. Aqui está, vírgula e nosso cabeçalho de linha. Então, neste temos que F4 um dois três vezes, bloquear a coluna, mas não a linha, então quando copiarmos a fórmula para baixo, iremos para o Gizmo e Widget,feche parênteses e essa é a fórmula. Arraste, clique duas vezes e envie para baixo. Eu posso ver que há problemas. É melhor eu chegar à última célula diagonalmente mais distante. Pressione F2. Agora, o comportamento padrão da Nomenclatura da Fórmula da Tabela é que, ao copiar as fórmulas ao lado, as colunas reais se movem como se fossem referências de células mistas. Agora podemos bloqueá-los, mas não vou fazer isso desta vez. Agora observe que quando você copia isso funciona bem, mas quando você copia para o lado, é quando as colunas reais se movem. Então preste atenção, irei usar Ctrl + C e Ctrl + V e isso evita que F para as colunas se movam quando você copia para o lado. Clique duas vezes e envie. Agora nossa fórmula de% de mudança = valor final / valor inicial -1, Ctrl + Enter, clique duas vezes e envie para baixo.Arraste, clique duas vezes e envie para baixo. Eu posso ver que há problemas. É melhor eu chegar à última célula diagonalmente mais distante. Pressione F2. Agora, o comportamento padrão da Nomenclatura da Fórmula da Tabela é que, ao copiar as fórmulas ao lado, as colunas reais se movem como se fossem referências de células mistas. Agora podemos bloqueá-los, mas não vou fazer isso desta vez. Agora observe que quando você copia isso funciona bem, mas quando você copia para o lado, é quando as colunas reais se movem. Então preste atenção, irei usar Ctrl + C e Ctrl + V e isso evita que F para as colunas se movam quando você copia para o lado. Clique duas vezes e envie. Agora nossa fórmula de% de mudança = valor final / valor inicial -1, Ctrl + Enter, clique duas vezes e envie para baixo.Arraste, clique duas vezes e envie para baixo. Eu posso ver que há problemas. É melhor eu chegar à última célula diagonalmente mais distante. Pressione F2. Agora, o comportamento padrão da Nomenclatura da Fórmula da Tabela é quando você copia as fórmulas para o lado, as colunas reais se movem como se fossem referências de células mistas. Agora podemos bloqueá-los, mas não vou fazer isso desta vez. Agora observe que quando você copia isso funciona bem, mas quando você copia para o lado, é quando as colunas reais se movem. Então preste atenção, irei usar Ctrl + C e Ctrl + V e isso evita que F para as colunas se movam quando você copia para o lado. Clique duas vezes e envie. Agora nossa fórmula de% de mudança = valor final / valor inicial -1, Ctrl + Enter, clique duas vezes e envie para baixo.É melhor eu chegar à última célula diagonalmente mais distante. Pressione F2. Agora, o comportamento padrão da Nomenclatura da Fórmula da Tabela é que, ao copiar as fórmulas ao lado, as colunas reais se movem como se fossem referências de células mistas. Agora podemos bloqueá-los, mas não vou fazer isso desta vez. Agora observe que quando você copia isso funciona bem, mas quando você copia para o lado, é quando as colunas reais se movem. Então preste atenção, irei usar Ctrl + C e Ctrl + V e isso evita que F para as colunas se movam quando você copia para o lado. Clique duas vezes e envie. Agora nossa fórmula de% de mudança = valor final / valor inicial -1, Ctrl + Enter, clique duas vezes e envie para baixo.É melhor eu chegar à última célula diagonalmente mais distante. Pressione F2. Agora, o comportamento padrão da Nomenclatura da Fórmula da Tabela é que, ao copiar as fórmulas ao lado, as colunas reais se movem como se fossem referências de células mistas. Agora podemos bloqueá-los, mas não vou fazer isso desta vez. Agora observe que quando você copia isso funciona bem, mas quando você copia para o lado, é quando as colunas reais se movem. Então preste atenção, irei usar Ctrl + C e Ctrl + V e isso evita que F para as colunas se movam quando você copia para o lado. Clique duas vezes e envie. Agora nossa fórmula de% de mudança = valor final / valor inicial -1, Ctrl + Enter, clique duas vezes e envie para baixo.as colunas reais se movem como se fossem referências de células mistas. Agora podemos bloqueá-los, mas não vou fazer isso desta vez. Agora observe que quando você copia isso funciona bem, mas quando você copia para o lado, é quando as colunas reais se movem. Então preste atenção, irei usar Ctrl + C e Ctrl + V e isso evita que F para as colunas se movam quando você copia para o lado. Clique duas vezes e envie. Agora, nossa fórmula de% de mudança = valor final / valor inicial -1, Ctrl + Enter, clique duas vezes e envie para baixo.as colunas reais se movem como se fossem referências de células mistas. Agora podemos bloqueá-los, mas não vou fazer isso desta vez. Agora observe que quando você copia isso funciona bem, mas quando você copia para o lado, é quando as colunas reais se movem. Então preste atenção, irei usar Ctrl + C e Ctrl + V e isso evita que F para as colunas se movam quando você copia para o lado. Clique duas vezes e envie. Agora nossa fórmula de% de mudança = valor final / valor inicial -1, Ctrl + Enter, clique duas vezes e envie para baixo.vou para Ctrl + C e Ctrl + V e isso evita que F para as colunas se movam quando você copia para o lado. Clique duas vezes e envie. Agora nossa fórmula de% de mudança = valor final / valor inicial -1, Ctrl + Enter, clique duas vezes e envie para baixo.vou para Ctrl + C e Ctrl + V e isso evita que F para as colunas se movam quando você copia para o lado. Clique duas vezes e envie. Agora nossa fórmula de% de mudança = valor final / valor inicial -1, Ctrl + Enter, clique duas vezes e envie para baixo.
Agora, antes de irmos testá-lo, agora adicione alguns novos registros. Na verdade, quero criar esse rótulo aqui para que seja dinâmico. E a maneira como vou fazer isso é dizer = assinar e vamos fazer uma fórmula de texto, então sempre que quisermos um texto e uma fórmula, você deve colocá-los em: “e eu estou vou digitar Sales Between, space ”& e agora eu preciso extrair dessa única data lá, o primeiro dia do mês até o final do mês. Vou usar a função TEXT. A função TEXTO pode pegar datas numéricas ou números de série, vírgulas e usar alguma formatação de número personalizada em ”. Sempre quero ver a abreviatura de três letras do mês, mmm, sempre a quero como a primeira. Agora, se eu colocar 1 aqui, vírgula yyy, não funcionará. Quer ver que isso nos dá um valor ou porque não gosta disso 1. Mas nós 'É permitido inserir um único caractere se usarmos barra, que está na formatação de número personalizado. O mm e o yy serão entendidos pela formatação de número personalizado como mês e ano e agora o formato de número personalizado entenderá a inserção do número 1. F2 e agora vamos simplesmente: & “-” & TEXTO dessa vírgula e agora nós usará apenas a formatação de números simples: “mmm spaceD, yyy”) Ctrl + Enter.
Now let's just, before we add some data, let's just change this. Pretending that today was showing: 3/15/2017 just like that, all the formulas are updating and our text formula is also, Ctrl+Z. Now, let's go down to the bottom of the data set, Ctrl+Down Arrow I want to add one new record. I'm in the last cell of the data set, I hit Tab to add a new record to our data set. I'm simply going to copy this record over here, Ctrl+Up Arrow, and there we can see the difference. If we wanted to check these formula values against the ones did: =relative cell reference= click on the sheet, we're going to click right in I4. We could see our formula up there, Ctrl+Enter. Actually I'm going to drag it down. Ctrl+Enter just populated everything I had highlighted. And of course, FALSE FALSE. Well guess what? = that amount right there -, click Ctrl+Down Arrow, Ctrl+Backspace, so I'm going to subtract that just to check and sure enough that was the exact amount we could look back there.
That is a little fun with some IFS and some date calculations, TODAY and even some text formula fun. Alright, throw back to.
Bill Jelen: Alright, Mike, that's awesome. So to wrap it up, Mike took the data and turned it into a table using Ctrl+T that allows more data to be added to the table, and the formulas will update, create that great little formula with SUMIFS, DATE, MONTH and DAY functions. Remember pressing F4 three times, locks the reference to just the column though watch out if you drag a table formula sideways using the Fill Handle, the columns change but Copy and Paste alleviates that problem. I never knew that one.
And then nice trick there using the heading with the text date format and that 1 to insert a number 1 in the text, in any character. I would allow to insert something, so you might have to do something like COOL to get an entire word in there but it would work.
Alright, my method was using a pivot table, I added a helper column with a MONTH TO DATE formula that one there using =AND checking if the MONTH and the DAY match. Add that field as a Slicer, set the Slicer =True. And then bonus tip: Group Daily Dates up to Years and then added a calculation outside of the pivot table while avoiding GetPivotData. And I'm interested, I still don't know how Mike did it with his formula. He managed to use the mouse to point to this equal to something, it’s on my pivot table and didn't get GetPivotData. Maybe, maybe he's turned it off.
Tudo bem, bem, quero agradecer a todos por passarem por aqui. Nos vemos na próxima vez para outro Dueling Excel Podcast do e Excel Is Fun.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Duel181.xlsm
