一個Excel公式搞定獨立性分析

不管是分析男女差異、調薪幅度跟績效有沒有關聯、不同話題引起的討論度是否不一樣，都可以利用Excel中的CHITEST函數來實現。 

舉例來說, 某家冰淇淋店想知道性別會不會影響對冰淇淋口味的選擇,

於是它們統計了最近1個月顧客的消費數據:

理論上, 如果男女對冰淇淋口味都沒什麼特別偏好的話,

消費數據應該是這樣子(兩種口味各50%):

因為在性別與冰淇淋口味間沒有關係的條件下,
理論購買人數的算法如下:

整個獨立性分析最複雜的計算就這樣子了,
計算完上述結果後CHITEST函式就可以登場了,

→ CHITEST(actual_range, expected_range) = χ2

CHITEST的輸入變數有兩個, 分別為:

     1. Actual Range : 實際上的分布

     2. Expected Range : 理論上的分布

回傳的值則是在1與2分布一樣的假設之下, 

有多少機率出現這樣子的分布, 也就是所謂的p value.

把圖一、圖二的分布代進CHITEST後我們得到2%的數字,

也就是說在這樣的分布下, 其實有2%的機率性別不影響冰淇淋口味的選擇.

一般我們把5%當作一個顯著與否的分界點,

The House Advantage(莊家優勢)的作者Jeffrey

也是以5%的機率門檻檢驗旗下的撲克算牌手有沒有黑吃黑的嫌疑,

在這樣的水準下我們可以說性別確實顯著影響冰淇淋的口味選擇.

動腦時間:

某公司將網頁改版後得到了新顧客數據,

舊數據與新數據分別如下, 在5%的門檻下, 

新版網頁的顧客購買率(購買人數/進站人數)究竟有沒有上升呢?

輕鬆用VBA做網路爬蟲（中）之 字串處理

字串處理(String Parsing)簡單說就是將一堆文字(包括符號、數字等)清理、整理後擷取我們需要的部份；以“輕鬆用VBA做網路爬蟲（上）” 最後的小挑戰做例子的話，可以(跟面試官)說我們是在開發處理玩股網報價的演算法。

開發一個網路爬蟲時，大概有八成以上的時間都在做字串處理，

在這篇文章中我會用網路爬蟲“上篇”中最後的小挑戰作為例子，

再用一個線上的字庫讓大家練習一下。

在上一篇文章中我們抓下來的原始資料長這樣：

200.32▲3.861.96%

108.47▲2.011.89%

134.03▲2.41.82%

130.71▲2.341.82%

113.09▲1.861.67%

82.59▲1.161.42%

208.67▲2.581.25%

160.88▲1.941.22%

484.47▲5.691.19%

380.09▲4.31.14%

263.25▲2.921.12%

首先，我們在被證明錯誤前先找找其中自以為的規律：「

1. 指數報價一定都到小數點後兩位；

2. 指數報價後會跟著上漲▲或下跌▼的符號，平盤時沒有符號(某天看見的)；

3. 當天漲跌(有負號)一定都到小數點兩位，平盤時為0(某天看見的)；

4. 不論當天漲跌(有負號)，漲跌幅一定到小數點後兩位(eg. x.xx%)。」

因此，萃取指數報價的方式就是：「

找到第一個"."的位置(假設為n)，然後抓第1位～第n+2位，

也就是 Left( 原始資料, inStr( 1, 原始資料, "." )+2 )。」

再來，萃取指數漲跌的方法稍微複雜點：「

【先看原始資料中是否有那兩個符號，有的話：「

首先，我們知道指數報價的結尾在n+2處，

因此我們新創一個從n+3開始的資料叫做"改動資料"，

改動資料= Right( 原始資料, (len( 原始資料) -(n+2)) )，

所以，我們一樣找改動資料中第一個"."的位置(假設為m)，然後抓第1位～第m+2位就行了，

也就是 Left( 改動資料, inStr( 1, 改動資料, "." )+2 )。」

如果找不到▲或▼的符號，那當日漲跌就是0了。】」

最後是當日漲跌幅：「

如果當日漲跌不為0，那麼改動資料的第m+3位～最後一位就是漲跌幅了，

也就是Right( 改動資料, (len( 改動資料) -(m+2)) )。

當日漲跌為0的話，漲跌幅自然也為0( 0.00%)了。」

========================================================

以下是另一個字串處理+網路爬蟲的例子

========================================================

這是一個古登堡計畫下的字庫網頁(A開頭)：
http://www.mso.anu.edu.au/~ralph/OPTED/v003/wb1913_a.html

我們要嘗試著計算出A開頭字庫中各種詞性(part of speech)佔的數目，

原始碼大致上長這樣子：「

<P><B>Abacinate</B> (<I>v. t.</I>) To blind by a red-hot metal plate held before the eyes.</P>

<P><B>Abacination</B> (<I>n.</I>) The act of abacinating.</P>

 <P><B>Abstaining</B> (<I>p. pr. & vb. n.</I>) of Abstain</P>  」

單字被<B>標籤包住、詞性則被<I>標籤包住，

除此之外一個單字可能會有多個詞性（在<I>標籤裡面被&符號分開）。

（註：實作後才發現有極少數的多詞性會被“ /” 以及“ ,” 符號分開）

爬蟲的前置除了網址不一樣外，其它的與上篇文章都一樣，

以下是抓取詞性的code：「

圖1:

圖2:

圖3:

理論上，運行完後我們得到的資料大概會長這樣(部份)：

Part of Speech	numbers
prep.	70
n.	5444
a.	2861
v.t.	1088
adv.	574
pl.	239
p.p.	317
imp.	287
p.pr.	285
vb.n.	285
v.	67
v.i.	278
n.pl.	171
superl.	10
i.	44
p.a.	7
a	2
interj.	15
p.p.Adored(/);p.pr.	1
a.superl.	2
Aprefix.	3
conj.	26

網路爬蟲中，字串處理的部份大致上就是這個樣子。

以下是範例檔案（附code）：

https://goo.gl/uyMn5l   」

字串處理是一門相當重要的功夫，

無論是做資料分析、語意分析、或甚至要暴力破解某個密碼都會用到它，

而有些問題只有在實作後才會被發現，

建議大家多多找出生活、工作中可以被自動化的工作，試著動手開發。

有任何問題歡迎來信或者留言，

如果想自動化一件工作卻毫無頭緒也可以來信我們討論看看，

IT'S   ALL   FREE !!

輕鬆用Excel函式做蒙地卡羅分析（上）

蒙地卡羅分析又被稱作統計模擬方法，是一種利用電腦強大計算能力產生大量模擬資料以供分析的方法。

但是在什麼情況之下會用到它呢？

下面先來個不會用到它的簡單例子（先備知識），
【假設長期聘請顧問給予公司經營上的建議，
據信可以讓每個月的利潤較聘請顧問前增加4%～8%。】
某公司目前每個月的利潤是500萬元，
(顧問費用/每月)應該要控制在多少以下，才能確信這八成是個賺錢的交易？

這樣的問題不需要用到蒙地卡羅分析，我們來小試身手：
『Step1:
每個月利潤增長率為4%～8%(換算利潤為20萬~40萬)，
我們可以簡單估算出增長率的標準差大約為1.2%(換算利潤為60,790元)，
(常態分佈中，數值有90%的機會坐落在±1.645個標準差內，
故對於大部份的資料區間，我們可以直接除以(1.645*2)來得到估計的標準差。)

Step2:
接下來我們要找出下圖的X點（"八成"是個賺錢的交易），

Excel中有個函式可以解決這個問題，
叫做NORM.INV(累積機率, 平均值, 標準差)，
它可以給出在特定累積機率下，常態分佈曲線上的值，
在這個例子上即是NORM.INV(20%, 300000, 60790)，
可以得出248838元這個數字，
也就是說聘請顧問後所增加的利潤有80%以上的機率大於248,838元，
因此若能控制顧問成本在一個月248,838元以下，則這八成就是筆賺錢的交易了。』

注：
與NORM.INV成雙成對的是NORM.DIST函數，
詳細為NORM.DIST(某值, 平均值, 標準差, 是否為累積機率)，
它會給出某值在常態分配中的累積機率。
舉例來說：
『NORM.DIST(248838 ,300000 ,60790 ,TRUE)的值就是近似於20%了』

下一篇文章中，我們會討論更多相關的函數，
以及它們在蒙地卡羅分析法中的應用。

用簡單的函式做出排行榜

開始實作前先介紹幾個會用到&常用到的函式。

SMALL函式簡介
SMALL函式的架構為: SMALL(要尋找的範圍，排名)，
舉例來說，SMALL(A1:C3,3)這個函數會呈現A1到C3當中排名第3小的數字。

LARGE函式簡介
LARGE函式的架構為: LARGE(要尋找的範圍，排名)，
舉例來說，LARGE(A1:C3,1)這個函數會呈現A1到C3當中最大的數字。

OFFSET函式簡介
OFFSET函式的架構為: OFFSET(標的欄位，欄數，列數)，
OFFSET(H6, 3, -2)這個函數會show出位於H6下面3欄、左邊2列的格子，也就是F9。

MATCH函式簡介
MATCH函式的架構為: MATCH(目標的值，要尋找的欄或列，搜尋方式)，
若搜尋方式打0的話，該函式會show出目標值位於要尋找的"欄"或"列"中的第幾個位置。

範例實作

上圖左側是台灣上市櫃公司7/4的部份資料，

只要每次有所更新，透過函式，右側的表格也會連動的呈現出最新的資訊，

這是Excel函式簡單又有效率的地方。

以找出外資當日買超張數最多的股票股號為例，邏輯如下:『

使用LARGE
我們先找出C列中外資買超最多的數量為何，

→LARGE(C:C,G1)，

之所以在排名當中輸入G1的原因，

是做好了這個函式後我們只要順著拉下來就會跑出排名1-10的張數了。

使用MATCH
接下來我們要找這個最大值位於C列的第幾個欄位上，

→MATCH(LARGE(C:C,G1),C:C,0)，在這個例子中是660。

使用OFFSET
已經知道是第幾個欄位了，

剩下我們只要用OFFSET往左邊推幾格，就會知道買超排名第一的股票股號了，

→OFFSET(C$1, MATCH(LARGE(C:C,G1),C:C,0)-1, -2)

這裡稍微複雜些，我們先講為什麼要C1要變成C$1，

這是因為之後我們把函式往下拉的時候，C1會隨著我們啦下去而變成C2、C3，

寫成C$1之後它就會固定在C1的欄位上不動如山~

而MATCH(LARGE(C:C,G1),C:C,0)之所以要減掉1的原因是，

我們已經知道C欄中最大的數字位於第660個欄位上，

因此從C1算下去，欄位660是數下來的第659個欄位，因此要減掉1，

OFFSET當中最後的-2，就是股號相對於C列上的位置囉（往左邊2列）。』

所以..OFFSET(C$1, MATCH(LARGE(C:C,G1),C:C,0)-1, -2)這串式子，

在這個例子中會跑出3231的股號，

如果要跑出股票名稱，改成OFFSET(C$1, MATCH(LARGE(C:C,G1),C:C,0)-1, -1)即可，

剩下的2-10名只要把最上面的函式拉下去就可以了。

範例檔案

現在你就擁有一個可以自動化更新的排行表了~

該範例我上傳至: https://goo.gl/I7d56p

有興趣的朋友可以自行下載試做，

如果有問題也歡迎提出來討論！