需要在工作表之外的任何一个中找到值的出现，并为每个工作表返回A：1的值

3 回答

• 1

  如果您厌倦了公式方法，这里有一个VBA方法，应该按照您描述的方式进行 .

  • 它查看sheet1上的第1列以获取要搜索的单词列表

  • 将该列表读入vba数组（速度）

  • 对于列表中的每个项目，搜索每个工作表以查看该项目是否存在

  • 我将每个项目添加到一个Dictionary中，然后用逗号连接结果，但你也可以动态构造一个字符串，存储在数组的第二个"column"中

  • 完成所有操作后，我们将结果写回工作表 .

  • 它应该能够处理任何合理数量的工作表和搜索条件

  • 如有必要，您可以限制在每个工作表上搜索的范围;排除某些工作表被搜查;查看单元格中的部分匹配;选择区分大小写的搜索;等等

  • 如果第一个和最后一个搜索词之间有空白条目，我已排除搜索 .

  ---

  Option Explicit
  Sub FindAllColA()
      Dim WB As Workbook, WS As Worksheet
      Dim WS1 As Worksheet
      Dim D As Object
      Dim V
      Dim R As Range
      Dim FirstRow As Long, LastRow As Long
      Dim I As Long
  
  Set D = CreateObject("scripting.dictionary")
  Set WB = ThisWorkbook
  Set WS1 = WB.Worksheets("Sheet1")
  
  With WS1
      If .Cells(1, 1) <> "" Then
          FirstRow = 1
      Else
          FirstRow = .Cells(1, 1).End(xlDown).Row
      End If
  
      LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
  
      'V will hold both search terms and the results
      V = .Range(.Cells(FirstRow, 1), .Cells(LastRow, 1)).Resize(columnsize:=2)
  End With
  
  For I = 1 To UBound(V)
      If Not V(I, 1) = "" Then
          D.RemoveAll
          For Each WS In WB.Worksheets
              If Not WS.Name = WS1.Name Then
              With WS
                  If Not .Cells.Find(what:=V(I, 1), LookIn:=xlValues, _
                                  lookat:=xlWhole, MatchCase:=False) Is Nothing Then
                      D.Add .Cells(1, 1).Text, .Cells(1, 1).Text
                  End If
              End With
              End If
          Next WS
          V(I, 2) = Join(D.Keys, ",")
      Else
          V(I, 2) = ""
      End If
  Next I
  
  With WS1
      Set R = .Range(.Cells(FirstRow, 1), .Cells(LastRow, 2))
      R.EntireColumn.Clear
      R = V
      R.EntireColumn.AutoFit
  End With
  
  End Sub
  

  回复于 2024-04-29T22:39:10+08:00
• 0

  另一种方法是，UDF可以在更广泛的范围内使用而不需要任何改变，例如：

  Public Function ValString(search_term As String, cell_string As Variant, ParamArray ignored_sheets()) As Variant
  
    Dim x As Variant
  
    If TypeOf cell_string Is Range Then cell_string = cell_string.Address
  
    If Not TypeOf Evaluate(cell_string) Is Range Then
      ValString = CVErr(2023)
      Exit Function
    ElseIf Range(cell_string).Cells.Count > 1 Then
      ValString = CVErr(2023)
      Exit Function
    End If
  
    If IsMissing(ignored_sheets) Then
      ignored_sheets = Array(Application.Caller.Parent.Name)
    Else
      For x = 0 To UBound(ignored_sheets)
        If TypeOf ignored_sheets(x) Is Range Then
          ignored_sheets(x) = ignored_sheets(x).Parent.Name
        ElseIf TypeName(ignored_sheets(x)) = "String" Or IsNumeric(ignored_sheets(x)) Then
          ignored_sheets(x) = Format(ignored_sheets(x), "@")
        Else
          ignored_sheets(x) = ""
        End If
      Next
    End If
  
    For Each x In ThisWorkbook.Worksheets
      If IsError(Application.Match(x.Name, Array(ignored_sheets)(0), 0)) Then
        If Not x.Cells.Find(search_term, , -4163, 1, , , True) Is Nothing Then
          ValString = ValString & ", " & x.Range(cell_string).Value2
        End If
      End If
    Next
  
    If Len(ValString) Then
      ValString = Mid(ValString, 3)
    Else
      ValString = CVErr(2042)
    End If
  
  End Function
  

  将代码放在模块中，您可以像工作表中的常规公式一样使用它 .

  例：

  =ValString(A1,"A1")
  

  或者对于你的情况：

  =IFERROR(ValString(A1,"A1"),"Not Found")
  

  使用： ValString([search_term],[cell_string],{[ignored_sheet1],[ignored_sheet2],...})

  • [search_term]: 要查找的字符串

  • [cell_string]: 单元格的地址为ref或字符串，如果找到则要输出

  • [ignored_sheets]: （可选）工作表名称为字符串或要忽略的引用

  如果省略 [ignored_sheets] ，则将忽略具有公式的表单 . 要将所有工作表包含在工作簿中，只需将其设置为 ""
  如果没有找到任何内容，它将返回 #N/A! （这很好，因为您可以捕获它以设置您想要的任何输出而不更改代码）
  如果 [cell_string] 不是地址字符串和/或用于多个单元格，则它将返回 #REF! [ignored_sheets] 用作 =ValString(A1,"A1",Sheet1!A1,Sheet5!A1) 或 =ValString(A1,"A1","Sheet3","Sheet4","Sheet7","MyWhateverSheetName") 之类的列表 . 如果在ref-way中使用，您可以重命名工作表，它也将在公式中 . 如果有一个您不想检查的摘要表，这很好 . 但请记住：如果使用，还需要包含配方本身的纸张！

  如果您还有任何疑问，请询问;）

  回复于 2024-04-29T22:39:10+08:00
• 0

  试试这个UDF

  Function findKeywords(findMe As String) As String
  
      findKeywords = ""
  
      Dim sheetToSkip As String
      sheetToSkip = "Sheet1"
  
      Dim sht As Worksheet
      For Each sht In ActiveWorkbook.Sheets
  
          If sht.Name <> sheetToSkip And Len(findMe) > 0 Then    ' do not look for blank cells
  
              ' note:    LookAt:=xlWhole ... whole word       LookAt:=xlPart ... partial
  
              Dim aaa As Range
              Set aaa = sht.Cells.Find( _
                              What:=findMe, _
                              After:=sht.Cells(1, 1), _
                              LookIn:=xlValues, _
                              LookAt:=xlWhole, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlNext, _
                              MatchCase:=False, _
                              SearchFormat:=False)
  
              If Not aaa Is Nothing Then
                  If Len(findKeywords) = 0 Then
                      findKeywords = sht.Range("a1")
                  Else
                      findKeywords = findKeywords & ", " & sht.Range("a1")
                  End If
              End If
  
          End If
  
      Next sht
  
  '   If Len(findKeywords) = 0 Then findKeywords = "Not Found"     ' uncomment to return "Not Found" if desired
  '   Debug.Print findKeywords
  
  
  End Function
  

  回复于 2024-04-29T22:39:10+08:00