Excelの入力規則について

質問

Excelの入力規則について教えてください。

A1:A20に入力規則を設定します。
入力値の種類にリストを選び、元の位置で、北海道、青森、岩手、山形・・・と都道府県名を20件ほど入力し、設定が完了したとします。
A1セルでドロップダウンして、岩手を選び、次のA2のセルでドロップダウンした時、岩手をリストから消しておきたいのです。
つまり、リストから選んだものは、次のセルでドロップダウンした時、常に消えておくことができるように設定したいのですが、良い方法はありませんか？

回答No.1

shiotan99 · ベストアンサー

こんにちは～

リストを2つ作ってもよければ、次の方法を試してみてください。

A1：A20を空白の状態にしておいてください。
元のリストが J1：J20 にあるとします。

K1に
=IF(COUNTIF($A$1:$A$20,J1),"",ROW())

と入れて、K20までフィルコピー（ 1から20の数字が入ります ）

同じリストをたとえば H列につくるとします。
H1に
=IF(COUNT(K:K)<ROW(A1),"",INDEX(J:J,SMALL(K:K,ROW(A1))))

と入れて、H20までフィルコピー
J列と同じリストができたと思います。

A1：A20の入力規則のリストを、この H列の方で指定してください。
リストが目障りでしたら非表示にしてもかまいません。

お礼

返事遅くなり申し訳ございません。
すごいです。できました。
簡単なことかと思いましたが、こんなに難しい関数が入っているとは、・・・。
私には、絶対できませんでした。
本当に教えていただき、ありがとうございました。

回答No.3

Wendy02

こんにちは。

私もちょっと試しに考えてみました。
混乱させないように、#1 さんの回答のデータの位置関係をそのまま利用させていただきます。

H1:～H20まで
=IF(COUNTA($J$1:$J$20)>=COUNTA($A$1:$A$20)+ROW(A1),INDEX($J$1:$J$20,SMALL(ISERROR(MATCH(ROW($A$1:$A$20),MATCH(INDIRECT("A1:A"&COUNTA($A$1:$A$20)),$J$1:$J$20,0),0))*ROW($A$1:$A$20),COUNTA($A$1:$A$20)+ROW(A1)),),"")

配列数式なので、一旦式を入力したら、F2を押して、『ShiftとCtrlを押しながらEnterキー』を押して、再確定してください。

入力規則のリスト位置は、「$H$1:$H$20」です。

また、範囲の変更は、置換で行ってください。また、ROW()関数内部の範囲は、$A$1から始るということになっていますので、ご注意願います。

なお、今回、あくまでも、数式はプロセスよりも解本位であり、配列により1列で出せることを示しただけに過ぎません。

お礼

ご連絡遅くなり申し訳ございません。
質問した内容が、私にとってこんなに難しい内容のものであったとは、全く分かりませんでした。
骨を折っていただき、本当に感謝しています。
やってみます。
回答をいただいたお二人に、心を込めて！

回答No.2

shiotan99

No.1です。

入力規則のリストを設定するとき、データの入力規則ダイアログの＜元の値＞の欄には、
=$H$1:$H$20

ではなく、

=INDIRECT("H1:H"&COUNT(K:K))

という数式を入れてください。

※No.1の最初の数式が K列、2番目の数式が H列に入っていれば、です。
H列、K列以外の列に数式を入れた場合は、上の数式の H、K をその列記号に変えてください。

＜元の値＞に =$H$1:$H$20 のように入れてもできますが、これだとよけいな空白ができて、ちょっとウザイです。

※おわかりかとは思いますが、No.1の数式も、セル位置・列記号などは実際の表に合わせて、変更すべきところは変更してくださいね（ J1 とか、 K:K とか、J:J とかです ）。
ただし、2番目の数式の ROW(A1) の (A1) は変えないでください。

お礼

いろいろな作り方があるのですね。
回答いただいたshiotan99さんは、関数の達人ですね。
私も、回答いただいたようになりたいですけど、
私の能力では、到底追いつけないようです。
でも、教えていただいた内容をペイストすれば、作成できます。
また、分からない事があったら、教えてください。
よろしくお願いします。