ตัวอย่าง gantt chart โครงการก่อสร้าง

หัวข้อการทำงานจริงลำดับถัดไปที่มีแฟนเพจเรียกร้องมาเยอะ นั่นก็คือเรื่องของ Project Management หรือ การบริหารโครงการด้วย Excel นั่นเอง โดยที่จะเป็นทั้งเรื่องของการทำ Gantt Chart และการทำ S-Curve ด้วย

แต่บอกไว้ก่อนว่าถ้าจะทำงาน Project Management อย่างจริงจัง ผมแนะนำให้ใช้โปรแกรม Microsoft Project ซึ่งออกแบบมาให้ทำงานแนวนี้โดยเฉพาะมากกว่านะครับ อย่างไรก็ตามถ้าโครงการเป็นงานที่ไม่ซับซ้อนมาก หรือองค์กรเราไม่มีโปรแกรม Microsoft Project ให้ใช้ เราก็ยังสามารถใช้ Microsoft Excel ทำงานแนวนี้ได้เช่นกัน (ก็อย่างที่บอกว่า Excel เป็น Super เป็ด ทำได้ทุกอย่าง)

ในบทความนี้ผมจะแสดงวิธีทำโดยใช้สูตร Excel ปกติ ร่วมกับ Conditional Format ซึ่งจะใช้ได้กับ Excel ทุก Version ให้ดูก่อน แต่ในบทความตอนต่อๆ ไปในอนาคต ยังมีวิธีอื่นๆ อีก เช่น ใช้ Power Query +Pivot Table +Pivot Chart มาช่วยอีก (ก็ผมชอบ Pivot นี่) รวมถึงอาจจะมีวิธี DAX & Data Model อีก

เพื่อไม่ให้เสียเวลา ไปดูวิธีแก้ปัญหาด้วยสูตรกันเลยครับ

  • ข้อมูลดิบ
  • สร้าง Gantt Chart
    • Allocate Budget
    • Fill สี Gantt Chart
  • สร้าง S-Curve
    • S-Curve ของ Budget
  • เพิ่ม Slicer เพื่อให้มีลูกเล่นในการ Filter
  • ปรับแต่งความสวยงาม
  • สรุป

ข้อมูลดิบ

เบื้องต้นเราจะมีการบันทึกข้อมูลว่ามีงานหลัก งานย่อย อะไรบ้าง เริ่มเมื่อไหร่ ใช้เวลาเท่าไหร่ถึงจะจบ ดังนี้

id งานย่อย งานหลัก วันที่เริ่ม ระยะเวลา งบประมาณ
1 Sub Task 1 Main 1 5/3/2020 60 2000
2 Sub Task 2 Main 1 10/3/2020 100 1600
3 Sub Task 3 Main 1 15/3/2020 30 3000
4 Sub Task 4 Main 2 20/3/2020 7 1100
5 Sub Task 5 Main 2 25/3/2020 9 2900
6 Sub Task 6 Main 2 30/3/2020 7 1500
7 Sub Task 7 Main 2 4/4/2020 110 1100
8 Sub Task 8 Main 2 9/4/2020 120 1500
9 Sub Task 9 Main 2 14/4/2020 100 1900
10 Sub Task 10 Main 3 19/4/2020 210 2100
11 Sub Task 11 Main 3 24/4/2020 240 2600
12 Sub Task 12 Main 3 29/4/2020 190 2100
13 Sub Task 13 Main 3 4/5/2020 150 1300
14 Sub Task 14 Main 3 9/5/2020 160 1300
15 Sub Task 15 Main 3 14/5/2020 210 1600
16 Sub Task 16 Main 3 19/5/2020 290 2900

ซึ่งเราจะคำนวณวันที่จบได้จากข้อมูลวันเริ่ม และระยะเวลา ซึ่งถ้าเราไม่ได้สนใจว่าจะเป็นวันทำงานหรือวันหยุด เราก็ใช้วิธีบวกกันง่ายๆ ได้เลย ดังนี้

วันที่จบ

=[@วันที่เริ่ม]+[@ระยะเวลา]

Tips : ถ้าสนใจเรื่องวันทำการด้วย ก็ต้องใช้ WORKDAY มาช่วยดังนี้

=WORKDAY.INTL([@วันที่เริ่ม],[@ระยะเวลา],เลือกโหมดวันหยุดประจำสัปดาห์,วันหยุดพิเศษ)

แต่เพื่อความง่าย ผมจะขอไม่สนใจเรื่องวันทำการวันหยุดละกัน เอาบวกกันตรงๆ เลย

ตัวอย่าง gantt chart โครงการก่อสร้าง

ทีนี้สมมติว่าเราจะแสดงข้อมูลเป็นราย Week เราก็เขียนหัวตารางว่า Week1 แล้วลากยาวไปด้านขวาเลย มันจะแสดงคำว่า Week2, Week3… ไปเรื่อยๆ เอายาวเท่าที่ต้องการ

ทีนี้ในบริเวณข้างบนของคอลัมน์ Week1 เราเขียนสูตรเพื่อคำนวณหาวันจันทร์ใน week เริ่มต้นในข้อมูลที่มี (assume ว่าเริ่มสัปดาห์ที่วันจันทร์) ดังนี้

=MIN(Table3[วันที่เริ่ม])-WEEKDAY(MIN(Table3[วันที่เริ่ม]),2)+1
ตัวอย่าง gantt chart โครงการก่อสร้าง

จากนั้นช่องข้างๆ ก็เขียนว่าเอาช่องด้านซ้าย +7 ไปเรื่อยๆ เพื่อให้เพิ่มทีละสัปดาห์ แล้วลากยาวไปด้านขวา เพื่อถือว่าคือจุดเริ่มต้นของสัปดหา์นั้นๆ

Tips : ถ้าจะเพิ่มทีละเดือนให้ใช้ =EDATE(วันที่,1) เพื่อเพิ่มทีละ 1 เดือน ซึ่งถ้าเพิ่มทีละเดือนวันเริ่มต้นก็ควรเป็นเริ่มต้นเดือนด้วย

Tips2 : บางมีเราก็ใช้เป็น 1 คอลัมน์คือ 1 วันเลย จะได้ความละเอียดสูงสุด แล้วหดคอลัมน์ให้เล็กๆ แล้ว Group เป็น Week อีกทีก็ยังได้นะครับ

แต่ในที่นี้ผมจะแสดงข้อมูลเป็น 1 คอลัมน์ =1 Week ละกันครับ

ตัวอย่าง gantt chart โครงการก่อสร้าง

สร้าง Gantt Chart

Gantt Chart นั้นคือกราฟแท่งที่เอาไว้แสดงข้อมูลว่างานแต่ละงานนั้นเริ่มต้นเมื่อไหร่ จบเมื่อไหร่ ซึ่งจะช่วยให้เราเข้าใจภาพรวมของโครงการมากขึ้น ว่างานไหนต้องทำก่อนหลังยังไง และยังเห็นว่าช่วงเวลาเดียวกันนั้นมีงานเยอะขนาดไหนด้วย

ตัวอย่าง gantt chart โครงการก่อสร้าง
ตัวอย่าง Gantt Chart จาก https://www.projectengineer.net/3-simple-gantt-chart-examples/

Tips : นอกจากนี้ เรายังสามารถคำนวณ Critical Path หรือก็คืองานที่ถ้า Delay ปุ๊ปจะทำให้โครงการช้าขึ้นทันทีได้ด้วย แต่ผมขอไว้ทำให้ดูในบทความถัดๆ ไปละกันะครับ (ขอติดไว้ก่อนนะ เดี๋ยวจะซับซ้อนไป)

สำหรับบทความนี้ เราจะทำการ Allocate Budget ลงในตารางแต่ละช่อง แล้วใช้ Conditional Format Fill สีพื้นหลัง ให้มันออกมาเหมือน Gantt Chart นั่นเอง เรามาดูแต่ละขั้นตอนกันครับ

Allocate Budget

ต่อไปผมก็จะทำการ Allocate Budget ลงในตารางแต่ละช่อง โดยคำนวณจาก Portion ของระยะเวลาที่อยู่ในช่องนั้นหารด้วยระยะเวลาทั้งหมด

ดังนั้นเพื่อให้เห็นภาพ ผมจะคำนวณก่อนว่าแต่ละงานนั้นมีจำนวนวันในแต่ละช่องเท่าไหร่ ดังนี้ (หลักการคล้ายๆ การคำนวณเวลาในบทความนี้)

=MAX(MIN([@วันที่จบ],J$1)-MAX([@วันที่เริ่ม],I$1),0)

หมายเหตุ : ที่เขียน MAX เทียบกับ 0 เพื่อไม่ให้ค่าติดลบ

โดยที่เราจะใช้วิธี Copy Paste ไปด้านขวา แทนการลาก Fill Handle เพื่อที่ทำให้คอลัมน์ของ Table ไม่เลื่อนจากคอลัมน์วันที่เริ่ม และวันที่จบ (ถ้าใช้ Fill Handle การอ้างอิง Table จะเลื่อน)

ตัวอย่าง gantt chart โครงการก่อสร้าง

จากนั้นเราเอาตัวเลขที่ได้ไปหารด้วยระยะวเลาแต่ละ Task ดังนั้นสูตรจึงเป็นดังนี้

=MAX(MIN([@วันที่จบ],J$1)-MAX([@วันที่เริ่ม],I$1),0)/[@ระยะเวลา]

ซึ่งจะได้ สัดส่วนการ Allocate Budget แบบนี้

ตัวอย่าง gantt chart โครงการก่อสร้าง

แล้วเราค่อยเอา สัดส่วนไปคูณกับ งบประมาณ ก็จะได้งบประมาณราย Week ดังนี้ โดยที่หากรวมงบประมาณตลอดช่วงเวลาของ Task นั้นๆ ก็จะได้งบประมาณรวมของ Task นั้นๆ นั่นเอง แบบนี้แสดงว่าการ Allocate ของเราถูกต้องแล้ว

ตัวอย่าง gantt chart โครงการก่อสร้าง

Fill สี Gantt Chart

dเราจะเอา Style ของ Table เดิมที่เป็นลายๆ ออกไปซะ โดยเลือกให้เป็นสีแบบ None เพื่อที่เราจะใส่ Conditional Format ได้อย่างสวยงาม

ตัวอย่าง gantt chart โครงการก่อสร้าง

ให้เราใส่ Conditional Format ว่าถ้าค่ามากกว่า 0 ให้ Fill สี Background และให้ทำสี Font ให้สีเป็นสีเดียวกับ Background ด้วย (หรือจะทำให้เข้มกว่า BG นิดๆ ก็ได้จะได้เห็นเลข แต่ไม่น่าเกลียด)

ส่วนถ้าเป็นเลข 0 ก็ให้เปลี่ยนสี Font เป็นสีขาวซะ จะได้มองไม่เห็น

ตัวอย่าง gantt chart โครงการก่อสร้าง

นี่คือส่วนของ Gantt Chart ก็น่าจะถือว่าเสร็จแล้วล่ะ ต่อไปจะเป็นการทำ S-Curve

สร้าง S-Curve

การสร้าง S-Curve จริงๆ แล้วไม่มีอะไรมากไปกว่าการ Plot กราฟเส้น โดยให้แกน x เป็นวันที่ ส่วนแกน y เป็น %งานสะสมในแต่ละช่วงเวลา ซึ่งโดยทั่วไป %งานสะสมจะพุ่งขึ้นเร็วในช่วงกลางๆ (เพราะทำหลาย Task พร้อมกันมากที่สุด) ทำให้กราฟดูเป็นรูปตัว S นั่นเอง แต่ในชีวิตจริงจะเหมือน S แค่ไหนก็เป็นอีกเรื่องนึงนะ 555

ตัวอย่าง gantt chart โครงการก่อสร้าง
ตัวอย่าง S-Curve จาก https://www.ntaskmanager.com/blog/s-curve-in-project-management/

โดยทั่วไป ในงานจริงๆ เรามักจะมีการ Plot S-Curve 2 เส้นเทียบกันนั่นคือ Planed vs Actual S-Curve เพื่อเทียบว่าโครงการของจริงเป็นไปตามแผนที่วางแผนไว้แค่ไหน? ซึ่งเราจะเอามิติไหนมาเทียบกันก็ได้ เช่น งบประมาณ vs ค่าใช้จ่ายจริง , วันที่วางแผน vs วันที่ใช้จริง, output ที่วางแผน vs output จริง เป็นต้น

ตัวอย่าง gantt chart โครงการก่อสร้าง
ตัวอย่าง S-Curve จาก https://www.ntaskmanager.com/blog/s-curve-in-project-management/

สำหรับในเคสของเรานั้น สำหรับการ Plan ผมจะใช้เรื่องของ Budget มาเป็นตัวแทน แต่สำหรับตัว Actual ผมขอไม่ทำให้ดูนะครับ เพราะก็แค่เป็นการทำ Gantt Chart อีกตารางนึงแค่นั้นเอง เพื่อนๆ น่าจะทำเองได้เนอะ

งั้นมาดูการทำ S-Curve ของตัว Budget กันดีกว่า

S-Curve ของ Budget

ซึ่งจะเห็นว่าจริงๆ แล้วสามารถใช้การ SUM Budget ในแต่ละ Week ธรรมดาๆ ก็ได้ แต่ในที่นี้ผมจะใช้ SUBTOTAL มาช่วย เพื่อให้สามารถเปลี่ยนผลรวมไปตามการ Filter ตารางได้ (เดี๋ยวผมจะใช้ Slicer กดเอา)

=SUBTOTAL(9,ProjectTable[Week1])
ตัวอย่าง gantt chart โครงการก่อสร้าง

จากนั้นให้ลาก Fill Handle ไปด้านขวาเพื่อให้มันเปลี่ยนคอลัมน์ในตารางไปเรื่อยๆ
จากนั้น งบประมาณสะสมสามารถใช้ SUM แบบ Lock จุดเริ่มต้นอย่างเดียวได้เลย

=SUM($I$2:I2)
ตัวอย่าง gantt chart โครงการก่อสร้าง

จากนั้นเราสร้าง %งบประมาณสะสมขึ้นมา แล้วทำการ plot กราฟเส้น ซะ โดยให้แกน x เป็นวันที่ แกน y เป็น %งบประมาณสะสม เป็นอันจบการสร้าง S-Curve ครับ

ตัวอย่าง gantt chart โครงการก่อสร้าง

เพิ่ม Slicer เพื่อให้มีลูกเล่นในการ Filter

เวลาเราทำข้อมูลเป็น Table แล้ว ใน Excel 2013 ขึ้นไปจะสามารถใช้ Slicer กับ Table ได้ด้วย เช่น ผมให้คนใช้งานสามารถกด Slicer เพื่อเลือกดูงานหลักที่สนใจก็ได้

ซึ่งพอกด Slicer แล้ว ตารางจะถูก Filter ซึ่งจะส่งผลให้การคำนวณจาก SUBTOTAL ลดลงไปกราฟก็เลยเปลี่ยนตามได้ด้วยนั่นเอง

ตัวอย่าง gantt chart โครงการก่อสร้าง

ปรับแต่งความสวยงาม

ถ้ากลัวคอลัมน์แคบไปจนมองไม่เห็น เราก็ปรับการ Alignment ให้เป็นแนวตั้งหรือเฉียงๆ หน่อยได้ รวมถึงจะปรับ Format ของวันที่ให้อ่านง่ายขึ้นก็ได้นะครับ

ถ้าตัว Fill มันดูทึบไป อาจตีกรอบบนล่างให้เป็นสีขาวก็ได้นะ

นอกจากนี้เรายังติ๊กเอา Filter Button ของ Table ออกได้ ในขณะที่ยังใช้ Slicer ได้อยู่เหมือนเดิมด้วยนะ จะได้ไม่ต้องมีปุ่มเกะกะ แต่ผมขอไม่ปรับอะไรเรื่องของความสวยงามมากแล้วกันนะ อิอิ

ตัวอย่าง gantt chart โครงการก่อสร้าง

สรุป

หวังว่าเทคนิคการทำ Project Management การบริหารโครงการ สร้าง Gantt Chart และ S-Curve ด้วย Excel ที่ผมนำเสนอในบทความนี้จะเป็นประโยชน์กับเพื่อนๆ นะครับ

แต่เดี๋ยวในตอนต่อไปผมจะใช้ Pivot Table + Power Query ทำ Gannt Chart ให้ดู ซึ่งจะสามารถสร้างรายงานที่ยืดหยุ่นกว่าเดิมได้มากเลยครับ รวมถึงเรื่องของการทำ Critical Path ด้วยเนอะ ใครอยากให้มีแง่มุมอื่นอีกก็สามารถบอกได้นะครับ

แชร์ความรู้ให้เพื่อนๆ ของคุณ