Top 10パーセントのルールが27個の売上数値からなる列に掛かっていて、Excel は3つのセルを塗ります。27の10パーセントは2.7です。四捨五入するレンダラーは3を出し、切り上げるレンダラーも3、切り捨てるレンダラーは2です。3つの推測のうち2つはこの列では Excel と一致しますが、どちらも理由が違います。Excel は件数を2に切り捨てたうえで、2位の88と同値になる3つ目のセルも塗るのです。値を1つ変えれば、この幸運な一致は崩れます
Excel の外で条件付き書式を描くことの難しさは、これに尽きます。ルールはファイルの中にありますが、「どのセルが光り、何色になるか」の答えはファイルの中にありません。出力が HTML レポート、PDF、自前で塗るグリッドのいずれであっても、計算はこちらの持ち回りです。そして算術が隠れているのは、集計系のルールファミリーです。ルールの作成は別の話題で、条件付き書式とリッチテキストスタイルの記事が作成側を扱い、行が動いたときにルール範囲に何が起きるかは、アンカー付き条件付き書式の分割の記事が扱っています。このノートは評価の話です
conditionalFormatting要素が語ることと語らないこと
ECMA-376 と ISO 29500-1 が定義するのは格納であり、そこで終わりです。conditionalFormatting 要素（§18.3.1.18）は sqref を1つと、cfRule の子要素のリスト（§18.3.1.10）を運びます。各ルールは type、任意の operator、priority、stopIfTrue フラグ、1つか2つの formula 子要素を持ち、ビジュアル系ファミリーには cfvo のしきい値集合が付きます。これらはすべて、ユーザーがダイアログでクリックした内容を忠実に記録したものです。そしてどれひとつ、アルゴリズムではありません
多くのルール種別では、この溝は無害です。operator="greaterThan" を持つ cellIs は「より大きい」の意味であり、containsText は部分文字列が存在することを意味します。厄介が始まるのは、判定が範囲の残りに依存するルールです。percent="1" を持つ top10 は、端数の件数をどう丸めるかを語らず、stdDev="1" を持つ aboveAverage は、偏差が標本か母集団かを語りません。そしてどちらも、しきい値と等しい値が含まれるのかについても語りません。ここを間違えると、PDF が、隣のウィンドウで顧客が開いているワークブックと食い違います
TCondFormatRule.Evaluate：1つのセルが自力で判定できること
HotXLS には2つの評価面があり、どこまで答える意志があるかが違います。クラシック（.xls）エンジンが公開するのは lxCondFormat.pas の TCondFormatRule.Evaluate です。計算機、シートインデックス、セル位置、セル値を受け取り、1つのルールに対して真偽値を返します。対応するのは、cellIs の8演算子（between、not between、equal、not equal、greater、less、greater or equal、less or equal）、自由形式の expression ルール（セル位置で評価されるため、相対参照が正しく付け直されます）、4つのテキスト述語、そして空白とエラーの述語です。しきい値の数式はセル位置で解決され、between ルールに上下逆の境界が来た場合は拒否ではなく入れ替えます。テキスト比較は Excel と同じく大文字小文字を区別しません
// クラシックエンジン: 1ルール、1セル、1つの真偽値
function AnyRuleMatches(Calculator: TXLSCalculator; CondFormat: TCondFormat;
SheetIndex, Row, Col: Integer; const Value: Variant): Boolean;
var
I: Integer;
begin
Result := False;
for I := 0 to CondFormat.RuleCount - 1 do
// top10、平均、重複、期間、ビジュアル系はここでは False を返す。
// 範囲全体が必要だからだ
if CondFormat.Rule(I).Evaluate(Calculator, SheetIndex, Row, Col, Value) then
Exit(True);
end;このメソッドは、top10、aboveAverage、belowAverage、duplicateValues、uniqueValues に対して False を返します。他の値を見ずに、自分が上位10パーセントに入るかを1つのセルが知る方法はなく、答えることは推測を意味します。相対日付ルール（timePeriod）もこの経路では False を返します。4つのビジュアル系ファミリー、dataBar、colorScale2、colorScale3、iconSet が False を返すのは別の理由です。これらはイエスかノーを決して出力せず、色、バーの長さ、アイコンインデックスを出力します。真偽値にはそれを入れる場所がありません。したがって、この呼び出しの False は「一致しない」と同じ頻度で「ここでは判定しない」を意味します。比較系ルールの手早なチェックにはそれで足り、出荷する何かに使うには危険です
ワークシート評価器はセルごとの再スキャンをどう避けるか
範囲全体にまたがる仕事は、作成時に一度だけ行います。lxHandleX.pas の TXLSXConditionalFormatEvaluator は、1枚のワークシートの条件付き書式についての不変スナップショットで、TXLSXWorksheet.CreateConditionalFormatEvaluator から取得します。所有と解放は呼び出し側の責任です。素朴な代替案、つまり塗るセルごとにルール範囲をスキャンして平均や順位を計算し直すやり方は、10,000セルの書き出しを、ルールごとに 10,000 回の範囲スキャンに変えてしまいます
構築時に、評価器はそれぞれの異なる sqref を一度だけパースします。同じ範囲に掛かった10個のルールは、1回のパースと、値の入ったセルへの1回の走査を共有します。この走査は平均、母偏差、最小値、最大値を一度の歩みで集め、Top/Bottom ルールやパーセンタイルしきい値が順序統計量を本当に必要とするときにだけ、数値のソート済みコピーを保持します。重複と一意のルールは値キーを一度構築してソートするため、以後のルックアップは二分探索になります。ルールの数式は一度コンパイルされ、セルごとにそのセルのオフセットを適用して再評価されるため、=A1>AVERAGE($A$1:$A$100) は行ごとに再パースされることなく、相対部分と絶対部分が Excel とまったく同じ振る舞いを保ちます。行はグループ化され、EvaluateCell は要求された行に届く範囲を持つルールだけを訪れます
ルールは priority 順に適用されます。数が小さいほど先で、同順はファイル内の位置で決めます。各書式プロパティは、それを設定している最初に一致したルールから取り、優先度の低いルールはまだ空いているプロパティだけを埋めます。太字を設定する優先度1のルールと、赤い塗りを設定する優先度2のルールは両方とも現れ、セルは赤地に太字で出てきます。StopIfTrue を持つルールに一致すると、そのセルの走査はそこで終わります
procedure PaintConditional(Sheet: TXLSXWorksheet; Canvas: TCanvas;
FirstRow, LastRow, FirstCol, LastCol: Integer);
var
Evaluator: TXLSXConditionalFormatEvaluator;
Res: TXLSXCfCellResult;
Row, Col: Integer;
begin
// 描画パスごとに1つのスナップショット。行と列は 1 始まり
Evaluator := Sheet.CreateConditionalFormatEvaluator;
try
for Row := FirstRow to LastRow do
for Col := FirstCol to LastCol do
begin
if not Evaluator.EvaluateCell(Row, Col, Res) then
Continue;
if Res.HasFillColor then
Canvas.Brush.Color := TColor(Res.FillColor and $00FFFFFF); // BGR、TColor と同じ並び
if Res.HasDataBar then // DrawBar は自前のルーチン
DrawBar(Row, Col, Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
if Res.HasIcon then // DrawIcon は自前のルーチン
DrawIcon(Row, Col, Res.IconSetType, Res.IconIndex);
if Res.ShowCellValue and Res.HasNumberFormat and
Sheet.Cells.HasCell(Row, Col) then // DrawCellText は自前のルーチン
DrawCellText(Row, Col,
Evaluator.FormatValue(Sheet.Cells[Row, Col].Value, Res.NumberFormatId));
end;
finally
Evaluator.Free;
end;
end;スナップショットがパスごとに一度だけ構築されるのは、意図的な設計です。内側のループの中で作ると、セルごとにすべてをコンパイルしてスキャンし直します。この設計が存在して避けようとしているのは、まさにそのコストです。ShowCellValue は、データバーのルールが showValue="0" を持つときや、アイコンセットがアイコンのみの表示に設定されているときに False で戻るため、レンダラーはバーかグリフを描き、数値は省きます
Top 10パーセントルールは実際には何セルを光らせるか
件数は切り捨てられ、1未満には決して下がらず、しきい値と同値の値はすべて含まれます。これを仕様は語っていません。Excel がそうするだけで、図の27個の値を列に入力して Top 10% を適用し、塗られたセルを数えれば、1分で確認できます。TXLSXConditionalFormatEvaluator は順位件数を Floor(Count * Min(Rank, 100) / 100) として計算し、ゼロになったら1に上げ、数値の入っているセル数にクランプし、ソート済みの値からしきい値を読み、それからすべてのセルを >= で比較します。27値の列なら、件数2、しきい値88、そして2つのセルが88を持つがゆえに塗られるのは3セル、という意味になります。Bottom-N ルールはこれを <= で鏡写しにします
平均以上のルールには2つ目の曖昧さがあります。stdDev="1" を持つルールは、平均より少なくとも1標準偏差上のセルを選びます。標本偏差と母偏差はベッセル補正ぶんだけ違い、このルールを人が実際に使う短い範囲ほど、その違いが効いてきます。5値では両者は約12パーセント違います。HotXLS は母偏差、つまり STDEV.P が返すのと同じ値を使います。したがって任意のレンダラーを手早く検査する方法は、範囲の隣に =AVERAGE(r)+STDEV.P(r) を置き、どのセルがそれを超えるかを見ることです。偏差バンドに対する比較は含みで、単純な上下の比較は、ルールが equalAverage="1" を持たない限り厳密です
重複と一意のルールは、「同じ値」を何と数えるかで決まります。数値の100とテキストの「100」は同じキーを共有します。数値テキストは数値のキー空間に正規化されるからです。テキストキーは大文字小文字を区別せずに比較されます。TRUE と 1 は別々のままです。種類の違うエラー値も同様です。* や ? を含むテキストは、COUNTIF 流にワイルドカードパターンとして照合されます。Q* を持つセルは、Q で始まるすべての値に対して重複として数えられるということです。空白セルは逆の罠です。範囲内の本当に空のセルは重複として決して塗られないため、書きかけの列が上から下まで光ることはありません。一方、空文字列を保存しているセルは空白として数えられます
カラースケール、データバー、アイコンセットは数値として戻る
ビジュアル系ファミリーは描画準備済みの値に解決されます。そして手書きのレンダラーがずれやすいのは、その端の挙動です。カラースケールでは、EvaluateCell は位置を0から1の区間にクランプし、各 RGB チャネルを切り捨てで補間します。最小ストップより下の値は、外挿された色ではなく最小の色を得ます。3ストップのスケールは、中間しきい値より上の値に中間〜最大のペアを、それ以外に最小〜中間のペアを使います。最初と最後のしきい値が同じ数に解決されるスケールは、ゼロ除算の代わりに最後の色を返します。パーセンタイルしきい値は、隣接するソート済み値のあいだで線形に補間されます。PERCENTILE.INC の定義です
データバーはセル幅に正規化されます。すべてが正の範囲では、DataBarAxis は0で、DataBarEnd はルールの最短バー長と最長バー長のあいだを動きます。既定は10パーセントと90パーセントで、最小値も短いバーを確保し、最大値がセルを完全には埋めません。範囲がゼロをまたぐときは、軸が比例位置に置かれ、DataBarEnd はバーの止まる位置を示します。負の値では軸の反対側です。方向、グラデーション、枠線、負側バーの色は同じレコードに埋められ、テーマカラーと色調はすでに解決済みです
アイコンセットでは、しきい値ごとの厳しさに注意が要ります。最初の cfvo 以降はそれぞれが gte フラグを運ぶからです。評価器はしきい値を上へ歩き、ThresholdEqualsInclude の示すとおりしきい値ごとに >= か > を適用し、値が越えた最も高いしきい値が勝ちます。逆順のセットは解決されたインデックスを反転させ、アイコン単位の上書きは IconSetType を通じて別ファミリーのグリフに差し替えられます。解決できないしきい値はそのセルのルールを止めるため、セルにもっともらしい間違いではなく、まったくアイコンが付きません
グリッド、HTML書き出し、PDFに同じ1つの結果を
TXLSXCfCellResult は、レンダラーの必要なすべてを運びます。テーマの色調が適用済みの塗りとフォント色、太字、斜体、下線、打ち消し線、数値フォーマット id、データバーの形状と色、アイコンのファミリーとインデックスです。HotXLS はこの同じレコードを HTML 書き出し、PDF 書き出し、TXLSWorkbookViewer に供給し、3つの出力がセル単位で一致し続けます。ビューアは最初に描画するときにアクティブなワークシートの評価器を1つ作り、スクロール、選択、再描画のあいだ使い回し、シートを切り替えたり別のワークブックを割り当てたりしたときに破棄します
// ライブビューアの背後での編集: キャッシュされたスナップショットには変更が見えない
Sheet := Viewer.XlsxWorkbook.Sheets[1]; // シートインデックスは 1 始まり
Sheet.Cells[5, 2].Value := 4200; // 平均と最大、順位が動く
Viewer.RefreshConditionalFormats; // スナップショットを捨てて再描画スナップショットは不変なので、その場での編集は、RefreshConditionalFormats が走るまで平均、順位、カラースケールの端点を古いままにします。自前のコードでも同じです。編集をまたいで保持する評価器は、解放して作り直す必要があります。CreateConditionalFormatEvaluator は、呼び出された瞬間の値を取り込むからです
相対日付ルールには、最初に決めておくべきことがあります。ワークシート評価器は timePeriod ルールをマシンの現在日と比較します。「今週」のルールは、今日生成する PDF と、同じワークブックを来週の火曜に書き出したものとで、違う描き方になります。正しい挙動ではあるものの、アーカイブの再現性が求められるならサポートチケットの一種です。アーカイブされるレポートでは、timePeriod ルールを、セルに保存されたレポート日と比較する expression ルールに置き換えてください。7日間の窓なら =AND(A2>=$Z$1-6,A2<=$Z$1) のようになります。出力はワークブックだけに依存し、それ以外には依存しません。なお、Excel は条件付き書式の数式内に構造化テーブル参照を認めないため、ルールは列のセル範囲に向けてください
対象がファイルではなく画面なら、同じ結果レコードは、このブログの別の記事で説明しているカスタム VCL スプレッドシートグリッドも駆動します。ルールモデル、評価器の API リファレンス、トライアルビルドは、HotXLS Delphi スプレッドシートコンポーネントのページにあります