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

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

基本操作表格之美函數圖表實戰演練基本操作——常用快捷鍵Ctrl+C——復制選定區域Ctrl+v——粘貼選定區域Ctrl+x——裁剪選定區域選定區域+Ctrl+d——縱向填充第一列單元格內容選定區域+Ctrl+r——橫向填充第一行單元格內容Windows鍵+d——顯示桌面Ctrl+b——加粗單元格字體Ctrl+f/h——查找與替換Tab+Alt——當前程序與上個程序間快速切換Tab+Ctrl——同類型程序間快速切換Alt+Enter——單元格內換行Ctrl+End——移到文本框de末尾Ctrl+Home——移到文本框de開頭Shift+Ctrl+End——選定自光標所在單元格以下所有非空單元格Shift+Ctrl+Home——選定自光標所在單元格以有所以非空單元格Ctrl+A——選定所有非空單元格選定對象+Shift+箭頭狀鼠標拖動——任意行間拖動選定對象+Ctrl+箭頭狀鼠標拖動——任意行間復制粘貼基本操作——排序與篩選

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

①

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

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

功能介紹:

數據透視表能夠將篩選、排序和分類匯總等操作依次完成,并生成匯總表格.Example:資金日報表

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

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

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

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

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

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

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

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

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

為需要計算其中滿足條件de單元格數目de單元格區域.Criteria

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

為用于條件判斷de單元格區域.Criteria

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

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

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