У меня есть 2 ячейки, в которых есть списки чисел, разделенных запятыми (,). Список в K - это полный набор, список в D - частичный набор. Я хотел бы вставить в M ту часть K, которой нет в D.
пример:
K4 = 1,2,5,6
D4 = 1,5,6
Результат M4 = 2
Я использовал SUBSTITUTE, но это работает только тогда, когда числа в D в порядке и ничего не пропущено из середины K.
Мне нужен ответ, не относящийся к VBA, пожалуйста.
Вот формула, которая будет работать для одного пропущенного значения. Если вам нужно вернуть более одного пропущенного значения, можно использовать вспомогательные столбцы, но VBA будет намного проще. Формула обычно вводится и должна работать с большинством версий Excel:
=LOOKUP(2,1/ISERR(SEARCH(TRIM(MID(SUBSTITUTE(K4,",",REPT(" ",99)),seq_99,99)),D4 & ",")),TRIM(MID(SUBSTITUTE(K4,",",REPT(" ",99)),seq_99,99)))
seq_99 - это определенное Имя (в данном случае формула), которое генерирует массив значений {1,99,198,297, ...}
seq_99 Refers to: =IF(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))=1,1,(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))-1)*99)
Для подпрограммы VBA, особенно рекомендуемой, если может отсутствовать более одного элемента, попробуйте следующее:
Option Explicit
Function MissingValues(sFull As String, sPartial As String) As String
Dim RE As Object
Dim sPat As String
Dim S As String
'Replace commas with pipes, surround by brackets "[]" and follow by end
' of line to create regex pattern from sPartial
sPat = "[" & Replace(sPartial, ",", "|") & "](?:,|$)"
Set RE = CreateObject("vbscript.regexp")
With RE
.Pattern = sPat
.ignorecase = True
.Global = True
.MultiLine = True
S = .Replace(sFull, "")
.Pattern = ",$"
MissingValues = .Replace(S, "")
End With
End Function
Подпрограмма использует регулярное выражение для удаления всего в полном наборе, которое появляется в частичном наборе. (И затем последняя проверка, чтобы удалить любую терминальную запятую). Это вернет несколько пропущенных значений в одном и том же шаблоне, разделенном запятыми. Он нечувствителен к регистру, но вы можете увидеть, где это можно легко изменить.
Вот снимок экрана, на котором сравниваются выходные данные двух методов при двух пропущенных значениях:
Если у вас есть подписка на Office 365 Excel, вы можете использовать эту формулу массива:
=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH("," &TRIM(MID(SUBSTITUTE(K4,",",REPT(" ",999)),(ROW(INDIRECT("1:" & LEN(K4)-LEN(SUBSTITUTE(K4,",",""))+1))-1)*999+1,999))&",",","&D4&",")),"",TRIM(MID(SUBSTITUTE(K4,",",REPT(" ",999)),(ROW(INDIRECT("1:" & LEN(K4)-LEN(SUBSTITUTE(K4,",",""))+1))-1)*999+1,999))))
Поскольку это формула массива, ее необходимо подтвердить с помощью Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования. Если все сделано правильно, Excel поместит {} вокруг формулы.
Я понимаю, что вы просили ответа не vba, но;
Если у вас нет подписки на Office 365 Excel, вы можете поместить этот код в модуль, прикрепленный к книге, и использовать формулу, как описано выше.
Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
Dim d As Long
Dim c As Long
Dim arr2()
Dim t As Long, y As Long
t = -1
y = -1
If TypeName(arr) = "Range" Then
arr2 = arr.Value
Else
arr2 = arr
End If
On Error Resume Next
t = UBound(arr2, 2)
y = UBound(arr2, 1)
On Error GoTo 0
If t >= 0 And y >= 0 Then
For c = LBound(arr2, 1) To UBound(arr2, 1)
For d = LBound(arr2, 1) To UBound(arr2, 2)
If arr2(c, d) <> "" Or Not skipblank Then
TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
End If
Next d
Next c
Else
For c = LBound(arr2) To UBound(arr2)
If arr2(c) <> "" Or Not skipblank Then
TEXTJOIN = TEXTJOIN & arr2(c) & delim
End If
Next c
End If
TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function
