HotXLS: eksakte Excel-trendline-ligningsetiketter i Delphi

HotXLS gengiver Excel-trendline-lignings- og R-squared-etiketter i Delphi gennem XLSTrendlineEquationLabel i lxStandard-uniten. Funktionen tilpasser den ejende series punkter for lineære, eksponentielle, logaritmiske, polynomiske (orden 2 til 6), power- og moving average-trendlines og returnerer samme tekst, som Excel rapporterer i DataLabel.Text, kodepunkt for kodepunkt, eller False, når tilpasningen er udefineret

Grunden til, at dette overhovedet behøver en funktion, er, at etiketten ikke er i filen. Et XLSX-diagram gemmer et c:trendline-element med en c:trendlineType, c:dispEq- og c:dispRSqr-kontakterne og en valgfri c:trendlineLbl til position og formattering, men ECMA-376 DrawingML Charts giver etiketten ingen egen tekst. Excel genberegner y = 1.96x + 0.14 hver gang, det tegner diagrammet. Al Delphi-kode, der vil vise, eksportere, søge i eller sammenligne den tekst uden at starte Excel, må genberegne den også, og genberegne den, som Excel gør, helt ned til afstanden omkring minustegnet

Hvorfor kan du ikke bare formattere regressionskoefficienterne selv?

Håndformatterede koefficienter matcher næsten aldrig Excel-etiketten, fordi etiketten følger flere små konventioner, der er lette at overse og lette at verificere imod. HotXLS fros disse konventioner fra live Excel COM-readbacks af DataLabel.Text på et scatter-diagram og skrev derefter formatteren mod dét orakel frem for mod intuition

De konventioner, der bider i praksis:

• Eksponenter er almindelige tegn i etiketteksten. Den polynomiske etiket lyder x2, ikke x², og power-etiketten lyder x0.9732; det hævede udseende er en rendingsattribut for diagrammet, ikke en del af strengen
• Det eneste ikke-ASCII-tegn er U+00B2 hævet to i R² = -præfikset
• En koefficient på præcis 1 eller -1 droppes, så Excel skriver y = x + 3, ikke y = 1x + 3
• Led forbinder gennem deres fortegn med mellemrum ( + og - ), mens en indledende negativ koefficient tager et nøgent minustegn, som i y = -0.0429x2 + 2.2171x - 0.16
• Når Excel viser begge linjer, forbinder DataLabel.Text dem med U+000B, en vertical tab, ikke et CR/LF-par

Talformattering er også afgrænset. XLSTrendlineEquationLabel printer nul som 0, skifter til et-cifret videnskabelig notation som 2E+06, når størrelsen er under 0,0001 eller mindst 1000000, og bruger ellers fast notation med fire decimaler op til 99,9999, én færre for hvert yderligere heltalsciffer og afkortede afsluttende nuller. Det er derfor, 1.96 optræder som 1.96 og ikke 1.9600

At kalde XLSTrendlineEquationLabel fra Delphi

XLSTrendlineEquationLabel tager trendline-kinden som et heltals ordinal af TXLSXChartTrendlineType, en polynomisk orden, et open array af TXLSTrendlineLabelPoint-poster med X- og Y-felter og de to display-kontakter. Den returnerer lignings- og R-squared-strenge gennem out-parametre som WideString og efterlader en streng tom, når dens kontakt er slået fra. Order-argumentet betyder kun noget for xlsxTrendPolynomial; giv 0 med for de andre typer

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;

  // Lineær: 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);

  // Polynomial orden 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);

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

  // Samme tekst, Excel lægger i DataLabel.Text, når begge linjer vises
  Eq := Eq + #$000B + R2;
end;

De fem værdier i det eksempel er det frosne Excel-orakel, som HotXLS-testsuiten tjekker for hver familie. Den logaritmiske tilpasning kommer tilbage som y = 4.7635ln(x) + 1.4589 med R² = 0.9519, og power-tilpasningen som y = 2.0719x0.9732 med R² = 0.9978. Afviger dit output fra Excel på samme data, er biblioteket og orakelet uenige, og det er en fejl værd at rapportere, ikke en afrundingspræference

Hvordan beregner Excel R-squared for eksponentielle og power-trendlines?

For trendline-etiketten er R-squared den kvadrerede Pearson-korrelation mellem de tilpassede værdier og de faktiske Y-værdier i det originale datarum, og XLSTrendlineEquationLabel beregner det på samme måde. Koefficienterne i en eksponentiel, logaritmisk eller power-tilpasning kommer stadig fra almindelige mindste kvadraters metode på log-transformerede data: ln(y) mod x for eksponentiel, y mod ln(x) for logaritmisk og ln(y) mod ln(x) for power. Kun goodness-of-fit-tallet måles tilbage i det naturlige domæne

Dette er detaljen, de fleste reimplementeringer får forkert, fordi lærebogstilgangen måler R-squared i det transformerede rum, hvor linjen blev tilpasset. På orakelpopulationen viser forskellen sig allerede i andet decimal. Den eksponentielle tilpasning i transformerede rum giver 0,9477, mens Excels etiket, læst tilbage live, siger R² = 0.9386, hvilket er den naturlige domænekorrelation. En etiketgenerator, der genbruger sine transformerede-rums-regressionsstatistikker, ville printe det forkerte tal på det første diagram, en bruger sammenligner

To specialtilfælde ændrer formlen. En lineær trendline med et fast skæringspunkt på nul bruger den ucentrerede residualform, 1 minus residualkvadratsummen over summen af kvadrerede Y-værdier, hvilket matcher det, Excel viser for den konfiguration. En population, hvis Y-værdier alle er ens, har ingen varians at forklare, så resultatet er R² = #N/A ved siden af en konstant ligning som y = 5. En ikke-konstant population, hvis tilpasning tilfældigvis har hældning nul, får R² = 0 i stedet

Faste skæringspunkter og den anden overload

Den anden XLSTrendlineEquationLabel-overload tilføjer InterceptSet: Boolean og Intercept: Double før outputparametrene, hvilket spejler c:intercept-elementet i trendlinen og InterceptSet- og Intercept-egenskaberne af TXLSXChartTrendline. Excel tilbyder kun et fast skæringspunkt for lineære, eksponentielle og polynomiske trendlines, og funktionen håndhæver samme regel: et fast skæringspunkt på en logaritmisk eller power-tilpasning returnerer False, og en eksponentiel tilpasning behøver et positivt skæringspunkt, fordi tilpasningen arbejder med dets logaritme

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
    // Udefineret tilpasning: opfind ikke en etiket
    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;

At fodre trendlinens egne egenskaber direkte ind i overloaden holder etiketten i takt med det, projektmappen vil bede Excel om at tegne. Når InterceptSet er False, ignoreres Intercept-værdien, så at give egenskaben med betingelsesløst er sikkert. Et fast skæringspunkt sænker også punktkravet med én: en polynomisk af orden n behøver normalt n + 1 punkter, men kun n, når konstantleddet er fastlåst

Hvilke X-værdier bruger en Line-diagram-trendline reelt?

En trendline på et almindeligt Line-diagram tilpasses mod den 1-baserede kategoriposition, ikke kategorienavnet, selv når navnene er tal. Excel behandler et Line-diagrams vandrette akse som en kategoriakse, så en serie plottet over årene 2019 til 2023 tilpasses som x = 1..5, og ligningens skæringspunkt afspejler det. Scatter- og Bubble-diagrammer er anderledes: de tilpasses mod de matchende numeriske X-værdier for hvert punkt

HotXLS lærte det på den hårde måde. Den første version af den semantiske snapshot-projektion læste numerisk kategoritekst som den faktiske x, hvilket producerede en matematisk gyldig ligning, som Excel aldrig ville vise. Opfølgningsfixet flyttede Line-tilpasninger til den oprindelige kategoriposition for hver kategori, numerisk eller ej, og afgjorde også, hvad der sker med huller: en tom Y-celle udelades af tilpasningen, men senere punkter beholder deres oprindelige indeks i stedet for at glide ned for at udfylde hullet. Samme skelnen mellem kategoripositioner og rigtige værdier ligger bag de kalenderpræcise datoakse-ticks i HotXLS-diagrammer

uses
  Math, lxStandard, lxHandleX;

// Values holder en Line-serie i kategorirækkefølge; NaN markerer en tom celle
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;                  // spring den tomme over, behold senere indekser
    Result[N].X := I + 1;        // 1-baseret kategoriposition
    Result[N].Y := Values[I];
    Inc(N);
  end;
  SetLength(Result, N);
end;

Får du dette trin forkert, er alle andre konventioner i denne artikel spildt, fordi etiketten bliver korrekt formatteret tekst til den forkerte regression. Projektmappe-semantiske snapshots i HotXLS anvender allerede reglen pr. plotgruppe, når de udsender EquationText- og RSquaredText-egenskaberne, så et snapshot af et importeret diagram bærer den etikettekst, Excel ville vise, sammen med resten af det bevarede ChartML for importerede Excel-diagrammer

Hvornår returnerer XLSTrendlineEquationLabel False?

XLSTrendlineEquationLabel returnerer False og efterlader begge strenge tomme, når end tilpasningen er udefineret, i stedet for at publicere en delvis eller gættet etiket. Funktionen udsender aldrig en halv ligning, så et True-resultat betyder altid, at begge anmodede strenge er komplette

• Færre end to punkter, en trendkind uden for 0..5 eller en polynomisk orden uden for 2..6
• En ikke-positiv X til logaritmiske eller power-tilpasninger, eller en ikke-positiv Y til eksponentielle eller power-tilpasninger, da begge behøver en logaritme
• NaN- eller uendelige input, eller enhver X, Y eller skæringsværdi med en størrelse over 1E50
• For få punkter til den polynomiske orden, alle X-værdier ens for en ikke-polynomisk tilpasning uden fast skæringsværdi eller et singulært polynomisk system
• Tilpassede værdier, der forlader det endelige interval (over 1E100), eller eksponenter, der ville løbe over

Moving average er den bevidste undtagelse. Excel udstiller ingen lignings- eller R-squared-tekst for en moving average-trendline, selv når de gemte dispEq- og dispRSqr-kontakter er slået til, så funktionen returnerer True med begge strenge tomme, hvilket korrekt fortæller kalderen, at der er intet at tegne

Numerisk stabilitet og aktuelle grænser

Polynomiske trendlines er dér, naiv kode taber cifre. Rå normale ligninger for en sjetteordens tilpasning over X-værdier i tusindvis er dårligt betingede, så HotXLS centrerer og skalerer X, før systemet bygges, løser det med Gauss-Jordan-eliminering og partiel pivoting og udvider først derefter koefficienterne tilbage til den oprindelige variabel. Lineære tilpasninger og R-squared-korrelationen bruger inkrementel middel- og variansakkumulering af samme grund, og et skæringspunkt, der kun er floating-point-støj i forhold til dataene, snappes til nul, så etiketten ikke ender i + 4E-17

De ærlige grænser ligger i formatteringen. Funktionen producerer Excels standardetikettal; en custom LabelNumberFormat på trendline-etiketten persisteres af TXLSXChartTrendline men anvendes ikke på den beregnede tekst. Hævet styling af eksponenter i renderet output og automatisk etiketplacering er separat arbejde, og meget højordens tilpasninger over large-offset- eller næsten-kollineære data kan stadig afvises som singulære frem for at blive løst. For et bibliotek, der er ment at være enig med Excel, er det sikrere svigt at nægte

FAQ

Gemmer XLSX-filen trendline-ligningsteksten

Nej. c:trendline-elementet gemmer tilpasningstypen, orden eller periode, den valgfrie skæringsværdi, forecasts og c:dispEq- og c:dispRSqr-kontakterne, og c:trendlineLbl gemmer etiketlayout og -formattering. Excel beregner lignings- og R-squared-teksten ved rendertid, hvilket er grunden til, at HotXLS beregner den med XLSTrendlineEquationLabel

Hvorfor viser den polynomiske etiket x2 i stedet for et hævet tegn

For det er det, Excels DataLabel.Text indeholder. Eksponenten er et almindeligt ciffer i strengen, og Excel anvender den hævede formattering, når den tegner diagrammet. Tegnet U+00B2 optræder kun i R² = -præfikset af R-squared-linjen

Kan jeg beregne etiketter til diagrammer bygget med den klassiske XLS-motor

XLSTrendlineEquationLabel bor i lxStandard og deles af begge HotXLS-motorer, så den virker med ethvert punktsæt, du kan udtrække. Automatisk projektion af etiketteksten ind i semantiske snapshots dækker i øjeblikket OOXML-diagrammer; til andre kilder, byg selv TXLSTrendlineLabelPoint-arrayet og følg kategoripositionsreglen for Line-diagrammer

Trendline-etiketberegning, diagramforfattelse og resten af Excel-objektmodellen følger med i HotXLS, den native Delphi-regnearkskomponent til XLS og XLSX, uden Excel-installation på kørselstidspunktet