Семейство инженерных функций в Excel кажется самым простым разделом в справочнике функций. DEC2BIN преобразует число в двоичную строку. HEX2DEC преобразует его обратно. IMSUM складывает два комплексных числа. Каждое из этих действий выглядит как упражнение по форматированию. Но это не так. За этими названиями скрывается десятибитная кодировка в дополнительном коде, с которой большинство разработчиков не сталкивались со времен курса архитектуры ЭВМ, формат комплексных чисел, полностью живущий внутри строк, и побитовые операторы, которые молча переполнят 64-битное целое число, если вы выполните сдвиг до проверки. Движок электронных таблиц, который в точности воспроизводит Excel, не может округлить ни одну из этих деталей
Функции делятся на три группы, и в каждой скрывается своя ловушка. Преобразование систем счисления связано с отрицательными числами и пороговыми значениями для каждой системы. Комплексная арифметика связана с синтаксическим анализом (парсингом) и форматированием строки. Побитовые операции связаны с тем, чтобы оставаться в пределах Int64. В этой статье рассматривается каждая группа в том виде, в котором она реализована в HotXLS, с вызовами рабочего листа, которые вы бы написали на самом деле
Преобразование систем счисления и десятибитный дополнительный код
Прямое преобразование — это та часть, которую все ожидают. DEC2BIN(9) дает "1001", а необязательный второй аргумент дополняет результат слева до фиксированной ширины. Ловушка кроется в отрицательных входных данных. Excel не пишет знак минуса. Он кодирует значение как десятизначную строку в дополнительном коде в целевой системе счисления, именно поэтому DEC2BIN(-5,10) возвращает "1111111011", а не что-либо со знаком. Аргумент количества разрядов игнорируется, если значение отрицательное, поскольку кодировка уже зафиксирована на десяти цифрах
Десять цифр — это фиксированный бюджет, и этот бюджет устанавливает представимый диапазон для каждой системы счисления. В двоичной системе значение, при котором происходит переход в отрицательную половину, равно 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. Префикс — это деталь хранения, которая никогда не должна просачиваться в формулы, с которыми вы работаете в коде
Сборка всего воедино на рабочем листе
Открытая поверхность API для всего этого невелика. Создайте TXLSXWorkbook, добавьте рабочий лист и либо запишите формулу в ячейку через Cells[Row, Col].Formula и пересчитайте, либо вычислите выражение напрямую с помощью метода Calculate рабочего листа, который компилирует формулу относительно этого листа и возвращает Variant. В приведенных выше примерах используется Calculate, потому что он показывает результат одиночного инженерного вызова без окружающего состояния листа, но те же функции вычисляются идентично внутри реальных формул ячеек, когда книга пересчитывается
Именно кодировки — это то, о чем следует помнить, а не места вызова. Двоичная строка имеет знак только при десяти цифрах и только после порога половины для своего основания. Комплексное число — это текст, пустой мнимый коэффициент равен единице, а парсер перешагивает через e экспоненты. Сдвиг влево проверяется перед сдвигом. Если правильно понять эти четыре факта, инженерное семейство перестанет быть источником сюрпризов из-за ошибки в знаке
Если вы встраиваете собственную предметную математику в тот же движок, механизмы регистрации обработчика и возврата значений описаны в нашей статье о расширении движка формул пользовательскими функциями, а когда эти формулы должны обращаться к другим листам по имени, а не по адресу ячейки, руководство по определенным именам и межстраничным формулам показывает, как разрешаются ссылки. Инженерные функции, описанные здесь, поставляются как часть компонента электронных таблиц HotXLS для Delphi и C++Builder, наряду с API чтения, записи и вычислений, рассмотренными в других статьях этого блога