5 tháng dưới cùng - Mẹo Excel

Lượng mưa năm tháng dưới cùng là những tháng nào? Tìm hiểu cách giải quyết vấn đề này bằng cách sử dụng bảng tổng hợp.

Xem video

• Không thể làm mới các bảng tổng hợp được tạo năm 2013 vào năm 2007
• Bạn cần tạo bảng tổng hợp vào năm 2007 để cho phép nó có thể làm mới
• Mục tiêu là tìm ra năm tháng có lượng mưa ít nhất
• Tạo một bảng tổng hợp lớn với lượng mưa theo tháng
• Sắp xếp theo lượng mưa tăng dần
• Thay đổi thành dạng bảng
• Sử dụng Bộ lọc giá trị, Top 10, để nhận được 5 phía dưới!
• Xóa hàng Grand Total
• Lưu ý rằng sự ràng buộc có thể khiến báo cáo này cung cấp cho bạn 6 hàng trở lên
• Khi bạn có bảng tổng hợp đầu tiên, hãy sao chép nó vào vị trí và tạo bảng tổng hợp tiếp theo
• Khi bạn thay đổi từ trường giá trị này sang trường giá trị khác, bạn phải thực hiện lại việc sắp xếp và lọc
• Khi bạn thay đổi từ trường hàng này sang trường hàng khác, bạn phải thực hiện lại việc sắp xếp và lọc
• Mẹo bổ sung: tạo bảng tổng hợp với các hàng và cột

Bản ghi video

Học Excel từ Podcast, Tập 2063: Năm tháng hoặc năm trên cùng hoặc dưới cùng bằng cách sử dụng bảng tổng hợp.

Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Câu hỏi hôm nay do Ken gửi đến. Ken có một bảng tính tuyệt vời ở đây với năm tháng và năm tháng về lượng mưa hàng ngày, từ năm 1999. Một bộ sưu tập dữ liệu thực sự ấn tượng mà anh ấy có và Ken đã có một số công thức tuyệt vời để thử và tìm tháng có lượng mưa nhiều nhất, tại lượng mưa ít nhất. Vì vậy, bây giờ, bạn biết đấy, điều này sẽ dễ dàng hơn rất nhiều với bảng xoay.

Được rồi, Ken chưa bao giờ tạo bảng tổng hợp và để làm phức tạp thêm mọi thứ, tôi đang ở đây trong Excel 2016, Ken đang sử dụng Excel 2007. Các bảng tổng hợp mà tôi đã tạo năm 2016, anh ấy có thể nhìn thấy anh ấy nhưng anh ấy không thể làm mới chúng. Được rồi, video này là Bảng tổng hợp 101: Cách tạo bảng tổng hợp đầu tiên của bạn.

Đầu tiên, Ken có Ngày này trong Cột A, ngày tháng thực sự, chúng ta có tốt không? Thật tuyệt vời, phải không? Và sau đó tôi sử dụng- chèn một vài công thức bổ sung vào đây tại hàm = YEAR để lấy năm, = MONTH để lấy tháng, hàm = DAY. Và sau đó nối chúng lại với nhau, tôi thực sự đã sử dụng hàm = TEXT trong YYYY-MM, theo cách đó tôi đã giảm bớt năm và tháng. Đây là dữ liệu của Ken, dữ liệu mưa ở đây và sau đó tôi thêm một số công thức. Ken's có bất kỳ thứ gì nhỏ hơn 0,5 mm, không được tính là ngày mưa nên có một công thức ở đó. Và sau đó, từ Tập 735, hãy quay lại và xem lại phần đó để xem cách tôi tính toán số ngày có mưa và số ngày không có mưa. Bây giờ nó sẽ không được sử dụng cho ngày hôm nay, nó đã được sử dụng cho việc khác.

Vì vậy, chúng tôi đến đây. Và trước tiên, chúng tôi muốn chọn dữ liệu cho bảng tổng hợp của chúng tôi. Bây giờ, trong hầu hết các trường hợp, bạn chỉ có thể chọn tất cả dữ liệu để bạn có thể chỉ chọn một ô ở đây nhưng trong trường hợp này, có một Phạm vi tên xác định dữ liệu thông qua, trong trường hợp này là năm 2016. Chúng tôi đang ngồi ở đây- Tôi ' m ghi lại điều này vào đầu năm 2017. Dữ liệu của Ken chỉ đến cuối năm 2016. Vì vậy, chúng tôi sẽ chỉ chọn dữ liệu đó. Và sau đó trên tab Chèn- tab Chèn. Excel 2007, đây là lần đầu tiên bảng tổng hợp chuyển từ tab Dữ liệu trở lại tab Chèn. Vì vậy, chúng tôi chọn: Pivot Table và dữ liệu đã chọn của chúng tôi sẽ là dữ liệu mà chúng tôi xây dựng từ đó. Và, chúng tôi không muốn chuyển đến trang tính mới, chúng tôi sẽ chuyển đến trang tính hiện có và tôi sẽ đặt điều đó ngay tại đây trong Cột - hãy chuyển sang Cột N.Cuối cùng, tôi muốn dữ liệu Năm có lượng mưa thấp nhất này xuất hiện ngay tại đây nhưng tôi biết rằng khi tôi đang xây dựng bảng tổng hợp này, nó sẽ cần nhiều hàng hơn 5 hàng đó, phải không? Vì vậy, tôi đang xây dựng nó ở bên này, được rồi. Và chúng tôi bấm OK.

Được rồi, đây là những gì bạn nhận được. Đây là nơi báo cáo sẽ đi đến và đây là danh sách tất cả các trường mà chúng tôi có trong tập dữ liệu nhỏ của mình. Và sau đó, chúng tôi có, vì cái mà tôi gọi là khủng khiếp đã bỏ rơi. Hàng là các mục bạn muốn ở phía bên trái. Giá trị là thứ bạn muốn tổng hợp và sau đó Cột là thứ bạn muốn ở trên cùng. Chúng tôi có thể sử dụng điều này ở cuối. Hôm nay chúng tôi sẽ không sử dụng Bộ lọc. Vì vậy, chúng tôi chỉ đang xây dựng một bảng tổng hợp nhỏ đơn giản với tổng lượng mưa theo năm, vì vậy tôi lấy trường Năm và kéo nó xuống đây ở phía bên trái. Có một danh sách tất cả các năm của chúng ta, được chứ? Và sau đó, hãy nghĩ về nó. Để có được công thức này ở đây mà không có bảng tổng hợp, bạn sẽ làm gì? SUMIF, vâng, SUMIF. Bạn thậm chí có thể sử dụng lại SUMIF trong Excel 2007. Vì vậy,Tôi sẽ lấy trường Mưa và kéo nó đến đây. Ngay bây giờ, hãy chú ý đến - Thấy chưa, họ đã chọn Count of Rain, đó là bởi vì có vài ngày trong dữ liệu hoặc Ken có một ô trống, một ô trống thay vì 0. Và vâng, chúng ta nên xem xét và khắc phục điều đó nhưng đó là dữ liệu của Ken. Đó là dữ liệu có giá trị 20 năm. Tôi sẽ không thực hiện ngay cả khi sử dụng Tìm & Thay thế. Được rồi, tôi chỉ- Vì bất cứ lý do gì, tôi sẽ tôn trọng rằng Ken có lý do để có những thứ đó, giống như tôi sẽ cho phép họ để trống. Và ở đây, trong Count of Rain, tôi sẽ đảm bảo chọn một ô trong cột Count of Rain, chuyển đến Field Settings và thay đổi ô đó từ Count thành Sum, được chứ? Vì vậy, có tất cả các năm của chúng tôi và chúng tôi đã có bao nhiêu mưa trong mỗi năm. Và chúng tôi đang tìm những năm có lượng mưa thấp nhất.Ngay bây giờ, hãy chú ý đến - Thấy chưa, họ đã chọn Count of Rain, đó là bởi vì có vài ngày trong dữ liệu hoặc Ken có một ô trống, một ô trống thay vì 0. Và vâng, chúng ta nên xem xét và khắc phục điều đó nhưng đó là dữ liệu của Ken. Đó là dữ liệu có giá trị 20 năm. Tôi sẽ không thực hiện ngay cả khi sử dụng Tìm & Thay thế. Được rồi, tôi chỉ- Vì bất cứ lý do gì, tôi sẽ tôn trọng rằng Ken có lý do để có những thứ đó, giống như tôi sẽ cho phép họ để trống. Và ở đây, trong Count of Rain, tôi sẽ đảm bảo chọn một ô trong cột Count of Rain, chuyển đến Field Settings và thay đổi ô đó từ Count thành Sum, được chứ? Vì vậy, có tất cả các năm của chúng tôi và chúng tôi đã có bao nhiêu mưa trong mỗi năm. Và chúng tôi đang tìm những năm có lượng mưa thấp nhất.Ngay bây giờ, hãy chú ý đến - Thấy chưa, họ đã chọn Count of Rain, đó là bởi vì có vài ngày trong dữ liệu hoặc Ken có một ô trống, một ô trống thay vì 0. Và vâng, chúng ta nên xem xét và khắc phục điều đó nhưng đó là dữ liệu của Ken. Đó là dữ liệu có giá trị 20 năm. Tôi sẽ không thực hiện ngay cả khi sử dụng Tìm & Thay thế. Được rồi, tôi chỉ- Vì bất cứ lý do gì, tôi sẽ tôn trọng rằng Ken có lý do để có những thứ đó, giống như tôi sẽ cho phép họ để trống. Và ở đây, trong Count of Rain, tôi sẽ đảm bảo chọn một ô trong cột Count of Rain, chuyển đến Field Settings và thay đổi ô đó từ Count thành Sum, được chứ? Vì vậy, có tất cả các năm của chúng tôi và chúng tôi đã có bao nhiêu mưa trong mỗi năm. Và chúng tôi đang tìm những năm có lượng mưa thấp nhất.s bởi vì có một vài ngày trong dữ liệu hoặc Ken có một ô trống, một ô trống thay vì 0. Và vâng, chúng ta nên xem xét và sửa lỗi đó nhưng đó là dữ liệu của Ken. Đó là dữ liệu có giá trị 20 năm. Tôi sẽ không thực hiện ngay cả khi sử dụng Tìm & Thay thế. Được rồi, tôi chỉ- Vì bất cứ lý do gì, tôi sẽ tôn trọng rằng Ken có lý do để có những thứ đó, giống như tôi sẽ để họ trống. Và ở đây, trong Count of Rain, tôi sẽ đảm bảo chọn một ô trong cột Count of Rain, chuyển đến Field Settings và thay đổi ô đó từ Count thành Sum, được chứ? Vì vậy, có tất cả các năm của chúng tôi và chúng tôi đã có bao nhiêu mưa trong mỗi năm. Và chúng tôi đang tìm những năm có lượng mưa thấp nhất.s bởi vì có một vài ngày trong dữ liệu hoặc Ken có một ô trống, một ô trống thay vì 0. Và vâng, chúng ta nên xem xét và sửa lỗi đó nhưng đó là dữ liệu của Ken. Đó là dữ liệu có giá trị 20 năm. Tôi sẽ không thực hiện ngay cả khi sử dụng Tìm & Thay thế. Được rồi, tôi chỉ- Vì bất cứ lý do gì, tôi sẽ tôn trọng rằng Ken có lý do để có những thứ đó, giống như tôi sẽ cho phép họ để trống. Và ở đây, trong Count of Rain, tôi sẽ đảm bảo chọn một ô trong cột Count of Rain, chuyển đến Field Settings và thay đổi ô đó từ Count thành Sum, được chứ? Vì vậy, có tất cả các năm của chúng tôi và chúng tôi đã có bao nhiêu mưa trong mỗi năm. Và chúng tôi đang tìm những năm có lượng mưa thấp nhất.s dữ liệu. Đó là dữ liệu có giá trị 20 năm. Tôi sẽ không thực hiện ngay cả khi sử dụng Tìm & Thay thế. Được rồi, tôi chỉ- Vì bất cứ lý do gì, tôi sẽ tôn trọng rằng Ken có lý do để có những thứ đó, giống như tôi sẽ để họ trống. Và ở đây, trong Count of Rain, tôi sẽ đảm bảo chọn một ô trong cột Count of Rain, chuyển đến Field Settings và thay đổi ô đó từ Count thành Sum, được chứ? Vì vậy, có tất cả các năm của chúng tôi và chúng tôi đã có bao nhiêu mưa trong mỗi năm. Và chúng tôi đang tìm những năm có lượng mưa thấp nhất.dữ liệu s. Đó là dữ liệu có giá trị 20 năm. Tôi sẽ không thực hiện ngay cả khi sử dụng Tìm & Thay thế. Được rồi, tôi chỉ- Vì bất cứ lý do gì, tôi sẽ tôn trọng rằng Ken có lý do để có những thứ đó, giống như tôi sẽ để họ trống. Và ở đây, trong Count of Rain, tôi sẽ đảm bảo chọn một ô trong cột Count of Rain, chuyển đến Field Settings và thay đổi ô đó từ Count thành Sum, được chứ? Vì vậy, có tất cả các năm của chúng tôi và chúng tôi đã có bao nhiêu mưa trong mỗi năm. Và chúng tôi đang tìm những năm có lượng mưa thấp nhất.Tôi sẽ đảm bảo chọn một ô trong cột Số lượng Mưa, đi tới Cài đặt Trường và thay đổi ô đó từ Đếm thành Tổng, được chứ? Vì vậy, có tất cả các năm của chúng tôi và chúng tôi đã có bao nhiêu mưa trong mỗi năm. Và chúng tôi đang tìm những năm có lượng mưa thấp nhất.Tôi sẽ đảm bảo chọn một ô trong cột Count of Rain, chuyển đến Field Settings và thay đổi ô đó từ Count thành Sum, được chứ? Vì vậy, có tất cả các năm của chúng tôi và chúng tôi đã có bao nhiêu mưa trong mỗi năm. Và chúng tôi đang tìm những năm có lượng mưa thấp nhất.

Được rồi, một điều khiến tôi khó chịu là từ này ở đây Row Labels. Điều đó bắt đầu xảy ra với chúng tôi trong Excel 2007, được chứ? Và tôi - 10 năm sau tôi vẫn coi thường điều đó. Tôi đi đến Tab Thiết kế, mở Bố cục Báo cáo và nói Hiển thị trong Biểu mẫu Bảng, và tất cả những điều đó. Trong trường hợp cụ thể này là có được một tiêu đề thực sự ở đó của Năm, phải không? Và tôi thích tiêu đề thực hơn. Ngay bây giờ, chúng tôi chỉ muốn xem phần trên cùng hoặc trong trường hợp này là những năm có lượng mưa thấp nhất. Vì vậy, tôi sẽ sắp xếp dữ liệu này tăng dần. Bây giờ có hai cách để làm điều này. Bạn có thể mở menu thả xuống này, đi tới Tùy chọn sắp xếp khác, chọn Gửi dựa trên tổng lượng mưa, nhưng cũng có thể vào đây vào Dữ liệu, A đến Z để sắp xếp mọi thứ từ thấp nhất đến cao nhất. Nhưng tôi không muốn chỉ xem 5 năm hàng đầu, vì vậy những năm có lượng mưa thấp nhất,Tôi đến đây với tiêu đề Năm, mở menu thả xuống nhỏ này và chọn Bộ lọc giá trị. Và tôi đang tìm Bottom 5. Chà, không có bộ lọc nào cho Bottom 5. Ahh, nhưng bộ lọc cho top 10 này cực kỳ mạnh mẽ. Được rồi, nó không cần phải đứng đầu. Nó có thể là trên cùng hoặc dưới cùng. Nó không nhất thiết phải là 10; nó có thể là 5. Vì vậy, yêu cầu 5 Mục hàng đầu dựa trên tổng lượng mưa, hãy nhấp vào OK. Và có báo cáo của chúng tôi.

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.

À, này, tôi muốn cảm ơn Ken vì đã gửi câu hỏi đó. Tôi muốn cảm ơn bạn đã ghé qua. Chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ.

Tải tập tin

Tải tệp mẫu tại đây: Podcast2063.xlsm