Excelの精度関係リンク集

背景

一般のコンピュータによる計算結果と同様に，Excelによる計算結果ももちろん不正確である。

関連するリンクをまとめておく。

• Excel で浮動小数点演算の結果が正しくない場合がある（Microsoft）
• Excelの演算誤差（三重大学 奥村研究室）
• Excel使うな（Okumura's Blog）
• Excel使うな：2010版（Okumura's Blog）
• Excelが計算ミス（Okumura's Blog）
• Excelでの数値表現と計算精度（神奈川大学 経済学部）
• Excel:エクセルの計算精度（ガスコン研究所）
• “達人”芳坂和行氏に学ぶ、エクセル「演算誤差」対策講座（日経 PC online）
• Numeric precision in Microsoft Excel（Wikipedia英語版）

Fortran DLLをVBAから呼び出す場合の関数名はAliasを利用すべし

背景

Fortranの関数名は，大文字/小文字の区別はされない。

VBAからFortran DLL内の関数（例: testfunction）を呼び出す場合に，VBA側で大文字/小文字を区別しておきたい場合（例: TestFunction）は，

Declare PtrSafe Sub TestFunction Lib "パス～" Alias "testfunction" (引数・・・)

のように宣言すべし。

検証環境

• OS: Windows 10 Pro（64bit）
• アプリケーション: Excel 2013（64bit）
• CPU: Core i7 2.4GHz
• RAM: 16GB
• 仮想メモリ: OFF

Excel 2013のLOG10関数の精度

背景

ある2個の数値の一致桁数を評価するときに，両者の相対誤差の常用対数をとる。このため，Excel上ではLOG10関数を利用している。しかし，一致桁数云々を議論する前に，そもそも，この関数の精度が重要である。

一方，CASIOの高精度計算サイト（50桁での計算が可能）にも，LOG関数という関数があり，常用対数を計算できる。

そこで，メタ議論になってしまうが，一致桁数評価ツールとしてのLOG10関数，LOG関数について，両者の結果を比較し，一致桁数を評価した。

検証環境

• OS: Windows 10 Pro（64bit）
• アプリケーション: Excel 2013（64bit）
• CPU: Core i7 2.4GHz
• RAM: 16GB
• 仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. 相対誤差に基づいて一致桁数を評価するとき，相対誤差は概ね10^-16から10^+2までの範囲に収まる。そこで，この範囲の数値を等比的に分割して数列を作成する。10倍区間を100分割して得た等比数列の値をxとする。
2. ExcelのLOG10関数を利用して，xの常用対数を計算する。
3. CASIOの高精度計算サイトで，「桁数50桁」，「精度保証あり」でxの常用対数を計算する。
4. ただし，この結果を適切にExcelに貼り付けるには有効桁数を15桁にしておく必要があるので，高精度計算サイト側でプログラムを自作し，最終結果の有効桁数を15桁にする。
5. 手順2.の結果と手順4.の結果をExcel上で比較し，一致桁数を評価する。
6. 同様にして，相対誤差は概ね10^-1から10^+1までの範囲で，10倍区間を999分割して一致桁数を評価する。なお，1000分割ではなく，999分割にしたのは，CASIOの高精度計算サイトの制約による。

結果

結果は下図のとおり。

• 10^-16≦x≦10^+2の範囲で，一致桁数はほぼ14桁以上であった。
• ただし，x=1の近辺で一致桁数が低下するようなので，上記手順6のとおり，より細かくみてみると，一致桁数は13.5桁まで低下している。
• さらに細かくみれば，一致桁数がさらに低下する可能性もあるが，重要なのは x≦10^-1の領域なので，大きな問題にはならないであろう。

Excel 2013でコーシー分布のパーセント点を計算したときの精度

背景

コーシー分布のパーセント点を求めるには，分布関数の逆関数が必要である。この関数はExcelには実装されていないが，TAN関数を利用すれば計算できる。

一方，CASIOの高精度計算サイト（50桁での計算が可能）には，cauchyicdlower関数という関数があり，コーシー分布のパーセント点を直接計算できる。

そこで，両者の結果を比較し，一致桁数を評価した。

検証環境

• OS: Windows 10 Pro（64bit）
• アプリケーション: Excel 2013（64bit）
• CPU: Core i7 2.4GHz
• RAM: 16GB
• 仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. ExcelのTAN関数を利用してコーシー分布のパーセント点を計算する。
2. CASIOの高精度計算サイトで，「桁数50桁」，「精度保証あり」でコーシー分布のパーセント点（cauchyicdlower関数）を計算する。
3. ただし，この結果を適切にExcelに貼り付けるには有効桁数を15桁にしておく必要があるので，高精度計算サイト側でプログラムを自作し，最終結果の有効桁数を15桁にする。
4. 1.の結果と3.の結果をExcel上で比較し，一致桁数を評価する。

結果

結果は下図のとおり。

• 下側確率で1%以上99%以下の領域では，一致桁数はほぼ14桁以上であった。
• それ以外の領域では一致桁数は低下した。

Excel 2013で標準正規分布のパーセント点（NORM.S.INV関数）を計算したときの精度

背景

Excelの統計関数は，何かとタタかれることが多い。

標準正規分布のパーセント点を求めるには，分布関数の逆関数が必要である。この関数はExcelではNORM.S.INV関数として実装されている。

一方，CASIOの高精度計算サイト（50桁での計算が可能）には，normalicdlower関数という関数があり，標準正規分布のパーセント点を直接計算できる。

そこで，両者の結果を比較し，一致桁数を評価した。

検証環境

• OS: Windows 10 Pro（64bit）
• アプリケーション: Excel 2013（64bit）
• CPU: Core i7 2.4GHz
• RAM: 16GB
• 仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. ExcelのNORM.S.INV関数を利用して標準正規分布のパーセント点を計算する。
2. CASIOの高精度計算サイトで，「桁数50桁」，「精度保証あり」で標準正規分布のパーセント点（normalicdlower関数）を計算する。
3. ただし，この結果を適切にExcelに貼り付けるには有効桁数を15桁にしておく必要があるので，高精度計算サイト側でプログラムを自作し，最終結果の有効桁数を15桁にする。
4. 1.の結果と3.の結果をExcel上で比較し，一致桁数を評価する。
5. ついでに， NORM.S.INV関数と，その互換性関数であるNORMSINV関数との結果も比較する。

結果

結果は下図のとおり。

• 下側確率で0%以上99.99%以下の領域では，一致桁数はほぼ14桁以上であった。
• それを超えると一致桁数は減少した。
• なお，NORM.S.INV関数と，その互換性関数であるNORMSINV関数は，すべての領域で一致桁数は15桁であった。Microsoftによれば，新しい関数のほうがより精度が高いとのことであるが，NORM.S.INV関数に関しては精度の向上はないようである。

Excel 2013のApplication.Waitの分解能

背景

Application.Waitは1秒未満も指定できる。

そこで，どこまで短い時間を指定できるのか，分解能を調べてみた。

検証環境

• OS: Windows 10 Pro（64bit）
• アプリケーション: Excel 2013（64bit）
• CPU: Core i7 2.4GHz
• RAM: 16GB
• 仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. For~NextループでApplication.Wait○○を指定回数だけ回した。
2. この処理にかかる時間をtimeGetTime関数で計測した。

結果

分解能は0.01秒（=10ms）のようである。

Excel 2013のCalculateFullRebuildメソッドは，すべての数式を再入力することとは異なる

背景

https://msdn.microsoft.com/ja-jp/library/office/ff822609.aspx?f=255&MSPPError=-2147217396 によれば，

 CalculateFullRebuild メソッドは、すべての数式を再入力するのと似ています。

とのことであるが，あくまでも似ているだけであって，実際には非なるものである。 セルを選択して 'F2' キーを押したときと同じ効果は得られない。

検証環境

• OS: Windows 10 Pro（64bit）
• アプリケーション: Excel 2013（64bit）
• CPU: Core i7 2.4GHz
• RAM: 16GB
• 仮想メモリ: OFF

検証方法

以下の手順で検証した。

1. A1セルに数値として， 0 を入力する。
2. A2セルに数式として， =A1+2.729 を入力する。 
3. A2セルをA87セルまでドラッグコピーする。その結果，A87セルの値は234.694となっている。
4. このA87セルをコピーして，B87セル，C87セルに値のみ貼り付ける。 いずれも，数式バーで確認すると，セルの値は234.694となっている。
5. D87セルに数式として，=B87=C87 を入力する。B87セル，C87セルには同じ数値が入っているので，D87セルには TRUE が表示される。
6. この状態で，Application.CalculateFullRebuildメソッドを実行し，D87セルの値を確認する。
7. C87セルを選択して 'F2' キーを押し，D87セルの値を確認する。
8. 手順6.の結果と 手順7. の結果を比較する。

結果

• 手順6.の結果はTRUE，手順7.の結果はFALSEとなり，両者の計算結果は一致しない。
• 手順6.でApplication.CalculateFullRebuildメソッドを実行する代わりに，Ctrl + Shift + Alt + F9 キーを押しても，やはり手順7.の結果とは一致しない。
• なお，手順4.を実行した直後のB87セル，C87セルの値には，16桁目の「隠し桁」（? このような表現が正しいかどうか不明であるが・・・）のようなものが含まれており，そのセルを選択した状態で 'F2' キーを押すか，そのセルをダブルクリックして入力可能状態にすると，有効数字が15桁に丸められるような印象を受ける。この現象にはときどき遭遇する。