主キーを軸として複数項目を抽出し、統合するには？

質問

Excel2003を使用しています。A列を主キーとしてデータを抽出&統合したいです。（重複レコードは削除）

＜元データ＞
（A列）ID、（B列）時刻、（C列）名称という3列で構成されるデータが3000行あります。

実際のデータ内容としては
　（A列）ID123456、（B列）00:01:00、（C列）りんご 開始、です。

同一IDについては、関連データが必ず2行あります。（データ行の並び順は、ID順というわけではなく、ランダムです）
※「名称」に"開始"という文字が入っているものと、「名称」に"終了"という文字が入っているものの2種類です。
※「時刻」はそれぞれ異なります。
※同一IDの場合、C列の「名称」も（"開始"か"終了"のテキストが入っている事を除けば）同一です。

例1） 関連データ1：（A列）ID123456、（B列）00:01:00、（C列）りんご 開始
　　 関連データ2：（A列）ID123456、（B列）00:05:00、（C列）りんご 終了

＜抽出内容＞
上記のようなデータを、1つのIDにつき1行にまとめ、下記の形式の表を作成したいです。

（A列）ID、（B列）名称、（C列）開始時間、（D列）終了時間、（E列）処理時間（終了時間-開始時間で算出する）

例1）の場合、下記になります。
（A列）ID123456、（B列）りんご、（C列）00:01:00（開始時間）、（D列）00:05:00（終了時間）、（E列）00:04:00（処理時間）

＜希望＞
関数、ピボットテーブル、フィルタオプション、VBA、いずれでも結構ですが、これを元に日々使用する自動ツールを作成するため、汎用性のある簡潔な方法が良いです。
（元データの列構成と抽出後データの列構成は固定ですが、元データ行数は可変します。1万行ぐらいまで増加する可能性あり）

宜しくお願い致します。

回答No.6

bunjii

質問の内容から業務用のデータ処理と思われます。
大量のデータを一括処理するときはCSV形式のファイルに書き出してExcelと関係のないテキスト自動編集プログラムを専門家に作成してもらうと良いでしょう。
前処理としてExcelを使ってIDをキーとしてソートすればC++等で簡単なプログラムができると思います。
業務用の処理システムは自作できないとき有償で外注すべきです。

ボランティアの知恵を借りるのは甘え過ぎです。

仕事としては単純な作業なので操作手順をマクロに記録すれば元データを読み込むだけで自動処理できます。

元データの時刻を名前に付加されている”開始”または”終了”を手掛かりに追加された列へIF関数を使って代入できます。
開始時刻と終了時刻に振り分けられたセルから計算式のみ除去するには対象範囲を選択してクリップボードへコピーし、同じ場所へ値のみを張り付ければ良いでしょう。
次に元データの時刻をキーとしてシート全体を対象にソートし、更にIDをキーとしてソートすれば同一IDの開始時刻、終了時刻の組み合わせができますので、終了時刻の最上位セルを削除して上へシフトすれは最後の処理として開始時刻と終了時刻が空欄の行を削除します。
以上の作業は応用力のみで解決する単純作業です。

お礼

ありがとうございます！

回答No.5

keithin

＞ですが、名称（りんご）は重複します。

別に問題ありません。回答した通りの手順で全く問題無く出来ますが、何か？

＃ちなみにマクロだとこんなカンジで。

sub macro1()
　dim LastRow as long

　range("D:I").insert shift:=xlshifttoright
　range("G:I").numberformat = "hh:mm:ss"
　lastrow = range("A65536").end(xlup).row

　with range("D2:D" & lastrow)
　　.formula = "=ROW(D1)"
　　.value = .value
　end with

　range("A:D").sort key1:=range("A1"), order1:=xlascending, key2:=range("B1"), order2:=xlascending, header:=xlyes
　range("E1:I1") = array("ID","商品","開始","終了","処理")

　range("E2").formula = "=A2"
　range("F2").formula = "=LEFT(C2,LEN(C2)-3)
　range("G2").formula = "=B2"
　range("H3").formula = "=B3"
　range("I2").formula = "=H3-G2"
　range("E2:I3").copy range("E2:I" & lastrow )
　range("E2:I" & lastrow).value = range("E2:I" & lastrow).value
　range("E2:I" & lastrow).specialcells(xlcelltypeblanks).delete shift:=xlshiftup
　range("A:D").sort key1:=range("D1"), order1:=xlascending, header:=xlyes

end sub

＃元データの並び順を保全する必要が無ければもっと簡単に。

sub macro1r1()
　dim LastRow as long

　range("E:I").insert shift:=xlshifttoright
　range("G:I").numberformat = "hh:mm:ss"
　lastrow = range("A65536").end(xlup).row

　range("A:C").sort key1:=range("A1"), order1:=xlascending, key2:=range("B1"), order2:=xlascending, header:=xlyes
　range("E1:I1") = array("ID","商品","開始","終了","処理")

　range("E2").formula = "=A2"
　range("F2").formula = "=LEFT(C2,LEN(C2)-3)
　range("G2").formula = "=B2"
　range("H3").formula = "=B3"
　range("I2").formula = "=H3-G2"
　range("E2:I3").copy range("E2:I" & lastrow )
　range("E2:I" & lastrow).specialcells(xlcelltypeblanks).delete shift:=xlshiftup

end sub

お礼

ありがとうございます！

回答No.4

noname#204879

簡単化するために、「元データ」を添付図 Sheet1 に示すように４列にします。
Sheet2 において、
B2: =VLOOKUP(A2,Sheet1!A:D,3,FALSE)
C2: =SUMPRODUCT((Sheet1!$A$2:$A$10000=$A2)*(Sheet1!$D$2:$D$10000=C$1),Sheet1!$B$2:$B$10000)
セル C2 を[コピー]して、此れをセル D2 に[貼り付け]
E2: =D2-C2
範囲 B2:E2 を下方にズズーッとドラッグ＆ペースト

お礼

ありがとうございます！

回答No.3

MackyNo1

ピボットテーブルを利用するなら以下のような手順になります。

まず準備として、C列を選択して「データ」「区切り位置」で「カンマやスペース・・・」にチェックが入っていることを確認して「次へ」で区切り文字の「その他」の欄にスペースを入力して「完了」します（添付画像上参照）。

このデータでピボットテーブルを作成し、行フィールドに「ID」と「品名」、列フィールドに「開始/終了」、データフィールドに「時刻」をドラッグし、データフィールドで右クリックし「フィールドの設定から「合計」を選択し、セルの書式を時刻にします。
さらにピボットテーブル上で右クリックし「オプション」から「列の合計」「行の合計」のチェックを外します。
またIDごとの合計欄にカーソルを置いて右クリックから「表示しない」を選択します（これで添付画像下のようなレイアウトになります。
E列の処理時間は「=IF(D13="","",D13-C13)」のような数式を入力しておきます。

ちなみに「合計/時刻」や「開始/終了」などの文字は、そのセルでスペースを入力して見えなくしたほうがきれいなレイアウトになると思います。
また罫線をデータ範囲だけに入れたい場合は、条件付き書式などを利用することもできます。

お礼

出来ました！ありがとうございます。

回答No.2

tom04

こんばんは！
VBAになってしまいますが、一例です。

IDと名称は常に対応していて、重複はない！という前提です。
↓の画像のようにSheet1のデータをSheet2に表示するようにしてみました。
両Sheetとも1行目の項目は入力済みで、Sheet1のデータは2行目以降にあるとします。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, endRow As Long, c As Range, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")
Application.ScreenUpdating = False
endRow = wS2.Cells(Rows.Count, "A").End(xlUp).Row
If endRow > 1 Then
Rows(2 & ":" & endRow).ClearContents
End If
For i = 2 To wS1.Cells(Rows.Count, "A").End(xlUp).Row

If WorksheetFunction.CountIf(wS2.Range("A:A"), wS1.Cells(i, "A")) = 0 Then
With wS2.Cells(Rows.Count, "A").End(xlUp).Offset(1)
.Value = wS1.Cells(i, "A")
If InStr(wS1.Cells(i, "C"), "開始") > 0 Then
.Offset(, 1) = Trim(Replace(wS1.Cells(i, "C"), "開始", ""))
.Offset(, 2) = wS1.Cells(i, "B")
Else
.Offset(, 1) = Trim(Replace(wS1.Cells(i, "C"), "終了", ""))
.Offset(, 3) = wS1.Cells(i, "B")
End If
End With
Else
Set c = wS2.Range("A:A").Find(what:=wS1.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
k = c.Row
If InStr(wS1.Cells(i, "C"), "開始") > 0 Then
wS2.Cells(k, "C") = wS1.Cells(i, "B")
Else
wS2.Cells(k, "D") = wS1.Cells(i, "B")
End If
End If
End If
Next i
For i = 2 To wS2.Cells(Rows.Count, "A").End(xlUp).Row
If WorksheetFunction.Count(Range(wS2.Cells(i, "C"), wS2.Cells(i, "D"))) = 2 Then
With wS2.Cells(i, "E")
.Value = .Offset(, -1) - .Offset(, -2)
End With
End If
Next i
wS2.Range("C:E").NumberFormatLocal = "[h]:mm:ss"
Application.ScreenUpdating = True
End Sub 'この行まで

こんな感じではどうでしょうか？m（_ _）m

補足

早々にありがとうございます！
前提についてですが、元ソースで、同一IDのレコードは常に2つ（"開始"と"終了"）です。
ですが、名称（りんご）は重複します。
ID123456が"りんご"で、ID222222も"りんご"、ID333333は"バナナ"という具合です。

回答No.1

keithin

ふつーに考えて、ID123456は必ず商品りんごに対応しているのですね。
ピボットテーブルレポートを使うなら、Ｃ列の「りんご開始」「りんご終了」のそれぞれ「りんご」が邪魔です。Ｄ列に関数を追加する（マクロで追加させるのでも勿論可）なり、Ｃ列を直接マクロで修正するなりして「開始」と「終了」だけにしてしまいます。

○ＩＤを列に配置する
○時間をデータに配置する
○「開始」「終了」だけにしたＤ列若しくはＣ列を行に配置する
○行に集計アイテムとして「=終了-開始」を追加する
とりあえず以上で、最低限必要な集計は出来ます。

●以下はオプションです
更に元データとしてＥ列に、ＩＤに対応した（若しくはＣ列から生成した）「商品名」（りんごだけ記載）を追記し列に配置する
「商品名▼」を右クリックしてフィールドの設定の「詳細」から「トップテン表示」を選び、使用するフィールドを「合計／時刻」にする

エクセル２００７以降を利用する環境があれば、
１．必要に応じてマクロを併用し、「重複の削除」を使って一意のＩＤ一覧を作成する
２．SUMIFS関数を使ってＩＤ・開始、ＩＤ・終了の時刻をそれぞれ参照する
３．引き算して時間を求める
４．VLOOKUP関数などでIDから「りんご××」を取り出してきて、××部分を（もちろん関数を使って）除去して「りんご」だけを算出する

ぐらいでも良いです。

１万行を想定するなら、「関数を使って一意のＩＤリストを並べさせる」みたいなことは考えてはいけません。またExcel2003以前では、関数(例えばSUMPRODUCT関数等)を使って回答後半の方法を試みるのもお勧めしません。
いずれもやればできますが、実際はよほど丁寧に作業列を追加する等して準備しないと使い物になりませんので。

補足

ありがとうございます！
元データで、同一IDのレコードは常に2つ（"開始"と"終了"）です。
ですが、名称（りんご）は重複します。
ID123456が"りんご"で、ID222222も"りんご"、ID333333は"バナナ"という具合です。
つまり、"開始"と"終了"という2つのレコードを、IDを主キーとして1つに統合する作業です。
従って、元データが3000行であれば、抽出後のデータは1500行になります。
残念ながら、Excel2007の環境はありません…