Ecel實(shí)戰(zhàn)培訓(xùn)_表格類模板_表格模板_實(shí)用文檔.ppt

Excel實(shí)戰(zhàn)技巧培訓(xùn) Jim Zhang 目錄 2 前言 操作與技巧 原來可以如此簡(jiǎn)單 數(shù)據(jù)透視表 百變星君 函數(shù)與公式 Excel的精髓之處 1 2 4 3 00 01 02 03 04 05 06 07 圖表 專業(yè)的我也可以做到 5 6 總結(jié)與作業(yè) 前言 Excel有些什么 3 前言 煩惱 困惑與疑問 4 前言 成為 高手 的捷徑 5 心態(tài) 方法 堅(jiān)持 No 1積極的心態(tài) 興趣是最好的老師 壓力是前進(jìn)的動(dòng)力 No 2正確的方法循序漸進(jìn)善用資源 學(xué)以致用閱讀與實(shí)踐的結(jié)合 No 3持之以恒的努力 每天進(jìn)步一點(diǎn)點(diǎn) 前言 關(guān)于本次培訓(xùn) 6 前言 起步之前讓我們回顧幾個(gè)場(chǎng)景 7 目錄 8 前言 操作與技巧 原來可以如此簡(jiǎn)單 數(shù)據(jù)透視表 百變星君 函數(shù)與公式 Excel的精髓之處 1 2 4 3 00 01 02 03 04 05 06 07 圖表 秀的是專業(yè) 拼的是創(chuàng)意 5 6 總結(jié)與作業(yè) 操作與技巧基礎(chǔ) 排序 9 排序的基本操作 案例1 如何按漢字筆畫排序 案例2 如何用排序制作工資條 案例3 如何按行排序 案例4 如何對(duì)合并單元格相鄰的數(shù)據(jù)區(qū)域排序 案例5 如何按單元格顏色排序 2007以上版本 操作與技巧基礎(chǔ) 篩選 自動(dòng)篩選 10 篩選的基本操作 案例1 如何對(duì)雙行標(biāo)題列表進(jìn)行篩選 案例2 篩選中數(shù)據(jù)區(qū)域的注意事項(xiàng) 案例3 如何進(jìn)行反向選擇 案例4 如何不增加輔助列對(duì)日期按星期篩選 操作與技巧基礎(chǔ) 查找與替換 11 查找與替換基本操作 案例1 如何清除或修改特定格式的單元格內(nèi)容 案例2 如何查找批注中的內(nèi)容 案例3 如何在整個(gè)工作簿查找 案例4 如何刪除工作表中的通配符和特定字符 操作與技巧基礎(chǔ) 定位 12 定位的概念和基本操作 案例1 如何披量清除工作表上的各種對(duì)象 案例2 如何運(yùn)用定位行列差異單元格 案例3 如何利用定位將數(shù)據(jù)填充完整 測(cè)試題1 操作與技巧基礎(chǔ) 選擇性粘貼 13 選擇性粘貼的基本操作和意義 案例1 如何將公式轉(zhuǎn)為數(shù)值 案例2 如何直接對(duì)某個(gè)區(qū)域進(jìn)行加減乘除運(yùn)算 案例3 如何只復(fù)制單元格中的有效性設(shè)置 案例4 關(guān)于轉(zhuǎn)置功能的運(yùn)用 案例5 關(guān)于跳過空單元格 操作與技巧基礎(chǔ) 效率加倍的快捷鍵 14 何為有用的快捷鍵 需要自己去摸索總結(jié) 本次培訓(xùn)僅僅列舉了一小部分供大家參考 令效率加倍的快捷鍵舉例 目錄 15 前言 操作與技巧 原來可以如此簡(jiǎn)單 數(shù)據(jù)透視表 百變星君 函數(shù)與公式 Excel的精髓之處 1 2 4 3 00 01 02 03 04 05 06 07 圖表 秀的是專業(yè) 拼的是創(chuàng)意 5 6 總結(jié)與作業(yè) 操作與技巧進(jìn)階 單元格格式 16 單元格格式概述 Ctrl 1 案例1 來瀏覽一些 千奇百怪 的自定義格式 案例2 如何對(duì)手機(jī)號(hào)碼進(jìn)行分段顯示 案例3 如何對(duì)不同范圍的數(shù)值使用不同格式 案例4 如何將自定義格式的顯示轉(zhuǎn)換成真實(shí)數(shù)據(jù) 操作與技巧進(jìn)階 工作表 工作簿的保護(hù) 17 保護(hù)工作表 保護(hù)工作簿 案例1 保護(hù)工作表基礎(chǔ)操作 案例2 如何限定用戶只能選擇可編輯的區(qū)域 案例3 如何在已保護(hù)的工作表中運(yùn)用篩選 操作與技巧進(jìn)階 數(shù)據(jù)有效性 18 數(shù)據(jù)有效性的概述 案例1 如何利用數(shù)據(jù)有效性控制單元格的輸入 案例2 如何限制重復(fù)值的錄入 案例3 用有效性制作二級(jí)下拉菜單 案例4 利用有效性圈釋不符合要求的數(shù)據(jù) 操作與技巧進(jìn)階 條件格式 19 條件格式概述 案例1 利用條件格式標(biāo)注達(dá)成率 案例2 用條件格式判斷列是否有重復(fù) 案例3 用條件格式自動(dòng)實(shí)現(xiàn)間隔底紋 案例4 用條件格式自動(dòng)為小計(jì)總計(jì)設(shè)定格式 簡(jiǎn)單介紹2010的條件格式 迷你圖 操作與技巧進(jìn)階 名稱的運(yùn)用 20 名稱是什么東東 操作與技巧進(jìn)階 名稱的運(yùn)用 21 為什么要使用名稱 操作與技巧進(jìn)階 名稱的運(yùn)用 22 關(guān)于名稱的約定 操作與技巧進(jìn)階 名稱的運(yùn)用 23 定義名稱的三種方法 方法1 插入名稱 案例2 使用名稱框 案例3 根據(jù)所選內(nèi)容創(chuàng)建 批量 操作與技巧進(jìn)階 名稱的運(yùn)用 24 關(guān)于名稱的案例 案例1 在名稱中使用常量 案例2 在名稱中使用函數(shù) 案例3 動(dòng)態(tài)名稱的運(yùn)用 案例4 如何一次性刪除所有定義的名稱 操作技巧進(jìn)階 綜合案例分析 25 操作與技巧進(jìn)階 高級(jí)篩選 26 高級(jí)篩選及舉例 參考一份網(wǎng)絡(luò)上的教程 請(qǐng)看案例 目錄 27 前言 操作與技巧 原來可以如此簡(jiǎn)單 數(shù)據(jù)透視表 百變星君 函數(shù)與公式 Excel的精髓之處 1 2 4 3 00 01 02 03 04 05 06 07 圖表 秀的是專業(yè) 拼的是創(chuàng)意 5 6 總結(jié)與作業(yè) 函數(shù)與公式 熱身運(yùn)動(dòng)Excel運(yùn)算符 28 讓我們一起來看看Excel的常用運(yùn)算符 函數(shù)與公式 熱身運(yùn)動(dòng)公式 函數(shù) 29 公式 是以等號(hào) 開頭 通過使用運(yùn)算符將數(shù)據(jù) 函數(shù)等元素按一定順序連接在一起 從而實(shí)現(xiàn)對(duì)工作表中的數(shù)據(jù)執(zhí)行計(jì)算的等式 例如C1 A1 A2 A3 A4 B1 B2 B3 B4 函數(shù) 簡(jiǎn)而言之 函數(shù)就是預(yù)先定義好了的公式 比如上例中的公式就可以用函數(shù)C1 SUM A1 B4 代替 函數(shù)不僅可以簡(jiǎn)化公式 而且具有僅用運(yùn)算符連接的一般公式所不能替代的功能 例如查找引用 邏輯判斷等等 函數(shù)與公式 函數(shù)的結(jié)鉤分析 30 函數(shù)與公式 熱身運(yùn)動(dòng)快速選擇函數(shù) 31 快速選擇 輸入函數(shù)的方法 通過 公式庫 功能區(qū)快速選擇輸入 通過開頭字母的提示快速輸入 通過插入函數(shù)輸入 直接輸入 目錄 32 前言 操作與技巧 原來可以如此簡(jiǎn)單 數(shù)據(jù)透視表 百變星君 函數(shù)與公式 Excel的精髓之處 1 2 4 3 00 01 02 03 04 05 06 07 圖表 秀的是專業(yè) 拼的是創(chuàng)意 5 6 總結(jié)與作業(yè) 函數(shù)起步 F1 不離不棄的好兄弟 33 自學(xué)函數(shù)的捷徑全在這里了記住 excel本身就是最好的參考書 F1 F1哪里不會(huì)點(diǎn)哪里 函數(shù)起步 F4 相對(duì) 絕對(duì) 由你說了算 34 何為引用 有何影響 案例1 三種引用的同時(shí)理解 案例2 為什么vlookup會(huì)出現(xiàn)錯(cuò)誤值 案例3 綜合案例 函數(shù)起步 F9 一把 庖丁解牛 的寶刀 35 目錄 36 前言 操作與技巧 原來可以如此簡(jiǎn)單 數(shù)據(jù)透視表 百變星君 函數(shù)與公式 Excel的精髓之處 1 2 4 3 00 01 02 03 04 05 06 07 圖表 秀的是專業(yè) 拼的是創(chuàng)意 5 6 總結(jié)與作業(yè) 函數(shù)如此簡(jiǎn)單 TRUEvsFALSE 37 Excel的邏輯值只有2個(gè) TRUEFALSE TRUEFALSE是函數(shù)的關(guān)鍵 可以這么說 任何函數(shù)都是在解決TRUE還是FALSE的問題 TRUEFALSE與運(yùn)算符進(jìn)行各種計(jì)算時(shí) 請(qǐng)牢記 TRUE 1FALSE 0 請(qǐng)務(wù)必牢記 通過示例文件 我們來詳細(xì)解釋一下TRUE和FALSE 函數(shù)如此簡(jiǎn)單 邏輯判斷是函數(shù)的靈魂 38 串聯(lián)開關(guān)等同于邏輯函數(shù)AND 即多個(gè)條件同時(shí)滿足 比如 TRUE TRUE 并聯(lián)開關(guān)等同于邏輯函數(shù)OR 即只要滿足一個(gè)條件 比如 TRUE FALSE 函數(shù)如此簡(jiǎn)單 邏輯判斷是函數(shù)的靈魂 39 通過案例 輕松掌握邏輯運(yùn)算 案例1 是否能申請(qǐng)經(jīng)濟(jì)適用房 案例2 如何根據(jù)性別和職務(wù)計(jì)算補(bǔ)貼 案例3 多層判斷 徹底理清邏輯關(guān)系 函數(shù)如此簡(jiǎn)單 數(shù)據(jù)匯總統(tǒng)計(jì)類函數(shù) 40 注 數(shù)據(jù)匯總統(tǒng)計(jì)類將全部采用案例形式進(jìn)行講解 匯總求和類函數(shù) 關(guān)鍵詞 sum sumif sumifs subtotal 案例1 匯總多個(gè)表格中相同單元格的數(shù)據(jù) sum 案例2 如何匯總某品名的銷售數(shù)量 sumif 案例3 深入理解sumif函數(shù)的 相對(duì)位置 案例4 如何匯總月薪在2000 4000元的工資總額 sumifs 案例5 如何忽略隱藏的行匯總數(shù)據(jù) subtotal 案例6 如何始終生成連續(xù)的商品序號(hào) subtotal 函數(shù)如此簡(jiǎn)單 數(shù)據(jù)匯總統(tǒng)計(jì)類函數(shù) 41 數(shù)據(jù)計(jì)數(shù)類函數(shù) 關(guān)鍵詞 count countif countifs 案例1 如何統(tǒng)計(jì)成績(jī)表中的及格人數(shù) countif countifs 案例2 如何統(tǒng)計(jì)產(chǎn)品規(guī)格中帶 號(hào)的產(chǎn)品個(gè)數(shù) countif 案例3 如何統(tǒng)計(jì)35歲以上有房有車的人員名單 案例4 如何統(tǒng)計(jì)不重復(fù)的數(shù)據(jù)個(gè)數(shù) 函數(shù)如此簡(jiǎn)單 數(shù)據(jù)匯總統(tǒng)計(jì)類函數(shù) 42 極值均值類函數(shù) 關(guān)鍵詞 min max median large small average 案例1 如何設(shè)置提成獎(jiǎng)金的上下限 max min mdeian 案例2 如何不計(jì)重復(fù)返回一組數(shù)據(jù)中次大 次小值 large small 案例3 如何統(tǒng)計(jì)前十名成績(jī)的平均分 average large 案例4 如何根據(jù)條件計(jì)算平均分 averageif averageifs 函數(shù)如此簡(jiǎn)單 查找引用類函數(shù) 43 注 查找引用類將采用函數(shù)解析 案例的方式 VLOOKUP函數(shù)動(dòng)態(tài)解析 辦公室函數(shù)使用率之最 MATCH函數(shù)動(dòng)態(tài)解析 案例1 根據(jù)不同的商品不同的訂貨量計(jì)算單價(jià)及折扣 案例2 輸入工號(hào)后可自動(dòng)顯示相關(guān)的人事資料 測(cè)試題 案例3 如何處理VLOOKUP函數(shù)找不到對(duì)應(yīng)數(shù)據(jù)產(chǎn)生的錯(cuò)誤 44 函數(shù)如此簡(jiǎn)單 查找引用類函數(shù) INDXE函數(shù)動(dòng)態(tài)解析 案例4 如何從右往左查詢對(duì)應(yīng)的數(shù)據(jù) 測(cè)試題 案例5 如何根據(jù)品牌和規(guī)格雙條件查找單價(jià) OFFSET函數(shù)動(dòng)態(tài)解析 初次接觸比較難理解 案例6 如何保證新增加的銷量自動(dòng)求和 offset counta動(dòng)態(tài)引用 函數(shù)如此簡(jiǎn)單 文本函數(shù) 45 我們一起來學(xué)習(xí)幾個(gè)最常用的文本處理函數(shù) LEFT MID RIGHT 三個(gè)兄弟 本事各不同 FIND 在文本中找文本SUBSTITUTE 替換函數(shù) 案例1 判斷產(chǎn)品缺陷中是否含某個(gè)字符 案例2 不通過分列如何將數(shù)據(jù)分成2列 案例2 如何查找某字符在字符串中第N次出現(xiàn)的位置 SUMPRODUCT 函數(shù)中一顆璀璨的明珠 46 千萬不會(huì)要去迷戀Excel幫助文件中對(duì)此函數(shù)的描述 也許是因?yàn)镾UMPRODUCT函數(shù)能力太過超群 Excel幫助文件都嫉妒了它的才能 SUM PRODUCT SUMPRODUCT 數(shù)據(jù)1 數(shù)據(jù)2 數(shù)據(jù)3 數(shù)據(jù)4 數(shù)據(jù)N SUMPRODUCT 數(shù)據(jù)1 數(shù)據(jù)2 數(shù)據(jù)3 數(shù)據(jù)4 數(shù)據(jù)N 將匯總數(shù)相乘 是這樣嗎 將乘積的結(jié)果匯總 Good SUMPRODUCT 數(shù)據(jù)1 數(shù)據(jù)2 SUMPRODUCT 函數(shù)中一顆璀璨的明珠 47 在了解的基本原理后 我們還是通過案例來講解SUMPRODUCTDE的常規(guī)用法 案例1 起步 讓一般統(tǒng)計(jì)類函數(shù)汗顏 案例2 奔跑 讓多條件統(tǒng)計(jì)類函數(shù)折服 Hi 不小心 我們已經(jīng)打開了統(tǒng)計(jì)類 數(shù)組公式 的潘多拉盒子 函數(shù) 美麗 魅力 綻放 你發(fā)現(xiàn)了嗎 48 SUMPRODUCT 函數(shù)中一顆璀璨的明珠 很遺憾 沒有任何事物是完美的 SUMPRODUCT也有自己的缺憾 1 在用我們今天所講的 SUMPRODUCT 數(shù)據(jù)1 數(shù)據(jù)2 的方式時(shí) 計(jì)算項(xiàng) 求和時(shí)出現(xiàn)文本 錯(cuò)誤值 邏輯值 將會(huì)出錯(cuò) 2 用SUMPRODUCT 計(jì)算很大的表 很復(fù)雜的邏輯時(shí) 運(yùn)行速度會(huì)比較慢 因?yàn)樵摵瘮?shù)內(nèi)部執(zhí)行的是數(shù)組運(yùn)算 會(huì)相對(duì)耗時(shí) 當(dāng)然這個(gè)耗時(shí)只是相對(duì)而言 比如像對(duì)SUMIFS DSUM等等 我和函數(shù)的約定 日積月累 學(xué)以致用 49 函數(shù)是Excel中最核心的部分 是技巧 圖表 透視表 VBA的基本功 同時(shí)函數(shù)本身內(nèi)容也相當(dāng)豐富 值得大家不斷的去學(xué)習(xí) 思考如何讓工作更輕松 同時(shí) 由于函數(shù)內(nèi)容太多 不需要大家把每個(gè)函數(shù)都去掌握由于函數(shù)很有深度 很多函數(shù)都值得反復(fù)的去學(xué)習(xí) 去發(fā)現(xiàn)由于許多函數(shù)在理解上比較困難 不可能短期內(nèi)達(dá)到某個(gè)高度因此 建議大家 日積月累 學(xué)以致用 Lookup查找之王 Text文本之王 目錄 50 前言 操作與技巧 原來可以如此簡(jiǎn)單 數(shù)據(jù)透視表 百變星君 函數(shù)與公式 Excel的精髓之處 1 2 4 3 00 01 02 03 04 05 06 07 圖表 秀的是專業(yè) 拼的是創(chuàng)意 5 6 總結(jié)與作業(yè) 數(shù)據(jù)透視圖 數(shù)據(jù)透視圖 數(shù)據(jù)透視表 數(shù)據(jù)透視表 認(rèn)識(shí)數(shù)據(jù)透視表 51 Excel數(shù)據(jù)清單 數(shù)據(jù)庫 文本文件 其他關(guān)系型數(shù)據(jù)文件 Excel數(shù)據(jù)透視表引擎 數(shù)據(jù)透視表 數(shù)據(jù)透視圖 數(shù)據(jù)透視表 數(shù)據(jù)透視表 了解數(shù)據(jù)透視表的結(jié)構(gòu) 52 數(shù)據(jù)透視表 認(rèn)識(shí)數(shù)據(jù)透視表結(jié)構(gòu) 53 報(bào)表篩選區(qū)域 行區(qū)域 列區(qū)域 數(shù)值區(qū)域 數(shù)據(jù)透視表 創(chuàng)建一個(gè)透視表 54 選擇區(qū)域中任意單元格插入透視表選擇列 行 值 數(shù)據(jù)透視表 無拘無束的數(shù)據(jù)透視表 1 55 篩選字段 列 行字段任意拖拽的無拘無束 字段匯總方式的無拘無束 字段顯示方式的無拘無束 修改數(shù)據(jù)透視表的字段名稱 靈活處理錯(cuò)誤值和空白單元格 數(shù)據(jù)透視表 無拘無束的數(shù)據(jù)透視表 2 56 誰先誰后由決定 展示字段先后順序任意指定 數(shù)據(jù)透視表的排序 數(shù)據(jù)透視表的篩選 數(shù)據(jù)透視表分組 組合方式由我定 更改數(shù)據(jù)源和移動(dòng)數(shù)據(jù)透視表 數(shù)據(jù)透視表 如虎添翼的計(jì)算字段和計(jì)算項(xiàng) 57 首先理解計(jì)算字段 計(jì)算項(xiàng) 案例 用計(jì)算項(xiàng)計(jì)算銷售提成 案例 用計(jì)算項(xiàng)計(jì)算年銷售差異 數(shù)據(jù)透視表 動(dòng)態(tài)創(chuàng)建數(shù)據(jù)透視表 58 方法 定義名稱法創(chuàng)建動(dòng)態(tài)的數(shù)據(jù)透視表 方法 使用表功能創(chuàng)建動(dòng)態(tài)的數(shù)據(jù)透視表 方法 導(dǎo)入外部數(shù)據(jù)創(chuàng)建動(dòng)態(tài)數(shù)據(jù)透視表 數(shù)據(jù)透視表 多重合并 外部引用 透視圖 59 數(shù)據(jù)透視表的內(nèi)容 比我們想象的還要多 今天我們僅僅見識(shí)了他的 冰山一角 目錄 60 前言 操作與技巧 原來可以如此簡(jiǎn)單 數(shù)據(jù)透視表 百變星君 函數(shù)與公式 Excel的精髓之處 1 2 4 3 00 01 02 03 04 05 06 07 圖表 秀的是專業(yè) 拼的是創(chuàng)意 5 6 總結(jié)與作業(yè) 圖表 圖表秀 61 圖表 圖表秀 62 圖表 圖表的制作過程 63 理解數(shù)據(jù)分析數(shù)據(jù)提煉