Quais são os últimos cinco meses de chuva? Aprenda a resolver esse problema usando uma tabela dinâmica.
Assistir vídeo
- As tabelas dinâmicas criadas em 2013 não podem ser atualizadas em 2007
- Você precisa criar a tabela dinâmica em 2007 para permitir que seja atualizável
- Objetivo é encontrar os cinco meses com menos chuva
- Crie uma grande tabela dinâmica com precipitação por mês
- Classificar por precipitação crescente
- Mudar para a forma tabular
- Use os Filtros de valor, 10 principais, para obter os 5 últimos!
- Remova a linha do Total geral
- Observe que um empate pode fazer com que este relatório forneça 6 ou mais linhas
- Depois de ter a primeira tabela dinâmica, copie-a no lugar e crie a próxima tabela dinâmica
- Quando você muda de um campo de valor para outro, você tem que refazer a classificação e o filtro
- Quando você muda de um campo de linha para outro, você tem que refazer a classificação e o filtro
- Dica bônus: criando uma tabela dinâmica com linhas e colunas
Transcrição de vídeo
Aprenda Excel em Podcast, Episódio 2063: Os primeiros ou últimos cinco meses ou anos usando uma tabela dinâmica.
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. A pergunta de hoje enviada por Ken. Ken tem uma planilha incrível aqui com anos e anos e anos de datas de chuvas diárias, desde 1999. Uma coleção realmente impressionante de dados que ele tem, e Ken tinha fórmulas incríveis para tentar encontrar o mês com mais chuvas, em menos chuva. Agora, você sabe, isso será muito mais fácil com uma tabela dinâmica.
Tudo bem agora, Ken nunca criou uma tabela dinâmica e para complicar ainda mais as coisas, estou aqui no Excel 2016, Ken está usando o Excel 2007. Minhas tabelas dinâmicas que criei em 2016, ele podia vê-lo, mas não conseguiu atualizá-las. Tudo bem, então este vídeo é Tabela Dinâmica 101: Como criar sua primeira Tabela Dinâmica.
Primeiro, Ken tem essa data na coluna A, datas reais, estamos bem? Isso é incrível, certo? E então eu uso- insiro algumas fórmulas extras aqui na função = ANO para obter o ano, função = MÊS para obter o mês, função DIA =. E então concatená-los novamente. Na verdade, usei a função = TEXT em AAAA-MM, dessa forma, tenho ano e mês para baixo. Estes são os dados do Ken, os dados da chuva aqui e depois adicionei algumas fórmulas. O de Ken tem menos de 0,5 milímetros, não conta como um dia de chuva, então há uma fórmula lá. E então, do Episódio 735, volte e dê uma olhada nisso para ver como calculei a sequência de dias com chuva e a sequência de dias sem chuva. Agora, isso não será usado hoje, isso foi usado para outra coisa.
Então, nós viemos aqui. E primeiro, queremos selecionar os dados para nossa tabela dinâmica. Agora, na maioria dos casos, você pode apenas selecionar todos os dados para que possa escolher apenas uma célula aqui, mas, neste caso, há um intervalo de nomes que define os dados apenas até, neste caso, 2016. Estamos sentados aqui … Estou registrando isso no início de 2017. Os dados de Ken vão apenas até o final de 2016. Então, vamos selecionar apenas esses dados. E então na guia Inserir - guia Inserir. Excel 2007, é a primeira vez que as tabelas dinâmicas são movidas da guia Dados de volta para a guia Inserir. Portanto, escolhemos: Tabela dinâmica, e nossos dados selecionados serão os dados a partir dos quais construímos. E, não queremos ir para uma nova planilha, vamos para uma planilha existente e vou colocá-la bem aqui na coluna - vamos com a coluna N.Agora, no final das contas, quero que esses dados Anos com a precipitação mais baixa apareçam aqui, mas sei que, enquanto estou construindo essa tabela dinâmica, ela vai precisar de muito mais linhas do que essas 5, certo? Então, estou construindo para o lado aqui, certo. E clicamos em OK.
Tudo bem, agora aqui está o que você obtém. É para onde o relatório vai e aqui está uma lista de todos os campos que temos em nosso pequeno conjunto de dados. E então nós temos, porque o que eu chamo de horrivelmente chamado desaparece. Linhas são os itens que você deseja no lado esquerdo. Valores é o que você deseja resumir e, em seguida, Colunas são o que você deseja no topo. Podemos usar isso no final. Não vamos usar filtros hoje. Portanto, estamos apenas construindo uma pequena tabela dinâmica simples com precipitação total por ano, então pego o campo Ano e arrasto-o aqui para o lado esquerdo. Há uma lista de todos os nossos anos, certo? E então, pense sobre isso. Para obter esta fórmula aqui sem uma tabela dinâmica, você estaria fazendo o quê? SUMIF, sim, SUMIF. Você pode até usar o SUMIF no Excel 2007. Então,Vou pegar o campo Rain e arrastá-lo aqui. Neste momento, preste atenção ao - Veja, eles escolheram Count of Rain, isso é porque há alguns dias nos dados ou Ken tem uma célula em branco, uma célula vazia em vez de um 0. E sim, devemos examinar e consertar isso, mas são os dados de Ken. São 20 anos de dados. Não vou continuar usando Localizar e substituir. Tudo bem, eu só- Por alguma razão, vou respeitar o fato de Ken ter um motivo para ter isso, como vou permitir que fiquem em branco. E aqui, em Count of Rain, vou ter certeza de escolher uma célula na coluna Count of Rain, ir para Configurações de campo e mudar isso de Count para Sum, certo? Portanto, há todos os nossos anos e quanta chuva tivemos em cada ano. E estamos procurando os anos com menor precipitação.No momento, preste atenção ao - Veja, eles escolheram Count of Rain, isso é porque há alguns dias nos dados ou Ken tem uma célula em branco, uma célula vazia em vez de 0. E sim, devemos examinar e consertar isso, mas são os dados de Ken. São 20 anos de dados. Não vou continuar usando Localizar e substituir. Tudo bem, eu só- Por alguma razão, vou respeitar o fato de Ken ter uma razão para ter isso, como vou permitir que fiquem em branco. E aqui, em Count of Rain, vou ter certeza de escolher uma célula na coluna Count of Rain, ir para Configurações de campo e mudar isso de Count para Sum, certo? Portanto, há todos os nossos anos e quanta chuva tivemos em cada ano. E estamos procurando os anos com menor precipitação.Neste momento, preste atenção ao - Veja, eles escolheram Count of Rain, isso é porque há alguns dias nos dados ou Ken tem uma célula em branco, uma célula vazia em vez de um 0. E sim, devemos examinar e consertar isso, mas são os dados de Ken. São 20 anos de dados. Não vou continuar usando Localizar e substituir. Tudo bem, eu só- Por alguma razão, vou respeitar o fato de Ken ter um motivo para ter isso, como vou permitir que fiquem em branco. E aqui, em Count of Rain, vou ter certeza de escolher uma célula na coluna Count of Rain, ir para Configurações de campo e mudar isso de Count para Sum, certo? Portanto, há todos os nossos anos e quanta chuva tivemos em cada ano. E estamos procurando os anos com menor precipitação.s porque há alguns dias nos dados ou Ken tem uma célula em branco, uma célula vazia em vez de 0. E sim, devemos examinar e consertar isso, mas são os dados de Ken. São 20 anos de dados. Não vou continuar usando Localizar e substituir. Tudo bem, eu só- Por alguma razão, vou respeitar o fato de Ken ter um motivo para ter isso, como vou permitir que fiquem em branco. E aqui, em Count of Rain, vou ter certeza de escolher uma célula na coluna Count of Rain, ir para Configurações de campo e mudar isso de Count para Sum, certo? Portanto, há todos os nossos anos e quanta chuva tivemos em cada ano. E estamos procurando os anos com menor precipitação.s porque há alguns dias nos dados ou Ken tem uma célula em branco, uma célula vazia em vez de 0. E sim, devemos examinar e consertar isso, mas são os dados de Ken. São 20 anos de dados. Não vou continuar usando Localizar e substituir. Tudo bem, eu só- Por alguma razão, vou respeitar o fato de Ken ter um motivo para ter isso, como vou permitir que fiquem em branco. E aqui, em Count of Rain, vou ter certeza de escolher uma célula na coluna Count of Rain, ir para Configurações de campo e mudar isso de Count para Sum, certo? Portanto, há todos os nossos anos e quanta chuva tivemos em cada ano. E estamos procurando os anos com menor precipitação.dados s. São 20 anos de dados. Não vou continuar usando Localizar e substituir. Tudo bem, eu só- Por alguma razão, vou respeitar o fato de Ken ter um motivo para ter isso, como vou permitir que fiquem em branco. E aqui, em Count of Rain, vou ter certeza de escolher uma célula na coluna Count of Rain, ir para Configurações de campo e mudar isso de Count para Sum, certo? Portanto, há todos os nossos anos e quanta chuva tivemos em cada ano. E estamos procurando os anos com menor precipitação.dados s. São 20 anos de dados. Não vou continuar nem usando Localizar e substituir. Tudo bem, eu só- Por alguma razão, vou respeitar o fato de Ken ter uma razão para ter isso, como vou permitir que fiquem em branco. E aqui, em Count of Rain, vou ter certeza de escolher uma célula na coluna Count of Rain, ir para Configurações de campo e mudar isso de Count para Sum, certo? Portanto, há todos os nossos anos e quanta chuva tivemos em cada ano. E estamos procurando os anos com menor precipitação.Vou ter certeza de escolher uma célula na coluna Contagem de chuva, ir para Configurações de campo e alterar de Contagem para Soma, certo? Portanto, há todos os nossos anos e quanta chuva tivemos em cada ano. E estamos procurando os anos com menor precipitação.Vou ter certeza de escolher uma célula na coluna Contagem de chuva, ir para Configurações de campo e alterar de Contagem para Soma, certo? Portanto, há todos os nossos anos e quanta chuva tivemos em cada ano. E estamos procurando os anos com menor precipitação.
Tudo bem agora, uma coisa que me incomoda é esta palavra aqui Row Labels. Isso começou a acontecer conosco no Excel 2007, certo? E eu - 10 anos depois, ainda odeio isso. Eu vou para a guia Design, abro Report Layout e digo Mostrar em formato tabular, e tudo o mais Nesse caso específico, é obter um título real de Ano, certo? E eu prefiro o título real. No momento, queremos ver apenas o topo ou, neste caso, os anos com menor precipitação. Então, vou classificar esses dados de forma ascendente. Agora, há duas maneiras de fazer isso. Você pode abrir esta lista suspensa, ir para Mais opções de classificação, escolher Para enviar com base na soma da chuva, mas também é possível apenas entrar aqui em Dados, de A a Z para que as coisas sejam classificadas do menor ao maior. Mas eu não quero ver apenas os 5 primeiros anos, então os anos com menor precipitação,Venho aqui para o título Ano, abro este pequeno menu pendente e escolho Filtros de valor. E estou procurando o Bottom 5. Bem, não há filtro para o Bottom 5. Ahh, mas este para os dez primeiros é incrivelmente poderoso. Tudo bem, não precisa ser superior. Pode ser superior ou inferior. Não precisa ser 10; pode ser 5. Portanto, peça os 5 principais itens com base na soma da chuva e clique em OK. E aí está o nosso relatório.
Now in this case, it would be really highly unusual if we had exactly 2 years with 767.7 inches or millimeters of rain exactly, right? Just not going to happen. But you have to be cognizant of the fact that when you asked for the Top 5, if there is a tie for that position, you might get a sixth row. If there's a 3-way tie, you might get a seventh row. Alright, so just be prepared for that. Grand total here really makes no sense since we're showing just the Top 5, and they're not even 5 consecutive years. So I’m going to right-click on the word Grand Total and say Remove Grand Total. Remove Grand Total. I’m wondering if that was there in Excel 2007. If it's not there in Excel 2007, go to the Design tab, Grand Totals, Off for Rows and Columns. We’ll do the same thing, alright. So now that we have this first pivot table and it's sized correctly, I’m going to copy that pivot table, Ctrl+C, make sure to choose the entire pivot table and go there - Years with Lowest Rainfall.
Now another thing that Ken wants is the years with highest, highest rainfall. Alright, so in this case, we're going to Sort the data, Z-A descending. And then here, come back into the Value Filters, go back into Top 10 and simply change it from Bottom to Top, click OK. Alright, so once you get the first pivot table built, pivot tables are so flexible. It's incredible how easy it is to just keep changing the pivot table. Alright now, here's the- here's the gotcha, right. The thing that makes us a little bit difficult. Now we want to look at the years with the least number of rain days, alright. How many days do we have- the fewest number of days with rain? Alright, so now this is going to change the pivot table a little because I want to take the Sum of Rain out, and I’m going to replace it with Rain Day. Alright, and see that one automatically came in as sum because my formula here is always returning a numeric values. We didn't have to change it to Account. And we're looking for the years with the least rain day so we're going to sort this ascending, alright, so that gets our sort back in but we've lost the Value Filter, the Top 5. Because we took the field that it was using, Rain out. So we have to reapply that: Value Filters and say Top - Actually we want the bottom, with the Bottom 5, like that.
Alright, so every time that I take a field in or out over here in the Values area, you have to be prepared to redo the Sort and redo the Filter. So Ctrl+C to copy that and paste right here, Years with the Least Rain Days.
Now, Ken has a lot of other statistics. I'll leave this up to Ken how to do this, but see here when we change the months, months with the highest rainfall. Alright, so now, I’m going to be changing a field in the row area. So we take Year out, put the Month field in like that and then this goes back to Rain, instead of Rain Days we put Rain in. Again, they forgotten that we want a sum so you have to go back in. In 2007, it’s called the Analyze tab, it's the first pivot table tools tab. Go to Field Settings, choose something like that - beautiful. And what are we looking for? We're looking for the months with the highest rainfall. So we're going to Sort Descending: Data, Z to A, and then again here, go back in to the Value Filters, Top 10 and we will ask for the Top 5 like that, alright? So very, very flexible. You can figure out the months with the least rain days, the most rain days and so on. So copy here, Ctrl+C and come and paste.
Now, one of the thing that Ken was building, he’s building a beautiful master table. It took this daily data and summarized it by year and month. So, let's just do that. Let's put Years down the left-hand side like this. Year's down the left-hand side, I got lazy there. I tried to check Market, it went to the wrong spot. We have some rainfall but we want to see months going across. And I know I have some other data out here to the right so I’m just going to insert a whole bunch of extra columns. Insert columns that way, I know that my pivot table won't crash into that. Beautiful thing here that Month field, the 1 through 12, I take that, drag it to the columns. And I now have a report showing years down the side, months going across the top and the summary of how much rain we had in each one. Those pivot tables are just an amazing, amazing feature.
Alright, if you're new to pivot tables, my new book, Power Excel with. This book hit the bookstores January 1st , what about - 36 days ago. But the new e-book versions for the Kindle, for your iPad, and we are PDF. Those are all now available at. If you buy the book from me, if you buy the print book for me, you get all 3 of those eBook formats for free. Minor have no DRM, no hassles. We believe in no hassles. You buy the book, you get all the formats. And what’s - Click the link down there in the YouTube description to get to my page where you can buy that book.
So wrap- up: Pivot tables created in Excel 2013 or 2016, can’t be refreshed in 2007. You have to create the pivot table in 2007 to allow it to be refreshable. So our goal is to find the five months with the least rainfall. Created a large pivot table with a rainfall by month, I knew that was going to fit in more than five rows. I built it off to the side. Sort by rainfall, actually, ascending is what we did here. Change the tabular form and then open that drop-down in the first column using the Value Filters, Top 5. It’s weird, you asked for the Top 10 and you get the bottom 5. Took the Grand Total row out. Even though we're asking for 5, you might get 6 if there's ever a tie - 6 or 7. And then, once we have the first pivot table, copied it into place and then created the next pivot table and just kept doing that. Although couple of gotchas when you change from one value field to another, you have to redo the Sort & Filter. You might have to redo the Count to Sum. When you change from one row field to another you have to redo the Sort & Filter. And then, right there at the end, showed you how to create a pivot table with rows and column, alright.
Bem, ei, quero agradecer a Ken por enviar essa pergunta. Quero agradecer a você por passar por aqui. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2063.xlsm
