2015年5月16日 星期六

Excel-挑出一欄中非空白的儲存格內容(陣列公式,OFFSET)

繼上一篇文章:Excel-根據某欄摘要出符合條件的結果(陣列公式,OFFSET),又有網友問到類似問題,在下圖中,想要挑出B欄中非空白的儲存格內容。由此可知這方面的問題真的困擾了很多人。

一般,我都使用「陣列公式」來處理比較多,但是很多人又對陣列公式沒有概念,無法充分理解公式的做法。所以,如果你有非用陣列公式的做法,不妨也提供廣大讀者參考。

【設計公式】

儲存格D2:{=OFFSET($B$1,SMALL(IF(B$2:B$20<>"",ROW(B$2:B$20),9999),
ROW(1:1))-1,,,)}

這是陣列公式,輸入完成要 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。

公式分解:

IF(B$2:B$20<>"",ROW(B$2:B$20),9999):找出儲存格陣列中內容不是空白者,符合者傳回列數(第幾列),不符合者傳回一個很大的值(本例為9999,資料總列數不超過該數。)

SMALL(IF(B$2:B$20<>"",ROW(B$2:B$20),9999),ROW(1:1)):利用 SMALL 函數在D欄的每一列中由小到大依序找出符合的列數。

OFFSET($B$1,SMALL(IF(B$2:B$20<>"",ROW(B$2:B$20),9999),ROW(1:1))-1,,,):將上式的結果置入 OFFSET 函數,以儲存格B1為起點,逐一由相對位置取得結果。

複製儲存格D2,貼至儲存格D2:D20。

若依本題的題意,公式更改為如下寫法,結果也是一樣的:

儲存格D2:{=OFFSET($B$1,SMALL(IF(A$2:A$20>=70,ROW(A$2:A$20),9999),
ROW(1:1))-1,,,)}

其差異在於公式中將條件 B$2:B$20<>"" 改為 A$2:A$20>=70。

沒有留言:

張貼留言

檢視其他文章

好康東東