Originally posted on 27/03/2022 @ 16:07
Trong bài viết và loạt video này, hãy tìm hiểu cách tạo Bảng điều khiển dự án tương tác hoàn toàn với Excel, như minh họa ở bên phải.
Bạn sẽ học:
- Biểu đồ Gantt để lập kế hoạch dự án
- Biểu đồ tiến trình để hiển thị trạng thái
- Chuẩn bị trang tổng quan cuối cùng
Phần 1 – Biểu đồ Gantt của Dự án
Biểu đồ Gantt là một cách cổ điển để hình dung kế hoạch và tình trạng hiện tại của dự án. Đó là lý do tại sao nó trở thành hòn đá tảng của bất kỳ Bảng điều khiển quản lý dự án nào.
Trong Phần 1 của hướng dẫn này, hãy tạo biểu đồ gantt đa cấp, tương tác bằng Excel. Đây là bản demo về những gì chúng tôi sẽ tạo.
Tài nguyên cho Biểu đồ Gantt
Tệp Excel để thực hành
Sử dụng các tệp dưới đây để thực hành các khái niệm.
- Tệp dữ liệu trống
- Tệp Biểu đồ Gantt đã hoàn thành
Biểu đồ Gantt – Video Hướng dẫn
Tôi đã làm một video vui nhộn và chi tiết về cách tạo biểu đồ Gantt này bằng Excel. Xem nó bên dưới hoặc trên kênh YouTube của tôi .
Lấy dữ liệu
Bất kỳ dự án nào cũng là sự kết hợp của con người và nhiệm vụ. Vì vậy, để tạo biểu đồ gantt kế hoạch dự án, bạn cần cả người và dữ liệu hoạt động dự án. Đây là dữ liệu cho Dự án Mega Something của chúng tôi.
Tải xuống tệp dữ liệu trống .
Dữ liệu kế hoạch dự án – Xem trước
Bảng hoạt động
Bàn người
Đặt mối quan hệ giữa các bảng
Sau khi chúng tôi có dữ liệu sẵn sàng, hãy kết nối mọi người và bảng hoạt động. Bạn có thể tạo mối quan hệ từ ruy-băng Dữ liệu> Mối quan hệ.
Chúng tôi muốn kết nối cột Chủ sở hữu hoạt động với cột Con người.
Không cần sử dụng X hoặc VLOOKUP nữa, bạn có thể kết nối các bảng trên một cột với Excel.
Tìm hiểu thêm về Mối quan hệ Bảng trong Excel .
Bảng tính tính toán
Vì các bảng của chúng tôi có liên quan với nhau, nên giờ đây chúng tôi có thể tính toán tất cả các con số cần thiết cần thiết cho biểu đồ gantt của chúng tôi.
Trong một trang tính mới,
- Chèn bảng tổng hợp (Excel sẽ tạo bảng tổng hợp từ mô hình dữ liệu mối quan hệ bảng của bạn)
- Thêm Danh mục và hoạt động vào khu vực nhãn hàng
- Thêm ngày bắt đầu và ngày kết thúc vào vùng giá trị
- Đặt ngày bắt đầu “tóm tắt các giá trị theo” thành Min
- Đối với ngày kết thúc, hãy đặt nó thành Max
- Thiết lập bảng tổng hợp ở định dạng phác thảo và thêm tổng phụ ở trên cùng.
- Loại bỏ bất kỳ tổng số lớn nào.
Bảng tổng hợp cuối cùng của bạn sẽ giống như bên dưới.
Chỉ định ngày bắt đầu Gantt
Trong một ô trống trong trang tính mới này, hãy xác định ngày bắt đầu cho trực quan biểu đồ gantt của chúng tôi. Bạn có thể sử dụng công thức như = TODAY () – 14 hoặc công thức khác cho việc này.
Đặt tên cho ô ngày bắt đầu này là start.date
Bảng công việc biểu đồ Gantt
Thêm một trang tính mới và đặt tên là Biểu đồ Gantt.
Trong trang tính này, hãy thiết lập lưới biểu đồ gantt của bạn. Chúng tôi sẽ sử dụng 67 cột theo kiểu này.
- Tên danh mục
- Hoạt động
- Người được giao nhiệm vụ
- Bắt đầu
- Chấm dứt
- % xong
- % xong
- 8 đến 67 – Ngày (cột hẹp)
Tải lên các giá trị bằng cách liên kết các giá trị của Pivot Table với một tham chiếu hoặc sử dụng các hàm tra cứu.
Bạn có thể sử dụng hàm XLOOKUP để lấy giá trị người &% hoàn thành.
Tất cả về chức năng XLOOKUP .
Công thức biểu đồ Gantt
Chúng tôi cần thiết lập một công thức trong lưới biểu đồ gantt của mình để hiển thị ĐÚNG khi một ngày ở hàng trên cùng nằm giữa ngày bắt đầu và ngày kết thúc .
Bạn có thể sử dụng công thức AND cho việc này. Điền vào công thức này cho toàn bộ phạm vi.
Trong bảng tính của mình, tôi có 105 hàng x 60 cột.
Quy tắc định dạng có điều kiện
Biểu đồ gantt của chúng tôi gần như đã sẵn sàng. Chúng ta cần thêm hai quy tắc định dạng có điều kiện để làm nổi bật ngày dự án và ngày hiện tại.
- Quy tắc số 1: Đánh dấu tất cả các giá trị TRUE trong lưới gantt
- Quy tắc số 2: Đánh dấu cột tương ứng với TODAY ()
Kiểm tra các quy tắc từ ảnh chụp màn hình bên dưới.
Cuối cùng chọn phạm vi công thức AND và áp dụng định dạng số tùy chỉnh của ;;;
Bạn có thể sử dụng giá trị ẩn trong một phạm vi ô bằng cách áp dụng mã định dạng tùy chỉnh ;;;
Cách ẩn giá trị với định dạng số .
Ở giai đoạn này, biểu đồ gantt của bạn sẽ giống như sau:
Để thêm thanh dữ liệu% đã hoàn thành:
- Chọn cột% đã hoàn thành và thêm các thanh dữ liệu.
- Đặt quy tắc thanh để chỉ hiển thị thanh
- Trong cột thứ 7 (có giá trị% done được nhân đôi), hãy áp dụng biểu tượng ô ✔khi% hoàn thành là 100% và không có biểu tượng ô nào cho phần còn lại.
- Ẩn nội dung của cột này bằng mã tùy chỉnh ;;;
Tìm hiểu thêm một số thủ thuật về định dạng có điều kiện tại đây .
Tính năng cắt lựa chọn nhóm
Quay lại trang tính toán và thêm bộ cắt nhóm trên bảng tổng hợp của bạn.
Cắt và di chuyển máy cắt này đến trang biểu đồ gantt.
Biểu đồ gantt của bạn hiện đã tương tác !!!
Máy cắt Excel cung cấp rất nhiều khả năng tương tác trong các báo cáo của bạn. Nếu bạn chưa sử dụng chúng trước đây, tôi khuyên bạn nên tìm hiểu một chút về chúng. Sử dụng hướng dẫn hữu ích này.
Máy cắt Excel – hướng dẫn đầy đủ .
Biểu đồ gantt cuối cùng …
Đây là biểu đồ gantt cuối cùng ở giai đoạn này.
Tệp Excel để tải xuống
Sử dụng các tệp dưới đây để thực hành các khái niệm.
- Tệp dữ liệu trống
- Tệp Biểu đồ Gantt đã hoàn thành
Thêm Biểu đồ Gantt cho bạn …
- Biểu đồ Gantt với tính năng Drill-down
- Biểu đồ hộp Gantt – để hiển thị sự không chắc chắn trong dự án
- Biểu đồ Gantt + Các mẫu PM hữu ích khác của Chandoo
Phần 2 – Biểu đồ tiến độ đẹp
Trong Phần 2 của hướng dẫn này, chúng tôi sẽ xây dựng các biểu đồ tiến độ dự án đẹp mắt bằng Excel.
Có 3 cách phổ biến để hình dung tiến độ dự án.
- Đèn giao thông?
- Nhiệt kế?
- Đồng hồ đo?
Trong phần này, tôi sẽ giải thích cách tạo biểu đồ đèn giao thông bằng Excel, như được minh họa ở bên phải. Trong video hướng dẫn, bạn có thể học cách tạo cả 3 biểu đồ.
Tài nguyên cho Biểu đồ Tiến độ Dự án
Tệp Excel để thực hành
Sử dụng các tệp dưới đây để thực hành các khái niệm.
- Tệp dữ liệu trống
- Tệp biểu đồ tiến độ đã hoàn thành
Video Hướng dẫn – Biểu đồ Tiến độ Dự án
Tôi đã làm một video giải thích cách lập cả ba biểu đồ tiến độ của dự án. Xem nó bên dưới hoặc trên kênh YouTube của tôi.
Đèn giao thông dự án – Hướng dẫn
Đây sẽ là phần ngắn nhất của hướng dẫn. Hãy tưởng tượng bạn có giá trị tiến độ dự án trong ô L3.
Sử dụng Định dạng có điều kiện trên ô để tô màu Đỏ nếu giá trị dưới 50%, Màu hổ phách nếu giá trị dưới 80% và màu XANH LÁ khác.
Xem hình minh họa bên dưới để hiểu thiết lập quy tắc.
Chuyển ô này thành Đèn giao thông?
Bây giờ L3 có thể thay đổi màu sắc, hãy biến nó thành một đèn giao thông đẹp.
Đầu tiên, làm cho giá trị% trong L3 biến mất với mã định dạng ô tùy chỉnh là ;;;
Bạn có thể sử dụng giá trị ẩn trong một phạm vi ô bằng cách áp dụng mã định dạng tùy chỉnh ;;;
Cách ẩn giá trị với định dạng số .
Tiếp theo, sao chép L3 và dán nó dưới dạng Liên kết Ảnh . Thao tác này sẽ cung cấp cho bạn ảnh chụp nhanh trực tiếp của ô đèn giao thông.
Như bây giờ chúng ta có một hình ảnh, chúng ta có thể áp dụng các tùy chọn định dạng hình ảnh để biến hình chữ nhật này thành một đèn giao thông hình tròn.
- Làm cho hình ảnh lớn hơn
- Từ ruy-băng Định dạng ảnh, cắt> tỷ lệ cỡ ảnh> 1: 1
- Bạn sẽ có được một hình vuông hoàn hảo
- Sử dụng crop> crop to shape> oval
- Bạn sẽ nhận được một vòng tròn. Nếu bất kỳ cạnh nào trông thẳng, chỉ cần điều chỉnh cài đặt cắt của bạn.
- Đèn giao thông của bạn đã sẵn sàng.
Xem GIF động này để hiểu quy trình.
Cuối cùng, thêm một ô phía sau bức tranh đèn giao thông để đóng khung. Áp dụng một số hiệu ứng đổ bóng trên đèn giao thông để có được một cái nhìn đẹp mắt.
Nhóm mọi thứ để bạn có thể di chuyển hoặc định vị đèn giao thông ở nơi bạn cần.
Dự án hoàn thành đèn giao thông
Đây là cách đèn giao thông dự án đã hoàn thành của tôi trông như thế nào.
Trong tệp tải xuống, bạn có thể tìm thấy biểu đồ này và hai biểu đồ khác (biểu đồ tiến trình nhiệt kế và bánh rán). Tham khảo video của tôi ở trên để biết hướng dẫn về hai điều đó.
Thêm Biểu đồ Trạng thái Dự án cho bạn
- Biểu đồ nhiệt kế
- Biểu đồ đo với Excel
- Ghi ra mẫu biểu đồ
- Biểu đồ trạng thái + Các mẫu PM hữu ích khác của Chandoo
Phần 3 – Trang tổng quan
Cho đến nay, chúng tôi đã tạo một Biểu đồ Gantt tương tác và một biểu đồ tiến trình đèn giao thông. Trong phần cuối cùng của hướng dẫn này, chúng tôi sẽ lấp đầy những khoảng trống còn thiếu và hoàn thiện bảng điều khiển.
Bạn sẽ học,
- Màu sắc, phông chữ và biểu tượng được sử dụng trong thiết kế bảng điều khiển
- Lập một bảng vấn đề / hoạt động sắp tới năng động
- Thiết lập ô bảng điều khiển
- Thêm ảnh chụp nhanh biểu đồ gantt
- Tạo thông tin tải tài nguyên
- Bảng điều khiển dự án đã hoàn thành
Tài nguyên cho Phần 3
Tệp Excel để thực hành
Sử dụng các tệp dưới đây để thực hành các khái niệm.
- Tệp dữ liệu trống
- Bảng điều khiển dự án đã hoàn thành
Video hướng dẫn – Phần 3 của PM Dashboard
Tôi đã tạo một video hướng dẫn chi tiết giải thích cách tạo bảng điều khiển. Xem nó bên dưới hoặc từ Kênh Youtube của tôi .
Màu sắc, biểu tượng và phông chữ cho Trang tổng quan
Bạn nên quyết định cách phối màu và thiết kế trực quan của bảng điều khiển trước khi đi quá xa. Trong trường hợp của chúng tôi, tôi đã sử dụng bên dưới.
Màu sắc:
- Bảng màu mặc định của Excel +
- # FF555A & # 638EC6 cho các thanh dữ liệu trong biểu đồ tải tài nguyên
Phông chữ:
- Mở Sans Extra Bold cho các tiêu đề
- Calibri cho mọi thứ khác.
Biểu tượng: Bạn có thể thêm các biểu tượng MIỄN PHÍ từ Chèn> Biểu tượng trong Excel 365.
Tạo trang bảng điều khiển
Tiếp theo, chúng ta sẽ tạo một bảng tính mới, đặt tên là bảng điều khiển. Tạo một bố cục trống như bên dưới.
Chúng tôi sẽ điền vào điều này với các biểu đồ và bảng khác nhau.
Hoạt động động / Bảng vấn đề
Trong phần này của hướng dẫn, chúng tôi sẽ tạo một bảng Hoạt động / Vấn đề tương tác, như hình dưới đây.
Bước 1: Bảng theo dõi vấn đề
Chúng tôi đã có bảng hoạt động. Đã đến lúc thêm dữ liệu sự cố vào tệp dự án của chúng tôi.
Bảng này đã có sẵn trong tệp dữ liệu trống . Chỉ cần thêm nó vào mô hình dữ liệu bằng cách thiết lập mối quan hệ giữa Sự cố [Bên tổ chức] và Con người [Người].
Bước 2: Thiết lập điều khiển biểu mẫu
Trong trang bảng điều khiển, hãy thêm điều khiển biểu mẫu hộp tổ hợp (Ruy-băng dành cho nhà phát triển> Điều khiển biểu mẫu) và đặt nó để hiển thị một trong hai giá trị sau –
- Các hoạt động
- Vấn đề
Liên kết điều khiển này với một ô trong trang tính tính toán (J8).
Điều khiển biểu mẫu rất hữu ích để làm cho biểu đồ và trang tổng quan của bạn trở nên tương tác. Bạn có thể tìm thấy chúng trong ruy-băng Nhà phát triển của Excel.
Tìm hiểu thêm về Kiểm soát biểu mẫu và cách sử dụng chúng .
Bước 3: Tính toán 10 hoạt động & vấn đề sắp tới
Để tính toán 10 hoạt động / vấn đề sắp tới, chúng ta cần logic sau:
- Hoạt động: ngày bắt đầu là sau ngày hôm nay ()
- Vấn đề: ngày kết thúc vấn đề bị bỏ trống
Chúng ta cần giới hạn cả hai danh sách trong 10 mục đầu tiên.
Chúng ta có thể sử dụng công thức FILTER () để lấy các danh sách như vậy.
Bước 4: Chọn một trong các danh sách dựa trên đầu vào điều khiển biểu mẫu
Chúng ta có thể sử dụng công thức IF để chọn một trong hai danh sách này dựa trên những gì người dùng đã chọn trong điều khiển biểu mẫu. Vì ô J8 trong các phép tính cho chúng ta biết lựa chọn là gì, chúng ta có thể sử dụng ô đó để viết công thức IF của mình.
Khi chúng tôi có kết quả, chỉ cần sao chép các giá trị và dán chúng làm tham chiếu trong trang tổng quan. Bảng các hoạt động / vấn đề năng động của chúng tôi sẽ sẵn sàng.
Gạch KPI trên bảng điều khiển
Tiếp theo, chúng ta sẽ tạo các ô bảng điều khiển.
Bắt đầu bằng cách đặt 4 hình chữ nhật tròn với màu tô # 404040 trên trang tính bảng điều khiển.
Tiếp theo, sao chép dán đèn giao thông (phần 2 của hướng dẫn này) trên ô đầu tiên.
Tính toán KPI của dự án
Chúng ta cần tính 3 số cho các ô còn lại trên bảng điều khiển. Họ đang,
Những ngày còn lai
Số ngày còn lại để hoàn thành dự án. Đây là sự khác biệt giữa hoạt động cuối cùng của dự án Ngày kết thúc và ngày hôm nay.
Các hoạt động đang diễn ra
Đây là số lượng hoạt động đã bắt đầu và chưa hoàn thành.
Vấn đề đang chờ giải quyết
Còn bao nhiêu vấn đề vẫn chưa được đóng.
Thêm giá trị KPI vào ô bảng điều khiển
Trên trang tổng quan, hãy thêm ba hộp văn bản, mỗi hộp một trong ba số trên. Sử dụng thanh công thức, liên kết giá trị hộp văn bản với các giá trị trang tính tính toán.
Điều chỉnh định dạng của hộp văn bản. Ngoài ra, hãy thêm các biểu tượng để biểu thị từng ô KPI.
Ở giai đoạn này, các ô trang tổng quan của chúng tôi sẽ trông như thế này:
Đưa vào Biểu đồ Gantt
Chúng tôi đã tạo một biểu đồ gantt tương tác đầy đủ trong phần 1 của hướng dẫn này. Chỉ cần đi tới tab biểu đồ gantt trên trang tổng quan, sao chép một phạm vi đủ lớn, đi tới trang tổng quan và dán nó dưới dạng hình ảnh được liên kết .
Điều này sẽ cung cấp cho bạn ảnh chụp nhanh trực tiếp của biểu đồ gantt trong tab trang tổng quan.
Điều chỉnh kích thước của hình ảnh biểu đồ gantt và thêm bất kỳ đường viền cần thiết nào xung quanh nó.
Cắt và di chuyển bộ cắt nhóm sang tab trang tổng quan.
Biểu đồ tải tài nguyên
Trong trang tính tính toán, hãy tạo một bảng tổng hợp để hiển thị số lượng nhiệm vụ và hoạt động được giao cho mỗi người.
Sao chép dữ liệu bảng tổng hợp, đi tới trang tổng quan và dán nó dưới dạng liên kết vào đúng vị trí.
Điều chỉnh định dạng và áp dụng cơ sở dữ liệu trên các cột vấn đề và hoạt động.
Tùy chỉnh màu sắc của thanh dữ liệu theo bảng màu của chúng tôi.
Và bảng điều khiển của chúng tôi đã XONG!
Bảng điều khiển đã hoàn thành
Vui lòng tải xuống bảng điều khiển đầy đủ từ đây .
Ngoài ra, hãy xem danh sách phát video để tìm hiểu cách tôi xây dựng video này. Các video sẽ cung cấp rất nhiều mẹo Excel hữu ích như một phần thưởng.
Tìm hiểu thêm về Quản lý dự án …
Quản lý dự án là một trong những niềm đam mê của tôi và tôi nói về nó rất nhiều trên trang web và Youtube của mình.
Vui lòng truy cập các trang bên dưới để tìm hiểu thêm về những thứ PM thú vị khác mà bạn có thể làm với Excel.
- Ngân sách so với Phân tích và biểu đồ thực tế
- Lập ma trận rủi ro với Excel
- Bảng điều khiển danh mục dự án với Excel
Sẵn sàng sử dụng Mẫu quản lý dự án
Tạo báo cáo và trang tổng quan quản lý dự án tuyệt vời bằng cách sử dụng các mẫu của tôi. Được tin cậy bởi hơn 20.000 nhà quản lý dự án trên toàn thế giới, những mẫu này được thiết kế để khiến bạn trông thật tuyệt. Tạo biểu đồ Gantt, tiến trình, bảng thời gian, trình theo dõi vấn đề, theo dõi rủi ro, trang tổng quan dự án và trang tổng quan danh mục đầu tư bằng cách sử dụng các mẫu của tôi.
Nhấn vào đây để có được chúng ngay hôm nay.
14 Câu trả lời cho “Cách tạo Bảng điều khiển dự án tương tác hoàn toàn với Excel – Hướng dẫn”
Tôi không nhận được ngày thanh toán PowerBI từ lâu. Làm ơn cho lời khuyên.
Muốn xử lý nhiều dự án đang thực hiện tại nhiều địa điểm. Một số hoạt động là phổ biến và một số là duy nhất. CÁCH Xử lý giống nhau trong một trang tính tại một nơi mà nhiều bên liên quan có thể cập nhật tiến trình
Cảm ơn bạn! Tôi đánh giá cao tất cả những gì bạn làm.
Chandoo
Làm cách nào để bạn có được tổng phụ ở trên cùng trong một tổng hợp bảng, tôi chỉ có thể thêm vào dưới cùng nếu tôi chọn dạng bảng, ở trên cùng nếu tôi sử dụng thu gọn?
Chúc mừng
Martin
Xin lỗi vì sự nhầm lẫn. Tôi cũng đang sử dụng bố cục OUTLINE.
Xin chào Chandoo,
Chúng tôi có một cuộc tham gia lao động dân sự tại trang web. chúng tôi cần duy trì và thanh toán vào cuối mỗi tuần. vấn đề là, mức lương thay đổi từ Mason đến Helper-1, Helper-2, v.v. Ngoài ra, chúng tôi đã sử dụng để thay đổi địa điểm theo nhu cầu. Bây giờ nhiệm vụ là chúng ta cần tính toán tiền lương của họ theo sự hiện diện của họ và cũng cần phải xác định vị trí chi tiết công việc khôn ngoan. Bạn có thể giúp tôi trong vấn đề này.
Lưu ý: Nếu được yêu cầu, tôi có thể chia sẻ tệp.
Trân trọng,