Које су доњих пет месеци падавина? Научите како да решите овај проблем помоћу пивот табеле.
Погледајте видео
- Изведене табеле креиране 2013. године не могу се освежити у 2007. години
- Треба да направите пивот табелу 2007. да бисте је омогућили да се освежи
- Циљ је пронаћи пет месеци са најмање падавина
- Направите велику стожерну табелу са падавинама по месецима
- Поредај по падавинама растуће
- Пређите на Табеларни образац
- Користите филтере вредности, првих 10, да бисте добили доњих 5!
- Уклоните свеукупни ред
- Имајте на уму да кравата може довести до тога да вам овај извештај додели 6 или више редова
- Када имате прву пивот табелу, копирајте је на своје место и креирајте следећу пивот табелу
- Када промените из једног поља вредности у друго, морате поново да извршите сортирање и филтрирање
- Када промените поље из једног реда у друго, морате поново да извршите сортирање и филтрирање
- Бонус савет: стварање пивот табеле са редовима и колонама
Видео транскрипт
Научите Екцел из Подцаста, епизода 2063: Врх или дно пет месеци или година помоћу пивот табеле.
Хеј, добродошао назад на нетцаст, ја сам Билл Јелен. Данашње питање које је послао Кен. Кен овде има невероватну прорачунску табелу са годинама и годинама и годинама дневних датума падавина, почев од 1999. Заиста импресивна збирка података које поседује, а Кен је имао неколико невероватних формула како би покушао да пронађе месец са највише падавина, на најмање падавина. Па сада, знате, ово ће бити много лакше са пивот табелом.
У реду, Кен никада није створио пивот табелу и да додатно закомплицирам ствари, ја сам овде у Екцел 2016, Кен користи Екцел 2007. Моје пивот табеле које сам креирао 2016. могао је да га види, али није могао да их освежи. У реду, овај видео је пивот табела 101: Како направити своју прву табелу.
Прво, Кен има овај датум у колони А, стварне датуме, јесмо ли добри? То је супер, зар не? А онда користим - убаците овде пар додатних формула овде у функцији = ИЕАР да бисте добили годину, = МОНТХ функцију да бисте добили месец, = ДАИ функцију. А онда их поново спојим, заправо сам користио функцију = ТЕКСТ у ГГГГ-ММ, на тај начин имам годину и месец мање. Ово су Кенови подаци, овде су подаци о киши, а затим сам додао неке формуле. Кен'с има нешто мање од 0,5 милиметра, не рачуна се као кишни дан, па тамо постоји формула. А онда, из Епизоде 735, вратите се и погледајте то како бисте видели како сам израчунао низ дана са кишом и низ дана без кише. То се данас неће користити, него за нешто друго.
Дакле, долазимо овде. И прво, желимо да одаберемо податке за нашу пивот табелу. Сада, у већини случајева, можете само да одаберете све податке како бисте овде могли да изаберете само једну ћелију, али у овом случају постоји опсег Имена који дефинише податке управо кроз, у овом случају, 2016. Седимо овде - ја ' м снимам ово почетком 2017. Кенови подаци пролазе само до краја 2016. Дакле, изабраћемо управо те податке. А затим на картици Уметање - картица Уметање. Екцел 2007, ово је први пут да се изведене табеле пребацују са картице Подаци натраг на картицу Уметање. Дакле, ми бирамо: Збирна табела, а наши изабрани подаци биће подаци из којих градимо. И, не желимо да идемо на нови радни лист, идемо на постојећи радни лист и поставићу то управо овде у колони - идемо са колоном Н.Сада на крају, желим да се ови подаци Године са најнижом кишом појаве управо овде, али знам да ће, док градим ову стожерну табелу, требати много више редова од оних 5, зар не? Дакле, овде га градим са стране, у реду. И кликнемо ОК.
У реду, ево шта ћете добити. Ово је место где ће извештај ићи, а ево листе свих поља која имамо у нашем малом скупу података. А онда смо, јер оно што ја називам ужасно названим испада. Ред је ставка коју желите на левој страни. Вредности су ствар коју желите да сумирате, а затим су колоне ствари које желите преко врха. Ово бисмо могли искористити на крају. Данас нећемо користити Филтере. Дакле, управо градимо једноставну малу стожерну табелу са укупном количином падавина по години, па узмем поље Година и превучем га овде доле на леву страну. Постоји списак свих наших година, у реду? А онда, размислите о томе. Да бисте ову формулу добили без пивот табеле, шта бисте радили? СУМИФ, ох да, СУМИФ. Можете чак и да користите СУМИФ-ове позадине у програму Екцел 2007. Дакле,Отићи ћу на поље кише и одвући то овде. Тренутно припазите на - Видите, изабрали су Цоунт оф Раин, то је зато што има неколико дана података или Кен има празну ћелију, празну ћелију уместо 0. И да, требали бисмо то проћи и поправити, али то су Кенови подаци. Подаци су вредни 20 година. Нећу проћи ни коришћење функције Пронађи и замени. У реду, ја само- Из било којег разлога који ћу поштовати да Кен има разлога да их има, као што ћу им дозволити да остану празни. И овде, под Бројем кише, обавезно одаберем ћелију у колони Бројање кише, идем у Поставке поља и променим то из Бројање у Збир, у реду? Дакле, постоје све наше године и колико смо кише имали сваке године. И тражимо године са најмање кише.Тренутно припазите на - Видите, изабрали су Цоунт оф Раин, то је зато што има неколико дана података или Кен има празну ћелију, празну ћелију уместо 0. И да, требали бисмо то проћи и поправити, али то су Кенови подаци. Подаци су вредни 20 година. Нећу проћи ни коришћење функције Пронађи и замени. У реду, ја само- Из било којег разлога који ћу поштовати да Кен има разлога да их има, као што ћу им дозволити да остану празни. И овде, под Бројем кише, обавезно одаберем ћелију у колони Бројање кише, идем у Поставке поља и променим то из Бројање у Збир, у реду? Дакле, постоје све наше године и колико смо кише имали сваке године. И тражимо године са најмање кише.Тренутно припазите на - Видите, изабрали су Цоунт оф Раин, то је зато што има неколико дана података или Кен има празну ћелију, празну ћелију уместо 0. И да, требали бисмо то проћи и поправити, али то су Кенови подаци. Подаци су вредни 20 година. Нећу проћи ни коришћење функције Пронађи и замени. У реду, ја само- Из било којег разлога који ћу поштовати да Кен има разлога да их има, као што ћу им дозволити да остану празни. И овде, под Бројем кише, обавезно одаберем ћелију у колони Бројање кише, идем у Поставке поља и променим то из Бројање у Збир, у реду? Дакле, постоје све наше године и колико смо кише имали сваке године. И тражимо године са најмање кише.с јер постоји неколико дана у подацима или Кен има празну ћелију, празну ћелију уместо 0. И да, требали бисмо то проћи и поправити, али то су Кенови подаци. Подаци су вредни 20 година. Нећу проћи ни коришћење функције Пронађи и замени. У реду, ја само- Из било којег разлога који ћу поштовати да Кен има разлога да их има, као што ћу им дозволити да остану празни. И овде, под Бројем кише, обавезно ћу изабрати ћелију у колони Бројање кише, отићи у Поставке поља и променити то из Бројање у Збир, у реду? Дакле, постоје све наше године и колико смо кише имали сваке године. И тражимо године са најмање кише.с јер постоји неколико дана у подацима или Кен има празну ћелију, празну ћелију уместо 0. И да, требали бисмо то проћи и поправити, али то су Кенови подаци. Подаци су вредни 20 година. Нећу проћи ни коришћење функције Пронађи и замени. У реду, ја само- Из било којег разлога који ћу поштовати да Кен има разлога да их има, као што ћу им дозволити да остану празни. И овде, под Бројем кише, обавезно одаберем ћелију у колони Бројање кише, идем у Поставке поља и променим то из Бројање у Збир, у реду? Дакле, постоје све наше године и колико смо кише имали сваке године. И тражимо године са најмање кише.с подаци. Подаци су вредни 20 година. Нећу проћи ни коришћење функције Пронађи и замени. У реду, ја само- Из било којег разлога који ћу поштовати да Кен има разлога да их има, као што ћу им дозволити да остану празни. И овде, под Бројем кише, обавезно одаберем ћелију у колони Бројање кише, идем у Поставке поља и променим то из Бројање у Збир, у реду? Дакле, постоје све наше године и колико смо кише имали сваке године. И тражимо године са најмање кише.с подаци. Подаци су вредни 20 година. Нећу проћи ни коришћење функције Пронађи и замени. У реду, ја само- Из било којег разлога који ћу поштовати да Кен има разлога да их има, као што ћу им дозволити да остану празни. И овде, под Бројем кише, обавезно одаберем ћелију у колони Бројање кише, идем у Поставке поља и променим то из Бројање у Збир, у реду? Дакле, постоје све наше године и колико смо кише имали сваке године. И тражимо године са најмање кише.Обавезно ћу одабрати ћелију у колони Бројање кише, отићи у Поставке поља и променити то из Бројање у Збир, у реду? Дакле, постоје све наше године и колико смо кише имали сваке године. И тражимо године са најмање кише.Обавезно ћу одабрати ћелију у колони Бројање кише, отићи у Поставке поља и променити то из Бројање у Збир, у реду? Дакле, постоје све наше године и колико смо кише имали сваке године. И тражимо године са најмање кише.
У реду, једна ствар која ме мучи је ова реч овде Ров Лабелс. То нам се почело дешавати у програму Екцел 2007, у реду? И ја - 10 година касније то и даље презирем. Идем на картицу Дизајн, отворим Изглед извештаја и кажем Прикажи у табеларном облику и све то. У овом конкретном случају је стварно кренути тамо године, зар не? И више волим прави наслов. Тренутно желимо да видимо само врх или, у овом случају, Године са најнижом кишом. Па ћу сортирати ове податке у порасту. Сада постоје два начина за то. Можете да отворите овај падајући мени, одете на Још опција сортирања, изаберете На слање на основу зброја кише, али такође је могуће само доћи овде у Подаци, А до Ж да бисте сортирали ствари од најнижег до највишег. Али не желим да видим само првих 5 година, тако да ће бити године са најнижом кишом,Долазим овде у наслов Година, отворим овај мали падајући мени и одаберем Филтери вредности. И ја тражим Дно 5. Па, не постоји филтер за Дно 5. Ахх, али овај за првих десет је невероватно моћан. У реду, не мора бити врх. Може бити горња или доња. Не мора бити 10; може бити 5. Дакле, затражите Топ 5 предмета на основу зброја кише, кликните ОК. И ту је наш извештај.
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.
Па, хеј, желим да захвалим Кену што је послао то питање. Желим да ти захвалим што си свратио. Видимо се следећи пут за још једно емитовање од.
Скини докуменат
Преузмите датотеку узорка овде: Подцаст2063.клсм
