用好EXCEL函数提高工作效率.doc

(云南省环境监测中心站 云南 昆明 650034)摘 要:Excel 是目前处理数据使用最频繁的工具之一，其强大的函数库更是受到广大用户的喜爱，可满足绝大部分环境质量数据分析需求。利用 EXCEL 的引用功能，可方便快捷的完成大量重复计算的公式输入，尤其适用于不太熟悉 EXCEL 操作的人员学习使用。本文以对环境底泥数据的处理与分析为例，列举了使用 EXCEL 查找和引用函数以及单 元格引用的方法及优点。中图分类号：X830.3文献标识码：A文章编号：（G）01-0079（2012）02-26-04Microsoft Excel 是最优秀的电子表格软件之一，它在数据综合管理和分析方面具有功能强大、技术 先进、使用方便等特点1。EXCEL 强大的内置函数几 乎无所不能2。使用好 EXCEL 的内置函数可有效提 高数据处理效率，促进了环境信息化建设的发展。使用 Excel 的内置函数不仅可执行数学、文本或 逻辑运算，还可实现数据的多条件查询。使用 Excel 系统提供的函数，可提高工作效率、降低人为出错 率、减少工作表的内存占用。河床底泥监测是水环境监测的一部分,通过监测 可以了解到污染物的沉积、归宿与变化规律等。编制 环境质量报告需要汇总处理大量的底泥监测数据， EXCEL 强大的内置函数几乎可以满足底泥数据分析 的需求。一个右括号表示函数结束。参数是函数中最复杂的组成部分，它规定了函 数的运算对象、顺序或结构等。使得用户可以对某个 单元格或区域进行处理，如分析存款利息、确定成绩 名次、计算三角函数值等。 按照函数的来源，Excel 函数可以分为内置函数和扩展函数两大类。前者只 要启动了 Excel，用户就可以使用它们；而后者必须 通过单击“工具加载宏”菜单命令加载，然后才能 像内置函数那样使用。假如一个函数可以使用多个参数，那么参数与参数之间使用半角逗号进行分隔。参数可以是常量(数字和文本)、逻辑值(例如 TRUE 或FALSE)、数组、错误值(例如 #N/A)或单元格引用(例如 E1:H1)，甚至可以是另一个或几个函数等。参数的类 型和位置必须满足函数语法的要求，否则将返回错 误信息。函数与公式既有区别又互相联系。如果说前者 是 Excel 预先定义好的特殊公式，后者就是由用户自 行设计对工作表进行计算和处理的公式。以公式“= SUM(E1:H1)*A1+26”为例，它以等号“=”开始，包括函 数“SUM(E1:H1)”、对单元格 A1 的引用(使用其中存 储的数据)、运算符“*”和“+”和常量“26”。如果函数要以公式的形式出现，它必须有两个 组成部分，一个是函数名称前面的等号，另一个则是 函数本身。对 Excel 公式而言，函数是其中的主要组成部31. 函数应用基础Excel 函数是预先定义，可执行计算、分析等处理数据任务的特殊公式。以常用的求和函数 SUM 为 例，它的语法是：“SUM (number1,number2,)”。其中 “SUM”称为函数名称，一个函数只有唯一的一个名 称，它决定了函数的功能和用途。函数名称后紧跟左 括号，接着是用逗号分隔的称为参数的内容，最后用收稿日期：20120226作者简介：白爱民，工程师，现从事环境监测统计工作。(或工具栏)中的“fx”按钮，则表示公式开始的“=”出现在单元格和编辑栏，然后在打开的“插入函数”对话 框中的“选择函数”列表找到所需函数。如果你需要 的函数不在里面，可以打开“或选择类别”下拉列表 进行选择。最后单击“确定”按钮，打开“函数参数”对 话框。2. 编辑栏输入：如果你要套用某个现成公式， 或者输入一些嵌套关系复杂的公式，利用编辑栏输 入更加快捷。首先选中存放计算结果的单元格；鼠标 单击 Excel 编辑栏，按照公式的组成顺序依次输入各 个部分，公式输入完毕后，单击编辑栏中的“输入”(即 “”)按钮(或回车)即可。为了更好的理解本文中的实例，简单介绍一下EXCEL 的自定义序列。将经常要用到的有规律的数据定义成序列，则 可实现按该序列进行排序和在需要时按有规律数据 的填充方法自动填充。自定义序列的设置：（1）选择工具 选项菜单 命令，打开“选项”对话框。（2）单击“自定义序列”选 项卡，在“自定义序列”列表中选择“新序列”项。（3） 在“输入序列”文本框中输入要添加的新序列（如编 号,姓名,姓别,年龄,参工时间）。注意在新序列各 项间要输入半角符号的逗号加以分隔。（4）单击“添 加”按钮将输入的序列添加到“自定义序列”列表框 中。（5）单击“确定”按钮关闭对话框。使用方法：（1）排序：选择数据 排序菜单命 令，选定主要关键字后，打开“选项”对话框，将自定标移到控制柄上向需要填充的方向拖动即可按自定义系列的次序循环填充。2使用 EXCEL 内置函数的实例本文以地表水环境质量中的底泥数据处理为例，说明如何利用 EXCEL 提供的内置函数来处理和分析底泥数据。基本需求及步骤：1)汇总基础数据。2) 按监测点和监测元素分别进行排序。3) 按监测点对各监测元素分别与上一年度监测值进行比较，标明上升或下降，计算出上升或下降百分比。4) 按各监测元素，找出各监测元素最大值、最大上升幅度、最小值、最大下降幅度、总上升和总下降个数及相应百分比。5) 按各监测元素，找出各监测元素值较上年上升测点数和下降测点数及相应比值、最大值、最大上升幅度、最小值、最大下降幅度对应的监测点。为完成数据分析，按上述需求和步骤，先做第 1)和第 2) 项工作。要将不同年度的数据按测点和监测元素分别进行比较，就必须将数据按测点和监测元素分别排序在底泥分析时，测点和监测元素的位置有一定的要求和规律性，将测点数据和监测元素数据分别定义表 1底泥数据分析表mg/L元素 1元素 2监测点2009 年2008 年升 / 降升降幅度2009 年2008 年升 / 降升降幅度监测1监测2 监测3 监测4 监测5 监测6 监测7 监测8 监测9 监测103.197.679.455.5213.577.7172.924.129.08103.314.835.5721.5010.6211.5811.5934.603.8311.7162.44du d d u d u u d u- 33.9%33.5%- 56.0%- 43.0%17.2%- 33.4%110.7%7.6%- 22.5%65.5%56.8824.09172.0013.8740.2325.2024.7532.0057.4069.3067.8031.61141.0026.9341.4023.0624.1329.50106.2081.90dd u d d u u u d d- 16.1%- 23.8%22.0%- 48.5%- 2.8%9.3%2.6%8.5%- 46.0%- 15.4%最大测值最小测值 最大升幅 最大降幅测点 10测点 1 测点 7 测点 3测点 3测点 4 测点 3 测点 4103.313.19110.7%-56.0%172.0%13.8722.0%-48.0%成两个新序列，在数据处理时，利用 EXCEL 的“自定义序列”排序功能，按行或列分别排序可迅速按需求 统一测点和监测元素相对位置。统一各年度的表格 格式后，再汇总成数据分析表就很容易了，完成后的 基础数据分析表如下图的非彩色部分。为确保数据的保密性，本文用测点 1、测点 2、来代表实际监测点名称，用元素 1、元素 2、 来代表各实际监测元素，如汞、砷、铅、。在例子 中只采用了 10 个测点和 2 个元素，以说明如何快速 输入函数，完成底泥数据的分析。为完成第 3) 项工作，在 D4 单元格(黄色)和 E4 单元格(橙色)中分别输入公式：“=IF(B4C4,u,d)” 和“=(B4-C4)/C4”，接着选中 D4 和 E4 单元格，执行 复制命令，再选择表中的所有空白单元格(上表中浅 绿色单元格，即 D5:E13 和 H4:I13)，然后执行粘贴命 令即可，见底泥数据分析表。其中，为使用表格显得简捷，用字母 u 代表监测 值较上年升高，字母 d 代表监测值较上年下降。最后两项工作，即基本需求及步骤的第 4)、第 5) 步，需在底泥数据分析续表中完成，其中，灰色区域 可使数据分析表与上表中元素列对应、方便查询，更 重要的是保证通过复制粘贴获得的公式能自动的正 确调整其引用单元格和区域；黄色和橙色单元格为 基础公式区域 (只能通过手工逐一输入)。在 B17 至 B25 单元格中输入公式：“=COUNTIF (D4:D13,u)”、“=COUNTIF (D4: D13,d)”、“=SUM (B16:B17)”、“=B16/B18”、“=B17/ B18”、“=MAX(B4:B13)”、“=MAX(E4:E13)”、“=MIN(B4: B13)”、“=MIN(E4:E13)”；在 C22 至 C25 单元格中输入公式：“=INDEX($A$4:$I$13,MATCH(B21,B$4:B$13,0),1)”、“=INDEX($A$4:$I$13,MATCH(B22, B$4: B$13,0),1)”、“=INDEX ($A $4: $I $13,MATCH (B23,E $4:E $13,0),1)”、“=INDEX ($A $4: $I $13,MATCH (B24,E $4:E $13,0),1)”。其中 C23 和 C25 可以通过分别复制 C22 和 C24来实现。各函数的作用为：COUNTIF：计算区域中满足给 定条件的单元格的个数；SUM：返回某一单元格区域中所有数字之和；MAX：返回一组值中的最大值；MIN：返回一组值中的最小值；INDEX：使用索引从引用或数组中选择值；MATCH：在引用或数组中查找 值。前四个函数很简单，使用也很普遍。INDEX 的引用形式返回特定行和列交叉处单元 格的引用。MATCH：返回在指定方式下与指定数值匹配的 数组（数组：用于建立可生成多个结果或可对在行和 列中排列的一组参数进行运算的单个公式。数组区 域共用一个公式；数组常量是用作参数的一组常 量。）中元素的相应位置。C22 包 括 有 MATCH 和 INDEX 两 个 函 数 ，而 MATCH 则套用于 INDEX 函数中。C22 公式的含义 是：先用 MATCH 函数找到基础数据(B$4:B$13)中元 素 1 的最大测值的相应位置，再据此查询出最大测 值对应的测点名。在 B17 至 B25 单元格中的公式采用的均为相对 引用，使用较简单。而在 C22 至 C25 单元格中采用了 相对引用、绝对引用和混和引用。其区别在于，采用 相对引用时，如果公式所在单元格的位置改变，引用 也随之改变。如果多行或多列地复制公式，引用会自 动调整。采用绝对引用时，如果公式所在单元格的位 置改变，绝对引用保持不变。如果多行或多列地复制 公式，绝对引用将不作调整。混合引用分为绝对列和整，而绝对引用不作调整。在进行数据处理和分析时，常常会在 EXCEL 表 中使用大量的函数和公式，充分利用 EXCEL 的引用 功能，可大大减少公式输入工作量，即只须输入一套 最基本的公式，再采用复制粘贴功能，就能完成全部 公式的输入，从而获得所需要的结果。要完成数据分析续表的其它部分，只须选中底 泥数据分析续表中元素 1 所属的各项公式，复制到 元素 2 所属的相应单元格即可。即复制 B17 至 B25 单元格的内容，粘贴到 F17 至 F25，复制 C22 至 C25 单元格的内容，粘贴到 G22 至 G25，则数据分析续表 的编制工作完成。在实际的数据分析中，监测点和监测元素较实 例多得多。只须调整各式公式中的数据起始行和结 束行的行标号，并将监测元素 1 所包括的已经完成 的公式复制到其它监测元素的相应的位置就可实现 全部计算。3. 结语停留在 EXCEL 的初级应用水平，远没有体现出 EXCEL 的优势。自定义序列排序和多条件查询在数据分析中使用的情况较多，而对数据的处理和分析时有大量的重复性工作要做(在不同的单元格输入同一公式)，用好 EXCEL 的“自定义序列”功能可迅速按特殊序列进行排序，熟悉内置函数和掌握各种引用方法可轻松的完成数据分析工作。总之，在做不同年度的数据比较分析时，熟练掌握 EXCEL 的这几项功能可大大减少工作量、降低出错率、提高工作效率。由于 EXCEL 易学易懂，即使是不熟悉软件编程语言且不太熟悉 EXCEL 的人员也能很快掌握这些功能。参考文献1 荣钦科技. EXCEL2003 在统计学中的应用M.北京：电子工业出版社，2005.2 Excel 研究组.Excel2007 函数与公式速查手册M.北京：电子工业出版社 2008.3 张迎新.EXCEL2003 函数应用完全手册M2004.!（上接第 3 页）周部长在第二次全国环保科技大会上强调，要积极 探索污染控制与质量改善兼顾的中国环境管理新模 式，以环境质量管理“倒逼”经济发展方式转变。2012 年，全省环境监测任务很重，各市州环保局要积极组 织完成好新修订环境空气质量标准的实施，特别 是开展 PM2.5 等新指标的监测和信息发布；省站牵头 做好“科学监测，大力推动环境监测技术规范、标准 贯彻落实”活动（简称“大贯标”活动）；加大全省环境 质量监测工作的检查监督等；国务院关于分类推进 事业单位改革的指导意见已经正式出台，分类推进 事业单位改革，是深入贯彻落实科学发展观、构建社 会主义和谐社会的必然要求，是推进政府职能转变、 建设服务型政府的重要举措