找到相同的单词到不同的单元格 [英] find the same words into different cells

问题描述

 'book common user_id 
物理物理1 
等离子体物理学，等离子体原理2 
等离子体物理基础，等离子体，物理学基础3 
热力学基础原理4 
'
  

解决方案

所以这里是我的这个问题的镜头。我知道代码是相当凌乱的：我已经非常草率的变量名称，错误处理等等，但它让您了解如何做到这一点。我创建了一个UDF Common（），它需要4个参数：

• rngText ：一个单元格的引用，包含您想要的文本（在您的案例中）


• compareList：与第一个参数进行比较的单元格范围


• minOccurences（可选）：这是一个单词应该被视为常见的最小出现次数的定义。默认值为2


• excludeList（可选）：包含应排除文本的单元格范围（例如a，of，...）

所以例如，如果您的A2：A7中的标题和E2：E3中的排除列表，您可以使用公式 =单元格B2中的通用（A2，$ A $ 2：$ A $ 7，$ E $ 2：$ E $ 3）并复制到B7。

  Option Explicit 
 
函数公用（rngText As Range，compareList As Range，_ 
可选minOccurences As Integer = 2，可选的excludeList作为范围）作为变量
 
'检查是否提供排除列表
 Dim exclusionListProvided As Boolean 
如果不是（排除列表不是）然后
 exclusionListProvided = True 
 Else 
 exclusionListProvided = False 
 End If 
 
'检查argments 
 Dim returnError As Boolean 
如果IsDate（rngText.Value）或IsNumeric（ rngText.Value）或IsError（rngText.V alue）然后'第一个参数应该引用一个包含文本的单元格
 returnError = True 
 ElseIf minOccurences< 2然后'函数应该检查至少2次出现
 returnError = True 
 ElseIf（compareList.Columns.Count> 1和compareList.Rows.Count> 1）然后'compareList应该是一维的
 returnError = True 
 ElseIf exclusionListProvided Then 
如果（exclusionList.Columns.Count> 1和exclusionList.Rows.Count> 1）那么'exclusionList应该是一维的
 returnError = True 
 End If 
 Else 
 returnError = False 
 End If 
 
'如果其中一个参数是意外的返回错误
如果returnError然后
 Common = CVErr（xlErrValue）
 Else 
 Dim text As String 
 text = rngText.Value 
 
'将文本拆分为单词
 Dim words（）As String 
 words = fullSplit（text）
 
'将排除列表和compareList转换为数组
 Dim arrExclu de（）
如果excludeListProvided Then 
 arrExclude（）= rangeToStringArray（exclusionList）
如果
 Dim arrCompare（）
 arrCompare（）= rangeToStringArray（compareList）
 
 Dim strCommon As String 
'循环通过文本中的单词
 Dim i As Integer 
 Dim j As Integer 
 Dim k As Integer 
 Dim nOccurences As Integer 
 Dim excluded As Boolean 
 Dim compareWords（）As String 
 
 For i = LBound（words）To UBound（words）
'排除列表
 excluded = False 
如果exclusionListProvided Then 
对于j = LBound（arrExclude）到UBound（arrExclude）
 compareWords = fullSplit（arrExclude（j））
 k = LBound（compareWords）到UBound（compareWords）
如果compareWords（k）= words（i）Then 
 excluded = True 
退出
结束如果
下一个k 
如果被排除然后退出对于
下一个j 
如果
 
 '比较列表中的单词的出现次数
如果不排除然后
 nOccurences = 0 
对于j = LBound（arrCompare）到UBound（arrCompare）
 compareWords =如果LCase（compareWords（k））= LCase（words（i））然后
 nOccurences = nOccurences + 1 
退出
结束如果
下一个k 
下一个j 
如果nOccurences> = minOccurences然后
如果不是strCommon =然后
 strCommon = strCommon& ，
 End If 
 strCommon = strCommon& LCase（单词（i））
如果
结束If如果
下一个i 
 
 Common = strCommon 
如果
 
 
结束函数
 
'通过使用分隔符列表分割文本
函数fullSplit（text As Variant）
'定义分隔符列表
 Dim delimiters ）
 delimiters = Array（，，，。，;，？，！）
 
'唯一的分隔符是列表中的第一个
 Dim uniqueDelimiter As String 
 uniqueDelimiter =分隔符（0）
 
'用唯一的分隔符替换文本中的所有分隔符
 Dim i As Integer 
 For i = LBound（分隔符）+ 1到UBound（分隔符）
替换文本，分隔符（i），uniqueDelimiter 
下一个i 
 
'通过使用唯一的分隔符
 fullSplit = SplitText（text，uniqueDelimiter）
 
结束函数
 
'通过使用单个分隔符拆分文本
函数SplitText（text As变量，分隔符As String）
'分隔符分隔符每个出现的子字符串中的文本
 Dim tempArray（）As String 
 tempArray =拆分（文本，分隔符）
 
'删除空子字符串
 Dim LastNonEmpty As Integer 
 LastNonEmpty = -1 
 Dim i As Integer 
 For i = LBound（tempArray）To UBound（tempArray）
如果tempArray（i）<> 然后
 LastNonEmpty = LastNonEmpty + 1 
 tempArray（LastNonEmpty）= tempArray（i）
结束如果
下一个
 ReDim保存tempArray（0到LastNonEmpty）
 
 SplitText = tempArray 
结束函数
 
'检查两个数组是否共享至少一个元素
函数sharedElements（array1（）As Variant，array2（）As变量）As Boolean 
 Dim found As Boolean 
 found = False 
 
 Dim i As Integer 
 Dim j As Integer 
对于i = LBound（array1）对于UBound（array1）
对于j = LBound（array2）到UBound（array2）
如果array1（i）= array2（j）然后
 found = True 
 Exit For 
结束如果
下一个j 
如果找到= True然后退出对于
下一个i 
 
 sharedElements = found 
结束函数
 
'将范围转换为字符串数组，省略所有非文本单元格
函数rangeToStringArray（my范围作为范围）
 Dim myArray（）
 Dim arraySize As Integer 
 arraySize = 0 
 
 Dim c As Object 
对于每个c在myRange 
如果IsDate（c.Value）= False和IsNumeric（c.Value）= False和IsError（c.Value）= False然后
 ReDim保留myArray（arraySize）
 myArray（arraySize）= c .Value 
 arraySize = arraySize + 1 
 End If 
 Next 
 
 rangeToStringArray = myArray 
结束函数
  

find the common words in title of the books in Excel, the output like this :

   'book                              common                      user_id
    physics                           physics                         1
    Principles of plasma physics      physics,plasma                  2
    Fundamentals of plasma physics    fundamentals,plasma,physics     3
    Fundamentals of thermodynamics    fundamentals                    4
   '

解决方案

So here's my shot at this problem. I am aware that the code is rather messy: I've been very sloppy with variable names, error handling and so on, but it gives you an idea of how it can be done. I've created a UDF Common() which takes 4 arguments:

• rngText: a reference to a single cell containing the text (in your case book) you want to comare
• compareList: a range of cells with which to compare the first argument
• minOccurences (optional): this is the definition of the minimum number of occurences a word should have to be considered "common". The default vanue is 2
• exclusionList (optional): a range of cells containing text that should be excluded (e.g. words like "a", "of", ...)

So for example, if you have your titles in A2:A7 and your exclusion list in E2:E3, you could use the formula = Common( A2, $A$2:$A$7, , $E$2:$E$3 )in cell B2 and copy down to B7.

Option Explicit

Function Common(rngText As Range, compareList As Range, _
    Optional minOccurences As Integer = 2, Optional exclusionList As Range) As Variant

    'Check if an exclusion list is provided
    Dim exclusionListProvided As Boolean
    If Not (exclusionList Is Nothing) Then
        exclusionListProvided = True
    Else
        exclusionListProvided = False
    End If

    'Check the argments
    Dim returnError As Boolean
    If IsDate(rngText.Value) Or IsNumeric(rngText.Value) Or IsError(rngText.Value) Then 'first argument should refer to a cell containing text
       returnError = True
    ElseIf minOccurences < 2 Then   'Function should check for at least 2 occurences
        returnError = True
    ElseIf (compareList.Columns.Count > 1 And compareList.Rows.Count > 1) Then  'compareList should be one-dimensional
        returnError = True
    ElseIf exclusionListProvided Then
        If (exclusionList.Columns.Count > 1 And exclusionList.Rows.Count > 1) Then  'exclusionList should be one-dimensional
            returnError = True
        End If
    Else
        returnError = False
    End If

    'Return an error if one of the arguments is unexpected
    If returnError Then
        Common = CVErr(xlErrValue)
    Else
        Dim text As String
        text = rngText.Value

        'split text into an array of words
        Dim words() As String
        words = fullSplit(text)

        'convert exclusionlist and compareList to arrays
        Dim arrExclude()
        If exclusionListProvided Then
            arrExclude() = rangeToStringArray(exclusionList)
        End If
        Dim arrCompare()
        arrCompare() = rangeToStringArray(compareList)

        Dim strCommon As String
        'loop through words in text
        Dim i As Integer
        Dim j As Integer
        Dim k As Integer
        Dim nOccurences As Integer
        Dim excluded As Boolean
        Dim compareWords() As String

        For i = LBound(words) To UBound(words)
            'check if word is in exclusion list
            excluded = False
            If exclusionListProvided Then
                For j = LBound(arrExclude) To UBound(arrExclude)
                    compareWords = fullSplit(arrExclude(j))
                    For k = LBound(compareWords) To UBound(compareWords)
                        If compareWords(k) = words(i) Then
                            excluded = True
                            Exit For
                        End If
                    Next k
                    If excluded Then Exit For
                Next j
            End If

            'count the number of occurences of the word in the compare list
            If Not excluded Then
                nOccurences = 0
                For j = LBound(arrCompare) To UBound(arrCompare)
                    compareWords = fullSplit(arrCompare(j))
                    For k = LBound(compareWords) To UBound(compareWords)
                        If LCase(compareWords(k)) = LCase(words(i)) Then
                            nOccurences = nOccurences + 1
                            Exit For
                        End If
                    Next k
                Next j
                If nOccurences >= minOccurences Then
                    If Not strCommon = "" Then
                        strCommon = strCommon & ", "
                    End If
                    strCommon = strCommon & LCase(words(i))
                End If
            End If
        Next i

        Common = strCommon
    End If


End Function

'split text by using a list of delimiters
Function fullSplit(text As Variant)
    'define list of delimiters
    Dim delimiters()
    delimiters = Array(" ", ",", ".", ";", "?", "!")

    'unique delimiter is the first one from the list
    Dim uniqueDelimiter As String
    uniqueDelimiter = delimiters(0)

    'replace all delimiters in the text by the unique delimiter
    Dim i As Integer
    For i = LBound(delimiters) + 1 To UBound(delimiters)
        Replace text, delimiters(i), uniqueDelimiter
    Next i

    'split the text by using the unique delimiter
    fullSplit = SplitText(text, uniqueDelimiter)

End Function

'split text by using a single delimiter
Function SplitText(text As Variant, delimiter As String)
    'split the text in substrings on each occurence of the delimiter
    Dim tempArray() As String
    tempArray = Split(text, delimiter)

    'remove empty substrings
    Dim LastNonEmpty As Integer
    LastNonEmpty = -1
    Dim i As Integer
    For i = LBound(tempArray) To UBound(tempArray)
        If tempArray(i) <> "" Then
            LastNonEmpty = LastNonEmpty + 1
            tempArray(LastNonEmpty) = tempArray(i)
        End If
    Next
    ReDim Preserve tempArray(0 To LastNonEmpty)

    SplitText = tempArray
End Function

'check if two arrays share a least one element
Function sharedElements(array1() As Variant, array2() As Variant) As Boolean
    Dim found As Boolean
    found = False

    Dim i As Integer
    Dim j As Integer
    For i = LBound(array1) To UBound(array1)
        For j = LBound(array2) To UBound(array2)
            If array1(i) = array2(j) Then
                found = True
                Exit For
            End If
        Next j
        If found = True Then Exit For
    Next i

    sharedElements = found
End Function

'converts a range to an array of strings, omitting all non-text cells
Function rangeToStringArray(myRange As Range)
    Dim myArray()
    Dim arraySize As Integer
    arraySize = 0

    Dim c As Object
    For Each c In myRange
        If IsDate(c.Value) = False And IsNumeric(c.Value) = False And IsError(c.Value) = False Then
            ReDim Preserve myArray(arraySize)
            myArray(arraySize) = c.Value
            arraySize = arraySize + 1
        End If
    Next

    rangeToStringArray = myArray
End Function

这篇关于找到相同的单词到不同的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！