2021年6月16日 近期為了驗證課程測試題栅表，深入了解了下 Excel 數(shù)據(jù)分析的功能谓晌，找到一些有趣的技巧笤妙，記錄一下。
滿足條件的單元格的位置淹父，及其引用
在評估創(chuàng)業(yè)點子的盈利性(Profitability evaluation)株婴，繪制上面的表格(考慮TVM - Time Value of Money)，算是基本的操作暑认，即得到連續(xù)幾年預(yù)估的收益情況困介。其中，投資回收期(Payback period)的計算蘸际，是評估盈利性的指標(biāo)之一座哩，也就是看在開始后未來某個時間點收回投資的時間點。
以前粮彤，都是眼睛看出來第5年中的某個點根穷，然后手工完成計算：
這次就想能否自動計算姜骡，也就需要知道
- "Cumulative Value" 列中有多少行的數(shù)值低于投資 - 應(yīng)該是4
- 根據(jù)前面的數(shù)值，取得相應(yīng)單元格的數(shù)據(jù) - E5和D6屿良，也就是
E(4+1)和D(4+2)圈澈。
為簡便，將"Cumulative Value" 列中有多少行的數(shù)值低于投資單獨設(shè)了一個單元格 - 即payback-period上面的4尘惧，表達(dá)式就是=COUNTIF(E2:E6,"< "&(A8)&" ")康栈。其中，A8是投資額的單元格喷橙。
那么啥么，payback-period右側(cè)的單元格的計算公式就是：=COUNTIF(E2:E6,"< "&(A8)&" ")+(A8-INDIRECT("E"&(M9+1)))/INDIRECT("D"&(M9+2))。其中的INDIRECT("E"&(M9+1))和INDIRECT("D"&(M9+2))也就是對E5和D6單元格的引用贰逾。
求解置信區(qū)間中的邊界值 - NORMINV()悬荣，FINV()，``
在課程中梳理了統(tǒng)計學(xué)(基礎(chǔ)部分)似踱，其實也就是圍繞4個分布計算給定置信度的置信區(qū)間，進(jìn)而進(jìn)行推斷的過程稽煤。比如核芽，下面的圖就展示了所謂的ANOVA (Analysis of Variance: 方差分析)的計算，最后就要借助F分布來計算置信區(qū)間的邊界值 - 即CI對應(yīng)的單元格酵熙。
CI單元格的計算公式是=FINV(C6,C5-1,C4-C5)轧简，其中的FINV()就是計算F分布置信區(qū)間邊界值的函數(shù)。在得到置信區(qū)間邊界值后匾二，因為樣本數(shù)據(jù)得到的F值(5.398)大于邊界值(是SPE事件 - Small Probability Event)哮独，意味著我們有理由相信不同設(shè)計方案對銷售沒有影響的假設(shè)有可能是有問題的。
其他3個分布的置信區(qū)間邊界值函數(shù)如下：
- 正態(tài)分布 -
= NORMINV()和= NORMSINV() - 學(xué)生分布 -
= TINV() - 卡方分布 -
= CHINV()
當(dāng)然察藐，也有直接得到置信區(qū)間的函數(shù) - = CONFIDENCE() -
計算值固定某些單元格皮璧，按某列的條件加另外一列，以及動態(tài)展示單元格的顏色 - KMeans算法展示
計算值固定某些單元格
為驗證 K-means聚類算法分飞，就需要計算數(shù)據(jù)到固定點的距離悴务。如上圖中B和C列展示了5條數(shù)據(jù)，初始的質(zhì)心是 M1和 M2譬猫，就需要計算那5條數(shù)據(jù)到 M1讯檐、M2的距離 - Dist-M1和Dist-M2。自然希望完成了一條數(shù)據(jù)的距離計算染服，直接拖拽來完成其他數(shù)據(jù)的距離計算别洪，這就需要M1和M2的數(shù)據(jù)在計算中不能因為拖拽而改變。
I3單元格保存了數(shù)據(jù)A到M1的距離柳刮，計算公式為 =SQRT(POWER(B3-$F$3,2)+POWER(C3-$G$3,2))挖垛，其中的$F$3和$G$3就是固定了的痒钝，當(dāng)拖拽I3來計算I列其他數(shù)據(jù)到M1的距離時，$F$3和$G$3都是固定的晕换，沒有被 $符號包圍的就隨之改變了午乓，也就完成了其他數(shù)據(jù)到M1的距離計算。
動態(tài)展示單元格的顏色
另外一個就是希望能夠根據(jù)數(shù)據(jù)距離M1和M2的遠(yuǎn)近自動以不同的顏色顯示類別歸屬信息闸准，即上圖中K和N兩列 - 兩種顏色是根據(jù)距離的差異自動顯示的益愈。為此，需要使用Excel的條件格式
- 選中一個單元格夷家，點擊條件格式蒸其，點擊 新建規(guī)則
如上圖進(jìn)行設(shè)定，然后拖拽刷新該列其他的單元格库快。
要注意摸袁，以上只是設(shè)定了一個顏色 - <= 0。
按照同樣的步驟义屏，再建一個格式規(guī)則 (此次是 > 0)靠汁，并指定不同的顏色，再次刷新其他的單元格闽铐，顏色就自動出現(xiàn)了蝶怔。
其他列類似處理即可。
按某列的條件加另外一列
在K-means計算中兄墅，按照質(zhì)心更新類別后踢星，還需要計算新的質(zhì)心，也就需要借助更新后的類別來計算新質(zhì)心的坐標(biāo)隙咸。
如上圖沐悦，在初始的M1和M2聚類后，K列展示了按照初始M1和M2的聚類五督，那么藏否，下一個循環(huán)就需要按照更新的聚類信息計算新的質(zhì)心。I12單元格計算了新的M1的X坐標(biāo)充包，計算公式為 =SUMIF(K3:K9,"<=0",B3:B9)/COUNTIF(K3:K9,"<=0")秕岛。其中的SUMIF(K3:K9,"<=0",B3:B9)就是按照 K3:K9列中滿足 "<=0"的行，將B3:B9中對應(yīng)的行上的單元格數(shù)據(jù)相加误证。效果就是基于A和B兩條數(shù)據(jù)計算新M1的坐標(biāo) - 因為按照初始的M1和M2值继薛， A和B離初始的M1更近。
Excel自帶的數(shù)據(jù)分析工具
