Power Query của Excel là một trong những công cụ mạnh mẽ nhất để kết nối, chuyển đổi dữ liệu và cập nhật theo thời gian thực. Power Query có thể nhập dữ liệu từ nhiều nguồn khác nhau và làm mới dữ liệu đó theo thời gian thực bất cứ khi nào có thay đổi trong dữ liệu nguồn. Trong bài viết này, chúng tôi sẽ hướng dẫn cách xây dựng bảng thông tin dữ liệu theo thời gian thực bằng Power Query.
Hãy tạo bảng thông tin bán hàng theo thời gian thực bằng cách thực hiện các thao tác bổ sung trong Power Query trên tập dữ liệu mở rộng. Các tiêu đề trong tập dữ liệu bán hàng bao gồm Ngày đặt hàng, Khu vực, Sản phẩm, Nhân viên bán hàng, Số lượng đã bán, Doanh thu ($) và Lợi nhuận ($).
Bước 1:Kết nối dữ liệu với Power Query
Để lấy dữ liệu từ các nguồn bên ngoài:
- Đi tới Dữ liệu tab>> chọn Nhận dữ liệu>> Từ tệp>> Từ sổ làm việc .
- Chọn tệp tập dữ liệu của bạn (ví dụ:SalesData.xlsx) và nhấp vào Nhập .
- Trong Bộ điều hướng, chọn trang tính có dữ liệu bán hàng và nhấp vào Tải .
Để kết nối dữ liệu từ sổ làm việc hiện có:
- Đi tới Dữ liệu tab>> chọn Từ Bảng/Phạm vi .
Power Query sẽ tải dữ liệu vào Trình soạn thảo Power Query .
Bước 2:Chuyển đổi dữ liệu bằng các thao tác nâng cao
Bây giờ dữ liệu đã được tải vào Power Query Editor, hãy thực hiện các phép tính và chuyển đổi dữ liệu.
1. Thay đổi kiểu dữ liệu cột
Power Query đôi khi thay đổi định dạng dữ liệu thành định dạng mặc định. Dựa trên loại dữ liệu của bạn, hãy thay đổi toàn bộ cột.
- Ngày đặt hàng :Đảm bảo rằng nó ở trong Ngày định dạng.
- Nếu không, hãy chọn cột Ngày.
- Đi tới Biến đổi tab>> từ DataType>> chỉ chọn Ngày .
- Số lượng đã bán: Kiểm tra xem nó có ở dạng Số nguyên không định dạng.
- Nếu không, hãy chọn cột Số lượng đã bán.
- Đi tới Biến đổi tab>> từ DataType>> chỉ chọn Số nguyên .
- Doanh thu ($) và Lợi nhuận ($) :Đảm bảo các cột này bằng Tiền tệ định dạng.
- Nếu không, hãy chọn cột Doanh thu ($) và Lợi nhuận ($).
- Đi tới Biến đổi tab>> từ DataType>> chỉ chọn Tiền tệ .
2. Thêm cột “Tháng”
- Chọn cột Ngày đặt hàng.
- Đi tới Thêm cột tab>> nhấp vào Ngày>> chọn Tháng>> chọn Tên tháng .
- Điều này sẽ tạo một cột trích xuất tên của tháng từ Ngày đặt hàng (ví dụ:tháng 1, tháng 2).
3. Tính doanh thu trên mỗi đơn vị
Hãy tính doanh thu được tạo ra cho mỗi đơn vị được bán.
- Đi tới Thêm cột tab>> chọn Cột tùy chỉnh .
- Trong Cột tùy chỉnh hộp thoại;
- Đặt tên cột Doanh thu trên mỗi đơn vị và sử dụng công thức sau trong hộp công thức:
[#"Revenue ($)"]/[Units Sold]
- Nhấp vào OK .
Cột tùy chỉnh mới này sẽ hiển thị doanh thu được tạo ra từ mỗi đơn vị được bán.
4. Tính tỷ suất lợi nhuận
Tiếp theo, hãy tính Tỷ suất lợi nhuận. Tỷ suất lợi nhuận được tính như sau:
- Đi tới Thêm cột tab>> chọn Cột tùy chỉnh .
- Trong Cột tùy chỉnh hộp thoại;
- Đặt tên cột Tỷ suất lợi nhuận (%) và sử dụng công thức sau trong hộp công thức:
([#"Profit ($)"]/[#"Revenue ($)"])*100)
- Nhấp vào OK .
Cột tùy chỉnh này sẽ hiển thị tỷ suất lợi nhuận cho mỗi lần bán.
5. Nhóm dữ liệu theo khu vực và sản phẩm để có thông tin chi tiết tổng hợp
Để xem tổng Doanh thu , Số lượng đã bán và Lợi nhuận theo Vùng và Sản phẩm , chúng tôi sẽ nhóm dữ liệu.
- Truy cập Trang chủ tab>> chọn Nhóm theo .
- Trong Nhóm theo hộp thoại, đặt như sau:
- Chọn Nâng cao để thêm nhiều tập hợp.
- Nhóm theo :Khu vực, Sản phẩm.
- Chèn hợp lệ Tên cột mới .
- Hoạt động :Tổng
- Cột:
- Số lượng đã bán
- Doanh thu ($)
- Lợi nhuận ($)
- Nhấp vào OK .
Nhóm Nhóm theo Tính năng này sẽ tóm tắt dữ liệu theo Khu vực và Sản phẩm để cung cấp cái nhìn tổng quan về hiệu suất bán hàng.
6. Sắp xếp dữ liệu
Sắp xếp dữ liệu theo Doanh thu ($) theo thứ tự giảm dần để xem các sản phẩm và khu vực hoạt động hiệu quả nhất.
- Nhấp vào menu thả xuống trên Doanh thu ($) tiêu đề cột.
- Chọn Sắp xếp giảm dần >> nhấp vào OK .
Thao tác này sẽ sắp xếp dữ liệu của bạn để hiển thị dữ liệu từ cao nhất đến thấp nhất dựa trên Doanh thu.
Bước 3:Tải dữ liệu đã chuyển đổi vào Excel
Để tải dữ liệu đã được làm sạch và chuyển đổi vào bảng tính Excel.
- Truy cập Trang chủ tab>> từ Đóng và tải>> chọn Đóng và tải .
Bước 4:Tạo Trang tổng quan
Khi dữ liệu của bạn được chuyển đổi và tải vào Excel, bạn có thể tạo trang tổng quan có hình ảnh trực quan.
1. Tạo Bảng tổng hợp
- Chọn bảng dữ liệu đã chuyển đổi.
- Đi tới Chèn tab>> chọn PivotTable .
- Trong Trường PivotTable Danh sách:
- Hàng :Khu vực, Sản phẩm.
- Giá trị :Tổng số đơn vị đã bán, Tổng doanh thu, Tổng lợi nhuận.
2. Tạo biểu đồ Pivot
Biểu đồ thanh về doanh thu theo khu vực :
- Đánh dấu Bảng tổng hợp bằng Khu vực và Doanh thu.
- Đi tới Phân tích PivotTable tab>> chọn Biểu đồ thanh>> chọn Thanh cụm .
Biểu đồ hình tròn về phân bổ doanh số theo sản phẩm :
- Chọn Bảng tổng hợp có Sản phẩm và Doanh thu.
- Đi tới Phân tích PivotTable tab>> chọn Biểu đồ hình tròn .
Hãy cùng khám phá tính tương tác của bảng điều khiển. Chọn khu vực phía Đông từ biểu đồ hình tròn.
Bạn có thể thêm Bộ cắt cho Nhân viên bán hàng và Dòng thời gian cho Ngày đặt hàng để tương tác.
Bước 5:Thiết lập làm mới theo thời gian thực
Để cập nhật dữ liệu thời gian thực trong trang tổng quan:
- Đi tới Dữ liệu tab>> nhấp vào Làm mới tất cả bất cứ khi nào bạn thêm dữ liệu mới vào dữ liệu nguồn của mình.
Hoặc bạn có thể đặt sổ làm việc tự động làm mới sau mỗi vài phút:
- Đi tới Dữ liệu tab>> chọn Truy vấn và kết nối>> nhấp chuột phải vào Truy vấn và kết nối>> chọn Thuộc tính .
- Trong Thuộc tính truy vấn hộp thoại;
- Bật Làm mới sau mỗi 5 phút .
- Nhấp vào OK .
Kết luận
Bằng cách làm theo các bước này, bạn có thể xây dựng bảng thông tin dữ liệu bán hàng động, theo thời gian thực bằng Power Query. Bạn có thể thực hiện các thao tác nâng cao trong trình soạn thảo Power Query, để nhóm, tính toán các cột tùy chỉnh, sắp xếp, lọc, v.v., sau đó tải dữ liệu đã chuyển đổi vào Excel để xây dựng bảng thông tin thời gian thực. Tính năng chuyển đổi và kết nối dữ liệu của Power Query sẽ nhanh chóng cập nhật trang tổng quan của bạn với dữ liệu mới theo thời gian thực.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!