Які найнижчі п’ять місяців опадів? Дізнайтеся, як вирішити цю проблему за допомогою зведеної таблиці.
Переглянути відео
- Зведені таблиці, створені в 2013 році, не можна оновити в 2007 році
- Вам потрібно створити зведену таблицю в 2007 році, щоб вона могла бути оновленою
- Мета - знайти п’ять місяців з найменшою кількістю опадів
- Створіть велику зведену таблицю з опадами за місяцями
- Сортувати за кількістю опадів за зростанням
- Перейдіть на табличну форму
- Використовуйте фільтри значень, 10 найкращих, щоб отримати 5 нижчих!
- Видаліть рядок загальної суми
- Зверніть увагу, що зв’язок може призвести до того, що цей звіт надасть вам 6 або більше рядків
- Отримавши першу зведену таблицю, скопіюйте її на місце та створіть наступну зведену таблицю
- Коли ви переходите з одного поля значення на інше, вам доведеться повторно виконати сортування та фільтрування
- Коли ви переходите з одного поля рядка на інше, вам доведеться повторно виконати сортування та фільтрування
- Бонусна порада: створення зведеної таблиці з рядками та стовпцями
Стенограма відео
Дізнайтеся Excel з подкасту, епізод 2063: Найвищі або найнижчі п’ять місяців чи років за допомогою зведеної таблиці
Привіт, ласкаво просимо назад до трансляції, я Білл Джелен. Сьогоднішнє запитання, надіслане Кеном. Кен має тут дивовижну електронну таблицю з роками та роками та роками щоденних дат опадів, починаючи з 1999 року. Дійсно вражаюча колекція даних, які він має, і Кен мав кілька дивовижних формул, щоб спробувати знайти місяць з найбільшою кількістю опадів, найменша кількість опадів. Отже, тепер, знаєте, це буде набагато простіше за допомогою зведеної таблиці.
Гаразд, Кен ніколи не створював зведену таблицю, а щоб ще більше ускладнити ситуацію, я тут у Excel 2016, Кен використовує Excel 2007. Мої зведені таблиці, які я створив у 2016 році, він бачив його, але не міг їх оновити. Гаразд, отже, це відео - зведена таблиця 101: Як створити свою першу зведену таблицю.
По-перше, Кен має цю дату в колонці А, справжні дати, ми добре? Це чудово, так? А потім я використовую - вставляю сюди пару додаткових формул у функцію = YEAR, щоб отримати рік, = функцію MONTH, щоб отримати місяць, = функцію DAY. А потім об'єднати їх назад разом, я насправді використовував функцію = TEXT у РРРР-ММ, таким чином у мене є рік і місяць. Це дані Кена, тут дані про дощ, а потім я додав кілька формул. Кен має щось менше, ніж 0,5 міліметра, не вважається дощовим днем, тому там є формула. А потім, з епізоду 735, поверніться назад і подивіться на це, щоб побачити, як я обчислив смугу днів з дощем і смугу днів без дощу. Зараз це не буде використано сьогодні, це було використано для чогось іншого.
Отже, ми прийшли сюди. І спочатку ми хочемо вибрати дані для нашої зведеної таблиці. Зараз, у більшості випадків, ви можете просто вибрати всі дані, щоб ви могли просто вибрати одну клітинку тут, але в цьому випадку є діапазон Імен, який визначає дані просто через, в даному випадку, 2016 рік. Ми сидимо тут - я ' m записую це на початку 2017 року. Дані Кена проходять лише до кінця 2016 року. Отже, ми збираємося відібрати саме ці дані. А потім на вкладці Вставка - вкладка Вставка. Excel 2007, це перший раз, коли зведені таблиці переміщуються з вкладки Дані назад на вкладку Вставка. Отже, ми вибираємо: зведену таблицю, і вибрані нами дані будуть даними, з яких ми будуємо. І ми не хочемо переходити до нового аркуша, ми перейдемо до існуючого аркуша, і я поміщу це прямо тут, у Стовпці - підемо зі Стовпцем N.Зрештою, я хочу, щоб ці дані Роки з найнижчою кількістю опадів з’явилися саме тут, але я знаю, що, будуючи цю зведену таблицю, їй знадобиться набагато більше рядків, ніж ці 5, правда? Отже, я будую його тут збоку, добре. І ми натискаємо ОК.
Добре, ось ось що ви отримаєте. Саме сюди йтиме звіт, і ось перелік усіх полів, які ми маємо в нашому маленькому наборі даних. І тоді ми маємо, бо те, що я називаю жахливо названим, випадає. Рядки - це елементи, які ви хочете ввести ліворуч. Значення - це те, що ви хочете підсумувати, і тоді Стовпці - це те, що ви хочете вгорі. Ми можемо використати це в кінці. Сьогодні ми не збираємось використовувати фільтри. Отже, ми просто будуємо просту маленьку зведену таблицю із загальною кількістю опадів за роками, тому я беру поле Рік і перетягую його сюди вліво. Є список усіх наших років, добре? А потім, подумайте. Щоб отримати цю формулу тут без зведеної таблиці, ви б робили що? SUMIF, о так, SUMIF. Ви навіть можете використовувати SUMIF в Excel 2007. Отже,Я збираюся взяти поле Дощу і перетягнути його сюди. Прямо зараз стежте за - Дивіться, вони обрали Графа дощу, це тому, що в даних є кілька днів, або у Кена є порожня клітинка, порожня клітина замість 0. І так, нам слід пройти це і виправити це, але це дані Кена. Це дані на 20 років. Я не збираюся проходити навіть за допомогою функції Знайти та замінити. Добре, я просто … З якої б причини я не поважав, що у Кен є причина мати їх, як я збираюся дозволити їм залишатися порожніми. І тут, у розділі Графік дощу, я обов’язково виберу клітинку у стовпці Графік дощу, перейду до Налаштування поля та зміню його з Графік на Суму, добре? Отже, є всі наші роки і скільки дощів ми мали кожного року. І ми шукаємо роки з найменшою кількістю опадів.Прямо зараз стежте за - Дивіться, вони обрали Графа дощу, це тому, що в даних є кілька днів, або у Кена є порожня клітинка, порожня клітина замість 0. І так, нам слід пройти це і виправити це, але це дані Кена. Це дані на 20 років. Я не збираюся проходити навіть за допомогою функції Знайти та замінити. Добре, я просто … З якої б причини я не поважав, що у Кен є причина мати їх, як я збираюся дозволити їм залишатися порожніми. І тут, у розділі Графік дощу, я обов’язково виберу клітинку у стовпці Графік дощу, перейду до Налаштування поля та зміню його з Графік на Суму, добре? Отже, є всі наші роки і скільки дощів ми мали кожного року. І ми шукаємо роки з найменшою кількістю опадів.Прямо зараз стежте за - Дивіться, вони обрали Графа дощу, це тому, що в даних є кілька днів, або у Кена є порожня клітинка, порожня клітина замість 0. І так, нам слід пройти це і виправити це, але це дані Кена. Це дані на 20 років. Я не збираюся проходити навіть за допомогою функції Знайти та замінити. Добре, я просто … З якої б причини я не поважав, що у Кен є причина мати їх, як я збираюся дозволити їм залишатися порожніми. І тут, у розділі Графік дощу, я обов’язково виберу клітинку у стовпці Графік дощу, перейду до Налаштування поля та зміню його з Графік на Суму, добре? Отже, є всі наші роки і скільки дощів ми мали кожного року. І ми шукаємо роки з найменшою кількістю опадів.s, тому що в даних є кілька днів, або у Кена є порожня клітинка, порожня клітинка замість 0. І так, нам слід пройти це і виправити це, але це дані Кена. Це дані на 20 років. Я не збираюся проходити навіть за допомогою функції Знайти та замінити. Добре, я просто … З якої б причини я не поважав, що у Кен є причина мати їх, як я збираюся дозволити їм залишатися порожніми. І тут, у розділі Графік дощу, я обов’язково виберу клітинку у стовпці Графік дощу, перейду до Налаштування поля та зміню його з Графік на Суму, добре? Отже, є всі наші роки і скільки дощів ми мали кожного року. І ми шукаємо роки з найменшою кількістю опадів.s, тому що в даних є кілька днів, або у Кена є порожня клітинка, порожня клітинка замість 0. І так, нам слід пройти це і виправити це, але це дані Кена. Це дані на 20 років. Я не збираюся проходити навіть за допомогою функції Знайти та замінити. Добре, я просто … З якої б причини я не поважав, що у Кен є причина мати їх, як я збираюся дозволити їм залишатися порожніми. І тут, у розділі Графік дощу, я обов’язково виберу клітинку у стовпці Графік дощу, перейду до Налаштування поля та зміню його з Графік на Суму, добре? Отже, є всі наші роки і скільки дощів ми мали кожного року. І ми шукаємо роки з найменшою кількістю опадів.s даних. Це дані на 20 років. Я не збираюся проходити навіть за допомогою функції Знайти та замінити. Добре, я просто … З якої б причини я не поважав, що у Кен є причина мати їх, як я збираюся дозволити їм залишатися порожніми. І тут, у розділі Графік дощу, я обов’язково виберу клітинку у стовпці Графік дощу, перейду до Налаштування поля та зміню його з Графік на Суму, добре? Отже, є всі наші роки і скільки дощів ми мали кожного року. І ми шукаємо роки з найменшою кількістю опадів.s даних. Це дані на 20 років. Я не збираюся проходити навіть за допомогою функції Знайти та замінити. Добре, я просто … З якої б причини я не поважав, що у Кен є причина мати їх, як я збираюся дозволити їм залишатися порожніми. І тут, у розділі Графік дощу, я обов’язково виберу клітинку у стовпці Графік дощу, перейду до Налаштування поля та зміню його з Графік на Суму, добре? Отже, є всі наші роки і скільки дощів ми мали кожного року. І ми шукаємо роки з найменшою кількістю опадів.Я обов’язково виберу клітинку в стовпці Count of Rain, перейду до налаштувань поля та зміню це з Count на Sum, добре? Отже, є всі наші роки і скільки дощів ми мали кожного року. І ми шукаємо роки з найменшою кількістю опадів.Я обов’язково виберу клітинку в стовпці Count of Rain, перейду до налаштувань поля та зміню це з Count на Sum, добре? Отже, є всі наші роки і скільки дощів ми мали кожного року. І ми шукаємо роки з найменшою кількістю опадів.
Гаразд, одне, що мене бентежить, це це слово тут Рядові ярлики. Це почалося з нами в Excel 2007, добре? І я - через 10 років я все ще зневажаю це. Я переходжу на вкладку «Дизайн», відкриваю макет звіту і кажу «Показати в табличній формі», і все, що робить. У цьому конкретному випадку це справжній заголовок року, чи не так? І я віддаю перевагу справжній рубриці. Зараз ми хочемо побачити лише вершину, або в даному випадку, роки з найменшою кількістю опадів. Тож я збираюся відсортувати ці дані за зростанням. Зараз це можна зробити двома способами. Ви можете відкрити це спадне меню, перейти до Додаткові параметри сортування, вибрати Надіслати на основі суми дощу, але також можна просто зайти сюди в Дані, від А до Я, щоб отримати речі, відсортовані від найнижчого до найвищого. Але я не хочу бачити лише найкращі 5 років, тому Роки з найменшою кількістю опадів,Я приходжу сюди до заголовка Рік, відкриваю це маленьке спадне меню і вибираю Фільтри вартості. І я шукаю Bottom 5. Ну, немає фільтра для Bottom 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.
Ну, привіт, я хочу подякувати Кену за те, що він надіслав це запитання. Я хочу подякувати вам за те, що завітали. Ми побачимось наступного разу для чергової трансляції від.
Завантажити файл
Завантажте зразок файлу тут: Podcast2063.xlsm
