搞造价都应该会的excel技巧分享（二）

继续接上一次文章，继续给大家带来常见实用的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')

很多同学用的wps软件，留言说要微软的excel软件使用，找不到资源，这里分享给大家office2016软件。（软件源于网络，使用后24小时后删除）