エクセルの関数で非常に困っています

質問

エクセルの関数で質問です。
添付に記載していますが、
次の三つの条件を満たす式を教えていただきたいです。
（データ入力規制を使用すれば有りがたいです）

1. A3セルに150～200の数値の場合は F3セルに 12 のみが入力できる
2. A3セルに200～265の数値の場合は F3セルに 14 のみが入力できる
3. A3セルに290～315の数値の場合は F3セルに 16 のみが入力できる
　A3セル数値が一部かぶる数値の場合 F3に両方（12 or 14）が入るようにしたいです

宜しくご教示の程お願いいたします。

回答No.3

nishi6 · ベストアンサー

問題はA3=200のときの処理ですね。また、A3の値によって、入力規則のリスト数が異なるので工夫が必要です。
また、1～3以外のとき、どのように規則を作るのか書いてないので、入力規則に候補が出ないようにしました。

条件が複雑なので、入力規則セル外に計算セル範囲を作ります。添付図のG5:I8です。J、K列に算式を表示しています。I列はH列を参照しています。その関係が正しければ、どのセルでも構いません。

G列は単に表題です。

H列は判定です。
　セルH5:=AND(150<=$A$3,$A$3<=200)
　セルH6:=AND(200<=$A$3,$A$3<=265)
　セルH7:=AND(290<=$A$3,$A$3<=315)
　セルH8:=FAlSE

I列は入力規則に表示する値になります。
　セルI5:=IF(H5,12,IF(H6,14,IF(H7,16,"")))
　セルI6:=IF(H5,IF(H6,14,""),"")
　記載していないセルは何も入力しません。

セルF3の入力規則は、
　リスト にして、
　元の値に、
　　=OFFSET(I5, 0, 0, 4-COUNTIF(I5:I8,""), 1)
※リストの行範囲を定義しています。添付図のI列を参照しています。

入力時メッセージやエラーメッセージは、エラー値を選択できなくなっているので不必要かと思いますが、セルF3への直接入力時に必要かもしれません。必要ならば設定してください。計算に使ったセルG5:I8は支障のない場所に移動したり、非表示にすればいいでしょう。当方、Win10、Excel2010です。

お礼

できました！！　本当に助かりました。有難う御座います。
親切に式まで教えていただき感謝です。　楽しく作成できました。

回答No.2

imogasi

わざわざ、入力規則の設定に持ち込まなくても、
Ｆ３セルに関数
=VLOOKUP($A3,$G$1:$H$5,2,TRUE)
を入れたら、よいのではないですか？だい４引数ＴＲＵＥ型であることに注意。
=VLOOKUPだけの解説本が出るくらい、エクセルでは超有名関数です。
上記関数を使う前に、検索表として、例えば空きセル範囲に（本件ではＧ１：Ｈ５に）
Ｇ列　Ｈ列
0
150 12
200 14
265 16
315
のように設定しておく。
ーーーー
この表は、時間がないので十分精査出来てませんが、チェックしてください。
空白シートのＡ列にＡ１：Ａ５００ぐらいに整数連番を入れ、Ｂ１に
=VLOOKUP($A１,$G$1:$H$5,2,TRUE)
と入れて、下方向に式を複写。Ｂ列に出た数との、対応が正しいか？
区切りの値前後や０－１５０や３１６以上についてチェックしてください。

回答No.1

skp026

私が思いついた方法は、入力規制のリストと、リストの範囲それぞれのセルでA3の値をチェックすることです。

とりあえずG1に12、G2に14、G3に16を入力し、F3に入力規制のリストを選びG1からG3を指定します。今回予定の値が、ドロップダウン式のリストで選択できることを確認ください。

次に、G1に式をいれますが、それは、ご質問条件の1と3をifを2つ使いなどし2種類の値を返すようにします。G2にはご質問条件の2の式をいれます。

これによりA3が200の場合、入力規制リストには、12と14がでます。290の場合は16だけ表示されます。

※
入力規制のリストの範囲には、クセがあります。途中が空白になると、リストの範囲が途切れたと判断します。例えば、今回は最初の試しで、G1からG3まで値を入れましたが、その状態でG2をクリアすると、G1の12しか表示されなくなります。式にする場合に、G1に質問条件の1と3を入れたのは、このような理由からです。

※
このような設計は、可読性が悪くなり、修正や変更に時間がかかりやすくなる可能性があります。「このような
設計である」とシートに説明を残すことをおすすめします。

以上、ご希望と違ったり、参考にならなかったらごめんなさい。