Excel實操應用培訓演講人:目錄

IIIIIIIV基本操作——原來可以如此簡單表格之美——表格也是門藝術(shù)函數(shù)圖表——Excelde精髓之處實戰(zhàn)演練——玩轉(zhuǎn)excel不是夢基本操作常用快捷鍵排序與篩選分列數(shù)據(jù)有效性條件格式刪除重復項不定行數(shù)據(jù)填充基本操作——原來可以如此簡單

基本操作表格之美函數(shù)圖表實戰(zhàn)演練基本操作——常用快捷鍵Ctrl+C——復制選定區(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——當前程序與上個程序間快速切換Tab+Ctrl——同類型程序間快速切換Alt+Enter——單元格內(nèi)換行Ctrl+End——移到文本框de末尾Ctrl+Home——移到文本框de開頭Shift+Ctrl+End——選定自光標所在單元格以下所有非空單元格Shift+Ctrl+Home——選定自光標所在單元格以有所以非空單元格Ctrl+A——選定所有非空單元格選定對象+Shift+箭頭狀鼠標拖動——任意行間拖動選定對象+Ctrl+箭頭狀鼠標拖動——任意行間復制粘貼基本操作——排序與篩選

幾類典型排序方法排序法——自定義序列法典型案例:按職務高低進行排序排序法——對排序選項進行設定典型案例:按單元格填充顏色排序（若存在多種顏色可通過“復制條件”選項來進行次關(guān)鍵字排序）排序注意事項:對帶公式de單元格進行排序注意事項篩選de基本操作篩選de類型——按文本特征篩選、按數(shù)字特征篩選、按日期特征篩選、按字體顏色或單元格顏色篩選篩選de基本操作——選定區(qū)域、篩選、搜索關(guān)鍵字自定義自動篩選方式篩選de注意事項:雙行標題de工作表篩選若只對一列單元格數(shù)據(jù)進行篩選,則需選中標題所在單元格及其下方至少一個單元格,否則默認為整個工作表.操作與技巧進階——數(shù)據(jù)有效性數(shù)據(jù)有效性de作用

①

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

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

功能介紹:

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

一、透視表de建立（單擊excel二零一零版插入按鈕）二、選項及設計功能介紹三、存在合并單元格透視表會提示字段名無效數(shù)據(jù)透視表解決方法:取消合并單元格,填充相同內(nèi)容,然后創(chuàng)建數(shù)據(jù)透視表.附加學習點:相隔不規(guī)則行數(shù)填充內(nèi)容.數(shù)據(jù)透視表步驟一如圖所示,選中合并單元格區(qū)域A二:A二一→單擊“開始”選項卡→“對齊方式”組→“合并后居中”,取消合并單元格.步驟二

如圖所示,選中取消合并后de單元格區(qū)域A二:A二一→按F五,在彈出de“定位條件”對話框中選擇“空值”→“確定”,即可定位并選中單元格區(qū)域A二:A一八中所有空值de單元格步驟三

如圖所示,在選中de空白單元格中輸入“=A二”,然后按【Ctrl+Enter】組合鍵,即可實現(xiàn)空白單元格填充相同項.步驟四

按之前講訴方法創(chuàng)建數(shù)據(jù)透視表.數(shù)據(jù)透視表—如何兩個求和項再求和?在彈出計算字段選項框中,把名稱改為“合計”,在公式框中,從下面字段框中雙擊選擇“金額”,輸入“+”,在雙擊選中“滯納金”,點擊確定即可.函數(shù)圖表VLOOKUP函數(shù)快速查找指定數(shù)值從懂得VLOOKUP開始功能介紹:

在表格數(shù)值或數(shù)組de首列查找指定de數(shù)值,并由此返回表格或者數(shù)組中該數(shù)值所在行中指定列處de數(shù)值.VLOOKUP函數(shù)注意事項:一、如果“查找值”小于“區(qū)域”第一列中de最小值,函數(shù)VLOOKUP返回錯誤值#N/A.二、如果函數(shù)VLOOKUP找不到“查找值”,且“邏輯值”為FALSE,函數(shù)VLOOKUP返回錯誤值#N/A.三、VLOOKUPde查找值如果在索引區(qū)域內(nèi)有多個對應值時,只能返回首條記錄de對應索引數(shù)據(jù).四、VLOOKUPde查找值相對應de索引區(qū)域de對應列,必須位于索引區(qū)域de首列.SUMIF函數(shù)案例:請根據(jù)《房產(chǎn)資料》工作表,用Sumif函數(shù)統(tǒng)計出“項目名稱”為“高爾夫三期”de所有房間建筑面積之和.SUMIF函數(shù)是Excel常用函數(shù).使用SUMIF函數(shù)可以對報表范圍中符合指定條件de值求和.Excel中sumif函數(shù)de用法是根據(jù)指定條件對若干單元格、區(qū)域或引用求和.第一個參數(shù):Range為條件區(qū)域,用于條件判斷de單元格區(qū)域.第二個參數(shù):Criteria是求和條件,由數(shù)字、邏輯表達式等組成de判定條件.第三個參數(shù):Sum_range為實際求和區(qū)域,需要求和de單元格、區(qū)域或引用.答案:'=SUMIF(房產(chǎn)資料!A:A,"高爾夫三期",房產(chǎn)資料!G:G)COUNTIF函數(shù)案例:請根據(jù)《房產(chǎn)資料》工作表,用Countif函數(shù)統(tǒng)計出“項目名稱”為“高爾夫二期”de房間個數(shù)(房產(chǎn)戶數(shù)).Countif函數(shù)是MicrosoftExcel中對指定區(qū)域中符合指定條件de單元格計數(shù)de一個函數(shù)參數(shù):range要計算其中非空單元格數(shù)目de區(qū)域參數(shù):criteria以數(shù)字、表達式或文本形式定義de條件答案:'=COUNTIF(房產(chǎn)資料!A:A,"高爾夫二期")IF函數(shù)案例:請用If函數(shù)判別,若上題“項目名稱”為“高爾夫二期”de房間個數(shù)超出三七零個,則提示“統(tǒng)計正確”,否則提示“統(tǒng)計錯誤”.IF函數(shù)一般是指Excel中deIF函數(shù),根據(jù)指定de條件來判斷其“真”（TRUE）、“假”（FALSE）,根據(jù)邏輯計算de真假值,從而返回相應de內(nèi)容.IF(logical_test,value_if_true,value_if_false)Logical_test表示計算結(jié)果為TRUE或FALSEde任意值或表達式.Value_if_truelogical_test為TRUE時返回de值.Value_if_falselogical_test為FALSE時返回de值答案:'=IF(C九>三七零,"統(tǒng)計正確","統(tǒng)計錯誤")圖表——同時顯示柱狀圖和折線圖在excel表中輸入需要插入圖表de區(qū)域范圍選中該工作表點擊插入——二維柱形圖（不能選擇三維圖）,會彈出以下圖表:注意:如選擇de數(shù)據(jù)范圍需重新選擇,可右擊鼠標,【選擇數(shù)據(jù)】,重新選擇數(shù)據(jù)范圍或者把多余de系列刪除,點擊保存即可.可通過圖表工具,對圖表進行美化,如改變標題、柱狀圖de顏色、圖例de擺放位置、數(shù)據(jù)標簽de顯示位置等.圖表——同時顯示柱狀圖和折線圖上述操作已經(jīng)選擇數(shù)據(jù)并做好了柱狀圖,要三大管家占比制作成折線圖,操作如下:將excel表中柱形圖適當放大,可看見顯示為占比de柱子（藍色）,右鍵選擇增長率de柱子——選擇“設置數(shù)據(jù)系列格式”——“系列繪制在”選擇“次坐標軸”,圖標如下所示:圖表——同時顯示柱狀圖和折線圖點擊年增長率柱子選擇“更改圖標類型”——更改為折線圖——確定.圖表如下所示:圖表——同時顯示柱狀圖和折線圖在圖標中,選中柱形或者折線項目——右鍵點擊“添加數(shù)據(jù)標簽”或者選擇菜單欄【圖表工具】--【布局】--數(shù)據(jù)標簽,即可在圖表中顯示該項具體數(shù)據(jù),如下圖所示:實戰(zhàn)演練感謝觀看Excel實用培訓教程培訓:一提高篇目錄一、Excel熟練操作de基礎二、Excel工作組de妙用三、Excel表格格式de美觀四、Excel樣表/輔助表de制作五、Excel常用復雜函數(shù)詳解六、Excel中對負增長de討論與解決七、Excel圖表分析八、Excelde加密與表格de鎖定九、Excel簡單de宏操作十、Excel表格打印一、Excel熟練操作de基礎一、Excel熟練操作de基礎復制:Ctrl+C粘貼:Ctrl+V剪貼板de使用:在使用電子表格進行工作時,用剪貼板可以把工作效率提高幾倍；熟練使用剪貼板,還可以實現(xiàn)許多功能.打開剪貼板:編輯

Office剪貼板；或者在表格中雙擊復制健（即雙擊“Ctrl+C”）復制進入剪貼板de數(shù)據(jù)按先后順序是從下到上,避免搞錯.（具體看實例）一、Excel熟練操作de基礎剪貼板只要是實現(xiàn)數(shù)據(jù)和字符串de復制粘貼,會把公式過濾掉.同時它有一個很大很實用de功能是否顯示不粘貼,非常方便選擇性數(shù)據(jù)de復制粘貼.具體看實例一、Excel熟練操作de基礎拖動＆復制:一、數(shù)字,日期,星期de復制二、如果不希望復制de數(shù)字,日期或星期自動累加,實現(xiàn)復制de數(shù)字,日期或星期一樣,如何實現(xiàn)?三、公式de拖動,公式de復制,公式（復制）拖動de鎖定,$行列號鎖定符de使用.一、Excel熟練操作de基礎選擇性粘貼de功用:可以實現(xiàn)數(shù)值,公式,格式,轉(zhuǎn)置,各種運算等功能.（具體請看操作示范）二、Excel工作組de妙用二、Excel工作組de妙用大多數(shù)大型de工作表格,都是由很多小表組成de,這時為了提高工作效率,節(jié)約時間,使用工作組來工作是非常必要de.一、工作組de選擇,工作表de新建,重命名,移動,復制,刪除等操作de熟悉.二、工作組復制粘貼de熟悉.三、工作組之間de超鏈接熟悉.三、Excel表格格式de美觀三、Excel表格格式de美觀設置單元格格式de使用:三、Excel表格格式de美觀一、單元格格式中de數(shù)字格式.二、單元格格式中de對齊格式.三、單元格格式中de字體格式.四、單元格格式中de邊框格式.五、單元格格式中de背景格式.六、單元格格式中de保護功能.三、Excel表格格式de美觀格式刷de使用:一、雙擊格式刷.二、工作組格式刷de功用.三、區(qū)域格式刷新.四、Excel樣表/輔助表de制作四、Excel樣表/輔助表de制作根據(jù)工作需要de不同,我們在工作中需要制作各種Excel表格樣表,一可以給自己工作帶來方便,二是給別人做參考學習,三是便于規(guī)范各個分公司或部門de工作.樣表主要是從實際需要出發(fā),多考慮實用性,美觀性.如果是給別人參考de,多從對方de角度出發(fā),方便對方快速接受.如果是統(tǒng)一規(guī)范工作de,應從嚴謹規(guī)范,大體上考慮.四、Excel樣表/輔助表de制作在工作中,有時候數(shù)據(jù)量較大,整理起來比較麻煩,因而常常需要使用輔助表格.輔助表格有兩種,一種是獨立de輔助表,此表只是在完成一項工作de一個中間過程,就像一個工具一樣,只要通過此表得到我們需要de結(jié)果即可.另外一種輔助表是和工作表在一起de,一般連接在工作表后面,是一種數(shù)據(jù)de來源,也叫數(shù)據(jù)源表.此類表在我們工作中是很常用de.具體請看實例!五、Excel常用復雜函數(shù)詳解五、Excel常用復雜函數(shù)詳解一、SUM（）函數(shù)de使用:SUM（）返回某一單元格區(qū)域中所有數(shù)字之和.語法SUM(number一,number二,...)Number一,number二,...

為一到三零個需要求和de參數(shù).注意:其最大限度只能累加三零個數(shù)據(jù)段de求和,超出三零就產(chǎn)生溢出.此函數(shù)de符號∑在工具欄里可以直接找到,是電子表格中最常用de函數(shù)之一!具體看示范操作.五、Excel常用復雜函數(shù)詳解AVERAGE()函數(shù)de使用:返回參數(shù)de平均值（算術(shù)平均值）.語法AVERAGE(number一,number二,...)Number一,number二,...

為需要計算平均值de一到三零個參數(shù).注意:其最大限度只能累加三零個數(shù)據(jù)段de求和,超出三零就產(chǎn)生溢出.說明參數(shù)可以是數(shù)字,或者是包含數(shù)字de名稱、數(shù)組或引用.如果數(shù)組或引用參數(shù)包含文本、邏輯值或空白單元格,則這些值將被忽略；但包含零值de單元格將計算在內(nèi).此函數(shù)在工具欄∑里也可以直接找到,是電子表格中最常用de函數(shù)之一!具體看示范操作.五、Excel常用復雜函數(shù)詳解INT()函數(shù)de使用:將數(shù)字向下舍入到最接近de整數(shù).語法INT(number)Number

需要進行向下舍入取整de實數(shù).例如int（六.九九）所得結(jié)果是六,int（六.一二）所得結(jié)果也是六.具體看示范操作.五、Excel常用復雜函數(shù)詳解COUNTIF()函數(shù)de詳解:計算區(qū)域中滿足給定條件de單元格de個數(shù).語法COUNTIF(range,criteria)Range

為需要計算其中滿足條件de單元格數(shù)目de單元格區(qū)域.Criteria

為確定哪些單元格將被計算在內(nèi)de條件,其形式可以為數(shù)字、表達式或文本.例如,條件可以表示為六零、“六零”、“>＝六零”或“數(shù)學".說明MicrosoftExcel提供其他函數(shù),可用來基于條件分析數(shù)據(jù).若要使公式返回兩個基于條件de值之一,例如:算出某門課程有多少學生及格,有多少學生九零分以上等等,都可以使用該函數(shù)實現(xiàn).五、Excel常用復雜函數(shù)詳解SUBTOTAL()函數(shù)詳解:返回數(shù)據(jù)清單或數(shù)據(jù)庫中de分類匯總.通常,使用“數(shù)據(jù)”菜單中de“分類匯總”命令可以容易地創(chuàng)建帶有分類匯總de數(shù)據(jù)清單.一旦創(chuàng)建了分類匯總,就可以通過編輯SUBTOTAL函數(shù)對該數(shù)據(jù)清單進行修改.SUBTOTAL(九,C二:C二四)該函數(shù)在表格中使用最多,也是做很多表格最有用de一個函數(shù),它de意思是匯總再求值,如果其中有此函數(shù)出現(xiàn),它比SUM（）函數(shù)de好處就是不會重復相加,（如一個店有食品,生鮮,非食品,家電,百貨這幾個區(qū),我用SUBTOTAL(九,C二:C二四)在各個區(qū)累加匯總了,然后我在最后再用該函數(shù)匯總整個店de銷售總額,不會出現(xiàn)重復計算,看示例!五、Excel常用復雜函數(shù)詳解SUBTOTAL()函數(shù)中de參數(shù)有一-九；其中九代表相加,一是求平均值,四是求最大值,五是求最小值,我常常使用de就這幾個；其中后面兩個數(shù)字是所要匯總de數(shù)據(jù)de區(qū)間,如C二:C二四就是要匯總C列中第二格到第二四格de數(shù)值.此函數(shù)主要使用在分類匯總de表格里.所以在修改有分類匯總de工作表de時候,必須要弄懂該函數(shù)de功用,才不會出錯.五、Excel常用復雜函數(shù)詳解SUMIF()函數(shù)de詳解:根據(jù)指定條件對若干單元格求和；此公式是判斷求和,非常實用語法SUMIF(range,criteria,sum_range)Range

為用于條件判斷de單元格區(qū)域.Criteria

為確定哪些單元格將被相加求和de條件,其形式可以為數(shù)字、表達式或文本.例如,條件可以表示為六零、“六零”、“>六零”或“數(shù)學".Sum_range

是需要求和de實際單元格.五、Excel常用復雜函數(shù)詳解SUMIF()函數(shù)de說明只有在區(qū)域中相應de單元格符合條件de情況下,sum_range中de單元格才求和.如果忽略了sum_range,則對區(qū)域中de單元格求和.例如:SUMIF(E二:E二四,”毛利”,C二:C二四),E二:E二四是指判斷區(qū)間,”毛利”是判斷條件,C二:C二四是求和區(qū)間（意思就是在E二:E二四有“毛利”這個字段對應deC二:C二四de數(shù)字相加）判斷條件可以是字符串,也可以是數(shù)字,但是是字符串必須使用英文de雙引號,數(shù)據(jù)就不需要.具體看示范操作.五、Excel常用復雜函數(shù)詳解IF()函數(shù)de詳解:執(zhí)行真假值判斷,根據(jù)邏輯計算de真假值,返回不同結(jié)果.可以使用函數(shù)IF對數(shù)值和公式進行條件檢測.語法IF(logical_test,value_if_true,value_if_false)IF(條件,真,假)；如果條件判斷為真,則執(zhí)行第一個命令,反之執(zhí)行第二個命令.該函數(shù)重要一個使用功能是嵌套,最多可以嵌套七層.說明函數(shù)IF可以嵌套七層,用value_if_false及value_if_true參數(shù)可以構(gòu)造復雜de檢測條件.在計算參數(shù)value_if_true和value_if_false后,函數(shù)IF返回相應語句執(zhí)行后de返回值.五、Excel常用復雜函數(shù)詳解IF()嵌套de實用性很廣,可以用于作各種判斷,例如:已知張三同學某課de成績,求出他de成績等級.可以用此函數(shù)實現(xiàn),IF(B二<六零,“不及格”,IF(B二<八零,“及格”,IF(B二<九零,“良好”,IF(B二>一零零,“成績有誤”,“優(yōu)秀”)))).用IF()函數(shù)嵌套其他函數(shù):例如嵌套sum,sumif,AVERAGE,countif等等常用de函數(shù).例如根據(jù)條件（求和或求平均值）進行數(shù)據(jù)運算,并反饋運算結(jié)果.可以這樣實現(xiàn):IF(D五="合計",SUM(B二:B一二),IF(D五="平均",AVERAGE(B二:B一二),"條件不符"))具體操作,看其他示例!六、Excel中對負增長de

討論與解決六、Excel中對負增長de討論與解決通過以上對IF函數(shù)de學習,我們可以通過它來實現(xiàn)我們在日常遇到de一些難題,比如負增長de問題.（負增長問題和一些完成率問題de事例）在我們平時工作中,增長de公式一般是:（當前數(shù)據(jù)－對比數(shù)據(jù)）/對比數(shù)據(jù)*一零零％但是該公式如果用在計算利潤de時候,就會出現(xiàn)問題,因為我們常常會遇到負利潤de情況（即虧損情況）；這里就不能僅僅把公式往表格里面一套就了事,而是需要通過if函數(shù)進行一些判斷運算；如附件例子所示.六、Excel中對負增長de討論與解決對利潤de完成率情況也有類似de問題,比如上個月de利潤計劃是-一零零萬（即允許虧損一零零萬）,但是實際完成是-一二零萬,該完成率是多少?而我們平時使用de完成率公式很簡單,即:實際/計劃*一零零％；但是把此公式對上面提到de情況進行運算,結(jié)果是完成了一二零％,可能嗎?如果上面de實際值是零萬呢?完成率又是多少?具體看實例,目前我們解決此問題de方法；七、Excel圖表分析七、Excel圖表分析圖表de概念:指將工作中de數(shù)據(jù)用圖形表示出來,圖表便于我們閱讀和分析數(shù)據(jù).建立圖表:在菜單欄里:插入－>圖表,即彈出如圖所示de界面:七、Excel圖表分析Excel圖表圖形有:柱形圖,條形圖,折線圖,餅圖,XY散點圖,面積圖,圓環(huán)圖,雷達圖,曲面圖,氣泡圖,股價圖,圓柱圖,圓錐圖,棱錐圖；各種圖形都有自己de特點和不同de功用,我們只能根據(jù)自己de需要選擇可用de圖形,現(xiàn)在我們常用de圖形有柱行圖,折線圖,餅圖,圓環(huán)圖,雷達圖；另外還有根據(jù)一些特別需要自己創(chuàng)建de圖形,不過這些不是