Excel関数

質問

Excelで適切な関数を探しています。
在庫の管理で複数ある在庫の指定した商品の過不足がわかるような在庫表を作成しようとしてます。
会社の基幹システムからExcelに落とした在庫表が下記のようにあります。

Ａ列　　　　Ｂ列　　　　　Ｃ列　　　　
品番　　　　倉庫名　　保有在庫数
111111　　　東京　　　　　10
111111　　　大阪　　　　　20
222222　　　東京　　　　　50
333333　　　大阪　　　　　10
333333　　　札幌　　　　　10
444444　　　札幌　　　　　30
続く・・・

他に各倉庫各品番の必要在庫の別シートのマスターがあります。

Ａ列　　　　Ｂ列　　　Ｃ列
品番　　　在庫名　　必要在庫数
1111111　　東京　　　　50
1111111　　大阪　　　　10
2222222　　東京　　　　100
2222222　　大阪　　　　20

最初の在庫表にD列を設け、そこに上記のマスターの必要在庫数を表示させて下記のようにしたいのですが・・・

Ａ列　　　　Ｂ列　　　　　Ｃ列　　　　Ｄ列　　　　Ｅ
品番　　　　倉庫名　　保有在庫数　　必要在庫数　過不足数
111111　　　東京　　　　　10　　　　　　50　　　　-40
111111　　　大阪　　　　　20　　　　　　10　　　　10
222222　　　東京　　　　　50　　　　　　100　　　-50
・・・・・
がIFやVLOOKUPの関数で組み合わせで適切なものができませんでした。

他の関数で適切なものはありますでしょうか。

回答No.2

shinkun0114 · ベストアンサー

●問題はデータ検索値の重複
　このような場合、vlookup関数を用いてデータテーブルから目的のデータを探す方法が基本です。
このとき、データテーブルの検索値、この場合は品番が重複していないことが条件になります。
しかし、ご質問の事例を見ますと、品番111111が東京と大阪で二つのデータに分かれていますので、111111だけで検索することができません。

●検索値を固有の値にする
　そこで、検索値が重複しない固有の値を持つようにしてみましょう。
　具体的には、品番と倉庫名を結合し、これを固有の検索値とすればよいのです。C列の前に1列挿入し、
　　=A1&B1
　を入力して、下にコピーします。在庫表は以下のようになります。

　A　　　B　　　　　C　　　　　　D
111111 東京　111111東京　10
111111 大阪　111111大阪　20
222222 東京　222222東京　50
333333 大阪　333333大阪　10
333333 札幌　333333札幌　10
444444 札幌　444444札幌　30
　：

　同様に必要在庫のシートも加工してください。
Ａ列　　　　Ｂ列　　　　　C列　　　　D列
品番　　　在庫名　　検索コード　必要在庫数
111111　　東京　　111111東京　　50
111111　　大阪　　111111大阪　　10
　：

この状態にして在庫表のE列にVLOOKUPを使います。検索値はC列です。
また、範囲設定も必要在庫のC列が左端になるように範囲を設定します。

一度、お試しください。

お礼

目からうろこが落ちました！！
検索値をまとめれば確かにVLOOKUPで出来ますね！

問題が解決しました。
ありがとうございます！

回答No.5

tom04

こんばんは！
参考になるかどうか分かりませんが・・・

↓の画像のように表を作ってみました。

VLOOKUP関数では一つのセルしか参照できないと思います（多分・・・）ので

Sheet1・Sheet2とも作業列を使わせてもらっています。

Sheet1のG2セル（F2セルでも構いません）に
=A2&B2　としてオートフィルで下へコピーします。
同じく、Sheet2のD2セルも　=A2&B2　としてオートフィルで下へコピー

これらの列を参照してSheet1のD列に必要在庫数を表示させるようにします。

Sheet1のD2セル
=INDEX(Sheet2!$C$2:$C$8,MATCH(G2,Sheet2!$D$2:$D$8,0))
E2セル=C2-D2　
として、D2・E2セルを範囲指定した後にオートフィルで下へコピーします。
（エラー処理はしていません）

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

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

お礼

ご返答ありがとうございます。
画像まで添付いただいて非常にわかりやすかったです。
INDEXとMACTH関数でも目的の数値を返せました。
私はVLOOKUP関数の方がなじみがありましたが、こちらの関数も応用が利きそうですね。
非常に参考になりました。

回答No.4

shinkun0114

#2です。補足しておきます。

●TRUE指定
　この方法でVLOOKUP検索を行なう場合、検索の型としてTRUEを指定しておいてください。
　TRUEではなくFALSE指定にしておくと、昇順に並んだデータでないと正しい検索ができません。東京・大阪など文字列を含んだ検索値では、思った通りの昇順になっていないことがあります。
　必ずTRUE指定をしてください。

●エラートラップ
　VLOOKUPでTRUE指定をする場合、検索値がないとエラーになります。
　在庫表にはあるけれど、必要在庫ない品番・倉庫名があると、エラーが出てしまいますので、ISERRORなどを用いてエラー回避をする必要があります。

回答No.3

noname#180098

ピボットテーブルを使ってみてはいかがですか。…と無責任に言ってみます。
覚えると結構便利な機能ですので試しにやってみてはいかがでしょう。

お礼

ご回答ありがとうございます。

ピボットテーブルでもできそうですが、在庫表は毎日メンテする予定のため、できれば関数を使って工程を減らしたいと考えてました。

参考にさせて頂きます。
ありがとうございました。

回答No.1

wisemac21

マスターと品番が1桁違いますので
VLOOKUPの検索値に1桁追加して
D2=VLOOKUP(A2&RIGHT(A2,1),マスター!A2:C5,3,FALSE)
こんなのではどうですか

お礼

ご返答ありがとうございます。
大変申し訳ありませんが品番の桁違いはタイプミスでした。
シンプルなVLOOKUP（D2=VLOOKUP(A2,マスター!A2:C5,3,FALSE)
だとマスターのA列とB列両方に合致した必要在庫数が導き出せないためそこがネックになってます。。。。

補足

大変申し訳ありません。
品番の桁数は在庫表もマスターも同じ桁数でした。
タイプミスです。