Excel應(yīng)用培訓(xùn)課件

主講人：

Excel實(shí)操應(yīng)用培訓(xùn)目錄

IIIIIIIV基本操作——原來可以如此簡(jiǎn)單表格之美——表格也是門藝術(shù)函數(shù)圖表——Excel的精髓之處實(shí)戰(zhàn)演練——玩轉(zhuǎn)excel不是夢(mèng)excel前言——Excel學(xué)習(xí)進(jìn)級(jí)有哪些階段？VBA編程VBA編程函數(shù)、圖表函數(shù)、圖表數(shù)據(jù)透視表基本操作基本操作數(shù)據(jù)透視表常用快捷鍵排序與篩選分列數(shù)據(jù)有效性條件格式刪除重復(fù)項(xiàng)不定行數(shù)據(jù)填充基本操作表格之美函數(shù)圖表實(shí)戰(zhàn)演練基本操作

——原來可以如此簡(jiǎn)單基本操作——常用快捷鍵

快捷鍵Ctrl+C——復(fù)制選定區(qū)域Ctrl+v——粘貼選定區(qū)域Ctrl+x——裁剪選定區(qū)域選定區(qū)域+Ctrl+d——縱向填充第一列單元格內(nèi)容選定區(qū)域+Ctrl+r——橫向填充第一行單元格內(nèi)容Windows鍵+d——顯示桌面Ctrl+b——加粗單元格字體Ctrl+f/h——查找與替換Tab+Alt——當(dāng)前程序與上個(gè)程序間快速切換Tab+Ctrl——同類型程序間快速切換Alt+Enter

——單元格內(nèi)換行

Ctrl+End——移到文本框的末尾Ctrl+Home——移到文本框的開頭Shift+Ctrl+End——選定自光標(biāo)所在單元格以下所有非空單元格Shift+Ctrl+Home——選定自光標(biāo)所在單元格以有所以非空單元格Ctrl+A——選定所有非空單元格選定對(duì)象+Shift+箭頭狀鼠標(biāo)拖動(dòng)——任意行間拖動(dòng)選定對(duì)象+Ctrl+箭頭狀鼠標(biāo)拖動(dòng)——任意行間復(fù)制粘貼基本操作——排序與篩選

幾類典型排序方法排序法——自定義序列法典型案例：按職務(wù)高低進(jìn)行排序排序法——對(duì)排序選項(xiàng)進(jìn)行設(shè)定典型案例：按單元格填充顏色排序（若存在多種顏色可通過“復(fù)制條件”選項(xiàng)來進(jìn)行次關(guān)鍵字排序）排序注意事項(xiàng)：對(duì)帶公式的單元格進(jìn)行排序注意事項(xiàng)篩選的基本操作篩選的類型——按文本特征篩選、按數(shù)字特征篩選、按日期特征篩選、按字體顏色或單元格顏色篩選篩選的基本操作——選定區(qū)域、篩選、搜索關(guān)鍵字自定義自動(dòng)篩選方式篩選的注意事項(xiàng)：雙行標(biāo)題的工作表篩選若只對(duì)一列單元格數(shù)據(jù)進(jìn)行篩選，則需選中標(biāo)題所在單元格及其下方至少一個(gè)單元格，否則默認(rèn)為整個(gè)工作表。操作與技巧進(jìn)階——分列利用分列主要功用：解決從別處粘貼的數(shù)據(jù)無法運(yùn)算的問題按規(guī)則拆分標(biāo)示顯示路徑：菜單欄【數(shù)據(jù)】--【分列】操作與技巧進(jìn)階——數(shù)據(jù)有效性數(shù)據(jù)有效性的作用

①

限制單元格的輸入或圈識(shí)無效數(shù)據(jù)②做單元格下拉表利用數(shù)據(jù)有效性對(duì)單元格輸入限制數(shù)據(jù)有效性的來源可文本也可公式，若是文本，需英文狀態(tài)下的逗號(hào)作為分隔符。路徑：菜單欄【數(shù)據(jù)】--【數(shù)據(jù)有效性】操作與技巧進(jìn)階——避免重復(fù)值錄入

實(shí)戰(zhàn)日常工作中，身份證號(hào)碼、工作證編碼等ID編號(hào)是唯一的，不允許重復(fù)，若在excel中重復(fù)錄入ID，就會(huì)給信息管理帶來不便，我們可以通過數(shù)據(jù)有效性來解決這個(gè)問題：切換到“設(shè)置”選項(xiàng)卡，打開“允許”下拉菜單，選擇【自定義】在公式處輸入=countif(a:a,a1)=1（英文狀態(tài)下輸入）切換到“出錯(cuò)警告”選項(xiàng)卡，選擇出錯(cuò)警告信息的樣式，填寫標(biāo)題和錯(cuò)誤信息，然后點(diǎn)擊【確定】COUNTIF函數(shù)可以統(tǒng)計(jì)單元格區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。該函數(shù)的語法結(jié)構(gòu)為：COUNTIF(rage,criteria),其參數(shù)range表示需要統(tǒng)計(jì)其中滿足條件的單元格數(shù)目的單元格區(qū)域；criteria表示指定的統(tǒng)計(jì)條件，其形式可以為數(shù)字、表達(dá)式、單元格引用或文本。在運(yùn)用COUNTIF函數(shù)時(shí)要注意，當(dāng)參數(shù)criteria為表達(dá)式或文本時(shí)，必須用引號(hào)引起來，否則將提示出錯(cuò)。操作與技巧進(jìn)階——條件格式利用條件格式主要功用：按規(guī)則突出顯示：數(shù)值、文本、如期等規(guī)則按規(guī)則建立標(biāo)示顯示快速標(biāo)示重復(fù)項(xiàng)路徑：菜單欄【開始】--【條件格式】注意使用“項(xiàng)目選取規(guī)則、數(shù)據(jù)條、色階、圖標(biāo)集”等功能時(shí)，所選擇的范圍僅能包含數(shù)值數(shù)據(jù)，不能存在文本的單元格。操作與技巧進(jìn)階——?jiǎng)h除重復(fù)項(xiàng)

拓展刪除重復(fù)值路徑：菜單欄【數(shù)據(jù)】--【刪除重復(fù)項(xiàng)】操作與技巧進(jìn)階——不定行數(shù)據(jù)填充選定需要填充的數(shù)據(jù)按“F5”，彈出定位選項(xiàng)框，點(diǎn)擊【定位條件】，選擇【空值】，點(diǎn)擊確定。在公式欄輸入選定區(qū)域的第一個(gè)單元格，同時(shí)按Ctrl+Enter。數(shù)據(jù)透視表的制作如何用含有合并單元格的數(shù)據(jù)來創(chuàng)建數(shù)據(jù)透視表？基本操作表格之美函數(shù)圖表實(shí)戰(zhàn)演練表格之美

——表格也是門藝術(shù)數(shù)據(jù)透視表面對(duì)一簍筐的數(shù)據(jù)，你還在一個(gè)個(gè)按計(jì)算器求和嗎？或者還在用SUM函數(shù)嗎？如果是，你奧特曼了！數(shù)據(jù)透視表

功能介紹：

數(shù)據(jù)透視表能夠?qū)⒑Y選、排序和分類匯總等操作依次完成，并生成匯總表格。Example:

資金日?qǐng)?bào)表

1、透視表的建立（單擊excel2010版插入按鈕）

2、選項(xiàng)及設(shè)計(jì)功能介紹

3、存在合并單元格透視表會(huì)提示字段名無效

數(shù)據(jù)透視表如何用含有合并單元格的數(shù)據(jù)來創(chuàng)建數(shù)據(jù)透視表？特殊應(yīng)用數(shù)據(jù)透視表解決方法：

取消合并單元格，填充相同內(nèi)容，然后創(chuàng)建數(shù)據(jù)透視表。附加學(xué)習(xí)點(diǎn)：

相隔不規(guī)則行數(shù)填充內(nèi)容。數(shù)據(jù)透視表步驟1如圖所示，選中合并單元格區(qū)域A2:A21→單擊“開始”選項(xiàng)卡→“對(duì)齊方式”組→“合并后居中”，取消合并單元格。數(shù)據(jù)透視表步驟2

如圖所示，選中取消合并后的單元格區(qū)域A2:A21→按F5，在彈出的“定位條件”對(duì)話框中選擇“空值”→“確定”，即可定位并選中單元格區(qū)域A2:A18中所有空值的單元格數(shù)據(jù)透視表步驟3

如圖所示，在選中的空白單元格中輸入“=A2”，然后按【Ctrl+Enter】組合鍵，即可實(shí)現(xiàn)空白單元格填充相同項(xiàng)。數(shù)據(jù)透視表步驟4

按之前講訴方法創(chuàng)建數(shù)據(jù)透視表。數(shù)據(jù)透視表如何在數(shù)據(jù)透視表中將兩個(gè)求和項(xiàng)再求和？特殊應(yīng)用選擇求和項(xiàng)--在【數(shù)據(jù)透視表工具】--【選項(xiàng)】--【域、項(xiàng)目合集】數(shù)據(jù)透視表—如何兩個(gè)求和項(xiàng)再求和？

在彈出計(jì)算字段選項(xiàng)框中，把名稱改為“合計(jì)”，在公式框中，從下面字段框中雙擊選擇“金額”，輸入“+”，在雙擊選中“滯納金”，點(diǎn)擊確定即可。VLOOKUP函數(shù)的應(yīng)用SUMIF函數(shù)應(yīng)用COUNTIF函數(shù)應(yīng)用IF函數(shù)應(yīng)用基本操作表格之美函數(shù)圖表實(shí)戰(zhàn)演練函數(shù)圖表

——Excel的精髓之處VLOOKUP函數(shù)快速查找指定數(shù)值從懂得VLOOKUP開始視頻教學(xué)功能介紹：

在表格數(shù)值或數(shù)組的首列查找指定的數(shù)值，并由此返回表格或者數(shù)組中該數(shù)值所在行中指定列處的數(shù)值。VLOOKUP函數(shù)注意事項(xiàng)：

1、如果“查找值”小于“區(qū)域”第一列中的最小值，函數(shù)VLOOKUP返回錯(cuò)誤值#N/A。2、如果函數(shù)VLOOKUP找不到“查找值”，且“邏輯值”為FALSE,函數(shù)VLOOKUP返回錯(cuò)誤值#N/A。3、VLOOKUP的查找值如果在索引區(qū)域內(nèi)有多個(gè)對(duì)應(yīng)值時(shí)，只能返回首條記錄的對(duì)應(yīng)索引數(shù)據(jù)。4、VLOOKUP的查找值相對(duì)應(yīng)的索引區(qū)域的對(duì)應(yīng)列，必須位于索引區(qū)域的首列。SUMIF函數(shù)案例：請(qǐng)根據(jù)《房產(chǎn)資料》工作表，用Sumif函數(shù)統(tǒng)計(jì)出“項(xiàng)目名稱”為“高爾夫三期”的所有房間建筑面積之和。演練SUMIF函數(shù)是Excel常用函數(shù)。使用SUMIF函數(shù)可以對(duì)報(bào)表范圍中符合指定條件的值求和。Excel中sumif函數(shù)的用法是根據(jù)指定條件對(duì)若干單元格、區(qū)域或引用求和。第一個(gè)參數(shù)：Range為條件區(qū)域，用于條件判斷的單元格區(qū)域。第二個(gè)參數(shù)：Criteria是求和條件，由數(shù)字、邏輯表達(dá)式等組成的判定條件。第三個(gè)參數(shù)：Sum_range為實(shí)際求和區(qū)域，需要求和的單元格、區(qū)域或引用。答案：'=SUMIF(房產(chǎn)資料!A:A,"高爾夫三期",房產(chǎn)資料!G:G)COUNTIF函數(shù)案例：請(qǐng)根據(jù)《房產(chǎn)資料》工作表，用Countif函數(shù)統(tǒng)計(jì)出“項(xiàng)目名稱”為“高爾夫二期”的房間個(gè)數(shù)(房產(chǎn)戶數(shù))。演練Countif函數(shù)是MicrosoftExcel中對(duì)指定區(qū)域中符合指定條件的單元格計(jì)數(shù)的一個(gè)函數(shù)參數(shù)：range要計(jì)算其中非空單元格數(shù)目的區(qū)域參數(shù)：criteria以數(shù)字、表達(dá)式或文本形式定義的條件答案：'=COUNTIF(房產(chǎn)資料!A:A,"高爾夫二期")IF函數(shù)案例：請(qǐng)用If函數(shù)判別，若上題“項(xiàng)目名稱”為“高爾夫二期”的房間個(gè)數(shù)超出370個(gè)，則提示“統(tǒng)計(jì)正確”，否則提示“統(tǒng)計(jì)錯(cuò)誤”。演練IF函數(shù)一般是指Excel中的IF函數(shù)，根據(jù)指定的條件來判斷其“真”（TRUE）、“假”（FALSE），根據(jù)邏輯計(jì)算的真假值，從而返回相應(yīng)的內(nèi)容。IF(logical_test,value_if_true,value_if_false)Logical_test表示計(jì)算結(jié)果為TRUE或FALSE的任意值或表達(dá)式。Value_if_truelogical_test為TRUE時(shí)返回的值。Value_if_falselogical_test為FALSE時(shí)返回的值答案：'=IF(C9>370,"統(tǒng)計(jì)正確","統(tǒng)計(jì)錯(cuò)誤")圖表——圖表的制作過程理解數(shù)據(jù)分析數(shù)據(jù)提煉信息明確主題提煉信息選擇圖表制作圖表數(shù)據(jù)圖表確定比較關(guān)系確定圖表類型確定圖表處理方式準(zhǔn)備做圖數(shù)據(jù)制作美化圖表檢查確認(rèn)圖表原始數(shù)據(jù)原始文件圖表——如何同時(shí)顯示分類軸，兩種圖表形式通常在Excel圖表中，僅僅在圖表的下方顯示一個(gè)分類（X）軸，有時(shí)需要在圖表的上方再顯示一個(gè)分類（X）軸（次分類軸），可以更加方便地查看數(shù)據(jù)的變化情況。下圖為三大管家收入及占比情況表，圖中分別用了兩個(gè)分類軸，實(shí)現(xiàn)步驟如下：圖表——同時(shí)顯示柱狀圖和折線圖

在excel表中輸入需要插入圖表的區(qū)域范圍選中該工作表點(diǎn)擊插入——二維柱形圖（不能選擇三維圖），會(huì)彈出以下圖表：

注意：如選擇的數(shù)據(jù)范圍需重新選擇，可右擊鼠標(biāo)，【選擇數(shù)據(jù)】，重新選擇數(shù)據(jù)范圍或者把多余的系列刪除，點(diǎn)擊保存即可?？赏ㄟ^圖表工具，對(duì)圖表進(jìn)行美化，如改變標(biāo)題、柱狀圖的顏色、圖例的擺放位置、數(shù)據(jù)標(biāo)簽的顯示位置等。圖表——同時(shí)顯示柱狀圖和折線圖上述操作已經(jīng)選擇數(shù)據(jù)并做好了柱狀圖，要三大管家占比制作成折線圖，操作如下：將excel表中柱形圖適當(dāng)放大，可看見顯示為占比的柱子（藍(lán)