Bài viết: 5 kỹ thuật nâng cao cho PivotTable

[kyo]

5 kỹ thuật nâng cao cho PivotTable


Bạn đã biết cách khởi tạo PivotTable chưa? Nếu bạn chưa biết mời bạn tham khảo bài viết này.
Nếu bạn đã biết rồi, vậy bạn có biết rằng, PivotTable không chỉ có kéo thả, lọc và tùy chỉnh dữ liệu theo ý muốn của bạn, nhưng bạn có thể sử dụng một số kỹ thuật nâng cao hơn để giúp cho PivotTable của bạn linh động hơn, đáp ứng nhu cầu của bạn nhiều hơn nữa.

Trong bài viết này, GPE trước nhất muốn gửi đến cho bạn 5 kỹ thuật. Chúng ta hãy cùng xem nhé.

1/ Sử dụng Slicer

Slicer là một công cụ mới xuất hiện từ Excel 2010. Nó cung cấp cho bạn một góc nhìn trực quan hơn hẳn dùng Filter. Và dĩ nhiên, khi mọi thứ được hiển hiện trước mắt, thao tác sau đó cũng sẽ dễ dàng hơn và khiến cho báo cáo, cho file của bạn thêm tiện dụng cho những người sử dụng khác.

Để có thể kích hoạt chức năng này, bạn có thể làm như sau:
1/ Chọn 1 PivotTable bạn muốn áp dụng -> chọn thẻ Insert của thanh Ribbon -> Chọn Slicer.

2/ Từ đây, bạn có thể chọn 1 trường mà bạn muốn thể hiện các dữ liệu để tiến hành filter sau này. Bạn có thể chọn 1 hoặc nhiều trường cùng một lúc. Giả sử trong hình dưới đây tôi chọn trường Department và Status. Và tôi sẽ có kết quả như sau:

Bạn có thể thấy là tất cả giá trị của trường Department và Status sẽ hiện lên rõ nét cho bạn. Và khi bạn cần lọc gì, bạn chỉ cần bấm nút tương ứng thay vì chọn Filter như cách truyền thống.

2/ Sử dụng Calculated Field

Đây là một chức năng đầy sức mạnh khác của PivotTable. Nó giúp bạn có thể tính toán những vấn đề khác thông qua các trường mà bạn đã có sẵn. Ví dụ, nếu bạn có trường Thời gian và Lương, thay vì bạn kéo thành 2 cột Thời gian và Lương trong PivotTable để rồi sau đó tính toán bằng tay theo kiểu =Lương*Thời gian, bạn có thể sử dụng Calculated Field để trực tiếp tạo một field mới theo đúng công thức mà bạn cần.

Bạn có thể thực hiện bằng cách chọn vào PivotTable, sau đó chọn thẻ Options trên thanh Ribbon -> chọn Fields, Items & Sets -> Calculated Field.

Một hộp thoại sẽ hiện lên và bạn gõ tên field cùng công thức tương ứng -> chọn OK.

Ưu điểm của cách làm này chính là việc bạn tạo mới được 1 field và nó sẽ cùng với bạn tạo nên những báo cáo tùy chỉnh kéo thả. Bởi vì, nếu bạn tính toán bằng tay dựa trên công thức ở ngoài PivotTable, khi PivotTable bạn thay đổi khi kéo thả, chắc chắn công thức tay này sẽ không còn đúng nữa.

Ngoài ra, một số vấn đề khác cũng cần lưu ý như bẫy Average trong PivotTable mà bạn sẽ dễ mắc phải nếu không sử dụng Calculated Field này.

3/ Dùng Conditional Formatting cho PivotTable

Dù cho bạn đang xài PivotTable, Conditional Formatting vẫn tỏ ra hiệu nghiệm như bình thường. Bạn chỉ cần vào thẻ Home trên thanh Ribbon -> chọn Conditional Formatting và thực hiện tất cả như bạn làm với ô, bạn sẽ có kết quả như bạn mong muốn.

Chẳng hạn như ví dụ của tôi, tôi chọn Conditional Formatting là Data Bars để xem tỷ lệ về dữ liệu của những người có trong danh sách.

Ưu điểm của vấn đề này chính là việc Conditional Formatting sẽ theo sát PivotTable của bạn, nghĩa là kể cả khi bạn kéo thả, Conditional Formatting sẽ chạy theo đúng với field mà bạn đã thiết lập từ trước và bạn sẽ không phải tốn công để chỉnh sửa lại điều kiện như phải làm với ô.

4/ Chuyển đổi dữ liệu của bạn thành dạng %

Quay trở lại dữ liệu của tôi mà bạn thấy trên hình trên, bây giờ tôi muốn biến nó thành dạng % tích lũy tăng dần để vẽ biểu đồ Pareto thì tôi phải làm như thế nào? Chắc chắn là bạn không nên tính toán bằng tay ra tỷ lệ % rồi sau đó cộng dồn lại.

Chúng ta có thể làm theo một cách khác như sau: Bấm vào giá trị mà bạn cần chuyển đổi ngay trên vùng Values của PivotTable -> chọn Value Field Settings.

Một cửa sổ sẽ hiện lên, và bạn chọn qua thẻ Show Values As. Ngay tại đây, bạn sẽ có rất nhiều lựa chọn tùy theo vào mục đích bạn muốn biến dữ liệu ra sao. Với tôi, nếu tôi muốn tùy biến thành % tích lũy, tôi có thể chọn vào % Running Total In -> OK.

Và dữ liệu của bạn sẽ biến chuyển thành % tích lũy ngay lập tức.

5/ Group (nhóm) các dữ liệu

Bạn có những dữ liệu rời rạc chẳng hạn như các tháng với nhau, và bạn muốn tính toán theo từng quý chẳng hạn, vậy bạn sẽ làm thế nào? Chúng ta sẽ có vài cách như sau:
1/ Trình diễn hết 12 tháng cùng dữ liệu tương ứng, sau đó tính tổng bằng hàm SUM bên ngoài để lấy giá trị tổng.
2/ Hoặc bạn chuyển qua dữ liệu gốc và thêm một cột Quý để PivotTable có thêm Quý nhằm giúp bạn kéo thả dễ dàng hơn.
3/ Sử dụng chức năng Group của PivotTable

Chúng ta có thể phân định cách nào tốt hơn qua ví dụ rời rạc hơn nữa như bài toán, tìm trung bình của 3 tháng 1, 5 và 7. Bạn có thể thấy bài toán này không có quy luật như bài toán Quý kể trên.

Và bạn cũng sẽ dễ dàng nhận thấy, cách 1 chỉ mang tính chất mì ăn liền giải quyết ngay tại chỗ, nhưng nó không thể theo bạn trong việc chuyển đổi bằng kéo thả vốn dĩ là đặc trưng của PivotTable. Bên cạnh đó, trong trường hợp tính kết quả tháng 1, 5 và 7, sử dụng công thức rất cập rập và khó khăn.

Cách 2 cũng không khá hơn vì không có quy luật nào tồn tại ở đây thì sao bạn có thể điền dữ liệu cho một cột mới?

Cách 3 là ổn thỏa hơn cả. Quả thật, chức năng Group của PivotTable rất tốt, nó giúp bạn group lại các dữ liệu rời rạc mà bạn cho rằng nó liên quan để có thể có được kết quả tổng, trung bình,… từ nó một cách dễ dàng mà không cần phải tốn sức sử dụng bất kỳ phương pháp làm tay nào cả.

Chi tiết hơn nữa, mời bạn xem một ví dụ tại bài viết này.

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

Một số bài viết có liên quan:
1/ Loại bỏ GETPIVOTDATA mà không cần tắt nó
2/ 3 cách đơn giản nhất để lọc danh sách duy nhất
3/ Tạo nhiều Subtotal trong PivotTable
4/ 5 phiền phức của PivotTable
5/ Làm cách nào để hiện các danh mục không có dữ liệu trong Pivot Table?
6/ PivotTable & PivotChart 2007 - Từ căn bản đến nâng cao (phần 7)
7/ PivotTable & PivotChart 2007 - Từ căn bản đến nâng cao (phần 6)
8/ PivotTable & PivotChart - Từ căn bản đến nâng cao (phần 5)
9/ PivotTable & PivotChart - Từ căn bản đến nâng cao (phần 4)
10/ PivotTable & PivotChart - Từ căn bản đến nâng cao (phần 3)