[EXCEL] 排除重複的資料

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

　　有一堆原始資料，其中可能有重複的部份，要如何才能排除重複的資料，每種資料只留下一筆？

　　第一種方法，是先排序，把重複的部份集中在一起，再一筆一筆過濾排除。這個方法的好處是簡單，不需要任何函數或公式；壞處是，它會破壞掉原來的資料順序，而且必須從頭到尾用人工巡視一遍，可能會有疏漏。

##CONTINUE##

　　第二種方法，是用「格式化條件」把重複的部份變色，再一筆一筆過濾排除。詳細方法請參考這篇「[EXCEL]挑出重複的資料」。它的好處是不破壞原來的資料順序，而且可以一邊輸入新資料，一邊即時檢查新資料是否重複；壞處是，它一樣必須從頭到尾用人工巡視一遍，可能會有疏漏。如下圖 A 欄的紅色資料。

　　第三種方法，也就是現在要介紹的方法，則是利用函數公式，直接挑出不重複的資料，這樣既不會破壞原來的資料順序，也不必從頭到尾用人工巡視一遍。

　　如上圖，有學生的請假記錄，如果想找出「二月份請過假的人」，也就是要把請過二天以上假的人只顯示一次，做法如下。

　　假設名單範圍在 A1:A20，C 欄是用下列公式直接挑出不重複的請假名單：C2 輸入公式

=INDEX(A:A,SMALL(

IF(

IF(ISNA(MATCH($A$1:$A$20,A:A,0)),
"",
MATCH($A$1:$A$20,A:A,0))=ROW($A$1:$A$20),

ROW($A$1:$A$20),
""),

ROW()))

　　這是一個陣列公式，請用 CTRL+SHIFT+ENTER 完成輸入。公式可往下複製。出現 #NUM! 時，表示名單已經列完了。

　　這個公式的原理，是利用 MATCH(A2,A:A,0) 會在 A:A 找出第一個符合 A2 資料的位置列數，如 E 欄所示。如果 MATCH() 的結果和列數相同，如 E2，表示 A2 是第一次出現。如果 MATCH() 的結果和列數不相同，如 E5，表示 A5 不是第一次出現，也就是重複資料。

　　利用 E 欄這個陣列，再加上 IF() 把陣列中的重複資料清為空白，就可以得到一個不重複名單列數的陣列了。再用 SMALL() 做排序，INDEX() 把列數轉為資料，就可以得到 C 欄的名單了。

　　為了讓公式能使用在大小不同的變動名單上，我們允許名單範圍 A1:A20 中有空白。注意 E12 的 #N/A，就是因為 A12 為空白造成的。公式中的 ISNA() 就是為了把 #N/A 清為空白以免公式出錯才加的。

　　實用上，可以把名單範圍設稍微大一點，當名單大小變動時，就不必每次更改公式了。

2008/10/21 補充：

　　夏日大大提供了一個更簡便的公式，

　　=T(INDEX(A:A,MIN(IF(COUNTIF(C$1:C1,$A$2:$A$20),4^8,ROW($A$2:$A$20)))))

　　小弟大力推薦，有興趣的朋友可以研究一下。

摺紙

　
　　連續幾波寒流來襲，再加上流感肆虐，連大人都快受不了了，小丫丫也是在咳嗽聲中硬撐著，期待著天氣回暖。天氣冷，不能出去玩，待在家裡能做什麼呢？

##CONTINUE##

　　那就來動手做勞作吧！

　　畫圖，畫字（也就是不照筆順，把阿拉伯數字或注音符號「畫」出來），摺紙，都是幼稚園小朋友不錯的家庭活動。尤其是摺紙，讓人不得不訝異小朋友的學習能力。

　　照著書店買的簡易的摺紙書，可以摺出一些簡單的造型，如花，狗，企鵝，天鵝，大象等等。可是，造型簡單，書上的解說方式更簡單，幾條抽象的山摺線和谷摺線，常常讓人無法理解，這一步是怎麼變成下一步的？父女倆在那裡傷腦筋研究摺法，也算是寒冬中的一種趣味。

［丫丫完全靠自己看書摺出來的天鵝，像嗎？］

　　照我原先的想法，連大人都看不太懂的摺紙說明，小朋友更是不可能理解的。沒想到，丫丫就是有辦法，不但之前教過她的，她有辦法自己看書再摺一次出來，有些從來沒教過她的，她自己也能照著書上的說明摺出來！

　　驕傲的大人啊！我們沒有你們想像的那麼無知啊！

［猜猜是什麼？企鵝啦！我負責最難的一個步驟，其他的部份丫丫自己摺。］

　　現在，紙飛機，小船，垃圾盒子，企鵝，天鵝，狗臉....丫丫會摺的越來越多了，色紙摺完了（其實是我捨不得用，照丫丫這麼大的消耗量，也是滿傷本的），改摺廣告紙，在寒冬中，不只是打發時間，不只是小肌肉和頭腦的鍛鍊，更是親子間的美好時光！

[EXCEL] 多重單位數值的運算

　
　　相關函數：INT() / MOD()

　　１小時２３分４５秒，加上２小時３４分５６秒，答案是？簡單，直接相加，３小時５８分４１秒。

　　１分２３秒４５，加上２分３４秒５６，答案是？呃....

##CONTINUE##

　　２打又７瓶，加上３打又８瓶，答案是？呃....用個 IF(瓶數加總>=12,打數加1,打數不加1) 公式，可以算出６打又３瓶。

　　２打又７瓶，加上３打又８瓶，加上４打又９瓶，加上５打又....？啊....不要再加了啦，IF() 公式中只用 >=12 算不出來了啦！

　　類似這種多重單位數值的運算，很實用，但是無法在 Excel 中直接做到，必須做點手腳。我的想法是，先把所有不同單位的數值，換算成最小單位的數值，直接加減運算後，再換算回多重單位。

　　如下圖，把上面的兩個例子實作出來。

• C2 公式 =A2*12+B2，可往下複製，將多重單位換算成最小單位，１打＝１２瓶。
• C5 公式 =SUM(C2:C4)，只是單純做加總運算。
• D5 公式 =INT(C5/12)，把瓶換算成打。
• E5 公式 =MOD(C5,12)，取得換算成打剩下的餘數。

　　公式重點在於 INT() 和 MOD() 的應用。其實這比較像數學問題，而不是單純 Excel 的問題。

　　進階一點，如果是三重單位的話，如上圖 G1:M5，也是類似的做法。

• J2 公式 =G2*6000+H2*100+I2，可往下複製，將多重單位換算成最小單位，１分＝６０秒，１秒＝１００百分之一秒。
• J5 公式 =SUM(J2:J4)，只是單純做加總運算。
• K5 公式 =INT(J5/6000)，把百分之一秒換算成分。
• L5 公式 =INT(MOD(J5,6000)/100)，把換算成分的餘數，再換算成秒。
• M5 公式 =MOD(J5,100)，取得剩下的餘數。

　　相同的公式模式，只要適當填入各單位之間的換算數量 (12, 6000, 100 等)，就可以完成多重單位的轉換和運算了。

[EXCEL] 今天放假嗎？

　
　　相關函數：WEEKDAY() / NETWORKDAYS()

　　如果你想用 Excel 來計算工時或工資，有個訊息你一定會想知道，今天放假嗎？

　　假日分為兩種，一種是週休二日，一種是國定假日或各其他特別的日子。週休二日可以用星期函數 WEEKDAY() 來判斷，那麼國定假日呢？

##CONTINUE##

　　Excel 提供了一個工作日函數 NETWORKDAYS(開始日期，結束日期，假日列表)，來計算工作日。它會計算兩個日期之間的天數，然後扣掉星期六，星期日和假日列表中的日子，結果就是工作日的天數。如果無法使用此函數，且傳回 #NAME? 錯誤，請安裝〔工具〕〔增益集〕〔分析工具箱〕。

　　運用這個函數，我們只要把開始日期和結束日期設為同一天，就可以知道那一天是不是假日了。傳回１表示是工作日，傳回０表示是假日。

　　如上圖，B2 公式 =WEEKDAY(A2,2)，會傳回星期數，當它等於６或７時，表示是週休二日。

　　C2 公式 =NETWORKDAYS(A2,A2,$G$2:$G$10)，會傳回工作天數，假日列表放在 G2:G10。請注意第９列和第１０列，這兩天不是週休二日，但是因為在假日列表中有這兩天，所以回傳的工作天數仍是０，表示放假。

　　C11 公式 =NETWORKDAYS(A2,A10,$G$2:$G$10)，會傳回 A2 ~ A10 中間的工作天數，它的值會和 C2:C10 個別計算的結果相同。

　　E2 公式 =IF(C2=0,200,100)*D2，用來計算加班費，它假設平日加班每小時一百元，假日加班每小時兩百元。當然，你可以把 C2 的 NETWORKDAYS() 公式直接代入 E2 公式，這樣就不需要 C 欄了。

相關文章：

• 今天放假嗎？要補班啦！：考慮週六週日要補班的計算公式。

[EXCEL] 部分符合查表法

　
　　相關函數：IF() / LARGE() / INDEX() / 陣列公式

　　之前提到一些查表法，包括「任意鍵值查表法」，「多重條件查表法」等，今天再來一個「部分符合查表法」。

　　一般的查表，鍵值必須完全符合才能查出來，所以沒有辦法用 "book" 當鍵值去查到 "This is a book"。今天的公式，則可以突破這個限制。它的原理是，先將原始資料做一些運算，再把運算結果拿來和鍵值比對。

##CONTINUE##

　　因為會用到陣列公式，所以還不懂陣列公式的朋友，可以先參考另一篇文章：淺談陣列公式。

　　如下圖，是學號和姓名的對照表，利用之前說過的 VLOOKUP()，我們可以用「完整的學號」查出姓名。那麼，可不可以用「部分的學號」來查出姓名？例如，只輸入「05」，來查出 A30305 的「周五」？

　　可以的，E2 公式如下：

=INDEX(B2:B11,LARGE(IF(RIGHT(A2:A11,2)=D2,ROW(A2:A11)-1,""),1))

　　這是陣列公式，記得要用 CTRL+SHIFT+ENTER 來完成輸入。

　　公式說明：

• RIGHT(A2:A11,2)=D2　這是陣列公式，將 A 欄的最右邊兩個字元取出來，和 D2 做比對。
• IF(RIGHT(...),ROW(A2:A11)-1,"")　如果比對成功，則傳回列數-1，否則傳回空白。
• LARGE(IF(...),1)　從比對結果傳回的列數中，找出最大值。如果有多個結果符合，傳回列數最大的；如果沒有人符合，會造成 #NUM! 錯誤。
• INDEX(B2:B11,LARGE(...))　根據傳回的列數，找出相對應的 B 欄資料，此為最後結果。