【Excel】INDEX関数とSMALL関数の配列数式で空白セルを詰める方法（ファイル付）

エクセルは親のかたき（by WordTOMO）

ただ、空白セルを詰める方法は長い間こちらのサイトにお世話になっています。

Excel2010-2016：空白セルを詰めて表示する方法  - 教えて！HELPDESK

空白セルを詰めてデータベースを別領域に表示：フィルタオプション

office-qa.com

しばらくこの数式について理解できませんでしたが。

ここからさらに空白セルを詰めた後の表示を別の場所にしたいとか。
縦に詰めるのではなく、横に詰める場合などについて検討を重ねました。

・空白セルを上に詰める
・空白セルを横に詰める
・空白セル

エクセル２０１６の環境での調査となっています。

あと始める前に、配列数式のほうは大丈夫でしょうか。
配列数式なので関数の入力後は（CTRL+SHIFT+ENTER）をしてください。
配列数式をやめる場合は、範囲全体を選択しなおした後に（CTRL+ENTER）です。

① 縦に詰める

基本形です。

=IFERROR(INDEX(A:A,SMALL(IF(A:A<>””,ROW(A:A)),ROW())),””)

C1からC9までを選択した状態で、上記の数式を貼り付け。
入力の最後はエンターではなく、（CTRL+SHIFT+ENTER）にしてください。

② 縦に詰める（異行）

実際の活用場面では、タイトル行がある場合など、行のズレを考慮する必要があります。

行が同じ場合は問題が発生していません。
開始行が異なる場合は、値の変更が必要です。

=IFERROR(INDEX(A:A,SMALL(IF(A:A<>””,ROW(A:A)),ROW()-3)),””)
=IFERROR(INDEX(A:A,SMALL(IF(A:A<>””,ROW(A:A)),ROW()-2)),””)
=IFERROR(INDEX(A:A,SMALL(IF(A:A<>””,ROW(A:A)),ROW()-1)),””)

（※表示側のROW()-?だけで調整しています。）

③ 横に詰める

=IFERROR(INDEX(1:1,SMALL(IF(1:1<>””,COLUMN(1:1)),COLUMN())),””)

行の指定を1:1に、ROW()からCOLUMN()に変更。

④ 横に詰める（異行）

=IFERROR(INDEX(1:1,SMALL(IF(1:1<>””,COLUMN(1:1)),COLUMN()-3)),””)
=IFERROR(INDEX(1:1,SMALL(IF(1:1<>””,COLUMN(1:1)),COLUMN()-2)),””)
=IFERROR(INDEX(1:1,SMALL(IF(1:1<>””,COLUMN(1:1)),COLUMN()-1)),””)

（※表示側のCOLOMN()-?だけで調整しています。）

⑤ 列から行に

=IFERROR(INDEX(A:A,SMALL(IF(A:A<>””,ROW(A:A)),COLUMN()-2)),””)

ROW()とCOLOMN()-?がミックスされるとともに、表示開始のズレを-2で調整しています。

⑥ 行から列に

=INDEX(1:1,SMALL(IF(1:1<>””,COLUMN(1:1)),ROW()-2))

ROW()とCOLOMN()-?がミックスされるとともに、表示開始のズレを-2で調整しています。

サンプルファイルのダウンロードはこちら。

[yop_poll id=”1″]