Сімейство інженерних функцій в Excel читається як найпростіший розділ довідника з функцій. DEC2BIN перетворює число на двійковий рядок. HEX2DEC перетворює його назад. IMSUM додає два комплексні числа. Кожна з них виглядає як вправа з форматування. Це не так. За цими назвами стоїть десятибітне кодування доповнювального коду (two's complement), якого більшість розробників не торкалися з часів курсу архітектури комп'ютерів, формат комплексних чисел, який живе повністю всередині рядків, та побітові оператори, які непомітно переповнять 64-бітне ціле число, якщо ви зсунете перед перевіркою. Движок електронної таблиці, який точно відтворює Excel, не може нічого з цього спростити
Функції поділяються на три групи, і кожна група приховує іншу пастку. Перетворення систем числення стосується від'ємних чисел і порогів для кожної основи. Комплексна арифметика стосується синтаксичного аналізу (парсингу) і форматування рядка. Побітові операції — це про те, як залишатися в межах Int64. У цій статті розглядається кожна група так, як їх реалізує HotXLS, із викликами робочого аркуша, які ви б фактично написали
Перетворення систем числення та десятибітний доповнювальний код
Прямий напрямок — це та частина, яку очікують усі. DEC2BIN(9) дає "1001", а необов'язковий другий аргумент доповнює результат зліва до фіксованої ширини. Пастка — це від'ємний ввід. Excel не пише знак мінус. Він кодує значення як десятизначний рядок доповнювального коду в цільовій системі числення, ось чому DEC2BIN(-5,10) повертає "1111111011", а не що-небудь зі знаком. Аргумент розрядності ігнорується, щойно значення стає від'ємним, оскільки кодування вже закріплене на десяти цифрах
Десять цифр — це фіксований бюджет, і цей бюджет встановлює діапазон представлення для кожної основи. У двійковій системі величина, яка переходить у від'ємну половину, дорівнює 512, а модуль циклу (wrap modulus) дорівнює 1024, тому двійковий рядок є знаковим, лише якщо його довжина становить рівно десять символів, а його значення дорівнює щонайменше 512. Ця сама ідея масштабується разом з основою. Вісімкова система використовує половину порогу 2^29 і повний модуль 2^30. Шістнадцяткова використовує 2^39 і 2^40. Аналізатор HotXLS застосовує саме це правило: він накопичує цифри, і лише коли ширина рядка становить десять символів, а накопичене значення знаходиться на рівні або перевищує половину порогу, він віднімає повний модуль, щоб відновити знакове значення. Дев'ятисимвольний рядок завжди невід'ємний, яким би великим він не був
Кодер є дзеркальним відображенням. Невід'ємне значення перетворюється цифра за цифрою і за бажанням доповнюється нулями до запитаної ширини, і воно відхиляється, якщо воно переповнює додатну стелю основи або якщо запитана ширина занадто вузька, щоб вмістити його. Від'ємне значення спочатку приводиться до діапазону шляхом додавання повного модуля, що перетворює його на значення, чиє представлення за базою завжди становить десять цифр, а потім цифри видаються з початковими нулями для заповнення ширини. Єдина спільна перевірка діапазону, симетричні нижні та верхні межі для кожної основи — це те, що робить DEC2BIN, DEC2OCT і DEC2HEX послідовними один з одним на їхніх краях
Залишаються перехресні перетворення основ, такі як HEX2BIN та OCT2HEX, які змінюють основу без проходження через "decimal" (десяткову) у назві функції. Реалізація не містить окремої підпрограми для кожної впорядкованої пари. Вона розбирає вхідний рядок у знакове десяткове значення, використовуючи вихідну основу, а потім форматує це десяткове значення в цільову основу. Десяткова система — це стрижень. Одна підпрограма розбору й одна підпрограма форматування, складені разом, охоплюють кожну комбінацію, і оскільки обидві половини поділяють ту саму десятизначну знакову конвенцію, від'ємне значення проходить цей шлях зі збереженим знаком
Комплексні числа — це рядки, тому робота полягає в парсингу
В 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 їх зберігає. Оригінальний двійковий формат робочого аркуша призначав кожній вбудованій функції числовий слот у фіксованій таблиці. Функції, винайдені після заморожування цієї таблиці, не мають слота. Щоб зберегти таку функцію у файл і сучасний Excel зміг розпізнати її, ім'я записується з префіксом _xlfn., тому BITAND зберігається як _xlfn.BITAND на диску, незважаючи на те, що користувач завжди вводить лише BITAND
Заковика полягає в тому, що правило не є єдиним. Деякі новіші функції отримали слоти в таблиці і пишуться без змін, тоді як кілька застарілих прихованих функцій також пишуться без префікса, незважаючи на їхній вік. HotXLS зберігає явний "білий список" того, які імена потребують префікса, додає його під час запису та прибирає під час читання, тому текст формули, який ви встановлюєте і читаєте назад, — це завжди чисте ім'я, з яким взаємодіє Excel. Ви встановлюєте =BITLSHIFT(5,2), у файлі зберігається _xlfn.BITLSHIFT, а значення повертається як 20 незалежно від цього. Префікс — це деталь зберігання, яка ніколи не повинна просочуватися у формули, з якими ви працюєте в коді
Збираємо все разом на робочому аркуші
Загальнодоступна поверхня (API) для всього цього невелика. Створіть TXLSXWorkbook, додайте аркуш і або запишіть формулу в клітинку через Cells[Row, Col].Formula і перерахуйте, або обчисліть вираз безпосередньо за допомогою методу Calculate аркуша, який компілює формулу відносно цього аркуша та повертає Variant. У наведених вище прикладах використовується Calculate, оскільки він показує результат одного інженерного виклику без навколишнього стану аркуша, але ті ж самі функції обчислюються ідентично всередині реальних формул клітинок під час перерахунку робочої книги
Кодування — це та частина, яку слід пам'ятати, а не місця викликів. Двійковий рядок має знак лише при десяти цифрах і лише після подолання половини порогу для його основи. Комплексне число — це текст, порожній уявний коефіцієнт дорівнює одиниці, а парсер переступає через e експоненти. Лівий зсув перевіряється до того, як він зсунеться. Засвойте ці чотири факти правильно, і інженерне сімейство перестане бути джерелом несподіванок із "помилкою на знак"
Якщо ви підключаєте свою власну математику домену до того ж движка, механізми реєстрації обробника та повернення значень розглядаються в нашій статті про розширення движка формул користувацькими функціями, а коли ці формули повинні діставати дані з різних аркушів за іменем, а не за адресою клітинки, покрокове керівництво з визначених імен та крос-аркушевих формул показує, як розв'язуються посилання. Інженерні функції, описані тут, постачаються як частина компонента електронних таблиць HotXLS для Delphi та C++Builder разом з API для читання, формул і форматування, що розглядалися в інших статтях на цьому блозі