M150
07-04-2012, 00:35
พอดีเห็นน้องหลายคนถามถึงเรื่องเกี่ยวกับการคำนวนวันทดลองงาน ว่าทำงานใหม่เป็นเด็กฝึกงานแล้วจะต้องทำงานกันกี่วันถึงจะผ่านโปร หรือแม้กระทั่งน้องที่เรียนบัญชีหรือเขียนโปรแกรมก็มีถามกันมาบ้างเกี่ยวกับการทำสูตรใน Excel เกี่ยวกับ สูตรการคำนวณวันผ่านทดลองงาน หรือ ระบบคำนวณวันทดลองงาน น่าจะเป็นหัวข้อที่อาจารย์หลายท่านกำลังฮิตฮอตอยากให้นักเรียนนักศึกษาทำรายงานหรือโปรเจ็คเกี่ยวกับการคำนวณวันทดลองงานอันนี้กัน เลยลองหาเว็บที่สอนเกี่ยวกับการทำสูตรหรือเขียนโปรแกรมตัวนี้มาให้ชมนะครับ ลองดูครับว่าถูกใจหรือไม่อย่างไร ถ้าถูกใจอย่าลืมมาเล่าต่อกันด้วยหละคร้าบ โดยเนื้อหาดีๆ อันนี้จาก IT for HR (ความรู้ IT เพื่อนักบริหารทรัพยากรมนุษย์): ตารางเตือนวันครบกำหนดทดลองงาน (http://it-for-hr.blogspot.com/2010/09/blog-post.html) เลยคร้าบ
http://board.roigoo.com/attachment.php?attachmentid=2643&stc=1&d=1333733436
ริษัทแห่งหนึ่งมีนโยบายให้ฝ่ายบุคคล ดำเนินการประเมินผลพนักงานใหม่แต่ละคน ในช่วงประมาณ 15 วันก่อนครบกำหนดทดลองงาน แต่เนื่องจากในระยะเวลาใกล้เคียงกันนั้น มีพนักงานเข้าใหม่จำนวนมาก ฝ่ายบุคคลเกรงว่าจะตรวจสอบไม่ทั่วถ้วน จึงต้องการสร้างตารางเตือนด้วย Excel ดังรูปที่ 1 โดยมีเงื่อนไขหลักๆ ดังนี้
http://4.bp.blogspot.com/_P05B8JwD878/TIELadzD3fI/AAAAAAAAGDM/-HYPvLawKx4/s320/03-01-%E0%B8%84%E0%B8%A3%E0%B8%9A%E0%B8%97%E0%B8%94%E0%B8%A5%E0%B8%AD%E0%B8%87%E0%B8%87%E0%B8%B2%E0%B8%99.jpg (http://4.bp.blogspot.com/_P05B8JwD878/TIELadzD3fI/AAAAAAAAGDM/-HYPvLawKx4/s1600/03-01-%E0%B8%84%E0%B8%A3%E0%B8%9A%E0%B8%97%E0%B8%94%E0%B8%A5%E0%B8%AD%E0%B8%87%E0%B8%87%E0%B8%B2%E0%B8%99.jpg)
รูปที่ 1 ตารางแจ้งเตือนวันครบกำหนดทดลองงาน
เงื่อนไขการทำงาน
วันครบทดลองงาน คือ วันเริ่มงาน + 119 วัน (รวมเป็น 120 วัน)
ถ้าวันครบทดลองงาน "ตรงกับ" วันปัจจุบัน ให้แสดงคำว่า ?ครบแล้ว?หรือ ?ครบวันนี้? ในคอลัมน์สถานะ ด้วยอักษรสีแดงตัวหนา
ถ้าวันปัจจุบัน "เลย" วันครบทดลองงานมาแล้ว ไม่ต้องแสดงข้อความใดๆ ในคอลัมน์สถานะ
ถ้าวันปัจจุบัน "ยังไม่ถึง" วันครบทดลองงาน ให้แสดงจำนวนวันที่เหลือในคอลัมน์คำเตือน และ
ถ้าเหลือเวลามากกว่า 15 วัน ให้แสดงคำว่า ?ช่วงทดลองงาน? ในคอลัมน์สถานะ
ถ้าเหลือระยะเวลาไม่เกิน 15 วัน ให้แสดงคำว่า ?ช่วงประเมินผล? ในคอลัมน์สถานะ ด้วยอักษรสีขาวบนพื้นสีแดง พร้อมทั้งกำหนดรูปแบบเซลล์คำเตือน ให้เป็นอักษรสีขาวพื้นสีแดงด้วยเช่นกัน
อ่าน เงื่อนไขแล้วมึนหรือเปล่าครับ แค่รู้วันครบทดลองงานโดยอัตโนมัติก็นับว่าเก่งแล้ว นี่ยังมีข้อแม้อื่นๆ อีกเช่น การกำหนดรูปแบบตัวอักษร สีพื้น แล้วยังมีการตรวจสอบระยะเวลาอีกว่า ช่วงไหนคือช่วงทดลองงาน ช่วงไหนคือช่วงประเมินผล แต่หน้าที่ของเราผู้ใช้ Excel แบบมืออาชีพคือ การตีโจทย์ให้แตก แล้วคิดวิธีการเขียนสูตรและเลือกใช้ฟังก์ชัน เพื่อให้ตารางข้อมูลพนักงานเข้าใหม่นี้ มีความเก่งในตัวเอง ดังนั้น เรามาช่วยกันออกแบบตารางนี้กันดีกว่าครับ
สร้างตารางการแจ้งเตือน
http://1.bp.blogspot.com/_P05B8JwD878/TIELjfKUfkI/AAAAAAAAGDU/mvf-9Fp0Quc/s320/03-01-%E0%B8%84%E0%B8%A3%E0%B8%9A%E0%B8%97%E0%B8%94%E0%B8%A5%E0%B8%AD%E0%B8%87%E0%B8%87%E0%B8%B2%E0%B8%991.JPG (http://1.bp.blogspot.com/_P05B8JwD878/TIELjfKUfkI/AAAAAAAAGDU/mvf-9Fp0Quc/s1600/03-01-%E0%B8%84%E0%B8%A3%E0%B8%9A%E0%B8%97%E0%B8%94%E0%B8%A5%E0%B8%AD%E0%B8%87%E0%B8%87%E0%B8%B2%E0%B8%991.JPG)
รูปที่ 2 ตารางแจ้งเตือนวันครบกำหนดทดลองงาน
ขอให้ลองทำตารางข้อมูล ดังตัวอย่างในรูปที่ 2 เพื่อจะได้ลองฝึกปฏิบัติไปพร้อมๆ กันกับที่ผมจะอธิบายแต่ละขั้นตอน
ก่อนอื่น...ที่เซลล์ F1 พิมพ์สูตร =TODAY()
เพื่อให้แสดงวันที่ปัจจุบันเสมอ (แต่...อย่าลืมตั้งวันที่ในเครื่องคอมพิวเตอร์ ให้ตรงกับวันปัจจุบันด้วยนะครับ เดี๋ยวจะยุ่ง)
หาวันครบทดลองงาน
จากที่โจทย์กำหนด วันครบทดลองงานคือ วันเริ่มงาน + 119 ดังนั้นในเซลล์ G3 พิมพ์ =F3+119 เสร็จแล้วก็คัดลอกสูตรจาก G3 ลงมาถึง G10
การแสดงข้อความเตือนในคอลัมน์ "คำเตือน"
โจทย์บอกว่า ?ถ้าวันปัจจุบันยังไม่ถึงวันครบทดลองงาน ให้แสดงจำนวนวันที่เหลือในคอลัมน์คำเตือน? ซึ่งวันที่เหลือก็คือ อีกกี่วันจึงจะครบกำหนดทดลองงานนั่นเอง ดังนั้น โดยพื้นฐานแล้ว จำนวนวันที่เหลือ (I3) ก็คือ วันครบทดลองงาน (G3) ? วันปัจจุบัน (F$1)
แต่หากพิจารณาต่อไปว่า ถ้าวันปัจจุบันยังไม่ถึงวันครบทดลองงาน เมื่อนำวันครบทดลองงานมาลบวันปัจจุบัน ก็จะได้เป็น ?ค่าบวก? แต่ในทางกลับกัน ถ้าวันปัจจุบันเลยวันครบทดลองงานแล้ว ก็จะได้เป็น ?ค่าลบ?
เราจึงต้องตั้งเงื่อนไขว่า "ถ้าวันปัจจุบันยังไม่ถึงวันครบทดลองงาน ก็ให้เอา วันครบทดลองงาน ? วันปัจจุบัน แล้วแจ้งเตือนว่าเหลืออีกกี่วัน, แต่ถ้าวันปัจจุบันเท่ากับวันครบทดลองงานพอดี หรือเลยวันครบทดลองงานไปแล้ว ก็ไม่ต้องแจ้งเตือนใดๆ" ดังนั้นในเซลล์ I3 จึงเขียนสูตรดังนี้
=IF(F$1 < $G3,"เหลืออีก "&$G3-F$1&" วัน","")
แล้วคัดลอกสูตรจาก I3 ลงมาถึง I10
ผังแนวคิดการแสดงข้อความในคอลัมน์ "สถานะ"
http://2.bp.blogspot.com/_P05B8JwD878/TIELqDNecgI/AAAAAAAAGDc/LpL9F6xfFHY/s320/03-02-%E0%B8%84%E0%B8%A3%E0%B8%9A%E0%B8%97%E0%B8%94%E0%B8%A5%E0%B8%AD%E0%B8%87%E0%B8%87%E0%B8%B2%E0%B8%99.jpg (http://2.bp.blogspot.com/_P05B8JwD878/TIELqDNecgI/AAAAAAAAGDc/LpL9F6xfFHY/s1600/03-02-%E0%B8%84%E0%B8%A3%E0%B8%9A%E0%B8%97%E0%B8%94%E0%B8%A5%E0%B8%AD%E0%B8%87%E0%B8%87%E0%B8%B2%E0%B8%99.jpg)
รูปที่ 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 เงื่อนไข
ส่วนในคอลัมน์คำเตือนมี เงื่อนไขเดียวคือ ถ้าในคอลัมน์สถานะของบรรทัดนั้นๆ แสดงคำว่า ?ช่วงประเมินผล? ก็ให้รูปแบบเซลล์ในคอลัมน์คำเตือนของบรรทัดนั้น เป็นอักษรสีขาวบนพื้นสีแดงเช่นกัน
การกำหนดรูปแบบเซลล์ในคอลัมน์ "สถานะ"
http://1.bp.blogspot.com/_P05B8JwD878/TIELw40qeLI/AAAAAAAAGDk/1RDAXPkYo8c/s320/03-03-%E0%B8%81%E0%B8%B2%E0%B8%A3%E0%B8%81%E0%B8%B3%E0%B8%AB%E0%B8%99%E0%B8%94%E0%B8%A3%E0%B8%B9%E0%B8%9B%E0%B9%81%E0%B8%9A%E0%B8%9A.jpg (http://1.bp.blogspot.com/_P05B8JwD878/TIELw40qeLI/AAAAAAAAGDk/1RDAXPkYo8c/s1600/03-03-%E0%B8%81%E0%B8%B2%E0%B8%A3%E0%B8%81%E0%B8%B3%E0%B8%AB%E0%B8%99%E0%B8%94%E0%B8%A3%E0%B8%B9%E0%B8%9B%E0%B9%81%E0%B8%9A%E0%B8%9A.jpg)
รูปที่ 4 การกำหนดรูปแบบเซลล์ในคอลัมน์ ?สถานะ?
เลือกช่วงเซลล์ 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 (ตกลง) อีกครั้ง เพื่อจบการตั้งค่า
การกำหนดรูปแบบเซลล์ในคอลัมน์ "คำเตือน"
http://1.bp.blogspot.com/_P05B8JwD878/TIEL2H3N07I/AAAAAAAAGDs/HrhVWjIJQVQ/s320/03-04-%E0%B8%81%E0%B8%B2%E0%B8%A3%E0%B8%81%E0%B8%B3%E0%B8%AB%E0%B8%99%E0%B8%94%E0%B8%A3%E0%B8%B9%E0%B8%9B%E0%B9%81%E0%B8%9A%E0%B8%9A.jpg (http://1.bp.blogspot.com/_P05B8JwD878/TIEL2H3N07I/AAAAAAAAGDs/HrhVWjIJQVQ/s1600/03-04-%E0%B8%81%E0%B8%B2%E0%B8%A3%E0%B8%81%E0%B8%B3%E0%B8%AB%E0%B8%99%E0%B8%94%E0%B8%A3%E0%B8%B9%E0%B8%9B%E0%B9%81%E0%B8%9A%E0%B8%9A.jpg)
รูปที่ 5 การกำหนดรูปแบบเซลล์ในคอลัมน์ ?คำเตือน?
เลือกช่วงเซลล์ I3:I10
คลิกเมนู Format -> Conditional Formatting (รูปแบบ -> การจัดรูปแบบตามเงื่อนไข)
เลือกเงื่อนไขเป็น Formula is (สูตรคือ)
พิมพ์สูตรลงในช่องว่างทางขวา ดังนี้
=AND( G3-F$1<=15,G3-F$1>0)
(วันครบทดลองงาน ลบ วันปัจจุบัน น้อยกว่าหรือเท่ากับ 15 วัน และมีค่ามากกว่าศูนย์)
คลิกปุ่ม Format? (รูปแบบ..)
ตั้งค่ารูปแบบตัวอักษร ให้เป็นสีขาว และตั้งค่าสีพื้นให้เป็นสีแดง
คลิกปุ่ม OK (ตกลง) เพื่อกลับมาที่หน้าเดิม
คลิกปุ่ม OK (ตกลง) อีกครั้ง เพื่อจบการตั้งค่า จะได้รูปแบบผลลัพธ์ดังรูปที่ 1 นั่นเอง
ทดลอง ทำตามทีละขั้นตอนนะครับ หวังว่าจะมีประโยชน์สำหรับทุกท่าน ในการนำไปประยุกต์ใช้งานจริง ในเรื่องนี้มีข้อแม้อยู่เรื่องหนึ่ง เนื่องจากมีการใช้ฟังก์ชัน TODAY ในการคำนวณ ดังนั้น การบันทึกวันเข้างาน จึงต้องบันทึกในรูปแบบ ปี ค.ศ. เท่านั้น (อ่านเพิ่มเติมเรื่อง การกำหนดรูปแบบเซลล์ประเภทวันที่ (Date format) (http://it-for-hr.blogspot.com/2010/05/date-format.html) ซึ่งผมเขียนไว้ก่อนหน้านี้แล้ว) และวันที่เข้างานนั้น จะต้องเป็นรูปแบบเดียวกันกับวันที่ ที่ได้จากฟังก์ชัน TODAY ซึ่งอาจจะเป็นรูปแบบ d/m/yyyy หรือ m/d/yyyy สุดแล้วแต่การตั้งค่าในแต่ละเครื่อง
http://board.roigoo.com/attachment.php?attachmentid=2643&stc=1&d=1333733436
ริษัทแห่งหนึ่งมีนโยบายให้ฝ่ายบุคคล ดำเนินการประเมินผลพนักงานใหม่แต่ละคน ในช่วงประมาณ 15 วันก่อนครบกำหนดทดลองงาน แต่เนื่องจากในระยะเวลาใกล้เคียงกันนั้น มีพนักงานเข้าใหม่จำนวนมาก ฝ่ายบุคคลเกรงว่าจะตรวจสอบไม่ทั่วถ้วน จึงต้องการสร้างตารางเตือนด้วย Excel ดังรูปที่ 1 โดยมีเงื่อนไขหลักๆ ดังนี้
http://4.bp.blogspot.com/_P05B8JwD878/TIELadzD3fI/AAAAAAAAGDM/-HYPvLawKx4/s320/03-01-%E0%B8%84%E0%B8%A3%E0%B8%9A%E0%B8%97%E0%B8%94%E0%B8%A5%E0%B8%AD%E0%B8%87%E0%B8%87%E0%B8%B2%E0%B8%99.jpg (http://4.bp.blogspot.com/_P05B8JwD878/TIELadzD3fI/AAAAAAAAGDM/-HYPvLawKx4/s1600/03-01-%E0%B8%84%E0%B8%A3%E0%B8%9A%E0%B8%97%E0%B8%94%E0%B8%A5%E0%B8%AD%E0%B8%87%E0%B8%87%E0%B8%B2%E0%B8%99.jpg)
รูปที่ 1 ตารางแจ้งเตือนวันครบกำหนดทดลองงาน
เงื่อนไขการทำงาน
วันครบทดลองงาน คือ วันเริ่มงาน + 119 วัน (รวมเป็น 120 วัน)
ถ้าวันครบทดลองงาน "ตรงกับ" วันปัจจุบัน ให้แสดงคำว่า ?ครบแล้ว?หรือ ?ครบวันนี้? ในคอลัมน์สถานะ ด้วยอักษรสีแดงตัวหนา
ถ้าวันปัจจุบัน "เลย" วันครบทดลองงานมาแล้ว ไม่ต้องแสดงข้อความใดๆ ในคอลัมน์สถานะ
ถ้าวันปัจจุบัน "ยังไม่ถึง" วันครบทดลองงาน ให้แสดงจำนวนวันที่เหลือในคอลัมน์คำเตือน และ
ถ้าเหลือเวลามากกว่า 15 วัน ให้แสดงคำว่า ?ช่วงทดลองงาน? ในคอลัมน์สถานะ
ถ้าเหลือระยะเวลาไม่เกิน 15 วัน ให้แสดงคำว่า ?ช่วงประเมินผล? ในคอลัมน์สถานะ ด้วยอักษรสีขาวบนพื้นสีแดง พร้อมทั้งกำหนดรูปแบบเซลล์คำเตือน ให้เป็นอักษรสีขาวพื้นสีแดงด้วยเช่นกัน
อ่าน เงื่อนไขแล้วมึนหรือเปล่าครับ แค่รู้วันครบทดลองงานโดยอัตโนมัติก็นับว่าเก่งแล้ว นี่ยังมีข้อแม้อื่นๆ อีกเช่น การกำหนดรูปแบบตัวอักษร สีพื้น แล้วยังมีการตรวจสอบระยะเวลาอีกว่า ช่วงไหนคือช่วงทดลองงาน ช่วงไหนคือช่วงประเมินผล แต่หน้าที่ของเราผู้ใช้ Excel แบบมืออาชีพคือ การตีโจทย์ให้แตก แล้วคิดวิธีการเขียนสูตรและเลือกใช้ฟังก์ชัน เพื่อให้ตารางข้อมูลพนักงานเข้าใหม่นี้ มีความเก่งในตัวเอง ดังนั้น เรามาช่วยกันออกแบบตารางนี้กันดีกว่าครับ
สร้างตารางการแจ้งเตือน
http://1.bp.blogspot.com/_P05B8JwD878/TIELjfKUfkI/AAAAAAAAGDU/mvf-9Fp0Quc/s320/03-01-%E0%B8%84%E0%B8%A3%E0%B8%9A%E0%B8%97%E0%B8%94%E0%B8%A5%E0%B8%AD%E0%B8%87%E0%B8%87%E0%B8%B2%E0%B8%991.JPG (http://1.bp.blogspot.com/_P05B8JwD878/TIELjfKUfkI/AAAAAAAAGDU/mvf-9Fp0Quc/s1600/03-01-%E0%B8%84%E0%B8%A3%E0%B8%9A%E0%B8%97%E0%B8%94%E0%B8%A5%E0%B8%AD%E0%B8%87%E0%B8%87%E0%B8%B2%E0%B8%991.JPG)
รูปที่ 2 ตารางแจ้งเตือนวันครบกำหนดทดลองงาน
ขอให้ลองทำตารางข้อมูล ดังตัวอย่างในรูปที่ 2 เพื่อจะได้ลองฝึกปฏิบัติไปพร้อมๆ กันกับที่ผมจะอธิบายแต่ละขั้นตอน
ก่อนอื่น...ที่เซลล์ F1 พิมพ์สูตร =TODAY()
เพื่อให้แสดงวันที่ปัจจุบันเสมอ (แต่...อย่าลืมตั้งวันที่ในเครื่องคอมพิวเตอร์ ให้ตรงกับวันปัจจุบันด้วยนะครับ เดี๋ยวจะยุ่ง)
หาวันครบทดลองงาน
จากที่โจทย์กำหนด วันครบทดลองงานคือ วันเริ่มงาน + 119 ดังนั้นในเซลล์ G3 พิมพ์ =F3+119 เสร็จแล้วก็คัดลอกสูตรจาก G3 ลงมาถึง G10
การแสดงข้อความเตือนในคอลัมน์ "คำเตือน"
โจทย์บอกว่า ?ถ้าวันปัจจุบันยังไม่ถึงวันครบทดลองงาน ให้แสดงจำนวนวันที่เหลือในคอลัมน์คำเตือน? ซึ่งวันที่เหลือก็คือ อีกกี่วันจึงจะครบกำหนดทดลองงานนั่นเอง ดังนั้น โดยพื้นฐานแล้ว จำนวนวันที่เหลือ (I3) ก็คือ วันครบทดลองงาน (G3) ? วันปัจจุบัน (F$1)
แต่หากพิจารณาต่อไปว่า ถ้าวันปัจจุบันยังไม่ถึงวันครบทดลองงาน เมื่อนำวันครบทดลองงานมาลบวันปัจจุบัน ก็จะได้เป็น ?ค่าบวก? แต่ในทางกลับกัน ถ้าวันปัจจุบันเลยวันครบทดลองงานแล้ว ก็จะได้เป็น ?ค่าลบ?
เราจึงต้องตั้งเงื่อนไขว่า "ถ้าวันปัจจุบันยังไม่ถึงวันครบทดลองงาน ก็ให้เอา วันครบทดลองงาน ? วันปัจจุบัน แล้วแจ้งเตือนว่าเหลืออีกกี่วัน, แต่ถ้าวันปัจจุบันเท่ากับวันครบทดลองงานพอดี หรือเลยวันครบทดลองงานไปแล้ว ก็ไม่ต้องแจ้งเตือนใดๆ" ดังนั้นในเซลล์ I3 จึงเขียนสูตรดังนี้
=IF(F$1 < $G3,"เหลืออีก "&$G3-F$1&" วัน","")
แล้วคัดลอกสูตรจาก I3 ลงมาถึง I10
ผังแนวคิดการแสดงข้อความในคอลัมน์ "สถานะ"
http://2.bp.blogspot.com/_P05B8JwD878/TIELqDNecgI/AAAAAAAAGDc/LpL9F6xfFHY/s320/03-02-%E0%B8%84%E0%B8%A3%E0%B8%9A%E0%B8%97%E0%B8%94%E0%B8%A5%E0%B8%AD%E0%B8%87%E0%B8%87%E0%B8%B2%E0%B8%99.jpg (http://2.bp.blogspot.com/_P05B8JwD878/TIELqDNecgI/AAAAAAAAGDc/LpL9F6xfFHY/s1600/03-02-%E0%B8%84%E0%B8%A3%E0%B8%9A%E0%B8%97%E0%B8%94%E0%B8%A5%E0%B8%AD%E0%B8%87%E0%B8%87%E0%B8%B2%E0%B8%99.jpg)
รูปที่ 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 เงื่อนไข
ส่วนในคอลัมน์คำเตือนมี เงื่อนไขเดียวคือ ถ้าในคอลัมน์สถานะของบรรทัดนั้นๆ แสดงคำว่า ?ช่วงประเมินผล? ก็ให้รูปแบบเซลล์ในคอลัมน์คำเตือนของบรรทัดนั้น เป็นอักษรสีขาวบนพื้นสีแดงเช่นกัน
การกำหนดรูปแบบเซลล์ในคอลัมน์ "สถานะ"
http://1.bp.blogspot.com/_P05B8JwD878/TIELw40qeLI/AAAAAAAAGDk/1RDAXPkYo8c/s320/03-03-%E0%B8%81%E0%B8%B2%E0%B8%A3%E0%B8%81%E0%B8%B3%E0%B8%AB%E0%B8%99%E0%B8%94%E0%B8%A3%E0%B8%B9%E0%B8%9B%E0%B9%81%E0%B8%9A%E0%B8%9A.jpg (http://1.bp.blogspot.com/_P05B8JwD878/TIELw40qeLI/AAAAAAAAGDk/1RDAXPkYo8c/s1600/03-03-%E0%B8%81%E0%B8%B2%E0%B8%A3%E0%B8%81%E0%B8%B3%E0%B8%AB%E0%B8%99%E0%B8%94%E0%B8%A3%E0%B8%B9%E0%B8%9B%E0%B9%81%E0%B8%9A%E0%B8%9A.jpg)
รูปที่ 4 การกำหนดรูปแบบเซลล์ในคอลัมน์ ?สถานะ?
เลือกช่วงเซลล์ 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 (ตกลง) อีกครั้ง เพื่อจบการตั้งค่า
การกำหนดรูปแบบเซลล์ในคอลัมน์ "คำเตือน"
http://1.bp.blogspot.com/_P05B8JwD878/TIEL2H3N07I/AAAAAAAAGDs/HrhVWjIJQVQ/s320/03-04-%E0%B8%81%E0%B8%B2%E0%B8%A3%E0%B8%81%E0%B8%B3%E0%B8%AB%E0%B8%99%E0%B8%94%E0%B8%A3%E0%B8%B9%E0%B8%9B%E0%B9%81%E0%B8%9A%E0%B8%9A.jpg (http://1.bp.blogspot.com/_P05B8JwD878/TIEL2H3N07I/AAAAAAAAGDs/HrhVWjIJQVQ/s1600/03-04-%E0%B8%81%E0%B8%B2%E0%B8%A3%E0%B8%81%E0%B8%B3%E0%B8%AB%E0%B8%99%E0%B8%94%E0%B8%A3%E0%B8%B9%E0%B8%9B%E0%B9%81%E0%B8%9A%E0%B8%9A.jpg)
รูปที่ 5 การกำหนดรูปแบบเซลล์ในคอลัมน์ ?คำเตือน?
เลือกช่วงเซลล์ I3:I10
คลิกเมนู Format -> Conditional Formatting (รูปแบบ -> การจัดรูปแบบตามเงื่อนไข)
เลือกเงื่อนไขเป็น Formula is (สูตรคือ)
พิมพ์สูตรลงในช่องว่างทางขวา ดังนี้
=AND( G3-F$1<=15,G3-F$1>0)
(วันครบทดลองงาน ลบ วันปัจจุบัน น้อยกว่าหรือเท่ากับ 15 วัน และมีค่ามากกว่าศูนย์)
คลิกปุ่ม Format? (รูปแบบ..)
ตั้งค่ารูปแบบตัวอักษร ให้เป็นสีขาว และตั้งค่าสีพื้นให้เป็นสีแดง
คลิกปุ่ม OK (ตกลง) เพื่อกลับมาที่หน้าเดิม
คลิกปุ่ม OK (ตกลง) อีกครั้ง เพื่อจบการตั้งค่า จะได้รูปแบบผลลัพธ์ดังรูปที่ 1 นั่นเอง
ทดลอง ทำตามทีละขั้นตอนนะครับ หวังว่าจะมีประโยชน์สำหรับทุกท่าน ในการนำไปประยุกต์ใช้งานจริง ในเรื่องนี้มีข้อแม้อยู่เรื่องหนึ่ง เนื่องจากมีการใช้ฟังก์ชัน TODAY ในการคำนวณ ดังนั้น การบันทึกวันเข้างาน จึงต้องบันทึกในรูปแบบ ปี ค.ศ. เท่านั้น (อ่านเพิ่มเติมเรื่อง การกำหนดรูปแบบเซลล์ประเภทวันที่ (Date format) (http://it-for-hr.blogspot.com/2010/05/date-format.html) ซึ่งผมเขียนไว้ก่อนหน้านี้แล้ว) และวันที่เข้างานนั้น จะต้องเป็นรูปแบบเดียวกันกับวันที่ ที่ได้จากฟังก์ชัน TODAY ซึ่งอาจจะเป็นรูปแบบ d/m/yyyy หรือ m/d/yyyy สุดแล้วแต่การตั้งค่าในแต่ละเครื่อง