Snasui Excel Forum

เรียนอาจารย์และเพื่อนสมาชิกทุกท่าน

ต้องการนับรายการแยกแผนกตาม วันจันทร์ - ศุกร์ กับวันหยุดเสาร์ - อาทิตย์ และวันหยุดนักขัตฤกษ์
ขออธิบายเป็น 2 กรณี ดังนี้

กรณีที่ 1 นับรายการแยก วันจันทร์ - ศุกร์ กับวันหยุดเสาร์ - อาทิตย์ ไม่ได้พิจารณาวันหยุดนักขัตฤกษ์
นับแผนก a เพศ ชาย ที่อยู่ในช่วงรหัส 0 - 100 เฉพาะวันจันทร์ - วันศุกร์ ในช่อง J4 ได้ 6
นับแผนก a เพศ ชาย ที่อยู่ในช่วงรหัส 0 - 100 เฉพาะวันเสาร์ - วันอาทิตย์ ในช่อง K4 ได้ 4 (ไม่รวมวันปีใหม่ ตรงกับ วันจันทร์ - ศุกร์
หรือวันหยุดเสาร์ - อาทิตย์ ก็ให้ใส่ตามวันนั้น เช่น ปีใหม่ตรงกับวันพุธ ก็ไปอยู่ในช่วง วันจันทร์ - วันศุกร์ เป็นต้น)
นับแผนก a เพศ หญิง ที่อยู่ในช่วงรหัส 0 - 100 เฉพาะวันจันทร์ - วันศุกร์ ในช่อง L4 ได้ 12
นับแผนก a เพศ หญิง ที่อยู่ในช่วงรหัส 0 - 100 เฉพาะวันเสาร์ - วันอาทิตย์ ในช่อง K4 ได้ 4

กรณีที่ 2 นับรายการแยก วันจันทร์ - ศุกร์ กับวันหยุดเสาร์ - อาทิตย์ พิจารณารวมวันหยุดนักขัตฤกษ์ด้วย
นับแผนก a เพศ ชาย ที่อยู่ในช่วงรหัส 0 - 100 เฉพาะวันจันทร์ - วันศุกร์ ในช่อง J4 ได้ 5 ไม่รวมวันปีใหม่
นับแผนก a เพศ ชาย ที่อยู่ในช่วงรหัส 0 - 100 เฉพาะวันเสาร์ - วันอาทิตย์และวันนักขัตฤกษ์ ในช่อง K4 ได้ 5 (รวมวันปีใหม่)
นับแผนก a เพศ หญิง ที่อยู่ในช่วงรหัส 0 - 100 เฉพาะวันจันทร์ - วันศุกร์ ในช่อง L4 ได้ 12
นับแผนก a เพศ หญิง ที่อยู่ในช่วงรหัส 0 - 100 เฉพาะวันเสาร์ - วันอาทิตย์และวันนักขัตฤกษ์ ในช่อง K4 ได้ 4

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

ขอบคุณครับ

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ J4 คีย์
   =SUMPRODUCT(--($B$2:$B$32=$J$2),--(WEEKDAY($A$2:$A$32,2)<6),--($D$2:$D$32=$I$4),--($C$2:$C$32>=$J$1),--($C$2:$C$32<=$K$1))
   Enter > Copy ลงด้านล่าง
2. ที่ K4 คีย์
   =SUMPRODUCT(--($B$2:$B$32=$J$2),--(WEEKDAY($A$2:$A$32,2)>5),--($D$2:$D$32=$I$4),--($C$2:$C$32>=$J$1),--($C$2:$C$32<=$K$1))
   Enter > Copy ลงด้านล่าง
3. ที่ J10 คีย์
   =SUMPRODUCT(--($B$2:$B$32=$J$8),--(WEEKDAY($A$2:$A$32,2)<6),--($D$2:$D$32=$I$10),--($C$2:$C$32>=$J$7),--($C$2:$C$32<=$K$7),--ISNA(MATCH($A$2:$A$32,$O$2:$O$28,0)))
   Enter > Copy ลงด้านล่าง
4. ที่ K10 คีย์
   =SUMPRODUCT(--($B$2:$B$32=$J$8),--(WEEKDAY($A$2:$A$32,2)>5)+ISNUMBER(MATCH($A$2:$A$32,$O$2:$O$28,0)),--($D$2:$D$32=$I$10),--($C$2:$C$32>=$J$7),--($C$2:$C$32<=$K$7))
   Enter > Copy ลงด้านล่าง

เซลล์ที่เหลือคิดว่าประยุกต์ใช้เองได้ งานลักษณะนี้สามารถเพิ่มคอลัมน์มาอีก 2 คอลัมน์เพื่อระบุว่าเป็นวันปกติหรือวันหยุด 1 คอลัมน์ อีกคอลัมน์ระบุว่าเป็น เสาร์ อาทิตย์ และนักขัตฤกษ์อีก 1 คอลัมน์ จากนั้นใช้ Pivot Table มาช่วย จะได้รายงานที่ยืดหยุ่นมากกว่าครับ

เรียนอาจารย์
ในช่อง M10 ได้ 5 ตามสูตร แต่ดูจากข้อมูลในคอลัมน์ A - D
1. 2/1/20 หญิง 1 a
2. 18/1/20 หญิง 1 a
3. 19/1/20 หญิง 2 a
4. 25/1/20 หญิง 8 a
นับได้ 4 ไม่ใช่ 5 ครับ ช่วนดูให้อีกทีครับ ขอบคุณครับ

แนบไฟล์ที่เขียนสูตรแล้วมาด้วยครับ

snasui wrote: Sun Feb 09, 2020 9:36 pm แนบไฟล์ที่เขียนสูตรแล้วมาด้วยครับ

แนบมาแล้วครับ

ตัวอย่างการปรับสูตรครับ

=SUMPRODUCT(--($B$2:$B$32=$L$8),--(((WEEKDAY($A$2:$A$32,2)>5)+ISNUMBER(MATCH($A$2:$A$32,$O$2:$O$28,0)))>0),--($D$2:$D$32=$I$10),--($C$2:$C$32>=$L$7),--($C$2:$C$32<=$M$7))

snasui wrote: Sun Feb 09, 2020 10:17 pm ตัวอย่างการปรับสูตรครับ

=SUMPRODUCT(--($B$2:$B$32=$L$8),--(((WEEKDAY($A$2:$A$32,2)>5)+ISNUMBER(MATCH($A$2:$A$32,$O$2:$O$28,0)))>0),--($D$2:$D$32=$I$10),--($C$2:$C$32>=$L$7),--($C$2:$C$32<=$M$7))

ได้ตามต้องการครับ ขอบคุณครับ
ขอถามในส่วนที่ปรับ
(((WEEKDAY($A$2:$A$32,2)>5)+ISNUMBER(MATCH($A$2:$A$32,$O$2:$O$28,0)))>0)
{1;0;0;1;1;0;0;0;0;0;1;1;0;0;0;0;0;1;1;0;0;0;0;0;1;1;0;0;0;0;0}
--(WEEKDAY($A$2:$A$32,2)>5)+ISNUMBER(MATCH($A$2:$A$32,$O$2:$O$28,0))
{1;0;0;1;1;0;0;0;0;0;1;1;0;0;0;0;0;1;1;0;0;0;0;0;2;1;0;0;0;0;0}
ทำไมต้อง >0 ? ครับ

Totem wrote: Sun Feb 09, 2020 10:51 pm {1;0;0;1;1;0;0;0;0;0;1;1;0;0;0;0;0;1;1;0;0;0;0;0;2;1;0;0;0;0;0}
ทำไมต้อง >0 ? ครับ

ที่ต้องกำหนดว่า >0 เพราะตัวที่ระบายสีเอาไว้ครับ มันคือเลข 2 ที่ได้จากการบวกกัน เลยปรับด้วยการนำไปเปรียบเทียบว่ามากกว่า 0 หรือไม่ หากใช่ก็จะเป็น 1 หากไม่ใช่ก็จะเป็น 0

เงื่อนไขสูตร Sumproduct ตามกระทู้นี้ต้องการผลลัพธ์ในการเปรียบเทียบเป็นเพียง 1 หรือ 0 เท่านั้นครับ

เข้าใจแล้วครับ ขอบคุณครับอาจารย์