EXCEL 名前を特定しないで在庫増減する方法

質問

こんにちは。EXCELで在庫表を動かす関数とマクロを組んでおりますが、つまずいてしまいました。
どうか皆様の知恵をお貸し願えたらと思います。

現在、(画像上)のように、JANコードでまとめた店別の商品在庫表があります。

各店から、在庫の＋－をあらわすCSVデータ(画像中央)が送られてくるのですが、これをEXCELに取り込み、JANコードをキーにして、＋－の数値を在庫表に反映させ、(画像下)のようにしたいと思っています。
ちなみに「数量」の項目はいつも必ず1です。
「増減」で＋－をあらわしています。
(りんごが３つ減ったら、増減「－」数量「３」ではなく、「-」「1」が3つ並んでデータに表示されます)

ですが、ここで問題が出てきてしまいました。

サンプル画像は商品もJANコードも３種類しかありませんが、実際には1000種類以上の商品があり、在庫表のデータベースももっと縦に長くなっています。
商品の数が少なくて、売れるものも毎日決まっていれば、VLOOKUPやCOUNTIF関数で「りんごのマイナスは何個」と指定して数量を抜き出すことができますが、毎日ちがう商品のデータが何種類も入ってくるため、「りんご」「みかん」といちいち指定して抜き出す手間がかけられません。

JANコードを軸にして、毎回ほぼランダム同然に送られてくる商品の在庫数を一度に表に変換できないものでしょうか。
いろいろ関数の組み合わせや作業プロセスを考えてみましたが、多くの関数は、特定の"名前"を指定して、数を抜き出すものなので、なかなかうまくいかずに困っています。

また大変恐縮ですが、マクロでワンタッチでできるようにしたいと思っているので、良いコードがあれば教えていただきたいと思っています。

どうかよろしくお願い致します。

回答No.2

list · ベストアンサー

#1です。

先程の補足です。

(1) ⇒ Janコード及び店舗別の数量をカウントするため
(2) ⇒ 複数のJANコードを整理
　=IF(COUNTIF(A$2:A2,A2)=1,A2,"")
(3) ⇒ JANコードを整列
　=IF(ISERROR(SMALL(E$2:E$9,G2))=TRUE,"",SMALL(E$2:E$9,G2))
(4) ⇒ 品名
　=IF(H2="","",VLOOKUP(H2,A$2:B$9,2,FALSE))
(5) ⇒ A店の数量
　=IF(I2="","",COUNTIF($D$2:$D$9,$H2&J$1))
(6) ⇒ B店の数量

実際使用される場合は、(1)に「＋」「－」の条件を加味し、(5)及び(6)の計算式で「＋」の個数から「－」の個数を引けば、実際の数量になると思います。

あとは、
・　値だけ複写
・　余分な計算式の削除

以上で、先程の『増減表の作成』が完成すると思います。

お礼

おかげさまで、完成にだいぶ近づきました。
使ったことのない関数が出てきたので難しそうに見えたのですが、関数の意味をひも解きながらやったら、どうにか仕組みが頭に入りました。
組み合わせの仕方が本当に工夫しだいといいますか、回答がひとつでないから逆に難しいんですよね。
会社にひとりもEXCEL熟練者がいないので、本当に助かりました。
ありがとうございます。

回答No.1

list

毎回、新たな増減表を作成し、前回の在庫表と調整する方法でよければ

『増減表の作成』
１．JANコード順にソート
２．同じコードを整理し品目ごとの一覧表を作成。(if関数とcountif関数)
３．JANコードと店名が一致する個数をカウント

『前回の在庫表と調整』
１．前回の在庫表のJANコードと今回の増減表のJANコードを併せて、『増減表の作成』の『２』と同じ考え方で新規一覧表を作成。
２．新しく作成した一覧表に前回の在庫と今回の増減を調整した表を作成。
３．必要に応じて、前回の在庫表を削除。

この方法で集計表を作成していました。
なお、この方法は、全て、手動（関数のみ）で出来ます。

お礼

どうもありがとうございます！
いま閲覧したばかりです。さっそくこれから試してみます、できたらまた返答させていただきます。