Найти все комбинации чисел, которые равны заданной сумме в Excel

3 способа найти все комбинации из набора чисел, которые в сумме дают определенную сумму.

Поиск комбинаций значений, которые составляют заданную сумму, является распространенной проблемой при анализе данных. Например, вы можете захотеть узнать все возможные комбинации предметов, которые можно приобрести с заданным бюджетом, или все возможные способы распределения ресурсов для удовлетворения определенных требований. В этой статье мы рассмотрим, как использовать Excel Solver и VBA для выполнения задачи. Мы также обсудим некоторые потенциальные ловушки и ограничения каждого подхода, которые могут возникнуть при работе с большими наборами данных или сложными ограничениями.

Найдите комбинацию чисел, равную заданной сумме, с помощью Excel Solver

К сожалению, ни одна из встроенных функций Excel не может помочь вам определить числа, которые в сумме составляют заданную сумму. К счастью, в Excel есть специальная надстройка для решения задач линейного программирования. Надстройка «Поиск решения» включена во все версии Excel, но не включена по умолчанию. Если вы не знакомы с этим инструментом, вот отличная статья о том, как добавить и использовать Solver в Excel.

Активировав надстройку Solver в Excel, выполните следующие действия:

1. Создайте модель.

   Для этого введите свой набор чисел в один столбец (в нашем случае A3:A12) и добавьте пустой столбец справа от ваших чисел для результатов (B3:B12). В отдельной ячейке (B13) введите формулу СУММПРОИЗВ, подобную этой:

   =СУММПРОИЗВ(A3:A12, B3:B12)
   

2. Запустите надстройку Solver.

   На вкладке Данные в группе Анализ нажмите кнопку Решатель.
   

3. Определите проблему для Решателя.

   В диалоговом окне Solver Parameters настройте целевые и переменные ячейки:

   • В поле Установить цель введите адрес ячейки формулы (B13).
   • В разделе «Кому» выберите «Значение» и введите желаемое значение суммы (50 в данном примере).
   • В поле «Изменяя ячейки переменных» выберите диапазон, который будет заполнен результатами (B3:B12).

   

4. Добавьте ограничения.

   Чтобы указать ограничения, т.е. ограничения или условия, которые должны быть соблюдены, нажмите кнопку Добавить. В диалоговом окне «Добавить ограничение» выберите результирующий диапазон (B3:B12) и выберите интервал из раскрывающегося списка. Ограничение будет автоматически установлено как двоичное. Когда закончите, нажмите OK.
   

5. Решать проблему.

   Вернувшись в диалоговое окно Solver Parameter, проверьте свои настройки и нажмите кнопку Solve.
   

   Через несколько секунд (или минут) появится диалоговое окно Solver Results. В случае успеха выберите опцию Keep Solver Solution и нажмите OK, чтобы выйти из диалогового окна.
   

В результате у вас будет вставлена ​​1 рядом с числами, которые в сумме составляют указанную сумму. Не удобное решение, но это лучшее, что может сделать Excel из коробки.

Для наглядности я выделил ячейки, дающие нужную сумму, светло-зеленым цветом:

Ограничение: Excel Solver может найти не более одной комбинации чисел, равной определенной сумме.

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

Чтобы получить все возможные комбинации из заданного набора чисел, которые в сумме дают определенное значение, вы можете использовать пользовательскую функцию ниже. Если вы новичок в UDF, вы найдете много полезной информации в этом руководстве: Как создавать пользовательские функции в Excel.

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

Параметр Явная общедоступная функция FindSumCombinations(rngNumbers As Range, lTargetSum As Long) Dim arNumbers() As Long, part() As Long Dim arRes() As String Dim indI As Long Dim cellCurr As Range ReDim arRes(0) If rngNumbers.Count > 1 Затем ReDim arNumbers(rngNumbers.Count – 1) indI = 0 Для каждой ячейкиCurr в rngNumbers arNumbers(indI) = CLng(cellCurr.Value) indI = indI + 1 Next cellCurr Call SumUpRecursiveCombinations(arNumbers, lTargetSum, part(), arRes( )) End If ReDim Preserve arRes(0 To UBound(arRes) – 1) FindSumCombinations = arRes End Function Private Sub SumUpRecursiveCombinations(Numbers() As Long, target As Long, part() As Long, ByRef arRes() As String) Dim s As Long, i As Long, j As Long, num As Long, indRes As Long Dim оставшийся() As Long, partRec() As Long s = SumArray(part) If s = target Then indRes = UBound(arRes) ReDim Preserve arRes(0 To indRes + 1) arRes(indRes) = ArrayToString(part) End If If s > target Then Exit Sub If (Not Not Numbers) <> 0 Then For i = 0 To UBound(Numbers) Удалить оставшееся() num = Numbers(i) For j = i + 1 To UBound(Numbers) AddToArray оставшиеся, Numbers(j) Next j Erase partRec() CopyArray partRec, part AddToArray partRec, num SumUpRecursiveCombinations оставшиеся, target, partRec, arRes Next i End If End Подзакрытая функция ArrayToString(x() As Long) As String Dim n As Long, result As String result = x(n) For n = LBound(x) + 1 To UBound(x) result = result & “,” & x (n) Next n ArrayToString = результат End Function Private Function SumArray(x() As Long) As Long Dim n As Long SumArray = 0 If (Not Not x) <> 0 Then For n = LBound(x) To UBound(x ) SumArray = SumArray + x(n) Next n End If End Function Private Sub AddToArray(arr() As Long, x As Long) If (Not Not arr) <> 0 Then ReDim Preserve arr(0 To UBound(arr) + 1) Else ReDim Preserve arr(0 To 0) End If arr(UBound(arr)) = x End Sub Private Sub CopyArray(destination() As Long, source() As Long) Dim n As Long If (не не источник) <> 0 Then For n = 0 To UBound(source) AddToArray Назначение, source(n) Next n End If End Sub

Как работает эта функция

Основная функция, FindSumCombinations, вызывает несколько вспомогательных функций, реализующих более мелкие подзадачи. Функция с именем SumUpRecursiveCombinations выполняет основной алгоритм, который находит все возможные суммы в указанном диапазоне и фильтрует те, которые достигают цели. Функция ArrayToString управляет формой выходных строк. Еще три функции (SumArray, AddToArray и CopyArray) отвечают за обработку промежуточных массивов: каждый раз, когда мы создаем временный массив, добавляем в него один элемент из исходного массива и проверяем, достигнута ли целевая сумма. Основной алгоритм взят из этот поток Stackoverflowспасибо, ребята, что поделились!

Синтаксис

С точки зрения пользователя, синтаксис нашей пользовательской функции так же прост:

FindSumCombinations(диапазон, сумма)

Где:

• Диапазон — это диапазон чисел для проверки.
• Сумма – целевая сумма.

Примечание! Пользовательская функция работает только в Dynamic Array Excel 365 и 2021.

Как использовать функцию FindSumCombinations:

1. Вставьте приведенный выше код в модуль кода вашей книги и сохраните его как книгу с поддержкой макросов (.xlsm). Пошаговые инструкции здесь.
2. В любой пустой ячейке введите формулу FindSumCombinations и нажмите клавишу Enter. Убедитесь, что справа достаточно пустых ячеек для вывода всех комбинаций, иначе формула вернет ошибку #SPILL.

Например, чтобы найти все возможные комбинации чисел в диапазоне A6:A15, равные сумме в A3, используется следующая формула:

=НайтиСуммКомбинаций(A6:A15, A3)

Как и в любой другой функции динамического массива, вы вводите формулу только в одну ячейку (C6 на изображении ниже), и она заполняет результаты в столько ячеек, сколько необходимо. По умолчанию строки, разделенные запятыми, выводятся подряд:

Чтобы вернуть результаты в столбец, оберните пользовательскую функцию в TRANSPOSE следующим образом:

=ТРАНСП(НайтиКомбинацииСуммы(A6:A15, A3))

Чтобы вывести строки в виде массива, заключенного в фигурные скобки, измените функцию ArrayToString следующим образом:

Частная функция ArrayToString(x() As Long) As String Dim n As Long, result As String result = “{” & x(n) For n = LBound(x) + 1 To UBound(x) result = result & “, ” & x(n) Следующий n результат = результат & “}” ArrayToString = результат End Function

Результаты будут выглядеть примерно так:

Ограничение: эта пользовательская функция работает только в Dynamic Array Excel 365 и Excel 2021.

Получить все комбинации, равные заданной сумме, с помощью макроса VBA

Пользовательская функция, описанная выше, возвращает комбинации чисел в виде строк. Если вы предпочитаете, чтобы каждое число было в отдельной ячейке, этот макрос будет полезен. Код написан другим экспертом по Excel Александром Трифунтовым, который активно помогает пользователям решать различные проблемы с Excel в этом блоге.

Начните с добавления следующего кода в книгу. Подробные инструкции см. в разделе Как вставить код VBA в Excel.

Макрос для поиска всех комбинаций, которые в сумме дают заданное значение

Public RefArray1 As String Public DS As Variant Public TargetSum As Long Public TargetCol As Integer Public TargetRow As Integer Sub Combination() UserForm1.Show End Sub Function GrayCode(Items As Variant) As String Dim CodeVector() As Integer Dim i, kk, rr , col1, row1, n1, e Как целое число Dim нижний как целое число, верхний как целое число Dim SubList как строка Dim NewSub как строка Dim done как логическое значение Dim OddStep как логическое значение Dim SSS Dim TargetArray() как строка kk = TargetCol rr = TargetRow col1 = TargetCol + 3 row1 = TargetRow OddStep = True нижний = LBound(Items) верхний = UBound(Items) Cells(rr – 1, kk) = “Result” Cells(rr – 1, kk + 1) = “Sum” Cells(rr , kk + 1) = TargetSum Cells(rr – 1, kk).Font.Bold = True Cells(rr – 1, kk + 1).Font.Bold = True ReDim CodeVector(с нижнего на верхний) ‘начинается все 0 Do Пока не выполнено NewSub = “” For i = нижний В верхний If CodeVector(i) = 1 Then If NewSub = “” Then NewSub = “,” & Items(i) SSS = SSS + Items(i) Else NewSub = NewSub & ” ,” & Items(i) SSS = SSS + Items(i) End If End If Next i If NewSub = “” Then NewSub = “{}” ‘пустой набор SubList = SubList & vbCrLf & NewSub If SSS = TargetSum Then Cells( rr, kk).NumberFormat = “@” Cells(rr, kk) = “{ ” & Mid(NewSub, 2) & ” }” TargetArray() = Split(Mid(NewSub, 2), “,”) n1 = UBound(TargetArray) For e = 0 To n1 Cells(row1, col1) = TargetArray(e) row1 = row1 + 1 Next e col1 = col1 + 1 row1 = TargetRow rr = rr + 1 End If SSS = 0 ‘код обновления вектор If OddStep Then ‘просто переверните первый бит CodeVector (нижний) = 1 – CodeVector (нижний) Else ‘сначала найдите первый 1 i = нижний Do While CodeVector (i) <> 1 i = i + 1 Цикл ‘сделано, если i = верхний : If i = upper Then done = True Else ‘если не сделано, то инвертируйте *следующий* бит: i = i + 1 CodeVector(i) = 1 – CodeVector(i) End If End If OddStep = Not OddStep ‘переключается между четными и нечетные шаги Loop GrayCode = SubList End Function

Затем создайте пользовательскую форму со следующим дизайном и свойствами:

После завершения дизайна формы добавьте код формы. Для этого щелкните правой кнопкой мыши форму в Project Explorer и выберите View Code:

Код для пользовательской формы

Private Sub CommandButton1_Click() Dim B Dim c As Integer Dim d As Integer Dim A() As Variant Dim i As Integer Dim e As Integer DS = Range(RefEdit1) TargetSum = TextBox1.Value Range(RefEdit2).Select TargetCol = Selection. Столбец TargetRow = Selection.Row c = LBound(DS) d = UBound(DS) ReDim B(d – 1) For i = 1 To d e = i – 1 B(e) = DS(i, 1) Next i Call GrayCode (B) Выгрузить меня End Sub Private Sub Label1_Click() End Sub Private Sub Label3_Click() End Sub

Кончик. Вместо того, чтобы заново создавать форму с нуля, вы можете загрузить наш образец рабочей книги в конце этого поста и скопировать оттуда модуль кода и UserForm1 (см., как скопировать макрос в другую рабочую книгу). Чтобы макрос был доступен во всех ваших книгах, скопируйте его в личную книгу макросов.

Разместив код и форму, нажмите Alt + F8 и запустите макрос FindAllCombinations:

В появившейся форме определите следующее:

• Диапазон с исходными номерами (A4:A13)
• Целевая сумма (50)
• Верхняя левая ячейка диапазона назначения (C4).

При указании верхней левой ячейки выходного диапазона убедитесь, что есть хотя бы одна пустая строка выше (для заголовков) и достаточно пустых ячеек внизу и справа. Если пустых ячеек недостаточно, существующие данные будут перезаписаны.

Нажав OK, вы получите результат, как показано на скриншоте ниже:

• В C4:C6 у вас есть комбинации чисел в виде значений, разделенных запятыми.
• Столбцы F, G и H содержат одинаковые комбинации чисел, причем каждое число находится в отдельной ячейке.
• В D4 у вас есть целевая сумма.

Такая форма вывода облегчает проверку результата — просто введите формулу СУММ в ячейку F13, перетащите ее вправо еще на две ячейки, и вы увидите, что каждая комбинация чисел в сумме дает указанное значение (50).

Ограничение: Для большого набора чисел макросу может потребоваться некоторое время для генерации всех возможных комбинаций.

Достоинства: Работает во всех версиях Excel 2010 – 365; обеспечивает две формы вывода – строки значений, разделенных запятыми, и числа в отдельных ячейках.

В заключение, нахождение всех комбинаций значений, равных определенному значению, является мощным инструментом для решения широкого круга задач анализа данных. Надеемся, что эта статья предоставила вам полезную отправную точку для дальнейшего изучения этой темы и решения подобных проблем в вашей собственной работе. Спасибо за чтение!

Практическая рабочая тетрадь

Найдите комбинации, равные заданной сумме – примеры (файл .xlsm)

Вас также могут заинтересовать