excelの入力規則リストで重複不可にするには

質問

エクセルの入力規則でリストを利用して【プルダウンメニュー】を作成しています。
リストのデータを重複して選択できないようにするにはど
のようにすればいいでしょうか？

例をあげるとしして

仮にＡ、ｂ、Ｃの３つのセルに入力規則のリストを設定していずれも元の値が
『リンゴ』『みかん』『バナナ』とした場合。

Ａのセルでリンゴを選択した時ＢやＣのセルでリンゴを選択できないようにするに
します。

VBAは全く分からないのでできれば関数で
やる方法をご教授頂ければと思います。
宜しくお願いします

回答No.3

imogasi

（１）入力規則でリストを使う
（２）入力規則で同じ値の入力を禁止する
それぞれどちらか１つなら可能だが両方となると難しい。回答は出ているが、質問者には思いつかない方法だろう。回答を丸写ししかない。
ーー
それなら、ＶＢＡは判らないといっているが、どちらも丸写ししか無いだろうから変わりは少ないのでは。
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If WorksheetFunction.CountIf(Range("a1:A100"), Target) <> 1 Then
Application.EnableEvents = False
MsgBox "重複禁止"
Target = ""
Target.Select
Application.EnableEvents = True
End If
End Sub
をＳｈeｅｔ１などのシートイベントに貼り付けるのと変わらないのではないか。
シートタブ部で右クリックー「コードの表示」を選ぶーそこで出てきた画面にコピペ。
＝＝＝
又別の手立てで
入力規則はリストを使い、どこか空きの目立つセルに
=IF(COUNTA(B2:B20)<>SUMPRODUCT(1/COUNTIF(OFFSET(B2,0,0,COUNTA(B2:B20)),OFFSET(B2,0,0,COUNTA(B2:B20)))),"重複あり","")
と入れるのはどうだろう。これも式が難しいが。
下記の条件付きだが、重複があれば、上記式を入れたセルに「重複あり」が出る。
・Ｂ列の例
・Ｂ２セル以下にデータを入れる例
・Ｂ２０までにデータが入る例。少なくても可。式を３０などに変えて対象行を増やすことは簡単。
・Ｂ列途中で空白行は無いという例

お礼

ご丁寧にありがとうございます。さっそく試してみます

回答No.2

keithin

丁寧に作れば簡単にできます。

手順：
シート２のＢ１に「元ネタ」と記入
シート２のＢ２からＢ４に「リンゴ」「バナナ」「オレンジ」を記入
シート２のＡ２に
=IF(OR(B2="",COUNTIF(Sheet1!A:A,B2)),"",MAX($A$1:A1)+1)
と記入し，ずっと下までコピー

シート２のＤ１に「入力規則の元リスト」と記入
シート２のＤ２に
=IF(ROW(D1)>MAX(A:A),"",VLOOKUP(ROW(D1),A:B,2))
と記入し，ずっと下までコピー

データメニュー(Excel2007以降ではデータタブ）の名前の定義（名前の管理）を開始，
　名前　元リスト
　参照範囲　=OFFSET(Sheet2!$D$2,0,NOW()*0,MAX(Sheet2!$A:$A),1)
として登録

シート１のＡ列を列選択
入力規則で種類をリストに変え，元の値に
=元リスト
とする。

お礼

早速ありがとうございます。

回答No.1

tom04

こんばんは！
苦肉の策の一例です。

↓の画像で説明します。
通常はF1～F3セルを範囲指定して、入力規則のリストの「元の値」にすると思います。
そこでA1～C1セルのリスト表示で重複なしに表示するように一手間かけます。

E1セルに
=IF(COUNTBLANK($A$1:$C$1)<ROW(A1),"",INDEX($F$1:$F$3,SMALL(IF(COUNTIF($A$1:$C$1,$F$1:$F$3)=0,ROW($A$1:$A$3)),ROW(A1))))
これは配列数式になってしまいますので、Shift+Ctrlキーを押しながらEnterキーで確定！

この画面からコピー＆ペーストする場合はE1セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift+Ctrlキー+Enterキー！
数式の前後に｛　｝マークが入り配列数式になります。
このE1セルをE3セルまでオートフィルでコピー！
これでA1～C1セルにデータがあるもの以外が順に表示されます。

このE1～E3セルを入力規則の「元の値」としてみてはどうでしょうか？

参考になれば良いのですが・・・m(_ _)m

お礼

なるほどそんなやり方があるのですね。是非試してみます