學生依班級自動篩選資料出來

imingho

請問假設我有一份excel,有個工作表(Teacher)有著老師及其班級名稱共兩個欄位,
另一個工作表(Student)有一份學生的名單,但學生名單中卻含有不同班級的學生
我想要就是利用老師當依據來篩選出其班級下的學生有誰,來匯出各老師的班級學生excel       請問該怎麼做   感恩~

說明及預想.zip (2.93 KB)

測試資料
下載次數: 12

2013-5-8 11:33

下載 (55.83 KB)

2013-5-8 11:32

1.學生班級名單

下載 (49.15 KB)

2013-5-8 11:32

2.是可以直接用公式產生結果

准提部林

陣列．適Ａ欄皆為文字．不防Ｂ欄空格：
=INDEX(Student!A:A,SMALL(IF(ISERR(FIND(Student!$B$2:$B$20,$D$2)),4^8,ROW($A$2:$A$20)),ROW(A1)))&""

陣列．排除FIND對空格的誤判：
=INDEX(Student!A:A,SMALL(IF(ISERR(FIND(TEXT(Student!$B$2:$B$20,"嫺"),$D$2)),4^8,ROW($A$2:$A$20)),ROW(A1)))&""

全域陣列：將公式貼入Ａ４，再全選Ａ４：Ｂ２０，進Ａ４成編輯狀態，Ctrl + Shift → Enter 三鍵齊按
=INDEX(Student!A:B,SMALL(IF(ISERR(FIND(Student!B2:B20,D2)),4^8,ROW(2:20)),ROW(1:20)),{1,2})&""

非EXCEL正科，只是撿別人公式修修改改，僅供參考！

Bodhidharma

回復 7# shan0948

一些基本的用法可參考：
http://office.microsoft.com/zh-tw/excel-help/HA010228458.aspx#BM1

imingho

回復 5# ML089


    謝謝ML089,讓我又學了一招.

ML089

A4:B18 全選式陣列

1. =INDEX(Student!A:B,MOD(SMALL(IF(ISERR(FIND(Student!B$2:B$20,Class!D$2)),4^8,FIND(Student!B$2:B$20,Class!D$2)*100+ROW($2:$20)),ROW(1:15)),100),{1,2})&""

複製代碼

本例預估最大人數為15人所以用 ROW(1:15)，此部分需求自定

一般陣列公式每一格都需計算一次及排列一次非常耗時，當資料筆數超過千筆時更為明顯，
例如一位老師上5個班每班40人時，公式執行速度就會相差 5*40*2 = 400倍。

所公式的應視資料的多寡及計算量需求選用適當的陣列公式
資料量多時要減少重複性計算，一般可以用輔助欄或全選式陣列來解決。

ML089

增加排序，將同班同學排列再一起

A4:B4 陣列公式

1. =INDEX(Student!A:B,MOD(SMALL(IF(ISERR(FIND(Student!B$2:B$20,Class!D$2)),4^8,FIND(Student!B$2:B$20,Class!D$2)*100+ROW($2:$20)),ROW()-3),100),{1,2})&""

複製代碼

注意! 公式為雙格公式，要同時選取 A4:B4 ，輸入公式後用 CTRL+SHIFT+ENTER三鍵齊按方式輸入，然後再同選A4:B4下拉複製

PS:
雙格公式可以減少一半的陣列計算時間
是否可以再使陣列計算更快，可以的，使用全選式陣列公式。

有點事等一下再說明

imingho

回復 2# Hsieh


謝謝,    Hsieh 結果跟我想要的是一樣的.

Hsieh

回復 1# imingho
A4陣列公式
=IF(SUMPRODUCT(ISNUMBER(FIND(Student!$B$2:$B$20,Class!$D$2))*1)>=ROW(A1),INDEX(Student!A:A,SMALL(IF(ISNUMBER(FIND(Student!$B$2:$B$20,Class!$D$2)),ROW(Student!$A$2:$A$20),""),ROW(A1)),),"")