Excelでは、データ処理をする時に何度も同じ作業をするということがありますが、そういう機能を一つ一つショートカットキーで操作しても結局は操作の手数が減らないので最終的な効率化にはならないのではないかなと思っています。
せっかくショートカットキーにするのであれば、複数の機能を一気に操作できるようにマクロにしてそのマクロをショートカットキーに登録すればいいのではないかなということなのですがその記事がこちらです。
究極のショートカットキーの使い方。ショートカットキーは自分で作れ！
今回はその応用で3年くらい前からずっとやりたかったことなのですが今までスキルがそこまでなくて作れないでいたのですが、やっとそれを作ることができたのでそれを紹介したいと思います。
二つの表があってその表の間でデータをガッチャンコするにはVLOOKUP関数を使うのですが、ひとつVLOOKUP関数を作成して縦方向にはコピーできるのですが横方向にはコピーできないので一つ一つ列番号を変えながら作っていくしかないのですね。何よりも今日のどこがキー項目でとか判断するのもVLOOKUP関数を、エラー回避を含めて作成するとか一切面倒でやりたくないというところが本音なのです。
だから、いわゆるトランザクションの表とマスターの表をクリックするだけで、VLOOKUP関数で連携する項目が追加されるというのが理想だと思っていたのです。
そこで作成したものがこちらです。
クリックしただけでというのはなかなか難しかったので、トランザクションの表のうち一つのセルをクリックしたらCtrl+Shift+V、その後、マスターの表のうち一つのセルをクリックしたらCtrl+Shift+Lのショートカットキーを押したらそれだけでVLOOKUP関数がエラー回避も含めて作成されて、トランザクションに不足しているマスターの項目が追加されるという動作にしました。
VBAソースは以下の通りです。
'* * * * * *
'VLOOKUP関数を自動的に作成する
'トランザクションの1セルを選択してFuncVLOOKUPsetTransactionを実行、
'マスターの1セルを選択してFuncVLOOKUPsetMasterを実行すると
'トランザクションにマスターデータを追加するVLOOKUP関数を挿入します。
'* * * * * *
Dim D1 As Range 'トランザクションの中の1セルを選択
Dim D2 As Range 'マスターの中の1セルを選択
'トランザクション選択
Sub FuncVLOOKUPsetTransaction()
'選択セルが表データではない場合エラー
If ActiveCell.CurrentRegion.Count = 1 Then
MsgBox ("トランザクションを選択してください")
End
End If
'アクティブセルをグローバル変数D1に格納
Set D1 = ActiveCell
End Sub
'マスター選択しVLOOKUP関数生成
Sub FuncVLOOKUPsetMaster()
'トランザクションが選択されていなかったらエラー
If D1 Is Nothing Then
MsgBox ("はじめにトランザクションを選択してください")
End
End If
'選択セルが表データではない場合エラー
If ActiveCell.CurrentRegion.Count = 1 Then
MsgBox ("マスターを選択してください")
End
End If
'トランザクションとマスターが同じ範囲ならエラー
If D1.CurrentRegion.Address = ActiveCell.CurrentRegion.Address Then
MsgBox ("範囲が同じです")
End
End If
'アクティブセルをグローバル変数D2に格納
Set D2 = ActiveCell
'トランザクションの四つ角の位置を計算
Dim c1 As Long
Dim c2 As Long
Dim r1 As Long
Dim r2 As Long
r1 = D1.CurrentRegion(1).Row
r2 = r1 + D1.CurrentRegion.Rows.Count - 1
c1 = D1.CurrentRegion(1).Column
c2 = c1 + D1.CurrentRegion.Columns.Count - 1
'トランザクションにキーがなかったらエラー
If WorksheetFunction.CountIf(D1.CurrentRegion.Rows(1), D2.CurrentRegion(1).Value) = 0 Then
MsgBox ("トランザクションにキーが見つかりません")
Set D1 = Nothing
End
End If
'グローバル変数D1をトランザクションのキーのセルに再計算
Set D1 = D1.CurrentRegion(1).Offset(0, _
WorksheetFunction.Match(D2.CurrentRegion(1).Value, D1.CurrentRegion.Rows(1), 0) - 1 _
)
'マスターにあってトランザクションにない項目の抽出
Dim colx1 As Range, colx2 As Range
Dim colx()
Dim i As Long
Dim fl As Boolean
For Each colx2 In D2.CurrentRegion.Rows(1).Columns
fl = True
For Each colx1 In D1.CurrentRegion.Rows(1).Columns
If colx1.Value = colx2.Value Then
fl = False
End If
Next
If fl Then
i = i + 1
ReDim Preserve colx(i)
colx(i) = colx2
End If
Next
'トランザクションに追加する項目数の列を挿入
Range(Columns(c2 + 1), Columns(c2 + i)).Insert
'トランザクションの挿入した列に項目名とVLOOKUP関数を入力
Dim D2adr As String
D2adr = "[" & D2.Parent.Parent.Name & "]" & D2.Parent.Name & "!" _
& D2.CurrentRegion.Address(ReferenceStyle:=xlR1C1)
Dim i2 As Long
Dim keycol As Long
Dim colnum As Long
For i2 = 1 To i
D1.Parent.Cells(r1, c2 + i2).Value = colx(i2)
keycol = D1.Column - (c2 + i2)
colnum = WorksheetFunction.Match(colx(i2), D2.CurrentRegion.Rows(1), 0)
D1.Parent.Cells(r1 + 1, c2 + i2).Formula _
= "=IFERROR(VLOOKUP(RC[" & keycol & "]," & D2adr & "," & colnum & ",FALSE),"""")"
D1.Parent.Cells(r1 + 1, c2 + i2).Copy _
Range( _
D1.Parent.Cells(r1 + 1, c2 + i2), D1.Parent.Cells(r2, c2 + i2) _
)
Next
'グローバル変数D1をリセット
D1.Parent.Parent.Activate
D1.Parent.Select
D1.Parent.Range("A1").Select
Set D1 = Nothing
End Sub
このマクロに前出の記事にある方法でショートカットキーを設定して呼び出すようにするだけです。
この記事で言いたいことは、VBAやマクロって何かの一覧表の管理とか電子カルテとまではいきませんがそのような業務遂行ツールを作るだとか、そのようなイメージが強いかもしれません。確かにそういうものを作れば業務効率化に直接影響するのでそれが魅力とも言えます。
でも、このようなExcelに対する機能追加、カスタマイズという意味でより使いやすくするという目的でも使えるということを伝えたかったのです。
▼実際の動作ファイルはこちら
コメント