EXCELの関数について（IF、ROUND）

質問

エクセルの数値の丸め方について

エクセル初心者です。今までの投稿で近い投稿が無かったので、質問させて頂きます。

例として、ある数値が小数第1位まであるとします。
(1)その値が300未満のとき
小数第1位の値が4以下のとき切り捨て、5以上のとき切り上げる。

(2)その値が300以上1000未満のとき
小数第1位の値が4以下のとき切り捨て、5以上のとき切り上げる。
整数第1位の数値が2以下のとき切り捨てる。3以上6以下のとき5とする。7以上のとき切り上げる。

(3)その値が1000以上10000未満のとき
小数第1位の値が4以下のとき切り捨て、5以上のとき切り上げる。
整数第1位の数値が4以下のとき切り捨てる。5以上のとき切り上げる。

このような関数、もしくはVBAの設定を詳細に教えて頂けないでしょうか？よろしくお願いします。

回答No.10

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

#1です
=CHOOSE(MATCH(A3,{0,300,1000,10000,9999999}),
ROUND(A3,0),
ROUNDDOWN(A3,-1)+LOOKUP(A3-ROUNDDOWN(A3,-1),{0,2.5,6.5},{0,5,10}),
ROUND(A3+0.5,-1),ROUND(A3+5.5,-2))

CHOOSE(MATCH(A3,{0,300,1000,10000,9999999})
で条件分岐しています。最大値を9999999としました

300まで
ROUND(A3,0)
単なる四捨五入

～1000まで
ROUNDDOWN(A3,-1)+
LOOKUP(A3-ROUNDDOWN(A3,-1),{0,2.5,6.5},{0,5,10})
整数第一位で切り捨てたもの＋
下2桁を条件分岐して 0(2.5以下)か5(～6.5)か10(6.6～)を足す

～10000まで
ROUND(A3+0.5,-1)
0.5を足して整数第一位で四捨五入

～9999999
ROUND(A3+5.5,-2))
5.5を足して整数第ニ位で四捨五入

299.4 299
299.5 300
299.9 300
300 300
300.5 300
302.4 300
302.5 305
996.4 995
996.5 1000
999.9 1000
1000 1000
1002.5 1000
1004.4 1000
1004.5 1010
1014.4 1010
10044.4 10000
10044.5 10100
401.8 400
531.5 530 ←は530で正解だと思いますが？

お礼

ご連絡大変遅くなりました。
ご回答ありがとうございます。
まさに求めていた数式で回答です。
まずは、CHOOSEとMATCH関数の複合で条件分岐するのですね。
解りやすい解説で大変勉強にもなりました。
この度は、丸投げ質問で他回答頂いた皆さんにも
ご迷惑をおかけしました。
ありがとうございました。

回答No.12

nattocurry

どの部分でエラーになっているのかを解析するのにも、長い式を書くより、分けて書いたほうが、エラー部分が判りやすいです。

その長い式を、分けて書いて、ご自分で解明する努力をしましょう。

お礼

自分で数式を分解して、時間掛けて解析した上での質問です。
どうしても解明できなかった為、質問しました。
ありがとうございました。

回答No.11

nattocurry

最初から1つの式を作ろうとせずに、いくつかのセルに計算式を書いて、段階的に処理してから、最終的に1つの式を作るようにしましょう。
と言っても、ぴんときませんよね。

たとえば、

A1：入力値
A2：数式：A1に対して、「小数第1位の値が4以下のとき切り捨て、5以上のとき切り上げる。」
A3：数式：A1に対して、「小数第1位の値が4以下のとき切り捨て、5以上のとき切り上げる。」
A4：数式：A3に対して、「整数第1位の数値が2以下のとき切り捨てる。3以上6以下のとき5とする。7以上のとき切り上げる。」
A5：数式：A1に対して、「小数第1位の値が4以下のとき切り捨て、5以上のとき切り上げる。
A6：数式：A5に対して、「整数第1位の数値が4以下のとき切り捨てる。5以上のとき切り上げる。」
A7：数式：A1が300未満のときはA2、A1が300以上1000未満のときはA4、A1が1000以上10000未満のときはA6

というようにすれば、A7が求めたい値になります。

A2とA3とA5は同じ式になるので、まとめてしまっても良いでしょうね。

A1：入力値
A2：数式：A1に対して、「小数第1位の値が4以下のとき切り捨て、5以上のとき切り上げる。」
A3：数式：A2に対して、「整数第1位の数値が2以下のとき切り捨てる。3以上6以下のとき5とする。7以上のとき切り上げる。」
A4：数式：A2に対して、「整数第1位の数値が4以下のとき切り捨てる。5以上のとき切り上げる。」
A5：数式：A1が300未満のときはA2、A1が300以上1000未満のときはA3、A1が1000以上10000未満のときはA4

まともな計算結果を得られるようになったら、

A3の式の中に書かれてある「A2」を、A2に書かれてある数式に置き換えて、

A4の式の中に書かれてある「A2」を、A2に書かれてある数式に置き換えて、

A5の式の中に書かれてある「A2」を、A2に書かれてある数式に置き換えて、
A5の式の中に書かれてある「A3」を、A3に書かれてある数式に置き換えて、
A5の式の中に書かれてある「A4」を、A4に書かれてある数式に置き換えて、

とやれば、最終的に、A5は、A1だけを使った数式になります。

上記の場合のA2、A3、A4と言った作業セル(作業列？作業行？)を残しておいても良いのであれば、無理やり1つのセルに長ったらしい数式を書くよりも、上記の「まともな計算結果を得られるようになったら」の時点での状態にしておいたほうが、ずっと解りやすい(＝メンテしやすい)です。
見栄えが気になるのであれば、その列か行を非表示にすれば良いだけです。

お礼

確かにそうですよね。
段階を踏んで、分けて計算するようにトライしてみます。
ご意見ありがとうございました。
しかし、次の数式がなぜエラーになるのかが、いまだ不明です。
本件とは、話がヅレますが、
どなたか解明していただけるとありがたいです。
=IF(K28<3000,ROUND(K28,-1),IF(AND(K28<10000,MOD(ROUND(K28,-1)/10,10)=0),ROUND(K28,-2),IF(AND(K28<10000,MOD(ROUND(K28,-1)/10,10)<=2),ROUNDDOWN(K28,-2),IF(AND(K28<10000,MOD(ROUND(K28,-1)/10,10)<=6),ROUNDDOWN(K28,-2)+50,IF(AND(K28<10000,MOD(ROUND(K28,-1)/10,10)>=7),ROUNDUP(K28,-2),IF(AND(K28<100000,MOD(ROUND(K28,-1)/10,10)<=4),ROUNDDOWN(K28,-2),IF(AND(K28<100000,MOD(ROUND(K28,-1)/10,10)>=5),ROUNDUP(K28,-2),ROUND(INT(K28+5),-2))))))))

回答No.9

tom04

No.7です！
前回で失礼しようと思っていたのですが・・・
No.7の数式では10000以上の場合はちゃんと表示されなかったので
また、顔を出してしまいました。

=IF(A1<300,ROUND(A1,0),IF(A1<1000,ROUNDDOWN(A1,-1)+INDEX($F$3:$F$6,MATCH(MOD(ROUND(A1,0),10),$E$3:$E$6,-1)),IF(A1<10000,ROUND(ROUND(A1,0),-1),IF(MOD(A1,100)<=44.4,ROUNDDOWN(A1,-2),ROUNDUP(A1,-2)))))
に変更してみてください。

尚、nkgw_4a_tさんが考えられた数式を確認したかったのですが、
目も痛くなり、かなり疲れました。
（歳には勝てませんねぇ～・・・(ーー;)）

また・・・時間があればゆっくり数式を確認させてもらいたいと思います。
それではおやすみなさい。m(__)m

回答No.8

imogasi

大きく分けて条件が４つあり
小数点第1位の条件
整数第１位の条件
条件が満たされるときが値（値ならVLOOKUPのTRUE型も考えるのだが）ではなく、式（切捨てなど・処理）である
の４つあり複雑で、有るので、VBA（でユーザー関数を作る）でやれば素直に区分けしてコードを記述できる。CASE分というのが使える。
http://oshiete1.goo.ne.jp/qa1969641.html
ーー
思いつきだが、元の数を１０倍し、結果を１/１０するのはどうだろう。
また桁の数字は、文字列としてRight関数で捉えられると思う。
＞4以下のとき切り捨て、5以上のとき切り上げる、のパターンが多いので四捨五入で対応できそう。
質問者がVBAが判るかどうか判らない。
関数の信奉者が多い。
丸投げ的質問なので、具体的コードはどうしようと思ったが
標準モジュールにユーザー関数Shoriを作る・
Function shori(a)
a = a * 10
Select Case a
Case Is < 3000 '300未満
a = a + 5
a = a / 10
shori = Int(a)
Exit Function
Case Is < 10000 '300以上1000未満
a = a + 5
a = a / 10
a = Int(a)
a = a * 10

Select Case Right(a, 2)

Case Is <= "20"
MsgBox Right(a, 2)
Mid(a, Len(a) - 2 + 1, 1) = "0"
Case Is < 70
Mid(a, Len(a) - 2 + 1, 1) = "5"
Case Else
Mid(a, Len(a) - 2 + 1, 1) = "7"
End Select
a = Int(a / 10)
shori = a
Exit Function
Case Is >= 10000 '1000以上
a = a + 5
a = a / 10
a = Int(a)
a = a * 10
Select Case Right(a, 2)
Case Is <= 40
a = Int(a / 100) * 100
Case Else
a = a + 50
End Select
a = Int(a / 10)
shori = a
Exit Function
Case Else
'条件は質問では不明？ーー（注1)
End Select

End Function
結果
A列　　　B列（質問者の答え　　D列式　=shori(A1)を式複写

299.9 300 300
123.2 123 123
123.3 123 123
123.4 123 123
123.5 124 124
123.6 124 124
123.7 124 124
123.8 124 124
123.9 124 124
1234.4 1230 1230
1234.5 1240 1240
1234.6 1240 1240
1234.7 1240 1240
1234.8 1240 1240
1234.9 1240 1240
531.5 531 530
532.6 535 535
533.7 535 535
535.8 535 535
535.9 535

一箇所違うがトレースに根尽きた。
＃３のお礼の部分の条件は省略した。(注１）
あとから追加されたので。
それにしても複雑なルールだのう。

お礼

ご回答ありがとうございます。
一度VBAでもトライしてみます。ありがとうございました。

回答No.7

tom04

何度も何度も・・・ごめんなさい！

No.5・6です！確認した結果では
先ほどの数式では　仮にA1=549.8　のような場合は　
表の　0～2の行を参照してしまいますので、540　となってしまいます。
↓の画像の行も増やしてもらって・・・
=IF(A1<300,ROUND(A1,0),IF(A1<1000,ROUNDDOWN(A1,-1)+INDEX($F$3:$F$6,MATCH(MOD(ROUND(A1,0),10),$E$3:$E$6,-1)),IF(A1<10000,IF(MOD(A1,100)<=44.4,ROUNDDOWN(A1,-2)),ROUNDUP(A1,-2))))
に訂正してみてください。
詳しく確認していませんので、他に希望通りに表示されない数値があるかもしれません。

尚、A1に入る数値は小数点第1位までと限定させてもらっています。
ほんとぉ～にっ！何度もごめんなさい。m(__)m

回答No.6

tom04

No.5です！
たびたびごめんなさい！

一番大切な数式を回答していませんでした。

A1セルの値をB1セルに表示させるとします。
これも結構長い数式になりますが・・・
B1=IF(A1<300,ROUND(A1,0),IF(A1<1000,ROUNDDOWN(A1,-1)+INDEX($F$3:$F$5,MATCH(MOD(ROUND(A1,0),10),$E$3:$E$5,-1)),IF(A1<10000,ROUND(ROUND(A1,0),-1),IF(MOD(A1,100)<=44.4,ROUNDDOWN(A1,-2),ROUNDUP(A1,-2)))))
です。
今、数式をコピー＆ペーストしてみましたが、
この式もほんと！長いですねぇ～！m(__)m

補足

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

No.3の数式を後で自分なりに解析し、大体理解できました。
ただ、No.6も同様ですが、次の数値が入ると希望する数値が得られません。恐らくMOD関数の割り切り数がOとなる為、だと思うのですが。。。
(例)
399.5-399.9間が390となります。
同様に499.5-499.9が490、599.5-599.9～999.5-999.9

長くなりますが、No.3を次の数式にすれば良いのですか？
B1=IF(A1<300,ROUND(A1,0),IF(AND(A1<1000,MOD(ROUND(A1,0),10)=0),ROUND(A1,-1),IF(AND(A1<1000,MOD(ROUND(A1,0),10)<=2),ROUNDDOWN(A1,-1),IF(AND(A1<1000,MOD(ROUND(A1,0),10)<=6),ROUNDDOWN(A1,-1)+5,IF(AND(A1<1000,MOD(ROUND(A1,0),10)>=7),ROUND(INT(A1+0.5),-1))))))
なお、10000以上の条件を付け加えた場合は、
B1=IF(A1<300,ROUND(A1,0),IF(AND(A1<1000,MOD(ROUND(A1,0),10)=0),ROUND(A1,-1),IF(AND(A1<1000,MOD(ROUND(A1,0),10)<=2),ROUNDDOWN(A1,-1),IF(AND(A1<1000,MOD(ROUND(A1,0),10)<=6),ROUNDDOWN(A1,-1)+5,IF(AND(A1<1000,MOD(ROUND(A1,0),10)>=7),ROUNDUP(A1,-1),IF(AND(A1<10000,MOD(ROUND(A1,-1),10)<=4),ROUNDDOWN(A1,-2),IF(AND(A1<10000,MOD(ROUND(A1,-1),10)>=5),ROUNDUP(A1,-2),ROUND(INT(A1+0.5),-1))))))
で良いでしょうか？なぜか数式エラーが出ます？？
更にもし、桁数が変わると次の数式でOKでしょうか？
IF(A1<3000,ROUND(A1,-1),IF(AND(A1<10000,MOD(ROUND(A1,-1)/10,10)=0),ROUND(A1,-2),IF(AND(A1<10000,MOD(ROUND(A1,-1)/10,10)<=2),ROUNDDOWN(A1,-2),IF(AND(A1<10000,MOD(ROUND(A1,-1)/10,10)<=6),ROUNDDOWN(A1,-2)+50,IF(AND(A1<10000,MOD(ROUND(A1,-1)/10,10)>=7),ROUNDUP(A1,-2),IF(AND(A1<100000,MOD(ROUND(A1,-1)/10,10)<=4),ROUNDDOWN(A1,-2),IF(AND(A1<100000,MOD(ROUND(A1,-1)/10,10)>=5),ROUNDUP(A1,-2),ROUND(INT(A1+5),-2))))))))
ほんと、ごめんなさい。続けて、質問してしまって。。
よろしくお願いします。
以上を、VBAで行うと複雑になりますかね？？？

回答No.5

tom04

こんばんは！
No.3です。
昨日は失礼しました。
何とか希望に近い形の回答のようなので安心しました。

＞532.5であれば、533⇒535と2回計算をしないと無理なはずです。
とありますが、
IF関数で条件分けしていますので、2回計算とは少し違うと思います。
例えば昨日の数式の中の
IF(AND(A1<1000,MOD(ROUND(A1,0),10)<=2),ROUNDDOWN(A1,-1)
という部分は
A1の数値が1000より小さくて、小数点第1位を四捨五入した整数の1位の値が2以下の場合は、A1の整数1位を切り捨てます。
（仮にA1=532.2　であれば、530になります）
と言うような感じの数式を羅列しただけです。

ところで本題ですが、昨日の数式ではあまりに長すぎますし、10000以上の場合の回答もしなくてはならないと思いますので
新たに、新しく考えてみました。
300≦A1<1000　の場合の条件分けが面倒なので表にして、小数点第1位を四捨五入した後の
整数１位の値を参照して返す方法です。

↓の画像を参考にしていただければいいと思います。

尚、新たに10000以上の場合の条件がありますが、
これを丸めなどの関数を使うとこれだけでかなりの数式になると思いますので、
私の解釈として、仮に11244.4以下→11200　11244.5以上→11300　となるような解釈になっています。
（11244.5は0.5を四捨五入→11245となるので5を四捨五入→11250となり、11250の5を四捨五入→11300）
と言うような感じです。
もしこれが間違っていればごめんなさい。

どうも長々とごめんなさい。
参考になれば幸いです。m(__)m

回答No.4

mu2011

一例です。
=IF(A1>=10000,FLOOR(A1+55.5,100),IF(A1>=1000,FLOOR(A1+5.5,10),IF(A1>=300,IF(MOD(A1,10)>6,FLOOR(A1+3.5,5),FLOOR(A1+2.5,5)),FLOOR(A1+0.5,1))))

回答No.3

tom04

こんばんは！
回答が質問にあっているかどうか分かりませんが・・・

とりあえずIF関数でやってみました。
かなり長い数式になってしまいました。

A1セルをB1セルに表示させると仮定します。

B1セル=IF(A1<300,ROUND(A1,0),IF(AND(A1<1000,MOD(ROUND(A1,0),10)<=2),ROUNDDOWN(A1,-1),IF(AND(A1<1000,MOD(ROUND(A1,0),10)<=6),ROUNDDOWN(A1,-1)+5,IF(AND(A1<1000,MOD(ROUND(A1,0),10)>=7),ROUNDUP(A1,-1),ROUND(INT(A1+0.5),-1)))))

としてみてはどうでしょうか？
尚、10000以上の場合でも(3)の条件が当てはまってしまいます。

表などを使うともっと簡単な式になるかも知れませんが、
今はこの程度の回答しか思い浮かびませんでした。

以上、参考になれば幸いです。
的外れの回答なら読み流してくださいね。m(__)m

補足

ご回答ありがとうございます。
すごいです。まさしくこの回答です。
同じセル内で、2重丸めができるのですか？
例えば、
532.5であれば、533⇒535と2回計算をしないと無理なはずです。
926.4であれば、926⇒925。。。
この関数の説明を是非お聞きしたいです。

なお、10000以上の場合は、
小数第1位の値が4以下のとき切り捨て、5以上のとき切り上げる。
整数第1位の数値が4以下のとき切り捨てる。5以上のとき切り上げる。
整数第2位の数値が4以下のとき切り捨てる。5以上のとき切り上げる。
としたいです。
続けて質問して、ご迷惑と思いますが、よろしくお願いします。

回答No.2

nattocurry

401.8のときは、どうなって欲しいのでしょうか？

補足

401.8のときは、小数第一位が5以上ですから切り上がり、整数第一位が2になります。2以下ですので切り捨てて、400にしたいです。