Excel公式練習32： 將包含空單元格的多行多列單元格區域轉換成單獨的列並去掉空單元格

• 2020 年 2 月 18 日
• 筆記

本次的練習是：如下圖1所示，單元格區域A1:D6中是一系列數據，其中包含空單元格，現在要將它們放置到一列中，並刪除空單元格，如圖中所示的單元格區域G1:G13，如何使用公式實現？

圖1

先不看答案，自已動手試一試。

公式

1.先將單元格區域A1:D6命名為「rngData」。

2.在單元格F1中輸入公式：

=SUM(COUNTIF(rngData,{">0","?*"}))

統計單元格區域A1:D6中非空單元格的數量。並將該單元格作為輔助單元格。

3.在單元格G1中輸入數組公式：

=IF(ROWS($1:1)>$F$1,"",INDIRECT(TEXT(SMALL(IF(rngData<>"",10^5*ROW(rngData)+COLUMN(rngData)),ROWS($1:1)),"R0C00000"),0))

向下拖拉至出現空單元格為止。

公式解析

公式中的：

ROWS($1:1)

隨著公式的下拉，其值會發生變化，在第1行為ROWS($1:1)，結果為1；第2行為ROWS($1:2)，結果為2；第3行為ROWS($1:3)，結果為3，依此類推。因此，如果結果大於單元格F1中的值，則公式結果為空，否則執行IF語句的第2部分。

IF(rngData<>"",10^5*ROW(rngData)+COLUMN(rngData))

這個IF語句rngData<>」」部分的值為：

IF({TRUE,TRUE,FALSE,TRUE;FALSE,TRUE,TRUE,TRUE;TRUE,FALSE,TRUE,FALSE;FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE}

10^5*ROW(rngData)+COLUMN(rngData)部分的值為：

{100001,100002,100003,100004;200001,200002,200003,200004;300001,300002,300003,300004;400001,400002,400003,400004;500001,500002,500003,500004;600001,600002,600003,600004}

其結果為{100001,100002,FALSE,100004;FALSE,200002,200003,200004;300001,FALSE,300003,FALSE;FALSE,FALSE,400003,FALSE;FALSE,500002,500003,500004;FALSE,FALSE,FALSE,600004}

上述IF函數的結果作為SMALL函數的第一個參數值，其第2個參數ROWS($1:1)隨行號變化，對於第1行為1，結果為100001；第2行為2，結果為100002；第3行為3，結果為100004，等等，依此類推。

SAMLL函數的結果作為TEXT函數的參數，例如第3行：

TEXT(100004,」R0C00000」)

結果為R1C00004。

這個結果傳遞給INDIRECT函數：

INDIRECT(「R1C00004」,0)

結果將取出第1行第4列中的值，即單元格D4中的值。

為什麼選用10^5，並且使用R0C00000作為格式字元串呢？使用足夠大的數值，主要是為了考慮行和列擴展後能夠準確地取出相應行列所在單元格的數據。

注意到，在TEXT函數中，先填充C之後的五個零，剩下的在填充R之後的部分。

其他解

還可以使用公式：

=IFERROR(INDIRECT(TEXT(SMALL(IF(rngData<>"",10^5*ROW(rngData)+COLUMN(rngData)),ROWS($1:1)),"R0C00000"),0),"")

這個公式不需要輔助列。直接在單元格G1中輸入，然後下拉至得到的結果為空為止。

這個公式的缺點是，當下拉很多行時，如果有許多行都為空，則仍會進行很多的計算，佔有資源，不會像前面給出的公式，第一個IF判斷為大於非空單元格值後，直接輸入空值。有興趣的朋友可以仔細研究。