第1讲笔记

1.输入算式可直接运算（不输入等号）

【文件】-【选项】-【高级】-找到【Lotus兼容性设置】-勾选【转换Lotus 1-2-3公式】

勾选后可在单元格中直接输入算式，此时无需输入等号都可以运算；但现在一般不用（现都需要在等式前面加等号。如图示：

2.Excel能做什么（能处理数据）

①数据存储 ②数据处理 ③数据分析 ④数据呈现

使用excel时可根据自己的目的，来选择相应的功能，就是上面的这个。

各区名称：

3.同时浏览两个表格（类似分屏）

• 我的是Office365，位置：【视图】-（窗口）【新建窗口】。这个是都分开在不同页面，一个页面一个窗口。同一个文件里的不同表格都同时展示，就是这样的效果：

• 另外，Excel2013版本以下（忘记多少了反正是低一点的版本）：【视图】-（窗口）【保存工作区】保存格式为.xlw。可保存当前布局，下次打开时依旧是分屏模式（但是office365没有这个功能，新版的关闭时是两个窗口，打开时也是两个窗口，不用保存工作区）。点击窗口的“最大化”可还原成一个。这个是在一个页面里面有不同的窗口。如图：

• 据弹幕说WPS里的位置：【视图】-【并排比较】（我电脑没下wps，未能验证）

4.工作表增删改

新增：工作表标签栏--点击加号按钮；

批量新增表：点击一个标签，按住“shift”键选中多个（选中几个就是添加几个），右键-【插入】-选择插入的表格类型-【确认】，如图示：

删：标签处右键操作；

批量删：也是按住shift键或CTRL键选择多个，右键操作删除；

改（改标签颜色、名称之类）：同上位置

5.行列之间的添加删除

这个不写了，比较简单，批量增删原理同#4

6.两列交换（快捷版）

选中一列/行（或多列/行），鼠标放到列/行的边缘变成十字键头的时候（如图示），按住shift键拖动到想要移动到的位置松开，即可。批量交换同#4原理

7.调整列宽

没什么特别的，主要是选择列头待右侧鼠标变时双击可调整至合适的列宽。

8.跳转表格边缘/全选表格

选中任意一个单元格，将鼠标放到单元格（最下）边缘，双击即可到达最下面一格，其他方向同理。

注意：如表格中间有空行或者隐藏，将不能实现。

全选表格:

先选左上第一格，按住【Ctrl+Shift+方向键】，先→再←，覆盖到整个表格即可

【Ctrl+A】也可以

9.冻结窗格

【视图】-（窗口）【冻结窗格】

多行冻结（需在无冻结的情况下操作）：总是冻结选中单元格的上部分和左部分。e.g.:选E3格，则冻结到D列及2行

10.填充

区分：WPS里数字直接拖拽是按顺序填充；但Offic365里面直接拖拽是复制上一格，至少要两个数才能按规律/顺序填充；按住【Ctrl】键拖拽才会按顺序填充。

快捷键：【Ctrl+冒号键】：快速填充当前日期。

office365拖拽日期时效果与数字相反

日期右键拖拽时，可选择填充内容，如图：

可自定义填充序列：【文件】-【选项】-【高级】-【常规】-【编辑自定义列表】，选中【新序列】才能定义新的。

11.多选很多单元格

可在蓝色这个位置输入冒号连选，e.g.:A3:D5，则选中了下面灰色部分的单元格。（冒号可不区分中英文）

第1讲END

第2讲笔记（这讲比较简单）

1.同一格分多行

软回车【Alt+回车】

2.单元格格式

设置成不显示（但值不变）：右键-【设置单元格格式】-【数字】-【自定义】-输入【;;;】-【确定】

3.日期

aaa星期几，mm两位数月，mmm月份英文简称，mmmm月份英文全称，d、y同理

4.分列

【数据】-（数据工具）【分列】。此工具也可将数据在常规和文本之间转换。

第3讲END

第4讲笔记

1.查找和替换、定位

查找和替换-快捷键：【Ctrl+F】

位置：【开始】-（编辑）放大镜图标

注意：替换时可以根据选项需求来进行匹配，【格式】处也可以选择单元格的格式来更改。并不是要将所有格式都写上才能替换，有其中一种格式的都能替换（相当于包含关系）

通配符（都要用半角）：

• 【*】表示任何值，可是两个或以上；

• 【?】表示只有一个值；

• 【~】表示该符号后面的所有通配符都不生效，仅为符号本身的意思。（如果有张**和张*，更换张**，则需要写 张~*~*）

规定字符数时，可以【?】和【单元格匹配】一起使用。

2.常用区域定位

选中常用区域，在编辑栏左侧输入命名后回车，方便之后定位。（尽量起非单元格名）

如想取消，则在【公式】-（定义的名称）【名称管理器】中操作。

3.批注

单元格右键可增删改批注，如想全部显示，则在【审阅】-（批注）【显示所有批注】

变换批注框形状：

创建一个图形-点击图形-【形状格式】-（插入形状）【编辑形状】旁边的小角-鼠标放到【更改形状】右键-【添加到快捷访问工具栏】，如图，我这里是已经添加了的。

就会出现在这里

再进入编辑批注的状态，上面这个工具就能点了，可以进行更改批注形状。

更改批注的图片：相当于添加背景图片，点进批注-右键【设置批注格式】-【颜色与线条】-【颜色】-【填充效果】-【图片】-【选择图片】

4.快速批量填充

选中多个单元格，输入值，按【Ctrl+回车】可复制填充到全部选中的单元格。

同上一个单元格，则【=】+【↑】，再加【Ctrl+回车】。

第3讲END

第4讲笔记

1.排序

【开始】-（编辑）AZ漏斗图标

选中一格或全表.

选择自定义排序，可以添加条件按照要求先后排;如果添加条件有限，可以从最次要的序列开始往最主要的序列排．

排序依据为【单元格数】值情况下可自定义排序，类似于第1讲中的第10点填充序列．

2.隔行插入

利用排序的特性

当然也可以插入空格,e.g.:

3.打印时打印表头

位置： [ 文件 ] - [ 打印 ] - [ 页面设置 ] - [ 工作表 ] - [ 打印标题 ]

或者: [ 页面布局 ] - ( 页面设置 )右下角的小箭头 - [ 工作表 ] - [ 打印标题 ]

就可以选择要打印的标题行.

4.筛选

快捷键: 【Ctrl+Shift+L】

筛选后复制去除隐藏数据的办法: 筛选后, 定位 [ 可见单元格 ] 复制, 此时可去除隐藏的数据.

5.高级筛选--筛选不重复数据

位置: 【数据】- ( 排序和筛选 )【高级】

如果是看某列中不重复的数据, [ 列表区域 ]中选择那一列, 无条件筛选, 勾选 [ 选择不重复的记录 ] 确定.

如果要选取满足两个或多个条件的记录, 需在 [ 条件筛选 ] 中放入条件, 表头要一致, 可多行,每一行代表一种条件, 如图为"且"关系:

如图为"或"关系:

如果筛选条件中有公式, 则表头一定不能写对, 可以写错可以空, 否则无法筛选.

第4讲END

第5讲笔记:

1.分类汇总

位置：【数据】-（分级显示）-【分类汇总】

[分类字段]：按什么汇总

[汇总方式]：怎么汇总

[选定汇总项]：把什么汇总

注意：分类汇总之前需要先把要分类的那一列排序，将同一类别的放在一起。

分类后左侧会有3列数字（如果只分类一项）

第一列代表综合的总计。第二列代表每个大类的数值，第三列是全部详细的数据，效果如图：

取消分类汇总：位置：【数据】-（分级显示）-【分类汇总】-【全部删除】

也可进行汇总不同项的。

2.利用分类汇总合并单元格

将要合并的类别放在第一列（方便后续在左侧插入空行），然后分类汇总，左边会有一列值出来，像这样：

选中表中左边这列（除去表头），定位到空值，合并，再取消分类汇总，将最左边合并好的单元格格式刷到需要合并的列，完成。这样当单元格取消合并后，全部单元格都有值。

3.数据有效性

位置：【数据】-（数据工具）【数据验证】-【数据验证】。（其他版本叫数据有效性）

数值和文本长度这两个较简单，不说了。

序列有效性：相当于做选择。

来源就是可供选择的内容，可以在表格中选取，也可以直接输入（见下第二张图），注意要用英文的逗号。我会倾向于提供下拉箭头，方便选择。

效果如下：

【数据验证】中的【出错警告】可分级别地禁止修改值：

【停止】是完全不能修改；

【警告】是能够修改，但修改时会弹出提示，相当于半保护状态。

第5讲END

第6讲笔记

1.数据透视表

位置：【插入】-（表格）【数据透视表】-【表格和区域】-（【确定】）

注意选择时要鼠标点一下要选的那个表格中任意的单元格。

表格中右键-【数据透视表选项】-【显示】，可修改数据表样式。

将值拖拽到相应位置，如果不想看求和，可以双击红框位置进行修改：

双击其中值的单元格，会自动新建一张新表，表中列明该数据是怎么的来的，如图：

将具体标签改为笼统标签：需要改的数据右键-【组合】-可选择按不同时间段划分，步长就是每段有多少。

注意划分日期时，全部单元格都必须有值，且格式一致，不能为文本，否则无法整合。

同样道理也可以用在分段划分金额中，注意每段划分的长度，要可以被整个区间整除。

数据透视表中添加公式：

点一下值区域的单元格-【数据透视表分析】-（计算）【字段、项目和集】-【计算字段】

双击下方的字段来输入公式。

2.批量生成选项卡

将需要生成选项卡的选项卡名称表格做数据透视，将这一列放到【筛选字段】里，值数据中放入任意数据。

点中任意一个单元格-【数据透视表分析】-（数据透视表）【选项】-【显示报表筛选页】-点需要生成选项卡的列，确定。

生成后里面会有数据，选中所有选项卡，将表格中空白的行复制覆盖到上面有数据的区域。

第6讲END

第7讲笔记

1.运算符

公式以【=】开头进行运算。【+】相加，【&】相连，多用于文字，【^】次方

2.公式

双击单元格右下方的小加号，可自动填充至最后。

在公式当中，文本需要用半角格式的双引号【""】引起来

3.绝对引用

我们平时直接运算的大多是相对引用，如果运算的某个值中有固定某个单元格的，需要绝对引用。选中绝对引用的数值，按【F4】键。每个【$】符号都代表锁定功能，如图是锁定了4行L列：

如果是$L4，则代表锁定L列，行数还是会变化；如果是L$4，则代表锁定第4行，列数还是会变化。

4.排名函数

【RANK()】注意要绝对引用比较区域

5.批量求和（或一些简单运算）

通过【定位】+自动求和，可完成非连续性的求和运算。定位到空值-【开始】-（编辑）【求和】，或者定位到空值之后，按【Alt和=】

也可以通过定位空值输入公式后批量填充，可完成非连续性的运算方位相同的公式，如图：

第7讲END

第8讲笔记

1.嵌套IF函数

如果判断条件大于两种，需要用到嵌套函数。如果满足条件1，则A；如果不满足，则再进行判断：如果满足条件2，则B，否则C。如图：

分批区间：因为第一层已经筛选掉了超过600的，所以到达第二层IF的一定是小于600的数值，此时不需要再写<600。

而是这样：

结果有几个可能性就写几个if函数

2.AND函数和OR函数

AND：需要同时满足所有条件才为1；

OR：多个条件满足其中一个就可以为1。

第8讲END

第9讲笔记

1.COUNT()函数

COUNT()函数数一个区域里有几个数，只能数数字，不能数其他条件的。

2.COUNTIF()函数

COUNTIF([区域],[条件])，比较结果为true和false。如果条件是比较的话，要在条件外用半角双引号引起来：

如果字符串位数大于15位，普通的比较将不能得出结果，要进行补充：&"*"

3.找重复

可与IF()函数结合使用， 注意需要固定比对区域。如果想要动态更新重复情况，可以在【开始】-（格式）【条件格式】-【新建规则】-【使用格式确定要设置格式的单元格】。

公式可为【=COUNTIF(G:G,A2)=0】，即表示：如果在某列中未找到某名字，则将该名字标出颜色/某格式。效果如图：

如果是禁止重复数据输入，可结合【数据验证】一起写，e.g.:

4.COUNTIFS()函数

类似COUNTIF()函数，只是可以添加多个条件，COUNTIF(范围1,数据1,范围2,数据2,...)，条件之间用半角逗号隔开，成对出现。

第9讲END

第10讲笔记

1.SUMIF()函数

=sumif(range,criteria,[sum_range])，分别为：查找的区域，需要满足的条件/等于的值，计算的区域。

第三参数为可选，如果与第一参数一样可省略。

额外说明：SUMIF()有容错机制，即使第三参数选的是某非整行/整列区域的值，函数也会当作是某整行/列的值进行运算。

2.多条件化为单条件

可用&字符，添加辅助列，将两个或多个条件连接成1个条件，再用该条件进行比较，e.g.:

3.SUMIFS()函数

参数：要先写求和区域，后面是条件，条件成对出现，同COUNTIFS()函数的条件格式

也可以用来替代数值版的vlookup函数，但只能返回数值，无法查找和计算文本，e.g.:

4.结合数据有效性限制单元格状态

此时如果彩盒输入的总量大于44855，则最后输入的值不能够输入，e.g.:

第10讲END

第11讲笔记

1.VLOOKUP()函数

VLOOKUP(比对的值,查找的区域,需要查找的内容在第几列,精准/近似匹配)，即（要找的数据在某个区域中的第几列，如何匹配）

近似匹配≠模糊匹配

如图示，比对的列必须要放在第一列，查找的列放在最后面，不能将前后的数据也包括上，不然会查找失败，匹配一般都是选精准匹配（0）。跨表运用也是一样的道理，跨表选完后在编辑栏直接打逗号可接着打下一个条件，注意区域引用的问题。

如果是匹配简称的名字，可在第一个条件后面添加通配符【&"*"】，仍用精确查找。

2.近似匹配

通常只用于划分区间使用。eg.:

3不同格式的单元格查找

将数值转化成文本：数值型单元格引用时后连【&"*"】；

将文本转化成数值：即通过运算，可【--单元格】或【单元格+0】

ISNA()函数可识别出vlookup得出为N/A的值，该函数返回ture和false，可放在IF()函数内判断使用。e.g.:

4.HLOOKUP()函数

相当于vlookup的横版，原理相同。

第11讲END

第12讲笔记

1.INDEX()函数和MATCH()函数

INDEX(【查找的区域】,【查找的第几行】)

MATCH(【参考的数据】,【查找的区域】，【精确/近似查找】)，后面一定要写0，不然查找不对。

结合使用，可不受列位置的限制用VLOOKUP函数时，只能从左往右找，不能从右往左找。

如果查找的部分跟原表的顺序一致，可通过COLUMN()函数来获取当前列的值（不写参数返回当前列的排序，如果参数引用某单元格，则返回引用单元格的列排序值），使用该值作为index()函数的第二个条件值，e.g.:

COLUMN()=当前列是整个表中从A开始的第几列，并不是自设表中的顺序。

同理，如果填充的表格中，列名不按原表顺序，可用MATCH()函数来查找列名在原表中排列是第几位，返回该值给INDEX()函数，继续获取内容值，e.g.:

其实一般都是几个函数结合起来使用，主要是清楚逻辑，理清思路。

补充：获取当前行的值：ROW()，参数同COLUMN()

第12讲END

第13讲笔记

只看了没操作，后面补。

第14讲笔记

1.日期和时间的基本表示

在前面几讲里面讲过单元格格式，日期和时间在excel中其实是数值。日期【1】即表示1900年1月1日，【1】是1天，如果是1小时，则用1/24表示，一分钟同理，进制换算就可以。注意单元格格式。

2.DATE()函数 推算日期

对于具体的天数，很好算，但是对于月份和年份，则需要换算。

因为不确定某月中是30天还是31天，但直观的想法就是直接在【月】的数值上相加。

DATE(year,month,day)，该函数中的三个条件正好也是函数，YEAR([某单元格])，其他同理，分别提取某单元格中的年/月/日。e.g.:

DATE()函数不会得到一个非法日期，都是自动进位的。

如果只是推算月份，可用EDATE([开始日期],[月份跨度])，e.g.:

推算某月的最后一天：可通过下个月-1天得到，就不用判断该月有几天了。

3.DATEDIF()函数计算间隔

DATEDIF([开始日期],[结束日期],[划分的单位])

开始日期一定要小于结束日期，划分的单位用半角双引号括起来，如果是算间隔年份，则"y"，月和日同理。划分的单位还有"ym"（刨除年份返回月份），"md"（刨除月份返回天数），"yd"（刨除月份返回天数）

WEEKNUM([某日期],[以哪天作为一周的第一天])，后面那个参数，一般1是以周天开始，2是以周一开始，以此类推。该函数可算某日期是本年中的第几周。

WEEKDAY()，参数同上。该函数可算某日期是本周中的第几天。

4.日期和时间的显示

原理同自定义格式，使用TEXT()函数，可以将日期和文本相互转化

第14讲END

第15讲笔记

1.条件格式

位置：【开始】-（样式）【条件格式】-根据需要选择条件

需要在选中表格区域后再点格式，才能设置。

首先是要有条件，满足某条件的单元格才能设置为某种格式，格式有系统预设的，也可以自定义，这里自定义跳转的就是设置单元格格式。

清楚条件格式的话也是在原来那个位置，【清楚条件格式】

条件格式在空列中也可用。

标记重复：我现在是office365，不区分大小写。

2.结合数据透视表

结合数据透视表+数据条等，可实现数据可视化，e.g.:

3.切片器

对于同一表格里想看不同类别的、按照月份、地区的表格，可使用切片器功能。

位置：选中数据透视表中任意一格-【插入】-（筛选器）【切片器】-选择分类的依据

会弹出一个选项框，点不同的类别，会单独展示该类别下的金额总计，点切片器右上方的勾勾，可实现类别多选。点漏斗叉叉按钮，可全选。

不一定要在数据透视表中才能使用，只要是有筛选的地方都能使用，切片器本质上是一个筛选工具。

2010版本/.xlsx后缀才开始有这个工具。据弹幕提供，WPS的切片器工具在【分析】里，未下载，未能验证。

4.多重条件

可在同一区域中设置不同的条件，如逻辑不冲突，条件格式可共存。

注意，后设置的条件会覆盖先设置的条件，参考IF()函数的逻辑顺序。

如果想使用自由度比较高的条件格式设置，可在【条件格式】中选【新建规则】。

通过【新建规则】，可回避某些错误值，这是将错误值设置为白色字体的例子：

5.条件格式与公式

以上对于条件格式的改变，都是对单元格本身来做判断，而不能修改其他关联的单元格，因此需要与公式结合使用。

例子分析：

在该例中，最终目的是要将日期列符合条件的单元格标注出来。符合什么条件呢？需要符合“数量>100”的。但是这两个数据不是在同一列，所以单纯的条件格式不能做到，需要用到自定义格式。注意单元格的引用问题。

如果是让整行变色，则选择所有内容的单元格，且D列需要绝对引用。

第15讲END

第16讲笔记

1.截取字符串 LEFT()、RIGHT()和MID()

截取左侧的字符：LEFT()，参数：[需要截取的文本所在的单元格]，[截取几个字符]；

RIGHT()是截取右侧的，参数同理。

以上两个只能是截取一个单元格里面的最左或者最右，但不能截取中间的。

于是有MID()函数，可用取中间的字符。

MID([要截取的文本所在的单元格],[开始截取的字符（含该个）],[需截取的长度])

如果MID()函数第三参数不知道取几位，可多取，函数只会返回有值的部分，像这样：

2.根据身份证号识别性别

【15位身份证号：身份证号的最后一位是性别位；

18位身份证号：身份证号的倒数第2位是性别位。

奇数为男性，偶数为女性】

思路：如果使用IF()函数判断位数再进行取值，会很麻烦（最主要的是我不知道位数是哪个函数）。但是我们从MID()函数中可以知道，位数可取多位，只会返回有值的内容。于是可以利用这个特性，不管身份证号是几位，全部都从左往右取17位。这样即使是15位的，15位之后没有值，依旧会返回15位数值，但如果是18位身份证号，则会滤掉第18位，返回共17位的值，此时，两种身份证号的最后一位都是性别位，再用RIGHT()函数取最后一位，即可知道性别。

2.FIND()函数

FIND([区分前后两段的文本],[在哪个单元格],[开始的位数（非必要参数）])

可截取邮箱号中的用户名，如果有多个@，可使用FIND()套娃，第三参数就是从哪位开始找起，这里可放下另一个FIND()函数，去找第一个@。如图：

3.LEN()与LENB()函数

求单元格字符串长度函数：LEN([单元格])

单元格中的中文算一个字符。如果是LENB()函数，则是求字节的数量，一个中文字算2字节，b即字节。

如果用LENB()-LEN()，就可以知道单元格中有几个中文字，从而可以在中英数夹杂的单元格中截取中文。e.g.:

当然，现在可以用【CTRL+E】自动填充，这个功能在13版本之后才能用，我这个是office365，可用。

第16讲END，部分遗留问题在17讲

第17讲笔记

1.ROUND()函数

作用：四舍五入

格式：ROUND([处理哪个单元格],[保留到小数点后第几位])

2.ROUNDUP()函数

向上取整，进位，只要有数字就往前进一位，不用舍去。格式同上。（比如计算工时）

3.ROUNDDOWN()函数

与ROUNDUP()函数相反，向下取整，直接舍掉，不进位，也不入位。（比如计算员工假期）

4.INT()函数

直接取整，不分位数，无论小数点后有多接近，都不要小数点后的位数。

这个与ROUNDDOWN()函数在处理部分数值如负数的时候，会有区别。INT()处理负数时会向下进，而ROUNDDOWN()处理负数时会向上进。e.g.:

5.MOD()函数

MOD([除数],[被除数])，求余数。

也可以用奇偶判定（除2，为0则是偶数，为1则是奇数）。

或者是用来提取小数部分（本身除1）。像这样：

6.关于员工休假时长的舍入

例子：一般我们舍入是按照整数去舍的，但是在实际运用中，比如假期的舍入，有时会按照半天（0.5天）去发放休假时长，此处用函数来算。

思路1：如果是12.3天，一般就只能算12天，不满0.5天不算，如果是7.6天，则只能算7.5天，也就是说，小数部分要么是0，要么是0.5，于是可以用IF()函数进行判断，以0.5为分界线进行条件判定，小数部分>=0.5，则整数部分需+0.5，反之直接取整，即舍去小数。

思路2：最终结果的小数部分只有0.5或0，这两个数即任意一个数除以2的两个结果，如果先将原数放大（×2），在取整/2，则可以直接得到一个小数部分为0.5或0的结果。原理：将一个数×2时，如果小数部分<0.5，则结果<1，无法进1，反之，结果会>1，即个位数可进一位。再用放大后的数取整/2时，也就是对已经进位之后的数运算，此时就会把小数滤掉。

7.根据身份证号判断性别

根据上节课内容取性别位数值，再判断该位的奇偶，可用MOD()函数：

这是我上节课做的判断：直接用判断奇偶的函数，ISODD()奇数的为true，ISEVEN()偶数为TRUE

另外补充：可在【公式】-（函数库）【插入函数】-【搜索函数】框中输入关键词查找相关函数。

8.ROW()和COLUMN()函数

两个函数在第12讲提到过，具体参数见前。

转置：复制后右键-右下角的粘贴选项中-选择转置

如果用INDEX()和定位函数结合时使用，原理：INDEX的第二参数是“取第几个值”，如果需取某列的第3个，则第二参数写3，利用这个特性，当需要用函数来转置时，在第二参数可用定位函数（ROW()或COLUMN()函数）来返回数字。横排时，“第几个”可用COLUMN()函数来返回当前列的列值，既然返回的是数值，则可放到INDEX()函数中进行抓取。

注意数值随位置的变化而变化。

也就是针对位置进行转置：

类似的，比如跨行选取值等，也是类似的思路，其实就是找规律。

9.单行按数量分列

需要目标如下：

分析：排个序，按照结果对号入座一下，就是是要按照右侧的顺序排列：

然后找规律就行了，每行从左到右是1为n递增，列的话就是跨行取值，跨的这个行也是有规律的。

第一列竖列是1, 4, 7, 10, 13，其实也是以1为d1、3为n的递增数列，就可以按照规律取值了。

使用跨行取值，可写出上面的分列排序，只用写出第一行，其他复制即可：

老师给出了一个更简单的答案：在第一格中将横竖的变量都写进去，思路类似混合引用。横往右拉+1，竖往下拉+1。注意第一个的数值要对应，而且这个不能移动位置，否则移动后当前列的数值无法对应：

第17讲END

第18讲笔记

1.数组

在没有SUMIF()函数的情况下使用筛选加和，可用使用数组的方式进行运算。

例子：筛选符合条件的值并求和

按照一般的思维，需要先判断表格中的值是否符合需要筛选的值，【判断】在编程中会转化成TRUE（1）和FALSE（0）两个值，这个在excel中同样适用，如果将这个值与金额相乘，则会得到一个数值（本身或0），这个值可以相加运算。以此类推，可得到结果。

但是一个一个比较太慢了，于是引入数组，即将一组数与某个值或某组数进行比较，也是会返回1或0。因为比较的结果有多种，而只有当全部都为TRUE时才是最终筛选出来的条件，那么可以将比较结果相乘，如果其中出现一个不符项（为0），则相乘结果为0。最终再乘金额，相加可得到结果。最终如上面第二张图的公式。

注意：如果使用数组进行运算，则不能只按回车键获得结果，需要【Ctrl+Shift+Enter】三个键一起按才能够得到运算结果，否则是0。（至少在我这个版本office365未能智能运算）计算结果之后的公式会被大括号包起来（类似矩阵左右的大括号），这个大括号自己打上是没有用的，一定是【Ctrl+Shift+Enter】按出来的。

另外提醒：当表格中数值比较多或者需要判断的值很多的时候，尽量不要用数组计算，此时的计算量会变得很庞大，降低计算效率。

2.SUMPRODUCT()函数

相当于是已经带了大括号的数组计算SUM()函数，不需要用三键连按即可得到结果。

3.VLOOKUP()函数

参数：这里演示的是第二个参数写法，放数组。

VLOOKUP()函数只能模糊查找，这个函数如果找到一个模糊匹配的，则会停止查找；如果要达到查找并返回某值的结果，可以将非目标值通过比较都转化成错误值，令函数无法模糊匹配上，则只会返回唯一一个完全匹配的值，可达到“精确匹配”的目的。

那么仍然是数组比较，返回结果应该是一串的0和一个1，如果此时直接返回比较结果为1的值，那么还是不行，因为VLOOKUP()函数是模糊匹配，小于1的值都能匹配上，那么比较筛选将毫无意义。函数能匹配数值，但是如果比较的结果是错误，则无法进行匹配。接着这个思路，已知不匹配的数值为0，则可以通过用【0/0结果为N/A】这个方法排除掉错误，因为0/1=0，所以唯一正确的那个数能够被筛选出来。得到答案。

第一参数是1，是因为该函数模糊匹配，比1小的都能被选中，但因为所有结果中只有一个值比1小，其他都是N/A，所以最后那个值是正确的值。

同理，可做多条件筛选，注意括号位置。

理解比较的逻辑和顺序就容易了。

4.个人所得税计算（使用数组）

以月薪6500为例，起征点之后的应缴税每项都算一遍，可以发现需要缴的是最高的那项费用。

如果不想判断，则直接每个都乘一遍，取最大值就可以，这里也是用数组。注意是三键连按才能得到最大值。这里少了判断，自行加上就可以。

第18讲END

第19讲笔记

1.indirect()函数

参数：[某单元格]

基本用法：文本转地址，参数为某单元格（单元格内可写一个类似单元格位置的地址），示意如下，相当于定位内容并取回该单元格中地址的值。

定位某表格中的规律位置：可以用INDEX()函数或者INDIRECT()函数做。

INDEX()函数时（直接引用）：可用当前行ROW()函数来获取数据；

INDIRECT()函数时（间接引用）：通过定位单元格地址，再来获取其中的数据；

通过间接引用（INDIRECT()函数），可进行跨表引用，且可不用固定位置，注意找到需要改变的，通用内容的话需要用半角双引号引出。

如果名字有重复，可以用SUMIF()函数辅助计算。

另外，如果表名或者参考的单元格名不规范（中间有空格等）但下方表名和参考的单元格名要一致，此时地址引用会错误，需要用半角单引号引起来，像这样：（为了方便区分引号，我作了颜色区分）

2.二级下拉菜单

首先需要将菜单内容整合到一个区域，并对这个区域进行命名（详见第4讲#2）

这里复习：位置：【公式】-（定义的名称）【定义名称】-输入名称后的【确定】

展示结果如下：

这个时候通过数据验证/数据有效性（详见第5讲#3），使用序列可选择省份，这个就是第一级菜单，如图：

第二级菜单的设置比较巧妙，因为我们的一级菜单选的内容会变化，不能直接通过在二级菜单的单元格中输入函数来变化，但是通过INDIRECT()函数，可以定位到函数存放的地址指向的区域，从而获取到二级菜单对应的值。在【序列】的【来源】处，当作是一个单元格来输入信息，那么我们可以在这里定位一级菜单的地址，这样二级菜单就会相应变化。如图：

只是这样有一个小小的缺陷，就是当从一级到二级菜单都选完之后，如果再更改一级菜单，此时二级菜单不会失效，也不会改变，依旧会保留上一次选择的值。但是如果编辑状态再推出，系统会提示验证不匹配，此时取消后还是不变，如图：

第19讲END

第20讲笔记

1.隐藏单元格同时隐藏图片

隐藏单元格时，如果想要将其中的图片同时隐藏，则需要对图片属性进行修改：点中图片【右键】-【大小和属性】（office365是这个，如果是其他版本，可能是【设置图片格式】）-（属性）【随单元格改变位置和大小】

此时隐藏覆盖该图片的行/列时，图片会随之隐藏，但是如果没有完全包含该图片，图片就会被压缩（见下第二张图），但是不会影响到看其他行字。

该调整对图片/图表均有用。

2.图表元素

通过七块积木+一个原则，可画出图表。

七块积木：图表标题、坐标轴标题、图例、数据标签、模拟运算表（数据表）、坐标轴、网格线

一个原则：主次坐标系原则

制作图表：选中数据区域-【插入】-（图表）这里可选择想要的图表形式，也可以点（图表）右边的小箭头进行导览选择。

选择图表后点中图表，选项卡会出现新的工具，【设计】、【布局】、【格式】三个选项对图表的精细化改动逐渐递增。（我这个是office365，没有【布局】这个卡，而是放在了【图表设计】-（图表布局）中）

所有图表元素点中之后右键，都可以设置格式，这个不多写了，很个性化的改变。

如果不慎取消了某个显示，也可以在（图表布局【添加图表元素】中重新添加上。

点击图表，在【格式】-（当前所选内容）中可以选择图中的元素。（其他版本可能在【布局】-（当前所选内容）中）

图表标题：可随单元格内容的改动而改动。点中后在编辑栏输入【=某单元格】，此时改动该单元格，图表标题会随之变化，由此可制作动态图表标题。

坐标轴格式：可选择【逆序类别】，即将坐标轴反转

其他自己摸索下就可以了。

柱形图的图案可以自定义，复制图案后点击柱形粘贴，可改变柱形形状，右键-【设置数据系列格式】-【填充】-【层叠】，可实现进度表示。如图：

3.保存图表模板

位置：点击图表-右键-【另存为模板】。其他版本可能在：点击图表-【设计】-（类型）【另存为模板】

使用模板：点击需要做图表的数据-【插入】-（图表）右方的小箭头-【所有图表】-【模板】

【TIPS】

在平时可以收集比较好看的模板，形成自己的模板库，在需要的时候可以直接使用，但要注意不能生搬硬套，还是要根据当前数据进行调整。

另外，图表在excel中和在ppt中的作用是不同的，excel作用是分析数据，我们通过图表来直观地看出趋势、重点，从而帮助我们分析这个数据能为我们下一步的工作提供依据。而ppt的作用是展示，我们需要通过展示出来的表格，来验证我的推断，做出来的图表应该是与我的结论相匹配的，不能说我的结论是增长，但给出的图表是减小，而且有些小巧思可以进一步加强我的证据，例如可以通过改变坐标的上下限来突出或弱化某数据的差距。

理清图表的作用，可以帮助我们事半功倍。

第20讲END

第21讲笔记

1.