[[20050119152410]]　『条件付の入力規則のリスト』（miu）　ページの最後に飛ぶ

『条件付の入力規則のリスト』（miu）

 いつもお世話になってます。
 質問です。

 sheet1
      A   
 1 
 2    [リスト(aa,bb,dd)]
 3    [リスト(bb,dd,ee)]
 4　　[リスト(aa,cc)]
 .
 .
 .

 sheet2
      A    　B 　   C   　 D  　  E 
 1    aa 　　bb 　  cc　   dd 　  ee 
 2    OK  　 OK      　 　 OK    
 3        　 OK      　　  OK   　OK
 4    OK    　    　OK        
 .
 .
 .

 上記の様な[sheet1],[sheet2]があるとします。
 sheet1のＡ列に入力規則のリスト（sheet2のＡ１：Ｅ１）で表示をしたいのですが
 条件がありましてsheet2で[OK]が入ってる列だけリストに反映するようにしたいです。
 上記の表を例にすると
 sheet1のＡ２は、sheet2の２行目でOKが入ってる列に対応するリストを抽出して
 リストは（aa,bb,dd）だけを選択できるようにする。
 以下同様に、
 ・sheet1のＡ３のリストは（bb,dd,ee）だけを選択できるようにする。
 ・sheet1のＡ４のリストは（aa,cc）だけを選択できるようにする。
 良い方法がありましたら教えてください。
 宜しくお願い致します。

 あまり応用は利かないと思います。
 Sheet2で作業列を使った方法です。

 Sheet2	 A	 B	 C	 D	 E	 F	 G	 H	 I	 J
  1	aa	bb	cc	dd	ee					
  2	OK	OK		OK		aa	bb	dd		
  3		OK		OK	OK	bb	dd	ee		
  4	OK		OK			aa	cc

 Sheet2のＦ2=IF(COUNTA($A2:$E2)<COLUMNS($F2:F2),"",INDEX($A$1:$E$1,1,SMALL(IF($A2:$E2<>"",COLUMN($A2:$E2)),COLUMNS($F2:F2))))
 として、Shift+Ctrlキー押しながらEnterキーで確定させて配列数式に。　Ｊ4までコピー

 Sheet1のA2を選択して、挿入-名前-定義で、参照範囲に以下の式を入れて、名前を　List　としてください。
=OFFSET(Sheet2!$F2,,,,COUNTA(Sheet2!$F2:$J2)-COUNTIF(Sheet2!$F2:$J2,""))
 A2を選択した状態で、データ－入力規則－リストで元の値に　=List　としてＯＫ

 A列以外でも使えるように、名前の定義を少し修正しました。
 19:13
 （川野鮎太郎）

 川野さんお返事遅くなってすみません。
 早速試してみたところうまくいきました。
 いつもありがとうございます。
 式の意味を知りたいのですが複雑すぎてよくわかりませんでした・・・。
 宜しければ式の解説もお願いしてもいいですか？
 宜しくお願いします。

 追記
 数式の検証で式を追ってみようとしても途中でエクセル自体にエラーがでてしまい
 式が何をしてるのか追うことができません・・・。
 例では、Ａ～Ｅ列でしたが実際にはＷ～ＡＡ列で使用したいのですが、上記の式の
 列を変えてしまうと＃ＲＥＦ！がでてしまい使用することができません。
 やっぱり、式の意味がよくわかないと使いこなせそうにないので
 解説お願い致します。
 (miu)

 またもや苦手な解説ですか・・・(^_^A;
 判りにくいかもしれませんが、こんな感じです。
　
IF(COUNTA($A2:$E2)<COLUMNS($F2:F2),"",INDEX($A$1:$E$1,1,SMALL(IF($A2:$E2<>"",COLUMN($A2:$E2)),COLUMNS($F2:F2))))
 　　　　　　　　　　　　　　　　↑　↑
上の↑から前は、空白処理をするための式で、A2からE2までの入力数よりCOLUMNS($F2:F2)が大きい場合TRUEになり空白になります。
このCOLUMNS($F2:F2)は、右方向にコピーすることにより、$F2:G2、$F2:H2となり右側のF2だけ変化します。
COLUMNS関数は範囲内の列数を返しますので、1,2,3と変化します。
よって、I4セルの場合　$F2:I2（4）となって空白になります。
　
上の右の↑より右側の　INDEX($A$1:$E$1,1,SMALL(IF($A2:$E2<>"",COLUMN($A2:$E2)),COLUMNS($F2:F2)))　は
大きく分けてINDEX関数、SMALL関数、IF関数で組み立ててます。
まず、INDEX関数ですが、配列の中の行番号、列番号にあてはまる値を取り出しますので、範囲が$A$1:$E$1　=　{"aa","bb","cc","dd","ee"}　であり
行番号が1、列番号が（？？？）なので列番号を計算するのにSMALL関数以降で求めています。
SMALL関数は範囲内の順位にたいする値を求めますので、範囲が　IF($A2:$E2<>"",COLUMN($A2:$E2))　、順位がCOLUMNS($F2:F2)
　
このIF関数IF($A2:$E2<>"",COLUMN($A2:$E2))　は、A2からE2の中で空白じゃないときCOLUMN($A2:$E2)を返す式で、返り値は{1,2,FALSE,4,FALSE}という配列になります。　
これをSMALL関数の第2引数（COLUMNS($F2:F2)=1）で計算することにより、1番目に小さい=1、2番目に小さい=2、3番目に小さい=4　となります。
　
上記のことにより、INDEX関数の範囲（$A$1:$E$1　=　{"aa","bb","cc","dd","ee"}）の中で、aa,bb,dd　だけが抽出できるわけです。

 （川野鮎太郎）

 川野さんありがとうございます。
 すごくよくわかりました。
 質問ですが、Ａ列からのリストだと上記の式が動くのですが列を変えるとうまくいきません。作業列が「＃ＲＥＦ！」になります。
 使いたいデータを移動してＡ列から試すとうまくいくのですが本来使用したい場所の列ではエラーになります。また、Ａ列でうまくいった状態でＡ列に列を挿入してもエラーになりました。
 範囲を変えるだけなのに＃ＲＥＦ！が出るのはなんででしょうか？
 (miu)

 Sheet2の方のデータのことですよね。

 行挿入したときに、入っていた式の中身を上記説明に照らし合わせたら、
 何が原因かある程度判りませんでしょうか。

 （川野鮎太郎）

 説明から、列数を求めてる時はどれも範囲内や配列内ですよね？
 式を見てもきちんとずらした範囲を選択してるのにエラーがでてしまいます・・・。
 それとも、意味を取り違えてて間違いに気づいてないのでしょうか。。。
 例えば、例の状態でＡ列に１列挿入してＢ列からになっても同じエラーになります。
 で、作業列の1列目も1列ずれてＧ列からになりますが、まずＧ列がＩＮＤＥＸの配列の
 ＦＡＬＳＥ以外の2番目を表示してしまい最後のＫ列に＃ＲＥＦ！がでます。
 配列が1列ずれてるってことですよね？でも配列は範囲を指定した所になってるし・・・。
 これでエラーが出る意味わかりますでしょうか？
 （miu）

 意味は判ります(^_^A;
 ただ上記の説明で、SMALL関数の第1引数は、
 ＞COLUMN($A2:$E2)を返す式で、返り値は{1,2,FALSE,4,FALSE}という配列になります。
 ってことですから、必ず　COLUMN($A2:$E2)　のままでなければいけません。

 というのは、INDEX関数の配列の行番号、列番号の値ですよね。
 配列の範囲が変わっても、左から何番目ってのは常に一緒ですから～・・・。残念！！＾＾
 （川野鮎太郎）

 意味がわかりました！！
 ということは、返り値から動かした列数分引けばいいんですよね！
 あ！出来ました！
 これで、なんとか作業ができます。
 いろいろありがとうございました。m(__)m
 （miu）

コメント返信：

[ 一覧(最新更新順) ]

[[20050119152410]] 『条件付の入力規則のリスト』（miu） ページの最後に飛ぶ

[[20050119152410]]　『条件付の入力規則のリスト』（miu）　ページの最後に飛ぶ