LoginSignup
0

More than 3 years have passed since last update.

Excel Excelのワークシート関数 RounDownは微小値加算をした方が早く計算する

Last updated at Posted at 2019-08-18

消費税の計算で偶然発見しました

検証用のコードはまずClassModuleであるGetTickCntClassをいれてください

https://qiita.com/Q11Q/items/13492275186ee9c95b8d
ここで説明しているClassModuleを入れます。
これは微小な時間を計るためのものであり、64/32Bit共用です。

3つのコードを動かして時間を計ります。

税込価格から税抜き価格を求めるための関数をA2に入れます。
RndでA1に0円~999,999円の数を作り、A1に入れます。
ここでExcelは自動的に計算するので、これを1000回やった時間を計ります。

コードは3パターン

微小値について

微小値を簡単に「計算する数の中で最も小さい数の一桁小さい数」と定義すると
消費税は10%とか5%ですので小数点2位です。
本当は3位でもよいのですが、小心者なので、それよりさらに1つ小さい、4位0.0001とします。
Aは微小値を加算してINTで切ります。

パターンの内容

今回は関係がありませんが、実務を想定し、いずれも符号を考慮します。
WorkSheetFunctionのためSIGNという名前になります。VBA / VBSだとSgnです
Aは微小値を加算して、Intで切ります。
Bは微小値を加算してRondDownで整数にします。(小数点以下を切り捨てます)
これはとても無駄な作業に思えます。一番遅い気がしますね。
CはRowndDownそのままです。
これで3回程度やってみてください。

Sub CountTimeCalc_A()
'微小値加算
Dim IntaxPrice As Currency
Dim exTaxPrice As Currency
Dim iCnt As Long
Dim gtTickcntClass As GetTickCountClass: Set gtTickcntClass = New GetTickCountClass 'GetTickCountClassクラスモジュールの宣言と初期化
Dim Dt
Application.ScreenUpdating = True

Dt = gtTickcntClass.GetValueOfTickCnt
Range("A2").Formula = "=IF(TODAY()>=DATEVALUE(""2019/10/1"")*1,INT(ABS(A1)/1.1+0.0001)*SIGN(A1),INT(ABS(A1)/1.08+0.0001)*SIGN(A1))"
For iCnt = 1 To 1000
IntaxPrice = Int(Rnd * 1000000)
Range("A1").Value = IntaxPrice
'exTaxPrice = Int(IntaxPrice / 1.1 + 0.0001)
Next
Debug.Print gtTickcntClass.GetValueOfTickCnt - Dt
Application.ScreenUpdating = True

End Sub

Sub CountTimeCalc_B()
'微小値加算後 RoundDownで切り捨て
Dim IntaxPrice As Currency
Dim exTaxPrice As Currency
Dim iCnt As Long
Dim gtTickcntClass As GetTickCountClass: Set gtTickcntClass = New GetTickCountClass 'GetTickCountClassクラスモジュールの宣言と初期化
Dim Dt
Application.ScreenUpdating = True

Dt = gtTickcntClass.GetValueOfTickCnt
Range("A2").Formula = "=IF(TODAY()>=DATEVALUE(""2019/10/1"")*1,RoundDown(ABS(A2)/1.1+0.0001,0)*SIGN(A2),Rounddown(ABS(A2)/1.08+0.0001,0)*SIGN(A2))"
For iCnt = 1 To 1000
IntaxPrice = Int(Rnd * 1000000)
Range("A1").Value = IntaxPrice
'exTaxPrice = Int(IntaxPrice / 1.1 + 0.0001)
Next
Debug.Print gtTickcntClass.GetValueOfTickCnt - Dt
Application.ScreenUpdating = True

End Sub

Sub CountTimeCalc_C()
'微小値加算をしないで、そのままRoundDown
Dim IntaxPrice As Currency
Dim exTaxPrice As Currency
Dim iCnt As Long
Dim gtTickcntClass As GetTickCountClass: Set gtTickcntClass = New GetTickCountClass 'GetTickCountClassクラスモジュールの宣言と初期化
Dim Dt
Application.ScreenUpdating = True

Dt = gtTickcntClass.GetValueOfTickCnt
Range("A2").Formula = "=IF(TODAY()>=DATEVALUE(""2019/10/1"")*1,RowndDown(ABS(A2)/1.1,0)*SIGN(A2),RoundDown(ABS(A2)/1.08,0)*SIGN(A2))"
For iCnt = 1 To 1000
IntaxPrice = Int(Rnd * 1000000)
Range("A1").Value = IntaxPrice
'exTaxPrice = Int(IntaxPrice / 1.1 + 0.0001)
Next
Debug.Print gtTickcntClass.GetValueOfTickCnt - Dt
Application.ScreenUpdating = True

End Sub

結果:一番早いのはINT、その次が微小値加算後RowndDown

1位は当然として、微小値加算後の方が単純なRowndDownより早いです。
これはランダムなので変動しますが、何回かやると微小値加算の方が早いです。
なお最速がINTなのはPCが生まれてからほぼ最初からある関数であり、どの言語でも存在する関数なので、ここで負けるわけにいかないからです。これを使えば四捨五入もできます。
それではなぜ関数があるのか。
たとえば小数点3以下を切り捨てて、小数点2位までにする場合
いったん100をかけたうえでINTを行い、100で割ります。
つまりこの場合有効桁数全部を使えません。
最大の数は有効桁数より2桁少ない数になります。
これがINTの弱点です。
しかし整数位であれば桁を動かすことがなく実行できるため、有効数字いっぱいまで使えます。この場合はRound関数より強いです。

微小値が求められる場合は微小値を加算した方がよい

ここからExcelの端数処理の関数、RonwdDownは微小値を加算した方がより早く処理できることになります。
今回は未確認ですがRowndUpも同様でしょう。
もちろん、これは微小値が得られる(求められる)場合です。
微小値が分からない場合は使えません。
部品や資材のようなものでは単価が円未満の場合があり、そうしたケースが考えられます。しかしながら、一般的な税込価格は円単位ですので、税抜き価格を求める場合の税率であれば税率が一番小さい数になりますので、微小値が求められます。
またより速いのはINTを使う場合です。
切り捨てはINTですが、切り上げもINTでできます。
整数位の場合微小値を加算してさらに0.9を加算するとできます。またこの場合も微小値を加算した方が正確です。

原因の推測

ExcelはRowndDown内の数式を計算して値を出したうえで、桁数を有効位いっぱいをチェックしたうえで、指定桁数で切り捨てをしているのに対し、微小値加算の場合は、微小値までしかチェックせずに指定桁数でRowndDownをかけるため、わずかに早いと考えられます。なぜなら微小値を加算したあとの桁は考慮しなくてよいからです。

補足

なお、このマクロは2019年10月1日以降は全体的に早くなります。これはExcelのシステムというより1.08で割り切れる数より1.1で割り切れる数の方が多いためです。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0