นับจำนวนรายการที่เหลือจากการถูกหักออกจากรายการในคอลัมน์อื่นๆ

[Questioner]

เรียน อาจารย์และผู้รู้ทุกท่านครับ
อยากทราบสูตรในการนับจำนวนรายการที่เหลือจากการถูกหักออกจากรายการในคอลัมน์อื่นๆ ตามไฟล์ที่แนบมา (ข้อมูล 2 ชีต)
ขอขอบพระคุณมากครับ

[snasui]

อธิบายวิธีคำนวณตัวอย่างคำตอบมาด้วยครับ

ยกตัวอย่างค่าในเซลล์ J4, K4 ของชีต หนี้ 0200 มีวิธีคำนวณอย่างไรจึงได้ค่าเท่านั้นครับ

[Questioner]

เรียนท่านอาจารย์ครับ

ใช้ Filter ข้อมูลแล้วนับรายการที่ยังไม่ได้รับการชำระ ในการเตือนทั้ง 3 ครั้งครับ เช่น
หนี้ 0200
เมื่อ Filter ลน01 จะได้ หนี้ค้างชำระ 3 ประเภท 3 รายการ คือ ประเภท 02001 ปี 2565, ประเภท 02002 ปี 2565, ประเภท 02003 ปี 2564
Filter ลน02 จะได้ หนี้ค้างชำระ 3 ประเภท 3 รายการ คือ ประเภท 02003 ปี 2564, ประเภท 02004 ปี 2564, ประเภท 02006 ปี 2564
Filter ลน03 จะได้ หนี้ค้างชำระ 1 ประเภท 1 รายการ คือ ประเภท 02007 ปี 2565
Filter ลน04 จะได้ หนี้ค้างชำระ 2 ประเภท 2 รายการ คือ ประเภท 02008 ปี 2563, ประเภท 02009 ปี 2565
Filter ลน05 จะได้ หนี้ค้างชำระ 2 ประเภท 2 รายการ คือ ประเภท 02010 ปี 2564, 02011 ปี 2564

หนี้ 0100
เมื่อ Filter ลน01 จะได้ หนี้ค้างชำระ 2 ประเภท 3 รายการ คือ ประเภท 01001 เดือนมกราคม 2565 และเดือนธันวาคม 2564, ประเภท 01002 เดือนมกราคม 2565
Filter ลน02 จะได้ หนี้ค้างชำระ 2 ประเภท 2 รายการ คือ ประเภท 01003 เดือนสิงหาคม 2564, ประเภท 01008 เดือนกุมภาพันธ์ 2565
Filter ลน03 จะได้ หนี้ค้างชำระ 1 ประเภท 2 รายการ คือ ประเภท 01001 เดือนมกราคม 2565 และเดือนมีนาคม 2565
Filter ลน04 จะได้ หนี้ค้างชำระ 3 ประเภท 4 รายการ คือ ประเภท 01004 เดือนมิถุนายน 2565, ประเภท 01005 เดือนกรกฎาคม 2565, ประเภท 01001 เดือนสิงหาคม 2565และเดือนตุลาคม 2565
Filter ลน05 จะได้ หนี้ค้างชำระ 2 ประเภท 2 รายการ คือ ประเภท 01006 เดือนพฤศจิกายน 2564, ประเภท 01002 เดือนธันวาคม 2564

ขอบพระคุณท่านอาจารย์มากครับ

[snasui]

กรุณาระบุ Version ของ Excel ที่ใช้จริงใน Profile ตาม Link นี้ด้วยครับ viewtopic.php?p=103177#p103177

ตัวอย่างสูตรที่ชีต หนี้ 200 เซลล์ J4 คีย์

=AGGREGATE(15,6,COUNT(1/((FREQUENCY(MATCH($A$4:$A$22&B$4:B$22,$A$4:$A$22&B$4:B$22,0),ROW(B$4:B$22)-ROW(B$4)+1))/($A$4:$A$22=$I4))),1)

Enter > Copy ไป K4 แล้ว Copy ลงด้านล่าง

[Xcelvba]

กรุณาระบุ Version ของ Excel ที่ใช้จริงใน Profile ตาม Link นี้ด้วยครับ viewtopic.php?p=103177#p103177

ตัวอย่างสูตรที่ชีต หนี้ 200 เซลล์ J4 คีย์

=AGGREGATE(15,6,COUNT(1/((FREQUENCY(MATCH($A$4:$A$22&B$4:B$22,$A$4:$A$22&B$4:B$22,0),ROW(B$4:B$22)-ROW(B$4)+1))/($A$4:$A$22=$I4))),1)

Enter > Copy ไป K4 แล้ว Copy ลงด้านล่าง

ขออนุญาตถามสูตรจากกระทู้นี้ครับ
Match _ & _ , _ & _ หมายความว่าอย่างไรครับ ปกติผมจะเห็นแค่ Match เป็นตัว หรือเป็นช่วงแบบนี้ครับ
1.Match(A1, A:B )
2.Match(A:B,A:B)

ไม่เคยเห็น Match(A:B & C:D , .... ) อะไรแบบนี้ครับ

[snasui]

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

[Questioner]

เรียนท่านอาจารย์ครับ
ขอเรียนว่าเมื่อใช้สูตรที่อาจารย์ให้มายังไม่ได้ผลลัพท์ที่สมบูรณ์ ดังรายละเอียดครับ ต้องปรับสูตรอย่างไรครับ

ผลลัพท์ที่ต้องการ.png

ผลลัพท์จากสูตร.png

ลน02.png

[Questioner]

ลน03.png

ลน04.png

ลน05.png

ขอบพระคุณอาจารย์มากครับ

[snasui]

กรุณาอธิบายรายเฉพาะรายการที่ไม่ถูกต้องว่าผิดพลาดอย่างไร

นอกจากภาพแล้วช่วยอธิบายด้วยข้อความด้วยว่าค่าที่ได้เป็นเท่าใด นับอย่างไรจึงได้ค่าเท่านั้นครับ

ลน02 มีค่าเป็น 3, 3 ตามลำดับ ช่วยอธิบายวิธีนับให้ได้ค่า 3 มาอย่างละเอียด ไม่ใช่แค่ Filter แล้วจับภาพมาแสดงครับ

[Questioner]

ลน02.png

เรียนท่านอาจารย์ครับ
ภาพ ลน 02 จะเห็นว่า ประเภทหนี้ค้างที่ยังไม่ได้รับชำระในการเตือนทั้ง 3 ครั้ง (ไม่มีการลงวันที่ในคอลัมน์ เตือนครั้งที่ 1,2 และ 3 )
คือหนี้ประเภท 02003 ปี 2564, 02004 ปี 2564, 02006 ปี 2564 หรือสรุปคือรวมหนี้ค้าง 3 ประเภท 3 รายการ คือค่าที่ต้องการครับ

ลน03.png

ภาพลน 03 จะเห็นว่า ประเภทหนี้ค้างที่ยังไม่ได้รับชำระในการเตือนทั้ง 3 ครั้ง (ไม่มีการลงวันที่ในคอลัมน์ เตือนครั้งที่ 1,2 และ 3 )
คือหนี้ประเภท 02007 ปี 2565 หรือสรุปคือหนี้ค้าง 1 ประเภท 1 รายการ คือค่าที่ต้องการครับ

ลน04.png

ภาพลน 04 จะเห็นว่า ประเภทหนี้ค้างที่ยังไม่ได้รับชำระในการเตือนทั้ง 3 ครั้ง (ไม่มีการลงวันที่ในคอลัมน์ เตือนครั้งที่ 1,2 และ 3 )
คือหนี้ประเภท 02008 ปี 2563, 02009 ปี 2565 หรือสรุปคือรวมหนี้ค้าง 2 ประเภท 2 รายการ คือค่าที่ต้องการครับ

[Questioner]

ลน05.png

ภาพลน 05 จะเห็นว่า ประเภทหนี้ค้างที่ยังไม่ได้รับชำระในการเตือนทั้ง 3 ครั้ง (ไม่มีการลงวันที่ในคอลัมน์ เตือนครั้งที่ 1,2 และ 3 )
คือหนี้ประเภท 02010 ปี 2564, 02011 ปี 2564 หรือสรุปคือรวมหนี้ค้าง 2 ประเภท 2 รายการ คือค่าที่ต้องการครับ

ขอขอบพระคุณท่านอาจารย์ครับ

[snasui]

ภาพลน 05 จะเห็นว่า ประเภทหนี้ค้างที่ยังไม่ได้รับชำระในการเตือนทั้ง 3 ครั้ง (ไม่มีการลงวันที่ในคอลัมน์ เตือนครั้งที่ 1,2 และ 3 )

ข้อความที่ระบายสีเอาไว้คือข้อความสำคัญที่จำเป็นต้องแจ้งมาครับ

ตัวอย่างสูตร O365 คีย์ในเซลล์ D4 ตามด้านล่างครับ

=COUNTA(UNIQUE(FILTER(B$4:B$22,(($D$4:$D$22+$E$4:$E$22+$F$4:$F$22)=0)*($A$4:$A$22=$I4))))

Enter > Copy ไป E4 และ Copy ลงด้านล่าง

[Questioner]

เรียนท่านอาจารย์ครับ
ขอประทานโทษอย่างสูงครับที่ผมให้ข้อมูลไม่สมบูรณ์ อธิบายไม่ครบถ้วน และเป็นเหตุให้เกิดปัญหาตามมาอีกแล้วครับ
เนื่องจากหากใช้สูตรเดียวกันจากการ copy ไปวางอีกคอลัมน์จะได้ค่าของอีกคอลัมน์ไม่ถูกต้องครับ
เช่น ลน02 ต้องการค่า 3, 3 แต่สูตรนับ เป็น 3, 1 เนื่องจากข้อมูลอีกคอลัมน์ที่ให้นับคือ 2564 มีอยู่ 3 รายการ (มีอยู่ใน 3 row)แต่ สูตรให้ค่าเป็น 1
ลน05 ต้องการค่า 2, 2 แต่สูตรนับ เป็น 2, 1 เนื่องจากข้อมูลอีกคอลัมน์ที่ให้นับคือ 2564 มีอยู่ 2 รายการ (มีอยู่ใน 2 row)แต่ สูตรให้ค่าเป็น 1
ลน01 ต้องการค่า 3, 3 แต่สูตรนับ เป็น 3, 2 เนื่องจากข้อมูลอีกคอลัมน์ที่ให้นับคือ 2564 และ 2565 มีอยู่ 3 รายการ (2564 ใน 1 row และ 2565 ใน 2 row)แต่ สูตรให้ค่าเป็น 2
กรณีนี้ต้องใช้สูตรสำหรับอีกคอลัมน์อย่างไร หรือปรับสูตรอย่างไรครับ

สูตรที่อาจารย์ให้มาใช้ได้กับ excel 365 เพียงอย่างเดียวใช่ไหมครับ
ในการใช้งานจริงที่ออฟฟิศใช้ excel 2010, excel 2013 ต้องปรับอย่างไรครับ
ขอประทานโทษครับที่รบกวนอาจารย์เป็นอย่างมาก
ขอบพระคุณมากครับ

[snasui]

ที่ K4 ปรับสูตรเป็นด้านล่างครับ

=SUMPRODUCT(--($D$4:$D$22+$E$4:$E$22+$F$4:$F$22=0),--($A$4:$A$22=$I4))

Enter > Copy ลงด้านล่าง

ส่วน Version อื่น ๆ คีย์สูตรที่ J4 เป็น

=SUM(IF(FREQUENCY(IF($D$4:$D$22+$E$4:$E$22+$F$4:$F$22=0,IF($A$4:$A$22=$I4,MATCH($B$4:$B$22,$B$4:$B$22,0))),ROW($A$4:$A$22)-ROW($A$4)+1),1))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

[Questioner]

ได้ผลลัพท์ตามต้องการแล้วครับ
ขอขอบพระคุณท่านอาจารย์ snasui เป็นอย่างสูงครับ