英文:
How to make this Google Sheet formula, into an array formula
问题
We have a Google Sheet table that's the target for a Google Form. It has multiple choice responses in column M that are strings containing numbers.
Example: 3. The mentor team clearly assigned..., 4. The mentor team knew the..., 5. The mentor team knew the choice..., 7. The mentor team came early on..., 8. The children were...
We need an array formula to:
- extract the numbers
- sum them
- for each non-empty row in column M
We have the below working formula, but we need it to be an array formula because the spreadsheet is ever growing with new entries from the form.
=SUM(IFERROR(SPLIT(REGEXREPLACE(M2,"[^\d\.]+"," "), " ")))
英文:
We have a Google Sheet table that's the target for a Google Form. It has multiple choice responses in column M that are strings containing numbers.
Example: 3. The mentor team clearly assigned..., 4. The mentor team knew the..., 5. The mentor team knew the choice..., 7. The mentor team came early on..., 8. The children were...
We need an array formula to:
- extract the numbers
- sum them
- for each non-empty row in column M
We have the below working formula, but we need it to be an array formula because the spreadsheet is ever growing with new entries from the form.
=SUM(IFERROR(SPLIT(REGEXREPLACE(M2,"[^\d\.]+"," ")," ")))
答案1
得分: 2
以下是翻译好的部分：
尝试以下内容
=BYROW(FILTER(M2:M,M2:M<>""),LAMBDA(sx, SUM(IFERROR(SPLIT(REGEXREPLACE(sx,"[^\d.]+"," "), " ")))))
这将跳过任何空行，但会“扰乱”答案的顺序。
要在空行保留0，请使用此公式
=BYROW(M2:M,LAMBDA(sx, SUM(IFERROR(SPLIT(REGEXREPLACE(sx,"[^\d.]+"," "), " ")))))
英文:
Try the following
=BYROW(FILTER(M2:M,M2:M<>""),LAMBDA(sx,
SUM(IFERROR(SPLIT(REGEXREPLACE(sx,"[^\d\.]+"," ")," ")))))
This will skip any empty rows but will "mess" with the order of the answers.
To keep 0s for empty rows use this formula
=BYROW(M2:M,LAMBDA(sx,
SUM(IFERROR(SPLIT(REGEXREPLACE(sx,"[^\d\.]+"," ")," ")))))
答案2
得分: 1
以下是要翻译的内容：
备选方案
您还可以利用Google表格中提供的MAP和REDUCE函数来实现此目标，如以下示例所示：
=MAP(ARRAYFORMULA(REGEXREPLACE(FILTER(M2:M, NOT(ISBLANK(M2:M))), "[^\d\.]+", ".")), LAMBDA(cell, REDUCE(0,SPLIT(cell,"."),LAMBDA(accumulator, current_value, accumulator+current_value))))
此示例公式遵循以下流程：
- 获取列M中每个单元格中的所有数字。
- 使用
MAP函数逐个单元格处理每个收集到的数字。 - 每个单元格将被传递到
SPLIT函数，以分隔数字。 - 一旦所有数字都被分隔开，它们将被传递到
REDUCE函数以计算它们的总和。 - 返回每个值的单元格总和。
演示
参考：
英文:
Alternate Solution
You can also achieve this by utilizing the MAP and REDUCE functions available in Google Sheets, as demonstrated in the following example:
=MAP(ARRAYFORMULA(REGEXREPLACE(FILTER(M2:M, NOT(ISBLANK(M2:M))), "[^\d\.]+", ".")), LAMBDA(cell, REDUCE(0,SPLIT(cell,"."),LAMBDA(accumulator, current_value, accumulator+current_value))))
This sample formula follows the following flow:
- Get all of the numbers in each of the cells in Column M.
- Process each gathered number using the
MAPfunction, cell by cell. - Each cell will be passed into the
SPLITfunction, to separate the numbers. - Once all the numbers have been separated, they will be passed into the
REDUCEfunction to calculate their sum. - Return the total per cell of each value.
Demo
References:
答案3
得分: 1
=ARRAYFORMULA(BYROW(IFERROR(SPLIT(REGEXREPLACE(M2:M,"[^\d.]+"," "), " ")), LAMBDA(row, SUM(row))))
或者：
=MAP(M2:M, LAMBDA(z, SUM(IFERROR(SPLIT(REGEXREPLACE(z, "[^\d.]+", " "), " ")))))
英文:
You could also try:
=ARRAYFORMULA(BYROW(IFERROR(SPLIT(REGEXREPLACE(M2:M,"[^\d\.]+"," ")," ")),LAMBDA(row,SUM(row))))
Or:
=MAP(M2:M,LAMBDA(z,SUM(IFERROR(SPLIT(REGEXREPLACE(z,"[^\d\.]+"," ")," ")))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论