Hướng dẫn sử dụng pivot table trong excel 2023 Informational

Một cửa sổ mới hiện lên, bạn nhấn chọn OK để thiết lập tạo một pivot table theo chế độ mặc định của excel.

Bảng pivottable hiện ra. Đầu tiên để tính tổng số tiền bán được của mỗi nhóm sản phẩm, thì bạn làm theo các bước

• Kéo cột Nhóm SP vào vùng Row Labels
• Kéo cột Tháng vào vùng Column Labels
• Kéo cột Số Tiền vào vùng Values area
• Kéo cột Khu Vực vào vùng Report Filter
• Và đây là kết quả sau khi hoàn thành các bước trên.
• Nếu bạn muốn lọc doanh thu theo 1 khu vực nào đó thì bạn chọn như hình bên dưới:

Bạn có thể thấy là tất cả giá trị của trường Department và Status sẽ hiện lên rõ nét cho bạn. Và khi bạn cần lọc gì, bạn chỉ cần bấm nút tương ứng thay vì chọn Filter như cách truyền thống.

1. Sử dụng Calculated Field để thêm trường tính toán trong pivot table

Calculated Field là một chức năng vô cùng hay khác của PivotTable. Nó giúp bạn có thể thực hiện tính toán những số liệu khác dựa vào các trường mà bạn đã có sẵn. Ví dụ, nếu bạn có trường Ngày công và Lương, thay vì bạn kéo thành 2 cột Ngày công và Lương trong PivotTable để rồi sau đó tính toán bằng tay theo kiểu =Lương*Ngày

công, bạn có thể sử dụng Calculated Field để trực tiếp tạo một field mới theo đúng công thức mà bạn cần.

Bạn có thể thực hiện bằng cách chọn vào PivotTable, sau đó chọn thẻ Options trên thanh Ribbon -> chọn Fields, Items & Sets -> Calculated Field.

Một hộp thoại sẽ hiện lên và bạn gõ tên field cùng công thức tương ứng -> chọn OK.

Ưu điểm của cách làm này chính là việc bạn tạo mới được 1 field và nó sẽ cùng với bạn tạo nên những báo cáo tùy chỉnh kéo thả. Bởi vì, nếu bạn tính toán bằng tay dựa trên công thức ở ngoài PivotTable, khi PivotTable bạn thay đổi khi kéo thả, chắc chắn công thức tay này sẽ không còn đúng nữa.

Ngoài ra, một số vấn đề khác cũng cần lưu ý như bẫy Average trong PivotTable mà bạn sẽ dễ mắc phải nếu không sử dụng Calculated Field này.

1. Dùng Conditional Formatting cho PivotTable để định dạng dữ liệu theo điều kiện

Ở chế độ tính toán bình thường thì mình nghĩ rất nhiều bạn biết sử dụng Conditional Formatting để định dạng dữ liệu theo điều kiện. Một điều khá hay là cho dù bạn đang xài PivotTable, Conditional Formatting vẫn tỏ ra sử dụng được như bình thường. Bạn chỉ cần vào thẻ Home trên thanh Ribbon -> chọn Conditional Formatting và thực hiện tất cả như bạn làm với ô, bạn sẽ có kết quả như bạn mong muốn.

Chẳng hạn như ví dụ của tôi, tôi chọn Conditional Formatting là Data Bars để xem tỷ lệ về dữ liệu của những người có trong danh sách.

Ưu điểm của vấn đề này chính là việc Conditional Formatting sẽ theo sát PivotTable của bạn, nghĩa là kể cả khi bạn kéo thả, Conditional Formatting sẽ chạy theo đúng với field mà bạn đã thiết lập từ trước và bạn sẽ không phải tốn công để chỉnh sửa lại điều kiện như phải làm với ô.

1. Chuyển đổi dữ liệu của bạn thành dạng %

Với bảng dữ liệu như bên trên, bây giờ bạn muốn thay đổi nó thành định dạng % tích lũy tăng dần để vẽ biểu đồ Pareto thì bạn phải làm như thế nào? Chắc chắn là bạn không nên tính toán bằng tay ra tỷ lệ % rồi sau đó cộng dồn lại.

Chúng ta có thể làm theo một cách khác như sau: Bấm vào giá trị mà bạn cần chuyển đổi ngay trên vùng Values của PivotTable -> chọn Value Field Settings.

Chúng ta có thể phân định cách nào tốt hơn qua ví dụ rời rạc hơn nữa như bài toán, tìm trung bình của 3 tháng 1, 5 và 7. Bạn có thể thấy bài toán này không có quy luật như bài toán Quý kể trên.

Và bạn cũng sẽ dễ dàng nhận thấy, cách 1 chỉ mang tính chất mì ăn liền giải quyết ngay tại chỗ, nhưng nó không thể theo bạn trong việc chuyển đổi bằng kéo thả vốn dĩ là đặc trưng của PivotTable. Bên cạnh đó, trong trường hợp tính kết quả tháng 1, 5 và 7, sử dụng công thức rất cập rập và khó khăn.

Cách 2 cũng không tốt hơn vì không có quy luật nào tồn tại ở đây thì sao bạn có thể điền dữ liệu cho một cột mới?

 1. Sử dụng Slicer

 2. Tạo Calculated Field

 3. Tạo nhiều PivotTable từ một PivotTable gốc

 4. Ẩn hoặc hiện tổng phụ

 5. Cách xem chi tiết từng dữ liệu trong bảng tổng hợp

 6. Làm mới dữ liệu

1. SỬ DỤNG SLICER

Slicer là công cụ cực kỳ hữu ích trong việc phân tích nhiều dữ liệu.

1 6 KỸ THUẬT NÂNG CAO CHO PIVOTTABLE BẠN NÊN BIẾT

1. Nhấp vào bảng tổng hợp.
2. Chọn “Insert” sau đó nhấp nút “Slicer”. Chọn biến số bạn muốn sắp xếp dữ liệu của mình (trong trường hợp này là “Year”) sau đó nhấp nút “ok”.

2 6 KỸ THUẬT NÂNG CAO CHO PIVOTTABLE BẠN NÊN BIẾT

1. Thay đổi kích thước và di chuyển slicer của bạn đến nơi bạn muốn nó xuất hiện. Lờ khuyên bạn nên đặt đó trên đầu bảng tổng hợp của bạn để bạn có thể dễ dàng xem mọi thứ.

3 6 KỸ THUẬT NÂNG CAO CHO PIVOTTABLE BẠN NÊN BIẾT

1. Bây giờ Jason cần liên kết các PivotTables hiện có với Slicer để tất cả dữ liệu được liên kết với những Slicer cụ thể. Để thực hiện thì nhấp chuột phải vào Slicer, chọn “Report Connections,”, sau đó cửa sổ hiện lên bạn chọn các pivottables kết nối với Slicer đó.

4 6 KỸ THUẬT NÂNG CAO CHO PIVOTTABLE BẠN NÊN BIẾT

8 6 KỸ THUẬT NÂNG CAO CHO PIVOTTABLE BẠN NÊN BIẾT

Với trường được tính toán đó, Jason có thể dễ dàng xem lợi nhuận của mình cho từng loại bia, cũng như tổng lợi nhuận lớn – ở hàng dưới cùng của bảng tổng hợp.

1. Tạo nhiều PivotTable từ một PivotTable gốc

Khi bạn muốn chia nhỏ dữ liệu của mình từ một PivotTable thành nhiều PivotTable nhỏ thì bạn có thể sử dụng thủ thuật hữu ích dưới đây.

Ví dụ: Jason có một bảng tổng hợp hiển thị doanh số bán bia theo quý. Anh ấy muốn đi sâu vào doanh số bia trong mỗi quý với từng loại bia (Amber, Pilsner, IPA hoặc Stout).

Để làm như vậy, Jason sẽ tạo một PivotTable cho từng loại bia: một cho Amber, một cho Pilsner ... Để làm điều đó chỉ cần vài cú nhấp chuột bằng cách sử dụng bảng Pivottable gốc và thực hiện như sau:

1. Dù bạn muốn lọc bảng tổng hợp của mình theo cách nào (trong tình huống này là lọc theo loại bia) thì bạn cũng càn áp cái bảng đó vào bộ lọc. Nhấp vào bảng Pivottable, trên thanh công cụ chọn “PivotTable Analyze” , nhấp vào “Fields List” sau đó bạn kéo Type vào Filters.

9 6 KỸ THUẬT NÂNG CAO CHO PIVOTTABLE BẠN NÊN BIẾT

1. Bộ lọc được áp dụng, Jason nhấp vào bên trong bảng tổng hợp, quay lại tab “PivotTable Analyze” trên thanh công cụ rồi nhấp vào Options rồi chọn “ Show report Filters Page”. Sau đó đánh dấu vào “Type” và chọn Ok. Excel sẽ tạo một trang tính mới với một bảng tổng hợp từng loại bia.
2. Ẩn hoặc hiện tổng phụ

Như đã đề cấp thì Excel có thể giúp bạn hạn chế những sai số có thể xả ra. Điều này không chỉ áp dụng cho các tổng số mà còn có thể hiển thị tổng phụ trong bảng tổng hợp nếu bạn muốn.

Giả sử Jason đang xem xét dữ liệu về doanh số bán bia của mình theo Size (quy mô) và theo loại (Type). Nếu đặt giá trị mặc định theo cách của anh ấy thì Excel sẽ không hiển thị tổng phụ cho mỗi tiêu chí mà chỉ hiển thị doanh thu bán bia của anh ấy.

Jason sẽ xem xét dữ liệu được chia nhỏ:

1. Nhấp vào bên trong bảng và nhấp vào tab “Design” trên thanh công cụ.
2. Nhấp vào “ Subtotal” và sau đó chọn hiển thị tổng phụ ở cuối hoặc đầu nhóm của bạn (cách nào cũng được tùy vào lựa chọn của bạn).
3. Khi làm như vậy sẽ sửa tất các lần xuất hiện của “Grolwer” trong tập dữ liệu nhưng sẽ không sửa được bất kỳ pivottables mà được liên kết với nó.
4. Để thực hiện cập nhật đó các vị trí mắc lỗi, hãy chuyển đến tab “Data” trong thanh công công cụ và sau đó nhấp vào nút “Refresh all”. Điều đó sẽ thực hiện chỉnh sửa tương tự trên toàn bộ sổ làm việc.

I. PIVOTTABLE, PIVOTCHART, SUBTOTAL

1. SUBTOTAL

B1: Sắp xếp dữ liệu theo trật tự cần tổng hợp theo nhóm

B2: Vào DATA / chọn SUBTOTAL / xuất hiện hộp thoại

B3: Thiết lập các tham số hộp thoại

+AT EACH CHANGE IN: Chọn cột cần tổng hợp theo nhóm

+USE FUNCTION: Chọn hàm tính toán

+ADD SUBTOTAL TO: Chọn cột cần tính / Nhấn OK

Hình ảnh kết quả sau tổng hợp:

2. PIVOTTABLE

PivotTable là một công cụ tuyệt vời MS Excel cung cấp dùng để phân tích dữ liệu theo nhiều góc độ, nhiều yêu cầu khác nhau từ một danh sách hay một bảng. Từ khối dữ liệu khổng lồ ban đầu thì PivotTable có thể giúp các bạn tổng hợp dữ liệu theo nhóm, thu gọn dữ liệu theo yêu cầu và mục đích sử dụng của bạn một cách nhanh chóng.

Để sử dụng tốt Pivot Table cần nắm vững cấu trúc, thuộc tính của Pivot Table. Chúng ta phân tích ví dụ sau để hiểu hơn về Pivot Table

Ví dụ: Cho bảng dự liệu sau:

Kết Luận:

Khi sử dụng Pivot Table để thống kê về cơ bản ít nhất 2 trong 3 thuộc tính sau:

• ROWS: Tổng hợp thông tin theo dòng
• COLUMNS: Tổng hợp thông tin theo cột
• VALUES: Chỉ số tổng hợp, phải chọn hàm tính toán(Sum, Count)

Ngoài ra còn thuộc tính Pivot Table: FILTERS dùng thiết lập Thuộc tính lọc để xem bảng thống kê theo một hoặc vài loại mẫu tin

Hình ảnh Layout Pivot Table

1. Cách tạo Pivot Table ( Tổng hợp dữ liệu từ 1 bảng / sheet)

B1: Đặt chuột vào ô bất kỳ của vùng dữ liệu cần tổng hợp

B2: INSERT / PIVOT TABLE / XUẤT HIỆN HỘP THOẠI SAU

• Tại SELECT A TABLE OR RANGE: Chọn bảng / vùng chứa dữ liệu cần tổng hợp
• Tại USE AN EXTERNAL DATA SOURCE: Chọn nguồn từ bên ngoài khác tập tin hiện tại
• Tại NEW WORKSHEET: Tạo 1 sheet mới thể hiện Pivot Table
• Tại EXISTING WORDKSHEET: Pivot Table được tạo tại vị trí con trỏ trên sheet hiện hành
• Ở đây thống nhất sử dụng mặc định / OK / mở ra sheet mới chứa Vỏ Pivot Table giống như “Hình ảnh Layout Pivot Table”

B3: Lần lượt kéo thả các cột thuộc tính vào Vỏ Pivot Table như : ROWS, COLUMNS, VALUES, FILTERS phù hợp

B4: Định dạng hiển thị cho Pivot Table: đặt chuột vào vùng Pivot Table / Trên thanh Menu xuất hiện PIVOTTABLE TOOLS / Chọn Tab ANALYZE hoặc DESIGN để thiết lập định dạng hiển thị như mong muốn.

2. Cách tạo Pivot Table ( Tổng hợp dữ liệu từ nhiều bảng )

Khi cùng lúc có nhiều bảng dữ liệu hoặc nhiều sheet khác nhau muốn tạo một PivotTable, chúng ta giải quyết vấn đề đó thế nào? Nếu sử dụng MS Excel 2013, thì bạn có một phương pháp được tổ chức hợp lý để thực hiện việc này, đó được gọi là Mô hình Quan Hệ Dữ liệu và nó sử dụng dữ liệu quan hệ theo Mô hình cơ sở dữ liệu làm việc.

Ví dụ:

Cho 3 bảng dữ liệu gồm: KhachHang, HoaDon, ChiTietHD, trong đó bảng KhachHang quan hệ với bảng HoaDon qua MaKH, bảng HoaDon quan hệ với bảng ChiTietHD qua SoHD

• Tại WHERE IS THE DATA FOR YOUR TABLE: Chọn vùng cần tạo bảng động
• Tại MY TABLE HAS HEADERS: Chọn nếu muốn sử dụng tên cột có sẵn, nếu không chọn Table tự tạo tên cột

\=> OK

• Thực hiện lại để tạo đủ các bảng mong muốn

B2: Đặt con trỏ vào bảng ChiTietHD

B3: Vào INSERT / PIVOT TABLE / xuất hiện hộp thoại / Chọn Add this data to the data Mode

OK / xuất hiện Vỏ PIVOT TABLE( giống như “Hình ảnh Layout Pivot Table” )

B4: Tạo quan hệ cho các bảng

• Đặt con trỏ vào bên trong vỏ Pivot Table
• Trên thanh Menu xuất hiện PIVOTTABLE TOOLS / Chọn Tab ANALYZE / Chọn Relationship / xuất hiện hộp thoại
• Chọn New / Xuất hiện hộp thoại (*)

\=> Tại Table: Chọn bảng KhachHang

\=> Tại Related table: Chọn bảng HoaDon

\=> Tại Column, Related Column : chọn MaKH

\=> OK

• Thực hiện lại từ bước (*) để tạo quan hệ giữa bảng HoaDon và ChiTietHD

B5: Sau khi tạo quan hệ giữa các bảng chúng ta kéo thả các cột vào thuộc tính PIVOTTABLE giống như ở trên

Như vậy để tạo Pivot Table cho nhiều bảng cùng lúc

B1: Tạo bảng động cho các bảng cần tổng hợp

B2: Chèn Pivot Table

B3: Tạo quan hệ giữa các bảng

B4: Kéo thả các cột vào Vỏ Pivot Table

Xem Thêm Video

1. PIVOT chart

Tương tự như Pivot Table chỉ thêm phần đồ thị tự hình thành sau khi thực hiện Pivot

II. CONSOLIDATE, DATA TABLE

1. consolidate

Consolidate là một công cụ vừa được sử dụng để tổng hợp, thống kê dữ liệu đồng thời dùng để ghép dữ liệu từ nhiều bảng khác nhau một cách nhanh chóng.

1. Consolidate Tổng hợp, thống kê

B1: Đặt con trỏ tại vị trí muốn hiển thị kết quả thống kê ( Vị trí này ở cùng sheet chứa thông tin cần thống kê hoặc nằm sheet khác )

Chú ý: Nếu muốn tổng hợp thống kê dữ liệu của cột nào đó cần chỉ định thì chúng ta nên tạo tiêu đề cột cho vùng thống kê trước rồi chọn vùng có chứa tiêu đề vừa tạo ( Xem trong video ví dụ phía dưới )