☰

Excel 单元格中 [xx] 中任意值的总和

tag-icon • tag-icon microsoft-excel
Excel 单元格中 [xx] 中任意值的总和

我想在 Excel 单元格中的 [xx] 括号中添加数字。例如。

adfsaf [30] asdfsaf dsaf[40] asfdsa sadf  saf [30] asdfsafsf [30]

答案：130

答案1

使用 TEXTSPLIT 进行拆分[

在此处输入图片描述

然后使用 TEXTBEFORE 获取]

在此处输入图片描述

然后尝试将它们变成数字，--如果错误则将其变成数字0

在此处输入图片描述

然后求和：

=SUM(IFERROR(--TEXTBEFORE(TEXTSPLIT(A1,"["),"]"),0))

在此处输入图片描述

现在，如果它们都是不同的单元格，那么我们用 TEXTAFTER 替换 TEXTSPLIT 并引用范围：

=SUM(IFERROR(--TEXTBEFORE(TEXTAFTER(A1:A4,"["),"]"),0))

在此处输入图片描述

答案2

这应该在 Excel 2013 中有效：

=SUM(
    --LEFT(
        FILTERXML(
            "<t><s>" & SUBSTITUTE(A1, "[", "</s><s>") & "</s></t>",
            "//s[contains(.,']')]"
        ),
        FIND(
            "]",
            FILTERXML(
                "<t><s>" & SUBSTITUTE(A1, "[", "</s><s>") & "</s></t>",
                "//s[contains(.,']')]"
            )
        ) - 1
    )
)
  • 使用FILTERXML函数对“ [”进行拆分，并确保拆分部分包含“ ]”
  • 从返回的字符串中提取数字部分
  • 使用双重一元将文本转换为数字
  • SUM结果

这是一个数组公式。如果您在 Excel 2013 中没有得到有效结果或错误，则可能需要输入/确认数组公式，方法是按住ctrl+shift并点击enter。如果你操作正确，Excel 会{...}在公式栏中显示的公式周围放置括号。

编辑：正如函数帮助中所述FILTERXML，第一个参数必须是有效的XML。如果有无效字符，例如 & 符号 ( &)，则必须逃脱使该函数正常工作。这意味着使用嵌套SUBSTITUTE函数。请参阅下面的与 & 符号相关的示例

另请参阅那么问题来了以获得无效字符及其替换字符的更完整列表。

=SUM(
    --LEFT(
        FILTERXML(
            "<t><s>" &
                SUBSTITUTE(
                    SUBSTITUTE(A1, "&", "&amp;"),
                    "[",
                    "</s><s>"
                ) & "</s></t>",
            "//s[contains(.,']')]"
        ),
        FIND(
            "]",
            FILTERXML(
                "<t><s>" &
                    SUBSTITUTE(
                        SUBSTITUTE(A1, "&", "&amp;"),
                        "[",
                        "</s><s>"
                    ) & "</s></t>",
                "//s[contains(.,']')]"
            )
        ) - 1
    )
)

相关内容