ผมได้คำถามทาง Inbox ถึงวิธีการใช้ Excel สรุปตารางการใช้ห้องประชุม ว่าวันไหนมีประชุมช่วงไหนบ้าง โดยข้อมูลที่ได้คือเริ่มประชุมกี่โมง จบกี่โมง อยากรู้ว่าในช่วงเวลาไหนของวันไหน มีการใช้ห้องประชุมบ้าง โดยแบ่งช่วงทีละ 1 ชม. ผมคิดว่าคำถามน่าสนใจดี เลยขอเอามาทำเป็นบทความดีกว่าครับ
ในบทความนี้ผมจะขอเพิ่มความซับซ้อนเข้าไปเล็กน้อย ก็คือ จะให้สามารถมีได้หลายห้องประชุมด้วย จะได้เหมือนจริงมากขึ้นนะครับ
ใช้สูตรเพื่อเตรียมข้อมูลเบื้องต้น
ก่อนอื่นเลย เรามีข้อมูลดิบดังนี้
| ห้องประชุม | Start | Finish |
| 1 | 1/10/2019 5:25 | 1/10/2019 7:00 |
| 1 | 1/10/2019 7:25 | 1/10/2019 7:55 |
| 1 | 2/10/2019 7:20 | 2/10/2019 8:00 |
| 1 | 2/10/2019 9:20 | 2/10/2019 10:00 |
| 1 | 2/10/2019 10:20 | 2/10/2019 15:10 |
| 2 | 1/10/2019 6:00 | 1/10/2019 9:00 |
| 2 | 1/10/2019 7:05 | 1/10/2019 7:35 |
| 2 | 2/10/2019 9:00 | 2/10/2019 14:55 |
โดยที่ข้อมูลบันทึกเป็นวันที่และเวลาได้ถูกต้องแล้วตามการตั้งค่าใน control panel ของตัวเองซึ่งของผมตั้งเป็น วัน/เดือน/ปี
หากเราต้องการเวลาว่าประชุมไปกี่นาที นี่ง่ายมาก นั่นคือเอาเวลาจบ ลบ เริ่ม ซึ่งเลขที่ออกมาจะมีหน่วยเป็นวัน
หากเราอยากทำให้เลขเป็นหน่วยนาที เราก็เอาไปคูณ 24*60 แค่นั้นเอง
เดี๋ยวเราจะสร้างคอลัมน์ใหม่เพื่อแยกวันที่กับเวลาออกจากกัน โดยใช้ INT มาช่วยในการแยกวันดังนี้ครับ
และใช้ช่องเดิมลบ INT ก็จะได้เวลาครับ (เป็นวิธีคำนวณแบบคร่าวๆ ซึ่งจะผิดไปเล็กน้อยจากการคำนวณแบบ Floating Point ใน Excel แต่ไม่ต้องซีเรียสครับ)
ส่วนเวลาจบก็ทำเหมือนกัน
จากนั้นเพื่อไม่ให้งง เราจะเปลี่ยน Number Format เวลาเริ่มกับเวลาจบ ให้เป็นเวลาครับ
ต่อไปเราจะทำการแบ่งเวลาออกเป็นช่วงๆ ทุกชั่วโมง โดยสร้างคอลัมน์ใหม่ดังนี้ครับ โดยเขียนแค่ 0:00 ตัวเดียว แล้วลาก Fill Handle ไปทางขวาเลย (โดยเราจะถือว่า ช่อง 0:00 คือเริ่มที่ 0:00 นั่นคือเป็นช่วง 0:00-1:00 ละกันครับ)
ต่อไป ในช่อง H2 เราจะเขียนสูตร IF เพื่อเช็คว่ามีการใช้ห้องประชุมในช่อง H2 นั่นมั้ย? ถ้ามีเราจะใส่เลข 1 ถ้าไม่มีจะใส่เลข 0 ครับ
หลักการคิดก็คือ ต้องตรงทั้งสองเงื่อนไขนี้ ดังนั้นจะใช้ AND มาช่วย
- เวลาเริ่มต้องเกิดน้อยกว่าหรือเท่ากับเวลาจบช่องนั้น หรือให้น้อยกว่าเวลาเริ่มช่องถัดไป นั่นคือ F2<I1
- เวลาจบต้องมากกว่าหรือเท่ากับเวลาเริ่มช่องนั้น G2>=H1
ยกตัวอย่างเช่น ถ้าเริ่ม 10:20 จบ 15:10
- ถ้าเป็นตอน 9:00 ก็จะผิดตั้งแต่เงื่อนไขเวลาเริ่ม แปลว่าไม่ติ๊ก
- ถ้าเป็นตอน 10:00 จะตรงตามเงื่อนไขทั้งคู่ แปลว่าติ๊ก
ดังนั้นใน H2 เราจะใส่สูตรว่า =AND( F2<I1 , G2>=H1 )*1
(คูณ 1 เพื่อแปลง TRUE เป็น 1 FALSE เป็น 0)
และเนื่องจากเราจะ Copy ไปทั้งตาราง ดังนั้นจะต้องใส่ $ fix ตำแหน่งดังนี้
=AND( $F2<I$1 , $G2>=H$1 )*1
จากนั้น Copy ไปทั้งตาราง
จากนั้นใส่ Conditional Format -> Highlight Cell Rule -> Equal to 1 ให้เป็นสีแดง จะได้เห็นชัดๆ จะได้ตรวจสอบได้ง่ายหน่อยว่าแต่ละบรรทัดถูกต้องรึยัง?
Pivot Table ตรงๆ แบบไม่ใช้ Power Query
พอได้ข้อมูลดังนี้แล้วก็สามาถเอาเข้า PivotTable ได้เลย แต่ว่ามันจะยากเพราะต้องดึงข้อมูลเวลาแต่ละช่วงมาที่ Value ถึง 24 อันแบบนี้
จากนั้นลาก Sigma Value จาก column มาไว้ที่ Row จะได้เห็นชัดขึ้น
แล้วเอาห้องประชุมไว้ที่คอลัมน์แทน แล้วเอาวันนี้ไปไว้ที่ Filter/Slicer/Timeline ก็ได้ จากนั้นเปลี่ยน Report Layout เป็น Tabular Form จะได้เห็นชัดๆ
ซึ่งถามว่าผลลัพธ์วิธีข้างบนถูกมั้ย? มันก็ถูกนะ แต่จะเจ๋งกว่าถ้าช่วงเวลามันเป็น Field ที่เอามาพลิกเล่นไปมาได้ แทนที่จะเป็น Sum of 24 อันแบบนี้
ดังนั้นเพื่อเราจะใช้ Power Query ช่วยในการตบคอลัมน์เวลา 24 อัน ให้มารวมเป็นคอลัมน์เดียวแต่มีหลายบรรทัดแทน
ใช้ Power Query ตบข้อมูลให้เป็นลักษณะ Database มากขึ้น
ตอนแรกที่ผมลองทำ Get Data from Table/Range จากข้อมูลที่เราทำไว้ ปรากฏว่า ใน Power Query เครื่องผมมันน่าจะมี Bug ทำให้เห็นเลขทุกอย่างเป็น 0 แทนที่จะเป็น 1 กับ 0 (หากเราใช้วิธี Get Data from Table/Range ตรงๆ)
ดังนั้นผมเลยเปลี่ยนวิธี Get Data เล็กน้อย เป็นการ Get Data จากไฟล์ Excel ตัวเองแทน ซึ่งหากจะใช้วิธีนี้ต้อง Save ไฟล์ Excel ของเราไว้ซะก่อนครับ
จากนั้นไปที่ Get Data -> From File -> From Workbook แล้วเลือก File ตัวเอง -> เลือก Sheet1 ที่มีตารางที่เราทำ -> กด Transform Data
จะได้หน้าตาแบบนี้มา
จากนั้นลบ Step Changed Type ด้านขวาออกไปซะ
พอเลื่อนไปดูคอลัมน์ด้านขวาๆ ปรากฏว่ามันดันใส่วันที่มาในช่องเวลาของเราซะงั้น
แต่ไม่เป็นไรเดี๋ยวเราใช้วิธี Transpose เพื่อกำจัดมันได้ โดยให้ไปที่ Transform -> Transpose จะได้แบบนี้
จากนั้นเลือกคอลัมน์แรก แล้ว Split Column by Delimiter แล้วเลือกแบบใช้ space จะได้ดังนี้
จากนั้นเราจะทำการถมช่องว่างในคอลัมน์ 1.2 โดยการ Transpose กลับมาก่อน แล้วเลือกคอลัมน์ 1-7 แล้วFill Down
จากนั้นให้ลบแถวแรกออกไปซะ โดยไปที่ Home -> Remove Rows -> Remove Top Rows แล้วใส่เลข 1 เพราะจะเอาออก 1 แถว
ให้เราเลือก Use First Row as Header ไปจะได้แบบนี้
แล้วเรากดลบคอลัมน์เวลาเริ่มกับเวลาจบทิ้งไปซะ เพราะไม่ใช้แล้ว
แล้วเราก็เลือกคอลัมน์เวลา 0:00-23:00 ที่เตรียมไว้ จากนั้นไปที่ Transform -> Unpivot -> Unpivot Only Selected Column (ถ้าใครไม่มีก็เลือก Unpivot Column ธรรมดา)
จาก 24 คอลัมน์ จะมารวมหัวตารางกันในคอลัมน์เดียวชื่อ Attribute และมี Value แยกออกมาเป็นอีกคอลัมน์ดังนี้
เราแค่ดับเบิ้ลคลิ๊กเปลี่ยนชื่อคอลัมน์จาก Attribute เป็น “ช่วงเวลา” และ Value เป็น “Flagใช้งาน”
ตรงคอลัมน์ช่วงเวลา ให้เปลี่ยน Data Type เป็นเวลาซะ เวลาเรียงใน Pivot Table จะได้ไม่มีปัญหา
จากนั้นเราก็ Close & Load to… ออกมาให้เป็น Pivot Table เลย
สรุปข้อมูลด้วย Pivot Table หลังจากใช้ Power Query
แล้วเราก็ลาก Field ต่างๆ ตามใจชอบ เช่น ช่วงเวลาไว้ที่ Row, ห้องประชุมไว้ที่ Column, Flagใช้งานไปที่ Value และเอาวันไปเป็น Slicer หรือ Timeline ก็ได้ครับ
แบบนี้ก็จะตอบคำถามต่างๆ ได้ง่ายขึ้นว่า ช่วงที่มีคนใช้มากสุดคือ 7:00-8:00 อะไรแบบนี้ครับ
นี่คือประโยชน์มากๆ ของการใช้ Pivot Table ช่วยสรุปข้อมูล เพราะมันพลิกมุมมองไปมาได้สะดวก และการที่ทำให้ช่วงเวลารวมกันในคอลัมน์เดียวด้วย Power Query ได้ ก็ทำให้เราสามารถพลิกมุมมองเกี่ยวกับช่วงเวลาได้สะดวกขึ้นเช่นกันครับ
