Excelのピボット集計をAccess集計フォームへ移す判断と作り方

概要

この記事の結論
Accessの集計フォームは、テーブルに蓄積したデータを集計クエリで計算し、期間や顧客などの条件を画面から指定する仕組みです。行数が増えてもセル範囲を選び直す必要はありません。ただしExcelでも、テーブルをピボットの元にすると範囲管理を改善できます。Accessを選ぶ場合は、元データの粒度、取込の完了、再集計のタイミングをそろえることが大切です。

この記事でわかること

  • 集計範囲の問題だけならExcelでも改善できる
  • 売上の粒度と集計基準をそろえる
  • 顧客別の集計クエリを作る
  • 期間を入力する集計フォームを組み立てる
  • 再集計と最新データの確認を分ける

この記事の主な内容

  1. 集計範囲の問題だけならExcelでも改善できる
  2. 売上の粒度と集計基準をそろえる
  3. 顧客別の集計クエリを作る
  4. 期間を入力する集計フォームを組み立てる
  5. 再集計と最新データの確認を分ける
  6. 二重計上と金額の不一致を防ぐ
  7. Excelとの併用と段階的な移行
  8. よくある質問

想定読了時間：約15分～20分

集計範囲の問題だけならExcelでも改善できる

「行が増えるたびにピボットの範囲を直す」という悩みは、Excelの元データをテーブルにすることで改善できる場合があります。固定したセル範囲ではなくテーブルを参照すれば、追加された行を更新時の対象に含めやすくなります。［参考3］
ピボットの範囲管理だけを理由に、Accessへの移行を決める必要はありません。

範囲に行が入ることと、表示済みの集計結果が更新されることは別です。ピボットの更新動作はExcelのバージョン、接続先、設定によって異なります。手動更新やブックを開くときの更新、自動更新の対応状況を自分の環境で確認し、追加した1行が結果へ反映されるまで試してください。［参考2］

困っていることExcelでの改善候補Accessを検討する理由
範囲の指定漏れテーブルを元にしたピボット他の要件がなければ移行を急がない
毎月複数のファイルを結合Power Queryで取込を定型化保存先と取込履歴を共通管理したい
集計条件が担当者ごとに違う集計手順・条件の統一条件フォームで選択肢を固定したい
入力と検索と集計が分断ブック構成を整理同じデータを複数業務で使いたい

Accessが有効なのは、蓄積するデータを中心に、入力、検索、集計の仕組みをつなぎたい場合です。一方、自由な切り口で分析する作業はExcelに残す方が便利なこともあります。「入力と共通集計はAccess、追加分析はExcel」という分担も比較しましょう。

AccessのExcel改善とAccess集計の役割を示した図

売上の粒度と集計基準をそろえる

集計前に、元データの1行が何を表すかを決めます。1行が伝票なのか、伝票内の商品明細なのかが曖昧だと、件数や金額を二重に数えます。また受注日基準と売上日基準、税込と税抜、取消を含むかどうかも、操作の前に業務上の定義をそろえてください。

この記事は、T_売上の1行を「売上明細1件」とする架空の例です。売上IDはオートナンバー型の主キー、売上日は日付/時刻型、顧客IDは数値型の長整数、売上金額は通貨型、取消はYes/No型です。取消の既定値はFalseとし、売上日、顧客ID、売上金額は必須を想定します。

売上ID売上日顧客ID売上金額取消
12026/10/011010000False
22026/10/15105000False
32026/10/20208000False
42026/10/25103000True

この例で10月の有効売上は23000、顧客10は15000、顧客20は8000、明細件数は3です。取消を含める場合の26000と混同しないようにします。売上IDが4まであることは、売上件数が4である根拠にはなりません。採番値と集計対象の件数を分けて考えましょう。

売上金額のNullはゼロと同じではありません。未入力をゼロに置き換えて合計が合ったように見せるのではなく、未確定の金額が残っていないかを先に検査します。返品をマイナスで保存するか、別区分にするかも統一し、担当者によって合計の意味が変わらないようにします

Accessの集計前に決める三つの基準を示した図

顧客別の集計クエリを作る

集計クエリでは、まとめるキーをグループ化し、金額に合計を指定します。M_顧客の顧客IDを主キーとし、T_売上の顧客IDとの関係が一対多になるようにしてから結合します。名称だけで結合すると、同名の顧客を誤って混ぜたり、複数の一致で金額が増えたりするおそれがあります。

デザインビューで確認する手順

1. ［作成］の［クエリデザイン］でT_売上とM_顧客を追加します。

2. 顧客ID同士を結び、顧客ID、顧客名、売上金額、取消をグリッドへ追加します。

3. ［集計］を押して集計行を表示します。顧客IDと顧客名はグループ化、売上金額は合計にします。

4. 取消は集計行を［Where条件］相当の項目にし、抽出条件をFalseにします。表示は外します。

5. 結果を確認し、顧客10の15000と顧客20の8000を照合します。

画面の日本語名称はバージョンで異なる場合があります。合計する前に対象を絞る条件はWHERE、合計した結果を絞る条件はHAVINGという違いがあります。ここで取消を除外するのは、売上を合計する前の条件です。［参考1］

SELECT S.[顧客ID], M.[顧客名],
       Sum(S.[売上金額]) AS [売上合計],
       Count(*) AS [明細件数]
FROM [T_売上] AS S INNER JOIN [M_顧客] AS M
ON S.[顧客ID] = M.[顧客ID]
WHERE S.[取消] = False
GROUP BY S.[顧客ID], M.[顧客名]
ORDER BY S.[顧客ID];

このINNER JOINは、対応する顧客マスターがある売上だけを集計します。移行直後など、未対応の顧客IDがあると合計から抜けるため、マスター不一致の売上がゼロ件かを別のクエリで確認します。正しい集計式でも、結合条件が不適切なら結果は正しくなりません。

Accessの顧客別集計の作り方を示した図

期間を入力する集計フォームを組み立てる

期間の入力欄は売上データへ連結しない部品にします。集計条件を入力したつもりが、売上レコードの日付を書き換える画面にならないよう、検索条件用の親フォームは非連結にします。結果は集計クエリに連結した読み取り用のサブフォームへ表示します。

F_売上集計にtxt開始日、txt終了日、cmd集計を配置します。二つの日付欄のコントロールソースは空欄にし、書式を日付にします。Q_顧客別期間売上を基に作ったSF_顧客別売上を配置し、サブフォームコントロールの名前をsub集計にします。リンク親フィールドとリンク子フィールドは空欄です。

PARAMETERS [Forms]![F_売上集計]![txt開始日] DateTime,
           [Forms]![F_売上集計]![txt終了日] DateTime;
SELECT [顧客ID], Sum([売上金額]) AS [売上合計],
       Count(*) AS [明細件数]
FROM [T_売上]
WHERE [取消] = False
  AND [売上日] >= DateValue([Forms]![F_売上集計]![txt開始日])
  AND [売上日] < DateAdd(“d”,1,
      DateValue([Forms]![F_売上集計]![txt終了日]))
GROUP BY [顧客ID]
ORDER BY [顧客ID];

このクエリは、親フォームが開いている前提です。パラメーターの型はDateTimeと明示し、日付欄の名前を一致させます。結果に顧客名が必要なら、前節と同様にIDでマスターを結合してください。初期表示では両日付を未入力にして結果を出さず、条件の指定後に集計する運用にします。

Private Sub cmd集計_Click()
    If Not IsDate(Me!txt開始日) Or Not IsDate(Me!txt終了日) Then
        MsgBox “開始日と終了日を日付で入力してください。”
        Exit Sub
    End If
    If DateValue(Me!txt開始日) > DateValue(Me!txt終了日) Then
        MsgBox “終了日は開始日以降にしてください。”
        Exit Sub
    End If
    Me!sub集計.Form.Requery
End Sub

終了日を翌日未満にするのは、売上日に時刻が含まれていても終了日の23時台まで対象にするためです。「10月31日以下」だけでは、10月31日午前0時より後のデータが漏れる場合があります。日付だけを入力する仕様として、開始日と終了日の時刻部分をDateValueで除いています。

Accessの条件フォームと集計結果を示した図

Accessの教科書で集計業務の改善を相談する

候補や集計条件が部署ごとに異なる、既存の画面を直せる担当者がいないなど、集計の手順だけでは解決しない場合は、現在のExcelと業務の流れを整理して相談すると検討が進みます。Accessの教科書のお問い合わせで、対象データ、利用人数、困っている操作をお知らせください。

Accessの教科書で集計業務の改善を相談する

再集計と最新データの確認を分ける

再クエリは、現在保存されているデータを取り直す処理です。［参考4］
外部システムのCSVがまだ取り込まれていなければ、再集計ボタンを押してもその売上は含まれません。「最新」と表示するなら、データの取込完了日時と、集計を実行した日時を別々に管理します。

確認対象意味画面へ示す例
取込完了日時どこまで元データを保存したか10月25日09時の取込まで
集計対象期間どの日付の売上を集計したか10月1日から10月31日
集計実行日時いつ再計算したか10月25日10時15分
対象の条件取消などの除外ルール取消を除く税抜売上

売上が追加された後にフォームを開いたまま結果を見続ける場合は、再集計の操作が必要です。一定間隔で自動再集計する設計も可能ですが、通信負荷や見ている行の移動を考慮します。通常は利用者が押す「集計」ボタンと、条件・日時の表示があるだけでも誤解を減らせます。

月次の確定値を後日も同じ数字で再現したい場合は、再計算される画面だけでは足りません。締め処理、確定日時、修正の承認、確定結果の保存などを別途設計します。常に現在値を見る日次集計と、過去の確定値を残す月次報告を同じ仕組みとして扱わないでください。

Accessの取込と再集計は別の処理を示した図

二重計上と金額の不一致を防ぐ

金額が合わない場合は、データの粒度と結合から調べます。売上1行に対して明細が3行ある表へ結合し、売上総額をそのまま合計すると3倍になることがあります。取引ヘッダーと明細、請求と入金のような一対多の関係をつなぐときは、合計する単位を先にそろえます。

代表的な対処は、明細側を売上ID単位に集計してから結合することです。「重複を消すためDISTINCTを付ける」といった見た目の対処だけでは、同額の正当な売上まで消す可能性があります。金額が一致した理由を、件数とIDの対応までたどって説明できる状態にしましょう。

試験データ確認する期待結果
サンプル4行の10月集計取消を除く合計23000、明細3件
終了日の23時59分の売上当日の対象へ含まれる
翌日の0時の売上対象外になる
該当する売上がない期間明細0件と分かる表示になる
未対応の顧客ID不一致一覧で検出される
同一CSVの再取込二重登録を検出・防止できる

集計結果を編集できないのは、集計クエリでは通常の仕様です。合計金額を書き換えるのではなく、元の売上を権限のある入力画面から訂正して再集計します。差異の原因を残す必要がある場合は、訂正理由や更新履歴も記録します。

Accessの二重計上を防ぐ確認を示した図

Excelとの併用と段階的な移行

最初は一つの月次集計だけをAccessで作り、既存のExcelと同じ条件で比較します。合計が一致したら、顧客別、日付別、取消別にも分けて確認します。総額だけが偶然一致し、顧客の配分が間違っている場合があるためです。

出力をExcelで加工する場合は、対象期間、取込時点、取消の扱いを一緒に渡します。数値だけがコピーされると、後から別の集計結果と比較したときに、条件の違いを判別できません。出力用のファイル名や先頭シートにも、対象月と作成日時を記載すると管理しやすくなります。

運用開始後は、取込件数と集計対象件数の差、未対応ID、取消件数を定期的に確認します。共通の集計定義はAccess側へまとめ、自由分析はExcelで行う分担なら、両方の得意分野を使えます。日次の画面と確定帳票の責任者も決め、条件を変える際の連絡手順を残してください。

AccessのAccessとExcelの併用を示した図

よくある質問

元データを固定のセル範囲で参照している場合は、追加行が範囲外になることがあります。ただしExcelテーブルを元にしたピボットなら、行追加への追従を改善できます。集計結果の更新がいつ行われるかは、バージョンや設定によって確認が必要です。まずテーブル化し、新規行を追加して更新後に含まれるかを試しましょう。範囲の問題だけならExcelで解決できる可能性があり、Accessへの移行は入力や共同利用など他の要件も含めて判断します。

常に最新になるわけではありません。Accessの集計は、保存済みのデータをクエリで計算します。外部CSVが未取込なら、その内容は再集計しても反映されません。また開いたままのフォームでは、追加されたデータを反映する再クエリが必要になる場合があります。取込完了日時と集計実行日時を分けて表示し、何の時点のデータかが分かるようにしてください。自動更新を設ける場合も、処理負荷と利用中の表示位置への影響を確認してから導入します。

集計クエリの合計値は、通常そのまま編集する対象ではありません。誤りがあれば元の売上明細を確認し、数量、金額、取消などの原因となる値を訂正して再集計します。合計だけを直接直せる運用にすると、明細とのつながりが失われ、後日の検算が難しくなります。確定後の調整が必要な業務では、調整伝票や訂正履歴として別に記録する設計を検討してください。誰が何を直したかを残し、日次の現在値と月次の確定値の扱いを区別します。

売上日が日付/時刻型で、時刻も保存されている可能性があります。終了日を「10月31日以下」とすると、条件によっては10月31日0時より後の売上が対象外になります。日単位で検索する画面では、開始日以上かつ終了日の翌日未満という条件にすると、終了日の23時台まで含められます。開始日と終了日は日付として検証し、時刻部分を除く仕様にそろえましょう。月末23時59分と翌月1日0時の両方を使う境界テストが有効です。

顧客マスター側に同じ結合値が複数あるか、売上1行に対して複数行が結び付いていないかを確認します。顧客名は同名や表記揺れがあるため、主キーである顧客IDで結合する方が安全です。明細表を結合する場合には、明細側を必要な単位で先に集計する方法もあります。重複を消す操作だけで数字を合わせず、結合前後の件数と売上IDを比較してください。対応するマスターがない売上が、INNER JOINで除外されていないかも併せて点検します。

必ずしも統一する必要はありません。入力データと共通の集計条件はAccessで管理し、自由な分析や提出用の加工はExcelで行う分担も有効です。その際は、どちらを元データの正本とするかを決め、Excelで直した数値を無条件にAccessへ戻さない運用にします。出力時には対象期間、取込完了日時、取消の扱いも付け、後から条件を再現できるようにしてください。まず一つの集計を並行運用し、件数と金額を検算してから対象を広げると移行しやすくなります。

まとめ

Access集計フォームの価値は、範囲指定を減らすだけでなく、集計条件と元データを共通化できる点にあります。Excelテーブルによる改善も比較し、移行する場合は粒度、取消、期間、取込時点をそろえましょう。サンプルの期待値と実データの件数・金額を照合し、確定値の保存が必要かまで決めると、繰り返し使える集計になります。

Accessの教科書で集計業務の改善を相談する

候補や集計条件が部署ごとに異なる、既存の画面を直せる担当者がいないなど、集計の手順だけでは解決しない場合は、現在のExcelと業務の流れを整理して相談すると検討が進みます。Accessの教科書のお問い合わせで、対象データ、利用人数、困っている操作をお知らせください。

Accessの教科書で集計業務の改善を相談する

関連記事

Accessクエリの演算フィールドの作り方

Accessテーブル作成前の正規化の手順

Accessクエリで並べ替えする方法

Access更新クエリで計算結果を保存する方法

参考情報

［参考1］Microsoft クエリを使用してデータを集計する
https://support.microsoft.com/ja-jp/access/sum-data-by-using-a-query

［参考2］Microsoft ピボットテーブルのデータを更新する
https://support.microsoft.com/ja-jp/excel/refresh-pivottable-data

［参考3］Microsoft ピボットテーブルおよびピボットグラフの概要
https://support.microsoft.com/ja-jp/excel/overview-of-pivottables-and-pivotcharts

［参考4］Microsoft Form.Requery
https://learn.microsoft.com/en-us/office/vba/api/access.form.requery

［参考5］Microsoft PARAMETERS declaration
https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/parameters-declaration-microsoft-access-sql

TOP