EXCEL数据分析在公共自行车服务系统中的运用

殷冬琴

(苏州经贸职业技术学院 江苏苏州 215009)

公共自行车服务系统是现代化科技产品，是智慧城市和绿色城市建设必不可缺的一部分。作为一种低碳、环保、节能、健康的出行方式，公共自行车已经在全国许多城市迅速普及。作为城市交通的重要组成部分之一，公共自行车在短距离出行上具有公交、出租无法替代的优势，如体积小、方便灵活、可达性高和投资少的优势；无废气排放和噪音小的环保优势；对道路适应性强以及遇交通堵塞时可利用空隙和支路灵活疏散的优势等。因此，对公共自行车服务系统的研究有重要意义。

在公共自行车服务系统中，自行车租赁的站点位置及各站点自行车锁桩和自行车数量的配置，对系统的运行效率与用户的满意度有重要影响。本文就某城市某个区181个站点一天内公共自行车服务系统运行情况，利用EXCEL强大的数据分析功能，根据提供的数据信息进行汇总统计、分类统计、排序、数据透视，解决了以下四个问题：各站点一天中累计的借车频次和还车频次；每张借车卡累计借车次数的分布情况；自行车用车的借还车站点之间（非零）最短距离与最长距离；各站点的借车高峰时段和还车高峰时段等。并根据讨论结果，对现有公共自行车服务系统进行优化，对存在的问题和隐患提出合理建议，以提高公共自行车服务系统运行效率和用户满意度[1][2]。

一、各站点一天累计的借车频次和还车频次

在EXCEL中将每个站点的借车车站号进行汇总，得到每个站点总的借车频次，再利用排序功能，将累计借车次数按降序排序，选取前10个站点借车、还车频次为例，统计结果如下：累计借车频次最多的是42号站点，前10名由高到低依次为：42、56、63、19、69、49、33、47、101、64；累计还车频次最多的是56号站点，前10名由高到低依次为：42、56、63、19、69、49、33、47、64、101。累计借车频次和还车频次最多的均是这10个站点，且前面8名都一样，每个站点借还车频次基本一致，可以推断这10个站点人流量大，每天借还车数量稳定，很可能是工业区、居民区、商务楼集中地等，有关部门可以适当增加这些地方的车辆和锁桩数量，以提高公共自行车服务系统运行效率和用户满意度。

二、每张借车卡累计借车次数的分布情况

假设一个卡号对应一个用户。

在EXCEL中，先将所有借车卡按降序排序，再插入数据透视表，统计出一天内每张借车卡累计借车的次数，结果显示最多一张借车卡一天内累计借车次数为16次，卡号为327011；最少1次。每张借车卡累计借车次数的分布情况如下：该区181个站点一天内总共借车次数为16842次，其中借车次数为1-2次的借车卡数量占到七成以上，其余借车次数不到三成。其中借车次数为1次的借车卡数量最多，有6525张，占总借车次数的38.742%，借车次数为2次的借车卡有5531张，占总借车次数的32.841%。

三、自行车用车的借还车站点之间（非零）最短距离与最长距离

（一）两站点间的用车时间

先将原始数据表格中用车时间的数据从文本格式转换为数字格式，在数菜单先选择分列功能选项即可。然后利用最大值MAX函数和最小值MIN函数统计出用车时间最小值为0分钟，最大值为2587分钟，再将整个用车时间以10分钟为组距分成7组：[0,10], (10,20], (20,30], (30,40], (40,50] ,(50,60], 60以上。最后利用统计函数COUNTIF统计出各时间段的频数和频率，统计结果显示：用车时间最多的分布在0至10分钟之间，其中用车时间不超过30分钟的占到90%以上，用车时间在30分钟以上的不到10%，说明大部分用户都是将公共自行车作为日常短途交通工具，主要用于近距离的上下班出行、办事；只有小部分人可能将其作为锻炼或游玩的工具。因此，应继续加大公共自行车绿色环保方面的宣传，进一步改善自行车性能以提高骑行舒适度，从而让更多人选择公共自行车绿色出行，保护环境。

（二）两站点间的距离

先进行以下合理的假设：

假设1：每个人骑车行驶时速度相同，时间差是由于行驶过程中有中断行驶的时间长短引起的。

假设2：借车车站号与还车车站号相同时，距离记为0。

假设3：道路都是双向通行，两个站点间往返的距离相等。

下面我们分别用两站点间用车时长的最小值和平均值两种方法来定义两站点间的距离（非零），统计结果以前面10个站点间距离为例显示。

1.两站点间用车时长最小值

先将原始数据表格中借车车站号、还车车站号、用车时长的数据按前面的方法从文本格式转换为数字格式，再将数据进行有条件升序排序，排序主要关键词“借车车站号”，次要关键词“还车车站号”，然后利用IF函数将借车车站号与还车车站号相同的距离改为0，最后对数据建立数据透视表，行标签为借车车站号，列标签为还车车站号，数值为用车时长，统计方式为最小值。统计结果显示：在1-10号站点中，距离最近的两个站点是站点6和8，最短距离为3分钟；距离最近的两个站点是站点1和2，最长距离为32分钟；部分站点间距离没有显示值，如：站点1和站点4、5、7、9之间，站点2和站点4、5、9、10之间等，很大可能是这两个站点距离远，超出正常骑行范围，也不排除这一路段维修等特殊情况。

2.两站点间用车时长平均值

根据上面的讨论可知，用车时长大于60分钟的仅占1.13%，这部分数据存在很多不确定因素，不具有代表性。而利用两站点间用车时长的平均值将受到这些数据影响，于是考虑将用车时长大于60分钟的数据剔除，然后再计算两站点间用车时长的均值作为站点间的距离，方法同1。先将原始数据表格中借车车站号、还车车站号、用车时长的数据按前面的方法从文本格式转换为数字格式，再将数据进行有条件升序排序，排序主要关键词“借车车站号”，次要关键词“还车车站号”，然后利用IF函数将借车车站号与还车车站号相同的距离改为0，最后对数据建立数据透视表，行标签为借车车站号，列标签为还车车站号，数值为用车时长，统计方式为平均值即可。统计结果显示：在1-10号站点中，距离最近的两个站点是站点6和8，最短距离为4分钟；距离最近的两个站点是站点1和2，最长距离为35分钟。比较两站点间距离的两种计算方法，不难看出两者关于两站点间距离的算法结果基本一致，但是站点3和站点6之间的距离，在方法1中仅为5分钟，在方法2中却为32分钟，出现这种结果的原因可能是此条线路途径为著名风景区或出现了突发事件等，从而造成平均值大幅度增加。

四、各站点的借车高峰时段和还车高峰时段

在原始数据中，借车时刻主要集中在5:59-21:00，还车时刻主要集中在6:00-21:00，下面统计时间为6:00-21:00。利用EXCEL数据提取功能，输入“text(G2,‘h’)”和“text(K2,‘h’)”将借车时刻和还车时刻中的时间提取出来。以一小时作为组距来统计各站点的借车高峰时段和还车高峰时段，如：6:00-6:59均记为时刻6。分别对借车时刻和还车时刻进行分类汇总，结果如下：借车、还车高峰时段都是7-8、8-9、16-17、17-18，这些时段均是上下班高峰期，其中借车、还车最频繁的是17-18时，下面就以这一时段为例，统计出该时段内各站点的借还车情况（取前10名数据）。统计结果显示：17-18时借车频数由高到低的10个站依次为：56、63、42、57、19、47、54、32、71、126，还车频数由高到低的10个站依次为：63、56、19、118、101、47、26、39、49、62。其中63、56、47、19既是借车频数高的站点，又是还车频数高的站点，56号站点号借车频数高于还车频数，可适当增加车辆，63站点还车频率远高于借车频率，可适当减少车辆，47、19号站点借还车频数基本一致，可维持原车辆数目。另外，从表8也可推断出42、57、54、32、71、126号站点可适当增加车辆，而118、101、26、39、49、62号车站可适当减少车辆。