Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7全般 : Excel 2010)
入力規則のドロップダウン 条件での絞り込み
投稿日時: 18/04/12 14:59:16
投稿者: あずさ

お世話になります。
  
Sheet1 (メインで使うシート)
  
    A       B
 野菜     オクラ
 肉      あいびき
  
   
Sheet2(補助シート)
  
 A   ・・・・・・     D     E
野菜           あいびき  肉
 肉           いちご   果物
 果物          イワシ   魚
 魚           うなぎ   魚
             エンドウ  野菜
             オクラ   野菜
             柿     果物
  
上記のようなブックがあります。
  
Sheet1のA列のセルには 入力規則 - リスト で『元の値』に =Sheet2!$A$2:$A$99 を設定しています。
Sheet1のB列では、A列で選択した項目に対応した、Sheet2のD列の値を選択できるように
 ドロップダウンの絞り込みを行いたいのですが、可能でしょうか?
たとえば、Sheet1のA2セルで『果物』を選択したら、B2セルのドロップダウンに表示されるのは 『いちご,柿』です。
  
・Sheet2のD,Eを別シートにすることは可能です。
・Sheet2のD,Eの列の入れ替えも可能です。
・可能でしたら『名前定義』を使用しないで実現したいです。
  
実現可能な方法はありますでしょうか?
よろしくお願いします。

回答
投稿日時: 18/04/12 15:27:44
投稿者: んなっと

 Sheet2でE列昇順に並べ替えておいて
 
   A B C     D   E
1              
2 野菜     いちご 果物
3  肉       柿 果物
4 果物     イワシ  魚
5  魚     うなぎ  魚
6       あいびき  肉
7       エンドウ 野菜
8        オクラ 野菜
 
→Sheet1でB2を選択
→データの入力規則
→入力値の種類:リスト
 元の値:
=OFFSET(Sheet2!$D$1,MATCH(A2,Sheet2!$E:$E,0)-1,,COUNTIF(Sheet2!$E:$E,A2))

投稿日時: 18/04/12 16:26:19
投稿者: あずさ

んなっと様
 
ありがとうございます!!
実現できました。
 
後追加で大変申し訳ないのですが、
Sheet2のD,Eのソートなしでの方法はありますでしょうか?
D列が、キー項目になっているのでできればE基準ソートなしでと考えています。
 
申し訳ありません。アイデアありましたらお願いします。

回答
投稿日時: 18/04/12 16:47:53
投稿者: んなっと

●Sheet2
 
      D   E   F G     H   I
 1
 2 あいびき  肉   1 1 あいびき  肉
 3  いちご 果物   2 2  いちご 果物
 4  イワシ  魚   3 5    柿 果物
 5  うなぎ  魚 3.001 6  りんご 果物
 6 エンドウ 野菜   4 7  イワシ  魚
 7  オクラ 野菜 4.001 8  うなぎ  魚
 8    柿 果物 2.001 3 エンドウ 野菜
 9  レタス 野菜 4.002 9  オクラ 野菜
10  りんご 果物 2.002 4  レタス 野菜
 
F2
=IFERROR(LOOKUP(1,0/(E$1:E1=E2),F$1:F1)+0.001,INT(MAX(F$1:F1))+1)
下方向・↓
G2
=RANK(F2,F:F,1)
下方向・↓
H2
=INDEX(D:D,MATCH(ROW(1:1),$G:$G,0))
右方向・→下方向・↓
 
●Sheet1
 B2 入力規則 リスト 元の値:
=OFFSET(Sheet2!$H$1,MATCH(A2,Sheet2!$I:$I,0)-1,,COUNTIF(Sheet2!$I:$I,A2))

回答
投稿日時: 18/04/12 17:09:16
投稿者: んなっと

D,E列が多すぎるときは関数を使わない別の方法で並べ替えを行いますが、
手順説明が複雑なので省略します。

投稿日時: 18/04/13 15:38:01
投稿者: あずさ

んなっと様
 
お礼が遅くなりましてすみません。
 
不特定多数の人間がアタッチするブックということもあり、
結果的には隠しシートに並べ替えを行ったD,Eを用意して
第一案でいくことになりました。
 
ありがとうございました。