Calcule los pesos de un promedio ponderado en Hojas de cálculo de Google o Excel

Puede usar Google Apps Script e importar la biblioteca nerdamer . Vea la configuración a continuación:

Configuración:

1. Cree archivos gs separados que contengan estas bibliotecas.

• https://cdn.jsdelivr.net/npm/nerdamer@latest/nerdamer.core.js
• https://cdn.jsdelivr.net/npm/nerdamer@latest/Algebra.js
• https://cdn.jsdelivr.net/npm/nerdamer@latest/Calculus.js
• https://cdn.jsdelivr.net/npm/nerdamer@latest/Solve.js

NOTA: Sigue el orden o tendrás errores

2. Utilice el siguiente guión:

function solveWeights() {
 
  // Get data, process, then outputs on the actual column header.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const vars = sheet.getLastColumn() - 1;

  let data = sheet.getRange(1, 1, vars + 1, vars + 1).getValues();
  let headers = data.shift();

  let solution = [nerdamer.solveEquations(data.map(row => {
    return `${row[0]}=${row.slice(1).map((x, i) => x + headers[i + 1]).join('+')}`;
  })).toString().split(',').filter((_, i) => i % 2 == 1).map((x, i) => `${headers[i + 1]} = ${x}`)]

  sheet.getRange(1, 2, solution.length, solution[0].length).setValues(solution);
}

Data de muestra:

Producción:

Nota:

• Puede agregar un desplazamiento a su rango de salida para que no sobrescriba sus columnas.
• La preparación de varias bibliotecas ralentiza la ejecución, por lo que puede llevar un tiempo, pero debería tardar unos 5 segundos (usando 4 columnas/datos variables)

EDITAR:

• Si no desea utilizar bibliotecas externas, puede utilizar LinearOptimizationService. Vea la muestra a continuación:

function solveWeight() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const vars = sheet.getLastColumn() - 1;

  // fetch N rows based on how many variables are present
  const data = sheet.getRange(1, 1, vars + 1, vars + 1).getValues();
  let headers = data.shift();
  headers = headers.slice(1);

  let engine = LinearOptimizationService.createEngine();
  // add variable
  headers.forEach(header => {
    // set the range of the weights' possible values (e.g. 0 - 100)
    engine.addVariable(header, 0, 100);
  });

  // create constraint
  data.forEach(row => {
    let sum = row.shift();
    let constraint = engine.addConstraint(sum, sum);
    // set all values as coefficients
    row.forEach((value, index) => {
      constraint.setCoefficient(headers[index], value);
    });
  });

  // get lowest possible values
  engine.setMinimization();

  let solution = engine.solve();
  if (!solution.isValid())
    Logger.log('No solution: ' + solution.getStatus());
  else 
    headers.forEach(header => {
      // workaround due to rounding issue of javascript
      console.log(header + ' value is: ' + solution.getVariableValue(header).toFixed(2))
    });
}

Producción:

Nota:

• La desventaja de este enfoque es que debe declarar cuál es el rango de los pesos y otras variables, como el coeficiente por peso, etc. El código anterior asume que todos los pesos están entre 0 y 100.
• El código en el servicio de optimización lineal solo registra los datos, puede modificar esto para que sea una función personalizada en su lugar o simplemente escribirlo directamente en la hoja.

Referencia:

• Servicio de Optimización Lineal