Excelでリストボックス(入力規則)を活用したい

質問

Excelでセルにリストボックスを作りたいと思っています。

リストボックスの値は、シートのセルから導きだすのですが、A1からA12までに値が入力されているとして、重複する値はリストボックスに含めないようにリストボックスを作成したいのです。
入力規則の手順は分かるのですが、重複しない配列を作成する方法が分からないです。
詳しくは画像のとおりです。

どうぞよい知恵を授けてください。

回答No.1

msMike · ベストアンサー

添付図参照
次式を入力したセル B2 を下方にズズーッとオートフィルして得た列Ｂのデータをリストとして参照したら如何かと。
￣￣ =IFERROR(INDEX(A$1:A$12,SMALL(IF(A$2:A$12=A$1:A$11,"",ROW(A$2:A$12)),ROW(A1))),"")
￣￣【お断り】上式は必ず配列数式として入力のこと

お礼

回答ありがとうございます！
なるほどそのような手があるのですね。
欲を言えば、Ｂ列の作業セルを使わずに導くことができればと考えていますが…難しいでしょうか。

回答No.4

imogasi

>欲を言えば、Ｂ列の作業セルを使わずに導くことができればと考えていますが…難しいでしょうか。
エクセルを使い始めて間もない人のようで、難しいと思う。質問者は、＃１回答に関して、エクセルの関数の本質も判ってない。
また質問者はＶＢＡにも首を突っ込んでいないだろうから、下記を書いても無駄だと思うが。
質問者以外の人でＶＢＡを少し判る人のために、参考までに、やってみたことを記してみます。
エクセルに限っても、素人的に「あれもできないか、これもできないか」言うなら、最低でもＶＢＡを勉強すること。それでもできないことは沢山ある。
素人には、簡単そうでもＶＢＡでは、むつかしいこともある。
（エクセルの本質）
（１）エクセル（関数）では、頭の中に、ああすればよい（たとえば一行おきとか、重複のないデータなど）いう方式は、簡単に想像して、文章で表現できても、中間の変数（や配列の変数）を、関数では使えないので、従ってどこかの空き（使わない邪魔にならない）セル範囲に、最終の（リストの）データを置くセル範囲を作らないといけない場合が多い。
（２）元データ（範囲と中身）は普通は、変更や追加・抹消される場合があると思うが、その場合に、連動してリストのデータ源も変わってほしい場合が多い。
（１）（２）を満たすのは、エクセルの関数です。すでに＃１で回答がありま
す。それも作業列を使わないために、配列数式という、普通ではない仕組みを使わざるを得ないのです。でも最終結果列は絶対使わないとできない。
普通入力規則はデータを利用者が手入力して、それを使う程度のものです。
それも表計算ソフトとして、異例な付加機能でしょう。
同じようなことをやりたいなら、勉強する人は、ＵｓｅｒＦｏｒｍのしくみを使って、ＶＢＡも使ってやるでしょう。
ーー
本件についてＶＢＡでやる一方法例
例データ　Ｓｈｅｅｔ１　Ａ１：Ａ１２に
データ
横
縦
縦
縦
斜
斜
左
左
上
上
下
ーー
ＶＢＥ画面で、挿入ー標準モジュール
コードは
Sub test02()
Dim a(100) '重複のないデータの配列
Dim b(100) '元データの配列
lr = Range("A10000").End(xlUp).Row
MsgBox lr
'---元データの配列を作る
For i = 1 To lr
b(i) = Cells(i, "A")
'MsgBox b(i)
Next i
'----重複のないデータを配列に作る
j = 1
For i = 2 To lr
'MsgBox b(i)
If Application.WorksheetFunction.CountIf(ActiveSheet.Range("A1:A" & i), b(i)) = 1 Then
a(j) = b(i)
j = j + 1
Else
End If
p1:
Next i
'---重複のないデータの配列から入力規則用のリスト文字列を作る
s = ""
For i = 1 To j
'MsgBox a(i)
s = s & a(i) & ","
Next i
s = Left(s, Len(s) - 1)
MsgBox s
'---Ｃ２；Ｃ７に入力規則を設定

With ActiveSheet.Range("c2:C7").Validation

'入力規則を削除
.Delete
'入力規則の設定：
'エラーメッセージのスタイルは「注意」
.Add Type:=xlValidateList, _
Formula1:=s, _
AlertStyle:=xlValidAlertWarning

End With

End Sub
ーーー
実行するとＣ２：Ｃ７に入力規則が設定される。
Ａｃｃｅｓｓなどのリストボックスではデータソースに配列やＳＱＬ文を指定できたように思う

補足

失礼ですが、VBAを使うこともできますし、全く不可能ならその方法も考えてはいました。
しかし、VBAはなるべ使いたくないし、数式の関数ですむなら数式で済ませたいと思っています。
知らないことは知らないし、知ってることは知っています。理解していないことは悪いことなんでしょうか？
現に、あなただって私がVBAを使えることは知らなかったはずなのだから。
知らない人が質問するのだから、どうせ教えても無駄みたいな言い方は、とても気分が悪いですよ。知恵を貸していただけるのはとてもありがたいことです。だからぜひ嫌みなしに今回のように丁寧に教えてくだされば、ベストアンサーになると思いますし、心がけてくだされば幸いだと思います。

回答No.3

tsubu-yuki

msMikeさん、横入りで申し訳ないです。

> No.1 へのコメント
> Ｂ列の作業セルを使わずに導く

出来ないとは言いませんが、「より」難しくなりますよ。
例えば

　　If Target.Address <> "$D$7" Then Exit Sub

　　myTxt = ""
　　For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
　　　　If WorksheetFunction.CountIf(Range("A1", "A" & i), Range("A" & i)) = 1 Then
　　　　　　If myTxt <> "" Then myTxt = myTxt & ","
　　　　　　myTxt = myTxt & Range("A" & i)
　　　　End If
　　Next

　　With Target.Validation
　　　　.Delete
　　　　.Add Type:=xlValidateList, _
　　　　　　AlertStyle:=xlValidAlertStop, _
　　　　　　Operator:=xlBetween, _
　　　　　　Formula1:=myTxt
　　End With
　　SendKeys "%{Down}"

やっつけですが、例えばこんな感じのものを
セレクションチェンジイベントに仕込んでやればＯＫです。

お礼

数式でできないのならVBAでもと考えてはいましたが、いろんなブックで使うのならリスクは高そうですね…
お知恵を貸してくださりありがとうございました？

回答No.2

msMike

[No.1お礼]へのコメント、
》 …ことができればと考えていますが…難しいでしょうか。
さぁ～、私にはアレが精いっぱいです。

お礼

難しそうですね…私なりにも考えてはみましたが…
VBAを使う方法も考えていましたが、作業列が１列ですむならお答えいただいた回答例を採用するのが１番よいと思いました。ありがとうございました！