Excelについて：複数の条件からデータを参照し、条件に合致した値を自動で表示する方法

初めて投稿させて頂きます。閲覧ありがとうございます。
複数の条件からデータを参照し、条件に合致した値を自動で表示する方法を探しております。
拙いですが、画像を参照ください。

例：営業、週4日を選択すると、条件に合った250,000が基本給へ表示される
　　※条件に当てはまらない場合は0が表示される

「部署」「勤務条件」を、作成したプルダウンから選択し、その選択条件から別シートに作成しておいたデータ一覧を参照、「基本給」(B6のセル）へ自動で条件に合った値を表示する上で、DSUMやVLOOKUPが近しいかと思い、とりあえずはDSUMを使用して画像のような形（1番上、上から2段目の画像）で表示させることができました。

しかし、実際のデータ一覧は画像よりも膨大になるため、DSUM用のデータですと「営業」の項目や「週3日」といった勤務条件を何度も入れることになり、縦長になってしまいます。一覧の修正が必要になった際、かなりスクロールしないと探せないほどになります。
そのため、参照先のデータを一番下の画像のようにもっとすっきりとした表にしたいと考えていますが、この場合DSUMが使用できなくなってしまうと思うので、悩んでおります。

Excelは簡単な四則演算程度の知識しかないため、うまく説明できているかも分かりませんが、
もっと簡略化できる方法をご回答頂けましたら幸いです。

質問者からの補足コメント

• すみません。慌てて例用の画像を作成したため、質問文と画像の内容が一致しておりませんでした。
  真ん中の画像についてC1は売上ではなく基本給で、C2～C5の基本給の内容は最下部の表にある100,000～300,000なります。

    補足日時：2018/06/28 00:26

No.1 ベストアンサー 回答者： fujillin 回答日時： 2018/06/28 10:11

こんにちは

余分な情報がいろいろあるみたいですが、単純化すると
　「部署、勤務日数の2軸の表から該当する値を検索したい」
ということと理解しました。

方法としては、それぞれの軸で該当する位置をMATCH関数で検索して、INDEX関数で表の該当位置の値をとれば良さそうです。（ぐぐってみると、たくさん解説サイトがみつかると思います）

多くに紹介されている方法をそのままあげても良いのですが、検索すればいくらでも詳細な解説があると思いますので、もう少しだけ便利にした方法をご紹介しておきます。（たいして変わりませんけれど…）

添付図の左側が「基本給一覧表」のつもりです。（金額はA、B、Cにしてあります）
ご質問文ではシート名等が不明ですが、この一覧表に「名前の定義」で名前をつけておきます。
つけなくても良いのですが、後々項目数が変わったりした時にも便利です。
添付の例では、＄B$3:＄E$8の範囲で「基本給一覧」の名前を付けてあります。

右側のシートが、検索側のシートです。
A4セル、B4セルは入力規則を利用していますが、先ほどの基本給一覧の表を参照するようにします。
入力規則のリストを選択し範囲の欄（「元の値」のところ）に
A4セルでは、=OFFSET(基本給一覧,0,0,,1)
B4セルでは、=OFFSET(基本給一覧,0,0,1)
の関数式を設定します。

このようにすることで、基本給一覧の項目範囲が変わったりした場合でも、同じ形式である限りは、リストの内容が自動的に連動してくれるようになります。

さて、ご質問のB6セルですが
=INDEX(基本給一覧,MATCH(A4,OFFSET(基本給一覧,0,0,,1),0),MATCH(B4,OFFSET(基本給一覧,0,0,1),0))
の関数式を入力してあります。
この式も「基本給一覧」の表のサイズが変わっても式を変更しなくて良いようになっています。
ですので、将来的に「基本給一覧」の名前の定義内容と表の範囲が一致するように注意してさえいれば、変更があっても関数式その他は修正しなくても成立する点が便利ではないかと思います。

この回答へのお礼

fujillin様

蛇足の多い質問文となり失礼いたしました。ご回答ありがとうございます。
＞「部署、勤務日数の2軸の表から該当する値を検索したい」
まさにその通りです。
INDEX、MATCH関数等を使用したことが無かったためそれぞれの意味を調べつつ、ご教示頂いた関数式を組み込んでみたところ、無事に表示することができました。
基本給一覧の編集についても、定義した範囲外となる部分に項目を追加（オートフィルなど隣接したセルにコピーを行う等）しても反映されないとのことで、データの修正の際には注意したいと思います。

使用していく中で、いずれ発生するであろう注意点も交えてひとつひとつ丁寧にご説明下さったこと、既存の手法＋ご自身の考える利便性を加えてご回答頂いたことから、ベストアンサーとさせて頂きます。

これからのExcel活用の為にも大変勉強になりました。重ねて御礼申し上げます。

お礼日時：2018/06/29 15:26

No.3 回答者： tom04 回答日時： 2018/06/28 11:08

こんにちは！

要は部署と勤務形態によって基本給の表示をしたい！というコトでしょうかね？

やり方だけ、一例です。
↓の画像のように対応表を作成しておきます。

B4セルに
=IFERROR(VLOOKUP(A$2,D:G,MATCH(B$2,D$1:G$1,0),0),"")

という数式を入れています。m(_ _)m

この回答へのお礼

tom04様

こんにちは、初めまして。
＞要は部署と勤務形態によって基本給の表示をしたい！というコトでしょうかね？

その通りです。本来シンプルな質問のはずなのに回りくどい文章となってしまい申し訳ございません。
ご提示いただいた関数式について、自分の中で理解しながら表を試作してみたところ、ご提示頂いた方法で結果を表示させることが出来ました。色々な関数を知っていると様々なアプローチの仕方を考えることができるのですね。
関数のネストというのがどうにも慣れないため、tom04様のご回答のようなすっきりした式を作れるようにこれからもチャレンジしてみたいと思います。

この度はご回答いただきありがとうございました。

お礼日時：2018/06/29 16:56

No.2 回答者： sorun2 回答日時： 2018/06/28 10:19

>※条件に当てはまらない場合は0が表示

>部署」「勤務条件」を、作成したプルダウンから選択
で入力していれば<
条件に当てはまらない場合は発生しません。

下記は、基本給の式の例です。
=IF($A$2="営業",VLOOKUP(B2,$A$7:$C$9,3),IF($A$2="事務",VLOOKUP(B2,$A$7:$C$9,2),0))

この回答へのお礼

sorun2様

ご回答いただきありがとうございます。
DSUMを試したあとVLOOKUPを自分で入力してみたのですが、うまくいかなかったため、 sorun2様のご回答を見ながら自分の入力の誤り等に気付くことができました。
IFはそれ単体の式で使用する程度でしたので、ご提示頂いた関数式のように他関数とうまく合わせて使用する方法もこれから勉強していきたいと思います。

お礼日時：2018/06/29 15:36