上 Excel数据分析——函数与公式_excel

本文共计3000字，建议阅读时间3分钟

函数学习必备知识

单元格引用
函数小贴士

基础函数

文本转化
日期和时间处理
数值计算

先问个问题：Excel有多少个函数？

? 来源于微软官网：Excel 函数（按类别列出）

有500 个，你答对了吗？
这么多个函数肯定是不需要都掌握的，但常用的函数及其组合的应用我们需要有所认识。但也不需要像考试一下死记硬背。初学者也只需要理解应用场景及其原理即可，建议记住一些专用词汇，以后Excel处理中碰到类似问题，首先知道有Excel函数能够解决，然后具体用法通过搜索获?。?记住专用词汇是为了在搜索时可以更精准。本节中的专用词汇第一次出现时会加粗，留心即可。
【上 Excel数据分析——函数与公式】

其实在微软官网上，就有非常好的学习资料。
官网链接在阅读原文中。以下是截图。

? 来源于微软官网：Excel 函数（按类别列出）

比如下图介绍的XLOOKUP函数，是Excel 2021新增的VLOOKUP函数的改进函数，简介之下有视频介绍，然后是详细的语法介绍。

? 微软官网的XLOOKUP函数介绍

对于初学者，建议先去找类似这样的资料自学一下。本节不会去一一讲解所有函数，仅会在花少量篇幅以示例形式列举简单常用函数的用法之后，讲解数据分析中常用的函数用法。
之后会再花一小节的篇幅，讲解特定场景下需要&好用的函数进阶用法。这里主要强调的是要理解函数特性、灵活组合构造函数、编写函数。
最后会再简单介绍一下，函数在其他场景下（如条件格式、图表）中的应用。

〇、函数学习必备知识
1、单元格引用
打开Excel就能看到一个个单元格组成的二维表，水平方向延伸的是“行”，序号是从1开始往下累加一直到1048576，垂直方向延伸的是“列”，序号是从A-Z、AA-AZ，一直到XFD（共16384列），每个单元格就可以通过行和列的序号被定位，在Excel中这就是“引用” 。相反，引用了其他单元格的单元格就是“从属”单元格，从属单元格也有自己的序号地址。当引用的是多个连续的单元格时，即区域引用。
当在公式中使用单元格（或区域）引用时，可以使用4种类型的引用：
行列相对引用：当把公式复制到其他单元格中时，行或列引用会发生改变，因为这些引用实际上是相对于当前行或列的偏移量。默认情况下， Excel会在公式中创建相对单元格引用（如A5）。
行列绝对引用：当复制公式时，行和列引用不会发生改变，因为引用的是单元格的实际地址。绝对引用会在其地址中使用两个美元符号：一个用于列字母，另一个用于行号（如$A$5）。
行绝对列相对引用：当把公式复制到其他单元格中时，列引用会发生改变而行引用不变（如A$5）。
列绝对行相对引用：当把公式复制到其他单元格中时，行引用会发生改变而列引用不变（如$A5）。

如果我们想在多个单元格使用同一公式，就必须考虑对单元格的引用的行和列是相对引用还是绝对引用。
举个例子，如下电商购物流程漏斗数据，若是环节转化率，每个环节都是除以上一个环节的人数，相对位置不变，所以这里用的是相对引用；整体转化率都是除以最开始的那个环节，随着公式往下复制，相对位置就变大了，这里就该使用绝对引用，以保证不会随着公式往下复制，导致引用位置也相对移动。

? 相对引用vs绝对引用

2、函数小贴士

? 常用函数工具

名称：名称可以是常量数值或常量数组，也可以是单元格引用，还可以是一段公式。使用名称可以简化公式、便于理解、利于维护，可以在条件格式、数据有效性、图表中使用。
追踪：可以定位到引用的或者从属单元格/区域。
监视：如果公式调整可能影响其他位置单元格，可以通过加入监视来查看变化情况。
调试：通过公式求值，可以查看一步步的输入和输出，通过这个也可以更加理解函数工作的原理。
检查：显示公式 ，适用于有大片公式区域，查看是否有引用上的或其他异常。
快速计算：在编辑栏中选中公式，按F9，快速计算
自动/手动计算：通过“公式”|“计算”中的计算选项调整。如果计算公式较多，建议开启手动计算。
关于函数的函数：FORMULATEXT(reference)，将引用单元格的函数文本显示出来。
函数屏幕提醒工具：在编写函数时，屏幕上会显示函数语法提醒。点击编辑栏左边的fx ，能调出函数参数窗口。
函数筛选：通过“公式”|“函数库”|“插入函数”可以浏览常用函数及各个分类下所有函数的使用介绍。