誕生日から星座を取得する(INDEX,MATCH,MONTH,DAY)

星座と年月の対応表を作成しておくことでセルに入力された誕生日から自動的に星座を取得することができます。 VLOOKUP 関数を使う方法もありますが、今回は VLOOKUP 関数を使わない方法で試してみます。ここでは Excel の関数である INDEX 関数、 MATCH 関数、 MONTH 関数、 DAY 関数を使って誕生日から星座を取得する方法について解説します。

※ VLOOKUP 関数を使う方法については「誕生日から星座を取得する(VLOOKUP,MONTH,DAY)」を参照されてください。

(Last modified: 2023年07月10日)

生年月日から星座を取得する

星座は何月何日から何月何日までに生まれたらどの星座というように決まっています。星座と月日の対応表は次のとおりです。

今回は MATCH 関数と INDEX 関数を使って生年月日から星座を取得します。先ほどの表を MATCH 関数で扱えるように次のように変更しました。

各星座ごとに対応する期間の最初の日付を 3 桁または 4 桁の数値で B 列に記述しています。例えば乙女座であれば 08/23 ～ 09/22 なので最初の日付である 823 と記述してあります。そして B 列の数値が昇順に並ぶようにしてあります。山羊座だけは 12 月から 1 月にかけて期間があるため 2 つ行が存在します。

それでは別途入力した誕生日のデータから対応する星座を取得します。次のシートを見てください。

星座を表示する G3 セルに次のように入力しました。検索範囲の B3:C15 は絶対参照で指定してください。また検索値として月と日の値から 4 桁の数値を作成するために、月の値を MONTH 関数で取得し 100 倍したものに日の値を DAY 関数で取得して加算しています。

Enter キーを押すと、生年月日から該当する星座を取得し G3 セルに表示します。

G4 セルから G6 セルにも同じように数式を入力しました。

このように MATCH 関数と INDEX 関数を使用することで、事前に作成した星座と期間の表を使って生年月日から該当する星座を取得することができます。

解説

今回は誕生日の値を数値に変換して比較するために MONTH(F3)*100+DAY(F3) という数式を使って 3 桁または 4 桁の数値に変換しています。実際にどのような数値に変換されたのか確認してみます。

この数値を MATCH 関数を使って表の期間の部分である B3:B15 を検索し、該当する期間のインデックスを取得します。

そして INDEX 関数を使って表の星座の部分である C3:C15 の中から先ほど取得したインデックスを指定して星座を取得します。

今回使用した関数の解説は下記を参照されてください。

星座の表を配列定数を使って指定する

先ほどは MATCH 関数や INDEX 関数から参照する星座の表を別に作成していましたが、配列定数を使って MATCH 関数の検索範囲や INDEX 関数の領域として直接指定することもできます。次のシートを見てください。

星座を表示する D3 セルに次のように入力しました。

Enter キーを押すと D3 セルには次のように星座が表示されます。

D4 セルから D6 セルに同じように数式を入力しました。

検索範囲である誕生日と星座の表を別途作成することなく MATCH 関数と INDEX 関数を使って誕生日から星座を取得することができました。

解説

今回は期間と星座の表を作成する代わりに、 MATCH 関数の検索範囲と INDEX 関数が参照する範囲をそれぞれ配列定数を使って指定しています。

MATCH 関数の検索範囲の配列定数は次の通りです。

INDEX 関数の範囲の配列定数は次の通りです。

このように期間と星座の表を作らなくても配列定数を使用することで生年月日から星座を取得することができます。

※ 配列定数については「配列定数の利用方法」を参照されてください。

-- --

Excel の関数である INDEX 関数、 MATCH 関数、 MONTH 関数、 DAY 関数を使って誕生日から星座を取得する方法について解説しました。