Google Sheets公式优化需求：特定店铺商品价格VAT换算与舍入调整

需求核心 #

• 目标工作表：Copy of Importdata
• 判断逻辑：当G列店铺为drankuwelnlview时执行特殊价格计算，否则直接引用Importdata表C列的不含VAT价格
• 特殊计算流程：
  1. 通过A列SKU匹配HG Productenlijst表中对应行的J列VAT税率（如BTW 21%）
  2. 将原不含VAT价格换算为含VAT价格
  3. 把含VAT价格舍入为以.49或.95结尾的数值
  4. 将舍入后的含VAT价格反算回不含VAT价格

完整公式 #

在Copy of Importdata表的目标单元格（比如D2）输入以下公式，下拉应用到整列：

=IF(G2="drankuwelnlview",
  LET(
    base_price, Importdata!C2,
    vat_rate, IFERROR(REGEXEXTRACT(VLOOKUP(A2, 'HG Productenlijst'!A:J, 10, FALSE), "\d+")/100, 0.21),
    price_inc_vat, base_price*(1+vat_rate),
    rounded_inc_vat, FLOOR(price_inc_vat, 1) + IF(MOD(price_inc_vat, 1) <= 0.72, 0.49, 0.95),
    rounded_ex_vat, ROUND(rounded_inc_vat/(1+vat_rate), 2)
  ),
  Importdata!C2
)

公式拆解 #

• 核心判断：IF(G2="drankuwelnlview", ..., Importdata!C2)，非目标店铺直接返回原不含税价格
• 变量封装（LET函数）：简化公式逻辑，避免重复计算
  • base_price：提取Importdata表C列的原始不含VAT价格
  • vat_rate：
    • 用VLOOKUP(A2, 'HG Productenlijst'!A:J, 10, FALSE)匹配SKU对应的VAT文本
    • REGEXEXTRACT(..., "\d+")/100从BTW 21%这类文本中提取税率数字并转为小数（如21%转为0.21）
    • IFERROR(..., 0.21)处理匹配失败的情况，默认使用21%税率（可按需调整）
  • price_inc_vat：计算含VAT价格，公式为不含税价 × (1 + 税率)
  • rounded_inc_vat：舍入为.49或.95结尾
    • FLOOR(price_inc_vat, 1)提取价格的整数部分
    • 以0.72为分界点：小数部分≤0.72时用整数部分+0.49，否则用整数部分+0.95，保证舍入后数值更接近原含VAT价格
  • rounded_ex_vat：将舍入后的含VAT价格反算回不含VAT价格，ROUND(..., 2)保留2位小数符合价格格式要求

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