根据String内容分组（1次）下的每个字符串？ （EXCEL） [英] Categorize Each String under Group (1 time), based on String content? (Excel)

问题描述

复杂的问题...让我先解释一下，也许有一个更好的解决方案，而不是使用迭代计算：

（链接到工作簿）

图像显示示例（显示我正在使用的）

问题：

有4,000+个字符串，并希望将它们分类为预先确定的组（基于String的内容）。

1. 每个字符串只能分配给一个组。 （即55加仑鼓龙头将列在水龙头列下，因为它包含龙头一词。）




2. 一旦分类为组，字符串不会被分类到任何其他组。 （即55加仑鼓式水龙头一旦分类为龙头），就不会被归入鼓。




3. 只要分类，真的没有关系每个String所在的组。

注意：（我几乎发现了一个使用迭代计算的解决方案，但并不完全正常）。

解决方案：

我遇到问题的方式是：

1. 使用以下公式计算工作表中String（Column A）的重复次数：

    公式：= COUNTIF（$ E $ 2：$ IA $ 10000，A3）
     

   
   
   

   
   
   • 此公式在列C中。
   
   



2. 创建一个可以分类的公式基于字符串是否包含组字（即水龙头，啤酒，加仑，厨房等）的组中的字符串...和之前没有被使用（即C列，其中包含上述公式）。

    公式：= IF（C3 <1，IF（IFERROR（SEARCH（faucet，A3），0） 0，A3，），）
     




3. 在C列和每个单独的组列。

这种方法的问题是它将进行迭代计算，它将：

1. 将组下的字符串分类（但不会增加时间Dup'd field from 0 to 1）...

或

2. 将Times Dup'd字段从0增加到1 BUT将保持字符串不被分类到组列。

---

有关如何解决迭代计算问题的任何建议？
（我知道它保持因为它是依赖的来回计算，所以将不得不解决一个正确的解决方案...我想知道是否有任何方法来创建一些一种'块'，所以它只能以一种方式计算...）

任何帮助将不胜感激！

解决方案

通过数据运行此过程。它执行一对变量数组中的所有处理。

  Sub byGroup（）
 Dim g As Long，s As Long，aSTRs As Variant，aGRPs As Variant 
 
 appTGGL bTGGL：= False 
 
与Worksheets（Sheet1）
 aSTRs = .Range（.Cells（2 （1），.Cells（Rows.Count，1）.End（xlUp））。 ）.Offset（0，Application.Match（zzz，.Rows（1）） -  1））
 .Resize（.Rows.Count，.Columns.Count）.Offset（1，0）.ClearContents 
 aGRPs = .Cells.Value2 
结束
 
对于s = LBound（aSTRs，1）到UBound（aSTRs，1）
对于g = LBound（aGRP ，2）到UBound（aGRP，2）
如果CBool​​（InStr（1，aSTRs（s，1），aGRPs（1，g），vbTextCompare））然后
 aGRPs（s + 1，g ）= aSTRs（s，1）
退出
结束如果
下一步g 
下一步s 
 
 .Cells（1，5）.R esize（UBound（aGRP，1），UBound（aGRP，2））= aGRPs 
 
结束
 
 appTGGL 
 End Sub 
 
 Public Sub appTGGL（可选bTGGL As Boolean = True）
 Debug.Print Timer 
 Application.ScreenUpdating = bTGGL 
 Application.EnableEvents = bTGGL 
 Application.DisplayAlerts = bTGGL 
 Application.Calculation = IIf（bTGGL，xlCalculationAutomatic，xlCalculationManual）
 End Sub 
  

已用时间（不包括您的工作表公式重新计算应在1-2秒范围内。

匹配组的优先级从左到右。如果您认为55加仑鼓应该分组为鼓而不是加仑，那么确保鼓在第1行加仑之前。 / p>

将新的启用宏的工作簿另存为Excel二进制工作簿（.XLSB）将工作簿文件大小减少一半。

Complicated problem... let me explain first, maybe there is a better solution rather than using iterative calculations:

(Link to Workbook)

Image showing example (to show what I'm working with)

PROBLEM:

Have 4,000+ Strings and would like to categorize them into pre-determined Groups (based on the String's content).

1. Each String should only be assigned to one Group. (ie. "55 gallon drum faucet" will be listed under "faucet" column, since it contains the word "faucet".)

2. Once categorized into a Group, the string won't be categorized under any other Groups. (ie. "55 gallon drum faucet" won't be categorized under "drum" once it's already been categorized under "faucet").

3. It really doesn't matter which Group each String does under, as long as it's categorized.

Note: (I've almost found a solution using iterative calculation, but it doesn't quite work).

SOLUTION:

The way I approached the problem was:

1. Count the number of times the String (Column A) was duplicated in the worksheet using the formula:

    Formula: =COUNTIF($E$2:$IA$10000,A3)
   

   • This formula goes in Column C.

2. Created a formula that will categorize a String underneath a Group based on whether the String contains the Group word (ie. "faucet", "beer", "gallon", "kitchen", etc)... AND has not been used before (ie. Column C, which contains the formula from above).

     Formula: =IF(C3<1,IF(IFERROR(SEARCH("faucet",A3),0)>0,A3,""),"")
   

3. Drag formula down for all 4,000 strings in Column C, and for each individual "Group" column.

The problem with this approach is that it will do an iterative calculation which will either:

1. Categorize the String under a Group (but WON'T increase the Times Dup'd field from 0 to 1)...

OR

2. Increase the "Times Dup'd" field from 0 to 1 BUT will keep the String from being categorized under the Groups column.

---

Any suggestions on how to address the iterative calculation issue? (I know it keeps calculating back and forth since it's dependent, so will have to settle with 1 "right" solution... I'm wondering if there's any way to create some sort of 'block' so it can only be calculated one way...)

Any help would be greatly appreciated!

解决方案

Run this procedure through your data. It performs all processing within a pair of variant arrays.

Sub byGroup()
    Dim g As Long, s As Long, aSTRs As Variant, aGRPs As Variant

    appTGGL bTGGL:=False

    With Worksheets("Sheet1")
        aSTRs = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Value2
        With .Range(.Cells(1, 5), .Cells(Rows.Count, 1).End(xlUp).Offset(0, Application.Match("zzz", .Rows(1)) - 1))
            .Resize(.Rows.Count, .Columns.Count).Offset(1, 0).ClearContents
            aGRPs = .Cells.Value2
        End With

        For s = LBound(aSTRs, 1) To UBound(aSTRs, 1)
            For g = LBound(aGRPs, 2) To UBound(aGRPs, 2)
                If CBool(InStr(1, aSTRs(s, 1), aGRPs(1, g), vbTextCompare)) Then
                    aGRPs(s + 1, g) = aSTRs(s, 1)
                    Exit For
                End If
            Next g
        Next s

        .Cells(1, 5).Resize(UBound(aGRPs, 1), UBound(aGRPs, 2)) = aGRPs

    End With

    appTGGL
End Sub

Public Sub appTGGL(Optional bTGGL As Boolean = True)
    Debug.Print Timer
    Application.ScreenUpdating = bTGGL
    Application.EnableEvents = bTGGL
    Application.DisplayAlerts = bTGGL
    Application.Calculation = IIf(bTGGL, xlCalculationAutomatic, xlCalculationManual)
End Sub

Elapsed time (excluding your worksheet formula recalculation should be in the 1-2 second range.

Priority of the matching groups is left to right. If you think '55 gallon drum' should be grouped as drum and not gallon then make sure that drum comes before gallon in row 1.

Saving the new macro-enabled workbook as an Excel Binary Workbook (.XLSB) reduces the workbook file size by roughly half.

这篇关于根据String内容分组（1次）下的每个字符串？ （EXCEL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！