Google Sheets中含百分比与数值的列表分类求和及格式输出需求

直接在B1单元格中输入以下公式即可得到要求的结果：

=LET(
  raw_content, SUBSTITUTE(SUBSTITUTE(A1, "(", ""), ")", ""),
  item_array, SPLIT(raw_content, ","),
  percent_items, FILTER(item_array, REGEXMATCH(item_array, "%")),
  total_percent, SUM(VALUE(SUBSTITUTE(percent_items, "%", "")))/100,
  number_items, FILTER(item_array, NOT(REGEXMATCH(item_array, "%"))),
  total_number, SUM(VALUE(number_items)),
  "(" & TEXT(total_percent, "0%") & "," & total_number & ")"
)

公式说明 #

• raw_content：清除A1内容里的左右括号，得到纯逗号分隔的元素字符串
• item_array：将清理后的字符串按逗号拆分为数组，方便逐个处理元素
• percent_items：筛选出数组中所有带%的百分比项
• total_percent：把每个百分比项去掉%转成数值，求和后除以100（转为Google Sheets识别的百分比小数格式）
• number_items：筛选出数组中所有不带%的数值项
• total_number：将数值项转成数字后求和
• 最后通过字符串拼接，把结果格式化为(xx%,xx)的样式

如果你的Google Sheets版本不支持LET函数，可以用嵌套版公式：

="("&TEXT(SUM(VALUE(SUBSTITUTE(FILTER(SPLIT(SUBSTITUTE(SUBSTITUTE(A1,"(", ""),")",""),","),REGEXMATCH(SPLIT(SUBSTITUTE(SUBSTITUTE(A1,"(", ""),")",""),","),"%")),"%","")))/100,"0%")&","&SUM(VALUE(FILTER(SPLIT(SUBSTITUTE(SUBSTITUTE(A1,"(", ""),")",""),","),NOT(REGEXMATCH(SPLIT(SUBSTITUTE(SUBSTITUTE(A1,"(", ""),")",""),","),"%"))))&")"

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