生年月日から星座を自動判定して人数を数えたい——IF関数を12個もネストして挫折した方へ。コピペできる関数1つで完結します。所要5分。
- 目的：生年月日リスト → 星座を自動表示 → 星座別の人数を集計
- 結果イメージ：A列に生年月日、B列に星座が自動で並び、12星座の人数表が出る
① 結論（最短手順）
まず「動くもの」を。次の式を B2 に貼り、変換表をE・F列に置くだけです。
数式（B2に貼り付け）
=IF(A2="","",VLOOKUP(MONTH(A2)*100+DAY(A2),$E$2:$F$14,2,TRUE))⚠️ 最初の警告（つまずく4点）
- A列は日付型であること（左揃え＝文字列でNG／対処は④）
- 変換表は昇順で並べる（VLOOKUPの近似一致
TRUEは昇順が前提） - 表の範囲は
$で固定（コピー時のズレ防止） - 空白セルの誤カウントに注意 →
IF(A2="","",…)が必須の保険
空白セルは内部的に
1900/1/0と解釈され、式が100＝「山羊座」を返してしまいます。IFラッパーでこれを防ぎます。
変換表（E2:F14）
E列に境界値、F列に星座名を入力します。
| E列（境界値） | F列（星座） |
|---|---|
| 0 | 山羊座 |
| 120 | 水瓶座 |
| 219 | 魚座 |
| 321 | 牡羊座 |
| 420 | 牡牛座 |
| 521 | 双子座 |
| 622 | 蟹座 |
| 723 | 獅子座 |
| 823 | 乙女座 |
| 923 | 天秤座 |
| 1024 | 蠍座 |
| 1123 | 射手座 |
| 1222 | 山羊座 |
Excelに直接コピペ用（E2 を選んで貼り付け＝下記のタブ区切りが2列に展開されます）：
0 山羊座
120 水瓶座
219 魚座
321 牡羊座
420 牡牛座
521 双子座
622 蟹座
723 獅子座
823 乙女座
923 天秤座
1024 蠍座
1123 射手座
1222 山羊座数える（人数集計）
=COUNTIF(B:B,"牡羊座")これで完結です。理由・詳しい手順・代替式は以下に。
② 原因（なぜ難しいのか）
- 星座は「月日」だけで決まり「年」は無関係。日付シリアル値のまま比較できない。
- 山羊座が年をまたぐ（12/22〜翌1/19）ため、単純な大小比較が崩れる。
- IF12個ネストは複雑すぎてミス・メンテが困難。
→ そこで月日をMMDD数値（例：3/25→325）に変換し、境界値の表で一発判定します。年も年またぎも気にせず判定できます。
③ 解決方法（手順）
- A列に生年月日を入力（A1見出し、A2からデータ）。形式は
1990/3/25のように日付として認識される形に。 - 変換表を①のとおりE2:F14に入力（タブ区切りブロックを貼ると速い）。境界値は昇順、先頭は
0→山羊座。 - B2に結論の数式を貼り付け。
- B2の右下■をダブルクリック → 最終行まで自動コピー。全員の星座が一瞬で埋まる。
- 集計は下の2方式から選ぶ。
方式A：一瞬で集計したい人 → ピボットテーブル【おすすめ】
- B列（星座）を含むデータ範囲を選択
- 「挿入」→「ピボットテーブル」→ OK
- 「星座」を行へドラッグ、もう一度「星座」を値へドラッグ（自動で「個数」になる）
→ 星座別人数が即完成。データ追加時は右クリック→「更新」だけ。12個の関数を手入力する必要がありません。
方式B：表に残したい人 → COUNTIF
H列に星座名を12個並べ、I列に次を入れてI13までコピー：
=COUNTIF($B:$B,H2)④ 数式の意味とエラー対処
数式の挙動
MONTH(A2)*100+DAY(A2)… 月×100＋日でMMDD数値化（3/25→325）。VLOOKUP(…,$E$2:$F$14,2,TRUE)…TRUE（近似一致）は「探す値を超えない最大の境界」を拾う。325は321（牡羊座）と420の間なので牡羊座。IF(A2="","",…)… 空白なら空白を返し、誤カウントを防ぐ。
トラブル対処チェックリスト
| 症状 | 原因 | 対処 |
|---|---|---|
#N/A が出る | A列が文字列／TRUE抜け | =DATEVALUE(A2) で日付化。式末尾が ,2,TRUE) か確認 |
| 全員同じ星座 | MMDD式の誤り／表が昇順でない | 別セルで =MONTH(A2)*100+DAY(A2) を検算。表を昇順に |
| 空白が山羊座になる | IFラッパー未使用 | 結論の IF(A2="","",…) 付き式に差し替え |
| COUNTIFが0 | 星座名の表記ゆれ・空白 | 判定結果をコピーして集計表に貼る／=TRIM(B2) で空白除去 |
| エラーを隠したい | — | =IFERROR(元の式,"") で丸ごと保護 |
日付文字列対策（補助列）
A列が文字列のときは、補助列で日付に変換してから判定すると安全です。
=IFERROR(DATEVALUE(A2),"")または列を選び「データ」→「区切り位置」→完了で一括変換も可能。
⑤ 代替式の比較
| 方法 | 長所 | 短所 |
|---|---|---|
| VLOOKUP（近似） | シンプル・互換性高い | 表が昇順必須 |
| XLOOKUP | 可読性高い・列指定が直感的 | 旧Excel非対応（365 / 2021〜） |
| INDEX+MATCH | 柔軟・堅牢（列の左右自由） | 書き方がやや複雑 |
使い分け一行ルール：最新Excelなら XLOOKUP、古い環境も配るなら VLOOKUP、表のレイアウトが自由なら INDEX+MATCH。
XLOOKUP（Microsoft 365向け）
=IFERROR(XLOOKUP(MONTH(A2)*100+DAY(A2),$E$2:$E$14,$F$2:$F$14,,-1),"")-1 ＝「完全一致または次に小さい値」。VLOOKUPの TRUE と同じ動作。
INDEX+MATCH（互換性◎）
=IF(A2="","",INDEX($F$2:$F$14,MATCH(MONTH(A2)*100+DAY(A2),$E$2:$E$14,1)))MATCHの 1 ＝昇順データから「探す値以下の最大」を返す。
MMDDを直感的に作る別法
「なぜ×100？」が分かりにくい人向け。日付を「0325」の4桁文字にしてから数値325へ戻す考え方です。
=VALUE(TEXT(A2,"mmdd"))VLOOKUPの第1引数を丸ごとこれに差し替えてもOK（※この式も空白時は100を返すのでIFラッパーは必要）。
⑥ 関連トラブル・補足
- 境界日が流派で1日ずれる場合：変換表の境界値（例
321→322）を書き換えるだけで全件に反映。 - うるう年：月日判定なので 2/29 生まれも問題なし（魚座）。
- すぐ試したい：下のサンプルCSVをコピー → メモ帳に貼り
.csv保存 → Excelで開き、B2に結論の式を貼って動作確認。
最小サンプルCSV（空白セルの検証用に1行空欄を含む）
氏名,生年月日
佐藤一郎,1990/3/25
鈴木花子,1985/12/22
高橋健,2001/1/5
加藤さくら,最終行は生年月日が空欄。IF(A2="","",…) 付きの式なら、この行は「山羊座」にならず空白のまま返ります。
実装チェックリスト（最終確認）
- A列が日付型（右揃え）になっている
- 変換表E2:F14が昇順、先頭は
0→山羊座 - 数式の範囲が
$固定 - 数式に
IF(A2="","",…)が入っている（空白対策） - COUNTIFの星座名が判定結果と完全一致（
TRIMで空白除去）
これで、生年月日からの星座自動計算と人数集計が、データが増えても貼り付け＋「更新」だけで回り続けます。
※星座の境界日は採用する流派により1日前後する場合があります。掲載基準に合わせて変換表の境界値を調整してください。
コメント