範囲と範囲を比べて該当するものを抽出

質問

エクセルでこういうことができるのかどうかわからないですが・・・。

図のように、A・B列に取引先と金額を書き込んでいきます。
一方、G列に上得意様のリストがあり、A・B列に上得意様の取引が書き込まれたら、それだけD・E列に書き出すようにしたいのです。

VLOOKUP関数でできるかな？と思いましたが、検査値がひとつでなく、複数の項目のあるリストなのでうまくいきませんでした。
このような場合は、どんな関数を使えばよいでしょうか？どうぞ、ご教授よろしくお願いします。

回答No.3

MackyNo1 · ベストアンサー

No1の回答に対する補足が無いようなので、数式入力セルが多くなるなどの条件によっては、シートの動きが重くなるなどの問題も考えられますが、ひとまずどちらのパターン（上得意様が重複する場合）にも対応できる数式を提示します。

以下の数式を入力して、Ctrl+Shift+Enterで確定して（数式が「｛」と「｝」で囲まれる）、右方向に１つ、下方向に適当数オートフィルしてください。

=IFERROR(INDEX(A:A,SMALL(IF(COUNTIF($G$2:$G$9,$A$2:$A$1000),ROW($A$2:$A$1000),""),ROW(A1))),"")

＃上記の数式はExcel2007以降のバージョンをご使用しているものとして、回答しています。
Officeソフトはバージョンによって使用できる機能や操作方法が大きく異なりますので、質問の際には必ずバージョンを明記するようにしましょう。

お礼

何度もありがとうございます。
エクセルは2010なので大丈夫です。（今後はバージョンを明記します。）
不明な部分を想定してまで式を作っていただいて、とても感謝します。
出勤したらさっそく試させていただきますね。

回答No.2

kagakusuki

今仮に、A列にNo.、B列に取引相手の社名、C列に取引金額、I列に上得意様の社名が入力されていて(１行目は「社名」、「金額」等の項目名が入力されています)、E列～G列に上得意様の取引のみを抽出するものとします。
　尚、K列を作業列として使用するものとします。

　まず、K2セルに次の関数を入力して下さい。

=IF(AND(COUNTIF($I:$I,INDEX($B:$B,ROW())),INDEX($B:$B,ROW())<>""),ROW(),"")

　次に、K2セルをコピーして、K3以下に貼り付けて下さい。
　次に、E2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT($K:$K),"",IF(INDEX($A:$C,SMALL($K:$K,ROWS($2:2)),COLUMNS($E:E))="","",INDEX($A:$C,SMALL($K:$K,ROWS($2:2)),COLUMNS($E:E))))

　次に、E2セルをコピーして、F2～G2の範囲に貼り付けて下さい。
　次に、E2～G2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上です。

お礼

ありがとうございます。なんと！こんなに難しい式になるのですね・・・
自分のスキルではまったく内容がわからないのですが、とりあえずセルの部分に代入して試してみます。

回答No.1

MackyNo1

例示のデータでは「山田工業」が複数あるのですが、上得意様のデータは１つしかないのでしょうか？
その場合は、名前さえ特定できれば、単純なVLOOKUP関数で対応できますが、この上得意様の名前を表示したいということでしょうか？

お礼

ありがとうございます。コメントが遅くなりました。
たしかにこれだけ山田工業と取引していれば、山田工業が一番の上得意様になりますよね（汗）
少し例のあげ方が変でしたね・・・
単に、G列のリストがあり、その中にある項目に該当するものが書き込まれれば抽出という意味でした。（山田工業は“その他大勢”という意味でした）
No.3で提示していただいた関数を試してみます。ありがとうございました。