エクセル2010 表の作成について

質問

エクセル初心者です。
作成したい表の作り方で、方法がありましたら教えていただきたく質問させて頂きました。

【基データ】は、

・個人のID
・氏名
・作業内容（A～F）
・上記作業の完了日

という一覧となっております。

とあるデータベースから抽出したもので、
抽出段階でこの配列を変更することはできません。

この【基データ】をもとに、
重複するIDと氏名をひとつにまとめて、
作業内容を列ごとに並べ、
該当するセルに完了日を表示させる
という表を作成したいのですが、
なにか方法はありますでしょうか？

【基データ】には膨大な量のデータがあるので、
良い方法があると助かります。

簡単な図を添付します。
説明がわかりづらく申し訳ございませんが
宜しくお願い致します。

回答No.2

MackyNo1 · ベストアンサー

ご希望の集計をするにはピボットテーブルを利用するのが簡単です。

元データのリストを選択し、挿入タブのピボットテーブルで行フィールドにＩＤと氏名、列フィールドに作業、Σ値に完了日をドラッグしてピボットテーブルを作成します。

ピボットテーブルのデータフィールドで右クリックし「その他のオプション」で「値の集計方法」を「最大値」にし、「表示形式」でご希望の日付形式を選択します。
もう一度右クリックして「ピボットテーブルオプション」からレイアウトと書式タブの「更新時に列幅を自動調整する」のチェックを外し、集計とフィルタタブで「列の総計」と「行の総計」のチェックを外します。
さらに表示タブで「従来のピボットテーブルのレイアウトを使用する」のチェックを入れ、「展開折りたたみボタンを表示する」のチェックを外します。

ちなみに、データの追加に合わせて自動的にピボットテーブルの範囲を拡大するには、元のリストをホームタブの「テーブルとして書式設定」からテーブルとして設定しておいてからピボットテーブルを作成してください。

お礼

MackyNo1様
教えていただいた通りの設定で表を作成することができました。
「ピボットテーブル」自体を知らなかったので、勉強になりました。
大変助かりました。
有難うございました。

補足

早速のご回答ありがとうございます。

教えて頂いた通りに作成しましたらできました！
但し、各個人の集計が表示されており、
個人の集計は不要ですのでこれを表示させない方法はありますでしょうか？

（例）
田中　　　　　2014/4/25
田中　集計　　2014/4/25

度々の質問で申し訳ございませんが、
教えていただけますと幸いです。
宜しくお願い致します。

回答No.7

coldblade

ピボートで簡単です。

お礼

coldblade様
ご回答いただきまして有難うございました。
今回は、MackyNo1様に教えていただいた設定で表を作成することができました。
画像を添付していただきとてもわかりやいです。
有難うございました。

回答No.6

bunjii

>エクセル初心者です。
>この【基データ】をもとに、重複するIDと氏名をひとつにまとめて、作業内容を列ごとに並べ、該当するセルに完了日を表示せるという表を作成したいのですが、なにか方法はありますでしょうか？
丸投げ状態ですが初心者には数式の解読が無理でしょう。
入門書を手元に置いて簡単な処理から手掛けるようにしてください。

幾つかの関数を組み合わせて数式を組めば目的通りの処理は可能です。
Excel 2013で検証した結果を添付しますので、各数式の動作を確認してみると良いでしょう。
F2=IF(COUNTA(A:A)>SUMPRODUCT(MAX(($A$2:$A$20=$F1)*($B$2:$B$20=$G1)*ROW($A$2:$A$20))),INDEX(A$2:A$20,SUMPRODUCT(MAX(($A$2:$A$20=$F1)*($B$2:$B$20=$G1)*ROW($A$2:$A$20)))+1,1),"")
F2セルをG2セルへコピーします。
H2=IF(SUMPRODUCT(MAX(($A$1:$A$20=$F2)*($C$1:$C$20=H$1)*ROW(H$1:H$20)))>0,INDEX($D$1:$D$20,SUMPRODUCT(MAX(($A$1:$A$20=$F2)*($C$1:$C$20=H$1)*ROW(H$1:H$20))),1),"")
H2セルをI2からM2セルまでコピーします。
F2からM2セルを下へ必要数だけコピーします。
但し、検証した元データの最大行番号は20になっていますので実際のデータでは最大の行番号に変更してください。
数式の説明が必要のときは不明な点を補足してください。

お礼

bunjii様
ご回答有難うございました。
私自身でもとても少ないのですが知っている限りの関数（IFやVLOOKUP等）で試してみましたが、
とても手間がかかり断念しました。
なにかもっと効率の良い方法があるはずと思い質問させていただきました。
今回はピボットテーブルを使って表を作成することができましたが、
関数でのやり方もあると教えていただき勉強になります。
詳しくご丁寧に教えていただき有難うございました。

回答No.5

MackyNo1

>個人の集計は不要ですのでこれを表示させない方法はありますでしょうか？

行フィールドで右クリックし「～の小計」をクリックして、そのチェックを外してください。

回答No.4

yosifuji20

＞＞エリアごとに一枚ずつシートを作っていった方が

私の趣旨はそういうことでなく、最後の料金表は一つとするということです。
つまり　距離ランク*100+サイズランクという式を設定すると(ランクが二けたなので*100とします。）料金の表は

Key　　　料金
0101　　　200
0102　　　300
0103　　　400
0201　　　250
0202　　　350
..
..
..
1912　　2000

この表のkeyは上２桁は距離のランク、下２桁はサイズです。
ここに0202というのは　距離ランクが2でサイズランクは2の場合の料金という意味です。
このkeyは前の答えの通り、元データの行ごとに式で求めます。

もちろんIndexやMatch関数でも可能ですが、これらの関数は初心者にはなかなかわかりにくく、万が一トラブルになった時に修正が難しいように思います。
ちょっと複雑でもよりやさしい関数を使う方がメンテが容易かなと思います。

回答No.3

tom04

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

元データはSheet1にあり、Sheet2に表示するとします。
尚、Sheet3を作業用のSheetとして使用していますので、
Sheet3は全く使用していない状態にしておいてください。

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

Sub Sample1() 'この行から
Dim i As Long, lastRow As Long, c As Range, r As Range
Dim wS2 As Worksheet, wS3 As Worksheet
Set wS2 = Worksheets("Sheet2")
Set wS3 = Worksheets("Sheet3")
Application.ScreenUpdating = False
With Worksheets("Sheet1")
.Range("A:A").AdvancedFilter Action:=xlFilterInPlace, unique:=True
.Range("A:B").Copy wS2.Range("A1")
.ShowAllData
.Range("C:C").AdvancedFilter Action:=xlFilterInPlace, unique:=True
.Range("C:C").Copy wS3.Range("A1")
.ShowAllData
wS3.Range("A1").Sort key1:=wS3.Range("A1"), order1:=xlAscending, Header:=xlYes
lastRow = wS3.Cells(Rows.Count, "A").End(xlUp).Row
Range(wS3.Cells(2, "A"), wS3.Cells(lastRow, "A")).Copy
wS2.Activate
ActiveSheet.Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Transpose:=True
.AutoFilterMode = False
For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
Set c = wS2.Range("A:A").Find(what:=.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
Set r = wS2.Rows(1).Find(what:=.Cells(i, "C"), LookIn:=xlValues, lookat:=xlWhole)
.Cells(i, "D").Copy wS2.Cells(c.Row, r.Column)
Next i
wS3.Cells.Clear
End With
Application.ScreenUpdating = True
MsgBox "処理完了"
End Sub 'この行まで

※　＞【基データ】には膨大な量のデータがあるので
というコトですので若干時間を要するかと思います。m（_ _）m

お礼

tom04様
ご回答いただきまして有難うございました。
今回は、MackyNo1様に教えていただいた設定で表を作成することができました。
とても詳しく丁寧に教えていただき恐縮です。
マクロの存在は知っておりましたが、どうしようするのかわからなかったので
勉強になります。
有難うございました。

回答No.1

yosifuji20

考え方としては
まず元のデータの左に　＝ID&作業内容という式を作ります
IDがB列、作業内容がD列ならば
　A2＝B2&D2　という感じです。これをデータの行数だけセットします。
次に
　ID、作業内容、完了日
に昇順で並べ替えをします。

そのあとで、データ（D）の集計機能でA列をKeyにして集計をします。
その時に集計項目は完了日の最大値と指定します。

これで目的のデータができます。
表示を変えれば明細データを隠して答え行だけを表示することができます。

細かな使い方はヘルプでお調べください。

お礼

yosifuji20様
ご回答いただきまして有難うございました。
今回は、MackyNo1様に教えていただいた設定で表を作成することができました。
いろいろな方法があるのですね。
勉強になります。