Excel在實(shí)驗(yàn)室設(shè)備管理中的應(yīng)用

周永勝 陳剛 屈智慧

摘? 要 Excel對(duì)設(shè)備管理具有重要意義。以Excel 2010為例，講解Excel在實(shí)驗(yàn)室設(shè)備管理中的典型應(yīng)用。如通過分類匯總和函數(shù)的方式，統(tǒng)計(jì)每種設(shè)備的數(shù)量和總價(jià)值;通過結(jié)合Word的郵件合并功能，制作大型設(shè)備管理卡片。

關(guān)鍵詞 Excel;實(shí)驗(yàn)室;設(shè)備管理;Word;大型設(shè)備

Abstract Excel have great significance to equipment management. Taking Excel 2010 as an example， this paper explains the typical application of Excel in laboratory equipment management. It can count the number and total value of each equipment by subtotals and functions. It can also make large equipments management card by combining the mail merging function of Word.

Key words Excel; laboratory; equipment management; Word; large equipments

1 前言

對(duì)于學(xué)校實(shí)驗(yàn)室來講，經(jīng)常會(huì)根據(jù)資產(chǎn)部門的要求進(jìn)行設(shè)備統(tǒng)計(jì)和清查，在撰寫實(shí)驗(yàn)室工作報(bào)告時(shí)也需要對(duì)設(shè)備進(jìn)行分析統(tǒng)計(jì)。Excel軟件有強(qiáng)大的數(shù)據(jù)處理、模擬計(jì)算的能力，函數(shù)編輯快捷靈活，一般用戶使用其基本功能和內(nèi)嵌的函數(shù)，就可以完成絕大部分?jǐn)?shù)據(jù)管理和分析工作。一般從學(xué)校的資產(chǎn)管理系統(tǒng)中調(diào)出設(shè)備匯總表，再利用Excel對(duì)設(shè)備進(jìn)行增刪、修改、查找和統(tǒng)計(jì)。Excel不用高級(jí)語言編程，使原本繁雜、費(fèi)時(shí)的數(shù)據(jù)處理變得簡便快捷，大大提高了設(shè)備管理工作的效率。

2 對(duì)設(shè)備的統(tǒng)計(jì)管理

在實(shí)驗(yàn)室設(shè)備管理過程中，有時(shí)需要知道每一種設(shè)備的具體數(shù)量、總價(jià)值等情況，可以使用Excel的分類匯總功能進(jìn)行統(tǒng)計(jì)。分類匯總是對(duì)數(shù)據(jù)列表按字段進(jìn)行分類，將字段值相同的連續(xù)記錄作為一類，進(jìn)行求和、平均、計(jì)數(shù)、最大、最小等匯總運(yùn)算[1]。

分類匯總前進(jìn)行排序? 選定設(shè)備名稱中的某個(gè)單元格，點(diǎn)擊打開“數(shù)據(jù)”選項(xiàng)卡，點(diǎn)擊“排序和篩選”組中的“升序”或“降序”按鈕，把各種設(shè)備分類排在一起，如圖1所示。

對(duì)設(shè)備數(shù)量和價(jià)值進(jìn)行分類統(tǒng)計(jì)[2]? 點(diǎn)擊打開“數(shù)據(jù)”選項(xiàng)卡，點(diǎn)擊“分組顯示”組中的“分類匯總”按鈕，在“分類匯總”對(duì)話框中，分類字段選擇“設(shè)備名稱”，匯總方式選擇“計(jì)數(shù)”，選定匯總項(xiàng)選擇“設(shè)備名稱”，如圖2所示。點(diǎn)擊“確定”，匯總結(jié)果如圖3所示。

折疊匯總表：在匯總表左上角單擊顯示級(jí)別按鈕中的2，隱藏第3級(jí)（原始記錄細(xì)節(jié)），得到僅含匯總項(xiàng)的表格，如圖4所示。

點(diǎn)擊打開“開始”選項(xiàng)卡，在“編輯”組中點(diǎn)擊“查找和替換”，在下拉列表中點(diǎn)擊“定位條件”，打開“定位條件”對(duì)話框[3]（快捷鍵“Ctrl+G”），選中“可見單元格”（圖5）。確定以后，對(duì)選中的可見單元格進(jìn)行復(fù)制，再以粘貼“值”的方式，粘貼到一個(gè)新的空白Excel中，就得到各種設(shè)備數(shù)量的專門統(tǒng)計(jì)表。

同樣的方式，可以得到每種設(shè)備總值的匯總表（圖6）。不同之處在于：分類匯總時(shí)分類字段選擇“設(shè)備名稱”，匯總方式選擇“求和”，選定匯總項(xiàng)選擇“單價(jià)”。當(dāng)然，這種對(duì)同一字段進(jìn)行不同方式的匯總，也可以在得到第一個(gè)匯總表以后直接進(jìn)行第二次匯總，在“分類匯總”對(duì)話框中需要取消“替換當(dāng)前分類匯總”前的“√”。這被稱作嵌套匯總，可以將計(jì)數(shù)和求和統(tǒng)計(jì)到一個(gè)表格中。

在新的統(tǒng)計(jì)表中，為了統(tǒng)一去掉“匯總”“計(jì)數(shù)”等字，在“開始”選項(xiàng)卡“編輯”組中點(diǎn)擊“查找和選擇”，在下拉列表中點(diǎn)擊“查找”，分別查找“匯總”“計(jì)數(shù)”，替換為空字符即可。設(shè)備價(jià)值匯總表中，為了把元改為萬元，在價(jià)格的右邊空格中輸入公式“=左邊一格/10000”，再下拉復(fù)制公式即可。

統(tǒng)計(jì)每種設(shè)備在不同存放地的分布? 在清查設(shè)備時(shí)，有時(shí)需要知道各種設(shè)備在不同地方的存放數(shù)量。此時(shí)要先按“設(shè)備名稱”分類，再按“存放地”進(jìn)行二次分類，可以使用數(shù)據(jù)透視表來統(tǒng)計(jì)。數(shù)據(jù)透視表綜合了數(shù)據(jù)排序、篩選、分類匯總等數(shù)據(jù)分析功能，可以用不同方式靈活展示數(shù)據(jù)的特征[4]。

點(diǎn)擊“插入”選項(xiàng)卡中“數(shù)據(jù)透視表”按鈕，打開“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框，選擇要統(tǒng)計(jì)的數(shù)據(jù)區(qū)域（如果先選定了區(qū)域，這里會(huì)自動(dòng)填入），點(diǎn)擊“確定”。在工作簿中自動(dòng)創(chuàng)建了一個(gè)新的工作表“Sheet2”，并顯示“數(shù)據(jù)透視表字段列表”任務(wù)窗格。將“設(shè)備名稱”字段拖動(dòng)到“行標(biāo)簽”下面，將“存放地”字段拖動(dòng)到“列標(biāo)簽”下面，將“設(shè)備編號(hào)”作為計(jì)數(shù)的字段（信息不完整的字段不能作為計(jì)數(shù)使用）拖動(dòng)到“數(shù)值”下面（圖7）。此時(shí)看到折疊桌在405有1張，407有27張，而405所有設(shè)備的臺(tái)件數(shù)為4，這些都是需要的統(tǒng)計(jì)數(shù)據(jù)。

在圖7中，如果“數(shù)值”下面不是顯示的“計(jì)數(shù)項(xiàng)：…”，則需要點(diǎn)擊右邊小三角，在下拉列表中點(diǎn)擊“值字段設(shè)置”，在打開的對(duì)話框中，計(jì)算類型選擇“計(jì)數(shù)”即可。

3 利用函數(shù)進(jìn)行分類統(tǒng)計(jì)

Excel函數(shù)能夠完成一般的統(tǒng)計(jì)任務(wù)，常用的函數(shù)有AVERAGE、COUNT、COUNTIF、MAX、MIN、RANK、SUM、SUMIF、IF、PMT等，為了方便對(duì)比，這里仍然以統(tǒng)計(jì)設(shè)備數(shù)量、價(jià)值、存放地為例進(jìn)行說明。

提取并排列不同的設(shè)備名稱? 要把所有設(shè)備名稱提取出來，可以依以下步驟操作。

1）在J2中輸入公式“=IF（B2=B1，1，B2）”，意思是如果下一行名稱與上一行相同，則在J2中輸入數(shù)字“1”;如果不同，則輸入B2的名稱。然后向下拖動(dòng)復(fù)制公式，則J2列中出現(xiàn)不同的設(shè)備名稱和多個(gè)數(shù)字“1”（圖8）。

2）選中J列，在“開始”選項(xiàng)卡“編輯”組中點(diǎn)擊“查找和選擇”，在下拉菜單中點(diǎn)擊“定位條件”，打開“定位條件”對(duì)話框，點(diǎn)擊“公式”復(fù)選框，保持“數(shù)字”前的勾，去掉其他勾。點(diǎn)擊“確定”，則所有數(shù)字“1”被選中（圖9）。

3）右擊選中的單元格，點(diǎn)擊“刪除”，在彈出的“刪除”對(duì)話框中選中“下方單元格上移”復(fù)選框。點(diǎn)擊“確定”，則所有數(shù)字“1”的單元格被刪除，不同的設(shè)備名稱向上緊縮，并按原先的順序排列在一列中（圖10），這就是需要的設(shè)備名稱列表。

設(shè)備匯總表的函數(shù)計(jì)算? 把上述提取的所有設(shè)備名稱復(fù)制粘貼到sheet 2表格中，并制作表頭（圖11）。選中B3單元格，輸入公式“=COUNTIF（Sheet1！$B$2：$B$48，A3）”，得到桌子的數(shù)量為4;向下拖動(dòng)復(fù)制公式，得到所有設(shè)備的數(shù)量（圖12）。

當(dāng)這些設(shè)備發(fā)生增減變化時(shí)，可以修改計(jì)算公式中絕對(duì)引用的單元格范圍重新統(tǒng)計(jì)。當(dāng)增加新的設(shè)備品種不多時(shí)，可以直接在設(shè)備匯總表中添加設(shè)備名稱;當(dāng)增加的新設(shè)備較多時(shí)，則依上述方法重新提取所有設(shè)備的名稱。

對(duì)某存放地的某種設(shè)備進(jìn)行函數(shù)計(jì)算? 設(shè)備有2657臺(tái)套，在進(jìn)行設(shè)備清查過程中有時(shí)需要知道某個(gè)房間某種設(shè)備的數(shù)量，如要統(tǒng)計(jì)B401中“HP計(jì)算機(jī)”有多少臺(tái)，以便進(jìn)行實(shí)物核對(duì)，檢查設(shè)備有無丟失情況。可以使用函數(shù)：COUNTIFS（I2：I2658，”*B401*”，B2：B2658，”HP計(jì)算機(jī)”）。要注意的是，函數(shù)中所有的符號(hào)均為西文符號(hào)。在B401前后加上“*”號(hào)，表示包含B401的所有存放地，如“B401教師機(jī)”“B401講臺(tái)”等。

用于房間管理的計(jì)算機(jī)叫“操作管理主機(jī)”，如果也需要統(tǒng)計(jì)為計(jì)算機(jī)，則可以按照上述函數(shù)再次統(tǒng)計(jì)，然后相加;或者直接使用以下函數(shù)（如圖14所示）：

4 大型設(shè)備資產(chǎn)管理卡片的生成

在過去沒有實(shí)現(xiàn)辦公自動(dòng)化以前，資產(chǎn)管理通常采用卡片的方式，一物一卡，在資產(chǎn)清查時(shí)一般要求賬、物、卡相符。而隨著管理信息化的實(shí)現(xiàn)以及設(shè)備的增多，再用卡片的方式進(jìn)行管理，不但沒有必要，也增加了管理工作量。但是，對(duì)于大型設(shè)備的管理，仍然結(jié)合卡片的管理方式，實(shí)現(xiàn)大型設(shè)備管理信息的多元化，強(qiáng)化對(duì)大型設(shè)備的管理。結(jié)合Word的郵件合并功能[6]，很方便地實(shí)現(xiàn)了資產(chǎn)卡片的生成。

篩選出單價(jià)10萬元以上的大型設(shè)備? 首先點(diǎn)擊“開始”選項(xiàng)卡“編輯”組中“排序和篩選”，在下拉菜單中點(diǎn)擊“篩選”;點(diǎn)擊字段“單價(jià)”右側(cè)小三角，在下拉菜單中點(diǎn)擊“數(shù)字篩選”中的“大于或等于”按鈕，打開“自定義自動(dòng)篩選方式”對(duì)話框;在“大于或等于”右側(cè)文本框中輸入“100000”，如圖15所示;點(diǎn)擊確定后得到大型設(shè)備列表，如圖16所示。

然后點(diǎn)擊“開始”選項(xiàng)卡“查找和選擇”按鈕，再通過下拉菜單中的“定位條件”按鈕，把可見單元格復(fù)制、粘貼到一個(gè)新表中，得到一個(gè)單獨(dú)的Excel文件《大型設(shè)備表.xlsx》。當(dāng)然，通過按單價(jià)降序的方式，把大型設(shè)備排在一起，再選擇、復(fù)制、粘貼到一個(gè)新表中，也可以得到大型設(shè)備表。

在Word中建立設(shè)備卡片的模板（圖17）Excel與Word的合并? 在Word中，點(diǎn)擊打開“郵件”選項(xiàng)卡，點(diǎn)擊“開始郵件合并”按鈕，在下拉菜單中點(diǎn)擊“普通Word文檔”命令;點(diǎn)擊“選擇收件人”按鈕，在下拉菜單中點(diǎn)擊“使用現(xiàn)有列表”命令，彈出“選擇數(shù)據(jù)源”對(duì)話

框;在對(duì)話框中選擇《大型設(shè)備表.xlsx》文件所在的路徑，單擊“打開”按鈕，彈出“選擇表格”對(duì)話框，保持默認(rèn)設(shè)置，單擊“確定”按鈕。此時(shí)，兩個(gè)文件便建立起了數(shù)據(jù)鏈接，激活了“郵件”選項(xiàng)卡的相關(guān)命令。

把鼠標(biāo)定位到管理卡片中“設(shè)備編號(hào)”右邊空格中，點(diǎn)擊“插入合并域”，在下拉菜單中點(diǎn)擊“設(shè)備編號(hào)”。同樣，在其他各空格中一一插入對(duì)應(yīng)的合并域（圖18）。

點(diǎn)擊“郵件”選項(xiàng)卡中“完成并合并”按鈕，彈出“合并到新文檔”對(duì)話框，點(diǎn)選合并記錄為“全部”，點(diǎn)擊“確定”。此時(shí)Word生成并打開一個(gè)合并的新文檔，包括了每個(gè)大型設(shè)備管理卡，如圖19所示。這些管理卡可以打印出來張貼到設(shè)備上，也可以塑封后掛在設(shè)備上。

Excel的使用技巧很多，如可以用Excel合并精靈，對(duì)多份Excel數(shù)據(jù)進(jìn)行合并或分割[7]。一般先對(duì)資產(chǎn)匯總表進(jìn)行一定的調(diào)整，然后針對(duì)不同的領(lǐng)用人、不同的存放地等進(jìn)行分割，大家可以分頭進(jìn)行設(shè)備清查。還使用條碼的方式，提高資產(chǎn)清查的效率。具體方法是首先利用設(shè)置字體的方式生成每個(gè)資產(chǎn)的條形碼，然后在手機(jī)上安裝條碼識(shí)別APP[8]，在設(shè)備管理中通過掃碼的方式進(jìn)行清點(diǎn)，不僅效率很高，還可以避免人工方式的差錯(cuò)。

參考文獻(xiàn)

[1]龔沛曾，楊志強(qiáng).大學(xué)計(jì)算機(jī)[M].北京：高等教育出版社，2017：146-147.

[2]楊強(qiáng)，劉凡馨.Word/Excel 2010文秘辦公應(yīng)用典型實(shí)例[M].北京：清華大學(xué)出版社，2015：49.

[3]殷阿娜，郝德全，等.Excel高手應(yīng)用技巧400招[M].北京：電子工業(yè)出版社，2012：103.

[4]鄒姝陽，鄒益民.基于EXCEL及CNKI題錄文件實(shí)現(xiàn)對(duì)文獻(xiàn)主題的計(jì)量分析[J].現(xiàn)代情報(bào)，2012，32（2）：73-80.

[5]陳雷.大學(xué)計(jì)算機(jī)基礎(chǔ)（MOOC）教程[M].北京：清華大學(xué)出版社，2018：163.

[6]李云龍，等.絕了！Excel可以這樣用：職場(chǎng)Excel效率提升秘笈[M].北京：清華大學(xué)出版社，2014：286-288.

[7]官鑫，韓宏志，李欣欣，等.巧用Excel軟件提高期刊編輯的工作效率[J].長春師范大學(xué)學(xué)報(bào)，2019（6）：197-200.

[8]張亮.基于EXCEL的資產(chǎn)條碼管理實(shí)踐探索[J].電腦知識(shí)與技術(shù)，2013，9（25）：5800-5802.