迴瀾打狗人扎記

前幾天客戶跟說他們有個 Excel 檔，裡面有一千多筆名單資料，但其中有幾筆資料是重複的，用人眼來挑資料挑到眼花了，不知道怎麼辦才好，請我幫他找出來。我先問他想找出什麼樣的重複資料，他說是 Email 的部分想「挑出」重複的資料，然後分析這些人會填寫相同的 Email 資料，這問題其實不用寫什麼程式，用 Excel 公式 (Formula) 就可以了。至於移除重複在 Excel 2010 裡就非常簡單了，已經有內建的功能可以幫你達成。 首先，客戶的 Excel 資料格式如下： 我們如果要檢查 B 欄的「電子郵件地址」是否重複，我們可以先新增一個計算用的欄位，欄位標題我們輸入「是否重複」，然後在內容的地方輸入以下公式： =COUNTIF($B:$B, B2) > 1 然後在 C2 這一格的右下角用滑鼠雙擊左鍵，這個公式就會自動從第一筆複製到最後一筆： 我複製完之後，馬上就會看到有資料標示為 TRUE，這些資料就代表該筆資料有重複： 複製後的公式你會發現寫 $B:$B 代表的是整個 B 欄位，而第二個參數會隨著不同的儲存格會自動變動，如下圖示，透過 COUNTIF 公式的搭配使用，就會自動將 B7 的資料，跟整個 B 欄的資料進行比對，如果大於 1 的話就代表該筆資料重複了。 接著我們透過篩選功能把重複資料篩選出來，你可以先將游標移往「是否重複」的標題欄位上： 然後點選「篩選」 將是否重複欄位的 FALSE 資料取消勾選，就可以選出所有重複資料了！ 最後我們再針對檢查重複的欄位進行排序，就可以進一步為何這些資料重複了！ 然而如果要移除重複資料，那就簡單多了，在 Ribbon 工具列的「資料」頁籤下有個「移除重複」按鈕： 然後你可以選擇要檢查重複資料的欄位名稱，選取完後按下確定即可自動刪除重複資料： Microsoft Excel 會提示你一些移除重複的資訊： 這功能唯一的缺點就是不知道他到底幫你刪除了哪幾筆，只知道他只會留下一筆不重複的資料而已，如果是重要的客戶資料，還是建議用我文章一開始教學的方法篩選出重複資料後再一一篩選比較妥當！ 相關連結

設有 A, B 兩欄，要從 B 欄刪除 A 欄的資料，則新增一欄 C 其公式如 =ISNA(MATCH(B4,$A$1:$A$50,0))， 再以C欄排序，再刪除 C 欄為 False 即可。 MATCH 根據指定的比對方式，傳回一陣列中與搜尋值相符合之相對位置。當您需要取得符合搜尋條件的元素之相對位置而非元素本身時，您應使用 MATCH 函數，而非 LOOKUP 函數。 語法 MATCH(lookup_value,lookup_array,match_type) Lookup_value 是您要在表格中尋找的值。 Lookup_value 是您要在 lookup_array 中尋找比對的值。例如：當您要在電話簿中尋找某人的電話號碼時，姓名就是所要尋找比對的值，而電話號碼才是您所要的資料。 Lookup_value 可以是數字、文字、邏輯值，或是一個參照到數字、文字、邏輯值的參照位址。 Lookup_array 是個連續的儲存格範圍，其中含有被比對值的資料。 Lookup_array 必須是個陣列或陣列參照位址。 Match_type 是個數字，其值有三種可能：-1、0 或 1。用以指定 Microsoft Excel 如何從 lookup_array 裡尋找 lookup_value。 如果 match_type 是 1，則 MATCH函數會找到等於或僅次於 lookup_value 的值。Lookup_array 必須以遞增次序排列：...，-2，-1，0，1，2，...A-Z，FALSE，TRUE。 如果 match_type 是 0，則 MATCH 函數會找第一個完全等於 lookup_value 的比較值。Lookup_array 可以依任意次序排列。 如果 match_type 是 -1，則 MATCH 函數會找到等於或大於 lookup_value 的值中的最小值。 Lookup_array 必須以遞減次序排列：TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ...等等。 如果 match_type 引數被省略，則假設其值為 1。 擴張為多欄比對 設有 A, B, C, D 兩欄，要從 C, D 欄刪除 A 與 C 同且 B 與 D 同之資料，則新增一欄 E 其公式如 =OR(ISNA(MATCH(C4,$A$

Count Distinct in Excel 今天朋友問了我一個 Excel 的問題，如果有一串資料，想要計算裡面的不同值共有幾個，要怎麼作？這個問題在資料庫很簡單，就是 select + count + group by 就作完了，但在 excel 嘛，還是拜請 google 大神，結果第一篇就得到這個答案。 =SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&"")) A1:A10 當然是 Range，可以自己替換，但這一串到底在作什麼，我試著用我的理解解釋看看。 假設有一串資料 A1 到 A10 共 10 個儲存格，共有 4 個 A、3 個 B、2 個 C、1 個空白，裡面不同的值就是 ABC 共 3 個，所以要得到的答案就是3，我們用上面的公式跑出來的結果如下： 判斷公式作啥，當然從括號裡面來，首先是 (A1:A10<>"")，這句判斷 Range 裡的每一個值是否不等於空字串，換句話說就是儲存格內有值為 true (1)，儲存格內沒有值就是 false (0) 囉。 那除號 (/) 後面的 COUNTIF(A1:A10,A1:A10&"") 又是什麼意思，它計算每一個值出現的次數，第二個參數之所以 AND (&) 空字串 ("")，是希望計次時用字串處理，這樣遇到空字串也計次得到，不會產生 0 的結果。 所以我們得到了一個全部是 true 跟 false 的資料陣列，以及一個全是次數的資料陣列，接下來相除 (/)，得到： 以 A 來說出現了 4 次，結果經過了上面的計算，得到的就是 1/4，這樣最後我們再用 SUMPRODUCT 加起來，就剛好是一個完整的 1，相同值因此不會被重覆計算而達到我們要的目的了。 至於最後一步為什麼要用 SUMPRODUCT 而不用 SUM 呢，這部份我也不瞭解，不過用 SUM 是不行的，所以我猜應該是因為 SUM 要的是數值的型別的關係吧。 最後的最後，附上一個不錯的討論串，是這個方法的一些延伸用法 http://www.pcreview.co.uk/forums/thread-1780806.php

以下的步驟是在微軟Office 2003中的Word軟體來操作，其他版本的Office由於目前手邊沒有軟體，並沒有經過測試，還不確定是否也有此功能，如果操作方法不太一樣的話，請再找找看軟體功能中是否有類似的敘述。 一、在文件中加入浮水印當作背景 第1步 首先開啟你的Word文件， 然後依序按下〔格式〕→【背景】→【列印浮水印】，準備在文件中加入一個背景圖當作浮水印。 第2步 出現「列印浮水印」的對話盒後，點選「圖片浮水印」，然後再按一下〔選取圖片〕按鈕，選取你要當作浮水印的圖檔，如果希望浮水印的透明度高一點，可以勾選右邊的「刷淡」，設定完成後，再按一下〔確定〕按鈕。 第3步 如圖，浮水印已經設定完成囉，只要3個步驟而已，相當簡單吧 。原來Word內建的這功能還真方便呀！ 二、修改圖片浮水印的亮度、顏色與尺寸 當我們用前面的方法將你指定的圖片當作是Word文件的浮水印之後，日後如果想要修改浮水印的大小、亮度與相關細節的話，可以按照下面的方法來設定。 第1步 要修改浮水印的圖檔配置方式的話，可以依序按下〔檢視〕→【頁首/頁尾】。 第2步 切換到「頁首/頁尾」編輯狀態後，先點一下該圖片，出現「圖片」工具列後，再依照上面的功能按鈕去操作。全部設定完後，再按一下上方「頁首/頁尾」工具列的〔關閉〕按鈕。 第3步 如圖，原本的圖片浮水印已經被我們改成其他樣式囉。 三、輸入文字也可以當浮水印： 第1步 如果你沒有準備圖檔，也可以在「列印浮水印」視窗中點選「文字浮水印」，然後在下面輸入文字挑選顏色、字型與字體大小，設定完成後再按下〔確定〕，即可使用你所輸入的文字來當作該分文件的浮水印囉。 第2步 如圖，剛剛輸入的文字，已經被我們拿來當作是該分文件的浮水印囉，看起來還不錯。 [gads]