Google表格ArrayFormula-复杂的行方程在列中的每个单元格中重复第一个结果

问题描述

我需要对每行中的单元格区域求和，但是单元格中的文本必须忽略.例如，一个单元格可能是 2 =中等，而我需要输入"2"求和.

如果使用此公式，则对于一行，我得到正确的结果:

= SUM(SPLIT(REGEXREPLACE(CONCATENATE(F2:AB2)，"[^ \ d] +"，"|")，"||)))

现在，我想使用 ArrayFormula 对每一行重复此公式.通过所有尝试，我得到了第一行的结果，每行重复一次.例如:如果第2行的结果为14，则每行将显示14.

这是我尝试过的最简单的公式:

  = ArrayFormula(IF(ISBLANK(F2:F)，"，SUM(SPLIT(REGEXREPLACE(CONCATENATE(F2:AB2)，"[^ \ d] +"，"|")，"|")))) 

对于未被更改以表示当前行的最内层范围，我尝试使用带有相对引用的间接方式 CONCATENATE(INDIRECT("R [0] C6:R [0] C28"))，结果相同.

有没有办法让 ArrayFormula 在这里工作?

将链接添加到工作表的演示版本:

I need to sum a range of cells in each row but the cells have text that has to be ignored. For example, a cell may be 2= Moderate and I need the "2" for the sum.

If I use this formula, for one row, I get the correct result:

=SUM(SPLIT(REGEXREPLACE(CONCATENATE(F2:AB2), "[^\d]+", "|"), "|"))

Now I want to use ArrayFormula to repeat this formula for every row. With everything I've tried, I get the result of the first row repeated for every row. Example: if the result of row 2 is 14, then every row will show 14.

Here is the simplest formula I have tried:

=ArrayFormula(IF(ISBLANK(F2:F),"",
    SUM(SPLIT(REGEXREPLACE(CONCATENATE(F2:AB2), "[^\d]+", "|"), "|"))
))

For the innermost range that is not getting changed to represent the current row, I have tried using indirect with relative references CONCATENATE(INDIRECT("R[0]C6:R[0]C28")) with the same result.

Is there a way to get ArrayFormula to work here?

Edit:
Adding link to demo version of sheet: https://docs.google.com/spreadsheets/d/17OYq3tjP1A1H8SPYAoAAlxOlrsFV2R5CQT3MNc7ZkMo/edit?usp=sharing

解决方案

use:

=ARRAYFORMULA(MMULT(IFERROR(REGEXEXTRACT(
 INDIRECT("F2:AB"&MAX((ROW(A2:A)*(A2:A<>"")))), "^\d+")*1, 0), 
 TRANSPOSE(COLUMN(F:AB))^0))

这篇关于Google表格ArrayFormula-复杂的行方程在列中的每个单元格中重复第一个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！