回答編集履歴
5
コード微修正
test
CHANGED
|
@@ -156,7 +156,7 @@
|
|
|
156
156
|
|
|
157
157
|
If pos > 0 Then
|
|
158
158
|
|
|
159
|
-
ConvExtRefer = Left(FilePath, pos
|
|
159
|
+
ConvExtRefer = Left(FilePath, pos) & "[" & Mid(FilePath, pos + 1) & "]"
|
|
160
160
|
|
|
161
161
|
End If
|
|
162
162
|
|
4
微修正
test
CHANGED
|
@@ -164,7 +164,7 @@
|
|
|
164
164
|
|
|
165
165
|
```
|
|
166
166
|
|
|
167
|
-
|
|
167
|
+
---
|
|
168
168
|
|
|
169
169
|
**コードの解説を少ししておきます。**
|
|
170
170
|
|
|
@@ -204,4 +204,4 @@
|
|
|
204
204
|
|
|
205
205
|
|
|
206
206
|
|
|
207
|
-
|
|
207
|
+
ちみみに、セル結合があっても、MATCHでの検索は問題ないです。
|
3
説明追記
test
CHANGED
|
@@ -92,7 +92,7 @@
|
|
|
92
92
|
|
|
93
93
|
Const conFormula = _
|
|
94
94
|
|
|
95
|
-
"=IF(I1=""一致"",""一致"",IF(I
|
|
95
|
+
"=IF(I1=""一致"",""一致"",IF(ISNUMBER(MATCH(G1,{外部参照}説明!CC$1:CC$1000,0)),""不一致"",""一致""))"
|
|
96
96
|
|
|
97
97
|
|
|
98
98
|
|
|
@@ -166,7 +166,7 @@
|
|
|
166
166
|
|
|
167
167
|
|
|
168
168
|
|
|
169
|
-
コードの解説を少ししておきます。
|
|
169
|
+
**コードの解説を少ししておきます。**
|
|
170
170
|
|
|
171
171
|
まず、ワークシート関数の意味を理解しておきましょう。
|
|
172
172
|
|
|
@@ -201,3 +201,7 @@
|
|
|
201
201
|
シート上のセルをループで一つずつ参照する必要がない。
|
|
202
202
|
|
|
203
203
|
ということです。この２つは重くなる二大原因ですので、これを回避できることで格段の高速化が可能になります。
|
|
204
|
+
|
|
205
|
+
|
|
206
|
+
|
|
207
|
+
---
|
2
説明追記
test
CHANGED
|
@@ -126,7 +126,7 @@
|
|
|
126
126
|
|
|
127
127
|
.Offset(, 1).Value = .Offset(, 1).Value
|
|
128
128
|
|
|
129
|
-
.Offset(, 2).Value = .Offset(, 1).Value 'I列を
|
|
129
|
+
.Offset(, 2).Value = .Offset(, 1).Value 'I列に前の検索結果を保存
|
|
130
130
|
|
|
131
131
|
Next
|
|
132
132
|
|
|
@@ -162,8 +162,42 @@
|
|
|
162
162
|
|
|
163
163
|
End Function
|
|
164
164
|
|
|
165
|
-
|
|
166
|
-
|
|
167
|
-
|
|
168
|
-
|
|
169
165
|
```
|
|
166
|
+
|
|
167
|
+
|
|
168
|
+
|
|
169
|
+
コードの解説を少ししておきます。
|
|
170
|
+
|
|
171
|
+
まず、ワークシート関数の意味を理解しておきましょう。
|
|
172
|
+
|
|
173
|
+
MATCHを使って一致する位置を検索する。一致する値がないとエラーを返す。
|
|
174
|
+
|
|
175
|
+
IFERRORでエラーを-1に変換。
|
|
176
|
+
|
|
177
|
+
IF関数でｰ1を"不一致"それいがいを"一致"に変換。
|
|
178
|
+
|
|
179
|
+
このようにワークシート関数を組み合わせて、検索結果から"不一致"、"一致"を返す式を作成します。
|
|
180
|
+
|
|
181
|
+
|
|
182
|
+
|
|
183
|
+
VBAでセル範囲に式を設定することは、
|
|
184
|
+
|
|
185
|
+
ユーザーがセル範囲の先頭セルに式を設定してから、セル範囲最後までフィルハンドルをドラッグして式を自動入力することと同じ意味になります(オートフィル機能)。
|
|
186
|
+
|
|
187
|
+
|
|
188
|
+
|
|
189
|
+
式を設定して取得した値（一致、不一致）は、Valueで上書きすることで、式が値に変換されます。
|
|
190
|
+
|
|
191
|
+
|
|
192
|
+
|
|
193
|
+
あとは、{外部参照}の部分を、実際のファイルパスを外部参照式に変換したもので置き換えて、複数ファイルを参照できるようにしてます。
|
|
194
|
+
|
|
195
|
+
|
|
196
|
+
|
|
197
|
+
このように、式を使ってデータを参照する方法のメリットは、
|
|
198
|
+
|
|
199
|
+
ブックを一つずつ開く必要がない。
|
|
200
|
+
|
|
201
|
+
シート上のセルをループで一つずつ参照する必要がない。
|
|
202
|
+
|
|
203
|
+
ということです。この２つは重くなる二大原因ですので、これを回避できることで格段の高速化が可能になります。
|
1
コード追記
test
CHANGED
|
@@ -38,7 +38,7 @@
|
|
|
38
38
|
|
|
39
39
|
|
|
40
40
|
|
|
41
|
-
Dim Path As sring
|
|
41
|
+
Dim Path As string
|
|
42
42
|
|
|
43
43
|
Path = "C:\test\"
|
|
44
44
|
|
|
@@ -73,3 +73,97 @@
|
|
|
73
73
|
とりあえず一つのファイルと比較する場合のコードです。
|
|
74
74
|
|
|
75
75
|
これができることを確認できたら、ループで複数のファイルと比較するように修正していきましょう。
|
|
76
|
+
|
|
77
|
+
|
|
78
|
+
|
|
79
|
+
---
|
|
80
|
+
|
|
81
|
+
配列に格納された複数ブックを検索して一致する値があるかチェックする場合のコード例
|
|
82
|
+
|
|
83
|
+
|
|
84
|
+
|
|
85
|
+
```vba
|
|
86
|
+
|
|
87
|
+
Dim Books(200) As String 'ファイルのパスとブック名(book.xlsxなど)が合わさったものを格納
|
|
88
|
+
|
|
89
|
+
|
|
90
|
+
|
|
91
|
+
Sub hikaku3()
|
|
92
|
+
|
|
93
|
+
Const conFormula = _
|
|
94
|
+
|
|
95
|
+
"=IF(I1=""一致"",""一致"",IF(IFERROR(MATCH(G1,{外部参照}説明!C$1:C$1000,0),-1)=-1,""不一致"",""一致""))"
|
|
96
|
+
|
|
97
|
+
|
|
98
|
+
|
|
99
|
+
Books(0) = "C:\test\book0.xlsx"
|
|
100
|
+
|
|
101
|
+
Books(1) = "C:\test\book1.xlsx"
|
|
102
|
+
|
|
103
|
+
'・・・中略
|
|
104
|
+
|
|
105
|
+
Books(200) = "C:\test\book200.xlsx"
|
|
106
|
+
|
|
107
|
+
|
|
108
|
+
|
|
109
|
+
Application.ScreenUpdating = False
|
|
110
|
+
|
|
111
|
+
Application.Calculation = xlCalculationManual
|
|
112
|
+
|
|
113
|
+
|
|
114
|
+
|
|
115
|
+
With ThisWorkbook.Worksheets("イベント").Range("G1").CurrentRegion.Columns(1)
|
|
116
|
+
|
|
117
|
+
Dim b
|
|
118
|
+
|
|
119
|
+
For Each b In Books
|
|
120
|
+
|
|
121
|
+
Dim f As String
|
|
122
|
+
|
|
123
|
+
f = Replace(conFormula, "{外部参照}", ConvExtRefer(b))
|
|
124
|
+
|
|
125
|
+
.Offset(, 1).Formula = f
|
|
126
|
+
|
|
127
|
+
.Offset(, 1).Value = .Offset(, 1).Value
|
|
128
|
+
|
|
129
|
+
.Offset(, 2).Value = .Offset(, 1).Value 'I列を作業列として使用
|
|
130
|
+
|
|
131
|
+
Next
|
|
132
|
+
|
|
133
|
+
.Offset(, 2).Clear
|
|
134
|
+
|
|
135
|
+
End With
|
|
136
|
+
|
|
137
|
+
|
|
138
|
+
|
|
139
|
+
Application.Calculation = xlCalculationManual
|
|
140
|
+
|
|
141
|
+
Application.ScreenUpdating = True
|
|
142
|
+
|
|
143
|
+
End Sub
|
|
144
|
+
|
|
145
|
+
|
|
146
|
+
|
|
147
|
+
'ファイルパスを外部参照式に変換
|
|
148
|
+
|
|
149
|
+
'C:\test\book1.xlsx → C:\test[book1.xlsx]
|
|
150
|
+
|
|
151
|
+
Public Function ConvExtRefer(FilePath As Variant) As String
|
|
152
|
+
|
|
153
|
+
Dim pos As Long
|
|
154
|
+
|
|
155
|
+
pos = InStrRev(FilePath, "\")
|
|
156
|
+
|
|
157
|
+
If pos > 0 Then
|
|
158
|
+
|
|
159
|
+
ConvExtRefer = Left(FilePath, pos - 1) & "[" & Mid(FilePath, pos + 1) & "]"
|
|
160
|
+
|
|
161
|
+
End If
|
|
162
|
+
|
|
163
|
+
End Function
|
|
164
|
+
|
|
165
|
+
|
|
166
|
+
|
|
167
|
+
|
|
168
|
+
|
|
169
|
+
```
|