HotXLS: exakta Excel-trendlinjeetiketter i Delphi

HotXLS återskapar Excel-trendlinjeernas ekvations- och R-kvadratetiketter i Delphi via XLSTrendlineEquationLabel i uniten lxStandard. Funktionen anpassar ägarseriens punkter för linjära, exponentiella, logaritmiska, polynoma (ordning 2 till 6), potens- och glidande medelvärde-trendlinjer, och returnerar samma text Excel rapporterar i DataLabel.Text, kodpunkt för kodpunkt, eller False när anpassningen är odefinierad

Skälet att det här behöver en funktion alls är att etiketten inte finns i filen. Ett XLSX-diagram lagrar ett c:trendline-element med en c:trendlineType, omkopplarna c:dispEq och c:dispRSqr och en valfri c:trendlineLbl för position och formatering, men ECMA-376 DrawingML Charts ger etiketten ingen egen text. Excel räknar om y = 1.96x + 0.14 varje gång den ritar diagramet. Varje Delphi-kod som vill visa, exportera, söka eller jämföra den texten utan att starta Excel måste räkna om den också, och räkna om den som Excel gör, ner till mellanrummen kring minustecknet

Varför kan du inte bara formatera regressionskoefficienterna själv?

Handformaterade koefficienter matchar nästan aldrig Excel-etiketten, eftersom etiketten följer flera små konventioner som är lätta att missa och lätta att verifiera mot. HotXLS frös in de konventionerna från direkta Excel COM-avläsningar av DataLabel.Text på ett punktdiagram, och skrev sedan formateraren mot det oraklet i stället för mot intuition

Konventionerna som biter i praktiken:

• Exponenter är vanliga tecken i etikettexten. Den polynoma etiketten lyder x2, inte x², och potensetiketten lyder x0.9732; det upphöjda utseendet är en renderingsegenskap hos diagramet, inte en del av strängen
• Det enda icke-ASCII-tecknet är U+00B2, upphöjt två, i prefixet R² =
• En koefficient på exakt 1 eller -1 släpper, så Excel skriver y = x + 3, inte y = 1x + 3
• Termer fogas ihop genom sina tecken med mellanslag ( + och - ), medan en inledande negativ koefficient tar ett bart minus, som i y = -0.0429x2 + 2.2171x - 0.16
• När Excel visar båda raderna fogar DataLabel.Text ihop dem med U+000B, en vertikal tabulator, inte ett CR/LF-par

Talformateringen är avgränsad också. XLSTrendlineEquationLabel skriver noll som 0, växlar till ensiffrig vetenskaplig notation som 2E+06 när storleken är under 0.0001 eller minst 1000000, och använder annars fast notation med fyra decimaler upp till 99.9999, en färre för varje ytterligare heltalssiffra, och avslutande nollor trimmade. Det är därför 1.96 visas som 1.96 och inte 1.9600

Att anropa XLSTrendlineEquationLabel från Delphi

XLSTrendlineEquationLabel tar trendlinjeslaget som ett heltalsordningstal av TXLSXChartTrendlineType, en polynomordning, en öppen array av TXLSTrendlineLabelPoint-poster med fälten X och Y, och de två visningsomkopplarna. Den returnerar ekvations- och R-kvadratsträngarna genom out-parametrar som WideString, och lämnar en sträng tom när dess omkopplare är av. Ordningsargumentet spelar bara roll för xlsxTrendPolynomial; skicka 0 för de andra slagen

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;

  // Linjä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);

  // Polynom ordning 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);

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

  // Samma text Excel lägger i DataLabel.Text när båda raderna visas
  Eq := Eq + #$000B + R2;
end;

De fem värdena i det exemplet är det frusna Excel-oraklet som HotXLS-testsviten kontrollerar för varje familj. Den logaritmiska anpassningen kommer tillbaka som y = 4.7635ln(x) + 1.4589 med R² = 0.9519, och potensanpassningen som y = 2.0719x0.9732 med R² = 0.9978. Skiljer din utdata sig från Excel på samma data tvistar biblioteket och oraklet, och det är en bugg värd att rapportera, inte en avrundningspreferens

Hur beräknar Excel R-kvadrat för exponentiella och potenstrendlinjer?

För trendlinjeetiketten är R-kvadrat den kvadrerade Pearson-korrelationen mellan de anpassade värdena och de faktiska Y-värdena i det ursprungliga datarummet, och XLSTrendlineEquationLabel beräknar den på samma sätt. Koefficienterna för en exponentiell, logaritmisk eller potensanpassning kommer fortfarande från vanlig minsta-kvadrater på logtransformerad data: ln(y) mot x för exponentiell, y mot ln(x) för logaritmisk, och ln(y) mot ln(x) för potens. Bara anpassningsgodhetstalet mäts tillbaka i den naturliga domänen

Det här är detaljen de flesta reimplementeringar får fel, eftersom läroboksansatsen mäter R-kvadrat i det transformerade rum där linjen anpassades. På orakelpopulationen syns skillnaden redan i andra decimalen. Den exponentiella anpassningen i transformerat rum ger 0.9477, medan Excels etikett, avläst direkt, säger R² = 0.9386, vilket är den naturliga domänens korrelation. En etikettgenerator som återanvänder sin regressionsstatistik i transformerat rum skulle skriva ut fel tal på det första diagram en användare jämför

Två specialfall ändrar formeln. En linjär trendlinje med fixerad intercept på noll använder den ocentrerade residualformen, 1 minus residualkvadratsumman över summan av kvadrerade Y-värden, vilket matchar vad Excel visar för den konfigurationen. En population vars Y-värden alla är lika har ingen varians att förklara, så resultatet är R² = #N/A bredvid en konstant ekvation som y = 5. En icke-konstant population vars anpassning råkar ha noll lutning får R² = 0 i stället

Fixerade intercept och den andra overloaden

Den andra XLSTrendlineEquationLabel-overloaden lägger till InterceptSet: Boolean och Intercept: Double före utdataparametrarna, vilket speglar c:intercept-elementet hos trendlinjen och egenskaperna InterceptSet och Intercept hos TXLSXChartTrendline. Excel erbjuder bara fixerad intercept för linjära, exponentiella och polynoma trendlinjer, och funktionen tvingar samma regel: en fixerad intercept på en logaritmisk eller potensanpassning returnerar False, och en exponentiell anpassning behöver en positiv intercept eftersom anpassningen arbetar med dess logaritm

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
    // Odefinierad anpassning: hitta inte på en etikett
    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;

Att mata trendlinjens egna egenskaper rakt in i overloaden håller etiketten i takt med vad arbetsboken kommer att be Excel rita. När InterceptSet är False ignoreras Intercept-värdet, så att skicka egenskapen ovillkorligt är säkert. En fixerad intercept sänker också punktkravet med ett: en polynom av ordning n behöver normalt n + 1 punkter, men bara n när den konstanta termen är fastspikad

Vilka X-värden använder en Line-diagramtrendlinje egentligen?

En trendlinje på ett vanligt Line-diagram anpassas mot den 1-baserade kategoripositionen, inte kategorietiketten, även när etiketterna är tal. Excel behandlar ett Line-diagrams horisontella axel som en kategoriaxel, så en serie plottad över åren 2019 till 2023 anpassas som x = 1..5 och ekvationens intercept speglar det. Scatter- och Bubble-diagram är annorlunda: de anpassas mot varje punkts matchade numeriska X-värden

HotXLS lärde sig det här den hårda vägen. Den första versionen av den semantiska snapshot-projektionen läste numerisk kategoritext som det faktiska x, vilket producerade en matematiskt giltig ekvation som Excel aldrig skulle visa. Uppföljningsfixen flyttade Line-anpassningar till den ursprungliga kategoripositionen för varje kategori, numerisk eller inte, och bestämde också vad som händer med hål: en tom Y-cell lämnas utanför anpassningen, men senare punkter behåller sitt ursprungliga index i stället för att glida ner och fylla hålet. Samma distinktion mellan kategoripositioner och verkliga värden ligger bakom kalenderkorrekta datumaxeldelningar i HotXLS-diagram

uses
  Math, lxStandard, lxHandleX;

// Values håller en Line-serie i kategoriordning; NaN markerar en tom cell
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;                  // hoppa över den tomma, behåll senare indices
    Result[N].X := I + 1;        // 1-baserad kategoriposition
    Result[N].Y := Values[I];
    Inc(N);
  end;
  SetLength(Result, N);
end;

Få det här steget fel och varje annan konvention i den här artikeln är bortkastad, eftersom etiketten blir korrekt formaterad text för fel regression. Arbetsbokens semantiska snapshots i HotXLS tillämpar redan regeln per plotgrupp när de sänder ut egenskaperna EquationText och RSquaredText, så en snapshot av ett importerat diagram bär etikettexten Excel skulle visa, jämte resten av det bevarade ChartML:en för importerade Excel-diagram

När returnerar XLSTrendlineEquationLabel False?

XLSTrendlineEquationLabel returnerar False och lämnar båda strängarna tomma närhelst anpassningen är odefinierad, i stället för att publicera en partiell eller gissad etikett. Funktionen sänder aldrig ut halva en ekvation, så ett True-resultat betyder alltid att båda begärda strängarna är kompletta

• Färre än två punkter, ett trendslag utanför 0..5, eller en polynomordning utanför 2..6
• Ett icke-positivt X för logaritmiska eller potensanpassningar, eller ett icke-positivt Y för exponentiella eller potensanpassningar, eftersom båda behöver en logaritm
• NaN eller oändliga indatan, eller något X, Y eller intercept med storlek över 1E50
• För få punkter för polynomordningen, alla X-värden lika för en icke-polynom anpassning utan fixerad intercept, eller ett singulärt polynomsystem
• Anpassade värden som lämnar det ändliga intervallet (över 1E100) eller exponenter som skulle spilla

Glidande medelvärde är det medvetna undantaget. Excel exponerar ingen ekvations- eller R-kvadrattext för en glidande medelvärde-trendlinje även när de lagrade omkopplarna dispEq och dispRSqr är på, så funktionen returnerar True med båda strängarna tomma, vilket korrekt talar om för anroparen att det inte finns något att rita

Numerisk stabilitet och nuvarande gränser

Polynoma trendlinjer är där naiv kod tappar siffror. Råa normalekvationer för en sjätte ordningens anpassning över X-värden i tusental är dåligt konditionerade, så HotXLS centrerar och skalar X innan systemet byggs, löser det med Gauss-Jordan-eliminering och partiell pivotering, och expanderar först därefter koefficienterna tillbaka till den ursprungliga variabeln. Linjära anpassningar och R-kvadratkorrelationen använder inkrementell medel- och variansackumulering av samma skäl, och en intercept som bara är flyttalsbrus relativt datan snäpps till noll så att etiketten inte slutar med + 4E-17

De ärliga gränserna ligger i formateringen. Funktionen producerar Excels standardetiketttal; en anpassad LabelNumberFormat på trendlinjeetiketten persisteras av TXLSXChartTrendline men appliceras inte på den beräknade texten. Upphöjd stilisering av exponenter i renderad utdata och automatisk etikettplacering är separat arbete, och mycket höggradiga anpassningar över data med stora offset eller nära kollineär data kan fortfarande avvisas som singulära i stället för att lösas. För ett bibliotek som ska hålla med Excel är att vägra det säkrare misslyckandet

FAQ

Lagrar XLSX-filen trendlinjeens ekvationstext

Nej. Elementet c:trendline lagrar anpassningstypen, ordning eller period, den valfria intercepten, prognoser och omkopplarna c:dispEq och c:dispRSqr, och c:trendlineLbl lagrar etikettlayout och formatering. Excel beräknar ekvations- och R-kvadrattexten vid renderingstid, vilket är varför HotXLS beräknar den med XLSTrendlineEquationLabel

Varför visar den polynoma etiketten x2 i stället för upphöjt

För att det är vad Excels DataLabel.Text innehåller. Exponenten är en vanlig siffra i strängen och Excel applicerar den upphöjda formateringen när den ritar diagramet. Tecknet U+00B2 förekommer bara i prefixet R² = på R-kvadratraden

Kan jag beräkna etiketter för diagram byggda med den klassiska XLS-motorn

XLSTrendlineEquationLabel bor i lxStandard och delas av båda HotXLS-motorerna, så den fungerar med vilken punktuppsättning du kan extrahera. Automatisk projektion av etikettexten i semantiska snapshots täcker för närvarande OOXML-diagram; för andra källor, bygg TXLSTrendlineLabelPoint-arrayen själv och följ kategoripositionsregeln för Line-diagram

Trendlinjeetikettberäkning, diagramförfattande och resten av Excels objektmodell levereras i HotXLS, den nativa Delphi-kalkylarkskomponenten för XLS och XLSX, utan att Excel behöver vara installerat vid körtid