HotXLS: Delphi'de Kesin Excel Trendline Denklem Etiketleri

HotXLS, Excel trendline denklemi ve R-kare etiketlerini Delphi'de lxStandard ünitesindeki XLSTrendlineEquationLabel ile yeniden üretir. Fonksiyon, sahibi olduğu serinin noktalarını lineer, üstel, logaritmik, polinom (2-6. derece), güç ve hareketli ortalama trendline'ları için geçirir ve Excel'in DataLabel.Text'te bildirdiği metnin aynısını — kod noktası kod noktasına — döndürür; uyum tanımsızsa False verir

Bunun bir fonksiyon gerektirmesinin nedeni etiketin dosyada olmamasıdır. Bir XLSX grafiği, c:trendlineType'lı bir c:trendline öğesini, c:dispEq ile c:dispRSqr anahtarlarını ve konum ile biçimlendirme için isteğe bağlı bir c:trendlineLbl'i saklar; ama ECMA-376 DrawingML Charts etikete kendi metnini vermez. Excel y = 1.96x + 0.14 ifadesini grafiği her çizdiğinde yeniden hesaplar. Excel'i başlatmadan o metni göstermek, dışa aktarmak, aramak ya da karşılaştırmak isteyen her Delphi kodu onu yeniden hesaplamak zorundadır; üstelik eksiğin çevresindeki boşluğa kadar Excel'in yaptığı biçimde

Regresyon katsayılarını neden kendiniz biçimlemiyorsunuz?

Elle biçimlenen katsayılar Excel etiketiyle neredeyse hiç eşleşmez; çünkü etiket, gözden kaçması ve ona karşı doğrulanması kolay birkaç küçük kuralı izler. HotXLS o kuralları, bir dağılım grafiğinde DataLabel.Text'in canlı Excel COM okumalarından dondurdu ve biçimlendiriciyi sezgiye değil o referans çıktı setine göre yazdı

Pratikte ısıran kurallar:

• Üsler etiket metninde düz karakterlerdir. Polinom etiketi x²'yi değil x2'yi okur ve güç etiketi x0.9732 okur; yukarı kalkık görünüm grafiğin bir render özniteliğidir, dizgenin parçası değildir
• Tek ASCII dışı karakter, R² = önekindeki U+00B2 üst iki karakteridir
• Tam olarak 1 ya da -1 olan bir katsayı atılır; Excel y = 1x + 3'ü değil y = x + 3'ü yazar
• Terimler işaretlerinden, boşluklarla birleşir ( + ile - ); baştaki negatif katsayı ise çıplak bir eksi alır: y = -0.0429x2 + 2.2171x - 0.16 gibi
• Excel iki satırı birden gösterdiğinde DataLabel.Text onları U+000B — dikey sekme — ile birleştirir; CR/LF çiftiyle değil

Sayı biçimlendirmesi de sınırlıdır. XLSTrendlineEquationLabel sıfırı 0 olarak basar; büyüklük 0.0001'in altındayken ya da en az 1000000 olduğunda 2E+06 gibi tek haneli bilimsel gösterime geçer; aksi hâlde 99.9999'a dek dört ondalıkla sabit gösterim kullanır, her ileri tamsayı basamağı için bir basamak azalarak ve sondaki sıfırlar kırpılarak. 1.96'nın 1.9600 değil 1.96 olarak görünmesinin nedeni budur

XLSTrendlineEquationLabel'ı Delphi'den çağırmak

XLSTrendlineEquationLabel, trendline türünü TXLSXChartTrendlineType'ın bir tamsayı ordinal'i olarak, bir polinom derecesini, X ile Y alanlı TXLSTrendlineLabelPoint kayıtlarından oluşan bir açık diziyi ve iki görüntü anahtarını alır. Denklem ile R-kare dizgelerini out parametrelerinden WideString olarak döndürür; anahtarı kapalıysa o dizgeyi boş bırakır. Sıra (order) argümanı yalnızca xlsxTrendPolynomial için önemlidir; öteki türler için 0 verin

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;

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

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

  // İki satır gösterildiğinde Excel'in DataLabel.Text'e koyduğu metnin aynısı
  Eq := Eq + #$000B + R2;
end;

O örnekteki beş değer, HotXLS test setinin her aile için kontrol ettiği donmuş Excel referans çıktısıdır. Logaritmik uyum R² = 0.9519 ile y = 4.7635ln(x) + 1.4589 olarak, güç uyumu R² = 0.9978 ile y = 2.0719x0.9732 olarak döner. Çıktınız aynı veride Excel'den farklıysa kütüphane ile referans çıktı çelişiyordur; bu bildirmeye değer bir hatadır, yuvarlama tercihi değil

Excel, üstel ve güç trendline'ları için R-kareyi nasıl hesaplar?

Trendline etiketi için R-kare, özgün veri uzayında uydurulmuş değerler ile gerçek Y değerleri arasındaki Pearson korelasyonunun karesidir ve XLSTrendlineEquationLabel onu aynı biçimde hesaplar. Bir üstel, logaritmik ya da güç uyumunun katsayıları hâlâ log dönüşümlü verideki sıradan en küçük karelerden gelir: üstel için ln(y)'ye karşı x, logaritmik için y'ye karşı ln(x), güç için ln(y)'ye karşı ln(x). Yalnızca uyum iyiliği sayısı doğal alanda geriye ölçülür

Çoğu yeniden uygulamanın yanlış yaptığı ayrıntı budur; çünkü ders kitabı yaklaşımı R-kareyi doğrunun uydurulduğu dönüşümlü uzayda ölçer. Referans popülasyonda fark ikinci ondalıkta zaten görünür. Dönüşümlü uzaydaki üstel uyum 0.9477 verir; Excel'in etiketi ise canlı okunduğunda doğal alan korelasyonu olan R² = 0.9386 der. Dönüşümlü uzay regresyon istatistiklerini yeniden kullanan bir etiket üreteci, kullanıcının karşılaştıracağı ilk grafikte yanlış sayıyı basar

İki özel durum formülü değiştirir. Sabit sıfır kesişmeli bir lineer trendline, ortalama çıkarılmamış artık biçimini kullanır: artık kareler toplamının Y değerlerinin kareler toplamına bölümü eksilisi 1; Excel'in o yapılandırma için gösterdiğiyle eşleşir. Y değerlerinin tamamı eşit olan bir popülasyonun açıklanacak varyansı yoktur; sonuç y = 5 gibi sabit bir denklemin yanında R² = #N/A olur. Uyumu sıfır eğimli çıkan sabit olmayan bir popülasyon ise R² = 0 alır

Sabit kesişmeler ve ikinci aşırı yükleme

İkinci XLSTrendlineEquationLabel aşırı yüklemesi, çıktı parametrelerinden önce InterceptSet: Boolean ile Intercept: Double ekler; trendline'ın c:intercept öğesini ve TXLSXChartTrendline'in InterceptSet ile Intercept özelliklerini yansıtır. Excel sabit kesişmeyi yalnızca lineer, üstel ve polinom trendline'ları için sunar ve fonksiyon aynı kuralı uygular: logaritmik ya da güç uyumunda sabit bir kesişme False döndürür ve üstel uyum pozitif bir kesişme ister, çünkü uyum onun logaritmasıyla çalışır

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
    // Tanımsız uyum: bir etiket uydurma
    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;

Trendline'ın kendi özelliklerini doğrudan aşırı yüklemeye vermek, etiketi çalışma kitabının Excel'e çizdireceğiyle uyumlu tutar. InterceptSet Falseken Intercept değeri yok sayılır; özelliği koşulsuz geçirmek bu yüzden güvenlidir. Sabit bir kesişme ayrıca nokta gereksinimini bir azaltır: n dereceli bir polinom normalde n + 1 nokta ister ama sabit terim sabitlenmişken yalnızca n

Bir Line grafiği trendline'ı gerçekte hangi X değerlerini kullanır?

Sıradan bir Line grafiğindeki trendline, kategori etiketine değil 1 tabanlı kategori konumuna göre uydurulur; etiketler sayı olsa bile. Excel bir Line grafiğinin yatay eksenini kategori ekseni sayar; 2019 ile 2023 yılları üzerine çizilmiş bir seri x = 1..5 olarak uydurulur ve denklemin kesişmesi bunu yansıtır. Scatter ile Bubble grafikleri farklıdır: her noktanın eşleşen sayısal X değerlerine göre uydurulurlar

HotXLS bunu canı yakanarak öğrendi. Semantik anlık görüntü projeksiyonunun ilk sürümü sayısal kategori metnini gerçek x olarak okuyordu; ortaya matematiksel olarak geçerli ama Excel'in asla göstermeyeceği bir denklem çıkıyordu. İzleyen düzeltme Line uyumlarını her kategori için — sayı olsun olmasın — özgün kategori konumuna taşıdı ve boşluklarda ne olacağını da netleştirdi: boş bir Y hücresi uyumun dışında bırakılır ama sonraki noktalar orijinal indekslerini korur; boşluğu doldurmak için aşağı kaymazlar. Kategori konumları ile gerçek değerler arasındaki aynı ayrım HotXLS grafiklerinde takvim doğru tarih ekseni tick'lerinin de arkasındadır

uses
  Math, lxStandard, lxHandleX;

// Values, kategori sırasında bir Line serisi tutar; NaN boş hücreyi işaretler
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;                  // boşu atla, sonraki indeksleri koru
    Result[N].X := I + 1;        // 1 tabanlı kategori konumu
    Result[N].Y := Values[I];
    Inc(N);
  end;
  SetLength(Result, N);
end;

Bu adımı yanlış yaparsanız bu yazıdaki diğer bütün kurallar boşa gider; çünkü etiket, yanlış regresyon için doğru biçimlenmiş metin olur. HotXLS'teki çalışma kitabı semantik anlık görüntüleri EquationText ile RSquaredText özelliklerini yayarlarken kuralı çizim grubu başına uygular; böylece içe aktarılmış bir grafiğin anlık görüntüsü, içe aktarılmış Excel grafikleri için korunan ChartML'nin geri kalanının yanında Excel'in göstereceği etiket metnini taşır

XLSTrendlineEquationLabel ne zaman False döndürür?

XLSTrendlineEquationLabel, uyum tanımsız olduğunda kısmi ya da tahmini bir etiket yayımlamak yerine False döndürür ve iki dizgeyi de boş bırakır. Fonksiyon asla yarım denklem üretmez; True sonuç bu yüzden daima istenen iki dizgenin de eksiksiz olduğu anlamına gelir

• İki noktadan az olması, 0..5 dışında bir trend türü ya da 2..6 dışında bir polinom derecesi
• Logaritmik ya da güç uyumları için pozitif olmayan bir X, üstel ya da güç uyumları için pozitif olmayan bir Y; ikisi de logaritma ister
• NaN ya da sonsuz girdiler; ya da büyüklüğü 1E50'nin üzerinde herhangi bir X, Y ya da kesişme
• Polinom derecesine göre çok az nokta; sabit kesişmesiz bir polinom olmayan uyumda birbirine eşit bütün X değerleri; ya da tekil bir polinom sistemi
• Sonlu aralığı terk eden uydurulmuş değerler (1E100'ün üzerinde) ya da taşmaya yol açacak üsler

Hareketli ortalama bilinçli istisnadır. Excel, saklanan dispEq ile dispRSqr anahtarları açıkken bile hareketli ortalama trendline'ı için denklem ya da R-kare metni sunmaz; fonksiyon bu yüzden iki dizgeyi de boş bırakarak True döndürür ve çizilecek bir şey olmadığını çağırana doğru biçimde bildirir

Sayısal kararlılık ve geçerli sınırlar

Polinom trendline'ları, naif kodun basamak kaybettiği yerdir. Binler mertebesindeki X değerleri üzerinde altıncı derece bir uyum için ham normal denklemler kötü koşullanmıştır; HotXLS bu yüzden sistemi kurmadan önce X'i merkezleyip ölçekler, Gauss-Jordan eliminasyonu ile kısmi pivotlamayla çözer ve katsayıları ancak ondan sonra özgün değişkene geri açar. Lineer uyumlar ile R-kare korelasyonu aynı nedenle artımlı ortalama ve varyans birikimi kullanır ve veriye göre salt kayan nokta gürültüsü olan bir kesişme sıfıra yuvarlanır; etiket + 4E-17 ile bitmesin diye

Dürüst sınırlar biçimlemededir. Fonksiyon Excel'in varsayılan etiket sayılarını üretir; trendline etiketindeki özel bir LabelNumberFormat, TXLSXChartTrendline tarafından saklanır ama hesaplanan metne uygulanmaz. Render çıktısındaki üslerin üst simge biçimlendirmesi ile otomatik etiket yerleşimi ayrı işlerdir; büyük ofsetli ya da birbirine yakın doğrusal veri üzerindeki çok yüksek dereceli uyumlar hâlâ çözülmek yerine tekil olarak reddedilebilir. Excel ile uyuşması amaçlanan bir kütüphane için reddetmek daha güvenli başarısızlıktır

SSS

XLSX dosyası trendline denklem metnini saklar

Hayır. c:trendline öğesi uyum türünü, derece ya da periyodu, isteğe bağlı kesişmeyi, öngörüleri ve c:dispEq ile c:dispRSqr anahtarlarını saklar; c:trendlineLbl etiket yerleşimini ve biçimlendirmesini saklar. Excel denklem ile R-kare metnini render zamanında hesaplar; HotXLS onu XLSTrendlineEquationLabel ile hesaplamasının nedeni budur

Polinom etiketi neden üst simge yerine x2 gösterir

Çünkü Excel'in DataLabel.Text'i tam olarak bunu içerir. Üs, dizgede düz bir basamaktır ve Excel grafiği çizerken yukarı kalkık biçimlendirmeyi uygular. U+00B2 karakteri yalnızca R-kare satırının R² = önekinde görünür

Klasik XLS motoruyla üretilmiş grafikler için etiket hesaplayabilir miyim

XLSTrendlineEquationLabel lxStandard'da yaşar ve her iki HotXLS motoru tarafından paylaşılır; çıkarabildiğiniz her nokta kümesiyle çalışır. Etiket metninin semantik anlık görüntülere otomatik yansıtılması şu anda OOXML grafiklerini kapsar; öteki kaynaklar için TXLSTrendlineLabelPoint dizisini kendiniz kurun ve Line grafikleri için kategori-konumu kuralına uyun

Trendline etiketi hesaplaması, grafik üretimi ve Excel nesne modelinin geri kalanı XLS ve XLSX için yerel Delphi elektronik tablo bileşeni HotXLS ile gelir; çalışma zamanında Excel kurulumu gerekmez