Excel教學 1分鐘學識試算表常用公式/合併儲存格/VLOOKUP/Pivot table用法

Excel教學的十大必學秘技目錄（點擊快速前往至以下段落）：

• 利用樞紐分析表 （Pivot Table） 整理及分析資料
• 利用篩選器 （Filters） 來篩選資料
• 交換行與列的位置
• 大量移除重覆的資料
• 增加下拉式功能表
• 利用條件轉換儲存格格式
• 利用 VLOOKUP 查找資料
• 利用 INDEX MATCH 查找資料
• 利用COUNTIF 計算特定字元或數字的數量
• 利用& 合拼儲存格資料

Excel公式1. 利用樞紐分析表（Pivot Table ） 整理及分析資料

如需要整理及分析資料，就一定要學會使用樞紐分析表（Pivot Table ）。假設我想統計人名表中有多少人來自馬來西亞和新加坡，我們可以製作一個樞紐分析表（Pivot Table ）來進行資料整理。

1. 首先到「插入」
2. 左上方會有「樞紐分析表」
3. 當「建立樞紐分析表」的視窗彈出後
4. 上方可以選取你需要分析的資料範圍
5. 下方可以選擇你想存放樞紐分析表的位置

建立完成後右方會出現「樞紐分析表欄位」，而調整設定各欄位時，左邊的報表亦會立即更新。而根據你想要的篩選資料，可將資料拖曳至該區欄位。

A 報表篩選：作為篩選整張報表資料的依據（Filter）
例如：我希望只顯示30歲以上的人，那我便需要將「Age」放在這裡。

B 欄：作為報表的欄資料（Column）
欄標籤即代表報表的欄資料，只要填寫超過一個欄位資料，Excel會在報表上進行分組。例如上圖放了「Country」於橫標籤內，報表上就可以見到來自「Malaysia」有8個人，而來自「Singapore」則有兩個人。

C 列標籤：作為報表的列（Row）
列標籤即代表報表的列資料，和欄標籤一樣如果填寫超過一個欄位的資料，Excel會在報表上進行分組，例如上圖放了「last」於欄標籤內，報表上便可以見到年齡超過30歲的人的姓氏。

D 值：顯示的統計數字
針對放入此區域的項目而決定顯示的數字，如果點開項目右邊的小三角型，選取「值欄位設定」，便可以設定希望的資料是加總、項目個數、平均值、最大值等。

另外，報表還可以設定以自定範圍顯示數據，方便整理年齡等資料。

1. 右鍵點選希望進行分類的數據
2. 點選「群組」
3. 設定開始點、結束點及間距值

Excel公式2. 利用篩選器（Filters） 來篩選資料

當需要整理大量資料時，有時希望只選取某些符合條件的資料時，篩選器（Filters）便可大派用場。

新增篩選器（Filters）的方法很簡單：

1. 選取你需要篩選的資料
2. 點選「資料」
3. 選取「篩選」
4. 篩選器（Filters）就會出現於你所選擇的資料中的第一個列（Row）

在Excel中，篩選器（Filters）可以加到每一個欄（Column）；點開小三角型進行勾選，報表便會只顯示有勾選的資料。

Excel公式3. 交換欄與列的位置

如果資料的欄（Column）比列（Row）多，你可能會希望資料可以交換欄與列的位置，以節省空間，但要逐個儲存格移動是非常花費時間，這時便需要轉置（Transpose）去節省時間。

1. 選取希望轉置（Transpose）的資料
2. 選取「選擇性貼上」
3. 點選「轉置」
4. 所需資料轉換完成！

Excel公式4. 大量移除重覆的資料

如果需要處理大量重複的資料，例如公司的聯絡資料，那Excel的移除重複的功能將非常適合你。

1. 選取需要移除的重覆資料
2. 選取「資料」
3. 點選「移除重覆」
4. 點選「依照目前的選取範圍排序」，然後按「移除重覆」
5. 移除完成後，Excel 還會很貼心的告訴你一共找到了多少個重複值，同時保留了多少個值

Excel公式5. 增加下拉式功能表

有時侯於報表中新增下拉式功能表可以給予使用者輸入資料的方向，亦可以增加資料輸入的速度，特別是重複的資料，例如：＂Yes＂、＂No＂、＂N/A＂等。

1. 選取需要增加下拉式功能表的儲存格
2. 選取資料驗證 （Data Validation）
3. 在儲存格內允許選擇「清單」，然後於來源中輸入選項 （需用半型,作分隔）
4. 下拉式功能表新增成功!

Excel公式6. 利用條件轉換儲存格格式

很多時候處理報表都需要突出某部分的數據，例如：我需要於市民資料中將所有移民的顏色轉換，但資料內容眾多，所花時間必然不少，那格式化資料（Conditional Formatting）的存在就變得非常重要。

1. 選取需要突出的資料範圍
2. 點選「常用」
3. 選擇「設定格式化的條件」（Conditional Formatting）
4. 設定你所需的條件後，所有包含的資料便會根據你所選的格式顯示

Excel公式7. 利用 VLOOKUP 查找資料

當希望整合兩組資料位處於不同的報表的資料時，可以如何快速進行對資料的抽取? 例如你有一張報表包含人名、年齡的資料，但你希望創作一個表格只包含某些人及他們的年齡，那你就需要用到VLOOKUP 功能。

VLOOKUP 的算式：=VLOOKUP(查閱值,查閱範圍,欄位編號,是否完全符合)
（小提示：所有算式都需要使用半型符號）

VLOOKUP 一共有四個參數，分別為：

• 查閱值：Excel 將依據這個值查找
• 查閱範圍：需要在那個範圍內查找
• 欄位編號：需要抽取的資料的欄位編號，由左算起（A為1、B為2、如此類推）
• 是否完全符合：如果需要查找與查閱值部分相同就填入 TRUE，如需完全相同則填入 FALSE

下圖例子可見：

算式為 =VLOOKUP(I3,$A$1:$F$20,4,FALSE)

我們依Angelina在$A$1:$F$20的範圍內查找，當Excel查找到完全符合Angelina的字元後，就會抽出由左算起第4個欄位（D欄）的對應資料，即是46。（$在Excel算式中代表絕對參照）

Excel公式8. 利用 INDEX MATCH 查找資料

上面介紹了非常好用的VLOOKUP的查找功能，但VLOOKUP有兩項缺點：一，如果需要處理的資料量大，VLOOKUP就需要一段較長的時間處理；二，VLOOKUP只能由左至右查找資料，當資料量大時便會容易產生錯誤。這裡介紹Excel 的INDEX MATCH算式可以解決以上兩點問題。

INDEX 的算式：=INDEX(查閱範圍,列索引值,欄索引值)

（小提示：所有算式都需要使用半型符號）

INDEX 共有3個參數，分別為：

• 查閱範圍：設定查閱範圍
• 列（Row）索引值：指定要取出表格中的第幾列資料
• 欄（Column）索引值：指定要取出表格中的第幾欄資料

MATCH的算式：=MATCH(查閱值,查閱範圍,符合程度)

MATCH共有3個參數，分別為：

• 查閱值：Excel 將依據這個值查找
• 查閱範圍：設定查閱範圍
• 符合程度：符合程度可以為1，0，-1，如果填寫0會查找跟查閱值完全一樣的資料；如果填寫1則會查找小於或等於查閱值的資料；如果填寫-1則會查找大於或等於查閱值的資料。

INDEX MATCH合用：=INDEX(查閱範圍,MATCH(查閱值,查閱範圍,符合程度))

簡單來說，就是以MATCH先查找目標的位置，然後以回傳的數值作為列索引值（和欄索引值），然後INDEX就可以立即在資料範圍內找出特定位置的值。

下圖例子可見：
算式為： =INDEX($A$1:$F$20,MATCH(I3,$A$1:$A$20,0),MATCH(J2,$A$1:$F$1,0))

中間間部分的 MATCH(I3,$A$1:$A$20,0) 會命令Excel依Angelina在$A$1:$A$20的範圍內查找，當Excel查找到完全符合Angelina的字元後，就會回傳列位置，即是3，而後尾部分的MATCH(J2,$A$1:$F$1,0) 會命令Excel依Age 在$A$1:$F$1的範圍內查找，當Excel查找到完全符合Age的字元後，就會回傳欄位置，即是4。這時，算式便成 INDEX($A$1:$F$20,3,4)，Excel便會在查閱範圍內讀取第3列（Row）、第4欄（Column）的值，即是46。 （$在Excel算式中代表絕對參照）

不論是INDEX MATCH或是VLOOKUP 都可以跨報表進行資料搜查，各位只需要於查閱範圍中指定需要查找的報表即可。

Excel公式9. 利用COUNTIF 計算特定字元或數字的數量

Excel的存在當然是為了讓大家可以方便進行資料整理，當你需要統計重複出現的字元或數字時，你可以選用COUNTIF 來代替人手統計，例如我希望統計＂No＂在報表中出現了多少次，那我就可以使用COUNTIF功能。

COUNTIF 的算式：=COUNTIF(查閱範圍,條件)
（小提示：所有算式都需要使用半型符號）

COUNTIF 共有2個參數，分別為：

• 查閱範圍：設定查閱範圍
• 條件：希望查找的條件或指定字元

下圖例子可見：
算式為 =COUNTIF(A1:F20,”No”)

利用COUNTIF命令Excel在查閱範圍為A1:F20中尋找＂No＂並計算數量，結果回傳為一共13個＂No＂。

Excel公式10. 利用& 合拼儲存格資料

很多情況下姓名、地址等資料都會分開輸入，你可能希望將姓、名或地區、街道、大廈、樓層等資料重新合拼，而面對大量需要進行合拼的資料時，Excel的&將會是非常重要。

算式為：=A2&” “&B2

例如希望將A2和B2的資料合拼，你可以簡單地用這條公式來進行合拼。

所有算式都可以向上下左右來拖拉儲存格右下角的小點，Excel會自動將公式應用到所選儲存格。

恭喜你完成了Excel教學的十大必學秘技，希望以上的excel教學可以幫助到各位快速完成資料整合，各位可以將本文bookmark以方便進行溫習，另外可將本文分享給朋友和身邊有需要的人。