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

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

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

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

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

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

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

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

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

Шаг1: Включите надстройку Solver

1. Пожалуйста, перейдите в Файл > Опции, в Параметры Excel в диалоговом окне нажмите Надстройки в левой панели, затем нажмите Перейти кнопку. См. скриншот:
   
2. Затем появится диалоговое окно Надстройки отметьте пункт Solver Add-in и нажмите OK чтобы успешно установить эту надстройку.
   

Шаг2: Введите формулу

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

=SUMPRODUCT(B2:B10,A2:A10)

Шаг3: Настройте и запустите Solver для получения результата

1. Нажмите Данные > Solver чтобы перейти в Параметры Solver в диалоговом окне выполните следующие действия:
   • (1.) Нажмите кнопку, чтобы выбрать ячейку B11 где находится ваша формула, в разделе Set Objective ;
   • (2.) Затем в разделе To выберите Value Of и введите нужное целевое значение 480;
   • (3.) В разделе By Changing Variable Cells пожалуйста, нажмите кнопку, чтобы выбрать диапазон ячеек B2:B10 где будут отмечены соответствующие числа.
   • (4.) Затем нажмите кнопку Добавить.
   • 
2. После этого появится диалоговое окно Добавить ограничение нажмите кнопку, чтобы выбрать диапазон ячеек B2:B10, и выберите bin из выпадающего списка. В конце нажмите OK кнопку. См. скриншот:
   
3. В Параметры Solver диалоге нажмите Решить кнопку, через несколько минут появится диалоговое окно Результаты Solver и вы увидите, что комбинации ячеек, сумма которых равна480, отмечены как1 в столбце B. В Результаты Solver диалоге выберите Сохранить решение Solver и нажмите OK чтобы выйти из диалога. См. скриншот:
   

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

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

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

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

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

1. Удерживайте клавиши ALT + F11 в Excel, чтобы открыть окно Microsoft Visual Basic for Applications.
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 for Applications.
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, на нашем сайте вы найдёте тысячи обучающих материалов. Спасибо за внимание, и мы рады будем делиться с вами полезной информацией в будущем!

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