エクセルのちょっといい話（４）

昨日のコラムで、改定システムのサマリーシートの一部をご覧いただきましたが、右側にあるテスト対象欄に表示されている値（20.41%）は、実は手作業で表示形式を変更したものです。
選択したテスト対象に応じて、自動的に書式設定できればいいのですが、現実にはなかなか難しいものです。

このサマリーの例で述べますと、テスト対象欄にセル番地（H63）を入力しますと、その右隣にそのセルの値が表示されるわけですが、このセルには参照先のセルの書式を引き継ぐことができません。
すなわち、表示形式を変更しないままですと、このセルには「0.20」と表示されてしまうわけです。

それでは、最初からこのセルの表示形式をパーセント表示にしておけばいいと思われるかもしれませんが、そうすると、パーセント表示でないテスト対象（例えばH64セルのCSR）まで、パーセント表示されてしまうという不具合が生じます。

ちなみに、テスト対象欄にセル番地を入力すると、その右隣のセルに対象セルの値が表示されるようにするには、お馴染みのINDIRECT関数を用いれば簡単に実現できます。
セル番地を入力するセルをG7セルとしますと、H7セルにおいて「=INDIRECT(G7)」としてやれば良いわけです。

さて、肝心の書式設定ですが、条件付き書式設定を用いれば良いと思われるかもしれません。しかし、残念ながらこの方法では目的を達成することはできません。
条件付き書式設定では、そもそも表示形式の指定ができないのです。

そうは言っても、テスト対象を変更するたびに手動で書式設定をするのは面倒ですし、それならいっその事、テスト対象欄にはセル番地しか表示しないようにしようとも考えましたが、それもまた、不親切な対応になってしまいます。

マクロを使えば簡単に実現できるのですが、システムシートには基本的にはマクロを置きたくないので（最適レバレッジ設定マクロは最終的にはシステムの外に出す予定です）、何とかワークシート関数だけで対処したいと考えました。

あれこれ試行錯誤した結果、TEXT関数を用いればいいことが分かりました。すなわち、パーセント表示したい場合は、「=TEXT(INDIRECT(G7),"0.00%")」とすればいいわけです。
ただし、その結果は数値ではなく文字列になってしまいますが、このセルでは値が表示されればいいと割り切りました。

もちろん、TEXT関数を使う前に、パーセント表示をするかしないかの、条件分岐を行う必要があります。その条件判断をどのように行うかと考えた結果、D列の単位を使えば簡単に実現できることに気付きました。

そこで、D列の値が「％」もしくは「％/年」の場合はTEXT関数を用いてパーセント表示、それ以外の場合はそのまま数値表示とすることで、完全ではないものの、ほぼ目的を達成することができました。

不完全な点としては、数値表示の場合、全ての数値が小数点以下2桁まで表示されてしまうことと、パーセント表示の場合、マイナスの値が赤字で表示されないということですが、まあ、これくらいはご容赦いただきたいと思います。

なお、条件判断を行うD列の値は、テスト対象セルと同じ行の値である必要がありますが、これは、INDIRECT関数と若干の文字列操作関数を組み合わせれば実現できます。
興味のある方は、ご自身でご確認ください。もちろん、ユーザーの方は、改定システムへの更新後に全ての数式をご確認いただけます。

今回は、TEXT関数を用いれば数値の表示形式を変更できるというお話でした。ちなみに、その結果を元の数値に戻すには、VALUE関数を用います。ただし、TEXT関数にVALUE関数を適用すると、TEXT関数で設定した表示形式は元に戻ってしまいますので、悪しからずご了承ください。


2008年5月15日　追記

TEXT表示された数値を演算可能な数値に戻すには、VALUE関数を用いる以外に、その数値に0を足すか1を掛けても実現できます。
この手法を含む様々なエクセルの裏技は、次の書籍で詳しく解説されていますので、興味のある方はご参照ください。

Excel Hacks 第2版― プロが教える究極のテクニック140選

• 作者: David Hawley
• 出版社/メーカー: オライリー・ジャパン
• 発売日: 2007/11/22
• メディア: 単行本（ソフトカバー）

タグ：エクセル INDIRECT関数 TEXT関数 書式設定 表示形式 株 システムトレード