1. 簡介
Excel，由微軟開發的一款強大的電子表格軟體，被廣泛用於數據處理、分析、和視覺化。學會Excel的基本操作，能極大提高工作效率。在這篇文章中，我們將介紹一些 Excel 的基本技巧、常用的函數和公式，並通過實例進行講解。
2. 上班常用 Excel 技巧
2.1 使用快捷鍵
快捷鍵可以使你的操作更為迅速，常用的快捷鍵如下：
掌握一些快捷鍵可以大大提高你在 Excel 中的工作效率。以下是一些常用的 Excel 快捷鍵：
- Ctrl + C：複製選中的單元格
- Ctrl + V：粘貼剪貼板上的內容
- Ctrl + Z：撤銷上一步操作
- Ctrl + Y：重做上一步被撤銷的操作
- Ctrl + S：保存當前工作簿
- Ctrl + F：在當前工作表中進行查找
- Ctrl + H：在當前工作表中進行替換
- Alt + =：自動求選中範圍的總和
- Ctrl + Shift + Arrow key：選擇連續的單元格範圍
2.2 使用自動填充功能
Excel 的自動填充功能可以讓你迅速填充一列或一行的數據。例如，我們想在 A 列的 1 到 10 單元格中填充數字1至10，只需在 A1 單元格輸入1，然後將滑鼠放到 A1 單元格右下角，出現黑色十字後，直接向下拖拽至 A10 單元格。
2.3 使用數據驗證
數據驗證是一種功能，允許你限制在特定單元格中可以輸入的數據類型，或者從預定義的列表中選擇數據。這可以幫助你防止輸入錯誤的數據。
2.4 使用條件格式化
條件格式化可以根據單元格的值改變單元格的格式，如字體顏色、背景色、數字格式等。這可以幫助你快速識別出符合特定條件的數據。
3. Excel 常用函數和基礎公式教學
3.1 SUM 函數
SUM 函數可以對一個範圍的單元格求和。例如，=SUM(A1:A10) 將返回 A1 至 A10 單元格的數值總和。
3.2 AVERAGE 函數
AVERAGE 函數可以計算一個範圍的單元格的平均值。例如，=AVERAGE(B1:B10) 將返回 B1 至 B10 單元格的數值平均值。
3.3 COUNT 函數
COUNT 函數可以計算一個範圍的單元格中含有數值的單元格的數量。例如，=COUNT(C1:C10) 將返回 C1 至 C10 單元格中含有數值的單元格的數量。
3.4 IF 函數
IF 函數可以根據指定的條件返回不同的值。例如，=IF(D1>10, "大於10", "小於或等於10") 將根據 D1 單元格的值是否大於 10 返回不同的文字
案例實作
假設有一個工作表記錄了學生的幾門課程成績，我們可以使用上述函數進行各種數據分析：
| A | B | C | D |
|---|---|---|---|
| 學生 | 數學 | 英語 | 物理 |
| 小明 | 80 | 90 | 85 |
| 小紅 | 75 | 95 | 88 |
| 小藍 | 90 | 85 | 90 |
- 計算平均成績：
=AVERAGE(B2:D4)計算所有學生所有科目的平均成績。 - 尋找最高成績：
=MAX(B2:D4)尋找所有成績中的最高分。 - 判斷及格情況：
=IF(B2>=60, "及格", "不及格")判斷小明的數學是否及格。
一些較為複雜但在數據分析中常用到的函數包括查找與引用函數、數據統計函數以及條件函數等。以下是一個案例實作，展示這些函數如何在實際情境中運用。
免費點我下載完整數據分析案例
4. 解決不同問題的 Excel 公式與函數
4.1 將多個文本字符串連接時
這時候你可以使用CONCATENATE函數。CONCATENATE 函數用於將兩個或更多的文字字符串連接為一個文字字符串。例如，=CONCATENATE(A1, " ", B1) 將結合 A1 和 B1 單元格的內容，並在中間插入一個空格。
4.2 查找相關數據時
VLOOKUP 函數將是你的好幫手。例如，=VLOOKUP("John", A1:B10, 2, FALSE) 將在 A1:B10 的範圍中查找 “John”，並返回與 “John” 在同一行的第 2 列的數據。
4.3 計算範圍中滿足特定條件的單元格的數量時
這時，你可以使用 COUNTIF 函數。例如，=COUNTIF(A1:A10, ">20") 將計算 A1:A10 範圍內大於 20 的單元格數量。
4.4 基於一系列條件來進行計算時
這時候你可以使用 SUMIFS 函數。SUMIFS可以基於一個或多個給定條件來對範圍的數據進行求和。例如，=SUMIFS(A1:A10, B1:B10, ">20", C1:C10, "<50") 將會計算在B1:B10範圍內大於20且C1:C10範圍內小於50的對應A1:A10的數據總和。
4.5 在列表或數據集中找到最大值或最小值時
你可以使用 MAX 和 MIN 函數。例如，=MAX(A1:A10) 將返回範圍 A1:A10 中的最大值，而 =MIN(A1:A10) 則會返回這個範圍內的最小值。
4.6 對日期進行操作時
Excel 有許多專門用於處理日期的函數，如 YEAR、MONTH、DAY、TODAY 等。例如，=YEAR(A1) 可以返回 A1 單元格中日期的年份；=TODAY() 函數則返回當前日期。
有 Excel 基礎後怎麼規劃學習？數據分析職涯指引「數據分析職能地圖」
案例實作
假設我們有一個銷售數據的Excel工作表，包含以下列：
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| 日期 | 產品ID | 產品名稱 | 區域 | 銷售量 | 單價 |
| 2021/1/1 | 1001 | 蘋果 | 北部 | 120 | 10 |
| 2021/1/1 | 1002 | 香蕉 | 南部 | 150 | 5 |
| 2021/1/1 | 1003 | 橙子 | 東部 | 200 | 8 |
| … | … | … | … | … | … |
複雜函數案例實作
1. INDEX 和 MATCH 函數組合
-
- 目的：查找特定產品在特定日期的銷售量。
-
- 公式：
=INDEX(E2:E100, MATCH(1, (A2:A100="2021/1/1")*(B2:B100=1002), 0))
- 公式：
-
- 解釋：這個公式組合用於在給定條件下查找數據。MATCH 函數定位符合特定條件的行，INDEX 函數則從該行提取銷售量。
2. SUMIF 和 SUMIFS 函數
-
- 目的：基於單一條件或多重條件計算銷售總額。
-
- 公式：
-
- 單一條件：
=SUMIF(D2:D100, "北部", E2:E100)
- 單一條件：
-
- 多重條件：
=SUMIFS(E2:E100, D2:D100, "北部", B2:B100, 1001)
- 多重條件：
-
- 公式：
-
- 解釋：SUMIF 函數用於計算符合單一條件的數值總和，而 SUMIFS 可以同時基於多個條件計算總和。
3. COUNTIF 和 COUNTIFS 函數
-
- 目的：計算符合特定條件的數據數量。
-
- 公式：
-
- 單一條件：
=COUNTIF(D2:D100, "北部")
- 單一條件：
-
- 多重條件：
=COUNTIFS(D2:D100, "北部", B2:B100, 1001)
- 多重條件：
-
- 公式：
-
- 解釋：類似於SUMIF和SUMIFS，這些函數用於計算符合一個或多個條件的數據項目數量。
4. VLOOKUP 和 HLOOKUP 函數
-
- 目的：在相關表格中查找並返回數據。
-
- 公式：
-
- 垂直查找：
=VLOOKUP(1001, A2:F100, 3, FALSE)
- 垂直查找：
-
- 水平查找：
=HLOOKUP("產品名稱", A1:F100, 2, FALSE)
- 水平查找：
-
- 公式：
-
- 解釋：VLOOKUP 函數用於垂直查找表格中的數據，而 HLOOKUP 用於水平查找。這些函數適用於從大型數據集中提取特定資訊。
資料科學和 Excel 有什麼關係？
資料科學是一種使用數據為基礎的科學，透過統計、機器學習等方法，以洞察出隱藏在數據中的訊息。在這個過程中，資料的整理、處理和視覺化扮演著關鍵的角色。這裡就是Excel進入場景的地方。
以下是Excel與資料科學的關聯：
-
- 資料清理與處理：大部分的資料科學專案開始於”髒數據”，這可能包含缺失值、錯誤值或異常值。Excel提供了一個直觀的介面和強大的功能，使得我們能夠輕鬆地對這些數據進行清理和處理。
-
- 資料分析與視覺化：Excel的功能如資料透視表、條件格式設定、圖表等，讓初步的資料探索和視覺化變得簡單。這對於理解數據分佈、發現潛在趨勢或異常點很有幫助。
-
- 相容性與普及度：Excel是許多企業和組織中最常用的工具之一，許多人都對它的操作有基本的了解。因此，使用Excel作為數據科學的一部分，能確保其他成員或者利害關係人能夠理解你的工作。
然而，儘管Excel在資料科學中有其優勢，但它也有其限制。對於大規模的數據集，Excel可能會面臨效能問題。此外，對於複雜的統計分析或機器學習模型，Excel可能無法提供足夠的支援。在這種情況下，資料科學家可能需要使用更專業的工具，如R或Python。
總的來說，Excel是資料科學的重要工具之一，特別是在資料的初步處理和分析階段。而學習如何在Excel中有效地處理和分析數據，對於想要進入資料科學領域的人來說，是一個重要的技能。
