Excelの入力規則について質問があります。
現在、入力規則のリストを用いて、商品名(書式例:「A1001 電卓」 Aから始まるコードはユニーク値で品名とコードの間は半角スペースです)を指定させています。
これまでは件数も20件程度と少なかったので問題なかったのですが、最近件数が50件を超えてしまい、リストの中から選択するのが大変になってきました。
商品名の一部を入力したら、オートコンプリートの機能のように残りの部分を補完するようなことを実現するにはどういった方法があるか、実例とセットで回答をお願いします。
なお、対象とするExcelのバージョンはver2000以降とします。
「コンボボックス」のオートコンプリート機能で実現する
1.Sheet1 に「コンボボックス」を追加します。
メニューの[表示]-[ツールバー]-[コントロールツールボックス]を表示します。
その中から、「コンボボックス」を選択し、Sheet1 に追加します。
2.VBAの記述
メニューの[ツール]-[マクロ]-[Visual Basic Editor]を表示します。
プロジェクトエクスプローラに表示されている[ThisWorkbook]をダブルクリックして、以下のコードを記述します。
'ワークブックのオープン時イベント
Private Sub Workbook_Open()
'コンボボックスの表示内容を設定
With Sheet1.ComboBox1
.Font.Size = 8
.Visible = False
.AddItem "A1001 電卓"
.AddItem "A1002 そろばん"
.AddItem "A1103 りんご"
.AddItem "A1104 みかん"
.AddItem "A1205 桜"
.AddItem "A1206 銀杏"
.AddItem "A2001 スプーン"
.AddItem "A2002 フォーク"
.AddItem "A2103 ウサギ"
.AddItem "A2104 たぬき"
.AddItem "A2205 えび"
.AddItem "A2206 かに"
End With
End Sub
次に、プロジェクトエクスプローラに表示されている[Sheet1]をダブルクリックして、以下のコードを記述します。
'ワークシートのセルの選択を変更した場合のイベント
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'A列のみ、コンボボックスでの入力に設定してみます。
If Target.Column = 1 Then 'A列は1、B列は2・・・といった具合に指定
If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
With ComboBox1
'コンボボックスの大きさをセルに合わせる
.Width = Target.Cells(1, 1).Width
.Height = Target.Cells(1, 1).Height
.Top = Target.Top
.Left = Target.Left
'コンボボックスを表示
.Value = ActiveCell.Value
.Visible = True
.Activate
End With
End If
Else
'コンボボックスを非表示
ComboBox1.Visible = False
End If
End Sub
'ワークシートの変更時イベント
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim hit As Boolean
If Target.Column = 1 Then 'A列は1、B列は2・・・といった具合に指定
If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
'入力規則チェック
If Target.Value <> "" Then
hit = False
For i = 0 To ComboBox1.ListCount - 1
If ComboBox1.List(i) = ComboBox1.Value Then
hit = True
End If
Next i
If hit = False Then
MsgBox "入力規則に一致していません。", vbExclamation
Target.Value = ""
Exit Sub
End If
End If
End If
End If
End Sub
'コンボボックスの変更時イベント
Private Sub ComboBox1_Change()
'コンボボックスの内容をセルにセット
ActiveCell.Value = ComboBox1.Value
End Sub
'コンボボックスのキー押下時イベント
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Or KeyCode = vbKeyTab Then
'[Enter]、[Tab]キーが押されたら、次のセルへ移動
ActiveCell.Activate
SendKeys "{TAB}"
End If
End Sub
3.以上で一旦、ワークブックを保存します。
4.ワークブックを再起動して、Sheet1のA列をクリックしてください
コンボボックスが表示されて、オートコンプリート入力が可能になります。
[Enter]、[Tab]キーの押下で、次の入力セルに移動します。
ツール→オプション→編集→オートコンプリートを使用するにチェックを入れるやり方ではダメですか?
残念ながら入力規則のリストを使用している場合このやり方では要求を達成できません。
回答1への返信のとおり、Excelのオートコンプリートでは残念ながら要求を達成できません。
Excel2003であれば
ツール⇒オプション⇒編集にオートコンプリートを使用するというチェックボックスがあります。
他のVersionは未確認です。
回答1への返信のとおり、Excelのオートコンプリートでは残念ながら要求を達成できません。
3です。
オートコンプリートを使用するにチェックを入れた後、一度でもその文字列を打ち込めば次回から使えるようになります
(Version2003にて確認済み)
質問の仕方が悪かったです。
条件をもう少し詳しく列挙します。
1.すでにリストに登録されている文字列が対象
※文字列を手入力するという操作は行いません
2.リストはウェブクエリで都度更新している
3.希望を言えば、対象となる文字列を頭から入力するだけではなく、たとえば電卓と入れたらA1001 電卓が表示されるようにしたい。
2です。
「入力規則」を使用する場合は要求を達成できませんが、
「入力規則」とは別の機能を使えば要求を達成すると思います。
セルB5に「た」などと入力し残りの文字を補完してもよいのですが、リストから選択することもできるんです。リストを表示するには、Altキーを押しながら下矢印キー(↓)を押します。あるいは、セルB5を右クリックしてショートカットメニューを表示し、[リストから選択]をクリックしてもOKです。
Office TANAKA - Excel(ワークシート上でリスト入力)
上記リンクの下の方に書いてあります。リストにも、オートコンプリートにも対応できます。
条件不足で申し訳ない。
「入力規則>リスト」は必須条件としてください。
今回の質問の意図は、「入力規則>リスト」の補助機能として、オートコンプリートのような手段を追加したいというものです。
「コンボボックス」のオートコンプリート機能で実現する
1.Sheet1 に「コンボボックス」を追加します。
メニューの[表示]-[ツールバー]-[コントロールツールボックス]を表示します。
その中から、「コンボボックス」を選択し、Sheet1 に追加します。
2.VBAの記述
メニューの[ツール]-[マクロ]-[Visual Basic Editor]を表示します。
プロジェクトエクスプローラに表示されている[ThisWorkbook]をダブルクリックして、以下のコードを記述します。
'ワークブックのオープン時イベント
Private Sub Workbook_Open()
'コンボボックスの表示内容を設定
With Sheet1.ComboBox1
.Font.Size = 8
.Visible = False
.AddItem "A1001 電卓"
.AddItem "A1002 そろばん"
.AddItem "A1103 りんご"
.AddItem "A1104 みかん"
.AddItem "A1205 桜"
.AddItem "A1206 銀杏"
.AddItem "A2001 スプーン"
.AddItem "A2002 フォーク"
.AddItem "A2103 ウサギ"
.AddItem "A2104 たぬき"
.AddItem "A2205 えび"
.AddItem "A2206 かに"
End With
End Sub
次に、プロジェクトエクスプローラに表示されている[Sheet1]をダブルクリックして、以下のコードを記述します。
'ワークシートのセルの選択を変更した場合のイベント
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'A列のみ、コンボボックスでの入力に設定してみます。
If Target.Column = 1 Then 'A列は1、B列は2・・・といった具合に指定
If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
With ComboBox1
'コンボボックスの大きさをセルに合わせる
.Width = Target.Cells(1, 1).Width
.Height = Target.Cells(1, 1).Height
.Top = Target.Top
.Left = Target.Left
'コンボボックスを表示
.Value = ActiveCell.Value
.Visible = True
.Activate
End With
End If
Else
'コンボボックスを非表示
ComboBox1.Visible = False
End If
End Sub
'ワークシートの変更時イベント
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim hit As Boolean
If Target.Column = 1 Then 'A列は1、B列は2・・・といった具合に指定
If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
'入力規則チェック
If Target.Value <> "" Then
hit = False
For i = 0 To ComboBox1.ListCount - 1
If ComboBox1.List(i) = ComboBox1.Value Then
hit = True
End If
Next i
If hit = False Then
MsgBox "入力規則に一致していません。", vbExclamation
Target.Value = ""
Exit Sub
End If
End If
End If
End If
End Sub
'コンボボックスの変更時イベント
Private Sub ComboBox1_Change()
'コンボボックスの内容をセルにセット
ActiveCell.Value = ComboBox1.Value
End Sub
'コンボボックスのキー押下時イベント
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Or KeyCode = vbKeyTab Then
'[Enter]、[Tab]キーが押されたら、次のセルへ移動
ActiveCell.Activate
SendKeys "{TAB}"
End If
End Sub
3.以上で一旦、ワークブックを保存します。
4.ワークブックを再起動して、Sheet1のA列をクリックしてください
コンボボックスが表示されて、オートコンプリート入力が可能になります。
[Enter]、[Tab]キーの押下で、次の入力セルに移動します。
ありがとうございます。
コンボボックスについては少し調べていたのですが、ここまでピンポイントで回答いただけると非常に助かります。
お昼ごろに締め切ろうと思いますが、今のところこのやり方が最有力です。
3です。
そこまで求めるならマクロを使うことになりますね。
マクロ利用はOKなんでしょうか?
それと、マクロを使った場合、入力NGのものはエラーを出すようにすれば、入力規則は無視していいですか?
おはようございます。
方法は、マクロでも関数でもかまいません。
また、マクロの場合はエラートラップについても実装なしでOKです。
検索用のセルを別にするなら入力規則でもできますが、要件からは外れるでしょうか?
一応、方法だけ提示します。
検索用の入力セル:C2
商品名のマスタ:E2:E...
F2 =IF(ISERROR(FIND($C$2,E2)),"",ROW()) この式を下にコピー
G2 =INDEX($E:$E,SMALL($F:$F,ROW()-1)) この式をしたにコピー
そして、入力規則の「リスト」で、元の値に
=OFFSET($G$2,0,0,COUNT($F:$F),1)
で、検索窓は別になりますがご希望のことはできます。
http://www.eurus.dti.ne.jp/yoneyama/Excel/n-kis.htm
を参考にしています。
もしかしたら、このリストの表を縦ではなく横方向にすると、各行で入力規則用のリストセルを作ると、50個程度ならできそうな気がしますが..
やってみないと..
前の回答から各行の入力規則に対応してみました。
シートを入力用、商品名、検索結果と3個のシートに分けています。
A列に入力規則を設定します。B列より右は作業列で使用します。
商品名シート
C2から右に、商品名のコード・品名を設定
検索結果シート
C2に =IF(ISERR(FIND(入力用!$A2,商品名!C$2)),"",IF(LEN(商品名!C$2)>0,COLUMN(),""))
この式を、右に商品名個数分、下に入力行数分コピー
入力用シート
B2に =COUNT(検索結果!2:2) これは、検索結果に合致する個数
この式を下に入力行数分コピー
C2に =INDEX(商品名!$2:$2,SMALL(検索結果!2:2,COLUMN()-2))
この式を、右に商品名個数分、下に入力行数分コピー
A2の入力規則にリストで =OFFSET($C2,0,0,1,B2)
これで、入力用シートのA列がご希望の状態になると思います。
セル位置によって、適宜調整が必要です。Column()-2の部分など
ありがとうございます。
さて、予定していたお昼になりましたので、皆さんからの回答を確かめ、ポイント付与に進みたいと思います。
ひとまず回答の受付を一時停止にします。
ありがとうございます。
コンボボックスについては少し調べていたのですが、ここまでピンポイントで回答いただけると非常に助かります。
お昼ごろに締め切ろうと思いますが、今のところこのやり方が最有力です。