[EXCEL] 挑出重複的資料

　
　　相關函數：COUNTIF() / SUMPRODUCT()

　　當 EXCEL 中的資料越來越多時，我們會產生一種需求：能不能找出重複的資料呢？以下表為例，當我們在輸入學生資料時，一定要避免學號重複輸入，這時候 EXCEL 就能自動幫上忙了！步驟如下：

##CONTINUE##

1. 將游標移到 A2
2. ［格式］［設定格式化的條件］，出現上圖的小視窗
3. 選擇［公式為］，輸入公式 =COUNTIF(A:A,A2)-1，設定格式，按［確定］
4. 將 A2 的格式複製到 A 欄所有儲存格，完成。

　　設定好之後，只要在 A 欄輸入重複的資料，資料就會變成紅色，提醒你資料重複了。COUNTIF() 用來計算 A2 在 A 欄中出現的次數，如果只有 1 次，減 1 後變成 0，也就是 FALSE，公式不成立就不會變色；如果大於 1 次，表示重複了，減 1 後大於 0，也就是 TRUE，就會變色了。

　　進階一點，如果要找出兩欄資料同時重複的資料呢？如上表，要找出性別相同，生日也相同的人，相同的步驟，只要把游標移到 C2，公式改成

=SUMPRODUCT(--($C$2:$C$11=$C2),--($D$2:$D$11=$D2))-1

　　再複製格式即可。

　　SUMPRODUCT() 的詳細說明，可以參考 多重條件查表法 這一篇。簡言之，有關多重條件的，都可以用 SUMPRODUCT() 來做。

[EXCEL] 不重複的亂數

　
　　相關函數：RAND() / INT() / RANK()

　　EXCEL 提供了一個函數 RAND()，讓使用者可以隨機產生亂數。RAND() 會產生一個 0<=RAND()<1 的數值，每當表格內有任何變化或修改時，RAND() 都會重新產生一個新的亂數。

　　介於 0 和 1 之間的亂數都是小數，可能不符合實際應用，更多的時候，我們會需要整數亂數。例如，要從 20 個人中隨機抽 10 個人出來，我們就需要 10 組 1~20 之間的變數。這時候，我們可以利用這個公式

##CONTINUE##

=INT(RAND()*20)+1

　　並把它複製到 10 個儲存格。如下表的 B 欄。INT() 用來取得數值的整體部份，而把小數部份捨棄。


　　上面的公式有個問題，13 重複出現了兩次，這可能不符合實際需求。如果要產生不重複的亂數，可以再配合排名次的函數 RANK()，在 C2 輸入公式

=RANK(A2,$A$2:$A$11)

　　並往下複製到 C3:C11，就可以得到不重複的亂數了。

　　整合一下，如果要從 20 個人中隨機抽出不重複的 10 個人出來，應該怎麼做？把 A 欄和 C 欄都擴充到 20 列，C 欄就會有 20 組不重複的 1~20 的亂數，然後取前 10 個亂數 (C2:C11) 即可。

[EXCEL] 多重條件查表法

　
　　相關函數：INDEX() / SUMPRODUCT() / ROW()

　　用 VLOOKUP() 來查表的另外一個缺點，就是只能使用單一條件來查詢，如果我們需要查詢兩個或多個條件同時成立的狀況，VLOOKUP() 就不適用了。例如，我們想要查詢國文和數學成績都不及格的人，就不能直接使用 VLOOKUP()。

##CONTINUE##

　　如上圖，要在 F3 輸入公式

=INDEX($B$1:$B$5, SUMPRODUCT(--($C$2:$C$5<60),--($d$2:$d$5<60),row($b$2:$b$5)), 1)

　　才能正確找出答案。

　　INDEX() 的作用，之前的斗文章已經提過。ROW() 則是傳回儲存格的列數，所以公式的重點便在 SUMPRODUCT() 這個函數上。SUMPRODUCT() 的原意是把陣列的相對元素先相乘，然後再把每個元素加起來，傳回一個總和的值。在這裡，我們則將每個陣列當做是一個條件的比對結果，符合值為 1，不符合值為 0，如果有任何一個條件不符合，會導致乘以 0 的狀況，進而排除這筆記錄。最後只有符合條件的記錄能傳回 ROW()。

　　以上表為例，結果是 C2:C5 {1,0,1,0} * D2:D5 (0,1,1,0} * ROW(B2:B5) {2,3,4,5} = {0,0,1,0}*{2,3,4,5} = {0,0,4,0}，元素相加後即為 4，是正確答案的列數。

　　注意一：公式中的符號 "--" 是用來把比對大小之後的邏輯值 TRUE/FALSE 轉成數字 1/0。不然，SUMPRODUCT() 會一律把邏輯值當做 0，造成運算錯誤。

　　注意二：如果原始資料中有多筆記錄符合所有條件 (多個人同時不及格)，這個公式就不能適用。我們會在以後的文章中討論這種情形要如何處理。

2006-10-31 補充

　　注意三：如果在原始資料中找不到同時符合多個條件的資料，SUMPRODUCT() 會傳回 0，INDEX() 在處理 0 時可能會造成誤判。要避免這種情形，可以用 IF() 來處理這種特別狀況。公式改成

=IF(SUMPRODUCT(...)=0, "找不到資料", INDEX($B$1:$B$5,SUMPRODUCT(...),1) )

中秋戲浪－基隆大武崙沙灘

　
　　10月6日，中秋團圓，自然應該和家人一起同歡。在基隆享用完豐盛的中餐之後，帶著四家老小，就近到附近的大武崙沙灘一遊。

　　大武崙沙灘據說是基隆僅存的沙灘，從北二高往北下萬里交流道，左轉接基金一路，沿著「外木山大武崙海岸」的路標走，在基金三路附近一個紅綠燈右轉往金山，不遠後再右轉下切Ｓ型陡降坡，大武崙沙灘立即映入眼簾。［地圖 (地圖中的澳底沙灘就是大武崙沙灘，因為沙灘就在澳底漁港旁］。

##CONTINUE##

　　大武崙沙灘據說是基隆僅存的沙灘，面積不大，但沙質不錯；沙灘縱深不深，造成沙面較陡，浪花也較大，大朋友可以沖浪戲耍，小小朋友則要小心，別被海浪沖倒了。

　　沙灘是免費開放的，無人管理，自然也沒有浴厠等公共設施。沙灘旁的民家倒是有提供付費沖洗服務，一次20元。停車也不太方便，只能在沙灘旁的小路路邊停車。政府如果能出面做點什麼，這片沙灘會更好才對。

　　玩的人多，又無人管理，沙灘上便留下一些原本不屬於沙灘的東西。套句老話，除了腳印，請不要留下別的東西。

　　整體來說，大武崙沙灘是個滿適合全家大小看海、玩沙、戲浪的景點，離市區也不算太遠，下次還要再帶小朋友來玩。

　　更新：2008/5/7 浪裡尋石－基隆大武崙沙灘 PART 2。

[EXCEL] 任意鍵值查表法

　
　　相關函數：INDEX() / MATCH()

　　用 VLOOKUP() 來查表是簡單易用的好方法，但是有個缺點，就是用來查詢的鍵值必須在原始資料的第一欄。以前一個例子來說，我們可以用學號來查姓名，但是無法用姓名來查學號，因為鍵值 (姓名) 不是在原始資料 A2:B5 的第一欄。

##CONTINUE##

　　如果想用第一欄之外的其他欄位來做鍵值查表，有一個變通的方法，就是利用 INDEX() 和 MATCH() 的組合。

　　MATCH() 可以在一個陣列中找出鍵值，並傳回它的位置；INDEX() 可以傳回一個陣列中，特定位置的內容值。我們可以利用 MATCH() 來找出鍵值 (姓名) 所在位置，再用 INDEX() 找出相對應位置的內容值。


　　在上表中，E1 可以輸入公式

=INDEX(A2:A5,MATCH(D1,$B$2:$B$5,0),1)

　　當 D2 值為「孫三」時，MATCH() 會傳回位置 3 (從 B2 起算第三列) 給 INDEX()，INDEX() 就會傳回 A2 起算第三列的值，也就是 A4 的內容值「 A30303」。