Delphi'de Excel Formül Değerlendirmesini Adım Adım İzleme

Excel, göz önünde küçük bir hata ayıklayıcı gizler. Bir hücre seçin, Formüller'i açın ve Formülü Değerlendir'e tıklayın; bir iletişim kutusu formülü bir alt ifadesi (subexpression) altı çizili olarak gösterir. Değerlendir'e (Evaluate) basın ve bu alt ifade kendi değerine çöker (collapses), ardından bir sonrakinin altı çizilir ve uzun bir ifadenin her bir indirgemede (reduction) tek bir sayıya küçülmesini izlersiniz. Hangi iç içe (nested) IF dalının gerçekten tetiklendiğini veya hangi başvurunun (reference) yanlış bir toplamı beslediğini bulmanın en hızlı yolu budur. HotXLS, TXLSFormulaTracer aracılığıyla tam olarak bu davranışı yeniden üretir, böylece bir Delphi veya C++Builder programı bir çalışma kitabını (workbook) denetlemek, oluşturulmuş bir formülde hata ayıklamak veya birisine sonucun neden bu şekilde çıktığını öğretmek için aynı adım listesini oluşturabilir. Kaydedilen her adım alt ifade metnini ve indirgendiği değeri taşır

İndirgeme motoru ifadede nasıl gezinir

İzleyici (tracer), hesaplama motoruna (calculation engine) uzanmaz. Formülü belirteçlere (tokens) ayırır ve onu yinelemeli-iniş (recursive-descent) bir ayrıştırıcıyla (parser) ayrıştırır, ardından ağacı (tree), en içteki değerlendirilebilir alt ifade önce olmak üzere derinlik-öncelikli (depth-first) olarak indirger. Bir düğüm bir değere indirgendiğinde, o değer çevresindeki ifadeye bir değişmez (literal) olarak geri yerleştirilir (substituted) ve motor gerçek hesaplayıcıdan (calculator), artık daha basit olan ifadeyi yeniden hesaplamasını ister. Her adım, özel bir kısayol yerine çalışma sayfasının (worksheet) genel Calculate yöntemi aracılığıyla değerlendirildiğinden, her adım hücrenin tam bir yeniden hesaplamasının (recalculation) üreteceği şeyle tam olarak uyuşur (agrees). Ayrıştırıcı, tasarımı gereği müdahaleci değildir (non-invasive); bu da onun herhangi bir çalışma sayfasına karşı, durumunu bozmadan çalışmasını sağlayan şeydir

Ayrıştırıcı, her bir öncelik bandı başına bir yinelemeli düzey ile bir operatör-öncelik (operator-precedence) merdiveni izler. En düşük bağlamadan (binding) en yükseğe bantlar şunlardır: seviye 0 karşılaştırma (=, <>, <, >, <=, >=), seviye 1 dize birleştirme (&), seviye 2 toplama ve çıkarma, seviye 3 çarpma ve bölme, seviye 4 üs alma ve son olarak bunun altındaki tekli (unary) artı ve eksi. Her bir düzey, kendi işlenenleri (operands) için bir üstündeki düzeyi ayrıştırır, böylece daha yüksek bir bant daha sıkı bağlanır. Bu, Excel'in uyguladığı önceliğin (precedence) aynısıdır; A1*B1+A2*B1'in toplamdan önce iki çarpımı indirgemesinin nedeni budur: çarpma seviye 3'te, toplama seviye 2'de oturur, bu nedenle çarpmalar ağacın daha derinlerindedir ve önce indirgenirler

Bir formülün izini sürme ve adımlarda gezinme

Kullanım (Usage), Demo/Delphi/FormulaTrace/FormulaTrace.dpr'de gönderilen demoyu yansıtır. Bir çalışma sayfası oluşturun (veya mevcut bir çalışma kitabını açın), sayfa üzerinde bir izleyici inşa edin, Trace öğesini çağırın ve döndürülen diziyi yineleyin (iterate). Her bir TXLSFormulaStep, girinti (indentation) için Depth, orijinal alt ifade için Source, işlenenleri zaten değiştirilmiş o alt ifade için Expression ve adımın sonucu için Value değerini sunar (exposes)

uses
  SysUtils, Variants, lxHandle, lxHandleX, lxFormulaTrace;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Tracer: TXLSFormulaTracer;
  Steps: TXLSFormulaStepArray;
  Final: Variant;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Order');
    Sheet.Cells[1, 1].Value := 10;    // A1 units
    Sheet.Cells[1, 2].Value := 25;    // B1 unit price
    Sheet.Cells[1, 3].Value := 0.08;  // C1 tax rate

    Tracer := TXLSFormulaTracer.Create(Sheet);
    try
      Final := Tracer.Trace('A1*B1*(1+C1)', Steps);
      for I := 0 to High(Steps) do
        Writeln(StringOfChar(' ', Steps[I].Depth * 2),
                Steps[I].Source, ' -> ', Steps[I].Expression,
                ' = ', VarToStr(Steps[I].Value));
      Writeln('result = ', VarToStr(Final));
    finally
      Tracer.Free;
    end;
  finally
    Book.Free;
  end;
end;

Hücre başvuruları önce çözümlenir (resolve) ve kendi adımları olarak görünür, ardından çarpımlar indirgenir, sonra parantez içindeki vergi faktörü ve son çarpma işlemi kapatır. Depth (Derinlik) alanı, tıpkı Excel'in herhangi bir dış terimden önce en içteki terimin altını çizmesi gibi, en içteki indirgemelerin görünür bir şekilde en derinde durması için girinti yapmanızı sağlar

Yerel ayar içermeyen değişmez değeri tuzağı

Tüm bu plandaki en tehlikeli ayrıntı, İngilizce bir makinede görünmezken Almanca bir makinede yüksek sesle bozulmasıdır (breaks loudly). Hesaplanmış bir sayı formül metnine geri yerleştirildiğinde, bir dize olarak yazılmalı ve ardından . öğesini ondalık nokta (decimal point) olarak ele alan hesaplama motoru tarafından yeniden ayrıştırılmalıdır. Eğer değiştirme (substitution) sistem yerel ayarını (locale) kullansaydı, Almanca bir TFormatSettings vergi faktörü için 1,08 yazardı, virgül (comma) bir argüman ayırıcısı olarak okunurdu ve A1*B1*1,08'in yeniden hesaplanması ya yanlış bir şekle ayrışır ya da tamamen başarısız olurdu

İzleyici, DecimalSeparator'ün (Ondalık Ayırıcı) . olarak zorlandığı ve hiçbir gruplama karakterinin hiçbir zaman yayılmaması için ThousandSeparator'ün (Binlik Ayırıcı) #0 olarak ayarlandığı, inşada iğnelediği (pins) özel bir TFormatSettings üzerinden her sayısal değişmezi biçimlendirerek bunu önler. FloatToStr ardından, operatörün bölgesel (regional) ayarları ne olursa olsun, motorun her zaman geri okuyabileceği bir değişmez üretir

// Conceptually what the tracer pins once, at construction
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// every reduced number is written with: FloatToStr(Double(V), FFloatFmt)

Bu, yazarın kendi testlerinde hiçbir zaman ortaya çıkmayan ve yalnızca başka bir yerel ayardaki bir müşteri aynı kodu çalıştırdığında yüzeye çıkan türden bir hatadır, bu yüzden bunu açıkça (plainly) belirtmekte fayda var: bir değeri formül metni aracılığıyla gidiş dönüşe sokmak (round-tripping) bir serileştirme sorunudur ve serileştirme yerel ayar içermemelidir (locale-free)

Boolean'lar 1 ve 0'a indirgenir

İlgili bir değiştirme (substitution) kararı mantıksal değerlerle ilgilidir. Bir alt ifade boolean bir değer aldığında, izleyici bunu TRUE veya FALSE olarak değil, 1 veya 0 olarak geri yazar. Bunun nedeni, indirgenmiş değişmezin, etrafını saran bağlam ne olursa olsun temiz bir şekilde yeniden ayrıştırılmak zorunda olmasıdır ve aritmetik zorlu (demanding) olan durumdur. Eğer A1>A2 gibi bir karşılaştırma TRUE metnine indirgenseydi ve o metin TRUE*B1'in içine inseydi, yeniden hesaplama motorun çarpmada (multiplication) yalın bir boolean anahtar kelimesini (keyword) kabul etmesine bağlı olurdu. 1'i değiştirmek soruyu tamamen atlar (sidesteps), çünkü 1*B1 herhangi bir aritmetik konumda nettir (unambiguous). Aynı zamanda, bir numara beklendiği an TRUE'nun 1 ve FALSE'un 0 olarak davrandığı Excel'in kendi zorlamasıyla (coercion) da eşleşir

İşlev çağrıları atomik olarak indirgenir

Naif bir adım motoru, önce bir işlevin argümanlarını ve ardından çağrıyı indirgerdi. Bu Excel için yanlıştır ve izleyici kasten (deliberately) bunu yapmaz. Bir işlev çağrısı, orijinal metninden tek bir adımda bir bütün olarak değerlendirilir. Neden, kısa devre (short-circuit) semantiğidir. IF, CHOOSE ve IFERROR yalnızca seçtikleri dalı değerlendirir ve önce argümanları indirgemek, motoru Excel'in asla dokunmayacağı dalları hesaplamaya zorlar. Klasik zayiat, IF(B1=0,0,A1/B1) gibi bir sıfıra bölme korumasıdır: İzleyici IF'i değerlendirmeden önce A1/B1'i indirgeseydi, koruma tekleyerek (misfire) önlemek için var olduğu hatanın tam da kendisini yükseltirdi. İzleyici, tüm çağrıyı atomik olarak değerlendirerek, bu tür korumaların çalışmasını sağlayan tembel değerlendirmeyi (lazy evaluation) korur

// IF is one atomic step; only the selected branch is evaluated
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 is true, so the step records A1*B1 as the chosen result;
// A2*B1 is never computed, exactly as Excel would do it.

Takas (trade-off) şudur ki işlev çağrısının içini ayrı adımlar olarak göremezsiniz, ancak doğru olan davranış budur. Excel'in hiçbir zaman gerçekleştirmediği argüman indirgemelerini göstermek, çağrıyı gerçekte olduğu gibi tek bir değerlendirme birimi olarak ele almaktan daha yanıltıcı bir izleme olacaktır

Argüman ayırıcılar ve bozulmamış aralıklar

İki normalleştirme daha yeniden hesaplamayı dürüst tutar. Hesaplama motorunun derleyicisi (compiler), işlev argüman ayırıcısı olarak ; bekler, bu nedenle izleyici bir işlev çağrısını ayrıştırılmış ağacından (parsed tree) yeniden oluşturduğunda, kullanıcı orijinal olarak , yazmış olsa bile argümanları ; ile birleştirir. SUM(A1,A2,A3) olarak yazılmış bir formül, motorun kabul ettiği SUM(A1;A2;A3) olarak yeniden hesaplanır. Değerlerin ikame edilmesi (substitution) bu yeniden oluşturmayı (rebuild) gerekli kılan şeydir ve ayırıcıyı doğru bir şekilde almak yeniden oluşturmanın ayrıştırılmasını (parse) sağlayan şeydir

Aralık (Range) başvuruları diğer durumdur. A1:A3 gibi bir aralık skaler değildir ve üç ayrı değere bölünmemelidir, çünkü onu tüketen işlev bir aralık argümanı bekler. İzleyici bir aralığı kendi orijinal metni olarak bozulmamış (intact) halde tutar ve çevreleyen işlevin bir bütün olarak indirgenmesini sağlar. SUM(A1:A3)*B1'de aralık bütün kalır, SUM(A1:A3) tek bir atomik adımda tek bir sayıya indirgenir ve ancak o zaman dıştaki çarpma çalışır. Bu, Excel'in bir aralık işleneni ile onun sonunda katkıda bulunduğu (contributes) skaler arasına çizdiği sınırın aynısıdır

// The range A1:A3 is never split; SUM is one atomic reduction,
// then the product with B1 reduces on top of it.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
  Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));

Bir araya getirildiğinde, bu kurallar adım listesini (step list) Excel'in Formülü Değerlendir komutunun bir yaklaşık değerinden (approximation) ziyade sadık (faithful) bir aynası yapar. İndirgemeler Excel'in bunları gerçekleştirdiği sırayla olur, ikame edilen (substituted) değişmezler herhangi bir yerel ayardan sağ çıkar, boolean'lar Excel'in onları zorladığı şekilde zorlar (coerce) ve tembel işlevler tembel kalır. Motoru kendi işlevlerinizle daha da ileriye itmek istiyorsanız, formül motoru ve özel işlevler makalesi bunların nasıl kaydedileceğini gösterir ve daha ağır sayısal işler için Delphi'deki istatistiksel dağılım işlevleri makalesi izleyicinin karşı değerlendirdiği (evaluates against) yerleşik kitaplığı (built-in library) ele alır. Tümü, bu blogda başka yerlerde ele alınan okuma, yazma, biçimlendirme ve hesaplama API'lerinin yanı sıra, Delphi ve C++Builder için HotXLS elektronik tablo bileşeni (spreadsheet component) bir parçası olarak gönderilir