Cách Dùng Pivot Table Nâng Cao / TOP 10 # Xem Nhiều Nhất & Mới Nhất 3/2023 # Top View

Pivot table với nhiều cấp độ trong Excel

Hãy nhớ rằng tập dữ liệu của chúng tôi bao gồm 213 bản ghi và 6 trường. Mã giao dịch, Mã cổ phiếu, Ngành, Giá trị, Ngày và Quốc gia.

Đầu tiên, chèn một PivotTable. Tiếp theo, kéo các trường sau vào các khu vực khác nhau.

1. Trường Ngành và trường Quốc gia cho khu vực Rows.

2. Trường Giá trị đến khu vực Values.

Thêm nhiều trường cho khu vực Values trong PivotTable

Đầu tiên, chèn một bảng trụ. Tiếp theo, kéo các trường sau vào các khu vực khác nhau.

1. Trường Quốc gia đến khu vực Rows.

2. Trường Giá trị đến khu vực Values (2x).

Lưu ý: nếu bạn kéo trường Số tiền vào khu vực Giá trị lần thứ hai, Excel cũng sẽ điền vào khu vực Cột.

Kết quả:

3. Tiếp theo, nhấp vào bất kỳ ô nào trong cột Sum of Giá trị2

4. Nhấp chuột phải và nhấp vào Value Field Settings .

5. Nhập tỷ lệ phần trăm cho tên tùy chỉnh.

6. Trên tab Show Values As , chọn % of Grand Total .

7. Nhấp vào OK.

Kết quả:

Thêm nhiều trường cho khu vực Filters

Đầu tiên, chèn một PivotTable. Tiếp theo, kéo các trường sau vào các khu vực khác nhau.

1. Mã giao dịch đến khu vực Rows.

2. Trường Giá trị đến khu vực Values.

3. Trường Quốc gia và trường Mã cố phiếu cho khu vực Filters.

4. Tiếp theo, chọn United Kingdom từ mũi tên thả xuống bộ lọc đầu tiên và GIL từ mũi tên thả xuống bộ lọc thứ hai.

Bảng Pivot hiển thị tất cả các giao dịch của mã cổ phiếu ‘GIL’ đến từ United Kingdom.

Các trường/mục tính toán

Ví dụ này dạy cho bạn cách chèn trường được tính toán hoặc mục được tính toán vào bảng xoay vòng.

Trường tính toán

Một trường được tính toán sử dụng các giá trị từ một trường khác. Để chèn một trường được tính toán, hãy thực hiện các bước sau.

1. Nhấp vào bất kỳ ô nào bên trong bảng trụ.

2. Trên tab Analyze, trong nhóm Calculations, bấm vào Fields, Items & Sets.

3. Nhấp vào Calculated Field

Hộp thoại Insert Calculated Field xuất hiện.

4. Nhập Oceania cho Name.

6. Nhấp vào Thêm.

Lưu ý: sử dụng nút Field để nhanh chóng chèn các trường khi bạn nhập công thức. Để xóa trường được tính toán, chọn trường và nhấp vào Delete (bên dưới Under).

7. Nhấp OK.

Excel tự động thêm trường Tax vào khu vực Value.

Kết quả:

Mục tính toán

Một mục được tính toán sử dụng các giá trị từ các mục khác. Để chèn một mục được tính toán, thực hiện các bước sau.

1. Nhấp vào bất kỳ Quốc gia nào trong bảng trụ.

2. Trên tab Analyze, trong nhóm Calculations, bấm vào Fields, Mục & Bộ.

3. Nhấp vào Calculated Item.

Hộp thoại Insert Calculated Item xuất hiện.

4. Nhập Oceania cho Name.

5. Nhập công thức =3%*(Australia+’New Zealand’)

6. Nhấp Add.

Lưu ý: sử dụng nút Insert Item để nhanh chóng chèn các mục khi bạn nhập công thức. Để xóa một mục được tính toán, chọn mục đó và nhấp vào Delete (bên dưới Under).

7. Lặp lại các bước 4 đến 6 cho North America (Canada và United States) và Europe (France, Germany and United Kingdom) với mức thuế lần lượt là 4% và 5%..

8. Nhấp OK.

Kết quả:

Lưu ý: chúng tôi đã tạo hai nhóm (Sales và Tax).

Chèn biểu đồ Pivot

Biểu đồ Pivot là biểu diễn trực quan của bảng Pivot trong Excel.

Chèn biểu đồ Pivot

Để chèn biểu đồ trục, thực hiện các bước sau.

1. Nhấp vào bất kỳ ô nào bên trong bảng Pivot.

2. Trên tab Analyze, trong nhóm Tools, bấm PivotChart.

Khi đó hộp thoại Insert Chart xuất hiện.

3. Nhấp OK.

Lưu ý: mọi thay đổi bạn thực hiện đối với biểu đồ Pivot được phản ánh ngay lập tức trong PivotTable và ngược lại.

Lọc biểu đồ Pivot

Để lọc biểu đồ Pivot này, bạn hãy thực hiện các bước sau.

1. Sử dụng các bộ lọc tiêu chuẩn (hình tam giác bên cạnh Product và Country). Ví dụ: sử dụng bộ lọc Country để chỉ hiển thị tổng số tiền của từng sản phẩm được xuất sang Hoa Kì (United States)

2. Xóa bộ lọc Country.

3. Vì trường Category đã được thêm vào khu vực Filters nên chúng tôi có thể lọc biểu đồ Pivot này (và bảng Pivot) theo Category. Ví dụ: sử dụng bộ lọc Category để chỉ hiển thị các loại rau được xuất sang mỗi quốc gia.

Thay đổi loại biểu đồ Pivot

Bạn có thể thay đổi thành một loại biểu đồ trục khác nhau bất cứ lúc nào.

1. Chọn biểu đồ.

2. Trên tab Design, trong nhóm Type, bấm Change Chart Type.

3. Chọn Pie.

4. Nhấp OK.

Kết quả:

Lưu ý: biểu đồ hình tròn luôn sử dụng một chuỗi dữ liệu (trong trường hợp này là Beans). Để có được biểu đồ Pivot của một quốc gia, hãy thay đổi dữ liệu trên các trục. Đầu tiên, chọn biểu đồ. Tiếp theo, trên tab Design, trong nhóm Data, bấm Swap Row/Column

Theo Excel Easy

Bên cạnh các hàm, công thức phổ biến để có thể tính toán thì pivot table là một trong những tính năng rất hữu dụng nhằm tổng hợp, phân tích dữ liệu một cách nhanh chóng trên excel. Vậy cách sử dụng pivot table excel 2013 như thế nào?

Pivot table là gì

Pivot table có thể hiểu là một phương thức tương tác nhanh chóng nhằm tóm tắt các dạng dữ liệu lớn trong một bản tính. Tinh năng này được thiết kế chuyên dùng cho các trường hợp như:

Truy tìm lượng dữ liệu lớn theo cách thức thân thiện

Tổng hợp dữ liệu số, tóm tắt dữ liệu theo thể loại, tạo công thức tùy chỉnh

Mở rộng, thu gọn các dữ liệu để tập trung kết quả và phân tích chi tiết các vùng bạn quan tâm

Lọc, sắp xếp, định dạng các nhóm dữ liệu hữu ích, tập trung vào thông tin mình muốn phân tích

Trình bày báo cáo trực tuyến một cách hấp dẫn, có kèm theo các chú thích rõ ràng.

Cách sử dụng pivot table excel 2013 cơ bản

Nếu muốn thực hiện cách sử dụng pivot table excel 2013, bạn cần phải bôi đen toàn bộ ô dữ liệu. Ví dụ, trong bảng tính toán này là từ ô B3 đến G15. Sau đó chọn menu insert/ Pivot table.

Kéo cột nhóm sản phẩm vào row labels

Cột tháng đưa vào column labels

Cột số tiền vào vùng values area

Cột khu vực đưa vào report filter

Và sau khi đã hoàn thành xong các bước trên của cách sử dụng pivot table excel 2013, bạn sẽ nhận lại được kết quả như trong hình:

Cách sử dụng pivot table excel 2013 nâng cao

Tính năng slicer để lọc dữ liệu với Pivot table

Tính năng slicer là một công cụ mới xuất hiện trong phần mềm excel kể từ phiên bản 2010. Tính năng này thường cung cấp những nút để bạn có thể lọc dữ liệu ở bản tính hoặc dữ liệu pivot table. Bên cạnh việc lọc nhanh các dữ liệu ở bảng tính, chức năng này của excel còn giúp bạn hể chính xác hơn những thông tin được hiển thị trong bảng pivot table mà bạn đã lọc.

Và để thực hiện cách sử dụng pivot table excel 2013 này, bạn cần làm các bước như sau

Bước 2: Sau khi đã lựa chọn xong 1 bảng tính mà bạn muốn lọc dữ liệu, bạn có thể chọn một hoặc nhiều trường khác nhau để có thể tiến hành lọc dữ liệu với tính năng slicer. Ví dụ, đối với bảng tính trên đã chọn các trường như department và status. Sau khi lọc dữ liệu bằng tính năng slicer sẽ cho ra kết quả như sau

Nhóm các dữ liệu lại với nhau

Nếu như bạn đang có một bản tính với các dữ liệu đang bị xếp một cách rời rạc, không có sự thống nhất. Thông thường, bạn sẽ sử dụng các cách như nhấp hết các dữ liệu tương ứng. Sau đó sử dụng hàm Sum để tính giá trị tổng. Tuy nhiên, nếu bạn biết tới tính năng group trong cách sử dụng pivot table excel 2013 thì đây là một tính năng giúp bạn rất dễ dàng có thể tính được các kết quả tổng, kết quả trung bình mà không cần phải mất quá nhiều công sức.

Ngoài ra, các bạn có thể học Excel từ cơ bản tới nâng cao thông qua chương trình đào tạo Miễn phí tại Webkynang Việt Nam.

List 30 thủ thuật dùng với Pivot table

PV là gì

Cách tạo Pivot đơn giản

Chỉnh sửa PV

Dùng phím tắt để tạo PV

Ẩn/ Hiện fields list

Đổi tên cột trong bảng Pivot

Làm mới dữ liệu

Dùng phím tắt để làm mới dữ liệu

Cách làm mới tất cả dữ liệu bằng phím tắt

Cách tạo tính năng làm mới dữ liệu của pivot table mỗi khi file được mở lên

Thêm dòng/ cột trong vùng dữ liệu nguồn thì làm thế nào để bảng Pivot cập nhật dữ liệu

Sắp xếp dữ liệu ở dạng Tabular view

Dùng bảng dữ liệu để tạo Pivot

Calculated fields: Ý nghĩa và cách tạo

Calculated fields: Cách xóa

Định dạng số

Đếm các giá trị duy nhất

Bật/ Tắt tổng phụ (Sub-total, Grand-total)

Thêm data bar

Thêm Icon sets

Cách Thêm color scales

Tạo Pivot chart

Tạo slicer

Cách Tạo timeline

Cách ẩn các nút bấm trong pivot chart

Chèn dòng trống sau mỗi mục tự động như thế nào?

Lặp tiêu đề mỗi nhóm dữ liệu

Pivot table tự động co giãn độ rộng cột: Làm thế nào để chặn việc này

Tạo % của mỗi mục trên số tổng

Tạo % trên số tổng phụ

1. Pivot table là gì?

Pivot table là một công cụ có sẵn trong office giúp chúng ta dễ dàng tổng hợp và truy xuất dữ liệu theo 1 vài chỉ tiêu từ một bảng dữ liệu KHỦNG.

KHỦNG là có thể lên tới cả triệu dòng đó bạn ạ.

Chỉ dùng hàm excel thì việc xử lý hàng chục nghìn dòng đã là nhiệm vụ rất khó khăn, nói chi tới triệu dòng.

PivotTable (PV) trong Excel 2010, 2013, 2016 và các phiên bản sau, còn giúp các bạn tạo ra những báo cáo động rất linh hoạt và hữu ích.

Thật khó để giải thích cho bạn tính VI DIỆU của PV, tốt nhất là chúng nên đi vào thực tế. Ở các phần sau, Trường sẽ thực hành làm một vài tác vụ để bạn thấy được tác dụng của PV.

2. Cách tạo Pivot table

Cách tạo Pivot table ở Excel 2003 hoàn toàn khác so với các phiên bản sau gồm: Excel 2007, 2010, 2013, 2017

Các bản từ 2007 có cách tạo tương tự nhau do sự khác biệt ở thanh Menu và Ribbon

B1: Bôi đen chọn vùng dữ liệu dùng để tạo Pivot Table – Ta còn gọi là Data source

B2 + 3: Mở thẻ Insert trên thanh ribbon rồi chọn PivotTable ngay phía bên trái.

B4 + 5 + 6: Chọn lại vùng dữ liệu để tạo pivot nếu cần tiếp theo ta chọn nơi lưu Pivot.

Có 2 phương án là:

+ Lưu Pivot table trực tiếp trên sheet hiện hành (Existing worksheet)

+ New worksheet: Lưu trên 1 sheet hoàn toàn mới

Cuối cùng bạn nhấn OK để hoàn tất

Kết quả của việc kéo thả đó được thể hiện trực tiếp tại 9.

Và đây là thành quả của Trường

Các bạn thấy hình dưới không ạ. Chỉ trong nháy mắt bạn đã có thể tổng hợp được chi phí của tất cả các Sub-Divison và chi tiết cho từng Group segment, từng tháng.

Để thực hiện việc chỉnh sửa PV bạn làm như sau:

Thường thì thao tác chỉnh sửa chủ yếu là:

Change Data source: Mở rộng hoặc thu hẹp vùng dữ liệu của Pivot table

Refresh: Làm mới dữ liệu cho bảng Pivot khi dữ liệu tại Data source thay đổi

4. Pivot table nâng cao trong excel

Đây là một trong những tính năng tuyệt với của PV giúp chúng ta tạo biểu đồ, báo cáo động một cách dễ dàng.

Đơn cử như báo cáo sau được lấy từ chương trình Đào tạo Excel nâng cao do Webkynang tổ chức tại các doanh nghiệp.

Thông thường khi tạo Pivot table, Excel sẽ chọn loại hiển thị Compact.

Nhưng khi đó việc lập các công thức hoặc xử lý dữ liệu có dữ liệu nguồn là bảng Pivot vừa tạo sẽ gặp khá nhiều khó khăn.

+ B1: Chọn bảng Pivot cần chỉnh

+ B2: Chọn thẻ Design trên thanh ribbon

+ B3: Chọn Report Layout, sau đó chọn Show in Tabular form

Tại đây các bạn có thể Kéo thả các cột, dòng từ dữ liệu nguồn vào bảng Pivot.

Nhiều khi các bạn không biết thì Phần PivotTable fields này bị ẩn đi nên không biết cách thêm fields vào pivot như thế nào.

Nguyên nhân: PivotTable fields bị ẩn

Trong một số trường hợp thực tế, chúng ta có thể lưu dữ liệu trong cùng 1 file Excel nhưng trong nhiều bảng tính (sheet) khác nhau. Chúng ta có thể lưu dữ liệu mỗi tháng ở 1 sheet, hoặc dữ liệu kết quả làm việc của mỗi nhân viên hay chi nhánh công ty ở một bảng tính. Đặc trưng của kiểu lưu trữ dữ liệu này là cấu trúc cột của các bảng dữ liệu giống nhau, chỉ có độ dài các bảng dữ liệu là có thể khác nhau. Làm sao chúng ta có thể tạo Pivot Table từ nhiều sheet trong Excel?

Chúng ta, ở vị trí quản lý, cần có cái nhìn tổng thể hơn dựa trên các bảng dữ liệu, trong trường hợp này chúng ta có thể sử dụng công cụ Pivot Table trong Excel. Tuy nhiên vấn đề đặt ra là, dữ liệu lại đang nằm ở các sheet khác nhau trong một File Excel. Bài này sẽ hướng dẫn các bạn một kỹ thuật để có thể tạo Pivot Table từ nhiều sheet trong Excel.

SỬ DỤNG MICROSOFT QUERY ĐỂ TẠO VÙNG DỮ LIỆU CHO BẢNG PIVOT

Trước khi có thể sử dụng tính năng Microsoft Query, các vùng dữ liệu trên các sheets cần được định dạng ở dạng bảng. Sau đó, tuỳ vào phiên bản Excel của bạn mà cách truy cập tính năng Microsoft Query sẽ hơi khác nhau 1 chút.

Trong cửa sổ chọn nguồn dữ liệu, chọn dữ liệu từ file excel như hình rồi bấm OK

Ở cửa sổ tiếp theo, bạn cần chọn đúng đường dẫn tới file Excel hiện tại

Sau khi chọn đường dẫn và bấm OK, bạn có thể nhìn thấy màn hình sau đây

Nếu làm tới bước này mà bạn không nhìn thấy dữ liệu trong phần “Available tables and columns”, thì bạn cần bấm vào nút Options… sau đó ở cửa sổ Table Options, chọn tick vào System Tables như hình rồi bấm OK

Sau đó bạn chọn bảng đầu tiên và bấm nút mũi tên sang phải để thêm các cột vào trong Query

Sau đó, bạn bấm Next cho tới khi gặp màn hình sau thì dừng lại và chọn mục

View data or edit query in Microsoft Query

Sau đó chúng ta cần bấm nút SQL

Trong hộp thoại SQL Statement, dán đoạn SQL sau đây:

SELECT * FROM `Y2017$` UNION ALL SELECT * FROM `Y2018$` UNION ALL SELECT * FROM `Y2019$` UNION ALL SELECT * FROM `Y2020$`

Sau đó bấm OK

Tiếp theo, chúng ta sẽ lựa chọn nơi sẽ lưu dữ liệu ở file Excel là 1 worksheet mới

Như vậy là từ bảng dữ liệu này, chúng ta có thể tạo ra Pivot Table và những báo cáo cần thiết. Khi dữ liệu ở các bảng dữ liệu ban đầu thay đổi, bạn chỉ cần bấm Refresh dữ liệu trong thẻ Table Design là dữ liệu sẽ được cập nhật

Nếu các bước phía trên còn chưa rõ với các bạn, mời các bạn xem video sau đây:

Chúc thành công.