小伙伴们好啊,今天咱们以WPS表格为例,分享几个常用函数公式。
1、如下图,B~D列是设备保温尺寸的测量记录,E列为有效面积的计算方式说明,希望在F列计算出有效面积。
F2单元格输入以下公式,向下复制:
=EVALUATE(SUBSTITUTES(E2,B$1:D$1,B2:D2))
![](http://mmbiz.qpic.cn/mmbiz_png/BAbVqibwwtmyEbXFWYqwCwziaCLxPFqFLADHZTibyAYYWrwwiax5eOEmGJyO2oxNXJicYCZZ3pYUNCfIKBzBWy4OibibQ/640?wx_fmt=other&from=appmsg&wxfrom=5&wx_lazy=1&wx_co=1&tp=webp)
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,)
![](http://mmbiz.qpic.cn/mmbiz_png/BAbVqibwwtmwGqicPFrPxuDXicXAMcQXmicurVTZDL5KHKicTn4ibUHt97JTPu962ADP5lZZzD4XjSQzVk1BGyyoOeWw/640?wx_fmt=other&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1)
SUBSTITUTES函数支持动态溢出,本例中,第一参数使用多个单元格,第三参数省略,表示将第二参数中的字符全部删除。
3、如下图所示,A2单元格输入以下公式,得到工作表名称的详单,并且不包含当前工作表名称。
=SHEETSNAME(,1,1)
![](http://mmbiz.qpic.cn/mmbiz_png/BAbVqibwwtmyZx78Nt7QYBQhO4vCFJoS6XOw2vKCrscxI2HkT0YXORMIPNyDNiaLJ9DFz0chviamAuT67Vxl0OlIQ/640?wx_fmt=other&from=appmsg&wxfrom=5&wx_lazy=1&wx_co=1&tp=webp)
B2单元格输入以下公式,再将公式下拉复制,即可得到带链接的工作表目录。
=HYPERLINK("#'"&A2&"'!A1","跳转")
![](http://mmbiz.qpic.cn/mmbiz_png/BAbVqibwwtmyZx78Nt7QYBQhO4vCFJoS6k8BKIv9HU5fjasGOtkeERABYiavKOa3J2ZvQia62ic4vibb81vs3tuIG0w/640?wx_fmt=other&from=appmsg&wxfrom=5&wx_lazy=1&wx_co=1&tp=webp)
4、如下图所示,A列是姓名和金额的混合信息,希望提取出其中的金额部分,并进行求和汇总。
B2单元格输入以下公式,向下复制即可。
=SUM(REGEXP(A2,"[0-9.]+")*1)
![](http://mmbiz.qpic.cn/mmbiz_png/BAbVqibwwtmwGqicPFrPxuDXicXAMcQXmicuGGxy6135YgKfqEpfKoAzt9tvKe7jhYBKfAuCoEYDCtaTXCQyfSTwYg/640?wx_fmt=other&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1)
REGEXP函数的参数使用[0-9.]+ 表示提取包含小数点的连续数字。
接下来乘以1转换为数值,再用SUM函数求和。
5、如下图,A~C列是各部门员工职务与姓名信息,希望按部门来汇总不同职务的员工姓名。
=PIVOTBY(A1:A31,B1:B31,C1:C31,ARRAYTOTEXT,1,0,,0)PIVOTBY函数的作用是按行列对数据进行分类汇总。公式中的A1:A31是行字段,B1:B31是列字段,C1:C31是值字段,聚合方式为ARRAYTOTEXT函数,表示将数组转换为文本形式。