【原】掌握这个方法，会发现原来公式编制并不难，& 练习题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、为了理清数据的规律，可以用表格来一步步整理。

如果本文对你有帮助，走时别忘了点一下文章底部的广告和右下角的大拇指