HotXLS: Delphi에서 정확한 Excel 추세선 방정식 레이블

HotXLS는 lxStandard 유닛의 XLSTrendlineEquationLabel로 Delphi에서 Excel 추세선 방정식과 R제곱 레이블을 재현합니다. 이 함수는 선형, 지수, 로그, 다항(2차에서 6차), 멱, 이동 평균 추세선에 대해 소속 시리즈의 점들을 피팅하고, Excel이 DataLabel.Text로 보고하는 것과 코드 포인트 단위로 같은 텍스트를 반환하거나, 피팅이 정의되지 않으면 False를 반환합니다

이게 함수가 필요한 이유는 레이블이 파일에 없기 때문입니다. XLSX 차트는 c:trendlineType과 함께 c:trendline 요소, c:dispEq와 c:dispRSqr 스위치, 위치와 서식을 위한 선택적 c:trendlineLbl을 저장하지만, ECMA-376 DrawingML Charts는 레이블에 자체 텍스트를 주지 않습니다. Excel은 차트를 그릴 때마다 y = 1.96x + 0.14를 다시 계산합니다. Excel을 띄우지 않고 그 텍스트를 보여 주거나, 내보내거나, 검색하거나, 비교하려는 Delphi 코드는 그것도 다시 계산해야 하고, 마이너스 부호 주변의 공백까지 Excel이 하는 방식으로 계산해야 합니다

회귀 계수를 그냥 직접 포매팅하면 안 되는 이유

손으로 포매팅한 계수는 Excel 레이블과 거의 일치하지 않습니다. 레이블은 놓치기 쉽고 대조해서 확인하기 쉬운 몇 가지 작은 관례를 따르기 때문입니다. HotXLS는 산점도 차트의 DataLabel.Text를 라이브 Excel COM으로 읽어 그 관례들을 얼려 두었고, 직관이 아니라 그 오라클을 향해 포매터를 썼습니다

실전에서 발을 무는 관례들입니다:

• 지수는 레이블 텍스트에서 평범한 문자입니다. 다항 레이블은 x²이 아니라 x2로 읽히고, 멱 레이블은 x0.9732로 읽힙니다. 위로 솟은 모양은 문자열의 일부가 아니라 차트의 렌더링 속성입니다
• 유일한 비 ASCII 문자는 R² = 접두어의 U+00B2 윗첨자 2입니다
• 정확히 1이나 -1인 계수는 빠지므로, Excel은 y = 1x + 3이 아니라 y = x + 3을 씁니다
• 항들은 부호를 사이에 두고 공백과 함께 붙습니다( + 와 - ). 반면 맨 앞의 음수 계수는 y = -0.0429x2 + 2.2171x - 0.16처럼 맨 마이너스 하나를 씁니다
• Excel이 두 줄을 모두 보여 줄 때 DataLabel.Text는 CR/LF 쌍이 아니라 U+000B, 수직 탭으로 연결합니다

숫자 서식도 한계가 있습니다. XLSTrendlineEquationLabel은 0을 0으로 출력하고, 크기가 0.0001 미만이거나 1000000 이상이면 2E+06 같은 한 자리 과학 표기로 전환하며, 그 외에는 99.9999까지 소수 넷의 고정 표기를 쓰고, 정수 자릿수가 늘 때마다 하나씩 줄이며, 끝의 0은 잘라 냅니다. 그래서 1.96이 1.9600이 아니라 1.96으로 나타납니다

Delphi에서 XLSTrendlineEquationLabel 호출하기

XLSTrendlineEquationLabel은 추세선 종류를 TXLSXChartTrendlineType의 정수 서수로, 다항 차수, X와 Y 필드를 가진 TXLSTrendlineLabelPoint 레코드의 열린 배열, 두 개의 표시 스위치를 받습니다. 방정식과 R제곱 문자열을 out 매개변수로 WideString으로 돌려주며, 스위치가 꺼진 문자열은 비워 둡니다. order 인자는 xlsxTrendPolynomial에만 의미가 있고 다른 종류에는 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;

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

  // 두 줄이 모두 표시될 때 Excel이 DataLabel.Text에 넣는 것과 같은 텍스트
  Eq := Eq + #$000B + R2;
end;

그 샘플의 다섯 값은 HotXLS 테스트 스위트가 모든 패밀리에 대해 확인하는 얼려 둔 Excel 오라클입니다. 로그 피팅은 R² = 0.9519와 함께 y = 4.7635ln(x) + 1.4589로 돌아오고, 멱 피팅은 R² = 0.9978과 함께 y = 2.0719x0.9732로 돌아옵니다. 같은 데이터에서 출력이 Excel과 다르다면 라이브러리와 오라클이 어긋난 것이므로, 반올림 취향이 아니라 보고할 가치가 있는 버그입니다

Excel은 지수와 멱 추세선의 R제곱을 어떻게 계산할까

추세선 레이블에서 R제곱은 원본 데이터 공간에서 피팅 값과 실제 Y 값 사이 피어슨 상관의 제곱이며, XLSTrendlineEquationLabel도 같은 방식으로 계산합니다. 지수, 로그, 멱 피팅의 계수는 여전히 로그 변환된 데이터의 평범한 최소제곱에서 나옵니다. 지수는 ln(y) 대 x, 로그는 y 대 ln(x), 멱은 ln(y) 대 ln(x)입니다. 적합도 숫자만 자연 도메인에서 되돌려 재고합니다

이것이 대부분의 재구현이 틀리는 디테일입니다. 교과서적 접근은 선이 피팅된 변환 공간에서 R제곱을 재기 때문입니다. 오라클 모집단에서 그 차이는 이미 소수 둘째 자리에서 드러납니다. 변환 공간의 지수 피팅은 0.9477을 주는 반면, 라이브로 읽어 온 Excel 레이블은 자연 도메인 상관인 R² = 0.9386을 말합니다. 변환 공간 회귀 통계를 재사용하는 레이블 생성기는 사용자가 처음 비교하는 차트에서 잘못된 숫자를 출력합니다

두 특수 사례가 수식을 바꿉니다. 고정 절편 0의 선형 추세선은 비중심화 잔차 형태, 즉 1에서 Y 값 제곱의 합에 대한 잔차 제곱합을 뺀 값을 쓰며, Excel이 그 구성에 대해 보여 주는 것과 일치합니다. Y 값이 전부 같은 모집단은 설명할 분산이 없으므로, 결과는 y = 5 같은 상수 방정식 옆에 R² = #N/A입니다. 피팅이 우연히 기울기 0을 갖는 비상수 모집단은 대신 R² = 0을 얻습니다

고정 절편과 두 번째 오버로드

두 번째 XLSTrendlineEquationLabel 오버로드는 출력 매개변수 앞에 InterceptSet: Boolean과 Intercept: Double을 더하는데, 이는 추세선의 c:intercept 요소와 TXLSXChartTrendline의 InterceptSet, Intercept 속성을 반영합니다. Excel은 선형, 지수, 다항 추세선에만 고정 절편을 제공하고 함수도 같은 규칙을 강제합니다. 로그나 멱 피팅의 고정 절편은 False를 반환하고, 지수 피팅은 그 로그로 작업하므로 양수 절편이 필요합니다

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
    // 정의되지 않은 피팅: 레이블을 지어내지 않습니다
    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;

추세선 자체 속성을 오버로드에 곧장 먹이면 레이블이 통합 문서가 Excel에 그리라고 시킬 것과 발을 맞춥니다. InterceptSet이 False면 Intercept 값은 무시되므로, 속성을 무조건 넘기는 것이 안전합니다. 고정 절편은 점 요구사항도 하나 줄입니다. n차 다항은 보통 n + 1개 점이 필요하지만 상수항이 고정되면 n개면 됩니다

Line 차트 추세선은 실제로 어느 X 값을 쓰나

평범한 Line 차트의 추세선은 카테고리 레이블이 아니라 1부터 시작하는 카테고리 위치에 대해 피팅합니다. 레이블이 숫자일 때조차 그렇습니다. Excel은 Line 차트의 수평 축을 카테고리 축으로 취급하므로, 2019에서 2023 연도에 걸쳐 그린 시리즈는 x = 1..5로 피팅되고 방정식의 절편이 그것을 반영합니다. Scatter와 Bubble 차트는 다릅니다. 각 점의 짝이 맞는 숫자 X 값에 대해 피팅합니다

HotXLS는 이것을 뼈저리게 배웠습니다. 의미 스냅샷 프로젝션의 첫 버전은 숫자 카테고리 텍스트를 실제 x로 읽어, Excel이 절대 보여 주지 않을 수학적으로 유효한 방정식을 만들었습니다. 후속 수정은 Line 피팅을 모든 카테고리, 숫자든 아니든, 원래 카테고리 위치로 옮기고, 빈틈의 운명도 정했습니다. 빈 Y 셀은 피팅에서 빠지지만 뒤 점들은 구멍을 메우려 내려가는 대신 원래 인덱스를 유지합니다. 카테고리 위치와 실제 값의 이 구별이 HotXLS 차트의 달력 정확한 날짜 축 눈금 뒤에도 있습니다

uses
  Math, lxStandard, lxHandleX;

// Values는 카테고리 순서의 Line 시리즈를 담습니다. NaN은 빈 셀 표시
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;                  // 빈 셀은 건너뛰고 뒤 인덱스는 유지
    Result[N].X := I + 1;        // 1부터 시작하는 카테고리 위치
    Result[N].Y := Values[I];
    Inc(N);
  end;
  SetLength(Result, N);
end;

이 단계를 틀리면 이 글의 다른 모든 관례가 헛됩니다. 레이블이 잘못된 회귀에 대해 올바르게 포매팅된 텍스트가 되어 버리니까요. HotXLS의 통합 문서 의미 스냅샷은 이미 EquationText와 RSquaredText 속성을 내보낼 때 플롯 그룹별로 규칙을 적용하므로, 임포트한 차트의 스냅샷은 임포트한 Excel 차트의 보존된 ChartML 나머지와 함께 Excel이 표시할 레이블 텍스트를 실어 나릅니다

XLSTrendlineEquationLabel은 언제 False를 반환하나

XLSTrendlineEquationLabel은 피팅이 정의되지 않을 때마다 부분적이거나 추측한 레이블을 발행하는 대신 False를 반환하고 두 문자열을 비워 둡니다. 함수는 방정식의 절반을 내보내는 일이 없으므로, True 결과는 항상 요청된 두 문자열이 완전하다는 뜻입니다

• 점이 둘 미만, 0..5 밖의 추세 종류, 2..6 밖의 다항 차수
• 로그나 멱 피팅의 양수가 아닌 X, 지수나 멱 피팅의 양수가 아닌 Y. 둘 다 로그가 필요하기 때문입니다
• NaN이나 무한 입력, 또는 크기가 1E50을 넘는 X, Y, 절편
• 다항 차수에 비해 너무 적은 점, 고정 절편 없는 비다항 피팅의 모든 X 값이 같은 경우, 특이한 다항 시스템
• 유한 범위(1E100 초과)를 벗어나는 피팅 값 또는 오버플로할 지수

이동 평균은 의도적인 예외입니다. Excel은 저장된 dispEq와 dispRSqr 스위치가 켜져 있어도 이동 평균 추세선에는 방정식이나 R제곱 텍스트를 노출하지 않으므로, 함수는 두 문자열을 비운 채 True를 반환해, 그릴 것이 없다는 것을 호출자에게 정확히 알려 줍니다

수치 안정성과 현재 한계

다항 추세선은 순진한 코드가 자릿수를 잃는 곳입니다. 수천 단위 X 값에 대한 6차 피팅의 raw 정규 방정식은 조건이 나쁘므로, HotXLS는 시스템을 세우기 전에 X를 중심화하고 스케일하고, 가우스-요르단 소거와 부분 피벗으로 풀고, 그다음에야 계수를 원래 변수로 다시 펼칩니다. 선형 피팅과 R제곱 상관은 같은 이유로 증분 평균과 분산 누적을 쓰고, 데이터에 비해 단순 부동소수점 노이즈인 절편은 0으로 스냅되어 레이블이 + 4E-17로 끝나지 않습니다

정직한 한계는 서식에 있습니다. 함수는 Excel의 기본 레이블 숫자를 만들어 냅니다. 추세선 레이블의 커스텀 LabelNumberFormat은 TXLSXChartTrendline이 보존하지만 계산된 텍스트에는 적용되지 않습니다. 렌더 출력에서 지수의 윗첨자 스타일링과 자동 레이블 배치는 별도 작업이고, 큰 오프셋이나 거의 공선적인 데이터 위의 매우 고차 피팅은 여전히 풀리는 대신 특이하다고 거부될 수 있습니다. Excel과 일치하기로 한 라이브러리에게 거부가 더 안전한 실패입니다

FAQ

XLSX 파일은 추세선 방정식 텍스트를 저장하나요

아니요. c:trendline 요소는 피팅 타입, 차수나 기간, 선택적 절편, 예측, c:dispEq와 c:dispRSqr 스위치를 저장하고, c:trendlineLbl은 레이블 배치와 서식을 저장합니다. Excel은 렌더 시점에 방정식과 R제곱 텍스트를 계산하며, 그래서 HotXLS는 XLSTrendlineEquationLabel로 계산합니다

다항 레이블은 왜 윗첨자 대신 x2를 보여 주나요

Excel의 DataLabel.Text에 그것이 들어 있기 때문입니다. 지수는 문자열에서 평범한 숫자이고 Excel은 차트를 그릴 때 솟은 서식을 적용합니다. U+00B2 문자는 R제곱 줄의 R² = 접두어에만 나타납니다

클래식 XLS 엔진으로 만든 차트의 레이블도 계산할 수 있나요

XLSTrendlineEquationLabel은 lxStandard에 살며 두 HotXLS 엔진이 공유하므로, 뽑아 낼 수 있는 어떤 점 집합이든 동작합니다. 레이블 텍스트의 의미 스냅샷 자동 프로젝션은 현재 OOXML 차트를 커버합니다. 다른 소스는 TXLSTrendlineLabelPoint 배열을 직접 만들고 Line 차트의 카테고리 위치 규칙을 따르십시오

추세선 레이블 계산, 차트 저작, Excel 객체 모델의 나머지는 XLS와 XLSX를 위한 네이티브 Delphi 스프레드시트 컴포넌트인 HotXLS에 들어 있으며, 실행 시점에 Excel 설치가 필요 없습니다