ช่วงวันที่ในฟังก์ชันตามเกณฑ์
ใช้ตรรกะระหว่างวันที่เพื่อรวมค่านับจำนวนภายในช่วงเวลา
ช่วงวันที่ในฟังก์ชันตามเกณฑ์ เป็นบทเรียน Excel Formulas Academy ฟรีบน CoddyKit นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน Excel Formulas Academy และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส Excel Formulas Academy มีบทเรียนทั้งหมด 4 บทเรียน
การกรองตามช่วงเวลา
รายงานจริงแทบทุกประเภทมักถามถึงช่วงเวลา เช่น ยอดขายในไตรมาสนี้ คำสั่งซื้อของเดือนที่แล้ว หรือการสมัครใช้งานระหว่างวันที่สองวัน ฟังก์ชันเกณฑ์ที่คุณได้เรียนรู้ ได้แก่ SUMIFS, COUNTIFS และ AVERAGEIFS สามารถจัดการเรื่องนี้ได้อย่างดีเมื่อคุณรู้วิธีระบุช่วงวันที่
เคล็ดลับคือ ช่วงวันที่แท้จริงแล้วประกอบด้วยเงื่อนไขสองข้อในคอลัมน์วันที่เดียวกัน ได้แก่ อยู่ในหรือหลังวันเริ่มต้น และอยู่ในหรือก่อนวันสิ้นสุด
วันที่เป็นเพียงตัวเลข
สเปรดชีตจัดเก็บวันที่เป็นเลขลำดับ โดยวันที่ 1 คือวันที่ 1 มกราคม 1900 (หรือ 1899 ในชีต) และแต่ละวันถัดไปจะเพิ่มขึ้นหนึ่งค่า ด้วยเหตุนี้ คุณจึงเปรียบเทียบวันที่ด้วย > และ < ได้เช่นเดียวกับตัวเลขทั่วไป
ดังนั้น หลังวันที่ 1 มกราคม จึงหมายถึง เลขลำดับที่มากกว่าเลขของวันที่นั้น นี่คือแนวคิดสำคัญที่ทำให้การกรองวันที่ทำงานได้
ผลรวมระหว่างวันที่สองวัน
สมมติว่าคอลัมน์ A เก็บข้อมูลวันที่สั่งซื้อ และคอลัมน์ C เก็บข้อมูลจำนวนเงิน หากต้องการรวมยอดขายของเดือนมกราคม 2024 ให้ใช้คอลัมน์ A สองครั้ง โดยกำหนดเงื่อนไขว่าอยู่ในหรือหลังวันที่ 1 มกราคม และอยู่ในหรือก่อนวันที่ 31 มกราคม
ใส่วันที่ไว้ในฟังก์ชัน DATE(year, month, day) เพื่อให้ตีความได้อย่างชัดเจนในรูปแบบวันที่ของแต่ละภูมิภาค เงื่อนไขทั้งสองทำงานแบบ AND จึงเลือกเฉพาะแถวของเดือนนั้น
=SUMIFS(C:C, A:A, ">="&DATE(2024,1,1), A:A, "<="&DATE(2024,1,31))เหตุผลที่ใช้ DATE() และสัญลักษณ์ &
คุณอาจลองพิมพ์ ">=1/1/2024" โดยตรง ซึ่งมักใช้งานได้ แต่ไม่เสถียร เพราะสเปรดชีตอาจอ่านค่าเป็นข้อความหรือตีความลำดับวันกับเดือนไม่ถูกต้อง
รูปแบบที่เชื่อถือได้คือ ">="&DATE(2024,1,1) ฟังก์ชัน DATE จะสร้างเลขลำดับของวันที่จริง และ & จะเชื่อมตัวดำเนินการเข้ากับค่านั้น วิธีนี้ใช้งานได้อย่างน่าเชื่อถือทั้งในเอ็กเซลและชีตของกูเกิล ไม่ว่าจะใช้รูปแบบภูมิภาคใด
=COUNTIFS(A:A, ">="&DATE(2024,1,1), A:A, "<="&DATE(2024,1,31))ดึงวันที่จากเซลล์
วันที่ที่กำหนดตายตัวเหมาะกับรายงานที่ไม่เปลี่ยนแปลง แต่รายงานที่ยืดหยุ่นควรอ่านวันเริ่มต้นและวันสิ้นสุดจากเซลล์ ใส่วันเริ่มต้นไว้ใน F1 และวันสิ้นสุดไว้ใน F2
ตอนนี้ช่วงเวลาจะเปลี่ยนตามชีต เพียงเปลี่ยน F1 หรือ F2 ยอดรวมทั้งหมดจะคำนวณใหม่ เช่นเคย ให้เชื่อมตัวดำเนินการเข้ากับเซลล์ด้วย & — อย่าใส่ชื่อเซลล์ไว้ภายในเครื่องหมายคำพูด
=SUMIFS(C:C, A:A, ">="&F1, A:A, "<="&F2)ช่วงที่มีขอบเขตด้านเดียว
บางครั้งคุณต้องการกำหนดขอบเขตเพียงด้านเดียว ทุกอย่างตั้งแต่วันที่กำหนดเป็นต้นไป ใช้เงื่อนไขมากกว่าหรือเท่ากับเพียงข้อเดียว ส่วน ทุกอย่างจนถึงวันที่กำหนด ใช้เงื่อนไขน้อยกว่าหรือเท่ากับเพียงข้อเดียว
วิธีนี้จะนับคำสั่งซื้อทั้งหมดที่เกิดขึ้นในหรือหลังวันที่ใน F1 โดยไม่มีขอบเขตด้านบน เหมาะสำหรับตัวชี้วัดลักษณะ "ยอดขายนับตั้งแต่เปิดตัว"
=COUNTIFS(A:A, ">="&F1)การรวมวันที่เข้ากับเงื่อนไขอื่น
เงื่อนไขวันที่สามารถใช้ร่วมกับเงื่อนไขข้อความและตัวเลขได้อย่างอิสระ หากต้องการรวมยอดขายของภูมิภาคตะวันออกภายในช่วงวันที่หนึ่ง ให้เพิ่มคู่เงื่อนไขภูมิภาคควบคู่ไปกับคู่เงื่อนไขวันที่ทั้งสองคู่
ลำดับไม่มีผลต่อผลลัพธ์ Excel จะประเมินเงื่อนไขทั้งหมดเป็น AND ชุดเดียว ในที่นี้มีคู่เกณฑ์สามคู่ที่ใช้ average_range หรือ sum_range เดียวกัน
=SUMIFS(C:C, B:B, "East", A:A, ">="&F1, A:A, "<="&F2)การกรองตามเดือนหรือปี
หากต้องการรวมข้อมูลทั้งปี ให้กำหนดขอบเขตเป็นวันแรกและวันสุดท้ายของปีนั้น ใช้ DATE กำหนดวันเริ่มต้นร่วมกับวันสิ้นสุดของช่วงเวลา
สำหรับเดือนเดียว ให้ใช้วันแรกของเดือนเป็นขอบเขตล่าง และวันแรกของเดือนถัดไปร่วมกับ "<" แบบเคร่งครัดเป็นขอบเขตบน วิธีนี้ช่วยหลีกเลี่ยงการกังวลว่าเดือนนั้นมี 28, 30 หรือ 31 วัน
=SUMIFS(C:C, A:A, ">="&DATE(2024,3,1), A:A, "<"&DATE(2024,4,1))ช่วงเวลาแบบเลื่อนด้วย TODAY
สำหรับรายงานแบบต่อเนื่อง ให้สร้างขอบเขตจาก TODAY() หากต้องการนับคำสั่งซื้อในช่วง 30 วันที่ผ่านมา ขอบเขตล่างคือวันนี้ลบ 30 และขอบเขตบนคือวันนี้
เนื่องจาก TODAY() จะอัปเดตทุกครั้งที่ชีตคำนวณใหม่ ช่วงเวลาจึงเลื่อนไปข้างหน้าโดยอัตโนมัติ ไม่ต้องแก้ไขด้วยตนเอง
=COUNTIFS(A:A, ">="&(TODAY()-30), A:A, "<="&TODAY())ระวังส่วนประกอบของเวลา
หากคอลัมน์วันที่เก็บวันที่และเวลาจริง ๆ เช่น การประทับเวลา แถวที่ระบุเป็นช่วงดึกของวันที่ 31 มกราคมจะมีเลขลำดับสูงกว่าค่าของวันที่ 31 มกราคมแบบเต็มวันอยู่เล็กน้อย ดังนั้นขอบเขต "<="&DATE(2024,1,31) จะไม่รวมแถวนั้น
วิธีที่ปลอดภัยคือใช้รูปแบบน้อยกว่าวันถัดไปอย่างเคร่งครัด: "<"&DATE(2024,2,1) ซึ่งจะครอบคลุมทุกช่วงเวลาภายในเดือนมกราคม รวมถึงเวลาประทับด้วย
=SUMIFS(C:C, A:A, ">="&DATE(2024,1,1), A:A, "<"&DATE(2024,2,1))การหาค่าเฉลี่ยภายในช่วงเวลา
รูปแบบช่วงวันที่เดียวกันนี้ใช้กับ AVERAGEIFS ได้เช่นกัน หากต้องการหาค่าเฉลี่ยมูลค่าคำสั่งซื้อภายในช่วงเวลา ให้ใช้คอลัมน์จำนวนเงินเป็น average_range แล้วเพิ่มเงื่อนไขวันที่สองข้อในคอลัมน์วันที่
อย่าลืมกับดักของช่วงเวลาที่ไม่มีข้อมูล: หากไม่มีคำสั่งซื้ออยู่ในช่วงวันที่ AVERAGEIFS จะคืนค่า #DIV/0! การครอบสูตรด้วย IFERROR ช่วยให้แดชบอร์ดที่กรองตามเวลาดูเรียบร้อย แม้บางช่วงจะไม่มีข้อมูล
=IFERROR(AVERAGEIFS(C:C, A:A, ">="&F1, A:A, "<="&F2), "No data")ตรวจสอบความเข้าใจ
ลองทบทวนวิธีที่เชื่อถือได้ในการเปรียบเทียบวันที่ภายในฟังก์ชันเกณฑ์
สรุป: ช่วงวันที่ในฟังก์ชันเกณฑ์
ตอนนี้คุณสามารถกรอง SUMIFS, COUNTIFS และ AVERAGEIFS ตามช่วงเวลาได้แล้ว:
- ช่วงวันที่คือเงื่อนไขสองข้อในคอลัมน์วันที่เดียวกัน (>= วันเริ่มต้น และ <= วันสิ้นสุด)
- สร้างวันที่ด้วย
DATE(y,m,d)และเชื่อมตัวดำเนินการโดยใช้">="& - สำหรับเดือน ให้ใช้ขอบเขตบนแบบน้อยกว่าวันถัดไปอย่างเคร่งครัด (
"<"&DATE(...)) เพื่อรองรับเวลาประทับ - ใช้
TODAY()สำหรับช่วงเวลาแบบเลื่อน เช่น 30 วันที่ผ่านมา
เนื้อหานี้ครอบคลุมฟังก์ชันตระกูล IFS ที่ใช้หลายเงื่อนไขแล้ว
=SUMIFS(C:C, B:B, F3, A:A, ">="&F1, A:A, "<"&F2)คำถามที่พบบ่อย
บทเรียน “ช่วงวันที่ในฟังก์ชันตามเกณฑ์” ฟรีหรือไม่
ใช่ — ข้อความเต็มของ “ช่วงวันที่ในฟังก์ชันตามเกณฑ์” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส Excel Formulas Academy ให้อัปเกรดเป็น CoddyKit PRO คอร์ส Excel Formulas Academy มีบทเรียนทั้งหมด 4 บทเรียน
คุณจะเรียนรู้อะไรในบทเรียน “ช่วงวันที่ในฟังก์ชันตามเกณฑ์”
ใช้ตรรกะระหว่างวันที่เพื่อรวมค่านับจำนวนภายในช่วงเวลา คุณปฏิบัติ Excel Formulas Academy ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน
คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน Excel Formulas Academy หรือไม่
ไม่จำเป็นต้องมีประสบการณ์มาก่อน Excel Formulas Academy บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน
บทเรียน “ช่วงวันที่ในฟังก์ชันตามเกณฑ์” ใช้เวลานานแค่ไหน
บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย
ฉันเขียนและรันโค้ดในบทเรียน Excel Formulas Academy นี้ได้ไหม
ได้ บทเรียน Excel Formulas Academy ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ
บทเรียนทั้งหมดในหลักสูตรนี้
- รวมค่าตามหลายเงื่อนไขด้วย SUMIFS
- นับตามหลายเงื่อนไขด้วย COUNTIFS
- หาค่าเฉลี่ยตามหลายเงื่อนไขด้วย AVERAGEIFS
- ช่วงวันที่ในฟังก์ชันตามเกณฑ์