用好函數　Excel數據分析有門道

平淡

一、了解RFMRFM

是三個指標單詞首字母的縮寫，分別指Rec ency（最近一次消費距離今天的時間，該值越小越好）、Frequency（某段統計時間內的消費次數，值越大越好）和Monetary（某段統計時間內的累計消費金額，值越大越好）。

二、求取R、F、M值

通過上面的描述可以知道，R FM的三個指標都是可以量化的數字，而且使用Excel內置的函數就可以輕易地完成統計。如下表是某公司3月份銷售統計報表，下面根據這些數據來分析RFM（圖1）。

1R值求取

最近一次消費距離今天的時間，使用“訂購日期-today（）”即可實現，不過由于客戶消費有重復的場景，需要提取其最近一次的消費數據。為了方便操作，新建工作表2，接著將原數據復制到表2的A：D列。定位到D2單元格，輸入公式“=TODAY（）-C2”并下拉填充得到各客戶最近一次消費距離今天的時間值。然后依次點擊“數據→排序”，以D列為基準執行升序排序（圖2）。

接著復制A：D列區域數據到I：L區域，依次點擊“數據→刪除重復值”，在打開的窗口中勾選“客戶名稱”列選項，點擊“確定”，獲取到了不重復名稱的客戶數據（圖3）。

選中I：K數據區域，依次點擊“數據→排序”，排序條件為“客戶名稱”，將其按照升序方式排列，這樣就可以獲得所有客戶的最新R值數據了（圖4）。

2F值求取

F是客戶截止到今天的消費次數，也就是客戶姓名在B列重復出現的統計數字。定位到M2單元格，輸入公式“=COUNTIF（$B$2：$B$34， I2）”并下拉完成統計（圖5）。

公式解釋：

客戶重復消費后姓名會在B列出現，這里將$B$2：$B$3 4作為統計區域，統計條件為I2顯示的名稱（及客戶姓名），通過COUNTIF函數完成重復消費次數的統計。

3M值求取

M是客戶在統計報表中累計的消費金額，定位到M2單元格，輸入公式“=SUMIF（$B：$B，I2，$D：$D）”并下拉填充完成統計（圖6）。

公式解釋：

$B：$B作為提供邏輯判斷依據的單元格區域，I2單元格名稱作為求和判斷條件，$D：$D作為求和條件區域，通過SUMIF函數可以統計每個客戶的累計金額。

三、對R、F、M值分類

完成對R、F、M值的求取后，就可以根據得到的數據來分類，比如公司的分類標準是，指標值如果低于平均值標記為0，大于等于平均值則標記為1。這個使用IF函數來判斷，定位到O2單元格，輸入公式“=IF（L2并下拉填充，同樣在O2單元格、P2單元格分別輸入公式“=IF（L2

四、依據R、F、M分類組合值判斷客戶

RFM分析法目的是衡量客戶價值和客戶創利能力，這個評判標準通過O、P、Q三個值的組合對客戶分類，具體的分類標準可以在S2：T9區域先行輸入，這樣在R列就可以引用這個標準來對客戶自動完成分類標注。定位到R2單元格并輸入公式“=VLOOKUP（O2&P2&Q2，$S$2：$T$9，2，0）”，下拉填充后就可以完成操作了（圖8）。

公式解釋：

先使用“&”字符將O2：Q2區域連接起來作為一個整體函數的查找值，查找區域則為$S$2：$T$9（即上述制定的客戶分級標準，注意絕對引用），查找結果所在的列為$S$2：$T$9的第2列（即客戶類型），匹配類型為“0”（精確查找）。這樣函數在下拉時就可以根據O2：Q2區域組合值，在K列將客戶的類型標注出來了。

當然，為了讓數據分析更直觀，大家還可以使用插入圖表的方式來統計客戶。具體操作可以自行嘗試。