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

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

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

я пробовал с

=ISNUMBER(SEARCH(substring,text))

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


excel vba excel-2016
person Piotrek Natkaniec    schedule 13.02.2017    source источник
comment
Таким образом, одна ячейка будет напр. 1,2,3,4,5,6 и другие автоматически будут 7,8,9,10,12... до 50.   -  person Piotrek Natkaniec    schedule 13.02.2017
comment
Excel 2016, самый новый   -  person Piotrek Natkaniec    schedule 13.02.2017

Ответы (3)


arrow_upward
3
arrow_downward

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

Этот 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)

введите здесь описание изображения

person Scott Craner    schedule 13.02.2017
comment
Большое спасибо за ответ - person Piotrek Natkaniec; 14.02.2017

arrow_upward
3
arrow_downward

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

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
person CallumDA    schedule 13.02.2017
comment
Хороший, но мой сделает меньше петель 8П - person Scott Craner; 13.02.2017
comment
Ха-ха, я тоже заметил это прямо перед тем, как опубликовать, но подумал, что могу все же выложить это. Хороший ответ :) - person CallumDA; 13.02.2017

arrow_upward
2
arrow_downward

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

  • позволить пользователю дополнительно указать общее количество уроков (по умолчанию = 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
person user3598756    schedule 14.02.2017