【Excel】エクセルで空欄を詰める（関数や数式で自動：コピー時：連続データ：フィルター等）方法

エクセルでデータの中に空欄があり、それを詰めて表示したいのに、1つずつ手作業で移動させていて時間がかかって困った経験はありませんか？

この記事では【Excel】エクセルで空欄を詰める（関数や数式で自動：コピー時：連続データ：フィルター等）方法について解説していきます。

ポイントは

です。

それでは詳しく見ていきましょう。

 

 

並べ替え機能で空欄を下に移動させる方法【最も簡単で確実な方法】

エクセルで空欄を詰める最も簡単で確実な方法は、並べ替え機能を使って空欄を下に移動させる方法です。

並べ替えを行うと、データがある行が上に集まり、空欄の行が自動的に下に移動するため、一瞬で空欄を詰めることができるのです。

例えば、商品リストに欠番があって空欄が点在している場合や、フィルター後にデータをコピーしたら空欄が混ざってしまった場合に非常に有効です。

この方法は関数を使わないため、初心者にも分かりやすく、最も頻繁に使われる手法となります。

解決方法

具体的な例として、A列に商品名があり、途中に空欄が混ざっている場合を考えてみましょう。

サンプルデータは以下のようになります。

まず、空欄を詰めたい列（A列）のいずれかのセルを選択します。

データタブをクリックして、「並べ替えとフィルター」グループの中にある「昇順」ボタンをクリックしましょう。

これだけで、データがある行が上に集まり、空欄が下に移動します。

ただし、この方法は元のデータの順序が変わってしまうため、順序を保持したい場合は注意が必要です。

順序を保持したまま空欄を詰めたい場合は、事前に連番を振っておく方法があります。

まず、B列に連番を振ります（1、2、3、4、5…）。

次に、A列で並べ替えを実行して空欄を下に移動させます。

空欄を削除したら、再度B列の連番で並べ替えれば元の順序に戻せるわけです。

複数列のデータがある場合は、並べ替えの基準列を指定する必要があります。

データ範囲全体を選択してから、データタブ→並べ替えを選択し、「列」で基準となる列を指定して昇順で並べ替えれば良いでしょう。

【操作のポイント】

 

 

ジャンプ機能で空欄セルを一括削除する方法【空欄を選択して上に詰める】

エクセルのジャンプ機能を使うと、空欄セルだけを一括で選択して削除し、上に詰めることができます。

この方法は、空欄セルを自動的にすべて選択し、削除して上方向にシフトさせることで、手作業で1つずつ削除する手間を省けるのです。

特に、縦方向に空欄が点在しているリストを詰めたい場合に非常に効果的です。

解決方法

A列に氏名データがあり、途中に空欄が複数混ざっている場合を考えてみましょう。

まず、空欄を詰めたい範囲（A列全体またはデータ範囲）を選択します。

Ctrl+Gキーまたはを押して「ジャンプ」ダイアログボックスを開きます。

「セル選択」ボタンをクリックすると、「選択オプション」ダイアログボックスが開きます。

「空白セル」を選択して「OK」ボタンをクリックしてください。

すると、選択範囲内のすべての空欄セルが自動的に選択された状態になります。

この状態で、ホームタブの「セル」グループにある「削除」→「セルの削除」を選択するか、右クリックして「削除」を選択します。

「削除」ダイアログボックスが表示されたら、「上方向にシフト」を選択して「OK」をクリックしましょう。

これで、空欄セルがすべて削除され、下にあったデータが上に詰められます。

複数列にまたがるデータの場合、この方法では列ごとにデータがずれてしまう可能性があるため注意が必要です。

複数列の場合は、行全体を削除する方が安全です。

行全体を削除する場合は、空白行を選択してから「行全体」を選択して削除します。

【操作のポイント】

 

 

FILTER関数で空欄を除外する方法【Excel 365やExcel 2021】

Excel 365やExcel 2021で使用できるFILTER関数を使えば、空欄を自動的に除外したリストを作成できます。

FILTER関数は条件に合致するデータだけを抽出する関数で、「空欄ではない」という条件を設定することで、空欄を詰めた結果が自動的に得られるのです。

この方法なら、元データを変更せずに別の場所に空欄なしのリストを表示できるため、安全に作業できます。

解決方法

A列に元データがあり、C列に空欄を除外したリストを表示したい場合を考えてみましょう。

サンプルデータは以下のようになります。

C2セルに以下の数式を入力します。

この数式の詳しい解説は以下の通りです。

・FILTER関数は「=FILTER(配列,含める条件,[空の場合])」という構造です

・第1引数「A2:A10」は、フィルターをかけたいデータ範囲を指定します

・第2引数「A2:A10<>””」は、フィルターの条件式です。「<>」は「等しくない」を意味し、「””」は空文字列なので、「A2:A10の各セルが空欄でない」という条件になります

・この条件に合致する行だけが抽出され、C2セルから下方向に自動的にスピルされます

・スピルとは、1つの数式で複数のセルに結果が自動展開される機能です

数式を入力すると、C2セルから下方向に空欄を除外した結果が自動的に表示されます。

元データのA列に変更があれば、C列の結果も自動的に更新されるため、常に最新の状態が保たれます。

複数列のデータから空欄行を除外したい場合は、以下のように記述できます。

この数式の詳しい解説は以下の通りです。

・第1引数「A2:B10」で、A列とB列の両方を含む範囲を指定しています

・第2引数「A2:A10<>””」で、A列が空欄でない行だけを抽出する条件を設定しています

・結果として、A列が空欄でない行のA列とB列のデータがセットで抽出されます

もしFILTER関数が使えない環境（Excel 2019以前）の場合は、次で紹介する従来の方法を使用する必要があります。

【操作のポイント】

 

 

IF関数と配列数式で空欄を詰める方法【Excel 2019以前でも使用可能】

FILTER関数が使えない環境では、IF関数と配列数式を組み合わせて空欄を詰める方法があります。

この方法は少し複雑ですが、古いバージョンのExcelでも空欄を除外したリストを作成できるため、覚えておくと便利です。

SMALL関数とIF関数を組み合わせることで、空欄でないデータの位置を特定し、順番に取り出すことができます。

解決方法

A列に元データがあり、C列に空欄を除外したリストを表示したい場合を考えてみましょう。

C2セルに以下の数式を入力します。

この数式は配列数式なので、Ctrl+Shift+Enterで確定する必要があります（Excel 365では不要）。

この数式の詳しい解説は以下の通りです。

・IF($A$2:$A$10<>””,ROW($A$2:$A$10)-ROW($A$2)+1)：A2:A10の各セルが空欄でない場合、その行の相対位置（1, 2, 3…）を返します。空欄の場合はFALSEを返します

・ROW($A$2:$A$10)-ROW($A$2)+1：各セルの行番号から開始行番号を引いて1を足すことで、1から始まる連番を作成します

・SMALL(配列,ROW(A1))：上記で作成された数値配列の中から、小さい方からROW(A1)番目（つまり1番目、2番目、3番目…）の値を取り出します

・ROW(A1)は行番号を取得する関数で、C2セルでは1、C3セルでは2、C4セルでは3…となります

・INDEX($A$2:$A$10,位置)：SMALL関数で取得した位置にあるデータをA列から取り出します

・IFERROR(…,””)：すべてのデータを取り出し終えるとエラーになるため、エラーの場合は空欄を表示します

数式を入力したら、C2セルを下方向にオートフィルでコピーしましょう。

これで、A列の空欄を除外したデータがC列に順番に表示されます。

この方法は複雑に見えますが、一度作成すればコピーするだけで使えるため、テンプレートとして保存しておくと便利です。

【操作のポイント】

 

 

フィルター機能で空欄を非表示にしてコピーする方法【一時的な空欄詰め】

一時的に空欄を詰めた状態で表示したい、またはコピーしたい場合は、フィルター機能を使う方法が便利です。

フィルターで空欄を非表示にすると、見かけ上は空欄が詰まった状態になり、その状態でコピーすれば空欄なしのデータを別の場所に貼り付けられるのです。

元データは変更されないため、安全に作業できる点が大きなメリットでしょう。

解決方法

A列にデータがあり、途中に空欄が混ざっている場合を考えてみましょう。

まず、データ範囲のいずれかのセルを選択した状態で、データタブの「フィルター」ボタンをクリックします。

列の見出し行に下向き矢印のフィルターボタンが表示されます。

A列のフィルターボタンをクリックして、フィルターメニューを表示させましょう。

フィルターメニューの下部に項目のチェックリストが表示されます。

この中の「(空白セル)」のチェックを外して「OK」ボタンをクリックしてください。

これで、空欄の行が非表示になり、データがある行だけが表示されます。

見かけ上は空欄が詰まった状態になっているため、この状態で範囲を選択してコピーすれば、空欄なしのデータをコピーできます。

コピーしたデータを別の場所に貼り付ければ、空欄が詰まった状態のリストが作成できます。

元のデータに戻したい場合は、フィルターボタンをクリックして「(空白セル)」にチェックを入れ直すか、フィルターボタンをもう一度クリックしてフィルターを解除します。

複数列にまたがるデータの場合も、同じ方法で特定の列を基準にフィルターをかけることができます。

例えば、A列が空欄の行をすべて非表示にしたい場合は、A列のフィルターで「(空白セル)」のチェックを外せば良いわけです。

【操作のポイント】

 

 

まとめ　エクセルで空欄を詰める（関数や数式で自動・コピー時・連続データ・フィルター）方法

エクセルで空欄を詰める方法をまとめると

・最も簡単な方法は並べ替え機能で空欄を下に移動させる方法だが、データの順序が変わる点に注意

・ジャンプ機能（Ctrl+G）で空白セルを一括選択し、上方向にシフトして削除すれば効率的に詰められる

・Excel 365やExcel 2021では「=FILTER(範囲,範囲<>””)」で空欄を除外した結果を自動表示できる

・Excel 2019以前では、配列数式を使った複雑な数式で空欄を詰めることが可能

・フィルター機能で「(空白セル)」のチェックを外せば、一時的に空欄を非表示にしてコピーできる

・元データを保持したい場合は関数を使う方法、直接編集したい場合は並べ替えやジャンプ機能を使う方法が適している

これらの方法を状況に応じて使い分ければ、ほとんどのケースで空欄を効率的に詰めることができるでしょう。

特に最初に紹介した並べ替え機能は、最もシンプルで確実な方法ですので、順序を気にしない場合はまずこれを試すことをおすすめします。

順序を保持したい場合や、元データを変更したくない場合は、FILTER関数や配列数式を使った方法が有効です。

また、一時的に空欄を非表示にしてコピーしたい場合は、フィルター機能が最も手軽で実用的でしょう。

エクセルの空欄詰め機能を正しく理解して、データ整理やリスト作成の効率を大幅に向上させていきましょう！