Excelのエンジニアリング関数ファミリーは、関数リファレンスの中で最も簡単な領域のように見えます。DEC2BINは数値をバイナリ文字列に変換します。HEX2DECはそれを元に戻します。IMSUMは2つの複素数を加算します。それぞれ単なるフォーマット処理のように見えますが、そうではありません。これらの名前の背後には、多くの開発者がコンピュータ構成の授業以来触れていない10ビットの2の補数エンコーディング、完全に文字列内に存在する複素数フォーマット、そしてチェックする前にシフトすると64ビット整数を静かにオーバーフローさせるビット演算子が隠されています。Excelを正確に再現するスプレッドシートエンジンは、これらのどれも妥協することはできません。
関数は3つのグループに分かれており、各グループには異なる落とし穴があります。基数変換は負の数値と基数ごとのしきい値が重要です。複素数演算は文字列のパースとフォーマットが重要です。ビット演算はInt64の範囲内に収めることが重要です。この記事では、HotXLSが実装している各グループについて、実際に記述するワークシート呼び出しを交えて解説します。
基数変換と10ビットの2の補数
順方向の変換は誰もが期待する通りの動作です。DEC2BIN(9)は"1001"を返し、オプションの第2引数は結果を指定された幅に左パディングします。落とし穴は負の入力です。Excelはマイナス記号を書きません。値をターゲットの基数における10桁の2の補数文字列としてエンコードします。そのため、DEC2BIN(-5,10)は符号付きの何かではなく"1111111011"を返します。値が負になると、エンコーディングがすでに10桁に固定されるため、幅の指定引数は無視されます。
10桁は固定の予算であり、その予算によって基数ごとに表現可能な範囲が決定されます。バイナリでは、負の半分に反転する閾値は512であり、ラップモジュラスは1024です。したがって、バイナリ文字列は、ちょうど10文字の長さで値が少なくとも512である場合にのみ負として扱われます。同じ考え方が他の基数にも拡張されます。8進数は2^29の半分閾値と2^30のフルモジュラスを使用します。16進数は2^39と2^40を使用します。HotXLSのリーダーはまさにこのルールを適用します。桁を累積し、文字列幅が10文字で累積値が半分閾値以上である場合にのみ、フルモジュラスを減算して符号付きの値を復元します。9文字の文字列は、値がどれほど大きくても常に非負です。
エンコーダーはその鏡像です。非負の値は桁ごとに変換され、オプションで要求された幅までゼロパディングされます。値が基数の正の上限を超えた場合、または要求された幅が値を保持するのに狭すぎる場合は拒否されます。負の値は、まずフルモジュラスを加算して範囲内に収められます。これにより、その基数表現が常に10桁になる値に変換され、幅を満たすためにリーディングゼロを伴って出力されます。基数ごとに共通の範囲チェック（対称的な下限と上限）を行うことで、DEC2BIN、DEC2OCT、DEC2HEXの間でエッジケースの一貫性が保たれます。
次に、関数名に十進数を経由しない、HEX2BINやOCT2HEXなどのクロス基数変換が残ります。実装においては、すべての順序ペアに対して個別のルーチンを用意しているわけではありません。ソース基数を使用して入力文字列を符号付き十進数値にパースし、その十進数値をデスティネーションの基数にフォーマットします。十進数がピボット（中心軸）です。パースルーチン1つとフォーマットルーチン1つを組み合わせることで、すべての組み合わせをカバーします。両者が同じ10桁の符号付きの取り決めを共有しているため、負の値は符号を維持したまま変換を通過します。
複素数は文字列であるため、処理の核心はパース
Excelには複素数データ型はありません。複素数値は文字列"a+bi"であり、IMファミリーのすべての関数はこれらの文字列を受け取り、文字列を返します。COMPLEXは実部と虚部から文字列を構築します。IMSUM、IMSUB、IMPRODUCT、およびIMDIVは引数をパースし、数値部分の算術演算を行い、結果を再び文字列にフォーマットします。数値計算自体は大学レベルの代数です。難しいのは、テキストを2つの浮動小数点数に確実に変換することであり、そこで内部パーサーが真価を発揮します。
そのパーサーにおいて、間違えやすいディテールが2つあります。1つ目は、単体の虚数単位です。文字列"i"は1かけるiを意味し、ゼロでもエラーでもありません。そのため、接尾辞の前の係数が空であるか、単なるプラス記号である場合、パーサーはそれを値1として読み取る必要があり、単なるマイナス記号の場合は-1として読み取る必要があります。これをスキップすると、IMSUM("i","i")が2iにならなくなります。2つ目は、科学的記数法と実部と虚部を分ける符号との衝突です。パーサーはプラスまたはマイナスをスキャンしてそのセパレータを見つけますが、"1.5E-3"と書かれた文字列には指数に属するマイナスが含まれています。そのため、直前の文字がeまたはEである場合、スキャンはプラスまたはマイナスをセパレータとして扱うことを拒否します。このガードがないと、実部が指数の符号の場所で引き裂かれ、完全に有効な入力に対してパースが失敗します。
接尾辞自体は標準化されるのではなく保持されます。Excelはiとjの両方を受け入れ、HotXLSは入力がどちらを使用したかを記憶しているため、フォーマットされた結果には同じ文字が出力されます。フォーマット処理は標準的な省略規則を適用します。虚部が1の場合は単に接尾辞を出力し、マイナス1の場合は-iを出力し、虚部がゼロの場合はプレーンな実部に折りたたまれ、実部がゼロの場合は先頭の0+がドロップされます。
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Engineering');
// Negative input: a ten-bit two's complement, places argument ignored.
Sheet.Cells[1, 1].Value := Sheet.Calculate('=DEC2BIN(-5,10)'); // 1111111011
// Complex multiply on two "a+bi" strings.
Sheet.Cells[2, 1].Value := Sheet.Calculate('=IMPRODUCT("3+4i","1+2i")'); // -5+10i
finally
Book.Free;
end;
end;超越的な複素数関数（IMSQRT、IMEXP、IMLN、およびIMPOWERなど）は、直交座標では動作しません。パースされた値を極形式に変換し、絶対値と偏角に対して演算を適用し、再び戻します。平方根は偏角を半分にし、絶対値の平方根を取ります。累乗は偏角を乗算し、絶対値を累乗します。他の方法で実行すると、直交座標形式で各恒等式を再導出することになり、コード量が増え、分岐カット付近での数値的安定性が低下します。
ビット演算子と、事前にチェックすべきオーバーフロー
Excel 2013はBITAND、BITOR、BITXOR、BITLSHIFT、およびBITRSHIFTを追加しました。オペランドは制約されており、それぞれ非負の整数で2^48マイナス1以下でなければならず、小数や負の引数は数値エラーになります。この上限は、ダブル精度の正確に表現可能な範囲内に十分に収まりつつ、実用的なフラグセットをカバーするのに十分な大きさです。これは、Excelがすべての数値引数を浮動小数点値として渡すために重要です。
シフト関数には、実際に注意すべき順序ルールが1つあります。左シフトは入力よりもはるかに大きな値を生成する可能性があり、先にshlを実行してから結果を検査しようとすると、すでにInt64をオーバーフローしており、そのテストは無意味になります。チェックはシフトの前に実行しなければなりません。HotXLSは、オペランドをシフト量だけ右シフトした上限値と比較し、オペランドが適合する場合にのみ実際の左シフトを実行します。53ビットを超えるシフト量は即座に拒否され、負のシフト数は方向を反転させるため、負のカウントを持つBITLSHIFTは右シフトとして動作します。この原則は、この1つの関数をはるかに超えて一般化されます。オーバーフローを防ぐためのガードが存在する場合、それは結果ではなく、入力に対して実行されなければなりません。
// Bitwise calls evaluate the same way through Calculate.
Sheet.Cells[3, 1].Value := Sheet.Calculate('=BITAND(13,11)'); // 9
Sheet.Cells[4, 1].Value := Sheet.Calculate('=BITLSHIFT(5,2)'); // 20
Sheet.Cells[5, 1].Value := Sheet.Calculate('=BITRSHIFT(40,3)'); // 5新しい関数と_xlfn名前プレフィックス
ビット演算子やその他の多くの2007年以降の追加機能は、計算内容とは関係がなく、Excelがそれらをどのように保存するかというネーミングスキーマと相互作用します。元のバイナリワークシート形式は、各組み込み関数を固定テーブルの特定の数値スロットに割り当てていました。そのテーブルが固定された後に発明された関数にはスロットがありません。そのような関数をファイルに保存し、現代のExcelに認識させるために、名前は_xlfn.プレフィックスを伴って書き込まれます。したがって、ユーザーがBITANDとタイプするだけであっても、ディスク上には_xlfn.BITANDとして保存されます。
注意すべき点は、このルールが均一ではないことです。一部の新しい関数にはテーブルスロットが与えられてプレーンに書き込まれる一方、いくつかのレガシーな非公開関数は、その古さにもかかわらずプレフィックスなしで書き込まれます。HotXLSはどの名前がプレフィックスを必要とするかの明示的なホワイトリストを維持し、書き込み時に追加し、読み込み時にストリップ（削除）するため、設定および読み取る数式テキストは常にExcelと対面するクリーンな名前になります。=BITLSHIFT(5,2)を設定すると、ファイルは_xlfn.BITLSHIFTを保持し、値は変わらず20として戻ります。プレフィックスは、コードで扱う数式に決してリークすべきではない保存上のディテールです。
ワークシートでの統合
これらすべての公開インターフェースは小規模です。TXLSXWorkbookを作成し、ワークシートを追加し、Cells[Row, Col].Formulaを介してセルに数式を書き込んで再計算するか、またはワークシートのCalculateメソッドを使用して式を直接評価します。これは数式をそのシートに対してコンパイルして評価し、Variantを返します。上記の例では、周囲のシート状態なしで単一のエンジニアリング呼び出しの結果を示すためにCalculateを使用していますが、ワークブックの再計算時に、実際のセル数式内でも同じ関数が全く同様に評価されます。
覚えておくべきは呼び出し側ではなくエンコーディングです。バイナリ文字列は10桁で、かつその基数の半分閾値を超えている場合にのみ符号付きになります。複素数はテキストであり、空の虚数係数は1であり、パーサーは指数のeを跨ぎます。左シフトはシフトする前にチェックされます。これら4つの事実を正しく把握すれば、エンジニアリングファミリーは符号のズレによる予期せぬエラーの源ではなくなります。
独自のドメイン数学を同じエンジンに組み込む場合、ハンドラーを登録して値を返す仕組みについては、数式エンジンをカスタム関数で拡張することに関する記事でカバーしています。また、それらの数式がセルのアドレスではなく名前によってシートをまたいでアクセスする必要がある場合は、定義された名前とシートをまたぐ数式に関するチュートリアルで参照の解決方法を説明しています。ここで説明したエンジニアリング関数は、読み込み、書き込み、および計算APIと並んで、DelphiおよびC++Builder向けのHotXLS spreadsheet componentの一部として提供されています。