エクセルにて受注一覧と出荷スケジュールを連動

質問

エクセルを使って受注一覧表と出荷スケジュールの連動をさせたいです。
イメージは以下の通りです。

Sheet1に注文一覧表を作ります。（現在、既にこの表はエクセルで存在します）
この表（Sheet1）では、出荷スケジュールが分かりづらいので、現在はSheet2の表を印刷し、手書きで数量を記入して管理をしています。

この手書きの作業がとても手間なので、できればSheet1からデータを読み込んで自動的にSheet2に数量が表示されるようにさせたいです。
その場合、Sheet2のB2にはどのような関数を入れれば良いのでしょうか。

Sheet1
注文番号　商品名　出荷月　出荷日　数量
1001　　　　AA-01　　　10　　　　2　　　100
1002　　　　AA-02　　　10　　　　2　　　150
1003　　　　AA-03　　　10　　　　2　　　200
1004　　　　AA-04　　　10　　　　4　　　300
1005　　　　AA-03　　　10　　　　5　　　100
　・
　・
　・

Sheet2
商品名　　　　１日　　　２日　　３日　　４日　　５日　～　３１日
AA-01
AA-02　　
AA-03　　
AA-04　　
AA-05
　・
　・
　・　　　　　　　
（商品名は400種類程度あります）

大変困っています。どなたか良い方法をご教示いただけないでしょうか。
よろしくお願いします。

回答No.2

kagakusuki · ベストアンサー

今仮に、Sheet1で「注文番号」と入力されているセルがSheet1のA1セルであるものとします。
　又、Sheet2のA2セルに「商品名」と入力し、Sheet2のA3以下に各商品名を入力し、Sheet2のB2～Sheet2のAF2にかけて、1～31の数値を入力し、Sheet2のA1セルには月を表す数値を入力するものとします。

【方法その1】作業列と関数を併用する方法
　まず、適当な列（ここでは仮にSheet3のA列とします）を作業列として使用する事とし、そのA2セルに次の数式を入力して下さい。

=IF(AND(INDEX(Sheet1!$B:$B,ROW())<>"",COUNT(INDEX(Sheet1!$C:$C,ROW()),INDEX(Sheet1!$D:$D,ROW()))=2,ISNUMBER((INDEX(Sheet1!$C:$C,ROW())&"/"&INDEX(Sheet1!$D:$D,ROW()))+0)),INDEX(Sheet1!$B:$B,ROW())&":"&INDEX(Sheet1!$C:$C,ROW())&"/"&INDEX(Sheet1!$D:$D,ROW()),"")

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。

　次に、Sheet2のB2～Sheet2のAF2にかけて、1～31の数値を入力して下さい。
　次に、Sheet2のB3セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet3!$A:$A,"="&$A3&":"&$A$1&"/"&B$2),SUMIF(Sheet3!$A:$A,"="&$A3&":"&$A$1&"/"&B$2,Sheet1!$E:$E),"")

　次に、Sheet2のB3セルをコピーして、Sheet2のC3～AF3の範囲に貼り付けて下さい。
　次に、Sheet2のB3～AF3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　後は、Sheet2のA3以下に各商品名を入力し、Sheet2のA1セルには月を表す数値を入力しますと、日付毎の各商品の出荷数が自動的に表示されます。

【方法その2】SUMPRODUCT関数で処理する方法（作業列は不要）
　※こちらの関数は、元データが500行以上でも、何行あっても、対応しますが、元データの行数が、数千行以上にもなる場合は、計算処理が重くなります。（元データの行数に合わせて、参照範囲が変化するため、行数が200～300行程度ならば、ANo.1様の関数よりは、早くなると思います）
　まず、Sheet2のB2～Sheet2のAF2にかけて、1～31の数値を入力して下さい。
　次に、Sheet2のB3セルに次の数式を入力して下さい。

=IF(SUMPRODUCT((Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$E:$E))=$A3)*(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9^9,Sheet1!$E:$E))=$A$1)*(Sheet1!$D$1:INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$E:$E))=B$2)),SUMPRODUCT((INDEX(Sheet1!$B:$B,ROW(Sheet1!$B$1)+1):INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$E:$E))=$A3)*(INDEX(Sheet1!$C:$C,ROW(Sheet1!$C$1)+1):INDEX(Sheet1!$C:$C,MATCH(9^9,Sheet1!$E:$E))=$A$1)*(INDEX(Sheet1!$D:$D,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$E:$E))=B$2)*INDEX(Sheet1!$E:$E,ROW(Sheet1!$E$1)+1):INDEX(Sheet1!$E:$E,MATCH(9^9,Sheet1!$E:$E))),"")

　次に、Sheet2のB3セルをコピーして、Sheet2のC3～AF3の範囲に貼り付けて下さい。
　次に、Sheet2のB3～AF3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　後は、Sheet2のA3以下に各商品名を入力し、Sheet2のA1セルには月を表す数値を入力しますと、日付毎の各商品の出荷数が自動的に表示されます。

【方法その3】Excel2007以降のパーションでのみ使用可能な関数で処理する方法（作業列は不要で、SUMPRODUCT関数よりも高速処理が可能）
　まず、Sheet2のB2～Sheet2のAF2にかけて、1～31の数値を入力して下さい。
　次に、Sheet2のB3セルに次の数式を入力して下さい。

=IF(COUNTIFS(Sheet1!$B:$B,$A3,Sheet1!$C:$C,$A$1,Sheet1!$D:$D,B$2),SUMIFS(Sheet1!$E:$E,Sheet1!$B:$B,$A3,Sheet1!$C:$C,$A$1,Sheet1!$D:$D,B$2),"")

　次に、Sheet2のB3セルをコピーして、Sheet2のC3～AF3の範囲に貼り付けて下さい。
　次に、Sheet2のB3～AF3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　後は、Sheet2のA3以下に各商品名を入力し、Sheet2のA1セルには月を表す数値を入力しますと、日付毎の各商品の出荷数が自動的に表示されます。

お礼

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

回答No.4

imogasi

タイプとしては、エクセルの「表の組み換え」です。関数でやろうとすると、既に他回答で出ているように、複雑になります。
ーー
一方VBAでやれば思ったとおりに簡単に出来ます。
仕事の絡んだようなこと（取引関係など）で、エクセルで処理するには、VBAが出来ることが必須だと思います。
VBAを勉強したことありますか。VBAを勉強するか、非常に意味も難しい関数式をコピペして、意味も判らず使うか。
好きなほうを選択するしかない。どちらも現状では、課題を丸投げで、「したい」といっているだけで、本質問に対し、基礎体力不足か。
ーー
例データ　Sheet1
注文番号 商品名 出荷月 出荷日 数量
1001 AA-01 10 2 100
1002 AA-02 10 2 150
1003 AA-03 10 2 200
1004 AA-02 10 2 50
1005 AA-04 10 4 300
1006 AA-04 10 4 30
1007 AA-03 10 5 100
1008 AA-01 10 6 50
1009 AA-02 10 6 70
1010 AA-03 10 6 80
1011 AA-01 10 6 40
1012 AA-02 10 6 10
1013 AA-03 10 7 20
別シートにコピーし商品名でソート　Sheet2　A1:E14
注文番号 商品名 出荷月 出荷日 数量
1001 AA-01 10 2 100
1008 AA-01 10 6 50
1011 AA-01 10 6 40
1002 AA-02 10 2 150
1004 AA-02 10 2 50
1009 AA-02 10 6 70
1012 AA-02 10 6 10
1003 AA-03 10 2 200
1007 AA-03 10 5 100
1010 AA-03 10 6 80
1013 AA-03 10 7 20
1005 AA-04 10 4 300
1006 AA-04 10 4 30
ーーー
コード　ヒョウジュンモジュール
Sub test01()
Dim sh2, sh3
Set sh2 = Worksheets("Sheet2")
Set sh3 = Worksheets("Sheet3")
' 準備
d = sh2.Range("a65536").End(xlUp).Row 'データ最終行
m = sh2.Cells(2, "B")
md = DateSerial(2011, sh2.Cells(2, "C"), sh2.Cells(2, "D"))
MsgBox md
k = 2
sh3.Cells(2, "A") = sh2.Cells(2, "B")
'--
For i = 2 To d
dt = DateSerial(2011, sh2.Cells(i, "C"), sh2.Cells(i, "D")) '日付作成
If sh2.Cells(i, "B") = m Then '商品が変わったか
'--変わらない
c = sh3.Range("B1:Z1").Find(dt).Column ' 該当日付の列を見つける
sh3.Cells(k, c) = sh3.Cells(k, c) + sh2.Cells(i, "E") 'その列に数量を足しこむ
Else
'--変わった
k = k + 1 '次の商品のため、次行に
sh3.Cells(k, "A") = sh2.Cells(i, "B")
c = sh3.Range("B1:Z1").Find(dt).Column '該当日付の列を見つける
sh3.Cells(k, c) = sh3.Cells(k, c) + sh2.Cells(i, "E") 'その列に数量を足しこむ
'--次の行処理の準備
m = sh2.Cells(i, "B")
md = DateSerial(2011, sh2.Cells(i, "C"), sh2.Cells(i, "D"))
End If
Next i
End Sub
実行結果　Sheet3　A1:H4
下記日付は日付シリアル値を使っているから注意。それを「１日」のように表示する表示形式は、わかっているよね。
商品名 2011/10/1 2011/10/2 2011/10/3 2011/10/4 2011/10/5 2011/10/6 2011/10/7　　－＞以下略
AA-01 - 100 - - - 90
AA-02 - 200 - - - 80
AA-03 - 200 - - 100 80 20
AA-04 - - - 330
ーは空白セルを示す。

お礼

早速の回答ありがとうございます。
アドバイスの通りじっくりVBAも勉強してみます。

回答No.3

keithin

状況から推測すると，関数を使って作成する方法は全くお奨めできません。（元データのリストが伸びていくと，加速度的にシートの計算が重くなっていきます。実際に対象とするリストの行数次第で，耐えられる範囲内であることが確認できれば，関数もありです）

ご利用のエクセルのバージョンが不明ですが，Excel2003まではデータメニュー，2007以降では挿入タブから「ピボットテーブルレポート」の作成を開始し，ページ（2007以降ではフィルタ）に「月」，行に「日」，列に「商品」，データ(2007以降ではΣ）に「数量」を投入することで，メンドウな関数を一切使わずに（オマケで当月発生する商品一覧をわざわざ事前に用意する必要もなく），必要な集計が直ちに得られます。

ページフィルタに現れる選択肢から，集計したい月を選びます。

お礼

ご回答ありがとうございます。
バージョンは2007です。早速試してみます。

回答No.1

mu2011

一例です。
B2に以下の数式を入力、縦横にコピー
=IF($A2<>"",SUMPRODUCT((Sheet1!$B$2:$B$500=$A2)*(Sheet1!$D$2:$D$500=B$1)*Sheet1!$E$2:$E$500),"")

お礼

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