　暱稱: 彰化一整天

帖子: 56
主題: 11
精華: 0
積分: 115
點名: 0
作業系統: windows
軟體版本: windows 7
閱讀權限: 20
性別: 男
註冊時間: 2013-3-13
最後登錄: 2023-7-13

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2013-5-8 11:38 | 只看該作者

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

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

說明及預想.zip (2.93 KB)

1.學生班級名單

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

樓主熱帖

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 246
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-12

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

2^#

發表於 2013-5-8 14:29 | 只看該作者

回復 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)),),"")

學海無涯_不恥下問

TOP

imingho

　暱稱: 彰化一整天

中學生 Rank: 2

帖子: 56
主題: 11
精華: 0
積分: 115
點名: 0
作業系統: windows
軟體版本: windows 7
閱讀權限: 20
性別: 男
註冊時間: 2013-3-13
最後登錄: 2023-7-13

3^#

發表於 2013-5-9 08:30 | 只看該作者

回復 2# Hsieh

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

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

4^#

發表於 2013-5-10 23:48 | 只看該作者

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

A4:B4 陣列公式

=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:
雙格公式可以減少一半的陣列計算時間
是否可以再使陣列計算更快，可以的，使用全選式陣列公式。

有點事等一下再說明

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

5^#

發表於 2013-5-11 00:32 | 只看該作者

A4:B18 全選式陣列

=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倍。

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

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

imingho

　暱稱: 彰化一整天

中學生 Rank: 2

帖子: 56
主題: 11
精華: 0
積分: 115
點名: 0
作業系統: windows
軟體版本: windows 7
閱讀權限: 20
性別: 男
註冊時間: 2013-3-13
最後登錄: 2023-7-13

6^#

發表於 2013-5-13 18:32 | 只看該作者

本帖最後由 imingho 於 2013-5-13 18:33 編輯

回復 5# ML089

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

TOP

Bodhidharma

中學生 Rank: 2

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

7^#

發表於 2013-5-18 22:00 | 只看該作者

回復 7# shan0948

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

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2833
主題: 10
精華: 0
積分: 2889
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-12

8^#

發表於 2013-5-18 23:45 | 只看該作者

陣列．適Ａ欄皆為文字．不防Ｂ欄空格：
=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正科，只是撿別人公式修修改改，僅供參考！

TOP