Excel在实验室设备管理中的应用

    周永胜 陈刚 屈智慧

    

    

    

    摘? 要 Excel对设备管理具有重要意义。以Excel 2010为例，讲解Excel在实验室设备管理中的典型应用。如通过分类汇总和函数的方式，统计每种设备的数量和总价值;通过结合Word的邮件合并功能，制作大型设备管理卡片。

    关键词 Excel;实验室;设备管理;Word;大型设备

    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 前言

    对于学校实验室来讲，经常会根据资产部门的要求进行设备统计和清查，在撰写实验室工作报告时也需要对设备进行分析统计。Excel软件有强大的数据处理、模拟计算的能力，函数编辑快捷灵活，一般用户使用其基本功能和内嵌的函数，就可以完成绝大部分数据管理和分析工作。一般从学校的资产管理系统中调出设备汇总表，再利用Excel对设备进行增删、修改、查找和统计。Excel不用高级语言编程，使原本繁杂、费时的数据处理变得简便快捷，大大提高了设备管理工作的效率。

    2 对设备的统计管理

    在实验室设备管理过程中，有时需要知道每一种设备的具体数量、总价值等情况，可以使用Excel的分类汇总功能进行统计。分类汇总是对数据列表按字段进行分类，将字段值相同的连续记录作为一类，进行求和、平均、计数、最大、最小等汇总运算[1]。

    分类汇总前进行排序? 选定设备名称中的某个单元格，点击打开“数据”选项卡，点击“排序和筛选”组中的“升序”或“降序”按钮，把各种设备分类排在一起，如图1所示。

    对设备数量和价值进行分类统计[2]? 点击打开“数据”选项卡，点击“分组显示”组中的“分类汇总”按钮，在“分类汇总”对话框中，分类字段选择“设备名称”，汇总方式选择“计数”，选定汇总项选择“设备名称”，如图2所示。点击“确定”，汇总结果如图3所示。

    折叠汇总表：在汇总表左上角单击显示级别按钮中的2，隐藏第3级（原始记录细节），得到仅含汇总项的表格，如图4所示。

    点击打开“开始”选项卡，在“编辑”组中点击“查找和替换”，在下拉列表中点击“定位条件”，打开“定位条件”对话框[3]（快捷键“Ctrl+G”），選中“可见单元格”（图5）。确定以后，对选中的可见单元格进行复制，再以粘贴“值”的方式，粘贴到一个新的空白Excel中，就得到各种设备数量的专门统计表。

    同样的方式，可以得到每种设备总值的汇总表（图6）。不同之处在于：分类汇总时分类字段选择“设备名称”，汇总方式选择“求和”，选定汇总项选择“单价”。当然，这种对同一字段进行不同方式的汇总，也可以在得到第一个汇总表以后直接进行第二次汇总，在“分类汇总”对话框中需要取消“替换当前分类汇总”前的“√”。这被称作嵌套汇总，可以将计数和求和统计到一个表格中。

    在新的统计表中，为了统一去掉“汇总”“计数”等字，在“开始”选项卡“编辑”组中点击“查找和选择”，在下拉列表中点击“查找”，分别查找“汇总”“计数”，替换为空字符即可。设备价值汇总表中，为了把元改为万元，在价格的右边空格中输入公式“=左边一格/10000”，再下拉复制公式即可。

    统计每种设备在不同存放地的分布? 在清查设备时，有时需要知道各种设备在不同地方的存放数量。此时要先按“设备名称”分类，再按“存放地”进行二次分类，可以使用数据透视表来统计。数据透视表综合了数据排序、筛选、分类汇总等数据分析功能，可以用不同方式灵活展示数据的特征[4]。

    点击“插入”选项卡中“数据透视表”按钮，打开“创建数据透视表”对话框，选择要统计的数据区域（如果先选定了区域，这里会自动填入），点击“确定”。在工作簿中自动创建了一个新的工作表“Sheet2”，并显示“数据透视表字段列表”任务窗格。将“设备名称”字段拖动到“行标签”下面，将“存放地”字段拖动到“列标签”下面，将“设备编号”作为计数的字段（信息不完整的字段不能作为计数使用）拖动到“数值”下面（图7）。此时看到折叠桌在405有1张，407有27张，而405所有设备的台件数为4，这些都是需要的统计数据。

    在图7中，如果“数值”下面不是显示的“计数项：…”，则需要点击右边小三角，在下拉列表中点击“值字段设置”，在打开的对话框中，计算类型选择“计数”即可。

    3 利用函数进行分类统计

    Excel函数能够完成一般的统计任务，常用的函数有AVERAGE、COUNT、COUNTIF、MAX、MIN、RANK、SUM、SUMIF、IF、PMT等，为了方便对比，这里仍然以统计设备数量、价值、存放地为例进行说明。

    提取并排列不同的设备名称? 要把所有设备名称提取出来，可以依以下步骤操作。

    1）在J2中输入公式“=IF（B2=B1，1，B2）”，意思是如果下一行名称与上一行相同，则在J2中输入数字“1”;如果不同，则输入B2的名称。然后向下拖动复制公式，则J2列中出现不同的设备名称和多个数字“1”（图8）。

    2）选中J列，在“开始”选项卡“编辑”组中点击“查找和选择”，在下拉菜单中点击“定位条件”，打开“定位条件”对话框，点击“公式”复选框，保持“数字”前的勾，去掉其他勾。点击“确定”，则所有数字“1”被选中（图9）。

    3）右击选中的单元格，点击“删除”，在弹出的“删除”对话框中选中“下方单元格上移”复选框。点击“确定”，则所有数字“1”的单元格被删除，不同的设备名称向上紧缩，并按原先的顺序排列在一列中（图10），这就是需要的设备名称列表。

    设备汇总表的函数计算? 把上述提取的所有设备名称复制粘贴到sheet 2表格中，并制作表头（图11）。选中B3单元格，输入公式“=COUNTIF（Sheet1！$B$2：$B$48，A3）”，得到桌子的数量为4;向下拖动复制公式，得到所有设备的数量（图12）。

    当这些设备发生增减变化时，可以修改计算公式中绝对引用的单元格范围重新统计。当增加新的设备品种不多时，可以直接在设备汇总表中添加设备名称;当增加的新设备较多时，则依上述方法重新提取所有设备的名称。

    对某存放地的某种设备进行函数计算? 设备有2657台套，在进行设备清查过程中有时需要知道某个房间某种设备的数量，如要统计B401中“HP计算机”有多少台，以便进行实物核对，检查设备有无丢失情况。可以使用函数：COUNTIFS（I2：I2658，”*B401*”，B2：B2658，”HP计算机”）。要注意的是，函数中所有的符号均为西文符号。在B401前后加上“*”号，表示包含B401的所有存放地，如“B401教师机”“B401讲台”等。

    用于房间管理的计算机叫“操作管理主机”，如果也需要统计为计算机，则可以按照上述函数再次统计，然后相加;或者直接使用以下函数（如图14所示）：

    4 大型设备资产管理卡片的生成

    在过去没有实现办公自动化以前，资产管理通常采用卡片的方式，一物一卡，在資产清查时一般要求账、物、卡相符。而随着管理信息化的实现以及设备的增多，再用卡片的方式进行管理，不但没有必要，也增加了管理工作量。但是，对于大型设备的管理，仍然结合卡片的管理方式，实现大型设备管理信息的多元化，强化对大型设备的管理。结合Word的邮件合并功能[6]，很方便地实现了资产卡片的生成。

    筛选出单价10万元以上的大型设备? 首先点击“开始”选项卡“编辑”组中“排序和筛选”，在下拉菜单中点击“筛选”;点击字段“单价”右侧小三角，在下拉菜单中点击“数字筛选”中的“大于或等于”按钮，打开“自定义自动筛选方式”对话框;在“大于或等于”右侧文本框中输入“100000”，如图15所示;点击确定后得到大型设备列表，如图16所示。

    然后点击“开始”选项卡“查找和选择”按钮，再通过下拉菜单中的“定位条件”按钮，把可见单元格复制、粘贴到一个新表中，得到一个单独的Excel文件《大型设备表.xlsx》。当然，通过按单价降序的方式，把大型设备排在一起，再选择、复制、粘贴到一个新表中，也可以得到大型设备表。

    在Word中建立设备卡片的模板（图17）Excel与Word的合并? 在Word中，点击打开“邮件”选项卡，点击“开始邮件合并”按钮，在下拉菜单中点击“普通Word文档”命令;点击“选择收件人”按钮，在下拉菜单中点击“使用现有列表”命令，弹出“选择数据源”对话

    框;在对话框中选择《大型设备表.xlsx》文件所在的路径，单击“打开”按钮，弹出“选择表格”对话框，保持默认设置，单击“确定”按钮。此时，两个文件便建立起了数据链接，激活了“邮件”选项卡的相关命令。

    把鼠标定位到管理卡片中“设备编号”右边空格中，点击“插入合并域”，在下拉菜单中点击“设备编号”。同样，在其他各空格中一一插入对应的合并域（图18）。

    点击“邮件”选项卡中“完成并合并”按钮，弹出“合并到新文档”对话框，点选合并记录为“全部”，点击“确定”。此时Word生成并打开一个合并的新文档，包括了每个大型设备管理卡，如图19所示。这些管理卡可以打印出来张贴到设备上，也可以塑封后挂在设备上。

    Excel的使用技巧很多，如可以用Excel合并精灵，对多份Excel数据进行合并或分割[7]。一般先对资产汇总表进行一定的调整，然后针对不同的领用人、不同的存放地等进行分割，大家可以分头进行设备清查。还使用条码的方式，提高资产清查的效率。具体方法是首先利用设置字体的方式生成每个资产的条形码，然后在手机上安装条码识别APP[8]，在设备管理中通过扫码的方式进行清点，不仅效率很高，还可以避免人工方式的差错。

    参考文献

    [1]龚沛曾，杨志强.大学计算机[M].北京：高等教育出版社，2017：146-147.

    [2]杨强，刘凡馨.Word/Excel 2010文秘办公应用典型实例[M].北京：清华大学出版社，2015：49.

    [3]殷阿娜，郝德全，等.Excel高手应用技巧400招[M].北京：电子工业出版社，2012：103.

    [4]邹姝阳，邹益民.基于EXCEL及CNKI题录文件实现对文献主题的计量分析[J].现代情报，2012，32（2）：73-80.

    [5]陈雷.大学计算机基础（MOOC）教程[M].北京：清华大学出版社，2018：163.

    [6]李云龙，等.绝了！Excel可以这样用：职场Excel效率提升秘笈[M].北京：清华大学出版社，2014：286-288.

    [7]官鑫，韩宏志，李欣欣，等.巧用Excel软件提高期刊编辑的工作效率[J].长春师范大学学报，2019（6）：197-200.

    [8]张亮.基于EXCEL的资产条码管理实践探索[J].电脑知识与技术，2013，9（25）：5800-5802.