こんにちは。
知人からの依頼で某Excelツールを作成中です。
あるセル範囲のうち最大値を表示してほしい、その際…
小数点以下がある時だけ小数点以下を表示
そうでなければ整数表示したい
と依頼されました。
「表示形式」機能のやりくりだけで簡単にできるだろーと想像しながら二つ返事。笑
後から気づいたのですが、一筋縄ではいかなそう…、で備忘録することに。^^
■ 動作環境
OS: Windows 10 Home(64ビット)
Excel: 2019(64ビット)
■ 小数点以下がある時だけ表示…とは
こんなイメージです。
「91.00」のところが、小数点以下が削除されて「91」になっていますよね!
それ以外は全て小数点以下があるため、そのまま表示も維持されています。
![対策後の表示結果](https://static.wixstatic.com/media/2b55d5_38ab1d7d2cbd42c5b26fb78cbf8d8b31~mv2.jpg/v1/fill/w_75,h_55,al_c,q_80,usm_0.66_1.00_0.01,blur_2,enc_auto/2b55d5_38ab1d7d2cbd42c5b26fb78cbf8d8b31~mv2.jpg)
■ 解決までの考え方
表示の仕方を変更するパターンが1種類だけであれば、「表示形式」機能のやりくりだけで解決できます。ただし、今回のように条件に応じて表示形式を変える必要がある場合は、それだけでは解決できません。
結論としては、「条件付き書式」機能やVBAじゃないと解決できません…涙
今回は、簡単に済ませたかったので、「条件付き書式」機能での解決方法を試みました!
■ 小数点以下がある時だけ表示するためには…
① 既定の動作として、「表示形式」機能で、小数点以下ありきの設定を適用します。
![セルの書式設定](https://static.wixstatic.com/media/2b55d5_54add2e8f6074076836ab1920045d381~mv2.png/v1/fill/w_53,h_54,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_auto/2b55d5_54add2e8f6074076836ab1920045d381~mv2.png)
今回の場合は「#,##0.#####」と設定
小数点以下の部分は、「#」ではなく「0」を併用することで任意の桁数を固定することも可能
ちなみにこの状態だと以下のように変換表示されます。
(まだ道半ば、「91.」のところが微妙ですよね…💦)
![道半ばの表示結果](https://static.wixstatic.com/media/2b55d5_45681abf2297456bace94879fed3f83f~mv2.png/v1/fill/w_90,h_65,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_auto/2b55d5_45681abf2297456bace94879fed3f83f~mv2.png)
② 条件付きの動作として、「条件付き書式」機能で、整数表示の設定を適用します。
![書式ルールの編集](https://static.wixstatic.com/media/2b55d5_89dc6d41351340faae6624e4cd8d2ee8~mv2.png/v1/fill/w_99,h_102,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_auto/2b55d5_89dc6d41351340faae6624e4cd8d2ee8~mv2.png)
条件として「小数点以下が存在しないケース」を指定 =INDIRECT(ADDRESS(ROW(),COLUMN()))=TRUNC(INDIRECT(ADDRESS(ROW(),COLUMN()))) ADDRESS(ROW(),COLUMN()) [条件付き書式]が評価される際の該当セルの参照文字列を取得 INDIRECT(セルの参照文字列) 指定された文字列が示すセルの値を参照 TRUNC(数字) 指定された数字の小数点を切り捨て よって、「該当セルの値」と「該当セルの小数点以下を切り捨てた値」は同じ?という条件を指定していることになります。(Trueであれば、元々小数点以下はなかったのだ…と判断できます) ちなみに1で割ったときの余りが0になるはずなので、余りを求めるmod関数を使って表現してもいいですよね! =MOD($E2, 1)=0
表示形式として「整数型」を指定 #,##0
■ まとめ
いかがでしたでしょうか?
最近
「excel 小数点 表示 ある時だけ」
「excel 小数点 ある時だけ」
などで検索し、当方サイトへ訪問されるケースが増えてきてます。
小数点以下がある時、ない時で表示形式を変えるニーズはよくある話なのだと思いますが、実現するとなると一工夫必要な代表例だと思います。
Excelは奥が深いですね…。
次回以降も、苦労した点を備忘録しようと思います。