《連載第3弾 あまり見かけないけど、こんな機能があったんだ》
第5回 文字と日付と数字と VLOOKUP関数

前回の “&”、MID、RIGHT、LEFT。特に“&”は、文字列操作の基本でした。
ご理解いただけましたか？是非、使ってみてください。結構いろいろな場面で使えるだろうと思います。

今回は、文字列操作の続きと予告しておきましたが、少し複雑に関数を組み合わせて使ってみます。

日付と文字とVLOOKUP関数と題しましたが、まず、【図1】【図2】を確認してください。

第1回でご覧いただいた名簿は、【図1】のように、生年月日から星座を自動検出するように作られています。この仕掛けは、【図2】の星座表が用意されていることによります。

L列[L9]に入力されている式は、 =VLOOKUP(K9,$P$9:$T$21,5,TRUE) です。
念のために、 = VLOOKUP(検索値, 範囲, 列番号, 検索の型) が、VLOOKUP 関数の書式です。
要するに、検索値 [K9]の値を、範囲（ $P$9:$T$21：すなわち星座表の一番左の列）から探し出して、見つけたら、その行の列番号[5：番目（指定範囲の左から）]の値を持ってきてください。という関数です。

このVLOOKUP関数は、ご存知の方が多いかと思うのですが、問題は、数式の末尾の “ TRUE”。すなわち「検索の型」というもの。よく見ていただくとわかるかと思いますが、[K9]は、“1110”。この値は、星座表のP列にはありません。こんなときに、検索の型を“TRUE” としておくと、検索値未満で最も大きい値が該当したことにしてもらえます。要するに、P列の“1123”では大きすぎる。だから“1024”がマッチしたものとして、蠍座を返してくれます。

逆に、検索の型を“ FALSE”は、完全に一致した場合に値を返すので、ここでは、該当するものがない（#N/A）となってしまいます。この星座表のように、一定の範囲に対して値を返したいようなとき、この検索の型（“TRUE”）は、是非覚えておきましょう。

注意 ： このTRUEを使う場合、対象の範囲の最左列は,昇順に並べ替えておかなければなりません。今回の例で、山羊座が、9行目と21行目に登場するのはそのための対策です。
90点～100点は、Aランク、75点～89点が、Bランクなどというときとか、典型的なのは、給与計算の税金の検索など、この検索の型“TRUE”は、結構活躍するはずです。

さて、VLOOKUPの説明が長くなってしまいましたが、今回本当に取り上げたいのは、[K列]のことです。

もともと[I列]には、生年月日が入力されています。（ちなみに表示形式は、 ge(yyyy)/mm/dd 。実際には、“1969/11/10”のように入力されています。）この生年月日には、年（ yyyy）がついているため、そもそも年に関係のない星座表から該当の星座を引っ張り出すのは難しそうです。VLOOKUPの検索値として使うには、星座表側も毎年（それこそ50年分くらい）の表にしておかなければ、うまくマッチングを取れないことになってしまいます。

そこで、[K列]。ここには、次の数式が入っています。
[K9]・・・・ =IF(I9<>"",TEXT(I9,"mmdd"),"")

IF関数については、以前の講座でも取り上げましたので、ここでは、
『 TEXT(I9,"mmdd") 』
を取り上げます。

この“ TEXT”関数は、何が何でも文字列にしてしまえ。という関数です。
書式は、 =TEXT(値, 表示形式)本例では、 値 [I9：1969/11/10] を 書式 ["mmdd"] の 『文字列』 にしなさい。

結果は、【図1】のとおり、“ 1110 ”という文字列が [K9] に入ることになります。

この TEXT 関数は、日付ばかりでなく、数字を文字列に変えたいときなども是非使ってみてください。

=TEXT( 258000,”####” ) → “258000”
=TEXT( 258000,”#,###円” ) → “258,000円”

などのような使い方もできます。書式は、セルの表示形式にある書式がほとんどそのまま使えます。
PHONETIC関数とこの[書式]－[ふりがな]は、入力時には併用すると間違いが減らせるのではと思います。

逆に、文字を数字に置き換える関数が 『VALUE』。

=VALUE( “258,000” ) → 258000

この2つは、是非セットで覚えておきましょう。

たとえば、数字だけの商品コードとか、社員番号とかを EXCEL で取り扱う場合など。ワークシート上では、見た目は、数字か文字か区別がつきませんよね。また、他の人が作成したワークシートやテキストファイルをインポートした場合など特に注意が必要です。見た目一緒なのに、数字と文字はEXCEL上で明確に違うものとして取り扱われます。先ほどの VLOOKUP で、確かに対象範囲内にあるのに #N/A になってしまう。 IF 関数で、思いと違う結果になってしまう。などということが起きがちなところです。

文字・数値は、少し意識しながら使い分けましょう。

参考までに、 [K列] を使わずに、星座を検出するには、少し長いですが、

= VLOOKUP( TEXT(I9,"mmdd") , $P$9:$T$21 , 5 , TRUE )

ということも可です。

ジャーナル最新のテーマ

---

---