Excel VBAで、横並びのデータを縦並びにしたい

毎月の店舗ごとの商品別の売り上げが、図（元データ）のような形式で提出されます。
（縦軸が商品、横軸が店舗名と数量・金額・在庫数）

集計のため、横軸の項目（店舗名・数量・金額・在庫数）を、縦軸に並び替えるため、手動で行っているのですが、80店舗あるため、VBAで並び替える方法がありましたらご教示いただけますと幸いです。

縦軸の商品は月によって増えたり、減ったりします。
横軸の店舗数も月によって増えたり、減ったりします。
数量・金額・在庫数　は毎月固定です。

説明が拙く恐縮ですが、何卒よろしくお願いいたします。

質問者からの補足コメント

• ご説明不足で申し訳ありません。補足いたします。
  
  元データの「商品名」と書かれているセルの位置はA1セルになり、シート名は「元データ」です。
  並び替え先のシート名は「並替」で、こちらも「商品名」と書かれているセルの位置はA1セルになります。
  
  元データは社外から取得しているものになり、こちらの希望レイアウトでいただけないか打診しましたが、できないとの回答で今回ご相談を上げさせていただきました。
  
  どうぞよろしくお願いいたします。

    補足日時：2020/12/01 15:35

No.3 ベストアンサー 回答者： tatsumaru77 回答日時： 2020/12/01 19:23

標準モジュールに登録してください。

並替の１行目はあなたが作成してください。
マクロは２行目から設定します。
Option Explicit

Public Sub 並び替え()
Dim maxrow1 As Long
Dim maxcol1 As Long
Dim maxrow2 As Long
Dim row1 As Long
Dim row2 As Long
Dim col1 As Long
Dim shouhin_count As Long '商品数
Dim tenpo_count As Long '店舗数
Dim sx As Long
Dim tx As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("元データ")
Set sh2 = Worksheets("並替")
maxrow1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row '1列目最終行
maxcol1 = sh1.Cells(2, Columns.Count).End(xlToLeft).Column '2行目最終列
maxrow2 = sh1.Cells(Rows.Count, 1).End(xlUp).Row '1列目最終行
shouhin_count = maxrow1 - 2
If shouhin_count < 1 Then Exit Sub
If (maxcol1 - 1) Mod 3 <> 0 Then Exit Sub
tenpo_count = (maxcol1 - 1) \ 3
If tenpo_count < 1 Then Exit Sub
sh2.Rows("2:" & Rows.Count).ClearContents '並替の2行目以降をクリア
row2 = 2
'店舗数分の繰り返し
For tx = 1 To tenpo_count
'商品数分の繰り返し
For sx = 1 To shouhin_count
row1 = sx + 2
sh2.Cells(row2, "A").Value = sh1.Cells(row1, "A") '商品名
col1 = (tx - 1) * 3 + 2
sh2.Cells(row2, "B").Value = sh1.Cells(1, col1) '店舗名
sh2.Cells(row2, "C").Value = sh1.Cells(row1, col1) '売り上げ数
sh2.Cells(row2, "D").Value = sh1.Cells(row1, col1 + 1) '売り上げ金額
sh2.Cells(row2, "E").Value = sh1.Cells(row1, col1 + 2) '在庫数
row2 = row2 + 1
Next
Next
MsgBox ("完了")
End Sub

この回答へのお礼

色々とご説明が足りず、申し訳ありませんでした。
早速試してみたところ、無事できました。
大変助かりました。
本当にありがとうございました。

お礼日時：2020/12/02 12:07

No.2 回答者： tatsumaru77 回答日時： 2020/12/01 04:35

各セルの正確な位置が判らないと、マクロを作成できません。

元データの商品名と書かれているセルはA1でしょうか。
並び替え先の商品名と書かれているセルはA１でしょうか。
元データのシート名はなんでしょうか。
並び替え先のシート名はなんでしょうか。
又、図を添付する場合は、添付図の赤線で囲った部分も提示してください。
そうするとセルの正確な位置がわかります。

No.1 回答者： fujillin 回答日時： 2020/11/30 17:18

こんにちは

VBAのご質問のようですが、関数でもできるのでご参考までに。

ご提示の図がはっきりと見えないのと、セル位置が不明なので考え方だけになりますけれど・・・

添付図で1～6行目までが元のデータ（行数は可変です）とし、11行目以降に縦並びに表示するものと仮定します。
また、9行目のB列、E列で商品数と店舗数を指定するものと仮定します。

添付図では、
A12セルに
=IF(ROW(A1)>$B$9*$E$9,"",INDEX(A:A,MOD(ROW(A1)-1,$B$9)+3))

B12セルに
=IF(ROW(A1)>$B$9*$E$9,"",INDEX($1:$1,INT((ROW(A1)-1)/$B$9)*3+2))

C12セルに
=IF(ROW(A1)>$B$9*$E$9,"",INDEX($B:$IV, MOD(ROW(A1)-1,$B$9)+3, INT((ROW(A1)-1)/$B$9)*3+COLUMN(A1)))
の式を入れて、E12セルまでフィルコピー

さらに、A12:E12を選択して、下方に必要な分だけフィルコピーしています。
（9行目の商品数、店舗数を変更すれば、それに応じた表示に変わります）



＞集計のため、横軸の項目（店舗名・数量・金額・在庫数）を、縦軸に並び替えるため、手動で行っているのですが、～
そもそもの話になりますが、元の表の形式を見直した方が宜しいようにも感じますが…？

この回答へのお礼

早々のご回答ありがとうございます。
色々とご説明が足りず、申し訳ありません。

元のデータは、社外から取得しているものになり、こちらの希望レイアウトでいただけないか打診しましたが、できないとの回答で今回ご相談を上げさせていただきました。

関数でもできるのですね。大変勉強になります。
早速試してみたいと思います。

お礼日時：2020/12/01 15:38