Rodina inžinierskych funkcií v Exceli pôsobí ako tá najľahšia časť referenčnej príručky funkcií. DEC2BIN zmení číslo na binárny reťazec. HEX2DEC ho zmení naspäť. IMSUM sčíta dve komplexné čísla. Každé z nich vyzerá ako cvičenie na formátovanie. Nie je to tak. Za týmito názvami sa skrýva desaťbitové kódovanie v dvojkovom doplnku, s ktorým väčšina vývojárov neprišla do styku od hodín architektúry počítačov; formát komplexných čísel, ktorý existuje výhradne vnútri reťazcov; a bitové operátory, ktoré potichu spôsobia pretečenie 64-bitového celého čísla, ak posuniete pred kontrolou. Tabuľkový procesor, ktorý verne reprodukuje Excel, nemôže nič z tohto zaokrúhliť
Funkcie sa delia do troch skupín a každá skupina v sebe skrýva inú pascu. Konverzia základov súvisí so zápornými číslami a prahmi pre jednotlivé základy. Komplexná aritmetika sa týka analýzy a formátovania reťazcov. Bitové operácie sú o udržaní sa v medziach Int64. Tento článok prejde každú skupinu tak, ako ju implementuje HotXLS, s volaniami pracovného hárka, ktoré by ste reálne napísali
Konverzia základov a desaťbitový dvojkový doplnok
Smer vpred je tá časť, ktorú každý očakáva. DEC2BIN(9) vráti "1001" a nepovinný druhý argument doplní výsledok zľava na pevnú šírku. Pascou sú záporné vstupy. Excel nezapisuje znamienko mínus. Zakóduje hodnotu ako desaťmiestny reťazec dvojkového doplnku v cieľovom základe, čo je dôvod, prečo DEC2BIN(-5,10) vráti "1111111011" namiesto čohokoľvek so znamienkom. Argument pre počet miest sa po zadaní zápornej hodnoty ignoruje, pretože kódovanie je už zafixované na desať číslic
Desať číslic je pevný rozpočet a tento rozpočet nastavuje reprezentovateľný rozsah pre každý základ. V binárnej sústave je veľkosť, pri ktorej dochádza k preklopeniu do zápornej polovice, 512, a modul zavinutia je 1024. Z tohto dôvodu je binárny reťazec so znamienkom iba vtedy, ak je dlhý presne desať znakov a jeho hodnota je aspoň 512. Rovnaká myšlienka sa prispôsobuje základu. Osmičková sústava používa polovičný prah 2^29 a plný modul 2^30. Šestnástková sústava používa 2^39 a 2^40. Čítačka v HotXLS aplikuje presne toto pravidlo: akumuluje číslice a až keď je reťazec široký desať znakov a akumulovaná hodnota leží na alebo nad polovičným prahom, odpočíta plný modul pre získanie hodnoty so znamienkom. Deväťznakový reťazec je vždy nezáporný bez ohľadu na to, aký je veľký
Kódovač je zrkadlovým obrazom. Nezáporná hodnota je konvertovaná číslica po číslici a voliteľne doplnená nulami na požadovanú šírku, pričom je odmietnutá, ak pretečie cez kladný strop základu alebo ak je požadovaná šírka príliš úzka na to, aby ju pojala. Záporná hodnota je najprv prispôsobená do povoleného rozsahu pripočítaním plného modulu, čo ju premení na hodnotu, ktorej reprezentácia v príslušnom základe je vždy desať číslic. Až potom sú emitované číslice s úvodnými nulami, ktoré vyplnia stanovenú šírku. Táto jedna zdieľaná kontrola rozsahu (symetrické spodné a horné hranice podľa základne) je to, čo udržiava konzistentnosť funkcií DEC2BIN, DEC2OCT a DEC2HEX na ich okrajoch
Zostávajú ešte konverzie medzi základmi navzájom – tie ako HEX2BIN a OCT2HEX, ktoré menia základ bez toho, aby v názve funkcie prešli cez decimálnu sústavu. Implementácia neobsahuje osobitnú rutinu pre každú usporiadanú dvojicu. Analyzuje vstupný reťazec na decimálnu hodnotu so znamienkom s využitím zdrojového základu a následne túto decimálnu hodnotu formátuje do cieľového základu. Desiatková sústava (decimal) je stredobodom. Jedna parsovacia rutina a jedna formátovacia rutina, navzájom prepojené, pokrývajú každú kombináciu. A keďže obe polovice zdieľajú rovnakú dohodu s desaťmiestnym znamienkom, záporná hodnota prežije cestu so zachovaným znamienkom
Komplexné čísla sú reťazce, takže podstata práce je vo formátovaní a parsovaní
Excel nemá žiadny komplexný dátový typ. Komplexná hodnota je reťazec "a+bi" a každá funkcia z rodiny IM tieto reťazce prijíma a odovzdáva späť. COMPLEX vytvára reťazec z reálnej a imaginárnej časti. IMSUM, IMSUB, IMPRODUCT a IMDIV parsujú svoje argumenty, vykonávajú aritmetiku nad číselnými časťami a výsledok naformátujú späť do reťazca. Tá číselná časť práce je vysokoškolská algebra. Celá náročnosť spočíva v spoľahlivej transformácii textu na dve čísla s plávajúcou desatinnou čiarkou (floating-point), a práve v tomto smere si interný parser musí obhájiť svoje miesto
Dva detaily v tomto parseri sa dajú ľahko pokaziť. Prvým je holá imaginárna jednotka. Reťazec "i" znamená jednotku krát i, nie nulu a nie chybu, takže keď je koeficient pred príponou prázdny alebo obsahuje osamotený znak plus, parser ho musí vyhodnotiť ako hodnotu 1 a osamotený znak mínus ako -1. Pokiaľ to vynecháte, z IMSUM("i","i") prestane byť 2i. Druhým detailom je vedecká notácia v kolízii so znamienkom, ktoré oddeľuje reálnu a imaginárnu časť. Parser nájde tento oddeľovač vyhľadávaním plusu alebo mínusu, avšak číslo zapísané ako "1.5E-3" obsahuje mínus, ktoré patrí exponentu. Vyhľadávanie preto musí odmietnuť považovať plus alebo mínus za oddeľovač v prípade, že znak bezprostredne pred ním je e alebo E. Bez tejto ochrany by bola reálna časť rozdelená na polovicu pri znamienku exponentu a parsovanie by zlyhalo na dokonale platnom vstupe
Samotná prípona je radšej zachovaná namiesto toho, aby sa normalizovala. Excel akceptuje i aj j a HotXLS si pamätá, ktorú z nich použil vstup, aby naformátovaný výsledok obsahoval to isté písmeno. Formátovanie potom aplikuje bežné skratky: imaginárna časť jedna sa vytlačí ako samotná prípona, mínus jedna ako -i, nulová imaginárna časť sa zredukuje len na čistú reálnu hodnotu a nulová reálna časť zahodí počiatočné 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;Trascendentné komplexné funkcie, medzi ktorými sú aj IMSQRT, IMEXP, IMLN a IMPOWER, nefungujú v pravouhlých súradniciach. Konvertujú parsovanú hodnotu do polárneho tvaru, aplikujú operáciu na modul a argument a následne ju konvertujú späť. Odmocnina rozdelí argument na polovicu a vezme odmocninu z modulu. Umocnenie vynásobí argument a umocní modul. Urobiť to inak by znamenalo znova odvodiť každú identitu v pravouhlom tvare, čo je jednak viac kódu a jednak menšia numerická stabilita v blízkosti rezu vetvy (branch cuts)
Bitové operátory a pretečenie, ktoré si musíte najprv skontrolovať
Excel 2013 pridal BITAND, BITOR, BITXOR, BITLSHIFT a BITRSHIFT. Operandy sú obmedzené: každý musí byť nezáporným celým číslom menším alebo rovným ako 2^48 mínus 1 a akýkoľvek desatinný alebo záporný argument vedie k numerickej chybe. Tento strop je dostatočne štedrý na to, aby pokryl akúkoľvek reálnu zostavu vlajok, no zároveň si udržal dostatočnú rezervu pred prekročením presne reprezentovateľného rozsahu čísla s plávajúcou rádovou čiarkou s dvojitou presnosťou (double). Na tom záleží, pretože Excel posúva každý číselný argument ako hodnotu s plávajúcou desatinnou čiarkou
Funkcie na posun nesú jedno pravidlo usporiadania, ktoré v praxi robí reálne problémy. Posun doľava môže vytvoriť hodnotu, ktorá je oveľa väčšia ako jej vstup. Pokiaľ najskôr vykonáte shl a výsledok preskúmate až neskôr, už ste pretekli cez hodnotu Int64 a daný test nemá žiadny význam. Kontrola musí predchádzať posunu. HotXLS porovnáva operand so stropom, ktorý je posunutý vpravo o veľkosť posunu a skutočný posun vľavo vykoná len vtedy, keď operand do tohto rozpätia spadá. Veľkosť posunu presahujúca 53 bitov je okamžite zamietnutá a záporný posun jednoducho obráti smer, takže BITLSHIFT so záporným počtom sa správa ako posun doprava. Princíp je vo svojej podstate uplatniteľný aj mimo tejto jedinej funkcie: ak je prítomná ochrana zabraňujúca pretečeniu, musí byť vykonaná na vstupoch, nikdy nie na výsledku, ktorý mala ochrá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ídavkov z obdobia po roku 2007 interagujú so schémou pomenovania, ktorá nemá nič spoločné s tým, čo vypočítajú, a naopak má všetko spoločné s tým, ako si ich Excel ukladá. Pôvodný binárny formát pracovného hárka pridelil každej zabudovanej funkcii číselný priestor v rámci pevnej tabuľky. Funkcie vymyslené po zmrazení tejto tabuľky nemajú pridelený takýto priestor. Aby bolo možné takúto funkciu uložiť do súboru a zabezpečiť jej rozpoznanie moderným Excelom, názov je zapísaný s predponou _xlfn., takže BITAND je na disku uložená ako _xlfn.BITAND aj napriek tomu, že používateľ iba vždy zadá BITAND
Háčik je v tom, že pravidlo nie je jednotné. Niektorým novším funkciám boli pridelené miesta v tabuľke a sú zapisované priamo bez predpony. Súčasne existuje niekoľko starších skrytých funkcií, ktoré sa aj napriek ich veku zapisujú bez akejkoľvek predpony. HotXLS udržiava explicitný zoznam povolených výrazov (whitelist) určujúci to, ktoré názvy potrebujú predponu, ktorú načíta a následne vymaže. Z toho vyplýva, že text vzorca, ktorý nastavíte a následne spätne prečítate, je zakaždým len ten čistý názov smerujúci k Excelu. Pokiaľ nastavíte =BITLSHIFT(5,2), súbor vo svojom vnútri obsahuje _xlfn.BITLSHIFT, a príslušná hodnota sa aj tak vráti ako 20. Predpona predstavuje záležitosť spojenú s uskladnením a nikdy by nemala unikať k vzorcom, v rámci ktorých s kódom narábate vy
Zostavenie do pracovného hárka
Verejná plocha je z tohto celkového hľadiska malá. Vytvorte TXLSXWorkbook, pridajte pracovný hárok a buď napíšte vzorec do bunky prostredníctvom Cells[Row, Col].Formula a nechajte vykonať opätovný prepočet, alebo vyhodnoťte priamo výraz prostredníctvom metódy Calculate aplikovanej na pracovný hárok, ktorá preloží vzorec zohľadňujúc príslušný hárok a vráti hodnotu typu Variant. Vyššie spomenuté príklady využívajú Calculate, lebo daná metóda demonštruje výsledok osamoteného inžinierskeho volania bez štátneho zázemia v kontexte hárka, ale identické funkcie vyhodnocujú rovnako aj v skutočných vzorcoch vložených v bunkách po prepočítaní zošita
Kódovania sú tou zložkou, ktorú je nutné mať stále na zreteli a nie samotné miesta vyvolania. Binárny reťazec je vybavený znamienkom len vtedy, keď má desať číslic a iba v prípadoch za polovičným prahom prislúchajúcim svojej báze. Komplexné číslo je prítomné vo formáte textu, prázdny imaginárny koeficient sa spája s číslom jeden a analyzátor pri spracúvaní prekračuje e pochádzajúce z exponentu. Posunúť operáciu smerom vľavo si vyžaduje previerku a kontrolu ešte pred zahájením zmeny. Osvojte si tieto štyri princípy a rodina s inžinierskymi funkciami sa stane pre vás štandardom bez jedinej prítomnej nepredvídateľnej výnimky pochádzajúcej zo znamienka navyše
Ak si programujete vlastnú prepojenú doménu v identickom prostredí, podrobnosti implementácie zaregistrovania príslušného prvku s riadiacou zložkou a vrátenia vyžiadaných výpočtových hodnôt sme už preberali v našom článku o rozšírení vzorcového stroja o vlastné funkcie. A zatiaľ čo predchádzajúce usmernenie analyzovalo a vysvetľovalo prekrývanie pracovných hárkov menami s vynechaním samotnej adresy príslušnej bunky, tak postup v rámci uverejnených definovania mien a presahov medzi rôznymi listami nám detailnejšie naznačuje funkčnosť referenčnej štruktúry. Tento uverejnený materiál pojednávajúci o inžinierskych funkciách je vo svojom celkovom znení a fungovaní distribuovaný rovnako aj so spreadsheat komponentom HotXLS pre rozhranie zamerané na Delphi a C++Builder. Jeho ďalšie prepojenia na základe samotného zapisovania, prenosu a samotného hodnotenia s využitím zobrazeného API boli zdokumentované rovnako aj v kontexte s naším blogom