Excelで合計金額が一定以上の取引先だけを抽出するには？
明細の金額と取引先別の合計では絞り込みの順序が異なります。先に合計を作ってから金額条件を指定する方法を具体例で説明します。
合計金額が100000以上の取引先を抽出するには、先に取引先別に合計し、その合計に条件を指定します。個々の明細を100000以上で絞ってから合計すると、少額の取引を積み重ねた取引先を見落とします。JOIN SHEETでは「集計」の下に「絞り込み」を置きます。
同じ条件でも適用する位置で結果が変わる
架空の明細ではA社が60000と70000、B社が40000と50000、C社が110000です。A社の各取引は基準未満でも、合計130000は条件を満たします。
| 取引先 | 金額 |
|---|---|
| A社 | 60000 |
| A社 | 70000 |
| B社 | 40000 |
| B社 | 50000 |
| C社 | 110000 |
図はこの架空データを使った説明図です。実際の操作画面ではありません。
合計を作ってから絞り込む
- シートを結果の基準にし「結果を整理」に「集計」を追加します。「まとめる項目」は取引先です。
- 金額の「合計」を追加し「結果の項目名」を「累計金額」にします。
- 下に「絞り込み」を追加し、項目を累計金額、条件を「以上」、値を100000にします。
- 大きい順で表示するため、その下に累計金額の「降順」の並べ替えを追加して「適用」を押します。
集計後は元の金額列ではなく、新しく作った累計金額を選びます。グループ化の基準と集計値以外の列は、この時点で残っていません。
A社とC社の2行が残る
| 取引先 | 累計金額 |
|---|---|
| A社 | 130000 |
| C社 | 110000 |
残った金額の合計は240000です。元の合計330000との差90000は、条件に合わないB社が除かれた分です。件数だけでなく、この差額も確認すると意図した抽出か判断できます。
完了済みの取引だけ合計する場合は？
その場合は状態が完了の明細を先に絞り込みます。さらに合計金額の条件も必要なら「完了状態で絞り込み → 取引先別に合計 → 合計金額で絞り込み」の順です。明細の条件と集計後の条件を分けて考えると順序を決められます。
結果の整理は上から順に全結果へ適用され、Excel保存にも反映されます。既存のシート条件で明細が除外されていないか、金額が数値として扱われているかも確認してください。基本の集計設定は項目別の合計で説明しています。
繰り返しの作業を減らす
シートを接続・比較して、必要な結果を確認できます。
この記事はProプランの利用環境を前提としています。
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
Excel pivot table does not filter — Stack Overflow
集計値の絞り込みで意図した結果にならない問題を参考にしました。原文のデータや回答は複製せず、集計前後の違いを独自の例で説明しています。
