《科達(dá)電腦外部設(shè)備管理信息系統(tǒng)》

1、實(shí)驗(yàn)1 科達(dá)電腦外部設(shè)備管理信息系統(tǒng)一、上機(jī)實(shí)驗(yàn)?zāi)康?. 了解使用Excel 軟件開(kāi)發(fā)一個(gè)小型信息系統(tǒng)的過(guò)程。2. 掌握使用Excel 軟件保存數(shù)據(jù)、按使用者要求對(duì)數(shù)據(jù)進(jìn)行處理輸出信息的方法。3. 掌握Excel 查詢(xún)、統(tǒng)計(jì)、輸出、宏、打印等功能，能較好地使用Excel 軟件開(kāi)發(fā)信息系統(tǒng)為管理服務(wù)。二、上機(jī)實(shí)驗(yàn)基本要求1 在規(guī)定上機(jī)時(shí)間內(nèi)完成信息系統(tǒng)的開(kāi)發(fā)任務(wù)，由指導(dǎo)老師檢查通過(guò)系統(tǒng)。2 按時(shí)提交上機(jī)實(shí)驗(yàn)報(bào)告。3 指出系統(tǒng)的創(chuàng)新之處（學(xué)生要說(shuō)明系統(tǒng)的創(chuàng)新點(diǎn)及意義）。三、開(kāi)發(fā)系統(tǒng)資料1開(kāi)發(fā)背景科達(dá)電腦公司是一個(gè)銷(xiāo)售電腦外部設(shè)備和組裝電腦的小公司，但成長(zhǎng)很快。該公司成立于1997 年，由于銷(xiāo)售量增

2、長(zhǎng)很快，公司考慮擴(kuò)展其業(yè)務(wù)。目前該公司電腦外部設(shè)備零售價(jià)格的計(jì)算依賴(lài)于720的成本加價(jià)率和10的稅率，即成本價(jià)乘以成本加價(jià)率為稅前價(jià)格，稅前價(jià)格乘以（1+稅率）為零售價(jià)格，零售價(jià)格如果有小數(shù)則四舍五入。2開(kāi)發(fā)系統(tǒng)資料下表是該公司一部分外設(shè)的數(shù)據(jù)清單。表1科達(dá)電腦公司電腦外部設(shè)備價(jià)格表2004 年9 月商品編號(hào)商品名稱(chēng)成本價(jià)成本加價(jià)率稅前價(jià)格零售價(jià)格HP640C HP Deskjet 640c Printer$135.008%$145.80 $160.00HP840C HP Deskjet 840c Printer$206.008%$222.48 $245.00HP970C HP Deskjet

3、 970c Printer$520.0010%$572.00$629.00HP990C HP Deskjet 990c Printer$636.0010%$699.60$770.00HP1100 HP Laserjet Printer 1100$694.0010%$763.40$840.00HP2100 HP Laserjet Printer 2100$1,075.0012%$1,204.00$1,324.00HP4050 HP Laserjet Printer 4050$1,940.0012%$2,172.80$2,390.00ES580 Epson Stylus 580 Printer$1

4、46.007%$156.22$172.00ES720 Epson Stylus 720 Printer$268.0010%$294.80$324.00BJC2100 Canon Bubble jet 2100 Printer$123.007%$131.61$145.00BJC3000 Canon Bubble jet 3000 Printer$204.007%$218.28$240.00CM56 Creative 56k Modem$76.0020%$91.20$100.00MT56M Mitsubishi 56k Modem$75.0015%$86.25$95.00HP3400C HP Sc

5、anjet 3400c Scanner$164.008%$177.12$195.00HP5300C HP Scanjet 5300c Scanner$350.0010%$385.00$424.00HP6350C HP Scanjet 6350c Scanner$645.0012%$722.40$795.00HP6390C HP Scanjet 6390c Scanner$970.0012%$1,086.40$1,195.00C340P Canon 340P Scanner$93.00 8%$100.44$110.00C640P Canon 640P Scanner$118.008%$127.4

6、4$140.00E640U Epson Scanner$227.0010%$249.70$275.00A1212UAgfa Scanner$160.0010%$176.00$194.003系統(tǒng)開(kāi)發(fā)基本需求科達(dá)公司希望為該公司外設(shè)銷(xiāo)售業(yè)務(wù)設(shè)計(jì)一個(gè)電腦外部設(shè)備管理信息系統(tǒng)，要求系統(tǒng)滿(mǎn)足以下要求：（1） 能輸出銷(xiāo)售發(fā)票（基于上表所列數(shù)據(jù)），發(fā)票上要有公司名稱(chēng)和地址、稅務(wù)登記號(hào)、發(fā)票號(hào)碼、客戶(hù)名稱(chēng)和地址、以及日期。發(fā)票上還應(yīng)包括商品明細(xì)部分，其內(nèi)容為：商品編號(hào)、商品名稱(chēng)、銷(xiāo)售價(jià)格（含稅），該部分至少能容納5 個(gè)條目（即該發(fā)票至少能填寫(xiě)5 個(gè)商品），并要給出貨款合計(jì)數(shù)（含稅）和稅款合計(jì)數(shù)。（2） 能對(duì)電

7、腦外部設(shè)備的數(shù)據(jù)進(jìn)行添加、刪除、修改。（3） 能對(duì)電腦外部設(shè)備的一些相關(guān)數(shù)據(jù)進(jìn)行查詢(xún)。四、開(kāi)發(fā)任務(wù)具體要求電腦外部設(shè)備管理信息系統(tǒng)要分為如下兩個(gè)子系統(tǒng)。1電腦外部設(shè)備數(shù)據(jù)編輯與查詢(xún)子系統(tǒng)應(yīng)滿(mǎn)足以下要求：（1） 有一個(gè)完整顯示外設(shè)清單的數(shù)據(jù)表，如表1 所示。外設(shè)清單數(shù)據(jù)表上應(yīng)包括公司名稱(chēng)和“電腦外部設(shè)備價(jià)格表2005 年9 月”。（2） 可以在數(shù)據(jù)表中進(jìn)行添加、刪除、修改數(shù)據(jù)的操作。（3） 在外設(shè)清單數(shù)據(jù)表中稅前價(jià)格和零售價(jià)格（即最右邊的兩列）應(yīng)由系統(tǒng)計(jì)算出來(lái)，這樣可以維護(hù)數(shù)據(jù)的一致性，零售價(jià)格還應(yīng)進(jìn)行圓整。（4） 所有數(shù)據(jù)必須格式化，例如增加貨幣符號(hào)和百分號(hào)。（5） 未使用的欄目應(yīng)保持空白，不

8、得出現(xiàn)任何符號(hào)如#N/A 或#VALUE 等。（6） 可以使外設(shè)清單數(shù)據(jù)表按升序排列。（7） 系統(tǒng)應(yīng)能輸出下列查詢(xún)報(bào)告（使用Excel 中的自動(dòng)篩選功能），在外設(shè)清單上添加宏操作按鈕，單擊按鈕可輸出上述4個(gè)報(bào)告。·零售價(jià)格小于$500 的所有外設(shè)·成本加價(jià)率大于10%的所有外設(shè)·所有的打印機(jī)（商品名稱(chēng)中含有“Printer”）·所有的惠普產(chǎn)品（商品名稱(chēng)中含有“HP”）2發(fā)票輸出與打印子系統(tǒng)在企業(yè)日常銷(xiāo)售活動(dòng)中使用的，應(yīng)滿(mǎn)足以下要求：（1） 銷(xiāo)售員可以在發(fā)票上輸入客戶(hù)名稱(chēng)和地址。（2） 銷(xiāo)售員可以根據(jù)顧客需要的商品在發(fā)票上輸入商品編號(hào)，系統(tǒng)將自動(dòng)從外設(shè)清單

9、中查出商品名稱(chēng)和零售價(jià)格，并顯示在正確的欄目里。（3） 在發(fā)票中可以自動(dòng)計(jì)算貨款合計(jì)數(shù)和稅款合計(jì)數(shù)。發(fā)票界面如圖1.1 所示。圖1.1五、系統(tǒng)開(kāi)發(fā)要點(diǎn)（一）電腦外部設(shè)備數(shù)據(jù)編輯與查詢(xún)子系統(tǒng)開(kāi)發(fā)要點(diǎn)1 輸入外設(shè)清單基礎(chǔ)數(shù)據(jù)打開(kāi)excel 軟件，在工作表“sheet1”中輸入如圖1.2 所示數(shù)據(jù)。商品編號(hào)商品名稱(chēng)成本價(jià)成本加價(jià)率稅前價(jià)格零售價(jià)格HP640C HP Deskjet 640c Printer$135.008%$145.80 $160.00HP840C HP Deskjet 840c Printer$206.008%$222.48 $245.00HP970C HP Deskjet 970

10、c Printer$520.0010%$572.00$629.00HP990C HP Deskjet 990c Printer$636.0010%$699.60$770.00HP1100 HP Laserjet Printer 1100$694.0010%$763.40$840.00HP2100 HP Laserjet Printer 2100$1,075.0012%$1,204.00$1,324.00HP4050 HP Laserjet Printer 4050$1,940.0012%$2,172.80$2,390.00ES580 Epson Stylus 580 Printer$146.0

11、07%$156.22$172.00ES720 Epson Stylus 720 Printer$268.0010%$294.80$324.00BJC2100 Canon Bubble jet 2100 Printer$123.007%$131.61$145.00BJC3000 Canon Bubble jet 3000 Printer$204.007%$218.28$240.00CM56 Creative 56k Modem$76.0020%$91.20$100.00MT56M Mitsubishi 56k Modem$75.0015%$86.25$95.00HP3400C HP Scanje

12、t 3400c Scanner$164.008%$177.12$195.00HP5300C HP Scanjet 5300c Scanner$350.0010%$385.00$424.00HP6350C HP Scanjet 6350c Scanner$645.0012%$722.40$795.00HP6390C HP Scanjet 6390c Scanner$970.0012%$1,086.40$1,195.00C340P Canon 340P Scanner$93.00 8%$100.44$110.00C640P Canon 640P Scanner$118.008%$127.44$14

13、0.00E640U Epson Scanner$227.0010%$249.70$275.00A1212UAgfa Scanner$160.0010%$176.00$194.00圖1.22 進(jìn)行數(shù)據(jù)格式化設(shè)置選擇C4 到C24 數(shù)據(jù)，右擊鼠標(biāo)選擇【設(shè)置單元格格式】菜單項(xiàng)，打開(kāi)單元格格式對(duì)話(huà)框，如圖1.3 所示進(jìn)行貨幣、小數(shù)位的設(shè)置。圖1.33 輸入“稅前價(jià)格”與“零售價(jià)格”數(shù)據(jù)的計(jì)算公式在E3 單元格輸入“稅前價(jià)格”，然后選中E4 單元格，然后在編輯欄輸入“=C4+C4*D4”，如圖1.4 所示。圖1.4輸入結(jié)束后，按回車(chē)鍵在E4 單元格會(huì)出現(xiàn)計(jì)算后的數(shù)據(jù)，如圖1.5 所示。選中E4 單元格，

14、按住右下角往下拖拽鼠標(biāo)，該列會(huì)自動(dòng)生成相應(yīng)計(jì)算公式，出現(xiàn)稅前價(jià)格數(shù)據(jù)，如圖1.6 所示。圖1.5圖1.6同理，在F3 單元格輸入“零售價(jià)格”，在F4 單元格輸入零售價(jià)格計(jì)算公式“=ROUND(E4*1.1,0)”，會(huì)自動(dòng)計(jì)算零售價(jià)格的數(shù)據(jù)。設(shè)置結(jié)果如圖1.7 所示。圖1.74 錄制宏為實(shí)現(xiàn)查詢(xún)的功能，先要錄制宏，宏可以保存一些固定的操作方式。（1） 錄制按成本價(jià)升序排列數(shù)據(jù)表的宏在excel 的菜單欄單擊【工具】【宏】【錄制新宏】菜單項(xiàng)，如圖1.8 所示。打開(kāi)錄制新宏對(duì)話(huà)框，在對(duì)話(huà)框中輸入宏的名字“成本價(jià)升序”如圖1.9 所示，單擊【確定】按鈕，即可開(kāi)始錄制新宏的工作。會(huì)重新一個(gè)宏錄制器的圖標(biāo)

15、，如圖1.10 所示。圖1.8圖1.9如圖1.10 所示選擇數(shù)據(jù)表，然后在菜單欄單擊【數(shù)據(jù)】【排序】菜單項(xiàng)，打開(kāi)排序?qū)υ?huà)框如圖1.11 所示。宏錄制器圖1.10 圖1.11 圖1.12在排序?qū)υ?huà)框“主要關(guān)鍵字”下拉欄中會(huì)出現(xiàn)選擇表的字段，選擇“成本價(jià)”，在單選框中選擇“升序”，最后單擊【確定】按鈕。此時(shí)可在數(shù)據(jù)表中看到按成本價(jià)升序排列的結(jié)果，如果沒(méi)有問(wèn)題，可單擊宏錄制器的停止按鈕，如圖1.12 所示，即可結(jié)束錄制新宏的工作。（2） 錄制使用零售價(jià)格小于$500 的所有外設(shè)的宏設(shè)置宏的名稱(chēng)為“零售價(jià)格小于$500”，在打開(kāi)宏錄制器后，選擇零售價(jià)格的數(shù)據(jù)，在菜單欄單擊【數(shù)據(jù)】【篩選】【自動(dòng)篩選】菜

16、單項(xiàng)，在零售價(jià)格名稱(chēng)欄會(huì)出現(xiàn)一個(gè)選擇的下箭頭，如圖1.13 所示。圖1.13單擊下箭頭會(huì)出現(xiàn)一個(gè)下拉選項(xiàng)，如圖1.14 所示。從中選擇“自定義”選項(xiàng)，打開(kāi)自定義自動(dòng)篩選方式對(duì)話(huà)框如圖1.15 所示。從中設(shè)置篩選的條件即可。設(shè)置完成后，關(guān)閉宏錄制器即可。圖1.14 圖1.15同理可錄制成本加價(jià)率大于10%的所有外設(shè)的宏、所有打印機(jī)（商品名稱(chēng)中含有“Printer”）的宏、所有惠普產(chǎn)品（商品名稱(chēng)中含有“HP”）的宏。5 使用宏首先在數(shù)據(jù)表下方插入一個(gè)文本方框，添加填充色與文字，并選擇陰影樣式，然后單擊右鍵選擇“指定宏”菜單項(xiàng)（如圖1.16 所示），打開(kāi)指定宏對(duì)話(huà)框如圖1.17 所示。從中選擇相應(yīng)的

17、宏名，單擊【確定】按鈕，即可完成為按鈕指定宏的工作。圖1.16圖1.17現(xiàn)在在表中單擊不同的按鈕可以出現(xiàn)不同的查詢(xún)結(jié)果，例如單擊【查HP 產(chǎn)品】按鈕，顯示結(jié)果如圖1.18 所示。圖1.18（二）發(fā)票輸出與打印子系統(tǒng)開(kāi)發(fā)要點(diǎn)1設(shè)置發(fā)票界面在工作表“sheet2”中設(shè)置發(fā)票界面格式，如圖1.19 所示。圖1.192“開(kāi)票日期”數(shù)據(jù)設(shè)置選中D4 單元格，在excel 工具欄單擊插入函數(shù)按鈕可打開(kāi)如圖1.20 所示的插入函數(shù)對(duì)話(huà)框，從中選擇“日期與時(shí)間”類(lèi)別，選擇函數(shù)TODAY，或直接在編輯欄輸入“=TODAY()”，即可在發(fā)票中自動(dòng)出現(xiàn)當(dāng)前日期，如圖1.21 所示。圖1.20圖1.213“商品名稱(chēng)”

18、數(shù)據(jù)設(shè)置選中B8 單元格，在編輯欄輸入“=IF(ISBLANK(A8),"",VLOOKUP(A8, sheet1!A1:F24,2,FALSE)”，這里使用了IF 函數(shù)、ISBLANK 函數(shù)、VLOOKUP 函數(shù)。ISBLANK 函數(shù)首先判別A8 單元格是否為空，如果為空返回值TURE，否則回值FALSE。IF 函數(shù)根據(jù)判別條件的值為FALSE，返回值VLOOKUP(A8,sheet1!A1:F24,2,FALSE)，否則返回值為空。VLOOKUP(A8,sheet1!A1:F24,2,FALSE)根據(jù)A8單元格輸入的商品名稱(chēng)搜索數(shù)據(jù)表中相同的數(shù)據(jù)，并返回第2 列（商品名稱(chēng)）的值。同理，D8 單元格設(shè)置為“=IF(ISBLANK(A8),"",VLOOKUP(A8,sheet1!A1:F24,6,FALSE)”，將自動(dòng)出現(xiàn)零售價(jià)格。在D14 顯示“合計(jì)貨款（含稅）：”數(shù)據(jù)的單元格輸入“=SUM(D8,D9,D10,D11,D12)”，將自動(dòng)出現(xiàn)和記后的數(shù)值。在D16 顯示“ 合計(jì)稅款： ” 數(shù)據(jù)的單元格輸入“=SUM(IF(ISBLANK(A8),0,VLOOKUP(A8,sheet1!A1:F2