Pivot Table-Power Pivot-Power Query

PIVOT TABLE

POWER PIVOT, POWER QUERY, POWER VIEW

Mục lục

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:

• CHƯƠNG 1: PIVOT TABLE
• I. PH숃N 1: PIVOT TABLE CĂN B䄃ऀ N - KH䄃ĀI NI쨃⌀M - C䄃ĀC BƯỚ C THỰ C HI쨃⌀N:
  • 1. TÙ ch漃⌀n v甃ng dữ li⌀u, V愃o Tab Insert - Pivot Table:
  • 2. XuĀt hi⌀n h⌀p thoại Create Pivot Table:
  • 3. K攃Āo v愃 th愃ऀ :...................................................................................................
  • 4. Định dạng báo cáo:
    • C䄃ĀC TH唃ऀ THU숃⌀T TRONG PIVOT TABLE:
  • 1. Xem từ ng phn báo cáo:
  • 2. Thay đऀ i cĀu tr甃Āc b愃ऀ ng ph‚n t椃Āch:
  • 3. Sử a chữ a, đị nh dạng Nhữ ng Sub Total:
  • 4. 숃ऀ n các d漃ng SubTotal Row v愃 Column v愃 Total:
  • 5. Thay đऀ i v甃ng dữ li⌀u ngun:
  • 6. Điu ch椃ऀ nh vị tr椃Ā 2 trườ ng dữ li⌀u d漃ng th愃nh c⌀t:
• II. PH숃N 2: PIVOT TABLE N¬NG CAO - Y U C숃U CƠ B䄃ऀ N KHI THỰ C HI쨃⌀N PIVOT TABLE
  • 1. TiÍu chÌ ph‚n tÌch
  • 2. Quy trÏnh chuẩn tạo dữ li⌀u ngun
    • C䄃ĀC PHƯƠNG PH䄃ĀP TH퐃ĀNG KÊ Đ퐃ĀI VỚ I TRƯỜ NG DỮ LI쨃⌀U
  • 1. Các ph攃Āp thĀng kÍ thÙng d甃⌀ng:
  • 2. Các ph攃Āp thĀng kê đặ c bi⌀t:
    • C䄃ĀC CÔNG CỤ MẠNH
  • 1. Công c甃⌀ Group:
  • 2. Tạo trường t椃Ānh toán dựa v愃o trường có sẵn :
  • 3. Slicer
  • 4. Time line
    • Dữ li⌀u từ nhiu ngun : (Multi Consolidation Range):
  • 1. Phương pháp:
• III. PH숃N III: ỨNG DỤNG PIVOT TABLE TRONG B䄃ĀO C䄃ĀO: - Ph‚n tÌch:
  • 1. Ph‚n tÌch chi phÌ theo loại:
  • 2. Phân t椃Āch chi ph椃Ā theo b⌀ phận:
    • TỔNG HỢP VÀ PHÂN TÍCH TỪNG CÔNG ĐOẠN S䄃ऀN XUẤT:
  • 1. Tऀng hợp theo công đoạn:
  • 2. Trình tự thực hi⌀n từng đơn h愃ng theo sự ki⌀n:
    • THÊM TRƯỜNG TÍNH TO䄃ĀN CHO DỮ LI쨃⌀U
  • 1. Thêm trên b愃ऀng t椃Ānh
  • 2. ThÍm trong Power pivot
    • PIVOT TABLE TỪ NHIỀU B䄃ऀNG
  • 1. Tạo Pivot table............................................................................................
  • 2. Các thủ thuật ch漃⌀n field
  • 3. Kết hợp Pivot table v愃 Pivot chart
• IV. POWER QUERY.................................................................................................... - GIỚI THI쨃⌀U
  • 1. Giới thi⌀u....................................................................................................
  • 2. C愃i đặt
    • CHỨC NĂNG UNPIVOT
  • 1. Ý nghĩa
  • 2. Thực hi⌀n
    • QUERY TỪ DỮ LI쨃⌀U TỪ B䄃ऀNG TÍNH
  • 1. Điu ki⌀n để tạo query
  • 2. Tạo query từ 1 b愃ऀng
    • HI쨃⌀U CHỈNH QUERY......................................................................................
  • 1. Sao chÈp, xÛa Query
  • 2. Ch漃⌀n c⌀t
  • 3. Xóa c⌀t......................................................................................................
  • 4. XÛa dÚng
  • 5. Tách 1 c⌀t th愃nh nhiu c⌀t
  • 6. Thêm c⌀t t椃Ānh toán ngo愃i dữ li⌀u
  • 7. Thêm c⌀t chứa giá trị theo điu ki⌀n
  • 8. Group by query
  • 9. Bỏ thao tác hi⌀u ch椃ऀnh đã thực hi⌀n
    • MERGE QUERY TỪ NHIỀU QUERY
  • 1. Mở cửa sऀ Query Editor
  • 2. Merge c·c query
  • 3. Kết th甃Āc merge v愃 sử d甃⌀ng
    • APPEND QUERY
    • QUERY DỮ LI쨃⌀U TỪ FILE EXCEL KH䄃ĀC
  • 1. Kết nĀi file Excel khác
  • 2. Tạo truy vĀn từ các query
    • COMBINE QUERY TỪ C䄃ĀC FILE EXCEL TRONG FOLDER
  • 1. Ý nghĩa combine v愃 điu ki⌀n để combine
  • 2. Thực hi⌀n
  • 3. Đánh giá
    • TRUY VẤN BẰNG SQL VÀO DỮ LI쨃⌀U ACCESS
  • 1. Kết nĀi dữ li⌀u bằng truy vĀn SQL
  • 2. Hi⌀u ch椃ऀnh SQL của query
    • TRUY VẤN BẰNG SQL VÀO DỮ LI쨃⌀U TỪ CƠ SỞ DỮ LI쨃⌀U KH䄃ĀC
  • 1. Kết nĀi dữ li⌀u bằng câu truy vĀn SQL
  • 2. Hi⌀u ch椃ऀnh SQL của query
• V. POWER VIEW - Giới thi⌀u v愃 c愃i đặt
  • 1. Giới thi⌀u...................................................................................................
  • 2. Fix lỗi khi không mở được Power view
    • THỰC HI쨃⌀N
  • 1. Tạo mới 1 power view sheet
  • 2. Tạo biểu đ
  • 3. Tạo thanh điu khiển l漃⌀c biểu đ
  • 4. Tạo slicer điu khiển
  • 5. Tạo biểu đ dạng map
    • TRANG TRÕ POWER VIEW
  • 1. Định dạng nn
  • 2. Định dạng b愃ऀn đ
  • 3. Hiển thị các th愃nh phn biểu đ
  • 4. Vẽ thêm v愃 sắp xếp Power View sheet
• D An Giang Điện cơ 2001 34.590 8. Đại lý Tỉnh Mặt hàng Năm Doanh thu Lơi nhu⌀n
• C An Giang Điện cơ 2003 39.568 8.
• C An Giang Điện cơ 2005 32.417 3.
• D An Giang Điện cơ 2004 45.046 7.
• C An Giang Điện gia dụng 2002 24.231 2.
• D An Giang Điện gia dụng 2005 29.373 2.
• D An Giang Điện gia dụng 2006 31.811 3.
• D An Giang Điện gia dụng 2003 25.425 2.
• C An Giang Điện cơ 2002 25.491 24.
• E Hà nội Điện cơ 2004 42.154 1.
  • F Hà nội Điện cơ 2006 54.546 1.
  • F Hà nội Điện gia dụng 2001 41.352 6.
  • F Hà nội Điện gia dụng 2005 46.373 4.
• E Hà nội Điện tử 2002 49.898 5.
• E Hà nội Điện tử 2003 43.131 4. - I HCM Điện cơ 2003 49.433 4.
• A HCM Điện cơ 2004 42.091 3.
• H HCM Điện gia dụng 2001 43.949 2.
• B HCM Điện gia dụng 2006 26.739 1.
• H HCM Điện tử 2002 45.937 2.
• A HCM Điện tử 2004 49.071 3.
• B HCM Điện tử 2005 53.449 3.
  • J Huế Điện cơ 2002 36.505 1.
  • J Huế Điện cơ 2005 47.416 5.

G Huế Điện gia dụng 2006 33.126 993. J Huế Điện tử 2001 44.527 4. G Huế Điện tử 2003 48.149 5. G Huế Điện tử 2004 52.858 7.

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ừng năm củ a từ 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:

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)

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.

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:

• 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攃Āo trường năm từ Page xuĀng Row:

• K攃Āo th愃ऀ trường Năm từ trÍn xuĀng dướ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:

• K攃Āo thêm trườ ng Lợi nhuận v愃o Data Values

• V愃 lập bĀt cứ mẫu ph‚n t椃Āch n愃o bạn muĀn, hoặ c bạn nghĩ ra, ch椃ऀ bằng cách k攃Āo th愃ऀ : thÍm, bớt, đऀ i chỗ, hoán vị.

Tuy nhiên, đừ ng l愃m chuy⌀n vÙ nghĩa như cho Đại l礃Ā nằm ở cĀp cao hơn T椃ऀ nh. Vì m⌀t t椃ऀ nh có thể có nhiu Đại l礃Ā, nhưng mỗi Đại l礃Ā ch椃ऀ nằm trong 1 t椃ऀ nh, rĀt cu⌀c l愃 li⌀t kÍ tĀt c愃ऀ ra m愃 chẳ ng tऀ ng hợp hơn được gì so với ch椃ऀ 1 trường Đạ i l礃Ā.

3. Sử a chữ a, đị nh dạng Nhữ ng Sub Total:

Bạn r愃 chu⌀t v愃o bÍn trái các Row Subtotal, hoặ c bÍn trÍn nhữ ng Column SubTotal, khi n愃o hi⌀n lÍn mũi tÍn nhỏ x椃Āu m愃u đen, thì click, ta sẽ ch漃⌀n được to愃n b⌀ các sub total.

B‚y giờ bạn có thể:

• TÙ m愃u d漃ng/ c⌀t Sub Total

• Sử a “An Giang Total” th愃nh “ C⌀ng An Giang”, (ch椃ऀ sử a cho 1 Ù, c愃Āc Ù c漃n l愃⌀i tự đ⌀ng đऀ i theo)

4. 숃ऀ n các d漃ng SubTotal Row v愃 Column v愃 Total:

Click ch漃⌀n nhữ ng m甃⌀c cn ẩn, nhĀn chu⌀t ph愃ऀ i, bỏ ch漃⌀n SubTotal <Fieldname>