Инженерното семейство функции в Excel изглежда като най-лесната част от справочника с функции. DEC2BIN превръща число в двоичен низ. HEX2DEC го връща обратно. IMSUM събира две комплексни числа. Всяка от тях изглежда като упражнение по форматиране. Те не са. Зад тези имена се крие десетбитово кодиране с допълнителен код (two's complement), което повечето разработчици не са докосвали от часовете по компютърна архитектура, формат за комплексни числа, който живее изцяло в низове, и побитови оператори, които безшумно ще препълнят 64-битово цяло число, ако извършите преместване (shift) преди проверка. Машина за електронни таблици, която възпроизвежда точно Excel, не може да закръгли нищо от това.
Функциите се разделят на три групи и всяка група крие различен капан. Преобразуването на бройни системи е свързано с отрицателните числа и праговете за всяка база. Комплексната аритметика е свързана с парсирането и форматирането на низ. Побитовите операции са свързани с оставането в границите на Int64. Тази статия разглежда всяка група така, както я имплементира HotXLS, с извикванията на работния лист, които действително бихте написали.
Преобразуване на бройни системи и десетбитовият допълнителен код
Посоката напред е частта, която всеки очаква. DEC2BIN(9) дава "1001", а незадължителен втори аргумент допълва резултата отляво до фиксирана ширина. Капанът е отрицателният вход. Excel не пише знак минус. Той кодира стойността как десетцифрен низ с допълнителен код в целевата бройна система, поради което DEC2BIN(-5,10) връща "1111111011" вместо нещо със знак. Аргументът за брой места (places) се игнорира, след като стойността е отрицателна, тъй като кодирането вече е фиксирано на десет цифри.
Десет цифри са фиксиран бюджет и този бюджет задава диапазона на представяне за всяка бройна система. В двоична система величината, която преминава в отрицателната половина, е 512, а модулът на обвиване е 1024, така че двоичният низ има знак само когато е дълъг точно десет знака и стойността му е поне 512. Същата идея се мащабира и с другите бройни системи. Осмичната използва половин праг от 2^29 и пълен модул от 2^30. Шестнадесетичната използва 2^39 и 2^40. Четецът на HotXLS прилага точно това правило: той натрупва цифрите и само когато низът е широк десет знака и натрупаната стойност е на или над половиния праг, той изважда пълния модул, за да възстанови стойността със знак. Низ от девет знака винаги е неотрицателен, без значение колко е голям.
Кодиращият модул (encoder) е огледалният образ. Неотрицателна стойност се преобразува цифра по цифра и по избор се допълва с нули до исканата ширина, и се отхвърля, ако препълни положителния таван на базата или ако исканата ширина е твърде тясна, за да я побере. Отрицателна стойност първо се вкарва в диапазона чрез добавяне на пълния модул, което я превръща в стойност, чието представяне в съответната база винаги е десет цифри, и след това цифрите се излъчват с водещи нули, за да се запълни ширината. Единствената споделена проверка на диапазона, симетричните долни и горни граници за всяка база, е това, което поддържа DEC2BIN, DEC2OCT и DEC2HEX съвместими една с друга в техните крайни стойности.
Това оставя преобразуванията между различни бройни системи, как HEX2BIN и OCT2HEX, които променят базата, без да преминават през десетична в името на функцията. Реализацията не съдържа отделна подпрограма за всяка наредена двойка. Тя парсира входния низ в десетична стойност със знак, използвайки изходната база, след което форматира тази десетична стойност в целевата база. Десетичната система е опорната точка. Една рутина за парсиране и една за форматиране, комбинирани, покриват всяка комбинация, и тъй като и двете половини споделят една и съща десетцифрена конвенция със знак, отрицателната стойност оцелява при пътуването с непокътнат знак.
Комплексните числа са низове, така че работата е парсиране
Excel няма комплексен тип данни. Комплексната стойност е низът "a+bi" и всяка функция от фамилията IM приема тези низове и връща такъв. COMPLEX изгражда низа от реална и имагинерна част. IMSUM, IMSUB, IMPRODUCT и IMDIV парсират своите аргументи, извършват аритметиката върху числовите части и форматират резултата обратно в низ. Числовата работа е алгебра за начинаещи. Трудността е изцяло в надеждното превръщане на текста в две числа с плаваща запетая и точно тук вътрешният парсер си заслужава усилията.
Две подробности в този парсер са лесни за бъркане. Първата е чистата имагинерна единица. Низът "i" означава едно по i, а не нула и не е грешка, така че когато коефициентът пред суфикса е празен или е само знак плюс, парсерът трябва да го прочете като стойност 1, а единичен минус като -1. Пропуснете това и IMSUM("i","i") спира да бъде 2i. Втората подробност е научната нотация, която се сблъсква със знака, разделящ реалната и имагинерната част. Парсерът намира този разделител чрез сканиране за плюс или минус, но число, записано като "1.5E-3", съдържа минус, който принадлежи на експонентата. Поради това сканирането отказва да третира плюс или минус като разделител, когато символът непосредствено преди него е e или E. Без тази защита реалната част би била разделена наполовина при знака на експонентата и парсирането би се провалило при напълно валидни входящи данни.
Самият суфикс се запазва, вместо да се нормализира. Excel приема както i, така и j, а HotXLS помни кой от тях е използван на входа, така че форматираният резултат да носи същата буква. Форматирането след това прилага конвенционалните съкращения: имагинерна част от едно се отпечатва само как суфикс, минус едно като -i, нулева имагинерна част се свива до чисто реално число, а нулева реална част изпуска водещото 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;Трансцендентните комплексни функции, сред които IMSQRT, IMEXP, IMLN и IMPOWER, не работят в правоъгълни координати. Те преобразуват парсираната стойност в полярна форма, прилагат операцията върху модула и аргумента и преобразуват обратно. Квадратен корен разделя наполовина аргумента и взема корена от модула. Степен умножава аргумента и повдига модула на степен. Извършването на това по друг начин би означавало повторно извеждане на всяко тъждество в правоъгълна форма, което е едновременно повече код и е по-малко числено стабилно близо до разрезите на разклоненията (branch cuts).
Побитови оператори и препълването, което трябва да проверите първо
Excel 2013 добави BITAND, BITOR, BITXOR, BITLSHIFT и BITRSHIFT. Операндите са ограничени: всеки трябва да бъде неотрицателно цяло число, не по-горямо от 2^48 минус 1, а всеки дробен или отрицателен аргумент е числова грешка. Този таван е достатъчно щедър, за да покрие всеки реалистичен набор от флагове, като същевременно остава доста в рамките на точно представимия диапазон на тип double, което е важно, тъй като Excel предава всеки числов аргумент как стойност с плаваща запетая.
Функциите за изместване носят едното правило за подредба, което наистина може да навреди. Ляво изместване може да произведе стойност, далеч по-голяма от нейния вход, и ако извършите shl първо и проверите резултата след това, вече сте препълнили Int64 и тестът е безсмислен. Проверката трябва да дойде преди изместването. HotXLS сравнява операнда с тавана, изместен надясно с количеството на изместването, и само ако операндът се побира, извършва действителното ляво изместване. Величина на изместване над 53 бита се отхвърля директно, а отрицателно изместване просто обръща посоката, така че BITLSHIFT с отрицателен брой се държи как дясно изместване. Принципът се обобщава далеч отвъд тази единична функция: когато съществува защита за предотвратяване на препълване, тя трябва да работи върху входовете, никога върху резултата, който е трябвало да защити.
// 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)'); // 5Бъдещи функции и префиксът за име _xlfn
Побитовите оператори и дълъг списък от други допълнения след 2007 г. си взаимодействат с схема за именуване, която няма нищо общо с това, което изчисляват, и има всичко общо с начина, по който Excel ги съхранява. Оригиналният двоичен формат на работния лист присвояваше на всяка вградена функция числова позиция (slot) в фиксирана таблица. Функциите, изобретени след замразяването на тази таблица, нямат позиция. За да запишете такава функция във файл и модерен Excel да я разпознае, името се записва с префикс _xlfn., така че BITAND се съхранява как _xlfn.BITAND на диска, въпреки че потребителят винаги въвежда само BITAND.
Уловката е, че правилото не е уеднаквено. На някои по-нови функции бяха дадени позиции в таблицата и се пишат изчистени, докато няколко наследени скрити функции също се пишат без префикс въпреки възрастта си. HotXLS поддържа изричен бял списък за това кои имена се нуждаят от префикс, добавя го при запис и го премахва при четене, така че текстът на формулата, който задавате и четете обратно, винаги е чистото име, насочено към Excel. Задавате =BITLSHIFT(5,2), файлът съдържа _xlfn.BITLSHIFT, а стойността се връща как 20 независимо от това. Префиксът е подробност за съхранение, която никога не трябва да изтича във формулите, с които работите в кода.
Сглобяване на всичко в работен лист
Публичната повърхност за всичко това е малка. Създавате TXLSXWorkbook, добавяте работен лист и или записвате формула в клетка чрез Cells[Row, Col].Formula and преизчислявате, или оценявате израз директно с метода на работния лист Calculate, който компилира формулата спрямо този лист и връща Variant. Примерите по-горе използват Calculate, защото показва резултата от едно инженерно извикване без околното състояние на листа, но същите функции се оценяват идентично вътре в реални формули на клетки, когато работната книга се преизчисли.
Кодиранията са частта, която трябва да имате предвид, а не местата на извикване. Двоичният низ има знак само при десет цифри и само след половиния праг за своята база. Комплексното число е текст, празен имагинерен коефициент е едно, а парсерът стъпва върху буквата e на експонентата. Лявото изместване се проверява, преди да се измести. Разберете правилно тези четири факта и инженерното семейство функции ще спре да бъде източник на изненади с объркан знак.
Ако свързвате собствена математика от предметната област в същата машина, механиката на регистриране на манипулатор и връщане на стойности е разгледана в нашата статия за разширяване на машината за формули с персонализирани функции, а когато тези формули трябва да достигат до различни листове по име, а не по адрес на клетка, ръководството за дефинирани имена и формули между листове показва как се разрешават препратките. Инженерните функции, описани тук, се доставят как част от HotXLS spreadsheet component за Delphi и C++Builder, заедно с API за четене, писане и изчисляване, разгледани на други места в този блог.