メディア個別 【エクセル関数】関数を使い生年月日から星座を調べるには? | editeur エディトゥール

editeur

検索
第9回 | デキる男の必修科目──大人のエクセル一問一答

【エクセル関数】関数を使い生年月日から星座を調べるには?

社交の場に出向く前に準備しておくと、何かと重宝するのが出会う相手の星座。話のきっかけにもなるほか、相手の性格もある程度推測できるので、ビジネス相手との相性を知りたい場合にも役立つ。とはいえ、誕生日から星座をすぐに思い浮かべるのは、よほどの星座占い好きでも難しいはず。そこで活用してほしいのが、住所録に入力された生年月日から対応する星座を判定する関数テクニックだ。少し高度な関数を使うが、パズルを解くような感覚で問題に挑戦してみよう。

問題:
関数を使い、生年月日から星座を調べ自動入力せよ。

難易度:☆☆☆☆☆
実用度:☆☆☆☆★
目標ステップ数: 7

解答:

生年月日と星座早見表を見比べ対応する星座を調べるというように、別途作成した表組のなかから条件にあったデータを抜き出す作業をするためには「VLOOKUP(ブイルックアップ)」関数を用いる。また、今回の問題では生年月日のデータから、星座の判定に必要な月日だけを抜き出すために「TEXT(テキスト)」関数も使っている。構造が複雑なので標準解答の手順に従って、まず数式を完成させてから、【今回のまとめ】で解説を読んでほしい。

●STEP1

住所録があるブックに新しいシートを作成し、下の画面を参照し星座早見表を入力しておく。「山羊座」が2回出てくるが、そのままでOK(理由は後述する)。また、「判定用」欄に入力されている数字は数値として入力すると「0101」ではなく「101」となってしまうので、文字列として入力する点にも注意しよう。なお、「期間(参考)」欄は入力しなくても構わない。

※星座と日付の対応には諸説あり、画面の通りではない場合もある。

●STEP2

星座を自動入力したいセル(ここではE2)を選択してから「数式バー」をクリック。半角で「=v」と入力すると関数の候補が表示されるので、その中から「VLOOKUP」をクリックする。

●STEP3

「=VLOOKUP(」と関数が自動的に補われるので、その後に続けて半角で「te」と入力。関数の候補が表示されるので、その中から「TEXT」をクリックする。

●STEP4

「TEXT(」と関数が自動的に補われる。ここで星座の判断基準となる生年月日が含まれるセル(ここではD2)をクリックし、さらに半角で「,”mmdd”,」と入力。ここまでで「数式バーに」「=VLOOKUP(TEXT(D2,”mmdd”,」と入力されているようにする。

●STEP5

「数式バー」が選択されている状態で星座早見表が入力されているシートに移動し、見出しを除いた「判定用」欄と「星座」欄を範囲選択。「数式バー」に選択した範囲(ここでは「星座早見表!A2:B14」)が入力されたことを確認したら、キーボードの「F4」キーを押す。これで範囲が「星座早見表!A2:B14」から「星座早見表!$A$2:$B$14」に変わる。

●STEP6

続けてキーボードから半角で「,2)」と入力し「Enter」キーを押す。

●STEP7

住所録のシートに戻り、関数を入力したセルに、誕生月日に対応する星座が自動入力される。セルを選択し、さらにセルの右下をダブルクリックするとオートフィル機能が働き、生年月日が入力されている範囲まで自動的に関数が補われる。

【今回のまとめ】

今回はVLOOKUP関数の中にTEXT関数を入れ込むという、少し複雑な構造の数式となっている。長くなってしまうが、それぞれの関数の解説ともに数式全体の構造を説明しよう。

今回のメインとなるVLOOKUP関数は「VLOOKUP(検索する値,検索する範囲,取り出す列番号,検索方法)」という基本構文になる。問題にあてはめて説明すると、「検索する値」は星座の判定材料となる誕生月日のこと。しかし住所録では生年まで入った日付データが入力されているため、まずはここから月日だけを抜き出す必要がある。

そこで登場するのが、日付や時刻から必要な部分だけを文字列として取り出すTEXT関数だ。基本構文は「TEXT(変換元,変換形式)」となる。問題では変換形式に「”mmdd”」と指定しているが、これは「mm」が月(2桁表示)、「dd」が日(2桁表示)を表している。たとえば「1970/2/6」なら「0206」と変換されることになる。

ここで注意しなければいけないのが、TEXT関数で変換した場合、数値も文字列扱いになってしまうということ。そのため星座早見表の「判定用」欄も、数値ではなく文字列として入力しておかなければならないのである。「判定用」欄を数値として入力すると、関数が機能しないので気をつけよう。

再びVLOOKUP関数に戻り、次は「検索する範囲」について。問題では、星座早見表の「判定用」欄と「星座」欄を検索する範囲として指定している。つまり、テキスト関数で取り出した誕生月日と早見表の「判定用」欄を照合させているわけだ。

なお、「F4」キーを押して「A2:B14」から「$A$2:$B$14」としているのは、オートフィル機能で関数を自動入力した際に選択範囲がズレないようにするため。セル番号の前に付けた「$」は「絶対参照」という指定で、手動で入力することもできる。

VLOOKUP関数では、「検索方法」の指定を省略すると、「検索する値」と「検索する範囲」が完全一致(同じ値)しない場合は、近似値(検索する値未満でもっとも大きな値)を含む行を検索結果として選んでくれる。たとえば、「検索する値」が「1006(10月6日)」だった場合は、「判定用」欄で「0923」を含む11行目が検索結果となる。

ここで、もう一度星座早見表を見てほしい。山羊座が2回用意されているのは、VLOOKUP関数の近似値による検索を利用するためだったのだ。星座早見表の「判定用」欄に入っている数値は、星座が始まる日付を示しているわけだが、山羊座に限っては年またぎ(12月22日~1月19日)になってしまうため、「1222(12月22日)」とは別に、1月分(1月1日から19日)までの判定値となる「0101」を用意する必要があったのである。

そして、最後は「取り出す列番号」。ここで「2」を指定することで選択した範囲の2列目、つまり「星座」欄が、取り出すデータということになる。

以上が、今回の数式の解説だ。難しいと思ったら、画面の通りにシートを作成し、「=VLOOKUP(TEXT(D2,”mmdd”, 星座早見表!$A$2:$B$14,2)」という数式を、そのままコピーして使っても構わない。

関数の中でも特に難しい部類に入るVLOOKUPだが、型番に対応する商品名を調べたり、名簿から条件にあったデータを取り出したりするなど、エクセルをデータベース的に活用したい場合には欠かせない存在のひとつ。使いこなせるようになれば、達人として周囲から一目置かれること請け合いなので、この機会にぜひおぼえておくとよいだろう。

Toshiro Ishii

editeur

検索