ตารางเตือนวันครบกำหนดทดลองงาน

บริษัทแห่งหนึ่งมีนโยบายให้ฝ่ายบุคคล ดำเนินการประเมินผลพนักงานใหม่แต่ละคน ในช่วงประมาณ 15 วันก่อนครบกำหนดทดลองงาน แต่เนื่องจากในระยะเวลาใกล้เคียงกันนั้น มีพนักงานเข้าใหม่จำนวนมาก ฝ่ายบุคคลเกรงว่าจะตรวจสอบไม่ทั่วถ้วน จึงต้องการสร้างตารางเตือนด้วย Excel ดังรูปที่ 1 โดยมีเงื่อนไขหลักๆ ดังนี้

รูปที่ 1 ตารางแจ้งเตือนวันครบกำหนดทดลองงาน

เงื่อนไขการทำงาน

1. วันครบทดลองงาน คือ วันเริ่มงาน + 119 วัน (รวมเป็น 120 วัน)
2. ถ้าวันครบทดลองงาน "ตรงกับ" วันปัจจุบัน ให้แสดงคำว่า “ครบแล้ว” หรือ “ครบวันนี้” ในคอลัมน์สถานะ ด้วยอักษรสีแดงตัวหนา
3. ถ้าวันปัจจุบัน "เลย" วันครบทดลองงานมาแล้ว ไม่ต้องแสดงข้อความใดๆ ในคอลัมน์สถานะ
4. ถ้าวันปัจจุบัน "ยังไม่ถึง" วันครบทดลองงาน ให้แสดงจำนวนวันที่เหลือในคอลัมน์คำเตือน และ
   • ถ้าเหลือเวลามากกว่า 15 วัน ให้แสดงคำว่า “ช่วงทดลองงาน” ในคอลัมน์สถานะ
   • ถ้าเหลือระยะเวลาไม่เกิน 15 วัน ให้แสดงคำว่า “ช่วงประเมินผล” ในคอลัมน์สถานะ ด้วยอักษรสีขาวบนพื้นสีแดง พร้อมทั้งกำหนดรูปแบบเซลล์คำเตือน ให้เป็นอักษรสีขาวพื้นสีแดงด้วยเช่นกัน

อ่านเงื่อนไขแล้วมึนหรือเปล่าครับ แค่รู้วันครบทดลองงานโดยอัตโนมัติก็นับว่าเก่งแล้ว นี่ยังมีข้อแม้อื่นๆ อีกเช่น การกำหนดรูปแบบตัวอักษร สีพื้น แล้วยังมีการตรวจสอบระยะเวลาอีกว่า ช่วงไหนคือช่วงทดลองงาน ช่วงไหนคือช่วงประเมินผล แต่หน้าที่ของเราผู้ใช้ Excel แบบมืออาชีพคือ การตีโจทย์ให้แตก แล้วคิดวิธีการเขียนสูตรและเลือกใช้ฟังก์ชัน เพื่อให้ตารางข้อมูลพนักงานเข้าใหม่นี้ มีความเก่งในตัวเอง ดังนั้น เรามาช่วยกันออกแบบตารางนี้กันดีกว่าครับ


สร้างตารางการแจ้งเตือน

รูปที่ 2 ตารางแจ้งเตือนวันครบกำหนดทดลองงาน

ขอให้ลองทำตารางข้อมูล ดังตัวอย่างในรูปที่ 2 เพื่อจะได้ลองฝึกปฏิบัติไปพร้อมๆ กันกับที่ผมจะอธิบายแต่ละขั้นตอน
ก่อนอื่น...ที่เซลล์ F1 พิมพ์สูตร =TODAY()
เพื่อให้แสดงวันที่ปัจจุบันเสมอ (แต่...อย่าลืมตั้งวันที่ในเครื่องคอมพิวเตอร์ ให้ตรงกับวันปัจจุบันด้วยนะครับ เดี๋ยวจะยุ่ง)


หาวันครบทดลองงาน

จากที่โจทย์กำหนด วันครบทดลองงานคือ วันเริ่มงาน + 119 ดังนั้นในเซลล์ G3 พิมพ์ =F3+119 เสร็จแล้วก็คัดลอกสูตรจาก G3 ลงมาถึง G10


การแสดงข้อความเตือนในคอลัมน์ "คำเตือน"

โจทย์บอกว่า “ถ้าวันปัจจุบันยังไม่ถึงวันครบทดลองงาน ให้แสดงจำนวนวันที่เหลือในคอลัมน์คำเตือน” ซึ่งวันที่เหลือก็คือ อีกกี่วันจึงจะครบกำหนดทดลองงานนั่นเอง ดังนั้น โดยพื้นฐานแล้ว จำนวนวันที่เหลือ (I3) ก็คือ วันครบทดลองงาน (G3) – วันปัจจุบัน (F$1)

แต่หากพิจารณาต่อไปว่า ถ้าวันปัจจุบันยังไม่ถึงวันครบทดลองงาน เมื่อนำวันครบทดลองงานมาลบวันปัจจุบัน ก็จะได้เป็น “ค่าบวก”แต่ในทางกลับกัน ถ้าวันปัจจุบันเลยวันครบทดลองงานแล้ว ก็จะได้เป็น “ค่าลบ”

เราจึงต้องตั้งเงื่อนไขว่า "ถ้าวันปัจจุบันยังไม่ถึงวันครบทดลองงาน ก็ให้เอา วันครบทดลองงาน – วันปัจจุบัน แล้วแจ้งเตือนว่าเหลืออีกกี่วัน, แต่ถ้าวันปัจจุบันเท่ากับวันครบทดลองงานพอดี หรือเลยวันครบทดลองงานไปแล้ว ก็ไม่ต้องแจ้งเตือนใดๆ" ดังนั้นในเซลล์ I3 จึงเขียนสูตรดังนี้

=IF(F$1 < $G3,"เหลืออีก "&$G3-F$1&" วัน","") 

แล้วคัดลอกสูตรจาก I3 ลงมาถึง I10 


ผังแนวคิดการแสดงข้อความในคอลัมน์ "สถานะ"

รูปที่ 3 แผนผังแสดงแนวคิดในการแสดงข้อความในคอลัมน์ “สถานะ”

จากแผนผังจะเห็นว่า ในขั้นตอนแรกเราจะตรวจสอบดูก่อนว่า วันปัจจุบัน (F$1) เลยกำหนดครบทดลองงาน (G3) หรือยัง ซึ่งถ้าเลยมาแล้ว ก็ไม่ต้องแสดงข้อความใดๆ (หรือใครจะให้แสดงข้อความว่า “เกินวันแล้ว” ก็ได้เหมือนกัน)

ถ้าวันปัจจุบัน (F$1) ยังไม่เกินกำหนดครบทดลองงาน (G3) แต่ตรงกับวันครบทดลองงานพอดี (F$1=G3) ก็ให้แสดงคำว่า “ครบวันนี้”

ส่วนเงื่อนไขสุดท้ายคือ ถ้าวันปัจจุบัน (F$1) ยังไม่ถึงกำหนดทดลองงาน (G3) ให้ตรวจสอบเงื่อนไขเพิ่มเติมอีกว่า ยังเหลืออีกกี่วัน (G3-F$1) ซึ่งถ้าเหลือมากกว่า 15 วัน ให้แสดงข้อความว่า “ช่วงทดลองงาน” แต่ถ้าวันที่ยังเหลือนั้นน้อยกว่าหรือเท่ากับ 15 วันพอดี ก็ให้แสดงข้อความว่า “ช่วงประเมินผล”

รวมแล้วมีด้วยกันทั้งสิ้น 3 เงื่อนไข เราจึงใช้ฟังก์ชัน IF ซ้อนกัน 3 ชั้น โดยพิมพ์สูตรที่ H3 ดังนี้

=IF(F$1>G3,"",IF(F$1=G3,"ครบวันนี้",
IF(G3-F$1>15,"ช่วงทดลองงาน","ช่วงประเมินผล")))

แล้วคัดลอกสูตรจาก H3 ลงมาถึง H10


การกำหนดรูปแบบเซลล์ตามเงื่อนไข

ผมขอแนะนำ การกำหนดรูปแบบตามเงื่อนไข (Conditional Formatting) เพื่อนำมาประยุกต์ใช้ในหัวข้อนี้ เนื่องจากโจทย์กำหนดเงื่อนไขรูปแบบเซลล์ ในคอลัมน์สถานะไว้ว่า ถ้าแสดงข้อความว่า “ครบวันนี้” ให้รูปแบบอักษรเป็นตัวหนาสีแดง แต่ถ้าแสดงข้อความว่า “ช่วงประเมินผล” ให้รูปแบบอักษรเป็นสีขาวบนพื้นสีแดง ดังนั้นรูปแบบในคอลัมน์สถานะจึงมี 2 เงื่อนไข

ส่วนในคอลัมน์คำเตือนมีเงื่อนไขเดียวคือ ถ้าในคอลัมน์สถานะของบรรทัดนั้นๆ แสดงคำว่า “ช่วงประเมินผล” ก็ให้รูปแบบเซลล์ในคอลัมน์คำเตือนของบรรทัดนั้น เป็นอักษรสีขาวบนพื้นสีแดงเช่นกัน


การกำหนดรูปแบบเซลล์ในคอลัมน์ "สถานะ"

รูปที่ 4 การกำหนดรูปแบบเซลล์ในคอลัมน์ “สถานะ” 

1. เลือกช่วงเซลล์ H3:H10
2. คลิกเมนู Format -> Conditional Formatting (รูปแบบ -> การจัดรูปแบบตามเงื่อนไข)
3. กำหนดเงื่อนไขแรก โดยเลือกเงื่อนไขเป็น Formula is (สูตรคือ) 
4. พิมพ์สูตรลงในช่องว่างทางขวา ดังนี้
   =F$1=G3 (วันปัจจุบันเท่ากับวันเริ่มงาน)
5. คลิกปุ่ม Format… (รูปแบบ..)
6. ตั้งค่ารูปแบบตัวอักษร ให้เป็นตัวหนา สีแดง
7. คลิกปุ่ม OK (ตกลง) เพื่อกลับมาที่หน้าเดิม
8. เพิ่มเงื่อนไขที่สอง โดยการคลิกที่ปุ่ม Add>> (เพิ่ม>>) ที่อยู่ด้านล่าง และเลือกเงื่อนไขเป็น Formula is (สูตรคือ)
9. พิมพ์สูตรลงในช่องว่างทางขวา ดังนี้
   =AND(G3-F$1<=15,G3-F$1>0) 
   (วันครบทดลองงาน ลบ วันปัจจุบัน น้อยกว่าหรือเท่ากับ 15 วัน และมีค่ามากกว่าศูนย์)
10. คลิกปุ่ม Format… (รูปแบบ..)
11. ตั้งค่ารูปแบบตัวอักษร ให้เป็นสีขาว และตั้งค่าสีพื้นให้เป็นสีแดง
12. คลิกปุ่ม OK (ตกลง) เพื่อกลับมาที่หน้าเดิมอีกครั้ง
13. คลิกปุ่ม OK (ตกลง) อีกครั้ง เพื่อจบการตั้งค่า

การกำหนดรูปแบบเซลล์ในคอลัมน์ "คำเตือน"

รูปที่ 5 การกำหนดรูปแบบเซลล์ในคอลัมน์ “คำเตือน”

1. เลือกช่วงเซลล์ I3:I10
2. คลิกเมนู Format -> Conditional Formatting (รูปแบบ -> การจัดรูปแบบตามเงื่อนไข)
3. เลือกเงื่อนไขเป็น Formula is (สูตรคือ)
4. พิมพ์สูตรลงในช่องว่างทางขวา ดังนี้
   =AND( G3-F$1<=15,G3-F$1>0)(วันครบทดลองงาน ลบ วันปัจจุบัน น้อยกว่าหรือเท่ากับ 15 วัน และมีค่ามากกว่าศูนย์)
5. คลิกปุ่ม Format… (รูปแบบ..)
6. ตั้งค่ารูปแบบตัวอักษร ให้เป็นสีขาว และตั้งค่าสีพื้นให้เป็นสีแดง
7. คลิกปุ่ม OK (ตกลง) เพื่อกลับมาที่หน้าเดิม
8. คลิกปุ่ม OK (ตกลง) อีกครั้ง เพื่อจบการตั้งค่า จะได้รูปแบบผลลัพธ์ดังรูปที่ 1 นั่นเอง

ทดลองทำตามทีละขั้นตอนนะครับ หวังว่าจะมีประโยชน์สำหรับทุกท่าน ในการนำไปประยุกต์ใช้งานจริง ในเรื่องนี้มีข้อแม้อยู่เรื่องหนึ่ง เนื่องจากมีการใช้ฟังก์ชัน TODAY ในการคำนวณ ดังนั้น การบันทึกวันเข้างาน จึงต้องบันทึกในรูปแบบ ปี ค.ศ. เท่านั้น (อ่านเพิ่มเติมเรื่อง การกำหนดรูปแบบเซลล์ประเภทวันที่ (Date format) ซึ่งผมเขียนไว้ก่อนหน้านี้แล้ว) และวันที่เข้างานนั้น จะต้องเป็นรูปแบบเดียวกันกับวันที่ ที่ได้จากฟังก์ชัน TODAY ซึ่งอาจจะเป็นรูปแบบ d/m/yyyy หรือ m/d/yyyy สุดแล้วแต่การตั้งค่าในแต่ละเครื่อง