エクセルでデータを入力する際の高速化についての考察② <予測変換>
記事IT・テクノロジー
こんにちは、盤屋の試験員です。
エクセルにデータ入力する際の高速化について②となります。
第２回はマクロによる特化型予測変換です。
エクセルにも予測変換はありますが、今回のものはデータ特化型でよりニッチな需要にこたえる目的のものです。
１．はじめに～～処理範囲の限定と分岐
マクロで予測変換する場合、この場所は数字、この場所は文字という具合にセルによって入力する規則が変わりますし、場所によっては予測変換はしたくないということもあり得るので、まずはその処置についてお話します。
下記がシートの処理を場所によって分岐する場合のサンプルです。
A1:A10の範囲であれば処理①を,B1:B10であれば処理②を、それ以外は無視する場合のコードです。
このコードはワークシートのコ記述シートに記載して使います。（これはサンプルなんで動きません）
-------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("A1:A10")) Is Nothing) Then
'処理①
ElseIf Not (Intersect(Target, Range("B1:B10")) Is Nothing) Then
'処理②
End If
End Sub
-------------------------------------------------------------------------
マクロの中でセルの場所を指定していますが、これだと使い勝手が悪すぎます。よって、前回同様セルの名前を利用する方法の方が良いでしょう。
前回は単独のセルに名称を付けましたが、今回はセル範囲に名前を付けます。
初めにモジュールに次のコードを貼り付けます。
-------------------------------------------------------------------------
Public Function GetRangeName(TergetCell As Object, Optional Keyword As String = "") As String
'指定したセルの範囲名称を返します。
Dim Q As Object
Dim D() As String
For Each Q In ActiveWorkbook.Names
If Not (Application.Intersect(TergetCell, Q.RefersToRange) Is Nothing) Then
If Keyword <> "" Then
D = Split(Q.Name, Keyword)
If UBound(D) = 1 And D(0) = "" Then
GetRangeName = Q.Name
Exit For
End If
Else
GetRangeName = Q.Name
Exit For
End If
End If
Next
End Function
-------------------------------------------------------------------------
前回とほぼ同じコードです。複数エリアに跨っていることがあるのを配慮してNameでフィルタ出来るようにしてあります。
特定のセルが含まれた特定の名前を含んだセルの名前を返しています。
続いて、Worksheetのコード記述シートに下記を書き込んで使います。（実際の処理は実装してないので今は動きません）
-------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If GetRangeName(Target, "SYA_") <> "" Then
'処理A
ElseIf GetRangeName(Target, "SYB_") <> "" Then
'処理B
End If
End Sub
-------------------------------------------------------------------------
セルの名前は同じものは使えないので末尾に番号を設け、前４文字で判定しています。セル範囲"SYA_1,SYA_2"は"処理A"を、セル範囲"SYB_1,SYB_2"は"処理B",それ以外は無視されます。
これで、セル範囲に名前を付けた範囲で処理が行えるようになりました。
続いて予測変換本体部分のマクロを記載します。
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
２．予測変換①＜前に入力した値を基にする＞
直前に入力した値をメモリしておき、上位桁を補完するようにします。
桁数が同じロット番号などで、上位桁が変わらないような場合や日付を入力する際に有効な方法です。純粋にキータッチ数を減らす効果のあるマクロとなります。
例えば 10桁のロット番号があったとします。
その値は 1：2212201035、2：2212201041のようなデータだとします。
ロット番号は 製造年やハード・ソフトのバージョンなどを含んでいるので前の桁が同じ事が多いので、実質変わるのは後ろの数文字となります。1:の値と2:の値では下２桁の見変わるので
２：の際には"41⏎"の３タッチとするようにコーディングしてみます。
ソースはいたって単純です。
-------------------------------------------------------------------------
Option Explicit
Dim PreValueA As String
Public Sub ValueCheckA(TargetCell As Object)
'前のデータを記憶し、前のデータを基に補完する
Dim S1 As String
Dim S2 As String
With TargetCell
S1 = .Value
S2 = PreValueA
If S1 = "" Then
PreValueA = ""
Exit Sub
End If
If Len(S2) > Len(S1) Then
'元の値より短い場合は補完
.Value = Left(S2, Len(S2) - Len(S1)) & S1
Else
'そのまま
End If
PreValueA = .Value
End With
End Sub
-------------------------------------------------------------------------
プロシージャの上、モジュールレベルで書かれた変数 Dim PreValueA As String がメモリになります。
モジュールレベルなので、プロシージャが終了しても値が破棄ません。
メモリされた値の文字数に合わせ、上位桁が保管されて値が書き直されます。
注意事項として、セルの属性が”数値”または”標準”の場合、上位桁のゼロは無かったことにされるので（０１→１）
関数計算していなければ、セルの属性は文字列にしておいた方が良いです。
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
３．予測変換居②＜セルの値を基に予測＞
セルに予め予定値を書き込んでおき、それを基に補完します。
コードは先ほどとほとんど変わりません。
-------------------------------------------------------------------------
Option Explicit
Dim PreValueB As String
Public Sub MemoryData(TargetCell As Object)
'以前の状態をメモリ
PreValueB = TargetCell.Value
End Sub
Public Sub ValueCheckB(TargetCell As Object)
'前のデータを記憶し、前のデータを基に補完する
Dim S1 As String
Dim S2 As String
With TargetCell
S1 = .Value
S2 = PreValueB
If S1 = "" Then
PreValueB = ""
Exit Sub
End If
If Len(S2) > Len(S1) Then
'元の値より短い場合は補完
.Value = Left(S2, Len(S2) - Len(S1)) & S1
Else
'そのまま
End If
.Font.ColorIndex = xlAutomatic '色を黒くします
.PreValueB = .Value
End With
End Sub
-------------------------------------------------------------------------
プロシージャの上、モジュールレベルで書かれた変数 Dim PreValueB As String がメモリになります。
先ほどと違い、セルが選択されたイベントにてセルの値を一時退避させる必要があるので
Worksheetのコード記述シートに下記を追記します。
-------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If GetRangeName(ActiveCell, "SYB_") <> "" Then
Call MemoryData(ActiveCell) '処理B
End If
End Sub
-------------------------------------------------------------------------
このマクロの場合、あらかじめ入っている値を書き換えたか判らなくなるので、
.Font.ColorIndex = xlAutomatic にて
書き換えた際に文字色を黒にしています。
これは、入力前に文字色を赤などに変えておくことが前提となります。
尚、予めセル範囲に名前がついているので、文字色を変えるのは簡単にできます。
参考までに、文字色を戻すマクロを書くと下になります。
-------------------------------------------------------------------------
Public Sub RedChange(Keyword As String)
Dim Q As Object
Dim D() As String
For Each Q In ActiveWorkbook.Names
D = Split(Q.Name, Keyword)
If UBound(D) = 1 And D(0) = "" Then
Q.RefersToRange.Font.ColorIndex = 3
End If
Next
End Sub
Sub SYB_To_RED()
Call RedChange("SYB")
End Sub
-------------------------------------------------------------------------
これで準備が完了しました。
最後に、Worksheet_Change イベントの部分にプロシージャへのCall関数を追記します。
また、セルの値を書き換えた時に２度目のイベントが発生してしまう為
Application.EnableEvents = False
にてイベントの停止を宣言します。
-------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo Err
If GetRangeName(Target, "SYA_") <> "" Then
Call ValueCheckA(Target) '処理A
ElseIf GetRangeName(Target, "SYB_") <> "" Then
Call ValueCheckB(Target) '処理B
End If
Err:
Application.EnableEvents = True
End Sub
-------------------------------------------------------------------------
これで、範囲"SYA"では前回のメモリによる入力補完
範囲”SYB"ではセルの前回値を基準にした入力補完が有効になりました。
これに、前回のフォーカス移動を組み合わせることで、キーボードで最低限の値を入力することで連続してデータを入れていく事ができるようになります。
値をコピーする際も、Ctrl+C & Ctrl+V より、末尾一文字+⏎ の方が早いのでマウスを持つ必要がかなり少なくなると思います。
今回はここまでとなります。
次回は、フィルハンドルによる連続データ作成や機能のON-OFF等、細かい制御を行う為装飾キーを制御に取り込むやり方を解説します。
----------------------------------------------
マクロの作成承ります。興味があったら相談してみてください。
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
