HotXLS : étiquettes de trendline Excel exactes en Delphi

HotXLS reproduit en Delphi les étiquettes déquation et R-squared de trendlines Excel via XLSTrendlineEquationLabel dans lunité lxStandard. La fonction ajuste les points de la série propriétaire pour les trendlines linéaire, exponentielle, logarithmique, polynomiale (ordre 2 à 6), puissance et moyenne mobile, et renvoie le même texte quExcel rapporte dans DataLabel.Text, point de code pour point de code, ou False quand lajustement est indéfini

La raison pour laquelle il faut une fonction est que létiquette nest pas dans le fichier. Un graphique XLSX stocke un élément c:trendline avec un c:trendlineType, les interrupteurs c:dispEq et c:dispRSqr et un c:trendlineLbl optionnel pour la position et le formatage, mais ECMA-376 DrawingML Charts ne donne à létiquette aucun texte propre. Excel recalcule y = 1.96x + 0.14 chaque fois quil dessine le graphique. Tout code Delphi qui veut montrer, exporter, chercher ou comparer ce texte sans démarrer Excel doit le recalculer aussi, et le recalculer comme Excel le fait, jusquà lespacement autour du signe moins

Pourquoi ne pas simplement formater vous-même les coefficients de régression ?

Les coefficients formatés à la main ne correspondent presque jamais à létiquette Excel, parce que létiquette suit plusieurs petites conventions faciles à rater et faciles à vérifier. HotXLS a figé ces conventions depuis des relectures COM Excel en direct de DataLabel.Text sur un graphique en nuage de points, puis a écrit le formateur contre cet oracle plutôt que contre lintuition

Les conventions qui mordent en pratique :

• Les exposants sont des caractères simples dans le texte de létiquette. Létiquette polynomiale lit x2, pas x², et létiquette puissance lit x0.9732 ; laspect surélevé est un attribut de rendu du graphique, pas une partie de la chaîne
• Le seul caractère non ASCII est lexposant deux U+00B2 dans le préfixe R² =
• Un coefficient valant exactement 1 ou -1 est abandonné, si bien quExcel écrit y = x + 3, pas y = 1x + 3
• Les termes se rejoignent au travers de leurs signes avec des espaces ( + et - ), tandis quun coefficient négatif en tête prend un moins nu, comme dans y = -0.0429x2 + 2.2171x - 0.16
• Quand Excel montre les deux lignes, DataLabel.Text les joint avec U+000B, une tabulation verticale, pas une paire CR/LF

Le formatage des nombres est borné aussi. XLSTrendlineEquationLabel imprime zéro comme 0, bascule en notation scientifique à un chiffre telle que 2E+06 quand la magnitude est sous 0.0001 ou au moins 1000000, et emploie sinon la notation fixe avec quatre décimales jusquà 99.9999, une de moins pour chaque chiffre entier supplémentaire, et les zéros de queue rognés. Voilà pourquoi 1.96 apparaît comme 1.96 et pas 1.9600

Appeler XLSTrendlineEquationLabel depuis Delphi

XLSTrendlineEquationLabel prend lespèce de trendline comme ordinal entier de TXLSXChartTrendlineType, un ordre polynomial, un tableau ouvert denregistrements TXLSTrendlineLabelPoint avec champs X et Y, et les deux interrupteurs daffichage. Il renvoie les chaînes déquation et R-squared par des paramètres out en WideString, laissant une chaîne vide quand son interrupteur est coupé. Largument ordre ne compte que pour xlsxTrendPolynomial ; passez 0 pour les autres espèces

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;

  // Linéaire : 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);

  // Polynomiale ordre 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);

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

  // Le même texte quExcel met dans DataLabel.Text quand les deux lignes sont montrées
  Eq := Eq + #$000B + R2;
end;

Les cinq valeurs de cet échantillon sont loracle Excel figé que la suite de tests HotXLS vérifie pour chaque famille. Lajustement logarithmique revient en y = 4.7635ln(x) + 1.4589 avec R² = 0.9519, et lajustement puissance en y = 2.0719x0.9732 avec R² = 0.9978. Si votre sortie diffère dExcel sur les mêmes données, la bibliothèque et loracle sont en désaccord, et cest un bug qui vaut dêtre signalé, pas une préférence darrondi

Comment Excel calcule-t-il le R-squared pour les trendlines exponentielle et puissance ?

Pour létiquette de trendline, le R-squared est la corrélation de Pearson au carré entre les valeurs ajustées et les valeurs Y réelles dans lespace de données dorigine, et XLSTrendlineEquationLabel le calcule de la même façon. Les coefficients dun ajustement exponentiel, logarithmique ou puissance viennent quand même des moindres carrés ordinaires sur des données transformées en log : ln(y) contre x pour lexponentielle, y contre ln(x) pour la logarithmique, et ln(y) contre ln(x) pour la puissance. Seul le nombre de qualité dajustement est mesuré en retour dans le domaine naturel

Cest le détail que la plupart des réimplémentations ratent, parce que lapproche de manuel mesure le R-squared dans lespace transformé où la droite a été ajustée. Sur la population oracle la différence se voit déjà à la seconde décimale. Lajustement exponentiel dans lespace transformé donne 0.9477, tandis que létiquette dExcel, relue en direct, dit R² = 0.9386, soit la corrélation en domaine naturel. Un générateur détiquettes qui réemploierait ses statistiques de régression en espace transformé imprimerait le mauvais nombre sur le premier graphique quun utilisateur compare

Deux cas spéciaux changent la formule. Une trendline linéaire avec un intercept fixé à zéro emploie la forme résiduelle non centrée, 1 moins la somme résiduelle des carrés sur la somme des valeurs Y au carré, ce qui correspond à ce quExcel montre pour cette configuration. Une population dont les valeurs Y sont toutes égales na aucune variance à expliquer, si bien que le résultat est R² = #N/A à côté dune équation constante telle que y = 5. Une population non constante dont lajustement a par hasard une pente nulle reçoit R² = 0 à la place

Intercepts fixés et la seconde surcharge

La seconde surcharge de XLSTrendlineEquationLabel ajoute InterceptSet: Boolean et Intercept: Double avant les paramètres de sortie, ce qui reflète lélément c:intercept de la trendline et les propriétés InterceptSet et Intercept de TXLSXChartTrendline. Excel noffre un intercept fixé que pour les trendlines linéaire, exponentielle et polynomiale, et la fonction applique la même règle : un intercept fixé sur un ajustement logarithmique ou puissance renvoie False, et un ajustement exponentiel exige un intercept positif parce que lajustement travaille avec son logarithme

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
    // Ajustement indéfini : ninventez pas une étiquette
    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;

Nourrir la surcharge directement avec les propriétés propres de la trendline garde létiquette en phase avec ce que le classeur dira à Excel de dessiner. Quand InterceptSet est False, la valeur Intercept est ignorée, si bien que passer la propriété inconditionnellement est sûr. Un intercept fixé abaisse aussi lexigence de points de un : une polynomiale dordre n exige normalement n + 1 points, mais seulement n quand le terme constant est épinglé

Quelles valeurs X une trendline de graphique Line emploie-t-elle réellement ?

Une trendline sur un graphique Line ordinaire ajuste contre la position de catégorie à base 1, pas contre létiquette de catégorie, même quand les étiquettes sont des nombres. Excel traite laxe horizontal dun graphique Line comme un axe de catégories, si bien quune série tracée sur les années 2019 à 2023 est ajustée comme x = 1..5 et lintercept de léquation le reflète. Les graphiques Scatter et Bubble sont différents : ils ajustent contre les valeurs X numériques appariées de chaque point

HotXLS la appris à ses dépens. La première version de la projection de photo sémantique lisait le texte de catégorie numérique comme le x réel, ce qui produisait une équation mathématiquement valide quExcel ne montrerait jamais. La correction de suivi a déplacé les ajustements Line vers la position de catégorie dorigine pour chaque catégorie, numérique ou pas, et a aussi réglé ce qui arrive avec les trous : une cellule Y vide est laissée hors de lajustement, mais les points ultérieurs gardent leur index dorigine au lieu de glisser vers le bas pour combler le trou. La même distinction entre positions de catégories et vraies valeurs est derrière les graduations daxe de dates calendaires exactes dans les graphiques HotXLS

uses
  Math, lxStandard, lxHandleX;

// Values détient une série Line en ordre de catégories ; NaN marque une cellule vide
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;                  // saute le vide, garde les indices ultérieurs
    Result[N].X := I + 1;        // position de catégorie à base 1
    Result[N].Y := Values[I];
    Inc(N);
  end;
  SetLength(Result, N);
end;

Se tromper dans cette étape et toute autre convention de cet article est gaspillé, parce que létiquette sera un texte correctement formaté pour la mauvaise régression. Les photos sémantiques de classeurs dans HotXLS appliquent déjà la règle par groupe de tracé quand elles émettent les propriétés EquationText et RSquaredText, si bien quune photo dun graphique importé porte le texte détiquette quExcel afficherait aux côtés du reste du ChartML préservé pour les graphiques Excel importés

Quand XLSTrendlineEquationLabel renvoie-t-il False ?

XLSTrendlineEquationLabel renvoie False et laisse les deux chaînes vides chaque fois que lajustement est indéfini, plutôt que de publier une étiquette partielle ou devinée. La fonction német jamais une demi-équation, si bien quun résultat True signifie toujours que les deux chaînes demandées sont complètes

• Moins de deux points, une espèce de tendance hors de 0..5, ou un ordre polynomial hors de 2..6
• Un X non positif pour les ajustements logarithmique ou puissance, ou un Y non positif pour les ajustements exponentiel ou puissance, puisque les deux ont besoin dun logarithme
• Des entrées NaN ou infinies, ou tout X, Y ou intercept de magnitude au-dessus de 1E50
• Trop peu de points pour lordre polynomial, toutes valeurs X égales pour un ajustement non polynomial sans intercept fixé, ou un système polynomial singulier
• Des valeurs ajustées qui quittent la plage finie (au-dessus de 1E100) ou des exposants qui déborderaient

La moyenne mobile est lexception délibérée. Excel nexpose aucune équation ni texte R-squared pour une trendline de moyenne mobile même quand les interrupteurs stockés dispEq et dispRSqr sont allumés, si bien que la fonction renvoie True avec les deux chaînes vides, ce qui dit correctement à lappelant quil ny a rien à dessiner

Stabilité numérique et limites actuelles

Les trendlines polynomiales sont là où le code naïf perd des chiffres. Les équations normales brutes pour un ajustement de sixième ordre sur des valeurs X en milliers sont mal conditionnées, si bien que HotXLS centre et met à léchelle X avant de construire le système, le résout par élimination de Gauss-Jordan avec pivot partiel, et ce nest quensuite quil étend les coefficients en retour vers la variable dorigine. Les ajustements linéaires et la corrélation R-squared emploient une accumulation incrémentale de moyenne et de variance pour la même raison, et un intercept qui nest que du bruit en virgule flottante relativement aux données est collé à zéro pour que létiquette ne finisse pas en + 4E-17

Les limites honnêtes sont dans le formatage. La fonction produit les nombres détiquette par défaut dExcel ; un LabelNumberFormat personnalisé sur létiquette de trendline est persisté par TXLSXChartTrendline mais non appliqué au texte calculé. Le style exposant des exposants dans la sortie rendue et le placement automatique des étiquettes sont des travaux séparés, et des ajustements de très haut degré sur des données à grand décalage ou presque colinéaires peuvent encore être rejetés comme singuliers plutôt que résolus. Pour une bibliothèque censée sentendre avec Excel, refuser est léchec le plus sûr

FAQ

Le fichier XLSX stocke-t-il le texte de léquation de trendline

Non. Lélément c:trendline stocke le type dajustement, lordre ou la période, lintercept optionnel, les prévisions et les interrupteurs c:dispEq et c:dispRSqr, et c:trendlineLbl stocke la disposition et le formatage de létiquette. Excel calcule le texte déquation et R-squared au moment du rendu, voilà pourquoi HotXLS le calcule avec XLSTrendlineEquationLabel

Pourquoi létiquette polynomiale montre-t-elle x2 au lieu dun exposant

Parce que cest ce que contient le DataLabel.Text dExcel. Lexposant est un chiffre simple dans la chaîne et Excel applique le formatage surélevé quand il dessine le graphique. Le caractère U+00B2 napparaît que dans le préfixe R² = de la ligne R-squared

Puis-je calculer des étiquettes pour des graphiques bâtis avec le moteur XLS classique

XLSTrendlineEquationLabel vit dans lxStandard et est partagé par les deux moteurs HotXLS, si bien quil marche avec tout ensemble de points que vous pouvez extraire. La projection automatique du texte détiquette dans les photos sémantiques couvre actuellement les graphiques OOXML ; pour les autres sources, construisez vous-même le tableau TXLSTrendlineLabelPoint et suivez la règle de position de catégorie pour les graphiques Line

Le calcul détiquettes de trendline, lauthoring de graphiques et le reste du modèle dobjets Excel sont livrés dans HotXLS, le composant de tableur Delphi natif pour XLS et XLSX, sans installation dExcel exigée à lexécution