大學(xué)計算機基礎(chǔ)系列課件 excel上

1、1歡迎同學(xué)們來到大學(xué)計算機應(yīng)用基礎(chǔ)導(dǎo)學(xué)講堂5主講：劉敏華.2Excel的考核要求掌握工作表的建立1掌握工作表的編輯以及格式化操作2掌握公式和函數(shù)的使用33掌握圖表處理操作4掌握數(shù)據(jù)庫的應(yīng)用35.3Excel的操作考點1、數(shù)據(jù)庫（工作表）的建立 理解數(shù)據(jù)庫的概念，理解字段與記錄的基本概念，掌握各種類型數(shù)據(jù)的輸入 公式的定義和復(fù)制（相對地址、絕對地址、混合地址的使用；表達(dá)式中數(shù)學(xué)運算符、文本運算符和比較運算符、區(qū)域運算符的使用） 掌握單元格、工作表與工作簿之間數(shù)據(jù)的傳遞 保存工作簿文件.4Excel的操作考點2、工作表中單元格數(shù)據(jù)的修改，常用的編輯與格式化操作： 數(shù)據(jù)的移動、復(fù)制、選擇性（轉(zhuǎn)置）粘

2、貼、單元格/行/列的插入與刪除、清除（對象包括全部、內(nèi)容、格式、批注），插入批注，數(shù)據(jù)的有效性 頁面設(shè)置（頁面方向、縮放、紙張大小，頁邊距、頁眉/頁腳） 工作表的復(fù)制、移動、重命名、插入、刪除 增加/刪除/修改/應(yīng)用樣式 單元格或區(qū)域格式化（數(shù)字、字體、對齊、邊框、圖案、設(shè)置行高/列寬）、自動套用格式、條件格式的設(shè)置 插入/刪除/修改頁眉、頁腳、批注4.5Excel的操作考點3、函數(shù)應(yīng)用：掌握以下函數(shù)，按要求對工作表進(jìn)行數(shù)據(jù)統(tǒng)計或分析 數(shù)學(xué)函數(shù)：ABS，INT，ROUND， TRUNC， RAND。 統(tǒng)計函數(shù)：SUM，SUMIF，AVERAGE，COUNT，COUNTIF，COUNTA，MAX

3、， MIN，RANK。 日期函數(shù)：DATE，DAY，MONTH，YEAR，NOW，TODAY，TIME。 條件函數(shù)：IF，AND，OR。 財務(wù)函數(shù)：PMT，PV，F(xiàn)V。 頻率分布函數(shù)： FREQUENCY。 數(shù)據(jù)庫統(tǒng)計函數(shù)：DCOUNT，DCOUNTA，DMAX，DMIN，DSUM，DAVERAGE 選擇函數(shù)：CHOOSE。 查找函數(shù)：VLOOKUP。.6Excel的操作考點4、圖表操作： 圖表類型、分類、數(shù)據(jù)系列。 圖表的建立與編輯：圖表的建立，插入 / 刪除 / 修改圖表（字體：名稱、傾斜、加粗、字號、下劃線、顏色、刪除線、上下標(biāo)；邊框：位置、線型、線寬、顏色；圖案：底紋顏色、圖案、圖案顏

4、色、邊框、圖案和填充）。5、數(shù)據(jù)庫應(yīng)用： 數(shù)據(jù)的排序（包括自定義排序） 篩選（自動篩選，高級篩選） 分類匯總 透視表的應(yīng)用6.7 Excel 2003概述 工作表的編輯與格式化 公式與函數(shù)本次導(dǎo)學(xué)講座主要內(nèi)容.81、 Excel 2003概述1.1 Excel 2003的窗口組成1.2 Excel 的基本概念 1.3 工作簿管理Excel 20031.4 Excel的數(shù)據(jù)輸入.91.1 窗口組成標(biāo) 題 欄菜 單 欄工 具 欄滾 動 條名 稱 框編 輯 框列 號行 號工作表標(biāo)簽工作簿文件名成績統(tǒng)計.xls活動單元格工作表滾動按鈕=3+6.101. 工作簿工作簿: 有若干個工作表組成1.2 基本概

5、念工作表:一個工作表有65536行、256列： 列 A,B.,IV(256) ；行 1,2, ., 65536單元格：行和列的交叉點 單元格地址：工作表名! 由列標(biāo)和行標(biāo)來引用區(qū)域：由相鄰單元格構(gòu)成的矩形區(qū)域地址： 工作表名! 左上角單元地址：右下角單元地址.11 數(shù)據(jù)清單與數(shù)據(jù)庫在Excel中，數(shù)據(jù)清單就是一個數(shù)據(jù)庫。清單中的列被認(rèn)為是數(shù)據(jù)庫的字段，清單中的列標(biāo)題認(rèn)為是數(shù)據(jù)庫的字段名，清單中的每一行被認(rèn)為是數(shù)據(jù)庫的一條記錄。.121.3 管理工作簿工作表的操作BGDEA切換工作表插入工作表重命名工作表復(fù)制工作表隱藏/顯示工作表C刪除工作表F移動工作表.13操作實例工作表的插入、復(fù)制、移動、刪

6、除、重命名；打開工作簿文件2007202.xls，并按指定要求完成有關(guān)的操作：將“sheet2”工作表刪除；復(fù)制“sheet1”工作表，并命名為“長虹商場”；插入“sheet4”工作表，并移動到sheet3的前面.14 1.4 輸入數(shù)據(jù)數(shù)據(jù)的類型BECDA字符型數(shù)據(jù)日期型數(shù)據(jù)數(shù)值型數(shù)據(jù)公式序列數(shù).15輸入數(shù)據(jù)1、數(shù)據(jù)的輸入 輸入.xls 字符 / 文本：默認(rèn)左對齊 數(shù)值：默認(rèn)右對齊 邏輯值：默認(rèn)居中對齊2、默認(rèn)的對齊方式3、數(shù)據(jù)自動輸入輸入數(shù)據(jù)指向該單元格右下角的自動填充柄 拖曳填充柄到結(jié)束 自動填充序列數(shù) 自定義序列 .162. 工作表的編輯與格式化數(shù)據(jù)的移動、復(fù)制、選擇性粘貼、單元格/行/

7、列的插入與刪除、清除（對象包括全部、內(nèi)容、格式、批注），插入批注，下拉列表數(shù)據(jù)及數(shù)據(jù)有效性頁面設(shè)置（頁面方向、縮放、紙張大小，頁邊距、頁眉/頁腳）增加/刪除/修改/應(yīng)用樣式單元格或區(qū)域格式（數(shù)字、字體、對齊、邊框、圖案、設(shè)置行高/列寬）、自動套用格式、條件格式的設(shè)置插入/刪除/修改頁眉、頁腳.17打開2007201.xls，對“Sheet1”工作表進(jìn)行如下設(shè)置： 當(dāng)用戶選中“職務(wù)”列的任一單元格時，在其右則顯示一個下拉列表框箭頭，并提供“副處長”，“處長”和“科長”的選擇項供用戶選擇 (提示：通過“數(shù)據(jù)”|“有效性”進(jìn)行設(shè)置，有效性條件為序列 ) ； 當(dāng)選中“年齡”列的任一單元格時，顯示“請輸

8、入1-100的有效年齡”，其標(biāo)題為“年齡”，當(dāng)用戶輸入某一年齡值時，即進(jìn)行檢查，如果所輸入的年齡不在指定的范圍內(nèi)，錯誤信息提示“年齡必須在1-100之間”，“停止”樣式，同時標(biāo)題為“年齡非法”。以上單元格均忽略空值。操作實例（有效性）.18打開20072002.xls，并按指定要求完成有關(guān)的操作：將B2:B21的數(shù)據(jù)采用會計專用格式、保留2位小數(shù)、使用“$”貨幣符號；將C2:C21的格式設(shè)置成與B2:B21區(qū)域相同的格式；請將“sheet3”中A1：H4區(qū)域的內(nèi)容轉(zhuǎn)置（即行和列的內(nèi)容置換），轉(zhuǎn)置結(jié)果放在A7開始的區(qū)域中，并格式化為單元格帶紅色框線（樣式；右列第5行，單元格底紋顏色淺綠(第5行第

9、4個)。利用自動套用格式“古典2”格式化sheet1的A1：C21；利用“格式”菜單的條件格式，將B2:B21中50-59用藍(lán)色表示，80-89綠色表示。操作實例（基本編輯）.19邊框線的設(shè)置利用“單元格格式”對話框設(shè)置單元格的邊框 “格式”“單元格” “邊框”第一步：先選樣式和顏色第二步：作用的地方.203. 公式與函數(shù) 3.1 創(chuàng)建公式 3.2 單元格的引用（地址） 3.4 常用函數(shù)公式與函數(shù)3.3 公式中的錯誤信息.21格式： =操作數(shù)+運算符 輸入.XLS實例 在A1中輸入公式:=8*5 在B1中輸入公式:=16/2 在A2中輸入公式:=A1/B1-2 在A3中輸入公式:=A1B13.

10、1 創(chuàng)建公式.22操作數(shù)：常量、區(qū)域名、單元格引用（地址）和函數(shù)乘方運算符：數(shù)學(xué)運算符、比較運算符、文字運算符數(shù)學(xué)運算符：、*、%、（）文字運算符：&比較運算符： =、=、=、實例 在A4中輸入公式:=(5-3)A2 在B4中輸入公式:=A1=B1 在A5中輸入公式:=A4-B1=A2.233.2 單元格的引用相對地址引用絕對地址引用 混合地址引用三維地址引用.243.2.1 相對地址的引用如果將含有單元地址的公式復(fù)制或移動到別的單元格時，這個公式中的單元格引用將會根據(jù)公式移動的相對位置作相應(yīng)的改變。 實例：在F1中的公式為：=A1+b2+100,將F1的公式復(fù)制到H2，H2的公式變?yōu)?.25

11、3.2.2 絕對地址的引用對于包括絕對引用的公式，無論將公式復(fù)制到什么位置，總是引用那些單元格。 實例：在F3中的公式為：=$A$1+100,將F3的公式復(fù)制到H4，H4的公式還是= $A$1+100.263.2.3 混合地址的引用單元格的混合引用是指公式中參數(shù)的行采用相對引用，列采用絕對引用；或列采用絕對引用、行采用相對引用，如$A3，A$3。當(dāng)含有公式的單元格因插入、復(fù)制等原因引起行、列引用的變化，公式中相對引用部分隨公式位置的變化而變化，絕對引用部分不隨公式位置的變化而變化。 .273.2.4 三維地址的引用引用包含一系列工作表名稱和單元格或單元格區(qū)域引用。三維引用的一般格式為： “工作

12、表標(biāo)簽！單元格引用”“工作簿名稱工作表標(biāo)簽！單元格引用”例如，如果想引用“Sheet1”工作表中的單元格B2，則應(yīng)輸入“Sheetl!B2”。例如，Book1Sheet1!B2例如，求Sheet1到Sheet5中區(qū)域A2:A5的和: =SUM(Sheet1:Sheet5!A2:A5).28數(shù)學(xué)函數(shù)：ABS，INT，ROUND，TRUNC, RAND統(tǒng)計函數(shù)：SUM，AVERAGE，COUNT，COUNTA，COUNTBLANK，COUNTIF，MAX，MIN，RANK日期函數(shù)：DATE，DAY，MONTH，YEAR，NOW，TODAY，TIME條件函數(shù)：IF，AND，OR頻率分布函數(shù)： FRE

13、QUENCY數(shù)據(jù)庫統(tǒng)計函數(shù)：DCOUNT，DCOUNTA，DMAX，DMIN，DSUM，DAVERAGE選擇函數(shù)：CHOOSE財務(wù)函數(shù)：PMT，PV，F(xiàn)V 3. 常用函數(shù).29 使用公式選項板輸入函數(shù)由于Excel提供了大量的函數(shù)，并且有許多函數(shù)不經(jīng)常使用，很難記住它們的參數(shù)。使用“公式選項板”能夠通過自動更正常見的錯誤和提供即時幫助來協(xié)助用戶工作。公式選項板是幫助用戶創(chuàng)建或編輯公式的工具，當(dāng)在公式中輸入函數(shù)時，公式選項板會顯示函數(shù)的名稱，它的每個參數(shù)、函數(shù)功能和參數(shù)的描述。函數(shù)的當(dāng)前結(jié)果和整個公式的結(jié)果。 “插入”“函數(shù)”.30請打開工作簿文件2007203.xls，并按指定要求完成有關(guān)的操

14、作：1.在B列計算原數(shù)值的絕對值。2.在C列計算不大于原數(shù)值的最大整數(shù)3.在D2：D31中產(chǎn)生30個隨機數(shù)操作實例.31絕對值函數(shù)ABS格式:ABS(number)功能:返回參數(shù)的絕對值。隨機函數(shù)RAND格式:RAND()功能：返回0,1)范圍內(nèi)的隨機數(shù)本操作實例中使用的函數(shù).32函數(shù)INT格式：INT(Number)功能：返回小于或等于數(shù)值number的最大整數(shù)numberINT(number)本操作實例中使用的函數(shù).33請打開2007203.xls，按要求完成有關(guān)的操作：在H列計算四門課程的平均分，并使用函數(shù)四舍五入取整。在I列計算四門課程的總分操作實例（統(tǒng)計、數(shù)學(xué)函數(shù)）.34格式:AVE

15、RAGE(number1,number2,)功能:計算參數(shù)中數(shù)值的平均值。說明:每個參數(shù)可以是數(shù)值、單元格引用坐標(biāo)或函數(shù)。求平均值函數(shù)AVERAGE.35格式：ROUND(number, num_digits)功能：按num-digits指定位數(shù),將number進(jìn)行四舍五入。說明：Number需要進(jìn)行舍入的數(shù)字。Num_digits指定的位數(shù)，按此位數(shù)進(jìn)行舍入。 四舍五入函數(shù)ROUND.36格式:SUM（number1,number2,）其中:number1，number2等表示參數(shù)。功能:計算參數(shù)中數(shù)值的總和。說明:每個參數(shù)可以是數(shù)值、單元格引用坐標(biāo)或函數(shù)。求和函數(shù)SUM.37請打開2007

16、203.xls，在K列按總分計算每位學(xué)生的排名。排位函數(shù)RANK格式：RANK(number,ref,order)功能：返回一個數(shù)值在一組數(shù)值中的排位。數(shù)值的排位是與數(shù)據(jù)清單中其他數(shù)值的相對大小說明： Number為需要找到排位的數(shù)字。Ref 為包含一組數(shù)字的數(shù)組或引用（絕對地址）。如果order為0或省略，則按降序進(jìn)行排位。操作實例（rank函數(shù)）.38請打開2007203.xls，按要求完成有關(guān)操作：在N1單元格計算全班總?cè)藬?shù)在N2單元格計算女生總?cè)藬?shù)在N3單元格計算英語最高分操作實例（統(tǒng)計函數(shù)）.39求數(shù)字個數(shù)函數(shù)COUNT格式:COUNT(value1,value2, .)功能:求參數(shù)

17、中數(shù)值數(shù)據(jù)的個數(shù)。求參數(shù)組中非空值的數(shù)目函數(shù)COUNTA格式:COUNTA(value1,value2, .)功能:返回參數(shù)組中非空值的數(shù)目。求參數(shù)組中非空值的數(shù)目函數(shù)COUNTBLANK格式:COUNTBLANK(value1,value2, .)功能:返回參數(shù)組中空值的數(shù)目。.40求滿足特定條件的單元格數(shù)目COUNTIF格式:COUNTIF(range,criteria)功能：計算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目。說明：Criteria是條件，其形式可以為數(shù)字、表達(dá)式或文本，還可以使用通配符。 Criteria表達(dá)式一般要加引號。.41求最大值函數(shù)MAX格式:MAX(number1,n

18、umber2,)功能:求參數(shù)中數(shù)值的最大值。求最小值函數(shù)MIN格式:MIN(number1,number2,)功能:求參數(shù)中數(shù)值的最小值。.42打開2007203.xls，請使用IF函數(shù)根據(jù)平均分，在J列給出級別。分三個級別：優(yōu)秀（100平均分80）及格（80平均分60）不及格（60平均分0）操作實例（IF函數(shù)）.43功能：判斷條件，根據(jù)不同的情況返回不同的結(jié)果Logical_test ：條件，即邏輯表達(dá)式。Value_if_true ：logical_test 為“TRUE”時返回的結(jié)果Value_if_false：logical_test 為“FALSE”時返回的結(jié)果。IF(logical

19、_test,valuel_if_true,value_if_false).44請打開2007203.xls，在“Sheet3”工作表中通過函數(shù)和公式計算每位教師的職稱津貼，計算標(biāo)準(zhǔn)為：職稱 津貼教授 2000元副教授 1800元講師 1500元助教 1000元要求:當(dāng)J2:J5的數(shù)據(jù)發(fā)生變化時,計算結(jié)果自動更新。操作實例（IF函數(shù)）.45操作實例請打開2007203.xls工作簿，對“Sheet4”工作表進(jìn)行以下操作： 1.在F2單元格中輸入函數(shù)求出當(dāng)前日期。2.求出各學(xué)生的出生年份。3.求出各學(xué)生的出生月份。.46本操作實例中使用的函數(shù)YEAR(date)功能：返回參數(shù)對應(yīng)的年份MONTH(

20、date)功能：返回參數(shù)對應(yīng)的月份DAY(date)功能：返回參數(shù)對應(yīng)的日的數(shù)字TODAY( )功能：返回系統(tǒng)的當(dāng)前日期，沒有參數(shù)。DATE(year, month, day)功能：把三個數(shù)組合成一個日期，3個參數(shù)。.47操作實例(財務(wù)函數(shù))請打開2007209.xls工作簿，進(jìn)行相關(guān)財務(wù)計算：若兩年后需要一筆學(xué)習(xí)費用支出，計劃從現(xiàn)在起每月初存入2000元，如果按年利2.25%，問兩年后該賬戶的存款（未來值FV）？假設(shè)要購買一項保險年金，該保險可以在今后二十年內(nèi)于每月末回報￥600。此項年金的購買成本為80,000，假定投資回報率為8%。請問該項投資合算嗎（現(xiàn)值PV）？若年利率為8%，支付的月

21、份數(shù)為10個月，貸款額為￥10，000元，那么在這樣的條件下貸款的月支付額是多少呢？（每期支付額PMT）2007209as.xls答案.48財務(wù)函數(shù)使用說明rate 為各期利率。例如，如果按 10% 的年利率借入一筆貸款來購買汽車，并按月償還貸款，則月利率為 10%/12（即 0.83%）?？梢栽诠街休斎?10%/12、0.83% 或 0.0083 作為 rate 的值。 nper為總投資（或貸款）期，即該項投資（或貸款）的付款期總數(shù)。例如，對于一筆 4 年期按月償還的汽車貸款，共有 4*12（即 48）個償款期數(shù)?？梢栽诠街休斎?48 作為 nper 的值。pmt 為各期所應(yīng)支付的金額，

22、其數(shù)值在整個年金期間保持不變。通常 pmt 包括本金和利息，但不包括其他費用及稅款。例如，$10,000 的年利率為 12% 的四年期汽車貸款的月償還額為 $263.33?？梢栽诠街休斎?-263.33 作為 pmt 的值。如果忽略 pmt，則必須包含 fv 參數(shù)。fv 為未來值，或在最后一次支付后希望得到的現(xiàn)金余額，如果省略 fv，則假設(shè)其值為零（一筆貸款的未來值即為零）。例如，如果需要在 18 年后支付 $50,000，則 $50,000 就是未來值?？梢愿鶕?jù)保守估計的利率來決定每月的存款額。如果忽略 fv，則必須包含 pmt 參數(shù)。pv 為現(xiàn)值，即從該項投資開始計算時已經(jīng)入帳的款項，或

23、一系列未來付款的當(dāng)前值的累積和，也稱為本金。如果省略 PV，則假設(shè)其值為零，并且必須包括 pmt 參數(shù)。.49格式:FV(rate,nper,pmt,pv,type)功能:基于固定利率及等額分期付款方式，返回某項投資的未來值。參數(shù)說明：Rate：各期利率。 Nper：總投資期，即該項投資的付款期總數(shù)。 Pmt：各期所應(yīng)支付的金額，其數(shù)值在整個年金期間保持不變。通常 pmt 包括本金和利息，但不包括其他費用及稅款。如果忽略 pmt，則必須包括pv 參數(shù)。Pv：現(xiàn)值，即從該項投資開始計算時已經(jīng)入帳的款項，或一系列未來付款的當(dāng)前值的累積和，也稱為本金。如果省略 PV，則假設(shè)其值為零，并且必須包括pmt 參數(shù)。 Type：數(shù)字 0 或 1，用以指定各期的付款時間是在期初還是期末。如果省略 type，則假設(shè)其值為零。 FV函數(shù).50格式:PV(rate,nper,pmt,fv,type)功能:返回投資的現(xiàn)值?，F(xiàn)值為一系