目次
EXCELで大きい（１０万行以上）データのシートを扱う際に注意すること
ひさびさに10万行以上のビックサイズのデータをEXCELで加工する必要があり、結構はまったので、備忘のために「注意しなきゃ」と思ったことを記録しておきます。
① 計算設定を「手動」にすること
通常は計算設定「自動」なので計算式を入力すると、すぐ計算結果が反映されます。
便利なのですが、１０万行以上もあると、ちょっと入力する都度、再計算のために作業がとまります。作業をすすめるほど、ひどくなり、最後は「応答なし」になってしまうので、最初にするべきはこれです。
計算設定は「手動」で、計算式を入力して範囲を選択して「再計算」・・が基本です。
② こまめに上書き保存すること
１０万行以上あると、ちょっと油断すると「応答なし」になります。
そうなるとかなりの確率でEXCELを強制終了せざるをえない状況に陥ります。
僕も最初のうち、何度も１時間分くらいの作業結果を失いました。
計算式を入力→再計算実行→結果表示→上書き保存・・というサイクルで、ひとつひとつ作業が終わる都度、上書き保存するくらいでちょうどいいです。
③ 自動保存はOFFにすること
こまめな保存は手動でやります。自動保存はだめです。
自動保存は「こまめな保存」にはとても便利ですが、１０万行以上もあるようなブックでの作業中に自動保存が走ると、それが原因で応答なしになります。
自動保存するときには再計算も走るので「手動」計算にする意味がありません。
④ こまめに値のみ貼付けをすること
計算式が設定されたセルが多くなってくると、どんどん重たくなります。
なので、大きなデータでやるときは値が確定した計算式は、どんどんコピー→値のみ貼付けで計算式をなくしていくほうがいいです。
ここで注意すべきなのは「こまめ」にやることです。
ある程度、そのまますすめて、重たくなってからやろうとするとコピー→値のみ貼付けの時に応答なしになって、そのまま戻ってこない＝EXCEL強制終了を余儀なくされる・・パターンで何度かはまりました(´;ω;｀)。
⑤ 重たい計算式の修正は貼りなおす前に保存すること
数万行のデータであっても「VLOOKUP」・「COUNTIF」・「SUMIF」・「INDEX」・「MATCH」などの関数は使わないわけにはいきません。
これらは総じて「重たい」ですが、大きなデータであっても使えます。
ただ、貼り直しの時は注意が必要です。
たとえば、数万行のセルにVLOOKUPをセットした後で絶対参照にしてなかったのを思い出して、修正してコピー・貼付けで修正する・・なんてやると、応答なしになってEXCEL強制終了せざるをえなくなったりします。
数万行のセルの重たい計算式の修正時は一度上書き保存をはさんだほうが良いです。
そうせずに、安易に修正→コピー→セル選択→貼付けをしただけで固まるリスクがあって、僕も実際何度も泣きました。
⑥ フィルターを安易に使わないこと
数万行になると工夫せずにフィルターを使うと、選択肢の表示限界を超えて「すべて表示できていません」になったり、絞り込みが恐ろしく遅くなったりもします。
なので、例えば「IF」なんかを使って条件によって「A,B,C」みたいなグループ分けしてその列でフィルターをかけたり、列を選択して「検索」して目指す行にとぶ・・みたいなやり方をするなどの工夫が必要になります。
まとめ
上記はあくまで僕の会社のPCでEXCEL２０１６を使った場合・・の経験で書いてます。
会社のPCは、1コアの3201MHzのCPUでメモリ４Gという、最近からするとプアなスペックです。
それでも、数千行くらいだと、ルーズに計算式をガンガン組んでても大丈夫なのですが、１０万行くらいだと、なにげない操作・・関数のコピー・貼付けとかフィルターとか・・で、突然応答なしになって画面が真っ白になり、泣く泣くＥＸＣＥＬを強制終了してやり直しする羽目になり、コツをつかんでスムーズに作業できるようになるまでに、かなりストレスがありました。
それでも喉元すぎれば忘れそうなので、こんなのを書いておくことにしました。
自分用ですけど、だれかの役にたつかもしれませんしね。
