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

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

ただいまの
回答率

91.37%

  • Excel

    966questions

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

Excel 小数点の桁数判定

解決済

回答 3

投稿 2017/12/06 15:07 ・編集 2017/12/06 15:27

  • 評価
  • クリップ 1
  • VIEW 85

iemon-yasu

score 12

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

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

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

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 3

checkベストアンサー

+3

簡単な判別方法としては「100倍して10で割った余りを求める」です。
例①:1     × 100 ÷ 10 = 10あまり0
例②:1.5   × 100 ÷ 10 = 15あまり0
例③:1.05  × 100 ÷ 10 = 10あまり5
例④:1.005 × 100 ÷ 10 = 10あまり0.5

つまりあまりが0なら小数点1桁以内、0以外は2桁以上とわかります。

余りを求めるにはMOD関数を利用します。

例えばA1セルを100倍して10で割った余りを求めるなら=MOD(A1*100,10)という式になります。
これが0でないときという条件になるので、例えばA1セルの条件付き書式に=MOD(A1*100,10)<>0とすると目的の条件付き書式になると思います。
お試しください。


余談ですが、例えば小数点以下3桁の判断の場合は1000倍した値を10で割った値で判断することになります。
このようにセルの値に10のn剰を掛けた値に対して10で割った余りを求めることになりますので、判定する少数桁数が変動するようでしたら=MOD(A1*10^2,10)<>0としたほうが桁数調整しやすいかもしれません。
参考までに。

(追記)

追記分を見落としておりました。
=LEN(TEXT(MOD(A1,1),"."&REPT("#",3)))-1について、解説はほとんどtakitoさんがしてくれている通りだと思います。

ただ、この式で注意しなければいけないのは小数点以下3桁目までしか見ないということです。
試しに1.0005などをこの式にかけてみるとわかりますが、小数部.0005.000までしか判断材料とならないため.###書式により0に変換されます。
※書式変換の際に少数以下4桁目の丸め処理もしてくれているので、.0006なら.001の扱いとなって3桁と返してくるんですけどね(^-^;

小数桁数がどれくらいまであるのか把握できているのであれば#の数を増やすことで対応できますが、根本的な解決にはならないですね。
というわけで、ご利用に際は注意が必要です。

投稿 2017/12/06 16:06

編集 2017/12/07 09:50

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/12/07 10:19

    >jawaさん
    考え方から、実現の方法までご教示いただきありがとうございました。
    jawaさんの考え方を基にして、無事条件付き書式を設定することができました。
    考え方の基礎を学べ、その上で実現までできたことから、jawaさんの投稿をベストアンサーとさせていただきます。
    追記情報のコメントも加え、助けていただきありがとうございました。

    キャンセル

  • 2017/12/11 19:16

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

    キャンセル

+2

=LEN(TEXT(MOD(A1,1)"."&REPT("#",3)))-1 

TEXT関数が成立していない(カンマが無い)のでその点を修正

LEN(TEXT(MOD(A1,1),"."&REPT("#",3)))-1

これがやろうとしていることは

MOD関数を使って整数1で割って余りを出す → 小数点以下を抽出
TEXT関数で".###"の書式にあてはめる → 小数点以下4桁目以降をカット
LEN関数で全体長さから小数点(.)の1文字分を差し引く → 最終の長さが求まる(0~3)

です

これで小数点以下桁数が3以上のものは「3」となります
以上で理解できそうでしょうか?

投稿 2017/12/06 16:19

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/12/07 10:16

    >takitoさん
    わかりやすく解説していただきありがとうございました。自分では理解・解釈の追いつかない表現だったので助かりました。

    キャンセル

+1

文字列としてはどうでしょうか?

例えば"3.281" だと

小数点の位置を割り出す。

その後、そこから次の位置 ( pos + 1 )から 最後までを取得し、

その桁数が2以上なら セルを赤くする...

みたいにすればいいのでは?

変換

変換


もし、一般ユーザみたいにExcel本体のっていう意味なら

「Excel 小数点」で検索した結果、

任意の小数点以下の桁数に数値を丸める

がヒットしました。

これらはどうでしょうか?

これを赤くするなら、

少数のみ色をつける方法

はどうでしょうか?

小数点があるやつになっていますが、

条件の項目を見ると

= A1 - INT(A1) > 0

となっています。

INT() はたぶん整数値に変換するやつだと思います。

で、これを 小数点2以降ってしたいのですよね。

小数点2以降ってことは...

再度、質問内容を読み返したところ、

"=LEN(TEXT(MOD(A1,1)"."&REPT("#",3)))-1"

とありますね。

検索結果

でしょうか。

たぶん、

"."&REPT("#",3)

の部分は 文字列の連結かなと。

VBAでは

"Hello " & "VBA"

のようにすれば

"Hello VBA" みたいになります。

その意味で、

小数点プラス数値( ワイルドカードって言う感じ? ) と、MODで割り出した値をつけて

MODで割り出した数値 + "." + ワイルドカード的なもの

を文字列 ( TEXT() ) として固めて、その長さ ( LEN() ) の長さから -1 している

のだと思います。

投稿 2017/12/06 15:14

編集 2017/12/06 16:14

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/12/06 15:29

    > BeatStarさん

    ご回答ありがとうございます。
    考え方はそのイメージでいけると思うのですが、
    いかんせん関数に不慣れなもので、数式がわからなくて・・・

    キャンセル

  • 2017/12/06 15:31

    ん?
    数式ってどういうことでしょうか?
    使い方ってこと?

    キャンセル

  • 2017/12/06 15:52

    条件付き書式にて使用する数式です。
    「数式を使用して、書式設定するセルを決定」にて、
    小数3桁以上のセルの書式設定を行いたいと思うのですが、
    その数式がなかなか上手くいかない状況です。

    キャンセル

  • 2017/12/06 15:58

    もしかして、VBAじゃなくて Excel本体のやつ!??
    カテゴリにはExcelってあるけど、この掲示板?ってプログラミング用だからVBAかと思った...

    Excel自体のなら、ここではあまり質問しないほうがいいかも。
    勘違いされるし。

    メインの回答に返信用を追記しました。

    キャンセル

  • 2017/12/06 16:10

    横から失礼しますm(__)m
    VBAタグとExcelタグは別に用意されていますので、今回はVBAは使わずワークシート関数とシート上の機能だけで実現したいのだと思いますよ。

    キャンセル

  • 2017/12/06 16:16

    jawaさん。
    かもしれませんね。
    ですが、最初マジでVBAの方だと思ったので...

    キャンセル

  • 2017/12/07 10:14

    >BeatStarさん
    勘違いさせてしまい申し訳ないです。
    jawaさんのおっしゃる通り、Excelのワークシート関数及びシート機能を用いての実現を目指しておりました。
    次からは質問の仕方を考えて質問させていただきますね。
    ありがとうございました!

    >jawaさん
    私の記載漏れによって生じた勘違いでしたが、コメントありがとうございました。

    キャンセル

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

ただいまの回答率

91.37%

関連した質問

  • 解決済

    Excel VBAの質問 

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

  • 解決済

    不定行数での合計方法について

    エクセルで不特定行数で合計をしたいです。 下記のようなデータがあります 1 2 3 test 1 test 3 9 test 上記を"test"の区切りごとに合計したいです。

  • 解決済

    【エクセル】データが存在する一番上のデータを取得したい

    上記の表で増加率を出したいです。 青のりアジサイは =(B16-B3)/B3+100% でできましたが、横にスライドすると開始データがC3になってしまい取得できません。 Cの場合

  • 解決済

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

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

  • 解決済

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

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

  • 解決済

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

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

  • 解決済

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

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

  • 解決済

    Sumproductがうまくいかない

    以下のように建物だけを条件に積和を出したいのですがうまくいかないです。 どこにもんだいがあるのでしょうか? 宜しくお願い申し上げます。 建物 10 ,20 土地  30 ,40

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

  • Excel

    966questions

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