Power Query của Excel là một trong những công cụ mạnh mẽ nhất để kết nối dữ liệu, biến đổi 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 và làm mới nó 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 ta sẽ chỉ cách xây dựng bảng điều khiển dữ liệu thời gian thực với Power Query.
Hãy tạo một bảng điều khiển bán hàng 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ừ nguồn bên ngoài:
- Vào tab Dữ liệu >> chọn Lấy 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 Nhập.
- Trong Navigator, chọn sheet chứa dữ liệu bán hàng và nhấp Tải.
Để kết nối dữ liệu từ sổ làm việc hiện có:
- Vào tab Dữ liệu >> chọn Từ Bảng/Phạm vi.
Power Query sẽ tải dữ liệu vào Trình chỉnh sửa Power Query.
Bước 2: Biến đổi Dữ liệu với Các Thao tác Nâng cao
Bây giờ dữ liệu đã được tải vào Trình chỉnh sửa Power Query, hãy thực hiện các biến đổi và tính toán 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 sang định dạng mặc định của nó. 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 nó ở định dạng Ngày.
- Nếu không, chọn cột Ngày.
- Vào tab Biến đổi >> từ Kiểu Dữ liệu >> chọn chỉ Ngày.
- Số lượng bán: Kiểm tra xem nó có ở định dạng Số nguyên không.
- Nếu không, chọn cột Số lượng bán.
- Vào tab Biến đổi >> từ Kiểu Dữ liệu >> chọn chỉ Số nguyên.
- Doanh thu ($) và Lợi nhuận ($): Đảm bảo các cột này ở định dạng Tiền tệ.
- Nếu không, chọn cột Doanh thu ($) và Lợi nhuận ($).
- Vào tab Biến đổi >> từ Kiểu Dữ liệu >> chọn chỉ Tiền tệ.
2. Thêm Cột "Tháng"
- Chọn cột Ngày đặt hàng.
- Vào tab Thêm Cột >> nhấp Ngày >> chọn Tháng >> chọn Tên của Tháng.
- Điều này sẽ tạo một cột trích xuất tên tháng từ Ngày đặt hàng (ví dụ: Tháng Một, Tháng Hai).
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ị bán.
- Vào tab Thêm Cột >> chọn Cột Tùy chỉnh.
- Trong hộp thoại Cột Tùy chỉnh:
- Đặ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 OK.
Cột tùy chỉnh mới này sẽ cho thấy doanh thu được tạo ra cho mỗi đơn vị bán.
4. Tính Biên Lợi nhuận
Tiếp theo, hãy tính Biên Lợi nhuận. Biên lợi nhuận được tính như sau:
- Vào tab Thêm Cột >> chọn Cột Tùy chỉnh.
- Trong hộp thoại Cột Tùy chỉnh:
- Đặt tên cột Biên 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 OK.
Cột tùy chỉnh này sẽ hiển thị biên 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 Tổng hợp
Để xem tổng Doanh thu, Số lượng bán và Lợi nhuận theo Khu vực và Sản phẩm, chúng ta sẽ nhóm dữ liệu.
- Vào tab Trang chủ >> chọn Nhóm theo.
- Trong hộp thoại Nhóm theo, thiết lập như sau:
- Chọn Nâng cao để thêm nhiều tổng hợp.
- Nhóm theo: Khu vực, Sản phẩm.
- Chèn Tên cột mới hợp lệ.
- Thao tác: Tổng
- Cột:
- Số lượng bán
- Doanh thu ($)
- Lợi nhuận ($)
- Nhấp OK.
Tính năng Nhóm theo 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 sản phẩm và khu vực hiệu suất cao nhất.
- Nhấp vào menu thả xuống trên tiêu đề cột Doanh thu ($).
- Chọn Sắp xếp Giảm dần >> nhấp OK.
Điều này sẽ sắp xếp dữ liệu của bạn để hiển thị từ cao nhất đến thấp nhất dựa trên Doanh thu.
Bước 3: Tải Dữ liệu đã Biến đổi vào Excel
Để tải dữ liệu đã được làm sạch và biến đổi vào sheet Excel.
- Vào tab Trang chủ >> từ Đóng & Tải >> chọn Đóng & Tải.
Bước 4: Tạo Bảng điều khiển
Khi dữ liệu của bạn đã được biến đổi và tải vào Excel, bạn có thể tạo bảng điều khiển với các hình ảnh hóa.
1. Tạo Bảng Pivot
- Chọn bảng dữ liệu đã biến đổi.
- Vào tab Chèn >> chọn Bảng Pivot.
- Trong Danh sách Trường Bảng Pivot:
- Hàng: Khu vực, Sản phẩm.
- Giá trị: Tổng Số lượng bán, Tổng Doanh thu, Tổng Lợi nhuận.
2. Tạo Biểu đồ Pivot
Biểu đồ cột cho Doanh thu theo Khu vực:
- Làm nổi bật Bảng Pivot với Khu vực và Doanh thu.
- Vào tab Phân tích Bảng Pivot >> chọn Biểu đồ cột >> chọn Cột nhóm.
Biểu đồ tròn cho Phân bố Bán hàng theo Sản phẩm:
- Chọn Bảng Pivot với Sản phẩm và Doanh thu.
- Vào tab Phân tích Bảng Pivot >> chọn Biểu đồ tròn.
Hãy khám phá tính tương tác của bảng điều khiển. Chọn khu vực Đông từ biểu đồ tròn.
Bạn có thể thêm Slicer cho Nhân viên bán hàng và Timeline cho Ngày đặt hàng để tăng tính tương tác.
Bước 5: Thiết lập Làm mới Thực tế
Để cập nhật dữ liệu thực tế trong bảng điều khiển:
- Vào tab Dữ liệu >> nhấp 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 mỗi vài phút:
- Vào tab Dữ liệu >> 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 hộp thoại Thuộc tính Truy vấn:
- Bật Làm mới mỗi 5 phút.
- Nhấp 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 một bảng điều khiển dữ liệu bán hàng động, thời gian thực sử dụng Power Query. Bạn có thể thực hiện các thao tác nâng cao trong Trình chỉnh sửa Power Query, để nhóm, tính cột tùy chỉnh, sắp xếp, lọc, v.v., sau đó tải dữ liệu đã biến đổi vào Excel để xây dựng bảng điều khiển thời gian thực. Tính năng kết nối dữ liệu và biến đổi của Power Query sẽ nhanh chóng cập nhật bảng điều khiển của bạn với dữ liệu mới theo thời gian thực.
Nhận bài tập Excel nâng cao miễn phí kèm lời giải!