HotXLS: etiquetas exactas de trendline en Delphi

HotXLS reproduce las etiquetas de ecuación y R cuadrado de las trendlines de Excel en Delphi a través de XLSTrendlineEquationLabel en la unidad lxStandard. La función ajusta los puntos de la serie dueña para trendlines lineales, exponenciales, logarítmicas, polinómicas (orden 2 a 6), de potencia y de media móvil, y devuelve el mismo texto que Excel reporta en DataLabel.Text, punto de código por punto de código, o False cuando el ajuste es indefinido

La razón de que esto necesite una función es que la etiqueta no está en el archivo. Un gráfico XLSX guarda un elemento c:trendline con un c:trendlineType, los interruptores c:dispEq y c:dispRSqr y un c:trendlineLbl opcional para posición y formato, pero ECMA-376 DrawingML Charts no le da a la etiqueta texto propio. Excel recalcula y = 1.96x + 0.14 cada vez que dibuja el gráfico. Cualquier código Delphi que quiera mostrar, exportar, buscar o comparar ese texto sin arrancar Excel tiene que recalcularlo también, y recalcularlo como lo hace Excel, hasta el espaciado alrededor del signo menos

¿Por qué no puede simplemente formatear usted los coeficientes de la regresión?

Los coeficientes formateados a mano casi nunca coinciden con la etiqueta de Excel, porque la etiqueta sigue varias convenciones pequeñas fáciles de pasar por alto y fáciles de verificar. HotXLS congeló esas convenciones a partir de lecturas COM en vivo de DataLabel.Text de Excel sobre un gráfico de dispersión, y luego escribió el formateador contra ese oráculo en lugar de contra la intuición

Las convenciones que muerden en la práctica:

• Los exponentes son caracteres simples en el texto de la etiqueta. La etiqueta polinómica dice x2, no x², y la etiqueta de potencia dice x0.9732; el aspecto elevado es un atributo de renderizado del gráfico, no parte de la cadena
• El único carácter no ASCII es el superíndice dos U+00B2 en el prefijo R² =
• Un coeficiente de exactamente 1 o -1 se omite, así que Excel escribe y = x + 3, no y = 1x + 3
• Los términos se unen por sus signos con espacios ( + y - ), mientras que un coeficiente negativo inicial lleva un signo menos pelado, como en y = -0.0429x2 + 2.2171x - 0.16
• Cuando Excel muestra ambas líneas, DataLabel.Text las une con U+000B, un tabulador vertical, no un par CR/LF

El formato numérico también está acotado. XLSTrendlineEquationLabel imprime el cero como 0, cambia a notación científica de un dígito como 2E+06 cuando la magnitud está por debajo de 0.0001 o en al menos 1000000, y de lo contrario usa notación fija con cuatro decimales hasta 99.9999, uno menos por cada dígito entero adicional, y ceros finales recortados. Por eso 1.96 aparece como 1.96 y no como 1.9600

Llamar a XLSTrendlineEquationLabel desde Delphi

XLSTrendlineEquationLabel toma el tipo de trendline como un ordinal entero de TXLSXChartTrendlineType, un orden polinómico, un open array de registros TXLSTrendlineLabelPoint con campos X y Y, y los dos interruptores de visualización. Devuelve las cadenas de ecuación y R cuadrado a través de parámetros out como WideString, dejando una cadena vacía cuando su interruptor está apagado. El argumento de orden solo importa para xlsxTrendPolynomial; pase 0 para los otros tipos

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;

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

  // Polinómica de 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);

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

  // El mismo texto que Excel pone en DataLabel.Text cuando se muestran ambas líneas
  Eq := Eq + #$000B + R2;
end;

Los cinco valores de esa muestra son el oráculo de Excel congelado que la suite de pruebas de HotXLS verifica para cada familia. El ajuste logarítmico vuelve como y = 4.7635ln(x) + 1.4589 con R² = 0.9519, y el de potencia como y = 2.0719x0.9732 con R² = 0.9978. Si su salida difiere de Excel con los mismos datos, la biblioteca y el oráculo discrepan, y ese es un bug que vale reportar, no una preferencia de redondeo

¿Cómo calcula Excel el R cuadrado para trendlines exponenciales y de potencia?

Para la etiqueta de trendline, el R cuadrado es la correlación de Pearson al cuadrado entre los valores ajustados y los valores Y reales en el espacio de datos original, y XLSTrendlineEquationLabel lo calcula igual. Los coeficientes de un ajuste exponencial, logarítmico o de potencia siguen viniendo de mínimos cuadrados ordinarios sobre datos transformados con log: ln(y) contra x para exponencial, y contra ln(x) para logarítmica, y ln(y) contra ln(x) para potencia. Solo el número de bondad de ajuste se mide de vuelta en el dominio natural

Este es el detalle que la mayoría de las reimplementaciones hace mal, porque el enfoque de libro mide el R cuadrado en el espacio transformado donde se ajustó la línea. Sobre la población del oráculo la diferencia ya se ve en el segundo decimal. El ajuste exponencial en espacio transformado da 0.9477, mientras que la etiqueta de Excel, leída en vivo, dice R² = 0.9386, que es la correlación en el dominio natural. Un generador de etiquetas que reutilice sus estadísticas de regresión del espacio transformado imprimiría el número equivocado en el primer gráfico que un usuario compare

Dos casos especiales cambian la fórmula. Una trendline lineal con intercepto fijo de cero usa la forma residual sin centrar, 1 menos la suma de cuadrados residuales sobre la suma de valores Y al cuadrado, que coincide con lo que Excel muestra para esa configuración. Una población cuyos valores Y son todos iguales no tiene varianza que explicar, así que el resultado es R² = #N/A junto a una ecuación constante como y = 5. Una población no constante cuyo ajuste resulta tener pendiente cero recibe R² = 0 en su lugar

Interceptos fijos y la segunda sobrecarga

La segunda sobrecarga de XLSTrendlineEquationLabel agrega InterceptSet: Boolean y Intercept: Double antes de los parámetros de salida, lo que refleja el elemento c:intercept de la trendline y las propiedades InterceptSet y Intercept de TXLSXChartTrendline. Excel solo ofrece intercepto fijo para trendlines lineales, exponenciales y polinómicas, y la función aplica la misma regla: un intercepto fijo en un ajuste logarítmico o de potencia devuelve False, y un ajuste exponencial necesita un intercepto positivo porque el ajuste trabaja con su logaritmo

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
    // Ajuste indefinido: no invente una etiqueta
    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;

Alimentar la sobrecarga directamente con las propiedades de la propia trendline mantiene la etiqueta en sintonía con lo que el libro le dirá a Excel que dibuje. Cuando InterceptSet es False, el valor de Intercept se ignora, así que pasar la propiedad incondicionalmente es seguro. Un intercepto fijo también baja el requisito de puntos en uno: una polinómica de orden n normalmente necesita n + 1 puntos, pero solo n cuando el término constante está fijado

¿Qué valores X usa realmente la trendline de un gráfico Line?

Una trendline en un gráfico Line ordinario se ajusta contra la posición de categoría base 1, no la etiqueta de categoría, incluso cuando las etiquetas son números. Excel trata el eje horizontal de un gráfico Line como un eje de categorías, así que una serie trazada sobre los años 2019 a 2023 se ajusta como x = 1..5 y el intercepto de la ecuación lo refleja. Los gráficos Scatter y Bubble son diferentes: se ajustan contra los valores X numéricos emparejados de cada punto

HotXLS lo aprendió por las malas. La primera versión de la proyección de instantáneas semánticas leía el texto de categoría numérico como la x real, lo que producía una ecuación matemáticamente válida que Excel jamás mostraría. La corrección posterior movió los ajustes Line a la posición de categoría original para cada categoría, numérica o no, y también zanjó qué pasa con los huecos: una celda Y vacía queda fuera del ajuste, pero los puntos posteriores conservan su índice original en lugar de deslizarse hacia abajo para llenar el agujero. La misma distinción entre posiciones de categoría y valores reales está detrás de las marcas de eje de fechas con precisión de calendario en los gráficos de HotXLS

uses
  Math, lxStandard, lxHandleX;

// Values contiene una serie Line en orden de categorías; NaN marca una celda vacía
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;                  // salte el vacío, conserve los índices posteriores
    Result[N].X := I + 1;        // posición de categoría base 1
    Result[N].Y := Values[I];
    Inc(N);
  end;
  SetLength(Result, N);
end;

Equivoque este paso y todas las demás convenciones de este artículo se desperdician, porque la etiqueta será texto correctamente formateado para la regresión equivocada. Las instantáneas semánticas de libros de HotXLS ya aplican la regla por grupo de trazado cuando emiten las propiedades EquationText y RSquaredText, así que la instantánea de un gráfico importado lleva el texto de etiqueta que Excel mostraría junto al resto del ChartML preservado de gráficos de Excel importados

¿Cuándo devuelve False XLSTrendlineEquationLabel?

XLSTrendlineEquationLabel devuelve False y deja ambas cadenas vacías siempre que el ajuste sea indefinido, en lugar de publicar una etiqueta parcial o adivinada. La función nunca emite media ecuación, así que un resultado True siempre significa que ambas cadenas solicitadas están completas

• Menos de dos puntos, un tipo de trendline fuera de 0..5, o un orden polinómico fuera de 2..6
• Una X no positiva para ajustes logarítmicos o de potencia, o una Y no positiva para ajustes exponenciales o de potencia, ya que ambos necesitan un logaritmo
• Entradas NaN o infinitas, o cualquier X, Y o intercepto con magnitud superior a 1E50
• Muy pocos puntos para el orden polinómico, todos los valores X iguales para un ajuste no polinómico sin intercepto fijo, o un sistema polinómico singular
• Valores ajustados que salen del rango finito (por encima de 1E100) o exponentes que desbordarían

La media móvil es la excepción deliberada. Excel no expone texto de ecuación ni de R cuadrado para una trendline de media móvil incluso cuando los interruptores guardados dispEq y dispRSqr están activos, así que la función devuelve True con ambas cadenas vacías, lo que correctamente le dice al llamador que no hay nada que dibujar

Estabilidad numérica y límites actuales

Las trendlines polinómicas son donde el código ingenuo pierde dígitos. Las ecuaciones normales crudas para un ajuste de sexto orden sobre valores X en los miles están mal condicionadas, así que HotXLS centra y escala X antes de construir el sistema, lo resuelve con eliminación de Gauss-Jordan y pivoteo parcial, y solo entonces expande los coeficientes de vuelta a la variable original. Los ajustes lineales y la correlación de R cuadrado usan acumulación incremental de media y varianza por la misma razón, y un intercepto que es solo ruido de punto flotante relativo a los datos se ajusta a cero para que la etiqueta no termine en + 4E-17

Los límites honestos están en el formato. La función produce los números de etiqueta por defecto de Excel; un LabelNumberFormat personalizado en la etiqueta de la trendline lo persiste TXLSXChartTrendline pero no se aplica al texto calculado. El estilo de superíndice de los exponentes en la salida renderizada y la colocación automática de etiquetas son trabajo aparte, y los ajustes de grado muy alto sobre datos con desplazamientos grandes o casi colineales todavía pueden rechazarse como singulares en lugar de resolverse. Para una biblioteca que se supone debe coincidir con Excel, rehusarse es la falla más segura

FAQ

¿Guarda el archivo XLSX el texto de la ecuación de la trendline?

No. El elemento c:trendline guarda el tipo de ajuste, el orden o el periodo, el intercepto opcional, los forecast y los interruptores c:dispEq y c:dispRSqr, y c:trendlineLbl guarda el layout y formato de la etiqueta. Excel calcula el texto de la ecuación y del R cuadrado al momento de renderizar, razón por la que HotXLS lo calcula con XLSTrendlineEquationLabel

¿Por qué la etiqueta polinómica muestra x2 en lugar de un superíndice?

Porque eso es lo que contiene el DataLabel.Text de Excel. El exponente es un dígito simple en la cadena y Excel aplica el formato elevado cuando dibuja el gráfico. El carácter U+00B2 solo aparece en el prefijo R² = de la línea de R cuadrado

¿Puedo calcular etiquetas para gráficos construidos con el motor XLS clásico?

XLSTrendlineEquationLabel vive en lxStandard y la comparten ambos motores de HotXLS, así que funciona con cualquier conjunto de puntos que pueda extraer. La proyección automática del texto de etiqueta en instantáneas semánticas cubre por ahora los gráficos OOXML; para otras fuentes, construya usted el arreglo TXLSTrendlineLabelPoint y siga la regla de posición de categoría para gráficos Line

El cálculo de etiquetas de trendline, la autoría de gráficos y el resto del modelo de objetos de Excel vienen en HotXLS, el componente de hojas de cálculo Delphi nativo para XLS y XLSX, sin necesidad de instalación de Excel en tiempo de ejecución