条件を満たす行が1件でもあればグループ全体を残す
条件に合う行からグループの一覧を作り、元の明細に結合します。たとえば30以上の値が1件でもあるグループなら、30未満の明細もまとめて残せます。
30以上の値が1件でもあるグループについて、ほかの明細もすべて確認したいとします。値が35・5のAは残し、20・20のBは除く、という条件です。まず条件に合う行からグループの一覧を作り、絞り込む前の明細に結合します。
35と5は残し、20と20は除く
架空のDetailsでは、各行のValueが30以上かを調べます。Aには35のR1があるため、同じグループのR2も対象です。Bは合計40でも、30以上の行はありません。Cの30は条件を満たします。この後、条件に合うグループを1行ずつにまとめたEligibleという一覧を作ります。
Details
| Group | Record | Value |
|---|---|---|
| A | R1 | 35 |
| A | R2 | 5 |
| B | R3 | 20 |
| B | R4 | 20 |
| C | R5 | 30 |
対象グループの一覧を先に作る
- Detailsだけを読み込みます。Valueの元データ条件で30「以上」を設定し「適用」を押します。この最初の作業ではR1・R5の2行だけが残ります。
- 「結果を整理」で「集計」を追加し、「まとめる項目」にGroupを選びます。「集計方法」は「件数」のまま、「結果の項目名」にQualifyingRowsと入力します。複数の構成行が条件に合っても、グループごとに1行にします。
- 「適用」を押し、Group・QualifyingRowsの2列、A=1・C=1の2行を確認します。AとCの全明細を元表から取り出すための一覧として、EligibleというExcelファイルに保存します。QualifyingRowsはグループ全体の行数ではなく、条件を満たした行の件数です。
- 新しい作業で未絞り込みの元のDetailsと保存したEligibleを読み込み、Group同士を「一致する行のみ」で結びます。この作業のDetailsにはValue ≥ 30を指定しません。
- Eligible側のGroupだけを非表示にし、QualifyingRowsを残します。「結果のプレビュー」でR1・R2・R5の3行を確認して保存します。値5のR2も必ず含めます。
基準未満のR2が残る理由
R2は30未満でも、同じグループAのR1が基準を満たすため残ります。Bの2行は合計40でも、どちらも30未満なので除かれます。QualifyingRowsは、そのグループで条件を満たした行数です。Aの明細には、どちらにも同じ1が付きます。
| Group | Record | Value | QualifyingRows |
|---|---|---|---|
| A | R1 | 35 | 1 |
| A | R2 | 5 | 1 |
| C | R5 | 30 | 1 |
最初の条件を最終結合に持ち越さない
最後の結合で元の表にもValue ≥ 30を指定すると、残したかったR2が消えてしまいます。また、集計せずに条件に合う明細を直接結ぶと、該当行が複数あるグループで結果の行数が増える場合があります。Groupは空欄にせず、表記をそろえてください。元データを更新したときは、Eligibleの作成からやり直します。
自分のファイルで始めましょう
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
I have a pivot table with different groups, i want to filter on whole groups if it meets a condition. (Reddit r/excel)
原文はグループの合計ではなく、構成行の1つが30以上なら残りの行もすべて保持する方法を尋ねています。 上の表と手順は、この質問を参考に別途作成した架空の例です。質問者の実データや製品の利用体験ではありません。
