使用Excel设计实现生活饮用水水质监测报告

２０１１年８月　

中国管理信息化　

Ａｕｇ．，２０１１　

第１４卷第１６期　

Ｃｈｉｎａ　Ｍａｎａｇｅｍｅｎｔ　Ｉｎｆｏｒｍａｔｉｏｎｉｚａｔｉｏｎ　

Ｖｏ１．１４，Ｎｏ．１６　

使用Ｅｘｃｅｌ设计实现生活饮用水水质监测报告　

刘悦　

（中国疾病预防控制中心环境与健康相关产品安全所，北京１０００２１）　

【摘要】在日常工作中，经常使用监测报告、调查表、实验报告等表单存储或收集专业数据。选取合适的表单设计工具非常　

重要。Ｅｘｃｅｌ作为袁单设计工具，不仅可以设计出式样美观、格式规范的表单，其内嵌的函数、公式还可以实现自动计算、条　

件判断、数据有效性验证、统计等功能。　

［关键词］生活饮用水；水质；监测报告；表单；设计工具；Ｅｘｃｅｌ；函数；公式　

ｄｏｉ：１０．３９６９／ｊ．ｉｓｓｎ．１６７３—０１９４．２０１　１．１６．０５９　

［中圈分类号］ＴＰ３１７．３　［文献标识码］Ａ　［文章编号］１６７３一Ｏ１９４（２０ｌ１）ｌ６－０ｏ９５—０３　

在１３常工作中．经常使用监测报告、调查表、实验报告等表　

一

、

监测报告的内容　

单存储或收集专业数据，用于日后的统计分析、科学研究和决策　

生活饮用水水质监测报告共分为３部分：监测点基本信息、　

支持。一份好的表单不仅要样式美观，更要功能完备．以方便工　样品基本信息和指标监测信息。监测点基本信息主要包括：监测　

作人员快速、准确地填写。因此．选取合适的表单设计工具非常　点名称、监测点编号、所属省（直辖市）、市（地区）、区（县）、行政区　

重要。Ｅｘｃｅｌ作为表单设计工具。不仅可以设计出式样美观、格式　

划代码、地址、联系人、电话、邮件、供水类型、监测类型、监测频率　

规范的表单，其内嵌的函数、公式和ＶＢＡ还可以实现计算、判断、　等内容。样品基本信息包括：样品名称、样品编号、采样员、采样时　

筛选、有效性验证、统计、抽取、汇总等功能．是一款功能丰富、易　

间、样品说明、备注等内容。指标监测信息包括：《生活饮用水卫生　

学易用的表单设计工具。非常适合日常的表单设计工作。　

标准》（ＧＢ　５７４９—２００６）中规定的１０６项水质指标。报告格式如图　

本文结合日常工作．阐述如何使用Ｅｘｃｅｌ设计实现生活饮用　

１所示　

水水质监测报告　

一

、

悬１ｌｆ性状和一般亿擘拯椽　

Ｉ　色．虔（铂怙色度革位）　：｜　｜｜　｜　ｉ｜　ｌ。ＯＯｌ　不台格　

２　潭浊度（散射潭浊度单位　＝Ｉ毫－一鬈｜｜　３．０００１　糍勰　麟　不舍格　

３　臭和噱　台格　

４　肉暖可见物　葺曹《　不台格　

５　岫ｌ幢　８．５ｌｌｌｌｌ　不舍格　

６　镪　）　＝◇０　｜蠢蠢　Ｏ．２Ｏ∞Ｑｌ　不舍格　

７　铁‘嘲．／Ｌ）　釜　｜　ｌ　ｌ０．３０００１　不舍格　

８　锰（　／Ｌ）　誉　｜｜｜｜　０．１０００１　不舍格　

图１　生活饮用水水质监测报告（部分）　

二、监测报告的设计要求　

一

栏应根据国家行政区划代码表自动生成　

（１）省（直辖市）、市（地区）、区（县）三级关联：为了提高工作　

（３）个别指标监测值自动计算：１０６项卫生指标监测值大部　

人员的填写速度、减少手工填写错误，监测点所属省（直辖市）、市　

分需要手工填写，个别指标则根据已填写的监测值通过公式计算　

（地区）、区（县）由工作人员通过下拉列表的方式进行选择，并实　

得出，这部分指标监测值应自动生成。　

现省（直辖市）、市（地区）、区（县）三级关联。　（４）指标监测值自动判断：由于卫生指标共有１０６项．数量　

（２）当省（直辖市）、市（地区）、区（县）确定后，行政区划代码　

多、内容复杂、涉及专业面广．工作人员很难记住所有指标的标准　

［收稿日期］２０１１－０４－０８　

限值。因此，当监测值填写完毕后，合格与否一栏应根据《生活饮　

企业管理信息化　

用水卫生标准》（ＧＢ　５７４９—２００６）自动判断指标监测值是否合格。　

的要求。　

如果某些指标监测值没有填写，则自动判断为未检测　

（５）统计要求：当指标填写完毕后，未检测项目总计、合格项　

目总计、不合格项目总计自动计算生成。　

使用下拉列表或自动计算的方式进行填写　

（７）安全性：为了实现业务需求。往往需要使用大量的公式、　

函数、中间过程表等。为了防止由于工作人员误删、误改而导致　

的错误．应对表单的操作权限进行限制。　

三　监测报告的实现方法　

为了实现上述需求．设计出式样美观、功能合理的表单。需　

要使用Ｅｘｃｅｌ中的公式、数据有效性、ＩＦ（）函数、ＣＯＵＮＴＩＦ（）函　

数。ＶＬＯＯＫＵＰ（）函数、工作表保护等。　

１．省（直辖市）、市（地区）、区（县）三级关联　

（１）根据国家行政区划代码表。整理成３个辅助表：辅助表　

ｌ：省（直辖市）表；辅助表２：省（直辖市）一市（地区）表；辅助表　

（６）其他要求：为减少手工填写出现错误的概率。应尽可能　

数、ＭＡＴＣＨ（）函数、ＯＦＦＳＥＴ（）函数、ＴＯＤＡＹ（）函数、ＶＡＬＵＥ（）函　

（８）美观性：表单设计应符合结构清晰、分类准确、整齐美观　３：省（直辖市）一市（地区）一区（县）一代码表（参见图２）。　

她索市　

天津市　

恕寮市　

北京市　

恕索市辖区　

：ｌｌ：ｌ￣ｌｒＪ，　

羁就者　

山露奢　

天津市　

天ｊ．Ｉ市　

ｊ夭潍市糖区　

天津帝善　

内ｔ古毒铪匿　

辽．，ｌ鬻　

皇辣密　

两：强：省　

镩彘嵩　

挥ｌ姥鲁　

＿葛蔫渲甫　

ｊ菪山市　

囊量蕊市　

舅惫江霜　

蠢窜　

棼奢　

再：艟：譬　

簿托誊　

羁托髫　

孵龙奢　

跨托黛｝　

ｔ氍馨市　

ｒ鄢台市　

辅罐爨市　

｜膏满意　

治娴市　

纛坊市　

湃　害　

安量省　

福童奢　

赶葱省　

山东窖　

拜膏密　

潮就囊　

甥宿髫　

广素密　

孵曩簧　

ｌｌｌ矗省　

ｌｌ｜西省　

山西囊　

ｌｌ｜矗禽　

ｉ餐承市　

轰鞭市　

ｉ大髓市　

｛鼯采市　

｛长治１ｌｒ　

，‘．矗挫ｌ｜皇ｌ抢区　ｌｉｌ．墨囊　ｊ量瓣甫　

图２　辅助表１。辅助表２。辅助表３　

（２）省（直辖市）下拉列表实现方法：由于省（直辖市）是第一　

中的数据，即可生成包含所有省或直辖市名称的下拉列表（参见　

图３）。　

ＥＺ屈ｉ强髓锻厦　

级选择项，所以使用数据有效性中的序列，来源处选择辅助表１　

一一　｜　…　…｜　

ｅ

Ｄ　

生

盏　点名辣ｌ　

话饮

监

用

舅

—　

承

点基

水

车

质　＿　

佳　ｒ设一…　输入ｌ１．１《出键警青《输入法嘎式｛　

并般｝堡条ｌ毕　省（Ｉ餐ｌ＇ｒ，ｌ鬻囊　缓　ｌｊ司．１匕｛Ｉ骥　蘩蘩　ｔ　ｒ　Ｃ地区，ｌ　

行致区崩代码糍　避　簪撼　Ｉ　地址　

、　

瓣一　

鬻ｌｌｌ蠹　—　

巷撰　豫琵｜　

允ｌ许《＾）：　

鬟蕞人

巷口　

ｌ　ｌ　电话　

样晶基奉健Ｊ曩　

庠列　

熬糕∞　

ｖ　回隅窑穗皿）　

豫隈下疆耵哭ｑｊ　ｌ　％嚣　薯毪｜甍薯撼　霞萎臻．∞簪锤　薯　瞄毛墨

田　

榉晶名稚ｌ　ｌ样晶　号　来懑ｃ￡）　

；采样日　ｒ　

序号　ｌ

２０１Ｉ－ｔ一６　

括标名嚣　

一

Ｉ样晶说明　

指标监　信息　

＝钳匏：似３５　

监蕾膳　

国　

ｌ　

、

床质，Ｉ｝熄描舞　ｒ一口　１●＿¨■　矗－＾＾‘　■●１ｒ　－兰●■ｄ　■Ａ　●●　Ｉ１打，ｈ、　

愚宣性状和一般化葺　Ｌ　柳■附谨曩网肌啊＾侣＿Ｉ　憎醒用强蛾瞰ｔ【，　

　。

２ｉ潭浊度（敏射潭浊鹱单位Ｉ．　

ｌｌ色度（镑镀色度单位）　ｌ－　

３．０Ｃ　

１．Ｃ　［司［　

图３　省（直辖市）下拉列表　

（３）市（地区）下拉列表实现方法：由于市（地区）下拉列表要　称），Ｉ）”。即可生成包含对应的省或直辖市所辖的所有市或地区　

与已选择的省或直辖市关联，单独使用数据有效性无法实现，因　

名称的下拉列表。其中．ＯＦＦＳＥＴ（）函数以辅助表２的最左上角位　

此需要使用ＣＯＵＮＴＩＦ　（ｒａｎｇｅ。ｃｒｉｔｅｒｉａ）　函数、ＭＡＴＣＨ　

置作为参照位置，使用ＣＯＵＮＴ（）函数和ＭＡＴＣＨ（）函数的返回值　

辅　

、ＯＦＦＳＥＴ　

作为参数．通过偏移返回满足要求的单元格区域。ＣＯＵＮＴＩＦ（

（ｒｅｆｅｒｅｎｃｅ，ｒｏｗｓ，ｃｏ］ｓ［ｈｅｉｇｈｔ］，［ｗｅｉｇｈｔ］）函数配合数据有效性完　

助表２中第１列．省或直辖市名称）函数判断某省或直辖市在辅　

　辅助表２　

成　通过在其数据有效性的序列来源处输入公式＂＝ＯＦＦＳＥＴ（辅助　

助表２的第１列中的个数。ＭＡＴＣＨ（省或直辖市名称，

（１ｏｏｋｕｐ＿ｖ￣ｕｅ，ｌｏｏｋｕｐ＿ａｒｒｅａｙ，［ｍａｔｃｈ＿ｔｙｐｅ］）　函数

表２左上角的位置．ＭＡＴＣＨ（省或直辖市名称，辅助表２中第１　

中第１列的行数．０）函数判断某省或直辖市在辅助表２的第１　

列的行数，０）一１，ｌ。ＣＯＵＮＴＩＦ（辅助表２中第１列，省或直辖市名　列中第一次出现的行数（参见图４）　

企业管理信息化　

生活饮用水永霞苴■　

－飘ａ＿ｏｔｍ扁▲－－▲平－　馕４　－　

苴—点名称　日ｆ～１ｊ辕入穗■８出镰０誊８输入法馘｝　

睿‘ｌｌ市’　０澎　剃鞠　删１｜（｜Ｉ区）　臻强　瞧帮　礴教蛙条伴　

忏ｌ酸区锚代翻　麟　戮　嬲　■生　允湃∞：　

；囊蓉人　ｌ　电簧　ｉ序判　；　里，　

供求樊盔　黪静　爨　鹈糍矧苴　凳墨　绶饿煎　鼹　戤糍　翼隈下ｌ明ｌ受∞　

嚣曩基幸蕾息　

样曩名称ｌ　ｌ并叠■号ｌ　ｌ　来蠢　：　

摹髯目一ｒ　２０１１－４－８　ｌ髯■　舅

擅标

Ｉ　

苴矗健童　

ｌ　ｉ司，，ｓ孵口ｌ

，

－时ｃＨａＭ，Ｊｌ：Ｊ∞　ｏ）－１．１．∞ｕ　园　

序号　ｌ捂椽名鼻　ｌ墨■■　

、　

一

墨

承

毫性

重

软和

＇｜嫂

一曩

搬

ｌ匕零

．ｉｉ　

、

擅　口对霄＿擀设舶扮解霄嚣ｈ．ｊ游明适旺毂ｑ巴）　

．

２

ｌ

　１【

潭浊度（

色压…‘铝韬邑压罩位）…

散射潭泣度簟位ｌ

…、　１

－

　３

．…　１．Ｏ０１　

．

０００ｌ　瀚　

ｊ广　］厂　］　

图４　市（地区）下拉列裹　

（４）区（县）下拉列表实现方法：与市（地区）下拉列表的实　５．统计汇总　

现方法类似，用辅助表３代替辅助表２．通过在其数据有效性的　使用ＣＯＵＮＴＩＦ（ｒａｎｇｅ，ｃｒｉｔｅｒｉａ）函数实现未检测项目、合格项　

序列来源处输入公式“＝ＯＦＦＳＥＴ（辅助表３第２列左上角的位置．　

目、不合格项目的统计汇总。具体实现如下：检测项目总计＝１０６一　

ＭＡＴＣＨ（市或地区名称。辅助表３中第２列的行数。０）一ｌ，１。　

ＣＯＵＮＴＩＦ（统计区域，”未检测”），合格总计＝ＣＯＵＮ￣Ｆ（统计区　

ＣＯＵＮＴＩＦ（辅助表３中第２列，市或地区名称），１）”，即可生成包　

域，”合格”），不合格总计＝ＣＯＵＮＴＩＦ（统计区域，”合格”）。　

含对应的市或地区所辖的所有区或县名称的下拉列表　

６．其他　

２．行政区划代码自动生成　

使用ＴＯＤＡＹ（）函数自动填写填表日期。使用数据有效性生　

使用ＶＬＯＯＫＵＰ（１ｏｏｋｕｐ

ｖａｌｕｅ，ｔａｂａｌｅ

＿＿

ａｒｒａｙ，ｃｏｌ

＿

ｉｎｄｅｘ　ｎｕｍ，　

成下拉列表填写表单。如臭和味的监测值为无异臭味和有异臭　

［ｒａｎｇｅ＿ｌｏｏｋｕｐ］）函数实现行政区划代码自动生成。具体实现如　味两种，使用数据有效性，在序列来源处填写“无异臭味。有异臭　

下：“＝ＶＬＯＯＫＵＰ（区或县名称。“辅助表３的第３列和第４列”，２，　

味”，即可形成下拉列表由工作人员选择填写。　

ｆａｌｓｅ）”。根据区或县的名称，在辅助表３的第３列中进行精确查　

７．安全性　

询。并将与查询结果对应的第４列中的行政区划代码取出。　使用隐藏辅助表、锁定非编辑单元格、保护工作表和设定安　

３．指标监测值自动计算　全密码相结合的方式，使得只有被授权的人员才可以修改表单．　

三卤甲烷的监测值是通过三氯甲烷、一氯二溴甲烷、二氯一　其他工作人员只能控制需要他们编辑的单元格．从而确保表单　

溴甲烷和三溴甲烷４种指标的监测值通过公式计算得出．具体　

安全。　

公式如下：“＝（三氯甲烷／０．ｏ６＋一氯二溴甲烷／ｏ．１＋二氯一溴甲　

８．美观性　

烷／０．０６＋三溴甲烷／０．１）”。　

使用不同的颜色区分填写项、选择项、自动计算项等，可以　

４．指标监测值自动判断　

方便工作人员使用表单。通过取消页面布局的网格线和标题．可　

使用ＩＦ（１ｏｇｉｃａｌ

＿

ｔｅｓｔ，［ｖａｌｕｅ＿ｉｆ＿ｔｍｅ］，［ｖａｌｕｅ＿ｉｆ　ｆｌａｓｅ］）函数判　

以使表单更加整齐和美观　

断指标是否检测以及监测值是否合格　

四、结束语　

（１）指标是否检测：所有指标需要先判断是否检测，如果没　Ｅｘｃｅｌ功能丰富、简单易学．使用Ｅｘｃｅｌ设计的表单不仅样式　

有填写监测值，则在合格与否一栏自动填写“未检测”；反之，判　

美观，通过使用函数、公式还可以实现自动计算、条件判断等功　

断指标监测值是否合格。具体实现如下：“＝ＩＦ（指标监测值＝．．．’，”　能，尤其适合包含大量数据项目的监测报告、调查表、实验报告　

未检测”，ＩＦ（判断指标监测值是否合格））。　

等。是日常表单设计的有力工具。　

（２）指标监测值是否合格：根据《生活饮用水卫生标准》（ＧＢ　

５７４９—２００６）中的指标限值，如果指标监测值符合标准规定。则在　

主要参考文献　

合格与否一栏自动填写“合格”；反之，填写“不合格”。如色度（铂　

钴色度单位）的指标限值为小于等于１，具体实现如下：“＝ＩＦ（色　

［１］宋翔．Ｅｘｃｅｌ公式与函数大辞典［Ｍ］．北京：人民邮电出版社。２０１０．　

度监测值＝…’，”未检测”，ＩＦ（色度监测值＞ｌ。”不合格”。”合　

［２］Ｅｘｃｅｌ　Ｈｏｍｅ．Ｅｘｃｅｌ应用大全［Ｍ］．北京：人民邮电出版社，２００８．　

格”））”。一般使用二重嵌套ＩＦ（）函数即可实现指标监测值合格　

［３］王红卫，自力军．Ｅｘｃｅｌ函数经典应用实例［Ｍ］．北京：清华大学出版　

与否的判断，但也有一些复杂的指标需要使用多重嵌套ＩＦ（）函　

杜．２００８．　

数方可实现自动判断。如一氯胺（ｍｇ／Ｌ）。出厂水的指标限值为小　

［４］何俊．Ｅｘｃｅｌ在市场调查工作中的应用［Ｍ］．北京：中国青年出版社，　

于等于３，末梢水的指标限值为小于０．５，具体实现如下：“＝ＩＦ（一　

２００６．　

氯胺监测值＝…－，”未检测”，ＩＦ（一氯胺监测值＞３，”不合格”，ＩＦ（监　

［５］姜黎莉，朱莹泽．Ｅｘｃｅｌ表格设计全能手册［Ｍ］．北京：中国铁道出版　

测类型＝”出厂水”，”合格”，ＩＦ（一氯胺监测值＞＝ｏ．５，”不合格”。ＩＦ　

社．２００９．　

［６］中华人民共和国卫生部，国家标准化管理委员会．中华人民共和国　

（监测类型＝”末梢水”。”合格”，”不合格”）））））”。　

国家标准：生活饮用水卫生标准（ＧＢ　５７４９—２ｏｏ６）［ｓ］．２００６．