Если SUBTOTAL(109, ...) и SUBTOTAL(9, ...) возвращают одно и то же число на книге, содержащей скрытые строки, одно из двух неверно. HotXLS, нативный компонент электронных таблиц Excel для Delphi и C++Builder, вёл себя именно так вплоть до версии 2.197.0, потому что его вычислительный движок не имел способа спросить лист, скрыта ли данная строка
Симптом редко приходит как отчёт о баге насчёт кодов формул. Он приходит как несовпадение: пакетное задание на сервере вычисляет итог, пользователь открывает тот же файл в Excel с применённым фильтром, и два числа расходятся на то, во что сложились отфильтрованные строки. Никто не подозревает функцию агрегации, потому что строка формулы в ячейке идентична в обоих местах. Разница целиком в том, что движку вычисления было позволено увидеть
Почему SUBTOTAL 109 включает скрытые строки?
Потому что в большинстве архитектур движка слой, вычисляющий формулу, никогда не узнаёт о видимости строки. HotXLS был хрестоматийным случаем: вычислительный движок в lxCalc.pas добирался до значений ячеек через единственный обратный вызов TXLSGetValue, отвечающий значением для тройки (лист, строка, столбец) и ничем больше. Видимость — это атрибут представления, хранящийся в записи строки, и ни одна часть этой записи не путешествовала вниз по цепочке вызова. У движка поэтому был один путь агрегации, и обе половины таблицы номеров функций SUBTOTAL разрешались в него. Это не класс дефекта округления: это вся причина существования второй половины таблицы. ECMA-376 часть 1, опубликованная как ISO/IEC 29500-1, определяет SUBTOTAL в своих определениях функций формул (§18.17.7) с первым аргументом, выбирающим и внутреннюю агрегацию, и политику скрытых строк. Коды с 1 по 11 отображаются на AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR и VARP, включая значения на вручную скрытых строках. Коды с 101 по 111 выбирают те же одиннадцать агрегаций и исключают их. Пользователь, набравший 109 вместо 9, делает намеренное заявление о скрытых данных, а движок, схлопывающий это различие, молча его отменяет
На что отображаются номера функций внутри движка
HotXLS разрешает первый аргумент SUBTOTAL в CalcSubtotalFunc, который нормализует коды со 101 по 111 к тем же внутренним идентификаторам функций, что и коды с 1 по 11, а затем диспетчеризует по самой агрегации. Большая часть семейства проходит через инкрементальный аккумулятор ExcelSum, тот, что обрабатывает SUM, COUNT, COUNTA, MIN, MAX и AVERAGE. Пять из них не могут: STDEV, VAR, STDEVP, VARP и PRODUCT нуждаются в проходе с замкнутой формой по данным, так что CalcSubtotalFunc маршрутизирует внутренние коды 12, 46, 193, 194 и 183 в отдельный редуктор, SubtotalReduceVariance. Это разделение — первое, что стоит нанести на карту, прежде чем что-либо трогать, потому что два независимых пути агрегации означают два независимых цикла обхода ячеек, а исправление, применённое только к одному из них, производит худший из возможных исходов: SUBTOTAL(109, ...) учитывает фильтр, пока SUBTOTAL(107, ...) на том же диапазоне — нет. Подсчёт циклов в HotXLS выявил шесть из них, как только был включён AGGREGATE, распределённых по вычислению диапазона, простому сбору диапазона и трём отдельным редукторам
Почему скретч-поле вместо шести новых сигнатур?
Потому что протягивание нового параметра через шесть функций обхода ячеек, плюс всё, что их вызывает, — это широкое изменение горячего пути кода ради одного булева значения. У HotXLS уже был прецедент альтернативы: временное поле на калькуляторе, в том же духе, что и скретч-поле, которое GetRangeInfo использует, чтобы записать, когда 3D-ссылка разрешилась во внешнюю книгу. Версия 2.197.0 добавила второе. Движок получил тип обратного вызова, TXLSIsRowHidden, объявленный как функция от (SheetIndex, row), возвращающая Boolean, хранящаяся в FIsRowHidden, плюс временный флаг FIgnoreHiddenRows. Флаг взводится на входе в CalcSubtotalFunc, когда код функции попадает в диапазон 101–111, и на входе в CalcAggregateFunc для кодов опций AGGREGATE, выбирающих исключение скрытых строк. Каждый цикл обхода ячеек затем проверяет его и пропускает одну строку, когда он установлен, добавляя по одной строке каждый
// The shape repeated in all six cell-walk loops
for sh := s1 to s2 do
for rr := r1 to r2 do
begin
if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
Continue;
for cc := c1 to c2 do
begin
// ... fold Cells[rr, cc] into the accumulator ...
end;
end;Две детали в коде взведения несут корректность всей схемы. Флаг сохраняется и восстанавливается, а не просто устанавливается и сбрасывается, потому что аргумент SUBTOTAL может содержать выражение, запускающее собственное вычисление, пока внешняя агрегация ещё в стеке, и эта вложенная работа не должна ни наследовать, ни разрушать внешний затвор. А восстановление живёт в блоке finally, потому что у CalcSubtotalFunc есть несколько ранних выходов для кодов ошибок; флаг, оставленный взведённым после возврата с ошибкой, молча испортил бы следующую несвязанную формулу в порядке пересчёта
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;Проверка Assigned — то, что сохраняет совместимость изменения. HotXLS расширил конструктор калькулятора третьим параметром по умолчанию nil, так что любой код, строящий TXLSCalculator старым двухаргументным вызовом, всё ещё компилируется и всё ещё получает старое поведение с включением скрытых. Форма существующего API не изменилась
Откуда на самом деле берётся бит скрытой строки?
С листа, через два разных источника, потому что HotXLS несёт два движка книги. Устаревшая сторона BIFF отвечает из TXLSRowInfoList.GetHidden, достигаемой через TXLSWorkbook.GetRowHidden. Сторона OOXML отвечает из TXLSXWorksheet.GetRowHidden, достигаемой через TXLSXWorkbook.GetCalcRowHidden. Оба подключены к калькулятору в момент конструирования, наряду с обратным вызовом значения ячейки, который они зеркалируют. Условности строк — это то место, где такого рода мост обычно ломается, так что их стоит явно назвать. Калькулятор передаёт обратному вызову 0-базированную строку, совпадающую с координатами, которые уже использует TXLSGetValue. Лист XLSX ключует свою карту скрытых строк 1-базированным номером строки, ровно как Excel нумерует строки, что также выставляет публичное свойство RowHidden[ARow]. Поэтому мост XLSX прибавляет единицу перед поиском, а мост BIFF — нет, потому что TXLSRowInfoList уже 0-базирован. Оба моста трактуют индекс листа или строки за пределами допустимого диапазона как видимый, так что запрос за границами деградирует в старый ответ с включением скрытых вместо отбрасывания данных
Что меняется для отфильтрованных книг
Это случай, порождающий тикеты поддержки. Применение AutoFilter в HotXLS через ApplyAutoFilter вычисляет критерии столбца и скрывает каждую строку данных, не соответствующую им, что в точности то, что делает Excel, когда пользователь щёлкает выпадающий список фильтра. До v2.197.0 эти скрытые строки были невидимы пользователю и полностью видимы вычислительному движку, так что серверный SUBTOTAL(109, ...) сообщал нефильтрованный итог. Теперь тот же вызов сообщает отфильтрованный
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
VisibleRows: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
VisibleRows := Sheet.ApplyAutoFilter; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;Ручное скрытие работает так же, поскольку RowHidden[ARow] := True — то же состояние, что записывает фильтр. Эта эквивалентность в Excel намеренна, и теперь она выполняется и в HotXLS. Одно следствие заслуживает примечания в любой документации, поставляемой с вашими сгенерированными книгами: итог, вычисленный кодом 109, — число, зависящее от представления, так что получатель, снимающий фильтр, его меняет. Когда отчёт обязан заявлять фиксированную цифру независимо от того, что читатель делает с представлением, код 9 — верный выбор, и всегда им был. Фильтры, валидация и таблицы разбираются вместе в статье о проверке данных, AutoFilter и таблицах. Поскольку скрытие строк не затрагивает ни одну формулу, оно также само по себе не пачкает граф зависимостей, что стоит знать, если вы полагаетесь на инкрементальный пересчёт по грязному подграфу, чтобы держать большие книги отзывчивыми
Коды опций AGGREGATE и одно ограничение, всё ещё открытое
AGGREGATE — это SUBTOTAL со вторым аргументом политики, и HotXLS обрабатывает его в CalcAggregateFunc. Аргумент опции кодирует независимые переключатели: пропускать ли вложенные вызовы SUBTOTAL и AGGREGATE внутри диапазона, пропускать ли значения на скрытых строках и подавлять ли значения ошибок вместо их распространения. HotXLS взводит общий затвор скрытых строк для кодов опций 2, 3, 6 и 7 и подавляет значения ошибок для кодов опций с 4 по 7. Аргумент номера функции затем выбирает агрегацию точно как это делает SUBTOTAL, включая маршрутизацию дисперсии, стандартного отклонения и произведения через их собственные редукторы. Один задокументированный пробел остаётся, и его лучше заявить здесь, чем обнаружить в продакшне: семантика игнорирования вложенных SUBTOTAL, связанная с низкими кодами опций, в HotXLS не реализована. Обнаружение вложенного SUBTOTAL внутри диапазона по ссылке требует маркировки состояния рекурсии вычислителя, чтобы внутренняя агрегация могла объявить о себе внешней, что более крупное изменение, чем затвор скрытых строк. На практике экспозиция мала, потому что реальные книги почти всегда размещают формулы SUBTOTAL вне диапазонов, которые агрегируют другие формулы SUBTOTAL. Если ваш генератор действительно строит перекрывающиеся диапазоны агрегации, не полагайтесь на низкие коды опций для их дедупликации
Защита арности, выпущенная вместе с этим
Версия 2.197.0 также закрыла пробел валидации в том же диспетчере, и причина дизайна та же, что мотивировала скретч-поле: разместить проверку там, где её можно написать один раз. Примерно 280 тел встроенных функций каждое проверяло собственное число аргументов против Item.ChildCount, что не оставляло согласованной границы для случая слишком многих аргументов. Вызов вроде =SIN(1,2) достигал тела функции, которое рассматривало свой первый аргумент, игнорировало избыток и возвращало правдоподобное число там, где Excel возвращает #VALUE!. HotXLS уже хранил заявленную арность каждой встроенной функции в своём реестре функций, выставленную как THashFunc.ArgsCnt, с -1, маркирующим вариативную функцию вроде SUM, IF или CONCAT. Версия 2.197.0 передала это через новое свойство TXLSFormula.FuncArgsCntByPtg и добавила один затвор в начало GetValueItemFunc, главного диспетчера
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;Затвор отклоняет слишком много аргументов и намеренно ничего не говорит о слишком малом их числе. Опущение хвостового опционального аргумента законно в Excel для VLOOKUP, SUBSTITUTE и долгого списка других, так что симметричная проверка сломала бы корректные формулы ради отлова некорректных. Неизвестные идентификаторы сообщаются как вариативные и полностью пропускают затвор, что и не даёт пользовательским функциям попасть под него; если вы регистрируете собственные функции, поведение, описанное в руководстве по движку формул и пользовательским функциям, не затронуто. Централизация случая слишком малого числа аргументов — отдельная работа, потому что у каждого из этих 280 тел своя собственная семантика кода ошибки, и их нужно рассматривать по одному, а не предполагать
Описанный здесь вычислительный движок, оба фасада книги и API AutoFilter и видимости строк, его питающие, — часть Delphi-компонента электронных таблиц HotXLS, который поставляется с полным исходным кодом для Delphi и C++Builder и не требует установки Excel на машине, где он выполняется