Excel関数の勉強４

文字列操作関数　２

[TEXT] 関数

書式　「 TEXT(値,表示形式) 」

数値を書式設定した文字列に変換する関数です。

数式パレット-注釈　：数値に指定した書式を設定し、文字列に変換した結果を返します。
値-注釈　　　　　　：値には数値、結果が数値となる数式、または数値が入力されているセルへの参照を指定します。
表示形式-注釈　　　：表示形式には[表示形式]ダイアログボックスに表示されている数値形式を、文字列として指定します。
補記　　　　　　　：[書式]→[セル] コマンドの [表示形式] タブ→[分類] ボックスに表示される数値書式を、半角の二重引用符 (") で囲んで指定します。

解説(MS-ヘルプより)

表示形式にアスタリスク (*) を使用することはできません。
[表示形式]タブのオプションを使用し、数値を含むセルに数値書式を設定しても、表示が変わるだけで、文字列には変換されません。
TEXT 関数を使用すると、数値は書式設定された文字列に変換され、その計算結果は数値として計算に利用できなくなります
補記１：但し、数値として変換できる形式のものは演算が可能です。
例えば、[']付き全角数字、文字列書式全角数字でも、演算ができました。

補記２：年月日の[2005/9/13]を直接引数として使用する場合は、半角の二重引用符 (") で囲む必要があります。
「 =TEXT(2005/9/13,"yyyy年m月d日") 」の場合は、1900年1月1日の日付表示になります。
「 =TEXT("2005/9/13","yyyy年m月d日") 」にすると、2005年9月13日 (セル参照の場合は、そのまま参照することができます。)

値について

数値：「1234567.89」「-123.045」「0.0123」などは当然数値です。
また、[2005/9/13]で表示される「シリアル値」も数値です。「2005年9月13日」は[38608]という数値です。
結果が数値となる数式：[WEEKDAY]関数では、設定にもよりますが、日曜は１、月曜は２、・・・土曜は７
のように、数値で返されます
そこで、「 =TEXT(WEEKDAY("2005/9/13",1),"aaaa") 」と入力すると、「火曜日」と返ってきます。
[WEEKDAY("2005/9/13",1)]の部分が数値の[3]になるからです。
「表示形式」の["aaaa"]で「火曜日」と表示される訳です。
数値が入力されているセルへの参照を指定：上記で[A1]セルに「2005/9/13」が入力されていれば、
セル参照で「 =TEXT(WEEKDAY(A1,1),"aaaa") 」でも良いことになります。
わざわざ、[WEEKDAY]関数を使わずとも、「 =TEXT(A1,"aaaa") 」でも、「火曜日」と返ってきます。
その他考えられるものに、数学・三角関数の戻り値、例えば、万年暦で使用した[MOD]関数、円周率を求める[PI]等々有りますが、「日付けのシリアル値」と「数値」が主な対象になるのではないでしょうか。
「日付･時刻のシリアル値」については、年月日・時刻のいろいろな表示法(下段)
「数値」のいろいろな処理については、下記または、こちらのページを参照ください。

表示形式について

メニューバー[書式]⇒[セル]⇒（セル書式設定ダイアログ）で⇒[表示形式]タブをクリック、ここの種類一覧で間に合わないときには、末尾の「ユーザー定義」で設定を行います。
（１）の種類一覧の図

(1)表示形式を「文字列」で設定すると、唯一、数値ではなくなります。表示位置も「左揃い」で表示されます。他は全て「数値表示」なので、計算式にそのまま使用できます。
(2)分数の表示ですが、「1/4」と入力すると「1月4日」になりますので「0 1/4」で記入しました。
(3)指数欄の数式の表示には、['](アポストロフィー)を頭に付けました。従って左寄せに表示されています。なお、指数のベキ乗根は、「 =8^(1/3) 」のように記述します。答えは[2]と表示されます。
⇒（分類（C）で）[ユーザー定義]をクリック⇒【ユーザー定義書式設定ダイアログ】（種類（T）の一覧表の中から適当な表示例を探し、加工します。　下図のセル書式設定ー表示形式の図参照
これらは全て「半角のダブルクォーテーション ["] 」で囲んで「関数の数式」に組み込みます。
また、ダブルクォーテーションは、戻り値が文字列であることを表しています。

（２）のユーザー定義の書式設定
EXCELの関数使用例です。

「ユーザー定義の表示形式」の作成について

使用する書式が組み込みの表示形式にない場合は、書式を組み合わせてユーザー定義の表示形式を作成することができます。
作成された表示形式はブックのすべてのシートで使用することができます。
新規ブックの初期状態では、組み込み表示形式だけが使用できます。

ユーザー定義の表示形式を作成するには、決められた書式記号を使う必要があります。書式記号には、数値、日付や時刻、通貨・パーセンテージ・指数、文字列やスペースなどがあります。　

書式を設定するセル範囲を選択します。
[書式] メニューの [セル] をクリックし、[1.表示形式] タブをクリックします。
[分類(C)] ボックスの一覧で最下部の [2.ユーザー定義] をクリックします。
右側に[種類(T)] ボックスが現れます。
下段のリストボックスには、「0.00」や「#,##0.00」などといった書式記号・基本パターンの一覧が用意されております。
この一覧で目的の表示形式に「最も近い表示形式」をクリックすると、
中段の「3.記入ボックス」に入ります。（「最も近い表示形式」とはあいまいな表現ですが、下表「小数点以下の桁と有効桁数」を参考に）
上段の「4.サンプル欄」に「或る形式を持った数値」表示されます。
「3.記入ボックス」で、一覧から選択した表示形式を編集して「新しい表示形式を作成」します。
上段の「4.サンプル欄」に「目的の形式を持った数値」が表示されたら[5.ＯＫ]ボタンです。
すると、[種類(T)] ボックスの末尾（6）に、新しい「ユーザー定義の表示形式」が追加されます。
削除は、削除したい記号を選択（アクッティブ化）して[削除]ボタンです。
注）ユーザー定義の表示形式の登録は、使用可能なメモリに依存します。従って、登録数は必要最小限にとどめましょう。（予め組み込まれた表示形式を編集しても、その表示形式が削除されることはありません。）

セル書式設定ー表示形式の図

表示形式の作成

ユーザー定義の書式記号は、一つの表示形式で 3 つまでの数値の書式と、文字列用に 4 つ目の書式を持つことができます。
各書式は次のようにセミコロン (;) で区切って指定します。
例 : #,###.00;[赤](#,###.00);0.00;"データ : "@
セミコロンで区切った 4 つの書式はそれぞれ <整の数>、<負の数>、<ゼロ>、<文字列> に対応しています。

4つすべてを定義した表示形式を作成する場合は、この構成順に作成する必要がありますが、一般的には１つか２つ程度と考えられます。

2 つのセクションだけを指定した場合、最初のセクションは正の数とゼロの表示形式になり、 2 番目のセクションは負の数の表示形式になります。

1 つのセクションを指定した場合は、その表示形式がすべての数値に適用されます。
セクションを省略する場合は、そのセクションの後ろのセミコロンだけを入力します。

４セクションを指定した例

　　　　　　　　表示形式　　　　　　　　　　　　　　　入力　　　　　　表示
　　　　------------------------------------------------------------------------
　　　　#,###.00;[赤]-#,###.00;0.00;" データ  "@　　　 123            123.00
　　　　　　　　　　　　　　　　　　　　　　　　　　　-123           -123.00
　　　　　　　　　　　　　　　　　　　　　　　　　　　　 0              0.00
　　　　　　　　　　　　　　　　　　　　　　　　　　　 3月    　　　3月

上記実際の設定は「 #,###.00;[赤]#,###.00;0.00;""@ 」です。[@]を付けないと入力データが表示されません。

「ユーザー定義書式記号」の見方

0を使って位取りを表示する。
0.0で、コンマ以下１桁まで表示します。（下表参照）小数点の両側に表示する桁数を指定します。
#を使って位取りを表示する。(#は井桁の桁です。）
) 小数部に表示する桁数を指定します。
書式[#,###]は整数部の1000の位ごとにカンマを表示します。
0 を入力したら 0 と表示し、さらに桁区切りのカンマも表示するには、[#,##0]という書式を使用します。
([#,##0]と末尾を[0]にするのは、0 を入力した時に 0 を表示させるためです。0 を表示しない場合は[#,###]とします。)

EXCELの関数使用例です。
上は図です。コピー用の表は、こちらに有ります。

なお詳しくは、こちらのページを参照ください。
後半に「書式記号を使用してユーザー定義の表示形式を作成する」があります。

[値]が[シリアル値]の場合

シリアル値とは1900年1月1日を１と定め、その日からの連続日数と時刻を表す値のことです。
　　　１日　は　　1　　　　　　に決めてあります。
　　　１時間は　　0.041666667
　　　３０分は　　0.020833333
　　　１分　は　　0.000694444
　　　１秒　は　　0.000011574
2005/9/13 日は　　38608　　　　1900年1月1日の[1]から数えて[38608]日目になります。

日付関係及び曜日の表示法

＜表２＞
=TEXT(シリアル値,"表示形式")　　　　表示結果
=TEXT(シリアル値,"yy")　　　　　　西暦の下２桁（00～99）
=TEXT(シリアル値,"yyyy")　　　　　西暦 (1900～9999)
=TEXT(シリアル値,"e")　　　　　　和暦 (1～99)
=TEXT(シリアル値,"ee")　　　　　　和暦 (01～99)
=TEXT(シリアル値,"g")　　　　　　元号略号（M・T・S・H）
=TEXT(シリアル値,"gg")　　　　　　元号略号（明・大・昭・平）
=TEXT(シリアル値,"ggg")　　　　　元号（明治・大正・昭和・平成）
=TEXT(シリアル値,"m")　　　　　　月　 (1～12）
=TEXT(シリアル値,"mm")　　　　　　月　 (01～12）
=TEXT(シリアル値,"mmm")　　　　　月　 (Jan～Dec)
=TEXT(シリアル値,"mmmm")　　　　　月　 (January～December)
=TEXT(シリアル値,"d")　　　　　　日　 (1～31)
=TEXT(シリアル値,"dd")　　　　　　日　 (01～31）
=TEXT(シリアル値,"ddd")　　　　　曜日 (Sun～Sat)
=TEXT(シリアル値,"dddd")　　　　　曜日（Sanday～Saturday）
=TEXT(シリアル値,"aaa")　　　　　曜日 (日～土）
=TEXT(シリアル値,"aaaa")　　　　　曜日 (日曜日～土曜日)

時刻の表示法

TEXT関数を使って、文字列に変換したものは、計算に使うことができなくなります。
そこで、[VALUE] 関数で「数値を表す文字列を数値に変換」することができます。これですと、計算に使用することができます。

VALUE 関数

書式：「 VALUE(文字列) 」

注釈　　　：文字列として入力されている数字を数値に変換した結果を返します。
文字列-注釈：文字列には文字列をダブルクォーテーションマークで囲んで指定するか、または変換したい文字列が入力したセルへの参照を指定します。
MS-解説　　：文字列には、Excel が認識できる数値、日付、または時刻を指定してください。それ以外の値を指定すると、エラー値 #VALUE! が返されます。
Excel では、数式中の値は必要に応じて自動的に変換されるため、通常は数式の中で VALUE 関数を使用する必要はありません。VALUE 関数は、他の表計算アプリケーションとの互換性を維持するために用意されている関数です。

補記　　　：なお、裏技的ですが、「 =セル番地*1 」と１を掛けることによって、数値データに戻す方法もあります。

TEXT 関数の使用例

例題１

セル[G4]に[TODAY]関数で日付けを取得(2005/9/13)しておいて、それを「元号年月日」で表示する。
「 =TEXT(TODAY(),"ggge年m月d日") 」で、平成17年9月13日(入力当日)と表示されます。(gggでも可)

例題２

セル[G4]に[TODAY]関数で日付けを取得(2005/9/13)しておいて、それを「西暦年月日」で表示し、曜日を付ける
「 =TEXT(TODAY(),"yyyy年m月d日(aaa)") 」で、2005年9月13日(火)と表示されます。

例題３

下段[F16]にある合計数値を、上段[B3]の「請求金額」の右セル[B4]に「一金○○円也」と表示する。
[B4]セルをアクティブにして「 =TEXT(F16,"一金00円也") 」と入力する。

例題４

9月12日まで、長期休暇、9月13日以降から出勤を関数で表示しました。([IF]関数に[TEXT]関数をネストして)
関数表示の良いところは、いちいち「表示形式」を開かずとも、１行1回で表現できるので、オートフィル機能が使えることにあります。現実にそぐわない例ですが、こんなことができますの例です。(38608は 2005年9月13日のシリアル値です。、)

トップへ戻るいろいろな関数目次へ戻る
 関数の勉強３へ戻るいろいろな関数１へ進む EXCEL関数５へ進む

Excel関数の勉強 ４

文字列操作関数 ２