空白以外のセルを詰めて抽出する方法｜値のあるセルのみ全て
ここでは、エクセルで「空白以外のセルを詰めて抽出する方法」を解説します。
複数の関数を使うので見た目ちょっと複雑ですが、使ってみると結構簡単です。
１．まずはやりたいことを確認
始めに、やりたいことを例を含めて確認します。
１－１．空白以外のセルを詰めて全部抽出したい
やりたいことは、「空白以外のセルを詰めて全部抽出する」です。
言い換えると、「空白のセルは無くして、値のあるセルのみにする」です。
途中途中で空白のある表を、詰めて空白のない形にまとめ直します。
１－２．例で確認
一つ例を見てみましょう。
上の表は、各県の県庁所在地が記載されたものです。ただし、ところどころ空白になっています。
そこで、都道府県名の列を基準にして、空白以外のセル・データのみを抽出してみます。
このように、基準にした列（例では「都道府県名」）が空白のデータを除いて表を上に詰めることができます。
ちなみに「0」が気になりますが、この対処法も解説します。
２．数式の形を確認
数式の形は、次の通りです。
この数式を「表示したい一番左上のセル」に入れ、数式を完成させた後で他のセルにコピーします。
数式が長くて関数も多いですが、指定するのは以下の2つだけです。
２－１．抽出列
「抽出列」には、元の表の一番左の列をまるっと指定します。
例えば、先ほどの例だと「抽出列」は、「A:A」となります。
２－２．条件範囲
「条件範囲」には、空白以外を判定する範囲を指定します。
ポイントは、次の通りです。
- 範囲を1列指定すること
- 必ず絶対参照にする（「$」を全部で4つ付ける）こと
- 2ヶ所とも同じくすること
例えば、先ほどの例だと「条件範囲」は、「$B$2:$B$7」となります。
２－３．出来上がった数式を確認
以上のことを入れると、先ほどの例でセルA9に入れる数式は、次のようになります。
=IFERROR(INDEX(A:A,1/LARGE(INDEX(($B$2:$B$7<>"")/ROW($B$2:$B$7),0),ROW(A1))),"")
後は、右方向とした方向に数式をコピーすれば完成です。
３．数式の作り方を確認
では、実際に数式を作ってみます。
上の表を使って、都道府県名の列で空白以外のデータを抽出してみます。
数式は「セルA9」に入れます。そして、数式完成後に他のセルにコピーします。
以下手順です。
（１）結果を表示させたいセルに、以下の数式を貼り付ける
【コピーする数式】
（２）「抽出列」の文字を消して、該当する列をクリックする
（３）「条件範囲」の文字を消して、マウスの左ドラッグで範囲を指定し、その後F4キーを１回押す
（４）もう一ヶ所も（３）と同様に行う
（５）Enterキーを押す
（６）セルの右下にカーソルを合わせ、「+」マークになったら下方向、右方向それぞれに左ドラッグで数式をコピーすれば完了
これで、空白以外のセル・データを抽出することができます。
４．ゼロを表示させない方法
先ほどの数式は、元のセルが空白だと「0」を返します。そこで、0を表示させたくない場合の対処法も説明します。
エクセルには「ゼロを表示させるか？」が設定で選べ、これを「表示させない」ように変えればゼロは空白で表示されるようになります。
設定の変更方法は、次の通りです。
（１）「ファイル」タブを選択する
（２）「オプション」を選択する
（３）「詳細設定」の「ゼロ値のセルにゼロを表示する」のチェックを外す
これで、ゼロが表示されなくなります。なお、この設定はシートごとにかかります。
以上、参考になれば幸いです。
【口コミ・評判のいいExcel本の紹介】
INDEX＋MATCH関数の、他の記事へのリンクはこちらです。
- INDEX＋MATCH関数その他の記事へのリンクはこちら