Cách tạo Pivot Table và các vấn đề liên quan

[ottieu]

Mong mọi người chỉ giúp cho mình:
- Pivot Table và Pivotchart Report dùng để làm gì?
- Cách tạo Pivot Table

 

[ptm0412]

Pivot table là 1 công cụ dùng 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).
bạn muốn phân tích:
1. Doanh thu từng tỉnh
2. Doanh thu từng nhóm hàng
3. Doanh thu từng cửa hàng
4. Doanh thu từng kỳ

Và 5. phân tích kết hợp 2, 3 tiêu chí trên

Pivot table tiện lợi nhưng rất dễ sử dụng vì có wizard và dùng chuột kéo thả. Quy trình như sau:
1. Đánh dấu chọn toàn khối dữ liệu
2. Mở menu - data - Pivot table and pivot chart
3. next
4. chọn vùng dữ liệu ( đã chọn rồi) - next
5. chọn nơi đặt Pivot table. click nút layout
6. kéo thả trường phân tích cao nhất (nếu muốn phân tích nhiều cấp theo ý mình) vào "Page"
7. Kéo thả 1 hoặ 2 trường thuộc cấp phân tích thấp hơn vào "row"
8. Kéo thả 1 hoặc 2 trường (nên chỉ 1 trương bảng tính không bị rối khó xem) vào "column"
9. Kéo thả trường dữ liệu cần phân tích vào "data" - finish
10. Trên tool bar Pivot (hiện ra ngay khi bạn chọn 1 ô bất kỳ trong vùng của Pivot) có đầy đủ các nút để tiến hành chỉnh sửa lại. Bạn cũng có thể kéo thả các trường vào những vị trí khác nhau hoặc hoán vị các trường này. Mỗi lần hoán vị bạn sẽ có 1 bảng phân tích mới.
bạn cũng có thể xem tất cả hoặc 1 phần của bảng phân tích bằng cách bấm vào các mũi tên kế bên các tên trường, chọn đánh dấu từng phần trong list đổ xuống.
trên tool bar cũngcó nút để tạo Pivot chart hoàn toàn tự động.
Ưu điểm số 1 của Pivot là phân tích nhiều cấp
Thứ nhì là khi bạn thay đổi dữ liệu gốc thì bảng phân tích thay đổi theo (tự động hoặc phải bấm vào nút ! (refresh) tùy cài đặt).
Thứ 3 là các dòng sum có thể chọn lại là count, hoặc average tùy ý

Xem file thí dụ kèm theo

 

[ptm0412]

Hồi nãy định up lên 1 file có sẵn, nhưng dữ liệu chỉ có thể phân tích 2 kiểu nên phải làm 1 file khác có thể phân tích nhiều kiểu hơn.
File này phân tích mẫu 4 kiểu, thực sự nếu hoán vị và kết hợp đủ kiểu thì vài chục kiểu
Ghi chú: có thể không cần kéo trường nào vào "page" nếu dữ liệu không phức tạp.

 

[ottieu]

Thí dụ mà ptm0412 đưa ra rất rõ ràng và cụ thể. Giờ thì mình đã bắt đầu biết sử dụng tính năng này. Cám ơn cậu nhiều lắm. Thế nhưng để có thể hiểu rõ hơn và sử dụng tính năng này thành thạo,liệu cậu có cung cấp thêm một vài VD nữa không?
Chúc mọi việc tốt lành!

 

[ptm0412]

1. Một thí dụ về phân tích chi phí.
bạn biết rằng một số chi phí chẳng hạn chi phí tiền lương, văn phòng phẩm, điện, điện thoại . . .được sử dụng ở nhiều bộ phận khác nhau như bộ phận kế toán, bộ phận kinh doanh, bộ phận hành chánh . . .
Nhà quản lý cần biết rằng chi phí tháng này so với tháng trước tăng hay giảm, tăng ở bộ phận nào, tăng có lý do chính đáng hay không? Tỷ lệ chi phí nào ở bộ phận nào là bao nhiêu, tỷ lệ trên tổng số là bao nhiêu? So với tháng trước là tăng hau giảm?
và quan trọng nhất là có thể tiết giảm hay không? tiết giảm ở bộ phận nào?
Cụ thể như Văn phòng phẩm bộ phận kế toán tăng có kèm theo doanh thu hoặc lượng chứng từ tăng không? chi phí điện thoại của bộ phận bán hàng tăng có kèm theo doanh thu tăng hay lượng khách hàng tăng hay không?

Vậy trong Pivot table bạn kéo mã chi phí vào row, kéo mã bộ phận và mã tháng vào column thứ tự tùy ý bạn, kéo số tiền vào data. Không cần là nhà phân tích giỏi cũng có thể thấy ngay 1 số vấn đề trên Pivot table.

2. Cũng là phân tích chi phí, bạn hãy phân tích các loại chi phí tạo nên giá thành sản phẩm như: nguyên vật liệu chính, nhiên liệu động lực, tiền lương, chi phí chung, chi phí sửa chữa, chi phí khấu hao máy móc trong nhiều kỳ, ở nhiều xưởng khác nhau.

3. Tạo sổ cái tổng hợp 1 bên của tài khoản kế toán, mục đích là xem thí dụ như chi tiền (bên có tài khoản 111 và 112) tương ứng với những tài khoản nào? mục đích chi là gì?
bạn lấy sổ nhật ký kế toán với 1 số cột sau:
a. TK nợ,
b. TK có,
c. số tiền (nợ có chung 1 cột cũng được),
d. Loại chi phí thí dụ: chi phí văn phòng, chi phí vật liệu gồm trả tiền người bán VL (331VL) và trả tiền VL mua trả ngay (152), trả tiền dịch vụ điện nước điện thoại, chi phí đầu tư (mua TSCĐ tương ứng 241 hoặc 211, 212) . . .

Bạn có thể đoán kéo cột nào thả vào đâu chăng?

4. ngược lại, hãy phân tích nguồn thu tiền mặt (nợ 111) và tiền thu NH (nợ 112) cũng từ bảng dữ liệu trên.

 

[ptm0412]

Một thí dụ khác của Pivot table

Trong khi lùng sục trên diễn đàn tìm các bài về sum product, mình thấy có một bài (không nhớ của ai), bạn ấy từ dữ liệu của nhật ký kế toán muốn:
1. Tìm ra tất cả các cặp đối ứng nợ- có
2. Tính tổng phát sinh của từng cặp đối ứng trên cho từng ngày.

tính tổng thì bạn ấy dùng hàm sumproduct thì được rồi, nhưng bạn ấy sợ bỏ sót 1 cặp đối ứng nào đó nên hỏi dùng filter hoặc advance fỉlte như thế nào để khỏi bỏ sót.

Topic này đã được các bạn khác giải quyết xong và hay nữa. nhưng mình muốn giới thiệu rằng Pivot table làm 2 việc này cái một, khỏi filter, khỏi cả sumproduct, mà bảo đảm không sót cặp nào.

Xem file mình kèm theo, 1 sheet là của bạn ấy, 1 sheet là Pivot. Nó còn phát hiện 1 số dòng trống, thể hiện bằng 1 cặp đối ứng blank - blank.

Khi nào rảnh mình sẽ nói thêm về các yêu cầu phân tích và cách và điều kiện để Pivot có thể thỏa mãn các yêu cầu này. (Nói kỹ hơi dài!)

 

[tvt232]

Cho mình hỏi là muốn thay đổi tên Field, chọn cho nó refesh tự động thì chọn ở đâu vậy? Làm thế nào để khi refesh thì bảng tính không bị thay đổi các định dạng format? Thanksss

 

[ptm0412]

1. Sửa tên field và sửa grand total thành Tổng cộng: Sửa trực tiếp trên formular bar
2. Sửa các sub total: sửa trên formular bar nhưng chỉ cần sửa 1 ô, thí dụ: "1111 total" sửa thành "Cộng 1111" các ô còn lại tự động sửa theo: Cộng 133, Cộng 1121 . . .
3. Tô màu hoặc định dạng các sub total: Chọn tất cả các cột sub total để định dạng 1 lần bằng cách rà con trỏ chuột lên trên 1 trong các cell tiêu đề cột chứa sub đến khi nào hiện ra mũi tên đen chỉ xuống thì click chọn. Nếu là sub total dòng thì rà chuột bên trái 1 trong các cell sub total cho đến khi hiện ra mũi tên đen chỉ ngang qua phải thì click.
4. Định dạng số không bị thay đổi kể cả khi hoán vị field
5. Định dạng tô màu sub total có thể bị mất khi hoán vị field, thực sự nbó không mất mà chỉ ẩn đi và hiện sub total mới cho field mới hoán vị đến. Nó vẫn nhớ và khi hoán vị trả lại như cũ, nó trả lại màu như trước khi hoán vị.
Nghĩa là chỉ định dạng 1 lần cho 1 mẫu bảng phân tích.
6. Khi refresh đâu có bị đổi định dạng font, số và màu. Hay ý bạn là mất định dạng gì?
7. Trong option của table có chọn lựa : refresh on open và refresh every... minute mà hình như chỉ on open file chứ không phải open Pivot table. Còn refresh every... minute sao trên máy tớ nó bị vô hiệu. Thôi thì sau khi thay đổi nội dung dữ liệu nguồn, chịu khó click nó 1 cái.

 

[tvt232]

Hôm wa mình làm thử thì khi refesh lại thì nó trả format lại trạng thái ban đầu, nhưng hôm nay làm thì lại giữ nguyên những gì mình thay đổi.. hihi không hĩu luôn.. Thanks very much

 

[ottieu]

Không hiểu có phải trình độ của em còn "non" quá không mà xem file anh ptm0412 gửi em chẳng hiểu mô tê gì cả. Ai đó có thời gian hãy hướng dẫn giúp em với.

 

[ptm0412]

Làm lại các bước từ 1 đến 9 ở bài số #2 ở trên. TKno và TKco kéo vào column, ngCT kéo vào row, TTVN kéo thả vào data. Xem mẫu đã làm ở cell F3450, so sánh 2 cái với nhau, giống là đạt yêu cầu.

 

[ottieu]

Trong bài số #1, trong bảng Pivot table em thấy anh ptm0412 có thể merge cells. Tại sao em cũng làm vậy mà không được?

 

[ptm0412]

Bấm chuột phải vào 1 cell bất kỳ trong Pivot, chọn Table option, chọn merge label.

 

[ottieu]

Trong lúc "táy máy, nghịch ngơm" ottieu nghĩ ra mấy cách "trang trí" cho Pivot hay hay. Nhưng mỗi lần tạo pivot mới lại phải dùng tay để định dạng thì mất nhiều thời gian lắm. Có cách nào có thể áp dụng được định dạng sẵn này cho Pivot mới mà không phải qua nhiều bước thực hiện như lần đầu không? ottieu mong nhận được sự hướng dẫn của "các bậc tiền bối'.

 

[ptm0412]

Chỉ có ở Excel 2007 thôi, trong tab Pivot - Tool - Design - chọn Pivot style- chọn 1 trong các định dạng hoặc chọn new style, chọn thêm make default nếu muốn.

 

[ottieu]

Thế bây giờ em muốn hiển thị dữ liệu doanh thu (của bài #2) trong bảng pivot table dưới dạng tỷ lệ phần trăm doanh thu của tổng chứ không phải theo giá trị, em nên làm thế nào?

 

[ptm0412]

Bản thân Pivot không hỗ trợ trực tiếp tỷ lệ phần trăm trên tổng số. Tuy nhiên do nhu cầu phân tích, người ta yêu cầu 1 bảng dữ liệu tổng hợp - phân tích dạng mà Pivot cung ứng. sau đó là thời kỳ phân tích hậu WTO, í quên hậu Pivot. Thực ra phân tích chuyên sâu là cả 1 môn học riêng trong chương trình ĐH các khoa kinh tế.
Thí dụ như bạn hỏi, phân tích doanh thu từng thời kỳ của từng mặt hàng, theo mức tăng giảm tuyệt đối (giá trị) và mức tăng giảm tương đối (tỷ lệ %) giữa 2 thời kỳ, nhà phân tích sẽ đưa ra 1 số kết luận, và người cuối cùng sẽ dựa vào kết luận ấy để đưa ra sách lược, chiến lược kinh doanh cho từng mặt hàng, và cho từng vùng địa lý.
Như vậy, trở lại Excel và Pivot, bạn là nhà phân tích hay chỉ là người cung cấp số liệu, bạn phải biết lấy gì từ 1 đống dữ liệu thô ban đầu. Pivot chỉ là 1 công cụ mạnh giúp sức cho bạn thay vì phải dùng rất nhiều công thức, hàm, mà như bài #1 tớ đã nói, Pivot cho kết quả dễ dàng cho các cấp độ phân tích khác nhau.
Từ kết quả của Pivot nếu muốn tỷ lệ % bạn phải thêm 1 cột phụ. Không chỉ thế bạn còn phải thêm 1 cột tăng giảm tuyệt đối, 1 cột tăng giảm tương đối. Nếu muốn phân tích chính xác hơn bạn phải tách giá trị ra thành đơn giá và số lượng và thêm 2 cột tăng giảm cho 2 chỉ tiêu này.
Từ đó bạn sẽ tính:
Trên 1 cột khác tính mức dộ ảnh hưởng của sự thay đổi đơn giá đến sự thay đổi doanh thu (của từng vùng và từng mặt hàng)
Trên 1 cột nữa tính mức độ ảnh hưởng của sự thay đổi số lượng bán đến sự thay đổi doanh thu (cũng của từng vùng và từng mặt hàng)
Cuối cùng dựa vào 2 cột này kết luận rằng:
- Doanh thu có thực sự tăng do số lượng bán nhiều hơn không.
- Tỷ lệ Doanh thu vùng này tăng có thực sự là tăng không hay tại vì doanh thu tại vùng khác bị giảm (thí dụ do thiên tai)
- Nếu giá bán tăng mà số lượng bán không giảm , hoặc ngược lại, thì nên làm gì?

Tóm lại phân tích là cả 1 ngành học và là 1 nghệ thuật. Pivot chỉ là công cụ hỗ trợ ban đầu thôi bạn ơi.

 

[ottieu]

Đúng là Pivot chỉ là công cụ hỗ trợ ban đầu, còn việc sử dụng Pivot phục vụ cho việc phân tích lại tùy thuộc vào trình độ chuyên môn, và khả năng phân tích vấn đề của nhà phân tích. Nếu tiếp tục hỏi, e rằng chủ đề sẽ đi sang một hướng khác mất. Quay lại với vấn đề Pivot ở góc độ đơn thuần là tin học, Em còn câu hỏi nữa muốn được giải đáp. Giả sử em muốn copy bảng Pivot sang 1 wordbook khác nhưng dữ liệu trong wordbook mới sẽ bị giới hạn (VD trong bài #1, chỉ hiện lên các số liệu liên quan trong năm 2001, 2002, 2003 thôi, còn những số liệu liên quan đến năm 2004, 2005 không được thể hiện). Dùng copy, paste theo cách thông thường thì không được rồi, chắc phải có cách nào đó đúng không? chỉ có điều em vẫn chưa biết mà thôi.

 

[ptm0412]

Vẫn phải nói về phân tích vì pivot là công cụ phân tích.
Pivot dựa vào 1 bảng dữ liệu có sẵn, phân tích là phân tích số liệu đã có dựa vào sự kiện đã diễn ra (nghĩa là quá khứ). Bởi vì mục đích phân tích là phân tích quá khứ nhằm vạch ra kế hoạch cho tương lai. Vấn đề là chọn vùng dữ liệu nguồn cho đủ chứ không phải copy rồi past sẽ bị mất. (cái gì không có thì không thể mất)
Vậy khi chọn dữ liệu nguồn của Pivot bạn phải chọn cho hết vùng dữ liệu đã có. Thí dụ trong bài #2 chỉ là thí dụ, số liệu đặt ra số liệu ngẫu nhiên đến 2005 thôi. Bạn xem tôi cũng chỉ copy và past ra 4 mẫu phân tích, mẫu nào cũng đủ bằng ấy dữ liệu. Nếu past sang workbook khác cũng sẽ đủ vậy thôi, vì trong thuộc tính của pivot có thuộc tính nguồn dữ liệu, nguồn này do bạn chọn từ đầu.
Khi dữ liệu tăng lên cho năm 2006, thì mỗi bảng Pivot đều phải định lại vùng dữ liệu nguồn.
Trường hợp của bạn không thấy dữ liệu đủ có thể là bị ẩn đi. bạn bấm vào nút mũi tên kế bên trường "năm" xem có phải mất dấu kiểm view all hay không?

 

[ptm0412]

Làm thế nào để Pivot chạy có hiệu quả mong muốn

Với số liệu này thì bạn bôi đen vùng dữ liệu và dùng tính năng Data -> Autofilter là phù hợp nhất

Đúng đây, Dùng Pivot chỉ để lọc tháng thì hơi phí.

Nói thêm về Pivot.
Đã nói về Pivot là nói 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í ĐT thì phải tách chi phí VP ra: chi phí ĐT, mực in, văn phòng phẩm, lương nhân viên VP, 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: Pivot 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.
Như vậy để cho Pivot 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 DS 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 DS đã 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.

Mong rằng các bạn ứng dụng được và ứng dụng tốt Pivot.

Nho cac ban giup minh tao pivottable and chart loc bao cao theo tung thang.

Nếu bạn chọn tiêu chí phân tích theo tháng, mà dữ liệu bạn đưa lên như vậy, không có gì khác để phân tích chẳng hạn như phân loại kq xét nghiệm hoặc phân loại vùng yêu cầu xét nghiệm thì Pivot sẽ cho 1 bảng dài bằng y dữ liệu thô, chỉ khác là có thứ tự hơn. Chẳng thà bạn sort thứ tự dữ liệu theo tháng rồi dùng hàm count() đếm mỗi tháng có bao nhiêu dòng còn hay hơn.
Bạn thử thêm vào 1 tiêu chí phân tích khác xem sao.