Excel (一般機能)

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

 
(Windows 10 Pro : Excel 2010)
入力規則のリスト
投稿日時: 19/01/08 15:58:01
投稿者: quma10

入力規則のリストで絞り込みをかけたいです。
 
【A列】    【B列】
 名称     表示
Aコード     ○
Bコード     ×
Cコード     ○
 
のようなシートがありリストボックスにA行を表示させたいのですが
表示の行に×が入力されている場合は
リストに表示をさせたくないです。
(この場合はリストに表示させたいのはAコードとCコード)
表示の行の○、×はしょっちゅう入れ替わります。
○がついている名称のみリストボックスに表示させるにはどうすればよいのでしょうか・・・
 

回答
投稿日時: 19/01/08 16:51:00
投稿者: コナミ

B列を基準にして◯が上に来るように並べ替えできるなら、
 
入力規則→リストで
 
=OFFSET($A$2,,,COUNTIF($B$2:$B$4,"◯"),1)
 
などのように入れれば◯のついているA列だけを表示することができます。

回答
投稿日時: 19/01/08 17:28:08
投稿者: Mike

   A   B   C        A   B   C 
1 名称  表示 名称2     1 名称  表示 名称2
2 Aコード ○  Aコード    2 Aコード ○  Aコード
3 Bコード ○  Bコード    3 Bコード ×  Cコード
4 Cコード ○  Cコード    4 Cコード ○
 
B列の全セルを○表示にしていた場合、
次式を入力したセル C2 を下方にオートフィルすると、上図左のように表示されます。=IFERROR(INDEX(A$1:A$4,SMALL(IF(B$2:B$4="○",ROW(B$2:B$4)),ROW(A1))),"")
【お断り】上式は必ず配列数式として入力のこと
 
[データの入力規則]メニューの[設定]で
[入力値の種類]を“リスト”として、[元の値]ボックスに =$C$2:$C$4 を入力すると、当該設定を施したセル Cvalid には、当然のことながら
Aコード
Bコード
Cコード
が表示されます。
 
此処で、セル B3 の○を×に上書き(あるいは、○か×を選択できる入力規則を設定しておいて、×を選択)すると、セル Cvalid には、下記のように表示されます。
Aコード
Cコード
(空白)

回答
投稿日時: 19/01/08 17:39:05
投稿者: んなっと

F列に上に詰めて表示して、それを参照する例です。
 
     A   B C D E    F
1   名称 表示      Aコード
2 Aコード  ○      Cコード
3 Bコード  ×         
4 Cコード  ○         
 
F1
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($2:$500)/(B$2:B$500="○"),ROW(F1))),"")
下方向・↓
 
としてから
 
入力規則:リスト▼:元の値:
=OFFSET($F$1,,,COUNTIF($B:$B,"○"))

投稿日時: 19/01/09 09:10:14
投稿者: quma10

みなさま、ありがとうございます。
ソートができればよいのですが
並び順は変えられないのです。Mikeさんにお書きいただいた件を
よく見てみます。

投稿日時: 19/01/09 11:33:31
投稿者: quma10

Mikeさんとんなっとさんの情報を参考に
ソートせずにうまくいきました。
配列数式だと後任者が直せなかったりセルを触られてしまうと困るので
詰めて表示するのは、んなっとさんの式をお借りしました。
マクロを使わずに済み助かりました。
ありがとうございました。