Excel電子表格

1、第三部分Excel 2010電子表格一、引言Excel同Word一樣是世界著名的Office系列之一。它強大的功能主要體現(xiàn)在能夠制作出各種電子表格，同時可引用公式對表格中的數(shù)據(jù)進行復(fù)雜的運算，并將結(jié)果用各種統(tǒng)計圖表的形式表現(xiàn)出來。因引，Excel被廣泛用于各大管理公司和財務(wù)領(lǐng)域。通過本章節(jié)學(xué)習(xí)需要掌握的知識：1、Excel制表基礎(chǔ)2、工作簿與多工作表操作3、Excel公式和函數(shù)4、Excel數(shù)據(jù)分析與處理5、Excel與其他程序的協(xié)同與共享二、Excel 2010入門1、Excel工作界面活動單元格工作表標簽行號名稱框列標編輯欄2、工作薄是工作表的集合體，一個工作薄就由一張或多張工作表構(gòu)成的；

2、（在默認情況下工作薄是由3張工作表組成，其名為工作薄1，一個工作薄就是一個Excel文件，擴展名為.xlsx）注：Excel 2003的擴展名為.xls,2010的為xlsx,高版本的Excel可以兼容低版本的所有功能，低版本的不能打開高版本的內(nèi)容3、工作表是單元格的集合，一張工作表最多可以由1048576行*16384列個單元格構(gòu)成 （行號：1-1048576列標：A-XFD列）4、單元格Excel中的最小單位（活動單元格：被激活的單元格），標識方法：列標+行號1）選定單元格：用鼠標單擊/用方向鍵移動選取/用tab移動選取。2）選擇連續(xù)的單元格區(qū)域：從第一個拖動到最后一個單元格 。3）選擇間

3、隔單元格區(qū)域：ctrl+拖動不相鄰的單元格。4）選整行整列：單擊行號與列標。5）全選：ctrl+A/單擊列標與行號交叉的全選按鈕。5、名稱框是顯示活動單元格的地址6、編輯欄顯示或編輯單元格內(nèi)容二、管理工作表1、插入工作表1）“開始”功能區(qū)單元格插入插入工作表2）右擊標簽插入工作表3）標簽右側(cè)單擊插入工作表圖標（shift+F11）2、刪除工作表1）“開始”功能區(qū)單元格刪除刪除工作表 2）右擊標簽刪除3、選擇工作表1）單個選擇：單擊標簽2）多個連續(xù)選擇：選擇第一個后按shift鍵單擊最后一個3）多個間隔選擇：按住ctrl鍵單擊所需要的工作表4）全選：右擊標簽全部選定工作表4、移動或復(fù)制工作表1）

4、右擊標簽移動或復(fù)制指定移動的位置/建立副本確定（復(fù)制工作表也可按住Ctrl+左鍵拖動）2）“開始”功能區(qū)單元格格式移動或復(fù)制工作表5、重命名工作表1）右擊標簽重命名輸入新名稱確定2）雙擊標簽輸入新名稱確定3）“開始”功能區(qū)單元格格式重命名工作表6、工作表標簽顏色設(shè)置1）右擊工作表標簽顏色2）“開始”功能區(qū)單元格格式工作表標簽顏色7、工作表行高/列寬/隱藏/凍結(jié)1)手工調(diào)整行高列寬方法一：光標放在行號列線上拖動，多選幾行可平均調(diào)整多行多列方法二：在行號之間的線上雙擊可根據(jù)內(nèi)容調(diào)整2）精確調(diào)整行高與列寬行高列寬：定位單元格“開始”功能區(qū)單元格格式行高/列寬/自動調(diào)整/最適合行高列寬。3）隱藏行/列

5、/工作表光標定位要隱藏的行列/工作表中“開始”功能區(qū)單元格可見性隱藏和取消隱藏行/列/單元格4）“視圖”功能區(qū)窗口凍結(jié)窗格凍結(jié)首行/首列/拆分單元格8、保護工作表將光標定位于要保護工作表的任意位置“開始”功能區(qū)單元格格式保護工作表輸入密碼確定9、加密工作薄“文件”選項卡 信息保護文檔用密碼進行加密輸入密碼確定保存一次。三、在單元格輸入內(nèi)容1、輸入文本漢字、字母及數(shù)字的一些組合(文本靠左對齊)2、輸入數(shù)字阿拉伯?dāng)?shù)字與$、%的組合（數(shù)字靠左對齊）注：當(dāng)數(shù)字超過單元格寬度時會以科學(xué)計數(shù)法顯示，解決的方法是調(diào)整單元格寬度，若以#顯示，也需調(diào)整列寬。3、文本與數(shù)字之間的相互轉(zhuǎn)換數(shù)字轉(zhuǎn)換為文本： 方法一：

6、前導(dǎo)加單引號：123456方法二：前導(dǎo)加等號，并用雙引號括起來：=“038011”方法三：先將單元格格式設(shè)置為“文本”型，再直接輸入（選中單元格右擊設(shè)置單元格格式數(shù)字選項卡文本確定）。4、輸入日期年月日之間用“/”或“-”號隔開，ctrl+；可以提取系統(tǒng)日期。5、輸入時間時分秒中間用冒號隔開，ctrl+shift+; ：提取系統(tǒng)時間。6、分數(shù)先輸入一個0及一個空格，再輸入分數(shù)。7、邏輯值邏輯真（true）和邏輯假(false)。四、數(shù)據(jù)的快速填充當(dāng)一組相鄰數(shù)據(jù)滿足某種數(shù)學(xué)關(guān)系或具有某種對應(yīng)關(guān)系時，便可使用“填充”方法輸入數(shù)據(jù)。1、使用填充柄在首單元格中輸入常數(shù)或公式，橫向或縱向拖動填充柄。適用

7、于以下幾種情況：1）數(shù)字格式時，直接拖動，復(fù)制數(shù)據(jù)；按住Ctrl鍵拖動，生成步長為1的等差序列。2）日期格式時，直接拖動，生成“日”步長為1的等差序列；按住Ctrl鍵拖動，復(fù)制數(shù)據(jù)。3）直接拖動公式，自動填充與首單元格相應(yīng)的公式。4）對于步長不是1的序列，也可以使用填充柄自動填充。方法是：先在相鄰兩個單元格中輸入數(shù)據(jù)，然后選定并拖動其填充柄。2、使用填充序列在首單元格輸入數(shù)據(jù)選定首單元格及填充區(qū)域開始編輯 填充系列3、自定義序列文件選項高級常規(guī)編輯自定義列表添加確定（可以定義沒有規(guī)律的序列）4、任意填充單元格選取單元格區(qū)域輸入數(shù)據(jù)按ctrl+回車(可以填充連續(xù)或不連續(xù)的單元格區(qū)域)。5、將單元

8、格行/列內(nèi)容相互轉(zhuǎn)換選中要轉(zhuǎn)換析內(nèi)容復(fù)制定位光標右擊粘貼選項轉(zhuǎn)置。五、利用公式統(tǒng)計計算1、公式的輸入及應(yīng)用公式必須以“=”開頭，可由常數(shù)、單元格引用、運算符、函數(shù)、括號等組成。 公式中的運算符：算術(shù)運算符：+、-、* 、/、%等關(guān)系運算符：=、文本運算符： 可以將一個或多個文本連接為一個組合文本值。例如：”音樂學(xué)1班”在A1單元格,”張三”在B1單元格，可寫成：=A1&B1，結(jié)果為：音樂學(xué)1班張三2、引用運算符冒號(區(qū)域運算符)：即對兩個引用之間，包括兩個引用在內(nèi)的所有單元格進行引用 ，如 A3:A7逗號(聯(lián)合引用運算符)：將多個引用合并為一個引用，如 SUM(B5:B10,D5:D1

9、0)空格是交叉運算符：產(chǎn)生對同時隸屬于兩個引用的單元格區(qū)域的引用。如SUM(A1:B3 B1:C3)，即對B2至B3單元格引用。3、單元格引用1）相對引用復(fù)制公式時地址跟著發(fā)生變化，表示方法：列坐標行坐標 例 B6,A4,C5:F8例：C1單元格有公式：=A1+B1 當(dāng)將公式復(fù)制到C2單元格時變?yōu)椋?A2+B2 當(dāng)將公式復(fù)制到D1單元格時變?yōu)椋?B1+C1當(dāng)將公式復(fù)制到A2或B2時單元格時變?yōu)?#REF!，即無效引用2）絕對引用復(fù)制公式時地址不會跟著發(fā)生變化表示方法：$列坐標$行坐標例 $B$6,$A$4,$C$5:$F$8 例：C1單元格有公式：=$A$1+$B$1 當(dāng)將公式復(fù)制到C2或任意

10、單元格時仍為：=$A$1+$B$1 （按下F4即可絕對引用3）混合引用復(fù)制公式時地址的部分內(nèi)容跟著發(fā)生變化表示方法：列坐標$行坐標 例 B$6 $列坐標行坐標 例 $B6注：如果行不變，行前加$，如果列不變，列前加$規(guī)律：加上了絕對地址符“$”的列標和行號為絕對地址，在公式向旁邊復(fù)制時不會發(fā)生變化，沒有加上絕對地址符號的列標和行號為相對地址，在公式向旁邊復(fù)制時會跟著發(fā)生變化。六、函數(shù)Excel提供的函數(shù)包括財務(wù)、日期與時間、數(shù)學(xué)、統(tǒng)計、文本等一系列函數(shù)1、數(shù)學(xué)與三角函數(shù)1）Abs（）：返回指定實數(shù)的絕對值Eg: 假定A1單元格中為4A2單元格中為了-4=Abs(A1)，結(jié)果為4=Abs(A2)

11、,結(jié)果為42）Fact（）：返回某一個數(shù)的階乘Eg:假定A3單元格中為5=Fact(A3)，結(jié)果為1203）Int（）：取指定數(shù)的整數(shù)，小數(shù)全部舍去Eg:假定A4單元格中為3.1A5單元格中為3.9=Int（A4），結(jié)果為3=Int（A5），結(jié)果為34）Mod（）：返回兩數(shù)相除的余數(shù)Eg:假定A6單元格中為9，A7單元格中為2 =Mod(A6，A7),結(jié)果為15）Round（）：對指定數(shù)進行四舍五入格式：=Round(需要四舍五入的具體數(shù)值，四舍五入后要保留的小數(shù)位數(shù))Eg:假定A8單元格中為123.456=Round（A8,2），結(jié)果為123.46=Round（A8,1），結(jié)果為123.56

12、）SUM（）：對指定單元格求和 格式：=Sum（求和的單元格） Eg：假定A9到E9的值分別為：1、2、3、4、5 =Sum（A9:E9）,結(jié)果為15=Sum（A9：C9，E9），結(jié)果為117）Sumif（）：對滿足條件的單元格求和格式：=Sumif(條件所在區(qū)域，條件，求和實際區(qū)域)Eg: 分別求男女語文成績注意：E2單元格中的公式為：=SUMIF(B2:B7,B2,C2:C7) F3單元格中的公式為：=SUMIF(B2:B7,B3,C2:C7)其中條件值可任意引用，如E2單元格中的條件引用的是B2，也可以引用B4、B7，結(jié)果是一致的8）Sumifs（）：對滿足條件的單元格求和 格式：=Su

13、mifs(求和單元格區(qū)域，條件1區(qū)域,條件1,條件2區(qū)域,條件2,條件3區(qū)域,條件3) 注意：sumifs（）函數(shù)是SUMIF（）函數(shù)的擴展，SUMIF()只能計算一個條件的，SUMIFS()可使用多個條件 Eg:匯總銷售額在100200之間的銷售額(此題涉及到了兩個條件) 匯總要求E2公式：=SUMIFS(C2:C10,A2:A10,A2)匯總要求E3公式：=SUMIFS(C2:C10,A2:A10,A2,B2:B10,B2)匯總要求E4公式：=SUM(SUMIFS(C2:C10,A2:A10,A2,B2:B10,1,3)匯總要求E5公式：=SUM(SUMIFS(C2:C10,A2:A10,

14、"A","C")匯總要求E6公式：=SUM(SUMIFS(C2:C10,A2:A10,"A","C",B2:B10,B2)匯總要求E7公式：=SUM(SUMIFS(C2:C10,A2:A10,"A","C",B2:B10,1;3;4)匯總要求E8公式：=SUM(SUMIFS(C2:C10,A2:A10,"A","B","C",B2:B10,1;3;4)9）Sumproduct（）：返回相應(yīng)的數(shù)組或區(qū)域乘積的和注：Su

15、mproduct()函數(shù)可以多個條件求和，也可以多個條件計數(shù)多條件計數(shù)格式：SUMPRODUCT(條件1)*(條件2)*(條件N)功能：統(tǒng)計同時滿足條件1、條件2到條件N的記錄的個數(shù)。Eg：統(tǒng)計性別為男且學(xué)歷為本科學(xué)生的人數(shù)注：F2中的公式為：=SUMPRODUCT(B2:B7="男")*(C2:C7="本科")F3中的公式為：=SUMPRODUCT(B2:B7="男")*1,(C2:C7="本科")*1) F1和F2中的公式是等價的其中true代表近似匹配，等價于1，false代表精確匹配，等價于0Eg：匯總一班

16、籍貫為安康男性人數(shù)=SUMPRODUCT(B2:B13="男")*1,(D2:D13="一班")*1,(E2:E13="安康")*1) 多條件求和格式：SUMPRODUCT(條件1)*(條件2) *(條件N)*求和的實際區(qū)域) 作用： 找出同時滿足條件1、條件2、條件N的記錄，然后對指定的區(qū)域求和Eg：統(tǒng)計性別為男性且學(xué)歷為本科學(xué)生的數(shù)學(xué)總和（假設(shè)C列為成績）=SUMPRODUCT(A2:A10="男")*(B2:B10="本科")*C2:C10) 2、文本函數(shù)1）=Left（）：從左邊取指定字

17、符串的值，默認從左邊的第一位開始取起格式：Left(字符串所在單元格，取字符串的長度)Eg：假定A1單元格有ABCDEFG則：Left(A1,3)，取出的結(jié)果為：ABCEg:假定A2單元格中有文博職校則：Left(A2,2),取出的結(jié)果為：文博2）Right（）：從右邊取指定字符串的值，默認從右邊的第一位開始取起格式：Right（字符串所在單元格，取字符串的長度)Eg：A1單元格有ABCDEFG則：Right(A1,3)，取出的結(jié)果為：EF3）Mid（）：從中間取指定字符串的值，取的值由起始位置和長度決定 格式：Mid(字符串，起始位置，長度)Eg：A1單元格有ABCDEFG則：Mid(A1,

18、3,2)，取出的結(jié)果為：CDEg:A2單元格中有：文博電腦學(xué)校則：Mid(A2,3,2)，取出的結(jié)果為：電腦4）Len（）：求指定單元格內(nèi)容的長度Eg:A3單元格中有：美麗的安康！則：Len(A3),返回的結(jié)果為：63、查找與引用函數(shù)1）Lookup（）：從單行或單列或數(shù)組中查找一個值，條件是向后兼容注：此函數(shù)需要用到數(shù)組，用數(shù)組則需要用花括號 Eg：取出學(xué)號的第3位與第4位做為班級，如03，則為3班=LOOKUP(MID(A2,3,2),"01","02","03","1班","2班",&qu

19、ot;3班") 2）Vlookup（）：是按列查找，最終返回該列所需查詢列序所對應(yīng)的值=Vlookup(兩個表格中相等的字段,查找范圍,返回值的列數(shù),精確Or 模糊查找) 0或False是精確查找，1或True是模糊查找例：從學(xué)生信息中返回姓名到學(xué)生成績的姓名列中，要求學(xué)號相等（注：跨工作表時表格與單元格之間用!隔開）=VLOOKUP(A2,學(xué)生信息!$A$2:$B$13,2,FALSE)4、日期和時間函數(shù)1）Date（）:返回括號里的年月日格式：Date(年，月，日)Eg: =Date(2015，3，9)，返回的結(jié)果為：2015/3/92）Now（）：返回系統(tǒng)現(xiàn)在的日期與時間Eg：

20、Now(),返回的結(jié)果為：2015/3/29 16：153）Today（）：返回今天的日期Eg：Today（），返回2015/3/294）Weekday（）：返回1至7的整數(shù)，代表一周的第幾天格式：Weekday(計算的單元格,返回值的表示):返回值的表示：1：代表星期日返回1，星期一返回2，以此類推（缺省或1都代表一周的第一天是星期日)2：代表星期一返回1，星期二返回2，以此類推3：代表星期一返回0，星期二返回1，以此類推Eg:5）Year（）：返回指定日期的年份Eg:假定A1單元格中有日期：2014-9-25則：Year(A1),返回的結(jié)果為：2014求年齡 =Year(Now( )-Ye

21、ar(出生年月日) 5、邏輯函數(shù)1）Not （）:非，取相反值2）And（）:與，當(dāng)所有結(jié)果都為真，最終的結(jié)果才會返回真3）Or（）：或，只要有一個為真，最終的結(jié)果就會返回真4）If（）：常用于判斷條件格式：=If(條件表達式，值1，值2) 注：如果條件表達式為True時，那么結(jié)果取值1；如果條件表達式為False時，則結(jié)果取值2） Eg：Eg:5）true（）：邏輯真，等價于1，false（）邏輯假，等價于06、其他函數(shù)1）Count（）：統(tǒng)計指定區(qū)域的個數(shù)Eg:A2到A8單元格中均有值則=Count（A2：A8），返回的結(jié)果為：72）Countblank（）:求空單元格個數(shù) Eg: =Co

22、untblank(引用區(qū)域)3）Counta：求非空單元格個數(shù)Eg:=Counta(引用區(qū)域)4）AVERAGE：求平均值 5）MAX（）：返回一組數(shù)值中的最大值 =Max(C2:F1)6）MIN（）：返回一組數(shù)值中的最小值 =Min (C2:F1)7）COUNTIF（）：在單元格X內(nèi)查找Y出現(xiàn)的次數(shù) 格式：=Countif(引用的區(qū)域，查找的值) 8）RANK（）：返回某數(shù)字在一列數(shù)字中相對于其他數(shù)值的大小排位=Rank(X，Y) (其中X是一個數(shù)值，Y是一個絕對引用區(qū)域)注：在書寫函數(shù)時出錯值信息：1、#DIV/0：被零除了2、#NAME：公式中出現(xiàn)了不能識別的文本3、連續(xù)多個“#”：單元格內(nèi)所含的日期、日期比單元格寬或者單元格的時間日期公式產(chǎn)生了負值4、#NULL!：為兩個相交叉的區(qū)域指定了交叉點5、NUM!：公式或函數(shù)中的數(shù)值有問題6、REF！單元格引用無效7、#N/A：在函數(shù)或公式中缺少可用數(shù)值8、#VALUE：使用錯誤的參數(shù)或運算對象類型七、圖表1、圖表的常用類型1）柱形圖和條形圖：一般反應(yīng)不同對象在同一時期某些屬性的比較2）折線圖：一般反應(yīng)同一對象在不同時期某些屬性的比較；3）餅圖：一般反應(yīng)部分與整體某些屬性的比較；2、創(chuàng)建圖表1）插入圖表：選擇數(shù)據(jù)和字