第1个回答 2021-11-10
在我看来，Excel函数用到极致要跨越这么几层：基础函数：SUM, IF, VLOOKUP进阶函数：SUMIF, COUNTIF, SUMIFS, SUMPRODUCT, MATCH, INDEX数组函数：大多数人能把1和2用熟就已经不错了组合函数：主要是学习函数思维自定义函数：将自己的思路通过VBA写成自定义函数，自己用得溜溜的〇、函数学习必备知识单元格引用 相对引用 绝对引用 混合引用公式中使用定义名称公式调试：在编辑栏中选中公式，按F9，快速计算关于函数的函数：FORMULATEXT(reference)一、基础函数&进阶函数示例1：文本函数基础CLEAN：删除文本中所有打印字符TRIM：从文本中删除多余的空格CONCATENATE，或CONCAT，也可以用&：LEN：返回文本字符串中的字符数LEFT/RIGHT/MID：返回文本值中最左侧的字符/返回文本值中最右侧的字符/返回文本字串中从指定位置开始的特定个数的字符LOWER/UPPER：将文本转换为小写/将文本转换为大写高阶TEXT：设置数字的格式，并将其转换为文本VALUE：将文本参数转换为数字FIND：在一个文本值中查找另一个文本值（区分大小写）SEARCH：在一个文本值中搜索另一个文本值（不区分大小写）SUBSTITUTE：使用新文本替换文本字符串中的旧文本REPLACE：替换文本中的字符2. 日期和时间基础TODAY：返回当天日期的序列号NOW：返回当前日期和时间的序列号DATE：返回特定日期的序列号DAY：将序列号转换为一个月中的第几天MONTH：将序列号转换为月YEAR：将序列号转换为年WEEKNUM：返回一年中的周数WEEKDAY：将序列号转换为一个星期中的某天WORKDAY：返回指定工作日数之前或之后的日期的序列号高阶DATEVALUE：将文本形式的日期转换为序列号（数值类型），便于做日期加减计算DAYS：返回两个日期之间的天数DATEDIF：计算两个日期之间相隔的天数、月数或年数3. 计数和求和基础COUNTCOUNTACOUNTBLANKSUBTOTALSUM高阶COUNTIFCOUNTIFSSUMIFSUMIFSSUMPRODUCT快捷键ALT+=可以快速填充求和公式，与手写SUM公式的好处在于不用挨个选择求和区域，即可以自动选择（连续）求和区域。有两种用法。第一种，Ctrl多选求和公式所在单元格后ALT+=，这时公式是SUM(X:X)第二种，筛选可见单元格后ALT+=，这时对应的公式是=SUBTOTAL(9,X:X)，SUBTOTAL函数有个特性是可以仅对可见单元格求和（需要把=SUBTOTAL(9,X:X)改成=SUBTOTAL(109,X:X)），这也是SUM函数做不到的。重点讲一下SUMIF，SUMIF仅针对满足条件的数据求和，也就是筛选后求和。这个筛选可以是数据本身，也可以是数据所在行的其他字段，筛选方式可以是数值比较，也可以是匹配通配符。下面这张图可以讲清楚常用的条件求和函数。一题多解，主要是为了拓展函数思维。如有疑问，可在评论中提出来，我看到会解答的。示例2：常用条件求和函数单条件求和（SUMIF）多条件求和（SUMIFS、SUMPRODUCT、SUM数组公式、SUM+SUMIF）条件求和函数4. 查找与引用基础CHOOSE高阶VLOOKUPLOOKUPINDEXMATCHOFFSETINDIRECTHYPERLINK示例3：查找函数单列查找，结果多列的VLOOKUP通用公式在左边的列中查找值（VLOOKUP和INDEX+MATCH两种方法）双列查找（VLOOKUP和INDEX+MATCH两种方法）在多个表格中查找（VLOOKUP+IF）示例4：引用函数确定一个值在区域内的单元格地址（ADDRESS）多行多列转换为一列（OFFSET）引用区域文本参数化（INDIRECT）ROW与ROWS的区别，COLUMN与COLUMNS同理引用函数5. 统计函数基础LARGESMALL高阶PERCENTILE.EXCPERCENTRANK.EXCQUARTILE.EXCRANK.AVG示例2：频率分布3. 累计求和5. VLOOKUP模糊查询二、数组函数在第一部分已经穿插讲了数组函数的应用了。唯一要注意的是，数组函数编辑完成后要按Shift+Ctrl+Enter确认。三、组合函数这里我更想讲的是——函数思维。我有一个理念：一共工具只要有逻辑，就一定能够为我所用，只不过是不同场合不同需求罢了。这可能跟技多不压身是一个道理吧。但我这里更想强调的是，我们学一个工具或者技能，本质上是在学其中的逻辑。回到Excel函数上来说，我们要学习的不是几个函数在几个特定场景下的示例，而是学习各个函数的特性。再就是如何使用这些函数的函数思维。妙用：COLUMN和ROWINDIRECT另外要讲的是，函数组合和函数嵌套是不同的两个概念，函数组合是指函数之间的搭配，而嵌套只是一个函数的输出是另一个函数的输入。不建议把一个函数嵌套太多层，不方便排查。四、自定义函数若你需要经常做某种计算，而用Excel自带公式太麻烦容易出错，建议你把计算逻辑固化为自定义函数。我曾经编辑过一个函数，实现功能是：将一个指标值映射到Ymin-Ymax之间，也就是指数化。具体功能是，指标值大于Xmax时，Y取Ymax，指标值小于Xmin时，Y取Ymin，指标值在Xmin-Xmax之间时，Y在Ymin-Ymax之间线性分布。'自定义函数VBA代码
Function zs_score(low As Double, high As Double, weight As Double, value As Double, up As Boolean)
flag = 1
If up = False Then
flag = -1
End If
If value * flag >= high * flag Then
zs_score = weight
ElseIf value * flag <= low * flag Then
zs_score = 0
Else
zs_score = weight * (value - low) / (high - low)
' MsgBox "hi"
End If
End Function评论里提到自定义函数唯一不方便的就是改格式。我的解法是：把包含函数的Excel发布为xlam加载项，每次启动Excel时自动加载函数，这样函数就在任何地方都可用了。不过，如果Excel发给了别人，别人的Excel没装加载项的话是无法识别自定义函数的。要么把加载项一起发给对方，要么把公式结果转成值（如果公式本身不重要结果值更重要的话）。详细可以参考下面这篇回答。Excel 有哪些可能需要熟练掌握而很多人不会的技能？...更多回答请看hiqingle的主页 ...更多文章请到数据分析师修炼手册 - 知乎专栏最后再探讨一下，什么叫函数用到极致。我有一个同事，只用函数解决各种问题，不屑于用数据透视表功能。但事实上，过度使用函数，很容易算错，而且文件很大，处理起来经常卡顿。效率反而不高。所以最后给大家的忠告是，可以把Excel用到极致，但不是只把Excel函数用到极致，合理利用工具，小心被其反噬。还没写完，但不小心已经发布了，就先这样吧。不会弃坑，想到或记起更好的应用，会陆续更新答案的。赞同数过5000，我就把多年来收集整理的“秘籍”共享出来。为什么是5000呢？因为励志要超越第一高赞答案。哈哈哈，天真本回答被网友采纳