在此之前，我曾问过同样的问题（For Loop JavaScript? VBA for Example），但是我不够具体，所以没有得到我所需要的反馈。如何在JavaScript中创建类似的代码？这可能会帮助https://poi.apache.org/apidocs/dev/org/apache/poi/ss/formula/functions/Offset.html

这里的内容是VBA中的“父子费用汇总”计算。第I列具有H列中的公式。我想在JavaScript中创建与此类似的公式。

Function ParentChildCalc(critCell As Range,valCell As Range) As Double
    Dim numYes As Long
    Dim i As Long
    Dim temp As Double
    Do Until i = 5000
        If critCell.Offset(i,0) = "Yes" Then numYes = numYes + 1 'If the cell below says "Yes" then this sums all the 'valCells' until that "yes"
        If numYes = 2 Then Exit Do                                'We only want to sum the numbers between each one of the "Yes's"
        temp = temp + valCell.Offset(i,0)                        'see Console.log(temp) tab to see what temp does
        i = i + 1
    Loop
    ParentChildCalc = temp
End Function

这是我使用JavaScript的最佳镜头。我计划将其与Office.js一起使用。我不知道如何抵消。

/**
 * ParentChildCalc1
 * @customfunction ParentChildCalc1
 * @param {string} critCell Critical Cell
 * @param {number} valCell Value Cell
 * @returns {number} ParentChildCalc1
 */
function ParentChildCalc1(critCell,valCell) {
  var numYes = 1;
  var i = 1;
  var temp = 1;
  while (i < 5000) {
    if (critCell.getOffsetRange(i,0) == "Yes") {
      numYes++;
    }
    if (numYes == 2) {
      break;
    }
    temp = temp + valCell.getOffsetRange(i,0);
    i++;
  }
  return temp;
}