項目七Excel的數(shù)據(jù)庫操作

1、項目七Excel的數(shù)據(jù)庫操作課題Excel的數(shù)據(jù)庫操作課型項目式教學(xué)執(zhí)教許史萍時間2010.04.26班級0913課時數(shù)4學(xué)習(xí)目標1 .學(xué)會運用Excel處理數(shù)據(jù)，得到想要的結(jié)果2 .能靈活運用數(shù)據(jù)庫操作完成特殊的任務(wù)課前準備PCM,OFFICET具，電源，相關(guān)展示內(nèi)容教學(xué)步驟教學(xué)內(nèi)容講解示范布置任務(wù)實施任務(wù)評價任務(wù)小結(jié)作業(yè)布置展示課前準備的數(shù)據(jù)表操作任務(wù)任務(wù)一：排序?qū)W生根據(jù)要求進行操作，老師巡回指導(dǎo)任務(wù)二：自動篩選完成基礎(chǔ)任務(wù)的同時，完成任務(wù)二進行方法歸納，小結(jié)。鞏固訓(xùn)練。任務(wù)三：高級篩選任務(wù)四：分類匯總完成實訓(xùn)報告學(xué)習(xí)目標3.學(xué)會運用Excel處理數(shù)據(jù)，得到想要的結(jié)果4,能靈活運用數(shù)據(jù)庫操

2、作完成特殊的任務(wù)學(xué)習(xí)準備圖4-52觀察上圖所示學(xué)生成績表，要求：(1)如找出三班中總分的最高分；(2)找出三個班中總分在400分以上的同學(xué)；(3)根據(jù)規(guī)定，每門功課在130分以上(含130分)的可以獲得三好學(xué)生稱號,請篩選出三好學(xué)生；(4)找出數(shù)學(xué)成績最好的一個班級。學(xué)習(xí)過程1 .排序Excel2000數(shù)據(jù)的排序功能可以使用戶非常容易地對記錄進行排序，用戶只要分別指定關(guān)鍵字及排序方法(升序或降序)，就可以完成排序操作。對圖4-52中的數(shù)據(jù)進行排序，步驟如下：(1)單擊數(shù)據(jù)區(qū)任一單元格，單擊“數(shù)據(jù)”菜單中的“排序”命令出現(xiàn)如圖4-53的排序圖4-53（2）在該對話框中的“主要關(guān)鍵字”下拉表框中選

3、定“總分”，系統(tǒng)默認排序方向為“遞增”，但在本例中要求找出最高分，所以選擇“遞減”前的單選按鈕，然后單擊“確定”按鈕，就出現(xiàn)如圖4-54所示的根據(jù)總分降序排列。61117-0223322t-I1±1-IIX1-1X952227444-444-J-I1±1-IIX1-1X4921562222221X1x1±-1lx1程源里霞燕嬌委彭孫吳魏21312I彳HSheetlZSheet2/Shee1:3/|<11714513039267co910111214:更）支件©浜騰®觀圉也插入喈式以工且數(shù)搪皿盤口也:幫助現(xiàn)圍X工，外口中12、.BF1I乒=

4、To?郊iJ江5firABCDEFG二1班級姓名語文數(shù)學(xué)英語總分21劉思瑤1301妁13541432娥12建林1301371364033金文靜11415013S402就靖圖4-54需要注意的是，在圖4-53中，有無標題行決定了第一行是否參加排序。下面我們學(xué)習(xí)有多個條件的時候如何進行排序。圖表如圖4-55所示。按照三個關(guān)鍵字對此表進行排序，其排序要求如圖4-56所示。排序結(jié)果如圖4-57所示2 .自動篩選使用自動篩選功能時，一次只能對工作表中的一個數(shù)據(jù)清單使用篩選命令；對同一列數(shù)據(jù)最多可以應(yīng)用兩個條件。具操作步驟如下：（1）單擊數(shù)據(jù)表區(qū)域中的任一單元格；（2）單擊“數(shù)據(jù)

5、”菜單，選擇“篩選”命令項，選取“自動篩選”命令（如圖4-58所示）；這時在每個字段上會出現(xiàn)一個篩選按鈕，如圖4-59所示。單擊需要篩選的數(shù)據(jù)列的篩選按鈕，有選擇條件“全部”、“前10個”或“自定義”等。這里我們選擇“（自定義）”，出現(xiàn)如圖4-60所示的對話框，在里面填入相應(yīng)條件即可。按圖4-60填寫篩選條件，按“確定”按鈕，顯示效果如圖4-61所示。如果是多條件自動篩選，還可以在圖4-60的第二條件行中填寫相關(guān)條件。篩選是一種用于查找數(shù)據(jù)清單中某些數(shù)據(jù)的快遞方法，即從眾多的數(shù)據(jù)中挑選出符合某種條件的數(shù)據(jù)顯示，而隱去不符合條件的其他數(shù)據(jù)（比顯示）。3 .高級篩選使用高級篩選，可以從數(shù)據(jù)庫表格中

6、篩選出符合特定條件的值。但有時限制的條件較多，再用自動篩選就有些麻煩，這時，可以使用高級篩選數(shù)據(jù)。使用高級篩選前，應(yīng)在工作表的數(shù)據(jù)清單上方或下方先建立至少有三個空行的區(qū)域，作為設(shè)置條件的區(qū)域，且數(shù)據(jù)清單必須有列標志。如要求找出符合三好學(xué)生條件的學(xué)生，也就是要求找出每門功課都在130分以上的學(xué)生，操作步驟如下：(1)在數(shù)據(jù)列表中，與數(shù)據(jù)區(qū)隔一行建立條件區(qū)域并在條件區(qū)域中輸入篩選條件。即在第一行前插入三行空行，接著在C1、D1、E1單元格中分別輸入標志“語文”、“數(shù)學(xué)”、“英語”，然后在C2D2E2單元格中分別輸入”>=130'(2)單擊“數(shù)據(jù)”菜單，選擇“篩選”中的“高級篩選”命令

7、項，屏幕出現(xiàn)如圖4-62所示的對話框。將“數(shù)據(jù)區(qū)域”和“條件區(qū)域”分別設(shè)定，再按“確定”按鈕，就會在遠數(shù)據(jù)區(qū)域顯示出符合條件的記錄，如圖4-63所示。(3)如果想保留原始的數(shù)據(jù)列表，就需將符合條件的記錄復(fù)制到其他位置，即在如圖4-62所示的對話框的“方式”選項中選擇“將篩選結(jié)果復(fù)制到其他位置”單選按鈕，并在“復(fù)制到“框中輸入欲復(fù)制到的位置，結(jié)果如圖4-64所示.(4)特別需要指出的是，當(dāng)條件區(qū)域中條件選項不在同一行的時候，代表這兩行的條件是“或”關(guān)系，在實際運用中要注意根據(jù)題目要求靈活變通。4 .分類匯總Excel2000具有很強的匯總功能，使用分類匯總工具，可以進行分類求和、求平均值等。當(dāng)然

8、，也可以很方便地刪除分類匯總的結(jié)果，恢復(fù)數(shù)據(jù)表格的原形。要進行分類匯總，首先要確定數(shù)據(jù)表格最主要的分類字段，并對數(shù)據(jù)表格按分類字段進行排序。如要求找出三個班中數(shù)學(xué)平均分最高的一個班級，則需先對如圖4-52中的數(shù)據(jù)按班級進行排序，然后單擊“數(shù)據(jù)”菜單，選擇“分類匯總”命令，出現(xiàn)如圖4-65的對話框，系統(tǒng)自動設(shè)置“分類字段”為“班級”，單擊“匯總方式”下拉列表，選擇“求平均值”,在“選定匯總項”窗口中選擇“數(shù)學(xué)”復(fù)選框，最后，按“確定”按鈕，得到如圖4-66所示的分類匯總表。由此可知數(shù)學(xué)平均分最高的為1班。在圖4-66中，用戶可以用鼠標選擇“1、2、3”來控制查看層次。只展開第一級，如圖4-67所

9、示。只展開第二級，如圖4-68所示。特別需要注意的是，在分類匯總前一定先根據(jù)分類字段進行排序，否則將不能正常進行分類匯總，如圖4-69所示。如果用戶要回到未分類匯總前的狀態(tài)，只需在如圖4-65所示的對話框中單擊“全部刪除”按鈕，圖表就會回到未分類匯總的狀態(tài)。綜合練習(xí)請你操作1 .對圖4-52中的學(xué)生進行排序，總分成績相同者按語文高低排名。根據(jù)規(guī)定，每門功課在130分以上(含130分)可以獲得三好學(xué)生稱號，請篩選出三好學(xué)生。請找出每個班中各功課的最高分。2 .在Excel中建立圖4-700要求如下：(1)在F列加入總分列，并用函數(shù)求出每個同學(xué)的總成績；(2)新建工作表“優(yōu)秀學(xué)生表”，把如圖4-7

10、0中的數(shù)據(jù)拷貝到新工作表，運用高級篩選找出符合優(yōu)秀學(xué)生條件的同學(xué)(每門功課在80分以上含(80分)；(3)新建工作表”分類匯總表”，把如圖4-70表中的數(shù)據(jù)拷貝到此表中、計算各系計算機課程的平均分。建立圖表要求如下：(1)在Excel中建立“原始表”，輸入圖4-71中的數(shù)據(jù)，用“表達式”功能，分別計算每種商品的金額(數(shù)量*單價)，要求金額的格式為“會計專用”格式(帶符號¥),并顯示為綠色斜體字。(2)建立新工作表“數(shù)據(jù)篩選”，將原始表中的數(shù)據(jù)拷貝到此表中，用篩選的方法(使用“高級篩選”，勿使用“自動篩選”)篩選出所有數(shù)量在50以上(含50)的商品，篩選后的數(shù)據(jù)放在原始表第12行的下面

11、。(3)建立新工作表“商品排序”，將原始表中的數(shù)據(jù)拷貝到此表中，用排序的方法，按“銷售日期”排序，相同日期的按“金額”排序，仍相同的按“商品類別”排序，全部采用升序。(4)建立新工作表“商品匯總”，將原始表中的數(shù)據(jù)拷貝到此表中，用分類匯總的方法，按“銷售日期”將“金額”求和匯總。(5)在工作表“商品匯總”中，數(shù)據(jù)匯總后，先將明細數(shù)據(jù)隱藏，然后用匯總數(shù)據(jù)在新工作表中建立三維餅圖，表示該商場不同日期所銷售商品占總銷售金額的百分比(不包括含數(shù)量和單價)，要求顯示百分比和圖例，標題為“各商品銷售金額比例圖”。將圖表所在工作白哦重新命名為“商品圖表”。建立圖表要求如下：(1)在Excel中建立“原始表”

12、，輸入圖4-72中的數(shù)據(jù)，新建工作表，將原始表中數(shù)據(jù)引用到此表中，并將工作表重新命名為“序列格式表”。(2)在工作表“序列格式表”中，在表格最后一行下增加一行，要求此行前6欄合并后居中顯示“平均評分”，第7欄中用“表達式”功能，計算完成所有學(xué)生評分的平均值o(3)建立新工作表“表格排序”，將原始工作表中的數(shù)據(jù)拷貝到此表中，將所有學(xué)生記錄。用排序方法，按“出生日期”進行升序排列。(4)在工作表“表格排序”中，用高級篩選的方法篩選出所有1976年以后(含1976年)的女學(xué)生和1977年以后(含1976年)的男學(xué)生，篩選后的數(shù)據(jù)放在工作表“表格排序”從第12行開始的空格中；(5)在“原始表”中，建立

13、三維簇狀條形圖，要求橫軸顯示分數(shù)，縱軸顯示學(xué)生姓名，其他內(nèi)容不在圖表中出現(xiàn)，圖表必須與圖例，標題為“學(xué)生評分圖表”。建立圖表要求如下：(1)在Excel中建立“原始表”，輸入4-73中的數(shù)據(jù)，計算“原始表”中第5列和8列銷售額，要求用“表達式”計算，并將計算出的銷售額值設(shè)為斜體，貨幣格式，加符號；(2)在“原始表”的第16行計算去年、今年的銷售量和銷售額，要求用“求和函數(shù)”，銷售數(shù)量用分位符分隔，且保留兩位小數(shù)，按第3行的格式要求表格中數(shù)據(jù)加上圖案，銷售金額用貨幣格式顯示；(3)新建工作表，表名“水果價格分析”，把原始表中的序號、名勝、去年單價、今年單價四列原始數(shù)據(jù)復(fù)制到此表中，在魚側(cè)增加一列

14、，列名為“價格比較”，計算公式為(今年單價-去年單價)今年單價，要求用百分比數(shù)顯示，小數(shù)后保留兩位小數(shù)；（4）新建工作表，表名“銷售額排名”，將原始表中的數(shù)據(jù)復(fù)制到此表中，按今年銷售額從高到低進行排序（相同銷售額的按去年銷售額從高到低排列）；（5）新建工作表，表名“銷售情況分布統(tǒng)計”，引用原始白哦中的名稱、去年銷售額、今年銷售額三列數(shù)據(jù)，作出簇狀條形圖，要求有圖例并添加標題“銷售狀況統(tǒng)計圖”（16磅紅色楷體字，并加下劃線）。知識拓展1 .圖4-53排序選項對話框的左下方有一個“選項”按鈕。點擊該按鈕，我們可以看到如圖4-74所示的對話框，該對話框可以幫助我們對排序選項做進一不控制。如圖4-75所示為各地區(qū)半年銷售情況表。按照月份進行排序，如果在選項中再選定按筆畫排序的話，就可以看到下面的效果圖，如圖4-76所示。2 .在綜合練習(xí)的請你操作1中，我們?nèi)绻褩l件2改為“根據(jù)規(guī)定