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

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

職場技能

廣告

Excel教學大全!Excel公式和功能不少學生和打工仔經常會用到,《經一》整合10大必須知道的Excel formula,提供詳細的教學步驟,包括VLOOKUP 教學、COUNTIF、Pivot table 等,幫助各位學好excel技能,更有效率地整合資料。

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

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

  1. 首先到「插入」
  2. 左上方會有「樞紐分析表」
  3. 當「建立樞紐分析表」的視窗彈出後
  4. 上方可以選取你需要分析的資料範圍
  5. 下方可以選擇你想存放樞紐分析表的位置
【 Excel教學 】Excel秘技 樞紐分析表(Pivot Table )Excel教學 一分鐘學識試算表常用公式/合併儲存格/VLOOKUP/if/Pivot table用法
樞紐分析表(Pivot Table )的創建步驟。(圖片來源:新傳媒資料室)

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

【 Excel教學 】Excel秘技 樞紐分析表(Pivot Table )Excel教學 一分鐘學識試算表常用公式/合併儲存格/VLOOKUP/if/Pivot table用法
(圖片來源:新傳媒資料室)

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

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

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

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

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

  1. 右鍵點選希望進行分類的數據
  2. 點選「群組」
  3. 設定開始點、結束點及間距值
【 Excel教學 】Excel秘技 樞紐分析表(Pivot Table )Excel教學 一分鐘學識試算表常用公式/合併儲存格/VLOOKUP/if/Pivot table用法
分類完成後,資料立即變得一目了然。(圖片來源:新傳媒資料室)

Excel公式2. 利用篩選器(Filters) 來篩選資料

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

新增篩選器(Filters)的方法很簡單:

  1. 選取你需要篩選的資料
  2. 點選「資料」
  3. 選取「篩選」
  4. 篩選器(Filters)就會出現於你所選擇的資料中的第一個列(Row)
【 Excel教學 】Excel秘技 篩選器(Filters)Excel教學 一分鐘學識試算表常用公式/合併儲存格/VLOOKUP/if/Pivot table用法
篩選器(Filters)的創建步驟。(圖片來源:新傳媒資料室)


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

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

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

  1. 選取希望轉置(Transpose)的資料
  2. 選取「選擇性貼上」
  3. 點選「轉置」
  4. 所需資料轉換完成!
【 Excel教學 】Excel秘技 轉置(Transpose)Excel教學 一分鐘學識試算表常用公式/合併儲存格/VLOOKUP/if/Pivot table用法
轉置(Transpose)的使用步驟。(圖片來源:新傳媒資料室)

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

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

  1. 選取需要移除的重覆資料
  2. 選取「資料」
  3. 點選「移除重覆」
  4. 點選「依照目前的選取範圍排序」,然後按「移除重覆」
  5. 移除完成後,Excel 還會很貼心的告訴你一共找到了多少個重複值,同時保留了多少個值
【 Excel教學 】Excel秘技 大量移除重覆的資料 Excel教學 一分鐘學識試算表常用公式/合併儲存格/VLOOKUP/if/Pivot table用法
大量移除重覆的資料的步驟。(圖片來源:新傳媒資料室)

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

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

  1. 選取需要增加下拉式功能表的儲存格
  2. 選取資料驗證 (Data Validation)
  3. 在儲存格內允許選擇「清單」,然後於來源中輸入選項 (需用半型,作分隔)
  4. 下拉式功能表新增成功!
【 Excel教學 】Excel秘技 增加下拉式功能表(Data Validation) Excel教學 一分鐘學識試算表常用公式/合併儲存格/VLOOKUP/if/Pivot table用法
新增下拉式功能表的步驟。(圖片來源:新傳媒資料室)

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

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

  1. 選取需要突出的資料範圍
  2. 點選「常用」
  3. 選擇「設定格式化的條件」(Conditional Formatting)
  4. 設定你所需的條件後,所有包含的資料便會根據你所選的格式顯示
【 Excel教學 】Excel秘技 設定格式化的條件(Conditional Formatting) Excel教學 一分鐘學識試算表常用公式/合併儲存格/VLOOKUP/if/Pivot table用法
所有移民都被轉換成淺綠色。(圖片來源:新傳媒資料室)

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

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

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

VLOOKUP 一共有四個參數,分別為:

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

下圖例子可見:

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

【 Excel教學 】Excel秘技 VLOOKUP Excel教學 一分鐘學識試算表常用公式/合併儲存格/VLOOKUP/if/Pivot table用法
VLOOKUP 算式示範。(圖片來源:新傳媒資料室)


我們依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))

【 Excel教學 】Excel秘技 INDEX MATCH Excel教學 一分鐘學識試算表常用公式/合併儲存格/VLOOKUP/if/Pivot table用法
INDEX MATCH 算式示範。(圖片來源:新傳媒資料室)

中間間部分的 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”)

【 Excel教學 】Excel秘技 COUNTIF Excel教學 一分鐘學識試算表常用公式/合併儲存格/VLOOKUP/if/Pivot table用法
COUNTIF 算式示範。(圖片來源:新傳媒資料室)


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

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

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

算式為:=A2&” “&B2

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

【 Excel教學 】Excel秘技 利用& 合拼儲存格資料 Excel教學 一分鐘學識試算表常用公式/合併儲存格/VLOOKUP/if/Pivot table用法
輕輕鬆鬆便可以合拼全名。(圖片來源:新傳媒資料室)

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

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

Excel公式中篩選器(Filters)有什麼好處?

如需要整理大量資料時,Excel公式中篩選器(Filters)可加快選取需要數據,做法是…詳情請看

當需要處理大量重複的資料時,可以如果善用excel?

Excel的移除重複的功能可以解決這個問題,做法是…詳情請看

圖片來源:新傳媒資料室