工程施工中十个EXCEL函数应运用技巧文档格式.docx

工程施工中十个EXCEL函数应运用技巧文档格式.docx

《工程施工中十个EXCEL函数应运用技巧文档格式.docx》由会员分享，可在线阅读，更多相关《工程施工中十个EXCEL函数应运用技巧文档格式.docx（22页珍藏版）》请在冰豆网上搜索。

在需要填写周岁年龄的单元中写上以下代码，“=DATEDIF（DATE（LEFT（D9,4）,MID（D9,5,2）,RIGHT（D9,2））,TODAY（）,"

）”。

在本例中是F9单元格，如图4所示。

图3

图4

仔细看图4的计算结果可看出，这种方法计算的结果非常准确。

你看：

计算周岁的日期2020年12月13日，王五的出生日期是1980年12月14日，离40周岁还差1天，所以计算结果是39周岁；

而周六的出生日期是1991年12月12日，刚好满29周岁多1天。

是不是很准确！

下面解释一下代码：

DATEDIF（DATE（LEFT（D9,4）,MID（D9,5,2）,RIGHT（D9,2））,TODAY（）,"

），其中TODAY（）函数是获取当天的日期作为计算周岁的日期；

LEFT（D9,4）,MID（D9,5,2）,RIGHT（D9,2）等是字符串截取函数，他们的含义是：

LEFT（D9,4）是从左边开始截4个字符，取得“年”，MID（D9,5,2）是从第5个字符开始截取2个字符，取得“月”，RIGHT（D9,2）是从右边开始截取2个字符取得“日”，这样就把出生日期的年、月、日分离出来，给DATE函数转换符合要求的日期格式。

扩展一下：

不难看出，采用上面的计算公式，计算到当天的周岁，所以每天打开表，计算的结果可能不一样。

若是要计算到指定日期的周岁，计算公式该如何写？

例如：

要计算到2020年6月20日上述人员的周岁，代码是这样的：

DATEDIF（DATE（LEFT（D9,4）,MID（D9,5,2）,RIGHT（D9,2））,"

2020/06/20"

"

），其结果如图5所示。

图5

若是按身份证号又如何计算周岁呢？

如图9所示：

图6

其实也不难，只要在计算周岁的单元格定上如下代码即可：

DATEDIF（DATE（MID（D18,7,4）,MID（D18,11,2）,MID（D18,13,2））,"

2020/6/20"

），周岁的计算日期是2020年6月20日，如图7所示。

哈哈，如此简单！

图7

技巧三Exact函数可以快速查找出表格在修改前后的内容差异！

在平时的造价工作中，大家一定会碰到这样一个问题，在您已经编制了工程量清单的格式（包括清单描述、单位等），需要其他算量同事填写工程量。

但他们完成填写工程量的表格是否会调整或不小心修改过原表格内容（如清单描述或单位等），你要是以一一核对或复核一下，那简直太费时间了，如图8所示。

其实Exact函数火眼金睛，很快识别出其中的差异。

1、在D3中写入“=EXACT（B3,C3）”，该函数的作用是把B3与C3中的内容作比较，如果是一致的，就是“TRUE”，如果有改动就是“FALSE”。

2、复制D3单元格的内容复制到D列的相应单元格，立显检查结果，序号1和2行的内容没有改动，序号3和4的内容已改动。

如图9所示。

图8

图9

若是感到显示英文不大习惯，也可改成中文，只要把D3中的代码改成：

=IF（EXACT（B3,C3）,"

一致"

有改动"

），结果如图10所示，是不是更人性化一些。

图10

扩展知识：

增加空格也认为是改动（图11），这种情如何处理？

解决方法如下：

把D3的代码改成：

=IF（EXACT（B10,SUBSTITUTE（C10,"

"

"

））,"

）（图12），这里用SUBSTITUTE把空格替换掉而不用TRIM函数压缩，因为TRIM压缩后会留下一个空格，达不到预期效果。

图11

图12

技巧四Vlookup函数可以轻松实现清单套价一步到位！

Vlookup函数是一个功能十分强大的函数，他能帮助大家从一大堆错综复杂的数据中查询并提取你所需要的数据。

下面举一个例子，方便大家认识并了解它。

首先，先来看下VLOOKUP的最基础用法，总共只有4个参数，分别是：

用谁去找、匹配对象范围、返回第几列、匹配方式（0表示精确匹配，1表示模糊匹配）。

VLOOKUP的基础单条件用法是简单的一种用法，使用单个检索关键字，并且检索关键字在选择区域的第1列，直接使用普通公示就可以解决。

总结一下基础查询公式的用法就是：

=VLOOKUP（用谁找,去哪里找,找到了返回什么,怎么着）。

施工单位在进行投标报价时，有很多单体工程分不同的清单表格进行报价，而其实大部分的清单项目是相同的，在完成第一个单体工程的投标报价后，再运用此函数在其他单体清单中，可达到了事半功倍。

又如某个变更签证要参照工程合同（工程量清单为计价合同）的相应清单项目进行套价，如图13所示：

图13

在单元格E16中输入公式“=VLOOKUP（B16,$B$4:

$E$12,4,0）”

这个公式有几个要点需要跟大家解释一下，是下面动态演示不能完全反映的，也是帮助大家理解这个函数，所以请大家务必仔细阅读。

VLOOKUP函数有四个参数：

第1个参数就是你指定的查找的内容或单元格引用。

如本例中的B16,“C35混凝土”。

本例中经济签证单B列的“项目名称”就是查找目标。

我们要根据经济签证单中“项目名称”在合同清单找到对应的单价。

指定了查找目标，如果没有说从哪里查找，EXCEL肯定会很为难。

所以下一步我们就要指定从哪个范围中进行查找。

VLOOKUP的第2个参数的解释是查询的范围，这第二个参数可以从一个单元格区域中查找，也可以从一个常量数组或内存数组中查找。

本例中要从表一中进行查找，那么范围我们要怎么指定呢?

这里也是极易出错的地方。

大家一定要注意，给定的第二个参数查找范围要符合以下条件才不会出错：

1、查找目标一定要在该区域的第一列。

本例中查找经济签证单的“项目名称”，那么“项目名称”所对应的合同清单的“项目名称”列，那么合同清单的“项目名称”列（列）一定要是查找区域的第一列。

象本例中，给定的区域要从第二列开始，即$B$4:

$E$12，而不能是$A$4:

$E$12。

因为查找的“项目名称”不在$A$4:

$E$12区域的第一列。

2、该区域中一定要包含要返回值所在的列，本例中要返回的值是“单价”。

“单价”列（合同清单的E列）一定要包括在这个范围内，即：

$B$4:

$E$12，如果写成$B$4:

$D$12就是错的。

需要注意的是选取范围的首列必须为第一个参数所对应的项目名称，还有在选取范围的时候需要用到“$”符号，该符号在Excel中是锁定单元格行或列的功能。

这么做是为了方便后期批量复制单元格的公式。

第3个参数的解释是查询数据在选取范围的第几列，如本例中要求查询返回的是C35混凝土的单位，在查询范围的第4列，所以在公式中写的是“4”。

这里一定要注意，列数不是在工作表中的列数（不是第4列），而是在查找范围区域的第几列。

第4个参数的解释是选择模糊查找或精确查找，我们要求是精确查找，所以写上0，若写上“1”，就是模糊查找。

写好E16单元格的公式后，即可复制到后续的单元格，一切OK，快捷方便，查询准确。

效果如图14所示

图14

技巧五用VLOOKUP查找部分匹配的单元格

例：

如图14所示，在C列和D列中的投标单位及报价有几百家，我们要在几百家投标单位中，找出“安徽升升建设工程公司”的投标报价，我们可以用技巧四的方法查找，但有时因输入数据误写入空格，导致查找失败，我们可以采用“*”通配符，进行模糊查找，效果比较理想。

也就是在AD7单元格中写入公式：

=VLOOKUP（"

*升升建设*"

C6:

D500,2,0）即可，如图15所示。

图15

图15在AD7单元格中写入公式：

D500,2,0）的自动查找方法，实际上是一种模糊查找。

图15中AD5和AF5以是如何自动查找出来的呢？

其中也不难。

AD5中代码是：

=IFERROR（VLOOKUP（1,B6:

D500,2,0）,"

），这段代码的意思是：

因为B列是排名，先在B6：

D500范围内找到“1”，然后取出对应的第二列的值，显然就是第一名的单位了。

AF5中的代码是：

D500,3,0）,"

），显然原理同上，找到第一名后，取第3列的值即可。

技巧六逆向查找

逆向查找跟普通的VLOOKUP查找存在什么差异，我们都知道检索关键字必须在查找区域的第1列，逆向查找的检索关键字不在查找区域的第1列，如图16所示,要求按身份证号查找员工的姓名和性别那该如何办呢?

图16

我们在E33中写入代码:

=VLOOKUP（D33,CHOOSE（{1,2},$D$25:

$D$29,$B$25:

$B$29）,2,0）,在F33中写入代码:

$B$29）,2,0）,一切OK!

如图17所示。

图17

E33中代码:

$B$29）,2,0）和F33中代码:

$B$29）,2,0）都引入了一个CHOOSE函数，这个函数在这里的作用是把D列和B列及C列和B列的位置换一下，使D列（身份证号）处于第一列，以范围VLOOKUP函数的要求。

CHOOSE本是一个根据单元格值判断取值函数，这里是利用了他的延伸功能，进行列位置的变换。

另外从E33的代码还可以看出，采用这种方法，可以跨列查询取值。

技巧七多条件查询

如图18所示

图18

若是我们要项目名称+单位查询单价，该如何写代码呢？

代码如图19所示：

=VLOOKUP（B16&

C16,IF（{1,0},$B$4:

$B$12&

$C$4:

$C$12,$E$4:

$E$12）,2,0）这里要重点注意一下代码IF（{1,0},$B$4:

$E$12），这代码的意思是把B4:

B12和C4:

C12组合成一列，并与查询关键字B16和C16的组合相匹配。

E4:

E12自成一列，作为查询结果取得列，正好符合了VLOOKUP的查询条件。

其中{1,0}参数的作用是保证了$B$4:

$C$12在首列，以便查询。

另外，特别提醒：

由于上面的代码是数组公式，公式完成输入后要使用数组三键Ctrl+Shift+Enter来返回运算结果！

否则出错。

写好了D16的代码，按下组合键Ctrl+Shift+Enter,然后，拖动复制即可。

使代码写成这样：

{=VLOOKUP（B16&

$E$12）,2,0）}

图19

技巧八多条件逆向查询

其实，通过上面的代码，我们也很容易推出逆向多条件查询的代码写法。

如在图19中，若是按数量+单位查询项目名称，怎么写代码？

代码如下：

=VLOOKUP（D22&

C22,IF（{1,0},$D$4:

$D$12&

$C$12,$B$4:

$B$12）,2,0）

图20

技巧九VSumif函数能轻松完成工程量指标汇总

大家在统计工程材料用量的各项技术指标时，往往先需要汇总各项材料的用量（如混凝土的总和），该项工作特别麻烦，还容易出错，有什么方法可以一步到位呢？

采用EXCEL的Sumif函数可以帮大家解决这个问题。

案例如图21所示：

图21

为了获得各项材料用量按材料名称分类（如混凝土、模板、井盖……）统计的结果，在D24单元格中写入以下代码：

=SUMIF（$B$4:

$B$14,"

*"

&

B24&

$D$4:

$D$14）

下面我们来解释下单元格D24的公式“=SUMIF（$B$4:

$D$14）”，以便理解后灵活应用。

Sumif（条件区域，求和条件，实际求和区域），第二个求和条件参数在第一个条件区域里。

本例公式中第一个参数“$B$4:

$B$14”和第三个参数“$D$4:

$D$14”必须保持单元格行数对应。

比如本案例中"

材料名称"

列的起始行数为4，"

数量"

列的起始行数也必须为4。

同样，"

列的结束行数为14，"

列的结束行数也必须为14。

这个规则，在错行统计时是个例外。

另外，这两个参数必须用”$”锁定单元格，这么做是为了方便后期批量复制单元格的时候分类汇总数据不会随粘贴单元格位置的不同而变化。

本例中的求和统计条件是"

，这里用到了连字符“&

”，其目的是在B24单元格值的前后分别加上“*”这个万能匹配符，使得求和统计条件成为：

*B24*，这样就能把材料出库记录表中所有含有“混凝土”的材料数量全部统计进来，无一遗漏。

这里的B24不能用“$”锁定，而是要让他随着不同的行而变化，正好达到我们写好一个公式后，拖拉复制即可。

由于分类统计表中不公要统计数量，还要统计金额，所以可以在E24单元格中写入如下公式：

$F$4:

$F$14），这里求和统计条件相同，只是统计的参数变成了金额，所以，只要把SUMIF的第三个参数由“$D$4:

$D$14”改成“$F$4:

$F$14”即可，因为金额在F列。

实际结果如图22所示：

图22

技巧十VSUMIF函数错列分类统计

如图23所示,我们要统计10月和11月材料出库的分类汇总,如何定忪式呢?

图23

方法1:

我们可以采用技巧九的方法,分月汇总再相加。

在D24中写入代码：

$D$14）+SUMIF（$I$4:

$I$14,"

$K$4:

$K$14）；

在E24中写入代码：

$F$14）+SUMIF（$I$4:

$M$4:

$M$14），然后拖动复制即可。

如图24所示。

图24

方法二：

我们也可以采用SUMIF的错列统计功能来实现，在D24中写入代码：

$M$14），然后采用拖动复制即可，如图25所示。

显而易见，方法二的代码比方法一要简单得多。

图25