如何在数据透视表中忽略mg单位计算Dose*quantity字段

数据透视表自带的计算字段对文本清洗的支持很差，没必要硬在透视表里写嵌套公式，3000行数据量很小，先在源表加1列辅助列把Dose字段的纯数值提出来，再进透视表算乘积，全程1分钟搞定，不容易出错。 #

第一步：源表提取无单位的纯剂量值 #

在Dose列旁边插1列，列名设为Dose_数值，列下第一个数据行输入公式即可自动剔除mg单位，同时兼容两种录入格式：

• 单剂量格式（如30 mg）：直接提取数字
• 范围/复方剂量格式（如5 - 325 mg）：默认按临床常用逻辑对横杠前后的剂量求和，如果你需要取最高值/最低值，改下公式里的计算规则就行

365/2021及以上版本Excel/WPS通用公式 #

=LET(
  clean_txt, SUBSTITUTE(A2," mg",""),
  split_arr, TEXTSPLIT(clean_txt," - "),
  SUM(--split_arr)
)

注：公式里A2是你Dose列第一个数据单元格的位置，按你实际表格位置改就行

老版本Excel兼容公式 #

如果你的Excel版本不支持LET和TEXTSPLIT函数，用这个版本：

=IF(ISNUMBER(FIND("-",SUBSTITUTE(A2," mg",""))),
  LEFT(SUBSTITUTE(A2," mg",""),FIND("-",SUBSTITUTE(A2," mg",""))-1)+MID(SUBSTITUTE(A2," mg",""),FIND("-",SUBSTITUTE(A2," mg",""))+1,99),
  --SUBSTITUTE(A2," mg","")
)

公式输完按回车，点单元格右下角的填充柄下拉到最后一行，3000行1秒就填充完。如果你的数据里存在无空格的录入格式（比如30mg、5-325mg），把公式里的" mg"替换成"mg"、" - "替换成"-"即可。 #

第二步：透视表配置乘积计算 #

1. 选中包含新列Dose_数值在内的全部源数据，插入数据透视表，按你的需求拖入行、列、筛选维度（比如药品名称、科室、开药日期这类）
2. 点击透视表任意单元格，在顶部菜单栏找到「透视表分析」选项卡，依次点「字段、项目和集」-「计算字段」
3. 弹出的设置框里，计算字段名称填Dose*quantity，公式栏输入=Dose_数值 * quantity，点「添加」后确定
4. 把新生成的Dose*quantity字段拖到透视表值区域，按需设置求和/计数规则即可，所有计算会自动忽略原Dose字段的mg单位。

注意事项 #

• 填充完辅助列建议随机抽十几行核对数值，尤其是带横杠的剂量行，避免因为录入时的多余不可见空格导致提取错误
• 不建议直接在透视表计算字段里嵌套文本清洗函数，透视表的计算字段对文本运算兼容性差，后续刷新数据时很容易出值错误，源表加辅助列的方式可追溯性更强，核对数据更方便。

内容的提问来源于stack exchange，提问作者Ruben Ulloa