Έλεγχος caches τύπων Excel με HotXLS Deep Recalc

Το HotXLS απαντά την ερώτηση που κάθε pipeline λογιστικών φύλλων αργά ή γρήγορα πρέπει να κάνει, αν δηλαδή οι αριθμοί που είναι αποθηκευμένοι σε ένα βιβλίο εργασίας ταιριάζουν ακόμα με τους τύπους που τους παρήγαν. Η CalculateAndVerify επανυπολογίζει όλο τον γράφο εξαρτήσεων σε απομονωμένο overlay, συγκρίνει κάθε αποτέλεσμα με την cached τιμή που υπάρχει ήδη στο κελί, και αναφέρει τις διαφωνίες. Εξ ορισμού δεν αλλάζει τίποτα

Ο λόγος που μετράει είναι ότι ένα αρχείο λογιστικού φύλλου αποθηκεύει δύο πράγματα ανά κελί τύπου: τον τύπο και την τελευταία τιμή που κάποιος υπολόγισε γι αυτόν. Το Excel τα κρατά συγχρονισμένα. Οτιδήποτε άλλο στον κόσμο μπορεί όχι. Ένα αρχείο που πέρασε από παλαιότερη βιβλιοθήκη, μερικό επανυπολογισμό, χειροκίνητα επεξεργασμένο XML part ή εργαλείο που έγραψε τιμές χωρίς να τις ξαναϋπολογίσει θα σας παρουσιάσει με χαρά ένα σύνολο που δεν ακολουθεί πια από τις εισόδους του, και τίποτα στη μορφή αρχείου δεν το σημαίνει

Γιατί μια cached τιμή που διαφωνεί με τον τύπο της είναι τόσο επικίνδυνη;

Γιατί είναι αόρατη σε κάθε συνηθισμένο μονοπάτι ανάγνωσης. Ανοίξτε το αρχείο σε viewer, διαβάστε το κελί μέσω API, εξάγετέ το σε CSV ή PDF, και παίρνετε τον cached αριθμό. Ο τύπος είναι ακριβώς εκεί στο ίδιο κελί, και κανείς δεν τους συγκρίνει. Η αναντιστοιχία έρχεται στην επιφάνεια μόνο όταν κάποιος ανοίγει το βιβλίο στο Excel, που επανυπολογίζει στη φόρτωση κάτω από τις περισσότερες ρυθμίσεις, και ξαφνικά μια αναφορά που υπογράφηκε πέρυσι το τρίμηνο δείχνει διαφορετικά σύνολα

Ο έλεγχος υπάρχει για να κάνει εκείνη τη σύγκριση συνειδητή, προγραμματισμένη πράξη αντί για ατύχημα. Είναι το ισοδύναμο λογιστικού φύλλου της επαλήθευσης checksum: φθηνό αρκετά για να τρέχει σε pipeline εισαγωγής, και το μόνο πράγμα που γυρνά ένα σιωπηλό πρόβλημα ακεραιότητας δεδομένων σε αναφορά που μπορείς να δράσεις

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

Υπάρχουν τρία overloads και απαντούν τρεις διαφορετικές ερωτήσεις. Η χωρίς παραμέτρους CalculateAndVerify γυρνά πλήθος αναντιστοιχιών, που είναι όσο θέλει ένας έλεγχος υγείας. Το overload με out πίνακα αναντιστοιχιών σας δίνει τα κελιά. Το overload που παίρνει TXLSRecalcAuditOptions γυρνά πλήρη TXLSCalculationAuditReport, που είναι αυτό που πιάνετε όταν θέλετε να ξέρετε όχι μόνο ότι μια τιμή διαφωνεί αλλά γιατί ο έλεγχος δεν μπόρεσε να αξιολογήσει κάτι

Το overlay, και γιατί ο έλεγχος δεν γράφει

Κάθε επανυπολογισμένη τιμή προσγειώνεται σε overlay και όχι στο cache κελιών, και το overlay εγχέεται στην πολύ μπροστινή θέση του callback ανάγνωσης κελιών και στις δύο μηχανές βιβλίου εργασίας. Εκείνη η τοποθέτηση είναι όσο κάνει τον έλεγχο αυτοσυνεπή: όταν το B1 ξαναϋπολογίζεται και το C1 εξαρτάται από το B1, το C1 βλέπει την τιμή από αυτό το πέρασμα ελέγχου, και όχι την ξεπερασμένη cached. Χωρίς αυτό, ένα μόνο λάθος ανάντη θα αναφερόταν μία φορά και μετά θα απορροφόταν, και κάθε κατάντη κελί θα φαινόταν να συμφωνεί με λάθος είσοδο

Κελιά των οποίων η επανυπολογισμένη τιμή ταιριάζει με το cache δεν μπαίνουν καθόλου στο overlay. Δεν είναι micro-optimization, είναι όσο κρατά τον έλεγχο προσιτό. Ένα καθαρό βιβλίο με εκατό χιλιάδες τύπους κάνει μηδέν εγγραφές overlay και το πέρασμα μένει μέσα σε budget 1.35x απέναντι σε πλήρη επανυπολογισμό, που είναι η διαφορά ανάμεσα σε κάτι που τρέχεις σε κάθε εισαγωγή και κάτι που τρέχεις μία φορά το τρίμηνο

Η αξιολόγηση ακολουθεί σειριακή τοπολογική σειρά παραγόμενη από τον γράφο εξαρτήσεων, με κάθε κόμβο σημειωμένο dirty πρώτα, ώστε κάθε κελί υπολογίζεται ακριβώς μία φορά μετά τις εισόδους του. Αν θέλετε τον επεκτατικό μηχανισμό που κρατά ζωντανό βιβλίο επικαιροποιημένο αντί για έλεγχο αποθηκευμένου, αυτή είναι διαφορετικός μηχανισμός, περιγραφόμενος στο incremental recalculation και ο γράφος εξαρτήσεων

Οι αποτυχίες ταξινομούνται, δεν μαζεύονται μαζί

Ένα κελί που ο έλεγχος δεν μπορεί να αξιολογήσει δεν είναι το ίδιο εύρημα με ένα κελί του οποίου η τιμή διαφωνεί, και η TXLSCalculationAuditIssueKind κρατά τις κατηγορίες χωριστά. Το xlcaiCacheMismatch είναι η διαφωνία τιμής. Τα xlcaiMissingFunction και xlcaiMissingName λένε ότι ο evaluator συνάντησε κάτι που δεν υλοποιεί ή δεν μπορεί να επιλύσει. Το xlcaiUnsupportedArguments καλύπτει σχήματα ορισμάτων έξω από το υποστηριζόμενο υποσύνολο. Τα xlcaiExternalReferenceDenied και xlcaiExternalReferenceMissing διαχωρίζουν άρνηση πολιτικής από απόν βιβλίο εργασίας. Τα xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled και xlcaiInternalFailure συμπληρώνουν το σετ

Μία διάκριση αξίζει να διατυπωθεί γιατί αντιστρέφει κοινή υπόθεση. Θετικός κωδικός σφάλματος Excel είναι αποτέλεσμα, όχι αποτυχία. Ένα κελί που νόμιμα αξιολογείται σε #DIV/0! έχει υπολογιστεί σωστά, οπότε ο έλεγχος αποθηκεύει εκείνο το σφάλμα στο overlay και το συγκρίνει με το cache όπως κάθε άλλη τιμή. Ένα βιβλίο γεμάτο κελιά εκούσιων σφαλμάτων παράγει μηδέν ευρήματα, και ένα βιβλίο όπου ένα σφάλμα εμφανίστηκε ή εξαφανίστηκε από τότε που οι τιμές cacheαρίστηκαν παράγει ακριβώς τα ευρήματα που θέλετε

Οι κυκλικές αναφορές παίρνουν δική τους μεταχείριση. Κόμβοι σε κύκλο δεν μπαίνουν ποτέ στην τοπολογική σειρά, οπότε ο καθένας αναφέρεται ατομικά ως xlcaiCircularReference, και ο έλεγχος δεν τρέχει τον επαναληπτικό solver. Είναι συνειδητό συμβόλαιο μόνο ανάγνωσης: το αν η επανάληψη είναι ενεργή επηρεάζει πώς πρέπει να ερμηνευτεί ο κωδικός αποτελέσματος, όχι τι κάνει ο έλεγχος. Η μηχανική της επαναληπτικής αξιολόγησης καλύπτεται χωριστά στο επαναληπτικός υπολογισμός και κυκλικές αναφορές

Ανάγνωση αλυσίδας αποτυχίας

Όταν ένας τύπος αποτυγχάνει να αξιολογηθεί, το να ξέρεις ποιο κελί απέτυχε σπάνια φτάνει, γιατί η αποτυχία συνήθως απέχει τρία επίπεδα μέσα σε αλυσίδα αναφορών. Κάθε εύρημα κουβαλεί επομένως συμβολοσειρά Stack αποδιδόμενη με το εξωτερικότερο πλαίσιο πρώτο, στη μορφή Sheet1!A1 > Sheet1!B2 > Data!C7, ώστε η αναφορά δείχνει το κελί που όντως έσπασε και όχι το κελί που τυχαία κοιτούσες

Ο καταγραφέας είναι οριοθετημένος. Το MaxStackFrames έχει προεπιλογή 64 με πάτωμα 8, και η βαθύτερη αποτυγχάνουσα αλυσίδα είναι εκείνη που διατηρείται: ένα εσωτερικό πλαίσιο καταγράφει την αλυσίδα όταν η αποτυχία πηγάζει εκεί, και εξωτερικά πλαίσια που ξετυλίγονται μετά δεν την ξεγράφουν. Αν οποιαδήποτε αλυσίδα ξεπέρασε το budget, το Report.StackTruncated ορίζεται, που σας λέει τη διαφορά ανάμεσα σε κοντή αλυσίδα και αλυσίδα που δεν είδατε ολόκληρη

// Μόνο ανάγνωσης εξ ορισμού. Η ApplyResults δεσμεύει το overlay μόνο μετά
// από πλήρως επιτυχή έλεγχο, κάτω από φρουρό εγγραφής που απορρίπτει τη
// δέσμευση αν η δομή του βιβλίου άλλαξε όσο ο έλεγχος τρέχε
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // ακριβής σύγκριση, φέρνει στην επιφάνεια παρασύρσεις
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // ο έλεγχος σταματά στο επόμενο όριο κόμβου
end;

Πότε πρέπει να αφήσετε τον έλεγχο να επιδιορθώσει το βιβλίο εργασίας;

Μόνο όταν ο έλεγχος γύρισε εντελώς καθαρός από ευρήματα κατηγορίας αποτυχίας, που είναι ακριβώς η συνθήκη που επιβάλλει η ApplyResults για εσάς. Η δέσμευση γίνεται μετά από πλήρως επιτυχημένο πέρασμα, δεν ακυρώθηκε, και περνά δομικό φρουρό: η δυαδική μηχανή παρακολουθεί αναγνωριστικό αλλαγής βιβλίου εργασίας, η μηχανή OOXML στιγμιότυπο γέννησης δομής ανά φύλλο. Αν κάτι μετακινήθηκε όσο ο έλεγχος τρέχε, τα αποτελέσματα περιγράφουν βιβλίο που δεν υπάρχει πια και η δέσμευση απορρίπτεται

Προσέξετε τη σκόπιμη ασυμμετρία. Οι αναντιστοιχίες cache δεν μπλοκάρουν την εφαρμογή, γιατί είναι ακριβώς όσο υπάρχει για να επιδιορθώσει η δέσμευση. Τα ευρήματα κατηγορίας αποτυχίας την μπλοκάρουν, γιατί ένα βιβλίο όπου μερικοί τύποι δεν μπόρεσαν να αξιολογηθούν θα ήταν μισοεπιορθωμένο, και ένα μισοεπιορθωμένο βιβλίο είναι χειρότερο από ένα ακατασκεύαστο που ξέρεις να μην εμπιστεύεσαι

Η ανοχή είναι απόφαση πολιτικής, όχι προεπιλογή

Η προεπιλεγμένη σύγκριση είναι απόλυτη ανοχή 1E-6 με σχετική ανοχή απενεργοποιημένη, που διατηρεί την κλασική συμπεριφορά και δέχεται ήσυχα παρασύρση 4E-7. Αυτό είναι συνήθως σωστό: διαφορές σειράς αξιολόγησης κινητής υποδιαστολής ανάμεσα σε ό,τι παρήγαγε το αρχείο και στον τρέχοντα evaluator θα παράγουν διαφορές τέτοιου μεγέθους σε μεγάλα αθροίσματα, και η αναφορά τους ως ευρήματα ακεραιότητας είναι θόρυβος

Ορίστε και τις δύο ανοχές στο μηδέν όταν η ερώτηση είναι διαφορετική, όταν προσπαθείτε να μάθετε αν ένας evaluator άλλαξε συμπεριφορά ανάμεσα σε εκδόσεις, ή αν ένα εργαλείο τρίτων ξαναγράφει τιμές με λεπτά διαφορετικό τρόπο. Στο μηδέν, η ίδια παρασύρση 4E-7 γίνεται ορατή, και όλα τα άλλα επίσης. Διαλέξτε την ανοχή με βάση ποια ερώτηση κάνετε, και καταγράψτε την επιλογή δίπλα στην αναφορά, γιατί μια αναφορά χωρίς την ανοχή της δεν ερμηνεύεται

Δύο γειτονικές δυνατότητες συμπληρώνουν την εικόνα. Όταν θέλετε να μάθετε γιατί ένας μόνο τύπος παράγει την τιμή που παράγει, η βήμα-προς-βήμα θέα στο tracer αξιολόγησης τύπων είναι το σωστό εργαλείο. Όταν σκόπιμα θέλετε cached τιμές να τηρηθούν χωρίς κανέναν επανυπολογισμό, για παράδειγμα σε μονοπάτι εισαγωγής που πρέπει να αναπαραγάγει το αρχείο ακριβώς όπως ήρθε, εκείνη η λειτουργία περιγράφεται στο ανάγνωση cached τιμών τύπων χωρίς επανυπολογισμό. Ο έλεγχος είναι όσο κάθεται ανάμεσα στα δύο: σας λέει αν το να εμπιστευτείς το cache είναι ασφαλές. Παραδίδεται με το HotXLS Delphi spreadsheet component και για τις δύο μηχανές, δυαδική και OOXML