利用ＳＱＬ ＳＥＲＶＥＲ、ＥＸＣＥＬ透視表等快速處理考試數(shù)據(jù)

摘要:該文展示了EXCEL透視表與SQL SERVER、ACCESS相結(jié)合，利用廣播電視大學(xué)教務(wù)管理信息系統(tǒng)數(shù)據(jù)資源，為電大的考務(wù)工作提供方便。

關(guān)鍵詞:EXCEL;透視表;ACCESS;SQL SERVER;ODBC

中圖分類號(hào):TP393 文獻(xiàn)標(biāo)識(shí)碼:A 文章編號(hào):1009-3044(2009)15-3864-03

The Use of SQL Server and Excel to Rapidly Process Exam Data

ZHOU Pao

(Hunan Radio and TV University，Changsha 410004，China)

Abstract: This text illustrated the technology of combining Excel pivot tables with SQL Server 2000 and Access in utilizing the data resources of radio and TV university teaching administration system to provide convenience for the examination work.

Key words: Excel; pivot table; Access; SQL Server 2000;ODBC

1 引言

電大考試工作比較繁重，為了方便工作，經(jīng)常需要各種各樣的數(shù)據(jù)供工作使用。大部分源數(shù)據(jù)都在SQL SERVER數(shù)據(jù)庫中，數(shù)據(jù)量也比較大，為了很快得到能滿足自己需要的數(shù)據(jù)，去編寫小的程序，在時(shí)間上和精力上可能都不夠，所以可以利用微軟的軟件:SQL SERVER、ACCESS、EXCEL和Windows操作系統(tǒng)自帶的工具:ODBC數(shù)據(jù)源管理器，在不開發(fā)軟件的情況下，就可以迅速、準(zhǔn)確、方便的得到自己需要的數(shù)據(jù)。

2 理念和流程

SQL SERVER是新一代大型關(guān)系型數(shù)據(jù)庫管理系統(tǒng)。SQL SERVER數(shù)據(jù)庫的功能強(qiáng)大，這里我們將利用SQL SERVER的視圖功能和導(dǎo)出功能。視圖是從一個(gè)表或多個(gè)表(或視圖)導(dǎo)出的表，視圖是一個(gè)虛擬表，它對(duì)應(yīng)的數(shù)據(jù)不能進(jìn)行實(shí)際存儲(chǔ)。但是把視圖從SQL SERVER導(dǎo)出，便就是一個(gè)真正的表，可以對(duì)表中的數(shù)據(jù)進(jìn)行操作。SQL SERVER的數(shù)據(jù)可以導(dǎo)出到ODBC數(shù)據(jù)源等數(shù)據(jù)源。在這里把微軟操作系統(tǒng)自帶的ODBC數(shù)據(jù)源管理器作為SQL SERVER導(dǎo)出的數(shù)據(jù)的目的地。

ODBC是一種數(shù)據(jù)庫互連接口。ODBC數(shù)據(jù)源管理器主要任務(wù)是管理安裝的ODBC驅(qū)動(dòng)程序和管理數(shù)據(jù)源。ODBC管理器根據(jù)數(shù)據(jù)源提供的數(shù)據(jù)庫位置、數(shù)據(jù)庫類型及ODBC驅(qū)動(dòng)程序等信息，建立起ODBC與具體數(shù)據(jù)庫的聯(lián)系。這里采用ACCESS數(shù)據(jù)庫，ACCESS 是微軟公司推出的基于Windows的桌面關(guān)系數(shù)據(jù)庫管理系統(tǒng)，完成大部分?jǐn)?shù)據(jù)管理的任務(wù)。選擇Access的原因有兩個(gè):1)它和SQL SERVER都是微軟公司開發(fā)的軟件，兩個(gè)數(shù)據(jù)庫系統(tǒng)之間的兼容性比較很好;2)比較靈活，存放在電腦中的位置可以改動(dòng)，改動(dòng)后只需在ODBC管理器中修改路徑即可。

EXCEL是一個(gè)功能十分強(qiáng)大而易于使用的動(dòng)態(tài)數(shù)據(jù)分析工具。其中EXCEL數(shù)據(jù)透視表報(bào)表是用于快速匯總大量數(shù)據(jù)的交互式表格，當(dāng)源數(shù)據(jù)發(fā)生變化時(shí)，通過刷新操作就可更新報(bào)表內(nèi)的數(shù)據(jù)。使用數(shù)據(jù)透視表可以將數(shù)據(jù)的排序、篩選和分類匯總?cè)齻€(gè)過程結(jié)合在一起。通過互換報(bào)表的行列字段以查看對(duì)數(shù)據(jù)源的不同匯總結(jié)果，通過顯示頁字段的不同數(shù)據(jù)項(xiàng)來篩選數(shù)據(jù)，還可以根據(jù)需要顯示所選區(qū)域中的明細(xì)數(shù)據(jù)，非常便于用戶重新組織和統(tǒng)計(jì)數(shù)據(jù)。

以上四個(gè)工具在平常的工作中好象聯(lián)系不起來，在平常工作中大家一般只用了其中一個(gè)，雖然一個(gè)工具的功能就比較強(qiáng)大了，但是很難得到我們需要的數(shù)據(jù)。平時(shí)，SQL SERVER和ACCESS都是被當(dāng)作數(shù)據(jù)庫，用來存儲(chǔ)數(shù)據(jù)，使用者可以在數(shù)據(jù)庫中查詢數(shù)據(jù)、建立視圖或查詢。EXCEL的強(qiáng)大的數(shù)據(jù)處理功能也經(jīng)常被大家忽略，經(jīng)常被認(rèn)為只是電子表格，是用來制作表格的軟件。EXCEL功能強(qiáng)大，也需要數(shù)據(jù)庫提供數(shù)據(jù)，因?yàn)镋XCEL不適合存儲(chǔ)大量數(shù)據(jù)，數(shù)據(jù)量過大會(huì)影響EXCEL的運(yùn)行速度，所以要使用SQL SERVER或者ACCESS作為數(shù)據(jù)庫來提供數(shù)據(jù)。但是EXCEL不能直接與SQL SERVER或ACCESS直接連接，需要使用ODBC與數(shù)據(jù)庫連接。如果源數(shù)據(jù)分別在不同的SQL SERVER數(shù)據(jù)庫中，可以把源數(shù)據(jù)導(dǎo)入到ACCESS數(shù)據(jù)中，便于數(shù)據(jù)的管理和維護(hù)。這四個(gè)工具之間的關(guān)系是:在ODBC數(shù)據(jù)源管理器為ACCESS數(shù)據(jù)庫建立數(shù)據(jù)源，把源數(shù)據(jù)從SQL SERVE導(dǎo)出到為ACCESS數(shù)據(jù)庫建立數(shù)據(jù)源，即把數(shù)據(jù)導(dǎo)出到ACCESS數(shù)據(jù)庫，EXCEL與數(shù)據(jù)源連接后生成透視表，當(dāng)數(shù)據(jù)源中的數(shù)據(jù)更新后，透視表中的數(shù)據(jù)也將隨著更新。

2006年開始，中央電大開始使用電大新教務(wù)管理信息系統(tǒng)平臺(tái)，此系統(tǒng)采用B/S模式(瀏覽器/服務(wù)器結(jié)構(gòu))，用戶無須安裝客戶端，只要使用瀏覽器便可完成操作。但是由于設(shè)計(jì)理念的原因，在期末考試后進(jìn)行計(jì)算機(jī)錄入成績(jī)的時(shí)候遇到了很多的不便。錄入成績(jī)有兩種方法:一是按照保密號(hào)登分，即按照考場(chǎng)錄入，因?yàn)橐粋€(gè)考場(chǎng)的人數(shù)最多只有30個(gè)，所以調(diào)用登分表的時(shí)間比較快;二是根據(jù)學(xué)號(hào)登分，如果不知道教學(xué)點(diǎn)名稱或者班代碼，調(diào)用一個(gè)分校的某門課程登分表的時(shí)間將很長(zhǎng)，將影響登分工作的進(jìn)度。由于電大某些課程的期末考試為無紙化考試，這些考試的數(shù)據(jù)從無紙化系統(tǒng)導(dǎo)出后，沒有班代碼和保密號(hào)，所以給登分工作帶來了很多的不便。利用以上四個(gè)工具就可以生成EXCEL的數(shù)據(jù)透視表，可以完善無紙化考試的考試數(shù)據(jù)，方便登分。

下面，我們以新教務(wù)管理系統(tǒng)和無紙化考試考務(wù)系統(tǒng)的數(shù)據(jù)分類匯總為例，來展示如何利用SQL SERVER、ACCESS、EXCEL和ODBC數(shù)據(jù)源管理器，方便、快捷的得到我們需要的數(shù)據(jù)，流程圖如圖1。

3 數(shù)據(jù)透視表的實(shí)現(xiàn)

1)在SQL SERVER中建立供使用的視圖

新教務(wù)管理信息系統(tǒng)數(shù)據(jù)庫中的xsb表為我們提供了學(xué)生基本數(shù)據(jù)，這個(gè)表的主要字段有四個(gè):Xh(學(xué)號(hào))、Xm(姓名)、Xxdm(學(xué)校代碼)、Bdm(班代碼)。為了使報(bào)表字段的中文化顯示，我們結(jié)合另外兩個(gè)表通過建立視圖創(chuàng)建一個(gè)虛擬表，此表將作為數(shù)據(jù)透視表的源表之一，另外兩個(gè)表是:Bjxxb(班級(jí)信息表)、Xxdmb(信息代碼表)。相關(guān)表和關(guān)鍵字段的說明如表1。

運(yùn)行SQL SERVER，打開它連接的電大新教務(wù)管理系統(tǒng)數(shù)據(jù)庫，在“視圖”中，把上面三個(gè)表進(jìn)行關(guān)聯(lián)，建立一個(gè)名為“v_學(xué)生基本信息表”的新視圖，此表就是數(shù)據(jù)透視表的源表之一?！皏_學(xué)生基本信息表”視圖的完整腳本如下:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_學(xué)生基本信息表]') and OBJECTPROPERTY(id， N'IsView') = 1)

drop view [dbo].[v_學(xué)生基本信息表]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE VIEW dbo.VIEW1

AS

SELECT dbo.xsb.Xh， dbo.xsb.Xm， dbo.xxdmb.Xxdm， dbo.xxdmb.Xxmc， dbo.xsb.Bdm，

dbo.bjxxb.Bmc

FROM dbo.bjxxb INNER JOIN

dbo.xsb ON dbo.bjxxb.Bdm = dbo.xsb.Bdm AND

dbo.bjxxb.Xxdm = dbo.xsb.Xxdm INNER JOIN

dbo.xxdmb ON dbo.xsb.Xxdm = dbo.xxdmb.Xxdm

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

2)建立數(shù)據(jù)源

另外一個(gè)源表在無紙化考試考務(wù)系統(tǒng)的SQL SERVER數(shù)據(jù)庫中，為了能方便的和“v_學(xué)生基本信息表”進(jìn)行關(guān)聯(lián)，把這兩個(gè)表導(dǎo)入到ACCESS數(shù)據(jù)庫中，再進(jìn)行關(guān)聯(lián)，就可以得到最終的數(shù)據(jù)表。需要把這個(gè)ACCESS數(shù)據(jù)庫進(jìn)行ODBC數(shù)據(jù)源配置，Excel透視表才能檢測(cè)到數(shù)據(jù)。

1.打開【ODBC 數(shù)據(jù)源管理器】窗體，點(diǎn)擊【添加按鈕】，選擇“MicrosoftAccessDriver(*.mdb)”，按【完成】按鈕，彈出【ODBC MicrosoftAccess 安裝】窗體。

2.在【數(shù)據(jù)源名】中填入“無紙化考試數(shù)據(jù)”，然后選擇相應(yīng)的ACCESS數(shù)據(jù)庫，點(diǎn)擊【確定】，即成了對(duì)ACCESS數(shù)據(jù)庫的ODBC數(shù)據(jù)源的配置(見圖2)。

ACCESS數(shù)據(jù)庫的ODBC數(shù)據(jù)源配置結(jié)束后，要把“v_學(xué)生基本信息表”和無紙化考試系統(tǒng)數(shù)據(jù)庫中的“學(xué)生成績(jī)表”導(dǎo)出到ODBC數(shù)據(jù)源中的“無紙化考試數(shù)據(jù)”中。

1.運(yùn)行SQL SERVER企業(yè)管理器，登錄數(shù)據(jù)庫服務(wù)器，選擇相應(yīng)的數(shù)據(jù)庫，啟用“導(dǎo)出數(shù)據(jù)”功能，在【選擇數(shù)據(jù)源】窗體中選擇相應(yīng)的數(shù)據(jù)源，輸入用戶名和密碼，點(diǎn)擊“下一步”;

2.在【選擇目的】窗體中“目的”選項(xiàng)中選擇【其他(ODBC數(shù)據(jù)源)】，在【用戶/系統(tǒng)DSN】中選擇“無紙化考試數(shù)據(jù)”，點(diǎn)擊“下一步”;

3.在【指定表復(fù)制或查詢】窗體中選擇【從源數(shù)據(jù)庫復(fù)制表和視圖】，點(diǎn)擊【下一步】，出現(xiàn)【選擇源表和視圖】窗體，選擇“v_學(xué)生基本信息表”視圖，依次點(diǎn)擊【下一步】*【下一步】*【完成】，即完成數(shù)據(jù)表的導(dǎo)出。

無紙化考試系統(tǒng)數(shù)據(jù)庫中的學(xué)生成績(jī)表的導(dǎo)出步驟和“v_學(xué)生基本信息表”的導(dǎo)出步驟一樣。

兩個(gè)表都導(dǎo)入了ACCESS數(shù)據(jù)庫，將要把這兩個(gè)表進(jìn)行關(guān)聯(lián)，生成供透視表使用的數(shù)據(jù)表:無紙化考試成績(jī)數(shù)據(jù)表。到此，數(shù)據(jù)全部到位。生成此表的腳本如下:

SELECT v_學(xué)生基本信息表.Xxdm， v_學(xué)生基本信息表.Xh， v_學(xué)生基本信息表.Xm， v_學(xué)生基本信息表.Xxmc， v_學(xué)生基本信息表.Bdm， v_學(xué)生基本信息表.Bmc， [8].準(zhǔn)考證號(hào)， [8].姓名， [8].總分

FROM v_學(xué)生基本信息表 RIGHT JOIN 8 ON (v_學(xué)生基本信息表.Xh = [8].準(zhǔn)考證號(hào)) AND (v_學(xué)生基本信息表.Xm = [8].姓名)

ORDER BY v_學(xué)生基本信息表.Xxdm， v_學(xué)生基本信息表.Xh

3)使用EXCEL數(shù)據(jù)透視表工具

1.調(diào)用EXCEL菜單欄中的【數(shù)據(jù)】欄，單擊其中的【數(shù)據(jù)透視表和數(shù)據(jù)透視圖】，彈出【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?-- 3步驟之1】窗體。在“指定待分析數(shù)據(jù)的數(shù)據(jù)源類型”項(xiàng)目中，選擇 “外部數(shù)據(jù)源”選項(xiàng)和“數(shù)據(jù)透視表”選項(xiàng)，然后單擊【下一步】按鈕;

2.進(jìn)入【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?-- 3步驟之2】窗體，單擊【獲取數(shù)據(jù)(G)…】，在彈出的【選擇數(shù)據(jù)源】的窗體中選擇“無紙化考試數(shù)據(jù)*”，然后單擊【確定】按鈕。

3.在【查詢向?qū)?– 選擇列】窗體左邊的列表框中選中“無紙化考試成績(jī)數(shù)據(jù)表”后，單擊【>】按鈕(見圖3)， 然后依次單擊【下一步】*【下一步】*【下一步】*【完成】按鈕，返回到【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?-- 3步驟之2】窗體，并單擊【下一步】按鈕。

4.進(jìn)入【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?-- 3步驟之3】窗體，在這里有兩種方法:一種是點(diǎn)擊【布局】按鈕，在彈出的【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?-布局】窗體中，定義數(shù)據(jù)透視表布局，對(duì)透視表不熟悉的人可以使用這種方法;另外一種是點(diǎn)擊【完成】按鈕，不使用向?qū)?，?duì)透視表比較熟悉的人可以使用這種方法。這里使用第二種方法，點(diǎn)擊【完成】按鈕后，可以進(jìn)行操作(見圖4)。將“xxdm”、“xxmc”、“xh”、“xm”字段依次拖入“行”欄目，將“總分”字段拖入“數(shù)據(jù)”欄目，然后單擊【保存】按鈕，則新建成數(shù)據(jù)透視表報(bào)表。

5.通過以上的操作，便可以得到很直觀的數(shù)據(jù)表格，可以看到全省各個(gè)教學(xué)點(diǎn)的考生的學(xué)號(hào)、姓名和成績(jī)(見圖5)。登分時(shí)，登分人員根據(jù)此表，就可以從登分系統(tǒng)中很快的調(diào)出登分表，根據(jù)學(xué)號(hào)順序進(jìn)行登分。

如果把將“xxdm”拖到“頁”欄目中，則可以根據(jù)選擇的學(xué)校代碼查看相應(yīng)學(xué)校的考生成績(jī)，便于分學(xué)校打印成績(jī)單，見圖6。

4)獲取多種報(bào)表

利用這個(gè)數(shù)據(jù)源，我們可以得到其他的統(tǒng)計(jì)報(bào)表。

為了得到更具體的信息，可以把“Bdm”和“Bjmc”拖入“行”欄目，就能看到各個(gè)教學(xué)點(diǎn)的各個(gè)班的學(xué)生的考試成績(jī)，更加方便數(shù)據(jù)的查找。

把“xxdm”、“xxmc”字段依次拖入“行”欄目，把“xh”拖入“數(shù)據(jù)”欄目，把“xh”的匯總方式改為計(jì)數(shù)，就可以得到各個(gè)教學(xué)點(diǎn)的考試人數(shù)。

當(dāng)新教務(wù)管理信息系統(tǒng)數(shù)據(jù)庫的數(shù)據(jù)和無紙化考試考務(wù)系統(tǒng)數(shù)據(jù)庫的數(shù)據(jù)更新后，只需要把ACCESS數(shù)據(jù)庫中的“v_學(xué)生基本信息表”和“學(xué)生成績(jī)表”刪除，再導(dǎo)入這兩個(gè)表，在透視表中選擇通過點(diǎn)擊鼠標(biāo)右鍵彈出菜單中的【更新數(shù)據(jù)】按鈕，就可以把報(bào)表中的數(shù)據(jù)更新，保證數(shù)據(jù)的準(zhǔn)確性。

3 結(jié)束語

通過以上例子可以看到，EXCEL的透視表功能很強(qiáng)大、操作簡(jiǎn)單方便，是我們用來進(jìn)行數(shù)據(jù)匯總、分類以及統(tǒng)計(jì)的好工具。在電大考務(wù)工作中合理地使用Excel的透視表，能夠使我們的考務(wù)工作更加方便，更加科學(xué)，工作效率更高。

參考文獻(xiàn):

[1] 楊正洪，鄭齊健，孫延輝，等.中文SQL SERVER 2000關(guān)系數(shù)據(jù)庫系統(tǒng)管理和開發(fā)指南[M].

[2] [美]John Walkenbach.中文版EXCEL 2003寶典[M].