HotXLS: equazioni di trendline Excel esatte in Delphi

HotXLS riproduce in Delphi le etichette di equazione e R-squared delle trendline di Excel attraverso XLSTrendlineEquationLabel nell'unità lxStandard. La funzione adatta i punti della serie proprietaria per trendline lineari, esponenziali, logaritmiche, polinomiali (ordine da 2 a 6), di potenza e media mobile, e restituisce lo stesso testo che Excel riporta in DataLabel.Text, code point per code point, oppure False quando l'adattamento non è definito

Il motivo per cui serve proprio una funzione è che l'etichetta non è nel file. Un grafico XLSX memorizza un elemento c:trendline con un c:trendlineType, gli interruttori c:dispEq e c:dispRSqr e un c:trendlineLbl facoltativo per posizione e formattazione, ma ECMA-376 DrawingML Charts non dà all'etichetta un testo proprio. Excel ricalcola y = 1.96x + 0.14 ogni volta che disegna il grafico. Qualunque codice Delphi che voglia mostrare, esportare, cercare o confrontare quel testo senza avviare Excel deve ricalcolarlo a sua volta, e ricalcolarlo come fa Excel, fino alla spaziatura attorno al segno meno

Perché non formattate da voi i coefficienti di regressione?

I coefficienti formattati a mano quasi mai combaciano con l'etichetta di Excel, perché l'etichetta segue parecchie piccole convenzioni facili da perdere e facili da verificare. HotXLS ha congelato quelle convenzioni da letture live via COM di DataLabel.Text di Excel su un grafico a dispersione, poi ha scritto il formatter contro quell'oracolo invece che contro l'intuizione

Le convenzioni che mordono in pratica:

• Gli esponenti sono caratteri semplici nel testo dell'etichetta. L'etichetta polinomiale recita x2, non x², e l'etichetta di potenza recita x0.9732; l'aspetto elevato è un attributo di rendering del grafico, non parte della stringa
• L'unico carattere non ASCII è la due in apice U+00B2 nel prefisso R² =
• Un coefficiente esattamente pari a 1 o -1 viene eliminato, così Excel scrive y = x + 3, non y = 1x + 3
• I termini si uniscono attraverso i loro segni con spazi ( + e - ), mentre un coefficiente negativo in testa prende un meno nudo, come in y = -0.0429x2 + 2.2171x - 0.16
• Quando Excel mostra entrambe le righe, DataLabel.Text le unisce con U+000B, un vertical tab, non una coppia CR/LF

Anche la formattazione dei numeri è delimitata. XLSTrendlineEquationLabel stampa lo zero come 0, passa alla notazione scientifica a una cifra come 2E+06 quando il modulo è sotto 0,0001 o almeno 1000000, e altrimenti usa la notazione fissa con quattro decimali fino a 99,9999, uno in meno per ogni cifra intera ulteriore, e zeri finali tolti. Ecco perché 1.96 appare come 1.96 e non 1.9600

Chiamare XLSTrendlineEquationLabel da Delphi

XLSTrendlineEquationLabel prende il tipo di trendline come ordinale intero di TXLSXChartTrendlineType, un ordine polinomiale, un open array di record TXLSTrendlineLabelPoint con campi X e Y, e i due interruttori di visualizzazione. Restituisce le stringhe di equazione e R-squared attraverso parametri out come WideString, lasciando una stringa vuota quando il suo interruttore è spento. L'argomento ordine conta solo per xlsxTrendPolynomial; passate 0 per gli altri tipi

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;

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

  // Polinomiale ordine 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);

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

  // Lo stesso testo che Excel mette in DataLabel.Text quando entrambe le righe sono mostrate
  Eq := Eq + #$000B + R2;
end;

I cinque valori in quel campione sono l'oracolo Excel congelato che la suite di test di HotXLS controlla per ogni famiglia. L'adattamento logaritmico torna come y = 4.7635ln(x) + 1.4589 con R² = 0.9519, e quello di potenza come y = 2.0719x0.9732 con R² = 0.9978. Se il vostro output differisce da Excel sugli stessi dati, la libreria e l'oracolo sono in disaccordo, ed è un bug da segnalare, non una preferenza di arrotondamento

Come calcola Excel l'R-squared per trendline esponenziali e di potenza?

Per l'etichetta della trendline, l'R-squared è la correlazione di Pearson al quadrato tra i valori adattati e i valori Y reali nello spazio dati originale, e XLSTrendlineEquationLabel lo calcola allo stesso modo. I coefficienti di un adattamento esponenziale, logaritmico o di potenza vengono comunque dai minimi quadrati ordinari su dati trasformati in log: ln(y) contro x per l'esponenziale, y contro ln(x) per il logaritmico, e ln(y) contro ln(x) per la potenza. Solo il numero di bontà dell'adattamento viene misurato di nuovo nel dominio naturale

Questo è il dettaglio che la maggior parte delle reimplementazioni sbaglia, perché l'approccio da manuale misura l'R-squared nello spazio trasformato in cui la retta è stata adattata. Sulla popolazione dell'oracolo la differenza si vede già alla seconda decimale. L'adattamento esponenziale nello spazio trasformato dà 0,9477, mentre l'etichetta di Excel, letta al volo, dice R² = 0.9386, che è la correlazione nel dominio naturale. Un generatore di etichette che riusa le proprie statistiche di regressione dello spazio trasformato stamperebbe il numero sbagliato sul primo grafico che un utente confronta

Due casi speciali cambiano la formula. Una trendline lineare con intercetta fissa a zero usa la forma residua non centrata, 1 meno la somma dei quadrati residui sulla somma dei quadrati dei valori Y, che combacia con ciò che Excel mostra per quella configurazione. Una popolazione i cui valori Y sono tutti uguali non ha varianza da spiegare, quindi il risultato è R² = #N/A accanto a un'equazione costante come y = 5. Una popolazione non costante il cui adattamento capita di avere pendenza zero riceve invece R² = 0

Intercette fisse e il secondo overload

Il secondo overload di XLSTrendlineEquationLabel aggiunge InterceptSet: Boolean e Intercept: Double prima dei parametri di output, il che rispecchia l'elemento c:intercept della trendline e le proprietà InterceptSet e Intercept di TXLSXChartTrendline. Excel offre un'intercetta fissa solo per trendline lineari, esponenziali e polinomiali, e la funzione impone la stessa regola: un'intercetta fissa su un adattamento logaritmico o di potenza restituisce False, e un adattamento esponenziale richiede un'intercetta positiva perché l'adattamento lavora con il suo 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
    // Adattamento non definito: non inventate un'etichetta
    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;

Nutrire l'overload direttamente con le proprietà della trendline tiene l'etichetta in passo con ciò che la cartella di lavoro dirà a Excel di disegnare. Quando InterceptSet è False, il valore Intercept viene ignorato, quindi passare la proprietà incondizionatamente è sicuro. Un'intercetta fissa abbassa anche il requisito di punti di uno: un polinomiale di ordine n richiede di norma n + 1 punti, ma solo n quando il termine costante è bloccato

Quali valori X usa davvero una trendline di un grafico Line?

Una trendline su un grafico Line ordinario si adatta alla posizione di categoria in base 1, non all'etichetta di categoria, anche quando le etichette sono numeri. Excel tratta l'asse orizzontale di un grafico Line come asse di categoria, quindi una serie tracciata sugli anni 2019-2023 viene adattata come x = 1..5 e l'intercetta dell'equazione lo riflette. I grafici Scatter e Bubble sono diversi: si adattano ai valori X numerici abbinati di ogni punto

HotXLS l'ha imparata alla dura. La prima versione della proiezione di snapshot semantico leggeva il testo di categoria numerico come la x vera, producendo un'equazione matematicamente valida che Excel non mostrerebbe mai. La correzione successiva ha spostato gli adattamenti Line alla posizione di categoria originale per ogni categoria, numerica o no, e ha anche stabilito cosa succede con i buchi: una cella Y vuota resta fuori dall'adattamento, ma i punti successivi conservano il proprio indice originale invece di scivolare giù a tappare il buco. La stessa distinzione tra posizioni di categoria e valori reali sta dietro ai tick di asse data accurati al calendario nei grafici HotXLS

uses
  Math, lxStandard, lxHandleX;

// Values contiene una serie Line in ordine di categoria; NaN segna una cella vuota
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 la vuota, conserva gli indici successivi
    Result[N].X := I + 1;        // posizione di categoria in base 1
    Result[N].Y := Values[I];
    Inc(N);
  end;
  SetLength(Result, N);
end;

Sbagliate questo passo e ogni altra convenzione di questo articolo è buttata, perché l'etichetta sarà testo formattato correttamente per la regressione sbagliata. Gli snapshot semantici delle cartelle di lavoro in HotXLS applicano già la regola per gruppo di tracciamento quando emettono le proprietà EquationText e RSquaredText, così lo snapshot di un grafico importato porta il testo dell'etichetta che Excel mostrerebbe accanto al resto del ChartML preservato dei grafici Excel importati

Quando XLSTrendlineEquationLabel restituisce False?

XLSTrendlineEquationLabel restituisce False e lascia entrambe le stringhe vuote ogni volta che l'adattamento non è definito, invece di pubblicare un'etichetta parziale o indovinata. La funzione non emette mai mezza equazione, quindi un risultato True significa sempre che entrambe le stringhe richieste sono complete

• Meno di due punti, un tipo di trend fuori da 0..5, o un ordine polinomiale fuori da 2..6
• Una X non positiva per adattamenti logaritmici o di potenza, o una Y non positiva per adattamenti esponenziali o di potenza, visto che entrambi richiedono un logaritmo
• Input NaN o infiniti, o qualsiasi X, Y o intercetta con modulo sopra 1E50
• Troppi pochi punti per l'ordine polinomiale, tutti i valori X uguali per un adattamento non polinomiale senza intercetta fissa, o un sistema polinomiale singolare
• Valori adattati che escono dall'intervallo finito (sopra 1E100) o esponenti che andrebbero in overflow

La media mobile è l'eccezione deliberata. Excel non espone testo di equazione o R-squared per una trendline a media mobile nemmeno quando gli interruttori memorizzati dispEq e dispRSqr sono accesi, così la funzione restituisce True con entrambe le stringhe vuote, il che dice correttamente al chiamante che non c'è nulla da disegnare

Stabilità numerica e limiti attuali

Le trendline polinomiali sono dove il codice ingenuo perde cifre. Le equazioni normali grezze per un adattamento di sesto ordine su valori X nelle migliaia sono mal condizionate, quindi HotXLS centra e scala X prima di costruire il sistema, lo risolve con eliminazione di Gauss-Jordan e pivoting parziale, e solo dopo riconduce i coefficienti alla variabile originale. Gli adattamenti lineari e la correlazione R-squared usano accumulo incrementale di media e varianza per la stessa ragione, e un'intercetta che è semplice rumore in virgola mobile rispetto ai dati viene agganciata a zero così l'etichetta non finisce in + 4E-17

I limiti onesti stanno nella formattazione. La funzione produce i numeri di etichetta predefiniti di Excel; un LabelNumberFormat personalizzato sull'etichetta della trendline viene persistito da TXLSXChartTrendline ma non applicato al testo calcolato. Lo stile in apice degli esponenti nell'output renderizzato e il piazzamento automatico dell'etichetta sono lavori a parte, e adattamenti di grado molto alto su dati a grande offset o quasi collineari possono comunque essere rifiutati come singolari invece che risolti. Per una libreria che deve essere d'accordo con Excel, rifiutare è il fallimento più sicuro

FAQ

Il file XLSX memorizza il testo dell'equazione della trendline

No. L'elemento c:trendline memorizza il tipo di adattamento, l'ordine o il periodo, l'intercetta facoltativa, le previsioni e gli interruttori c:dispEq e c:dispRSqr, e c:trendlineLbl memorizza layout e formattazione dell'etichetta. Excel calcola il testo di equazione e R-squared al momento del rendering, ed è per questo che HotXLS lo calcola con XLSTrendlineEquationLabel

Perché l'etichetta polinomiale mostra x2 invece di un apice

Perché è ciò che contiene il DataLabel.Text di Excel. L'esponente è una cifra semplice nella stringa e Excel applica la formattazione elevata quando disegna il grafico. Il carattere U+00B2 compare solo nel prefisso R² = della riga R-squared

Si possono calcolare le etichette per grafici costruiti con il motore XLS classico

XLSTrendlineEquationLabel vive in lxStandard ed è condivisa da entrambi i motori HotXLS, quindi funziona con qualunque insieme di punti siate in grado di estrarre. La proiezione automatica del testo dell'etichetta negli snapshot semantici copre attualmente i grafici OOXML; per altre sorgenti, costruite voi l'array TXLSTrendlineLabelPoint e seguite la regola della posizione di categoria per i grafici Line

Il calcolo delle etichette di trendline, la creazione di grafici e il resto del modello a oggetti Excel sono inclusi in HotXLS, il componente spreadsheet Delphi nativo per XLS e XLSX, senza necessità di installazione di Excel a runtime