专栏名称: Excel之家ExcelHome

excel技巧原创教程每日推送，excel表格职场模板干货仓库，图文/视频/动画等多种教学方式分享excel操作技巧教程/excel函数公式教程/excel数据透视表教程/excel图表教程/Word教程，助您轻松提高办公效率!

WPS表格中的这些特色函数，确实不孬

Excel之家ExcelHome · 公众号 · Excel · 2024-12-15 07:45

主要观点总结

文章介绍了在WPS表格中如何使用常用函数公式进行特定操作。包括计算有效面积、删除关键字、获取工作表名称、提取金额信息以及按部门汇总员工职务与姓名等。

关键观点总结

关键观点1: 有效面积的计算

使用SUBSTITUTES函数替换测量记录中的尺寸数据，再通过EVALUATE函数计算文本算式的结果。

关键观点2: 删除关键字

使用SUBSTITUTES函数根据对照表删除B列单元格中的关键字。

关键观点3: 获取工作表名称

介绍使用SHEETSNAME函数获取工作表名称的方法，并通过HYPERLINK函数创建工作表目录。

关键观点4: 提取金额信息并求和

使用REGEXP函数提取混合信息中的金额部分，通过SUM函数进行求和汇总。

关键观点5: 按部门汇总员工职务与姓名

使用PIVOTBY函数按部门分类汇总员工职务与姓名信息，通过ARRAYTOTEXT函数将结果转换为文本形式。

正文

小伙伴们好啊，今天咱们以WPS表格为例，分享几个常用函数公式。

1、如下图，B~D列是设备保温尺寸的测量记录，E列为有效面积的计算方式说明，希望在F列计算出有效面积。

F2单元格输入以下公式，向下复制：

=EVALUATE(SUBSTITUTES(E2,B$1:D$1,B2:D2))

SUBSTITUTES函数的作用是将多个待替换的内容批量替换为其他内容。

第一参数是要处理的字符，第二参数是要从中替换的旧字符（组），第三参数是要替换成的新字符（组）。

公式首先使用SUBSTITUTES函数，将E2单元格中的“长”“宽”“高”字样，分别替换为B~D列的实际尺寸。其中B$1:D$1是要替换的旧字符，B2:D2是要替换为的新字符。替换后的结果如下：

"15*6*2+6*5+15*5"

最后使用EVALUATE函数将文本算式转换为实际计算结果。

2、如下图所示，使用以下公式可以根据右侧的对照表，将B列单元格中包含的关键字全部删除。

=SUBSTITUTES(B2,E$3:E$5,)

SUBSTITUTES函数支持动态溢出，本例中，第一参数使用多个单元格，第三参数省略，表示将第二参数中的字符全部删除。

3、如下图所示，A2单元格输入以下公式，得到工作表名称的详单，并且不包含当前工作表名称。

=SHEETSNAME(,1,1)

B2单元格输入以下公式，再将公式下拉复制，即可得到带链接的工作表目录。

=HYPERLINK("#'"&A2&"'!A1","跳转")

4、如下图所示，A列是姓名和金额的混合信息，希望提取出其中的金额部分，并进行求和汇总。

B2单元格输入以下公式，向下复制即可。

=SUM(REGEXP(A2,"[0-9.]+")*1)

REGEXP函数的参数使用[0-9.]+ 表示提取包含小数点的连续数字。

接下来乘以1转换为数值，再用SUM函数求和。

5、如下图，A~C列是各部门员工职务与姓名信息，希望按部门来汇总不同职务的员工姓名。

E2单元格输入以下公式：

=PIVOTBY(A1:A31,B1:B31,C1:C31,ARRAYTOTEXT,1,0,,0)

PIVOTBY函数的作用是按行列对数据进行分类汇总。

公式中的A1:A31是行字段，B1:B31是列字段，C1:C31是值字段，聚合方式为ARRAYTOTEXT函数，表示将数组转换为文本形式。

后面的几个参数用来指定标头和总计行的显示方式。

好了，今天咱们的分享就是这些吧，祝各位一天好心情~~

图文制作：祝洪忠

1、点击或搜索微信公众号【 Excel之家ExcelHome】到公众号首页。

2、点击右上角【···】，在下拉菜单中点击【设为星标】。

设置星标后，小伙伴们就不用担心错过咱们的推送了！

设为星标，精彩内容不错过