Excel YouTube

【動画解説あり】基礎から学ぶ!実務では必須のTEXT関数を徹底解説

覚えるべき関数はそれほど多くはありません。とはいえ、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で、[&]を使って、別の文字列と結合してみましょう。

A1に日付が入っている

 

別の文字列をここでは、[(木)]とし、C1に、[=A1&"(木)"]と入力してみると、[44154(木)]という結果になってしまいます。

A1セルを&で結合

▼  ▼  ▼

44154(木)となってしまう

 

[表示形式]が設定されている、A1と(木)というような別の文字を単純に[&]で結合すると、A1に入っているものが日付だった場合には、[シリアル値]になってしまいます。

 

[44154(木)]ではなく、[2020/11/19(木)]と表示したかったはずです。

 

このような文字列を結合するときに、TEXT関数を使うのです。TEXT関数をどう使うのかというと、A1には、実際には、[44154]という数字が入っています。それを[表示形式]で、[2020/11/19]と見せています。

 

TEXT関数というのは、この[表示形式]をシミュレーションする関数です。実際に、[2020/11/19]のように設定するのではなく、もし、このセルに、[yyyy/m/d]のような[表示形式]を設定したら、どのように見えるのか?というのをシミュレーションする関数です。

 

ですから、計算式の中で[表示形式]が使える関数なのです。

 

では、今回どのようにするのかというと、シリアル値が入っているA1のところをTEXT関数[日付]に変換します。

 

具体的には、A1TEXT関数で囲み、[表示形式]を入力して、[=text(A1,"yyyy/m/d")&"(木)"]とします。すると、[2020/11/19(木)]と表示されます。

TEXT関数で囲む

▼  ▼  ▼

正しく表示された

 

TEXT関数で、A1にあった本当は[44154]というシリアル値を、[yyyy/m/d]という[表示形式]に変換したということです。

 

ほかにもできます。例えば、和暦表示[ggge]を用いて、[=text(A1,"ggge年m月d日")&"(木)"]とすると、[令和2年11月19日(木)]となります。

和暦表示の式

▼  ▼  ▼

和暦表示

 

日付だけではありません!

 

今度は、A1に、[8,000]というような[,(カンマ)]をつけた数値が入っている場合を考えてみましょう。

A1に8,000

 

先ほどと同じように、単純に文字列結合[=A1&"円"]とすると、[,(カンマ)]が消えて[8000円]となってしまいます。

A1セルを&で結合

▼  ▼  ▼

8000円となってしまう

 

[,(カンマ)]をつけて、[8,000円]としたいですよね。これも同じように、単純に文字列結合するのではなく、TEXT関数を組み合わせて、表示形式をシミュレーションします。

 

[,(カンマ)]をつける表示形式[#,##0]ですので、A1[text(A1,"#,##0")]TEXT関数で囲んで、[=text(A1,"#,##0")&"円"]という式にします。すると、[8,000円]と表示できます。

TEXT関数で囲む

▼  ▼  ▼

狙いどおり表示された

 

さきほど紹介した["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列に[曜日]を表示したいとします。

A列に日付が入っている

 

少し話がそれてしまいますが、[曜日]は表示形式で出すことができます。

 

[Ctrl]+[1][セルの書式設定]ダイアログボックスを表示し、[表示形式]タグの[分類]の中にある[ユーザー定義]を選択します。

 

[種類(T):]には、[yyyy/m/d]が入っています。これに、[(aaa)]を加えて、[yyyy/m/d(aaa)]とすると、丸括弧付きで曜日が表示されます。

A列に日付に曜日を表示

 

では、B列に[曜日]だけを表示する場合はどうでしょう。やり方は2つあります。

 

1つ目は、A列を単純に[=]で参照します。

A1を参照

▼  ▼  ▼

A1を参照後

 

そして、B列の[表示形式]を、先ほどの要領で、[aaa]([ユーザー定義]→[種類(T):]の欄に入力)とします。すると、曜日が表示されます(B列には、[日付]が入っています)。

表示形式変更前

▼  ▼  ▼

表示形式変更後

 

もう1つは、TEXT関数を使う方法。[曜日]に関して何かするときはTEXT関数を使うと覚えておくのもいいと思います。

 

B2セルに[=text(A2,"aaaa")]と入力します。すると、[木曜日]と表示されました。なお、[aaa]だと[木]という表示になります。

B2にTEXT関数入力

▼  ▼  ▼

狙いどおり表示された

 

このTEXT関数で曜日を取得するということを知っておくと、別の記事で紹介する[土曜日]、[日曜日]だけ[色を変える]ということができるようになります。

 

用途3[数値を文字列に変換する]

今度は、A列にコードが[数値]で入っています。また、D列にはマスターデータとして、コードが[文字列]で入っています。

A列数字、D列文字列

 

このとき、B列の[名前]の欄に、「D列E列のマスターデータを利用して、VLOOKUP関数でデータを入力したい」とします。D列はマスターデータなので、ほかへの影響を考慮して、容易に[数値]に変換できないとします。

 

さて、この場合にどうしますか?VLOOKUP関数を用いて、[=VLOOKUP(A2,$D$2:$E$5,2,false)]と正しい式を入れても[#N/A]とエラーになってしまいます。エラーになるのは、VLOOKUP関数[数値]と[文字列]を区別するからです。

B列に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)]という式になり、うまくいきます。

TEXT関数で囲む前

▼  ▼  ▼

TEXT関数で囲む

 

ちなみに、A列[文字列]のコードが、D列にマスターデータとして、[数値]が入っているときは、文字列を数値に変える関数[VALUE関数]を用います。

 

[=VLOOKUP(VALUE(A2),$D$2:$E$5,2,false)]とすればいいです。[文字列]→[数値]VALUE関数という専用の関数がありますが、[数値]→[文字列]TEXT関数で対応するしかないので、ぜひ覚えておいてください。

 

おすすめ記事

参考文献

-Excel, YouTube
-

© 2021 まなびっと Powered by AFFINGER5