Phiên bản Excel
Kỹ thuật này sử dụng các tính năng mới trong Excel trong Office 365. Hãy chắc chắn rằng phiên bản Excel của bạn có các chức năng mới, chẳng hạn như SORT và UNIQUE.
Để kiểm tra các chức năng mới:
Bảng Excel được đặt tên
Trong ví dụ này, dữ liệu nguồn mà chúng ta muốn sử dụng cho bảng xoay vòng là Sales_Data- tải tại đây
Có 100 bản ghi trong bảng và hiện tại nó được lọc để hiển thị 2 tên người bán hàng [Smith và Riaz] và tất cả các danh mục ngoại trừ Cookies.
LƯU Ý: Nếu bảng dữ liệu nguồn của bạn có nhiều các bản ghi, nó có thể làm chậm sổ làm việc của bạn
Tạo bảng Pivot
Nếu tạo bảng Pivot từ bảng Sales_Data, nó sẽ bao gồm tất cả 100 bản ghi, không chỉ các bản ghi hàng hiển thị.
Tất cả các tên người bán hàng được liệt kê và tất cả các loại thông tin
Bảng Pivot từ danh sách đã lọc
Để tạo bảng Pivot chỉ từ các hàng hiển thị được lọc ta sẽ thực hiện các bước sau:
Đánh dấu các hàng đang hiển thị
Bước đầu tiên là thêm một cột mới trong bảng Sales_Data, để đánh dấu các hàng hiển thị sử dụng hàm SubtotalTải tại đây.
Phương thức hoạt động
Tất cả các hàng hiển thị 1 là kết quả công thức trong cột J.
Nhưng, nếu bạn sử dụng các công thức để thực hiện COUNT và SUM cho cột J, bạn sẽ thấy các số đó khác nhau.
Có 100 số trong cột J, nhưng chỉ có 24 trong số đó là 1.
Hàm SUBTOTAL bỏ qua các giá trị bị ẩn bởi bộ lọc, do đó đối với các hàng không hiển thị, kết quả trong cột J bằng không.Ví dụ:
Đặt PivotTable trong 1 Sheet mới
Chúng ta sẽ tạo một bảng tính mới và xây dựng một nguồn mới cho bảng Pivot ở đó.
Để tạo bảng Pivot dữ liệu cần các tiêu đề, vì vậy chúng ta sẽ sử dụng các bảng từ bảng Sales_Data.
Tiếp theo, hãy làm theo các bước sau :
Lấy dữ liệu đã lọc
Tiếp theo, chúng ta sẽ sử dụng hàm Excel mới Filter để kéo các hàng hiển thị từ bảng Sales_Data
=FILTER[
Đối số đầu tiên làmảng [ array ] những gì chúng ta muốn lọc.
Nhập tên của bảng mà chúng ta muốn lọc Sales_Data, sau đó nhập dấu phẩy
= FILTER [Sales_Data,
Đối số tiếp theo làbao gồm [ include ] quy tắc của chúng ta sẽ ghi lại kết quả.Chúng ta muốn các hàng sẽ hiển thị trong cột Vis.
= FILTER[Sales_Data,Sales_Data [Vis]
= FILTER [Sales_Data, Sales_Data [Vis] = 1
Mảng động của dữ liệu được lọc
Mặc dù bạn chỉ nhập công thức vào ô A2, kết quả công thức sẽ nằm trong một mảng động.
Có một viền màu xanh mỏng xung quanh mảng động
Các ô trong mảng động
Nếu bạn bấm vào bất kỳ ô nào trong mảng động, trừ ô A2,
Nếu bạn chọn ô A2, nơi nhập công thức, bạn có thể chỉnh sửa công thức, như thường lệ
Tạo PivotTable từ vùng được đặt tên động
Bây giờ chúng ta có các tiêu đề và dữ liệu được lọc, chúng ta sẽ tạo một
phạm vicó tênđộng để sử dụng làm dữ liệu nguồn của bảng nguồn.
Để tham chiếu một mảng động trong công thức, hãy sử dụng ô bắt đầu của mảng, theo sau là dấu #
Trong ví dụ này, đây là tham chiếu mảng động:
DataFiltered!$A$2#
Chúng ta sẽ sử dụng tham chiếu đó trong công thức OFFSET[Tải tại đây], để tạo phạm vi động:
= OFFSET [DataFiltered! $ A $ 2 #, 1,0, ROWS [DataFiltered! $ A $ 2 #] + 1, COLUMNS [DataFiltered! $ A $ 2 #]]
Công thức của OFFSET hoạt động như thế nào
Hàm OFFSET cho kết quả trong một phạm vi giới hạn:
Nếu các bộ lọc trên bảng Sales_Data bị thay đổi, số lượng hàng trong mảng động sẽ thay đổi.
Phạm vi được đặt tên động của chúng ta PivotUse sẽ tự động điều chỉnh theo những thay đổi đó
Tạo bảng Pivot
Bước cuối cùng là tạo một bảng nguồn, dựa trên phạm vi được đặt tên động.
Thêm trường [ Fields ] vào bảng Pivot
Tiếp theo, sử dụng Danh sách trường của bảng Pivot để thêm các trường mà bạn muốn hiển thị trong bảng Pivot.
Nếu bạn bao gồm các trường Rep và Category , bạn sẽ thấy rằng chúng chỉ bao gồm các mục từ các hàng hiển thị trong bảng Sales_Data.
Thay đổi Bộ lọc Sales_Data
Nếu bạn thay đổi các bộ lọc trong bảng Sales_Data, hãy đảm bảo làm mới bảng Pivot sau khi bạn thực hiện xong các thay đổi.
Ví dụ: lọc dữ liệu để nó chỉ hiển thị doanh số ở khu vực phía Đông.
Mảng động tự động cập nhật và hiện chỉ có 14 hàng trên trang tính đó.
Tuy nhiên, các bảng Pivot không tự động làm mới, vì vậy bạn có thể thực hiện bước đó theo cách thủ công.
Nhấp chuột phải vào bảng trụ và bấm Refresh
Sau khi làm mới, chỉ có các hồ sơ khu vực phía Đông được hiển thị.Riaz đã không có bất kỳ doanh số nào trong khu vực đó, vì vậy Smith là người duy nhất trong dữ liệu được lọc và trong bảng Pivot được làm mới.
Lấy tập tin mẫu
Để xem bảng Pivot từ danh sách được lọc Bấm vào đây.
Trong phần Tải xuống, hãylấy tệp mẫu Dữ liệu NguồnđượcLọc.Tệp nén có định dạng xlsx và không chứa bất kỳ macro nào.
Hy vọng bài viết này đã giúp các bạn thực hiện tốt các thao tác sử dụng PivotTable trên Excel. Ngoài ra, bạn có thể truy cập ngay //gitiho.com/ để tìm hiểu các khóa học thú vị về tin học văn phòng: Word, Excel, Power Point.
Cảm ơn các bạn đã theo dõi. Chúc các bạn thành công!