Ứng dụng Pivot table - Phần 2

[Thanh Nam]

PHẦN 2: PIVOT TABLE NÂNG CAO

Tác giả bài viết: Phạm Thành Mỹ

I. YÊU CẦU CƠ BẢN KHI THỰC HIỆN PIVOT TABLE

Đã nói về Pivot Table là nói về tổng hợp và phân tích.
Đã nói về phân tích là phải có tiêu chí phân tích.
- Tiêu chí phân tích là bất kỳ tiêu chí nào có thể dùng để phân loại dữ liệu. Thí dụ tiêu chí loại chi phí, tiêu chí kỳ báo cáo, tiêu chí nhóm sản phẩm, tiêu chí vùng thị trường, tiêu chí loại khách hàng, thậm chí phân loại khách hàng cũng có nhiều cách phân loại: phân loại theo khách mua sỉ, lẻ; phân loại theo doanh số lớn nhỏ

- Phân loại dữ liệu cần chính xác, không trùng lắp, không mơ hồ. 1 dòng dữ liệu chỉ có thể nằm trong 1 loại, không được hơn. Thí dụ nếu đã có loại chi phí Văn Phòng thì không được có chi phí Điện thoại. Muốn có chi phí Điện thoại thì phải tách chi phí Văn Phòng ra: chi phí Điện thoại, mực in, văn phòng phẩm, lương nhân viên Văn Phòng, cái gì không cần quá chi tiết thì gộp vào chi phí linh tinh.
Đó là nói về phân tích, còn nói về Pivot Table: Pivot Table cũng yêu cầu chính xác y như vậy thậm chí nếu bạn gõ sai chính tả, dư dấu space, thiếu dấu sắc, dấu huyền; Pivot cũng coi như 1 loại mới và tách riêng ra.
Thí dụ trong bảng Pivot Table trên nếu tất cả dữ liệu cột Thành phố Hồ Chí Minh trong cột tỉnh là “HCM”, lọt vào 1 ô “HCM ” (dư 1 khoảng trắng) thì sẽ bị tách riêng ra 1 dòng như sau: ô tô màu cam là ô gõ dư khoảng trắng, thể hiện dòng HCM thứ 2 trên Pivot Table.

|

A​

|

B​

|

C​

|

1​

|

Tỉnh​

|

Đại lý​

|

Total​

|

2​

|An Giang|C|

108799000​

|

3​

| |D|

325820000​

|

4​

|Hà nội|E|

166348000​

|

5​

| |F|

107271000​

|

6​

|HCM|A|

39071000​

|

7​

| |B|

60188000​

|

8​

| |H|

89886000​

|

9​

| |I|

49433000​

|

10​

|HCM |A|

82091000​

|

11​

|Huế|G|

174133000​

|

12​

| |J|

118448000​

|

13​

|Grand Total| |

1321488000​

|

Như vậy để cho Pivot Table làm tốt bạn phải làm theo đúng cách. Tôi gợi ý 1 quy trình như sau:
1. Lấy dữ liệu thô
2. Chọn tiêu chí phân tích
3. Lập 1 danh sách các loại dữ liệu theo tiêu chí trên, phân bao nhiêu loại tùy bạn, nhưng tên từng loại phải chính xác và ngắn gọn. Nếu tiêu chí này có sẵn trong dữ liệu thô: kiểm tra tính chính xác của các loại dữ liệu trong tiêu chí đó. Nếu việc kiểm tra khó khăn do dữ liệu nhiều, tốt nhất là làm 1 Danh sách mới.
4. Thêm 1 cột vào dữ liệu thô. Trong cột này mỗi dòng dữ liệu phải chọn 1 loại trong Danh sách đã làm ở bước 3, chính xác từng ký tự. Tốt nhất là dùng validation để chọn.
5. Nếu bạn cần phân tích nhiều tiêu chí, làm lại bước 3 và 4 cho mỗi tiêu chí.
6. Tiến hành tạo Pivot Table.

II. CÁC PHƯƠNG PHÁP THỐNG KÊ ĐỐI VỚI TRƯỜNG DỮ LIỆU

A. Các phép thống kê thông dụng:
Pivot table không chỉ có thống kê tổng, nó có thể thống kê số lượng, số lớn nhất, nhỏ nhất, số trung bình, ...
Thí dụ ta có bảng điểm thi 5 môn của học sinh 3 lớp như sau:

|

A​

|

B​

|

C​

|

D​

|

E​

|

17​

|

STT​

|

Tên HS​

|

Lớp​

|

Môn​

|

Điểm​

|

18​

|

1​

|Nguyễn văn 01|

6A1​

|Toán|

8,3​

|

19​

|

2​

|Nguyễn văn 02|

6A1​

|Văn|

6,9​

|

20​

|

3​

|Nguyễn văn 03|

6A1​

|Lý|

9,7​

|

21​

|

4​

|Nguyễn văn 04|

6A1​

|Hóa|

6,9​

|

22​

|

5​

|Nguyễn văn 05|

6A1​

|Sinh|

6,5​

|

23​

|

6​

|Nguyễn văn 06|

6A1​

|Toán|

9,2​

|

24​

|

7​

|Nguyễn văn 07|

6A2​

|Văn|

8,1​

|

25​

|

8​

|Nguyễn văn 08|

6A2​

|Lý|

10​

|

26​

|

9​

|Nguyễn văn 09|

6A2​

|Hóa|

8,9​

|

27​

|

10​

|Nguyễn văn 10|

6A2​

|Sinh|

7,4​

|

28​

|

11​

|Nguyễn văn 11|

6A2​

|Toán|

8​

|

29​

|

12​

|Nguyễn văn 12|

6A2​

|Văn|

7,8​

|

30​

|

13​

|Nguyễn văn 13|

6A3​

|Lý|

8,7​

|

31​

|

14​

|Nguyễn văn 14|

6A3​

|Hóa|

7,2​

|

32​

|

15​

|Nguyễn văn 15|

6A3​

|Sinh|

8,1​

|

33​

|

16​

|Nguyễn văn 16|

6A3​

|Toán|

7,7​

|

34​

|

17​

|Nguyễn văn 17|

6A3​

|Văn|

6,9​

|

Bây giờ ta muốn thống kê:
- Số học sinh mỗi lớp dự thi
- Điểm cao nhất của mỗi lớp
- Điểm thấp nhất của mỗi lớp
- Điểm trung bình mỗi lớp

Hãy tạo 1 Pivot table với cấu trúc như sau: Kéo thả Lớp vào Rows, Tên HS và điểm vào Data:

|

A​

|

B​

|

C​

|

37​

||

Data​

||

38​

|

Lớp​

|

Count of Tên HS​

|

Sum of Điểm​

|

39​

|

6A1​

|

6​

|

47,5​

|

40​

|

6A2​

|

6​

|

50,2​

|

41​

|

6A3​

|

5​

|

38,6​

|

42​

|

Grand Total​

|

17​

|

136,3​

|

 

[Thanh Nam]

Vì trường tên HS là text, nên Excel tự gán công thức là count: 2 lớp có 6 HS và 1 lớp có 5 HS. Trường Điểm là số (number) nên Excel tự gán công thức Sum. Sửa trường Tên HS thành Số HS cho oai.

NHấn chuột phải vào trường “Sum of Điểm”, chọn field setting. Chọn lại là Max thay vì Sum, sửa tên field thành Điểm lớn nhất:


Kết quả:

|

H​

​

|

I​

​

|

J​

​

|

31​

​

|

Lớp​

​

|

Số HS​

​

|

Điểm lớn nhất​

​

|

32​

​

|

6A1​

|

6​

|

9,7​

|

33​

​

|

6A2​

|

6​

|

10​

|

34​

​

|

6A3​

|

5​

|

8,7​

|

35​

​

|

Grand Total​

|

17​

|

10​

|

Kéo thả Trường điểm vào 2 lần nữa, một lần là Min và 1 lần là Average ta sẽ có;

|

G​

​

|

H​

​

|

I​

​

|

J​

​

|

K​

​

|

38​

​

|

Lớp​

​

|

Số HS​

​

|

Điểm lớn nhất​

​

|

Điểm nhỏ nhất​

​

|

Điểm trung bình​

​

|

39​

​

|

6A1​

|

6​

|

9,7​

|

6,5​

|

7,92​

|

40​

​

|

6A2​

|

6​

|

10​

|

7,4​

|

8,37​

|

41​

​

|

6A3​

|

5​

|

8,7​

|

6,9​

|

7,72​

|

42​

​

|

Grand Total​

|

17​

|

10​

|

6,5​

|

8,02​

|

Nếu thay vì trường “Lớp”, ta kéo trường Môn vào Row, ta sẽ có:

|

B​

​

|

C​

​

|

D​

​

|

E​

​

|

F​

​

|

46​

​

|

Môn​

​

|

Số HS​

​

|

Điểm lớn nhất​

​

|

Điểm nhỏ nhất​

​

|

Điểm trung bình​

​

|

47​

​

|Hóa|

3​

|

8,9​

|

6,9​

|

7,67​

|

48​

​

|Lý|

3​

|

10​

|

8,7​

|

9,47​

|

49​

​

|Sinh|

3​

|

8,1​

|

6,5​

|

7,33​

|

50​

​

|Toán|

4​

|

9,2​

|

7,7​

|

8,3​

|

51​

​

|Văn|

4​

|

8,1​

|

6,9​

|

7,43​

|

52​

​

|

Grand Total​

|

17​

|

10​

|

6,5​

|

8,02​

|

 

[Thanh Nam]

A. Các phép thống kê đặc biệt:
Quay trở lại phần Doanh thu và Lợi nhuận của dữ liệu mẫu phần 1.

1. Phân tích % Doanh thu trên cả nước:

Kéo tỉnh và đại lý vào Row, Doanh thu kéo 2 lần vào Values: 1 lần là Sum, 1 lần là % of Total trong hộp thoại Field setting:

|

B​

​

|

C​

​

|

D​

​

|

E​

​

|

55​

​

|

Tỉnh​

​

|

Đại lý​

​

|

Doanh thu​

​

|

% Doanh thu cả nước​

​

|

56​

​

|An Giang|C|

108799000​

|

0,0823​

|

57​

​

| |D|

325820000​

|

0,2466​

|

58​

​

|An Giang Total| |

434619000​

|

0,3289​

|

59​

​

|Hà nội|E|

166348000​

|

0,1259​

|

60​

​

| |F|

107271000​

|

0,0812​

|

61​

​

|Hà nội Total| |

273619000​

|

0,2071​

|

62​

​

|HCM|A|

121162000​

|

0,0917​

|

63​

​

| |B|

60188000​

|

0,0455​

|

64​

​

| |H|

89886000​

|

0,068​

|

65​

​

| |I|

49433000​

|

0,0374​

|

66​

​

|HCM Total| |

320669000​

|

0,2427​

|

67​

​

|Huế|G|

174133000​

|

0,1318​

|

68​

​

| |J|

118448000​

|

0,0896​

|

69​

​

|Huế Total| |

292581000​

|

0,2214​

|

70​

​

|Grand Total| |

1321488000​

|

1​

|

Ta thấy doanh thu tỉnh An Giang chiếm 32,89% Doanh thu cả nước, trong đó đại lý C chiếm 8,23% và đại lý D, 24,66%. (cộng 32,89%).

1. Phân tích % tăng giảm Doanh thu từng tỉnh so với năm trước:

Kéo trường tỉnh và trường năm vào Rows, Doanh thu kéo 2 lần vào Data, 1 lần tính Sum và 1 lần % Difference from “năm”, Previous:

|

C​

​

|

D​

​

|

E​

​

|

F​

​

|

G​

​

|

77​

​

|

Tỉnh​

​

|

Năm​

​

|

​

​

|

Doanh thu​

​

|

% Tăng giảm Doanh thu so với năm trước​

​

|

78​

​

|An Giang|

2001​

| |

84590000​

| |

79​

​

| |

2002​

| |

29231000​

|

-0,6544​

|

80​

​

| |

2003​

| |

79568000​

|

1,722​

|

81​

​

| |

2004​

| |

70046000​

|

-0,1197​

|

82​

​

| |

2005​

| |

79373000​

|

0,1332​

|

83​

​

| |

2006​

| |

91811000​

|

0,1567​

|

84​

​

|An Giang Total| | |

434619000​

| |

85​

​

|Hà nội| |

2001​

|

41352000​

| |

86​

​

| | |

2002​

|

49898000​

|

0,2067​

|

87​

​

| | |

2003​

|

36131000​

|

-0,2759​

|

88​

​

| | |

2004​

|

80319000​

|

1,223​

|

89​

​

| | |

2005​

|

41373000​

|

-0,4849​

|

90​

​

| | |

2006​

|

24546000​

|

-0,4067​

|

91​

​

|Hà nội Total| | |

273619000​

| |

92​

​

|HCM| |

2001​

|

43949000​

| |

93​

​

| | |

2002​

|

45937000​

|

0,0452​

|

94​

​

| | |

2003​

|

49433000​

|

0,0761​

|

95​

​

| | |

2004​

|

121162000​

|

1,451​

|

96​

​

| | |

2005​

|

33449000​

|

-0,7239​

|

97​

​

| | |

2006​

|

26739000​

|

-0,2006​

|

98​

​

|HCM Total| | |

320669000​

| |

99​

​

|Huế| |

2001​

|

44527000​

| |

100​

​

| | |

2002​

|

26505000​

|

-0,4047​

|

101​

​

| | |

2003​

|

88149000​

|

2,3257​

|

102​

​

| | |

2004​

|

52858000​

|

-0,4004​

|

103​

​

| | |

2005​

|

47416000​

|

-0,103​

|

104​

​

| | |

2006​

|

33126000​

|

-0,3014​

|

105​

​

|Huế Total| | |

292581000​

| |

106​

​

|Grand Total| | |

1321488000​

| |

Có thể kết hợp mức tăng giảm và tỷ lệ tăng giảm so với năm trước:

|

I​

​

|

J​

​

|

K​

​

|

L​

​

|

M​

​

|

110​

​

|

Tỉnh​

​

|

Năm​

​

|

Doanh thu​

​

|

Mức Tăng giảm Doanh thu so với năm trước​

​

|

% Tăng giảm Doanh thu so với năm trước​

​

|

111​

​

|An Giang|

2001​

|

84590000​

| | |

112​

​

| |

2002​

|

29231000​

|

-55359000​

|

-0,6544​

|

113​

​

| |

2003​

|

79568000​

|

50337000​

|

1,722​

|

114​

​

| |

2004​

|

70046000​

|

-9522000​

|

-0,1197​

|

115​

​

| |

2005​

|

79373000​

|

9327000​

|

0,1332​

|

116​

​

| |

2006​

|

91811000​

|

12438000​

|

0,1567​

|

117​

​

|An Giang Total| |

434619000​

| | |

118​

​

|Hà nội|

2001​

|

41352000​

| | |

119​

​

| |

2002​

|

49898000​

|

8546000​

|

0,2067​

|

120​

​

| |

2003​

|

36131000​

|

-13767000​

|

-0,2759​

|

121​

​

| |

2004​

|

80319000​

|

44188000​

|

1,223​

|

122​

​

| |

2005​

|

41373000​

|

-38946000​

|

-0,4849​

|

123​

​

| |

2006​

|

24546000​

|

-16827000​

|

-0,4067​

|

124​

​

|Hà nội Total| |

273619000​

| | |

125​

​

|HCM|

2001​

|

43949000​

| | |

126​

​

| |

2002​

|

45937000​

|

1988000​

|

0,0452​

|

127​

​

| |

2003​

|

49433000​

|

3496000​

|

0,0761​

|

128​

​

| |

2004​

|

121162000​

|

71729000​

|

1,451​

|

129​

​

| |

2005​

|

33449000​

|

-87713000​

|

-0,7239​

|

130​

​

| |

2006​

|

26739000​

|

-6710000​

|

-0,2006​

|

131​

​

|HCM Total| |

320669000​

| | |

132​

​

|Huế|

2001​

|

44527000​

| | |

133​

​

| |

2002​

|

26505000​

|

-18022000​

|

-0,4047​

|

134​

​

| |

2003​

|

88149000​

|

61644000​

|

2,3257​

|

135​

​

| |

2004​

|

52858000​

|

-35291000​

|

-0,4004​

|

136​

​

| |

2005​

|

47416000​

|

-5442000​

|

-0,103​

|

137​

​

| |

2006​

|

33126000​

|

-14290000​

|

-0,3014​

|

138​

​

|Huế Total| |

292581000​

| | |

139​

​

|Grand Total| |

1321488000​

| | |

1. Phân tích % tăng giảm Doanh thu từng tỉnh so với năm chọn làm năm gốc:

Tính mức tăng giảm doanh thu và tỷ lệ tăng giảm doanh thu so với năm gốc là năm 2001:

|

C​

​

|

D​

​

|

E​

​

|

F​

​

|

G​

​

|

142​

​

|

Tỉnh​

​

|

Năm​

​

|

Doanh thu​

​

|

Mức Tăng giảm Doanh thu so với năm 2001​

​

|

% Tăng giảm Doanh thu so với năm 2001​

​

|

143​

​

|An Giang|

2001​

|

84590000​

| | |

144​

​

| |

2002​

|

29231000​

|

-55359000​

|

-0,6544​

|

145​

​

| |

2003​

|

79568000​

|

-5022000​

|

-0,0594​

|

146​

​

| |

2004​

|

70046000​

|

-14544000​

|

-0,1719​

|

147​

​

| |

2005​

|

79373000​

|

-5217000​

|

-0,0617​

|

148​

​

| |

2006​

|

91811000​

|

7221000​

|

0,0854​

|

149​

​

|An Giang Total| |

434619000​

| | |

150​

​

|Hà nội|

2001​

|

41352000​

| | |

151​

​

| |

2002​

|

49898000​

|

8546000​

|

0,2067​

|

152​

​

| |

2003​

|

36131000​

|

-5221000​

|

-0,1263​

|

153​

​

| |

2004​

|

80319000​

|

38967000​

|

0,9423​

|

154​

​

| |

2005​

|

41373000​

|

21000​

|

0,0005​

|

155​

​

| |

2006​

|

24546000​

|

-16806000​

|

-0,4064​

|

156​

​

|Hà nội Total| |

273619000​

| | |

157​

​

|HCM|

2001​

|

43949000​

| | |

158​

​

| |

2002​

|

45937000​

|

1988000​

|

0,0452​

|

159​

​

| |

2003​

|

49433000​

|

5484000​

|

0,1248​

|

160​

​

| |

2004​

|

121162000​

|

77213000​

|

1,7569​

|

161​

​

| |

2005​

|

33449000​

|

-10500000​

|

-0,2389​

|

162​

​

| |

2006​

|

26739000​

|

-17210000​

|

-0,3916​

|

163​

​

|HCM Total| |

320669000​

| | |

164​

​

|Huế|

2001​

|

44527000​

| | |

165​

​

| |

2002​

|

26505000​

|

-18022000​

|

-0,4047​

|

166​

​

| |

2003​

|

88149000​

|

43622000​

|

0,9797​

|

167​

​

| |

2004​

|

52858000​

|

8331000​

|

0,1871​

|

168​

​

| |

2005​

|

47416000​

|

2889000​

|

0,0649​

|

169​

​

| |

2006​

|

33126000​

|

-11401000​

|

-0,256​

|

170​

​

|Huế Total| |

292581000​

| | |

171​

​

|Grand Total| |

1321488000​

| | |

 

[Thanh Nam]

4. Tỷ lệ so với 1 dữ liệu cùng cấp dùng làm mốc so sánh:

So sánh từng mặt hàng của mỗi tỉnh với cùng mặt hàng của TP HCM:

|

C​

​

|

D​

​

|

E​

​

|

F​

​

|

175​

​

|

Tỉnh​

​

|

Mặt hàng​

​

|

Doanh thu​

​

|

So sánh Doanh thu với TP HCM​

​

|

176​

​

|An Giang|Điện cơ|

234204000​

|

1,7807​

|

177​

​

| |Điện gia dụng|

200415000​

|

2,8352​

|

178​

​

|An Giang Total| |

434619000​

|

1,3554​

|

179​

​

|Hà nội|Điện cơ|

104865000​

|

0,7973​

|

180​

​

| |Điện gia dụng|

82725000​

|

1,1703​

|

181​

​

| |Điện tử|

86029000​

|

0,7262​

|

182​

​

|Hà nội Total| |

273619000​

|

0,8533​

|

183​

​

|HCM|Điện cơ|

131524000​

|

1​

|

184​

​

| |Điện gia dụng|

70688000​

|

1​

|

185​

​

| |Điện tử|

118457000​

|

1​

|

186​

​

|HCM Total| |

320669000​

|

1​

|

187​

​

|Huế|Điện cơ|

73921000​

|

0,562​

|

188​

​

| |Điện gia dụng|

33126000​

|

0,4686​

|

189​

​

| |Điện tử|

185534000​

|

1,5663​

|

190​

​

|Huế Total| |

292581000​

|

0,9124​

|

191​

​

|Grand Total| |

1321488000​

| |

Điện cơ An Giang so với Điện cơ TP HCM là 178,07%, mặt hàng Điện gia dụng An Giang so với Điện gia dụng TP HCM là 283,52%. Nhưng tổng số của An Giang chỉ bằng 135,54% của TP HCM vì không bán được mặt hàng Điện tử.
TP HCM so với chính nó nên tất cả đều 100%.