曾听一位同学这样说:我的一个同事excel水平很高,公式写的好长。

看了公式,果然好长。

不过,公式越长,excel水平越高吗?

其实结果正好相反,因为大部分长公式都有很大的优化余地。

下面我们就一起看一下最常见的长公式。

一、隔列求和

如下图所示,需要对1~12月份的计划和实际数进行求和(为方便截图)

长公式:=B3+D3+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3

短公式:=SUMIF(B$2:Y$2,Z$2,B3:Y3)

excel长公式优化,excel短公式实现复杂运算

点评:本例巧用sumif条件求和,解决了隔列求和的问题。

二、总计行

如下图所示,需要对下表设置总计行。

长公式:

C27 =C5+C8+C11+C14+C17+C20+C23+C26

短公式:C27 =SUM(C2:C26)/2

excel长公式优化,excel短公式实现复杂运算

点评:(所有明细 小计)/2 的结果正好是总计数额。妙!

三、多表求和

如下图所示,需要对1~31日的报表进行汇总

长公式:=\’1\’!B4 \’2\’!B4 \’3\’!B4 \’4\’!B4 \’5\’!B4 \’6\’!B4 \’7\’!B4 \’8\’!B4 \’9\’!B4 \’10\’!B4 \’11\’!B4 \’12\’!B4 \’13\’!B4 \’14\’!B4 \’15\’!B4 \’16\’!B4 \’17\’!B4 \’18\’!B4 \’19\’!B4 \’20\’!B4 \’21\’!B4 \’22\’!B4 \’23\’!B4 \’24\’!B4 \’25\’!B4 \’26\’!B4 \’27\’!B4 \’28\’!B4 \’29\’!B4 \’30\’!B4 \’31\’!B4

短公式:=SUM(\’1:31\’!B4)

excel长公式优化,excel短公式实现复杂运算

点评:sum函数具有多表同位置求和功能,大家一定要记住。

四、判断条件

如下图所示,需要根据销售量来使用提成比率。

长公式:=IF(B11<11,1%,IF(B11<21,2%,IF(B11<51,3%,IF(B11<81,4%,IF(B11<101,5%,6%)))))

短公式:=VLOOKUP(B11,A3:B8,2)

excel长公式优化,excel短公式实现复杂运算

提成比率表格式稍调整一下:

excel长公式优化,excel短公式实现复杂运算

点评:vlookup第4个参数省略时,可以实现区间查找。

五、计算完成率

如下表在计算完成率时,如果实际和计划数有一方为空或0,则公式返回空。

长公式:D2 =IF(OR(B2=0,C2=0),””,B2/C2)

短公式:D2 =IF(B2*C2,B2/C2,””)

excel长公式优化,excel短公式实现复杂运算

点评:巧用相乘来判断是否其中一个为0

六、奖励封顶

如下图所示的“实际奖励”计算表中,如果“应奖励”数大于“最高奖励”,则按“最高奖励”金额,如果小于则按“应奖励”金额。

长公式:D2 =IF(B2>=C2,D2,B2)

短公式:D2 =MIN(B2,C2)

excel长公式优化,excel短公式实现复杂运算

点评:min和max都可以实现比大小的判断。

兰色说:excel短公式看起来清爽,只是对新手来说有点难懂。什么样的公式最好的,兰色觉得有以下几点来判断:

1 是否容易理解。

2 是否易修改,易维护,公式能否表格的变化而自动调整。

3 计算量尽可能的少,不会拖慢表格。