[EXCEL] 生年月日一覧から年代別集計を出す ～公務員に必要なエクセルのスキル　集計実践編～

▶目次 > 集計 > 集計実践

生年月日のデータを元に、年代別人数を集計します。（「こんなの、生成AIで出来るよ」って言われるかもしれませんが）
以下、常体にて失礼。
＊2026/2/2 PDF版を掲載しました

[想定]

「あ」～「え」の５団体の一覧表（Copilotにて作成）から、「団体別」の「年代構成」を集計する。
＊データは団体順に並んでいる必要はない。
＊個人情報取得の取得の是非は置いておく。

[手順]

① 年齢を出す（DATEDIF関数）
② 年代を出す（INT関数）
③ 団体×年代別に集計する（COUNTIFS関数）

[手順１]　事前準備

データを集計できる数値になおしておく、

① 基準日欄の作成

冒頭に空白行を２行作る。
A列選択 ⇒ 右クリック ⇒ 挿入
又は A列選択 ⇒ Ctrl + Shift + ;　等

②「基準日」を入れる

生年月日の上に「基準日」を入れる欄を作り、基準日を入れる。
　右図の場合　R8年3月31日
＊基準年はこの場所以外も可。

③ 満年齢を出す

　（上図参照）
E列を「年齢」として、E4セルに次の数式を入れる。
=DATEDIF(D4-1,$D$1,"Y")　
＊D1をクリックしたら、F4を押し「絶対参照」にしておく。
数式の意味： D4（生年月日）の前日（D4-1）を起算日とし、D1（基準日）までの満年数(“Y”)を出す。
★重要★満年齢は誕生日前日に到達するので、生年月日-1とする（生年月日を起算日とするため）
参考記事：［Excel］誕生日の前日に年を取るのはなんで？、と思った方へ（WEB記事やエクセル本は不正確なものが多いので注意）

④ 数式のコピー

E3セルの右下にカーソルを当て、「＋」が出たらダブルクリック
⇒ 下のセル全てに数式が入る
（フラッシュフィル機能による）

⑤ コピーした数式の確認

E5セルを選択 ⇒ Alt ⇒ Ｍ ⇒ Ｐ で
「参照元のトレース」を表示する
⇒ 下図のように出れば問題ない
⇒ ならなければE4の数式を修正
⑥ OKなら Alt ⇒ A ⇒ Aで矢印を消す。

⑦ 年代を出す

F列を「年代」として、F4に次の式を入れる。　=INT(E4/10)*10
＊数式の説明：　満年齢（E４）を10で割り、INT関数で小数点以下を切り捨て、10を掛けて年代を出す。
＊四捨五入だと、46歳は50代になるので切り捨てる。
　例：46歳なら、46÷10で4.6、INTで４になり（少数点以下切り捨て）、10を掛けて40になる。
＊=LEFT(E4,1)*10では10歳未満が計算できない。

⑧ 数式のコピー

F4セルの右下にカーソルを合わせ、「＋」が出たらダブルクリック
⑨ 下のセルに数式がコピーされ、年代が表示される。

[手順２]　集計する

① 集計表作成

団体名×年代の集計表を作る（本来は別シートに作る。下例では説明用に同一シート上に作成）
＊年代は数値だけにする（「20代」としない。後で「セルの書式設定」で「代」を付ける）。

② 数式入力

J４欄に次の数式を入れる　　=COUNTIFS($B:$B,$I4,$F:$F,J$3)
数式の意味：B列（団体）にI4セル（「あ」）と同じデータがあり、かつ、F列（年代）にJ3セル（20代）と同じデータがあるものの数を数えて表示する（=結果を返す）。

 
【COUTIFS関数】
特定の条件に合致するセルの数を数える
=COUNTIFS(検索条件範囲①,検索条件①,検索条件範囲②,検索条件②・・・)
＊対象範囲を選択したら「, 」を入れて、検索条件のセルをクリック。「,」を入れて次の検索条件範囲を選択・・・を繰り返す（最後のカッコは不要。Enter押下で自動で付く）
＊検索条件範囲は列全体を選択できる（データ数が多い場合は不適。動作が重くなる）
＊検索条件は１つでも構わない（従って、COUNTIF関数を使う必要はない）。

注意：他セルへコピーしても変わらないよう、B列（団体）、F列（年代）には「$」が付く「絶対参照」にする。
＊I4セルは列番号が変わらないよう、I （アイ）のみ「$」を付ける（行番号は変わるように「$」を付けない）。
＊J3セルは行番号は変わらないよう、 3 のみ「$」を付ける（列番号は変わるように「$」を付けない）。

【絶対参照と相対参照】
・行または列に「$」が付くと、コピーしても変わらない（行・列ともについているとセル番地は変わらない
・「$」はＦ４を押すことで付き方が変わる。例　A1セルの場合：A1 ⇒ $ A $ 1 ⇒ A$1 ⇒ $A1 ⇒ A1 ⇒ 繰り返し

③ 数式のコピー

J3セルをコピー（Ctrl＋C）し、他のセルを範囲指定(Shift+↓→）⇒貼り付け（Ctrl＋V）

④ 合計を出す

上の③から続けて、計の行・列を選択（Shift+↓→）し、Altl + Shift + =（同時押し）で縦横計が出る。

⑤ 数式の確認

J3セル以外（下図では、O８）を選択し、Alt ⇒ M ⇒ P で「参照元のトレース」を表示する。
＊下図のように出ていれば問題ない。誤っていたらJ3セルを修正して再度コピーする。

⑥ 検算

データに欠損や不備があると正しい集計にならない。
「団体」「年代」それぞれで集計を出し、集計表の計と合っているか確認する。　
＊データが多い場合は =計のセル=検算のセル 等を使う（結果が「TURE」なら合致、「FALSE」なら相違）。

「団体」の計を出す数式（「あ」の場合）　=COUNTIFS(B:B,I4)
＊縦にコピーするので絶対参照は不要

「年代」の計を出す数式（「20」の場合）　=COUNTIFS($F:$F,J3)　
＊横にコピーうするので絶対参照は必要

[手順３]集計表を整える

① 項目の「年代」の数値に「代」を付ける

「セルの書式設定」(Ctlr+1) ⇒「表示形式」⇒「ユーザー定義」⇒「種類」欄 ⇒ #代　と入れ、OK押下
＊「#」に年代の数値が入る。
＊必須ではないが「代」があった方が分かりやすい。
＊セル内データは数値のまま、表示は「20代」となる。
＊セルに「20代」等と入れると、一覧表の「年代」との突合が出来なくなる。

②「0」を表示しない場合

表内（合計欄を除く）を選択し、「セルの書式設定」（Ctrl＋1）を開く。
「表示形式」⇒「ユーザー定義」⇒「種類」欄に　[#,###」と入力⇒OK⇒「0」が表示されない。
＊個別のセルは「０」を表示せず、合計欄のみ「０」を表示させる。合計欄にも「０」が入っていないと、データが入っていない（未入力／入力漏れ）と取られる場合があるため「計」「合計」欄は「０」であることを示すため「０」を表示する。ただし、「０」が多数となる場合は見づらいので表示しないことも一案。

以上

・2026/2/2 PDF版を掲載しました
・2026/1/13追記）基準日は3/31がいい理由を掲載しました