统计汽车油耗

统计汽车油耗

 

此段文字节选自《Excel 2007函数与公式应用大全》一书

现在的有车一族越来越多，而车辆的油耗是有车一族最关心的问题之一。下面的例子讲解了如何统计和计算汽车的油耗。

14.5.1  从零开始

首先，要创建一个原始数据记录表格来记录与汽车油耗相关的数据（见光盘文件CDROM/14/统计汽车油耗.xls）。如图14-62所示。

在上面的表格中，我们需要公里表里程来计算汽车的行使距离，加油量和单价来计算加油的消费。接下来就输入一些加油的数据来填充表格。如图14-63所示。

在上面的表格中，D2单元格使用公式“=B3*C3”来计算消费额下面的顺序填充，F2单元格使用公式“=(A3-A2)/B3”来计算单次加油的百公里油耗，下面的顺序填充。

14.5.2  分析数据

当录入了一些数据之后，就可以进行时间区域的统计了。首先打开Sheet2工作表，建立一个表格来描述需要统计的条目。如图14-64所示。

首先来计算总体油耗统计表格。

 总加油量。首先来明确一个约定，在原始数据表中的第一行记录为起始值，它只起到一个初始化作用，该行的记录不被计算在内。在明确这个约定之后，很容易就写出该条目的公式：

=SUM(Sheet1!B:B)-Sheet1!B2

计算整个B列的值之和，然后减去B2初始值。

 总行使里程。由于我们记录的是公里表的数字，所以取出最大值，与初始值相减就可以了：

=MAX(Sheet1!A:A)-Sheet1!A2

 平均油耗。这个也非常简单，用总行使里程除以总加油量即可：

=C3/C2

 加油总消费。计算原始记录表中D列的和，然后减去D2的值就可以了：

=SUM(Sheet1!D:D)-Sheet1!D2

 加油次数。使用COUNT函数计算原始数据表中B列的个数，并减去1（除去原始记录）：

=COUNT(Sheet1!B:B)-1

到此，总体油耗统计表格就制作完毕了。如图14-65所示。

接下来，制作日期段油耗统计表格。因为这个表格设计到日期段内的计算，所以这个表的计算公式会有一定的难度。

 总加油量。要想在日期段内计算总加油量，可以使用SUMIF函数来计算：

=IF(C9<=Sheet1!E2,SUMIFS(Sheet1!B:B,Sheet1!E:E,">="&C9,Sheet1!E:E,"<="&E9)- Sheet1!B2,SUMIFS(Sheet1!B:B,Sheet1!E:E,">="&C9,Sheet1!E:E,"<="&E9))

在上面的公式里，">="&C9参数的目的是组合出一个字符串，来表示大于等于C9单元格中的日期。例如，C9中的内容为“2005-1-1”算后的结果是“>=2005-1-1”。由于SUMIF接受的条件参数为字符串或单元格引用，当需要一个可变字符串时，就要使用“&”运算符来连接字符串和单元格引用。最外面的IF函数的作用是，当第一条记录包含在日期区间内，则减去第一个记录的值，否则返回原值。

 总行使里程。这个函数是最复杂的，由于在原始记录中的信息并不是行使里程，而是公里表的数值，导致这个函数有些复杂。不过理顺思路之后发现还是有办法解决的。

首先，计算这个值的方法是用小于上限日期的最大日期对应的公里数减去大于下限的最小日期值对应记录的上一个记录的公里数。

其次，要取出最大日期对应的公里数，在此使用公式：

=LOOKUP(Sheet2!E9,Sheet1!E:E,Sheet1!A:A)

接着是算出最小日期对应记录的上一次记录的公里数，在此使用公式：

=MAX(INDIRECT("Sheet1!A2:A"&(COUNTIF(Sheet1!E:E,"<"&C9)+1)))

这个公式的意思是：先计算小于最小日期的记录有几个，在此假设为n个，然后再从A2到A（n－1）单元格中选出最大的值。也就是最小日期对应记录的上一次记录的公里数。

最后，把两个公式计算的结果相减就完成了，公式为：

=LOOKUP(Sheet2!E9,Sheet1!E:E,Sheet1!A:A)-(MAX(INDIRECT ("Sheet1!A2:A"&(COUNTIF(Sheet1!E:E,"<"&C9)+1))))

 平均油耗。这次与总体油耗表中的方法一样，用总行使里程除以总加油量即可。

=C3/C2

 加油总消费。由于我们有每次加油的消费额记录，所以使用SUMIFS函数可以方便的计算。

=IF(C9<=Sheet1!E2,SUMIFS(Sheet1!D:D,Sheet1!E:E,">="&C9,Sheet1!E:E,"<="&E9)- Sheet1!D2,SUMIFS(Sheet1!D:D,Sheet1!E:E,">="&C9,Sheet1!E:E,"<="&E9))

在此公式中，计算在日期区间内的所有消费额的和，如果日期区间中包括第一条记录，则减去第一条记录中的值。

 加油次数。使用COUNTIFS函数来计算：

=IF(C9<=Sheet1!E2,COUNTIFS(Sheet1!E:E,">="&C9,Sheet1!E:E, "<="&E9)-1,COUNTIFS(Sheet1!E:E,">="&C9,Sheet1!E:E,"<="&E9))

上面的公式意思是，计算在日期内的日期的个数（同时也就是记录的个数），如果日期区间包含第一条记录，则在计算结果中减1。

到此，日期段内的油耗统计表格便制作完成了。如图14-66所示。

最后，我们需要一张图来描述这几次加油时，汽车的百公里油耗的趋势。

选择“插入”选项卡中的“图表>折线图>二维折线图”命令，如图14-67所示。

随后，表格区域中会出现一个空白的折线图框。选择“图表工具>设计”选项卡中的“数据>选择数据”命令，为折线图选择数据。如图14-68所示。

在弹出的选择数据原对话框中修改图表数据区域文本框，改为“=Sheet1!$F$3:$F$7”。单击确定退出。如图14-69所示。

于是，折线图便绘制完毕。如图14-70所示。

下面，我们要在绘制的折线图中添加趋势线。选中折线图中的折线，单击鼠标右键在弹出的菜单中选择“添加趋势线”命令。如图14-71所示。

在弹出的“设置趋势线格式”对话框中，选择“趋势预测/回归分析类型”的“线性”选项，并勾选“显示公式”复选框。然后单击关闭退出。如图14-72所示。

随后，一条趋势直线便显示出来。如图14-73所示。

在图14‑73显示的公式可以看出，这条直线的斜率是-1.135，这说明汽车的油耗下降的速度比较快。

14.5.3  总结

对数据进行记录，并在合适的时候进行统计和分析是抓住事物规律的一种方法。在上面的例子中，针对记录数据的内容的设计主要的原则是让记录方便，不容易出错。尤其是“公里表里程”数据项体现了这个原则。其实完全可以为了计算方便而改为统计车辆在两次加油中行使的里程。不过这样一来，则增加了记录数据时的复杂性，我们需要进行一次计算才可以向表格中录入数据。在本例中，宁可增加统计计算的复杂度，也不增加数据录入的复杂度。

 

预购地址：http://www.china-pub.com/956543

 【书名】Excel 2007 函数与公式应用大全-(与Excel97-2003版本兼容)(含光盘1张

 【作者】Excel研究组 编著
    【ISBN】9787121057779 
    【出版社】电子工业出版社
    【出版日期】2008年3月
    【宣传语】科学的学习方法

         人性化的讲解

         多种索引方式

         超值的售后服务

         盘书结合