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

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

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

Excel：空白セルを詰めて表示する方法  - 教えて！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で調整しています。

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

お読みいただきありがとうございます！

WordTOMOからのお知らせ

• 当ブログの記事内容は、作成時点の情報に基づいています。そのため、記事が公開された後に情報が変更される場合があります。最新の情報は各自でご確認ください。
• 記事の内容は筆者が調査した結果に基づいていますが、必ずしも完全・正確であるとは限りません。参考情報としてご活用いただき、最終的な判断はご自身の責任で行ってください。
• 記事の内容に具体的な誤りや不足がある場合は、コメント欄またはお問い合わせフォームからお知らせいただければ幸いです。
• 当ブログをお読みいただいた評価やコメントをお寄せいただけると励みになります。以下の投票ボタンからぜひご感想をお寄せください！