Ms Excel Nâng Cao → 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:

Bảng dữ liệu 1

Yêu cầu: Tạo bảng Thống Kê có dạng sau:

Bảng Thống Kê 1

Phân Tích Bảng Thông Kê 1:

– Bảng Thống Kê có 3 vùng màu đỏ, vàng , xanh

+ Màu đỏ: Thể hiện cột Mã SP từ bảng dữ liệu 1, mọi 1 Mã SP( ví dụ: CH-01 ) hiển thị như vậy gọi là dòng ( ROWS )

+ Màu xanh: Thể hiện cột Xuất Xứ từ bảng dữ liệu 1, mọi 1 Xuất Xứ( ví dụ: Anh ) hiển thị như vậy gọi là Cột ( COLUMNS )

+ Màu vàng: Thể hiện cột Tổng Chi Phí từ bảng dữ liệu 1, mọi 1 ô trong vùng màu vàng Bảng Thống Kê 1 là giao của dòng Mã SP(ROWS) và cột Xuất Xứ(COLUMNS) thể hiện tổng chi phí theo Mã SP và Xuất Xứ đây được gọi là Chỉ số tổng hợp(VALUES)

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

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

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

KhachHang

HoaDon

ChiTietHD

 

Yêu Cầu: Tạo bảng thống kê theo mẫu sau(Thống kê các đơn hàng của từng khách hàng và tình trạng của từng đơn hàng.

Để làm được Ví dụ bằng Pivot Table thực hiện từng bước sau:

B1: Tạo Bảng động cho 3 bảng KhachHang, HoaDon, ChiTietHD

+ Đặt Trỏ vào vùng cần tạo bảng động

+ Vào INSERT / TABLE / HỘP THOẠI

+ 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

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

Có liên quan