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