Bảng Pivot mạnh mẽ cho phân tích dữ liệu, và với các kỹ thuật nâng cao như Trường Tính Toán và Nhiều Nguồn Dữ Liệu, bạn có thể khai phá thêm thông tin chi tiết. Trong bài viết này, chúng tôi sẽ giải thích và chỉ các kỹ thuật Bảng Pivot nâng cao, tập trung vào Trường Tính Toán và sử dụng Nhiều Nguồn Dữ Liệu trong Bảng Pivot.
Tạo Trường Tính Toán Trong Bảng Pivot
Trường Tính Toán tạo các tính toán tùy chỉnh trong Bảng Pivot dựa trên dữ liệu hiện có. Các trường tính toán thêm một cột mới vào Bảng Pivot, nơi các giá trị được dẫn xuất từ các trường hiện có. Điều này hữu ích khi bạn muốn thực hiện tính toán trên dữ liệu của mình mà không cần sửa đổi tập dữ liệu gốc.
Hãy tính hoa hồng 10% trên bán hàng từ tập dữ liệu bán hàng bằng cách sử dụng Trường Tính Toán trong Bảng Pivot. Để sử dụng trường tính toán, bạn cần tạo một Bảng Pivot trước.
Bước 1: Tạo Bảng Pivot.
- Chọn phạm vi dữ liệu của bạn.
- Vào tab Chèn >> chọn Bảng Pivot.
- Chọn nơi đặt Bảng Pivot của bạn (Sheet Mới/Sheet Hiện Có).
Bây giờ, kéo và thả các trường cần thiết vào các phần Hàng, Cột, Giá Trị và Bộ Lọc.
Bước 2: Chèn Trường Tính Toán.
- Nhấp vào bất kỳ đâu bên trong Bảng Pivot của bạn.
- Vào tab Phân Tích Bảng Pivot >> từ Trường, Mục & Tập Hợp >> chọn Trường Tính Toán.
- Trong hộp thoại Chèn Trường Tính Toán:
- Trong hộp Tên, nhập tên cho trường tính toán của bạn: Hoa Hồng Trên Bán Hàng (10%)
- Trong hộp Công Thức, chèn công thức sau:
- = 0.01 * 'Tổng Doanh Thu'
- Chọn Thêm >> nhấp vào OK để thêm trường vào Bảng Pivot của bạn.
Bây giờ cột Trường Tính Toán mới Hoa Hồng Trên Bán Hàng (10%) sẽ được thêm vào Bảng Pivot.
Mẹo: Trường Tính Toán hoạt động tốt nhất cho các tính toán cộng dồn, nhưng cho các tính toán phi cộng dồn hoặc phức tạp hơn, hãy sử dụng Power Pivot.
Tạo Bảng Pivot Từ Nhiều Nguồn Dữ Liệu
Kết hợp dữ liệu từ nhiều bảng (hoặc nguồn) trong một Bảng Pivot đơn lẻ cho phép bạn phân tích các tập dữ liệu toàn diện hơn mà không cần hợp nhất bảng thủ công.
Hãy giả sử bạn có nhiều nguồn dữ liệu, một là dữ liệu Bán Hàng chứa thông tin bán hàng và một là dữ liệu Sản Phẩm chứa thông tin sản phẩm. Chúng tôi sẽ chỉ cách bạn có thể sử dụng nhiều nguồn dữ liệu này trong Bảng Pivot.
Phương Pháp 1: Sử Dụng Mô Hình Dữ Liệu Tích Hợp Của Excel
Bằng cách sử dụng tính năng Mô Hình Dữ Liệu, bạn có thể sử dụng nhiều nguồn dữ liệu trong Bảng Pivot. Excel 2013 và các phiên bản sau cung cấp tính năng Mô Hình Dữ Liệu tích hợp này.
Bước 1: Thiết Lập Mỗi Bảng Trong Excel.
- Chọn phạm vi ô >> đi đến tab Chèn >> chọn Bảng.
- Để hiểu tốt hơn, bạn có thể đặt tên cho mỗi bảng (ví dụ: Sales_Data cho bảng Bán Hàng và Products_Table cho bảng Sản Phẩm).
Bước 2: Chèn Bảng Pivot Sử Dụng Mô Hình Dữ Liệu.
- Chọn Bảng >> đi đến Chèn tab >> chọn Bảng Pivot.
- Trong hộp thoại Tạo Bảng Pivot,
- Chọn nơi đặt Bảng Pivot của bạn (Sheet Mới/Sheet Hiện Có).
- Nhấp vào Thêm Dữ Liệu Này Vào Mô Hình Dữ Liệu.
- Nhấp OK.
Bước 3: Tạo Mối Quan Hệ.
- Đi đến tab Dữ Liệu>> từ Công Cụ Dữ Liệu >> chọn Mối Quan Hệ.
- Trong hộp thoại Quản Lý Mối Quan Hệ:
- Nhấp Mới, sau đó chọn Product_ID từ cả bảng Bán Hàng và Sản Phẩm để thiết lập mối quan hệ.
- Nhấp OK để lưu mối quan hệ.
Bước 4: Xây Dựng Bảng Pivot.
- Bây giờ, trong ngăn Trường Bảng Pivot, bạn sẽ thấy các trường từ cả hai bảng.
- Kéo Danh Mục và Sản Phẩm từ bảng Product_Table vào Hàng.
- Kéo Số Lượng Bán từ bảng Sales_Data vào Cột.
- Kéo Tổng Doanh Thu từ bảng Sales_Data vào Giá Trị để xem tổng số lượng và bán hàng theo sản phẩm.
Với mối quan hệ được thiết lập, Bảng Pivot của bạn bây giờ có thể kéo các trường từ cả hai bảng, hiển thị tóm tắt bán hàng, tên sản phẩm và danh mục mà không cần hợp nhất thủ công.
Phương Pháp 2. Sử Dụng Power Pivot Cho Nhiều Nguồn Dữ Liệu
Tiện ích bổ sung Power Pivot của Excel có sẵn trong các phiên bản như Excel 2016, 2019 và Office 365 Professional & Enterprise cho phép bạn tạo mối quan hệ giữa các bảng từ các nguồn khác nhau. Cách tiếp cận này cho phép bạn tạo một Bảng Pivot dựa trên nhiều bảng và thực hiện phân tích trên chúng.
Bước 1: Bật Power Pivot (nếu chưa được bật).
- Đi đến tab Tệp >> chọn Tùy Chọn >> chọn Tiện Ích Bổ Sung.
- Ở dưới cùng của cửa sổ, trong menu thả xuống Quản Lý >> chọn COM Add-ins >> nhấp Đi.
- Chọn Microsoft Power Pivot for Excel >> nhấp OK.
Bước 2: Tải Dữ Liệu Vào Power Pivot.
- Đi đến tab Dữ Liệu >> chọn Lấy Dữ Liệu để nhập dữ liệu từ các nguồn khác nhau (ví dụ: nhiều sheet, cơ sở dữ liệu bên ngoài, tệp CSV).
- Chọn nguồn dữ liệu (Sổ Làm Việc Excel, SQL Server, Văn Bản/CSV, v.v.), sau đó nhấp Tải Đến.
- Từ hộp thoại Nhập Dữ Liệu:
- Chọn Bảng.
- Chọn Sheet Mới.
- chọn hộp kiểm cho Thêm Dữ Liệu Này Vào Mô Hình Dữ Liệu.
Lưu Ý: Mỗi bảng bạn tải nên có một mã định danh duy nhất (như ID Sản Phẩm hoặc ID Khách Hàng) để cho phép mối quan hệ giữa các bảng.
Bước 3: Tạo Mối Quan Hệ Giữa Các Bảng.
- Đi đến tab Power Pivot >> chọn Quản Lý để mở Trình Chỉnh Sửa Power Pivot.
- Đi đến Chế Độ Xem Sơ Đồ để xem tất cả các bảng của bạn trực quan.
- Kéo và thả các trường giữa các bảng để tạo mối quan hệ. Kết nối Product ID từ Bảng Bán Hàng đến Product ID trong Bảng Sản Phẩm).
- Đi đến tab Trang Chủ trong Power Pivot >> chọn Bảng Pivot.
- Chọn nơi đặt Bảng Pivot (ví dụ: trên sheet mới) và nhấp OK.
- Trong Danh Sách Trường Bảng Pivot, bạn sẽ thấy các trường từ tất cả các bảng. Sử dụng các trường này để tạo báo cáo kéo dữ liệu từ nhiều nguồn.
- Kéo Sản Phẩm từ Product_Table vào Hàng.
- Kéo Số Lượng Bán từ Sales_Data vào Cột.
- Kéo Tổng Doanh Thu từ Sales_Data vào Giá Trị.
Kết Luận
Các kỹ thuật nâng cao này cho phép bạn tùy chỉnh phân tích và tích hợp nhiều nguồn dữ liệu, làm cho Bảng Pivot của bạn thậm chí còn có thêm thông tin chi tiết. Làm chủ các trường tính toán và hợp nhất nhiều nguồn dữ liệu trong Bảng Pivot của Excel sẽ giúp bạn phân tích các tập dữ liệu phức tạp một cách hiệu quả. Với các kỹ thuật này, bạn có thể có được thông tin chi tiết nhanh hơn và quản lý dữ liệu hiệu quả hơn.
Hình ảnh bởi councilcle từ Pixabay
Nhận bài tập Excel nâng cao miễn phí kèm lời giải!