空の行と列は、多くの場合、テーブルで問題になる可能性があります。 ソート、フィルタリング、要約、ピボット テーブルの作成などの標準機能は、空の行と列をテーブル ブレークとして認識し、その後ろにあるデータを取得しません。 このようなギャップが多数ある場合、それらを手動で削除すると非常にコストがかかる可能性があり、フィルターを使用して一度に「まとめて」削除するのはうまくいきません。
この問題を解決するいくつかの方法を見てみましょう。
方法 1. 空のセルを検索する
これは最も便利ではないかもしれませんが、最も簡単な方法であることは間違いありません。
内部に多くの空の行と列を含むこのようなテーブルを扱っているとします (わかりやすくするために強調表示されています)。
テーブルの最初の列 (列 B) には常に都市の名前が含まれていると確信しているとします。 この列の空のセルは、不要な空の行の兆候になります。 それらをすべてすばやく削除するには、次の手順を実行します。
- 都市で範囲を選択 (B2:B26)
- キーを押します F5 それから、 特徴 (スペシャルへ) またはタブで選択します ホーム — 検索と選択 — セルのグループを選択する (ホーム — Find&Select — スペシャルに移動).
- 開いたウィンドウで、オプションを選択します 空のセル （空白） を押し OK – テーブルの最初の列にあるすべての空のセルを選択する必要があります。
- タブで選択します ホーム Command 削除 – シートから行を削除します (削除 — 行を削除) またはキーボードショートカットを押します Ctrlキー+マイナス –そして、私たちの仕事は解決されました。
もちろん、テーブル ヘッダーを基にして、まったく同じ方法で空の列を取り除くことができます。
方法 2: 空の行を検索する
すでにお気づきかもしれませんが、前の方法は、空のセルを検索するときにフックできる、完全に入力された行と列がデータに必ず含まれている場合にのみ機能します。 しかし、そのような信頼がなく、データに空のセルが含まれている可能性がある場合はどうなるでしょうか?
たとえば、次の表を見てください。
ここでは、アプローチが少しトリッキーになります。
- セル A2 に関数を入力します。 COUNT (カウンター)、右側の行の塗りつぶされたセルの数を計算し、この式をテーブル全体にコピーします。
- セル A2 を選択し、次のコマンドでフィルターをオンにします データ – フィルター (データ — フィルター) またはキーボードショートカット Ctrlキー+シフト+L.
- 計算列、つまりデータのないすべての行でゼロを除外しましょう。
- フィルタリングされた行を選択し、コマンドでそれらを削除する必要があります ホーム — 削除 -' シートから行を削除 (ホーム — 削除 — 行を削除) またはキーボードショートカット Ctrlキー+マイナス.
- フィルターをオフにして、空行のないデータを取得します。
残念ながら、このトリックは列では実行できなくなりました。Excel は列でフィルター処理する方法をまだ学習していません。
方法 3. シートの空の行と列をすべて削除するマクロ
簡単なマクロを使用して、このタスクを自動化することもできます。 キーボード ショートカットを押す 他の+F11 またはタブから選択 開発者 — ビジュアルベーシック (開発者 — Visual Basic エディター). タブの場合 開発者 は表示されません。 ファイル – オプション – リボン設定 (ファイル - オプション - リボンのカスタマイズ).
開いた Visual Basic エディター ウィンドウで、メニュー コマンドを選択します。 インサート – モジュール 表示される空のモジュールに、次の行をコピーして貼り付けます。
Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If rng Is Nothing の場合 Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Then If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) End If Next r If Not rng Is Nothing Then rng.Delete End Sub
エディターを閉じて、Excel に戻ります。
今ヒットコンビネーション 他の+F8 またはボタン マクロ タブ 開発者. 開いたウィンドウには、作成したばかりのマクロを含め、現在実行可能なすべてのマクロが一覧表示されます。 空の削除. それを選択してボタンをクリックします ラン （走る） – シート上のすべての空の行と列が即座に削除されます。
方法 4: パワー クエリ
私たちの問題を解決する別の方法と非常に一般的なシナリオは、Power Query で空の行と列を削除することです。
まず、テーブルを Power Query クエリ エディターに読み込みましょう。 キーボード ショートカット Ctrl+T を使用して動的な「スマート」に変換するか、データ範囲を選択して名前を付けることができます (たとえば、 且つ) 数式バーで、名前付きに変換:
次に、コマンド Data – Get data – From table / range (Data – Get Data – From table / range) を使用して、すべてを Power Query に読み込みます。
次に、すべてが簡単です：
- コマンドホームで空行を削除します–行を減らします–行を削除します–空行を削除します（ホーム–行を削除–空行を削除します）。
- 最初の City 列の見出しを右クリックし、コンテキスト メニューから Unpivot Other Columns コマンドを選択します。 私たちのテーブルは、技術的に正しく呼ばれているように、 正規化 – XNUMX つの列に変換: 都市、月、元のテーブルの都市と月の交点からの値。 Power Query でのこの操作の特徴は、ソース データの空のセルをスキップすることです。これが必要です。
- 次に、逆の操作を実行します。元の形式に戻すために、結果のテーブルを XNUMX 次元のテーブルに戻します。 月の列を選択し、タブで 変換 チームを選ぶ ピボット列 (変換 — ピボット列). 開いたウィンドウで、値の列として最後の (値) を選択し、詳細オプションで操作を選択します。 集約しない (集計しないでください):
- コマンドを使用して結果をExcelにアップロードする必要があります ホーム — 閉じてロード — 閉じてロード… (ホーム — クローズ & ロード — クローズ & ロード…)
- マクロとは何ですか、どのように機能するのか、マクロのテキストをどこにコピーするのか、マクロを実行する方法は?
- リスト内のすべての空のセルに親セルの値を入力する
- 指定された範囲からすべての空のセルを削除する
- PLEX アドオンを使用してワークシート内のすべての空の行を削除する