Excelのピボットテーブルを使って条件付き累計を計算する方法 - TechRepublic

ランドスケープデザインの XLS アイコンのベクター。ダウンロードボタン付きのスプレッドシート形式のファイル。デジタルシート。データ処理アプリケーション。 — 画像: アフマド/Adobe Stock

Excelで単純な累計を返す式は簡単です。いくつかの参照だけで完了します。条件付き累計はもう少し手間がかかります。式自体は難しくなく、専門知識もそれほど必要ありませんが、特に結果を報告する場合は、必要以上に手間がかかることがあります。この記事では、Excelピボットテーブルを使って、式を一切使わずに条件付き累計を計算する方法を説明します。ピボットテーブルで単純な累計が作成できれば、条件付き累計は適切なフィールドをグループ化するだけで簡単に作成できます。

参照:ソフトウェアインストールポリシー(TechRepublic)

Windows 10 64ビットシステムでMicrosoft 365を使用していますが、2007までの以前のバージョンの.xlsx形式も使用できます。Web版Excelはピボットテーブルオブジェクトをサポートしています。ご参考までに、.xlsxデモファイルをダウンロードできます。

ピボットテーブルオブジェクトにまだ慣れていない場合は、「Excelのピボットテーブルツールを使ってデータを意味のある情報に変換する方法」をお読みください。シートレベルで条件付き累計を生成する方法を知りたい場合は、「Excelの売上シートで条件付き累計を計算する方法」をお読みください。

累計とは何ですか?

累計は当座預金口座の残高に似ています。入金と出金が行われ、それぞれの取引が口座残高、つまりこの場合は特定の日における累計に加算されます。厳密に言えば、累計とは、一定期間内の現在の取引だけでなく、過去の取引も含めた累計額です。

Excel の式を使用して Excel シートで累計を返すことができますが、Excel のピボットテーブルは、数学を知る必要がないため、式よりも速い可能性があります。ピボットテーブルには累計オプションがあります。

データ

図Aは、6人の従業員の3ヶ月間のコミッションを示すシンプルなシートです。右側のテーブルには、「金額」列と「コミッション」列の累計が表示されています。シートには2つのテーブルオブジェクトがあります。4列のテーブルは「コミッション」、2列のテーブルは「ランニング合計」という名前です。

図A

G3 と H3 の式は、各列の最初の値への単純な参照です。

G3: = C3

H3: = D3

G4 と H4 の合計計算式は次のとおりです。

G4: =Commission[@Amount]+G3
H4 =Commission[@Commission]+H3

または

G4: =G4+G3
H4: =H4+H3

Table オブジェクトを使用していない場合。

その後、それらの式が残りのセルにコピーされます。構造化参照式と単純参照式は同じで、参照の種類が異なるだけです。Tableオブジェクトで作業していない場合は、単純参照式を使用してください。

それでは、ピボットテーブルを作成しましょう。

Excelでピボットテーブルを作成する方法

まず、図Aに示すRunningTotalsテーブルとほぼ同じ結果を表示する、コミッションテーブルに基づいたシンプルなピボットテーブルを作成します。まず、コミッションテーブル（4列）内の任意の場所をクリックし、以下の操作を行います。

[挿入] タブをクリックし、[テーブル] グループの [ピボットテーブル] をクリックします。
表示されるダイアログで、[既存のワークシート] をクリックすると、データとピボットテーブルが表示されます。
場所コントロール内をクリックし、シート内の J2 をクリックします (図 B )。

図B

「OK」をクリックします。最初はピボットテーブルは空白です。
フレームをクリックすると、ピボットテーブルフィールドウィンドウが表示されます。表示されない場合は、フレームを右クリックし、表示されるダイアログから選択してください。
図 C をガイドとして使用し、フィールドを下のリストにドラッグします。

図C

金額合計列の任意のセルを右クリックし、表示されるサブメニューから値フィールドの設定を選択します。
[値の表示形式] タブをクリックし、[値の表示形式] ドロップダウンから [実行合計] を選択します。
「ベースフィールド」リストで「金額」が選択されていることを確認します (図 D )。

図D

[数値書式] ボタンをクリックし、[カテゴリ] リストから [通貨] を選択します。
[OK] を 2 回クリックして、図 Eに示すピボットテーブルに戻ります。

図E

ピボットテーブルフィールドウィンドウで「金額」フィールドを「手数料」フィールドに置き換えることで、「手数料」のピボットテーブルを作成できます。現状では、Excel が名前と金額をどのように並べ替えたかを判断するのは困難です。「ピボットテーブルフィールド」ウィンドウの「行」リストにおけるフィールドの順序に注意してください。これは後で再び取り上げます。グループ化を行う際には、フィールドの位置が重要になります。

フィールドを移動し始めると、ピボットテーブルはグループ化を開始します。これが次に確認したい内容です。

Excelで条件付き累計を計算する方法

この時点では、あまり得るものがありません。ピボットテーブルは、テーブルとそのシンプルな式と比べてそれほど優れているわけではありません。条件付き累計を計算したい場合、難しさを感じます。例えば、列全体の値ではなく、各人の累計を返す式を作成するには、ある程度のノウハウが必要です。一方、ピボットテーブルでは、少しの並べ替えだけで済みます。

参照: Microsoft Lists が新しい Excel である理由 (TechRepublic)

まず、図Fは行リスト内の2つのフィールドを並べ替えた結果を示しています。片方のフィールドをクリックし、もう片方のフィールドの上または下にドラッグするだけです。ピボットテーブルは各人の累計を返すだけでなく、グループを分かりやすく並べ替えます。

図F