掌握这个方法，会发现原来公式编制并不难，& 练习题070：计算产品的奖惩金额
最近推送的五篇文章：如何冻结最底行、最右列、中间行、中间列？如何用公式判断是否包含指定字符？给Excel加一个随鼠标移动的十字星，再也不用担心看错行了【扩展】FREQUENCY能干啥？分段统计就用它！【扩展】Excel不可见字符会给我们带来无尽的困扰，这样搞定！· 正 · 文 · 来 · 啦 ·本文用练习题070来介绍如何摸清数据的规律，理清逻辑，以简化公式的编制。首先来看一下练题070：练习题070练习题070：按指定规则计算奖惩金额之和1、请计算“练习题070-1”工作表各产品4月5月的奖惩金额
表格说明：A2:C8单元格区域为2018年的数据，F2:H8单元格区域为2017年数据，请在K12:L17单元格区域计算出4月5月的奖惩金额。2、请用一个公式计算4月、5月各产品的奖惩金额之和（见“练习题070-2”工作表）。
本练习题是根据《偷懒的技术》读者群读者朋友的问题改编，原问题中奖惩规则是这样描述的：计算规则：同比≥8%且占比≥30%，则奖励金额为总量*（占比-30%）*5占比≥30%但同比＜8%，则不奖不罚占比＜30%，则惩罚金额为总量*（30%-占比）*5从这个问题的描述可以看出，逻辑层次不够清晰。大家之所以一遇到较复杂的情况，就不会写公式，是因为没有掌握编制公式的思路和方法，因而，借这个练习题070，给大家介绍一下如何找出数据的规律、梳理逻辑、理清思路。在介绍之前，先来看一段话：使用Excel来进行整理分析数据时，不管是使用基本功能，还是使用函数公式，都应该先对数据进行琢磨分析，总结数据的规律，数据都有什么特点或共性。把规律琢磨出来后，再考虑如何转换为公式表达出来。只有把数据的规律理清了，才能进行思路的构建，以及进行下一步：理清逻辑，以及逻辑的优化。不能一上来就考虑用什么函数和公式，如果没把规律理清，编出来的公式也只是逻辑混乱、漏洞百出。这是即将出版的《偷懒的技术2》里面的一段话，正好用到这里。将公式编制的过程总结为一句话，那就是：摸规律、顺逻辑、理思路、编公式。我们把本练习题的计算规则多琢磨一下，就会发现：占比≥30%时，奖励金额为总量*（占比-30%）*5占比＜30%时，惩罚金额为总量*（30%-占比）*5这两点可以用一个公式来表达，直接用 “总量*（占比-30%）*5" 就可以。另外，“占比≥30%，但同比＜8%” 时，则不奖不罚，实际上也可用上面的公式，用0去乘上面的公式就是了，得到的结果是0，也就是不奖不罚。我们将提成计算规则，稍稍整理一下：占比＜30%，则处罚金额为：总量*（占比-30%）*5占比≥30%且同比≥8%，则奖励金额为：总量*（占比-30%）*5占比≥30%但同比＜8%，则：0*总量*（占比-30%）*5如果用“提成公式”来代表“总量*（占比-30%）*5”，提成规则可以总结为下面的表格：占比同比提成<30%提成公式>=30%>=8%提成公式>=30%<8%提成公式*0由于占比小于30%时，不管同比是怎么样，都不会影响其提成，因而，可以将上面的表格再完善一下，就成为下面的表：占比<30%占比>=30%同比>=8%提成公式提成公式同比<8%提成公式提成公式*0从上面的表可以看出，只需在“占比>=30%"并且”同比<8%“时等于0即可。要实现这一点很简单，直接用NOT函数，一个公式即可搞定：NOT((占比>=30%)*(同比<8%))将上表用完整的公式表达就是：NOT((占比>=30%)*(同比<8%))*总量*（占比-30%）*5然后将上述公式套用到表格中：=(NOT((B12>=30%)*(B3/G3<1.08)))*G12*(B12-30%)*5
本练习题第二问，要用一个公式计算出奖惩金额之和，前不久本公众号写文介绍过：【扩展】新手进阶必学的三个函数③：最佳劳模SUMPRODUCT函数，这篇必须收藏！SUMPRODUCT函数可以进行数组运算，因而第二问用SUMPRODUCT，将前面的公式单元个单元格换成单元格区域即可：=SUMPRODUCT((NOT((B12:C17>=30%)*(B3:C8/G3:H8<1.08)))*G12:H17*(B12:C17-30%)*5)
案例二：下面我们再来看一个摸清数据规律，然后通过梳理逻辑，简化公式的案例：问题原来的描述：如果是甲公司，男，大于50的发1000，小于900，甲公司，女，大于50的发900，小于的发800，乙公司，男大于50的发800，小于50的发700，乙公司 女，大于50的发700，小于的发600我们将上面的规则描述整理成一张表：
这样就比较清晰了，用IF函数来判断的话，直接多层嵌套就是了，嵌套的过程如下图所示：
然后根据上图的思路编制公式：=IF(C2="甲",IF(D2="男",IF(E2>50,1000,900),IF(E2>50,900,800)),IF(D2="男",IF(E2>50,800,700),IF(E2>50,700,600)))
这个公式有点长，看一下能否优化一下：我们将上面表格中的数据拿来对比，为了方便对比，将表稍微改一下：
将甲公司、乙公司、年龄、性别对比一下，就会发现：甲公司比乙公司多200，50岁以上的多100，男士要多100发现这个规律就好办了，我们只需判断：是否为甲公司、是否大于50岁，是否为男士，如果是，就在基数600的基础上，加上相应的金额即可。公式如下：=(C2="甲")*200+(D2="男")*100+(E2>50)*100+600
如果想再缩短一点，将上面的公因数提取出来：=((C2="甲")*2+(D2="男")+(E2>50)+6)*100从上面的二个案例可以看出，一些看起来很复杂的问题，只要发现了数据的规律、理清了逻辑的层次，公式编制起来就要容易得多，并且，还比较简单清晰。最后，总结一下本文的知识点：知识点总结1、公式编制四步曲：摸规律、顺逻辑、理思路、编公式2、使用Excel来进行整理分析数据时，不管是使用基本功能，还是使用函数公式，都应该先对数据进行琢磨分析，总结数据的规律，数据都有什么特点或共性。把规律琢磨出来后，再考虑如何转换为公式表达出来。只有把数据的规律理清了，才能进行思路的构建，以及进行下一步：理清逻辑，以及逻辑的优化。3、为了理清数据的规律，可以用表格来一步步整理。如果本文对你有帮助，走时别忘了点一下文章底部的广告和右下角的大拇指