Rodina technických funkcí v Excelu vypadá jako nejsnadnější kout referenční příručky funkcí. DEC2BIN převádí číslo na binární řetězec. HEX2DEC jej převádí zpět. IMSUM sčítá dvě komplexní čísla. Každá z nich vypadá jako pouhé cvičení ve formátování. Nejsou jím. Za těmito názvy se skrývá desetibitové kódování ve dvojkovém doplňku, kterého se většina vývojářů nedotkla od přednášek o architektuře počítačů, formát komplexních čísel, který žije výhradně uvnitř řetězců, a bitové operátory, které tiše přetečou 64bitové celé číslo, pokud provedete posun dříve, než provedete kontrolu. Tabulkový engine, který přesně reprodukuje Excel, nemůže nic z toho zjednodušit.
Funkce se dělí do tří skupin a každá skupina skrývá jinou past. Převod číselných soustav je o záporných číslech a prahových hodnotách pro jednotlivé soustavy. Komplexní aritmetika je o parsování a formátování řetězce. Bitové operace jsou o setrvání v mezích typu Int64. Tento článek provází každou skupinou tak, jak ji HotXLS implementuje, s voláními v listu, která byte skutečně napsali.
Převod číselných soustav a desetibitový dvojkový doplněk
Přímý směr je ten, který každý očekává. DEC2BIN(9) dává "1001" a volitelný druhý argument doplní výsledek zleva na pevnou šířku. Pastí je záporný vstup. Excel nepíše znaménko mínus. Kóduje hodnotu jako desetimístný řetězec ve dvojkovém doplňku v cílové soustavě, což je důvod, proč DEC2BIN(-5,10) vrací "1111111011" namísto čehokoli se znaménkem. Argument určující počet míst je ignorován, jakmile je hodnota záporná, protože kódování je již pevně stanoveno na deset číslic.
Deset číslic je pevný rozpočet a tento rozpočet určuje rozsah, který lze reprezentovat v každé soustavě. V binární soustavě je hodnota, která se překlápí do záporné poloviny, 512 a modulo cyklu je 1024, takže binární řetězec je považován za záporný pouze tehdy, když je dlouhý přesně deset znaků a jeho hodnota je alespoň 512. Stejný princip se mění se soustavou. Osmičková používá poloviční práh 2^29 a plné modulo 2^30. Šestnáctková používá 2^39 a 2^40. Čtečka HotXLS uplatňuje přesně toto pravidlo: akumuluje číslice, a pouze když má řetězec délku deseti znaků a akumulovaná hodnota je na polovičním prahu nebo nad ním, odečte plné modulo, aby získala zápornou hodnotu. Devítimístný řetězec je vždy nezáporný bez ohledu na velikost.
Kodér je zrcadlovým obrazem. Nezáporná hodnota se převádí číslici po číslici a volitelně doplňuje nulami na požadovanou šířku, přičemž je odmítnuta, pokud přeteče kladný strop soustavy nebo pokud je požadovaná šířka příliš malá pro její vyjádření. Záporná hodnota se nejprve převede do správného rozsahu připočtením plného modula, což ji změní na hodnotu, jejíž reprezentace v dané soustavě má vždy deset číslic, a poté jsou číslice emitovány s vedoucími nulami pro vyplnění šířky. Jediná sdílená kontrola rozsahu, symetrické dolní a horní meze pro každou soustavu, je to, co udržuje funkce DEC2BIN, DEC2OCT a DEC2HEX vzájemně konzistentní na jejich hranicích.
Tím zůstávají převody mezi soustavami, jako jsou HEX2BIN a OCT2HEX, které mění soustavu, aniž by v názvu funkce procházely desítkovou soustavou. Implementace neobsahuje samostatnou rutinu pro každou uspořádanou dvojici. Analyzuje vstupní řetězec na desítkovou hodnotu se znaménkem pomocí zdrojové soustavy a poté tuto desítkovou hodnotu naformátuje do cílové soustavy. Desítková soustava je zde pivotem. Jedna analytická rutina a jedna formátovací rutina, spojené dohromady, pokrývají každou kombinaci, a protože obě poloviny sdílejí stejnou desetimístnou znaménkovou konvenci, záporná hodnota přežije cestu se zachovaným znaménkem.
Komplexní čísla jsou řetězce, takže práce spočívá v parsování
Excel nemá žádný komplexní datový typ. Komplexní hodnota je řetězec "a+bi" a každá funkce z rodiny IM tyto řetězce přijímá a vrací je zpět. COMPLEX sestavuje řetězec z reálné a imaginární části. Funkce IMSUM, IMSUB, IMPRODUCT a IMDIV parsují své argumenty, provedou aritmetiku na číselných částech a naformátují výsledek zpět do řetězce. Číselná práce je středoškolská algebra. Obtíž spočívá výhradně ve spolehlivém převodu textu na dvě čísla s plovoucí řádovou čárkou, a to je přesně místo, kde interní parser plní svou úlohu.
Dva detaily v tomto parseru je snadné pokazit. Prvním je samotná imaginární jednotka. Řetězec "i" znamená jedna krát i, nikoli nula a ne chyba, takže když je koeficient před příponou prázdný nebo jde o osamocené znaménko plus, parser jej musí přečíst jako hodnotu 1 a osamocené mínus jako -1. Pokud to vynecháte, IMSUM("i","i") přestane vracet 2i. Druhým detailem je vědecký zápis kolidující se znaménkem, které odděluje reálnou a imaginární část. Parser nachází tento oddělovač hledáním plusu nebo mínusu, ale číslo zapsané jako "1.5E-3" obsahuje mínus patřící k exponentu. Vyhledávání proto odmítá považovat plus nebo mínus za oddělovač, pokud je znak těsně před ním e nebo E. Bez této ochrany by byla reálná část roztržena na polovinu u znaménka exponentu a analýza by selhala na zcela platném vstupu.
Samotná přípona je zachována, nikoli normalizována. Excel přijímá jak i, tak j a HotXLS si pamatuje, kterou z nich vstup použil, takže naformátovaný výsledek nese stejné písmeno. Formátování pak používá konvenční zkratky: imaginární část o hodnotě jedna se vytiskne jako pouhá přípona, mínus jedna jako -i, nulová imaginární část se zredukuje na prosté reálné číslo a nulová reálná část vynechá úvodní 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í funkce, mimo jiné IMSQRT, IMEXP, IMLN a IMPOWER, nepracují v pravoúhlých souřadnicích. Převádějí analyzovanou hodnotu do polárního tvaru, aplikují operaci na modul a argument a převádějí ji zpět. Druhá odmocnina půlí argument a odmocňuje modul. Jakýkoli jiný způsob by znamenal odvozování každé identity v pravoúhlém tvaru, což představuje více kódu a menší numerickou stabilitu v blízkosti bodů nespojitosti.
Bitové operátory a přetečení, které musíte zkontrolovat jako první
Excel 2013 přidal funkce BITAND, BITOR, BITXOR, BITLSHIFT a BITRSHIFT. Operandy jsou omezeny: každý musí být nezáporné celé číslo nepřesahující 2^48 mínus 1 a jakýkoli desetinný nebo záporný argument je číselnou chybou. Tento strop je dostatečně velkorysý, aby pokryl jakoukoli reálnou sadu příznaků, a zároveň zůstává bezpečně uvnitř přesně reprezentovatelného rozsahu typu double, což je důležité, protože Excel předává každý číselný argument jako hodnotu s plovoucí řádovou čárkou.
Posunové funkce nesou jedno pravidlo pořadí, které vás může skutečně vytrestat. Levý posun může vytvořit hodnotu mnohem větší než její vstup, a pokud nejprve provedete shl a výsledek zkontrolujete až poté, již došlo k přetečení typu Int64 a test je bezvýznamný. Kontrola musí přijít před posunem. HotXLS porovnává operand s horním limitem posunutým doprava o velikost posunu, a pouze pokud se operand vejde, provede samotný levý posun. Velikost posunu přesahující 53 bitů je rovnou odmítnuta a záporný posun jednoduše obrací směr, takže BITLSHIFT se záporným počtem se chová jako pravý posun. Tento princip se zobecňuje daleko za tuto jedinou funkci: když existuje ochrana proti přetečení, musí běžet na vstupech, nikdy na výsledku, který měla chránit.
// 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)'); // 5Budoucí funkce a předpona názvu _xlfn
Bitové operátory a dlouhý seznam dalších přírůstků po roce 2007 spolupracují se schématem pojmenování, které nemá nic společného s tím, co počítají, ale souvisí s tím, jak je Excel ukládá. Původní binární formát listu přiřazoval každé vestavěné funkci číselnou pozici v pevné tabulce. Funkce vynalezené po zmrazení této tabulky žádnou pozici nemají. Aby bylo možné takovou funkci uložit do souboru a moderní Excel ji rozpoznal, zapisuje se název s předponou _xlfn., takže BITAND se na disk ukládá jako _xlfn.BITAND, přestože uživatel zadává pouze BITAND.
Záludnost je v tom, jak se toto pravidlo neuplatňuje jednotně. Některé novější funkce dostaly pozice v tabulce a zapisují se čisté, zatímco několik historických skrytých funkcí se také zapisuje bez předpony navzdory svému stáří. HotXLS udržuje explicitní seznam povolených názvů, které předponu vyžadují, přidává ji při zápisu a odstraňuje při čtení, takže text vzorce, který nastavíte a čtete zpět, je vždy čistý název určený pro uživatele v Excelu. Nastavíte =BITLSHIFT(5,2), soubor obsahuje _xlfn.BITLSHIFT a hodnota se přesto vrátí jako 20. Předpona je detailem úložiště, který by nikdy neměl proniknout do vzorců, se kterými pracujete v kódu.
Sestavení v listu
Veřejné rozhraní pro toto všechno je malé. Vytvoříte TXLSXWorkbook, přidáte list a buď zapíšete vzorec do buňky pomocí Cells[Row, Col].Formula a přepočítáte jej, nebo vyhodnotíte výraz přímo pomocí metody listu Calculate, která zkompiluje vzorec vůči tomuto listu a vrátí Variant. Vyše uvedené příklady používají Calculate, protože to ukazuje výsledek jediného technického volání bez okolního stavu listu, ale stejné funkce se vyhodnocují identicky v reálných vzorcích buněk při přepočtu sešitu.
Kódování jsou tou částí, kterou je třeba mít na paměti, nikoli místa volání. Binární řetězec je záporný pouze při deseti číslicích a pouze za polovičním prahem pro svou soustavu. Komplexní číslo je text, prázdný imaginární koeficient znamená jedna a parser přeskakuje znak e v exponentu. Levý posun se kontroluje před samotným posunem. Mějte tato čtyři fakta na paměti a rodina technických funkcí přestane být zdrojem překvapení se špatným znaménkem.
Pokud zapojujete vlastní matematické výpočty do stejného enginu, mechanismus registrace obsluhy a vracení hodnot popisuje náš článek o rozšíření formulového enginu o vlastní funkce, a když tyto vzorce musí přistupovat k jiným listům pomocí názvu namísto adresy buňky, průvodce definovanými názvy a vzorci napříč listy ukazuje, jak se odkazy řeší. Technické funkce popsané zde jsou dodávány jako součást HotXLS tabulkového komponentu pro Delphi a C++Builder spolu s rozhraními API pro čtení, zápis a výpočty popsanými na jiných místech tohoto blogu.