巧用EXCEL—用公式及功能打造各种日常小工具

范鹏飞+曹峥+张苗

摘要：对于EXCEL，我们每个人都不陌生，几乎是我们日常工作中最常用的工具之一，但往往，对于其内置各种强大的功能，我们只是利用了很小一部分，实际上，很多公式，技巧，以及宏和VBA的制作可以帮我们大大的提升工作效率，下面我就给大家介绍几个EXCEL公式，及VBA简单的使用方法及应用。

关键词：EXCEL；公式；工具

1.重复性试验中的IF（ISERR（）及判断IF THEN公式的应用

在对标准器进行重复性试验时，我们通常是将重复性试验表格打印出来，填写记录再经过计算，对照数据做出判定。其实，我们完全可以应用EXCEL强大的内置公式等功能，将重复性试验编程EXCEL电子表格，只需要将重复性试验的数据填入，之后不需重复打开或查询计量标准档案，无论是重复性结果，还是重复性结果的判定，都会在瞬间完成，一步到位。只需填入数据后，点选打印，将结果直接放入档案即可。免除了重复查询，填写的繁琐工作。

首先，在重复性试验的EXCEL文件里，我们可以根据JJF1033-2016标准格式进行，并在经过贝塞尔公式计算重复性结果的单元格里输入标准偏差公式：“=IF（ISERR（STDEV（X：Y）），""，STDEV（X：Y））”，X，Y分别填入试验数据第一个和最后一个单元格的行列号，如B1：B10。与平常直接输入公式=STDEV（X：Y）不同，我们需要在前面加入“IF（ISERR（STDEV（X：Y）），""，”，因为在数据没有填写时，结果计算单元格通常会显示“#DIV/0！”字样，不利于文件的打印。我们在计算公式前添加上述语句，当数据为空时，结果计算单元格就会同样显示空白，而不会显示“#DIV/0！”字样，这也是平常工作中经常会遇到的问题。

在重复性试验判定结果单元格我们同样需要用到公式，即在判定结果单元格输入“=IF（ISERR（Y/X），""，IF（X<=Z，"合格"，"重新评定不确定度"））”，X填入重复性结果的单元格横列号，Y填入重復性试验中第一次数据的单元格横列号，Z填入技术报告中，当做不确定度分量引入重复性试验的数值（单次实验标准差）。同样在判定公式前加入IF（ISERR（））语句，即在重复性结果在没有数值的情况下，判定结果也显示为空，而不会显示“#DIV/0！”字样。同时，在表格打印区域外，找一处填写原重复性的数据，在重复性试验得出结果时，自动与原重复性相比较，如小于等于原重复性，判定结果显示合格，如大于原重复性，则显示重新评定不确定度。

同时，我们也可以在表格打印区域外，做两个公式，公式一：将重复性作为变量，其他评定的不确定度分量作为不变量，当重复性结果大于原重复性时，直接按照新的重复性所引入的不确定度分量计算出合成和扩展不确定度。公式二：将新计算出的扩展不确定度做为变量，其他作为不变量，做不确定度验证公式。这样，在输出重复性试验数据后，我们将在表格中迅速得知本次重复性试验结果所得到的所有结论，一目了然，不用重新翻阅技术报告，就可以得到所有结论。

2.规程一键查新功能HYPERLINK公式的应用

如何实现规程一键查新呢，我们可以选择两列，并分别命名为规程号和规程更新查询。在规程号列，输入属于对应计量标准的各个规程号编号，比如卡尺量具检定装置下所属规程JJG30-2012《通用卡尺》检定规程的查新，规程号列下输出JJG30即可。同时，在旁边规程更新查询列写入公式：=HYPERLINK（"https：//www.baidu.com/s？wd="&X，X），X为规程之前JJG30所在单元格，如JJG30所在单元格式A1，则在规程更新查询列B1格输出公式：=HYPERLINK（"https：//www.baidu.com/s？wd="&A1，A1），我们就可观察到，规程更新查询列出现了JJG30可点击的蓝色超级链接，只要一点击，就会自动打开浏览器，通过百度搜索JJG30规程的相关信息，我们很快就可以观察到是否有规程更新的信息。之后所有规程的查新都可以用此公式完成，即：在规程号列，输入JJGXXX，或JJFXXX（不要加后面年费）

这样，我们的规程查新功能就完成了，只需点击一下超级链，直接打开网页自动查询，方便快捷，不用你再一本一本找规程号，再在搜索引擎上按个查询了，只需要一次把所有规程号记录在基本库了，便可节省我们大部分的工作时间。

3.超期提醒功能

在进行计量标准器管理时，对于标准器周期送检日期是重要的一环，日常管理中，基本都会把日期写到一处，但由于信息繁多，周期并不统一，对于日期的管理并不方便。如何能使日期提醒一目了然，最方便的方法就是使送检日期随着实时日期通过颜色动态变化来呈现。比如，在超周期显示红色，1个月内到期显示黄色，其余不显示颜色。通过3种颜色的实时变化，使标准器送检周期变得一目了然，实时变化。那么，如何实现这样的功能呢，我们就需要依靠EXCEL其中一项功能：条件格式。

在EXCEL，选中溯源日期单元格后，在开始选项卡中，找到条件格式并点击，选择新建规则，开始创建我们的日期实时提醒功能。首先选择规则类型，选择使用公式来确定单元格，在下面输入我们需要的公式，比如超期显示红色，就输入=（L1+365）-TODAY（）<1式中，L1代表标准器的溯源日期的单元格，公式很容易理解，就是溯源日期加365天，再减去当前的日期，如何小于1，则符合单元格条件变化。输入公式栏右边选择公式的使用范围即可。之后选择下面的格式，选择填充选项卡，选择填充颜色：红色。这样，我们的第一个规则就创建完毕了。即：标准器有限期如已超过当前日期，则显示红色。

创建了第一个规则，那下面的规则我们也就很容易创建了，以同样的方法，再创建一个月内将到期的，提醒黄色的规则。同前面步骤一样，创建新规则。在公式中输入=（L1+365）-TODAY（）<30，选取适用范围和填充颜色，就完成了我们标准器周期到期动态显示的功能。之后，我们只需像下拉填充公式的做法一样：将鼠标移动至单元格的右下角，待其变为十字后，向下拉拽至你所需运用此条件格式的所有单元格即可。当然，如果有的标准器是半年周期，那么也很简单，因为之前通过填充公式的方法，已将溯源日期单元格默认为一年周期，所以我需要对半年周期的进行单个设置：选定要修改条件格式的单元格，在开始选项卡中找到清除，点选清除格式，再重新按照上面步骤进行条件设置，只是将365变成184，即可达到半年周期提醒的效果了。