第2章_Excel_知識點筆記

Excel 知識點總結（第2章）

來自：第2章_Excel_知識點筆記，原筆記

基礎操作

狀態欄：快速查看計數/求和等數據（右鍵可配置）。
篩選（Ctrl+Shift+L）：按條件顯示數據，支持多列交集篩選。
排序：升序（A→Z/小→大）、降序（Z→A/大→小）。

數據工具

數據驗證：創建下拉框（序列輸入需用英文逗號分隔）。
刪除重復值：清理重復行。
條件格式：高亮關鍵數據。

布局與顯示

合并單元格：合并后居中（慎用，影響數據處理）。
取消網格線：視圖更簡潔。

聚合函數

1.最大值、最小值、中位數、眾數

MAX：返回一組數值中的最大值。例如，=MAX(B1:B10)會找出B1到B10單元格中的最大數值。
MIN：返回一組數值中的最小值。例如，=MIN(E1:E10)會找出E1到E10單元格中的最小數值。
MEDIAN：返回一組數值的中位數。例如，=MEDIAN(K1:K12)會計算K1到K12的中位數值。
MODE：返回一組數值中出現頻率最高的數（眾數）。例如，=MODE(L1:L8)會找出L1到L8中出現次數最多的數值。

2.平均數

AVERAGE：計算一組數值的平均值。例如，=AVERAGE(C1:C8)會計算C1到C8單元格中所有數字的平均值。
AVERAGEIF：按條件計算平均值。例如，=AVERAGEIF(I1:I30, ">=60")會計算I1到I30中≥60的數值的平均值。
AVERAGEIFS?：多條件求平均值，計算滿足多個條件的數值平均值。=AVERAGEIFS(M1:M50, N1:N50, "是", O1:O50, ">0") → 對N列為“是”且O列>0的M列數值求平均。

3.計數

COUNT：統計一組單元格中包含數字的個數。例如，=COUNT(D1:D3)會計算D1到D3單元格中有多少單元格包含數字。

僅統計包含數字的單元格?（數值、日期、時間等），忽略文本、邏輯值、錯誤值、空白單元格。

如果有多個相同的數字（如 5, 5, 10），=COUNT(D1:D3) 會返回 3（每個數字都會被計數，無論是否重復）。
COUNTIF：單條件計數。例如，=COUNTIF(F1:F20, ">50")會統計F1到F20中大于50的單元格個數。

=COUNTIF(范圍, 條件)
?范圍?：要統計的單元格區域（如 A1:A10）。
?條件?：可以是數字、文本、表達式或通配符（如 “>50”、“蘋果”、“A*”）。
例子：
=COUNTIF(B1:B20, “蘋果”) // 統計B列中等于“蘋果”的單元格數量
=COUNTIF(C1:C30, “>50”) // 統計C列中大于50的單元格數量
=COUNTIF(D1:D15, “<100”) // 統計D列中小于100的單元格數量
=COUNTIF(E1:E50, “A*”) // 統計E列中以“A”開頭的文本數量
=COUNTIF(F1:F10, “電腦”) // 統計F列中包含“電腦”的單元格數量
=COUNTIF(G1:G100, “<>”) // 統計G列中非空單元格數量
=COUNTIF(H1:H30, “>=2024/1/1”) // 統計H列中≥2024年1月1日的日期數量

注意事項：
?>不區分大小寫?： “apple” 和 “Apple” 視為相同。
?>支持通配符?：
* 匹配任意多個字符（如 “A*” 匹配 Apple、Air）。
? 匹配單個字符（如 “A?” 匹配 At，但不匹配 Air）。
忽略錯誤值?：COUNTIF 不會統計 #N/A、#VALUE! 等錯誤值。
COUNTIFS：多條件計數。 =COUNTIFS(A1:A10, ">10", B1:B10, "<20")。
COUNTA：統計非空單元格數量（包括文本和數字）。例如，=COUNTA(J1:J50)會統計J1到J50中所有非空單元格的數量。僅忽略真正空白的單元格。

4.求和

SUM：用于計算一組數值的總和。例如，=SUM(A1:A5)會計算A1到A5單元格中所有數字的和。
SUMIF：按條件求和。例如，=SUMIF(G1:G15, "蘋果", H1:H15)會計算G列中為“蘋果”時對應H列的數值之和。

?語法?：=SUMIF(條件范圍, 條件, [求和范圍])
?示例?：
=SUMIF(A1:A10, “>50”, B1:B10) → 對A列中大于50的對應B列數值求和。
=SUMIF(C1:C5, “蘋果”, D1:D5) → 對C列為“蘋果”的對應D列數值求和。
?SUMIFS?：?多條件求和，根據多個條件對范圍內的單元格求和。

語法?：=SUMIFS(求和范圍, 條件范圍1, 條件1, 條件范圍2, 條件2, …)
?示例?：
=SUMIFS(E1:E20, F1:F20, “>100”, G1:G20, “<200”) → 對F列>100且G列<200的對應E列數值求和。
=SUMIFS(H1:H10, I1:I10, “A”, J1:J10, “>=10”) → 對I列為“A”且J列≥10的對應H列數值求和。
SUMPRODUCT?：?多條件求和/計數的高級用法，可替代部分SUMIFS或COUNTIFS功能。

示例?：=SUMPRODUCT((P1:P10="是")*(Q1:Q10>10)*R1:R10) → 對P列為“是”且Q列>10的R列數值求和。

5.方差與標準差

STDEV / STDEV.P / STDEV.S：計算標準差（STDEV.P用于總體，STDEV.S用于樣本）。

例如，=STDEV.S(M1:M20)會計算M1到M20的樣本標準差。
VAR / VAR.P / VAR.S：計算方差（VAR.P用于總體，VAR.S用于樣本）。

例如，=VAR.P(N1:N25)會計算N1到N25的總體方差。

6.四分位數

QUARTILE / QUARTILE.INC / QUARTILE.EXC：返回數據集的四分位數（INC包含0和1，EXC不包含）。

例如，=QUARTILE.INC(O1:O40, 1)會計算O1到O40的第1四分位數（25%分位）。

四分位數（Quartile）是將一組數據從小到大排序后，分成四等份的臨界值：
?Q1（第1四分位）??：25% 的數據 ≤ Q1（即下四分位）
?Q2（第2四分位）??：50% 的數據 ≤ Q2（即中位數）
Q3（第3四分位）??：75% 的數據 ≤ Q3（即上四分位）
? 推薦使用 QUARTILE.INC（除非有特殊需求）。

quart 參數：
- 0：最小值（等同 MIN 函數）
- 1：Q1（25%分位）
- 2：Q2（50%分位，等同 MEDIAN）
- 3：Q3（75%分位）
- 4：最大值（等同 MAX 函數）

函數	包含范圍	適用場景	示例（計算Q1）
`QUARTILE`	同 `QUARTILE.INC`	舊版兼容（Excel 2010前）	`=QUARTILE(A1:A10, 1)`
`QUARTILE.INC`	包含最小值和最大值（0和1）	默認方法（與統計學常用一致）	`=QUARTILE.INC(A1:A10, 1)`
`QUARTILE.EXC`	排除最小值和最大值（0和1）	更嚴格的數據分析	`=QUARTILE.EXC(A1:A10, 1)`

PERCENTILE / PERCENTILE.INC / PERCENTILE.EXC：返回數據集的百分位數。

例如，=PERCENTILE.INC(P1:P100, 0.9)會計算P1到P100的90%分位值。

錯誤處理

IFERROR?：?錯誤處理，當公式返回錯誤時顯示指定值，否則返回原結果。

語法?：=IFERROR(公式, 錯誤時返回的值)
?示例?：
=IFERROR(1/0, “除零錯誤”) → 返回“除零錯誤”（原公式為#DIV/0!）。
=IFERROR(VLOOKUP(A1, B:C, 2, 0), “未找到”) → 如果查找失敗顯示“未找到”。
IFNA：?專門處理#N/A錯誤，與IFERROR類似但僅針對#N/A。

示例?：=IFNA(VLOOKUP(A1, B:C, 2, 0), "無匹配") → 僅當#N/A時顯示“無匹配”。

文本處理函數

場景	推薦函數
提取部分文本	`LEFT` / `RIGHT` / `MID`
合并或拆分文本	`TEXTJOIN` / `TEXTSPLIT`
清洗數據	`TRIM` / `CLEAN`
大小寫轉換	`UPPER` / `LOWER` / `PROPER`
查找與替換	`FIND` / `SUBSTITUTE`
數值與文本互轉	`TEXT` / `VALUE`

1. 基本文本提取與計算

LEFT：從文本左側提取指定數量的字符。

示例：=LEFT("Excel", 2) → 返回 "Ex"（提取前2個字符）。
用途：提取前綴（如姓名首字母、產品編碼前綴）。
RIGHT：從文本右側提取指定數量的字符。

示例：=RIGHT("Hello", 3) → 返回 "llo"（提取后3個字符）。
用途：提取后綴（如文件擴展名、電話號碼后幾位）。
MID：從文本中間指定位置提取字符。

示例：=MID("ABCDEF", 2, 3) → 返回 "BCD"（從第2字符開始取3位）。
用途：提取固定格式的中間部分（如身份證出生日期段）。
LEN：計算文本長度（包括空格）。

示例：=LEN("Excel") → 返回 5。
用途：校驗輸入長度（如密碼位數限制）。

2. 文本連接與拆分

CONCATENATE / CONCAT / &：合并多個文本。

示例：="A" & "B" 或 =CONCAT("A", "B") → 返回 "AB"。
用途：拼接姓名、地址等字段。
TEXTJOIN：按分隔符合并文本，可忽略空值。

示例：=TEXTJOIN("-", TRUE, "A", "", "B") → 返回 "A-B"。
用途：合并多列數據并自動跳過空白。
TEXTSPLIT（新版Excel）：按分隔符拆分文本為數組。

示例：=TEXTSPLIT("A,B,C", ",") → 返回 {"A","B","C"}。
用途：快速分列（如拆分CSV數據）。

3. 文本清洗與格式化

TRIM：刪除文本首尾空格及重復空格。

示例：=TRIM(" Excel ") → 返回 "Excel"。
用途：清理導入數據中的多余空格。
CLEAN：刪除文本中的非打印字符（如換行符）。

示例：=CLEAN(A1) → 清除A1中的亂碼。
用途：處理從網頁或數據庫導入的臟數據。
UPPER / LOWER / PROPER：轉換大小寫。
示例：
- =UPPER("excel") → "EXCEL"
- =PROPER("john doe") → "John Doe"（首字母大寫）。
  用途：標準化姓名、標題等格式。

4. 查找與替換

FIND / SEARCH：查找字符位置（FIND區分大小寫，SEARCH不區分）。
示例：
- =FIND("n", "Excel") → 返回 4（"n"在第4位）。
- =SEARCH("e", "Excel") → 返回 1（不區分大小寫）。
  用途：定位關鍵詞或分隔符位置。
SUBSTITUTE：替換指定文本。

示例：=SUBSTITUTE("A-B-C", "-", "/") → 返回 "A/B/C"。
用途：批量修改符號或關鍵詞。
REPLACE：按位置替換字符。

示例：=REPLACE("ABCD", 2, 2, "XY") → 返回 "AXYD"（從第2字符開始替換2位）。
用途：掩碼處理（如隱藏手機號中間四位）。

5. 高級文本處理

TEXT：將數值/日期格式化為指定文本樣式。

示例：=TEXT(1234.5, "$#,##0.00") → 返回 "$1,234.50"。
用途：自定義顯示格式（如金額加貨幣符號）。
VALUE：將文本格式的數字轉為數值。

示例：=VALUE("123") → 返回 123（可參與計算）。
用途：修復文本型數字導致的計算錯誤。
REPT：重復文本指定次數。

示例：=REPT("*", 5) → 返回 "*****"。
用途：快速生成填充符或簡易圖表。

匹配與邏輯函數

`VLOOKUP`

=VLOOKUP("李四", A1:C4, 3, FALSE)

VLOOKUP 是 Excel 中最常用的查找函數之一，用于在表格中垂直查找數據并返回對應值。

??“垂直查找”?指的是按列方向（從上到下）?查找數據的行為。

Excel 表格的列是垂直排列的（縱向），而 VLOOKUP 的查找范圍（表格區域）通常是多列組成的區域，函數會先在第一列中垂直搜索目標值，找到后橫向（向右）?返回對應行的其他列的值。

VLOOKUP（Vertical Lookup）：按列查找，適合列式數據。
HLOOKUP（Horizontal Lookup）：按行查找，適合行式數據?（但實際使用較少）。

🔎 核心語法

VLOOKUP(查找值, 查找范圍, 返回列號, [匹配模式])

查找值：要搜索的值（如商品編號、姓名）。
查找范圍：包含查找值和返回值的表格區域（建議用絕對引用 A1:D100）。
返回列號：從查找范圍的第1列開始數，返回值所在的列數（如第3列填 3）。
匹配模式：

FALSE 或 0：精確匹配（最常用）。

TRUE 或 1：模糊匹配（用于數值區間）。

📌 實戰案例

假設有一個表格如下（A1:C4）：

姓名 (A)	年齡 (B)	部門 ?
張三	25	銷售部
李四	30	技術部
王五	28	市場部

用 VLOOKUP 查找“李四”的部門：

=VLOOKUP("李四", A1:C4, 3, FALSE)

垂直搜索：在 A列（姓名列） 中從上到下查找“李四”。
橫向返回：找到后，向右移動到第3列（C列），返回對應的“技術部”。

?? 常見錯誤 & 解決
#N/A 錯誤：

原因：查找值不存在，或拼寫不一致（如“李四” vs “李四 ”）。
解決：用 TRIM() 清除空格，或用 IFERROR 隱藏錯誤： =IFERROR(VLOOKUP(F2, A1:D4, 4, FALSE), "未找到")

返回錯誤列：

原因：列號數錯（如工資是第4列，但誤填 3 會返回工齡）。
技巧：用 COLUMN() 動態獲取列號（如 COLUMN(D1) 返回 4）。
數據未鎖定：

錯誤：下拉公式時查找范圍變動（如 A1:D4 變成 A2:D5）。

解決：用絕對引用 A1:D4。

🚀 高效技巧

1. 反向查找（從左往右查）

VLOOKUP 只能從左向右查，若需用“工資”查“姓名”，改用：
=INDEX(A1:A4, MATCH(F2, D1:D4, 0))

2. 批量查找

下拉公式自動匹配多行數據（記得鎖定區域 A1:D4）。
結合下拉菜單：

在 F2 設置數據驗證（菜單），選擇姓名自動顯示工資。

`IF`

條件判斷（如=IF(A1>10,"達標","未達標")）。

Power Query（數據清洗）

導入數據：從工作簿、文件夾或當前區域。
處理空值：向上/向下填充。
拆分列：按分隔符或字符數拆分文本。
合并查詢：表連接（內連接、左連接等）。
追加查詢：多表上下拼接。

透視表與可視化

創建透視表：聚合數據（求和、平均值等）。
值顯示方式：差異、差異百分比、總計百分比。
切片器：交互式篩選（連接多個透視表）。
GETPIVOTDATA：動態引用透視表數據。

效率工具

格式刷：快速復制樣式（雙擊可多次使用）。
主題顏色：統一調整文檔配色。

核心技巧

數據清洗優先：使用Power Query處理重復值、空值和非標準格式。
透視表分析：快速匯總數據，結合切片器實現動態報表。
函數嵌套：如IFERROR(VLOOKUP(...),"未找到")提升公式健壯性。
避免合并單元格：影響函數和透視表計算，改用“跨越合并”優化布局。

案例分析

1-計算月環比

這個公式是一個復雜的Excel公式，結合了IFERROR、SUMIFS和數學運算。我來逐步分析它的結構和功能：

公式結構

=IFERROR(C15/SUMIFS(源數據!$E:$E,源數據!$H:$H,$A15,源數據!$I:$I,$B15,源數據!$O:$O,$H$4-1)-1,"-")

組成部分解析

SUMIFS函數：
- 作用：多條件求和
- 參數：
  - 源數據!$E:$E - 要求和的列（E列）
  - 源數據!$H:$H,$A15 - 第一個條件：H列等于A15單元格的值
  - 源數據!$I:$I,$B15 - 第二個條件：I列等于B15單元格的值
  - 源數據!$O:$O,$H$4-1 - 第三個條件：O列等于H4單元格值減1
數學運算部分：
- C15/SUMIFS(...)-1 - 用C15單元格的值除以SUMIFS的結果，然后減1
IFERROR函數：
- 作用：如果前面的計算出現錯誤（如除以零），則返回"-"而不是錯誤值

引用類型分析

絕對引用：
- 源數據!$E:$E、源數據!$H:$H等 - 列引用固定
- $H$4 - 完全固定引用H4單元格
混合引用：
- $A15、$B15 - 列固定，行相對（復制公式時行號會變）
相對引用：
- C15 - 完全相對引用

公式的實際功能

這個公式計算的是：

在"源數據"表中找出所有滿足三個條件的行：
- H列值等于當前表的A15單元格值
- I列值等于當前表的B15單元格值
- O列值等于H4單元格值減1
對這些行的E列值求和
用當前表的C15單元格值除以這個和，然后減1
如果計算過程中出現任何錯誤，顯示"-"

典型應用場景

這種公式常用于：

計算增長率或變化率（(新值/舊值)-1）
數據對比分析（如本月與上月對比）
帶有條件篩選的比率計算

潛在問題

如果SUMIFS返回0，會導致除以零錯誤（被IFERROR捕獲）
引用整列（如E:E）可能影響計算性能
條件中的$H$4-1需要確保H4是數值類型

優化建議

考慮限制數據范圍（如E2:E1000）而非整列引用
可以添加注釋說明公式的用途
如果H4是日期，確保減1的邏輯正確（可能需要使用EDATE等日期函數）

引用狀態切換

在 Excel 中，切換 絕對引用（$A $1 ） ? ? 、 ? ? 混合引用（$ A1 或 A$1） 和 相對引用（A1） 的快捷鍵是：

使用方法

選中公式中的單元格引用（例如 A1）。
按 F4 鍵（Windows）或 Command + T（Mac）：
- 第1次按：A1 → $A$1（絕對引用）
- 第2次按：$A$1 → A$1（鎖定行）
- 第3次按：A$1 → $A1（鎖定列）
- 第4次按：$A1 → A1（相對引用）
- 循環切換，直到你想要的引用方式。

示例

輸入公式 =A1，選中 A1 后按 F4：
- 第一次：=A1 → =$A$1（絕對引用）
- 第二次：=$A$1 → =A$1（鎖定行）
- 第三次：=A$1 → =$A1（鎖定列）
- 第四次：=$A1 → =A1（相對引用）

適用場景

絕對引用（$A$1）：復制公式時，引用始終不變（適用于固定參數）。
混合引用（$A1 或 A$1）：
- $A1：列固定，行可變（適用于向下填充公式）。
- A$1：行固定，列可變（適用于向右填充公式）。
相對引用（A1）：復制公式時，引用會隨位置變化（適用于動態計算）。

總結

引用類型	示例	適用場景
絕對引用	`$A$1`	固定行和列（如常量、固定參數）
混合引用（鎖定行）	`A$1`	行固定，列可變（如向右填充公式）
混合引用（鎖定列）	`$A1`	列固定，行可變（如向下填充公式）
相對引用	`A1`	行和列均可變（如動態計算）