王佩丰Excel笔记

王佩丰第一讲

excel 的基础课程

历史上其他的数据处理软件与microsoft excel

1977年，苹果公司开发了自己的第一款数据处理软件，那款软件卖得非常好。后来，lotos123公司发行额自己的lotos123.excel这款软件开发，是因为微软公司看到了lotos卖得非常好，它最早期的目标就是做到lotos123所有能做的事情，并且做的更好。我们现在的这款软件中，仍然保留着对于loros123的支持。可以帮助我们，手里有很多单据的时候，你希望都直接显示结果，有没有一种在写公式的过程省略等号呢。在选项里面勾选lotus，选择转换lotus123公式。

excel中公式必须以等号开头，没有以等号开头，就不会被运算。

excel能做些什么？

数据存储；数据处理（需要讲到函数的运用）；数据分析（从数据中得到结果，用语做判断总结和预算）；数据呈现

excel中的重要概念

excel的工作簿文件

后缀是xls的一般是2003年以前的

xlw 是工作区文件

怎么并排查看不同表格的内容呢

只有点击试图里的新建窗口，然后点击全部重排，然后点击垂直并排。就可以了。实际上做出来的两个窗口，是互为镜像的关系。所以改变一个，另一个也会随之改变。其实不需要每天都重排，如果要做数据对比的话。很遗憾，保存工作区这个功能已经废止了。所以没办法用了。

工作簿、工作表和单元格

新建工作表

在底下所有表格后面又一个加号，点击即可新建工作表

在工作表的标签上面右键，找删除，就可以删除工作表了

更改工作表的标签颜色

在表的标签上右键，然后选择“修改工作表标签颜色”，就可以自己更改工作表标签颜色了。

插入/删除多个工作表

点击一个工作表标签，然后按住shift，然后再点击一个工作表，就可以选中读个工作表了，然后在标签上点击右键，选择插入，然后选择工作表，然后现在插入的就是三张表额（也就是连选了几个工作表，就能够同时新建几个工作表）。（按住shift进行连选，点中第一个和最后一个就能选中连续的几个工作表）。

反之，删除也一样。用shift连选，然后删除就可以了。

插入行/列，插入多个行或列，移动行或列，调整行高列宽

1，2，3，4行

A，B，C，D列（横行竖列），便于定位

定位到每个单元格

插入空行，直接点击行前的数字，然后右键，选择插入，这样就Ok了。（右键，选择删除就可以删除了）

也可以同时新建多个空行。（选中多行之后，右键，然后选择插入，就可以插入多行了）

列也是一样的。

如果我不小心弄反了c列和d列，我怎么吧两列的次序交换呢？

当我们点击任意一个单元格的时候，如果我们把鼠标一道段元歌表框线上，鼠标聚会变成有上下左右四个方向的箭头。如果把鼠标拉一下，就可以把这个单元格直接拉走（然后我也可以把它拉回来）

这里我们可以先选中c列整列，然后把鼠标放到边框线上，然后鼠标变成上下左右四个方向的箭头，然后直接拉到d列，这时，就会出现窗口，询问是否替换目标单元格内容，当然不能选择替换，替换了就覆盖掉了，原本d列的内容就会消失。所以我们应该按住shift键去拖拽，然后就可以原来的虚线现在就变成了一跳竖线，而不是原来的框了。我们只需要把它挡在d列的右侧，（也就是e列的前面），然后再松开鼠标，在松开shift键，这样两列就把顺序交叉过来了。

调整行高列宽

当我们把鼠标移动到列标签的边框线上时，鼠标样式又变得不同了。（一个竖线，两边是箭头）拉一下鼠标，就可以把列调宽或者窄。也可以不自己左右拉。你可以直接双击，然后单元格大小就可以自动调整到能够容纳内容的大小（自动调整为最窄）。

也可以选择多列，然后你左右拉，你选中的这几列，同时被调整。选择多列，然后在边框线上双击一下，然后就都调整为最窄。

单元格选取、整行整列选取、数据区域选取

怎么样快速到达一个表格的底部呢？

选中一个单元格，然后把鼠标移动到它的（下面的）边框线上，然后把双击鼠标，这样就可以到达表格的底部了。

如果随意选择一个单元格，然后在它上面的边框线上双击，那么就会回到表格的顶部。

最左面和最右面也同理。

使用小工具：冻结窗格、填充柄、查找替换

冻结窗格

看很多数据的时候，往下滑的时候，看不到表头，就会忘记表里面的内容是什么。

但是有些表格怎么样做到滑动的时候一直在顶上不动的呢？

在视图当中，有一项是冻结窗格。点击一下，其中有一项是冻结首行，点击一下，可以看到表格的第一行出现了一条黑线，然后接下来我们再滚动，第一行就不动了。（想取消冻结的话，还是点击冻结窗格，然后单机取消冻结首行，就可以了）

也可以冻结首列。

但有的时候，我们需要的不只是冻结首行。（因为表头信息）

这时候，要选择想要冻结行的下一行的第一个单元格，然后，点击冻结窗格，选中冻结拆分格。然后就会看到，该单元格上面的行都冻结了。（例如，点第四行，那么冻结的就是前三行）

类似地，也可以冻结列。

比如，选择冻结窗格中的冻结首列。

（例如，选中b4单元格，然后在点击，冻结拆分窗格，最后不仅冻结了前三行，也冻结了第一列）

也就是选中单元格的左侧和上部的行和列会被冻结。

填充柄

其实就是拖拽

比如我在f1单元格里写一个1，然后回车，会发现此时单元格的右下角又一个小正方形。当我把鼠标移动到这个小方块上的时候，鼠标的样式再次变化，变成了实心的加号。

此时，当我们吧鼠标左键按住往下拖时，会发现，我们拖拽出来了很多“1”。

但此时如果不拖拽，而是在第二个单元格中写“2”的话。我同时选中f1和f2,那么就出现接着下去的“3，4，5，6，7，8……”（居然会自己找规律）

然后我在个g1单元格中写上今天的日期（即用Ctrl键+：键，按住Ctrl敲冒号），然后再向下拖拽，然后发现，日期的话，也是很自然的一天一天填下来。

那为什么有时候是顺序的，有的时候是复制呢？

比方说，我在d1单元格打“1”，我向下拖拽时按住ctrl，那么我得到的就是序列。

但是日期的话，我按住ctrl键再来拖拽的话就是复制。

但是如果输入2021年3月31日，我们拖拽时，不用左键，而是按住右键，就会出来提示，问是否要按照工作日填充。选中工作日填充，就会填充出星期一到星期五。（右键填充，特别的填充）

拖拽出的东西是可以设置的。

点击文件-选项-高级-创建排序和填充序列的列表

然后点击左侧“新序列”，然后右侧就可以输入序列，然后输入，输入完了，就点击右侧的“添加”，就可以了。

我怎么可以快速选中很多行？

现在名称框中输入单元格名称，比如A10000，然后回车，就会自动跳到该单元格。

然后我可以在名称框中输入2：900，意思是2到900行这样一个范围，回车，然后我选中了这样一个范围

王佩丰第二讲

1.使用单元格格式工具美化表格

（1）“设置单元格格式”的对话框在哪里？

开始-对齐方式栏-点击右下小箭头，是指单元格格式的对话框就出来了。

也可以直接选中某个单元格，然后右键，再选择设置单元格格式

合并单元格

选中要合并的单元格，然后点击 开始-对齐方式栏-合并后居中。

表格的边框？

开始-字体栏-边框（图标在填充颜色的左边）

比如选择所有框线

合并并居中下拉 跨越合并

随手选择一大块单元格，然后点击跨越合并，然后就可以看到这一大块中，每一行的安远隔都核定成了一个单元格，但是每列并没有合并。（即中间有横线，并不是一整块空白）

表格中的斜线怎么设置？

选中单元格，右键，点击设置单元格格式，然后，选择边框。

这里选中两行两列的单元格，然后随便选择边框线样式，然后因为单元格中有很多条线条，可以依据具体情况，点击，然后设置。

斜线？

还是直接边框，然后点一下，就可以设置斜线了。

斜线上下的字怎么写进去的？

首先，双击单元格就可以写字了。

但是如果我想在下一行继续写呢？ 我就必须按住alt，敲击回车，然互就可以到达下一行。

首先，在一个单元格中输入两行字。第一行输完了，然互按住alt，敲击回车，然后就可以输入第二行字了。然后设置斜线。然后再按回车，就出现了斜线上和斜线下两行字，然后选择左对齐，然后自己调整字的位置，做到美观。

格式刷。

点击格式刷，然后把某个格式刷一下，然后再选择一块数据，就会自动粑之前的格式刷过去。

选中-格式刷-再选中

很多地方要刷，先选中想要刷的格式，然后，双击格式刷，然后就可以随便刷了。不想刷了，就按esc键，然后鼠标就恢复正常了。

2.单元格数字格式

在excel当中，会去掉无意义的数字格式。比如，输入2400.00，会直接省略成2400，输入007，会变成7.

设置单元格格式-数字-分类-数值-小数数位，可以自己设置

千分位分隔符，就是千位数字之后的逗号。

点击数字-分类-货币，还可以添加货币符号。

还可以选择，如果是负数，要不要变成红颜色的。

数字-分类-会计专用，会计专用这种格式，他的货币符号是在单元格最左侧的。（而货币格式下，货币符号是在数字前面，贴着数字的）

数字是0的情况下，会计专用会显示为横线，而货币会显示为0。

数字-分类-日期

excel的日期系统，微软采用了1900纪年方式，比如数字1就是1900年1月1日。所以以此类推，1000就是1902年9月26日。

所以当输入今天的日期，2021年4月1日，我们点击设置单元格格式-数字-分类-常规，2021.4.1就会变成44287.

数字-分类-时间

我们打数字1，然后改成时间，就会显示0：00,1.5，就显示12:00.，2，也是0:00.

数字-分类-科学计数

10000，变为1.00E+04，意思是10的4次方

数字-分类-特殊

邮政编码 中文大写数字 中文小写数字

数值永远是数值，只是显示格式变了，复制的话，最后粘贴出来的还是原本的数值。自定义，mmm表示英文月份缩写。y是年，m是月，d是日。aaa显示为一二三四五六日，aaaa显示为星期一星期二星期三……

文本和其他格式不通用

在excel 中，数字分为两类。表示大小和多少的含义。不表示大小和多少。

在excel中写身份证号不能直接写（因为身份证号有18位，而excel只能精确到15位，所以回车之后，后四位就会变成0000），需要提前告诉excel要输入的内容并不是一个表示大小或者多少的概念，而是一个所有数字都要意义的一个编码。所以怎么做呢？

选中要输入身份证号的一列，然后设置单元格格式-数字-文本。这样就不会自动变成科学计数，也不会使得后四位变为0000.（即输入什么就是什么，excel不会擅自删改内容）

但是文本格式之后，再也无法通过“设置单元格格式”改为常规样式。所以，文本格式不能随便改变。

如果一组数字是文本格式，那么这组数字不能运算。但是仍然可以把它改为数字。选中文本格式的数字，然后这时，这些文本格式数字的左上角会出现黄色菱形感叹号，点击这个感叹号，然后，选择“转换为数字”。那么就转换为数字了。

如果数字太多，看不出谁是文本，谁是数字，直接随便找个单元格写“1”，然后右键，复制，然后选择表中数字区域，然后右键，选择选择性粘贴-乘。然后所有的文本格式数字，就会被强制转化为数字格式。

3.使用“分列”工具

怎么把txt文件直接拷进excel？

首先复制txt，然后直接选中A1单元格，然后直接把内容复制进去。然后就会发现很乱，而且所有的表格内容全都在A列的各行里。

然后我们选中整个A列，然后点击开始菜单里的数据，点击数据工具栏的分列。然后就会出现使用分隔符号哈市用固定宽度分。看情况，但一般是选择用分隔符号分。然后就去选择用什么分隔符号分。

excel中提到的符号都是半角英文符号。

如果要按照中文符号分，我们选择‘其他’，然后输入相应的中文符号。

有些情况下，日期是文本格式，跟文本格式的数字不同，不能通过黄色叹号转变。这时要把它改为常规样式，只要选中这些文本格式的日期，然后数据-分列，然后直接点击“完成”。

这样这些文本格式的日期，就可以直接变成常规格式。

王佩丰第三讲

1.查找与替换

任何文本和编辑类工具里都带有查找和替换的功能。

1.快速查找

开始-查找与选择-替换-选项

这里勾选单元格匹配。

就可以把苏州替换为苏州市，而不会吧苏州市替换为苏州市市。

只替换完全符合的选项。

2.按格式查找

颜色的替换。

替换填充色 查找与替换-格式-填充-点选颜色

清除格式

从一种格式变为另一种格式。

3.模糊查找通配符

快捷键：查找Ctrl+F 替换Ctrl+H

（1）一个*号 无论几个字的模糊查找 如：张* 表示张字后全为模糊匹配

（2）英文状态下的?号 一个?号代替一个模糊字 如：张?? 表示两个模糊字

注意：像“张?”，这样“张三三”也会被替换。此时注意单元格匹配的应用，开启单元格匹配后，张?表示一个模糊字，这样单元格内“张三三”就不会被替换了。用?号时，常常需要勾选单元格匹配，为了限制字符。

（3）规避通配符的作用，用波浪线~。如想替换“张*”，需要查找“张~*”。如想替换“张**”，需要查找“张~*~*”（两条波浪线）。~的意义是让后面的字符不生效。

2.定位工具

1.通过名称框定位单元格及区域位置

选区域：如，A9000：B10000 用冒号表示“到”

其中 A9000和B10000是所选区域对角线的两个端点。

选行：如，9000:10000，则选中9000行到10000行，无关列

还可以为某个单元格区域自定义名称，以后可以直接使用这个名称查找。通过输入名称，或运用“查找和替换”>“转到”。

2.定义名称

选择某个区域，然后在名称框中输入一个名字，然后回车，这个名字 就被保留了下来，下一次就可以比较容易找到。

3.定位条件

定位条件

“查找和替换”>“转到”>“定位条件”或“查找和替换”>“定位条件”。如：可以定位所有有批注的单元格，定位到的目标会变成蓝色背景。

批注

标志：右上角红色小三角。

插入批注：右键，插入批注。

显示所有批注：在审阅选项卡。

删除所有批注：选中区域，右键删除批注。

在批注中插入图片：设置批注格式》颜色与线条》填充》颜色》填充效果》图片

合并单元格后空白内容处理

取消单元格合并，定位表内的空值，在一个空的单元格内输入=并按下“上方向键”，之后进行软回车（Ctrl+Enter）。

数据处理中，常常需要这样做。

另：选中一个区域，在一个单元格输入数据，按下软回车（Ctrl+Enter）,则该区域所有单元格都被填上相同的数据。

选中Excel表内的所有图片

定位条件：对象

如果没有图片，那么，Excel的“找不到对象”警告就是这么来的。

通过“选择对象”，就可以实现类似于PowerPoint的框选图片。

王佩丰第四讲  排序和筛选

1.自定义排序

（1）主要和次要的自定义排序

打开自定义排序对话框，添加条件：

或者分别进行单列排序：先依次排次要关键字，再排主要关键字。

（2）按颜色排序

排序依据：单元格颜色

（3）中文汉字排序

默认按拼音首字母排序。若要改变，如排序“一二三四五”，那么要选择次序为自定义序列。

2.复制表头——制作工资条

思路，利用数字排序：

（1）复制表头

（2）增加一列数字

（3）对数字列排序

3.打印要求每页都有表头

页面布局》页面设置》工作表》设置顶端标题行，然后再进行打印。

设置后，第一行名称栏会显示Print Titles。  

4.筛选

（1）打开筛选开关 后，就会出现下拉箭头。

（2）复制筛选后的内容，利用普通复制粘贴，如果出错（旧版本Office），可以尝试定位条件可见单元格。

（3）数字筛选——大于小于等于

（4）同时筛选多列

分别进行筛选，在上一列的筛选结果中再筛选。

（5）文本筛选

利用“开头是”“结果是”或通配符。

5.高级筛选

（1）选择不重复的记录，复制到新的列。应用于找出不重复数据的数量。

（2）条件区域

注意：条件区域的写法，以下图片进行了示例，注意输入表头。

1）“且”（与）的条件 写法：写在同一行

全选的快捷键：先选择A1单元格，按下键盘的Ctrl和Shift，再按下方向键的→和↓。

2）“或”的条件 写法：错开位置

3）条件也可以输入大于小于

王佩丰第五讲 分类汇总和数据有效性

1.分类汇总

(1) 选中某个单元格，点击【开始-排序和筛选】或【数据】里的【升序】或【降序】。

(2) 点击【数据】里的【分类汇总】，选择分类字段(即根据哪一项进行分类)、汇总项(即对哪一项进行汇总、)汇总方式(可对汇总项进行求和、计数、求平均值等操作)。

2.分类汇总的嵌套

根据多个字段进行分类汇总，即依次根据不同的字段进行操作。

(1) 选择主要关键字、次要关键字进行排序(或者先对较后列排序，再对较前列排序)。

(2) 依次以相对主要的关键字为分类字段先进行分类汇总，第二次起注意取消"替换当前分类"的勾选。

(3) 可以通过点击工作表左上角的【1,2,3,4】来查看结果。

3.复制分类汇总的结果区域

选中结果区域，选择【开始-查找和选择-定位条件】，选择【可见单元格】(可用快捷键alt+;)，然后复制。

4.使用分类汇总批量合并内容相同的单元格

(1) 使用排序让内容相同的单元格排在一起

(2) 以该内容所在列的表头为分类字段和汇总项进行汇总，将汇总方式改为计数(不改也问题不大)，确定

(3) 选中新出现的列并选择【开始-查找和选择-定位条件】，定位到【空值】，然后【合并单元格】

(4) 选择【数据-分类汇总-全部删除】

(5) 选中新出现的列，使用【开始-格式刷】，再选中目标列(或者先复制再【选择性粘贴-格式】)。

5.数据有效性(数据验证)

选中目标列，点击【数据-数据有效性】(在excel2016中是［数据验证])选择不同的有效性条件进行设置，如整数、文本长度等，若规定只能输入特定文本，应选择序列，在【来源】中编辑，多个待选文本间以英文逗号";"分隔，可创建出一个下拉列表。也可通过自定义条件来输入判断公式。

6.以数据有效性来保护工作表

选中目标区域，点击【数据-数据有效性】，选择自定义条件，输入一个逻辑值为"false"的公式(可以直接输入0)。

7.数据有效性的其他设置

(1) 输入法切换

可以选择只能以中文或英文输入，功能能否实现和个人电脑输入法设置有关

(2) 出错警告

可以将【输入无效数据时显示的出错警告】改为【警告】并输入警告信息引起别人注意，相当于一种半保护状态。

王佩丰第六讲  认识数据透视表

1.创建数据透视表

(1)选中任意一个单元格，选择【插入-数据透视表-确定】

(2)点中生成的数据透视表，点击右键右键，选择【数据透视表选项-显示】-勾选【经典数据透视表布局】

(3)根据需要将右侧的字段拉入数据透视表中

(4)数据的默认汇总方式是求和，可以双击表中"求和项"(其实我的是双击"发生额")，可以更改汇总方式。双击表中汇总后的数据可以得到一张新表，查看详细记录。

(5)可以将字段拖入"报表筛选字段"中，相当于根据该字段对透视表做筛选。

2.数据透视表中的组合

将多个字段拉入数据透视表中的"行字段"中，注意放的左右位置，无需汇总的字段可以双击该字段，将【分类汇总】勾选为【无】。可以选中分的过于详细的日期，单击右键，选择【创建组】，选择步长。

3.对数据进行划分区间的统计

选中数据字段拉入"行字段"中，再从右侧选中数据字段拉入"值字段"。选中"行字段"中任意一个数据，单击右键，选择【创建组】，确定起止和数值间隔。

4.汇总多列数据

(1)将数据字段拉入"值字段"

(2)再将数据字段拉入"值字段"，此时数据字段上下分，选中"数据"拉到"汇总"上

(3)重复将数据一段拉入"值字段"

(4)双击"求和项"，更改【值字段汇总方式】，也可以单击右键，选择【值字段设置】进行更改。

(5)可以在(4)中更改值字段名称，也可以在编辑栏中更改，不可与原有字段重复。

5.更改表格样式

点中透视表中任意单元格，选择窗口最上面出现的【数据透视表工具-设计】，选择喜欢的样式。

6.在数据表中使用计算

将多个不同的数据字段拉入"值字段"中，选择【数据透视表工具-选项(分析)-域(字段)、项目和集-计算字段】，插入计算字段，编辑名称、输入公式。(比率值可单击右键-单元格格式，设置百分号)。美化有错误值的表格，选中表中任意单元格，点击右键，选择【数据透视表选项-布局和格式】，勾选"对于错误值，显示"。

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

插入数据透视表，分别将字段拖入"报表筛选字段"和"值字段"中。选择【数据透视表工具-选项(分析)-显示报表筛选页-确定】，可根据"报表筛选字段"中的项生成多张工作表。借助shift键选中所有生成的工作表，复制空白区域覆盖生成的透视表。

王佩丰第七讲  认识函数与公式

1.认识Excel公式

(Excel公式必须以"="开始)

(1)运算符

1)算数运算符

"&"连字符，用法:单元格1&单元格2，连接两个单元格的内容(数值相加，文本相接，文本&数值得到相接的文本)

用SUM函数对文本类型的数进行求和运算的结果是0，但可以用"＋"、"–"、"×"、"/"运算

在一个单元格中输入公式后，点中该单元格右下角进行拖拽(或者双击)，可以完成其他所有同类的运算。

2)比较运算符

"＜＞":不等于

比较运算的结果一定是逻辑值"TRUE"(运算中当做"1")或者"FALSE"(运算中当做"0")。

公式里的文本要用""引用。

(2)单元格的引用

相对引用:A1(引用的是相对位置的单元格)

绝对引用:$A$1(引用的是固定单元格)

混合引用:$A1 A$1(只有行或列被锁定)(既要横向又要纵向拖拽，结合"九九乘法表"理解)

2.认识函数

(1)如何使用函数

等号开头、函数名在中间、括号结尾、括号中间写参数

(2)常用函数

SUM:对括号内的数值求和

AVERAGE:求平均值

MIN:求最小值

MAX:求最大值

COUNT与COUNTA:计数

RANK:排名(括号内的参数为参与排名的当前数值和所有数值区域，中间用","隔开，区域应该用绝对引用)

(3)利用定位工具选择输入公式的位置

1)选中目标区域，点击【查找和选择-定位条件-空值】，再点击自动求和工具里的【求和】(在Excel2016中是【自动求和】)，实现跳跃式的求和。

2)选中目标区域，点击【查找和选择-定位条件-空值】，输入公式函数，用"Ctrl+Enter"进行填充。

王佩丰第八讲 IF函数逻辑判断

1.使用IF函数

(1)基本用法(只有两种情况时)

=if(条件，如果符合则A，如果不符合则B)

参数1)条件:进行一次判断，是一个逻辑值

参数2)如果逻辑值为TRUE则在单元格中打印A

参数3)如果逻辑值为FALSE则打印B。

(2)嵌套(有多种情况时)

=if(条件1，如果符合则A，if(条件2，如果符合则B，如果不符合则C))(文本应用""引用，参数间用逗号","隔开)

1)条件1:进行一次判断，是一个逻辑值

2)如果TRUE则打印A

3)如果不为TRUE，而且需要再根据剩下的情况进行区分，则在if函数里再使用if函数，以此类推。(不要遗漏小括号)

(3)尽量避免IF函数的嵌套

1)当IF函数嵌套超过四五层了，应考虑是不是用错了或者改用VLOOKUP函数(以后讲)。

2)可以并列使用多个IF函数。若打印的值为数值，则"IF1(...)+IF2(...)+..."(若为FALSE打印0)；若为文本，"IF1(...)&IF2(...)&..."(若为FALSE则不打印)。

(4)ISERROR函数

判断公式运行是否出错

ISERROR(A):如果运算A出错，则返回TRUE，否则返回FALSE。

2.AND函数与OR函数

(1)AND函数:表示"且"的关系

表示需要满足多个条件:

AND(条件1,条件2,条件3...)

(2)OR函数:表示"或"的关系

表示需要满足至少一个条件:

OR(条件1,条件2,条件3...)

if函数逻辑判断

1.if函数的基本用法

函数语法：IF(logical_est, [value_if _true], [value_if_false])

如果，那么，否则

2.if函数的嵌套

=IF(B2="理工","LG",IF(B2="文科","WK","CJ"))

所有的符号必须都是英文半角

=IF(G2="本地",H2+30,IF(G2="本省",H2+20,H2+10))

=IF(I2>=600,"第一批",IF(I2>=400,"第二批","落榜"))

3.如何尽量回避if函数的嵌套

if嵌套层次非常多，就应该考虑用别的函数

4.用if函数处理运算错误  Iserror函数

iserror是 “是错误”的意思

=IF(ISERROR(D35/C35),0,D35/C35)

2.and函数与or函数

1.and函数：表示“且”的关系

=IF(AND(A3="男",B3>=60),1000,0)

2.or函数：表示“或”的关系

=IF(OR(B12>=60,B12<=40),1000,0)

=IF(OR(AND(A20="男",B20>=60),AND(A20="女",B20<=40)),1000,0)

王佩丰第九讲 COUNTIF函数

1.COUNT函数

用于对给定数据集合或者单元格区域中数据的个数进行计数，只能对数字数据进行统计，对于空单元格、逻辑值或者文本数据将被忽略。

2.COUNTIF函数

(1)语法

=COUNTIF(range,criteria)

range范围:数据集合、数值区域

criteria标准:数值、文本或条件等。

(2)计算数值区间

计数默认最多只能计算数值的前15位，若需计算超过15位，应在后面加上&"＊"。

(3)找重复

检查A列各数据是否存在于B列中，在C1中输入公式"=COUNTIF(B:B,A1)"，往下拖拽，存在为1，不存在为0。(然后可结合IF公式和条件格式着重标出。)

(4)与数据有效性结合使用，禁止输入重复数据

选中A列，点击【数据-数据有效性(数据验证)】，将有效性条件设置为【自定义】，输入公式"=COUNTIF(A:A,A1)＜2(或=1)"即可。

※注意

1)公式中计数时会把当前单元格计入，所以是"＜2(或=1)"

2)若目标区域不是一整列，要注意使用绝对引用。

(5)条件格式

选中目标区域，选择【开始-条件格式-新建规则】功能，根据需要选择规则类型，再为符合条件的单元格设置特别的格式。

3.COUNTIFS函数

表示需要满足一个或不止一个条件时:

=COUNTIFS(range1,criteria1,range2,criteria2...)(只有一个条件则和COUNTIF函数一样。)

1.使用countif函数

countif，区域和条件（range,criteria）

countif只把前十五位作为判断对象

2.常见应用示例

=IF(COUNTIF(G:G,A2)=0,"未体检","已体检")

3.countifs函数

多个条件

=COUNTIFS(E:E,J5,D:D,I5)

王佩丰第十讲 SUMIF函数

1.使用SUMIF函数

语法

=SUMIF(range,criteria,【sum_range】)

查找符合条件的项并相加

(1)range指查找的单元格区域

(2)criteria指条件

(3)sum_range指相加的范围。

※【】中的参数为非必要参数，当"sum_range"和"range"相同时可省略不写

2.超过15位字符时的错误

查找数据 时默认最多只能计算数据的前15位，若需计算超过15位，应在后面加上&"＊"。(和COUNTIF一样)

3.第三参数的简写

(1)参数内range与sum_range的范围必须一样大。

(2)SUMIF有很大的自由度和容错率，当输入的sum_range范围较小时，会默认补充至和range一样大，因此可以简写第三参数。

(3)简写第三参数应保证第三参数的第一行要与range的第一行相对应。

4在多列中使用SUMIF函数

选中整个表作为参数中的range，仅选择一个或几个数值作为sum_range(注意"3.3"中所提的相对应)。

5.使用辅助列处理多条件的SUMIF

当需要满足两个或以上条件时，可新增一列作为辅助列，输入公式"=条件1&条件2&..."，拖拽，以此列作为range，以"条件1所在的单元格&条件2所在的单元格&..."作为criteria。

6.SUMIFS函数

语法

=SUMIFS(sum_range,criteria_range1,criteria1【,criteria_range2,criteria2...】)

(1)sum_range:求和区域

(2)criteria_range1,cruteria1:条件区域1,条件1

7.复习数据有效性(数据验证)

用SUMIF函数做产品出库数量的限制(不能大于库存数量)。

(1)在产品列做一个只包含产品名称的下拉序列:

点击【数据-数据有效性】，设置【验证条件】为【序列】，以产品名称作为来源。

(2)在出库数量列设置数据有效性，设置【验证条件】为【自定义】，输入公式=SUMIF(range1,criteria1,sum_range1)<=SUMIF(range2,criteria1,sum_range2)

即同一产品在出库表中的数量总和不能大于它在库存表中的数量。

sumif函数

range，criteria，sum_range

区域，条件，求和区域

=SUMIF(A:A,F3&"*",B:B)

sumif容错能力。

sumif函数第三参数的简写。

简写第三参数，要注意第一参数是从哪里开始选的，不能错位。第三参数的范围会自动扩充为与第一参数一样大。

sumifs函数

参数

求和区域，条件区域1，条件1，条件区域2，条件2，条件区域3，条件3……

王佩丰第十一讲  VLOOKUP函数

1.VLOOKUP函数语法
=VLOOKUP(lookup_value,table_array,col_index_num【,range_lookup】)
参数(1)查找(匹配)的值 (2)查找的区域
(3)返回的值在当前区域的第几列 (4)"0"精确匹配"lookup_value"，非"0"近似匹配。
※"table_array"必须包含查找的值和返回的值所在的区域，且以查找的值所在的列作为第一列。(注意使用绝对引用)
※若查找的值在列表中有重复，函数只会返回查找到的第一个记录所对应的值。
2.VLOOKUP函数的跨表引用
在需要接收返回值的单元格内输入公式，当需要跨表引用时，直接点击切换到工作簿内的其他工作表进行选择，在输入完逗号后方可切换回来。
3.VLOOKUP中使用通配符
要查找的数据是列表区域中的部分关键字(参数1与参数2中的值不能完全匹配)，可以使用通配符"*":
=VLOOKUP(lookup_value&"*",table_array,col_index_num,0)
4.VLOOKUP模糊查找
找小于等于自己的最大值，适用于找数值区间的划分。
※模糊匹配用的是"二分法"，在使用时需要查找区域的值从小到大排序。
5.使用ISNA函数处理数字格式引起的错误
VLOOKUP函数不能匹配储存格式不一样的数值。
(1)在函数中将数值转换成以文本形式储存的数值:=VLOOKUP(A1&"",...)
(2)在函数中将以文本形式储存的数值转换成数值(常规)格式:=VLOOKUP(A1*1(或A1+0、--A1),...)。(即做一次不改变值大小的运算)
(3)当一列数据中文本格式和数值(常规)格式都存在时
将VLOOKUP函数参数1全部转为其中一种格式进行运算，再利用ISNA函数判断是否会出现#N/A错误，结合IF函数，将有格式错误的再转化为另一种格式。
=IF(ISNA(VLOOKUP1()),VLOOKUP2(),VLOOKUP1())
※上述方法比较复杂，只是为了探讨函数的可行性。较好的做法还是先将两列转化为相同的储存格式。
6.HLOOKUP函数
用法与VLOOKUP函数基本
一样VLOOKUP函数适用于以一行为一条记录的表格，而HLOOKUP用于以一列为一条记录的表格。

第十二讲 MATCH+INDEX

1.函数语法

(1)=MATCH(lookup_value,lookup_array【,match_type】)，函数返回的是查找的值在所选区域的第几个位置。

参数1)需要查找的值

2)在哪列或哪行查找(可以不是一整列或一整行)

3)"0"精确匹配

(2)=INDEX(array,row_num【,column_num】)函数返回的是对应单元格的值

参数1)引用哪一区域

2)引用第几行的值

3)引用第几列的值

将MATCH的返回值作为INDEX的第二个参数:=INDEX(A,MATCH())

2.MATCH+INDEX与VLOOKUP函数的比较

(1)VLOOKUP函数只会查找选中区域的最左列，而且引用列在查找列的右边，不能做从右向左的查找引用。MATCH与INDEX函数的查找和引用是分开进行的，不存在列序的矛盾。

(2)VLOOKUP只能查询返回一个值，不能引用照片，INDEX可以。

3.认识COLUMN函数

=COLUMN()

查询单元格在第几列，若无参数则返回当前单元格在第几列。

4.VLOOKUP函数嵌套返回多列结果

(1)与COLUMN函数嵌套使用

=VLOOKUP(lookup_value,table_array,COLUMN(),0)

(2)与MATCH函数嵌套使用

=VLOOKUP(lookup_value,table_array,MATCH(),0) 通过MATCH函数查找出需要引用的值所在的列号，返回给VLOOKUP函数做第三个参数。

※在需要进行拖拽时要注意混合引用。

5.INDEX函数引用图片

(1)在工作表中添加图片。

(2)点击【公式-定义名称】，如定义名称为"图片"，在【引用位置】中输入引用图片的函数。

(3)a.在【文件-选项-自定义功能区】中添加【照相机】功能到【新建选项卡】。选中用于接收图片的单元格，点击【新建选项卡-照相机】，画一个框，在编辑栏中输入"=图片"，回车。

b.复制图片，粘贴到用于接收图片的单元格，点中图片，在编辑栏中输入"=图片"，回车。

※按住alt移动图片可以让照片自动贴近单元格边框。

第十四讲 时间函数

1.认识时间和日期

1)日期格式

Excel日期采用"1900纪年方式"，日期可转换为整数，即从1900年1月1日开始的第几天。

2)时间格式

时刻可以转换为小数，即到了该时刻，该天已经过去了多少。

3)基本的时间与日期运算

a.时间运算:注意单位换算，天/小时/分钟/秒钟=1/24/60/60。

b.日期运算:日期可与整数相加减，可更改单元格数字格式得到日期或者数值。

2.日期函数

1)YEAR、MONTH、DAY函数

分别求参数(一个日期)的年、月、日。

2)DATE函数

将三个参数相组合，生成一个日期，三个参数依次是生成日期的年、月、日(若代表月、日的数字过大，会向前进位；若小于1，则会退位)。

3)DATEDIF函数

=DATEDIF(日期1,日期2,"参数3")

参数1和参数2是两个不同的日期，日期1要比日期2小；参数3是"y"、"m"或"d"。

DATEDIF函数用于比较两个日期的不同，参数3决定返回值是年、月还是日的差别。

参数3还可以是"ym"、"md"或"yd"，分别表示"刨除年剩余多少月"、"刨除月剩余多少天"、"刨除年剩多少天"(相当于求余)。

4)WEEKNUM

返回参数1(日期)是该年中的第几周，参数2可以设置以星期几为一周的开始。

5)WEEKDAY

返回参数1(日期)是该周的第几天(1-7，也可以选择返回0-6)，参数2可以设置以周几为一周的开始。

6)"整容大师"

※复习:自定义单元格数字格式

aaaa:星期几

aaa:几(星期几中的"几")

0000-00-00:将假日期转化为日期。

=TEXT()函数，可通过格式代码向数字应用格式，进而更改数字的显示方式(根据指定的数值格式将数字转成文本)。

参数1：一个数值

参数2：数值格式。日期函数

第十五讲  条件格式与公式

1.使用简单的条件格式

1）为特定范围的数值标记特殊颜色

*标记大于1500000的值

选中数据范围-开始-条件格式-突出显示单元格规则，设定标记值范围。

2）清除规则

开始-条件格式-清除规则

3）回顾数据透视表

4）在数据透视表中标记数据条

选中数值区域-条件格式-数据条

5）在已设置条件格式的数据透视表中设置“切片器”

位置：插入-切片器-选择其中一项值

注：03版Excel没有切片器功能

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

原理：设置多重条件不会覆盖替换前一个条件格式

但是后一个条件包含前一个条件就会覆盖如小于100万、小于200万，想要兼容的做法是先设定一个大的范围标记，再设置小的

条件格式-新建规则

其中为包含以下内容设置规则里，可以设置错误值、空值、无空值等的格式

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

1）使用场景如：将数量大于100的项目日期标记为红颜色背景

条件格式-新建规则-使用公式确定单元格

=d2>100，设置样式

2）使用场景如：将数量大于100的项目整行标记为红颜色背景

=$D2>100

4.课后作业

1、标记周末为红色背景

条件格式-新建规则-使用公式确定单元格

公式：=WEEKDAY(B2,2)>5

2)将周末整行标记为红颜色背景

条件格式-新建规则-使用公式确定单元格

公式：=WEEKDAY($B2,2)>5

2、标记未来15天过生日的员工姓名为红色背景

公式写法：=DATEDIF(C2,TODAY()+15,"yd")<16

解释：TODAY()函数意为今日日期，TODAY()+15为今日往后未来15天

"yd"表示不计年份计算相隔天数

DATEDIF(C2,TODAY()+15,"yd")表示从1967/6/15到今日往后15天，相隔的日子，

得出的值小于16，等于小于15的，标记为红色。

第十六讲  简单文本函数

1.使用文本截取字符串

1)LEFT函数：从一个文本字符串的(左边)第一个字符开始返回指定个数的字符

=LEFT(text【,num_chars】)

a.参数1：一个文本(单元格)

b.参数2：指定返回字符的个数(若无规定，默认返回1个)。

※若参数2大于文本长度，则返回整个文本。

2)RIGHT函数

同"1)LEFT函数"，只是从文本字符串的右边(即最后一个字符)开始，(按照原顺序)返回指定个数的字符。

3)MID函数

返回文本字符串中从指定位置开始的特定数目的字符。

=MID(text,start_num,num_chars)

a.参数1：一个文本(单元格)

b.参数2：开始返回的字符所在的位置

c.参数3：返回字符的个数

※a.若参数2小于1，则返回错误

b.若参数2大于文本字符个数，则返回空

c.若参数2加参数3大于文本字符个数，则返回从参数2位置开始到文本结尾的所有字符

d.LEFT函数和RIGHT函数嵌套使用可以替代MID函数。

2.获取文本中的信息

(1)LEN函数

返回文本的长度。

=LEN(text)

(2)FIND函数

返回一个字符串(子串)在另一个字符串中出现的起始位置(区分大小写)。

=FIND(find_text,within_text【start_num】)

a.参数1：一个子串(可以是单个字符、数字)，需要查找的文本。若是字符，要用""引用。

b.参数2：一个文本(字符串)，即在此文本内查找

c.参数3：规定开始查找的位置，若无，则默认为1，即从文本第一个字符开始查找。

※a.若参数2不是参数1的子串，则返回错误

b.若参数3大于文本长度或小于1，则返回错误

c.若参数1为空，则返回参数3的值。

★补充

上述LEFT、RIGHT、MID、LEN、FIND有与其对应的-B函数(如LEFTB)。前者返回的是字符个数，后者返回的是字节个数。

※单个字母或数字、符号占1字节，单个汉字占2字节。=LENB(A1)-LEN(A1)得到的值就是A1中的汉字个数。

※对文本处理得到的数字还是文本。

3.关于身份证

(1)第1-6位：地区码

(2)第7-14位：出生日期

(3)第15-17位：顺序码(奇数男，偶数女)

(4)第18位：校验码，用于检验身份证第18位是否符合GB11643-1999规则。课件中有具体算法。

第十七讲 数学函数与引用

1.认识函数

(1)ROUND函数

按指定的位数对数值进行四舍五入

=ROUND(number,num_digits)

a.参数1：要进行四舍五入的数字

b.参数2：要进行四舍五入的位数。

※若参数2＞0，则四舍五入到指定的小数位数；若=0，则四舍五入到最接近的整数；若＜0，则四舍五入到小数点左边的相应位数。

※ROUNDUP函数：按指定的位数，朝着远离0的方向，对数值进行向上舍入。参数同上。

ROUNDDOWN函数：按指定的位数，朝着0的方向，对数值进行向下舍入。参数同上。

(2)INT函数：将数值向下取整为最接近的整数。只有一个参数，不能规定位数。

(3)MOD函数

返回两数相除的余数，余数可以包含小数部分。

=MOD(number,divisor)

a.参数1：除数

b.参数2：被除数

※MOD(A1,1) 求A1的小数部分。

(4)ROW行函数与COLUMN列函数

=ROW(【reference】)：求单元格的行号

=COLUMN(【referenxe】)：求单元格的列号

若不带参数，则对当前单元格进行运算。

2.函数应用实例

(1)通过身份证号码判断性别

取身份证的性别判断位，用MOD函数来判断其奇偶性，再用IF函数来判断男女。

(2)特殊的舍入方式(结合实例理解)

以0.5为基本单位的向下舍入：

1)=IF(MOD(A1,1)＜0.5,INT(A1),INT(A1)+0.5)

2)=INT(A1*2)/2

(3)基于位置规律的引用

INDEX函数与ROW函数、COLUMN函数的嵌套使用。

※复习VLOOKUP函数和INDEX与MATCH函数的用法。

第十八讲 LOOKUP和数组

1.回顾统计函数

回顾SUMIF和SUMIFS函数的用法，在第十讲，大家自己回头翻吧~

2.认识数组

(1)数组生成原理

用SUM函数替代SUMIF(S)

=SUM(条件判断1*条件判断2*...*条件判断n*求和项)

eg:

=SUMIF(A1:A100,H1,B1:B100)→SUM((A1:A100=H1)*B1:B100)

※a.A1:A100和B1:B100是两个一维数组。

b.在编辑栏可以用F9键查看数组的运算结果，按Esc或"✘"可退出。

c.写完公式以后，不要直接按Enter键，而是按Ctrl+Shift+Enter组合键锁定数组公式，Excel将在公式两边自动加上花括号“{}”。

d.不要引用一整列(行)，而且此处的两个数组长度应该一致。

(2)SUMPRODUCT函数

作用与SUM相同，但不用三键组合使用，直接回车即可。

3.LOOKUP函数基本应用

(1)认识LOOKUP函数

从单行或单列或从数组中进行查找并返回一个值。本讲只学习从行或列中查找。

=LOOKUP(lookup_value,lookup_vector【,result_vector】)

a.第一参数：需要查找的值

b.第二参数：查找的区域，只包含一行或者一列

c.第三参数：返回的值所在的区域，只包含一行或者一列

※第二参数的区域大小必须与第三参数相同。

(2)LOOKUP函数模糊匹配

LOOKUP与VLOOKUP函数比较

1)LOOKUP函数第二参数只有一列，而VLOOKUP函数必须包含查询列和返回值所在的列。相较之下，LOOKUP函数更加灵活。

2)LOOKUP函数只有3个参数，不能设置精确匹配。

3)使用LOOKUP函数时，若查找列按升序排列，运算结果与VLOOKUP函数精确查找一致。

(3)LOOKUP函数多条件精确匹配

LOOKUP函数在查找时虽然不能设置精确匹配，但是会自动回避错误值。故可将所有不匹配值转化为错误来进行精确匹配。

eg:

=LOOKUP(1,0/(A1:A100=Y1)*(B1:B100=Y2),Z:Z)

0作除数的#N/A错误会被函数回避，只能匹配到唯一无错误的值。

第二十讲 图表基础

1.认识图表中的元素

图片/图表如何自由伸缩隐藏：设置图片格式-属性-随单元格改变位置和大小

1）图表标题、坐标轴标题、图例、数据标签、模拟运算表（数据表）、坐标轴、网格线

2）图表设计-添加图表元素：可以选择图表标题、坐标轴标题、坐标轴、网格线、图例等

a.选中图表标题文本框，在编辑栏输入=B1，标题会自动随B1切换

b.设置坐标轴格式：含刻度线、标签、数值，选中坐标轴文字-设置坐标轴格式-选项-标签，可以选择轴旁、高低等。

纵坐标是数值的话，可以设置比例尺大小，即边界的最大值最小值，间隔单位值。

设置横坐标重排顺序、纵坐标移到右侧（镜像）：坐标轴选项-逆序类别。

2.主次坐标系

1）如各个地区有销售额、指标完成率，插入柱形图表后，

a.点击指标完成率的柱形图（或格式-左上角下拉框选择“指标完成率”-设置所选内容格式），选择系列绘制在次坐标轴上。

b.再点击指标完成率的柱形图，右键更改系列图表类型-折线图。（在组合图选项中为指标完成率选择折线图，勾选次坐标轴），在折线上右键设置数据系列格式，标记-勾选自动，也可内置选择大小或形状。

c.根据实际情况美化，分别设置主次纵坐标轴的边界大小值、间隔值。根据需要设置标签、刻度线等为无。

d.图表设计-添加图表元素，可设置网格线为虚线等样式；可设置图例位置；选中整个图表可直接修改字体；折线图-设置数据系列格式-线条颜色、标记填充色、标记边框线颜色设置钟意的颜色。

e.分别点击柱形图和折线图，添加数据标签值，根据需要更改字体颜色、大小。

2）如每个人有指标金额、实际完成额，插入柱形图表后，

a.点击指标金额的柱形图，选择系列绘制在次坐标轴上。注意两侧纵坐标轴刻度一致。

b.点击指标金额的柱形图，设置数据系列格式-设置填充色无色，边框线颜色和实线粗细。点击实际完成额柱形图，设置数据系列格式-设置填充色、边框线颜色。

3）改变柱形图形状，如将柱形图改为许多爱心/手机/树木等显示

在其他地方，插入图形爱心，选择填充色，复制，再点击图表里的柱形，粘贴，然后点击设置数据系列格式-填充-层叠。 如果图形显示太紧密，可以在绘制爱心后，再绘制一个大点的无填充色无边框线颜色的矩形框，置于爱心底下组合（无法选中两个图形时可以按查找-选择对象），再进行复制粘贴。

4）网上看到喜欢的图表样式，复制下来，右键图表-另存为模板，重命名即可。

第二十一讲 动态图表

动态图表（对比简单的切片器自由度更高，可加DIY的控件）

1.利用控件做动态图表

图表的本质：几列/行数据源共用一对X轴Y轴。因此想要通过勾选控件显示图表里对应数据的话，需要先赋予每个控件公式从而引用不同列的数据源。

1）用IF函数做控件-假设需要两个控件（广州上海）

a.【加控件】开发工具-插入-表单控件-复选框，即打勾框，右键编辑文字输入广州上海， 先打勾，右键设置控件格式，已选择打勾，单元格链接中分别输入或选择$G$2, $G$4（这两个为任意空白单元格），此时勾选控件与否，G2G4单元格会显示TRUE/FALSE。

b.【输公式】随便另找个单元格G8，输入=IF（$G$2，$B$2：$B$13，$F$2：$F$13），表示如果G2为TRUE，链接B2-13数据列，否则链接F2-13空白数据列，注意绝对引用。

c.【定义公式】复制公式，ESC退出，选择公式-定义名称，名称输入广州，引用位置-粘贴刚才的公式。此时G8单元格无用，可以删掉。

d.【加图表】无需选数据源，插入空折线图，右键选择数据，点击添加，系列名称输入广州，系列值输入=sheet1!广州（英文模式下的!），确定出现相应折线图。点击空白单元格，操作上海同理。

e.【统一纵坐标】折线图两条折线对应的纵坐标轴刻度应一致，右键纵坐标轴设置格式，边界单位大小值勾选固定(新版无固定选项，分别勾选控件直接输入相同数字即可)。

f.【移动控件】两个控件编辑文字删掉文字，移动框框到图表相应图例前方，如果看不见将图表置于底层，将图表和控件组合，方便一起移动。

 3）利用OFFSET做动态图表-加左右滑动滚动条（滚动看数据以及滚动取几行）

a. 【加控件】开发工具-插入-表单控件-滚动条（第2排第3个），在表格里任意位置横着拉一个，再复制粘贴另一个。选择一个滚动条，设置控件格式，最小值输入1，最大值看表实际多少行，比如输入100，单元格链接输入$D$2，另一个滚动条同理，单元格链接输入$D$4，此时拉动滚动条，D2D4会有数据变化。

b. 【输公式】在其他单元格输入公式=OFFSET（$B$1,$D$2,0,$D$4,1）。

c. 【定义公式】公式-定义名称，输入名称“成交量”，公式粘贴如上。

d. 【加图表】插入空柱形图，右键选择数据，点击添加，系列名称输入成交量，系列值输入sheet1！成交量（英文模式下的！）点击确定即可。

e. 【统一横坐标】同时，为了保证X轴动态显示日期，公式-定义名称，输入“日期”，公式粘贴=OFFSET（$A$1,$D$2,0,$D$4,1），在图表上右键选择数据，在右侧“水平分类轴标签”点击编辑，在轴标签区域框内输入sheet1！日期，点击确定即可。

f.【移动滚动条】移动滚动条至图表里相应位置，操作同控件。

第二十二讲 甘特图与动态甘特图

1.制作双向条形图（补第二十讲内容）

有一张表显示不同年份的出口和内销比例。

1）选择表格数据，插入条形图，选择出口的条形，设置数据系列格式，绘制在次坐标轴上。

2）选中主坐标轴/次坐标轴（任意一个都可），设置坐标轴格式为逆序刻度值。

3）分别去选主坐标轴、次坐标轴，将边界最小值都修改为-1，最大值都修改为1。

4）a.选中上面的次坐标轴，delete删除；

b.选中任一网格线，delete删除；

c.添加数据标签，修改字体颜色；

d.选中年份，设置坐标轴格式-标签-位置改为高/低；

e.选中下行的主坐标轴，设置坐标轴格式，修改合适的间隔单位值，坐标轴选项-数字-格式代码里已有0%，再补输入“；0%”（注意英文模式的；表示负数形式和正数一样），再点击添加；

f.分别选中出口、内销的条形，设置数据系列格式，修改间隙宽度，比例越小，条形越粗，另外设置喜欢的填充颜色、阴影效果；

g.插入想要的背景图片，按Ctrl+C，然后点击图表，右键设置图表区格式，填充-图片或纹理填充，点击剪贴板，图表背景就变了，但是绘图区不会变，再点击绘图区，设置绘图区格式，点击无填充，背景太深还可调节透明度或插入图片后设置艺术效果虚化。

引申：添加一张难看的图片做背景，如何变好看？选择图片-格式-艺术效果选项最下面，艺术效果选择虚化，拉高半径（辐射）。

第二十三讲 EXCEL图表与PPT 1

1.双坐标柱形图补充

在第二十讲中，有提到两个例子，销售额与指标完成率（柱形图加折线图）、销售额与实际完成额（两个柱形图，其中一个用无填充色的框线重合表示）。如果销售额与指标完成率想用两个不同颜色柱形图并列表示，纵坐标左边是数额，右边是比率，如何操作？

1）选中数据，插入柱形图，点击指标完成率的柱形图，右键设置数据系列格式，选择系列绘制在次坐标轴上。

2）在图表上右键选择数据，点击添加，系列名称空着，系列值改为={0}，再重复操作添加，此时多了两种颜色的柱形分别为系列3、系列4。

3）选中任一系列，如系列3（格式-系列3-设置内容格式），绘制在主坐标轴上。

4）点击图表选择数据，在添加下方通过▲▼调整四个系列位置穿插分布（如系列4放第一、系列3放第三），使得图表销售额、指标完成率两个柱形肩并肩显示。如果有空隙，将两个图形设置格式，系列重叠均改为0。

5）删掉图例中的系列3、4，调整纵坐标轴数字格式。

2.饼图美化

1）单饼图美化

选中数据，插入三维饼图，右键三维旋转，把自动缩放的勾去掉，高度默认100改为30，效果-三维格式-顶部棱台，效果-阴影-居中阴影，添加数据标签。

2）双层饼图美化

把谁放前面，就先做谁。ABC列分别是部门、城市、金额，B15:B17是部门的汇总金额。

a.选中B列城市C列金额即B2:C10数据，插入二维饼图。

b.点中饼图，右键选择数据，点击添加，系列值选中B15:B17的数据区域，点击确定。

c.点击饼图，设置数据系列格式，绘制在次坐标轴上。

d.把整个图表区拉大一些，点击饼周围，把绘图区拉小一些，选中饼图（所有饼形），往外拉一些，像分散的三角披萨，再点击其中一块饼形（鼠标多点几次），往圆心拉回去，其余饼形依次照做。

e.点击里面的圆饼，添加数据标签，显示类别名称和百分比，调整字体大小，点击外面的环形，添加标签同理（类别名称只会显示123，在此之前选择饼图点击右键选择数据，在系列2中右边框编辑123，改为A15:A17的数据区域）。

3.图表与PPT

PPT上方出现图表工具以及图表设计和格式时，表示粘贴的是图表，不是图片。

1）PPT如果有模板配色方案（设计-颜色），复制粘贴来自EXCEL的图表时，图表颜色也会变换。 如何改回原来的图表颜色呢？

复制粘贴后，图表右下方有个粘贴选项，选择第二个保留源格式和嵌入工作簿。

2）如何使得PPT的图表自动更新表格数据？ 

复制粘贴后，图表右下方有个粘贴选项，选择第四个保留源格式和链接数据。 如果PPT是关闭状态，打开后，点击图表工具-图表设计-刷新数据即可。

如果PPT有许多页图表，如何一步到位更新表格数据？

复制原图表后，在PPT中，开始-粘贴-选择性粘贴，点击粘贴链接（EXCEL图表对象）。 此后关闭PPT后，修改EXCEL数据，再打开PPT，会弹出一个窗口-点击更新链接即可。

3）如何让PPT里的图表柱形一个个出现？

复制粘贴图表后，选择动画-动画窗格-擦除，在右侧动画方框里，点击动画效果1，右键效果选项，点击图表动画，选择相应的下拉选项。 按系列-黄色柱形一起先出来，再蓝色柱形； 按分类-北京的黄蓝一起先出来，再上海再广州； 按系列中的元素-北京黄，上海黄…… 然后北京蓝，上海蓝…… 按分类中的元素-北京的黄，北京蓝依次出来，再上海再广州。

如果中间有些动画效果需要跳过，选中不需要的第一个，按住shift键选中不需要的最后一个，右键-从上一项之后开始。

第二十四讲 宏表函数 2

2.GET.WORKBOOK提取工作簿

GET. WORKBOOK (type_num,name_text), name_text表示打开的工作簿的名字，type_num是类型号，常见的如下：

1——返回工作簿所有工作表的名字

3——返回工作簿当前选择工作表的名字

4——返回工作簿中工作表的个数

38——返回活动工作表的名字

1）点击工作表任意单元格，公式-定义名称，名称随便输入如“工作表名”，引用位置输入“=get.workbook(1)”，然后在工作表任意单元格输入“=工作表名”，就会显示工作簿的第一个工作表名字（只显示第一个，但是在编辑栏选中工作表名，按住F9，可以看到一个数组，显示所有的工作表名字）。如何全部显示呢？在第一个单元格输入“=INDEX（工作表名，1）”然后其余参数改为2、3……或者输入“=INDEX（工作表名，ROW（A1））”下拉即可。

2）函数HYPERLINK（link_location,【friendly_name】），第一个参数是超链接的地址，第二个参数是显示的名字，可省略。如果想为工作表名字做超链接，单元格输入改为=HYPERLINK（INDEX（工作表名，ROW（A1））&“！A1”），注意是英文模式下的！，然后公式下拉即可，超链接不能直接链接到某一工作表，必须到某一工作表里的某一单元格。

3.EVALUATE函数

1）运算

假设A3:A5是一串没有=的公式，如3+4、8*9，求运算结果。那么点击B3单元格，公式-定义名称，名称随便输入如“运算”，引用位置输入“=evaluate(A3)”，不能绝对引用，然后在B3单元格输入“=运算”，下拉即可。