导图社区 金山办公技能认证---KOS表格应用大赛笔记
这是一篇关于金山办公技能认证---KOS表格应用大赛笔记的思维导图,办公软件操作高手们快速报名,扩张你的影响力。
编辑于2022-03-29 11:38:11保护与共享 工作薄和工作表
保护工作薄 和工作表
保护表格中的数据 选中列-右键-设置单元格格式
锁定
取消即可编辑
保护工作薄
隐藏
公式不让被看到
保护工作薄
位置:审阅-保护工作表-输入密码-确定-撤销工作表保护
给用户设置不同的权限搭配
保护工作表-勾上“设置单元格格式”
先做好筛选-审阅-保护工作表 -勾上“筛选”
不允许修改列,可以改行-审阅-保护工作表-列的都不选,行的都选上
给不同的客户设置不同的区域编辑密码
选中区域,设标题,设置区域密码 创建可编辑区域并设置编辑密码
位置:审阅-允许用户编辑区域
保护工作表
位置:审阅-保护工作表-设置管理员密码
管理员若修改文档-撤销工作表保护即可
保护工作薄-保护所有的子表
位置:审阅-保护工作薄
给工作表设置打开密码
位置:文件-文档加密-密码加密
打开密码
删除密码-原来的位置 -删除
编辑密码
使用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创建动态图表
将公式定义为名称(位置:公式-名称管理器-新建)
插入空白图表,手工选择数据,将值和类别名称指定为定义好的名称
注意事项:必须将公式定义为名称才能在图表中使用