やりたいこと
- 表から空白行を削除し、上に詰める
また頼まれたので。要は、左の状態を右の状態に組み替える関数ですね。
単に空白行を抜くだけなら、エクセルで［ジャンプ］コマンド→空白行を選択→行全体を選択→削除で完了。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!が返ります。
とりあえず、そんなところです。
眠いので、寝ます。
