空白を除外してデータのみを抽出

2013Excel関数技BEST 73回

空白以外のデータを抽出したい

空白を含むデータがA列に入力されています。そこから、データだけを抽出して、別セルに取り出す方法を考えます。
方法としては、1.関数と作業列を使う方法、2.配列を使う方法の2通りを考えます。
空白以外のデータだけ取り出す
(サンプルファイルは、こちらから 2013関数技73回サンプルデータ

範囲から位置を指定して値を取り出す

INDEX関数は、範囲の中で、指定した行位置、列位置にあるセルの内容を返す関数です。
この関数を使うと範囲とその範囲の行番号がわかれば値を取り出せます。結果を見てみましょう。
空白以外のデータだけ取り出す

作業セルを使ってデータのある位置に番号を振る

これから、データ範囲の行番号をどうやって導くかを考えます。 大変そうな時は作業列を用意しましょう。 B列を作業列として使って、関数でA列にデータがある場合に番号をふるようにしましょう。
空白以外のデータを抽出する1
B2セルにIF関数を使って次の数式を入力します。
=IF(A2="","",MAX(B$1:B1)+1)
今まで何度も登場しているIF関数の書式は、
IF(論理式,真の場合,偽の場合)ですが、
サンプルでは、=IF(A2="","","偽の場合")のようにしています。
特に、偽の場合がポイントです。
そこに、B列に番号をふる数式の部分、MAX(B$1:B1)+1をネストしているからです。
空白以外のデータを抽出する2
数式を下までコピーすると、作業列としたB列に番号が振られます。この番号の左のセルにはデータが入っています。また、番号の最大値はデータの個数ということになります。
空白以外のデータを抽出する3

INDEX関数で指定された位置の値を取り出す

作業セルの範囲B$2:B$12と、 その範囲に入力された番号を利用して、最初のINDEX関数の数式をうまく組み立ててみます。
サンプルの例では、入力された番号は4までですから、抽出する個数も4つ。A20セルには1番の値を、A21セルには2番の値が入ればいいわけです。 その1番から4番までをA20セルに適用するのには、ROW関数を使うことができます。
空白以外のデータを抽出する4
A20セルに入力された式を解説します。初めの=IF(ROW(A1)>MAX(B$2:B$12),"",偽の場合)の部分で、作業列の範囲の最大値を超えたら、データはないので、表示しません。
後半の式偽の場合の部分でINDEX関数を使っています。 INDEX(A$2:A$12,MATCH(ROW(A1),B$2:B$12,0))でA列の参照から、行番号の値を探します。どんな行番号かというとMATCH関数で作業セルに入っているROW(A1)がある場所ということになります。
空白以外のデータを抽出する5