如何用Google Sheets生成12欧酒吧酒水消费组合并测算涨价影响

需求1：生成所有总价不超过12欧元的酒水购买组合 #

步骤1：搭建基础定价表 #

新建工作表命名为「基础参数」，按如下规则录入数据：

• A列填酒水品类，依次为啤酒、混合饮品（Mix）、苏打水（Soda）、葡萄酒（Wine）、烈酒量贩装（Shots）
• B列填对应当前单价，依次为1.5、3.5、0.5、2.0、3.0（单位：欧元）
• C列计算单品类最大可购买量，公式为=INT(12/对应单价单元格)，下拉后结果依次为8、3、24、6、4

步骤2：自动生成所有符合条件的组合 #

在「基础参数」工作表的D1单元格输入如下公式，一键生成所有总价≤12欧元的购买组合，输出结果每行依次为啤酒、混合饮品、苏打水、葡萄酒、烈酒的购买数量，最后一列为对应组合总价：

=REDUCE({"啤酒","混合饮品","苏打水","葡萄酒","烈酒","组合总价"},SEQUENCE(C2,1,0),LAMBDA(acc,beer,REDUCE(acc,SEQUENCE(C3,1,0),LAMBDA(acc1,mix,REDUCE(acc1,SEQUENCE(C4,1,0),LAMBDA(acc2,soda,REDUCE(acc2,SEQUENCE(C5,1,0),LAMBDA(acc3,wine,REDUCE(acc3,SEQUENCE(C6,1,0),LAMBDA(acc4,shots,LET(total,beer*B2+mix*B3+soda*B4+wine*B5+shots*B6,IF(total<=12,VSTACK(acc4,{beer,mix,soda,wine,shots,total}),acc4)))))))))))

如果需要过滤掉无任何酒水购买的全0无效组合，可在外层嵌套QUERY函数：=QUERY(上述生成组合的公式,"WHERE 第1列+第2列+第3列+第4列+第5列>0",1)

需求2：测算酒水涨价方案对客均消费与整体营收的影响 #

步骤1：搭建涨价方案配置表 #

新建工作表命名为「涨价测算」，第一列录入所有酒水品类，第二列录入原价，后续每一列对应一套涨价方案，填写对应方案的酒水单价即可。

步骤2：关联组合数据计算各方案消费额 #

将「基础参数」表生成的所有购买组合的数量列，复制到「涨价测算」表的空白列区域，对应每一行的酒水购买数量，按方案计算对应组合总价，公式示例（以第一套涨价方案为例）：
=啤酒数量*方案1啤酒单价 + 混合饮品数量*方案1混合饮品单价 + 苏打水数量*方案1苏打水单价 + 葡萄酒数量*方案1葡萄酒单价 + 烈酒数量*方案1烈酒单价
下拉公式即可得到所有组合在对应涨价方案下的消费金额。

步骤3：计算核心测算指标 #

• 客均消费额：可根据实际经营需求选择计算逻辑：
  • 无偏好统计：所有符合条件的组合总价的平均值，公式为=AVERAGE(对应方案所有组合总价列)
  • 加权统计：如果有历史客人购买偏好数据，给不同价位/品类组合设置权重，加权计算平均消费额
  • 区间统计：统计当前方案下总价≤12欧的组合中，10-12欧高价位段的组合数量变化，判断客人可选择的高消费组合变动
• 整体营收：
  基础公式：=客均消费额 * 日均到店客流
  如果需要考虑涨价对客流的影响，可新增客流衰减系数参数（比如涨价10%客流减少5%，对应系数为0.95），调整公式为=客均消费额 * 日均到店客流 * 客流衰减系数
• 变化率对比：计算各方案与原价基准的客均消费变化率、营收变化率，公式为=(方案测算值 - 基准值)/基准值，直观展示不同涨价方案的影响。

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