Bài giảng Tin học ứng dụng trong Kinh doanh - Chương 4: Tổng hợp, Phân tích và Thống kê số liệu - Phạm Mạnh Cường

Chương 6. TỔNG HỢP, PHÂN TÍCH VÀ THỐNG KÊ SỐ LIỆUCHỨC NĂNG SUB TOTALÝ nghĩa: Thống kê, tính toán theo từng nhóm dữ liệu trên những trường được chỉ định của cơ sở dữ liệu. Excel sẽ tự động phân tách nhóm dữ liệu, tính toán và chèn kết quả tổng hợp và đầu hoặc cuối mỗi nhóm.Cách thực hiện:Bước 1: Sắp xếp cơ sở dữ liệu theo trường nào cần phân nhóm để thống kê, tính toán;Bước 2: Chọn cơ sở dữ liệu sau đó mở mục chọn Data\ Sub Total; 2CHỨC NĂNG SUB TOTALBước 3: Xác định các thông số sau:At each change in: Chọn trường dùng để phân nhóm;Use Function: Chọn hàm để thống kê;Add Sub Total to: Chọn những trường nào muốn thực hiện thống kê trên đó;Replace current subtotals: Thay thế những giá trị subtotal hiện có;3CHỨC NĂNG SUB TOTALPage Break between groups: chọn, nếu muốn tạo dấu ngắt trang giữa các nhóm;Summary below data: chọn, muốn dòng tổng kết được chèn ở sau mỗi nhóm;Remove All: Xóa tất cả những Subtotal hiện có;4CHỨC NĂNG CONSOLIDATEÝ nghĩa: Thống kê, kết hợp trên các dữ liệu tương ứng của nhiều Sheet hoặc Book và kết xuất kết quả vào 1 Sheet tổng hợp nào đó.Cách thực hiện:Bước 1: Chọn cơ sở dữ liệu;Bước 2: Mở mục chọn Data\ Consolidate; xác đinh các thông số sau:5CHỨC NĂNG CONSOLIDATEFunction: Chọn hàm để tính;Reference: Xác định phạm vi dữ liệu;All reference: Liệt kê tất cả các phạm vi dữ liệu tham gia kết hợp.Use label in:Top Row: Dùng dòng dữ liệu đầu tiên bên trên là tiêu đề cột;Left Column: Dùng cột dữ liệu đầu tiên bên trái là tiêu đề dòng;6CHỨC NĂNG CONSOLIDATECreate links to source data: chọn, nếu muốn tạo liên kết với dữ liệu nguồn;Browse: liệt kê danh sách các file Workbook.Add: Bổ sung 1 phạm vi được chọn từ Reference vào All reference.Delete: Xóa 1 phạm vi( Nếu lỡ xác định sai).7CHỨC NĂNG CONSOLIDATEChọn hàm để tínhXác định phạm vi dữ liệuLiệt kê tất cả các phạm vi dữ liệu tham gia kết hợpliệt kê DS các fileThêmXóa phạm viDùng dòng dữ liệu đầu tiên bên trên là tiêu đề cộtDùng cột dữ liệu đầu tiên bên trái là tiêu đề dòngtạo liên kết với dữ liệu nguồn8CHỨC NĂNG CONSOLIDATEChú ý Ta có thể tạo khung của bảng thống kê trước, với điều kiện là các tên trường và các Label(nhãn) của cơ sở dữ liệu này phải hoàn toàn giống hệt các tên trường và các (nhãn) của cơ sở dữ liệu(trật tự có thể khác).9TỔNG HỢP THỐNG KÊ VÀ PHÂN TÍCH SỐ LIỆU VỚI PIVOTTABLEÝ nghĩa: Pivot Table là một công cụ để tổng hợp và phân tích số liệu từ một cơ sở dữ liệu. Ngoài khả năng nhóm dữ liệu theo dạng hàng cột (2D) Pivot Table còn tạo báo cáo dạng 3 chiều.(3S)10Chức năng:Tạo ra bảng tổng kếtTổ chức bảng dữ liệu bằng phương pháp kéo thảLọc và nhóm dữ liệuVẽ biểu đồ từ PivotTableTỔNG HỢP THỐNG KÊ VÀ PHÂN TÍCH SỐ LIỆU VỚI PIVOTTABLE11TỔNG HỢP THỐNG KÊ VÀ PHÂN TÍCH SỐ LIỆU VỚI PIVOTTABLEPage FieldData FieldColumn FieldRowField12Thực hiện:Quét khối chọn vùng số liệu Data\ Pivot Table and PivotChart Report ->xuất hiện hộp thoại Pivot Table and PivotChart Wizard:TỔNG HỢP THỐNG KÊ VÀ PHÂN TÍCH SỐ LIỆU VỚI PIVOTTABLE13Bước 2: Lần lượt làm theo từng bước của quá trình tạo bảng. Cụ thể như sau:Step 1: Chọn loại dữ liệu dùng để phân tích trong việc tạo bảng.Microsoft Excel List or Database: dữ liệu là cơ sở dữ liệu của Excel.External data source: Dữ liệu là cơ sở dữ liệu của một chương trình khác.Multiple consolidation ranges: Dữ liệu là nhiều vùng cơ sở dữ liệu.Another Pivot Table: dữ liệu là bảng Pivot Table khác.Nhấn nút Next để sang bước tiếp theo:TỔNG HỢP THỐNG KÊ VÀ PHÂN TÍCH SỐ LIỆU VỚI PIVOTTABLE14Step 2: Xác định phạm vi dữ liệu dùng để tạo bảngStep 3: Xác định vị trí đặt của Pivot Table, Chọn nút Layout (có thể thực hiện sau khi tạo Pivot table) Xác định Field nào chứa dữ liệu mà ta muốn tổng hợp hoặc phân tích rồi kéo vào phần DATAChọn giá trị sắp xếp theo cột với nhãn nằm phía trên, kéo vào vùng ColumnSắp xếp theo hàng, kéo thả vào RowThống kê theo vùng, kéo thả vào PageTỔNG HỢP THỐNG KÊ VÀ PHÂN TÍCH SỐ LIỆU VỚI PIVOTTABLE15Step 3: Options: Xác định thêm một số thông số khác. Ta quan tâm các thông số sau:Name: đặt tên cho Pivot table.Format options: gồm các thông số:Grand total for columns: Chọn, nếu muốn hiển thị dòng tổng cộng cho các dòng ở bên dưới bảng.AutoFormat table: chọn, nếu muốn thực hiện việc định dạng tự động cho bảng.Merge labels: canh giữa các nhãn của Pivot Table.TỔNG HỢP THỐNG KÊ VÀ PHÂN TÍCH SỐ LIỆU VỚI PIVOTTABLE16Preserve formatting: giữ lại trật tự các định dạng cảu bảng.Page layout: thay đổi trật tự của bố cục bảng.For error values; show: bật/tắt nội dung sẽ hiển thị khi có lỗi sai trị.For empty cells; show: bật/tắt nội dung sẽ hiển thị khi có ô rỗng.TỔNG HỢP THỐNG KÊ VÀ PHÂN TÍCH SỐ LIỆU VỚI PIVOTTABLE17Data Option: Data source options: gồm các thông số sauSave datawith table layout: lưu loại dữ liệu External lên bảng hiện hành.Enable drilldown: bật/tắt tính năng drilldown- tính năng hiển thị thông tin chi tiết tại một ô thống kê nào đó trên bảng Pivot Table khi ta nhấn kép chuột tại đó.Refresh on Open: cập nhật lại dữ liệu khi mở ra.External data options: một số thông số khác đối với loại dữ liệu External. TỔNG HỢP THỐNG KÊ VÀ PHÂN TÍCH SỐ LIỆU VỚI PIVOTTABLE18Hiệu chỉnh PivotTable:Thay đổi cấu trúc PivotTableThay đổi hàm tổng kết cho FieldSử dụng thanh công cụ để hiệu chỉnhTỔNG HỢP THỐNG KÊ VÀ PHÂN TÍCH SỐ LIỆU VỚI PIVOTTABLE19Thay đổi cấu trúc PivotTableSử dụng PivotTable Wizard: Chọn nút PivotTable  PivotTable Wizard, thực hiện giống phần thiết kếKéo thả Field: Kéo thả Field tương ứng từ PivotTable Field List vào các vùng tương ứng trên PivotTable và ngược lại nếu muốn bỏ đi.TỔNG HỢP THỐNG KÊ VÀ PHÂN TÍCH SỐ LIỆU VỚI PIVOTTABLE20Thay đổi hàm tổng kết cho FieldKích chuột phải tại vùng Data của PivotTable, chọn Field Setting..Chọn hàm tính toánhàm tính toánTỔNG HỢP THỐNG KÊ VÀ PHÂN TÍCH SỐ LIỆU VỚI PIVOTTABLE21