忙しい現代人の腰&肩のお悩み対策!

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

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

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

A列    B列   C列
品番   在庫名  必要在庫数
1111111  東京    50
1111111  大阪    10
2222222  東京    100
2222222  大阪    20

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

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

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

このQ&Aに関連する最新のQ&A

A 回答 (5件)

●問題はデータ検索値の重複


 このような場合、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
 :

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

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

一度、お試しください。
    • good
    • 0
この回答へのお礼

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

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

お礼日時:2009/07/07 19:16

こんばんは!


参考になるかどうか分かりませんが・・・

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

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
「Excel関数」の回答画像5
    • good
    • 0
この回答へのお礼

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

お礼日時:2009/07/09 09:11

#2です。

補足しておきます。

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

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

ピボットテーブルを使ってみてはいかがですか。

…と無責任に言ってみます。
覚えると結構便利な機能ですので試しにやってみてはいかがでしょう。
    • good
    • 0
この回答へのお礼

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

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

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

お礼日時:2009/07/07 19:35

マスターと品番が1桁違いますので


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

この回答への補足

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

補足日時:2009/07/07 16:28
    • good
    • 0
この回答へのお礼

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

お礼日時:2009/07/07 16:26

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qこの場合のEXCELの計算式 ありますか?

EXCELでの計算式で、
「=SUMIF(A1:A5),">=0"」は、
エラーにならないのですが、
「=SUMIF(A1,A3,A7),>=0"」は、
エラーになってしまいます。
範囲が横に連なっていれば出来るのですが、
[Ctrl+セルを複数選択]とすると、
出来ません・・・

このような場合、適用できる計算式はありますでしょうか?
アドバイスをお願いいたします!

Aベストアンサー

こんばんは!
No.1さんのお礼の欄を読ませてもらい、回答させてもらいます。

今回の場合、事業所名は関係ないのでしょうか?
単に要員がプラスの場合とマイナスの場合のみの合計をすれば良いと言うことでの回答になりますが・・・

まずSUMUIF関数はセルが連続していないと使えないと思います。
そこで↓の画像のようにプラスの場合とマイナスの場合を分けて
それぞれのセルに表示させるようにするのはどうでしょうか?

以上、参考になれば幸いですが、
的外れなら読み流してくださいね。m(__)m


このカテゴリの人気Q&Aランキング