Tạo Bảng điều khiển dự án tương tác hoàn toàn với Excel

bảng điều khiển quản lý dự án - đã hoàn thiện

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:

bảng điều khiển quản lý dự án - đã hoàn thiện

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.

gif - bản trình diễn biểu đồ gantt

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

dữ liệu - hoạt động

Bảng hoạt động

dữ liệu - con người

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.

mối quan hệ bảng - biểu đồ gantt dự án

Mẹo

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.

bảng xoay để hỗ trợ tính toán biểu đồ gantt

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.

ngày bắt đầu gantt

Đặ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.

thiết lập biểu đồ gantt - giải thích

  1. Tên danh mục
  2. Hoạt động
  3. Người được giao nhiệm vụ
  4. Bắt đầu
  5. Chấm dứt
  6. % xong 
  7. % 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.

Mẹo

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.

công thức lưới để kiểm tra ngày hoạt động
=AND($F7<=J$3,$G7>=J$3)

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.

quy tắc định dạng có điều kiện

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 ;;;

Mẹo

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:

gantt-chart-after-addin-cf-rules

Để 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 ;;;
Mẹo

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ẹo

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.

gif - bản trình diễn biểu đồ gantt

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 …

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. 

Quản lý dự án - Đèn giao thông - DemoCó 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.

quy tắc định dạng có điều kiện cho đèn giao thông

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à ;;;

Mẹo

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.

  1. Làm cho hình ảnh lớn hơn
  2. Từ ruy-băng Định dạng ảnh, cắt> tỷ lệ cỡ ảnh> 1: 1
  3. Bạn sẽ có được một hình vuông hoàn hảo
  4. Sử dụng crop> crop to shape> oval
  5. 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.
  6. Đèn giao thông của bạn đã sẵn sàng.

Xem GIF động này để hiểu quy trình.

tạo liên kết hình ảnh vòng tròn cho đèn giao thông

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 dạng liên kết hình ảnh đèn giao thông

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.

Quản lý dự án - Đèn giao thông - Demo

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

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-phông chữ-biểu tượng được sử dụng trong bảng điều khiển quản lý dự án

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.

Nhận các biểu tượng miễn phí 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.

pm dashboard - canvas

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.

demo-tương tác-hoạt động-sự cố-bảng

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.  

dự án-vấn đề-bảng

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điều khiển biểu mẫu hộp kết hợp hoàn hảo để hiển thị các lựa chọn trong trang tổng quan

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).

Mẹo

Đ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.

'note: $I$6 has TODAY() date
10 activities
=INDEX(FILTER(activities,activities[Start Date]>$I$6),SEQUENCE(10),{1,2,4})
10 issues
=IFERROR(INDEX(SORT(FILTER(issues, issues[Date Closed]=""),4,1),SEQUENCE(10),{1,2,4}),"")

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.

demo-tương tác-hoạt động-sự cố-bả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.

pm-dashboard-kpi-tile-setup

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.

=MAX(activities[End Date])-TODAY()

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.

=COUNTIFS(activities[Start Date],"<="&TODAY(), activities[% Done],"<1")

Vấn đề đang chờ giải quyết

Còn bao nhiêu vấn đề vẫn chưa được đóng.

=COUNTIFS(issues[Date Closed],"")

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:

bảng điều khiển dự án hoàn thành gạch kpi

Đư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.

bảng tổng hợp với thông tin tải tài nguyên

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

bảng điều khiển quản lý dự án - đã hoàn thiện

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
Bảng điều khiển danh mục dự án - Chandoo

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.

Có điều gì đó để nói?

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”

  1. Shweta Bandarkar nói:

    Tôi không nhận được ngày thanh toán PowerBI từ lâu. Làm ơn cho lời khuyên.

    Hồi đáp
  2. Rajnikant Katira nói:

    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

    Hồi đáp
  3. Denise Z. nói:

    Cảm ơn bạn! Tôi đánh giá cao tất cả những gì bạn làm.

    Hồi đáp
  4. Martin Lewis nói:

    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

    Hồi đáp
    • Chandoo nói:

      Xin lỗi vì sự nhầm lẫn. Tôi cũng đang sử dụng bố cục OUTLINE.

      Hồi đáp
  5. Sethumadhavan VS nói:

    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,

Recommended For You

About the Author: hocketoan