こんにちは。
知人からの依頼で某Excelツールを作成中です。
あるセル範囲のうち最大値を表示してほしい、その際…
小数点以下がある時だけ小数点以下を表示
そうでなければ整数表示したい
と依頼されました。
「表示形式」機能のやりくりだけで簡単にできるだろーと想像しながら二つ返事。笑
後から気づいたのですが、一筋縄ではいかなそう…、で備忘録することに。^^
■ 動作環境
OS: Windows 10 Home（64ビット）
Excel: 2019（64ビット）
■ 小数点以下がある時だけ表示…とは
こんなイメージです。
「91.00」のところが、小数点以下が削除されて「91」になっていますよね！
それ以外は全て小数点以下があるため、そのまま表示も維持されています。
■ 解決までの考え方
表示の仕方を変更するパターンが1種類だけであれば、「表示形式」機能のやりくりだけで解決できます。ただし、今回のように条件に応じて表示形式を変える必要がある場合は、それだけでは解決できません。
結論としては、「条件付き書式」機能やVBAじゃないと解決できません…涙
今回は、簡単に済ませたかったので、「条件付き書式」機能での解決方法を試みました！
■ 小数点以下がある時だけ表示するためには…
① 既定の動作として、「表示形式」機能で、小数点以下ありきの設定を適用します。
今回の場合は「#,##0.#####」と設定
小数点以下の部分は、「#」ではなく「0」を併用することで任意の桁数を固定することも可能
ちなみにこの状態だと以下のように変換表示されます。
（まだ道半ば、「91.」のところが微妙ですよね…💦）
② 条件付きの動作として、「条件付き書式」機能で、整数表示の設定を適用します。
条件として「小数点以下が存在しないケース」を指定 =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は奥が深いですね…。
次回以降も、苦労した点を備忘録しようと思います。
Comments