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

523
user152294

В результате я бы хотел получить столбец «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. Но, например, для ID 5 нет способа суммировать данные числа «600» и «300», чтобы получить «800».

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

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

0
Сколько повторений данного идентификатора может быть в столбце D? Bandersnatch 6 лет назад 0
Вы не сможете сделать это только с помощью формул рабочего листа, вам придется зацикливаться с использованием VBA, и даже в этом случае он будет очень быстро масштабироваться до большого количества итераций по мере роста числа чисел в идентификаторе. Для идентификатора `1` у вас есть 7 комбинаций, которые нужно учитывать, но добавление только одного дополнительного числа, как в идентификаторе` 9`, означает, что у вас 15 комбинаций, то есть экспоненциальный рост. Dan 6 лет назад 0
@Bandersnatch Я думаю, я бы ограничил его до 10 user152294 6 лет назад 0
@ user152294, проверьте мой отредактированный пост, теперь, используя метод кумулятивной суммы, я получил решение. Rajesh S 6 лет назад 0
Нет, снова не так. Bandersnatch 6 лет назад 0

2 ответа на вопрос

1
Bandersnatch

Поскольку у вас может быть до 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",""),"")

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

enter image description here

Я видел ответы с кодом VBA, где ключевые слова и комментарии окрашены в цвет. Кто-нибудь знает, как это происходит? Редактор Excel VBA показывает цвета, но как только вы вставляете код, он переходит на черно-белый. Bandersnatch 6 лет назад 0
@ Bandersnatch, причина проста, редактор VB также имеет схему Color Format, которая по умолчанию установлена ​​на AUTO. Если вы хотите изменить их. В окнах редактора нажмите «Инструмент», «Option», вы увидите, что диалоговое окно «Страница вкладки» теперь нажало «Формат редактора». Вы найдете поля Код цвета, Шрифт, Размер и Список настроек цвета. Выберите элемент из кода цвета, например, «Текст ключевого слова» и примените цвета переднего плана, фона и индикатора. Rajesh S 6 лет назад 0
В редакторе Excel VBA цвета отображаются правильно. Я спрашивал, как получить цвета в ответе SU. Bandersnatch 6 лет назад 0
@Bandersnatch Я думаю, что это реализация уценки здесь. См .: https://stackoverflow.com/editing-help#syntax-highlighting. Вот как будет выглядеть ваш ответ: https://i.imgur.com/35uIo2E.png user152294 6 лет назад 0
Привет, @ user152294. Эта ссылка была интересной, и [эта связанная ссылка] (https://meta.stackexchange.com/questions/72082/changes-to-syntax-highlighting), похоже, указывает на то, что система может «вывести», какой язык используется и выделите код соответственно. Я просто копирую / вставляю из редактора Excel VBA и обычно не получаю цвета, как этот ответ, но иногда получаю цвет, как в этом [другом ответе] (https://superuser.com/questions/1307146/duplicating-excel-buttons -с-относительная ссылка / 1307838? noredirect = 1 # comment1943590_1307838). Совершенно не очевидно, в чем разница. Bandersnatch 6 лет назад 0
@Bandersnatch Я считаю, что система может автоматически определять язык, только если в сообщении добавлены соответствующие теги. Поэтому, если бы я разместил VBA в своем исходном сообщении и пометил свой вопрос VBA, он бы отображал цвета. Но вы не можете сделать это в ответах, я думаю, поэтому вам придется вручную добавить эту строку ` ` в ваш ответ user152294 6 лет назад 0
@Bandersnatch Я только что добавил тег 'VBA' к своему вопросу. Похоже, ваш ответ теперь имеет цвета user152294 6 лет назад 1
-1
Rajesh S

Редакция:

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

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

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

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

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

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

@Bandersnatch, в столбце B значения записываются случайным образом. Если кто-то использует мою формулу с такими же значениями, в C6 появится YES, чего не должно! Я использовал SUMIF для вспомогательного столбца в B и получил 900 для B6, что логически правильно, и в этом случае C6 имеет пробел. В остальных ячейках C2, C3, C7 и C10 моя формула в обоих случаях выдает ДА ​​(как на скриншоте). Просто проверь это. :-) Rajesh S 6 лет назад 0
@Bandersnatch, теперь я исправил проблему с помощью метода накопительной суммы, и он работает правильно. Rajesh S 6 лет назад 0
Тем не менее, важно исследовать, с 300, 100, 700 моя формула также работает. Я протестировал его примерно с 15 общими комбинациями и только с 2, в С6 я получил Да. Хотя при изменении местоположения ваш UDF выдает ошибку #Value или Yes в C6, даже я изменил Range ("D: D") на новый Range. Надеюсь, скоро мы справимся, хорошего воскресенья :-) Rajesh S 6 лет назад 0
@Bandersnatch, я всегда стремлюсь учиться ,,, Эти данные использовались в недавнем прошлом с другим вопросом того же пользователя, а затем он ответил мне, что случайным образом не выбрано никакой логики, так что я применил SUMIF, и вы знаете, ,, Инновации всегда имеют много пути, и я должен предложить вам "ценить идеи мыслить нестандартно. Приятно провести время, скоро я вернусь сейчас Из страны по назначению с клиентом Rajesh S 6 лет назад 0
Если я введу 400 для идентификатора 10, то в следующей ячейке к нему моя Формула также выдаст «Да», если идентификатор 10 имеет значения 200 500 500 200, это то, что я сказал, что идея опа была случайными числами, и поэтому я попытался использовать SUMIF. ** Да, VBA может сделать это лучше, чем это сделал Кодекс ученика Гэри, **, как показывает ваш ответ. Rajesh S 6 лет назад 0

Похожие вопросы