Snasui Excel Forum

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

สามารถทำได้ทุกอย่างครับ

ลองทำตัวอย่างคำตอบ และสิ่งที่ต้องการมาดูครับ ผมจะช่วยอีกแรง

มีวิธีทำที่ไม่ยากมากคือใช้ PivotTable มาช่วยได้ครับ แต่ต้องปรับชีท Fสรุปผลคะแนนให้เรียงรายวิชาลงด้านล่าง เป็นแนวคอลัมน์ไม่ใช่เรียงไปด้านขวาเช่นที่ทำมานี้ โดยชื่อและนามสกุลจะซ้ำกันตามจำนวนรายวิชา

ที่ผมทำมาเป็นตัวอย่างนี้เป็นการใช้สูตรดึงข้อมูล ซึ่งถือว่าเป็นสูตรที่ยากมากสำหรับผู้ไม่คุ้นเคย โดยมีวิธีการทำคร่าว ๆ ตามด้านล่าง

1. ทำ Validation ที่ F1 โดยนำข้อมูลรายวิชาที่ K3:K12 มาใช้
2. ที่ A4 คีย์สูตรเพื่อให้ค่าลำดับ
   =IF(LEN(B4)>1,ROWS(A$4:A4),"")
   Enter > Copy ลงด้านล่างและ Copy ไปวางที่ D4:D33, G4:G33
3. ที่ B4 คีย์สูตรเพื่อดึงข้อมูล
   =LOOKUP(CHAR(255),CHOOSE({1,2},"",INDEX('Fสรุปผลคะแนน '!B$5:B$59,SMALL(IF(INDEX('Fสรุปผลคะแนน '!$D$5:$W$59,0,MATCH($F$1,'Fสรุปผลคะแนน '!$D$3:$W$3,0)+1)>=3,ROW('Fสรุปผลคะแนน '!$B$5:$B$59)-ROW('Fสรุปผลคะแนน '!$B$5)+1),ROWS(B$4:B4)))))
   Ctrl+Shfit+Enter > Copy ไปที่ C4 แล้ว Copy ลงด้านล่าง
4. เซลล์ E4 คีย์สูตรเพื่อดึงข้อมูล
   =LOOKUP(CHAR(255),CHOOSE({1,2},"",INDEX('Fสรุปผลคะแนน '!B$5:B$59,SMALL(IF(INDEX('Fสรุปผลคะแนน '!$D$5:$W$59,0,MATCH($F$1,'Fสรุปผลคะแนน '!$D$3:$W$3,0)+1)>=1,IF(INDEX('Fสรุปผลคะแนน '!$D$5:$W$59,0,MATCH($F$1,'Fสรุปผลคะแนน '!$D$3:$W$3,0)+1)<3,ROW('Fสรุปผลคะแนน '!$B$5:$B$59)-ROW('Fสรุปผลคะแนน '!$B$5)+1)),ROWS(E$4:E4)))))
   Ctrl+Shfit+Enter > Copy ไปที่ F4 แล้ว Copy ลงด้านล่าง
5. เซลล์ H4 คีย์สูตรเพื่อดึงข้อมูล
   =LOOKUP(CHAR(255),CHOOSE({1,2},"",INDEX('Fสรุปผลคะแนน '!B$5:B$59,SMALL(IF(INDEX('Fสรุปผลคะแนน '!$D$5:$W$59,0,MATCH($F$1,'Fสรุปผลคะแนน '!$D$3:$W$3,0)+1)=0,IF(INDEX('Fสรุปผลคะแนน '!$D$5:$W$59,0,MATCH($F$1,'Fสรุปผลคะแนน '!$D$3:$W$3,0)+1)<>"",ROW('Fสรุปผลคะแนน '!$B$5:$B$59)-ROW('Fสรุปผลคะแนน '!$B$5)+1)),ROWS(H$4:H4)))))
   Ctrl+Shfit+Enter > Copy ไปที่ I4 แล้ว Copy ลงด้านล่าง

ดูไฟล์แนบประกอบครับ

ขอเสนอวิธีใช้สูตรธรรมดาแบบนี้เพื่อจำแนกกลุ่ม
=MATCH(1,--(OFFSET(C5,0,MATCH($AE$2,$D$3:$W$3,0)+1)<={0,2.5,4}),0)*ISNUMBER(D5)

แยกกลุ่มได้แล้วก็สามารถเลือกจะใช้วิธีรายงานได้หลายแบบ เช่น
1.Advance Filter Copy
2.VBA
3.สูตรธรรมดา (ตามตัวอย่างที่แนบ)

แถมให้อีกวิธีครับ ลดดีกรีความยากของสูตรลงมาอีกหน่อย แต่เพิ่มขั้นตอนการทำให้ง่ายต่อการเข้าใจ

โดยการปรับตารางข้อมูลดิบให้อยู่ในสถานะใช้งานง่าย ขั้นตอนแรกคือยกเลิกการประสานเซลล์ โดยเฉพาะหัวข้อวิชาต่าง
ให้ตรงกับคอลัมน์ที่ทำสีเหลืองไว้

จากนั้นขยับมาคอลัมน์สุดท้าย ใช้ทดสอบเงื่อนไขและรับข้อมูลจากชีท "ผลการประเมิน" ครับ
ขั้นตอนการทำดังนี้
ที่ BI5 คีย์ =$BL5>=3 คัดลอกลงมา
ที่ BJ5 คีย์ =IF($BL5=0,FALSE,$BL5<=2.5) คัดลอกลงมา
ที่ BK5 คีย์ =$BL5=0 คัดลอกลงมา
ขั้นตอนดังกล่าวทำเพื่อทดสอบเงื่อนไขว่าเป็น True,False

จากนั้น ทำการนับเงื่อนไขที่เป็น True เพื่อใช้เป็นคีย์หลักในการดึงข้อมูลต่อไป
โดยที่ BI4,BJ4,BK4 คีย์ =COUNTIF(BI$5:BI$38,TRUE) =COUNTIF(BJ$5:BJ$38,TRUE) =COUNTIF(BK$5:BK$38,TRUE) ตามลำดับ

รับข้อมูล จากชีท "ผลการประเมิน" เซลล์ F1 โดยคีย์สูตร =ผลการประเมิน!F1 ที่ BL4
แสดงข้อมูลเกรดที่ตรงกับเงื่อนไขชื่อวิชา ที่ BL5 คีย์ =HLOOKUP($BL$4,$D$3:$Y$38,ROW(E5)-2,0) คัดลอกลงมา
เสร็จขั้นตอนนี้ ข้อมูลก็พร้อมนำไปแสดงแล้วครับ

ที่ชีท "ผลการประเมิน"
ที่ F1 ทำ Dropdown List ชื่อวิชา (เหมือนกับไฟล์แนบของอาจารย์คนควน")
ที่ A4 คีย์ =IF(ROWS(A$4:$A4)>'Fสรุปผลคะแนน '!$BI$4,"",INDEX('Fสรุปผลคะแนน '!A$5:A$38,SMALL(IF('Fสรุปผลคะแนน '!$BI$5:$BI$38=TRUE,ROW('Fสรุปผลคะแนน '!$B$5:$B$38)-ROW('Fสรุปผลคะแนน '!$B$5)+1),ROWS(A$4:$A4)))) กด Ctrl+Shift+Enter คัดลอกไปทางขวา 2 คอลัมน์

ที่ D4 คีย์ =IF(ROWS(D$4:$D4)>'Fสรุปผลคะแนน '!$BJ$4,"",INDEX('Fสรุปผลคะแนน '!A$5:A$38,SMALL(IF('Fสรุปผลคะแนน '!$BJ$5:$BJ$38=TRUE,ROW('Fสรุปผลคะแนน '!$B$5:$B$38)-ROW('Fสรุปผลคะแนน '!$B$5)+1),ROWS(D$4:$D4)))) กด Ctrl+Shift+Enter คัดลอกไปทางขวา 2 คอลัมน์

ที่ G4 คีย์ =IF(ROWS(G$4:$G4)>'Fสรุปผลคะแนน '!$BK$4,"",INDEX('Fสรุปผลคะแนน '!A$5:A$38,SMALL(IF('Fสรุปผลคะแนน '!$BK$5:$BK$38=TRUE,ROW('Fสรุปผลคะแนน '!$B$5:$B$38)-ROW('Fสรุปผลคะแนน '!$B$5)+1),ROWS(G$4:$G4)))) กด Ctrl+Shift+Enter คัดลอกไปทางขวา 2 คอลัมน์

เป็นอันเสร็จสิ้น ทดสอบเลือกชื่อวิชาในเซลล์ F1 ชื่อนักเรียนจะเปลี่ยนไปตามเงื่อนไขที่วางไว้