Excel 小技巧《進階篩選》快速篩選重複資料

怎麼將重複的資料篩選出來，一開始對於Excel的用法並沒有很徹底的研究，第一想到的就是使用countif這個函數來做比對，然後再將有重複的刪掉，不過後來發現並不用那麼麻煩，原來在Excel裡還有一個『進階篩選』的功能，可以更快的將重複的資料篩選掉，有需求的人就一起看看是怎麼辦到的吧。

原先在做篩選時，都用conutif來看看每個有重複的有幾次，再利用排序名稱來刪掉重複的，不過如果數據很多的話這樣做就太累人了。

Excel 14個樞紐分析表應用練習

在一個 Excel 工作作的銷售記錄的資料清單中，含有欄位：日期、店名、業務員、產品代碼、機型、單價、數量、銷售額。取用這個資料清單來練習樞分析表的操作。
以下使用 Excel 2013 為例，資料來源有 700 筆以上。


1. 計算各店的銷售總額
在樞紐分析表欄位中，設定：
列：店名；值：銷售總額。並且更改儲存格A3和儲存格B3的標籤名稱。

Excel 輸入日期產生星期幾並將星期六、日顯示不同格式(WEEKDAY,TEXT)

在 Excel 的工作表中輸入一個日期之後，能自動產生星期幾，並且將是星期六、日的儲存格格式標示不一樣的格式，該如何處理？
如下圖中的一個日期對照三種不同的星期幾表示法，而且星期六和星期日自動以不同的格式來標示。

Excel 對一個資料表執行多個運算2(表格,SUBTOTAL)

在下圖左中的運算如果是要各種小計得到自行設計多組公式，而下圖右的做法是以下拉式清單的方式來處理，不用設計任何公式。


這個解決方法其實也是結合「篩選功能」和儲存格範圍轉換為表格而來。而一般在工作表中的一個儲存格區塊只能稱為儲存格範圍，在此所稱的「表格」是必須經過定義，並且經過轉換。

Excel 取得工作表名稱

在 Excel 中如果要取得某個儲存格所在的工作表之名稱，要藉助 CELL 函數。
儲存格A1：=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))
CELL("filename")：取得活頁簿的完整路徑。
例如：磁碟名稱:\資料夾名稱\[活頁簿名稱]工作表名稱
FIND("]",CELL("filename"))：搜尋「]」的位置。
LEN(CELL("filename"))：計算檔案完整路徑的總字元數。
利用 RIGHT 函數取得「]」右邊的全部字元，即為工作表名稱。

Excel 多條件AND運算來計算總和

在 Excel 的工作表中，如果要根據二個以上條件來取出某一欄的內容加總，其條件之間是以 AND 運算來執行，可以有多種方式來達到目的。
例如使用 SUMIFS 函數、SUM+IF+陣列、SUMPRODUCT 函數等方式。

Excel REPLACE和SUBSTITUTE 函數

在 Excel 中的 REPLACE 和 SUBSTITUTE 函數都是用來取代字串中的某些特定文字之用，其用法有那些差異呢？(參考下圖)
REPLACE 函數主要是根據指定的字元起始位置，指定被取代的字元數，然後以新的字串來取代。
(1) 儲存格E2：=REPLACE(A2,5,7,"_^_")
在儲存格A2中的字串中，由第5個字元開始，一共7個字元，以「_^_」取代。
(2) 儲存格E3：=REPLACE(A3,7,4,"999")
(3) 儲存格E4：=REPLACE(A4,11,5,"Word")

Excel 去除資料中的特定符號(SUBSTITUTE)

又有人問到在 Excel 的資料表中，因為儲存格中含有一些特定的符號(參考下圖)，如何將這些符號一次去除呢？這個問題被問過好幾次了，大部分都是使用 SUBSTITUTE 函數即可解決！
因為本例中的儲存格內只有三種符號：*、/、!。所以只要以 SUBSTITUTE 將這些符號取代為空白即可。
儲存格B2：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"*",""),"/",""),"!","")

Excel 樞紐分析表快速惡補

我們已經知道，在Excel中要「變」出一張分類匯總表，還要靠資料透視表這個強大的資料分析工具。那麼，如何新增一張樞紐分析表，就是我們接下來要學習的內容了！我們可以把新增樞紐分析表分為3個步驟：

Step 1 設定資料來源

選取作為分析資料來源的儲存格區域，切換到【插入】索引標籤，按一下「表格」群組中的［樞紐分析表］按鈕。

Excel 輸入數字變亂碼好糗！前輩不會告訴你的魔鬼細節

在Excel表格裡輸入的資料有多種類型，比如數字、數值、貨幣、百分比、日期、時間、欄位等，一些特殊的資料類型有屬於自己的輸入方法，用錯誤的方法輸入Excel將無法識別，進而得不到正確的顯示結果。所以，若要真正掌握資料輸入，則需要搞清楚Excel數字格式！

1. 正確輸入以「0」開頭的數字

要在Excel儲存格中輸入以「0」開頭的數字，方法很簡單，在輸入數字前，先輸入一個英文的單引號「'」，然後輸入以「0」開頭的數字即可。這裡 的關鍵就在於輸入的英文單引號「'」，它使Excel將隨後輸入的以「0」開頭的數字識別為欄位資料，進而避免被識別為數值型資料的「0」被吃掉。

還有一個方法就是在輸入數字前，先將要輸入數字的儲存格或儲存格區域設定為「文字」格式，此後直接輸入數字即可。

2. 正確輸入分數

Excel 自動輸入讓你事半功倍

EXCEL 除了好用的函數外，本身也有很多智慧型功能都很神奇，像是「自動填入」的功能，當我們輸入 1、2、3 後，就可以自動幫我們在後面的儲存格中填入 4、5、6… 等順序數字。除了數字外，連月份、星期甚至天干、地支、十二生肖都能幫我們自動填入呢！另外，如果需要將直行的儲存格內容轉換成橫列排列時，也不需重新輸 入哦！

step 1

先在第一個儲存格中輸入序列的第一個內容，在此以月份為例，在 A1 中輸入「一月」。

Excel 善用 COUNT 函數快速計算個數

Excel 裡藏了許多好用的函數，都可以幫我們讓工作更快速。像是「COUNTA」，可以幫我們計算儲存格範圍中不是空白儲存格的個數；而「COUNTIF」可以計 算儲存格範圍中符合特定條件的儲存格個數；另外，「COUNTBLANK」則是可以計算儲存格範圍中空白儲存格的個數，只要懂得善加運用，就能讓工作更有 效率。

step 1

先選取要計算非空白儲存格的儲存格範圍，然後在「資料編輯列」中輸入「=COUNTA (範圍起點:範圍終點)」。

Excel 善用巨集指令，讓 Excel 更有效率

如果在 Excel 中，經常要執行重複的動作，這樣不但會讓你覺得很繁瑣，而且也顯得很沒有效率。這時候，只要能善用 Excel 中特有的「巨集」功能，就能幫我們把這些重複的動作「打包」起來，日後只要執行這個巨集，就可以自動幫我們執行所有重複的動作，如果再加上快速鍵的設定， 包準工作效率大增百倍哦！

step 1

開啟 Excel 檔，點選〔檔案〕活頁標籤中的「選項」，接著在「Excel 選項」對話盒中，點選「自訂功能區」後，在右側【自訂功能區】中勾選「開發人員」，然後按下〔確定〕。

Excel也能按照中文排序

Excel中的排序功能，可以讓我們以同一行的內容，依照筆劃或英文字母的先後順序，進行由小到大的升冪排序，或是由大到小的降冪排序。甚至對於一、二、三、四、五……等中文數字，照樣也能依照我們想要的順序排列哦！

Step 1

當我們想對相同類別內容的儲存格進行排序時，可以點選〔常用〕活頁標籤中的〔排序與篩選〕，然後在下拉選單中選擇升冪的【從A到Z排序】或降冪的【從Z到A排序】。

Excel-隨意挑出欄位中任意5個名字

如果想要在一串學生姓名欄位中，挑出任意幾個名字，做為抽籤之用，該如何處理呢？
將名字列在A欄中，然後在B欄中輸入公式「=RAND()」，即產生任意亂數值。
接著在D4儲存格中輸入公式：
=INDEX($A$1:$A$19,MATCH(LARGE($B$1:$B$19,ROW(1:1)),$B$1:$B$19,))
再將公式複製到D5:D8。其中ROW(1:1)會變為ROW(2:2) … ROW(5:5)。
 

Excel-找出一欄中最後一個數值

如果想要抓取某一欄位(例如A欄)中的最後一個數值，可以使用以下的公式：
=LOOKUP(9.99999999999999E+307,A:A)
=LOOKUP(9.9E+307,A:A)
公式的意思是要在A欄中找尋Excel可容許的最大正數(9.99999999999999E+307)。
因為LOOKUP函數是以二分搜尋法方式來找尋資料，例如：
=LOOKUP(10,{1,2,3,4,5,6,7,8,9})
先找到中間值5，判斷後繼續在{6,7,8,9}找尋，
先找到中間值8，判斷後繼續在{9}中找尋，
最後找到最接近的值為9。(注意該陣列已經過排序)

Excel：利用陣列製作摘要表

如下圖的基本資料，假設要依星期幾來計算各天的數量小計。

Excel-SUMPRODUCT+COLUMN

如果你想要計算一群欄位中，奇數欄位的和或是偶數欄位的和，可以使用以下的公式：
 

Excel-sum+if+陣列

當在一個儲存格中要使用多個條件來計算個數或是總和，可以透過陣列，藉由「*」符號，將多個條件「AND」在一起。例如：

Excel-SUMPRODUCT函數應用

SUMPRODUCT函數：傳回各陣列中所有對應元素乘積的總和。
語法 ：SUMPRODUCT(array1,array2,array3, ...)
Array1, array2, array3, ...   是 2 到 255 個欲求其對應元素乘積之和的陣列。
如 果想要根據一個人員缺曠的明細表，來統計每個人的缺曠時數小計。若利用SUMPRODUCT函數，在本例的應用中，符合公式中的條件會傳回True(否則 為False)，再將其X1，可以將True/False陣列轉換為1/0陣列。如此SUMPRODUCT函數中的各元素相乘積，將只會留下符合條件者的 和，因為不符合條件者(False,0)都會是0。(參考下圖)