Excel 複数条件からのデータ抽出

質問

Excel　複数条件からのデータ抽出

Excelの関数について質問です。
色々と調べたのですが、近いようで遠いものしか見つけることができませんでしたので、
我慢できず質問させていただきます。

下記のようなデータがあったとします。
-----------------------------
ホテル

ホテル1　プラン1　部屋1　価格
ホテル1　プラン2　部屋2　価格
ホテル1　プラン3　部屋3　価格
ホテル1　プラン3　部屋4　価格
ホテル2　プラン1　部屋5　価格
ホテル2　プラン2　部屋6　価格
ホテル2　プラン2　部屋7　価格
ホテル2　プラン2　部屋8　価格
・
・
・
-----------------------------
このデータから、下記の処理がしたいです。

・オートフィルタでソートして絞るのではなく、
　別セルに入力規制などで、プルダウンから選ぶ方式で、
　各プランの比較を行いたい。

(1)まずは、別シートのA1セルに、
入力規制を設け、ホテルを選択できるようにします。

(2)次に、A2セルでプランを同じ方法で選択するようにしたいのですが、
上記のデータでホテル1にはプラン1～3が該当しますが、
ホテル2はプラン1と2のみになってます。
このとき、(1)でホテルを選択した時点で、
自動でプラン選択リストをそのホテルに合わせたものにしたいです。

(3)次に、A3セルで同じように部屋を選択するのですが、(2)と同じように、
(1)と(2)で選んだホテル・プラン向けのリストに自動的になるようにしたいです。

(4)最後に、A4セルに該当の価格を自動で表示させたいです。

可能でしょうか。
実際はホテルの数が全部で5つ、
プランの数が5つ、
部屋のタイプは26種類、
価格はすべて異なります。

よろしくお願いいたします。

回答No.4

kagakusuki · ベストアンサー

回答番号:ANo.3の続きです。

　次に、A1セルの場合と同様の操作で、A2セルの入力規則に次の数式を設定して下さい。

=INDIRECT("Sheet3!H2:H"&COUNTIF(INDIRECT("Sheet3!H:H"),"><"))

　次に、同様の操作でA3セルの入力規則に次の数式を設定して下さい。

=INDIRECT("Sheet3!I2:I"&COUNTIF(INDIRECT("Sheet3!I:I"),"><"))

　最後に、Sheet1のA4セルに次の数式を入力して下さい。（入力規則ではありません）

=SUMPRODUCT((OFFSET(Sheet2!$B$1,1,0,COUNTIF(Sheet2!$B:$B,"><"))=Sheet1!$A$1)*(OFFSET(Sheet2!$C$1,1,0,COUNTIF(Sheet2!$B:$B,"><"))=Sheet1!$A$2)*(OFFSET(Sheet2!$D$1,1,0,COUNTIF(Sheet2!$B:$B,"><"))=Sheet1!$A$3)*OFFSET(Sheet2!$E$1,1,0,COUNTIF(Sheet2!$B:$B,"><")))

　それから、入力規則で新たな入力に制限を加えたとしても、既にSheet1のA2セルやA3セルにプランや部屋を入力済みの場合、A1セルに別のホテルを入力すると、そのホテルには存在しないプランや部屋が入力されている状態になります。
　これでは紛らわしいため、条件付き書式を応用して、ホテル名とプラン名や部屋番号の間に、整合性がない場合には、A2セルやA3セルの表示が消えた様に見せかけます。

Sheet1のA2セルを選択
　　↓
メニューの[書式]ボタンをクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ウィンドウの中の左端にある欄をクリックして、現れた選択肢の内の「数式が」をクリック
　　↓
「条件付き書式の設定」ウィンドウの中の右側にある欄をクリックして次の様な数式を入力

=COUNTIF(INDIRECT("Sheet3!H:H"),A2)=0

　　↓
「条件付き書式の設定」ウィンドウの中の[書式]ボタンをクリック
　　↓
現れた「セルの書式の設定」ウィンドウの「フォント」タグをクリック
　　↓
「色」欄をクリック
　　↓
白色をクリック
　　↓
「セルの書式の設定」ウィンドウの中の[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ウィンドウの中の[OK]ボタンをクリック

　同様の操作で、Sheet1のA3セルにも、条件付き書式に次の数式を設定して下さい。

=COUNTIF(INDIRECT("Sheet3!I:I"),A3)=0

　以上で完成です。

回答No.5

nattocurry

> >部屋と価格の対応表
> こちら、同じ部屋の名前で、価格が異なるものがあるのですが、

あなたの例では、部屋名はすべて異なっていたので、このような回答をしました。

回答No.3

kagakusuki

今仮に、Sheet2の
B1セルに「ホテル名」、
C1セルに「プラン名」、
D1セルに「部屋番号」、
E1セルに「価格」、
という具合に項目名が並んでいて（B1、C1、D1は空欄にはしないで下さい）、Sheet2の2行目から下方に向かって、
B列に各ホテルの名称データ、
C列に各プランの名称データ、
D列に各部屋の番号のデータ、
E列に価格のデータ、
という具合に各データが入力されていて、
ドロップダウンリストでSheet1の
A1セルにホテル名、
A2セルにプラン名、
A3セルに部屋番号を入力すると、
Sheet1のA4セルに価格が自動的に表示されるものとします。

　ここでは、Sheet3のB列、C列、D列、G列、H列、I列を作業列として使用する方法を述べます。
　又、この方法では、Sheet2に入力されているデータは、ホテル毎にまとまって入力されていなくても良く、（例えば上から、「ホテル2のプラン3」、「ホテル5のプラン7」、「ホテル3のプラン5」、「ホテル1のプラン1」、「ホテル4のプラン6」、「ホテル2のプラン2」の順番でも可）
異なるホテルに、同名のプランや部屋番号が存在していても、正しい価格が表示されます。

　まず、Sheet2の
B1セルとG1セルに「ホテル」、
C1セルとH1セルに「プラン」、
D1セルとI1セルに「部屋」、
と入力して下さい。（G1、H1、I1は空欄にはしないで下さい）
　次に、B2セルに次の数式を入力して下さい。

=IF(Sheet2!$B2="","",IF(COUNTIF(Sheet2!$B1:$B2,Sheet2!$B2)=1,COUNT($B$1:$B1)+1,""))

　次に、C2セルに次の数式を入力して下さい。

=IF(AND(Sheet2!$B2=Sheet1!$A$1,SUMPRODUCT((Sheet2!$B$1:$B1&Sheet2!$C$1:$C1=Sheet1!$A$1&Sheet2!$C2)*1)=0),COUNT(C$1:C1)+1,"")

　次に、D2セルに次の数式を入力して下さい。

=IF(AND(Sheet2!$B2=Sheet1!$A$1,Sheet2!$C2=Sheet1!$A$2,SUMPRODUCT((Sheet2!$B$1:$B1&Sheet2!$C$1:$C1&Sheet2!$D$1:$D1=Sheet1!$A$1&Sheet1!$A$2&Sheet2!$D2)*1)=0),COUNT(D$1:D1)+1,"")

　そして、Sheet3のB2～D2の範囲をコピーして、同じ列の3行目以下に、Sheet2のリストの行数を上回るのに充分な回数だけ貼り付けて下さい。
　それから、Sheet3のG2セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT($B:$B),"",INDEX(Sheet2!$B:$B,MATCH(ROWS($1:1),$B:$B)))

　次に、H2セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT($C:$C),"",INDEX(Sheet2!$C:$C,MATCH(ROWS($1:1),$C:$C)))

　次に、I2セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT($D:$D),"",INDEX(Sheet2!$D:$D,MATCH(ROWS($1:1),$D:$D)))

　そして、Sheet3のG2～I2の範囲をコピーして、同じ列の3行目以下に、最も部屋数の多いプランにおける部屋数を、上回るのに充分な回数だけ貼り付けて下さい。

　次に、Sheet1のA1セルを選択してから、次の操作を行って下さい。

メニューの[データ]をクリック
　　↓
選択肢の[入力規則]をクリック
　　↓
現れた「データの入力規則」ウィンドウの「設定」タグをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中の「リスト」をクリック
　　↓
現れた「元の値」欄の中に以下の数式を入力

=INDIRECT("Sheet3!G2:G"&COUNTIF(INDIRECT("Sheet3!G:G"),"><"))

　　↓
「データの入力規則」ウィンドウの[OK]ボタンをクリック

※回答欄に入力可能な文字数を超えたため、少々規則違反になりますが、後半は次の回答に掲載させて頂きます。

回答No.2

tom04

こんばんは！
入力規則のリスト表示を3段階で行いたい！ということですよね？
一応そういうことだとして・・・
小さくて見にくいかもしれませんが、↓の画像で説明します。
一例です。

右側のSheet2にリスト表示する表を作成しています。
この表さえきっちりできればあとは簡単だと思います。

2段階までなら簡単ですが、今回のように3段階となると表を作成するのも工夫が必要だと思います。
結局最後の部屋名によって値段が変わってくるのでしょうから、すべての部屋の価格を表示しておく必要があると思います。

Sheet2のA1～C5がSheet1のB1セルの入力規則に利用するための表です。
当方使用のExcel2003の場合ですが
Sheet2のA1～A4セルを範囲指定　→　メニュー　→　挿入　→　名前　→　作成　→　上端行　を選択し、OK
これでA2～A4セルが「ホテル1」と名前定義されます。
同様に黄色部分（ホテル1～ホテル3）を名前定義

同様にA7～D７セルを範囲指定　→　（中略）→　作成　→　「左端列」を選択　→　OK
これでB7～D7セルが「ホテル1プラン1」と名前定義されます。
これをすべての行で名前定義しておきます。

そして、F列～I列にすべての部屋名の価格を「ホテル名」「プラン名」「部屋名」とともに、全てを入力します。

これで準備完了です。

Sheet1のB1セルをアクティブにして　メニュー　→　データ　→　入力規則　→　リスト　→　「元の値」の欄に
=ホテル
としてOK

B2セルも同様に入力規則のリストから「元の値」の欄に
=INDIRECT(B1)
としてOK

B3セルも入力規則のリスト　→　「元の値」の欄に
=INDIRECT(B1&B2)
としてOK

これでB1　→　B2　→　B3　とリストで選択したものが徐々に絞られてリスト候補になるはずです。

最後に画像では見えていませんが、B4セルに
=IF(COUNTBLANK(B1:B3),"",SUMPRODUCT((Sheet2!F2:F100=B1)*(Sheet2!G2:G100=B2)*(Sheet2!H2:H100=B3)*(Sheet2!I2:I100)))
（F～I列の100行目まで対応できるようにしています）

という数式を入れ、完了です。
これでB1～B3セルを選択すればSheet2のI列の価格が表示されると思います。

この程度しか思いつきませんが、Sheet2の表の作成方法を工夫すればもう少し簡単になるかもしれません。

どうも長々と失礼しました。m(__)m

回答No.1

nattocurry

あなたがやりたいことを実現させるためには、例に挙げたデータであれば、

ホテル1のときのプランのリスト
ホテル2のときのプランのリスト
ホテル1でプラン1のときの部屋のリスト
ホテル1でプラン2のときの部屋のリスト
ホテル1でプラン3のときの部屋のリスト
ホテル2でプラン1のときの部屋のリスト
ホテル2でプラン2のときの部屋のリスト
部屋と価格の対応表

というものをあらかじめ作って、リストに名前を付けておく必要があります。
その上で、INDIRECT関数を使って、入力規則を利用します。

あらかじめリストを作って名前を付ける、という作業を手作業でおこなうかVBAマクロでおこなうか、の選択肢はありますが。

このようなリストをあらかじめ作らずに、例に挙げたデータから直接、重複の無い選択リストにするのは、無理だと思います。
私はExcelの機能をすべて把握していないので、絶対に無理だとは断言できませんが。

私なら、素直にオートフィルタ機能を利用しますね。
オートフィルタを使えば、絞っていく途中で価格を見ながら選択肢を選ぶこともできますし。

なぜ、オートフィルタを使いたくないのでしょうか？

補足

ありがとうございます。

あまりExcelが詳しくないため、疑問に思ったのですが、

>部屋と価格の対応表
こちら、同じ部屋の名前で、価格が異なるものがあるのですが、
(実際にはホテルもプランも違う)
このまま作成しても意味を成すものでしょうか。。

オートフィルタを使いたくない理由といたしましては、
具体的に言いますと、今回「プラン」で旅行にいくか、ただの「宿泊費にオプションを追加」して行くかで悩んでいて、それをA列とB列でそれぞれ比較しながら選択を進めていきたいのです。。

他に良い方法があれば、、是非ご紹介いただけますと幸いです。