☰

Excel: 2つの列セットを1つに集約する

tag-icon • tag-icon microsoft-excel
Excel: 2つの列セットを1つに集約する

1 つの Excel シートに、それぞれ 3 列の 2 つの異なるデータ セットがあり、非常に類似したデータを表しています。

これを 3 つの列の 1 セットに結合したいと思います。

各データ セットには、グループ化するキーとなる左端の列があります。

キーは各データセットに 1 回だけ出現するか、まったく出現しません。その他の列は数値で、空 (ゼロとして解釈) になる場合があります。2 つのシリーズの長さは異なる場合があります。

そのようです：

[Name_A],[Score_A],[Value_A],[Name_B],[Score_B],[Value_B]
Adam,14,20,Johnny,8,
Johnny,11,,Bernice,5,5
,,,Adam,2,8

次のように集約されます:

[Name],[Score],[Value]
Adam,16,28
Johnny,19,
Bernice,5,5

最終結果の行の順序は重要ではありません。

答え1

テキストを列に分割し、コンマを区切り文字として使用してデータを解析します。次に、一意の名前のリストをコンパイルし (Excel のデータ タブで重複を削除してこれを行うことができます)、 を使用します=sumif(Name_Column,Name1,Value1_Column)。

これにより、名前と値ごとにすべてが要約されます。

例

答え2

データセットをマージする鍵は、一意の名前のリストを抽出することです。これは多くの名前がすべて 1 つの列にあると簡単です。データを示したとおりに整理する必要がある場合は、VBA ソリューションがありますので、必要な場合は以下にコメントしてください。

以下の最初の表では、「B」のデータを「A」のデータの下に移動し、各セクションにさらにいくつかの行を追加しました。

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

この数式は、E2 から下方向に入力され、名前をリストし、重複を削除します。

=IFERROR(INDEX((A$2:A$15),MATCH(0,COUNTIF($E$1:E1,A$2:A$15),0)),"")

これは配列数式なので、 とともに入力する必要がありますCTRLShiftEnter。

F2 と G2 から入力される次の 2 つの数式は、リスト内の各名前のスコアと値を合計します。

=IF(LEN(E2)<1,"",SUMIF(A$2:A$15,E2,B$2:B$15))

=IF(LEN(E2)<1,"",SUMIF(A$2:A$15,E2,C$2:C$15)) ______________________________________________________________________

使い方：inner は、COUNTIF()列 A の各名前が列 E の増加するリストに出現する回数の配列を作成します。 は、MATCH()その配列の最初の 0 (まだリストされていない名前に対応) の位置を見つけ、それが の row_num として使用されINDEX()、列 A から名前が選択されます。すでにリストされている名前は再度出現しません。

IFERROR()数式で一意の値がなくなると、#NUM! エラーが発生する行に空白が挿入されます。

2 つのSUMIF()数式は、それぞれの名前のスコアと値の数値を合計するだけです。IF()列 E の長さが 1 未満の行には空白が挿入されます (つまり、セルに名前がなく、空白で表示されます)。

関連情報