王佩丰Excel基础教程24讲完整版-带完整练习资料和答疑服务

【1~15讲学习笔记】

ps：易掌握点；难点

第一讲

• 多行多列自动调整合适的列宽行高

• 快速定位至表格边界区域（Ctrl+方向键也可以）

• 快捷键：今天日期（Ctrl+：）

• 鼠标右键下拉填充，可选择填充方式

• WPS-选项-自定义序列（可设置常用内容）

• 快速选择多行多列直接输入（头单元格：尾单元格）

第二讲

• 批量格式刷（双击格式刷可快速实现）

• 单元格格式-自定义，给数值后面增加单位

• 单元格格式-自定义类型解读及运用（主要讲日期）

• 文本格式的数据转换成数值（两种方式）

• txt文档的数据复制到表格，一列快速分成多列（数据-分列）

• 文本格式的日期转换成其他格式（数据-分列）

第三讲

• 查找替换的高级运用：
• 单元格匹配
• 通配符：*代表多个字符，？代表一个字符，~代表后面字符不生效

• 快速选择任意的多行或多列

• 表格常用区域

• 改变批注形状、显示及隐藏批注

• 批注中加入图片

• 用定位查找批注、公式

• 批量填充数值：先选中要填充的区域-填充数值-按住Ctrl+回车键
• 本单元格数据快速填充到下面空的单元格：选中空的单元格（或用定位-空值）→敲“=”+“上方向键”→按住Ctrl+回车
• 批量删除表格中的图片：用定位-对象

第四讲

• 自定义排序的高级运用

• 工资条制作（利用排序）

• 筛选的数据复制粘贴到其他表格：定位-可见单元格（分类汇总也是同样操作）

• 高级筛选运用（横行且，竖行或）

• 筛选出发生额大于3000的数据
• 快速选中区域快捷键：Ctrl+shift+方向键

第五讲

• 分类汇总（先排序再分类）

• 不规则多行、有相同数据快速合并单元格（WPS有直接合并相同单元格的功能）

• 数据有效性（比较常用的：金额、文本长度、序列）

• 利用数据有效性切换输入法（WPS暂时没看到有这个功能）

第六讲（多看几遍，扩展自己的制表思路）

• 一列=一个字段；一行=一个完整的记录
• 数据-数据透视表-右键(数据透视表选项)-显示-勾选经典透视表布局
• WPS拖到选项垂直往下的区域(行、列、值)

• 按区间汇总统计（WPS右键-组合）常用于销售数据、薪资体系是否合理、生产型环境报表中

• 日期、数值组合前需保证其区间非文本格式、无空值（如何快速把日期调整成非文本格式详见第二讲）

• 数据透视表中汇总多列数据

• 数据透视表里面写入想要的公式（WPS：分析→字段、项目→计算字段）比如计算利润率
• 对于自行插入数据表的公式结果不想其显示错误提示可这样设置：数据透视表选项-布局与选项-打钩对于错误值……

• 批量建立工作表（WPS：分析→选项→显示报表筛选页）
• 批量处理多个工作表（第一个工作表按住shift再点最后一个工作表）

第七讲（多看几遍，扩展自己的制表思路）

• 连字符&对于数值和文本都可用（比如张和三组合成张三）

• 比较运算符（最后结果为ture和flash）
• 运用：比如本地考生加30分的例子

• 绝对引用固定的单元格：快捷键F4或手动添加美元符号也行

• 绝对引用固定的单元格的运用：比如九九乘法表（竖向锁列，横向锁行）

• 用函数做排名：rank的运用（比如给学生成绩做排名）

• 求和（不连续区域或者手工选区不方便）的方法：选择要求和的区域→定位空值→点击自动求和工具中的求和

第八讲（多看几遍，扩展自己的制表思路）

• 函数IF简单运用（如果某某人是女的就称呼为女士，否则称为先生）

• 函数IF嵌套运用（比如理工、文科、财经的例子）
• 文本的话要加英文引号，数值运算的话不用

• 函数IF数值区间的运用（比如600~800分显示第一批的例子）适用于只有三种区间

• 三个区间以上的函数运用（尽量回避IF函数嵌套）

• 用IF函数处理运算错误iserror

• and函数代表且；or函数代表或
• and、or互相嵌套

第九讲（多看几遍，重在理解）

• count函数：计数
• countif函数：条件区域+条件

• countif函数的运用，比如统计成绩≥60分的人数，条件为">=60"

• 运用：比如多个银行卡号计算相同的数量（超过15位数字的解决办法&“*”）
• 通配符*，不计位数，&连字符(可见第七讲)

• 运用：学生是否体检（if函数嵌套countif）

• 开始-条件格式的运用：给未做体检的学生标注颜色

• 运用：将重复银行卡卡标记为红色背景（超15位），利用开始-条件格式

• 设置禁止输入重复数据（即可以允许输入非重复数据）有点绕主要靠理解

• countifs：多个条件计数

第十讲（多看几遍，重在理解）

• sumif函数（条件区域，条件，求和区域）

• 运用：银行卡金额统计（超过15位字符）

• sumif容错力强，可简写

• 在多列中使用sunif函数（列不需要绝对引用，部分区域的话需要绝对引用）

• 简写参数时应注意的地方（第三参数要与第一参数对齐）

• 使用辅助列处理多条件sumif（也可以用sumifs）

• 运用：出库单的各类产品总数量不能大于总库存

第十一讲（多看几遍，重在理解）

• VLOOKUP函数基本用法(查找值，数据表，列序数，匹配条件)

• Vlookup函数会从选区最左侧开始找，所以应视需求而定选择数据表

• 运用于跨表查找（注意！选区完成后要先写逗号，否则会出错）

• 通配符&"*"运用：比如简写的公司名称查找出正确的地址(不是模糊匹配)

• 模糊匹配和精确匹配区别，记住模糊匹配的例子就行（男的择偶匹配女性年龄、销售人员等级划分的提成比例）0是精确匹配，1是模糊匹配

• 公式中处理数值转为文本：单元格+&“”

• 公式中处理文本转为数值：单元格*1或者+0（只要有对单元格进行运算就都可行）

• 运用：利用if函数、isna函数和Vlookup函数，处理数值没找到转为文本查找

• Hlookup与Vlookup函数，仅方向上有区别

• 运用：计算个税（）

第十二讲（多看几遍，重在理解）

• match函数：查找具体第几行数第几行列数；index函数：引用

• index和match函数嵌套=vlookup函数（index和match嵌套是不分左右顺序查询）

• 第七讲的九九乘法表（竖向锁列，横向锁行）

• 运用：要查找的数据与原表结构一致
• column函数：列号

• 运用：要查找的数据与原表结构不一致
• 利用vlookup、match函数

• 插入图片，按住Alt键会自动贴单元格边框
• 运用：引用照片（WPS中只要把照片嵌入单元格后，用vlookup、index和match函数可实现）

第十三讲（此讲WPS无法操作暂时跳过）

• WPS中无法打开数据源，暂时无法操作

第十四讲

• 时间、数值之间的关系及相互转化（Excel里的1=24h）

• 比如：已知开始日期、间隔月份求结束日期（date、year、month、day）

• 比如：已知开始日期，求本月最后一天

• 比如：已知开始日期，求本月天数

• 比如：已知入职日期、离职日期，求工龄（运用datedif函数）

• 比如：已知日期，求第几周(运weeknum函数)

• 比如：已知日期，求周几(运用weekday函数)

• 比如：已知日期，求周几(运用text函数)
• 可详见第二讲——单元格格式-自定义类型解读及运用（主要讲日期）

• 比如：已知假日期，转化为周几(运用text函数)