Obitelj inženjerskih funkcija u Excelu izgleda kao najjednostavniji kut referentnog priručnika funkcija. DEC2BIN pretvara broj u binarni niz znakova. HEX2DEC ga vraća natrag. IMSUM zbraja dva kompleksna broja. Svaka od njih izgleda kao vježba oblikovanja. No, nisu. Iza ovih naziva nalazi se desetbitno kodiranje komplementa dvojke koje većina programera nije dotaknula od predavanja o arhitekturi računala, format kompleksnog broja koji u potpunosti živi unutar tekstualnih nizova, te bitovni operatori koji će tiho preliti 64-bitni cijeli broj ako pomaknete bitove prije nego što provjerite. A pokretač proračunskih tablica koji točno reproducira Excel ne može ništa od toga zaobići.
Funkcije se dijele u tri skupine i svaka skupina skriva drugačiju zamku. Pretvorba baza odnosi se na negativne brojeve i pragove po bazi. Kompleksna aritmetika odnosi se na parsiranje i oblikovanje niza znakova. Bitovne operacije odnose se na ostanak unutar granica Int64. Ovaj članak prolazi kroz svaku skupinu kako ih HotXLS implementira, s pozivima proračunske tablice koje biste stvarno napisali.
Pretvorba baza i desetbitni komplement dvojke
Smjer prema naprijed je dio koji svi očekuju. DEC2BIN(9) daje "1001", a neobavezni drugi argument popunjava rezultat slijeva do fiksne širine. Zamka je negativan unos. Excel ne piše znak minus. On kodira vrijednost kao desetoznamenkasti niz komplementa dvojke u ciljnoj bazi, zbog čega DEC2BIN(-5,10) vraća "1111111011" umjesto bilo čega sa znakom. Argument mjesta (places) se zanemaruje č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 omotavanja je 1024, pa binarni niz ima predznak samo kada je dug točno deset znakova i njegova vrijednost je najmanje 512. Ista ideja se skalira s bazom. Oktalni sustav koristi polovični prag od 2^29 i puni modul od 2^30. Heksadecimalni koristi 2^39 i 2^40. HotXLS čitač primjenjuje točno ovo pravilo: akumulira znamenke, i samo kada je niz širok deset znakova i akumulirana vrijednost je na ili iznad polovičnog praga, oduzima puni modul kako bi povratio predznačenu vrijednost. Niz od devet znakova uvijek je nenegativan, bez obzira na to koliko je velik.
Kodera je slika u ogledalu. Nenegativna vrijednost se pretvara znamenku po znamenku i opcionalno popunjava nulama do tražene širine, a odbija se ako prelije pozitivni strop baze ili ako je tražena širina preuska da bi je primila. Negativna vrijednost se najprije dovodi u raspon dodavanjem punog modula, što je pretvara u vrijednost čija je reprezentacija u bazi uvijek deset znamenki, a zatim se znamenke emitiraju s vodećim nulama kako bi ispunile širinu. Jedinstvena zajednička provjera raspona, simetrične donje i gornje granice po bazi, ono je što održava funkcije DEC2BIN, DEC2OCT i DEC2HEX međusobno konzistentnima na njihovim granicama.
To ostavlja konverzije između različitih baza, one poput HEX2BIN i OCT2HEX koje mijenjaju bazu bez prolaska kroz decimalni sustav u nazivu funkcije. Implementacija ne sadrži zasebnu rutinu za svaki uređeni par. Ona analizira ulazni niz znakova u predznačenu decimalnu vrijednost koristeći izvornu bazu, a zatim oblikuje tu decimalnu vrijednost u odredišnu bazu. Decimalni sustav je stožer. Jedna rutina za parsiranje i jedna rutina za oblikovanje, sastavljene zajedno, pokrivaju svaku kombinaciju, a budući da obje polovice dijele istu desetoznamenkastu konvenciju s predznakom, negativna vrijednost preživljava putovanje sa svojim predznakom netaknutim.
Kompleksni brojevi su nizovi znakova, pa je rad zapravo parsiranje
Excel nema kompleksni tip podataka. Kompleksna vrijednost je niz znakova "a+bi", i svaka funkcija u obitelji IM prima te nizove i vraća jedan natrag. COMPLEX gradi niz znakova iz realnog i imaginarnog dijela. IMSUM, IMSUB, IMPRODUCT i IMDIV parsiraju svoje argumente, obavljaju aritmetiku na numeričkim dijelovima i oblikuju rezultat natrag u niz znakova. Numerički rad je dodiplomska algebra. Poteškoća je u potpunosti u pouzdanom pretvaranju teksta u dva broja s pomičnim zarezom, i tu unutarnji parser opravdava svoje postojanje.
Dva detalja u tom parseru lako je pogriješiti. Prvi je gola imaginarna jedinica. Niz znakova "i" znači jedan puta i, a ne nula i nije pogreška, pa kada je koeficijent ispred sufiksa prazan ili je usamljeni znak plus, parser ga mora pročitati kao vrijednost 1, a usamljeni minus kao -1. Preskočite to i IMSUM("i","i") prestaje biti 2i. Drugi detalj je znanstveni zapis koji se sudara sa znakom koji odvaja realni i imaginarni dio. Parser pronalazi taj separator skeniranjem za plus ili minus, ali broj napisan kao "1.5E-3" sadrži minus koji pripada eksponentu. Skeniranje stoga odbija tretirati plus ili minus kao separator kada je znak neposredno ispred njega e ili E. Bez te zaštite, realni dio bi se prepolovio na znaku eksponenta i parsiranje ne bi uspjelo na savršeno valjanom unosu.
Sam sufiks se čuva umjesto da se normalizira. Excel prihvaća i i i j, a HotXLS pamti koji je od njih ulaz koristio kako bi oblikovani rezultat nosio isto slovo. Oblikovanje zatim primjenjuje uobičajene skraćenice: imaginarni dio od jedan ispisuje se samo kao sufiks, minus jedan kao -i, nulti imaginarni dio sažima se u običan realni dio, a nulti realni dio odbacuje vodeće 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;Transcedentne kompleksne funkcije, među kojima su IMSQRT, IMEXP, IMLN i IMPOWER, ne rade u pravokutnim koordinatama. One pretvaraju analiziranu vrijednost u polarni oblik, primjenjuju operaciju na modul i argument te pretvaraju natrag. Kvadratni korijen polovi argument i uzima korijen modula. Potencija množi argument i potencira modul. Činjenje toga na bilo koji drugi način značilo bi ponovno izvođenje svakog identiteta u pravokutnom obliku, što je i više koda i manje numerički stabilno u blizini rezova grana.
Bitovni operatori i preljev koji morate provjeriti prvi
Excel 2013 je dodao 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 je numerička pogreška. Ta je granica dovoljno velikodušna da pokrije bilo koji realni skup zastavica, dok ostaje unutar raspona točno predstavljivog dvostrukom preciznošću (double), što je važno jer Excel prenosi svaki numerički argument kao vrijednost s pomičnim zarezom.
Funkcije pomaka nose jedno pravilo redoslijeda koje doista stvara probleme. Pomak ulijevo može proizvesti vrijednost daleko veću od njezina ulaza, a ako najprije izvršite shl i nakon toga pregledate rezultat, već ste prelili Int64 and the test is meaningless. Provjera mora doći prije pomaka. HotXLS uspoređuje operand sa stropom pomaknutim udesno za iznos pomaka, i samo ako operand odgovara, on izvodi stvarni pomak ulijevo. Magnituda pomaka veća od 53 bita odbija se odmah, a negativni pomak jednostavno mijenja smjer, pa se BITLSHIFT s negativnim brojem ponaša kao pomak udesno. Načelo se uopćava daleko izvan ove jedne funkcije: kada zaštita postoji kako bi se spriječio preljev, ona mora raditi na ulazima, a nikada na rezultatu koji je trebala zaštititi.
// 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)'); // 5Buduće funkcije i prefiks naziva _xlfn
Bitovni operatori i dugačak popis drugih dodataka nakon 2007. stupaju u interakciju sa shemom imenovanja koja nema nikakve veze s onim što oni izračunavaju, već isključivo s načinom na koji ih Excel pohranjuje. Izvorni binarni format proračunske tablice dodijelio je svakoj ugrađenoj funkciji numerički utor u fiksnoj tablici. Funkcije izumljene nakon što je ta tablica zamrznuta nemaju utor. Kako bi se takva funkcija spremila u datoteku i kako bi je moderni Excel prepoznao, naziv se piše s prefiksom _xlfn., pa se BITAND pohranjuje kao _xlfn.BITAND na disku iako korisnik uvijek upisuje samo BITAND.
Kvaka je u tome što pravilo nije jedinstveno. Neke novije funkcije dobile su utore u tablici i pišu se gole, dok se nekoliko starih skrivenih funkcija također piše bez prefiksa unatoč njihovoj starosti. HotXLS održava eksplicitni bijeli popis naziva koji trebaju prefiks, dodaje ga pri pisanju i uklanja pri čitanju, tako da je tekst formule koji ste postavili i pročitali natrag uvijek čisti naziv okrenut Excelu. Postavite =BITLSHIFT(5,2), datoteka drži _xlfn.BITLSHIFT, a vrijednost se vraća kao 20 bez obzira na to. Prefiks je detalj pohrane koji nikada ne bi trebao procuriti u formule s kojima radite u kodu.
Sastavljanje svega u proračunsku tablicu
Javna površina za sve ovo je mala. Stvorite TXLSXWorkbook, dodajte proračunsku tablicu i ili upišite formulu u ćeliju putem Cells[Row, Col].Formula i ponovno izračunajte, ili procijenite izraz izravno pomoću metode proračunske tablice Calculate, koja prevodi formulu za tu tablicu i vraća Variant. Gornji primjeri koriste Calculate jer prikazuju rezultat jednog inženjerskog poziva bez okolnog stanja tablice, bi iste funkcije se procjenjuju identično unutar stvarnih formula ćelija kada se knjiga ponovno izračuna.
Kodiranja su dio koji treba imati na umu, a ne mjesta poziva. Binarni niz znakova ima predznak samo pri deset znamenki i samo preko polovičnog praga za svoju bazu. Kompleksni broj je tekst, prazan imaginarni koeficijent je jedan, a parser preskače e eksponenta. Pomak ulijevo se provjerava prije nego što se pomakne. Shvatite te četiri činjenice ispravno i inženjerska obitelj prestaje biti izvor iznenađenja s pogrešnim predznakom.
Ako povezujete vlastitu matematiku domene u isti motor, mehanika registracije rukovatelja (handler) i vraćanja vrijednosti pokrivena je u našem članku o proširenju motora formula prilagođenim funkcijama, a kada te formule moraju dosezati preko listova po nazivu umjesto po adresi ćelije, vodič kroz definirane nazive i formule između listova pokazuje kako se reference razješuju. Inženjerske funkcije opisane ovdje isporučuju se kao dio proračunske komponente HotXLS za Delphi i C++Builder, uz API-je za čitanje, pisanje i izračunavanje pokrivene drugdje na ovom blogu.