Familia de inginerie din Excel arată ca cel mai ușor colț al referinței de funcții. DEC2BIN transformă un număr într-un șir binar. HEX2DEC îl transformă înapoi. IMSUM adună două numere complexe. Fiecare dintre ele arată ca un exercițiu de formatare. Nu sunt. În spatele acestor nume se ascunde o codificare în completare față de doi pe zece biți pe care majoritatea dezvoltatorilor nu au mai atins-o de la ora de arhitectură a calculatoarelor, un format de număr complex care trăiește în întregime în interiorul șirurilor de caractere (strings), și operatori la nivel de biți (bitwise operators) care vor depăși silențios limita unui întreg pe 64 de biți (overflow) dacă aplici deplasarea (shift) înainte de a verifica. Un motor de foi de calcul care reproduce exact Excel nu poate aproxima/rotunji nimic din toate acestea
Funcțiile se împart în trei grupuri, și fiecare grup ascunde o capcană diferită. Conversia bazei este despre numere negative și praguri per-bază. Aritmetica complexă este despre parsarea și formatarea unui șir de caractere. Operațiunile la nivel de bit sunt despre păstrarea valorilor în limitele Int64. Acest articol parcurge fiecare grup așa cum îl implementează HotXLS, cu apelurile de foaie de calcul (worksheet calls) pe care le-ai scrie de fapt
Conversia bazei și completarea la doi pe zece biți
Direcția înainte este partea la care se așteaptă toată lumea. DEC2BIN(9) dă "1001", și un al doilea argument opțional adaugă zerouri (left-pads) la stânga rezultatului până la o lățime fixă. Capcana o reprezintă datele de intrare (input) negative. Excel nu scrie un semn minus. Codifică valoarea ca pe un șir de completare la doi format din zece cifre în baza țintă, motiv pentru care DEC2BIN(-5,10) returnează "1111111011" mai degrabă decât orice cu un semn. Argumentul pentru poziții este ignorat odată ce valoarea este negativă, deoarece codificarea este deja fixată (pinned) la zece cifre
Zece cifre reprezintă un buget fix, iar acel buget stabilește intervalul reprezentabil per bază. În binar, mărimea (magnitude) care trece în jumătatea negativă este 512, iar modulul de rotație (wrap modulus) este 1024, astfel încât un șir binar poartă semn doar când are exact zece caractere lungime și valoarea sa este de cel puțin 512. Aceeași idee se scalează odată cu baza. Octalul folosește un prag la jumătate (half threshold) de 2^29 și un modul complet de 2^30. Hexazecimalul folosește 2^39 și 2^40. Cititorul HotXLS aplică exact această regulă: acumulează cifrele, și abia când șirul are lățimea de zece caractere iar valoarea acumulată stă la sau deasupra pragului la jumătate scade modulul complet pentru a recupera valoarea cu semn. Un șir de nouă caractere este întotdeauna nenegativ, indiferent de cât de mare este
Codorul (encoder) este imaginea în oglindă. O valoare nenegativă este convertită cifră cu cifră și, opțional, completată cu zerouri la lățimea solicitată, și este respinsă dacă depășește plafonul (ceiling) pozitiv al bazei sau dacă lățimea solicitată este prea îngustă pentru a o conține. O valoare negativă este mai întâi adusă în interval (brought into range) prin adăugarea modulului complet, ceea ce o transformă într-o valoare a cărei reprezentare în bază are întotdeauna zece cifre, iar apoi cifrele sunt emise cu zerouri inițiale (leading zeros) pentru a umple lățimea. Singura verificare de interval partajată (shared range check), limitele simetrice inferioare și superioare per bază, este ceea ce menține DEC2BIN, DEC2OCT și DEC2HEX coerente (consistent) între ele la marginile lor
Mai rămân conversiile între baze, cele precum HEX2BIN și OCT2HEX, care schimbă baza fără a trece prin zecimal (decimal) în numele funcției. Implementarea nu poartă o rutină separată pentru fiecare pereche ordonată. Analizează (parses) șirul de intrare într-o valoare zecimală cu semn folosind baza sursă, apoi formatează acea valoare zecimală în baza destinație. Zecimalul este pivotul. O rutină de analiză și una de formatare, compuse, acoperă fiecare combinație, și deoarece ambele jumătăți partajează (share) aceeași convenție de zece cifre cu semn, o valoare negativă supraviețuiește călătoriei cu semnul său intact
Numerele complexe sunt șiruri, deci munca e la nivel de parsare
Excel nu are un tip de date complex (complex data type). O valoare complexă este șirul (string) "a+bi", și fiecare funcție din familia IM preia acele șiruri și returnează unul. COMPLEX construiește șirul dintr-o parte reală și una imaginară. IMSUM, IMSUB, IMPRODUCT și IMDIV analizează (parse) argumentele lor, fac aritmetica pe părțile numerice și formatează rezultatul înapoi într-un șir. Munca numerică este algebră de nivel de licență (undergraduate algebra). Dificultatea constă în întregime în transformarea textului în două numere în virgulă mobilă (floating-point numbers) în mod fiabil, și acolo își câștigă existența analizorul (parser) intern
Două detalii din acel parser sunt ușor de greșit. Primul este unitatea imaginară goală. Șirul "i" înseamnă unu înmulțit cu i, nu zero și nu o eroare, deci când coeficientul din fața sufixului este gol sau este doar un semn plus, analizorul (parser) trebuie să-l citească drept valoarea 1, și un minus solitar (lone minus) ca -1. Dacă sari peste asta, IMSUM("i","i") încetează să mai fie 2i. Al doilea este notația științifică (scientific notation) ce se ciocnește cu semnul care separă părțile reale de cele imaginare. Parser-ul găsește acel separator prin scanarea unui plus sau minus, dar un număr scris ca "1.5E-3" conține un minus care aparține exponentului. Scanarea refuză prin urmare să trateze un plus sau un minus ca separator când caracterul imediat anterior este e sau E. Fără acel scut (guard), partea reală ar fi sfâșiată în două la semnul exponentului și parsarea ar eșua pe o intrare perfect validă
Sufixul în sine este păstrat, nu normalizat. Excel acceptă atât i cât și j, iar HotXLS reține pe care l-a folosit datele de intrare (input) astfel încât rezultatul formatat poartă aceeași literă. Formatarea aplică apoi scurtăturile convenționale: o parte imaginară cu valoarea 1 se tipărește doar ca sufix, valoarea -1 ca -i, o parte imaginară zero se colapsează la o parte reală simplă, iar o parte reală zero renunță la 0+-ul inițial
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Engineering');
// Intrare negativă: o completare la doi pe zece biți, argumentul locurilor fiind ignorat.
Sheet.Cells[1, 1].Value := Sheet.Calculate('=DEC2BIN(-5,10)'); // 1111111011
// Înmulțire complexă pentru două șiruri "a+bi".
Sheet.Cells[2, 1].Value := Sheet.Calculate('=IMPRODUCT("3+4i","1+2i")'); // -5+10i
finally
Book.Free;
end;
end;Funcțiile complexe transcendentale (transcendental complex functions), printre care IMSQRT, IMEXP, IMLN și IMPOWER, nu lucrează în coordonate rectangulare (rectangular coordinates). Ele convertesc valoarea parsată (parsed value) în formă polară (polar form), aplică operația pe modul și argument, și convertesc înapoi. O rădăcină pătrată (square root) înjumătățește argumentul și extrage rădăcina modulului. O putere înmulțește argumentul și ridică modulul. A proceda altfel ar însemna re-derivarea fiecărei identități în formă rectangulară, ceea ce implică atât mai mult cod, cât și o stabilitate numerică mai redusă în apropierea tăieturilor de ramură (branch cuts)
Operatori pe biți (Bitwise operators) și depășirea capacității (overflow) pe care trebuie s-o verifici prima dată
Excel 2013 a adăugat BITAND, BITOR, BITXOR, BITLSHIFT și BITRSHIFT. Operanzii sunt constrânși: fiecare trebuie să fie un întreg nenegativ nu mai mare de 2^48 minus 1, iar orice argument fracționar sau negativ este o eroare numerică. Acel plafon este suficient de generos pentru a acoperi orice set de steaguri (flag set) realist în timp ce rămâne mult în interiorul intervalului exact reprezentabil (exactly representable range) al unui double, ceea ce contează deoarece Excel predă (hands) fiecare argument numeric sub forma unei valori în virgulă mobilă (floating-point value)
Funcțiile de deplasare (shift functions) poartă singura regulă de ordonare care te va mușca (bites) cu adevărat. O deplasare la stânga (left shift) poate produce o valoare mult mai mare decât intrarea sa, iar dacă efectuezi mai întâi un shl și inspectezi rezultatul după, ai depășit (overflowed) deja Int64, iar testul este lipsit de sens (meaningless). Verificarea trebuie să aibă loc înaintea deplasării. HotXLS compară operandul cu plafonul (ceiling) deplasat la dreapta cu suma de deplasare, și doar dacă operandul se potrivește, efectuează deplasarea la stânga propriu-zisă (actual left shift). O mărime de deplasare (shift magnitude) mai mare de 53 de biți este respinsă din start (rejected outright), iar o deplasare negativă pur și simplu inversează direcția, deci BITLSHIFT cu un contor negativ (negative count) se comportă ca o deplasare la dreapta. Principiul se generalizează cu mult dincolo de această unică funcție: atunci când există o protecție (guard) pentru a preveni depășirea capacității (overflow), ea trebuie să ruleze pe intrări, niciodată pe rezultatul pe care trebuia să-l protejeze
// Apelurile Bitwise (pe biți) se evaluează la fel prin 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)'); // 5Funcțiile viitoare și prefixul de nume _xlfn
Operatorii la nivel de bit (bitwise operators) și o lungă listă de alte adăugiri post-2007 interacționează cu o schemă de denumire (naming scheme) care nu are nimic de a face cu ceea ce calculează și totul de a face cu modul în care Excel le stochează. Formatul binar original al foii de calcul aloca fiecărei funcții predefinite (built-in) un slot numeric într-un tabel fix (fixed table). Funcțiile inventate după ce acel tabel a fost înghețat (frozen) nu au niciun slot. Pentru a salva o astfel de funcție într-un fișier și pentru a o face recunoscută de un Excel modern, numele este scris cu un prefix _xlfn., astfel încât BITAND este stocat pe disc ca _xlfn.BITAND chiar dacă utilizatorul doar tastează (types) vreodată BITAND
Capcana (catch) este că regula nu e uniformă. Unor funcții mai noi le-au fost date sloturi în tabel și sunt scrise simplu (bare), în timp ce câteva funcții ascunse moștenite (legacy hidden functions) sunt de asemenea scrise fără prefix în ciuda vechimii lor. HotXLS menține o listă albă explicită a căror nume necesită prefixul, îl adaugă la scriere (write) și îl îndepărtează la citire (read), astfel încât textul formulei (formula text) pe care îl setezi și îl citești înapoi este întotdeauna numele curat adresat Excel. Tu setezi =BITLSHIFT(5,2), fișierul conține _xlfn.BITLSHIFT, iar valoarea se întoarce ca 20 indiferent de asta. Prefixul este un detaliu de stocare (storage detail) care nu ar trebui niciodată să se scurgă în (leak into) formulele cu care lucrezi în cod
Îmbinând totul într-o foaie de calcul (worksheet)
Suprafața publică (public surface) pentru toate acestea este mică. Creează un TXLSXWorkbook, adaugă o foaie de calcul, și fie scrie o formulă într-o celulă prin Cells[Row, Col].Formula și recalculează, fie evaluează o expresie direct cu metoda Calculate a foii de calcul, care compilează formula în raport cu acea foaie și returnează un Variant. Exemplele de mai sus folosesc Calculate deoarece arată rezultatul unui singur apel de inginerie (engineering call) fără starea înconjurătoare a foii de calcul (surrounding sheet state), dar aceleași funcții se evaluează identic în interiorul formulelor reale de celulă (real cell formulas) atunci când workbook-ul se recalculează
Codificările (encodings) sunt partea ce trebuie reținută (kept in mind), nu locurile de apel (call sites). Un șir binar (binary string) este cu semn doar la zece cifre și doar dincolo de pragul la jumătate pentru baza sa (half threshold for its base). Un număr complex (complex number) este text, un coeficient imaginar gol (empty imaginary coefficient) este unu (one), iar analizorul (parser) sare peste litera e a unui exponent (exponent). O deplasare la stânga (left shift) este verificată înainte de a se deplasa (shift). Păstrând corecte aceste patru fapte, familia de inginerie încetează a mai fi o sursă de surprize greșite cu un semn (off-by-a-sign surprises)
Dacă îți conectezi (wiring) propria matematică de domeniu (domain math) în același motor, mecanismele de înregistrare a unui manipulator (handler) și de returnare a valorilor sunt acoperite în articolul nostru despre extinderea motorului de formule cu funcții personalizate (custom functions), iar atunci când acele formule trebuie să se extindă (reach across) între foi după nume și nu după adresa de celulă (cell address), tutorialul (walkthrough) despre nume definite și formule transversale foilor de calcul (cross-sheet formulas) arată cum se rezolvă referințele. Funcțiile de inginerie descrise aici se livrează ca parte a componentei de foi de calcul HotXLS pentru Delphi și C++Builder, alături de API-urile de citire, scriere, și de calcul abordate în altă parte pe acest blog