O HotXLS reproduz rótulos de equação de linha de tendência e R² do Excel no Delphi através do XLSTrendlineEquationLabel na unidade lxStandard. A função ajusta os pontos da série proprietária para linhas de tendência 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 para isto precisar de uma função afinal é que o rótulo não está no ficheiro. Um gráfico XLSX guarda um elemento c:trendline com um c:trendlineType, os interruptores 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 recalcula y = 1.96x + 0.14 todas as vezes que desenha o gráfico. Qualquer código Delphi que queira mostrar, exportar, pesquisar ou comparar esse texto sem arrancar o Excel tem de o recalcular também, e recalculá-lo como o Excel faz, até ao espaçamento em volta do sinal de menos
Por que não pode simplesmente formatar os coeficientes de regressão você mesmo?
Coeficientes formatados à mão quase nunca coincidem 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 Excel COM em vivo do DataLabel.Text num gráfico de dispersão, e depois escreveu o formatador contra esse oráculo em vez de contra a intuição
As convenções que mordem na prática:
- Expoentes são caracteres simples no texto do rótulo. O rótulo polinomial lê-se
x2, nãox², e o rótulo power lê-sex0.9732; o aspeto elevado é um atributo de renderização do gráfico, não parte da string - O único carácter não ASCII é o dois sobrescrito U+00B2 no prefixo
R² = - Um coeficiente exatamente 1 ou -1 é descartado, pelo que o Excel escreve
y = x + 3, nãoy = 1x + 3 - Os termos juntam-se pelos seus sinais com espaços (
+e-), enquanto um coeficiente negativo à cabeça leva um menos nu, como emy = -0.0429x2 + 2.2171x - 0.16 - Quando o Excel mostra ambas as linhas, o
DataLabel.Textjunta-as com U+000B, uma tabulação vertical, não um par CR/LF
A formatação de números também é delimitada. O XLSTrendlineEquationLabel imprime zero como 0, muda para notação científica de um dígito como 2E+06 quando a magnitude está abaixo de 0.0001 ou a partir de 1000000, e de outra forma usa notação fixa com quatro casas decimais até 99.9999, uma menos para cada dígito inteiro adicional, e zeros finais aparados. É por isso que 1.96 aparece como 1.96 e não 1.9600
Chamar o XLSTrendlineEquationLabel a partir do Delphi
O XLSTrendlineEquationLabel recebe o tipo de linha de tendência como um ordinal inteiro de TXLSXChartTrendlineType, uma ordem polinomial, um open array de registos TXLSTrendlineLabelPoint com campos X e Y, e os dois interruptores de exibição. Devolve as strings de equação e R² através de parâmetros out como WideString, deixando uma string vazia quando o seu interruptor está desligado. O argumento de ordem só importa para 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 nessa amostra são o oráculo Excel congelado que a suite de testes do HotXLS verifica para todas as famílias. O ajuste logarítmico regressa como y = 4.7635ln(x) + 1.4589 com R² = 0.9519, e o ajuste power como y = 2.0719x0.9732 com R² = 0.9978. Se a sua saída difere do Excel nos mesmos dados, a biblioteca e o oráculo discordam, e isso é um bug que vale a pena reportar, não uma preferência de arredondamento
Como calcula o Excel o R² para linhas de tendência exponencial e power?
Para o rótulo da linha de tendência, o R² é 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 calcula-o 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. Só o número de qualidade do ajuste é medido de volta no domínio natural
Este é o detalhe que a maioria das reimplementações erra, porque a abordagem dos manuais mede o R² no espaço transformado onde 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 em vivo, diz R² = 0.9386, que é a correlação no domínio natural. Um gerador de rótulos que reutilize as suas estatísticas de regressão do espaço transformado imprimiria o número errado no primeiro gráfico que um utilizador comparar
Dois casos especiais mudam a fórmula. Uma linha de tendência linear com interseção fixa em zero usa a forma de resíduos não centrada, 1 menos a soma dos quadrados dos resíduos sobre a soma dos valores Y ao quadrado, que corresponde ao 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, pelo que o resultado é R² = #N/A ao lado de uma equação constante como y = 5. Uma população não constante cujo ajuste tem por acaso declive zero recebe R² = 0 em vez disso
Interseções fixas e o segundo overload
O segundo overload do XLSTrendlineEquationLabel acrescenta InterceptSet: Boolean e Intercept: Double antes dos parâmetros de saída, o que espelha o elemento c:intercept da linha de tendência e as propriedades InterceptSet e Intercept do TXLSXChartTrendline. O Excel só oferece interseção fixa para linhas de tendência linear, exponencial e polinomial, e a função impõe a mesma regra: uma interseção fixa num ajuste logarítmico ou power devolve False, e um ajuste exponencial precisa de uma interseção positiva porque o ajuste trabalha com o 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 o overload com as próprias propriedades da linha de tendência mantém o rótulo em passo com o que o workbook dirá ao Excel desenhar. Quando InterceptSet é False, o valor Intercept é ignorado, pelo que passar a propriedade incondicionalmente é seguro. Uma interseção fixa também baixa o requisito de pontos em um: um polinómio de ordem n normalmente precisa de n + 1 pontos, mas só n quando o termo constante está fixado
Que valores X usa afinal a linha de tendência de um gráfico Line?
Uma linha de tendência num gráfico Line ordinário ajusta-se contra a posição de categoria de base 1, não o rótulo de categoria, mesmo quando os rótulos são números. O Excel trata o eixo horizontal de um gráfico Line como um eixo de categorias, pelo que uma série plotada sobre os anos 2019 a 2023 é ajustada como x = 1..5 e a interseção da equação reflete isso. Os gráficos Scatter e Bubble são diferentes: ajustam-se contra os valores X numéricos correspondentes de cada ponto
O HotXLS aprendeu isto à má. A primeira versão da projeção de fotografias semânticas lia texto de categoria numérico como o x real, o que produzia uma equação matematicamente válida que o Excel nunca mostraria. A correção de seguimento moveu os ajustes Line para a posição de categoria original para todas as categorias, numéricas ou não, e também decidiu o que acontece com buracos: uma célula Y vazia fica de fora do ajuste, mas os pontos seguintes mantêm o seu índice original em vez de escorregarem para baixo para encher o buraco. A mesma distinção entre posições de categoria e valores reais está por trás das marcações 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 pela ordem das categorias; 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; // salta o vazio, mantém índices seguintes
Result[N].X := I + 1; // posição de categoria de base 1
Result[N].Y := Values[I];
Inc(N);
end;
SetLength(Result, N);
end;Erre neste passo e todas as outras convenções deste artigo são desperdiçadas, porque o rótulo será texto corretamente formatado para a regressão errada. As fotografias semânticas de workbooks no HotXLS já aplicam a regra por grupo de plotagem quando emitem as propriedades EquationText e RSquaredText, pelo que uma fotografia de um gráfico importado transporta o texto do rótulo que o Excel mostraria ao lado do resto do ChartML preservado para gráficos Excel importados
Quando devolve False o XLSTrendlineEquationLabel?
O XLSTrendlineEquationLabel devolve False e deixa ambas as strings vazias sempre que o ajuste é indefinido, em vez de publicar um rótulo parcial ou adivinhado. A função nunca emite meia equação, pelo que um resultado True significa sempre que ambas as strings pedidas estão completas
- Menos de dois pontos, um tipo de tendência fora de 0..5, ou uma ordem polinomial fora de 2..6
- Um X não positivo para ajustes logarítmicos ou power, ou um Y não positivo para ajustes exponenciais ou power, já que ambos precisam de um logaritmo
- Entradas NaN ou infinitas, ou qualquer X, Y ou interseção com magnitude acima de 1E50
- Pontos a menos para a ordem polinomial, todos os valores X iguais num ajuste não polinomial sem interseção fixa, ou um sistema polinomial singular
- Valores ajustados que saiam do intervalo finito (acima de 1E100) ou expoentes que transbordem
A média móvel é a exceção deliberada. O Excel não expõe equação nem texto de R² para uma linha de tendência de média móvel mesmo que os interruptores guardados dispEq e dispRSqr estejam ligados, pelo que a função devolve True com ambas as strings vazias, o que diz corretamente ao chamador que não há nada a desenhar
Estabilidade numérica e limites atuais
As linhas de tendência polinomiais são onde o código ingénuo perde dígitos. Equações normais brutas para um ajuste de sexta ordem sobre valores X nos milhares são mal condicionadas, pelo que o HotXLS centra e escala X antes de construir o sistema, resolve-o com eliminação de Gauss-Jordan e pivotação parcial, e só então expande os coeficientes de volta para a variável original. Ajustes lineares e a correlação R² usam acumulação incremental de média e variância pela mesma razão, e uma interseção que seja mero ruído de vírgula flutuante relativo aos dados é apanhada para zero para que o rótulo não acabe em + 4E-17
Os limites honestos estão na formatação. A função produz os números de rótulo predefinidos do Excel; um LabelNumberFormat personalizado no rótulo da linha de tendência é persistido pelo TXLSXChartTrendline mas não aplicado ao texto calculado. O estilo sobrescrito dos expoentes na saída renderizada e a colocação automática de rótulos são trabalho separado, e ajustes de grau muito elevado sobre dados de desvio grande ou quase colineares ainda podem ser rejeitados como singulares em vez de resolvidos. Para uma biblioteca que se destina a concordar com o Excel, recusar é a falha mais segura
FAQ
O ficheiro XLSX guarda o texto da equação da linha de tendência
Não. O elemento c:trendline guarda o tipo de ajuste, ordem ou período, a interseção opcional, previsões e os interruptores c:dispEq e c:dispRSqr, e o c:trendlineLbl guarda o layout e formatação do rótulo. O Excel calcula o texto de equação e R² na altura da renderização, que é por isso que o HotXLS o calcula 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 carácter U+00B2 só aparece no prefixo R² = da linha de R²
Posso calcular rótulos para gráficos construídos com o motor XLS clássico
O XLSTrendlineEquationLabel vive na lxStandard e é partilhado por ambos os motores do HotXLS, pelo que funciona com qualquer conjunto de pontos que consiga extrair. A projeção automática do texto do rótulo em fotografias semânticas cobre atualmente gráficos OOXML; para outras fontes, construa a matriz TXLSTrendlineLabelPoint você mesmo e siga a regra de posição de categoria para gráficos Line
O cálculo de rótulos de linhas de tendência, a autoria de gráficos e o resto do modelo de objetos Excel viajam no HotXLS, o componente nativo de folhas de cálculo Delphi para XLS e XLSX, sem necessidade de instalação do Excel em tempo de execução