Группировка частей одной ЯЧЕЙКИ с содержимым другой ЯЧЕЙКИ
Вот пример Excel того, что мне нужно решить, желательно с использованием кода VBA.
Ячейка A1 содержит Z002, Z003, Z004, Z006, Z007.
Ячейка B1 содержит Z200
Ячейка C1 содержит Z002, Z003, Z004, Z005, Z006, Z007, Z008.
B1 — единственное значение, определяющее значения A1.
Чтобы результат был истинным, все значения в ячейке A1 должны содержаться в ячейке C1.
Тогда результат ячейки D1 будет: Z200, Z005, Z008.
Перенести это в следующую строку, где это неверно.
Ячейка A2 содержит Z002, Z003, Z004, Z006, Z007.
Ячейка B2 содержит Z200
Ячейка C2 содержит Z002, Z003, Z005, Z006, Z007.
Результат ячейки D2: Z002, Z003, Z005, Z006, Z007.
Во втором примере все значения в A2 не содержатся в C2, поэтому результатом в D2 снова будет C2.
Мне нужна формула в столбце D, которая ссылается на значения в A, B и C, чтобы получить желаемый результат. Формула в D# может быть макросом как таковым: =Group(A#,B#,C#) Таким образом, D возвращает изменения, если A является частью группы в C, иначе просто верните C.
Заранее спасибо
mbart67
Я попробовал это,
Function Group(valuesA As String, valuesB As String, valuesC As String) As String
Dim arrA() As String
Dim arrB() As String
Dim arrC() As String
Dim Unmatched As String
Dim i As Integer, Found As Boolean
' Split each string by commas into arrays
arrA = Split(valuesA, ",")
arrB = Split(valuesB, ",")
arrC = Split(valuesC, ",")
' Check if all values in A are contained in C
For i = LBound(arrA) To UBound(arrA)
Found = False
Dim j As Integer
For j = LBound(arrC) To UBound(arrC)
If Trim(arrA(i)) = Trim(arrC(j)) Then
Found = True
Exit For
End If
Next j
' If a value in A is not found in C, return the entire C list
If Not Found Then
Group = valuesC
Exit Function
End If
Next i
' If all values in A are found in C, combine unmatched B and C
Unmatched = valuesB
' Check for values in C that are not in A
For i = LBound(arrC) To UBound(arrC)
Found = False
For j = LBound(arrA) To UBound(arrA)
If Trim(arrC(i)) = Trim(arrA(j)) Then
Found = True
Exit For
End If
Next j
' If not found in A, add to the unmatched list
If Not Found Then
If Len(Unmatched) > 0 Then
Unmatched = Unmatched & "," & arrC(i)
Else
Unmatched = arrC(i)
End If
End If
Next i
Group = Unmatched
End Function
Привет, mdmay74! Второй пример должен был показать, что когда ВСЕ значения в A2 НЕ содержатся в C2, конечный результат остается как C2. Значение в B является более коротким единственным значением того, что содержится в A. Следовательно, почему в первом примере, где ВСЕ значения в A1 содержатся в C1, результатом является Z200 (который является единственной версией A1, как определено в B1). ),Z005,Z008 (Какие две строки в C1 не определены в A1). т.е. D1 = Z200,Z005,Z008, а во втором примере D2=C2, поскольку A2 НЕ является полным подмножеством C2. Надеюсь, это немного прояснит.
Ответы 3
В VBA это достаточно легко сделать, но я думаю, что это излишество. Я думаю, что это довольно легко решить с помощью формулы:
=IF(AND(ISNUMBER(MATCH(TEXTSPLIT(A2, ","), TEXTSPLIT(C2, ","), 0))),
TEXTJOIN(",", TRUE, B2, FILTER(TEXTSPLIT(C2, ","), ISNA(MATCH(TEXTSPLIT(C2, ","), TEXTSPLIT(A2, ","), 0)))),
C2)
Спасибо, Майкл, да, это работает именно так, как я хочу. Причина использования VBA заключается в том, что он совместим со старыми версиями EXCEL 2010 года.
@ mbart67 - см. мой другой ответ
Спасибо, только что понял, что вы также предоставили VBA, и это тоже отлично работает. ..
@ mbart67 - если вы считаете это полезным и работает в соответствии с вашими требованиями, примите ответ.
Решение VBA:
Function CheckValues(A As String, B As String, C As String) As String
Dim arrA() As String
Dim arrC() As String
Dim result As String
Dim allContained As Boolean
Dim i As Integer, j As Integer
Dim found As Boolean
arrA = Split(A, ",")
arrC = Split(C, ",")
allContained = True
' Check if all values in A are contained in C
For i = LBound(arrA) To UBound(arrA)
found = False
For j = LBound(arrC) To UBound(arrC)
If arrA(i) = arrC(j) Then
found = True
Exit For
End If
Next j
If Not found Then
allContained = False
Exit For
End If
Next i
If allContained Then
' If all values in A are contained in C, find values not in A
result = B
For j = LBound(arrC) To UBound(arrC)
found = False
For i = LBound(arrA) To UBound(arrA)
If arrC(j) = arrA(i) Then
found = True
Exit For
End If
Next i
If Not found Then
result = result & "," & arrC(j)
End If
Next j
Else
' If not all values in A are contained in C, return C
result = C
End If
CheckValues = result
End Function
Строка строки в зависимости от того, является ли список с разделителями подмножеством другого
Function GetGroupList( _
ByVal List As String, _
ByVal Sublist As String, _
ByVal ListTitle As String, _
Optional ByVal ListDelimiter As String = ",", _
Optional ByVal TitleDelimiter As String = ": ") _
As String
' Validate the inputs.
Dim Result As String
If Len(ListDelimiter) = 0 Then Result = vbNullString: GoTo WriteResult
If Len(ListTitle) = 0 Then Result = vbNullString: GoTo WriteResult
If Len(List) = 0 Then Result = vbNullString: GoTo WriteResult
If Len(Sublist) = 0 Then Result = List: GoTo WriteResult
If Len(List) < Len(Sublist) Then Result = List: GoTo WriteResult
' Split the lists into arrays.
Dim ArrSub() As String: ArrSub = Split(Sublist, ListDelimiter)
Dim ArrList() As String: ArrList = Split(List, ListDelimiter)
' Determine whether 'Sublist' is a subset of 'List'.
Dim IsSubset As Boolean
With Application
IsSubset = (.Count(.Match(ArrSub, ArrList, 0)) = .CountA(ArrSub))
End With
' Build the result.
Dim Indices() As Variant, i As Long
If IsSubset Then
Indices = Application.Match(ArrList, ArrSub, 0)
For i = 1 To UBound(Indices)
If IsError(Indices(i)) Then
Result = Result & ListDelimiter & ArrList(i - 1)
End If
Next i
If Len(Result) = 0 Then
Result = ListTitle
Else
Result = ListTitle & TitleDelimiter & _
Right(Result, Len(Result) - Len(ListDelimiter))
End If
Else
Result = List
End If
WriteResult:
GetGroupList = Result
End Function
Пытаясь понять, чего вы хотите: во втором примере вы говорите, что «все значения в A2 не содержатся в C2», но A2 и C2 содержат Z002, Z003, Z006 и Z007. Также ячейка B2, похоже, игнорируется, а ячейка B1 была включена. Можете ли вы отредактировать свой вопрос, чтобы уточнить, пожалуйста?