回答編集履歴
5
コード微修正
answer
CHANGED
|
@@ -77,7 +77,7 @@
|
|
|
77
77
|
Dim pos As Long
|
|
78
78
|
pos = InStrRev(FilePath, "\")
|
|
79
79
|
If pos > 0 Then
|
|
80
|
-
ConvExtRefer = Left(FilePath, pos
|
|
80
|
+
ConvExtRefer = Left(FilePath, pos) & "[" & Mid(FilePath, pos + 1) & "]"
|
|
81
81
|
End If
|
|
82
82
|
End Function
|
|
83
83
|
```
|
4
微修正
answer
CHANGED
|
@@ -81,7 +81,7 @@
|
|
|
81
81
|
End If
|
|
82
82
|
End Function
|
|
83
83
|
```
|
|
84
|
-
|
|
84
|
+
---
|
|
85
85
|
**コードの解説を少ししておきます。**
|
|
86
86
|
まず、ワークシート関数の意味を理解しておきましょう。
|
|
87
87
|
MATCHを使って一致する位置を検索する。一致する値がないとエラーを返す。
|
|
@@ -101,4 +101,4 @@
|
|
|
101
101
|
シート上のセルをループで一つずつ参照する必要がない。
|
|
102
102
|
ということです。この２つは重くなる二大原因ですので、これを回避できることで格段の高速化が可能になります。
|
|
103
103
|
|
|
104
|
-
|
|
104
|
+
ちみみに、セル結合があっても、MATCHでの検索は問題ないです。
|
3
説明追記
answer
CHANGED
|
@@ -45,7 +45,7 @@
|
|
|
45
45
|
|
|
46
46
|
Sub hikaku3()
|
|
47
47
|
Const conFormula = _
|
|
48
|
-
"=IF(I1=""一致"",""一致"",IF(
|
|
48
|
+
"=IF(I1=""一致"",""一致"",IF(ISNUMBER(MATCH(G1,{外部参照}説明!CC$1:CC$1000,0)),""不一致"",""一致""))"
|
|
49
49
|
|
|
50
50
|
Books(0) = "C:\test\book0.xlsx"
|
|
51
51
|
Books(1) = "C:\test\book1.xlsx"
|
|
@@ -82,7 +82,7 @@
|
|
|
82
82
|
End Function
|
|
83
83
|
```
|
|
84
84
|
|
|
85
|
-
コードの解説を少ししておきます。
|
|
85
|
+
**コードの解説を少ししておきます。**
|
|
86
86
|
まず、ワークシート関数の意味を理解しておきましょう。
|
|
87
87
|
MATCHを使って一致する位置を検索する。一致する値がないとエラーを返す。
|
|
88
88
|
IFERRORでエラーを-1に変換。
|
|
@@ -99,4 +99,6 @@
|
|
|
99
99
|
このように、式を使ってデータを参照する方法のメリットは、
|
|
100
100
|
ブックを一つずつ開く必要がない。
|
|
101
101
|
シート上のセルをループで一つずつ参照する必要がない。
|
|
102
|
-
ということです。この２つは重くなる二大原因ですので、これを回避できることで格段の高速化が可能になります。
|
|
102
|
+
ということです。この２つは重くなる二大原因ですので、これを回避できることで格段の高速化が可能になります。
|
|
103
|
+
|
|
104
|
+
---
|
2
説明追記
answer
CHANGED
|
@@ -62,7 +62,7 @@
|
|
|
62
62
|
f = Replace(conFormula, "{外部参照}", ConvExtRefer(b))
|
|
63
63
|
.Offset(, 1).Formula = f
|
|
64
64
|
.Offset(, 1).Value = .Offset(, 1).Value
|
|
65
|
-
.Offset(, 2).Value = .Offset(, 1).Value 'I列を
|
|
65
|
+
.Offset(, 2).Value = .Offset(, 1).Value 'I列に前の検索結果を保存
|
|
66
66
|
Next
|
|
67
67
|
.Offset(, 2).Clear
|
|
68
68
|
End With
|
|
@@ -80,6 +80,23 @@
|
|
|
80
80
|
ConvExtRefer = Left(FilePath, pos - 1) & "[" & Mid(FilePath, pos + 1) & "]"
|
|
81
81
|
End If
|
|
82
82
|
End Function
|
|
83
|
+
```
|
|
83
84
|
|
|
85
|
+
コードの解説を少ししておきます。
|
|
86
|
+
まず、ワークシート関数の意味を理解しておきましょう。
|
|
87
|
+
MATCHを使って一致する位置を検索する。一致する値がないとエラーを返す。
|
|
88
|
+
IFERRORでエラーを-1に変換。
|
|
89
|
+
IF関数でｰ1を"不一致"それいがいを"一致"に変換。
|
|
90
|
+
このようにワークシート関数を組み合わせて、検索結果から"不一致"、"一致"を返す式を作成します。
|
|
84
91
|
|
|
92
|
+
VBAでセル範囲に式を設定することは、
|
|
93
|
+
ユーザーがセル範囲の先頭セルに式を設定してから、セル範囲最後までフィルハンドルをドラッグして式を自動入力することと同じ意味になります(オートフィル機能)。
|
|
94
|
+
|
|
95
|
+
式を設定して取得した値（一致、不一致）は、Valueで上書きすることで、式が値に変換されます。
|
|
96
|
+
|
|
97
|
+
あとは、{外部参照}の部分を、実際のファイルパスを外部参照式に変換したもので置き換えて、複数ファイルを参照できるようにしてます。
|
|
98
|
+
|
|
99
|
+
このように、式を使ってデータを参照する方法のメリットは、
|
|
85
|
-
|
|
100
|
+
ブックを一つずつ開く必要がない。
|
|
101
|
+
シート上のセルをループで一つずつ参照する必要がない。
|
|
102
|
+
ということです。この２つは重くなる二大原因ですので、これを回避できることで格段の高速化が可能になります。
|
1
コード追記
answer
CHANGED
|
@@ -18,7 +18,7 @@
|
|
|
18
18
|
Sub hikaku1()
|
|
19
19
|
Const conFormula = "=IF(IFERROR(MATCH(G1,パス[ブック名]説明!CC:CC,0),-1)=-1,""不一致"",""一致"")"
|
|
20
20
|
|
|
21
|
-
Dim Path As
|
|
21
|
+
Dim Path As string
|
|
22
22
|
Path = "C:\test\"
|
|
23
23
|
Dim FileName As String
|
|
24
24
|
FileName = "book.xlsx"
|
|
@@ -35,4 +35,51 @@
|
|
|
35
35
|
```
|
|
36
36
|
|
|
37
37
|
とりあえず一つのファイルと比較する場合のコードです。
|
|
38
|
-
これができることを確認できたら、ループで複数のファイルと比較するように修正していきましょう。
|
|
38
|
+
これができることを確認できたら、ループで複数のファイルと比較するように修正していきましょう。
|
|
39
|
+
|
|
40
|
+
---
|
|
41
|
+
配列に格納された複数ブックを検索して一致する値があるかチェックする場合のコード例
|
|
42
|
+
|
|
43
|
+
```vba
|
|
44
|
+
Dim Books(200) As String 'ファイルのパスとブック名(book.xlsxなど)が合わさったものを格納
|
|
45
|
+
|
|
46
|
+
Sub hikaku3()
|
|
47
|
+
Const conFormula = _
|
|
48
|
+
"=IF(I1=""一致"",""一致"",IF(IFERROR(MATCH(G1,{外部参照}説明!C$1:C$1000,0),-1)=-1,""不一致"",""一致""))"
|
|
49
|
+
|
|
50
|
+
Books(0) = "C:\test\book0.xlsx"
|
|
51
|
+
Books(1) = "C:\test\book1.xlsx"
|
|
52
|
+
'・・・中略
|
|
53
|
+
Books(200) = "C:\test\book200.xlsx"
|
|
54
|
+
|
|
55
|
+
Application.ScreenUpdating = False
|
|
56
|
+
Application.Calculation = xlCalculationManual
|
|
57
|
+
|
|
58
|
+
With ThisWorkbook.Worksheets("イベント").Range("G1").CurrentRegion.Columns(1)
|
|
59
|
+
Dim b
|
|
60
|
+
For Each b In Books
|
|
61
|
+
Dim f As String
|
|
62
|
+
f = Replace(conFormula, "{外部参照}", ConvExtRefer(b))
|
|
63
|
+
.Offset(, 1).Formula = f
|
|
64
|
+
.Offset(, 1).Value = .Offset(, 1).Value
|
|
65
|
+
.Offset(, 2).Value = .Offset(, 1).Value 'I列を作業列として使用
|
|
66
|
+
Next
|
|
67
|
+
.Offset(, 2).Clear
|
|
68
|
+
End With
|
|
69
|
+
|
|
70
|
+
Application.Calculation = xlCalculationManual
|
|
71
|
+
Application.ScreenUpdating = True
|
|
72
|
+
End Sub
|
|
73
|
+
|
|
74
|
+
'ファイルパスを外部参照式に変換
|
|
75
|
+
'C:\test\book1.xlsx → C:\test[book1.xlsx]
|
|
76
|
+
Public Function ConvExtRefer(FilePath As Variant) As String
|
|
77
|
+
Dim pos As Long
|
|
78
|
+
pos = InStrRev(FilePath, "\")
|
|
79
|
+
If pos > 0 Then
|
|
80
|
+
ConvExtRefer = Left(FilePath, pos - 1) & "[" & Mid(FilePath, pos + 1) & "]"
|
|
81
|
+
End If
|
|
82
|
+
End Function
|
|
83
|
+
|
|
84
|
+
|
|
85
|
+
```
|