盘点WPS那些超级好用的神级函数指令

一、盘点那些好用的函数清单1.1、基础高频函数这些函数适合处理日常数据判断、查找和汇总任务函数名称使用场景用法案例IF函数根据条件进行判断和返回不同结果IF(A260, 及格, 不及格)判断成绩是否及格。IF(AND(A2500, B2未到期), 补款, )同时满足两个条件时提示补款。VLOOKUP函数按列查找特定值并返回对应数据。适合单条件查询VLOOKUP(张小敬, A2:D10, 3, FALSE)在A2:D10区域精确查找张小敬并返回第3列的值。SUMIF/SUMIFS函数按条件单条件/多条件对数据进行求和SUMIF(B2:B10, 北京, C2:C10)统计B列中北京对应C列的总和。SUMIFS(C2:C10, A2:A10, 电视, B2:B10, 郑州)统计郑州电视机的销量。COUNTIF/COUNTIFS函数按条件单条件/多条件统计单元格个数COUNTIF(D2:D303, *LN*)统计D列中包含LN的型号数量。COUNTIFS(D2:D303, *LN*, F2:F303, 10)统计型号含LN且销量大于10的订单数。ROUND/INT函数对数值进行四舍五入或向下取整ROUND(A1, 2)将A1单元格数值四舍五入保留两位小数。INT(A1)对A1单元格数字向下取整。DATEDIF函数计算两个日期之间的间隔年、月、日DATEDIF(B2, TODAY(), M)计算B2单元格日期到今天相隔的月数。IFERROR函数将公式返回的错误值转换为指定内容使表格更整洁IFERROR(A2/B2, 计算有误)如果A2/B2出错如除数为0则显示计算有误。TEXTSPLIT函数按分隔符拆分文本为多列或多行TEXTSPLIT(A1, -, ,)输入A1为“北京-上海,广州-深圳”输出为2行2列矩阵示例TEXTSPLIT函数用法Textsplit拆分函数如逗号、空格、符号等可以根据分隔符将字符串拆分开语法TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])1参数 text需要拆分的文本2参数 col_delimiter列分隔符号3参数 [row_delimiter]行分隔符号4参数 [ignore_empty]是否忽略空单元格默认为FALSE不忽略空单元5参数 [match_mode]是否区分大小写默认为0不区分6参数 [pad_with])出错时填充的值这个函数的参数虽然比较多但是使用起来却非常的简单。TEXTSPLIT的 “聚类使用” 核心是先拆分文本为结构化数组再通过批量处理函数BYROW/UNIQUE/FILTER等对数组进行聚合、分类或提取适用于处理格式统一的复杂文本如日志、标签、复合信息大幅提升数据整理效率。按分隔符“/”来拆分拆分多列TEXTSPLIT(C2,/)如下如所示需要将C列会计科目拆分成一级、二级和三级科目拆分多行公式TEXTSPLIT(B2,,、)同时设置多个分隔符A列待拆分字符中有多种类型的间隔符号包括逗号、分号斜杠和顿号使用以下公式可以按常量数组的形式将多种分隔符号写到一起。TEXTSPLIT(A2,{/,、,,})提取混合内容中的金额再求和A列是商品和金额的混合信息希望提取出其中的金额部分并进行求和汇总SUM(1*TEXTSPLIT(A2,TEXTSPLIT(A2,ROW(1:10)-1,.,1),,1))1.2、高阶函数这些现代函数可以一次性处理多个数据动态返回结果非常适合处理复杂或动态变化的数据函数名称使用场景用法案例XLOOKUP函数更强大的查找可处理多条件查询无需指定列序数且能自定义错误提示XLOOKUP(E3F3, A2:A7B2:B7, C2:C7)根据E3姓名和F3班级两个条件在A列和B列中查找并返回C列成绩FILTER函数基于条件动态筛选出一系列数据FILTER(B2:B10, A2:A10F3)从A列中筛选出所有等于F3的姓名并返回B列对应的信息。多条件筛选FILTER(返回数组, (条件1)*(条件2), 无结果)用*连接同时满足的条件用连接满足其一的条件UNIQUE函数快速提取指定区域中的唯一值去重UNIQUE(A1:A18)提取A1:A18区域中不重复的名单SORT函数对某个区域或数组的内容自动排序SORT(A1:E10, 2, -1)对A1:E10区域以第2列为依据进行降序排序TEXTJOIN函数用指定分隔符连接列表或文本字符串区域TEXTJOIN(,, TRUE, IF($A$2:$A$18D2, $B$2:$B$18, ))将A列中等于D2的所有B列对应值用逗号合并到一个单元格需按CtrlShiftEnterCONCAT函数直接合并多个单元格的内容比用更简便CONCAT(A2:O2)将A2到O2的所有单元格内容无缝连接起来VSTACK函数将多个数组或区域垂直堆叠合并成一个大的数组VSTACK(01.现金:04.支付宝!A2:E12)将名为01.现金到04.支付宝的所有工作表的A2:E12区域上下合并TAKE/DROP函数从数组的开头或结尾返回/删除指定数量的行或列TAKE(SORT(A1:B14, 2, -1), 4)先对数据按第2列降序排序然后返回前4行数据。DROP(A2:D9, 2)删除A2:D9区域的前2行数据TOCOL/TOROW函数将多行多列数据快速转换成一列或一行TOCOL(A2:F4, 3)将A2:F4区域转换成一列并忽略区域中的空白和错误值WRAPROWS/WRAPCOLS函数将一列数据按行/列顺序转换为指定列数/行数的多行多列表格WRAPROWS(A2:A18, 6, )将A2:A18的单列数据按行方向重新排列成6列空白处用空值填充XLOOKUP 函数用法介绍XLOOKUP 函数是 WPS 表格中强大的查找函数支持正向 / 反向查找、模糊匹配等比 VLOOKUP 更灵活基础语法XLOOKUP(查找值, 查找区域, 返回区域, [未找到时返回值], [匹配模式], [搜索模式])匹配模式0 精确匹配默认1 模糊匹配升序区域-1 模糊匹配降序区域搜索模式1 从首到尾默认-1 从尾到头示例假设有如下员工信息表A1:C6用 XLOOKUP 实现不同查找需求工号A姓名B部门C101张三销售102李四技术103王五人事1. 基础查找根据工号查姓名需求已知工号 “102”查找对应姓名。公式XLOOKUP(102, A2:A6, B2:B6)结果返回 “李四”2. 反向查找根据姓名查工号VLOOKUP 需嵌套其他函数XLOOKUP 直接支持需求已知姓名 “王五”查找对应工号。公式XLOOKUP(王五, B2:B6, A2:A6)结果返回 “103”3. 多条件查找匹配后返回多列结果需求根据工号 “101”同时返回姓名和部门。公式XLOOKUP(101, A2:A6, B2:C6)结果返回数组{张三, 销售}会自动填充到相邻两列4. 模糊匹配查找区间值假设有成绩等级表E1:F5根据分数查等级分数E等级F60及格80良好90优秀需求分数 “85” 对应哪个等级公式XLOOKUP(85, E2:E5, F2:F5, 不及格, 1)匹配模式 “1”模糊匹配小于等于查找值的最大数80结果返回 “良好”5. 未找到时自定义返回值需求查找工号 “105”若不存在则显示 “无此员工”。公式XLOOKUP(105, A2:A6, B2:B6, 无此员工)结果返回 “无此员工”1.3、组合函数应用场景动态排序并获取前N名结合SORT和TAKE函数可以轻松获取排序后的前几名数据。例如TAKE(SORT(A1:B14, 2, -1), 4)先按第二列业绩降序排序然后取前4行。多表合并与统计使用VSTACK合并多个分表的数据再用GROUPBY或FILTER进行统计和清理。例如FILTER(VSTACK(表1:表3!A2:E100), VSTACK(表1:表3!E2:E100)0)可以合并三个表并自动过滤掉值为0的行。提取不重复值并计数使用UNIQUE提取唯一值再嵌套COUNTA进行不重复计数。例如COUNTA(UNIQUE(B2:B18))统计B列中有多少个不重复的项。1.4、利用WPS AI辅助编写公式如果你不确定该用什么函数或者不想记忆复杂的语法可以尝试WPS AI功能在目标单元格输入等号点击编辑栏右侧的WPS AI图标在对话框中用通俗的语言描述你的计算需求AI会自动生成公式并插入单元格你还可以点击函数解释了解其原理例如你可以对AI说计算型号中包含LN且销量大于10的订单个数AI会为你生成对应的COUNTIFS(D2:D303,*LN*,F2:F303,10)公式直接调用WPS AI函数享受更智能的AI功能WPS表格AI指令示例文本总结使用WPSAI.SUMMARIZE()函数语法为WPSAI.SUMMARIZE(单元格字数)。例如A2 单元格存放某一章节的原文在 B2 单元格中填入WPSAI.SUMMARIZE(A2, 100)即可得到该短文的核心摘要且生成文本字数在 100 字左右中英翻译使用WPSAI.TRANSLATE()函数语法为WPSAI.TRANSLATE(单元格语言)。例如A2 单元格是待翻译的中文文本在 B2 单元格中输入WPSAI.TRANSLATE(A2, 英文)即可将 A2 单元格的中文翻译成英文文本分类使用WPSAI.CLASSIFY()函数语法为WPSAI.CLASSIFY(单元格,用于分类的标签)。例如B2 单元格内容为 “苹果”在 C2 单元格输入WPSAI.CLASSIFY(B2,将食品分类为“蔬菜水果肉类”)则 C2 单元格会显示 “水果”信息提取使用WPSAI.EXTRACT()函数语法为WPSAI.EXTRACT(单元格,想要从单元格文本中提取的信息)。例如C2 单元格内容为 “张三25 岁北京人”在 D2 单元格输入WPSAI.EXTRACT(C2,根据文本提取“年龄”)则 D2 单元格会显示 “25 岁”情感分析使用WPSAI.SENTIMENTANALYSIS()函数语法为WPSAI.SENTIMENTANALYSIS(单元格)。例如A2 单元格内容为 “这个产品非常好用我很喜欢”在 B2 单元格输入WPSAI.SENTIMENTANALYSIS(A2)则 B2 单元格会显示 “正面”利用菜单栏-WPS AI模块写公式、写函数1.5、WPS将一些常用函数功能整合到快捷工具WPS网页版为例二、日常使用小技巧技巧类别功能名称主要作用适用场景视图高亮行列高亮显示当前行/列聚焦查看数据数据演示、大型表格核对视图冻结窗口固定表格特定区域如首行标题、首列序号方便在滚动查看其他数据时始终能看到固定区域的内容处理行 / 列数较多的表格比如财务报表、员工信息表等数据录入智能填充 (CtrlE)自动识别并填充已有数据模式批量提取、合并、规范数据批量填充 (CtrlEnter)在多个选中的单元格内一次性输入相同内容批量填写相同标识、状态或数值自定义序列填充根据自定义列表如部门、等级进行智能填充快速输入固定且有序的内部数据序列格式与美化条件格式变色让单元格边框/数据根据条件自动改变颜色动态突出显示异常值、关键指标文本框标题制作无边框、可自由移动的漂亮标题制作专业美观的报表标题数据验证拒绝重复录入强制阻止在指定区域输入重复项确保工号、订单号等关键信息的唯一性公式与操作快速复制公式双击填充柄快速向下复制公式无需长距离拖动处理长数据列时快速应用公式2.1、视图-高亮行列位置不同的高亮颜色2.2、快速填充数据使用方法1、在wps表格窗口拖动鼠标选中要填充的单元格2、接着输入数值内容3、完成后按住 【CtrlEnter】组合键批量完成输入即可在空白单元格中填充相同内容先选中包含空白单元格的区域然后按 CtrlG 打开 “定位” 对话框选择 “空值”点击 “确定”此时所有空白单元格会被选中接着在编辑栏中输入要填充的内容按下 CtrlEnter 键即可在所有空白单元格中填充相同内容。快速应用公式到多个单元格先在一个单元格中输入公式然后选中需要应用该公式的多个单元格可以是连续的也可以是不连续的按下 CtrlEnter 键公式会自动应用到所有选中的单元格中并且会根据单元格的相对位置自动调整公式中的引用。拆分合并单元格并填充内容选中合并过的区域点击 “开始”-“合并居中” 取消合并按 CtrlG 打开定位选择 “空值”输入 “↑”表示引用上方单元格的值后按 CtrlEnter 批量填充即可将合并单元格拆分并填充相应内容。2.3、设置条件样式