EXCELから効率よくデータ抽出したい

質問

EXCELのデータが20万行あります。

この中から、データを抽出したいのです。1個なら検索で良いかと思うのですが、20個とか30個とかの場合は、どうすれば効率が良いのでしょうか？

今やっているのは、こんな感じです。

EXCELの20万行のデータを(1)とします。
抽出したいデータを(2)とします。
(1)には重複不可のキーとなるIDがあります。(2)には調べたいIDが入っています。

(1)のとなりに(2)を貼り付け、IDを頼りに(1)から(2)のデータをvlookupします。
そこで#n/aとならなかったデータが対象・・・としています。

ただ、(2)のデータが全て(1)に含まれているわけではなく、含まれていないものは他のテーブルから抽出します。（(1)のテーブルは20個ほどあります）

そのため、今度は(2)のIDをキーにvlookupをして、(1)から抽出されなかったデータを絞っています。

また、(2)に入っているIDに無駄な空白があったり・・・とデータの整形もあります。

現在、データが重い上にかなり手間がかかっています。ACCESSのクエリーを使えば早いのでしょうが、EXCELのデータで各カラムにいろいろなデータが入っているために（フィールドが整理されていない）カンタンに移行できそうもありません。

やっぱり、ここは何とかAccessへ移行させるべきでしょうか？
20万行のファイルを20個1つのAccessにすればやはり重くなりますでしょうか？

すみません。よろしくお願いします。

回答No.6

chayamati

ACCESSへの移行ですね
添付は社員のデータファイルのACCESSモデルです。
フィールドを定義し、リレーションシップを設定しただけで
テーブルを開くと社員毎の家族構成がリストされるし、このままデータ入力も可能になります。
T-家族の住所、電話番号はT-社員を参照できるので不要です。

自分は趣味で始めたので最大数千件レコードしか扱っていませんので
20万行×２０＝400万行ですね何とも言えませんが
EXCELより多くのレコードを処理できることは確かです。

移行手順は
１．（２）のIDがユニーク（重複なし）が前提です。
．．IDに空白が入っているセルにはユニークなものを記述しておきます。
．．IDが列見出しならコード等に変更しておきます。
．．Accessで（２）をインポートします。主キーはお任せにします。
．．改名したIDに重複なしの設定にします。
．．重複があると設定できませんのでこれを解消して再挑戦

２．（1）のテーブルのうち代表的な行を1行インポートします。
．．IDはコード親とでも改名します
．．コードとコード親でリレーションシップを定義します。
．．残りの行をインポートします．．．コードの見つからない行はインポートできません
できなかった行はリストされます。
，，IDを除いて（２）と同じ（１）のフィールドを内容照合の上除去します

回答No.5

aoyama984

http://www.moug.net/tech/exvba/0050116.html
http://officetanaka.net/excel/vba/speed/s11.htm
http://tuka.s12.xrea.com/index.xcg?p=ADO#p13
(2)に入っているIDに無駄な空白があったり・・・とデータの整形
これは具体的にはどういうものなのでしょうか
手作業の準備レベルの話だと思いますが
照合作業に何らかの考慮が必要なのでしょうか
完全一致のIDの検索　ということでいいのでしょうか

回答No.4

o_chi_chi

下記のようなマクロで抽出できます。

マクロがわからなければスルーしてください。
---
Sub mTyusyutsu()
Dim strSql As String
Dim cnXL As Object
Dim rsXL As Object

Set cnXL = CreateObject("ADODB.Connection")
Set rsXL = CreateObject("ADODB.Recordset")

With cnXL
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & ThisWorkbook.FullName & "; ReadOnly=True;"
.Open
End With

strSql = "select a.ID " _
& " FROM [Sheet1$A1:A100] a " _
& " INNER JOIN [Sheet1$B1:B20] b " _
& " ON a.ID = b.ID"
Debug.Print strSql
rsXL.Open strSql, cnXL, adOpenForwardOnly

Worksheets("sheet2").Cells(1, 1).CopyFromRecordset rsXL

rsXL.Close: Set rsXL = Nothing
cnXL.Close: Set cnXL = Nothing

MsgBox "出力終了！"
End Sub

回答No.3

idaroi

詳しい要求仕様がわからないのでなんとも言えませんが、代替案としては、

・excel関数でif関数の併用
・マクロを書く
・データベースの併用

などの方法が考えられると思います。

データベースですが、MySQLなど、フリーでもかなり性能の良いものがありますし、マイクロソフトのSQLServerも、フリー版があったかと。
全部のカラムをデータベースに移行することが唯一の解ではなく、例えばidと行番号の対応だけをデータベースに入れて、得られた行番号から、データの本体はExcelの方から転記するとか、Excelのカラム値を全部文字列としてフリー情報としてデータベースに、まるまる突っ込むとか、方法は色々あると思います。

回答No.2

FEX2053

Accessで20万行だと、それほど多いデータ量では無いです。
抽出もほとんど一瞬で済むので、それだけの量だとAccessに
移行した方が良いでしょうね。

ただ、Excelでもデータベース機能はあります。Vlookupは処理
が決して速くないのですが、データベース機能は処理速度が
比較的速いので、まずはExcelのままデータベースが使えない
か、チェックしてみた方が良いです。調べれば分かりますが、
テーブルが１つだけなら、Accessに劣らない機能があります。
http://www.becoolusers.com/excel/excel-database.html

Excelのデータベースは、セル内のデータの整合性（文字と数字
が混在していたり、空白があったり）に余り影響されずに動作
しますので、その面でもAccessよりは楽ですよ。

回答No.1

aokii

何とかAccessへ移行させるべきでしょう。
20万行のファイルを20個1つのAccessにしても重くはなりません。
エクセルでは重くなります。

お礼

やっぱりエクセルだと無理がある段階に来ていますよね～
古いエクセルのバージョンの時に行数が多くなったんで
アクセスにしようと思ったんですが、
その後、エクセルのバージョンアップでどうにかなるようになったんで
そのままにしちゃってたんですよね。

検討したいと思います。
ありがとうございました。