[エクセル中級] 空白セルを詰める関数
2018/04/07
空白行を詰めたいときに、それを関数で行う方法です。
関数でなく、単に空白セルを削除したいだけの場合は、[エクセル初級] 空白セルを削除(関数を使わない方法) をご覧ください。
マクロを使えば実現するのは簡単ですが、データが更新されるたびにマクロを実行しなければならないため、頻繁に更新されるデータや、他の人に編集されるデータの場合には関数で書いておいた方が便利です。
『番号！』「いっち！」「にっ！」「さんっ！」「しっ！」「ごっ！」
データの入っている列の前に1列足し、データのある行のみに順に番号を振っていきます。
データのある行のみに番号を振るために、IFでデータがあるかを分岐します。
- データがある場合は前の行までの最大値に1を足します。
- データがない場合は空白にします。
A2: =IF(B2<>"",MAX(A:A1)+1,"")
先頭行のみは、「前の行までの最大値」が求められないので、データがある場合には1にします。
1番から順に並べる
データを取り出す列の横に1から順に数字を並べます（D列）。
E3: =VLOOKUP(D3,A:B,2,FALSE)
データを取り出したい列に VLOOKUP で順にデータを取り出します。
- 検索値：1から順に並べた数字。
- 範囲：元のデータと、番号。行を絶対参照にするのを忘れずに。
- 列番号：今回は2列目（A列で検索してB列を取り出す）なので、2。
- 検索方法：数値に完全一致なので、FALSE。
エラー対策
単純に VLOOKUP を使うだけでは、検索値が見つからなかった場合に #N/A エラーになってしまいます。これではカッコ悪いので、IFERROR を使ってエラーの場合には空欄にします。
E3: =IFERROR(VLOOKUP(D3,A:B,2,FALSE),"")
これでデータを詰められました。
応用編①：1列減らしましょう
数字を順番に並べただけの列（先ほどのD列）は、関数の書き方で不要にできます。
自分のセルの行番号（row(D3)）と先頭のセルの行番号（row(D$1)）の差に1を足すことで、これの代わりになります。
E3: =IFERROR(VLOOKUP(ROW(D3)-ROW(D$1)+1,A$1:B$10,2,FALSE),"")
応用編②：列番号を参照形式に
VLOOKUP 関数の列番号は 2 に固定していました。これでは、AとBの間に別のデータ列を追加した場合に、列番号を修正しなければいけません。
そこで、セルの列番号を返す COLUMN 関数を使って、固定値ではなくセルを参照する形式にします。
E3: =IFERROR(VLOOKUP(ROW(D3)-ROW(D$1)+1,A$1:B$10,COLUMN(B$1)-COLUMN(A$1)+1,FALSE),"")
これで、A列とB列の間隔が変化しても関数を変更する必要が無くなりました。
最後にA列を非表示にして完成です。
