Phân tích dữ liệu Excel - PivotTables

Phân tích dữ liệu trên một tập hợp dữ liệu lớn thường khá cần thiết và quan trọng. Nó liên quan đến việc tóm tắt dữ liệu, thu được các giá trị cần thiết và trình bày kết quả.

Excel cung cấp PivotTable để cho phép bạn tóm tắt hàng nghìn giá trị dữ liệu một cách dễ dàng và nhanh chóng để thu được kết quả cần thiết.

Hãy xem xét bảng dữ liệu bán hàng sau đây. Từ dữ liệu này, bạn có thể phải tóm tắt tổng doanh số bán hàng của khu vực khôn ngoan, theo tháng hoặc nhân viên bán hàng khôn ngoan. Cách dễ dàng để xử lý các tác vụ này là tạo PivotTable mà bạn có thể sửa đổi động để tóm tắt kết quả theo cách bạn muốn.

Tạo PivotTable

Để tạo PivotTable, hãy đảm bảo hàng đầu tiên có tiêu đề.

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

Như bạn có thể thấy trong hộp thoại, bạn có thể sử dụng 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.

Trong Hộp Bảng / Phạm vi, hãy nhập tên bảng.
Bấm Trang tính Mới để cho Excel biết nơi giữ PivotTable.
Bấm OK.

PivotTable trống và danh sách trường PivotTable xuất hiện.

PivotTables được đề xuất

Trong trường hợp bạn chưa quen với PivotTables hoặc bạn không biết chọn trường nào từ dữ liệu, bạn có thể sử dụng Recommended PivotTables mà Excel cung cấp.

Nhấp vào bảng dữ liệu.
Bấm vào tab CHÈN.
Bấm vào 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.

Bấm OK. PivotTable đã chọn sẽ xuất hiện trên một trang tính mới. Bạn có thể quan sát các trường PivotTable đã được chọn trong danh sách trường PivotTable.

Trường PivotTable

Các tiêu đề trong bảng dữ liệu của bạn sẽ xuất hiện dưới dạng các trường trong PivotTable.

Bạn có thể chọn / bỏ chọn chúng để thay đổi ngay lập tức PivotTable của mình để chỉ hiển thị thông tin bạn muốn và theo cách bạn muốn. Ví dụ: nếu bạn muốn hiển thị thông tin tài khoản thay vì thông tin số tiền đặt hàng, hãy bỏ chọn Số tiền đặt hàng và chọn Tài khoản.

Khu vực PivotTable

Bạn thậm chí có thể thay đổi Bố cục của PivotTable ngay lập tức. Bạn có thể sử dụng Vùng PivotTable để thực hiện việc này.

Trong các vùng PivotTable, bạn có thể chọn -

Những trường nào để hiển thị dưới dạng hàng
Những trường nào để hiển thị dưới dạng cột
Cách tóm tắt dữ liệu của bạn
Bộ lọc cho bất kỳ trường nào
Khi nào cập nhật Bố cục PivotTable của bạn
- Bạn có thể cập nhật nó ngay lập tức khi bạn kéo các trường qua các khu vực hoặc
- Bạn có thể trì hoãn cập nhật và chỉ cập nhật khi bạn nhấp vào CẬP NHẬT

Bản cập nhật tức thì giúp bạn tìm hiểu các Bố cục khác nhau và chọn một Bố cục phù hợp với yêu cầu báo cáo của bạn.

Bạn chỉ có thể kéo các trường qua các vùng này và quan sát bố cục PivotTable khi bạn thực hiện.

Lồng trong PivotTable

Nếu bạn có nhiều hơn một trường trong bất kỳ khu vực nào, thì việc lồng nhau sẽ xảy ra theo thứ tự bạn đặt các trường trong khu vực đó. Bạn có thể thay đổi thứ tự bằng cách kéo các trường và quan sát cách lồng nhau thay đổi. Trong các tùy chọn bố cục trên, bạn có thể thấy rằng

Tháng ở dạng cột.
Khu vực và nhân viên bán hàng trong các hàng theo thứ tự đó. tức là các giá trị của nhân viên bán hàng được lồng vào các giá trị của vùng.
Tổng kết là bằng Tổng số tiền đặt hàng.
Không có bộ lọc nào được chọn.

PivotTable kết quả như sau:

Trong Vùng PivotTable, trong các hàng, hãy bấm vào vùng và kéo vùng đó bên dưới nhân viên bán hàng sao cho vùng đó trông như sau:

Thứ tự lồng nhau thay đổi và PivotTable kết quả như sau:

Note- Bạn có thể quan sát rõ rằng bố cục có thứ tự lồng nhau - Vùng 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ố cục có thứ tự lồng nhau - Nhân viên bán hàng và sau đó là Vùng. Trong trường hợp Nhân viên bán hàng đại diện cho nhiều khu 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 thứ hai sẽ là một lựa chọn tốt hơn.

Bộ lọc

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 sẽ được đẩy xuống để tạo khoảng trống cho Bộ lọc.

Bạn có thể thấy rằng -

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 Người bán hàng khôn ngoan xuất hiện trong cột Tổng số tiền
- Tổng đơn đặt hàng Số tiền theo tháng xuất hiện trong hàng Tổng tổng

Nhấp 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 vùng trường sẽ xuất hiện.

Kiểm tra tùy chọn Select Multiple Items. Hộp kiểm xuất hiện cho tất cả các giá trị.
Chọn South and West và bỏ chọn các giá trị khác và nhấp vào OK.

Dữ liệu chỉ liên quan đến Khu vực phía Nam và phía Tây sẽ được tóm tắt như thể hiện trong ảnh chụp màn hình dưới đây -

Bạn có thể thấy rằng bên cạnh Vùng lọc, Multiple Itemsđược hiển thị, cho biết rằng bạn đã chọn nhiều hơn một mục. Tuy nhiên, không biết có bao nhiêu mục và / hoặc mục nào được chọn 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.

Máy thái

Bạn có thể sử dụng Slicer để rõ ràng hơn về các mục dữ liệu đã được lọc.

Nhấp vào PHÂN TÍCH bên dưới CÔNG CỤ CÓ THỂ TÍCH HỢP trên Ruy-băng.
Nhấp vào Chèn trình cắt trong nhóm Bộ lọc. Hộp Chèn lát xuất hiện. Nó chứa tất cả các trường từ dữ liệu của bạn.
Chọn các trường 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.

Chọn Nam và Tây trong Bộ cắt cho Khu vực.
Chọn tháng 2 và tháng 3 trong Bộ cắt cho tháng.
Giữ phím Ctrl trong khi chọn nhiều giá trị trong Slicer.

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ị.

Tổng hợp các giá trị bằng các phép tính khác

Trong các ví dụ cho đến nay, bạn đã thấy các giá trị tóm tắt bằng Sum. Tuy nhiên, bạn cũng có thể sử dụng các phép tính khác nếu cần.

Trong danh sách trường PivotTable

Chọn Tài khoản Trường.
Bỏ chọn Số lượng Thứ tự Trường.

Kéo trường Tài khoản vào vùng Giá trị Tóm tắt. Theo mặc định, Tổng tài khoản sẽ được hiển thị.
Nhấp vào mũi tên ở bên phải của hộp.
Trong menu thả xuống xuất hiện, nhấp vào Cài đặt trường giá trị.

Hộp Cài đặt Trường Giá trị xuất hiện. Một số kiểu tính toán xuất hiện dưới dạng danh sách trong trường giá trị Tóm tắt bằng cách:

Chọn Đếm trong danh sách.
Tên tùy chỉnh tự động thay đổi thành Số lượng tài khoản. Bấm OK.

PivotTable tóm tắt các giá trị Tài khoản theo Số lượng.

Công cụ PivotTable

Làm theo các bước dưới đây để tìm hiểu cách sử dụng Công cụ PivotTable.

Chọn PivotTable.

Các công cụ PivotTable sau xuất hiện trên ruy-băng:

ANALYZE
DESIGN

PHÂN TÍCH

Một số ANALYZE Lệnh ruy-băng là -

Đặt Tùy chọn PivotTable
Cài đặt trường giá trị cho trường đã chọn
Mở rộng trường
Thu gọn trường
Chèn Slicer
Chèn dòng thời gian
Làm mới dữ liệu
Thay đổi nguồn dữ liệu
Di chuyển PivotTable
Giải thứ tự (Nếu có nhiều phép tính hơn)
PivotChart

THIẾT KẾ

Một số DESIGN Lệnh ruy-băng là -

Bố cục PivotTable
- Tùy chọn cho Tổng phụ
- Tùy chọn cho Tổng số
- Biểu mẫu bố cục báo cáo
- Tùy chọn cho hàng trống
Tùy chọn kiểu PivotTable
Kiểu PivotTable

Trường Mở rộng và Thu gọn

Bạn có thể mở rộng hoặc thu gọn tất cả các mục của trường đã chọn theo hai cách:

Bằng cách chọn biểu tượng

hoặc

bên trái trường đã chọn.
Bằng cách nhấp vào Trường Mở rộng hoặc Trường Thu gọn trên Ruy băng PHÂN TÍCH.

Bằng cách chọn biểu tượng Mở rộng

hoặc biểu tượng Thu gọn

ở bên trái của trường đã chọn

Chọn ô chứa Đông trong PivotTable.
Nhấp vào biểu tượng Thu gọn

ở bên trái phía Đông.

Tất cả các mục dưới Đông sẽ bị thu gọn. Biểu tượng Thu gọn

ở bên trái của Đông chuyển thành biểu tượng Mở rộng

Bạn có thể quan sát thấy chỉ có các hạng mục bên dưới East là bị sập. Phần còn lại của các mục PivotTable như chúng vốn có.

Nhấp vào biểu tượng Mở rộng

ở bên trái của Đông. Tất cả các mục bên dưới Đông sẽ được hiển thị.

Sử dụng ANALYZE trên Ribbon

Bạn có thể thu gọn hoặc mở rộng tất cả các mục trong PivotTable cùng một lúc bằng các lệnh Mở rộng Trường và Thu gọn Trường trên Ruy-băng.

Bấm vào ô chứa Đông trong PivotTable.
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 East trong PivotTable sẽ thu gọn.

Bấm Mở rộng Trường trong nhóm Trường Hoạt động.

Tất cả các mục sẽ được hiển thị.

Báo cáo kiểu trình bày

Bạn có thể chọn kiểu trình bày cho PivotTable của mình như khi bạn đưa nó vào dưới dạng báo cáo. Chọn một kiểu phù hợp với phần còn lại của bản trình bày hoặc báo cáo của bạn. Tuy nhiên, đừng quá nhàm chán với các phong cách bởi vì một báo cáo có tác động đến việc hiển thị kết quả luôn tốt hơn một báo cáo sặc sỡ, không làm nổi bật các điểm dữ liệu quan trọng.

Nhấp vào Đông trong PivotTable.
Nhấp vào PHÂN TÍCH.
Bấm Cài đặt Trường trong nhóm Trường Hoạt động. Hộp thoại Cài đặt Trường xuất hiện.
Nhấp vào tab Bố cục & In.
Chọn Chèn dòng trống sau mỗi nhãn mục.

Các hàng trống sẽ được hiển thị sau mỗi giá trị của trường Vùng.

Bạn có thể chèn các hàng trống từ DESIGN tab cũng được.

Nhấp vào tab THIẾT KẾ.
Nhấp vào Bố cục Báo cáo trong nhóm Bố cục.
Chọn Hiển thị trong Biểu mẫu Dàn ý trong danh sách thả xuống.

Di chuột qua các Kiểu PivotTable. Một bản xem trước của kiểu đặt chuột sẽ xuất hiện.
Chọn Kiểu phù hợp với báo cáo của bạn.

PivotTable trong Biểu mẫu Dàn ý với Kiểu đã chọn sẽ được hiển thị.

Dòng thời gian trong PivotTables

Để hiểu cách sử dụng Dòng thời gian, hãy xem xét ví dụ sau, trong đó dữ liệu bán hàng của các mặt hàng khác nhau được cung cấp cho nhân viên bán hàng khôn ngoan và thông thái về vị trí. Có tổng cộng 1891 hàng dữ liệu.