Rodina technických (engineering) funkcií v Exceli vyzerá ako tá najjednoduchšia časť prehľadu funkcií. DEC2BIN prevádza číslo na binárny reťazec. HEX2DEC ho prevádza späť. IMSUM sčítava dve komplexné čísla. Každá z nich vyzerá ako cvičenie z formátovania. Nie je to tak. Za týmito názvami sa skrýva desaťbitové kódovanie dvojkového doplnku, ktorého sa väčšina vývojárov nedotkla od prednášok o počítačových architektúrach, formát komplexných čísel, ktorý žije výhradne v reťazcoch, a bitové operátory, ktoré potichu pretečú cez 64-bitové celé číslo, ak vykonáte posun skôr, ako ho skontrolujete. Tabuľkový engine, ktorý presne reprodukuje Excel, nič z toho nemôže zaokrúhliť.
Funkcie sa delia do troch skupín a každá skrýva iné úskalie. Prevod sústav je o záporných číslach a prahoch pre jednotlivé sústavy. Komplexná aritmetika je o analýze a formátovaní reťazca. Bitové operácie sú o zotrvaní v medziach Int64. Tento článok prechádza každú skupinu tak, ako ju implementuje HotXLS, spolu s volaniami v hárku, ktoré by ste reálne napísali.
Prevod sústav a desaťbitový dvojkový doplnok
Priamy prevod je časť, ktorú každý očakáva. DEC2BIN(9) vráti "1001" a voliteľný druhý argument doplní výsledok zľava na pevnú šírku. Pascou je záporný vstup. Excel nepíše znamienko mínus. Kóduje hodnotu ako desaťmiestny reťazec dvojkového doplnku v cieľovej sústave, preto DEC2BIN(-5,10) vráti "1111111011" namiesto čohokoľvek so znamienkom. Argument pre počet miest (places) sa pri zápornej hodnote ignoruje, pretože kódovanie je už pevne stanovené na desať číslic.
Desať číslic je pevný rozpočet a tento rozpočet určuje reprezentovateľný rozsah pre každú sústavu. V binárnej sústave je hodnota, ktorá sa preklápa do zápornej polovice, 512 a modul obalenia je 1024, takže binárny reťazec je so znamienkom iba vtedy, keď má presne desať znakov a jeho hodnota je aspoň 512. Rovnaký princíp platí pre ostatné sústavy. Osmičková sústava používa polovičný prah 2^29 a plný modul 2^30. Šestnástková sústava používa 2^39 and 2^40. Čítačka HotXLS uplatňuje presne toto pravidlo: akumuluje číslice a až keď má reťazec šírku desať znakov a naakumulovaná hodnota je na úrovni polovičného prahu alebo nad ním, odpočíta plný modul, čím získa hodnotu so znamienkom. Deväťznakový reťazec je vždy nezáporný bez ohľadu na veľkosť.
Kodér je zrkadlovým obrazom. Nezáporná hodnota sa prevádza číslicu po číslici a voliteľne sa dopĺňa nulami na požadovanú šírku, pričom sa odmietne, ak pretečie kladný strop danej sústavy alebo ak je požadovaná šírka príliš úzka na jej udržanie. Záporná hodnota sa najprv privedie do rozsahu pripočítaním plného modulu, čo ju zmení na hodnotu, ktorej reprezentácia v danej sústave má vždy desať číslic, a potom sa číslice emitujú s vedúcimi nulami na vyplnenie šírky. Jediná zdieľaná kontrola rozsahu, symetrické spodné a horné hranice pre každú sústavu, je to, čo udržuje DEC2BIN, DEC2OCT a DEC2HEX navzájom konzistentné na ich okrajoch.
To ponecháva prevody medzi rôznymi sústavami, ako napríklad HEX2BIN a OCT2HEX, ktoré menia sústavu bez toho, aby v názve funkcie prešli cez desiatkovú sústavu. Implementácia neobsahuje samostatnú rutinu pre každú usporiadanú dvojicu. Analyzuje vstupný reťazec na desiatkovú hodnotu so znamienkom pomocou zdrojovej sústavy a potom túto desiatkovú hodnotu naformátuje do cieľovej sústavy. Pivotom je desiatková sústava. Jedna rutina analýzy (parse) a jedna formátovania (format), spojené dohromady, pokrývajú každú kombináciu, a kódže obe polovice zdieľajú rovnakú desaťznakovú konvenciu so znamienkom, záporná hodnota prežije cestu so zachovaným znamienkom.
Komplexné čísla sú reťazce, takže práca spočíva v analýze
Excel nemá komplexný dátový typ. Komplexná hodnota je reťazec "a+bi" a každá funkcia z rodiny IM tieto reťazce prijíma a vracia. COMPLEX vytvára reťazec z reálnej a imaginárnej časti. IMSUM, IMSUB, IMPRODUCT a IMDIV analyzujú svoje argumenty, vykonajú aritmetiku na číselných častiach a naformátujú výsledok späť do reťazca. Číselná práca je vysokoškolská algebra. Obtiažnosť spočíva výhradne v spoľahlivom prevode textu na dve čísla s pohyblivou rádovou čiarkou, a práve tu odvádza svoju prácu interný parser.
Dva detaily v tomto parseri sa dajú ľahko pokaziť. Prvým je holá imaginárna jednotka. Reťazec "i" znamená jedenkrát i, nie nula a nie chyba, takže keď je koeficient pred príponou prázdny alebo je to len samotné znamienko plus, parser ho musí prečítať ako hodnotu 1 a samotné mínus ako -1. Vynechajte to a IMSUM("i","i") prestane byť 2i. Druhým je vedecký zápis kolidujúci so znamienkom, ktoré oddeľuje reálnu a imaginárnu časť. Parser nachádza tento oddeľovač vyhľadávaním plusu alebo mínusu, ale číslo zapísané ako "1.5E-3" obsahuje mínus, ktoré patrí exponentu. Vyhľadávanie preto odmieta považovať plus alebo mínus za oddeľovač, keď znak bezprostredne pred ním je e alebo E. Bez tejto ochrany by sa reálna časť roztrhla na polovicu na znamienku exponentu a analýza by zlyhala na dokonca úplne platnom vstupe.
Samotná prípona sa zachováva, namiesto toho, aby sa normalizovala. Excel akceptuje i aj j a HotXLS si pamätá, ktorú z nich vstup použil, takže naformátovaný výsledok nesie rovnaké písmeno. Formátovanie potom uplatňuje konvenčné skratky: imaginárna časť s hodnotou 1 sa vytlačí len ako prípona, mínus jedna ako -i, nulová imaginárna časť sa skolí na obyčajné reálne číslo a nulová reálna časť vynechá vedúce 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;Transcendentné komplexné funkcie, okrem iného IMSQRT, IMEXP, IMLN a IMPOWER, nepracujú v pravouhlých súradniciach. Prevádzajú analyzovanú hodnotu do polárneho tvaru, aplikujú operáciu na modul a argument a prevádzajú ju späť. Odmocnina rozpoľuje argument a berie odmocninu modulu. Mocnina násobí argument a umocňuje modul. Akýkoľvek iný postup by znamenal opätovné odvodzovanie každej identity v pravouhlom tvare, čo predstavuje viac kódu a menšiu numerickú stabilitu v blízkosti rezov vetiev.
Bitové operátory a pretečenie, ktoré musíte skontrolovať ako prvé
Excel 2013 pridal funkcie BITAND, BITOR, BITXOR, BITLSHIFT a BITRSHIFT. Operandy sú obmedzené: každý musí byť nezáporné celé číslo nie väčšie ako 2^48 mínus 1 a akýkoľvek desatinný alebo záporný argument je číselnou chybou. Tento strop je dostatočne veľkorysý na to, aby pokryl akúkoľvek reálnu sadu príznakov, pričom zostáva hlboko v presne reprezentovateľnom rozsahu typu double, na čom záleží, pretože Excel odovzdáva každý číselný argument ako hodnotu s pohyblivou rádovou čiarkou.
Funkcie posunu nesú jedno pravidlo usporiadania, ktoré môže skutočne uškodiť. Posun doľava môže vygenerovať hodnotu oveľa väčšiu ako jej vstup, a ak najprv vykonáte shl a výsledok skontrolujete až potom, už ste pretiekli cez Int64 and test nemá zmysel. Kontrola musí prísť pred posunom. HotXLS porovnáva operand s horným limitom posunutým doprava o veľkosť posunu a len vtedy, ak sa operand zmestí, vykoná skutočný posun doľava. Veľkosť posunu nad 53 bitov je úplne odmietnutá a záporný posun jednoducho obráti smer, takže BITLSHIFT so záporným počtom sa správa ako posun doprava. Tento princíp sa zovšeobecňuje ďaleko za túto jednu funkciu: keď existuje ochrana na zabránenie pretečeniu, musí bežať na vstupoch, nikdy nie na výsledku, ktorý mala chrániť.
// 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)'); // 5Budúce funkcie a predpona názvu _xlfn
Bitové operátory a dlhý zoznam ďalších prírastkov po roku 2007 interagujú so schémou pomenovania, ktorá nemá nič spoločné s tým, čo počítajú, ale súvisí výlučne s tým, ako ich Excel ukladá. Pôvodný binárny formát hárka priraďoval každej vstavanej funkcii číselný slot v pevnej tabuľke. Funkcie vymyslené po zmrazení tejto tabuľky nemajú žiadny slot. Ak chcete takúto funkciu uložiť do súboru a dosiahnuť, aby jui moderný Excel rozpoznal, názov sa zapisuje s predponou _xlfn., takže BITAND sa na disku ukladá ako _xlfn.BITAND, aj keď používateľ píše iba BITAND.
Háčik je v tom, že toto pravidlo nie je jednotné. Niektoré novšie funkcie dostali sloty v tabuľke a zapisujú sa bez predpony, zatiaľ čo niekoľko starších skrytých funkcií sa tiež zapisuje bez predpony napriek ich veku. HotXLS udržiava explicitný biely zoznam názvov, ktoré predponu potrebujú, pridáva ju pri zápise a odstraňuje pri čítaní, takže text vzorca, ktorý nastavíte a prečítate späť, je vždy čistým názvom smerujúcim k Excelu. Nastavíte =BITLSHIFT(5,2), súbor obsahuje _xlfn.BITLSHIFT a hodnota sa vráti ako 20 bez ohľadu na to. Predpona je detailom úložiska, ktorý by nikdy nemal preniknúť do vzorcov, s ktorými pracujete v kóde.
Spojenie v pracovnom hárku
Verejný povrch pre toto všetko je malý. Vytvorte TXLSXWorkbook, pridajte hárok a buď zapíšte vzorec do bunky cez Cells[Row, Col].Formula a znova prepočítajte, alebo vyhodnoťte výraz priamo pomocou metódy Calculate hárka, ktorá skompiluje vzorec voči danému hárku a vráti Variant. Vyššie uvedené príklady používajú Calculate, pretože ukazuje výsledok jedného technického volania bez okolitého stavu hárka, ale rovnaké funkcie sa vyhodnocujú identicky vo vnútri reálnych vzorcov buniek, keď sa prepočítava celý zošit.
Kódovania sú časťou, ktorú treba mať na pamäti, nie miesta volaní. Binárny reťazec je so znamienkom iba pri desiatich čísliciach a iba po prekročení polovičného prahu pre danú sústavu. Komplexné číslo je text, prázdny imaginárny koeficient je jedna a parser preskakuje e exponentu. Posun doľava sa kontroluje pred jeho vykonaním. Správne pochopenie týchto štyroch faktov zabráni tomu, aby rodina technických funkcií bola zdrojom prekvapení so znamienkami.
Ak prepájate vlastnú matematiku domény do rovnakého enginu, mechanizmus registrácie handlera a vrátenia hodnôt je popísaný v našom článku o rozšírení vzorcového enginu o vlastné funkcie, a keď tieto vzorce musia siahať naprieč hárkami podľa názvu a nie podľa adresy bunky, sprievodca definovanými názvami a vzorcami naprieč hárkami ukazuje, ako sa referencie riešia. Technické funkcie popísané tu sa dodávajú ako súčasť tabuľkového komponentu HotXLS spreadsheet component pre Delphi and C++Builder spolu s rozhraniami API na čítanie, zápis a výpočty, o ktorých sa píše inde na tomto blogu.