Excelで在庫表(クエリ、ピボット)

在庫表をExcel(2016以降)のクエリとピボットで作成しようとしているのですが、
以下の点でつまずいております。

手順として、
1.担当者が個人のbookに日々、生産数と日付を入力します。
2.1を一つの管理表にまとめます。
（例）
日付　　　製品　　工程　 数量
2022/4/5　製品A　 工程1　100
2022/4/6　製品A　 工程2　 50
2022/4/5　製品A　 工程1　100

（工程は順に工程1→工程2→出荷があるとします。）

3.これを在庫表として、テーブルで作成したいのですが、ここでつまずいております。
　　　　　　 4/4　4/5　4/6　4/7　(2022は省略)
製品A　工程1 200
　　　 工程2 50
　　　　　出荷
と、なってしまいます。当たり前なのですが……。
　　　　　　 4/4　4/5　4/6　4/7
製品A　工程1 200　150
　　　 工程2 50
　　　　　出荷
のように、「昨日の在庫+今日の生産-今日の次工程生産数」を出さないといけません。

このような計算はクエリとピボットのみを使用して作成できるのでしょうか。
それともVBAを使用しないと自動ではできないでしょうか。
または、他の方法等ありますでしょうか。
ご教示いただきたく存じます。
宜しくお願いいたします。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2022/04/11 21:36

こんばんは

＞他の方法等ありますでしょうか。
クエリ内でご提示のような計算をするのは面倒と思います。

クエリでは、ご提示のように日別、工程別の表に素直に読み込んでおいて、最終の表はそれを参照しながら計算するようにしておけば、クエリを読み込めば結果が出るようになると思います。

最終の表には、あらかじめ
＞「昨日の在庫+今日の生産-今日の次工程生産数」
のような計算式を設定しておけば宜しいのではないでしょうか。

この回答へのお礼

こんにちは
ご回答ありがとうございます。

あらかじめ別sheetを作成した方が良さそうですね。
その場合ですが、クエリで読み込む表は日に日に行が増えていく予定です。
あらかじめ作成しておく表は関数式で対応することはできるのでしょうか。

宜しくお願いいたします。

お礼日時：2022/04/12 11:03

No.2 回答者： fujillin 回答日時： 2022/04/12 13:37

No1です

＞クエリで読み込む表は日に日に行が増えていく予定です。
＞あらかじめ作成しておく表は関数式で対応することはできるのでしょうか。
最終形を想定可能なのでしょうから、対応は可能だと想像します。

実際にどのような形になるのかわかりませんけれど、例えば日付もクエリから参照するようにしておけば良いことはお分かりになると思います。
計算式はそれに応じた式にしておく必要がありますが、内容がよくわからないのでどの程度変更する必要があるのかは不明です。
若干一般化する必要はあるのかも知れませんけれど、ほとんどが相対的なセルの位置関係で算出できるではないかと想像しますので、あまり変えなくても対応できるのではないでしょうか？

また、予め全部のセルに関数式を設定しておく方式の場合は、表示が不要な欄には空白文字を返すような式にしておいた方が、見やすいものになると思います。

この回答へのお礼

ご回答ありがとうございます。
頑張って作ってみます。

お礼日時：2022/04/12 14:39