[エクセル中級] 空白セルを詰める関数

      2018/04/07

空白行を詰めたいときに、それを関数で行う方法です。

関数でなく、単に空白セルを削除したいだけの場合は、[エクセル初級] 空白セルを削除(関数を使わない方法) をご覧ください。

1

マクロを使えば実現するのは簡単ですが、データが更新されるたびにマクロを実行しなければならないため、頻繁に更新されるデータや、他の人に編集されるデータの場合には関数で書いておいた方が便利です。

SPONSORED LINK

『番号!』「いっち!」「にっ!」「さんっ!」「しっ!」「ごっ!」

データの入っている列の前に1列足し、データのある行のみに順に番号を振っていきます。

データのある行のみに番号を振るために、IFでデータがあるかを分岐します。

  • データがある場合は前の行までの最大値に1を足します。
  • データがない場合は空白にします。

2-2
A2: =IF(B2<>"",MAX(A:A1)+1,"")

 先頭行のみは、「前の行までの最大値」が求められないので、データがある場合には1にします。

2-1

1番から順に並べる

データを取り出す列の横に1から順に数字を並べます(D列)。

3E3: =VLOOKUP(D3,A:B,2,FALSE)

データを取り出したい列に VLOOKUP で順にデータを取り出します。

  • 検索値:1から順に並べた数字。
  • 範囲:元のデータと、番号。行を絶対参照にするのを忘れずに。
  • 列番号:今回は2列目(A列で検索してB列を取り出す)なので、2。
  • 検索方法:数値に完全一致なので、FALSE。
SPONSORED LINK

エラー対策

単純に VLOOKUP を使うだけでは、検索値が見つからなかった場合に #N/A エラーになってしまいます。これではカッコ悪いので、IFERROR を使ってエラーの場合には空欄にします。

4E3: =IFERROR(VLOOKUP(D3,A:B,2,FALSE),"")

これでデータを詰められました。

応用編①:1列減らしましょう

数字を順番に並べただけの列(先ほどのD列)は、関数の書き方で不要にできます。

自分のセルの行番号(row(D3))と先頭のセルの行番号(row(D$1))の差に1を足すことで、これの代わりになります。

5

E3: =IFERROR(VLOOKUP(ROW(D3)-ROW(D$1)+1,A$1:B$10,2,FALSE),"")

応用編②:列番号を参照形式に

VLOOKUP 関数の列番号は 2 に固定していました。これでは、AとBの間に別のデータ列を追加した場合に、列番号を修正しなければいけません。

そこで、セルの列番号を返す COLUMN 関数を使って、固定値ではなくセルを参照する形式にします。

6

E3: =IFERROR(VLOOKUP(ROW(D3)-ROW(D$1)+1,A$1:B$10,COLUMN(B$1)-COLUMN(A$1)+1,FALSE),"")

これで、A列とB列の間隔が変化しても関数を変更する必要が無くなりました。

最後にA列を非表示にして完成です。

7

itjo レスポンシブ 本文下

 - Excel
 - , ,