Σαρώσεις lookup του HotXLS και ψευδείς κυκλικές αναφορές

Βάλτε =VLOOKUP(A1,B:B,1) σε ένα κελί της στήλης B και το Excel το υπολογίζει χωρίς παράπονο. Δώστε το ίδιο βιβλίο εργασίας σε μια μηχανή επανυπολογισμού με γράφο εξαρτήσεων και πιθανότατα θα πάρετε σφάλμα κυκλικής αναφοράς, επειδή ο τύπος εξαρτάται από ένα εύρος που περιέχει τον τύπο. Το HotXLS ανέφερε ακριβώς αυτό μέχρι το v2.361.98. Η διόρθωση δεν είναι ειδική περίπτωση για εύρη ολόκληρης στήλης· είναι μια διάκριση ανάμεσα σε δύο είδη ακμής εξάρτησης που χρειάζεται μια μηχανή λογιστικού φύλλου και δεν έχει ένας απλός κατευθυνόμενος γράφος

Το όρισμα lookup-array της οικογένειας lookup, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP και XMATCH, σημειώνεται πλέον ως αναφορά σάρωσης. Μια αναφορά σάρωσης εξακολουθεί να σπέρνει ακάθαρτη κατάσταση, οπότε η επεξεργασία κελιού μέσα στο εύρος επανυπολογίζει τον τύπο, αλλά δεν συνεισφέρει ποτέ σε ανίχνευση κύκλων ούτε σε διάταξη αξιολόγησης. Οι πραγματικοί κύκλοι εξακολουθούν να βρίσκονται· οι ψευδείς έχουν εξαφανιστεί

Γιατί το Excel επιτρέπει σε εύρος lookup να περιέχει τον τύπο;

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

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

Τι κάνει ένας γράφος εξαρτήσεων με τον ίδιο τύπο

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

Εξάγετε εξαρτήσεις από =VLOOKUP(A1,B:B,1) στο κελί B7 και το δεύτερο όρισμα δίνει ένα εύρος που περιέχει το ίδιο το B7. Ο γράφος έχει πλέον self-loop. Η in-degree εκείνου του κόμβου δεν φτάνει ποτέ το μηδέν, οπότε το τοπολογικό πέρασμα δεν μπορεί ποτέ να τον προγραμματίσει, και το πέρασμα συστατικών τον ταξινομεί ως κύκλο. Η μηχανή συλλογίζεται σωστά για τον γράφο που της δόθηκε. Ο γράφος είναι το λάθος μοντέλο, επειδή κωδικοποιεί ένα είδος ακμής εκεί όπου το λογιστικό φύλλο έχει δύο

Δύο κλάσεις ακμών, ένας γράφος

Η αλλαγή προσθέτει μια σημαία στο record επιλυμένης αναφοράς TXLSDepRange.LookupScan, που ορίζει ο εξαγωγέας εξαρτήσεων όταν διατρέχει το όρισμα lookup-array μίας από τις έξι συναρτήσεις. Κατάντι, οι ακμές που προέρχονται από εκείνες τις αναφορές αποθηκεύονται χωριστά από τις συνηθισμένες: ο κόμβος γράφου κρατά λίστες ScanDependents και ScanPrecedents δίπλα στις κανονικές λίστες εξαρτωμένων και προηγούμενών του

Ο διαχωρισμός είναι αυτό που κάνει τη σημασιολογία σωστή. Οι ακμές σάρωσης διατρέχονται από τη διάδοση ακάθαρτης κατάστασης, οπότε μια επεξεργασία οπουδήποτε στο B:B εξακολουθεί να σημαίνει το B7 ως ακάθαρτο και το B7 επανυπολογίζει. Οι ακμές σάρωσης δεν μετρώνται ποτέ στην in-degree και δεν μπαίνουν ποτέ στον builder συστατικών, οπότε δεν μπορούν να δημιουργήσουν τοπολογικό αδιέξοδο και δεν μπορούν να ταξινομηθούν ως κύκλος. Και οι δύο υλοποιήσεις γράφου στη βιβλιοθήκη, ο κλασικός γράφος ανά βιβλίο εργασίας και ο γράφος χώρου εργασίας δια-βιβλίων που κουβαλά την ανάλυση συστατικών, άλλαξαν μαζί· αν άφηναν να αποκλίνουν θα παρήγαν βιβλίο εργασίας που επανυπολογίζει διαφορετικά ανάλογα με το αν άνοιξε μόνο του ή ως μέρος χώρου εργασίας

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Ledger');
    Sheet.Cells[1, 1].Value := 'ACC-4471';
    Sheet.Cells[1, 2].Value := 1200.00;
    // Το εύρος lookup καλύπτει τη στήλη B, και αυτός ο τύπος ζει μέσα της
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // Πριν το v2.361.98 αυτός ο κλάδος ήταν απρόσιτος για αυτό το φύλλο
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

Τι παρατάτε εξαιρώντας τις ακμές σάρωσης από τη διάταξη

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

Είναι αποδεκτό επειδή αυτό κάνει το Excel. Για ένα βιβλίο εργασίας χωρίς ενεργοποιημένο επαναληπτικό υπολογισμό, η δική του απάντηση του Excel για τιμή που δεν επανυπολογίστηκε ακόμη στο τρέχον πέρασμα είναι η τελευταία υπολογισμένη τιμή, οπότε μια μηχανή που αναπαράγει αυτή τη συμπεριφορά ταιριάζει με την υλοποίηση αναφοράς και όχι την προσεγγίζει. Αν χρειάζεστε πραγματικά συγκλίνουσα απάντηση πάνω σε αυτο-αναφερόμενο μοντέλο, ο μηχανισμός γι' αυτό είναι ο επαναληπτικός υπολογισμός με ρητό όριο επαναλήψεων, που καλύπτεται στο άρθρο για τον επαναληπτικό υπολογισμό, και εφαρμόζεται σε πραγματικούς κύκλους και όχι σε επικαλύψεις σάρωσης

Ο κίνδυνος regression που κρύβεται μέσα στη διόρθωση

Η προσθήκη της LookupScan στο TXLSDepRange εισήγαγε έναν κίνδυνο που δεν έχει καμία σχέση με τα lookups και τα έχει όλα με την Pascal. Το TXLSDepRange είναι unmanaged record, οπότε μια τοπική μεταβλητή εκείνου του τύπου δεν αρχικοποιείται στο μηδέν. Κάθε σημείο της codebase που χτίζει ένα με το χέρι, συμπεριλαμβανομένων των μπλοκ εξαρτήσεων data-table και αρκετών βοηθών test, έπρεπε επομένως να ενημερωθεί ώστε να ορίζει το νέο πεδίο ρητά. Χάστε ένα και ό,τι byte τυχαία ήταν στη στοίβα αποφασίζει αν εκείνη η αναφορά αντιμετωπίζεται ως ακμή σάρωσης, που παράγει σφάλμα επανυπολογισμού που εμφανίζεται και εξαφανίζεται με άσχετες αλλαγές κώδικα

// Ένα νέο Boolean πεδίο σε unmanaged record κάνει κάθε σημείο
// χειροκίνητης κατασκευής λανθάνον σφάλμα. Δύο ασφαλείς ιδιωματισμοί:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // μηδέν παντού, μετά συμπλήρωση
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // ή ορίστε κάθε πεδίο, συμπεριλαμβανομένου του νέου, σε κάθε σημείο
  R.LookupScan := False;
end;

Ο γενικός κανόνας που κερδήθηκε από εκεί: η προσθήκη πεδίου σε record που κατασκευάζεται στη στοίβα σε περισσότερα από μερικά σημεία είναι αλλαγή υψηλότερου κινδύνου απ' όσο φαίνεται, και ο μεταγλωττιστής δεν θα σας βοηθήσει να βρείτε τα σημεία. Αν το record είναι προσιτό από hot διαδρομή, προτιμήστε έναν βοηθό που το αρχικοποιεί πλήρως παρά να εμπιστευτείτε ότι κάθε σημείο κλήσης θα ενημερωθεί

Η διάκριση πραγματικού κύκλου από επικάλυψη σάρωσης

Τίποτα σε αυτή την αλλαγή δεν εξασθενεί την ανίχνευση κύκλων. Το =B7+1 στο B7 εξακολουθεί να είναι κύκλος, μια αλυσίδα τριών τύπων που κλείνει στον εαυτό της εξακολουθεί να είναι κύκλος, και και οι δύο εξακολουθούν να αναφέρονται μέσω του αποτελέσματος επανυπολογισμού με τα μέλη του κύκλου να διατηρούν τις προηγούμενες cached τιμές τους ενώ όλα έξω από τον κύκλο μένουν ενημερωμένα. Αυτό που άλλαξε είναι μόνο ότι το όρισμα lookup-array δεν κατασκευάζει πλέον κύκλους που το Excel δεν βλέπει

Αν ελέγχετε ένα βιβλίο εργασίας και θέλετε να ξέρετε ποιες αναφορές έλυσε πραγματικά η μηχανή και με τι σειρά, ο ιχνηλάτης αξιολόγησης είναι το εργαλείο γι' αυτό· το άρθρο για τον ιχνηλάτη αξιολόγησης τύπων καλύπτει πώς να διαβάσετε την έξοδό του. Το HotXLS είναι εγγενές στοιχείο λογιστικού φύλλου Delphi και C++Builder που διαβάζει και γράφει XLS, XLSX, ODS και CSV χωρίς εγκατεστημένο Excel, και η μηχανή επανυπολογισμού είναι η ίδια σε κάθε μορφή· η τρέχουσα κάλυψη συναρτήσεων και μηχανής καταγράφεται στη σελίδα προϊόντος HotXLS Delphi spreadsheet component