Inženjerska obitelj u Excelu čita se kao najlakši kutak referentnog priručnika funkcija. DEC2BIN pretvara broj u binarni niz (string). HEX2DEC ga pretvara natrag. IMSUM zbraja dva kompleksna broja. Svaka od njih izgleda kao vježba formatiranja. Ali nije tako. Iza ovih imena krije se desetobitno kodiranje s dvojnim komplementom (two's complement) kojeg većina programera nije dotakla od nastave arhitekture računala, format kompleksnog broja koji u potpunosti živi unutar nizova, i operatori po bitovima (bitwise) koji će tiho preplaviti (overflow) 64-bitni cijeli broj ako izvedete pomak (shift) prije provjere. Mehanizam proračunske tablice koji točno reproducira Excel ne može ništa od ovoga zaokružiti
Funkcije se dijele u tri grupe, a svaka grupa skriva drugačiju zamku. Konverzija baza bavi se negativnim brojevima i pragovima po bazama. Kompleksna aritmetika bavi se parsiranjem i formatiranjem niza. Operacije po bitovima bave se ostajanjem unutar granica Int64. Ovaj članak prolazi kroz svaku grupu onako kako ih HotXLS implementira, sa stvarnim pozivima u radnom listu (worksheet) koje biste vi napisali
Konverzija baza i desetobitni dvojni komplement
Smjer prema naprijed je onaj dio koji svi očekuju. DEC2BIN(9) daje "1001", a neobavezni drugi argument ispunjava (pads) rezultat nulama s lijeve strane na fiksnu širinu. Zamka je negativan ulaz. Excel ne piše znak minus. On kodira vrijednost kao desetoznamenkasti niz u dvojnom komplementu u ciljanoj bazi, što je razlog zašto DEC2BIN(-5,10) vraća "1111111011" umjesto nečega sa znakom. Argument za broj mjesta zanemaruje se čim je vrijednost negativna, jer je kodiranje već fiksirano na deset znamenki
Deset znamenki je fiksni budžet, a taj budžet postavlja raspon koji se može prikazati po bazi. U binarnom sustavu veličina koja prelazi u negativnu polovicu je 512, a modul omatanja (wrap modulus) je 1024, tako da je binarni niz predznačen samo kada je dug točno deset znakova i njegova je vrijednost najmanje 512. Ista ideja rasteže se zajedno s bazom. Oktalni sustav koristi polovični prag od 2^29 i puni modul od 2^30. Heksadecimalni sustav koristi 2^39 i 2^40. Čitač HotXLS-a primjenjuje točno ovo pravilo: on akumulira znamenke, i samo kada je niz širok deset znakova, a akumulirana vrijednost leži na ili iznad polovičnog praga, on oduzima puni modul kako bi vratio vrijednost sa znakom. Niz od devet znakova uvijek je nenegativan, bez obzira na to koliko je velik
Enkoder je slika u zrcalu. Nenegativna vrijednost pretvara se znamenku po znamenku i opcionalno se ispunjava nulama do zatražene širine, a bit će odbačena ako premaši pozitivni plafon baze ili ako je zatražena širina preuska da je zadrži. Negativna vrijednost prvo se dovodi u raspon dodavanjem punog modula, što je pretvara u vrijednost čiji je prikaz u bazi uvijek deset znamenki, a zatim se znamenke emitiraju s vodećim nulama kako bi ispunile širinu. Jedna jedina dijeljena provjera raspona, simetrične donje i gornje granice po bazi, ono je što drži DEC2BIN, DEC2OCT i DEC2HEX dosljednima jedna drugoj na njihovim rubovima
To ostavlja unakrsne konverzije baza, one poput HEX2BIN i OCT2HEX koje mijenjaju bazu bez prolaska kroz decimalni sustav u nazivu funkcije. Implementacija ne nosi zasebnu rutinu za svaki uređeni par. Ona parsira ulazni niz u dekadsku vrijednost sa znakom koristeći izvornu bazu, a zatim formatira tu dekadsku vrijednost u ciljanu bazu. Decimalni sustav je točka okreta (pivot). Jedna rutina za parsiranje i jedna rutina za formatiranje, povezane u kompoziciju, pokrivaju svaku kombinaciju, a budući da obje polovice dijele istu konvenciju desetoznamenkastog prikaza sa znakom, negativna vrijednost preživljava putovanje sa sačuvanim znakom
Kompleksni brojevi su nizovi znakova, pa je posao u parsiranju
Excel nema podatkovni tip za kompleksne brojeve. Kompleksna vrijednost je niz znakova "a+bi", i svaka funkcija u IM obitelji prima takve nizove i vraća jedan natrag. COMPLEX gradi niz od realnog i imaginarnog dijela. IMSUM, IMSUB, IMPRODUCT i IMDIV parsiraju svoje argumente, obavljaju aritmetiku nad numeričkim dijelovima i formatiraju rezultat natrag u niz znakova. Numerički posao je algebra preddiplomskog nivoa. Poteškoća je isključivo u tome da se tekst pouzdano pretvori u dva broja s pomičnim zarezom (floating-point), i tu interni parser zarađuje za život
Dva detalja u tom parseru lako je pogrešno napisati. Prvi je ogoljena imaginarna jedinica. Niz znakova "i" znači jedan puta i, ne nula i ne greška, tako da kada je koeficijent ispred sufiksa prazan ili je to usamljeni znak plus, parser to mora pročitati kao vrijednost 1, a usamljeni minus kao -1. Preskočite to i IMSUM("i","i") prestaje biti 2i. Drugi detalj je znanstvena notacija koja se sudara sa znakom koji odvaja realni i imaginarni dio. Parser pronalazi taj separator skenirajući plus ili minus, ali broj zapisan kao "1.5E-3" sadrži minus koji pripada eksponentu. Stoga skeniranje odbija tretirati plus ili minus kao separator kada je znak neposredno ispred njega e ili E. Bez tog osiguranja realni bi dio bio prepolovljen na znaku eksponenta i parsiranje bi propalo na potpuno ispravnom ulazu
Sam sufiks se čuva umjesto da se normalizira. Excel prihvaća i i i j, a HotXLS pamti koji je od njih ulaz koristio tako da formatirani rezultat nosi isto slovo. Formatiranje tada primjenjuje konvencionalne skraćenice: imaginarni dio od jedan ispisuje se kao samo sufiks, minus jedan kao -i, nulti imaginarni dio kolapsira na čisti realni broj, a nulti realni dio ispušta vodeći dio 0+
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Engineering');
// Negativan ulaz: desetobitni dvojni komplement, argument mjesta ignoriran.
Sheet.Cells[1, 1].Value := Sheet.Calculate('=DEC2BIN(-5,10)'); // 1111111011
// Kompleksno množenje nad dva niza "a+bi".
Sheet.Cells[2, 1].Value := Sheet.Calculate('=IMPRODUCT("3+4i","1+2i")'); // -5+10i
finally
Book.Free;
end;
end;Transcendentalne kompleksne funkcije, IMSQRT, IMEXP, IMLN i IMPOWER među njima, ne rade u pravokutnim koordinatama. One pretvaraju parsiranu vrijednost u polarni oblik, primjenjuju operaciju na modul (modulus) i argument, te ih pretvaraju natrag. Kvadratni korijen prepolovljuje argument i uzima korijen iz modula. Potenciranje množi argument i potencira modul. Rad na bilo koji drugi način značio bi ponovno izvođenje svakog identiteta u pravokutnom obliku, što je i više koda i manje numerički stabilno u blizini granskih rezova (branch cuts)
Operatori po bitovima i preljev (overflow) koji prvo morate provjeriti
Excel 2013 dodao je BITAND, BITOR, BITXOR, BITLSHIFT i BITRSHIFT. Operandi su ograničeni: svaki mora biti nenegativan cijeli broj ne veći od 2^48 minus 1, a svaki razlomljeni ili negativni argument rezultira numeričkom greškom. Ta gornja granica dovoljno je velikodušna da pokrije bilo koji realan skup zastavica (flags) istovremeno ostajući debelo unutar točno reprezentativnog raspona dvostruke preciznosti (double), što je bitno jer Excel predaje svaki numerički argument kao vrijednost s pomičnim zarezom
Funkcije pomaka (shift) nose jedno jedino pravilo redoslijeda koje doista grize. Pomak ulijevo (left shift) može proizvesti vrijednost daleko veću od njenog ulaza, a ako prvo izvedete operaciju shl i provjerite rezultat nakon toga, već ste preplavili Int64 i test je besmislen. Provjera mora uslijediti prije pomaka. HotXLS uspoređuje operand s gornjom granicom pomaknutom udesno za količinu pomaka, i tek ako operand stane, izvodi stvarni pomak ulijevo. Magnituda pomaka veća od 53 bita izravno se odbija, a negativni pomak jednostavno obrće smjer, pa se BITLSHIFT s negativnim brojačem ponaša kao pomak udesno. Princip se generalizira daleko izvan ove jedne funkcije: kada postoji osiguranje za sprječavanje preljeva, mora se pokrenuti na ulazima, nikada na rezultatu kojeg je trebalo zaštititi
// Pozivi po bitovima ocjenjuju se na isti način kroz 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)'); // 5Buduće funkcije i _xlfn prefiks u imenu
Operatori po bitovima i dugačak popis ostalih dodataka nakon 2007. komuniciraju sa shemom imenovanja koja nema nikakve veze s onim što oni računaju, i ima svake veze s time kako ih Excel pohranjuje. Izvorni binarni format radnog lista dodijelio je svakoj ugrađenoj funkciji numerički utor (slot) u fiksnoj tablici. Funkcije izmišljene nakon što je ta tablica zamrznuta nemaju utor. Kako bi se takva funkcija spremila u datoteku i da ju prepozna moderan Excel, ime se upisuje s prefiksom _xlfn., pa se BITAND pohranjuje kao _xlfn.BITAND na disk iako korisnik uvijek upisuje samo BITAND
Kvaka je u tome da pravilo nije ujednačeno. Neke novije funkcije dobile su utore u tablici i upisuju se bez prefiksa, dok se nekoliko zastarjelih skrivenih funkcija također upisuje bez prefiksa unatoč svojoj starosti. HotXLS vodi eksplicitnu bijelu listu onih imena kojima je potreban prefiks, dodaje ga prilikom pisanja i skida ga prilikom čitanja, tako da je tekst formule kojeg postavljate i čitate uvijek čisto Excelovo ime. Postavite =BITLSHIFT(5,2), datoteka čuva _xlfn.BITLSHIFT, a vrijednost se bez obzira vraća kao 20. Prefiks je detalj pohrane koji nikada ne bi smio iscuriti u formule s kojima radite u kodu
Sastavljanje svega u radnom listu
Javna površina (public surface) za sve ovo je malena. Stvorite TXLSXWorkbook, dodajte radni list, te ili upišite formulu u ćeliju preko Cells[Row, Col].Formula i preračunajte je, ili ocijenite (evaluate) izraz izravno s metodom Calculate na radnom listu, koja prevodi (compiles) formulu u odnosu na taj list i vraća tip Variant. Gornji primjeri koriste Calculate jer on prikazuje rezultat jednog poziva iz inženjerske funkcije bez okolnog stanja radnog lista, no te se iste funkcije identično ocjenjuju unutar pravih formula ćelija kada se radna knjiga ponovno preračunava
Kodiranja (encodings) su dio koji treba imati na umu, a ne mjesta na kojima se nalaze pozivi. Binarni niz dobiva predznak tek na deset znamenki i to tek nakon prelaska polovice praga za njegovu bazu. Kompleksni broj je tekst, prazan imaginarni koeficijent je jedan, a parser preskače e kod eksponenta. Pomak ulijevo provjerava se prije samog pomaka. Shvatite ove četiri činjenice točno i inženjerska obitelj funkcija više neće biti izvor iznenađenja proizašlih iz greške u predznaku
Ako povezujete svoju vlastitu domensku matematiku u taj isti mehanizam, mehanika registracije rutine za obradu i vraćanje vrijednosti objašnjena je u našem članku o proširenju mehanizma za formule s prilagođenim funkcijama, a kada te formule moraju dosegnuti druge listove prema imenu, a ne prema adresi ćelije, vodič o definiranim imenima i formulama za referenciranje između listova pokazuje kako se te reference rješavaju. Ovdje opisane inženjerske funkcije isporučuju se kao dio HotXLS komponente za proračunske tablice namijenjene Delphiju i C++Builderu, zajedno s API-jima za čitanje, pisanje i izračunavanje koji su obrađeni na drugim mjestima na ovom blogu