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

下面是小编为大家整理的2023年使用Excel设计实现生活饮用水水质监测报告,供大家参考。

　与市地区下拉列表的实现方法类似用辅助表3代替辅助表2通过在其数据有效性的序列来源处输入公式offset辅助表3第2列左上角的位置match市或地区名称辅市或地区名称1即可生成包含对应的市或地区所辖的所有区或县名称的下拉列表

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

　［摘要］ 在日常工作中，经常使用监测报告、调查表、实验报告等表单存 储或收集专业数据，选取合适的表单设计工具非常重要。Excel 作为表单设计工 具，不仅可以设计出式样美观、格式规范的表单，其内嵌的函数、公式还可以实 现自动计算、条件判断、数据有效性验证、统计等功能。

　［关键词］ 生活饮用水；

　水质；

　监测报告；

　表单；

　设计工具；

　Excel；

　函数；

　公式 在日常工作中，经常使用监测报告、调查表、实验报告等表单存储或收集专 业数据，用于日后的统计分析、科学研究和决策支持。一份好的表单不仅要样式 美观，更要功能完备，以方便工作人员快速、准确地填写。因此，选取合适的表 单设计工具非常重要。

　Ｅｘｃｅｌ作为表单设计工具， 不仅可以设计出式样美观、 格式规范的表单，其内嵌的函数、公式和ＶＢＡ还可以实现计算、判断、筛选、 有效性验证、统计、抽取、汇总等功能，是一款功能丰富、易学易用的表单设计 工具，非常适合日常的表单设计工作。

　本文结合日常工作， 阐述如何使用Ｅｘｃｅｌ设计实现生活饮用水水质监测 报告。

　一、监测报告的内容

　生活饮用水水质监测报告共分为 3 部分：监测点基本信息、样品基本信息和 指标监测信息。

　监测点基本信息主要包括：

　监测点名称、 监测点编号、 所属省 （直 辖市） 、市（地区） 、区（县） 、行政区划代码、地址、联系人、电话、邮件、供 水类型、监测类型、监测频率等内容。样品基本信息包括：样品名称、样品编号、 采样员、采样时间、样品说明、备注等内容。指标监测信息包括：

　《生活饮用水 卫生标准》 （ＧＢ ５７４９－２００６）中规定的１０６项水质指标。报告格式 如图１所示。

　二、监测报告的设计要求

　（１） 省（直辖市） 、市（地区） 、区（县）三级关联：为了提高工作人员 的填写速度、减少手工填写错误，监测点所属省（直辖市） 、市（地区） 、区（县） 由工作人员通过下拉列表的方式进行选择，并实现省（直辖市） 、市（地区） 、区 （县）三级关联。

　（２） 当省（直辖市） 、市（地区） 、区（县）确定后，行政区划代码一栏 应根据国家行政区划代码表自动生成。

　（３） 个别指标监测值自动计算：１０６项卫生指标监测值大部分需要手 工填写， 个别指标则根据已填写的监测值通过公式计算得出，这部分指标监测值 应自动生成。

　（４） 指标监测值自动判断：由于卫生指标共有１０６项，数量多、内容 复杂、涉及专业面广，工作人员很难记住所有指标的标准限值。因此，当监测值 填写完毕后，合格与否一栏应根据《生活饮用水卫生标准》 （ＧＢ ５７４９－２ ００６）自动判断指标监测值是否合格。如果某些指标监测值没有填写，则自动 判断为未检测。

　（５） 统计要求：当指标填写完毕后，未检测项目总计、合格项目总计、 不合格项目总计自动计算生成。

　（６） 其他要求：为减少手工填写出现错误的概率，应尽可能使用下拉列 表或自动计算的方式进行填写。

　（７） 安全性：为了实现业务需求，往往需要使用大量的公式、函数、中 间过程表等。为了防止由于工作人员误删、误改而导致的错误，应对表单的操作 权限进行限制。

　（８） 美观性：表单设计应符合结构清晰、分类准确、整齐美观的要求。

　三、监测报告的实现方法

　为了实现上述需求，设计出式样美观、功能合理的表单，需要使用Ｅｘｃｅ ｌ中的公式、数据有效性、ＩＦ（）函数、ＣＯＵＮＴＩＦ（）函数、ＭＡＴＣ Ｈ（）函数、ＯＦＦＳＥＴ（）函数、ＴＯＤＡＹ（）函数、ＶＡＬＵＥ（）函 数，ＶＬＯＯＫＵＰ（）函数、工作表保护等。

　１． 省（直辖市） 、市（地区） 、区（县）三级关联 （１） 根据国家行政区划代码表，整理成 3 个辅助表：辅助表１：省（直 辖市）表；

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

　辅助表３：省（直辖市） —市（地区）—区（县）—代码表（参见图２） 。

　（３） 市（地区）下拉列表实现方法：由于市（地区）下拉列表要与已选 择的省或直辖市关联， 单独使用数据有效性无法实现，因此需要使用ＣＯＵＮＴ ＩＦ（ｒａｎｇｅ，ｃｒｉｔｅｒｉａ）函数、ＭＡＴＣＨ（ｌｏｏｋｕｐ＿ｖ ａｌｕｅ，ｌｏｏｋｕｐ＿ａｒｒｅａｙ， ［ｍａｔｃｈ＿ｔｙｐｅ］ ）函数、Ｏ ＦＦＳＥＴ（ｒｅｆｅｒｅｎｃｅ，ｒｏｗｓ，ｃｏｌｓ［ｈｅｉｇｈｔ］ ， ［ｗ ｅｉｇｈｔ］ ）函数配合数据有效性完成。通过在其数据有效性的序列来源处输 入公式“＝ＯＦＦＳＥＴ（辅助表２左上角的位置，ＭＡＴＣＨ（省或直辖市名 称，辅助表２中第１列的行数，０）－１，１，ＣＯＵＮＴＩＦ（辅助表２中第 １列， 省或直辖市名称） ，１）”，即可生成包含对应的省或直辖市所辖的所有 市或地区名称的下拉列表。其中，ＯＦＦＳＥＴ（）函数以辅助表２的最左上角 位置作为参照位置，使用ＣＯＵＮＴ（）函数和ＭＡＴＣＨ（）函数的返回值作 为参数， 通过偏移返回满足要求的单元格区域。ＣＯＵＮＴＩＦ（辅助表２中 第１列， 省或直辖市名称）函数判断某省或直辖市在辅助表２的第１列中的个 数。ＭＡＴＣＨ（省或直辖市名称，辅助表２中第１列的行数，０）函数判断某 省或直辖市在辅助表２的第１列中第一次出现的行数（参见图４） 。

　（４） 区（县）下拉列表实现方法：与市（地区）下拉列表的实现方法 类似， 用辅助表３代替辅助表２， 通过在其数据有效性的序列来源处输入公式“＝ ＯＦＦＳＥＴ（辅助表３第２列左上角的位置，ＭＡＴＣＨ（市或地区名称，辅 助表３中第２列的行数，０）－１，１，ＣＯＵＮＴＩＦ（辅助表３中第２列， 市或地区名称） ，１）”，即可生成包含对应的市或地区所辖的所有区或县名称的 下拉列表。

　２． 行政区划代码自动生成 使用ＶＬＯＯＫＵＰ （ｌｏｏｋｕｐ＿ｖａｌｕｅ，ｔａｂａｌｅ＿ａｒｒ ａｙ，ｃｏｌ＿ｉｎｄｅｘ＿ｎｕｍ， ［ｒａｎｇｅ＿ｌｏｏｋｕｐ］ ）函数实现 行政区划代码自动生成。具体实现如下：“＝ＶＬＯＯＫＵＰ（区或县名称，“辅 助表３的第３列和第４列”，２，ｆａｌｓｅ）”。根据区或县的名称，在辅助表 ３的第３列中进行精确查询， 并将与查询结果对应的第４列中的行政区划代码取 出。

　３． 指标监测值自动计算 三卤甲烷的监测值是通过三氯甲烷、一氯二溴甲烷、二氯一溴甲烷和三溴甲 烷 4 种指标的监测值通过公式计算得出， 具体公式如下：

　“＝ （三氯甲烷／０． ０ ６＋一氯二溴甲烷／０．１＋二氯一溴甲烷／０．０６＋三溴甲烷／０．１）”。

　４． 指标监测值自动判断 使用ＩＦ（ｌｏｇｉｃａｌ＿ｔｅｓｔ， ［ｖａｌｕｅ＿ｉｆ＿ｔｒｕｅ］ ， ［ｖａｌｕｅ＿ｉｆ＿ｆａｌｓｅ］ ）函数判断指标是否检测以及监测值是否合 格。

　（１） 指标是否检测：所有指标需要先判断是否检测，如果没有填写监测 值，则在合格与否一栏自动填写“未检测”；反之，判断指标监测值是否合格。具 体实现如下：“＝ＩＦ（指标监测值＝＂＂，＂未检测＂，ＩＦ（判断指标监测 值是否合格） ） 。

　（２） 指标监测值是否合格：根据《生活饮用水卫生标准》 （ＧＢ ５７４ ９－２００６）中的指标限值，如果指标监测值符合标准规定，则在合格与否一 栏自动填写“合格”；反之，填写“不合格”。如色度（铂钴色度单位）的指标限值 为小于等于１ ，具体实现如下：“＝ＩＦ（色度监测值＝＂＂，＂未检测＂，Ｉ Ｆ（色度监测值＞１，＂不合格＂，＂合格＂） ）”。一般使用二重嵌套ＩＦ（） 函数即可实现指标监测值合格与否的判断， 但也有一些复杂的指标需要使用多重 嵌套ＩＦ（）函数方可实现自动判断。如一氯胺（ｍｇ／Ｌ） ，出厂水的指标限 值为小于等于３，末梢水的指标限值为小于０．５，具体实现如下：“＝ＩＦ（一 氯胺监测值＝＂＂，＂未检测＂，ＩＦ（一氯胺监测值＞３，＂不合格＂，ＩＦ （监测类型＝＂出厂水＂，＂合格＂，ＩＦ（一氯胺监测值＞＝０．５，＂不合 格＂，ＩＦ（监测类型＝＂末梢水＂，＂合格＂，＂不合格＂） ） ） ） ）”。

　５． 统计汇总 使用ＣＯＵＮＴＩＦ（ｒａｎｇｅ，ｃｒｉｔｅｒｉａ）函数实现未检测项 目、合格项目、不合格项目的统计汇总。具体实现如下：检测项目总计＝１０６ －ＣＯＵＮＴＩＦ（统计区域，＂未检测＂） ，合格总计＝ＣＯＵＮＴＩＦ（统 计区域，＂合格＂） ，不合格总计＝ＣＯＵＮＴＩＦ（统计区域，＂合格＂） 。

　６． 其他 使用ＴＯＤＡＹ（）函数自动填写填表日期。使用数据有效性生成下拉列表 填写表单。如臭和味的监测值为无异臭味和有异臭味两种，使用数据有效性，在 序列来源处填写“无异臭味， 有异臭味”， 即可形成下拉列表由工作人员选择填写。

　７． 安全性 使用隐藏辅助表、 锁定非编辑单元格、保护工作表和设定安全密码相结合的 方式， 使得只有被授权的人员才可以修改表单，其他工作人员只能控制需要他们 编辑的单元格，从而确保表单安全。

　８． 美观性 使用不同的颜色区分填写项、选择项、自动计算项等，可以方便工作人员使 用表单。通过取消页面布局的网格线和标题，可以使表单更加整齐和美观。

　四、结束语

　Ｅｘｃｅｌ功能丰富、简单易学，使用 Excel 设计的表单不仅样式美观，通

　过使用函数、公式还可以实现自动计算、条件判断等功能，尤其适合包含大量数 据项目的监测报告、调查表、实验报告等，是日常表单设计的有力工具。

　主要参考文献

　［１］ 宋翔． Ｅｘｃｅｌ公式与函数大辞典［Ｍ］ ． 北京：人民邮电出版 社，２０１０． ［２］ Ｅｘｃｅｌ Ｈｏｍｅ． Ｅｘｃｅｌ应用大全［Ｍ］ ． 北京：人民 邮电出版社，２００８． ［３］ 王红卫，白力军． Ｅｘｃｅｌ函数经典应用实例［Ｍ］ ． 北京：清 华大学出版社，２００８． ［４］ 何俊． Ｅｘｃｅｌ在市场调查工作中的应用［Ｍ］ ． 北京：中国青 年出版社，２００６． ［５］ 姜黎莉， 朱莹泽． Ｅｘｃｅｌ表格设计全能手册［Ｍ］ ． 北京：

　中国铁道出版社，２００９． ［６］ 中华人民共和国卫生部，国家标准化管理委员会． 中华人民共和国 国家标准：

　生活饮用水卫生标准 （ＧＢ ５７４９－２００６） ［Ｓ］ ． ２００６．

　　

推荐访问:饮用水水质情况监测报告 生活饮用水 报告 水质监测