• 作者： 郅龙(@老菜鸟)著
• 出版社：电子工业出版社
• 出版时间：2019年9月
• 版权提供：电子工业出版社

出版社 电子工业出版社

全书分24 课，前4 课是学习Excel 公式和函数必须掌握的基础概念，从5 课开始进入Excel 函数的讲解，重点介绍了统计类函数、引用类函数、文本类函数、时间和日期类函数，还介绍了信息类函数、数据库函数和宏表函数。各课之间有一定的逻辑关系，使学习充满了乐趣和动力。书中的示例均来自各行业的实际应用，可操作性强，既可作为学习的案例，也可直接应用于工作中。除24 课的五个函数适用于Excel 2019 和Excel 365 外， 其他函数均适用于Excel 2007 及以上版本，对于想学习Excel 函数的读者，本书无疑是很好的选择。本书适合几乎所有需要掌握Excel 公式和函数应用的读者，包括初学Excel 的各类人员、大院校的师生、相关培训机构的学员、Excel 爱好者，以及在财务、销售、行政等岗位并与Excel

1课 公式和函数基础知识——公式三板斧式 / 1

1.1 一个概念和六个符号 / 1

1.2 会做比较也是一技术 / 3

1.4 再来一个概念和五个操作 / 9

1.4.1重要的概念来了——函数到底是什么 / 9

1.4.2 公式操作式：批量创建公式 / 12

1.4.3 公式操作二式：快速复制公式 / 14

1.4.4 公式操作三式：批量修改公式 / 16

1.4.5 公式操作四式：隐藏和保护公式 / 18

1.4.6 公式操作五式：批量删除公式 / 21

2课 奇妙的逻辑值——公式三板斧二式 / 29

2.1 逻辑值的诞生和一函数 / 29

2.1.1 逻辑值不是从石头缝里蹦出来的 / 29

2.2 逻辑值和数字之间的瓜葛 / 33

2.3.3 计算特殊津贴也能信手拈来 / 36

3课 从发现规律开始——公式三板斧三式 / 38

3.1 简单的事实揭示了一个重要的问题 / 38

3.5 你真的会玩构造数列吗 / 43

4课 一组数的公式——数组公式并不只属于高手 / 44

4.1的公式输入方法也特别——数组公式简介 / 44

4.3 公式有数组，常量也有数组 / 48

5课 取舍之间的智慧——取舍类函数为你拉开函数学习的大 / 54

6课 正式开始学习函数——SUM函数原来有这么多学问 / 59

6.3快的求和方式——一键完成求和 / 62

6.10 举一反三——SUM函数教会你这十个函数 / 70

7课 IF函数就是一个假设句——它考验的不仅仅是你的逻辑思维 / 78

7.3 同时满足多个条件和满足任意一个条件 / 81

7.4 谁的大脑强——这些函数竟然抢了IF函数的风头 / 83

8课 SUM嫁给IF，然后就有了SUMIF——它带你走进条件统计的世界 / 87

8.2.1 通配符来了，各种模糊条件的求和通通搞定 / 89

8.2.2 LARGE函数来了，想要前几名的合计你说了算 / 91

8.2.3 MOD函数来了，隔行隔列求和再也不惧怕 / 91

8.5 你会按条件求和，我会按条件求平均 / 102

9课会数数的函数——COUNTIF函数挑起了家族的大梁 / 104

11课 又一个统计函数——SUMPRODUCT函数绝不只是求乘积之和 / 117

12.3 模糊匹配还是部分匹配不要搞错 / 139

12.4 模糊匹配和匹配原来是这个意思 / 141

13课 函数界佳配角来了——MATCH函数人见人爱 / 153

13.3.2 还能统计两列数据中有几个是相同的 / 157

14课 靠配角出名的主角——INDEX函数并不孤单 / 160

15课 这个函数能玩瞬移——OFFSET函数值得拥有 / 165

17课有争议的引用函数——LOOKUP函数真的是引用函数吗 / 179

17.3.2 按销售额确定佣金比例并计算佣金 / 187

17.3.3 找到每个产品近一次的订货金额 / 188

18课 时间“达人”——日期与时间函数大会师 / 190

18.3 指定的日期、时间和能自动更新的日期、时间 / 193

18.4.1 到期提醒其实很简单——会减法就够了 / 195

18.4.5 做考勤必会的三招：工时、迟到与早退 / 201

19课 函数中的魔术师——TEXT函数让人难以捉摸 / 211

20课 左中右，还有查找和替换——一常用文本函数来了 / 219

21课 竟然还有这么多五花八的函数 / 231

21.1 字母、汉字及其他符号的秘密全知道——CHAR和CODE函数 / 231

22课 以不变应万变的函数家族——数据库函数到底该怎么用 / 242

22.2 秘密都在这里——细数条件区域的用法 / 243

23课 函数也有宏——宏表函数和公式中的名称 / 246

24课 函数家族的五个新成员 / 254

网名“老菜鸟”，近20年零售行业从业经历，擅长利用Excel公式和函数的基础知识建立数据模型，同时培训了大量的办公自动化人才，有非常丰富的基础培训经验和理论。微信公众号“Excel基础学习园地”和“和老菜鸟一起学Excel”创办者，知乎专栏“Excel基础学习园地”，同时创办了网络学习社群“老菜鸟的班”。在部落窝教育的微信公众号“Excel教程”发表了近百篇文章，广受。

本学习笔记基于王佩丰老师的，老师讲的超级棒，浅显易懂，循循善诱。在此将我学习过程中的笔记分享给大家，一方面勉励自己，一方面分享知识~

• 1.3 单元格格式设置
• 1.5 分类汇总和数据有效性（数据验证）

• 2.6.1 认识时间和日期

• 2.8.1 使用文本截取字符串
• 2.8.2 获取文本中的信息

• 3 图表美化与动态图表
• 3.1.1 动态图表实现原理
• 3.2 甘特图与旋风图

• 3.3.1 主次坐标系避让

• 3.4.1 宏表函数获取信息
• 3.4.2 宏表函数常见应用

3.插入、删除多个工作表
4.插入行/列，插入多行/列，行高列宽设置
5.单元格选取、整行整列选取，数据区域选取

选取单元格区域时有三种方法进行选取

• 2.快捷键Ctrl+Shift+方向键 ：选取该方向数据区域的某行/列，进行扩选，同样要求连续
• 3.通过在单元格地址栏输入地址信息，如A1：C100，表示A1单元格与C100单元格对角线区域的矩形选区，不要求连续。

以当前被选中区域位置判断进行冻结

• 顺序填充：以序列顺序进行填充，填充时若按Ctrl，则变为复制填充
• 复制填充：以复制方式进行填充，填充时若按Ctrl，则变为顺序填充
• 特殊填充：填充时不按鼠标左键，按右键拖动，出现特殊选项窗口，依据需要进行选择。

依照单元格格式，如颜色、字体、格式、字体等 单元格匹配决定是否精确匹配 认识通配符：？ * ~
*：通配符 ？：表示字符，问好个数代表字数 ~：使后方字符不生效，仅保留原本含义 1.通过名称框定位单元格及区域位置

1.3 单元格格式设置

单元格格式包括对齐、边框、字体、填充等

可利用自定义数字格式改变或修改日期、隐藏数据（;;;）

• 利用分列工具转化文本、数值、日期

通过新增行，采用数值区分顺序，排序新增行

条件1与条件2为且的关系，条件1与条件3为或的关系（是否为同一行）
3.函数公式确定条件区域：不需要写标题行

1.5 分类汇总和数据有效性（数据验证）

• 使用分类汇总前首先需要排序
• 分类汇总的嵌套，不覆盖上次分类汇总
• 可利用分类汇总合并相同的单元格，分类截取、空值合并、格式复制
  
• 设置数据有效性（数据类型、文本长度、逻辑关系）
• 数据有效性的其他设置（输入法切换、单元格信息、单元格信息保护）

2.更改数据透视表汇总方式

双击单元格，选择汇总方式

3.数据透视表中的组合

利用组合，使数据分区间、时间等显示

插入到数值区，后拖动到列字段

5.在透视表中使用计算

计算功能，丰富透视表展示数据的能力

6.利用筛选字段自动创建工作表

筛选字段，选项→分页显示

比较运算符的结果:True,False分别可表示为0，1

以等号开头，函数名在中间，括号结尾，括号中间写参数。

3.可以利用定位工具选择输入公式的位置

1.IF函数的基本用法

2.IF函数的嵌套，如何回避嵌套

可以利用“+”“&”来进行数值或文本的处理，避免嵌套。

3.IF函数处理运算错误

回避公式中的错误值，使表格美观

1.在数据区域中寻找重复数据
2.在数据有效性中使用Countif函数
3.在条件格式中使用Countif函数
3.Countifs，用法与Countif函数用法相似，直接在括号内连续键入条件即可。

2.该函数计算超过15位数值时同样会出现错误，需要使用&" * "进行修正

计算区域与条件区域需要平齐，计算多列区域可简写，函数可自动判断

不经常使用，当其被使用时，往往做向下区间查询

4.使用isna函数处理数字格式引起的错误

如文本与数字格式相互无法查询时，使用isna函数做IF条件判断

与VLOOKUP函数区别，一个以“列”为查询，一个以“行”为查询

VLOOKUP仅能从最左列查找引用，而MATCH+INDEX函数可以组合使用无视引用方向，且能够配合开发工具引用图片。

3.使用MATCH与VLOOKUP函数嵌套使用可以返回多列结果，且无视顺序

列函数，可返回当前所在单元格或指定单元格所在的列数

2.6.1 认识时间和日期

Year、Month、Day函数可以求得所选单元格（日期）的年、月、日——拆分日期。

data函数可以组合Year、Month、Day函数所得到的年、月、日
例如：2月最后一天可理解为3月0日，即下一月第一天的前一天。
例如：2月有多少天，可单独将2月最后一天的“日”拿出来，其数值即为该月天数。（可使用Day（）函数）

datedif（开始日期，结束日期，类型）可计算开始日期到结束日期的间隔
“Y”时间段中的整年数
“M”时间段中的整月数
“D”时间段中的整天数
“YD”时间段中，去除年份，月份，所得天数的差
“YM”时间段中，去除年份，天数，所得月份的差
“MD”时间段中，去除月份，年份，所得天数的差

计算周数，Weeknum（日期，返回类型）
计算周几？，WeekDay（日期，返回类型）

text（单元格，“所需类型格式代码”）可自定义单元格格式为所需类型

简单的单元格格式可以用来标记特定单元格的格式（颜色，字体）
可以为数据透视表中的数据制作数据条

2.定义多重条件的条件格式

多重条件中，对于同一列数据，先做大范围，后做小范围。

3。使用公式定义条件格式

2.8.1 使用文本截取字符串

Left(字符，位数）
可取目标字符从左往右某几位数的字符，1~n

可取目标字符从右往左某几位数的字符，1~n

Mid(字符，起始位数，结束位数)
可取目标字符从起始位数到结束位数的字符
可利用Left及Right函数达到同样的目的，例如：

2.8.2 获取文本中的信息

Lenb() 计算字节数（英文为单字节字符，汉字为双字节字符）

Find（“查找字符”，字符串位置，从第几位开始找）可查找某一字符在某一字符串中所在的位数

1.身份证前6为判断地区

2.通过身份证计算出生年月日

3.通过身份证判断性别

倒数第二位为性别，单数男性，复数女性

Round（数字，留几位）四舍五入
Roundup（数字，留几位）向上取数
Rounddown（数字，留几位）向下取数

Mod（除数，被除数）取余运算

Row（）返回某单元格所在行
Colum（）返回某单元格所在列

单条件求和，其实质是将条件与判断区域逐项判断，形成{数组}，数组实质为{1，0，1，0······}，之后将判断数组与求和数组{求和项}进行乘积，形成结果数组{多条件项求和}，之后对结果数组进行{=sum（）}运算（三键求和-Control+Shift+Enter）

多条件求和，与SUMIF区别，实质是判断区域数组{条件1}与{条件2}进行乘积运算，得到{判断结果}，后续过程与SUMIF同样。

实质为数学中的矩阵运算

可以计算数组求和，不需要三键求和

即可将月薪对税率进行数组”与“运算，得到{税收数组}-{速扣数数组}={个税数组}，之后对其求MAX即可。

LOOKUP(查找项，查找项所在列（单列），返回数据列)

2.LOOKUP函数多条件精确匹配

利用查找项与查找区域进行数组”与“运算，得到逻辑数组{逻辑数组}，以逻辑数组为查找区域，但需要排除”0“项，即”1/{逻辑数组}“，在此查找数组中找”1“
即为=LOOKUP（1，1/{逻辑数组}，返回数据列）

indirect（）可将文本转化为其代表的地址，对其进行引用

index函数仅提取文本，无法将所提取文本转化为地址进行应用
indrect函数可达到与index相同的效果，过程需要使用""进行配合，因为文本需要使用双引号

使用indirect（）函数搭配VLOOKUP（）进行跨表引用，其使用方式即为通过文本转化方式，搭配row（）或colum（）函数进行可变引用
引用时，若表名中有空格或其他特殊字符，需要对其添加单引号“'" "'”

选中区域，定义名称，搭配"数据验证"项，可制作下拉栏

其原理是将"数据验证"项中的”来源“定义为=indirect（）函数

3 图表美化与动态图表

3.1.1 动态图表实现原理

利用函数关联图表，使图表相关项变为可变项，之后通过开发工具控件控制图表的动作。

通过IF函数在定义名称中关联名称，使函数与数据源相关联
图表选择数据时，手动输入所定义名称项，达到函数-数据源-图表三者之间的关联
通过控件，变化IF判断条件的逻辑值

OFFSET（原点，下移N行，右移M列，取X行，取Y列）
通过OFFSET函数定义可变区域，将OFFSET中函数因子与单元格匹配，将控件与单元格值匹配
将OFFSET所定义可变区域与图表项目相关联
通过控件，变化OFFSET函数相关因子

3.2 甘特图与旋风图

• 时间区间设置-坐标轴刻度设置

today（）获取当天日期
IF（）判断当前时间与计划时间关系

• 将计划时间段分为已完成及未完成：通过IF（）判断，已完成=IF（当前日期<计划日期,0，IF（当前日期>计划日期+执行天数，执行天数，当前日期-计划日期）），未完成=执行天数-已完成

3.3.1 主次坐标系避让

通过新增系列，设置系列值为={0}，变化系列位置，及所属主次坐标，达到两项系列值的避让目的

通过主、次系列值的主次坐标系设置，达到双层饼图的目的

无法直接在单元格中写入宏表函数，需要借助定义名称工具，将函数语句关联至定义名称中才可使用。

3.4.1 宏表函数获取信息

若要将获取的工作表名设置为超链接，需要将后面加&“！A1”，指向该单元格，获取的工作表明为一数组{工作表名}
通过 =index（{工作表}，row())一一取出获取的工作表名
通过 =hyperlink（地址，显示名）来将其设置为超链接

3.宏表函数的更新问题

3.4.2 宏表函数常见应用

将单元格内文本公式，转化为真正的公式来计算
同样可转化数组等相关元素
=substitute（单元格，“符号1”，“符号2”，“替换第几个符号1”）将单元格内符号替换为其他符号

我在VBA宏中寻找以下内容。

我希望宏在Excel中的工作表1中查找某个值，并使用Excel中的sheet2中的值执行VLOOKUP。

如果工作表1中的字段中的值为空，则Excel也应显示为空白而不是#N/A。

我可以编写代码来执行VLOOKUP，但我真的不确定如何输入IF命令。