Excel 2013电费时段计算公式的简洁优化方案问询

一、第一步：将硬编码规则迁移到辅助表 #

先在表格空白区域（比如K1:N10）创建规则对照表，把所有季节、时段、周末/节假日对应的计算逻辑统一管理，彻底消除公式里的硬编码：

注：把你原来G3-I3里硬编码的数值或计算逻辑，对应填入「计算系数/规则」列

二、优化G3公式（支持下拉复制） #

假设你的表格基础结构：

• A列=日期（用于判断周末/节假日）
• B列=季节（夏季/冬季）
• G1/I1表头分别是On-Peak/Mid-Peak/Off-Peak
• 总用电量存放在F列

方案1：用LOOKUP函数（无需数组快捷键） #

在G3输入以下公式，回车后直接下拉到I3及下方行即可：

=LOOKUP(1,0/((B3=$K$2:$K$10)*(G$1=$L$2:$L$10)*(OR(WEEKDAY(A3,2)>5,ISNUMBER(MATCH(A3,$P$2:$P$20,0)))=$M$2:$M$10)),$N$2:$N$10)*F3

参数说明：

• $P$2:$P$20 替换为你存放节假日日期的实际区域
• G$1 锁定行号，下拉时会自动匹配H$1（Mid-Peak）、I$1（Off-Peak）
• WEEKDAY(A3,2)>5 判断是否周末（周一=1，周日=7）
• ISNUMBER(MATCH(...)) 判断当前日期是否属于节假日

方案2：用INDEX+MATCH数组公式（兼容性更强） #

如果LOOKUP出现匹配异常，可使用数组公式（输入时按Ctrl+Shift+Enter触发）：

=INDEX($N$2:$N$10,MATCH(1,(B3=$K$2:$K$10)*(G$1=$L$2:$L$10)*(OR(WEEKDAY(A3,2)>5,ISNUMBER(MATCH(A3,$P$2:$P$20,0)))=$M$2:$M$10),0))*F3

下拉时数组格式会自动保留，无需重复按快捷键

三、进阶优化：用定义名称简化公式 #

通过定义名称让公式更易读维护：

1. 选中节假日区域（比如P2:P20），点击「公式」选项卡→「定义名称」，命名为节假日
2. 选中规则对照表的表头区域（K1:N10），定义名称为电费规则

优化后的公式：

=LOOKUP(1,0/((B3=电费规则[季节])*(G$1=电费规则[时段类型])*(OR(WEEKDAY(A3,2)>5,ISNUMBER(MATCH(A3,节假日,0)))=电费规则[是否周末/节假日])),电费规则[计算系数/规则])*F3

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