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號車站可適當減少車輛。