Power Query là giải pháp hiệu quả cho những vấn đề thường gặp của ngân sách.
Power Query có thể thiết lập bảng ngân sách với mức phức tạp tối thiểu. Nó cũng có thể tự động hóa quy trình và trong nhiều trường hợp, Power Query còn tối ưu trình phân tích dữ liệu của bạn làm việc với bảng ngân sách
Ví dụ:
Hãy xem một tình huống mà bạn có 4 trạng thái. Mỗi tiểu bang có một tệp riêng để biên dịch. Mỗi tệp có năm phiếu nhập dữ liệu phòng ban riêng biệt, một phiếu nhập cho mỗi phòng ban. Bạn cần kết hợp 4 tệp và trích xuất năm trang bộ phận từ mỗi tệp. Lý tưởng nhất là nên tạo cả bố cục ngân sách(số tháng trên trang) và bố cục dữ liệu của tổng ngân sách.
Ví dụ này sẽ sử dụng ngân sách lãi và lỗ
Hình 01 cho thấy danh sách của bốn tiểu bang và năm phòng ban
Bố cục của từng trang bộ phận được thể hiện trong hình 02. Các cột của tháng giữa đã bị ẩn
Cột U đảm bảo tổng giá trị trong cột T nằm trong dung sai của tổng giá trị đầu vào trong cột D. Cột V kiểm tra bất kỳ lỗi Excel nào. Mỗi tệp có một sheet riêng cho từng bộ phận, cộng với Control sheet. Quá trình này là gửi hồ sơ của tỉnh cho mỗi người quản lý nhà nước, người chịu trách nhiệm hoàn thành năm trang bộ phận tạo nên P&L. Các tệp cập nhật được trả lại và mỗi tệp trạng thái sẽ được lưu vào cùng một thư mục. Điều này cho phép bạn có một thư mục riêng cho từng loại của ngân sách.
Note: Ảnh chụp màn hình và một số tùy chọn và tên nút có thể khác nhau giữa các phiên bản Microsoft. Power Query đang được nâng cấp thường xuyên. Màn hình và các tùy chọn có thể thay đổi theo thời gian. Power Query cũng là một phần của Power BI(ứng dụng báo cáo và bảng điều khiển của Microsoft). Bất kỳ kĩ năng nào bạn học được trong Power Query trong Excel đều có thể được áp dụng trực tiếp cho Power BI.
Những điểm cần lưu ý:
- Khi nhập tệp, tất cả các tệp phải được đóng lại
- Power Query không ảnh hưởng đến các tệp nguồn theo bất kỳ cách nào
Các bước
1. Trong một tệp trống, hãy nhấp vào Data – Get Data – From File – From Folder
2. Nhấn vào nút Browse. Màn hình sẽ điều hướng đến thư mục BUDGET(ngân sách), chọn nó và nhấn Import
3. Các tệp trong thư mục sẽ được hiển thị. Nhấp vào menu thả xuống Combine (nằm ở cuối hộp thoại) và chọn Combine & Transform Data
4.Nhấp vào tùy chọn Parameter ở bên trái của hộp thoại và nhấp vào OK
5.Cửa sổ Power Query sẽ mở ra và tất cả các tên trang tính sẽ được liệt kê. Bạn không cần nhập Control sheet. Nhấp vào trình đơn filter trong cột Name và sử dụng tùy chọn Text Filter để lọc ra sheet điều khiển như trong hình 03. Bấm OK
6. Bây giờ chỉ có các trang của bộ phận sẽ được liệt kê. Có một biểu tượng mũi tên kép đặc biệt ở bên phỉa của tiêu đề cột Data. Nhấp vào biểu tượng này và sau đó nhấp vào OK
7. Bạn không cần hai cột đầu tiên hoặc ba cột cuối cùng. Bạn có thể chọn và xóa chúng bằng cách sử dụng biểu tượng Remove Columns trên Home hoặc bấm chuột phải vào tiêu đề cột và chọn Remove
8. Ba hàng trên cùng là không bắt buộc. Trên Home, nhấp vào biểu tượng Remove Rows và chọn Remove Top Rows rồi sau đó nhập 3 và nhấp OK
9. Hàng đầu tiên bây giờ chứa các tiêu đề cho các cột. Bấm vào biểu tượng Use First Row As Headers trên Home
10. Xóa 3 cột Allocation, Months Between, và Number of Allocations. Bạn có thể chọn cột đầu tiên, giữ phím Crtl và sử dụng chuột để chọn các cột khác đã sẵn sàng để xóa
11. Để loại bỏ các hàng không có giá trị, hãy sử dụng trình đơn filter trên cột Account, bỏ chọn Null và nhấp OK. Tùy chọn Null luôn được hiển thị ở đầu danh sách. Trong Power Query, null có nghĩa là trống
12. Để loại bỏ các hàng thừa còn lại, hãy sử dụng trình đơn filter trên cột Status, bỏ chọn null và State sau đó nhấn OK. Thao tác này sẽ xóa các hàng trống và các hàng tiêu đề khác
13. Dữ liệu đã sẵn sàng để nhập. Nhấn vào biểu tượng Close và Load(trên cùng bên trái màn hình)
14. Ngân sách đã được nhập theo bố cục ngân sách với các tháng trên trang
15. Bạn cũng có thể tạo danh sách dữ liệu tiêu chuẩn về ngân sách. Hình 04 cho thấy cấu trúc đầu ra cuối cùng. Trong Queries Task Pane ở bên phải màn hình, nhấp chuột phải vào BUDGET query và chọn Reference. Điều này tạo ra một query mới sử dụng đầu ra của BUDGET query làm đầu vào của nó(Nguồn)
16. Đối với danh sách dữ liệu này, bạn có thể xóa các cột Total, Total_1, Validation và Error Validation. Lưu ý: dữ liệu đã nhập có hai cột Total. Power Query không cho phép các tiêu đề cột trùng lặp, vì vậy nó nối các số vào các tiêu đề cột trùng lặp.
17. Chọn cột State, sau đó giữ phím Crtl và chọn cột Department và Account. Bấm chuột phải vào tiêu đề của một trong ba cột đã chọn và chọn Unpivot Other Columns. Thao tác này sẽ giữ lại các cột đã chọn và tạo hai cột cho các mục nhập khác như trong Hình 04
18. Đổi tên cột Attribute thành Date. Nhấn chuột phải vào cột Date và chọn Change Type and Date. Thay đổi loại cột Value thành Currency
19. Ngân sách hiện đã được chuyển đổi thành bố cục dữ liệu tiêu chuẩn – Xem hình 04
20. Nhấp vào Close & Load để kết thúc
Để xử lý phiên bản ngân sách mới, bạn có thể tạo một thư mục mới, BUDGET_2 và lưu các tệp đã sửa đổi vào thư mục đó. Thay đổi duy nhất mà bạn cần thực hiện là trong BUDGET query ban đầu
Chỉnh sửa BUDGET query. Chọn bước Source ở bên phải của màn hình. Thanh Formula sẽ hiển thị đường dẫn của thư mục như trong hình 05. Nếu thanh Formula không hiển thị, hãy nhấp vào tab View và đánh dấu tùy chọn
Chỉ cần thay đổi đường dẫn thư mục trong thanh Formula và nhấn enter. Nhấp vào Close & Load để tạo một phiên bản ngân sách hoàn toàn mới. Nếu mắc lỗi, bạn có thể đóng Query (nhấp vào dấu X ở trên cùng bên phải), chọn Discard và bắt đầu lại
Những điều cần lưu ý
- Nếu bạn muốn cập nhật một tệp trạng thái duy nhất, hãy ghi đè tệp hiện có, làm mới truy vấn đầu tiên và sau đó truy vấn thứ hai
- Nếu bạn có một trạng thái mới, chẳng hạn như TAS, chỉ cần lưu tệp TAS vào thư mục và làm mới các truy vấn theo trình tự
- Các hướng dẫn này giả định rằng không có tệp nào khác nằm trong thư mục BUDGET. Nếu có thể thêm các tệp, bạn sẽ cần sử dụng một bộ lọc bổ sung ở bước năm để loại trừ các tệp khác đó
- Điều này giả định chỉ có một Control sheet cộng với các Department sheets có trong mỗi tệp. Nếu có các trang tính khác, chúng sẽ cần được loại trừ trong bước năm.
Nguồn: intheblack
Xem thêm
Power Query tips: Hàm làm tròn bằng ngôn ngữ M
HOT: Power Query Editor đã có cho Mac
Khóa học: Tổ chức và quản lý dữ liệu báo cáo trong Excel