HotXLS: rótulos exatos de trendline do Excel em Delphi

O HotXLS reproduz rótulos de equação e R-squared de trendline do Excel em Delphi por meio do XLSTrendlineEquationLabel na unit lxStandard. A função ajusta os pontos da série dona para trendlines linear, exponencial, logarítmica, polinomial (ordem 2 a 6), power e média móvel, e devolve o mesmo texto que o Excel reporta em DataLabel.Text, code point por code point, ou False quando o ajuste é indefinido

A razão de isso precisar de uma função é que o rótulo não está no arquivo. Um gráfico XLSX armazena um elemento c:trendline com um c:trendlineType, as chaves c:dispEq e c:dispRSqr e um c:trendlineLbl opcional para posição e formatação, mas o ECMA-376 DrawingML Charts não dá ao rótulo texto próprio. O Excel recomputa y = 1.96x + 0.14 toda vez que desenha o gráfico. Qualquer código Delphi que queira mostrar, exportar, buscar ou comparar esse texto sem iniciar o Excel precisa recomputá-lo também, e recomputá-lo do jeito que o Excel faz, até o espaçamento em volta do sinal de menos

Por que você não pode formatar os coeficientes da regressão você mesmo?

Coeficientes formatados à mão quase nunca casam com o rótulo do Excel, porque o rótulo segue várias convenções pequenas que são fáceis de perder e fáceis de verificar. O HotXLS congelou essas convenções a partir de leituras ao vivo do COM do Excel de DataLabel.Text em um gráfico de dispersão, e então escreveu o formatador contra aquele oráculo em vez de contra a intuição

As convenções que mordem na prática:

• Exponentes são caracteres simples no texto do rótulo. O rótulo polinomial lê x2, não x², e o rótulo power lê x0.9732; a aparência elevada é um atributo de renderização do gráfico, não parte da string
• O único caractere não ASCII é o sobrescrito dois U+00B2 no prefixo R² =
• Um coeficiente de exatamente 1 ou -1 é descartado, então o Excel grava y = x + 3, não y = 1x + 3
• Os termos se unem pelos seus sinais com espaços ( + e - ), enquanto um coeficiente negativo à esquerda leva um menos sem espaços, como em y = -0.0429x2 + 2.2171x - 0.16
• Quando o Excel mostra as duas linhas, o DataLabel.Text as une com U+000B, um tab vertical, não um par CR/LF

A formatação de números também é limitada. O XLSTrendlineEquationLabel imprime zero como 0, troca para notação científica de um dígito como 2E+06 quando a magnitude está abaixo de 0.0001 ou em pelo menos 1000000, e caso contrário usa notação fixa com quatro casas decimais até 99.9999, uma a menos para cada dígito inteiro adicional, e zeros à direita aparados. É por isso que 1.96 aparece como 1.96 e não 1.9600

Chamando XLSTrendlineEquationLabel do Delphi

O XLSTrendlineEquationLabel recebe o tipo de trendline como um ordinal inteiro de TXLSXChartTrendlineType, uma ordem polinomial, um open array de registros TXLSTrendlineLabelPoint com campos X e Y, e as duas chaves de exibição. Ele devolve as strings de equação e R-squared por parâmetros out como WideString, deixando uma string vazia quando sua chave está desligada. O argumento de ordem só importa para o xlsxTrendPolynomial; passe 0 para os outros tipos

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;

  // Linear: 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);

  // Polinomial ordem 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);

  // Exponencial: 'y = 1.6767e0.3812x', R2 0.9386
  if XLSTrendlineEquationLabel(Ord(xlsxTrendExponential), 0, Points,
    True, True, Eq, R2) then
    Writeln(Eq, ' | ', R2);

  // O mesmo texto que o Excel põe no DataLabel.Text quando ambas as linhas são mostradas
  Eq := Eq + #$000B + R2;
end;

Os cinco valores dessa amostra são o oráculo congelado do Excel que a suíte de testes do HotXLS confere para cada família. O ajuste logarítmico volta como y = 4.7635ln(x) + 1.4589 com R² = 0.9519, e o power como y = 2.0719x0.9732 com R² = 0.9978. Se sua saída difere do Excel nos mesmos dados, a biblioteca e o oráculo divergem, e isso é um bug que vale reportar, não uma preferência de arredondamento

Como o Excel calcula o R-squared para trendlines exponencial e power?

Para o rótulo de trendline, o R-squared é a correlação de Pearson ao quadrado entre os valores ajustados e os valores Y reais no espaço de dados original, e o XLSTrendlineEquationLabel o calcula da mesma forma. Os coeficientes de um ajuste exponencial, logarítmico ou power ainda vêm de mínimos quadrados ordinários sobre dados transformados por log: ln(y) contra x para exponencial, y contra ln(x) para logarítmica, e ln(y) contra ln(x) para power. Apenas o número de qualidade do ajuste é medido de volta no domínio natural

Esse é o detalhe que a maioria das reimplementações erra, porque a abordagem de livro mede o R-squared no espaço transformado em que a reta foi ajustada. Na população do oráculo a diferença já aparece na segunda casa decimal. O ajuste exponencial no espaço transformado dá 0.9477, enquanto o rótulo do Excel, lido ao vivo, diz R² = 0.9386, que é a correlação no domínio natural. Um gerador de rótulos que reutilize suas estatísticas de regressão do espaço transformado imprimiria o número errado no primeiro gráfico que um usuário comparar

Dois casos especiais mudam a fórmula. Uma trendline linear com intercepto fixo de zero usa a forma residual não centrada, 1 menos a soma dos quadrados dos resíduos sobre a soma dos Y ao quadrado, que casa com o que o Excel mostra para essa configuração. Uma população cujos valores Y são todos iguais não tem variância a explicar, então o resultado é R² = #N/A ao lado de uma equação constante como y = 5. Uma população não constante cujo ajuste por acaso tem inclinação zero recebe R² = 0 em vez disso

Interceptos fixos e a segunda sobrecarga

A segunda sobrecarga do XLSTrendlineEquationLabel acrescenta InterceptSet: Boolean e Intercept: Double antes dos parâmetros de saída, o que espelha o elemento c:intercept da trendline e as propriedades InterceptSet e Intercept do TXLSXChartTrendline. O Excel só oferece intercepto fixo para trendlines linear, exponencial e polinomial, e a função impõe a mesma regra: um intercepto fixo em um ajuste logarítmico ou power devolve False, e um ajuste exponencial precisa de intercepto positivo porque o ajuste trabalha com seu logaritmo

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
    // Ajuste indefinido: não invente um rótulo
    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;

Alimentar a sobrecarga direto com as propriedades da própria trendline mantém o rótulo em passo com o que a pasta de trabalho vai dizer ao Excel para desenhar. Quando o InterceptSet é False, o valor de Intercept é ignorado, então passar a propriedade incondicionalmente é seguro. Um intercepto fixo também abaixa o requisito de pontos em um: um polinômio de ordem n normalmente precisa de n + 1 pontos, mas apenas n quando o termo constante está fixado

Que valores de X uma trendline de gráfico Line realmente usa?

Uma trendline em um gráfico Line comum ajusta contra a posição de categoria base 1, não contra o rótulo da categoria, mesmo quando os rótulos são números. O Excel trata o eixo horizontal de um gráfico Line como eixo de categoria, então uma série plotada sobre os anos 2019 a 2023 é ajustada como x = 1..5 e o intercepto da equação reflete isso. Gráficos Scatter e Bubble são diferentes: eles ajustam contra os valores numéricos X correspondentes de cada ponto

O HotXLS aprendeu isso da maneira difícil. A primeira versão da projeção de snapshot semântico lia texto de categoria numérico como o x real, o que produzia uma equação matematicamente válida que o Excel jamais mostraria. A correção de acompanhamento moveu os ajustes Line para a posição de categoria original para toda categoria, numérica ou não, e também decidiu o que acontece com buracos: uma célula Y vazia fica de fora do ajuste, mas pontos posteriores mantêm seu índice original em vez de deslizar para baixo para preencher o buraco. A mesma distinção entre posições de categoria e valores reais está por trás dos ticks de eixo de datas com precisão de calendário nos gráficos do HotXLS

uses
  Math, lxStandard, lxHandleX;

// Values guarda uma série Line em ordem de categoria; NaN marca uma célula vazia
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;                  // pula a vazia, mantém índices posteriores
    Result[N].X := I + 1;        // posição de categoria base 1
    Result[N].Y := Values[I];
    Inc(N);
  end;
  SetLength(Result, N);
end;

Errar esse passo e toda outra convenção deste artigo é desperdiçada, porque o rótulo será texto corretamente formatado para a regressão errada. Snapshots semânticos de pasta de trabalho no HotXLS já aplicam a regra por grupo de plotagem quando emitem as propriedades EquationText e RSquaredText, então um snapshot de um gráfico importado carrega o texto de rótulo que o Excel mostraria ao lado do resto do ChartML preservado para gráficos Excel importados

Quando o XLSTrendlineEquationLabel devolve False?

O XLSTrendlineEquationLabel devolve False e deixa ambas as strings vazias sempre que o ajuste é indefinido, em vez de publicar um rótulo parcial ou chutado. A função nunca emite metade de uma equação, então um resultado True sempre significa que ambas as strings pedidas estão completas

• Menos de dois pontos, um tipo de trend fora de 0..5, ou uma ordem polinomial fora de 2..6
• Um X não positivo para ajustes logarítmico ou power, ou um Y não positivo para exponencial ou power, já que ambos precisam de um logaritmo
• Entradas NaN ou infinitas, ou qualquer X, Y ou intercepto com magnitude acima de 1E50
• Pontos insuficientes para a ordem polinomial, todos os X iguais para um ajuste não polinomial sem intercepto fixo, ou um sistema polinomial singular
• Valores ajustados que saem da faixa finita (acima de 1E100) ou expoentes que estourariam

Média móvel é a exceção deliberada. O Excel não expõe equação nem texto de R-squared para uma trendline de média móvel mesmo quando as chaves armazenadas dispEq e dispRSqr estão ligadas, então a função devolve True com ambas as strings vazias, o que corretamente diz ao chamador que não há nada a desenhar

Estabilidade numérica e limites atuais

Trendlines polinomiais é onde código ingênuo perde dígitos. Equações normais cruas para um ajuste de sexta ordem sobre valores X nos milhares são mal condicionadas, então o HotXLS centra e escala X antes de montar o sistema, resolve-o com eliminação de Gauss-Jordan e pivotamento parcial, e só então expande os coeficientes de volta para a variável original. Ajustes lineares e a correlação de R-squared usam acumulação incremental de média e variância pela mesma razão, e um intercepto que é mero ruído de ponto flutuante relativo aos dados é encaixado em zero para o rótulo não terminar em + 4E-17

Os limites honestos estão na formatação. A função produz os números de rótulo padrão do Excel; um LabelNumberFormat customizado no rótulo da trendline é persistido pelo TXLSXChartTrendline mas não aplicado ao texto computado. Estilização sobrescrita de expoentes na saída renderizada e posicionamento automático de rótulos são trabalhos separados, e ajustes de grau muito alto sobre dados de offset grande ou quase colineares ainda podem ser rejeitados como singulares em vez de resolvidos. Para uma biblioteca que tem a pretensão de concordar com o Excel, recusar é a falha mais segura

FAQ

O arquivo XLSX armazena o texto da equação de trendline

Não. O elemento c:trendline armazena o tipo de ajuste, ordem ou período, o intercepto opcional, previsões e as chaves c:dispEq e c:dispRSqr, e o c:trendlineLbl armazena layout e formatação do rótulo. O Excel computa o texto de equação e R-squared no momento da renderização, e é por isso que o HotXLS o computa com o XLSTrendlineEquationLabel

Por que o rótulo polinomial mostra x2 em vez de um sobrescrito

Porque é isso que o DataLabel.Text do Excel contém. O expoente é um dígito simples na string e o Excel aplica a formatação elevada quando desenha o gráfico. O caractere U+00B2 só aparece no prefixo R² = da linha de R-squared

Posso calcular rótulos para gráficos criados com o engine XLS clássico

O XLSTrendlineEquationLabel vive na lxStandard e é compartilhado pelos dois engines do HotXLS, então funciona com qualquer conjunto de pontos que você consiga extrair. A projeção automática do texto de rótulo em snapshots semânticos cobre atualmente gráficos OOXML; para outras origens, monte o array de TXLSTrendlineLabelPoint você mesmo e siga a regra de posição de categoria para gráficos Line

Computação de rótulos de trendline, autoria de gráficos e o resto do modelo de objetos Excel vêm no HotXLS, o componente de planilhas Delphi nativo para XLS e XLSX, sem instalação do Excel exigida em tempo de execução