• ベストアンサー

Excel関数

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の関数で組み合わせで適切なものができませんでした。 他の関数で適切なものはありますでしょうか。 

質問者が選んだベストアンサー

  • ベストアンサー
回答No.2

●問題はデータ検索値の重複  このような場合、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列が左端になるように範囲を設定します。 一度、お試しください。

moon1973
質問者

お礼

目からうろこが落ちました!! 検索値をまとめれば確かにVLOOKUPで出来ますね! 問題が解決しました。 ありがとうございます!

その他の回答 (4)

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.5

こんばんは! 参考になるかどうか分かりませんが・・・ ↓の画像のように表を作ってみました。 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

moon1973
質問者

お礼

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

回答No.4

#2です。補足しておきます。 ●TRUE指定  この方法でVLOOKUP検索を行なう場合、検索の型としてTRUEを指定しておいてください。  TRUEではなくFALSE指定にしておくと、昇順に並んだデータでないと正しい検索ができません。東京・大阪など文字列を含んだ検索値では、思った通りの昇順になっていないことがあります。  必ずTRUE指定をしてください。 ●エラートラップ  VLOOKUPでTRUE指定をする場合、検索値がないとエラーになります。  在庫表にはあるけれど、必要在庫ない品番・倉庫名があると、エラーが出てしまいますので、ISERRORなどを用いてエラー回避をする必要があります。

noname#180098
noname#180098
回答No.3

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

moon1973
質問者

お礼

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

  • wisemac21
  • ベストアンサー率39% (171/429)
回答No.1

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

moon1973
質問者

お礼

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

moon1973
質問者

補足

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

関連するQ&A

専門家に質問してみよう