|
如果他的工作对使用Excel没有提出哪个方面有特殊要求的话，我都会普遍性的告诉他，作为一个经常使用Excel的人士，应该从以下几个方面去学习下Excel： 1、 熟练掌握下 Excel中基本的操作技巧，因为这小技巧很容易学习，而且能极大的提高使用Excel的效率，平常多关注一些Excel公众号，可以学习到很多基本的操作技巧。 2、 熟练掌握数据透视表，这个功能是Excel中比较亮眼的一个功能，非常的强大且便捷，特别是针对函数水平不好的同学。 3、 能用基础的函数统计数据，比如求和类、计数类、判断类、文本处理类、查找类的函数，这些都是最常用的函数，基本用法一定要明白。 4、 掌握 作 图表的基本功底，不说能做出多精美的图表，至少我们要对每种图表中的 各种 元素的特点都有所了解，怎么去修改、调整这些元素都要会操作。 在这四个能力都具备的基础之上，如果觉得依然不够，那就可以考虑提升自己的函数水平，甚至可以去学习VBA，这些或许会对我们有很大的帮助。 总之， 不提倡没有目的的学习，Excel仅仅是一个生产力工具，能满足你的需求即可，但是因为自己对工具的使用不熟悉导致效率低下就真不值得了。 回到今天的正题，在写一些复杂的嵌套公式时，往往会用到一些常用的套路，比如： lookup的多条件查找： lookup（1，0/条件一/条件二/……，返回值所在的数组或列） 提取不重复值的万金油公式： INDEX+SAMLL(LARGE)+IF+MATCH+ROW …… 今天我们来分析下 加权思维 在函数公式中是如何使用的。 案例1：
图一 根据左边A-C列的数据查询E列编码对应的系数。 本例的难点在于开始编码和结束编码都是文本，不能直接比较大小，所以无法应用Vlookup或者是lookup函数的近似匹配功能。 怎么办呢？ 如果A-C列是这样的，我想你该知道怎么解决这个问题了吧。
图二 所以我们 现在 要办法把图一中的编码变为图二中的编码。 把图一编码中横杠前面的数字都扩大10000倍，加上横杠后面的数字，这样处理后的编码就可以直接用lookup函数近似匹配了。 为什么是扩大10000倍，而不是100或者1000倍呢？ 因为原来的编码中横杠后最多四位数字，所以横杠前的数字扩大一万倍后加横杠后的数字的结果中后四位数字肯定是原来横杠后的数字，不会因为加法进位而导致查找错误的情况发生。 E2中的公式如下： =LOOKUP( LEFT(E2,1)*10^4+RIGHT(E2,LEN(E2)-FIND("-",E2)) , LEFT($A$2:$A$14,1)*10^4+RIGHT($A$2:$A$14,LEN($A$2:$A$14)-FIND("-",$A$2:$A$14)) , $C$2:$C$14 ) 公式虽然比较长，但思路其实很简单，即把编码中横杠前的数字都乘以一个相同的权重系数然后加横杠后的数字，查找值和查找列都用了一样的处理方式，然后就可以用查找函数进行查找了。 案例2：
图三 随机提取前三名，且不能重复。 基本的思路大家都知道： INDEX(A:A,{2到21之间随机抽取三个数字}) 关键是怎么抽取这三个随机数字呢？如果用RANDBETWEEN(2,21)，则这三个数字可能会重复，怎么才能不重复呢？
图四 生成一个序列，这个序列的数字位于1-99之间，把这个数字的序列扩大100倍即可得到图四种的I列，尽管这个序列的数字仍然可能有重复，但是其加序号是绝对不可能重复的（J列），因为序号是不重复的。 这样我们就可以从中取出最大的三个数字或最小的三个数字，然后从中剥离出序号，用INDEX返回姓名即可。 案例2中B2单元格的公式如下： =INDEX( A1:A21 , RIGHT(SMALL(RANDBETWEEN(1^ROW(2:21),99)/1%+ROW(2:21),ROW(1:3)),2) ) RANDBETWEEN(1^ROW(2:21),99)/1%+ROW(2:21) ，这一部分就是返回一列1-99之间的一组随机数（20个数字）扩大10000倍后加序号。因为这里的序号最多只有两位，所以只要扩大100倍以上都是可以的。 此案例中用行号作为基数乘以一个相同的权重并加原数据让每行数据有阶梯之分，从而达到其它目的。 案例3：
图五 H列为每行中最大的三个数字的和，H5单元格为H列的最大值，现在要求用一个公式求出此最大值。 综合思路和分步思路其实是差不多的。
图六 我们需要从B2:G8的每行中取出三个最大值，其结果将会是图六中J4:L10(一个7行3列)的二维数组，用公式： LARGE(B2:G8,{1,2,3;7,8,9;13,14,15;19,20,21;25,26,27;31,32,33;37,38,39}) 虽然能取出21个数字来，但是取第几个最大值是从整个数组里筛选的，并没有在每行中选取，如果能让每行的数字大小上有明显的区分但又和原来的数字相关该怎么呢？ 其实和案例2思路是一样的，把每个数字所在的行号扩大10000倍再加原来的数字，结果如下：
图七 用公式： LARGE(B2:G8,{1,2,3;7,8,9;13,14,15;19,20,21;25,26,27;31,32,33;37,38,39}) 第1，2，3个最大值就是80476、80384、80273，第7，8，9个最大值就是70421，70337，70323，依次类推……，上面的公式就能找出每行的前三个最大值。 这时得到最大值还不是我们想要的最大值，因为这些最大值里加了行号的10000倍，所以应该除以行号然后取余数才是我们需要的的值。
图八 J4中的公式如下： =MOD( LARGE( ROW(2:8)*10^4+B2:G8 , {1,2,3;7,8,9;13,14,15;19,20,21;25,26,27;31,32,33;37,38,39} ) ,10^4) {1,2,3;7,8,9;13,14,15;19,20,21;25,26,27;31,32,33;37,38,39}这一段相当于：
图九 用(ROW(1:7)-1)*6+{1,2,3})即可达到如上效果（不明白的同学可以去学习下矩阵的加法和乘法是怎么运算的），所以上面的公式可以简化为： =MOD(LARGE(ROW(2:8)*10^4+B2:G8,(ROW(1:7)-1)*6+{1,2,3}),10^4) 出来的7行3列的结果，每行包含原数据中每行的三个最大值，下面我们该把每行的这三个值求和，然后在得到的7行1列的数组中找最大值即可。 怎么求和呢？ 让这个7行3列的数组乘以一个3行1列的数组（｛1；1；1｝）就会得到一个7行1列的数组，如下：
图十 再从这个数组中用max找到最大值即可。 综合公式如下： =MAX( MMULT(MOD(LARGE(ROW(2:8)*10^4+B2:G8,(ROW(1:7)-1)*6+{1,2,3}),10^4) , 1^ROW(1:3)) ) 要理解案例3的公式，要先掌握矩阵的乘积运算，要时刻对自己需要的数组结构有清晰的把握才行。 以上三个案例均有用到加权思维，希望能为大家带来启发！ 最后再给大家布置一道练习题，如果能理解上面的内容，下面这道练习题也就不难了。
|
|
|
