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

问题描述

我在这里遇到了一些问题。我有一个约9,000个有机化合物的电子表格，我试图计算所有这些的分子量。

通常情况下，这很简单：它只是分子式中元素的数量乘以元素的分子量，然后将其全部添加。问题是，电子表格的分子式列出为字符串。

例如，列出了乙腈的分子量在列中： C2H3N 。

我想要做的是编写一个扫描该单元格内容的功能， 好的，每当我遇到一些文字的时候，看看紧随其后的数字，直到你再打另一个文字，然后停下来，然后拿这个数字乘以那个特定元素的分子量（我会照顾分子量的总和后来因为我觉得这是容易的部分）。

这可能与Excel的内置函数有关，或者我必须使用VBA（我真的没有经验）。

解决方案

虽然您的请求有可能通过一些相当复杂（和CPU密集型）公式使用只有本机Excel函数，VBA 用户定义函数或 UDF 将更加合适。我不是化学家，所以请原谅我提供的单个样本的添加，因为他们被无耻地从

该链接将保持活动一段时间。如果我将其位置更改为更长久的存储空间，我将在此调整链接。

简要说明

由'变量声明' em>，我猜你实际上是指'变量赋值'。我倾向于编写相当严格的代码，我已经把别人最多可以将4个代码行放入一行，方法是用冒号重新排列变量。我转过来，

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

...进入这个，

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

IT行业最糟糕的失误之一是会计师决定向编程人员支付他们写的每一行代码。

在重新进入循环之前，这些变量需要重新设置，但它是一个平凡的任务，所以我只需将所有四个任务填充到一行。

两个 Do While ...循环通过逐个字符传递给函数的字符串进行爬网。内循环专门处理数字。每个遍历循环将从左侧截取字符串，将其缩短一个或多个字符，并将这些字符作为元素的符号或与其在有机化合物中的使用相关联的数字进行收集。最终没有什么可以截断（length = 0），而这就是 CBool​​（Len（sTMP））成为 False ，循环结束。内循环以相同的方式执行，但收集数字直到达到无长度或字母字符为止。在收集了元素（和可能的数字修饰符）之后，化合物中该元素的分子量用分子量表的 VLOOKUP 计算，并添加到越来越多。当所有元素及其相关号码已经收集并添加到总计中时，总计将作为函数的结果返回。

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.

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

What I would like to do is write a function that scans that cell's contents and says, "Okay, every time I come across something that is text, look at the numbers immediately following it until you hit another text and then stop. Then, take that number and multiply by that particular element's molecular weight" (I will take care of the summation of the molecular weights later because I feel that is is the easy part).

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.

解决方案

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.

     

Step 1 - Create a table of molecular weights and name it

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.

     

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

     

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.

Step 2 - Add the code for a User Defined Function

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

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.

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(<single cell with compound formula in plain text>)

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

=udf_Molecular_Weight(B2)

... or,

=udf_Molecular_Weight("C2H3N")

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.

As promised, here is a link to the sample .XLSB workbook I created for this purpose for you to download and reference.

    Chemical_Compound_Atomic_Weights.xlsb

That link will remain active for a while. If I change its location to more permanent storage, I will adjust the link here.

BRIEF EXPLANATION:

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

... into this,

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

One of the worst mistakes ever made in the IT industry was an accountant's decision to pay programmers for every line of code they wrote.

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.

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屋！