造价人必备Excel操作技巧（二）你知道几个?

服务新干线 2022-07-29 | 4758阅读 0 赞

内容来源：公众号欣欣向荣学造价

作者：张向荣

文章已获作者授权

继续接上一次文章，继续给大家带来常见实用的Excel操作技巧，下面我们就一起学习起来吧！

1、Sumproduct函数一键汇总

大家在汇总多户型工程量的时候是否还在用公式“=A户型单户工程量*A户型户数+ B户型单户工程量*B户型户数+ C户型单户工程量*C户型户数……”，一系列的操作下来估计会使大家眼花缭乱了。

造价人

图1

其实有一个捷径，用Sumproduct函数就可以简单、高效地实现这一功能了。这个函数的功能就是，两个区域数据乘积，再加一起。案例的动态演示，如图2所示。

造价技能

图2

所用公式 =SUMPRODUCT(C4:E4,F4:H4)

第一个参数（C4:E4）：第一个区域数组
第二个参数（F4:H4）：第二个区域数组

2、条件格式快速标注报价最高(低)价

对于业主或造价咨询人员在对投标文件进行回标分析的时候一定是时间紧、任务重。

如何才能快速地将各家单位投标单价中的最高价和最低价以不同颜色突出显示，以方便下一步去判断各投标单位的投标报价高低。

条件格式就可以帮我们这个忙。下面请看案例。

建筑行业专业资讯门户-新干线头条

图3

第1步，选择范围选中单元格E2：G2，
第2步，在条件格式中选取“新建规则”。
第3步，选择“使用公式确定要设置格式的单元格”。
第4步，输入公式，这里采用的是提前输入好复制的

选择最小值“=E2=MIN(\$E2:\$G2)”，对应格式选成蓝色

选择最大值“=E2=MAX(\$E2:\$G2)”，对应格式选成红色

需要注意的是，”\$”符号绝不能省略。

把其它单元格用同样的显示方法，就不能拖动了，是用格式刷。

造价人

图4

第5步，最后批量生产的过程中，只需点击格式刷，再选取需要对比的范围，就大功告成了。

3、冻结窗口的妙用

大家平常有没有碰到过非常大的表格，Excel表格宽了或者长了找数据容易错行，对应不到数据，大家是否有用过冻结窗口呢？

如图5所示，表格浏览起来很麻烦，但是锁定了表头，数据对应起来就方便多了。

造价技能

图5

案例选择时用到快捷键，Ctrl+shift+↓（向下键），即可快速从第一行选到最后一行。

4、表格转置的妙用

在造价工作中有些时候为了方便计算和公式链接，需要将表格的行与列相互转置。下面请看动态演示。

建筑行业专业资讯门户-新干线头条

图6

5、快速求和

如果遇到了这样的表格你会怎么做？一个个求和，再汇总？来看看简单方法吧。

造价人

图7

“Ctrl+g”定位空值，按“alt+=”一键设置求和公式。

造价技能

图8

这样的一键求和，是不是很酷，只需要Ctrl+g，alt+=两个快捷键就可以做到了。

6，Sumif单条件求和的应用

如案例所示，需要求得计划量的总和，实际量的总和，就需要用到Sumif函数了。

=Sumif的格式是：（条件范围，求和条件，求和数据范围）

建筑行业专业资讯门户-新干线头条

图9

所用公式 =SUMIF(\$B\$2:\$G\$2,H\$2,\$B3:\$G3)

第一个参数（\$B\$2:\$G\$2）：条件的范围
第二个参数（H\$2）：求和条件
第二个参数（\$B3:\$G3）：求和范围

解读：

条件范围为B2:G2，是“计划和实际”而且不会变化，所以采用绝对引用的形式；

条件为“计划”和“实际”，要根据不同的情况进行变化，而“计划”和“实际”在不同列，同一行，故列采用相对引用的形式，也就是H\$2；

求和范围为数据区B4：G4，列的范围不会发生变化，而行要变化，所以是\$B3:\$G3。

软件中按F4，可以快速切换引用模式，不用手动打\$了。

关于引用模式：

1、\$A\$1(绝对引用) 在复制公式时所引用的A1,不会随着复制的而改变。

2、A\$1(相对引用) 在复制公式时引用的A会改变，但1不会改变。列不动，行数据变化。

3、\$A1(相对引用) 在复制公式时引用的1会改变，但A不会改变。行不动，列数据变化。

可能对表格不了解的同学还是不明白，最简单的办法，就是现在就打开excel，做一个下图所示的乘法表，实践中一下子能明白引用的用法了，这个在使用表格中是很基础的概念知识。

造价人

图9

先做出来的同学，可以在评论里分享公式。

7、身份证号在excel的应用

如果你是一个hr，那么登记么身份证一定经常做的事情，下面有一些处理身份证小技巧，如需要登记他们出生日期，但是表格里又没有记录，要怎么操作呢？

造价技能

图10

这里可以用下面这些公式，可以直接复制使用，B2对应你表格中的身份证号，除此之外还籍贯，退休日期等，都可以用函数轻松一键生成。

1、出生年月

=TEXT(MID(B2,7,8),"0-00-00")

2、计算性别

=IF(MOD(MID(B2,17,1),2),"男","女")

3、年龄公式

=DATEDIF(B2,TODAY(),"y")

本文讲解了一些excel的一些函数的应用与使用技巧，大家可以平常多收藏，练习一下，表格软件看似简单，但是会用和用的好真的不一样，同学们有什么实用的技巧也可以分享出来给其它同学。

新干线头条是提供给用户的知识共享平台，新干线头条上的内容均由在平台上注册的用户发布，如您在新干线头条上发现违法或侵权内容，请【立即反馈】或拨打我们的投诉专线4000-960-166与我们联系。

反馈

热评

(作者) 内容检查中未能发表

回复() 反馈删除赞()

(作者)

@ {{replyItem.content}}

回复反馈删除赞()

内容检查中未能发表

提交提交中...

暂时没有回复，收到回复时可在此看到并进行答复

还有0条回复正在审核中，请稍后回来查看

共28条评论

网友评论文明上网，理性发言，共同维护网络环境

提交提交中...

选为热评 (作者) 内容检查中未能发表

回复() 反馈删除赞()

(作者)

@ {{replyItem.content}}

回复反馈删除赞()

内容检查中未能发表

提交提交中...

暂时没有回复，收到回复时可在此看到并进行答复

还有0条回复正在审核中，请稍后回来查看

更多评论

服务新干线
官方认证权威媒体作者

已发表1146篇文章

造价人必备Excel操作技巧（二）你知道几个?

服务新干线
官方认证权威媒体作者

推荐关注换一换

头条热榜换一换

造价人必备Excel操作技巧（二）你知道几个?

服务新干线 官方认证权威媒体作者

推荐关注换一换

头条热榜 换一换

服务新干线
官方认证权威媒体作者

头条热榜换一换