Excel中如何将M/K格式区间文本批量转换为对应数值平均值

不需要逐单元格手动计算，以下三种方法都可以批量完成转换，根据自己的Excel版本选择即可：

方法1：Excel 365/2021及以上版本（公式最简） #

假设原始数据存放在A列，首个数据在A2单元格，点击B2单元格输入以下公式，按回车后下拉填充整列，即可一次性得到所有区间的平均值：

=LET(
    clean_txt, SUBSTITUTE(A2," ",""),
    range_arr, TEXTSPLIT(clean_txt,"-"),
    val1, VALUE(LEFT(range_arr[1],LEN(range_arr[1])-1)) * IF(RIGHT(range_arr[1],1)="M",1000000,1000),
    val2, VALUE(LEFT(range_arr[2],LEN(range_arr[2])-1)) * IF(RIGHT(range_arr[2],1)="M",1000000,1000),
    AVERAGE(val1,val2)
)

公式会自动清除文本里的多余空格，拆分横杠前后的两个区间端点，自动识别后缀单位：M对应乘以1000000，K对应乘以1000，最后直接输出两个端点的平均值，结果为纯数值。

方法2：2019及更早老版本Excel（全版本兼容） #

如果你的Excel没有TEXTSPLIT、LET这类新函数，用兼容公式即可，同样在B2输入后下拉填充：

=AVERAGE(
    VALUE(LEFT(SUBSTITUTE(A2," ",""),FIND("-",SUBSTITUTE(A2," ",""))-2))*IF(MID(SUBSTITUTE(A2," ",""),FIND("-",SUBSTITUTE(A2," ",""))-1,1)="M",1000000,1000),
    VALUE(MID(SUBSTITUTE(A2," ",""),FIND("-",SUBSTITUTE(A2," ",""))+1,LEN(SUBSTITUTE(A2," ",""))-FIND("-",SUBSTITUTE(A2," ",""))-1))*IF(RIGHT(SUBSTITUTE(A2," ",""),1)="M",1000000,1000)
)

计算逻辑和新版本公式完全一致，不需要额外加载插件，所有Excel版本都能直接用。

方法3：万行以上超大数据集（Power Query自动刷新） #

如果数据量过万，公式下拉容易卡顿，可以用Power Query做一次性配置，后续数据更新只要点刷新就能自动重算：

• 选中原始数据区域，点击「数据」选项卡-「从表格/区域」，将数据加载到Power Query编辑器
• 点击「添加列」-「自定义列」，在弹出的窗口中输入以下公式（注意把公式里的[原始列名]替换成你表格里实际的列标题）：

= List.Average(List.Transform(Text.Split(Text.Replace([原始列名]," ",""),"-"), each Value.FromText(Text.RemoveEnd(_,{"K","M"})) * (if Text.EndsWith(_,"M") then 1000000 else 1000)))

• 点击确定后，选择「关闭并上载」，即可将计算好的结果导回Excel表格。

格式提示：选中所有计算结果单元格，按Ctrl+1调出单元格格式窗口，选择「数值」分类，勾选「使用千位分隔符」，即可将结果显示为1,500,000这类带千分位的格式。

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