Snasui Excel Forum

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

หากเข้าหลักเกณฑ์ว่า อยู่ในห้อง 1 , 2 , 9 , ช่วงอายุ 0-0-1 ถึง 0-0-9 และ สถานะ G1
นับได้ 4 อยู่ในช่อง F17

หากเข้าหลักเกณฑ์ว่า มีคำนำหน้าชื่อเป็น ด.ช.หรือ ด.ญ. , อยู่ในห้อง 1 , 2 , 9 และ สถานะ G1
นับได้ 5 อยู่ในช่อง F21

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

ขอบคุณครับ

F17=SUM(COUNTIFS(D2:D10,I3,C2:C10,H3:H11))
F21=SUM(COUNTIFS(D2:D10,I3,A2:A10,"ด*"))

puriwutpokin wrote: Sat Dec 26, 2020 2:07 pm F17=SUM(COUNTIFS(D2:D10,I3,C2:C10,H3:H11))
F21=SUM(COUNTIFS(D2:D10,I3,A2:A10,"ด*"))

ยังไมได้ตามต้องการ

จากสูตร
F17= 0
F21= 5

ตามหลักเกณฑ์ คือ ถ้าต้องมี ห้อง 1 , 2 , 9 และ ช่วงอายุ 0-0-1 ถึง 0-0-9 และ สถานะ G1 จะทำการนับ
F17 = 4
ตามหลักเกณฑ์ คือ ถ้ามีคำนำหน้าชื่อเป็น ด.ช.หรือ ด.ญ. และ อยู่ในห้อง 1 , 2 , 9 และ สถานะ G1 จะทำการนับ
F21= 5

หากเราจะทำการเปลี่ยนแปลงข้อมูลในตาราง column F ถึง column I ในโอกาสหน้า เช่า
เราจะเปลี่ยนนับคำนำหน้า เป็น นาง เป็น นาย หรือ เปลี่ยน ห้องเป็น ห้อง 5 หรือ เปลี่ยน ช่่วงอายุ เป็น ช่วงอื่น 0-0-10 ถึง 0-0-20 หรือเปบลี่ยน สถานะ เป็น G3 เป็นต้น
การนับในช่อง F17 และ F21 ก็จะเปลี่ยนแปลงการนับไปตามที่เราต้องการได้ด้วยครับ

ขอบคุณครับ

F17
=COUNT(INDEX(MATCH(B2:B10,G3:G12,)*MATCH(C2:C10,H3:H12,)*MATCH(D2:D10,I3:I12,),))

F21
=COUNT(INDEX(MATCH(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A2:A10,".",". ",2)," ",REPT(" ",9)),9)),F3:F12,)*MATCH(B2:B10,G3:G12,)*MATCH(D2:D10,I3:I12,),))

Bo_ry wrote: Sat Dec 26, 2020 6:14 pm F17
=COUNT(INDEX(MATCH(B2:B10,G3:G12,)*MATCH(C2:C10,H3:H12,)*MATCH(D2:D10,I3:I12,),))

F21
=COUNT(INDEX(MATCH(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A2:A10,".",". ",2)," ",REPT(" ",9)),9)),F3:F12,)*MATCH(B2:B10,G3:G12,)*MATCH(D2:D10,I3:I12,),))

สูตรได้ตามต้องการครับ

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

ขอบคุณครับ

H21
=COUNT(MMULT(--ISNUMBER(FIND(TRANSPOSE(F3:F12),A2:A10)),N(F3:F12>0))*MATCH(B2:B10,G3:G12,)*MATCH(D2:D10,I3:I12,))

กด Ctrl+Shift+Enter

Bo_ry wrote: Sun Dec 27, 2020 12:20 pm H21
=COUNT(MMULT(--ISNUMBER(FIND(TRANSPOSE(F3:F12),A2:A10)),N(F3:F12>0))*MATCH(B2:B10,G3:G12,)*MATCH(D2:D10,I3:I12,))

กด Ctrl+Shift+Enter

สูตรที่ได้นั่น คำนำหน้าชื่อ นาย ใช้ได้ครับ แต่ ถ้าหากลองเปลี่ยนที่ช่อง A4 เป็น นาง หรือ นางสาว ช่อง H21
จะต้องนับได้ 5 ในสูตรยังให้ช่อง H21 จำนวนที่นับเป็น 6 เหมือนเดิมครับ

ขอบคุณครับ

ลองปรับสูตรคุณ Bo_ry
ที่ H21=SUM(IFERROR(SIGN(MMULT(--ISNUMBER(FIND(TRANSPOSE(F3:F12),A2:A10)),N(F3:F12>0))*MATCH(B2:B10,G3:G12,)*MATCH(D2:D10,I3:I12,)),)) Ctrl+Shift+Enter

puriwutpokin wrote: Mon Dec 28, 2020 10:59 pm ลองปรับสูตรคุณ Bo_ry
ที่ H21=SUM(IFERROR(SIGN(MMULT(--ISNUMBER(FIND(TRANSPOSE(F3:F12),A2:A10)),N(F3:F12>0))*MATCH(B2:B10,G3:G12,)*MATCH(D2:D10,I3:I12,)),)) Ctrl+Shift+Enter

สูตรสามารถนับได้ในกรณีคำนำหน้าชื่อ นาย และ นาง นับได้ถูกต้องครับ
แต่ถ้าคำนำหน้าชื่อ เป็นนางสาว การนับยังไม่ถูกต้องครับ เช่น ใน column A
ช่อง A4 , A8 เป็นนางสาว และ column F ช่อง F5 เป็น นาง ในช่อง H21 = 5
คือไม่นับนางสาว
ขอบคุณครับ

=COUNT(MATCH(LOOKUP(INT(LOG(MMULT(1-ISERR(FIND({"ด.ช.","ด.ญ.","นาง","นางสาว","นาย"},A2:A10)),2^{1;2;3;4;5}),2)),{1;2;3;4;5},{"ด.ช.","ด.ญ.","นาง","นางสาว","นาย"}),F3:F12,)*MATCH(B2:B10,G3:G12,)*MATCH($D$2:$D$10,$I$3:$I$12,))

Bo_ry wrote: Tue Dec 29, 2020 12:37 pm =COUNT(MATCH(LOOKUP(INT(LOG(MMULT(1-ISERR(FIND({"ด.ช.","ด.ญ.","นาง","นางสาว","นาย"},A2:A10)),2^{1;2;3;4;5}),2)),{1;2;3;4;5},{"ด.ช.","ด.ญ.","นาง","นางสาว","นาย"}),F3:F12,)*MATCH(B2:B10,G3:G12,)*MATCH($D$2:$D$10,$I$3:$I$12,))

ได้ตามต้องการครับ ขอบคุณครับ