エクセルで複数のデータがあるものを一つにする

質問

こんにちは。
過去記事を検索しても見つからなかったので、こちらに質問させていただきます。

エクセル2010を使って、商品の配送管理をするデータを作成しているのですが、
データに「全セット」というものがあり、「全セット」の内訳をエクセルの関数を使って、商品項目に1つづつ反映したいのですが、全く分からなくて苦戦しております。

データはエクセルの「外部データの取り込み」を使ってアクセスから抽出しております。

例として、
--------------------------------------------
名前　　電話番号　　商品名　　数量 　合計
--------------------------------------------
吉田　　00000000　　イチゴ　　　　1　　　700

宮本　　11111111　　全セット　　　1　　　1000

松崎　　22222222　　リンゴ　　　　2　　　500

小川　　33333333　　みかん　　　　1　　　300
--------------------------------------------

これを

--------------------------------------------
名前　　電話番号　　商品名　　数量 　合計
--------------------------------------------
吉田　　00000000　　イチゴ　　　　1　　　700

宮本　　11111111　　イチゴ　　　　1　　　1000

宮本　　11111111　　リンゴ　　　　1　　　1000

宮本　　11111111　　みかん　　　 1　　　1000　

松崎　　22222222　　リンゴ　　　　2　　　 500

小川　　33333333　　みかん　　　　1　　　300
--------------------------------------------

このようにセット商品の内訳を表示したいのです。

名前、電話番号、数量、合計は、別シートにあるデータからVLOOKUP関数を使って反映しています。

お知恵をお貸しください！！！

説明が分かり辛くて申し訳ありません。
ご教示お願いいたします。

回答No.2

tom04 · ベストアンサー

こんばんは！
No.1さんが仰っているような疑問は残るのですが・・・

とりあえず質問を素直に解釈して
VBAになってしまいますが、一例です。

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

Sub Sample1() 'この行から
Dim i As Long, k As Long, cnt As Long, c As Range
Dim wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")

Application.ScreenUpdating = False
With wS1.Cells(1, 1).CurrentRegion
.Value = .Value
End With
For i = 2 To wS1.Cells(Rows.Count, 1).End(xlUp).Row
If wS1.Cells(i, 3) <> "全セット" Then
Set c = wS2.Range("A:A").Find(what:=wS1.Cells(i, 3), LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
cnt = cnt + 1
wS2.Cells(cnt, 1) = wS1.Cells(i, 3)
End If
End If
Next i

For i = wS1.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If wS1.Cells(i, 3) = "全セット" Then
wS1.Rows(i + 1 & ":" & i + cnt - 1).Insert
For k = 1 To cnt
With wS1.Cells(i + k - 1, 1)
.Value = wS1.Cells(i, 1)
.Offset(, 1) = wS1.Cells(i, 2)
.Offset(, 2) = wS2.Cells(k, 1)
.Offset(, 3) = wS1.Cells(i, 4)
.Offset(, 4) = wS1.Cells(i, 5)
End With
Next k
End If
Next i
wS2.Columns(1).ClearContents
Application.ScreenUpdating = True
End Sub 'この行まで

※　Sheet1のデータは実データに変えていますので、関数は消えてしまいます。
※　Sheet2を作業用のSheetとして使用していますので、Sheet2は何もデータが入っていないという前提です。
※　一旦マクロを実行すると元に戻せませんので、別Sheetでマクロを試してみてください。

こんなんではどうでしょうか？m(_ _)m

お礼

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

希望通りの一覧ができました。
ご教示ありがとうございます。

回答No.1

shintaro-2

>データに「全セット」というものがあり、「全セット」の内訳をエクセルの関数を使って、商品項目に1つづつ反映したいのですが、全く分からなくて苦戦しております。

何故そのように変更する必要があるのでしょう？

もともと何か意図があって「全セット」にしていたと思うのですが？
個々のアイテムの集計をするときに、全セットもカウントすれば良いだけだとおもうのですが・・・

特に
宮本　　11111111　　イチゴ　　　　1　　　1000
宮本　　11111111　　リンゴ　　　　1　　　1000
宮本　　11111111　　みかん　　　 1　　　1000　

としてしまうと、合計金額がでたらめなものになってしまいますよ。

お礼

ご回答ありがとうございます。
その通りだと思います。ですが、色々な事情によりエクセルの関数を使ってデータを変更するということになりました。
金額ですが、全セットの金額を付け加えたいので、上記のように表示いたしました。
今回購入者の内訳を知りたいということだったのでこのような一覧にいたしました。