ริษัทแห่งหนึ่งมีนโยบายให้ฝ่ายบุคคล ดำเนินการประเมินผลพนักงานใหม่แต่ละคน ในช่วงประมาณ 15 วันก่อนครบกำหนดทดลองงาน แต่เนื่องจากในระยะเวลาใกล้เคียงกันนั้น มีพนักงานเข้าใหม่จำนวนมาก ฝ่ายบุคคลเกรงว่าจะตรวจสอบไม่ทั่วถ้วน จึงต้องการสร้างตารางเตือนด้วย Excel ดังรูปที่ 1 โดยมีเงื่อนไขหลักๆ ดังนี้
รูปที่ 1 ตารางแจ้งเตือนวันครบกำหนดทดลองงาน เงื่อนไขการทำงาน
[LIST=1][*]
วันครบทดลองงาน คือ วันเริ่มงาน + 119 วัน (รวมเป็น 120 วัน)[*]ถ้าวันครบทดลองงาน
"ตรงกับ" วันปัจจุบัน ให้แสดงคำว่า
?ครบแล้ว?หรือ
?ครบวันนี้? ในคอลัมน์สถานะ ด้วยอักษรสีแดงตัวหนา[*]ถ้าวันปัจจุบัน
"เลย" วันครบทดลองงานมาแล้ว ไม่ต้องแสดงข้อความใดๆ ในคอลัมน์สถานะ[*]ถ้าวันปัจจุบัน
"ยังไม่ถึง" วันครบทดลองงาน ให้แสดงจำนวนวันที่เหลือในคอลัมน์คำเตือน และ[LIST][*]
ถ้าเหลือเวลามากกว่า 15 วัน ให้แสดงคำว่า
?ช่วงทดลองงาน? ในคอลัมน์สถานะ[*]
ถ้าเหลือระยะเวลาไม่เกิน 15 วัน ให้แสดงคำว่า
?ช่วงประเมินผล? ในคอลัมน์สถานะ ด้วยอักษรสีขาวบนพื้นสีแดง พร้อมทั้งกำหนดรูปแบบเซลล์คำเตือน ให้เป็นอักษรสีขาวพื้นสีแดงด้วยเช่นกัน[/LIST] [/LIST]
อ่าน เงื่อนไขแล้วมึนหรือเปล่าครับ แค่รู้วันครบทดลองงานโดยอัตโนมัติก็นับว่าเก่งแล้ว นี่ยังมีข้อแม้อื่นๆ อีกเช่น การกำหนดรูปแบบตัวอักษร สีพื้น แล้วยังมีการตรวจสอบระยะเวลาอีกว่า ช่วงไหนคือช่วงทดลองงาน ช่วงไหนคือช่วงประเมินผล แต่หน้าที่ของเราผู้ใช้ 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 การกำหนดรูปแบบเซลล์ในคอลัมน์ ?สถานะ?
[LIST=1][*]เลือกช่วงเซลล์ H3:H10[*]คลิกเมนู
Format -> Conditional Formatting (รูปแบบ -> การจัดรูปแบบตามเงื่อนไข)[*]กำหนดเงื่อนไขแรก โดยเลือกเงื่อนไขเป็น
Formula is (สูตรคือ) [*]พิมพ์สูตรลงในช่องว่างทางขวา ดังนี้
=F$1=G3 (วันปัจจุบันเท่ากับวันเริ่มงาน)[*]คลิกปุ่ม
Format? (รูปแบบ..)[*]ตั้งค่ารูปแบบตัวอักษร ให้เป็นตัวหนา สีแดง[*]คลิกปุ่ม
OK (ตกลง) เพื่อกลับมาที่หน้าเดิม[*]เพิ่มเงื่อนไขที่สอง โดยการคลิกที่ปุ่ม
Add>> (เพิ่ม>>) ที่อยู่ด้านล่าง และเลือกเงื่อนไขเป็น
Formula is (สูตรคือ)[*]พิมพ์สูตรลงในช่องว่างทางขวา ดังนี้
=AND(G3-F$1<=15,G3-F$1>0)
(วันครบทดลองงาน ลบ วันปัจจุบัน น้อยกว่าหรือเท่ากับ 15 วัน และมีค่ามากกว่าศูนย์)[*]คลิกปุ่ม
Format? (รูปแบบ..)[*]ตั้งค่ารูปแบบตัวอักษร ให้เป็นสีขาว และตั้งค่าสีพื้นให้เป็นสีแดง[*]คลิกปุ่ม
OK (ตกลง) เพื่อกลับมาที่หน้าเดิมอีกครั้ง[*]คลิกปุ่ม
OK (ตกลง) อีกครั้ง เพื่อจบการตั้งค่า[/LIST]
การกำหนดรูปแบบเซลล์ในคอลัมน์ "คำเตือน" รูปที่ 5 การกำหนดรูปแบบเซลล์ในคอลัมน์ ?คำเตือน?
[LIST=1][*]เลือกช่วงเซลล์ I3:I10[*]คลิกเมนู
Format -> Conditional Formatting (รูปแบบ -> การจัดรูปแบบตามเงื่อนไข)[*]เลือกเงื่อนไขเป็น
Formula is (สูตรคือ)[*]พิมพ์สูตรลงในช่องว่างทางขวา ดังนี้
=AND( G3-F$1<=15,G3-F$1>0)
(วันครบทดลองงาน ลบ วันปัจจุบัน น้อยกว่าหรือเท่ากับ 15 วัน และมีค่ามากกว่าศูนย์)[*]คลิกปุ่ม
Format? (รูปแบบ..)[*]ตั้งค่ารูปแบบตัวอักษร ให้เป็นสีขาว และตั้งค่าสีพื้นให้เป็นสีแดง[*]คลิกปุ่ม
OK (ตกลง) เพื่อกลับมาที่หน้าเดิม[*]คลิกปุ่ม
OK (ตกลง) อีกครั้ง เพื่อจบการตั้งค่า จะได้รูปแบบผลลัพธ์ดังรูปที่ 1 นั่นเอง[/LIST]
ทดลอง ทำตามทีละขั้นตอนนะครับ หวังว่าจะมีประโยชน์สำหรับทุกท่าน ในการนำไปประยุกต์ใช้งานจริง ในเรื่องนี้มีข้อแม้อยู่เรื่องหนึ่ง เนื่องจากมีการใช้ฟังก์ชัน TODAY ในการคำนวณ ดังนั้น การบันทึกวันเข้างาน จึงต้องบันทึกในรูปแบบ ปี ค.ศ. เท่านั้น
(อ่านเพิ่มเติมเรื่อง การกำหนดรูปแบบเซลล์ประเภทวันที่ (Date format) ซึ่งผมเขียนไว้ก่อนหน้านี้แล้ว) และวันที่เข้างานนั้น จะต้องเป็นรูปแบบเดียวกันกับวันที่ ที่ได้จากฟังก์ชัน TODAY ซึ่งอาจจะเป็นรูปแบบ
d/m/yyyy หรือ
m/d/yyyy สุดแล้วแต่การตั้งค่าในแต่ละเครื่อง