PivotTable là một tính năng tiện lợi dùng để báo cáo trong Excel. Nhưng vẫn còn rất nhiều thắc mắc về cách dùng PivotTable nâng cao. Vậy nên hôm nay Admin Edu sẽ giúp bạn tìm hiểu về các thủ thuật nâng cao của PivotTable một cách chi tiết nhất.
1. Sử dụng Slicer
Slicer là công cụ cực kỳ hữu ích trong việc phân tích nhiều dữ liệu. Slicer chính xác là gì? Nói một cách đơn giản Slicer dùng để liên kết nhiều Pivottables với nhau từ đó bạn có thể lọc dữ liệu của mình từ những bảng đó cùng một lúc, thay vì bạn phải lọc dữ liệu trên từng bảng tổng hợp riêng lẻ.
Giả sử trường hợp sau đây là Jason đang xem xét hai bảng tổng hợp khác nhau: Một bảng hiển thị doanh số bia bán được theo quý và một bảng hiển thị doanh số bán bia theo quy mô. Hiện tại anh ấy đang cân nhắc dữ liệu cho cả 2 năm là 2016, 2017:
Điều mà Jason muốn là đi sâu xem doanh số bán bia theo quý và theo quy mô chỉ riêng năm 2016. Thay vì lọc trên cả 2 năm đó thì anh ta có thể tạo ra một bộ lọc riêng cho năm 2016. Cách thực hiện điều đó như sau:
Bước 1: Nhấp vào bảng tổng hợp.
Bước 2: Chọn “Insert” sau đó nhấp nút “Slicer”. Chọn biến số bạn muốn sắp xếp dữ liệu của mình (trong trường hợp này là “Year”) sau đó nhấp nút “ok”.
Bước 3: Thay đổi kích thước và di chuyển slicer của bạn đến nơi bạn muốn nó xuất hiện. Lời khuyên bạn nên đặt đó trên đầu bảng tổng hợp của bạn để bạn có thể dễ dàng xem mọi thứ.
Bước 4: Bây giờ Jason cần liên kết các PivotTables hiện có với Slicer để tất cả dữ liệu được liên kết với những Slicer cụ thể. Để thực hiện thì nhấp chuột phải vào Slicer, chọn “Report Connections,”, sau đó cửa sổ hiện lên bạn chọn các pivottables kết nối với Slicer đó.
Với thiết lập Slicer đó, Jason chỉ cần nhấn nút trên slicer đó là có thể nhìn thấy dữ liệu năm 2016 như trong bảng dưới đây. Mặc dù việc thiết lập Slicer là một công việc nhỏ nhưng nó cực kỳ quan trọng giúp giải quyết công việc tốt hơn, đặc biệt nếu bạn đang sử dụng nhiều Pivottable khác nhau.
2. Tạo Calculated Field
Excel là một công cụ mạnh mẽ trong việc giải quyết những tính toán và khi làm việc với nhiều Pivottable thì điều chắc chắn bạn cần biết đến đó là sử dụng Calculated Field.
Calculated Field cho phép bạn duy trì một phép tính xuyên suốt các Pivottable – tương tự như cách bạn sử dụng các công thức cắm vào bảng tính chuẩn thông thường.
Jason muốn tính toán lợi nhuận từng loại bia mà anh ta kinh doanh: Pilsner, Stout, Amber và IPA. Nếu như tự mình tính toán lợi nhuận bên ngoài PivotTable thì khá phức tạp vì phải trừ chi phí Q1 khỏi doanh thu Q1, trừ chi phí Q2 khỏi doanh thu Q2 và tương tự…
Bây giờ Jason có thể thiết lập một trường tính toán tự động thu thập các con số và sau đó trả kết quả lợi nhuận đối với từng loại bia. Để thực hiện làm các bước sau:
Bước 1: Nhấp vào ô bất kỳ trên bảng tổng hợp, chọn “PivotTable Analyze” trên thanh công cụ, sau đó nhập vào “Calculated Field”.
Bước 2: Trong cửa sổ bật lên, nhập tên của trường mới được tính toán (Trong trường hợp này thì Jason sẽ tên là “Profit” hoặc cái gì đó tương tự).
Bước 3: Bây giờ Jason nhập công thức để tính toán. Để tính ra lợi nhuận thì lấy doanh thu trừ chi phí. Trong ô công thứ Jason sẽ nhập công thức “=Sale-Cost”. “Sale và Cost thì Jason sẽ lần lượt chọn trong bảng Field bên dưới và nhấn “”Insert Field”. Sau đó nhấp Ok.
Với trường được tính toán đó, Jason có thể dễ dàng xem lợi nhuận của mình cho từng loại bia, cũng như tổng lợi nhuận lớn – ở hàng dưới cùng của bảng tổng hợp.
3. Tạo nhiều PivotTable từ một PivotTable gốc
Khi bạn muốn chia nhỏ dữ liệu của mình từ một PivotTable thành nhiều PivotTable nhỏ thì bạn có thể sử dụng thủ thuật hữu ích dưới đây.
Ví dụ: Jason có một bảng tổng hợp hiển thị doanh số bán bia theo quý. Anh ấy muốn đi sâu vào doanh số bia trong mỗi quý với từng loại bia (Amber, Pilsner, IPA hoặc Stout).
Để làm như vậy, Jason sẽ tạo một PivotTable cho từng loại bia: một cho Amber, một cho Pilsner … Để làm điều đó chỉ cần vài cú nhấp chuột bằng cách sử dụng bảng Pivottable gốc và thực hiện như sau:
Bước 1: Dù bạn muốn lọc bảng tổng hợp của mình theo cách nào (trong tình huống này là lọc theo loại bia) thì bạn cũng càn áp cái bảng đó vào bộ lọc. Nhấp vào bảng Pivottable, trên thanh công cụ chọn “PivotTable Analyze” , nhấp vào “Fields List” sau đó bạn kéo Type vào Filters.
Bước 2: Bộ lọc được áp dụng, Jason nhấp vào bên trong bảng tổng hợp, quay lại tab “PivotTable Analyze” trên thanh công cụ rồi nhấp vào Options rồi chọn “ Show report Filters Page” . Sau đó đánh dấu vào “Type” và chọn Ok. Excel sẽ tạo một trang tính mới với một bảng tổng hợp từng loại bia.
4. Ẩn hoặc hiện tổng phụ
Như đã đề cấp thì Excel có thể giúp bạn hạn chế những sai số có thể xả ra. Điều này không chỉ áp dụng cho các tổng số mà còn có thể hiển thị tổng phụ trong bảng tổng hợp nếu bạn muốn.
Giả sử Jason đang xem xét dữ liệu về doanh số bán bia của mình theo Size (quy mô) và theo loại (Type). Nếu đặt giá trị mặc định theo cách của anh ấy thì Excel sẽ không hiển thị tổng phụ cho mỗi tiêu chí mà chỉ hiển thị doanh thu bán bia của anh ấy.
Jason sẽ xem xét dữ liệu được chia nhỏ:
Bước 1: Nhấp vào bên trong bảng và nhấp vào tab “Design” trên thanh công cụ.
Bước 2: Nhấp vào “ Subtotal” và sau đó chọn hiển thị tổng phụ ở cuối hoặc đầu nhóm của bạn (cách nào cũng được tùy vào lựa chọn của bạn).
Bước 4: Sau khi làm theo những bước trên thì các tổng phụ cho từng Size của sản phẩm bia sẽ hiển thị. Nếu muốn xóa tổng phụ thì có thể thực hiện theo các bước tương tự và chọn “Hide subtotal”.
5. Cách xem chi tiết từng dữ liệu trong bảng tổng hợp
Đây là một kỹ thuật giúp bạn tiết kiệm thời gian và tìm hiểu dữ liệu của mình. Bạn có thể xem chi tiết bất kỳ dữ liệu trong bảng tổng hợp chỉ bằng cách nhấp đúp vào đó.
Ví dụ: Jason đang xem bảng tổng hợp doanh số bán bia theo quarter (quý) và theo size (quy mô) trong 2017. Anh ấy muốn xem thông tin chi tiết về doanh số bia của mình trong Q1. Chúng ta chỉ cần nhấp đúp vào số đó trong excel sẽ mở ra thông tin chi tiết trong một trang tính mới.
6. Làm mới dữ liệu
Nếu như bạn làm việc với dữ liệu của mình hàng giờ đồng hồ, xây dựng hàng chục bảng tổng hợp khác nhau từ dữ liệu gốc như ví dụ Jason ở trên. Những tình huống xấu nhất đó là bạn nhận ra bạn mắc lỗi như lỗi đánh máy trong tập dữ liệu. giả dụ đánh nhầm “Growler” là “growler” và bây giờ nó hiển thị ở tất cả các bảng.
Nếu sửa một cách thủ công thì Jason phải xem qua cửa sổ làm việc của mình một cách tỉ mỉ, chi tiết để sửa tất cả các lỗi đó trong dữ liệu và các Pivottable. Tuy nhiên điều này rất mất thời gian và chúng ta chỉ cần làm những bước đơn giản như sau:
Bước 1: Quay lại tập dữ liệu gốc mà bạn dùng để tạo các bảng Pivottable nhỏ và thực hiện “Find and Place”. Nhấn Ctrl+F và sau đó nhập những gì bạn muốn và tất cả sẽ được thay đổi cho những vị trí mà bạn nhầm lẫn.
Bước 2: Khi làm như vậy sẽ sửa tất các lần xuất hiện của “Grolwer” trong tập dữ liệu nhưng sẽ không sửa được bất kỳ pivottables mà được liên kết với nó.
Bước 3: Để thực hiện cập nhật đó các vị trí mắc lỗi, hãy chuyển đến tab “Data” trong thanh công công cụ và sau đó nhấp vào nút “Refresh all”. Điều đó sẽ thực hiện chỉnh sửa tương tự trên toàn bộ sổ làm việc.
Trên đây là các kỹ thuật sử dụng PivotTable nâng cao mà bạn nên biết. Việc tóm tắt dữ liệu và phân tích sẽ dễ dàng hơn khi bạn trang bị những kiến thức nâng cao về PivotTable. Cảm ơn bạn đã đọc bài viết. Chúc bạn thành công!
