第一講：認識Excel

1.簡介

excel能做什么？

• 數(shù)據(jù)存儲 → 數(shù)據(jù)處理 → 數(shù)據(jù)分析 → 數(shù)據(jù)呈現(xiàn)

excel界面

補充：Excel數(shù)據(jù)分析步驟
①提出問題：明確自己需要通過數(shù)據(jù)分析解決什么問題
②理解數(shù)據(jù)：理解數(shù)據(jù)各個字段的含義
③數(shù)據(jù)清洗 

這時候就需要用到Excel了
首先要對數(shù)據(jù)進行一次大清洗，將多余重復的數(shù)據(jù)篩選清除，將缺失數(shù)據(jù)補充完整，將錯誤數(shù)據(jù)糾正或刪除
這時要用到一系列函數(shù)

• 數(shù)據(jù)重復：countif
• 缺失數(shù)據(jù)：if，and，or，嵌套函數(shù)等
• 數(shù)據(jù)抽樣：left，right，concatenate，vlookup
• 數(shù)據(jù)計算：average，sum，max，min，date，if
• 數(shù)據(jù)分組：vlookup，近似匹配，search
• 數(shù)據(jù)抽樣：rand

④構(gòu)建模型：運用函數(shù)，數(shù)據(jù)透視表，分析工具庫（描述統(tǒng)計分析） 

⑤數(shù)據(jù)可視化

根據(jù)數(shù)據(jù)關(guān)系選擇圖表

• 成分：餅圖，柱形圖，條形圖，瀑布圖
• 排序：柱形圖，條形圖，氣泡圖，帕累托圖
• 時間序列：折線圖，柱形圖
• 相關(guān)性：散點圖，柱形圖，對稱條形圖（旋風圖），散點圖，氣泡圖
• 多重數(shù)據(jù)比較：雷達圖

圖表注意事項

• 信息完整：圖表標題，單位，圖例，腳注，來源等
• 避免無意義的圖表
• 一表反映一個觀點
• 只選對的不選復雜的圖表
• 標題一句話闡述清楚反映觀點

2.一些重要概念

幾種常用文件類型

• XLS工作簿文件：2003之前的格式
• XLSX 工作簿文件：2007到2010之后新版本的文件格式
• XLW工作區(qū)文件：類似快捷方式，數(shù)據(jù)隨著原文件變動，可進入【視圖 - 新建窗口】熟悉該選項卡

工作簿，工作表，單元格

• 新建工作表：點擊右側(cè)小加號，雙擊重命名
• 更改工作表標簽顏色：右鍵
• 交換兩列數(shù)據(jù)的位置：選中目標列 → 按住shift鍵 → 拖動目標列至目標位置
• 插入/刪除多個工作表：左鍵選中第一個工作表，按住shift，選中最后一個工作表，右鍵插入/刪除
• 插入多行/列：選中n行/n列，右鍵插入
• 交換兩列數(shù)據(jù)位置：選中目標列，鼠標放置右邊線處，按住shift鍵拖動目標列至目標位置
• 調(diào)整行高列寬：選中多列 → 鼠標放置右邊線雙擊 → 自動調(diào)整多列寬度；選擇多列 → 手動調(diào)整列寬度（也可以同時調(diào)整多行寬度，并且都是被一致調(diào)整的）
• 框選多行：名稱框中輸入（起始行：目標行）

3.使用小工具，凍結(jié)窗格，填充柄，查找和替換

凍結(jié)窗格

• 定義：依據(jù)當前被選中單元格鎖定表格行與列，滾屏時，被凍結(jié)的標題行總顯示在上面，標題列顯示在左面
• 位置：選項卡 → 視圖 → 凍結(jié)窗格
• 凍結(jié)首行：視圖 - 凍結(jié)窗格 - 凍結(jié)首行（即可實現(xiàn)滾動表格，而首行不動）
• 凍結(jié)多行：選擇下一行單元格（以凍結(jié)前3行為例，選中第4行首個單元格，選擇凍結(jié)拆分窗格命令）
• 同時凍結(jié)行與列：選擇交界處單元格，凍結(jié)窗格。選中某一單元格，選擇凍結(jié)拆分窗格命令，被選中的單元格符合以下規(guī)律：總是凍結(jié)被選中單元格上方和左側(cè)的單元格，下方和右側(cè)單元格是可變動范圍

填充柄

• 定義：鼠標放置在單元格右下方變成黑色實心加號拖拽自動填充單元格
• 用法：選中n個單元格 → 加號拖拽。
• 拖拽規(guī)律為序列填充，按住Ctrl再拖拽可實現(xiàn)復制填充
• 自定義填充順序：文件 - 選項 - 高級 - 編輯自定義列表 - 輸入序列添加
• 其它格式拖拽：選中單元格，移動至右下角按住右鍵拖拽會彈出填充快捷菜單，可以選擇想要的填充格式

小技巧

• 快速到達表格最前&最后：選中單元格，在表格的上邊線雙擊到達表格最前，表格下邊線雙擊到達表格最后
• 顯示今天日期快捷鍵：Ctrl + : ，直接向下拖拽默認序列拖拽

第二講：Excel單元格格式設(shè)置

1.使用單元格格式工具美化表格

“設(shè)置單元格格式”對話框在哪里？

• 選中單元格 - 右鍵 - 設(shè)置單元格格式
• 開始選項卡 - 單元格功能區(qū)

合并單元格

• 多個小單元格合并成一個大單元格，常用于標題
• 選中多個單元格 - 開始選項卡 - 合并后居中
• 合并多行：選中多個單元格 - 開始選項卡 - 跨行合并（避免用上述操作合并3次）

邊框線

• 若不設(shè)置，打印不會有邊框
• 選中整個表格區(qū)域的單元格 → 開始選項卡 → 邊框 → 選擇所有框線
• 如需設(shè)置邊框格式，選中單元格區(qū)域后，右擊選擇設(shè)置單元格格式

斜線

• 設(shè)置斜線：在單元格內(nèi)右鍵 - 設(shè)置單元格格式 - 邊框 - 選擇斜線
• 斜線雙表頭（通過雙行文字實現(xiàn)）：表頭1單元格設(shè)置斜線 - 雙擊單元格 - 寫表頭2 - alt + enter - 左右對齊，空格設(shè)置格式 - 回車
• 為什么應(yīng)用空格調(diào)整而不直接設(shè)置右對齊和左對齊？因為對齊是針對整個單元格內(nèi)容，不能同時設(shè)置一行左對齊，另一行右對齊
• 如果需要三個斜線：插入 - 形狀 - 直線 - 鼠標繪制斜線
• 填充文字：通過雙航文字實現(xiàn)，回車（alt + enter）分行，文字左對齊，第一行輸入空格將文字右對齊

格式刷

• 先在已經(jīng)完成格式設(shè)置的表格里應(yīng)用格式刷，可以刷到另一個新表格，就會應(yīng)用一樣的格式
• 雙擊格式刷按鈕，可以保持格式刷狀態(tài)，刷新多個單元格的格式，直到按下esc退出

2.單元格數(shù)據(jù)格式

定義：在Excel當中，會去掉無意義的數(shù)字格式。比如，輸入2400.00，會直接省略成2400，輸入007，會變成7。所以需要設(shè)置單元格數(shù)字格式

路徑：選中單元格 - 右鍵設(shè)置單元格格式 - 可以設(shè)置不同的數(shù)字類別（但數(shù)值永遠是數(shù)值，只是顯示格式改變了）

如果一組數(shù)字是文本格式，那么這組數(shù)字不能運算，但仍然可以把它改成數(shù)字：選中文本格式的數(shù)字，然后這些文本格式數(shù)字的左上角會出現(xiàn)黃色棱形感嘆號，點擊這個感嘆號，選擇“轉(zhuǎn)換成數(shù)字”，就可以轉(zhuǎn)換成數(shù)字了

自定義數(shù)字格式：可以實現(xiàn)在所有數(shù)值前面或后面加內(nèi)容，如在數(shù)值后加“元”字，并且這些數(shù)值還可以做相加等運算（因為設(shè)置格式不改變值）

3.使用“分列”工具

將一組txt內(nèi)容復制黏貼到Excel表格，數(shù)據(jù)會全部在A列，此時找到數(shù)據(jù)選項卡 - 分列，根據(jù)實際情況選擇通過【分隔符號】或者【固定寬度】分列

一般是通過【分隔符號】來分列，找到數(shù)據(jù)里可以用來分隔的符號

如果一次分列沒有完成，可以再次找到能夠分隔的符號，進行二次分列

日期是以文本格式輸入的，無法通過“設(shè)置單元格格式”改為其他格式，可以通過分列的第3步修改列數(shù)據(jù)格式，修改成常規(guī)或者日期，修改成功后就可以修改格式了

第三講：查找，替換與定位

1.查找與替換

快捷鍵：查找Ctrl + F ，替換Ctrl + H

補充：替換選項卡里有 “選項” 功能，可選擇 “單元格匹配”，避免一些不必要的替換

按值查找

• 開始選項卡 - 查找和選擇
• 替換選項卡里有“選項”功能，選擇“單元格匹配”，避免一些不必要的替換

按格式查找

• 如按顏色查找替換：格式按鈕 - 填充 - 選擇顏色

模糊查找

• 認識通配符：* 和 ?都是通配符
• * 指的是任意一個或多個值
• ?（英文的半角問號）指的是一個模糊字
• 注意：像 “張?” 替換成 “經(jīng)理的親戚” ，這樣 “張三” 也會被替換成 “經(jīng)理的親戚三” 。此時需要開啟單元格匹配，張?表示一個模糊字，這樣單元格內(nèi) “張三三” 就不會被替換了。用 ? 時，常常需要勾選單元格匹配，為了限制字符
• 規(guī)避通配符的作用，用波浪線 ~

2.定位工具

通過名稱框定位單元格及區(qū)域位置

• 如在名稱框輸入A900：B1000即可選中該單元格區(qū)域
• 在名稱框輸入100:200即可選中100到200整行

定義名稱

• 為某個單元格區(qū)域自定義名稱，以后可以直接使用這個名稱查找，通過輸入名稱，或運用 “查找和替換” - “轉(zhuǎn)到”
• 選擇某個區(qū)域，然后在名稱框中輸入一個名字，然后回車，這個名字就被保留了下來，下一次就可以比較容易找到

定位條件

• 查找和替換 - 轉(zhuǎn)到 - 定位條件
• 查找和替換 - 定位條件

使用定位條件解決以下問題

• 為有批注的單元格設(shè)置紅色填充色：查找和替換 - 定位條件里選擇批注 - 選中 - 可以自行修改顏色
• 為有公式的單元格設(shè)置紅色填充色：查找和替換 - 定位條件里選擇公式 - 選中 - 可以自行修改顏色
• 填充解除單元格合并后遺留的空白單元格：查找和替換 - 定位條件里面選擇空值 - 選中 - 輸入 - 并按下上方向鍵 - 全部填充快捷鍵（Ctrl + enter）
• 批量刪除圖片：查找和替換 - 定位條件里選擇對象 - 選中 - 按刪除鍵刪除，如果沒有圖片，執(zhí)行上述操作后Excel會警告 “找不到對象”，除此之外，在查找和替換里找到 “選擇對象”，也可以只框選圖片

批注

• 標志：右上角紅色小三角
• 插入批注：選中單元格 - 右鍵 - 插入批注 - 編輯文字
• 顯示/隱藏單個批注：選中單元格 - 右鍵 - 顯示隱藏批注
• 顯示所有批注：審閱選項卡 - 顯示所有批注
• 刪除所有批注：選中區(qū)域，右鍵刪除批注
• 更改批注框形狀：插入 - 形狀 - 選擇目標形狀
• 在批注中插入圖片：設(shè)置批注格式 - 顏色與線條 - 填充 - 顏色 - 填充效果 - 圖片

取消合并單元格后空白內(nèi)容處理

• 選中區(qū)域 - 定位條件：空值 → 在一個空單元格內(nèi)輸入 “=” 
• 若填充與上/下方相同的內(nèi)容，則按下上方向鍵，然后軟回車（Ctrl + enter）
• 若填充其它內(nèi)容，則手動輸入再軟回車
• 選中一個區(qū)域，在一個單元格輸入數(shù)據(jù)，按下軟回車，則該區(qū)域所有單元格都被填上相同的數(shù)據(jù)

第四講：排序與篩選

1.排序

注意：執(zhí)行排序操作的時候避免選中某列，如果以當前選中的區(qū)域排序會導致數(shù)據(jù)錯亂，可以選擇全部數(shù)據(jù)或者這一列中的某個單元格

簡單排序

• 開始 - 排序和篩選 - 升序/降序

多條件排序

• 如將語數(shù)外成績排序，當數(shù)學成績一樣時按照語文成績排，語文成績一樣時按英語成績排：排序和篩選 - 自定義排序 - 添加排序依據(jù)
• 也可以分別進行單列排序：先依次排次要關(guān)鍵字，再排主要關(guān)鍵字（先排英文，再排語文）

按顏色排序

• 排序和篩選 - 自定義排序 - 添加排序依據(jù) - 單元格顏色

自定義排序次序

• 如果要排序的值是文字，默認按照拼音首字母排序
• 如果要改變，那么選擇次序為自定義序列

利用排序插入行制作工資條（格式：表頭 + 個人信息）

• 假設(shè)第一行是工資表表頭，下方11行是員工個人信息
• 再復制10行工資表表頭
• 在第一行表頭旁的空白列輸入0，在員工個人信息輸入1-11
• 其它10行表頭輸入1.5-10.5
• 最后將數(shù)字按照升序排序即可

打印要求每頁都有表頭

• 頁面布局 - 頁面設(shè)置 - 工作表 - 設(shè)置頂端標題行 - 選中第一行 - 打印
• 設(shè)置后，第一行名稱欄會顯示print titles

2.篩選

使用篩選

• 點擊篩選區(qū)域的任意單元格 - 排序和篩選 - 篩選 - 第一行出現(xiàn)下拉箭頭
• 篩選完了，讓原本所有數(shù)據(jù)都出現(xiàn)，在下拉箭頭勾選 “全選”
• 想要把篩選出來的數(shù)據(jù)復制到另一個表中去，卻發(fā)現(xiàn)粘貼了原來的整個表，解決方法：篩選完成后 - 查找和選擇 - 定位條件 - 可見單元格 - 復制

在篩選中使用多個條件

• 數(shù)字篩選：根據(jù)大于/小于/等于等條件來篩選
• 同時篩選多列：分別進行篩選，在上一列的篩選結(jié)果中再篩選
• 文本篩選：輸入開頭/結(jié)尾能匹配的關(guān)鍵詞

高級篩選

• 篩選不重復值：數(shù)據(jù)選項卡 - 高級篩選 - 可選擇在原區(qū)域/其它位置顯示篩選結(jié)果 - 選中列表區(qū)域 - 選中條件區(qū)域（沒有就不動）- 選中復制到哪個單元格 - 勾選不重復的記錄。應(yīng)用場景為找出不重復的數(shù)量
• 使用常量條件區(qū)域：條件區(qū)域指的是在數(shù)據(jù)中找到符合條件的需要另外輸出條件，且（與）的條件寫在同一行，或的條件錯開位置，條件也可以輸入大于小于

第五講：分類匯總與數(shù)據(jù)有效性

1.分類匯總工具

認識分類匯總

• 數(shù)據(jù)選項卡 - 分類匯總
• 按什么分類，匯總什么，怎么匯總

使用分類匯總前先排序

• 選中要分類所屬列的某個單元格 - 點擊【開始 - 排序和篩選】或【數(shù)據(jù)】里的【升序】或【降序】- 完成排序操作
• 點擊【數(shù)據(jù)】里的【分類匯總】，選擇分類字段（即根據(jù)哪一項進行分類），匯總項（即對哪一項進行匯總）匯總方式（可對匯總項進行求和，計數(shù)，求平均值等操作）

分類匯總的嵌套

• 根據(jù)多個字段進行分類匯總，即依次根據(jù)不同的字段進行重復操作
• 選擇主要關(guān)鍵字，次要關(guān)鍵字進行排序
• 依次對主要關(guān)鍵字為分類字段先進行分類匯總，第二次起注意取消 “替換當前分類” 的勾選
• 可以通過點擊工作表左上角的【1,2,3,4】來查看結(jié)果

復制分類匯總的結(jié)果區(qū)域

• 選中結(jié)果區(qū)域 - 選擇【開始 - 查找和選擇 - 定位條件】- 選擇【可見單元格】（可用快捷鍵alt+;）- 復制

使用分類匯總批量合并內(nèi)容相同的單元格

• 將需要批量合并的列先排序
• 數(shù)據(jù) - 分類匯總 - 將該列表頭設(shè)置為【分類字段和匯總項】- 將匯總方式改為計數(shù)（不改也問題不大）- 確定
• 完成后會自動新生成 - 列計數(shù) - 選中新列并選擇【開始 - 查找和選擇 - 定位條件】- 定位到【空值】- 合并單元格
• 數(shù)據(jù) - 分類匯總 - 全部刪除，即可刪除分類匯總的數(shù)據(jù)
• 選中新出現(xiàn)的列 - 開始 - 格式刷 - 選中目標列，即可粘貼格式，合并內(nèi)容相同的單元格（或者先復制再【選擇性粘貼 - 格式】）

2.設(shè)置數(shù)據(jù)有效性

設(shè)置整數(shù)數(shù)據(jù)有效性

• 僅能輸入500~1000之間的整數(shù)
• 選中目標列 - 數(shù)據(jù)選項卡 - 數(shù)據(jù)有效性 - 選擇允許【整數(shù)】，數(shù)據(jù)【介于】，最小值【500】，最大值【1000】- 確定

設(shè)置文本長度數(shù)據(jù)有效性

• 僅能輸入字符長度為8位的產(chǎn)品編碼
• 選中目標列 - 數(shù)據(jù)選項卡 - 數(shù)據(jù)有效性 - 允許【文本長度】，數(shù)據(jù)【等于】，長度【8】- 確定

數(shù)據(jù)有效性的其他設(shè)置

• 輸入法切換：可以選擇只能以中文或英文輸入，功能能否實現(xiàn)和個人電腦輸入法設(shè)置有關(guān)
• 單元格信息：可以將【輸入無效數(shù)據(jù)時現(xiàn)實的出錯警告】改為【警告】并手動輸入警告信息引起別人注意，相當于一種半保護狀態(tài)
• 單元格信息保護：選中目標區(qū)域 - 數(shù)據(jù)選項卡 - 數(shù)據(jù)有效性 - 允許【自定義】- 輸入一個邏輯值為 “false” 的公式（可以直接輸入0），這樣公式無法成立表格也無法被編輯

第六講：認識數(shù)據(jù)透視表

常見數(shù)據(jù)透視表

• 字段名稱：原始表格中列的字段
• 報表篩選：如果把某個字段拖拽到這個里面，它就會變?yōu)楹Y選項，相當于根據(jù)該字段對透視表做篩選
• 列和行：如果想在透視表的行上顯示什么就把對應(yīng)字段拖拽到行上，想在列上顯示什么就把什么拖拽到列標簽
• 值：就是透視表中的數(shù)據(jù)，有求和，計數(shù)，求最大值等多種匯總依據(jù)
• 創(chuàng)建步驟：選中任意一個單元格，點擊【插入 - 數(shù)據(jù)透視表 - 確定】→ 點中生成的數(shù)據(jù)透視表 → 選擇【數(shù)據(jù)透視表選項 - 顯示】→ 勾選【經(jīng)典透視表布局】（可做可不做，看個人習慣）→ 根據(jù)需要將字段拉入數(shù)據(jù)透視表中的行/列/值，即完成一個簡單的數(shù)據(jù)透視表

更改數(shù)據(jù)透視表匯總方式

• 熟知的默認匯總方式是求和，可以雙擊表頭值字段，可以更改匯總方式（如更改為計數(shù)，平均值等）
• 雙擊表中匯總后的數(shù)值可以得到一張新表，能夠查看詳細記錄
• 雙擊表頭第一個單元格，除了可以更改匯總方式，也可以選擇【無】，則去掉分類匯總（不同版本/電腦不一定實現(xiàn)，也可以通過點擊數(shù)據(jù)透視表中的任一單元格，此時表格頂端會出現(xiàn)“設(shè)計”功能選項卡，點擊“設(shè)計”中的子菜單 “分類匯總”，選擇 “不顯示分類匯總”）

數(shù)據(jù)透視表中的組合

• 對日期進行組合：選中日期和其它字段拉入數(shù)據(jù)透視表中的 “行字段”中，注意放的位置，一級字段放前面，如果日期字段是以天為統(tǒng)計維度，可以選中任一日期右擊組合，能夠修改統(tǒng)計維度為月/季度/年
• 對數(shù)據(jù)進行劃分區(qū)間的統(tǒng)計：選中數(shù)據(jù)字段拉入“行字段”中，再從右側(cè)選中數(shù)據(jù)字段拉入 “值字段”，選中 “行字段” 中任意一個數(shù)據(jù) - 右擊 - 組合 - 確定起止和數(shù)值間隔

匯總多列數(shù)據(jù)

• 將姓名字段拉入 “行字段” - 再將工號字段拉入 “行字段”，此時姓名和公號字段呈現(xiàn)上下分布
• 但是針對姓名和工號默認出現(xiàn)了匯總，可通過設(shè)計 - 分類匯總 - 不顯示分類匯總隱藏
• 目的是統(tǒng)計生產(chǎn)數(shù)量的求和項，平均產(chǎn)量，最大產(chǎn)量和最小產(chǎn)量，所以重復4次將生產(chǎn)數(shù)量拉入 “值字段”，此時如果4行生產(chǎn)數(shù)量呈現(xiàn)上下分布，新生成了一行數(shù)據(jù)和匯總，可以把數(shù)據(jù)字段拉到匯總字段上
• 雙擊 “求和項”，更改【值字段匯總方式】也可以單擊右鍵，選擇【值字段設(shè)置】進行更改
• 更改值字段名稱：選中后雙擊 - 在字段名稱內(nèi)更改，也可以在編輯欄中更改，不可與原有字段重復
• 更改表格樣式：點鐘透視表中任意單元格，選擇窗口最上面出現(xiàn)的【數(shù)據(jù)透視表工具 - 設(shè)計】，選擇喜歡的樣式

利用篩選字段自動創(chuàng)建工作表

• 插入數(shù)據(jù)透視表，將某字段拖入“報表篩選字段”中，則可以篩選數(shù)據(jù)
• 但是如果要同時展示多個字段的數(shù)據(jù)透視表，也可以利用篩選字段自動創(chuàng)建
• 準備要新建表格的一列數(shù)據(jù) - 插入數(shù)據(jù)透視表 - 將字段拖入“報表篩選字段”和“值字段”中 - 打開數(shù)據(jù)透視表分析選項卡 - 找到【選項】- 選擇【顯示報表篩選頁】就自動生成了新的工作表
• 但是每一張工作表上都有透視表，需要刪除這些透視表：按住shift鍵選中所有生成的工作表 - 復制空白區(qū)域覆蓋生成的透視表，就去掉了透視表區(qū)域，新的工作表也批量生成了

第七講：認識函數(shù)與公式

1.公式

運算符

• 算術(shù)運算符：用來完成基本的數(shù)學運算
• 比較運算符：結(jié)果一定是邏輯值“TRUE”（運算中當做“1”）或者“FALSE”（運算中當做“0”）

公式中的比較判斷

• 比較運算符的結(jié)果：TRUE，F(xiàn)ALSE
• 公式里的文本要用""引用

運算符的優(yōu)先級

單元格引用

• 相對引用：A1（引用的是相對位置的單元格，公式所在單元格的位置改變，引用也隨之改變）
• 絕對引用：$A$11（引用的是固定單元格，公式所在單元格的位置改變，絕對引用的單元格始終保持不變）
• 混合引用：$A1絕對引用列，A$1絕對引用行（只有行或列被鎖定）（既要橫向又要縱向拖拽，結(jié)合“九九乘法表”理解）

2.函數(shù)

如何使用函數(shù)

• 等號開頭
• 函數(shù)名在中間
• 括號結(jié)尾
• 括號中間寫參數(shù)

學習以下函數(shù)

• sum：求和
• average：求平均
• min：求最小
• max：求最大
• count和counta：計數(shù)
• rank：排名

利用定位工具選擇輸入公式的位置

• 選中目標區(qū)域，點擊【查找和選擇 - 定位條件 - 空值】，再點擊公式選項卡里面自動求和工具，實現(xiàn)跳躍式的求和
• 選中目標區(qū)域，點擊【查找和選擇 - 定位條件 - 空值】，輸入公式函數(shù)，用 “Ctrl + Enter” 進行填充

第八講：IF函數(shù)邏輯判斷

1.使用IF函數(shù)

基本用法

• = if（條件，如果符合則A，如果不符合則B）
• 參數(shù)1：進行一次判斷，是一個邏輯值
• 參數(shù)2：如果邏輯值為TRUE，則在單元格中返回A
• 參數(shù)3：如果邏輯值為FALSE，則返回B

嵌套

• = if（條件1，如果符合則A，if（條件2，如果符合則B，如果不符合則C））
• 參數(shù)1：進行一次判斷，是一個邏輯值
• 參數(shù)2：如果TRUE，則返回A
• 參數(shù)3：如果不為TRUE，需要再根據(jù)剩下的情況進行區(qū)分，則再使用if函數(shù)，以此類推（不要遺漏小括號）

如何盡量回避IF函數(shù)的嵌套

• 當IF函數(shù)嵌套超過了四五層，應(yīng)考慮是不是用錯了函數(shù)或者改用VLOOKUP等其他函數(shù)
• 可以并列使用多個IF函數(shù)，若返回的值為數(shù)值，則可以=IF1(..)+IF2(...)+...(若為FALSE返回0)；若返回的值為文本，則可以=IF1(...)&IF2(...)&（若為FALSE則不返回）

用IF函數(shù)處理運算錯誤

• 判斷公式運行是否出錯
• = IF(ISERROR(A),0,A)：如果運算A出錯，返回0，如果是數(shù)字，返回數(shù)字本身

2.AND函數(shù)與OR函數(shù)

AND函數(shù)：表示 “且” 的關(guān)系

• 表示需要滿足多個條件：參數(shù)必須同時為真，返回的結(jié)果才為真
• AND(條件1，條件2，條件3，...）

OR函數(shù)：表示 “或” 的關(guān)系

• 表示需要滿足至少一個條件：只需滿足其中一個為真，結(jié)果為真
• OR(條件1，條件2，條件3，...)

第九講：COUNTIF函數(shù)

1.使用countif函數(shù)

count函數(shù)

• 用于對給定數(shù)據(jù)集合或單元格區(qū)域中數(shù)據(jù)的個數(shù)進行計數(shù)，只能對數(shù)字數(shù)據(jù)進行統(tǒng)計，對于空單元格，邏輯值或者文本數(shù)據(jù)將被忽略
• 可以利用該函數(shù)來判斷給定的單元格區(qū)域中是否包含空單元格
• = COUNT(value1,value2,...)
• 參數(shù)1：必須，用于計算選中的第一個區(qū)域內(nèi)數(shù)字的個數(shù)
• 參數(shù)2：可選，用于計算選中的第一/三個區(qū)域內(nèi)數(shù)字的個數(shù)，最多可包含255個區(qū)域

countif函數(shù)

• 計算某個單元格區(qū)域內(nèi)，滿足單個指定條件的單元格數(shù)量
• = COUNT(range,criteria)
• 參數(shù)1：數(shù)據(jù)集合，數(shù)值區(qū)域
• 參數(shù)2：數(shù)值，文本或條件等形式定義的條件

2.常見應(yīng)用示例

在數(shù)據(jù)區(qū)域中尋找重復數(shù)據(jù)

在數(shù)據(jù)有效性中使用

3.2007以上版本中的countifs函數(shù)

表示對滿足多個條件的單元格計數(shù)

= COUTNIFS(range1,criteria1,range2,criteria2,...)

只有一個條件則和COUNTIF函數(shù)一樣

第十講：SUMIF函數(shù)

語法

• 根據(jù)指定條件對若干單元格，區(qū)域或引用求和
• = SUMIF(range，criteria，sum_range)
• 參數(shù)1：條件區(qū)域，用于條件判斷的單元格區(qū)域
• 參數(shù)2：求和條件，由數(shù)字，邏輯表達式等組成的判定條件
• 參數(shù)3：實際求和區(qū)域，需要求和的單元格，區(qū)域或引用

超過15位字符時的錯誤

• 查找數(shù)據(jù)時默認最多只能計算數(shù)據(jù)的前15位，若需計算超過15位，應(yīng)該在后面加上&“*”（和countif一樣）

關(guān)于第三參數(shù)簡寫時的注意事項

• 參數(shù)內(nèi)range與sum_range的范圍必須一樣大
• sumif有很大的自由度和容錯率，當輸入的sum_range范圍較小時，會默認補充至range一樣大，因此可以簡寫第三參數(shù)
• 簡寫第三參數(shù)應(yīng)保證第三參數(shù)的第一行要與range的第一行相對應(yīng)

在多列中使用sumif函數(shù)

• 選中整個表作為參數(shù)中的range，僅選擇一個或幾個數(shù)值作為sum_range

使用輔助列處理多條件的sumif

• 當需要滿足兩個或以上條件時，可新增一列作為輔助列，輸入公式 “= 條件1 & 條件2 & ...”，向下填充，以此列作為range條件區(qū)域
• 以“條件1所在的單元格 & 條件2所在的單元格&...”作為criteria求和條件

sumifs函數(shù)

• 快速對多條件單元格求和
• = SUMIFS(sum_range,criteria_range1,criteria1,【,criteria_range2,criteria2...】)
• 參數(shù)1：要求和的單元格區(qū)域
• 參數(shù)2：條件區(qū)域1
• 參數(shù)3：條件1
• 【】為附加的區(qū)域及其關(guān)聯(lián)條件，最多可以輸入127個區(qū)域/條件

第十一講：Vlookup函數(shù)

語法

• = VLOOKUP(lookup_value,table_array,col_index_num,【range_lookup】）
• 參數(shù)1：要查找（匹配）的值
• 參數(shù)2：要查找的區(qū)域
• 參數(shù)3：返回的數(shù)據(jù)在查找區(qū)域的第幾列
• 參數(shù)4：精確匹配/近似匹配，F(xiàn)ALSE(0,空格或不填（但是要有 ',' 占位))，TRUE(1或者不填(無逗號占位))，盡量都使用精確匹配

跨表引用

• 在需要接收返回值的單元格內(nèi)輸入公式，當需要跨表引用時，直接點擊切換到工作簿內(nèi)的其他工作表進行選擇查找的區(qū)域，在輸入完逗號后可切換回來，也可以直接繼續(xù)輸入完公式更方便

使用通配符

• 要查找的數(shù)據(jù)是列表區(qū)域中的部分關(guān)鍵字（參數(shù)1與參數(shù)2中的值不能完全匹配）可以使用通配符 “*”（代表任意數(shù)量的任意字符）

模糊查找

• 找小于等于自己的最大值，適用于找數(shù)值區(qū)間的劃分
• 模糊匹配用的是 “二分法”，在使用時需要查找區(qū)域的值從小到大排序

使用isna函數(shù)處理數(shù)字格式引起的錯誤

• vlookup函數(shù)不能匹配儲存格式不一樣的數(shù)值，比如一個是文本數(shù)值，一個是數(shù)字數(shù)值無法匹配

Hlookup函數(shù)

• 用法與vlookup函數(shù)基本一樣，vlookup函數(shù)適用于以一行為一條記錄的表格，而hlookup用于以一列為一條記錄的表格，注意絕對引用

第十二講：Match + Index

語法

①MATCH函數(shù)

• 返回查找值在查找區(qū)域中的位置
• = MATCH(lookup_value,lookup_array,【match_type】)
• 參數(shù)1：需要查找的值
• 參數(shù)2：在哪列或哪行查找（可以不是一整列或一整行）
• 參數(shù)3：精確查找/模糊查找

②INDEX函數(shù)

• 返回表或區(qū)域中的值或值的引用
• = INDEX(array,row_num,【column_num】）函數(shù)返回的是對應(yīng)單元格的值
• 參數(shù)1：引用哪一區(qū)域
• 參數(shù)2：引用第幾行的值
• 參數(shù)3：引用第幾列的值

Match + Index 與 Vlookup函數(shù)比較

• vlookup函數(shù)只會查找選中區(qū)域的最左列，而且引用列在查找列的右邊，不能做從右向左的查找引用。match和index函數(shù)的查找和引用是分開進行的，不存在列序的矛盾
• vlookup只能查詢返回一個值，不能引用照片，index可以

使用Match與Vlookup函數(shù)嵌套返回多列結(jié)果

• = VLOOKUP(lookup_value,table_array,COLUMN(),0)
• = VLOOKUP(lookup_value,table_array,MATCH(),0)

column函數(shù)

• = COLUMN()
• 查詢單元格在第幾列，若無參數(shù)則返回當前單元格在第幾列

index函數(shù)引用圖片

• 在工作表中添加圖片
• 點擊【公式 - 定義名稱】如定義名稱為 “圖片”，在【引用位置】中輸入引用圖片的函數(shù)
• 在【文件 - 選項 - 自定義功能區(qū)】中添加【照相機】功能到【新建選項卡】選中用于接收圖片的單元格，點擊【新建選項卡 - 照相機】畫一個框，在編輯欄中輸入 “=圖片”，回車
• 復制圖片，粘貼到用于接收圖片的單元格，點中圖片，在編輯欄中輸入 “=圖片”，回車

第十三講：郵件合并

1.簡單的郵件合并

先建立兩個文檔：一個word包括所有文件共有內(nèi)容的主文檔（比如未填寫的信封等）和一個包括變化信息的數(shù)據(jù)源Excel（填寫的收件人，發(fā)件人，郵編等）然后使用郵件合并功能在主文檔中插入變化的信息，合成后的文件用戶可以保存為word文檔，可以打印出來，也可以以郵件形式發(fā)出去

可以應(yīng)用在批量打印工資條，批量打印個人簡歷，批量打印請柬，批量打印準考生等

批量生成多個文檔，利用word發(fā)送郵件（不同版本操作大同小異）

• 在【郵件】選項卡選擇【開始郵件合并】- 【電子郵件】
• 在excel中創(chuàng)建數(shù)據(jù)源（準備數(shù)據(jù)源）
• 在【郵件】選項卡選擇【選擇收件人】選擇【使用現(xiàn)有列表】，打開數(shù)據(jù)源表格，根據(jù)提示點擊確定
• 在【郵件】選項卡選擇【插入合并域】，然后選擇要添加的域，輸入郵件內(nèi)容設(shè)置格式
• 在【郵件】選項卡上，選擇【預覽結(jié)果】查看電子郵件外觀
• 如果對郵件外觀已經(jīng)滿意，請在【郵件】選項卡上選擇【完成并合并】-【合并到電子郵件】，完成郵件合并并發(fā)送
• 如果電子郵件外觀已經(jīng)滿意，請在【郵件】選項卡上選擇上【完成并合并】-【合并到電子郵件】完成郵件合并并發(fā)送

每頁顯示多條記錄

• 在【郵件】選項卡選擇【開始郵件合并】-【目錄】-選擇【選擇收件人】選擇【使用現(xiàn)有列表】，打開數(shù)據(jù)源表格，根據(jù)提示點擊確定
• 選擇【插入合并域】，然后選擇要添加的域，輸入郵件內(nèi)容設(shè)置格式，選擇【完成并合并】-【編輯單個文檔】

郵件合并后的數(shù)字格式處理

• 數(shù)字格式\#"#,##0.00（切換為千分位并保留兩位小數(shù)顯示）
• 日期格式\@"M/d/yyy"（代表月份的M一定要大寫）
• 使用“alt + f9組合鍵”（或者【選中數(shù)字合并域 - 單擊右鍵 - 切換域代碼（編輯域 - 域代碼）】），在字段名稱后輸入代碼，再切換回來，按f9鍵（或右鍵 - 更新域）來更新域代碼
• 每一種顯示格式在excel中有特定對應(yīng)的代碼，可【右鍵單擊excel單元格 - 設(shè)置單元格格式 - 數(shù)字 - 自定義】來查看

2.復雜的郵件合并

利用郵件合并批量生成單個的文檔

為不同的郵件插入不同的附件

（視頻不完整，沒有后續(xù)內(nèi)容）

第十四講：日期函數(shù)

1.認識時間和日期

回顧日期格式

• Excel日期采用 “1900紀年方式”，日期可轉(zhuǎn)換為整數(shù)，即從1900年1月1日開始的第幾天

時間格式

• 時刻可以轉(zhuǎn)換為小數(shù)，即到了該時刻，該天已經(jīng)過去了多少

基本的時間與日期運算

• 時間運算：注意單位換算，天/小時/分鐘/秒鐘 = 1/24/60/60
• 日期運算：日期可與整數(shù)相加減，可更改單元格數(shù)字格式得到日期或數(shù)值

2.日期函數(shù)

Year，Month，Day函數(shù)

• 分別求參數(shù)（一個日期）的年，月，日

Date函數(shù)

• = DATE(YEAR(),(MONTH(),(DAY())
• 將三個參數(shù)相組合，生成一個日期，三個參數(shù)依次是生成日期的年，月，日
• 若代表月，日的數(shù)字過大，會向前進位；若小于1，則會退位

Dateif函數(shù)

• = DATEIF(start_date,end_date,unit)，用于比較兩個日期的不同，參數(shù)3決定返回值是年，月還是日
• 參數(shù)1：表示給定期間的第一個或開始日期的日期
• 參數(shù)2：表示時間段的最后一個（即結(jié)束）日期的日期
• 參數(shù)3：表示要返回的信息類型

Weeknum

• 返回參數(shù)1（日期）是該年中的第幾周，參數(shù)2可以設(shè)置以星期幾為一周的開始

Weekday

• 返回參數(shù)1（日期）是該周的第幾天（1-7，也可以選擇返回0-6），參數(shù)2可以設(shè)置以周幾為一周的開始

第十五講：條件格式與公式

1.使用簡單的條件格式

位置：開始選項卡 - 條件格式

為特定范圍的數(shù)值標記特殊顏色

• 在數(shù)據(jù)內(nèi)標記大于1500000的值，選中數(shù)據(jù)范圍 - 開始 - 條件格式 - 突出顯示單元格規(guī)則 - 大于，在格式規(guī)則內(nèi)輸入條件和設(shè)置格式

• 在數(shù)據(jù)內(nèi)將車間標記為紅色背景，選中數(shù)據(jù)范圍 - 開始 - 條件格式 - 突出顯示單元格規(guī)則 - 文本包含，在格式規(guī)則內(nèi)輸入條件和設(shè)置格式

清除規(guī)則

• 開始 - 條件格式 - 清除規(guī)則

查找重復值

• 找到憑證號的重復值，選中C列 - 開始 - 條件格式 - 突出顯示單元格規(guī)則 - 重復值

為數(shù)據(jù)透視表中的數(shù)據(jù)制作數(shù)據(jù)條

• 新建數(shù)據(jù)透視表 - 選中數(shù)據(jù)區(qū)域 - 開始 - 數(shù)據(jù)格式 - 數(shù)據(jù)條，選擇想要的填充方案

在已設(shè)置條件格式的數(shù)據(jù)透視表中設(shè)置“切片器”

• 點擊數(shù)據(jù)透視表任意單元格 - 數(shù)據(jù)透視表分析 - 插入切片器，根據(jù)想要的字段進行選擇，即可完成【點擊字段進行篩選】如果不要“切片器”直接選中刪除

2.定義多重條件的條件格式

如何管理條件

• 設(shè)置多重條件不會覆蓋替換前一個條件格式。但是后一個條件包含前一個條件就會覆蓋前一個條件
• 想要兼容的做法是先設(shè)置大的范圍標記，再設(shè)置小的

條件格式 - 新建規(guī)則

• 可通過新建規(guī)則為【錯誤值，空值，無空值】等設(shè)置格式
• 新版本也可以直接通過突出顯示單元格規(guī)則 - 文本包含，選擇具體的值進行設(shè)置

3.使用公式定義條件格式

條件格式中公式的書寫規(guī)則

• 將數(shù)量大于100的項目日期標記為紅色背景

使用weekday函數(shù)標記周末

• 將日期為周六日設(shè)置紅色背景，說明在【使用數(shù)字1（星期一）到7（星期日）表示的一周中的第幾天】該類型下，只要返回的結(jié)果為6或7就可以標紅，在條件格式規(guī)則內(nèi)輸入公式 = weekday(A2,2)=6，再進行1次條件設(shè)置即可
• 如果會使用or函數(shù)，可直接輸入 = or(weekday(A2,2)=6,weekday(A2,2)=7)即可
• 如果要將周末整行設(shè)置為紅色背景，選擇整個數(shù)據(jù)區(qū)域，輸入公式 = or(weekday($A2,2)=6，weekday($A2,2)=7)，將A列絕對引用可以避免公式在B列的時候變成B2

標記未來15天的日期

• 將未來15天過生日的員工姓名設(shè)置為紅色背景，說明在忽略年份的情況下只要員工生日距離今天開始的未來15天相差小于等于15就可以設(shè)置為紅色背景，在條件格式規(guī)則內(nèi)輸入公式 = datedif(C2,TODAY()+15,"yd")<=15
• today函數(shù)的語法：該函數(shù)沒有參數(shù)，只用一對括號即可，=today()

第十六講：簡單文本函數(shù)

1.使用文本截取字符串

left函數(shù)

• 從一個文本字符串左邊的第一個字符開始返回指定個數(shù)的字符
• = LEFT(text,【num_chars】)
• 參數(shù)1：一個文本（要取數(shù)的單元格）
• 參數(shù)2：指定返回字符的個數(shù)（若無規(guī)定，默認返回1個）
• 若參數(shù)2大于文本長度，則返回整個文本

right函數(shù)

• 從一個文本字符串的右邊（即最后一個字符）開始返回指定個數(shù)的字符，與LEFT函數(shù)用法完全相同（返回的字符依然是從左到右截?。?/li>
• = RIGHT(text,【num_chars】
• 參數(shù)1：一個文本（要取數(shù)的單元格）
• 參數(shù)2：指定返回字符的個數(shù)（若無規(guī)定，默認返回1個）

mid函數(shù)

• 返回文本字符串中從指定位置開始的指定數(shù)目的字符
• = MID(text,start_num,num_chars)
• 參數(shù)1：一個文本（要取數(shù)的單元格）
• 參數(shù)2：截取字符的開始位置，若小于1，則返回錯誤；若大于文本字符個數(shù)，則返回空
• 參數(shù)3：截取字符的長度，若大于文本字符個數(shù)，則返回從參數(shù)2位置開始到文本結(jié)尾的所有字符
• left函數(shù)和right函數(shù)嵌套使用可以替代mid函數(shù)

2.獲取文本中的信息

find函數(shù)

• 查找指定字符在另一個字符中的位置
• 除此之外，search是另一個常用的查找函數(shù)，區(qū)別在于FIND函數(shù)能區(qū)分大小寫字母，SEARCH函數(shù)可以使用通配符，F(xiàn)IND不可以
• = FIND(find_text,within_text【start_num】)
• 參數(shù)1：要查找的文本（可以是單個字符，數(shù)字）若是字符，要用""引用；若為空，則返回參數(shù)3的值
• 參數(shù)2：在此文本內(nèi)查找；若不是參數(shù)1的子串，則返回錯誤
• 參數(shù)3：規(guī)定開始查找的位置，若無，則默認為1，即從文本第一個字符開始查找；若大于文本長度或小于1，則返回錯誤

len函數(shù)

• 返回文本字符數(shù)的長度
• = LEN(text)，某單元格有多少個字符，空格也會作為字符計數(shù)

lenb()函數(shù)

• 返回文本字節(jié)數(shù)的長度
• = LENB(text)，某單元格有多少個字節(jié)，空格也會作為字符計數(shù)

上述LEFT，RIGHT，MID，LEN，F(xiàn)IND有與其對應(yīng)的-B函數(shù)，前者返回的是字符個數(shù)，后者返回的是字節(jié)個數(shù)

3.關(guān)于身份證

通過身份證前六位判斷地區(qū)

• 首先可以通過LEFT()函數(shù)截取前6位地區(qū)編碼，接著使用VLOOKUP()函數(shù)匹配地區(qū)碼，即可得出身份證對應(yīng)的地區(qū)
• 需要注意，身份證是文本格式，對文本處理得到的6位地區(qū)編碼數(shù)字還是文本，但是匹配表內(nèi)的地區(qū)碼是數(shù)字格式，所以要將地區(qū)編碼*1變成數(shù)字格式，否則無法查找

通過身份證計算出生年月日

第十七講：數(shù)學函數(shù)

1.認識函數(shù)

round函數(shù)

• ROUND(number,num_digits)，按指定的位數(shù)對數(shù)值進行四舍五入
• 參數(shù)1：要進行四舍五入的數(shù)字
• 參數(shù)2：要進行四舍五入的位數(shù)
• 若參數(shù)2 > 0，則四舍五入到指定的小數(shù)位數(shù)；若 = 0，則四舍五入到最接近的整數(shù)；若 < 0，則四舍五入到小數(shù)點左邊的相應(yīng)位數(shù)

roundup函數(shù)

• 按指定的位數(shù)對數(shù)值進行向上舍入（往上無條件進位，如果是負數(shù)是往大的值進位），公式參數(shù)同上

rounddown函數(shù)

• 按指定的位數(shù)，對數(shù)值進行向下舍入（無條件舍去），公式參數(shù)同上

int函數(shù)

• 取整，將數(shù)值向下取整為最接近的整數(shù)，只有一個參數(shù)，不能規(guī)定位數(shù)（不是四舍五入，而是舍尾）
• 處理正值，跟ROUNDDOWN類似，處理負值，跟ROUNDUP相似

mod函數(shù)

• 求余數(shù)
• 返回兩數(shù)相除后的余數(shù)，余數(shù)可以包含小數(shù)部分
• = MOD(number,divisor)，參數(shù)1為除數(shù)，參數(shù)2為被除數(shù)

row函數(shù)和column函數(shù)

• ROW(【reference】)：求單元格的行號
• COLUMN(【reference】)：求單元格的列號
• 若不帶參數(shù)，則判定當前單元格的位置

2.函數(shù)應(yīng)用實例

通過身份證號碼判斷性別

• 取身份證的性別判斷位，用MOD函數(shù)來判斷其奇偶性，再用IF函數(shù)來判斷男女
• = IF(MOD(RIGHT(LEST(B2,I7),1),2)=1,"男","女")
• 遇到多個函數(shù)嵌套，可以先從最里層函數(shù)梳理

特殊的舍入方式：員工假期計算

• = IF(MOD(C2,1)>=0.5,INT(C2)+0.5,INT(C2))
• = INT(A2*2)/2

第十八講：Vlookup與數(shù)組

1.統(tǒng)計函數(shù)

SUMIF函數(shù) = (條件區(qū)域，條件單元格，求和區(qū)域)

SUMIFS函數(shù) = (求和區(qū)域，條件區(qū)域1，條件單元格1，條件區(qū)域1，條件單元格1)

2.認識數(shù)組

數(shù)組生成原理

• 用SUM函數(shù)替代SUMIF/SUMIFS
• =SUM(條件判斷1 * 條件判斷2 * ... *條件判斷n * 求和項)

sumproduct函數(shù)

• 作用和SUM相同，但不用三鍵組合使用，直接回車即可

3.lookup函數(shù)基本應(yīng)用

認識lookup函數(shù)

• 從單行或單列或從數(shù)組中進行查找并返回一個值
• = LOOKUP(lookup_value,lookup_vector[,result_vector])
• 第一參數(shù)：需要查找的值
• 第二參數(shù)：查找的區(qū)域，只包含一行或者一列，數(shù)值必須按升序排序
• 第三參數(shù)：返回的值所在的區(qū)域，只包含一行或一列，必須和第二參數(shù)區(qū)域大小一致

lookup函數(shù)模糊匹配

• lookup函數(shù)第二參數(shù)只有一列，而vlookup函數(shù)必須包含查詢列和返回值所在的列。相較之下，lookup函數(shù)更加靈活
• lookup函數(shù)只有3個參數(shù)，不能設(shè)置精確匹配
• 使用lookup函數(shù)時，若查找列按升序排列，運算結(jié)果與vlookup函數(shù)精確查找一致

lookup函數(shù)多條件精確匹配

• lookup函數(shù)在查找時雖然不能設(shè)置精確匹配，但是會自動回避錯誤值，故可將所有不匹配的值轉(zhuǎn)化為錯誤來進行精確匹配
• = LOOKUP(1,0/(條件區(qū)域1=條件1)，結(jié)果區(qū)域)
• = LOOKUP(1,0/((條件區(qū)域1=條件1)*(條件區(qū)域2=條件2)),結(jié)果區(qū)域)

第十九講：indirect函數(shù)

1.認識indirect單元格引用

了解indirect函數(shù)的基本運用

• 如在A1單元格輸入小佩，在當前單元格輸入=A1可返回小佩，這種屬于直接引用
• 如繼續(xù)在C10單元格輸入A1，在當前單元格輸入=INDIRECT(C10)也可返回小佩，因為C10單元格的內(nèi)容是A1，表示單元格地址，所以會返回A1單元格的內(nèi)容，這種屬于間接引用
• 但是如果在當前單元格輸入=INDIRECT("C10")，加上雙引號后C10就是一個文本，不會返回A1單元格的內(nèi)容；同理在當前單元格輸入=INDIRECT("A10")，也是返回小佩

indirect函數(shù)的意義及語法

• = indirect(ref_text,[a1])，即=indirect(引用的單元格，引用方式)
• 參數(shù)1：需要引用的單元格，如果不是合法的單元格的引用，函數(shù)indirect返回錯誤值
• 參數(shù)2：引用的單元格類型，引用方式為A1或R1C1兩種方法，在A1樣式中，列和行將分別按字母和數(shù)字順序添加標簽；在R1C1引用樣式中，列和行均按數(shù)字順序添加標簽
• 平時使用的工作表行都使用阿拉伯數(shù)字表示，列都是用英文大寫字母表示，默認A1-樣式，所以這里的第二個參數(shù)往往都會省略，僅需考慮第一個參數(shù)即可

indirect函數(shù)與index函數(shù)引用方式對比

• 通過index函數(shù)直接引用：取每周第二個數(shù)值也就是數(shù)據(jù)內(nèi)第5,10,15,20,25列，所以通過index函數(shù)返回E列第5行數(shù)據(jù)，輸入公式 = index(E:E,ROW()*5-25)
• 通過indirect函數(shù)間接引用：取每周第二個數(shù)值也就是數(shù)據(jù)內(nèi)E5,E10,E15,E20,E25單元格，如果直接輸入單元格公式無法自動拖拽，所以通過輸入 = "e"&row()*5-25得到單元格，再加上indirect函數(shù)引用單元格即可，=indirect("e"&row()*5-25)

處理跨表

• 在12張表內(nèi)統(tǒng)計了銷售人員的業(yè)績，每張表排版一致，匯總張三每月的銷售業(yè)績：在每張表格內(nèi)張三的單元格位置不變，只有月份變化，可以直接通過公式實現(xiàn) = indirect((A4&"!G2"))
• 在12張表內(nèi)統(tǒng)計了銷售人員的業(yè)績，每張表排版不一致，匯總張三每個月的銷售業(yè)績：在每張表格內(nèi)張三的單元格位置不固定，可以與vlookup結(jié)合找到張三的銷售業(yè)績，輸入公式 = vlookup("張三"，indirect(A4&"!A:G"),7,false)

跨表引用時的單引號問題

• 如果有時候使用indirect函數(shù)錯誤，注意表格命名問題，可以加上單引號規(guī)避

2.indirect名稱引用

為區(qū)域定義名稱

• 選擇單元格區(qū)域 - 公式選項卡 - 名稱管理器 - 對選定區(qū)域定義名稱

制作二級下拉列表

• 新建名稱：選中單元格區(qū)域 - 公式選項卡 - 名稱管理器 - 新建名稱
• 在實操中，如果要新建3個名稱，就需要操作3次，為了省略重復步驟，也可以選中單元格區(qū)域 - 根據(jù)所選內(nèi)容創(chuàng)建 - 創(chuàng)建名稱的位置根據(jù)需要選中首行 - 名稱管理器里面就批量新建好了3個名稱
• 域 - 根據(jù)所選內(nèi)容創(chuàng)建 - 創(chuàng)建名稱的位置根據(jù)需要選中首行 - 名稱管理器里面就批量新建好了3個名稱
• 一級下拉框：選中制作下拉框的列 - 數(shù)據(jù)選項卡 - 數(shù)據(jù)驗證 - 驗證條件選中序列 - 來源選中所需單元格的范圍 - 生成一級下拉框
• 二級下拉框：選中制作下拉框的列 - 數(shù)據(jù)選項卡 - 數(shù)據(jù)驗證 - 驗證條件選中序列 - 來源輸入 =indirect(定義名稱指定單元格) - 生成二級下拉框

第二十講：圖表基礎(chǔ)

1.認識圖表中的元素

在組合內(nèi)隱藏詳細信息時如何將圖片一起隱藏：默認設(shè)置下隱藏行/列之后，圖片不會隨之隱藏，而是移到下一行/列了。選擇圖片 - 右擊設(shè)置圖片格式 - 屬性選中隨單元格改變位置和大小，此時隱藏組合（設(shè)置：數(shù)據(jù) - 組合）時圖片也會一起隱藏

插入圖表

• 選中需要制表的所有數(shù)據(jù) - 插入選項卡 - 找到想要的圖表款式
• 它包含兩個分類，一個是推薦的圖表，另一個則是所有圖表

布局

• 圖表標題：跟文章標題一樣，用一句簡明扼要的歸納出圖表內(nèi)容；在圖表設(shè)計 - 添加圖表元素 - 圖表標題，可以設(shè)置圖表標題位置；在圖表內(nèi)右擊圖表標題，可以設(shè)置圖表標題格式；選中圖表標題文本框，在編輯欄輸入=B1，標題會自動隨B1切換
• 坐標軸標題：對坐標軸的命名；在圖表設(shè)計 - 添加圖表元素 - 坐標軸標題，可以設(shè)置坐標軸標題
• 圖例：跟地圖上的圖例一樣，方便查看數(shù)據(jù)系列反映的是哪些內(nèi)容
• 數(shù)據(jù)標簽：數(shù)據(jù)系列的數(shù)據(jù)化顯示，可以通過設(shè)置數(shù)據(jù)標簽格式來調(diào)整顯示內(nèi)容/顯示位置
• 模擬運算表：原始制表表格
• 坐標軸：一般是主要橫坐標軸（分類軸）和主要縱坐標軸（數(shù)據(jù)軸）一些復雜的圖表可能還有兩個次坐標軸；在圖表元素 - 坐標軸或者圖表內(nèi)右擊坐標軸，可以設(shè)置坐標軸格式：坐標軸選項，刻度線，標簽，數(shù)值等；選中坐標軸文字 - 設(shè)置坐標軸格式 - 選項 - 標簽，可以選擇周旁，高低等；縱坐標軸是數(shù)值的話，可以設(shè)置比例尺大小，即邊界的最大值最小值，間隔單位值；設(shè)置橫坐標重排順序，縱坐標移到右側(cè)（鏡像）：坐標軸選項 - 逆序類別
• 網(wǎng)格線：網(wǎng)格線垂直各水平網(wǎng)格線，便于使用圖表的人進行比較
• 如果隱藏了某個元素，可以在格式選項卡里找到

2.創(chuàng)建并美化柱形圖

了解主次坐標軸的概念

• 當涉及不成比例的兩組數(shù)據(jù)時，需要用主，次坐標軸區(qū)分

制作折線與柱形復合圖表

• 各個地區(qū)有營業(yè)額，指標完成率，這兩個數(shù)據(jù)比例不匹配，插入柱形圖表后，指標完成率無法直觀顯示
• 點擊指標完成率的柱形圖（或格式選項卡 - 左上角下拉框選擇指標完成率 - 設(shè)置所選內(nèi)容格式）選擇系列繪制在次坐標軸上
• 再點擊指標完成率的柱形圖，右鍵更改系列圖表類型 - 折線圖，在折線上右鍵設(shè)置數(shù)據(jù)系列格式 - 標記 - 勾選自動，也可內(nèi)置選擇大小或形狀
• 此時圖表基礎(chǔ)已經(jīng)完成，但是要把柱形圖和折線圖分開。選中主要縱坐標軸 - 右擊選擇設(shè)置坐標軸格式 - 在坐標軸選項內(nèi)將邊界最大值設(shè)為110，最小值設(shè)為70，單位設(shè)置為10，在標簽內(nèi)將標簽位置改為無；選擇次要坐標中 - 右擊選擇設(shè)置坐標軸格式 - 將邊界最大值設(shè)為1，最小值設(shè)置為0.6，單位設(shè)置為0.1，在標簽內(nèi)將標簽位置改為無
• 選中網(wǎng)格線 - 設(shè)置網(wǎng)格線顏色和類型；移動圖例位置；選中整個圖表可直接修改字體；修改圖表標題名稱；分別點擊柱狀圖/折線圖 - 設(shè)置數(shù)據(jù)系列格式 - 線條顏色，標記填充色，標記邊框顏色設(shè)置合適的顏色
• 分別點擊柱形圖和折線圖 - 右擊添加數(shù)據(jù)標簽 - 在設(shè)置數(shù)據(jù)標簽格式根據(jù)需要更改字體顏色，大小，圖標就按照需求完成了

制作計劃與實際對比圖

• 各個銷售人員有計劃完成銷售額，實際完成銷售額，插入柱形圖表 - 點擊計劃完成的柱形圖 - 選擇系列繪制在次坐標軸上
• 將主要坐標軸和次要坐標軸的邊界設(shè)置為一致，最小值為0，最大值為900萬，單位是100萬
• 點擊計劃完成柱形圖 - 將格式修改為無填充 - 紅色邊框，調(diào)整間隙寬度為160；點擊實際完成柱形圖 - 將格式修改為綠色填充，調(diào)整間隙寬度為160
• 選中主要坐標軸 - 設(shè)置坐標軸格式內(nèi)的數(shù)字類別為自定義 - 類型為【#，“百萬”，0】選中次要坐標軸，將標簽位置改成無；選擇網(wǎng)格線，將線條設(shè)置成無線條；選擇圖例 - 刪除；修改圖表標題名稱，修改字體和大小，圖標就按照需求完成了

制作雙向柱形圖

• 設(shè)置主次坐標軸，選中整個數(shù)據(jù)區(qū)域 - 插入選項卡 - 建議的圖表 - 簇狀條形圖；點擊出口條形圖 - 設(shè)置數(shù)據(jù)系列格式 - 次坐標
• 目的是兩個條形從中間分別向左右延伸，也就是說主坐標軸正中間的值應(yīng)該是0，坐標軸的數(shù)值分別從中間的0向兩邊延伸，所以點擊橫向坐標軸 - 點擊選擇設(shè)置坐標軸格式 - 在坐標軸選項里邊界最小值為-1，最大值為1，單位為0.25，勾選逆序刻度值讓兩個條形方向相反，在數(shù)字里面選擇數(shù)字類別為自定義 - 類型為【0%；0%】這樣就不會存在負數(shù)
• 調(diào)整下次坐標軸邊界和單位大小，保持跟主坐標軸一致，可以忽略負數(shù)，在標簽內(nèi)將標簽位置改成無，此時得到旋風圖的基本形態(tài)
• 把縱坐標軸標簽移出圖外，點擊縱坐標軸右鍵 - 設(shè)置坐標軸格式 - 標簽內(nèi)將標簽位置改成低；點中網(wǎng)格線，在填充內(nèi)將線條改成無線條（或者按delete鍵刪除）
• 美化條形圖，分別點中紅色條形和藍色條形 - 右鍵 - 設(shè)置數(shù)據(jù)系列格式 - 間隙寬度為100%，填充顏色改成適合的顏色，陰影預設(shè)內(nèi)調(diào)整適當?shù)年幱澳Ｊ?/li>
• 在條形圖上加相應(yīng)數(shù)值，分別點中兩側(cè)條形圖 - 點擊添加數(shù)據(jù)標簽，點擊數(shù)據(jù)標簽設(shè)置標簽位置為居中，字體為白色；修改圖表標題；移動圖例位置
• 圖片背景：選中圖片 - 格式 - 設(shè)置對象格式 - 藝術(shù)效果 - 虛化 - 再選中圖片 - 復制 - 選中圖表區(qū)（表外框內(nèi)的空白部分）- 右鍵 - 設(shè)置圖表區(qū)格式 - 填充：圖片或紋理填充 - 插入圖片來自：剪貼板 - 選中圖表數(shù)據(jù)區(qū)域 - 右鍵 - 設(shè)置繪圖區(qū)格式 - 無填充

3.圖表通用設(shè)置

利用復制粘貼更改數(shù)據(jù)系列顯示樣式

• 將柱狀圖改成三角形狀圖：插入 - 生成1個三角形 - 復制 - 選中柱狀圖 - 粘貼
• 將柱狀圖改成心形圖：插入 - 生成1個心形 - 復制 - 選中柱狀圖 -粘貼后出現(xiàn)變形的心形 - 點擊心形右鍵設(shè)置數(shù)據(jù)系列格式 - 填充點擊層疊完成

巧用圖表模板

• 選中圖表 - 右擊 - 保存為模板
• 選中數(shù)據(jù) - 插入 - 圖表模板
• 特別注意：數(shù)據(jù)表要和原圖標的格式一致，否則無法應(yīng)用圖表模板

第二十一講：動態(tài)圖表

1.動態(tài)圖表實現(xiàn)原理

理解圖表中的數(shù)據(jù)系列

• 選擇訂購日期，彩盒，寵物用品3列數(shù)據(jù)插入一個折線圖，在圖表上右擊選擇數(shù)據(jù)則可以看到圖表工具自動分的圖例項
• 也可以直接制作該類圖表：點擊空單元格 - 插入 - 折線圖 - 得出空的圖表 - 右鍵圖表 - 選擇數(shù)據(jù) - 圖例項（系列）- 添加 - 系列名稱：彩盒；系列值：選擇B2:B13（不能選兩列）同里添加寵物用品；水平（分類）軸標簽 - 選擇A2:A13

手工修改系列中的數(shù)值與坐標軸數(shù)據(jù)

• 建立空白圖表，右鍵，選擇數(shù)據(jù)，可以手工增刪改表中的數(shù)值與坐標軸數(shù)據(jù)

小試牛刀：利用IF創(chuàng)建簡單的動態(tài)圖表

• 復選框：將數(shù)據(jù)復制到新表格中，添加開發(fā)工具選項卡 → 文件 - 選項 - 自定義功能區(qū) - 右側(cè)勾選開發(fā)工具 - 開發(fā)工具 - 插入 - 復選框；右鍵復選框1 - 編輯文字：彩盒（寵物用品同理）右鍵 - 拖拉外表狂可移動位置（移到H2,H4）
• 控件：右鍵彩盒/寵物用品復選框 - 設(shè)置格式控件 - 控制 - 單元格鏈接：選中G2（此時勾選復選框的話G2會變?yōu)門RUE，不勾選為FALSE）
• 公式：隨便找個單元格G8，輸入 = IF($G$2,$B$2:$B$13,$F$2:$F$13)，表示如果G2為true，則返回B2-13數(shù)據(jù)列，否則返回F2-13空白數(shù)據(jù)列，注意絕對引用
• 定義公式：復制公式，ESC退出，選擇公式 - 定義名稱，名稱輸入彩盒，引用位置粘貼公式 = IF($G$2,$B$2:$B$13,$F$2:$F$13)，寵物用品同理，此時G8單元格無用，可以刪掉
• 制作圖表：在任意空白單元格插入折線圖，右鍵圖表 - 選擇數(shù)據(jù) - 圖例項：添加 - 定義名稱為彩盒，Y值輸入 = sheet1！彩盒（英文模式下的?。┐_定出現(xiàn)相應(yīng)折線圖，點擊空白單元格，寵物用品同理
• 同一縱坐標：同時勾選彩盒和寵物用品，右鍵縱坐標 - 設(shè)置坐標軸格式 - 最小值0，最大值1400000，如果要去掉小數(shù)點，在設(shè)置坐標軸格式 - 類別為數(shù)字，小數(shù)位數(shù)為0
• 移動控件：將彩盒和寵物用品復選框文字刪除 - 移動框框到相應(yīng)圖例前方，如果看不見將圖表置于底層，將圖表和控件組合，方便一起移動

2.利用offset函數(shù)與控件創(chuàng)建動態(tài)圖表

offset函數(shù)概述

• = OFFSET(reference,rows,cols,[height],[width])
• 以某個單元格為基準，下移n行，右移n列，取n行n列

offset函數(shù)的動態(tài)引用示例

• counta($A:$A)計算A列有多少非空單元格取表格中所有的數(shù)據(jù)區(qū)域 = OFFSET($A$1,0,0,COUNTA($A:$A),11)
• 公式 - 定義名稱 - 名稱內(nèi)輸入數(shù)據(jù)區(qū)域；引用位置輸入 = offset($A$1,0,0,COUNTA($A:$A),11)
• 選中任意有數(shù)據(jù)的單元格 - 插入 - 數(shù)據(jù)透視表 - 表/區(qū)域：數(shù)據(jù)區(qū)域
• 做一個數(shù)據(jù)透視表選擇：行標簽：產(chǎn)品類別；數(shù)值：金額
• 此時，在原表格下邊增加行，數(shù)據(jù)透視表刷新后，新增金額也隨之更新

動態(tài)圖表1：永遠返回最后10行數(shù)據(jù)

• 將圖表2中的數(shù)據(jù)復制到新的Excel表中，取最后10行數(shù)據(jù)的話，以B1單元格為基準，一共17行的話下移7行右移0列，就可以取最后10行，規(guī)律就是總行數(shù) - 10就是下移的行數(shù)，輸入公式 = OFFSET($B$1,COUNTA($B:$B)-10,0,10,1)
• 公式 - 定義名稱 - 名稱：成交量；引用位置 = OFFSET($B$1,COUNTA($B:$B)-10,0,10,1)
• 插入 - 折線圖 - 右鍵空圖表 - 選擇數(shù)據(jù) - 圖例項：添加 - 定義名稱為成交，Y值輸入 = sheet2！成交量
• 日期也對應(yīng)引用，= OFFSET($A$1,COUNTA($A:$A)-10,0,10,1)；公式 - 定義名稱 - 名稱：日期；引用位置：=OFFSET($A$1,COUNTA($A:$A)-10,0,10,1)；右鍵折線 - 選擇數(shù)據(jù) - 水平分類軸標簽輸入 = sheet2！日期

動態(tài)圖表2：通過控件控制圖表數(shù)據(jù)

• 每點一次第一個滾動條，取的行數(shù)就往下走一行，第一個滾動條空值OFFSET往下走的位置，第二個滾動條控制圖展現(xiàn)多少數(shù)據(jù)
• 將數(shù)據(jù)復制到新的Excel表格中
• 開發(fā)工具 - 插入 - 滾動條 - 拉出合適的長度（插入兩個滾動條）在第一個滾動條上右鍵 - 設(shè)計控件格式 - 最小值：1；單元格鏈接：$D$2（直接點）第二個滾動條同樣操作，鏈到$D$4
• 實現(xiàn)成交量動態(tài)區(qū)域 = OFFSET($B$1,$D$2,$D$4,1)，公式 - 定義名稱 - 名稱：成交量；引用位置：=OFFSET($B$1,$D$2,0,$D$4,1)
• 插入 - 柱形圖 - 右鍵圖表 - 選擇數(shù)據(jù) - 添加圖例項 - 系列名稱：成交量；系列值：=sheet3！成交量；在第一個滾動條上右鍵 - 設(shè)計控件格式 - 最小值：1；單元格鏈接：￥D$2（直接點）第二個滾動條同樣操作，鏈到$D$4
• 實現(xiàn)成交量動態(tài)區(qū)域=OFFSET($B$1,$D$2,0,$D$4,1)，公式 - 定義名稱 - 名稱：成交量；引用位置：=OFFSET($B$1,$D$2,0,$D$4,1)
• 插入 - 柱形圖 - 右鍵圖表 - 選擇數(shù)據(jù) - 添加圖例項 - 系列名稱：成交量；系列值：=sheet3！成交量
• 實現(xiàn)日期動態(tài)區(qū)域 =OFFSET($A$1,$D$2,0,$D$4,1)，公式 - 定義名稱 - 名稱：日期；引用：=OFFSET($A$1,$D$2,0,$D$4,1)
• 點擊表格柱形 - 右鍵 - 選擇數(shù)據(jù) - 水平（分類）軸標簽 - 編輯 - 軸標簽區(qū)域：=sheet3！日期

第二十二講：甘特圖與動態(tài)甘特圖

1.甘特圖

• 選中數(shù)據(jù)A1:C9 - 插入選項卡 - 插入堆積條形圖

• 點中生成的圖表區(qū)域 - 圖表設(shè)計，把圖表修改成想要的顏色

• 右擊條形框 - 設(shè)置數(shù)據(jù)系列格式 - 填充：無填充；邊框顏色：無線條；陰影：無陰影

• 右鍵橫坐標 - 設(shè)置坐標軸格式 - 坐標軸選項里邊界最小值：41760；最大值：41790；數(shù)字選項里類別選擇日期，類型選擇只有月和日

• 美化表格：右鍵條形 - 設(shè)置數(shù)據(jù)系列格式 - 間隙寬度：10%；右鍵縱坐標 - 設(shè)置坐標軸格式 - 勾選逆序類別；右鍵網(wǎng)格線 - 設(shè)置網(wǎng)格線格式 - 線型 - 短劃線類型：虛線。即完成甘特圖，也可根據(jù)實際需求繼續(xù)美化

2.動態(tài)甘特圖

• 選中數(shù)據(jù)A1:C9，將數(shù)據(jù)復制到新表中；在E1單元格新建【已完成】輔助列；在F1單元格新建【未完成】輔助列；在B11單元格任意輸入一個項目日期，假設(shè)今天是【2014/5/11】
• 在E2單元格輸入公式：=IF($B$11<B2,0,IF($B$11>B2+C2,C2,$B$11-B2))（如果今日日期小于計劃開始日，說明已完成0天；如果今日日期大于計劃開始日，說明已完成0天；如果今日日期大于計劃開始日 + 計劃持續(xù)天數(shù)，說明已完成天數(shù)為計劃持續(xù)天數(shù)；如果以上兩種都不是，說明已完成天數(shù)為今日日期 - 計劃開始日）
• 在F2單元格輸入公式：=C2 - E2；選中A1 - B9，按住Ctrl，選中E1-E9，插入堆積條形圖，隱藏藍顏色條形，設(shè)置縱坐標軸格式，設(shè)置分類間距，逆序列，網(wǎng)格線
• 開發(fā)工具選項卡插入【滾動條】- 右鍵滾動條 - 設(shè)置控件格式 - 控制：最小值：0；最大值：30；單元格鏈接：點擊D11（空白單元格），B11單元格改為=41760+D11
• 插入選項卡 - 文本框：橫排文本框 - 選中文本框 - 在編輯欄輸入 = ￥B$11

第二十三講：Excel圖表與PPT

1.雙坐標柱形圖的補充知識：主次坐標設(shè)置以及避讓

根據(jù)A1:C6的數(shù)據(jù)生成柱形圖，由于營業(yè)額和指標完成度數(shù)字相差太多，所以柱形的比例也不對，因此應(yīng)該給指標完成度增加一個次坐標軸

首先可以點擊【指標完成度】柱形，右鍵設(shè)置數(shù)據(jù)系列格式，將它設(shè)置在次坐標軸上，此時兩個顏色的柱形會重疊顯示

如果不想讓主次坐標軸上的數(shù)據(jù)重疊，可以用兩個空的柱形將兩個有顏色的柱形分別往左往右擠開

在圖標區(qū)域右擊 - 選擇數(shù)據(jù)，點擊添加，系列名稱自由命名，數(shù)據(jù)選擇一組空數(shù)據(jù)即可，但是要使數(shù)據(jù)和原某一系列數(shù)據(jù)一樣大小，創(chuàng)建兩個空白系列，確定

現(xiàn)在我們看到兩組數(shù)據(jù)已經(jīng)被擠開了，我們可以將我們設(shè)置的兩組空白數(shù)據(jù)分別放置在主次坐標軸，選中圖表區(qū)域，點擊格式選項卡 - 將系列3放到主坐標

圖表區(qū)域右擊 - 選擇數(shù)據(jù)，調(diào)整4個圖例項的位置，確保4個柱形圖不會重疊展示即可，最后再對表格進行美化

2.餅圖美化

餅圖美化常見設(shè)置

• 選擇A1:B6數(shù)據(jù)，插入一個三維餅圖
• 右鍵圖表區(qū)，選擇三維旋轉(zhuǎn)，將高度設(shè)置為原圖表的30%，餅圖會變薄

• 再選擇餅圖，右擊設(shè)置數(shù)據(jù)系列格式，對餅圖進行三維設(shè)置

• 選中餅圖，右擊選擇添加數(shù)據(jù)標簽，點擊圖標設(shè)計選項卡 - 添加圖表元素 - 數(shù)據(jù)標簽居中。再點擊餅圖上的數(shù)字，點擊開始選項卡，修改字體，顏色和大小，即完成餅圖美化

制作雙層餅圖

• 根據(jù)下面效果圖顯示有兩層餅圖，先做最上層的餅圖

• 選中B2:C10銷售區(qū)域匯總的兩列數(shù)據(jù)，插入二維餅圖

• 右鍵餅圖 - 選擇數(shù)據(jù)，添加圖例項 - 在Y值內(nèi)選中匯總下的三個數(shù)字 - 確定

• 此時餅圖沒有任何變化，拉開上面餅圖下面也沒有顯示，原因是兩層餅圖在同一個坐標軸，所以此時需要將上面的餅圖調(diào)整到次坐標軸上，點擊上層餅圖右擊設(shè)置數(shù)據(jù)系列格式，將系列繪制在次坐標軸

• 此時，再拉上層餅圖則會全部縮小，再單個選中上層縮小的餅圖往中間聚攏

• 選中上層餅圖，右擊添加數(shù)據(jù)標簽，再右擊選擇設(shè)置數(shù)據(jù)標簽格式，并調(diào)整標簽選項勾選類別名稱，百分比，將標簽位置設(shè)為標簽內(nèi)。在大餅圖下并調(diào)整標簽選項勾選類別名稱，百分比，將標簽位置設(shè)為最佳匹配，同時右鍵圖表區(qū)，選擇數(shù)據(jù)，在圖例項選擇系列2，降水平軸標簽選擇一科到三科，確定

• 再對餅圖進行美化，調(diào)整文字字體，顏色，大小即可

3.PPT中的圖表

圖表粘貼

• 如果選擇一張圖表在PPT中粘貼，直接粘貼會變?yōu)镻PT本身設(shè)置的顏色，因為默認使用了PPT的配色方案，如果要修改PPT的配色方案：選擇設(shè)計選項卡，選中自己希望的顏色即可
• 在粘貼Excel中的圖表時，在Excel中復制出來，在空白PPT模板中右鍵點擊，可選擇粘貼選項
• 使用目標主題，此時顏色會選用PPT中本來設(shè)置好的顏色
• 保留原格式，此時顏色會保持為原來Excel中的配色
• 同時選中使用目標主題和鏈接到Excel數(shù)據(jù)，此時顏色會選用PPT中本來設(shè)置好的顏色，并且數(shù)據(jù)會隨著Excel中數(shù)據(jù)的變動而更新，可在圖表工具中的設(shè)計中點擊刷新數(shù)據(jù)
• 保留原格式，此時顏色會保持為原來Excel中的配色
• 同時選中使用目標主題和鏈接到Excel數(shù)據(jù)，此時顏色會選用PPT中本來設(shè)置好的顏色，并且數(shù)據(jù)會隨著Excel中數(shù)據(jù)的變動而更新，可在圖表工具中的設(shè)計中點擊刷新數(shù)據(jù)
• 同時選中保留原格式和鏈接到Excel數(shù)據(jù)，此時顏色會保持為原來Excel中的配色，并且數(shù)據(jù)會隨著Excel中的數(shù)據(jù)的變動而更新，可在設(shè)計中點擊刷新數(shù)據(jù)

圖表動畫

• 將Excel中的圖表作為圖表粘貼在PPT中，選擇動畫選項卡，選擇進入效果擦除即可生成一個簡單的進入動畫
• 點擊動畫選項卡下的動畫窗格，點擊已經(jīng)設(shè)置的動畫效果，底部會出現(xiàn)點擊效果選項，選擇圖表動畫，在組合圖表中按需選擇即可

第二十四講：宏表函數(shù)

1.利用宏表函數(shù)獲取信息

get.cell函數(shù)

• GET.CELL(type_num,reference)，其作用是返回引用單元格的信息
• type_num指明單元格中信息的類型，用數(shù)字表示，范圍為1-66，具體數(shù)字代表的類型可見[get.cell]表
• reference為引用的單元格或區(qū)域范圍
• 用法：函數(shù)不能直接在單元格內(nèi)寫，必須先定義名稱（公式 → 定義名稱 → 引用位置）

get.workbook函數(shù)

• GET.WORKBOOK(type_num,name_text)，其作用是用來提取工作簿的信息數(shù)據(jù)
• type_num：必需，指明要得到的工作簿信息類型的數(shù)字代碼
• name_text：可選，指明打開的工作簿的名字，如果省略，則默認為當前活動的工作簿
• 用法：前提是定義名稱（如“工作表名”）即引用位置=get.workbook()

HYPERLINK函數(shù)

• 如果想做成超鏈接，點擊表名即可直達表格內(nèi)的單元格，需要用到HYPERLINK函數(shù)
• HYPERLINK(link_location,friendly_name)，用于創(chuàng)建一個快捷方式（跳轉(zhuǎn)），是excel超級鏈接的函數(shù)實現(xiàn)方法
• link_location：為要打開的文件名稱或完整路徑?？梢允潜镜赜脖P，UNC路徑或URL路徑
• friendly_name：單元格中顯示的跳轉(zhuǎn)文本或數(shù)字值，如果省略，第一個參數(shù)顯示為跳轉(zhuǎn)文本

2.宏表函數(shù)常見應(yīng)用

EVALUATE函數(shù)

• EVALUTE(formula_text)，將參數(shù)字符串表達式進行運算求出結(jié)果
• 注：宏函數(shù)并不能單獨在單元格中運行，在名稱定義中運行

SUBSTITUTE函數(shù)

• SUBSTITUTE(text,old_text,new_text,[instance_num])，替換文本內(nèi)容
• text：不省略參數(shù)，為需要替換其中字符的文本，或?qū)形谋镜膯卧竦囊?/li>
• old_text：不省略參數(shù)，為需要替換的舊文本
• new_text：不省略參數(shù)，但有默認值空，為換成的新文本內(nèi)容
• instance_num：替換第幾個