37 CHƯƠNG 3 TỔNG HỢP VÀ PHÂN TÍCH SỐ LIỆU Mục đích, yêu cầu Mục đích: - Trang bị cho sinh viên kiến thức cơ bản về cách tổng hợp dữ liệu từ nhiều sheet, nhiều tập tin khác nhau trong đó có thực hiện một số phép toán: tính tổng, đếm, tìm giá trị lớn nhất, nhỏ nhất - Trang bị kỹ năng phân tích số liệu 3 chiều Yêu cầu: - Sinh viên phải hiểu rõ các thao tác khi thực hiện tổng hợp số liệu trong 2 trường hợp: các bảng dữ liệu có cấu trúc gống nhau, các bảng dữ liệu có cấu trúc khác nhau - Biết cách tính tổng của mỗi bộ phận trong bảng cơ sở dữ liệu - Biết các thành phần của bảng phân tích số liệu 3 chiều và cách tạo bảng phân tích số liệu 3 chiều - Giải các bài tập cuối chương và biết vận dụng các kiến thức đã học để giải quyết các bài toán ứng dụng trong thực tế 3.1 Chức năng Subtotals (Tổng bộ phận): Chức năng này dùng để nhóm dữ liệu theo từng nhóm đồng thời chèn vào cuối mỗi nhóm những dòng thống kê tính toán (gọi là các bộ phận - Subtotals ) và một dòng tổng kết ở cuối phạm vi (gọi là toàn bộ - GrandTotal). Thao tác như sau: - Sắp xếp CSDL theo cột làm khoá (muốn nhóm theo cột nào thì cột đó gọi là cột làm khoá) - Đặt con trỏ ô vào vùng CSDL - Chọn lệnh Data Xuất hiện nhóm công cụ Outline Hình 3.1 - Trong nhóm công cụ out line (hình 3.1) chọn công cụ Subtotals Xuất hiện hộp thoại như hình 3.2 + At each change in: Chọn trường làm khoá để sắp xếp + Use Function: Chọn hàm sử dụng để thống kê + Add SubTotal to: Đánh dấu vào những cột cần thống kê giá trị + Replace current Subtotals: Thay các hàng Subtotal tạo trước đó bằng các hàng Subtotal mới. + Page Break Between Groups: Tự động động tạo dấu ngắt trang giữa các nhómdữ liệu. + Sumary Below data: Tạo các dòng thống kê phía dưới các nhóm dữ liệu.
- Chọn xong ấn OK. Ví dụ: Có số liệu về bảng doanh thu bán hàng tháng 7/2010 như sau Hãy tính tổng thành tiền theo tên hàng 39 Giải : B1: Sắp xếp CSDL theo tên hàng Kết quả như sau B2: Đưa con trỏ vào vùng CSDL B3: Chọn lệnh Data , tại nhóm Outline chọn Subtotal + Tại At Each Change In, Tên hàng + Tại Use Function, chọn hàm Sum + Tại Add Subtotal To chọn thành tiền B4: Chọn OK Kết quả như sau 40 3.2 Chức năng Consolidate (Tổng hợp từ các cơ sở dữ liệu chi tiết): Chức năng Consolidate được sử dụng để tạo CSDL tổng hợp từ những CSDL chi tiết (được chọn lựa trên cùng một hoặc trên nhiều tập tin bảng tính khác nhau) 3.Tổng hợp theo vị trí. Được sử dụng khi dữ liệu bảng tính giống hệt nhau về Cấu trúc, bao gồm cả Số hàng, Số cột. Để thực hiện tổng hợp dữ liệu, chúng ta cần tạo ra một Sheet trống, với cấu trúc tương tự như các Sheet khác.
B1: Chọn vùng mà chúng ta muốn tổng hợp dữ liệu. B2: Chọn lệnh Data Xuất hiện Ribbon như hình 3.3 B3: Trong nhóm công cụ data tools chọn Consolidate Xuất hiện hộp thoại như hình 3.4 B4: Lần lượt chọn hàm, nhập vùng dữ liệu cần tổng hợp vào hộp thoại - Function: Chọn hàm cần dùng để tổng hợp - Reference: Nhập địa chỉ vùng dữ liệu. Địa chỉ này bao gồm: 'tên ổ đĩa\[tên tập tin]tên Sheet'!địa chỉ vùng dữ liệu (Nếu dữ liệu ở trong cùng tập tin với bảng tổng hợp thì không cần nhập tên ổ đĩa, tên tập tin - Top Row: Tạo dòng tiêu đề cho bảng tổng hợp. - Left Column: Tạo tiêu đề cột đầu tiên cho bảng tổng hợp.
- Create link to source data: Tạo mối liên kết từ bảng tổng hợp đến các bảng chi tiết nhằm mục đích nếu có sự thay đổi trong các bảng dữ liệu chi tiết thì các dữ liệu liên quan trong bảng tổng hợp cũng tự thay đổi theo. - Kích chuột vào nút Add - Tiếp tục khai báo cho các vùng dữ liệu tiếp theo - Sau khi khai báo xong các vùng dữ liệu cần tổng hợp kích chuột vào nút OK. Ví dụ: Có số liệu chi tiết về hàng bán của 3 năm như sau 42 Yêu cầu: Tổng hợp hàng bán sau 3 năm theo mẫu: Giải: B1: Tạo sheet tổng hợp theo mẫu B2: Chọn khối ô B4:E7 B3: Chọn lệnh Data B4: Trong nhóm công cụ data tools chọn Consolidate Xuất hiện hộp thoại (như hình 3.4) B5: Tại ô Reference nhập địa chỉ : nam2008!$B$4:$E$7, rồi chọn Add B6: Tại ô Reference nhập tiếp địa chỉ : nam2009!$B$4:$E$7, rồi chọn Add B7: Tại ô Reference nhập địa chỉ : nam2010!$B$4:$E$7, rồi chọn Add 43 Cuối B7 ta có hộp thoại như hình 3.5 B8: Chọn OK sẽ được kết quả tổng hợp bảng 3.Tổng hợp theo hàng và cột. Được sử dụng khi cấu trúc dữ liệu khác nhau.
Excel dựa trên Hàng và Cột mà tổng hợp dữ liệu. Thao tác : - Đặt con trỏ ô ở sheet cần tổng hợp - Chọn lệnh Data - Trong nhóm công cụ data tools chọn Consolidate… Xuất hiện hộp thoại như hình 3.3 - Lần lượt chọn những vùng dữ liệu cần tổng hợp trên các sheet (chọn cả tiêu đề dòng, cột), đánh dấu vào mục Top Row & Left Column rồi nhấn nút Add - Chọn Creat Link to Source Data (nếu muốn kết quả tổng hợp thay đổi theo khi dữ liệu nguồn thay đối). - Chọn OK Lưu ý Nếu CSDL chi tiết có cùng cấu trúc (có cùng số lượng trường, tên trường 44 và kiểu dữ liệu từng trường hoàn toàn như nhau) thì CSDL tổng hợp sẽ có cấu trúc tương tự như các CSDL chi tiết và mỗi bản ghi của CSDL tổng hợp sẽ là dữ liệu tổng hợp từ các bản ghi trong các CSDL chi tiết. Nếu các CSDL chi tiết không có cùng cấu trúc thì nhất thiết phải có chung ít nhất trường đầu tiên bên trái cùng kiểu dữ liệu để làm khoá.
Lúc đó CSDL tổng hợp sẽ có dạng gộp các CSDL chi tiết theo qui tắc: + Các trường trùng tên sẽ được tổng hợp + Các trường không trùng tên sẽ được ghép nối Ví dụ: Cho bảng số liệu chi tiết về hàng bán của 3 năm (2008 – 2010) như sau: Yêu cầu: Tổng hợp hàng bán sau 3 năm cho các đại lý 45 Giải: Nhận xét: Các bảng dữ liệu có cấu trúc không giống nhau, số lượng hàng của mỗi bảng cũng không giống nhau, số cột nhiều nhất là 5; số hàng nhiều nhất là 5 B1: Tạo sheet tổng hợp B2: Đưa con trỏ ô đến vị trí cần tạo bảng tổng hợp B3: Chọn lệnh Data, Trong nhóm công cụ data tools chọn Consolidate Xuất hiện hộp thoại như hình 3.3 B4: Tại ô Reference nhập địa chỉ : nam2008!$A$3:$D$6 , chọn Top row và left column sau đó chọn Add B5: Tại ô Reference nhập địa chỉ : nam2009!$A$3:$C$7, chọn Top row và left column sau đó chọn Add B6: Tại ô Reference nhập địa chỉ : nam2010!$A$3:$E$7, chọn Top row và left column sau đó chọn Add Cuối B6 ta có hộp thoại như hình 3.6 B7: Chọn OK sẽ được kết quả tổng hợp như bảng 3.3 Tổng hợp, thống kê và phân tích số liệu với Pivotable 46 Pivot table là công cụ để tổng hợp và phân tích nhanh chóng số lượng lớn dữ liệu. Sử dụng báo biểu PivotTable để: - Truy vấn một lượng lớn dữ liệu, trình bày gọn, dễ hiểu; - Dữ liệu được gom nhóm và tính toán trên các nhóm; - Trình bày nhiều dạng tổng hợp dữ liệu khác nhau (chuyển cột thành dòng, dòng thành cột); 3.1 Cách tạo Pivot Table - Chọn vùng dữ liệu nguồn cho PivotTable - Chọn lệnh Insert Chọn Pivot Table Xuất hiện công cụ như hình 3.7 - Chọn PivotTable Xuất hiện hộp thoại như hình 3.8 - Chọn vị trí đặt Pivot table New Worksheet (Trên worksheet mới) Hoặc Existing Worksheet (Trên worksheet hiện tại) Nếu chọn Existing Worksheet thì nhập thêm địa chỉ ô đặt Pivot table - Chọn OK Xuất hiện hộp thoại như hình 3.9 - Chọn các vùng dữ liệu liên kết trên Pivottable bằng cách rê các Field thả vào các vị trí tương ứng gồm: + Report Fielter: Cấp lọc dữ liệu cao nhất + Row Labels: hiển thị đầu dòng + Comumn Labels: hiển thị đầu cột + Value: Field tính toán - Để chọn hàm tính toán: kích phải chuột vào ô Values chọn Value Field settings chọn lại tên hàm Ví dụ: Có bảng kê chi tiết bán hàng như bảng 3.3 A B C D E 1 SHD TENKHACHHANG NGAY TENSP SOLUONG 2 47 Tan Hiep 04/05/11 Gach thedac190 6,700 3 48 Anh Vu 04/05/11 Gach 4lo90 1,400 4 49 Dong Tien 04/05/11 Gach 4lo90 6,000 5 50 Hai Thach 04/05/11 Gach TC4l080 5,000 6 51 Tan Hiep 04/05/11 Gach 4lo90 4,000 7 52 Hai Thach 04/05/11 Gach TC4l080 5,000 8 53 Anh Tu 04/05/11 Gach 4l80 4,000 9 54 Tan Hiep 04/05/11 Gach thedac180 2,000 10 55 Hai Thach 04/05/11 Gach thedac190 5,000 11 56 Hoan My 04/05/11 Gach 4l80 4,000 12 57 Thanh Cong 05/05/11 Gach 4lo 75 5,000 48 13 58 Quang Son 05/05/11 Gach 4lo90 3,000 14 59 Tan Hiep 05/05/11 Gach 4l80 2,000 15 60 Phu Thuan 05/05/11 Gach chongnong 2,000 16 616 Anh Hai 03/06/11 Gach thedac180 2,000 17 617 Chuc Bao 03/06/11 Gach 4lo 75 5,000 18 618 Cty XD Hop Thang 03/06/11 Gach 4lo 75 5,000 19 619 Phu Thuan 03/06/11 Gach chongnong 2,000 20 620 Dang Phuong 03/06/11 Gach thedac190 6,000 21 621 Tan Hiep 03/06/11 Gach 4lo90 4,500 22 622 Thanh son 03/06/11 Gach 4lo90 2,500 23 623 Truong Ba Ngac 03/06/11 Gach 4lo90 3,000 24 625 Nguyen Duc Chanh 03/06/11 Gach 4lo90 3,000 25 626 Tan Hiep 03/06/11 Gach thedac190 5,000 Bảng 3.3 Yêu cầu: Lập báo cáo dưới góc độ từng khách hàng như sau: TENKHACHHANG NGAY TENSP Tổng SOLUONG Bài giải : B1: Đưa con trỏ ô vào bảng dữ liệu B2: Chọn lệnh Insert Pivot Table Pivot Table B3: Chọn OK B4: Rê Field TENKH thả vào ô Report Filter B5: Rê Field TENSP thả vào ô Row Labels B6: Rê Field NGAY thả vào ô Column Labels B7: Rê Field SOLUONG thả vào ô Values Kết quả như bảng 3.4 - Để xem khách hàng nào ta kích chuột vào biểu tượng rồi chọn tên khách hàng - Để xem theo ngày ta kích chuột vào biểu tượng rồi chọn ngày cần xem - Để xem sản phẩm nào ta kích chuột vào biểu tượng rồi chọn tên sản phẩm cần xem 3.2 Hiệu chỉnh Pivot Table - Thêm / xoá trường Để thêm/xoá trường trong PivotTable + Kích chuột phải vào bảng PivotTable Chọn lệnh Show Field list Xuất hiện lại hộp thoại Pivot Table Field list như hình 3.