Excel全攻略

用Excel制作工程计量支付证书

在工程监理中，工程的计量支付是监理工程师的一项重要工作。除大型工程采用专用计量支付软件外，在一般的工程中还大都采用人工计算。利用Excel快捷的表格、绘图功能，强大的计算能力，能够方便的制作出准确、规范的工程计量支付证书。下面，我们就通过一个工程实例来介绍如何用Excel制作工程计量支付证书。 一、组成支付证书的基本表格 1.工程进度表 2.中期支付证书 3.清单支付报表 4.中间计量表

5.中间计量支付汇总表 6.工程变更一览表

7.扣回材料设备预付款一览表 8.扣回动员预付款一览表

用Excel制作支付证书，是把上述表格作为Excel工作簿的工作表，工作表的名字取支付表相同的名字，便于相互引用其有关单元格的数据进行自动计算。在制作某一期支付证书时，只要将上一期的支付证书复制、改名、修改有关数据，即可作为本期的支付证书，无需再重新制表。为简单起见，这里只重点介绍表1至表5中有关单元格之间是如何互相引用，最终制作出所要求的工程计量支付证书。表格本身的绘制可参照附图制作。 二、中间计量表

《中间计量表》是计量支付的基础表格，它是承包人申请支付的依据，其目的是提供支付的数量基础。该表一般由施工单位提供，监理工程师进行审核，并将审核的结果直接填入《中间计量支付汇总表》。但为了资料的清楚、准确，监理工程师可以将审核后的《中间计量表》重新编制(如图1)，其“工程量”的数据(如单元格D17)将被《中间计量支付汇总表》直接引用。

表中的计算草图可根据施工图或实际情况绘制。用Excel中的绘图工具，选择各种图形组合而成。图形画好

1

后要单击“选择对象”，将所画图形全部选中，再单击“组合”。

计算式一栏中可列出计算过程，也可利用Excel列表计算。计算结果放在单元格D17中。 三、中间计量支付汇总表

《中间计量支付汇总表》是对中间计量表的汇总，起计量证书的作用。《中间计量支付汇总表》比较简单，主要是将《中间计量表》中的“工程量”汇总，计算出合计金额(如图2)。

根据合同文件，在表中输入有关项目的编号、名称、单位、单价等，将《中间计量表》中的“工程量”链接到本表中“数量”一栏的相关位置。 操作步骤：

1.单击单元格E8，按下编辑公式按钮“=”，再单击《中间计量表3》中的单元格D17，确定，这时，《中间计量表3》中单元格D17的数据将链接到《中间计量支付汇总表》的单元格E8。其它《中间计量表》的“工程量”也用同样方法链接到《中间计量支付汇总表》的相应单元格中。

2.单击单元格G6，按下编辑公式按钮“=”，单击单元格E6，然后键入乘号“*”，再单击单元格F6，确定。 3.选中单元格区域G6至G16，选择“编辑”菜单中的“填充→向下填充”，完成各项目的“金额”计算。 4.单击单元格G20，按下编辑公式按钮“=”，输入SUM(G6：G16)，算出“金额合计”。 四、清单支付报表

《清单支付报表》是对合同文件中的工程量清单完成情况的全面反映(如图3)。根据合同文件，在表中输入有关项目的编号、内容、单位；在“合同工程量”一栏中输入“原合同工程量”、“单价”和“变更后工程量”。 在本表中，“本期末完成”=“到上期末完成”+“本期完成”；“本期完成金额”=“本期完成工程量”*“单价”。 操作步骤(以清单支付3为例)：

1.单击单元格L7，按下编辑公式按钮“=”，单击单元格K7，然后键入乘号“*”，再单击单元格E7，确定；单击单元格G7，按下编辑公式按钮“=”，

单击单元格K7，然后键入加号“+”，再单击单元格I7，确定；单击单元格H7，按下编辑公式按钮“=”，单击单元格J7，然后键入加号“+”，再单击单元格L7，确定；单击单元格M7，按下编辑公式按钮“=”，单击单元格G7，然后

2

键入除号“/”，单击单元格F7，再键入乘号“*”及100，确定。

2.选中单元格区域G7至G31，选择“编辑”菜单中的“填充→向下填充”，将完成“到本期末完成工程量”各项目的工程量计算。用同样方法操作“到本期末完成的金额”、“本期完成金额”及“本期末累计完成%”的有关单元格。 3.在“本期完成工程量”一栏中，单击相应单元格，如单元格K19，按下编辑公式按钮“=”，然后转到《中间计量支付汇总表》，单击单元格E8，确定。用同样的方法将《中间计量支付汇总表》中的“工程量”全部链接到相关《清单支付报表》对应的单元格中。

4.单击单元格H6，按下编辑公式按钮“=”，输入SUM(H7：H31)，计算“到本期末完成金额”的金额合计；用同样方法计算“到上期末完成金额”及“本期完成金额”的金额合计。

5.由于每一期的支付证书都是通过上一期的支付证书拷贝、修改得来的，所以录入“到上期末完成工程量及金额”时，只要将上一期支付证书的“到本期末完成”的有关单元格数值拷贝到“到上期末完成”的相应单元格位置，然后删除“本期完成工程量”的全部数值即可。具体操作是：选中单元格区域G7至H31，选择“编辑”菜单中的“复制”，再选中单元格区域I7至J31，选择“编辑”菜单中的“选择性粘贴”，选中“数值”后确定。最后删除“本期完成工程量”中的全部数值。

6.其它“清单支付报表”也用同样方法制作。 五、中期支付证书

《中期支付证书》是向业主提供工程完成情况，并作为向业主结算工程价款的财务依据。

根据合同文件，在表中输入项目的清单号、项目名称、原有总金额(如图4)。在表中，“到本期末完成合同金额”=“到上期末完成合同金额”+“本期完成合同金额”。“保留金”每期扣“本期完成合同金额合计”的10%，但累计不能超过“原有总金额合计”的5%。“动员预付款”按计划逐月扣回。 操作步骤：

1.单击单元格F6，按下编辑公式按钮“=”，

单击单元格G6，然后键入加号“+”，再单击单元格H6，按“确定”；选中单元格区域F6至F12，选择“编辑”菜单中的“填充→向下填充”，完成各项目“到本期末完成合同金额”的计算。

3

2.单击“本期完成合同金额”的相应单元格，如单元格H8，按下编辑公式按钮“=”，然后转到相应的《清单支付报表》，如“清单支付(3)”，单击单元格L6，确定，完成数据的链接。用同样的方法将其它《清单支付报表》中的项目全部链接到《中期支付证书》的相应单元格中。

3.单击单元格I6，按下编辑公式按钮“=”，单击单元格F6，然后键入除号“/”，再单击单元格C6，确定；选中单元格区域I6至I12，选择“编辑”菜单中的“填充→向下填充”，完成“本期末累计完成金额占总价%”的计算。 4.单击单元格H14，按下编辑公式按钮“=”，输入SUM(H6：H12)，计算出“本期完成合同金额小计”。用同样方法计算各有关项目的“小计”。

5.本期完成“扣回动员预付款”，可从《扣回预付款一览表》相关单元格链接。

6.每次扣“保留金”是“本期完成合同金额”的10%，既单元格H21设置为“=(-0.1)*H18”，但扣除的“保留金”累计不能超过“合同价”的5%。

7.采用上一期的支付证书拷贝、修改的具体操作是：选中单元格区域F6至F12，选择“编辑”菜单中的“复制”，再选中单元格区域G6至G12，选择“编辑”菜单中的“选择性粘贴”，选中“数值”后确定。最后删除“本期完成合同金额”中的相应数值。

8.单击单元格H22，按下编辑公式按钮“=”，输入“H18+H20+H21”，计算出“本期完成合同金额的支付”，这是本期工程的计量支付金额。用同样的方法设置单元格F22和单元格G22。 六、工程进度表

《工程进度表》主要用来反映工程项目的进度计划与实际完成的情况。 根据合同文件，在表中输入项目的清单号、项目名称、合同金额等(如图5)。 操作步骤：

1.单击单元格D12，按下编辑公式按钮“=”，单击单元格C12，然后键入除号“/”，再单击单元格C23，再键入乘号“*”号及100；用同样方法定义单元格D14、D16、D18、D20和D22。

2.单击单元格E12，按下编辑公式按钮“=”，单击“中期支付证书”的单元格F6，然后键入除号“/”，单击本表单元格C12，再键入乘号“”号及100，确定。

4

3.单击单元格F12，按下编辑公式按钮“=”，单击“中期支付证书”的单元格F6，然后键入除号“/”，单击本表单元格C23，再键入乘号“”号及100，确定。 4.用同样方法设置其它相关单元格。

5.单击单元格J24，按下编辑公式按钮“=”，单击“中期支付证书”的单元格F18，然后键入除号“/”，单击“中期支付证书”的单元格C18，再键入乘号“*”号及100，确定。

6.单击单元格J25，按下编辑公式按钮“=”，单击“中期支付证书”的单元格H18，然后键入除号“/”，单击“中期支付证书”的单元格C18，再键入乘号“*”号及100，确定。

7.工程形象进度图的绘制比较简单，采用Excel绘图工具栏的“直线”工具，以时间(月)为横坐标，以“实际累计完成的百分数”为纵坐标，逐月绘出线段，连接而成。(计划进度图未画)。

Excel 2000斜线表头轻松做

为了符合中国人的制表习惯，Word 2000中文版中加入了“绘制斜线表头”的功能，使得我们在制表时可以方便地在表格中插入斜线表头。但遗憾的是在Excel 2000中却没有这项功能，以致在Excel2000表格中插入表头时不得不用直线工具来绘制，这样做一来很麻烦，二来文字的位置也不易调整。其实我们完全可以利用Word2000中的绘制斜线表头功能在Excel 2000中轻松插入斜线表头。具体做法如下： 1.在Excel 2000中制作好一张表格并留出放置斜线表头的位置 。 2.在Word 2000中绘制斜线表头

创建一个新文档，并使用“插入表格”工具插入一个二行二列的表格。选中表格中左上角的单元格，单击“表格”菜单，在弹出的下拉菜单中单击“绘制斜线表头”命令，弹出“绘制斜线表头”对话框，该对话框共提供了五种斜线表头的样式，根据需要选择一种样式，并设置适当的字号，然后在行标题栏中分别填入各栏的标题，完成后单击“确定”按钮，则“插入斜线表头”对话被关闭，在被选中的单元格中插入了一个斜线表头 。

其实在Word 2000中制作的斜线表头是预先制作好的一个斜线表头框架，用鼠标点击后会在四周出现控点。用鼠标选中这个斜线表头框架，将它复制到“剪贴板”上。

5

要使单元格内的文字竖排，可以在其输入前(或后)选中这些单元格。打开“单元格格式→对齐”选项卡，将“方向”下竖排的“文本”选中，然后“确定”即可。 14.常数引用命名

为常数指定名称可以方便使用。如果某个工作表用2.1%计算利息，可以将它指定为常数“LX”。具体方法是：单击“插入→名称”子菜单下的“定义”命令打开对话框，在“当前工作薄的名字”框内输入“LX”，在“引用位置”框中输入“＝0.02.1”，“确定”之后即可使用该名称引用2.1%了。

Excel XP数据分析应用指南

Excel之所以强大，是因为其具有完整的数据分析函数和图表功能，让我们对数据进行归纳后，能够利用这些函数和图表对数据进行分析。本文以教育、金融和财务应用为例，介绍Excel XP数据分析的主要手段和操作方法。 一、数据的排序与筛选

排序与筛选是数据分析的基本手段。下面以图1所示的“学籍管理表”为例，介绍数据排序与筛选的操作方法。 1.数据排序 (1)数据清单：

Excel排序和筛选的操作对象是“数据清单”。它是在工作表首行的单元格内输入列标记(相当于数据库中的字段，可以作为关键字参与各种操作)，在列标记以下各行依次输入数据(相当于数据库是的一条条记录)而构成的表。其基本结构如图1所示，与Access数据表基本相同。 (2)常规排序：

Excel数据清单中的列标记可以作为关键字参与排序，具体操作方法有以下两种：如果你需要根据“年龄”、“总分”等数值关键字(字段)对数据清单排序，只须将图1中的“年龄”或“总分”选中，然后点击工具栏中的“升序排序”或“降序排序”按钮，整个数据清单就会根据该关键字重新排列。

如果你需要按姓氏笔划对图1数据清单排序，则应选中图1中B1单元格，点击“数据→排序”菜单命令，在打开的对话框中点击“选项”按钮打开“排序选项”对话框。选中“笔画排序”选项，根据图1中的数据排列方向选择“按列排序”选项，点击“确定”按钮后返回到“排序”对话框。将其中的“有标题行”选中，然后打开“主要关键字”下拉列表，选

46

择其中的“姓名”选面，最后选中排序方式(“升序”或“降序”)并确定，数据清单就会根据姓名的笔划重新排列。 如果数据清单排序依据多个关键字，你只要打开“排序”对话框，在“次要关键字”和“第三关键字”下拉列表中选择即可。

(3)自定义排序：

如果你要求图1中的数据按照“广东省”、“云南省”、“四川省”、“山西省”和“河北省”的顺序排列，前面介绍的两种方法就无能为力了，只能采取自定义排序规则的方法处理。

首先你要建立一个自定义序列：点击“工具→选项”菜单命令，打开“选项”对话框中的“自定义序列”选项卡。选中左边“自定义序列”下的“新序列”选项，此时光标就会在右边的“输入序列”框内闪动，你可以输入“广东省”、“云南省”等自定义序列，输入的每个序列之间要用英文逗号分隔，或者每输入一个序列就敲一次回车。完成后点击“添加”按钮，将自定义序列添加到“自定义序列”框内备用。

自定义序列排序的方法与笔划排序很相似，你只要打开“排序选项”对话框中的“自定义排序次序”下拉列表，选中前面定义好的序列，其他选项保持不变。回到“排序”对话框后根据需要选择“升序”或“降序”，“确定”后即可完成数据清单的排序。

注意：假如数据按自定义序列的顺序排列，就应选中“排序”对话框中的“升序”排列，否则应该选择“降序”排列。 2.数据检索

从数据结构的角度看，图1所示“数据清单”就是一个小型数据库，其中的每一条记录占用一个表格行。对此，可以采用下面的方法检索数据：

点击“数据→记录单”菜单命令，在打开的对话框中点击“条件”按钮。对话框中的字段就会变成空白等待输入，而且“条件”按钮转变为“表单”按钮。你可以在对话框的相应字段中输入条件，例如在“姓名”字段框内输入“李小丫”回车，则姓名是“李小丫”的数据就会显示在对话框中。当然，你输入的检索条件可以使用>、<、>=、<=、<>等逻辑符号。例如点击“条件”按钮后在“总分”框内输入“>500”，表示检索“总分”大于500分的所有记录，回车后“新建”按钮上方就会显示“1/6”字样，说明6条记录中的第一条符合条件。点击“上一条”或“下一条”按钮，可以查看检索出来的其他记录，并显示“4/6”、“5/6”字样。

47

3.自动筛选

上面介绍的方法可以逐条查看检索出来的记录。如果你要批量查看符合条件的所有记录，仅靠“记录单”进行检索就不能满足你的需要。此时可以使用Excel的自动筛选功能，具体操作方法是：点击“数据→筛选→自动筛选”菜单命令，数据清单的列标记(字段)右侧会显示一个下拉按钮。如果你需要将总分大于500分的记录全部筛选出来，可以点击“总分”下拉按钮，选择“自定义”选项打开“自定义自动筛选方式”对话框。点击“总分”下面第一行右侧的下拉按钮，选择“大于”选项，然后在其右边的框内输入“500”，选中两行中间的“与”后确定，工作表就会显示所有符合筛选条件的记录。

当然，筛选出来的记录还可以再次筛选。假如你要将总分大于500分中“性别”为“女”的记录筛选出来。可以按相同方法打开“性别”下拉列表选择“女”，则“张晓菲”的记录就会显示在工作表中。

自动筛选出来的数据可供进一步分析，也可以打印或复制到其他工作表。如果你要清除筛选结果，点击“数据→筛选→自动筛选”菜单命令即可。另外，执行数据自动筛选时，如果“自定义自动筛选方式”对话框中的两个条件需要同时满足，则应选中“与”选项，否则应当选中“或”选项。 4.高级筛选

虽然“自动筛选”操作简单，但是可供使用的筛选条件有限。为此，Excel提供了“高级筛选”功能。它能够使用各种条件对数据清单进行筛选，其功能强大和使用灵活远非“自动筛选”可比。 (1)条件区域：

使用高级筛选必须在工作表中构造区域，它由条件标记和条件值构成。条件标记和数据清单的列标记相同，可以从数据清单中直接复制过来；条件值则须根据筛选需要在条件标记下方构造，是执行高级筛选的关键部分。 构造高级筛选的条件区域需要注意：如果条件区域放在数据清单的下方，那么两者之间应至少有一个空白行。如果条件区域放在数据清单的上方，则数据清单和条件区域之间也应剩余一个或几个空白行(一般不要这样设计，这样影响其他功能使用数据清单)。 (2)单列多条件：

如果某一个条件标记下面输入了两个或多个筛选条件，我们将其称为单列多条件，你只要在条件标记下自上而下

48

依次输入筛选条件即可。例如你需要列出图1中年龄大于17和年龄小于17的学生名单，只需在图1条件标记“年龄”的下方D10和D11单元格输入“>17”和“<17”即可。 (3)多列单条件：

多列单条件是指筛选条件由多个条件标记构成，但每个条件标记下面只有一个条件。如果你要列出图1中“性别”为“女”，且“语文”成绩大于等于85的学生，可以在图1条件区域的C10单元格内输入“女”，在G10单元格内输入“>=85”即可。

(4)多行单条件：

构造高级筛选条件有这样的要求：如果多个筛选条件需要同时满足，它们必须分布于条件区域的同一行，这就是所谓的“与”条件，否则筛选条件必须分布于条件区域的不同行，也就是“或”条件。假如你要在图1中找出“性别”是“男”，或“语文”成绩“>=80”，或“化学”成绩“>=90”的所有记录。可以在图1条件区域的C10单元格内输入“男”，然后在G11单元格内输入“>=80”，最后在J12单元格内输入“>=90”即可。 (5)两列两组条件：

如果你需要在图1中寻找物理成绩大于等于90的男生或者物理成绩大于等于80的女生，可以按照如下方法构造条件区域。C10、C11单元格内分别输入“男”和“女”，接着在H10、H11单元格内分别输入“>=90”和“>=80”即可。 (6)执行筛选操作：

数据清单和条件区域建立完毕，你就可以执行数据筛选任务了，具体操作方法是：点击“数据→筛选→高级筛选”菜单命令，在打开的对话框中首先选择筛选结果的显示方式，若选择“在原有区域显示筛选结果”，则像自动筛选那样在数据清单中显示结果；否则就要在“复制到”框内指定筛选结果的显示位置。接着点击“数据区域”框，选中数据清单所在的区域，使该区域的绝对引用进入框内；再用相同方法完成对“条件区域”的绝对引用。点击“确定”按钮，就会在你选择的位置显示筛选结果。

如果你选择“在原有区域显示筛选结果”，则筛选完成后只能看到结果而看不到原来的数据清单。此时点击“数据→筛选→全部显示”菜单命令，即可恢复原来的数据清单。 二、数据的分析与求解

49

Excel提供了多种数据分析手段，从函数、分析工具库、加载宏等，一直到数据透视表和数据透视图。下面介绍以函数和加载宏为主的若干分析工具。 1.成绩分布频率分析

学生成绩分析的一项重要任务，就是统计各分数段中的人数，为研究成绩分布提供基础数据。下面以图1为例，说明如何计算550～500、500～450、450～400、400～350，以及小于350分数段内的人数。

首先在图1中的M2：M6区域依次输入550、500、450、400、350，表示统计上述分数段内的成绩个数。当然，你可以根据数据分析的具体要求，选择其他方式划分数据分段方法。接着在M2:M6区域右侧预留相同大小的单元格区域(N2：N6)，作为存放数据统计结果的位置。

上述工作完成后，选中存放统计结果的区域(N2：N6)，在编辑栏内输入公式“=FREQUENCY(L2:L7，M2:M6)，最后让光标停留在公式的末尾。按下Shift+Ctrl键敲回车，编辑栏内将显示“{=FREQUENCY(L2:L6，M2:M6)}”(大括号表示这是一个数组公式)，N2：N6区域就会显示各分数段中的成绩个数。

注意：数组公式“{=FREQUENCY(B2:B6，C2:C6)}”可以对一组或多组数值进行多重计算，并得出一个或多个计算结果。数组公式中的大括号“{

}”不能用手工输入，只能按组合键Shift+Ctrl+Enter自动生成。 2.对象相关性分析

教育教学研究的任务之一是了解各学科之间的相互关系，例如数学的学习是否对物理成绩有影响。而商品销售领域，也需要分析两组数据(单位可以不同)之间是否相关。例如空调和冷饮的销售量与气温之间的关系，或者商场的客流人数与销售额是否相关等等。

这里仍然以图1所示的“学籍管理表”为例，我们的任务是分析这些学生的物理成绩是否与数学成绩相关。分析的操作方法是：选中数据清单中的一个空白单元格，在编辑栏内输入公式“=CORREL(F2:F7，I2:I7)”，回车即可得到数学和物理成绩的相关系数。这个计算结果有以下三种情况：如果物理成绩中的高(低)分与数学成绩中的高(低)分对应，说明这两个数据集合是正相关。计算结果应该是一个小于1的实数，它越接近于1，说明两者相关性越强；如果物理成绩中的低(高)分与数学成绩中的高(低)分对应，说明这两个数据集合是负相关。计算结果应该是接近-1的实数，它

50

本文来源：https://www.bwwdw.com/article/sxyd.html