Bài giảng Thực hành quản trị trên máy - Bài 2: Báo cáo tổng hợp

1Bài 2: 2NỘI DUNG 2.1. Báo cáo tổng hợp nhóm (SubTotal) 2.1.1. Khái niệm 2.1.2. Trình tự tạo báo cáo 2.1.3. Khai thác thông tin trên báo cáo 2.2. Báo cáo PivotTable 2.2.1. Tổng quan 2.2.2. Trình tự tạo PivotTable 2.2.3. Tổ chức dữ liệu 2.2.4. Hoàn thiện PivotTable 32.1. Báo cáo tổng hợp nhóm (SubTotal) 42.1.1. Khái niệm 1. Tổng giá trị đặt hàng? Giá trị đặt hàng theo từng nhân viên ?? 2. Số lượng đơn đặt hàng? Số lượng đơn hàng mỗi nhân viên đã thực hiện ?? ?Grand & Sub Total Order List Order 1 Order 2 Order n 5SubTotals dùng tổng hợp dữ liệu theo nhóm Yêu cầu tổ chức dữ liệu: danh sách – Nhãn mỗi cột đặt ở dòng đầu tiên. – Mỗi cột có cùng kiểu dữ liệu – Không có dòng, cột rỗng chen giữa danh sách Xác định cột phân nhóm và giá trị cần tổng hợp Chuẩn bị    cột phân nhóm  cột tổng hợp Ví dụ: giá trị đặt hàng theo từng nhân viên 2.1.2. Trình tự tạo báo cáo 62.1.2. Trình tự tạo báo cáo SubTotals dùng tổng hợp dữ liệu theo nhóm trong danh sách Cột phân nhóm Hàm tổng hợp Cột chứa giá trị tổng hợp 1.Sắp thứ tự theo cột phân nhóm 2.Chọn danh sách 3.Ra lệnh Data, SubTotals Ra lệnh 72.1.3. Khai thác thông tin trên báo cáo SubTotal Drill down Show and Hide details Outline symbol 82.1 Báo cáo tổng hợp nhóm (SubTotal) Tạo báo cáo SubTotal 1. Doanh số của nhân viên 2. Doanh số bán được theo các quốc gia 3. Số lượng đơn đặt hàng mỗi nhân viên đã thực hiện 4. Thực hành 9Doanh số nhân viên mỗi năm & năm của nhân viên Nhân viên  Năm Năm  Nhân viên Nhân viên Năm 2.2. Báo cáo PivotTable 2.2.1. Tổng quan 10 2.2.1. Tổng quan PivotTable present comparisons, reveal patterns and relationships, and analyze trends. Công cụ tổ chức và tổng hợp đa chiều Year Salesperson Countries Doanh số nhân viên mỗi năm theo từng quốc gia 11 2.2.1. Tổng quan Kaleidoscope ??? 12 2.2.1. Tổng quan Kaleidoscope • Vật liệu: Giấy vụn • Kết quả: hoa văn • Cách làm: xoay kính • Hoạt động: ngẫu nhiên PivotTable • Vật liệu: data • Kết quả: thông tin (view) • Cách làm: xoay bảng • Hoạt động: chủ định theo yêu cầu PivotTable 13 2.2.2. Trình tự tạo PivotTable 1. Đặt câu hỏi. 2. Tạo PivotTable. 3. Dàn dựng PivotTable. 1 2 3 How To? 1.Ask yourself questions. 2.Create PivotTable. 3.Lay out the PivotTable report. 14 2.2.2. Trình tự tạo PivotTable Hãy bắt đầu bằng cách tự hỏi xem bạn cần biết cái gì: • Giá trị đặt hàng mỗi nhân viên đã thực hiện? • Giá trị đặt hàng theo từng quốc gia? • Sales figures that go on and on Hãy xác định cái mà bạn cần biết ① Đặt câu hỏi 15 2.2.2. Trình tự tạo PivotTable Sau khi đã sẵn sàng, hãy làm theo các bước sau: 1.Click chọn trong danh sách. 2. Ra lệnh Insert -> PivotTable. ② Tạo PivotTable 2 1 16 2.2.2. Trình tự tạo PivotTable 3. Xuất hiện hộp thoại Create PivotTable, chọn dữ liệu nguồn và nơi chứa PivotTable, rồi click nút OK. ② Tạo PivotTable 3 •Chuẩn bị vùng “Layout area” để “tổ chức” PivotTable. 17 2.2.2. Trình tự tạo PivotTable • Ở bên trái là vùng báo cáo PivotTable • Ở bên phải là cửa sổ PivotTable Field List Layout Area ③ Dàn dựng PivotTable 18 2.2.2. Trình tự tạo PivotTable PivotTable Field List chứa tất cả tên các cột của bảng dữ liệu gốc như: OrderID, Salesperson, Order Date, Order Amount và Country. Lưu ý: Mỗi cột dữ liệu  vùng (field) có cùng tên. PivotTable Field List ③ Dàn dựng PivotTable 19 2.2.2. Trình tự tạo PivotTable Bạn drag các field từ Field List vào các khung bên dưới để xác định cấu trúc báo cáo ③ Dàn dựng PivotTable 4 khu vực: – Report Filter – Row Labels – Column Labers – Values 20 2.2.2. Trình tự tạo PivotTable Nếu bạn drag vùng Salesperson vào trong: • Khung Row Labels mỗi nhân viên được bố trí trên 1 hàng • Khung Column Labels  mỗi cột báo cáo chứa dữ liệu của một nhân viên. Khu vực Row vs Column Labels ③ Dàn dựng PivotTable 21 2.2.2. Trình tự tạo PivotTable Để trả lời câu hỏi “Giá trị đặt hàng mỗi nhân viên đã thực hiện?” cần phải sử dụng dữ liệu của 2 vùng: 1. Salesperson  tên nhân viên  Khung Row Labels 2. Order Amount  giá trị đặt hàng  Khung Values Khu vực Values ③ Dàn dựng PivotTable 22 2.2.2. Trình tự tạo PivotTable • Chỉ được drop field “số ” vào khu vực Values • Kết quả báo cáo sẽ được hiển thị ngay sau khi khai báo khu vực Values. • Các field trong khu vực Values được xử lý mặc định bằng phép cộng. • Bảng Pivot có thể in ra giấy, copy vào Word, hoặc xuất lên trang Web. Khu vực Data Items ③ Dàn dựng PivotTable 23 2.2.2. Trình tự tạo PivotTable Khi yêu cầu thay đổi  bạn có thể • Thêm field mới vào báo cáo • Bỏ bớt field không cần thiết. • Thay đổi cấu trúc (pivot) bằng cách drag các field từ vị trí này sang vị trí khác. The PivotTable report Thay đổi linh hoạt ③ Dàn dựng PivotTable 24 2.2.2. Trình tự tạo PivotTable 1. Tạo mẫu báo cáo ”report view”. 2. Bố trí dữ liệu trong PivotTable Layout. 3. “Pivot” báo cáo. 4. Drag fields ra khỏi báo cáo. Bài thực hành (Pivot1.xlsx) Thực hành 25 2.2.3. Tổ chức dữ liệu 1. Hàng đầu tiên chứa tiêu đề cột và không trùng lắp. 2. Không có hàng/ cột rỗng. 3. Mỗi cột chỉ chứa cùng 1 kiểu dữ liệu 4. Phải xóa các subtotal và grand totals đã tạo trước đó (nếu có) Yêu cầu đối với dữ liệu gốc 26 2.2.3. Tổ chức dữ liệu 1. Chuẩn bị tốt dữ liệu 2. Xác định đúng câu hỏi  chọn đúng field cần dùng 3. Thả các field vào đúng chỗ Và cuối cùng: đừng sợ làm sai khi tổ chức khu vực layout. Để dàn dựng thành công PivotTable 27 2.2.3. Tổ chức dữ liệu 1. Tạo báo cáo PivotTable report 2. Thay đổi cách dàn dựng 3. Cách dùng lệnh để di chuyển các fields vào khu vực dàn dựng Bài thực hành (Pivot2.xlsx) Thực hành 28 2.2.4. Hoàn thiện PivotTable 1. PivotTable tự tạo ra các ô có nền xám khi thả các field vào vùng layout. 2. Nội dung trong các ô này được tạo tự động dựa theo tên field và nơi bố trí 3. Có thể thay đổi nội dung để báo cáo rõ ràng hơn Ô màu xám chứa tên field Excel inserts gray boxes containing field names. ① Xử lý ô màu xám 29 2.2.4. Hoàn thiện PivotTable 1. Bấm vào mũi tên bên phải của của nhãn hàng hoặc cột muốn sắp xếp. 2. Bấm vào tuỳ chọn sắp xếp. 3. Sort and Top 10 ② Sắp thứ tự 30 2.2.4. Hoàn thiện PivotTable ② Sắp thứ tự Sort and Top 10 Làm thế nào để hiển thị 10 nhân viên có doanh số cao nhất? 31 2.2.4. Hoàn thiện PivotTable 1. Nhấn mũi tên tên xổ xuống cạnh field cần đinh dạng trong vùng Values. 2. Chọn Value Field Settings trong danh sách mở ra. 3. Nhấn vào nút Number Format để mở hộp thoại Format Cells. 4. Chọn kiểu định dạng số thích hợp. 5. Nhấn OK hai lần để trở về PivotTable. Format Number ③ Định dạng số 32 2.2.4. Hoàn thiện PivotTable • Nhấn vào nút Refresh nằm trong tab PivotTable|Option trên Ribbon để cập nhật báo cáo khi có sự thay đổi. Refresh Data ④ Cập nhật dữ liệu 33 2.2.4. Hoàn thiện PivotTable 1. Nhấn mũi tên tên xổ xuống cạnh field cần định dạng trong vùng Values. 2. Chọn Value Field Settings trong danh sách mở ra. 3. Chọn phương thức xử lý trong mục Summarize value field by Không chỉ có SUM ⑤ Tổng hợp dữ liệu 34 2.2.4. Hoàn thiện PivotTable 1. Đổi tên vùng. 2. Thay đổi thứ tự sắp xếp 3. Định dạng lại số. 4. Thay đổi cách thức tổng hợp số liệu. 5. Hủy bỏ báo cáo. Bài thực hành (Pivot3.xlsx) Thực hành 35 2.2.4. Hoàn thiện PivotTable Để bổ sung thông tin thời gian vào báo cáo 1. Hiển thị lại PivotTable Field List. 2. Drag vùng Order Date vào vị trí sau Salesperson trong vùng Row Lables 3. Đổi tên vùng Order Date thành Qtr Lưu ý: giá trị trong cột Order Date là ngày bán, chưa được quy đổi ra quý. Tạo báo cáo Quý ⑥ Group & Show Detail 36 2.2.4. Hoàn thiện PivotTable 1. Right-click vùng Qtr 2. Chọn Group, sau đó chọn Quarters. Phân nhóm ⑥ Group & Show Detail 37 2.2.4. Hoàn thiện PivotTable Group and Show Detail cho phép phân tích báo cáo ở mức tổng hợp hay chi tiết hơn • Có khả năng group dữ liệu thời gian theo seconds, minutes, hours, days, Công dụng • Để hủy nhóm: Right—Click vào field, chọn Ungroup ⑥ Group & Show Detail 38 2.2.4. Hoàn thiện PivotTable Để hiển thị thời gian, bạn cần thêm vùng Order Date mới. Vấn đề là để vùng Order Date vào vị trí row hay column? Nếu đưa vào vị trí “row” thì: – Trước “Salesperson”? hay – Sau “Salesperson”? – Bất kỳ? A PivotTable report before and after the Order Date field is added to it. Row & Column ⑦ Inner–Outer Row Field 39 2.2.4. Hoàn thiện PivotTable Trước và sau 1. Doanh thu mỗi quý của nhân viên 2. Doanh thu mỗi nhân viên theo quý ⑦ Inner–Outer Row Field 40 2.2.4. Hoàn thiện PivotTable Khi có nhiều row fields thì: • Duy nhất 1 “inner row field” ở sát vùng dữ liệu. • Còn lại là các “outer row fields”.  Các giá trị Outer field ngoài cùng xuất hiện 1 lần.  Các giá trị ở field khác sẽ xuất hiện lặp lại khi cần. Whether a row field is an inner or outer field determines how the data is displayed (repeated). Khái niệm ⑦ Inner–Outer Row Field 41 2.2.4. Hoàn thiện PivotTable 1. Sử dụng 2 row fields 2. Tạo báo cáo quý 3. Pivot báo cáo để quan sát thông tin khác 4. Bài thực hành (Pivot4.xlsx) Thực hành 42 2.2.4. Hoàn thiện PivotTable Báo cáo trước cho biết giá trị đặt hàng của nhân viên. Yêu cầu  xem cùng lúc thông tin về doanh số và số lượng đơn đặt hàng của nhân viên PivotTable reports can do more than add. Vấn đề ⑧ Thêm Data Item 43 2.2.4. Hoàn thiện PivotTable Đưa thêm vùng Order Amount vào khu vực Value Khi đưa vùng Order Amount vào lần thứ 2, Excel sẽ:  Cộng giá trị đặt hàng 1 lần nữa và đặt tên Sum of Order Amount2. Đưa nhiều field vào khu vực Data Items ⑧ Thêm Data Item 44 2.2.4. Hoàn thiện PivotTable 1. Right Click vào 1 trong các ô Sum of Order Amount2. 2. Chọn Value Field Settings trong danh sách mở ra. 3. Chọn Count trong danh sách Summarize value field by Changing the summary from Sum to Count in the PivotTable Field dialog box. Đếm số đơn hàng ⑧ Thêm Data Item 45 2.2.4. Hoàn thiện PivotTable 1. Nhấn vào trong báo cáo. 2. Chọn Design trên PivotTable Tools 3. Chọn 1 trong các định dạng thích hợp trong PivotTable Style. AutoFormat ⑨ Format bảng Pivot 46 2.2.4. Hoàn thiện PivotTable 1. Right Click vào 1 trong các ô Sum of Order Amount 2. Chọn Value Field Settings trong danh sách mở ra. 3. Click mục Show values as, chọn % of Gand Total trong danh sách Show values as. 4. OK Show data as a percentage of the total instead of as the number of orders. Tính tỷ trọng giá trị đặt hàng của nhân viên ⑩ Đổi cách xử lý 47 2.2.4. Hoàn thiện PivotTable 1. Tổng hợp 1 vùng dữ liệu theo nhiều cách khác nhau 2. Đếm số đơn hàng và thể hiện ở dạng tỷ trọng. 3. Định dạng report. Bài thực hành (Pivot5.xlsx) Thực hành