Bảng tổng hợp rất hữu ích cho việc phân tích dữ liệu và với các kỹ thuật nâng cao như Trường được tính toán và Nhiều nguồn dữ liệu, bạn có thể mở khóa nhiều thông tin chi tiết hơn nữa. Trong bài viết này, chúng tôi sẽ giải thích và trình bày các kỹ thuật Bảng tổng hợp nâng cao, tập trung vào Trường được tính toán và sử dụng Nhiều nguồn dữ liệu trong Bảng tổng hợp.
Tạo trường được tính toán trong bảng tổng hợp
Trường được tính toán tạo các phép tính tùy chỉnh trong Bảng tổng hợp dựa trên dữ liệu hiện có. Các trường được tính toán sẽ thêm một cột mới vào Bảng tổng hợp, nơi các giá trị được lấy từ các trường hiện có. Điều này hữu ích khi bạn muốn thực hiện các phép tính trên dữ liệu của mình mà không sửa đổi tập dữ liệu gốc.
Hãy tính 10% hoa hồng cho doanh số bán hàng từ tập dữ liệu bán hàng bằng cách sử dụng Trường được tính trong Bảng tổng hợp. Để sử dụng trường được tính toán, trước tiên bạn cần tạo bảng Pivot.
Bước 1:Tạo Bảng tổng hợp .
- Chọn phạm vi dữ liệu của bạn.
- Đi tới Chèn tab>> chọn Bảng tổng hợp .
- Chọn vị trí đặt Bảng tổng hợp của bạn (Bảng tính mới/Bảng tính hiện có).
Bây giờ, hãy kéo và thả các trường cần thiết vào Hàng , Cột , Giá trị và Bộ lọc phần.
Bước 2:Chèn trường được tính toán .
- Nhấp vào bất kỳ đâu trong Bảng tổng hợp của bạn.
- Đi tới Phân tích PivotTable tab>> từ Trường, Mục &Bộ>> chọn Trường được tính toán .
- Trong Chèn trường được tính toán hộp thoại:
- Trong Tên vào hộp, hãy nhập tên cho trường được tính toán của bạn: Hoa hồng bán hàng (10%)
- Trong Công thức vào hộp, chèn công thức sau:
- =0,01 * ‘Tổng doanh số bán hàng’
- Chọn Thêm >> nhấp vào OK để thêm trường vào Bảng tổng hợp của bạn.
Bây giờ là Trường tính toán mới Hoa hồng bán hàng (10%) cột sẽ được thêm vào Bảng tổng hợp.
Mẹo: Trường được tính toán hoạt động tốt nhất cho các phép tính cộng, nhưng đối với các phép tính không cộng hoặc phức tạp hơn, hãy sử dụng Power Pivot.
Tạo bảng tổng hợp từ nhiều nguồn dữ liệu
Việc kết hợp dữ liệu từ nhiều bảng (hoặc nguồn) trong một Bảng tổng hợp duy nhất 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 các bảng theo cách thủ công.
Giả sử bạn có nhiều nguồn dữ liệu, một nguồn là Dữ liệu bán hàng chứa thông tin bán hàng và nguồn khác là Dữ liệu sản phẩm chứa thông tin sản phẩm. Chúng tôi sẽ chỉ ra cách bạn có thể sử dụng nhiều nguồn dữ liệu này trong Bảng tổng hợp.
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 tổng hợp. Excel 2013 và các phiên bản mới hơn cung cấp tính năng Mô hình Dữ liệu tích hợp sẵn này.
Bước 1:Thiết lập từng bảng trong Excel .
- Chọn phạm vi ô>> đi tới phần Chèn tab>> chọn Bảng .
- Để hiểu rõ hơn, bạn có thể đặt tên cho từng 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 tổng hợp bằng Mô hình dữ liệu .
- Chọn Bảng >> đi tới Chèn tab>> chọn PivotTable .
- Trong Tạo PivotTable hộp thoại,
- Chọn vị trí đặt Bảng tổng hợp của bạn (Bảng tính mới/Bảng tính hiện có).
- Nhấp vào Thêm dữ liệu này vào Mô hình dữ liệu .
- Nhấp vào OK .
Bước 3:Tạo mối quan hệ .
- Chuyển đến Dữ liệu tab>> từ Công cụ dữ liệu>> chọn Mối quan hệ .
- Trong phần Quản lý mối quan hệ hộp thoại:
- Nhấp vào Mới , sau đó chọn Product_ID từ cả Bán hàng và Sản phẩm bảng để thiết lập mối quan hệ.
- Nhấp vào OK để cứu vãn mối quan hệ.
Bước 4:Xây dựng Bảng tổng hợp .
- Bây giờ, trong Trường PivotTable ngăn, 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 sản phẩm tới Hàng .
- Kéo Đơn vị đã bán từ Sales_Data bảng thành Cột .
- Kéo Tổng doanh số từ Sales_Data bảng thành Giá trị để xem tổng số lượng và doanh số bán hàng theo sản phẩm.
Sau khi thiết lập mối quan hệ, Bảng tổng hợp của bạn giờ đây có thể lấy các trường từ cả hai bảng, hiển thị tóm tắt doanh số, tên sản phẩm và danh mục mà không yêu cầu 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
Bổ trợ 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ừ nhiều nguồn khác nhau. Cách tiếp cận này cho phép bạn tạo Bảng tổng hợp dựa trên nhiều bảng và thực hiện phân tích trên các bảng đó.
Bước 1:Bật Power Pivot (nếu chưa được bật).
- Đi tới Tệp tab>> chọn Tùy chọn>> chọn Phần bổ trợ .
- Ở cuối cửa sổ, trong Quản lý thả xuống>> chọn Phần bổ trợ COM>> nhấp vào Đi .
- Kiểm tra Microsoft Power Pivot dành cho Excel>> nhấp vào OK .
Bước 2:Tải dữ liệu vào Power Pivot .
- Đi tới Dữ liệu tab>> chọn Nhận dữ liệu để nhập dữ liệu từ nhiều nguồn khác nhau (ví dụ:nhiều trang tính, 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, Text/CSV, v.v.), sau đó nhấp vào Tải vào .
- Từ Nhập dữ liệu hộp thoại:
- Chọn Bảng .
- Chọn Bảng tính mới .
- chọn hộp 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 phải có một mã định danh duy nhất (như ID sản phẩm hoặc ID khách hàng) để kích hoạt 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 tới Power Pivot tab>> chọn Quản lý để mở Power Pivot Editor .
- Đi tới Chế độ xem sơ đồ để xem trực quan tất cả các bảng của bạn.
- Kéo và thả các trường giữa các bảng để tạo mối quan hệ. Kết nối ID sản phẩm từ Bảng bán hàng tới ID sản phẩm trong Bảng sản phẩm ).
- Truy cập Trang chủ tab trong Power Pivot>> chọn PivotTable .
- Chọn vị trí đặt Bảng tổng hợp (ví dụ:trên trang tính mới) và nhấp vào OK .
- Trong Danh sách trường bảng tổng hợp, 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 lấy dữ liệu từ nhiều nguồn.
- Kéo Sản phẩm từ Bảng sản phẩm tới Hàng .
- Kéo Đơn vị đã bán từ Sales_Data vào Cột .
- Kéo Tổng doanh số từ Sales_Data tới Giá trị .
Kết luận
Những kỹ thuật nâng cao này cho phép bạn tùy chỉnh việc phân tích và tích hợp nhiều nguồn dữ liệu, làm cho Bảng tổng hợp của bạn trở nên sâu sắc hơn nữa. Nắm vững các trường được tính toán và hợp nhất nhiều nguồn dữ liệu trong Bảng tổng hợp 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 những kỹ thuật này, bạn có thể nhận đượ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 được cung cấp bởi hội đồng từ Pixabay