Družina inženirskih funkcij v Excelu se bere kot najlažji kotiček reference funkcij. DEC2BIN spremeni število v binarni niz (binary string). HEX2DEC ga spremeni nazaj. IMSUM sešteje dve kompleksni števili. Vsaka od njih izgleda kot vaja iz formatiranja. Vendar niso. Za temi imeni se skriva deset-bitno dvojiško komplementarno kodiranje (ten-bit two's complement encoding), ki se ga večina razvijalcev ni dotaknila od predavanj o računalniški arhitekturi (computer-architecture), format kompleksnih števil, ki živi v celoti znotraj nizov, in bitni operatorji, ki bodo tiho povzročili prekoračitev (overflow) 64-bitnega celega števila, če izvedete premik (shift), preden to preverite. Pogon preglednice (spreadsheet engine), ki natančno reproducira Excel, ne more ničesar od tega zaokrožiti (round off)
Funkcije se delijo v tri skupine in vsaka skupina skriva drugačno past. Pretvorba osnov (base conversion) je povezana z negativnimi števili in pragovi za vsako osnovo. Kompleksna aritmetika se ukvarja z razčlenjevanjem (parsing) in formatiranjem niza. Bitne operacije pomenijo ostati znotraj meja tipa Int64. Ta članek obravnava vsako skupino, kot jo izvaja (implements) HotXLS, z vključenimi klici na delovnem listu, ki bi jih dejansko napisali
Pretvorba osnov in deset-bitni dvojiški komplement
Smer naprej je tisti del, ki ga vsi pričakujejo. DEC2BIN(9) vrne "1001" in neobvezni drugi argument izvede dodajanje na levi strani (left-pads), da je rezultat fiksne širine. Past pa je negativen vhodni podatek (negative input). Excel ne zapiše znaka minus. Vrednost zakodira kot desetmestni dvojiški komplementni niz v ciljni osnovi, zaradi česar funkcija DEC2BIN(-5,10) vrne "1111111011" in ne nečesa z znakom. Argument z mesti je prezrt takoj, ko je vrednost negativna, ker je kodiranje že pripeto (pinned) na deset mest
Deset mest pomeni fiksni proračun in ta proračun določa razpon, ki ga je mogoče predstaviti (representable range) za posamezno osnovo. Pri binarnem zapisu je vrednost, pri kateri se vse skupaj prevesi v negativno polovico, 512, modul ovijanja (wrap modulus) pa je 1024, tako da je binarni niz predznačen (signed) samo, ko je dolg natanko deset znakov in je njegova vrednost vsaj 512. Ista ideja se stopnjuje sorazmerno z osnovo. Oktalni zapis uporablja polovični prag vrednosti 2^29 in polni modul 2^30. Heksadecimalni uporablja 2^39 in 2^40. Bralnik orodja HotXLS (HotXLS reader) uporablja natanko to pravilo: zbira števke in šele ko je niz širok deset znakov in se zbrana vrednost nahaja na ali nad polovičnim pragom, odšteje polni modul, da s tem pridobi nazaj predznačeno (signed) vrednost. Devetznakovni niz ni nikoli negativen, ne glede na to, kako velik je
Koder (encoder) je zrcalna slika. Nenegativna vrednost se pretvori števk za števko (digit by digit) in neobvezno dopolni z ničlami (zero-padded) do zahtevane širine. Koder jo zavrne, če povzroči prekoračitev bazičnega pozitivnega stropa (base's positive ceiling) ali v primeru, če je zahtevana širina preozka, da bi lahko vsebovala to vrednost. Negativna vrednost se najprej spravi v domet z dodajanjem polnega modula, kar jo pretvori v vrednost, katere bazična reprezentacija (base representation) je vedno deset mest, nato pa se te števke prikažejo (emitted) z vodilnimi ničlami (leading zeros), da zapolnijo širino. Edino deljeno preverjanje razpona (single shared range check), se pravi simetrična spodnja in zgornja meja po posamezni osnovi, je tisto, kar ohranja DEC2BIN, DEC2OCT in DEC2HEX med seboj usklajene na njihovih robovih
Tako ostanejo tiste pretvorbe med osnovami (cross-base conversions), kot sta na primer HEX2BIN in OCT2HEX, ki zamenjajo osnovo (change base), ne da bi pri tem šle preko "decimal" v samem imenu funkcije. Implementacija nima ločene rutine za vsak urejen par. Prebere vhodni niz (input string) v označeno desetiško vrednost (signed decimal value) z uporabo izvorne osnove (source base) in to desetiško vrednost nato formatira v ciljno osnovo (destination base). Desetiški sistem je vrtišče (pivot). Ena sama rutina za branje in ena sama rutina za formatiranje, ko sta združeni, pokrijeta vsako kombinacijo, in ker obe polovici delita isto desetmestno konvencijo o označenosti, negativna vrednost preživi to pot, ne da bi izgubila svoj predznak (sign intact)
Kompleksna števila so nizi, zato je delo razčlenjevanje (parsing)
Excel nima kompleksnega podatkovnega tipa (complex data type). Kompleksna vrednost je niz "a+bi" in vsaka funkcija iz družine IM vzame te nize ter enega vrne nazaj. COMPLEX sestavi niz (string) iz realnega in imaginarnega dela. IMSUM, IMSUB, IMPRODUCT in IMDIV preberejo (parse) svoje argumente, opravijo aritmetične izračune z numeričnimi deli in nato rezultat spet formatirajo nazaj v obliko niza. Samo računanje s številkami je srednješolska algebra. Celotna težava je le v tem, da je besedilo (text) potrebno zanesljivo spremeniti v dve števili s plavajočo vejico (floating-point numbers), pri čemer notranji bralnik (internal parser) upraviči svoj namen
Dve podrobnosti pri tem razčlenjevalniku (parser) zlahka spregledamo. Prva je gola imaginarna enota (bare imaginary unit). Niz "i" pomeni ena krat i, ne nič in ne napaka (error), zato ko je koeficient pred pripono (suffix) prazen ali je samo en znak plus (lone plus sign), ga mora razčlenjevalnik obravnavati kot vrednost 1, izoliran minus pa kot -1. Če to izpustimo, izraz IMSUM("i","i") neha biti enak 2i. Druga podrobnost pa je znanstveni zapis, ki trči s tistim znakom, kateri razdeli in ločuje realni ter imaginarni del (separates the real and imaginary parts). Bralnik (parser) namreč poišče ta ločevalnik z iskanjem po znakih plus oziroma minus, vendar pa število, zapisano kot "1.5E-3", vključuje tisti znak za minus, ki se drži eksponenta. Tisto skeniranje zato odkloni (refuses to treat) branje takega znaka plus ali minus v smislu ločevalnika tedaj, kadar tisti znak leži točno za znakom za e ali E. Brez tiste zaščite bi se realni del (real part) razpolovil točno pri eksponentnem znaku, in bralni proces (parse) pri tako sicer popolnoma urejenem in veljavnem vnosu ne bi zmogel uspeti
Pripona (suffix) sama po sebi ostane ohranjena namesto (rather than) normalizirana. Excel namreč akceptira (accepts) tako i kot j, pri programu HotXLS pa si to celotno proceduro in tisti uporabljen znak sam zapomni, zatorej potem tudi tisti in s tem oblikovani izpis ali formatirani (formatted result) prenaša to popolnoma isto črko na koncu (carries the same letter). Postopek formata ali (formatting) namreč samo uporabi okrajšave (conventional shorthands): imaginarni element enke oziroma preprosta enka pri izpisu (prints) prilepi zgolj pripono (just the suffix), z znakom in samo z tisto enko ali kot pri (minus one) kot minusom -i, ob vrednosti same ničle se pri imaginarnem tistem podre ali sesede do gole oblike in osnove realnega in golega the plain ali samo gole the zero the kot na in (collapses to a plain real), nula realne komponente oziroma the zero (a zero real part) nato odbaci znak vodilnega ali pri tistem začetku na prvem drop the (drops the leading) s preprostim in čistim tistim 0+ na the v in the za to the in to in to ob the v or na
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;Transcendentalne kompleksne funkcije, med njimi IMSQRT, IMEXP, IMLN in IMPOWER, ne delujejo v pravokotnih koordinatah. Razčlenjeno vrednost pretvorijo v polarno obliko, izvedejo operacijo na modulu in argumentu ter jo pretvorijo nazaj. Kvadratni koren razpolovi argument in izračuna koren iz modula. Potenciranje pomnoži argument in dvigne modul. Če bi to storili na kakršen koli drug način, bi to pomenilo ponovno izpeljavo vsake identitete v pravokotni obliki, kar pomeni več kode in je manj numerično stabilno v bližini rezov vej (branch cuts)
Bitni operatorji in prekoračitev, ki jo morate najprej preveriti
Excel 2013 je dodal BITAND, BITOR, BITXOR, BITLSHIFT in BITRSHIFT. Operandi so omejeni: vsak mora biti nenegativno celo število, ki ni večje od 2^48 minus 1, in vsak delni ali negativen argument pomeni numerično napako. Ta zgornja meja je dovolj radodarna, da pokrije kateri koli realističen nabor zastavic, hkrati pa ostane močno znotraj razpona, ki ga je mogoče natančno predstaviti z dvojno natančnostjo (double), kar je pomembno, ker Excel preda vsak numerični argument kot vrednost s plavajočo vejico
Funkcije za premik (shift functions) vsebujejo tisto eno pravilo o vrstnem redu, ki dejansko predstavlja past. Levi premik lahko proizvede vrednost, ki je veliko večja od njegovega vnosa, in če najprej izvedete shl in nato preverite rezultat, ste že prekoračili (overflowed) Int64 in preverjanje je nesmiselno. Preverjanje se mora zgoditi pred premikom. HotXLS primerja operand z zgornjo mejo (ceiling), premaknjeno v desno za vrednost premika, in samo če se operand prilega, izvede dejanski levi premik. Velikost premika nad 53 bitov se takoj zavrne, negativen premik pa preprosto obrne smer, zato se BITLSHIFT z negativnim štetjem vede kot desni premik. Načelo je veliko bolj splošno od zgolj te ene funkcije: kadar obstaja varovalo (guard) za preprečevanje prekoračitve, se mora zagnati na vhodnih podatkih, nikoli na rezultatu, ki naj bi ga zaščitilo
// 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)'); // 5Prihodnje funkcije in predpona imena _xlfn
Bitni operatorji in dolg seznam drugih dodatkov po letu 2007 so povezani s poimenovanjem, ki nima nobene zveze s tem, kaj izračunajo, in ima vso zvezo s tem, kako jih Excel shrani. Prvotni format binarnega delovnega lista (binary worksheet format) je vsaki vgrajeni funkciji dodelil numerično režo v fiksni tabeli. Funkcije, ki so bile izumljene po tem, ko je bila ta tabela zamrznjena, nimajo reže. Če želite takšno funkcijo shraniti v datoteko, da bi jo sodoben Excel prepoznal, je njeno ime napisano s predpono _xlfn., tako da je BITAND shranjena kot _xlfn.BITAND na disku, čeprav uporabnik vedno vnese zgolj BITAND
Zanka je v tem, da pravilo ni enotno. Nekatere novejše funkcije so dobile reže v tabeli in se pišejo same, medtem ko se tudi nekaj starih skritih (legacy hidden) funkcij, kljub njihovi starosti, piše brez predpone. HotXLS vodi ekspliciten seznam (whitelist), katera imena potrebujejo predpono, jo doda pri pisanju in odstrani pri branju, tako da je besedilo formule, ki jo nastavite in preberete nazaj, vedno tisto čisto ime, ki ga uporablja Excel. Nastavite =BITLSHIFT(5,2), datoteka vsebuje _xlfn.BITLSHIFT, in vrednost se ne glede na to vrne kot 20. Predpona je zgolj podrobnost glede shranjevanja in nikoli ne bi smela pricurljati (leak) v formule, s katerimi delate v svoji kodi
Združevanje vsega skupaj na delovnem listu
Javna površina za vse to je majhna. Ustvarite TXLSXWorkbook, dodajte delovni list in nato ali zapišite formulo v celico prek Cells[Row, Col].Formula ter ponovno izračunajte ali pa ovrednotite izraz neposredno z metodo Calculate tistega delovnega lista, ki prevede formulo glede na ta list in vrne Variant. Zgornji primeri uporabljajo Calculate, ker prikazuje rezultat posameznega inženirskega klica brez stanja okoliškega lista, vendar se te iste funkcije identično ovrednotijo tudi znotraj pravih celic v formulah ob ponovnem izračunu samega delovnega zvezka (workbook)
Kodiranje (encodings) je tisto, kar si je treba zapomniti, in ne klicna mesta (call sites). Binarni niz je predznačen šele ob desetih znakih in samo čez polovični prag njegove osnove. Kompleksno število je besedilo, prazen imaginarni koeficient pomeni ena in razčlenjevalnik preskoči (steps over) znak e pri eksponentu. Levi premik se preveri pred izvedbo premika. Če imate ta štiri dejstva urejena in jih dobro poznate, potem inženirska družina (engineering family) preneha biti vir neprijetnih presenečenj, kjer se vrednost za en sam predznak zgreši (off-by-a-sign surprises)
Če v isti pogon povezujete lastno domensko matematiko, so mehanizmi registracije obravnavalnika (handler) in vračanja vrednosti zajeti v našem članku o razširitvi pogona formul z lastnimi funkcijami po meri (custom functions), in kadar morajo tiste formule posegati po drugih listih z imenom namesto z naslovom celice, vodnik po določenih imenih in formulah med listi (cross-sheet formulas) prikazuje, kako se reference razrešijo. Tu opisane inženirske funkcije so na voljo kot del komponente HotXLS spreadsheet component za Delphi in C++Builder, skupaj z API-ji za branje, pisanje in izračunavanje, ki so opisani drugje na tem spletnem dnevniku (blog)