Familia de funcții inginerești din Excel pare a fi cel mai simplu 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 acestea pare un simplu exercițiu de formatare. Nu este așa. În spatele acestor nume se află o codificare a complementului față de doi pe zece biți pe care majoritatea dezvoltatorilor nu au mai atins-o de la cursul de arhitectură a calculatoarelor, un format de numere complexe care trăiește în întregime în interiorul șirurilor de caractere și operatori pe biți care vor depăși în mod silențios un întreg pe 64 de biți dacă faceți deplasarea (shift) înainte de a verifica. Un motor de foi de calcul care reproduce exact Excel nu poate omite nimic din toate acestea.
Funcțiile se împart în trei grupuri și fiecare grup ascunde o capcană diferită. Conversia de baze se referă la numere negative și praguri specifice fiecărei baze. Aritmetica complexă se referă la parsarea și formatarea unui șir. Operațiunile pe biți se referă la menținerea în limitele Int64. Acest articol parcurge fiecare grup așa cum îl implementează HotXLS, împreună cu apelurile de foi de calcul pe care le-ați scrie de fapt.
Conversia de baze și complementul față de doi pe zece biți
Direcția înainte este partea pe care o așteaptă toată lumea. DEC2BIN(9) returnează "1001", iar un al doilea argument opțional completează rezultatul la stânga până la o lățime fixă. Capcana este intrarea negativă. Excel nu scrie un semn minus. Codifică valoarea ca un șir de complement față de doi de zece cifre în baza țintă, motiv pentru care DEC2BIN(-5,10) returnează "1111111011" în loc de ceva cu semn. Argumentul pentru poziții este ignorat odată ce valoarea este negativă, deoarece codificarea este deja fixată la zece cifre.
Zece cifre reprezintă un buget fix, iar acel buget stabilește intervalul reprezentabil per bază. În binar, magnitudinea care trece în jumătatea negativă este 512, iar modulul de înfășurare este 1024, astfel încât un șir binar are semn numai când are exact zece caractere lungime și valoarea sa este de cel puțin 512. Aceeași idee se extinde cu baza. Octalul folosește un prag de jumătate 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, numai când șirul are zece caractere lățime și valoarea acumulată se află la sau peste pragul de jumătate, scade modulul complet pentru a recupera valoarea cu semn. Un șir de nouă caractere este întotdeauna nenegativ, indiferent cât de mare este.
Codificatorul este imaginea în oglindă. O valoare nenegativă este convertită cifră cu cifră și completată opțional cu zerouri la lățimea solicitată și este respinsă dacă depășește limita superioară pozitivă a bazei sau dacă lățimea solicitată este prea îngustă pentru a o conține. O valoare negativă este adusă mai întâi în interval prin adăugarea modulului complet, ceea ce o transformă într-o valoare a cărei reprezentare în bază este întotdeauna de zece cifre, iar apoi cifrele sunt emise cu zerouri la început pentru a umple lățimea. Singura verificare comună a intervalului, limitele inferioare și superioare simetrice per bază, este ceea ce menține DEC2BIN, DEC2OCT și DEC2HEX coerente între ele la limitele lor.
Asta lasă conversiile între baze diferite, cele cum ar fi HEX2BIN și OCT2HEX care schimbă baza fără a trece prin zecimal în numele funcției. Implementarea nu conține o rutină separată pentru fiecare pereche ordonată. Aceasta parsează șirul de intrare într-o valoare zecimală cu semn folosind baza sursă, apoi formatează acea valoare zecimală în baza destinație. Zecimala este pivotul. O singură rutină de parsare și o singură rutină de formatare, compuse, acoperă fiecare combinatie, și deoarece ambele jumătăți împart aceeași convenție cu semn pe zece cifre, o valoare negativă supraviețuiește călătoriei cu semnul intact.
Numerele complexe sunt șiruri, deci munca constă în parsare
Excel nu are un tip de date complex. O valoare complexă este șirul "a+bi" și fiecare funcție din familia IM primește acele șiruri și returnează unul. COMPLEX construiește șirul dintr-o parte reală și una imaginară. IMSUM, IMSUB, IMPRODUCT și IMDIV parsează argumentele, efectuează aritmetica pe părțile numerice și formatează rezultatul înapoi într-un șir. Munca numerică este algebră de facultate. Dificultatea constă în întregime în transformarea textului în două numere în virgulă mobilă în mod fiabil și acolo își dovedește utilitatea parserul intern.
Două detalii din acel parser sunt ușor de greșit. Primul este unitatea imaginară simplă. Șirul "i" înseamnă unu înmulțit cu i, nu zero și nu o eroare, așa că atunci când coeficientul din fața sufixului este gol sau este un simplu semn plus, parserul trebuie să îl citească ca fiind valoarea 1, iar un simplu minus ca -1. Omiteți acest lucru și IMSUM("i","i") nu mai este 2i. Al doilea este notația științifică care se ciocnește cu semnul care separă părțile reale și imaginare. Parserul găsește acel separator scanând după plus sau minus, dar un număr scris ca "1.5E-3" conține un minus care aparține exponentului. Prin urmare, scanarea refuză să trateze un plus sau minus ca separator atunci când caracterul imediat dinaintea lui este e sau E. Fără această protecție, partea reală ar fi împărțită la semnul exponentului și parsarea ar eșua pe o intrare perfect validă.
Sufixul în sine este păstrat, mai degrabă decât normalizat. Excel aceeaptă atât i, cât și j, iar HotXLS își amintește pe care l-a folosit intrarea, astfel încât rezultatul formatat poartă aceeași literă. Formatarea aplică apoi prescurtările convenționale: o parte imaginară de unu se tipărește doar ca sufix, minus unu ca -i, o parte imaginară zero se reduce la un simplu real, iar o parte reală zero elimină prefixul 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;Funcțiile complexe transcendentale, printre care IMSQRT, IMEXP, IMLN și IMPOWER, nu funcționează în coordonate rectangulare. Ele convertesc valoarea parsat în formă polară, aplică operația pe modul și argument și convertesc înapoi. O rădăcină pătrată înjumătățește argumentul și extrage rădăcina modulului. O putere înmulțește argumentul și ridică modulul la putere. Procedarea în orice alt mod ar însemna re-derivarea fiecărei identități în formă rectangulară, ceea ce înseamnă atât mai mult cod, cât și o stabilitate numerică mai redusă în apropierea punctelor de ramificare.
Operatorii pe biți și depășirea pe care trebuie să o verificați mai întâi
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ă. Limita este suficient de generoasă pentru a acoperi orice set realist de flag-uri, rămânând în același timp bine în intervalul reprezentabil exact al unui double, ceea ce contează deoarece Excel transmite fiecare argument numeric ca o valoare în virgulă mobilă.
Funcțiile de deplasare (shift) poartă acea regulă de ordonare care pune cu adevărat probleme. O deplasare la stânga poate produce o valoare mult mai mare decât intrarea sa și, dacă efectuați shl mai întâi și inspectați rezultatul după aceea, ați depășit deja Int64, iar testul este lipsit de sens. Verificarea trebuie să vină înainte de deplasare. HotXLS compară operandul cu limita superioară deplasată la dreapta cu valoarea deplasării și, numai dacă operandul se încadrează, efectuează deplasarea efectivă la stânga. O magnitudine de deplasare dincolo de 53 de biți este respinsă direct, iar o deplasare negativă pur și simplu inversează direcția, astfel încât BITLSHIFT cu un număr negativ se comportă ca o deplasare la dreapta. Principiul se generalizează mult dincolo de această singură funcție: atunci când există o protecție pentru a preveni depășirea, aceasta trebuie să ruleze pe intrări, niciodată pe rezultatul pe care trebuia să îl protejeze.
// 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)'); // 5Funcțiile viitoare și prefixul de nume _xlfn
Operatorii pe biți și o listă lungă de alte adăugiri post-2007 interacționează cu o schemă de denumire care nu are nicio legătură cu ceea ce calculează și are legătură în întregime cu modul în care Excel le stochează. Formatul original binar al foii de calcul asocia fiecărei funcții încorporate un slot numeric într-un tabel fix. Funcțiile inventate după ce acel tabel a fost înghețat nu au niciun slot. Pentru a salva o astfel de funcție într-un fișier și pentru a fi recunoscută de un Excel modern, numele este scris cu un prefix _xlfn., astfel încât BITAND este stocat ca _xlfn.BITAND pe disc, chiar dacă utilizatorul tastează doar BITAND.
Problema este că regula nu este uniformă. Unele funcții mai noi au primit sloturi de tabel și sunt scrise simple, în timp ce câteva funcții ascunse moștenite sunt scrise fără prefix în ciuda vechimii lor. HotXLS păstrează o listă albă explicită a numelor care au nevoie de prefix, îl adăuga la scriere și îl elimină la citire, astfel încât textul formulei pe care îl setați și îl citiți înapoi este întotdeauna numele curat orientat către Excel. Setați =BITLSHIFT(5,2), fișierul conține _xlfn.BITLSHIFT, iar valoarea se întoarce ca 20 indiferent. Prefixul este un detaliu de stocare care nu ar trebui să ajungă niciodată în formulele cu care lucrați în cod.
Punerea cap la cap într-o foaie de calcul
Interfața publică pentru toate acestea este redusă. Creați un TXLSXWorkbook, adăugați o foaie de calcul și fie scrieți o formulă într-o celulă prin Cells[Row, Col].Formula și recalculați, fie evaluați 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 ingineresc fără starea foii înconjurătoare, mas aceleași funcții se evaluează identic în interiorul formulelor reale de celule atunci când registrul de calcul se recalculează.
Codificările sunt partea de reținut, nu locurile de apel. Un șir binar are semn numai la zece cifre și numai dincolo de pragul de jumătate pentru baza sa. Un număr complex este text, un coeficient imaginar gol este unu, iar parserul trece peste e-ul unui exponent. O deplasare la stânga este verificată înainte de a se efectua. Respectați aceste patru fapte și familia de funcții inginerești încetează să mai fie o sursă de surprize legate de semn.
Dacă vă conectați propria matematică de domeniu în același motor, mecanismul de înregistrare a unui handler și returnarea valorilor sunt acoperite în articolul nostru despre extinderea motorului de formule cu funcții personalizate, iar când acele formule trebuie să acceseze foi diferite prin nume în loc de adresa celulei, ghidul despre nume definite și formule între foi arată cum se rezolvă referințele. Funcțiile inginerești descrise aici sunt livrate ca parte a componentei de foi de calcul HotXLS pentru Delphi și C++Builder, alături de API-urile de citire, scriere și calcul acoperite în alte părți ale acestui blog.