如何计数excel中的元素 [英] How to count up elements in excel

问题描述

所以我有一个名为化学公式的列，对于40,000个条目，我想要做的是计算化学分子式中包含多少元素。例如： -

  EXACT_MASS公式
 626.491026 C40H66O5 
 275.173274 C13H25NO5 
  

为此，我需要一些返回

结果的公式

  CHO 
 40 66 5 
 13 25 5 
  

全部作为不同元素的单独列，以及不同条目的行。

解决方案

您可以制作自己的公式。

使用 ALT 和 F11 打开VBA编辑器并插入一个新模块。

Add

现在添加以下代码： Microsoft VBScript正则表达式5.5 / p>

 公共函数FormulaSplit（TheFormula As String，theLetter As String）As String 
 
 Dim RE As Object 
 Set RE = CreateObject（VBScript.RegExp）
使用RE 
 .Global = True 
 .MultiLine = False 
 .IgnoreCase = False 
。 =[AZ] {1} [az]？ 
结束于
 
 Dim Matches作为对象
设置Matches = RE.Execute（theFormula）
 
 Dim TheCollection作为集合
设置TheCollection =新集合
 
 Dim i As Integer 
 Dim Match As Object 
 For i =（Matches.Count  -  1）To 0步骤-1 
设置Match = Matches。项目（i）
 TheCollection.Add Mid（TheFormula，Match.FirstIndex +（Len（Match.Value）+1）），UCase（Trim（Match.Value））
 TheFormula = Left（TheFormula， Match.FirstIndex）
下一个
 
 FormulaSplit =未找到
出现错误时继续下一个
 FormulaSplit = TheCollection.Item（UCase（Trim（TheLetter）））
出现错误GoTo 0 
 
如果FormulaSplit =then 
 FormulaSplit =1
结束如果
 
设置RE = Nothing 
 Set Matches = Nothing 
设置Match = Nothing 
设置TheCollection = Nothing 
 
结束函数
  

用法：

• FormulaSplit C40H66O5，H）将返回66。


• FormulaSplit（C40H66O5，O）会返回5。


• FormulaSplit（C40H66O5，blah） li>
  

您可以直接在工作簿中使用此公式。

So I have a column called chemical formula for like 40,000 entries, and what I want to be able to do is count up how many elements are contained in the chemical formula. So for example:-

EXACT_MASS  FORMULA
626.491026  C40H66O5
275.173274  C13H25NO5

For this, I need some kind of formula that will return with the result of

C  H  O
40 66 5
13 25 5

all as separate columns for the different elements and in rows for the different entries. Is there a formula that can do this?

解决方案

You could make your own formula.

Open the VBA editor with ALT and F11 and insert a new module.

Add a reference to Microsoft VBScript Regular Expressions 5.5 by clicking Tools, then references.

Now add the following code:

Public Function FormulaSplit(theFormula As String, theLetter As String) As String

    Dim RE As Object
    Set RE = CreateObject("VBScript.RegExp")
    With RE
        .Global = True
        .MultiLine = False
        .IgnoreCase = False
        .Pattern = "[A-Z]{1}[a-z]?"
    End With

    Dim Matches As Object
    Set Matches = RE.Execute(theFormula)

    Dim TheCollection As Collection
    Set TheCollection = New Collection

    Dim i As Integer
    Dim Match As Object
    For i = (Matches.Count - 1) To 0 Step -1
        Set Match = Matches.Item(i)
        TheCollection.Add Mid(theFormula, Match.FirstIndex + (Len(Match.Value) + 1)), UCase(Trim(Match.Value))
        theFormula = Left(theFormula, Match.FirstIndex)
    Next

    FormulaSplit = "Not found"
    On Error Resume Next
    FormulaSplit = TheCollection.Item(UCase(Trim(theLetter)))
    On Error GoTo 0

    If FormulaSplit = "" Then
        FormulaSplit = "1"
    End If

    Set RE = Nothing
    Set Matches = Nothing
    Set Match = Nothing
    Set TheCollection = Nothing

End Function

Usage:

• FormulaSplit("C40H66O5", "H") would return 66.
• FormulaSplit("C40H66O5", "O") would return 5.
• FormulaSplit("C40H66O5", "blah") would return "Not found".

You can use this formula directly in your workbook.

这篇关于如何计数excel中的元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！