Etiquetas exactas de líneas de tendencia de Excel en Delphi

HotXLS reproduce las etiquetas de ecuación y R cuadrado de líneas de tendencia de Excel en Delphi mediante XLSTrendlineEquationLabel en la unidad lxStandard. La función ajusta los puntos de la serie propietaria para líneas de tendencia lineal, exponencial, logarítmica, polinómica (orden 2 a 6), potencial y de media móvil, y devuelve el mismo texto que Excel informa 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 fichero. 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 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 podéis simplemente formatear vosotros los coeficientes de regresión

Los coeficientes formateados a mano casi nunca casan con la etiqueta de Excel, porque la etiqueta sigue varias convenciones pequeñas fáciles de perder y fáciles de verificar. HotXLS congeló esas convenciones a partir de lecturas COM en vivo de DataLabel.Text en 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 planos en el texto de la etiqueta. La etiqueta polinómica dice x2, no x², y la potencial dice x0.9732; el aspecto elevado es un atributo de renderizado del gráfico, no parte del string
• 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 elimina, así que Excel escribe y = x + 3, no y = 1x + 3
• Los términos se unen a través de sus signos con espacios ( + y - ), mientras que un coeficiente negativo inicial lleva un 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 formateo 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 si no usa notación fija con cuatro decimales hasta 99,9999, uno menos por cada dígito entero más, y con los 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 línea de tendencia como ordinal entero de TXLSXChartTrendlineType, un orden polinómico, un open array de registros TXLSTrendlineLabelPoint con campos X e Y, y los dos interruptores de visualización. Devuelve las cadenas de ecuación y R cuadrado mediante parámetros out como WideString, dejando un string vacío cuando su interruptor está apagado. El argumento de orden solo importa para xlsxTrendPolynomial; pasad 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ómico 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 comprueba para cada familia. El ajuste logarítmico vuelve como y = 4.7635ln(x) + 1.4589 con R² = 0.9519, y el potencial como y = 2.0719x0.9732 con R² = 0.9978. Si vuestra salida difiere de Excel con los mismos datos, la biblioteca y el oráculo discrepan, y eso es un bug que merece reportarse, no una preferencia de redondeo

Cómo calcula Excel el R cuadrado para líneas de tendencia exponencial y potencial

Para la etiqueta de la línea de tendencia, 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 potencial siguen viniendo de mínimos cuadrados ordinarios sobre datos transformados con log: ln(y) contra x para exponencial, y contra ln(x) para logarítmico, y ln(y) contra ln(x) para potencial. Solo el número de bondad de ajuste se mide de vuelta en el dominio natural

Este es el detalle que más reimplementaciones torcen, porque el enfoque de libro de texto mide el R cuadrado en el espacio transformado donde se ajustó la recta. 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 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 compare un usuario

Dos casos especiales cambian la fórmula. Una línea de tendencia lineal con intersección fija de cero usa la forma residual sin centrar, 1 menos la suma de residuos al cuadrado 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 tiene pendiente cero recibe R² = 0 en su lugar

Intersecciones fijas y la segunda sobrecarga

La segunda sobrecarga de XLSTrendlineEquationLabel añade InterceptSet: Boolean e Intercept: Double antes de los parámetros de salida, lo que refleja el elemento c:intercept de la línea de tendencia y las propiedades InterceptSet e Intercept de TXLSXChartTrendline. Excel solo ofrece intersección fija para líneas de tendencia lineal, exponencial y polinómica, y la función impone la misma regla: una intersección fija en un ajuste logarítmico o potencial devuelve False, y un ajuste exponencial necesita una intersección positiva 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 inventéis 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 con las propiedades de la propia línea de tendencia 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 sin condición es seguro. Una intersección fija también baja el requisito de puntos en uno: un polinómico de orden n normalmente necesita n + 1 puntos, pero solo n cuando el término constante está fijado

Qué valores X usa realmente una línea de tendencia de gráfico Line

Una línea de tendencia en un gráfico Line ordinario ajusta contra la posición de categoría 1-based, no contra la etiqueta de categoría, incluso cuando las etiquetas son números. Excel trata el eje horizontal de un gráfico Line como eje de categorías, así que una serie trazada sobre los años 2019 a 2023 se ajusta como x = 1..5 y la intersección de la ecuación refleja eso. Scatter y Bubble son distintos: 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 texto de categoría numérico como la x real, lo que producía una ecuación matemáticamente válida que Excel nunca mostraría. El arreglo de seguimiento movió los ajustes Line a la posición de categoría original para cada categoría, numérica o no, y también asentó qué pasa con los huecos: una celda Y vacía se queda fuera del ajuste, pero los puntos posteriores conservan su índice original en lugar de deslizarse 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 guarda una serie Line en orden de categoría; 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;                  // saltad el vacío, mantened los índices posteriores
    Result[N].X := I + 1;        // posición de categoría 1-based
    Result[N].Y := Values[I];
    Inc(N);
  end;
  SetLength(Result, N);
end;

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

Cuándo devuelve False XLSTrendlineEquationLabel

XLSTrendlineEquationLabel devuelve False y deja ambos strings vacíos 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 significa siempre que ambos strings pedidos están completos

• Menos de dos puntos, un tipo de tendencia fuera de 0..5, o un orden polinómico fuera de 2..6
• Una X no positiva para ajustes logarítmicos o potenciales, o una Y no positiva para exponenciales o potenciales, ya que ambos necesitan un logaritmo
• Entradas NaN o infinitas, o cualquier X, Y o intersección con magnitud por encima de 1E50
• Demasiados pocos puntos para el orden polinómico, todos los valores X iguales para un ajuste no polinómico sin intersección fija, 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 línea de tendencia de media móvil incluso cuando los interruptores guardados dispEq y dispRSqr están activos, así que la función devuelve True con ambos strings vacíos, lo que correctamente dice al llamador que no hay nada que dibujar

Estabilidad numérica y límites actuales

Las líneas de tendencia polinómicas son donde el código ingenuo pierde dígitos. Las ecuaciones normales en crudo 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 el mismo motivo, y una intersección que es mero ruido de coma flotante respecto a los datos se ajusta a cero para que la etiqueta no acabe en + 4E-17

Los límites honestos están en el formateo. La función produce los números de etiqueta por defecto de Excel; un LabelNumberFormat custom en la etiqueta de la línea de tendencia 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 de offset grande o casi colineales aún pueden rechazarse como singulares en lugar de resolverse. Para una biblioteca que se supone que concuerda con Excel, negarse es el fallo más seguro

FAQ

¿Guarda el fichero XLSX el texto de ecuación de la línea de tendencia

No. El elemento c:trendline guarda el tipo de ajuste, el orden o periodo, la intersección opcional, los forecasts y los interruptores c:dispEq y c:dispRSqr, y c:trendlineLbl guarda el layout y formato de la etiqueta. Excel calcula el texto de ecuación y R cuadrado en el momento del render, que es por lo 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 plano en el string 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 hechos 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 podáis extraer. La proyección automática del texto de etiqueta en instantáneas semánticas cubre por ahora gráficos OOXML; para otras fuentes, construid vosotros el array TXLSTrendlineLabelPoint y seguid la regla de posición de categoría para gráficos Line

El cálculo de etiquetas de líneas de tendencia, la autoría de gráficos y el resto del modelo de objetos 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