LlosLabソフトウェア開発ブログPDF、Delphi、およびドキュメントソフトウェア開発者向けのエンジニアリングノート。

技術記事

Delphiエンジニアリング関数：基数変換、複素数演算

HotXLS Component

Excelのエンジニアリング関数ファミリーは、関数リファレンスの中で最も簡単な部分のように見えます。DEC2BIN は数値を2進数文字列に変換します。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桁は固定された予算であり、その予算が基数ごとに表現可能な範囲を設定します。2進数で負の半分に反転する大きさのしきい値は512であり、ラップモジュラスは1024であるため、2進数文字列が符号付きになるのは、長さがちょうど10文字で、その値が512以上の場合のみです。同じ考え方が基数に応じてスケールします。8進数では半分のしきい値2^29とフルのモジュラス2^30を使用します。16進数では2^39と2^40を使用します。HotXLSのリーダーはまさにこのルールを適用します。桁を蓄積し、文字列が10文字幅であり、蓄積された値が半分のしきい値以上である場合にのみ、フルモジュラスを減算して符号付きの値を取得します。9文字の文字列は、どんなに大きくても常に非負です

エンコーダーはその鏡像です。非負の値は桁ごとに変換され、要求された幅にオプションでゼロパディングされます。基数の正の上限をオーバーフローする場合や、要求された幅が狭すぎて保持できない場合は拒否されます。負の値は、まずフルモジュラスを加算することで範囲内に収められ、これによりその基数表現は常に10桁の値になります。その後、幅を埋めるために先頭のゼロとともに桁が出力されます。単一の共有される範囲チェック、つまり基数ごとの対称的な下限と上限が、DEC2BIN、DEC2OCT、および DEC2HEX をその境界で互いに一貫性を保たせるものです

残るは、関数名に10進数を経由せずに基数を変更する HEX2BIN や OCT2HEX のような、基数間の変換です。実装では、順序付けられたすべてのペアに対して個別のルーチンを用意していません。入力文字列をソース基数を使用して符号付きの10進数として解析し、その10進数値をターゲット基数にフォーマットします。10進数がピボットになります。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 と入力するだけでも、BITAND はディスク上では _xlfn.BITAND として保存されます

問題は、このルールが一律ではないことです。一部の新しい関数にはテーブルスロットが与えられて裸で書き込まれますが、古いにもかかわらず接頭辞なしで書き込まれるレガシーな隠し関数もいくつかあります。HotXLSは、どの名前に接頭辞が必要かという明示的なホワイトリストを保持し、書き込み時にそれを追加し、読み取り時にそれを取り除くため、設定および読み戻す数式テキストは常にクリーンなExcel向けの名前になります。=BITLSHIFT(5,2) と設定すると、ファイルには _xlfn.BITLSHIFT が保持されますが、値は無関係に20として戻ってきます。接頭辞は、コードで扱う数式に決して漏れてはならないストレージの詳細です

ワークシートでのまとめ

これらすべての公開サーフェスは小さいです。TXLSXWorkbook を作成し、ワークシートを追加し、Cells[Row, Col].Formula を通じてセルに数式を書き込んで再計算するか、ワークシートの Calculate メソッドを使用して式を直接評価します。このメソッドはそのシートに対して数式をコンパイルし、Variant を返します。上記の例では、周囲のシート状態なしで単一のエンジニアリング呼び出しの結果を示すために Calculate を使用していますが、同じ関数は、ワークブックが再計算するときに実際のセル数式内で同一に評価されます

覚えておくべき部分は呼び出し元ではなく、エンコーディングです。2進数文字列は、10桁で、かつその基数の半分のしきい値を超えた場合にのみ符号付きになります。複素数はテキストであり、空の虚数係数は1であり、パーサーは指数の e をまたぎます。左シフトはシフトする前にチェックされます。これら4つの事実を正しく理解すれば、エンジニアリングファミリーが符号のズレによる驚きの原因になることはなくなります

独自のドメインの計算を同じエンジンに組み込む場合、ハンドラの登録や値を返す仕組みについては、カスタム関数による数式エンジンの拡張に関する記事で解説しています。また、数式がセルアドレスではなく名前でシートをまたぐ必要がある場合、参照がどのように解決されるかは定義された名前とクロスシートの数式に関するウォークスルーで示されています。ここで説明したエンジニアリング関数は、このブログの他の場所で説明している読み取り、書き込み、および計算APIとともに、DelphiおよびC++Builder用のHotXLSスプレッドシートコンポーネントの一部として出荷されています