誰かの生年月日を聞いて、干支と星座が何であるかと、瞬時に思い浮かぶ人は少ないと思います。
干支は生まれ年から、星座は月日から、それぞれ当てはめる必要があるためです。
上記の悩みを解決するために、生年月日を入力するだけで「干支」と「星座」が表示されるエクセルを作成してみました。
干支と星座の一覧表を作成する
まず、干支と星座を表示するための事前準備が必要となります。
B3セルに生年月日を入力すると、B4セルに干支を、B5セルに星座を表示させるためには、元となるデータが必要です。
そこで、D列～E列に干支のデータを、G列～I列に星座のデータを設置します。
まず、干支のデータは、D列に番号を、E列に干支を設置します。
D列の番号は、B4セルに該当する干支を表示させるために必要となります。
番号の決め方は、西暦の年数を12で割った時の「余りの値」としています。
※丑年の場合は、直近の丑年が2021年なので、2021÷12=168余り5となり、番号を「5」としています。
次に、星座のデータは、G列に星座を、H列に開始日を、I列に終了日を設置します。
H列の開始日とI列の終了日は、B5セルに該当する星座を表示させるために必要となります。
干支と星座の算出数式
干支を表示するために、B4セルには下記数式を入力します。
=VLOOKUP(MOD(VALUE(LEFT(B3,4)),12),D1:E13,2,FALSE)
星座を表示するために、B5セルには下記数式を入力します。
非常に長い数式ですが、内容はシンプルです。
=IF(OR(VALUE(H11)<=VALUE(MID(B3,6,2)&MID(B3,9,2)),VALUE(I11)>=VALUE(MID(B3,6,2)&MID(B3,9,2))),G11,IF(AND(VALUE(H2)<=VALUE(MID(B3,6,2)&MID(B3,9,2)),VALUE(I2)>=VALUE(MID(B3,6,2)&MID(B3,9,2))),G2,IF(AND(VALUE(H3)<=VALUE(MID(B3,6,2)&MID(B3,9,2)),VALUE(I3)>=VALUE(MID(B3,6,2)&MID(B3,9,2))),G3,IF(AND(VALUE(H4)<=VALUE(MID(B3,6,2)&MID(B3,9,2)),VALUE(I4)>=VALUE(MID(B3,6,2)&MID(B3,9,2))),G4,IF(AND(VALUE(H5)<=VALUE(MID(B3,6,2)&MID(B3,9,2)),VALUE(I5)>=VALUE(MID(B3,6,2)&MID(B3,9,2))),G5,IF(AND(VALUE(H6)<=VALUE(MID(B3,6,2)&MID(B3,9,2)),VALUE(I6)>=VALUE(MID(B3,6,2)&MID(B3,9,2))),G6,IF(AND(VALUE(H7)<=VALUE(MID(B3,6,2)&MID(B3,9,2)),VALUE(I7)>=VALUE(MID(B3,6,2)&MID(B3,9,2))),G7,IF(AND(VALUE(H8)<=VALUE(MID(B3,6,2)&MID(B3,9,2)),VALUE(I8)>=VALUE(MID(B3,6,2)&MID(B3,9,2))),G8,IF(AND(VALUE(H9)<=VALUE(MID(B3,6,2)&MID(B3,9,2)),VALUE(I9)>=VALUE(MID(B3,6,2)&MID(B3,9,2))),G9,IF(AND(VALUE(H10)<=VALUE(MID(B3,6,2)&MID(B3,9,2)),VALUE(I10)>=VALUE(MID(B3,6,2)&MID(B3,9,2))),G10,IF(AND(VALUE(H12)<=VALUE(MID(B3,6,2)&MID(B3,9,2)),VALUE(I12)>=VALUE(MID(B3,6,2)&MID(B3,9,2))),G12,IF(AND(VALUE(H13)<=VALUE(MID(B3,6,2)&MID(B3,9,2)),VALUE(I13)>=VALUE(MID(B3,6,2)&MID(B3,9,2))),G13))))))))))))
B4セルとB5セルに数式を入力し、生年月日に「2021/09/18」と入力した場合、下図のようになります。
数式の解説
まず、B4セルに入力した数式についてです。
1.LEFT関数を使い、B3セルに入力した生年月日の最初の4文字を抽出します。
2.MOD関数を使い、余りの値を算出する前に、割られる数を数値にするために、VALUE関数を使い文字を数値に変換します。
3.MOD関数を使い、「年数÷12」で余りの値を算出します。
4.VLOOKUP関数を使い、MOD関数で算出した余りの値と同じ番号の星座を、B4セルに表示します。
次に、B5セルに入力した数式についてです。
1.MID関数を使い、B3セルに入力した生年月日の月日を抽出します。
2.VALUE関数を使い、MID関数で抽出した月日を数値に変換します。
3.IF関数を使い、MID関数で抽出した月日が、「1222以上の値である」または「119以下の値である」に該当したら山羊座、違えば次の処理に進むように設定を行います。
(複数条件の1つに該当すれば良い場合はOR関数を使う。)
4.手順3のIF関数で該当しなかった場合、「321以上の値である」と「419以下の値である」の2つの条件に該当したら牡羊座、違えば次の処理に進むように設定を行います。
(複数条件の全てに該当すれば良い場合はAND関数を使う。)
5.手順4で設定した手順と同じように、牡牛座から魚座まで関数の設定を行います。
(山羊座は手順3で実施したため、ここでは設定しない。)
6.手順5までを終えて、入力した数式の文字列が表示された場合は、下図の設定をしてみてください。
エクセル関数の記事の紹介
今回、干支と星座を関数で表示するために色々な関数を使用しました。
使い方が難しいと思った関数があった場合は、使い方を載せている記事がありますので、参考にしてください。
○VLOOKUP関数
【エクセル】VLOOKUP関数の使い方 - イシバシランブログ
○MOD関数
【エクセル】QUOTIENT関数とMOD関数の使い方 - イシバシランブログ
○LEFT関数・MID関数
【エクセル】LEFT関数とRIGHT関数とMID関数の使い方 - イシバシランブログ
○IF関数
○OR関数・AND関数
