วิธีคำนวณ Critical Path Method ในงานบริหารโครงการ ด้วย Excel

ต่อเนื่องจากบทความการบริหารโครงการ หรือ Project Management ด้วย Excel ที่ผมได้เขียนไปก่อนหน้านี้ ซึ่งเป็นเรื่องของการทำ Gantt Chart คราวนี้ผมจะขอพูดถึงเรื่องของการวางแผนโดยใช้ Critical Path หรือคือเส้นทางการทำงานที่สำคัญที่สุด ซึ่งถ้าหากงานใดงานหนึ่งใน Path นั้นเกิดการ Delay ปุ๊ปจะทำให้โครงการโดยรวม Delay ทันที

ซึ่งเราจะคำนวณพวก Early Start, Early Finish, Late Start, Late Finish ของแต่ละงานแบบอัตโนมัติครับ บอกไว้ก่อนว่าสูตรในบทความนี้ค่อนข้างซับซ้อน(มาก) กรุณาทำใจก่อนดู 555

โดยผมใช้ภาพประกอบจากบทความนี้ ซึ่งมีอธิบายเรื่องวิธีการคำนวณแบบ Manual ให้ด้วย แต่ผมจะอธิบายวิธีการทำแบบ Auto ใน Version Excel ให้ดูครับ

วิธีการอ่านรูปคือ

• A,B,C คือชื่อของงานย่อยๆ
• ตัวเลขในกล่องสี่เหลี่ยมคือ ระยะเวลาที่ต้องใช้ในการทำงานนั้นๆ
• ลูกศรที่ชี้คือทำงานนั้นเสร็จแล้วต้องทำงานอะไรต่อ (แสดงความ dependency กัน) เช่น งาน B จะเริ่มทำได้เมื่อ A และ D เสร็จแล้วทั้งคู่เท่านั้น

Assumption

สมมติว่าทำงานนึงเสร็จวันที่ 5 งานที่ต้องทำลำดับต่อไปจะเริ่มได้เร็วสุดวันที่ 6 นะครบ (ถือว่าวันที่ 5 คือสิ้นวันแล้ว)

องค์ประกอบในการคำนวณ

มีองค์ประกอบอยู่ 4 ตัวที่สำคัญ คือ

• Early Start (ES) : งานนั้นๆ จะเริ่มเร็วสุดได้เมื่อไหร่ = Early Finish ของ Activity ก่อนหน้าอันสุดท้าย + 1 นั่นเอง
• Early Finish (EF) : งานนั้นๆ จะเสร็จได้เร็วสุดเมื่อไหร่ = Early Start + Duration ของงาน -1 (เพื่อ adj ให้นับวันเริ่มด้วย)
• Late Finish (LF) : งานนั้นๆ จะเสร็จได้ช้าสุดเมื่อไหร่ = Late Start ของ Activity ถัดไปอันแรก -1
• Late Start (LS) : งานนั้นๆ จะเริ่มได้ช้าสุดเมื่อไหร่ = Late Finish – Duration + 1

บันทึกข้อมูลดิบใน Excel

เราบันทึกข้อมูลลงไปประมาณนี้

เริ่มคำนวณ ES และ EF

เราจะต้องคำนวณ Early Start (ES) ก่อน ซึ่งคำนวณมาจาก

Early Start (ES) = Early Finish ของ Activity ก่อนหน้าอันสุดท้าย + 1

ซึ่ง Early Finish ของ Activity ก่อนหน้าอันสุดท้าย ถ้าไม่มีก็คือถือว่าเป็น 0 ไปนั่นเอง ซึ่งเราใช้ IF มาดักได้

Early Start (ES) = IF(C2="",0,EFก่อนหน้า)+1

ส่วน EFก่อนหน้า เรายังไม่มี ก็ต้องคำนวณออกมาก่อน ซึ่งทำได้โดยใช้สูตรที่ว่า

Early Finish (EF) = ESตัวนั้น + Duration -1 

ซึ่งพอเขียนสูตรลงไป ณ ตอนนี้จะได้ดังนี้

ซึ่ง EF ก่อนหน้า มันอาจมีหลายตัวก็ได้ เช่น กิจกรรม B มีก่อนหน้าคือ A กับ D ซึ่งเราต้องทำการ Lookup ค่า EF ของ A กับ D ออกมา แล้วหาค่า MAX

การจะ Lookup แล้วมา MAX ได้ เราจะทำการคำนวณแบบ Array ให้มีค่าทุก item ในช่องเดียวกัน ซึ่ง Excel ไม่มีฟังก์ชัน Split ตรงๆ ผมเลยต้องใช้ฟังก์ชัน FILTERXML มาช่วยในการ Split ดังนี้ (เทคนิคการดึงค่าด้วย FILTERXML ผมเรียนรู้มาจากคลิป Excel Wizard อันนี้ ซึ่งต้องใช้ Excel 2013 ขึ้นไป)

ถ้าอยาก Split ค่า A ,D ใน C4 ออกมาเป็น Array ผมสามารถเขียนสูตรได้ดังนี้

ใน I4

=FILTERXML("<a><b>"&SUBSTITUTE(C4,",","</b><b>")&"</b></a>","//b")

<a> กับ <b> ใส่อะไรก็ได้ แค่เป็นชื่อ tag เปิดปิดคล้ายๆ ภาษา HTML ให้ FILTERXML ใช้เลือก item ที่ต้องการได้ (ในที่นี้ผมเลือก item ที่ชื่อ tag ว่า b

จากนั้นค่อยทำการ Lookup EF ดังนี้

=INDEX(E:E,MATCH(H4#,A:A,0))

ซึ่งพอรวบ 2 สูตรเข้าด้วยกัน และใช้ MAX ครอบอีกทีจะได้ดังนี้

ใน I4

=MAX(INDEX(E:E,MATCH(FILTERXML("<a><b>"&SUBSTITUTE(C4,",","</b><b>")&"</b></a>","//b"),A:A,0)))

จากนั้นเอาสูตรไปแทนคำว่า EF ก่อนหน้าที่เราเขียนทิ้งไว้ตอนแรก ใน D4 แล้ว copy ให้ครบทุกแถวจะได้ดังนี้

ใน D3

=IF(C3="",0,MAX(INDEX(E:E,MATCH(FILTERXML("<a><b>"&SUBSTITUTE(C3,",","</b><b>")&"</b></a>","//b"),A:A,0))))+1

ซึ่งถ้าใครมี Excel 365 อันล่าสุดจะมีฟังก์ชัน LET มาให้ใช้ ซึ่งจะช่วยให้เราประกาศตัวแปรในสูตรได้ ซึ่งจะช่วยแบ่งสูตรให้อ่านง่ายขึ้นได้มากดังนี้ ซึ่งจะได้ผลลัพธ์เดียวกันกับสูตรข้างบนนะ

=LET(prepareText,"<a><b>"&SUBSTITUTE(C3,",","</b><b>")&"</b></a>",
split,FILTERXML(prepareText,"//b"),
MaxEFBefore,MAX(INDEX(E:E,MATCH(split,A:A,0))),
IF(C3="",0,MaxEFBefore)+1)

ที่นี้จะเห็นว่างานที่จะเสร็จเป็นอันสุดท้ายคือ C ซึ่งจะไปจบวันที่ 31 ไอ้เส้นทางที่จบที่ C นี่แหละ คือ Critical Path ล่ะ

เดี๋ยวต่อไปเราจะคำนวณ LF กับ LS นะครับ โดยจะคำนวณ LF ก่อน เพราะเรารู้แล้วว่าต้องจบไม่เกิน 31 นะ

ปรับตารางให้ทำงานง่ายขึ้น

เพื่อให้ง่ายขึ้น จากนั้นผมจะเพิ่มคอลัมน์อีกอัน เพื่อคำนวณว่า งานนั้นมีอะไรต่อหลังบ้าง?

ถ้ามี Excel 365

ซึ่งการคำนวณถ้าใครมี Excel 365 เราเขียนสูตรง่ายๆ เลย แค่นี้ (Assume ว่าชื่อ Task ไม่ได้มีคำเป็น Subset ของกันและกัน)

ใน F6

=FILTER(A:A,ISNUMBER(FIND(A6,C:C)),"")

ซึ่งสามารถใช้ TEXTJOIN ช่วยรวบเพื่อแสดงผลออกมาก็ได้ดังนี้

ใน F3

=TEXTJOIN(",",TRUE,FILTER(A:A,ISNUMBER(FIND(A3,C:C)),""))

ถ้าเป็น Excel เก่ากว่า 365

ถ้าใครไม่มี Excel 365 หรือเป็น Version เก่าๆ ที่ไม่มี FILTER หรือ TEXTJOIN การคำนวณจะค่อนข้างซับซ้อนมากหน่อย (แถมใช้ LET ไม่ได้อีก) เช่นใช้ FIND หาตัวที่ต้องการ แล้วเอาลำดับแถวมา แล้วใช้ SMALL คัดสิ่งที่ต้องการเป็นต้น ดังนี้

ใน F6

=INDEX(A:A,SMALL(IF(ISNUMBER(FIND(A6,C:C)),ROW(C:C)),ROW(A1:INDEX(A:A,(COUNT(IF(ISNUMBER(FIND(A6,C:C)),(ROW(C:C)))))))))

การไม่มี TEXTJOIN ผมจะไม่ขอรวบเป็น B,E นะครับเพราะเท่าที่ผมลอง ถ้าไม่มี CONCAT หรือ TEXTJOIN มันไม่น่าจะรวบแบบ Array ได้ เดี๋ยวเราจะเอาสูตรยาวๆ นี้ไปคำนวณ LF เลยทีเดียว

คำนวณ LF และ LS

สำหรับ LF นั้น = Late Start ของ Activity ถัดไปอันแรก -1 แต่ถ้าเป็น Activity สุดท้ายแล้ว (ไม่มีตัวต่อ ก็จะเท่ากับค่า MAX ของ EF)

ดังนั้นเราจะเขียนสูตรได้ว่า ถ้าตัวตามหลังไม่มี ก็ให้ LF เป็น MAX EF เลย

=IF(F3="",MAX(E:E),LSอันแรก -1)

จากนั้นเราคำนวณ LS ก่อน

= LateFinishอันนั้น - Duration + 1

คราวนี้เราจะกลับมาคำนวณ LF ที่ค้างไว้ โดย LS อันแรก ก็คือต้อง Lookup LS ของงานต่อหลังมา แล้วหาค่า MIN

ถ้ามี Excel365 จะเป็นแบบนี้

ใน I4

=MIN(INDEX(H:H,MATCH(FILTER(A:A,ISNUMBER(FIND(A4,C:C)),""),A:A,0)))

จากนั้นเรายัดสิ่งนี้เข้าไปแทนคำว่า LS อันแรก ใน G4 แล้ว Copy Paste ให้ครบทุกแถว

ใน G3 สูตรจะเป็น

=IF(F3="",MAX(E:E),MIN(INDEX(H:H,MATCH(FILTER(A:A,ISNUMBER(FIND(A3,C:C)),""),A:A,0))) -1)

แล้วเราก็จะได้ผลลัพธ์ครบทุกช่องตามต้องการแล้ว

ถ้าเป็น Excel เก่ากว่า 365

หลังจากลองเขียนสูตรแบบเต็มสรีม และอ้างทั้งคอลัมน์แล้วปรากฏว่าเครื่องรับไม่ไหว 555

เดี๋ยวต้องปรับให้สูตรมัน Efficient มากขึ้น โดยผมขอเขียนเผื่อไว้ถึงแค่แถว 1000 พอนะครับ เพราะการอ้างทั้งคอลัมน์การคำนวณจะช้ากว่ามากเลย (คงไม่มีงานถึง 1000 งานหรอกมั้ง)

และการจะเช็คว่ามีงานต่อหลังหรือไม่ จริงๆ เขียนสูตรเท่านี้ก็น่าจะพอ ผลลัพธ์จะ Efficient กว่าเดิมที่เราต้องดึงมาทุกค่า

=MIN(IFERROR(FIND(A3,$C$3:$C$1000),""))=0

จากนั้นใน LF กับ LS ก็ทำตาม Step ข้างบน เป็นแบบนี้

ใน G3

=IF(F3,MAX($E$3:$E$1000),LSแรก-1)

ใน H3

=G3-B3+1

คราวนี้เราจะลองทำการ Lookup แบบใช้ Excel Version เก่าบ้าง

ใน I6

=INDEX($A$3:$A$1000,SMALL(IF(ISNUMBER(FIND(A6,$C$3:$C$1000)),ROW($C$3:$C$1000)-ROW($C$2)),ROW($A$1:INDEX(A:A,(COUNT(IF(ISNUMBER(FIND(A6,$C$3:$C$1000)),(ROW($C$3:$C$1000)-ROW($C$2)))))))))

จากนั้นทำการ LookupLS แล้วหาค่า MIN

ใน I3 จะเป็นแบบนี้

=MIN(INDEX(H:H,MATCH(INDEX($A$3:$A$1000,SMALL(IF(ISNUMBER(FIND(A3,$C$3:$C$1000)),ROW($C$3:$C$1000)-ROW($C$2)),ROW($A$1:INDEX(A:A,(COUNT(IF(ISNUMBER(FIND(A3,$C$3:$C$1000)),(ROW($C$3:$C$1000)-ROW($C$2))))))))),A:A,0)))

คราวนี้เรายัดสูตรนี้ลงไปในคำว่า LSแรก ในคอลัมน์ G

ใน G3 จะเป็นแบบนี้

=IF(F3,MAX($E$3:$E$1000),MIN(INDEX(H:H,MATCH(INDEX($A$3:$A$1000,SMALL(IF(ISNUMBER(FIND(A3,$C$3:$C$1000)),ROW($C$3:$C$1000)-ROW($C$2)),ROW($A$1:INDEX(A:A,(COUNT(IF(ISNUMBER(FIND(A3,$C$3:$C$1000)),(ROW($C$3:$C$1000)-ROW($C$2))))))))),A:A,0)))-1)

แล้วเราก็จะได้ผลลัพธ์สุดท้ายตามต้องการ

สรุปสูตรที่บรรทัดที่ 3 ของแต่ละอัน

Excel 365

D3 : Early Start

=LET(prepareText,"<a><b>"&SUBSTITUTE(C3,",","</b><b>")&"</b></a>",
split,FILTERXML(prepareText,"//b"),
MaxEFBefore,MAX(INDEX(E:E,MATCH(split,A:A,0))),
IF(C3="",0,MaxEFBefore)+1)

E3 : Early Finish

=D3+B3-1

F3 : งานต่อหลัง

=TEXTJOIN(",",TRUE,FILTER(A:A,ISNUMBER(FIND(A3,C:C)),""))

G3 : Late Finish

=IF(F3="",MAX(E:E),MIN(INDEX(H:H,MATCH(FILTER(A:A,ISNUMBER(FIND(A3,C:C)),""),A:A,0))) -1)

H3 : Late Start

=G3-B3+1

Excel เก่ากว่า 365

D3 : Early Start (ต้องมี Excel 2013 ขึ้นไป เพราะใช้ FILTERXML)

=IF(C3="",0,MAX(INDEX(E:E,MATCH(FILTERXML("<a><b>"&SUBSTITUTE(C3,",","</b><b>")&"</b></a>","//b"),A:A,0))))+1

E3 : Early Finish

=D3+B3-1

F3 : เป็นตัวสุดท้าย

=MIN(IFERROR(FIND(A3,$C$3:$C$1000),""))=0

G3 : Late Finish

=IF(F3,MAX($E$3:$E$1000),MIN(INDEX(H:H,MATCH(INDEX($A$3:$A$1000,SMALL(IF(ISNUMBER(FIND(A3,$C$3:$C$1000)),ROW($C$3:$C$1000)-ROW($C$2)),ROW($A$1:INDEX(A:A,(COUNT(IF(ISNUMBER(FIND(A3,$C$3:$C$1000)),(ROW($C$3:$C$1000)-ROW($C$2))))))))),A:A,0)))-1)

H3 : Late Start

=G3-B3+1

จัดเรียงหน้าตาและทำ Gantt Chart

จากนั้นจัดเรียงคอลัมน์ใหม่ให้เหมาะสม เช่น ให้ LS ขึ้นก่อน LF โดย Cut แล้ย้ายคอลัมน์ซะ

จากนั้นขั้นตอนการใส่สี จะทำได้ 2 แบบ คือ แบบแรก เขียนสูตรให้ออกมาเป็น Code ตัวเลข 0,1,2 ตามเงื่อนไขดังนี้

• 0 = ไม่อยู่ในช่วงไหนเลย
• 1 = อยู่ในช่วงของ LS-LF
• 2 = อยู่ในช่วงของ ES-EF

โดยจะใส่สูตรแบบนี้ ที่ช่อง I3 แล้ว Copy ไปใช้ทั้งตาราง แล้วเราก็ใส่ Conditional Format ให้เหมาะสมตามแต่ละเลข

=IF(AND(I$2>=$D3,I$2<=$E3),2,IF(AND(I$2>=$G3,I$2<=$H3),1,0))

หรือจะทำอีกแบบคือ ใส่ Conditional Format 2 อัน สำหรับในช่อง ES-EF กับ LS-LF ก็ได้ ซึ่งในบทความนี้ผมจะใช้วิธีนี้แทนที่จะเขียนเลข 0,1,2 ออกมา

สำหรับ ES-EF เราจะทำเป็น Pattern สีเขียว จะได้มองทะลุด้านหลังได้ ซึ่งเขียน Conditional Format แบบ Custom ดังนี้ (เลือกพื้นที่เริ่มจาก I3)

=AND(I$2>=$D3,I$2<=$E3)

สำหรับ LS-LF เราจะทำเป็นสีเหลือง ซึ่งเขียน Conditional Format แบบ Custom ดังนี้ (เลือกพื้นที่เริ่มจาก I3)

=AND(I$2>=$G3,I$2<=$H3)

ซึ่งเราจะเรียงเอาสีเขียวขึ้นก่อน ไม่งั้นเดี๋ยวจะมองไม่เห็น

แค่นี้เราจะเห็นได้ชัดเลยว่าสามารถเริ่มงานอะไรได้ตั้งแต่ตอนไหน เริ่มได้ช้าสุดตอนไหน

และเราจะสังเกตได้ง่ายเลยว่าตรงไหนคือ Critical Path ซึ่งก็คืองานที่สองสีซ้อนกัน 100% ทั้งงานนั่นเอง (ไม่มีเวลาให้ Late ได้เลย)

ซึ่งทุกอย่างจะเปลี่ยนแบบอัตโนมัติทั้งหมดด้วย!

ลองเปลี่ยน Dependency

ลองแก้โจทย์ว่างาน G ต้องทำงาน B ให้เสร็จก่อนดู

จะเห็นว่าตัวเลขทุกอย่างเปลี่ยนตามหมดเลย

ซึ่งถ้าลอง Sort ตาม LS อาจจะดูง่ายขึ้นได้ดังนี้

สรุป

วิธีที่แสดงในบทความนี้ ใช้เทคนิคการเขียนสูตรซึ่งใช้ Excel 2013 ขึ้นไป (เพราะใช้ FILTERXML) ถ้าใครมี Excel version เก่ากว่านี้ แล้วมีวิธีดีๆ อย่างอื่นอีก ก็ลองมาแชร์กันได้นะครับ