swatanabe’s diary

ラノベ創作、ゲーム、アニメ、仕事の話など。仕事はwebメディアの仕組み作り・アライアンスなど。

【スプレッドシート関数】表の空白行を削除して上に詰める

やりたいこと

  • 表から空白行を削除し、上に詰める

 

 

また頼まれたので。要は、左の状態を右の状態に組み替える関数ですね。

単に空白行を抜くだけなら、[ジャンプ]コマンド→空白行を選択→行全体を選択→削除で完了。10秒もあれば終わる簡単なお仕事です。

ですが、この作業は当然、手動です。自動で表の空白行を詰めたいケースでは使えません。というわけで、関数ならどうやればいいのか、という話です。

 

関数

IFERROR(INDEX(B:B,1/LARGE(INDEX((B$2:B<>"")/ROW(B$2:B),0),ROW(A1))),"")

これをG2にコピーして、G21までオートフィル。

そこそこ複雑なので、今回はちょっとだけ丁寧に書いておきます。

 

仕組み

INDEX(B$2:B<>"")

これはB列に値が入力されていればTRUEを、されていなければFALSEを返します。

 

INDEX((B$2:B<>"")/ROW(B$2:B),0)

次に返った論理値を行番号で割ります。以前の記事でお伝えした通り、論理値は演算に組み込むと自動で1か0に変換されます。この仕様を知っておくと、かなり幅広いプロセスを実装できるようになるので、覚えておくと吉です。

計算すると、下図みたいな感じになります。

 

 

LARGE(INDEX((B$2:B<>"")/ROW(B$2:B),0),ROW(A1))

計算した結果を、LARGE関数で降順に並び替えます。とはいえ、前段の並び替える前の時点で、FALSE(値が0、すなわち空白行)の行以外は降順になっているので、0が一番下にくるだけです。

下図のようになります。

 

 

1/LARGE(INDEX((B$2:B<>"")/ROW(B$2:B),0),ROW(A1))

次に、関数全体で1を割ります。2つ目のステップで論理値TRUE、つまり1を行番号で割っているので、この作業で行番号のみが返ります。この行番号は計算過程から分かるように、論理値TRUE=データが入力されているセルの行番号です。

 

 

INDEX(B:B,1/LARGE(INDEX((B$2:B<>"")/ROW(B$2:B),0),ROW(A1)))

あとはINDEX関数で、元のフィールドでデータを引っ張りたい範囲を指定し、求めた行番号と、必要な列番号を指定すれば完了です。ERRORが鬱陶しい人は、頭にIFERROR関数でもつけておきましょう。

 

 

ちなみに、右の表のNo14と15にあるTRUEとFALSEは、左の表のNo20(ドーナツ)の数行ばかり下にTRUE、FALSEと入力されたセルがある影響です(消し忘れました)。本来は#DIV/0!が返ります。

 

とりあえず、そんなところです。

眠いので、寝ます。