エクセルで注文書を作成発注しています。そのデータを入荷一覧表を作成した

質問

エクセルで注文書を作成発注しています。そのデータを入荷一覧表を作成したいのですが、教えて下さい

業務上で、予めあるエクセルで作った注文書を取引先毎ににパソコンからFAXしています。
各取引先への注文書は統一しております。その注文書を印刷し、今は入荷一覧表に入力しています。
その作業を改善したく、注文書に打ち込んだと同時に別に作った入荷一覧表に跳ね返す事ができたら
と思いますが、計算式がわかりません。
何方か、教えていただけませんでしょうか？

＜注文書の内容＞（エクセル画面）

　　Ａ　　　Ｂ　　Ｃ
1　得意先Ａ
2　商品名　数量　納品日
3　醤油　　　１　９/１
4　砂糖　　　２　９/４

　　Ａ　　　Ｂ　　Ｃ
1　得意先Ｂ
2　商品名　数量　納品日
3　味噌　　　１　９/２
4　塩　　　　２　９/５

＜入荷一覧表＞

　　Ａ　　　Ｂ　　Ｃ　　Ｄ　　Ｅ　　Ｆ　　
1　商品名　9/1　　9/2　9/3　9/4　9/5　　　　
2　醤油　　１
3　砂糖　　　　　　　　　　　　２　

お願い致します。

回答No.5

tom04

No.3・4です！
またまたお邪魔します。

ご希望としては日々更新される注文書のデータが前回の表に反映される方法だと思います。
そういうことだとして・・・

一つの案ですが
前回の数式は「入力用Sheet」の1000行目まで対応できる数式でしたが、
これを仮に10000（←これ以上その月のデータはない！というくらいの行数まで）に変更すれば
元データが空白でも問題ありませんので、とりあえず数式の範囲指定の領域を変更してみてはどうでしょうか？

そして、「注文書」のデータを入力し次第、「入力用Sheet」の最終行にコピーすると
そのまま「集計表」の方に反映されるはずです。

尚、この一連の「コピー＆ペースト」の操作をマクロに記録しておいて、マクロを実行するのも
一つの方法かと思います。
ただし、日々の「注文書」のデータ量が一定ではないと思いますので、
本来であれば「マクロの記録」ではなく、こちらでコードを作ってマクロを実行する方法が望ましいと思いますけどね！

「注文書」のSheetがどのような感じか判らないので具体的なコードが書けませんが
ごめんなさいね。m(__)m

お礼

面倒な質問ですみませんでした。
兎に角、やってみたいと思います。有難うございました。

回答No.4

tom04

No.3です！
得意先が20以上あっても対応できる方法ですが・・・
一つの案として、↓の画像のように入力用のSheetを1つ用意します。
仮にそのSheet名を「入力用Sheet」としています。
このSheetに日付順にとにかく入力していきます。
このデータを元にSheet2に集計するようにしてみました。
今回は得意先別の集計ではなく商品別の集計のようですので、
↓の画像のように「入力用Sheet」作業用の列を1列設けています。
作業列E2セルに
=IF(COUNTIF($C$2:C2,C2)=1,ROW(),"")
という数式をいれ、オートフィルで下へずぃ～～～！っとコピーします。
（これは単に、商品名の抽出で重複を防ぐためです）

Sheet2のA2セルに
=IF(COUNT(入力用Sheet!E:E)<ROW(A1),"",INDEX(入力用Sheet!C:C,SMALL(入力用Sheet!E:E,ROW(A1))))
として下へずぃ～～！とコピーすると商品名が重複なしに表示されます。

そして、B2セルに
=IF($A2="","",SUMPRODUCT((入力用Sheet!$A$2:$A$1000=B$1)*(入力用Sheet!$C$2:$C$1000=$A2)*(入力用Sheet!$D$2:$D$1000)))
として列方向と行方向にオートフィルでコピーすると
画像のような感じになります。
今回も　メニュー　→　ツール　→　オプション　→　「表示タブ」から「ゼロ値」のチェックを外して意います。

これで何とか希望に近い形にならないでしょうか？

尚、余計なお世話かも知れませんが、得意先別の条件を一つ追加すれば
得意先別・日付別の商品名が表示されます。

今回はその方法を載せるのは差し控えますが数式としては同じような数式になります。

この程度ですが、お役に立ちますかね？m(__)m

お礼

有難うございます。
入力用のsheet作成する方法でやってみました。何とか出来ました。有難うございます。
出来れば、注文書の画面の数量、納品日が一覧表に跳ね返れば良いのですが・・・
何か良い方法があれば教えて下さい。
※注文書の品名と一覧表の品名は一致しています。
※注文書は日々注文日をsheet名につけて上書き保存しています。

面倒な話で申し訳ございませんが、宜しくお願い致します。

回答No.3

tom04

こんばんは！
横からお邪魔します。

私もNo.2さんが仰っているように入力用Sheetとして取引先・商品名・日付が一気に入力できるSheetにした方が
後々、集計するにも簡単だと思います。
たとえば取引先ごとに集計もできますし、ご希望のような集計も簡単にできると思います。

あくまでお示しのように取引先がそれぞれ別Sheetになっているとすると
↓のような方法になるのではないでしょうか？

とりあえず取引先がA・Bの2社だけだとしての方法です。
画像の上側が集計Sheetで、下の左側が取引先A　右側が取引先B　になります。
取引先がもっとあるのなら数式もすごく長くなってしまいます。

画像の「入荷一覧表」の商品名はあらかじめ入力されているとしています。
「入荷一覧表」SheetのB2セルに
=SUM(SUMPRODUCT((得意先A!$A$3:$A$100=入荷一覧表!$A2)*(得意先A!$C$3:$C$100=入荷一覧表!B$1)*(得意先A!$B$3:$B$100)),SUMPRODUCT((得意先B!$A$3:$A$100=入荷一覧表!$A2)*(得意先B!$C$3:$C$100=入荷一覧表!B$1)*(得意先A!$B$3:$B$100)))

という数式を入れ、列方向と行方向にオートフィルでコピーしています。
尚、空白のセルには「0」が表示されてしまいますので、当方使用のExcel2003の場合は
メニュー　→　ツール　→　オプション　→　「表示タブ」の「ゼロ値」のチェックを外しています。

以上、長々と書いてしまいました。
参考になればよいのですが・・・m(__)m

お礼

tom4さん、Cerberosさん、end-Uさん　有難うございました。
tom4さんの計算式にて作成する事が出来ました。

誠に申し訳ありませんが、取引先数が２０を超える場合は又別なやり方があるのでしょうか？
もし、やり方がありましたら是非教えて下さい。
宜しくお願い致します。

回答No.2

end-u

試用のみで常用してない私が紹介するのもなんなんですが、
[データ追跡機能付きテンプレートウィザード]というアドインがあります。
http://kokodane.com/tec3_24.htm
（2007は別途ダウンロードする必要があるよう）
http://support.microsoft.com/kb/873209/ja

ご提示の＜入荷一覧表＞のレイアウトそのものではなく、
得意先　　商品名　数量　納品日
得意先Ａ　醤油　　　１　９/１
得意先Ａ　砂糖　　　２　９/４
得意先Ｂ　味噌　　　１　９/２
得意先Ｂ　塩　　　　２　９/５
：
というデータベース形式のものになるとは思いますが。
でもこちらの形式のほうが活用し易いです。

また、考え方を変えて、データベース形式の＜入荷一覧表＞に入力して、
印刷の時に別シートの注文書フォーマットを利用するようにしてはどうでしょう。
その場合はVLOOKUPやINDEX、MATCH関数などを使って簡単に処理できます。

回答No.1

cerberos

データ量にもよりますが、関数で一覧表にまとめるよりもエクセルの標準機能として
ある集約機能「ピボットテーブル」を利用した方が早いかもしれません。

「ピボットテーブルによる複数シートのくし刺し集計を行う」
http://www.atmarkit.co.jp/fwin2k/win2ktips/407pivokushi/pivokushi.html