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:
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
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
Để 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:
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 Subtotal Tải tại đây .
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ụ:
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 :
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ặ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
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ệ
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 vi có 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 #))
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 đó
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.
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.
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.
Để xem bảng Pivot từ danh sách được lọc Bấm vào đây.
Trong phần Tải xuống, hãy lấy tệp mẫu Dữ liệu Nguồn được Lọ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 https://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!
Top khóa học Excel được học viên đánh giá cao