Khi bạn tạo bảng Pivot trong Excel, sẽ không có vấn đề gì nếu có các bộ lọc được áp dụng trong bảng dữ liệu nguồn. Bảng Pivot bao gồm tất cả các dữ liệu, cho dù nó ẩn hay không. Nhưng nếu bạn muốn tạo một bảng Pivot chỉ từ các hàng hiển thị danh sách được lọc, bạn có thể làm cách này.
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:
Chọn một ô trống, sau đó nhập: =SO
Nếu mẹo màn hình hiển thị SORT và SORTBY, bạn có các chức năng mới.
Bảng Excel được đặt tên
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:
Thêm một cột mới trong bảng Sales_Data.
Trong cột đó sử dụng công thức để đánh dấu các hàng hiển thị
Trên một trang tính khác, hãy lấy các tiêu đề dữ liệu nguồn
Sử dụng chức năng mới để kéo các hàng có thể nhìn thấy từ bảng Sales_Data
Tạo một phạm vi giới hạn và đặt tên động, dựa trên dữ liệu đã kéo và các tiêu đề
Tạo bảng Pivot dựa trên phạm vi được đặt tên động
Đánh dấu các hàng đang hiển thị
Trong ô J3, nhập tiêu đề – Vis
Trong ô J4, bắt đầu công thức SUBTOTAL này, với 2 (Đếm) là số hàm:
= SUBTOTAL (2,
Sau đó, nhấp vào ô I4, có số lượng Orders và nhập dữ liêu .
= SUBTOTAL (2, [@ Orders])
Nhấn Enter, để thêm công thức cho tất cả các ô trong cột Vis (ngay cả các hàng bị ẩn)
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ụ:
hàng 18 nó được hiển thị, vì vậy kết quả là 1
hàng 29 bị ẩn bởi bộ lọc, vì vậy nó trả về số 0
Đặ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 ở đó.
Chèn một bảng tính mới
Đặt tên cho trang tính – DataFiltered
Để 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.
Trong ô A1 trên trang tính mới, nhập một dấu bằng
Chuyển đến trang FoodSales và nhấp vào ô A3, có ô tiêu đề đầu tiên trong bảng Sales_Data.
Nhấn Enter, để hoàn thành công thức
Tiếp theo, hãy làm theo các bước sau :
Chọn ô A1, có liên kết đến tiêu đề đầu tiên
Chỉ vào ở góc dưới bên phải của ô A1
Kéo qua cột J, để lấy phần còn lại của tiêu đề bảng Sales_Data
(Tùy chọn) Định dạng các ô tiêu đề bằng phông chữ đậm
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
Chọn ô A2 và bắt đầu công thức:
=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.
Chuyển đến trang FoodSales và nhấp vào đầu ô tiêu đề cột Vis
Tên bảng và tên cột được thêm vào công thức
= FILTER(Sales_Data, Sales_Data [Vis]
Nhập quy tắc của chúng ta cho cột đó: = 1
= FILTER (Sales_Data, Sales_Data [Vis] = 1
Nhập dấu ngoặc để kết thúc, sau đó nhấn Enter để hoàn thành công thức
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,
bạn có thể thấy công thức ở phông chữ màu xám nhạt trong thanh công thức
bạn không thể thay đổi công thức
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ộtphạ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#
Trên thanh công cụ Excel, bấm vào tab Formulas, sau đó bấm lệnh Define Name
Đối với Tên, nhập: PivotUse
Phần Scope để Workbook
Trong phần Refers to, hãy nhập công thức OFFSET này:
= OFFSET (DataFiltered! $ A $ 2 #, – 1,0, ROWS (DataFiltered! $ A $ 2 #) + 1, COLUMNS (DataFiltered! $ A $ 2 #))
Nhấn OK, để hoàn thành tên
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:
Bắt đầu ở mảng động
Đi lên một hàng (-1)
Di chuyển trên 0 cột
Bao gồm số lượng hàng trong mảng động, cộng 1 cho hàng tiêu đề
Bao gồm số lượng cột trong mảng động
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.
Chèn một trang tính mới và đặt tên là PivotVis
Chọn bất kỳ ô nào trên trang tính mới
Trên thanh công cụ Excel, bấm vào tab Chèn
Nhấp vào lệnh Bảng Pivot
Trong hộp thoại Tạo bảng Pivot, bấm vào hộp Bảng / Phạm vi và nhấn phím F3 trên bàn phím của bạn
Trong hộp thoại Paste name, bấm vào PivotUse và bấm OK
Đối với vị trí, chọn bảng PivotVis
Nhấn OK để tạo bảng nguồn.
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ã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.
Đánh giá bài viết này