Проверка, совпадает ли сумма произвольного количества ячеек в столбце с ячейкой в другом

Question

В результате я бы хотел получить столбец «C»:

Чтобы сделать эту электронную таблицу проще для копирования / вставки в вашу собственную, вот данные для вставки:

ID
1
2
3
4
5
6
7
8
9

Number
1000
300


800
300


200

ID
1
1
1
2
2
5
5
6
9
9
9
9

Number
100
300
700
200
100
600
300
300
900
100
100
300

Я хочу сделать так, чтобы он мог проверить, суммирует ли любая из комбинаций чисел из каждого набора уникальных идентификаторов номер уникального идентификатора в столбце «А». Так, например, для идентификатора 1 это верно, потому что «700» и «300» из столбца «E» ID 1 - это возможный набор чисел, которые суммируют до 1000 в столбце B ID 1. Но, например, для идентификатора 5 нет способа суммировать данные числа «600» и «300», чтобы получить «800».

Я знаю, что могу использовать функцию COUNT для подсчета количества каждого идентификатора в столбце «D», но поскольку это может быть любое натуральное число, включая 0, я не могу найти способ сделать цикл IF/SUM который бы заканчивается, когда счетчик достигает минимума или максимума COUNT как в программировании. Должен ли я как-то создать подматрицу идентификаторов с соответствующими номерами и как-то начать цикл?

Я не уверен, ясно ли я объяснил свои мысли. Пожалуйста, попросите какие-либо разъяснения, необходимые. Благодарю.

Answer 1

Поскольку у вас может быть до 10 идентификационных номеров, а сумма может быть составлена из любого из соответствующих значений в столбце E (включая несмежные значения), Дэн прав: вам нужно решение VBA.

Эта пользовательская функция (UDF) многим обязана этому ответу от @ Gary's Student:

Function CheckSums(ID, TargetSum)
    Dim NumBits As Long, NumSums As Long, RngStart As Integer
    Dim Mask As String, i As Integer, j As Integer
    Dim MaskArray() As Integer
    Dim SumArray() As Integer
    Dim TestSum As Long

    NumBits = Application.WorksheetFunction.CountIf(Range("D:D"), ID)
    NumSums = 2 ^ NumBits - 1
    RngStart = Application.WorksheetFunction.Match(ID, Range("D:D"), 0)

    ReDim MaskArray(NumSums - 1)
    ReDim SumArray(NumSums - 1)

    For i = 1 To NumSums
        Mask = Application.WorksheetFunction.Dec2Bin(i, NumBits)
        For j = 0 To NumBits - 1
            MaskArray(j) = Mid(Mask, j + 1, 1)
            If MaskArray(j) = 0 Then
                SumArray(j) = 0
            Else
                SumArray(j) = Range("E" & RngStart + j)
            End If
        Next j
        TestSum = Application.WorksheetFunction.Sum(SumArray())
        If TestSum = TargetSum Then
            CheckSums = True
            Exit Function
        End If
     Next i

    CheckSums = False
End Function

Чтобы использовать эту функцию, вставьте ее в новый модуль VBA, как описано здесь.

Тогда эта формула, заполненная из C2:

=IF(B2<>"",IF(CheckSums(A2,B2),"Yes",""),"")

дает результаты, показанные на скриншоте ниже.

Answer 2

Редакция:

Может быть возможно решить эту проблему с накопленной суммой. Итак, я попробовал SUMIF() как показано ниже. Кроме того, обратите внимание, что я использовал данные, отличные от данных в вопросе ОП.

Эта формула в C2, дает показанные результаты.

=IF(B2<>"",IF(SUMIF($E$2:E2,E2,$B$2:B2)=B2,"Yes",""),"")

Но обратите внимание, что параметры для SUMIF() указываются как SUMIF(range,criteria,sum_range) .

Как видите, приведенная выше формула суммирует значения в столбце B, а не в столбце E, как указано в вопросе. Эта формула находит и "суммирует" целевое значение в столбце B и представляет его так, как если бы он рассчитал правильный ответ.

Ясно, что если вы хотите получить правильный, разумный ответ на свой вопрос, вам нужно искать в другом месте.