1057EXCEL查詢某儲存格所對應過去的欄列名稱嗎?

錦子老師
·
·
IPFS
·

豬八戒詢問唐三藏:「師傅,資料表資料如下:

請問一下,如果今天想作一個查詢或是報表,有函數或方法可以讓我在某一儲存格輸入 " 張三元 " 然後可以抓取張三元上了那些班的什麼課嗎?也就是輸入某一字串,它會自動去抓去在那個資料表的那些儲存格出現過,並全部列出。」

唐三藏:「其實這需要一堆函數的組合應用,下面我們來討論一下。」

點取I2儲存格輸入要找尋的學員姓名。

點取J2儲存格輸入公式「=IF(COUNTIF(A:F,$I$2)>=ROW()-1,INDEX($1:$1,SMALL(IF (A$1:F$6=I$2,COLUMN(A:F)),ROW()-1)),"")」後,按ENTER鍵完成輸入,並將公式複製到J3:J6儲存格。

【公式說明】

1.COUNTIF(A:F,$I$2)>=ROW()-1 判斷I2儲存格內容A在欄到F欄中的個數是否大於或等於目前儲存格的列號減1。

2.IF(A$1:F$6=I$2,COLUMN(A:F)) 傳回A2欄到F欄中I2儲存格內容位於那些欄。

3.SMALL(IF (A$1:F$6=I$2,COLUMN(A:F)),ROW()-1) 傳回2.中計算出來第幾小的值(目前儲存格列號減1)。

4.INDEX($1:$1,SMALL(IF (A$1:F$6=I$2,COLUMN(A:F)),ROW()-1)) 搜尋第一列中第N欄的值(3.計算出來)

5.=IF(COUNTIF(A:F,$I$2)>=ROW()-1,INDEX($1:$1,SMALL(IF(A$1:F$6=I$2,COLUMN (A:F)),ROW()-1)),"") 如果1.計算結果成立,傳回4.的結果,否則不輸入資料。

點取K2儲存格輸入公式「=IF(COUNTIF(A:F,$I$2)>=ROW()-1,INDEX($1:$1,SMALL(IF (A$1:F$6=I$2,COLUMN(A:F)),ROW()-1)),"")」後,按ENTER鍵完成輸入,並將公式複製到K3:K6儲存格。

豬八戒:「哇!謝謝師傅!」

唐三藏:「不客氣。」







CC BY-NC-ND 2.0 授权

喜欢我的作品吗?别忘了给予支持与赞赏,让我知道在创作的路上有你陪伴,一起延续这份热忱!