1つ入力するたびに数分止まり、最後は1時間「応答なし」
月に一度、全店舗・全商品の販売数を修正するブックを使っていました。元になるのは200店舗×5,000品目×12か月分の販売データで、集計表は5,000行×200列。そのほぼすべてのセルに、VLOOKUPとSUMIFSの数式が入っていました。数にすると約100万セルです。
使い始めのころは我慢できる重さでしたが、データが積み上がるにつれて様子が変わりました。セルに数字を1つ入れると、画面の下に計算中の表示が出て数分待たされる。保存しても数分。ある月はとうとう、1時間待ってもタイトルバーに「応答なし」と出たまま戻ってきませんでした。
計算方法を「手動」に切り替えてみましたが、それでは解決しませんでした。最終的にやったのは、1つのブックで全部を計算するのをやめて、データ整形用・集計用・報告用の3つのブックに分けることです。丸1日かかっていた作業が、約3時間で終わるようになりました。
この記事では、ブックが重いときに原因が数式の量かどうかを見分ける方法、手動計算では足りなかった理由、ブックを工程ごとに分けて値で受け渡すやり方、分けたあとに必要になる手順書の作り方を、筆者の例に沿って説明します。
まず、重さの原因が数式かどうかを見分ける
Excelが重くなる原因は数式だけではありません。書式や図形が原因のこともあります。数式を減らす作業は手間がかかるので、先に「本当に数式のせいか」を確かめます。
画面の下に「計算中」が出ているか
入力したあとに固まるとき、ステータスバー（画面のいちばん下）の左側を見てください。「計算中（4 プロセッサ）: 35%」のような表示が出ていれば、待たされているのは再計算です。表示が出ないまま固まるなら、数式以外の原因を疑います。
数式の入ったセルを数える
ブックに数式がどれくらいあるかは、次の操作で数えられます。
- 数えたいシートを開き、どこか1つのセルを選ぶ
- Ctrl+G →［セル選択］をクリック
- ［数式］を選んで［OK］
数式の入ったセルがすべて選ばれ、ステータスバーに「データの個数」が出ます。これがそのシートの数式の数です。シートが複数あるなら、1枚ずつ同じ操作をして足します。
筆者のブックでこれをやると、集計シートだけで約100万でした。数千〜数万程度で重いなら、数式の数だけでなく、後半で紹介するほかの原因も合わせて確かめます。
手動にすると入力が軽くなるか
［数式］タブ→［計算方法の設定］→［手動］に切り替えて、セルに入力してみます。入力がすっと終わるようになれば、重さの正体は再計算です。手動にしても入力やスクロールが重いままなら、数式以外（条件付き書式・書式・図形・広がった使用範囲など）が効いています。確かめ終わったら、設定は［自動］に戻しておきます。
計算を「手動」にしても軽くならなかった理由
「重いなら計算を手動にすればいい」という対策はよく紹介されます。筆者も最初にこれを試しましたが、作業全体は速くなりませんでした。理由は3つあります。
1つ目は、計算をしなくてよくなるわけではないことです。手動にすると、入力のたびの再計算は止まります。しかし、集計結果を見るにはどこかでF9キーを押して計算させる必要があり、そのときには100万セル分の計算がまとめて走ります。待ち時間がなくなるのではなく、後ろにずれるだけです。
2つ目は、保存のときに計算が走ることです。手動にしていても、［ファイル］→［オプション］→［数式］の「ブックの保存前に再計算を行う」にチェックが入っていると、保存のたびに計算が始まります。このチェックは初めから入っています。保存で数分かかる状態は、手動にしても変わりませんでした。
3つ目は、古い数字のまま作業してしまう危険です。手動のあいだは、入力しても結果が更新されません。計算させ忘れたまま報告に使えば、間違った数字が外に出ます。計算が止まっているだけなのか、数式そのものが消えているのかの見分け方は、数式が数字になったセルとの見分け方の後半で扱っています。
手動計算は、入力をまとめて済ませたいときの一時的な手段としては使えます。ただ、ブックが抱えている計算の量は減らないので、根本的な対策にはなりません。
重さは「数式の数×1つの数式が見る行数」で決まる
VLOOKUPやSUMIFSは、計算のたびに検索範囲を見ていきます。つまり、ブック全体の計算量は、ざっくり次の掛け算で見積もれます。
計算量 ≒ 数式の数 × 1つの数式が見に行く行数
数式が1,000個でも、それぞれが10万行を見に行けば1億回の照合です。筆者のブックのように数式が100万個あると、1つの数式が見る行数が1万行だったとしても100億回になります。
規模の違いを並べると、次のようになります。数字は説明のために置いた概算です。
| 数式の数 | 1つの数式が見る行数 | 照合の回数（概算） |
|---|---|---|
| 1,000 | 1万行 | 1,000万回 |
| 1,000 | 10万行 | 1億回 |
| 100万 | 1万行 | 100億回 |
| 100万 | 10万行 | 1,000億回 |
この表からわかるのは、軽くするには「数式の数を減らす」か「1つの数式が見る行数を減らす」かのどちらかしかない、ということです。1つのブックの中で12か月分・全店舗分をまとめて計算しようとすると、この両方が最大になります。
数式がほかのシートの計算結果を参照しているときは、さらに重くなります。上流のセルが1つ変わると、それを使っている数式、その結果を使っている数式……と、つながっているものが順に計算し直されるからです。
工程ごとにブックを分け、前の工程からは「値」で受け取る
筆者が最終的にたどり着いたのが、作業の工程ごとにブックを分ける方法です。分け方は次の3つでした。
整形用のブック
元の販売データを受け取って、集計しやすい形に整えるブックです。商品コードの形式をそろえる、不要な列を消す、月や店舗の表記を統一する、といった作業をここで済ませます。整えたデータは、数式を残さず値にして保存します。
集計用のブック
整形済みのデータを貼り付けて、店舗別・商品別に集計するブックです。VLOOKUPやSUMIFSはこのブックにだけ置きます。参照するデータがすでに値なので、元データ側の変化で計算が連鎖することがありません。集計が終わったら、結果を値にして次に渡します。
報告用のブック
集計結果を貼り付けて、報告に使う表やグラフを作るブックです。ここには見た目を整えるための軽い数式しか置きません。報告の体裁を直すたびに100万セルの再計算を待つ、ということがなくなります。
分けると速くなる理由
ポイントは、ブックの間を数式でつながず、値で受け渡すことです。値になったデータは計算の対象にならないので、1つのブックが抱える数式は、その工程に必要な分だけになります。
1つのブックで全部をやっていたときは、報告用の表の文字を1か所直しても、集計の数式まで再計算が走っていました。分けたあとは、集計の計算は集計用のブックでだけ起き、整形や報告の作業中には起きません。待ち時間が「操作のたびに毎回」から「集計するときに1回」に変わったことが、1日から3時間への短縮の大部分を占めていると感じています。
なお、ブック同士を数式で参照し合う形（外部参照）にすると、開くたびにリンクの更新を聞かれたり、参照先のブックが閉じていると思うように計算されなかったりして、かえって扱いにくくなります。受け渡しは値の貼り付けに決めておくのがおすすめです。
1つのブックの中で「貼る場所」と「集計する場所」を分ける考え方は、貼り付け用と集計用にシートを分けた実例でも紹介しています。データが数千行ならシートを分けるだけで十分なことも多いので、まずシート、数十万行を超えて重くなったらブック、という順番で考えると無駄がありません。
集計用のブックでも重いときは、見る行数を減らす
集計用のブックに12か月分を入れて、それでも重いことがあります。そのときは、1つの数式が見る行数を減らします。たとえば、月ごとに集計して値にしてから12か月分を足す、商品マスタを引くVLOOKUPは整形の段階で済ませて集計には持ち込まない、といった具合です。先ほどの掛け算のうち、後ろの数字を小さくする工夫です。
SUMIFSの条件の書き方そのものに不安があるときは、SUMIFSで複数の条件を指定する書き方を先に確かめてください。
分けて速くなった代わりに、手順が増えた
3つに分けて作業時間は約3時間になりましたが、困ったこともありました。手順が増えたことです。
1つのブックだったときは「データを貼って待つ」だけでした。分けたあとは、整形用のブックを開いて整える、値で保存する、集計用のブックに貼る、計算させる、結果を値で書き出す、報告用のブックに貼る……と、ブックを行き来する作業が増えます。順番を1つ飛ばしたり、前の月のデータを貼ったままにしたりすると、正しい数字になりません。
自分だけで回すなら頭の中の段取りでも何とかなりますが、休みの日に誰かに頼むとなると無理があります。そこで、別に手順書を作ることにしました。
手順書は「工程ごとに5項目」で書く
手順書は、長い文章で書くと読まれません。工程ごとに同じ項目で書いておくと、初めての人でも順番に追えます。項目は次の5つです。
- 使うブック：ファイル名と保存場所
- 受け取るもの：前の工程から何を受け取り、どこに貼るか
- やること：操作を番号付きで、1行1操作
- 渡すもの：次の工程に何を、どの形（値）で渡すか
- 確かめること：次に進む前に見る数字
集計の工程を、この5項目で書いた例です。ファイル名やシート名は作例として置いています。
- 使うブック：販売集計_集計用.xlsx（共有フォルダの「月次」）
- 受け取るもの：整形用ブックの「整形済み」シートを値でコピーし、集計用ブックの「データ」シートのA2に貼る
- やること：F9キーで計算 →「集計」シートの合計欄にエラーがないか見る
- 渡すもの：「集計」シートを値でコピーし、報告用ブックの「貼り付け」シートに貼る
- 確かめること：データの行数と全店合計が、整形用ブックの数字と一致するか
いちばん大事なのは5つ目の「確かめること」です。ブックを分けると、貼り忘れや貼り間違いが起きても、数式のエラーとしては出てきません。工程の終わりに「行数」と「合計」の2つを前の工程と比べるようにしておくと、取り違えにその場で気づけます。
人に渡すことを前提にしたブックの作り方は、人に引き継げる表にする4つの対策でも扱っています。
数式の量以外で重くなる4つの原因
最初の見分けで「手動にしても重い」となった場合や、数式の数がそれほど多くない場合は、次の4つを確かめます。
1. 列全体を指定した配列の計算
SUMPRODUCTや配列数式で A:A のように列全体を指定すると、データが1,000行しかなくても約100万行すべてを計算します。A2:A5000 のように範囲を区切るか、表をテーブルにして列名で参照すると軽くなります。
2. 揮発性関数
OFFSET、INDIRECT、TODAY、NOW、RANDなどは「揮発性関数」と呼ばれ、どこか1つのセルを変えただけでも毎回計算し直されます。それを参照している数式もまとめて計算されるので、大きな表の中で多用すると重くなります。OFFSETで範囲を動かしているならINDEXに置き換える、INDIRECTでシート名を切り替えているならデータを1つのシートにまとめて条件で絞る、といった形に変えられないかを考えます。
3. 条件付き書式の範囲
条件付き書式の範囲が列全体や数万行に広がっていると、スクロールのたびに判定が走ります。［ホーム］→［条件付き書式］→［ルールの管理］で「このワークシート」を選び、同じルールが何十個にも分かれていないか、範囲が広すぎないかを見ます。コピーと貼り付けを繰り返したシートでは、同じルールが細切れに増えていることがよくあります。
4. 使用範囲が広がっている
Ctrl+End を押すと、Excelが「ここまでデータがある」と認識している最後のセルに飛びます。データは500行なのに10万行目に飛ぶようなら、昔の書式などが残っています。余分な行と列を選んで削除し（中身を消すのではなく、行・列ごとの削除）、保存し直すと、ファイルサイズと動作が軽くなることがあります。
これらは1つずつは小さな原因ですが、数式の多いブックに重なると効いてきます。ブックを分ける前の下準備として片付けておくと、分けたあとの各ブックも軽く保てます。
分けるかどうか迷ったときの目安
最後に、ブックを分けるかどうかの判断の目安をまとめておきます。
- 入力1回で数秒待つ程度なら、まずは上の4つの原因の点検と、検索範囲の絞り込みで様子を見る
- 入力のたびに数十秒から数分待つ、保存に分単位でかかるなら、工程ごとのブック分けを考える
- 分けるときは、ブック間は値で受け渡し、工程ごとの手順書と「行数・合計」の確認をセットで用意する
ブックを分けることは、速さと引き換えに手順を増やすことでもあります。手順書と確かめる数字まで含めて1つの仕組みにしておけば、自分以外の人が回しても同じ結果になります。
人に渡せる形にブックを作り替えるときの改修の順番と点検シートを、もう一歩踏み込んで確かめたい方は、引き継ぎ前に行う改修手順と点検シートにまとめています。
コメント