|
80个必学必会Excel常用函数 第31天 位置查找函数Match 技巧1：match查找值所在位置用法 技巧2：两列数据对比差异 技巧3：首次超过记录的日期 技巧4：考核等级评定 技巧5：match模糊查找 练习软件：office Excel 2016 01 Match查找位置函数用法  Match函数的作用是查找目标在指定范围里的相对位置，数值大小跟区域的范围选择有关。这一点，与row函数和cloumn函数返回单元格的绝对行号和列标有很大不同。 用法：MATCH(查找值, 查找范围, [查找方式]) 第一个参数决定了找谁， 第二个参数决定了在哪儿找，只能为一列或一行，太复杂的二维表格得想其他的办法。 第三个参数是一个数字，决定了是精确查找还是模糊查找，分别是 -1（查找大于或等于目标的最小值）、0（精确查找） 或 1（查找小于或等于目标的最大值）。虽然只是简单的3个数字，但要想用得恰到好处还是要仔细学习的。咱们先来个精确查找案例，看下效果。 match查找时，如果找到，返回位置数字；没找到，则返回错误值#N/A，记住这个规则，在后续的实际工作中有大用。 公式是“=MATCH(D2,$A$1:$A$6,0)”，因为mtach函数默认的省略值是1，所以要想精确查找，必须明确指定查找方式值为0。因为match函数只要找到目标就停止查找，所以E3单元格返回的结果为3，和目标第二次出现的位置无关。  02 两列数据对比差异 很多教程讲match函数的第一个案例就是说这个函数可以用来做两列对比数据，那咱就纳闷了，好好的干嘛要进行两列数据对比呢？ 一个真实的可落地的场景就是安排人员出差或者值班，羊毛不能逮着一个薅，出差最好轮着来。 公式“=IF(ISNA(MATCH(B2,$E$2:$E$6,0)),'未出差','已出差')”，就是利用了match函数能找到查找值返回位置，找不到时返回#N/A的特性，用isna函数对结果进行判断，如果是na错误值，那就说明近期没出过差，可以安排起来了。  03 首次超过记录的日期 当match函数的第三个参数查找方式值为1或省略时，MATCH 查找小于或等于查找值的最大值。并且查找范围参数中的值必须以升序排序。 感觉最近雪糕卖得不错，想着是不是要补点货，同时，也想总结一下是什么时候开始变得热销的。这些模棱两可的要求转换成量化指标就是销量首次超过60 的日期。 为了对比，咱们分别在乱序和升序两列进行查找。公式是一样的“=MATCH($F2,$C$2:$C$23,1)+1”，如果按照说明，严格按照升序，那么match函数按顺序进行查找，结果会返回数据源中首个小于等于查找值的最大值！ 如果是乱序的，match函数会按照大名鼎鼎的二分法进行查找，结果完全不可控。二分法是计算机系统一个常用的查找算法，总结起来就是每次都从中间位置进行查找。  04 考核等级评定 当match函数的第三个参数查找方式值为-1时，MATCH 查找大于或等于查找值的最小值。此时，查找范围参数中的值必须按降序排列。 掐指一算，这个规则拿来计算考核等级评定刚刚好。公式“=MATCH(B2,$G$2:$G$5,-1)”。阈值的数据带小数点主要是想着考核70分的同学也是辛苦一个月，所以给个合格吧。 公式的计算原理是，从上往下顺序查找，如果大于或等于查找值，就会被留下来，最后返回其中最小的，由于数据降序，所以应该就是最后一个大于等于查找值的位置！ 记住一定要是降序的，要是乱序或者升序的话，结果都会出乎意料。  05 文本字符模糊查找 除了支持近似查找数值，match函数其实也支持文本字符模糊查找。原理是将查找值与通配符进行连接，对拼接后的字符串进行精确查找。所以使用通配符时，必须将查找方式设置为0。 Excel 2016中有 问号 (?) 和星号 (*) 两个通配符。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在字符前键入波形符 (~)。  |
|
|
