如何在Calc中提取冒号与分号间的数字并求和及结合VLOOKUP计算？

适用场景 #

你的表格中B列单元格包含多组d.m.yyyy: x;格式内容，需要提取所有:与;之间的数字x求和，再乘以A列对应打印类型的单页成本（通过VLOOKUP获取）。以下是几种Calc原生支持的可行方法：

方法1：正则表达式快速实现（Calc 6.0+推荐） #

如果你的Calc版本在6.0及以上，直接用REGEX全局匹配提取所有目标数字，再求和计算：

=SUM(VALUE(TEXTSPLIT(REGEX(B2;"(?<=: )\d+(?=;)";"g");","))) * VLOOKUP(A2;Utskriftspris;2;0)

• 公式解析：
  • REGEX(B2;"(?<=: )\d+(?=;)";"g")：匹配所有位于: 之后、;之前的数字，g参数表示全局匹配所有符合条件的内容，结果以逗号分隔。
  • TEXTSPLIT：将逗号分隔的数字文本拆分为独立条目。
  • VALUE：将文本转为数值，SUM求和后乘以VLOOKUP获取的单页成本。

方法2：数组公式兼容旧版本 #

针对不支持REGEX的旧版Calc，用数组公式组合基础函数实现：

=SUM(VALUE(MID(SUBSTITUTE(B2;":";REPT(" ";LEN(B2)));SEARCH(":";SUBSTITUTE(B2;":";REPT(" ";LEN(B2));ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2;":";""))))) )+1;SEARCH(";";B2;SEARCH(":";SUBSTITUTE(B2;":";REPT(" ";LEN(B2));ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2;":";""))))) )) - SEARCH(":";SUBSTITUTE(B2;":";REPT(" ";LEN(B2));ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2;":";""))))) )-1)) * VLOOKUP(A2;Utskriftspris;2;0)

输入完成后需按 Ctrl+Shift+Enter 触发数组公式（Calc中数组公式需手动激活）。

• 公式解析：
  • LEN(B2)-LEN(SUBSTITUTE(B2;":";""))：统计单元格内:的数量，即需要提取的数字x的个数。
  • SUBSTITUTE(B2;":";REPT(" ";LEN(B2)))：将所有:替换为与单元格长度相同的空格，便于定位每个:的位置。
  • ROW(INDIRECT("1:"&[冒号数量]))：生成遍历序列，逐个定位每个:的位置。
  • MID：根据:和对应;的位置提取中间数字文本，VALUE转数值后SUM求和，最后乘以单页成本。

方法3：辅助列分步处理（易调试） #

如果复杂公式难以排查问题，可拆分步骤用辅助列实现：

1. 辅助列1（统计数字个数）：在D2输入=LEN(B2)-LEN(SUBSTITUTE(B2;":";""))，下拉复制，得到每个单元格内的x数量。
2. 辅助列2（提取单个数字）：在E2输入以下公式，下拉至空值出现：

   =IF(ROW()-ROW($E$2)+1>$D$2;"";VALUE(MID(B2;SEARCH(":";B2;IF(ROW()-ROW($E$2)=0;1;SEARCH(":";B2;SEARCH(":";B2;ROW()-ROW($E$2)))+1))+1;SEARCH(";";B2;SEARCH(":";B2;IF(ROW()-ROW($E$2)=0;1;SEARCH(":";B2;SEARCH(":";B2;ROW()-ROW($E$2)))+1)))-SEARCH(":";B2;IF(ROW()-ROW($E$2)=0;1;SEARCH(":";B2;SEARCH(":";B2;ROW()-ROW($E$2)))+1))-1))
   

3. 计算总成本：在C2输入=SUM(E2:E[最后一行行号]) * VLOOKUP(A2;Utskriftspris;2;0)，得到最终结果。

原公式的问题说明 #

你之前的公式仅统计了:的数量，并未提取真实的页数x，若x不为1则计算结果错误。上述方法均能准确提取每个x的数值并求和，解决核心需求。

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