Page 1 of 2
หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Mon Jan 18, 2021 9:10 pm
by yangkodza
z01.PNG
จากภาพต้องการหาตัวเลขที่หายไปในแต่ละห้อง เช่น F7 ต้องได้คำตอบเป็น 10,15,20 แบบนี้ครับจะได้ไม่ต้องเสียเวลามาไล่หาตัวเลขที่หายไปเอง cell ที่ต้องการหาคำตอบ คือ F7,F10,F13,F16,F19 ขอบคุณมากครับ
หาเลขที่หายไป.xlsx
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Mon Jan 18, 2021 9:28 pm
by puriwutpokin
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Mon Jan 18, 2021 9:33 pm
by yangkodza
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 12:28 am
by puriwutpokin
ต้องใช้คอลัมน์ช่วยครับ ษ7=IF($E7<>"เลขที่ขาด","",IFERROR(AGGREGATE(15,6,(ROW(INDIRECT("1:"&$F5))-1)/ISNA(MATCH(ROW(INDIRECT("1:"&$F5))-1,OFFSET($B$2,MATCH($D5,$C$2:$C$118,)-1,,COUNTIF($C$2:$C$118,$D5),1),0)),COLUMNS($K7:K7)+1),"")) คัดลอกไปถึง Z7 และลงล่างครับ
F7=MID(CONCATENATE(K7,",",L7,",",M7,",",N7,",",O7,",",P7,",",Q7,",",R7,",",S7,",",T7,",",U7,",",V7,",",W7,",",X7,",",Y7,",",Z7),1,SUM(LEN(K7:Z7))+15-COUNTBLANK(K7:Z7)) คัดลอกไปเซลที่เกี่ยวข้อง
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 6:05 am
by yangkodza
puriwutpokin wrote: Tue Jan 19, 2021 12:28 am
ต้องใช้คอลัมน์ช่วยครับ ษ7=IF($E7<>"เลขที่ขาด","",IFERROR(AGGREGATE(15,6,(ROW(INDIRECT("1:"&$F5))-1)/ISNA(MATCH(ROW(INDIRECT("1:"&$F5))-1,OFFSET($B$2,MATCH($D5,$C$2:$C$118,)-1,,COUNTIF($C$2:$C$118,$D5),1),0)),COLUMNS($K7:K7)+1),"")) คัดลอกไปถึง Z7 และลงล่างครับ
F7=MID(CONCATENATE(K7,",",L7,",",M7,",",N7,",",O7,",",P7,",",Q7,",",R7,",",S7,",",T7,",",U7,",",V7,",",W7,",",X7,",",Y7,",",Z7),1,SUM(LEN(K7:Z7))+15-COUNTBLANK(K7:Z7)) คัดลอกไปเซลที่เกี่ยวข้อง
ติดอยู่นิดนึงครับ พอดีห้อง ป.4/5เลขที่สุดท้ายคือ 28 หายไป แต่ในผลลัพธ์ไม่ขึ้นแสดง ผมเลยลองลบเลขที่สุดท้ายของห้องทุกห้องออกก็ไม่แสดงผลเหมือนกันครับ
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 7:16 am
by puriwutpokin
แก้ k7=IF($E7<>"เลขที่ขาด","",IFERROR(AGGREGATE(15,6,(ROW(INDIRECT("1:"&$F5+1))-1)/ISNA(MATCH(ROW(INDIRECT("1:"&$F5+1))-1,OFFSET($B$2,MATCH($D5,$C$2:$C$118,)-1,,COUNTIF($C$2:$C$118,$D5),1),0)),COLUMNS($K7:K7)+1),"")) คัดลอกทั่วตาราง
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 8:44 am
by yangkodza
puriwutpokin wrote: Tue Jan 19, 2021 7:16 am
แก้ k7=IF($E7<>"เลขที่ขาด","",IFERROR(AGGREGATE(15,6,(ROW(INDIRECT("1:"&$F5+1))-1)/ISNA(MATCH(ROW(INDIRECT("1:"&$F5+1))-1,OFFSET($B$2,MATCH($D5,$C$2:$C$118,)-1,,COUNTIF($C$2:$C$118,$D5),1),0)),COLUMNS($K7:K7)+1),"")) คัดลอกทั่วตาราง
ขอบคุณมากครับ ตอนแรกผมก็กำปั้นทุบดินเลยไม่รู้จะแก้ตรงไหน เลยใช้วิธีเพิ่มจำนวนนักเรียนทั้งห้องอีก 1 คนเหมือนกัน แต่ผมเพิ่มสดไม่ได้ไปแก้ในสูตรครับ จริงๆผมคิดว่าเอกเซลทำไม่ได้นะโจทย์นี้เพราะในมุมมองของผมมันซับซ้อนมากๆ ฟอร์มชุดนี้ที่ผมทำเป็นการเช็คนักเรียนตอบกลับจาก GoogleFrom (เรียนออนไลน์ครับ) ช่วยได้มากจริงๆเลยเอาไว้เช็คคนที่ยังไม่ได้ส่งงาน ขอขอบคุณอีกครั้งนะครับ
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 12:23 pm
by linzhengli
ผมขอสูตรเป็น 2003 บ้างครับ
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 1:41 pm
by puriwutpokin
linzhengli wrote: Tue Jan 19, 2021 12:23 pm
ผมขอสูตรเป็น 2003 บ้างครับ
2003 ปรับ K7=IF(COLUMNS($K7:K7)>$H7,"",SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:"&$F5)),OFFSET($B$1,MATCH($D5,$C$2:$C$118,),,COUNTIF($C$2:$C$118,$D5),1),0)),ROW(INDIRECT("1:"&$F5))),COLUMNS($K7:K7))) กด Ctrl+Shift+Enter คัดลอกทั่วตารางครับ
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 1:43 pm
by puriwutpokin
puriwutpokin wrote: Tue Jan 19, 2021 7:16 am
แก้ k7=IF($E7<>"เลขที่ขาด","",IFERROR(AGGREGATE(15,6,(ROW(INDIRECT("1:"&$F5+1))-1)/ISNA(MATCH(ROW(INDIRECT("1:"&$F5+1))-1,OFFSET($B$2,MATCH($D5,$C$2:$C$118,)-1,,COUNTIF($C$2:$C$118,$D5),1),0)),COLUMNS($K7:K7)+1),"")) คัดลอกทั่วตาราง
แก้ไข K7=IF($E7<>"เลขที่ขาด","",IFERROR(AGGREGATE(15,6,ROW(INDIRECT("1:"&$F5))/ISNA(MATCH(ROW(INDIRECT("1:"&$F5)),OFFSET($B$1,MATCH($D5,$C$2:$C$118,),,COUNTIF($C$2:$C$118,$D5),1),0)),COLUMNS($K7:K7)),""))
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 2:05 pm
by linzhengli
ประมวลผลแล้วขึ้น #value! ครับ
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 2:07 pm
by linzhengli
ขออภัยครับ ได้ผลลัพธ์แล้วครับ
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 2:13 pm
by linzhengli
F7,10,13,16,19 ต้องใส่สูตรเช่นไรครับ ของผมคัดลอกจากระบบแต่อ่านค่าเป็น #value! ครับ
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 3:06 pm
by puriwutpokin
linzhengli wrote: Tue Jan 19, 2021 2:13 pm
F7,10,13,16,19 ต้องใส่สูตรเช่นไรครับ ของผมคัดลอกจากระบบแต่อ่านค่าเป็น #value! ครับ
ลองแนบไฟล์มาดูครับ
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 3:09 pm
by linzhengli
ไฟล์แนบครับ
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 3:16 pm
by linzhengli
ท่านครับถ้าแบบแบบตอบกลับผมส่งไม่ไปครับ
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 3:21 pm
by puriwutpokin
ไฟล์ใหญ่ หรือเปล่าครับ ถ้าใหญ่ ก็ทำตัวอย่าง สั้นๆ มาเป็นตัวอย่างครับว่าปัญหาจากส่วนไหนถึงได้ #value!
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 3:23 pm
by linzhengli
ท่านครับส่งทางเมลให้ท่านได้หรื่อไม่ครับ
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Tue Jan 19, 2021 11:40 pm
by Bo_ry
F7
=MID(AE7,2,99)
K7:AE7
=J7&IFERROR(","&AGGREGATE(15,6,ROW($B$1:INDEX($B:$B,$F5))/ISNA(MATCH(ROW($B$1:INDEX($B:$B,$F5)),INDEX($B$2:$B$118/($C$2:$C$118=$D5),),)),COLUMNS($K7:K7)),"")
Re: หาเลขที่ที่หายไปของชุดข้อมูล
Posted: Wed Jan 20, 2021 11:15 pm
by yangkodza
เพิ่มเติมครับ ถ้าเด็กมาครบ จะแสดงค่าเป็น #value! ครับ อยากให้ซ่อนไปเลยถ้ามาครบครับผม 2013 นะครับ