Bạn đang duy trì lịch trình dự án cũng như tiến độ trong Google Sheets? Sau đây, bạn sẽ tìm thấy hướng dẫn hữu ích. Tôi có một giải pháp, một công thức mảng, để đánh dấu các công việc cha là hoàn thành khi tất cả các công việc phụ tương ứng của nó đã hoàn thành.
Hãy bắt đầu với một công việc cha và các công việc phụ của nó. Giả sử bạn có một công việc trong ô A2, các công việc phụ của nó trong các ô B2:B4 và trạng thái (luồng công việc) của các công việc phụ trong C2:C4.
Trong một công việc duy nhất với một số lượng công việc phụ hạn chế như trên, bạn có thể sử dụng một công thức logic để đánh dấu công việc là hoàn thành.
Công thức sau trong ô D2 sẽ trả về “Đang chờ” vì hai công việc phụ vẫn chưa hoàn thành.
=nếu(và(C2="Hoàn thành",C3="Hoàn thành",C4="Hoàn thành"),"Hoàn thành","Đang chờ")
Tuy nhiên, nó sẽ không hoạt động tốt hoặc trở nên phức tạp khi có nhiều công việc và công việc phụ trong lịch trình dự án của bạn. Việc sử dụng Vlookup một cách thông minh là cách hiệu quả nhất để đánh dấu nhiều công việc đã hoàn thành.
Định dạng dữ liệu để đánh dấu công việc là hoàn thành nếu tất cả các công việc phụ đã hoàn thành
Bạn chỉ có thể sử dụng công thức của tôi trong dữ liệu có cấu trúc như sau (A1:C). Nếu dữ liệu của bạn không tuân thủ với dữ liệu trong cột A đến C, bạn có thể muốn sửa đổi dữ liệu của mình. Xem “Ảnh chụp màn hình 2” để biết dữ liệu không có cấu trúc và cách định dạng nó.
Ảnh chụp màn hình 1:
Phạm vi E1:F chứa công thức của tôi và kết quả. Cột E chứa các công việc duy nhất và cột F chứa xem các công việc đó đã hoàn thành hay chưa.
Công thức trong ô F1 (bạn sẽ nhận được nó dưới đây) sẽ đánh dấu các công việc là Hoàn thành trong cột F khi tất cả các công việc phụ đã hoàn thành trong cột C.
Hãy xem chuỗi “Đang chờ” đối với công việc số 2 trong ô F3. Lý do là nó có ba công việc phụ và trong đó, hai công việc vẫn chưa hoàn thành.
Ảnh chụp màn hình 2:
Tôi đã thấy nhiều người dùng sử dụng định dạng sau để nhập các công việc và công việc phụ.
Trong trường hợp này, bạn có thể sử dụng một cột trợ giúp để tạo cấu trúc cho nó.
Hãy xem lại “Ảnh chụp màn hình 2” về cột B ẩn. Trong cột ẩn B, tôi có cùng dữ liệu mà bạn có thể thấy trên “Ảnh chụp màn hình 1” cột A. Vì vậy trong công thức, tôi có thể sử dụng B1:D thay vì A1:C là phạm vi dữ liệu.
Ở ví dụ này, tôi đang sử dụng dữ liệu được hiển thị trên bản chụp màn hình đầu tiên.
Công thức Google Sheets để đánh dấu công việc cha là hoàn thành
Trong ví dụ của tôi, bạn có thể thấy rằng các công việc chính (cha) được lặp lại trong cột A. Sử dụng công thức UNIQUE sau, bạn có thể trích xuất các công việc duy nhất trong cột E.
Công thức trong ô E1:
={"Công việc";unique(A2:A10)}
Giờ chúng ta có các công việc cha duy nhất trong cột E. Trong ô F1, công thức sau đánh dấu các công việc (cha) đã hoàn thành với chuỗi “Đã hoàn thành”.
=nếu(lỗi({"Trạng thái";Công thứcMảng(nếu(vlookup(E2:E,sắp xếp({A2:C,if(C2:C="Hoàn thành","Z",C2:C)},4,1),4,0)="Z","Đã hoàn thành","Đang chờ"))}))
Công thức này kiểm tra các công việc phụ của từng công việc cha để xác định hoàn thành.
Hãy lấy công việc cha “Công việc # 1: Kéo cáp” làm ví dụ. Nó nằm trong phạm vi A2:A7. Tất cả các công việc phụ của nó trong phạm vi C2:C7 đều đã hoàn thành.
Khi tôi thay đổi một trong các công việc phụ thành “Giữ” trong ô C4, giá trị trong ô F2 sẽ thay đổi từ “Đã hoàn thành” thành “Đang chờ”.
Làm thế nào công thức đánh dấu các công việc là hoàn thành khi tất cả các công việc phụ của nó đã hoàn thành? Dưới đây là logic và giải thích của công thức.
Cách công thức kiểm tra công việc phụ để hoàn thành
Thực tế, công thức cốt lõi được sử dụng trong ô F1 là Vlookup. Nhưng bạn có thể gọi nó là một công thức kết hợp vì các công thức khác (SORT và IF) được sử dụng.
Hãy để tôi giải thích các công thức được sử dụng trong sự kết hợp này từng bước một. Tôi sẽ sử dụng nhiều ảnh chụp màn hình hơn để bạn hiểu rõ công thức.
Bước 1: Di chuyển các công việc phụ đã hoàn thành xuống dưới cùng
Trên thực tế, công thức được gạch chân màu đỏ là phạm vi tìm kiếm trong Vlookup. Để hiểu phạm vi tìm kiếm này trước tiên, bạn phải hiểu bài kiểm tra logic IF được đánh dấu màu vàng.
Dưới đây là công thức được đánh dấu màu vàng (trừ ArrayFormula) trên ảnh trên.
Lưu ý: Không sử dụng hàm ArrayFormula trong SORT.
=công thứcMảng(nếu(C2:C="Hoàn thành","Z",C2:C))
Nhập công thức này vào ô D2 để xem nó trả về gì. Nó sẽ trả về chuỗi “Z” đối với “Hoàn thành” trong mỗi hàng. Nếu văn bản trong cột C không phải là “Hoàn thành”, công thức sẽ trả lại văn bản đó.
Tôi đã thêm cột này làm cột thứ tư vào dữ liệu hiện có trong A1:C bằng cặp Ngoặc nhọn như sau.
=công thứcMảng({A2:C,IF(C2:C="Hoàn thành","Z",C2:C)})
Một lần nữa hãy xem công thức SORT được gạch chân. Bạn có thể thấy rằng tôi đã sử dụng công thức phía trên như là phạm vi sắp xếp và sắp xếp cột thứ tư trong nó theo thứ tự tăng dần.
=sắp xếp({A2:C,IF(C2:C="Hoàn thành","Z",C2:C)},4,1)
Bước 2: Vlookup để kiểm tra xem tất cả công việc phụ đã hoàn thành chưa
Tại sao tôi đã sắp xếp dữ liệu theo thứ tự tăng dần? Vlookup là câu trả lời cho câu hỏi này.
Khi sắp xếp cột thứ tư theo thứ tự tăng dần (A-Z), tất cả các công việc phụ đã hoàn thành, được biểu thị bằng chữ “Z”, sẽ được di chuyển xuống dưới cùng. Xem “Công việc # 2” được đánh dấu trên ảnh chụp màn hình. Việc sắp xếp này có vai trò quan trọng trong Vlookup.
Hãy xem công thức Vlookup.
Cú pháp:
VLOOKUP(search_key, range, index, [is_sorted])
search_key: Khóa tìm kiếm trong Vlookup là E2:E (hai công việc duy nhất).
range: Phạm vi tìm kiếm là công thức sắp xếp ở trên, trả về 4 cột. Hãy nhớ, cột thứ tư chứa “Z” cho các công việc phụ đã hoàn thành và cũng được sắp xếp theo thứ tự tăng dần.
index: Cột chỉ mục (cột đầu ra) là cột thứ tư chứa “Z” cho các công việc phụ đã hoàn thành.
Vì có các công việc giống nhau lặp lại nhiều lần trong cột đầu tiên trong phạm vi Vlookup, Vlookup chỉ trả về giá trị từ cột thứ tư của hàng đầu tiên của công việc tìm thấy.
Ví dụ: Công việc # 2 lặp lại ba lần. Vlookup sẽ trả về giá trị “Đang chờ” từ cột thứ tư.
Nếu tất cả các công việc phụ đều hoàn thành, Vlookup nhất định sẽ trả về chuỗi “Z”. Điều đó có nghĩa là công việc đã hoàn thành. Lý do là chúng ta đã di chuyển các hàng chứa “Z” xuống dưới cùng. Nếu Vlookup vẫn trả về “Z” có nghĩa là tất cả các công việc phụ đã hoàn thành (Z). Đó là logic!
