ピボットで作る毎月の購買集計を、作り直しから更新に変える

この記事のポイント（結論先出し）

毎月の購買実績の集計がピボットテーブルの手作業になっているなら、最初に変えるのは道具ではなく手順の形である。「毎月作り直す」作業を「元データを差し替えて更新する」作業に変える。そのために、①元データを 1 行 1 明細・結合セル無し・1 シート 1 表の形に固定する、②「仕入金額」「どの月に入れるか」などの定義を集計表の 1 枚目に書く、③手直しは集計表ではなく元データの側で行う、④毎月の結果を件数と合計で元データと照らす、の 4 つを決める。金融庁の内部統制の実施基準も、入力の正確性を確保する統制や例外処理は手作業でも行えるが、システムに組み込むほうが効率的かつ正確になると書いている。ダッシュボードに移すのは、この 4 つを決めたあとでよい。移す前に、システムに入っていない取引（カード払いや経費精算の購入など）が集計から抜けることを確かめておく。

毎月の集計は、こうやって作られている

💡 こうした調達・受発注の属人化、Newji one なら「ひとつの画面」で解決。見積依頼から発注・進捗・承認までAIが下支えします。

サービス資料を見る（無料）→

月が明けると、購買の担当者が受発注の一覧や会計ソフトの仕入データを書き出し、集計用のブックに貼り付ける。ピボットテーブルを更新し、取引先別・品目別・部門別の表を作る。合わない行を手で直し、グラフを貼り、前月や前年と比べるコメントを付けて、PDF にして上長や経営会議に送る。月に 1 日、多いと 2 日がこの作業に消える会社は珍しくない。

この記事は、この「毎月の作業」をどう減らすかを扱う。集計した数字から何を読むか、つまり取引先への集中や同じ品目の単価のばらつきの見方は購買データの集計で何を読むかにまとめた。ここでは、読む前の段階、数字を毎月同じ手順で出せるようにするところだけを見る。

手作業の集計が壊れる 5 つの場所

1. 貼り付けた範囲が伸びない
ピボットテーブルの元の範囲を固定のセル番地で指定していると、今月の行数が先月より多いとき、はみ出した行が集計に入らない。逆に少ないと、先月の行が残ったまま集計される。表を見ても気づきにくく、合計が少しずれるだけである。

2. 集計表を手で直す
取引先名の揺れや、誤って登録された行を、ピボットテーブルの結果や貼り付けた表の上で直す。翌月には同じ揺れがまた現れ、同じ手直しを繰り返す。直した内容は元のシステムに戻らないので、ほかの人が同じデータを使うと数字が合わない。

3. 列の意味が月によって変わる
書き出し元の列の並びが変わった、税抜だった列が税込になった、キャンセルの行が含まれるようになった。こうした変化は、集計の結果がおかしくなって初めて気づく。

4. 手順を知っているのが 1 人だけ
どの順番で貼り、どこを直し、何を除くかが担当者の頭の中にある。担当が休むと、その月の集計が出ない。属人化をどう減らすかは発注の状況を担当者しか知らないときで扱う。

5. 送った PDF ごとに数字が違う
月の途中で一度送り、修正して送り直すと、経営会議の資料と部門に配った資料で数字が違う。どれが正しい版かは「どれが最新のファイルか分からない」を無くすで整理する。

元データの形を、集計向けに固定する

集計の作業を安定させる一番の近道は、元データの形を毎月同じにすることである。総務省は政府統計の表について、機械が読める形にするための表記のルールを定めており、1 セル 1 データ、セルを結合しない、体裁のためのスペースや改行を入れない、項目名を省略しない、空白の行や列でデータを分断しない、1 枚のシートに複数の表を載せない、といった点を挙げている^[1]。統計表のためのルールだが、ピボットテーブルの元にする表の条件とほぼ同じである。

ルールは、数式でセルの値を出している場合、並べ替えなどをしたときに正確な値が表示されなくなる可能性があるので値のみにすること、とも書いている^[1]。元データの側に計算の列を作ると、行を足したときに数式が付いてこない行ができる。元データは書き出したままの値だけにし、計算はピボットテーブルの側で行う。

実務で決めておくのは次の 4 点である。

元データは 1 枚のシートに、1 行 1 明細で置く。月ごとにシートを分けず、月の列を持たせて下に足していく
元データの範囲は表計算ソフトの「テーブル」にして、行が増えればピボットテーブルの範囲も伸びるようにする
単位や通貨は、数量や金額と別の列に持つ。ルールも、単位は別のセルに入れるよう求めている^[1]
元データのシートには手を入れない。直すときは、書き出し元のシステムや台帳の側で直してから書き出し直す

定義を、集計表の 1 枚目に書く

「今月の仕入金額」という一つの数字にも、決めておくべきことがいくつもある。税抜か税込か。どの日付でどの月に入れるか（発注日か、入荷日か、検収日か）。キャンセルや下書きの行を含めるか。返品や値引をどう差し引くか。外貨の取引をどう扱うか。

税抜か税込かは、経理の帳簿と揃えておくと説明がしやすい。消費税には軽減税率と標準税率の 2 つの税率があり、事業者は取引を税率ごとに区分して記帳する必要がある^[2]。購買の集計を税込で作ると、税率の違う取引が混ざったときに経理の数字と並べにくくなる。国税庁は、電子取引のデータを検索できるようにしておく項目の「取引金額」について、帳簿の処理方法（税抜経理か税込経理か）に合わせるべきと考えられると説明している^[3]。集計表もこの考え方に合わせ、どちらで作っているかを定義に書いておく。

日付や外貨など、ほかの揃え方の詳しい判断は購買データの集計の記事に書いた。ここで言いたいのは、決めた結果を集計表の 1 枚目に文章で残すことである。定義が担当者の頭の中にしかないと、担当が替わった月から数字の意味が変わる。

「作る」作業を「更新する」作業に変える

元データの形と定義が固まれば、毎月の作業は「元データのシートを今月の書き出しに差し替え、ピボットテーブルを更新し、検算する」の 3 つに減る。グラフやコメントの枠は先月のものをそのまま使う。

検算は省かない。集計表の合計件数と合計金額が、書き出した元データの件数と合計に一致するかを毎月確かめる。合わなければ、範囲の外にはみ出した行があるか、除くべき行が残っている。金融庁の企業会計審議会が示す内部統制の実施基準は、IT に係る業務処理統制の例として、入力情報の完全性・正確性・正当性を確保する統制、例外処理（エラー）の修正と再処理、マスタ・データの維持管理を挙げ、これらは手作業により実施することも可能だが、システムに組み込むことでより効率的かつ正確な処理が可能になる、と書いている^[4]。上場会社の財務報告のための基準だが、毎月の検算は、手作業の集計に組み込める統制の典型である。

手順は、画面の操作まで含めて 1 枚の手順書にする。手順書があれば、担当以外の人でも同じ数字を出せる。手順書を書いてみて、「この行は毎月手で消す」「この取引先名は毎月置き換える」という項目が出てきたら、それが元データの側で直すべきところである。

ダッシュボードに移すときに決めること

手順が固まると、次はシステムの画面で集計を見られるようにする、つまりダッシュボードに移すかどうかの判断になる。総務省の令和 6 年の通信利用動向調査によれば、常用雇用者 100 人以上の企業（回答 2,330 社）のうち、デジタルデータの収集・解析のために IoT や AI などのシステム・サービスを導入している企業は 18.4% だった^[5]。導入している企業（回答 479 社）に目的を聞くと、「効率化・業務改善」が 88.1% で最も多い^[5]。割合は母集団の構成に合わせて比重調整した値で、社数は調整前の回答数なので、2,330 社に 18.4% を掛けても 479 社にはならない^[5]。この数字は購買の集計に限ったものではなく、IoT や AI という限られた種類の仕組みについての数字だが、100 人以上の規模の企業でも、データの収集・解析の仕組みを入れているのは 2 割に届いていない。移すかどうかは、他社がどうしているかではなく、自社の毎月の手間で決めればよい。

移すときに決めることは次のとおりである。

決めること	ピボットの手作業	ダッシュボード	確かめ方
集計の元になる取引	書き出した範囲すべて（会計の仕入も混ぜられる）	そのシステムに入っている取引だけ	カード払い・経費精算・別システムの発注が抜けていないか
金額の定義	集計表の 1 枚目に書いたとおり	システムの決めた定義（税抜・税込、日付、キャンセルの扱い）	同じ月をピボットとダッシュボードで出して、合計が合うか
外貨の取引	自分で決めた相場で換算	換算するか、集計から外すかはシステムによる	外貨の件数と金額が別に分かるか
更新の頻度	月 1 回（担当が作った日）	登録した時点で反映	月の途中の数字を、確定の数字として使っていないか
配り方	PDF をメールで送る	見る人が画面を開く。定期配信の有無はシステムによる	経営会議の資料をどの時点の画面で作るか
見る人と権限	ファイルを受け取った人	アカウントを持つ人。プランや権限で見える範囲が変わることがある	見せたい人が、見せたい粒度まで見られるか

移した直後の 1〜2 か月は、ピボットの集計も並行して作り、同じ月の合計を照らすとよい。合わない場合、たいていは定義の違い（税込と税抜、日付の基準、キャンセルの扱い）か、ダッシュボード側に入っていない取引である。違いの理由が説明できるようになってから、ピボットの作業をやめる。

ダッシュボードに移しても、見る数字を増やしすぎない。毎月の会議で実際に話題になる数字（今月の仕入の合計、上位の取引先、前月からの大きな変化）に絞ると、画面を開く人が続く。

ピボットのままでよい場合

集計の元データが 1 つのシステムからの書き出しで、手順書があり、検算も毎月合っているなら、ピボットテーブルのままで困ることは少ない。月に 1 回見るだけの数字のために道具を替える必要はない。移すことを考えたほうがよいのは、元データが複数の台帳に分かれていて毎月の貼り合わせに時間がかかる、月の途中で数字を見たいという要望が多い、集計の担当が替わるたびに数字の定義がぶれる、といった場合である。移すと決めたら、どの業務から順に移すかをエクセルの購買業務をシステムに移す順番で確かめてほしい。

よくある質問

マクロで自動化すれば十分ではないですか

手順が固まっていれば、マクロで貼り付けと更新を自動にするのは有効である。ただし、元データの列の並びが変わるとマクロは黙って違う列を読む。マクロを使う場合も、毎月の件数と合計の検算は残しておく。マクロの中身を担当者しか分からない状態は、手順が頭の中にある状態と変わらない。表計算で集計を続けるときに何が先に壊れるかは、原価の集計を例に原価管理をエクセルでやる——どこまでできて、何が壊れるかで整理している。

会計ソフトの仕入データで集計してもよいですか

金額の合計を経理の数字と揃えたいなら、会計のデータは使いやすい。ただし会計のデータには品番や数量が無いことが多く、品目別の集計には向かない。取引先別・月別の金額は会計から、品目別の数量と単価は受発注の記録から、と元を分けて使う方法もある。その場合は、両方の取引先の合計が合うかを確かめる。

ダッシュボードの数字と、経理の数字が合いません

多くは定義の違いである。購買の側は発注や検収の時点、経理は計上の基準の時点で月を区切っていることが多い。税抜と税込、返品・値引の差し引き方も確かめる。どちらが正しいかではなく、どの定義の数字かを画面や資料に書いておくことで、会議での食い違いは減る。

まとめ

ピボットテーブルで作る毎月の購買集計は、道具を替える前に手順の形を変えると手間が減る。元データは 1 行 1 明細・結合セル無し・1 シート 1 表・値のみの形に固定し、表計算ソフトのテーブルにして範囲が伸びるようにする。税抜か税込か、どの日付で月に入れるか、キャンセルや外貨をどう扱うかを集計表の 1 枚目に書き、手直しは元データの側で行う。毎月の作業は差し替え・更新・検算の 3 つにし、件数と合計を元データと照らす。ダッシュボードに移すときは、集計の元になる取引の範囲と金額の定義を確かめ、1〜2 か月は並行して合計を照らしてからピボットの作業をやめる。

出典・参考資料

統計表における機械判読可能なデータ作成に関する表記方法（総務省統計局・政策統括官（統計基準担当）／令和 2 年 12 月 18 日）
No.6497 仕入税額控除のために保存する帳簿および請求書等の記載事項（国税庁）
電子帳簿保存法一問一答【電子取引関係】（国税庁／令和 7 年 6 月）
財務報告に係る内部統制の評価及び監査の基準並びに財務報告に係る内部統制の評価及び監査に関する実施基準（金融庁企業会計審議会／令和 5 年 4 月 7 日改訂）
令和 6 年通信利用動向調査報告書（企業編）（総務省情報流通行政局）

仕入の推移と上位の取引先を、毎月の貼り付けなしで見る

この記事で扱った項目のうち、Newji one（製造業の見積・発注クラウド）では、登録した注文から、指定した期間の売上・仕入・収支と在庫金額、月別・週別の推移、仕入の上位の取引先を画面で確認できます。下書きとキャンセルした注文は集計に含めず、円以外の通貨の注文は円と合算せずに件数で知らせます。月別・週別の推移と上位の取引先は有料プランの機能で、無料の招待プランでは合計だけが表示されます。集計結果をメールで定期的に送る機能は無く、Newji one に登録していない取引は集計に入りません。

サービス資料を見る（無料）

WHITE PAPER

この記事の理解を深める
無料ホワイトペーパーをプレゼント

製造業の現場で使える実務資料（PDF）を無料でお届けします。"こんな資料が届きます" ↓ 下のボタンからどうぞ。