Инженерные функции в Delphi: преобразование систем счисления и комплексные числа

Семейство инженерных функций в Excel кажется простейшим разделом справочника. Функция DEC2BIN преобразует число в двоичную строку, HEX2DEC выполняет обратное действие, а IMSUM складывает два комплексных числа. Каждое из этих действий выглядит просто как операция форматирования. Но это не так. За этими именами скрываются 10-битное представление в дополнительном коде (которое большинство разработчиков не использовали со времен лекций по архитектуре компьютеров), представление комплексных чисел в виде обычных строк и побитовые операторы, которые могут привести к негласному переполнению 64-битного целого числа при выполнении сдвига без предварительной проверки. Табличный движок, стремящийся к полной совместимости с Excel, должен в точности воспроизводить эти алгоритмы.

Эти функции делятся на три группы, и в каждой группе разработчика подстерегают свои сложности. Преобразование систем счисления связано с обработкой отрицательных чисел и специфическими лимитами для каждого основания. Комплексная арифметика требует корректного парсинга и форматирования строк. Побитовые операции требуют строгого контроля границ типа Int64. В этой статье рассматривается реализация каждой группы функций в HotXLS на примерах реальных вызовов на рабочем листе.

Преобразование систем счисления и 10-битный дополнительный код

Прямое преобразование работает вполне ожидаемо. Вызов DEC2BIN(9) возвращает "1001", а необязательный второй аргумент позволяет дополнить результат нулями слева до нужной длины. Сложность возникает при обработке отрицательных чисел. Excel не выводит знак минуса. Вместо этого программа кодирует значение в виде 10-символьной строки в дополнительном коде в целевой системе счисления. По этой причине DEC2BIN(-5,10) возвращает "1111111011", а не строку со знаком. Дополнительный аргумент длины игнорируется для отрицательных чисел, так как длина закодированного значения жестко зафиксирована на уровне десяти символов.

Десять символов накладывают жесткие ограничения на диапазон представимых чисел для каждого основания. В двоичной системе значение, при котором число переходит в разряд отрицательных, равно 512, а модуль циклического сдвига составляет 1024. Соответственно, двоичная строка считается отрицательной только в том случае, если ее длина равна ровно десяти символам, а значение составляет не менее 512. Аналогичная логика применяется и к другим системам счисления. Восьмеричная система использует порог перехода 2^29 и модуль 2^30. Шестнадцатеричная система задействует порог 2^39 и модуль 2^40. Модуль чтения HotXLS в точности следует этому правилу: он накапливает цифры, и только если длина строки составляет десять символов, а итоговое значение оказывается не ниже порога перехода, он вычитает модуль сдвига для получения отрицательного числа. Девятисимвольная строка всегда интерпретируется как неотрицательное число независимо от его величины.

Логика кодировщика является зеркальной. Неотрицательное значение переводится посимвольно и при необходимости дополняется ведущими нулями до указанной длины. Вызов отклоняется, если число превышает положительный лимит системы счисления или если указанная ширина недостаточна для вывода. Отрицательное число сначала приводится к допустимому диапазону путем сложения с модулем сдвига, что дает значение, представление которого всегда занимает ровно десять символов, после чего генерируются цифры с ведущими нулями для заполнения длины. Единая проверка диапазона, а также симметричные нижняя и верхняя границы для каждого основания обеспечивают согласованную работу функций DEC2BIN, DEC2OCT и DEC2HEX на границах диапазонов.

Это также относится к перекрестным преобразованиям между системами счисления (таким как HEX2BIN и OCT2HEX), которые переводят числа без явного использования десятичной системы в имени функции. В реализации нет отдельных процедур для каждой пары систем счисления. Парсер переводит входную строку в знаковое десятичное значение на основе исходного основания, а затем форматирует это десятичное значение в целевую систему счисления. Десятичное представление выступает в качестве связующего звена. Сочетание одной процедуры парсинга и одной процедуры форматирования покрывает все комбинации, и, поскольку обе половины используют общее 10-символьное представление отрицательных чисел, знак числа сохраняется при любых переходах.

Комплексные числа как строки и особенности их парсинга

В Excel нет встроенного типа данных для комплексных чисел. Комплексное значение представляется строкой "a+bi", и любая функция из семейства IM принимает такие строки на вход и возвращает строку в качестве результата. Функция COMPLEX строит строку по действительной и мнимой частям. Функции IMSUM, IMSUB, IMPRODUCT и IMDIV парсят аргументы, выполняют математические операции над числовыми компонентами и форматируют результат обратно в строку. Математические расчеты сводятся к базовой алгебре, а основная сложность заключается в надежном преобразовании текста в два числа с плавающей запятой, с чем отлично справляется внутренний парсер.

Две детали в логике работы парсера легко упустить из виду. Первая из них связана с одиночным обозначением мнимой единицы. Строка "i" означает умножение 1 на 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 spreadsheet component для Delphi и C++Builder вместе с API для чтения, записи и расчета электронных таблиц.