エクセル 商品棚卸の照合表を作りたいのですが・・・

質問

エクセルで商品棚卸の照合表を作ろうとしているのですが上手くいきません。
VLOOKUP関数を使って、在庫帳と棚卸票の照合をしようしたのですが、棚卸票にあるのに在庫帳にない商品が相当数ある為、在庫帳を基準にした照合表ではデータの拾い漏れが発生してしまいます。
２つの表のデータ（商品名）をダブらせずに一つの表に転記したいのですが・・・。
どうかアドバイスをお願い致します。

回答No.1

keithin · ベストアンサー

データメニューの統合を使い，２つのリストの寄せ集めを簡単に行えます。

手順：
まとめの集め表を配置したいセル(添付図：B10)を選ぶ(必須)
データメニューの統合を開始する
それぞれのリストのセル範囲を追加する
左端列，上端行にチェックを入れてＯＫする

必要に応じて差し引きの単純な引き算の式を追加する。

＃別の考え方
＞棚卸票にあるのに在庫帳にない商品が相当数ある為、
＞在庫帳を基準にした照合表ではデータの拾い漏れが発生してしまいます。

データが足りないのが判っている在庫帳を基準にしてもしかたありません。
敢えて言えばモノが「ある」のが判っている棚卸票を基準にして，在庫帳をVLOOKUPしてくるのが適正と言えます。
また実際には棚卸票と在庫帳の商品一覧を単純に縦に続けてコピーして並べ，データメニューのフィルタから「フィルタオプションの設定」を使って（オートフィルタのオプションではないので間違えないこと）「重複を無視する」で漏れもダブりも無い商品一覧リストを作成します。

あとはVLOOKUP関数でそれぞれの商品を各リストから調べてくれば，必要な突き合わせ表を作成できます。

お礼

ありがとうございます。
「フィルターオプションの設定」で商品一覧を作って、在庫差異リストを作成することができました。

回答No.3

KURUMITO

初心者でもデータを入力していくだけで結果が表示されるようにしておくことが便利ですし、分かり易い方法で処理をすることが計算に負担を掛けない方法として適当でしょう。そのためには作業列を使って処理をすることです。作業列が目障りでしたらその列を選択して右クリックし「非表示」を選択しましょう。
シート1には在庫表が、シート2には棚卸表があるとしてシート3にお望みの表を作るとします。
シート1ではA2セルに商品名、B2セルに数量の文字があり、各データはそれぞれの下行に入力されるとします。
C3セルには次の式を入力して下行にオートフィルドラッグします。

=IF(COUNTIF(A$3:A3,A3)=1,MAX(C$2:C2)+1,IF(COUNTIF(A$3:A3,A3)>1,"重複記載",""))

この式では在庫表に同じ商品名がダブって入力された場合には2つ目の商品名には重複記載が表示されるようにしています。しかし、ダブって記載されていても在庫の数量は問題なくカウントされるようにします。
シート2でもシートと同じようにしますが、C2セルには次の式を入力します。

=MAX(Sheet1!C:C)

C3セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(Sheet1!A:A,A3)>0,"",IF(COUNTIF(A$3:A3,A3)=1,MAX(C$2:C2)+1,IF(COUNTIF(A$3:A3,A3)>1,"重複記載","")))

この式ではシート1に無い商品名についてシート1の番号に続いて番号が付けられます。
次にまとめのためのシート3については次のようにします。
A2セルに商品名、B2セルに帳簿数量、C2セルに棚卸数量、D2セルに棚卸差異と入力します。
A3セルには次の式を入力してC3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX(Sheet2!$C:$C),"",IF(ROW(A1)<=MAX(Sheet1!$C:$C),IF(COLUMN(A1)=1,INDEX(Sheet1!$A:$A,MATCH(ROW(A1),Sheet1!$C:$C,0)),IF(COLUMN(A1)=2,SUMIF(Sheet1!$A:$A,$A3,Sheet1!$B:$B),IF(COLUMN(A1)=3,SUMIF(Sheet2!$A:$A,$A3,Sheet2!$B:$B),""))),IF(ROW(A1)<=MAX(Sheet2!$C:$C),IF(COLUMN(A1)=1,INDEX(Sheet2!$A:$A,MATCH(ROW(A1),Sheet2!$C:$C,0)),IF(COLUMN(A1)=2,0,IF(COLUMN(A1)=3,SUMIF(Sheet2!$A:$A,$A3,Sheet2!$B:$B),""))))))

最後にD3セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A3="","",C3-B3)

お礼

ありがとうございます。
求めているものが出来ました。

回答No.2

tom04

こんばんは！
色々方法はあるかと思いますが・・・
一例です。

↓の画像で「在庫帳」・「棚卸表」・「棚卸照合表」それぞれを別Sheetにしています。
（Sheet名は上記の通りにしています）

棚卸表に作業用の列を1列設けます。
棚卸Sheetの作業列C2セルに
=IF(OR(A2="",COUNTIF(在庫帳!A:A,A2)),"",ROW())
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そして、棚卸照合表のA2セルに
=IF(COUNTA(在庫帳!A:A)-1+COUNT(棚卸表!C:C)<ROW(A1),"",IF(COUNTA(在庫帳!A:A)-1>=ROW(A1),在庫帳!A2,INDEX(棚卸表!A:A,SMALL(棚卸表!C:C,ROW(A1)-(COUNTA(在庫帳!A:A)-1)))))
B2セルに
=IF(A2="","",IF(COUNTIF(在庫帳!A:A,A2),VLOOKUP(A2,在庫帳!A:B,2,0),0))
C2セルに
=IF(A2="","",IF(COUNTIF(棚卸表!A:A,A2),VLOOKUP(A2,棚卸表!A:B,2,0),0))
D2セルに
=IF(A2="","",C2-B2)
という数式をそれぞれ入力し、A2～D2セルを範囲指定しD2セルのフィルハンドルで下へコピーすると画像のような感じになります。

他に良い方法があればごめんなさいね。m(__)m

お礼

ありがとうございます。
いろいろと応用してみたいと思います。