礦業(yè)信息技術基礎第一章Excel軟件應用基礎及常用計算課件

1、電子表格系統(tǒng)Microsoft Excel 97第 五 章中國礦業(yè)大學 采礦工程專業(yè)礦業(yè)信息技術基礎多媒體教學課件 Microsoft Excel 2003 中文版是一個功能強大的電子表格應用軟件。它是Microsoft公司生產(chǎn)的辦公自動化套裝軟件 Office 2003的主要應用程序之一。 它具有強大的數(shù)據(jù)計算與分析處理功能， 可以把數(shù)據(jù)用表格及各種統(tǒng)計圖、 透視圖的形式表示出來。使得制作出來的報表圖文并茂， 信息表述更加清晰。因而受到人們的普遍歡迎。1.1 Excel基礎知識第一章Excel軟件基礎及常用運算啟動 Excel 2003中文版常用方法：1.在開始菜單中，單擊“程序”的級聯(lián)菜單

2、：Microsoft Excel 2. 快捷方式：在桌面上建立一個快捷方式，雙擊其圖標即可。了解Excel 2003窗口標題欄常用工具欄菜單欄格式工具欄編輯欄列標共256列行號共65536行工作表編輯區(qū)狀態(tài)欄工作表切換Excel 2003的信息元素單元格單元格區(qū)域工作表工作簿單元格的概念 單元格是工作表中最基本的存儲數(shù)據(jù)單元。每個單元格最多可以保存32000個字符。 單元格的命名是由列標和行號唯一來標識。例如：A12表示第一列第十二行的單元格。每個單元格的六個屬性，即：數(shù)字、對齊、字體、邊框、圖案及保護；2. 單元格區(qū)域單元格區(qū)域是指多個相鄰單元格形成的矩形區(qū)域。從D3開始到右下角E6的一片連

3、續(xù)單元格區(qū)域表示為D3:E6單元格區(qū)域，連續(xù)或不連續(xù)，選取方法：按住Ctrl鍵和鼠標共同操作。工作表(1)工作表的概念 工作表是一個由行和列交叉排列的表格。行和列交叉的部分稱為一個單元格。(2)查看其它工作表單擊此處看第一單擊此處看最后一個左右移動看其它表工作簿（）工作簿的概念 工作簿是存儲數(shù)據(jù)、數(shù)據(jù)運算公式以及數(shù)據(jù)格式化等信息的文件。 一個工作簿由若干個工作表（最多可同時打開255個工作表）組成。 工作簿的文件名包括路徑（全名）不超過218個字符，默認的擴展名是 .XLS。（）系統(tǒng)每次打開的工作表的個數(shù)設置 “工具”“選項”“常規(guī)”“新工作簿內的工作表數(shù)”選擇單元格和單元格區(qū)域單元格及其區(qū)域

4、的選擇.選擇單元格和單元格區(qū)域2. 快速選擇大范圍的區(qū)域3. 選擇整個工作表4. 選擇不相鄰單元格區(qū)域5.選擇整行、整列6. 選擇相鄰和不相鄰的行和列7. 在選擇區(qū)域中移動當前活動單元格切換到Excel系統(tǒng)演示數(shù)據(jù)的輸入1. 文字（字符）型數(shù)據(jù)及輸入（默認左對齊）2. 數(shù)字（值）型數(shù)據(jù)及輸入（默認右對齊 ）3. 日期和時間型數(shù)據(jù)及輸入（默認右對齊）4. 數(shù)據(jù)輸入技巧 1）同時在多個單元格中輸入相同的數(shù)據(jù) (1)輸入相同的數(shù)字型或不具有增減的文字型 (2)輸入日期時間及具有增或減序列的文字型數(shù)據(jù) 2）填充自動增1序列 (1)填充數(shù)字型 (2)填充日期時間及有增或減的文字型 3）輸入任意等差、等比

5、的序列 4）創(chuàng)建自定義序列 5）記憶式鍵入插入（刪除）行、列和單元格插入單元格操作）插入一行或多行）插入一列或多列）插入單元格插入行若在第一行下插入行，就選中、三行，單擊“插入”里的“行”命令。插入列若在D列后面插入列，則選中E、F兩列，單擊“插入”里的“列”命令。插入單元格選中活動單元格，單擊“插入”里“單元格”或單擊鼠標右鍵，出現(xiàn)如圖所示對話框。根據(jù)需要選擇一項2. 刪除單元格操作根據(jù)需要選擇一項 選中被刪除的單元格或區(qū)域，單擊“編輯”里“刪除”命令或單擊右鍵選“刪除”項，出現(xiàn)如圖所示對話框。數(shù)據(jù)的計算與排序公式和函數(shù)的使用公式的使用1）運算符的種類2）公式輸入和復制3）相對引用和絕以引用

6、運算符的種類（1）算術運算符（2）比較運算符（3）文本運算符（4）單元格引用符 算術運算符有哪些？（）、 運算級別：括號最優(yōu)先，其次是乘方，再其次是乘、除，最后是加、減。同一級別的從左至右計算。比較運算符有哪些？、 使用以上運算符可以進行兩個數(shù)大小的比較。可以比較的數(shù)據(jù)類型有：數(shù)字型、文字型、日期型。 當比較的結果成立時，其值為：True；否則為：False 文本運算符是用來連接不同的文本產(chǎn)生新的文本。 例如：A1單元格中輸入“中”字，在A2單元格中輸入“國”字，在A3單元格中輸入：A1&A2，則A3中的數(shù)據(jù)是：中國。文本運算符 &單元格引用引用符有：冒號、空格和逗號 冒號用來定義一個單元格區(qū)

7、域。例如在右圖中，引用符表示為：A1:C5空格運算符(A1:C2 C1:D2)表示的區(qū)域相當于C1:C2空格運算符是一種交集運算(A1:A4，B1:B2，C1:C4) 相當于 A1:A4+B1:B2+C1:C4逗號運算符逗號是一種并集運算按Pagedown向下演示公式輸入和復制回車后，得到陳建軍同學的總分，點擊該單元格，點工具欄中的“復制”，然后選中F4:F7，再點擊“粘貼”。輸入公式或單擊工具欄上的按鈕其他同學的總分已被復制公式中的參數(shù)可以使用行或列的標志名?？蓮椭频狡渌麊卧瘛９綇椭婆e例相對引用和絕對引用相對引用絕對引用混合引用什么是相對引用？ 單元格相對引用的地址是單元格的相對位置。當

8、公式所在單元格地址改變時，公式中引用的單元格地址也相應發(fā)生變化。什么是絕對引用？ 絕對引用的地址是單元格的絕對位置。它不隨單元格地址的變化而變化。絕對引用地址的表示方法是在單元格的列號和行標前分別加以字符“”。例如：$A$5:$D$7 混合引用是指行采用相對引用，列采用絕對引用，或者列采用絕對引用而行采用相對引用。如$A3，$A3:B$3什么是混合引用？2.函數(shù)的使用1）從鍵盤上直接輸入函數(shù)2）使用“粘貼函數(shù)”對話框輸入函數(shù)3）出錯信息 如果用戶對常用函數(shù)及其格式比較熟悉，可以直接在單元格中輸入公式。例如， 求A1:A4區(qū)域的平均值可以在相應的單元格中輸入：AVERAGE(A1:A4)從鍵盤上

9、直接輸入函數(shù)使用“粘貼函數(shù)”對話框輸入函數(shù)調出“粘貼函數(shù)”對話框的方法： 方法一：單擊“插入”中的“函數(shù)”命令 方法二：直接單擊“格式工具欄”中的函數(shù)按鈕輸入一個等號或加號方法三：單擊此處 ，在下拉菜單列出了最常用函數(shù)。若單擊“其他函數(shù)”，也可以調出對話框 “粘貼函數(shù)”對話框 (一)選中某一函數(shù)點擊“確定”后，調出粘貼函數(shù)對話框 (2)?！罢迟N函數(shù)”對話框(二)如果輸入多個區(qū)域，可以在Number1、Number2、Number3(自動出現(xiàn)）等輸入框內輸入。1.2 回歸分析1.2.1 原理回歸分析的原理是最小二乘原理。最小二乘原理就是找到一條曲線, 使散點P的值yi 與曲線的值之差趨于最小。

10、1.2 回歸分析其分析過程為： 即：圖1-10為回歸分析原理圖。圖中曲線 為回歸曲線，只要求出系數(shù)a、b，則曲線即為所求。1.2 回歸分析1.2.2 一元線性回歸 公式推導 由 令求得系數(shù)a、b即可。1.2 回歸分析先求出系數(shù)a：再求出系數(shù)b：將式（1-2）代入式（1-3）中得：（1-2）1.2 回歸分析由方差定義 :令則式（1-4）可化簡為：由式（1-2）和式（1-6）可得：式（1-7）即為所求回歸曲線的系數(shù)。1.2 回歸分析求相關系數(shù)定義：討論：如果r=0，則離散點是無關的;如果r=1，則離散點為函數(shù)關系，所有點在直線上;如果0r K則顯著相關，若r K則不相關，若rK則弱相關。 (n-2

11、)K(n-2)K(n-2)K(n-2)K30.878100.576170.4555400.304440.811110.5529180.4438500.273250.754120.5324190.4329600.2560.707130.5139200.4227700.231970.666140.4973250.3809800.217280.632150.4821300.3494900.20590.602160.4683350.32461000.19461.2 回歸分析4 用Excel工作表解一元線形回歸問題參照例1說明解題步驟。例1：徐州夾河礦7408工作面1978年9月對端面頂板的破碎度和控頂

12、距進行礦壓觀測，得到如表1-2數(shù)據(jù): 表1-2試問控頂距（x）與破碎度（y）是否相關？控頂距X破碎度Y控頂距X破碎度Y0.7201.330.0971.10.0861.380.0791.190.0411.410.0951.220.0831.470.1181.290.0751.540.1121.2 回歸分析解:（1）打開Excel工作表，在b3:c13中輸入表1-2的數(shù)據(jù)。 圖1-111.2 回歸分析（2）選中全部數(shù)據(jù)，點擊圖表向導圖標，見圖1-11。（3）選中圖1-11中的“X-Y散點圖”項，單擊下一步見圖1-12，檢查無誤后單擊下圖1-12一步見圖1-13。（4）在圖1-13中填入標題和（X）

13、（Y）軸名稱，然后單擊下一步，見圖1-14。選中圖中的散點（呈黃色），按鼠標右鍵，選“添加趨勢線”見圖1-15。（5）在圖1-15的“選項”頁中選擇“添加公式”和“添加r值”項，在“類型”頁中選擇“線形”圖，點擊“確定”后見圖1-16。 1.2 回歸分析圖1-12圖1-131.3 行列式、方程及矩陣運算1.3.1 行列式行列式是一個數(shù)值，例如： 4 2 0 1A = 0 -1 5 3 -3 -4 2 2 0 1 -5 3 -1 5 3 2 0 1= 4 - 4 2 2 - 3 -1 5 3 1 -5 3 1 -5 3 = 2521.3 行列式、方程及矩陣運算在Excel中應用MDETERM()

14、函數(shù)可直接求解，見表1-4。BCDEF4420125250-1536-3-422701-53表1-41.3 行列式、方程及矩陣運算 在F4單元中寫入：=MDETERM(B4:E7)后，同時按Ctri+Alt+Enter健，F(xiàn)4單元中顯示行列式的值為252。1.3 行列式、方程及矩陣運算1.3.2 解方程例1:X2+7X2+3X+9 = 0求解步驟：1）對應數(shù)據(jù)見表1-5。3BCD41 201.3 行列式、方程及矩陣運算 在B4中輸入1，在D4輸入=B43+7*B42+3*B4+9，即顯示D4的值為20。1.3 行列式、方程及矩陣運算2）光標選D4單元，選“工具單變量求解”菜單，在“目標值”中輸

15、入0，在“可變單元格”中輸入B4，見圖1-19。圖1-19 圖1-201.3 行列式、方程及矩陣運算3）單擊“確定”后，出現(xiàn)“單變量求解狀況”，見圖1-20。4）單擊“確定”后，在Excel表B4單元中顯示出-6.75311，即X = -6.75311，見表1-6。3BCD4-6.753111.2139E-081.3 行列式、方程及矩陣運算注：D4單元顯示的數(shù)值與Excel設置的計算精度有關，讀者可在“工具選項重新計算最大誤差”中調整精度。1.3 行列式、方程及矩陣運算例2:X5+0.89X3+8.97X2+98 = 0求解步驟與例1相似，在B8中輸入1，在D8中輸入：=B85+0.89*B8

16、3+8.97*B82+98所得結果為：X = -2.711.3 行列式、方程及矩陣運算1.3.3 解線性方程組 4X1-X2+X3=4 X1+6X2+2X3=9 -X1+2X2+5X3=2求解步驟：1）先分別求4個行列式的值為：D = 119，D1 = 151, D2 = 147, D3 = 192）計算：X1 = D1/D X2 = D2/D X3 = D3/D X1 = 151/119=1.2689， X2 = 1.2353， X3 = 0.15971.3 行列式、方程及矩陣運算3）檢驗：X1、X2、X3是原方程的解。見圖1-21。圖1-211.3 行列式、方程及矩陣運算1.3.4 矩陣(

17、Matrix)1 定義mn個數(shù)，排成 m行n列的表，叫（m，n）矩陣。當m=n時，A叫n階矩陣。注意：矩陣與行列式是兩個完全不同的概念。行列式是一個數(shù)，矩陣是個有序元素組成的表。1.3 行列式、方程及矩陣運算2 矩陣運算1）乘法第一個矩陣的列數(shù)=第二個矩陣的行數(shù)時才能相乘。步驟a. 分別在B4:D6，F(xiàn)4:H6中輸入矩陣A、B的數(shù)值b. 選中B9:D11c. 健入=MMULT(B4:D6, F4: H6)d. 同時按Ctrl+Shift+Enter得： 43 63 58 AB = 46 88 77 87 99 88見圖1-221.3 行列式、方程及矩陣運算圖1-221.3 行列式、方程及矩陣運

18、算2）矩陣求逆 A A-1 B C D F G H 4 2 3 4 -2 0.818182 0.6363645 3 5 2 1 -0.18182 -0.363646 4 3 10 0.5 -0.27273 -0.045451.3 行列式、方程及矩陣運算步驟：a. 選中F4:H6圖1-23b. 鍵入= minverse(B4:D6)c. 同時按Ctrl+Shift+Enter見圖1-23。圖1-231.3 行列式、方程及矩陣運算DETERMent, 威脅，妨礙，(行列式)Matrix，母體，基礎，（矩陣）MULTeity 多Inverse 倒轉，逆1.4 Excel 2003數(shù)據(jù)庫管理1.4 E

19、xcel 2003數(shù)據(jù)庫管理功能建立數(shù)據(jù)清單數(shù)據(jù)的排序和篩選 數(shù)據(jù)的分類匯總目 錄Excel 2003數(shù)據(jù)庫 Excel 數(shù)據(jù)庫管理并不是真正意義上的數(shù)據(jù)庫管理系統(tǒng)，它只是把Excel工作表中的數(shù)據(jù)清單當作一個數(shù)據(jù)庫， 利用系統(tǒng)提供的組織、管理、處理數(shù)據(jù)的功能對數(shù)據(jù)清單進行查詢、 排序、 篩選以及分類匯總等類似于數(shù)據(jù)庫管理系統(tǒng)的操作。建立數(shù)據(jù)清單此行是字段名此四行是四條記錄此行是字段名此四行是四條記錄建立數(shù)據(jù)庫的原則（1）最好每張工作表只放一個數(shù)據(jù)庫清單 （一片連續(xù)的 數(shù)據(jù)區(qū)域，不應有空行或空列）；（2）數(shù)據(jù)庫的第一行稱為數(shù)據(jù)庫的結構 （字段集或列標題）；（3）同一列中除了字段之外， 其它各數(shù)

20、據(jù)類型和格式要一致。（4）一個數(shù)據(jù)庫最多記錄數(shù)是65536條，字段最大個數(shù)是255。設計數(shù)據(jù)庫的結構 要建立一個數(shù)據(jù)庫清單，首先要決定該數(shù)據(jù)庫的字段個數(shù)及其字段名（我們稱數(shù)據(jù)庫結構）。字段名可以是漢字、英文或漢語拼音等。建立好數(shù)據(jù)結構后 ，就可以利用前面介紹的方法輸入數(shù)據(jù)了。編輯數(shù)據(jù) 1）利用記錄單查看、編輯記錄 2）利用“工作表編輯”方法編輯數(shù)據(jù)庫數(shù)據(jù)編輯 打開“數(shù)據(jù)”中的“記錄單”命令，顯示如圖所示記錄單。右上角的1表示當前記錄是1號，8表示一共有8條記錄?！疤砑佑涗洝眴螕簟靶陆ā?；刪除當前記錄選“刪除”，“上一條”、“下一條”可以查看其它記錄；點擊“條件”，輸入條件表達式，只對滿足條件的

21、記錄進行顯示。若修改只需點擊某數(shù)據(jù)項。打開記錄單對話框利用“工作表編輯”方法編輯數(shù)據(jù)庫數(shù)據(jù)添加記錄：在當前工作表最后一行的空白處添加數(shù)據(jù)插入記錄：先插入一空白行，再輸入數(shù)據(jù)刪除記錄：利用刪除一行（或多行）單元格區(qū)域的方法刪除字段：選用刪除一列（或多列）單元格區(qū)域的方法數(shù)據(jù)庫的排序和篩選什么是排序？ 排序就是根據(jù)數(shù)據(jù)庫中的一列或幾列數(shù)據(jù)的大小對各記錄進行重新排列順序。排序有升序（遞增）和降序（遞減）兩種。 可進行排序的數(shù)據(jù)類型有：數(shù)值型、文字型、邏輯型、日期型四種。 （1）利用“排序”對話框排序； （2）利用常用工具欄中的排序按鈕排序。排序示例 這是個未經(jīng)排序的學生成績表數(shù)據(jù)庫?，F(xiàn)要求按總分從高

22、到底排序，總分相同的再按數(shù)學成績，數(shù)學相同的再按計算機分從高到底排列。打開“數(shù)據(jù)”中的“排序”命令，選擇主要關鍵字段、次要關鍵字段和第三關鍵字段；選擇遞增或遞減單選按鈕，按“確定”后，其結果.設置排序條件用“排序”對話框排序排序后的“學生成績表”清單若取消排序，則單擊“菜單” 中“編輯”里的“取消排序”升序按鈕降序按鈕若只按一個關鍵字段排序可以使用升（降）序按鈕使用工具欄上按鈕排序什么是篩選？ 使用篩選命令可以只顯示滿足指定條件的那些記錄。它具有查找的功能。這對于一個大型的數(shù)據(jù)庫而言，此功能是很有用的。 系統(tǒng)提供了“自動篩選”和“自定義自動篩選”兩種。自動篩選單擊“數(shù)據(jù)”里的“篩選”命令，在其

23、下拉的級聯(lián)菜單中選擇“自動篩選”命令，如圖，點擊性別下拉按鈕，點擊“男”，顯示結果.：單擊“總分”字段的“自定義.”只顯示性別是“男”的記錄自定義自動篩選選擇或輸入上例篩選條件，得到滿足條件的三條記錄:撤消自動篩選 再次單擊“數(shù)據(jù)”里的“篩選”級聯(lián)菜單中的“自動篩選”命令即可（即取消其前的復選框選中標記）。數(shù)據(jù)的分類匯總分類匯總 分類匯總就是把某一個關鍵字段的相同數(shù)據(jù)匯總在一起。 使用分類匯總命令之前，首先要對關鍵字段進行排序，使關鍵字段相同的數(shù)據(jù)記錄可以連續(xù)訪問。分類匯總舉例 先將性別字段排序。選擇分類匯總的區(qū)域。單擊“數(shù)據(jù)”中的“分類匯總”命令，在分類匯總對話框中選擇相關內容，單擊“確定”

24、即可。按總分匯總匯總結果 單擊此處只顯示“總計”單擊此處只顯示“男分類匯總”和“女分類匯總”單擊此處顯示所有記錄 單擊此處只顯示“總計”單擊此處只顯示“男分類匯總”和“女分類匯總”單擊此處顯示所有記錄清除分類匯總 如果想清除分類匯總回到數(shù)據(jù)庫清單的初始狀態(tài)，只需單擊菜單欄中“編輯“里的“撤消分類匯總”命令（若已按了1、2或3分級顯示，則需多次按“撤消顯示分級層次”至到出現(xiàn)“撤消分類匯總”為止），或在“分類匯總”對話框中按“全部刪除”按鈕。1.5 使 用 圖 表1.5 使用圖表建立圖表編輯圖表 設置圖表的格式目 錄建 立 圖 表1. 一步創(chuàng)建圖表 這種方法是利用“圖表工具欄”中的“圖表類型” 按鈕創(chuàng)建嵌入式簡單的圖表。2. 利用“圖表向導”建立圖表 這種方法可以建立類型更加豐富的嵌入式圖表或獨立的圖表工作表。建立圖表的方法選擇數(shù)據(jù)區(qū)域打開圖表工具欄，單擊“圖表類型”按鈕選擇一類型一步創(chuàng)建圖表選擇按行，結果為選擇按列，圖例內是列標題，如左圖。選擇按行，圖例是行標題利用“圖表向導”建立圖表1）選擇數(shù)據(jù)區(qū)域；2）單擊常用工具欄上的圖標 或菜單中“插入”里的“圖表” 命令，調出對話框。步驟之一 圖表類型步驟之一 圖表類型步驟之二 圖表數(shù)據(jù)源若數(shù)據(jù)區(qū)域正確，單擊“下一步”，否則單擊此處重新選中單元格區(qū)域步驟之三圖表選項圖表選項中共有六項，供對圖表進一步