Które pięć miesięcy opadów jest najgorszych? Dowiedz się, jak rozwiązać ten problem za pomocą tabeli przestawnej.
Obejrzyj wideo
- Tabel przestawnych utworzonych w 2013 roku nie można odświeżyć w 2007 roku
- Musisz utworzyć tabelę przestawną w 2007 roku, aby umożliwić jej odświeżanie
- Celem jest znalezienie pięciu miesięcy z najmniejszą ilością opadów
- Utwórz dużą tabelę przestawną z opadami miesięcznymi
- Sortuj według opadów rosnących
- Zmień na formularz tabelaryczny
- Użyj filtrów wartości, Top 10, aby uzyskać najgorsze 5!
- Usuń wiersz Suma całkowita
- Zwróć uwagę, że remis może spowodować, że w raporcie zostanie wyświetlonych 6 lub więcej wierszy
- Gdy masz już pierwszą tabelę przestawną, skopiuj ją na miejsce i utwórz następną tabelę przestawną
- W przypadku zmiany z jednego pola wartości na inne należy ponownie przeprowadzić sortowanie i filtrowanie
- Kiedy zmieniasz jedno pole wiersza na inne, musisz ponownie przeprowadzić sortowanie i filtrowanie
- Dodatkowa wskazówka: utworzenie tabeli przestawnej z wierszami i kolumnami
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2063: Góra lub dół pięć miesięcy lub lat, korzystając z tabeli przestawnej.
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Dzisiejsze pytanie wysłane przez Kena. Ken ma tutaj niesamowity arkusz kalkulacyjny z latami, latami i latami codziennych dat opadów, sięgającymi 1999 roku. Naprawdę imponujący zbiór danych, które posiada, i Ken miał kilka niesamowitych wzorów, aby spróbować znaleźć miesiąc z największą ilością opadów, na najmniej opadów. Więc wiesz, będzie to o wiele łatwiejsze dzięki tabeli przestawnej.
W porządku, Ken nigdy nie utworzył tabeli przestawnej i aby jeszcze bardziej skomplikować sprawy, jestem tutaj w programie Excel 2016, Ken używa programu Excel 2007. Moje tabele przestawne, które utworzyłem w 2016 roku, widział go, ale nie mógł ich odświeżyć. W porządku, więc ten film to Tabela przestawna 101: Jak utworzyć pierwszą tabelę przestawną.
Po pierwsze, Ken ma tę datę w kolumnie A, prawdziwe daty, czy jesteśmy dobrzy? To niesamowite, prawda? A potem używam - wstaw kilka dodatkowych formuł tutaj w funkcji = ROK, aby uzyskać rok, = funkcja MIESIĄC, aby uzyskać miesiąc, funkcja = DZIEŃ. A potem łączę je z powrotem razem, właściwie użyłem funkcji = TEKST w RRRR-MM, w ten sposób mam rok i miesiąc w dół. To są dane Kena, dane dotyczące deszczu tutaj, a następnie dodałem kilka formuł. Ken ma mniej niż 0,5 milimetra, nie liczy się jako deszczowy dzień, więc jest tam wzór. A potem, od odcinka 735, wróć i spójrz na to, aby zobaczyć, jak obliczyłem pasmo dni z deszczem i pasmo dni bez deszczu. To nie będzie dzisiaj używane, to było używane do czegoś innego.
Więc przychodzimy tutaj. Najpierw chcemy wybrać dane do naszej tabeli przestawnej. W większości przypadków wystarczy zaznaczyć wszystkie dane, aby wybrać tutaj jedną komórkę, ale w tym przypadku istnieje zakres Nazwa, który definiuje dane tylko do końca, w tym przypadku do roku 2016. Siedzimy tutaj - ja ” m rejestruję to na początku 2017 roku. Dane Kena przechodzą tylko do końca 2016 roku. Więc wybierzemy tylko te dane. A następnie na karcie Wstaw- Zakładka Wstaw. W programie Excel 2007 po raz pierwszy tabele przestawne są przenoszone z karty Dane z powrotem na kartę Wstawianie. Wybieramy więc: Tabela przestawna, a wybrane przez nas dane będą danymi, z których będziemy budować. I nie chcemy przechodzić do nowego arkusza roboczego, zamierzamy przejść do istniejącego arkusza roboczego i wstawię go tutaj, w kolumnie - przejdźmy do kolumny N.Ostatecznie chcę, aby te dane Lata z najniższymi opadami deszczu pojawiły się tutaj, ale wiem, że kiedy buduję tę tabelę przestawną, będzie potrzebować znacznie więcej wierszy niż te 5, prawda? Więc buduję to z boku, w porządku. Klikamy OK.
W porządku, oto co otrzymałeś. Tutaj trafi raport, a oto lista wszystkich pól, które mamy w naszym małym zestawie danych. A potem mamy, bo to, co nazywam okropnie nazwaną, odpada. Wiersze to elementy, które chcesz umieścić po lewej stronie. Wartości to rzecz, którą chcesz podsumować, a kolumny to rzeczy, które chcesz u góry. Moglibyśmy to wykorzystać na końcu. Nie będziemy dzisiaj używać filtrów. Więc tworzymy tylko prostą małą tabelę przestawną z całkowitymi opadami rocznymi, więc biorę pole Rok i przeciągam je tutaj na lewą stronę. Jest lista wszystkich naszych lat, dobrze? A potem pomyśl o tym. Aby uzyskać tę formułę bez tabeli przestawnej, co zrobiłbyś? SUMIF, o tak, SUMIF. Możesz nawet użyć funkcji SUMIF w programie Excel 2007. Tak więc,Mam zamiar wziąć Pole Deszczu i przeciągnąć to tutaj. W tej chwili uważaj na - Widzisz, wybrali Count of Rain, to dlatego, że w danych jest kilka dni lub Ken ma pustą komórkę, pustą komórkę zamiast 0. I tak, powinniśmy przejść i to naprawić, ale to dane Kena. To dane z 20 lat. Nie zamierzam nawet używać funkcji Znajdź i zamień. W porządku, po prostu … Z jakiegoś powodu mam zamiar szanować to, że Ken ma powód, żeby je mieć, jakbym pozwolił im pozostać pustymi. I tutaj, pod Count of Rain, upewnię się, że wybrałem komórkę w kolumnie Count of Rain, przejdę do Ustawień pola i zmienię to z Count na Sum, dobrze? Mamy więc wszystkie lata i ile deszczu mieliśmy każdego roku. I szukamy lat z najmniejszymi opadami.W tej chwili uważaj na - Widzisz, wybrali Count of Rain, to dlatego, że w danych jest kilka dni lub Ken ma pustą komórkę, pustą komórkę zamiast 0. I tak, powinniśmy przejść i to naprawić, ale to dane Kena. To dane z 20 lat. Nie zamierzam nawet używać funkcji Znajdź i zamień. W porządku, po prostu … Z jakiegoś powodu mam zamiar szanować to, że Ken ma powód, żeby je mieć, jakbym pozwolił im pozostać pustymi. I tutaj, pod Count of Rain, upewnię się, że wybrałem komórkę w kolumnie Count of Rain, przejdę do Ustawień pola i zmienię to z Count na Sum, dobrze? Mamy więc wszystkie lata i ile deszczu mieliśmy każdego roku. I szukamy lat z najmniejszymi opadami.W tej chwili uważaj na - Widzisz, wybrali Count of Rain, to dlatego, że jest kilka dni w danych lub Ken ma pustą komórkę, pustą komórkę zamiast 0. I tak, powinniśmy przejść i to naprawić, ale to dane Kena. To dane z 20 lat. Nie zamierzam nawet używać funkcji Znajdź i zamień. W porządku, po prostu … Z jakiegoś powodu mam zamiar szanować to, że Ken ma powód, żeby je mieć, jakbym pozwolił im pozostać pustymi. I tutaj, pod Count of Rain, upewnię się, że wybrałem komórkę w kolumnie Count of Rain, przejdę do Ustawień pola i zmienię to z Count na Sum, dobrze? Mamy więc wszystkie lata i ile deszczu mieliśmy każdego roku. I szukamy lat z najmniejszymi opadami.ponieważ w danych jest kilka dni lub Ken ma pustą komórkę, pustą komórkę zamiast 0. I tak, powinniśmy to sprawdzić i naprawić, ale to dane Kena. To dane z 20 lat. Nie zamierzam nawet używać funkcji Znajdź i zamień. W porządku, po prostu … Z jakiegoś powodu mam zamiar szanować to, że Ken ma powód, żeby je mieć, jakbym pozwolił im pozostać pustymi. I tutaj, pod Count of Rain, upewnię się, że wybrałem komórkę w kolumnie Count of Rain, przejdę do Ustawień pola i zmienię to z Count na Sum, dobrze? Mamy więc wszystkie lata i ile deszczu mieliśmy każdego roku. I szukamy lat z najniższymi opadami.ponieważ w danych jest kilka dni lub Ken ma pustą komórkę, pustą komórkę zamiast 0. I tak, powinniśmy to sprawdzić i naprawić, ale to dane Kena. To dane z 20 lat. Nie zamierzam nawet używać funkcji Znajdź i zamień. W porządku, po prostu … Z jakiegoś powodu mam zamiar szanować to, że Ken ma powód, żeby je mieć, jakbym pozwolił im pozostać pustymi. A tutaj, pod Count of Rain, upewnię się, że wybrałem komórkę w kolumnie Count of Rain, przejdę do Ustawień pola i zmienię to z Count na Sum, dobrze? Mamy więc wszystkie lata i ile deszczu mieliśmy każdego roku. I szukamy lat z najmniejszymi opadami.s danych. To dane z 20 lat. Nie zamierzam nawet używać funkcji Znajdź i zamień. W porządku, po prostu … Z jakiegoś powodu mam zamiar szanować to, że Ken ma powód, żeby je mieć, jakbym pozwolił im pozostać pustymi. I tutaj, pod Count of Rain, upewnię się, że wybrałem komórkę w kolumnie Count of Rain, przejdę do Ustawień pola i zmienię to z Count na Sum, dobrze? Mamy więc wszystkie lata i ile deszczu mieliśmy każdego roku. I szukamy lat z najniższymi opadami.s danych. To dane z 20 lat. Nie zamierzam nawet używać funkcji Znajdź i zamień. W porządku, po prostu … Z jakiegoś powodu mam zamiar szanować to, że Ken ma powód, żeby je mieć, jakbym pozwolił im pozostać pustymi. I tutaj, pod Count of Rain, upewnię się, że wybrałem komórkę w kolumnie Count of Rain, przejdę do Ustawień pola i zmienię to z Count na Sum, dobrze? Mamy więc wszystkie lata i ile deszczu mieliśmy każdego roku. I szukamy lat z najmniejszymi opadami.Upewnię się, że wybiorę komórkę w kolumnie Count of Rain, przejdę do Ustawień pola i zmienię to z Count na Sum, dobrze? Mamy więc wszystkie lata i ile deszczu mieliśmy każdego roku. I szukamy lat z najniższymi opadami.Upewnię się, że wybiorę komórkę w kolumnie Count of Rain, przejdę do Ustawień pola i zmienię to z Count na Sum, dobrze? Mamy więc wszystkie lata i ile deszczu mieliśmy każdego roku. I szukamy lat z najniższymi opadami.
W porządku, jedną rzeczą, która mnie niepokoi, jest to słowo tutaj Etykiety wierszy. Zaczęło się to dziać w programie Excel 2007, dobrze? A ja - 10 lat później nadal tym gardzę. Przechodzę do karty Projekt, otwieram układ raportu i mówię Pokaż w formie tabelarycznej i wszystko to. W tym konkretnym przypadku otrzymujesz prawdziwy nagłówek roku, prawda? A ja wolę prawdziwy kierunek. W tej chwili chcemy zobaczyć tylko szczyt lub w tym przypadku lata z najniższymi opadami. Więc posortuję te dane rosnąco. Teraz można to zrobić na dwa sposoby. Możesz otworzyć to menu, przejść do Więcej opcji sortowania, wybrać Do wysyłania na podstawie sumy opadów, ale można również przejść tutaj do danych, od A do Z, aby posortować rzeczy od najniższej do najwyższej. Ale nie chcę widzieć tylko najlepszych 5 lat, więc lat z najniższymi opadami deszczu,Przychodzę do nagłówka Rok, otwieram to małe menu i wybieram Filtry wartości. I szukam piątego dołu. Cóż, nie ma filtra dla piątego. Ach, ale ten dla pierwszej dziesiątki jest niesamowicie potężny. W porządku, to nie musi być najlepsze. Może być na górze lub na dole. Nie musi to być 10; może to być 5. Poproś więc o 5 najlepszych pozycji na podstawie sumy opadów, kliknij OK. A oto nasz raport.
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.
Cóż, hej, chcę podziękować Kenowi za przesłanie tego pytania. Chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2063.xlsm
