☰

Excel: グループ化列を使用して、複数のスパース データを 1 行にまとめる方法

tag-icon • tag-icon microsoft-excel
Excel: グループ化列を使用して、複数のスパース データを 1 行にまとめる方法

では、現在のレイアウトの中へこのレイアウト

ご覧のとおり、重複したタイトルを削除し、空のセルを削除したいと考えています。最初の行の後の 7 行ごとにタイトルを削除し、他のセルを上に移動する数式を作成したいと考えていますが、どのように記述すればよいかわかりません。また、Excel でそのようなことを行うことは可能ですか? 行は数百行あるため、手動でこれを行うことはできません。Mac で Excel 2016 を使用しています。

前もって感謝します！

答え1

データ内の任意のセルを選択し、[データ] > [データの取得と変換] > [テーブル/範囲から] を使用して PowerQuery を作成します。

Power Query エディターで、[ホーム] > [変換] > [グループ化] を使用して、次のように構成します。

ここに画像の説明を入力してください

[OK] をクリックすると、必要な結果が得られます。

ここに画像の説明を入力してください

[ホーム] > [閉じて読み込む] をクリックするだけで、データがブックに戻ります。

編集：

これを数式で行うには、次のようなものを使用できます。

ここに画像の説明を入力してください

これは、上の画像のセル J2 の数式です。

=INDEX(B$2:B$15,SUMPRODUCT(N($A$2:$A$15=$I2),N(NOT(ISBLANK(B$2:B$15))),ROW(B$2:B$15))-1,1)

このSUMPRODUCT部分は基本的に、列 A が列 I のタイトルと一致する列 B の空でないセルの行番号を取得します。INDEX次に、その行から値を取得します。

データに合わせて範囲の垂直サイズを調整してください。ただし、上記の J2:O3 で行ったように、統合テーブルを埋めるために横方向および下方向にドラッグする前に、$ が上記と同じであることを確認してください。

答え2

まず、列内の空白セルを削除しますB:G。

  1. 列を選択B:G
  2. タブからHome選択しFind and SelectてGo To Special
  3. を選択しBlanksてクリックしますOK。空白のセルが選択されていることに注意してください。
  4. タブからHome選択Delete

次に、列の行ヘッダーを配置しますA。

  1. 列を選択A
  2. タブDataから選択Remove Duplicates
  3. 選択Continue with current selectionしてヒットEnter

これで、データが希望どおりに配置されるはずです。

Excel 365 にアップグレードする場合は、Excel の新しい動的数式を使用してこれを簡単に実行できます。

  1. セルにI1入力=UNIQUE(FILTER(A:A,A:A<>""))
  2. セルにJ1入力=FILTER(B:B,B:B<>"")
  3. J1セルをセルにコピーK1:O1

関連情報