EXCEL常用操作及簡(jiǎn)單函數(shù)運(yùn)用講解課件

1、EXCEL常用操作及簡(jiǎn)單函數(shù)運(yùn)用 QQ18332655 樂(lè)樂(lè)狗 2011-6-24修訂第1頁(yè)，共30頁(yè)。特色:1.總結(jié)自身與他人經(jīng)驗(yàn)，可能帶有一定的片面性2.側(cè)重常用功能3.舉例講解4.盡量快捷鍵操作第2頁(yè)，共30頁(yè)。三點(diǎn)建議1.經(jīng)濟(jì)基礎(chǔ)決定上層建筑 熟悉EXCEL的基本功能，才能把功能運(yùn)用自如，才能用好VLOOKUP，數(shù)據(jù)透視及其他 2.不要讓自己的左手廢了 要習(xí)慣用左手操盤(pán),右手抓鼠 左手除了打字，還應(yīng)該經(jīng)常做如下事情： 文檔間切換Alt+Tab (左手無(wú)名指和拇指 ) 顯示桌面 win+D 單元格鎖定F43.學(xué)會(huì)做盲人，不要練就“一指神功” 習(xí)慣用小鍵盤(pán)盲打 食指（147）中指（258）

2、無(wú)名指（.369*）大拇指（0）小手指（Enter+-) 習(xí)慣用“+”，少用“=”，近水樓臺(tái)先得月 第3頁(yè)，共30頁(yè)。目錄1流星花園13篩選求和2有頭有臉 14格子介紹3減壓瘦身15分類(lèi)匯總4好聚好散16選擇粘貼5斷章取義 17數(shù)字掃尾6勞逸結(jié)合 18間隔填充7隨心所選19Ctrl大家族8巧走小道20雜七雜八9用小刷子21常用函數(shù)10顏色管理22輕松換行11下拉菜單 23尋遍天下12規(guī)矩方圓24剝皮抽筋25團(tuán)隊(duì)精神第4頁(yè)，共30頁(yè)。1.流星花園-“F4”：?jiǎn)卧褚媒榻B“流星花園”“F4”“偶像”“固定”1.絕對(duì)引用：在B1中用公式引用A1單元格內(nèi)容，=$A$1（按F4，即鎖定單元格），向右邊

3、或下面，拖動(dòng)此公式，始終不會(huì)改變2.相對(duì)引用：在B1中用公式引用A1單元格內(nèi)容，=A1，向右邊或下面，拖動(dòng)此公式，公式會(huì)隨之變動(dòng)C1=B1，B1=C13.混合引用:在B1中用公式引用A1單元格內(nèi)容，=$A1或=A$1 ，向右邊或下面，拖動(dòng)此公式，公式鎖定的部分不變,未鎖的部分隨之變動(dòng)第5頁(yè)，共30頁(yè)。2.有頭有臉頭表頭字段，一般是第一行看起來(lái)有頭操作：視圖-窗口凍結(jié)拆分窗格 快捷鍵ALT+W+F+F凍行下一行，凍列右一列，行列右下格例如：活動(dòng)單元格在A2,凍結(jié)第一行 活動(dòng)單元格在B1,凍結(jié)第一列 活動(dòng)單元格在B2,凍結(jié)第一行第一列 打印出來(lái)也有頭操作：頁(yè)面布局-頁(yè)面設(shè)置-工作表-打印標(biāo)題打印預(yù)

4、覽快捷鍵 CTRL+F2第6頁(yè)，共30頁(yè)。3.減壓瘦身 ERP或系統(tǒng)導(dǎo)出文件，往往帶格式，導(dǎo)致文件大，打開(kāi)慢，處理慢,嚴(yán)重影響工作效率操作：1）全選-開(kāi)始-編輯-清除格式（這點(diǎn)非常有用，請(qǐng)牢記） 2）office-選項(xiàng)-高級(jí)-常規(guī)-清除”啟動(dòng)時(shí)打開(kāi)此目錄中的所有文件”第7頁(yè)，共30頁(yè)。4.好聚好散好聚合并例如：合并品號(hào)品名規(guī)格 =A1&B1&C1 =“01”&B1&C1注意:文本必須加“”，或用CONCATENATE好散分列 操作：數(shù)據(jù)-數(shù)據(jù)工具-分列 可以按長(zhǎng)度分,也可以按特殊符號(hào)分 同時(shí)又可以使文本數(shù)字 數(shù)值化例如對(duì)包裝公式長(zhǎng)寬高 50*45*60的分列第8頁(yè)，共30頁(yè)。5.斷章取義 1.

5、left 左取單元格字符串 =left(單元格,要取的個(gè)數(shù))2.right 右取單元格字符串 =right(單元格,要取的個(gè)數(shù))3.mid 中間取單元格字符串 =mid(單元格,開(kāi)始取的位置,要取的個(gè)數(shù))4.len 計(jì)算單元格字符串長(zhǎng)度(一個(gè)中文字算一位) =len(單元格)5.lenb 計(jì)算單元格字符串長(zhǎng)度(一個(gè)中文字算兩位,即英文狀態(tài)) =lenb(單元格)例如不帶顏色碼的品號(hào)第9頁(yè)，共30頁(yè)。6.勞逸結(jié)合你是否每天對(duì)著枯燥的EXCEL表，悶悶不樂(lè)呢？那就放個(gè)帥哥，美女進(jìn)來(lái)吧！操作：頁(yè)面布局-背景, 選擇需要插入的圖片后確定 局部背景:選中不要背景的單元格，填充成其他顏色第10頁(yè)，共30頁(yè)

6、。7. 隨心所選1.上下左右選區(qū)域Ctrl+shift+方向鍵2.Ctrl+shift+7或Ctrl+HOME到左上單元格，Ctrl+shift+1或Ctrl+END到右下單元格3.Ctrl+shift+3/Ctrl+Pageup右切換工作表， Ctrl+shift+9/Ctrl+Pagedown左切換工作表4.Ctrl+shift+2當(dāng)前列最低行單元格， Ctrl+shift+8當(dāng)前列第一行單元格5. Ctrl+”*” Ctrl+Shift+空格鍵 Ctrl+A 一次：數(shù)據(jù)區(qū)域，兩次：全部6. Ctrl+shift+HOME選中左上區(qū)域 Ctrl+shift+END 選中右下區(qū)域把我學(xué)會(huì)就行

7、了哦第11頁(yè)，共30頁(yè)。8.巧走小道當(dāng)你在工作薄中有太多工作表，而找不到你想要的那張而苦惱嗎？那就學(xué)習(xí)走小道吧！操作：工作表左下角，右鍵點(diǎn)擊左右箭頭處第12頁(yè)，共30頁(yè)。9.用小刷子單次刷：?jiǎn)螕簟案袷剿ⅰ卑粹o時(shí)，可以將選定單元格的格式應(yīng)用到其余的單元格中，但每一次只能應(yīng)用一次“格式刷” 多次刷：需要重復(fù)使用，只要雙擊“格式刷”按鈕，然后單擊要改變其格式的多個(gè)單元格。完成操作后，按Esc鍵或再次單擊“格式刷”按鈕，就可以關(guān)閉“格式刷”。 第13頁(yè)，共30頁(yè)。10. 顏色管理讓不同類(lèi)型數(shù)據(jù)用不同顏色顯示 執(zhí)行“格式條件格式”命令，打開(kāi)“條件格式”對(duì)話框。單擊第二個(gè)方框右側(cè)的下拉按鈕，選中“大于或等

8、于”選項(xiàng)，在后面的方框中輸入數(shù)值“x”。單擊“格式”按鈕，打開(kāi)“單元格格式”對(duì)話框，將“字體”的“顏色”設(shè)置為“紅色” 。例如對(duì)毛利率低于10%的產(chǎn)品，用紅色警示條件格式公式-西洋棋盤(pán)：=MOD(ROW(A1),2)=MOD(COLUMN(A1),2)注意：公式字母必須大寫(xiě)第14頁(yè)，共30頁(yè)。11.下拉菜單名字或產(chǎn)品名稱等字段輸入到表格中，為了保持名稱的一致性，利用“數(shù)據(jù)有效性”功能建了一個(gè)分類(lèi)下拉列表填充項(xiàng)。 操作：數(shù)據(jù)-有效性-設(shè)置-序列-數(shù)據(jù)來(lái)源 選中單元格區(qū)域或輸入數(shù)據(jù),用“,”隔開(kāi)第15頁(yè)，共30頁(yè)。12.規(guī)矩方圓以規(guī)矩，成方圓自定義序列并排序 操作：OFF-EXCEL選項(xiàng) 常用-自

9、定義序列 選中單元格區(qū)域或輸入數(shù)據(jù),用“,”隔開(kāi) 數(shù)據(jù)-排序-次序（自定義序列）例如：規(guī)定人員順序序列填充：左鍵單格拖拉/雙格拖拉/格式標(biāo)簽 右鍵拖拉，日期填充可選年，月，日 當(dāng)然也可以用開(kāi)始-編輯-填充按鈕 可刪除的自動(dòng)序列號(hào)：ROW()-ROW($A$1)第16頁(yè)，共30頁(yè)。13.篩選求和操作：數(shù)據(jù)-篩選-自動(dòng)篩選 高級(jí)篩選，不太使用(不作介紹)篩選求和：Alt+“=”，注意，一定要“=“ 出現(xiàn)函數(shù) (2003版可以,2007版不可以)例如選擇性求和=SUBTOTAL(9,A2:A50), 第一個(gè)參數(shù)的改變，匯總的方式也不同 2007版本如何粘貼（或刪除）篩選結(jié)果 1.選中篩選后的數(shù)據(jù)區(qū)域

10、，按“Alt+；”也可以F5或Ctrl+G定位“可見(jiàn)單元格” 或點(diǎn)“數(shù)據(jù)-排序和篩選-重新應(yīng)用” 2.選擇性粘貼到新的區(qū)域第17頁(yè)，共30頁(yè)。14.格子介紹（CTRL+1）數(shù)字：各種數(shù)字顯示格式，不改變?cè)袛?shù)據(jù)以萬(wàn)顯示“0！.0，”對(duì)齊：學(xué)會(huì)用跨列居中字體：學(xué)會(huì)上下標(biāo)邊框：填充：保護(hù):鎖定公式單元格，防止更改,跟保護(hù)工作表一起使用第18頁(yè)，共30頁(yè)。15.分類(lèi)匯總適用情況：快速查看分類(lèi)匯總數(shù)據(jù)操作: 數(shù)據(jù)-分類(lèi)匯總（注意先排序） 分類(lèi)字段：按哪個(gè)字段匯總 匯總方式：求和，計(jì)數(shù) 選定匯總項(xiàng):哪些數(shù)據(jù)需要匯總 每組數(shù)據(jù)分頁(yè):按分類(lèi)自動(dòng)分頁(yè) 第19頁(yè)，共30頁(yè)。16.選擇粘貼1.去公式:復(fù)制單元格,

11、右鍵選擇性粘貼,選“值”2.加減乘除:在空白單元格中輸入另一(組)數(shù)字(加減數(shù),倍數(shù),除數(shù)), 復(fù)制該單元格或區(qū)域, 再選中被加減,被乘除的單元格或區(qū)域, 右鍵選擇性粘貼,選擇“值”“加減乘除確定3.轉(zhuǎn)置:行數(shù)據(jù)轉(zhuǎn)換成列數(shù)據(jù)復(fù)制數(shù)據(jù)區(qū)域,右鍵選擇性粘貼,選擇”轉(zhuǎn)置” 4.文本變值，加零或減零，或除1或乘1第20頁(yè)，共30頁(yè)。17.數(shù)字掃尾round(四舍五入） roundup（入）（舉例按日期取季度）rounddown（舍）trunc （直截）floor（按一定基數(shù)四舍）ceiling（按一定基數(shù)五入）Fixed四舍五入后轉(zhuǎn)為文本第21頁(yè)，共30頁(yè)。18.間隔填充1.插入間隔行，方法一：插入兩

12、列輔助列，分別在第一行和第二行輸入數(shù)字1，選中包含1的四個(gè)單元格，雙擊往下或向下拖來(lái)，選中此兩列，定位-常量，右鍵插入行方法二：增加輔助列，對(duì)數(shù)據(jù)行加序號(hào)1-2-3空白行1.5-2.5-3.5,然后排序. 2.隔間填充上下一單元格或左右單元格的內(nèi)容 （1）選中區(qū)域，定位到空白單元格 （2）=上一下單元格或左右單元格 （3）ctrl+enter批量填充 第22頁(yè)，共30頁(yè)。19.CTRL大家族批量填寫(xiě)Ctrl+Enter,單個(gè)工作表復(fù)制可按Ctrl托拖動(dòng)工作表打印預(yù)覽Ctrl+F2輸入當(dāng)天日期 Ctrl+“ ；”時(shí)間Ctrl+Shift +“ ；” 復(fù)制上一單元格Ctrl+“ ” 復(fù)制前任意連續(xù)

13、文本單元格Alt+“”（文本）復(fù)制Ctrl+C 剪切Ctrl+X 粘貼Ctrl+V 查找Ctrl+F 定位Ctrl+G 替換Ctrl+H打印Ctrl+F 打印預(yù)覽Ctrl+F2后退Ctrl+Z第23頁(yè)，共30頁(yè)。20.雜七雜八數(shù)字以文本輸入 “”快捷激活單元格F2巧用替換和通配符*，！分頁(yè)預(yù)覽（右下按鈕）大小寫(xiě)字母轉(zhuǎn)換，按Shift建立圖表F11快速關(guān)閉，雙擊off復(fù)制或轉(zhuǎn)移工作表（工作組）工作組同步操作第24頁(yè)，共30頁(yè)。21. 常用函數(shù)日期函數(shù)求值函數(shù)IF：兩條件判斷SUMIF：條件求和(舉例)COUNTIF：條件計(jì)數(shù)ABS：絕對(duì)值MOD:除余第25頁(yè)，共30頁(yè)。22.輕松換行1.輸入數(shù)據(jù)

14、隨時(shí)換行Alt+Enter組合鍵2.單元格區(qū)域內(nèi)換行選定區(qū)域，選擇“編輯填充內(nèi)容重排”3.調(diào)整單元格格式換行“格式單元格-自動(dòng)換行”4.文本框的巧用為了保證文本框的邊界與工作表網(wǎng)格線重合，需按住Alt鍵的同時(shí)插入文本框第26頁(yè)，共30頁(yè)。23.尋遍天下-VLOOKUP適用范圍：兩表或區(qū)域有共同的項(xiàng)或類(lèi)似的項(xiàng)，從一表或區(qū)域獲取另一表或區(qū)域的數(shù)據(jù) =vlookup(本表共同列或單元格,另一表共同項(xiàng)開(kāi)始右邊區(qū)域,所取數(shù)據(jù)在表中共同項(xiàng)開(kāi)始右邊列數(shù),精確”0”或模糊匹配參數(shù))精確匹配:共同項(xiàng)內(nèi)容一致,文本或數(shù)值格式一致模糊匹配:共同項(xiàng)內(nèi)容在某一區(qū)域中,常用于按區(qū)域分類(lèi),記住向上區(qū)域原則例如客戶根據(jù)銷(xiāo)售額

15、進(jìn)行ABC分類(lèi)等第27頁(yè)，共30頁(yè)。24.剝皮抽筋數(shù)據(jù)透視-隨心所欲,徹底分析透視表/圖,是一個(gè)統(tǒng)計(jì)工具注意點(diǎn)：1）列列有頭:數(shù)據(jù)透視區(qū)域第一行必須有字段名, 2）雙擊顯示:雙擊透視表某一數(shù)據(jù),可創(chuàng)建該數(shù)據(jù)的明細(xì)表 3）插入計(jì)算字段 4）去除空白:用替換功能把”(空白)”替換成空格” “，一定要空格 5）下拉選擇：類(lèi)似篩選條件下的選擇，數(shù)據(jù)會(huì)自動(dòng)更新 6）列行總計(jì)：表格選項(xiàng)-行（列）總計(jì) 7）按需排序：點(diǎn)擊字段表頭，字段設(shè)置-高級(jí)-升（降）序 8）批選小計(jì)：選定-啟用選定內(nèi)容，鼠標(biāo)點(diǎn)擊小計(jì)行，可批量選中小計(jì)行 9）表圖轉(zhuǎn)換：右擊透視表某一區(qū)域，選透視圖創(chuàng)建 10）條件格式套用第28頁(yè)，共30頁(yè)。25