WPS函数和公式经典案例.xlsx

1、WPSWPS函函数数和和公公式式应应用用案案例例编编号号内内容容所所用用函函数数和和公公式式案例1从身份证计算出岁数、生日、性别mid datedif案例2提取QQ号码mid left案例3反向查找、双向查找index match iferror案例4制作多级菜单indirect案例5一对多查询index small if row案例6datedif函数技巧应用（计算两个日期之间的天数）datedif案例7提取不重复数据index match countif案例8抽奖小程序index rank rand案例9提取一段文字中的日期mid find案例10提取文件夹中所有文件的名称files in

2、dex if row案例11去除单元格中的空格substitute trim案例12链接函数“hyperlink”用法大全hyperlink案例13实现可精确查询、可模糊查询index small if find iserror案例14用indirect工作表之间的动态跳转indirect案例15制作动态响应图表if offset案例16全表查询，输入一关键字都能查出符合条件的所有信息。concat find vlookup match案例17输入日期后不随时间变化if today案例18获取工作簿地址、名称cell mid find案例19逆向查找和多条件查找lookup案例20查找最后一个

3、中文字符、数字lookup案例21获取文件下的文件名称dir案例22万年历date match row column案例23经过审核的数据不能更改len案例24条件格式的典型应用and datedif案例25text 函数应用详解text案例26自动计算答题成绩if or and iserror isnumber案例27轻松搞定各类排名rank countifs large案例28到期提醒datedif案例29全自动随机座次表rand vlookup rank案例30全自动排序，选择升序、降序自动变化index match large small案例31考勤表date if text coun

4、tif案例32合并单元格实现连续编号max案例33cell函数的基本用法cell案例34查找字符串中某个字符走后出现的位置substitute mid find案例35提取中英文混杂字符串中的中文和英文concat mid lenb row案例36在多个数值中提取与已知数相邻的两个数frequency lookup if案例37利用复选框和公式创建自动判断问卷textjion iferror char code案例38自动判断不同职级的薪资区间max min if案例39提取指定字符串之间的多个字符串trim mid substitute案例40分离字符串（汉字、字母、数字）mid.案例41列

5、出不重复的数据链链接接地地址址点这里查看“从身份证计算出岁数、生日、性别”点这里查看“提取QQ号码”点这里查看“反向查找、双向查找”点这里查看“制作多级菜单”点这里查看“一对多查询”点这里查看“datedif函数技巧应用（计算两个日期之间的天数）”点这里查看“提取不重复数据”点这里查看“抽奖小程序”点这里查看“提取一段文字中的日期”点这里查看“提取文件夹中所有文件的名称”点这里查看“去除单元格中的空格”点这里查看“链接函数“hyperlink”用法大全”点这里查看“实现可精确查询、可模糊查询”点这里查看“用indirect工作表之间的动态跳转”点这里查看“制作动态响应图表”点这里查看“全表查询

6、，输入一关键字都能查出符合条件的所有信息。”点这里查看“输入日期后不随时间变化”点这里查看“获取工作簿地址、名称”点这里查看“逆向查找和多条件查找”点这里查看“查找最后一个中文字符、数字”点这里查看“获取文件下的文件名称”点这里查看“万年历”点这里查看“经过审核的数据不能更改”点这里查看“条件格式的典型应用”WPSWPS函函数数和和公公式式应应用用案案例例点这里查看“text 函数应用详解”点这里查看“自动计算答题成绩”点这里查看“轻松搞定各类排名”点这里查看“到期提醒”点这里查看“全自动随机座次表”点这里查看“全自动排序，选择升序、降序自动变化”点这里查看“考勤表”点这里查看“合并单元格实现

7、连续编号”点这里查看“cell函数的基本用法”点这里查看“查找字符串中某个字符走后出现的位置”点这里查看“提取中英文混杂字符串中的中文和英文”点这里查看“在多个数值中提取与已知数相邻的两个数”点这里查看“利用复选框和公式创建自动判断问卷”点这里查看“自动判断不同职级的薪资区间”点这里查看“提取指定字符串之间的多个字符串”点这里查看“分离字符串（汉字、字母、数字）”点这里查看“列出不重复的数据”样表序序号号岗岗位位姓姓名名身身份份证证年年龄龄生生日日性性别别1船长韩祝峰132926197505075418471975年05月07日男2大副张福海132928198206063991401982年0

8、6月06日男3大副白桐林132931198009151210421980年09月15日男4水手长张忠群130983198212054218391982年12月05日男5水手蔡义亮130924198204173532401982年04月17日男6水手刘森130224197912048713421979年12月04日男7轮机长杨茂学132931198202150615401982年02月15日男返回返回提取QQ号码序号邮箱在这里提取QQ号8790765455返回WPSWPS 函函数数与与公公式式技技巧巧例例1 1：反反向向查查找找编号产品名称 入库单价A001A10A002B20A003C30A0

9、04D40A005E50A006F60产品名称编号EA005例例2 2：双双向向查查找找费用项目1月2月3月4月5月6月办公费60007000800090001000011000绿化费5000600070008000900010000修车费400050006000700080009000标识费300040005000600070008000招待费200030004000500060007000月份费用项目 费用额6月标识费8000例例2 2：双双向向查查找找返回WPSWPS 函函数数与与公公式式技技巧巧(制制作作多多级级菜菜单单)海务管理科船机管理科 调度管理科科室姓名张三李四王五海务管理科张

10、三张浩杰岳小林张宁船机管理科李四白洪义李文钊李宝生调度管理科李宝生庞锋船机管理科岳小林选中B3-D7区域选中F4-F7选中G4返回123在来源 里填上“indirect（$F4)”然后下拉注：名称定义可以跨工作表使用WPSWPS 函函数数与与公公式式技技巧巧(制制作作多多级级菜菜单单)张三海务管理科输入内容：海海务务管管理理科科张浩杰海务管理科张三白洪义海务管理科张浩杰庞锋海务管理科白洪义李四船机管理科庞锋岳小林船机管理科李文钊船机管理科王五调度管理科张宁调度管理科李宝生调度管理科解析1 1、条条件件模模块块：IF(C$3:C$12=F$3,ROW(C$3:C$12),48)表示如果A2:A8

11、的姓名等于F3的“海务管理科”，则返回C3:C12的行号，否则返回48，即65536。IF（如果，则，否则这语言不难读吧条件模块经过数组运算，因为C3-C6满足条件，所以返回的是行号数组1;2;3;4;65536;65536;65536;65536;65536;655362 2、排排序序模模块块：SMALL(IF,ROW(1:1)公式的第1行，ROW(1:1)返回1，在第2行返回2因此，利用SMALL+ROW可以将条件模块返回的行号数组从小到大依次排序得出。当然，此例中SMALL(1;2;3;4;65536;65536;65536;65536;65536;65536,1)得到的是1，第2小的是

12、2，第3小的是3，第4小的是4，第5小的及以后都是65536。3 3、引引用用模模块块：INDEX(引用列，SMALL得到的行号)=INDEX(B:B,2)引用B2，=INDEX(B:B,65536)引用B65536返回4 4、容容错错模模块块：&当公式到了第4行，3个满足条件的记录都已经找出来，此时公式是=INDEX(B:B,65536)&“”因为Excel2003的最大行数是65536行，而在这一行中，一般不会有人输入数据，是空单元格。因此，利用空单元格与空文本合并返回空文本的特性，将超出结果数量的部分不显示出来。WPSWPS 函函数数与与公公式式技技巧巧(Datedif(Datedif函

13、函数数技技巧巧)DATEDIF函数的用法及参数含义Datedif函数的参数含义unit参数返回值的意义y两个时间段之间的整年数m两个时间段之间的整月数d两个时间段之间的天数md两个时间段之间的天数的差，忽略日期中的年和月ym两个时间段之间的月数的差，忽略日期中的年和日yd两个时间段之间的天数的差，忽略日期中的年和月计算两个日期之间的整年数start_date2007/5/23end_date2016/5/24时间差9 公式：DATEDIF(B27,B28,y)计算两个日期之间的整月数start_date2007/5/23end_date2016/5/24时间差108 公式：DATEDIF(B3

14、3,B34,m)计算两个日期之间的整天数start_date2007/5/23返回end_date2016/5/24时间差3289 公式：DATEDIF(B33,B34,m)计算两个日期之间的整天数，忽略年月start_date2007/1/13end_date2016/5/24时间差11 公式：DATEDIF(B45,B46,md)根据生日计算年龄姓名生日年龄张三1998/1/1324张小丫2008/9/1014张浩杰1979/6/2743WPSWPS 函函数数与与公公式式技技巧巧(提提取取不不重重复复数数据据)序号姓名科目成绩不重复数据1张三数学56张三2李四数学88李四3李明数学78李明

15、4明明数学67明明5周正语文89周正6李四地理90#N/A7张三科学67#N/A 一、INDEXMATCH=ROW 法=INDEX(A:A,SMALL(IF(MATCH($A$2:$A$11,$A$2:$A$11,0)=ROW($A$2:$A$11)-1,ROW($2:$11),48),ROW(1:1)&分析：1、对 A 列中的每一项利用 MATCH 函数找出其在 A 列中首次出现的位置，返回相应的行 号。超出列表范围时将返回一个较大的行号，如 65536。2、利用 SMALL 函数对这些行号数据由小到大排序。3、利用 INDEX 函数返回与查找到的行号相应的 A 列的值。4、用 A 列的值与

16、”连接作为最终的值，当行号足够大时，对应的 A 列单元格为空单元格，空单元格与连接，依然是空单元格。最终结果，从 A 列筛选出的没有重复数据的单元格排在表格的上半部分，下半部分为空单 元格，正是我们想要的效果。二、COUNTIF 法=INDEX(A:A,MIN(IF(COUNTIF($C$1:C1,$A$2:$A$160)=0,ROW($A$2:$A$160),48)&(C2开始输入公式)这种解法看着有点怪异，一般人是想不到的，除非你是二般人。说它怪，怪在哪儿呢？怪就 怪在它的着眼点让人匪夷所思：1、要查找 A 列中不重复的数据，一般人都会首先考虑怎样处理 A 列中的数据，其它无关的 或空白区

17、域是不会上眼的。这种解法的独到之处就是首先在一空白列处查找 A 列中的数据，如果找不到就返回该数据所在的行号，否则返回一个较大的值 65536。2、用 MIN 函数据找到 A 列中的数据首次出现的行号。3、用 INDEX 函数在空白列处返回对应的 A 列中的值。三、FREQUENCY 法（仅适用于数值）=INDEX(A:A,SMALL(IF(FREQUENCY($A$2:$A$11,$A$2:$A$11),ROW($A$2:$A$11),48),ROW(1:1)&1、用 FREQUENCY 函数判断 A 列中的每一个数据是否在 A 列中出现一次，如果仅出现一 次就返回当前行号，否则返回一个较大

18、的数 65536。2、用 SMALL 函数对返回的行号从由小到大排序。3、用 INDEX 函数返回对应的 A 列中的值。四、COUNTIF 法=IF(COUNTIF($A$2:A2,A2)1,A2)1、利用函数 COUNTIF 统计 A 列中的数据是否大于 1，如果大于 1，返回空值，否则返回 当前值。2、对生成的数据排序以清除空白单元格。五、其它方法。公式一：=SUM(1/countif(A1:A10,A1:A10)按 Ctrl+Shift+回车键结束 公式：INDEX($C$4:$C$10,MATCH(0,COUNTIF($I$3:$I3,$C$4:$C$10),0)&返回其他方法公式二：

19、=COUNT(0/(MATCH(A1:A10,A1:A10)=ROW(1:10)按 Ctrl+Shift+回车键结束 公式三：=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10)公式四：=COUNT(0/(COUNTIF(A1:A10,A1:A10)=1)按 Ctrl+Shift+回车键结束 公式五：=SUM(N(COUNTIF(A1:A10,A1:A10)=1)按 Ctrl+Shift+回车键结束 公式六：=SUM(INT(1/COUNTIF(A1:A10,A1:A10)按 Ctrl+Shift+回车键结束 修改后张三李四李明明明周正注意：此方法在尾部产生错误值“#N/A

20、”，记得删除错 一、INDEXMATCH=ROW 法=INDEX(A:A,SMALL(IF(MATCH($A$2:$A$11,$A$2:$A$11,0)=ROW($A$2:$A$11)-1,ROW($2:$11),48),ROW(1:1)&1、对 A 列中的每一项利用 MATCH 函数找出其在 A 列中首次出现的位置，返回相应的行 号。超出列表范围时将返回一个较大的行号，如 65536。4、用 A 列的值与”连接作为最终的值，当行号足够大时，对应的 A 列单元格为空单元格，空单元格与连接，依然是空单元格。最终结果，从 A 列筛选出的没有重复数据的单元格排在表格的上半部分，下半部分为空单 元格，

21、正是我们想要的效果。二、COUNTIF 法=INDEX(A:A,MIN(IF(COUNTIF($C$1:C1,$A$2:$A$160)=0,ROW($A$2:$A$160),48)&(C2开始输入公式)这种解法看着有点怪异，一般人是想不到的，除非你是二般人。说它怪，怪在哪儿呢？怪就 怪在它的着眼点让人匪夷所思：1、要查找 A 列中不重复的数据，一般人都会首先考虑怎样处理 A 列中的数据，其它无关的 或空白区域是不会上眼的。这种解法的独到之处就是首先在一空白列处查找 A 列中的数据，如果找不到就返回该数据所在的行号，否则返回一个较大的值 65536。三、FREQUENCY 法（仅适用于数值）=I

22、NDEX(A:A,SMALL(IF(FREQUENCY($A$2:$A$11,$A$2:$A$11),ROW($A$2:$A$11),48),ROW(1:1)&1、用 FREQUENCY 函数判断 A 列中的每一个数据是否在 A 列中出现一次，如果仅出现一 次就返回当前行号，否则返回一个较大的数 65536。1、利用函数 COUNTIF 统计 A 列中的数据是否大于 1，如果大于 1，返回空值，否则返回 当前值。公式：INDEX($C$4:$C$10,MATCH(0,COUNTIF($I$3:$I3,$C$4:$C$10),0)&二、COUNTIF 法=INDEX(A:A,MIN(IF(COU

23、NTIF($C$1:C1,$A$2:$A$160)=0,ROW($A$2:$A$160),48)&(C2开始输入公式)这种解法看着有点怪异，一般人是想不到的，除非你是二般人。说它怪，怪在哪儿呢？怪就 怪在它的着眼点让人匪夷所思：1、要查找 A 列中不重复的数据，一般人都会首先考虑怎样处理 A 列中的数据，其它无关的 或空白区域是不会上眼的。这种解法的独到之处就是首先在一空白列处查找 A 列中的数据，如果找不到就返回该数据所在的行号，否则返回一个较大的值 65536。WPSWPS 函函数数与与公公式式技技巧巧(抽抽奖奖小小程程序序)职工名单 随机数幸运者张三0.630389732三碧命李四0.2

24、404306青城王五0.937486257李四李柳0.950021753张三张樯0.657019687立强立强0.651860816张浩杰0.74530913三碧命0.692687877投影0.342477611皮龙0.779762613青城0.510247277太虚0.237934765返回F9按住F9键WPSWPS 函函数数与与公公式式技技巧巧(提提取取日日期期)目标内容提取的内容在线答题活动（2017年9月23日）班组安全活动的意义2017年9月23日返回公式：=MID(B3,FIND(（,B3)+1,FIND(）,B3)-1-FIND(（,B3)解释：利用mid函数，提取两个括号之间的

25、内容。FIND(（,B3)+1 是取“（”的前一位公式：=MID(B3,FIND(（,B3)+1,FIND(）,B3)-1-FIND(（,B3)解释：利用mid函数，提取两个括号之间的内容。FIND(（,B3)+1 是取“（”的前一位WPSWPS 函函数数与与公公式式技技巧巧(提提取取文文件件夹夹中中的的所所有有文文件件)在工作簿中输入公式if语句是用来判断是否到达文件显示的尾部，如果行数超过文件的个数，将显示为空。返回1=files=files（“D:aaa*”)“D:aaa*”)2=IF(ROW()COUNTA(=IF(ROW()COUNTA(文文件件名名称称),INDEX(),INDEX

26、(文文件件名名称称,ROW()-1),ROW()-1)if语句是用来判断是否到达文件显示的尾部，如果行数超过文件的个数，将显示为空。=IF(ROW()COUNTA(=IF(ROW()COUNTA(文文件件名名称称),INDEX(),INDEX(文文件件名名称称,ROW()-1),ROW()-1)WPSWPS 函函数数与与公公式式技技巧巧(去去除除单单元元格格中中的的空空格格)单单元元格格文文本本中中的的空空格格，没没有有特特殊殊标标记记，很很难难发发现现，对对我我们们查查找找和和搜搜索索结结果果带带来来很很大大影影响响，有有时时可可能能带带来来错错误误的的结结果果。而而且且极极难难发发现现错错

27、误误所所在在之之处处，我我们们的的想想法法就就是是去去掉掉文文本本中中的的空空格格。姓名分数张三34李四45张浩杰99庞锋99庞锋100姓名去除空格张三张三李四李四张浩杰张浩杰庞锋庞锋庞锋庞锋姓名去除空格张三张三李四李四张浩杰张浩杰庞锋庞锋庞锋庞锋返返回回这这里里我我们们要要查查找找“庞庞锋锋”显显然然查查不不到到，因因为为前前一一个个中中间间有有一一个个空空格格，后后一一个个前前边边有有一一个个空空格格，不不仔仔细细看看很很难难发发现现，但但数数据据量量大大的的时时候候要要想想发发现现错错误误，那那估估计计得得花花上上一一段段时时间间。使用substitute函数函函数数SUBSTITUTE

28、(B16,SUBSTITUTE(B16,),)把把原原来来的的空空格格都都替替换换掉掉使用trim函数函函数数trimtrim(B(B2727)，然然后后下下拉拉说说明明：trimtrim函函数数只只能能去去除除字字符符串串两两边边的的空空格格，中中间间有有多多个个空空格格的的话话要要保保留留一一个个空空格格，一一般般用用于于英英文文书书写写，请请注注意意WPSWPS 函函数数与与公公式式技技巧巧(链链接接函函数数“hyperlink”“hyperlink”用用法法大大全全)打开C盘=HYPERLINK(c:/,打开C盘)打开D盘下载文件夹=HYPERLINK(D:/下载/,打开D盘下载文件夹

29、)打开D盘下载文件夹中照片 018=HYPERLINK(D:/下载/照片 018.jpg,打开D盘下载文件夹中照片 018)跳转单元格=HYPERLINK(#K3,跳转单元格)，地址前加“#”需要使用#表名！单元格跳转其他工作簿返返回回用用法法HyperlinkHyperlink函函数数HYperlinkHYperlink（参参数数1 1，参参数数2 2）参参数数1 1：需需要要跳跳转转的的链链接接地地址址参参数数2 2：单单元元格格显显示示的的文文字字1.1.跳跳转转到到本本地地文文件件夹夹2.2.跳跳转转到到本本地地文文件件夹夹文文件件3.3.跳跳转转到到当当前前工工作作簿簿中中的的某某个

30、个单单元元格格4.4.跳跳转转到到当当前前工工作作簿簿中中其其他他工工作作表表中中某某个个位位置置5.5.跳跳转转到到其其他他工工作作簿簿中中工工作作表表中中某某个个位位置置使用本地文件全路径表名!单元格如F盘学习资料中的公众号素材.xlsx中sheet2表的F1单元格=HYPERLINK(F:学习资料公众号素材.xlsxsheet2!F1,跳至公众号素材sheet2的F1)跳至公众号素材sheet2的F1链接到百度注意要打上http:/编编号号内内容容所所用用函函数数和和公公式式链链接接地地址址案例1从身份证计算出岁数、生日、性别mid datedif点这里查看“从身份证计算出岁数、生日、性

31、别”案例2提取QQ号码 mid left 点这里查看“提取QQ号码”首先建立了名称管理器，然后进行跳转#名称如在hyperlink详解表格里面建立了一个名称为数据源，引用的E:F列利用函数快速链接过去=HYPERLINK(#数据源,打开数据源)当工作表中存在宏时，可以对宏编程跳转#宏名=HYPERLINK(#宏1,打开宏)6.6.跳跳转转至至网网络络地地址址7.7.单单元元格格参参数数引引用用8.8.跳跳转转至至名名称称管管理理器器9.9.跳跳转转至至宏宏编编辑辑器器1010特特别别说说明明：如如果果引引用用的的表表中中有有特特殊殊符符号号如如表表名名为为1-11-1需需要要在在1-11-1前

32、前后后加加上上单单引引号号，否否则则会会出出现现错错误误！=HYPERLINK(#1-1!F1,=HYPERLINK(#1-1!F1,表表格格)=HYPERLINK(D:/下载/照片 018.jpg,打开D盘下载文件夹中照片 018)WPSWPS 函函数数与与公公式式技技巧巧(实实现现可可精精确确查查询询、可可模模糊糊查查询询)序号姓名科目成绩模糊查询姓名1张三数学56姓姓名名2李四数学88张三3李明数学78李四4明明数学67李明5周正语文89明明6李四地理90周正7张浩杰科学100李四8庞锋科学1009岳小林语文10010张宁科学10011李文钊科学100解释：C:C是要查询并返回的数值，F

33、IND($J$3,$C$4:$C$14,1)，在C4到C14的单元格中查询J3中你输入的数值，ROW()-4是单元格所在行号减去上面4个行后从该处计算返回值。所解释的参数是需要在实际运用中进行适当的变化的。公公式式：INDEX(C:C,SMALL(IF(ISERROR(FIND($J$3,$C$4:$C$14,1),9999,ROW($C$4:$C$14),ROW(INDEX(C:C,SMALL(IF(ISERROR(FIND($J$3,$C$4:$C$14,1),9999,ROW($C$4:$C$14),ROW()-)-4)&4)&记记得得“ctrl”+“shift”+“enter”“ctr

34、l”+“shift”+“enter”返返回回科科目目成成绩绩数学56数学88数学78数学67语文89地理90解释：C:C是要查询并返回的数值，FIND($J$3,$C$4:$C$14,1)，在C4到C14的单元格中查询J3中你输入的数值，ROW()-4是单元格所在行号减去上面4个行后从该处计算返回值。所解释的参数是需要在实际运用中进行适当的变化的。公公式式：INDEX(C:C,SMALL(IF(ISERROR(FIND($J$3,$C$4:$C$14,1),9999,ROW($C$4:$C$14),ROW(INDEX(C:C,SMALL(IF(ISERROR(FIND($J$3,$C$4:$C

35、$14,1),9999,ROW($C$4:$C$14),ROW()-)-4)&4)&记记得得“ctrl”+“shift”+“enter”“ctrl”+“shift”+“enter”WPSWPS 函函数数与与公公式式技技巧巧(用用indirectindirect工工作作表表之之间间的的动动态态跳跳转转)序号工作表名称取得每个工作表A1单元格的数据1案例1样表2案例2提取QQ号码3案例3WPS 函数与公式技巧4案例4制作多级菜单5案例5制作多级菜单6案例6Datedif函数技巧7案例7提取不重复数据8案例8抽奖小程序9案例9提取日期10案例10提取文件夹中的所有文件11案例11去除单元格中的空格返

36、回返回WPSWPS 函函数数与与公公式式技技巧巧(制制作作动动态态响响应应图图表表)月份2017.12考考核核得得分分序序号号考考核核类类别别权权重重0101轮轮0202轮轮0303轮轮0505轮轮1生产安全责任事故考核20202020202月度安全检查考核1511.611.7217054313.512.595731833隐患整改完成率考核10101010104动火、同场作业考核10101010105安全培训考核54.64.75.04.76作业质量度考核10101010107应急演练完成考核555558作业时间利用率考核54.84.74.35.09设备完好率考核55.05.05.04.910油

37、耗指标完成率考核1010.010.010.010.011专题工作完成情况考核5555512加分0.531.21.413综合得分96.4899.0499.0298.6314兑现率（%）15单船分数210101轮轮0202轮轮0303轮轮0505轮轮0606轮轮0707轮轮0808轮轮0909轮轮1010轮轮1111轮轮1212轮轮202020202020202020202001轮02轮03轮05轮06轮07轮08轮09轮10轮11轮12轮按船舶按项目生产安全责任事故考核返回1 首先，观察表格，一共有2个维度的参数，分别是按船舶分类和按考核类别分类，可以分为两类，一类是按照不同出船舶显示，另外是按

38、照不同项目显示。2 选项按钮2个3 插入列表框4 设置名称202020202020202020202001轮02轮03轮05轮06轮07轮08轮09轮10轮11轮12轮这里分别建立三个名称（1）名称：坐标轴，引用位置：=IF(案例15!$B$22=2,案例15!$B$23:$B$33,案例15!$B$5:$B$17)（首先在B23:B33输入船舶数据）（2）名称：序列，引用位置：=IF(案例15!$B$22=1,案例15!$B$23:$B$33,案例15!$B$5:$B$17)（3）名称：数据表，引用位置：=IF(案例15!$B$22=2,OFFSET(案例15!$D$5,案例15!$C$22

39、-1,0,1,11),OFFSET(案例15!$D$5,0,案例15!$C$22-1,12,1)5 设置图表随便选择一些区域建立图表区域，不要管数据内容，先建立 12300.20.40.60.811.20606轮轮0707轮轮0808轮轮0909轮轮1010轮轮1111轮轮1212轮轮2020202020202013.19146366 12.595731831311.514.0329146313.512.5228136910101010101010101010101010104.74.94.94.85.04.84.81010101010101055555554.84.44.64.64.64.7

40、4.44.94.94.44.54.64.54.69.410.09.49.310.010.010.055555550.52.73.51.50.50.60.897.6099.4499.8696.1998.8098.0597.13考考核核得得分分这里数据源暂时不输入，之后设置完成“序列”之后，再设置首先，观察表格，一共有2个维度的参数，分别是按船舶分类和按考核类别分类，可以分为两类，一类是按照不同出船舶显示，另外是按照不同项目显示。（1）名称：坐标轴，引用位置：=IF(案例15!$B$22=2,案例15!$B$23:$B$33,案例15!$B$5:$B$17)（首先在B23:B33输入船舶数据）（2

41、）名称：序列，引用位置：=IF(案例15!$B$22=1,案例15!$B$23:$B$33,案例15!$B$5:$B$17)（3）名称：数据表，引用位置：=IF(案例15!$B$22=2,OFFSET(案例15!$D$5,案例15!$C$22-1,0,1,11),OFFSET(案例15!$D$5,0,案例15!$C$22-1,12,1)随便选择一些区域建立图表区域，不要管数据内容，先建立 12300.20.40.60.811.2备备注注A A公公司司B B公公司司C C公公司司60.060.0100.036.838.464.630.030.050.030.030.050.014.214.424

42、.330.030.050.015.015.025.013.814.223.014.914.722.730.029.448.615.015.025.04.74.66.9294.5295.7490.027.266%27.371%45.363%98.298.698.0WPSWPS 函函数数与与公公式式技技巧巧(全全表表查查询询，输输入入一一关关键键字字都都能能查查出出符符合合条条件件的的所所有有信信息息。)张序号姓名员工编号性别学历工作时间部门手机号码1张三A001男大学2018/7/9工程部133333344423456789101112131415输入查询内容本例中，运用添加辅助列的方式，利用v

43、lookup和match组合实现查找第一步：在原数据基础上，添加辅助列（1）为了全局查找，把N:U列数据合并，这里用到concat函数，合并字符串。（2）建立匹配项，把合乎要求的行显示出来。IFERROR(IF(FIND($C$3,M6)0,“”),)第一步：在原数据基础上，添加辅助列（1）为了全局查找，把N:U列数据合并，这里用到concat函数，合并字符串。（2）建立匹配项，把合乎要求的行显示出来。IFERROR(IF(FIND($C$3,M6)0,“”),)爱好序号匹配合并项辅助列吃饭1张三A001男大学43290工程部1333333444吃饭1李柳A002女大学42713机械部1567

44、75444睡觉1郑久忠A003男小学37141现场办132536988459打牌1黄国中A004男高中39698稽查部132536988459打人11111111111返回第一步：在原数据基础上，添加辅助列（1）为了全局查找，把N:U列数据合并，这里用到concat函数，合并字符串。（2）建立匹配项，把合乎要求的行显示出来。IFERROR(IF(FIND($C$3,M6)0,“”),)第一步：在原数据基础上，添加辅助列（1）为了全局查找，把N:U列数据合并，这里用到concat函数，合并字符串。（2）建立匹配项，把合乎要求的行显示出来。IFERROR(IF(FIND($C$3,M6)0,“”)

45、,)姓名员工编号性别学历工作时间部门手机号码爱好张三A001男大学2018/7/9工程部1333333444吃饭李柳A002女大学2016/12/9机械部156775444睡觉郑久忠A003男小学2001/9/7现场办132536988459打牌黄国中A004男高中2008/9/7稽查部132536988459打人原始数据WPSWPS 函函数数与与公公式式技技巧巧(输输入入日日期期后后不不随随时时间间变变化化)录入日期问题描述录入人2022/10/20迟到张三2022/10/20作业未完成张三录入日期问题描述录入人2019/4/27早退张胜男2019/4/27迟到李四返返回回大大家家一一定定遇

46、遇到到过过这这种种情情况况，想想让让系系统统自自动动写写入入录录入入日日期期，使使用用todaytoday（）或或nownow（）函函数数，但但是是，这这两两个个函函数数都都是是取取系系统统当当前前时时间间，每每次次打打开开记记录录表表，就就更更新新到到系系统统当当前前时时间间，这这显显然然不不是是我我们们想想要要的的结结果果。那那么么，看看看看以以下下的的办办法法在在B12B12 单单元元格格中中输输入入，=IF(C12=,IF(B12=,TODAY(),B12)=IF(C12=,IF(B12=,TODAY(),B12)，这这样样就就会会自自动动填填入入录录入入日日期期。但但是是，有有两两点

47、点注注意意1.1.作作为为判判断断，C12C12单单元元格格事事先先不不能能有有数数据据，要要为为空空，2.2.要要选选择择迭迭代代计计算算，不不然然会会计计算算错错误误系系统统菜菜单单-选选项项-重重新新计计算算-迭迭代代计计算算 打打钩钩录入日期问题描述录入人#VALUE!工作不认真张三#VALUE!迟到张三另另外外的的方方法法=IF(M4=,IF(M4=CELL(contents)*(CELL(col)=13)*(CELL(r=IF(M4=,IF(M4=CELL(contents)*(CELL(col)=13)*(CELL(row)=ROW(),NOW(),L4)ow)=ROW(),NO

48、W(),L4)=IF(M4=,IF(M4=CELL(contents)*(CELL(col)=13)*(CELL(r=IF(M4=,IF(M4=CELL(contents)*(CELL(col)=13)*(CELL(row)=ROW(),NOW(),L4)ow)=ROW(),NOW(),L4)WPSWPS 函函数数与与公公式式技技巧巧(获获取取工工作作簿簿地地址址、名名称称)1 1 返返回回工工作作簿簿的的路路径径，如如果果未未保保存存，则则返返回回为为空空C:audit-20221020921004e024084efcb993e0d218ca253a8f4abf71-f8aa-3a6f-b0

49、4a-97dd4eb2b40d8f4abf71-f8aa-3a6f-b04a-97dd4eb2b40d.xlsx首页2 2 提提取取工工作作簿簿名名称称#VALUE!3 3 提提取取工工作作表表名名称称首页nfo_typenfo_type返返回回结结果果“address”reference中第一个单元格的引用，文本类型。“col”reference中单元格的列号。“color”如果单元格中的负值以不同颜色显示，则为值1；否则返回0（零）。“contents”reference中左上角单元格的值；不是公式。“filename”包含reference的文件名（包括全部路径），文本格式。如果包含re

50、ference的工作表尚未保存，则返回空文本()。“format”与单元格中的数字格式对应的文本值。下表显示了不同格式的文本值。如果单元格中的负值以不同颜色显示，则在文本值结尾处返回“-”；如果单元格中为正值或所有值加括号，则在文本值结尾处返回“()”。“parentheses”如果单元格中为正值或全部值加括号，则值1；否则，返回0。“prefix”与单元格中“标签前缀”相对应的文本值。如果单元格文本左对齐，则返回单引号（）；如果单元格文本右对齐，则返回双引号（）；如果单元格文本居中，则返回插字号（）；如果单元格文本两端对齐，则返回反斜线（）；如果是其他情况，则返回空文本（）。“protect