Η οικογένεια των συναρτήσεων μηχανικής στο Excel διαβάζεται ως η ευκολότερη γωνιά της αναφοράς συναρτήσεων. Η DEC2BIN μετατρέπει έναν αριθμό σε δυαδική συμβολοσειρά. Η HEX2DEC τον μετατρέπει πίσω. Η IMSUM προσθέτει δύο μιγαδικούς αριθμούς. Κάθε μία μοιάζει με μια άσκηση μορφοποίησης. Δεν είναι. Πίσω από αυτά τα ονόματα κρύβεται μια κωδικοποίηση συμπληρώματος ως προς δύο δέκα bit (ten-bit two's complement encoding) την οποία οι περισσότεροι προγραμματιστές δεν έχουν αγγίξει από το μάθημα της αρχιτεκτονικής υπολογιστών, μια μορφή μιγαδικών αριθμών που ζει εξ ολοκλήρου μέσα σε συμβολοσειρές (strings), και τελεστές bitwise που αθόρυβα θα προκαλέσουν υπερχείλιση (overflow) σε έναν ακέραιο 64-bit αν κάνετε μετατόπιση (shift) πριν ελέγξετε. Μια μηχανή υπολογιστικών φύλλων (spreadsheet engine) που αναπαράγει το Excel επακριβώς δεν μπορεί να στρογγυλοποιήσει τίποτα από όλα αυτά
Οι συναρτήσεις χωρίζονται σε τρεις ομάδες, και κάθε ομάδα κρύβει μια διαφορετική παγίδα. Η μετατροπή βάσης αφορά τους αρνητικούς αριθμούς και τα όρια ανά βάση. Η μιγαδική αριθμητική αφορά την ανάλυση (parsing) και τη μορφοποίηση (formatting) μιας συμβολοσειράς. Οι πράξεις bitwise αφορούν την παραμονή εντός των ορίων του Int64. Αυτό το άρθρο διατρέχει κάθε ομάδα όπως τις υλοποιεί το HotXLS, με τις κλήσεις του φύλλου εργασίας που θα γράφατε πραγματικά
Μετατροπή βάσης και το συμπλήρωμα ως προς δύο των δέκα bit
Η μπροστινή κατεύθυνση είναι το μέρος που περιμένουν όλοι. Η κλήση DEC2BIN(9) δίνει "1001", και ένα προαιρετικό δεύτερο όρισμα γεμίζει αριστερά το αποτέλεσμα σε ένα σταθερό πλάτος (fixed width). Η παγίδα είναι η αρνητική είσοδος. Το Excel δεν γράφει το σύμβολο μείον. Κωδικοποιεί την τιμή ως μια συμβολοσειρά δέκα ψηφίων με συμπλήρωμα ως προς δύο στη βάση στόχο, και γι' αυτό το DEC2BIN(-5,10) επιστρέφει "1111111011" και όχι οτιδήποτε με πρόσημο. Το όρισμα των θέσεων (places) αγνοείται από τη στιγμή που η τιμή είναι αρνητική, επειδή η κωδικοποίηση είναι ήδη καρφιτσωμένη στα δέκα ψηφία
Τα δέκα ψηφία είναι ένας σταθερός προϋπολογισμός (budget), και αυτός ο προϋπολογισμός θέτει το εύρος που μπορεί να αναπαρασταθεί ανά βάση. Στο δυαδικό, το μέγεθος που αναποδογυρίζει στο αρνητικό μισό είναι το 512, και το modulo αναδίπλωσης (wrap modulus) είναι το 1024, οπότε μια δυαδική συμβολοσειρά έχει πρόσημο μόνο όταν έχει μήκος ακριβώς δέκα χαρακτήρες και η τιμή της είναι τουλάχιστον 512. Η ίδια ιδέα κλιμακώνεται ανάλογα με τη βάση. Το οκταδικό (octal) χρησιμοποιεί ένα όριο στο μισό της τιμής (half threshold) ίσο με 2^29 και ένα πλήρες modulo 2^30. Το δεκαεξαδικό (hexadecimal) χρησιμοποιεί 2^39 και 2^40. Ο reader του HotXLS εφαρμόζει ακριβώς αυτόν τον κανόνα: συσσωρεύει τα ψηφία, και μόνο όταν η συμβολοσειρά έχει πλάτος δέκα χαρακτήρες και η συσσωρευμένη τιμή βρίσκεται στο μισό όριο ή και παραπάνω, αφαιρεί το πλήρες modulo για να ανακτήσει την προσημασμένη (signed) τιμή. Μια συμβολοσειρά εννέα χαρακτήρων είναι πάντα μη αρνητική, ανεξάρτητα από το πόσο μεγάλη είναι
Ο κωδικοποιητής (encoder) είναι η αντίστροφη εικόνα. Μια μη αρνητική τιμή μετατρέπεται ψηφίο προς ψηφίο και προαιρετικά γεμίζει με μηδενικά στο ζητούμενο πλάτος, και απορρίπτεται αν υπερβαίνει τη θετική οροφή (positive ceiling) της βάσης ή εάν το ζητούμενο πλάτος είναι πολύ στενό για να τη χωρέσει. Μια αρνητική τιμή αρχικά φέρεται εντός εύρους προσθέτοντας το πλήρες modulo, το οποίο τη μετατρέπει σε μια τιμή της οποίας η αναπαράσταση βάσης είναι πάντα δέκα ψηφία, και στη συνέχεια τα ψηφία εκπέμπονται με μηδενικά στην αρχή για να γεμίσουν το πλάτος. Ο μοναδικός κοινός έλεγχος εύρους, τα συμμετρικά κατώτερα και ανώτερα όρια ανά βάση, είναι αυτό που διατηρεί τις DEC2BIN, DEC2OCT και DEC2HEX συνεπείς μεταξύ τους στα άκρα τους
Αυτό αφήνει τις μετατροπές μεταξύ βάσεων (cross-base), αυτές όπως οι HEX2BIN και OCT2HEX που αλλάζουν βάση χωρίς να περνούν από το δεκαδικό στο όνομα της συνάρτησης. Η υλοποίηση δεν φέρει ξεχωριστή ρουτίνα για κάθε διατεταγμένο ζεύγος (ordered pair). Αναλύει τη συμβολοσειρά εισόδου σε μια προσημασμένη δεκαδική τιμή χρησιμοποιώντας την αρχική βάση, στη συνέχεια μορφοποιεί (formats) αυτήν τη δεκαδική τιμή στη βάση προορισμού. Το δεκαδικό είναι ο άξονας (pivot). Μία ρουτίνα ανάλυσης (parse) και μία ρουτίνα μορφοποίησης, συνδυασμένες, καλύπτουν κάθε συνδυασμό, και επειδή και τα δύο μισά μοιράζονται την ίδια προσημασμένη σύμβαση των δέκα ψηφίων, μια αρνητική τιμή επιβιώνει από το ταξίδι με το πρόσημό της ανέπαφο
Οι μιγαδικοί αριθμοί είναι συμβολοσειρές, οπότε η δουλειά είναι η ανάλυση (parsing)
Το Excel δεν διαθέτει τύπο μιγαδικών δεδομένων. Μια μιγαδική τιμή (complex value) είναι η συμβολοσειρά "a+bi", και κάθε συνάρτηση στην οικογένεια IM δέχεται αυτές τις συμβολοσειρές και επιστρέφει μία τέτοια. Η COMPLEX χτίζει τη συμβολοσειρά από ένα πραγματικό και ένα φανταστικό (imaginary) μέρος. Οι IMSUM, IMSUB, IMPRODUCT και IMDIV αναλύουν τα ορίσματά τους, κάνουν την αριθμητική στα αριθμητικά μέρη, και μορφοποιούν το αποτέλεσμα ξανά σε μια συμβολοσειρά. Η αριθμητική εργασία είναι άλγεβρα προπτυχιακού επιπέδου. Η δυσκολία βρίσκεται εξ ολοκλήρου στη μετατροπή του κειμένου σε δύο αριθμούς κινητής υποδιαστολής (floating-point) με αξιόπιστο τρόπο, και εκεί είναι που ο εσωτερικός parser (αναλυτής) κερδίζει την αξία του
Δύο λεπτομέρειες σε αυτόν τον parser είναι εύκολο να γίνουν λάθος. Η πρώτη είναι η γυμνή φανταστική μονάδα. Η συμβολοσειρά "i" σημαίνει ένα επί i, όχι μηδέν και όχι σφάλμα, οπότε όταν ο συντελεστής μπροστά από την κατάληξη (suffix) είναι κενός ή είναι ένα μεμονωμένο σύμβολο συν, ο parser πρέπει να τον διαβάσει ως την τιμή 1, και ένα μεμονωμένο μείον ως -1. Παραλείψτε το αυτό και η IMSUM("i","i") παύει να είναι 2i. Η δεύτερη είναι η επιστημονική σημειογραφία (scientific notation) που συγκρούεται με το πρόσημο που χωρίζει το πραγματικό από το φανταστικό μέρος. Ο parser βρίσκει αυτόν τον διαχωριστή σαρώνοντας για ένα συν ή πλην, αλλά ένας αριθμός γραμμένος ως "1.5E-3" περιέχει ένα μείον που ανήκει στον εκθέτη (exponent). Η σάρωση, επομένως, αρνείται να αντιμετωπίσει ένα συν ή μείον ως διαχωριστή όταν ο χαρακτήρας ακριβώς πριν από αυτό είναι e ή E. Χωρίς αυτήν τη δικλείδα ασφαλείας, το πραγματικό μέρος θα κοβόταν στη μέση στο πρόσημο του εκθέτη και η ανάλυση θα αποτύγχανε σε απόλυτα έγκυρη είσοδο
Η ίδια η κατάληξη (suffix) διατηρείται αντί να κανονικοποιείται (normalised). Το Excel δέχεται τόσο το i όσο και το j, και το HotXLS θυμάται ποιο από τα δύο χρησιμοποίησε η είσοδος έτσι ώστε το μορφοποιημένο αποτέλεσμα να φέρει το ίδιο γράμμα. Στη συνέχεια, η μορφοποίηση εφαρμόζει τις συμβατικές συντομεύσεις: ένα φανταστικό μέρος του ένα εκτυπώνεται μόνο ως η κατάληξη, το μείον ένα ως -i, ένα μηδενικό φανταστικό μέρος καταρρέει σε ένα απλό πραγματικό μέρος, και ένα μηδενικό πραγματικό μέρος αφαιρεί το 0+ στην αρχή
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Engineering');
// Negative input: a ten-bit two's complement, places argument ignored.
Sheet.Cells[1, 1].Value := Sheet.Calculate('=DEC2BIN(-5,10)'); // 1111111011
// Complex multiply on two "a+bi" strings.
Sheet.Cells[2, 1].Value := Sheet.Calculate('=IMPRODUCT("3+4i","1+2i")'); // -5+10i
finally
Book.Free;
end;
end;Οι υπερβατικές μιγαδικές συναρτήσεις (transcendental complex functions), όπως οι IMSQRT, IMEXP, IMLN και IMPOWER, δεν λειτουργούν σε ορθογώνιες συντεταγμένες (rectangular coordinates). Μετατρέπουν την αναλυθείσα τιμή σε πολική (polar) μορφή, εφαρμόζουν την πράξη στο μέτρο (modulus) και το όρισμα (argument), και μετατρέπουν ξανά. Η τετραγωνική ρίζα διαιρεί το όρισμα διά δύο και παίρνει τη ρίζα του μέτρου. Μια δύναμη (power) πολλαπλασιάζει το όρισμα και υψώνει το μέτρο. Το να το κάνετε με οποιονδήποτε άλλο τρόπο θα σήμαινε την εκ νέου εξαγωγή (re-deriving) κάθε ταυτότητας σε ορθογώνια μορφή, κάτι που είναι ταυτόχρονα περισσότερος κώδικας και λιγότερο αριθμητικά σταθερό (numerically stable) κοντά στις διακλαδώσεις (branch cuts)
Οι τελεστές bitwise και η υπερχείλιση που πρέπει να ελέγξετε πρώτα
Το Excel 2013 πρόσθεσε τις BITAND, BITOR, BITXOR, BITLSHIFT και BITRSHIFT. Οι τελεστέοι (operands) υπόκεινται σε περιορισμούς: καθένας πρέπει να είναι ένας μη αρνητικός ακέραιος αριθμός, όχι μεγαλύτερος από 2^48 μείον 1, και οποιοδήποτε κλασματικό ή αρνητικό όρισμα αποτελεί αριθμητικό σφάλμα. Αυτό το ανώτατο όριο (cap) είναι αρκετά γενναιόδωρο ώστε να καλύπτει οποιοδήποτε ρεαλιστικό σύνολο σημαιών (flag set) μένοντας παράλληλα πολύ εντός του ακριβώς αναπαραστάσιμου εύρους ενός double, το οποίο έχει σημασία επειδή το Excel παραδίδει κάθε αριθμητικό όρισμα ως τιμή κινητής υποδιαστολής (floating-point value)
Οι συναρτήσεις μετατόπισης (shift) φέρουν τον μόνο κανόνα διάταξης που πραγματικά «δαγκώνει». Μια αριστερή μετατόπιση (left shift) μπορεί να παραγάγει μια τιμή πολύ μεγαλύτερη από την είσοδό της, και εάν εκτελέσετε πρώτα το shl και ελέγξετε το αποτέλεσμα εκ των υστέρων, έχετε ήδη προκαλέσει υπερχείλιση στο Int64 και ο έλεγχος είναι χωρίς νόημα. Ο έλεγχος πρέπει να γίνει πριν τη μετατόπιση. Το HotXLS συγκρίνει τον τελεστέο έναντι της οροφής (ceiling) μετατοπισμένης δεξιά κατά το ποσό μετατόπισης, και μόνο εάν ο τελεστέος χωράει εκτελεί την πραγματική αριστερή μετατόπιση. Ένα μέγεθος μετατόπισης πέρα από τα 53 bit απορρίπτεται κατηγορηματικά, και μια αρνητική μετατόπιση απλώς αντιστρέφει την κατεύθυνση, οπότε η BITLSHIFT με αρνητική μέτρηση (count) συμπεριφέρεται ως δεξιά μετατόπιση. Η αρχή αυτή γενικεύεται πολύ πέρα από αυτήν τη μεμονωμένη συνάρτηση: όταν υπάρχει μια δικλείδα ασφαλείας (guard) για την αποτροπή υπερχείλισης, πρέπει να τρέχει στις εισόδους, ποτέ στο αποτέλεσμα που προοριζόταν να προστατεύσει
// Bitwise calls evaluate the same way through Calculate.
Sheet.Cells[3, 1].Value := Sheet.Calculate('=BITAND(13,11)'); // 9
Sheet.Cells[4, 1].Value := Sheet.Calculate('=BITLSHIFT(5,2)'); // 20
Sheet.Cells[5, 1].Value := Sheet.Calculate('=BITRSHIFT(40,3)'); // 5Μελλοντικές συναρτήσεις και το πρόθεμα _xlfn
Οι τελεστές bitwise και μια μακρά λίστα άλλων προσθηκών μετά το 2007 αλληλεπιδρούν με ένα σχήμα ονοματοδοσίας (naming scheme) που δεν έχει καμία σχέση με το τι υπολογίζουν και έχει άμεση σχέση με το πώς τις αποθηκεύει το Excel. Η αρχική μορφή δυαδικού φύλλου εργασίας εκχωρούσε σε κάθε ενσωματωμένη συνάρτηση μια αριθμητική υποδοχή (numeric slot) σε έναν σταθερό πίνακα (fixed table). Οι συναρτήσεις που εφευρέθηκαν αφότου πάγωσε (frozen) αυτός ο πίνακας δεν έχουν υποδοχή. Για να αποθηκευτεί μια τέτοια συνάρτηση σε ένα αρχείο και ένα σύγχρονο Excel να την αναγνωρίσει, το όνομα γράφεται με το πρόθεμα (prefix) _xlfn., έτσι η BITAND αποθηκεύεται ως _xlfn.BITAND στον δίσκο, παρόλο που ο χρήστης πληκτρολογεί πάντα μόνο BITAND
Η παγίδα (catch) είναι ότι ο κανόνας δεν είναι ομοιόμορφος. Ορισμένες νεότερες συναρτήσεις έλαβαν υποδοχές στον πίνακα και γράφονται γυμνές, ενώ μερικές παλαιού τύπου (legacy) κρυφές συναρτήσεις γράφονται επίσης χωρίς πρόθεμα παρά την ηλικία τους. Το HotXLS διατηρεί μια ρητή λίστα επιτρεπόμενων (whitelist) σχετικά με το ποια ονόματα χρειάζονται το πρόθεμα, το προσθέτει κατά την εγγραφή (write) και το αφαιρεί κατά την ανάγνωση (read), έτσι το κείμενο του τύπου που ορίζετε και διαβάζετε ξανά είναι πάντα το καθαρό όνομα που βλέπει το Excel. Ορίζετε =BITLSHIFT(5,2), το αρχείο κρατάει το _xlfn.BITLSHIFT, και η τιμή επιστρέφει ως 20 ανεξάρτητα από αυτό. Το πρόθεμα είναι μια λεπτομέρεια αποθήκευσης που δεν πρέπει ποτέ να διαρρεύσει στους τύπους με τους οποίους δουλεύετε στον κώδικα
Εφαρμόζοντάς τα σε ένα φύλλο εργασίας
Η δημόσια επιφάνεια (public surface) για όλα αυτά είναι μικρή. Δημιουργήστε ένα TXLSXWorkbook, προσθέστε ένα φύλλο εργασίας (worksheet) και είτε γράψτε έναν τύπο σε ένα κελί μέσω του Cells[Row, Col].Formula και επανυπολογίστε (recalculate), είτε αξιολογήστε μια παράσταση απευθείας με τη μέθοδο Calculate του φύλλου εργασίας, η οποία μεταγλωττίζει τον τύπο (compiles the formula) σε σχέση με αυτό το φύλλο και επιστρέφει ένα Variant. Τα παραπάνω παραδείγματα χρησιμοποιούν τη μέθοδο Calculate επειδή δείχνει το αποτέλεσμα μιας μεμονωμένης κλήσης μηχανικής χωρίς την περιβάλλουσα κατάσταση (state) του φύλλου, αλλά οι ίδιες συναρτήσεις αξιολογούνται με πανομοιότυπο τρόπο μέσα στους τύπους πραγματικών κελιών όταν το βιβλίο εργασίας (workbook) επανυπολογίζεται
Οι κωδικοποιήσεις είναι το μέρος που πρέπει να θυμάστε, όχι τα σημεία κλήσης (call sites). Μια δυαδική συμβολοσειρά έχει πρόσημο (signed) μόνο στα δέκα ψηφία και μόνο αφού περάσει το όριο του μισού της (half threshold) για τη βάση της. Ένας μιγαδικός αριθμός είναι κείμενο, ένας κενός φανταστικός συντελεστής (imaginary coefficient) ισούται με ένα, και ο parser προσπερνά το e ενός εκθέτη (exponent). Μια αριστερή μετατόπιση ελέγχεται πριν κάνει τη μετατόπιση (shift). Αν κατανοήσετε σωστά αυτά τα τέσσερα γεγονότα, η οικογένεια της μηχανικής (engineering family) παύει να είναι πηγή εκπλήξεων από λάθος πρόσημο (off-by-a-sign surprises)
Αν συνδέετε τα δικά σας domain μαθηματικά στην ίδια μηχανή, οι μηχανισμοί καταχώρισης ενός handler και επιστροφής τιμών καλύπτονται στο άρθρο μας για την επέκταση της μηχανής τύπων με προσαρμοσμένες συναρτήσεις (custom functions), και όταν αυτοί οι τύποι πρέπει να φτάσουν σε άλλα φύλλα (cross sheets) με το όνομά τους αντί με διεύθυνση κελιού (cell address), η αναλυτική περιγραφή για τα καθορισμένα ονόματα (defined names) και τους τύπους μεταξύ φύλλων (cross-sheet formulas) δείχνει πώς επιλύονται οι αναφορές. Οι συναρτήσεις μηχανικής που περιγράφονται εδώ διατίθενται ως μέρος του HotXLS spreadsheet component για Delphi και C++Builder, μαζί με τα API ανάγνωσης (reading), εγγραφής (writing) και υπολογισμού που καλύπτονται αλλού σε αυτό το ιστολόγιο