excel使用技巧講座

辦公office報(bào)表軟件excel使用技巧講座制作時(shí)間：2013年3月7日

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

下面講解我會(huì)以問題、概念、圖例與實(shí)操演示等四個(gè)方式為大家講解每項(xiàng)功能的使用方法，以便大家能快速掌握。

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

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

初級(jí)用戶

中級(jí)用戶

高級(jí)用戶

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

10、篩選功能

Excel提供了二種不同的篩選方式:自動(dòng)篩選和高級(jí)篩選

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

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

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

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

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

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

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

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

若要?jiǎng)?chuàng)建數(shù)據(jù)透視表，請(qǐng)運(yùn)行“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А薄Ｔ谙驅(qū)е?，從工作表列表或外部?shù)據(jù)庫(kù)選擇源數(shù)據(jù)。向?qū)缓鬄槟峁﹫?bào)表的工作表區(qū)域和可用字段的列表。當(dāng)您將字段從列表窗口拖到分級(jí)顯示區(qū)域時(shí)，MicrosoftExcel自動(dòng)匯總并計(jì)算報(bào)表。數(shù)據(jù)透視表是excel中一個(gè)我們經(jīng)常會(huì)使用的功能，他的功能是非常強(qiáng)大的，我在這里為大家簡(jiǎn)單演示下其數(shù)據(jù)分析方面的使用辦法。

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

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

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

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

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

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

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

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

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

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

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

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

Vlookup函數(shù)主要功能：在數(shù)據(jù)表的首列查找指定的數(shù)值，并由此返回?cái)?shù)據(jù)表當(dāng)前行中指定列處的數(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ū)域中待返回的匹配值的列序號(hào)（當(dāng)Col_index_num為2時(shí),返回table_array第2列中的數(shù)值，為3時(shí)，返回第3列的值……）；Range_lookup為一邏輯值，如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果為FALSE，則返回精確匹配值，如果找不到，則返回錯(cuò)誤值#N/A。

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

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

如下所示：第三講

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

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

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

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

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

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

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

這里我為大家制作一個(gè)相對(duì)比較復(fù)雜的餅圖來認(rèn)識(shí)學(xué)習(xí)餅圖的制作：復(fù)合餅圖

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

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

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

開發(fā)控件“滾動(dòng)條”與“組合框”至于它倆的作用我會(huì)在下面讓大家看到。在制作貸款還息動(dòng)態(tài)圖標(biāo)前，我們需要知道三個(gè)數(shù)據(jù)，即貸款金額，貸款年限，貸款年利息，這里先給出了三個(gè)數(shù)據(jù)，在后面制作圖表的時(shí)候我們就會(huì)讓這三個(gè)數(shù)據(jù)成為一組可以自動(dòng)調(diào)整的值，以供我們對(duì)比不同金額，不同年限下的費(fèi)用單擊觀看完成效果演示單擊觀看制作過程演示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ù)的錄入第二步：圖表的繪制圖表的繪制這步我們需要制作兩個(gè)圖表，一個(gè)是利息與本金的柱狀對(duì)比圖。另一個(gè)是利息總額與本金綜合所占百分比餅型圖。這兩個(gè)圖都比較簡(jiǎn)單，我們只需要選擇年份、利息、本金三縱列所有的數(shù)據(jù)，點(diǎn)擊插入柱狀圖，便會(huì)生成我們需要的單一年限與