จะตรวจสอบได้อย่างไรว่าวันที่เป็นวันหยุดราชการและนับวันยกเว้นวันหยุดใน Excel
ตัวอย่างเช่นคุณมีรายการวันที่และต้องการตรวจสอบว่ามีวันหยุดราชการอยู่ในรายการหรือไม่คุณจะทำอย่างไรให้เสร็จเร็ว ในบทความนี้ฉันจะแสดงวิธีคำนวณวันหยุดราชการของอเมริกาทั้งหมดภายในปีที่กำหนดจากนั้นตรวจสอบว่าวันที่เป็นวันหยุดราชการของอเมริกาใน Excel ได้อย่างง่ายดายหรือไม่
- ส่วนที่ 1: คำนวณวันหยุดราชการภายในปีที่กำหนดใน Excel
- ส่วนที่ 2: ตรวจสอบว่าวันที่เป็นวันหยุดราชการใน Excel หรือไม่
- ส่วนที่ 3: นับวันระหว่างสองวันยกเว้นวันหยุดสุดสัปดาห์และวันหยุดใน Excel
ส่วนที่ 1: คำนวณวันหยุดราชการกับปีที่กำหนดใน Excel
ก่อนตรวจสอบว่าวันใดเป็นวันหยุดราชการหรือไม่คุณต้องระบุวันหยุดทั้งหมดภายในปีที่กำหนดใน Excel ดังนั้นการเตรียมตารางดังภาพหน้าจอต่อไปนี้จะทำให้งานของคุณง่ายขึ้น
วันหยุดราชการของอเมริกามีสามประเภท:
(1) ประเภทแรกคือวันหยุดนักขัตฤกษ์ในวันที่กำหนดเช่นวันปีใหม่คือวันที่ 1 มกราคม เราสามารถคำนวณวันปีใหม่ได้อย่างง่ายดายด้วยสูตร = DATE (ระบุปี 1,1);
(2) ประเภทที่สองคือวันหยุดนักขัตฤกษ์ในวันธรรมดาเช่นวันประธานาธิบดี เราสามารถคำนวณวันประธานาธิบดีได้อย่างง่ายดายด้วยสูตร = DATE (ระบุปี 1,1) + 14 + CHOOSE (WEEKDAY (วันที่ (ระบุปี 1,1)) 1,0,6,5,4,3,2);
(3) และประเภทสุดท้ายคือวันแห่งความทรงจำเราสามารถคำนวณวันแห่งความทรงจำได้อย่างง่ายดายด้วยสูตร = วันที่ (ระบุปี 6,1) - วันธรรมดา (วันที่ (ระบุปี 6,6)).
ฉันแสดงรายการสูตรคำนวณวันหยุดราชการทั้งหมดในตารางต่อไปนี้ เพียงป้อนสูตรลงในเซลล์ที่เหมาะสมแล้วกด เข้าสู่ คีย์ทีละคน
| วันหยุด | เซลล์ | สูตร |
| วันปีใหม่ | C2 | = วันที่ (C1,1,1) |
| วัน Martin Luther King Jr. | C3 | = DATE (C1,1,1) + 14 + CHOOSE (WEEKDAY (DATE (C1,1,1)), 1,0,6,5,4,3,2) |
| วันประธานาธิบดี | C4 | = DATE (C1,2,1) + 14 + CHOOSE (WEEKDAY (DATE (C1,2,1)), 1,0,6,5,4,3,2) |
| วันที่ระลึก | C5 | = วันที่ (C1,6,1) -WEEKDAY (วันที่ (C1,6,6)) |
| วันประกาศอิสรภาพ | C6 | = วันที่ (C1,7,4) |
| วันแรงงาน | C7 | = วันที่ (C1,9,1) + ทางเลือก (WEEKDAY (DATE (C1,9,1)), 1,0,6,5,4,3,2) |
| วันโคลัมบัส | C8 | = DATE (C1,10,1) + 7 + CHOOSE (WEEKDAY (DATE (C1,10,1)), 1,0,6,5,4,3,2) |
| วันทหารผ่านศึก | C9 | = วันที่ (C1,11,11) |
| วันขอบคุณพระเจ้า | C10 | = DATE (C1,11,1) + 21 + CHOOSE (WEEKDAY (DATE (C1,11,1)), 4,3,2,1,0,6,5) |
| วันคริสต์มาส | C11 | = วันที่ (C1,12,25) |
หมายเหตุ ในสูตรในตารางด้านบน C1 คือเซลล์อ้างอิงที่ระบุตำแหน่งของปีที่กำหนด ในตัวอย่างของเราหมายถึงปี 2015 และคุณสามารถเปลี่ยนแปลงได้ตามความต้องการของคุณ
ด้วยสูตรเหล่านี้คุณสามารถคำนวณวันหยุดราชการกับปีที่กำหนดได้อย่างง่ายดาย ดูภาพหน้าจอด้านล่าง:
บันทึกช่วงเป็นรายการ AutoText (รูปแบบเซลล์และสูตรที่เหลืออยู่) เพื่อนำมาใช้ใหม่ในอนาคต
การอ้างอิงเซลล์และใช้สูตรในการคำนวณแต่ละวันหยุดจะต้องเป็นเรื่องที่น่าเบื่อมาก Kutools สำหรับ Excel มีวิธีแก้ปัญหาที่น่ารักของ ข้อความอัตโนมัติ ยูทิลิตี้เพื่อบันทึกช่วงเป็นรายการ AutoText ซึ่งยังคงเป็นรูปแบบเซลล์และสูตรในช่วง จากนั้นคุณจะใช้ช่วงนี้ซ้ำได้ในคลิกเดียว งานกลายเป็นเรื่องง่ายเพียงคลิกเดียวเพื่อแทรกตารางนี้และเปลี่ยนปีในตารางนี้!
Kutools สำหรับ Excel - เพิ่มประสิทธิภาพ Excel ด้วยเครื่องมือที่จำเป็นมากกว่า 300 รายการ เพลิดเพลินกับฟีเจอร์ทดลองใช้ฟรี 30 วันโดยไม่ต้องใช้บัตรเครดิต! Get It Now
ส่วนที่ 2: ตรวจสอบว่าวันที่เป็นวันหยุดราชการใน Excel หรือไม่
หลังจากระบุวันหยุดราชการทั้งหมดของปีที่ระบุแล้วเราสามารถตรวจสอบได้อย่างง่ายดายว่าวันนั้นเป็นวันหยุดราชการหรือไม่ด้วยสูตรใน Excel สมมติว่าคุณมีรายการวันที่ตามภาพหน้าจอต่อไปนี้และฉันจะแนะนำวิธีการทำอย่างง่ายดาย
เลือกเซลล์ว่างนอกเหนือจากรายการวันที่กล่าวว่าเซลล์ B18 ป้อนสูตร = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Holiday", "No") จากนั้นลาก Fill Handle ไปยังช่วงที่คุณต้องการ ดูภาพหน้าจอด้านบน:
หมายเหตุ / รายละเอียดเพิ่มเติม:
(1) ในสูตร = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Holiday", "No"), $ C $ 2: $ D $ 11 คือช่วงของวันหยุดราชการในบางปีและ A18 คือเซลล์ที่มีวันที่ที่คุณต้องการตรวจสอบว่าเป็นวันหยุดนักขัตฤกษ์หรือไม่และคุณสามารถเปลี่ยนแปลงได้ตามความต้องการของคุณ และสูตรนี้จะส่งคืน "วันหยุด" หากวันที่ระบุเป็นวันหยุดนักขัตฤกษ์และจะส่งกลับ "ไม่ใช่" หากไม่ใช่
(2) คุณยังสามารถใช้สูตรอาร์เรย์นี้ได้ = IF (หรือ ($ C $ 2: $ D $ 11 = A18), "Holiday", "NO") เพื่อตรวจสอบว่าวันที่ตรงกันเป็นวันหยุดหรือไม่
ส่วนที่ 3: นับวันระหว่างสองวันยกเว้นวันหยุดสุดสัปดาห์และวันหยุดใน Excel
ในส่วนที่ 1 เราได้ระบุวันหยุดทั้งหมดในปีหนึ่ง ๆ และตอนนี้วิธีนี้จะแนะนำให้คุณนับจำนวนวันยกเว้นวันหยุดสุดสัปดาห์และวันหยุดทั้งหมดในช่วงวันที่
เลือกเซลล์ว่างคุณจะส่งคืนจำนวนวันและป้อนสูตร = NETWORKDAYS (E1, E2, B2: B10) เข้าไปแล้วกดปุ่ม เข้าสู่ กุญแจ
หมายเหตุ: ในเซลล์ด้านบน E1 คือวันที่เริ่มต้นของช่วงวันที่ที่ระบุ E2 คือวันที่สิ้นสุดและ B2: B10 คือรายการวันหยุดที่เราคำนวณในส่วนที่ 1
ตอนนี้คุณจะได้รับจำนวนวันยกเว้นวันหยุดสุดสัปดาห์และวันหยุดในช่วงวันที่ที่ระบุ
คัดลอกสูตรแบบตรง / คงที่โดยไม่เปลี่ยนการอ้างอิงเซลล์ใน Excel
Kutools สำหรับ Excel สำเนาถูกต้อง ยูทิลิตี้สามารถช่วยให้คุณคัดลอกสูตรหลาย ๆ สูตรได้อย่างง่ายดายโดยไม่ต้องเปลี่ยนการอ้างอิงเซลล์ใน Excel ป้องกันการอ้างอิงเซลล์สัมพัทธ์ที่อัปเดตโดยอัตโนมัติ
Kutools สำหรับ Excel - เพิ่มประสิทธิภาพ Excel ด้วยเครื่องมือที่จำเป็นมากกว่า 300 รายการ เพลิดเพลินกับฟีเจอร์ทดลองใช้ฟรี 30 วันโดยไม่ต้องใช้บัตรเครดิต! Get It Now
สุดยอดเครื่องมือเพิ่มผลผลิตในสำนักงาน
เพิ่มพูนทักษะ Excel ของคุณด้วย Kutools สำหรับ Excel และสัมผัสประสิทธิภาพอย่างที่ไม่เคยมีมาก่อน Kutools สำหรับ Excel เสนอคุณสมบัติขั้นสูงมากกว่า 300 รายการเพื่อเพิ่มประสิทธิภาพและประหยัดเวลา คลิกที่นี่เพื่อรับคุณสมบัติที่คุณต้องการมากที่สุด...
แท็บ Office นำอินเทอร์เฟซแบบแท็บมาที่ Office และทำให้งานของคุณง่ายขึ้นมาก
- เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
- เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
- เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
