CSVの集計だけで、毎週半日近くが消えていた時期があります。私は小売業のマーケティング部署で集計を担当していて、基幹システムから出力した販売データを週に1回まとめています。ファイルは1本あたり120行・30列ほどで、それが2〜3本。行数だけ見れば大した量ではないのに、1回の集計に3時間ほどかかっていました。
いまは同じ作業が、CSVを貼り付けて結果を確かめるところまで含めて1時間ほどで終わります。変えたのは関数の腕前ではなく、表の作りです。元データを貼り付けるシートと、集計するシートを分けただけです。
この記事では、そのとき作った「貼るだけで集計できる表」の作り方を、作例の表を使って順に説明します。列名や金額は説明のために用意したもので、職場の実際のデータではありません。
3時間のうち、ほとんどは「表を整える」時間だった
作り直す前の手順はこうでした。
- 基幹システムからCSVを出力して開く
- 集計に使わない列を削除する
- 並べ替える
- SUMIFを入れ直し、範囲を今週の行数に合わせる
- 合計を確かめて、報告用の表に書き写す
振り返ると、集計そのものにかかっていた時間は長くありません。時間を食っていたのは2〜4の「表を整える」作業で、しかも毎週同じことを繰り返していました。
ミスも、ほとんどがこの部分で起きていました。いちばん多かったのは範囲の間違いです。先週は118行だったのに、今週は124行ある。SUMIFの範囲を入れ直すときに最後の数行を入れ忘れると、エラーは出ないまま合計だけが少なくなります。列を消す作業にも、消す列を取り違える危険がいつもありました。
もう1つ困ったのが、人によってやり方が違うことです。これまでこの集計を担当した人ごとに、消す列の位置もSUMIFの書き方もばらばらでした。数字が合わないときに原因を探そうとしても、まずその表がどういう手順で作られたのかを読み解くところから始めなければなりません。3時間の中には、この読み解きの時間も入っていました。
手順の中に「毎回、人の手で表を作り替える」工程がある限り、どれだけ注意しても範囲の間違いはなくならない。そう考えて、表の作りを変えることにしました。
貼り付けるシートと、集計するシートを分ける
作り直した表は、シートが2枚だけです。
- 貼り付けシート：CSVの中身をそのまま貼るだけのシート。列を消したり並べ替えたりしない
- 集計シート：貼り付けシートを参照して、カテゴリーごとの合計を出すシート。毎週は触らない
ここからは作例で説明します。CSVは30列あり、そのうち集計に使うのは次の3列だとします。
| 列 | 中身 | 作例の値 |
|---|---|---|
| A | 売上日 | 2026/11/2 |
| H | カテゴリー | 食品 |
| N | 売上金額 | 3,480 |
集計シートのA列にカテゴリー名を並べ、B列に次の式を入れます。
=SUMIFS(貼り付け!$N$2:$N$1000,貼り付け!$H$2:$H$1000,$A4)合計範囲は貼り付けシートのN列（売上金額）、条件範囲はH列（カテゴリー）、条件は集計シートのA4に書いたカテゴリー名です。この式を下のカテゴリーの行へコピーします。範囲に$を付けて固定しているので、コピーしても範囲は動きません。$の付け方に迷ったらコピーしても参照がずれない書き方を見てください。
作り直す前との違いは、使わない列を消していないことです。CSVの30列をそのまま貼り、必要な列だけを式の側で選んでいます。列を消す作業がなくなれば、消す列を取り違えることもありません。並べ替えも同じで、SUMIFSは行の順番に関係なく条件に合う行を拾うので、貼る前に並べ替える必要がなくなりました。
式はSUMIFではなくSUMIFSにそろえています。条件が1つならどちらでも同じ結果になりますが、あとから「今週分だけ」「この店舗だけ」と条件を足したくなったとき、SUMIFSなら条件を書き足すだけで済みます。SUMIFとSUMIFSは合計範囲を書く位置が違うので、同じブックに両方あると、確かめるたびに引数の順番を読み直すことになります。書き方の基本は複数の条件で合計を出す方法で詳しく説明しています。
カテゴリー名の一覧は、最初に1回だけ作る
集計シートのA列に並べるカテゴリー名は、作り直すときに1回だけ用意します。貼り付けシートのH列をコピーして別の場所に貼り、「データ」タブの「重複の削除」で1つずつにしてから、集計シートのA4から下に並べます。作例では、食品・日用品・飲料・衣料・家電・季節品の6つで、A4からA9に入ります。
毎週の作業では、この一覧には触りません。新しいカテゴリーが増えたときだけ、一覧の最後に1行足して、上の行の式をコピーします。増えたことにどう気づくかは、このあとの「貼ったあとに確かめる3か所」で説明します。手で打ち込むと「日用品」と「日用雑貨」のように名前が揺れやすいので、一覧は必ずCSVからコピーして作るのがコツです。
範囲は「多めの行数」で固定しておく
式の範囲は、2行目から1000行目までに固定しています。毎週の行数に合わせて範囲を入れ直していたことが、ミスのいちばんの原因だったからです。
作例では、今週のCSVが3本で、行数は118行・124行・121行。合わせて363行です。1000行まで取っておけば、ファイルが1本増えても収まります。範囲の中に空白の行が残っていても、SUMIFSは条件に合わない行として飛ばすので、合計は変わりません。
列全体（N:N）で指定する方法もあります。私が行数で固定したのは、式を見たときに「どこまでを集計しているか」がひと目でわかるからです。人に説明するときも、範囲の終わりが書いてあるほうが伝わりやすくなります。
ただし、行数を固定するなら、上限を超えたときに気づける仕掛けが要ります。集計シートの隅に、次の確認用のセルを置いておきます。
=COUNTA(貼り付け!$A$1001:$A$5000)1001行目より下にデータがあれば、この結果が0より大きくなります。0以外になったら、範囲を広げる合図です。黙って集計から漏れることを防ぐためのセルなので、目立つ色を付けておきます。範囲が古い行数のまま止まって合計が合わなくなる仕組みは、SUMIFSの合計が少ないときの原因でも説明しています。
条件を足すときも、範囲の決め方は変えない
「今週分だけ」のように日付で絞りたくなったら、同じ1000行の範囲で条件を1組足します。集計シートのB1に週の初日を入れておき、式を次のように書き換えます。
=SUMIFS(貼り付け!$N$2:$N$1000,貼り付け!$H$2:$H$1000,$A4,貼り付け!$A$2:$A$1000,">="&$B$1)足した範囲も、ほかの範囲と同じ2行目から1000行目です。SUMIFSは、範囲どうしの行数がそろっていないとエラーになります。条件を足すたびに範囲の終わりをそろえる決まりにしておけば、どこか1つだけ999行目で止まっていた、という見落としも起きません。
貼り付けシートは「消して貼るだけ」にする
貼り付けシートの扱いには、3つの決まりを作っておきます。
前回の分は、2行目から下の値だけを消す
1行目の見出しは残し、A2からAD1000までを選んでDeleteキーを押します。行を削除するのではなく、セルの中身だけを消します。行ごと削除すると、集計シートの式の範囲が一緒に縮んでしまうからです。
CSVは見出しを除いて、続けて貼る
1本目のCSVは見出しを除いた2行目から下をコピーし、貼り付けシートのA2に貼ります。2本目と3本目も見出しを除いてコピーし、直前のファイルの最後の行のすぐ下に貼ります。A列でCtrl＋↓を押すと最後の行に飛べるので、その1つ下が貼り付け先です。
ファイルの間に空白の行は入れません。空白の行があっても集計の数字は変わりませんが、あとで並べ替えやフィルターを使ったときに、表が途中で切れてしまいます。
貼ったあとに、列を足したり消したりしない
気になる行を見つけても、貼り付けシートの上では直しません。直す必要がある値は、集計シートの側に「調整」の行を作って足し引きします。貼り付けシートを「CSVと同じ中身」に保っておけば、数字が合わないときに元のCSVと突き合わせるのも簡単です。
CSVをExcelで開いた時点で、金額が文字の数字になったり、商品コードの先頭の0が消えたりすることもあります。そうした取り込みのトラブルはCSVを壊さずに取り込む手順で扱っています。
貼ったあとに確かめる3か所
貼るだけで集計できるようになっても、確かめる工程は残します。範囲を入れ直さなくなった分、間違いが起きるとすれば「貼り方」のほうだからです。集計シートの上のほうに、次の確認欄を並べておきます。
| 確認すること | 式 | 作例の結果 |
|---|---|---|
| 貼った行数 | =COUNTA(貼り付け!$A$2:$A$1000) | 363 |
| 金額の総額 | =SUM(貼り付け!$N$2:$N$1000) | 1,452,600 |
| カテゴリー別の合計を足した値 | =SUM(B4:B9) | 1,452,600 |
行数は、出力したときの件数と同じか
3本で118・124・121なら363です。違っていれば、どれか1本の貼り漏れか、同じファイルを2回貼ったかのどちらかです。CSVごとの件数は、それぞれのファイルをExcelで開いたときの最終行の番号から1を引けば（見出しの1行分）すぐにわかります。
総額と、カテゴリー別の合計が一致しているか
この2つは一致していなければいけません。ずれていれば、集計シートに並べたカテゴリー名と、CSVのカテゴリー名が食い違っています。新しいカテゴリーが増えた、名前の表記が変わった、という場合です。差額がいくらかを見ると、どのカテゴリーが漏れたのか見当がつきます。作例で言えば、総額が1,452,600、カテゴリー別の合計が1,438,200なら、差は14,400です。この額が、一覧に載っていないカテゴリーの売上です。貼り付けシートのH列でフィルターの一覧を開き、集計シートにない名前を見つけたら、一覧の最後に1行足します。差額から原因を絞る考え方はずれた金額の形で原因を見分ける手順にまとめています。
列の並びが、前回と同じか
基幹システムの出力項目が変わって列が1本増えると、N列にあったはずの金額が隣の列にずれます。式はエラーを出さずに別の列を合計してしまうので、気づきにくいミスです。
そこで、正しい見出しの並びを「見出し」という別のシートの1行目にコピーしておき、集計シートに次の式を置きます。
=SUMPRODUCT(--EXACT(貼り付け!$A$1:$AD$1,見出し!$A$1:$AD$1))EXACTは、2つの文字が完全に同じならTRUEを返す関数です。30列の見出しが全部同じなら結果は30になり、1列でもずれていれば30より小さくなります。30以外になったら、式の列を直す前に、CSVのどこが変わったのかを先に確かめます。
人が替わっても同じ手順で回すために
作り直しにかかった時間は2時間ほどでした。1回の集計が3時間から1時間になったので、1回目で元が取れた計算です。それ以上に大きかったのは、誰がやっても同じ手順になったことです。
担当者ごとにやり方が違っていたのは、手順の中に判断が入っていたからだと思います。どの列を消すか、範囲をどこまで取るか、並べ替えをするか。人によって判断が変われば、表も変わります。貼るだけの形にしてからは、手順は「前回分を消す」「CSVを貼る」「3か所を確かめる」の3つだけになりました。
この3つの手順は、集計シートのいちばん上に書いておくのがおすすめです。手順書を別のファイルにすると、そのファイルの場所を探すところから始まってしまいます。ほかの人に頼むときも、このシートを開いてもらえば説明が済みます。
表を人に渡す前に、入力・計算・出力をどう分けておくと引き継ぎやすいかは、人に渡せる表の作り方で整理しています。今回の表は、その考え方を毎週のCSV集計に当てはめたものです。
もう一歩踏み込んで、引き継ぎの前にどこを直しておくかを決めたい方向けに、人に渡せる形に直す改修手順と点検シートをnoteにまとめています。自分の作った集計表を同僚に任せる予定がある方は、渡す前の点検に使ってください。
コメント