Thông thường, khi tạo Pivot Table, bạn cần mở rộng phân tích và bao gồm nhiều dữ liệu/tính toán. Nếu bạn cần tính toán chỉ tiêu mới bằng cách sử dụng các dữ liệu hiện có trong Pivot Table, bạn không cần quay lại và thêm nó vào dữ liệu nguồn. Thay vào đó, bạn có thể sử dụng Pivot Table Calculated Field để thực hiện việc này.
Pivot Table Calculated Field là gì?
Hãy bắt đầu với một ví dụ cơ bản về Pivot Table.
Giả sử bạn có bộ dữ liệu của các nhà bán lẻ và bạn tạo Pivot Table như dưới đây:
Pivot Table ở trên tóm tắt các giá trị doanh thu và lợi nhuận cho các nhà bán lẻ.
Bây giờ, điều gì sẽ xảy ra nếu bạn cũng muốn biết tỷ suất lợi nhuận của các nhà bán lẻ này là bao nhiêu ? [trong đó tỷ suất lợi nhuận là ‘Profit’ chia cho ‘Sales’].
Thay vì Quay trở lại tập dữ liệu gốc và thêm điểm dữ liệu mới này hoặc thêm các tính toán bên ngoài Pivot Table, bạn có thể sử dụng Pivot Table Calculated Field.
Đây là cách hiệu quả nhất để sử dụng dữ liệu Pivot Table hiện có và tính toán số liệu mong muốn. Xem xét Calculated Field như một cột ảo mà bạn đã thêm bằng các cột hiện có từ Pivot Table. Có rất nhiều lợi ích của việc sử dụng Pivot Table Calculated Field
- – Nó không yêu cầu bạn xử lý các công thức hoặc cập nhật dữ liệu nguồn.
- – Có thể mở rộng vì nó sẽ tự động chiếm bất kỳ dữ liệu mới nào bạn có thể thêm vào Pivot Table của mình. Khi bạn thêm Calculated Field, bạn có thể sử dụng nó như bất kỳ trường nào khác trong Pivot Table của bạn.
- – Dễ dàng để cập nhật và quản lý. Ví dụ: nếu số liệu thay đổi hoặc bạn cần thay đổi phép tính, bạn có thể dễ dàng thực hiện điều đó từ chính Pivot Table .
Thêm Calculated Field vào Pivot Table
Chúng ta hãy xem cách thêm Pivot Table Calculated Field trong Pivot Table hiện có.
Giả sử bạn có Pivot Table như được hiển thị bên dưới và bạn muốn tính tỷ suất lợi nhuận cho mỗi nhà bán lẻ:
Dưới đây là các bước để thêm Calculated Field trong Pivot Table :
- – Đặt tên cho nó bằng cách nhập nó vào mục Name.
- – Trong mục Formula, tạo công thức bạn muốn cho calculated field. Lưu ý rằng bạn có thể chọn từ các tên trường được liệt kê bên dưới nó. Trong trường hợp này, công thức là ‘= Profit/ Sales’. Bạn có thể nhập thủ công tên trường hoặc nhấp đúp vào tên trường được liệt kê trong mục Fields.
- Nhấp vào Add và đóng hộp thoại.
Ngay khi bạn thêm Calculated Field, nó sẽ xuất hiện trong danh sách PivotTable Fields.
Bây giờ, bạn có thể sử dụng Calculated Field này như bất kỳ trường Pivot Table nào khác [lưu ý rằng bạn không thể sử dụng Calculated Field của Pivot Table làm filter or slicer].
Như tôi đã đề cập trước đây, lợi ích của việc sử dụng Calculated Field trong Pivot Table là bạn có thể thay đổi cấu trúc của Pivot Table và nó sẽ tự động điều chỉnh.
Ví dụ: nếu tôi kéo và thả ‘region’ vào hàng, bạn sẽ nhận được kết quả hiển thị bên dưới
Đây là một số điều bạn phải biết:
- – Bạn KHÔNG THỂ sử dụng các tham chiếu hoặc named ranges trong khi tạo Calculated Field. Điều đó sẽ loại trừ rất nhiều công thức như VLOOKUP , INDEX , OFFSET , v.v. Tuy nhiên, bạn có thể sử dụng các công thức không cần tham chiếu [chẳng hạn như SUM, IF, COUNT, v.v.].
- – Bạn có thể sử dụng một hằng số trong công thức. Ví dụ: nếu bạn muốn biết doanh số dự báo nơi dự báo tăng 10%, bạn có thể sử dụng công thức =Sales*1.1 [trong đó 1.1 là hằng số].
- – Thứ tự ưu tiên được tuân theo trong công thức tạo ra calculated field. Cách tốt nhất là sử dụng dấu ngoặc đơn để đảm bảo bạn không phải nhớ thứ tự ưu tiên.
Làm cách nào để sửa đổi hoặc xóa Pivot Table Calculated Field?
Khi bạn đã tạo Calculated Field trong Pivot Table, bạn có thể sửa đổi công thức hoặc xóa nó bằng các bước sau:
Khi tạo PivotTable, chúng ta cần kéo từng field [trường] vào Row Labels hoặc Value theo cách thủ công. Nếu có một danh sách dài các trường, chúng ta có thể thêm một vài tên dòng một cách nhanh chóng, nhưng các trường còn lại nên được thêm vào vùng Value. Vậy có cách nào nhanh chóng để chúng ta có thể thêm tất cả các trường khác vào vùng Value chỉ bằng một cú nhấp chuột trong bảng tổng hợp không?
Thật tiếc là không có hộp checkbox nào để chúng ta có thể chọn tất cả các trường trong Pivot Table Field List chỉ bằng một cú click, nhưng với mã VBA sau có thể giúp bạn thêm các trường còn lại vào vùng Value cùng một lúc. Bây giờ, các bạn hãy thao tác theo các bước sau:
Bước 1: Sau khi tạo bảng tổng hợp, đầu tiên, bạn nên thêm các trường row label theo nhu cầu của mình và để lại các trường value trong Choose Fields để thêm vào report list:
Bước 2: Nhấn tổ hợp phím ALT + F11 để mở cửa sổ Microsoft Visual Basic for Applications [VBA].
Bước 3: Click Insert => Module và dán mã sau vào Module Window.
Sub AddAllFieldsValues[] 'Update 20141112 Dim pt As PivotTable Dim I As Long For Each pt In ActiveSheet.PivotTables For I = 1 To pt.PivotFields.Count With pt.PivotFields[I] If .Orientation = 0 Then .Orientation = xlDataField End With Next Next End Sub
Bước 4: Sau đó nhấn phím F5 để chạy mã này, tất cả các trường còn lại đã được thêm vào vùng Value cùng một lúc:
Lưu ý: Mã VBA này áp dụng cho tất cả các bảng tổng hợp của trang tính đang hoạt động.
Trên đây là những hướng dẫn các bạn cách để thêm nhiều field vào PivotTable. Hi vọng các bạn có thể vận dụng nó trong thực tế để làm việc hiệu quả hơn. Chúc các bạn thành công!
Thời đại công nghệ 4.0 đang dần đi vào cuộc sống đòi hỏi mọi người phải tự trang bị kiến thức tin học cho phù hợp để có thể bắt kịp nhưng thay đổi nhanh chóng này. Chẳng ai khác ngoài bạn hiểu mình cần trang bị thêm kiến thức gì. Hãy tìm hiểu ngay TẠI ĐÂY những khóa học hấp dẫn, có tính thực tế cao của Gitiho và đăng ký nhận tư vấn ngay hôm nay. Chi tiết tại Gitiho.com