各位表亲们，周末好。昨天分享了IF函数在单条件判断、区间判断和多条件判断的三个用法。今天将进一步分享IF函数的其他应用场景，让我们逐步更深入地了解EXCEL函数之所以被称为“三大利器”之首，绝非浪得虚名。
（一）IF+OR+{}用法
根据武将的姓名判断武将的等级，总所周知三国时期蜀国的五虎上将“关张赵马黄”，其余我们暂且看做“普通武将”，现在咱们根据下面的表格用IF函数判断下武将的等级。
图1：IF+OR+{}用法案例1.操作：在E3中输入公式“=IF(OR(D3={"关羽","张飞","赵云","马超","黄忠"}),"五虎上将","普通武将")”，向下填充公式即可。
2.分析：首先，我们用{}构件一个数据集合{"关羽","张飞","赵云","马超","黄忠"}；然后，用D列的数据判断武将的姓名是否在这个集合内，D3={"关羽","张飞","赵云","马超","黄忠"}，这一部分的预算逻辑是，用D3单元格的内容，分别与"关羽","张飞","赵云","马超","黄忠"进行比较，得到一个有TRUE和FALSE组成的集合，“{TRUE,FALSE,FALSE,FALSE,FALSE}”，接着用OR函数嵌套一下，OR(D3={"关羽","张飞","赵云","马超","黄忠"})，如果D3单元格的内容在{"关羽","张飞","赵云","马超","黄忠"}集合内，则范围逻辑值TRUE，否则返回FALSE，最后IF函数的单条件判断的套路即可解决问题，“=IF(OR(D3={"关羽","张飞","赵云","马超","黄忠"}),"五虎上将","普通武将")”。结果如图2。
图2：计算结果3.说明：对于EXCEL函数的初学者而言，遇到复杂的函数建议从里到外逐层拆分，一时弄不明白的话，可以借助快捷键"F9",查看每一层的计算结果，最终转化为函数的基本应用套路。
（二）IF函数的{1,0}用法
根据D列武将的姓名查询将军所属的国家,如图3
图3：IF函数的{0,1}用法案例1.分析：这是常见的数据查找场景，可能大家第一个想到的就是VLOOKUP函数，但VLOOKUP函数在不借助其他函数时，只能进行正向查询（匹配值在查询值的左边）我们分析完表格结构后，因匹配值“将军”在查询值“国家”的右边，属于逆向查询。针对逆向查询，常见的函数有LOOKUP,INDEX+MATCH等，这个咱们以后会介绍。今天的主角仍然是IF函数，我们借助IF函数实现VLOOKUP的逆向查询。
2.操作：首先，用IF({1,0},D3:D14,C3:C14)重构数据区域，即让VLOOKUP的第二参数（查询区域）由“将军”在查询值“国家”的右边，调整为"将军”在查询值“国家”的左边，转化为正向查询。接着，用VLOOKUP进行查询即可，即输入下面的公式即可：“=VLOOKUP(G3,IF({1,0},D3:D14,C3:C14),2,0)”。
3.说明：①IF({1,0},D3:D14,C3:C14)这一部分可以理解为是两个IF函数的和，即IF(1,D3:D14,C3:C14)+IF(0,D3:D14,C3:C14)，然后讲个两个IF函数的结果构成一个新的数据区域；②IF({1,0},D3:D14,C3:C14)只是构成了内存数组，并未实际改变数据的结构，该内容理解起来有一定难度，大家了解下套路即可，毕竟条条大路通罗马。
（三）IF函数的综合应用案例
某项目招标文件里约定的评标办法如下：
1.报价得分的计算规则：“有效投标人的投标报价等于评标基准价的，得满分100分；投标报价每低于评标基准价1%在满分的基础上扣2分，扣完为止；投标报价每高于评标基准价1%在满分的基础上扣3分,扣完为止（不足1%按线性插值法计算，计算结果按四舍五入法保留至小数点后两位）。”
2.评标基准价的计算规则：评标基准价=A×50%+B×50%。其中：A为招标控制价（40000万元）； B为投标报价在“低于（含等于）招标控制价～高于（含等于）95%×招标控制价”范围内的各有效投标报价，去掉一个最高和一个最低报价后的算术平均值（符合此条件范围内的有效投标报价不足五家时，以该范围内所有有效报价的算术平均值作为B值）。当有效投标报价全部低于95%×招标控制价时，则以95%×A作为B值
现在根据以上评标办法，在有效投标单位的数量未知的情况下，提前做好评标表，便于快速计算有效评标单位的商务标得分排名。
第一步：计算评标基准价。根据给定的计算规则，A值已知（40000），重点在于如何求B。有两种情况：
1）当有效投标报价全部低于38000万元（95%*40000）时，B=38000；
2）有效投标不全部低于38000万元，在这种情况下又分为另种情况：
①满足38000≤有效投标报价≤40000的有效投标报价超过五家时，B=去掉一个最低值和一个最高值后进行算术平均（也即修剪平均数）；
②满足38000≤有效投标报价≤40000的有效投标报价不足五家时，B=满足该条件的有效报价的算术平均；
刚好符合IF函数多条件判断的套路，咱们分情况写公式：
针对情况“1）”：可以用MAX(有效报价)<38000来判断是否，最高报价低于38000，即有效报价全部低于38000；
针对情况“2）”：首先，使用COUNTIFS(有效报价,>=38000,有效报价，＜=40000）判断有效投标报满足条件的家数。然后，在根据满足条件的家数选择不同的公式进行计算，如果结果大于等于5家，则使用TRIMMEAN函数计算修剪平均值，如果不足五家，则用AVERAGEIFS函数对所有满足“38000≤有效投标报价≤40000”的有效投标报价进行算术平均。
至此评标基准价中的B部分的计算公式基本成型：
B=IF(MAX(C7:C19<C2*0.95,C2*0.95,IF(COUNTIFS(C7:C15,">="&C2*0.95,C7:C15<="&C2)>=5,TRIMMEAN(C7:C19,2/COUNT(C7:C19)),AVERAGEIFS(C7:C15,C7:C15,">="&C2*0.95,C7:C15,"<="&C2)))
第二步：评标基准价=A×50%+B×50%
第三步：根据有效投标报价与评标基准价的偏差率，用IF函数计算得分即可。
其他的计算就相对简单，此处就省略掉了。最终的评标表成果如图4
图4：评标表模板结语：今天介绍了IF函数的两个高阶用法，并分享了学习函数的技巧（从里至外，以及使用快捷键F9），在制作评标表的案例中，除了IF函数外，还用到了MAX、COUNTIFS、TRIMMEAN、COUNT、AVERAGEIFS、RANDBETWEEN等函数。还是那句话，动手实践是用好函数的最好办法，为了方便表亲们操作，我把模板整理进网盘里（因平台规则，如需要链接和提取码可以私信我），大家下载后可动手试一试，看懂不代表真懂。好了，今天的内容就到这里了，下周见。Bye！