王佩丰excel课件_王佩丰Excel 2010视频教程笔记(1-8讲)

网易云课堂上有王佩丰老师的Excel教学视频，以下是链接地址。

跟王佩丰学Excel视频教程：Excel实战1800分钟 - 网易云课堂​study.163.com

在网页版左下角的参考资料里可以下载每节课的课件

第一讲 认识Excel

Excel界面

移动行/列

选中行（或列），光标移到上下两边任一边（或左右两边任一边），按住Shift键，移动光标，即可移动行（或列）的位置

定位边界处的单元格

将光标分别置于单元格的上下左右边界处，双击，可分别定位到与该单元格对应的表的上下左右边界处的单元格

整行整列选取

在名称框中输入要选取的行或列。 比如，B:E选择B到E列，3:5选择3到5行

插入当前日期和当前时间

插入当前日期，按Ctrl+;；要插入当前时间，按Ctrl+Shift+;(Windows) 或者Command+;(Mac)

第二讲 格式设置

双表头设置

先对单元格插入斜边框，在一个单元格内输入一行文字，然后Alt+回车(Windows)或者Option+回车(Mac)，在该单元格内输入第二行文字，最后调整两行文字位置

单元格数字格式

• 单元格数字格式无论怎么变，数字本身的值不变，可以在编辑栏中看到数字本身的值
• Excel默认1900年1月1日为1，1900年1月2日为2，以此类推…… 因此如果将日期改为数字，则该值表示距离1900年1月1日的天数
• 单元格的自定义格式中，输入;;;(三个分号)，可以将该单元格的值隐藏
• 利用分列工具可以直接将文本格式的日期（比如‘2017/01/01’）直接转换为数字格式的日期，然后通过设置单元格格式可以改为‘2017年1月1日’

第三讲 查找、替换及定位

通配符

*: 任意多个字符

?: 任意一个字符

~: ~?、~*、~~可分别查找?、* 、~本身

单元格匹配

在查找或替换中开启“单元格匹配“功能会以是否匹配整个单元格内容为标准来查找和替换

快捷键

查找：Ctrl+F

替换：Ctrl+H

定位条件：Ctrl+G

定位条件

利用定位条件可以选中一些特殊的单元格，比如带批注的、带公式的

单元格填充

• 在指定单元格区域填充相同的值：选中需要填充的单元格区域，输入要填充的内容，按Ctrl+回车，所有的单元格内都会是该内容
• 生成序列：在第一个单元格写0，光标移到单元格右下角，按住光标往下拉的同时按住Ctrl键，会让你选择填充方式，选择填充系列，就会生成0、1、2、3的序列

批量填充

如上图所示，要将空单元格内填上与上一单元格相同的内容。先选中表所在区域，用定位条件，选择空值，将空单元格选中，然后输入=，再按上键，让每一个空单元格的值等于其上面单元格的值，再按Ctrl+回车，即得到上图右边所示结果

批量删除表中图片

定位条件选择对象，可以全选表中的对象，包括插入的图片、形状等

第四讲 排序与筛选

自定义排序

对于“一部”、“二部”、“三部”这样的中文，Excel默认按拼音首字母顺序排序。如果要按照自己规定的顺序排序 ，在自定义排序中，点击顺序中的自定义列表，创建需要的顺序(Windows)或者在Excel偏好设置中，选择自定义序列，添加需要的顺序，然后在自定义排序中，点击顺序中的自定义列表，选择刚才创建的顺序(Mac)

打印工资条

要将原始表格变成工资条

原始表格

工资条

首先将表头复制相应的份数

在表的最后加一列，输入如下图所示的序列，然后进行排序即可

打印表头

页面布局-打印标题-顶端标题行，选择表头，打印出来的表每一张第一行都是标题

高级筛选

假设有这样一张表

• 要选出所有一车间的邮寄费条目，在高级筛选中的条件区域选择以下内容

• 要选出财务部的数据或者发生额大于3000的数据，在高级筛选中的条件区域选择以下内容

• 要选出一车间的数据或者大于3000的二车间的数据或者发生额大于10000的数据，在高级筛选中的条件区域选择以下内容

写在同一行的条件表示与(AND)，写在不同行的条件表示或(OR)

选中单元格所有区域的方法：用光标选中工作表左上角第一个单元格，按住Ctrl+Shift，再按右键、下键可以选中工作表的所有区域

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

分类汇总

使用分类汇总前要先排序

假设有以下这张表

要按所属区域和产品类别分别汇总

1. 用自定义排序将所属区域和产品类别两项排序
2. 先将所属区域进行分类汇总
3. 再将产品类别进行分类汇总，取消勾选替换当前分类汇总

点击左上角的3，可查看分类汇总结果

复制分类汇总的结果区域

如果直接复制分类汇总的结果区域，会将所有的数据都复制，可以先用定位条件选择仅可见单元格，再进行复制粘贴

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

有些时候会遇到这种情况，几列相同的单元格，合并之后再取消合并，只剩下第一个单元格的内容 比如针对如下表格的所属区域

先按所属区域进行分类汇总，得到如下结果

对于A列，通过定位条件选择空值，选中所有空单元格，然后合并

然后删除分类汇总

将A列的单元格复制，选择性粘贴格式到B列。 通过这种方式合并的单元格，在取消合并时单元格内所有的数据都还在

序列方式设置序列有效性

付款方式只能选择现金、转账、支票中的一种

数据有效性(Windows)或者数据验证(Mac)。“允许”选序列，“来源”中填“现金,转账,支票”，注意逗号是半角符号。付款方式下的单元格会生成下拉菜单，只能选这三个值

第六讲 数据透视表

在透视表中使用公式

选中数据透视表中的任意数据，点击选项卡中的数据透视表分析——字段、项目和集——计算字段，可以输入公式

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

如下图所示，每一个费用生成一张工作表

在数据透视表的筛选器和值中均拖入科目划分这一项，在数据透视表分析—-选项——显示报表筛选器页中，显示全部页面，就可以生成所有的工作表（在工作表标签中，按住Shift再点按工作表，将这些工作表全部选中，删除其中一张表的内容，其他表相应区域的内容也会被删除）

第七讲 认识公式与函数

运算符

&：连字符，文本连接

^：求幂，A^B表示A的B次幂

相对引用和绝对引用

写一个公式：=E1+B1。如果直接按住单元格右下角向下拖拽，下面的单元格内会自动生成公式：=E2+B2，=E3+B3…… 这是相对引用，即总是对单元格同一行的数值进行操作。选中公式中的B1，按F4，B1会在$B$1、$B1、B$1、B1之间切换，其中带$的就是绝对引用。$B$1表示所有行和列的单元格的公式都只针对B1单元格的内容进行操作

排名函数

rank(number, ref, order)
number表示需要排名的数字
ref表示排名的区域，注意排名区域如果固定的话请用绝对引用
order表示排序方式。order=0或省略按降序排列，order=1按升序排列

单元格不连续时分段用函数

针对上图这种分段的单元格求和，根据之前的方法选中D列所有空单元格，使用开始菜单中的自动求和，会自动分段求和

针对上图这种多列应用函数，根据之前的方法选中表中所有的空单元格，C2单元格会显示白色，输入=B2/A2，按住Ctrl+回车，所有单元格中的值都是它左边第一个单元格的值除以它左边第二个单元格的值

第八讲 IF函数逻辑判断

IF函数嵌套

如果B2单元格内容为“理工“，该单元格内为“LG“，如果B2单元格内容为“文科“，该单元格内为“WK“，如果B2单元格为其他内容，该单元格为“CJ“，函数为=IF(B2="理工","LG”,IF(B2="文科","WK","CJ”))。Excel2016及其之后的版本中有IFS函数，可以实现同样的功能，函数为=IFS(B2="理工","LG",B2="文科","WK",B2="财经","CJ")

如果嵌套次数过多，不宜使用IF函数，应使用VLOOKUP函数

IF函数处理运算错误

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

IF函数和逻辑联结词连用

对于60岁以上的男员工或40岁以下的女员工给予1000元奖金 =IF(OR(AND(A20="男",B20>60),AND(A20="女",B20<40)),1000,0)