ピンポイントな回答には500ポイント差し上げます!


Excelの入力規則について質問があります。
現在、入力規則のリストを用いて、商品名(書式例:「A1001 電卓」 Aから始まるコードはユニーク値で品名とコードの間は半角スペースです)を指定させています。
これまでは件数も20件程度と少なかったので問題なかったのですが、最近件数が50件を超えてしまい、リストの中から選択するのが大変になってきました。
商品名の一部を入力したら、オートコンプリートの機能のように残りの部分を補完するようなことを実現するにはどういった方法があるか、実例とセットで回答をお願いします。
なお、対象とするExcelのバージョンはver2000以降とします。

回答の条件
  • 1人5回まで
  • 登録:2006/09/11 22:41:57
  • 終了:2006/09/12 15:50:49

ベストアンサー

id:llusall No.6

llusall回答回数505ベストアンサー獲得回数612006/09/12 01:00:44

ポイント500pt

「コンボボックス」のオートコンプリート機能で実現する


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]キーの押下で、次の入力セルに移動します。

id:sugi99

ありがとうございます。

コンボボックスについては少し調べていたのですが、ここまでピンポイントで回答いただけると非常に助かります。

お昼ごろに締め切ろうと思いますが、今のところこのやり方が最有力です。

2006/09/12 05:43:56

その他の回答(8件)

id:untidy No.1

untidy回答回数25ベストアンサー獲得回数02006/09/11 22:47:21

ポイント5pt

ツール→オプション→編集→オートコンプリートを使用するにチェックを入れるやり方ではダメですか?

id:sugi99

残念ながら入力規則のリストを使用している場合このやり方では要求を達成できません。

2006/09/11 22:51:03
id:jan8 No.2

jan8回答回数456ベストアンサー獲得回数962006/09/11 22:49:37

ポイント5pt

EXCELのオートコンプリート機能では目的を満足しないのでしょうか?(下記リンクの一番下)

Office TANAKA - Excel(ワークシート上でリスト入力)

id:sugi99

回答1への返信のとおり、Excelのオートコンプリートでは残念ながら要求を達成できません。

2006/09/11 22:56:04
id:arhbwastrh No.3

arhbwastrh回答回数447ベストアンサー獲得回数232006/09/11 22:54:41

ポイント5pt

Excel2003であれば

ツール⇒オプション⇒編集にオートコンプリートを使用するというチェックボックスがあります。

他のVersionは未確認です。

id:sugi99

回答1への返信のとおり、Excelのオートコンプリートでは残念ながら要求を達成できません。

2006/09/11 22:57:10
id:arhbwastrh No.4

arhbwastrh回答回数447ベストアンサー獲得回数232006/09/11 22:58:23

3です。

オートコンプリートを使用するにチェックを入れた後、一度でもその文字列を打ち込めば次回から使えるようになります

(Version2003にて確認済み)

id:sugi99

質問の仕方が悪かったです。

条件をもう少し詳しく列挙します。

1.すでにリストに登録されている文字列が対象

  ※文字列を手入力するという操作は行いません

2.リストはウェブクエリで都度更新している

3.希望を言えば、対象となる文字列を頭から入力するだけではなく、たとえば電卓と入れたらA1001 電卓が表示されるようにしたい。

2006/09/11 23:08:02
id:jan8 No.5

jan8回答回数456ベストアンサー獲得回数962006/09/11 23:05:30

2です。

「入力規則」を使用する場合は要求を達成できませんが、

「入力規則」とは別の機能を使えば要求を達成すると思います。

セルB5に「た」などと入力し残りの文字を補完してもよいのですが、リストから選択することもできるんです。リストを表示するには、Altキーを押しながら下矢印キー(↓)を押します。あるいは、セルB5を右クリックしてショートカットメニューを表示し、[リストから選択]をクリックしてもOKです。

Office TANAKA - Excel(ワークシート上でリスト入力)

上記リンクの下の方に書いてあります。リストにも、オートコンプリートにも対応できます。

id:sugi99

条件不足で申し訳ない。

「入力規則>リスト」は必須条件としてください。

今回の質問の意図は、「入力規則>リスト」の補助機能として、オートコンプリートのような手段を追加したいというものです。

2006/09/11 23:13:15
id:llusall No.6

llusall回答回数505ベストアンサー獲得回数612006/09/12 01:00:44ここでベストアンサー

ポイント500pt

「コンボボックス」のオートコンプリート機能で実現する


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]キーの押下で、次の入力セルに移動します。

id:sugi99

ありがとうございます。

コンボボックスについては少し調べていたのですが、ここまでピンポイントで回答いただけると非常に助かります。

お昼ごろに締め切ろうと思いますが、今のところこのやり方が最有力です。

2006/09/12 05:43:56
id:arhbwastrh No.7

arhbwastrh回答回数447ベストアンサー獲得回数232006/09/12 02:32:13

3です。

そこまで求めるならマクロを使うことになりますね。

マクロ利用はOKなんでしょうか?

それと、マクロを使った場合、入力NGのものはエラーを出すようにすれば、入力規則は無視していいですか?

id:sugi99

おはようございます。

方法は、マクロでも関数でもかまいません。

また、マクロの場合はエラートラップについても実装なしでOKです。

2006/09/12 05:45:12
id:kaiton No.8

kaiton回答回数260ベストアンサー獲得回数342006/09/12 09:46:39

検索用のセルを別にするなら入力規則でもできますが、要件からは外れるでしょうか?

一応、方法だけ提示します。

検索用の入力セル: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個程度ならできそうな気がしますが..

やってみないと..

id:kaiton No.9

kaiton回答回数260ベストアンサー獲得回数342006/09/12 11:59:37

ポイント50pt

前の回答から各行の入力規則に対応してみました。

シートを入力用、商品名、検索結果と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の部分など

id:sugi99

ありがとうございます。

さて、予定していたお昼になりましたので、皆さんからの回答を確かめ、ポイント付与に進みたいと思います。

ひとまず回答の受付を一時停止にします。

2006/09/12 12:24:13
  • id:llusall
    たくさんのポイントありがとうございました。
  • id:sugi99
    こちらこそ、的確な回答感謝します。
    Private Sub Workbook_Open()のWith Sheet1.ComboBox1の部分を、ウェブクエリのデータ更新と連動させることで、目的を100%解決することができました。
    作業を大幅に短縮でき、本当に助かりました。
  • id:robbie21
    'コンボボックスの表示内容を設定

    With Sheet1.ComboBox1
    ...
    .AddItem "A1001 電卓"
    ...

    End With

    のところは
    "商品リスト"と名前のついたセル範囲に入力規則のためのリストがあるとして、

    .ListFillRange="商品リスト"
    とすればすっきりします。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません