สร้างปฏิทินรายปีแบบไดนามิกใน Excel – คู่มือทีละขั้นตอน

Excel ปฏิทินอาจเป็นประโยชน์ค่อนข้างมาก หากคุณชอบวางแผนล่วงหน้าและมีตารางเวลาที่วุ่นวาย จดจำสิ่งนี้ไว้ ในบทความนี้ เราจะผ่านขั้นตอนพร้อมกับคำอธิบายเกี่ยวกับ วิธีสร้างปฏิทินรายปีใน Excel อย่างน่าสนใจ ปฏิทินนี้เป็นแบบไดนามิกและ โต้ตอบ (interactive) นั่นหมายถึง ปฏิทินอัปเดตตัวเองเมื่อคุณเปลี่ยนค่าปี นอกจากนี้ เราจะอภิปรายวิธีสร้างปฏิทินรายเดือนโดยใช้ Excel VBA ด้วย

คุณสามารถดาวน์โหลดสมุดงานฝึกฝนได้จากลิงก์ด้านล่าง

4 ขั้นตอนในการสร้างปฏิทินรายปีใน Excel

ตอนนี้ ก่อนที่เราจะวิเคราะห์อย่างละเอียดในบทช่วยสอนนี้ ให้ฉันแสดงให้คุณเห็นว่าผลลัพธ์สุดท้ายจะเป็นอย่างไร ที่นี่ เรามีปฏิทินรายปี โดย วันเสาร์ และ วันอาทิตย์ (วันหยุดสุดสัปดาห์) แสดงในสีส้มอ่อน ในขณะที่ วันหยุด (Holidays) ปรากฏในสีส้มสด

ที่นี่ เราใช้เวอร์ชัน Microsoft Excel 365 คุณอาจใช้เวอร์ชันอื่นตามสะดวกได้

📌 ขั้นตอนที่ 01: สร้างโครงร่างหลัก

• ในขั้นตอนแรก เราจะสร้างโครงร่างของปฏิทินโดยแบ่ง เดือน (Months) ไป 3 คอลัมน์และ 4 แถว
• ต่อไป ใส่ 7 วันใน สัปดาห์ (Week) และไฮไลท์ วันหยุดสุดสัปดาห์ (Weekends) ในกรณีนี้คือ วันเสาร์ และ วันอาทิตย์

อ่านเพิ่มเติม: วิธีสร้างปฏิทินรายสัปดาห์ใน Excel (3 วิธีที่เหมาะสม)

📌 ขั้นตอนที่ 02: ใส่ชื่อเดือน:

• ขั้นที่สอง ไปที่เซลล์ B4 >> พิมพ์หมายเลข 1 >> กดปุ่ม CTRL + 1 บนคีย์บอร์ด

ตอนนี้ จะเปิดกล่องโต้ตอบ Format Cells

• ใน البداية ไปที่แท็บ Number >> เลือกตัวเลือก Custom >> ในฟิลด์ Type ใส่ "January" (ภายในเครื่องหมายอัญประกาศ) >> สุดท้าย ไปที่แท็บ Font

• ต่อไป เลือก Bold-Italic Font style >> เลือก Color ที่นี่เราเลือกสีม่วง >> กดปุ่ม OK

• ตามลำดับ เลือกช่วงเซลล์ B4:H4 >> กด CTRL + 1 เพื่อไปที่หน้าต่าง Format Cells
• ตามมา ไปที่แท็บ Alignment >> ในส่วน Horizontal เลือกตัวเลือก Center Across Selection >> กดปุ่ม OK

สุดท้าย ควรจะแสดงชื่อเดือน January ตามที่แสดงในภาพด้านล่าง

• เช่นเดียวกัน ทำซ้ำขั้นตอนเดียวกันสำหรับเดือนอื่นๆ และผลลัพธ์ของคุณควรจะมีลักษณะเหมือนภาพต่อไปนี้

อ่านเพิ่มเติม: วิธีสร้างปฏิทินใน Excel โดยไม่ใช้เทมเพลต (2 ตัวอย่าง)

📌 ขั้นตอนที่ 03: ใช้ฟังก์ชัน Excel เพื่อทำปฏิทินแบบไดนามิก

• ขั้นที่สาม ไปที่เซลล์ B6 และใส่สูตรที่กำหนดไว้ด้านล่าง

=IF(MONTH(DATE($B$2,$B$4,1)+SEQUENCE(6,7)-WEEKDAY(DATE($B$2,$B$4,1),2))=$B$4,DATE($B$2,$B$4,1)+SEQUENCE(6,7)-WEEKDAY(DATE($B$2,$B$4,1),2),"")

ที่นี่ เซลล์ B2 และ B4 หมายถึง ปี 2022 และ เดือน ของ มกราคม ตามลำดับ

📃 หมายเหตุ: ฟังก์ชัน SEQUENCE พร้อมใช้งานใน Excel 365, Excel 2021 และ Excel สำหรับเว็บ โปรดตรวจสอบให้แน่ใจว่าคุณมีเวอร์ชันที่เข้ากันได้

• ตอนนี้ กดปุ่ม ENTER และสูตรจะเติมวันทั้งหมดสำหรับ เดือน ของ มกราคม

📃 หมายเหตุ: โปรดตรวจสอบให้แน่ใจว่าเปลี่ยนรูปแบบ Date เป็น dd เท่านั้น ในหน้าต่าง Format Cells โดยการกด CTRL + 1 บนคีย์บอร์ด

ในที่สุด ผลลัพธ์ควรจะมีลักษณะเหมือนภาพด้านล่าง

📌 ขั้นตอนที่ 04: แสดงวันหยุดในปฏิทิน:

• ในขั้นตอนที่สี่และขั้นสุดท้าย สร้าง รายการวันหยุด (List of the Holidays) ในชีตใหม่ ตามที่แสดงด้านล่าง

• จากนั้น เลือกเซลล์ B6:H11 >> ไปที่ดรอปดาวน์ Conditional Formatting >> เลือกตัวเลือก New Rule

ทันที ตัววิซาร์ด New Formatting Rule จะโผล่ขึ้นมา

• ต่อไป เลือกตัวเลือก Use a formula to determine which cells to format
• จากนั้น ใน Rule Description ใส่สูตรต่อไปนี้

=ISNUMBER(VLOOKUP(B6,Holidays!$C:$C,1,0))

ที่นี่ เซลล์ B6 ชี้ไปที่ วันจันทร์ ใน เดือน ของ มกราคม ในขณะที่ Holidays!$C:$C แสดงถึง วันที่ ในชีต Holidays

การแยกวิเคราะห์สูตร:

• VLOOKUP(B6,Holidays!$C:$C,1,0)) → ฟังก์ชัน VLOOKUP ค้นหาค่าในคอลัมน์แรกสุดของตาราง และคืนค่าในแถวเดียวกันจากคอลัมน์ที่คุณระบุ ที่นี่ B6 ( lookup_value argument) ถูกแมปจากอาร์เรย์ Holidays!$C:$C (table_array argument) ต่อไป 1 (col_index_num argument) แสดงถึงหมายเลขคอลัมน์ของค่าการค้นหา สุดท้าย 0 (range_lookup argument) หมายถึง Exact match ของค่าการค้นหา
  • ผลลัพธ์ → #N/A
• ISNUMBER(VLOOKUP(B6,Holidays!$C:$C,1,0)) → กลายเป็น
  • ISNUMBER(#N/A) → ฟังก์ชัน ISNUMBER ตรวจสอบว่าค่าเป็นตัวเลขหรือไม่ และคืนค่า TRUE หรือ FALSE ที่นี่ #N/A เป็น value argument และเนื่องจากไม่ใช่ตัวเลข ฟังก์ชันจึงคืนค่า FALSE
  • ผลลัพธ์ → FALSE

ตอนนี้ ควรจะไฮไลท์วันหยุด วันที่ 1 มกราคม ในสีส้มสด

ในลักษณะเดียวกัน ทำซ้ำกระบวนการเดียวกันสำหรับเดือนอื่นๆ และผลลัพธ์ของคุณควรจะมีลักษณะเหมือนภาพหน้าจอที่แสดงด้านล่าง

วิธีสร้างปฏิทินรายเดือน

ถ้าคุณต้องการ สร้างปฏิทินรายเดือน? วิธีต่อไปของเราจะช่วยคุณได้! ที่นี่ เราจะใช้ โค้ด VBA เพื่อทำงานอัตโนมัติให้เรา ดังนั้น มาดูการทำงานกัน

📌 ขั้นตอน:

• ขั้นแรกและสำคัญที่สุด นำทางไปที่แท็บ Developer >> คลิกปุ่ม Visual Basic

สิ่งนี้จะเปิด Visual Basic Editor ในหน้าต่างใหม่

• ในขั้นตอนต่อไป ไปที่แท็บ Insert >> เลือก Module

เพื่อความสะดวกในการอ้างอิง คุณสามารถคัดลอกโค้ดจากที่นี่และวางลงในหน้าต่างตามที่แสดงด้านล่าง

Sub CalendarMaker() 'This code was taken from extendoffice.com ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _ Scenarios:=False Application.ScreenUpdating = False On Error GoTo MyErrorTrap Range("a1:g14").Clear MyInput = InputBox("Type in Month and year for Calendar ") If MyInput = "" Then Exit Sub StartDay = DateValue(MyInput) If Day(StartDay) <> 1 Then StartDay = DateValue(Month(StartDay) & "/1/" & _ Year(StartDay)) End If Range("a1").NumberFormat = "mmmm yyyy" With Range("a1:g1") .HorizontalAlignment = xlCenterAcrossSelection .VerticalAlignment = xlCenter .Font.Size = 18 .Font.Bold = True .RowHeight = 35 End With With Range("a2:g2") .ColumnWidth = 11 .VerticalAlignment = xlCenter .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Orientation = xlHorizontal .Font.Size = 12 .Font.Bold = True .RowHeight = 20 End With Range("a2") = "Sunday" Range("b2") = "Monday" Range("c2") = "Tuesday" Range("d2") = "Wednesday" Range("e2") = "Thursday" Range("f2") = "Friday" Range("g2") = "Saturday" With Range("a3:g8") .HorizontalAlignment = xlRight .VerticalAlignment = xlTop .Font.Size = 18 .Font.Bold = True .RowHeight = 21 End With Range("a1").Value = Application.Text(MyInput, "mmmm yyyy") DayofWeek = Weekday(StartDay) CurYear = Year(StartDay) CurMonth = Month(StartDay) FinalDay = DateSerial(CurYear, CurMonth + 1, 1) Select Case DayofWeek Case 1 Range("a3").Value = 1 Case 2 Range("b3").Value = 1 Case 3 Range("c3").Value = 1 Case 4 Range("d3").Value = 1 Case 5 Range("e3").Value = 1 Case 6 Range("f3").Value = 1 Case 7 Range("g3").Value = 1 End Select For Each cell In Range("a3:g8") RowCell = cell.Row ColCell = cell.Column If cell.Column = 1 And cell.Row = 3 Then ElseIf cell.Column <> 1 Then If cell.Offset(0, -1).Value >= 1 Then cell.Value = cell.Offset(0, -1).Value + 1 If cell.Value > (FinalDay - StartDay) Then cell.Value = "" Exit For End If End If ElseIf cell.Row > 3 And cell.Column = 1 Then cell.Value = cell.Offset(-1, 6).Value + 1 If cell.Value > (FinalDay - StartDay) Then cell.Value = "" Exit For End If End If Next For x = 0 To 5 Range("A4").Offset(x * 2, 0).EntireRow.Insert With Range("A4:G4").Offset(x * 2, 0) .RowHeight = 65 .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .WrapText = True .Font.Size = 10 .Font.Bold = False .Locked = False End With With Range("A3").Offset(x * 2, 0).Resize(2, _ 7).Borders(xlLeft) .Weight = xlThick .ColorIndex = xlAutomatic End With With Range("A3").Offset(x * 2, 0).Resize(2, _ 7).Borders(xlRight) .Weight = xlThick .ColorIndex = xlAutomatic End With Range("A3").Offset(x * 2, 0).Resize(2, 7).BorderAround _ Weight:=xlThick, ColorIndex:=xlAutomatic Next If Range("A13").Value = "" Then Range("A13").Offset(0, 0) _ .Resize(2, 8).EntireRow.Delete ActiveWindow.DisplayGridlines = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True ActiveWindow.WindowState = xlMaximized ActiveWindow.ScrollRow = 1 Application.ScreenUpdating = True Exit Sub MyErrorTrap: MsgBox "You may not have entered your Month and Year correctly." _ & Chr(13) & "Spell the Month correctly" _ & " (or use 3 letter abbreviation)" _ & Chr(13) & "and 4 digits for the Year" MyInput = InputBox("Type in Month and year for Calendar") If MyInput = "" Then Exit Sub Resume End Sub

• ตามลำดับ ปิดหน้าต่าง VBA >> คลิกปุ่ม Macros

สิ่งนี้จะเปิดกล่องโต้ตอบ Macros

• ตามมา เลือกแมโคร CalendarMaker >> กดปุ่ม Run

ตอนนี้ จะเปิด กล่องป้อนข้อมูล (Input box) >> ใส่ ชื่อเดือน และ ปี ตามที่แสดงด้านล่าง

ตามมา ผลลัพธ์ควรจะมีลักษณะเหมือนภาพที่กำหนดไว้ด้านล่าง

สรุป

หวังว่าบทความนี้จะช่วยให้คุณเข้าใจวิธีสร้างปฏิทินรายปีใน Excel หากคุณมีคำถาม โปรดทิ้งความคิดเห็นด้านล่าง นอกจากนี้ หากคุณต้องการอ่านบทความแบบนี้เพิ่มเติม คุณสามารถเยี่ยมชมเว็บไซต์ของเรา ExcelDemy ผู้ให้บริการโซลูชัน Excel ครบครัน

บทความที่เกี่ยวข้อง

• วิธีสร้างปฏิทินว่างใน Excel (ดาวน์โหลดเทมเพลตฟรี)