1386925063 エクセルで行の統合(集計)方法を教えてください


図内の左の表を右の表のように統合したいです。
「a」「b」「c」にバラバラの情報が入っているのですが、「ID」ごとに一行にする方法を教えてください。

できる限りシンプルなやり方が嬉しいです。

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2013/12/13 17:57:43
  • 終了:2013/12/18 11:53:55

ベストアンサー

id:nanoha3 No.3

nanoha3回答回数53ベストアンサー獲得回数152013/12/13 20:48:21

ポイント300pt

ピボットで10秒でできます。
http://f.hatena.ne.jp/nanoha3/20131213204118
1行で1つのIDに対して、a,b,cのいずれかの値が2以上をとることがあるのなら、合計か個数のカウントかは内容に応じて使い分けてください。
・レコード数、ID数がその数でも問題なく処理できます
・内容に更新がかかった場合でも、ピボット範囲を適切に設定しておけば、ピボット上のコンテキストメニューからの「更新」で即時反映できます(列全部を対象にすると良いです)

a-kuma3さんの関数のやり方でも、「$A$2:$A$8」といった範囲指定の部分を列全体指定にしてやれば、ID数が多くなっても対応出来ます。
ただ、その場合シート当りの関数が非常に多くなって重い&予めIDの洗い出しが必要なので、ピボットの方が便利かと思います。

id:ramon-nakajima

abcの値は入るとすれば必ず「1」で重複はありません。
その場合でもこの方法が理想に近そうです。検証してみます。
ありがとうございます。

2013/12/13 23:05:39

その他の回答(2件)

id:a-kuma3 No.1

a-kuma3回答回数4440ベストアンサー獲得回数18242013/12/13 18:31:38

ポイント100pt

こんな感じになってるとして、集計用の表の H2 に図のような式を入力します。
f:id:a-kuma3:20131213182856p:image
式を大きく書くと、以下のようになってます。

=IF(SUMIF($A$2:$A$8,$G2,B$2:B$8)>0,1,"")

セルの範囲に付けている$も、微妙に変えてあるので、その通りに入力します。
SUMIF には三つのセル範囲を指定しますが、
ひとつめ:元の表の ID の列範囲(列にも行にも$をつける)
ふたつめ:集計する表の同じ行の ID のセル(列に$をつける)
みっつめ:元の表の集計するデータの列範囲(行に$をつける)

で、H2 のセルをコピー。
H2~J4 を選択して、貼りつけ。
f:id:a-kuma3:20131213182855p:image

とりあえず、質問の画像のような状態にはなります。
f:id:a-kuma3:20131213182854p:image

# きっと手元にある表は、こんなんじゃないんだろうなと思いつつ...

id:ramon-nakajima

回答ありがとうございます。
レコード数は数万行、IDは数千あります...
もう少し皆さまの回答を待ってみます。

2013/12/13 19:00:04
id:ramon-nakajima

レコード数は数万行、IDは数千あります。

id:gong1971 No.2

gong1971回答回数443ベストアンサー獲得回数682013/12/13 19:14:47

ポイント100pt

※(追記)ゴメンなさい。コメントに書くつもりが、
 回答に書いてしまいました。

 Excelのバージョンと併せて、情報を頂ければ、
 追記にて回答させて頂きます。
 ただ、早くても今日の深夜になっちゃうと思います。


あと、IDがAで、aに値が入る行は1行のみですか?(例だと1という値)
複数行ある時は、どのように処理しますか?
a-kuma3さんの回答のように合計値で良いのかな?
もしくは重複していても必ず1を表示とか…何かルールはありますか?

また、IDと情報は必ず対になっているのですか?
例えばIDがAの場合、情報は必ず情報Aですか?
それとも、IDがAで、情報が情報Bという事がありますか?
(追記)もし対になっていない場合は、IDと情報、
2つのキーを元に統合するという事でしょうか?

最後にレコード数ですが、もう少し具体的な数は提示できますか?
また、a,b,cというのは3列のみでしょうか?違う場合、何列ありますか?

この後、しばらくはアクセスできないので、
他の方に先を越されてしまうかもしれませんが…

--------------------
2013/12/14 3:24 追記

VBAを使わないとして、3つの方法が思い浮かびました。一つがnanoha3さんが回答されたピボットテーブル、もう一つがa-kuma3さんが回答されたSUMIF関数、最後に次に回答する統合です。

手元にあるのがExcel2010なので、Excel2010での方法を紹介させて頂きます。
また、ピボットテーブルを使用した方法、SUMIF関数を使用した方法で、補足したい点があったので、記載させて頂きました。

■統合を使用した方法

1.結果を表示したい範囲の左上のセル1つを選択する。
 (新規シートのA1や、元データの右側など)
2.[データ]リボンの[統合]をクリックし、
 [集計の方法]を合計に、[上端行]と[左端列]にチェックを入れ、
 [統合元範囲]にカーソルを合わせ、元データの範囲を見出し行を含めて
 選択し、[追加]をクリック、続けて[OK]をクリック。
3.[統合]では数値しか統合できないので、
 情報の項目については、VLOOKUP関数で求める。
 例:元データ範囲:A1:E8 統合データ範囲:G1:K4 として、
   K2に =VLOOKUP(G2,$A$2:$E$8,5,0) と入力し、
   必要行数分下方向にコピー貼り付けする。

■ピボットテーブルを使用した方法の補足

ピボットテーブルでも統合と同様に情報の項目のような
文字列は集計出来ないので、VLOOKUP関数などで対応が必要です。

また、もしExcel2003をお使いの場合、合計値が縦に並んでしまいます。
これを目的通り横に展開するには下記ページで紹介されている方法を
ご使用ください。
http://hamachan4.exblog.jp/15769921

■SUMIF関数を使用した方法の補足

SUMIF関数を使用する場合、重複していない表を用意する必要があります。
[重複の削除]を使用する事で簡単に用意出来ます。

1.まず、元の表をコピーし、結果を表示したい範囲に貼り付ける。
2.[データ]リボンの[重複の削除]をクリックし、
 [すべて選択解除]をクリックし、列の一覧から
 「ID」と「情報」のみチェックを入れ、[OK]をクリック。
※この時、「ID」と「情報」以外の列には最初に見つかった
 行のデータが表示されます。削除してご利用ください。

id:a-kuma3

ぼくはよく分からなかったので、合計を取って正だったら 1、そうじゃなければ空白としました。

2013/12/13 19:46:47
id:ramon-nakajima

エクセルのバージョンは問いません。

> IDがAで、aに値が入る行は1行のみですか?(例だと1という値)
はい。
値がある場合は必ず「1」で重複もありません。

> IDと情報は必ず対になっている
はい。

レコード数は約3万行で、abcに該当するのは20ほどです。

回答は週明けでも大丈夫ですのでアイディアがあれば教えてください。
実はプログラマなので解決はできるのですが、エクセルはこういった処理ができるのではないかという好奇心が大きいです。
よろしくお願いします。

2013/12/13 23:00:24
id:nanoha3 No.3

nanoha3回答回数53ベストアンサー獲得回数152013/12/13 20:48:21ここでベストアンサー

ポイント300pt

ピボットで10秒でできます。
http://f.hatena.ne.jp/nanoha3/20131213204118
1行で1つのIDに対して、a,b,cのいずれかの値が2以上をとることがあるのなら、合計か個数のカウントかは内容に応じて使い分けてください。
・レコード数、ID数がその数でも問題なく処理できます
・内容に更新がかかった場合でも、ピボット範囲を適切に設定しておけば、ピボット上のコンテキストメニューからの「更新」で即時反映できます(列全部を対象にすると良いです)

a-kuma3さんの関数のやり方でも、「$A$2:$A$8」といった範囲指定の部分を列全体指定にしてやれば、ID数が多くなっても対応出来ます。
ただ、その場合シート当りの関数が非常に多くなって重い&予めIDの洗い出しが必要なので、ピボットの方が便利かと思います。

id:ramon-nakajima

abcの値は入るとすれば必ず「1」で重複はありません。
その場合でもこの方法が理想に近そうです。検証してみます。
ありがとうございます。

2013/12/13 23:05:39
  • id:gong1971
    Excelのバージョンは?
  • id:Yoshiya
    VBAでサンプルコードを作ってみたので、参考になれば幸いです。

    条件
    コードはThisWorkBookに記述。Sheet1に集計前のデータをセットする。他のシートは削除しておく。
    (ワークシートにはSheet1のみ)
    集計したデータはSheet2に書き込まれます。

    >>
    Option Explicit

    Private Sub Workbook_Open()

    Dim Row As Long
    Dim Column As Long
    Dim Address As String
    Dim RowID, ColID As String
    Dim CopyRow, CopyCol As Long
    Dim lp1, lp2 As Long

    Row = Sheet1.Range("A1").End(xlDown).Row
    Column = Sheet1.Range("A1").End(xlToRight).Column

    Worksheets("Sheet1").Copy After:=Worksheets("Sheet1")
    Sheets(2).Name = "Sheet2"

    Worksheets("Sheet2").Activate
    lp1 = Range("A1").End(xlDown).Row - 1
    While lp1 > 0
    If Range("A1").Offset(lp1, 0) = Range("A1").Offset(lp1 - 1, 0) Then
    Range("A1").Offset(lp1 - 1).EntireRow.Delete Shift:=xlShiftUp
    End If
    lp1 = lp1 - 1
    Wend

    Range(Cells(2, 2), Cells(Range("A1").End(xlDown).Row, Range("A1").End(xlToRight).Column - 1)).ClearContents

    Worksheets("Sheet1").Activate
    For lp1 = 1 To Row
    For lp2 = 1 To Column - 1
    Range("A1").Offset(lp1, lp2).Select
    If ActiveCell <> "" And InStr(1, ActiveCell, "情報") = 0 Then
    Address = ActiveCell.Address
    RowID = Range("A" & Mid(Address, InStr(2, Address, "$") + 1, Len(Address)))
    ColID = Range(Mid(Address, 2, InStr(2, Address, "$") - 2) & "1")

    CopyRow = Sheets("Sheet2").Range("A:A").Find(what:=RowID).Row
    CopyCol = Sheets("Sheet2").Range("1:1").Find(what:=ColID).Column
    Sheets("Sheet2").Cells(CopyRow, CopyCol) = Sheets("Sheet2").Cells(CopyRow, CopyCol) + Range("A1").Offset(lp1, lp2)
    End If
    Next lp2
    Next lp1

    End Sub
    <<

    処理手順
    1.データシート(Sheet1)を丸ごとコピーし、シート名をSheet2に変更
    2.Sheet2のA列をキーにして、重複行を削除
    3.A列と1行と最終列(「情報×」)を残して、セルの中身をクリア
    4.Sheet1のセルを順番に調べ、数字が入っているかチェックする。 数字があれば行列の番地を調べ、Sheet2の該当セルに加算する。

    複雑なチェックはしていないので、もしかしたらご希望の結果にならないかもしれません。
  • id:ramon-nakajima
    皆様ありがとうございます。

    いつも読むだけで人力検索を使うのは初めてですが、
    いろいろな回答をいただきとても参考になります。

    質問の情報が足りなく申し訳なかったのですが、私も少しプログラムを
    書いており、VBAのサンプルコードは特に勉強にになります。

    他にも方法がありそうで楽しみなのでもう少しお時間ください。
    よろしくお願いします。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません