Bắt đầu với nhiều năm dữ liệu.
Giả sử bạn có hồ sơ chi tiết trị giá hai năm. Mỗi bản ghi có một ngày hàng ngày. Khi bạn tạo bảng tổng hợp từ báo cáo này, bạn sẽ có hàng trăm hàng ngày hàng ngày trong bảng tổng hợp. Đây không phải là nhiều tóm tắt.
Chọn một trong các ô ngày đó trong bảng tổng hợp. Từ tab Phân tích trong ruy-băng, hãy chọn Trường Nhóm.
Bởi vì bạn đang ở trên một trường ngày tháng, bạn nhận được phiên bản này của hộp thoại Nhóm. Trong đó, bỏ chọn Tháng và chọn Năm.
Ngày hàng ngày được tổng hợp lên đến hàng năm. Di chuyển trường Ngày từ ROWS sang COLUMNS.
Kết quả là gần như hoàn hảo. Nhưng thay vì tổng số lớn trong cột D, bạn có thể muốn một phương sai phần trăm.
Để loại bỏ cột Tổng Tổng, trên tab Thiết kế, chọn Tổng Tổng, Bật Chỉ cho Cột. (Tôi đồng ý, đây là một trong những từ khó hiểu hơn trong Excel.)
Để tạo cột phương sai, bạn cần viết một công thức bên ngoài bảng tổng hợp trỏ bên trong bảng tổng hợp. Không chạm vào chuột hoặc các phím mũi tên trong khi xây dựng công thức, nếu không hàm GETPIVOTDATA khó chịu sẽ xuất hiện. Thay vào đó, chỉ cần nhập =C5/B5-1và nhấn Enter.
Cảm ơn Александр Воробьев đã gợi ý mẹo này.
Xem video
- Bắt đầu với nhiều năm dữ liệu
- Chèn, bảng tổng hợp
- Kéo trường ngày vào vùng hàng
- Excel 2016: Nhấn Ctrl + Z để hủy nhóm ngày
- Kéo doanh thu đến khu vực giá trị
- Chọn bất kỳ ngày nào trong khu vực hàng
- Sử dụng trường nhóm
- Chọn Tháng & Năm
- Cách thêm tổng phụ vào trường năm sau khi nhóm bảng tổng hợp
- Sử dụng biểu mẫu Bảng trong bảng tổng hợp để cung cấp cho mỗi trường hàng cột riêng của nó
- Lặp lại tất cả các nhãn hàng để điền vào chỗ trống trong bảng tổng hợp
- Kéo năm vào vùng cột
- Nhấp chuột phải vào tiêu đề cột Grand Total và xóa
- Cách tránh hàm GetPivotData khi công thức trỏ đến bảng tổng hợp
- Để tạo một phương sai, hãy nhập công thức mà không có chuột hoặc mũi tên
- Cảm ơn Александр Воробьев đã đề xuất mẹo này
Bản ghi video
Học Excel từ podcast MrExcel, tập 1998. Bảng Pivot Năm qua Năm.
Tôi sẽ podcasting toàn bộ cuốn sách này, hãy nhấp vào chữ "i" ở góc trên cùng bên phải, để xem danh sách phát MrExcel Excel.
Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Hôm nay chủ đề đầu tiên trong cuốn sách về Pivot Tables và cách tạo báo cáo hàng năm. Vì vậy, chúng tôi đang bắt đầu với tập dữ liệu, kéo dài hai năm, bạn thấy đấy, chúng tôi sẽ đi từ năm 2018 đến năm 2019. Tôi sẽ tạo Bảng Pivot, Chèn, PivotTable, OK, không sao cả, hãy bắt đầu bảng tính mới.
Trên trang tính mới, chúng ta sẽ lấy trường Ngày và kéo nó vào vùng Hàng. Và sau đó, bạn thấy đấy, trong Excel 2016, nó tự động nhóm mọi thứ lại. Bây giờ tôi biết hầu hết mọi người vẫn đang sử dụng Excel 2013, có thể là Excel 2010, vì vậy tôi sẽ nhấn Ctrl + Z để hoàn tác. Đó là trải nghiệm bạn sẽ nhận được trong năm 2010 và 2013, chúng ta sẽ lấy trường Doanh thu và kéo nó vào vùng Giá trị. Vì vậy, chúng tôi bắt đầu với một báo cáo xấu xí, sẽ có hàng trăm hàng trăm hàng, bởi vì mỗi ngày chúng tôi đều có doanh số bán hàng. Nhưng tôi sẽ chuyển đến trường ngày đầu tiên, trường ngày đầu tiên, đi tới Công cụ PivotTable, Phân tích, vào năm 2013 hoặc 2016 và 2010, nó được gọi là Tùy chọn và chọn Trường Nhóm. Đảm bảo rằng bạn chọn một ngày trước khi chọn Trường Nhóm và tổng hợp mọi thứ lên đến Tháng và Năm. Hoặc các tháng và năm,hoặc Tháng và Quý và Năm, bấm OK. Và những gì chúng tôi nhận được, là một trường ảo mới có tên là Years ở đây trong khu vực Hàng.
Bây giờ một số khó chịu. Thật khó chịu khi họ không cho chúng tôi tổng số Năm, thật dễ dàng để khắc phục điều đó, mặc dù trong trường hợp của tôi, tôi không cần phải sửa điều đó. Chúng tôi nhấp vào Cài đặt trường và Tự động, bởi vì tôi không có ý định rời khỏi Năm ở đây. Tất nhiên mỗi khi tôi tạo PivotTable, tôi đi tới Thiết kế và nói Hiển thị trong Biểu mẫu dạng bảng để đưa mọi thứ vào cột riêng của nó. Và sau đó Lặp lại Tất cả các Nhãn Mục để điền vào các ô trống đó dọc theo phía bên tay trái. Một lần nữa, điều đó không cần thiết ở đây, bởi vì tôi không có ý định để lại Số năm ở đâu, tôi sẽ lấy Số năm và kéo nó qua Cột, và đó là bản báo cáo mà tôi đang quay.
Được rồi, chúng ta có Số tháng ở phía bên trái, 2018 đến 2019. Trong cột D, tôi thực sự muốn gì, tôi không muốn Tổng số, tôi sẽ nhấp chuột phải và nói Xóa Tổng số Tổng cộng và bây giờ chúng ta đang ở ngoài PivotTable. Vì vậy,% Change, rất hấp dẫn để xây dựng công thức này bằng cách sử dụng chuột hoặc các phím mũi tên, nhưng bạn không thể, bởi vì bạn sẽ nhận được hàm… get Pivot Data, hàm này không dễ dàng sao chép. Vì vậy = C5 / B5-1, chúng tôi sẽ định dạng nó dưới dạng phần trăm và nhấp đúp để sao chép nó xuống, phải. Báo cáo tuyệt đẹp cho thấy năm ngoái chúng tôi đã ở đâu, năm nay chúng tôi ở đâu và sau đó là tỷ lệ phần trăm thay đổi từ năm này sang năm khác.
Chà, mẹo này và 40 mẹo khác, đều có trong cuốn sách này. Hãy tiếp tục, nhấp vào chữ “i” ở góc trên cùng bên phải của cuốn sách. Được rồi, vì vậy bạn bắt đầu với tập dữ liệu có nhiều năm, hãy thực hiện Chèn, Bảng tổng hợp, kéo trường ngày vào vùng hàng. Và trong Excel 2016, nhấn Ctrl + Z để hoàn tác cập nhật nhóm tự động của chúng. Tại sao chúng ta không để nó ở đây? Vâng, nó thực sự phụ thuộc. Nếu bạn có dữ liệu từ ngày 2 tháng 1 đến ngày 30 tháng 12, thì họ sẽ không nhóm dữ liệu đó một cách chính xác. Bạn phải dành cả năm để có được trường năm. Có thể là nó phù hợp với bạn, tôi hơi khó kiểm soát, tôi thà chỉ có quyền kiểm soát, đó là lý do tại sao tôi hoàn tác. Có: kéo doanh thu đến vùng giá trị, chọn bất kỳ ô nào, bất kỳ ngày nào trong vùng hàng và sau đó Trường nhóm, chọn Tháng & Năm,kéo năm qua vùng cột, bấm chuột phải vào Tổng số lớn để loại bỏ và sau đó xây dựng phương sai, nhập công thức, không sử dụng chuột hoặc phím mũi tên.
Tôi không biết phải phát âm nó như thế nào, nhưng cảm ơn độc giả này (Александр Воробьев) đã gợi ý mẹo này và 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 xuống tệp mẫu tại đây: Podcast1998.xlsx
