Bài viết: 5 phiền phức của PivotTable

[kyo]

5 phiền phức của Pivot Table bạn cần phải biết



Pivot Table là một trong những công cụ rất mạnh của Excel và rất thường được sử dụng trong nhiều trường hợp liên quan đến phân tích dữ liệu và trình bày báo cáo. Tuy nhiên, trong quá trình tương tác với Pivot Table, chắc hẳn bạn cũng phần nào nhận biết được một số mặt hạn chế của công cụ này. GPE xin giới thiệu đến bạn Top 5 phiền phức của Pivot Table mà bạn cần phải biết.

Phiền phức số 1: Những dữ liệu cũ trong danh sách xổ xuống.

Nếu bạn thay đổi dữ liệu nguồn của Pivot Table, sau đó làm mới nó, nhiều lúc bạn vẫn thấy những dữ liệu cũ vẫn còn tồn tại trong danh sách xổ xuống như hình dưới đây.

Để có thể loại bỏ nó hoàn toàn, bạn có thể thực hiện cách sau: Nhấn chuột phải vào Pivot Table -> chọn Options -> chọn thẻ Data, chọn None tại thiết lập: "Number of items to retain per field" -> OK -> làm mới lại Pivot Table.

Hoặc trong trường hợp bạn chỉ muốn chỉnh sửa tay tại thời điểm sử dụng này thôi, bạn có thể làm cách đơn giản hơn, đó là: Loại bỏ tất cả trường (field) của Pivot Table ra ngoài bằng cách kéo thả -> làm mới Pivot Table -> Kéo trả lại trường (field) mà bạn muốn xem.

Phiền phức số 2: Hàm GETPIVOTDATA.

Nếu bạn cố gắng tham chiếu đến một ô của Pivot Table từ một công thức, trong nhiều trường hợp, hàm GETPIVOTDATA sẽ xuất hiện thay vì chỉ đơn giản là địa chỉ của một ô. Không phủ nhận hàm GETPIVOTDATA là một hàm hữu ích, tuy nhiên, đa phần bạn mong muốn địa chỉ ô hơn vì sự tiện lợi của nó trong việc kéo thả công thức tự động.

Để sửa chữa điều này, bạn có thể thay đổi trong thiết lập của Pivot Table bằng cách: Chọn một ô trong Pivot Table -> tại thẻ Pivot Table trên thanh Ribbon, chọn thẻ Options-> chọn phím mũi tên xổ xuống bên cạnh nút Options -> nhấn vào lựa chọn Generate GetPivotData để tắt.

Phiền phức số 3: Hàm COUNT xuất hiện thay vì hàm SUM.

Khi bạn thêm một trường (field) vào Pivot Table, thông thường giá trị sẽ được tính bằng hàm SUM. Tuy nhiên, nhiều lúc bạn sẽ thấy hàm COUNT và đó là điều bạn không hề mong muốn.

Để sửa chữa điều này, cách dễ nhất chính là bạn tự thay đổi hàm sử dụng trong Pivot Table, hoặc bảo đảm rằng dữ liệu của bạn không có giá trị chữ hoặc ô trống. Hoặc, bạn có thể sử dụng macro sau trong trường hợp bạn có quá nhiều trường cần sửa chữa.

Sub SumAllValueFields()
  Dim pt As PivotTable
  Dim pf As PivotField
  Dim ws As Worksheet
 
  Set ws = ActiveSheet
  Set pt = ws.PivotTables(1)
  Application.ScreenUpdating = False
 
    pt.ManualUpdate = True
    For Each pf In pt.DataFields
      pf.Function = xlSum
    Next pf
    pt.ManualUpdate = False
 
  Application.ScreenUpdating = True
  Set pf = Nothing
  Set pt = Nothing
  Set ws = Nothing
End Sub

Phiền phức số 4: Dữ liệu mới không xuất hiện.

Khi bạn có thêm một dữ liệu mới, và bỗng nhiên bạn nhận ra là Pivot Table không tự động lấy dữ liệu mới này dù rằng bạn đã làm mới rất nhiều lần bởi vì địa chỉ nguồn không quét tới dữ liệu mới đó.

Bạn có thể khắc phục sự cố này bằng 2 cách:
- Cách thông thường: Bạn quét trước một khoảng dữ liệu lớn, ví dụ từ dòng 1 đến dòng 10000.
- Một cách khác chuyên nghiệp hơn: Bạn dùng liên kết động thông qua chức năng Table của Excel hoặc sử dụng Name động. Chi tiết bạn có thể tham khảo tại GPE: + Table và + Name động.

Phiền phức số 5: Tất cả dữ liệu không hiện hết.

Nếu bạn có nhiều trường trong khu vực Row, có khả năng tất cả dữ liệu của bạn sẽ không được liệt kê hết nếu nó không có đầy đủ số liệu. Ví dụ, bạn có 3 danh mục đầu tư nhưng trong dữ liệu chỉ đề cập đến hai, như vậy, trên Pivot Table chỉ hiện ra có 2 danh mục mà thôi, còn danh mục cuối cùng sẽ không được hiện ra.

Vậy, để tránh phiền phức, bạn có thể linh hoạt thay đổi thiết lập của Pivot Table. Chi tiết bạn có thể xem tại đây.

Chúc bạn thành công.

Một số bài viết có liên quan:
1/ PivotTable trong Excel 2010 - 2013
2/ Làm cách nào để hiện các danh mục không có dữ liệu trong Pivot Table?
3/ PivotTable & PivotChart 2007 - Từ căn bản đến nâng cao (phần 7)
4/ PivotTable & PivotChart 2007 - Từ căn bản đến nâng cao (phần 6)
5/ PivotTable & PivotChart - Từ căn bản đến nâng cao (phần 5)
6/ PivotTable & PivotChart - Từ căn bản đến nâng cao (phần 4)
7/ PivotTable & PivotChart - Từ căn bản đến nâng cao (phần 3)
8/ PivotTable & PivotChart - Từ căn bản đến nâng cao (phần 2)
9/ PivotTable & PivotChart - Từ căn bản đến nâng cao (phần 1)
10/ Ebook: Dữ liệu & Báo cáo trong Excel 2013

 

[nhocwaila]

Cho em hỏi 1 gúc mắt mà từ đó đến giờ em mò hoài không ra. Làm sao để sử dụng pivot table cho các dữ liệu mà 1 dòng trả lời cho nhiều cột. ví dụ: 1 người sẽ có nhiều sở thích, khi được hỏi bạn có những sở thích nào, thì sẽ có 2, 3 câu trả lời (như bên dưới).
câu hỏi: thống kê theo Sở thích theo từng giới tính?

P/s: Câu hỏi này chỉ xoay quanh Pivot Table thôi, em không bàn đến trường hợp sử dụng VBA hoặc phải format lại bảng tính. Dùng SPSS thì không nói làm gì.

ID	Gioitinh	Sothich_1	Sothich_2	Sothich_3	Sothich_4	Sothich_5
1001	1	1	2	3
1002	2	3	1	2
1003	1	1	2
1004	2	4	5	6
1005	1	6	7	8
1006	1	7	8	9	10	11

Hóng cao nhân ạ. Kể từ khi thành thạo pivot table (hơi tự tin ) đến giờ, câu hỏi này cứ quanh quẩn trong đầu em

 

[Lê Duy Thương]

Cho em hỏi 1 gúc mắt mà từ đó đến giờ em mò hoài không ra. Làm sao để sử dụng pivot table cho các dữ liệu mà 1 dòng trả lời cho nhiều cột. ví dụ: 1 người sẽ có nhiều sở thích, khi được hỏi bạn có những sở thích nào, thì sẽ có 2, 3 câu trả lời (như bên dưới).
câu hỏi: thống kê theo Sở thích theo từng giới tính?

P/s: Câu hỏi này chỉ xoay quanh Pivot Table thôi, em không bàn đến trường hợp sử dụng VBA hoặc phải format lại bảng tính. Dùng SPSS thì không nói làm gì.

ID	Gioitinh	Sothich_1	Sothich_2	Sothich_3	Sothich_4	Sothich_5
1001	1	1	2	3
1002	2	3	1	2
1003	1	1	2
1004	2	4	5	6
1005	1	6	7	8
1006	1	7	8	9	10	11

Hóng cao nhân ạ. Kể từ khi thành thạo pivot table (hơi tự tin ) đến giờ, câu hỏi này cứ quanh quẩn trong đầu em

xin hỏi bạn cái chữ màu đỏ là gì mà tôi nghĩ mãi vẫn không dịch được ra nghĩa.
bảng của bạn là bảng data của pivot table hay là bảng kết quả.
Bạn nên đưa file lên cho tiện

 

[Lê Duy Thương]

Để khắc phụ phiền phức số 1 của pivot table có thể dùng cách này nữa :

Sub CLEAR_PIVOTCACHE()


 Dim WS As Worksheet, PVT As PivotTable
  For Each WS In Worksheets
    For Each PVT In WS.PivotTables
      With PVT.PivotCache
        .MissingItemsLimit = xlMissingItemsNone
        .Refresh
      End With
    Next PVT
  Next WS
  Set PVT = Nothing
  Set WS = Nothing
End Sub

 

[nhocwaila]

xin hỏi bạn cái chữ màu đỏ là gì mà tôi nghĩ mãi vẫn không dịch được ra nghĩa.
bảng của bạn là bảng data của pivot table hay là bảng kết quả.
Bạn nên đưa file lên cho tiện

SPSS là chương trình thống kê thôi. Ý mình là có thể nào chỉ dùng Pivot Table chạy những câu có cấu trúc như trên hay không?

 

[nhocwaila]

xin hỏi bạn cái chữ màu đỏ là gì mà tôi nghĩ mãi vẫn không dịch được ra nghĩa. bảng của bạn là bảng data của pivot table hay là bảng kết quả.
Bạn nên đưa file lên cho tiện

File Demo này đây, khung vẽ là kết quả muốn pivot chạy, còn bảng pivot là cách mà em làm
http://www.mediafire.com/download/bbz8qhikboxce32/Demo.xlsx

 

[giaro]

PP số 4 cũng có thể chỉ cần dùng chiêu này: chèn dữ liệu mới (insert copied cells) vào hàng đầu ngay dưới tên trường (cho nó tiện), rồi làm mới là ok thôi!