2015年8月18日 星期二

Excel-略過空白儲存格重新列出清單(ISTEXT,OFFSET,INDEX,SMALL)

如下圖,在 Excel 要將一欄的內容中全部空白儲存格抽離,重新依序排列,該如何處理?
已提出數種可行的方式和公式,這次再補充不同的方式和公式。
註:本例假設清單內容都是文字而沒有數字。

1. 使用陣列公式
(1)
儲存格A2:{=IFERROR(OFFSET($A$2,SMALL(IF(ISTEXT($A$2:$A$17),
ROW($A$1:$A$16)), ROW(A1))-1,,,),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
ISTEXT($A$2:$A$17):在陣列公式中判斷儲存格範圍中是否為文字(不包含空白),傳回 TRUE/FALSE 陣列。
IF(ISTEXT($A$2:$A$17),ROW($A$1:$A$16)), ROW(A1)):如果上述結果為 TRUE,則傳回列號,否則依序傳回 1, 2, 3, ...。(ROW(A1)=1、ROW(A2)=2、...)
再透過 OFFSET 函數取得儲存格內容,而 IFERROR 函數乃是要將查詢結果傳為的錯誤訊息,改以空白顯示。
(2)
儲存格A2:{=IFERROR(INDEX($A$2:$A$17,SMALL(IF(ISTEXT($A$2:$A$17),
ROW($A$1:$A$16)), ROW(A1))),"")}
原理同 (1),不同處為透過 INDEX 函數取得儲存格內容,而 IFERROR 函數乃是要將查詢結果傳為的錯誤訊息,改以空白顯示。

2. 使用手動方式
手動方式乃主要是利用表格中的「刪除重覆」功能。
先將儲存格A1:A17轉換為表格:
再使用「移除重覆」功能:
最後再刪除第一個空白列即可。

沒有留言:

張貼留言

好康東東