やりたいこと
1万件以上のデータから特定の条件に一致するレコードだけを抽出し、別シートに表示したい。
例：下記住所マスタをもとに、
↓あいまい検索の結果を別シートに表示したい。
まずは結論から
できた。
上のGIFはデータが14万件、表示領域1万件で実際に動かしたものになる。
ポイント
- 全件チェックするような関数を極力なくす
- 並び順を気にしない。
- VLOOKUPの近似一致が大活躍
全件チェックを減らそう
いろんなサイトを拝見し参考にしたが基本的な考え方はどれも同じで
- 検索に引っかかるレコードに印をつけ、
- 印をもとにデータをVLOOKUPやINDEX+MATCHで抽出する。
というもの。
自分と同じようなことをしたい方がたくさんいてサンプルは十分だが問題が。
データ量が多くなると処理が重くなる。
10万行を超えるとそれが顕著になる。EXCELが応答しなくなることも。
データ件数によって計算量が増えるような関数を使っているっぽい。
例えば、
- レコードに印をつける時にCOUNTIFやCOUNTを使っている。
- 抽出する順番を重視するためにSMAALやLARGEを使っている。
- 配列数式を使っている。
とか
速くするための方針
全件チェックするような関数を極力使わなければ速くなるんじゃね？
実装した中身
以下、理解しやすいよう式を構造化参照や名前定義で記述する。
【データ側】検索に引っかかるレコードをROWで採番しながら印をつける
抽出時の印となるPkeyを住所マスタに追加し、検索の表示対象になる場合はROWで採番する。逆に対象外の場合は空欄""。
=IF(ISERROR(FIND(検索条件_市町村,[@市区町村])),"",ROW())
【表示側】印をVLOOKUPの近似一致で見つけ出す。降順になるが気にしてはいけない。
ここが一番のポイント。データには上と下なんかないんだから並び順は気にせずまずは印を集める。
それから好きなように並べる。
印さえ集めればVLOOKUPなりINDEX+MATCHなりで情報を収集すれば終わり。
以下、各項目の式とポイント
式
| 項目名 | 式 | ポイント |
|---|---|---|
| 抽出件数(WORK項目) | =COUNT(住所マスタ[Pkey]) | 表示する件数を計算しておく。抽出結果が表より大きくなりそうならROWS(検索結果[#データ])とMINを取っておく。 |
| 最終行(WORK項目) | =ROW(住所マスタ[#見出し])+ROWS(住所マスタ[#データ]) | 住所マスタの最終行を取得しておく。Pkeyの最初の検索キーになる。 |
| No | =ROW()-ROW(検索結果[#見出し]) | 無くてもよい。「Pkey降順」に埋め込んでしまえば不要。 1から採番できてればどんな式でもよい、ROW(A1)とかもいける。 |
| Pkey降順 | 1行目**=VLOOKUP(最終行,住所マスタ[Pkey],1,TRUE)** n行目以降**=VLOOKUP((n-1行のPkey) - 1,住所マスタ[Pkey],1,TRUE)** |
住所マスタのPkeyが昇順で並んでいるためVLOOKUPの近似一致、いわゆるTRUEが使える。 1行目で検索結果の最終レコードを引っ張ってこれる。 2行目以降の検索キーは「1行上のPkeyから1を引いた値」にする。これでPkeyが降順に並ぶ。 ※1行目の検索キーを「最終行」ではなく9999999のような大きな数字にしておけば実は最終行は要らない。 |
| 行 | =MATCH([@Pkey降順],住所マスタ[Pkey],1) | Pkeyが住所マスタの何行目にあるか計算しておく。この値、以下の項目でたくさん使う。 |
| 市町村 | =INDEX(住所マスタ[市区町村],[@行]) | 行の値をもとに情報を集める。 |
| 町域 | =INDEX(住所マスタ[町域],[@行]) | 同様。 |
| 字丁目 | =INDEX(住所マスタ[字丁目],[@行]) | 同様。 |
| ※本質的な部分だけを表記したため、エラー回避は省略している。 |
どうしても昇順で表示したいのならば、Pkey昇順列を追加してINDEX([Pkey(降順)],抽出件数-[@No]+1)とか入れれば反対に並ぶ。
もしくはデータ自体を逆順に並べておけばよい。
これホントに速いの？
雑に計測。
COUNTやSMALLを使ったやり方と比較してみた。
抽出結果最大1万件で選択率10%として計測。
| データ件数 | データ側でCOUNTを使用した場合 | 表示側でSMALLを使用した場合 | 今回のやり方 |
|---|---|---|---|
| 1万 | 0.32秒 | 0.25秒 | 0.23秒 |
| 5万 | 4.66秒 | 2.44秒 | 0.71秒 |
| 10万 | 計測不能 | 21.70秒 | 0.92秒～2.84秒 |
データの偏りによって時間がかかったりかからなかったり。
体感はもっと速い気がする。
どこまで使えるか。
100万件のデータ、100万件の表示領域、選択率3%と20%で計測してみた。
| 選択率 | 結果 |
|---|---|
| 3% | 31.55秒 |
| 20% | 403.50秒 |
処理が終わったことに驚き。
というか、100万件のデータをエクセルでやらせてはいけない。
しめ
もう少し検証したい。
結果によっては実運用でバリバリ使っていきたい。