エクセルにおいて表形式の一覧から、複数条件に一致したデータを抽出する方法について

お世話になっております。

エクセルで、表形式の一覧から複数条件に一致したデータを抽出できる方法について
教えて頂きたく思います。イメージは下記の通りです。

条件は3つあり、
①一覧の商品コード　が　シートの H2 に入力される　商品コード　である
②一覧の区分　が　”販売用”　である
③一覧のステータス　が　”入荷”　である

、、、が条件となり、H6 のセルに一覧の価格を表示するものとなります。
今回の場合は商品コード1001 、区分が販売用、ステータスが入荷である価格の
￥500を表示させたいです。

②と③の条件はシート上には明示しませんが、数式の条件式で表現することを
想定しております。

Sumif関数で抽出する方法も考えましたが、15行、16行のように、
一覧表に同一のデータの行が存在するので、合計値ではなく、
複数条件が一致した、１行のみのデータを抽出したいと考えております。

お手数ですが、ご回答いただけますと有難いです。
よろしくお願いいたします。

yoshinavi

2019/03/11 04:08 編集

＞15行、16行のように、一覧表に同一のデータの行が存在する･･･　→　同一ですか？ -追記- 12行と同一と言う意味では無く、同一も存在します、との事ですね。早とちりでした。 (^^;)

行動規範の内容に同意します

回答4件

Microsoft Queryが敷居が高い、或いは仕様的に使えないのであれば、VLOOKUPでもできるような気もします。
その場合には検索キー列を別途用意して、「商品コード+区分+ステータス」を連結してしまえば、VLOOKUPでいけませんかね？

VLOOKUPを複数条件／２つの条件を検索値にする方法

投稿2019/03/10 13:33

toshi17922062

総合スコア183

somsom

2019/03/20 04:24

ご回答ありがとうございます。もし関数の組み合わせだけで価格が表示されればと思っておりましたが、もし難しいようであれば、ご指摘の通り検索キーの列を別途作成して対応することも検討いたします。ありがとうございました。

行動規範の内容に同意します

ベストアンサー

15-16行のように、複数ある場合も価格が同じなのであれば「平均」を返してあげるという手があります。

これは一般的に利用されているSUMIFとCOUNTIFを組み合わせても計算できますが、これらの仲間にAVERAGEIF/AVERAGEIFSという関数がありますのでこれを利用されるのが適当だと思います。

平均ではなく最初に見つけた項目を表示したい、などの場合には、この方法では実現できません。
同様にMAXIFやMINIF、TOPIFなどあれば便利なのですが、残念ながらそういったものは今のところないようです。

こうなると、VLOOKUP検索用に１列追加するとか、VBAで独自関数を作成するとか、他のアプローチが必要になると思います。
参考になれば幸いです。

投稿2019/03/11 03:53

編集2019/03/11 03:55

jawa

総合スコア3020

somsom

2019/03/20 04:09

ご回答ありがとうございます。Averageif関数は初めて知りました。将来的に価格が変わってしまう可能性はあるものの、当分はこの関数で解決するかと思います。遅くなってしまいましたが、ベストアンサーとさせて頂きます

jawa

2019/03/20 04:57 編集

もうひとつの手段として、「配列数式」というものを使えば先頭一致も実現可能なことに思い至りました。たとえばG6セルに `=INDEX(E3:E16,MATCH(H2&"販売用"&"入荷",B3:B16&C3:C16&D3:D16,0),1)` という数式を記述し、[Ctrl]+[Shift]+[Enter]で入力を確定してみてください。入力した数式は`{=INDEX(E3:E16,MATCH(H2&"販売用"&"入荷",B3:B16&C3:C16&D3:D16,0),1}`のように{}で括られた表示になると思います。これが配列数式です。式の内容としては、MATCH関数でB列＆C列＆D列の各行を連結した内容と、H2&"販売用"&"入荷"が一致する行を求めて、INDEX関数でその行のE列から値を取り出しています。「各行との比較」という部分が配列数式でなければ実現できないので、これを普通の数式として記述してもエラーとなってしまいますが、配列数式として記述すれば期待する値が取得できると思います。この場合、MATCH/INDEX検索を行っているので、条件に複数一致した場合は最初に見つけた行の内容が返されます。よろしければこちらもお試しください。

行動規範の内容に同意します

もし、
「同じシートのH6セルに合計値が表示されなくてもいい」、
「条件指定は複数のセルでやってもいい」
ということでしたら、私だったらワークシート関数を使うのは面倒くさいので、「Microsoft Query」という機能のパラメータクエリ機能を使います。
（データ→その他のデータソース、にて。）

以下のWebページになんとなくの例が載っています。
http://www11.plala.or.jp/koma_Excel/contents6/mame6041/mame604101.html

上記の例では、「1つの列に対して1つのセル」で条件設定していますが、質問者様の場合なら、これを「商品コード」「区分」「ステータス」の3つ分をやります。

Microsoft Queryでは、「商品コード」「区分」「ステータス」の列を「非表示の条件設定のためけの列」とみなして、「価格」の列だけを表示させて合計することもできますので・・・。（フィルタとピボットの同時利用みたいな感じ。）

あるいは、希望の行だけを全列表示させて、あとはSum関数やSumIf関数で「価格」の列をセルに合計させてももちろん良いと思います。

複数条件、複雑な条件、表と表のリレーション（Vlookupのような紐付け）、ということによって、データ抽出や同時集計などをしたい場合は、ワークシート関数だけだと非常に条件設定が面倒になってしまいます。
なので、そのような時は私はいつも、「Microsoft Query」を使っています。
（MicrosoftQueryでは「SQL」をベースに抽出や集計をしているので、SQL文が分かる人なら、なおのこと、MicrosoftQueryで抽出や集計をするほうが良いと思います。）

ちなみにですが、Microsoft Query は、Microsoft Access というソフトの「クエリ」という機能と、その画面の見え方もできること・操作方法も酷似しています（ほんとうにそっくりです。SQLが使えるところもまったく同じです。Excelとしての「方言」はありますものの。）。

なので、もし周囲にAccessの分かる人がいたら、一緒に見てもらうと早いと思います。

ただ、質問者様のやりたいことと違っていましたら、本当にすみません。

投稿2019/03/10 11:04

編集2019/03/10 11:34