項(xiàng)目地址：https://github.com/datawhalechina/free-excel

學(xué)習(xí)進(jìn)度：

1. 數(shù)據(jù)緣何而來(lái)
2. Excel數(shù)據(jù)格式
3. Excel的快捷操作
4. Excel的表合并
5. Excel的函數(shù) - IF函數(shù)
6. Excel的函數(shù) - 查找函數(shù)
7. Excel的函數(shù) - 動(dòng)態(tài)函數(shù)
8. Excel數(shù)據(jù)可視化
9. Excel數(shù)據(jù)透視
10. Excel數(shù)據(jù)看板

1. 數(shù)據(jù)緣何而來(lái)

1.1 了解Excel

• 工作簿
• 工作表
• 單元格

1.2 從文件導(dǎo)入數(shù)據(jù)

獲取數(shù)據(jù)

1.3 從網(wǎng)頁(yè)導(dǎo)入數(shù)據(jù)

從網(wǎng)頁(yè)導(dǎo)入數(shù)據(jù)

1.3 練習(xí)

1. 使用Excel數(shù)據(jù)導(dǎo)入功能，完成data/chap1/Titanic_train.txt數(shù)據(jù)導(dǎo)入弄慰。

導(dǎo)入文本文件

載入中

導(dǎo)入結(jié)果

2. 將工作簿文件data/chap1/Titanic_test.xlsx轉(zhuǎn)為表格數(shù)據(jù)署尤。

從表格中轉(zhuǎn)換

轉(zhuǎn)換結(jié)果

3. 將“中國(guó)銀行外匯牌價(jià)”通過(guò)網(wǎng)頁(yè)導(dǎo)入到 Excel 表中。

自網(wǎng)站導(dǎo)入數(shù)據(jù)

導(dǎo)航器

導(dǎo)入結(jié)果

2. Excel數(shù)據(jù)格式

2.1 單元格數(shù)據(jù)類(lèi)型

Excel有11種數(shù)據(jù)類(lèi)型：

• 常規(guī)
• 數(shù)字
• 貨幣
• 會(huì)計(jì)專(zhuān)用
• 短日期
• 長(zhǎng)日期
• 時(shí)間
• 百分比
• 分?jǐn)?shù)
• 科學(xué)記數(shù)
• 文本

2.1.1 數(shù)字

• 真數(shù)值默認(rèn)情況下是右對(duì)齊
• 假數(shù)值是數(shù)值型文本屬于文本时鸵，左上角有綠色三角錯(cuò)誤檢查胶逢，無(wú)法完成數(shù)字運(yùn)算操作
• 12 位數(shù)以上時(shí)，以科學(xué)計(jì)數(shù)法顯示
• Excel 自帶數(shù)據(jù)糾錯(cuò)功能饰潜，點(diǎn)擊黃色感嘆號(hào)初坠，將文本轉(zhuǎn)出數(shù)字，或者使用=VALUE()函數(shù)將文本轉(zhuǎn)成數(shù)字

數(shù)字格式

2.1.2 文本

• 主要為文本字符串

2.1.3 日期

• Excel 的日期最早是1900年1月1日彭雾，對(duì)應(yīng)的數(shù)值為1
• 通過(guò)兩日期相減可以得到日期相差的天數(shù)

日期格式

2.2 單元格格式

自定義格式的用法：https://baijiahao.baidu.com/s?id=1642006746212954122&wfr=spider&for=pc

2.3 轉(zhuǎn)換單元格數(shù)據(jù)

通過(guò)數(shù)據(jù)工具的分列功能能夠巧妙的將數(shù)據(jù)進(jìn)行轉(zhuǎn)換碟刺。

2.3.1 數(shù)值和文本相互轉(zhuǎn)換

通過(guò)分列把數(shù)值轉(zhuǎn)為文本

2.3.2 日期和文本相互轉(zhuǎn)換

通過(guò)分列把日期轉(zhuǎn)為文本

2.4 練習(xí)

1. 將data/chap1/Titanic_train工作簿中的Pclass列的數(shù)值轉(zhuǎn)文本。

見(jiàn) 2.3.1 節(jié)

2. 完成data/chap2/2.1.xlsx中案例1-3中的問(wèn)題冠跷。

見(jiàn) 2.3.2 節(jié)

3. 完成data/chap2/2.2.xlsx中案例1-2中的問(wèn)題南誊。

練習(xí)3

3. Excel的快捷操作

3.1 快捷鍵簡(jiǎn)介

3.1.1 文件相關(guān)

• Ctrl + N 創(chuàng)建一個(gè)新的 Excel 工作簿
• Ctrl + W 關(guān)閉當(dāng)前工作簿
• Ctrl + S 保存當(dāng)前的 Excel 工作簿
• F12 將當(dāng)前文件進(jìn)行另存為
• Ctrl + P 打印當(dāng)前工作表

3.1.2 通用快捷鍵

• Ctrl + Z 撤銷(xiāo)當(dāng)前操作
• Ctrl + Y 恢復(fù)撤銷(xiāo)，可以用這個(gè)快捷鍵恢復(fù)被撤消的操作
• Ctrl + C 復(fù)制所選單元格
• Ctrl + X 剪切所選單元格
• Ctrl + V 粘貼所選單元格
• Ctrl + F 彈出查找和替換對(duì)話框
• Ctrl + G 顯示定位對(duì)話框

3.1.3 表格選擇

• Ctrl + A 全選表格
• Ctrl + → 移動(dòng)到數(shù)據(jù)區(qū)域的右邊緣
• Ctrl + ← 移動(dòng)到數(shù)據(jù)區(qū)域的左邊緣
• Ctrl + ↑ 移動(dòng)到數(shù)據(jù)區(qū)域的上邊緣
• Ctrl + ↓ 移動(dòng)到數(shù)據(jù)區(qū)域的下邊緣

3.1.4 單元格編輯

• F2 編輯當(dāng)前單元格（跟雙擊單元格效果一致）
• Alt + Enter 在當(dāng)前單元格內(nèi)換行（直接按 Enter 會(huì)跳轉(zhuǎn)到下一行）
• Enter 下移一個(gè)單元格
• Shift + Enter 上移一個(gè)單元格
• Tab 右移一個(gè)單元格
• Shift + Tab 左移一個(gè)單元格
• Ctrl + Enter 輸入完成蜜托，并停留在當(dāng)前單元格
• Ctrl + ; 輸入當(dāng)前日期（顯示為年/月/日）
• Ctrl + Shift + : 輸入當(dāng)前時(shí)間 (顯示為小時(shí)/分鐘)
• Ctrl + E 快速填充

3.1.5 Excel格式化

• Ctrl + 1 設(shè)置單元格格式
• Ctrl + B 加粗或取消加粗
• Ctrl + i 加斜體或取消斜體

3.1.6 Excel公式

• F4 絕對(duì)引用和相對(duì)引用切換
• Alt + = 自動(dòng)插入求和公式

3.2 Excel快捷鍵實(shí)踐

3.2.1 自動(dòng)插入求和公式

1. Ctrl + Shift + → + ↓ 選擇求和區(qū)域
2. Alt + = 自動(dòng)插入求和公式

[Alt] + [=] 自動(dòng)插入求和公式

3.2.2 自動(dòng)進(jìn)行列差異比對(duì)

使用快捷鍵 Ctrl + \ 定位出差異的單元格

• 從左至右選擇區(qū)域抄囚，以左列為參照
• 從右至左選擇區(qū)域，以右列為參照

[Ctrl] + [\] 定位列差異

3.2.3 自動(dòng)填充

快捷鍵 Ctrl + E 進(jìn)行自動(dòng)填充

[Ctrl] + [E] 進(jìn)行自動(dòng)填充

3.3 練習(xí)

1. 完成data/chap3/3.1.xlsx工作簿中的【案例2】【案例3】的快捷鍵自動(dòng)求和任務(wù)橄务。

[Alt] + [=] 求和練習(xí)

2. 完成data/chap3/3.2.xlsx工作簿中的【案例2】【案例3】的多行快速比對(duì)任務(wù)（提示：需要選中所有待比對(duì)表格框）幔托。

[Ctrl] + [\] 多列對(duì)比

3. 完成data/chap3/3.3.xlsx工作簿中的【案例3】【案例4】的快速填充任務(wù)。

[Ctrl] + [E] 自動(dòng)填充練習(xí)

4. Excel的表合并

4.1 多表行合并

3個(gè)消費(fèi)表格蜂挪，每個(gè)客戶的消費(fèi)金額不同重挑，現(xiàn)在需要統(tǒng)計(jì)每個(gè)客戶在消費(fèi)的總金額：

1. 選擇需要匯總表的起點(diǎn)單元格
2. 選擇 Excel 中的數(shù)據(jù)欄
3. 點(diǎn)擊合并計(jì)算功能
4. 選擇合并計(jì)算函數(shù)中的求和
5. 點(diǎn)擊引用位置右側(cè)的引用項(xiàng)
6. 選擇第一個(gè)數(shù)據(jù)表所在的范圍
7. 點(diǎn)擊添加
8. 將 3 個(gè)數(shù)據(jù)表添加到所有引用位置
9. 選擇標(biāo)簽位置信息
10. 點(diǎn)擊完成

多表行合并

4.2 多表列合并

3個(gè)不同的消費(fèi)表格，每個(gè)客戶的消費(fèi)金額不同棠涮，現(xiàn)在需要你統(tǒng)計(jì)每個(gè)客戶在3個(gè)不同消費(fèi)的總金額谬哀，操作與4.1類(lèi)似：

多表列合并

4.3 練習(xí)

1. 選擇data/chap3/4.1.xlsx工作簿中的【案例1】得到不同客戶的消費(fèi)次數(shù)。

練習(xí) 2

2. 選擇data/chap3/4.1.xlsx工作簿中的【案例2】得到不同客戶的不同項(xiàng)目的消費(fèi)次數(shù)严肪。

練習(xí) 2

5. Excel的函數(shù) - IF函數(shù)

5.1 IF函數(shù)

=IF(logical_test, [value_if_true], [value_if_false])

【案例1】需要對(duì)客戶的原始得分進(jìn)行校正史煎，當(dāng)客戶的原始得分為負(fù)時(shí)谦屑，校正為0。

=IF(B2<0,0,B2)

快速填充

• 選擇C2單元格右下角篇梭，當(dāng)鼠標(biāo)變成黑色十字氢橙，向下拖拽，對(duì)剩下的列進(jìn)行填充;

  
  
  十字拖拽

• 選擇C2單元格右下角恬偷，當(dāng)鼠標(biāo)變成黑色十字悍手，雙擊，對(duì)剩下的列進(jìn)行填充;

  
  
  雙擊十字
• 以C2單元格開(kāi)始選擇需要填充公式的區(qū)域袍患，進(jìn)入公式編輯狀態(tài)坦康，使用快捷鍵Ctrl + Enter進(jìn)行批量填充。
  
  [CTRL]+[Enter]

5.2 IF函數(shù)與復(fù)雜邏輯

=AND([logical1], [logical2], ...)
=OR([logical1], [logical2], ...)

【案例2】現(xiàn)在有客戶的1协怒，2月消費(fèi)金額涝焙，如果客戶2個(gè)月的消費(fèi)金額都超過(guò)400，那么就認(rèn)為這個(gè)客戶為高消費(fèi)客戶孕暇。

=IF(AND(B2>400,C2>400),"高消費(fèi)客戶","")

IF函數(shù)與邏輯函數(shù)

5.3 IFS函數(shù)

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3; ...)

【案例3】有員工的相關(guān)考核分，考核得分在4.8分以上赤兴，優(yōu)秀妖滔；考核得分在3.5-4.7分，良好桶良；考核得分在0-3.4分座舍，一般。

=IFS(B2<=3.4,"一般",AND(B2>=3.5,B2<=4.7),"良好",B2>=4.8,"優(yōu)秀")

IFS函數(shù)

5.4 練習(xí)

1. 選擇data/chap5/5.1.xlsx工作簿中的【案例1】陨帆，將客戶原始得分為1以下的校正為0曲秉。

練習(xí)1

2. 選擇data/chap5/5.1.xlsx工作簿中的【案例2】，客戶1月2月中只要有1個(gè)月消費(fèi)超過(guò)400疲牵，即為"高消費(fèi)客戶"承二。

練習(xí)2

3. 選擇data/chap5/5.1.xlsx工作簿中的【案例3】，能否不使用IFS函數(shù)纲爸，使用IF函數(shù)通過(guò)嵌套完成相應(yīng)的任務(wù)亥鸠。

練習(xí)3

6. Excel的函數(shù) - 查找函數(shù)

6.1 VLOOKUP

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

1. 【案例1】根據(jù)崗位編號(hào)，得到該員工的應(yīng)發(fā)工資识啦。

=VLOOKUP(K2,A2:H50,8,FALSE)

1. K2 單元格為要查找的值
2. A2:H50 是查找的范圍
3. 應(yīng)發(fā)工資在查找范圍中的第 8 列
4. FALSE 為精確匹配

案例1

2. 【案例2】根據(jù)崗位編號(hào)负蚊，得到該員工的應(yīng)發(fā)工資。

要查找的“應(yīng)發(fā)工資”在查詢列“員工編號(hào)”的右邊
VLOOKUP函數(shù)查找的區(qū)域在被查找值的左側(cè)

列變換技巧： IF({1,0},...)

=VLOOKUP(J2,IF({1,0},H2:H19,G2:G19),2,FALSE)

1. J2 是要查找的值所在單元格
2. IF({1,0},H2:H19,G2:G19) 是查找區(qū)域颓哮，使用IF函數(shù)調(diào)換H家妆，G兩列的順序
3. 返回值所在列為查找區(qū)域的第2列，即G列（H冕茅，G列順序已交換）
4. FALSE 為精確匹配

案例2

3. 【案例3】根據(jù)性別與部門(mén)信息伤极，獲得員工編號(hào)蛹找。

技巧：通過(guò)&拼接兩列的信息

=VLOOKUP(J2&K2,IF({1,0},C2:C20&D2:D20,A2:A20),2,0)

案例3

4. 【案例4】當(dāng)不知道具體的信息，需要進(jìn)行模糊查詢塑荒。

使用通配符進(jìn)行模糊查詢：

Excel中的通配符匹配規(guī)則

=VLOOKUP("*"&K2,A2:B20,2,0)

1. "*"&K2 匹配以K2 單元格的值結(jié)尾的值
2. A2:B20 查找范圍
3. 返回的值在查找范圍的第2列
4. 0 即是 TRUE熄赡，模糊查找

案例4

6.2 XLOOKUP

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

其中，

• 第1~3個(gè)參數(shù)跟LOOKUP的參數(shù)非常接近齿税，但將「查找區(qū)域」和「結(jié)果區(qū)域」獨(dú)立出來(lái)了
• 第4參數(shù)（可選參數(shù)）：if_not_found彼硫，找不到結(jié)果，就返回第四參數(shù)凌箕，如果省略第四參數(shù)函數(shù)默認(rèn)返回#N/A這個(gè)錯(cuò)誤值
• 第5參數(shù)（可選參數(shù)）：match_mode拧篮，指定匹配類(lèi)型
  • 參數(shù)為：0 ，精確匹配牵舱，未找到結(jié)果串绩，返回 #N/A。 這是默認(rèn)選項(xiàng)芜壁。
  • 參數(shù)為：-1礁凡，近似匹配，未找到結(jié)果慧妄，返回下一個(gè)較小的項(xiàng)顷牌。
  • 參數(shù)為：1，近似匹配塞淹，未找到結(jié)果窟蓝，返回下一個(gè)較大的項(xiàng)。
  • 參數(shù)為：2 饱普，通配符匹配
• 第6參數(shù)（可選參數(shù)）：search_mode, 指定要使用的搜索模式
  • 參數(shù)為：1运挫，從第一項(xiàng)開(kāi)始執(zhí)行搜索。 這是默認(rèn)選項(xiàng)
  • 參數(shù)為：-1套耕，從最后一項(xiàng)開(kāi)始執(zhí)行反向搜索
  • 參數(shù)為：2谁帕，根據(jù) lookup_array 按升序排序的二進(jìn)制搜索。 如果未排序箍铲，將返回?zé)o效結(jié)果
  • 參數(shù)為：-2雇卷，根據(jù)lookup_array 按降序排序的二進(jìn)制搜索。 如果未排序颠猴，將返回?zé)o效結(jié)果

1. 【案例1】

=XLOOKUP(K2,A1:A50,H1:H50)

1. K2 單元格為要查找的值
2. A1:A50 是查找的列
3. H1:H50 是結(jié)果（應(yīng)發(fā)工資）所在列
4. 默認(rèn)查找不到則返回#N/A
5. 默認(rèn)為精確匹配

案例1

2. 【案例2】

=XLOOKUP(J2,H1:H19,G1:G19)

案例2

3. 【案例3】

=XLOOKUP(J2&K2,C1:C20&D1:D20,A1:A20)

案例3

4. 【案例4】

=XLOOKUP("*"&K2,A2:A20,B2:B20,,2)

1. "*"&K2 匹配以K2 單元格的值結(jié)尾的值
2. A2:A20 查找列
3. B2:B20 返回值所在列
4. 若查找不到結(jié)果則返回#N/A
5. 參數(shù) 2 表示通配符匹配

案例4

5. 【案例5】姓名6在數(shù)據(jù)表中出現(xiàn)了2次关划，想知道他的最后銷(xiāo)售額應(yīng)該怎么操作？

案例5

=XLOOKUP(H2,B1:B20,F1:F20,0,0,-1)

1. 查找目標(biāo)為 H2 單元格的值

2. B1:B20 為查找列

3. F1:F20 為結(jié)果所在列

4. 若找不到結(jié)果翘瓮，輸出0

5. 參數(shù) 0 表示精確查找

6. 參數(shù) -1 表示反向搜索（此處從下到上）

7. 【案例6】這里有4個(gè)人贮折，現(xiàn)在想知道這4個(gè)人的最大銷(xiāo)售額。

=MAX(XLOOKUP(H2:H5,B2:B20,F2:F20))

案例6

7. 【案例7】當(dāng)數(shù)據(jù)集以橫向形式時(shí)资盅，如果查詢到需要的數(shù)據(jù)调榄。

橫向數(shù)據(jù)的查詢方法與縱向數(shù)據(jù)并無(wú)太大區(qū)別踊赠。

=XLOOKUP(B6,B2:L2,B1:L1)

案例7

7. Excel函數(shù) - 動(dòng)態(tài)函數(shù)

7.1 FILTER函數(shù)

FILTER(array, include, [if_empty])

FILTER函數(shù)

7.1.1 單條件篩選

【例7.1】獲得湖北區(qū)的相關(guān)數(shù)據(jù)。

=FILTER(A2:G30,C2:C30="湖北區(qū)")

FILTER函數(shù) - 單條件篩選

7.1.2 返回選定的列

【例7.2】如果在實(shí)際的任務(wù)中不需要所有列每庆，只需要其中幾行筐带，那么應(yīng)該怎么操作？

=FILTER(F2:G30,C2:C30="湖北區(qū)")

FILTER函數(shù) - 返回選定的列

現(xiàn)在需要知道湖北區(qū)的最大銷(xiāo)售金額是多少?

=MAX(FILTER(F2:F30,C2:C30="湖北區(qū)"))

FILTER+MAX函數(shù) - 湖北區(qū)的最大銷(xiāo)售金額

7.1.3 多條件篩選

ANlD邏輯：FILTER(array, (range1=criteria1) * (range2=criteria2), "No results")
OR邏輯：FILTER(array, (range1=criteria1) + (range2=criteria2), "No results")

,
【例7.3】計(jì)算湖北區(qū)缤灵，湖南區(qū)中最大的銷(xiāo)售金額伦籍。

=MAX(FILTER(F2:F30,(C2:C30="湖北區(qū)")+(C2:C30="湖南區(qū)")))

FILTER函數(shù) - 多條件篩選1

【例7.4】計(jì)算湖北區(qū)商品2的最大銷(xiāo)售金額。

=MAX(FILTER(F2:F30,(C2:C30="湖北區(qū)")*(D2:D30="商品2")))

FILTER函數(shù) - 多條件篩選2

7.2 SUMIF函數(shù)

SUMIF(range, criteria, [sum_range])

SUMIF函數(shù)

【例1】統(tǒng)計(jì)數(shù)學(xué)成績(jī)?cè)?0分以上的成績(jī)之和腮出。

=SUMIF(B:B,">=90",B:B)

• 第1參數(shù)為條件區(qū)域帖鸦，因?yàn)橐袛鄶?shù)學(xué)成績(jī)，B:B表示選擇B列胚嘲，也可以為實(shí)際區(qū)域B2:B12
• 第2參數(shù)為判定條件作儿，判定條件為大于等于90
• 第3參數(shù)為實(shí)際求和區(qū)，這是選填參數(shù)

例1

【例2】統(tǒng)計(jì)小米系列的產(chǎn)品銷(xiāo)售額是多少馋劈？

=SUMIF(B:B,"小米*",C:C)

例2

• "小米*"匹配所有的小米系產(chǎn)品
• C:C為統(tǒng)計(jì)的目標(biāo)列

【例3】統(tǒng)計(jì)1-3月的預(yù)計(jì)銷(xiāo)售額和實(shí)際銷(xiāo)售額攻锰。

=SUMIF($B$2:$G$2,H$2,B3:G3)
=SUMIF($B$2:$G$2,I$2,B3:G3)

$符號(hào)表示絕對(duì)引用，字母前面加$表示絕對(duì)引用列妓雾，數(shù)字前加$表示絕對(duì)引用行口注，2 個(gè)都加即表示絕對(duì)引用該單元格 。

例3(1)

例3(2)

7.3 SUMIFS函數(shù)

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

【例】統(tǒng)計(jì)語(yǔ)文成績(jī)?cè)?0分以上君珠，90分以下同學(xué)的數(shù)學(xué)成績(jī)之和。

=SUMIFS(C2:C13,B2:B13,">80",B2:B13,"<90")

SUMIFS函數(shù)

7.4 SUBTOTAL函數(shù)

SUBTOTAL(function_num,ref1,[ref2],...)

SUBTOTAL函數(shù)

`Function_num `

SUM函數(shù)與SUBTOTAL對(duì)比

7.5 練習(xí)

1. 打開(kāi)data/chap7/7.1xlsx中的【案例1】計(jì)算河北區(qū)中商品1的最小銷(xiāo)售額娇斑。

=MIN(FILTER(F2:F30,(C2:C30="河北區(qū)")+(D2:D30="商品1")))

練習(xí)1

2. 打開(kāi)data/chap7/7.1xlsx中的【案例1】計(jì)算湖北中什么渠道下的什么商品具有最高銷(xiāo)售額策添。

=FILTER(D2:E30,(C2:C30="湖北區(qū)")*(F2:F30=LARGE(F2:F30,1)))

練習(xí)2

3. 打開(kāi)data/chap7/7.2xlsx中的【案例1】語(yǔ)文成績(jī)?cè)?0分以上的同學(xué)成績(jī)之和。

=SUMPRODUCT(array1, [array2], [array3], ...)

=SUMPRODUCT((B2:G12)*(C2:C12>80))

練習(xí)3

4. 打開(kāi)data/chap7/7.2xlsx中的【案例2】統(tǒng)計(jì)所有蘋(píng)果系列產(chǎn)品的銷(xiāo)售額毫缆。

=SUMIF(B:B, "蘋(píng)果*", C:C)

練習(xí)4

5. 打開(kāi)data/chap7/7.2xlsx中的【案例5】完成所有手機(jī)的銷(xiāo)售額求和唯竹。

=SUMIF($B$1:$K$1,"*手機(jī)",B2:J2)
=SUMIF($B$1:$K$1,"*筆記本",B2:J2)

練習(xí)5

8. Excel數(shù)據(jù)可視化

8.1 數(shù)據(jù)條

【例】為銷(xiāo)售金額繪制數(shù)據(jù)條。

【開(kāi)始】 -> 【條件格式】-> 【數(shù)據(jù)條】

數(shù)據(jù)條

如果想僅顯示數(shù)據(jù)條苦丁，不顯示數(shù)字：

選擇要修改的區(qū)域 -> 【條件格式】 -> 【管理規(guī)則】 -> 雙擊要修改的【規(guī)則】 -> 勾選【僅顯示數(shù)據(jù)條】

僅顯示數(shù)據(jù)條

8.2 條件單元格格式

【例】標(biāo)注沒(méi)有超過(guò)今天的截止日期浸颓。

1. 選擇需要標(biāo)注的范圍
2. 【條件格式】 -> 【新建規(guī)則】 -> 【使用公式確定要設(shè)置格式的單元格】
3. 填入公式 =B2>TODAY()
4. 【格式】設(shè)置格式，如【填充】選擇填充顏色
5. 【確定】x3

輸入的公式是一個(gè)被模擬的對(duì)象旺拉，它應(yīng)該針對(duì)所選區(qū)域的左上角第一個(gè)單元格來(lái)設(shè)置产上。Excel將根據(jù)其引用形式（絕對(duì)引用與相對(duì)引用）自動(dòng)復(fù)制運(yùn)用到所選區(qū)域的其它單元格。

條件單元格格式

8.3 迷你圖

迷你圖是適合放在工作表中的單個(gè)單元格內(nèi)的小型圖表

【例】現(xiàn)知道不同業(yè)務(wù)員1-12月的銷(xiāo)售額蛾狗，獲取銷(xiāo)售額最差月份以及業(yè)務(wù)員的銷(xiāo)售額的變化趨勢(shì)晋涣。

1. 確定銷(xiāo)售額最差月份：

=XLOOKUP(MIN(B2:M2),B2:M2,$B$1:$M$1)

XLOOKUP

2. 生成迷你圖

1）選擇插入范圍
2）【插入】
3）【迷你圖】 -> 【折線】
4）選擇數(shù)據(jù)范圍

生成迷你圖

8.4 練習(xí)

1. 選擇data/chap8/8.2.xlsx工作簿中的【案例1】，對(duì)拜訪客戶數(shù)大于5以上的業(yè)務(wù)員進(jìn)行黃色填充沉桌。

練習(xí)1

9. Excel數(shù)據(jù)透視

9.1 Excel數(shù)據(jù)透視表

數(shù)據(jù)透視表是計(jì)算谢鹊、匯總和分析數(shù)據(jù)的強(qiáng)大工具算吩，可幫助了解數(shù)據(jù)中的對(duì)比情況、模式和趨勢(shì)佃扼。

1. 【插入】
2. 【數(shù)據(jù)透視表】
3. 選擇表格或區(qū)域
4. 選擇放置透視表的位置
5. 如果想看各個(gè)地區(qū)每月的銷(xiāo)售金額及匯總：

• 把【區(qū)域】放置在【列】
• 把【日期】放置在【行】
• 把【金額】放置到【值】（默認(rèn)是匯總求和）

Excel數(shù)據(jù)透視表示例

若要添加篩選條件偎巢，可以使用【切片器】，如查看不同店員的銷(xiāo)售金額匯總：

1. 在【數(shù)據(jù)透視表分析】欄中
2. 選擇【插入切片器】
3. 選擇篩選項(xiàng)【員工】
4. 點(diǎn)擊切片便可查看不同員工的銷(xiāo)售金額
5. 如果要查看多個(gè)員工的可點(diǎn)擊右上角的【多選】

切片器示例

9.2 Excel數(shù)據(jù)透視圖

在數(shù)據(jù)透視表的基礎(chǔ)上兼耀，可通過(guò)數(shù)據(jù)透視圖向數(shù)據(jù)添加數(shù)據(jù)可視化压昼。

步驟類(lèi)似于數(shù)據(jù)透視表

Excel數(shù)據(jù)透視圖示例

若要更改圖表類(lèi)型，可以在圖表范圍點(diǎn)擊右鍵翠订，選擇【更改系列圖表類(lèi)型】巢音，然后在對(duì)話框中選擇需要的圖表類(lèi)型。

更改系列圖表類(lèi)型示例

同樣也可以添加【切片器】來(lái)篩選圖表的曲線尽超。

透視圖與切片器示例

10. Excel數(shù)據(jù)看板

10.1 業(yè)務(wù)背景

• 數(shù)據(jù)源：某廠家在淘寶官撼、拼多多、京東這3個(gè)電商銷(xiāo)售平臺(tái)的銷(xiāo)售數(shù)據(jù)
• 目標(biāo)：做一個(gè)數(shù)據(jù)周報(bào)看板似谁，顯示商品在不同渠道的每周銷(xiāo)售情況

數(shù)據(jù)源

10.2 業(yè)務(wù)分析

1. 確定每天日期所在周數(shù)與每周的第幾天
2. 根據(jù)確定周數(shù)對(duì)銷(xiāo)售金額進(jìn)行匯總
3. 對(duì)銷(xiāo)售金額進(jìn)行動(dòng)態(tài)展示

10.3 實(shí)現(xiàn)過(guò)程

1. 確定日期的周數(shù)與星期

• WEEKNUM函數(shù)返回日期所在年份的第幾周

WEEKNUM函數(shù)語(yǔ)法

=WEEKNUM(B2,2)

WEEKNUM函數(shù)示例

• WEEKDAY函數(shù)返回?cái)?shù)字代表日期對(duì)應(yīng)星期幾

WEEKDAY函數(shù)語(yǔ)法

=WEEKDAY(B2,11)

WEEKDAY函數(shù)示例

2. 銷(xiāo)售基礎(chǔ)數(shù)據(jù)整理

1）本周開(kāi)始日期

回憶一下XLOOKUP函數(shù)

XLOOKUP函數(shù)語(yǔ)法

=XLOOKUP(C2,數(shù)據(jù)源!H1:H50001,數(shù)據(jù)源!B1:B50001)

• C2為搜索的周數(shù)
• 數(shù)據(jù)源!H1:H50001表示在“數(shù)據(jù)源”sheet中的H列的搜索范圍
• 數(shù)據(jù)源!B1:B50001表示在“數(shù)據(jù)源”sheet中的B列返回值的范圍

XLOOKUP函數(shù)返回本周開(kāi)始日期

注：需要把單元格格式設(shè)置為【日期】格式

2）本周結(jié)束日期

=XLOOKUP(C2,數(shù)據(jù)源!H1:H50001,數(shù)據(jù)源!B1:B50001,0,0,-1)

• 最后一個(gè)參數(shù)-1表示從反方向（從下而上）搜索傲绣，因?yàn)槿掌谑巧蚺帕校詴?huì)返回該周的最后一天的日期

XLOOKUP函數(shù)返回本周結(jié)束日期

3）上周開(kāi)始與結(jié)束日期

=C3-7
=C4-7

• 直接使用本周的起始的兩個(gè)日期減去7天得到

上周開(kāi)始日期

上周結(jié)束日期

4）本周與上周銷(xiāo)售額

回憶一下SUMIFS函數(shù)巩踏，計(jì)算本周銷(xiāo)售額

SUMIFS函數(shù)語(yǔ)法

=SUMIFS(數(shù)據(jù)源!$F:$F,數(shù)據(jù)源!$B:$B,">="&$C$3,數(shù)據(jù)源!$B:$B,"<="&$C$4)

• 數(shù)據(jù)源!$F:$F表示求和的范圍（下單金額）
• 數(shù)據(jù)源!$B:$B,">="&$C$3,數(shù)據(jù)源!$B:$B,"<="&$C$4 表示介于本周開(kāi)始日期（C3）與本周結(jié)束日期（C4）之間

本周銷(xiāo)售額

同理計(jì)算上周銷(xiāo)售額

=SUMIFS(數(shù)據(jù)源!$F:$F,數(shù)據(jù)源!$B:$B,">="&$E$3,數(shù)據(jù)源!$B:$B,"<="&$E$4)

上周銷(xiāo)售額

5）本周與上周訂單數(shù)

類(lèi)似SUMIFS函數(shù)秃诵，使用COUNTIFS函數(shù)計(jì)算訂單數(shù)

COUNTIFS函數(shù)語(yǔ)法

=COUNTIFS(數(shù)據(jù)源!$B:$B,">="&$C$3,數(shù)據(jù)源!$B:$B,"<="&$C$4)

本周訂單數(shù)

=COUNTIFS(數(shù)據(jù)源!$B:$B,">="&$E$3,數(shù)據(jù)源!$B:$B,"<="&$E$4)

上周訂單數(shù)

6）比較銷(xiāo)售額和訂單數(shù)的變化

使用TEXT函數(shù)輸出百分比格式

=TEXT(((C5-E5)/E5)100,"0.00")&"%"
=TEXT(((C6-E6)/E6)100,"0.00")&"%"

銷(xiāo)售額變化

訂單數(shù)變化

• 設(shè)置單元格條件格式：當(dāng)本周銷(xiāo)售額/訂單數(shù)比上周銷(xiāo)售額/訂單數(shù)大時(shí)，顯示為紅色塞琼；反之為綠色菠净。

設(shè)置單元格顏色規(guī)則

單元格條件格式

3. 周銷(xiāo)售數(shù)據(jù)變化趨勢(shì)

1）統(tǒng)計(jì)一周每天的下單金額

=SUMIFS(數(shù)據(jù)源!$F:$F,數(shù)據(jù)源!$B:$B,">="&$C$3,數(shù)據(jù)源!$B:$B,"<="&$C$4,數(shù)據(jù)源!$I:$I,"="&計(jì)算過(guò)程!G3)

一周每天的下單金額

2）統(tǒng)計(jì)一周每天的下單數(shù)

=COUNTIFS(數(shù)據(jù)源!$B:$B,">="&$C$3,數(shù)據(jù)源!$B:$B,"<="&$C$4,數(shù)據(jù)源!$I:$I,"="&計(jì)算過(guò)程!G3)

一周每天的下單數(shù)

3）繪制圖表

下單金額與下單數(shù)的折線圖

4. 周銷(xiāo)售渠道數(shù)據(jù)整理

1）不同渠道的銷(xiāo)售金額

=SUMIFS(數(shù)據(jù)源!$F:$F,數(shù)據(jù)源!$E:$E,Q3,數(shù)據(jù)源!$B:$B,">="&$C$3,數(shù)據(jù)源!$B:$B,"<="&$C$4)

不同渠道的銷(xiāo)售金額

2）不同渠道的下單數(shù)

=COUNTIFS(數(shù)據(jù)源!$E:$E,Q3,數(shù)據(jù)源!$B:$B,">="&$C$3,數(shù)據(jù)源!$B:$B,"<="&$C$4)

不同渠道的下單數(shù)

待續(xù)未完....