使用 Excel 计算分子量 [英] Calculating Molecular Weight Using Excel

问题描述

我在这里遇到了一些问题.我有一个包含大约 9,000 种有机化合物的电子表格，我正在尝试计算所有这些化合物的分子量.

I have come across a bit of problem here. I have a spreadsheet with about 9,000 organic compounds and I am trying to compute the molecular weight of all of them.

通常，这很容易:它只是分子式中元素的数量乘以元素的分子量，然后将它们全部加起来.问题是，电子表格将分子式列为字符串.

Normally, this would be easy: it's simply the number of elements in the molecular formula multiplied by the element's molecular weight and then you add them all up. The problem is, the spreadsheet has the molecular formulas listed out as a string.

例如，乙腈"的分子量在列中列为:C2H3N.

For example, the molecular weight for "acetonitrile" is listed in a column as: C2H3N.

我想做的是编写一个函数来扫描该单元格的内容并说:好吧，每次我遇到文本内容时，请查看紧随其后的数字，直到您点击另一个文本并然后停止.然后，取那个数字并乘以那个特定元素的分子量"(我稍后会处理分子量的总和，因为我觉得这是很容易的部分).

这可能与 Excel 的内置函数有关，还是我必须使用 VBA(我真的没有经验).任何帮助在这里将不胜感激.

Is this possible to do with Excel's built in functions, or do I have to use VBA (which I really don't have experience with). Any help here would be greatly appreciated.

推荐答案

虽然通过一些非常复杂(和 CPU 密集型)的公式只使用原生 Excel 函数，VBA 用户定义函数 或 UDF 会更合适.我不是化学家，所以请原谅我提供的单个样品中的添加物，因为它们是从 互联网页面.TBH，我什至不确定我有一半的术语是否正确.

While your request is marginally possible through some pretty complex (and CPU intensive) formulas using nothing but native Excel functions, a VBA User Defined Function or UDF would be vastly more appropriate. I'm not a chemist so please excuse the additions to your single sample I've provided as they were stolen shamelessly from an Internet page. TBH, I'm not even sure if I have half of the terminology correct.

步骤 1 - 创建一个分子量表并命名

您将需要某种形式的交叉引用来从元素的周期符号中检索分子量.这是我拼凑的.我将在下面的示例工作簿中提供指向完整数据表的链接.

You are going to require some form of cross-reference to retrieve the molecular weights from the element's periodic symbols. Here is what I scraped together. I'll supply a link to the full table of data in a sample workbook below.

在名为 Element Data 的工作表上，转到 Formulas ► Defined Names ► Name Manger 并为交叉引用矩阵指定一个定义的名称.

With that on a worksheet named Element Data, go to Formulas ► Defined Names ► Name Manger and give the cross-reference matrix a defined name.

这里我使用了一个公式 (=OFFSET('Element Data'!$A$1,0,0,COUNTA('Element Data'!$A:$A),6)) 来定义范围，但数据的大小是相当静态的，因此单元格范围引用应该绰绰有余.

Here I've used a formula (=OFFSET('Element Data'!$A$1,0,0,COUNTA( 'Element Data'!$A:$A),6)) to define the range but the size of the data is fairly static so a cell range reference should be more than sufficient.

第 2 步 - 添加用户定义函数的代码

点击Alt+F11，当VBE打开时，立即使用下拉菜单Insert ► Module(Alt+I+M).将以下内容粘贴到名为 Book1 - Module1 (Code) 的新窗格中.

Tap Alt+F11 and when the VBE opens, immediately use the pull-down menus to Insert ► Module (Alt+I+M). Paste the following into the new pane titled something like Book1 - Module1 (Code).

Public Function udf_Molecular_Weight(sCMPND As String) As Double
    Dim sTMP As String, i As Long, sEL As String, sSB As String
    Dim dAW As Double, dAWEIGHT As Double, dSUB As Long
    sTMP = sCMPND: dAWEIGHT = 0: sSB = "0": sEL = vbNullString
    Do While CBool(Len(sTMP))
        sSB = "0": sEL = vbNullString
        If Asc(Mid(sTMP, Application.Min(2, Len(sTMP)), 1)) > 96 Then
            sEL = Left(sTMP, 2)
        Else
            sEL = Left(sTMP, 1)
        End If
        sTMP = Right(sTMP, Len(sTMP) - Len(sEL))
        Do While IsNumeric(Left(sTMP, 1))
            sSB = sSB & Int(Left(sTMP, 1))
            sTMP = Right(sTMP, Len(sTMP) - 1)
        Loop
        'Debug.Print sEL & ":" & (Int(sSB) - (Not CBool(Int(sSB))))
        dAWEIGHT = dAWEIGHT + Application.VLookup(sEL, ThisWorkbook.Names("tblPeriodic").RefersToRange, 6, False) * (Int(sSB) - (Not CBool(Int(sSB))))
    Loop
    udf_Molecular_Weight = dAWEIGHT
End Function

Public Function udf_Styled_Formula_Alt(sCMPND As String) As String
    Dim sb As Long, sCOMPOUND As String
    sCOMPOUND = sCMPND
    For sb = 0 To 9
        sCOMPOUND = Replace(sCOMPOUND, sb, ChrW(8320 + sb))
    Next sb
    udf_Styled_Formula_Alt = sCOMPOUND
End Function

Public Function udf_Unstyled_Formula_Alt(sCMPND As String) As String
    Dim sb As Long, sCOMPOUND As String
    sCOMPOUND = sCMPND
    For sb = 0 To 9
        sCOMPOUND = Replace(sCOMPOUND, ChrW(8320 + sb), sb)
    Next sb
    udf_Unstyled_Formula_Alt = sCOMPOUND
End Function

只有第一个与您发布的问题有关.后两者使用 Unicode 下标字符风格化化合物的化学式，然后反转该过程.

Only the first of those is pertinent to your posted question. The latter two stylize the compound's chemical formula with Unicode subscript characters and reverse the process.

完成粘贴后，点击 Alt+Q 返回到您的工作表.这些 UDF 函数现在可以像任何本机 Excel 函数一样在您的工作簿中使用.语法尽可能简单.

When you have completed the paste, tap Alt+Q to return to your worksheet. These UDF functions can now be used within your workbook just as any native Excel function can. The syntax is as simple as I could muster.

=udf_Molecular_Weight(<带有纯文本复合式的单个单元格>)

=udf_Molecular_Weight(<single cell with compound formula in plain text>)

对于您的样品化合物(在上面的数据图像中)，这将是，

For your sample compound (in the data image above) this would be,

<代码>=udf_Molecular_Weight(B2)

...或者，

=udf_Molecular_Weight("C2H3N")

对于其中 9000 多个，我怀疑您会使用前一种方法.根据需要填写.虽然此 UDF 比使用 INDIRECT 和其他本机工作表函数的复杂 array 公式高效得多，但它们并不神奇.在提交 9000+ 之前在几百行上测试公式，以便您知道会发生什么.如果您选择使用其他两个 UDF，它们的工作方式大致相同.

With 9000+ of these, I suspect you'll use the former method. Fill down as necessary. While this UDF is vastly more efficient than convoluted array formulas using INDIRECT and other native worksheet functions, they are not magic. Test the formula on a few hundred rows before committing to the 9000+ so you know what to expect. The other two UDFs work in much the same fashion should you choose to put them to use.

简要说明:

'变量声明'，我猜你的意思是'变量赋值'.我倾向于编写相当紧凑的代码，并且通过用冒号堆叠变量的清零，我已经将其他人将最多 4 行代码放入一行.我转这个，

By 'variable declarations', I'm guessing you actually mean 'variable assignments'. I tend to write fairly tight code and I've taken what others would put into up to 4 code lines into a single line by stacking the zeroing of the variables with a colon. I turn this,

sTMP = sCMPND
dAWEIGHT = 0
sSB = "0"
sEL = vbNullString

...进入这个，

sTMP = sCMPND: dAWEIGHT = 0: sSB = "0": sEL = vbNullString

在重新进入循环之前需要重置变量，但这是一项平凡的任务，所以我只是将所有四个分配塞进一行.

The variables need to be reset before reentering the loops but it's a mundane task so I simply cram all four assignments into a single line.

两个Do While ... Loop 逐个字符地遍历传入函数的字符串.内部循环专门处理数字.每次通过循环都会从左边截断字符串，将其缩短一个或多个字符，并将这些字符收集为元素的符号或与其在有机化合物中的使用相关的数字.最终没有什么可以截断(长度 = 0)，这就是 CBool​​(Len(sTMP)) 变成 False 并且循环结束的地方.内部循环的执行方式大致相同，但收集数字直到没有长度或字母字符为止.收集到一个元素(和可能的数字修饰符)后，化合物中该元素的分子量将通过 VLOOKUP 根据分子量表计算出来，并添加到一个不断增长的数字中.当所有元素及其相关数字都已收集并添加到总计中时，总计将作为函数的结果返回.

The two Do While ... Loop crawl through the string that was passed into the function character by character. The inner loop deals exclusively with numbers. Each pass through the loop truncates the string from the left, shortening it by one or more characters and collecting those characters as either the symbol of a element or the number associated with its use in the organic compound. Eventually there is nothing left to truncate (length=0) and that is where CBool(Len(sTMP)) becomes False and the loop ends. The inner loop performs much the same way but collects numeric digits until it reaches no length or an alphabetic character. After an element (and a possible numeric modifier) has been collected, the molecular weight for that element within the compound is calculated with a VLOOKUP against the molecular weight table and added to a growing number. When all elements and their associated number has been gathered and added into the grand total, the total is returned as the result of the function.

这篇关于使用 Excel 计算分子量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！