HotXLS: přesné popisky rovnic trendline Excelu v Delphi

HotXLS reprodukuje v Delphi popisky rovnice a R-squared trendline Excelu přes XLSTrendlineEquationLabel v unitu lxStandard. Funkce proloží body vlastnící řady pro trendline lineární, exponenciální, logaritmické, polynomiální (řád 2 až 6), mocninné a klouzavý průměr a vrátí tentýž text, který Excel hlásí v DataLabel.Text, code point za code pointem, nebo False, když je proložení nedefinované

Důvod, proč to vůbec potřebuje funkci, je ten, že popisek v souboru není. Graf XLSX ukládá element c:trendline s c:trendlineType, přepínači c:dispEq a c:dispRSqr a volitelným c:trendlineLbl pro pozici a formátování, ale ECMA-376 DrawingML Charts nedává popisku žádný vlastní text. Excel přepočítává y = 1.96x + 0.14 pokaždé, když kreslí graf. Jakýkoli kód v Delphi, který chce ten text ukázat, exportovat, hledat v něm nebo ho srovnávat bez spuštění Excelu, ho musí přepočítat taky, a přepočítat tak, jak to dělá Excel, až po mezery kolem znaménka minus

Proč si nemůžete sami naformátovat regresní koeficienty?

Ručně formátované koeficienty se s popiskem Excelu skoro nikdy neshodnou, protože popisek následuje několik malých konvencí, snadno přehlédnutelných i snadno ověřitelných. HotXLS ty konvence zamrazil z živých Excel COM čtení zpět DataLabel.Text na scatter grafu a pak psal formátovač proti tomu orákulu, ne proti intuici

Konvence, které v praxi kousají:

• Exponenty jsou prosté znaky v textu popisku. Polynomiální popisek čte x2, ne x², a mocninný popisek čte x0.9732; zvednutý vzhled je vykreslovací atribut grafu, ne část řetězce
• Jediný ne-ASCII znak je U+00B2 horní dvojka v prefixu R² =
• Koeficient přesně 1 nebo -1 se vynechá, takže Excel píše y = x + 3, ne y = 1x + 3
• Členy se spojují přes svá znaménka s mezerami ( + a - ), zatímco úvodní záporný koeficient dostane holé minus, jako v y = -0.0429x2 + 2.2171x - 0.16
• Když Excel ukazuje obě řádky, spojí DataLabel.Text přes U+000B, vertikální tab, ne dvojici CR/LF

Formátování čísel je taky ohraničené. XLSTrendlineEquationLabel tiskne nulu jako 0, přepíná na jednocifernou vědeckou notaci jako 2E+06, když je velikost pod 0,0001 nebo aspoň 1000000, a jinak používá pevnou notaci se čtyřmi desetinnými místy do 99,9999, o jedno méně na každý další celočíselný řád, se zkrácenými koncovými nulami. Proto se 1.96 ukáže jako 1.96 a ne 1.9600

Volání XLSTrendlineEquationLabel z Delphi

XLSTrendlineEquationLabel bere druh trendline jako celočíselný ordinal TXLSXChartTrendlineType, polynomiální řád, otevřené pole záznamů TXLSTrendlineLabelPoint s poli X a Y a dva zobrazovací přepínače. Rovnici a R-squared řetězce vrací přes out parametry jako WideString a nechává řetězec prázdný, když je jeho přepínač vypnutý. Argument řádu záleží jen u xlsxTrendPolynomial; pro ostatní druhy podávejte 0

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ární: 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 řádu 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);

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

  // Týž text, který Excel dává do DataLabel.Text, když se ukazují obě řádky
  Eq := Eq + #$000B + R2;
end;

Těch pět hodnot v ukázce je zamrazené orákulum Excelu, které testovací sada HotXLS kontroluje pro každou rodinu. Logaritmické proložení se vrátí jako y = 4.7635ln(x) + 1.4589 s R² = 0.9519 a mocninné jako y = 2.0719x0.9732 s R² = 0.9978. Liší-li se váš výstup od Excelu na stejných datech, knihovna a orákulum se rozešly, a to je bug hodný nahlášení, ne preference zaokrouhlování

Jak Excel počítá R-squared pro exponenciální a mocninné trendline?

U popisku trendline je R-squared kvadrát Pearsonovy korelace mezi proloženými hodnotami a skutečnými hodnotami Y v původním datovém prostoru a XLSTrendlineEquationLabel ho počítá stejně. Koeficienty exponenciálního, logaritmického i mocninného proložení stále pocházejí z obyčejných nejmenších čtverců nad log-transformovanými daty: ln(y) proti x pro exponenciální, y proti ln(x) pro logaritmický a ln(y) proti ln(x) pro mocninný. Jen číslo goodness of fit se měří zpět v přirozené doméně

Tohle je detail, který většina reimplementací pokazí, protože učebnicový přístup měří R-squared v transformovaném prostoru, kde se linie prokládala. Na populaci orákula se rozdíl ukáže už na druhém desetinném místě. Exponenciální proložení v transformovaném prostoru dává 0,9477, zatímco popisek Excelu přečtený zpět živě říká R² = 0.9386, což je korelace přirozené domény. Generátor popisků, který znovu použije regresní statistiky z transformovaného prostoru, vytiskne špatné číslo na prvním grafu, který uživatel srovná

Dva zvláštní případy mění vzorec. Lineární trendline s pevným průsečíkem nula používá necentrický reziduální tvar, 1 mínus součet čtverců reziduí přes součet čtverců hodnot Y, což odpovídá tomu, co Excel ukazuje pro tu konfiguraci. Populace, jejíž hodnoty Y jsou všechny rovné, nemá žádný rozptyl k vysvětlení, takže výsledkem je R² = #N/A vedle konstantní rovnice jako y = 5. Nekonstantní populace, jejíž proložení má zrovna nulovou strmost, dostane místo toho R² = 0

Pevné průsečíky a druhý overload

Druhý overload XLSTrendlineEquationLabel přidává InterceptSet: Boolean a Intercept: Double před výstupní parametry, což zrcadlí element c:intercept trendline a vlastnosti InterceptSet a Intercept třídy TXLSXChartTrendline. Excel nabízí pevný průsečík jen pro lineární, exponenciální a polynomiální trendline a funkce vynucuje totéž pravidlo: pevný průsečík u logaritmického nebo mocninného proložení vrací False a exponenciální proložení potřebuje kladný průsečík, protože proložení pracuje s jeho logaritmem

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
    // Nedefinované proložení: nevymýšlejte popisek
    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;

Predání vlastních vlastností trendline rovnou do overloadu drží popisek v kroku s tím, co sešit řekne Excelu kreslit. Když je InterceptSet False, hodnota Intercept se ignoruje, takže předání vlastnosti bezpodmínečně je bezpečné. Pevný průsečík taky sníží požadavek na body o jednu: polynom řádu n normálně potřebuje n + 1 bodů, ale jen n, když je konstantní člen přibitý

Jaké hodnoty X trendline grafu Line skutečně používá?

Trendline na obyčejném grafu Line prokládá proti pozici kategorie počítané od 1, ne proti popisku kategorie, i když jsou popisky čísla. Excel bere vodorovnou osu grafu Line jako kategorickou osu, takže řada nesená přes roky 2019 až 2023 se prokládá jako x = 1..5 a průsečík rovnice to odráží. Grafy Scatter a Bubble jsou jiné: prokládají proti spárovaným numerickým hodnotám X každého bodu

HotXLS se to naučil těžkou cestou. První verze projekce semantických snapshotů četla numerický text kategorie jako skutečné x, což produkovalo matematicky validní rovnici, kterou by Excel nikdy neukázal. Následná oprava přesunula Line proložení na původní pozici kategorie pro každou kategorii, numerickou i ne, a taky urovnala, co se děje s mezerami: prázdná buňka Y se z proložení vynechá, ale pozdější body si drží svůj původní index místo sklouznutí dolů, aby díru zaplnily. Tentýž rozdíl mezi pozicemi kategorií a skutečnými hodnotami stojí za kalendářně přesné ticky datové osy v grafech HotXLS

uses
  Math, lxStandard, lxHandleX;

// Values drží řadu Line v pořadí kategorií; NaN značí prázdnou buňku
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;                  // přeskoč prázdnou, drž pozdější indexy
    Result[N].X := I + 1;        // pozice kategorie od 1
    Result[N].Y := Values[I];
    Inc(N);
  end;
  SetLength(Result, N);
end;

Pokazíte-li tenhle krok, je každá jiná konvence v tomhle článku ztracená, protože popisek bude správně naformátovaným textem pro špatnou regresi. Semantické snapshoty sešitu v HotXLS už pravidlo aplikují na plot skupinu, když vysílají vlastnosti EquationText a RSquaredText, takže snapshot importovaného grafu nese text popisku, který by Excel ukázal, vedle zbytku zachovaného ChartML pro importované grafy Excelu

Kdy XLSTrendlineEquationLabel vrací False?

XLSTrendlineEquationLabel vrací False a nechává oba řetězce prázdné, kdykoli je proložení nedefinované, místo publikace částečného nebo uhádnutého popisku. Funkce nikdy nevysílá půl rovnice, takže výsledek True vždycky znamená, že oba požadované řetězce jsou kompletní

• Méně než dva body, druh trendu mimo 0..5 nebo polynomiální řád mimo 2..6
• Nepozitivní X pro logaritmické nebo mocninné proložení, nebo nepozitivní Y pro exponenciální či mocninné, protože obojí potřebuje logaritmus
• NaN nebo nekonečné vstupy, nebo jakékoli X, Y nebo průsečík s velikostí nad 1E50
• Příliš málo bodů pro polynomiální řád, všechny hodnoty X rovné u nepolynomiálního proložení bez pevného průsečíku, nebo singulární polynomiální systém
• Proložené hodnoty opouštějící konečný rozsah (nad 1E100) nebo exponenty, které by přetekly

Klouzavý průměr je záměrná výjimka. Excel nevystavuje žádnou rovnici ani text R-squared pro trendline klouzavého průměru, i když jsou uložené přepínače dispEq a dispRSqr zapnuté, takže funkce vrací True s oběma řetězci prázdnými, což volajícímu správně říká, že kreslit není nic

Numerická stabilita a současné limity

Polynomiální trendline je místo, kde naivní kód ztrácí číslice. Surové normální rovnice pro proložení šestého řádu nad hodnotami X v tisících jsou špatně podmíněné, takže HotXLS centruje a škáluje X před stavbou systému, řeší ho Gaussovou-Jordanovou eliminací s částečným pivotováním a teprve pak rozvine koeficienty zpět do původní proměnné. Lineární proložení i R-squared korelace používají inkrementální akumulaci průměru a rozptylu ze stejného důvodu a průsečík, který je jen číselným šumem vůči datům, se snapne na nulu, aby popisek nekončil + 4E-17

Poctivé limity jsou ve formátování. Funkce produkuje defaultní popisková čísla Excelu; vlastní LabelNumberFormat na popisku trendline se přes TXLSXChartTrendline ukládá, ale na spočtený text se neaplikuje. Horní index styling exponentů ve vykresleném výstupu i automatické umístění popisku jsou samostatná práce a velmi vysoce řádové proložení nad daty s velkým posunem nebo téměř kolineárními mohou být stále odmítnuty jako singulární místo vyřešení. U knihovny, která má s Excelem souhlasit, je odmítnutí bezpečnější selhání

FAQ

Ukládá soubor XLSX text rovnice trendline

Ne. Element c:trendline ukládá typ proložení, řád nebo periodu, volitelný průsečík, forecasty a přepínače c:dispEq a c:dispRSqr a c:trendlineLbl ukládá layout a formátování popisku. Excel počítá text rovnice a R-squared při vykreslení, což je důvod, proč ho HotXLS počítá přes XLSTrendlineEquationLabel

Proč ukazuje polynomiální popisek x2 místo horního indexu

Protože to je to, co drží DataLabel.Text Excelu. Exponent je prostá číslice v řetězci a Excel aplikuje zvednuté formátování, když kreslí graf. Znak U+00B2 se objevuje jen v prefixu R² = řádku R-squared

Lze počítat popisky grafů postavených klasickým XLS enginem

XLSTrendlineEquationLabel žije v lxStandard a sdílejí ho oba engine HotXLS, takže funguje s jakoukoli sadou bodů, kterou dokážete vytáhnout. Automatická projekce textu popisku do semantických snapshotů zatím pokrývá grafy OOXML; u jiných zdrojů si postavte pole TXLSTrendlineLabelPoint sami a držte se pravidla pozice kategorie u grafů Line

Výpočet popisků trendline, autoring grafů i zbytek objektového modelu Excelu dodává HotXLS, nativní komponenta Delphi pro tabulky XLS a XLSX, bez nutnosti instalace Excelu za běhu