Как найти все комбинации, сумма которых равна заданному значению в Excel?

Поиск всех возможных комбинаций чисел в списке, сумма которых равна заданному значению, — задача, с которой часто сталкиваются пользователи Excel при составлении бюджета, планировании или анализе данных.

В этом примере у нас есть список чисел, и задача — найти такие их комбинации, сумма которых равна 480. На приведённом снимке экрана показано, что существует пять групп комбинаций, дающих эту сумму, включая, например, 300 + 120 + 60 или 250 + 120 + 60 + 50. В данной статье мы рассмотрим различные методы поиска конкретных комбинаций чисел в списке, сумма которых соответствует заданному значению в Excel.

Найдите комбинацию чисел, равную заданной сумме, с помощью функции «Поиск решения»

Получение всех комбинаций чисел, дающих заданную сумму

• С помощью пользовательской функции
• С помощью интеллектуальной функции – Kutools для Excel

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

Найдите комбинацию ячеек, сумма которых равна заданной, с помощью функции «Поиск решения»

Поиск комбинаций ячеек в Excel, сумма которых равна заданному числу, может показаться сложной задачей — но надстройка «Поиск решения» превращает её в простую и выполнимую. Мы подробно расскажем, как настроить «Поиск решения» и быстро найти нужную комбинацию ячеек, сделав, казалось бы, непростую задачу лёгкой и доступной.

Шаг 1: Включение надстройки «Поиск решения»

1. Перейдите в меню Файл > Параметры. В диалоговом окне Параметры Excel нажмите Надстройки на левой панели, затем — кнопку Перейти. См. снимок экрана:
   
2. Затем откроется диалоговое окно Надстройки. Установите флажок напротив пункта Поиск решения и нажмите кнопку OK, чтобы успешно установить эту надстройку.
   

Шаг 2: Ввод формулы

После активации надстройки «Поиск решения» введите следующую формулу в ячейку B11:

=SUMPRODUCT(B2:B10,A2:A10)

Шаг 3: Настройка и запуск «Поиска решения» для получения результата

1. Нажмите Данные > Поиск решения, чтобы открыть диалоговое окно Параметры поиска решения. В этом окне выполните следующие действия:
   • (1.) Щёлкните кнопку, чтобы выбрать ячейку B11, в которой находится ваша формула, в разделе «Установить целевую ячейку»;
   • (2.) Затем в разделе «Ограничения»выберите «Значение»и введите целевое значение 480, как вам нужно;
   • (3.) В разделе «Изменяя ячейки» щёлкните кнопку, чтобы выбрать диапазон ячеек B2:B10, в которых будут указаны соответствующие числа.
   • (4.) Затем нажмите кнопку Добавить.
   • 
2. Затем откроется диалоговое окно Добавление ограничения. Нажмите кнопку, чтобы выбрать диапазон ячеек B2:B10 , и выберите значение bin из раскрывающегося списка. В завершение нажмите кнопку OK . См. снимок экрана: button. See screenshot:
   
3. В диалоговом окне Параметры поиска решения нажмите кнопку Найти решение. Через несколько минут откроется диалоговое окно Результаты поиска решения, в котором вы увидите комбинации ячеек, сумма которых равна заданному значению 480, помеченные как «1» в столбце B. В диалоговом окне Результаты поиска решения выберите опцию Сохранить решение Поиска решения и нажмите кнопку OK, чтобы закрыть окно. См. снимок экрана:
   

Получение всех комбинаций чисел, дающих заданную сумму

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

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

Приведённая ниже пользовательская функция — эффективный инструмент для поиска всех возможных комбинаций чисел из заданного набора, сумма которых равна определённому значению.

Шаг 1: Откройте редактор модулей VBA и скопируйте код

1. Удерживая клавиши ALT + F11 в Excel, вы откроете окно Microsoft Visual Basic для приложений.
2. Нажмите Вставка>Модульи вставьте следующий код в окно модуля.
   Код VBA: Получить все комбинации чисел, дающих заданную сумму

   Public Function MakeupANumber(xNumbers As Range, xCount As Long)
   'updateby Extendoffice
       Dim arrNumbers() As Long
       Dim arrRes() As String
       Dim ArrTemp() As Long
       Dim xIndex As Long
       Dim rg As Range
   
       MakeupANumber = ""
       
       If xNumbers.CountLarge = 0 Then Exit Function
       ReDim arrNumbers(xNumbers.CountLarge - 1)
       
       xIndex = 0
       For Each rg In xNumbers
           If IsNumeric(rg.Value) Then
               arrNumbers(xIndex) = CLng(rg.Value)
               xIndex = xIndex + 1
           End If
       Next rg
       If xIndex = 0 Then Exit Function
       
       ReDim Preserve arrNumbers(0 To xIndex - 1)
       ReDim arrRes(0)
       
       Call Combinations(arrNumbers, xCount, ArrTemp(), arrRes())
       ReDim Preserve arrRes(0 To UBound(arrRes) - 1)
       MakeupANumber = arrRes
   End Function
   
   Private Sub Combinations(Numbers() As Long, Count As Long, ArrTemp() As Long, ByRef arrRes() As String)
   
       Dim currentSum As Long, i As Long, j As Long, k As Long, num As Long, indRes As Long
       Dim remainingNumbers() As Long, newCombination() As Long
       
       currentSum = 0
       If (Not Not ArrTemp) <> 0 Then
           For i = LBound(ArrTemp) To UBound(ArrTemp)
               currentSum = currentSum + ArrTemp(i)
           Next i
       End If
    
       If currentSum = Count Then
           indRes = UBound(arrRes)
           ReDim Preserve arrRes(0 To indRes + 1)
           
           arrRes(indRes) = ArrTemp(0)
           For i = LBound(ArrTemp) + 1 To UBound(ArrTemp)
               arrRes(indRes) = arrRes(indRes) & "," & ArrTemp(i)
           Next i
       End If
       
       If currentSum > Count Then Exit Sub
       If (Not Not Numbers) = 0 Then Exit Sub
       
       For i = 0 To UBound(Numbers)
           Erase remainingNumbers()
           num = Numbers(i)
           For j = i + 1 To UBound(Numbers)
               If (Not Not remainingNumbers) <> 0 Then
                   ReDim Preserve remainingNumbers(0 To UBound(remainingNumbers) + 1)
               Else
                   ReDim Preserve remainingNumbers(0 To 0)
               End If
               remainingNumbers(UBound(remainingNumbers)) = Numbers(j)
               
           Next j
           Erase newCombination()
   
           If (Not Not ArrTemp) <> 0 Then
               For k = 0 To UBound(ArrTemp)
                   If (Not Not newCombination) <> 0 Then
                       ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
                   Else
                       ReDim Preserve newCombination(0 To 0)
                   End If
                   newCombination(UBound(newCombination)) = ArrTemp(k)
   
               Next k
           End If
           
           If (Not Not newCombination) <> 0 Then
               ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
           Else
               ReDim Preserve newCombination(0 To 0)
           End If
           
           newCombination(UBound(newCombination)) = num
   
           Combinations remainingNumbers, Count, newCombination, arrRes
       Next i
   
   End Sub
   

Шаг 2: Введите пользовательскую формулу для получения результата

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

=MakeupANumber(A2:A10,B2)

Совет: Если Вы хотите вывести результаты комбинаций вертикально в столбец, используйте следующую формулу:

=TRANSPOSE(MakeupANumber(A2:A10,B2))

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

Учитывая ограничения вышеупомянутой функции, мы рекомендуем быстрое и комплексное решение — функцию «Создание чисел» из Kutools для Excel, совместимую с любой версией Excel. Эта альтернатива эффективно работает с положительными числами, десятичными дробями и отрицательными значениями, мгновенно находя все комбинации, сумма которых равна заданной.

1. Нажмите Kutools > Содержимое > Создание чисел. См. снимок экрана:
   
2. В диалоговом окне Создание чисел нажмите кнопку, чтобы выбрать нужный список чисел из поля Исходный диапазон, затем введите итоговую сумму в текстовое поле Сумма и нажмите кнопку OK. См. снимок экрана:
   
3. Затем появится окно с запросом выбора ячейки для размещения результата. Нажмите кнопку OK. См. снимок экрана:
   
4. Теперь все комбинации, сумма которых равна указанному числу, отображаются, как показано на снимке экрана ниже:
   

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

Иногда возникает необходимость найти все возможные комбинации чисел, сумма которых попадает в заданный диапазон — например, от 470 до 480.

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

Шаг 1: Откройте редактор модулей VBA и скопируйте код

1. Удерживая клавиши ALT + F11в Excel, вы откроете окно Microsoft Visual Basic для приложений.
2. Нажмите Вставка>Модульи вставьте следующий код в окно модуля.
   Код VBA: Получить все комбинации чисел, сумма которых попадает в заданный диапазон

   Sub Getall_combinations()
   'Updateby Extendoffice
       Dim xNumbers As Variant
       Dim Output As Collection
       Dim rngSelection As Range
       Dim OutputCell As Range
       Dim LowLimit As Long, HiLimit As Long
       Dim i As Long, j As Long
       Dim TotalCombinations As Long
       Dim CombTotal As Double
       Set Output = New Collection
       On Error Resume Next
       Set rngSelection = Application.InputBox("Select the range of numbers:", "Kutools for Excel", Type:=8)
       If rngSelection Is Nothing Then
           MsgBox "No range selected. Exiting macro.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
       On Error GoTo 0
       xNumbers = rngSelection.Value
       LowLimit = Application.InputBox("Select or enter the low limit number:", "Kutools for Excel", Type:=1)
       HiLimit = Application.InputBox("Select or enter the high limit number:", "Kutools for Excel", Type:=1)
       On Error Resume Next
       Set OutputCell = Application.InputBox("Select the first cell for output:", "Kutools for Excel", Type:=8)
       If OutputCell Is Nothing Then
           MsgBox "No output cell selected. Exiting macro.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
       On Error GoTo 0
       TotalCombinations = 2 ^ (UBound(xNumbers, 1) * UBound(xNumbers, 2))
       For i = 1 To TotalCombinations - 1
           Dim tempArr() As Double
           ReDim tempArr(1 To UBound(xNumbers, 1) * UBound(xNumbers, 2))
           CombTotal = 0
           Dim k As Long: k = 0
           
           For j = 1 To UBound(xNumbers, 1)
               If i And (2 ^ (j - 1)) Then
                   k = k + 1
                   tempArr(k) = xNumbers(j, 1)
                   CombTotal = CombTotal + xNumbers(j, 1)
               End If
           Next j
           If CombTotal >= LowLimit And CombTotal <= HiLimit Then
               ReDim Preserve tempArr(1 To k)
               Output.Add tempArr
           End If
       Next i
       Dim rowOffset As Long
       rowOffset = 0
       Dim item As Variant
       For Each item In Output
           For j = 1 To UBound(item)
               OutputCell.Offset(rowOffset, j - 1).Value = item(j)
           Next j
           rowOffset = rowOffset + 1
       Next item
   End Sub
   
   
   

Шаг 2: Выполнение кода

1. После вставки кода нажмите клавишу F5, чтобы запустить его. В первом появившемся диалоговом окне выберите диапазон чисел, которые вы хотите использовать, и нажмите кнопку OK. См. снимок экрана:
   
2. Во втором диалоговом окне укажите или введите нижнюю границу диапазона и нажмите кнопку OK. См. снимок экрана:
   
3. В третьем диалоговом окне укажите или введите верхнюю границу диапазона и нажмите кнопку OK. См. снимок экрана:
   
4. В последнем диалоговом окне выберите ячейку вывода — именно с неё начнётся отображение результатов, затем нажмите кнопку OK. См. снимок экрана:
   

Результат

Теперь каждая подходящая комбинация будет отображаться в последовательных строках на листе, начиная с выбранной вами ячейки вывода.

Excel предлагает несколько способов найти группу чисел, сумма которых равна заданному значению. Каждый метод работает по-своему, так что вы можете выбрать тот, что лучше всего соответствует вашему уровню владения Excel и задачам проекта. Если вы хотите узнать ещё больше полезных советов и приёмов работы в Excel, на нашем сайте доступны тысячи обучающих материалов. Благодарим за внимание и надеемся делиться с вами ещё большим количеством полезной информации в будущем!

Связанные статьи: