EXCEL數(shù)據(jù)分析在公共自行車服務(wù)系統(tǒng)中的運(yùn)用

殷冬琴

(蘇州經(jīng)貿(mào)職業(yè)技術(shù)學(xué)院 江蘇蘇州 215009)

公共自行車服務(wù)系統(tǒng)是現(xiàn)代化科技產(chǎn)品，是智慧城市和綠色城市建設(shè)必不可缺的一部分。作為一種低碳、環(huán)保、節(jié)能、健康的出行方式，公共自行車已經(jīng)在全國許多城市迅速普及。作為城市交通的重要組成部分之一，公共自行車在短距離出行上具有公交、出租無法替代的優(yōu)勢，如體積小、方便靈活、可達(dá)性高和投資少的優(yōu)勢；無廢氣排放和噪音小的環(huán)保優(yōu)勢；對(duì)道路適應(yīng)性強(qiáng)以及遇交通堵塞時(shí)可利用空隙和支路靈活疏散的優(yōu)勢等。因此，對(duì)公共自行車服務(wù)系統(tǒng)的研究有重要意義。

在公共自行車服務(wù)系統(tǒng)中，自行車租賃的站點(diǎn)位置及各站點(diǎn)自行車鎖樁和自行車數(shù)量的配置，對(duì)系統(tǒng)的運(yùn)行效率與用戶的滿意度有重要影響。本文就某城市某個(gè)區(qū)181個(gè)站點(diǎn)一天內(nèi)公共自行車服務(wù)系統(tǒng)運(yùn)行情況，利用EXCEL強(qiáng)大的數(shù)據(jù)分析功能，根據(jù)提供的數(shù)據(jù)信息進(jìn)行匯總統(tǒng)計(jì)、分類統(tǒng)計(jì)、排序、數(shù)據(jù)透視，解決了以下四個(gè)問題：各站點(diǎn)一天中累計(jì)的借車頻次和還車頻次；每張借車卡累計(jì)借車次數(shù)的分布情況；自行車用車的借還車站點(diǎn)之間（非零）最短距離與最長距離；各站點(diǎn)的借車高峰時(shí)段和還車高峰時(shí)段等。并根據(jù)討論結(jié)果，對(duì)現(xiàn)有公共自行車服務(wù)系統(tǒng)進(jìn)行優(yōu)化，對(duì)存在的問題和隱患提出合理建議，以提高公共自行車服務(wù)系統(tǒng)運(yùn)行效率和用戶滿意度[1][2]。

一、各站點(diǎn)一天累計(jì)的借車頻次和還車頻次

在EXCEL中將每個(gè)站點(diǎn)的借車車站號(hào)進(jìn)行匯總，得到每個(gè)站點(diǎn)總的借車頻次，再利用排序功能，將累計(jì)借車次數(shù)按降序排序，選取前10個(gè)站點(diǎn)借車、還車頻次為例，統(tǒng)計(jì)結(jié)果如下：累計(jì)借車頻次最多的是42號(hào)站點(diǎn)，前10名由高到低依次為：42、56、63、19、69、49、33、47、101、64；累計(jì)還車頻次最多的是56號(hào)站點(diǎn)，前10名由高到低依次為：42、56、63、19、69、49、33、47、64、101。累計(jì)借車頻次和還車頻次最多的均是這10個(gè)站點(diǎn)，且前面8名都一樣，每個(gè)站點(diǎn)借還車頻次基本一致，可以推斷這10個(gè)站點(diǎn)人流量大，每天借還車數(shù)量穩(wěn)定，很可能是工業(yè)區(qū)、居民區(qū)、商務(wù)樓集中地等，有關(guān)部門可以適當(dāng)增加這些地方的車輛和鎖樁數(shù)量，以提高公共自行車服務(wù)系統(tǒng)運(yùn)行效率和用戶滿意度。

二、每張借車卡累計(jì)借車次數(shù)的分布情況

假設(shè)一個(gè)卡號(hào)對(duì)應(yīng)一個(gè)用戶。

在EXCEL中，先將所有借車卡按降序排序，再插入數(shù)據(jù)透視表，統(tǒng)計(jì)出一天內(nèi)每張借車卡累計(jì)借車的次數(shù)，結(jié)果顯示最多一張借車卡一天內(nèi)累計(jì)借車次數(shù)為16次，卡號(hào)為327011；最少1次。每張借車卡累計(jì)借車次數(shù)的分布情況如下：該區(qū)181個(gè)站點(diǎn)一天內(nèi)總共借車次數(shù)為16842次，其中借車次數(shù)為1-2次的借車卡數(shù)量占到七成以上，其余借車次數(shù)不到三成。其中借車次數(shù)為1次的借車卡數(shù)量最多，有6525張，占總借車次數(shù)的38.742%，借車次數(shù)為2次的借車卡有5531張，占總借車次數(shù)的32.841%。

三、自行車用車的借還車站點(diǎn)之間（非零）最短距離與最長距離

（一）兩站點(diǎn)間的用車時(shí)間

先將原始數(shù)據(jù)表格中用車時(shí)間的數(shù)據(jù)從文本格式轉(zhuǎn)換為數(shù)字格式，在數(shù)菜單先選擇分列功能選項(xiàng)即可。然后利用最大值MAX函數(shù)和最小值MIN函數(shù)統(tǒng)計(jì)出用車時(shí)間最小值為0分鐘，最大值為2587分鐘，再將整個(gè)用車時(shí)間以10分鐘為組距分成7組：[0,10], (10,20], (20,30], (30,40], (40,50] ,(50,60], 60以上。最后利用統(tǒng)計(jì)函數(shù)COUNTIF統(tǒng)計(jì)出各時(shí)間段的頻數(shù)和頻率，統(tǒng)計(jì)結(jié)果顯示：用車時(shí)間最多的分布在0至10分鐘之間，其中用車時(shí)間不超過30分鐘的占到90%以上，用車時(shí)間在30分鐘以上的不到10%，說明大部分用戶都是將公共自行車作為日常短途交通工具，主要用于近距離的上下班出行、辦事；只有小部分人可能將其作為鍛煉或游玩的工具。因此，應(yīng)繼續(xù)加大公共自行車綠色環(huán)保方面的宣傳，進(jìn)一步改善自行車性能以提高騎行舒適度，從而讓更多人選擇公共自行車綠色出行，保護(hù)環(huán)境。

（二）兩站點(diǎn)間的距離

先進(jìn)行以下合理的假設(shè)：

假設(shè)1：每個(gè)人騎車行駛時(shí)速度相同，時(shí)間差是由于行駛過程中有中斷行駛的時(shí)間長短引起的。

假設(shè)2：借車車站號(hào)與還車車站號(hào)相同時(shí)，距離記為0。

假設(shè)3：道路都是雙向通行，兩個(gè)站點(diǎn)間往返的距離相等。

下面我們分別用兩站點(diǎn)間用車時(shí)長的最小值和平均值兩種方法來定義兩站點(diǎn)間的距離（非零），統(tǒng)計(jì)結(jié)果以前面10個(gè)站點(diǎn)間距離為例顯示。

1.兩站點(diǎn)間用車時(shí)長最小值

先將原始數(shù)據(jù)表格中借車車站號(hào)、還車車站號(hào)、用車時(shí)長的數(shù)據(jù)按前面的方法從文本格式轉(zhuǎn)換為數(shù)字格式，再將數(shù)據(jù)進(jìn)行有條件升序排序，排序主要關(guān)鍵詞“借車車站號(hào)”，次要關(guān)鍵詞“還車車站號(hào)”，然后利用IF函數(shù)將借車車站號(hào)與還車車站號(hào)相同的距離改為0，最后對(duì)數(shù)據(jù)建立數(shù)據(jù)透視表，行標(biāo)簽為借車車站號(hào)，列標(biāo)簽為還車車站號(hào)，數(shù)值為用車時(shí)長，統(tǒng)計(jì)方式為最小值。統(tǒng)計(jì)結(jié)果顯示：在1-10號(hào)站點(diǎn)中，距離最近的兩個(gè)站點(diǎn)是站點(diǎn)6和8，最短距離為3分鐘；距離最近的兩個(gè)站點(diǎn)是站點(diǎn)1和2，最長距離為32分鐘；部分站點(diǎn)間距離沒有顯示值，如：站點(diǎn)1和站點(diǎn)4、5、7、9之間，站點(diǎn)2和站點(diǎn)4、5、9、10之間等，很大可能是這兩個(gè)站點(diǎn)距離遠(yuǎn)，超出正常騎行范圍，也不排除這一路段維修等特殊情況。

2.兩站點(diǎn)間用車時(shí)長平均值

根據(jù)上面的討論可知，用車時(shí)長大于60分鐘的僅占1.13%，這部分?jǐn)?shù)據(jù)存在很多不確定因素，不具有代表性。而利用兩站點(diǎn)間用車時(shí)長的平均值將受到這些數(shù)據(jù)影響，于是考慮將用車時(shí)長大于60分鐘的數(shù)據(jù)剔除，然后再計(jì)算兩站點(diǎn)間用車時(shí)長的均值作為站點(diǎn)間的距離，方法同1。先將原始數(shù)據(jù)表格中借車車站號(hào)、還車車站號(hào)、用車時(shí)長的數(shù)據(jù)按前面的方法從文本格式轉(zhuǎn)換為數(shù)字格式，再將數(shù)據(jù)進(jìn)行有條件升序排序，排序主要關(guān)鍵詞“借車車站號(hào)”，次要關(guān)鍵詞“還車車站號(hào)”，然后利用IF函數(shù)將借車車站號(hào)與還車車站號(hào)相同的距離改為0，最后對(duì)數(shù)據(jù)建立數(shù)據(jù)透視表，行標(biāo)簽為借車車站號(hào)，列標(biāo)簽為還車車站號(hào)，數(shù)值為用車時(shí)長，統(tǒng)計(jì)方式為平均值即可。統(tǒng)計(jì)結(jié)果顯示：在1-10號(hào)站點(diǎn)中，距離最近的兩個(gè)站點(diǎn)是站點(diǎn)6和8，最短距離為4分鐘；距離最近的兩個(gè)站點(diǎn)是站點(diǎn)1和2，最長距離為35分鐘。比較兩站點(diǎn)間距離的兩種計(jì)算方法，不難看出兩者關(guān)于兩站點(diǎn)間距離的算法結(jié)果基本一致，但是站點(diǎn)3和站點(diǎn)6之間的距離，在方法1中僅為5分鐘，在方法2中卻為32分鐘，出現(xiàn)這種結(jié)果的原因可能是此條線路途徑為著名風(fēng)景區(qū)或出現(xiàn)了突發(fā)事件等，從而造成平均值大幅度增加。

四、各站點(diǎn)的借車高峰時(shí)段和還車高峰時(shí)段

在原始數(shù)據(jù)中，借車時(shí)刻主要集中在5:59-21:00，還車時(shí)刻主要集中在6:00-21:00，下面統(tǒng)計(jì)時(shí)間為6:00-21:00。利用EXCEL數(shù)據(jù)提取功能，輸入“text(G2,‘h’)”和“text(K2,‘h’)”將借車時(shí)刻和還車時(shí)刻中的時(shí)間提取出來。以一小時(shí)作為組距來統(tǒng)計(jì)各站點(diǎn)的借車高峰時(shí)段和還車高峰時(shí)段，如：6:00-6:59均記為時(shí)刻6。分別對(duì)借車時(shí)刻和還車時(shí)刻進(jìn)行分類匯總，結(jié)果如下：借車、還車高峰時(shí)段都是7-8、8-9、16-17、17-18，這些時(shí)段均是上下班高峰期，其中借車、還車最頻繁的是17-18時(shí)，下面就以這一時(shí)段為例，統(tǒng)計(jì)出該時(shí)段內(nèi)各站點(diǎn)的借還車情況（取前10名數(shù)據(jù)）。統(tǒng)計(jì)結(jié)果顯示：17-18時(shí)借車頻數(shù)由高到低的10個(gè)站依次為：56、63、42、57、19、47、54、32、71、126，還車頻數(shù)由高到低的10個(gè)站依次為：63、56、19、118、101、47、26、39、49、62。其中63、56、47、19既是借車頻數(shù)高的站點(diǎn)，又是還車頻數(shù)高的站點(diǎn)，56號(hào)站點(diǎn)號(hào)借車頻數(shù)高于還車頻數(shù)，可適當(dāng)增加車輛，63站點(diǎn)還車頻率遠(yuǎn)高于借車頻率，可適當(dāng)減少車輛，47、19號(hào)站點(diǎn)借還車頻數(shù)基本一致，可維持原車輛數(shù)目。另外，從表8也可推斷出42、57、54、32、71、126號(hào)站點(diǎn)可適當(dāng)增加車輛，而118、101、26、39、49、62號(hào)車站可適當(dāng)減少車輛。