Какие пять месяцев меньше всего осадков? Узнайте, как решить эту проблему с помощью сводной таблицы.
Смотреть видео
- Сводные таблицы, созданные в 2013 г., нельзя обновить в 2007 г.
- Вам необходимо создать сводную таблицу в 2007 году, чтобы она могла обновляться.
- Цель - найти пять месяцев с наименьшим количеством осадков
- Создайте большую сводную таблицу с осадками по месяцам
- Сортировать по возрастанию количества осадков
- Перейти к табличной форме
- Используйте фильтры значений, Top 10, чтобы получить последние 5!
- Удалить строку "Общий итог"
- Обратите внимание, что при равенстве значений в этом отчете может быть 6 или более строк.
- Как только у вас будет первая сводная таблица, скопируйте ее на место и создайте следующую сводную таблицу.
- Когда вы переходите от одного поля значений к другому, вам нужно заново выполнить сортировку и фильтрацию
- Когда вы переходите от одного поля строки к другому, вам нужно заново выполнить сортировку и фильтрацию
- Бонусный совет: создание сводной таблицы со строками и столбцами
Стенограмма видео
Изучите Excel из подкаста, эпизод 2063: первые или последние пять месяцев или лет с использованием сводной таблицы.
Привет, добро пожаловать обратно в сеть, я Билл Джелен. Сегодняшний вопрос прислал Кен. У Кена есть потрясающая таблица с годами, годами и годами ежедневных дат выпадения осадков, начиная с 1999 года. У него действительно впечатляющий набор данных, и у Кена было несколько удивительных формул, чтобы попытаться найти месяц с наибольшим количеством осадков, наименьшее количество осадков. Теперь вы знаете, что со сводной таблицей это будет намного проще.
Хорошо, Кен никогда не создавал сводную таблицу, и, чтобы еще больше усложнить ситуацию, я здесь в Excel 2016, Кен использует Excel 2007. Мои сводные таблицы, которые я создал в 2016 году, он мог видеть его, но не мог их обновить. Хорошо, это видео - Сводная таблица 101: Как создать свою первую сводную таблицу.
Во-первых, у Кена есть дата в столбце A, настоящие даты, все в порядке? Это круто, правда? И затем я использую - вставляю пару дополнительных формул здесь, в функцию = ГОД, чтобы получить год, функцию = МЕСЯЦ, чтобы получить месяц, функцию = ДЕНЬ. А затем объедините их вместе, я фактически использовал функцию = ТЕКСТ в ГГГГ-ММ, таким образом у меня есть год и месяц вниз. Это данные Кена, здесь данные о дожде, а затем я добавил несколько формул. У Кена все меньше 0,5 миллиметра, это не считается дождливым днем, так что здесь есть формула. А затем, начиная с эпизода 735, вернитесь и посмотрите на это, чтобы увидеть, как я рассчитал полосу дней с дождем и полосу дней без дождя. Это не будет использоваться сегодня, это было использовано для чего-то другого.
Итак, мы пришли сюда. И сначала мы хотим выбрать данные для нашей сводной таблицы. Теперь в большинстве случаев вы можете просто выбрать все данные, чтобы вы могли просто выбрать здесь одну ячейку, но в этом случае есть диапазон имен, который определяет данные только до, в данном случае, 2016 года. Мы сидим здесь - я ' m записал это в начале 2017 года. Данные Кена относятся только к концу 2016 года. Итак, мы собираемся выбрать только эти данные. А потом на вкладке Вставка - вкладка Вставка. В Excel 2007 сводные таблицы впервые перемещаются с вкладки «Данные» на вкладку «Вставка». Итак, мы выбираем: Сводная таблица, и выбранные нами данные будут данными, из которых мы строим. И мы не хотим переходить к новому рабочему листу, мы собираемся перейти к существующему рабочему листу, и я собираюсь поместить его прямо здесь, в столбец - давайте перейдем к столбцу N.Теперь, в конечном счете, я хочу, чтобы эти данные «Годы с наименьшим количеством осадков» отображались прямо здесь, но я знаю, что при построении этой сводной таблицы потребуется намного больше строк, чем эти 5, верно? Итак, я строю это в стороне, хорошо. И нажимаем ОК.
Хорошо, вот что вы получили. Это то место, где будет находиться отчет, и вот список всех полей, которые у нас есть в нашем небольшом наборе данных. А потом у нас выпадает то, что я называю ужасно названным. Строки - это элементы, которые вы хотите разместить в левой части страницы. Значения - это то, что вы хотите подвести, а затем столбцы - это то, что вы хотите в верхней части. Мы можем использовать это в конце. Сегодня мы не будем использовать фильтры. Итак, мы просто создаем простую небольшую сводную таблицу с общим количеством осадков по годам, поэтому я беру поле «Год» и перетаскиваю его сюда, в левую часть. Есть список всех наших лет, хорошо? А потом подумайте об этом. Что бы вы сделали, чтобы получить здесь эту формулу без сводной таблицы? СУММИФ, о да, СУММИФ. Вы даже можете использовать СУММЕСЛИ в Excel 2007. Итак,Я собираюсь взять поле Дождя и перетащить его сюда. Прямо сейчас следите за … Видите ли, они выбрали Count of Rain, потому что в данных несколько дней или у Кена пустая ячейка, пустая ячейка вместо 0. И да, мы должны пройти и исправить это, но это данные Кена. Это данные за 20 лет. Я не собираюсь проходить даже через «Найти и заменить». Хорошо, я просто … По какой-то причине я буду уважать, что у Кена есть причина иметь их, например, я позволю им оставаться пустыми. И здесь, в разделе «Счетчик дождя», я собираюсь выбрать ячейку в столбце «Счетчик дождя», перейти в «Настройки поля» и изменить его с «Счетчик» на «Сумма», хорошо? Итак, есть все наши годы и сколько дождей было в каждом году. И мы ищем годы с наименьшим количеством осадков.Прямо сейчас следите за … Видите ли, они выбрали Count of Rain, потому что в данных несколько дней или у Кена пустая ячейка, пустая ячейка вместо 0. И да, мы должны пройти и исправить это, но это данные Кена. Это данные за 20 лет. Я не собираюсь проходить даже через «Найти и заменить». Хорошо, я просто … По какой-то причине я буду уважать, что у Кена есть причина иметь их, например, я позволю им оставаться пустыми. И здесь, в разделе «Счетчик дождя», я собираюсь выбрать ячейку в столбце «Счетчик дождя», перейти в «Параметры поля» и изменить его с «Счетчик» на «Сумма», хорошо? Итак, есть все наши годы и сколько дождей было в каждом году. И мы ищем годы с наименьшим количеством осадков.Прямо сейчас следите за … Видите ли, они выбрали Count of Rain, потому что в данных несколько дней или у Кена пустая ячейка, пустая ячейка вместо 0. И да, мы должны пройти и исправить это, но это данные Кена. Это данные за 20 лет. Я не собираюсь проходить даже через «Найти и заменить». Хорошо, я просто … По какой-то причине я буду уважать то, что у Кена есть причина иметь их, например, я позволю им оставаться пустыми. И здесь, в разделе «Счетчик дождя», я собираюсь выбрать ячейку в столбце «Счетчик дождя», перейти в «Параметры поля» и изменить его с «Счетчик» на «Сумма», хорошо? Итак, есть все наши годы и сколько дождей было в каждом году. И мы ищем годы с наименьшим количеством осадков.s, потому что в данных несколько дней или у Кена пустая ячейка, пустая ячейка вместо 0. И да, мы должны пройти и исправить это, но это данные Кена. Это данные за 20 лет. Я не собираюсь проходить даже через «Найти и заменить». Хорошо, я просто … По какой-то причине я буду уважать то, что у Кена есть причина иметь их, например, я позволю им оставаться пустыми. И здесь, в разделе «Счетчик дождя», я собираюсь выбрать ячейку в столбце «Счетчик дождя», перейти в «Настройки поля» и изменить его с «Счетчик» на «Сумма», хорошо? Итак, есть все наши годы и сколько дождей было в каждом году. И мы ищем годы с наименьшим количеством осадков.s потому что в данных несколько дней или у Кена пустая ячейка, пустая ячейка вместо 0. И да, мы должны пройти и исправить это, но это данные Кена. Это данные за 20 лет. Я не собираюсь проходить даже через «Найти и заменить». Хорошо, я просто … По какой-то причине я буду уважать то, что у Кена есть причина иметь их, например, я позволю им оставаться пустыми. И здесь, в разделе «Счетчик дождя», я собираюсь выбрать ячейку в столбце «Счетчик дождя», перейти в «Параметры поля» и изменить его с «Счетчик» на «Сумма», хорошо? Итак, есть все наши годы и сколько дождей было в каждом году. И мы ищем годы с наименьшим количеством осадков.s данные. Это данные за 20 лет. Я не собираюсь проходить даже через «Найти и заменить». Хорошо, я просто … По какой-то причине я буду уважать, что у Кена есть причина иметь их, например, я позволю им оставаться пустыми. И здесь, в разделе «Счетчик дождя», я собираюсь выбрать ячейку в столбце «Счетчик дождя», перейти в «Настройки поля» и изменить его с «Счетчик» на «Сумма», хорошо? Итак, есть все наши годы и сколько дождей было в каждом году. И мы ищем годы с наименьшим количеством осадков.s данные. Это данные за 20 лет. Я не собираюсь проходить даже через «Найти и заменить». Хорошо, я просто … По какой-то причине я буду уважать, что у Кена есть причина иметь их, например, я позволю им оставаться пустыми. И здесь, в разделе «Счетчик дождя», я собираюсь выбрать ячейку в столбце «Счетчик дождя», перейти в «Настройки поля» и изменить его с «Счетчик» на «Сумма», хорошо? Итак, есть все наши годы и сколько дождей было в каждом году. И мы ищем годы с наименьшим количеством осадков.Я собираюсь выбрать ячейку в столбце «Количество дождя», перейти в «Настройки поля» и изменить его с «Количество» на «Сумма», хорошо? Итак, есть все наши годы и сколько дождей было в каждом году. И мы ищем годы с наименьшим количеством осадков.Я собираюсь выбрать ячейку в столбце «Количество дождя», перейти в «Настройки поля» и изменить его с «Количество» на «Сумма», хорошо? Итак, есть все наши годы и сколько дождей было в каждом году. И мы ищем годы с наименьшим количеством осадков.
Хорошо, вот что меня беспокоит - это слово «Ярлыки строк». Это начало происходить с нами в Excel 2007, понятно? И я - 10 лет спустя я все еще презираю это. Я перехожу на вкладку «Дизайн», открываю «Макет отчета» и говорю «Показать в табличной форме» и все, что происходит. В данном конкретном случае это действительно заголовок года, верно? И я предпочитаю настоящий заголовок. Прямо сейчас мы хотим видеть только вершину или, в данном случае, годы с наименьшим количеством осадков. Итак, я собираюсь отсортировать эти данные по возрастанию. Теперь есть два способа сделать это. Вы можете открыть это раскрывающееся меню, перейти к разделу «Дополнительные параметры сортировки», выбрать «На отправку на основе суммы дождя», но также можно просто перейти сюда, в «Данные», от А до Я, чтобы отсортировать вещи от наименьшего к наибольшему. Но я не хочу видеть только первые 5 лет, поэтому годы с наименьшим количеством осадковЯ перехожу к заголовку «Год», открываю это небольшое раскрывающееся меню и выбираю «Фильтры значений». И я ищу Bottom 5. Ну, для Bottom 5 нет фильтра. Ага, но этот фильтр для первой десятки невероятно мощный. Хорошо, это не обязательно должно быть сверху. Он может быть верхним или нижним. Это не обязательно должно быть 10; это может быть 5. Итак, спросите 5 лучших элементов по сумме дождя, нажмите OK. А вот и наш отчет.
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
