Hướng dẫn cách tạo bảng Pivot từ các hàng được hiển thị

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

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:

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ị

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 .

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ộ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:

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.

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!