用好函數(shù)　Excel數(shù)據(jù)分析有門道

平淡

一、了解RFMRFM

是三個(gè)指標(biāo)單詞首字母的縮寫，分別指Rec ency（最近一次消費(fèi)距離今天的時(shí)間，該值越小越好）、Frequency（某段統(tǒng)計(jì)時(shí)間內(nèi)的消費(fèi)次數(shù)，值越大越好）和Monetary（某段統(tǒng)計(jì)時(shí)間內(nèi)的累計(jì)消費(fèi)金額，值越大越好）。

二、求取R、F、M值

通過上面的描述可以知道，R FM的三個(gè)指標(biāo)都是可以量化的數(shù)字，而且使用Excel內(nèi)置的函數(shù)就可以輕易地完成統(tǒng)計(jì)。如下表是某公司3月份銷售統(tǒng)計(jì)報(bào)表，下面根據(jù)這些數(shù)據(jù)來分析RFM（圖1）。

1R值求取

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

接著復(fù)制A：D列區(qū)域數(shù)據(jù)到I：L區(qū)域，依次點(diǎn)擊“數(shù)據(jù)→刪除重復(fù)值”，在打開的窗口中勾選“客戶名稱”列選項(xiàng)，點(diǎn)擊“確定”，獲取到了不重復(fù)名稱的客戶數(shù)據(jù)（圖3）。

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

2F值求取

F是客戶截止到今天的消費(fèi)次數(shù)，也就是客戶姓名在B列重復(fù)出現(xiàn)的統(tǒng)計(jì)數(shù)字。定位到M2單元格，輸入公式“=COUNTIF（$B$2：$B$34， I2）”并下拉完成統(tǒng)計(jì)（圖5）。

公式解釋：

客戶重復(fù)消費(fèi)后姓名會(huì)在B列出現(xiàn)，這里將$B$2：$B$3 4作為統(tǒng)計(jì)區(qū)域，統(tǒng)計(jì)條件為I2顯示的名稱（及客戶姓名），通過COUNTIF函數(shù)完成重復(fù)消費(fèi)次數(shù)的統(tǒng)計(jì)。

3M值求取

M是客戶在統(tǒng)計(jì)報(bào)表中累計(jì)的消費(fèi)金額，定位到M2單元格，輸入公式“=SUMIF（$B：$B，I2，$D：$D）”并下拉填充完成統(tǒng)計(jì)（圖6）。

公式解釋：

$B：$B作為提供邏輯判斷依據(jù)的單元格區(qū)域，I2單元格名稱作為求和判斷條件，$D：$D作為求和條件區(qū)域，通過SUMIF函數(shù)可以統(tǒng)計(jì)每個(gè)客戶的累計(jì)金額。

三、對R、F、M值分類

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

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

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

公式解釋：

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

當(dāng)然，為了讓數(shù)據(jù)分析更直觀，大家還可以使用插入圖表的方式來統(tǒng)計(jì)客戶。具體操作可以自行嘗試。