覚えるべき関数はそれほど多くはありません。とはいえ、SUM関数やVLOOKUP関数だけを知っていても、実務では通用しません。このページで詳しく解説しているTEXT関数は、覚えるべき関数というか、使えるようになるべき関数の1つです。このページを読むか、このページ内にある動画を視聴すれば、実務でTEXT関数が使えるようになるはずです。
TEXT関数が活躍する3パターン
合計を求めるSUM関数、平均を求めるAVERAGE関数、最大値を求めるMAX関数、そして、VLOOKUP関数・・・エクセル(Excel)関数の学習としては、だいたい、この辺で終わります。
実務では、確かに、SUM関数やVLOOKUP関数をよく使います。ですが、これらの関数だけでは困ることが多いんです。実は、もう一歩踏み込んだ関数、VLOOKUP関数などをさらに使いやすくする関数を習得する必要があります。
その1つが、このTEXT関数です(ほかには、FIND関数、SUBSTITUTE関数あたりも知っててほしい。)。
TEXT関数の説明といえば、[TEXT(値,表示形式)]というのをよく目にしますが、[=TEXT(値,"表示形式")]と、表示形式をダブルクォーテーションで囲む必要があることを含めて覚えておいてください。そうでないと、実務では使えません。
では、TEXT関数って、実務でどんなときに使うのでしょうか?大きく分けて3パターンあると思います。
この3パターンを紹介していきます。
用途1[表示形式が設定されている状態で文字列結合する]
セルの中に入っている何かと何かを、[&](アンパサンド)を使って、くっつける、文字列結合するときに、TEXT関数は必須です。
今、A1には、日付が入っています。そして、A1には、[yyyy/m/d]という[表示形式]が設定されています。では、C1で、[&]を使って、別の文字列と結合してみましょう。
別の文字列をここでは、[(木)]とし、C1に、[=A1&"(木)"]と入力してみると、[44154(木)]という結果になってしまいます。
▼ ▼ ▼
[表示形式]が設定されている、A1と(木)というような別の文字を単純に[&]で結合すると、A1に入っているものが日付だった場合には、[シリアル値]になってしまいます。
[44154(木)]ではなく、[2020/11/19(木)]と表示したかったはずです。
このような文字列を結合するときに、TEXT関数を使うのです。TEXT関数をどう使うのかというと、A1には、実際には、[44154]という数字が入っています。それを[表示形式]で、[2020/11/19]と見せています。
TEXT関数というのは、この[表示形式]をシミュレーションする関数です。実際に、[2020/11/19]のように設定するのではなく、もし、このセルに、[yyyy/m/d]のような[表示形式]を設定したら、どのように見えるのか?というのをシミュレーションする関数です。
ですから、計算式の中で[表示形式]が使える関数なのです。
では、今回どのようにするのかというと、シリアル値が入っているA1のところをTEXT関数で[日付]に変換します。
具体的には、A1をTEXT関数で囲み、[表示形式]を入力して、[=text(A1,"yyyy/m/d")&"(木)"]とします。すると、[2020/11/19(木)]と表示されます。
▼ ▼ ▼
TEXT関数で、A1にあった本当は[44154]というシリアル値を、[yyyy/m/d]という[表示形式]に変換したということです。
ほかにもできます。例えば、和暦表示[ggge]を用いて、[=text(A1,"ggge年m月d日")&"(木)"]とすると、[令和2年11月19日(木)]となります。
▼ ▼ ▼
日付だけではありません!
今度は、A1に、[8,000]というような[,(カンマ)]をつけた数値が入っている場合を考えてみましょう。
先ほどと同じように、単純に文字列結合で[=A1&"円"]とすると、[,(カンマ)]が消えて、[8000円]となってしまいます。
▼ ▼ ▼
[,(カンマ)]をつけて、[8,000円]としたいですよね。これも同じように、単純に文字列結合するのではなく、TEXT関数を組み合わせて、表示形式をシミュレーションします。
[,(カンマ)]をつける表示形式は[#,##0]ですので、A1を[text(A1,"#,##0")]とTEXT関数で囲んで、[=text(A1,"#,##0")&"円"]という式にします。すると、[8,000円]と表示できます。
▼ ▼ ▼
さきほど紹介した["yyyy/m/d"]の部分や、今紹介した["#,##0"]の部分などの[表示形式]を指定する部分を変えれば、いかようにも表現することができるということです。
[表示形式]が設定されている状態で文字列結合したい、これが1つ目のパターンです。
表示形式コードの解説
表示形式には、様々なものがありますので、主なものを載せておきます。
日付
表示形式 | 説明 | 表示例 |
yyyy | 西暦を下4桁で表示 | 2020 |
yy | 西暦を下2桁で表示 | 20 |
m | 月を数値で表示 | 7 |
mm | 月を2ケタの数値で表示 | 07 |
mmmm | 月を英語で表示 | November |
mmm | 月の英語を短縮形で表示 | Nov |
d | 日付を数値で表示 | 1 |
dd | 日付を2ケタの数値で表示 | 01 |
ggg | 和暦の元号を表示 | 令和 |
gg | 和暦の元号を漢字短縮形で表示 | 令 |
g | 和暦の元号をアルファベット短縮形で表示 | R |
e | 和暦の年を表示 | 2 |
ee | 和暦の年を2桁で表示 | 02 |
数値
[#]は数値の1桁を表しますが0のときは何も表示しません。一方、[0]は数値の1桁を表しますが、0のときは表示します。なお、[?]は数値の1桁を表しますが、0のときは空白(半角スペース)を表します。
表示形式 | 説明 | 表示例 |
#,##0 | 数値を桁区切り[,(カンマ)]で表示(0表示) | 0 |
#,##0.00 | 数値を桁区切りで小数点第二位まで表示(0表示) | 0.00 |
#,### | 数値を桁区切りで表示(0非表示) | 8,000 |
#,###.## | 数値を桁区切りで小数点第二位まで表示(0非表示) | 8,000.00 |
000 | 数値を3ケタで表示 | 020 |
?,??? | 数値を桁区切りで表示(4ケタ以下には半角スペース) | _800 |
文字
表示形式 | 説明 | 表示例 |
@ | 文字列で表示 | 0 |
用途2[曜日の取得]
今度は、A列に[日付]が入っている場合に、B列に[曜日]を表示したいとします。
少し話がそれてしまいますが、[曜日]は表示形式で出すことができます。
[Ctrl]+[1]で[セルの書式設定]ダイアログボックスを表示し、[表示形式]タグの[分類]の中にある[ユーザー定義]を選択します。
[種類(T):]には、[yyyy/m/d]が入っています。これに、[(aaa)]を加えて、[yyyy/m/d(aaa)]とすると、丸括弧付きで曜日が表示されます。
では、B列に[曜日]だけを表示する場合はどうでしょう。やり方は2つあります。
1つ目は、A列を単純に[=]で参照します。
▼ ▼ ▼
そして、B列の[表示形式]を、先ほどの要領で、[aaa]([ユーザー定義]→[種類(T):]の欄に入力)とします。すると、曜日が表示されます(B列には、[日付]が入っています)。
▼ ▼ ▼
もう1つは、TEXT関数を使う方法。[曜日]に関して何かするときはTEXT関数を使うと覚えておくのもいいと思います。
B2セルに[=text(A2,"aaaa")]と入力します。すると、[木曜日]と表示されました。なお、[aaa]だと[木]という表示になります。
▼ ▼ ▼
このTEXT関数で曜日を取得するということを知っておくと、別の記事で紹介する[土曜日]、[日曜日]だけ[色を変える]ということができるようになります。
用途3[数値を文字列に変換する]
今度は、A列にコードが[数値]で入っています。また、D列にはマスターデータとして、コードが[文字列]で入っています。
このとき、B列の[名前]の欄に、「D列とE列のマスターデータを利用して、VLOOKUP関数でデータを入力したい」とします。D列はマスターデータなので、ほかへの影響を考慮して、容易に[数値]に変換できないとします。
さて、この場合にどうしますか?VLOOKUP関数を用いて、[=VLOOKUP(A2,$D$2:$E$5,2,false)]と正しい式を入れても[#N/A]とエラーになってしまいます。エラーになるのは、VLOOKUP関数が[数値]と[文字列]を区別するからです。
▼ ▼ ▼
ではどうするか。そう、ここでTEXT関数を使うんです。
今、A列に入っているのは[数値]です。ですから、A列をTEXT関数で文字列に変換します。
さきほどの[=VLOOKUP(A2,$D$2:$E$5,2,false)]のA2の部分を[TEXT(A2,"@")]とTEXT関数で囲みます。なお、[@]は文字列形式の書式記号です。
こうすると、A列の数値が文字列として扱われる、[=VLOOKUP(TEXT(A2,"@"),$D$2:$E$5,2,false)]という式になり、うまくいきます。
▼ ▼ ▼
ちなみに、A列に[文字列]のコードが、D列にマスターデータとして、[数値]が入っているときは、文字列を数値に変える関数[VALUE関数]を用います。
[=VLOOKUP(VALUE(A2),$D$2:$E$5,2,false)]とすればいいです。[文字列]→[数値]はVALUE関数という専用の関数がありますが、[数値]→[文字列]はTEXT関数で対応するしかないので、ぜひ覚えておいてください。
おすすめ記事
- Excel関数の基本的な使い方と応用テクニック辞典(関数一覧)
- Excel厳選ショートカットキー・印刷用・早見表PDF付き
- Windows10厳選ショートカットキー・早見表・印刷用PDF付き
- Word 厳選ショートカットキー・印刷用・早見表PDF付き
- PowerPoint 厳選ショートカットキー・早見表・印刷用PDF付き