2012年10月2日 星期二

Excel-列出含有某字的資料(陣列)

有網友問到:在一堆資料中要取出某些特定資料,並重新排列,該如何處理?例如以下圖為例,要取出姓名是姓「蔡」的姓名重新排列。

【準備工作】

選取所有座號和姓名的儲存格,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:座號、姓名。

【輸入公式】

儲存格D2:{=OFFSET(姓名,SMALL(IF(LEFT(姓名,1)="蔡",座號,999),ROW(1:1))-1,,,)}

這是陣列公式,輸入完成要按 Ctrl+Shfit+Enter 鍵。

IF(LEFT(姓名,1)="蔡",座號,999):使用 LEFT 函數找出姓名第1個字是「蔡」的座號陣列,如果不是姓蔡,就設定為999(總人數不超過999),得到一個座號和999組成的陣列。

SMALL(IF(LEFT(姓名,1)="蔡",座號,999),ROW(1:1)):ROW(1:1)=1向下複製時,會產生ROW(2:2)=2、ROW(3:3)、…,取出上式陣列中第1小(6)、第2小(11)、第3小(16)、…的座號。

最後透過 OFFSET 函數,取出姓名。

 

【補充資料】

詳細函式說明,請參閱微軟網站:

OFFSEThttp://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

OFFSET 函數:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照位址。

Rows:左上角儲存格要往上或往下參照的列數。Rows可以是正數(表示在起始參照位址下方)或負數(表示在起始參照位址上方)

Cols:左上角儲存格要往左或往右參照的欄數。Cols 可以是正數(表示在起始參照位址右方)或負數(表示在起始參照位址左方)

Height:所傳回參照位址的高度 (以列數為單位)Height 必須是正數。

Width:所傳回參照位址的寬度 (以欄數為單位)Width 必須是正數。

沒有留言:

張貼留言

好康東東