EXCEL生産性向上委員会ちょっと便利テクニック２３

08/06/2005 -
２３．特定の条件によってセルの表示形式を変える
	ここでは、「数値の範囲指定や特定の数値指定よりも複雑な条件によってセルの表示形式を変える方法」を紹介します。
（復習）	似たようなテクニックとして、ちょっと便利テク８「数値によって自動的に文字の色を変える」では、数値が正か負かゼロか、または数値の範囲や特定の数値を指定して文字の色を変える方法を紹介しています。例えば、8-2では『ユーザー設定の書式に　[赤][<10]#,##0;[青][<100]#,##0;[黒]#,##0　と書くと、１０未満は赤、１０以上１００未満は青、１００以上は黒の文字色になります。』と紹介しています。この例では数値の範囲によって”文字の色”を変えることに着目していますが、実は数値の範囲によって”表示形式”を変えることも可能です。
	上記の書式をユーザー設定で [<1000](0);[<10000]<#,##0.00>;#,##0円　と指定すると、右図の２～４行目のB列のように表示されます。具体的には、1000未満は（）で囲み、1000以上10000未満は< >で囲んで3桁区切り・小数点以下2桁表示、それ以外は3桁区切りで”円”を付けて表示となります。もし書式を　[=100](0);[=1000]<#,##0>;#,##0　と指定すると、右図の６～９行目のB列のようになります。具体的には、100の時は( )で囲み、1000のときは< >で囲んで3桁区切り、それ以外は3桁区切りという表示になります。	図1
	このように、数値の範囲や特定の数値を指定して（指定は3つまで）表示形式を変える事は出来ますが、　・小数点以下の有無といった数値の大小ではない条件で表示形式を変えたい　・４つ以上の数値の範囲について異なる表示形式を指定したい　・他のセルの内容によって表示形式を変えたいといった場合には、このユーザー設定書式の方法は使用できません。そこで、より複雑な条件で表示形式を変える方法を考えて見ました。
	因みに、8-3や8-5で紹介している「条件付書式」を使うと、数式を指定して複雑な条件設定を行うことが可能ですが、条件付書式で変更できるのはフォント・罫線・パターン（セルの背景）だけで、表示形式を指定する事は出来ません。つまり、条件付書式では上の赤字で書いたようなケースには対応できません。

（例題）	例題として、株やその他の有価証券の価格をエクセルに入力して管理する場合を考えて見ます。株の売買をしている方は、その取引履歴をエクセルで記録するケースが多いのではないでしょうか。通常、株価は円単位（小数点以下の数字が無い整数）です。しかし、日経平均株価やTOPIX指数は小数点以下2桁まで表示しますし、株価指数先物や上場CB、個人向け社債の店頭気配なども小数点以下2桁まであります。さらにVWAP（加重平均）価格で株取引する場合（またはVWAP価格を記録したい場合）などは小数点以下4桁まで扱うようです。つまり、扱う数値が、条件（銘柄）によって整数だったり、小数点以下２桁まで有効だったり、小数点以下４桁まで有効だったりする、というケースということです。
	右の図2は、普通に価格の数値をＣ列に入力して3桁区切りを表示させ、小数点以下の桁数をセルごとに調節したものです。価格の表示の桁（小数点の位置）が揃っていないので、見にくい状態です。	図2
	図2の表の価格欄（C列）を、小数点の位置を揃えて図3のように表示させたい場合、どうしたらよいでしょうか。（なお、桁を揃えるためにフォントは「ＭＳＰゴシック」等のプロポーショナルフォントではなく「ＭＳゴシック」等の等副フォントにする必要があります）	図3
	まず、標準機能でどこまで出来るか考えて見ます。Ｃ列について、「桁区切りスタイル」を設定すると、図4のようになります。 3桁区切りのカンマは入りますが、小数点以下が四捨五入されて整数表示になってしまいます。もちろん、これではダメです。	図4
	図5	そこで、Ｃ列で「セルの書式設定」を開き（Ctrl + 1）、図5のように「小数点以下の桁数(D)」に”４”を設定すると、下の図6のようになります。（因みに、桁区切りスタイルを設定した図4の状態では、Ｃ列の書式は図5の「分類(C)」が”通貨”、「小数点以下の桁数(D)」が”０”、記号(S)が”なし”になっています）小数点の位置は揃いましたが、トヨタやヤフーのような整数の株価にも”.0000”が表示されてしまっていますし、日経平均のように小数点以下2桁までの表示で良い部分にも小数点以下4桁まで0が余計に表示されてしまっています。
		図6
	図7	では、セルの表示形式で「ユーザー定義」を使うとどうでしょうか。図7のようにユーザー定義で　#,##0.##??;[赤]-#,##0.##??　という書式を指定すると、下の図9ようになります。
		図8
		書式にの小数点以下の部分を ##??　としたので、日本電産（VWAP）のように小数点以下4桁まであれば下4桁全て表示されますし、日経平均のように小数点以下2桁までのものは小数点以下3桁目4桁目の部分はスペースが表示されています。しかし、トヨタやヤフーのように小数点以下が無い場合は小数点以下のゼロは表示されないものの、小数点だけは表示されてしまい、また小数点以下はスペース2文字分しか空白が無い状態になっています。（ "?"はゼロの場合空白となるが、"#"はゼロの場合空白も表示されないため）同様に、「三菱マ4」は価格100.30ですが、100.30 ではなく100.3 と表示され、その後ろの空白も3文字ではなく2文字になっています。結果的に、小数点の位置は揃っていません。
	図9	そこで、書式に図9のように　#,##0.00??;[赤]-#,##0.00??　を指定すると（ ##?? の部分を 00?? に変更）、図10のようになります。
		図10
		今度は、「三菱マ4」も100.30となって桁の位置が揃いましたが、やはりトヨタやヤフーのような整数で良い株価にも .00と余計な表示がされてしまっています。このトヨタとヤフーの２つだけについて、小数点も含め小数点以下を表示せず、かつ他の行と桁を揃えるためには、図11のような書式を設定する必要があります。 #,##0" ";[赤]-#,##0" "と、#,##0 の後ろに "" で囲って半角の空白を5つ入れています（空白は""で囲わなくてもOKです）。これで桁を揃えています。その結果、図12のように（これは目標としていた図3と同じですが）整数は小数点も小数点以下の数字も表示されず、その他は小数点以下2桁まで、または4桁まで表示されるようになります。
	図11	図12
	つまり、この例題のようなケースでは１つの表示形式設定だけで全てを解決させることはできず、銘柄の区別または価格の小数点以下の有無によって２つの表示形式　#,##0.00??;[赤]-#,##0.00??　と　#,##0" ";[赤]-#,##0" "　を使い分けないといけないということになります。
	今回の例では、　・銘柄コードが9999よりも大きい場合は　#,##0.00??;[赤]-#,##0.00??　という表示形式、　・それ以外（銘柄コードが9999未満の場合）は　#,##0" ";[赤]-#,##0" "　という表示形式で数値を表示させることにします。

	３つの方法を考えてみました。方式２と３はマクロを使用しますので、「ちょっと便利テク」の主旨からは外れますが、簡単なマクロですしついでに紹介してしまいます。
	方式１：表示形式を指定した表示用セルを数値入力用のセルとは別の場所に設ける方式	関数だけで簡単に実現できますが、表示用セルと数値入力用セルを分けないといけません。
	方式２：既に数値がセットされたシートに対し、マクロで数値のセルに一括で書式を設定する方式	CSVファイルを読み込んだ場合など、既に数値がセットされたシートを印刷用に表示形式を設定する（以後、数値の変更は行わない）場合などにに向く方法です。マクロを使用します。
	方式３：数値を入力する都度、その数値を判定して書式をマクロで自動セットする方式	数値の入力や変更が常に発生するようなシートの場合に、数値を入力した時点で条件を判定して表示形式を設定する方法です。マクロを使用します。

方式１	表示形式を指定した表示用セルを数値入力用のセルとは別の場所に設ける方式

	Ｃ列は表示用として、数値入力欄を別に設けます。図13の例では、Ｅ列に数値を入力します。Ｃ列には、IF文でＡ列の銘柄コードを判定し、その結果によってTEXT関数でＥ列の値を２種類の表示形式で変換してＣ列にセットする関数（下図の赤枠で囲んだ部分）を記述しておきます。
	図13
	なお、TEXT関数は書式で変換した文字列を表示するだけで、表示色の指定は出来ないので、　#,##0.00??;[赤]-#,##0.00??　といった表示形式をTEXT関数の中で指定してもＣ列の表示は赤字になりません。
	もし、価格の前日比などマイナスの数値も扱う場合でマイナスは赤字表示したい場合は、Ｅ列の値によってＣ列の文字を赤くする以下のような条件付書式を指定しましょう。
	図14	図15

方式２	既に数値がセットされたシートに対し、マクロで数値のセルに一括で書式を設定する方式

	この方式２は、既に価格が入力されたシートがある場合（他人が作成した、ウェブなど何かのサービスからダウンロードした、といったケース）に、その価格部分を特定の条件によって複数の表示形式に一気に設定してしまう場合に使えそうな方法です。 C2からC100に価格が既にセットされているとします。（このあたりの条件はケースバイケースで修正してください） Alt+F11でマクロを開き、「挿入(I)」「標準モジュール(M)」とします。そして、図16のようにマクロを記述してください。このマクロを実行すると、Ａ列の銘柄コードを判定しながらＣ列に２種類の表示形式のどちらかをセットしていきます。

図16

方式３	数値を入力する都度、その数値を判定して書式をマクロで自動セットする方式

	この方式３は、方式１のように入力欄と表示欄を分けずに、マクロを使って数字を入力するそばから条件を判定して表示形式を設定する方法です。やはり、価格を入力・表示する欄はSheet1のセルC2からC100まであることとします。（このあたりの条件はケースバイケースで修正してください）また、銘柄コードによって設定する表示形式を判断しますので、価格を入力する行には銘柄コードが先に入力されていることを前提とします。 Alt+F11でマクロを開き、図17のように画面左側でSheet1を選択し、WorksheetオブジェクトのChangeプロシージャに以下のマクロを記述します。これで、Ｃ列に数値を入力して確定（Enterキーを押す）した瞬間に、Ａ列の内容を判定してＣ列に２種類の表示形式のどちらかをセットします。

	なお、このマクロを設定した場合、Ｃ列に数値を入力後に Ctrl+Z を押しても（または「元に戻す」ボタンを押しても）、数値の入力は元に戻りません。（他の項目は戻る）

図17


	Web excel7.com