生年月日から干支（十二支）を出すには、=MID("申酉戌亥子丑寅卯辰巳午未",MOD(YEAR(生年月日),12)+1,1) と書きます。西暦を 12 で割った余りで、12 文字の中から 1 文字を取り出す式です。星座は、月と日を「月×100＋日」の数字にして、LOOKUP 関数で区切りの表から探します。
この記事では、生年月日の表を使って、干支・干支の読み・星座・十干十二支（丙午など）を自動で出す式を、実際の Excel で確かめた結果で説明します。どの式も、古い Excel でそのまま使えます。
干支・星座を求める式
=MID("申酉戌亥子丑寅卯辰巳午未", MOD(YEAR(生年月日),12)+1, 1)
=LOOKUP(MONTH(生年月日)*100+DAY(生年月日), 月日の列, 星座の列)| 部品 | 意味 |
|---|---|
| YEAR(生年月日) | 生年月日から西暦の年を取り出す |
| MOD(年,12) | 年を 12 で割った余り（0〜11）。余りが 0 の年は申（さる）年 |
| MID(文字,余り+1,1) | 12 文字の中から「余り＋1」番目の 1 文字を取り出す |
| MONTH(…)*100+DAY(…) | 3月21日なら 321、12月25日なら 1225 のような数字にする |
| LOOKUP(数字,月日の列,星座の列) | 月日の列から「その数字以下でいちばん大きい値」を探し、同じ行の星座を返す |
干支の文字が「申」から始まるのは、西暦を 12 で割り切れる年（2016 年など）が申年だからです。星座の区切りの表は、E 列に始まりの月日（小さい順）、F 列に星座を入れておきます。
1. 生年月日から干支を出す（MID と MOD）
I2 に式を入れて、下へコピーします。B 列の生年月日から年を取り出し、12 で割った余りで文字を選びます。
=MID("申酉戌亥子丑寅卯辰巳午未",MOD(YEAR(B2),12)+1,1)
1990 年生まれは「午」、1985 年生まれは「丑」、2000 年生まれは「辰」になりました。2026 年は「午」です。
この式は 1月1日で干支が切り替わります。年のとちゅうの日付で区切りたいときは、この式では対応できません。
2. 「うま年」のように読みで出す（CHOOSE）
漢字 1 文字ではなく読みで出したいときは、CHOOSE 関数で 12 個の言葉から選びます。番号の決め方は 1 と同じです。
=CHOOSE(MOD(YEAR(B2),12)+1,"さる","とり","いぬ","い","ね","うし","とら","う","たつ","み","うま","ひつじ")&"年"
1990 年生まれは「うま年」、1985 年生まれは「うし年」、1972 年生まれは「ね年」になりました。最後の &"年" を外すと、読みだけになります。CHOOSE の使い方は CHOOSE関数の使い方で説明しています。
3. 生年月日から星座を出す（LOOKUP）
月と日を「月×100＋日」の数字にして、E 列の月日から探します。E 列は 101（1月1日）から始めて、小さい順に並べておきます。やぎ座は年をまたぐので、表の最初と最後の 2 か所に入れます。
=LOOKUP(MONTH(B2)*100+DAY(B2),$E$2:$E$14,$F$2:$F$14)
4月15日は「おひつじ座」、12月25日は「やぎ座」、1月20日は「みずがめ座」、8月23日は「おとめ座」でした。3月20日は「うお座」、3月21日は「おひつじ座」で、区切りの日で切り替わっています。
星座の区切りの日は、一般的によく使われる日付です。年によって 1 日前後することがあり、占いの本やサイトによっても少し違います。合わせたい区切りがあるときは、E 列の数字を書き換えてください。
4. 「丙午」のように十干十二支で表示する
十干（甲・乙・丙…の 10 文字）は、年を 10 で割った余りで選びます。干支の式と & でつなげると、十干十二支になります。
=MID("庚辛壬癸甲乙丙丁戊己",MOD(YEAR(B2),10)+1,1)&MID("申酉戌亥子丑寅卯辰巳午未",MOD(YEAR(B2),12)+1,1)
1990 年は「庚午」、1985 年は「乙丑」、2000 年は「庚辰」、2026 年は「丙午」になりました。
【実機で検証】干支・星座の式でつまずくところ
| ためしたこと | 式 | 結果 |
|---|---|---|
| 2026 年を数字で入れる | =MID("申酉戌亥子丑寅卯辰巳午未",MOD(2026,12)+1,1) | 午 |
| 12 で割り切れる年（2016 年） | =MID("申酉戌亥子丑寅卯辰巳午未",MOD(2016,12)+1,1) | 申 |
| 年だけのセル（A12）に YEAR を使った | =MID("申酉戌亥子丑寅卯辰巳午未",MOD(YEAR(A12),12)+1,1) | 巳 |
| 生年月日が空欄（A14）の干支 | =MID("申酉戌亥子丑寅卯辰巳午未",MOD(YEAR(A14),12)+1,1) | 子 |
| 3月20日（320）の星座 | =LOOKUP(320,干支と星座!E2:E14,干支と星座!F2:F14) | うお座 |
| 3月21日（321）の星座 | =LOOKUP(321,干支と星座!E2:E14,干支と星座!F2:F14) | おひつじ座 |
| 生年月日が空欄（A14）の星座 | =LOOKUP(MONTH(A14)*100+DAY(A14),干支と星座!E2:E14,干支と星座!F2:F14) | #N/A |
年を数字で入れると、2026 年は「午」、12 で割り切れる 2016 年は「申」でした。年がセルに数字で入っているなら、YEAR( ) を付けずに MOD(年のセル,12) と書きます。
「1990」と年だけを入れたセルに YEAR を使うと「巳」になり、正しい「午」とは違う結果になりました。Excel は 1990 を日付の番号として読むので、別の年として計算されます。
生年月日が空欄でも、干支は「子」と表示されました。空欄は 1900 年として計算されるためです。星座のほうは #N/A になります。空欄の行があるときは、=IF(B2="","",干支の式) のように IF で包みます。星座は 3月20日が「うお座」、3月21日が「おひつじ座」で、表の区切りどおりに切り替わりました。
よくある質問
生年月日から年齢もいっしょに出したいです。
DATEDIF 関数で出せます。生年月日から年齢を計算する方法で説明しています。
和暦（昭和・平成・令和）で表示するには？
生年月日のセルを TEXT 関数で和暦にできます。日付を和暦に変換する方法で説明しています。
星座が #N/A になります。
生年月日が空欄だと #N/A になりました（上の検証）。まず生年月日が入っているかを確かめます。区切りの表の月日（E 列）が小さい順に並んでいるかも見てください。LOOKUP の決まりは LOOKUP関数の使い方で説明しています。
