IE小組 EXCEL技能培訓(xùn)

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

一、

EXCEL初認(rèn)識及功能簡介1.

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

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

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

訂購日期ERPCO號所屬區(qū)域產(chǎn)品類別數(shù)量金額成本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合并單元格不能設(shè)公式，比如SUMIF,COUTIF等，也不能用來透視，那該怎么辦？2、定位到有批注、公式的單元格，以及定位公值，分解合并單元格第三節(jié)排序、篩選

訂購日期ERPCO號所屬區(qū)域銷售部門數(shù)量金額成本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、利用排序的方法巧制工資條姓名工號月基本薪資加其它應(yīng)付工資事假扣款缺勤扣款應(yīng)發(fā)工資小計個人承擔(dān)的公積金所得稅實(shí)發(fā)數(shù)汪梅SU10016300486348038842464002464郭磊SU10026300630000630001525922林濤SU10036006000060000600朱健SU10042400240000240010002074李明SU100520003220320123379900799王建國SU10063000300000300010002674陳玉SU10074000400018020219810001872張華SU10081800180004891311001311李麗SU100916001600001600001374汪成SU101030003000003000002774李軍SU10113000300023017351035001035Page123、高級篩選的使用月日憑證號數(shù)部門科目劃分發(fā)生額0129記-0023一車間郵寄費(fèi)5.000129記-0021一車間出租車費(fèi)14.800131記-0031二車間郵寄費(fèi)20.000129記-0022二車間過橋過路費(fèi)50.000129記-0023二車間運(yùn)費(fèi)附加56.000124記-0008財務(wù)部獨(dú)子費(fèi)65.000129記-0021二車間過橋過路費(fèi)70.000129記-0022銷售1部出差費(fèi)78.000129記-0022經(jīng)理室手機(jī)電話費(fèi)150.000129記-0026二車間郵寄費(fèi)150.000124記-0008二車間話費(fèi)補(bǔ)180.000129記-0021人力資源部資料費(fèi)258.000131記-0037二車間辦公用品258.500124記-0008財務(wù)部養(yǎng)老保險267.080129記-0027二車間出租車費(fèi)277.700131記-0037經(jīng)理室招待費(fèi)278.000131記-0031銷售1部手機(jī)電話費(fèi)350.000129記-0027銷售1部出差費(fèi)408.000129記-0022銷售1部出差費(fèi)560.000129記-0022二車間交通工具消耗600.000124記-0008財務(wù)部采暖費(fèi)補(bǔ)助925.000129記-0027經(jīng)理室招待費(fèi)953.000129記-0022二車間過橋過路費(fèi)1,010.000129記-0022二車間交通工具消耗1,016.780129記-0026二車間郵寄費(fèi)1,046.001篩選出所有科目2篩選出財務(wù)部或發(fā)生額大于3000的數(shù)據(jù)3篩選出成本大于金額的數(shù)據(jù)第四節(jié)數(shù)據(jù)透視

1、創(chuàng)建數(shù)據(jù)透視表1、求和各部門每月份發(fā)生額的匯總月日憑證號數(shù)部門科目劃分發(fā)生額0129記-0023一車間郵寄費(fèi)5.000129記-0021一車間出租車費(fèi)14.800131記-0031二車間郵寄費(fèi)20.000129記-0022二車間過橋過路費(fèi)50.000129記-0023二車間運(yùn)費(fèi)附加56.000124記-0008財務(wù)部獨(dú)子費(fèi)65.000129記-0021二車間過橋過路費(fèi)70.000129記-0022銷售1部出差費(fèi)78.000129記-0022經(jīng)理室手機(jī)電話費(fèi)150.000129記-0026二車間郵寄費(fèi)150.000124記-0008二車間話費(fèi)補(bǔ)180.000129記-0021人力資源部資料費(fèi)258.000131記-0037二車間辦公用品258.500124記-0008財務(wù)部養(yǎng)老保險267.080129記-0027二車間出租車費(fèi)277.700131記-0037經(jīng)理室招待費(fèi)278.000131記-0031銷售1部手機(jī)電話費(fèi)350.000129記-0027銷售1部出差費(fèi)408.000129記-0022銷售1部出差費(fèi)560.000129記-0022二車間交通工具消耗600.000124記-0008財務(wù)部采暖費(fèi)補(bǔ)助925.000129記-0027經(jīng)理室招待費(fèi)953.000129記-0022二車間過橋過路費(fèi)1,010.000129記-0022二車間交通工具消耗1,016.780129記-0026二車間郵寄費(fèi)1,046.000124記-0008人力資源部教育經(jīng)費(fèi)1,066.25訂購日期發(fā)票號銷售部門銷售人員工單號ERPCO號所屬區(qū)域產(chǎn)品類別數(shù)量金額成本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ū)域、訂購季度分類，求和每種產(chǎn)品類別的總金額第五節(jié)函數(shù)與公式

（一）函數(shù)和公式1.什么是函數(shù)

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

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

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

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

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

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

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

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

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

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

令狐沖男

本地599

wj102理工

任盈盈女

本地661

wj103理工

林平之男

本省467

wj101文科

岳靈珊男

本省310

wj102文科

儀琳女

本省584

wj103財經(jīng)

曲飛燕女

外省260

wj101財經(jīng)

田伯光女

本省406

wj102文科

向問天女

本地771

wj103文科

劉振峰男

本地765

wj101理工

陸大有男

本地522

wj102理工

勞德諾女

本地671

wj103文科

左冷禪男

本地679

wj101理工

東方不敗女

本省596

wj102財經(jīng)

上官云女

外省269

wj103財經(jīng)

楊蓮?fù)づ?/p>

外省112

wj101文科

童百熊女

本省396

wj102理工

木高峰女

本地712

wj103文科

余滄海女

本省354

wj101文科

寧中則男

本地793

wj102理工

陶根仙女

本地654

wj103理工

孫靜女

外省300

wj101理工

戚旭國女

本地528

wj102財經(jīng)

程曉男

本地578

wj103財經(jīng)

張小清女

外省77

wj101理工

童桂香女

本地539

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

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

落榜2、Vlookup函數(shù)使用VLOOKUP常用指數(shù)★★★☆☆

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

李四C級

王五F級

趙六D級

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