其它教程（3）——使用Excel制作称料表格

（视频教程见 末）

一、背景简介

作为一名光荣的材（lian）料（dan）人，称料是每一个做实验的同学必不可少的基本技能。

基本的称料配比过程如下：

设计好实验，确定好要做的样品的名义成分，例如Zr0.5Hf0.5CoSb0.8Cn0.2，这里以单个样品举例。

根据相对原子质量以及原子数比例，确定每种元素的实际质量之比。

1）对于粉末称料，例如Bi2Te3和Mg3Sb2，我们只要确定好最终需要几克成品，就可以把每种元素对应的具体质量求出来了。

2）对于块体称料，由于不同元素的块体打磨难度不同，我们一般先将最难打磨（最硬的，需要用锯子锯或者锉刀砂纸磨起来很费劲的）的单质称取大致的质量，然后根据比例确定其他几种元素单质的质量。

其中23两步每次称料时都需要重复，我发现很多同学都会在配料的实验室拿个计算器每次称料都要算个半天，于是我通过Excel的函数功能编写了一个 件，能够较为快速地确定各元素配料质量，如图所示。

其中绿色部分是手动输入区，红色部分是自动输出区，Excel会通过单元格内写好的函数自动生成对应值。可以看到我们将配料成分输入至前两行之后，通过输入成品总质量/第一列元素单质的质量之后其他每种元素的质量就可以自动生成了。另外，根据单个原子的平均原子质量，我们在这里也可以直接把比热容的Dulong-Petit值给出来（表中单位为J/g/K）。

有了这个表之后，称粉末的同学直接将信息输入，截个图打印出来或者手抄一下就能去实验室配料了，也不需要每次再去查一下某元素的原子量，减少了很多重复操作。称块体的同学就麻烦一些，称完基体单质的质量后得在手机上进Excel填一下信息，下面的总质量2也会自动生成~

本次教程主要想分享一下这个表格是怎么做出来的，需要用到哪些函数，也希望大家基于这些Excel的基础知识能够编写出更多实用的 件，分享给其他同学，来提高今后的科研效率，减少在这些重复的机械操作上浪费的时间。

二、Excel基础

要制作这个Excel表格，主要需要掌握两块知识：

公式及自动填充，绝对引用和相对引用

VLOOKUP函数的用法

下面我们分别来讲一下这两块内容

1.Excel中公式的用法

1.1 基本公式

打开Excel，在单元格中输入“=1+1”，回车，单元格的内容便自动变成了“2”，因此平时打开Excel的时候也可以直接将其作为小计算器使用。这个基础功能相信大家早在初高中阶段都使用过，我就不过多赘述了。

1.2 公式的自动填充

如图所示，我们现在有某个样品的温度、电阻率和Seebeck系数的数据（ABC列），现在想要得到电导率和功率因子，那么我们在D2列中输入“=1/C2*1e-4”，在E2列中输入“=B2^2*D2*1e-5”，便可以分别算出第一个温度对应的电导率和功率因子了。（单位分别是1e4S/m和mW/m/K2）

选中D2和E2两个单元格，将鼠标移到E2右下角处，鼠标样式会变成一个黑色的十字架，此时双击左键或者是按住左键向下拖动，便将计算电导和PF的公式自动往下填充了，其他温度的对应数值也全都会显示出来。

这里会有一个问题，比如我们的D2中填入“=B2+C2”，然后往下拖动，D3中就会变成“=B3+C3”，那如果我们希望往下拖动的时候 第一个值一直是固定的B2，该如何操作呢？这时候就需要用到绝对引用功能。

1.3 绝对引用和相对引用

在Excel中，绝对引用功能通过符号“$”来实现。例如当我们在C1中输入“=A1”时，此时是相对引用，我们将公式往下拖一格会变成“=A2”，往右拖一格会变成“=B1”，此时公式中记录的是引用单元格和当前单元格的相对位置。而如果要记录绝对位置，我们可以把C1中的内容改成“=$A$1”，此时不管往哪个方向拖动，单元格公式中这个位置的对应值都不会改变，仍然是A1中的值，这就是公式中的绝对引用了。那么只在行坐标（1234）或列坐标（ABCD）前添加“$”符号，就意味着只固定引用单元格的行和列，大家可以自己输入拖动看一下效果。

2.VLOOKUP函数

我们先看一下Excel中VLOOKUP函数的简要介绍

也就是说，这个函数需要四个参数，为了便于理解，我直接简单举个例子：

图中红色区域是已有数据，G列是姓名，H列是分数，假设有很多很多数据，我们想快速知道其中几个人的分数，比如B和E的，那么我们在K列中输入待确认分数的姓名，L列中输入如图所示公式“=VLOOKUP(K5,$G$2:$H$6,2,0)”,此时单元格内就会显示匹配出来的结果了，第一个参数是姓名，第二个参数是在哪个区域内查找这个姓名（需确保待查找内容是这个区域中第一列里面的信息），第三个参数是要返回这个区域中查找到的姓名对应的第几列的信息，第四个参数是精确匹配（0）还是模糊匹配（1），一般情况下都是0。

此时我们会看到，为了能够使公式向下拖动但是待查找区域不会随之向下移动，需要将第二个参数进行绝对引用处理（如果只是向下拖动的话只固定行坐标即可，但后面编的 件也需要向右拖动处理，因此保险起见全部固定）。

更多用法和说明希望大家能够通过搜索引擎自行学习，那么以上我们已经将制作称料表格所需的Excel基础知识全部介绍完毕。

三、称料表格的制作

那其实学完VLOOKUP之后我们的思路已经很清晰了，只要在某个区域存放好所有元素对应的相对原子质量，再通过这个函数匹配，乘上对应的化学计量比，便可以得到材料中每个元素的实际质量比例了，再通过总质量或其中某个元素的质量，得到所有元素的质量就是小case啦~

首先，我们在Excel的Sheet1中输入所有元素的相关信息如下，并将Sheet1改名为“基本信息”（本表格中元素的信息来自浙江大学赵新兵教授整理的元素周期表，具体 件地址请在 末查看。）

接着，在Sheet2中，如图所示，确认好要输入的信息（元素名，化学计量，所需总质量/单个元素基体质量），以及输出的信息。

以第一个元素举例，在B3中输入“=IFERROR(VLOOKUP(B1,基本信息!$D$2:$E$84,2,0)*称料专用!B2,"")”，意思是在基本信息的Sheet中查找A2中的元素，返回对应的单位原子质量，乘上化学配比，对每个元素进行如此操作，第3行中的数字之比就是实际的质量之比了，那么第5行和第7行分别根据比例计算对应元素单质的实际质量即可。

这里再补充一下iferror的作用是:没有这个函数的话，当第一行中没有信息，第三行会返回错误#VALUE!，因为待查找区域中找不到空值，那使用iferror的话，不 错则显示第一个参数的值，如果第一个参数 错则显示第二个参数，此处我第二个参数使用两个双引号里面没有内容，也就是显示空值。

Dulong-Petit值就很简单了，直接使用公式3R/MM，其中MM是平均每个原子的相对质量，那么这里直接用第三行的总和除以第二行的总和即可，此处B8中填写的公式是“=IFERROR(3*8.314*SUM(B2:I2)/SUM(B3:I3),"")”，其中8.314是气体常数。

本篇教程 字描述比较多，可能没有玩过Excel的人理解起来会慢一点，因此大家可以在 “电声不语”后台留言“称料表格下载”获取 中所示的成品表格，还加了一个查询熔沸点的Sheet方便大家理解VLOOKUP函数，希望大家点开后能自己看一下每个单元格中的公式，结合本篇教程理解一下具体是怎么用的，当然，如果以后确保不会用到这些Excel知识的，直接把它当成一个小工具用也行~

视频教程如下：

其它教程（3）——使用Excel制作称料表格

微信扫码查看更多