Excelのピボットで費目別金額と構成比を横に並べる手順

1．何に対する割合なのかを先に決める

ピボットテーブルの動作は利用するプラットフォームによって少し異なるため、以下はWindows版の公式手順を基準に整理します。値フィールドの計算を説明するヘルプの適用先は、Excel for Microsoft 365、Excel for Microsoft 365 for Mac、Excel 2024、Excel 2024 for Mac、Excel 2021、Excel 2021 for Mac、Excel 2019、Excel 2016 です。

ここでは、費目別の合計ができている表から進めます。見たいのは、集計した経費全体の中で各費目が占める割合です。予算額に対してどこまで使ったかを求める計算とは、分母が異なります。対象期間と含める明細を決め、比較する金額の意味をそろえてから作業に入るとよいです。

［行］のフィールドは左側の行ラベル、［値］のフィールドは集計された数値として表示されます。この配置の考え方に沿って、費目を縦に読み、金額と割合を横に見比べる表にします。元の費目名や明細を直す作業は、表示を追加する作業と分けて進めます。

2．金額を残し、追加した側だけ割合へ変える

同じ金額を値の領域へ重ねる

フィールド リストで、「金額」を、すでに金額が入っている［値］領域へドラッグし、元の金額フィールドのすぐ下へ置きます。追加する対象は、元の合計と同じ金額欄です。別の金額欄を選ぶと、隣に並んでいても比較の前提がそろいません。追加前に元の合計を控え、表示を変えた後も照合できるようにしておきます。

追加した側の計算を切り替える

追加した側の値を右クリックして［値の表示の種類］を開き、使う計算オプションを選びます。［総計に対する比率］は、レポート全体の総計に対して各値が占める割合を表示する設定です。元の金額欄までまとめて変えず、追加した側を対象にしているかを、開いた位置と列名で見直すとよいです。

金額を残す側は［計算しない］にすると、フィールドに入力された値の表示を保てます。金額が消えて割合だけになったときには、追加した側と元の側を取り違えていないか、作業前の控えと比較します。

列の意味を読めるようにする

追加後のフィールド名は編集できます。名前は「金額合計」「構成比」など、読む人が内容を区別できるものにするとよいです。［値フィールドの設定］では、［表示形式］からフィールド全体の表示形式を変更できます。見た目を整える前に、金額を示す欄と割合を示す欄が分かれているかを確認します。列名だけを付け替えて済ませず、選んだ計算と名前の意味を一致させることが大切です。

3．小さな内訳表で分母のそろい方を見る

ここからは当社の整理です。

費目別の支出を共有する場面では、次のような読み方になります。金額の大小と、全体の中で占める位置を、同じ行で確認するための見本です。表内の「対象全体」は、含める明細をそろえた経費全体を指します。

見比べたい費目が変わっても、分母の意味はそろえておきます。資料には対象期間と集計の対象を短く添えると、受け取った人が割合の意味を読み違えにくくなります。割合の大きさだけから支出の妥当性を判断せず、必要な理由や明細の内容は担当者と確かめます。

4．割合が意図と違うときは計算の基準を見る

Microsoft のヘルプの説明では、［列集計に対する比率］は列や系列の合計、［行集計に対する比率］は行や項目の集計を分母にします。集計全体の内訳を求める今回の目的と、選んだ項目の分母が合っているかを確認します。似た名前の項目が並んでいても、表の見た目だけを頼りに選ばないほうがよいです。

列の並びも見直します。同じ領域内の表示順は、フィールドを必要な位置へドラッグして変更できると、Microsoft のヘルプにあります。ここでは金額の後に割合が続く順を目指し、読みたい費目と対応しているかを見比べます。

Mac版で目的の計算がメニューに見当たらない場合は、［その他のオプション］を選ぶ案内が Microsoft のヘルプにあります。引き継ぎのメモには、使った環境と選んだ計算名を残すと、受け取った人も確認先を探しやすくなります。

5．金額の照合と割合の検算を分けて終える

Microsoft のヘルプでは、集計結果を手動で更新するときは、ピボットテーブルを右クリックし、［更新］を選ぶと案内されています。元の明細を変更した後は、表示された結果を見直してから資料として共有するとよいです。

まず、元の明細から対象の費目に該当する支出を拾い、金額の合計を突き合わせます。次に、その金額を対象全体の合計で割り、割合の欄と比べます。金額が合っていることと、意図した分母で割合を求めていることを、別々に確かめる進め方です。

差があるときは、対象期間、含めた明細、費目の分類を順に見直します。比べている相手の対象がずれたまま表示だけを直しても、照合は終わりません。原因が分からない明細は、割合を合わせるために書き換えず、記入者へ戻して確認します。

引き継ぎでは、元の資料の保存場所、集計の対象、分母の意味、照合を終えた箇所を短く残します。後から同じ対象をたどり、金額と割合の対応を説明できる状態が、今回の作業の区切りになります。

6．確認表

公開・共有の前に、この表で確かめます。

7．自社の事務の手順まで形にしたいとき

ここまでの確認を、自社の事務の手順書や、AIの結果を確かめる検査まで形にしたい場合は、当社のように、手順と検査の設計まで行う相談先が選択肢になります。オンラインは全国から対応しています。

自社の事務のどこから確認を入れるかを相談したい場合は、初回無料相談をご利用ください。法律・税務・労務の判断や、効果の約束は行いません。