Google Sheets QUERY中如何将选中区域指定列文本转数字后求和

解决方案 #

你可以在ARRAYFORMULA内分区域处理列，D到R列正常执行点转逗号的文本替换，仅对S列替换完成后额外执行数值转换，既保留原有适配本地格式的逻辑，又能让QUERY正常识别S列为可计算的数值类型。

可用公式

=QUERY(
  ARRAYFORMULA(
    {
      IFERROR(REGEXREPLACE('Input'!D:R;"\.";","))\
      IFERROR(TO_PURE_NUMBER(REGEXREPLACE('Input'!S:S;"\.";",")))
    }
  );
  "SELECT Col1, Col2, Col3, Col13, Col16, SUM(Col16) 
   WHERE Col13 = 'NET A PAYER' 
   GROUP BY Col1, Col2, Col3, Col13, Col16
   ORDER BY Col13
   LABEL SUM(Col16) '总金额'
  ";
  1
)

逻辑说明

• 用{}做列拼接，前半部分'Input'!D:R对应最终查询范围的Col1到Col15，仅执行点转逗号的文本替换，和你原有逻辑完全一致
• 后半部分单独处理S列，替换点为逗号后用TO_PURE_NUMBER转成数值类型，对应最终查询范围的Col16
• QUERY读取到的Col16本身就是数值格式，可直接执行SUM等聚合计算
• 公式中列拼接用的\适配你当前使用分号作为参数分隔符的区域设置，如果后续切换到逗号作为参数分隔符的区域，把\换成逗号，参数之间的分号也换成逗号即可
• 因为同时查询了明细列和聚合结果，所有非聚合的列都需要加入GROUP BY子句，如果你不需要返回明细的Col16数值，直接把Col16从SELECT和GROUP BY中删除即可

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