同一人物IDが複数行に分かれたExcelデータは、平均寿命や職業別人数を出すと「人」ではなく「行」で計算され、集計結果がズレがちです。元データ（出典行）を潰さずに、人物単位で重複排除しながら集計するための現実的な数式パターンと設計をまとめます。
同一IDが複数行に分かれるデータで起きる「集計ズレ」
歴史人物データのように、同じ人物（同一ID）でも「情報源（ソース）ごと」に行が分かれている表は、データとしては健全です。出典管理ができるからです。ところが、Excelでそのまま平均や件数を取ると、次のような問題が起きます。
- 件数を数えると「人物数」ではなく「行数」になる（同一人物が複数回カウントされる）
- 平均寿命を取ると「人の平均」ではなく「行の平均」になる（同一人物の寿命が複数回加算される）
- 職業別に集計すると、複数行の人物が特定職業に偏っている場合、平均・人数が歪む
例えば、同一人物が2行に分かれているだけでも、平均は簡単にズレます。
| ID | 人物名 | 職業 | 寿命 | Location | Source |
|---|---|---|---|---|---|
| A001 | Person A | Carpenter | 80 | UnitedStates/NY | Source 1 |
| A001 | Person A | Carpenter | 80 | UnitedStates/NY | Source 2 |
| B010 | Person B | Painter | 60 | France/Paris | Source 3 |
このとき、寿命列をそのまま平均すると (80 + 80 + 60) / 3 = 73.33… です。しかし「人物単位」で平均を取りたいなら (80 + 60) / 2 = 70 が正しい、という状況になります。
つまり、やりたいことはシンプルで、考え方は次のどちらかに集約できます。
- 先にIDを一意化（重複排除）してから集計する
- 「この行は人物を代表する1行目だ」というフラグを作って、そのフラグだけ集計する
まず押さえるべき実務設計：元データを残し、集計は別レイヤーで作る
結論から言うと、元データ（出典行）は消さず、集計用の仕組みを追加するのが最も安全です。おすすめは次の3層構造です。
| 層 | 役割 | 具体例 | 編集方針 |
|---|---|---|---|
| Raw（元データ） | 出典管理・データの正本 | Sourceごとの行が並ぶ表 | 基本的に消さない（追記・訂正は可） |
| Helper（補助列） | 集計を成立させる「旗」や整形 | ユニークIDフラグ、条件フラグ | Rawの隣列に追加 |
| Summary（集計） | 人数・平均寿命・職業別などの結果 | 通常シートの集計表 | 数式のみでOK |
この構造にしておくと、後から「場所条件を足す」「職業分類を増やす」「別の指標（在位年数など）を追加する」といった変更が入っても、破綻しにくくなります。
解決策A：Microsoft 365の動的配列で「IDを重複排除してから集計」
Excel（Microsoft 365）で UNIQUE や FILTER が使えるなら、発想はとても素直です。
- まず IDを一意化（UNIQUE）する
- 必要なら 条件で絞る（FILTER）
- 一意化したIDに対応する値を引いて 平均や件数 を出す（XLOOKUP / COUNTA / AVERAGE）
Mac環境でも、Microsoft 365版のExcelであれば動的配列は基本的に利用できます（環境によって提供タイミングは差があるため、使える関数が揃っているかは関数入力時に候補が出るかで確認してください）。
ユニーク人物数（全体）
最小構成はこれです。
=ROWS(UNIQUE($A$2:$A$100))ただし、ID列に空白が混ざると空白も1件として数えられることがあります。実務では空白を除外する形が安全です。
=LET(ids,FILTER($A$2:$A$100,$A$2:$A$100<>""),ROWS(UNIQUE(ids)))職業別のユニーク人数
例えば、職業名が集計表のセル X2 にあり、RawのIDがA列、職業がF列だとします。
=LET(ids,FILTER($A$2:$A$100,($F$2:$F$100=X2)*($A$2:$A$100<>"")),COUNTA(UNIQUE(ids)))「該当者がゼロ」のときに #CALC! が出る場合は、IFERROR で0に落とすと集計表が崩れません。
=LET(ids,IFERROR(FILTER($A$2:$A$100,($F$2:$F$100=X2)*($A$2:$A$100<>"" )),""),IF(ids="",0,COUNTA(UNIQUE(ids))))平均寿命（全体）
寿命列（例：G列）が「同一ID内では同じ値で揃っている」前提なら、XLOOKUP で一意化IDの寿命を引いて平均できます。
=LET(u,UNIQUE(FILTER($A$2:$A$100,$A$2:$A$100<>"")),lifes,XLOOKUP(u,$A$2:$A$100,$G$2:$G$100),AVERAGE(lifes))重要：XLOOKUP は同じIDが複数行ある場合、基本的に「最初に見つかった1件」を返します。寿命がソースによって揺れる（80と82が混在する等）場合は、このままだと“たまたま先頭行の値”が代表値になってしまいます。揺れ対策は後半で詳しく扱います。
職業別の平均寿命
職業で絞ってから、ユニークIDの寿命を引いて平均します。
=LET(u,UNIQUE(FILTER($A$2:$A$100,($F$2:$F$100=X2)*($A$2:$A$100<>""))),lifes,XLOOKUP(u,$A$2:$A$100,$G$2:$G$100),AVERAGE(lifes))場所条件も追加したい（Locationに “UnitedStates” を含む）
動的配列で場所条件を入れるなら、SEARCH と ISNUMBER を使うと分かりやすいです。Location列がH列として、UnitedStates を含む行だけ対象にします。
全体のユニーク人数（UnitedStates関連人物）
=LET(mask,($A$2:$A$100<>"")*ISNUMBER(SEARCH("UnitedStates",$H$2:$H$100)),COUNTA(UNIQUE(FILTER($A$2:$A$100,mask))))職業X2かつUnitedStates関連人物の平均寿命
=LET(mask,($A$2:$A$100<>"")*($F$2:$F$100=X2)*ISNUMBER(SEARCH("UnitedStates",$H$2:$H$100)),u,UNIQUE(FILTER($A$2:$A$100,mask)),lifes,XLOOKUP(u,$A$2:$A$100,$G$2:$G$100),AVERAGE(lifes))SEARCH は部分一致で、英字の大文字・小文字を区別しません。逆に大文字・小文字を厳密に区別したい場合は FIND を使います（ただし FIND は見つからないとエラーになるので IFERROR を組み合わせることが多いです）。
解決策Aを「使い倒す」ための小技
同じ範囲を何度も書くと式が長くなり、修正ミスも起きやすいです。LET で範囲に名前を付けると、保守性が上がります。
| 目的 | 考え方 | おすすめ |
|---|---|---|
| 全体のユニーク人数 | UNIQUEの件数 | ID空白はFILTERで除外 |
| 職業別ユニーク人数 | FILTER→UNIQUE→COUNTA | 該当ゼロ対策にIFERROR |
| 平均寿命 | ユニークIDに対し寿命を引いて平均 | 寿命が揺れるなら代表値ルールが必要 |
| 場所条件付き | SEARCHで部分一致判定 | mask（条件）をLETで一元管理 |
解決策B：ヘルパー列（ユニークIDフラグ）で「最初の1行だけ集計する」
動的配列が使えない環境でも、最も安定して運用できるのがヘルパー列です。ポイントは1つだけ。
「このIDが初めて登場した行」だけ1にして、以降は0にする。
この“ユニークIDフラグ”があると、以降は SUM や SUMIFS / AVERAGE の考え方で、人物単位の件数・平均が作れます。ピボットに頼らず通常シートの数式で完結し、Macでも扱いやすいのが強みです。
ユニークIDフラグの作り方（最初の出現だけ1）
例として、IDがA列、フラグをK列（K2から）に作るケースです。
=IF(COUNTIF($A$2:$A2,$A2)=1,1,0)$A$2:$A2は「2行目から現在行まで」の伸びる範囲- 同じIDが初めて出た行だけCOUNTIFが1になり、フラグが1
- 2回目以降は2,3…となるので0
このフラグ列ができるだけで、集計が劇的にシンプルになります。
ユニーク人物数（全体）
=SUM($K$2:$K$100)フラグは1か0なので、合計＝ユニーク人物数です。
職業別ユニーク人数
職業がF列で、例えば“Carpenter”の人数を数えるなら次です。
=SUMIFS($K$2:$K$100,$F$2:$F$100,"Carpenter")フラグ列は「人物を代表する1行目」にだけ1が入っているので、職業別でも“人”として数えられます。
職業別の平均寿命（人物単位）
寿命がG列、職業がF列、フラグがK列の場合、人物単位の平均寿命は「寿命の合計（フラグ=1の行だけ）÷ 人数（フラグ=1の行だけ）」で作れます。
=IFERROR(
SUMIFS($G$2:$G$100,$F$2:$F$100,"Carpenter",$K$2:$K$100,1)
/ SUMIFS($K$2:$K$100,$F$2:$F$100,"Carpenter"),
"")IFERROR を入れておくと、対象者が0人のときに #DIV/0! が出て見た目が崩れるのを防げます（0表示にしたいなら空文字の代わりに0にします）。
ヘルパー列方式が「現場で強い」理由
| 観点 | 動的配列（解決策A） | ヘルパー列（解決策B） |
|---|---|---|
| 互換性 | Microsoft 365中心 | 幅広いバージョンで安定 |
| 式の見通し | 長くなりやすい | 短い式で分解できる |
| 拡張（条件追加） | mask管理が必要 | フラグ列を増やす／拡張するだけ |
| 大規模データ | 式が重くなることがある | 計算が軽く運用しやすい |
追加要件：Locationに “UnitedStates” を含む人物だけを集計したい
ここが実務で一番つまずきやすいポイントです。「LocationにUnitedStatesを含む行だけ」と言ったとき、意図が2種類に分かれます。
- 行ベースの条件：LocationがUnitedStatesの行だけを見る（＝出典行のフィルタ）
- 人物ベースの条件：UnitedStatesの出典が1つでもある人物を対象にする（＝人物の選別）
今回やりたいのは、集計単位が“人物”なので、後者（人物ベース）の考え方が自然です。つまり「UnitedStatesを含む行が少なくとも1行あるID」を1人として数え、寿命もその人物として1回だけ集計します。
ヘルパー列を拡張して「条件下での初出だけ1」にする
IDがA列、LocationがH列、フラグをK列に置く場合の例です。
=IF(
AND(
ISNUMBER(SEARCH("UnitedStates",$H2)),
COUNTIFS($A$2:$A2,$A2,$H$2:$H2,"*UnitedStates*")=1
),
1,0)この式のポイントは次の通りです。
SEARCHで「含む（部分一致）」を判定（見つかれば数値、見つからなければエラー）ISNUMBERで「見つかったかどうか」をTRUE/FALSEに変換COUNTIFS(...,"*UnitedStates*")で「UnitedStatesを含む行」に限定した上で、そのIDが何回出たかを数える- その回数が1なら「UnitedStates条件の中で初出」なので1、以降は0
このフラグ（K列）ができれば、人数も平均寿命も、先ほどと同じ集計式で「UnitedStates対象だけ」を計算できます。
UnitedStates対象のユニーク人数（全体）
=SUM($K$2:$K$100)職業別UnitedStates対象人数
=SUMIFS($K$2:$K$100,$F$2:$F$100,"Carpenter")職業別UnitedStates対象の平均寿命
=IFERROR(
SUMIFS($G$2:$G$100,$F$2:$F$100,"Carpenter",$K$2:$K$100,1)
/ SUMIFS($K$2:$K$100,$F$2:$F$100,"Carpenter"),
"")フラグが「条件を満たす人物の代表行だけ1」になっているので、人数も平均も“人物単位”を保ったまま条件付き集計ができます。
「UnitedStatesを含む」が表記揺れする場合の実務対策
Locationが United States（スペース入り）だったり、USA だったり、UnitedStates/NY のように区切りが混在することはよくあります。その場合は、次のように「検索語」を統一するか、複数語を許容するマスクにします。
- RawのLocationを整形する補助列（例：空白除去、ハイフン統一）を作る
- 検索語をOR条件にする（例：UnitedStates または USA）
OR条件をフラグに入れる例（UnitedStatesまたはUSAを許容）：
=IF(
AND(
OR(ISNUMBER(SEARCH("UnitedStates",$H2)),ISNUMBER(SEARCH("USA",$H2))),
COUNTIFS($A$2:$A2,$A2,$H$2:$H2,"*UnitedStates*")+COUNTIFS($A$2:$A2,$A2,$H$2:$H2,"*USA*")=1
),
1,0)式が長くなるので、実務では「Location整形列を作る」ほうが保守が楽になることが多いです。
寿命や職業がID内で揺れるとき：代表値ルールを決めないと破綻する
「寿命（G列）がIDごとに同一値で揃っている前提」は、データが理想的な場合に限り成立します。歴史人物データのように、出典によって数値が揺れるケースは珍しくありません。
この状態で XLOOKUP や「先頭行フラグ」を使うと、どの行がたまたま先頭に来たか で平均寿命が変わってしまいます。ここは必ず設計で吸収しましょう。
よくある代表値の決め方
| 代表値のルール | 向いているケース | 注意点 |
|---|---|---|
| 最大値（MAX）を採用 | 複数出典の中で“上限側”を採りたい | 誤って極端な値が混ざると影響が大きい |
| 最小値（MIN）を採用 | “下限側”を採りたい | 欠損を0にしていると崩壊するので注意 |
| 平均（AVERAGE）を採用 | 出典差を平均化したい | 外れ値耐性が弱い（中央値のほうがよい場合も） |
| 優先ソースを固定 | 一次資料など信頼度が明確 | ソース優先順位の管理が必要 |
「揺れ」を検出して、レビュー対象を炙り出す
まずは“揺れているID”を見つけるだけでも、集計の信頼性が上がります。ヘルパー列で手早く検出するなら、同一IDで寿命が違う行が存在するかをチェックできます（寿命がG列の場合）。
=IF(COUNTIFS($A:$A,$A2,$G:$G,"<>"&$G2)>0,"寿命が揺れ","")- 同じIDで、今の行の寿命と異なる寿命がどこかにあれば「寿命が揺れ」
- 揺れがあるIDだけを別途確認し、代表値の決め方（MAX/MIN/優先ソース）を確定する
代表値列を別に作る（おすすめ）
実務では、Rawの寿命列をそのまま集計に使うより、集計に使う“採用寿命”列 を作ってしまうほうが事故が減ります。例えば「IDごとの最大寿命を採用する」なら、各行に次のような列を作れます。
=MAXIFS($G$2:$G$100,$A$2:$A$100,$A2)これで「採用寿命」列はID内で必ず同一値になり、XLOOKUP でもフラグ方式でも安全に集計できます。最小値にしたいなら MINIFS、平均にしたいなら AVERAGEIFS（条件が単一なら）を検討します。
もし「優先ソース」を採用するなら、Source列に優先順位（例：一次資料=1、二次資料=2…）を付ける列を作り、最小順位の行を採用する、といった設計が現実的です（この設計まで進めると、Power Queryや別シートの人物マスタ化がさらに有利になります）。
「ピボットに頼らず通常シートの数式で」集計するコツ
ピボットテーブルは強力ですが、次の理由で「数式だけの集計」を求めるケースは多いです。
- 集計表をレイアウト込みで固定したい（報告書や公開用に整形したい）
- Mac環境で操作感が違い、更新手順が属人化しやすい
- 複数条件（職業×場所×時代など）が増えて、ピボットの設定が複雑になる
数式集計で事故を減らすために、次の3点を意識すると安定します。
データ範囲はテーブル化して「行追加に強く」する
範囲が $A$2:$A$100 のように固定だと、行が増えたときに集計が漏れます。Excelのテーブル（WindowsならCtrl+T、Macなら挿入メニューからテーブル化）にしておくと、参照が自動で伸びます。
テーブル化すると式が読みやすくなり、列名で参照できます（例：[ID]、[Job]、[Lifespan] など）。
フラグ列は「集計ロジックの核」なので、1つ増やす勇気を持つ
「ユニークIDフラグ」1本で多くが解決しますが、条件が増えるなら、フラグを増やすのは悪ではありません。
- ユニークIDフラグ（全体）
- UnitedStates対象フラグ
- 特定年代対象フラグ（年があるなら）
- データ品質チェックフラグ（寿命揺れ等）
この方針にすると、集計表は SUMIFS で読みやすく保てます。
集計表の「再利用」を意識して、条件セルを使う
職業名や場所キーワードを直接式に埋めるより、集計表側のセルを条件にするほうが運用が楽です。
- 職業名：集計表の列見出しセル（例：X2）
- 場所キーワード：集計表の入力セル（例：Y1にUnitedStates）
こうしておくと、後から「UnitedStates→Franceに切り替える」などがワンクリックで済みます。
補足：REGEXTEST（正規表現）でLocationを判定する案について
別案として、正規表現でLocationを判定し、UNIQUE と組み合わせて集計する方法があります。たとえば「UnitedStates または USA」を正規表現でまとめたい、スラッシュ区切りの階層を正確に拾いたい、といったときに便利です。
ただし、正規表現系の関数は提供状況や利用できる環境差が出ることがあります。そのため、互換性重視なら SEARCH（部分一致）＋ワイルドカード のほうが安定しやすい、というのが実務上の判断になります。
最後に：おすすめの選択肢（迷ったらこれ）
- Microsoft 365で動的配列が使え、データ量もそこまで多くない → 解決策A（UNIQUE/FILTER） が速い
- 環境差がある、将来の保守性を重視したい、条件が増えそう → 解決策B（ヘルパー列） が最強
- 寿命や職業がソースで揺れる可能性がある → 先に 代表値ルール（採用寿命列など） を作ってから集計する
- 場所条件の追加が多い → 「条件下で初出だけ1」のフラグ設計にしておくと、集計式はずっと同じで回せる
同一IDが複数行に分かれるデータは、出典管理のために必要な形です。だからこそ、元データは残し、集計側で「人物単位」の考え方を実装するのが、長期的に最も安定します。
コメント