“อยากนับเฉพาะวันอาทิตย์ ต้องเขียนสูตรยังไงครับ” จู่ๆโจ้ก็โพล่งถามขึ้นในบ่ายวันศุกร์ ตอนผมนั่งสะลึมสะลือ ตากำลังปรือได้ที่
“แป๊ปนะ” ผมตอบทั้งที่ยังง่วง พลางคิดในหัวว่าจะใช้สูตรอะไร

คำถามนี้ทำเอาผมตื่นเลย !
ดูเผินๆเหมือนจะง่าย แต่เอาเข้าจริงแล้วถือว่ายากเลยล่ะ

ทำไมถึงยากน่ะหรือครับ?
เพราะ Excel ไม่มีฟังก์ชันนับเฉพาะวันเสาร์-อาทิตย์
ถ้าอยากได้คำตอบ ต้องคิดตรรกะแล้วเขียนสูตรเอง
แล้วจะเขียนสูตรยังไงล่ะ?

จริงๆแล้วคำถามนี้จะไม่ยากเลย ถ้ามีช่วงข้อมูลที่ต้องการ
เช่น อยากรู้ว่าวันที่ 1 กรกฎาคม 2019 ถึงวันที่ 15 กรกฎาคม 2019 มีวันอาทิตย์กี่วัน ก็สร้างคอลัมน์วันที่ขึ้นมาก่อน แบบนี้

(เพื่อให้ง่ายต่อการเข้าใจ ขอไฮไลต์ข้อมูลวันอาทิตย์เป็นสีส้มนะครับ ^_^)
จากนั้นก็ใช้ฟังก์ชัน WEEKDAY เช็คว่าวันนั้นเป็นวันอะไร
เช่น เขียนสูตรในเซลล์ B2 เป็น
=WEEKDAY(A2)

มาทำความรู้จักฟังก์ชัน WEEKDAY กันนิดนึง ^__^

WEEKDAY ใช้คำนวณว่าวันนั้นเป็นวันอะไรในรอบสัปดาห์
ผลลัพธ์จาก WEEKDAY คือตัวเลข 1-7
ความหมายของแต่ละตัวเลขคือ

นั่นคือ ถ้าได้ผลลัพธ์ = 1 ก็แปลว่าวันนั้นเป็นวันอาทิตย์ !

อยากนับเฉพาะวันอาทิตย์ ก็นับเฉพาะค่าในคอลัมน์ B ที่เป็น 1
เช่นใช้ฟังก์ชัน COUNTIF หรือเขียนสูตรเป็น
=COUNTIF(B2:B16,1)
เคสนี้ได้คำตอบเป็น 2
(วันที่ 7 และ 14 กรกฎาคม 2019)

อย่างไรก็ตาม การเขียนสูตรด้วย COUNTIF ไม่ค่อยยืดหยุ่นกรณีมีเงื่อนไขซับซ้อน
ถ้าต้องการให้สูตรยืดหยุ่น ใช้ฟังก์ชัน SUMPRODUCT ดีกว่า
เช่น เปลี่ยนจาก
=COUNTIF(B2:B16,1)
เป็น
=SUMPRODUCT(- -(B2:B16=1))

ได้คำตอบเท่ากันเป๊ะ!

ขออธิบายสูตร SUMPRODUCT เพิ่มเติมนิดนึงนะครับ ^__^
เคสนี้ไม่สามารถเขียนสูตรเป็น
=SUMPRODUCT(B2:B16=1)
เพราะผลลัพธ์ของ B2:B16=1 คือ TRUE หรือ FALSE
ถ้าเขียนแบบนี้ จะได้ผลลัพธ์เป็น
=SUMPRODUCT({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE})
= 0

ต้องการผลลัพธ์เป็นตัวเลข ต้องเปลี่ยน TRUE ให้เป็น 1 และเปลี่ยน FALSE ให้เป็น 0
เทคนิคการเปลี่ยน TRUE, FALSE ให้เป็น 1, 0 แบบง่ายที่สุดก็คือ ใส่เครื่องหมายลบสองครั้งด้านหน้า (- -)
= SUMPRODUCT(- -(B2:B16=1))
= SUMPRODUCT(- -({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}))
= SUMPRODUCT({0;0;0;0;0;0;1;0;0;0;0;0;0;1;0})
= 2

อยากได้ผลลัพธ์แบบนี้ แต่ไม่ต้องเพิ่มคอลัมน์ B ได้ไหมครับ?
เป็นคำถามที่ดีมากครับ
การสร้างคอลัมน์เพิ่มอาจเกิดความไม่สะดวก
เดิมทีเราสร้างคอลัมน์ B ขึ้นมาเพื่อใช้ฟังก์ชัน WEEKDAY
B2 = WEEKDAY(A2)
นั่นแปลว่า
B2:B16 = WEEKDAY(A2:A16)
ถ้าไม่อยากสร้างคอลัมน์ B งั้นก็เอาฟังก์ชัน WEEKDAY ไปใส่ใน SUMPRODUCT เลย
หรือเปลี่ยนจาก
=SUMPRODUCT(- -(B2:B16=1))
เป็น
=SUMPRODUCT(- -(WEEKDAY(A2:A16)=1))
ได้ผลลัพธ์เหมือนกันเป๊ะ !

แต่… การคำนวณแบบนี้ เอาเข้าจริงแล้วไม่สะดวกเลย
ทำไมน่ะหรือครับ?
เพราะต้องสร้างคอลัมน์วันที่ขึ้นมาก่อน และต้องคิดเผื่อว่าช่วงวันที่ที่ต้องการมีกี่วัน
บางเคสอาจต้องสร้างคอลัมน์วันที่ยาวเฟื้อยเป็นหมื่นๆบรรทัด

บ้าไปแล้ว !

ในการทำงานจริงมักกำหนดวันเริ่มต้น (Start Date) และวันสิ้นสุด (End Date) เช่น กำหนดแบบนี้

แล้วจะเขียนสูตรยังไงล่ะ?
เป็นคำถามที่ดีเช่นเคย
คำตอบคือ ยังคงใช้สูตรเดิมครับ ^__^

นั่นคือ จากสูตร
=SUMPRODUCT(- -(WEEKDAY(A2:A16)=1))
ให้มองเป็น
=SUMPRODUCT(- -(WEEKDAY(StartDate:EndDate)=1))

หรือแทนค่า StartDate = B1, EndDate = B2
กลายเป็น
=SUMPRODUCT(- -(WEEKDAY(ฺB1&”:”&B2)=1))

แต่… Excel จะไม่ยอมให้เราเขียนสูตรแบบนี้ตรงๆ เพราะ Excel มอง B1&”:”&B2 เป็นตัวหนังสือ (Text) ไม่ใช่ช่วงเซลล์ (Cell Reference)

ถ้าเขียนตรงๆไม่ได้ งั้นก็ต้องเขียนอ้อมๆ!
ถ้าเขียนอ้อมๆ งั้นก็ต้องฟังก์ชันนี้
INDIRECT
(คำว่า INDIRECT แปลว่า อ้อมๆ ด้วยนะ ^^)
นำฟังก์ชัน INDIRECT มาครอบ B1&”:”&B2
หรือเขียนสูตรเป็น
=SUMPRODUCT(- -(WEEKDAY(ฺINDIRECT(B1&”:”&B2))=1))

สูตรนี้ดูเผินๆเหมือนจะโอ แต่ Excel ก็ยังไม่ยอมอยู่ดี
ทำไมน่ะหรือครับ?
ก็เพราะว่า
B1 คือวันที่ 1 กรกฎาคม 2019 หรือค่าจริงๆคือ 43647
B2 คือวันที 15 กรกฎาคม 2019 หรือค่าจริงๆคือ 43661

ทำไม 1 กรกฎาคม 2019 ถึงมีค่าเป็น 43647 ล่ะ?
เป็นคำถามที่ดีเช่นเดิม ขออธิบายเรื่องวันที่เพิ่มเติมเล็กน้อยครับ ^__^

จริงๆแล้ววันที่ใน Excel ก็คือตัวเลขนั่นเอง ที่เห็นเป็นวันที่คือการแสดงผลเท่านั้น แต่ค่าเนื้อในจริงๆเป็นตัวเลข
(อารมณ์เหมือนสังข์ทอง กายภายนอกเป็นเงาะป่า แต่กายภายในเป็นทอง ^^)

วันที่เก่าสุดที่ Excel รองรับได้คือวันที่ 1 มกราคม 1900 (ปี ค.ศ.)
ดังนั้น 1 จึงเทียบเท่าวันที่ 1 มกราคม 1900
2 เทียบเท่าวันที่ 2 มกราคม 1900
3 เทียบเท่าวันที่ 3 มกราคม 1900
32 เทียบเท่าวันที่ 1 กุมภาพันธ์ 1900
การเพิ่มขึ้น 1 ก็คือการเพิ่มขึ้น 1 วันนั่นเอง

1 ปี = 365
10 ปี = 3,652 (ทุก 4 ปี เดือนกุมภาพันธ์จะมี 29 วัน)
100 ปี = 36,525 (ทุก 4 ปี เดือนกุมภาพันธ์จะมี 29 วัน)
119 ปี = 43,465
ดังนั้นวันที่ในปี 2019 จะมีค่ามากกว่า 43,465 เพราะปีปัจจุบันห่างจากปีเริ่มต้น (1900) 119 ปี
วันที่ 1 กรกฎาคม 2019 จึงมีค่าที่แท้จริงเป็น 43,647
วันที่ 15 กรกฎาคม 2019 จึงมีค่าเพิ่มขึ้นอีก 14 หรือก็คือ 43,461 นั่นเอง ^_^

ขอกลับมาที่ INDIRECT(B1&”:”&B2) ต่อ
(นอกเรื่องไปซะนาน ^^)
B1 คือ 1 กรกฎาคม 2019 (43467)
B2 คือ 15 กรกฎาคม 2019 (43461)
Excel มองว่า INDIRECT(B1&”:”&B2) คือ
INDIRECT(43647:43661)
แปลว่าจะดึงข้อมูลทุกเซลล์ตั้งแต่บรรทัดที่ 43647 ถึงบรรทัดที่ 43661 ของชีตนี้มาทั้งหมด
(Excel แปลความหมาย 43647 เป็นแถว (row) 43467 ของชีตนี้)

ทราบไหมครับว่า Excel 1 บรรทัดมีกี่เซลล์? หรือ Excel 1 แถว มีกี่คอลัมน์?
ติ๊กต่อก ติ๊กต่อก …
.
.
คำตอบคือ 16,384 คอลัมน์ !!
(คอลัมน์ A:XFD )

ถ้าข้อมูลในเซลล์นั้นเป็นค่าว่าง จะดึงค่าเป็น 0

บรรทัดที่ 43647 ถึงบรรทัดที่ 43661 ของชีตนี้ไม่มีข้อมูลใดๆเลย แปลว่าจะดึงเลข 0 มาทั้งหมด 245,760 ตัว !!
(15 x 16,384 = 245,760)
บ้าไปแล้ว !!

แล้วจะทำยังไงดีล่ะ?
ลองกลับมามองที่จุดประสงค์ของสูตรครับ
เราเขียนสูตร
INDIRECT(B1&”:”&B2)
เพื่อต้องการให้เป็น
INDIRECT(43647:43661)
หรือต้องการผลลัพธ์เป็นช่วงตัวเลข 43647-43661

ถ้าต้องการเช่นนี้ เทคนิคที่ใช้คือ นำฟังก์ชัน ROW ไปครอบ
หรือเขียนสูตรเป็น
ROW( INDIRECT(B1&”:”&B2) )

ฟังก์ชัน ROW คืออะไร?
ฟังก์ชัน ROW ใช้หาเลขบรรทัดของเซลล์ หรือเลขบรรทัดของแถว (row) นั้นๆ เช่น
ROW(A9) = 9
ROW(B9) = 9
ROW(9:10) = {9;10}
ROW(9:12) = {9;10;11;12}

ROW( INDIRECT(B1&”:”&B2) ) ได้ผลลัพธ์เป็น
{43647;43648;43649;43650;43651;43652;43653;43654;43655;43656;43657;43658;43659;43660;43661}

ต้องการรู้ว่าตัวเลขนี้เป็นวันอะไร ก็นำฟังก์ชัน WEEKDAY ไปครอบ
หรือเขียนเป็น
WEEKDAY( ROW( INDIRECT(B1&”:”&B2) ) )

ต้องการเช็ควันเป็นวันอาทิตย์หรือไม่ ก็เพิ่ม =1 เข้าไป
หรือเขียนสูตรเป็น
WEEKDAY(ROW( INDIRECT(B1&”:”&B2) ) ) =1

ถ้าเขียนสูตรแค่นี้ จะได้ผลลัพธ์เป็น
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}

แต่เราต้องการผลลัพธ์เป็นตัวเลข
งั้นก็ต้องเปลี่ยน TRUE เป็น 1 , FALSE เป็น 0
หรือก็คือนำเครื่องหมายลบสองครั้งมาครอบ (- -) นั่นเอง
เขียนสูตรเป็น
=- -( WEEKDAY(ROW( INDIRECT(B1&”:”&B2) ) ) =1 )
ได้ผลลัพธ์เป็น
={0;0;0;0;0;0;1;0;0;0;0;0;0;1;0}

ต้องการรวมตัวเลขทั้งหมดเข้าด้วยกัน ก็นำฟังก์ชัน SUMPRODUCT ไปครอบ
หรือเขียนสูตรเป็น

=SUMPRODUCT(- -( WEEKDAY(ROW( INDIRECT(B1&”:”&B2) ) ) =1 ))

ฮัดช่า !!

เคสนี้ใช้ SUM ครอบก็ได้เช่นกัน แต่ถ้าใช้ SUM ต้องเปลี่ยนจากการกด Enter เป็น Ctrl+Shift+Enter เมื่อกดแล้วสูตรจะมีวงเล็บปีกกาครอบ
หรือกลายเป็น
{=SUM(- -(WEEKDAY(ROW(INDIRECT(E6&”:”&E7)))=7))}
โดยส่วนตัวผมมองว่าใช้ SUMPRODUCT ง่ายกว่า ^__^

ถ้ามองดีๆ จะพบว่าสูตร
=SUMPRODUCT(- -(WEEKDAY(ROW(INDIRECT(B1&”:”&B2)))=1))
มีโครงสร้างเหมือนสูตรเดิม
=SUMPRODUCT(- -(WEEKDAY(A2:A16)=1))
เป๊ะเลย

แค่เปลี่ยนจาก
A2:A16
เป็น
ROW(INDIRECT(B1&”:”&B2))
เท่านั้นเอง

ที่อธิบายมาตั้งนาน เพื่อต้องการสื่อว่า เราสามารถสร้างช่วงข้อมูลเสมือนด้วย ROW(INDIRECT(B1&”:”&B2))
หรือไม่จำเป็นต้องสร้างช่วงข้อมูลจริงขึ้นมา

เทคนิคนี้ประยุกต์ใช้ได้เยอะมากๆ
ถ้าเห็นใครใช้สูตร ROW(INDIRECT(X:Y)) แปลว่าเค้าต้องการสร้างช่วงข้อมูลเสมือนขึ้นมานั่นเองครับ ^__^

แล้วถ้าต้องการนับเฉพาะวันเสาร์ล่ะ?
ถ้าต้องการนับเฉพาะวันเสาร์ ปรับสูตรตรง WEEKDAY นิดเดียวครับ
เพราะถ้าต้องการวันเสาร์ ผลลัพธ์จาก WEEKDAY = 7

เราก็เพียงปรับสูตรจาก
=SUMPRODUCT(- -(WEEKDAY(ROW(INDIRECT(B1&”:”&B2)))=1))
เป็น
=SUMPRODUCT(- -(WEEKDAY(ROW(INDIRECT(B1&”:”&B2)))=7))

ว้าว !

แล้วถ้าต้องการนับทั้งวันเสาร์และวันอาทิตย์ล่ะ?
ถ้าต้องการนับทั้งวันเสาร์และวันอาทิตย์ ก็ใช้ผลลัพธ์จาก WEEKDAY เป็นทั้ง 1 หรือ 7
หรือปรับจาก
=1
เป็น
={1,7}
หรือเขียนสูตรเป็น
=SUMPRODUCT(- -(WEEKDAY(ROW(INDIRECT(B1&”:”&B2)))={1,7}))

อึ้ง จึ้ง กึ้ง ! เลยใช่ไหมล่ะ ^^

การนำวงเล็บปีกกามาครอบ หรือเขียนเป็น {1,7} คือเทคนิคที่เรียกว่า Array Constants หมายความว่าเป็นได้ทั้ง 1 หรือ 7 นั่นเอง
แต่เทคนิคการใช้ Array Constants นั้นใช้ได้กับบางฟังก์ชันเท่านั้น (เช่น SUM, SUMPRODUCT) นั่นแปลว่าใช้กับบางฟังก์ชันไม่ได้
ขอไม่ลงรายละเอียดเรื่อง Array Constants เยอะนะครับ เดี๋ยวบทความจะยาวยิ่งกว่านี้ ^__^

อ้อ ! สูตรนับเฉพาะวันอาทิตย์ยังเขียนได้อีกแบบนึงนะครับ

สูตรเดิมของเราคิดแบบตรงๆ โดยใช้เทคนิคของฟังก์ชันใน Excel
แต่สามารถนำตรรกะทางคณิตศาสตร์มาคำนวณได้ด้วยเช่นกัน
ผมเจอสูตรจากเว็บไซต์ https://a4accounting.com.au/count-sundays-between-two-dates/ แล้วชอบมากๆ
ถ้าใช้กับเคสเดิม จะเขียนสูตรได้เป็น

=INT((WEEKDAY(B1-1)-B1+B2)/7)

ไม่รู้คุณคิดเหมือนผมหรือเปล่า ผมว่าสูตรนี้ ‘งดงาม’ มากๆ

แถมประยุกต์เป็นนับวันเสาร์ได้ด้วย หรือเขียนสูตรเป็น

=INT((WEEKDAY(B1-7)-B1+B2)/7)

ต้องการนับทั้งวันเสาร์และอาทิตย์ ก็จับมาบวกกัน

=INT((WEEKDAY(B1-7)-B1+B2)/7)+INT((WEEKDAY(B1-1)-B1+B2)/7)

เจ๋งเนอะ ^__^
ขอคารวะคนคิดสูตรงามๆ 3 ที ^/\^

และนี่คือสูตรทั้งหมดของบทความนี้ครับ

ปรับเป็นรูปแบบการคำนวณแบบต่อเนื่องก็ได้

รอช้าอยู่ไย?
เปิดคอมพ์ขึ้นมา ลองเขียนสูตรดูเลย
ลุย !!

สำหรับใครที่สนใจไฟล์ตัวอย่างของบทความนี้ สามารถดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้เลยครับ

อ้อ! บทความนี้มี ep2 ด้วยนะครับ สูตรเจ๋งมาก อ่านได้จาก บทความนี้

.
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่โดยคลิก Like เฟซบุ๊กแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^

อ้อ! ตอนนี้ผมมีสอนคอร์สออนไลน์ ชื่อ “Excel In Essence: รู้ Excel เท่านี้ ชีวิตดีขึ้นมากมาย” สนใจดูรายละเอียดได้จาก ลิงค์นี้ ครับ

Credit:
https://a4accounting.com.au/count-sundays-between-two-dates/
https://exceljet.net/formula/count-dates-by-day-of-week
https://www.extendoffice.com/documents/excel/1421-excel-count-mondays-sundays-between-two-dates.html