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

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

ただいまの
回答率

90.86%

  • Excel

    1328questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

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

受付中

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 609

iemon-yasu

score 12

以前、「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桁を指定している・・・?
ようには見えるのですが、他の関数が何をやっているかもわからず。
上手い具合に、自分で編集することもできない状況です。

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • hatena19

    2017/12/11 23:08

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

    キャンセル

回答 2

+1

ところで計算機で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/12 07:36 編集

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

    キャンセル

+1

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

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

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


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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

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

  • ただいまの回答率 90.86%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

  • 解決済

    Excel VBAの質問 

    excel vbaの複数セルを一つにするマージ方法です。 複数行の値を一つのセルにコピーしていく方法をおききしたいです。

  • 解決済

    Excelである法則で連続データで入力する方法について

    Excelでとある法則で連続データで入力する方法を2通り考えています。できればマクロ、VBAを使わずにスムースにできる方法は無いでしょうか。   ステージ |取得スコア 1

  • 解決済

    excelのLookup関数の使い方について。

    Lookup関数で1つのセル(仮にA1のセルだとします)に「午前中」の文字が入ったら、他のセル(ここではB1だとします)は「0812」とセルを置き換えをしたいです。 その場合

  • 解決済

    共有ファイルにてexcelやwordが誰かが開いているとき、それが誰か特定する方法

    掲題のとおりですが、下図のように、とあるディレクトリで~$となっている場合だれかがファイルを開いていることがわかります。 googleドライブの場合でしたら開いている

  • 解決済

    excelの60進数で、計算を行う関数を教えてください

    excelのセルに、2つの時間データがあるのですが、この2つのデータの時間差を求めたいのですが (セルのデータは時間ですが、時分はコロンで区切られておりません) <例1>

  • 解決済

    EXCELでの8行文のオートフィル

    初めまして。 EXCELのオートフィルで下記のような動きを実現させたいです。  1から8行目が元になるセルで、9行目以降がオートフィルしたいセルです。 1行目 L17 2行

  • 解決済

    経過日数の計算について

    下記の問題をIf文とFor文を利用してどのようにプログラミングしていけば良いのか分かりません。大変お手数ですが、教えて頂けると幸いです。 入力年月日1から2までの経過年、日を計

  • 解決済

    Excel 相対参照で文字列に+1したいです。

    お疲れ様です。 Takkoです。 現在Excelを使用して試験用データを作成することとなり、 約9万件のテストデータを作成することとなりました。 そこで尋ねたいことがありま

同じタグがついた質問を見る

  • Excel

    1328questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。