Pivot Table-Power Pivot-Power Query | Học viện phụ nữ Việt Nam

PIVOT TABLE
POWER PIVOT, POWER QUERY, POWER VIEW PIVOT TABLE giaiphapexcel.com
Mục lục
CHƯƠNG 1: PIVOT TABLE ............................................................................................ 5
I. PHN 1: PIVOT TABLE CĂN BN ............................................................................ 6
KHI NIM ...................................................................................................... 6
CC BƯC THC HIN: ................................................................................... 7 1. Tô ch n v 
ng d liu, Vo Tab Insert - Pivot Table:
....................................... 7 2.
Xut hin hp thoại Create Pivot Table: .......................................................... 8 3.
Ko v th: ................................................................................................... 9 4.
Định dạng báo cáo: ..................................................................................... 11
CC TH THUT TRONG PIVOT TABLE: .......................................................... 13 1. Xem tng phn báo cáo
: ............................................................................. 13 2.
Thay đi cu trc bng phân tch: ................................................................ 15 3.
Sa cha, định dạng Nhng Sub Total: ........................................................ 19 4.  á n c 
c d ng SubTotal Row v Column v Total: ........................................... 19 5.
Thay đi vng d liu ngun: ...................................................................... 20 6.
Điu chnh vị tr 2 trưng d liu d 
ng th nh ct: ......................................... 21
II. PHN 2: PIVOT TABLE NÂNG CAO ........................................................................ 23 YÊU C N KHI TH U CƠ B
C HIN PIVOT TABLE ............................................. 23 1.
Tiêu chí phân tích ....................................................................................... 23 2.
Quy trình chuẩn tạo d liu ngun ............................................................... 23
CC PHƯƠNG PHP THNG KÊ ĐI I TRƯ V
NG D LIU ............................ 24 1.
Các php thng kê thông dng: ................................................................... 24 2.
Các php thng kê đc bit: ........................................................................ 27
CC CÔNG CỤ MẠNH ..................................................................................... 34 1.
Công c Group: .......................................................................................... 34 2.
Tạo trưng tnh toán dựa vo trưng có sẵn: ............................................... 39 3.
Slicer .......................................................................................................... 45 4.
Time line .................................................................................................... 49
D liu t nhiu ngun : (Multi Consolidation Range): ..................................... 51 1.
Phương pháp: ............................................................................................. 51
III. PHN III: ỨNG DỤNG PIVOT TABLE TRONG BO CO: ......................................... 56
Phân tích: ...................................................................................................... 56 1.
Phân tích chi phí theo loại: .......................................................................... 56 2.
Phân tch chi ph theo b phận: ................................................................... 58
TỔNG HỢP VÀ PHÂN TÍCH TỪNG CÔNG ĐOẠN SN XUẤT: .............................. 59 1.
Tng hợp theo công đoạn: .......................................................................... 61 2.
Trình tự thực hin tng đơn hng theo sự kin: ............................................ 61 Biên soạn: Ptm0412 Trang 1 / 151 PIVOT TABLE giaiphapexcel.com 3.
Tng hợp theo khách hng v đơn hng: ..................................................... 62 4.
Tng hợp theo sn phẩm: ........................................................................... 62 5.
Tng hợp sn phẩm theo tháng, năm: ......................................................... 63 6.
Tng hợp theo tình trạng: ........................................................................... 63 7.
Thng kê theo phân xưỡng v chuyn SX: ................................................... 64 8.
Thng kê theo quy cách (mu v kch thước): .............................................. 65
IV. PIVOT OPTION VÀ LAYOUT .................................................................................. 65
Layout ........................................................................................................... 65 1.
Ý nghĩa ...................................................................................................... 65 2.
Thao tác ..................................................................................................... 66
Pivot option ................................................................................................... 69 1.
Ý nghĩa v cách mở ..................................................................................... 69 2.
Các ty chn .............................................................................................. 70
V. HÀM GETPIVOT DATA .......................................................................................... 73
Giới thiu ....................................................................................................... 73
S dng ........................................................................................................ 73
VI. DASHBOARD VI PIVOT VÀ PIVOT CHART ........................................................... 75
Ý NGHĨA DASHBOARD .................................................................................... 75
Thực hin trên Excel ...................................................................................... 75
Tham kho: ................................................................................................... 75
CHƯƠNG 2: POWER PIVOT......................................................................................... 78
I. GII THIU VÀ CÀI ĐẶT ...................................................................................... 78
Giới thiu ....................................................................................................... 78
Ci đt .......................................................................................................... 78
II. D LIU NGUỒN CHO POWER PIVOT ................................................................... 81
D LIU TỪ BNG TÍNH EXCEL ...................................................................... 81 1.
Excel 2010 .................................................................................................. 81 2.
Excel 2016 .................................................................................................. 81
D LIU TỪ CƠ SỞ D LIU KHC ................................................................. 81 1.
Kết ni v ly d liu v Excel ..................................................................... 81 2.
Ty chn ch ly mt s ct ......................................................................... 84 3.
Ty chn ch kết ni không lưu xung sheet ................................................. 86 4.
Kết ni thông qua Power Pivot ..................................................................... 87
III. SỬ DỤNG POWER PIVOT ..................................................................................... 90
LIÊN KẾT BNG D LIU ................................................................................ 90 1.
Điu kin để liên kết ................................................................................... 90 2.
Tạo liên kết ................................................................................................ 90 Biên soạn: Ptm0412 Trang 2 / 151 PIVOT TABLE giaiphapexcel.com
THÊM TRƯNG TÍNH TON CHO D LIU ...................................................... 91 1.
Thêm trên bng tnh ................................................................................... 91 2.
Thêm trong Power pivot .............................................................................. 91
PIVOT TABLE TỪ NHIỀU BNG ....................................................................... 92 1.
Tạo Pivot table............................................................................................ 92 2.
Các thủ thuật chn field .............................................................................. 93 3.
Kết hợp Pivot table v Pivot chart ................................................................ 96
IV. POWER QUERY................................................................................................... . 97
GII THIU ................................................................................................... 97 1.
Giới thiu.................................................................................................... 97 2.
Ci đt ....................................................................................................... 97
CHỨC NĂNG UNPIVOT ................................................................................... 97 1.
Ý nghĩa ...................................................................................................... 97 2.
Thực hin ................................................................................................... 98
QUERY TỪ D LIU TỪ BNG TÍNH............................................................... 102 1.
Điu kin để tạo query ............................................................................... 102 2.
Tạo query t 1 bng .................................................................................. 102
HIU CHỈNH QUERY ...................................................................................... 103 1.
Sao chép, xóa Query .................................................................................. 103 2.
Chn ct ................................................................................................... 104 3.
Xóa ct...................................................................................................... 105 4.
Xóa dòng ................................................................................................... 105 5.
Tách 1 ct thnh nhiu ct ......................................................................... 106 6.
Thêm ct tnh toán ngoi d liu ................................................................ 107 7.
Thêm ct chứa giá trị theo điu kin ........................................................... 108 8.
Group by query .......................................................................................... 111 9.
Bỏ thao tác hiu chnh đã thực hin ............................................................ 113
MERGE QUERY TỪ NHIỀU QUERY .................................................................. 113 1.
Mở ca s Query Editor .............................................................................. 114 2.
Merge các query ........................................................................................ 114 3.
Kết thc merge v s dng ........................................................................ 116
APPEND QUERY ............................................................................................ 117
QUERY D LIU TỪ FILE EXCEL KHC ........................................................... 121 1.
Kết ni file Excel khác ................................................................................ 121 2.
Tạo truy vn t các query .......................................................................... 122
COMBINE QUERY TỪ CC FILE EXCEL TRONG FOLDER ................................... 122 1.
Ý nghĩa combine v điu kin để combine ................................................... 122 Biên soạn: Ptm0412 Trang 3 / 151 PIVOT TABLE giaiphapexcel.com 2.
Thực hin .................................................................................................. 122 3.
Đánh giá ................................................................................................... 125
TRUY VẤN BẰNG SQL VÀO D LIU ACCESS .................................................. 125 1.
Kết ni d liu bằng truy vn SQL ............................................................... 125 2.
Hiu chnh SQL của query........................................................................... 129
TRUY VẤN BẰNG SQL VÀO D LIU TỪ CƠ SỞ D LIU KHC ....................... 130 1.
Kết ni d liu bằng câu truy vn SQL ........................................................ 130 2.
Hiu chnh SQL của query........................................................................... 133
V. POWER VIEW ..................................................................................................... 136
Giới thiu v ci đt ...................................................................................... 136 1.
Giới thiu................................................................................................... 136 2.
Fix lỗi khi không mở được Power view ......................................................... 137
THC HIN .................................................................................................. 138 1.
Tạo mới 1 power view sheet ....................................................................... 138 2.
Tạo biểu đ ............................................................................................... 140 3.
Tạo thanh điu khiển lc biểu đ ................................................................ 141 4.
Tạo slicer điu khiển .................................................................................. 143 5.
Tạo biểu đ dạng map ............................................................................... 144
TRANG TRÍ POWER VIEW ............................................................................. 147 1.
Định dạng nn ........................................................................................... 147 2.
Định dạng bn đ ...................................................................................... 148 3.
Hiển thị các thnh phn biểu đ ................................................................. 149 4.
Vẽ thêm v sắp xếp Power View sheet ........................................................ 149 Biên soạn: Ptm0412 Trang 4 / 151 PIVOT TABLE giaiphapexcel.com
CHƯƠNG 1: PIVOT TABLE
LỜI GIỚI THIỆU
Pivot table là một công cụ rất mạnh của Excel, từ Office 97 đã có hiện diện của PivotTable
rồi. Trải qua các phiên bản khác nhau của MSOffice, công cụ này ngày một cải tiến và trở
thành công cụ phân tích hàng đầu trong Excel.
Không biết PivotTable, người dùng có khi phải sử dụng những công thức tính tổng, đếm,
trung bình, … có điều kiện, nếu 2 điều kiện trở lên, sẽ phải dùng công thức mảng hoặc
hàm SumProduct. Khi dữ liệu lên đến vài trăm, vài ngàn dòng, thậm chí chục ngàn dòng,
thì các công thức này khiến cho bảng tính chạy 1 cách ì ạch, khó chịu.
Hơn nữa, có những dạng bảng phân tích dữ liệu 2 chiều, việc tách 1 phần dữ liệu ra thành
tiêu đề 2 chiều dọc và ngang, có khi phải dùng VBA để giải quyết.
Trong khi đó, nếu sử dụng PivotTable, bạn chỉ việc làm theo wizard, và dùng chuột kéo
thả. Mỗi lần kéo thả vào các vị trí khác nhau, bạn sẽ có 1 dạng phân tích khác, và tuỳ theo
nhu cầu bạn sẽ thả vào đâu. Một ưu điểm mạnh của Pivot table là có thể phân tích với
nhiều cấp, tương tự tính tổng nhiểu điều kiện.
Với PivotTable 2003, bạn có thể phân tích theo %, có thể tạo field tính toán và phân tích
field mới này, và nhiều tính năng khác.
Với Pivot Table 2007, 2010 và cao hơn, các tính năng lại được bổ sung thêm hơn nữa.
PivotTable làm việc hiệu quả và nhanh chóng cho dù bạn có dữ liệu rất nhiều dòng.
Pivotable có nhược điểm là không dùng nó để trình bày như 1 báo cáo chính thức. Kể cả
với Office 2007 hay 2010, các dạng trình bày định dạng có sẵn rất đa dạng và phong phú,
không ai muốn in nó ra như 1 báo cáo, vì không đúng mẫu (form of report) chính thống.
Dù vậy, sức mạnh của PivotTable là chưa thể thay thế trong rất nhiều trường hợp. Trong
tập tài liệu này, tôi xin giới thiệu các bạn PivotTable từ căn bản đến nâng cao, và 1 số ứng
dụng của nó. Để lập ra báo cáo chính thống, Excel đã hỗ trợ hàm GetPivotTable, có thể
tính toán với 14 điều kiện đối với Excel 2003, và 126 điều kiện đối với Excel 2007 và 2010.
Một hàm mạnh như đại bác, và dùng để lấy số liệu trong bảng phân tích ra ngoài làm báo cáo.
Đặc biệt lần này, tôi xin giới thiệu Pivot Table trong Excel 2007 và 2010, , 2016 với những
tính năng bổ sung mới nhất của từng phiên bản.
Giới thiệu là việc của tôi, còn ứng dụng, là việc của các bạn, chúc thành công. Biên soạn: Ptm0412 Trang 5 / 151 PIVOT TABLE giaiphapexcel.com I.
PHN 1: PIVOT TABLE CĂN BN
KHI NIM
Pivot table l 1 công c của Excel dùng tng hợp v phân tch d liu với nhiu góc đ v nhiu cp khác nhau.
Th d bạn có d liu v doanh thu của nhiu ca hng, nhiu nhóm mt hng, ở nhiu
tnh khác nhau, trong nhng khong thi gian khác nhau (th d năm) như sau: Đại lý Tỉnh Mặt hàng Năm Doanh thu Lơi nhu n D An Giang Điện cơ 2001 34.590.000 8.459.000 C An Giang Điện cơ 2003 39.568.000 8.161.120 C An Giang Điện cơ 2005 32.417.000 3.824.800 D An Giang Điện cơ 2004 45.046.000 7.202.760 C An Giang Điện gia dụng 2002 24.231.000 2.507.720 D An Giang Điện gia dụng 2005 29.373.000 2.937.300 D An Giang Điện gia dụng 2006 31.811.000 3.017.320 D An Giang Điện gia dụng 2003 25.425.000 2.784.250 C An Giang Điện cơ 2002 25.491.000 24.124.520 E Hà nội Điện cơ 2004 42.154.200 1.825.520 F Hà nội Điện cơ 2006 54.546.000 1.736.380 F Hà nội Điện gia dụng 2001 41.352.000 6.202.800 F Hà nội Điện gia dụng 2005 46.373.000 4.964.760 E Hà nội Điện tử 2002 49.898.000 5.488.780 E Hà nội Điện tử 2003 43.131.000 4.419.650 I HCM Điện cơ 2003 49.433.000 4.943.300 A HCM Điện cơ 2004 42.091.000 3.283.640 H HCM Điện gia dụng 2001 43.949.000 2.197.450 B HCM Điện gia dụng 2006 26.739.000 1.069.560 H HCM Điện tử 2002 45.937.000 2.756.220 A HCM Điện tử 2004 49.071.000 3.907.100 B HCM Điện tử 2005 53.449.000 3.679.390 J Huế Điện cơ 2002 36.505.000 1.325.250 J Huế Điện cơ 2005 47.416.000 5.215.760 Biên soạn: Ptm0412 Trang 6 / 151 PIVOT TABLE giaiphapexcel.com G Huế Điện gia dụng 2006 33.126.000 993.780 J Huế Điện tử 2001 44.527.000 4.007.430 G Huế Điện tử 2003 48.149.000 5.170.430 G Huế Điện tử 2004 52.858.000 7.400.120 Bây gi bạn mun t ng h  ợp: 1. Doanh thu tng tnh
2. Doanh thu tng nhóm hng
3. Doanh thu tng đại l 4. Doanh thu tng năm V bạn mu n phân t  ch:
5. Doanh thu tng tnh theo tng mt hng
6. Doanh thu tng đại l theo tng mt hng 7. Doanh thu t a t ng năm củ ng tnh
8. Doanh thu tng đại l theo năm
9. Doanh thu tng năm theo nhóm hng
10. Chi tiết Doanh thu tng nh m h ó
ng cho tng đại l, nhóm theo tnh.
11. Phân tch theo bt cứ kiểu no bạn có thể nghĩ ra.
Bạn có thể lm tt c nhng bng ny ch bằng dng chu t
 ko th với công c Pivot Table.
CC BƯC THC HIN:
1. Tô chn vng d
 liu, Vo Tab Insert - Pivot Table: Biên soạn: Ptm0412 Trang 7 / 151 PIVOT TABLE giaiphapexcel.com
2. Xut hin hp thoi Create Pivot Table:
- Chn 1 ô trong Existing worksheet hoc chn New worksheet, Nhn OK. Kết qu 2007
và 2010 cho ra 1 bng Field list ging nhau: Biên soạn: Ptm0412 Trang 8 / 151 PIVOT TABLE giaiphapexcel.com
3. Ko v th: - Trưng t ợ ng h p 
c p cao nht vo Report Filter (th d Năm) - 1 hoc 2 Trưng t ng 
hợp cp thp hơn vo Row Labels (th d Tnh, sau đó l Đại
l, đng lm ngược lại l cho Đại l cp cao hơn Tnh)
- 1 hoc 2 Trưng phân tch vo Column labels (th d mt hng)
- Trưng d liu phân tch vo Data (th d Doanh thu hoc c Lợi nhuận) Biên soạn: Ptm0412 Trang 9 / 151 PIVOT TABLE giaiphapexcel.com Biên soạn: Ptm0412 Trang 10 / 151 PIVOT TABLE giaiphapexcel.com
4. Định dng báo cáo:
a) Định dạng khung, màu sắc
Excel 2007 và 2010 có sẵn nhng style định dạng, ch cn click chn trong tab design, nếu
mun nhiu mẫu hơn thì click vo mũi tên bên phi của mc Pivot Table Styles.
b) Định dạng số, ngày tháng, …
Trong ca s field list hin ra mỗi khi Pivot Table được kch hoạt, nhn chut vo trưng
d liu trong ô Values, hoc nhn chut phi vo 1 ô trong vng Pivot Table, chn Values Field setting như sau: Biên soạn: Ptm0412 Trang 11 / 151 PIVOT TABLE giaiphapexcel.com
ca s setting hin ra như sau:
Nhn tiếp nt Number Format, v định dạng s có du phân cách hng ngn, s k s
thập phân, hoc s có dạng %, text, ngy tháng, gi, tin t, dạng phân s, dạng
Custom, … y như format cell. Biên soạn: Ptm0412 Trang 12 / 151 PIVOT TABLE giaiphapexcel.com
CC TH THUT TRONG PIVOT TABLE: 1. Xem t ng ph 
n bo co:
Kế bên mỗi tên trưng của Pivot table, bạn sẽ thy c 1 n ó
t có hình tam giác, nhn vo sẽ
x xung cho bạn chn xem 1, hoc vi nhóm d liu:
Không ging như 2003 ch cho php xem m i
ỗ ln 1 loại trong Page Field, Excel 2007 và
2010 cho php xem 1 hoc vi loại 1 lc nếu click ch n
 vo Select Mutiple Items trong Report Filter fields.
Tương tự, Row fields v Column fields có thể cho lc theo các kiểu lc khác nhau như Auto filter- Custom: Biên soạn: Ptm0412 Trang 13 / 151 PIVOT TABLE giaiphapexcel.com
Đi với trưng ngy tháng, ta cn có nhiu lựa chn hơn na: Biên soạn: Ptm0412 Trang 14 / 151 PIVOT TABLE giaiphapexcel.com
- Nhóm so sánh: bằng, trước ngy, trước hoc bằng ngy, sau ngy, sau hoc bằng
ngy, trong khong thi gian, không trong khong thi gian.
2. Thay đi cu trc bng phân tch: Bạn có thể dng chu t k
 o th: thêm, bớt, hoán vị, các trưng trong Pivot table để cho ra 1 mẫu phân tch mới:
- Ko bỏ trưng Đại l ra ngoi, K Page xu o trưng năm t ng Row:  Biên soạn: Ptm0412 Trang 15 / 151 PIVOT TABLE giaiphapexcel.com
- Ko th trưng Năm t ng dướ trên xu
i trưng Tnh trong vng Row:
- Ko bỏ trưng năm ra ngoi, ko trưng Mt hng t vng Column xung trưng Row: Biên soạn: Ptm0412 Trang 16 / 151 PIVOT TABLE giaiphapexcel.com
- Hoán vị 2 trưng Tnh v Mt hng:
- Ko trưng Tnh b lên Column: ỏ Biên soạn: Ptm0412 Trang 17 / 151