Bảng Excel Pivot - Hướng dẫn Nhanh

PivotTable là một công cụ cực kỳ mạnh mẽ mà bạn có thể sử dụng để chia nhỏ dữ liệu. Bạn có thể theo dõi và phân tích hàng trăm nghìn điểm dữ liệu bằng một bảng nhỏ gọn có thể thay đổi động để cho phép bạn tìm các góc nhìn khác nhau của dữ liệu. Nó là một công cụ đơn giản để sử dụng, nhưng mạnh mẽ.

Các tính năng chính của PivotTable như sau:

Tạo PivotTable cực kỳ đơn giản và nhanh chóng
Cho phép xáo trộn dữ liệu ngay lập tức bằng cách kéo các trường, sắp xếp và lọc đơn giản và các phép tính khác nhau trên dữ liệu.
Tìm được cách trình bày phù hợp cho dữ liệu của bạn khi bạn hiểu rõ hơn về nó.
Khả năng tạo báo cáo nhanh chóng.
Tạo nhiều báo cáo từ cùng một PivotTable chỉ trong vài giây.
Cung cấp các báo cáo tương tác để đồng bộ hóa với khán giả.

Trong hướng dẫn này, bạn sẽ hiểu chi tiết các tính năng PivotTable này cùng với các ví dụ. Vào thời điểm hoàn thành hướng dẫn này, bạn sẽ có đủ kiến thức về các tính năng của PivotTable có thể giúp bạn bắt đầu khám phá, phân tích và báo cáo dữ liệu dựa trên các yêu cầu.

Tạo PivotTable

Bạn có thể tạo PivotTable từ một dải dữ liệu hoặc bảng Excel. Bạn có thể bắt đầu với một PivotTable trống để điền thông tin chi tiết, nếu bạn biết mình đang tìm gì. Bạn cũng có thể sử dụng PivotTable được Đề xuất của Excel có thể cung cấp cho bạn các bố cục PivotTable phù hợp nhất để tóm tắt dữ liệu của bạn.

Bạn sẽ học cách tạo PivotTable từ một phạm vi dữ liệu hoặc bảng Excel trong Chương - Tạo PivotTable từ một Bảng hoặc Phạm vi.

Excel cung cấp cho bạn một cách mạnh mẽ hơn để tạo PivotTable từ nhiều bảng, các nguồn dữ liệu khác nhau và các nguồn dữ liệu bên ngoài. Nó được đặt tên là PowerPivot hoạt động trên cơ sở dữ liệu của nó được gọi là Mô hình Dữ liệu. Bạn sẽ học các công cụ điện Excel này trong các hướng dẫn khác trong Thư viện Hướng dẫn này.

Trước tiên, bạn cần biết về PivotTable bình thường như được giải thích trong hướng dẫn này, trước khi bạn tham gia vào các công cụ điện.

Bố cục PivotTable - Trường và Vùng

Bố cục PivotTable chỉ đơn giản phụ thuộc vào trường bạn đã chọn cho báo cáo và cách bạn sắp xếp chúng trong Vùng. Việc lựa chọn và sắp xếp có thể được thực hiện bằng cách kéo các trường. Khi bạn kéo các trường, bố cục PivotTable sẽ tiếp tục thay đổi và điều đó xảy ra trong vài giây.

Bạn sẽ tìm hiểu về Trường và Vùng PivotTable trong Chương - Trường PivotTable và Vùng PivotTable.

Khám phá dữ liệu với PivotTable

Mục tiêu chính của việc sử dụng PivotTable thông thường là khám phá dữ liệu để trích xuất thông tin quan trọng và bắt buộc. Bạn có một số tùy chọn để thực hiện việc này, bao gồm Sắp xếp, Lọc, Lồng, Thu gọn và Mở rộng, Nhóm và Bỏ nhóm, v.v.

Bạn sẽ có cái nhìn tổng quan về các tùy chọn này trong Chương - Khám phá dữ liệu với PivotTable.

Giá trị tóm tắt

Khi bạn đối chiếu dữ liệu theo yêu cầu của bạn bằng các kỹ thuật thăm dò khác nhau, bước tiếp theo bạn muốn thực hiện là tóm tắt dữ liệu. Excel cung cấp cho bạn nhiều kiểu tính toán khác nhau mà bạn có thể áp dụng dựa trên sự phù hợp và yêu cầu. Bạn cũng có thể chuyển đổi các kiểu tính toán khác nhau và xem kết quả chỉ trong vài giây.

Bạn sẽ học cách áp dụng các kiểu tính toán trên PivotTable trong Chương - Tổng hợp các giá trị theo các kiểu tính toán khác nhau.

Cập nhật PivotTable

Khi bạn đã khám phá dữ liệu và tóm tắt nó, bạn không cần lặp lại bài tập nếu và khi dữ liệu nguồn được cập nhật. Bạn có thể làm mới PivotTable để nó phản ánh những thay đổi trong dữ liệu nguồn.

Bạn sẽ tìm hiểu các cách làm mới dữ liệu khác nhau trong Chương - Cập nhật PivotTable.

Báo cáo PivotTable

Sau khi khám phá và tóm tắt dữ liệu bằng PivotTable, bạn sẽ trình bày dữ liệu đó dưới dạng báo cáo. Báo cáo PivotTable có tính chất tương tác, với đặc điểm là ngay cả một người không quen thuộc với Excel cũng có thể sử dụng chúng một cách trực quan. Do bản chất năng động vốn có của chúng, chúng sẽ cho phép bạn nhanh chóng thay đổi góc nhìn của báo cáo để hiển thị mức độ chi tiết cần thiết hoặc tập trung vào các mục cụ thể mà khán giả bày tỏ sự quan tâm.

Hơn nữa, bạn có thể cấu trúc báo cáo PivotTable để trình bày độc lập hoặc như một phần không thể thiếu của báo cáo rộng tùy trường hợp. Bạn sẽ tìm hiểu một số cách báo cáo với PivotTable trong Chương - Báo cáo PivotTable.

Bạn có thể tạo PivotTable từ một dải dữ liệu hoặc từ bảng Excel. Trong cả hai trường hợp, hàng đầu tiên của dữ liệu phải chứa tiêu đề cho các cột.

Nếu bạn chắc chắn về các trường sẽ được bao gồm trong PivotTable và bố cục bạn muốn có, bạn có thể bắt đầu với PivotTable trống và tạo PivotTable.

Trong trường hợp bạn không chắc bố cục PivotTable nào phù hợp nhất với dữ liệu của mình, bạn có thể sử dụng lệnh PivotTables được đề xuất của Excel để xem PivotTable được tùy chỉnh theo dữ liệu của bạn và chọn một cái bạn thích.

Tạo PivotTable từ Dải dữ liệu

Hãy xem xét phạm vi dữ liệu sau chứa dữ liệu bán hàng cho từng Nhân viên bán hàng, ở từng Khu vực và trong các tháng từ tháng 1, tháng 2 và tháng 3 -

Để tạo PivotTable từ dải dữ liệu này, hãy thực hiện như sau:

Đảm bảo rằng hàng đầu tiên có tiêu đề. Bạn cần tiêu đề vì chúng sẽ là tên trường trong PivotTable của bạn.
Đặt tên cho dải dữ liệu là SalesData_Range.
Nhấp vào phạm vi dữ liệu - SalesData_Range.
Bấm vào tab CHÈN trên ruy-băng.

Bấm PivotTable trong nhóm Bảng. CácCreate PivotTable hộp thoại xuất hiện.

Trong hộp thoại Tạo PivotTable, bên dưới Choose the data that you want to analyze, bạn có thể chọn Bảng hoặc Phạm vi từ sổ làm việc hiện tại hoặc sử dụng nguồn dữ liệu bên ngoài.

Khi bạn đang tạo PivotTable từ một dải dữ liệu, hãy chọn phần sau từ hộp thoại -

Lựa chọn Select a table or range.
Trong hộp Bảng / Phạm vi, hãy nhập tên phạm vi - SalesData_Range.
Chọn Trang tính Mới trong Chọn nơi bạn muốn đặt báo cáo PivotTable và bấm OK.

Bạn có thể chọn phân tích nhiều bảng bằng cách thêm dải dữ liệu này vào Mô hình Dữ liệu. Bạn có thể tìm hiểu cách phân tích nhiều bảng, sử dụng Mô hình Dữ liệu và cách sử dụng nguồn dữ liệu bên ngoài để tạo PivotTable trong hướng dẫn Excel PowerPivot.

Một trang tính mới được chèn vào sổ làm việc của bạn. Trang tính mới chứa một PivotTable trống. Đặt tên cho trang tính - Range-PivotTable.

Như bạn có thể quan sát, PivotTable Fieldsdanh sách xuất hiện ở phía bên phải của trang tính, chứa tên tiêu đề của các cột trong phạm vi dữ liệu. Hơn nữa, trên Ribbon, Công cụ PivotTable - PHÂN TÍCH và THIẾT KẾ xuất hiện.

Thêm trường vào PivotTable

Bạn sẽ hiểu chi tiết về Trường và Vùng PivotTable trong các chương sau của hướng dẫn này. Bây giờ, hãy quan sát các bước để thêm trường vào PivotTable.

Giả sử bạn muốn tóm tắt số lượng đơn đặt hàng mà nhân viên bán hàng khôn ngoan cho các tháng 1, 2 và 3. Bạn có thể làm điều đó trong vài bước đơn giản như sau:

Bấm vào trường Người bán hàng trong danh sách Trường PivotTable và kéo nó vào vùng ROWS.
Bấm vào trường Tháng trong danh sách Trường PivotTable và kéo trường đó vào vùng ROWS.
Nhấp vào Số lượng đặt hàng và kéo nó đến khu vực ∑ VALUES.

PivotTable đầu tiên của bạn đã sẵn sàng như hình dưới đây

Quan sát rằng hai cột xuất hiện trong PivotTable, một cột chứa Nhãn Hàng mà bạn đã chọn, tức là Người bán hàng và Tháng và cột thứ hai chứa Tổng số tiền đặt hàng. Ngoài Tổng số tiền đặt hàng theo tháng cho mỗi Nhân viên bán hàng, bạn cũng sẽ nhận được tổng phụ đại diện cho tổng doanh số bán hàng của người đó. Nếu bạn cuộn trang tính xuống, bạn sẽ thấy hàng cuối cùng là Grand Total đại diện cho tổng doanh số.

Bạn sẽ tìm hiểu thêm về cách tạo PivotTable theo nhu cầu khi bạn tiến bộ qua hướng dẫn này.

Tạo PivotTable từ một bảng

Hãy xem xét bảng Excel sau có chứa dữ liệu bán hàng giống như trong phần trước -

Một bảng Excel vốn dĩ sẽ có tên và các cột sẽ có tiêu đề, đây là yêu cầu để tạo PivotTable. Giả sử tên bảng là SalesData_Table.

Để tạo PivotTable từ bảng Excel này, hãy làm như sau:

Nhấp vào bảng - SalesData_Table.
Bấm vào tab CHÈN trên ruy-băng.
Bấm PivotTable trong nhóm Bảng. CácCreate PivotTable hộp thoại xuất hiện.

Nhấp vào Chọn bảng hoặc phạm vi.
Trong hộp Bảng / Phạm vi, hãy nhập tên bảng - SalesData_Table.
Chọn Bảng tính Mới trong Choose where you want the PivotTable report to be placed. Bấm OK.

Một trang tính mới được chèn vào sổ làm việc của bạn. Trang tính mới chứa một PivotTable trống. Đặt tên cho trang tính - Table-PivotTable. Trang tính - Table-PivotTable trông tương tự như trang bạn có trong trường hợp phạm vi dữ liệu ở phần trước.

Bạn có thể thêm các trường vào PivotTable như bạn đã thấy trong phần - Thêm Trường vào PivotTable, trước đó trong chương này.

Tạo PivotTable với PivotTable được đề xuất

Trong trường hợp bạn không quen thuộc với Excel PivotTables hoặc nếu bạn không biết trường nào sẽ dẫn đến một báo cáo có ý nghĩa, bạn có thể sử dụng lệnh PivotTables được đề xuất trong Excel. PivotTables được đề xuất cung cấp cho bạn tất cả các báo cáo có thể có với dữ liệu của bạn cùng với bố cục được liên kết. Nói cách khác, các tùy chọn được hiển thị sẽ là PivotTable được tùy chỉnh cho dữ liệu của bạn.

Để tạo PivotTable từ bảng Excel SalesData-Table bằng PivotTable được đề xuất, hãy tiến hành như sau:

Bấm vào bảng SalesData-Table.
Bấm vào tab CHÈN.
Bấm PivotTables được đề xuất trong nhóm Bảng. Hộp thoại PivotTables được đề xuất xuất hiện.

Trong hộp thoại PivotTables được đề xuất, các PivotTables có thể tùy chỉnh phù hợp với dữ liệu của bạn sẽ được hiển thị.

Bấm vào từng tùy chọn PivotTable để xem bản xem trước ở phía bên phải.
Nhấp vào PivotTable - Tổng số tiền đặt hàng theo người bán hàng và tháng và nhấp vào OK.

Bạn sẽ nhận được bản xem trước ở phía bên phải.

PivotTable đã chọn sẽ xuất hiện trên một trang tính mới trong sổ làm việc của bạn.

Bạn có thể thấy rằng Trường PivotTable - Nhân viên bán hàng, Khu vực, Số lượng Đơn hàng và Tháng đã được chọn. Trong số này, Khu vực và Nhân viên bán hàng ở khu vực ROWS, Tháng ở khu vực COLUMNS và Tổng số tiền đặt hàng ở khu vực ∑ VALUES.

PivotTable đã tóm tắt dữ liệu Theo khu vực, khôn ngoan cho nhân viên bán hàng và khôn ngoan theo tháng. Các tổng phụ được hiển thị cho từng Khu vực, từng Nhân viên bán hàng và từng Tháng.

Trường PivotTable là Ngăn tác vụ được liên kết với PivotTable. Ngăn Tác vụ Trường PivotTable bao gồm Trường và Vùng. Theo mặc định, Ngăn Tác vụ xuất hiện ở phía bên phải của cửa sổ với Các Trường hiển thị phía trên Các Vùng.

Các trường đại diện cho các cột trong bảng dữ liệu - phạm vi hoặc bảng Excel của bạn và sẽ có các hộp kiểm. Các trường đã chọn được hiển thị trong báo cáo. Các khu vực đại diện cho bố cục của báo cáo và các tính toán có trong báo cáo.

Ở cuối Ngăn tác vụ, bạn sẽ tìm thấy một tùy chọn - Trì hoãn Cập nhật Bố cục với nút CẬP NHẬT bên cạnh.

Theo mặc định, điều này không được chọn và bất kỳ thay đổi nào bạn thực hiện trong việc chọn trường hoặc trong các tùy chọn bố cục sẽ được phản ánh ngay lập tức trong PivotTable.
Nếu bạn chọn mục này, các thay đổi trong lựa chọn của bạn sẽ không được cập nhật cho đến khi bạn nhấp vào UPDATE cái nút.

Trong chương này, bạn sẽ hiểu chi tiết về Fields. Trong chương tiếp theo, bạn sẽ hiểu chi tiết về các khu vực.

Ngăn tác vụ PivotTable Fields

Bạn có thể tìm thấy Ngăn Tác vụ Trường PivotTable trên trang tính nơi bạn có PivotTable. Để xem Ngăn tác vụ Trường PivotTable, hãy bấm vào PivotTable. Trong trường hợp Ngăn Tác vụ Trường PivotTable không được hiển thị, hãy kiểm tra Ruy-băng để biết thông tin sau:

Bấm vào tab PHÂN TÍCH bên dưới CÔNG CỤ CÓ THỂ BỐ TRÍ trên Ruy-băng.
Kiểm tra xem Danh sách Trường có được chọn (nghĩa là được đánh dấu) trong nhóm Hiển thị hay không.
Nếu Danh sách Trường không được chọn, hãy bấm vào đó.

Ngăn Tác vụ Trường PivotTable sẽ được hiển thị ở phía bên phải của cửa sổ, với tiêu đề - Trường PivotTable.

Di chuyển Ngăn tác vụ Trường PivotTable

Ở bên phải tiêu đề Trường PivotTable của Ngăn tác vụ PivotTable, bạn sẽ tìm thấy nút

. Điều này đại diện cho Tùy chọn Ngăn Tác vụ. Bấm vào nút

. Các Tùy chọn Ngăn Tác vụ- Di chuyển, Kích thước và Đóng xuất hiện trong danh sách thả xuống.

Bạn có thể di chuyển Ngăn tác vụ PivotTables đến bất kỳ đâu bạn muốn trong cửa sổ như sau:

Nhấp vào Di chuyển trong danh sách thả xuống. Các

nút xuất hiện trên Task Pane.
Nhấp vào

biểu tượng và kéo ngăn đến vị trí bạn muốn đặt nó. Bạn có thể đặt Ngăn tác vụ bên cạnh PivotTable như được đưa ra bên dưới.

Bạn có thể đặt Ngăn tác vụ ở bên trái cửa sổ như hình dưới đây.

Thay đổi kích thước Ngăn tác vụ Trường PivotTable

Bạn có thể thay đổi kích thước Ngăn tác vụ PivotTables - tức là tăng / giảm chiều dài và / hoặc chiều rộng Ngăn tác vụ như sau:

Bấm vào Tùy chọn Ngăn Tác vụ -

ở bên phải của tiêu đề - Trường PivotTable.
Nhấp vào Kích thước trong danh sách thả xuống.
Sử dụng biểu tượng ⇔ để tăng / giảm chiều rộng của Ngăn tác vụ.
Sử dụng biểu tượng ⇕ để tăng / giảm chiều rộng của Ngăn tác vụ.

Trong khu vực ∑ VALUES, để hiển thị hoàn toàn Tổng Số tiền Đơn hàng, bạn có thể thay đổi kích thước Ngăn Nhiệm vụ như được đưa ra bên dưới.

Trường PivotTable

Danh sách Trường PivotTable bao gồm tất cả các bảng được liên kết với sổ làm việc của bạn và các trường tương ứng. Đó là bằng cách chọn các trường trong danh sách trường PivotTable, bạn sẽ tạo PivotTable.

Các bảng và trường tương ứng có hộp kiểm, phản ánh dữ liệu PivotTable của bạn. Vì bạn có thể chọn / bỏ chọn các trường một cách ngẫu nhiên, bạn có thể nhanh chóng thay đổi PivotTable, làm nổi bật dữ liệu tóm tắt mà bạn muốn báo cáo hoặc trình bày.

Như bạn có thể quan sát, nếu chỉ có một bảng, tên bảng sẽ không được hiển thị trong danh sách Trường PivotTable. Chỉ các trường sẽ được hiển thị với các hộp kiểm.

Phía trên danh sách trường, bạn sẽ thấy hành động Chọn trường để thêm vào báo cáo. Ở bên phải, bạn sẽ tìm thấy nút -

đại diện cho Công cụ.

Bấm vào nút Công cụ.

Trong danh sách thả xuống, bạn sẽ tìm thấy những điều sau:

Năm tùy chọn bố cục khác nhau cho Trường và Khu vực.
Hai tùy chọn cho Thứ tự sắp xếp của các trường trong danh sách Trường -
- Sắp xếp từ A đến Z.
- Sắp xếp theo Thứ tự Nguồn Dữ liệu.

Như bạn có thể quan sát trong danh sách Trường ở trên, thứ tự Sắp xếp là theo mặc định - tức là trong Thứ tự nguồn dữ liệu. Điều này có nghĩa là, đó là thứ tự các cột trong bảng dữ liệu của bạn xuất hiện.

Thông thường, bạn có thể giữ lại thứ tự mặc định. Tuy nhiên, đôi khi, bạn có thể gặp nhiều trường trong một bảng và có thể không quen với chúng. Trong trường hợp như vậy, bạn có thể sắp xếp các trường theo thứ tự bảng chữ cái bằng cách nhấp vào - Sắp xếp A đến Z trong danh sách Công cụ thả xuống. Sau đó, danh sách Trường PivotTable trông như sau:

Vùng PivotTable là một phần của Ngăn tác vụ Trường PivotTable. Bằng cách sắp xếp các trường đã chọn trong các khu vực, bạn có thể đến các bố cục PivotTable khác nhau. Vì bạn có thể chỉ cần kéo các trường qua các khu vực, bạn có thể nhanh chóng chuyển đổi giữa các bố cục khác nhau, tóm tắt dữ liệu theo cách bạn muốn.

Bạn đã học về Ngăn tác vụ Trường PivotTable trong chương trước về Trường PivotTable trong hướng dẫn này. Trong chương này, bạn sẽ tìm hiểu về các khu vực PivotTable.

Có bốn khu vực PivotTable có sẵn -

ROWS.
COLUMNS.
FILTERS.
∑ GIÁ TRỊ (Đọc là Giá trị Tóm tắt).

Thông điệp - Drag fields between areas below xuất hiện phía trên các khu vực.

Với PivotTable Areas, bạn có thể chọn -

Những trường nào để hiển thị dưới dạng hàng (vùng ROWS).
Những trường nào để hiển thị dưới dạng cột (COLUMNS vùng).
Cách tóm tắt dữ liệu của bạn (khu vực area GIÁ TRỊ).
Bộ lọc cho bất kỳ trường nào (vùng LỌC).

Bạn chỉ có thể kéo các trường qua các khu vực này và quan sát cách Bố cục PivotTable thay đổi.

ROWS

Nếu bạn chọn các trường trong danh sách Trường PivotTable chỉ bằng cách chọn hộp, tất cả các trường không phải số sẽ tự động được thêm vào vùng ROWS, theo thứ tự bạn chọn.

Bạn có thể tùy ý kéo một trường vào vùng ROWS. Các trường được đặt trong vùng ROWS xuất hiện dưới dạng hàng trong PivotTable, với Nhãn Hàng là giá trị của các trường đã chọn.

Ví dụ: hãy xem xét bảng dữ liệu Bán hàng.

Kéo trường Nhân viên bán hàng vào vùng ROWS.
Kéo trường Tháng vào vùng ROWS.

PivotTable của bạn xuất hiện với một cột chứa Nhãn Hàng - Người bán hàng và Tháng và một hàng cuối cùng là Tổng cộng, như được đưa ra bên dưới.

CỘT

Bạn có thể kéo các trường vào vùng COLUMNS.

Các trường được đặt trong vùng COLUMNS xuất hiện dưới dạng cột trong PivotTable, với Nhãn Cột là giá trị của các trường đã chọn.

Kéo vùng trường vào vùng COLUMNS. PivotTable của bạn xuất hiện với cột đầu tiên chứa Nhãn Hàng - Người bán hàng và Tháng, bốn cột tiếp theo chứa Nhãn Cột - Vùng và cột cuối cùng Tổng cộng như được đưa ra bên dưới.

Kéo trường Tháng từ ROWS sang COLUMNS.
Kéo trường Vùng từ COLUMNS sang ROWS. Bố cục PivotTable của bạn thay đổi như được cung cấp bên dưới.

Bạn có thể thấy rằng bây giờ chỉ có năm cột - cột đầu tiên có Nhãn hàng, ba cột có Nhãn cột và cột cuối cùng có Tổng cộng.

Số lượng Hàng và Cột dựa trên số lượng giá trị bạn có trong các trường đó.

∑ GIÁ TRỊ

Công dụng chính của PivotTable là để tóm tắt các giá trị. Do đó, bằng cách đặt các trường mà bạn muốn tóm tắt dữ liệu trong đó∑ VALUES khu vực, bạn đến bảng tóm tắt.

Kéo trường Số tiền đặt hàng vào ∑ VALUES.
Kéo trường Khu vực lên phía trên trường Nhân viên bán hàng trong khu vực ROWS. Bước này là thay đổi thứ tự lồng nhau. Bạn sẽ học cách lồng trong chương - Lồng trong PivotTable trong hướng dẫn này.

Như bạn có thể quan sát, dữ liệu được tóm tắt theo khu vực, theo nhân viên bán hàng và theo tháng. Bạn có tổng phụ cho từng khu vực, theo tháng. Bạn cũng có tổng tổng số tháng khôn ngoan trong vùng Tổng tổng số hàng lớn khôn ngoan trong cột Tổng tổng số.

BỘ LỌC

Khu vực Bộ lọc là để đặt các bộ lọc trong PivotTable. Giả sử bạn chỉ muốn hiển thị kết quả riêng cho các vùng đã chọn.

Kéo trường Vùng từ vùng ROWS sang vùng LỌC. Vùng lọc sẽ được đặt phía trên PivotTable. Trong trường hợp bạn không có hàng trống phía trên PivotTable, PivotTable được đẩy xuống chèn các hàng phía trên PivotTable cho bộ lọc.

Như bạn có thể quan sát, (TẤT CẢ) xuất hiện trong bộ lọc theo mặc định và PivotTable hiển thị dữ liệu cho tất cả các giá trị của Vùng.

Nhấp vào mũi tên ở bên phải bộ lọc.
Chọn hộp - Chọn Nhiều Mục.

Các hộp kiểm sẽ xuất hiện cho tất cả các tùy chọn trong danh sách thả xuống. Theo mặc định, tất cả các hộp được chọn.

Đánh dấu vào các ô - Bắc và Nam.
Xóa các hộp khác. Bấm OK.

PivotTable được thay đổi để phản ánh dữ liệu đã lọc.

Bạn có thể quan sát thấy bộ lọc hiển thị (Nhiều Mục). Do đó, khi ai đó đang xem PivotTable, sẽ không rõ ràng ngay lập tức những giá trị nào được lọc.

Excel cung cấp cho bạn một công cụ khác có tên là Slicer để xử lý lọc hiệu quả hơn. Bạn sẽ hiểu chi tiết về Lọc Dữ liệu trong PivotTable trong chương sau của hướng dẫn này.

Excel PivotTable cho phép bạn khám phá và trích xuất dữ liệu quan trọng từ bảng Excel hoặc phạm vi dữ liệu. Có một số cách để thực hiện việc này và bạn có thể chọn những cách phù hợp nhất với dữ liệu của mình. Hơn nữa, trong khi khám phá dữ liệu, bạn có thể xem các kết hợp khác nhau ngay lập tức khi bạn thay đổi lựa chọn của mình để chọn các giá trị dữ liệu.

Bạn có thể thực hiện những việc sau với PivotTable -

Sắp xếp dữ liệu.
Lọc dữ liệu.
Lồng các trường PivotTable.
Mở rộng và Thu gọn các trường.
Giá trị trường nhóm và trường hủy nhóm.

Sắp xếp và lọc dữ liệu

Bạn có thể sắp xếp dữ liệu trong PivotTable theo thứ tự tăng dần hoặc giảm dần của các giá trị trường. Bạn cũng có thể sắp xếp theo tổng phụ từ giá trị lớn nhất đến nhỏ nhất hoặc giá trị nhỏ nhất đến lớn nhất. Bạn cũng có thể đặt các tùy chọn sắp xếp. Bạn sẽ tìm hiểu những điều này một cách chi tiết trong chương - Sắp xếp Dữ liệu trong PivotTable trong hướng dẫn này.

Bạn có thể lọc dữ liệu trong PivotTable để tập trung vào một số dữ liệu cụ thể. Bạn có một số tùy chọn lọc trong PivotTable mà bạn sẽ học trong chương - Lọc Dữ liệu trong PivotTable trong hướng dẫn này. Bạn có thể sử dụng Slicers để lọc, bạn sẽ học trong chương - Lọc bằng Slicers trong hướng dẫn này.

Trường lồng, mở rộng và thu gọn

Bạn có thể lồng các trường trong PivotTable để hiển thị hệ thống phân cấp, nếu có liên quan đến dữ liệu của bạn. Bạn sẽ học điều này trong chương - Lồng trong PivotTable trong hướng dẫn này.

Khi bạn có các trường lồng nhau trong PivotTable của mình, bạn có thể mở rộng và thu gọn giá trị của các trường đó. Bạn sẽ học những điều này trong Chương - Khám phá dữ liệu với các công cụ PivotTable trong hướng dẫn này.

Nhóm và Hủy nhóm Giá trị trường

Bạn có thể nhóm và hủy nhóm các giá trị cụ thể của một trường trong PivotTable. Bạn sẽ tìm hiểu điều này trong Chương - Khám phá Dữ liệu với Công cụ PivotTable trong hướng dẫn này.

Bạn có thể sắp xếp dữ liệu trong PivotTable để dễ dàng tìm thấy các mục bạn muốn phân tích. Bạn có thể sắp xếp dữ liệu từ giá trị thấp nhất đến cao nhất hoặc giá trị cao nhất đến thấp nhất hoặc theo bất kỳ thứ tự tùy chỉnh nào khác mà bạn chọn.

Hãy xem xét PivotTable sau trong đó bạn có dữ liệu bán hàng tóm tắt theo khu vực, nhân viên bán hàng khôn ngoan và khôn ngoan theo tháng.

Sắp xếp trên các trường

Bạn có thể sắp xếp dữ liệu trong PivotTable ở trên trên các Trường nằm trong Hàng hoặc Cột - Khu vực, Người bán hàng và Tháng.

Để sắp xếp PivotTable với trường Người bán hàng, hãy tiến hành như sau:

Bấm vào mũi tên

trong Nhãn Hàng.
Chọn Người bán hàng trong hộp Chọn Trường từ danh sách thả xuống.

Các tùy chọn sắp xếp sau được hiển thị:

Sắp xếp từ A đến Z.
Sắp xếp Z thành A.
Tùy chọn sắp xếp khác.

Hơn nữa, trường Người bán hàng được sắp xếp theo thứ tự tăng dần, theo mặc định. Nhấp chuộtSort Z to A. Trường Nhân viên bán hàng sẽ được sắp xếp theo thứ tự giảm dần.

Theo cách tương tự, bạn có thể sắp xếp trường trong cột - Tháng, bằng cách bấm vào mũi tên

trong nhãn cột.

Sắp xếp theo Tổng phụ

Giả sử bạn muốn sắp xếp PivotTable dựa trên tổng số tiền đặt hàng - từ cao nhất đến thấp nhất ở mọi Vùng. Đó là, bạn muốn sắp xếp PivotTable trên tổng phụ.

Bạn có thể thấy rằng không có mũi tên

cho tổng phụ. Bạn vẫn có thể sắp xếp PivotTable theo tổng phụ như sau:

Bấm chuột phải vào tổng phụ của bất kỳ Nhân viên bán hàng nào trong cột Tổng số.
Lựa chọn Sort từ danh sách thả xuống.
Một danh sách thả xuống khác xuất hiện với các tùy chọn sắp xếp - Sắp xếp Nhỏ nhất đến Lớn nhất, Sắp xếp Lớn nhất đến Nhỏ nhất và Tùy chọn Sắp xếp khác. Chọn Sắp xếp Lớn nhất đến Nhỏ nhất.

Các tổng phụ trong cột Tổng cộng được sắp xếp từ giá trị cao nhất đến giá trị thấp nhất, ở mọi vùng.

Tương tự như vậy, nếu bạn muốn sắp xếp PivotTable trên vùng tổng phụ một cách khôn ngoan, hãy thực hiện như sau:

Nhấp chuột phải vào tổng phụ của bất kỳ vùng nào trong cột Tổng cộng.
Nhấp vào Sắp xếp trong danh sách thả xuống.
Nhấp vào Sắp xếp Lớn nhất đến Nhỏ nhất trong danh sách thả xuống thứ hai. PivotTable sẽ được sắp xếp theo tổng phụ theo vùng.

Như bạn có thể quan sát, miền Nam có lượng đặt hàng cao nhất trong khi miền Bắc có lượng đặt hàng thấp nhất.

Bạn cũng có thể sắp xếp PivotTable dựa trên tổng số tiền theo tháng như sau:

Nhấp chuột phải vào bất kỳ Tổng phụ nào trong hàng Tổng tổng.
Chọn Sắp xếp từ danh sách thả xuống.
Chọn Sắp xếp Lớn nhất đến Nhỏ nhất từ danh sách thả xuống thứ hai.

PivotTable sẽ được sắp xếp theo tổng số tiền theo tháng.

Bạn có thể thấy rằng tháng 2 có lượng đặt hàng cao nhất trong khi tháng 3 có lượng đặt hàng thấp nhất.

Các tùy chọn sắp xếp khác

Giả sử bạn muốn sắp xếp PivotTable theo vùng tổng số tiền trong tháng 1.

Bấm vào mũi tên

trong Nhãn Hàng.
Chọn Thêm Tùy chọn Sắp xếp từ danh sách thả xuống. CácSort (Region) hộp thoại xuất hiện.

Như bạn có thể quan sát, trong phần Tóm tắt, thứ tự Sắp xếp hiện tại được đưa ra dưới dạng Khu vực Sắp xếp theo thứ tự tăng dần. Tăng dần (A đến Z) theo được chọn trong Tùy chọn sắp xếp. Trong hộp bên dưới,Region được hiển thị.

Nhấp vào hộp chứa Vùng.
Nhấp vào Tổng số tiền đặt hàng.

Nhấn vào More Optionscái nút. CácMore Sort Options (Region) hộp thoại xuất hiện.

Như bạn có thể quan sát, trong Sắp xếp theo, Tổng số được chọn. Trong Tóm tắt, thứ tự sắp xếp hiện tại được đưa ra làSort Region by Sum of Order Amount theo thứ tự tăng dần.

Nhấp chuột Values in selected column: trong Sắp xếp theo.
Trong hộp bên dưới, nhập B5.

Như bạn có thể quan sát, trong phần Tóm tắt, thứ tự sắp xếp hiện tại được đưa ra như sau:

Sắp xếp khu vực theo Sum of Order Amounttheo thứ tự tăng dần sử dụng các giá trị trong cột này: Tháng Giêng. Bấm OK.
Hộp thoại Sắp xếp (Khu vực) xuất hiện. Chọn Giảm dần (Z đến A) bằng cách: trong Tùy chọn sắp xếp.

Trong Tóm tắt, thứ tự sắp xếp hiện tại được đưa ra như sau:

Sắp xếp Khu vực theo Tổng số tiền đặt hàng theo thứ tự giảm dần, sử dụng các giá trị trong cột này: Tháng Giêng. Bấm OK. PivotTable sẽ được sắp xếp theo khu vực, sử dụng các giá trị vào tháng Giêng.

Như bạn có thể quan sát, trong tháng 1, miền Tây có lượng đặt hàng cao nhất trong khi miền Bắc có lượng đặt hàng thấp nhất.

Sắp xếp dữ liệu theo cách thủ công

Trong PivotTable, dữ liệu được sắp xếp tự động theo tùy chọn sắp xếp mà bạn đã chọn. Điều này được gọi là Tự động sắp xếp.

Đặt con trỏ vào mũi tên

trong Nhãn Hàng hoặc Nhãn Cột.

Tự động sắp xếp xuất hiện, hiển thị thứ tự sắp xếp hiện tại cho từng trường trong PivotTable. Bây giờ, giả sử bạn muốn sắp xếp trường Vùng theo thứ tự - Đông, Tây, Bắc và Nam. Bạn có thể làm điều này theo cách thủ công, như sau:

Nhấp vào mũi tên

trong Nhãn Hàng.
Chọn Vùng trong hộp Chọn Trường từ danh sách thả xuống.
Nhấp chuột More Sort Options. Hộp thoại Sắp xếp (Khu vực) xuất hiện.
Chọn Thủ công (bạn có thể kéo các mục để sắp xếp lại chúng).
Bấm OK.

Trong Tóm tắt, thứ tự sắp xếp hiện tại được đưa ra dưới dạng Kéo các mục của trường Vùng để hiển thị chúng theo thứ tự bất kỳ.

Nhấp vào phía Đông và kéo nó lên trên cùng. Trong khi bạn đang kéo về phía Đông, một thanh ngang màu xanh lục xuất hiện trên toàn bộ di chuyển của hàng.

Lặp lại thao tác kéo với các mục khác của trường Vùng cho đến khi bạn có được sự sắp xếp theo yêu cầu.

Bạn có thể quan sát những điều sau:

Các mục của trường lồng nhau - Người bán hàng cũng di chuyển cùng với mục trường Vùng tương ứng. Hơn nữa, các giá trị trong các cột khác cũng di chuyển tương ứng.
Nếu bạn đặt con trỏ vào mũi tên

trong Nhãn Hàng hoặc Nhãn Cột, Tự động Sắp xếp sẽ xuất hiện hiển thị thứ tự sắp xếp hiện tại của các trường Chỉ nhân viên bán hàng và Tháng. Vì bạn đã sắp xếp trường Vùng theo cách thủ công, nó sẽ không hiển thị trong Tự động sắp xếp.

Note- Bạn không thể sử dụng cách kéo thủ công này đối với các mục của trường nằm trong vùng ∑ VALUES của danh sách Trường PivotTable. Do đó, bạn không thể kéo các giá trị Tổng của Số lượng Đơn hàng trong PivotTable này.

Đặt các tùy chọn sắp xếp

Trong phần trước, bạn đã học cách đặt tùy chọn sắp xếp cho một trường thành thủ công. Bạn có một số tùy chọn sắp xếp khác mà bạn có thể đặt như sau:

Nhấp vào mũi tên

trong Nhãn Hàng.
Chọn Vùng trong hộp Chọn Trường.
Nhấp vào Tùy chọn sắp xếp khác. Hộp thoại Sắp xếp (Khu vực) xuất hiện.
Nhấp vào nút Tùy chọn khác.

Hộp thoại Tùy chọn Sắp xếp Khác (Khu vực) xuất hiện. Bạn có thể đặt nhiều tùy chọn sắp xếp hơn trong hộp thoại này.

Trong Tự động sắp xếp, bạn có thể chọn hoặc bỏ chọn hộp - Tự động sắp xếp mỗi khi báo cáo được cập nhật, để cho phép hoặc dừng sắp xếp tự động bất cứ khi nào dữ liệu PivotTable được cập nhật.

Bỏ chọn hộp - Sort automatically mỗi khi báo cáo được cập nhật.

Bây giờ, tùy chọn thứ tự sắp xếp khóa đầu tiên có sẵn. Bạn có thể sử dụng tùy chọn này để chọn thứ tự tùy chỉnh mà bạn muốn sử dụng.

Bấm vào hộp bên dưới Thứ tự sắp xếp khóa đầu tiên.

Như bạn có thể quan sát, danh sách tùy chỉnh ngày trong tuần và tháng trong năm được cung cấp trong danh sách thả xuống. Bạn có thể sử dụng bất kỳ kích thước nào trong số này hoặc bạn có thể sử dụng danh sách tùy chỉnh của riêng mình như Cao, Trung bình, Thấp hoặc danh sách kích thước S, M, L, XL không theo thứ tự bảng chữ cái.

Bạn có thể tạo danh sách tùy chỉnh của mình từ tab FILE trên Ribbon. FILE → Tùy chọn. Trong hộp thoại Tùy chọn Excel, bấm vào nâng cao và duyệt đến Chung. Bạn sẽ tìm thấy nút Chỉnh sửa danh sách tùy chỉnh bên cạnh Tạo danh sách để sử dụng theo trình tự sắp xếp và điền.

Lưu ý rằng thứ tự sắp xếp danh sách tùy chỉnh không được giữ lại khi bạn cập nhật (làm mới) dữ liệu trong PivotTable của mình.

Trong Sắp xếp Theo, bạn có thể nhấp vào Grand Total or Valuestrong các cột đã chọn để sắp xếp theo các giá trị này. Tùy chọn này không khả dụng khi bạn đặt sắp xếp thành Thủ công.

Các điểm cần cân nhắc khi sắp xếp PivotTable

Khi bạn sắp xếp dữ liệu trong PivotTable, hãy nhớ những điều sau:

Dữ liệu có khoảng trắng ở đầu sẽ ảnh hưởng đến kết quả sắp xếp. Xóa bất kỳ khoảng trắng nào trước khi bạn sắp xếp dữ liệu.
Bạn không thể sắp xếp các mục nhập văn bản phân biệt chữ hoa chữ thường.
Bạn không thể sắp xếp dữ liệu theo một định dạng cụ thể như ô hoặc màu phông chữ.
Bạn không thể sắp xếp dữ liệu theo các chỉ báo định dạng có điều kiện, chẳng hạn như bộ biểu tượng.

Bạn có thể phải thực hiện phân tích chuyên sâu về một tập hợp con dữ liệu PivotTable của mình. Điều này có thể là do bạn có dữ liệu lớn và sự tập trung của bạn được yêu cầu vào một phần nhỏ hơn của dữ liệu hoặc bất kể kích thước của dữ liệu, sự tập trung của bạn là bắt buộc vào một số dữ liệu cụ thể. Bạn có thể lọc dữ liệu trong PivotTable dựa trên một tập hợp con các giá trị của một hoặc nhiều trường. Có một số cách để làm điều đó như sau:

Lọc bằng Slicer.
Lọc bằng Bộ lọc Báo cáo.
Lọc dữ liệu theo cách thủ công.
Lọc bằng Bộ lọc Nhãn.
Lọc bằng Bộ lọc giá trị.
Lọc bằng Bộ lọc ngày.
Lọc bằng Bộ lọc 10 hàng đầu.
Lọc bằng Dòng thời gian.

Bạn sẽ tìm hiểu cách lọc dữ liệu bằng Slicer trong chương tiếp theo. Bạn sẽ hiểu việc lọc bằng các phương pháp khác được đề cập ở trên trong chương này.

Hãy xem xét PivotTable sau, trong đó bạn có vùng dữ liệu bán hàng tóm tắt khôn ngoan, nhân viên bán hàng khôn ngoan và khôn ngoan theo tháng.

Bộ lọc Báo cáo

Bạn có thể gán Bộ lọc cho một trong các trường để có thể thay đổi động PivotTable dựa trên các giá trị của trường đó.

Kéo Vùng từ Hàng sang Bộ lọc trong Vùng PivotTable.

Bộ lọc có nhãn là Vùng xuất hiện phía trên PivotTable (trong trường hợp bạn không có hàng trống phía trên PivotTable của mình, PivotTable được đẩy xuống để tạo khoảng trống cho Bộ lọc.

Bạn sẽ quan sát thấy

Giá trị nhân viên bán hàng xuất hiện trong các hàng.
Giá trị tháng xuất hiện trong các cột.
Bộ lọc Vùng xuất hiện ở trên cùng với mặc định được chọn là TẤT CẢ.
Giá trị tổng kết là Tổng số tiền đặt hàng.
- Tổng số tiền đặt hàng mà nhân viên bán hàng khôn ngoan xuất hiện trong cột Tổng số tiền.
- Tổng của Đơn hàng Số tiền Theo tháng xuất hiện trong hàng Tổng Tổng.
Bấm vào mũi tên trong hộp bên phải của Vùng lọc.

Một danh sách thả xuống với các giá trị của trường Vùng xuất hiện. Kiểm tra hộpSelect Multiple Items.

Theo mặc định, tất cả các hộp được chọn. Bỏ chọn hộp (All). Tất cả các hộp sẽ được bỏ chọn.

Sau đó đánh dấu vào các hộp - South and West và bấm OK.

Dữ liệu chỉ liên quan đến các khu vực Nam và Tây sẽ được tóm tắt.

Trong ô bên cạnh Vùng lọc - (Nhiều Mục) được hiển thị, cho biết rằng bạn đã chọn nhiều mục. Tuy nhiên, có bao nhiêu mục và / hoặc mục nào không được biết từ báo cáo được hiển thị. Trong trường hợp này, sử dụng Slicer là một lựa chọn tốt hơn để lọc.

Lọc thủ công

Bạn cũng có thể lọc PivotTable bằng cách chọn các giá trị của trường theo cách thủ công. Bạn có thể thực hiện việc này bằng cách nhấp vào mũi tên

trong ô Nhãn Hàng hoặc Nhãn Cột.

Giả sử bạn chỉ muốn phân tích dữ liệu tháng Hai. Bạn cần lọc các giá trị theo trường Tháng. Như bạn có thể quan sát, Tháng là một phần của Nhãn Cột.

Bấm vào mũi tên

trong ô Nhãn Cột.

Như bạn có thể quan sát, có một hộp Tìm kiếm trong danh sách thả xuống và bên dưới hộp, bạn có danh sách các giá trị của trường đã chọn, tức là Tháng. Các hộp của tất cả các giá trị được chọn, cho thấy rằng tất cả các giá trị của trường đó đã được chọn.

Bỏ chọn hộp (Chọn Tất cả) ở đầu danh sách các giá trị.
Chọn các hộp của các giá trị bạn muốn hiển thị trong PivotTable của mình, trong trường hợp này là Tháng Hai và bấm OK.

PivotTable chỉ hiển thị những giá trị có liên quan đến giá trị trường Tháng đã chọn - Tháng Hai. Bạn có thể quan sát thấy mũi tên lọc thay đổi thành biểu tượng

để cho biết rằng một bộ lọc được áp dụng. Đặt con trỏ vào

biểu tượng.

Bạn có thể quan sát thấy hiển thị cho biết rằng Bộ lọc Thủ công được áp dụng trên trường- Tháng.

Nếu bạn muốn thay đổi giá trị lựa chọn bộ lọc, hãy làm như sau:

Nhấp vào

biểu tượng.
Chọn / bỏ chọn hộp của các giá trị.

Nếu tất cả các giá trị của trường không hiển thị trong danh sách, hãy kéo chốt ở góc dưới bên phải của menu thả xuống để phóng to nó. Ngoài ra, nếu bạn biết giá trị, hãy nhập giá trị đó vào hộp Tìm kiếm.

Giả sử bạn muốn áp dụng một bộ lọc khác trên PivotTable đã lọc ở trên. Ví dụ: bạn muốn hiển thị dữ liệu của Walters, Chris cho tháng Hai. Bạn cần tinh chỉnh bộ lọc của mình bằng cách thêm một bộ lọc khác cho trường Nhân viên bán hàng. Như bạn có thể quan sát, Nhân viên bán hàng là một phần của Nhãn hàng.

Bấm vào mũi tên

trong ô Nhãn Hàng.

Danh sách các giá trị của trường - Vùng được hiển thị. Điều này là do, Khu vực nằm ở cấp bên ngoài của Nhân viên bán hàng trong thứ tự lồng nhau. Bạn cũng có một tùy chọn bổ sung - Chọn Trường. Bấm vào hộp Chọn Trường.

Nhấp vào Nhân viên bán hàng từ danh sách thả xuống. Danh sách các giá trị của trường - Nhân viên bán hàng sẽ được hiển thị.
Bỏ chọn (Chọn tất cả) và chọn Walters, Chris.
Bấm OK.

PivotTable chỉ hiển thị những giá trị có liên quan đến giá trị trường Tháng đã chọn - Tháng Hai và giá trị trường Người bán hàng - Walters, Chris.

Mũi tên lọc cho Nhãn Hàng cũng thay đổi thành biểu tượng

để cho biết rằng bộ lọc được áp dụng. Đặt con trỏ vào

biểu tượng trên Nhãn Hàng hoặc Nhãn Cột.

Một hộp văn bản được hiển thị cho biết rằng Bộ lọc Thủ công được áp dụng trên các trường - Tháng và Người bán hàng.

Do đó, bạn có thể lọc PivotTable theo cách thủ công dựa trên bất kỳ số lượng trường nào và trên bất kỳ số lượng giá trị nào.

Lọc theo văn bản

Nếu bạn có các trường chứa văn bản, bạn có thể lọc PivotTable theo Văn bản, miễn là nhãn trường tương ứng là dựa trên văn bản. Ví dụ: hãy xem xét dữ liệu Nhân viên sau đây.

Dữ liệu có thông tin chi tiết về các nhân viên - EmployeeID, Title, BirthDate, MaritalStatus, Gender và HireDate. Ngoài ra, dữ liệu còn có cấp quản lý của nhân viên (cấp 0 - 4).

Giả sử bạn phải thực hiện một số phân tích về số lượng nhân viên báo cáo cho một nhân viên nhất định theo chức danh. Bạn có thể tạo PivotTable như dưới đây.

Bạn có thể muốn biết có bao nhiêu nhân viên có 'Người quản lý' trong chức danh của họ có nhân viên báo cáo cho họ. Vì Tiêu đề Nhãn là văn bản, bạn có thể áp dụng Bộ lọc Nhãn trên trường Tiêu đề như sau:

Bấm vào mũi tên

trong ô Nhãn Hàng.
Chọn Tiêu đề trong hộp Chọn Trường từ danh sách thả xuống.
Nhấp vào Bộ lọc Nhãn.
Nhấp vào Chứa trong danh sách thả xuống thứ hai.

Hộp thoại Bộ lọc Nhãn (Tiêu đề) xuất hiện. Nhập Trình quản lý vào hộp bên cạnh Chứa. Bấm OK.

PivotTable sẽ được lọc thành các giá trị Tiêu đề có chứa 'Trình quản lý'.

Nhấp vào

biểu tượng.

Bạn có thể thấy điều đó

được hiển thị cho biết như sau:

Bộ lọc Nhãn được áp dụng trên trường - Tiêu đề và
Bộ lọc Nhãn được áp dụng là gì.

Lọc theo giá trị

Bạn có thể muốn biết chức danh của những nhân viên có hơn 25 nhân viên báo cáo cho họ. Đối với điều này, bạn có thể áp dụng Bộ lọc Giá trị trên trường Tiêu đề như sau:

Bấm vào mũi tên

trong ô Nhãn Hàng.
Lựa chọn Title trong hộp Chọn Trường từ danh sách thả xuống.
Nhấp vào Bộ lọc giá trị.
Chọn Lớn hơn hoặc bằng từ danh sách thả xuống thứ hai.

Hộp thoại Bộ lọc Giá trị (Tiêu đề) xuất hiện. Nhập 25 vào ô bên phải.

PivotTable sẽ được lọc để hiển thị các chức danh nhân viên có hơn 25 nhân viên báo cáo cho họ.

Lọc theo ngày

Bạn có thể muốn hiển thị dữ liệu của tất cả nhân viên đã được thuê trong năm tài chính 2015-15. Bạn có thể sử dụng Bộ lọc dữ liệu tương tự như sau:

Bao gồm trường HireDate trong PivotTable. Bây giờ, bạn không yêu cầu dữ liệu người quản lý và vì vậy hãy xóa trường ManagerLevel khỏi PivotTable.

Bây giờ bạn có trường Ngày trong PivotTable, bạn có thể sử dụng Bộ lọc Ngày.

Bấm vào mũi tên

trong ô Nhãn Hàng.
Chọn Ngày thuê trong hộp Chọn Trường từ danh sách thả xuống.
Nhấp vào Bộ lọc ngày.
Seelct Between từ danh sách thả xuống thứ hai.

Hộp thoại Bộ lọc Ngày (HireDate) xuất hiện. Nhập 4/1/2014 và 3/31/2015 vào hai hộp Ngày. Bấm OK.

PivotTable sẽ được lọc để chỉ hiển thị các dữ liệu với HireDate giữa 1 ^st tháng 4 năm 2014 và 31 ^st tháng 3 năm 2015.

Bạn có thể nhóm các ngày thành Quarters như sau:

Nhấp chuột phải vào bất kỳ ngày nào. CácGrouping hộp thoại xuất hiện.
Nhập 4/1/2014 vào hộp Bắt đầu lúc. Kiểm tra hộp.
Nhập 3/31/2015 vào hộp Kết thúc lúc. Kiểm tra hộp.
Nhấp vào Quarters trong hộp bên dưới By.

Các ngày sẽ được nhóm thành các phần tư trong PivotTable. Bạn có thể làm cho bảng trông nhỏ gọn bằng cách kéo trường HireDate từ vùng ROWS sang vùng COLUMNS.

Bạn sẽ có thể biết có bao nhiêu nhân viên đã được thuê trong năm tài chính, theo quý.

Lọc bằng 10 bộ lọc hàng đầu

Bạn có thể sử dụng Bộ lọc 10 hàng đầu để hiển thị một vài giá trị trên cùng hoặc một vài giá trị dưới cùng của một trường trong PivotTable.

Bấm vào mũi tên

trong ô Nhãn Hàng.
Nhấp vào Bộ lọc giá trị.
Nhấp vào Top 10 trong danh sách thả xuống thứ hai.

Hộp thoại 10 Bộ lọc (Tiêu đề) hàng đầu xuất hiện.

Trong hộp đầu tiên, nhấp vào Trên cùng (Bạn cũng có thể chọn Dưới cùng).
Trong hộp thứ hai, hãy nhập một số, chẳng hạn như, 7.
Trong hộp thứ ba, bạn có ba tùy chọn mà bạn có thể lọc.
- Nhấp vào Mục để lọc theo số lượng mục.
- Nhấp vào Phần trăm để lọc theo phần trăm.
- Bấm vào Sum để lọc theo tổng.
Khi bạn có số lượng EmployeeID, hãy nhấp vào Mục.
Trong hộp thứ tư, bấm vào trường Số lượng ID nhân viên.
Bấm OK.

Bảy giá trị hàng đầu theo số lượng EmployeeID sẽ được hiển thị trong PivotTable.

Như bạn có thể quan sát, số lượng tuyển dụng cao nhất trong năm tài chính là Kỹ thuật viên sản xuất và số lượng chủ yếu trong số này là trong Qtr1.

Lọc bằng Dòng thời gian

Nếu PivotTable của bạn có trường ngày, bạn có thể lọc PivotTable bằng Dòng thời gian.

Tạo PivotTable từ Dữ liệu Nhân viên mà bạn đã sử dụng trước đó và thêm dữ liệu vào Mô hình Dữ liệu trong hộp thoại Tạo PivotTable.

Kéo Tiêu đề trường vào vùng ROWS.
Kéo trường EmployeeID đến vùng ∑ VALUES và chọn Số lượng để tính toán.

Bấm vào PivotTable.
Bấm vào tab CHÈN.
Nhấp vào Dòng thời gian trong nhóm Bộ lọc. Hộp thoại Chèn đường thời gian xuất hiện.

Đánh dấu vào hộp HireDate.
Bấm OK. Dòng thời gian xuất hiện trong trang tính.
Công cụ Dòng thời gian xuất hiện trên Ribbon.

Như bạn có thể quan sát, Tất cả các Khoảng thời gian - tính bằng Tháng được hiển thị trên Dòng thời gian.

Bấm vào mũi tên bên cạnh - THÁNG.
Chọn QUARTERS từ danh sách thả xuống. Màn hình Dòng thời gian thay đổi thành Tất cả các khoảng thời gian - tính theo Phần tư.

Nhấp vào Q1 2014.
Giữ phím Shift và kéo đến 2014 Q4. Khoảng thời gian được chọn là Q1 - Q4 năm 2014.
PivotTable được lọc theo Khoảng thời gian này.

Xóa bộ lọc

Thỉnh thoảng, bạn có thể phải xóa các bộ lọc bạn đã đặt để chuyển đổi giữa các kết hợp và phép chiếu khác nhau của dữ liệu. Bạn có thể làm điều này theo một số cách như sau:

Xóa tất cả các bộ lọc trong PivotTable

Bạn có thể xóa tất cả các bộ lọc được đặt trong PivotTable một lúc như sau:

Bấm vào tab HOME trên ruy-băng.
Nhấp vào Sắp xếp & Lọc trong nhóm Chỉnh sửa.
Chọn Xóa từ danh sách thả xuống.

Xóa nhãn, bộ lọc ngày hoặc giá trị

Để xóa Bộ lọc Nhãn, Ngày hoặc Giá trị, hãy làm như sau:

Bấm vào biểu tượng trong Nhãn Hàng hoặc Nhãn Cột.
Bấm vào

<tên trường> mà bạn muốn xóa bộ lọc trong hộp Chọn Trường trong danh sách thả xuống.
Nhấp vào Xóa bộ lọc khỏi <Tên đã nộp> xuất hiện trong danh sách thả xuống.
Bấm OK. Bộ lọc cụ thể sẽ bị xóa.

Sử dụng một hoặc nhiều máy cắt là một cách nhanh chóng và hiệu quả để lọc dữ liệu của bạn. Có thể chèn các lát cắt cho từng trường mà bạn muốn lọc. Slicer sẽ có các nút biểu thị các giá trị của trường mà nó đại diện. Bạn có thể nhấp vào các nút của máy cắt để chọn / bỏ chọn các giá trị trong trường.

Các lát cắt luôn hiển thị với PivotTable và do đó bạn sẽ luôn biết những trường nào được sử dụng để lọc và những giá trị nào trong các trường đó được hiển thị hoặc ẩn trong PivotTable đã lọc.

Để hiểu việc sử dụng máy thái, hãy xem xét ví dụ về dữ liệu bán hàng theo khu vực, theo tháng và khôn ngoan của nhân viên bán hàng. Giả sử bạn có PivotTable sau với dữ liệu này.

Chèn các lát cắt

Giả sử bạn muốn lọc PivotTable này dựa trên các trường - Vùng và Tháng.

Nhấp vào PHÂN TÍCH bên dưới CÔNG CỤ CÓ THỂ CHỌN được trên Ribbon.
Bấm vào Chèn Slicer trong nhóm Bộ lọc. Hộp thoại Chèn lát cắt xuất hiện. Nó chứa tất cả các trường từ bảng dữ liệu của bạn.
Chọn các hộp Khu vực và Tháng.
Bấm OK.

Các lát cắt cho mỗi trường đã chọn sẽ xuất hiện với tất cả các giá trị được chọn theo mặc định. Công cụ Slicer xuất hiện trên Ribbon để làm việc trên cài đặt Slicer, hãy xem và cảm nhận.

Lọc bằng Slicer

Như bạn có thể quan sát, mỗi bộ cắt có tất cả các giá trị của trường mà nó đại diện và các giá trị được hiển thị dưới dạng các nút. Theo mặc định, tất cả các giá trị của một trường được chọn và do đó tất cả các nút được đánh dấu.

Giả sử bạn chỉ muốn hiển thị PivotTable cho các khu vực Nam và Tây và cho các Tháng Tháng Hai và Tháng Ba.

Nhấp vào Nam trong Bộ cắt cho Khu vực. Chỉ Nam sẽ được đánh dấu trong Slicer - Region.
Giữ phím Ctrl và nhấp vào Tây trong Bộ cắt cho Khu vực.
Nhấp vào Tháng Hai trong Bộ cắt cho Tháng.
Giữ phím Ctrl và nhấp vào March trong Slicer for Month.

Các mục đã chọn trong Slice được đánh dấu. PivotTable với các giá trị tóm tắt cho các mục đã chọn sẽ được hiển thị.

Để thêm / xóa các giá trị của một trường khỏi bộ lọc, hãy giữ phím Ctrl và nhấp vào các nút đó trong bộ cắt của trường.

Xóa bộ lọc trong máy cắt

Để xóa bộ lọc trong máy cắt, hãy nhấp vào

ở góc trên bên phải của máy cắt.

Loại bỏ một Slicer

Giả sử bạn muốn loại bỏ bộ cắt cho trường Vùng.

Nhấp chuột phải vào Slicer - Region.
Nhấp vào Xóa “Khu vực” trong danh sách thả xuống.

Công cụ Slicer

Sau khi bạn chèn một bộ cắt, Công cụ Bộ cắt sẽ xuất hiện trên Ruy băng với tab TÙY CHỌN. Để xem Công cụ cắt, hãy nhấp vào một máy cắt.

Như bạn có thể quan sát, trong tab Công cụ cắt - TÙY CHỌN, bạn có một số tùy chọn để thay đổi giao diện của máy cắt bao gồm:

Chú thích của Slicer
Cài đặt Slicer
Báo cáo kết nối
Ngăn lựa chọn

Chú thích của Slicer

Bạn có thể tìm hộp Slicer Caption trong nhóm Slicer. Chú thích của bộ cắt là tiêu đề được hiển thị trên bộ cắt. Theo mặc định,Slicer Caption là tên của trường mà nó đại diện.

Nhấp vào Trình cắt cho Khu vực.
Bấm vào tab TÙY CHỌN trên Ruy-băng.

Nhóm Slicer trên Ribbon, trong hộp Slicer Caption, Region được hiển thị dưới dạng tiêu đề của bộ cắt. Đây là tên của trường mà bộ cắt được chèn vào. Bạn có thể thay đổi Chú thích của Slicer như sau:

Bấm vào hộp Slicer Caption trong nhóm Slicer trên Ribbon.
Xóa khu vực. Hộp đã được xóa.
Nhập Vị trí vào hộp và nhấn Enter. Chú thích của bộ cắt thay đổi thành Vị trí và nó được phản ánh giống như tiêu đề trong bộ cắt.

Note- Bạn chỉ thay đổi chú thích của bộ cắt, tức là tiêu đề. Tên của trường mà bộ cắt đại diện - Vùng vẫn như cũ.

Cài đặt Slicer

Bạn có thể sử dụng Cài đặt bộ cắt để thay đổi tên của bộ cắt, thay đổi chú thích của bộ cắt, chọn có hiển thị tiêu đề của bộ cắt hay không và đặt các tùy chọn sắp xếp và lọc cho các mục -

Nhấp vào máy cắt - Vị trí.
Bấm vào tab TÙY CHỌN trên Ruy-băng. Bạn có thể tìm Cài đặt Trình cắt trong nhóm Trình cắt trên Ribbon. Bạn cũng có thể tìm thấy Cài đặt trình cắt trong danh sách thả xuống khi bạn nhấp chuột phải vào trình cắt.
Nhấp vào Cài đặt Slicer. Hộp thoại Cài đặt Slicer xuất hiện.

Như bạn có thể quan sát, những điều sau đây là cố định cho máy cắt -

Tên nguồn.
Tên để sử dụng trong công thức.

Bạn có thể thay đổi những điều sau cho máy cắt -

Name.
Header - Chú thích.
Tiêu đề hiển thị.
Các tùy chọn Sắp xếp và Lọc cho các mục được hiển thị trên máy cắt.

Báo cáo kết nối

Bạn có thể kết nối các PivotTable khác nhau với một Slicer, miễn là một trong các quy tắc sau tốt:

PivotTable được tạo bằng cách sử dụng cùng một dữ liệu.
Một PivotTable đã được sao chép và dán như một PivotTable bổ sung.
Nhiều PivotTable được tạo trên các trang tính riêng biệt với Hiển thị Trang Bộ lọc Báo cáo.

Hãy xem xét các PivotTable sau đây được tạo từ cùng một dữ liệu:

Đặt tên PivotTable trên cùng là PivotTable-Top và PivotTable dưới cùng là PivotTable-Bottom.
Bấm vào PivotTable trên cùng.
Chèn một Slicer cho vùng trường.
Chọn Đông và Bắc trên Slicer.

Quan sát rằng bộ lọc chỉ được áp dụng cho PivotTable trên cùng chứ không phải cho PivotTable dưới cùng. Bạn có thể sử dụng cùng một bộ cắt cho cả PivotTable bằng cách kết nối nó với PivotTable dưới cùng cũng như sau:

Nhấp vào bộ cắt - Vùng. CÔNG CỤ SLICER xuất hiện trên Ribbon.
Bấm vào tab TÙY CHỌN trên Ruy-băng.

Bạn sẽ tìm thấy Kết nối Báo cáo trong nhóm Trình cắt trên Ruy-băng. Bạn cũng có thể tìm thấy Kết nối Báo cáo trong danh sách thả xuống khi bạn nhấp chuột phải vào bộ cắt.

Nhấp chuột Report Connections trong nhóm Slicer.

Các Report Connectionshộp thoại xuất hiện. Hộp PivotTable-Top được chọn và các hộp khác không được chọn. Đánh dấu hộp PivotTable-Bottom và bấm OK.

PivotTable dưới cùng sẽ được lọc thành các mục đã chọn - Đông và Bắc.

Điều này trở nên khả thi vì cả hai PivotTable hiện được kết nối với bộ cắt. Nếu bạn thực hiện các thay đổi đối với các lựa chọn trong bộ cắt, bộ lọc giống nhau sẽ xuất hiện trong cả PivotTable.

Ngăn lựa chọn

Bạn có thể tắt và bật hiển thị các dao cắt trên trang tính bằng Ngăn lựa chọn.

Nhấp vào máy cắt - Vị trí.
Bấm vào tab TÙY CHỌN trên Ruy-băng.
Bấm Ngăn Lựa chọn trong nhóm Sắp xếp trên Ruy băng. Ngăn lựa chọn xuất hiện ở bên phải của cửa sổ.

Như bạn có thể quan sát, tên của tất cả các dao được liệt kê trong ngăn Lựa chọn. Ở phía bên phải của tên, bạn có thể tìm thấy biểu tượng khả năng hiển thị -

cho biết bộ cắt hiển thị trên trang tính.

Nhấp vào

biểu tượng cho Tháng. Biểu tượng

thay đổi thành biểu tượng

, cho biết rằng máy cắt bị ẩn (không nhìn thấy).

Như bạn có thể quan sát, bộ cắt - Tháng không được hiển thị trên trang tính. Tuy nhiên, hãy nhớ rằng bạn đã không loại bỏ máy cắt trong Tháng mà bạn chỉ ẩn nó đi.

Nhấp vào

biểu tượng cho Tháng.
Biểu tượng

thay đổi thành biểu tượng

, cho biết rằng máy cắt hiện đang hiển thị.

Khi bạn bật / tắt chế độ hiển thị của máy cắt, việc lựa chọn các mục trong máy cắt đó để lọc vẫn không thay đổi. Bạn cũng có thể thay đổi thứ tự của các mảnh trong ngăn Lựa chọn bằng cách kéo chúng lên / xuống.

Nếu bạn có nhiều trường trong bất kỳ vùng PivotTable nào, thì bố cục PivotTable phụ thuộc vào thứ tự bạn đặt các trường trong vùng đó. Đây được gọi là Lệnh làm tổ.

Nếu bạn biết dữ liệu của mình được cấu trúc như thế nào, bạn có thể đặt các trường theo thứ tự bắt buộc. Nếu bạn không chắc chắn về cấu trúc của dữ liệu, bạn có thể thay đổi thứ tự của các trường thay đổi ngay lập tức bố cục của PivotTable.

Trong chương này, bạn sẽ hiểu thứ tự lồng nhau của các trường và cách bạn có thể thay đổi thứ tự lồng nhau.

Thứ tự lồng ghép của các trường

Hãy xem xét ví dụ về dữ liệu bán hàng, nơi bạn đã đặt các trường theo thứ tự sau:

Như bạn có thể thấy, trong khu vực hàng có hai trường - nhân viên bán hàng và khu vực theo thứ tự đó. Thứ tự này của các trường được gọi là thứ tự lồng nhau tức là Nhân viên bán hàng trước và Khu vực tiếp theo.

Trong PivotTable, các giá trị trong các hàng sẽ được hiển thị dựa trên thứ tự này, như được đưa ra bên dưới.

Như bạn có thể quan sát, các giá trị của trường thứ hai theo thứ tự lồng nhau được nhúng dưới mỗi giá trị của trường đầu tiên.

Trong dữ liệu của bạn, mỗi nhân viên bán hàng chỉ được liên kết với một khu vực, trong khi hầu hết các khu vực được liên kết với nhiều nhân viên bán hàng. Do đó, có khả năng nếu bạn đảo ngược thứ tự lồng nhau, PivotTable của bạn trông sẽ có ý nghĩa hơn.

Thay đổi Thứ tự Tổ

Để thay đổi thứ tự lồng nhau của các trường trong một khu vực, chỉ cần nhấp vào trường và kéo trường đó đến vị trí bạn muốn.

Bấm vào trường Nhân viên bán hàng trong vùng ROWS và kéo nó xuống bên dưới trường Vùng. Do đó, bạn đã thay đổi thứ tự lồng vào - Khu vực đầu tiên và Nhân viên bán hàng tiếp theo, như sau -

PivotTable kết quả sẽ như dưới đây:

Bạn có thể quan sát rõ ràng rằng Bố cục có thứ tự lồng nhau - Khu vực và sau đó là Nhân viên bán hàng tạo ra một báo cáo tốt hơn và nhỏ gọn hơn so với báo cáo có thứ tự lồng nhau - Nhân viên bán hàng và sau đó là Khu vực.

Trong trường hợp Nhân viên bán hàng đại diện cho nhiều lĩnh vực và bạn cần tóm tắt doanh số của Nhân viên bán hàng, thì Bố cục trước đó sẽ là một lựa chọn tốt hơn.

Trong trang tính có chứa PivotTable, Ruy-băng sẽ chứa Công cụ PivotTable, với các Tab PHÂN TÍCH và THIẾT KẾ. Tab ANALYZE có một số lệnh cho phép bạn khám phá dữ liệu trong PivotTable. Các lệnh tab THIẾT KẾ sẽ hữu ích để cấu trúc PivotTable với các tùy chọn báo cáo và tùy chọn kiểu khác nhau.

Bạn sẽ học các lệnh ANALYZE trong chương này. Bạn sẽ học các lệnh THIẾT KẾ trong Chương - Báo cáo thẩm mỹ với PivotTables.

ANALYZE Lệnh

Các lệnh trên tab Ruy-băng của ANALYZE bao gồm:

Mở rộng và Thu gọn một trường.
Nhóm và Hủy nhóm Giá trị trường.
Cài đặt Trường Hoạt động.
Tùy chọn PivotTable.

Mở rộng và Thu gọn một trường

Nếu bạn có các trường lồng nhau trong PivotTable của mình, bạn có thể mở rộng và thu gọn một mục duy nhất hoặc bạn có thể mở rộng và thu gọn tất cả các mục của trường hiện hoạt.

Hãy xem xét PivotTable sau, trong đó bạn có trường Nhân viên bán hàng được lồng trong trường Khu vực.

Nhấp vào

biểu tượng ở bên trái của Đông. Mục Phía đông của trường Vùng sẽ sụp đổ.

Như bạn có thể quan sát, các mục khác - Bắc, Nam và Tây của Vùng hiện trường không bị thu gọn. Nếu bạn muốn thu gọn bất kỳ cái nào trong số chúng, hãy lặp lại các bước mà bạn đã làm cho Đông.

Nhấp vào

biểu tượng bên trái của Đông. Mục Phía đông của trường Vùng sẽ mở rộng.

Nếu bạn muốn thu gọn tất cả các mục của một trường cùng một lúc, hãy làm như sau:

Nhấp vào bất kỳ mục nào của trường - Khu vực.
Bấm vào tab PHÂN TÍCH trên Ruy-băng.
Bấm vào Trường thu gọn trong nhóm Trường hiện hoạt.

Tất cả các mục của trường Vùng sẽ bị thu gọn.

Nếu bạn muốn mở rộng tất cả các mục của một trường cùng một lúc, hãy làm như sau:

Nhấp vào bất kỳ mục nào của trường - Khu vực.
Bấm vào tab PHÂN TÍCH trên Ruy-băng.
Bấm Mở rộng Trường trong nhóm Trường Hoạt động.

Tất cả các mục của trường Vùng sẽ được mở rộng.

Nhóm và Hủy nhóm Giá trị trường

Bạn có thể nhóm và bỏ nhóm các giá trị trường để xác định nhóm của riêng bạn. Ví dụ: bạn có thể muốn biết dữ liệu kết hợp các vùng Đông và Bắc.

Chọn các mục Đông và Bắc của trường Khu vực trong PivotTable, cùng với các mục trường Nhân viên bán hàng lồng nhau.
Bấm vào tab PHÂN TÍCH trên Ruy-băng.
Nhấp vào Lựa chọn nhóm trong nhóm - Nhóm.

Các mục - Đông và Bắc sẽ được nhóm lại dưới tên Nhóm1. Ngoài ra, một miền Nam mới được tạo ra mà theo đó miền Nam được lồng vào nhau và một miền Tây mới được tạo ra mà theo đó là hướng Tây.

Bạn cũng có thể quan sát thấy một trường mới - Vùng2 được thêm vào danh sách Trường PivotTable, xuất hiện trong vùng ROWS.

Chọn các mục Nam và Tây của trường Vùng2 trong PivotTable, cùng với các mục trường Vùng và Người bán hàng lồng nhau.
Bấm vào tab PHÂN TÍCH trên Ruy-băng.
Nhấp vào Lựa chọn nhóm trong nhóm - Nhóm.

Các mục - Phía nam và phía Tây của trường Vùng sẽ được nhóm lại dưới tên Nhóm2.

Để hủy nhóm, hãy làm như sau:

Bấm vào Tên nhóm.
Nhấp vào tab PHÂN TÍCH.
Nhấp vào Ungroup trong nhóm - Nhóm.

Nhóm theo trường ngày

Hãy xem xét PivotTable sau, trong đó bạn có dữ liệu nhân viên được tổng hợp bởi Count of EmployeeID, hãy thuê người khôn ngoan và có chức danh khôn ngoan.

Giả sử bạn muốn nhóm dữ liệu này theo trường HireDate là trường Ngày thành năm và quý.

Bấm vào một mục Ngày trong PivotTable.
Bấm vào tab PHÂN TÍCH trên Ruy-băng.
Nhấp vào Trường Nhóm trong nhóm - Nhóm.

Hộp thoại Nhóm xuất hiện.

Đặt ngày cho - Bắt đầu lúc và Kết thúc lúc.
Chọn Quanh và Năm trong hộp bên dưới Theo. Để chọn / bỏ chọn nhiều mục, hãy giữ phím Ctrl.
Bấm OK.

Các giá trị của trường HireDate sẽ được nhóm lại thành Quarters, lồng trong Năm.

Nếu bạn muốn bỏ nhóm này, bạn có thể làm như được hiển thị trước đó, bằng cách nhấp vào Ungroup trong nhóm - Nhóm trên Ribbon.

Cài đặt trường giá trị hoạt động

Bạn có thể đặt tùy chọn trường bằng cách nhấp vào giá trị của trường đó. Hãy xem xét ví dụ về dữ liệu bán hàng mà chúng ta đã sử dụng trước đó trong chương này.

Giả sử bạn muốn đặt các tùy chọn cho trường Vùng.

Nhấp vào Đông. Trên Ruy-băng, trong nhóm Trường Hoạt động, trong hộp Trường Hoạt động, Vùng sẽ được hiển thị.
Bấm vào Field Settings. Hộp thoại Cài đặt Trường xuất hiện.

Bạn có thể đặt tùy chọn của mình cho trường - Khu vực.

Tùy chọn PivotTable

Bạn có thể đặt Tùy chọn PivotTable theo sở thích của mình.

Bấm vào PivotTable.
Nhấp vào tab PHÂN TÍCH.
Bấm Tùy chọn trong nhóm PivotTable.

Các PivotTable Optionshộp thoại xuất hiện. Bạn có thể đặt tùy chọn của mình trong hộp thoại.

Bạn có thể tóm tắt một PivotTable bằng cách đặt một trường vào vùng ∑ VALUES trong ngăn Tác vụ Trường PivotTable. Theo mặc định, Excel lấy tóm tắt là tổng các giá trị của trường trong vùng VALUES. Tuy nhiên, bạn có các kiểu tính toán khác, chẳng hạn như Đếm, Trung bình, Tối đa, Tối thiểu, v.v.

Trong chương này, bạn sẽ học cách đặt kiểu tính toán dựa trên cách bạn muốn tóm tắt dữ liệu trong PivotTable.

Tổng

Hãy xem xét PivotTable sau đây, trong đó bạn có dữ liệu bán hàng tóm tắt theo từng vùng, thông thái của nhân viên bán hàng và theo tháng.

Như bạn có thể quan sát, khi bạn kéo trường Số tiền đặt hàng đến vùng ∑ GIÁ TRỊ, nó được hiển thị dưới dạng Tổng của Số tiền đặt hàng, cho biết phép tính được lấy dưới dạng Tổng. Trong PivotTable, ở góc trên cùng bên trái, Tổng số tiền đặt hàng được hiển thị. Hơn nữa, cột Tổng tổng và hàng Tổng tổng được hiển thị cho trường tổng phụ tương ứng trong các hàng và cột tương ứng.

Cài đặt trường giá trị

Với Cài đặt Trường Giá trị, bạn có thể đặt kiểu tính toán trong PivotTable của mình. Bạn cũng có thể quyết định cách bạn muốn hiển thị các giá trị của mình.

Nhấp vào Tổng số tiền đặt hàng trong khu vực ∑ VALUES.
Chọn Cài đặt Trường Giá trị từ danh sách thả xuống.

Hộp thoại Cài đặt Trường Giá trị xuất hiện.

Tên nguồn là trường và Tên tùy chỉnh là Tổng trường. Loại tính toán là Tổng. Nhấn vàoShow Values As chuyển hướng.

Trong hộp Hiển thị Giá trị Như, No Calculationđược hiển thị. Nhấn vàoShow Values Ascái hộp. Bạn có thể tìm thấy một số cách để hiển thị tổng giá trị của mình.

% trong tổng số

Bạn có thể hiển thị các giá trị trong PivotTable dưới dạng% của Tổng tổng.

Trong hộp Tên tùy chỉnh, nhập% của Tổng số.
Bấm vào hộp Hiển thị Giá trị Dưới dạng.
Nhấp vào% của Tổng số trong danh sách thả xuống. Bấm OK.

PivotTable tóm tắt các giá trị theo% của Tổng số lớn.

Như bạn có thể quan sát, Tổng Số tiền Đơn hàng ở góc trên cùng bên trái của PivotTable và trong vùng ∑ GIÁ TRỊ trong ngăn Trường PivotTable được thay đổi thành Tên Tùy chỉnh mới -% Tổng Tổng.

Bấm vào tiêu đề của cột Grand Total.
Nhập% của Tổng số vào thanh công thức. Cả tiêu đề Cột và Hàng sẽ thay đổi thành% của Tổng Tổng.

% tổng số cột

Giả sử bạn muốn tóm tắt các giá trị dưới dạng% của tổng mỗi tháng.

Nhấp vào Tổng số tiền đặt hàng trong khu vực ∑ VALUES.
Chọn Cài đặt Trường Giá trị từ danh sách thả xuống. Hộp thoại Cài đặt Trường Giá trị xuất hiện.
Trong hộp Tên tùy chỉnh, nhập% Tổng số Tháng.
Nhấp vào hộp Hiển thị giá trị dưới dạng.
Chọn% Tổng số Cột từ danh sách thả xuống.
Bấm OK.

PivotTable tóm tắt các giá trị dưới dạng% của Tổng số Cột. Trong cột Tháng, bạn sẽ tìm thấy các giá trị là% của tổng số tháng cụ thể.

Bấm vào tiêu đề của cột Grand Total.
Nhập% của Tổng số Cột vào thanh công thức. Cả tiêu đề Cột và Hàng sẽ thay đổi thành% của Tổng số Cột.

% tổng số hàng

Bạn có thể tóm tắt các giá trị dưới dạng% tổng số khu vực và% tổng số nhân viên bán hàng, bằng cách chọn% Tổng số Hàng trong hộp Hiển thị Giá trị Dưới dạng trong hộp thoại Cài đặt Trường Giá trị.

Đếm

Giả sử bạn muốn tóm tắt các giá trị theo số khu vực Tài khoản khôn ngoan, nhân viên bán hàng khôn ngoan và khôn ngoan theo tháng.

Bỏ chọn Số lượng đặt hàng.
Kéo Tài khoản vào khu vực ∑ VALUES. Tổng tài khoản sẽ được hiển thị trong khu vực ∑ VALUES.
Nhấp vào Tổng tài khoản.
Chọn Cài đặt Trường Giá trị từ danh sách thả xuống. Hộp thoại Cài đặt Trường Giá trị xuất hiện.
Trong trường Tổng hợp giá trị theo hộp, hãy chọn Đếm. Tên Tuỳ chỉnh thay đổi thành Số lượng Tài khoản.
Bấm OK.

Số lượng tài khoản sẽ được hiển thị như hình dưới đây -

Trung bình cộng

Giả sử bạn muốn tóm tắt PivotTable theo giá trị trung bình của khu vực Số lượng đặt hàng khôn ngoan, nhân viên bán hàng khôn ngoan và khôn ngoan theo tháng.

Bỏ chọn Tài khoản.
Kéo Số lượng đặt hàng vào vùng ∑ VALUES. Tổng số tiền đặt hàng sẽ được hiển thị trong khu vực ∑ VALUES.
Nhấp vào Tổng số tiền đặt hàng.
Nhấp vào Cài đặt Trường Giá trị trong danh sách thả xuống. Hộp thoại Cài đặt Trường Giá trị xuất hiện.
Trong trường Tóm tắt giá trị theo hộp, bấm vào Trung bình. Tên Tùy chỉnh thay đổi thành Trung bình của Số tiền Đặt hàng.
Bấm OK.

Mức trung bình sẽ được hiển thị như hình dưới đây -

Bạn phải đặt định dạng số của các giá trị trong PivotTable để làm cho nó dễ thấy hơn.

Nhấp vào Trung bình của Số tiền Đặt hàng trong khu vực ∑ VALUES.
Nhấp vào Cài đặt Trường Giá trị trong danh sách thả xuống. Hộp thoại Cài đặt Trường Giá trị xuất hiện.
Bấm vào nút Định dạng Số.

Hộp thoại Định dạng Ô xuất hiện.

Nhấp vào Số trong Danh mục.
Nhập 2 vào hộp Vị trí thập phân và nhấp vào OK.

Các giá trị PivotTable sẽ được định dạng thành số có hai chữ số thập phân.

Bấm vào tiêu đề của cột Grand Total.
Nhập Số tiền đặt hàng trung bình vào thanh công thức. Cả tiêu đề Cột và Hàng sẽ thay đổi thành Số lượng Đơn hàng Trung bình.

Max

Giả sử bạn muốn tóm tắt PivotTable theo các giá trị tối đa của Số tiền đặt hàng theo khu vực, theo nhân viên bán hàng và theo tháng.

Nhấp vào Tổng số tiền đặt hàng.
Chọn Cài đặt Trường Giá trị từ danh sách thả xuống. Hộp thoại Cài đặt Trường Giá trị xuất hiện.
Trong trường Tóm tắt giá trị theo hộp, hãy bấm Giá trị tối đa. Tên tùy chỉnh thay đổi thành Số tiền đặt hàng tối đa.

PivotTable sẽ hiển thị vùng giá trị tối đa khôn ngoan, nhân viên bán hàng khôn ngoan và khôn ngoan theo tháng.

Bấm vào tiêu đề cột Grand Total.
Nhập Số lượng Đơn hàng Tối đa vào thanh công thức. Cả tiêu đề Cột và Hàng sẽ thay đổi thành Số lượng Đơn hàng Tối đa.

Min

Giả sử bạn muốn tóm tắt PivotTable theo các giá trị tối thiểu của vùng Số lượng đặt hàng khôn ngoan, nhân viên bán hàng khôn ngoan và khôn ngoan theo tháng.

Nhấp vào Tổng số tiền đặt hàng.
Nhấp vào Cài đặt Trường Giá trị trong danh sách thả xuống. Hộp thoại Cài đặt Trường Giá trị xuất hiện.
Trong trường Tóm tắt giá trị theo hộp, bấm Min. Tên tùy chỉnh thay đổi thành Số tiền đặt hàng tối thiểu.

PivotTable sẽ hiển thị vùng giá trị tối thiểu khôn ngoan, nhân viên bán hàng khôn ngoan và khôn ngoan theo tháng.

Bấm vào tiêu đề của cột Grand Total.
Nhập Số lượng Đơn hàng Tối thiểu vào thanh công thức. Cả hai tiêu đề Cột và Hàng sẽ thay đổi thành Số lượng Đơn hàng Tối thiểu.

Bạn đã học cách tóm tắt dữ liệu bằng PivotTable. Dữ liệu mà PivotTable dựa trên đó có thể được cập nhật theo định kỳ hoặc khi xảy ra sự kiện. Hơn nữa, bạn cũng có thể yêu cầu thay đổi Bố cục PivotTable cho các báo cáo khác nhau.

Trong chương này, bạn sẽ tìm hiểu các cách khác nhau để cập nhật Bố cục và / hoặc làm mới dữ liệu trong PivotTable.

Cập nhật Bố cục PivotTable

Bạn có thể quyết định xem PivotTable của mình sẽ được cập nhật bất cứ khi nào bạn thực hiện thay đổi đối với bố cục hay nó sẽ được cập nhật bởi một trình kích hoạt riêng biệt.

Như bạn đã học trước đó, trong ngăn tác vụ Trường PivotTable, ở phía dưới cùng, bạn sẽ tìm thấy hộp kiểm cho Trì hoãn Cập nhật Bố cục. Theo mặc định, nó không được chọn, có nghĩa là Bố cục PivotTable được cập nhật ngay sau khi bạn thực hiện các thay đổi trong các vùng PivotTable.

Kiểm tra tùy chọn - Defer Layout Update.

Nút CẬP NHẬT bên cạnh sẽ được bật. Nếu bạn thực hiện bất kỳ thay đổi nào đối với các khu vực PivotTable, các thay đổi sẽ chỉ được phản ánh sau khi bạn nhấp vào nút CẬP NHẬT.

Làm mới dữ liệu PivotTable

Khi dữ liệu của PivotTable được thay đổi trong nguồn của nó, dữ liệu tương tự có thể được phản ánh trong PivotTable bằng cách làm mới nó.

Bấm vào PivotTable.
Bấm vào tab PHÂN TÍCH trên Ruy-băng.
Nhấp vào Làm mới trong nhóm Dữ liệu.

Có các tùy chọn khác nhau để làm mới dữ liệu trong danh sách thả xuống -

Refresh - Để lấy dữ liệu mới nhất từ nguồn kết nối với ô hoạt động.
Refresh All - Để nhận dữ liệu mới nhất bằng cách làm mới tất cả các nguồn trong sổ làm việc.
Connection Properties - Để đặt các thuộc tính làm mới cho các kết nối sổ làm việc.

Thay đổi dữ liệu nguồn của PivotTable

Bạn có thể thay đổi phạm vi dữ liệu nguồn của PivotTable. Ví dụ: bạn có thể mở rộng dữ liệu nguồn để bao gồm nhiều hàng dữ liệu hơn.

Tuy nhiên, nếu dữ liệu nguồn đã bị thay đổi đáng kể, chẳng hạn như có nhiều hoặc ít cột hơn, hãy cân nhắc tạo PivotTable mới.

Bấm vào PivotTable. PIVOTTABLE TOOLS xuất hiện trên Ribbon.
Nhấp vào tab PHÂN TÍCH.
Bấm Thay đổi Nguồn Dữ liệu trong nhóm Dữ liệu.

Chọn Thay đổi nguồn dữ liệu từ danh sách thả xuống.

Hộp thoại Thay đổi Nguồn Dữ liệu PivotTable xuất hiện và Nguồn Dữ liệu hiện tại sẽ được đánh dấu.

Chọn Bảng hoặc Phạm vi bạn muốn đưa vào Hộp Bảng / Phạm vi bên dưới Chọn một Bảng hoặc Phạm vi. Bấm OK.

Nguồn dữ liệu cho PivotTable sẽ được thay đổi thành Bảng / Phạm vi dữ liệu đã chọn.

Thay đổi thành nguồn dữ liệu bên ngoài

Nếu bạn muốn thay đổi nguồn dữ liệu cho PivotTable của mình là nguồn bên ngoài, tốt nhất bạn nên tạo một PivotTable mới. Tuy nhiên, nếu vị trí của nguồn dữ liệu bên ngoài của bạn bị thay đổi, ví dụ: tên cơ sở dữ liệu SQL Server của bạn giống nhau, nhưng nó đã được chuyển đến một máy chủ khác hoặc cơ sở dữ liệu Access của bạn đã được chuyển sang một mạng chia sẻ khác, bạn có thể thay đổi kết nối dữ liệu hiện tại của bạn để phản ánh giống nhau.

Bấm vào PivotTable.
Bấm vào tab PHÂN TÍCH trên Ruy-băng.
Nhấp chuột Change Data Sourcetrong nhóm Dữ liệu. CácChange PivotTable Data Source hộp thoại xuất hiện.
Nhấn vào Choose Connection cái nút.

Các Existing Connections hộp thoại xuất hiện.

Chọn Tất cả kết nối trong hộp Hiển thị. Tất cả các Kết nối trong Sổ làm việc của bạn sẽ được hiển thị.
Nhấn vào Browse for More cái nút.

Các Select Data Source cửa sổ xuất hiện.

Bấm vào nút Nguồn mới.
Xem qua các bước của trình hướng dẫn kết nối dữ liệu.

Nếu nguồn dữ liệu của bạn nằm trong sổ làm việc Excel khác, hãy làm như sau:

Bấm vào hộp Tên tệp.
Chọn tên tệp sổ làm việc.

Xóa PivotTable

Bạn có thể xóa PivotTable như sau:

Bấm vào PivotTable.
Bấm vào tab PHÂN TÍCH trên Ruy-băng.
Nhấp vào Chọn trong nhóm Hành động.

Lựa chọn Entire PivotTabletừ danh sách thả xuống. Toàn bộ PivotTable sẽ được chọn.

Nhấn phím Delete. PivotTable sẽ bị xóa.

Nếu PivotTable nằm trên một trang tính riêng biệt, bạn cũng có thể xóa PivotTable bằng cách xóa toàn bộ trang tính.

Nhấp chuột phải vào tab trang tính và chọn Delete từ danh sách thả xuống.

Toàn bộ trang tính cùng với PivotTable sẽ bị xóa.

Công dụng chính của PivotTable là báo cáo. Khi bạn đã tạo PivotTable, khám phá dữ liệu bằng cách sắp xếp và sắp xếp lại các trường trong các hàng và cột của nó, bạn sẽ sẵn sàng trình bày dữ liệu cho nhiều đối tượng. Với các bộ lọc, các tóm tắt khác nhau, tập trung vào dữ liệu cụ thể, bạn sẽ có thể tạo một số báo cáo bắt buộc dựa trên một PivotTable duy nhất.

Vì báo cáo PivotTable có tính tương tác, bạn có thể nhanh chóng thực hiện các thay đổi cần thiết để làm nổi bật các kết quả cụ thể, chẳng hạn như xu hướng dữ liệu, tóm tắt dữ liệu, v.v. trong khi trình bày báo cáo đó. Bạn cũng có thể cung cấp các dấu hiệu trực quan như bộ lọc báo cáo, bộ lọc, dòng thời gian, PivotCharts, v.v. cho người nhận để họ có thể hình dung chi tiết họ muốn.

Trong chương này, bạn sẽ tìm hiểu các cách khác nhau để làm cho báo cáo PivotTable của bạn trở nên hấp dẫn với các dấu hiệu trực quan cho phép khám phá nhanh dữ liệu.

Cấu trúc phân cấp

Bạn đã học cách lồng các trường để tạo thành một hệ thống phân cấp, trong Chương - Lồng trong PivotTable trong hướng dẫn này. Bạn cũng đã học cách nhóm / hủy nhóm dữ liệu trong PivotTable trong Chương - Sử dụng công cụ PivotTable. Chúng tôi sẽ lấy một số ví dụ để chỉ cho bạn cách tạo báo cáo PivotTable tương tác với cấu trúc phân cấp.

Nếu bạn có cấu trúc dựng sẵn cho các trường trong dữ liệu của mình, chẳng hạn như Năm-Quý-Tháng, việc lồng các trường để tạo thành hệ thống phân cấp sẽ cho phép bạn nhanh chóng mở rộng / thu gọn các trường để xem các giá trị tóm tắt ở cấp độ yêu cầu.

Ví dụ: giả sử bạn có dữ liệu bán hàng cho năm tài chính 2015-16 cho các khu vực - Đông, Bắc, Nam và Tây, như được cung cấp bên dưới.

Tạo PivotTable như hình dưới đây.

Như bạn có thể quan sát, đây là một cách toàn diện để báo cáo dữ liệu bằng cách sử dụng các trường lồng nhau dưới dạng phân cấp. Nếu bạn chỉ muốn hiển thị kết quả ở cấp Phần tư, bạn có thể nhanh chóng thu gọn trường Phần tư.

Giả sử bạn có trường Ngày trong dữ liệu của mình như hình dưới đây.

Trong trường hợp này, bạn có thể nhóm dữ liệu theo trường Ngày như sau:

Tạo PivotTable.

Như bạn có thể quan sát, PivotTable này không thuận tiện để đánh dấu dữ liệu quan trọng.

Nhóm trường PivotTable theo Ngày. (Bạn đã học cách nhóm trong Chương - Khám phá dữ liệu với các công cụ PivotTable trong hướng dẫn này).
Đặt trường Người bán hàng trong vùng Bộ lọc.
Lọc nhãn Cột thành Vùng phía Đông.

Bộ lọc Báo cáo

Giả sử bạn muốn có một báo cáo cho từng Nhân viên bán hàng riêng biệt. Bạn có thể thực hiện như sau:

Đảm bảo rằng bạn có trường Người bán hàng trong vùng Bộ lọc.
Bấm vào PivotTable.
Bấm vào tab PHÂN TÍCH trên Ruy-băng.
Bấm vào mũi tên bên cạnh Tùy chọn trong nhóm PivotTable.
Chọn Hiển thị các Trang Bộ lọc Báo cáo từ danh sách thả xuống.

Các Show Report Filter Pageshộp thoại xuất hiện. Chọn trường Nhân viên bán hàng và nhấp vào OK.

Một trang tính riêng biệt cho từng giá trị của trường Người bán hàng được tạo, với PivotTable được lọc theo giá trị đó.

Trang tính sẽ được đặt tên theo giá trị của trường, trường này hiển thị trên tab của trang tính.

Máy thái

Một tính năng phức tạp khác mà bạn có trong PivotTables là Slicer có thể được sử dụng để lọc các trường một cách trực quan.

Bấm vào PivotTable.
Nhấp vào tab PHÂN TÍCH.
Nhấp vào Chèn trình cắt trong nhóm Bộ lọc.
Nhấp chuột Order Date, Quarters and Yearstrong hộp thoại Chèn lát cắt. Ba lát - Ngày, tháng và năm đặt hàng sẽ được tạo.
Điều chỉnh kích thước của máy thái, thêm nhiều cột hơn cho các nút trên máy thái.
Cũng tạo các Slice cho các trường Nhân viên bán hàng và Vùng.
Chọn Kiểu Slicer để các trường ngày được nhóm thành một màu và hai trường còn lại có các màu khác nhau.
Bỏ chọn Gridlines.

Như bạn có thể thấy, bạn không chỉ có một báo cáo tương tác mà còn có một báo cáo hấp dẫn, có thể hiểu một cách dễ dàng.

Dòng thời gian trong PivotTable

Khi bạn có trường Ngày trong PivotTable, chèn Dòng thời gian cũng là một tùy chọn để tạo báo cáo thẩm mỹ.

Tạo PivotTable với Nhân viên bán hàng trong khu vực ROWS và Khu vực trong khu vực COLUMNS.
Chèn Dòng thời gian cho trường Ngày đặt hàng.
Lọc Dòng thời gian để hiển thị dữ liệu 5 tháng, từ tháng 11 năm 2015 đến tháng 3 năm 2016.

Lệnh THIẾT KẾ

Các PIVOTTABLE TOOLS - DESIGN các lệnh trên Ribbon cung cấp cho bạn các tùy chọn để định dạng PivotTable, bao gồm các tùy chọn sau:

Layout
Tùy chọn kiểu PivotTable
Kiểu PivotTable

Bố trí

Bạn có thể có Bố cục PivotTable dựa trên tùy chọn của mình cho những điều sau:

Subtotals
Tổng số
Bố cục Báo cáo
Hàng trống

PivotTable Layout – Subtotals

Bạn có một tùy chọn có hiển thị hay không Subtotalshay không. Theo mặc định, Tổng phụ được hiển thị ở đầu nhóm.

Như bạn có thể quan sát nhóm được đánh dấu - Đông, tổng phụ nằm ở đầu nhóm. Bạn có thể thay đổi vị trí của tổng phụ như sau:

Bấm vào PivotTable.
Bấm vào tab THIẾT KẾ trên Ruy-băng.
Bấm Tổng phụ trong nhóm Tùy chọn Bố cục.
Nhấp vào Hiển thị tất cả Tổng phụ ở cuối Nhóm.

Tổng phụ bây giờ sẽ xuất hiện ở cuối mỗi nhóm.

Nếu bạn không phải báo cáo Tổng phụ, bạn có thể chọn - Không Hiển thị Tổng phụ.

Tổng số

Bạn có thể chọn hiển thị Tổng cộng hoặc không. Bạn có bốn kết hợp có thể -

Tắt cho Hàng và Cột
Bật cho Hàng và Cột
Chỉ bật cho hàng
Chỉ bật cho các cột

Theo mặc định, nó là kết hợp thứ hai - Bật cho Hàng và Cột.

Bố cục Báo cáo

Bạn có thể chọn từ một số Bố cục Báo cáo, bố cục phù hợp nhất với dữ liệu của bạn.

Hình thức nhỏ gọn.
Mẫu phác thảo.
Dạng bảng.

Bạn cũng có thể chọn lặp lại tất cả các nhãn mục hay không, trong trường hợp có nhiều lần xuất hiện.

Bố cục Báo cáo mặc định là biểu mẫu Thu gọn mà bạn đã quen thuộc.

Compact Form

Biểu mẫu Thu gọn tối ưu hóa PivotTable để dễ đọc. Hai biểu mẫu khác cũng hiển thị tiêu đề trường.

Bấm vào Show trong Mẫu phác thảo.

Nhấp chuột Show trong Biểu mẫu Bảng.

Hãy xem xét Bố cục PivotTable sau, trong đó trường Tháng được lồng trong trường Vùng -

Như bạn có thể quan sát, nhãn Tháng được lặp lại và đây là nhãn mặc định.

Nhấp vào Không lặp lại nhãn mục. Các nhãn Tháng sẽ chỉ được hiển thị một lần và PivotTable trông rõ ràng.

Hàng trống

Để làm cho Báo cáo PivotTable của bạn trở nên khác biệt hơn, bạn có thể chèn một dòng trống sau mỗi mục. Bạn có thể xóa các Dòng trống này bất cứ lúc nào sau đó.

Nhấp chuột Insert Blank Line after Each Item.

Tùy chọn kiểu PivotTable

Bạn có các Tùy chọn Kiểu PivotTable sau -

Tiêu đề hàng
Tiêu đề cột
Các hàng có dải
Cột có dải

Theo mặc định, các hộp dành cho Tiêu đề Hàng và Tiêu đề Cột được chọn. Các tùy chọn này để hiển thị định dạng đặc biệt cho hàng đầu tiên và cột đầu tiên tương ứng. Kiểm tra hộpBanded Rows.

Chọn hộp Cột có dải.

Kiểu PivotTable

Bạn có thể chọn một số Kiểu PivotTable. Chọn một trong những phù hợp với báo cáo của bạn. Ví dụ: nếu bạn chọn Pivot Style Dark 5, bạn sẽ nhận được kiểu sau cho PivotTable.