如何在Google Sheets自定义函数中遍历单元格范围实现求和？

Got it, let's break this down step by step. You want to create a custom function that takes a cell range, loops through all its values, and returns their total—mirroring how Google Sheets' built-in SUM works. Here's exactly how to make it happen, using your provided function framework as a starting point:

The Complete Working Function #

First, here's the full code you can drop into your Google Apps Script editor:

function CalculateProgressionTaxOfSalary(range) {
  let total = 0;

  // Handle single-value inputs (not just ranges)
  if (typeof range !== 'object') {
    return typeof range === 'number' ? range : 0;
  }

  // Loop through each row in the range
  for (const row of range) {
    // Loop through each cell in the current row
    for (const cell of row) {
      // Only add numeric values (just like built-in SUM)
      if (typeof cell === 'number') {
        total += cell;
      }
    }
  }

  return total;
}

Let's Break Down What's Happening #

• Initializing the total: We start with total = 0 to keep a running tally of the sum.
• Handling single values: The built-in SUM works with both ranges and single numbers, so we added a check to handle that. If someone inputs a single number (like =CalculateProgressionTaxOfSalary(50)), we return it directly (or 0 if it's non-numeric).
• Looping through the range: Google Apps Script passes cell ranges as a 2D array—even a single column is an array of single-element arrays. That's why we need two loops: one to go through each row, and another to iterate through each cell in that row.
• Skipping non-numeric values: Just like the built-in SUM, we ignore text cells or empty cells by checking if the cell value is a number before adding it to the total. This prevents errors from non-numeric data in your range.

How to Use It #

1. Open your Google Sheet, go to Extensions > Apps Script to open the script editor.
2. Replace any existing code with the function above, then save the project (give it a name like "CustomSumFunction").
3. Go back to your sheet and use it just like any other function:
   • For a range: =CalculateProgressionTaxOfSalary(A1:C5)
   • For a single cell: =CalculateProgressionTaxOfSalary(B2)
   • For a single number: =CalculateProgressionTaxOfSalary(100)

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