咔片PPT · AI自动生成演示文稿，模板丰富、排版精美 讯飞智文 · 一键生成PPT和Word，高效应对学习与办公

学习函数，只掌握其基本语法是远远不够的，还要不断练习，多解题，才能打开思路。


越学习，越会发现学无止境，自己会的那两把刷子，连百宝全书一只角都算不上。


比如 max 函数，它真的就是一个求最大值函数吗？


对于这一点如果有质疑，不妨参阅一下 。


很多看过上文的读者都表示：本来以为会的函数，看完后发现不会了。


如果这已经让人感到震惊，那么接下来我要讲解的案例，可能更加刷新三观。

案例：


下图 1 是每个销售当月的获客数和平均客单价，根据以下规则，计算出应得奖金数，结果如下图 2 所示。


根据获客数区间发放奖金：

  • <3：0
  • >=3 & <6：3000
  • >=6 & <9：7200
  • 9： 13500
  • >=10：20000


    • 根据客单价乘以奖金系数：

  • <=500：100%
  • >500 & <=1000：80%
  • >1000 & <=1500：60%
  • >1500 & <=2000：40%
  • >2000 & <=3000：20%
  • >3000：0%


    • 解决方案：


    1. 根据需求，我们先分别构建出获客数和客单价两个区间条件表。


    看到上面的两个条件查询表，不少同学就明白了，这是要按区间查找，于是想起了最常用的区间查找函数 lookup，类似的案例请参阅 Excel函数(16)–lookup函数按区间查找。


    还有一些按区间统计的用法，例如：

    Excel 版本低也不怕，用 countif 也能按区间计算、统计唯一值

    Excel 高手们青睐的小众函数 frequency，究竟有多强大？


    然而前面说过了，今天的主角是 max 函数，要靠它实现按区间查找。


    2. 在 D2 单元格中输入以下公式 --> 按 Ctrl+Shift+Enter 生成数组公式 --> 下拉复制公式：

    =MAX((B2>=$F$2:$F$5)*$G$2:$G$5)*MAX((C2<=$I$2:$I$6)*$J$2:$J$6)


    公式释义：

  • 这个公式中有两个 max 函数，分别对两个不同的区间进行查询，我依次来解释一下；
  • MAX((B2>=$F$2:$F$5)*$G$2:$G$5)：
  • B2>=$F$2:$F$5：依次判断 B2 的值是否大于等于 $F$2:$F$5 区域的值，生成一个由逻辑值组成的数组，结果为 {TRUE;FALSE;FALSE;FALSE}，true 相当于 1，false 相当于 0;
  • ...*$G$2:$G$5：将上述数组与 G 列的数据区域相乘，只有数组中为 true 的乘积有值，false 的乘积均为 0，结果为 {3000;0;0;0};
  • MAX(...)：用 max 函数取出数组中的最大值，即 3000
  • MAX((C2<=$I$2:$I$6)*$J$2:$J$6)：
  • C2<=$I$2:$I$6：依次判断 C2 的值是否小于等于 $I$2:$I$6 区域的值，结果为 {FALSE;FALSE;FALSE;FALSE;TRUE};
  • ...*$J$2:$J$6：将数组与 J 列的值相乘，只有 true 值的乘积不为 0；
  • MAX(...)：用 max 函数取出数组中的最大值，即 20%
  • MAX(...)*MAX(...)：将上述两个 max 的结果相乘，得到最后的奖金数 600
  • 无论哪个数组函数中有多个 true 值，也没关系，max 会取结果中的最大值，即最接近的区间所对应的值。


    • * 参数中的区间都需要绝对引用。


    很多同学会觉得 Excel 单个案例讲解有些碎片化，初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学，从最基础的概念开始，一步步由简入繁、从入门到精通，系统化地讲解 Excel 的各个知识点。

    现在终于有了，文中专栏，从最基础的操作和概念讲起，用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本，你也能成为 Excel 高手。