エクセルで在庫表を作りたいのですが

質問

ネットショップのバイヤーをやっております。
今まではエクセルで仕入れ表を作り商品の在庫管理も同じ表を使い管理してきましたが、商品数が増えて今までの方法では管理できないようになってしまいました。

フリー配布の在庫表でもかまわないのですがネックになっているのが海外からの仕入れ金額の反映方法で同じ商品を別の地区で購入することがあり、州ごとに税金が違うなど管理方法が面倒である点です。

今までは在庫表としてこれらをコピペしたものを使い、売れるたびに商品番号を過去の在庫表からひとつずつ探して削除しており手間がかかってしまいました。

今までの仕入れ表（これと同じものを在庫表としてコピペ利用）

購入地　日付　商品番号　購入金額　消費税　個数　購入金額トータル　レート（買い付け時）日本円での金額

上記のような項目を作り管理しております。
購入地によって変動するのが消費税、レートはそのときの円高、円安で変動します。
同じ商品番号の商品を別の場所で複数購入する場合もあるので在庫表上で同じ商品としてカウントしたい
例・Ａ州で００１という商品を二個購入
　・Ｂ州で同じ００１を10個購入
在庫表では12個の入庫数として自動で反映したい。

欲しい機能と項目

同じ商品番号があれば在庫として集計して別に作る在庫表に自動でカウントしたい
日本円での購入金額も上の仕入れ表に打ち込むと在庫表に反映できるようにしたい

在庫表として欲しいもの

商品コード（ハンドバックならＡ，ショルダーならＢという大カテゴリー）
商品番号　（これは上記の仕入れで商品番号を打ち込むと連動して反映できるように）
日付　（手打ちで問題なし）
購入地　（手打ち後にオートフィルタで対応予定）
入庫数　（買い付け時の個数、同じ商品番号があった場合に自動でカウントしたい）
出庫数　（都度、手打ちにて入力）
在庫数　（入庫数ー在庫数）
購入金額（買い付け時の日本円税込み価格）
販売金額（日本での販売価格）

売り上げ　（販売金額ー購入価格の差額）在庫数ー出庫数で自動にカウントできればよい
繰越　残った在庫数とその金額

以上の機能となります。
簡単な計算式は理解できるのですが、仕入れ表で入力した数字を在庫表にどうやって反映させるのかなどわからずここで教えていただければ幸いです。
また実際に同じように海外買い付けなどされている方からのアドバイスなども御座いましたらご教授ください。
よろしくお願いいたします。

回答No.1

Pakkun10

全体的な構想ではなく、部分的なアイディアを書いてみたいと思います。

・仕入をしたときに仕入数を10と入れます。売り上げたときにその下に同じ商品を入力して仕入数と同じ列に-1と入れます。
集計すると仕入数の列は9になるわけです。
このように集計機能をつかえば、コピーしなくて済むのではないでしょうか？

たとえば、
品名 区分 数量
りんご 仕入 10
りんご 売上 -1
りんごの合計 9 ←在庫
みかん 仕入 5
みかん 売上 -3
みかんの合計 2 ←在庫

・ピボットテーブルという機能があって、入力されたデータを集計して別シートに表示することができる機能があります。
この機能を使えば、入力したデータを自由に集計出来て便利かと思います。調べてみると使えるかもしれませんね。

お礼

早速のアドバイスありがとうございました。
ピボットテーブル早速調べてやってみます。ありがとうございました。