Για τον υπολογισμό σκόπιμων κυκλικών αναφορών (circular references) στο Delphi, το HotXLS εκθέτει επαναληπτικούς υπολογισμούς στη μηχανή του XLSX: ορίστε το TXLSXWorkbook.Iterate σε True και το TXLSXWorkbook.Recalculate διατρέχει κάθε ανιχνευμένο κύκλο αναφοράς προς ένα σταθερό σημείο — έως και IterateCount περάσματα, ή έως ότου κάθε κελί μεταβληθεί κατά λιγότερο από IterateDelta — αντί να επιστρέψει #REF! και να εγκαταλείψει την προσπάθεια
Αυτή η διάκριση έχει μεγαλύτερη σημασία από ό,τι υποδηλώνει μια απλή τιμή boolean. Η ίδια μηχανή που ανιχνεύει έναν κύκλο αναφοράς και αρνείται να εκτελέσει βρόχο σε αυτόν, με μία ιδιότητα τροποποιημένη, θα αξιολογήσει αυτόν τον κύκλο σκόπιμα μέχρι να σταθεροποιηθεί. Η σωστή διάκριση των δύο συμπεριφορών — πότε ένας κύκλος είναι σφάλμα προς αναφορά και πότε είναι ένα μοντέλο προς επίλυση — αποτελεί το θέμα αυτού του άρθρου
Γιατί μια κυκλική αναφορά προκαλεί σφάλμα από προεπιλογή;
Από προεπιλογή, το HotXLS αντιμετωπίζει οποιονδήποτε κύκλο αναφοράς ως σφάλμα σύνταξης και τον αναφέρει αντί να τον υπολογίσει. Το TXLSXWorkbook.Recalculate δημιουργεί ένα γράφημα εξαρτήσεων τύπων, αξιολογεύει κάθε κελί τύπου σε τοπολογική σειρά και επιστρέφει το lxErrorRef τη στιγμή που βρίσκει έναν κύκλο — τους κόμβους που δεν μπορούν ποτέ να απελευθερωθούν κατά την τοπολογική ταξινόμηση. Αυτά τα μέλη του κύκλου διατηρούν τις προηγούμενες προσωρινά αποθηκευμένες τιμές τους. Κάθε τύπος εκτός του κύκλου εξακολουθεί να αξιολογείται κανονικά. Οι μηχανισμοί αυτού του γραφήματος, και ο λόγος για τον οποίο τα μέλη του κύκλου παρακάμπτονται αντί να εκτελούν βρόχο, καλύπτονται στο συνοδευτικό άρθρο σχετικά με τον σταδιακό επανυπολογισμό τύπων και το γράφημα εξαρτήσεων
Η προεπιλογή είναι η ασφαλής επειδή οι περισσότεροι κύκλοι είναι σφάλματα: μια γραμμή σύνοψης που περιλήφθηκε κατά λάθος στο δικό της εύρος SUM, μια αντιγραφή-επικόλληση που μετέφερε μια αναφορά στον εαυτό της. Ένας εμφανής κωδικός σφάλματος κατά τον επανυπολογισμό είναι ακριβώς αυτό που θέλετε για αυτές τις περιπτώσεις. Ωστόσο, μια συγκεκριμένη και σημαντική κατηγορία μοντέλων είναι κυκλική επίτηδες. Τα χρονοδιαγράμματα ανατοκισμού, οι κυκλικές κατανομές κόστους ή γενικών εξόδων μεταξύ τμημάτων και οι υπολογισμοί προμηθειών βάσει υπολοίπου περιγράφουν όλα μια τιμή που νομίμως ανατροφοδοτεί τις δικές της εισόδους — και το Excel τις υπολογίζει μόνο όταν ο χρήστης επιλέξει Αρχείο → Επιλογές → Τύποι → Ενεργοποίηση επαναληπτικού υπολογισμού
Πώς ενεργοποιείτε τον επαναληπτικό υπολογισμό στο HotXLS?
Το HotXLS αντικατοπτρίζει αυτό το πλαίσιο επιλογής του Excel με τρεις ιδιότητες στο TXLSXWorkbook: το Iterate ενεργοποιεί τη λειτουργία, και όταν είναι True ένας ανιχνευμένος κύκλος παραδίδεται σε έναν επαναληπτικό επιλυτή (iterative solver) αντί να παράγει lxErrorRef. Εξετάστε το τυπικό ζεύγος κελιών ανατοκισμού, όπου το τελικό υπόλοιπο εξαρτάται από τον τόκο και ο τόκος εξαρτάται από το υπόλοιπο
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 συγκλίνει στο 1052.63..., το B4 στο 52.63...
Report(Sheet.Cells[3, 2].Value);
finally
Book.Free;
end;
end;Το B3 αναφέρεται στο B4 και το B4 αναφέρεται στο B3, οπότε το γράφημα εξαρτήσεων αναφέρει έναν κύκλο δύο κόμβων. Με το Iterate στην προεπιλογή του False, αυτό το ζεύγος θα επέστρεφε ως lxErrorRef και κανένα κελί δεν θα σταθεροποιούνταν. Με αυτό σε True, το Recalculate τροφοδοτεί τον κύκλο από τις τρέχουσες προσωρινά αποθηκευμένες τιμές και αξιολογεί εκ νέου τα μέλη του πέρασμα μετά από πέρασμα, ανατροφοδοτώντας τα αποτελέσματα κάθε περάσματος ως εισόδους του επόμενου, μέχρι οι αριθμοί να σταματήσουν να αλλάζουν. Εδώ η κλειστή μορφή είναι principal / (1 - rate), οπότε το υπόλοιπο σταθεροποιείται στο 1052.63 και ο τόκος στο 52.63 — τα ίδια νούμερα που παράγει το Excel με ενεργοποιημένη την επανάληψη
Τι σταματά την επανάληψη;
Δύο ανεξάρτητες συνθήκες τερματισμού περιορίζουν τον επιλυτή, και η κατανόηση και των δύο είναι αυτή που εμποδίζει ένα μοντέλο είτε από το να παρουσιάσει σφάλμα είτε από το να εκτελείται επ' άπειρον. Το IterateCount είναι το σκληρό όριο (hard cap) για το πόσες φορές αξιολογούνται εκ νέου τα μέλη του κύκλου. Έχει ως προεπιλογή την τιμή 100, όπως και το Excel. Το IterateDelta είναι το όριο σύγκλισης: μετά από κάθε πέρασμα, ο επιλυτής μετρά τη μεγαλύτερη αριθμητική μεταβολή σε όλα τα κελιά του κύκλου, και μόλις αυτή η μέγιστη μεταβολή πέσει κάτω από το IterateDelta — προεπιλογή 0.001 — ο βρόχος περασμάτων διακόπτεται νωρίς. Όποια συνθήκη εκπληρωθεί πρώτη τερματίζει την επανάληψη
Book.Iterate := True;
Book.IterateCount := 1000; // σκληρό όριο: το πολύ 1000 περάσματα στον κύκλο
Book.IterateDelta := 0.0001; // σύγκλιση: σταματήστε μόλις κάθε κελί μεταβληθεί κατά < 0.0001
case Book.Recalculate of
lxOk:
// ο κύκλος συνέκλινε Ή έφτασε το όριο των 1000 περασμάτων και διατήρησε τις τιμές της τελευταίας επανάληψης -- και οι δύο διαδρομές επιστρέφουν lxOk όταν το Iterate είναι True
SaveWorkbook(Book);
lxErrorRef:
// προσβάσιμο μόνο με Iterate = False: ο κύκλος αναφέρθηκε, δεν επιλύθηκε
LogWarning('Circular reference with iteration disabled');
end;
Μια συνέπεια αξίζει να αναφερθεί ξεκάθαρα, επειδή αποτελεί το ειλικρινές όριο της δυνατότητας αυτής. Όταν επιτευχθεί το όριο χωρίς η μεταβολή να πέσει κάτω από το IterateDelta, το SolveCycleIteratively δεν προκαλεί σφάλμα — επιστρέφει lxOk και αφήνει τα κελιά να κρατούν τις τιμές της τελευταίας επανάληψης, ακριβώς όπως το Excel γράφει τους τελευταίους υπολογισμένους αριθμούς όταν επιτυγχάνεται το δικό του όριο επανάληψης χωρίς σύγκλιση. Έτσι, ένας επιτυχής κωδικός επιστροφής από το Recalculate σε επαναληπτική λειτουργία σημαίνει "ο επιλυτής εκτελέστηκε", όχι "ο επιλυτής συνέκλινε". Ένα μοντέλο του οποίου ο βρόχος ανατροφοδότησης αποκλίνει ή ταλαντώνεται θα καταναλώσει αθόρυβα όλα τα περάσματα IterateCount και θα επιστρέψει αριθμούς που δεν αποτελούν καθόλου σταθερό σημείο, χωρίς καμία εξαίρεση (exception) να επισημαίνει τη διαφορά
Πώς αποθηκεύεται η ρύθμιση σε αρχεία XLSX και XLS;
Οι ρυθμίσεις επαναληπτικού υπολογισμού παραμένουν και στις δύο μορφές υπολογιστικών φύλλων, οπότε ένα βιβλίο εργασίας που ανοίγει στο Excel συμπεριφέρεται με τον τρόπο που το διαμόρφωσε ο κώδικάς σας Delphi. Στην πλευρά του XLSX, ο εγγραφέας εκπέμπει το στοιχείο OOXML <calcPr> μόνο όταν το Iterate είναι True, και παραλείπει οποιοδήποτε χαρακτηριστικό παραμένει στην προεπιλογή του για να διατηρήσει την έξοδο ελάχιστη: ένα βιβλίο εργασίας που χρησιμοποιεί τις προεπιλογές γράφει απλώς <calcPr iterate="1"/>, ενώ το iterateCount εμφανίζεται μόνο όταν διαφέρει από το 100 και το iterateDelta μόνο όταν διαφέρει από το 0.001. Κατά το άνοιγμα, το TXLSXWorkbook διαβάζει τα ίδια τρία χαρακτηριστικά πίσω, οπότε η κυκλική διαδρομή είναι συμμετρική
Η παλαιού τύπου μηχανή BIFF8 (.xls), TXLSWorkbook, μεταφέρει την αντίστοιχη κατάσταση μέσω τριών ξεχωριστών εγγραφών (records) υπό μια διαφορετική τριάδα ιδιοτήτων. Το EnableIteration αντιστοιχίζει στην εγγραφή CalcIter ($0011, [MS-XLS] §2.4.33), το MaxIterations στην εγγραφή CalcCount ($000C, [MS-XLS] §2.4.31), και το MaxIterationChange στην εγγραφή CalcDelta ($0010, [MS-XLS] §2.4.32). Οι setters επιβάλλουν τα εύρη των προδιαγραφών — το CalcCount πρέπει να βρίσκεται στο 1..32767, οπότε το MaxIterations περιορίζεται, και ένα αρνητικό MaxIterationChange επανέρχεται στην προεπιλογή 0.001. Ορίστε τα σε ένα φορτωμένο βιβλίο εργασίας .xls και οι τρεις εγγραφές υπολογισμού γράφονται πιστά κατά την αποθήκευση
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'); // οι τρεις εγγραφές υπολογισμού ολοκληρώνουν την κυκλική διαδρομή
finally
Book.Free;
end;
end;Σημειώστε τον σκόπιμο διαχωρισμό ονομάτων: η μηχανή XLSX χρησιμοποιεί τα Iterate / IterateCount / IterateDelta (το λεξιλόγιο OOXML), ενώ η μηχανή BIFF8 χρησιμοποιεί τα EnableIteration / MaxIterations / MaxIterationChange (ευθυγραμμισμένα με τα ονόματα εγγραφών [MS-XLS]). Και οι δύο τριάδες περιγράφουν τα ίδια τρία κουμπιά — έναν διακόπτη on/off, ένα όριο επαναλήψεων και ένα δέλτα σύγκλισης — με τις ίδιες προεπιλογές: off, 100 και 0.001
Πότε μια κυκλική αναφορά είναι σφάλμα και όχι μοντέλο;
Η ενεργοποίηση της επανάληψης δεν είναι τρόπος για να εξαφανίσετε τις προειδοποιήσεις κυκλικών αναφορών, και η αντιμετώπισή της με αυτόν τον τρόπο είναι παγίδα. Η ενεργοποίηση του Iterate καθολικά μετατρέπει κάθε τυχαίο κύκλο — αυτούς που ο προεπιλεγμένος κωδικός σφάλματος ήταν εκεί για να εντοπίσει — σε έναν αριθμό που συγκλίνει ή δεν συγκλίνει σιωπηλά. Η σωστή πειθαρχία είναι η αντίστροφη: κρατήστε το Iterate σε False ως κανονική λειτουργία, ώστε τα πραγματικά συντακτικά λάθη να εξακολουθούν να εμφανίζονται ως lxErrorRef, και ενεργοποιήστε την επανάληψη μόνο σε βιβλία εργασίας των οποίων η κυκλικότητα είναι βάσει σχεδιασμού και κατανοητή
Όταν εμφανίζεται ένας κύκλος και δεν είστε σίγουροι για το είδος του, ο ιχνηλάτης αξιολόγησης τύπων είναι το εργαλείο που τα ξεχωρίζει: εντοπίστε τον ύποπτο τύπο και η αλυσίδα αναφορών που αναδιπλώνεται στον εαυτό της γίνεται ορατή βήμα προς βήμα, ώστε να μπορείτε να αποφασίσετε εάν κωδικοποιεί έναν πραγματικό βρόχο ανατροφοδότησης ή μια τυχαία αυτοαναφορά. Βοηθά επίσης να θυμάστε ότι ένα κελί κύκλου μπορεί να καλέσει οποιαδήποτε ενσωματωμένη συνάρτηση κατά τη διαδρομή γύρω από τον βρόχο — η ίδια αριθμομηχανή που επιλύει έναν μηχανικό τύπο ή τύπο μιγαδικών αριθμών αξιολογεί τα μέλη του κύκλου σε κάθε πέρασμα — επομένως ένα αποκλίνον μοντέλο είναι συχνά πρόβλημα τύπου μέσα στον βρόχο, και όχι πρόβλημα ρυθμίσεων επανάληψης
Η πρακτική λίστα ελέγχου είναι σύντομη. Επιβεβαιώστε ότι ο βρόχος έχει ένα πραγματικό σταθερό σημείο προτού βασιστείτε στην επανάληψη. Κρατήστε το IterateDelta αρκετά αυστηρό ώστε η "σύγκλιση" να σημαίνει αυτό που χρειάζεται το μοντέλο σας. Και μετά από ένα Recalculate που αναμένετε να συγκλίνει, κάντε έναν έλεγχο λογικής σε μια γνωστή έξοδο αντί να εμπιστεύεστε μόνο το lxOk, καθώς αυτός ο κωδικός δεν μπορεί να διακρίνει τη σύγκλιση από το όριο επανάληψης που επιτεύχθηκε
Ο επαναληπτικός υπολογισμός κυκλικής αναφοράς αποτελεί μέρος της μηχανής XLSX στο HotXLS Delphi Excel Component, μαζί με τον σταδιακό επανυπολογισμό γραφήματος εξαρτήσεων στον οποίο βασίζεται και τη διατήρηση OOXML και BIFF8 που μεταφέρει τη ρύθμιση σε κάθε αρχείο που γράφετε. Για τα χρηματοοικονομικά και μηχανικά μοντέλα που είναι κυκλικά επίτηδες, είναι η διαφορά μεταξύ ενός κωδικού σφάλματος και μιας απάντησης