Delphi'de bilinçli döngüsel başvuruları hesaplamak için HotXLS, XLSX motorunda yinelemeli hesaplamayı sunar: TXLSXWorkbook.Iterate özelliğini True olarak ayarlayın ve TXLSXWorkbook.Recalculate algılanan her referans döngüsünü #REF! döndürüp vazgeçmek yerine sabit bir noktaya doğru yürütür — IterateCount geçişine kadar veya her hücre IterateDelta değerinden daha az değişene kadar
Bu ayrım, tek bir boolean değerinin ima ettiğinden daha önemlidir. Referans döngüsünü algılayıp üzerinde dönmeyi reddeden aynı motor, bir özellik açıldığında bu döngüyü yerleşene kadar bilinçli olarak değerlendirir. İki davranışı net tutmak — bir döngünün ne zaman raporlanacak bir hata olduğunu ve ne zaman çözülecek bir model olduğunu bilmek — bu makalenin konusudur
Bir döngüsel başvuru varsayılan olarak neden hata verir?
Varsayılan olarak HotXLS, herhangi bir referans döngüsünü bir yazım hatası olarak değerlendirir ve hesaplamak yerine bunu rapor eder. TXLSXWorkbook.Recalculate bir formül bağımlılık grafiği oluşturur, her formül hücresini topolojik sırada değerlendirir og bir döngü bulduğu an lxErrorRef döndürür — topolojik sıralama sırasında asla serbest bırakılamayan düğümler. Bu döngü üyeleri önceki önbelleğe alınmış değerlerini korur; döngü dışındaki her formül normal şekilde değerlendirilmeye devam eder. Bu grafiğin işleyişi og döngü üyelerinin döngüye sokulmak yerine neden atlandığı, artan formül yeniden hesaplaması ve bağımlılık grafiği hakkındaki yardımcı makalede ele alınmıştır
Varsayılan olan güvenlidir çünkü döngülerin çoğu hatadır: yanlışlıkla kendi SUM aralığına çekilen bir özet satırı, bir başvuruyu kendi üzerine kaydıran bir kopyala-yapıştır işlemi. Yeniden hesaplama zamanında yüksek sesli bir hata kodu, bunlar için tam olarak istediğiniz şeydir. Ancak belirli ve önemli bir model sınıfı bilinçli olarak döngüseldir. Faiz üzerine faiz programları, departmanlar arasındaki döngüsel maliyet veya genel gider dağılımları ve bakiyeye dayalı ücret hesaplamalarının tümü, kendi girdilerini meşru bir şekilde besleyen bir değeri tanımlar — ve Excel bunları yalnızca kullanıcı Dosya → Seçenekler → Formüller → Yinelemeli hesaplamayı etkinleştir seçeneğini işaretlediğinde hesaplar
HotXLS'te yinelemeli hesaplamayı nasıl etkinleştirirsiniz?
HotXLS, bu Excel onay kutusunu TXLSXWorkbook üzerindeki üç özellikle yansıtır: Iterate modu açar ve bu mod True olduğunda, algılanan bir döngü lxErrorRef üretmek yerine yinelemeli bir çözücüye teslim edilir. Kapanış bakiyesinin faize, faizin de bakiyeye bağlı olduğu kanonik faiz üzerine faiz hücre çiftini düşünün:
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Model');
Sheet.Cells[1, 2].Value := 1000; // B1: opening principal
Sheet.Cells[2, 2].Value := 0.05; // B2: period rate
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: balance = principal + interest
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: interest = balance * rate
Book.Iterate := True; // opt in to iterative calculation
if Book.Recalculate = lxOk then
// B3 converges to 1052.63..., B4 to 52.63...
Report(Sheet.Cells[3, 2].Value);
finally
Book.Free;
end;
end;B3, B4'e başvurur ve B4, B3'e başvurur; bu nedenle bağımlılık grafiği iki düğümlü bir döngü rapor eder. Iterate varsayılan değeri olan False'ta bırakıldığında, bu çift lxErrorRef olarak geri dönecek ve hiçbir hücre yerleşmeyecektir. True olduğunda ise Recalculate döngüyü geçerli önbelleğe alınmış değerlerden besler ve üyelerini geçiş üstüne geçişle yeniden değerlendirir, her geçişin çıktılarını bir sonraki geçişin girdileri olarak besler; ta ki sayılar durana kadar. Burada kapalı form principal / (1 - rate) şeklindedir, bu yüzden bakiye 1052.63'te ve faiz 52.63'te yerleşir — yineleme etkinleştirildiğinde Excel'in ürettiği rakamların aynısı
Yinelemeyi ne durdurur?
Çözücüyü iki bağımsız durdurma koşulu sınırlar ve her ikisini de anlamak, bir modelin hata vermesini veya sonsuz dönmesini engelleyen şeydir. IterateCount, döngü üyelerinin kaç kez yeniden değerlendirileceğine dair sabit sınırdır; varsayılan olarak Excel ile eşleşen 100 değerini alır. IterateDelta ise yakınsama eşiğidir: her geçişten sonra çözücü tüm döngü hücrelerindeki en büyük sayısal değişimi ölçer og bu maksimum değişim IterateDelta değerinin — varsayılan 0.001 — altına düştüğünde geçiş döngüsü erken sonlanır. Hangi koşul önce karşılanırsa yinelemeyi o sonlandırır
Book.Iterate := True;
Book.IterateCount := 1000; // hard cap: at most 1000 passes over the cycle
Book.IterateDelta := 0.0001; // convergence: stop once every cell moves < 0.0001
case Book.Recalculate of
lxOk:
// the cycle converged, OR it hit the 1000-pass cap and kept its
// last-iteration values -- both paths return lxOk when Iterate is True
SaveWorkbook(Book);
lxErrorRef:
// only reachable with Iterate = False: the cycle was reported, not solved
LogWarning('Circular reference with iteration disabled');
end;
Bir sonucun açıkça belirtilmesinde fayda vardır, çünkü bu, özelliğin dürüst sınırıdır. Limit yakınsama IterateDelta değerinin altına düşmeden ulaşıldığında, SolveCycleIteratively bir hata oluşturmaz — lxOk döndürür ve hücreleri son yineleme değerleriyle bırakır; tıpkı Excel'in kendi yineleme sınırına yakınsama olmadan ulaşıldığında en son hesaplanan sayıları yazması gibi. Bu nedenle, yinelemeli modda Recalculate yönteminden dönen başarılı bir kod çözücünün yakınsadığı anlamına değil, "çözücünün çalıştığı" anlamına gelir. Geri besleme döngüsü sapan veya salınan bir model, sessizce tüm IterateCount geçişlerini tüketecek ve sabit bir nokta olmayan rakamları geri verecektir; aradaki farkı hiçbir istisna işaret etmez
Ayar XLSX ve XLS dosyalarına nasıl kaydedilir?
Yinelemeli hesaplama ayarları her iki elektronik tablo biçiminde de kalıcıdır, bu nedenle Excel'de açılan bir çalışma kitabı, Delphi kodunuzun yapılandırdığı şekilde davranır. XLSX tarafında, yazar OOXML <calcPr> öğesini yalnızca Iterate True olduğunda yayar ve çıktı boyunu minimumda tutmak için varsayılan değerinde olan tüm öznitelikleri atlar: varsayılanları kullanan bir çalışma kitabı sadece <calcPr iterate="1"/> yazar, iterateCount ise yalnızca 100'den farklı olduğunda ve iterateDelta yalnızca 0.001'den farklı olduğunda yazılır. Açılışta TXLSXWorkbook aynı üç özniteliği geri okur, bu nedenle karşılıklı aktarım simetriktir
Eski BIFF8 (.xls) motoru TXLSWorkbook, eşdeğer durumu farklı bir özellik üçlüsü altında üç ayrı kayıtla taşır. EnableIteration, CalcIter kaydına ($0011, [MS-XLS] §2.4.33), MaxIterations, CalcCount kaydına ($000C, [MS-XLS] §2.4.31) ve MaxIterationChange, CalcDelta kaydına ($0010, [MS-XLS] §2.4.32) eşlenir. Belirleyiciler spec aralıklarını zorlar — CalcCount 1..32767 arasında olmalıdır, bu nedenle MaxIterations sınırlandırılır ve negatif bir MaxIterationChange 0.001 varsayılanına geri döner. Bunları yüklü bir .xls çalışma kitabında ayarlayın ve üç hesaplama kaydı kaydetmede aslına uygun olarak yazılır
var
Book: TXLSWorkbook; // BIFF8 (.xls) engine
begin
Book := TXLSWorkbook.Create;
try
Book.Open('model.xls');
Book.EnableIteration := True; // CalcIter record $0011
Book.MaxIterations := 500; // CalcCount record $000C (clamped to 1..32767)
Book.MaxIterationChange := 0.0001; // CalcDelta record $0010
Book.SaveAs('model.xls'); // the three calc records round-trip
finally
Book.Free;
end;
end;Bilinçli isimlendirme ayrımına dikkat edin: XLSX motoru Iterate / IterateCount / IterateDelta (OOXML kelime dağarcığı) terimlerini konuşurken, BIFF8 motoru EnableIteration / MaxIterations / MaxIterationChange ([MS-XLS] kayıt adlarıyla hizalanmış) terimlerini konuşur. Her iki üçlü de aynı üç düğmeyi açıklar — bir açma/kapama anahtarı, bir yineleme sınırı ve bir yakınsama farkı — varsayılan olarak kapalı, 100 ve 0.001 değerleriyle
Bir döngüsel başvuru ne zaman bir model yerine bir hatadır?
Yinelemeyi etkinleştirmek döngüsel başvuru uyarılarının ortadan kalkmasını sağlamanın bir yolu değildir ve buna bu şekilde yaklaşmak bir tuzaktır. Iterate özelliğini küresel olarak açmak, varsayılan hata kodunun yakalamak için orada olduğu her kazara döngüyü sessizce yakınsayan veya sessizce yakınsamayan bir sayıya dönüştürür. Disiplin tam tersidir: varsayılan modu False tutun ki gerçek yazım hataları yine de lxErrorRef olarak yüzeye çıksın ve yinelemeyi yalnızca döngüselliği tasarımla yapılmış ve anlaşılmış olan çalışma kitaplarında etkinleştirin
Bir döngü ortaya çıktığında ve bunun hangi türden olduğundan emin olmadığınızda, formül değerlendirme izleyicisi bunları birbirinden ayıran araçtır: şüpheli formülü izleyin ve kendi üzerine katlanan referans zinciri adım adım görünür hale gelir, böylece bunun gerçek bir geri besleme döngüsü mü yoksa rastgele bir kendi kendine başvuru mu olduğuna karar verebilirsiniz. Ayrıca döngüsel bir hücrenin döngü boyunca herhangi bir yerleşik işlevi çağırabileceğini hatırlamakta fayda var — bir mühendislik veya karmaşık sayı formülünü çözen aynı hesaplayıcı her geçişte döngü üyelerini değerlendirir — bu nedenle sapan bir model genellikle döngünün içindeki bir formül problemidir, yineleme ayarları problemi değil
Pratik kontrol listesi kısadır. Yinelemeye güvenmeden önce döngünün gerçek bir sabit noktası olduğunu onaylayın; IterateDelta değerini "yakınsadı" ifadesinin modelinizin ihtiyaç duyduğu anlama geleceği kadar sıkı tutun; ve yakınsamasını beklediğiniz bir Recalculate işleminden sonra, sadece lxOk koduna güvenmek yerine bilinen bir çıktıyı mantık kontrolünden geçirin; çünkü bu kod yakınsama ile ulaşılan bir yineleme sınırını ayırt edemez
Yinelemeli döngüsel başvuru hesaplaması, üzerine inşa edildiği artan bağımlılık grafiği yeniden hesaplaması ve ayarı yazdığınız her dosyaya taşıyan XLSX ve BIFF8 kalıcılığı ile birlikte HotXLS Delphi Excel Bileşeni'ndeki XLSX motorunun bir parçasıdır. Bilinçli olarak döngüsel olan finansal ve mühendislik modelleri için bu, bir hata kodu ile bir cevap arasındaki farktır