Bài 2:

1

NỘI DUNG

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.1. Báo cáo tổng hợp nhóm (SubTotal)

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

2

2.2. Báo cáo PivotTable

2.1. Báo cáo tổng hợp nhóm (SubTotal)

3

2.1.1. Khái niệm

Grand & Sub Total

?

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 ?? Order List Order 1

Order n

Order 2

4

2.1.2. Trình tự tạo báo cáo

Chuẩn bị

SubTotals 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

Ví dụ: giá trị đặt hàng theo từng nhân viên

 cột phân nhóm  cột tổng hợp

5

2.1.2. Trình tự tạo báo cáo

Ra lệnh SubTotals dùng tổng hợp dữ liệu theo nhóm trong

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

6

danh sách

2.1.3. Khai thác thông tin trên báo cáo SubTotal

Drill down

Outline symbol

Show and Hide details

7

2.1 Báo cáo tổng hợp nhóm (SubTotal)

Thực hành

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

8

4. … … … …

2.2. Báo cáo PivotTable

2.2.1. Tổng quan

Doanh số nhân viên mỗi năm & năm của nhân viên

Năm

Nhân viên

Nhân viên  Năm

Năm  Nhân viên

9

2.2.1. Tổng quan

Công cụ tổ chức và tổng hợp đa chiều Year

PivotTable … present comparisons, reveal patterns and relationships, and analyze trends.

Salesperson

Countries

10

Doanh số nhân viên mỗi năm theo từng quốc gia

2.2.1. Tổng quan

Kaleidoscope ???

11

2.2.1. Tổng quan

PivotTable

Kaleidoscope

PivotTable

• Vật liệu: Giấy vụn

• Vật liệu: data

• Kết quả: hoa văn

• Kết quả: thông tin (view)

• Cách làm: xoay kính

• Cách làm: xoay bảng

• Hoạt động: ngẫu nhiên

• Hoạt động: chủ định theo

yêu cầu

12

2.2.2. Trình tự tạo PivotTable

How To?

1

1. Đặt câu hỏi. 2

2. Tạo PivotTable.

1.Ask yourself questions.

2.Create PivotTable.

3.Lay out the PivotTable report.

13

3 3. Dàn dựng PivotTable.

2.2.2. Trình tự tạo PivotTable

① Đặt câu hỏi ① Đặt câu hỏi

Hãy xác định cái mà bạn cần biết

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?

Sales figures that go on and on

• Giá trị đặt hàng theo từng quốc gia?

14

• ……

2.2.2. Trình tự tạo PivotTable

② Tạo PivotTable ② 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.

1 2. Ra lệnh Insert -> PivotTable.

15

2

2.2.2. Trình tự tạo PivotTable

② Tạo PivotTable ② 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.

3

16

•Chuẩn bị vùng “Layout area” để “tổ chức” PivotTable.

2.2.2. Trình tự tạo PivotTable

③ Dàn dựng PivotTable ③ Dàn dựng PivotTable

Layout Area

• Ở bên trái là vùng báo cáo PivotTable

17

• Ở bên phải là cửa sổ PivotTable Field List

2.2.2. Trình tự tạo PivotTable

③ Dàn dựng PivotTable ③ Dàn dựng PivotTable

PivotTable Field List

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.

18

Lưu ý: Mỗi cột dữ liệu  vùng (field) có cùng tên.

2.2.2. Trình tự tạo PivotTable

③ Dàn dựng PivotTable ③ Dàn dựng PivotTable

– Report Filter

– Row Labels

– Column Labers

– Values

4 khu vực:

19

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

2.2.2. Trình tự tạo PivotTable

③ Dàn dựng PivotTable ③ Dàn dựng PivotTable

Khu vực Row vs Column Labels

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

20

• Khung Column Labels  mỗi cột báo cáo chứa dữ liệu của một nhân viên.

2.2.2. Trình tự tạo PivotTable

③ Dàn dựng PivotTable ③ Dàn dựng PivotTable

Khu vực Values Để 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

21

2. Order Amount  giá trị đặt hàng  Khung Values

2.2.2. Trình tự tạo PivotTable

③ Dàn dựng PivotTable ③ Dàn dựng PivotTable

Khu vực Data Items

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

22

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

2.2.2. Trình tự tạo PivotTable

③ Dàn dựng PivotTable ③ Dàn dựng PivotTable

Thay đổi linh hoạt

Khi yêu cầu thay đổi  bạn có

thể

• Thêm field mới vào báo cáo

The PivotTable report

• Bỏ bớt field không cần thiết.

23

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

2.2.2. Trình tự tạo PivotTable

Thực hành

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.

Bài thực hành (Pivot1.xlsx)

24

4. Drag fields ra khỏi báo cáo.

2.2.3. Tổ chức dữ liệu

Yêu cầu đối với dữ liệu gốc

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

25

4. Phải xóa các subtotal và grand totals đã tạo trước đó (nếu có)

2.2.3. Tổ chức dữ liệu

Để dàn dựng thành công PivotTable

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ỗ

26

Và cuối cùng: đừng sợ làm sai khi tổ chức khu vực layout.

2.2.3. Tổ chức dữ liệu

Thực hành

1. Tạo báo cáo PivotTable

report

2. Thay đổi cách dàn dựng

Bài thực hành (Pivot2.xlsx)

27

3. Cách dùng lệnh để di chuyển các fields vào khu vực dàn dựng

2.2.4. Hoàn thiện PivotTable

① Xử lý ô màu xám ① Xử lý ô màu xám

Ô màu xám chứa tên field 1. PivotTable tự tạo ra các ô thả các

có nền xám khi 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í

inserts gray boxes

Excel containing field names.

3. Có thể thay đổi nội dung để

28

báo cáo rõ ràng hơn

2.2.4. Hoàn thiện PivotTable

② Sắp thứ tự ② Sắp thứ tự

Sort and Top 10

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.

29

3. …

2.2.4. Hoàn thiện PivotTable

② Sắp thứ tự ② 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?

30

2.2.4. Hoàn thiện PivotTable

③ Định dạng số ③ Định dạng số

Format Number

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.

lần để trở về

31

5. Nhấn OK hai PivotTable.

2.2.4. Hoàn thiện PivotTable

④ Cập nhật dữ liệu ④ Cập nhật dữ liệu

Refresh Data

32

• Nhấn vào nút Refresh nằm tab trong PivotTable|Option trên Ribbon để cập nhật báo cáo khi có sự thay đổi.

2.2.4. Hoàn thiện PivotTable

⑤ Tổng hợp dữ liệu ⑤ Tổng hợp dữ liệu

Không chỉ có SUM

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

33

value field by

2.2.4. Hoàn thiện PivotTable

Thực hành

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.

34

Bài thực hành (Pivot3.xlsx)

2.2.4. Hoàn thiện PivotTable

⑥ Group & Show Detail ⑥ Group & Show Detail

Tạo báo cáo Quý

Để 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ý.

35

2.2.4. Hoàn thiện PivotTable

⑥ Group & Show Detail ⑥ Group & Show Detail

Phân nhóm

1. Right-click vùng Qtr

2. Chọn Group, sau đó chọn Quarters.

36

2.2.4. Hoàn thiện PivotTable

⑥ Group & Show Detail ⑥ Group & Show Detail

Công dụng 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

gian

• Để hủy nhóm: Right—Click vào field,

chọn Ungroup

37

• Có khả năng group dữ liệu seconds, theo thời minutes, hours, days, …

2.2.4. Hoàn thiện PivotTable

⑦ Inner–Outer Row Field ⑦ Inner–Outer Row Field

Row & Column

Để hiển thị

thời gian, bạn cần thêm vùng Order Date mới.

Vấn đề là để vùng Order row hay trí

Date vào vị column?

A PivotTable report before and after the Order Date field is added to it.

– Trước “Salesperson”? hay

– Sau “Salesperson”?

– Bất kỳ?

38

Nếu đưa vào vị trí “row” thì:

2.2.4. Hoàn thiện PivotTable

⑦ Inner–Outer Row Field ⑦ Inner–Outer Row Field

Trước và sau

1. Doanh thu mỗi quý của

nhân viên

39

2. Doanh thu mỗi nhân viên theo quý

2.2.4. Hoàn thiện PivotTable

⑦ Inner–Outer Row Field ⑦ Inner–Outer Row Field

Khái niệm

Khi có nhiều row fields thì:

• Duy nhất 1 “inner row field”

ở sát vùng dữ liệu.

là các “outer row

• Còn lại fields”.

 Các giá trị Outer field ngoài

Whether a row field is an inner or outer field determines how the data is displayed (repeated).

cùng xuất hiện 1 lần.

 Các giá trị ở field khác sẽ

40

xuất hiện lặp lại khi cần.

2.2.4. Hoàn thiện PivotTable

Thực hành

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

Bài thực hành (Pivot4.xlsx)

41

4. …

2.2.4. Hoàn thiện PivotTable

⑧ Thêm Data Item ⑧ Thêm Data Item

Vấn đề

Báo cáo trước cho biết giá trị

đặt hàng của nhân viên.

PivotTable reports can do

more than add.

42

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

2.2.4. Hoàn thiện PivotTable

⑧ Thêm Data Item ⑧ Thêm Data Item

Đưa nhiều field vào khu vực Data Items

Đư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.

43

2.2.4. Hoàn thiện PivotTable

⑧ Thêm Data Item ⑧ Thêm Data Item

Đếm số đơn hàng

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 value

sách Summarize field by

Changing summary the from Sum to Count in the PivotTable Field dialog box.

44

2.2.4. Hoàn thiện PivotTable

⑨ Format bảng Pivot ⑨ Format bảng Pivot

AutoFormat

1. Nhấn vào trong báo cáo.

2. Chọn Design trên PivotTable

Tools

45

3. Chọn 1 trong các định dạng thích hợp trong PivotTable Style.

2.2.4. Hoàn thiện PivotTable

⑩ Đổi cách xử lý ⑩ Đổi cách xử lý

Tính tỷ trọng giá trị đặt hàng của nhân viên 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, trong

chọn % of Gand Total danh sách Show values as.

4. OK

as

a data Show percentage of the total instead of as the number of orders.

46

2.2.4. Hoàn thiện PivotTable

Thực hành 1. Tổng hợp 1 vùng dữ liệu nhiều cách khác

theo nhau

2. Đếm số đơn hàng và thể

hiện ở dạng tỷ trọng.

Bài thực hành (Pivot5.xlsx)

47

3. Định dạng report.