Excel если ячейка содержит то напиши в другой

Question

Таким образом, в основном мне нужно создать список количества уроков, выполненных студентами в одной ячейке, например (1,10,15,16). Максимальное количество уроков - 50. Урок распознается по его количеству.

Что я хочу сделать, это создать формулу, которая показывает все уроки, которые не были сделаны в другой ячейке. Я имею в виду: в одной ячейке я пишу уроки, которые учащийся выполнил для примера 1,5,7, а в другой ячейке результатом автоматически будут все числа до 50, кроме той, которая выполнена, поэтому ячейка будет 2,3,6,8....

Я пробовал с

=ISNUMBER(SEARCH(substring,text))

но это не дает мне хорошего результата.

Answer 1

Для этого нам нужно разделить строку на , а затем заменить эти значения ни на что.

Этот UDF делает то, что вы хотите:

Function LessonsLeft(rng As Range) As String
If rng.Count > 1 Then Exit Function
Dim spltStr() As String
Dim i As Long
spltStr = Split(rng.Value, ",")
LessonsLeft = ",1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,"
For i = LBound(spltStr) To UBound(spltStr)
    LessonsLeft = Replace(LessonsLeft, "," & spltStr(i) & ",", ",")
Next i
LessonsLeft = Mid(LessonsLeft, 2, Len(LessonsLeft) - 2)
End Function

Поместите его в модуль, прикрепленный к книге, тогда вы бы назвали его формулой:

=LessonsLeft(A1)

Answer 2

Это еще один вариант:

Function MissedLessons(str As String) As String
    Dim resultString As String
    Dim i As Integer

    str = "," & str & ","
    For i = 1 To 50
        If InStr(str, "," & i & ",") = 0 Then
            resultString = resultString & i & ","
        End If
    Next i  

    MissedLessons = Left(resultString, Len(resultString) - 1)
End Function

Answer 3

Небольшое улучшение решения Скотта:

• позволить пользователю при желании указать общее количество уроков (по умолчанию = 50)

• избегать ее иметь код всех строк чисел уроки

следующее:

Function LessonsLeft(rng As Range, Optional nLessons As Long = 50) As String
    If rng.count > 1 Then Exit Function
    Dim spltStr() As String
    Dim i As Long

    With CreateObject("Scripting.Dictionary")
        For i = 1 To nLessons
            .Add i, i
        Next
        spltStr = Split(rng.Value, ",")
        For i = LBound(spltStr) To UBound(spltStr)
            .Remove CLng(spltStr(i))
        Next
        LessonsLeft = Join(.keys, ",")
    End With
End Function