HotXLS: точный текст уравнений линий тренда Excel в Delphi

HotXLS воспроизводит подписи уравнения и R-квадрат линии тренда Excel в Delphi через XLSTrendlineEquationLabel в модуле lxStandard. Функция строит аппроксимацию по точкам серии-владельца для линейных, экспоненциальных, логарифмических, полиномиальных (порядки 2–6), степенных линий тренда и скользящего среднего, и возвращает тот же текст, что Excel показывает в DataLabel.Text, кодовая точка в кодовую точку, либо False, когда аппроксимация не определена

Причина, по которой это вообще функция, в том, что подписи в файле нет. Диаграмма XLSX хранит элемент c:trendline с c:trendlineType, переключателями c:dispEq и c:dispRSqr и необязательным c:trendlineLbl для позиции и форматирования, но ECMA-376 DrawingML Charts не даёт подписи собственного текста. Excel пересчитывает y = 1.96x + 0.14 при каждом рисовании диаграммы. Любой Delphi-код, желающий показать, экспортировать, искать или сравнить тот текст без запуска Excel, обязан пересчитать его тоже — и пересчитать так, как это делает Excel, вплоть до пробелов вокруг знака минус

Почему нельзя просто отформатировать коэффициенты регрессии самому?

Коэффициенты, отформатированные вручную, почти никогда не совпадают с подписью Excel, потому что подпись следует нескольким мелким конвенциям, которые легко пропустить и легко проверить. HotXLS зафиксировал те конвенции по живым COM-считываниям DataLabel.Text из Excel на точечной диаграмме, а затем писал форматтер против того оракула, а не против интуиции

Конвенции, которые кусаются на практике:

• Показатели степени — обычные символы в тексте подписи. Полиномиальная подпись читается x2, а не x², а степенная — x0.9732; приподнятый вид — атрибут рендеринга диаграммы, а не часть строки
• Единственный не-ASCII символ — верхний индекс два U+00B2 в префиксе R² =
• Коэффициент ровно 1 или -1 опускается, поэтому Excel пишет y = x + 3, а не y = 1x + 3
• Члены соединяются через свои знаки с пробелами ( + и - ), тогда как ведущий отрицательный коэффициент берёт голый минус, как в y = -0.0429x2 + 2.2171x - 0.16
• Когда Excel показывает обе строки, DataLabel.Text склеивает их через U+000B — вертикальную табуляцию, а не пару CR/LF

Форматирование чисел тоже ограничено. XLSTrendlineEquationLabel печатает ноль как 0, переключается на однозначную научную запись вроде 2E+06, когда величина ниже 0,0001 или не меньше 1000000, а иначе использует фиксированную запись с четырьмя знаками после запятой до 99,9999, минус один знак за каждую следующую целую цифру и со срезанными хвостовыми нулями. Потому 1.96 появляется как 1.96, а не как 1.9600

Вызов XLSTrendlineEquationLabel из Delphi

XLSTrendlineEquationLabel принимает вид линии тренда как целочисленный ordinal TXLSXChartTrendlineType, полиномиальный порядок, открытый массив записей TXLSTrendlineLabelPoint с полями X и Y и два переключателя показа. Уравнение и строку R-квадрат он возвращает через параметры out как WideString, оставляя строку пустой, когда её переключатель выключен. Аргумент порядка важен только для xlsxTrendPolynomial; для прочих видов передавайте 0

uses
  lxStandard, lxHandleX;

procedure ShowFiveTrendlines;
const
  YS: array[0..4] of Double = (2.1, 3.9, 6.2, 8.1, 9.8);
var
  Points: array[0..4] of TXLSTrendlineLabelPoint;
  Eq, R2: WideString;
  I: Integer;
begin
  for I := 0 to 4 do
  begin
    Points[I].X := I + 1;
    Points[I].Y := YS[I];
  end;

  // Линейная: Eq = 'y = 1.96x + 0.14', R2 = 'R' + #$00B2 + ' = 0.9976'
  if XLSTrendlineEquationLabel(Ord(xlsxTrendLinear), 0, Points,
    True, True, Eq, R2) then
    Writeln(Eq, ' | ', R2);

  // Полином 2-го порядка: 'y = -0.0429x2 + 2.2171x - 0.16', R2 0.9983
  if XLSTrendlineEquationLabel(Ord(xlsxTrendPolynomial), 2, Points,
    True, True, Eq, R2) then
    Writeln(Eq, ' | ', R2);

  // Экспонента: 'y = 1.6767e0.3812x', R2 0.9386
  if XLSTrendlineEquationLabel(Ord(xlsxTrendExponential), 0, Points,
    True, True, Eq, R2) then
    Writeln(Eq, ' | ', R2);

  // Тот же текст, что Excel кладёт в DataLabel.Text при показе обеих строк
  Eq := Eq + #$000B + R2;
end;

Пять значений в том сэмпле — зафиксированный оракул Excel, который набор тестов HotXLS проверяет для каждого семейства. Логарифмическая аппроксимация возвращается как y = 4.7635ln(x) + 1.4589 с R² = 0.9519, а степенная как y = 2.0719x0.9732 с R² = 0.9978. Если ваш вывод расходится с Excel на тех же данных, библиотека и оракул не согласны, и это баг, о котором стоит сообщить, а не предпочтение округления

Как Excel считает R-квадрат для экспоненциальных и степенных линий тренда?

Для подписи линии тренда R-квадрат — квадрат корреляции Пирсона между подогнанными значениями и фактическими Y в исходном пространстве данных, и XLSTrendlineEquationLabel считает его так же. Коэффициенты экспоненциальной, логарифмической или степенной аппроксимации по-прежнему приходят из обычного метода наименьших квадратов на логарифмированных данных: ln(y) против x для экспоненты, y против ln(x) для логарифмической и ln(y) против ln(x) для степенной. Только число качества аппроксимации меряется обратно в естественном домене

Именно эту деталь большинство реимплементаций делает неверно, потому что учебниковый подход меряет R-квадрат в преобразованном пространстве, где строилась линия. На популяции оракула разница уже видна во втором знаке. Экспоненциальная аппроксимация в преобразованном пространстве даёт 0,9477, тогда как подпись Excel, считанная вживую, говорит R² = 0.9386 — корреляцию в естественном домене. Генератор подписей, переиспользующий свою регрессионную статистику из преобразованного пространства, напечатает неверное число на первой же диаграмме, которую пользователь сравнит

Два особых случая меняют формулу. Линейная линия тренда с фиксированным нулевым пересечением использует нецентрированную остаточную форму — единица минус отношение суммы квадратов остатков к сумме квадратов значений Y, — что совпадает с тем, что Excel показывает для этой конфигурации. Популяция, у которой все Y равны, не имеет дисперсии для объяснения, поэтому результат — R² = #N/A рядом с константным уравнением вроде y = 5. Непостоянная популяция, чья аппроксимация случайно вышла с нулевым наклоном, получает вместо этого R² = 0

Фиксированные пересечения и вторая перегрузка

Вторая перегрузка XLSTrendlineEquationLabel добавляет InterceptSet: Boolean и Intercept: Double перед выходными параметрами, что зеркалит элемент c:intercept линии тренда и свойства InterceptSet и Intercept класса TXLSXChartTrendline. Excel предлагает фиксированное пересечение только для линейных, экспоненциальных и полиномиальных линий тренда, и функция требует того же правила: фиксированное пересечение на логарифмической или степенной аппроксимации возвращает False, а экспоненциальной аппроксимации нужно положительное пересечение, поскольку она работает с его логарифмом

procedure LabelForTrendline(Trend: TXLSXChartTrendline;
  const Points: array of TXLSTrendlineLabelPoint);
var
  Eq, R2: WideString;
begin
  if not XLSTrendlineEquationLabel(Ord(Trend.TrendlineType), Trend.Order,
    Points, Trend.DispEq, Trend.DispRSqr, Trend.InterceptSet,
    Trend.Intercept, Eq, R2) then
  begin
    // Неопределённая аппроксимация: не выдумывайте подпись
    Writeln('No label for this trendline');
    Exit;
  end;
  if Eq <> '' then Writeln(Eq);
  if R2 <> '' then Writeln(R2);
end;

procedure AddQuadraticThroughOrigin(Series: TXLSXChartSeries);
var
  Trend: TXLSXChartTrendline;
begin
  Trend := Series.AddTrendline(xlsxTrendPolynomial);
  Trend.Order := 2;
  Trend.DispEq := True;
  Trend.DispRSqr := True;
  Trend.InterceptSet := True;
  Trend.Intercept := 0;
end;

Прямая подача собственных свойств линии тренда в перегрузку держит подпись в ногу с тем, что книга велит Excel нарисовать. Когда InterceptSet равен False, значение Intercept игнорируется, поэтому безусловная передача свойства безопасна. Фиксированное пересечение заодно снижает требование к точкам на единицу: полином порядка n обычно требует n + 1 точек, но только n, когда константный член приколочен

Какие значения X реально использует линия тренда на графике Line?

Линия тренда на обычном графике Line строится по позиции категории с основанием 1, а не по метке категории, даже когда метки — числа. Excel трактует горизонтальную ось графика Line как ось категорий, поэтому серия по годам 2019–2023 аппроксимируется как x = 1..5, и пересечение уравнения отражает это. Точечные (Scatter) и пузырьковые (Bubble) диаграммы другие: они строят аппроксимацию по совпадающим числовым значениям X каждой точки

HotXLS усвоил это на своей шкуре. Первая версия проекции семантических снимков читала числовой текст категорий как настоящий x, что давало математически валидное уравнение, которое Excel никогда бы не показал. Последующий фикс перевёл аппроксимации Line на исходную позицию категории для каждой категории, числовой или нет, и заодно определился с пропусками: пустая ячейка Y выбрасывается из аппроксимации, но поздние точки сохраняют исходный индекс вместо сползания вниз заполнять дыру. То же различие между позициями категорий и настоящими значениями стоит за календарно точными метками оси дат в диаграммах HotXLS

uses
  Math, lxStandard, lxHandleX;

// Values держит серию Line в порядке категорий; NaN помечает пустую ячейку
function LinePointsFromCategories(const Values: array of Double):
  TArray<TXLSTrendlineLabelPoint>;
var
  I, N: Integer;
begin
  SetLength(Result, Length(Values));
  N := 0;
  for I := 0 to High(Values) do
  begin
    if IsNan(Values[I]) then
      Continue;                  // пропустить пустую, сохранить поздние индексы
    Result[N].X := I + 1;        // позиция категории с основанием 1
    Result[N].Y := Values[I];
    Inc(N);
  end;
  SetLength(Result, N);
end;

Ошибитесь на этом шаге — и каждая прочая конвенция из статьи потрачена зря, потому что подпись будет корректно отформатированным текстом для неверной регрессии. Семантические снимки книг в HotXLS уже применяют правило по каждой группе построения, когда выпускают свойства EquationText и RSquaredText, поэтому снимок импортированной диаграммы несёт текст подписи, который показал бы Excel, рядом с остальным сохранённым ChartML импортированных диаграмм Excel

Когда XLSTrendlineEquationLabel возвращает False?

XLSTrendlineEquationLabel возвращает False и оставляет обе строки пустыми всякий раз, когда аппроксимация не определена, вместо публикации частичной или угаданной подписи. Функция никогда не выдаёт половину уравнения, поэтому результат True всегда значит, что обе запрошенные строки полны

• Меньше двух точек, вид тренда вне 0..5 или полиномиальный порядок вне 2..6
• Неположительный X для логарифмических или степенных аппроксимаций, либо неположительный Y для экспоненциальных или степенных, поскольку обоим нужен логарифм
• Входы NaN или бесконечные, либо любой X, Y или пересечение с величиной выше 1E50
• Слишком мало точек для полиномиального порядка, все X равны у неполиномиальной аппроксимации без фиксированного пересечения, или сингулярная полиномиальная система
• Подогнанные значения, выходящие из конечного диапазона (выше 1E100), или показатели, которые переполнились бы

Скользящее среднее — нарочитое исключение. Excel не выставляет ни уравнения, ни текста R-квадрат для линии тренда скользящего среднего, даже когда сохранённые переключатели dispEq и dispRSqr включены, поэтому функция возвращает True с обеими пустыми строками, что корректно сообщает вызывающему: рисовать нечего

Численная устойчивость и текущие пределы

Полиномиальные линии тренда — место, где наивный код теряет знаки. Сырые нормальные уравнения для аппроксимации шестого порядка по значениям X в тысячах плохо обусловлены, поэтому HotXLS центрирует и масштабирует X перед построением системы, решает её методом Гаусса—Жордана с частичным выбором ведущего элемента и лишь потом разворачивает коэффициенты обратно в исходную переменную. Линейные аппроксимации и корреляция R-квадрат по той же причине используют инкрементальное накопление среднего и дисперсии, а пересечение, являющееся лишь шумом плавающей точки относительно данных, прижимается к нулю, чтобы подпись не кончалась + 4E-17

Честные пределы — в форматировании. Функция выдаёт числа подписи Excel по умолчанию; кастомный LabelNumberFormat на подписи линии тренда сохраняется TXLSXChartTrendline, но не применяется к вычисленному тексту. Надстрочный стиль показателей в отрисованном выводе и автоматическое размещение подписи — отдельная работа, а аппроксимации очень высокой степени по данным с большим сдвигом или почти коллинеарным могут быть отвергнуты как сингулярные, а не решены. Для библиотеки, которая обязана соглашаться с Excel, отказ — более безопасный сбой

FAQ

Хранит ли XLSX-файл текст уравнения линии тренда

Нет. Элемент c:trendline хранит вид аппроксимации, порядок или период, необязательное пересечение, прогнозы и переключатели c:dispEq и c:dispRSqr, а c:trendlineLbl — раскладку и форматирование подписи. Excel вычисляет текст уравнения и R-квадрат в момент рендеринга, потому HotXLS и вычисляет его через XLSTrendlineEquationLabel

Почему полиномиальная подпись показывает x2, а не верхний индекс

Потому что именно это содержит DataLabel.Text Excel. Показатель — обычная цифра в строке, а приподнятое форматирование Excel применяет при рисовании диаграммы. Символ U+00B2 появляется только в префиксе R² = строки R-квадрат

Можно ли считать подписи для диаграмм, построенных классическим движком XLS

XLSTrendlineEquationLabel живёт в lxStandard и делится обоими движками HotXLS, так что работает с любым набором точек, который вы сумеете извлечь. Автоматическая проекция текста подписи в семантические снимки покрывает пока диаграммы OOXML; для прочих источников стройте массив TXLSTrendlineLabelPoint сами и следуйте правилу позиции категорий для графиков Line

Вычисление подписей линий тренда, авторинг диаграмм и остальная объектная модель Excel поставляются в HotXLS, нативном Delphi spreadsheet component для XLS и XLSX, без установки Excel во время выполнения