Най-долните 5 месеца - Съвети за Excel

Кои са долните пет месеца валежи? Научете как да решите този проблем с помощта на обобщена таблица.

Гледам видео

• Обобщените таблици, създадени през 2013 г., не могат да бъдат опреснени през 2007 г.
• Трябва да създадете обобщена таблица през 2007 г., за да можете да я опреснявате
• Целта е да се намерят петте месеца с най-малко валежи
• Създайте голяма въртяща таблица с валежи по месеци
• Сортирай по валежи възходящо
• Преминете към табличен формуляр
• Използвайте Филтрите за стойност, Топ 10, за да получите най-долните 5!
• Премахнете реда от общата сума
• Имайте предвид, че равенството може да накара този отчет да ви даде 6 или повече реда
• След като имате първата обобщена таблица, копирайте я на място и създайте следващата обобщена таблица
• Когато превключвате от едно поле на стойност в друго, трябва да направите повторно сортирането и филтрирането
• Когато превключвате от едно поле на ред в друго, трябва да направите повторно сортирането и филтрирането
• Бонус съвет: създаване на обобщена таблица с редове и колони

Видео транскрипция

Научете Excel от Подкаст, Епизод 2063: Най-горните или най-долните пет месеца или години с помощта на обобщена таблица.

Хей, добре дошъл в мрежата, аз съм Бил Джелен. Днешният въпрос, изпратен от Кен. Кен има невероятна електронна таблица с години и години и години на ежедневните дати на валежите, датиращи от 1999 г. Наистина впечатляваща колекция от данни, с които разполага, и Кен имаше няколко невероятни формули, за да опита да намери месеца с най-много валежи, на най-малко валежи. Така че сега, знаете ли, това ще бъде много по-лесно с обобщена таблица.

Добре сега, Кен никога не е създавал обобщена таблица и за допълнително усложняване на нещата, аз съм тук в Excel 2016, Кен използва Excel 2007. Моите обобщени таблици, които създадох през 2016 г., той можеше да го види, но не можа да ги опресни. Добре, така че това видео е обобщена таблица 101: Как да създадете първата си обобщена таблица.

Първо, Кен има тази дата в колона А, реални дати, добре ли сме? Това е страхотно, нали? И тогава използвам - вмъкнете тук няколко допълнителни формули във функцията = YEAR, за да получите годината, = MONTH функция, за да получите месеца, = DAY функция. И след това ги обединих обратно, всъщност използвах функцията = TEXT в ГГГГ-ММ, по този начин имам година и месец надолу. Това са данните на Кен, данните за дъжда тук и тогава добавих няколко формули. Ken's има нещо по-малко от .5 милиметра, не се брои за дъждовен ден, така че там има формула. И тогава, от Епизод 735, върнете се и погледнете това, за да видите как изчислих ивицата дни с дъжд и ивица дни без дъжд. Сега това няма да се използва днес, което се използва за нещо друго.

И така, ние идваме тук. И първо, искаме да изберем данните за нашата обобщена таблица. Сега, в повечето случаи, можете просто да изберете всички данни, за да можете просто да изберете една клетка тук, но в този случай има диапазон от имена, който определя данните точно през, в този случай, 2016 г. Ние седим тук - I ' m записвам това в началото на 2017 г. Данните на Кен минават само до края на 2016 г. И така, ще изберем точно тези данни. И след това в раздела Вмъкване - Раздел Вмъкване. Excel 2007, за първи път обобщените таблици се преместват от раздела Данни обратно в раздела Вмъкване. Затова избираме: Сводна таблица и избраните от нас данни ще бъдат данните, от които изграждаме. И ние не искаме да отидем на нов работен лист, ще отидем на съществуващ работен лист и ще го сложа точно тук в колона - нека отидем с колона N.Сега в крайна сметка искам тези данни Години с най-ниски валежи да се появят точно тук, но знам, че докато изграждам тази обобщена таблица, ще са необходими много повече редове от тези 5, нали? Така че, аз го изграждам отстрани тук, добре. И ние щракваме ОК.

Alright, now here's what you get. This is where the report is going to go and here's a list of all the fields we have in our little data set. And then we have, for what I call horribly named drops out. Rows is the items you want down the left-hand side. Values is the thing that you want to sum up and then Columns are the things you want across the top. We might use this at the end. We're not going to use Filters today. So, we're just building a simple little pivot table with total rainfall by year, so I take the Year field and drag it down here to the left-hand side. There's a list of all of our years, alright? And then, think about it. To get this formula here without a pivot table, you'd be doing what? SUMIF, oh yeah, SUMIF. You could even use SUMIF’s back in Excel 2007. So, I’m going to take the Rain field and drag that here. Right now watch out for the - See, they chose Count of Rain, that's because there's a few days in the data or Ken has a blank cell, an empty cell instead of a 0. And yeah, we should go through and fix that but it's Ken's data. It's 20 years worth of data. I’m not going to go through even using Find & Replace. Alright, I’m just- For whatever reason I’m going to respect that Ken has a reason to have those, like I’m going to allow them to stay blank. And here, under Count of Rain, I’m going to make sure to choose a cell in the Count of Rain column, go to Field Settings, and change that from Count to Sum, alright? So there is all of our years and how much rain we had in each year. And we're looking for the years with the lowest rainfall.

Добре сега, едно нещо, което ме притеснява, е тази дума тук Row Labels. Това започна да ни се случва в Excel 2007, нали? И аз - 10 години по-късно все още презирам това. Отивам в раздела „Дизайн“, отварям оформлението на отчета и казвам „Покажи в табличен формуляр“ и всичко останало. В този конкретен случай е да получите истинска насока там на годината, нали? И предпочитам истинската насока. В момента искаме да видим само върха или в този случай годините с най-ниски валежи. Така че ще сортирам тези данни възходящо. Сега има два начина да направите това. Можете да отворите това падащо меню, да отидете на Още опции за сортиране, да изберете Изпращане въз основа на сумата от дъжд, но също така е възможно просто да влезете тук в Данни, от А до Я, за да получите нещата сортирани от най-ниското до най-високото. Но не искам да виждам само първите 5 години, така че годините с най-ниски валежи,Дойдох тук в заглавието Година, отворих това малко падащо меню и избрах Филтри за стойност. И аз търся Bottom 5. Е, няма филтър за Bottom 5. Ааа, но този за първите десет е невероятно мощен. Добре, не трябва да е отгоре. Тя може да бъде отгоре или отдолу. Не е задължително да е 10; може да бъде 5. И така, попитайте за Топ 5 артикула въз основа на сумата от дъжд, щракнете върху OK. И там е нашият доклад.

Сега в този случай би било наистина много необичайно, ако имахме точно 2 години с 767,7 инча или милиметри дъжд точно, нали? Просто няма да се случи. Но трябва да сте наясно с факта, че когато поискате Топ 5, ако има равенство за тази позиция, може да получите шести ред. Ако има равенство в 3 посоки, може да получите седми ред. Добре, просто бъдете готови за това. Общият сбор тук наистина няма смисъл, тъй като показваме само Топ 5 и те дори не са 5 поредни години. Така че щраквам с десния бутон върху думата Общ сбор и казвам Премахване на общия сбор. Премахнете общата сума. Чудя се дали това е било в Excel 2007. Ако го няма в Excel 2007, отидете в раздела Дизайн, Общи суми, Изключено за редове и колони. Ще направим същото нещо, добре.Така че сега, когато имаме тази първа обобщена таблица и тя е оразмерена правилно, ще копирам тази обобщена таблица, Ctrl + C, не забравяйте да изберете цялата обобщена таблица и отидете там - Години с най-ниски валежи.

Сега друго нещо, което Кен иска, са годините с най-много, най-много валежи. Добре, така че в този случай ще сортираме данните, ZA намалява. И след това тук, върнете се във Филтрите за стойност, върнете се в Топ 10 и просто го променете от Отдолу на Горе, щракнете върху OK. Добре, така че след като изградите първата обобщена таблица, тя е толкова гъвкава. Невероятно е колко лесно е просто да продължите да сменяте пивотната таблица. Добре сега, ето - тук е разбраното, нали. Нещото, което ни прави малко трудни. Сега искаме да разгледаме годините с най-малко дъждовни дни, добре. Колко дни имаме- най-малко дни с дъжд? Добре, така че сега това ще промени малко обобщената таблица, защото искам да извадя Сумата на дъжда и ще я заменя с Ден на дъжда. Добре,и вижте, че едно автоматично влиза като сума, защото моята формула тук винаги връща числови стойности. Не трябваше да го променяме на акаунт. И ние търсим годините с най-малко дъждовни дни, така че ще сортираме този възходящ, добре, така че това да ни върне сортирането, но загубихме Филтъра за стойност, Топ 5. Защото взехме полето че използва, Rain out. Така че трябва да приложим отново това: Филтри за стойност и да кажем Top - Всъщност искаме дъното, с Bottom 5, така.Защото взехме полето, което използваше, Rain out. Така че трябва да приложим отново това: Филтри за стойности и да кажем Top - Всъщност искаме дъното, с Bottom 5, така.Защото взехме полето, което използваше, Rain out. Така че трябва да приложим отново това: Филтри за стойност и да кажем Top - Всъщност искаме дъното, с Bottom 5, така.

Добре, така че всеки път, когато взема поле или навътре тук в областта Стойности, трябва да сте подготвени да повторите Сортирането и да повторите филтъра. Така че Ctrl + C, за да копирате това и да го поставите тук, Години с най-малко дъждовни дни.

Сега Кен има много други статистически данни. Ще оставя това на Кен как да го направи, но вижте тук, когато сменим месеците, месеците с най-много валежи. Добре, така че сега ще променя поле в областта на реда. И така, изваждаме Година, поставяме полето Месец така и след това се връща към Дъжд, вместо Дни на Дъжд поставяме Дъжд. Отново забравиха, че искаме сума, така че трябва да се върнете. През 2007 г. той се нарича раздел Анализиране, това е първият раздел инструменти за обобщена таблица. Отидете в Настройки на полето, изберете нещо подобно - красиво. И какво търсим? Търсим месеците с най-много валежи. Така че ще отидем към Сортиране по низходящо: Данни, Z до A и след това отново тук, върнете се към Филтрите за стойност, Топ 10 и ще поискаме Топ 5 така, нали? Толкова много, много гъвкави.Можете да разберете месеците с най-малко дъждовни дни, най-много дъждовни дни и т.н. Така че копирайте тук, Ctrl + C и елате и поставете.

Сега, едно от нещата, които Кен изграждаше, той изгражда красива майсторска маса. Тя взе тези ежедневни данни и ги обобщи по години и месеци. Така че, нека просто направим това. Нека поставим Години отляво по този начин. Година по лявата страна, там ме мързелува. Опитах се да проверя Market, той отиде на грешното място. Имаме малко валежи, но искаме да видим месеци напред. И знам, че имам някои други данни тук вдясно, така че просто ще вмъкна цял куп допълнителни колони. Поставете колони по този начин, знам, че моята обобщена таблица няма да се срине в това. Красиво нещо тук, че полето Month, от 1 до 12, вземам това, плъзнете го до колоните. И сега имам доклад, показващ години надолу, месеци, преминаващи отгоре, и обобщение на това колко дъжд сме имали във всеки един.Тези обобщени таблици са просто невероятна, невероятна функция.

Добре, ако сте нов в обобщените таблици, новата ми книга, Power Excel с. Тази книга се появи в книжарниците на 1 януари, какво ще кажете - преди 36 дни. Но новите версии на електронни книги за Kindle, за вашия iPad и ние сме PDF. Всички те вече са достъпни на. Ако купите книгата от мен, ако купите печатната книга за мен, получавате безплатно всичките 3 от тези формати на електронни книги. Непълнолетните нямат DRM, караница. Ние вярваме в никакви кавги. Купувате книгата, получавате всички формати. И какво - Щракнете върху връзката долу в описанието на YouTube, за да стигнете до моята страница, където можете да си купите тази книга.

Така че обобщение: Обобщените таблици, създадени в Excel 2013 или 2016, не могат да се обновяват през 2007 г. Трябва да създадете обобщената таблица през 2007 г., за да може да се опреснява. Така че нашата цел е да намерим петте месеца с най-малко валежи. Създадох голяма въртяща таблица с валежи по месеци, знаех, че ще се побере в повече от пет реда. Изградих го отстрани. Сортиране по валежи, всъщност възходящо е това, което направихме тук. Променете табличната форма и след това отворете падащото меню в първата колона, като използвате Филтри за стойности, Топ 5. Странно е, че поискахте Топ 10 и получавате долната част 5. Извадихте реда от общата сума. Въпреки че искаме 5, може да получите 6, ако някога има равенство - 6 или 7. И след това, след като имаме първата обобщена таблица, я копирахме на място и след това създадохме следващата обобщена таблица и просто продължихме да правим това .Въпреки че има няколко проблеми, когато промените от едно поле на стойност на друго, трябва да повторите Сортирането и филтрирането. Може да се наложи да повторите преброяването до сумиране. Когато превключвате от едно поле на ред в друго, трябва да повторите Сортирането и филтрирането. И тогава, точно там в края, ви показа как да създадете обобщена таблица с редове и колони, добре.

Е, хей, искам да благодаря на Кен, че изпрати този въпрос. Искам да ви благодаря, че отбихте. Ще се видим следващия път за поредното излъчване от.

Свали файл

Изтеглете примерния файл тук: Podcast2063.xlsm