Giáo án điện tử chuyên đề Tin học ứng dụng 12 cánh diều Bài 3: Dùng bảng pivot để phân tích thống kê

THÂN MẾN CHÀO ĐÓN

CẢ LỚP ĐẾN BUỔI HỌC NÀY!

KHỞI ĐỘNG

Trong một trang tính của Excel, có một bảng dữ liệu lớn theo dõi hoạt động bán hàng của một cửa hàng bán lẻ với khá nhiều hàng, nhiều cột (ví dụ các cột: tên nhân viên, ngày bán, số lượng, đơn giá, thành tiền,...).

Nếu được yêu cầu làm báo cáo tổng hợp cuối quý từ bảng dữ liệu lớn này về doanh số hàng tháng của từng nhân viên thì em làm như thế nào?

Trả lời:

Để làm báo cáo tổng hợp cuối quý về doanh số hàng tháng của từng nhân viên từ bảng dữ liệu lớn trong Excel, có thể thực hiện các bước sau:

1. Chuẩn bị dữ liệu

Đảm bảo bảng dữ liệu được sắp xếp hợp lý, mỗi cột có tiêu đề rõ ràng (ví dụ: Tên nhân viên, Ngày bán, Số lượng, Đơn giá, Thành tiền,…).

2. Thêm cột phụ nếu cần thiết

Thêm cột “Tháng” nếu chưa có để xác định tháng của mỗi giao dịch.

3. Sử dụng PivotTable (Bảng tổng hợp)

• Chọn toàn bộ bảng dữ liệu.
• Chọn Insert trên thanh công cụ và chọn PivotTable.
• Trong hộp thoại Create PivotTable, chọn nơi muốn đặt PivotTable (có thể là trên một trang tính mới hoặc cùng trang tính hiện tại).
• Khi PivotTable Field List xuất hiện:
• Kéo “Tên nhân viên” vào vùng Rows (Hàng).
• Kéo “Tháng” vào vùng Columns (Cột).
• Kéo “Thành tiền” vào vùng Values (Giá trị).
• Excel sẽ tự động tính tổng cho các giá trị này.

4. Định dạng và tinh chỉnh PivotTable

• Có thể thay đổi cách hiển thị của dữ liệu trong PivotTable bằng cách nhấn chuột phải vào các vùng giá trị và chọn Value Field Settings để thay đổi cách tính toán (Sum, Average, Count,…).

• Để dữ liệu dễ đọc hơn, có thể thêm các bộ lọc (Filters) như “Tên nhân viên” hoặc “Tháng” để tùy chỉnh cách hiển thị dữ liệu.

5. Định dạng báo cáo

• Sau khi có PivotTable, có thể định dạng theo ý muốn, ví dụ như thay đổi font chữ, màu nền, thêm đường viền,…

• Có thể thêm tiêu đề cho báo cáo, ngày tháng lập báo cáo và bất kỳ ghi chú nào khác cần thiết.

BÀI 3:

DÙNG BẢNG PIVOT ĐỂ

PHÂN TÍCH THỐNG KÊ

NỘI DUNG BÀI HỌC

1

Thực hành cơ bản về bảng pivot

2

Thực hành tạo bảng tần số hai chiều

3

Thực hành tạo bảng tần số ghép nhóm

4

Thực hành tạo bảng tổng hợp nhiều chiều

1. THỰC HÀNH CƠ BẢN

VỀ BẢNG PIVOT

Hoạt động 1: Trong Excel, hãy nhập bảng dữ liệu như ở Hình 1a. Từ đó, hãy tạo bảng tổng hợp như Hình 1b để đếm số học sinh theo từng mức xếp loại.

1. Thực hành cơ bản về bảng pivot

Hình 1a.

Bảng dữ liệu

Hình 1b. Bảng tổng hợp thống kê số học sinh theo biến “Xếp loại”

1. Thực hành cơ bản về bảng pivot

Hình 2. Hộp thoại chọn khối ô dữ liệu và chọn nơi hiển thị bảng pivot

Bước 1. Đánh dấu chọn khối ô dữ liệu A1:C12 để tạo bảng pivot.

Bước 2. Chọn lệnh Insert\PivotTable để mở hộp thoại Create PivotTable.

Hướng dẫn thực hiện

1. Thực hành cơ bản về bảng pivot

Hình 2. Hộp thoại chọn khối ô dữ liệu và chọn nơi hiển thị bảng pivot

• Trên dòng Table/Range sẽ xuất hiện địa chỉ khối ô dữ liệu đã chọn.

• Chọn trang tính hiển thị bảng pivot là New Worksheet. Nếu chọn Existing Worksheet thì phải điền vào hộp Location địa chỉ ô ở góc trên trái của khối ô dành cho hiển thị bảng pivot.

1. Thực hành cơ bản về bảng pivot

Hình 3. Hộp thoại PivotTable Fields để tổ chức nội dung cho bảng pivot

Bước 3. Chọn OK. Hộp thoại PivotTable Fields xuất hiện.

1. Thực hành cơ bản về bảng pivot

Hình 3. Hộp thoại PivotTable Fields để tổ chức nội dung cho bảng pivot

• Kéo thả trường phân loại (theo VD: trường Xếp loại) từ bên trên vào vùng ROWS. Lặp lại thao tác vào vùng VALUES → chọn OK.

Hình 1b.

Bảng tổng hợp thống kê số học sinh theo biến “Xếp loại”

1. Thực hành cơ bản về bảng pivot

• Nếu kéo thả một nhãn tên trường vào vùng ROWS (hay COLUMNS) thì tên trường đó thành tên hàng – Row Labels (hay thành tên cột – Column Labels) trong bảng pivot.

• Nếu kéo thả một nhãn tên trường vào vùng VALUES thì Excel sẽ hiểu là cần tóm tắt các giá trị của trường.

Kiến thức cần biết

1. Thực hành cơ bản về bảng pivot

• Nếu kéo thả một nhãn tên trường dữ liệu kiểu số vào vùng VALUES thì Excel sẽ tự động chọn tính tổng Sum of.

→ Vì Xếp loại là trường dữ liệu không phải kiểu số nên Excel không tính tổng mà tự động chuyển thành Count of Xếp loại.

Kiến thức cần biết

• Hai cách tóm tắt dữ liệu thông dụng nhất là tính tổng và đếm.

2. THỰC HÀNH TẠO BẢNG

TẦN SỐ HAI CHIỀU

Hoạt động 2: Sử dụng bảng dữ liệu trong Hình 1a, hãy lập bảng tần số hai chiều (Two-way frequency tables) có hai cột tần số riêng cho Nam và Nữ.

2. Thực hành tạo bảng tần số hai chiều

Hình 1a. Bảng dữ liệu

Hướng dẫn thực hiện

2. Thực hành tạo bảng tần số hai chiều

• Cột Giới tính của bảng dữ liệu cũng biểu thị một trường phân loại. Do đó, có thể tạo bảng pivot theo trường này.

• Thực hiện các bước để khởi tạo bảng pivot và mở hộp thoại PivotTable Fields trong tự như đã làm. Trường Xếp loại được kéo thả vào vùng ROWS và vùng VALUES.

Hình 4a. Hộp thoại chọn vùng dữ liệu

Hướng dẫn thực hiện

2. Thực hành tạo bảng tần số hai chiều

• Kéo thả trường Giới tính vào vùng COLUMNS để tính tần số theo mỗi giới tính.

Hình 4b. Bảng tần số hai chiều

từ hai biến phân loại

Hình 4a. Hộp thoại chọn vùng dữ liệu

3. THỰC HÀNH TẠO BẢNG

TẦN SỐ GHÉP NHÓM

Hoạt động 3: Nhập bảng dữ liệu là danh sách học sinh với các trường Họ và tên, Giới tính, Tuổi như Hình 5a. Từ đó hãy:

1) Tạo bảng thống kê số lượng học sinh theo từng nhóm tuổi với khoảng là 10. Kết quả nhận được như Hình 5b.

2) Tạo bảng thống kê số lượng học sinh theo tuổi và giới tính. Nói cách khác, cần tạo bảng tần số theo tuổi được ghép nhóm hai chiều: Tuổi (với khoảng là 10) và Giới tính (để tách riêng cho Nam và Nữ). Kết quả nhận được như Hình 5c.

3. Thực hành tạo bảng tần số ghép nhóm

3. Thực hành tạo bảng tần số ghép nhóm

Hình 5. Tạo bảng tần số ghép nhóm và bảng tần số hai chiều

--------------------------------------

--------------------- Còn tiếp ----------------------