複数条件の検索と抽出

質問

複数店舗を運営しており、片方の店舗の在庫csvが以下の様になっています。

　　A　　 |　　B　 　|　　C 　　|　　D　　|　　E　 　|　　F　 　|　　G　　|
商品型番　|　不使用　|　不使用　| コードA　|　不使用　| コードB　|　 在庫　 |
００００１|　　　　　|　　　　　|　　カラー|　　　　　|ブラック　|　　　　５|
００００１|　　　　　|　　　　　|　　カラー|　　　　　|シルバー　|　　　　９|
００００８|　　　　　|　　　　　|　　カラー|　　　　　|ブラック　|　　　　１|
００００８|　　　　　|　　　　　|　　カラー|　　　　　|シルバー　|　　　　０|
０００５５|　　　　　|　　　　　|　　東芝用|　　　　　|　　　　　|　　　１５|
０００５５|　　　　　|　　　　　|　　日立用|　　　　　|　　　　　|　　　５５|

上記の在庫を別店舗の在庫csvに登録する際に、VLOOKUPを使用して以下の表に抽出したいのですが、

　　A　 　|　　B　　 |　　C 　　|　　D　　|　　E　　 |　　F　 　|　　G　　|
商品型番　|　不使用　|　不使用　| コードC　|　不使用　|　 在庫 　|　不使用　|
００００１|　　　　　|　　　　　|ブラック　|　　　　　|　　　　＊|　　　　　|
００００１|　　　　　|　　　　　|シルバー　|　　　　　|　　　　＊|　　　　　|
００００８|　　　　　|　　　　　|ブラック　|　　　　　|　　　　＊|　　　　　|
００００８|　　　　　|　　　　　|シルバー　|　　　　　|　　　　＊|　　　　　|
０００５５|　　　　　|　　　　　|東芝用　　|　　　　　|　　　　＊|　　　　　|
０００５５|　　　　　|　　　　　|日立用　　|　　　　　|　　　　＊|　　　　　|

コードCにはコードAとコードBの両方のデータが入っている
＊の箇所に在庫数を持ってきたい
不使用行は別データが入っているので削除不可

上記の場合にIF関数とVLOOKUP関数を使用して在庫数を入れる事は可能でしょうか。
いくつか調べてみたのですが、上記のような条件の場合の説明が無かった為、
もし可能であればご教授頂ければと思っています。

よろしくお願いします。

回答No.2

WindFaller · ベストアンサー

こんにちは。
バージョンが書かれていませんが、Excel 2007以上でしたら、
SUMIFS関数がよいと思います。

=IF(COUNTIF(Sheet1!$D$2:$D$7,Sheet3!D2)=0,SUMIFS(Sheet1!$G$2:$G$7,Sheet1!$A$2:$A$7,A2,Sheet1!$F$2:$F$7,D2),SUMIFS(Sheet1!$G$2:$G$7,Sheet1!$A$2:$A$7,A2,Sheet1!$D$2:$D$7,D2))

この式を、[在庫]の2行目に貼り付け、フィルハンドルコピーすればよいと思います。

>コードCにはコードAとコードBの両方のデータが入っている
今回は、元のシートの[コードA]を探して、そこになければ、元のシートの[コードB]探せということにしています。

>片方の店舗の在庫csvはダウンロードのまま修正等を行わずに使用したいと思っております。
検索元のデータを空白が、末尾に混入していたりしたら、上記の文字の検索値のD2を　[D2&"*"](カギ括弧は不要)　のようにします。

お礼

今回は、実際に関数を使用させて頂いたWindFaller様にベストアンサーをさせて頂きます。

他2名に関しても抽出方法をご記載下さり大変感謝致します。
ありがとうございました。

補足

ご回答ありがとうございます。
WindFaller様から教えて頂いた方法で在庫を移す事ができました。

ここまで教えて頂いておいて恐縮なのですが、
片方の店舗の在庫csvの商品型番、コードAかつコードBも条件として含めなければいけない
項目があったのですが、
例）
　　A　　 |　　B　 　|　　C 　　|　　D　　|　　E　 　|　　F　 　|　　G　　|
商品型番　|　不使用　|　不使用　| コードA　|　不使用　| コードB　|　 在庫　 |
００１００|　　　　　|　　　　　|　　　色Ａ|　　　　　|Ｍサイズ　|　　　　５|
００１００|　　　　　|　　　　　|　　　色Ａ|　　　　　|Ｌサイズ　|　　　　９|
００１００|　　　　　|　　　　　|　　　色Ｂ|　　　　　|Ｍサイズ　|　　　　１|
００１００|　　　　　|　　　　　|　　　色Ｂ|　　　　　|Ｌサイズ　|　　　　０|

上記のような場合に

>今回は、元のシートの[コードA]を探して、そこになければ、元のシートの[コードB]探せということにしています。

を

コードＡも条件に含めてコードＢを探す

という方法は可能でしょうか。
お手数をお掛けしますが、お分かりになるようでしたらご教授いただけると幸いです。

回答No.3

bunjii

>上記の場合にIF関数とVLOOKUP関数を使用して在庫数を入れる事は可能でしょうか。
VLOOKUP関数ではできません。
VLOOKUP関数は指定範囲の1番左側が一致した行番号の右側からデータを抽出するようになっていますのでA列の商品型番に同じ値があると目的のデータを正しく抽出できません。
一般的に商品型番はカラーやメーカー専用（東芝用、日立用等）の仕様が異なれば別の番号（符号）を付加して商品型番のみで検索できるようにされているようです。
00001　カラー　ブラック　→　00001B
00001　カラー　シルバー　→　00001S
00055　東芝用　　　　　　→　00055T
00055　日立用　　　　　　→　00055H
現状の表を変更できないときはINDEX関数とSUMPRODUCT関数関数を組み合わせて抽出します。
F2=IF(A2="","",INDEX(Sheet1!G:G,SUMPRODUCT(((Sheet1!A$2:A$1000=A2)*(Sheet1!D$2:D$1000=D2)+(Sheet1!A$2:A$1000=A2)*(Sheet1!F$2:F$1000=D2))*ROW(F$2:F$1000))))
F2セルを下へ必要数コピーします。

補足

ご回答ありがとうございます。
bunjii様からお教えいただいた方法でも在庫数を入れる事ができましたが、
先にお答えいただいたWindFaller様の式を使用させていただきました。

回答No.1

aokii

片方の店舗の在庫csvをsheet1、別店舗の在庫csvをsheet2として、

sheet1のコードAとコードBをコードCに変換（カラーをブラックやシルバーに変換）してsheet1のE列に作ってから、商品型番とコードCを結合（＝A1&E1）して、sheet1のB列に作りその後に、sheet2でも商品型番とコードCを結合（＝A1&E1）して、F列で、VLOOKUP関数=VLOOKUP(B2,Sheet2!B:G,6,FALSE)を使うことをお勧めします。

補足

ご回答ありがとうございます。
大変失礼かとは思いますが、補足コメントに記載させて頂きます。

（以下の説明では、シートではなく別ファイルから抽出するという前提にしております。）

今回このような質問をさせて頂いた理由の１つとして
私以外が在庫修正等を行う際に、店舗からcsvをダウンロードして、
関数に合わせたディレクトリにダウンロードしたcsvを入れるだけの簡単な作業にしたいという思いがありました。

aokii様のご回答では両ファイルで結合の必要がありましたが、可能であれば
片方の店舗の在庫csvはダウンロードのまま修正等を行わずに使用したいと思っております。

初回の質問とは異なってしまいますが、以下の方法では可能でしょうか。
(1)片方の店舗の在庫csvのAとDで抽出した在庫を出す列（別店舗の在庫csvのH列）
(2)片方の店舗の在庫csvのAとFで抽出した在庫を出す列（別店舗の在庫csvのI列）
の2種類の関数を使用し、
(3)別店舗の在庫csvのF列に対応した在庫数を入れていく

上記の方法が可能であればそちらで行えればと思っております。

図々しいご質問になってしまい恐縮ですが、お分かりになればご回答いただけると幸いです。