當excel不夠用時,如何利用Access進行數據分析?

數據量太大,Excel拖不動怎麼辦?還有其他能取代Excel但易上手的工具麼?

這個問題真是撞到槍口上了,我有收到不少的類似提問,今天就來統一解答一下。

大數據量做報表或數據分析的方案

  • 百兆以上,幾十萬行excel的數據量:數據庫ACCESS+SQL

  • 數據若不是達到億萬級別,直接用BI工具分析

  • 再大,就不是爾等表哥表姐,數據分析師能解決的了。

鑑於大家都有一般日常都用Excel,本文將要主要講第一類方案,出一篇完整教程,通用且實用,用到的工具是ACCESS數據庫。

關於ACCESS,它Excel的同宗兄弟,同屬微軟Office一門,上手不難,一週搞定基本操作。

它可以解決做數據運營的小夥伴們的如下抱怨:

業務上要處理的Excel數據表格存儲量越來越大,超過50MB就慢如蝸牛,這時表格裡要是再多個IF、VLOOKUP函數什麼的,電腦就直接罷工了;要是遇到向下面這樣大小的Excel表格,服務器級別的電腦都吃不消,更別談進行數據處理和數據分析了。

當excel不夠用時,如何利用Access進行數據分析?

數據量超大的excel表格連打開都難

遇到上圖這麼大存儲量的Excel數據表格,卡是必然的,死不死機就看人品了。那遇到這樣的難題,有沒有能處理大存儲量文件,同時又操作簡單、容易上手的數據分析軟件呢?

答案是當然是:YES,而且還是Excel的同宗兄弟,同屬微軟Office派系的ACCESS。

以下內容將以運營中常見的一個分析項目為案例,力求讓做數據分析的小夥伴們對ACCESS有一個基本的瞭解,從而找到分析大批量數據的思路和方法。

下圖是本文使用ACCESS對原始表格進行數據分析的4大目標。

當excel不夠用時,如何利用Access進行數據分析?

這裡先就ACCESS的基本情況說兩句,然後用一個實際案例進行數據分析的實操。

一、ACCESS數據庫簡介

1.ACCESS和SQL語句的基本概念

SELECT first_name, last_name FROM employees WHERE age> =25

上面的例子很容易理解,我們不用關心這些僱員記錄從哪裡來,我們所需要的只是那些年齡大於等於25歲的僱員的數據(age> =25)。

2.ACCESS的優勢

ACCESS最明顯的好處在於,它可以在不用掌握很高深編程語言的條件下,處理Excel所不能承載的大存儲量的數據原始文件,速度奇快,且易學易用。

當excel不夠用時,如何利用Access進行數據分析?

3. ACCESS的常用語句

下表是ACCESS使用過程中常用的一些SQL語句,理解起來不算困難。

當excel不夠用時,如何利用Access進行數據分析?

ACCESS數據庫常用的SQL語句

要想學好數據分析工具,最重要的是用實際案例來調動各種零碎的工具使用知識點,在歷經完整的案例分析後,短時間內就可以掌握這些工具的操作方法。

簡單介紹完了ACCESS和SQL語句後,接下來開始ACCESS數據分析實操吧!

二、ACCESS數據分析實操

1.數據導入

下表是本文進行ACCESS數據分析的原始文件,數據量近230MB,Excel打開需等待好幾分鐘,而且得看電腦心情…出於商業保密的目的,本文將使用其中的部分數據進行分析實操,且做一定處理。

當excel不夠用時,如何利用Access進行數據分析?

後臺導出的原始數據

先將Excel中的文件導入ACCESS中,按下圖箭頭路徑所示:

當excel不夠用時,如何利用Access進行數據分析?

導入excel原始數據文件

按上述步驟操作後,自動生成主鍵(即ID),得到如下結果:

當excel不夠用時,如何利用Access進行數據分析?

Excel原始數據文件導入到ACCESS中

2.用戶下單時間段分析

進行下單時間段的分析,需要將用戶下單的時間轉化為小時“時點”,這裡使用的SQL語句是format,功能是對所選字段進行格式設定,語法為:

format(引用字段,"數據格式")

其中,“數據格式”在時間上一般選用H(小時)、D(天)、M(月)或Y(年)。

然後,再使用count函數,將UserID進行計數,得到的結果即是訂單量。

注意,使用format和count之後,需要使用“AS”將其定義為新的字段,這裡二者分別定義為“時段”和“訂單量”。

當excel不夠用時,如何利用Access進行數據分析?

下單時間段分析操作步驟

在“創建”裡新建一個“查詢設計”,點開右下角的“SQL”,然後在SQL會話框輸入如下語句:

SELECT format(下單時間,"h") AS 時段, count(UserID) AS 訂單量

FROM 元數據

GROUP BY format(下單時間,"h");

然後,點擊“設計”下的“運行”,得到如下結果:

當excel不夠用時,如何利用Access進行數據分析?

各個時段的訂單量分佈情況

3.付款區間訂單量分佈情況分析

計算付款區間需要用到一個比較牛X的函數--- Switch,它是按順序計算一系列的表達式,如果某一表達式成立,則返回其隨後的值。

語法:

SWITCH(條件1,結果1,條件2,結果2,條件3,結果3,…,條件N,結果N)

條件1、條件2、條件3:表示要計算的表達式,條件1成立的話,返回值結果1,條件2成立的話,返回值結果2,依次類推。

按照上述的方法,在“創建”裡新建一個“查詢設計”,點開右下角的“SQL”後,輸入如下語句:

SELECT userID, 付款額, switch(付款額<=10,"1~10元",

付款額<=20,"11~20元",

付款額<=50,"21~50元",

付款額<=80,"51~80元",

付款額<=150,"81~150元",

付款額>150,"151~220元")AS 消費區間

FROM 元數據;

點擊“運行”後,得到如下結果:

當excel不夠用時,如何利用Access進行數據分析?

用戶單次下單的消費金額所對應的消費區間

此時,關於消費區間的數據處理還未結束,因為這是每一條下單記錄的付款額所對應的消費區間。我們接下來要做的是類似於excel中數據透視表的做法,將消費區間放在第一列,從而對每個消費區間有多少訂單量進行統計。

所以呢,跟上面一樣,得新建一個查詢了,名稱改為“付款區間訂單量統計”。

這裡需要輸入的SQL語句是:

SELECT 消費區間, count(UserID) AS 訂單數量

FROM 付款區間

GROUP BY 消費區間;

點擊“運行”後,得到的結果顯示如下:

當excel不夠用時,如何利用Access進行數據分析?

各消費區間訂單量分佈情況

然後,將上述數據複製到Excel表格裡,製成如下的百分比扇形圖,可以直觀的分析出每個消費區間的訂單量佔比情況,進而看到整體的用戶消費水平如何,對這段時間內的運營進行合理評估。

當excel不夠用時,如何利用Access進行數據分析?

各消費區間訂單量佔比扇形圖

4.各區域訂單量、用戶數量及銷售額分析

(1)各區域用戶數量

這個就有點小麻煩了,用戶數量用“userID”的計數間接計算出來,但是由於絕大部分的用戶下單次數不小於2次,所以直接計數的話,得出來的結果就是訂單量了。鑑於此種情況,我們得換個思路,先做出一個不重複的用戶下單信息表,也就是每個用戶ID下單的頻次表。

新建一個“查詢設計”,命名為“用戶消費頻次”。在SQL對話框裡輸入如下語句:

SELECT UserID, COUNT(UserID) AS 消費次數, 區域

FROM 元數據

GROUP BY UserID, 區域;

點擊“運行”後,得到的結果顯示如下:

當excel不夠用時,如何利用Access進行數據分析?

用戶下單頻次表

這樣,我們就可以以這張用戶消費頻次表作為跳板,在再次新建的表裡計算出每個區域的用戶數量咯。

新建一個“查詢設計”,命名為“各區域用戶數”。在SQL對話框裡輸入如下語句:

SELECT 區域, count(UserID) AS 總用戶數

FROM 用戶消費頻次

GROUP BY 區域;

點擊“運行”後,得到的結果顯示如下:

當excel不夠用時,如何利用Access進行數據分析?

各區域用戶數量

(2)各區域訂單量、消費金額狀況

新建一個“查詢設計”,命名為“各區域訂單情況”。在SQL對話框裡輸入如下語句:

SELECT 區域, count(UserID) AS 訂單總數, sum(付款額) AS 總金額, avg(付款額)AS 平均消費金額

FROM 元數據

GROUP BY 區域;

點擊“運行”後,得到的結果顯示如下:

當excel不夠用時,如何利用Access進行數據分析?

各區域訂單量、消費金額狀況

再將上面的各區域用戶數量整合到這張表裡,就得到了關於這三個區域完整的運營情況概覽表。見下表:

當excel不夠用時,如何利用Access進行數據分析?

各區域運營情況概覽

5.用戶價值分析

這裡的用戶價值分析基於RFM模型,不過對其進行了進一步的完善,在原先“累計消費金額”的基礎上,引入了“最低消費金額”、“最高消費金額”和“平均消費金額”這三個指標,力求全面的反映消費者的購買力。

新建一個“查詢設計”,命名為“用戶消費情況”。在SQL對話框裡輸入如下語句:

SELECT userID, min(付款額) AS 最低消費金額,

max(付款額) AS 最高消費金額,

avg(付款額) AS 平均消費金額,

sum(付款額) AS 消費總金額,

count(付款額) AS 消費頻次,

datediff("d",max(下單日期),#2015-9-15#) AS 最近一次消費距離今天天數

FROM 元數據

GROUP BY userID;

點擊“運行”後,得到的結果顯示如下:

當excel不夠用時,如何利用Access進行數據分析?

用戶價值分析表

得到該表後,可以對其進行聚類分析,按照R、F、M這三個維度對用戶進行分類,詳情可參看“【數據運營實操】如何運用數據分析對某個試運營項目進行“無死角”的覆盤?”這篇文章。

最後,我們還可以得出這三個區域總的訂單情況和銷售金額情況:

新建一個“查詢設計”,命名為“各區域銷售總覽”。在SQL對話框裡輸入如下語句:

SELECT count(userID) AS 訂單總數,

sum(付款額) AS 付款總額,

avg(付款額) AS 平均訂單金額

FROM 元數據;

點擊“運行”後,得到的結果顯示如下:

當excel不夠用時,如何利用Access進行數據分析?

三個區域的銷售情況總覽

結語

由上面的案例可以看出,如果SQL語句用得稍微熟練的話,ACCESS處理數據不會比Excel遜色,而且處理大批量數據正是它的強項。


分享到:


相關文章: