Giả sử bạn đang chạy một số dự án với các ngân sách khác nhau và muốn hình dung chi phí của mình cho từng dự án đó. Đó là, từ bảng nguồn này:
.. nhận được một cái gì đó như thế này:
Nói cách khác, bạn cần dàn trải ngân sách qua các ngày của mỗi dự án và có được phiên bản đơn giản hóa của biểu đồ Gantt của dự án. Làm điều này với đôi tay của bạn là lâu và nhàm chán, macro rất khó, nhưng Power Query cho Excel trong tình huống như vậy cho thấy sức mạnh của nó trong tất cả các vinh quang của nó.
Truy vấn nguồn là một tiện ích bổ sung của Microsoft có thể nhập dữ liệu vào Excel từ hầu hết mọi nguồn và sau đó biến đổi nó theo nhiều cách khác nhau. Trong Excel 2016, phần bổ trợ này đã được tích hợp sẵn theo mặc định và đối với Excel 2010-2013, phần bổ trợ này có thể được tải xuống từ trang web của Microsoft và sau đó được cài đặt trên PC của bạn.
Đầu tiên, hãy biến bảng gốc của chúng ta thành một bảng “thông minh” bằng cách chọn lệnh Định dạng dưới dạng bảng chuyển hướng Trang Chủ (Trang chủ - Định dạng dưới dạng Bảng) hoặc bằng cách nhấn phím tắt Ctrl+T :
Sau đó chuyển đến tab Ngày (nếu bạn có Excel 2016) hoặc trên tab Truy vấn nguồn (nếu bạn có Excel 2010-2013 và bạn đã cài đặt Power Query dưới dạng bổ trợ riêng) và nhấp vào nút Từ Bảng / Phạm vi. :
Bảng thông minh của chúng tôi được tải vào trình chỉnh sửa truy vấn Power Query, trong đó bước đầu tiên là thiết lập các định dạng số cho mỗi cột bằng cách sử dụng trình đơn thả xuống trong tiêu đề bảng:
Để tính toán ngân sách mỗi ngày, bạn cần tính thời lượng của từng dự án. Để thực hiện việc này, hãy chọn (giữ phím Ctrl) cột đầu tiên Kết thúc, và sau đó Bắt đầu và chọn một đội Thêm cột - Ngày - Trừ ngày (Thêm cột - Ngày - Trừ ngày):
Các con số kết quả ít hơn 1 so với mức cần thiết, bởi vì chúng tôi phải bắt đầu mỗi dự án vào ngày đầu tiên vào buổi sáng và kết thúc vào ngày cuối cùng vào buổi tối. Do đó, hãy chọn cột kết quả và thêm một đơn vị vào nó bằng lệnh Chuyển đổi - Tiêu chuẩn - Thêm (Chuyển đổi - Tiêu chuẩn - Thêm):
Bây giờ, hãy thêm một cột để chúng tôi tính toán ngân sách mỗi ngày. Để làm điều này, trên tab Thêm cột Tôi không chơi Cột tùy chỉnh (Cột tùy chỉnh) và trong cửa sổ xuất hiện, hãy nhập tên của trường mới và công thức tính toán, sử dụng tên của các cột từ danh sách:
Bây giờ là thời điểm tinh tế nhất - chúng tôi tạo một cột được tính toán khác với danh sách các ngày từ đầu đến cuối với bước 1 ngày. Để thực hiện việc này, hãy nhấn lại nút Cột tùy chỉnh (Cột tùy chỉnh) và sử dụng ngôn ngữ Power Query tích hợp M, được gọi là Danh sách. Ngày:
Hàm này có ba đối số:
- ngày bắt đầu - trong trường hợp của chúng tôi, nó được lấy từ cột Bắt đầu
- số ngày sẽ được tạo - trong trường hợp của chúng tôi, đây là số ngày cho mỗi dự án, mà chúng tôi đã tính trước đó trong cột Trừ
- bước thời gian - do thiết kế đặt #duration (1,0,0,0), nghĩa là trong ngôn ngữ của M - một ngày, không giờ, không phút, không giây.
Sau khi nhấp vào OK chúng tôi nhận được một danh sách (Danh sách) ngày tháng, có thể được mở rộng thành các dòng mới bằng cách sử dụng nút trong tiêu đề bảng:
… Và chúng tôi nhận được:
Bây giờ tất cả những gì còn lại là thu gọn bảng, sử dụng các ngày đã tạo làm tên cho các cột mới. Nhóm chịu trách nhiệm về điều này. Cột chi tiết (Cột xoay) chuyển hướng Chuyển đổi (Biến đổi):
Sau khi nhấp vào OK chúng tôi nhận được một kết quả rất gần với kết quả mong muốn:
Null, trong trường hợp này, là một giá trị tương tự của một ô trống trong Excel.
Nó vẫn là loại bỏ các cột không cần thiết và dỡ bỏ bảng kết quả bên cạnh dữ liệu ban đầu bằng lệnh Đóng và tải - Đóng và tải vào… (Đóng & Tải - Đóng & Tải vào…) chuyển hướng Trang Chủ (Trang Chủ):
Kết quả là chúng tôi nhận được:
Để đẹp hơn, bạn có thể tùy chỉnh giao diện của các bảng thông minh kết quả trên tab xây dựng (Thiết kế): đặt một kiểu màu duy nhất, tắt các nút bộ lọc, bật tính tổng, v.v. Ngoài ra, bạn có thể chọn bảng có ngày tháng và bật đánh dấu số cho bảng đó bằng cách sử dụng định dạng có điều kiện trên tab Trang chủ - Định dạng có điều kiện - Thang màu (Trang chủ - Định dạng có điều kiện - Thang màu):
Và phần tốt nhất là trong tương lai, bạn có thể chỉnh sửa những cái cũ một cách an toàn hoặc thêm dự án mới vào bảng gốc, sau đó cập nhật bảng phù hợp với ngày tháng bằng nút chuột phải - và Power Query sẽ tự động lặp lại tất cả các hành động mà chúng tôi đã thực hiện .
Võngà!
- Biểu đồ Gantt trong Excel sử dụng định dạng có điều kiện
- Lịch mốc dự án
- Tạo các hàng trùng lặp với Power Query