如何将R中poly输出的线性回归结果自动转换为Excel可用函数？

Got it, let's solve this—turning your polynomial linear regression results from R into an Excel-ready formula that works for any number of predictor variables. Below is a reusable R function that automates the entire process, plus a breakdown of how it works.

The Reusable R Function #

This function takes your fitted lm model (with raw polynomial terms from poly()) and outputs a string you can directly paste into Excel.

lm_to_excel_formula <- function(model, response_var = NULL) {
  # Extract coefficients and their names
  coefs <- coef(model)
  coef_names <- names(coefs)
  
  # Set response variable name if not provided
  if (is.null(response_var)) {
    response_var <- as.character(formula(model)[[2]])
  }
  
  # Start building the formula with intercept
  formula_parts <- list(paste0(coefs[1]))
  
  # Process each non-intercept term
  for (i in 2:length(coefs)) {
    coef_val <- coefs[i]
    term_name <- coef_names[i]
    
    # Extract the polynomial exponents part (e.g., "2.0" from the poly string)
    exponent_str <- gsub(".*poly\\(.*\\)(.*)", "\\1", term_name)
    exponents <- as.numeric(strsplit(exponent_str, "\\.")[[1]])
    
    # Get the original predictor variable names from the model formula
    predictors <- all.vars(formula(model)[[3]])
    
    # Build the term (e.g., x3^2, x3*x4)
    term_parts <- c()
    for (j in 1:length(predictors)) {
      exp <- exponents[j]
      if (exp == 1) {
        term_parts <- c(term_parts, predictors[j])
      } else if (exp > 1) {
        term_parts <- c(term_parts, paste0(predictors[j], "^", exp))
      }
    }
    full_term <- paste(term_parts, collapse = "*")
    
    # Handle positive/negative coefficients for formatting
    if (coef_val >= 0) {
      formula_parts <- c(formula_parts, paste0("+", coef_val, "*", full_term))
    } else {
      formula_parts <- c(formula_parts, paste0(coef_val, "*", full_term))
    }
  }
  
  # Combine all parts into the final formula
  full_formula <- paste0(response_var, " = ", paste(formula_parts, collapse = ""))
  return(full_formula)
}

How to Use It #

Using your exact example:

1. Fit your model as before:

   model <- lm(z~poly(x3,x4, degree=2,raw=TRUE), MyData)
   

2. Run the function:

   lm_to_excel_formula(model)
   

Example Output #

For your provided coefficient values, this will return:

z = 0.1742518-1.130082*x3^2-17.35482*x3*x4

Key Features That Adapt to Multivariate Scenarios #

• Works with any number of predictors: If you add a third variable like x5 (e.g., poly(x3,x4,x5, degree=2, raw=TRUE)), the function will automatically generate terms like x3*x5, x5^2, etc.
• Handles positive/negative coefficients: Formats signs correctly so you don't end up with messy + - combinations.
• Auto-detects variable names: Pulls the response and predictor names directly from your model formula, so you don't have to hardcode them.

Quick Notes #

• Make sure your lm model uses raw=TRUE in poly()—this ensures the terms are in standard polynomial form (not orthogonal), which matches what Excel uses.
• If you want to rename the response variable in the Excel formula, pass it to the response_var argument (e.g., lm_to_excel_formula(model, response_var = "Predicted_Z")).

内容的提问来源于stack exchange，提问作者dani_bagh