试验检测EXCEL使用技巧及实例

目前，EXCEL已广泛应用，由于其功能强大，使用方便，尤其在表格制作方面表现出优异的性能，所以在各行业工程建设中颇受欢迎，特别是表格和涉及计算图表较多的试验检测方面，很多检测公司都以EXCEL作为报告记录的模板。

本文操作步骤均源自EXCEL2007,其它版本的EXCEL可参照使用，若界面中找不到相应选项，请自行百度。考虑到有部分人仍然习惯2003版，故文所介绍函数均为2003版可用函数。。

如何生成指定范围的随机数

1、生成均匀分布的随机数

使用RAND()函数可以产生一个0～1之间的随机数，那么如何产生一个其它范围的，比如M～N之间的数呢？很简单，输入以下公式：

=RAND()*(M-N)+N

式中M,N代表任意数，也可以用单元格代替，并且可以M>N,也可以M

例如，在A1单元格中产生一个上下限分别为B1、C1单元格内容的随机数，那就在A1单元格中输入：

=RAND()*(B1-C1)+C1

2、 生成更接近下限的随机数

本条为使用技巧。

有些情况下，需要有更多接近于下限的数据。例如要生成在93-96范围内的随机数，使得生成的数接近93的概率更大。如何做到呢？只要在RAND函数的基础上稍微改动一下。

例如，在A1单元格中产生一个上下限分别为B1、B2单元格内容且更大概率接近下限B2的随机数，那就在A1单元格中输入：

=(RAND())^2*(B1-C1)+C1

3、生成固定间隔的随机数

有些情况下，需要生成一个范围内有固定间隔的随机数。生成M～N之间且间隔为P的随机数，公式为：

=ROUND((RAND()*(M-N)+N)/P,0)*P

例如要生成1～5之间且间隔为0.25的随机数，可在A1单元格输入：

=ROUND((RAND()*(5-1)+1)/0.25,0)*0.25

将一列内容随机排序并列出与之对应的内容

利用RANK和 INDEX函数组合并添加一列辅助列可以实现。

例如，在A2:A6单元格内输入姓名，B2:B6单元格内输入对应的编号，然后添加一列辅助列，在C2:C6单元格内输入随机函数=RAND()，

在D2单元格输入：

=INDEX($A$2:$A$6,RANK(C2,$C$2:$C$6))

在E2单元格输入：

=INDEX($B$2:$B$6,RANK(C2,$C$2:$C$6))

把D2和E2单元格往下拉即可。得到效果如下图：

注：

1、$A$2中$号的作用为固定单元格，使之在单元格往下拉时不变；

2、RANK(C2,$C$2:$C$6)的作用，生成C2单元格在$C$2:$C$6中大小的顺序，因为$C$2:$C$6为随机生成的数字，因此该顺序也为随机；

3、INDEX($A$2:$A$6,RANK(C2,$C$2:$C$6))的作用，根据RANK(C2,$C$2:$C$6)生成的序号，查找出数组$A$2:$A$6中该序号排位对应的姓名。

如何根据一组数据得到曲线方程

1、 线性方程（测力环曲线）

利用EXCEL自带的趋势线功能将一组数据进行线性回归。

例如，某测力环校准证书测试数据如下图：

点击插入->散点图->仅带数据标记的散点图，此时会弹出空白表格窗口。在该窗口任意处点击右键->选择数据->添加->X轴系列值->选择B3到B9单元格-> Y轴系列值->选择A3到A9单元格->确定->确定。

此时出现如下图的曲线:

注：该曲线非趋势线。对着该线点右键->添加趋势线，在“趋势预测/回归分析类型”中选择“线性”，下面勾选“显示公式”和“显示R平方值”。此时可以看见曲线图中显示出方程Y=7.2142X-7.1294，此方程中X表示百分表示值，单位mm，Y表示荷载，单位kN。如需使用其它单位，可自行换算。

2、 多项式（击实曲线自动判定最大干密度和最佳含水率）

前一节讲了如何根据一组已知数据得到线性回归的趋势线，这一节讲一讲如何得到多项式曲线并自动判定极值的办法。

得到多项式趋势线公式并不难，和线性公式步骤基本一致。但如需要自动判定曲线上的极值，则需使用LINEST函数和INDEX函数组合，INDEX前面已经介绍过用法，这里主要介绍LINEST的用法。

LINEST(known_y's,[known_x's], [const], [stats])

其中known_y's为需要回归的数组中的Y值；known_x's代表需要回归的数组中的X值；const为逻辑值，一般选择TRUE，用于指定不将常量 b 强制设为 0；stats为逻辑值，如果该值为TRUE，则返回一组数列，依次为多项式从最高次项到低次项直到常数项的系数，以及其它参数。由于该函数较为复杂，因此解释也不尽详细，只能粗略介绍。学习者可以结合实例自行体会。

利用LINEST得到多项式各系数的组合，再利用INDEX函数得到各系数分别为多少，就可以自动组合成多项式公式并计算所需值。

有了多项式公式，可以用笨办法，从含水率最少的土样含水率开始，按间隔0.1依次递增，计算干密度，将计算所得的所有干密度中最大值选出，就是最大干密度。

例如，有一组击实数据，如下图所示：

按照上条步骤，图表类型选择“仅带数据标记的散点图”，得到曲线图如下所示（实际计算结果与是否有曲线图无关）：

在单元格C1内输入：

=B2

在单元格C2内输入：

=C1+0.1

将C2往下拉，直至C80。得到10.1～18.0且间隔为0.1的80个含水率值。然后在D1单元格内输入：

=INDEX(LINEST($A$2:$A$6,$B$2:$B$6^,1,1),1,1)*C1^4+INDEX(LINEST($A$2:$A$6,$B$2:$B$6^,1,1),1,2)*C1^3+INDEX(LINEST($A$2:$A$6,$B$2:$B$6^,1,1),1,3)*C1^2+INDEX(LINEST($A$2:$A$6,$B$2:$B$6^,1,1),1,4)*C1+INDEX(LINEST($A$2:$A$6,$B$2:$B$6^,1,1),1,5)

注：此处INDEX(LINEST($A$2:$A$6,$B$2:$B$6^,1,1),1,1)表示拟合曲线方程中X的4次项系数，其中$A$2:$A$6为曲线已知点的Y坐标，$B$2:$B$6为曲线已知点的X坐标，^的作用是表示多项式最高次为4次，因为LINEST($A$2:$A$6,$B$2:$B$6^,1,1)给出的为一组数，因此需用INDEX函数调用该组数的第一个值，也就是最高次项的系数，后面其它各次项依此类推。

将D1往下拉，直至D80。得到对应各含水率的干密度。利用LARGE函数找出D1～D80单元格的干密度中最大值，在B8单元格内输入：

=LARGE(D1:D80,1)

然后利用INDEX和MATCH函数组合，找出B8单元格最大干密度对应的最佳含水率，在B9单元格内输入：

=INDEX(C1:C80,MATCH(B8,D1:D80,0))

最终结果如下图：

信息内容转载自网络，不代表本公众号观点

如有侵权请联系主编删除，谢谢合作

2019试验检测考试视频课件

关注我们

好看请点这里~