Teknik Makale

Delphi için HotXLS'te Artımlı Formül Yeniden Hesaplama

Her biri bir üstündeki hücredeki değeri büyüten 10.000 formüllük bir sütun, bir elektronik tablonun tutabileceği en ucuz modellerden biridir. Karşılaştığı her başvurulan formülü yerinde hesaplayan bir değerlendiricide toplamlar yine de doğru çıkar, ama aritmetik fatura çıkmaz: en alttaki hücre 9.999 atasını değerlendirmeye sokar, bir üstündeki 9.998'ini ve tek bir tam geçiş, 10.000 çarpma için kabaca 50 milyon değerlendirme toplar. Tepedeki bir büyüme oranını değiştirin, o değerlendirici bütün faturayı yeniden öder; çünkü hangi hücrenin hangisini okuduğuna dair hiçbir kayıt tutmaz

Düzenlenen tek bir hücre neden bütün çalışma kitabı geçişine mal olabilir

O davranışı iki kısa yol üretir ve elle yazılmış formül motorlarının çoğu birini seçer. Özyinelemeli kısa yol, her başvurulan formülü yeniden hesaplar ve zaten önbellekte olan değeri yok sayar; bu bir zinciri karesel işe, dairesel bir başvuruyu yığın taşmasına çevirir. Tam geçiş kısa yolu daha güvenlidir: hiçbir şey değişmeyene kadar, her formülü belirli bir sabit sırada hesapla. Doğrudur ve maliyeti, düzenlemenin değil çalışma kitabının boyutunu izler

Excel ikisinden de hesaplama zinciriyle kaçınır: bir düzenlemenin küçük bir hücre kümesini kirli işaretleyip yalnızca etkilenen kuyrukta yürümesini sağlayan bir formül hücresi sıralaması. HotXLS'in XLSX motoru aynı fikri, TXLSXWorkbook.Recalculate ardındaki açık bir bağımlılık grafiği biçiminde kullanır. İlk çağrı, her sayfadaki her formülden grafiği kurar ve hepsini değerlendirir. Sonraki her çağrı, yalnızca önceki geçişten bu yana değeri değişen hücrelerin akış aşağısındaki formülleri ve her seferinde çalışması gereken kısa bir formül listesini değerlendirir

Recalculate bir düzenlemeyi nasıl tek bir sıralı geçişe çevirir

Her formül hücresi tek bir düğüm olur ve bir formülün okuduğu her formül hücresinden onu okuyan forma bir kenar gider. SUM(Model!B2:B500) gibi bir başvuru, o dikdörtgenin içindeki her formül hücresinden bir kenar üretir; bir dizi sayfayı kateden bir 3D başvuru, dizideki her sayfayla eşleştirilir. Düz sabitler hiç düğüm değildir; yalnızca bir düzenlemenin başladığı yer olarak önemlidirler

Bir hücreye Value yazmak, yazıldığı anda neredeyse hiçbir şey yapmaz. Çalışma kitabı hücrenin adresini kuyruğa alır, hepsi bu. Recalculate başladığında, başvuruları kuyruktaki bir adresi kapsayan her formül kirli işaretlenir ve işaret, kenarlar boyunca o formülün tüm bağımlılarına, sonra onlarınkine akar. Üzerinde hiç Recalculate çağrılmamış bir çalışma kitabı grafik tutmaz ve yazmaları tek bir nil denetimine mal olur; dolayısıyla yalnızca değer okuyup yazan kod, bu özelliğin bedelini ödemez

Grafiğin kirli kısmı sonra Kahn algoritmasıyla sıralanır. Geçişi ucuz yapan ayrıntı, giriş derecelerinin nasıl sayıldığıdır: yalnızca kirli öncüller sayılır. Temiz bir öncül zaten geçerli bir önbellek değeri taşır; dolayısıyla bir formül onu asla beklemez. Geçiş sırasında başka bir formül hücresine başvuran bir formül, o hücrenin önbellek değerini okur, onu yeniden hesaplamak yerine; sıralama, önbelleğin hâlihazırda taze olduğunu garanti eder. Her kirli formül tam olarak bir kez değerlendirilir

Delphi'de bir HotXLS Workbook.Recalculate geçişinin akış şeması: kirli-bayrak toplama ve topolojik sıralamadan değerlendirme ve döngü bildirimine
Her geçiş kirli kümeyi toplar, topolojik sıralar ve etkilenen her hücreyi bir kez değerlendirir; yinelemeli hesaplama kapalıyken bir başvuru döngüsü döngüye girmek yerine lxErrorRef olarak geri gelir

Aşağıdaki kod, açılış paragrafındaki zinciri kurar ve tek girdisini düzenler. HotXLS'te sayfa indeksleri 1'den başlar; Inputs 1. sayfa, Model 2. sayfadır; Cells'te satırlar ve sütunlar da 1 tabanlıdır

uses
  SysUtils, lxCalc, lxHandleX;   // lxOk, lxCalc içinde tanımlıdır

var
  Book: TXLSXWorkbook;
  Inputs, Model: TXLSXWorksheet;
  Row: Integer;
  Last: Variant;
begin
  Book := TXLSXWorkbook.Create;
  try
    Inputs := Book.Sheets.Add('Inputs');   // Sheets[1]
    Model  := Book.Sheets.Add('Model');    // Sheets[2]

    Inputs.Cells[2, 2].Value := 0.05;      // Inputs!B2'deki büyüme varsayımı
    Model.Cells[2, 2].Formula := 'Inputs!B2*1000';
    for Row := 3 to 10000 do
      Model.Cells[Row, 2].Formula :=
        'B' + IntToStr(Row - 1) + '*(1+Inputs!$B$2)';

    if Book.Recalculate <> lxOk then      // grafiği kurar, 9.999 formülü değerlendirir
      Exit;

    Inputs.Cells[2, 2].Value := 0.07;      // tek bir adres kuyruğa alır
    if Book.Recalculate = lxOk then        // Inputs!B2 ile beslenen formüller yeniden çalışır (burada: 9.999'un hepsi)
      Last := Model.Cells[10000, 2].Value; // önbelleklenmiş sonuç, herhangi bir değer gibi okunur

    // Tek seferlik okuma: RecalcCell önce Recalculate çalıştırır, sonra hücreyi döndürür
    // (sayfa 2 = Model, satır 10000, sütun 2)
    Book.RecalcCell(2, 10000, 2, Last);
  finally
    Book.Free;
  end;
end;

Bu özel modelde her formül Inputs!$B$2'yi okur; dolayısıyla ikinci geçiş yine de 9.999'un hepsine dokunur. Grafik, gerçekten her şeyi etkileyen bir düzenlemeyi küçültemez. Tasarruf, çok girdili modellerde görünür: senaryo döngüsünün üç dört hücre yazdığı, çalışma kitabının geri kalanının önbellek değerlerini koruduğu yerde

HotXLS'de düzenlenen bir girdi hücresinin, yalnızca kendi aşağı akış Delphi model formüllerini yeniden değerlendirme için kirli olarak işaretlediğini gösteren bağımlılık grafiği örneği
Inputs!B2'yi düzenlemek tek bir kirli düğüm tohumlar; kirlilik öncülden-bağımlıya kenarları boyunca akar ve yalnızca o alt grafik yeniden çalışır

Değer yazmaları ucuzdur, formül yazmaları grafiği yeniden kurar

Yalnızca değer düzenlemeleri artımlı kalır. Düğüm ya da kenar kümesini değiştirebilecek her şey bütün grafiği bayat işaretler ve bir sonraki Recalculate onu sıfırdan yeniden kurup her formülü değerlendirir. Bu, Formula atamayı, bir formül hücresine Null ya da boş değer yazarak temizlemeyi, sayfa eklemeyi, taşımayı ya da silmeyi, satır ve sütun ekleyip silmeyi, tanımlı adları düzenlemeyi ve what-if veri tablolarını değiştirmeyi kapsar. Bir kullanıcı işlevi kaydetmek ya da kaydını silmek, ya da OnUserFunction atamak grafiği korur ama her düğümü kirli işaretler

Rapor üreticileri için pratik sonuç bir döngü biçimidir. Formülleri bir kez dizin, Recalculate çağırın ve o andan sonra yalnızca değerlere dokunun. Müşteri başına bir formül şablonunu yeniden yazıp sonra yeniden hesaplayan bir döngü, her yinelemede tam bir yeniden kurulum alır; grafik, tam olarak o maliyetten kaçınmak için vardır. Şablon değişikliği gerçekten gerekiyorsa, bütün formül düzenlemelerini tek bir Recalculate'in önüne yığın; yeniden kurulum böylece bir kez olur. Senaryo döngüsünün kendisi de o zaman yalnızca değer yazar

// Model bir kez kuruldu; Recalculate zaten çağrıldı
for I := 0 to High(Scenarios) do
begin
  Inputs.Cells[2, 2].Value := Scenarios[I].Growth;    // yalnızca değer yazmaları
  Inputs.Cells[3, 2].Value := Scenarios[I].Discount;
  if Book.RecalcNeeded then          // Hücreler kuyruktayken ya da kirliyken True
    if Book.Recalculate <> lxOk then
      Break;
  Results[I] := Model.Cells[10000, 2].Value;
end;

// SaveAs, bir şey hâlâ askıdaysa yeniden hesaplasın
Book.RecalcBeforeSave := xrmIfDirty;
Book.SaveAs('scenarios.xlsx');

RecalcBeforeSave varsayılan olarak bir kayıt sırasında asla yeniden hesaplama yapmayan xrmManual'dır. Üretilen dosyalar için kullanılacak ayar xrmIfDirty'dir: yalnızca grafik bayatmışsa ya da kirli formülleri varsa yeniden hesaplar; böylece döngüde zaten hesaplanmış bir çalışma kitabı olduğu gibi yazılır ve unutulmuş bir düzenlemesi olan ise bayat önbellek değerleriyle kaydedilmez

Hangi Excel işlevleri her geçişte yeniden hesaplamayı zorunlu kılar?

NOW, TODAY, RAND, RANDBETWEEN, RANDARRAY, OFFSET, INDIRECT, CELL, INFO ya da GETPIVOTDATA çağıran formüller HotXLS'te volatildir: yukarı akışta bir şey değişmiş olsun olmasın her geçişin başında kirli işaretlenirler; bağımlılarının hepsi de öyle. Saat ve rastgele işlevleri bariz nedenle volatildir. OFFSET ve INDIRECT ise volatildir, çünkü okudukları hücreler çalışma zamanında bağımsız değişken değerlerinden hesaplanır ve formülün statik bir gezintisi hangi kenarların çizileceğini söyleyemez

Aynı kural, grafik kurucusunun somut bir dikdörtgene sabitleyemediği başvurulara, diğer adlar üzerinden kendine gönderme yapan bir tanımlı ada, dinamik dizi taşma başvurularına ve what-if veri tablolarına da uygulanır. Tanımlı adlar genel olarak sorun değildir: kurucu bir adı, gönderme yaptığı aralıklara açar; dolayısıyla çalışma kitabı ya da sayfa kapsamlı adlardan geçen formüller gerçek kenarlar alır. Tanımlı adlar ve sayfalar arası formüller yazısı o adların nasıl çözümlendiğini ele alır

Muhafazakâr yön burada doğrusudur. Gereksiz bir değerlendirme biraz zaman kostirir; eksik bir kenar, bitmiş görünen bir raporda bayat bir sayı demektir. OFFSET ve INDIRECT'i büyük modellerin sıcak yollarının dışında tutun ve gerçekten hesaplanmış adrese ihtiyaç duyan birkaç hücreyle sınırlayın; çünkü bir zincirin tepesine yakın bir volatile hücre, pratiğe bütün zinciri volatile kılar

OnUserFunction ya da RegisterUserFunction üzerinden sunulan işlevler öteki uçtadır. Volatile değildirler; dolayısıyla bir işleyici, herhangi bir gömülü işlev gibi, yalnızca bağımsız değişkenlerini besleyen hücreler değiştiğinde yeniden çağrılır. Veritabanından bir döviz kuru okuyan bir işleyici bu yüzden geçişler boyunca son yanıtını döndürmeye devam eder. Onun yerine kuru bir girdi hücresine yazın ve o hücreyi bağımsız değişken olarak geçirin; grafik o zaman bağımlılığı görür ve formül, tam olarak kur değiştiğinde yeniden çalışır. HotXLS formül motorunda özel işlevler yazısı işleyici bağımsız değişkenlerinin nasıl ulaştığını anlatır

Dairesel başvurular: lxErrorRef ya da yinelemeli hesaplama

Varsayılan Iterate = False ile TXLSXWorkbook.Recalculate, sıralama bir döngü bulduğunda lxErrorRef döndürür. Döngü üyeleri, Kahn algoritmasının asla serbest bırakamayacağı kirli düğümlerdir ve akış aşağılarındaki kirli her formül aynı nedenle bloke olur. O formüller önceki önbellek değerlerini korur; kirli kümedeki geri kalan her şey sırayla değerlendirilir. Başarısız bir geçiş ayrıca grafiği bayat işaretler; dolayısıyla ondan sonraki çağrı grafiği yeniden kurar ve her formülü bir kez daha değerlendirir

case Book.Recalculate of
  lxOk:
    Book.SaveAs(OutputFile);
  lxErrorRef:
    // döngü ve akış aşağısındaki formülleri eski önbelleklerini hâlâ tutar;
    // LogWarning kendi yordamınızdır
    LogWarning('Circular reference in model, report not written');
else
  LogWarning('Recalculation did not complete');
end;

Iterate := True atamak, Excel'in sunduğu yinelemeli hesaplamayı açar. Motor, döngüyü ve bloke bağımlılarını, hiçbir değer IterateDelta (varsayılan 0,001) kadar ya da fazlasına hareket edene kadar IterateCount geçişe (varsayılan 100) dek yinelenen biçimde değerlendirir. Boş döngü üyeleri 0'dan başlar. Geçiş sınırına ulaşmak son sonuçları korur ve yine de lxOk döndürür; üretilen raporlarda yinelemenin kapalı kalmasının nedeni budur: gerçek rapor modellerindeki döngüler neredeyse her zaman hatadır — örneğin kendi SUM aralığının içine yakalanmış bir toplam satırı — ve siz onlardan istediğiniz kesin bir hata kodudur. Yalnızca kasıtlı bir döngü etrafında kurulmuş modeller için açın; faizin kendisini de içeren ortalama bir bakiye üzerinden işleyen faiz gibi

Dairesel görünen bir durum döngü olarak raporlanmaz. VLOOKUP, HLOOKUP, LOOKUP, MATCH, XLOOKUP ve XMATCH'in arama-dizisi bağımsız değişkeni, o aralıktaki bir hücre değiştiğinde formülü yine kirli yapar ama döngü algılamasına sayılmaz; dolayısıyla B sütununa yazılmış VLOOKUP(A1,B:B,1) normal değerlendirilir. Gerçek döngüler için formül değerlendirme izleyicisi, kendine geri katlanan başvuru zincirini tek tek adımlarla gösterir

Dizi formülleri ve çok iş parçacıklı geçişler

Eski bir CSE dizi formülü, tüm çıpalanmış dikdörtgeni için tek bir düğümdür. Kök formül her geçişte bir kez değerlendirilir, matrisi her üye hücreye yazılır (skaler bir sonuç dikdörtgen boyunca yayınlanır) ve dikdörtgenin — yalnızca sol üst çıpa değil — herhangi bir hücresine başvuran bir formül kökten bir kenar alır. Dinamik dizi formülleri daha ihtiyatlı ele alınır: her geçişte çalışırlar ve bir taşma boyut değiştirdiğinde Recalculate grafiği yeniden kurar ve taşma biçimleri oturana kadar geçişi yineler. 32 tur içinde hiç oturmazlarsa çağrı lxErrorCalc döndürür

1'in üstünde ayarlanan RecalcThreads, bağımsız formüllerin her topolojik düzeyini iş parçacıkları boyunca, iş parçacığı başına ayrı bir hesaplayıcıyla değerlendirir. Veri tabloları yine de seri çalışır ve dinamik diziler içeren bir çalışma kitabı seri yolda kalır. Bu değeri yükseltmeden önce her kullanıcı işlevi işleyicisi thread-safe olmak zorundadır; paylaşılan duruma dokunan bir işleyici için RecalcThreads'ı varsayılan 0'da tutmak daha güvenli seçimdir

Bağımlılık grafiği, volatile işleme ve yinelemeli hesaplama, HotXLS Delphi Excel Component'teki XLSX motorunun parçasıdır; o bileşen formül hesaplayıcısını, tanımlı adları ve çevrelerindeki okuma-yazma hattını da kapsar. Fiyatlandırma sayfalarını ya da konsolidasyon çalışma kitaplarını açık tutup bütün gün yeni girdiler besleyen Delphi ve C++Builder uygulamalarında düzenleme başına iş, düzenlemenin gerçekten ulaştığı hücreleri izler