หาเลขที่ที่หายไปของชุดข้อมูล

[yangkodza]

z01.PNG

จากภาพต้องการหาตัวเลขที่หายไปในแต่ละห้อง เช่น F7 ต้องได้คำตอบเป็น 10,15,20 แบบนี้ครับจะได้ไม่ต้องเสียเวลามาไล่หาตัวเลขที่หายไปเอง cell ที่ต้องการหาคำตอบ คือ F7,F10,F13,F16,F19 ขอบคุณมากครับ

หาเลขที่หายไป.xlsx

[puriwutpokin]

แจ้ง Version Excel ด้วยครับ
https://snasui.com/viewtopic.php?f=6&t=17480

[yangkodza]

แจ้ง Version Excel ด้วยครับ
https://snasui.com/viewtopic.php?f=6&t=17480

ตั้งค่าเรียบร้อยครับผม

[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)) คัดลอกไปเซลที่เกี่ยวข้อง

[yangkodza]

ต้องใช้คอลัมน์ช่วยครับ ษ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 หายไป แต่ในผลลัพธ์ไม่ขึ้นแสดง ผมเลยลองลบเลขที่สุดท้ายของห้องทุกห้องออกก็ไม่แสดงผลเหมือนกันครับ

[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),"")) คัดลอกทั่วตาราง

[yangkodza]

แก้​ 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 (เรียนออนไลน์ครับ) ช่วยได้มากจริงๆเลยเอาไว้เช็คคนที่ยังไม่ได้ส่งงาน ขอขอบคุณอีกครั้งนะครับ

[linzhengli]

ผมขอสูตรเป็น 2003 บ้างครับ

[puriwutpokin]

ผมขอสูตรเป็น 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 คัดลอกทั่วตารางครับ

[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),"")) คัดลอกทั่วตาราง

แก้ไข 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)),""))

[linzhengli]

ประมวลผลแล้วขึ้น #value! ครับ

[linzhengli]

ขออภัยครับ ได้ผลลัพธ์แล้วครับ

[linzhengli]

F7,10,13,16,19 ต้องใส่สูตรเช่นไรครับ ของผมคัดลอกจากระบบแต่อ่านค่าเป็น #value! ครับ

[puriwutpokin]

F7,10,13,16,19 ต้องใส่สูตรเช่นไรครับ ของผมคัดลอกจากระบบแต่อ่านค่าเป็น #value! ครับ

ลองแนบไฟล์มาดูครับ

[linzhengli]

ไฟล์แนบครับ

[linzhengli]

ท่านครับถ้าแบบแบบตอบกลับผมส่งไม่ไปครับ

[puriwutpokin]

ไฟล์ใหญ่ หรือเปล่าครับ ถ้าใหญ่ ก็ทำตัวอย่าง สั้นๆ มาเป็นตัวอย่างครับว่าปัญหาจากส่วนไหนถึงได้ #value!

[linzhengli]

ท่านครับส่งทางเมลให้ท่านได้หรื่อไม่ครับ

[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)),"")

[yangkodza]

เพิ่มเติมครับ ถ้าเด็กมาครบ จะแสดงค่าเป็น #value! ครับ อยากให้ซ่อนไปเลยถ้ามาครบครับผม 2013 นะครับ