グループごとに最も多い値を、同数のものも含めて取り出すには？
グループ別に各値が何回現れるかを数え、最も多い回数の値を取り出します。件数表と最大件数の表を結合することで、同率の値もすべて残せます。
グループ内で最も多く現れる値を「最頻値」と呼びます。同じ回数の値が複数ある場合も、すべて取り出せます。JOIN SHEETに最頻値の専用ボタンはないので、値ごとの件数を集計し、そのグループの最大件数と結合して求めます。
同率と1件だけのグループ
架空の表のGroupは所属グループ、Bonusは観測した数値です。空欄はなく、同じ内容の行もそれぞれ1回の観測として数えます。
| Group | Bonus |
|---|---|
| A | 100 |
| A | 100 |
| A | 200 |
| A | 200 |
| B | 100 |
| B | 300 |
| B | 300 |
| C | 50 |
Aでは100と200が2回ずつ現れます。Bの最頻値は300、Cには50だけがあります。Bonusの最大値を選ぶ操作とは、求める結果が異なります。
件数表と最大件数表を保存する
- 元の表を読み込み、結果の基準に選んで「結果を整理」を開きます。「集計」の「まとめる項目」にGroupとBonusを指定します。
- 最初の集計の「集計方法」は「件数」のままにし、「結果の項目名」にFrequencyと入力します。「適用」を押すと、Aの2つの値はどちらも2件です。「結果を保存」で件数表を保存します。
- 結果の整理を開き直し、最初の集計の下にもう1つ「集計」を追加します。今度はGroupだけでまとめ、Frequencyの「最大値」をTopFrequencyにします。
- 「適用」後にA=2、B=2、C=1を確認して別ファイルに保存します。最初の件数表も残してください。
最初に保存する表は次の5行です。
| Group | Bonus | Frequency |
|---|---|---|
| A | 100 | 2 |
| A | 200 | 2 |
| B | 100 | 1 |
| B | 300 | 2 |
| C | 50 | 1 |
グループと件数の両方で結合する
保存した件数表には値と出現回数、最大件数表には各グループで一番多い回数が入っています。新しい作業に両方を読み込み、Group同士とFrequency・TopFrequencyを接続して「一致する行のみ」を選びます。グループが同じで、出現回数もそのグループの最大件数と一致する値だけが残ります。
結果には件数表のGroup、Bonus、Frequencyを表示します。最大件数表の重複するGroupとTopFrequencyは、表示を隠しても結合の基準として使えます。
Aの2値とCの1値を確認する
| Group | Bonus | Frequency |
|---|---|---|
| A | 100 | 2 |
| A | 200 | 2 |
| B | 300 | 2 |
| C | 50 | 1 |
結果は4行です。この後Aを1行にまとめ直すと、同率の最頻値を失うことがあります。1つに絞る必要がある場合は、最小の数値や最新の記録など、別の選択基準を決めてください。
Cのように1件しかデータがないグループでは、その値を最頻値として残します。空欄も1つの値として数えるかどうかは、作業前に決めておきましょう。除外するなら最初の集計より前に元データの条件で外し、保存する2つの表で対象をそろえます。
自分のファイルで始めましょう
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
How to Group by the Most Common Valueの質問を参考に、架空のデータを用意しました。
