IE小組 EXCEL技能培訓

IE組EXCEL技能培訓匯報人：XXX匯報時間：20200820第一講EXCEL的認識及入門操作第二講查找、定位、替換第三講排序、篩選第四講數據透視表第五講函數與公式目錄第一節(jié)EXCEL的認識及入門操作

一、

EXCEL初認識及功能簡介1.

EXCEL是由MICROSOFT公司開發(fā)的辦公套裝軟件中的一個組件，用于處理和分析數據。2.

EXCEL中進行數據處理，如公式計算、數字錄入時，必須在半角實心狀態(tài)或英文狀態(tài)下操作。工作表標簽標題欄名稱框編輯欄行標題列標題狀態(tài)欄功能區(qū)選項卡Ctrl+S保存Ctrl+W關閉程序Ctrl+N新建Ctrl+O打開Ctrl+Z撤銷Ctrl+F查找Ctrl+X剪切Ctrl+C復制Ctrl+V粘貼Ctrl+A全選Ctrl+[縮小文字Ctrl+]放大文字Ctrl+B粗體Ctrl+I斜體Ctrl+U下劃線Ctrl+Shift輸入法切換Ctrl+空格中英文切換Ctrl+回車發(fā)送信息3、常用快捷鍵Ctrl+Home光標快速移到文件頭Ctrl+End光標快速移到文件尾Ctrl+Esc顯示開始菜單Ctrl+Shift+<快速縮小文字Ctrl+Shift+>快速放大文字Ctrl+F5在IE中強行刷新Ctrl+拖動文件復制文件Ctrl+Backspace啟動\關閉輸入法拖動文件時按住Ctrl+Shift創(chuàng)建快捷方式Alt+空格+C關閉窗口Alt+空格+N最小化當前窗口Alt+空格+R恢復最小化窗口Alt+空格+X最大化當前窗口Alt+空格+M移動窗口Alt+空格+S改變窗口大小Alt+Tab兩個程序交換Alt+255QQ號中輸入無名人Alt+F打開文件菜單Alt+V打開視圖菜單Alt+E打開編輯菜單Alt+I打開插入菜單Alt+O打開格式菜單Alt+T打開工具菜單Alt+A打開表格菜單Alt+W打開窗口菜單Alt+H打開幫助菜單Alt+回車查看文件屬性Alt+雙擊文件查看文件屬性Alt+X關閉C語言Shift快捷鍵Shift+空格半\全角切換Shift+Delete永久刪除所選項，而不將它放到“回收站”中拖動某一項時按CTRL復制所選項。拖動某一項時按CTRL+SHIFT創(chuàng)建所選項目的快捷鍵。第二節(jié)查找與替換、定位

地區(qū)姓名日憑證號數部門科目劃分發(fā)生額蘇州張三29記-0023一車間郵寄費5.00蘇州張四四29記-0021一車間出租車費14.80蘇州市張五31記-0031二部郵寄費20.00蘇州張六29記-0022二廠過橋過路費50.00蘇州市區(qū)李七29記-0023二車間運費附加56.00蘇州李八24記-0008財務部獨子費65.00蘇州市區(qū)李九29記-0021二廠過橋過路費70.00蘇州張三29記-0023一車間郵寄費5.00蘇州張四四29記-0021一車間出租車費14.80蘇州市張五31記-0031二部郵寄費20.00蘇州張六29記-0022二廠過橋過路費50.00蘇州市區(qū)李七29記-0023二車間運費附加56.00蘇州張*24記-0008財務部獨子費65.00蘇州市區(qū)李九29記-0021二廠過橋過路費70.001將姓張的員工都改為"經理的親戚"2將姓張并且名字為兩個字的員工都改為"經理的親戚"3僅將張*替換為"經理的親戚"1、查找與替換銷售記錄項目

訂購日期ERPCO號所屬區(qū)域產品類別數量金額成本2007年3月21日C014673-004北京寵物用品1619,269.6918,982.852007年4月28日C014673-005寵物用品4039,465.1740,893.082007年4月28日C014673-006寵物用品2021,015.9422,294.092007年5月31日C014673-007上海寵物用品2023,710.2624,318.372007年6月13日C014673-008寵物用品1620,015.0720,256.692007年7月16日C014673-009寵物用品20040,014.1243,537.562007年9月14日C014673-010寵物用品10021,423.9522,917.342007年10月19日C014673-011寵物用品20040,014.1244,258.362007年11月20日C014673-012廣州寵物用品40084,271.4992,391.152007年3月21日C014673-013寵物用品21248,705.6651,700.032007年4月28日C014673-014寵物用品22447,192.0350,558.502007年4月28日C014673-015寵物用品9221,136.4222,115.232007年5月31日C014673-016深圳寵物用品10027,499.5130,712.182007年6月13日C014673-019寵物用品14029,993.5332,726.662007年7月16日C015084-001寵物用品10834,682.7635,738.662007年9月14日C015084-002寵物用品7212,492.9511,098.922007年10月19日C014673-001寵物用品3230,449.3129,398.002007年11月20日C014673-002寵物用品1212,125.3011,641.51合并單元格不能設公式，比如SUMIF,COUTIF等，也不能用來透視，那該怎么辦？2、定位到有批注、公式的單元格，以及定位公值，分解合并單元格第三節(jié)排序、篩選

訂購日期ERPCO號所屬區(qū)域銷售部門數量金額成本2007年3月21日C014673-004蘇州四部1619,269.6918,982.852007年4月28日C014673-005蘇州二部4039,465.1740,893.082007年4月28日C014673-006蘇州二部2021,015.9422,294.092007年5月31日C014673-007蘇州一部2023,710.2624,318.372007年6月13日C014673-008蘇州三部1620,015.0720,256.692007年7月16日C014673-009蘇州一部20040,014.1243,537.562007年9月14日C014673-010蘇州四部10021,423.9522,917.342007年10月19日C014673-011蘇州五部20040,014.1244,258.362007年11月20日C014673-012蘇州二部40084,271.4992,391.152007年3月21日C014673-013常熟一部21248,705.6651,700.032007年4月28日C014673-014常熟二部22447,192.0350,558.502007年4月28日C014673-015常熟五部9221,136.4222,115.232007年5月31日C014673-016常熟四部10027,499.5130,712.182007年6月13日C014673-019常熟二部14029,993.5332,726.662007年7月16日C015084-001常熟五部10834,682.7635,738.662007年9月14日C015084-002常熟五部7212,492.9511,098.922007年10月19日C014673-001常熟一部3230,449.3129,398.002007年11月20日C014673-002常熟三部1212,125.3011,641.511、按自定義部門排序2、利用排序的方法巧制工資條姓名工號月基本薪資加其它應付工資事假扣款缺勤扣款應發(fā)工資小計個人承擔的公積金所得稅實發(fā)數汪梅SU10016300486348038842464002464郭磊SU10026300630000630001525922林濤SU10036006000060000600朱健SU10042400240000240010002074李明SU100520003220320123379900799王建國SU10063000300000300010002674陳玉SU10074000400018020219810001872張華SU10081800180004891311001311李麗SU100916001600001600001374汪成SU101030003000003000002774李軍SU10113000300023017351035001035Page123、高級篩選的使用月日憑證號數部門科目劃分發(fā)生額0129記-0023一車間郵寄費5.000129記-0021一車間出租車費14.800131記-0031二車間郵寄費20.000129記-0022二車間過橋過路費50.000129記-0023二車間運費附加56.000124記-0008財務部獨子費65.000129記-0021二車間過橋過路費70.000129記-0022銷售1部出差費78.000129記-0022經理室手機電話費150.000129記-0026二車間郵寄費150.000124記-0008二車間話費補180.000129記-0021人力資源部資料費258.000131記-0037二車間辦公用品258.500124記-0008財務部養(yǎng)老保險267.080129記-0027二車間出租車費277.700131記-0037經理室招待費278.000131記-0031銷售1部手機電話費350.000129記-0027銷售1部出差費408.000129記-0022銷售1部出差費560.000129記-0022二車間交通工具消耗600.000124記-0008財務部采暖費補助925.000129記-0027經理室招待費953.000129記-0022二車間過橋過路費1,010.000129記-0022二車間交通工具消耗1,016.780129記-0026二車間郵寄費1,046.001篩選出所有科目2篩選出財務部或發(fā)生額大于3000的數據3篩選出成本大于金額的數據第四節(jié)數據透視

1、創(chuàng)建數據透視表1、求和各部門每月份發(fā)生額的匯總月日憑證號數部門科目劃分發(fā)生額0129記-0023一車間郵寄費5.000129記-0021一車間出租車費14.800131記-0031二車間郵寄費20.000129記-0022二車間過橋過路費50.000129記-0023二車間運費附加56.000124記-0008財務部獨子費65.000129記-0021二車間過橋過路費70.000129記-0022銷售1部出差費78.000129記-0022經理室手機電話費150.000129記-0026二車間郵寄費150.000124記-0008二車間話費補180.000129記-0021人力資源部資料費258.000131記-0037二車間辦公用品258.500124記-0008財務部養(yǎng)老保險267.080129記-0027二車間出租車費277.700131記-0037經理室招待費278.000131記-0031銷售1部手機電話費350.000129記-0027銷售1部出差費408.000129記-0022銷售1部出差費560.000129記-0022二車間交通工具消耗600.000124記-0008財務部采暖費補助925.000129記-0027經理室招待費953.000129記-0022二車間過橋過路費1,010.000129記-0022二車間交通工具消耗1,016.780129記-0026二車間郵寄費1,046.000124記-0008人力資源部教育經費1,066.25訂購日期發(fā)票號銷售部門銷售人員工單號ERPCO號所屬區(qū)域產品類別數量金額成本2007/3/21H00012769三科劉輝A12-086C014673-004蘇州寵物用品1619,269.6918,982.852007/4/28H00012769三科劉輝A12-087C014673-005蘇州寵物用品4039,465.1740,893.082007/4/28H00012769三科劉輝A12-088C014673-006蘇州寵物用品2021,015.9422,294.092007/5/31H00012769三科劉輝A12-089C014673-007蘇州寵物用品2023,710.2624,318.372007/6/13H00012769三科劉輝A12-090C014673-008蘇州寵物用品1620,015.0720,256.692007/7/16H00012769三科劉輝A12-091C014673-009蘇州寵物用品20040,014.1243,537.562007/9/14H00012769三科劉輝A12-092C014673-010蘇州寵物用品10021,423.9522,917.342007/10/19H00012769三科劉輝A12-093C014673-011蘇州寵物用品20040,014.1244,258.362007/11/20H00012769三科劉輝A12-094C014673-012蘇州寵物用品40084,271.4992,391.152007/3/21H00012769三科劉輝A12-095C014673-013常熟寵物用品21248,705.6651,700.032007/4/28H00012769三科劉輝A12-096C014673-014常熟寵物用品22447,192.0350,558.502007/4/28H00012769三科劉輝A12-097C014673-015常熟寵物用品9221,136.4222,115.232007/5/31H00012769三科劉輝A12-098C014673-016常熟寵物用品10027,499.5130,712.182007/6/13H00012769三科劉輝A12-101C014673-019常熟寵物用品14029,993.5332,726.662007/7/16H00012774三科劉輝A11-155C015084-001常熟寵物用品10834,682.7635,738.662007/9/14H00012774三科劉輝A11-156C015084-002常熟寵物用品7212,492.9511,098.922007/10/19H00012774三科劉輝A12-083C014673-001常熟寵物用品3230,449.3129,398.002007/11/20H00012774三科劉輝A12-084C014673-002常熟寵物用品1212,125.3011,641.512007/3/23H00012774三科劉輝A12-085C014673-003蘇州寵物用品2022,920.9622,707.052007/9/24H00012774三科劉輝A12-178C015240-001蘇州服裝6017,794.9318,667.472007/3/23H00012774三科劉輝A12-179C015240-002常熟寵物用品248,325.078,601.752007/4/28H00012792三科劉輝D01-119C015960-001蘇州寵物用品400067,654.5835,420.342007/5/31H00012793三科劉輝A03-077C014980-026蘇州寵物用品10020,199.9421,929.732、創(chuàng)建透視表中的組合1、按所屬區(qū)域、訂購季度分類，求和每種產品類別的總金額第五節(jié)函數與公式

（一）函數和公式1.什么是函數

Excel函數即是預先定義，執(zhí)行計算、分析等處理數據任務的特殊公式。以常用的求和函數SUM為例，它的語法是

“SUM(number1,number2,)”。其中“SUM”稱為函數名稱，一個函數只有唯一的一個名稱，它決定了函數的功能和用途。

函數名稱后緊跟左括號，接著是用逗號分隔的稱為參數的內容，最后用一個右括號表示函數結束。參數是函數中最復雜的組成部分，它規(guī)定了函數的運算對象、順序或結構等。使得用戶可以對某個單元格或區(qū)域

進行處理，如分析存款利息、確定成績名次、計算三角函數值等。按照函數的來源，Excel函數可以分為內置函數和擴展函數兩大類。前者只要啟動了Excel，用戶就可以使用它們；

而后者必須通過單擊“工具→加載宏”菜單命令加載，然后才能像內置函數那樣使用。2.什么是公式函數與公式既有區(qū)別又互相聯系。如果說前者是Excel預先定義好的特殊公式，后者就是由用戶自行設計對工作表

進行計算和處理的公式。以公式“=SUM(E1:H1)*A1+26”為例，它要以等號“=”開始，其內部可以包括函數、引用、運算

符和常量。上式中的“SUM(E1:H1)”是函數，“A1”則是對單元格A1的引用(使用其中存儲的數據)，“26”則是常量，

“*”和“+”則是算術運算符(另外還有比較運算符、文本運算符和引用運算符)。如果函數要以公式的形式出現，它必

須有兩個組成部分，一個是函數名稱前面的等號，另一個則是函數本身。1、if

邏輯函數使用IF常用指數★★★★★說明:判斷指定的值,如果滿足條件時則…,如果不滿足條件時則….公式可以理解為:=IF(條件,符合條件時的計算方式,不符合條件時的計算方式.)參數定義:參數用逗號分開,最多可以指定30個.要點:03版中最多可以嵌套使用7個IF函數;07版中最多可以嵌套64個IF函數.使用格式IF(Logical,Value_if_true,Value_if_false)Logical代表邏輯判斷表達式；Value_if_true表示當判斷條件為邏輯“真（TRUE）”時的顯示內容，如果忽略返回“TRUE”；Value_if_false表示當判斷條件為邏輯“假（FALSE）”時的顯示內容，如果忽略返回“FALSE”。編號專業(yè)類專業(yè)代號姓名性別稱呼來源原始分總分錄取情況wj101理工

令狐沖男

本地599

wj102理工

任盈盈女

本地661

wj103理工

林平之男

本省467

wj101文科

岳靈珊男

本省310

wj102文科

儀琳女

本省584

wj103財經

曲飛燕女

外省260

wj101財經

田伯光女

本省406

wj102文科

向問天女

本地771

wj103文科

劉振峰男

本地765

wj101理工

陸大有男

本地522

wj102理工

勞德諾女

本地671

wj103文科

左冷禪男

本地679

wj101理工

東方不敗女

本省596

wj102財經

上官云女

外省269

wj103財經

楊蓮亭女

外省112

wj101文科

童百熊女

本省396

wj102理工

木高峰女

本地712

wj103文科

余滄海女

本省354

wj101文科

寧中則男

本地793

wj102理工

陶根仙女

本地654

wj103理工

孫靜女

外省300

wj101理工

戚旭國女

本地528

wj102財經

程曉男

本地578

wj103財經

張小清女

外省77

wj101理工

童桂香女

本地539

1、專業(yè)類后加專業(yè)代號理工：LG文科：WK財經：CJ2、本地學生總分為原始分加30本省學生總分為原始分加20外省學生總分為原始分加103、錄取情況600分含600

顯示第一批400-600含400分顯示第二批400分以下

落榜2、Vlookup函數使用VLOOKUP常用指數★★★☆☆

說明:按照垂直方向搜索"Table_array"的首例,搜索出滿足指定"Lookup_value"的值,或者搜索出小于搜索值的最大值.返回值為與查找到的單元格同行的,指定的"Col_index_num"(列序號)右移的單元格的值.即,指定搜索條件,設定搜索區(qū)域,向右設定至某列.結果為找出在搜索區(qū)域中與條件相同的數據再向右移動N行后(同一行)找出相應結果.使用格式：VLOOKUP(lookup_value?table_array?col_index_num?range_lookup)參數定義:英文字母不區(qū)分大小寫;右移列數從第一列開始;搜索區(qū)域為含條件列至結果列.要點:適用于縱長的表格的搜索,搜索的列通常為首列.當搜索不到滿足值時,返回錯誤值"#N/A".當超出搜索區(qū)域時,返回錯誤值"#REF".特別提醒：Lookup_value參見必須在Table_array區(qū)域的首列中；如果忽略Range_lookup參數，則Table_array的首列必須進行排序；在此函數的向導中，有關Range_lookup參數的用法是錯誤的Lookup_value代表需要查找的數值Table_array代表需要在其中查找數據的單元格區(qū)域Col_index_num為在table_array區(qū)域中待返回的匹配值的列序號（當Col_index_num為2時?返回table_array第2列中的數值，為3時，返回第3列的值……）Range_lookup為一邏輯值，如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數值；如果為FALSE，則返回精確匹配值，如果找不到，則返回錯誤值#N/A獎金評定標準業(yè)務員等級獎金A級10000B級9000C級8000D級7000E級6000F級5000G級4000H級3000I級2000J級1000獎金計算姓名業(yè)務員等級應發(fā)獎金張三A級

李四C級

王五F級

趙六D級

獎金等級業(yè)務員等級獎金等級A級一級B級二級C級三級D級四級E級五級F級六級獎金等級姓名業(yè)務員等級獎