导图社区 金山办公技能认证---KOS表格应用大赛笔记
- 96
- 2
- 0
- 举报
金山办公技能认证---KOS表格应用大赛笔记
这是一篇关于金山办公技能认证---KOS表格应用大赛笔记的思维导图，办公软件操作高手们快速报名，扩张你的影响力。
编辑于2022-03-29 11:38:11- Excel学习
- 相似推荐
- 大纲
保护与共享 工作薄和工作表
保护工作薄 和工作表
保护表格中的数据 选中列-右键-设置单元格格式
锁定
取消即可编辑
保护工作薄
隐藏
公式不让被看到
保护工作薄
位置：审阅-保护工作表-输入密码-确定-撤销工作表保护
给用户设置不同的权限搭配
保护工作表-勾上“设置单元格格式”
先做好筛选-审阅-保护工作表 -勾上“筛选”
不允许修改列，可以改行-审阅-保护工作表-列的都不选，行的都选上
给不同的客户设置不同的区域编辑密码
选中区域，设标题，设置区域密码 创建可编辑区域并设置编辑密码
位置：审阅-允许用户编辑区域
保护工作表
位置：审阅-保护工作表-设置管理员密码
管理员若修改文档-撤销工作表保护即可
保护工作薄-保护所有的子表
位置：审阅-保护工作薄
给工作表设置打开密码
位置：文件-文档加密-密码加密
打开密码
删除密码-原来的位置 -删除
编辑密码
使用WPS账号加密文件
位置：文件-文档加密-文档权限-开启-确认账号-再打开有个小盾牌
修订与共享 工作薄和工作表
共享工作薄
多人同时编辑文档
位置：审阅-共享工作薄-允许多人编辑-确定-触发保存-是
保存按钮-保存+刷新
绿色按钮-说明有人动了某个位置-标记最新数据
两个人同时改-A保存后，B保存时会发生询问-B做选择
追溯哪些人查看了数据
位置：审阅-修订 -突出显示修订
在 屏幕上显示
我自己改了我知道
在新工作表 +时间字段
会显示所有的 修改记录
历史记录不能删除
删除需把选√去掉
编辑与格式化 数据的高级应用
管理单元格 的内容与格式
设置自定义数字格式
位置：数字-自定义-代码
编码规则
0叫占位符，帮数字占着位置
0“箱”-英文半角双引号
可以在原有格式上修改
看原来的， 点击看一下自定义的展示
8个常用符号
#-数字占位符-#.##
0-数字强制占位符号-0.00
？-用空格占位，常用语对齐-#.？？？
示例
0.00-超过2位保留2位小数，不足两位补齐
0.##-不足两位就不补足
#.##-0.5-就不显示前面的0了-结果是“.5”
0.？？-还是保留2位小数，不足2位的用空格占位，小数点对齐
@-文本占位符
“GH”@
，-千位分隔符
#，##0.00
；-正数；负数；零；文本
0.00“剩余”；-0.00“短缺”；“-”；@
有些数据暂时不显示-选中所有数据-设置单元格格式-；；；（三个分号）
*-重复最后一个字符，填满单元格
*0.00
！-重复最后一个字符，重复一次
！~0.00
~重复一次
*~0.00
~填满空格
* 0.00
人民币左对齐（*空格0.00）
使用样式可以把大量的同类数据进行统一管理
位置：开始 -单元格格式-新建单元格样式
快速统一同类数据格式
位置：开始 -单元格格式-在待修改的样式上点击右键-选择修改
数据格式不保护，可让他人修改
位置：开始-单元格格式-样式-修改-格式-保护-取消锁定
获取外部数据
导入TXT/CSV格式数据
位置：数据选项卡-导入数据-导入数据-选择待导入的文件后使用分列法
文本转换，多点击右侧的，看看是否可正常转换（无乱码）
UTF8/简体中文G132312
可更新---右键-刷新-更改数据源-便可更新
导入网站数据（先复制网址）
位置：数据选项卡 -导入数据-自网站链接-输入域名后导入数据
无用的可隐藏
右键刷新可更新为最新的
链接与定位数据
定位工具用于选中某些特殊属性的单元格
最热门的五个定位方式
文本
位置：开始-查找-定位-数据-公式+常量+“文本”
可以过滤出来数值中的文本
解决：数据-单元格-文本转数值
错误
位置：开始-查找-定位-数据-公式/错位置
可见单元格
位置：开始-查找-定位-可见单元格-Ctrl+C-Ctrl+V
定位图片-对象
位置：开始-查找-定位-对象-定位-delete
空白
Ctrl+enter
选中区域-写公式 -快捷键
在替换中使用通配符
使用通配符用来在查找中使用模糊匹配
*表示任意多个字符
替换
选中列-确定范围-销售*部vs销售部-销售服务部/销售一部都可替换
？表示任意1个字符
替换
选中列-确定范围-销售？部vs销售部-销售服务部“服务”两个字符替换不了
单元格匹配
A03*VS过期编号/
A03？？？+单元格匹配对钩VS过期编号
~将*和？转化为文本字符
替换对象就有*/？
要 将单元格内容为 “A03*”替换为‘错误编号“---A03~*VS错误编号
使用照相机工具的目的
保护数据
数据动态改变
双击可以跳转
原位置 插入的图标也可显示
一项重排版的工具
拖拽按住shift， 可以等比例变化
按住Ctrl可以图片对齐
位置 ：插入-照相机-鼠标变十字-选择的位置单击
宏的基本操作
开发工具
位置 ：文件-选项-自定义功能区-开发工具-确定
高度 重复性劳动-宏-录下来-重新在其他表中执行
如何录制宏
位置：开发工具-录制新宏-确定-操作想达到目的的各项操作-停止录制
如何使用已有宏
位置：开发工具-JS宏-选中所需的宏-运行
找到宏代码
位置 ：开发工具-WPS宏编辑器
设置宏安全性
位置 ：开发工具-宏安全性-建议“中/低”
公式与函数 的高级应用
公式的高级应用
1使用函数排错工具
公式排错
①公式求值---从左往右---求值---修改为正确的
②选中公式，按F9+FN-ESC
写了公式，导致计算卡顿，插入删除都很缓慢---改为了手动计算
文件-选项-重新计算-自动重算/手动重算
输入公式后，数据计算结果错误---可能是取消了自动计算-公式-重算工作薄
F9+FN-在公式中强制运算-在书写公式的过程中帮我们快速找到公式出错的地方
2公式中的循环运用
循环引用
死循环的处理---固定循环
迭代计算：位置-文件-选项-重新计算-迭代计算+次数
循环引用
位置：公式-循环引用-循环引用
示例：输入数据A2，显示时间B2
开启迭代计算，=if（A2=“”，“”，if（B2<>""，B2，NOW（）））
3公式中的基础运算符
算术运算符-数值
+ - * / % 2^6
文本运算符-文本
&
比较运算符-逻辑值-TRUE/FALSE
= > < >= <= <>
引用运算符-引用
，逗号-联合运算符，多个引用合并成一个引用
=sum（A1:A3，A4:A9）
:冒号-区域运算符，引用两个单元格之间的全部区域
=sum（A1:A3）
空格-交叉运算符，引用两个区域的共有区域
=sum（A1:A9 D5:F6）
4数组公式的计算原型
区域*区域
选中 积列区域=A2:A8*B2:B8，按住Ctrl+shift+回车
{}-数组-手工写无效
不能单个删除结果
利用数组公式计算加权平均值
进货量*进货价---求商品批次均值进价
=sum（A2:A7*B2:B7）/sum（B2:B7），按住ctrl+shift+回车
5利用公式标记数字
通过条件格式选出答案-通过甲选出乙
选中列-单元格格式-新建格式规则-使用公式-写公式+格式
一个月值班大于3次以上的标红=countif（B:B,B2）>3，格式，红色
当条件较为复杂的时候可以使用公式定义条件格式
位置：条件格式-新建规则-使用公式确定单元格格式的单元格
注意事项：设置条件格式时需要针对当前编辑单元格书写公式，注意相对引用和绝对引用
6利用公式制定输入规则
数据有效性
位置：数据-有效性-有效性-允许-自定义-输入公式
无法放置复制粘贴，只能防止手写
注意事项：设置允许条件时需要针对当前编辑单元格书写公式
7名称与公式
给区域设名称
位置 ：公式-名称管理器 -新建-运货费-引用区域
求和可以直接引用名称
给常量设名称
位置：公式-名称管理器 -新建-比例 -引用区域改为0.8（常量）
给公式设名称
示例：工资=收入 -扣款-税费，可以对收入、扣款及税费在引用区域内 设置计算公式引用
定义名称的好处
增加公式的可读性
批量修改公式
比如测算的时候，需要修改常量80%为90%
8多行、多列填充公式
相对引用
A2
绝对引用
$A$2
混合引用
$A2/A$2
示例：99乘法表 ---=$A2*B$1
混合引用可以区分行、列两个方向上的填充问题；可以用在既要横向填充又要纵向填充的公式中
VLOOKUP返回多列结果
=vlookup($A2,$B:$C，column（）-7,0)
条件格式
示例：数量大于30的整行都标红
选中整个区域-条件格式-使用公式-输入公式=$D2>30
使用数学与统计函数
1条件求和与条件计数
条件求和
sumif（条件区域，条件，【求和 区域】）
多条件求和
sumifs（求和区域，条件区域1，条件1，...）
条件计数
countif（条件区域，条件）
多条件计数
countis（条件区域1，条件1，...）
示例：根据产品和部门统计销量
2其他条件统计函数
多条件求平均值
averageifs（求均值区域，条件区域1，条件1，条件区域2，条件2，...）
多条件求最大值
maxifs（求最大值区域，条件区域1，条件1，条件区域2，条件2，
多条件求最小值
minifs（求最小值区域，条件区域1，条件1，条件区域2，条件2，
示例：去掉最大的三个值和最小的三个值求平均值
small（数组，K）
第K小
large（数组，K）
第K大
注意连字符&的使用">"&min（A:A）
不要把别的函数包在双引号里
3统计筛选后的数据
使用多种统计方式汇总筛选后的数据
subtotal（函数序号，统计区域）
1-11
针对于筛选
101-111
针对筛选和隐藏
sum（）
筛选，隐藏都不变
subtotal（9-sum）
计算筛选后的结果
subtotal（109-sum）
统计筛选或隐藏之后的结果
示例：创建随筛选而变化的统计报表
4使用函数进行舍入运算
四舍五入
round（数值，小数位数 ）
计算工资
支持小数位数，写负数
直接进位
roundup（数值，小数位数）
计算邮费
直接舍去
rounddown（数值，小数位数）
计算工龄
设置单元格格式只是表面改了样式，会引起后期计算误差
示例：排查因小数位数导致的薪资表统计误差
数据不规范-先round四舍五入-再统计-舍入要在统计前，否则会引起一分钱的误差
5“随机选取”相关问题
得到0-1之间随机数（小数点后15位）
rand（）
得到指定区间内的随机数（取整数）
randbetween（最小整数，最大整数 ）
示例：随机抽检中避免出现重复（随机且不重复）
6数组公式多条件求和
数组求和
sumproduct（数组1，数组2，...）
sum（数组1，数组2，...），全选公式+Ctrl+shift+回车键
7其他数学函数
求绝对值
abs（数值）
求余数
mod（数值，除数）
求中位值
median（数值）
奇数个数字，取中间那个数
偶数个数字，取中间两个的平均值
求众数
mode（数值）
最先出现的那个最多的数字
示例：创建间隔填充色的条件格式效果
选中区域-条件格式-新建规则-使用格式-=mod（row（）,2）=1，格式背景填充 为蓝色
行号
row（）
列号
column（）
使用逻辑判断函数
1使用函数处理条件判断
处理条件判断
if（如果，则，否则）
处理多条件判断
if（如果，则，if（如果，则，if（如果，则，否则）））
区间为逐层减掉的关系
注意事项：处理连续数值区间判断，区间为逐层递减的关系
示例：根据考生分数判断录取情况
2IF函数与逻辑运算
表示“且”的关系
AND（逻辑值1，逻辑值2，...）
=if（and（countif(B2:D2,">=85"）=3,E2="优秀"），“优秀”，“”）
表示“或”的关系
OR（逻辑值1，逻辑值2，...）
=if（or（countif(B2:D2,">=60"）<3,E2="差"），“需关注”，“”）
示例：根据年龄和性别判断是否满足结婚年龄
男>=22; 女>=20
=if（or（and（B2="男",C2>=22），and（B2="女",C2>=20）），“是”，“否”）
3处理公式中的运算错误
如果计算出错则...
iferror（值，错误值）
0不能做分母就会出现#DIV/0!
=iferror（C2/D2,0）
错误值结果都为 0了
如果找不到则...
ifna（值，错误值）
vlookup()显示#N/A错误
示例：以两张工作表为数据源使用vlookup( )函数查询数据
=ifna（vlookup(A2,部门1！A:D，4,0)，vlookup(vlookup(A2,部门2！A:D，4,0)）
4使用IFS函数
处理多条件判断
ifs（测试条件1，真值1，测试条件2，真值2，...）
示例：根据业务员评级求对应奖金
理解即可，非费用不可的公式，主推switch公式
5使用SWITCH函数 switch---转换
根据匹配项返回对应结果
switch（表达式，值1，对应结果1，值2，对应结果2，...）
switch（F5，“A级”，10000，“B级”，9000，“C级”，8000）
VLOOKUP函数处理多重条件，使用F9将引用转换为常量
=vlookup(F5，A3:B5,2,0)，选中A3:B5，按住F9，变为了数组
使用文本与日期函数
1使用函数截取文字
按字符从左截取
left（字符串，截取的字符个数）
按字符从右截取
right（字符串，截取的字符个数）
按字符从中间截取
mid（字符串，开始的位置，截取的字符个数）
计算字符数量
len（字符串）
按字节从左截取
leftb（字符串，截取的字节个数）
按字节从左截取
rightb（字符串，截取的字节个数）
按字节从中间截取
midb（字符串，开始的位置，截图的字节个数）
计算字节数量
lenb（字符串）
使用vlookup()查询是文本格式，要*1，转变为数值格式
2查找文字的位置
查找字符区分大小写
find（要查找的字符串，被查找的字符串，【开始位置】）
不支持通配符
查找字符不分大小写
search（要查找的字符串，被查找的字符串，【开始位置】）
支持通配符
示例：提取邮箱用户名
=left（A2,find（“@“，A2））-1）
示例：提取邮箱域名
=mid（A2,find（“@”，A2）+1）,500)
3规范系统导出数据
字符转大写
upper（字符串）
字符转小写
lower（字符串）
首字母大写---proper（字符串）
去掉所有不合理的空格
trim（字符串）
处理英文公司名的空格，俩单词间1个空格即可，开头、结尾都不 需要有空格
清理非打印字符
clean（字符串）
银行，大型金融证券企业，系统中导出的数据，兼容性原因
4处理数值与文本
文本转数值
value（字符串）
=A2*1
数值转文本
text（数值，数值格式）
3000转为大写金额---=text（3000，“单元格格式（自定义格式）复制过来即可“）
示例：8位数字转为正确的日期格式
日期本质是数字
text（日期，“yyyy/m/d”）
20200202，显示的是两千多万的数字---日期是4万多的数字---text（20200202,”0000-00-00“）*1+设置单元格格式
5使用函数进行替换
针对固定位置替换
replace（原字符，开始位置，字符数量，新字符）
针对内容替换
substitute（待处理字符，原字符，新字符，【替换序号】）
2021.3.2---substitute（A2，“.”，“-”）*1+设置单元格格式
注意事项：实际工作中常用“替换后比较字符数量变化”的方式确认是否存在某内容
手机号隐藏中间5位---substitute（A9,mid（A9，4,5），“*****”）
手机号隐藏中间5位 1399999902
substitute（A9,mid（A9，4,5），“*****”）
13*******02
replace（A9,4,5,"*****"）
139*****902
示例：一个单元格内包含多个员工姓名， 如何统计员工人数
一个单元格中罗列了一堆人名：人名1，人名2，...=len（C2）-len（substitute（C2，“，”，“”））*1
6文本连接函数
带分隔符连接文本
textjoin（分隔符，忽略空白单元格，字符串1，字符串2，...）
连接多个文本
concat（字符串1，字符串2，...）
concat（C2:L2）
弊端，直接相连，无规则，后期无法再用
示例：多列数据合并到一列，用于导入到公司内部系统
一个单元格一个人名，合并多个=textjoin（“，”，TRUE，A2:L2）
true-没有就算了
false-保留一个 分隔符
有些统计表格，为保证后期分列，建议选false/不忽略空格---后期可用分隔符分列
7格式错误表格中统计数据
统计学生分数
数学100/语文94...
总分=sumproduct（mid（B2：E2，3,100）*1）
统计员工考勤
事3/病2
事假3天/病假2天
事假=sumproduct（iferror（substitute（B2:AF2,"事",""）*1,0)）
8计算结款日期
计算-年
year（日期）
计算-月
month（日期）
计算-日
day（日期）
根据年、月、日三个数字生成日期
date（年，月，日）
=date（year（A2），month（A2）+3，day（A2））*1
9使用函数计算工龄
根据开始和结束日期 计算间隔
datedif（开始日期，结束日期，“比较单位”）
间隔---Y年，M月，D日，YM忽略年份算月，YD忽略年算天数，MD忽略年月算天数
10处理星期相关问题
计算某日期是星期几
weekday（日期，2）
计算某日期是全年第几周
weeknum（日期，一周从哪天算起）
1-周日
2-周一
注意事项：一周起始于哪一天将会影响某日期是全年第几周的统计结果
示例：横排日期中，把周六，周日改为红色填充
条件格式-使用公式-weekday（b$1,2）>5-格式调为红色
11处理工作日相关问题
计算某日期N个工作日后是哪一天
workday（开始日期，间隔工作日，（节假日列表））
计算两个日期间隔多少个工作日
networkday（开始日期，结束日期，（节假日列表））
注意事项：节假日列表需要单独列出每一个节假日日期，不能写成日期范围。
20210910-20210925间隔几个工作日
networkdays（A2,B2,G:G）+countifs（H:H,">="&A2，H:H“<=”&B2）
20210910间隔10个工作日后的日期？
workday（A9,B9,G:G）
12“到期自动提醒”
获取当前系统日期
today（）
获取当前系统时间
now（）
理解日期和时间的本质
示例：今天和未来10天标红-条件格式-介于-today（）/today（）+10，自定义格式选红色
使用查找与引用函数
1使用VLOOKUP函数查找 数据
使用关键字垂直查询信息
vlookup（查找值，查找区域，列序数，查找模式）
使用关键字水平查询信息
hlookup（查找值，查找区域（注意锁定 ），行序数，查找模式）
注意事项：理解近似匹配，原则为匹配小于或等于目标值的最大值-<=目标值
近似匹配常用于解决数值区间匹配的问题（如根据金额计算个税、提成等）
精确匹配0，近似匹配1
根据左边取右边
查找，查询
2使用MATCH与INDEX函数组合
查找关键字的位置
match（查找值，查找区域，查找模式）-负责找
根据位置取回数据
index（数据区域，行序数，列序数）-负责取
示例：在数据源中根据右侧关键字匹配左侧信息
index（B:B,match（G3,A:A,0））>=VLOOKUP( )
3VLOOKUP返回多列结果
函数嵌套公式
vlookup(查找值 ，查找区域，match（），查找模式)
index（数据区域，match（），match（））
示例：根据客户 ID分别查询公司地址、公司名等多项不连续信息
注意事项：注意公式中单元格的混合引用问题
4使用indirect函数跨表引用数据
将文本转换为引用
indirect（单元格，【引用样式】）
注意事项
理解引用与文本的区别
理解indirect函数跨表引用的原理
使用简单的财务函数
速算每月还款金额
基于固定利率和等额分期付款方式，返回贷款的每期付款额
pmt（年利率/12，总支付期数，现值（欠银行的钱），【终值】，【是否期初支付】）
基于固定利率和等额分期付款方式，返回某期投资的总期数
nper（利率每期还款额，现值，【终值】，【是否期初支付】）
返回投资的现值。现值为一系列未来付款的当期值的累计和
pv（利率，总支付期数，定期支付额，【终值】，【是否期初支付】）
基于固定利率和等额分期付款方式，返回某期投资的未来值
fv（利率，总支付期数，定期支付额，【现值】，【是否期初支付】）
示例：按不同方式测算房贷还款相关数据
组织、汇总与分析 数据的高级应用
在智能表中管理和分析数据
1自定义排序和高级筛选
自定义排序
位置：文件-选项-自定义序列
高级筛选
注意：条件区域必须带表头
逻辑：同行条件表示“且”，不同行条件表示“或”
2使用表格工具
位置：插入-表格
优点
自动显示统计结果
可以使用切片器筛选结果
具有自动扩展的特性
示例：利用切片器优化统计表
数据透视表的高级应用
1数据透视表值字段设置
透视表的汇总方式
求和
计数等
透视表的值显示方式
列汇总百分比
父级百分比
差异百分比等
示例：分析家庭支出数据，同时显示多种统计维度
2筛选排名前三的数据
透视表中的排序
按数值大小排序
按自定义列表次序排序
透视表中的筛选
需要在待筛选的分类字段上点击筛选按钮，并选择以值为依据筛选
示例：筛选金额最高的三项支出
3快速按日期统计数据
按日期组合
在分类项中点击右键，选择组合，按年、季度、月、日后点击组合按钮
按数值组合
在分类项中点击右键，选择组合，设置范围和步长值后点击组合按钮
按文本组合
选择待组合数据后点击右键，选择组合
4数据透视表与公式
创建计算字段
处理“字段”与“字段”之间的运算
创建计算项
处理“项目”与“项目”之间的运算
示例：在商品销售数据中计算各类商品的利润率
5数据透视表的显示样式
分类汇总
显示或隐藏透视表分类字段下的总计
总计
显示或隐藏最外侧的总计项目
报表布局
以压缩、大纲、表格三个样式展示透视表
空行
是否再项目后插入空行
6快速切换统计数据
认识报表筛选字段
使用切片器筛选数据透视表、数据透视图中的数据
使用切片器同步控制多个透视表数据
使用高级数据分析工具
1根据客户预算制定报价
位置：数据-模拟分析-单变量求解
示例：根据客户预算制定报价方案
注意：必须是公式得到的结果
2找到未回款的交易记录
位置：数据-模拟分析-规划求解
示例：根据金额总计和发票金额找到未回款的发票
创建和格式化高级图表
创建个性化图表
1展示差异较大的数据
组合图表是指在同一个图标中使用两种或以上图表类型的图表
插入方式：图表类型中选择组合型图表
注意事项：理解主/次坐标轴的概念及用途
2在堆积柱形图上显示总计
学习堆积柱形图的使用方式
在堆积柱形图上显示总计数据
原表中要增加总计数据计算列
簇状柱形图标签可以在外部（堆积-上中下）
次坐标轴的使用-选中区域-右键-系列-次坐标
示例：利用图表展示两年所有月份的数据
簇状柱形图-各分类+2019/2020分别显示
堆积柱形图-各分类2019/2020堆放在一起
高级工具-添加工具-数据标签
3用堆积条形图制作甘特图
以开始日期+任务周期创建堆积条形图
将开始日期设置为透明
按项目开始日期和结束日期设置坐标轴范围
要理解日期是数据的概念
4在折线图中绘制直线
在折线图任意位置画线，强调数据趋势
在现有图表中增加数据系列
位置：右击图表-选择数据-添加系列
设置空数据直接连接数据店
位置：右击图表-选择数据-高级设置-用直线连接数点
5折线图与面积图
折线图
折线图
堆积图
面积图
示例：利用较为简单的数据创建图表并美化
6XY散点图展示数据分布
折线图---X轴是分类，Y轴是数据
XY散点图---X/Y都是数值轴
示例：男女、身高、年龄
先建空图表，再二次选择数据源
7一键新建图表
将图表保存为模板
图表-右键-保存模板-不要修改路径chart-命名
利用模板创建图表
迁移图表模板
图表-右键-模板-管理模板-拷贝走
创建交互图表
1使用窗体控件控制数据区域
使用offset函数引用动态范围
offset（参照区域，行号，列号，【高度】、【宽度】）
使用滚动条控件
插入-控件-滚动条
需要将滚动条链接到单元格，右击滚动条-控制-单元格链接
注意事项：理解从控件到单元格再到offset结果区域的变化过程
2创建动态图表
将公式定义为名称（位置：公式-名称管理器-新建）
插入空白图表，手工选择数据，将值和类别名称指定为定义好的名称
注意事项：必须将公式定义为名称才能在图表中使用