Excelで表を展開するには？

質問

客先より下のような発注書が、エクセルで届きます。

商品名　単位　Ａ社　Ｂ社　Ｃ社　・・・　合計
---------------------------------------------
いちご　20個　　10　　 0　　 0　・・・　(数式)
いちご　50個　　 5　　20　　 0　・・・　(数式)
みかん　10個　　10　　 0　　 5　・・・　(数式)
みかん　30個　　 5　　10　　 0　・・・　(数式)
りんご　 5個　　 0　　 5　　10　・・・　(数式)
りんご　10個　　 0　　 5　　10　・・・　(数式)

これを以下のようなリストを作成するために展開したいのです。

商品名 単位 出荷先 個数
--------------------------
いちご　20個　　Ａ社　　10
いちご　50個　　Ａ社　　 5
いちご　50個　　Ｂ社　　20
みかん　10個　　Ａ社　　10
みかん　10個　　Ｃ社　　 5
みかん　30個　　Ａ社　　 5
みかん　30個　　Ｂ社　　10
りんご　 5個　　Ｂ社　　 5
りんご　 5個　　Ｃ社　　10
りんご　10個　　Ｂ社　　 5
りんご　10個　　Ｃ社　　10

別シートにセル参照で書き出してみたのですが、出荷先名や出荷先数が変動するため、日が変わると合計が入ったり出荷先が漏れたりしてしまい、うまくいきません。

上記のような表を、別シートか、もしくはCSVでエクスポートする方法があれば教えてください。

よろしくおねがいします。

回答No.5

GRATIAS · ベストアンサー

No.3です。
マクロでよいなら・・・。
ソース汚いけど許してください。

データのシート名を「データ」、作成後を「処理後」とします。
---
'ワークシート"データ"の設定
Dim i As Integer 'セル座標指定用（行数）
Dim j As Integer 'セル座標指定用（列数）
Dim X As Integer '最大行数
Dim Y As Integer '最大列数

'ワークシート"処理後"の設定
Dim p As Integer 'セル座標指定用（行数）

'最大行数を求める
X = 2 'データはここから
Do While Worksheets("データ").Cells(X , 1).Value <> ""
X = X + 1
Loop
X = X - 1

'最大列数を求める
Y = 3 'データここから（A社・B社・・・）
Do While Worksheets("データ").Cells(1 , Y).Value <> ""
Y = Y + 1
Loop
Y = Y - 1

'タイトルを設定する。
Worksheets("処理後").Cells(1, 1).Value = "商品名"
Worksheets("処理後").Cells(1, 2).Value = "単位"
Worksheets("処理後").Cells(1, 3).Value = "出荷先"
Worksheets("処理後").Cells(1, 4).Value = "個数"

'データ入力処理
p=2
For j = 3 to Y
For i = 2 to X
Worksheets("処理後").Cells(p, 1).Value = Worksheets("データ").Cells(i,1).Value '商品名
Worksheets("処理後").Cells(p, 2).Value = Worksheets("データ").Cells(i,2).Value '単位
Worksheets("処理後").Cells(p, 3).Value = Worksheets("データ").Cells(1,j).Value '出荷先
Worksheets("処理後").Cells(p, 4).Value = Worksheets("データ").Cells(i,j).Value '個数
p = p + 1
Next i
Next j
---
あ、値がゼロのデータを省いていません。
適当に修正してください。

お礼

ありがとうございます。
ご返事遅くなり、申し訳ありません。

やはりマクロ(VBA)のほうが、ボタンに登録できるので使用者にとっても楽なようです。

参考にさせていただきます。
ご丁寧な説明、ありがとうございました。

回答No.8

imogasi

#4です。
関数式でやると、複雑化するといいましたが、自称imogasi方式でやってみました。
私にとっては同じパターンの繰り返しですが、初めての人には複雑に
見えて、難しく感じると思いますが、よろしく。
例データ　Sheet1のA1:E８　に
　　　　　A B C D
いちご20個 10 0 0 1
いちご50個 5 20 20 4
みかん10個 10 0 5 6
みかん30個 5 0 0 7
りんご5個 0 5 10 9
りんご10個 5 5 10 12
りんご20個 0 5 0 13
Sheet1の　J2:M100について　(第１行目はあけておくこと）
J2に
=INDEX($A$2:$A$100,INT((ROW()-2)/4)+1,1)
K2に
=INDEX($B$1:$E$1,1,MOD(ROW()-2,4)+1)
L2に
=INDEX($B$1:$E$1,1,MOD(ROW()-2,4)+1)
M2に
=IF(L2=0,"",MAX($M$1:M1)+1)
それぞれJ2,K2,L2,M2の式を下方向に複写する
結果　J2:M２９　M列で０以外に連番を振ってます。
いちご20個 A 10 1
いちご20個 B 0
いちご20個 C 0
いちご20個 D 1 2
いちご50個 A 5 3
いちご50個 B 20 4
いちご50個 C 20 5
いちご50個 D 4 6
みかん10個 A 10 7
みかん10個 B 0
みかん10個 C 5 8
みかん10個 D 6 9
みかん30個 A 5 10
みかん30個 B 0
みかん30個 C 0
みかん30個 D 7 11
りんご5個 A 0
りんご5個 B 5 12
りんご5個 C 10 13
りんご5個 D 9 14
りんご10個 A 5 15
りんご10個 B 5 16
りんご10個 C 10 17
りんご10個 D 12 18
りんご20個 A 0
りんご20個 B 5 19
りんご20個 C 0
りんご20個 D 13 20
Sheet2に行って
A1は
=INDEX(Sheet1!$J$2:$M$100,MATCH(ROW(),Sheet1!$M$2:$M$100,0),1)
B1は
=INDEX(Sheet1!$J$2:$M$100,MATCH(ROW(),Sheet1!$M$2:$M$100,0),2)
C1は
=INDEX(Sheet1!$J$2:$M$100,MATCH(ROW(),Sheet1!$M$2:$M$100,0),3)
と入れてそれぞれ下方向に式を複写する。
結果　A1:C20に
いちご20個 A 10
いちご20個 D 1
いちご50個 A 5
いちご50個 B 20
いちご50個 C 20
いちご50個 D 4
みかん10個 A 10
みかん10個 C 5
みかん10個 D 6
みかん30個 A 5
みかん30個 D 7
りんご5個 B 5
りんご5個 C 10
りんご5個 D 9
りんご10個 A 5
りんご10個 B 5
りんご10個 C 10
りんご10個 D 12
りんご20個 B 5
りんご20個 D 13
実際は
該当存在数を超えた行にはエラー表示が出ますが、防ぎ方はありますが、長くなるので略します。
（OKWAVEで「imogasi方式」で照会してもらえば、これと似たとき方の質問が、沢山出ますので、そこには一部載せてるのがあるので見てください。）
計数０のところが、空白だったり、０と混合していたりすると、式が
複雑化しそうですが、略。

お礼

ありがとうございます。
ご返事遅くなり、申し訳ありません。

Sheet1のL2の式を
=INDEX($B$2:$E$100,INT((ROW()-2)/4)+1,MOD(ROW()-2,4)+1)
にしたところ、動作いたしました。
これに表の列数を取得する方法を組み合わせれば、うまくいくかもしれません。

いろいろ試してみます。
ご丁寧な説明、ありがとうございました。

回答No.7

taspon

#6です。
回答の中でC1セルが全く違う式になっていました。
訂正します。

A1=INT((ROW()-1)/列数)+1
B1=MOD(ROW()-1,列数)+1
C1="元!r1"&"c"&B1+2 　　 <--訂正
D1=INDIRECT(C1,FALSE)
E1="元!r"&A1+1&"c1"
F1=INDIRECT(E1,FALSE)
G1="元!r"&A1+1&"c"&B1+2
H1=INDIRECT(G1,FALSE)
（以下行を下へコピー）

回答No.6

taspon

自分で作るならVBAにしますが、関数でも可能です。
思ったほど複雑にはなりませんでした。

元表の何行何列目を参照すればよいかを求めてR1C1形式の文字列を生成できれば、あとはindirect関数のfalse指定で参照できます。
0は含まれてしまうので、ここだけフィルターで操作することになります。

１．元シートを作成

　シート名　”元”で作成し、データを入れます。
　元のシートは１行目に見出し（A1=商品名,B1=単位・・・）
　として明細行は２行目からとして下さい。

２．対象の列数（出荷先数）を調べる式を用意します。

　結果のシート上でもかまいませんが、説明の都合上、
　新しい別シートに作成します。（シート名任意）

元シートの見出しが元シートの１行目にあるとして
　A1に　=COUNTA(元!1:1)-3
（-3で合計,商品名,単位を除いています）

ご質問の例では3が表示されます。

A1セルに”列数”という名前をつけておきます。

２．結果表示用のシート

　別の新しいシートを作成します（名称任意）
　説明上作業列を用意していますが、まとめてしまえば不要です。

A1 =INT((ROW()-1)/列数)+1
B1 =MOD(ROW()-1,列数)+1
C1 ="元!r"&A3+1&"c1"
D1 =INDIRECT(C1,FALSE)
E1 ="元!r"&A1+1&"c1"
F1 =INDIRECT(E1,FALSE)
G1 ="元!r"&A1+1&"c"&B1+2
H1 =INDIRECT(G1,FALSE)

結果

A B C D E F G H
-----------------------------------------------
1 1 元!r1c3 Ａ社 元!r2c1 いちご 元!r2c3 10
1 2 元!r1c4 Ｂ社 元!r2c1 いちご 元!r2c4 0
1 3 元!r1c5 Ｃ社 元!r2c1 いちご 元!r2c5 0
2 1 元!r1c3 Ａ社 元!r3c1 いちご 元!r3c3 5
2 2 元!r1c4 Ｂ社 元!r3c1 いちご 元!r3c4 20
2 3 元!r1c5 Ｃ社 元!r3c1 いちご 元!r3c5 0
3 1 元!r1c3 Ａ社 元!r4c1 みかん 元!r4c3 10
3 2 元!r1c4 Ｂ社 元!r4c1 みかん 元!r4c4 0

すみません、ご質問と表示順が違いますが入れ替えてください。
単位の項目も略してありますが、考え方は同じです。

お礼

ありがとうございます。

作業列を別シートに置けば、すっきりしますね。

参考になりました。
丁寧な説明、ありがとうございました。

回答No.4

imogasi

これはVBA向けの問題ですね。
VBAの経験ない人でも、やりたくなる場面があると思いますが、関数式では、不定の数（リンゴでA,BC社の社数やどの会社か）のものは扱いにくい。
できても式を作るのに独特の着想が必要で、複雑化します。こういうのは不可能というべきものと思います。
また多分後の処理が控えていて、、ピボットテ－ブルの表ではダメなのでしょうね。

お礼

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

おっしゃるとおり、ピボットテーブルではその後の処理に使いにくいのです。
VBAでは可能なのでしょうか？
考え方だけでも教えていただければ幸いです。

回答No.3

GRATIAS

ACCESS　にデータをインポートした場合。

１．テーブル１にデータを読み込みます。
２．ユニオンクエリーで、ＳＱＬに、

　　SELECT　商品名，単位，Ａ社　
　　FROM　テーブル１
　　WHERE　Ａ社＜＞０
　　UNION　ALL
　　SELECT　商品名，単位，Ｂ社
　　FROM　テーブル１
　　WHERE　Ｂ社＜＞０
　　UNION　ALL
　　SELECT　商品名，単位，Ｃ社
　　FROM　テーブル１
　　WHERE　Ｃ社＜＞０
　　;

　等と記述し、クエリー１として保存します。
３．クエリー１をエクセルにエクスポートします。

面倒ですね．．．（汗）

お礼

早速のお返事ありがとうございます。

項目名(出荷先名)が毎回違うと、SQLでも記述しにくいですね。(union all select～from～が増減してしまう)

参考になりました。ありがとうございます。

回答No.2

can

以下のような表（単位を商品名の1部とします）からピボットテーブルを作ることができます

商品名 Ａ社 Ｂ社 Ｃ社 合計
いちご20個 10 0 0 10
いちご50個 5 20 0 25
みかん10個 10 0 5 15
みかん30個 5 10 0 15
りんご5個 0 5 10 15
りんご10個 0 5 10 15

で レイアウトを作成するときに
1.行 に [商品名]フィールドを
2.データ に [A社][B社][C社]フィールドをドラッグします

この時、データエリア内の3つのフィールドの集計方法を[合計]とすると、以下のような表が作成されますが・・・・・

商品名 データ 計
いちご20個 合計 : Ａ社 10
合計 : Ｂ社 0
合計 : Ｃ社 0
いちご50個 合計 : Ａ社 5
合計 : Ｂ社 20
合計 : Ｃ社 0
みかん10個 合計 : Ａ社 10
合計 : Ｂ社 0
合計 : Ｃ社 5
みかん30個 合計 : Ａ社 5
合計 : Ｂ社 10
合計 : Ｃ社 0
りんご10個 合計 : Ａ社
合計 : Ｂ社 5
合計 : Ｃ社 10
りんご5個 合計 : Ａ社 0
合計 : Ｂ社 5
合計 : Ｃ社 10

ちょっと形が変わってしまいますが・・・・
出荷先名が変動した場合は、列の挿入(削除)後,"2."のフィールドの追加(削除)をした後で、データ更新をすると、ピボットデーブルの内容も更新されます

一度お試しください

お礼

早速のお返事ありがとうございます。

No.1のchar0078様でもコメントさせていただきましたが、この状態(ピボットテーブル)ですと、次の処理につなぎにくいのです。
この表がCSVでエクスポートできれば、Access等で処理できるのですが。

参考になりました。ありがとうございます。

回答No.1

char0078

ピボットテーブルを使えば似たような感じにはなります。

商品名　　単位　　　　データ　　　　　　　　計
いちご　　20個　　　　合計 : Ａ社　　　　　10
　　　　　　　　　　　　　合計 : Ｂ社　　　　　0
　　　　　　　　　　　　　合計 : Ｃ社　　　　　0
　　　　　　　50個　　　　合計 : Ａ社　　　　　5
　　　　　　　　　　　　　合計 : Ｂ社　　　　　20
　　　　　　　　　　　　　合計 : Ｃ社　　　　　0
みかん　　10個　　　　合計 : Ａ社　　　　　10
　　　　　　　　　　　　　合計 : Ｂ社　　　　　0
　　　　　　　　　　　　　合計 : Ｃ社　　　　　5
　　　　　　30個　　　　合計 : Ａ社　　　　　5
　　　　　　　　　　　　　合計 : Ｂ社　　　　　10
　　　　　　　　　　　　　合計 : Ｃ社　　　　　0
りんご　　　10個　　　合計 : Ａ社　　　　　0
　　　　　　　　　　　　　合計 : Ｂ社　　　　　5
　　　　　　　　　　　　　合計 : Ｃ社　　　　　10
　　　　　　　5個　　　　合計 : Ａ社　　　　　0
　　　　　　　　　　　　　合計 : Ｂ社　　　　　5
　　　　　　　　　　　　　合計 : Ｃ社　　　　　10

このように、0の行も表示されてしまう欠点はありますが、これだと元の表が変わってもピボットテーブルを更新すれば良いだけなので簡単だと思います。

念のためやり方を。
元の表（数式列を除く）を選んでピボットテーブルウイザードを立ち上げます。
レイアウトで、行に商品名と単位を、データにＡ社、Ｂ社、Ｃ社をドラッグして完成です。

お礼

早速のお返事ありがとうございます。
ピボットテーブルも考えたのですが、自分がするのであればコピペ等をして、0の行を削除しながらその後の処理につなげるのですが、半分素人な作業員にさせるにはちょっと危険かなと思いまして。
またこの状態(ピボットテーブル)ですと、次の処理につなぎにくいのです。

参考になりました。ありがとうございます。