エクセルvlookup関数で値を取得したいリストの行数が多すぎてエクセ

質問

エクセルvlookup関数で値を取得したいリストの行数が多すぎてエクセルで表示できない
単純にa列をキーにしてb列の値を取得したいですがリストの行数が65＊＊＊行以上でエクセルで開くと欠落します。リストはcsvでオープンしないで値を取得することはできますか？
宜しくお願いいたします。

回答No.2

mitarashi · ベストアンサー

面白半分でユーザー定義関数を作成してみました。
A1に検索する値があるとして、B1に次の様に入れます。頭の1は検索する値がcsvの何列目かを示し、末尾の2は引用する列番です。
=extVlookup(1, A1, "C:\sample.csv", 2)
10万行のcsvで試験してみましたが、予想通り遅くて実用的ではないです。
'Microsoft ActiveX Data Object 2.x Libraryに参照設定要
'Schema.iniを同じフォルダーに作成しないと「抽出条件でデータ型不一致」のエラーになる事がある
'Schema.iniの内容例
'[sample.csv]
'ColNameHeader = False
'CharacterSet = OEM
'Format = CSVDelimited
'Col1=F1 Char Width 255
'Col2=F2 Char Width 255
Function extVlookup(searchColNo As Long, searchVal As Variant, fileFullPath As String, refColNo As Long) As Variant
Dim filePath As String, fileName As String
Dim strSQL As String
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim lastBackSlashPos As Long
Dim errFlag As Boolean

fileFullPath = UCase(fileFullPath)
If Right(fileFullPath, 4) <> ".CSV" Then
extVlookup = CVErr(xlErrValue)
GoTo errorHandle
End If
Set CN = New ADODB.Connection
lastBackSlashPos = InStrRev(fileFullPath, "\")
fileName = Mid(fileFullPath, lastBackSlashPos + 1, Len(fileFullPath) - lastBackSlashPos)
filePath = Left(fileFullPath, lastBackSlashPos)
CN.Provider = "Microsoft.Jet.OLEDB.4.0"
'見出し行を対象としない
CN.Properties("Extended Properties") = "Text;HDR=NO"
CN.ConnectionString = filePath
CN.Open
'Schema.iniと矛盾するとNG
If IsNumeric(searchVal) Then
strSQL = "SELECT * FROM " & fileName & _
" WHERE [" & Replace(fileName, ".CSV", "#CSV") & "].F" & CStr(searchColNo) & _
"=" & searchVal & ";"
Else
strSQL = "SELECT * FROM " & fileName & _
" WHERE [" & Replace(fileName, ".CSV", "#CSV") & "].F" & CStr(searchColNo) & _
"='" & searchVal & "'"
End If
Set RS = New ADODB.Recordset
RS.CursorLocation = adUseClient
RS.Open strSQL, CN, adOpenStatic, adLockOptimistic
If RS.RecordCount <> 1 Then
extVlookup = CVErr(xlErrValue)
GoTo errorHandle
End If
extVlookup = RS.Fields(refColNo - 1)
errorHandle:
If RS.State = 1 Then RS.Close
Set RS = Nothing
If CN.State = 1 Then CN.Close
Set CN = Nothing
End Function

回答No.1

KURUMITO

MATCH関数やCOUNTIF関数などを作業列に入力しておき、その列での値を利用してＢ列のデータを表示させるようにしてはいかがでしょう。

お礼

リストの行数は141千行ありました
リスト分割すれば何とかなりますが
1回でできる方法があれば・・・