Bạn đã bao giờ cần tạo biểu đồ Gantt linh hoạt trong Google Sheets chưa? Trong bài viết này, chúng ta sẽ tìm hiểu cách tạo một thời gian linh hoạt cho biểu đồ Gantt trong Google Sheets. Phương pháp là như sau.
Tạo một danh sách thời gian đa dạng
Bằng cách sử dụng một danh sách thả xuống, bạn có thể chuyển đổi giữa các đơn vị thời gian khác nhau. Điều này bao gồm Giờ, Ngày, Tuần, Hai tuần, Tháng, Quý, Nửa năm và Năm.
Hãy tưởng tượng rằng bạn muốn hiển thị các thanh Gantt theo tuần, chứ không phải là ngày, bạn có thể chọn đơn vị thích hợp từ danh sách thả xuống. Công thức tùy chỉnh sẽ điều chỉnh các đơn vị thời gian một cách phù hợp.
Tôi sẽ cũng chia sẻ công thức cho biểu đồ Gantt với bạn. Tuy nhiên, hãy nhớ rằng các đơn vị thời gian ngắn hơn thì tốt hơn. Trong các đơn vị lớn hơn, các thanh cho các nhiệm vụ ngắn hơn có thể không xuất hiện.
Hãy xem cách tạo một thời gian linh hoạt cho biểu đồ Gantt trong Google Sheets.
Tạo một danh sách thả xuống chứa các đơn vị thời gian
Công thức của tôi sẽ hỗ trợ các đơn vị sau đây.
Hours, Days, Weeks, Fortnights, Months, Quarters, Half Years, YearsTrước tiên, chúng ta sẽ tạo một danh sách thả xuống trong ô B1 để chọn các đơn vị.
- Sao chép các đơn vị trên.
- Chọn
Dữ liệu > Xác thực dữ liệu. - Phạm vi ô: B1.
- Tiêu chí: Danh sách các mục. Chèn các đơn vị đã sao chép.
- Bấm Lưu.
Sau đó, công thức linh hoạt cho biểu đồ Gantt trong ô E3 sẽ tạo ra đơn vị cho biểu đồ Gantt dọc theo hàng thứ 3 (E3:3) dựa trên giá trị được chọn trong ô B1.
Nhập các thông số khác để tạo ra thời gian linh hoạt cho biểu đồ Gantt
Chúng ta cũng cần cung cấp số lượng đơn vị cần thiết trong ô B2 và ngày bắt đầu dự án (định dạng NgàyGiờ) trong ô B3.
Để kiểm tra, bạn có thể nhập 14 trong ô B2 và DateTime 01/07/2021 00:00:00 vào ô B3.
Trong ô B3, hãy bao gồm thành phần thời gian là một trong những đơn vị của chúng ta trong thời gian linh hoạt của chúng ta là “Giờ”.
Lưu ý 1:-
Để động lấy số lượng đơn vị cần thiết (ở đây là giá trị trong ô B2), chúng ta có thể tính khoảng cách giữa ngày bắt đầu dự án và ngày kết thúc dự án.
Nhưng vấn đề khi làm như vậy là chúng ta phải xem xét đơn vị trong ô B1.
Hiện tại, chúng ta sẽ nhập giá trị B2 thủ công. Nhưng, sau này, tất nhiên, tôi sẽ cung cấp công thức đó.
Trên đây, chúng ta đã hoàn thành ba bước quan trọng sau để tạo ra thời gian linh hoạt cho biểu đồ Gantt trong Google Sheets.
- Tạo một danh sách thả xuống trong ô B1.
- Nhập số lượng đơn vị cần tạo ra trong ô B2.
- Chèn ngày bắt đầu dự án (trong định dạng thời gian) vào ô B3.
Công thức linh hoạt cho biểu đồ Gantt trong Google Sheets
Xóa nội dung trong dải hàng E3:3 và trong ô E3, chèn công thức dưới đây và sau đó định dạng dải E3:3 thành Ngày từ Định dạng > Số > Ngày.
=ArrayFormula(
if(B1="Hours",TIME(
SEQUENCE(1,B2,hour(B3),1),0,0),
ifna(
edate(B3, sequence(1,B2,0, ifs(
B1="Months",1,
B1="Quarters",3,
B1="Half Years",6,
B1="Years",12
) ) ),
sequence(1,B2,B3,ifs(
B1="Fortnights",14,
B1="Weeks",7,
B1="Days",1
))
)
)
)Lưu ý 2:-
Nếu đơn vị là “Giờ”, hãy định dạng dải E3:3 thành Thời gian (Định dạng > Số > Thời gian).
Bây giờ thay đổi đơn vị trong ô B1. Ví dụ, chọn “Nửa năm” trong ô B1. Sau đó nhập 4 trong ô B2.
Công thức sẽ tạo ra các ngày 01/07/2021, 01/01/2022, 01/07/2022 và 01/01/2023 trong dải ô E3:H3.
Nhập 01/01/2021 00:00:00 vào ô B3. Sau đó công thức sẽ trả về các ngày 01/01/2021, 01/07/2021, 01/01/2022 và 01/07/2022.
Bạn có thể giải thích công thức trên, mà hoạt động như một thời gian linh hoạt cho biểu đồ Gantt trong Google Sheets?
Giải thích công thức
Chúng ta có thể chia công thức thành ba phần. Đó là IF, EDATE và IFNA.
Phần 1 – IF (Hours)
Đây là phần cho đơn vị “Giờ”.
if(B1="Hours",TIME(
SEQUENCE(1,B2,hour(B3),1),0,0)Câu lệnh IF được sử dụng để kiểm tra giá trị trong ô B1.
Nếu nó được đánh giá là ĐÚNG, tức là if(B1="Hours", công thức sẽ trả về các đơn vị thời gian “Hàng giờ”.
Tương tự: – Cách Tăng Thời Gian Theo Phút và Giờ trong Google Sheets (Sử dụng RỒNG thay vì SEQUENCE).
Phần 2 – EDATE (Months, Quarters, Half Years và Years)
Phần này cũng đóng một vai trò quan trọng trong công thức thời gian linh hoạt của chúng ta.
Khi giá trị trong ô B1 không phải là “Giờ”, công thức phần 1 trên có thể trả về GIẢ.
Cú pháp: – IF (biểu_thức_logic, giá_trị_nếu_đúng, giá_trị_nếu_sai)
Điều đó có nghĩa rằng B1="Hours" là biểu_thức logic và phần 1 công thức là đối số giá_trị_nếu_đúng.
Nếu phần 1 được đánh giá là GIẢ, trong giá_trị_nếu_sai, IF sẽ thực hiện EDATE phần 2.
edate(B3, sequence(1,B2,0, ifs(
B1="Months",1,
B1="Quarters",3,
B1="Half Years",6,
B1="Years",12
) ) )Cú pháp: – EDATE (ngày_bắt_đầu, [tháng])
Sử dụng hàm EDATE, chúng ta có thể có được một ngày mà ngày đó là một số tháng được chỉ định (tháng) sau một ngày khác (ngày_bắt_đầu).
Ví dụ, công thức =edate("01/01/2021",5) sẽ trả về 01/06/2021.
Phải đọc:- Cách Sử Dụng Các Hàm Ngày Tháng Trong Google Sheets [Hướng Dẫn Hoàn Chỉnh].
Giải thích EDATE:-
ngày_bắt_đầu= B3 (ngày bắt đầu dự án).tháng= công thức SEQUENCE.
Chúng ta sẽ chỉ định tháng bằng cách sử dụng hàm SEQUENCE để EDATE trả về nhiều ngày.
Cú pháp: – SEQUENCE(hàng, [cột], [bắt_đầu], [bước])
hàng= 1 (chúng ta muốn tạo chuỗi trong một hàng).cột= B2 (chúng ta muốn B2 số chuỗi).bắt_đầu= 0.bước= B1. Bước kiểm tra logic IFS trong phần 2 công thức trả về 1, 3, 6 hoặc 12 dựa trên đơn vị được chọn.
Nếu B2 = 3 và IFS đánh giá là 6, chúng ta có thể dịch công thức trên thành;
edate(B3, sequence(1,3,0, 6 ) )Kết quả của chuỗi sẽ là {0,6,12} và đó là các tháng trong EDATE.
Tương tự: – Cách Lấy Chuỗi Các Tháng Trong Google Sheets.
Phần 3 – IFNA (Fortnights, Weeks và Dates)
Nếu IFS trong EDATE không khớp với bất kỳ đơn vị nào, nó sẽ trả về N/A.
Điều đó có nghĩa là đơn vị được chọn không phải là Tháng, Quý, Nửa năm hay Năm.
Trong trường hợp đó, hàm IFNA sẽ thực hiện phần dưới đây.
sequence(1,B2,B3,ifs(
B1="Fortnights",14,
B1="Weeks",7,
B1="Days",1
)Nó sẽ tạo ra các ngày trong 1 hàng, B2 số cột, bắt đầu từ B3 dựa trên giá trị bước trả về từ IFS.
Đó là cách cắt công thức linh hoạt của tôi cho biểu đồ Gantt trong Google Sheets.
Thời gian linh hoạt và cách biểu đồ Gantt phản ứng với nó
Một biểu đồ Gantt dựa trên công thức sẽ phản ứng với thời gian linh hoạt của chúng ta trong Google Sheets.
Nhân đôi đang là một lưu ý nhỏ, bạn cũng có thể sử dụng Biểu đồ Cột Chồng hoặc hàm Sparkline để vẽ biểu đồ Gantt trong Google Sheets.
Chúng ta đã viết công thức để tạo ra thời gian linh hoạt cho biểu đồ Gantt. Nó nằm trong ô E2 và yêu cầu đầu vào trong các ô B1, B2 và B3.
Đối với biểu đồ Gantt, chúng ta cần các đầu vào bổ sung sau đây.
- Ô B4: Ngày kết thúc dự án (định dạng Timestamp).
- Ô B5:B10 – Ngày bắt đầu công việc.
- Ô C5:C10 – Ngày kết thúc công việc.
Bây giờ hãy chọn dải E5:10. Trong Định dạng có điều kiện, chèn công thức tùy chỉnh sau đây.
=and(E$3>=$B5,E$3<=$C5)Điều đó sẽ tạo ra thanh dựa trên đơn vị thời gian được chọn.
Lưu ý 3:-
Nếu đơn vị thời gian là “Giờ”, hãy nhập thời gian bắt đầu và thời gian kết thúc của các công việc vào ô B5:B10 và C5:C10.
Nó phải có định dạng HH: MM: SS như 07:00:00.
Công thức bổ sung
Trong ô C2 (vui lòng tham khảo hình ảnh trên), bạn có thể thấy một giá trị mà tôi chưa sử dụng trong bất kỳ công thức nào.
Nó thực sự trả về số đơn vị dựa trên ngày bắt đầu và kết thúc dự án trong ô B3:B4 và các đơn vị thời gian được chọn trong danh sách thả xuống.
Tôi đã sử dụng công thức sau đây trong ô C2.
=ifs(
B1="Days",datedif(B3,B4,"d")+1,
B1="Months",datedif(B3,B4,"m")+1,
B1="years",datedif(B3,B4,"y")+2,
B1="Half Years",roundup((datedif(B3,B4,"d")+1)/182)+1,
B1="Quarters",roundup((datedif(B3,B4,"d")+1)/120),
B1="Fortnights",roundup((datedif(B3,B4,"d")+1)/14),
B1="Weeks",roundup((datedif(B3,B4,"d")+1)/7),
B1="Hours",roundup((B4-B3)*24)
)Bạn có thể sử dụng nó trong ô B2 hoặc tham chiếu nó để nhập thủ công số lượng đơn vị bạn muốn trong ô B2.
Đó là tất cả về cách tạo ra thời gian linh hoạt cho biểu đồ Gantt trong Google Sheets.
Cảm ơn bạn đã ở lại. Chúc bạn vui vẻ.
Sheet ví dụ 131221
Liên quan:-
