イシバシランブログ

「エクセル関数」と「お金に関する情報」をメインテーマに書いています。

【エクセル】干支と星座を関数の組み合わせで算出してみた

誰かの生年月日を聞いて、干支と星座が何であるかと、瞬時に思い浮かぶ人は少ないと思います。

干支は生まれ年から、星座は月日から、それぞれ当てはめる必要があるためです。

上記の悩みを解決するために、生年月日を入力するだけで「干支」と「星座」が表示されるエクセルを作成してみました。

 

干支と星座の一覧表を作成する

まず、干支と星座を表示するための事前準備が必要となります。

B3セルに生年月日を入力すると、B4セルに干支を、B5セルに星座を表示させるためには、元となるデータが必要です。

そこで、D列~E列に干支のデータを、G列~I列に星座のデータを設置します。

f:id:ishibashiran:20210831221415j:plain

まず、干支のデータは、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」と入力した場合、下図のようになります。

f:id:ishibashiran:20210831223415j:plain

 

数式の解説

まず、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までを終えて、入力した数式の文字列が表示された場合は、下図の設定をしてみてください。

f:id:ishibashiran:20210904181713j:plain

 

エクセル関数の記事の紹介

今回、干支と星座を関数で表示するために色々な関数を使用しました。

使い方が難しいと思った関数があった場合は、使い方を載せている記事がありますので、参考にしてください。

 

○VLOOKUP関数

【エクセル】VLOOKUP関数の使い方 - イシバシランブログ

 

○MOD関数

【エクセル】QUOTIENT関数とMOD関数の使い方 - イシバシランブログ

 

○LEFT関数・MID関数

【エクセル】LEFT関数とRIGHT関数とMID関数の使い方 - イシバシランブログ

 

○IF関数

【エクセル】IF関数の使い方 - イシバシランブログ

 

○OR関数・AND関数

【エクセル】AND関数とOR関数の使い方 - イシバシランブログ