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

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