Yağışın en düşük beş ayı hangileri? Bir pivot tablo kullanarak bu sorunu nasıl çözeceğinizi öğrenin.
Video izle
- 2013'te oluşturulan pivot tablolar 2007'de yenilenemez
- Yenilenebilir olmasına izin vermek için 2007'de pivot tablo oluşturmanız gerekir
- Hedef, en az yağış alan beş ayı bulmaktır.
- Aylık yağış miktarı ile büyük bir pivot tablo oluşturun
- Artan yağmura göre sırala
- Tablo Biçimine Geç
- En alttaki 5'i elde etmek için Değer Filtreleri, İlk 10'u kullanın!
- Genel Toplam satırını kaldırın
- Bir bağın bu raporun size 6 veya daha fazla satır vermesine neden olabileceğini unutmayın
- İlk pivot tabloyu aldıktan sonra, yerine kopyalayın ve sonraki pivot tabloyu oluşturun
- Bir değer alanından diğerine geçtiğinizde, sıralamayı ve filtrelemeyi yeniden yapmanız gerekir
- Bir satır alanından diğerine geçtiğinizde, sıralamayı ve filtrelemeyi yeniden yapmanız gerekir
- Bonus ipucu: satırlar ve sütunlarla bir pivot tablo oluşturma
Video Metni
Podcast, Bölüm 2063'ten Excel Öğrenin: Pivot Tabloyu Kullanarak İlk veya Son Beş Ay veya Yıl.
Hey, İnternet'e tekrar hoş geldiniz, ben Bill Jelen. Bugünün sorusu Ken tarafından gönderildi. Ken'in burada, 1999'a kadar uzanan, yıllar, yıllar ve yıllar süren günlük yağış tarihlerini içeren harika bir elektronik tablosu var. Sahip olduğu gerçekten etkileyici bir veri koleksiyonu ve Ken'in en çok yağış alan ayı denemek ve bulmak için harika formülleri vardı. en az yağış. Şimdi, biliyorsunuz, bu bir pivot tablo ile çok daha kolay olacak.
Pekala, Ken hiçbir zaman bir pivot tablo oluşturmadı ve işleri daha da karmaşık hale getirmek için buradayım, Ken Excel 2007 kullanıyor. 2016'da oluşturduğum pivot tablolarımı görebiliyordu ama onları yenileyemedi. Pekala, öyleyse bu video Pivot Tablo 101: İlk pivot tablonuzu nasıl oluşturabilirsiniz?
Birincisi, Ken'in A Sütununda bu tarihi var, gerçek tarihler, iyi miyiz? Bu harika, değil mi? Ve sonra ben kullanıyorum- yılı elde etmek için = YEAR fonksiyonuna birkaç ekstra formül ekliyorum, = AY fonksiyonunu ay = GÜN fonksiyonunu elde etmek için. Ve sonra bunları tekrar birleştirin, YYYY-MM'de = TEXT işlevini kullandım, bu şekilde yıl ve ay aşağıya sahibim. Bu Ken'in verisi, buradaki yağmur verisi ve sonra bazı formüller ekledim. Ken'in 0,5 milimetreden daha az bir şeyi var, yağmur günü sayılmaz, bu yüzden orada bir formül var. Ve sonra, Bölüm 735'ten geri dönün ve yağmurlu günlerin çizgisini ve yağmursuz günlerin çizgisini nasıl hesapladığımı görmek için buna bir göz atın. Şimdi bu bugün kullanılmayacak, başka bir şey için kullanılıyordu.
Öyleyse buraya geliyoruz. Ve önce, pivot tablomuz için verileri seçmek istiyoruz. Şimdi, çoğu durumda, sadece tüm verileri seçebilirdiniz, böylece burada sadece bir hücre seçebilirsiniz, ancak bu durumda, verileri tam olarak tanımlayan bir İsim aralığı var, bu durumda, 2016. Burada oturuyoruz- Ben ' Bunu 2017'nin başında kaydediyorum. Ken'in verileri yalnızca 2016'nın sonundan geçiyor. Yani, sadece bu verileri seçeceğiz. Ve sonra Ekle sekmesinde - Ekle sekmesinde. Excel 2007, pivot tabloların Veri sekmesinden Ekle sekmesine ilk kez taşınmasıdır. Biz de şunu seçiyoruz: Pivot Tablo ve seçtiğimiz veriler, derlediğimiz veriler olacak. Ve, yeni çalışma sayfasına gitmek istemiyoruz, mevcut bir çalışma sayfasına gideceğiz ve bunu tam buraya Sütun'a koyacağım - hadi Sütun N ile devam edelim.Şimdi nihayetinde, bu verinin En Düşük Yağış Yıllarının tam burada görünmesini istiyorum, ancak bu pivot tabloyu oluştururken, bu 5'ten çok daha fazla satıra ihtiyaç duyacağını biliyorum, değil mi? Öyleyse, burada yan tarafa inşa ediyorum, tamam. Ve Tamam'ı tıklıyoruz.
Pekala, şimdi aldığınız şey şu. Raporun gideceği yer burası ve işte küçük veri setimizdeki tüm alanların bir listesi. Ve sonra, benim korkunç bir isimle adlandırdığım şey için ayrılıyoruz. Satırlar, sol tarafta olmasını istediğiniz öğelerdir. Değerler, özetlemek istediğiniz şeydir ve ardından Sütunlar, en üstte istediğiniz şeylerdir. Bunu sonunda kullanabiliriz. Bugün Filtreleri kullanmayacağız. Yani, sadece yıllık toplam yağış alan basit bir pivot tablo oluşturuyoruz, bu yüzden Yıl alanını alıp burada sol tarafa sürüklüyorum. Tüm yıllarımızın bir listesi var, tamam mı? Ve sonra bir düşünün. Bu formülü burada pivot tablo olmadan elde etmek için ne yaparsınız? SUMIF, oh evet, SUMIF. SUMIF'leri Excel 2007'de bile kullanabilirsiniz. Yani,Yağmur tarlasını alıp buraya sürükleyeceğim. Şu anda dikkatli olun - Bakın, Yağmur Sayısı'nı seçtiler, çünkü verilerde birkaç gün var veya Ken'in boş bir hücresi var, 0 yerine boş bir hücre var. Ve evet, bunu gözden geçirip düzeltmeliyiz ama Ken'in verileri. 20 yıllık veri. Bul ve Değiştir'i bile kullanmayacağım. Pekala, ben sadece- Her ne sebeple olursa olsun Ken'in bunlara sahip olması için bir sebebi olmasına saygı duyacağım, sanki boş kalmalarına izin vereceğim gibi. Ve burada, Yağmur Sayısı altında, Yağmur Sayısı sütununda bir hücre seçtiğinizden emin olacağım, Alan Ayarlarına gidip bunu Sayı'dan Toplam'a değiştireceğim, tamam mı? Yani tüm yıllarımız ve her yıl ne kadar yağmur yağdığımız var. Ve en az yağış alan yılları arıyoruz.Şu anda dikkatli olun - Bakın, Yağmur Sayısı'nı seçtiler, çünkü verilerde birkaç gün var veya Ken'in boş bir hücresi var, 0 yerine boş bir hücre var. Ve evet, bunu gözden geçirip düzeltmeliyiz ama Ken'in verileri. 20 yıllık veri. Bul ve Değiştir'i bile kullanmayacağım. Pekala, ben sadece- Her ne sebeple olursa olsun Ken'in bunlara sahip olması için bir sebebi olmasına saygı duyacağım, sanki boş kalmalarına izin vereceğim gibi. Ve burada, Yağmur Sayısı altında, Yağmur Sayısı sütununda bir hücre seçtiğinizden emin olacağım, Alan Ayarlarına gidip bunu Sayı'dan Toplam'a değiştireceğim, tamam mı? Yani tüm yıllarımız ve her yıl ne kadar yağmur yağdığımız var. Ve en az yağış alan yılları arıyoruz.Şu anda dikkatli olun - Bakın, Yağmur Sayısı'nı seçtiler, çünkü verilerde birkaç gün var veya Ken'in boş bir hücresi var, 0 yerine boş bir hücre var. Ve evet, bunu gözden geçirip düzeltmeliyiz ama Ken'in verileri. 20 yıllık veri. Bul ve Değiştir'i bile kullanmayacağım. Pekala, ben sadece- Her ne sebeple olursa olsun Ken'in bunlara sahip olması için bir sebebi olmasına saygı duyacağım, sanki boş kalmalarına izin vereceğim gibi. Ve burada, Yağmur Sayısı altında, Yağmur Sayısı sütununda bir hücre seçtiğinizden emin olacağım, Alan Ayarlarına gidip bunu Sayı'dan Toplam'a değiştireceğim, tamam mı? Yani tüm yıllarımız ve her yıl ne kadar yağmur yağdığımız var. Ve en az yağış alan yılları arıyoruz.s çünkü verilerde birkaç gün var veya Ken'in boş bir hücresi var, 0 yerine boş bir hücre var. Ve evet, bunu gözden geçirip düzeltmeliyiz ama bu Ken'in verisi. 20 yıllık veri. Bul ve Değiştir'i bile kullanmayacağım. Pekala, ben sadece- Her ne sebeple olursa olsun Ken'in bunlara sahip olması için bir sebebi olmasına saygı duyacağım, sanki boş kalmalarına izin vereceğim gibi. Ve burada, Yağmur Sayısı altında, Yağmur Sayısı sütununda bir hücre seçtiğinizden emin olacağım, Alan Ayarlarına gidip bunu Sayı'dan Toplam'a değiştireceğim, tamam mı? Yani tüm yıllarımız ve her yıl ne kadar yağmur yağdığımız var. Ve en az yağış alan yılları arıyoruz.s çünkü verilerde birkaç gün var veya Ken'in boş bir hücresi var, 0 yerine boş bir hücre var. Ve evet, bunu gözden geçirip düzeltmeliyiz ama bu Ken'in verisi. 20 yıllık veri. Bul ve Değiştir'i bile kullanmayacağım. Pekala, ben sadece- Her ne sebeple olursa olsun Ken'in bunlara sahip olması için bir sebebi olmasına saygı duyacağım, sanki boş kalmalarına izin vereceğim gibi. Ve burada, Yağmur Sayısı altında, Yağmur Sayısı sütununda bir hücre seçtiğinizden emin olacağım, Alan Ayarlarına gidip bunu Sayı'dan Toplam'a değiştireceğim, tamam mı? Yani tüm yıllarımız ve her yıl ne kadar yağmur yağdığımız var. Ve en az yağış alan yılları arıyoruz.s verileri. 20 yıllık veri. Bul ve Değiştir'i bile kullanmayacağım. Pekala, ben sadece- Her ne sebeple olursa olsun Ken'in bunlara sahip olması için bir sebebi olmasına saygı duyacağım, sanki boş kalmalarına izin vereceğim gibi. Ve burada, Yağmur Sayısı altında, Yağmur Sayısı sütununda bir hücre seçtiğinizden emin olacağım, Alan Ayarlarına gidip bunu Sayı'dan Toplam'a değiştireceğim, tamam mı? Yani tüm yıllarımız ve her yıl ne kadar yağmur yağdığımız var. Ve en az yağış alan yılları arıyoruz.s verileri. 20 yıllık veri. Bul ve Değiştir'i bile kullanmayacağım. Pekala, ben sadece- Her ne sebeple olursa olsun Ken'in bunlara sahip olması için bir sebebi olmasına saygı duyacağım, sanki boş kalmalarına izin vereceğim gibi. Ve burada, Yağmur Sayısı altında, Yağmur Sayısı sütununda bir hücre seçtiğinizden emin olacağım, Alan Ayarları'na gidip bunu Sayı'dan Toplam'a değiştireceğim, tamam mı? Yani tüm yıllarımız ve her yıl ne kadar yağmur yağdığımız var. Ve en az yağış alan yılları arıyoruz.Yağmur Sayısı sütununda bir hücre seçtiğinizden emin olacağım, Alan Ayarlarına gidip bunu Sayı'dan Toplam'a değiştireceğim, tamam mı? Yani tüm yıllarımız ve her yıl ne kadar yağmur yağdığımız var. Ve en az yağış alan yılları arıyoruz.Yağmur Sayısı sütununda bir hücre seçtiğinizden emin olacağım, Alan Ayarlarına gidip bunu Sayı'dan Toplam'a değiştireceğim, tamam mı? Yani tüm yıllarımız ve her yıl ne kadar yağmur yağdığımız var. Ve en az yağış alan yılları arıyoruz.
Pekala şimdi, beni rahatsız eden tek şey, buradaki Satır Etiketleri. Bu bize Excel 2007'de başladı, tamam mı? Ve ben - 10 yıl sonra hala bundan nefret ediyorum. Tasarım Sekmesine gidiyorum, Rapor Düzeni'ni açıyorum ve Tablo Formunda Göster diyorum ve bunların hepsini yapıyorum. Bu özel durumda gerçek bir Yılın başı olur, değil mi? Ve gerçek başlığı tercih ederim. Şu anda sadece zirveyi veya bu durumda En Düşük Yağışla Yılları görmek istiyoruz. Bu yüzden bu verileri artan şekilde sıralayacağım. Şimdi bunu yapmanın iki yolu var. Bu açılır listeyi açabilir, Diğer Sıralama Seçenekleri'ne gidebilir, Yağmur toplamına göre göndermeyi seçebilirsiniz, ancak her şeyi en düşükten en yükseğe doğru sıralamak için buraya sadece Veri'ye, A'dan Z'ye gelmek de mümkündür. Ama ben sadece ilk 5 yılı görmek istemiyorum, bu yüzden En Düşük Yağışlı Yıllar,Buraya Yıl başlığına geliyorum, bu küçük açılır menüyü açıyorum ve Değer Filtrelerini seçiyorum. Ve Alt 5'i arıyorum. Alt 5 için filtre yok. Ahh, ama bu ilk 10 için inanılmaz derecede güçlü. Pekala, üstte olmak zorunda değil. Üstte veya altta olabilir. 10 olmak zorunda değil; 5 olabilir. Bu nedenle, yağmur toplamına göre İlk 5 Öğeyi isteyin, Tamam'a tıklayın. Ve raporumuz var.
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.
Pekala, hey, bu soruyu gönderdiği için Ken'e teşekkür etmek istiyorum. Uğradığın için sana teşekkür etmek istiyorum. Bir dahaki sefere başka bir internet yayını için görüşürüz.
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2063.xlsm
