質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%

Q&A

2回答

4346閲覧

Excel 小数点の桁数判定(VBAではありません)

iemon-yasu

総合スコア18

0グッド

0クリップ

投稿2017/12/11 10:20

編集2022/01/12 10:55

以前、「Excel 小数点の桁数判定」というタイトルで、12/6頃に質問させていただいたものです。

前回の質問でご教授いただいた条件付き書式のロジックで、やりたいことは実現できたのですが、
入力する数値に100億以上の数値が入っている場合は、Excelの字数制限に引っかかり条件付き書式が効かなくなってしまいました。(10,000,000,000*1,000/10=1,000,000,000,000)になってしまうため。
こちら、Excelの字数制限の緩和や、ロジックの変更等で対応できませんでしょうか?
何度もお願いしてしまい申し訳ありませんが、お助けいただけると幸いです。宜しくお願いします。

Excelで、セルに小数点2桁以上入力された場合は条件付き書式でセルを赤くしたいのですが、数式がわかりません。
ぜひ、宜しくお願いします。

インターネット検索で近しいものは見つけられたのですが、解説もなく・・・
=LEN(TEXT(MOD(A1,1)"."&REPT("#",3)))-1

解釈できたのは、MOD関数でA1セルのあまりを取得して、
あまりの中で、小数点(.)以下3桁を指定している・・・?
ようには見えるのですが、他の関数が何をやっているかもわからず。
上手い具合に、自分で編集することもできない状況です。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

hatena19

2017/12/11 14:08

現在、条件付き書式に設定してある式を提示してください。
guest

回答2

0

ところで計算機で10進数の小数を扱う場合必ず誤差があるというのはご存知でしょうか?
小数点付きの数値をEXCELに入力するとそれは内部では浮動小数点数として2のべき乗の値しか保持できない形式として保存されます。つまり0.1とか0.01は正確には表現できてなくて見かけ上

0.1
0.01

のように表示しているだけなのです・・・さらに浮動小数点数では精度に限りがあり最大でも10進数で15桁程度しかありません。

1,0000,0000,0000.00

このような数値(兆のオーダー)にたっすると小数以下3桁未満はもはや誤差が大きくその桁が0であるかどうかは正確に判定しようとすること自体に意味がなくなります。

例え数値の桁数が小さくても10進数での小数点以下の数値は正確に表せないのですが、TEXT(値, ".##")は10億程度までの値だと「まぁ、このくらいの誤差なら小数点以下2桁ぐらいまで文字列に変換してもいいかな?」といった割とアバウトな基準で文字列変換していると捉えても良いかも知れません。それゆえ誤差が大きくなりそうなところ(100億程度)では「ちょっと誤差が大きくなりそうなんで変換失敗にしておこう」ということで計算結果が#NUM!になるのだと思います。15桁よりは若干余裕のあるところで変換エラーにして安全に倒しているのではないかと想像します。

もし「誤差があってもかまわないので100億までなんとか小数点以下3桁が指定されたかどうか判定したい」ということでしたらやり方はありますが、上記の点を踏まえて「本当にそれでいいのかどうか」を再考した方がよいようにも思えました。

必要なのは100億までなのでしょうか?1000億?それとも?


訂正:すみません、訂正します。TEXTで#NUM!エラーになると上で述べましたが、正しくはMOD(数値,1)で#NUM!エラーになるのでした。ただつまるところ原因は上に述べたとおり精度の限界であるとは思います。

投稿2017/12/11 12:15

編集2017/12/11 13:36
KSwordOfHaste

総合スコア18392

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

KSwordOfHaste

2017/12/11 22:36 編集

なんだか自分のコメントは考えすぎだったかも。こんなふうに考えてました。 通常の書式として小数以下2ケタ(#.00など)を指定してあり、そこへ小数以下3桁まで入力されてしまうと表示上それが見えないので条件付き書式で3桁以上のときに「赤」にしているのではないか。しかしケタが大きな数字を入力されると小数点以下の精度がないため指定数値の小数点以下の部分に意味がなくなってくるのでそれが問題になりはしないか・・・といった感じです。 そのケースでは入力した値の小数以下が切り捨てられるので入力者自身ですぐ気づけますね(w;
guest

0

MOD や * を使うと数値として扱われますので、桁数が大きいと#NUM!エラーになりますね。
文字列にして、小数点(.)以下の文字数を取得するようにすれば、どうでしょう。
条件付き書式に下記の式を設定してください。

=LEN(A1 & "")-FIND(".",A1 & ".")>1

A1 & ""は空文字列を付加することで数値を文字列に変換してます。
A1 & "."は小数点を最後に付加することで、文字列に変換すると同時に、整数の場合にエラーにならないようにしてます。
FIND関数で小数点の位置を求めて、文字列の長さから引くことで、小数点以下の桁数を取得します。


浮動小数点の誤差に関しては、WEB検索すると解説ページが見つかると思いますので、よく読んで検討してください。

例えば、
Excel で浮動小数点演算の結果が正しくない場合がある

投稿2017/12/11 15:10

編集2017/12/11 15:14
hatena19

総合スコア33620

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問