Excel関数で数字の丸め方

質問

Excelの関数で、数字の丸め方を教えて下さい。

A1にある”74,256,444”（毎回違います）という数字を、20回で割って、2回目～20回目を均等の数字・1回目はA1-（2回目～19回目の和)で導き出したいです。以下、手計算なのですが、これを関数を使って実現できますか？ROUNDUP関数だと、切上げの単位を”-4”（千の位）で行うと、3,710,000になってしまい、(2)が実現できません。

(1)74,256,444/20=3,712,822
(2)2回目～20回目の1万単位は、0か5にしたいので、3,700,000になります。
(3)74,256,444-70,300,000（※3,700,000*19回)=3,956,444（１回目）

ROUNDUP関数を使うと、

回答No.8

MarcoRossiItaly

補足のみ。ベストアンサーは辞退します。

例えば、下の INT の数式でも合っています。しかし 2、5 という 10 の素因数の他に 10^4 という数を何回も掛けたり割ったりするのは何だかムダな感じがするというか、美しくないので、「丸める単位の幅を掛け算・割り算により調節すると同時に、小数点以下の丸める桁数も調節している」だけのことです。INT だと、小数点以下は常に 0 桁ですね？

=int(a1/(5*10^4))*(5*10^4)

=rounddown(a1/(5*10^5),1)*(5*10^5)　　　→規則性 10^(-1) x　(5 x 10^5) = 50000
=rounddown(a1*(2*10^5),-10)/(2*10^5)　　→規則性 10^10　 /　(2 x 10^5) = 50000
=rounddown(a1/(5*10^4),0)*(5*10^4)　　　→規則性 10^0　　x　(5 x 10^4) = 50000
=rounddown(a1*(2*10^4),-9)/(2*10^4)　　 →規則性 10^9　　/　(2 x 10^4) = 50000
=rounddown(a1/(5*10^3),-1)*(5*10^3)　　 →規則性 10^1　　x　(5 x 10^3) = 50000
=rounddown(a1*(2*10^3),-8)/(2*10^3)　　 →規則性 10^8　　/　(2 x 10^3) = 50000
=rounddown(a1/500,-2)*500　　　　　　　 →規則性 10^2　　x 500　　　　 = 50000
=rounddown(a1*200,-7)/200　　　　　　　 →規則性 10^7　　/ 200　　　　 = 50000
=rounddown(a1/50,-3)*50　　　　　　　　 →規則性 10^3　　x　50　　　　 = 50000
=rounddown(a1*20,-6)/20　　　　　　　　 →規則性 10^6　　/　20　　　　 = 50000
=rounddown(a1/5,-4)*5　　　　　　　　　 →規則性 10^4　　x　 5　　　　 = 50000
=rounddown(a1*2,-5)/2　　　　　　　　　 →規則性 10^5　　/　 2　　　　 = 50000

素朴な算数パズルです。どの数式でも同じ答えになることを確かめてください。

回答No.7

kagakusuki

回答No.6です。

　失礼しました。
　回答No.6に添付した画像の例では、D列の「3～20回目(切上げ)」を求める関数が、誤ってG列の「3～20回目(切捨て)」を求める関数と同じ関数となっておりました。
　正しくは、以下の画像の通りです。
　尚、回答文内で記述した関数に関しては要訂正箇所は御座いません。

回答No.6

kagakusuki

確認したいのですが、

>2回目～20回目の1万単位は、0か5にしたい

という条件で2回目～20回目の値に対して切り上げを行った場合、元の数値が「1,000,000の倍数に1を加えた値」から「『元の数を1,000,000で割って小数点以下を切り上げた値に、950000を掛け合せた値』から元の値を差し引くと1となる様な値」までの範囲内（例えば、1～949,999、,1000,001～1,899,999、2,000,001～2,849,999、3,000,001～3,799,999、4,000,001～4,749,999、5,000,001～5,699,999、6,000,001～6,649,999、・・・18,000,001～18,049,999）になる場合において、切り上げた値を19倍した値が、元の数値よりも大きくなってしまいますので、

A1-（2回目～19回目の和)

という計算の結果で求める1回目の値がマイナスの値になってしまいますが、1回目の値はマイナスの値になっても構わないのでしょうか？
　もし、１回目がマイナスとなっても構わないのでしたら、１回目～20回目の値を求める関数は次の様なものとなります。
　今仮に、元の数値がA2セルに入力されていて、1回目の値をB2セルに、２回目の値をC2セルに、3回目～20回目の値をD2～U2セルに表示させるものとします。
　その場合、C２セル(２回目の値)には次の様な関数を入力して下さい。

=IF(ISNUMBER($A2),CEILING($A2/20,50000),"")

　そして、B2セル(1回目の値)には次の様な関数を入力して下さい。

=IF(ISNUMBER($C2),$A2-$C2*19,"")

　又、D2～U2セル(3回目～20回目の値)には次の様な関数を入力して下さい。

=IF(ISNUMBER($C2),$C2,"")

　因みに、

>2回目～20回目の1万単位は、0か5にしたい

という条件で2回目～20回目の値に対して切り上げではなく、切り捨てを行った場合には、上記の様な１回目がマイナスとなる問題は発生しません。
　ですからもし、２回目以降の値を丸める際に切り捨てる様にする場合には、C２セル(２回目の値)には次の様な関数を入力して下さい。（１回目と3回目～20回目の関数に関しては、切り上げる場合と同じ関数のままで構いません）

=IF(ISNUMBER($A2),FLOOR($A2/20,50000),"")

回答No.5

keithin

＞A1/20/を5で割っているのはなぜなのでしょうか？

別にムズカシイ話じゃなく、５刻みに計算させたいという事なので「５ずつにする」ためです。５で割ってROUNDすれば、５まとまりになるのはなんとなく判るんじゃないでしょうか。

割り算したあと数式の後ろの＊５で５倍してるので、それで数字の辻褄は合わせてあります。

回答No.4

bunjii

>(1)74,256,444/20=3,712,822
一般的には分割払いのとき初回に端数込として残りを均等払いにしますので端数切捨てにします。
従って、初回は以下の式で良いと思います。

=A1-ROUNDDOWN(A1/20,-5)*19
この時、10万単位で丸めていますので(2)の要望は満たしていません。

>(2)2回目～20回目の1万単位は、0か5にしたいので、3,700,000になります。
5万単位で端数を丸めるには2つの方法があります。

万単位を5で割り丸めた結果を5倍する方法
=ROUNDDOWN(対象数値/5,-4)*5

10万単位を2倍して丸めた結果を2で割る方法
=ROUNDDOWN(対象数値*2,-5)/2

何方でも計算結果は同じです。
あなたの応用力で目的に合うよう修正してください。

回答No.3

keithin

言葉のご説明では切り上げという事ですが、事例のご説明は切り捨てになってます。どっちをしたいのですか。

（２）の説明の方が間違いで、切り上げで3,750,000としたいのでしたらROUND系の関数では
Ａ２：
=ROUNDUP(A1/20/5,-4)*5
とかで出来ます。

初回（残高）は
=A1-A2*19
で十分ですね。

まぁ切り捨てでもやり方は一緒なので、適切に応用してみて下さい。

お礼

みなさまありがとうございます。
その中でも、keithinさんの関数が自分の使い方で一番合いそうです。
私は、単純なROUNDUP、DOWNの使い方しかしていなかったのですね。それは前提の計算が苦手だからなのだと思いました。

=ROUNDUP(A1/20/5,-4)*5
この数式で、A1/20/を5で割っているのはなぜなのでしょうか？うまく動作するのに頭が悪いので理解できません。教えて下さい。

回答No.2

mshr1962

FLOOR関数を使えばいいのでは？

2回目以降=FLOOR(A1/20,50000)
1回目=A1-FLOOR(A1/20,50000)*19

回答No.1

shintaro-2

A1に数値があるとして、

=int(a1/10000)*10000/20 が２－20回目の個々の支払い分です