excel使用技巧講座

辦公office報表軟件excel使用技巧講座制作時間：2013年3月7日

制作人：劉凱Excel簡介Excel在工作中起的作用，是不言而喻的。靈活的使用Excel可以極大的提高我們的工作效率，以下是我工作之余總結(jié)的Excel常用功能，希望對大家工作有所益處。

下面講解我會以問題、概念、圖例與實操演示等四個方式為大家講解每項功能的使用方法，以便大家能快速掌握。

但是在講解之前我先為大家介紹下Excel它到底具有哪些功能？我們對它的功能掌握到了哪一步？

Excel用戶水平的5個層次初步的接觸基本掌握常用功能熟練使用常用功能+數(shù)據(jù)透視表+函數(shù)宏+數(shù)組公式+VBA簡單編程Excel技能+專業(yè)知識+行業(yè)經(jīng)驗新手

初級用戶

中級用戶

高級用戶

專家數(shù)據(jù)操作錄入與導出常規(guī)編輯格式化高級編輯自定義環(huán)境圖表與圖形標準圖形組合圖形圖表美化高級圖表交互式圖表圖示公式與函數(shù)公式基礎函數(shù)應用函數(shù)嵌套數(shù)組公式自定義函數(shù)數(shù)據(jù)分析排序篩選列表數(shù)據(jù)透視表假設分析高級分析宏與VBA錄制運行宏VBA基礎Excel對象窗體與控件類模塊Excel功能剖析Excel功能講解Excel常用操作Excel基本函數(shù)Excel中插入圖表Excel常用技巧函數(shù)高級應用創(chuàng)建數(shù)據(jù)透視表數(shù)據(jù)處理與分析復雜實例演示基礎功能進階功能第一講

Excel常用操作1、excel常用功能界面介紹，07版與03版的區(qū)別與相似在哪里？07版界面03版界面5、設置單元格格式

鼠標左鍵選擇所要改變的區(qū)域，右鍵菜單設置單元格格式。

這里可以對數(shù)字、對齊、字體、邊框、圖案等項目進行設置，助我們制作一個精美的報表。如演示所示3、如何設置具體數(shù)值的行高與列寬？

鼠標左鍵選擇所要改變的行（或列），右鍵菜單

行高（列寬）。如圖例所示2、Shift+Ctrl+方向鍵有什么功能呢？

它們實現(xiàn)的功能是：從當前單元格開始，按照選定的方向選取數(shù)據(jù)，遇到第一個空單元格時結(jié)束。如演示所示：4、快速的自動填充序列？

所謂自動填充簡單的理解就是當行或列中的部分數(shù)據(jù)形成有一定的序列時，我們就可以使用自動填充功能來快速填充數(shù)據(jù)。

自動填充功能可以實現(xiàn)多種數(shù)據(jù)的填充，如：序列、單元格格式、年月份、數(shù)據(jù)的重復錄入等。自動擴展與填充是excel中一項比較重要的功能，這里我會以演示的方式為大家講解。如演示所示6、查找與替換功能如何快速調(diào)出？

查找與替換功能是一個常用的功能，這里我只介紹大家其鍵盤快捷鍵:ctrl+F注：此快捷方式在使用前需選定需要查找的數(shù)據(jù)區(qū)。此功能比較簡單。

9、靈活使用狀態(tài)欄

在Excel的左下角，有一個狀態(tài)欄，提供了許多有用的功能與函數(shù)對選定的范圍進行處理，如：記數(shù)，求和，求平均數(shù)，求最大/最小值等。在狀態(tài)欄上，單擊鼠標右鍵就可以調(diào)出這個功能，靈活運用這個功能可以節(jié)省我們很多工作時間。如下所示：7、如何進行批量數(shù)據(jù)清除？

Delete鍵.這是數(shù)據(jù)清除的快捷鍵。當數(shù)據(jù)量相當大的時候，對于誤填，錯填的數(shù)據(jù)，每次一個個的清除很是浪費時間，也不利于工作效率的提升，這里我們就是使用delete鍵，一鍵清除數(shù)據(jù)。這里的清除只包括清除單純數(shù)據(jù)，不包括清除單元格的格式等。

注：我們可以搭配使用ctrl鍵，這樣可以單獨選擇多個不連續(xù)單元格數(shù)據(jù)或圖片清除。8、單元格數(shù)據(jù)的文本與數(shù)字格式的如何互換？

很多時候當我們從系統(tǒng)中導出的數(shù)據(jù)，它一般都會默認為文本格式，這樣很不方便我們對數(shù)據(jù)的處理，這里就需要就數(shù)據(jù)轉(zhuǎn)化為數(shù)字格式。如演示所示

10、篩選功能

Excel提供了二種不同的篩選方式:自動篩選和高級篩選

自動篩選功能：快捷鍵：Alt+D+F+F，只能用于條件簡單的篩選操作,不能實現(xiàn)字段之間“與”“或”關(guān)系的操作，我這里不做介紹，主要說說他的高級篩選的使用辦法。高級篩選功能：快捷鍵：Alt+D+F+A。這里我們要求篩選出所有員工中基本工資大于2，并且績效工資小于7，以及獎勵工資大于等于10的員工，并且隱藏條件不符的員工。

這里就包含有“與”“或”的關(guān)系，使用自動篩選是很難辦到的，所以在這里使用高級篩選就會非常簡單。這里是數(shù)據(jù)區(qū)這里是條件區(qū)11、選擇性粘貼我們經(jīng)常會使用一些函數(shù)，但是用完函數(shù)后，單元格中的數(shù)值會包含公式，此時我們會運用選擇性粘貼去除函數(shù)。選擇性粘貼是excel中一項比較重要的功能，使用它我們可以對數(shù)據(jù)進行多種格式的復制。

以下主要演示粘貼中得運算、行列倒置、粘貼的多種選擇等。如圖所示12、合并單元格數(shù)據(jù)許多情況下我們需要不單單只合并兩個單元格，還要合并單元格中的數(shù)據(jù)，比如合并NCC和BCC，這樣我們會用到“&”（和號）運算符。

如下所示這里我們使用了公式函數(shù)，函數(shù)一講我會為大家詳細講解函數(shù)的使用辦法13、窗口的凍結(jié)與拆分有時候我們在看數(shù)據(jù)時，希望將某一行數(shù)據(jù)的凍結(jié)不動，此時我們會使用凍結(jié)窗口功能，快捷鍵：Alt+W+F。

拆分功能是將數(shù)據(jù)區(qū)拆分成幾塊，方便我們對標題或其他數(shù)據(jù)的對比錄入.如圖所示14、強制換行

有些時候我們需要在一個單元格中輸入較長的文字，此時我們就用到了強制換行功能。選擇Alt+Enter即可進行強制換行，沒必要再去用空格將文本擠到下一行了15、數(shù)據(jù)有效性

有時我們可能需要限制輸入到單元格中得數(shù)據(jù)格式，這就需要數(shù)據(jù)有效性功能。

16、數(shù)據(jù)透視表數(shù)據(jù)透視表是一種交互式的表，可以進行某些計算，如求和與計數(shù)等。所進行的計算與數(shù)據(jù)跟數(shù)據(jù)透視表中的排列有關(guān)。例如，可以水平或者垂直顯示字段值，然后計算每一行或列的合計；也可以將字段值作為行號或列標，在每個行列交匯處計算出各自的數(shù)量，然后計算小計和總計。

若要創(chuàng)建數(shù)據(jù)透視表，請運行“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?。在向?qū)е校瑥墓ぷ鞅砹斜砘蛲獠繑?shù)據(jù)庫選擇源數(shù)據(jù)。向?qū)缓鬄槟峁﹫蟊淼墓ぷ鞅韰^(qū)域和可用字段的列表。當您將字段從列表窗口拖到分級顯示區(qū)域時，MicrosoftExcel自動匯總并計算報表。數(shù)據(jù)透視表是excel中一個我們經(jīng)常會使用的功能，他的功能是非常強大的，我在這里為大家簡單演示下其數(shù)據(jù)分析方面的使用辦法。

如有以下數(shù)據(jù)，現(xiàn)在我們需要統(tǒng)計分析得到這樣幾個數(shù)據(jù)？1、員工姓名相同的重復有幾個人？他們的保險編號是多少？2、姓名相同的人各自里面誰的工資最高，誰的最低？他們的保險編號是多少？3、姓名相同的人合計工資是多少？電話費是多少？所有總計各項費用多少？第二講

Excel常用函數(shù)Excel是辦公室自動化中非常重要的一款軟件，很多企業(yè)都是依靠Excel進行數(shù)據(jù)管理。它不僅僅能夠方便的處理表格和進行圖形分析.其更強大的功能體現(xiàn)在對數(shù)據(jù)的自動處理和計算，然而很多對Excel強大數(shù)據(jù)處理功能不了解的人卻難以進一步深入。而對Excel函數(shù)應用的不了解正是阻擋普通用戶完全掌握Excel的攔路虎，希望我的講解能夠?qū)Υ蠹覍xcel函數(shù)學習有所幫助。Excel中所提的函數(shù)其實是一些預定義的公式，它們使用一些稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進行計算。Excel函數(shù)一共有11類，分別是數(shù)據(jù)庫函數(shù)、日期與時間函數(shù)、工程函數(shù)、財務函數(shù)、信息函數(shù)、邏輯函數(shù)、查詢和引用函數(shù)、數(shù)學和三角函數(shù)、統(tǒng)計函數(shù)、文本函數(shù)以及用戶自定義函數(shù)。共計約500多個函數(shù)。

這里我會介紹8個我們工作中常用函數(shù)功能及用法，更為復雜的函數(shù)操作我在這里不做講解。Sum函數(shù)（求和函數(shù)）主要功能：計算所有參數(shù)數(shù)值的和。使用格式：SUM（Number1,Number2……）參數(shù)說明：Number1、Number2……代表需要計算的值，可以是具體的數(shù)值、引用的單元格（區(qū)域）、邏輯值等。應用舉例：如右圖所示，在A10單元格中輸入公式：=SUM(A1:A9)，確認后即可求出以上單元格的和。Sumif函數(shù)主要功能：計算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值和。使用格式：SUMIF（Range,Criteria,Sum_Range）參數(shù)說明：Range代表條件判斷的單元格區(qū)域；Criteria為指定條件表達式；Sum_Range代表需要計算的數(shù)值所在的單元格區(qū)域。應用舉例：如右圖所示，分別求出各項費用的合計1、數(shù)學函數(shù)條件區(qū)Max函數(shù)（Min函數(shù)）主要功能：求出一組數(shù)中的最大值（或最小值）。使用格式：MAX(number1,number2……)參數(shù)說明：number1,number2……代表需要求最大值的數(shù)值或引用單元格（區(qū)域），參數(shù)不超過30個。簡單的加減乘除運算的公式使用

這里使用的符號為:+-*/直接的公式是在這里輸入這里我是以文本格式輸入，故無法計算2、什么叫文本函數(shù)？Left函數(shù)

主要功能：從一個文本字符串的第一個字符開始，截取指定數(shù)目的字符。（即從左開始）使用格式：Lift(text,num_chars)參數(shù)說明：text代表要截字符的字符串；num_chars代表給定的截取數(shù)目。Right函數(shù)

主要功能：從一個文本字符串的最后一個字符開始，截取指定數(shù)目的字符。（即從右開始）使用格式：Right(text,num_chars)參數(shù)說明：text代表要截字符的字符串；num_chars代表給定的截取數(shù)目。應用舉例：使用方法同Left函數(shù)，但是只是提取的右邊的字符。

應用具體：例如我要對比下列幾家服務站報單量最大的服務站進行提取，但是前面帶有服務站代碼，無法進行直接對比排序，這里就需要截取處理單后三位的值，以便進行比對。如右下圖Mid函數(shù)

主要功能：從一個文本字符串的指定位置開始，截取指定數(shù)目的字符。（即從中間任意位置開始）使用格式：Mid(text,start_num,num_chars)參數(shù)說明：text代表一個文本字符串；start_num表示指定的起始位置；num_chars表示

文本函數(shù)是指通過文本函數(shù)，可以在公式中處理文字串。3、邏輯函數(shù)If函數(shù)（如果）

主要功能：根據(jù)對指定條件的邏輯判斷的真假結(jié)果，返回相對應的內(nèi)容。使用格式：=IF(Logical,Value_if_true,Value_if_false)參數(shù)說明：Logical代表邏輯判斷表達式；Value_if_true表示當判斷條件為邏輯“真（TRUE）”時的顯示內(nèi)容，如果忽略返回“TRUE”；Value_if_false表示當判斷條件為邏輯“假（FALSE）”時的顯示內(nèi)容，如果忽略返回“FALSE”。

舉例說明：如右下圖，當成績大于60分的為“及格”，大于80分為“良”，大于90分為“優(yōu)”=if(E120>60,(),“差”)第一級判斷if(E120>80,(),“及格”)第二級判斷if(E120>90,“優(yōu)”，“良”）第三級判斷公式拆分講解注：此處使用了函數(shù)的嵌套功能，即復合函數(shù)，復合函數(shù)屬于函數(shù)使用的高級用法，因為實際工作數(shù)據(jù)的復雜性，需要我們使用多個函數(shù)來共同完成某項數(shù)據(jù)分析。

這里我想說明下”if”函數(shù)搭配其它函數(shù)共同使用，是excel函數(shù)應用最為重要的一個函數(shù)。此處要顯示的是文本，所以需要加雙引號4、查找函數(shù)

Vlookup函數(shù)主要功能：在數(shù)據(jù)表的首列查找指定的數(shù)值，并由此返回數(shù)據(jù)表當前行中指定列處的數(shù)值。使用格式：)Vlookup(lookup_value,table_array,col_index_num,range_lookup）參數(shù)說明：Lookup_value代表需要查找的數(shù)值；Table_array代表需要在其中查找數(shù)據(jù)的單元格區(qū)域；Col_index_num為在table_array區(qū)域中待返回的匹配值的列序號（當Col_index_num為2時,返回table_array第2列中的數(shù)值，為3時，返回第3列的值……）；Range_lookup為一邏輯值，如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果為FALSE，則返回精確匹配值，如果找不到，則返回錯誤值#N/A。

應用舉例：vLookup函數(shù)是非常重要的，可以說無時無刻不再用他。

比如通過單位（左）來索引表二的二月數(shù)據(jù)，在二月費用單元格中輸入“=VLOOKUP函數(shù)”，表示在單位（右）列中查找需要的二月費用，返回二月列中匹配值，下拉單元格直接填充單元格即可。

如下所示：第三講

Excel中插入圖表我們生活的這個世界是豐富多彩的，幾乎所有的知識都來自于視覺。也許無法記住一連串的數(shù)字，以及它們之間的關(guān)系和趨勢。但是可以很輕松地記住一幅圖畫或者一個曲線。因此由于使用圖表，會使得用Excel編制的工作表更易于理解和交流。圖表可以用來表現(xiàn)數(shù)據(jù)間的某種相對關(guān)系，在常規(guī)狀態(tài)下我們一般運用柱形圖比較數(shù)據(jù)間的多少關(guān)系;用折線圖反映數(shù)據(jù)間的趨勢關(guān)系;用餅圖表現(xiàn)數(shù)據(jù)間的比例分配關(guān)系。運用Excel的圖表制作可以生成多種類型的圖表。

下面以柱形圖、折線圖、餅圖三種類型為例，分別介紹其制作方法，最后我會制造一個比較復雜的圖表來作為圖表講座的結(jié)束。1、柱形圖

柱形圖比較數(shù)據(jù)間的多少關(guān)系下面我們以企業(yè)的收入與支出對比表來演示柱形圖的建立。統(tǒng)計得到的數(shù)據(jù)表可以將其建立為柱形圖表，從而更加直觀的顯示出收入與支出的對比情況，以及全年中收入與支出的發(fā)展趨勢對比。右圖所示圖表需要四個步驟：1）數(shù)據(jù)區(qū)選擇生成默認柱狀表格2）圖表中添加標題欄3）收入與支出柱狀圖上下對齊4）月份顯示到軸坐標下方

下面我會以演示的方式為大家呈現(xiàn)此圖表的制作。從圖中可以看出全年中期為盈利淡季，年初與年末盈利較大，其中5、6月出現(xiàn)反彈收入與支出基本成比例生成完整柱型圖表流程圖首先要選擇數(shù)據(jù)區(qū)<1><2><3><4><5>2、折線圖用折線圖反映數(shù)據(jù)間的趨勢關(guān)系這里我們同樣以企業(yè)收入與支出作為對比關(guān)系

制作流程與柱狀圖相似，只是在第二步選擇折線圖圖表，程序會自動為我們生成好折線圖圖表。

這里我們要注意的是：默認的折線圖，“月份”會在x坐標中軸線附近，這里就需要使用柱形圖流程中4-5步奏設置月份到下方去3、餅狀圖用餅圖表現(xiàn)數(shù)據(jù)間的比例分配關(guān)系

餅圖有多種樣式：突出型餅圖、復合型餅圖、雙子餅圖、母子餅圖、動態(tài)母子餅圖

這里我為大家制作一個相對比較復雜的餅圖來認識學習餅圖的制作：復合餅圖

復合餅圖制作中運用的辦法，基本可以代表基礎功能中各種餅圖，所以熟練掌握復合餅圖，我們在餅圖制作中就可以游刃有余。復合餅狀圖制作流程原始數(shù)據(jù)注：在這里生成的餅形圖界面是非常簡單的，需要我們自己按照自己的喜好去美化它，07版Excel提供了多種美化方式，我們可以自己去學習

左圖是已經(jīng)美化好的效果圖貸款還息動態(tài)圖表最后在這里我制作一個那些已經(jīng)買房或準備買二房的兄弟們準備的一個小工具，就是在貸款費用與年限各不同的情況下需要還的利息與本金的對比分析，希望各位為了國家經(jīng)濟發(fā)展趕緊買房吧。這里我制作這個圖表的目的是準備將前面幾講中的函數(shù)、動態(tài)圖表，還有目前不屬于基礎功能的財務函數(shù)、窗口開發(fā)工具—控件的使用融匯到一起，讓大家可以對excel的高級功能有一個初步的認識，為我們下面的高級功能算是做一個開場吧。再將之前我先介紹下目前我們還沒有接觸的兩個功能。

財務函數(shù)PMT——PMT顧名思義就是基于固定利率及等額分期付款方式，返回貸款的每期付款額。

開發(fā)控件“滾動條”與“組合框”至于它倆的作用我會在下面讓大家看到。在制作貸款還息動態(tài)圖標前，我們需要知道三個數(shù)據(jù)，即貸款金額，貸款年限，貸款年利息，這里先給出了三個數(shù)據(jù)，在后面制作圖表的時候我們就會讓這三個數(shù)據(jù)成為一組可以自動調(diào)整的值，以供我們對比不同金額，不同年限下的費用單擊觀看完成效果演示單擊觀看制作過程演示C5=—PMT(C4，$C$3，MAX(0，$C$2*10000))C6=SUM(C9:C33)B9=C2*10000C9=$C$4*B9D9=MIN(B9+C9,$C$5-C9B10=B9-D9B11=IF(B10-D10<0,0,B10-D10第一步：數(shù)據(jù)的錄入第二步：圖表的繪制圖表的繪制這步我們需要制作兩個圖表，一個是利息與本金的柱狀對比圖。另一個是利息總額與本金綜合所占百分比餅型圖。這兩個圖都比較簡單，我們只需要選擇年份、利息、本金三縱列所有的數(shù)據(jù)，點擊插入柱狀圖，便會生成我們需要的單一年限與