Excel如何将单元格内多支付方式文本转数值编码并计算众数

你可以根据是否需要留存中间编码结果，选择对应方案，所有方案都兼容给出的映射规则和样例数据。

方案一：直接输出最终众数结果（无需中间列，Excel 365/2021及以上版本支持） #

直接在客户数据行的空白列（假设支付方式数据在B列，从第2行开始，结果放在C2单元格）输入以下公式，下拉填充即可直接得到每名用户最常使用的支付方式编码：

=MODE(XLOOKUP(TEXTSPLIT(B2,", "),{"Credit Card","Check","Apple Pay","PayPal","Venmo"},{1,2,3,4,5}))

公式逻辑说明：

• TEXTSPLIT(B2,", ") 自动按逗号分割单元格内的支付方式，自动兼容逗号后带多个空格的不规范格式，拆分后生成独立的支付方式文本数组
• XLOOKUP 按预设的映射规则，把数组内的每个支付方式文本替换为对应数值编码
• MODE 直接统计编码数组中出现频次最高的值，也就是众数结果

方案二：生成中间编码列（需要留存逗号分隔编码串时使用，Excel 365/2021及以上版本支持） #

如果需要得到预期的逗号分隔编码字符串，在空白列输入以下公式下拉即可：

=TEXTJOIN(", ",TRUE,XLOOKUP(TEXTSPLIT(B2,", "),{"Credit Card","Check","Apple Pay","PayPal","Venmo"},{1,2,3,4,5}))

输出的编码串会自动统一为「值, 值」的规范格式，和给出的中间转换预期完全一致。如果后续需要计算众数，直接对编码列用方案一的MODE+TEXTSPLIT逻辑计算即可。

低版本Excel兼容方案（2019及更早无动态数组函数版本） #

• 生成编码串：用嵌套SUBSTITUTE函数直接替换文本即可，注意替换顺序要保证长文本优先替换，避免短文本匹配到长文本的子串出错，此处优先替换"Credit Card"再替换其他短名称即可避免匹配错误，公式如下：

  =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"Credit Card",1),"Check",2),"Apple Pay",3),"PayPal",4),"Venmo",5)
  

• 计算众数：选中编码列，点击「数据」选项卡→「分列」，分隔符选择逗号，完成后会把单个单元格内的多个编码拆分为独立列，再对每行拆分后的编码列使用MODE()函数，选中该行所有编码单元格作为参数，即可得到众数结果。

样例验证 #

用上述公式测试提供的4条样例数据，最终输出结果完全匹配需求：

• 客户1：返回1
• 客户2：返回3
• 客户3：返回4
• 客户4：返回5

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