【EXCEL】小数点のあるセルをみつける

質問

EXCELで、数字を入力してもらう入力用フォームを作成しているのですが、
フォーム内に、小数点を含んだ数字の入力をさせないようにしたいのです。

「データ＞入力規則」で、小数点を使わせないように設定すると
直接入力すれば警告を表示させることができますが
コピー＆ペーストだとエラーが表示されず、入力ができてしまいます。
「書式＞条件付書式」で、
小数点のあるセルの色を変えることも考えたのですが、
それだと、表全体から色の変わっているセルを探さなければならないので
できれば関数などを使用して、選択した範囲内に小数点が含まれていたら
「小数点のある数字が入力されていますので、修正してください」
などの警告を、特定のセルに表示させたいのです。

ひとつのセルに小数点があるかどうかを調べる関数までは見つけたのですが、
複数のセル内から見つける方法がなかなか考え付きません。

何年越しの悩みです。
宜しくお願いいたします！

回答No.4

noname#79209 · ベストアンサー

#2です。
警告メッセージを出したいセルをA1、
検査したいセル範囲をA2:A10とした場合、
A1セルに、
=IF(SUMPRODUCT((A2:A10<>INT(A2:A10))*1)>0,"小数あり","")
で
A2～A10に小数があれば、A1に「小数あり」と表示されます。

お礼

↑間違えて「補足」にお礼を入力してしまいました。。。
素人間違いゴメンナサイ。。。

補足

何度もご回答いただきありがとうございます。

早速試してみました。
すごい！完璧です！
この数式使わせていただきました。
これでやっとフォームつくりの次のステップへ進めます♪
本当にどうもありがとうございました！

また何かありましたら宜しくお願いいたしますm(_ _"m)

回答No.8

noname#79209

うーん。そろそろマクロの領域に入っていますね。
エクセルのワークシート関数はセルの実際の中身でなく、関数等の計算結果に対して行われるので、
ワークシート関数を使って、そのセルが計算式かどうかをチェックできません。

ただ、そのセルがロックされているか否かをチェックする関数があるので、
入力してほしいセルのロックを全て解除しておけばこれでチェックできるかもしれません。
これでシート保護をかけておけば、誤って数式の部分にデータを入力されてしまうのも防げますし．．

もう一つは、手入力すべきセルを選択して、名前をつけてしまい、
A2:A10としている部分をこの名前にしてしまえば、計算式が入っているセルを検索対象から除外できます。

お礼

むむむ。。。マクロですか。。。
かなり高度な領域に突入していきそうな雰囲気ですね。
自分自身がついていけないような空気を感じます（焦）
＃7までで教えていただいた関数を使って、
前回よりも十分パワーアップした入力フォームが出来上がりそうです！

何度も回答くださってどうもありがとうございましたm(_ _"m)

回答No.7

noname#79209

#2です。調子に乗って更に追加です。

そのブック名が「Book1」、シート名が「Sheet1」、検索する範囲がA2～C10、
かつ、#6が導入されていた場合、

=IF(SUMPRODUCT((A2:C10<>INT(A2:C10))*1)>0,HYPERLINK("[Book1]Sheet1!"&ADDRESS(B1,C1),ADDRESS(B1,C1)&"に小数あり"),"")

で、A1をクリックすると、小数点があるセルへジャンプします。

お礼

閉じるのを忘れていました。でも閉じなくてよかったです！
ありがとうございます！
すすすすごいですね！
フォームに「小数点があります」と表示させても
「でもどこにあるかわからなかったからそのままにしておいた」
と言われることが多いので
（ctrl+Fで検索してくれればよいだけの話なのですが・・・。）
小数点のあるセルを指定してあげられると大変助かります！

初心者なため、教えていただいた関数や配列数式についていちから調べました（汗）。

ひとつ質問なのですが、
max関数を使って、「小数がある最後のセル番地が表示されます」とのことでしたが、
フォーム内には、途中sum式がいくつも挿入されていて
「最後のセル番地」として、直接小数点を入力したセルではなく
sum式のセルを返してきてしまうので
max関数をmin関数に変えてみたら、B1セル、C1セルともに
「0」という計算結果になってしまいました。

【参考】文章だと分かりずらくてすみません。

　　　　A
1 　　　１
2 　 ２．５
3 =sum(A1:A2)
4 　　　３
5 　　　４
6 =sum(A4:A5)
7 =sum(A3,A6)

このような感じのフォームです。
本当は計算結果で「A2（小数点2.5）」を導き出してほしいのですが、
「A7（計算結果10.5）」が出てきてしまいます。

私が関数をしっかり理解していないせいでこのような結果になってしまったと思うので
お聞きするのもはずかしいのですが、
「小数点がある最初のセル番地を表示」させることは難しいですか？

何から何まで頼ってしまってすみません。。。
お時間あるときにでもまたお願いいたしますm(_ _"m)

回答No.6

noname#79209

#2です、よかった！まだ開いていましたね。
B1.C1セルを使って良いなら

B1に
=MAX((A2:A10<>INT(A2:A10))*ROW(A2:A10))
と入力して、「ENTER」でなく、「SHIFT」と「CTRL」を同時に押したまま「ENTER」
C1に
=MAX((A2:A10<>INT(A2:A10))*COLUMN(A2:A10))
と入力して、「ENTER」でなく、「SHIFT」と「CTRL」を同時に押したまま「ENTER」
A1を
=IF(SUMPRODUCT((A2:A10<>INT(A2:A10))*1)>0,"小数あり","")
から
=IF(SUMPRODUCT((A2:A10<>INT(A2:A10))*1)>0,ADDRESS(B1,C1)&"に小数あり","")
と変更。
こうすると、もしA2、A5、A8に小数が入っていたら
「$A$8に小数あり」と小数がある最後のセル番地が表示されます。
B1、C1に数式を入れる時に「SHIFT」と「CTRL」を同時に押したまま「ENTER」を押すのが面倒ですが．．．
数式の内容を変更した時も「SHIFT」と「CTRL」を同時に押したまま「ENTER」です。

回答No.5

noname#79209

#2です。
お役に立って何よりです。
あと、条件付書式も併用すると、探し易いかも知れませんね。

回答No.3

noname#79209

最初だけ手間がかかりますが、作業用の列を作らないで済みそうです。

入力させるセルを選択し「条件付書式」で、
「数式が」「=(A1<>INT(A1))=TRUE」とし、書式に「パターン」「赤」に設定すれば、
小数点つきデータを入力するとバックが赤になりますし、小数点なしのデータを入れるとバックは白になります。

なお、複数セルを選択している場合は、上記数式の「A1」の部分をアクティブセル（選択されている中でバックの色が白いセル）のセル番地にして下さい。

お礼

たびたびありがとうございます！
条件付書式は私も考えたのですが、
質問文にも書かせていただいたように、
表全体から色の変わっているセルを探さなければならないので
できれば避けたいと思っていたのです。。。
（表全体を画面に一発で表示できればいいのですが
　かなり大きいため左右ともにスクロールが必要なのです。）

わがままばかり言ってすみません。。。
またもし名案ありましたら宜しくお願いいたしますm(_ _"m)

回答No.2

noname#79209

やはり作業列は必要です。
前提：A列データ
　　　B列作業列
　　　　　A　　　　　　　B　　　　　　　　
1　=IF(SUM(B2:B22)>0,少数点あり","")
2 以下データ 　=(A2<>INT(A2))*1
　　　　　　　　　以下関数コピー

原理：
　　もし、小数点以下があれば、INT(A2)とA2とは異なるのでTrue、無ければFalseとなります。
　　しかし、このままでは数値として使えないので、*1で計算させ、Trueは1、Falseは0とします。
　　これを合計して、1以上なら小数点が存在すると判断できます。

お礼

ありがとうございます！
たて170行×横6列が10シートくらいある
非常に大きなフォームなので、
全セルに作業用の数式を入力すると
フォーム自体も重くなりそうなのでなるべく避けたいのですが、
やはりこのような方法が一番妥当ですね。
どうもありがとうございましたm(_ _"m)

回答No.1

245689731

作業列を使う方法です。

前提　A列データ
　　B列作業列
　　　　　A　　　　　　　B　　　　　　　　　　　C
1　=SUM(A2:A22)　=SUM(B2:B22) 　IF(A1=A2,"","少数点あり")
2 以下データ 　=ROUNDDOWN(A2,0)
　　　　　　　　　　以下関数コピー

小数点のデータが入力されるとＢ列で小数点を切り捨てた数がでていますので
比較をしたときに差が出るのを利用しています

もう少しスマートな方法があると思いますがとりあえずの回答です

お礼

ありがとうございます！
ところがフォーム内の合計が整数になるように数字を入れたら、
誤差が生じませんでした。。。
こまかいつっこみで申し訳ありません。

もし良い案あればまた宜しくお願いいたします！