Excel Calendar (2)

ใส่วันหยุดให้ปฏิทิน ด้วย Conditional Formatting

ปกติเมื่อเราต้องการจะจัดรูปแบบให้ cell ใดๆใน Excel สามารถทำได้โดยเลือก cell แล้วเลือกเมนู Format หรือคลิ๊กขวาแล้วเลือกเมนูลัด Format Cells  หรือกด shortcut ด้วยการกดปุ่ม Ctrl+1 (กดปุ่ม Ctrl ค้างไว้และกดปุ่มเลข 1) เรียกว่าเป็น fixed format แต่ถ้าเราต้องการจะจัดรูปแบบ cells โดยให้รูปแบบของ cell นั้นๆไม่คงที่ คือเปลี่ยนรูปแบบไปตามเงื่อนไขใดๆที่ต้องการ เช่น ในกรณีนี้ เราต้องการให้ cell วันที่เป็นวันหยุดพิเศษหรือวันหยุดนักขัตฤกษ์ มีสีพื้นเป็นสีอื่น (เช่น ส้ม) เราต้องใช้คุณสมบัติใน Excel ที่เรียกว่า Conditional Formatting หรือการจัดรูปแบบ แบบมีเงื่อนไข

จากตัวอย่างปฏิทินที่เราสร้างขึ้นในตอนที่แล้ว ให้เราเปิด sheet ที่ 2 ขึ้นมา แล้วใส่วันหยุดประจำปีลงไป สมมติว่าวันหยุดประจำปีมีดังในรูป

เพื่อให้สะดวกต่อการใช้งาน เราจะทำการตั้งชื่อ range หรือช่วงของ cell ที่เก็บข้อมูลวันหยุดนี้ไว้ โดยให้ทำการเลือกช่วง cell ตั้งแต่ A1 ถึง B21 แล้วเลือกเมนู FORMULAS-> Define Name ดังในรูป

จะปรากฏหน้าต่างขึ้นมาให้ทำการตั้งชื่อตรงช่อง Name: เป็น Holidays ตามในรูป

กลับมาที่ Sheet1 ทำการเลือก cell ตั้งแต่ B4:H9 แล้วเลือกเมนู HOME -> Conditional Formatting -> Manage Rules ตามในรูป

จะปรากฏหน้าต่าง Conditional Formatting Rules Manager ขึ้นมาดังในรูป

ทำการเลือก New Rule เพื่อสร้างเงื่อนไขใหม่

เลือก Use a formula to determine which cells to format และตรงช่องที่ให้ใส่สูตรให้พิมพ์สูตรดังนี้

=VLOOKUP(B4,HOLIDAYS,1,0)

เสร็จแล้วคลิ๊กที่ปุ่ม Format เพื่อเลือกจัดรูปแบบ cell ตามต้องการ เช่นใส่สีพื้นเป็นสีส้ม ตัวอักษรสีน้ำเงิน เสร็จแล้วคลิ๊ก OK ออกไปทีละขั้นจะได้ผลลัพธ์ของปฏิทินดังในรูป

ลองเปลี่ยนเดือนเป็นเดือนอื่นๆดู จะเห็นว่าวันที่เป็นวันหยุดพิเศษในเดือนนั้นจะเปลี่ยนสีตามไปดังรูป

อธิบายสูตร

Conditional Formatting เป็นเครื่องมือตัวหนึ่งใน Excel ที่เราสามารถนำมาประยุกต์ใช้งานใน 2 ลักษณะใหญ่ๆคือ

1.      เมื่อเราต้องการจัดรูปแบบ cell ใด cell หนึ่งให้มีลักษณะต่างๆกันไป ขึ้นอยู่กับค่าปัจจุบันใน cell นั้น

2.      เมื่อเราต้องการจัดรูปแบบ cell ใน range ให้มีลักษณะแตกต่างจาก cell อื่นๆ ขึ้นอยู่กับค่าใน cell นั้นๆ เช่นในกรณีนี้ เป็นต้น

การจัดรูปแบบแบบมีเงื่อนไข หรือ Conditional Formatting จะเห็นว่าเมื่อเราคลิ๊กที่เมนูนี้ จะมีตัวเลือกต่างๆคือ

-          Format all cells based on their values

-          Format only cells that contain

-          Format only top or bottom ranked values

-          Format only values that are above or below average

-          Format only unique or duplicate values

-          Use a formula to determine which cells to format

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

อย่างกรณีนี้เราเลือก format cell แบบมีเงื่อนไข โดยใช้สูตรว่า

=VLOOKUP(B4, Holidays, 1, 0)

อันนี้หมายความว่า ถ้าผลลัพธ์จากสูตรนี้เป็นจริง ก็จะทำการจัดรูปแบบตามที่ set ไว้ แต่ถ้าผลลัพธ์จากสูตรนี้เป็นเท็จ ก็จะไม่ทำการจัดรูปแบบตามที่ set ไว้

ทีนี้ เราอาศัยหลักที่ว่า ถ้า VLOOKUP หาพบว่าวันที่ใน B4 มีอยู่ใน Holidays ด้วย ก็จะได้ค่าเป็นวันที่นั้น ถ้าไม่พบจะมีค่าเป็น #N/A เนื่องจาก Excel มองว่าค่าตัวเลข 1 หรือมากกว่า คือค่า TRUE ซึ่งวันที่ใดๆจะมีค่าเป็นเลขจำนวนเต็มที่มากกว่า 1 อยู่แล้ว จึงเท่ากับว่าถ้า VLOOKUP หาค่าวันที่พบใน Holidays ก็จะทำการจัดรูปแบบ cell นั้นตามที่กำหนดไว้

สังเกตว่าเราเลือกช่วง cell ตั้งแต่ B4 ถึง H9 แต่ในสูตรเราใส่เฉพาะ B4 และใช้รูปแบบการอ้างถึง B4 ในรูปแบบ relative หมายความว่าสำหรับ cell อื่นๆ เช่น C4 Excel ก็จะทำการอ้างถึง cell C4 ในสูตรแทน เป็น VLOOKUP(C4, Holidays, 1, 0) และ cell อื่นๆก็เช่นกัน

Excel Calendar

สร้างปฏิทินอย่างง่าย ด้วยฟังก์ชัน DATE, WEEKDAY

ลองมาเรียนรู้การทำงานของข้อมูลในรูปแบบวัน-เวลา ใน Excel โดยการสร้างปฏิทินใช้เองอย่างง่ายกันดูครับ

เริ่มต้นจากเปิด File Excel ว่างๆขึ้นมา 1 ไฟล์ แล้วหาที่ว่างๆพิมพ์รูปแบบปฏิทินไว้ก่อนดังในรูปครับ

เสร็จแล้วที่ cell B4 ใส่สูตร =DATE($C$2-543,$F$2,2)-WEEKDAY(DATE($C$2-543,$F$2,1),2) จะได้ผลลัพธ์ดังในรูป

ต่อไปทำการจัดรูปแบบที่แสดงใน cell B4 ใหม่ โดยการเลือก cell B4 แล้วกด Ctrl+1 จะปรากฏหน้าต่าง Format Cellsขึ้นมา ที่หัวข้อ Categories เลือก Custom ตรงช่อง Type: แก้เหลือ D ตัวเดียวตามรูป

พอกด OK ใน cell B4 ก็จะแสดงแค่ 29

เสร็จแล้วไปที่ cell C4 ใส่สูตร =B4+1 แล้ว copy สูตรไปถึง H4 ตามรูป

ที่ cell B5 ใส่สูตร =B4+7 แล้ว copy สูตรไปถึง cell H9 เลย

ตอนนี้เราก็ได้ปฏิทินคร่าวๆออกมาแล้ว ต่อไปเราจะทำการตกแต่งรูปแบบอีกสักหน่อย โดยเลือก cell B4:H4 แล้วเลือกเมนู Conditional Formatting , New rule … ตามรูป

จะปรากฏหน้าต่าง New Formatting Rule มาให้ ตรงหัวข้อ Select a Rule Type เลือกเป็น Use a formula to determine which cells to format และตรงช่องที่ให้ใส่สูตร ใส่เป็น =DAY(B4)>7 แล้วคลิ๊กที่ปุ่ม Format จะปรากฏหน้าต่าง Format Cell มาให้ ทำการจัดรูปแบบสี Font ให้เป็นสีเทาหรือสีอ่อนๆตามต้องการ

กด OK ก็จะได้รูปแบบปฏิทินตามรูป

เช่นเดียวกัน เราไปทำการจัด conditional format ในบรรทัดที่ 8, 9 ใหม่ โดยเลือก cell B8:H9 แล้วเลือกเมนู Conditional Formatting เลือก New Rule แล้วตรงหัวข้อ Select a Rule Type เลือกเป็น Use a formula to determine which cells to format และตรงช่องที่ให้ใส่สูตร ใส่เป็น =DAY(B8)<15 แล้วคลิ๊กปุ่ม Format เพื่อจัดรูปแบบสีของ Font ให้เป็นสีอ่อนๆตามต้องการ เมื่อคลิ๊ก OK ออกมาจะได้รูปแบบตารางปฏิทินดังในรูป

ทดลองเปลี่ยนเดือนเป็นเดือนต่างๆดู แต่ในขั้นตอนนี้ยังต้องใส่เดือนเป็นตัวเลข 1-12 อยู่)